Report.php 24 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564
  1. <?php
  2. namespace app\admin\controller;
  3. use think\facade\Db;
  4. use think\facade\Validate;
  5. //报表处理类
  6. class Report extends Base
  7. {
  8. //产品价格和产品导出
  9. public function exportGood()
  10. {
  11. $param = $this->request->only(['date', 'platform_id', 'status'], 'post', 'trim');
  12. $val = Validate::rule([
  13. 'date|筛选时间' => 'require|date',
  14. 'platform_id|筛选平台ID' => 'require|number|gt:0',
  15. 'status|状态' => 'require|number|in:5,6,8',
  16. ]);
  17. if (!$val->check($param)) return error_show(1004, $val->getError());
  18. $where_ladder = [['gl.is_del', '=', 0]];
  19. $where_good = [['g.is_del', '=', 0]];
  20. if (!empty($param['date'])) {
  21. $where_ladder[] = ['gl.addtime', 'between', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']];
  22. $where_good[] = ['g.addtime', 'between', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']];
  23. }
  24. if (!empty($param['platform_id'])) {
  25. $where_ladder[] = ['gp.platform_code', '=', $param['platform_id']];
  26. $where_good[] = ['gp.platform_code', '=', $param['platform_id']];
  27. }
  28. if (!empty($param['status'])) {
  29. $where_ladder[] = ['g.status', '=', $param['status']];
  30. $where_good[] = ['g.status', '=', $param['status']];
  31. }
  32. $data = [];
  33. //产品价格
  34. $rs_ladder = Db::name('good_ladder')
  35. ->alias('gl')
  36. ->field('g.good_name 商品名称,gl.market_price 市场价,gl.origin_rate 税率,g.moq 起订量,gl.sale_price 售价,gl.skuCode 商品编码,gl.cost_fee 工艺费,gl.market_platform 对比平台')
  37. ->where($where_ladder)
  38. ->leftJoin('good_platform gp', 'gp.skuCode=gl.skuCode AND gp.is_del=0')
  39. ->leftJoin('good g', 'g.spuCode=gp.spuCode AND g.is_del=0')
  40. ->order('gl.id')
  41. ->select()
  42. ->toArray();
  43. if (!empty($rs_ladder)) {
  44. $data[] = [
  45. 'head' => array_keys($rs_ladder[0]),
  46. 'list' => $rs_ladder,
  47. 'filename' => '产品价格' . date('YmdHis'),
  48. ];
  49. }
  50. //产品
  51. $rs_temp_good = Db::name('good')
  52. ->alias('g')
  53. ->field('"" 一级分类,"" 二级分类, g.cat_id 三级分类,g.good_name 商品名称,g.good_type 商品类型,g.brand_id 商品品牌,\'\' 型号,g.origin_place 产地,g.good_unit 计量单位,g.weight 重量g,\'\' 响应时间,g.lead_time 供货周期,g.good_size 商品尺寸,g.packing_size 装箱尺寸,g.packing_way 包装方式,g.packing_spec 装箱规格,g.packing_list 包装清单,g.delivery_place 发货地,g.delivery_day 物流时间,gp.skuCode 商品编码,g.spuCode')
  54. ->where($where_good)
  55. ->leftJoin('good_platform gp', 'gp.spuCode=g.spuCode AND gp.is_del=0')
  56. ->order('g.id')
  57. ->select()
  58. ->toArray();
  59. $all_good_type = [1 => '定制商品', 2 => '常规商品'];
  60. $all_brand = Db::name('brand')->whereIn('id', array_column($rs_temp_good, '商品品牌'))->where('is_del', 0)->column('brand_name', 'id');
  61. $all_cat = Db::name('cat')
  62. ->alias('c3')
  63. ->whereIn('c3.id', array_column($rs_temp_good, '三级分类'))
  64. ->where('c3.is_del', 0)
  65. ->leftJoin('cat c2', 'c2.id=c3.pid AND c2.is_del=0')
  66. ->leftJoin('cat c1', 'c1.id=c2.pid AND c1.is_del=0')
  67. ->column('c3.cat_name cat_name_3,c2.cat_name cat_name_2,c1.cat_name cat_name_1', 'c3.id');
  68. $all_unit = Db::name('unit')->whereIn('id', array_column($rs_temp_good, '计量单位'))->where('is_del', 0)->column('unit', 'id');
  69. foreach ($rs_temp_good as &$value) {
  70. $value['商品品牌'] = isset($all_brand[$value['商品品牌']]) ? $all_brand[$value['商品品牌']] : '';
  71. $value['商品类型'] = isset($all_good_type[$value['商品类型']]) ? $all_good_type[$value['商品类型']] : '';
  72. $value['一级分类'] = isset($all_cat[$value['三级分类']]['cat_name_1']) ? $all_cat[$value['三级分类']]['cat_name_1'] : '';
  73. $value['二级分类'] = isset($all_cat[$value['三级分类']]['cat_name_2']) ? $all_cat[$value['三级分类']]['cat_name_2'] : '';
  74. $value['三级分类'] = isset($all_cat[$value['三级分类']]['cat_name_3']) ? $all_cat[$value['三级分类']]['cat_name_3'] : '';
  75. $temp = explode(',', $value['产地']);
  76. $value['产地'] = GetAddr(json_encode(['provice_code' => isset($temp[0]) ? $temp[0] : '', 'city_code' => isset($temp[1]) ? $temp[1] : '', 'area_code' => isset($temp[2]) ? $temp[2] : '']));
  77. $value['计量单位'] = isset($all_unit[$value['计量单位']]) ? $all_unit[$value['计量单位']] : '';
  78. $temp_2 = explode(',', $value['发货地']);
  79. $value['发货地'] = GetAddr(json_encode(['provice_code' => isset($temp_2[0]) ? $temp_2[0] : '', 'city_code' => isset($temp_2[1]) ? $temp_2[1] : '', 'area_code' => isset($temp_2[2]) ? $temp_2[2] : '']));
  80. $value['型号'] = Db::name('good_spec')
  81. ->alias('gp')
  82. ->field('')
  83. ->leftJoin('specs s', 's.id=gp.spec_id AND s.is_del=0')
  84. ->leftJoin('spec_value sv', 'sv.id=gp.spec_value_id AND s.is_del=0')
  85. ->where([
  86. 'gp.spuCode' => $value['spuCode'],
  87. 'gp.is_del' => 0,
  88. 's.spec_name' => '型号',
  89. ])
  90. ->value('sv.spec_value', '');
  91. $value['响应时间'] = Db::name('good_spec')
  92. ->alias('gp')
  93. ->field('')
  94. ->leftJoin('specs s', 's.id=gp.spec_id AND s.is_del=0')
  95. ->leftJoin('spec_value sv', 'sv.id=gp.spec_value_id AND s.is_del=0')
  96. ->where([
  97. 'gp.spuCode' => $value['spuCode'],
  98. 'gp.is_del' => 0,
  99. 's.spec_name' => '响应时间',
  100. ])
  101. ->value('sv.spec_value', '');
  102. unset($value['spuCode']);
  103. }
  104. if (!empty($rs_temp_good)) {
  105. $data[] = [
  106. 'head' => array_keys($rs_temp_good[0]),
  107. 'list' => $rs_temp_good,
  108. 'filename' => '产品' . date('YmdHis'),
  109. ];
  110. }
  111. if (empty($data)) return error_show(1005, '没有可供导出的数据');
  112. else excelSaveBatch($data);
  113. }
  114. //【一、采购日报表】1.咨询单总数
  115. public function zixunTotal()
  116. {
  117. $param = $this->request->only([
  118. 'token',
  119. 'start_date' => date('Y-m-d'),
  120. 'end_date' => date('Y-m-d'),
  121. 'page' => 1,
  122. 'size' => 15,
  123. ], 'post', 'trim');
  124. $val_params = Validate::rule([
  125. 'start_date' => 'require|date|elt:end_date',
  126. 'end_date' => 'require|date',
  127. ]);
  128. if (!$val_params->check($param)) return error_show(1004, $val_params->getError());
  129. $count = Db::name('good_zixun')
  130. ->alias('gz')
  131. ->field('count(gz.id) total,DATE_FORMAT(gz.addtime,"%Y-%m-%d") addtime,du.itemid,ci.name')
  132. ->leftJoin('depart_user du', 'du.uid=gz.createrid AND du.is_del=0')
  133. ->leftJoin('company_item ci', 'ci.id=du.itemid AND ci.is_del=0')
  134. ->where('gz.is_del', 0)
  135. ->group('addtime,du.itemid,ci.name')
  136. ->whereBetween('gz.addtime', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59'])
  137. ->count();
  138. $list = Db::name('good_zixun')
  139. ->alias('gz')
  140. ->field('count(gz.id) total,DATE_FORMAT(gz.addtime,"%Y-%m-%d") addtime,du.itemid,ci.name')
  141. ->leftJoin('depart_user du', 'du.uid=gz.createrid AND du.is_del=0')
  142. ->leftJoin('company_item ci', 'ci.id=du.itemid AND ci.is_del=0')
  143. ->where('gz.is_del', 0)
  144. ->group('addtime,du.itemid,ci.name')
  145. ->whereBetween('gz.addtime', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59'])
  146. ->order('addtime')
  147. ->page($param['page'], $param['size'])
  148. ->select()
  149. ->toArray();
  150. return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
  151. }
  152. //【一、采购日报表】2.采购订单总金额
  153. public function purcheaseOrderSum()
  154. {
  155. $param = $this->request->only([
  156. 'token',
  157. 'start_date' => date('Y-m-d'),
  158. 'end_date' => date('Y-m-d'),
  159. 'page' => 1,
  160. 'size' => 15,
  161. ], 'post', 'trim');
  162. $val_params = Validate::rule([
  163. 'start_date' => 'date|elt:end_date',
  164. 'end_date' => 'date',
  165. ]);
  166. if (!$val_params->check($param)) return error_show(1004, $val_params->getError());
  167. $rs = Db::name('purchease_order')
  168. ->alias('po')
  169. ->leftJoin('depart_user du', 'du.uid=po.cgder_id AND du.is_del=0')
  170. ->leftJoin('company_item ci', 'ci.id=du.itemid AND ci.is_del=0')
  171. ->where('po.is_del', 0)
  172. ->order('addtime,itemid')
  173. ->whereBetween('po.addtime', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']);
  174. //统计条数的时候,不能按照status分组,否则下方列表不准
  175. $count = $rs
  176. ->field('DATE_FORMAT(po.addtime,"%Y-%m-%d") addtime,SUM(po.total_fee) total_fee,SUM(po.good_num) good_num,du.itemid,ci.name,"" wait_total_fee,"" wait_good_num')
  177. ->group('addtime,du.itemid')
  178. ->count();
  179. $list = $rs
  180. ->field('DATE_FORMAT(po.addtime,"%Y-%m-%d") addtime,SUM(po.total_fee) total_fee,SUM(po.good_num) good_num,po.status,du.itemid,ci.name,"" wait_total_fee,"" wait_good_num')
  181. ->page($param['page'],$param['size'])
  182. ->group('addtime,du.itemid,po.status')
  183. ->cursor();
  184. $data = [];
  185. foreach ($list as $value) {
  186. if (!isset($data[$value['addtime']][$value['itemid']])) {
  187. $data[$value['addtime']][$value['itemid']] = [
  188. 'addtime' => $value['addtime'],
  189. 'itemid' => $value['itemid'],
  190. 'name' => $value['name'],
  191. 'total_fee' => 0,
  192. 'good_num' => 0,
  193. 'wait_total_fee' => 0,
  194. 'wait_good_num' => 0,
  195. ];
  196. }
  197. if ($value['status'] == 0) {
  198. $data[$value['addtime']][$value['itemid']]['wait_total_fee'] += $value['total_fee'];
  199. $data[$value['addtime']][$value['itemid']]['wait_good_num'] += $value['good_num'];
  200. } else {
  201. $data[$value['addtime']][$value['itemid']]['total_fee'] += $value['total_fee'];
  202. $data[$value['addtime']][$value['itemid']]['good_num'] += $value['good_num'];
  203. }
  204. }
  205. //去除下标
  206. $da = [];
  207. foreach ($data as $v) {
  208. foreach ($v as $vvv) {
  209. $da[] = $vvv;
  210. }
  211. }
  212. return app_show(0, '请求成功', ['list' => $da, 'count' => $count]);
  213. }
  214. //【一、采购日报表】3.采购员回复咨询单数
  215. public function consultBidsSum()
  216. {
  217. $param = $this->request->only([
  218. 'token',
  219. 'start_date' => date('Y-m-d'),
  220. 'end_date' => date('Y-m-d'),
  221. 'page' => 1,
  222. 'size' => 15,
  223. ], 'post', 'trim');
  224. $val_params = Validate::rule([
  225. 'start_date' => 'date|elt:end_date',
  226. 'end_date' => 'date',
  227. ]);
  228. if (!$val_params->check($param)) return error_show(1004, $val_params->getError());
  229. $count = Db::name('consult_bids')
  230. ->alias('cb')
  231. ->field('DATE_FORMAT(cb.addtime,"%Y-%m-%d") addtime,du.itemid,cb.createrid,SUM(c.num) num,COUNT(cb.id) total,du.nickname,ci.name')
  232. ->leftJoin('consult c', 'c.zxNo=cb.zxNo AND c.is_del=0')
  233. ->leftJoin('depart_user du', 'du.uid=cb.createrid AND du.is_del=0')
  234. ->leftJoin('company_item ci', 'ci.id=du.itemid AND ci.is_del=0')
  235. ->where('cb.is_del', 0)
  236. ->whereBetween('cb.addtime', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59'])
  237. ->group('addtime,du.itemid,ci.name ,cb.createrid,du.nickname')
  238. ->order('addtime,du.itemid,ci.name ,cb.createrid,du.nickname')
  239. ->count();
  240. $list = Db::name('consult_bids')
  241. ->alias('cb')
  242. ->field('DATE_FORMAT(cb.addtime,"%Y-%m-%d") addtime,du.itemid,cb.createrid,SUM(c.num) num,COUNT(cb.id) total,du.nickname,ci.name')
  243. ->leftJoin('consult c', 'c.zxNo=cb.zxNo AND c.is_del=0')
  244. ->leftJoin('depart_user du', 'du.uid=cb.createrid AND du.is_del=0')
  245. ->leftJoin('company_item ci', 'ci.id=du.itemid AND ci.is_del=0')
  246. ->where('cb.is_del', 0)
  247. ->whereBetween('cb.addtime', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59'])
  248. ->group('addtime,du.itemid,ci.name ,cb.createrid,du.nickname')
  249. ->order('addtime,du.itemid,ci.name ,cb.createrid,du.nickname')
  250. ->page($param['page'],$param['size'])
  251. ->select()
  252. ->toArray();
  253. $list[] = ['addtime' => '汇总', 'itemid' => 0, 'createrid' => 0, 'num' => array_sum(array_column($list, 'num')), 'total' => array_sum(array_column($list, 'total')), 'nickname' => '', 'name' => ''];
  254. return app_show(0, '请求成功', ['list'=>$list,'count'=>$count]);
  255. }
  256. //【一、采购日报表】4.采购员订单金额
  257. public function purcheaseOrderSumByUser()
  258. {
  259. $param = $this->request->only([
  260. 'token',
  261. 'start_date' => date('Y-m-d'),
  262. 'end_date' => date('Y-m-d'),
  263. 'page'=>1,
  264. 'size'=>15
  265. ], 'post', 'trim');
  266. $val_params = Validate::rule([
  267. 'start_date' => 'date|elt:end_date',
  268. 'end_date' => 'date',
  269. ]);
  270. if (!$val_params->check($param)) return error_show(1004, $val_params->getError());
  271. $count = Db::name('purchease_order')
  272. // ->field('DATE_FORMAT(addtime, "%Y-%m-%d") addtime,cgder_id,cgder,SUM(total_fee) total_fee,SUM(good_num) good_num,0 wait_total_fee,0 wait_good_num')
  273. ->where('is_del', 0)
  274. ->group('addtime,cgder_id')
  275. ->order('addtime,cgder_id')
  276. ->whereBetween('addtime', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59'])
  277. ->count('id');
  278. $rs = Db::name('purchease_order')
  279. ->field('DATE_FORMAT(addtime, "%Y-%m-%d") addtime,cgder_id,cgder,SUM(total_fee) total_fee,SUM(good_num) good_num,status,0 wait_total_fee,0 wait_good_num')
  280. ->where('is_del', 0)
  281. ->group('addtime,cgder_id,cgder,status')
  282. ->order('addtime,cgder_id')
  283. ->whereBetween('addtime', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59'])
  284. ->cursor();
  285. $data = [];
  286. foreach ($rs as $value) {
  287. if (!isset($data[$value['addtime']][$value['cgder_id']])) {
  288. $data[$value['addtime']][$value['cgder_id']] = [
  289. 'addtime' => $value['addtime'],
  290. 'cgder' => $value['cgder'],
  291. 'total_fee' => 0.00,
  292. 'good_num' => 0,
  293. 'wait_total_fee' => 0.00,
  294. 'wait_good_num' => 0,
  295. ];
  296. }
  297. if ($value['status'] == 0) {
  298. $data[$value['addtime']][$value['cgder_id']]['wait_total_fee'] += $value['total_fee'];
  299. $data[$value['addtime']][$value['cgder_id']]['wait_good_num'] += $value['good_num'];
  300. } else {
  301. $data[$value['addtime']][$value['cgder_id']]['total_fee'] += $value['total_fee'];
  302. $data[$value['addtime']][$value['cgder_id']]['good_num'] += $value['good_num'];
  303. }
  304. }
  305. $da = [];
  306. //去除下标
  307. foreach ($data as $v) {
  308. foreach ($v as $vv) {
  309. $da[] = $vv;
  310. }
  311. }
  312. return app_show(0, '请求成功', ['list'=>$da,'count'=>$count]);
  313. }
  314. //【二、咨询单报表】1.已采反报价信息
  315. public function consultInfoBidsSum()
  316. {
  317. $param = $this->request->only([
  318. 'token',
  319. 'zxNo' => [],
  320. 'start_date' => date('Y-m-d'),
  321. 'end_date' => date('Y-m-d'),
  322. 'page' => 1,
  323. 'size' => 15,
  324. 'is_export' => 0,//是否导出,1导出,0不导出
  325. ], 'post', 'trim');
  326. $val_params = Validate::rule([
  327. 'start_date' => 'date|elt:end_date',
  328. 'end_date' => 'date',
  329. 'is_export' => 'in:0,1',
  330. ]);
  331. if (!$val_params->check($param)) return error_show(1004, $val_params->getError());
  332. $rs = Db::name('consult_bids')
  333. ->alias('cb')
  334. ->where('cb.is_del', 0);
  335. if (!empty($param['start_date']) && !empty($param['end_date'])) $rs->whereBetween('cb.addtime', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']);
  336. if (!empty($param['zxNo'])) $rs->whereIn('cb.zxNo', $param['zxNo']);
  337. if ($param['is_export'] == 1) {
  338. $data = $rs
  339. ->field('ci.addtime as 咨询时间,cb.bidNo as 采购单反馈单号,cb.zxNo as 咨询订单号,cb.addtime as 回复时间,cb.good_name as 产品名称,s.name as 供应商名称,cb.total_fee 成本合计,cb.delivery_day 物流时间,cb.work_day 产品工期,cb.expire_day 信息有效期,cb.creater 采购员,ci.num 需求数量,ci.arrival_time 要求到货日期,co.salesman 业务人员,csi.companyName 客户名称')
  340. ->leftJoin('consult_info ci', 'ci.zxNo=cb.zxNo AND ci.is_del=0')
  341. ->leftJoin('consult_order co', 'co.zxNo=cb.zxNo AND co.is_del=0')
  342. ->leftJoin('supplier s', 's.code=cb.supplierNo AND s.is_del=0')
  343. ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo AND csi.is_del=0')
  344. ->select()
  345. ->toArray();
  346. if (empty($data)) return error_show(1005, '没有可供导出的数据');
  347. else {
  348. $headerArr = array_keys($data[0]);
  349. excelSave('咨询单报表-已采反报价信息' . date('YmdHis'), $headerArr, $data);
  350. }
  351. } else {
  352. $total = $rs->count('cb.id');
  353. $data = $rs
  354. ->field('cb.id,ci.addtime,cb.bidNo,cb.zxNo,cb.addtime cbaddtime,cb.good_name,s.name supplier,cb.total_fee,cb.delivery_day,cb.work_day,cb.expire_day,cb.creater,ci.num,ci.arrival_time,co.salesman,csi.companyName')
  355. ->leftJoin('consult_info ci', 'ci.zxNo=cb.zxNo AND ci.is_del=0')
  356. ->leftJoin('consult_order co', 'co.zxNo=cb.zxNo AND co.is_del=0')
  357. ->leftJoin('supplier s', 's.code=cb.supplierNo AND s.is_del=0')
  358. ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo AND csi.is_del=0')
  359. ->page($param['page'], $param['size'])
  360. ->select()
  361. ->toArray();
  362. return app_show(0, '请求成功', ['list' => $data, 'total' => $total]);
  363. }
  364. }
  365. //【二、咨询单报表】2.未采反信息
  366. public function consultInfoBidsSumNot()
  367. {
  368. $param = $this->request->only([
  369. 'token',
  370. 'companyName' => '',
  371. 'start_date' => date('Y-m-d'),
  372. 'end_date' => date('Y-m-d'),
  373. 'page' => 1,
  374. 'size' => 15,
  375. 'is_export' => 0,//是否导出,1导出,0不导出
  376. ], 'post', 'trim');
  377. $val_params = Validate::rule([
  378. 'start_date' => 'date|elt:end_date',
  379. 'end_date' => 'date',
  380. 'is_export' => 'in:0,1',
  381. ]);
  382. if (!$val_params->check($param)) return error_show(1004, $val_params->getError());
  383. $rs = Db::name('consult_bids')
  384. ->alias('cb')
  385. ->leftJoin('consult_info ci', 'ci.zxNo=cb.zxNo AND ci.is_del=0')
  386. ->leftJoin('consult_order co', 'co.zxNo=cb.zxNo AND co.is_del=0')
  387. ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo AND csi.is_del=0')
  388. ->where('cb.is_del', 0)
  389. ->order('co.endtime', 'desc');
  390. if ($param['start_date'] != '' && $param['end_date'] != '') $rs->whereBetween('ci.addtime', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']);
  391. if ($param['companyName'] != '') $rs->whereLike('csi.companyName', '%' . $param['companyName'] . '%');
  392. if ($param['is_export'] == 1) {
  393. $data = $rs
  394. ->field('cb.zxNo 咨询订单号,cb.good_name 产品名称,ci.num 需求数量,csi.companyName 客户名称,ci.addtime 咨询时间,co.endtime 截止时间')
  395. ->select()
  396. ->toArray();
  397. if (empty($data)) return error_show(1005, '没有可供导出的数据');
  398. else {
  399. $headerArr = array_keys($data[0]);
  400. excelSave('咨询单报表-未采反信息' . date('YmdHis'), $headerArr, $data);
  401. }
  402. } else {
  403. $total = $rs->count('cb.id');
  404. $data = $rs
  405. ->field('cb.zxNo,cb.good_name,ci.num,csi.companyName,ci.addtime,co.endtime')
  406. ->page($param['page'], $param['size'])
  407. ->select()
  408. ->toArray();
  409. return app_show(0, '请求成功', ['list' => $data, 'total' => $total]);
  410. }
  411. }
  412. //【三、订单明细报表】
  413. public function orderListDetailed()
  414. {
  415. $param = $this->request->only([
  416. 'token',
  417. 'addtime_start' => date('Y-m-d'),
  418. 'addtime_end' => date('Y-m-d'),
  419. 'status' => '',
  420. 'page' => 1,
  421. 'size' => 15,
  422. 'is_export' => 0,//是否导出,1导出,0不导出
  423. ], 'post', 'trim');
  424. $val_params = Validate::rule([
  425. 'start_date' => 'date|elt:end_date',
  426. 'end_date' => 'date',
  427. 'is_export' => 'in:0,1',
  428. ]);
  429. if (!$val_params->check($param)) return error_show(1004, $val_params->getError());
  430. $rs = Db::name('purchease_order')
  431. ->alias('po')
  432. ->leftJoin('business b', 'b.companyNo=po.companyNo AND b.is_del=0');
  433. if ($param['addtime_start'] != '' && $param['addtime_end'] != '') $rs->whereBetween('po.addtime', [$param['addtime_start'] . ' 00:00:00', $param['addtime_end'] . ' 23:59:59']);
  434. if ($param['status'] != '') $rs->where('po.status', '=', $param['status']);
  435. $all_status = ['待与供应商确认', '待入库', '部分入库', '入库完成', '已取消订单'];
  436. if ($param['is_export'] == 1) {
  437. $data = $rs
  438. ->field('po.cgdNo 采购单编号,po.addtime 创建时间,po.supplierNo 供应商编号,po.supplier_name 供应商名称,po.good_name 产品名称,po.good_num 购买数量,po.nake_fee 裸价,po.total_fee 成本合计,po.status 单据状态,po.wsend_num 未发货数量,"" 创建人,b.company 客户名称,po.cgder 采购员,"" 发货时间')
  439. ->withAttr('单据状态', function ($val) use ($all_status) {
  440. return isset($all_status[$val]) ? $all_status[$val] : '';
  441. })
  442. ->select()
  443. ->toArray();
  444. if (empty($data)) return error_show(1005, '没有可供导出的数据');
  445. else {
  446. $headerArr = array_keys($data[0]);
  447. excelSave('订单明细报表' . date('YmdHis'), $headerArr, $data);
  448. }
  449. } else {
  450. $total = $rs->count('po.id');
  451. $data = $rs
  452. ->field('po.cgdNo,po.addtime,po.supplierNo,po.supplier_name,po.good_name,po.good_num,po.nake_fee,po.total_fee,po.status,po.wsend_num,"" 创建人,b.company,po.cgder,"" 发货时间')
  453. ->withAttr('status', function ($val) use ($all_status) {
  454. return isset($all_status[$val]) ? $all_status[$val] : '';
  455. })
  456. ->page($param['page'], $param['size'])
  457. ->select()
  458. ->toArray();
  459. return app_show(0, '请求成功', ['list' => $data, 'total' => $total]);
  460. }
  461. }
  462. }