setName('now_report') ->setDescription('定时处理报表预约,生成报表文件'); } //处理报表预约记录,生成报表文件 protected function execute(Input $input, Output $output) { try { $info = Cache::store('redis')->handler()->rpop($this->key); if ($info) { $info = json_decode($info, true); //不同的方法处理不同的脚本 switch ($info['code']) { //退货台账-业务口径 case 'A': $res = $this->A($info['start'], $info['end']); break; //库存日报及预警汇总表 case 'B': $res = $this->B($info['start'], $info['end']); break; //退货台账 case 'C': $res = $this->C($info['start'], $info['end']); break; // case 'D': $res = $this->D($info['start'], $info['end']); break; case 'E': $res = $this->E($info['start'], $info['end']); break; default: throw new Exception('暂不支持这个报表'); } $file = excelSaveFile($res, $info['name'] . date('YmdHis')); Db::name('exec') ->where(['id' => $info['id'], 'status' => 1])//status==1 待处理 ->update([ 'status' => 2, //status==2 处理完成 'down_url' => $file, 'updatetime' => date('Y-m-d H:i:s'), 'expiretime' => date('Y-m-d H:i:s', strtotime('+7 day')) ]); $output->writeln('【' . $info['id'] . '】该预约记录处理成功'); } else $output->writeln('没有可供处理的报表预约记录'); } catch (Exception $exception) { $output->writeln($exception->getMessage() . '|' . $exception->getFile() . '|' . $exception->getLine()); } } //退货台账-业务口径 private function A(string $start_date = '', string $end_date = '') { $data = Db::name('sale_return') ->alias('sr') ->field('sr.returnCode 退货单号,sr.status 流程进度,sr.addtime 退货发起日期,b.company 公司名称, ci1.name 退货人所在部门,sr.apply_name 退货人,sr.num 退货数量,sr.total_fee 退货销售货款,sr.remark 退货备注,c.companyName 客户名称,or.customer_code 客户编码,"" 一级组织,"" 二级组织,"" 三级组织,p.platform_name 平台名称,s.poNo PO编号,sr.orderCode 确认单编号,s.order_type 确认单类型,s.addtime 确认单下单时间,ci2.name 业务人员所在部门,s.apply_name 业务人员,sr.orderCode 确认单产品编号,sr.good_name 产品名称,sr.good_code 产品编码,"" 一级分类,s.cat_id,c.itemid') ->leftJoin('platform p', 'p.id = sr.platform_id') ->leftJoin('order_return or', 'or.orderCode = sr.orderCode') ->leftJoin('customer_info c', 'c.companyNo = or.customer_code') ->leftJoin('sale s', 's.orderCode = sr.orderCode') ->leftJoin('business b', 'b.companyNo = s.supplierNo') ->leftJoin('depart_user du1', 'du1.uid = sr.apply_id') ->leftJoin('company_item ci1', 'ci1.id = du1.itemid') ->leftJoin('depart_user du2', 'du2.uid = s.apply_id') ->leftJoin('company_item ci2', 'ci2.id = du2.itemid') ->where('sr.is_del', 0) ->whereBetween('sr.addtime', [$start_date, $end_date]) ->order('sr.addtime', 'desc') ->cursor(); $all_sale_return_status = [1 => '待业务审批', 2 => '待专员审批', 3 => '待主管审批', 4 => '退货完成', 5 => '业务驳回', 6 => '采购驳回', 7 => '专员审批不通过']; $all_sale_order_type = [1 => '备库', 2 => '非库存', 3 => '咨询采反', 4 => '项目采反', 5 => '平台部订单销售',]; $list = []; foreach ($data as $value) { $value['流程进度'] = isset($all_sale_return_status[$value['流程进度']]) ? $all_sale_return_status[$value['流程进度']] : ''; $value['确认单类型'] = isset($all_sale_order_type[$value['确认单类型']]) ? $all_sale_order_type[$value['确认单类型']] : ''; $top = made($value['cat_id']); $value['一级分类'] = isset($top[0]['name']) ? $top[0]['name'] : ''; if (!empty($value['itemid'])) { $customer_org1 = get_top_customer_org($value['itemid']); foreach ($customer_org1 as $vv) { switch ($vv['level']) { case 1: $value['一级组织'] = $vv['name']; break; case 2: $value['二级组织'] = $vv['name']; break; case 3: $value['三级组织'] = $vv['name']; break; } } } unset($value['cat_id']); unset($value['itemid']); yield $list[] = $value; } return $list; } //库存预警汇总表 private function B(string $start_date = '', string $end_date = '') { $data = Db::name('good_stock') ->alias('gs') ->field('b.company 公司名称,pi.updatetime 最近一次入库时间,pi.apply_name 采购员,gs.spuCode 产品编码,"" 一级分类,gb.good_name 产品名称,"" 成本单价,gs.wait_out_stock 待出库量,gs.wait_in_stock 待入库量,gs.usable_stock 可用库存数量,gs.total_stock 当前库存量,"" 可用库存金额,"" 当前库存金额,"" 保质期时间,"" 库存天数,po.supplier_name 供应商名称,wi.name 仓库名称,gb.cat_id') ->leftJoin('purchease_in pi', 'pi.wsm_code = gs.wsm_code AND pi.status=4') ->leftJoin('purchease_order po', 'po.cgdNo = pi.cgdNo') ->leftJoin('warehouse_info wi', 'wi.wsm_code = gs.wsm_code') ->leftJoin('business b', 'b.companyNo = po.companyNo') ->leftJoin('good_basic gb', 'gb.spuCode = gs.spuCode') ->where('gs.is_del', 0) ->whereBetween('gs.addtime', [$start_date, $end_date]) ->order('gs.addtime', 'desc') ->cursor(); $list = []; foreach ($data as $value) { $value['一级分类'] = implode('/', array_column(made($value['cat_id']), 'name')); $value['成本单价'] = Db::name('good_nake') ->where('spuCode', $value['产品编码']) ->order('min_num', 'asc') ->value('nake_fee', ''); $value['可用库存金额'] = bcmul((string)$value['可用库存数量'] ?? '0', (string)$value['成本单价'] ?? '0', 2); $value['当前库存金额'] = bcmul((string)$value['当前库存量'] ?? '0', (string)$value['成本单价'] ?? '0', 2); if (!empty($value['最近一次入库时间'])) { $value['保质期时间'] = date('Y-m-d H:i:s', strtotime($value['最近一次入库时间']) + 31536000);//365天之后 $value['库存天数'] = bcdiv((string)(time() - strtotime($value['最近一次入库时间'])), (string)(24 * 3600));//365天之后 } unset($value['cat_id']); yield $list[] = $value; } return $list; } //退货台账 private function C(string $start_date = '', string $end_date = '') { $data = Db::name('sale_return') ->alias('sr') ->field('DATE_FORMAT(sr.addtime,"%Y") 年,DATE_FORMAT(sr.addtime,"%m") 月,DATE_FORMAT(sr.addtime,"%d") 日,b.company 公司名称,sr.returnCode 退货单号,sr.status 流程进度,ci2.name 业务人员所在部门,s.apply_name 业务人员,sr.orderCode 订单编号,p.platform_name 平台名称,s.workNo 平台订单号,"" 一级组织,"" 二级组织,"" 三级组织,c.companyName 客户名称,gp.spuCode 产品编码,sr.good_name 产品名称,"" 一级分类,"" 二级分类,"" 三级分类,cat.fund_code 财务核算码,u.unit 单位,sr.num 退货数量,s.sale_price 销售单价,round(sale_price*sr.num,2) 退货销售货款,sr.remark 退货备注,on.cgdNo 采购单号,po.cgder 采购员,po.nake_fee 成本裸价,po.pakge_fee 包装费,po.delivery_fee 物流费,po.cert_fee 证书费,po.mark_fee 加标费,po.open_fee 开模费,po.diff_weight 工差,po.diff_fee 采购工差金额,po.good_price 采购单价,round(sr.num*po.good_price-po.diff_fee,2) 退货采购货款,gb.tax 采购税点,supp.name 供应商名称,wi.name 库房名称,pi.sendtype 发货方式,supp.pay_type 结算方式,c.itemid,s.cat_id') ->leftJoin('platform p', 'p.id = sr.platform_id') ->leftJoin('order_return or', 'or.orderCode = sr.orderCode') ->leftJoin('customer_info c', 'c.companyNo = or.customer_code') ->leftJoin('sale s', 's.orderCode = sr.orderCode') ->leftJoin('business b', 'b.companyNo = s.supplierNo') ->leftJoin('depart_user du2', 'du2.uid = s.apply_id') ->leftJoin('company_item ci2', 'ci2.id = du2.itemid') ->leftJoin('good_platform gp', 'gp.skuCode = s.skuCode') ->leftJoin('good_basic gb', 'gb.spuCode = gp.spuCode') ->leftJoin('unit u', 'u.id = gb.good_unit') ->leftJoin('order_num on', 'on.orderCode =sr.orderCode') ->leftJoin('cat cat', 'cat.id =s.cat_id') ->leftJoin('purchease_order po', 'po.cgdNo =on.cgdNo') ->leftJoin('purchease_in pi', 'pi.cgdNo =on.cgdNo') ->leftJoin('supplier supp', 'supp.code =s.supplierNo') ->leftJoin('warehouse_info wi', 'wi.wsm_code =po.wsm_code') ->where('sr.is_del', 0) ->whereBetween('sr.addtime', [$start_date, $end_date]) ->order('sr.addtime', 'desc') ->cursor(); $all_sale_return_status = [1 => '待业务审批', 2 => '待专员审批', 3 => '待主管审批', 4 => '退货完成', 5 => '业务驳回', 6 => '采购驳回', 7 => '专员审批不通过']; $all_sendtype = [1 => '公司自提', 2 => '供应商包邮']; $list = []; foreach ($data as $value) { $value['流程进度'] = isset($all_sale_return_status[$value['流程进度']]) ? $all_sale_return_status[$value['流程进度']] : ''; $value['发货方式'] = isset($all_sendtype[$value['发货方式']]) ? $all_sendtype[$value['发货方式']] : ''; $top = made($value['cat_id']); $value['一级分类'] = isset($top[0]['name']) ? $top[0]['name'] : ''; $value['二级分类'] = isset($top[1]['name']) ? $top[1]['name'] : ''; $value['三级分类'] = isset($top[2]['name']) ? $top[2]['name'] : ''; if (!empty($value['itemid'])) { $customer_org1 = get_top_customer_org($value['itemid']); foreach ($customer_org1 as $vv) { switch ($vv['level']) { case 1: $value['一级组织'] = $vv['name']; break; case 2: $value['二级组织'] = $vv['name']; break; case 3: $value['三级组织'] = $vv['name']; break; } } } unset($value['cat_id']); unset($value['itemid']); yield $list[] = $value; } return $list; } public function D($start,$end){ $list =Db::name("order_out")->alias('woo')->leftJoin("sale a","a.orderCode=woo.orderCode") ->leftJoin("order_back wor","wor.outCode=woo.outCode and wor.status=4") ->leftJoin("order_num won","won.orderCode=a.orderCode") ->leftJoin("purchease_order wpo","wpo.cgdNo=won.cgdNo") ->leftJoin("good_basic wgb","wgb.spuCode=wpo.spuCode") ->leftJoin("good_zixun wgz","wgz.spuCode=wpo.spuCode") ->leftJoin("supplier ws","ws.code=wpo.supplierNo") ->where('woo.status',">=", 2) ->whereBetween('woo.addtime', [$start, $end]) ->field(" year(`a`.`addtime`) AS `年`,month(`a`.`addtime`) AS `月`,dayofmonth(`a`.`addtime`) AS `日`,a.supplierNo as '公司名称', a.apply_id '业务部门名称' ,a.apply_name as '业务人员',a.order_type as '订单类型',a.good_type as '商品类型',woo.outCode '发货编号', `woo`.`send_num` AS `本次发货数量`, `woo`.`sendtime` AS `本次发货时间`, `woo`.`orderCode` AS `确认单编号`, `a`.`good_code` AS `商品编号`,'' as '一级分类','' as '二级分类',a.cat_id as '三级分类', '' as '财务核算编码',a.good_name as '商品名称', ifnull( `wgb`.`good_unit`, `wgz`.`good_unit` ) as '单位', `a`.`good_num` AS `下单数量`, `a`.`total_price` AS `销售货款`, `wpo`.`cgdNo` AS `采购单单号`, `wpo`.`cgder` AS `采购员`, `wpo`.`nake_fee` AS `采购裸价`, `wpo`.`mark_fee` AS `加标费`, `wpo`.`pakge_fee` AS `包装费`, `wpo`.`cert_fee` AS `证书费`, `wpo`.`open_fee` AS `开模费`, `wpo`.`teach_fee` AS `工艺费`, `wpo`.`delivery_fee` AS `物流费`, `wpo`.`good_price` AS `成本合计`, `wpo`.`total_fee` AS `采购货款`, ifnull( `wgb`.`tax`, `wgz`.`tax` ) AS `采购税点`, `ws`.`name` AS `供应商名称`, `wgb`.`is_stock` as '是否库存品', if (`wgb`.`is_stock`=1,wpo.bkcode,wpo.cgdNo) as '发货方式', ifnull( `wor`.`thNo`, '' ) AS `退货编号`, ifnull( `wor`.`return_num`, '' ) AS `退货数量`, round(( ifnull( `wor`.`return_num`, 0 ) * `a`.`sale_price` ), 2 ) as '退货金额', '' as '售前退货数量', `a`.`sale_price` as '售前退货金额' ")->cursor(); $order_tyepe=["库存销售",'非库存销售','咨询销售','项目销售','平台库存销售','平台非库存销售']; $good_type=["常规商品",'赠品','样品']; $data=[]; foreach($list as $value){ $value['公司名称'] = Db::name("business")->where(["companyNo"=>$value['公司名称']])->value('company',''); $value['业务部门名称'] = Db::name("depart_user")->alias("a")->leftJoin("company_item ci","a.itemid=ci.id") ->where(["a.uid"=>$value['业务部门名称'],"a.status"=>1,"a.is_del"=>0])->value('ci.name',''); $value['订单类型'] = $order_tyepe[ $value['订单类型']-1]; $value['商品类型'] = $good_type[ $value['商品类型']-1]; $cat =made($value['三级分类']); $value['财务核算编码'] =Db::name("cat")->where(["id"=>$value['三级分类']])->value("fund_code",''); $value['一级分类']=isset($cat[0]['name'])?$cat[0]['name']:""; $value['二级分类']=isset($cat[1]['name'])?$cat[1]['name']:""; $value['三级分类']=isset($cat[2]['name'])?$cat[2]['name']:""; $value['单位']=Db::name("unit")->where(["id"=>$value['单位']])->value("unit",''); if($value['是否库存品']==1){ $sendtype = Db::name("purchease_order")->alias("a")->leftJoin("purchease_in b","a.cgdNo=b.cgdNo") ->where(["bkcode"=>$value['发货方式'],"order_type"=>[1,5]])->order("b.addtime desc")->value("b .sendtype",''); $value['是否库存品']='是'; }else{ $sendtype = Db::name("purchease_order")->alias("a")->leftJoin("purchease_in b","a.cgdNo=b.cgdNo") ->where(["a.cgdNo"=>$value['发货方式']])->order("b.addtime desc")->value("b.sendtype",''); $value['是否库存品']='否'; } $value['发货方式'] = $sendtype=1 ? '包邮':$sendtype=2 ? '自提':""; $value['售前退货数量'] = Db::name("sale_return")->where(["orderCode"=>$value['确认单编号']])->sum('num'); $value['售前退货金额'] =round($value['售前退货数量']* $value['售前退货金额'],2); yield $data[] = $value; } return $data; } public function E($start,$end){ $list =Db::name("purchease_in")->alias('wpi')->leftJoin("purchease_order wpo","wpo.cgdNo=wpi.cgdNo") ->leftJoin("purchease wp","wpo.bkcode=wp.bk_code") ->leftJoin("good_basic wgb","wgb.spuCode=wpo.spuCode") ->leftJoin("warehouse_info wwi","wwi.wsm_code = wpo.wsm_code") ->leftJoin("supplier ws","ws.code=wpo.supplierNo") ->where('wpi.status',"in", [4,6]) ->where('wpo.order_type',"=",1) ->whereBetween('wpi.addtime', [$start, $end]) ->field(" wpo.companyNo '业务公司', bkcode '备库单号', wpi.cgdNo '采购单号', wpo.skuCode '商品编号', '' as '一级分类', '' as '二级分类', wgb.cat_id as '三级分类', wgb.cat_id as '财务核算编号', wpo.good_name as '商品名称', wpi.send_num '入库数量', round(wpi.send_num* good_price,2) '入库金额', wpo.updatetime '本次入库时间', wgb.tax/100 '采购税率', '' as '不含税入库金额', '' as '税额', wp.apply_id as '业务部门', wp.apply_name as '业务员', wpo.cgder as '采购员', nake_fee '成本裸价', wsm_type '仓库类型', ws.name '供应商名称', wwi.name '仓库名称' ")->cursor(); $data=[]; foreach($list as $value){ $value['业务公司'] = Db::name("business")->where(["companyNo"=>$value['业务公司']])->value('company',''); $value['业务部门'] = Db::name("depart_user")->alias("a")->leftJoin("company_item ci","a.itemid=ci.id") ->where(["a.uid"=>$value['业务部门'],"a.status"=>1,"a.is_del"=>0])->value('ci.name',''); $cat =made($value['三级分类']); $value['财务核算编号'] =Db::name("cat")->where(["id"=>$value['三级分类']])->value("fund_code",''); $value['一级分类']=isset($cat[0]['name'])?$cat[0]['name']:""; $value['二级分类']=isset($cat[1]['name'])?$cat[1]['name']:""; $value['三级分类']=isset($cat[2]['name'])?$cat[2]['name']:""; $value['不含税入库金额']= round($value['入库金额']/(1+$value['采购税率']),2); $value['税额']= round(($value['入库金额']/(1+$value['采购税率'])) *$value['采购税率'],2); $value['仓库类型'] = Db::name("warehouse_type")->where(["id"=>$value['仓库类型']])->value("name",''); yield $data[] = $value; } return $data; } }