RepairStatistics.php 42 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079
  1. <?php
  2. namespace app\admin\controller;
  3. use think\App;
  4. use think\Db;
  5. class RepairStatistics extends Auth
  6. {
  7. public function addrType(){
  8. $start = input('start',date('Y-m'));
  9. $end = input('end',date('Y-m'));
  10. if($start > $end){
  11. $this->error('开始时间不能大于结束时间');
  12. }
  13. $map[] = ['o.create_yyyymm','>=',date('Ym',strtotime($start))];
  14. $map[] = ['o.create_yyyymm','<=',date('Ym',strtotime($end))];
  15. $typeIds = input('typeIds');
  16. if($typeIds !=''){
  17. $where[] = ['id','in',$typeIds];
  18. }
  19. $mode = [
  20. ['type'=>1,'title'=>'需求数','value'=>[1]],
  21. ['type'=>2,'title'=>'已派工','value'=>[4]],
  22. ['type'=>3,'title'=>'已完成','value'=>[5,6]],
  23. ['type'=>4,'title'=>'已取消','value'=>[2,3]],
  24. ];
  25. $addr = Db::name('address')
  26. ->field('id,title')
  27. ->where('org_id',$this->orgId)
  28. ->where('del',0)
  29. ->where('enable',1)
  30. ->where('','exp',Db::raw("FIND_IN_SET(1,types)"))
  31. ->select();
  32. $where[] = ['org_id','=',$this->orgId];
  33. $type = Db::name('order_type')
  34. ->where($where)
  35. ->where('parent_id',0)
  36. ->where('del',0)
  37. ->where('enable',1)
  38. ->select();
  39. $type = $type?$type:[];
  40. $lists = [];
  41. foreach ($addr as $k=>$v){
  42. $a = [
  43. 'title' => $v['title'],
  44. 'total' => '',
  45. 'status' => '',
  46. ];
  47. foreach ($type as $key=>$val){
  48. $a['type'.$val['id']] = '';
  49. }
  50. $lists[] = $a;
  51. foreach ($mode as $kk=>$vv){
  52. $b = [
  53. 'title' => '',
  54. 'status' => $vv['title'],
  55. ];
  56. $total = 0;
  57. foreach ($type as $key=>$val){
  58. $subTypeId = Db::name('order_type')
  59. ->where('parent_id',$val['id'])
  60. ->where('del',0)
  61. ->where('enable',1)
  62. ->where('org_id',$this->orgId)
  63. ->column('id');
  64. $count = Db::name('orders')
  65. ->alias('o')
  66. ->join('order_repair or','or.order_id=o.id')
  67. ->where('o.work_type_mode',1)
  68. ->where('o.del',0)
  69. ->where($map)
  70. ->whereIn('o.order_mode',$vv['value'])
  71. ->where('or.address_id',$v['id'])
  72. ->whereIn('or.type_id',$subTypeId)
  73. ->count();
  74. $total += $count;
  75. $b['type'.$val['id']] = $count;
  76. }
  77. $b['total'] = $total;
  78. $lists[] = $b;
  79. }
  80. }
  81. $this->assign('lists',$lists);
  82. $this->assign('addr',$addr);
  83. $this->assign('type',$type);
  84. $typeList = Db::name('order_type')
  85. ->where('org_id',$this->orgId)
  86. ->where('parent_id',0)
  87. ->where('del',0)
  88. ->where('enable',1)
  89. ->select();
  90. $this->assign('typeList',$typeList);
  91. $this->assign('start',$start);
  92. $this->assign('end',$end);
  93. $this->assign('typeIds',$typeIds);
  94. $this->assign('typeIds2',!empty($typeIds)?explode(',',$typeIds):[]);
  95. return $this->fetch();
  96. }
  97. public function addrTypeExport(){
  98. $start = input('start',date('Y-m'));
  99. $end = input('end',date('Y-m'));
  100. if($start > $end){
  101. $this->error('开始时间不能大于结束时间');
  102. }
  103. $map[] = ['o.create_yyyymm','>=',date('Ym',strtotime($start))];
  104. $map[] = ['o.create_yyyymm','<=',date('Ym',strtotime($end))];
  105. $typeIds = input('typeIds','','trim');
  106. if($typeIds !=''){
  107. $where[] = ['id','in',$typeIds];
  108. }
  109. $mode = [
  110. ['type'=>1,'title'=>'需求数','value'=>[1]],
  111. ['type'=>2,'title'=>'已派工','value'=>[4]],
  112. ['type'=>3,'title'=>'已完成','value'=>[5,6]],
  113. ['type'=>4,'title'=>'已取消','value'=>[2,3]],
  114. ];
  115. $addr = Db::name('address')
  116. ->field('id,title')
  117. ->where('org_id',$this->orgId)
  118. ->where('del',0)
  119. ->where('enable',1)
  120. ->where('','exp',Db::raw("FIND_IN_SET(1,types)"))
  121. ->select();
  122. $where[] = ['org_id','=',$this->orgId];
  123. $type = Db::name('order_type')
  124. ->where($where)
  125. ->where('parent_id',0)
  126. ->where('del',0)
  127. ->where('enable',1)
  128. ->select();
  129. $type = $type?$type:[];
  130. $lists = [];
  131. foreach ($addr as $k=>$v){
  132. $a = [
  133. 'title' => $v['title'],
  134. 'total' => '',
  135. ];
  136. foreach ($type as $key=>$val){
  137. $a['type'.$val['id']] = '';
  138. }
  139. $lists[] = $a;
  140. foreach ($mode as $kk=>$vv){
  141. $b = [
  142. 'title' => $vv['title'],
  143. ];
  144. $total = 0;
  145. foreach ($type as $key=>$val){
  146. $subTypeId = Db::name('order_type')
  147. ->where('parent_id',$val['id'])
  148. ->where('del',0)
  149. ->where('enable',1)
  150. ->where('org_id',$this->orgId)
  151. ->column('id');
  152. $count = Db::name('orders')
  153. ->alias('o')
  154. ->join('order_repair or','or.order_id=o.id')
  155. ->where('o.work_type_mode',1)
  156. ->where('o.del',0)
  157. ->where($map)
  158. ->whereIn('o.order_mode',$vv['value'])
  159. ->where('or.address_id',$v['id'])
  160. ->whereIn('or.type_id',$subTypeId)
  161. ->count();
  162. $total += $count;
  163. $b['type'.$val['id']] = $count;
  164. }
  165. $b['total'] = $total;
  166. $lists[] = $b;
  167. }
  168. }
  169. include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
  170. //实例化PHPExcel类
  171. $objPHPExcel =new \PHPExcel();
  172. //激活当前的sheet表
  173. $objPHPExcel->setActiveSheetIndex(0);
  174. //设置表格头(即excel表格的第一行)
  175. $sheet = $objPHPExcel->setActiveSheetIndex(0);
  176. $arr = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T');
  177. foreach ($arr as $k=>$v) {
  178. $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setWrapText(false);//换行
  179. $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getFont()->setBold(true);
  180. $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  181. $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
  182. $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  183. $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
  184. $objPHPExcel->getActiveSheet()->getColumnDimension($v)->setWidth(18);
  185. }
  186. $sheet->setCellValueByColumnAndRow(0, 1, '地点');
  187. if($type){
  188. foreach ($type as $k=>$v){
  189. $sheet->setCellValueByColumnAndRow($k+1, 1, $v['title']);
  190. }
  191. }
  192. $sheet->setCellValueByColumnAndRow(count($type)+1, 1, '总数');
  193. foreach ($lists as $k=>$v) {
  194. $sheet->setCellValueByColumnAndRow(0, $k + 2, $v['title']);
  195. foreach ($type as $kk=>$vv){
  196. $sheet->setCellValueByColumnAndRow($kk+1, $k + 2, $v['type'.$vv['id']]);
  197. }
  198. $sheet->setCellValueByColumnAndRow(count($type)+1, $k + 2, $v['total']);
  199. }
  200. //设置保存的Excel表格名称
  201. $filename = '报修地点报表_' . date('YmdHis', time()) . '.xls';
  202. //设置当前激活的sheet表格名称
  203. $objPHPExcel->getActiveSheet()->setTitle('报修地点报表');
  204. //设置浏览器窗口下载表格
  205. ob_end_clean();
  206. header("Content-Type: application/force-download");
  207. header("Content-Type: application/octet-stream");
  208. header("Content-Type: application/download");
  209. header('Content-Disposition:inline;filename="' . $filename);
  210. //生成excel文件
  211. $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  212. //下载文件在浏览器窗口
  213. return $objWriter->save('php://output');
  214. }
  215. public function todoXyTime(){
  216. $list = Db::name('todo')
  217. ->where('work_type_mode',1)
  218. ->where('del',0)
  219. ->where('todo_mode','in',[2,3])
  220. ->where('org_id',$this->orgId)
  221. ->order('id desc')
  222. ->select();
  223. $count = Db::name('todo')
  224. ->where('work_type_mode',1)
  225. ->where('del',0)
  226. ->where('todo_mode','in',[2,3])
  227. ->where('org_id',$this->orgId)
  228. ->order('id desc')
  229. ->sum('xy_time');
  230. foreach ($list as $k=>$v){
  231. $subType = Db::name('order_type')
  232. ->alias('ot')
  233. ->join('order_repair or','or.type_id=ot.id')
  234. ->where('or.order_id',$v['order_id'])
  235. ->value('type_id');
  236. $parentId = Db::name('order_type')->where('id',$subType)->value('parent_id');
  237. $list[$k]['type_title'] = Db::name('order_type')->where('id',$parentId)->value('title');
  238. $list[$k]['content'] = Db::name('orders')->where('id',$v['order_id'])->value('content');
  239. $list[$k]['dep'] = Db::name('orders')->alias('o')->join('dep d','d.id=o.dep_id')->where('o.id',$v['order_id'])->value('d.title');
  240. $list[$k]['user_name'] = Db::name('user')->where('id',$v['to_user_id'])->value('real_name');
  241. $list[$k]['xy_time'] = round($v['xy_time']/60,1);
  242. }
  243. $this->assign('count',round($count/60,1));
  244. $this->assign('list',$list);
  245. return $this->fetch();
  246. }
  247. public function xyTimeEcharts(){
  248. $curTime = date('Y-m-d H:i:s');
  249. $time = [
  250. ['title'=>'24小时内','sTime'=>date('Y-m-d H:i:s',strtotime($curTime)-3600*24),'eTime'=>$curTime,'type'=>1],
  251. ['title'=>'24~48小时','sTime'=>date('Y-m-d H:i:s',strtotime($curTime)-3600*48),'eTime'=>date('Y-m-d H:i:s',strtotime($curTime)-3600*24),'type'=>1],
  252. ['title'=>'超过72小时','sTime'=>'','eTime'=>date('Y-m-d H:i:s',strtotime($curTime)-3600*72),'type'=>2],
  253. ['title'=>'未响应','sTime'=>'','eTime'=>'','type'=>3],
  254. ['title'=>'总数','sTime'=>'','eTime'=>'','type'=>4],
  255. ];
  256. $list = [];
  257. foreach ($time as $k=>$v){
  258. if($v['type'] == 1){
  259. $count = Db::name('todo')
  260. ->where('org_id',$this->orgId)
  261. ->where('work_type_mode',1)
  262. ->where('del',0)
  263. ->where('create_time','>',$v['sTime'])
  264. ->where('create_time','<=',$v['eTime'])
  265. ->where('todo_mode','in',[2,3])
  266. ->count();
  267. }elseif ($v['type'] == 2){
  268. $count = Db::name('todo')
  269. ->where('org_id',$this->orgId)
  270. ->where('work_type_mode',1)
  271. ->where('del',0)
  272. ->where('create_time','<',$v['eTime'])
  273. ->where('todo_mode','in',[2,3])
  274. ->count();
  275. }elseif ($v['type'] == 3){
  276. $count = Db::name('todo')
  277. ->where('org_id',$this->orgId)
  278. ->where('work_type_mode',1)
  279. ->where('del',0)
  280. ->where('todo_mode',1)
  281. ->count();
  282. }elseif($v['type'] == 4){
  283. $count = Db::name('todo')
  284. ->where('org_id',$this->orgId)
  285. ->where('work_type_mode',1)
  286. ->where('del',0)
  287. ->count();
  288. }
  289. $list[$k]['title'] = $v['title'];
  290. $list[$k]['value'] = $count;
  291. }
  292. $data = [
  293. 'title'=>array_column($time,'title'),
  294. 'list'=>$list,
  295. ];
  296. $this->success('','',$data);
  297. }
  298. public function xyTimeExport(){
  299. $list = Db::name('todo')
  300. ->where('work_type_mode',1)
  301. ->where('del',0)
  302. ->where('todo_mode','in',[2,3])
  303. ->where('org_id',$this->orgId)
  304. ->order('id desc')
  305. ->select();
  306. $count = Db::name('todo')
  307. ->where('work_type_mode',1)
  308. ->where('del',0)
  309. ->where('todo_mode','in',[2,3])
  310. ->where('org_id',$this->orgId)
  311. ->order('id desc')
  312. ->sum('xy_time');
  313. foreach ($list as $k=>$v){
  314. $subType = Db::name('order_type')
  315. ->alias('ot')
  316. ->join('order_repair or','or.type_id=ot.id')
  317. ->where('or.order_id',$v['order_id'])
  318. ->value('type_id');
  319. $parentId = Db::name('order_type')->where('id',$subType)->value('parent_id');
  320. $list[$k]['type_title'] = Db::name('order_type')->where('id',$parentId)->value('title');
  321. $list[$k]['content'] = Db::name('orders')->where('id',$v['order_id'])->value('content');
  322. $list[$k]['dep'] = Db::name('orders')->alias('o')->join('dep d','d.id=o.dep_id')->where('o.id',$v['order_id'])->value('d.title');
  323. $list[$k]['user_name'] = Db::name('user')->where('id',$v['to_user_id'])->value('real_name');
  324. $list[$k]['xy_time'] = round($v['xy_time']/60,1);
  325. }
  326. include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
  327. //实例化PHPExcel类
  328. $objPHPExcel =new \PHPExcel();
  329. //激活当前的sheet表
  330. $objPHPExcel->setActiveSheetIndex(0);
  331. //设置表格头(即excel表格的第一行)
  332. $sheet = $objPHPExcel->setActiveSheetIndex(0);
  333. $arr = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N');
  334. foreach ($arr as $k=>$v) {
  335. $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setWrapText(false);//换行
  336. $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getFont()->setBold(true);
  337. $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  338. $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
  339. $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  340. $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
  341. $objPHPExcel->getActiveSheet()->getColumnDimension($v)->setWidth(18);
  342. }
  343. $sheet->setCellValueByColumnAndRow(0, 1, '类型');
  344. $sheet->setCellValueByColumnAndRow(1, 1, '部门');
  345. $sheet->setCellValueByColumnAndRow(2, 1, '员工');
  346. $sheet->setCellValueByColumnAndRow(3, 1, '需求描述');
  347. $sheet->setCellValueByColumnAndRow(4, 1, '状态');
  348. $sheet->setCellValueByColumnAndRow(5, 1, '响应时间');
  349. $sheet->setCellValueByColumnAndRow(6, 1, '合计');
  350. foreach ($list as $k=>$v) {
  351. $sheet->setCellValueByColumnAndRow(0, $k + 2, $v['type_title']);
  352. $sheet->setCellValueByColumnAndRow(1, $k + 2, $v['dep']);
  353. $sheet->setCellValueByColumnAndRow(2, $k + 2, $v['user_name']);
  354. $sheet->setCellValueByColumnAndRow(3, $k + 2, $v['content']);
  355. if($v['todo_mode'] == 2){
  356. $sheet->setCellValueByColumnAndRow(4, $k + 2, '进行中');
  357. }
  358. $sheet->setCellValueByColumnAndRow(5, $k + 2, $v['xy_time']);
  359. }
  360. $sheet->setCellValueByColumnAndRow(6, $k + 2, round($count/60,1));
  361. //设置保存的Excel表格名称
  362. $filename = '报修工单响应时间_' . date('YmdHis', time()) . '.xls';
  363. //设置当前激活的sheet表格名称
  364. $objPHPExcel->getActiveSheet()->setTitle('报修工单响应时间');
  365. //设置浏览器窗口下载表格
  366. ob_end_clean();
  367. header("Content-Type: application/force-download");
  368. header("Content-Type: application/octet-stream");
  369. header("Content-Type: application/download");
  370. header('Content-Disposition:inline;filename="' . $filename);
  371. //生成excel文件
  372. $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  373. //下载文件在浏览器窗口
  374. return $objWriter->save('php://output');
  375. }
  376. public function addrWorkHour(){
  377. $start = input('start',date('Y-m'));
  378. $end = input('end',date('Y-m'));
  379. if($start > $end){
  380. $this->error('开始时间不能大于结束时间');
  381. }
  382. $map[] = ['t.create_yyyymm','>=',date('Ym',strtotime($start))];
  383. $map[] = ['t.create_yyyymm','<=',date('Ym',strtotime($end))];
  384. $addrIds= input('addrIds','','trim');
  385. if($addrIds !=''){
  386. $where[] = ['id','in',$addrIds];
  387. }
  388. $where[] = ['org_id','=',$this->orgId];
  389. $addr = Db::name('address')
  390. ->field('id,title')
  391. ->where($where)
  392. ->where('org_id',$this->orgId)
  393. ->where('del',0)
  394. ->where('enable',1)
  395. ->where('','exp',Db::raw("FIND_IN_SET(1,types)"))
  396. ->select();
  397. $addr = $addr?$addr:[];
  398. $list = [];
  399. $total = $total2 = 0;
  400. foreach ($addr as $k=>$v){
  401. $count = Db::name('todo')
  402. ->alias('t')
  403. ->join('order_repair or','or.order_id=t.order_id')
  404. ->where('or.address_id',$v['id'])
  405. ->where($map)
  406. ->where('t.del',0)
  407. ->where('t.work_type_mode',1)
  408. ->where('t.org_id',$this->orgId)
  409. ->where('t.todo_mode',3)
  410. ->count();
  411. $total +=$count;
  412. $count2 = Db::name('todo')
  413. ->alias('t')
  414. ->join('order_repair or','or.order_id=t.order_id')
  415. ->where('or.address_id',$v['id'])
  416. ->where($map)
  417. ->where('t.del',0)
  418. ->where('t.work_type_mode',1)
  419. ->where('t.org_id',$this->orgId)
  420. ->where('t.todo_mode',3)
  421. ->sum('wc_time');
  422. $total2 +=$count2;
  423. $list[$k]['title'] = $v['title'];
  424. $list[$k]['count'] = $count;
  425. $list[$k]['count2'] = round($count2/60,1);
  426. }
  427. $this->assign('list',$list);
  428. $this->assign('total',$total);
  429. $this->assign('total2',round($total2/60,1));
  430. $addrList = Db::name('address')
  431. ->field('id,title')
  432. ->where('org_id',$this->orgId)
  433. ->where('del',0)
  434. ->where('enable',1)
  435. ->where('','exp',Db::raw("FIND_IN_SET(1,types)"))
  436. ->select();
  437. $this->assign('addrList',$addrList);
  438. $this->assign('start',$start);
  439. $this->assign('end',$end);
  440. $this->assign('addrIds',$addrIds);
  441. $this->assign('addrIds2',!empty($addrIds)?explode(',',$addrIds):[]);
  442. return $this->fetch();
  443. }
  444. public function addrWorkHourExport(){
  445. $start = input('start',date('Y-m'));
  446. $end = input('end',date('Y-m'));
  447. if($start > $end){
  448. $this->error('开始时间不能大于结束时间');
  449. }
  450. $map[] = ['t.create_yyyymm','>=',date('Ym',strtotime($start))];
  451. $map[] = ['t.create_yyyymm','<=',date('Ym',strtotime($end))];
  452. $addrIds= input('addrIds','','trim');
  453. if($addrIds !=''){
  454. $where[] = ['id','in',$addrIds];
  455. }
  456. $where[] = ['org_id','=',$this->orgId];
  457. $addr = Db::name('address')
  458. ->field('id,title')
  459. ->where($where)
  460. ->where('org_id',$this->orgId)
  461. ->where('del',0)
  462. ->where('enable',1)
  463. ->where('','exp',Db::raw("FIND_IN_SET(1,types)"))
  464. ->select();
  465. $addr = $addr?$addr:[];
  466. $list = [];
  467. $total = $total2 = 0;
  468. foreach ($addr as $k=>$v){
  469. $count = Db::name('todo')
  470. ->alias('t')
  471. ->join('order_repair or','or.order_id=t.order_id')
  472. ->where('or.address_id',$v['id'])
  473. ->where($map)
  474. ->where('t.del',0)
  475. ->where('t.work_type_mode',1)
  476. ->where('t.org_id',$this->orgId)
  477. ->where('t.todo_mode',3)
  478. ->count();
  479. $total +=$count;
  480. $count2 = Db::name('todo')
  481. ->alias('t')
  482. ->join('order_repair or','or.order_id=t.order_id')
  483. ->where('or.address_id',$v['id'])
  484. ->where($map)
  485. ->where('t.del',0)
  486. ->where('t.work_type_mode',1)
  487. ->where('t.org_id',$this->orgId)
  488. ->where('t.todo_mode',3)
  489. ->sum('wc_time');
  490. $total2 +=$count2;
  491. $list[$k]['title'] = $v['title'];
  492. $list[$k]['count'] = $count;
  493. $list[$k]['count2'] = round($count2/60,1);
  494. }
  495. include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
  496. //实例化PHPExcel类
  497. $objPHPExcel =new \PHPExcel();
  498. //激活当前的sheet表
  499. $objPHPExcel->setActiveSheetIndex(0);
  500. //设置表格头(即excel表格的第一行)
  501. $sheet = $objPHPExcel->setActiveSheetIndex(0);
  502. $arr = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N');
  503. foreach ($arr as $k=>$v) {
  504. $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setWrapText(false);//换行
  505. $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getFont()->setBold(true);
  506. $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  507. $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
  508. $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  509. $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
  510. $objPHPExcel->getActiveSheet()->getColumnDimension($v)->setWidth(18);
  511. }
  512. $sheet->setCellValueByColumnAndRow(0, 1, '地点');
  513. $sheet->setCellValueByColumnAndRow(1, 1, '工单数');
  514. $sheet->setCellValueByColumnAndRow(2, 1, '所需工时');
  515. foreach ($list as $k=>$v) {
  516. $sheet->setCellValueByColumnAndRow(0, $k + 2, $v['title']);
  517. $sheet->setCellValueByColumnAndRow(1, $k + 2, $v['count']);
  518. $sheet->setCellValueByColumnAndRow(2, $k + 2, $v['count2']);
  519. }
  520. $sheet->setCellValueByColumnAndRow(0, count($list)+3, '合计');
  521. $sheet->setCellValueByColumnAndRow(1, count($list)+3, '总工单数');
  522. $sheet->setCellValueByColumnAndRow(2, count($list)+3, $total);
  523. $sheet->setCellValueByColumnAndRow(1, count($list)+4, '总工时');
  524. $sheet->setCellValueByColumnAndRow(2, count($list)+4, round($total2/60,1));
  525. $sheet->setCellValueByColumnAndRow(1, count($list)+5, '平均工时');
  526. if($total > 0){
  527. $sheet->setCellValueByColumnAndRow(2, count($list)+5, round($total2/60/$total,1));
  528. }else{
  529. $sheet->setCellValueByColumnAndRow(2, count($list)+5, 0);
  530. }
  531. //设置保存的Excel表格名称
  532. $filename = '报修地点工时报表_' . date('YmdHis', time()) . '.xls';
  533. //设置当前激活的sheet表格名称
  534. $objPHPExcel->getActiveSheet()->setTitle('报修地点工时报表');
  535. //设置浏览器窗口下载表格
  536. ob_end_clean();
  537. header("Content-Type: application/force-download");
  538. header("Content-Type: application/octet-stream");
  539. header("Content-Type: application/download");
  540. header('Content-Disposition:inline;filename="' . $filename);
  541. //生成excel文件
  542. $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  543. //下载文件在浏览器窗口
  544. return $objWriter->save('php://output');
  545. }
  546. public function typeWorkHour(){
  547. $start = input('start',date('Y-m'));
  548. $end = input('end',date('Y-m'));
  549. if($start > $end){
  550. $this->error('开始时间不能大于结束时间');
  551. }
  552. $map[] = ['t.create_yyyymm','>=',date('Ym',strtotime($start))];
  553. $map[] = ['t.create_yyyymm','<=',date('Ym',strtotime($end))];
  554. $typeIds = input('typeIds');
  555. if($typeIds !=''){
  556. $where[] = ['id','in',$typeIds];
  557. }
  558. $addr = Db::name('address')
  559. ->field('id,title')
  560. ->where('org_id',$this->orgId)
  561. ->where('del',0)
  562. ->where('enable',1)
  563. ->where('','exp',Db::raw("FIND_IN_SET(1,types)"))
  564. ->select();
  565. $where[] = ['org_id','=',$this->orgId];
  566. $type = Db::name('order_type')
  567. ->where($where)
  568. ->where('parent_id',0)
  569. ->where('del',0)
  570. ->where('enable',1)
  571. ->select();
  572. $type = $type?$type:[];
  573. $newType = [];
  574. foreach ($type as $k=>$v){
  575. $a = [
  576. 'title'=>$v['title'],
  577. 'id'=>$v['id'],
  578. 'type'=>1
  579. ];
  580. $newType[]= $a;
  581. $b= [
  582. 'title'=>$v['title'].'工时',
  583. 'id'=>$v['id'],
  584. 'type'=>2
  585. ];
  586. $newType[]= $b;
  587. }
  588. $this->assign('newType',$newType);
  589. $lists = [];
  590. $total = $total2 = 0 ;
  591. foreach ($addr as $k=>$v){
  592. $a = [
  593. 'title' => $v['title'],
  594. ];
  595. foreach ($newType as $key=>$val){
  596. $subTypeId = Db::name('order_type')
  597. ->where('parent_id',$val['id'])
  598. ->where('del',0)
  599. ->where('enable',1)
  600. ->where('org_id',$this->orgId)
  601. ->column('id');
  602. if($val['type']==1){
  603. $count = Db::name('todo')
  604. ->alias('t')
  605. ->join('order_repair or','or.order_id=t.order_id')
  606. ->where('t.work_type_mode',1)
  607. ->where('t.del',0)
  608. ->where($map)
  609. ->where('t.todo_mode',3)
  610. ->where('or.address_id',$v['id'])
  611. ->whereIn('or.type_id',$subTypeId)
  612. ->count();
  613. $total +=$count;
  614. }elseif ($val['type']==2){
  615. $wx_time = Db::name('todo')
  616. ->alias('t')
  617. ->join('order_repair or','or.order_id=t.order_id')
  618. ->where('t.work_type_mode',1)
  619. ->where('t.del',0)
  620. ->where($map)
  621. ->where('t.todo_mode',3)
  622. ->where('or.address_id',$v['id'])
  623. ->whereIn('or.type_id',$subTypeId)
  624. ->sum('wc_time');
  625. $count = round($wx_time/60,1);
  626. $total2 +=$count;
  627. }
  628. $a['type'.$key] = $count;
  629. }
  630. $lists[] = $a;
  631. }
  632. $this->assign('lists',$lists);
  633. $this->assign('total',$total);
  634. $this->assign('total2',$total2);
  635. $this->assign('addr',$addr);
  636. $this->assign('type',$type);
  637. $typeList = Db::name('order_type')
  638. ->where('org_id',$this->orgId)
  639. ->where('parent_id',0)
  640. ->where('del',0)
  641. ->where('enable',1)
  642. ->select();
  643. $this->assign('typeList',$typeList);
  644. $this->assign('start',$start);
  645. $this->assign('end',$end);
  646. $this->assign('typeIds',$typeIds);
  647. $this->assign('typeIds2',!empty($typeIds)?explode(',',$typeIds):[]);
  648. return $this->fetch();
  649. }
  650. public function typeWorkHourExport(){
  651. $start = input('start',date('Y-m'));
  652. $end = input('end',date('Y-m'));
  653. if($start > $end){
  654. $this->error('开始时间不能大于结束时间');
  655. }
  656. $map[] = ['t.create_yyyymm','>=',date('Ym',strtotime($start))];
  657. $map[] = ['t.create_yyyymm','<=',date('Ym',strtotime($end))];
  658. $typeIds = input('typeIds');
  659. if($typeIds !=''){
  660. $where[] = ['id','in',$typeIds];
  661. }
  662. $addr = Db::name('address')
  663. ->field('id,title')
  664. ->where('org_id',$this->orgId)
  665. ->where('del',0)
  666. ->where('enable',1)
  667. ->where('','exp',Db::raw("FIND_IN_SET(1,types)"))
  668. ->select();
  669. $where[] = ['org_id','=',$this->orgId];
  670. $type = Db::name('order_type')
  671. ->where($where)
  672. ->where('parent_id',0)
  673. ->where('del',0)
  674. ->where('enable',1)
  675. ->select();
  676. $type = $type?$type:[];
  677. $newType = [];
  678. foreach ($type as $k=>$v){
  679. $a = [
  680. 'title'=>$v['title'],
  681. 'id'=>$v['id'],
  682. 'type'=>1
  683. ];
  684. $newType[]= $a;
  685. $b= [
  686. 'title'=>$v['title'].'工时',
  687. 'id'=>$v['id'],
  688. 'type'=>2
  689. ];
  690. $newType[]= $b;
  691. }
  692. $this->assign('newType',$newType);
  693. $lists = [];
  694. $total = $total2 = 0 ;
  695. foreach ($addr as $k=>$v){
  696. $a = [
  697. 'title' => $v['title'],
  698. ];
  699. foreach ($newType as $key=>$val){
  700. $subTypeId = Db::name('order_type')
  701. ->where('parent_id',$val['id'])
  702. ->where('del',0)
  703. ->where('enable',1)
  704. ->where('org_id',$this->orgId)
  705. ->column('id');
  706. if($val['type']==1){
  707. $count = Db::name('todo')
  708. ->alias('t')
  709. ->join('order_repair or','or.order_id=t.order_id')
  710. ->where('t.work_type_mode',1)
  711. ->where('t.del',0)
  712. ->where($map)
  713. ->where('t.todo_mode',3)
  714. ->where('or.address_id',$v['id'])
  715. ->whereIn('or.type_id',$subTypeId)
  716. ->count();
  717. $total +=$count;
  718. }elseif ($val['type']==2){
  719. $wx_time = Db::name('todo')
  720. ->alias('t')
  721. ->join('order_repair or','or.order_id=t.order_id')
  722. ->where('t.work_type_mode',1)
  723. ->where('t.del',0)
  724. ->where($map)
  725. ->where('t.todo_mode',3)
  726. ->where('or.address_id',$v['id'])
  727. ->whereIn('or.type_id',$subTypeId)
  728. ->sum('wc_time');
  729. $count = round($wx_time/60,1);
  730. $total2 +=$count;
  731. }
  732. $a['type'.$key] = $count;
  733. }
  734. $lists[] = $a;
  735. }
  736. include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
  737. //实例化PHPExcel类
  738. $objPHPExcel =new \PHPExcel();
  739. //激活当前的sheet表
  740. $objPHPExcel->setActiveSheetIndex(0);
  741. //设置表格头(即excel表格的第一行)
  742. $sheet = $objPHPExcel->setActiveSheetIndex(0);
  743. $arr = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T');
  744. foreach ($arr as $k=>$v) {
  745. $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setWrapText(false);//换行
  746. $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getFont()->setBold(true);
  747. $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  748. $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
  749. $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  750. $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
  751. $objPHPExcel->getActiveSheet()->getColumnDimension($v)->setWidth(18);
  752. }
  753. $sheet->setCellValueByColumnAndRow(0, 1, '地点');
  754. foreach ($newType as $k=>$v){
  755. $sheet->setCellValueByColumnAndRow($k+1, 1, $v['title']);
  756. }
  757. foreach ($lists as $k=>$v) {
  758. $sheet->setCellValueByColumnAndRow(0, $k + 2, $v['title']);
  759. foreach ($newType as $kk=>$vv){
  760. $sheet->setCellValueByColumnAndRow($kk+1, $k + 2, $v['type'.$kk]);
  761. }
  762. }
  763. $sheet->setCellValueByColumnAndRow(0, count($lists)+3, '合计');
  764. $sheet->setCellValueByColumnAndRow(1, count($lists)+3, '总工单数');
  765. $sheet->setCellValueByColumnAndRow(2, count($lists)+3, $total);
  766. $sheet->setCellValueByColumnAndRow(1, count($lists)+4, '总工时');
  767. $sheet->setCellValueByColumnAndRow(2, count($lists)+4, $total2);
  768. $sheet->setCellValueByColumnAndRow(1, count($lists)+5, '平均工时');
  769. if($total > 0){
  770. $sheet->setCellValueByColumnAndRow(2, count($lists)+5, $total2/$total);
  771. }else{
  772. $sheet->setCellValueByColumnAndRow(2, count($lists)+5, 0);
  773. }
  774. //设置保存的Excel表格名称
  775. $filename = '报修类型工时报表_' . date('YmdHis', time()) . '.xls';
  776. //设置当前激活的sheet表格名称
  777. $objPHPExcel->getActiveSheet()->setTitle('报修类型工时报表');
  778. //设置浏览器窗口下载表格
  779. ob_end_clean();
  780. header("Content-Type: application/force-download");
  781. header("Content-Type: application/octet-stream");
  782. header("Content-Type: application/download");
  783. header('Content-Disposition:inline;filename="' . $filename);
  784. //生成excel文件
  785. $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  786. //下载文件在浏览器窗口
  787. return $objWriter->save('php://output');
  788. }
  789. public function consumables(){
  790. $month = input('month',date('Y-m'));
  791. // $curYear = date('Y',strtotime($month));
  792. // $curMonth = date('m',strtotime($month));
  793. $map[] = ['create_yyyymm','=',date('Ym',strtotime($month))];
  794. $goodsIds = input('goodsIds');
  795. if($goodsIds !=''){
  796. $where[] = ['id','in',$goodsIds];
  797. }
  798. $where[] = ['org_id','=',$this->orgId];
  799. $where[] = ['del','=',0];
  800. $where[] = ['enable','=',1];
  801. $mate = Db::name('mate_goods')
  802. ->field('id,title')
  803. ->where($where)
  804. ->select();
  805. $this->assign('mate',$mate);
  806. //获取某年某个月的总天数
  807. // $t = cal_days_in_month(CAL_GREGORIAN,$curMonth,$curYear);
  808. $t = date('t',strtotime($month));
  809. $sDay = strtotime($month.'-01');
  810. $arr = [];
  811. for ($i = 0;$i<$t;$i++){
  812. $arr[$i]['date'] = date('m-d',$sDay+$i*86400);
  813. $arr[$i]['ymd'] = date('Y-m-d',$sDay+$i*86400);
  814. }
  815. $lists = [];
  816. foreach ($arr as $k=>$v){
  817. $a = [
  818. 'title' => $v['date'],
  819. ];
  820. foreach ($mate as $key=>$val){
  821. $count = Db::name('todo_mate_item')
  822. ->where('items_id',$val['id'])
  823. ->where('create_time','>=',$v['ymd'].' 00:00:00')
  824. ->where('create_time','<=',$v['ymd'].' 23:59:59')
  825. ->sum('total');
  826. $a['type'.$val['id']] = $count;
  827. }
  828. $lists[] = $a;
  829. }
  830. $subtotal = []; $total = 0 ;
  831. foreach ($mate as $k=>$v){
  832. $c = Db::name('todo_mate_item')
  833. ->where('items_id',$v['id'])
  834. ->where('create_time','>=',$month.'-01 00:00:00')
  835. ->where('create_time','<=',$month.'-31 23:59:59')
  836. ->sum('total');
  837. $total +=$c;
  838. $subtotal[$k] = $c;
  839. }
  840. $this->assign('lists',$lists);
  841. $this->assign('subtotal',$subtotal);
  842. $this->assign('total',$total);
  843. $mateGoods = Db::name('mate_goods')
  844. ->field('id,title')
  845. ->where('org_id',$this->orgId)
  846. ->where('del',0)
  847. ->where('enable',1)
  848. ->select();
  849. $this->assign('mateGoods',$mateGoods);
  850. $this->assign('month',$month);
  851. $this->assign('goodsIds',$goodsIds);
  852. $this->assign('goodsIds2',!empty($goodsIds)?explode(',',$goodsIds):[]);
  853. return $this->fetch();
  854. }
  855. public function consumablesExport(){
  856. $month = input('month',date('Y-m'));
  857. // $curYear = date('Y',strtotime($month));
  858. // $curMonth = date('m',strtotime($month));
  859. $map[] = ['create_yyyymm','=',date('Ym',strtotime($month))];
  860. $goodsIds = input('goodsIds');
  861. if($goodsIds !=''){
  862. $where[] = ['id','in',$goodsIds];
  863. }
  864. $where[] = ['org_id','=',$this->orgId];
  865. $where[] = ['del','=',0];
  866. $where[] = ['enable','=',1];
  867. $mate = Db::name('mate_goods')
  868. ->field('id,title')
  869. ->where($where)
  870. ->select();
  871. // $this->assign('mate',$mate);
  872. //获取某年某个月的总天数
  873. // $t = cal_days_in_month(CAL_GREGORIAN,$curMonth,$curYear);
  874. $t = date('t',strtotime($month));
  875. $sDay = strtotime($month.'-01');
  876. $arr = [];
  877. for ($i = 0;$i<$t;$i++){
  878. $arr[$i]['date'] = date('m-d',$sDay+$i*86400);
  879. $arr[$i]['ymd'] = date('Y-m-d',$sDay+$i*86400);
  880. }
  881. $lists = [];
  882. foreach ($arr as $k=>$v){
  883. $a = [
  884. 'title' => $v['date'],
  885. ];
  886. foreach ($mate as $key=>$val){
  887. $count = Db::name('todo_mate_item')
  888. ->where('items_id',$val['id'])
  889. ->where('create_time','>=',$v['ymd'].' 00:00:00')
  890. ->where('create_time','<=',$v['ymd'].' 23:59:59')
  891. ->sum('total');
  892. $a['type'.$val['id']] = $count;
  893. }
  894. $lists[] = $a;
  895. }
  896. $subtotal = []; $total = 0 ;
  897. foreach ($mate as $k=>$v){
  898. $c = Db::name('todo_mate_item')
  899. ->where('items_id',$v['id'])
  900. ->where('create_time','>=',$month.'-01 00:00:00')
  901. ->where('create_time','<=',$month.'-31 23:59:59')
  902. ->sum('total');
  903. $total +=$c;
  904. $subtotal[$k] = $c;
  905. }
  906. include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
  907. //实例化PHPExcel类
  908. $objPHPExcel =new \PHPExcel();
  909. //激活当前的sheet表
  910. $objPHPExcel->setActiveSheetIndex(0);
  911. //设置表格头(即excel表格的第一行)
  912. $sheet = $objPHPExcel->setActiveSheetIndex(0);
  913. $arr2 = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T');
  914. foreach ($arr2 as $k=>$v) {
  915. $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setWrapText(false);//换行
  916. $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getFont()->setBold(true);
  917. $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  918. $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
  919. $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  920. $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
  921. $objPHPExcel->getActiveSheet()->getColumnDimension($v)->setWidth(18);
  922. }
  923. $sheet->setCellValueByColumnAndRow(0, 1, '日期');
  924. foreach ($mate as $k=>$v){
  925. $sheet->setCellValueByColumnAndRow($k+1, 1, $v['title']);
  926. }
  927. foreach ($lists as $k=>$v) {
  928. $sheet->setCellValueByColumnAndRow(0, $k + 2, $v['title']);
  929. foreach ($mate as $kk=>$vv){
  930. $sheet->setCellValueByColumnAndRow($kk+1, $k + 2, $v['type'.$vv['id']]);
  931. }
  932. }
  933. $sheet->setCellValueByColumnAndRow(0, count($lists)+3, '小计');
  934. foreach ($subtotal as $k=>$v){
  935. $sheet->setCellValueByColumnAndRow($k+1, count($lists)+3, $v);
  936. }
  937. $sheet->setCellValueByColumnAndRow(0, count($lists)+4, '总计');
  938. $sheet->setCellValueByColumnAndRow(1, count($lists)+4, $total);
  939. //设置保存的Excel表格名称
  940. $filename = '报修耗材报表_' . date('YmdHis', time()) . '.xlsx';
  941. //设置当前激活的sheet表格名称
  942. $objPHPExcel->getActiveSheet()->setTitle('报修耗材报表');
  943. //设置浏览器窗口下载表格
  944. ob_end_clean();
  945. header("Content-Type: application/force-download");
  946. header("Content-Type: application/octet-stream");
  947. header("Content-Type: application/download");
  948. header('Content-Disposition:inline;filename="' . $filename);
  949. //生成excel文件
  950. $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
  951. //下载文件在浏览器窗口
  952. return $objWriter->save('php://output');
  953. }
  954. }