1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237 |
- <?php
- declare (strict_types=1);
- namespace app\command;
- use app\admin\common\User;
- use think\console\Command;
- use think\console\Input;
- use think\console\Output;
- use think\Exception;
- use think\facade\Cache;
- use think\facade\Db;
- class NowReportHandle extends Command
- {
-
- private $key = 'cxreport';
- protected function configure()
- {
-
- $this->setName('"cxreport"')
- ->setDescription('定时处理报表预约,生成报表文件');
- }
-
- protected function execute(Input $input, Output $output)
- {
- ini_set("memory_limit", "516M");
- $info = Cache::store('redis')->handler()->rpop($this->key);
- try {
- 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;
-
- case 'F':
- $res = $this->F($info['start'], $info['end']);
- break;
-
- case 'G':
- $res = $this->G($info['start'], $info['end']);
- break;
-
- case 'H':
- $res = $this->H($info['start'], $info['end']);
- break;
-
- case 'I':
- $res = $this->I($info['start'], $info['end']);
- break;
-
- case 'J':
- $res = $this->J($info['start'], $info['end']);
- break;
-
- case 'K':
- $res = $this->K($info['start'], $info['end']);
- break;
-
- case 'L':
- $res = $this->NewOut($info['start'], $info['end']);
- break;
-
- case 'M':
- $res = $this->EveryDayStock($info['start'], $info['end']);
- break;
-
- case 'N':
- $res = $this->GetGoodNake();
- break;
-
- case 'P':
- $res = $this->GetGoodLadder();
- break;
- case 'Q':
- $res = $this->GetBid($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])
- ->update([
- 'status' => 2,
- 'down_url' => $file,
- 'updatetime' => date('Y-m-d H:i:s'),
- 'expiretime' => date('Y-m-d H:i:s', strtotime('+7 day'))
- ]);
-
- }
- } catch (Exception $exception) {
- Db::name('exec')
- ->where(['id' => $info['id'], 'status' => 1])
- ->update([
- 'status' => 0,
- 'down_url' => '',
- 'updatetime' => date('Y-m-d H:i:s'),
- 'expiretime' => date('Y-m-d H:i:s', strtotime('+7 day'))
- ]);
- $output->writeln($exception->getMessage() . '|' . $exception->getFile() . '|' . $exception->getLine());
- }
- }
-
- private function A(string $start_date = '', string $end_date = '')
- {
- $data = Db::name('order_back')
- ->alias('ob')
- ->field('ob.thNo 退货单号,ob.status 流程进度,ob.addtime 退货发起日期,"" 退货人所在部门,ob.apply_name 退货人,ob.return_num 退货数量,ob.total_fee 退货销售货款,ob.remark 退货备注,"" 客户名称,ob.customer_code 客户编码,"" 一级组织,"" 二级组织,"" 三级组织,p.platform_name 平台名称,s.poNo PO编号,s.orderCode 确认单编号,s.order_type 确认单类型,s.addtime 确认单下单时间,"" 业务人员所在部门,ob.cgder 业务人员,s.good_code 确认单产品编号,ob.good_name 产品名称,s.skuCode 产品编码,"" 一级分类,0 售前记录总数,ob.apply_id,ob.cgderid,s.cat_id')
- ->leftJoin('sale s', 's.orderCode=ob.orderCode')
- ->leftJoin('platform p', 'p.id=ob.platform_id')
- ->where(['ob.is_del' => 0, 'ob.status' => 4])
- ->whereBetween('ob.addtime', [$start_date, $end_date])
- ->cursor();
- $all_status = [1 => '待业务审批', 2 => '待专员审批', 3 => '待主管审批', 4 => '退货完成', 5 => '业务驳回', 6 => '采购驳回', 7 => '专员审批不通过'];
- $all_order_type = [1 => '备库', 2 => '非库存', 3 => '咨询采反', 4 => '项目采反', 5 => '平台部订单库存品', 6 => '平台部订单非库存品'];
- $list = [];
- $tmp_customer = $tmp = [];
- $userCommon = User::getIns();
- foreach ($data as $value) {
- $value['流程进度'] = isset($all_status[$value['流程进度']]) ? $all_status[$value['流程进度']] : '';
- if (!isset($tmp_customer[$value['customer_code']])) {
- $temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
- $tmp_customer[$value['customer_code']] = ['companyName' => $temp['data']['companyName'] ?? '', 'itemid' => $temp['data']['itemid'] ?? ''];
- }
- $value['客户名称'] = $tmp_customer[$value['customer_code']]['companyName'];
- $value['itemid'] = $tmp_customer[$value['customer_code']]['itemid'];
- 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;
- }
- }
- }
- if (!isset($tmp[$value['apply_id']])) $tmp[$value['apply_id']] = get_company_name_by_uid($value['apply_id']);
- if (!isset($tmp[$value['cgderid']])) $tmp[$value['cgderid']] = get_company_name_by_uid($value['cgderid']);
- $value['退货人所在部门'] = $tmp[$value['apply_id']] ?? '';
- $value['业务人员所在部门'] = $tmp[$value['cgderid']] ?? '';
- $value['确认单类型'] = isset($all_order_type[$value['确认单类型']]) ? $all_order_type[$value['确认单类型']] : '';
- $top = made($value['cat_id']);
- $value['一级分类'] = isset($top[0]['name']) ? $top[0]['name'] : '';
- $value['售前记录总数'] = Db::name('sale_return')->where(['orderCode' => $value['确认单编号'], 'is_del' => 0, 'status' => 5])->count('id');
- unset($value['cat_id']);
- unset($value['itemid']);
- unset($value['apply_id']);
- unset($value['cgderid']);
- yield $list[] = $value;
- }
- return $list;
- }
-
- private function B(string $start_date = '', string $end_date = '')
- {
- $data = Db::name('good_stock')
- ->alias('wgs')
- ->field(" wwi.companyName as '公司名称',
- wpo.bkcode as '备库单号',
- wpo.cgdNo as '采购单号',
- wp.apply_id as '申请部门',
- wp.apply_name as '申请人',
- cat_id '一级分类',
- wgb.good_name '产品名称',
- wp.good_num '备库数量',
- wgs.spuCode '产品编号',
- wpo.good_price as '成本单价',
- wait_in_stock '待入库存数量',
- wait_out_stock '待出库存数量',
- usable_stock '可用库存数量',
- wait_out_stock+usable_stock '当前库存数量',
- '' as '可用库存金额',
- '' as '当前库存金额',
- '' as '保质期时间',
- '' as '库存天数',
- wgb.creater as 'CGD.采购员',
- wgb.supplierName as '供应商名称',
- wwi.`name` as '仓库名称',
- wp.addtime as '备库创建时间' ,
- ")
- ->leftJoin('purchease_order wpo', 'wgs.spuCode = wpo.spuCode and order_source=0')
- ->leftJoin('purchease wp', 'wp.bk_code = wpo.bkcode')
- ->leftJoin('warehouse_info wwi', 'wwi.wsm_code = wgs.wsm_code')
- ->leftJoin('good_basic wgb', 'wgb.spuCode = wgs.spuCode')
- ->where('wgs.is_del', 0)
- ->where('wgb.is_stock', 1)
- ->whereBetween('wgs.updatetime', [$start_date, $end_date])
- ->order('wgs.updatetime', 'desc')
- ->cursor();
- $list = $tmp = [];
- foreach ($data as $value) {
- $cat = made($value['一级分类']);
- $value['申请部门'] = intval($value['申请部门']);
- if (!isset($tmp[$value['申请部门']])) $tmp[$value['申请部门']] = get_company_name_by_uid($value['申请部门']);
- $value['申请部门'] = $tmp[$value['申请部门']] ?? '';
- $value['一级分类'] = isset($cat[0]['name']) ? $cat[0]['name'] : "";
- $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);
- $value['库存天数'] = bcdiv((string)(time() - strtotime($value['备库创建时间'])), (string)(24 * 3600));
- }
- yield $list[] = $value;
- }
- return $list;
- }
-
- private function C(string $start_date = '', string $end_date = '')
- {
- $data = Db::name('th_data')
- ->alias('wtd')
- ->leftJoin('sale_return wsr', 'wtd.thCode = wsr.returnCode and wtd.th_type=1')
- ->leftJoin('order_return wor', 'wtd.thCode = wor.returnCode and wtd.th_type in (2,3)')
- ->leftJoin('sale ws', 'ws.orderCode=wtd.orderCode and ws.is_del=0')
- ->leftJoin('order_num won', 'won.orderCode=wtd.orderCode')
- ->leftJoin('purchease_order wpo', 'wpo.cgdNo=won.cgdNo and wpo.is_del=0')
- ->whereBetween('wtd.addtime', [$start_date, $end_date])
- ->field(" year(wtd.addtime) '年',
- month(wtd.addtime) '月',
- DAYOFMONTH(wtd.addtime) '日',
- wtd.addtime '退货单创建时间',
- wsr.companyName as '公司名称',
- thCode '退货单号',
- if(wtd.th_type=1,'售前','售后') '退货类型',
- '已完成' as '流程进度',
- wtd.apply_id '业务部门',
- wtd.apply_name '业务人员',
- wtd.orderCode '订单编号',
- ws.platform_id '平台类型',
- ws.platform_order '平台订单号',
- '' as '客户属性',
- wtd.spuCode '产品编码',
- '' as '财务核算码',
- '' as `一级分类`,
- '' as `二级分类`,
- wtd.cat_id as `三级分类`,
- wtd.good_name as '商品名称',
- ws.order_type as `单位`,
- wtd.th_num '退货数量',
- ws.sale_price '销售单价',
- wtd.th_fee '退货金额',
- ifnull(wsr.remark,wor.error_remark) as '退货备注',
- won.cgdNo '采购单单号',
- wpo.cgder '采购员',
- wpo.nake_fee '采购裸价',
- wpo.pakge_fee '包装费',
- wpo.delivery_fee '物流费',
- wpo.cert_fee '证书费',
- wpo.mark_fee '加标费',
- wpo.open_fee '开模费',
- wpo.diff_weight '工差',
- wpo.diff_fee '采购工差金额',
- wpo.good_price '成本合计',
- (wpo.good_price * wtd.th_num) '退货采购货款',
- '' as '税点',
- wpo.supplier_name '供应商名称',
- if(ws.is_stock=1,'是','否') '是否库存',
- '' as '发货方式',
- if(ifnull(wsr.is_th,wor.is_th)=0,'否','是') as '供应商是否同意退货',
- '' as '付款方式',
- ws.customer_code
- ")->order("wtd.addtime")->cursor();
- $platform = Db::name('platform')->where('is_del', 0)->column('platform_name', 'id');
- $fund_code = Db::name('cat')->where(['is_del' => 0, 'level' => 3])->column('fund_code', 'id');
- $userCommon = User::getIns();
- $temp_customer = $tmp = [];
- $com = [];
- foreach ($data as $value) {
- if (!isset($temp_customer[$value['customer_code']])) {
- $tmp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
- $temp_customer[$value['customer_code']] = [
- '分公司' => $tmp['data']['itemid'] ?? '',
- '客户名称' => $tmp['data']['companyName'] ?? '',
- ];
- }
- $value['分公司'] = $temp_customer[$value['customer_code']]['分公司'] ?? '';
- $value['客户名称'] = $temp_customer[$value['customer_code']]['客户名称'] ?? '';
- $value['业务部门'] = intval($value['业务部门']);
- if (!isset($tmp[$value['业务部门']])) $tmp[$value['业务部门']] = get_company_name_by_uid($value['业务部门']);
- $value['业务部门'] = $tmp[$value['业务部门']] ?? '';
- $value['财务核算码'] = $fund_code[$value['三级分类']] ?? '';
- $comp = get_top_customer_org($value['分公司']);
- $value['客户属性'] = isset($comp[0]['name']) ? $comp[0]['name'] : "";
- $value['分公司'] = isset($comp[1]['name']) ? $comp[1]['name'] : "";
- $top = made($value['三级分类']);
- $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 ($value['单位'] == 3) {
- $good = Db::name("good_zixun")->where(["spuCode" => $value['产品编码']])->find();
- } else {
- $good = Db::name("good_basic")->where(["spuCode" => $value['产品编码']])->find();
- }
- if ($value['单位'] == 1 && $value['退货采购货款'] == 0) {
- $returninfo = Db::name("return_info")->where(["returnCode" => $value['退货单号']])->field("sum(origin_price * num) as cgd_total")->find();
- $value['退货采购货款'] = $returninfo['cgd_total'] ?? 0;
- }
- if ($value['采购员'] == '' || $value['供应商名称'] == '') {
- $supplier = $userCommon->handle('sInfo', ['code' => $good['supplierNo']]);
- $value['采购员'] = $supplier['data']['person'] ?? '';
- $value['供应商名称'] = $supplier['data']['name'] ?? '';
- }
- $value['单位'] = isset($good['good_unit']) ? Db::name("unit")->where(["id" => $good['good_unit']])->value('unit', '') : "";
- $value['税点'] = isset($good['tax']) ? $good['tax'] . '%' : "";
- $value['平台类型'] = $platform[$value['平台类型']] ?? '';
- unset($value['customer_code']);
- yield $com[] = $value;
- }
- return $com;
- }
-
- public function D($start, $end)
- {
- $list = Db::name("order_out")
- ->alias('woo')
- ->leftJoin("sale a", "a.orderCode=woo.orderCode and a.is_del=0")
- ->leftJoin("order_back wor", "wor.outCode=woo.outCode and wor.status=4 and wor.is_del=0")
- ->leftJoin("order_num won", "won.orderCode=a.orderCode")
- ->leftJoin("purchease_order wpo", "wpo.cgdNo=won.cgdNo and wpo.is_del=0")
- ->leftJoin("good_basic wgb", "wgb.spuCode=wpo.spuCode and wgb.is_del=0")
- ->leftJoin("good_zixun wgz", "wgz.spuCode=wpo.spuCode and wgz.is_del=0")
- ->where('woo.status', ">=", 2)
- ->where('woo.is_del', "=", 0)
- ->where('wpo.order_type', "<>", 1)
- ->whereBetween('woo.sendtime', [$start, $end])
- ->field(" year(`a`.`addtime`) AS `年`,
- month(`a`.`addtime`) AS `月`,
- dayofmonth(`a`.`addtime`) AS `日`,
- a.supplierName 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` )/100 AS `采购税点`,
- `wpo`.`supplier_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 = $tmp = [];
- $fund_code = Db::name('cat')->where(['is_del' => 0, 'level' => 3])->column('fund_code', 'id');
- $unit = Db::name('unit')->where('is_del', 0)->column('unit', 'id');
- foreach ($list as $value) {
- if (!isset($tmp[$value['业务部门名称']])) $tmp[$value['业务部门名称']] = get_company_name_by_uid($value['业务部门名称']);
- $value['业务部门名称'] = $tmp[$value['业务部门名称']] ?? '';
- $value['订单类型'] = $order_tyepe[$value['订单类型'] - 1];
- $value['商品类型'] = $good_type[$value['商品类型'] - 1];
- $cat = made($value['三级分类']);
- $value['财务核算编码'] = $fund_code[$value['三级分类']] ?? '';
- $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['单位'] = $unit[$value['单位']] ?? '';
- if ($value['是否库存品'] == 1) {
- $sendtype = Db::name("purchease_order")
- ->alias("a")
- ->leftJoin("purchease_in b", "a.cgdNo=b.cgdNo and b.is_del=0")
- ->where(["bkcode" => $value['发货方式'], "order_type" => 1, "order_source" => 0])
- ->order("b.addtime desc")
- ->value("b.sendtype", '');
- $value['是否库存品'] = '是';
- } else {
- $sendtype = Db::name("purchease_order")
- ->alias("a")
- ->leftJoin("purchease_in b", "a.cgdNo=b.cgdNo and b.is_del=0")
- ->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;
- }
- $cgd = $this->GetWlb($start, $end);
- foreach ($cgd as $key => $value) {
- 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 and wp.is_del=0")
- ->leftJoin("good_basic wgb", "wgb.spuCode=wpo.spuCode and wgb.is_del=0")
- ->leftJoin("warehouse_info wwi", "wwi.wsm_code = wpo.wsm_code")
- ->where('wpi.status', "in", [4, 6])
- ->where('wpi.is_del', "=", 0)
- ->where('wpo.order_type', "=", 1)
- ->where('wpo.order_source', "=", 0)
- ->whereBetween('wpi.addtime', [$start, $end])
- ->field("wpo.companyName '业务公司',
- bkcode '备库单号',
- wpi.wsm_in_code '入库单号',
- wpi.bnCode 'BN批次单号',
- wpi.cgdNo '采购单号',
- wpo.spuCode '商品编号',
- '' as '一级分类',
- '' as '二级分类',
- wgb.cat_id as '三级分类',
- wgb.cat_id as '财务核算编号',
- wpo.good_name as '商品名称',
- if(wpi.status=4,wpi.send_num,wpi.wsm_num) '入库数量',
- round( if(wpi.status=4,wpi.send_num,wpi.wsm_num) * good_price, 2 ) '入库金额',
- wpi.updatetime '本次入库时间',
- wgb.tax/100 '采购税率',
- '' as '不含税入库金额',
- '' as '税额',
- wp.apply_id as '业务部门',
- wp.apply_name as '业务员',
- wpo.cgder as '采购员',
- wpo.good_price '成本裸价',
- wsm_type '仓库类型',
- wpo.supplier_name '供应商名称',
- wwi.name '仓库名称',
- wp.addtime '备库创建时间'
- ")->cursor();
- $data = $tmp = [];
- $fund_code = Db::name('cat')->where(['is_del' => 0, 'level' => 3])->column('fund_code', 'id');
- $warehouse_type = Db::name('warehouse_type')->column('name', 'id');
- foreach ($list as $value) {
- if (!isset($tmp[$value['业务部门']])) $tmp[$value['业务部门']] = get_company_name_by_uid($value['业务部门']);
- $value['业务部门'] = $tmp[$value['业务部门']] ?? '';
- $cat = made($value['三级分类']);
- $value['财务核算编号'] = $fund_code[$value['三级分类']] ?? '';
- $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['仓库类型'] = $warehouse_type[$value['仓库类型']] ?? '';
- yield $data[] = $value;
- }
- return $data;
- }
-
- public function F($start, $end)
- {
- $list = Db::name("order_out")->alias('woo')->leftJoin("sale a", "a.orderCode=woo.orderCode and a.is_del=0")
- ->leftJoin("order_num won", "won.orderCode=a.orderCode")
- ->leftJoin("order_back wor", "wor.outCode=woo.outCode and wor.status=4 and wor.is_del=0")
- ->leftJoin("purchease_order wpo", "wpo.cgdNo=won.cgdNo")
- ->leftJoin("good_basic wgb", "wgb.spuCode=wpo.spuCode and wgb.is_del=0")
- ->leftJoin("warehouse_info wwi", "wwi.wsm_code = wpo.wsm_code")
- ->where([['woo.status', ">=", 2], ['woo.is_del', "=", 0], ['a.order_type', "=", 1], ['woo.sendtime', "between", [$start, $end]]])
- ->field("wpo.companyName '业务公司',
- bkcode '备库单号',
- woo.orderCode '销售单号',
- woo.outCode '出库单号',
- wpo.cgdNo '采购单号',
- wpo.spuCode '商品编号',
- '' as '一级分类',
- '' as '二级分类',
- wgb.cat_id as '三级分类',
- wgb.cat_id as '财务核算编号',
- wpo.good_name as '商品名称',
- woo.send_num'出库数量',
- round( woo.send_num* wpo.good_price,2) '出库金额',
- woo.sendtime '本次出库时间',
- wgb.tax/100 '税率',
- '' as '不含税出库金额',
- ''as '税额',
- a.apply_id as '业务部门',
- a.apply_name as '业务员',
- wpo.cgder as '采购员',
- wpo.good_price '成本裸价',
- wor.return_num '出库退货数量',
- wwi.wsm_type '仓库类型',
- wpo.supplier_name '供应商名称',
- wwi.name '仓库名称'
- ")->cursor();
- $data = $tmp = [];
- $warehouse_type = Db::name('warehouse_type')->column('name', 'id');
- $fund_code = Db::name('cat')->where(['is_del' => 0, 'level' => 3])->column('fund_code', 'id');
- foreach ($list as $value) {
- if (!isset($tmp[$value['业务部门']])) $tmp[$value['业务部门']] = get_company_name_by_uid($value['业务部门']);
- $value['业务部门'] = $tmp[$value['业务部门']] ?? '';
- $cat = made($value['三级分类']);
- $value['财务核算编号'] = $fund_code[$value['三级分类']] ?? '';
- $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['仓库类型'] = $warehouse_type[$value['仓库类型']] ?? '';
- yield $data[] = $value;
- }
- return $data;
- }
-
- public function GetWlb($start, $end)
- {
- $db = Db::connect("mysql3");
- $list = $db->name("cgd_info")
- ->where(['cgdSource' => 10, "is_del" => 0])->whereBetweenTime('createdTime', $start, $end)->field(
- "year(createdTime) AS `年`,
- month(createdTime) AS `月`,
- dayofmonth(createdTime) AS `日`,
- companyName '公司名称',
- department '业务部门',
- ownerName '业务人员',
- '网络部采购单' '订单类型',
- '' as '商品类型',
- '' as '发货编号',
- sendNum '本次发货数量',
- createdTime '本次发货时间',
- qrdCode '确认单编号',
- goodNo '商品编号',
- firstCat '一级分类',
- secCat as '二级分类',
- thirdCat as '三级分类',
- fundCode as '财务核算编码',
- goodName as '商品名称',
- goodUnit as '单位',
- goodNum '下单数量',
- '' AS `销售货款`,
- sequenceNo as '采购单单号',
- ownerName '采购员',
- barePrice AS `采购裸价`,
- markPrice AS `加标费`,
- packPrice AS `包装费`,
- certPrice AS `证书费`,
- openPrice AS `开模费`,
- costPrice AS `工艺费`,
- deliveryPrice AS `物流费`,
- goodPrice AS `成本合计`,
- totalPrice AS `采购货款`,
- tax AS `采购税点`,
- supplierName AS `供应商名称`,
- '否'as '是否库存品',
- '包邮' as '发货方式',
- '' AS `退货编号`,
- thNum AS `退货数量`,
- th_fee as '退货金额',
- '' as '售前退货数量',
- '' as '售前退货金额'
- ")->cursor();
- $data = [];
- foreach ($list as $value) {
- $value['销售货款'] = $db->name("qrd_info")->where(['sequenceNo' => $value['确认单编号']])->value('totalPrice', '');
- yield $data[] = $value;
- }
- return $data;
- }
-
- public function G(string $start_date = '', string $end_date = '')
- {
- $all_order_type = [1 => '备库', 2 => '非库存', 3 => '咨询采反', 4 => '项目采反', 5 => '平台部订单销售库存', 6 => '平台部订单销售非库存'];
- $all_sale_status = [0 => '待发货', 1 => '待发货完成', 2 => '发货已完成', 3 => '订单已取消'];
- $all_stock = [0 => '非库存品', 1 => '库存品'];
- $all_is_activity = [0 => '不参与活动', 1 => '参与活动'];
- $all_good_type = [1 => '正常商品', 2 => '赠品', 3 => '样品'];
- $data = Db::name('sale')
- ->alias('s')
- ->field('s.addtime 确认单下单时间,s.orderCode 订单编号,s.order_type 订单类型,s.status 订单状态,s.apply_name 创建人,"" 部门,s.supplierName 销售方公司,"" 一级组织,"" 二级组织,s.customerName 购买方公司,p.platform_name 所属平台,s.platform_order 平台订单号,s.poNo 其他单号,s.paytime 承诺回款时间,ou.order_use 订单用途,"" 活动类型,s.good_name 产品名称,"" 一级分类,gb.tax 税率,s.sale_price 销售单价,s.good_num 下单数量,s.total_price 销售总额,po.cgder 采购人,gb.customized 工期时间,s.arrive_time 要求到货时间,s.remark 备注,s.is_stock 是否库存品,s.cat_id,s.is_activity,s.good_type,s.customer_code,s.apply_id')
- ->leftJoin('platform p', 'p.id=s.platform_id')
- ->leftJoin('good_basic gb', 'gb.spuCode=s.good_code')
- ->leftJoin('order_use ou', 'ou.id=s.use_order')
- ->leftJoin('order_num on', 'on.orderCode=s.orderCode')
- ->leftJoin('purchease_order po', 'po.cgdNo=on.cgdNo')
- ->where(['s.is_del' => 0, 'ci.is_del' => 0])
- ->whereBetween('s.addtime', [$start_date, $end_date])
- ->order('s.addtime', 'desc')
- ->cursor();
- $list = $tmp_codes = $tmp_customer = $tmp_company_names = [];
- $userCommon = User::getIns();
- foreach ($data as $value) {
- if (!isset($tmp_company_names[$value['apply_id']])) $tmp_company_names[$value['apply_id']] = get_company_name_by_uid($value['apply_id']);
- $value['部门'] = $tmp_company_names[$value['apply_id']];
- if (!isset($tmp_customer[$value['customer_code']])) {
- $tmp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
- $tmp_customer[$value['customer_code']] = $tmp['data']['itemid'];
- }
- $value['itemid'] = $tmp_customer[$value['customer_code']];
- $value['订单类型'] = isset($all_order_type[$value['订单类型']]) ? $all_order_type[$value['订单类型']] : '';
- $value['订单状态'] = isset($all_sale_status[$value['订单状态']]) ? $all_sale_status[$value['订单状态']] : '';
- if (!empty($value['itemid'])) {
- $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
- $value['一级组织'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
- $value['二级组织'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
- }
- $cat = made($value['cat_id']);
- $value['一级分类'] = isset($cat[0]['name']) ? $cat[0]['name'] : '';
- $value['是否库存品'] = isset($all_stock[$value['是否库存品']]) ? $all_stock[$value['是否库存品']] : '';
- $is_activity = isset($all_is_activity[$value['is_activity']]) ? $all_is_activity[$value['is_activity']] : '';
- $good_type = isset($all_good_type[$value['good_type']]) ? $all_good_type[$value['good_type']] : '';
- $value['活动类型'] = $is_activity . '/' . $good_type;
- unset($value['good_code']);
- unset($value['itemid']);
- unset($value['cat_id']);
- unset($value['is_activity']);
- unset($value['good_type']);
- unset($value['customer_code']);
- unset($value['supplierNo']);
- yield $list[] = $value;
- }
- return $list;
- }
-
- private function H(string $start_date = '', string $end_date = '')
- {
- $data = Db::name('good_stock')
- ->alias('wgs')
- ->field(" wwi.companyName '公司名称',wgs.spuCode '产品编号',cat_id '一级分类',good_name '产品名称',wait_in_stock '待入库存数量',wait_out_stock '待出库存数量',usable_stock '可用库存数量',wait_out_stock+usable_stock '当前库存','' as '保质期时间','' as '库存天数',wgb.creater as 'CGD.采购员','' as '备库单号',wgb.supplierName as '供应商名称',wwi.`name` as '仓库名称','' as '最近入库时间'")
- ->leftJoin('warehouse_info wwi', 'wwi.wsm_code = wgs.wsm_code')
- ->leftJoin('good_basic wgb', 'wgb.spuCode = wgs.spuCode')
- ->where('wgs.is_del', 0)
- ->where('wgb.is_stock', 1)
- ->whereBetween('wgs.addtime', [$start_date, $end_date])
- ->order('wgs.addtime', 'desc')
- ->cursor();
- $list = [];
- foreach ($data as $value) {
- $cat = made($value['一级分类']);
- $value['一级分类'] = isset($cat[0]['name']) ? $cat[0]['name'] : "";
- $value['最近入库时间'] = Db::name("purchease_order")->alias("a")->leftJoin("purchease_in c", "a.cgdNo=c.cgdNo")->where(["spuCode" => $value['产品编号'], "order_type" => 1, "order_source" => 0, "c.status" => [4, 6]])->order("a.addtime desc")->value("c.addtime", "");
- $value['备库单号'] = Db::name("purchease_order")->alias("a")->where(["spuCode" => $value['产品编号'], "order_type" => 1, "order_source" => 0])->order("a.addtime desc")->value("a.bkcode", "");
- if (!empty($value['最近入库时间'])) {
- $value['保质期时间'] = date('Y-m-d H:i:s', strtotime($value['最近入库时间']) + 31536000);
- $value['库存天数'] = bcdiv((string)(time() - strtotime($value['最近入库时间'])), (string)(24 * 3600));
- }
- yield $list[] = $value;
- }
- return $list;
- }
-
- public function I(string $start = '', string $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")
- ->where('woo.status', ">=", 2)
- ->whereBetween('woo.sendtime', [$start, $end])
- ->field(" year(`a`.`addtime`) AS `年`,month(`a`.`addtime`) AS `月`,dayofmonth(`a`.`addtime`) AS `日`,a.supplierName 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 `下单数量`,`wpo`.`cgdNo` AS `采购单单号`,`wpo`.`cgder` AS `采购员`,`wpo`.`supplier_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 `退货数量`,'' as '售前退货数量'")
- ->cursor();
- $order_tyepe = ["库存销售", '非库存销售', '咨询销售', '项目销售', '平台库存销售', '平台非库存销售'];
- $good_type = ["常规商品", '赠品', '样品'];
- $data = $tmp = [];
- $fund_code = Db::name('cat')->where(['is_del' => 0, 'level' => 3])->column('fund_code', 'id');
- $unit = Db::name('unit')->where('is_del', 0)->column('unit', 'id');
- foreach ($list as $value) {
- if (!isset($tmp[$value['业务部门名称']])) $tmp[$value['业务部门名称']] = get_company_name_by_uid($value['业务部门名称']);
- $value['业务部门名称'] = $tmp[$value['业务部门名称']] ?? '';
- $value['订单类型'] = $order_tyepe[$value['订单类型'] - 1];
- $value['商品类型'] = $good_type[$value['商品类型'] - 1];
- $cat = made($value['三级分类']);
- $value['财务核算编码'] = $fund_code[$value['三级分类']] ?? '';
- $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['单位'] = $unit[$value['单位']] ?? '';
- if ($value['是否库存品'] == 1) {
- $sendtype = Db::name("purchease_order")->alias("a")
- ->leftJoin("purchease_in b", "a.cgdNo=b.cgdNo")
- ->where(["bkcode" => $value['发货方式'], "order_type" => 1, "order_source" => 0])
- ->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');
- yield $data[] = $value;
- }
- $cgd = $this->GetWlb($start, $end);
- foreach ($cgd as $key => $value) {
- yield $data[] = $value;
- }
- return $data;
- }
-
- public function J(string $start = '', string $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")
- ->where('wpi.status', "in", [4, 6])
- ->where('wpo.order_type', "=", 1)
- ->where('wpo.order_source', "=", 0)
- ->whereBetween('wpi.addtime', [$start, $end])
- ->field("wpo.companyName '业务公司',bkcode '备库单号',wpi.wsm_in_code '入库单号',wpi.cgdNo '采购单号',wpo.spuCode '商品编号','' as '一级分类','' as '二级分类',wgb.cat_id as '三级分类',wgb.cat_id as '财务核算编号',wpo.good_name as '商品名称',if(wpi.status=4,wpi.send_num,wpi.wsm_num) '入库数量',wpo.updatetime '本次入库时间',wp.apply_id as '业务部门',wp.apply_name as '业务员',wpo.cgder as '采购员',wsm_type '仓库类型',wpo.supplier_name '供应商名称',wwi.name '仓库名称'")
- ->cursor();
- $data = [];
- $fund_code = Db::name('cat')->where(['is_del' => 0, 'level' => 3])->column('fund_code', 'id');
- $warehouse_type = Db::name('warehouse_type')->column('name', 'id');
- foreach ($list as $value) {
- if (!isset($tmp[$value['业务部门']])) $tmp[$value['业务部门']] = get_company_name_by_uid($value['业务部门']);
- $value['业务部门'] = $tmp[$value['业务部门']] ?? '';
- $cat = made($value['三级分类']);
- $value['财务核算编号'] = $fund_code[$value['三级分类']] ?? '';
- $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['仓库类型'] = $warehouse_type[$value['仓库类型']] ?? '';
- yield $data[] = $value;
- }
- return $data;
- }
-
- public function K(string $start = '', string $end = '')
- {
- $list = Db::name("order_out")
- ->alias('woo')
- ->leftJoin("sale a", "a.orderCode=woo.orderCode and a.is_del=0")
- ->leftJoin("order_num won", "won.orderCode=a.orderCode")
- ->leftJoin("order_back wor", "wor.outCode=woo.outCode and wor.status=4 and wor.is_del=0")
- ->leftJoin("purchease_order wpo", "wpo.cgdNo=won.cgdNo and wpo.is_del=0")
- ->leftJoin("good_basic wgb", "wgb.spuCode=wpo.spuCode and wgb.is_del=0")
- ->leftJoin("warehouse_info wwi", "wwi.wsm_code = wpo.wsm_code")
- ->where([['woo.status', ">=", 2], ['woo.is_del', "=", 0], ['a.order_type', "=", 1], ['woo.sendtime', 'between', [$start, $end]]])
- ->field("wpo.companyName '业务公司',bkcode '备库单号',woo.orderCode '销售单号',woo.outCode '出库单号',wpo.cgdNo '采购单号',wpo.spuCode '商品编号','' as '一级分类','' as '二级分类',wgb.cat_id as '三级分类',wpo.good_name as '商品名称',woo.send_num'出库数量',woo.sendtime '本次出库时间',a.apply_id as '业务部门',a.apply_name as '业务员',wpo.cgder as '采购员',wor.return_num '出库退货数量',wwi.wsm_type '仓库类型',wpo.supplier_name '供应商名称',wwi.name '仓库名称'")
- ->cursor();
- $data = $tmp = [];
- $warehouse_type = Db::name('warehouse_type')->column('name', 'id');
- foreach ($list as $value) {
- if (!isset($tmp[$value['业务部门']])) $tmp[$value['业务部门']] = get_company_name_by_uid($value['业务部门']);
- $value['业务部门'] = $tmp[$value['业务部门']] ?? '';
- $cat = made($value['三级分类']);
- $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['仓库类型'] = $warehouse_type[$value['仓库类型']] ?? '';
- yield $data[] = $value;
- }
- return $data;
- }
-
- private function NewOut(string $start, string $end)
- {
- $data = Db::name("order_out")
- ->alias("a")
- ->leftJoin("sale b", "a.orderCode=b.orderCode")
- ->field("b.supplierName '业务公司',a.orderCode AS '销售单号',outCode '出库单号',
- b.good_code '商品编号',b.cat_id ,'' as '一级分类','' as '二级分类','' as '三级分类','' as '财务核算编号',
- b.good_name '商品名称',a.send_num '出库数量','' as '出库金额',a.sendtime '本次出库时间',
- ''as '税率', b.apply_id as '业务部门',a.apply_name as '业务人员',cgder as '采购员','' as '成本裸价',
- '' as '供应商名称','' as '出库退货数量',b.order_type")
- ->where([["a.status", ">=", 2], ["a.is_del", "=", 0], ["b.order_type", "=", 1], ['a.sendtime', 'between', [$start, $end]]])
- ->cursor();
- $list = $tmp = [];
- $fund_code = Db::name('cat')->where(['is_del' => 0, 'level' => 3])->column('fund_code', 'id');
- foreach ($data as $value) {
- if (!isset($tmp[$value['业务部门']])) $tmp[$value['业务部门']] = get_company_name_by_uid($value['业务部门']);
- $value['业务部门'] = $tmp[$value['业务部门']] ?? '';
- $cat = made($value['cat_id']);
- $value['财务核算编号'] = $fund_code[$value['cat_id']] ?? '';
- unset($value['cat_id']);
- $value['一级分类'] = isset($cat[0]['name']) ? $cat[0]['name'] : "";
- $value['二级分类'] = isset($cat[1]['name']) ? $cat[1]['name'] : "";
- $value['三级分类'] = isset($cat[2]['name']) ? $cat[2]['name'] : "";
- if (in_array($value["order_type"], [3, 4])) {
- $good = Db::name("good_zixun")->where(["spuCode" => $value['商品编号']])->find();
- } else {
- $good = Db::name("good")->where(["spuCode" => $value['商品编号']])->find();
- }
- $saleinfo = Db::name("sale_info")->where(["orderCode" => $value['销售单号']])->field("num,origin_price,num*origin_price as total_price")->select()
- ->toArray();
- if (empty($saleinfo)) {
- $value['成本裸价'] = Db::name("order_out")->alias("a")
- ->leftJoin("order_send b", "a.outCode=b.outCode")
- ->leftJoin("purchease_order c", "b.cgdNo=c.cgdNo")
- ->where(["a.outCode" => $value['出库单号']])->value("c.good_price", '0');
- } else {
- $num = array_sum(array_column($saleinfo, "num"));
- if ($num == 0) {
- $value['成本裸价'] = 0;
- } else {
- $value['成本裸价'] = round(array_sum(array_column($saleinfo, "total_price")) / array_sum(array_column($saleinfo, "num")), 2);
- }
- }
- $value['出库金额'] = $value['出库数量'] * $value['成本裸价'];
- $value['税率'] = $good['tax'] . "%";
- $value['供应商名称'] = $good['supplierName'];
- $value["出库退货数量"] = Db::name("order_back")->where(["outCode" => $value['出库单号']])->sum("return_num");
- unset($value['order_type']);
- yield $list[] = $value;
- }
- }
-
- private function EveryDayStock(string $start, string $end)
- {
- $list = Db::name("good_stock_info")
- ->alias("a")
- ->leftJoin("good_stock b", "a.stockid=b.id")
- ->leftJoin("purchease_in c", "c.bnCode=a.bnCode")
- ->leftJoin("purchease_order d", "c.cgdNo=d.cgdNo")
- ->leftJoin("purchease e", "d.bkcode=e.bk_code")
- ->leftJoin("good f", "b.spuCode=f.spuCode")
- ->leftJoin("warehouse_info j", "b.wsm_code=j.wsm_code")
- ->field(" e.companyName '业务公司',
- e.apply_id '申请部门',
- e.apply_name '申请人',
- e.addtime '备库创建时间',
- e.bk_code '备库单号',
- a.bnCode 'BN编号' ,
- a.addtime '入库时间',
- c.cgdNo '采购单编号' ,
- f.spuCode '产品编号' ,
- f.good_name '产品名称' ,
- e.good_num '备库数量' ,
- f.cat_id '一级分类' ,
- f.cat_id,
- '' as '财务核算编码',
- f.tax as '税率',
- a.origin_price '成本单价',
- b.wait_in_stock '待入库数量',
- b.wait_out_stock '待出库数量',
- (b.wait_out_stock+b.usable_stock) '当前库存数量',
- b.usable_stock '可用库存数量',
- a.total_num 'BN总数',
- a.balance_num 'BN库存数',
- a.used_num 'BN已使用数量',
- DATEDIFF(NOW(),a.addtime) '库存天数',
- f.supplierNo '采购员',
- d.supplier_name '供应商名称',
- j.`name` '仓库名称'")
- ->where(["f.is_stock" => 1])
- ->whereBetween("a.updatetime", [$start, $end])
- ->cursor();
- $fund_code = Db::name('cat')->where(['is_del' => 0, 'level' => 3])->column('fund_code', 'id');
- $tmp = [];
- foreach ($list as $value) {
- if (!isset($tmp[$value['申请部门']])) $tmp[$value['申请部门']] = get_company_name_by_uid($value['申请部门']);
- $value['申请部门'] = $tmp[$value['申请部门']] ?? '';
- $cat = made($value['cat_id']);
- $value['财务核算编号'] = $fund_code[$value['cat_id']] ?? '';
- unset($value['cat_id']);
- $value['一级分类'] = isset($cat[0]['name']) ? $cat[0]['name'] : "";
- $supplier = Db::connect('mysql_sys')->name("supplier")->where(["code" => $value['采购员']])->field("person,name")->find();
- if ($value['供应商名称'] == '') {
- $value['供应商名称'] = $supplier['name'] ?? "";
- }
- $value['采购员'] = $supplier['person'] ?? "";
- yield $value;
- }
- }
-
- private function GetGoodNake()
- {
- $list = Db::name("good_platform")
- ->alias("a")
- ->leftJoin("good b", "a.spuCode=b.spuCode")
- ->leftJoin("good_nake c", "b.spuCode=c.spuCode and c.is_del=0")
- ->field("
- a.skuCode '上线商品编号',
- a.spuCode '成本商品编号',
- a.plat_code '平台商品编号',
- b.good_name '商品名称',
- b.brand_id '品牌',
- b.cat_id '分类',
- b.companyName '业务企业名称',
- a.creater '申请人',
- platform_code '上线平台',
- online_time '上线时间',
- exam_status '上线状态',
- if(b.is_stock=1,'库存','非库存') '是否库存',
- if(b.good_type=1,'定制','非定制')'是否定制',
- '' as '规格',
- '' as '型号',
- b.good_unit '单位',
- b.good_remark '商品备注',
- b.craft_desc '工艺说明',
- b.noble_metal '贵金属信息',
- b.cgd_gold_price '供应商采购金价',
- b.demo_fee '打样费',
- b.open_fee '开模费',
- b.sample_fee '调样费',
- c.min_num '起订量',
- c.nake_fee '成本单价',
- c.cost_fee '工艺费',
- c.delivery_fee '物流费',
- c.cert_fee '证书费',
- c.mark_fee '加标费',
- c.package_fee '包装费',
- c.other_fee '其他费用',
- c.nake_total '成本合计',
- b.tax '税点',
- if(b.supply_area=1,'全国','全国除偏远地区') '供货区域',
- b.supplierName '供应商名称'
- ")->cursor();
- $brand = Db::name('brand')->where('is_del', 0)->column('brand_name', 'id');
- $cat = Db::name('cat')->where(['is_del' => 0, 'level' => 3])->column('cat_name', 'id');
- $platform = Db::name('platform')->where('is_del', 0)->column('platform_name', 'id');
- $unit = Db::name('unit')->where('is_del', 0)->column('unit', 'id');
- $arr = ["待提交", '待完善成本', '待产品审核', '待财务定价', '待财务审核定价', '待上线', '已上线', '审核失败', '已下线'];
- $noble = ["0" => "", "1" => "18K", "2" => "24K", "3" => "白银"];
- foreach ($list as $value) {
- $value["品牌"] = $brand[$value["品牌"]] ?? '';
- $value["分类"] = $cat[$value["分类"]] ?? '';
- $value["上线平台"] = $platform[$value["上线平台"]] ?? '';
- $value["上线状态"] = $arr[$value["上线状态"]];
- $value['单位'] = $unit[$value['单位']] ?? '';
- $value['贵金属信息'] = $noble[$value['贵金属信息']];
- yield $value;
- }
- }
-
- private function GetGoodLadder()
- {
- $list = Db::name("good_platform")->alias("a")
- ->leftJoin("good b", "a.spuCode=b.spuCode")
- ->leftJoin("good_ladder c", "a.skuCode=c.skuCode and c.is_del=0")
- ->field("
- a.skuCode '上线商品编号',
- a.spuCode '成本商品编号',
- a.plat_code '平台商品编号',
- b.good_name '商品名称',
- b.brand_id '品牌',
- b.cat_id '分类',
- b.companyName '业务企业名称',
- a.creater '申请人',
- platform_code '上线平台',
- online_time '上线时间',
- exam_status '上线状态',
- if(b.is_stock=1,'库存','非库存') '是否库存',
- if(b.good_type=1,'定制','非定制')'是否定制',
- '' as '规格',
- '' as '型号',
- b.good_unit '单位',
- b.good_remark '商品备注',
- b.craft_desc '工艺说明',
- b.noble_metal '贵金属信息',
- b.cgd_gold_price '供应商采购金价',
- b.demo_fee '打样费',
- b.open_fee '开模费',
- b.sample_fee '调样费',
- c.min_num '起订量',
- c.sale_price '售价',
- c.cost_fee '工艺费',
- c.market_price '物流费',
- c.market_platform '对比平台',
- b.tax '税点',
- if(b.supply_area=1,'全国','全国除偏远地区') '供货区域',
- b.supplierName '供应商名称'
- ")->cursor();
- $noble = ["0" => "", "1" => "18K", "2" => "24K", "3" => "白银"];
- $arr = ["待提交", '待完善成本', '待产品审核', '待财务定价', '待财务审核定价', '待上线', '已上线', '审核失败', '已下线'];
- $brand = Db::name('brand')->where('is_del', 0)->column('brand_name', 'id');
- $cat = Db::name('cat')->where(['is_del' => 0, 'level' => 3])->column('cat_name', 'id');
- $platform = Db::name('platform')->where('is_del', 0)->column('platform_name', 'id');
- $unit = Db::name('unit')->where('is_del', 0)->column('unit', 'id');
- foreach ($list as $value) {
- $value["品牌"] = $brand[$value["品牌"]] ?? '';
- $value["分类"] = $cat[$value["分类"]] ?? '';
- $value["上线平台"] = $platform[$value["上线平台"]] ?? '';
- $value["上线状态"] = $arr[$value["上线状态"]];
- $value['单位'] = $unit[$value['单位']] ?? '';
- $value['贵金属信息'] = $noble[$value['贵金属信息']];
- yield $value;
- }
- }
- private function GetBid(string $start, string $end)
- {
- $list = Db::name("consult_info")->alias("a")
- ->leftJoin("consult_bids d", "a.infoNo=d.infoNo")
- ->leftJoin("consult_order c", "a.zxNo=c.zxNo")
- ->leftJoin("bargain_order b", "d.bidNo=b.bidsNo and b.is_del=0")
- ->field("c.companyNo '业务公司',
- d.addtime '竞价时间',
- d.infoNo '竞价单编号',
- a.`status` '状态',
- c.creater '申请人',
- c.createrid '申请人部门',
- c.khNo '客户名称',
- c.platform_code '所属平台',
- if(a.pgNo='','咨询','项目') as '竞价类型',
- a.cat_id '商品分类',
- a.good_name '商品名称',
- a.budget_price '预算单价',
- a.unit '单位',
- a.num '数量',
- a.budget_price*a.num '竞价单总额',
- d.remark '商品备注',
- d.addtime '反馈时间',
- d.creater '反馈人',
- d.bidNo '反馈商品编码',
- d.status '反馈商品状态',
- d.good_name '反馈商品名称',
- d.sale_price*a.num '反馈单总额',
- d.cat_id '反馈商品分类',
- d.unit_id '反馈商品单位',
- d.tax '反馈商品税点',
- c.endtime '竞价有效期',
- b.bargainNo '议价单编号',
- b.sale_price '议价前售价',
- b.after_price '议价后售价',
- b.before_rate '议价前毛利率',
- b.after_rate '议价后毛利率',
- b.`status` '议价状态',
- b.bargain_remark '议价备注',
- b.result_info_id '议价原因'")
- ->where("a.is_del", 0)
- ->whereBetween("a.addtime", [$start, $end])
- ->cursor();
- $cat = Db::name('cat')->where(['is_del' => 0, 'level' => 3])->column('cat_name', 'id');
- $unit = Db::name('unit')->where('is_del', 0)->column('unit', 'id');
- $platform = Db::name('platform')->where('is_del', 0)->column('platform_name', 'id');
- $result = Db::name('result_info')->where(['is_del' => 0])->column('result_desc', 'id');
- $arr1 = ["待提交", '招标进行中', '招标已结束', '等待议价结果', '确认商品', '成功转单', '取消转单', '招标已暂停'];
- $arr2 = ["待提交", "待任务结束", "待咨询确认", "议价待财务定价", "待主管确认", "待咨询确认定价", "咨询确认"];
- $arr3 = ['待业务主管审核', '业务主管通过', '待财务专员审核', '业务驳回', '财务专员通过', '财务专员驳回', '财务主管通过', '待boss审核', '财务主管驳回', 'boss审核通过', 'boss越线通过', 'boss驳回', '取消议价'];
- $tmp = [];
- foreach ($list as $value) {
- $value["商品分类"] = $cat[$value["商品分类"]] ?? '';
- $value["反馈商品分类"] = $cat[$value["反馈商品分类"]] ?? '';
- $value["反馈商品单位"] = $unit[$value["反馈商品单位"]] ?? '';
- if (isset($tmp[$value['业务公司']])) $tmp[$value['业务公司']] = get_headquarters_code_and_name($value['业务公司'])[$value['业务公司']] ?? '';
- $value["业务公司"] = $tmp[$value['业务公司']] ?? '';
- $value["所属平台"] = $platform[$value["所属平台"]] ?? '';
- if (isset($tmp[$value['申请人部门']])) $tmp[$value['申请人部门']] = get_company_name_by_uid($value['申请人部门']);
- $value['申请人部门'] = $tmp[$value['申请人部门']] ?? '';
-
- $value["状态"] = $arr1[$value["状态"]] ?? "";
-
- $value["反馈商品状态"] = $arr2[$value["反馈商品状态"]] ?? "";
-
- $value["议价状态"] = $arr3[$value["议价状态"]] ?? "";
- $value['单位'] = $unit[$value['单位']] ?? '';
- $value['议价原因'] = $result[$value['议价原因']] ?? '';
- if (isset($tmp[$value['客户名称']])) $tmp[$value['客户名称']] = get_headquarters_code_and_name($value['客户名称'][$value['客户名称']]) ?? '';
- $value['客户名称'] = $tmp[$value['客户名称']] ?? '';
- yield $value;
- }
- }
- }
|