CompanyGoods.php 24 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660
  1. <?php
  2. namespace app\admin\controller;
  3. use app\common\model\MateGoodsLog;
  4. use app\common\util\ExcelUtil;
  5. use think\App;
  6. use think\Db;
  7. use think\Exception;
  8. class CompanyGoods extends Auth
  9. {
  10. public function __construct(App $app = null) {
  11. parent::__construct($app);
  12. $this->model = new \app\common\model\MateGoods();
  13. $this->table = $this->model->table;
  14. }
  15. public function index(){
  16. if(request()->isAjax()){
  17. //分页参数
  18. $length = input('rows',10,'intval'); //每页条数
  19. $page = input('page',1,'intval'); //第几页
  20. $start = ($page - 1) * $length; //分页开始位置
  21. //排序
  22. $sortRow = input('sidx','id','trim'); //排序列
  23. $sort = input('sord','desc','trim'); //排序方式
  24. $order = $sortRow.' '.$sort;
  25. $title = input('title','','trim');
  26. if($title){
  27. $map[] = ['title','like','%'.$title.'%'];
  28. }
  29. $enable = input('enable','','trim');
  30. if($enable != ''){
  31. $map[] = ['enable','=',$enable];
  32. }
  33. $having = '1=1';
  34. $snums = input('kc_status','','trim');
  35. if($snums == 1) { // 正常
  36. $having = 'mnums >= 0 and nnums >= 0';
  37. }else if($snums == 2){ // 偏多
  38. $having = 'mnums < 0';
  39. }else if($snums == 3){ // 偏少
  40. $having = 'nnums < 0';
  41. }
  42. $cate_id = input('cate_id','','trim');
  43. if($cate_id != ''){
  44. $map[] = ['cate_id','=',$cate_id];
  45. }
  46. $map[] = ['del','=',0];
  47. $map[] = ['org_id','=',$this->orgId];
  48. $map= empty($map) ? true: $map;
  49. //数据查询
  50. $lists =db($this->table)
  51. // ->field('id,price,nums,type,title,unit,brand,total_price,spec,buy_time,remark,create_time,enable,cate_id,s_limit,x_limit,IFNULL(s_limit-nums,0) as mnums,IFNULL(nums- x_limit,0) as nnums')
  52. ->where($map)
  53. ->limit($start,$length)
  54. // ->group('id')
  55. // ->having($having)
  56. ->order($order)
  57. ->select();
  58. foreach ($lists as $k=>$v){
  59. $lists[$k]['cate_name'] = Db::name('mate_cate')->where('id',$v['cate_id'])->value('title');
  60. $kc_txt = '正常';
  61. // if($lists[$k]['s_limit'] != 0 && $lists[$k]['x_limit'] != 0){
  62. // if($lists[$k]['s_limit'] > 0 && $v['nums'] > $lists[$k]['s_limit']){
  63. // $kc_txt = '<span style="color: #DCAC6C">偏多</span> ';
  64. // }
  65. // if($lists[$k]['s_limit'] > 0 && $v['nums'] < $lists[$k]['s_limit']){
  66. // $kc_txt = '<span style="color: red">偏少</span> ';
  67. // }
  68. // }
  69. if($v['s_limit'] > 0){
  70. if($v['nums'] > $v['s_limit']){
  71. $kc_txt = '<span style="color: #DCAC6C">偏多</span> ';
  72. }elseif ($v['nums'] < $v['s_limit'] && $v['nums'] > $v['x_limit']){
  73. $kc_txt = '正常';
  74. }elseif ($v['nums'] == 0 || $v['nums'] < $v['x_limit']){
  75. $kc_txt = '<span style="color: red">偏少</span> ';
  76. }
  77. }
  78. $lists[$k]['kc_txt'] = $kc_txt;
  79. }
  80. //数据返回
  81. $totalCount = db($this->table)->where($map)->count();
  82. $totalPage = ceil($totalCount/$length);
  83. $result['page'] = $page;
  84. $result['total'] = $totalPage;
  85. $result['records'] = $totalCount;
  86. $result['rows'] = $lists;
  87. return json($result);
  88. }else{
  89. $this->assign('meta_title','物品管理');
  90. $cate = Db::name('mate_cate')
  91. ->where('enable',1)
  92. ->where('del',0)
  93. ->where('org_id',$this->orgId)
  94. ->select();
  95. $this->assign('cate',$cate);
  96. return $this->fetch();
  97. }
  98. }
  99. public function selectGoods(){
  100. if(request()->isAjax()){
  101. //分页参数
  102. $length = input('rows',10,'intval'); //每页条数
  103. $page = input('page',1,'intval'); //第几页
  104. $start = ($page - 1) * $length; //分页开始位置
  105. //排序
  106. $sortRow = input('sidx','id','trim'); //排序列
  107. $sort = input('sord','desc','trim'); //排序方式
  108. $order = $sortRow.' '.$sort;
  109. $title = input('title','','trim');
  110. if($title){
  111. $map[] = ['title','like','%'.$title.'%'];
  112. }
  113. $map[] = ['enable','=',1];
  114. $map[] = ['del','=',0];
  115. $map[] = ['org_id','=',$this->orgId];
  116. $map= empty($map) ? true: $map;
  117. //数据查询
  118. $lists =db($this->table)
  119. ->where($map)->limit($start,$length)
  120. ->order($order)->select();
  121. //数据返回
  122. $totalCount = db($this->table)->where($map)->count();
  123. $totalPage = ceil($totalCount/$length);
  124. $result['page'] = $page;
  125. $result['total'] = $totalPage;
  126. $result['records'] = $totalCount;
  127. $result['rows'] = $lists;
  128. return json($result);
  129. }else{
  130. return $this->fetch();
  131. }
  132. }
  133. /**
  134. * 新增/编辑
  135. */
  136. public function add($id=0){
  137. $model = $this->model;
  138. if(request()->isPost()){
  139. $res = $model->updates();
  140. if($res){
  141. $this->success('操作成功',url('index'));
  142. }else{
  143. $this->error($model->getError());
  144. }
  145. }else{
  146. $meta_title = '新增物品';
  147. if($id){
  148. $info =db($this->table)->where('id',$id)->find();
  149. $this->assign('info',$info);
  150. $meta_title = '编辑物品';
  151. }
  152. $cate = Db::name('mate_cate')->where('del',0)->where('org_id',$this->orgId)->select();
  153. $this->assign('cate',$cate);
  154. $this->assign('meta_title',$meta_title);
  155. return $this->fetch();
  156. }
  157. }
  158. /**
  159. * 删除记录
  160. * @param int $id
  161. */
  162. public function del($id=0){
  163. if(!$id){
  164. $this->error('参数错误');
  165. }
  166. if(db('mate_apply_goods')->where('goods_id',$id)->find()){
  167. $this->error('该商品有入库或出库记录,暂不能删除');
  168. }
  169. $res = db($this->table)->where('id',$id)->update(['del'=>1]);
  170. if($res){
  171. $this->success('删除成功');
  172. }else{
  173. $this->error('删除失败');
  174. }
  175. }
  176. /**
  177. * 改变字段值
  178. * @param int $fv
  179. * @param string $fn
  180. * @param int $fv
  181. */
  182. public function changeField($id=0,$fn='',$fv=0){
  183. if(!$fn||!$id){
  184. $this->error('参数错误');
  185. }
  186. $res = db($this->table)->where('id',$id)->update([$fn => $fv]);
  187. if($res){
  188. $this->success('操作成功');
  189. }else{
  190. $this->error('操作失败');
  191. }
  192. }
  193. /**
  194. * 处置
  195. */
  196. public function option($id=0){
  197. $model = new MateGoodsLog();
  198. if(request()->isPost()){
  199. $data = request()->post();
  200. if(empty($data['nums'])) $this->error('请输入数量');
  201. if(empty($data['remark'])) $this->error('请输入备注');
  202. $info = db('mate_goods')
  203. ->where('id',$id)
  204. ->find();
  205. if($info['nums'] <$data['nums']) $this->error('库存数量不足');
  206. $sData = [
  207. 'org_id'=>$this->orgId,
  208. 'goods_id'=>$id,
  209. 'nums'=>$data['nums'],
  210. 'remark'=>$data['remark'],
  211. 'price'=>$info['price'],
  212. ];
  213. Db::startTrans();
  214. try{
  215. $res = $model->saveData($sData);
  216. if(!$res){
  217. \exception($model->getError());
  218. }
  219. $ret = Db::name('mate_goods')->where('id',$id)->setDec('nums',$data['nums']);
  220. if(!$ret){
  221. \exception('操作失败');
  222. }
  223. Db::commit();
  224. }catch (Exception $e){
  225. Db::rollback();
  226. $this->error($e->getMessage());
  227. }
  228. $this->success('操作成功');
  229. }else{
  230. $this->assign('id',$id);
  231. return $this->fetch();
  232. }
  233. }
  234. public function import(){
  235. return $this->fetch();
  236. }
  237. /**
  238. * 下载点模板
  239. */
  240. public function downloadtem(){
  241. set_time_limit(0);
  242. ini_set("memory_limit","512M");
  243. include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
  244. $types = model('Address')->getTypes();
  245. ksort($types);
  246. $fileName = '物品模板.xlsx';
  247. $excel = new \PHPExcel();
  248. $sheet = $excel->setActiveSheetIndex(0);
  249. $arr = array('A','B','C','D','E','F','H','I','J','K');
  250. foreach($arr as $k=>$v){
  251. $excel->getActiveSheet()->getStyle($v)->getAlignment()->setWrapText(true);//换行
  252. $excel->getActiveSheet()->getStyle($v.'1')->getFont()->setBold(true);
  253. $excel->getActiveSheet()->getColumnDimension($v)->setWidth(18);
  254. }
  255. $sheet->setCellValueByColumnAndRow(0,1,'名称');
  256. $sheet->setCellValueByColumnAndRow(1,1,'分类');
  257. $sheet->setCellValueByColumnAndRow(2,1,'单位');
  258. $sheet->setCellValueByColumnAndRow(3,1,'品牌');
  259. $sheet->setCellValueByColumnAndRow(4,1,'规格');
  260. $sheet->setCellValueByColumnAndRow(5,1,'类型(固定资产/耗材)');
  261. $sheet->setCellValueByColumnAndRow(6,1,'备注');
  262. $sheet->setCellValueByColumnAndRow(7,1,'单价');
  263. $sheet->setCellValueByColumnAndRow(8,1,'数量');
  264. $sheet->setCellValueByColumnAndRow(9,1,'库存上限');
  265. $sheet->setCellValueByColumnAndRow(10,1,'库存下限');
  266. $excel->getActiveSheet()->setCellValue('A2', '测试物品');
  267. $excel->getActiveSheet()->setCellValue('B2', '测试分类编号(2)');
  268. $excel->getActiveSheet()->setCellValue('C2', '个');
  269. $excel->getActiveSheet()->setCellValue('D2', '格力');
  270. $excel->getActiveSheet()->setCellValue('E2', '10个/包');
  271. $excel->getActiveSheet()->setCellValue('F2', '固定资产');
  272. $excel->getActiveSheet()->setCellValue('G2', 'xxxx');
  273. $excel->getActiveSheet()->setCellValue('H2', '10');
  274. $excel->getActiveSheet()->setCellValue('I2', '100');
  275. $excel->getActiveSheet()->setCellValue('J2', '100');
  276. $excel->getActiveSheet()->setCellValue('K2', '0');
  277. ob_end_clean();//清除缓冲区,避免乱码
  278. header('Content-Type: application/vnd.ms-excel');
  279. header('Content-Disposition: attachment;filename="'.$fileName.'"');
  280. header('Cache-Control: max-age=0');
  281. $objWriter = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
  282. $objWriter->save('php://output'); //文件通过浏览器下载
  283. }
  284. /**
  285. * 导入
  286. */
  287. public function importexcel(){
  288. set_time_limit(0);
  289. ini_set("memory_limit", -1);
  290. ob_flush();//清空缓存
  291. flush();//刷新缓存
  292. include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
  293. $orgId = $this->orgId;
  294. if(request()->file()) {
  295. $file = request()->file('file');
  296. //获取文件后缀
  297. $e = explode('.',$_FILES['file']['name']);
  298. $ext = $e[count($e)-1];
  299. if($ext == 'xls'){
  300. \PHPExcel_IOFactory::createReader( 'Excel5');
  301. }else{
  302. \PHPExcel_IOFactory::createReader('Excel2007');
  303. }
  304. $newArr=['xls','xlsx'];
  305. if(!in_array($ext,$newArr)){
  306. exit('文件格式不正确');
  307. }
  308. // 移动到框架应用根目录/uploads/ 目录下
  309. $info = $file->validate([ 'size'=>config('app.max_upload_file_size') ])
  310. ->move(env('root_path') . 'public' . DIRECTORY_SEPARATOR . 'uploads'. DIRECTORY_SEPARATOR . 'files');
  311. if(!$info){
  312. exit('文件上传失败');
  313. }
  314. $img = './uploads/files/' . $info->getSaveName();
  315. $filePath = str_replace('\\', '/', $img);
  316. $newPath = \PHPExcel_IOFactory::load($filePath);
  317. $excelArray = $newPath->getsheet(0)->toArray(); //转换为数组格式
  318. array_shift($excelArray); //删除第一个数组(标题);
  319. $a = [
  320. '固定资产'=>1,
  321. '耗材'=>2,
  322. ];
  323. if(empty($excelArray)){
  324. exit('文件内容为空');
  325. }
  326. foreach ($excelArray as $k => $v) {
  327. if(!$v[0]){
  328. echo "<font color=\"red\" style='margin-left: 20px;font-size: 17px'>第".($k+1)."行,名称为空,未导入</font><br />";
  329. continue;
  330. }
  331. $cateId = 0;
  332. if(trim($v[1])){
  333. $cateId = Db::name('mate_cate')->where('id',$v[1])->value('id');
  334. if(empty($cateId)){
  335. $ps =[
  336. 'title'=>$v[1],
  337. 'org_id'=>$this->orgId,
  338. 'create_time'=>getTime(),
  339. ];
  340. $cateId = Db::name('mate_cate')->insertGetId($ps);
  341. }
  342. }
  343. if(!$v[5]){
  344. echo "<font color=\"red\" style='margin-left: 20px;font-size: 17px'>第".($k+1)."行,类型为空,未导入</font><br />";
  345. continue;
  346. }
  347. $check = Db::name('mate_goods')
  348. ->where('del',0)
  349. ->where('title',$v[0])
  350. ->find();
  351. if($check){
  352. echo "<font color=\"red\" style='margin-left: 20px;font-size: 17px'>第".($k+1)."行,名称已存在,未导入</font><br />";
  353. continue;
  354. }
  355. if(!isset($a[$v[5]])){
  356. echo "<font color=\"red\" style='margin-left: 20px;font-size: 17px'>第".($k+1)."行,类型格式错误,未导入</font><br />";
  357. continue;
  358. }
  359. $rData = [
  360. 'org_id'=>$this->orgId,
  361. 'title'=>$v[0],
  362. 'cate_id'=>$cateId?$cateId:0,
  363. 'unit'=>$v[2],
  364. 'brand'=>$v[3],
  365. 'spec'=>$v[4],
  366. 'type'=>$a[$v[5]],
  367. 'remark'=>$v[6],
  368. 'price'=>$v[7],
  369. 'nums'=>$v[8],
  370. 's_limit'=>$v[9],
  371. 'x_limit'=>$v[10],
  372. 'create_time'=>getTime(),
  373. 'buy_time'=>date('Y-m-d')
  374. ];
  375. $ret=Db::name('mate_goods')->insert($rData);
  376. if(!$ret){
  377. echo "<font color=\"red\" style='margin-left: 20px;font-size: 17px'>第".($k+1)."行,导入失败</font><br />";
  378. }else{
  379. echo "<font color=\"green\" style='margin-left:20px;font-size: 17px'>第".($k+1)."行,导入成功</font><br />";
  380. }
  381. }
  382. }else{
  383. exit('请上传文件');
  384. }
  385. }
  386. public function xhtj(){
  387. $cur = date('Y-m-d');
  388. $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 month')));
  389. $end = input('end', date('Y-m-d'));
  390. $start1 = $start . ' 00:00:00';
  391. $end1 = $end . ' 00:00:00';
  392. $list = $this->xhtjData($start1, $end1);
  393. $this->assign('list', $list['list']);
  394. $this->assign('zj', $list['zj']);
  395. $this->assign('start', $start);
  396. $this->assign('end', $end);
  397. return $this->fetch();
  398. }
  399. public function xhtjData($start1, $end1){
  400. $map[] = ['ma.create_time', '>=', $start1];
  401. $map[] = ['ma.create_time', '<=', $end1];
  402. $map[] = ['ma.org_id', '=', $this->orgId];
  403. $list = Db::name('mate_apply')
  404. ->alias('ma')
  405. ->join('mate_apply_goods mag','ma.id=mag.apply_id')
  406. ->join('mate_goods mg','mg.id=mag.goods_id')
  407. ->where($map)
  408. ->field('mag.goods_id,mag.price,mg.title')
  409. ->group('mag.goods_id')
  410. ->select();
  411. foreach ($list as $k => $v) {
  412. $list[$k]['num'] = Db::name('mate_apply')
  413. ->alias('ma')
  414. ->join('mate_apply_goods mag','ma.id=mag.apply_id')
  415. ->join('mate_goods mg','mg.id=mag.goods_id')
  416. ->where('mag.goods_id',$v['goods_id'])
  417. ->where($map)
  418. ->sum('mag.nums');
  419. $list[$k]['total_price'] = round($list[$k]['num']*$v['price'],1);
  420. }
  421. $map1[] = ['tm.create_time', '>=', $start1];
  422. $map1[] = ['tm.create_time', '<=', $end1];
  423. $map1[] = ['tm.org_id', '=', $this->orgId];
  424. $list1 = Db::name('todo_mate')
  425. ->alias('tm')
  426. ->join('todo_mate_item tmi','tmi.todo_mate_id=tm.id')
  427. ->join('mate_goods mg','mg.id=tmi.items_id')
  428. ->where($map1)
  429. ->group('tmi.items_id')
  430. ->field('tmi.items_id as goods_id,tmi.money as price,mg.title')
  431. ->select();
  432. foreach ($list1 as $k=>$v){
  433. $list1[$k]['num'] = Db::name('todo_mate')
  434. ->alias('tm')
  435. ->join('todo_mate_item tmi','tmi.todo_mate_id=tm.id')
  436. ->where($map1)
  437. ->where('tmi.items_id',$v['goods_id'])
  438. ->sum('tmi.total');
  439. $list1[$k]['total_price'] = round($list1[$k]['num']*$v['price'],1);
  440. }
  441. // $map2[] = ['mau.create_time', '>=', $start1];
  442. // $map2[] = ['mau.create_time', '<=', $end1];
  443. // $map2[] = ['mau.org_id', '=', $this->orgId];
  444. // $list2 = Db::name('mate_apply_use')
  445. // ->alias('mau')
  446. // ->join('mate_apply_use_goods maug','mau.id=maug.apply_id')
  447. // ->join('mate_goods mg','mg.id=maug.goods_id')
  448. // ->where($map2)
  449. // ->field('maug.goods_id,maug.price,mg.title')
  450. // ->group('maug.goods_id')
  451. // ->select();
  452. //
  453. // foreach ($list2 as $k => $v) {
  454. // $list2[$k]['num'] = Db::name('mate_apply_use')
  455. // ->alias('mau')
  456. // ->join('mate_apply_use_goods maug','mau.id=maug.apply_id')
  457. // ->join('mate_goods mg','mg.id=maug.goods_id')
  458. // ->where('maug.goods_id',$v['goods_id'])
  459. // ->where($map2)
  460. // ->sum('maug.nums');
  461. // $list2[$k]['total_price'] = round($list2[$k]['num']*$v['price'],1);
  462. // }
  463. foreach ($list as $k=>$v){
  464. $totalPrice = 0;
  465. $tnum = 0;
  466. foreach ($list1 as $kk=>$vv){
  467. if($v['goods_id'] == $vv['goods_id']){
  468. $totalPrice = $v['total_price'] + $vv['total_price'];
  469. $tnum = $v['num'] + $vv['num'];
  470. unset($list1[$kk]);
  471. }
  472. /* foreach ($list2 as $kkk=>$vvv){
  473. if($v['goods_id'] == $vv['goods_id']){
  474. $totalPrice = $v['total_price'] + $vv['total_price'];
  475. $tnum = $v['num'] + $vv['num'];
  476. unset($list1[$kk]);
  477. }elseif ($v['goods_id'] == $vvv['goods_id']){
  478. $totalPrice = $v['total_price'] + $vvv['total_price'];
  479. $tnum = $v['num'] + $vvv['num'];
  480. unset($list2[$kkk]);
  481. }elseif ($v['goods_id'] == $vv['goods_id'] && $v['goods_id'] == $vvv['goods_id']){
  482. $totalPrice = $v['total_price'] + $vv['total_price'] + $vvv['total_price'];
  483. $tnum = $v['num'] + $vv['num'] + $vvv['num'];
  484. unset($list1[$kk]);
  485. unset($list2[$kkk]);
  486. }
  487. }*/
  488. }
  489. if($totalPrice > 0 && $tnum > 0){
  490. $list[$k]['total_price'] = $totalPrice;
  491. $list[$k]['num'] = $tnum;
  492. }
  493. }
  494. $array = array_merge($list,$list1);
  495. $zj = 0;
  496. foreach ($array as $k=>$v){
  497. $zj +=$v['total_price'];
  498. }
  499. return ['list'=>$array,'zj'=>$zj];
  500. }
  501. public function qrcode($id=0){
  502. $info = Db::name('mate_goods')->where('id',$id)->find();
  503. $code = get_qrcode_str('mate_goods',$id);
  504. $this->assign('code',$code);
  505. $this->assign('info',$info);
  506. return $this->fetch();
  507. }
  508. public function wptj(){
  509. $cur = date('Y-m-d');
  510. $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 month')));
  511. $end = input('end', date('Y-m-d'));
  512. $start1 = $start . ' 00:00:00';
  513. $end1 = $end . ' 23:59:59';
  514. $title = input('title','','trim');
  515. $cateTitle = input('cateTitle','');
  516. $list = $this->wptjData($start1, $end1,$title,$cateTitle);
  517. $this->assign('list', $list);
  518. $this->assign('start', $start);
  519. $this->assign('end', $end);
  520. $this->assign('title', $title);
  521. $this->assign('cateTitle', $cateTitle);
  522. return $this->fetch();
  523. }
  524. public function wptjData($start1, $end1,$title,$cateTitle) {
  525. $map1[] = ['ma.create_time', '>=', $start1];
  526. $map1[] = ['ma.create_time', '<=', $end1];
  527. $map =[];
  528. if($title){
  529. $map[] = ['title','like','%'.$title.'%'];
  530. }
  531. if($cateTitle){
  532. $cateIds = Db::name('mate_cate')->where('title','like','%'.$cateTitle.'%')->column('id');
  533. $map[] = ['cate_id','in',$cateIds];
  534. }
  535. $list = Db::name('mate_goods')
  536. ->where('org_id', $this->orgId)
  537. ->where('del', 0)
  538. ->where('enable', 1)
  539. ->where($map)
  540. ->select();
  541. foreach ($list as $k => $v) {
  542. $list[$k]['ck'] = Db::name('mate_apply')
  543. ->alias('ma')
  544. ->join('mate_apply_goods mag','mag.apply_id=ma.id')
  545. ->where('ma.type',1)
  546. ->where('mag.goods_id',$v['id'])
  547. ->where($map1)
  548. ->sum('mag.nums');
  549. $list[$k]['rk'] = Db::name('mate_apply')
  550. ->alias('ma')
  551. ->join('mate_apply_goods mag','mag.apply_id=ma.id')
  552. ->where('ma.type',2)
  553. ->where('mag.goods_id',$v['id'])
  554. ->where($map1)
  555. ->sum('mag.nums');
  556. $tt = '';
  557. if($v['consume'] == 1){
  558. $tt ='使用消耗';
  559. }elseif ($v['consume'] == 2){
  560. $tt ='出库消耗';
  561. }
  562. $list[$k]['consume_title'] = $tt;
  563. $list[$k]['cate_title'] = Db::name('mate_cate')->where('id',$v['cate_id'])->value('title');
  564. }
  565. return $list;
  566. }
  567. public function wptjDataExport() {
  568. $cur = date('Y-m-d');
  569. $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
  570. $end = input('end', date('Y-m-d'));
  571. $start1 = $start . ' 00:00:00';
  572. $end1 = $end . ' 23:59:59';
  573. $title = input('title','','trim');
  574. $cateTitle = input('cateTitle','');
  575. $list = $this->wptjData($start1, $end1,$title,$cateTitle);
  576. $filename = '物品统计';
  577. $header = [
  578. ['title' => '分类', 'name' => 'cate_title','width'=>'20'],
  579. ['title' => '物品名称', 'name' => 'title','width'=>'20'],
  580. ['title' => '品牌', 'name' => 'brand','width'=>'20'],
  581. ['title' => '规格', 'name' => 'spec','width'=>'20'],
  582. ['title' => '入库数量', 'name' => 'ck','width'=>'20'],
  583. ['title' => '出库数量', 'name' => 'rk','width'=>'20'],
  584. ['title' => '当前余量', 'name' => 'nums','width'=>'20'],
  585. ['title' => '类型', 'name' => 'consume_title','width'=>'20'],
  586. ];
  587. ExcelUtil::export($filename,$header,$list);
  588. }
  589. }