0
0

Statistics.php 75 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570157115721573157415751576157715781579158015811582158315841585158615871588158915901591159215931594159515961597159815991600160116021603160416051606160716081609161016111612161316141615161616171618161916201621162216231624162516261627162816291630163116321633163416351636163716381639164016411642164316441645164616471648164916501651165216531654165516561657165816591660166116621663166416651666166716681669167016711672167316741675167616771678167916801681168216831684168516861687168816891690169116921693169416951696169716981699170017011702170317041705170617071708170917101711171217131714171517161717
  1. <?php
  2. /**
  3. * Created by PhpStorm.
  4. * User: my
  5. * Date: 2021/4/12
  6. * Time: 16:04
  7. */
  8. namespace app\admin\controller;
  9. use think\App;
  10. use think\Db;
  11. class Statistics extends Auth{
  12. public function __construct(App $app) {
  13. parent::__construct($app);
  14. }
  15. /**
  16. * 订单统计
  17. *
  18. * @author wst
  19. * @date 2021/4/12 16:05
  20. */
  21. public function OrderTotal(){
  22. $data = (new Wyorders())->OrderTotal(cur_org_id());
  23. $this->assign('data',$data);
  24. $this->assign('meta_title','订单统计');
  25. return $this->fetch();
  26. }
  27. /**
  28. * 完成工时统计
  29. *
  30. * @author wst
  31. * @date 2021/4/12 16:05
  32. */
  33. public function duration(){
  34. $month = request()->get('month')?request()->get('month'):date('Y-m');
  35. $data = (new \app\model\Todo())->duration(cur_org_id(),$month);
  36. $this->assign('data',$data);
  37. $this->assign('month',$month);
  38. $this->assign('meta_title','完成工时统计');
  39. return $this->fetch();
  40. }
  41. /**
  42. * 巡更工作量统计
  43. *
  44. * @author wst
  45. * @date 2021/4/12 18:13
  46. */
  47. public function PatrolWork(){
  48. $data = (new \app\model\PatrolAddr())->PatrolWork(cur_org_id(),1);
  49. $this->assign('data',$data);
  50. $this->assign('meta_title','巡检巡查工作量统计');
  51. return $this->fetch();
  52. }
  53. /**
  54. * 设备台账工作量统计
  55. *
  56. * @author wst
  57. * @date 2021/4/12 18:13
  58. */
  59. public function DeviceWork(){
  60. $data = (new \app\common\model\Device())->DeviceWork(cur_org_id());
  61. $this->assign('data',$data);
  62. $this->assign('meta_title','设备维保工作量统计');
  63. return $this->fetch();
  64. }
  65. /**
  66. * 日常工作工作量统计
  67. *
  68. * @author wst
  69. * @date 2021/4/12 18:13
  70. */
  71. public function DailyWork(){
  72. $data = (new \app\common\model\Daily())->DailyWork(cur_org_id());
  73. $this->assign('data',$data);
  74. $this->assign('meta_title','日常工作量统计');
  75. return $this->fetch();
  76. }
  77. /**
  78. * 巡更人员工作量统计
  79. *
  80. * @author wst
  81. * @date 2021/4/12 18:13
  82. */
  83. public function WorkerPatrol(){
  84. $search['start'] = request()->get('start');
  85. $search['end'] = request()->get('end');
  86. if(empty($search['start'])){ // 未传时间默认近7天的
  87. $search['start'] = date('Y-m-d',time()-(6*24*60*60));
  88. $search['end'] = date('Y-m-d');
  89. }
  90. $data = (new \app\model\PatrolTask())->WorkerPatrol(cur_org_id(),$search,1);
  91. $this->assign('data',$data);
  92. $this->assign('month',$search);
  93. $this->assign('meta_title','巡检巡查人员工作量统计');
  94. return $this->fetch();
  95. }
  96. //预约统计
  97. public function ServiceCount(){
  98. $type=input('type');
  99. $start=input('start');
  100. $end=input('end');
  101. if($type == 1){
  102. $meta_title = '餐饮预约统计';
  103. }elseif ($type == 2){
  104. $meta_title = '美发预约统计';
  105. }elseif ($type == 3){
  106. $meta_title = '洗衣预约统计';
  107. }elseif ($type == 4){
  108. $meta_title = '医疗预约统计';
  109. }elseif ($type == 5){
  110. $meta_title = '活动室预约统计';
  111. }
  112. //获取服务id
  113. $serviceId = Db::name('service')->where('org_id',$this->org_id)->where('type',$type)->value('id');
  114. if(!$serviceId){
  115. $data = [];
  116. $this->assign('data',$data);
  117. }
  118. if(!$start && !$end){
  119. $end = date('Y-m-d H:i:s');
  120. $start = date('Y-m-d H:i:s',strtotime("$start -1 month"));
  121. }
  122. if($start >= $end){
  123. $this->error('开始时间不能大于结束时间');
  124. }
  125. if($type == 1 || $type == 2){
  126. $map[] = ['service_id','=',$serviceId];
  127. $cateList = Db::name('service_cate')->where($map)->order('id asc')->select()->toArray();
  128. $map[] = ['del','=',0];
  129. $itemList = Db::name('service_item')->where($map)->order('id asc')->select()->toArray();
  130. $this->assign('itemList',$itemList);
  131. $this->assign('cateList',$cateList);
  132. if($start && $end){
  133. $where[] = ['sb.create_time','>=',$start];
  134. $where[] = ['sb.create_time','<=',$end];
  135. }
  136. $where[] = ['sb.service_id','=',$serviceId];
  137. $where[] = ['sb.org_id','=',$this->org_id];
  138. $data = [];
  139. foreach ($itemList as $k=>$v){
  140. $data[$k]['title']= $v['title'];
  141. foreach ($cateList as $kk=>$vv){
  142. $bookList = Db::name('service_book')
  143. ->alias('sb')
  144. ->field('sb.*')
  145. ->join('service_book_cate sbc','sbc.service_book_id=sb.id')
  146. ->where('sb.service_item_id',$v['id'])
  147. ->where('sbc.service_cate_id',$vv['id'])
  148. ->where($where)
  149. ->select()
  150. ->toArray();
  151. $total = 0;
  152. foreach ($bookList as $kay=>$val){
  153. $total +=$val['nums'];
  154. }
  155. $data[$k]['list'][$kk] = $total;
  156. }
  157. }
  158. $count = [];
  159. foreach ($cateList as $k=>$v){
  160. $bookList = Db::name('service_book')
  161. ->alias('sb')
  162. ->field('sb.*')
  163. ->join('service_book_cate sbc','sbc.service_book_id=sb.id')
  164. ->where('sbc.service_cate_id',$v['id'])
  165. ->where($where)
  166. ->select()
  167. ->toArray();
  168. $countNum = 0;
  169. foreach ($bookList as $kk=>$vv){
  170. $countNum +=$vv['nums'];
  171. }
  172. $count[$k]['count'] = $countNum;
  173. }
  174. $this->assign('count',$count);
  175. }
  176. if($type == 3){
  177. $where[] = ['service_id','=',$serviceId];
  178. $where[] = ['org_id','=',$this->org_id];
  179. if($start && $end){
  180. $where[] = ['create_time','>=',$start];
  181. $where[] = ['create_time','<=',$end];
  182. }
  183. $data = [];
  184. $bookList = Db::name('service_book')->where($where)->select()->toArray();
  185. $total = 0;
  186. foreach ($bookList as $k=>$v){
  187. $total +=$v['nums'];
  188. }
  189. $data['k'] = 1;
  190. $data['count'] = $total;
  191. }
  192. if($type == 4 || $type == 5){
  193. $map[] = ['service_id','=',$serviceId];
  194. $map[] = ['del','=',0];
  195. $itemList = Db::name('service_item')->where($map)->order('id asc')->select()->toArray();
  196. $this->assign('itemList',$itemList);
  197. $where[] = ['create_time','>=',$start];
  198. $where[] = ['create_time','<=',$end];
  199. $where[] = ['service_id','=',$serviceId];
  200. $where[] = ['org_id','=',$this->org_id];
  201. $data = [];
  202. foreach ($itemList as $k=>$v){
  203. $data[$k]['id']= $v['id'];
  204. $data[$k]['title']= $v['title'];
  205. $bookList = Db::name('service_book')
  206. ->where('service_item_id',$v['id'])
  207. ->where($where)
  208. ->select()
  209. ->toArray();
  210. $total = 0;
  211. foreach ($bookList as $kay=>$val){
  212. $total +=$val['nums'];
  213. }
  214. $data[$k]['count'] = $total;
  215. }
  216. $countNum = 0;
  217. foreach ($data as $kay =>$val){
  218. $countNum += $val['count'];
  219. }
  220. $this->assign('countNum',$countNum);
  221. }
  222. $this->assign('start',$start);
  223. $this->assign('end',$end);
  224. $this->assign('data',$data);
  225. $this->assign('type',$type);
  226. $this->assign('meta_title',$meta_title);
  227. return $this->fetch();
  228. }
  229. public function serviceCountDetails(){
  230. $month = input('month');
  231. $itemId = input('itemId',0);
  232. $serviceId = input('serviceId',0);
  233. if(!$month && !$itemId){
  234. $this->error('参数错误');
  235. }
  236. $startMonth = $month.'-1 00:00:00';
  237. $endMonth = $month.'-31 23:59:59';
  238. $sWeek = date('Y-m-d', (time() - ((date('w') == 0 ? 7 : date('w')) - 1) * 24 * 3600));
  239. $eWeek = date('Y-m-d', (time() + (7 - (date('w') == 0 ? 7 : date('w'))) * 24 * 3600));
  240. $startWeek = $sWeek.' 00:00:00';
  241. $endWeek = $eWeek.' 23:59:59';
  242. $day = date('Y-m-d');
  243. $startDay= $day.' 00:00:00';
  244. $endDay= $day.' 23:59:59';
  245. $cateList = Db::name('service_cate')->where('service_id',$serviceId)->where('enable',1)->select()->toArray();
  246. foreach ($cateList as $k=>$v){
  247. $bookCate = Db::name('service_book_cate')
  248. ->where('service_book_cate_id',$v['id'])
  249. ->select()
  250. ->toArray();
  251. $ids = [];
  252. foreach ($bookCate as $kay=>$val){
  253. $ids[] = $val['service_book_id'];
  254. }
  255. $newIds = array_unique($ids);
  256. $bookList = Db::name('service_book')
  257. ->whereIn('id',$newIds)
  258. ->where('service_item_id',$itemId)
  259. ->where('service_id',$serviceId)
  260. ->where('org_id',$this->org_id)
  261. ->whereTime('create_time', 'between', [$startMonth, $endMonth])
  262. ->select()->toArray();
  263. $nums = 0;
  264. foreach ($bookList as $kk=>$vv){
  265. $nums +=$vv['nums'];
  266. }
  267. $bookList1 = Db::name('service_book')
  268. ->whereIn('id',$newIds)
  269. ->where('service_item_id',$itemId)
  270. ->where('service_id',$serviceId)
  271. ->where('org_id',$this->org_id)
  272. ->whereTime('create_time', 'between', [$startWeek, $endWeek])
  273. ->select()->toArray();
  274. $nums1 = 0;
  275. foreach ($bookList1 as $kk=>$vv){
  276. $nums1 +=$vv['nums'];
  277. }
  278. $bookList2 = Db::name('service_book')
  279. ->whereIn('id',$newIds)
  280. ->where('service_item_id',$itemId)
  281. ->where('service_id',$serviceId)
  282. ->where('org_id',$this->org_id)
  283. ->whereTime('create_time', 'between', [$startDay, $endDay])
  284. ->select()->toArray();
  285. $nums2 = 0;
  286. foreach ($bookList2 as $kk=>$vv){
  287. $nums2 +=$vv['nums'];
  288. }
  289. $cateList[$k]['count'] = $nums;
  290. $cateList[$k]['week'] = $nums1;
  291. $cateList[$k]['day'] = $nums2;
  292. }
  293. $this->assign('meta_title','餐厅统计');
  294. $this->assign('data',$cateList);
  295. return $this->fetch();
  296. }
  297. public function userWork()
  298. {
  299. if(request()->isAjax()){
  300. //分页参数
  301. $length = input('rows',10,'intval'); //每页条数
  302. $page = input('page',1,'intval'); //第几页
  303. $start = ($page - 1) * $length; //分页开始位置
  304. //排序
  305. $sortRow = input('sidx','id','trim'); //排序列
  306. $sort = input('sord','desc','trim'); //排序方式
  307. $order = $sortRow.' '.$sort;
  308. $title = input('title','','trim');
  309. if($title){
  310. $map[] = ['u.nickname','like','%'.$title.'%'];
  311. }
  312. $map= empty($map) ? true: $map;
  313. $ret = Db::name('user')
  314. ->alias('u')
  315. ->field('u.id,u.nickname')
  316. ->join('user_org uo', 'uo.user_id=u.id')
  317. ->where('uo.org_id', $this->org_id)
  318. ->where('u.enable', 1)
  319. ->where('u.del', 0)
  320. ->where($map)
  321. ->limit($start,$length)
  322. ->order($order)
  323. ->select()
  324. ->toArray();
  325. foreach ($ret as $k => $v) {
  326. //获取本月开始时间结束时间
  327. $smonth = date('Y-m') . '-1 00:00:00';
  328. $emonth = date('Y-m') . '-31 23:59:59';
  329. $where[] = ['update_time','>=',$smonth];
  330. $where[] = ['update_time','<=',$emonth];
  331. $userWork = Db::name('user_work')
  332. ->where('user_id', $v['id'])
  333. ->where($where)
  334. ->whereNotNull('start_time')
  335. ->whereNotNull('end_time')
  336. ->select()
  337. ->toArray();
  338. $num = 0;
  339. foreach ($userWork as $kk => $vv) {
  340. $stime = strtotime($vv['start_time']);
  341. $etime = strtotime($vv['end_time']);
  342. $newTime = $etime - $stime;
  343. $num += $newTime;
  344. }
  345. $ret[$k]['month'] = $num;
  346. //获取本周开始时间 结束时间
  347. $sweek = date('Y-m-d', (time() - ((date('w') == 0 ? 7 : date('w')) - 1) * 24 * 3600)) . ' 00:00:00';
  348. $eweek = date('Y-m-d', (time() + (7 - (date('w') == 0 ? 7 : date('w'))) * 24 * 3600)) . ' 23:59:59';
  349. $where2[] = ['update_time','>=',$sweek];
  350. $where2[] = ['update_time','<=',$eweek];
  351. $userWork2 = Db::name('user_work')
  352. ->where('user_id', $v['id'])
  353. ->where($where2)
  354. ->whereNotNull('start_time')
  355. ->whereNotNull('end_time')
  356. ->select()->toArray();
  357. $num2 = 0;
  358. foreach ($userWork2 as $kk => $vv) {
  359. $stime = strtotime($vv['start_time']);
  360. $etime = strtotime($vv['end_time']);
  361. $newTime = $etime - $stime;
  362. $num2 += $newTime;
  363. }
  364. $ret[$k]['week'] = $num2;
  365. //获取今天开始时间 结束时间
  366. $sday = date('Y-m-d') . ' 00:00:00';
  367. $eday = date('Y-m-d') . ' 23:59:59';
  368. $where3[] = ['update_time','>=',$sday];
  369. $where3[] = ['update_time','<=',$eday];
  370. $userWork3 = Db::name('user_work')
  371. ->where('user_id', $v['id'])
  372. ->where($where3)
  373. ->whereNotNull('start_time')
  374. ->whereNotNull('end_time')
  375. ->select()->toArray();
  376. $num3 = 0;
  377. foreach ($userWork3 as $kk => $vv) {
  378. $stime = strtotime($vv['start_time']);
  379. $etime = strtotime($vv['end_time']);
  380. $newTime = $etime - $stime;
  381. $num3 += $newTime;
  382. }
  383. $ret[$k]['day'] = $num3;
  384. }
  385. //数据返回
  386. $totalCount = Db::name('user')
  387. ->alias('u')
  388. ->field('u.id,u.nickname')
  389. ->join('user_org uo', 'uo.user_id=u.id')
  390. ->where('uo.org_id', $this->org_id)
  391. ->where('u.enable', 1)
  392. ->where('u.del', 0)
  393. ->where($map)
  394. ->count();;
  395. $totalPage = ceil($totalCount/$length);
  396. $result['page'] = $page;
  397. $result['total'] = $totalPage;
  398. $result['records'] = $totalCount;
  399. $result['rows'] = $ret;
  400. return json($result);
  401. }else{
  402. $this->assign('meta_title','用户工作时长统计');
  403. return $this->fetch();
  404. }
  405. }
  406. //耗材统计
  407. public function mateItems(){
  408. $month = input('month','','trim');
  409. if($month){
  410. $smonth = $month.'-01 :00:00:00';
  411. $emonth = $month.'-31 :23:59:59';
  412. $where[] = ['t.done_time','>=',$smonth];
  413. $where[] = ['t.done_time','<=',$emonth];
  414. }else{
  415. //本月
  416. $month = date('Y-m');
  417. $sdate = date('Y-m').'-01 :00:00:00';
  418. $edate = date('Y-m').'-31 :23:59:59';
  419. $where[] = ['t.done_time','>=',$sdate];
  420. $where[] = ['t.done_time','<=',$edate];
  421. //本周
  422. $sweek = date('Y-m-d', (time() - ((date('w') == 0 ? 7 : date('w')) - 1) * 24 * 3600)) . ' 00:00:00';
  423. $eweek = date('Y-m-d', (time() + (7 - (date('w') == 0 ? 7 : date('w'))) * 24 * 3600)) . ' 23:59:59';
  424. $where2[] = ['t.done_time','>=',$sweek];
  425. $where2[] = ['t.done_time','<=',$eweek];
  426. //当天
  427. $sday = date('Y-m-d') . ' 00:00:00';
  428. $eday = date('Y-m-d') . ' 23:59:59';
  429. $where3[] = ['t.done_time','>=',$sday];
  430. $where3[] = ['t.done_time','<=',$eday];
  431. }
  432. $map[] = ['enable','=',1];
  433. $map[] = ['org_id','=',$this->org_id];
  434. $map= empty($map) ? true: $map;
  435. $ret = Db::name('dep')->where($map)->select()->toArray();
  436. foreach ($ret as $k=>$v){
  437. //本月
  438. $todo = Db::name('todo')
  439. ->alias('t')
  440. ->field('t.id,t.to_user_id,t.mate_price')
  441. ->join('user_dep ud','ud.user_id=t.to_user_id')
  442. ->where('ud.dep_id',$v['id'])
  443. ->where('t.todo_mode',3)
  444. ->where('t.del',0)
  445. ->where('t.org_id',$this->org_id)
  446. ->where($where)
  447. ->select()
  448. ->toArray();
  449. $total = 0;
  450. foreach ($todo as $kk=>$vv){
  451. $total +=$vv['mate_price'];
  452. }
  453. $ret[$k]['month'] = $total;
  454. //本周
  455. $ret[$k]['week'] = 0;
  456. if(!empty($where2)){
  457. $todo = Db::name('todo')
  458. ->alias('t')
  459. ->field('t.id,t.to_user_id,t.mate_price')
  460. ->join('user_dep ud','ud.user_id=t.to_user_id')
  461. ->where('ud.dep_id',$v['id'])
  462. ->where('t.todo_mode',3)
  463. ->where('t.del',0)
  464. ->where('t.org_id',$this->org_id)
  465. ->where($where2)
  466. ->select()
  467. ->toArray();
  468. $total2 = 0;
  469. foreach ($todo as $kk=>$vv){
  470. $total2 +=$vv['mate_price'];
  471. }
  472. $ret[$k]['week'] = $total2;
  473. }
  474. $ret[$k]['day'] = 0;
  475. if(!empty($where3)){
  476. //当天
  477. $todo = Db::name('todo')
  478. ->alias('t')
  479. ->field('t.id,t.to_user_id,t.mate_price')
  480. ->join('user_dep ud','ud.user_id=t.to_user_id')
  481. ->where('ud.dep_id',$v['id'])
  482. ->where('t.todo_mode',3)
  483. ->where('t.del',0)
  484. ->where('t.org_id',$this->org_id)
  485. ->where($where3)
  486. ->select()
  487. ->toArray();
  488. $total3 = 0;
  489. foreach ($todo as $kk=>$vv){
  490. $total3 +=$vv['mate_price'];
  491. }
  492. $ret[$k]['day'] = $total3;
  493. }
  494. }
  495. $this->assign('data',$ret);
  496. $this->assign('month',$month);
  497. $this->assign('meta_title','耗材统计列表');
  498. return $this->fetch();
  499. }
  500. public function mateItemsDetails($id,$month){
  501. $sdate = $month.'-1 00:00:00';
  502. $edate = $month.'-31 23:59:59';
  503. $map[] = ['t.done_time','>=',$sdate];
  504. $map[] = ['t.done_time','<=',$edate];
  505. if(request()->isAjax()){
  506. //分页参数
  507. $length = input('rows',10,'intval'); //每页条数
  508. $page = input('page',1,'intval'); //第几页
  509. $start = ($page - 1) * $length; //分页开始位置
  510. //排序
  511. $sortRow = input('sidx','id','trim'); //排序列
  512. $sort = input('sord','desc','trim'); //排序方式
  513. $order = $sortRow.' '.$sort;
  514. $ret = Db::name('todo')
  515. ->alias('t')
  516. ->field('t.to_user_id,tmi.*')
  517. ->join('user_dep ud','ud.user_id=t.to_user_id')
  518. ->join('todo_mate_item tmi','tmi.todo_id=t.id')
  519. ->where('ud.dep_id',$id)
  520. ->where('t.todo_mode',3)
  521. ->where('t.del',0)
  522. ->where('t.org_id',$this->org_id)
  523. ->where($map)
  524. ->limit($start,$length)
  525. ->order($order)
  526. ->select()
  527. ->toArray();
  528. foreach ($ret as $k=>$v){
  529. $ret[$k]['title']=Db::name('mate_items')
  530. ->where('id',$v['items_id'])
  531. ->value('title');
  532. $ret[$k]['nickname']=Db::name('user')
  533. ->where('id',$v['to_user_id'])
  534. ->value('nickname');
  535. }
  536. //数据返回
  537. $totalCount = Db::name('todo')
  538. ->alias('t')
  539. ->field('tmi.*')
  540. ->join('user_dep ud','ud.user_id=t.to_user_id')
  541. ->join('todo_mate_item tmi','tmi.todo_id=t.id')
  542. ->where('ud.dep_id',$id)
  543. ->where('t.todo_mode',3)
  544. ->where('t.del',0)
  545. ->where('t.org_id',$this->org_id)
  546. ->where($map)
  547. ->count();
  548. $totalPage = ceil($totalCount/$length);
  549. $result['page'] = $page;
  550. $result['total'] = $totalPage;
  551. $result['records'] = $totalCount;
  552. $result['rows'] = $ret;
  553. return json($result);
  554. }else{
  555. $this->assign('meta_title','部门使用耗材列表');
  556. $this->assign('id',$id);
  557. $this->assign('month',$month);
  558. return $this->fetch();
  559. }
  560. }
  561. //报修科室使用材料统计
  562. public function cltj(){
  563. $cur = date('Y-m-d');
  564. $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
  565. $end = input('end', date('Y-m-d'));
  566. $start1 = $start . ' 00:00:00';
  567. $end1 = $end . ' 23:59:59';
  568. $data = $this->cltjData($start1,$end1);
  569. $this->assign('data', $data);
  570. $this->assign('start', $start);
  571. $this->assign('end', $end);
  572. return $this->fetch();
  573. }
  574. public function cltjData($start,$end){
  575. $orgId = $this->orgId;
  576. $ret = Db::name('order_repair')
  577. ->alias('a')
  578. ->join('orders b','a.order_id=b.id')
  579. ->where('b.org_id',$orgId)
  580. ->where('a.address_id','>',0)
  581. ->field('a.address_id')
  582. ->distinct(true)
  583. ->select();
  584. $map = [];
  585. if(!empty($start)){
  586. $map[] = ['t.done_time','>=',$start];
  587. }
  588. if(!empty($end)){
  589. $map[] = ['t.done_time','<',$end];
  590. }
  591. $map[] = ['t.del','=',0];
  592. $map[] = ['t.org_id','=',$orgId];
  593. foreach ($ret as $k=>$v){
  594. $map1 = [];
  595. $todo = Db::name('todo_mate_item')
  596. ->alias('tmi')
  597. ->join('todo_mate tm','tm.id = tmi.todo_mate_id')
  598. ->join('todo t','tm.todo_id = t.id')
  599. ->join('orders o','o.id = t.order_id')
  600. ->join('order_repair r','o.id = r.order_id')
  601. ->where('r.address_id',$v['address_id'])
  602. ->where($map)
  603. ->sum('tmi.total_money');
  604. $num =$todo?round($todo,2):0;
  605. if($num<=0){
  606. unset($ret[$k]);
  607. }else{
  608. $ret[$k]['month'] = $num;
  609. if(!empty($start)){
  610. $map1[] = ['t.done_time','>=',$start];
  611. }
  612. if(!empty($end)){
  613. $map1[] = ['t.done_time','<',$end];
  614. }
  615. $map1[] = ['t.del','=',0];
  616. $map1[] = ['t.org_id','=',$orgId];
  617. $map1[] = ['r.address_id','=',$v['address_id']];
  618. $lists =Db::name('todo_mate_item')
  619. ->alias('tmi')
  620. ->join('todo_mate tm','tm.id = tmi.todo_mate_id')
  621. ->join('todo t','tm.todo_id = t.id')
  622. ->join('orders o','o.id = t.order_id')
  623. ->join('user u','u.id = t.to_user_id')
  624. ->join('order_repair r','o.id = r.order_id')
  625. ->join('mate_goods ma','ma.id = tmi.items_id')
  626. ->field('tmi.*,u.real_name,ma.title')
  627. ->where($map1)
  628. ->select();
  629. if(empty($lists)){
  630. unset($ret[$k]);
  631. continue;
  632. }
  633. $ret[$k]['rows'] = count($lists);
  634. $ret[$k]['items'] = $lists;
  635. $ret[$k]['title'] =Db::name('address')
  636. ->where('id',$v['address_id'])
  637. ->value('title');
  638. }
  639. }
  640. return $ret;
  641. }
  642. //耗材统计导出
  643. public function cltjExport()
  644. {
  645. set_time_limit(0);
  646. ini_set("memory_limit", "1024M");
  647. $cur = date('Y-m-d');
  648. $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
  649. $end = input('end', date('Y-m-d'));
  650. $start1 = $start . ' 00:00:00';
  651. $end1 = $end . ' 23:59:59';
  652. $data = $this->cltjData($start1,$end1);
  653. $title = '报修科室使用材料统计报表';
  654. header("Content-type: application/vnd.ms-excel");
  655. header("Content-Type: application/force-download");
  656. header("Content-Disposition: attachment; filename=".$title.".xls");
  657. header('Expires:0');
  658. header('Pragma:public');
  659. $res = '';
  660. $res.='<table style="border-top: 1px solid #ddd;border-left: 1px solid #ddd;border-spacing: 0;">';
  661. $res.='<tr style="background: #ffffff;">';
  662. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">科室名称</th>';
  663. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">材料名称</th>';
  664. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">数量</th>';
  665. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">单价</th>';
  666. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">总价</th>';
  667. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">小计</th>';
  668. $res.='</tr>';
  669. foreach ($data as $k=>$v){
  670. $res.='<tr style="background: #ffffff;">';
  671. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="'.$v['rows'].'">'.$v['title'].'</td>';
  672. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['items'][0]['title'].'</td>';
  673. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['items'][0]['total'].'</td>';
  674. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['items'][0]['money'].'</td>';
  675. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['items'][0]['total_money'].'</td>';
  676. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="'.$v['rows'].'">'.$v['month'].'</td>';
  677. $res.='</tr>';
  678. if(count($v['items']) >=2){
  679. foreach ($v['items'] as $k1=>$v1){
  680. if($k1>0){
  681. $res.='<tr style="background: #ffffff;">';
  682. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1['title'].'</td>';
  683. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1['total'].'</td>';
  684. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1['money'].'</td>';
  685. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1['total_money'].'</td>';
  686. $res.='</tr>';
  687. }
  688. }
  689. }
  690. }
  691. echo $res;
  692. }
  693. //员工维修工作量
  694. public function finishTime(){
  695. $cur = date('Y-m-d');
  696. $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
  697. $end = input('end', date('Y-m-d'));
  698. $start1 = $start . ' 00:00:00';
  699. $end1 = $end . ' 23:59:59';
  700. $data = $this->finishTimeData($start1,$end1);
  701. $this->assign('data', $data);
  702. $this->assign('start', $start);
  703. $this->assign('end', $end);
  704. return $this->fetch();
  705. }
  706. public function finishTimeData($start,$end) {
  707. $org_id = $this->orgId;
  708. $list = (new \app\common\model\WorkTypeMode())->getRolesUser(1,$this->orgId);
  709. $a = [];
  710. foreach ($list as $k=>$v){
  711. if($v['user']){
  712. foreach ($v['user'] as $k1=>$v1){
  713. $a[] = $v1;
  714. }
  715. }
  716. }
  717. $accepted = $nFinish=0;
  718. $list = $a;
  719. $map = [];
  720. if(!empty($start)){
  721. $map[] = ['todo.create_yyyymmdd','>=',date('Ymd',strtotime($start))];
  722. }
  723. if(!empty($end)){
  724. $map[] = ['todo.create_yyyymmdd','<=',date('Ymd',strtotime($end))];
  725. }
  726. foreach ($list as $k=>$v){
  727. $hs = 0;
  728. $orderList = Db::name('todo')
  729. ->alias('todo')
  730. ->where('todo.org_id',$org_id)
  731. ->where('todo.todo_mode',3)
  732. ->where('todo.to_user_id',$v['id'])
  733. ->where('todo.work_type_mode',1)
  734. ->where($map)
  735. ->select();
  736. foreach ($orderList as $k1=>$v1){
  737. $hs += strtotime($v1['done_time'])-strtotime($v1['create_time']);
  738. }
  739. $hszj = $hs >0?round($hs/3600,2):0;
  740. $list[$k]['hszj'] = $hszj;
  741. $wc_time = Db::name('todo')
  742. ->alias('todo')
  743. ->where('todo.org_id',$org_id)
  744. ->where('todo.todo_mode',3)
  745. ->where('todo.to_user_id',$v['id'])
  746. ->where('todo.work_type_mode',1)
  747. ->where($map)
  748. ->sum('todo.wc_time');
  749. $total = $wc_time?round($wc_time/3600,2):0;
  750. $list[$k]['total'] = $total;
  751. $nums = Db::name('todo')
  752. ->alias('todo')
  753. ->where('todo.org_id',$org_id)
  754. ->where('todo.todo_mode',3)
  755. ->where('todo.to_user_id',$v['id'])
  756. ->where('todo.work_type_mode',1)
  757. ->where($map)
  758. ->count();
  759. $list[$k]['num'] =$nums;
  760. if($nums>0){
  761. $avg = round($total/$nums,2);
  762. }else{
  763. $avg = 0;
  764. }
  765. $list[$k]['avg'] = $avg;
  766. $dep = Db::name('user_dep')
  767. ->alias('a')
  768. ->join('dep o','a.dep_id=o.id')
  769. ->where('a.user_id',$v['id'])
  770. ->field('o.title')
  771. ->find();
  772. $list[$k]['dep'] = $dep?$dep['title']:'';
  773. // 已接工
  774. $list[$k]['accepted'] = Db::name('todo')
  775. ->alias('todo')
  776. ->where('todo.org_id',$org_id)
  777. ->where('todo.todo_mode','>',1)
  778. ->where('todo.to_user_id',$v['id'])
  779. ->where('todo.work_type_mode',1)
  780. ->where($map)
  781. ->count();
  782. $accepted += $list[$k]['accepted'];
  783. // 未完工
  784. $list[$k]['nFinish'] = Db::name('todo')
  785. ->alias('todo')
  786. ->where('todo.org_id',$org_id)
  787. ->where('todo.todo_mode','in',[2,4,5,6,7,8])
  788. ->where('todo.to_user_id',$v['id'])
  789. ->where('todo.work_type_mode',1)
  790. ->where($map)
  791. ->count();
  792. $nFinish += $list[$k]['nFinish'];
  793. }
  794. return $list;
  795. }
  796. //excel导出
  797. public function finishTimeExport() {
  798. $meta_title = '维修人员工作时长';
  799. $cur = date('Y-m-d');
  800. $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
  801. $end = input('end', date('Y-m-d'));
  802. $start1 = $start . ' 00:00:00';
  803. $end1 = $end . ' 23:59:59';
  804. $ret = $this->finishTimeData($start1,$end1);
  805. include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
  806. //实例化PHPExcel类
  807. $objPHPExcel = new \PHPExcel();
  808. //激活当前的sheet表
  809. $objPHPExcel->setActiveSheetIndex(0);
  810. //设置表格头(即excel表格的第一行)
  811. $objPHPExcel->setActiveSheetIndex(0)
  812. ->setCellValue('A1', '姓名')
  813. ->setCellValue('B1', '班组名称')
  814. ->setCellValue('C1', '接工量')
  815. ->setCellValue('D1', '完工量')
  816. ->setCellValue('E1', '未完工量')
  817. ->setCellValue('F1', '维修耗时')
  818. ->setCellValue('G1', '工时总计');
  819. // 设置表格头水平居中
  820. $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()
  821. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  822. $objPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()
  823. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  824. $objPHPExcel->setActiveSheetIndex(0)->getStyle('C1')->getAlignment()
  825. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  826. $objPHPExcel->setActiveSheetIndex(0)->getStyle('D1')->getAlignment()
  827. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  828. $objPHPExcel->setActiveSheetIndex(0)->getStyle('E1')->getAlignment()
  829. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  830. $objPHPExcel->setActiveSheetIndex(0)->getStyle('F1')->getAlignment()
  831. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  832. $objPHPExcel->setActiveSheetIndex(0)->getStyle('G1')->getAlignment()
  833. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  834. $objPHPExcel->setActiveSheetIndex(0)->getStyle('H1')->getAlignment()
  835. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  836. //设置列水平居中
  837. $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()
  838. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  839. $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()
  840. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  841. $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()
  842. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  843. $objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()
  844. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  845. $objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()
  846. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  847. $objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()
  848. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  849. $objPHPExcel->setActiveSheetIndex(0)->getStyle('G')->getAlignment()
  850. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  851. $objPHPExcel->setActiveSheetIndex(0)->getStyle('H')->getAlignment()
  852. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  853. //设置单元格宽度
  854. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(10);
  855. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(20);
  856. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(20);
  857. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(20);
  858. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(20);
  859. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(50);
  860. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(20);
  861. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setWidth(20);
  862. //循环刚取出来的数组,将数据逐一添加到excel表格。
  863. for ($i = 0; $i < count($ret); $i++) {
  864. $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $ret[$i]['real_name']);
  865. $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $ret[$i]['dep']);
  866. $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), $ret[$i]['accepted']);
  867. $objPHPExcel->getActiveSheet()->setCellValue('D' . ($i + 2), $ret[$i]['num']);
  868. $objPHPExcel->getActiveSheet()->setCellValue('E' . ($i + 2), $ret[$i]['nFinish']);
  869. $objPHPExcel->getActiveSheet()->setCellValue('F' . ($i + 2), $ret[$i]['hszj']);
  870. $objPHPExcel->getActiveSheet()->setCellValue('G' . ($i + 2), $ret[$i]['total']);
  871. }
  872. //设置保存的Excel表格名称
  873. $filename = '维修人员工作时长' . date('YmdHis', time()) . '.xls';
  874. //设置当前激活的sheet表格名称
  875. $objPHPExcel->getActiveSheet()->setTitle('维修人员工作时长');
  876. //设置浏览器窗口下载表格
  877. ob_end_clean();
  878. header("Content-Type: application/force-download");
  879. header("Content-Type: application/octet-stream");
  880. header("Content-Type: application/download");
  881. header('Content-Disposition:inline;filename="' . $filename);
  882. //生成excel文件
  883. $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  884. //下载文件在浏览器窗口
  885. return $objWriter->save('php://output');
  886. }
  887. //班组维修工作量
  888. public function bzwx(){
  889. $cur = date('Y-m-d');
  890. $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
  891. $end = input('end', date('Y-m-d'));
  892. $title = input('title','');
  893. $start1 = $start . ' 00:00:00';
  894. $end1 = $end . ' 23:59:59';
  895. $data = $this->bzwxData($start1,$end1,$title);
  896. $this->assign('data', $data);
  897. $this->assign('start', $start);
  898. $this->assign('end', $end);
  899. $this->assign('title', $title);
  900. return $this->fetch();
  901. }
  902. public function bzwxData($start,$end,$title){
  903. $org_id = $this->orgId;
  904. $m = [];
  905. if(!empty($title)){
  906. $m[] =['title','like','%'.$title.'%'];
  907. }
  908. $list = Db::name('dep')
  909. ->where('del',0)
  910. ->where('enable',1)
  911. ->where('org_id',$org_id)
  912. ->select();
  913. $map = [];
  914. if(!empty($start)){
  915. $map[] = ['create_yyyymmdd','>=',date('Ymd',strtotime($start))];
  916. }
  917. if(!empty($end)){
  918. $map[] = ['create_yyyymmdd','<=',date('Ymd',strtotime($end))];
  919. }
  920. $accepted = $nFinish=0;
  921. foreach ($list as $k=>$v){
  922. $user = Db::name('user_dep')
  923. ->alias('b')
  924. ->join('user a','b.user_id=a.id')
  925. ->where('a.del',0)
  926. ->where('a.enable',1)
  927. ->where('b.dep_id',$v['id'])
  928. ->field('b.user_id')
  929. ->select();
  930. $ids = $user?array_column($user,'user_id'):[];
  931. $list[$k]['num'] =0;
  932. $list[$k]['accepted'] =0;
  933. $list[$k]['nFinish'] =0;
  934. if(!empty($ids)){
  935. $nums =Db::name('todo')
  936. ->where('org_id',$org_id)
  937. ->where('to_user_id','in',$ids)
  938. ->where('work_type_mode',1)
  939. ->where('todo_mode','in',[2,3,9])
  940. ->where($map)
  941. ->count();
  942. $list[$k]['num'] =$nums;
  943. // 已接工
  944. $list[$k]['accepted'] = Db::name('todo')
  945. ->where('org_id',$org_id)
  946. ->where('to_user_id','in',$ids)
  947. ->where('work_type_mode',1)
  948. ->where('todo_mode','>',1)
  949. ->where($map)
  950. ->count();
  951. $accepted += $list[$k]['accepted'];
  952. // 未完工
  953. $list[$k]['nFinish'] = Db::name('todo')
  954. ->where('org_id',$org_id)
  955. ->where('to_user_id','in',$ids)
  956. ->where('work_type_mode',1)
  957. ->where('todo_mode','in',[2,4,5,6,7])
  958. ->where($map)
  959. ->count();
  960. $nFinish += $list[$k]['nFinish'];
  961. if($nums==0 && $list[$k]['accepted']==0 && $list[$k]['nFinish']==0){
  962. unset($list[$k]);
  963. }
  964. }else{
  965. unset($list[$k]);
  966. }
  967. }
  968. return $list;
  969. }
  970. //excel导出
  971. public function bzwxExport() {
  972. $meta_title = '班组维修工作量统计表';
  973. $cur = date('Y-m-d');
  974. $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
  975. $end = input('end', date('Y-m-d'));
  976. $start1 = $start . ' 00:00:00';
  977. $end1 = $end . ' 23:59:59';
  978. $title = input('title','');
  979. $ret = $this->bzwxData($start1,$end1,$title);
  980. include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
  981. //实例化PHPExcel类
  982. $objPHPExcel = new \PHPExcel();
  983. //激活当前的sheet表
  984. $objPHPExcel->setActiveSheetIndex(0);
  985. //设置表格头(即excel表格的第一行)
  986. $objPHPExcel->setActiveSheetIndex(0)
  987. ->setCellValue('A1', '班组名称')
  988. ->setCellValue('B1', '接工量')
  989. ->setCellValue('C1', '完工量')
  990. ->setCellValue('D1', '未完工量');
  991. // 设置表格头水平居中
  992. $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()
  993. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  994. $objPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()
  995. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  996. $objPHPExcel->setActiveSheetIndex(0)->getStyle('C1')->getAlignment()
  997. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  998. $objPHPExcel->setActiveSheetIndex(0)->getStyle('D1')->getAlignment()
  999. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1000. $objPHPExcel->setActiveSheetIndex(0)->getStyle('E1')->getAlignment()
  1001. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1002. $objPHPExcel->setActiveSheetIndex(0)->getStyle('F1')->getAlignment()
  1003. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1004. $objPHPExcel->setActiveSheetIndex(0)->getStyle('G1')->getAlignment()
  1005. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1006. $objPHPExcel->setActiveSheetIndex(0)->getStyle('H1')->getAlignment()
  1007. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1008. //设置列水平居中
  1009. $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()
  1010. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1011. $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()
  1012. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1013. $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()
  1014. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1015. $objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()
  1016. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1017. $objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()
  1018. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1019. $objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()
  1020. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1021. $objPHPExcel->setActiveSheetIndex(0)->getStyle('G')->getAlignment()
  1022. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1023. $objPHPExcel->setActiveSheetIndex(0)->getStyle('H')->getAlignment()
  1024. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1025. //设置单元格宽度
  1026. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(10);
  1027. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(20);
  1028. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(20);
  1029. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(20);
  1030. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(20);
  1031. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(50);
  1032. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(20);
  1033. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setWidth(20);
  1034. //循环刚取出来的数组,将数据逐一添加到excel表格。
  1035. for ($i = 0; $i < count($ret); $i++) {
  1036. $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $ret[$i]['title']);
  1037. $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $ret[$i]['accepted']);
  1038. $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), $ret[$i]['num']);
  1039. $objPHPExcel->getActiveSheet()->setCellValue('D' . ($i + 2), $ret[$i]['nFinish']);
  1040. }
  1041. //设置保存的Excel表格名称
  1042. $filename = '班组维修工作量统计表' . date('YmdHis', time()) . '.xls';
  1043. //设置当前激活的sheet表格名称
  1044. $objPHPExcel->getActiveSheet()->setTitle('班组维修工作量统计表');
  1045. //设置浏览器窗口下载表格
  1046. ob_end_clean();
  1047. header("Content-Type: application/force-download");
  1048. header("Content-Type: application/octet-stream");
  1049. header("Content-Type: application/download");
  1050. header('Content-Disposition:inline;filename="' . $filename);
  1051. //生成excel文件
  1052. $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  1053. //下载文件在浏览器窗口
  1054. return $objWriter->save('php://output');
  1055. }
  1056. public function bxks(){
  1057. $cur = date('Y-m-d');
  1058. $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
  1059. $end = input('end', date('Y-m-d'));
  1060. $title = input('title','');
  1061. $start1 = $start . ' 00:00:00';
  1062. $end1 = $end . ' 23:59:59';
  1063. $data = $this->bxksData($start1,$end1,$title);
  1064. $this->assign('data', $data);
  1065. $this->assign('start', $start);
  1066. $this->assign('end', $end);
  1067. $this->assign('title', $title);
  1068. return $this->fetch();
  1069. }
  1070. public function bxksData($start,$end,$title){
  1071. $org_id = $this->orgId;
  1072. $map = [];
  1073. if(!empty($title)){
  1074. $ks = Db::name('address')
  1075. ->where('org_id',$org_id)
  1076. ->where('del',0)
  1077. ->where('enable',1)
  1078. ->where('title','like','%'.$title.'%')
  1079. ->select();
  1080. if(empty($ks)){
  1081. $map[] = ['a.address_id','=',-1];
  1082. }else{
  1083. $map[] = ['a.address_id','in',array_column($ks,'id')];
  1084. }
  1085. }
  1086. $ret =Db::name('order_repair')
  1087. ->alias('a')
  1088. ->join('orders b','a.order_id=b.id')
  1089. ->where('b.org_id',$org_id)
  1090. ->where('a.address_id','>',0)
  1091. ->where($map)
  1092. ->field('a.address_id')
  1093. ->distinct(true)
  1094. ->select();
  1095. if(!empty($start)){
  1096. $map1[] = ['t.create_yyyymmdd','>=',date('Ymd',strtotime($start))];
  1097. }
  1098. if(!empty($end)){
  1099. $map1[] = ['t.create_yyyymmdd','<=',date('Ymd',strtotime($end))];
  1100. }
  1101. $map1[] = ['t.del','=',0];
  1102. $map1[] = ['t.org_id','=',$org_id];
  1103. foreach ($ret as $k=>$v){
  1104. $num = Db::name('order_repair')
  1105. ->alias('a')
  1106. ->join('orders t','a.order_id = t.id')
  1107. ->where('a.address_id',$v['address_id'])
  1108. ->where($map1)
  1109. ->count();
  1110. $ret[$k]['num'] = $num;
  1111. $ret[$k]['title'] = Db::name('address')
  1112. ->where('id',$v['address_id'])
  1113. ->value('title');
  1114. }
  1115. return $ret;
  1116. }
  1117. //excel导出
  1118. public function bxksExport() {
  1119. $meta_title = '报修科室统计';
  1120. $cur = date('Y-m-d');
  1121. $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
  1122. $end = input('end', date('Y-m-d'));
  1123. $start1 = $start . ' 00:00:00';
  1124. $end1 = $end . ' 23:59:59';
  1125. $title = input('title','');
  1126. $ret = $this->bxksData($start1,$end1,$title);
  1127. include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
  1128. //实例化PHPExcel类
  1129. $objPHPExcel = new \PHPExcel();
  1130. //激活当前的sheet表
  1131. $objPHPExcel->setActiveSheetIndex(0);
  1132. //设置表格头(即excel表格的第一行)
  1133. $objPHPExcel->setActiveSheetIndex(0)
  1134. ->setCellValue('A1', '科室名称')
  1135. ->setCellValue('B1', '报修订单数量');
  1136. // 设置表格头水平居中
  1137. $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()
  1138. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1139. $objPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()
  1140. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1141. $objPHPExcel->setActiveSheetIndex(0)->getStyle('C1')->getAlignment()
  1142. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1143. $objPHPExcel->setActiveSheetIndex(0)->getStyle('D1')->getAlignment()
  1144. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1145. $objPHPExcel->setActiveSheetIndex(0)->getStyle('E1')->getAlignment()
  1146. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1147. $objPHPExcel->setActiveSheetIndex(0)->getStyle('F1')->getAlignment()
  1148. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1149. $objPHPExcel->setActiveSheetIndex(0)->getStyle('G1')->getAlignment()
  1150. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1151. $objPHPExcel->setActiveSheetIndex(0)->getStyle('H1')->getAlignment()
  1152. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1153. //设置列水平居中
  1154. $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()
  1155. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1156. $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()
  1157. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1158. $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()
  1159. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1160. $objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()
  1161. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1162. $objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()
  1163. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1164. $objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()
  1165. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1166. $objPHPExcel->setActiveSheetIndex(0)->getStyle('G')->getAlignment()
  1167. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1168. $objPHPExcel->setActiveSheetIndex(0)->getStyle('H')->getAlignment()
  1169. ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1170. //设置单元格宽度
  1171. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(10);
  1172. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(20);
  1173. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(20);
  1174. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(20);
  1175. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(20);
  1176. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(50);
  1177. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(20);
  1178. $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setWidth(20);
  1179. //循环刚取出来的数组,将数据逐一添加到excel表格。
  1180. for ($i = 0; $i < count($ret); $i++) {
  1181. $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $ret[$i]['title']);
  1182. $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $ret[$i]['num']);
  1183. }
  1184. //设置保存的Excel表格名称
  1185. $filename = '报修科室统计' . date('YmdHis', time()) . '.xls';
  1186. //设置当前激活的sheet表格名称
  1187. $objPHPExcel->getActiveSheet()->setTitle('报修科室统计');
  1188. //设置浏览器窗口下载表格
  1189. ob_end_clean();
  1190. header("Content-Type: application/force-download");
  1191. header("Content-Type: application/octet-stream");
  1192. header("Content-Type: application/download");
  1193. header('Content-Disposition:inline;filename="' . $filename);
  1194. //生成excel文件
  1195. $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  1196. //下载文件在浏览器窗口
  1197. return $objWriter->save('php://output');
  1198. }
  1199. public function grcltj(){
  1200. $cur = date('Y-m-d');
  1201. $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
  1202. $end = input('end', date('Y-m-d'));
  1203. $start1 = $start . ' 00:00:00';
  1204. $end1 = $end . ' 23:59:59';
  1205. $data = $this->grcltjData($start1,$end1);
  1206. $this->assign('data', $data);
  1207. $this->assign('start', $start);
  1208. $this->assign('end', $end);
  1209. return $this->fetch();
  1210. }
  1211. public function grcltjData($start,$end){
  1212. $orgId = $this->orgId;
  1213. $ret = Db::name('todo_mate')
  1214. ->alias('a')
  1215. ->join('todo b','a.todo_id=b.id')
  1216. ->field('a.*')
  1217. ->where('a.org_id',$orgId)
  1218. ->where('a.type',0)
  1219. ->where('b.del',0)
  1220. ->group('a.user_id')
  1221. ->select();
  1222. $map = [];
  1223. if(!empty($start)){
  1224. $map[] = ['tmi.create_time','>=',$start];
  1225. }
  1226. if(!empty($end)){
  1227. $map[] = ['tmi.create_time','<',$end];
  1228. }
  1229. $map[] = ['t.del','=',0];
  1230. $map[] = ['tm.org_id','=',$orgId];
  1231. foreach ($ret as $k=>$v){
  1232. $map1 = [];
  1233. $todo = Db::name('todo_mate_item')
  1234. ->alias('tmi')
  1235. ->join('todo_mate tm','tm.id = tmi.todo_mate_id')
  1236. ->join('todo t','tm.todo_id = t.id')
  1237. ->join('orders o','o.id = t.order_id')
  1238. ->where('tmi.user_id',$v['user_id'])
  1239. ->where($map)
  1240. ->sum('tmi.total_money');
  1241. $num =$todo?round($todo,2):0;
  1242. if($num<=0){
  1243. unset($ret[$k]);
  1244. }else{
  1245. $ret[$k]['month'] = $num;
  1246. if(!empty($start)){
  1247. $map1[] = ['t.create_time','>=',$start];
  1248. }
  1249. if(!empty($end)){
  1250. $map1[] = ['t.create_time','<',$end];
  1251. }
  1252. $map1[] = ['t.del','=',0];
  1253. $map1[] = ['t.org_id','=',$orgId];
  1254. $map1[] = ['tm.user_id','=',$v['user_id']];
  1255. $lists =Db::name('todo_mate_item')
  1256. ->alias('tmi')
  1257. ->join('todo_mate tm','tm.id = tmi.todo_mate_id')
  1258. ->join('todo t','tm.todo_id = t.id')
  1259. ->join('orders o','o.id = t.order_id')
  1260. ->join('user u','u.id = t.to_user_id')
  1261. ->join('mate_goods ma','ma.id = tmi.items_id')
  1262. ->field('tmi.*,u.real_name,ma.title')
  1263. ->where($map1)
  1264. ->select();
  1265. if(empty($lists)){
  1266. unset($ret[$k]);
  1267. continue;
  1268. }
  1269. $ret[$k]['rows'] = count($lists);
  1270. $ret[$k]['items'] = $lists;
  1271. $ret[$k]['real_name'] = Db::name('user')
  1272. ->where('id',$v['user_id'])
  1273. ->value('real_name');
  1274. }
  1275. }
  1276. return $ret;
  1277. }
  1278. public function grcltjExport()
  1279. {
  1280. set_time_limit(0);
  1281. ini_set("memory_limit", "1024M");
  1282. $cur = date('Y-m-d');
  1283. $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
  1284. $end = input('end', date('Y-m-d'));
  1285. $start1 = $start . ' 00:00:00';
  1286. $end1 = $end . ' 23:59:59';
  1287. $data = $this->grcltjData($start1,$end1);
  1288. $title = '工人耗材用量统计';
  1289. header("Content-type: application/vnd.ms-excel");
  1290. header("Content-Type: application/force-download");
  1291. header("Content-Disposition: attachment; filename=".$title.".xls");
  1292. header('Expires:0');
  1293. header('Pragma:public');
  1294. $res = '';
  1295. $res.='<table style="border-top: 1px solid #ddd;border-left: 1px solid #ddd;border-spacing: 0;">';
  1296. $res.='<tr style="background: #ffffff;">';
  1297. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">工人名称</th>';
  1298. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">材料名称</th>';
  1299. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">数量</th>';
  1300. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">单价</th>';
  1301. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">总价</th>';
  1302. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">使用时间</th>';
  1303. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">小计</th>';
  1304. $res.='</tr>';
  1305. foreach ($data as $k=>$v){
  1306. $res.='<tr style="background: #ffffff;">';
  1307. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="'.$v['rows'].'">'.$v['real_name'].'</td>';
  1308. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['items'][0]['title'].'</td>';
  1309. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['items'][0]['total'].'</td>';
  1310. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['items'][0]['money'].'</td>';
  1311. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['items'][0]['total_money'].'</td>';
  1312. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['items'][0]['create_time'].'</td>';
  1313. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="'.$v['rows'].'">'.$v['month'].'</td>';
  1314. $res.='</tr>';
  1315. if(count($v['items']) >=2){
  1316. foreach ($v['items'] as $k1=>$v1){
  1317. if($k1>0){
  1318. $res.='<tr style="background: #ffffff;">';
  1319. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1['title'].'</td>';
  1320. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1['total'].'</td>';
  1321. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1['money'].'</td>';
  1322. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1['total_money'].'</td>';
  1323. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1['create_time'].'</td>';
  1324. $res.='</tr>';
  1325. }
  1326. }
  1327. }
  1328. }
  1329. echo $res;
  1330. }
  1331. public function bxrcltj(){
  1332. $cur = date('Y-m-d');
  1333. $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
  1334. $end = input('end', date('Y-m-d'));
  1335. $start1 = $start . ' 00:00:00';
  1336. $end1 = $end . ' 23:59:59';
  1337. $data = $this->bxrcltjData($start1,$end1);
  1338. $this->assign('data', $data);
  1339. $this->assign('start', $start);
  1340. $this->assign('end', $end);
  1341. return $this->fetch();
  1342. }
  1343. public function bxrcltjData($start,$end){
  1344. $orgId = $this->orgId;
  1345. $ret = Db::name('orders')
  1346. ->alias('a')
  1347. ->where('org_id',$orgId)
  1348. ->where('del',0)
  1349. ->where('finish_time','>=',$start)
  1350. ->where('finish_time','<',$end)
  1351. ->group('user_id')
  1352. ->select();
  1353. $map = [];
  1354. if(!empty($start)){
  1355. $map[] = ['t.done_time','>=',$start];
  1356. }
  1357. if(!empty($end)){
  1358. $map[] = ['t.done_time','<',$end];
  1359. }
  1360. $map[] = ['t.del','=',0];
  1361. $map[] = ['t.org_id','=',$orgId];
  1362. foreach ($ret as $k=>$v){
  1363. $map1 = [];
  1364. $todo = Db::name('todo_mate_item')
  1365. ->alias('tmi')
  1366. ->join('todo_mate tm','tm.id = tmi.todo_mate_id')
  1367. ->join('todo t','tm.todo_id = t.id')
  1368. ->join('orders o','o.id = t.order_id')
  1369. ->where('o.user_id',$v['user_id'])
  1370. ->where($map)
  1371. ->sum('tmi.total_money');
  1372. $num =$todo?round($todo,2):0;
  1373. if($num<=0){
  1374. unset($ret[$k]);
  1375. }else{
  1376. $ret[$k]['month'] = $num;
  1377. if(!empty($start)){
  1378. $map1[] = ['t.done_time','>=',$start];
  1379. }
  1380. if(!empty($end)){
  1381. $map1[] = ['t.done_time','<',$end];
  1382. }
  1383. $map1[] = ['t.del','=',0];
  1384. $map1[] = ['t.org_id','=',$orgId];
  1385. $map1[] = ['o.user_id','=',$v['user_id']];
  1386. $lists =Db::name('todo_mate_item')
  1387. ->alias('tmi')
  1388. ->join('todo_mate tm','tm.id = tmi.todo_mate_id')
  1389. ->join('todo t','tm.todo_id = t.id')
  1390. ->join('orders o','o.id = t.order_id')
  1391. ->join('user u','u.id = t.to_user_id')
  1392. ->join('mate_goods ma','ma.id = tmi.items_id')
  1393. ->field('tmi.*,u.real_name,ma.title')
  1394. ->where($map1)
  1395. ->select();
  1396. if(empty($lists)){
  1397. unset($ret[$k]);
  1398. continue;
  1399. }
  1400. $ret[$k]['rows'] = count($lists);
  1401. $ret[$k]['items'] = $lists;
  1402. $ret[$k]['title'] =Db::name('user')
  1403. ->where('id',$v['user_id'])
  1404. ->value('real_name');
  1405. }
  1406. }
  1407. return $ret;
  1408. }
  1409. //耗材统计导出
  1410. public function bxrcltjExport()
  1411. {
  1412. set_time_limit(0);
  1413. ini_set("memory_limit", "1024M");
  1414. $cur = date('Y-m-d');
  1415. $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
  1416. $end = input('end', date('Y-m-d'));
  1417. $start1 = $start . ' 00:00:00';
  1418. $end1 = $end . ' 23:59:59';
  1419. $data = $this->bxrcltjData($start1,$end1);
  1420. $title = '报修人耗材统计报表';
  1421. header("Content-type: application/vnd.ms-excel");
  1422. header("Content-Type: application/force-download");
  1423. header("Content-Disposition: attachment; filename=".$title.".xls");
  1424. header('Expires:0');
  1425. header('Pragma:public');
  1426. $res = '';
  1427. $res.='<table style="border-top: 1px solid #ddd;border-left: 1px solid #ddd;border-spacing: 0;">';
  1428. $res.='<tr style="background: #ffffff;">';
  1429. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">报修人</th>';
  1430. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">材料名称</th>';
  1431. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">数量</th>';
  1432. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">单价</th>';
  1433. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">使用人</th>';
  1434. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">使用时间</th>';
  1435. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">总价</th>';
  1436. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">小计</th>';
  1437. $res.='</tr>';
  1438. foreach ($data as $k=>$v){
  1439. $res.='<tr style="background: #ffffff;">';
  1440. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="'.$v['rows'].'">'.$v['title'].'</td>';
  1441. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['items'][0]['title'].'</td>';
  1442. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['items'][0]['total'].'</td>';
  1443. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['items'][0]['money'].'</td>';
  1444. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['items'][0]['real_name'].'</td>';
  1445. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['items'][0]['create_time'].'</td>';
  1446. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['items'][0]['total_money'].'</td>';
  1447. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="'.$v['rows'].'">'.$v['month'].'</td>';
  1448. $res.='</tr>';
  1449. if(count($v['items']) >=2){
  1450. foreach ($v['items'] as $k1=>$v1){
  1451. if($k1>0){
  1452. $res.='<tr style="background: #ffffff;">';
  1453. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1['title'].'</td>';
  1454. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1['total'].'</td>';
  1455. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1['money'].'</td>';
  1456. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1['real_name'].'</td>';
  1457. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1['create_time'].'</td>';
  1458. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1['total_money'].'</td>';
  1459. $res.='</tr>';
  1460. }
  1461. }
  1462. }
  1463. }
  1464. echo $res;
  1465. }
  1466. public function wlflcltj(){
  1467. $cur = date('Y-m-d');
  1468. $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
  1469. $end = input('end', date('Y-m-d'));
  1470. $start1 = $start . ' 00:00:00';
  1471. $end1 = $end . ' 23:59:59';
  1472. $data = $this->wlflcltjData($start1,$end1);
  1473. $this->assign('data', $data);
  1474. $this->assign('start', $start);
  1475. $this->assign('end', $end);
  1476. return $this->fetch();
  1477. }
  1478. public function wlflcltjData($start,$end){
  1479. $orgId = $this->orgId;
  1480. $ret = Db::name('mate_cate')
  1481. ->where('org_id',$orgId)
  1482. ->where('del',0)
  1483. ->where('enable',1)
  1484. ->select();
  1485. $map = [];
  1486. if(!empty($start)){
  1487. $map[] = ['t.done_time','>=',$start];
  1488. }
  1489. if(!empty($end)){
  1490. $map[] = ['t.done_time','<',$end];
  1491. }
  1492. $map[] = ['t.del','=',0];
  1493. $map[] = ['t.org_id','=',$orgId];
  1494. foreach ($ret as $k=>$v){
  1495. $map1 = [];
  1496. $todo = Db::name('todo_mate_item')
  1497. ->alias('tmi')
  1498. ->join('todo_mate tm','tm.id = tmi.todo_mate_id')
  1499. ->join('mate_goods mg','tmi.items_id=mg.id')
  1500. ->join('mate_cate mc','mg.cate_id = mc.id')
  1501. ->join('todo t','tm.todo_id = t.id')
  1502. ->join('orders o','o.id = t.order_id')
  1503. ->where('mc.id',$v['id'])
  1504. ->where($map)
  1505. ->sum('tmi.total_money');
  1506. $num =$todo?round($todo,2):0;
  1507. if($num<=0){
  1508. unset($ret[$k]);
  1509. }else{
  1510. $ret[$k]['month'] = $num;
  1511. if(!empty($start)){
  1512. $map1[] = ['t.done_time','>=',$start];
  1513. }
  1514. if(!empty($end)){
  1515. $map1[] = ['t.done_time','<',$end];
  1516. }
  1517. $map1[] = ['t.del','=',0];
  1518. $map1[] = ['t.org_id','=',$orgId];
  1519. $map1[] = ['mc.id','=',$v['id']];
  1520. $lists =Db::name('todo_mate_item')
  1521. ->alias('tmi')
  1522. ->join('todo_mate tm','tm.id = tmi.todo_mate_id')
  1523. ->join('mate_goods mg','tmi.items_id=mg.id')
  1524. ->join('mate_cate mc','mg.cate_id = mc.id')
  1525. ->join('todo t','tm.todo_id = t.id')
  1526. ->join('orders o','o.id = t.order_id')
  1527. ->join('user u','u.id = t.to_user_id')
  1528. ->join('mate_goods ma','ma.id = tmi.items_id')
  1529. ->field('tmi.*,u.real_name,ma.title')
  1530. ->where($map1)
  1531. ->select();
  1532. if(empty($lists)){
  1533. unset($ret[$k]);
  1534. continue;
  1535. }
  1536. $ret[$k]['rows'] = count($lists);
  1537. $ret[$k]['items'] = $lists;
  1538. }
  1539. }
  1540. return $ret;
  1541. }
  1542. //耗材统计导出
  1543. public function wlflcltjExport()
  1544. {
  1545. set_time_limit(0);
  1546. ini_set("memory_limit", "1024M");
  1547. $cur = date('Y-m-d');
  1548. $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
  1549. $end = input('end', date('Y-m-d'));
  1550. $start1 = $start . ' 00:00:00';
  1551. $end1 = $end . ' 23:59:59';
  1552. $data = $this->wlflcltjData($start1,$end1);
  1553. $title = '物料分类统计报表';
  1554. header("Content-type: application/vnd.ms-excel");
  1555. header("Content-Type: application/force-download");
  1556. header("Content-Disposition: attachment; filename=".$title.".xls");
  1557. header('Expires:0');
  1558. header('Pragma:public');
  1559. $res = '';
  1560. $res.='<table style="border-top: 1px solid #ddd;border-left: 1px solid #ddd;border-spacing: 0;">';
  1561. $res.='<tr style="background: #ffffff;">';
  1562. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">分类名称</th>';
  1563. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">材料名称</th>';
  1564. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">数量</th>';
  1565. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">单价</th>';
  1566. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">使用人</th>';
  1567. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">使用时间</th>';
  1568. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">总价</th>';
  1569. $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">小计</th>';
  1570. $res.='</tr>';
  1571. foreach ($data as $k=>$v){
  1572. $res.='<tr style="background: #ffffff;">';
  1573. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="'.$v['rows'].'">'.$v['title'].'</td>';
  1574. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['items'][0]['title'].'</td>';
  1575. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['items'][0]['total'].'</td>';
  1576. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['items'][0]['money'].'</td>';
  1577. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['items'][0]['real_name'].'</td>';
  1578. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['items'][0]['create_time'].'</td>';
  1579. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['items'][0]['total_money'].'</td>';
  1580. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="'.$v['rows'].'">'.$v['month'].'</td>';
  1581. $res.='</tr>';
  1582. if(count($v['items']) >=2){
  1583. foreach ($v['items'] as $k1=>$v1){
  1584. if($k1>0){
  1585. $res.='<tr style="background: #ffffff;">';
  1586. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1['title'].'</td>';
  1587. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1['total'].'</td>';
  1588. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1['money'].'</td>';
  1589. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1['real_name'].'</td>';
  1590. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1['create_time'].'</td>';
  1591. $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1['total_money'].'</td>';
  1592. $res.='</tr>';
  1593. }
  1594. }
  1595. }
  1596. }
  1597. echo $res;
  1598. }
  1599. }