0
0

ConveyStatistics.php 9.1 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223
  1. <?php
  2. namespace app\admin\controller;
  3. use think\Db;
  4. use think\Exception;
  5. class ConveyStatistics extends Auth
  6. {
  7. public function urgencyOrder(){
  8. $start = input('start',date('Y-m-d',strtotime('-1 month')));
  9. $end = input('end',date('Y-m-d'));
  10. $rolesId = Db::name('roles')
  11. ->whereIn('parent_id',6)
  12. ->where('del',0)
  13. ->where('type',1)
  14. ->where('enable',1)
  15. ->where('org_id',$this->orgId)
  16. ->column('id');
  17. $user = Db::name('user')
  18. ->alias('u')
  19. ->field('u.*')
  20. ->join('user_roles ur','ur.user_id=u.id')
  21. ->whereIn('ur.roles_id',$rolesId)
  22. ->where('u.del',0)
  23. ->where('u.enable',1)
  24. ->select();
  25. $data = [];
  26. foreach ($user as $k=>$v){
  27. $data[$k]['user_name'] = $v['real_name'];
  28. $count = Db::name('todo')
  29. ->alias('t')
  30. ->join('order_convey oc','oc.order_id=t.order_id')
  31. ->where('t.to_user_id',$v['id'])
  32. ->where('t.org_id',$this->orgId)
  33. ->where('t.del',0)
  34. ->where('t.work_type_mode',3)
  35. ->where('t.todo_mode',3)
  36. ->where('t.create_yyyymmdd','>=',date('Ymd',strtotime($start)))
  37. ->where('t.create_yyyymmdd','<=',date('Ymd',strtotime($end)))
  38. ->where('oc.priority',3)
  39. ->count();
  40. $data[$k]['count'] = $count;
  41. $list = Db::name('todo')
  42. ->alias('t')
  43. ->field('t.id,t.order_id,t.done_time,oc.ywc_time')
  44. ->join('order_convey oc','oc.order_id=t.order_id')
  45. ->where('t.to_user_id',$v['id'])
  46. ->where('t.org_id',$this->orgId)
  47. ->where('t.del',0)
  48. ->where('t.work_type_mode',3)
  49. ->where('t.todo_mode',3)
  50. ->where('t.create_yyyymmdd','>=',date('Ymd',strtotime($start)))
  51. ->where('t.create_yyyymmdd','<=',date('Ymd',strtotime($end)))
  52. ->where('oc.priority',3)
  53. ->select();
  54. $hg = 0;
  55. foreach ($list as $kk=>$vv){
  56. if($vv['done_time'] && $vv['ywc_time'] >= $vv['done_time']){
  57. $hg+=1;
  58. }
  59. }
  60. $data[$k]['hg_num'] = $hg;
  61. $data[$k]['no_hg_num'] = $count -$hg;
  62. if($count > 0){
  63. $bl = round($hg/$count*100,2).'%';
  64. }else{
  65. $bl = '0%';
  66. }
  67. $data[$k]['bl'] = $bl;
  68. }
  69. $this->assign('data',$data);
  70. $this->assign('start',$start);
  71. $this->assign('end',$end);
  72. return $this->fetch();
  73. }
  74. public function urgencyOrderExport(){
  75. $start = input('start',date('Y-m-d',strtotime('-1 month')));
  76. $end = input('end',date('Y-m-d'));
  77. $rolesId = Db::name('roles')
  78. ->whereIn('parent_id',6)
  79. ->where('del',0)
  80. ->where('type',1)
  81. ->where('enable',1)
  82. ->where('org_id',$this->orgId)
  83. ->column('id');
  84. $user = Db::name('user')
  85. ->alias('u')
  86. ->field('u.*')
  87. ->join('user_roles ur','ur.user_id=u.id')
  88. ->whereIn('ur.roles_id',$rolesId)
  89. ->where('u.del',0)
  90. ->where('u.enable',1)
  91. ->select();
  92. $data = [];
  93. foreach ($user as $k=>$v){
  94. $data[$k]['user_name'] = $v['real_name'];
  95. $count = Db::name('todo')
  96. ->alias('t')
  97. ->join('order_convey oc','oc.order_id=t.order_id')
  98. ->where('t.to_user_id',$v['id'])
  99. ->where('t.org_id',$this->orgId)
  100. ->where('t.del',0)
  101. ->where('t.work_type_mode',3)
  102. ->where('t.todo_mode',3)
  103. ->where('t.create_yyyymmdd','>=',date('Ymd',strtotime($start)))
  104. ->where('t.create_yyyymmdd','<=',date('Ymd',strtotime($end)))
  105. ->where('oc.priority',3)
  106. ->count();
  107. $data[$k]['count'] = $count;
  108. $list = Db::name('todo')
  109. ->alias('t')
  110. ->field('t.id,t.order_id,t.done_time,oc.ywc_time')
  111. ->join('order_convey oc','oc.order_id=t.order_id')
  112. ->where('t.to_user_id',$v['id'])
  113. ->where('t.org_id',$this->orgId)
  114. ->where('t.del',0)
  115. ->where('t.work_type_mode',3)
  116. ->where('t.todo_mode',3)
  117. ->where('t.create_yyyymmdd','>=',date('Ymd',strtotime($start)))
  118. ->where('t.create_yyyymmdd','<=',date('Ymd',strtotime($end)))
  119. ->where('oc.priority',3)
  120. ->select();
  121. $hg = 0;
  122. foreach ($list as $kk=>$vv){
  123. if($vv['done_time'] && $vv['ywc_time'] >= $vv['done_time']){
  124. $hg+=1;
  125. }
  126. }
  127. $data[$k]['hg_num'] = $hg;
  128. $data[$k]['no_hg_num'] = $count -$hg;
  129. if($count > 0){
  130. $bl = round($hg/$count*100,2).'%';
  131. }else{
  132. $bl = '0%';
  133. }
  134. $data[$k]['bl'] = $bl;
  135. }
  136. include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
  137. //实例化PHPExcel类
  138. $objPHPExcel =new \PHPExcel();
  139. //激活当前的sheet表
  140. $objPHPExcel->setActiveSheetIndex(0);
  141. //设置表格头(即excel表格的第一行)
  142. $objPHPExcel->setActiveSheetIndex(0)
  143. ->setCellValue('A1', '姓名')
  144. ->setCellValue('B1', '总数')
  145. ->setCellValue('C1', '合格数')
  146. ->setCellValue('D1', '不合格数')
  147. ->setCellValue('E1', '合格率');
  148. // 设置表格头水平居中
  149. $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()
  150. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  151. $objPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()
  152. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  153. $objPHPExcel->setActiveSheetIndex(0)->getStyle('C1')->getAlignment()
  154. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  155. $objPHPExcel->setActiveSheetIndex(0)->getStyle('D1')->getAlignment()
  156. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  157. $objPHPExcel->setActiveSheetIndex(0)->getStyle('E1')->getAlignment()
  158. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  159. //设置列水平居中
  160. $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()
  161. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  162. $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()
  163. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  164. $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()
  165. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  166. $objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()
  167. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  168. $objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()
  169. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  170. //设置单元格宽度
  171. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(30);
  172. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(30);
  173. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(30);
  174. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(30);
  175. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(30);
  176. //循环刚取出来的数组,将数据逐一添加到excel表格。
  177. for ($i = 0; $i < count($data); $i++) {
  178. $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $data[$i]['user_name']);
  179. $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $data[$i]['count']);
  180. $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), $data[$i]['hg_num']);
  181. $objPHPExcel->getActiveSheet()->setCellValue('D' . ($i + 2), $data[$i]['no_hg_num']);
  182. $objPHPExcel->getActiveSheet()->setCellValue('E' . ($i + 2), $data[$i]['bl']);
  183. }
  184. //设置保存的Excel表格名称
  185. $filename = '运送急查订单统计报表_' . date('YmdHis', time()) . '.xls';
  186. //设置当前激活的sheet表格名称
  187. $objPHPExcel->getActiveSheet()->setTitle('运送急查订单统计报表');
  188. //设置浏览器窗口下载表格
  189. ob_end_clean();
  190. header("Content-Type: application/force-download");
  191. header("Content-Type: application/octet-stream");
  192. header("Content-Type: application/download");
  193. header('Content-Disposition:inline;filename="' . $filename);
  194. //生成excel文件
  195. $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  196. //下载文件在浏览器窗口
  197. return $objWriter->save('php://output');
  198. }
  199. }