<?php declare (strict_types=1);
namespace app\command;
use app\admin\common\User;
use think\console\Command;
use think\console\Input;
use think\console\input\Argument;
use think\console\Output;
use think\db\exception\PDOException;
use think\Exception;
use think\facade\Cache;
use think\facade\Db;
//处理报表预约记录，生成报表文件(业务公司)
class ExecByRelaComNoHandle extends Command
{
//redis队列的key，在ExecByRelaComNo控制器中也有定义，要同步修改(轻易不要修改)
private $key = 'cxreport_relaComNo';
protected function configure()
{
// 指令配置
$this->setName('cxreport_relaComNo')
->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'], $info['relaComNo'], $info['relaComName']);
break;
//【财务报表】库存日报及预警汇总表
case 'B':
$res = $this->B($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
break;
//【财务报表】退货台账
case 'C':
$res = $this->C($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
break;
//【财务报表】出入库明细
case 'D':
$res = $this->D($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
break;
//【财务报表】库存品入库明细
case 'E':
$res = $this->E($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
break;
//【财务报表】库存品出库明细
case 'F':
$res = $this->F($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
break;
//【销售】订单表导出
case 'G':
$res = $this->G($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
break;
//【库存报表】-库存日报及预警汇总表
case 'H':
$res = $this->H($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
break;
//【库存报表】-出入库明细
case 'I':
$res = $this->I($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
break;
//【库存报表】-库存品入库明细
case 'J':
$res = $this->J($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
break;
//【库存报表】-库存品出库明细
case 'K':
$res = $this->K($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
break;
//【库存报表】-库存品出库明细
case 'L':
$res = $this->NewOut($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
break;
//每日库存数据更新
case 'M':
$res = $this->EveryDayStock($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
break;
//商品成本信息
case 'N':
$res = $this->GetGoodNake($info['relaComNo'], $info['relaComName']);
break;
//商品售价信息
case 'P':
$res = $this->GetGoodLadder($info['relaComNo'], $info['relaComName']);
break;
case 'Q':
$res = $this->GetBid($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
break;
default:
throw new Exception('暂不支持这个报表');
}
$file = excelSaveFile($res, $info['name'] . '_' . $info['relaComName'] . '_' . date('YmdHis'));
Db::name('exec_rela_com_no')
->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')),
'remark' => ''
]);
//$output->writeln('【' . $info['id'] . '】该预约记录处理成功');
} //else $output->writeln('没有可供处理的报表预约记录');
} catch (Exception $exception) {
Db::name('exec_rela_com_no')
->where(['id' => $info['id'], 'status' => 1])//status==1 待处理
->update([
'status' => 0, //status==2 处理完成
'down_url' => '',
'updatetime' => date('Y-m-d H:i:s'),
'expiretime' => date('Y-m-d H:i:s', strtotime('+7 day')),
'remark' => $exception->getMessage()
]);
//$output->writeln($exception->getMessage() . '|' . $exception->getFile() . '|' . $exception->getLine());
}
}
//【财务报表】退货台账-业务口径
private function A(string $start_date = '', string $end_date = '', string $relaComNo = '', string $relaComName = '')
{
$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_name 客户名称,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, 's.supplierNo' => $relaComNo])
->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['客户编码']])) {
$temp = $userCommon->handle('cInfo', ['companyNo' => $value['客户编码']]);
$tmp_customer[$value['客户编码']] = $temp['data']['itemid'] ?? '';
}
$value['itemid'] = $tmp_customer[$value['客户编码']];
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');
$value['业务公司编码'] = $relaComNo;
$value['业务公司名称'] = $relaComName;
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 = '', string $relaComNo = '', string $relaComName = '')
{
$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)
->where('wpo.companyNo', $relaComNo)
->whereBetween('wgs.updatetime', [$start_date, $end_date])
->order('wgs.updatetime', 'desc')
->cursor();
$list = [];
foreach ($data as $value) {
$cat = made($value['一级分类']);
$value['申请部门'] = get_company_name_by_uid(intval($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);
// $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","");
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天之后
}
$value['业务公司编码'] = $relaComNo;
$value['业务公司名称'] = $relaComName;
yield $list[] = $value;
}
return $list;
}
//【财务报表】退货台账
private function C(string $start_date = '', string $end_date = '', string $relaComNo = '', string $relaComName = '')
{
$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])
->where('ws.supplierNo', $relaComNo)
->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.customerName 客户名称,
ws.customer_code
")->order("wtd.addtime")
->cursor();
$userCommon = User::getIns();
$temp_customer = [];
$com = [];
$cat = Db::name('cat')->where(['is_del' => 0, 'status' => 1, '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');
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'] ?? '';
}
$value['分公司'] = $temp_customer[$value['customer_code']] ?? '';
$value['业务部门'] = get_company_name_by_uid(intval($value['业务部门']));
$value['财务核算码'] = $cat[$value['三级分类']] ?? '';//Db::name("cat")->where(["id" => $value['三级分类']])->value("fund_code", '');
$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")->field('supplierNo,supplierName,good_unit,tax')->where(["spuCode" => $value['产品编码']])->find();
} else {
$good = Db::name("good_basic")->field('supplierNo,supplierName,good_unit,tax')->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['供应商名称'] = $good['supplierName'];
}
$value['单位'] = isset($good['good_unit']) ? $unit[$good['good_unit']] ?? '' : '';
$value['税点'] = isset($good['tax']) ? $good['tax'] . '%' : "";
$value['平台类型'] = $platform[$value['平台类型']] ?? '';
$value['业务公司编码'] = $relaComNo;
$value['业务公司名称'] = $relaComName;
unset($value['customer_code']);
yield $com[] = $value;
}
return $com;
}
//【财务报表】出入库明细
public function D($start, $end, string $relaComNo = '', string $relaComName = '')
{
$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)
->where('a.supplierNo', $relaComNo)
->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 '业务人员',
if(a.pay_id>0,'是','否') 是否拆单,
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`.`diff_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 = [];
$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');
$sale_return_num = Db::name('sale_return')->where('is_del', 0)->group('orderCode')->column('sum(num) ', 'orderCode');
$temp = [];
foreach ($list as $value) {
if (!isset($temp[$value['业务部门名称']])) $temp[$value['业务部门名称']] = get_company_name_by_uid($value['业务部门名称']);
$value['业务部门名称'] = $temp[$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['售前退货数量'] = $sale_return_num[$value['确认单编号']] ?? 0;
$value['售前退货金额'] = round($value['售前退货数量'] * $value['售前退货金额'], 2);
$value['业务公司编码'] = $relaComNo;
$value['业务公司名称'] = $relaComName;
yield $data[] = $value;
}
$cgd = $this->GetWlb($start, $end);
foreach ($cgd as $key => $value) {
yield $data[] = $value;
}
return $data;
}
//【财务报表】库存品入库明细
public function E($start, $end, string $relaComNo = '', string $relaComName = '')
{
$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' => 2,
'wpi.is_del' => 0,
'wpo.order_type' => 1,
'wpo.order_source' => 0,
'wpo.companyNo' => $relaComNo,
])
->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 '商品名称',
wpi.send_num'入库数量',
round( wpi.send_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 = [];
$all_warehouse_type = Db::name('warehouse_type')->column('name', 'id');
$all_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['财务核算编号'] = $all_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['仓库类型'] = $all_warehouse_type[$value['仓库类型']] ?? '';
$value['业务公司编码'] = $relaComNo;
$value['业务公司名称'] = $relaComName;
yield $data[] = $value;
}
return $data;
}
//【财务报表】库存品出库明细
public function F($start, $end, string $relaComNo = '', string $relaComName = '')
{
$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]], ['a.supplierNo', '=', $relaComNo]])
->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 = [];
$all_warehouse_type = Db::name('warehouse_type')->column('name', 'id');
$all_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['财务核算编号'] = $all_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['仓库类型'] = $all_warehouse_type[$value['仓库类型']] ?? '';
$value['业务公司编码'] = $relaComNo;
$value['业务公司名称'] = $relaComName;
yield $data[] = $value;
}
return $data;
}
//网络部出入库
public function GetWlb($start, $end)
{
$db = Db::connect("mysql_cxinv");
$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 '业务人员',
if(cxCode='','否','是') '是否拆单',
'网络部采购单' '订单类型',
'' 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 `物流费`,
0 AS `公差金额`,
goodPrice AS `成本合计`,
totalPrice AS `采购货款`,
tax/100 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 = '', string $relaComNo = '', string $relaComName = '')
{
$all_order_type = [1 => '备库', 2 => '非库存', 3 => '咨询采反', 4 => '项目采反', 5 => '平台部订单销售库存', 6 => '平台部订单销售非库存'];//订单类型
$all_sale_status = [0 => '待发货', 1 => '待发货完成', 2 => '发货已完成', 3 => '订单已取消'];//订单状态(sale表里的status)
$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 创建人,"" 部门,"" 销售方公司,"" 一级组织,"" 二级组织,"" 购买方公司,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.customerName 购买方公司,s.supplierName 销售方公司,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, 's.supplierNo' => $relaComNo])
->whereBetween('s.addtime', [$start_date, $end_date])
->order('s.addtime', 'desc')
->cursor();
$list = $tmp_customer = $tmp = [];
foreach ($data as $value) {
if (!isset($tmp[$value['apply_id']])) $tmp[$value['apply_id']] = get_company_name_by_uid($value['apply_id']);
$value['部门'] = $tmp[$value['apply_id']];
if (!isset($tmp_customer[$value['customer_code']])) {
$tmp = User::getIns()->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;
$value['业务公司编码'] = $relaComNo;
$value['业务公司名称'] = $relaComName;
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']);
unset($value['apply_id']);
yield $list[] = $value;
}
return $list;
}
//【库存报表】库存日报及预警汇总表
private function H(string $start_date = '', string $end_date = '', string $relaComNo = '', string $relaComName = '')
{
$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, 'wgb.is_stock' => 1, 'wgb.companyNo' => $relaComNo])
->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);//365天之后
$value['库存天数'] = bcdiv((string)(time() - strtotime($value['最近入库时间'])), (string)(24 * 3600));//365天之后
}
$value['业务公司编码'] = $relaComNo;
$value['业务公司名称'] = $relaComName;
yield $list[] = $value;
}
return $list;
}
//【库存报表】出入库明细
public function I(string $start = '', string $end = '', string $relaComNo = '', string $relaComName = '')
{
$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)
->where('a.supplierNo', $relaComNo)
->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 = [];
$unit = Db::name("unit")->where('is_del', 0)->column('unit', '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['业务部门名称']] ?? '';
$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');
$value['业务公司编码'] = $relaComNo;
$value['业务公司名称'] = $relaComName;
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 = '', string $relaComNo = '', string $relaComName = '')
{
$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' => [4, 6],
'wpo.order_type' => 1,
'wpo.order_source' => 0,
'wpo.companyNo' => $relaComNo,
])
->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 = $tmp = [];
$all_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['仓库类型'] = $all_warehouse_type[$value['仓库类型']] ?? '';
$value['业务公司编码'] = $relaComNo;
$value['业务公司名称'] = $relaComName;
yield $data[] = $value;
}
return $data;
}
//【库存报表】库存品出库明细
public function K(string $start = '', string $end = '', string $relaComNo = '', string $relaComName = '')
{
$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]], ['a.supplierNo', '=', $relaComNo]])
->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 = [];
$all_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['仓库类型'] = $all_warehouse_type[$value['仓库类型']] ?? '';
$value['业务公司编码'] = $relaComNo;
$value['业务公司名称'] = $relaComName;
yield $data[] = $value;
}
return $data;
}
/**
* 新出库单明细
* @param string $start
* @param string $end
* @return \Generator
* @throws \think\db\exception\DataNotFoundException
* @throws \think\db\exception\DbException
* @throws \think\db\exception\ModelNotFoundException
*
*/
private function NewOut(string $start, string $end, string $relaComNo = '', string $relaComName = '')
{
$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]], ['b.supplierNo', '=', $relaComNo]])
->cursor();
$list = $tmp = [];
$all_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['财务核算编号'] = $all_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")->field('tax,supplierNo,supplierName')->where(["spuCode" => $value['商品编号']])->findOrEmpty();
} else {
$good = Db::name("good")->field('tax,supplierNo,supplierName')->where(["spuCode" => $value['商品编号']])->findOrEmpty();
}
$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");
$value['业务公司编码'] = $relaComNo;
$value['业务公司名称'] = $relaComName;
unset($value['order_type']);
yield $list[] = $value;
}
}
/**每日库存记录
* @param string $start
* @param string $end
* @return \Generator
* @throws \think\db\exception\DataNotFoundException
* @throws \think\db\exception\DbException
* @throws \think\db\exception\ModelNotFoundException
*/
private function EveryDayStock(string $start, string $end, string $relaComNo = '', string $relaComName = '')
{
$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, 'd.companyNo' => $relaComNo])
->whereBetween("a.updatetime", [$start, $end])
->cursor();
$all_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['财务核算编号'] = $all_fund_code[$value['cat_id']] ?? '';
unset($value['cat_id']);
$value['一级分类'] = isset($cat[0]['name']) ? $cat[0]['name'] : "";
$value['业务公司编码'] = $relaComNo;
$value['业务公司名称'] = $relaComName;
yield $value;
}
}
/**获取商品成本列表
* @return \Generator
*/
private function GetGoodNake(string $relaComNo = '', string $relaComName = '')
{
$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 '供应商名称'
")->where('b.companyNo', $relaComNo)->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');
$brand = Db::name('brand')->where(['is_del' => 0])->column('brand_name', 'id');
$noble = ["0" => "", "1" => "18K", "2" => "24K", "3" => "白银"];
$arr = ["待提交", '待完善成本', '待产品审核', '待财务定价', '待财务审核定价', '待上线', '已上线', '审核失败', '已下线'];
foreach ($list as $value) {
$value["品牌"] = $brand[$value["品牌"]] ?? '';
$value["分类"] = $cat[$value["分类"]] ?? '';
$value["上线平台"] = $platform[$value["上线平台"]] ?? '';
$value["上线状态"] = $arr[$value["上线状态"]];
$value['单位'] = $unit[$value['单位']] ?? '';
$value['贵金属信息'] = $noble[$value['贵金属信息']];
$value['业务公司编码'] = $relaComNo;
$value['业务公司名称'] = $relaComName;
yield $value;
}
}
/**获取商品阶梯售价列表
* @return \Generator
*/
private function GetGoodLadder(string $relaComNo = '', string $relaComName = '')
{
$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 '供应商名称'
")->where('b.companyNo', $relaComNo)->cursor();
$arr = ["待提交", '待完善成本', '待产品审核', '待财务定价', '待财务审核定价', '待上线', '已上线', '审核失败', '已下线'];
$noble = ["0" => "", "1" => "18K", "2" => "24K", "3" => "白银"];
$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');
$brand = Db::name('brand')->where(['is_del' => 0])->column('brand_name', 'id');
foreach ($list as $value) {
$value["品牌"] = $brand[$value["品牌"]] ?? '';
$value["分类"] = $cat[$value["分类"]] ?? '';
$value["上线平台"] = $platform[$value["上线平台"]] ?? '';
$value["上线状态"] = $arr[$value["上线状态"]];
$value['单位'] = $unit[$value['单位']] ?? '';
$value['贵金属信息'] = $noble[$value['贵金属信息']];
$value['业务公司编码'] = $relaComNo;
$value['业务公司名称'] = $relaComName;
yield $value;
}
}
private function GetBid(string $start, string $end, string $relaComNo = '', string $relaComName = '')
{
$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, 'c.companyNo' => $relaComNo])
->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');
$arr = ["待提交", '招标进行中', '招标已结束', '等待议价结果', '确认商品', '成功转单', '取消转单', '招标已暂停'];
$arr1 = ["待提交", "待任务结束", "待咨询确认", "议价待财务定价", "待主管确认", "待咨询确认定价", "咨询确认"];
$arr2 = ['待业务主管审核', '业务主管通过', '待财务专员审核', '业务驳回', '财务专员通过', '财务专员驳回', '财务主管通过', '待boss审核', '财务主管驳回', 'boss审核通过', 'boss越线通过', 'boss驳回', '取消议价'];
$tmp = $temp = [];
foreach ($list as $value) {
$value["商品分类"] = $cat[$value["商品分类"]] ?? '';
$value["反馈商品分类"] = $cat[$value["反馈商品分类"]] ?? '';
$value["反馈商品单位"] = $unit[$value["反馈商品单位"]] ?? '';
if (!isset($tmp[$value['客户名称']])) {
$names = User::getIns()->handle('getCodeAndName', ['code' => $value['客户名称']]);
$tmp[$value['客户名称']] = $names['data'][$value['客户名称']];
}
$value["业务公司"] = $relaComName;
$value["所属平台"] = $platform[$value["所属平台"]] ?? '';
if (!isset($temp[$value['申请人部门']])) $temp[$value['申请人部门']] = get_company_name_by_uid($value['申请人部门']);
$value['申请人部门'] = $temp[$value['申请人部门']] ?? '';
//状态 ；1招标进行中 2 招标已结束 3 等待议价结果 4.确认商品 5成功转单 6 取消转单 7 招标已暂
$value["状态"] = $arr[$value["状态"]] ?? "";
//状态0 待提交 1 提交待确认 2 发起议价 3 财务定价4 主管定价5 确认采购
$value["反馈商品状态"] = $arr1[$value["反馈商品状态"]] ?? "";
//状态 1 待主管审批 2.待财务专员审批 3. 待财务主管审批 4 审批通过 5 主管驳回 6 财务主管驳回 7 已取消议价
$value["议价状态"] = $arr2[$value["议价状态"]] ?? "";
$value['单位'] = $unit[$value["单位"]] ?? '';
$value['议价原因'] = $result[$value['议价原因']] ?? '';
$value['客户名称'] = $tmp[$value['客户名称']] ?? '';
$value['业务公司编码'] = $relaComNo;
$value['业务公司名称'] = $relaComName;
yield $value;
}
}
}