ExportModuleController.class.php.bak.2021-09-02 34 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672
  1. <?php
  2. namespace Home\Controller;
  3. use Think\Controller;
  4. use Vendor\PHPExcel;
  5. class ExportModuleController extends ComController
  6. {
  7. public function getStatusDetailsListExpor()
  8. {
  9. $Date = I();
  10. $phone = $Date['phone'];
  11. $company_code = $Date['company_code'];
  12. $status_type = $Date['status_type'];
  13. $transfer_type = $Date['transfer_type'];
  14. $dwtype = $Date['dwtype'];
  15. if (!empty($_SESSION['phone'])) {
  16. $phone = $_SESSION['phone'];
  17. }
  18. if (!isset($status_type)) {
  19. $res['msg'] = '操作失败,缺少重要参数';
  20. $res['flag'] = false;
  21. echo json_encode($res);
  22. exit();
  23. }
  24. if (!empty($company_code)) {
  25. $device_list = $this->getDeviceCode($company_code);
  26. } else {
  27. $company_code1 = M()->query("select owner_code from sp_owner_phone WHERE phone='" . $phone . "' GROUP BY owner_code limit 1");
  28. $company_code = $company_code1[0]['owner_code'];
  29. $device_list = $this->getDeviceCode($company_code);
  30. }
  31. $where = '';
  32. if (!empty($transfer_type)) {
  33. $where .= " and b.transfer_type='" . $transfer_type . "' ";
  34. }
  35. if (!empty($dwtype)) {
  36. $where .= " and b.dwtype='" . $dwtype . "' ";
  37. }
  38. switch ($status_type) {
  39. case '1':
  40. $data1 = M()->query("select b.* from sp_v_device_status as a join sp_owner as b on a.device_id=b.owner_code where a.device_status='离线' and a.device_id in(" . $device_list . ")" . $where);
  41. $data2 = M()->query("SELECT b.* FROM sp_devices_status AS a JOIN sp_owner AS b ON a.deviceid=b.owner_code WHERE b.dwtype=1 AND a.true_status in(75,300) AND b.company='" . $company_code . "' " . $where);
  42. break;
  43. case '2':
  44. $data1 = M()->query("select b.* from sp_v_device_status as a join sp_owner as b on a.device_id=b.owner_code where a.device_status='在线' and a.device_id in(" . $device_list . ")" . $where);
  45. $data2 = M()->query("SELECT b.* FROM sp_devices_status AS a JOIN sp_owner AS b ON a.deviceid=b.owner_code WHERE b.dwtype=1 AND a.true_status='0' AND b.company='" . $company_code . "' " . $where);
  46. break;
  47. default:
  48. $res['msg'] = '操作失败,系统中尚未定义该类型';
  49. $res['flag'] = false;
  50. echo json_encode($res);
  51. exit();
  52. break;
  53. }
  54. $data = array_merge($data1, $data2);
  55. vendor('PHPExcel.PHPExcel');
  56. // vendor('PHPExcel.PHPExcel.Style');
  57. $objPHPExcel = new \PHPExcel();
  58. $objPHPExcel->setActiveSheetIndex(0);
  59. $objPHPExcel->setActiveSheetIndex(0)
  60. ->setCellValue('A1', '设备编号')
  61. ->setCellValue('B1', '设备名称')
  62. ->setCellValue('C1', '单元地址')
  63. ->setCellValue('D1', '类型编号')
  64. ->setCellValue('E1', '传输方式')
  65. ->setCellValue('F1', '添加时间');
  66. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(30);
  67. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(30);
  68. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(30);
  69. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(20);
  70. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(20);
  71. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(30);
  72. $objPHPExcel->getActiveSheet()->getStyle('A')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  73. $objPHPExcel->getActiveSheet()->getStyle('B')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  74. $objPHPExcel->getActiveSheet()->getStyle('C')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  75. $objPHPExcel->getActiveSheet()->getStyle('D')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  76. $objPHPExcel->getActiveSheet()->getStyle('E')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  77. $objPHPExcel->getActiveSheet()->getStyle('F')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  78. if (!empty($data)) {
  79. foreach ($data as $key => $value) {
  80. // $objPHPExcel->getActiveSheet()->getStyle('A'. ($key + 2))->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  81. $objPHPExcel->getActiveSheet()->setCellValue('A' . ($key + 2), ' ' . $data[$key]['owner_code']);
  82. $objPHPExcel->getActiveSheet()->setCellValue('B' . ($key + 2), $data[$key]['owner_name']);
  83. $objPHPExcel->getActiveSheet()->setCellValue('C' . ($key + 2), $data[$key]['unitinfo']);
  84. $objPHPExcel->getActiveSheet()->setCellValue('D' . ($key + 2), $data[$key]['dwtype']);
  85. $objPHPExcel->getActiveSheet()->setCellValue('E' . ($key + 2), $data[$key]['transfer_type']);
  86. $objPHPExcel->getActiveSheet()->setCellValue('F' . ($key + 2), $data[$key]['install_time']);
  87. }
  88. }
  89. $filename = '设备运行状态列表' . date('ymd', time()) . '.xls';
  90. $objPHPExcel->getActiveSheet()->setTitle('设备运行状态列表');
  91. ob_end_clean();
  92. header("Content-Type: application/force-download");
  93. header("Content-Type: application/octet-stream");
  94. header("Content-Type: application/download");
  95. header('Content-Disposition:inline;filename="' . $filename . '"');
  96. $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  97. $objWriter->save('php://output');
  98. exit;
  99. }
  100. public function getManualInspectorListExpor()
  101. {
  102. $Data = I('');
  103. $clzt = $Data['clzt'];
  104. $start_time = $Data['start_time'];
  105. $end_time = $Data['end_time'];
  106. $company_code = $Data['company_code'];
  107. if (empty($company_code)) {
  108. $res['msg'] = '操作失败,缺少参数';
  109. $res['flag'] = false;
  110. echo json_encode($res);
  111. }
  112. $where = '';
  113. if (isset($clzt)) {
  114. $where .= " and clzt=" . $clzt . " ";
  115. }
  116. if (!empty($start_time)) {
  117. $where .= " and timestamp>='" . $start_time . "' ";
  118. }
  119. if (!empty($end_time)) {
  120. $where .= " and timestamp<'" . $end_time . "' ";
  121. }
  122. $ResData = query_187_ytapi("select id,content,timestamp,artificial_code,clzt from person_data where company_code='" . $company_code . "' " . $where . " order by timestamp desc LIMIT 1000");
  123. foreach ($ResData as $key => $value) {
  124. $str = str_replace('"]', '', $ResData[$key]['content']);
  125. $ResData[$key]['content'] = str_replace('["', '', $str);
  126. }
  127. vendor('PHPExcel.PHPExcel');
  128. $objPHPExcel = new \PHPExcel();
  129. $objPHPExcel->setActiveSheetIndex(0);
  130. $objPHPExcel->setActiveSheetIndex(0)
  131. ->setCellValue('A1', '督察单编号')
  132. ->setCellValue('B1', '督察内容')
  133. ->setCellValue('C1', '处理状态')
  134. ->setCellValue('D1', '上报时间');
  135. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(30);
  136. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(30);
  137. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(30);
  138. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(30);
  139. $objPHPExcel->getActiveSheet()->getStyle('A')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  140. $objPHPExcel->getActiveSheet()->getStyle('B')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  141. $objPHPExcel->getActiveSheet()->getStyle('C')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  142. $objPHPExcel->getActiveSheet()->getStyle('D')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  143. if (!empty($ResData)) {
  144. foreach ($ResData as $key => $value) {
  145. $objPHPExcel->getActiveSheet()->setCellValue('A' . ($key + 2), ' ' . $ResData[$key]['artificial_code']);
  146. $objPHPExcel->getActiveSheet()->setCellValue('B' . ($key + 2), $ResData[$key]['content']);
  147. if ($ResData[$key]['clzt'] == 0) {
  148. $objPHPExcel->getActiveSheet()->setCellValue('C' . ($key + 2), "未处理");
  149. } else {
  150. $objPHPExcel->getActiveSheet()->setCellValue('C' . ($key + 2), "已处理");
  151. }
  152. $objPHPExcel->getActiveSheet()->setCellValue('D' . ($key + 2), $ResData[$key]['timestamp']);
  153. }
  154. }
  155. $filename = '人员督察单' . date('ymd', time()) . '.xls';
  156. $objPHPExcel->getActiveSheet()->setTitle('人员督察单');
  157. ob_end_clean();
  158. header("Content-Type: application/force-download");
  159. header("Content-Type: application/octet-stream");
  160. header("Content-Type: application/download");
  161. header('Content-Disposition:inline;filename="' . $filename . '"');
  162. $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  163. $objWriter->save('php://output');
  164. exit;
  165. }
  166. public function getElectronicInspectorListExpor()
  167. {
  168. $Data = I('');
  169. $clzt = $Data['clzt'];
  170. $start_time = $Data['start_time'];
  171. $end_time = $Data['end_time'];
  172. $company_code = $Data['company_code'];
  173. if (empty($company_code)) {
  174. $res['msg'] = '操作失败,缺少参数';
  175. $res['flag'] = false;
  176. echo json_encode($res);
  177. }
  178. $where = '';
  179. if (isset($clzt)) {
  180. $where .= " and clzt=" . $clzt . " ";
  181. }
  182. if (!empty($start_time)) {
  183. $where .= " and timestamp>='" . $start_time . "' ";
  184. }
  185. if (!empty($end_time)) {
  186. $where .= " and timestamp<'" . $end_time . "' ";
  187. }
  188. $ResData = query_187_ytapi("select id,content,timestamp,supervise_code,clzt from jingan_analysis_data where status=0 and content !='设备离线' and company_code='" . $company_code . "' " . $where . " order by timestamp desc");
  189. foreach ($ResData as $key => $value) {
  190. $str = str_replace('"]', '', $ResData[$key]['content']);
  191. $ResData[$key]['content'] = str_replace('["', '', $str);
  192. }
  193. vendor('PHPExcel.PHPExcel');
  194. $objPHPExcel = new \PHPExcel();
  195. $objPHPExcel->setActiveSheetIndex(0);
  196. $objPHPExcel->setActiveSheetIndex(0)
  197. ->setCellValue('A1', '督察单编号')
  198. ->setCellValue('B1', '督察内容')
  199. ->setCellValue('C1', '处理状态')
  200. ->setCellValue('D1', '上报时间');
  201. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(30);
  202. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(30);
  203. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(30);
  204. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(30);
  205. $objPHPExcel->getActiveSheet()->getStyle('A')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  206. $objPHPExcel->getActiveSheet()->getStyle('B')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  207. $objPHPExcel->getActiveSheet()->getStyle('C')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  208. $objPHPExcel->getActiveSheet()->getStyle('D')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  209. if (!empty($ResData)) {
  210. foreach ($ResData as $key => $value) {
  211. $objPHPExcel->getActiveSheet()->setCellValue('A' . ($key + 2), ' ' . $ResData[$key]['supervise_code']);
  212. $objPHPExcel->getActiveSheet()->setCellValue('B' . ($key + 2), $ResData[$key]['content']);
  213. if ($ResData[$key]['clzt'] == 0) {
  214. $objPHPExcel->getActiveSheet()->setCellValue('C' . ($key + 2), "未处理");
  215. } else {
  216. $objPHPExcel->getActiveSheet()->setCellValue('C' . ($key + 2), "已处理");
  217. }
  218. $objPHPExcel->getActiveSheet()->setCellValue('D' . ($key + 2), $ResData[$key]['timestamp']);
  219. }
  220. }
  221. $filename = '电子督察单' . date('ymd', time()) . '.xls';
  222. $objPHPExcel->getActiveSheet()->setTitle('电子督察单');
  223. ob_end_clean();
  224. header("Content-Type: application/force-download");
  225. header("Content-Type: application/octet-stream");
  226. header("Content-Type: application/download");
  227. header('Content-Disposition:inline;filename="' . $filename . '"');
  228. $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  229. $objWriter->save('php://output');
  230. exit;
  231. }
  232. public function getDeviceListExpor()
  233. {
  234. $Data = I('');
  235. $company_code = $Data['company_code'];
  236. $dwtype = $Data['dwtype'];
  237. $start_time = $Data['start_time'];
  238. $end_time = $Data['end_time'];
  239. $where = '';
  240. if (!empty($start_time)) {
  241. $where .= " and a.install_time>='" . $start_time . "' ";
  242. }
  243. if (!empty($end_time)) {
  244. $where .= " and a.install_time<'" . $end_time . "' ";
  245. }
  246. if ($dwtype == 1) {
  247. $device_list = M()->query("SELECT a.id,a.owner_code,a.owner_name,a.dwtype,b.true_status,IF(b.true_status=0,'在线','离线') as device_state,a.install_time,a.unitinfo FROM sp_owner AS a JOIN sp_devices_status AS b ON a.owner_code=b.deviceid WHERE a.company='" . $company_code . "' AND a.dwtype=1 " . $where . " order by a.id DESC");
  248. } else {
  249. $device_list = M()->query("SELECT a.id,a.owner_code,a.owner_name,a.dwtype,IF(timestampdiff(HOUR,max(`b`.`data_time`),now())<12,'在线','离线') AS device_state,a.install_time,a.unitinfo FROM sp_owner AS a left JOIN sp_owner_status AS b ON a.owner_code=b.device_id WHERE a.company='" . $company_code . "' AND a.dwtype=" . $dwtype . " " . $where . " GROUP BY a.owner_code ORDER BY a.id DESC");
  250. }
  251. vendor('PHPExcel.PHPExcel');
  252. $objPHPExcel = new \PHPExcel();
  253. $objPHPExcel->setActiveSheetIndex(0);
  254. $objPHPExcel->setActiveSheetIndex(0)
  255. ->setCellValue('A1', '设备编号')
  256. ->setCellValue('B1', '设备名称')
  257. ->setCellValue('C1', '单元地址')
  258. ->setCellValue('D1', '设备状态')
  259. ->setCellValue('E1', '添加时间');
  260. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(30);
  261. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(30);
  262. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(30);
  263. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(30);
  264. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(30);
  265. $objPHPExcel->getActiveSheet()->getStyle('A')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  266. $objPHPExcel->getActiveSheet()->getStyle('B')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  267. $objPHPExcel->getActiveSheet()->getStyle('C')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  268. $objPHPExcel->getActiveSheet()->getStyle('D')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  269. $objPHPExcel->getActiveSheet()->getStyle('E')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  270. if (!empty($device_list)) {
  271. foreach ($device_list as $key => $value) {
  272. $objPHPExcel->getActiveSheet()->setCellValue('A' . ($key + 2), ' ' . $device_list[$key]['owner_code']);
  273. $objPHPExcel->getActiveSheet()->setCellValue('B' . ($key + 2), $device_list[$key]['owner_name']);
  274. $objPHPExcel->getActiveSheet()->setCellValue('C' . ($key + 2), $device_list[$key]['unitinfo']);
  275. $objPHPExcel->getActiveSheet()->setCellValue('D' . ($key + 2), $device_list[$key]['device_state']);
  276. $objPHPExcel->getActiveSheet()->setCellValue('E' . ($key + 2), $device_list[$key]['install_time']);
  277. }
  278. }
  279. $filename = '设备管理列表' . date('ymd', time()) . '.xls';
  280. $objPHPExcel->getActiveSheet()->setTitle('设备管理列表');
  281. ob_end_clean();
  282. header("Content-Type: application/force-download");
  283. header("Content-Type: application/octet-stream");
  284. header("Content-Type: application/download");
  285. header('Content-Disposition:inline;filename="' . $filename . '"');
  286. $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  287. $objWriter->save('php://output');
  288. exit;
  289. }
  290. public function getIntegratedAlarmListExpor()
  291. {
  292. $Date = I();
  293. $company_code = $Date['company_code'];
  294. $type = $Date['type'];
  295. $processing_status = $Date['processing_status'];
  296. $start_time = $Date['start_time'];
  297. $end_time = $Date['end_time'];
  298. if (empty($company_code) && empty($type)) {
  299. $res['msg'] = '操作失败,缺少关键参数';
  300. $res['flag'] = false;
  301. echo json_encode($res);
  302. exit();
  303. }
  304. $where = '';
  305. if (!empty($start_time)) {
  306. $where .= " and a.time>='" . $start_time . "' ";
  307. }
  308. if (isset($processing_status)) {
  309. $where .= " and a.clzt='" . $processing_status . "' ";
  310. }
  311. if (!empty($start_time)) {
  312. $where .= " and a.time<'" . $end_time . "' ";
  313. }
  314. switch ($type) {
  315. case '1'://报警主机
  316. $table = 'sp_hj2017';
  317. $types = '1';
  318. break;
  319. case '2'://水表和液位
  320. $table = 'sp_sj2017';
  321. $types = '2,5';
  322. break;
  323. case '3'://烟感
  324. $table = 'sp_hj2017';
  325. $types = '3';
  326. break;
  327. case '4'://消防栓
  328. $table = 'sp_sj2017';
  329. $types = $type;
  330. break;
  331. case '5'://液位
  332. $table = 'sp_sj2017';
  333. $types = '5';
  334. break;
  335. case '6'://RTU
  336. $table = 'sp_rtu2017';
  337. $types = $type;
  338. break;
  339. case '7'://电气火灾
  340. $table = 'sp_ef2017';
  341. $types = $type;
  342. break;
  343. case '16'://视频监测
  344. $table = 'sp_video2017';
  345. $types = $type;
  346. break;
  347. case '17'://电梯监测
  348. $table = 'sp_lifter2017';
  349. $types = $type;
  350. break;
  351. case '128'://井盖
  352. $table = 'sp_iw2017';
  353. $types = $type;
  354. break;
  355. case '129'://地磁
  356. $table = 'sp_dc2020';
  357. $types = $type;
  358. break;
  359. case '130'://门禁
  360. $table = 'sp_door2020';
  361. $types = $type;
  362. break;
  363. case '131'://可燃气体
  364. $table = 'sp_gas2020';
  365. $types = $type;
  366. break;
  367. default:
  368. $res['msg'] = '操作失败,设备类型尚未定义,请联系管理员';
  369. $res['flag'] = 3002;
  370. echo json_encode($res);
  371. exit();
  372. break;
  373. }
  374. $alarm_list = M()->query("SELECT a.id,a.device_code,a.time,a.data1,a.data4,a.data2,a.clzt,b.company AS company_code,b.owner_name,b.unitinfo,b.dwtype as type FROM " . $table . " AS a JOIN sp_owner AS b ON a.device_code=b.owner_code WHERE b.dwtype in(" . $types . ") AND b.company='" . $company_code . "' " . $where . " ORDER BY a.id DESC");
  375. if ($type == 2 || $type == 4) {
  376. foreach ($alarm_list as $key => $value) {
  377. switch ($alarm_list[$key]['data1']) {
  378. case 'WP1':
  379. $alarm_list[$key]['data4'] = '低压';
  380. break;
  381. case 'WP2':
  382. $alarm_list[$key]['data4'] = '高压';
  383. break;
  384. case 'WP3':
  385. $alarm_list[$key]['data4'] = '故障';
  386. break;
  387. case 'WP4':
  388. $alarm_list[$key]['data4'] = '离线';
  389. break;
  390. case 'LL1':
  391. $alarm_list[$key]['data4'] = '低水位';
  392. break;
  393. case 'LL2':
  394. $alarm_list[$key]['data4'] = '高水位';
  395. break;
  396. default:
  397. $alarm_list[$key]['data4'] = '其他' . $alarm_list[$key]['data1'];
  398. break;
  399. }
  400. }
  401. } else if ($type == 7) {
  402. foreach ($alarm_list as $key => $value) {
  403. if ($alarm_list[$key]['data1'] == 'EF1') {
  404. $alarm_list[$key]['data4'] = '欠压报警';
  405. } elseif ($alarm_list[$key]['data1'] == 'EF2') {
  406. $alarm_list[$key]['data4'] = '过压报警';
  407. } elseif ($alarm_list[$key]['data1'] == 'EF3') {
  408. $alarm_list[$key]['data4'] = '过流报警';
  409. } elseif ($alarm_list[$key]['data1'] == 'EF4') {
  410. $alarm_list[$key]['data4'] = '漏电报警';
  411. } elseif ($alarm_list[$key]['data1'] == 'EF5') {
  412. $alarm_list[$key]['data4'] = '回路1温度超限报警';
  413. } elseif ($alarm_list[$key]['data1'] == 'EF6') {
  414. $alarm_list[$key]['data4'] = '回路2温度超限报警';
  415. } elseif ($alarm_list[$key]['data1'] == 'EF7') {
  416. $alarm_list[$key]['data4'] = '回路3温度超限报警';
  417. } elseif ($alarm_list[$key]['data1'] == 'EF8') {
  418. $alarm_list[$key]['data4'] = '回路4温度超限报警';
  419. } elseif ($alarm_list[$key]['data1'] == 'EF9') {
  420. $alarm_list[$key]['data4'] = '离线';
  421. } else {
  422. $alarm_list[$key]['data4'] = '其他' . $alarm_list[$key]['data1'];
  423. }
  424. }
  425. }
  426. vendor('PHPExcel.PHPExcel');
  427. $objPHPExcel = new \PHPExcel();
  428. $objPHPExcel->setActiveSheetIndex(0);
  429. $objPHPExcel->setActiveSheetIndex(0)
  430. ->setCellValue('A1', '设备编号')
  431. ->setCellValue('B1', '设备名称')
  432. ->setCellValue('C1', '单元地址')
  433. ->setCellValue('D1', '告警状态')
  434. ->setCellValue('E1', '告警时间');
  435. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(30);
  436. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(30);
  437. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(30);
  438. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(30);
  439. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(30);
  440. $objPHPExcel->getActiveSheet()->getStyle('A')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  441. $objPHPExcel->getActiveSheet()->getStyle('B')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  442. $objPHPExcel->getActiveSheet()->getStyle('C')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  443. $objPHPExcel->getActiveSheet()->getStyle('D')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  444. $objPHPExcel->getActiveSheet()->getStyle('E')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  445. if (!empty($alarm_list)) {
  446. foreach ($alarm_list as $key => $value) {
  447. $objPHPExcel->getActiveSheet()->setCellValue('A' . ($key + 2), ' ' . $alarm_list[$key]['device_code']);
  448. $objPHPExcel->getActiveSheet()->setCellValue('B' . ($key + 2), $alarm_list[$key]['owner_name']);
  449. $objPHPExcel->getActiveSheet()->setCellValue('C' . ($key + 2), $alarm_list[$key]['unitinfo']);
  450. if ($alarm_list[$key]['clzt'] == 0) {
  451. $objPHPExcel->getActiveSheet()->setCellValue('D' . ($key + 2), "未处理");
  452. } else if ($alarm_list[$key]['clzt'] == 1) {
  453. $objPHPExcel->getActiveSheet()->setCellValue('D' . ($key + 2), "已处理");
  454. }
  455. $objPHPExcel->getActiveSheet()->setCellValue('E' . ($key + 2), $alarm_list[$key]['time']);
  456. }
  457. }
  458. $filename = '综合告警列表' . date('ymd', time()) . '.xls';
  459. $objPHPExcel->getActiveSheet()->setTitle('综合告警列表');
  460. ob_end_clean();
  461. header("Content-Type: application/force-download");
  462. header("Content-Type: application/octet-stream");
  463. header("Content-Type: application/download");
  464. header('Content-Disposition:inline;filename="' . $filename . '"');
  465. $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  466. $objWriter->save('php://output');
  467. exit;
  468. }
  469. public function getDeviceConfigListExpor()
  470. {
  471. $Data = I();
  472. $company_code = $Data['company_code'];
  473. $dwtype = $Data['dwtype'];
  474. $transmission_mode = $Data['transmission_mode'];
  475. $status = $Data['status'];
  476. $device_code_list = $Data['device_code_list'];
  477. $where = "";
  478. if (!empty($dwtype)) {
  479. $where .= " and a.dwtype = '" . $dwtype . "' ";
  480. }
  481. if (!empty($transmission_mode)) {
  482. $where .= " and a.transfer_type = '" . $transmission_mode . "' ";
  483. }
  484. $device_list = '';
  485. if ($status == 1) {
  486. } else {
  487. if (!empty($device_code_list)){
  488. $device_list1 = explode(",", $device_code_list);
  489. // print_r($device_list1);
  490. // exit();
  491. for ($i=0;$i<count($device_list1);$i++){
  492. if ($i==0){
  493. $device_list="'".$device_list1[$i]."'";
  494. }else{
  495. $device_list.=",'".$device_list1[$i]."'";
  496. }
  497. }
  498. }
  499. // $device_list = str_replace('"',"'",str_replace(']', '', str_replace('[', '', $device_code_list)));
  500. if (!empty($device_list)) {
  501. $where .= " and a.owner_code in (" . $device_list . ") ";
  502. }
  503. }
  504. if (empty($dwtype)) {
  505. $device_data1 = M()->query("SELECT a.id,a.owner_code,a.louyu,a.company,a.owner_name,a.dwtype,IF(timestampdiff(HOUR,max(`b`.`data_time`),now())<12,'在线','离线') AS device_state,a.install_time,a.unitinfo FROM sp_owner AS a JOIN sp_owner_status AS b ON a.owner_code=b.device_id WHERE a.dwtype!=1 and a.company='" . $company_code . "' " . $where . " GROUP BY a.owner_code ORDER BY a.install_time DESC");
  506. $device_data2 = M()->query("SELECT a.id,a.owner_code,a.louyu,a.company,a.owner_name,a.dwtype,IF(true_status=0,'在线','离线') AS device_state,a.install_time,a.unitinfo FROM sp_owner AS a JOIN sp_devices_status AS b ON a.owner_code=b.deviceid WHERE a.dwtype=1 and a.company='" . $company_code . "' " . $where . " GROUP BY a.owner_code ORDER BY a.install_time DESC");
  507. $device_data = array_merge($device_data1, $device_data2);
  508. array_multisort(array_column($device_data, 'install_time'), SORT_DESC, $device_data);
  509. } else {
  510. if ($dwtype == 1) {
  511. $device_data = M()->query("SELECT a.id,a.owner_code,a.louyu,a.company,a.owner_name,a.dwtype,IF(true_status=0,'在线','离线') AS device_state,a.install_time,a.unitinfo FROM sp_owner AS a JOIN sp_devices_status AS b ON a.owner_code=b.deviceid WHERE a.dwtype=1 and a.company='" . $company_code . "' " . $where . " GROUP BY a.owner_code ORDER BY a.install_time DESC");
  512. } else {
  513. $device_data = M()->query("SELECT a.id,a.owner_code,a.louyu,a.company,a.owner_name,a.dwtype,IF(timestampdiff(HOUR,max(`b`.`data_time`),now())<12,'在线','离线') AS device_state,a.install_time,a.unitinfo FROM sp_owner AS a JOIN sp_owner_status AS b ON a.owner_code=b.device_id WHERE a.dwtype!=1 and a.company='" . $company_code . "' " . $where . " GROUP BY a.owner_code ORDER BY a.install_time DESC");
  514. }
  515. }
  516. vendor('PHPExcel.PHPExcel');
  517. $objPHPExcel = new \PHPExcel();
  518. $objPHPExcel->setActiveSheetIndex(0);
  519. $objPHPExcel->setActiveSheetIndex(0)
  520. ->setCellValue('A1', '设备编号')
  521. ->setCellValue('B1', '设备名称')
  522. ->setCellValue('C1', '设备类型')
  523. ->setCellValue('D1', '单元地址')
  524. ->setCellValue('E1', '设备状态')
  525. ->setCellValue('F1', '告警时间');
  526. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(30);
  527. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(30);
  528. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(30);
  529. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(30);
  530. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(30);
  531. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(30);
  532. $objPHPExcel->getActiveSheet()->getStyle('A')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  533. $objPHPExcel->getActiveSheet()->getStyle('B')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  534. $objPHPExcel->getActiveSheet()->getStyle('C')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  535. $objPHPExcel->getActiveSheet()->getStyle('D')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  536. $objPHPExcel->getActiveSheet()->getStyle('E')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  537. $objPHPExcel->getActiveSheet()->getStyle('F')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  538. if (!empty($device_data)) {
  539. foreach ($device_data as $key => $value) {
  540. $objPHPExcel->getActiveSheet()->setCellValue('A' . ($key + 2), ' ' . $device_data[$key]['owner_code']);
  541. $objPHPExcel->getActiveSheet()->setCellValue('B' . ($key + 2), $device_data[$key]['owner_name']);
  542. switch ($device_data[$key]['dwtype']) {
  543. case '1':
  544. $objPHPExcel->getActiveSheet()->setCellValue('C' . ($key + 2), "用传");
  545. break;
  546. case '2':
  547. $objPHPExcel->getActiveSheet()->setCellValue('C' . ($key + 2), "水表");
  548. break;
  549. case '3':
  550. $objPHPExcel->getActiveSheet()->setCellValue('C' . ($key + 2), "烟感");
  551. break;
  552. case '5':
  553. $objPHPExcel->getActiveSheet()->setCellValue('C' . ($key + 2), "液位");
  554. break;
  555. case '6':
  556. $objPHPExcel->getActiveSheet()->setCellValue('C' . ($key + 2), "RTU");
  557. break;
  558. case '7':
  559. $objPHPExcel->getActiveSheet()->setCellValue('C' . ($key + 2), "电气火灾");
  560. break;
  561. case '16':
  562. $objPHPExcel->getActiveSheet()->setCellValue('C' . ($key + 2), "视频监测");
  563. break;
  564. case '17':
  565. $objPHPExcel->getActiveSheet()->setCellValue('C' . ($key + 2), "电梯监测");
  566. break;
  567. case '128'://井盖
  568. $objPHPExcel->getActiveSheet()->setCellValue('C' . ($key + 2), "井盖监测");
  569. break;
  570. case '129'://地磁
  571. $objPHPExcel->getActiveSheet()->setCellValue('C' . ($key + 2), "地磁监测");
  572. break;
  573. case '130'://门禁
  574. $objPHPExcel->getActiveSheet()->setCellValue('C' . ($key + 2), "门禁监测");
  575. break;
  576. case '131'://可燃气体
  577. $objPHPExcel->getActiveSheet()->setCellValue('C' . ($key + 2), "可燃气体");
  578. break;
  579. default:
  580. $objPHPExcel->getActiveSheet()->setCellValue('C' . ($key + 2), "未定义");
  581. break;
  582. }
  583. $objPHPExcel->getActiveSheet()->setCellValue('D' . ($key + 2), $device_data[$key]['unitinfo']);
  584. $objPHPExcel->getActiveSheet()->setCellValue('E' . ($key + 2), $device_data[$key]['device_state']);
  585. $objPHPExcel->getActiveSheet()->setCellValue('F' . ($key + 2), $device_data[$key]['install_time']);
  586. }
  587. }
  588. $filename = '设备列表' . date('ymd', time()) . '.xls';
  589. $objPHPExcel->getActiveSheet()->setTitle('设备列表');
  590. ob_end_clean();
  591. header("Content-Type: application/force-download");
  592. header("Content-Type: application/octet-stream");
  593. header("Content-Type: application/download");
  594. header('Content-Disposition:inline;filename="' . $filename . '"');
  595. $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  596. $objWriter->save('php://output');
  597. exit;
  598. }
  599. public function getceshi()
  600. {
  601. $arr = ["devcie" => ['aaa', 'bbb']];
  602. echo json_encode($arr);
  603. }
  604. }