<?php
namespace app\admin\controller;
//use app\abutment\model\SupplierRelationUser;
//use app\abutment\model\SupplierUser;
use think\facade\Db;
//前端报表处理类
class SaleReport extends Base
{
//订单来源:1直接下单,2咨询,3项目,4平台,5有赞,6售后补换货,7报备转单,8支付渠道9采销录单10结算录单
private $all_order_type = [1 => '备库', 2 => '非库存', 3 => '咨询采反', 4 => '报备转单'];//订单类型
private $all_stock = [0 => '非库存品', 1 => '库存品'];//是否库存品
private $all_sale_status = [0 => '待发货', 1 => '待发货完成', 2 => '发货已完成', 3 => '订单已取消'];//订单状态(sale表里的status)
private $is_addrs = [0 => '是', 1 => '否'];//是否一地(数据库中存的是'是否多地'，逻辑相反)
private $is_custom = [0 => '否', 1 => '是'];//是否定制
private $consult_info_status = [1 => '招标进行中', 2 => '招标已结束', 3 => '等待议价结果', 4 => '确认商品', 5 => '成功转单', 6 => '取消转单', 7 => '招标已暂停'];//状态
private $noble_metal = [1 => '18K', 2 => '24K', 3 => '白银'];//贵金属种类
private $all_order_return_status = [1 => '待业务审核', 2 => '待采购审核', 3 => '待设置仓库', 4 => '待客户退货', 5 => '售后已完成', 6 => '业务已驳回', 7 => '采购已驳回', 8 => '申请已取消'];
private $is_activity = [0 => '不参与活动', 1 => '参与活动'];
private $good_type = [1 => '正常商品', 2 => '赠品', 3 => '样品'];
private $sale_diff_status = [1 => '修改销售额', 2 => '不修改销售额'];
private $is_receive = [0 => '未收到', 1 => '收到'];
private $bargain_order_status = [1 => '待主管审批', 2 => '待财务专员审批', 3 => '待财务主管审批', 4 => '审批通过', 5 => '主管驳回', 6 => '财务主管驳回', 7 => '已取消议价'];
private $order_out_status = [0 => '待采购备货', 1 => '待库管发货', 2 => '已发货待收货', 3 => '已收货', 4 => '已全部退货'];
private $except_code = [0 => [1 => '退货', 2 => '补发'], 1 => [1 => '退货', 2 => '换货'],];//期望意愿
private $order_back_status = [0 => '待申请', 1 => '待验收', 2 => '待验收审核', 3 => '待业务审核', 4 => '完成退货'];
private $order_source = [0 => '备库下单', 1 => '直接下单', 2 => '咨询下单', 3 => '项目下单', 4 => '平台导入',5=>'有赞转单',6=>"售后补换货",7=>'报备转单',8=>'支付渠道',9=>'采销录单',10=>'结算录单'];
private $plat_type = [0=>"无",1=>"ToB",2=>"ToC"];
//【一、管理报表】1.日报_列表
public function consultDay()
{
$param = $this->request->only(['token', 'date' => date('Y-m-d')], 'post', 'trim');
$rs_1 = Db::name('consult_info')
->alias('ci')
->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
->where([
['ci.is_del', '=', 0],
['ci.addtime', 'between', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']],
])
->group('co.createrid,co.creater')
->column('COUNT(ci.id) 当日咨询单数量,SUM(ci.budget_price*ci.num) 金额,co.creater 创建人', 'co.createrid');
$rs_2 = Db::name('sale')
->where([
['is_del', '=', 0],
['addtime', 'between', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']],
])
->group('apply_id,apply_name')
->column('COUNT(id) 当日下单数量,SUM(total_price) 金额,apply_name 创建人', 'apply_id');
$rs_3 = Db::name('order_out')
->alias('oo')
->leftJoin('sale s', 's.orderCode=oo.orderCode')
->where([
['oo.status', '=', 3],
['oo.addtime', 'between', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']],
])
->group('oo.apply_id,oo.apply_name')
->column('COUNT(oo.id) 当日签收数量,SUM(s.total_price) 金额,oo.apply_name 创建人', 'oo.apply_id');
$rs_4 = Db::name('order_return')
->alias('or')
->leftJoin('sale s', 's.orderCode=or.orderCode')
->where([
['or.is_del', '=', 0],
['or.addtime', 'between', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']],
])
->group('or.apply_id,or.apply_name')
->column('COUNT(or.id) 单日售后单数,SUM(s.total_price) 金额,or.apply_name 创建人', 'or.apply_id');
$all_ids = array_unique(array_merge(array_keys($rs_1), array_keys($rs_2), array_keys($rs_3), array_keys($rs_4)));
$all_company = get_company_name_by_uid($all_ids);//获取创建人的所属部门
$list = [];
foreach ($all_ids as $id) {
$val = [];
if (isset($rs_1[$id]['创建人'])) $val['creater'] = $rs_1[$id]['创建人'];
elseif (isset($rs_2[$id]['创建人'])) $val['creater'] = $rs_2[$id]['创建人'];
elseif (isset($rs_3[$id]['创建人'])) $val['creater'] = $rs_3[$id]['创建人'];
elseif (isset($rs_4[$id]['创建人'])) $val['creater'] = $rs_4[$id]['创建人'];
else $val['creater'] = '';
$val['creater_company'] = $all_company[$id] ?? '';//get_company_name_by_uid($id);//获取创建人的所属部门
$val['consult_num'] = isset($rs_1[$id]['当日咨询单数量']) ? $rs_1[$id]['当日咨询单数量'] : '';
$val['consult_money'] = isset($rs_1[$id]['金额']) ? $rs_1[$id]['金额'] : '';
$val['sale_num'] = isset($rs_2[$id]['当日下单数量']) ? $rs_2[$id]['当日下单数量'] : '';
$val['sale_money'] = isset($rs_2[$id]['金额']) ? $rs_2[$id]['金额'] : '';
$val['receipt_num'] = isset($rs_3[$id]['当日签收数量']) ? $rs_3[$id]['当日签收数量'] : '';
$val['receipt_money'] = isset($rs_3[$id]['金额']) ? $rs_3[$id]['金额'] : '';
$val['invoice_num'] = '';
$val['invoice_money'] = '';
$val['after_sales_num'] = isset($rs_4[$id]['单日售后单数']) ? $rs_4[$id]['单日售后单数'] : '';
$val['after_sales_money'] = isset($rs_4[$id]['金额']) ? $rs_4[$id]['金额'] : '';
$val['payment_num'] = '';
$val['payment_money'] = '';
$list[] = $val;
}
return app_show(0, '请求成功', ['list' => $list]);
}
//【一、管理报表】1.日报_导出
public function consultDayExport()
{
$param = $this->request->only(['token', 'date' => date('Y-m-d')], 'post', 'trim');
$rs_1 = Db::name('consult_info')
->alias('ci')
->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
->where([
['ci.is_del', '=', 0],
['ci.addtime', 'between', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']],
])
->group('co.createrid,co.creater')
->column('COUNT(ci.id) 当日咨询单数量,SUM(ci.budget_price*ci.num) 金额,co.creater 创建人', 'co.createrid');
$rs_2 = Db::name('sale')
->where([
['is_del', '=', 0],
['addtime', 'between', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']],
])
->group('apply_id,apply_name')
->column('COUNT(id) 当日下单数量,SUM(total_price) 金额,apply_name 创建人', 'apply_id');
$rs_3 = Db::name('order_out')
->alias('oo')
->leftJoin('sale s', 's.orderCode=oo.orderCode')
->where([
['oo.status', '=', 3],
['oo.addtime', 'between', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']],
])
->group('oo.apply_id,oo.apply_name')
->column('COUNT(oo.id) 当日签收数量,SUM(s.total_price) 金额,oo.apply_name 创建人', 'oo.apply_id');
$rs_4 = Db::name('order_return')
->alias('or')
->leftJoin('sale s', 's.orderCode=or.orderCode')
->where([
['or.is_del', '=', 0],
['or.addtime', 'between', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']],
])
->group('or.apply_id,or.apply_name')
->column('COUNT(or.id) 单日售后单数,SUM(s.total_price) 金额,or.apply_name 创建人', 'or.apply_id');
$all_ids = array_unique(array_merge(array_keys($rs_1), array_keys($rs_2), array_keys($rs_3), array_keys($rs_4)));
$all_company = get_company_name_by_uid($all_ids);//获取创建人的所属部门
$list = [];
foreach ($all_ids as $id) {
$val = [];
if (isset($rs_1[$id]['创建人'])) $val['创建人'] = $rs_1[$id]['创建人'];
elseif (isset($rs_2[$id]['创建人'])) $val['创建人'] = $rs_2[$id]['创建人'];
elseif (isset($rs_3[$id]['创建人'])) $val['创建人'] = $rs_3[$id]['创建人'];
elseif (isset($rs_4[$id]['创建人'])) $val['创建人'] = $rs_4[$id]['创建人'];
else $val['创建人'] = '';
$val['创建人部门'] = $all_company[$id] ?? '';
$val['当日咨询单数量'] = isset($rs_1[$id]['当日咨询单数量']) ? $rs_1[$id]['当日咨询单数量'] : '';
$val['当日咨询单金额'] = isset($rs_1[$id]['金额']) ? $rs_1[$id]['金额'] : '';
$val['当日下单数量'] = isset($rs_2[$id]['当日下单数量']) ? $rs_2[$id]['当日下单数量'] : '';
$val['当日下单金额'] = isset($rs_2[$id]['金额']) ? $rs_2[$id]['金额'] : '';
$val['当日签收数量'] = isset($rs_3[$id]['当日签收数量']) ? $rs_3[$id]['当日签收数量'] : '';
$val['当日签收金额'] = isset($rs_3[$id]['金额']) ? $rs_3[$id]['金额'] : '';
$val['当日申报发票数量'] = '';
$val['当日申报发票金额'] = '';
$val['单日售后单数'] = isset($rs_4[$id]['单日售后单数']) ? $rs_4[$id]['单日售后单数'] : '';
$val['单日售后金额'] = isset($rs_4[$id]['金额']) ? $rs_4[$id]['金额'] : '';
$val['当日回款单数'] = '';
$val['当日回款金额'] = '';
$list[] = $val;
}
if (empty($list)) $list[] = ['没有相关可导出的数据'];
excelSave('咨询单报表' . date('YmdHis'), array_keys($list[0]), $list);
}
//【一、管理报表】2.竞价反馈报表
public function consultInfo()
{
$param = $this->request->only([
'token',
'start_date' => '',
'end_date' => '',
'page' => 1,
'size' => 15,
], 'post', 'trim');
$where = [['ci.is_del', '=', 0]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['ci.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
$count = Db::name('consult_info')
->alias('ci')
->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
->leftJoin('unit u', 'u.id=ci.unit')
// ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
->leftJoin('consult_bids cb', 'cb.infoNo=ci.infoNo')
->where($where)
->order('ci.addtime', 'desc')
->count('ci.id');
$data = Db::name('consult_info')
->alias('ci')
->field('co.creater,ci.addtime,ci.infoNo,cb.bidNo,"" companyName,ci.good_name,ci.num,u.unit,ci.budget_price,cb.id bid_id,ci.status,0 itemid,co.createrid,co.khNo')
->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
->leftJoin('unit u', 'u.id=ci.unit')
// ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
->leftJoin('consult_bids cb', 'cb.infoNo=ci.infoNo')
->where($where)
->page($param['page'], $param['size'])
->order('ci.addtime', 'desc')
->cursor();
$userCommon = \app\admin\common\User::getIns();
$list = $customer_tmp = $company_tmp = $customer2_tmp = [];
foreach ($data as $value) {
$value['total_fee'] = round($value['budget_price'] * $value['num'], 2);
$value['is_bids'] = is_null($value['bid_id']) ? '否' : '是';
$value['is_zhuandan'] = $value['status'] == 5 ? '是' : '否';//5成功转单
if (!isset($customer2_tmp[$value['khNo']])) {
$temp = $userCommon->handle('cInfo', ['companyNo' => $value['khNo']]);
$customer2_tmp[$value['khNo']] = [
'itemid' => $temp['data']['itemid'],
'companyName' => $temp['data']['companyName'],
];
}
$value['itemid'] = $customer2_tmp[$value['khNo']]['itemid'];
$value['companyName'] = $customer2_tmp[$value['khNo']]['companyName'];
if (!empty($value['itemid'])) {
if (!isset($customer_tmp[$value['itemid']])) $customer_tmp[$value['itemid']] = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
$value['first_customer_org'] = isset($customer_tmp[$value['itemid']][1]) ? $customer_tmp[$value['itemid']][1] : '';
$value['second_customer_org'] = isset($customer_tmp[$value['itemid']][2]) ? $customer_tmp[$value['itemid']][2] : '';
}
if (!isset($company_tmp[$value['createrid']])) $company_tmp[$value['createrid']] = $value['createrid'];//get_company_name_by_uid($value['createrid']);//获取创建人的所属部门
// $value['creater_company'] = $company_tmp[$value['createrid']] ?? '';
$list[] = $value;
}
$all_company = get_company_name_by_uid(array_values($company_tmp));
foreach ($list as &$val) {
$val['creater_company'] = $all_company[$val['createrid']] ?? '';
}
return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
}
//【一、管理报表】2.竞价反馈报表_导出
public function consultInfoExport()
{
$param = $this->request->only(['token', 'start_date' => '', 'end_date' => ''], 'post', 'trim');
$where = [['ci.is_del', '=', 0]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['ci.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
$data = Db::name('consult_info')
->alias('ci')
->field('co.creater 创建人,"" 创建人部门,ci.addtime 竞价时间,ci.infoNo 竞价单号,cb.bidNo 反馈编号,"" 一级企业,"" 二级企业,"" 客户名称,ci.good_name 产品名称,ci.num 数量,u.unit 单位,ci.budget_price 单价, "" 金额,cb.id 是否采反,ci.status 是否转单,co.createrid,co.khNo')
->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
->leftJoin('unit u', 'u.id=ci.unit')
->leftJoin('consult_bids cb', 'cb.infoNo=ci.infoNo')
->where($where)
->order('ci.addtime', 'desc')
->cursor();
$userCommon = \app\admin\common\User::getIns();
$list = $customer_tmp = $company_tmp = $customer2_tmp = [];
foreach ($data as $value) {
if (!isset($company_tmp[$value['createrid']])) $company_tmp[$value['createrid']] = $value['createrid'];//get_company_name_by_uid($value['createrid']);//获取创建人的所属部门
// $value['创建人部门'] = $company_tmp[$value['createrid']] ?? '';
$value['金额'] = round($value['单价'] * $value['数量'], 2);
$value['是否采反'] = is_null($value['是否采反']) ? '否' : '是';
$value['是否转单'] = $value['是否转单'] == 5 ? '是' : '否';//5成功转单
if (!isset($customer2_tmp[$value['khNo']])) {
$temp = $userCommon->handle('cInfo', ['companyNo' => $value['khNo']]);
$customer2_tmp[$value['khNo']] = [
'itemid' => $temp['data']['itemid'],
'companyName' => $temp['data']['companyName'],
];
}
$value['itemid'] = $customer2_tmp[$value['khNo']]['itemid'];
$value['客户名称'] = $customer2_tmp[$value['khNo']]['companyName'];
if (!empty($value['itemid'])) {
if (!isset($customer_tmp[$value['itemid']])) $customer_tmp[$value['itemid']] = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
$value['一级企业'] = isset($customer_tmp[$value['itemid']][1]) ? $customer_tmp[$value['itemid']][1] : '';
$value['二级企业'] = isset($customer_tmp[$value['itemid']][2]) ? $customer_tmp[$value['itemid']][2] : '';
}
// unset($value['createrid']);
unset($value['itemid']);
unset($value['khNo']);
$list[] = $value;
}
$all_company = get_company_name_by_uid(array_values($company_tmp));
foreach ($list as &$val) {
$val['创建人部门'] = $all_company[$val['createrid']] ?? '';
unset($val['createrid']);
}
unset($customer_tmp);
unset($company_tmp);
unset($customer2_tmp);
unset($all_company);
if (empty($list)) $list[] = ['没有相关可导出的数据'];
excelSave('竞价反馈报表' . date('YmdHis'), array_keys($list[0]), $list);
}
//【一、管理报表】2.1.竞价单报表
public function consultInfoNew()
{
$param = $this->request->only([
'token',
'start_date' => '',
'end_date' => '',
'page' => 1,
'size' => 15,
], 'post', 'trim');
$where = [['ci.is_del', '=', 0]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['ci.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
$count = Db::name('consult_info')
->alias('ci')
->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
->leftJoin('unit u', 'u.id=ci.unit')
// ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
->where($where)
->order('ci.addtime', 'desc')
->count('ci.id');
$data = Db::name('consult_info')
->alias('ci')
->field('co.creater,ci.addtime,ci.infoNo,"" companyName,ci.good_name,ci.num,u.unit,ci.budget_price,ci.status,0 itemid,co.createrid,co.khNo')
->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
->leftJoin('unit u', 'u.id=ci.unit')
// ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
->where($where)
->page($param['page'], $param['size'])
->order('ci.addtime', 'desc')
->cursor();
$userCommon = \app\admin\common\User::getIns();
$list = $customer_tmp = $company_tmp = $customer2_tmp = [];
foreach ($data as $value) {
$value['total_fee'] = round($value['budget_price'] * $value['num'], 2);
$value['is_zhuandan'] = $value['status'] == 5 ? '是' : '否';//5成功转单
if (!isset($customer2_tmp[$value['khNo']])) {
$temp = $userCommon->handle('cInfo', ['companyNo' => $value['khNo']]);
$customer2_tmp[$value['khNo']] = [
'itemid' => $temp['data']['itemid'],
'companyName' => $temp['data']['companyName'],
];
}
$value['itemid'] = $customer2_tmp[$value['khNo']]['itemid'];
$value['companyName'] = $customer2_tmp[$value['khNo']]['companyName'];
if (!empty($value['itemid'])) {
if (!isset($customer_tmp[$value['itemid']])) $customer_tmp[$value['itemid']] = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
$value['first_customer_org'] = isset($customer_tmp[$value['itemid']][1]) ? $customer_tmp[$value['itemid']][1] : '';
$value['second_customer_org'] = isset($customer_tmp[$value['itemid']][2]) ? $customer_tmp[$value['itemid']][2] : '';
}
if (!isset($company_tmp[$value['createrid']])) $company_tmp[$value['createrid']] = $value['createrid'];//get_company_name_by_uid($value['createrid']);//获取创建人的所属部门
// $value['creater_company'] = $company_tmp[$value['createrid']] ?? '';
$list[] = $value;
}
$all_company = get_company_name_by_uid(array_values($company_tmp));
foreach ($list as &$val) {
$val['creater_company'] = $all_company[$val['createrid']] ?? '';
// unset($val['createrid']);
}
unset($customer_tmp);
unset($company_tmp);
unset($customer2_tmp);
unset($all_company);
return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
}
//【一、管理报表】2.1.竞价单报表_导出
public function consultInfoNewExport()
{
$param = $this->request->only([
'token',
'start_date' => '',
'end_date' => '',
], 'post', 'trim');
$where = [['ci.is_del', '=', 0]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['ci.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
$data = Db::name('consult_info')
->alias('ci')
->field('co.creater 创建人,"" 创建人部门,ci.addtime 竞价时间,ci.infoNo 竞价单号,"" 一级企业,"" 二级企业,"" 客户名称,ci.good_name 产品名称,ci.num 数量,u.unit 单位,ci.budget_price 单价, "" 金额,ci.status 是否转单,co.createrid,co.khNo')
->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
->leftJoin('unit u', 'u.id=ci.unit')
// ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
->where($where)
->order('ci.addtime', 'desc')
->cursor();
$userCommon = \app\admin\common\User::getIns();
$list = $customer_tmp = $company_tmp = $customer2_tmp = [];
foreach ($data as $value) {
if (!isset($company_tmp[$value['createrid']])) $company_tmp[$value['createrid']] = $value['createrid'];//get_company_name_by_uid($value['createrid']);//获取创建人的所属部门
// $value['创建人部门'] = $company_tmp[$value['createrid']] ?? '';
$value['金额'] = round($value['单价'] * $value['数量'], 2);
$value['是否转单'] = $value['是否转单'] == 5 ? '是' : '否';//5成功转单
if (!isset($customer2_tmp[$value['khNo']])) {
$temp = $userCommon->handle('cInfo', ['companyNo' => $value['khNo']]);
$customer2_tmp[$value['khNo']] = [
'itemid' => $temp['data']['itemid'],
'companyName' => $temp['data']['companyName'],
];
}
$value['itemid'] = $customer2_tmp[$value['khNo']]['itemid'];
$value['客户名称'] = $customer2_tmp[$value['khNo']]['companyName'];
if (!empty($value['itemid'])) {
if (!isset($customer_tmp[$value['itemid']])) $customer_tmp[$value['itemid']] = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
$value['一级企业'] = isset($customer_tmp[$value['itemid']][1]) ? $customer_tmp[$value['itemid']][1] : '';
$value['二级企业'] = isset($customer_tmp[$value['itemid']][2]) ? $customer_tmp[$value['itemid']][2] : '';
}
// unset($value['createrid']);
unset($value['itemid']);
unset($value['khNo']);
$list[] = $value;
}
$all_company = get_company_name_by_uid(array_values($company_tmp));
foreach ($list as &$val) {
$val['创建人部门'] = $all_company[$val['createrid']] ?? '';
unset($val['createrid']);
}
unset($customer_tmp);
unset($company_tmp);
unset($customer2_tmp);
unset($all_company);
if (empty($list)) $list[] = ['没有相关可导出的数据'];
excelSave('竞价单报表' . date('YmdHis'), array_keys($list[0]), $list);
}
//【一、管理报表】3.订单报表
public function saleList()
{
$param = $this->request->only(['token','depart_id'=>'', 'plat_type'=>'', 'start_date' => '', 'end_date' => '', 'page' => 1, 'size' => 15], 'post', 'trim');
$where = [['s.is_del', '=', 0]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['s.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
if ($param['depart_id'] != ''){
$uidArr = get_company_item_user_by_name('',$param['depart_id']);
$where[] = ['a.apply_id', 'in',$uidArr];
}
if ($param['plat_type'] !== ''){
$plat =Db::name('platform')->where(['use_type'=>$param['plat_type'],'is_del'=>0])->column('id');
$where[] = ['a.platform_id', 'in',$plat];
}
$count = Db::name('sale')
->alias('s')
// ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
->where($where)
->count('s.id');
$data = Db::name('sale')
->alias('s')
->field('s.apply_name,s.addtime,s.orderCode,"" companyName,s.good_name,s.good_num,s.sale_price,s.total_price,s.is_stock,s.order_type,s.good_code,s.apply_id,s.cat_id,s.customer_code')
// ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
->where($where)
->page($param['page'], $param['size'])
->order('s.addtime', 'desc')
->cursor();
$userCommon = \app\admin\common\User::getIns();
$list = $cat_tmp = $customer2_tmp = $company_tmp = [];
foreach ($data as $value) {
if (!isset($customer2_tmp[$value['customer_code']])) {
$temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
$customer2_tmp[$value['customer_code']] = [
'itemid' => $temp['data']['itemid'],
'companyName' => $temp['data']['companyName'],
];
}
$value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
$value['companyName'] = $customer2_tmp[$value['customer_code']]['companyName'];
if (!empty($value['itemid'])) {
$customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
$value['first_customer_org'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
$value['second_customer_org'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
}
if ($value['order_type'] == 3 || $value['order_type'] == 4) $value['unit'] = Db::name("good_zixun")->alias('gz')->leftJoin('unit u', 'u.id=gz.good_unit')->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])->value('u.unit');
else $value['unit'] = Db::name('good_basic')->alias('gb')->leftJoin('unit u', 'u.id=gb.good_unit')->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])->value('u.unit');
$value['is_stock'] = isset($this->all_stock[$value['is_stock']]) ? $this->all_stock[$value['is_stock']] : '';
$value['order_type'] = isset($this->all_order_type[$value['order_type']]) ? $this->all_order_type[$value['order_type']] : '';
if (!isset($company_tmp[$value['apply_id']])) $company_tmp[$value['apply_id']] = $value['apply_id'];
// $value['creater_company'] = get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门
if (!isset($cat_tmp[$value['cat_id']])) $cat_tmp[$value['cat_id']] = implode('/', array_column(made($value['cat_id']), 'name'));
$value['cat_name'] = $cat_tmp[$value['cat_id']] ?? '';
$list[] = $value;
}
$all_company = get_company_name_by_uid(array_values($company_tmp));
foreach ($list as &$val) {
$val['creater_company'] = $all_company[$val['apply_id']] ?? '';
}
return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
}
//【一、管理报表】3.订单报表_导出
public function saleExport()
{
$param = $this->request->only(['token',"depart_id"=>"", "plat_type"=>"",'start_date' => '', 'end_date' => ''], 'post', 'trim');
$where = [['s.is_del', '=', 0]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['s.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
if ($param['depart_id'] != ''){
$uidArr = get_company_item_user_by_name('',$param['depart_id']);
$where[] = ['a.apply_id', 'in',$uidArr];
}
if ($param['plat_type'] !== ''){
$plat =Db::name("platform")->where(["use_type"=>$param['plat_type'],"is_del"=>0])->column("id");
$where[] = ['a.platform_id', 'in',$plat];
}
$data = Db::name('sale')
->alias('s')
->field('s.apply_name 创建人,"" 创建人所属部门,s.addtime 下单时间,s.orderCode 订单号,"" 一级企业,"" 二级企业,"" 客户名称,s.good_name 商品名称,s.good_num 数量,"" 单位,s.sale_price 单价,s.total_price 金额,s.is_stock 是否库存,s.order_type 订单类型,s.good_code,s.apply_id,s.cat_id,s.customer_code')
// ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
->where($where)
->order('s.addtime', 'desc')
->cursor();
$userCommon = \app\admin\common\User::getIns();
$list = $tmp_customer = $tmp_item = $cat_tmp = $customer2_tmp = [];
foreach ($data as $value) {
if (!isset($customer2_tmp[$value['customer_code']])) {
$temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
$customer2_tmp[$value['customer_code']] = [
'itemid' => $temp['data']['itemid'],
'companyName' => $temp['data']['companyName'],
];
}
$value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
$value['客户名称'] = $customer2_tmp[$value['customer_code']]['companyName'];
if (!empty($value['itemid'])) {
if (!isset($tmp_customer[$value['itemid']])) $tmp_customer[$value['itemid']] = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
// $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
$value['一级企业'] = isset($tmp_customer[$value['itemid']][1]) ? $tmp_customer[$value['itemid']][1] : '';
$value['二级企业'] = isset($tmp_customer[$value['itemid']][2]) ? $tmp_customer[$value['itemid']][2] : '';
}
if ($value['订单类型'] == 3 || $value['订单类型'] == 4) {
$value['单位'] = Db::name("good_zixun")
->alias('gz')
->leftJoin('unit u', 'u.id=gz.good_unit')
->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])
->value('u.unit');
} else {
$value['单位'] = Db::name('good_basic')
->alias('gb')
->leftJoin('unit u', 'u.id=gb.good_unit')
->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])
->value('u.unit');
}
$value['是否库存'] = isset($this->all_stock[$value['是否库存']]) ? $this->all_stock[$value['是否库存']] : '';
$value['订单类型'] = isset($this->all_order_type[$value['订单类型']]) ? $this->all_order_type[$value['订单类型']] : '';
if (!isset($tmp_item[$value['apply_id']])) $tmp_item[$value['apply_id']] = $value['apply_id'];//get_company_name_by_uid($value['apply_id']);
// $value['创建人所属部门'] = $tmp_item[$value['apply_id']];//获取创建人的所属部门
if (!isset($cat_tmp[$value['cat_id']])) $cat_tmp[$value['cat_id']] = implode('/', array_column(made($value['cat_id']), 'name'));
$value['分类'] = $cat_tmp[$value['cat_id']] ?? '';
unset($value['itemid']);
// unset($value['apply_id']);
unset($value['good_code']);
unset($value['cat_id']);
unset($value['customer_code']);
$list[] = $value;
}
$all_company = get_company_name_by_uid(array_values($tmp_item));
foreach ($list as &$val) {
$val['创建人所属部门'] = $all_company[$val['apply_id']] ?? '';
unset($val['apply_id']);
}
unset($tmp_customer);
unset($tmp_item);
unset($cat_tmp);
unset($customer2_tmp);
if (empty($list)) $list[] = ['没有相关可导出的数据'];
excelSave('订单报表' . date('YmdHis'), array_keys($list[0]), $list);
}
//【一、管理报表】4.未发货报表_列表
public function saleNotOut()
{
$param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'start_arrive_date' => '', 'end_arrive_date' => '', 'page' => 1, 'size' => 15], 'post', 'trim');
$where = [['s.is_del', '=', 0], ['out.status', '=', 1]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['s.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
if ($param['start_arrive_date'] != '' && $param['end_arrive_date'] != '') $where[] = ['s.arrive_time', 'between', [$param['start_arrive_date'] . ' 00:00:00', $param['end_arrive_date'] . ' 23:59:59']];
$count = Db::name('sale')
->alias('s')
->leftJoin('order_out out', 'out.orderCode=s.orderCode AND out.is_del=0')
->where($where)
->order('s.addtime', 'desc')
->count('s.id');
$data = Db::name('sale')
->alias('s')
->field('s.apply_name,s.addtime,s.orderCode,"" companyName,s.good_name,s.good_num,s.sale_price,s.total_price,s.arrive_time,s.good_code,s.skuCode,s.is_stock,s.order_type,s.apply_id,s.customer_code')
// ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
->leftJoin('order_out out', 'out.orderCode=s.orderCode AND out.is_del=0')
->where($where)
->page($param['page'], $param['size'])
->order('s.addtime', 'desc')
->cursor();
$userCommon = \app\admin\common\User::getIns();
$list = $customer2_tmp = $company_tmp = [];
foreach ($data as $value) {
if (!isset($customer2_tmp[$value['customer_code']])) {
$temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
$customer2_tmp[$value['customer_code']] = [
'itemid' => $temp['data']['itemid']??"",
'companyName' => $temp['data']['companyName'],
];
}
$value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
$value['companyName'] = $customer2_tmp[$value['customer_code']]['companyName'];
if (!empty($value['itemid'])) {
$customer_org1 = array_column(get_top_customer_org($value['itemid'])??[], 'name', 'level');
$value['first_customer_org'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
$value['second_customer_org'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
}
if ($value['order_type'] == 3 || $value['order_type'] == 4) {
$value['unit'] = Db::name("good_zixun")
->alias('gz')
->leftJoin('unit u', 'u.id=gz.good_unit')
->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])
->value('u.unit');
} else {
$value['unit'] = Db::name('good_basic')
->alias('gb')
->leftJoin('unit u', 'u.id=gb.good_unit')
->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])
->value('u.unit');
}
if (!isset($company_tmp[$value['apply_id']])) $company_tmp[$value['apply_id']] = $value['apply_id'];
// $value['creater_company'] = get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门
$list[] = $value;
}
$all_company = get_company_name_by_uid(array_values($company_tmp));
foreach ($list as &$val) {
$val['creater_company'] = $all_company[$val['apply_id']] ?? '';
// unset($val['createrid']);
}
unset($customer2_tmp);
unset($company_tmp);
return app_show(0, '请求成功', ['count' => $count, 'list' => $list]);
}
//【一、管理报表】4.未发货报表_导出
public function saleNotOutExport()
{
$param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'start_arrive_date' => '', 'end_arrive_date' => ''], 'post', 'trim');
$where = [['s.is_del', '=', 0], ['out.status', '=', 1]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['s.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
if ($param['start_arrive_date'] != '' && $param['end_arrive_date'] != '') $where[] = ['s.arrive_time', 'between', [$param['start_arrive_date'] . ' 00:00:00', $param['end_arrive_date'] . ' 23:59:59']];
$data = Db::name('sale')
->alias('s')
->field('s.apply_name 创建人,"" 创建人所属部门,s.addtime 下单时间,s.orderCode 订单号,"" 一级企业,"" 二级企业,"" 客户名称,s.good_name 商品名称,s.good_num 数量,"" 单位,s.sale_price 单价,s.total_price 金额,s.arrive_time 承诺发货时间,s.good_code,s.skuCode,s.is_stock,s.order_type,s.apply_id,s.customer_code')
->leftJoin('order_out out', 'out.orderCode=s.orderCode AND out.is_del=0')
->where($where)
->order('s.addtime', 'desc')
->cursor();
$userCommon = \app\admin\common\User::getIns();
$list = $customer2_tmp = $company_tmp =$depart= [];
foreach ($data as $value) {
if (!isset($customer2_tmp[$value['customer_code']])) {
$temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
$customer2_tmp[$value['customer_code']] = [
'itemid' => $temp['data']['itemid']??"",
'companyName' => $temp['data']['companyName'],
];
}
$value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
$value['客户名称'] = $customer2_tmp[$value['customer_code']]['companyName'];
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] : '';
}
if (!isset($depart[$value['apply_id']])) {
$depart[$value['apply_id']] =get_company_name_by_uid($value['apply_id']);
}
$value['创建人所属部门'] = $depart[$value['apply_id']] ?? '';
if ($value['order_type'] == 3 || $value['order_type'] == 4) $value['单位'] = Db::name("good_zixun")->alias('gz')
->leftJoin('unit u', 'u.id=gz.good_unit')->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])->value('u.unit');
else $value['单位'] = Db::name('good_basic')->alias('gb')->leftJoin('unit u', 'u.id=gb.good_unit')->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])->value('u.unit');
if (!isset($company_tmp[$value['apply_id']])) $company_tmp[$value['apply_id']] = $value['apply_id'];
$value['创建人所属部门'] = get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门
unset($value['itemid']);
unset($value['good_code']);
unset($value['order_type']);
unset($value['apply_id']);
unset($value['customer_code']);
$list[] = $value;
}
unset($customer2_tmp);
unset($company_tmp);
if (empty($list)) $list[] = ['没有相关可导出的数据'];
excelSave('未发货报表' . date('YmdHis'), array_keys($list[0]), $list);
}
//【一、管理报表】5.未签收报表
public function saleNotSigned()
{
$param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'page' => 1, 'size' => 15], 'post', 'trim');
$where = [['oo.is_del', '=', 0], ['oo.status', '=', 2], ['s.is_del', '=', 0]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['s.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
$count = Db::name('order_out')
->alias('oo')
->leftJoin('sale s', 's.orderCode=oo.orderCode')
// ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
// ->leftJoin('order_out out', 'out.orderCode=s.orderCode AND out.is_del=0')
->where($where)
->count('oo.id');
$data = Db::name('order_out')
->alias('oo')
->field('oo.id,s.apply_name,s.addtime,s.orderCode,"" companyName,s.good_name,s.good_num,s.sale_price,s.total_price,oo.sendtime,s.good_code,s.order_type,s.apply_id,s.customer_code')
->leftJoin('sale s', 's.orderCode=oo.orderCode')
// ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
// ->leftJoin('order_out out', 'out.orderCode=s.orderCode AND out.is_del=0')
->where($where)
->page($param['page'], $param['size'])
->order('s.addtime', 'desc')
->cursor();
$userCommon = \app\admin\common\User::getIns();
$list = $customer2_tmp = $uid_tmp = [];
foreach ($data as $value) {
if (!isset($customer2_tmp[$value['customer_code']])) {
$temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
$customer2_tmp[$value['customer_code']] = [
'itemid' => $temp['data']['itemid'],
'companyName' => $temp['data']['companyName'],
];
}
$value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
$value['companyName'] = $customer2_tmp[$value['customer_code']]['companyName'];
if (!empty($value['itemid'])) {
$customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
$value['first_customer_org'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
$value['second_customer_org'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
}
if ($value['order_type'] == 3 || $value['order_type'] == 4) $value['unit'] = Db::name("good_zixun")->alias('gz')->leftJoin('unit u',
'u.id=gz.good_unit')->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])->value('u.unit');
else $value['unit'] = Db::name('good_basic')->alias('gb')->leftJoin('unit u', 'u.id=gb.good_unit')->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])->value('u.unit');
if (!isset($uid_tmp[$value['apply_id']])) $uid_tmp[$value['apply_id']] = $value['apply_id'];//get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门
// $value['creater_company'] = $uid_tmp[$value['apply_id']];
$list[] = $value;
}
$all_company = get_company_name_by_uid(array_values($uid_tmp));
foreach ($list as &$val) {
$val['creater_company'] = $all_company[$val['apply_id']] ?? '';
// unset($val['apply_id']);
}
unset($customer2_tmp);
unset($uid_tmp);
return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
}
//【一、管理报表】5.未签收报表_导出
public function saleNotSignedExport()
{
$param = $this->request->only(['token', 'start_date' => '', 'end_date' => ''], 'post', 'trim');
$where = [['s.is_del', '=', 0], ['oo.status', '=', 2]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['s.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
// $data = Db::name('order_out')
// ->alias('oo')
// ->field('s.apply_name 创建人,"" 创建人所属部门,s.addtime 下单时间,s.orderCode 订单号,"" 一级企业,"" 二级企业,csi.companyName 客户名称,s.good_name 商品名称,s.good_num 数量,"" 单位,s.sale_price 单价,s.total_price 金额,oo.sendtime 发货时间,csi.itemid,s.good_code,s.order_type,s.apply_id')
// ->leftJoin('sale s', 's.orderCode=oo.orderCode')
// ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
// ->leftJoin('order_out out', 'out.orderCode=s.orderCode')
// ->where($where)
// ->order('s.addtime', 'desc')
// ->cursor('oo.id');
// $list = [];
// foreach ($data as $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] : '';
// }
//
// if ($value['order_type'] == 3 || $value['order_type'] == 4) $value['单位'] = Db::name("good_zixun")->alias('gz')->leftJoin('unit u', 'u.id=gz.good_unit')->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])->value('u.unit');
// else $value['单位'] = Db::name('good_basic')->alias('gb')->leftJoin('unit u', 'u.id=gb.good_unit')->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])->value('u.unit');
//
// $value['创建人所属部门'] = get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门
//
// unset($value['itemid']);
// unset($value['good_code']);
// unset($value['order_type']);
// unset($value['apply_id']);
//
// $list[] = $value;
// }
$list = $this->saleNotSignedExportTemp($where);
excelSave('未签收报表' . date('YmdHis'), ['创建人', '创建人所属部门', '下单时间', '订单号', '一级企业', '二级企业', '客户名称', '商品名称', '数量', '单位', '单价', '金额', '发货时间'], $list);
}
//针对"【一、管理报表】5.未签收报表_导出"数据量太大
private function saleNotSignedExportTemp(array $where = [])
{
$data = Db::name('order_out')
->alias('oo')
->field('s.apply_name 创建人,"" 创建人所属部门,s.addtime 下单时间,s.orderCode 订单号,"" 一级企业,"" 二级企业,"" 客户名称,s.good_name 商品名称,s.good_num 数量,"" 单位,s.sale_price 单价,s.total_price 金额,oo.sendtime 发货时间,s.good_code,s.order_type,s.apply_id,s.customer_code')
->leftJoin('sale s', 's.orderCode=oo.orderCode')
// ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
->leftJoin('order_out out', 'out.orderCode=s.orderCode AND out.is_del=0')
->where($where)
->order('s.addtime', 'desc')
->cursor();
$userCommon = \app\admin\common\User::getIns();
$customer2_tmp = $uid_tmp = [];
foreach ($data as $value) {
if (!isset($customer2_tmp[$value['customer_code']])) {
$temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
$customer2_tmp[$value['customer_code']] = [
'itemid' => $temp['data']['itemid'],
'companyName' => $temp['data']['companyName'],
];
}
$value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
$value['客户名称'] = $customer2_tmp[$value['customer_code']]['companyName'];
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] : '';
}
if ($value['order_type'] == 3 || $value['order_type'] == 4) $value['单位'] = Db::name("good_zixun")->alias('gz')
->leftJoin('unit u', 'u.id=gz.good_unit')->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])->value('u.unit');
else $value['单位'] = Db::name('good_basic')->alias('gb')->leftJoin('unit u', 'u.id=gb.good_unit')->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])->value('u.unit');
if (!isset($uid_tmp[$value['apply_id']])) $uid_tmp[$value['apply_id']] = get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门
$value['创建人所属部门'] = $uid_tmp[$value['apply_id']];
unset($value['itemid']);
unset($value['good_code']);
unset($value['order_type']);
unset($value['apply_id']);
unset($value['customer_code']);
yield $value;
}
}
//【一、管理报表】6.售后报表
public function orderReturn()
{
$param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'status' => '', 'page' => 1, 'size' => 15], 'post', 'trim');
$where = [['or.is_del', '=', 0]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['or.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
if ($param['status'] != '') $where[] = ['or.status', '=', $param['status']];
$count = Db::name('order_return')
->alias('or')
->leftJoin('sale s', 's.orderCode=or.orderCode')
// ->leftJoin('customer_info csi', 'csi.companyNo=or.customer_code')
->leftJoin('order_out out', 'out.orderCode=s.orderCode')
->leftJoin('result_info ri', 'ri.id=or.error_code')
->where($where)
->count('or.id');
$data = Db::name('order_return')
->alias('or')
->field('or.addtime,or.apply_name,or.orderCode,"" companyName,or.good_name,or.total_num,s.sale_price,s.total_price,or.updatetime,ri.result,or.status,or.good_code,or.order_type,or.apply_id,or.customer_code')
->leftJoin('sale s', 's.orderCode=or.orderCode')
// ->leftJoin('customer_info csi', 'csi.companyNo=or.customer_code')
->leftJoin('order_out out', 'out.orderCode=s.orderCode')
->leftJoin('result_info ri', 'ri.id=or.error_code')
->where($where)
->page($param['page'], $param['size'])
->order('s.addtime', 'desc')
->cursor();
$userCommon = \app\admin\common\User::getIns();
$list = $customer2_tmp = $company_tmp = [];
foreach ($data as $value) {
if (!isset($customer2_tmp[$value['customer_code']])) {
$temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
$customer2_tmp[$value['customer_code']] = [
'itemid' => $temp['data']['itemid'],
'companyName' => $temp['data']['companyName'],
];
}
$value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
$value['companyName'] = $customer2_tmp[$value['customer_code']]['companyName'];
if (!empty($value['itemid'])) {
$customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
$value['first_customer_org'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
$value['second_customer_org'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
}
if (!isset($company_tmp[$value['apply_id']])) $company_tmp[$value['apply_id']] = $value['apply_id'];
// $value['creater_company'] = get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门
if ($value['order_type'] == 3 || $value['order_type'] == 4) $value['unit'] = Db::name("good_zixun")->alias('gz')->leftJoin('unit u', 'u.id=gz.good_unit')->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])->value('u.unit');
else $value['unit'] = Db::name('good_basic')->alias('gb')->leftJoin('unit u', 'u.id=gb.good_unit')->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])->value('u.unit');
$value['is_finish'] = $value['status'] == 5 ? '已完结' : '未完结';
$value['status'] = isset($this->all_order_return_status[$value['status']]) ? $this->all_order_return_status[$value['status']] : '';
$list[] = $value;
}
$all_company = get_company_name_by_uid(array_values($company_tmp));
foreach ($list as &$val) {
$val['creater_company'] = $all_company[$val['apply_id']] ?? '';
unset($val['apply_id']);
}
unset($customer2_tmp);
unset($company_tmp);
return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
}
//【一、管理报表】6.售后报表_导出
public function orderReturnExport()
{
$param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'status' => ''], 'post', 'trim');
$where = [['or.is_del', '=', 0]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['or.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
if ($param['status'] != '') $where[] = ['or.status', '=', $param['status']];
$data = Db::name('order_return')
->alias('or')
->field('or.addtime 售后时间,or.apply_name 创建人,"" 创建人部门,or.orderCode 订单号,"" 一级企业,"" 二级企业,"" 客户名称,or.good_name 商品名称,or.total_num 数量,"" 单位,s.sale_price 单价,s.total_price 金额,or.updatetime 签收时间,ri.result 异常原因,or.status 异常状态,"" 是否完结,or.good_code,or.order_type,or.apply_id,or.customer_code')
->leftJoin('sale s', 's.orderCode=or.orderCode')
// ->leftJoin('customer_info csi', 'csi.companyNo=or.customer_code')
->leftJoin('order_out out', 'out.orderCode=s.orderCode')
->leftJoin('result_info ri', 'ri.id=or.error_code')
->where($where)
->order('s.addtime', 'desc')
->cursor();
$userCommon = \app\admin\common\User::getIns();
$list = $customer2_tmp = $company_tmp = [];
foreach ($data as $value) {
if (!isset($customer2_tmp[$value['customer_code']])) {
$temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
$customer2_tmp[$value['customer_code']] = [
'itemid' => $temp['data']['itemid'],
'companyName' => $temp['data']['companyName'],
];
}
$value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
$value['客户名称'] = $customer2_tmp[$value['customer_code']]['companyName'];
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] : '';
}
if ($value['order_type'] == 3 || $value['order_type'] == 4) $value['单位'] = Db::name("good_zixun")->alias('gz')->leftJoin('unit u', 'u.id=gz.good_unit')->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])->value('u.unit');
else $value['单位'] = Db::name('good_basic')->alias('gb')->leftJoin('unit u', 'u.id=gb.good_unit')->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])->value('u.unit');
if (!isset($company_tmp[$value['apply_id']])) $company_tmp[$value['apply_id']] = $value['apply_id'];
// $value['创建人部门'] = get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门
$value['是否完结'] = $value['异常状态'] == 5 ? '已完结' : '未完结';
$value['异常状态'] = isset($this->all_order_return_status[$value['异常状态']]) ? $this->all_order_return_status[$value['异常状态']] : '';
unset($value['itemid']);
unset($value['good_code']);
unset($value['order_type']);
// unset($value['apply_id']);
unset($value['customer_code']);
$list[] = $value;
}
$all_company = get_company_name_by_uid(array_values($company_tmp));
foreach ($list as &$val) {
$val['创建人部门'] = $all_company[$val['apply_id']] ?? '';
unset($val['apply_id']);
}
unset($customer2_tmp);
unset($company_tmp);
if (empty($list)) $list[] = ['没有相关可导出的数据'];
excelSave('售后报表' . date('YmdHis'), array_keys($list[0]), $list);
}
//【一、管理报表】7.产品热销排行
public function goodRankingList()
{
$param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'page' => 1, 'size' => 15], 'post', 'trim');
$where = [['s.is_del', '=', 0]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['s.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
$count = Db::name('sale')
->alias('s')
// ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
->where($where)
->group('s.good_code')
->count('s.id');
$data = Db::name('sale')
->alias('s')
// ->field('s.good_code,s.good_name,SUM(s.good_num) total_num,SUM(s.total_price) total_price,s.cat_id,s.customer_code')
->field('s.good_code,SUM(s.good_num) total_num,SUM(s.total_price) total_price')
// ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
->where($where)
->group('s.good_code')//存疑，此处原本是根据csi.itemid分组的
->page($param['page'], $param['size'])
->order('total_num', 'desc')
->select()
->toArray();
$replenish = Db::name('sale')
->where(['is_del' => 0, 'good_code' => array_unique(array_column($data, 'good_code'))])
->column('good_name,cat_id,customer_code', 'good_code');
$userCommon = \app\admin\common\User::getIns();
$list = $customer2_tmp = [];
foreach ($data as $value) {
$value['good_name'] = $replenish[$value['good_code']]['good_name'] ?? '';
$value['cat_id'] = $replenish[$value['good_code']]['cat_id'] ?? '';
$value['customer_code'] = $replenish[$value['good_code']]['customer_code'] ?? '';
if (!isset($customer2_tmp[$value['customer_code']])) {
$temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
$customer2_tmp[$value['customer_code']] = [
'itemid' => $temp['data']['itemid'],
'companyName' => $temp['data']['companyName'],
];
}
$value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
// $value['客户名称'] = $customer2_tmp[$value['customer_code']]['companyName'];
if (!empty($value['itemid'])) {
$customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
$value['first_customer_org'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
$value['second_customer_org'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
}
$value['cat'] = implode('/', array_column(made($value['cat_id']), 'name'));
$list[] = $value;
}
return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
}
//【一、管理报表】7.产品热销排行_导出
public function goodRankingListExport()
{
$param = $this->request->only(['token', 'start_date' => '', 'end_date' => ''], 'post', 'trim');
$where = [['s.is_del', '=', 0]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['s.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
$data = Db::name('sale')
->alias('s')
->field('s.good_code,"" 产品品类,"" 产品名称,SUM(s.good_num) total_num,"" 数量,SUM(s.total_price) 金额,0 cat_id,"" customer_code,"" 一级企业,"" 二级企业')
->where($where)
->group('s.good_code')//存疑，此处原本是根据csi.itemid分组的
->order('total_num', 'desc')
->cursor();
$replenish = Db::name('sale')
->alias('s')
->where($where)
->column('good_name 产品名称,cat_id,customer_code', 'good_code');
$userCommon = \app\admin\common\User::getIns();
$list = $customer2_tmp = [];
foreach ($data as $value) {
// $tmp=Db::name('sale')
// ->field('good_name 产品名称,cat_id,customer_code')
// ->where(['is_del'=>0,'good_code'=>$value['good_code']])
// ->findOrEmpty();
$value = array_merge($value, $replenish[$value['good_code']]);
if (!isset($customer2_tmp[$value['customer_code']])) {
$temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
$customer2_tmp[$value['customer_code']] = [
'itemid' => $temp['data']['itemid'],
'companyName' => $temp['data']['companyName'],
];
}
$value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
// $value['客户名称'] = $customer2_tmp[$value['customer_code']]['companyName'];
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] : '';
}
$value['产品品类'] = implode('/', array_column(made($value['cat_id']), 'name'));
$value['数量'] = $value['total_num'];
unset($value['good_code']);
unset($value['itemid']);
unset($value['cat_id']);
unset($value['total_num']);
unset($value['customer_code']);
$list[] = $value;
}
if (empty($list)) $list[] = ['没有相关可导出的数据'];
excelSave('产品热销排行' . date('YmdHis'), array_keys($list[0]), $list);
}
//【三、咨询单导出明细】
public function consultDetailed()
{
$param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'use_desc' => '', 'page' => 1, 'size' => 15], 'post', 'trim');
$where = [['ci.is_del', '=', 0]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['ci.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
if ($param['use_desc'] != '') $where[] = ['ci.use_desc', 'like', '%' . $param['use_desc'] . '%'];
$count = Db::name('consult_info')
->alias('ci')
->leftJoin('consult_bids cb', 'cb.infoNo=ci.infoNo AND cb.is_del=0')
->leftJoin('sale s', 's.zxNo=cb.bidNo AND s.is_del=0')
->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
->leftJoin('brand b', 'b.id=ci.brand_id')
->leftJoin('unit u', 'u.id=ci.unit')
->where($where)
->order('ci.addtime', 'desc')
->count('ci.id');
$data = Db::name('consult_info')
->alias('ci')
->field('ci.addtime,co.salesman,"" name,ci.infoNo,"" companyName,ci.use_desc,ci.good_name,b.brand_name,
ci.model,s.good_code,ci.cat_id,ci.budget_price,u.unit,ci.num,s.sale_price,ci.arrival_time,ci.is_addrs,
ci.is_custom,ci.remark,ci.status,ci.bargain_code,s.orderCode,co.khNo,co.saleid')
->leftJoin('consult_bids cb', 'cb.infoNo=ci.infoNo AND cb.is_del=0')
->leftJoin('sale s', 's.zxNo=cb.bidNo AND s.is_del=0')
->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
->leftJoin('brand b', 'b.id=ci.brand_id')
->leftJoin('unit u', 'u.id=ci.unit')
->where($where)
->page($param['page'], $param['size'])
->order('ci.addtime', 'desc')
->cursor();
$userCommon = \app\admin\common\User::getIns();
$list = $customer2_tmp = $tmp=[];
foreach ($data as $value) {
if (!isset($tmp[$value['saleid']])) $tmp[$value['saleid']] = get_company_name_by_uid($value['saleid']);
$value['name']=$tmp[$value['saleid']] ?? '';
if (!isset($customer2_tmp[$value['khNo']])) {
$temp = $userCommon->handle('cInfo', ['companyNo' => $value['khNo']]);
$customer2_tmp[$value['khNo']] = [
'itemid' => $temp['data']['itemid'] ?? 0,
'companyName' => $temp['data']['companyName'] ?? '',
];
}
$value['itemid'] = $customer2_tmp[$value['khNo']]['itemid'];
$value['companyName'] = $customer2_tmp[$value['khNo']]['companyName'];
if (!empty($value['itemid'])) {
$customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
$value['first_customer_org'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
$value['second_customer_org'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
}
$cat = made($value['cat_id']);
$value['first_cat_name'] = isset($cat[0]['name']) ? $cat[0]['name'] : '';
$value['budget_total_price'] = round($value['budget_price'] * $value['num'], 2);
$value['is_addrs'] = isset($this->is_addrs[$value['is_addrs']]) ? $this->is_addrs[$value['is_addrs']] : '';
$value['is_custom'] = isset($this->is_custom[$value['is_custom']]) ? $this->is_custom[$value['is_custom']] : '';
$value['status'] = isset($this->consult_info_status[$value['status']]) ? $this->consult_info_status[$value['status']] : '';
$list[] = $value;
}
return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
}
//【三、咨询单导出明细】_导出
public function consultDetailedExport()
{
$param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'use_desc' => ''], 'post', 'trim');
$where = [['ci.is_del', '=', 0]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['ci.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
if ($param['use_desc'] != '') $where[] = ['ci.use_desc', 'like', '%' . $param['use_desc'] . '%'];
$data = Db::name('consult_info')
->alias('ci')
->field('ci.addtime 咨询时间,co.salesman 业务员,"" 业务部门,cb.infoNo 咨询单号,"" 一级组织,"" 二级组织,"" 客户名称,ci.use_desc 产品用途,ci.good_name 产品名称,b.brand_name 品牌,ci.model 型号,s.good_code 产品编码,"" 一级分类,ci.cat_id,ci.budget_price 预算单价,u.unit 单位,ci.num 需求数量,"" 预算总价,s.sale_price 单价,ci.arrival_time 要求到货时间,ci.is_addrs 是否一地,ci.is_custom 定制,ci.remark 咨询备注,ci.status 竞单状态,ci.bargain_code 上次咨询单单号,s.orderCode 销售订单编号,co.khNo,co.saleid')
->leftJoin('consult_bids cb', 'cb.infoNo=ci.infoNo AND cb.is_del=0')
->leftJoin('sale s', 's.zxNo=cb.bidNo AND s.is_del=0')
->leftJoin('consult_order co', 'co.zxNo=cb.zxNo')
// ->leftJoin('depart_user du', 'du.uid=co.saleid AND du.is_del=0')
// ->leftJoin('company_item item', 'item.id=du.itemid')
// ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
->leftJoin('brand b', 'b.id=ci.brand_id')
->leftJoin('unit u', 'u.id=ci.unit')
->where($where)
->order('ci.addtime', 'desc')
->cursor();
$userCommon = \app\admin\common\User::getIns();
$list = $customer2_tmp = [];
foreach ($data as $value) {
if (!isset($tmp[$value['saleid']])) $tmp[$value['saleid']] = get_company_name_by_uid($value['saleid']);
$value['业务部门']=$tmp[$value['saleid']] ?? '';
if (!isset($customer2_tmp[$value['khNo']])) {
$temp = $userCommon->handle('cInfo', ['companyNo' => $value['khNo']]);
$customer2_tmp[$value['khNo']] = [
'itemid' => $temp['data']['itemid'] ?? 0,
'companyName' => $temp['data']['companyName'] ?? '',
];
}
$value['itemid'] = $customer2_tmp[$value['khNo']]['itemid'];
$value['客户名称'] = $customer2_tmp[$value['khNo']]['companyName'];
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['预算总价'] = round($value['预算单价'] * $value['需求数量'], 2);
$value['是否一地'] = isset($this->is_addrs[$value['是否一地']]) ? $this->is_addrs[$value['是否一地']] : '';
$value['定制'] = isset($this->is_custom[$value['定制']]) ? $this->is_custom[$value['定制']] : '';
$value['竞单状态'] = isset($this->consult_info_status[$value['竞单状态']]) ? $this->consult_info_status[$value['竞单状态']] : '';
unset($value['itemid']);
unset($value['cat_id']);
unset($value['khNo']);
unset($value['saleid']);
$list[] = $value;
}
if (empty($list)) $list[] = ['没有相关可导出的数据'];
excelSave('咨询单导出明细' . date('YmdHis'), array_keys($list[0]), $list);
}
//【四、工差明细表】
public function saleDiff()
{
$param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'page' => 1, 'size' => 15], 'post', 'trim');
$where = [['pd.is_del', '=', 0]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['pd.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
$count = Db::name('purchease_diff')
->alias('pd')
->leftJoin('sale_diff sd', 'sd.cgd_diffid=pd.id')
->leftJoin('purchease_order po', 'po.cgdNo=pd.cgdNo')
->leftJoin('order_num on', 'on.cgdNo=pd.cgdNo')
->leftJoin('sale s', 's.orderCode=on.orderCode')
->where($where)
->order('pd.addtime', 'desc')
->count('pd.id');
$data = Db::name('purchease_diff')
->alias('pd')
->field('pd.addtime,sd.diffCode,sd.orderCode,pd.apply_name,pd.apply_id,po.addtime poaddtime,pd.good_name,"" companyName,pd.good_num,pd.sale_price,pd.good_weight,pd.diff_weight,pd.diff_price,sd.status,po.order_type,po.spuCode,s.apply_name sale_apply_name,s.apply_id sale_apply_id,s.customer_code')
->leftJoin('sale_diff sd', 'sd.cgd_diffid=pd.id')
->leftJoin('purchease_order po', 'po.cgdNo=pd.cgdNo')
->leftJoin('order_num on', 'on.cgdNo=pd.cgdNo')
->leftJoin('sale s', 's.orderCode=on.orderCode')
->where($where)
->page($param['page'], $param['size'])
->order('pd.addtime', 'desc')
->cursor();
$list = $codes = $uid_tmp = [];
foreach ($data as $value) {
if (!isset($codes[$value['customer_code']])) $codes[$value['customer_code']] = $value['customer_code'];
if ($value['order_type'] == 3 || $value['order_type'] == 4) $noble_metal = Db::name("good_zixun")->where(["spuCode" => $value['spuCode'], "is_del" => 0])->value('noble_metal');
else $noble_metal = Db::name('good_basic')->where(['spuCode' => $value['spuCode'], 'is_del' => 0])->value('noble_metal');
if (!isset($uid_tmp[$value['apply_id']])) $uid_tmp[$value['apply_id']] = $value['apply_id'];
if (!isset($uid_tmp[$value['sale_apply_id']])) $uid_tmp[$value['sale_apply_id']] = $value['sale_apply_id'];
// $value['apply_company'] = $uid_tmp[$value['apply_id']];
// $value['sale_apply_company'] = $uid_tmp[$value['sale_apply_id']];
$value['noble_metal'] = isset($this->noble_metal[$noble_metal]) ? $this->noble_metal[$noble_metal] : '';
$value['total_price'] = round($value['sale_price'], $value['good_num'], 2);
$value['is_update_sale'] = isset($this->sale_diff_status[$value['status']]) ? $this->sale_diff_status[$value['status']] : '';
$list[] = $value;
}
$userCommon = \app\admin\common\User::getIns();
$names = $userCommon->handle('getCodeAndName', ['code' => array_values($codes)]);
$all_company = get_company_name_by_uid(array_values($uid_tmp));
foreach ($list as &$val) {
$val['companyName'] = $names['data'][$val['customer_code']] ?? '';
$val['apply_company'] = $all_company[$val['apply_id']] ?? '';
$val['sale_apply_company'] = $all_company[$val['sale_apply_id']] ?? '';
}
unset($codes);
unset($uid_tmp);
unset($all_company);
return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
}
//【四、工差明细表】_导出
public function saleDiffExport()
{
$param = $this->request->only(['token', 'start_date' => '', 'end_date' => ''], 'post', 'trim');
$where = [['pd.is_del', '=', 0]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['pd.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
$data = Db::name('purchease_diff')
->alias('pd')
->field('pd.addtime 发起时间,sd.diffCode 工差单号,sd.orderCode 关联确认单,pd.apply_name 创建人,"" 部门,pd.apply_id,po.addtime 确认单下单日期,pd.good_name 产品名称,"" 客户名称,"" 贵金属种类,pd.good_num 下单数量,pd.sale_price 单价,"" 总额,pd.good_weight 商品总重量,pd.diff_weight 工差重量,pd.diff_price 销售工差总费用,sd.status 是否修改销售额,s.apply_name 销售人员,s.apply_id sale_apply_id,"" 销售部门,po.order_type,po.spuCode,s.customer_code')
->leftJoin('sale_diff sd', 'sd.cgd_diffid=pd.id')
->leftJoin('purchease_order po', 'po.cgdNo=pd.cgdNo')
->leftJoin('order_num on', 'on.cgdNo=pd.cgdNo')
->leftJoin('sale s', 's.orderCode=on.orderCode')
// ->leftJoin('customer_info ci', 'ci.companyNo=s.customer_code')
->where($where)
->order('pd.addtime', 'desc')
->cursor();
$list = $codes = $uid_tmp = [];
foreach ($data as $value) {
if (!isset($codes[$value['customer_code']])) $codes[$value['customer_code']] = $value['customer_code'];
if ($value['order_type'] == 3 || $value['order_type'] == 4) $noble_metal = Db::name("good_zixun")->where(["spuCode" => $value['spuCode'], "is_del" => 0])->value('noble_metal');
else $noble_metal = Db::name('good_basic')->where(['spuCode' => $value['spuCode'], 'is_del' => 0])->value('noble_metal');
if (!isset($uid_tmp[$value['apply_id']])) $uid_tmp[$value['apply_id']] = $value['apply_id'];
if (!isset($uid_tmp[$value['sale_apply_id']])) $uid_tmp[$value['sale_apply_id']] = $value['sale_apply_id'];
// $value['部门'] = $uid_tmp[$value['apply_id']];
// $value['销售部门'] = $uid_tmp[$value['sale_apply_id']];
$value['贵金属种类'] = isset($this->noble_metal[$noble_metal]) ? $this->noble_metal[$noble_metal] : '';
$value['总额'] = round($value['单价'], $value['下单数量'], 2);
$value['是否修改销售额'] = isset($this->sale_diff_status[$value['是否修改销售额']]) ? $this->sale_diff_status[$value['是否修改销售额']] : '';
// unset($value['apply_id']);
unset($value['order_type']);
unset($value['spuCode']);
// unset($value['sale_apply_id']);
$list[] = $value;
}
$userCommon = \app\admin\common\User::getIns();
$names = $userCommon->handle('getCodeAndName', ['code' => array_values($codes)]);
$all_company = get_company_name_by_uid(array_values($uid_tmp));
foreach ($list as &$val) {
$val['客户名称'] = $names['data'][$val['customer_code']] ?? '';
$val['部门'] = $all_company[$val['apply_id']] ?? '';
$val['销售部门'] = $all_company[$val['sale_apply_id']] ?? '';
unset($val['customer_code']);
unset($val['apply_id']);
unset($val['sale_apply_id']);
}
unset($codes);
unset($uid_tmp);
unset($all_company);
unset($names);
if (empty($list)) $list[] = ['没有相关可导出的数据'];
excelSave('工差明细表' . date('YmdHis'), array_keys($list[0]), $list);
}
//【五、退货明细表】
public function thData()
{
$param = $this->request->only(['token', 'start_date' => '',"companyNo"=>"", 'end_date' => '', 'page' => 1, 'size'
=> 15], 'post', 'trim');
$where = [['thd.is_del', '=', 0]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['thd.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
if($param['companyNo'] != '') $where[] = ["s.supplierNo","=",$param['companyNo']];
$count = Db::name('th_data')
->alias('thd')
->leftJoin('sale s', 's.orderCode=thd.orderCode')
->where($where)
->order('thd.addtime', 'desc')
->count('thd.id');
$data = Db::name('th_data')
->alias('thd')
->field('thd.id,thd.th_type,
thd.addtime return_time,thd.thCode,"" outCode,"" status,s.apply_name,s.apply_id apply_company,thd.orderCode,s.addtime,s.customerName companyName,s.good_name,s.sale_price,s.wsend_num,thd.th_num,thd.th_fee,"" contactor,"" mobile,"" addr,"" result')
->leftJoin('sale s', 's.orderCode=thd.orderCode')
->where($where)
->page($param['page'],$param['size'])
->order('thd.addtime', 'desc')
->select()
->toArray();
$th_types = ["1"=>[],"2"=>[],"3"=>[],"4"=>[]];
foreach ($data as $value) {
$th_types[$value['th_type']][] = $value['thCode'];
}
$all_order_return = Db::name('order_return')
->alias('or')
->withAttr('status', function ($val) {
return isset($this->all_order_return_status[$val]) ? $this->all_order_return_status[$val] : '';
})
->leftJoin('order_out oo', 'oo.outCode=or.outCode')
->leftJoin('order_addr oa', 'oa.id=oo.addrid')
->leftJoin('result_info ri', 'ri.result_code=or.error_code')
->where('or.is_del', 0)
->whereIn('or.returnCode', $th_types[2])
->column('or.id,or.outCode,or.status,or.apply_id,or.apply_name,oa.contactor,oa.mobile,oa.addr,ri.result,oa.addr_code', 'or.returnCode');
$all_order_back = Db::name('order_back')
->alias('ob')
->where('ob.is_del', 0)
->whereIn('ob.thNo', $th_types[3])
->withAttr('status', function ($val) {
return isset($this->order_back_status[$val]) ? $this->order_back_status[$val] : '';
})
->leftJoin('order_out oo', 'oo.outCode=ob.outCode')
->leftJoin('order_addr oa', 'oa.id=oo.addrid')
->leftJoin('result_info ri', 'ri.result_code=ob.return_code')
->column('ob.id,ob.outCode,ob.status,ob.apply_id,ob.apply_name,oa.contactor,oa.mobile,oa.addr,ri.result,oa.addr_code', 'thNo');
$list = [];
$all_uids = array_unique(array_merge(array_column($all_order_return, 'apply_id'), array_column
($all_order_back, 'apply_id'), array_column($data, 'apply_company')));
$all_company = get_company_name_by_uid($all_uids);
// $userCommon = \app\admin\common\User::getIns();
// $names = $userCommon->handle('getCodeAndName', ['code' => array_unique(array_column($data, 'customer_code'))]);
foreach ($data as $value) {
if ($value['th_type'] == 2) {
$value['outCode'] = isset($all_order_return[$value['thCode']]['outCode']) ? $all_order_return[$value['thCode']]['outCode'] : '';
$value['status'] = isset($all_order_return[$value['thCode']]['status']) ? $all_order_return[$value['thCode']]['status'] : '';
$value['apply_name'] = isset($all_order_return[$value['thCode']]['apply_name']) ? $all_order_return[$value['thCode']]['apply_name'] : '';
$value['apply_company'] = $all_company[$all_order_return[$value['thCode']]['apply_id'] ?? 0] ?? '';//isset($all_order_return[$value['thCode']]['apply_company']) ? $all_order_return[$value['thCode']]['apply_company'] : '';
$value['contactor'] = isset($all_order_return[$value['thCode']]['contactor']) ? $all_order_return[$value['thCode']]['contactor'] : '';
$value['mobile'] = isset($all_order_return[$value['thCode']]['mobile']) ? $all_order_return[$value['thCode']]['mobile'] : '';
$value['addr'] = isset($all_order_return[$value['thCode']]['addr']) ? $all_order_return[$value['thCode']]['addr'] : '';
$value['result'] = isset($all_order_return[$value['thCode']]['result']) ? $all_order_return[$value['thCode']]['result'] : '';
} elseif ($value['th_type'] == 3) {
$value['outCode'] = isset($all_order_back[$value['thCode']]['outCode']) ? $all_order_back[$value['thCode']]['outCode'] : '';
$value['status'] = isset($all_order_back[$value['thCode']]['status']) ? $all_order_back[$value['thCode']]['status'] : '';
$value['apply_name'] = isset($all_order_back[$value['thCode']]['apply_name']) ? $all_order_back[$value['thCode']]['apply_name'] : '';
$value['apply_company'] = $all_company[$all_order_back[$value['thCode']]['apply_id'] ?? 0] ?? '';//isset($all_order_back[$value['thCode']]['apply_company']) ? $all_order_back[$value['thCode']]['apply_company'] : '';
$value['contactor'] = isset($all_order_back[$value['thCode']]['contactor']) ? $all_order_back[$value['thCode']]['contactor'] : '';
$value['mobile'] = isset($all_order_back[$value['thCode']]['mobile']) ? $all_order_back[$value['thCode']]['mobile'] : '';
$value['addr'] = isset($all_order_back[$value['thCode']]['addr']) ? $all_order_back[$value['thCode']]['addr'] : '';
$value['result'] = isset($all_order_back[$value['thCode']]['result']) ? $all_order_back[$value['thCode']]['result'] : '';
}else{
$value['apply_company'] = $all_company[$value['apply_company'] ?? 0] ?? '';
$value['status'] = $value['th_type']==1?"销售退货完成":"取消订单完成";
$value['result'] = $value['th_type']==1?"销售退货":"取消订单";
}
$list[] = $value;
}
unset($names);
unset($all_uids);
unset($all_company);
return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
}
//【五、退货明细表】
public function thDataExport()
{
$param = $this->request->only(['token', 'start_date' => '','companyNo'=>"", 'end_date' => ''], 'post', 'trim');
$where = [['thd.is_del', '=', 0]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['thd.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
if ($param['companyNo'] != '')
$where[]=[
's.supplierNo' ,
'=' ,
$param['companyNo']
];
$data = Db::name('th_data')
->alias('thd')
->field('thd.id,thd.th_type,thd.addtime 退货时间,thd.thCode 退货单号,"" 发货申请单编码,"" 退货单状态,
s.apply_name 创建人,s.apply_id 部门,thd.orderCode 确认单编号,s.addtime 下单日期,s.customerName 客户名称,
s.good_name 产品名称,s.good_code 商品成本编号,s.is_stock 商品类型,s.order_source 订单来源,s.sale_price 单价,
s.supplierName 业务公司,s.good_num 下单数量,s.wsend_num 未发货数量,thd.th_num 退货数量,thd.th_fee 退货销售货款,
"" 发货单联系人,"" 发货单联系人电话,"" 发货单地址,"" 退货原因')
->leftJoin('sale s', 's.orderCode=thd.orderCode')
->where($where)
->order('thd.addtime', 'desc')
->select()
->toArray();
$th_types = ["1"=>[],"2"=>[],"3"=>[],"4"=>[]];
foreach ($data as $value) {
$th_types[$value['th_type']][] = $value['退货单号'];
}
$all_order_return = Db::name('order_return')
->alias('or')
->withAttr('退货单状态', function ($val) {
return isset($this->all_order_return_status[$val]) ? $this->all_order_return_status[$val] : '';
})
->leftJoin('order_out oo', 'oo.outCode=or.outCode')
->leftJoin('order_addr oa', 'oa.id=oo.addrid')
->leftJoin('result_info ri', 'ri.result_code=or.error_code')
->where('or.is_del', 0)
->whereIn('or.returnCode', $th_types[2])
->column('or.id,or.outCode 发货申请单编码,or.status 退货单状态,or.apply_id,or.apply_name 创建人,"" 部门,oa.contactor 发货单联系人,oa.mobile 发货单联系人电话,oa.addr 发货单地址,ri.result 退货原因,oa.addr_code', 'or.returnCode');
$all_order_back = Db::name('order_back')
->alias('ob')
->where('ob.is_del', 0)
->whereIn('ob.thNo', $th_types[3])
->withAttr('退货单状态', function ($val) {
return isset($this->order_back_status[$val]) ? $this->order_back_status[$val] : '';
})
->leftJoin('order_out oo', 'oo.outCode=ob.outCode')
->leftJoin('order_addr oa', 'oa.id=oo.addrid')
->leftJoin('result_info ri', 'ri.result_code=ob.return_code')
->column('ob.id,ob.outCode 发货申请单编码,ob.status 退货单状态,ob.apply_id,ob.apply_name 创建人,"" 部门,oa.contactor 发货单联系人,oa.mobile 发货单联系人电话,oa.addr 发货单地址,ri.result 退货原因,oa.addr_code', 'thNo');
$list = [];
// $userCommon = \app\admin\common\User::getIns();
// $names = $userCommon->handle('getCodeAndName', ['code' => array_column($data, 'customer_code')]);
$all_uids = array_unique(array_merge(array_column($all_order_return, 'apply_id'), array_column
($all_order_back, 'apply_id'), array_column($data, '部门')));
$all_company = get_company_name_by_uid($all_uids);
foreach ($data as $value) {
if ($value['th_type'] == 2) {
$value['发货申请单编码'] = isset($all_order_return[$value['退货单号']]['发货申请单编码']) ? $all_order_return[$value['退货单号']]['发货申请单编码'] : '';
$value['退货单状态'] = isset($all_order_return[$value['退货单号']]['退货单状态']) ? $all_order_return[$value['退货单号']]['退货单状态'] : '';
$value['创建人'] = isset($all_order_return[$value['退货单号']]['创建人']) ? $all_order_return[$value['退货单号']]['创建人'] : '';
$value['部门'] = $all_company[$all_order_return[$value['退货单号']]['apply_id'] ?? 0] ?? '';//isset($all_order_return[$value['退货单号']]['部门']) ? $all_order_return[$value['退货单号']]['部门'] : '';
$value['发货单联系人'] = isset($all_order_return[$value['退货单号']]['发货单联系人']) ? $all_order_return[$value['退货单号']]['发货单联系人'] : '';
$value['发货单联系人电话'] = isset($all_order_return[$value['退货单号']]['发货单联系人电话']) ? $all_order_return[$value['退货单号']]['发货单联系人电话'] : '';
$value['发货单地址'] = isset($all_order_return[$value['退货单号']]['发货单地址']) ? $all_order_return[$value['退货单号']]['发货单地址'] : '';
$value['退货原因'] = isset($all_order_return[$value['退货单号']]['退货原因']) ? $all_order_return[$value['退货单号']]['退货原因'] : '';
} elseif ($value['th_type'] == 3) {
$value['发货申请单编码'] = isset($all_order_back[$value['退货单号']]['发货申请单编码']) ? $all_order_back[$value['退货单号']]['发货申请单编码'] : '';
$value['退货单状态'] = isset($all_order_back[$value['退货单号']]['退货单状态']) ? $all_order_back[$value['退货单号']]['退货单状态'] : '';
$value['创建人'] = isset($all_order_back[$value['退货单号']]['创建人']) ? $all_order_back[$value['退货单号']]['创建人'] : '';
$value['部门'] = $all_company[$all_order_back[$value['退货单号']]['apply_id'] ?? 0] ?? '';//isset($all_order_back[$value['退货单号']]['部门']) ? $all_order_back[$value['退货单号']]['部门'] : '';
$value['发货单联系人'] = isset($all_order_back[$value['退货单号']]['发货单联系人']) ? $all_order_back[$value['退货单号']]['发货单联系人'] : '';
$value['发货单联系人电话'] = isset($all_order_back[$value['退货单号']]['发货单联系人电话']) ? $all_order_back[$value['退货单号']]['发货单联系人电话'] : '';
$value['发货单地址'] = isset($all_order_back[$value['退货单号']]['发货单地址']) ? $all_order_back[$value['退货单号']]['发货单地址'] : '';
$value['退货原因'] = isset($all_order_back[$value['退货单号']]['退货原因']) ? $all_order_back[$value['退货单号']]['退货原因'] : '';
}else{
$value['部门'] = $all_company[$value['部门'] ?? 0] ?? '';
$value['退货单状态'] = $value['th_type']==1?"销售退货完成":"取消订单完成";
$value['退货原因'] = $value['th_type']==1?"销售退货":"取消订单";
}
$value['商品类型'] = $value['商品类型']==1?'库存':'非库存';
$value['订单来源'] = \app\admin\model\Sale::$order_source[$value['订单来源']];
// $value['客户名称'] = $names['data'][$value['customer_code']];
unset($value['id']);
unset($value['th_type']);
unset($value['customer_code']);
$list[] = $value;
}
unset($all_uids);
unset($all_company);
unset($names);
if (empty($list)) $list[] = ['没有相关可导出的数据'];
excelSave('退货明细表' . date('YmdHis'), array_keys($list[0]), $list);
}
//【六、售后明细表】
public function orderReturnDetailed()
{
$param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'status' => '', 'page' => 1, 'size' => 15], 'post', 'trim');
$where = [['or.is_del', '=', 0]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['or.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
if ($param['status'] != '') $where[] = ['or.status', '=', $param['status']];
$count = Db::name('order_return')
->alias('or')
// ->leftJoin('customer_info c', 'c.companyNo=or.customer_code')
->leftJoin('sale s', 's.orderCode=or.orderCode')
->leftJoin('result_info ri', 'ri.result_code=or.error_code')
->where($where)
->order('or.addtime', 'desc')
->count('or.id');
$data = Db::name('order_return')
->alias('or')
->field('or.addtime,or.returnCode,or.apply_name,or.apply_id,or.outCode,or.total_num,or.status,or.orderCode,"" companyName,or.good_name,s.sale_price,or.is_receive,or.error_num,ri.result result_desc,or.error_remark,or.except_code,or.customer_code')
// ->leftJoin('customer_info c', 'c.companyNo=or.customer_code')
->leftJoin('sale s', 's.orderCode=or.orderCode')
->leftJoin('result_info ri', 'ri.result_code=or.error_code')
->where($where)
->page($param['page'], $param['size'])
->order('or.addtime', 'desc')
->cursor();
$userCommon = \app\admin\common\User::getIns();
$list = $customer2_tmp = $uid_tmp = [];
foreach ($data as $value) {
$value['status'] = isset($this->all_order_return_status[$value['status']]) ? $this->all_order_return_status[$value['status']] : '';
$value['except_code'] = isset($this->except_code[$value['is_receive']][$value['except_code']]) ? $this->except_code[$value['is_receive']][$value['except_code']] : '';
$value['is_receive'] = isset($this->is_receive[$value['is_receive']]) ? $this->is_receive[$value['is_receive']] : '';
if (!isset($uid_tmp[$value['apply_id']])) $uid_tmp[$value['apply_id']] = $value['apply_id'];;//get_company_name_by_uid($value['apply_id']);
// $value['apply_company'] = $uid_tmp[$value['apply_id']];
$value['error_total_price'] = round($value['error_num'] * $value['sale_price'], 2);
if (!isset($customer2_tmp[$value['customer_code']])) {
$temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
$customer2_tmp[$value['customer_code']] = [
'itemid' => $temp['data']['itemid'],
'companyName' => $temp['data']['companyName'],
];
}
$value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
$value['companyName'] = $customer2_tmp[$value['customer_code']]['companyName'];
if (!empty($value['itemid'])) {
$customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
$value['first_customer_org'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
$value['second_customer_org'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
}
$list[] = $value;
}
$all_company = get_company_name_by_uid(array_values($uid_tmp));
foreach ($list as &$val) {
$val['apply_company'] = $all_company[$val['apply_id']] ?? '';
// unset($val['apply_id']);
}
unset($customer2_tmp);
unset($uid_tmp);
unset($all_company);
return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
}
//【六、售后明细表】
public function orderReturnDetailedExport()
{
$param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'status' => ''], 'post', 'trim');
$where = [['or.is_del', '=', 0]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['or.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
if ($param['status'] != '') $where[] = ['or.status', '=', $param['status']];
$data = Db::name('order_return')
->alias('or')
->field('or.addtime 售后时间,or.returnCode 售后申请编号,or.apply_name 申请人,or.apply_id,"" 部门,or.outCode 发货申请编号,or.total_num 发货数量,or.status 处理状态,or.orderCode 订单号,"" 一级组织,"" 二级组织,"" 客户名称,or.good_name 商品名称,s.sale_price 销售单价,"" 异常金额,or.is_receive 收货情况,or.error_num 异常数量,ri.result 异常原因,or.error_remark 异常备注,or.except_code 期望意愿,or.customer_code')
// ->leftJoin('customer_info c', 'c.companyNo=or.customer_code')
->leftJoin('sale s', 's.orderCode=or.orderCode')
->leftJoin('result_info ri', 'ri.result_code=or.error_code')
->where($where)
->order('or.addtime', 'desc')
->cursor();
$userCommon = \app\admin\common\User::getIns();
$list = $customer2_tmp = $uid_tmp = [];
foreach ($data as $value) {
$value['处理状态'] = isset($this->all_order_return_status[$value['处理状态']]) ? $this->all_order_return_status[$value['处理状态']] : '';
if (!isset($uid_tmp[$value['apply_id']])) $uid_tmp[$value['apply_id']] = $value['apply_id'];//get_company_name_by_uid($value['apply_id']);
// $value['部门'] = $uid_tmp[$value['apply_id']];
$value['异常金额'] = round($value['异常数量'] * $value['销售单价'], 2);
if (!isset($customer2_tmp[$value['customer_code']])) {
$temp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
$customer2_tmp[$value['customer_code']] = [
'itemid' => $temp['data']['itemid'],
'companyName' => $temp['data']['companyName'],
];
}
$value['itemid'] = $customer2_tmp[$value['customer_code']]['itemid'];
$value['客户名称'] = $customer2_tmp[$value['customer_code']]['companyName'];
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] : '';
}
$value['期望意愿'] = isset($this->except_code[$value['收货情况']][$value['期望意愿']]) ? $this->except_code[$value['收货情况']][$value['期望意愿']] : '';
$value['收货情况'] = isset($this->is_receive[$value['收货情况']]) ? $this->is_receive[$value['收货情况']] : '';
// unset($value['apply_id']);
unset($value['itemid']);
unset($value['customer_code']);
$list[] = $value;
}
$all_company = get_company_name_by_uid(array_values($uid_tmp));
foreach ($list as &$val) {
$val['部门'] = $all_company[$val['apply_id']] ?? '';
unset($val['apply_id']);
}
unset($customer2_tmp);
unset($uid_tmp);
unset($all_company);
if (empty($list)) $list[] = ['没有相关可导出的数据'];
excelSave('售后明细表' . date('YmdHis'), array_keys($list[0]), $list);
}
//【七、议价明细表】
public function bargainOrder()
{
$param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'status' => '', 'page' => 1, 'size' => 15], 'post', 'trim');
$where = [['bo.is_del', '=', 0]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['bo.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
$count = Db::name('bargain_order')
->alias('bo')
->leftJoin('consult_info ci', 'ci.infoNo=bo.infoNo')
->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
// ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
->leftJoin('result_info ri', 'ri.result_code=bo.result_info_id')
->where($where)
->order('bo.addtime', 'desc')
->count('bo.id');
$data = Db::name('bargain_order')
->alias('bo')
->field('bo.addtime,bo.bargainNo,bo.infoNo,bo.status,bo.bidsNo,"" companyName,ci.good_name,bo.creater,bo.createrid,bo.sale_price,bo.after_price,ri.result result_desc,bo.bargain_remark,co.khNo')
->leftJoin('consult_info ci', 'ci.infoNo=bo.infoNo')
->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
// ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
->leftJoin('result_info ri', 'ri.result_code=bo.result_info_id')
->where($where)
->page($param['page'], $param['size'])
->order('bo.addtime', 'desc')
->cursor();
$list = $codes = $uid_tmp = [];
foreach ($data as $value) {
$value['status'] = isset($this->bargain_order_status[$value['status']]) ? $this->bargain_order_status[$value['status']] : '';
if (!isset($uid_tmp[$value['createrid']])) $uid_tmp[$value['createrid']] = $value['createrid'];//get_company_name_by_uid($value['createrid']);
// $value['creater_company'] = $uid_tmp[$value['createrid']];
if (!isset($codes[$value['khNo']])) $codes[$value['khNo']] = $value['khNo'];
$list[] = $value;
}
//补充客户名称
$userCommon = \app\admin\common\User::getIns();
$names = $userCommon->handle('getCodeAndName', ['code' => $codes]);
$all_company = get_company_name_by_uid(array_values($uid_tmp));
foreach ($list as &$val) {
$val['companyName'] = $names['data'][$val['khNo']];
$val['creater_company'] = $all_company[$val['createrid']] ?? '';
}
unset($codes);
unset($uid_tmp);
unset($userCommon);
unset($names);
unset($all_company);
return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
}
//【七、议价明细表】
public function bargainOrderExport()
{
$param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'status' => ''], 'post', 'trim');
$where = [['bo.is_del', '=', 0]];
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['bo.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
$data = Db::name('bargain_order')
->alias('bo')
->field('bo.addtime 议价时间,bo.bargainNo 议价编号,bo.infoNo 咨询单编号,bo.status 议价单状态,bo.bidsNo 采返商品编号,"" 客户名称,ci.good_name 商品名称,bo.creater 申请人,"" 部门,bo.createrid,bo.sale_price 议价前售价,bo.after_price 期望售价,ri.result 议价原因,bo.bargain_remark 议价备注,co.khNo')
->leftJoin('consult_info ci', 'ci.infoNo=bo.infoNo')
->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
// ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
->leftJoin('result_info ri', 'ri.result_code=bo.result_info_id')
->where($where)
->order('bo.addtime', 'desc')
->cursor();
$list = $codes = $uid_tmp = [];
foreach ($data as $value) {
if (!isset($codes[$value['khNo']])) $codes[$value['khNo']] = $value['khNo'];
if (!isset($uid_tmp[$value['createrid']])) $uid_tmp[$value['createrid']] = $value['createrid'];//get_company_name_by_uid($value['createrid']);
$value['议价单状态'] = isset($this->bargain_order_status[$value['议价单状态']]) ? $this->bargain_order_status[$value['议价单状态']] : '';
// $value['部门'] = $uid_tmp[$value['createrid']];
// unset($value['createrid']);
$list[] = $value;
}
//补充客户名称
$userCommon = \app\admin\common\User::getIns();
$names = $userCommon->handle('getCodeAndName', ['code' => $codes]);
$all_company = get_company_name_by_uid(array_values($uid_tmp));
foreach ($list as &$val) {
$val['客户名称'] = $names['data'][$val['khNo']];
$val['部门'] = $all_company[$val['createrid']] ?? '';
unset($val['createrid']);
unset($val['khNo']);
}
unset($codes);
unset($uid_tmp);
unset($userCommon);
unset($names);
unset($all_company);
if (empty($list)) $list[] = ['没有相关可导出的数据'];
excelSave('议价明细表' . date('YmdHis'), array_keys($list[0]), $list);
}
//【八、延时发货申请单统计】
public function orderOutNotAddr()
{
$param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'start_sendtime' => '', 'end_sendtime' => '', 'status' => '', 'apply_company' => '', 'page' => 1, 'size' => 15, 'order_type' => ''], 'post', 'trim');
$where = [['a.is_del', '=', 0], ['b.send_type', '=', 2], ['a.send_num', '>', 0], ['a.status', '<>', 4]];//send_type==2 延迟发货,a.status==4 已全部退货
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['a.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
if ($param['start_sendtime'] != '' && $param['end_sendtime'] != '') $where[] = ['a.sendtime', 'between', [$param['start_sendtime'] . ' 00:00:00', $param['end_sendtime'] . ' 23:59:59']];
if ($param['status'] != '') $where[] = ['a.status', '=', $param['status']];
// if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
if ($param['order_type'] != '') $where[] = ['b.order_type', '=', $param['order_type']];
$count = Db::name('order_out')
->alias('a')
->leftJoin("sale b", "b.orderCode=a.orderCode")
->leftJoin("order_send os", "os.outCode=a.outCode")
->leftJoin("purchease_order d", "d.cgdNo=os.cgdNo")
->order("a.addtime desc")
->where($where)
->count('a.id');
$data = Db::name('order_out')
->alias('a')
->field('a.addtime,a.orderCode,os.cgdNo,"" company,a.apply_name,a.apply_id,a.status,a.send_num,b.good_code,b.good_name,"" companyName,b.wsend_num,b.skuCode,b.order_type,""person,b.customer_code,b.supplierNo companyNo,b.supNo as supplierNo')
->leftJoin("sale b", "b.orderCode=a.orderCode")
->leftJoin("order_send os", "os.outCode=a.outCode")
->leftJoin("purchease_order d", "d.cgdNo=os.cgdNo")
->page($param['page'], $param['size'])
->where($where)
->order("a.addtime desc")
->cursor();
$list = $tmp_apply = $tmp_cat = $tmp_person = $tmp_codes = [];
$userCommon = \app\admin\common\User::getIns();
foreach ($data as $value) {
if (!isset($tmp_apply[$value['apply_id']])) $tmp_apply[$value['apply_id']] = $value['apply_id'];
if (!isset($tmp_codes[$value['customer_code']])) $tmp_codes[$value['customer_code']] = $value['customer_code'];
if (!isset($tmp_codes[$value['companyNo']])) $tmp_codes[$value['companyNo']] = $value['companyNo'];
if (!isset($tmp_person[$value['supplierNo']])) {
$tmp = $userCommon->handle('sInfo', ['code' => $value['supplierNo']]);
$tmp_person[$value['supplierNo']] = $tmp['data']['person']??"";
}
$value['person'] = $tmp_person[$value['supplierNo']];
if ($value['order_type'] == 3 || $value['order_type'] == 4) {
$goon = Db::name("good_zixun")
->field('id,cat_id,customized')
->where(["spuCode" => $value['good_code'], "is_del" => 0])
->find();
} else {
$goon = Db::name('good_platform')
->alias('a')
->field('a.id,b.cat_id,b.customized')
->join('good b', 'b.spuCode=a.spuCode', 'left')
->where(['a.is_del' => 0, 'a.skuCode' => $value['skuCode']])
->find();
}
if (!isset($tmp_cat[$goon['cat_id']])) $tmp_cat[$goon['cat_id']] = implode('/', array_column(made($goon['cat_id']), 'name'));
$value['cat'] = $tmp_cat[$goon['cat_id']];
$value['send_date'] = date('Y-m-d H:i:s', strtotime($value['addtime']) + $goon['customized'] * 24 * 3600);
$value['status'] = isset($this->order_out_status[$value['status']]) ? $this->order_out_status[$value['status']] : '';
$list[] = $value;
}
$names = $userCommon->handle('getCodeAndName', ['code' => $tmp_codes]);
$apply_company = get_company_name_by_uid($tmp_apply);
foreach ($list as &$val) {
$val['companyName'] = $names['data'][$val['customer_code']] ?? '';
$val['company'] = $names['data'][$val['companyNo']] ?? '';
$val['apply_company'] = $apply_company[$val['apply_id']] ?? '';
}
unset($tmp_apply);
unset($tmp_cat);
unset($tmp_person);
unset($tmp_codes);
return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
}
//【八、延时发货申请单统计】
public function orderOutNotAddrExport()
{
$param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'start_sendtime' => '', 'end_sendtime' => '', 'status' => '', 'apply_company' => '', 'page' => 1, 'size' => 15, 'order_type' => ''], 'post', 'trim');
$where = [['a.is_del', '=', 0], ['b.send_type', '=', 2], ['a.send_num', '>', 0], ['a.status', '<>', 4]];//send_type==2 延迟发货,a.status==4 已全部退货
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['a.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
if ($param['start_sendtime'] != '' && $param['end_sendtime'] != '') $where[] = ['a.sendtime', 'between', [$param['start_sendtime'] . ' 00:00:00', $param['end_sendtime'] . ' 23:59:59']];
if ($param['status'] != '') $where[] = ['a.status', '=', $param['status']];
// if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
if ($param['order_type'] != '') $where[] = ['b.order_type', '=', $param['order_type']];
$data = Db::name('order_out')
->alias('a')
->field('a.addtime 下单时间,a.orderCode 订单编号,os.cgdNo 采购单编号,"" 公司名称,a.apply_name 申请人,"" 部门,a.apply_id,a.status 状态,a.send_num 出库总数,"" 商品分类,b.good_code 商品编码,b.good_name 商品名称,"" 客户名称,b.wsend_num 未发货数量,"" 应发货日期,b.skuCode,b.order_type 订单类型,"" 采购员,b.customer_code,b.supplierNo companyNo,b.supNo as supplierNo')
->leftJoin("sale b", "b.orderCode=a.orderCode")
->leftJoin("order_send os", "os.outCode=a.outCode")
->leftJoin("purchease_order d", "d.cgdNo=os.cgdNo")
->where($where)
->order("a.addtime desc")
->cursor();
$userCommon = \app\admin\common\User::getIns();
$list = $tmp_apply = $tmp_cat = $tmp_person = $tmp_codes = [];
foreach ($data as $value) {
if (!isset($tmp_codes[$value['customer_code']])) $tmp_codes[$value['customer_code']] = $value['customer_code'];
if (!isset($tmp_codes[$value['companyNo']])) $tmp_codes[$value['companyNo']] = $value['companyNo'];
if (!isset($tmp_apply[$value['apply_id']])) $tmp_apply[$value['apply_id']] = $value['apply_id'];// get_company_name_by_uid($value['apply_id']);
if (!isset($tmp_person[$value['supplierNo']])) {
$tmp = $userCommon->handle('sInfo', ['code' => $value['supplierNo']]);
$tmp_person[$value['supplierNo']] = $tmp['data']['person']??"";
}
$value['采购员'] = $tmp_person[$value['supplierNo']];
if ($value['订单类型'] == 3 || $value['订单类型'] == 4) {
$goon = Db::name("good_zixun")
->field('id,cat_id,customized')
->where(["spuCode" => $value['商品编码'], "is_del" => 0])
->find();
} else {
$goon = Db::name('good_platform')
->alias('a')
->field('a.id,b.cat_id,b.customized')
->join('good b', 'b.spuCode=a.spuCode', 'left')
->where(['a.is_del' => 0, 'a.skuCode' => $value['skuCode']])
->find();
}
if (!isset($tmp_cat[$goon['cat_id']])) $tmp_cat[$goon['cat_id']] = implode('/', array_column(made($goon['cat_id']), 'name'));
$value['商品分类'] = $tmp_cat[$goon['cat_id']];
$value['应发货日期'] = date('Y-m-d H:i:s', strtotime($value['下单时间']) + $goon['customized'] * 24 * 3600);
$value['状态'] = isset($this->order_out_status[$value['状态']]) ? $this->order_out_status[$value['状态']] : '';
$value['订单类型'] = isset($this->all_order_type[$value['订单类型']]) ? $this->all_order_type[$value['订单类型']] : '';
// unset($value['apply_id']);
unset($value['skuCode']);
$list[] = $value;
}
$names = $userCommon->handle('getCodeAndName', ['code' => $tmp_codes]);
$apply_company = get_company_name_by_uid(array_values($tmp_apply));
foreach ($list as &$val) {
$val['客户名称'] = $names['data'][$val['customer_code']] ?? '';
$val['公司名称'] = $names['data'][$val['companyNo']] ?? '';
$value['部门'] = $apply_company[$val['apply_id']] ?? '';
unset($val['customer_code']);
unset($val['companyNo']);
unset($val['apply_id']);
}
unset($tmp_apply);
unset($tmp_cat);
unset($tmp_person);
unset($tmp_codes);
unset($names);
unset($apply_company);
unset($userCommon);
if (empty($list)) $list[] = ['没有相关可导出的数据'];
excelSave('延时发货申请单' . date('YmdHis'), array_keys($list[0]), $list);
}
//【九、直接发货申请单统计 】
public function orderOutAddr()
{
$param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'start_sendtime' => '', 'end_sendtime' => '', 'status' => '', 'apply_company' => '', 'page' => 1, 'size' => 15, 'order_type' => ''], 'post', 'trim');
$where = [['a.is_del', '=', 0], ['b.send_type', '=', 1], ['a.send_num', '>', 0], ['a.status', '<>', 4]];//send_type==1 直接发货
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['a.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
if ($param['start_sendtime'] != '' && $param['end_sendtime'] != '') $where[] = ['a.sendtime', 'between', [$param['start_sendtime'] . ' 00:00:00', $param['end_sendtime'] . ' 23:59:59']];
if ($param['status'] != '') $where[] = ['a.status', '=', $param['status']];
// if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
if ($param['order_type'] != '') $where[] = ['b.order_type', '=', $param['order_type']];
$count = Db::name('order_out')
->alias('a')
->leftJoin("sale b", "b.orderCode=a.orderCode")
->leftJoin("order_send os", "os.outCode=a.outCode")
->leftJoin("purchease_order po", "po.cgdNo=os.cgdNo")
->leftJoin("order_addr oa", "oa.id=a.addrid")
// ->leftJoin("depart_user u", "u.uid=a.apply_id AND u.is_del=0")
// ->leftJoin("company_item ci", "ci.id=u.itemid")
// ->leftJoin("supplier c", "c.code=po.supplierNo")
->where($where)
->order("a.addtime desc")
->count('a.id');
$data = Db::name('order_out')
->alias('a')
->field('a.addtime,a.orderCode,a.sendtime,a.outCode,a.status,b.good_name,b.sale_price,a.send_num,a.apply_name,"" apply_company,"" cgder,a.post_name,a.post_code,oa.contactor,oa.mobile,oa.addr,oa.addr_code,a.remark,b.order_type,po.supplierNo,a.apply_id')
->leftJoin("sale b", "b.orderCode=a.orderCode")
->leftJoin("order_send os", "os.outCode=a.outCode")
->leftJoin("purchease_order po", "po.cgdNo=os.cgdNo")
->leftJoin("order_addr oa", "oa.id=a.addrid")
// ->leftJoin("depart_user u", "u.uid=a.apply_id AND u.is_del=0")
// ->leftJoin("company_item ci", "ci.id=u.itemid")
// ->leftJoin("supplier c", "c.code=po.supplierNo")
->where($where)
->page($param['page'], $param['size'])
->order("a.addtime desc")
->cursor();
$list = $tmp = [];
foreach ($data as $value) {
if (!isset($tmp[$value['apply_id']])) $tmp[$value['apply_id']] = get_company_name_by_uid($value['apply_id']);
$value['apply_company']=$tmp[$value['apply_id']] ?? '';
$value['send_total_price'] = round($value['send_num'] * $value['sale_price'], 2);
$value['status'] = isset($this->order_out_status[$value['status']]) ? $this->order_out_status[$value['status']] : '';
//拼接省市区
if (!json_decode($value['addr_code'])) {
$temp = explode(',', $value['addr_code']);
$value['addr_code'] = json_encode(['provice_code' => $temp[0] ?? '', 'city_code' => $temp[1] ?? '', 'area_code' => $temp[2] ?? '']);
}
$value['addr'] = GetAddr($value['addr_code']) . $value['addr'];
if (!isset($tmp[$value['supplierNo']])) $tmp[$value['supplierNo']] = $value['supplierNo'];
$list[] = $value;
}
$person = \app\admin\common\User::getIns()->handle('sGetList', ['more_code' => array_values($tmp), 'size' => count($tmp)]);
$person = array_column($person['data']['list'], 'person', 'code');
foreach ($list as &$value) {
$value['cgder'] = $person[$value['supplierNo']] ?? '';
}
unset($tmp);
unset($person);
return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
}
//【九、直接发货申请单统计-导出】
public function orderOutAddrExport()
{
$param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'start_sendtime' => '', 'end_sendtime' => '', 'status' => '', 'apply_company' => '', 'order_type' => ''], 'post', 'trim');
$where = [['a.is_del', '=', 0], ['b.send_type', '=', 1], ['a.send_num', '>', 0], ['a.status', '<>', 4]];//send_type==1 直接发货
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['a.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
if ($param['start_sendtime'] != '' && $param['end_sendtime'] != '') $where[] = ['a.sendtime', 'between', [$param['start_sendtime'] . ' 00:00:00', $param['end_sendtime'] . ' 23:59:59']];
if ($param['status'] != '') $where[] = ['a.status', '=', $param['status']];
if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
if ($param['order_type'] != '') $where[] = ['b.order_type', '=', $param['order_type']];
$data = Db::name('order_out')
->alias('a')
->field('a.addtime 下单日期,a.orderCode 订单编号,a.sendtime 发货时间,a.outCode 发货申请单号,a.status 发货申请单状态,b.good_name 商品名称,b.sale_price 产品单价,a.send_num 发货数量,"" 发货申请单总价,a.apply_name 申请人,"" 部门,"" 采购员,a.post_name 快递公司,a.post_code 快递单号,oa.contactor 收货人,oa.mobile 收货电话,oa.addr 收货地址,oa.addr_code,a.remark 备注,b.order_type 订单类型,po.supplierNo,a.apply_id')
->leftJoin("sale b", "b.orderCode=a.orderCode")
->leftJoin("order_send os", "os.outCode=a.outCode")
->leftJoin("purchease_order po", "po.cgdNo=os.cgdNo")
->leftJoin("order_addr oa", "oa.id=a.addrid")
// ->leftJoin("depart_user u", "u.uid=a.apply_id AND u.is_del=0")
// ->leftJoin("company_item ci", "ci.id=u.itemid")
// ->leftJoin("supplier c", "c.code=po.supplierNo")
->where($where)
->order("a.addtime desc")
->cursor();
$list = $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']] ?? '';
$value['发货申请单总价'] = round(bcmul($value['发货数量'], $value['产品单价'], 3), 2);
$value['发货申请单状态'] = isset($this->order_out_status[$value['发货申请单状态']]) ? $this->order_out_status[$value['发货申请单状态']] : '';
$value['订单类型'] = isset($this->all_order_type[$value['订单类型']]) ? $this->all_order_type[$value['订单类型']] : '';
//拼接省市区
if (!json_decode($value['addr_code'])) {
$temp = explode(',', $value['addr_code']);
$value['addr_code'] = json_encode(['provice_code' => $temp[0] ?? '', 'city_code' => $temp[1] ?? '', 'area_code' => $temp[2] ?? '']);
}
$value['收货地址'] = GetAddr($value['addr_code']) . $value['收货地址'];
if (!isset($tmp[$value['supplierNo']])) $tmp[$value['supplierNo']] = $value['supplierNo'];
unset($value['addr_code']);
unset($value['apply_id']);
$list[] = $value;
}
$person = \app\admin\common\User::getIns()->handle('sGetList', ['more_code' => array_values($tmp), 'size' => count($tmp)]);
$person = array_column($person['data']['list'], 'person', 'code');
foreach ($list as &$value) {
$value['采购员'] = $person[$value['supplierNo']] ?? '';
}
unset($tmp);
unset($person);
if (empty($list)) $list[] = ['没有相关可导出的数据'];
excelSave('直接发货申请单统计' . date('YmdHis'), array_keys($list[0]), $list);
}
//【十、无地址销售订单】
//@todo 待处理，2023-02-08 此处用户及所属部门应该修改，但是困难之处在于数据是依据u.nickname分组的，是否可以按照a.apply_id分组，并在查询之后补充用户昵称和所属部门？
public function saleNotAddr()
{
$param = $this->request->only(['token',
'start_date' => '',
'end_date' => '',
'status' => '',
'apply_company' => '',
"supplierNo"=>"",
"depart_id"=>"",
"companyNo"=>"",
'page' => 1, 'size' => 15, 'order_type' => ''], 'post', 'trim');
$where = [['a.send_type', '=', 2], ['a.is_del', '=', 0], ['a.status', '<>', 3], ['a.send_status', '<>', 3]];
//send_type==2 延迟发货
if ($param['depart_id'] != ''){
$uidArr = get_company_item_user_by_name("",$param['depart_id']);
$where[] = ['a.apply_id', 'in',$uidArr];
}
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['a.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
if ($param['status'] != '') $where[] = ['a.status', '=', $param['status']];
// if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
if ($param['order_type'] != '') $where[] = ['a.order_type', '=', $param['order_type']];
if ($param['supplierNo'] != '') $where[] = ['a.supNo', 'like', "%{$param['supplierNo']}%"];
if ($param['companyNo'] != '') $where[] = ['a.supplierNo', 'like',"%{$param['companyNo']}%"];
$count_row = Db::name('sale')
->alias('a')
->where($where)
->where('a.good_num-a.th_num > ifnull((select sum(receipt_quantity) from wsm_order_addr where orderCode=a.orderCode and is_del=0),0)')
->count();
$data = Db::name('sale')
->alias('a')
->field("a.id,
a.orderCode,
a.order_type,
a.good_code,
a.skuCode,
good_name,
a.good_num,
a.th_num,
a.send_num,
a.wsend_num,
a.status,
a.good_num-a.th_num - IFNULL((select sum(receipt_quantity) from wsm_order_addr where orderCode=a.orderCode and is_del=0),0) not_addr_address_good_num,
a.apply_name nickname,
a.apply_id,
a.addtime")
->where($where)
->page($param['page'], $param['size'])
->order("a.addtime desc")
->where('a.good_num-a.th_num > ifnull((select sum(receipt_quantity) from wsm_order_addr where orderCode=a.orderCode and is_del=0),0)')
->select()
->toArray();
$applyAr = array_unique(array_column($data,"apply_id")) ;
$depart =get_company_name_by_uid($applyAr);
foreach ($data as &$value){
$value['name']=$depart[$value['apply_id']]??"";
$value['month']=date("m",strtotime($value['addtime']));
}
return app_show(0, '请求成功', ['list' => $data, 'count' => $count_row]);
}
//【十、无地址销售订单-导出】
public function saleNotAddrExport()
{
$param = $this->request->only(['token',
'start_date' => '',
'end_date' => '',
"supplierNo"=>"",
"companyNo"=>"",
"depart_id"=>"",
'status' => '',
'apply_company' => '',
'order_type' => ''], 'post', 'trim');
$where = [['a.send_type', '=', 2], ['a.is_del', '=', 0], ['a.status', '<>', 3],['a.send_status', '<>', 3]];//send_type==2 延迟发货
if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['a.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
if ($param['status'] != '') $where[] = ['a.status', '=', $param['status']];
if ($param['depart_id'] != ''){
$uidArr = get_company_item_user_by_name("",$param['depart_id']);
$where[] = ['a.apply_id', 'in',$uidArr];
}
// if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
if ($param['order_type'] != '') $where[] = ['a.order_type', '=', $param['order_type']];
if ($param['supplierNo'] != '') $where[] = ['a.supNo', 'like', "%{$param['supplierNo']}%"];
if ($param['companyNo'] != '') $where[] = ['a.supplierNo', 'like',"%{$param['companyNo']}%"];
$data = Db::name('sale')
->alias('a')
->field("a.orderCode 订单编号,a.order_type 订单类型,
a.good_code 商品成本编码,a.skuCode 商品上线编码,a.good_name 商品名称,a.supplierName 业务公司,a.sale_price 商品单价,
a.good_num 购买数量,a.send_num 已发货数量,a.wsend_num 未发货数量,a.status 订单状态,
a.apply_name 申请人名称,a.apply_id 申请人所属部门,
a.good_num-a.th_num - IFNULL((select sum(receipt_quantity) from wsm_order_addr where orderCode=a.orderCode and is_del=0),0) 无地址数量,
'' 下单月份,
a.addtime")
->where($where)
->where( "a.good_num-a.th_num > IFNULL((select sum(receipt_quantity) from wsm_order_addr where orderCode=a.orderCode and is_del=0),0) ")
->order("a.addtime desc")
->select();
$list = [];
$depart=[];
foreach ($data as $value) {
if(!isset($depart[$value['申请人所属部门']])){
$departArr =get_company_name_by_uid($value['申请人所属部门']);
$depart[$value['申请人所属部门']] = $departArr??"";
}
$value['订单类型'] = isset($this->all_order_type[$value['订单类型']]) ? $this->all_order_type[$value['订单类型']] : '';
$value['订单状态'] = isset($this->all_sale_status[$value['订单状态']]) ? $this->all_sale_status[$value['订单状态']] : '';
$value['申请人所属部门']=$depart[$value['申请人所属部门']]??"";
$value['下单月份']=date("m",strtotime($value['addtime']));
unset($value['addtime']);
$list[] = $value;
}
if (empty($list)) $list[] = ['没有相关可导出的数据'];
excelSave('无地址销售订单列表' . date('YmdHis'), array_keys($list[0]), $list);
}
//【十一、销售订单商品类型】
public function saleMonth()
{
$param = $this->request->only(['token', 'apply_company' => '', 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');
$where = [['a.is_del', '=', 0]];
// if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
$data = Db::name('sale')
->alias('a')
->field('a.id,a.order_type,a.apply_id')
// ->leftJoin("depart_user u", "u.uid=a.apply_id AND u.is_del=0")
// ->leftJoin("company_item ci", "ci.id=u.itemid")
->where($where)
->cursor();
$list = $tmp=[];
$total = ['name' => '总计', 'tag_1' => 0, 'tag_2' => 0, 'tag_3' => 0, 'tag_total' => 0];
foreach ($data as $val) {
if (!isset($tmp[$val['apply_id']])) $tmp[$val['apply_id']] = get_company_name_by_uid($val['apply_id']);
$val['company_name']=$tmp[$val['apply_id']] ?? '';
if (!isset($list[$val['company_name']])) $list[$val['company_name']] = [
'name' => $val['company_name'],
'tag_1' => 0,
'tag_2' => 0,
'tag_3' => 0,
'tag_total' => 0,
];
$list[$val['company_name']]['tag_' . $val['order_type']]++;
$list[$val['company_name']]['tag_total']++;
$total['tag_' . $val['order_type']]++;
$total['tag_total']++;
}
$list = array_merge($list);//重置下标
$list[] = $total;
return app_show(0, '请求成功', $list);
}
//【十一、销售订单商品类型-导出】
public function saleMonthExport()
{
$param = $this->request->only(['token', 'apply_company' => '', 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');
$where = [['a.is_del', '=', 0]];
if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
$data = Db::name('sale')
->alias('a')
->field('a.id,a.order_type,a.apply_id')
// ->leftJoin("depart_user u", "u.uid=a.apply_id AND u.is_del=0")
// ->leftJoin("company_item ci", "ci.id=u.itemid")
->where($where)
->cursor();
$list =$tmp= [];
$total = ['部门' => '总计', '库存品' => 0, '非库存品' => 0, '咨询采反' => 0, '总计' => 0];
foreach ($data as $k => $val) {
if (!isset($tmp[$val['apply_id']])) $tmp[$val['apply_id']] = get_company_name_by_uid($val['apply_id']);
$val['company_name']=$tmp[$val['apply_id']] ?? '';
if (!isset($list[$val['company_name']])) $list[$val['company_name']] = ['部门' => $val['company_name'], '库存品' => 0, '非库存品' => 0, '咨询采反' => 0, '总计' => 0];
switch ($val['order_type']) {
case 1:
$list[$val['company_name']]['库存品']++;
$total['库存品']++;
break;
case 2:
$list[$val['company_name']]['非库存品']++;
$total['非库存品']++;
break;
case 3:
$list[$val['company_name']]['咨询采反']++;
$total['咨询采反']++;
break;
}
$list[$val['company_name']]['总计']++;
$total['总计']++;
}
$list = array_merge($list);//重置下标
$list[] = $total;
if (empty($list)) $list[] = ['没有相关可导出的数据'];
excelSave('销售订单商品类型' . date('YmdHis'), array_keys($list[0]), $list);
}
//【十二、采购订单发货维度表】
public function orderOutMonth()
{
$param = $this->request->only(['token', 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');
$where = [['a.is_del', '=', 0], ['b.is_del', '=', 0]];
if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
$data = Db::name('order_out')
->alias('a')
->field('a.id,b.order_type,b.cat_id,d.supplierNo')
->leftJoin('sale b', 'b.orderCode=a.orderCode')
->leftJoin('order_send c', 'c.outCode=a.outCode')
->leftJoin('purchease_order d', 'd.cgdNo=c.cgdNo')
->where($where)
->cursor();
$list = [];
// $total = ['cat_1' => '总计', 'cat_2' => '', 'cat_3' => '', 'tag_1' => 0, 'tag_2' => 0, 'tag_3' => 0, 'tag_supplier' => 0, 'tag_person' => 0, 'tag_logistics' => 0, 'tag_total' => 0];
// $supplierNos = SupplierRelationUser::where(['is_del' => SupplierUser::$is_del_normal, 'status' => SupplierUser::$status_normal])
// ->group('supplierNo')
// ->column('supplierNo');
$has_sup_acc = checkHasAccountBySupplierNos();
foreach ($data as $val) {
if (!isset($list[$val['cat_id']])) {
$cat = made($val['cat_id']);
$list[$val['cat_id']] = [
'cat_1' => $cat[0]['name'] ?? '',
'cat_2' => $cat[1]['name'] ?? '',
'cat_3' => $cat[2]['name'] ?? '',
'tag_1' => 0,
'tag_2' => 0,
'tag_3' => 0,
'tag_supplier' => 0,
'tag_person' => 0,
'tag_logistics' => 0,
];
}
switch ($val['order_type']) {
case 1:
//库存品,物流岗发
$list[$val['cat_id']]['tag_logistics']++;
break;
case 2: //非库存品
case 3: //咨询采反
if (in_array($val['supplierNo'], $has_sup_acc)) $list[$val['cat_id']]['tag_supplier']++;
else $list[$val['cat_id']]['tag_person']++;
break;
}
$list[$val['cat_id']]['tag_' . $val['order_type']]++;
// $list[$val['cat_id']]['tag_total']++;
// $total['tag_' . $val['order_type']]++;
// $total['tag_total']++;
}
$list = array_merge($list);//重置下标
// $list[] = $total;
return app_show(0, '请求成功', $list);
}
//【十二、采购订单发货维度表-导出】
public function orderOutMonthExport()
{
$param = $this->request->only(['token', 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');
$where = [['a.is_del', '=', 0], ['b.is_del', '=', 0]];
if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
$data = Db::name('order_out')
->alias('a')
->field('a.id,b.order_type,b.cat_id,d.supplierNo')
->leftJoin("sale b", "b.orderCode=a.orderCode")
->leftJoin('order_send c', 'c.outCode=a.outCode')
->leftJoin('purchease_order d', 'd.cgdNo=c.cgdNo')
->where($where)
->cursor();
$has_sup_acc = checkHasAccountBySupplierNos();
// $supplierNos = SupplierRelationUser::where(['is_del' => SupplierUser::$is_del_normal, 'status' => SupplierUser::$status_normal])
// ->group('supplierNo')
// ->column('supplierNo');
$list = [];
// $total = ['一级分类' => '总计', '二级分类' => '', '三级分类' => '', '库存品' => 0, '非库存品' => 0, '咨询采反' => 0, '总计' => 0];
foreach ($data as $val) {
if (!isset($list[$val['cat_id']])) {
$cat = made($val['cat_id']);
$list[$val['cat_id']] = [
'一级分类' => $cat[0]['name'] ?? '',
'二级分类' => $cat[1]['name'] ?? '',
'三级分类' => $cat[2]['name'] ?? '',
'库存品' => 0,
'非库存品' => 0,
'咨询采反' => 0,
'供应商发货' => 0,
'负责人发货' => 0,
'物流岗发货' => 0,
// '总计' => 0,
];
}
switch ($val['order_type']) {
case 1:
$list[$val['cat_id']]['库存品']++;
// $total['库存品']++;
//物流岗发
$list[$val['cat_id']]['物流岗发货']++;
break;
case 2:
$list[$val['cat_id']]['非库存品']++;
// $total['非库存品']++;
if (in_array($val['supplierNo'], $has_sup_acc)) $list[$val['cat_id']]['供应商发货']++;
else $list[$val['cat_id']]['负责人发货']++;
break;
case 3:
$list[$val['cat_id']]['咨询采反']++;
if (in_array($val['supplierNo'], $has_sup_acc)) $list[$val['cat_id']]['供应商发货']++;
else $list[$val['cat_id']]['负责人发货']++;
// $total['咨询采反']++;
break;
}
// $list[$val['cat_id']]['总计']++;
// $total['总计']++;
}
$list = array_merge($list);//重置下标
// $list[] = $total;
if (empty($list)) $list[] = ['没有相关可导出的数据'];
excelSave('采购订单发货维度表' . date('YmdHis'), array_keys($list[0]), $list);
}
//【十三、供应商采购订单情况】
public function purcheaseOrderMonth()
{
$param = $this->request->only(['token',
'supplierNo' => '',
'companyNo' => '',
'supplier_name' => '',
'status' => '',
'person_id' => '',
'start_day' => date('Y-m-01'),
'end_day' => date('Y-m-t')], 'post', 'trim');
$where = [['a.is_del', '=', 0]];
if ($param['supplierNo'] != '') $where[] = ['a.supplierNo', 'like', '%' . $param['supplierNo'] . '%'];
if ($param['companyNo'] != '') $where[] = ['a.companyNo', 'like', '%' . $param['companyNo'] . '%'];
if ($param['supplier_name'] != '') $where[] = ['a.supplier_name', 'like', '%' . $param['supplier_name'] . '%'];
if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
if($param['status']!==''){
$where[]=["a.status","=",$param['status']];
}
if($param['person_id']!==''){
$where[]=["a.cgder_id","=",$param['person_id']];
}
$data = Db::name('purchease_order')
->alias('a')
->leftJoin("order_num b", "b.cgdNo=a.cgdNo")
->leftJoin("sale s", "s.orderCode=b.orderCode AND s.is_del=0")
->leftJoin("platform p", "p.id=s.platform_id AND p.is_del=0")
->where($where)
->group("a.supplierNo,a.supplier_name,a.order_type,p.use_type,a.order_source,a.status")
->field("a.supplierNo,a.supplier_name,a.order_type,p.use_type,a.order_source,a.status,count(a.id) as total_cgd,sum(a.total_fee-a.th_fee) as total_fee,sum(a.wsend_num*a.good_price) as wtotal_fee")
->select()
->toArray();
$list = [];
$total = ['supplier_name' => '总计',
"is_supplier"=>'',//是否开通供应商端
"person"=>'', //供应商负责人
'total_cgd' => 0,//订单总量
'total_fee' => 0,//订单总金额
'dtotal_fee' => 0,//待确认订单总金额
'dtotal_cgd' => 0,//待确认订单总量
'wtotal_fee' => 0,//待入库订单总金额
'wtotal_cgd' => 0,//待入库订单总量
'tag_1' => 0,//库存品
'tag_2' => 0,//非库存品
'tag_c' => 0,//to C
'tag_b' => 0,//to B
'tag_zx' => 0,//咨询采反总数
];
$supArr=array_unique(array_column($data,'supplierNo'));
$supplier =checkHasAccountBySupplierNos($supArr);
$pers =get_personid_by_supplierNo($supArr,"person");
foreach ($data as $val) {
if (!isset($list[$val['supplierNo']])) {
$list[$val['supplierNo']] = [
'supplier_name' => $val['supplier_name'],
"is_supplier"=>isset($supplier[$val['supplierNo']]) ?'是' :'否',//是否开通供应商端
"person"=>$pers[$val['supplierNo']]??"", //供应商负责人
'total_cgd' => 0,//订单总量
'total_fee' => 0,//订单总金额
'dtotal_fee' => 0,//待确认订单总金额
'dtotal_cgd' => 0,//待确认订单总量
'wtotal_fee' => 0,//待入库订单总金额
'wtotal_cgd' => 0,//待入库订单总量
'tag_1' => 0,//库存品
'tag_2' => 0,//非库存品
'tag_c' => 0,//to C
'tag_b' => 0,//to B
'tag_zx' => 0,//咨询采反总数
];
}
switch ($val['order_type']) {
case 1:
//库存品
if ($val['order_source'] == 0) {
$list[$val['supplierNo']]['tag_1']+=$val['total_cgd'];
$total['tag_1']+=$val['total_cgd'];
}
break;
case 2:
//非库存品
$list[$val['supplierNo']]['tag_2']+=$val['total_cgd'];
$total['tag_2']+=$val['total_cgd'];
break;
case 3:
//咨询采反
// if ($val['order_source'] == 2) {
$list[$val['supplierNo']]['tag_zx']+=$val['total_cgd'];
$total['tag_zx']+=$val['total_cgd'];
// }
break;
}
switch ($val['use_type']) {
case 1:
//to B
$list[$val['supplierNo']]['tag_b']+=$val['total_cgd'];
$total['tag_b']+=$val['total_cgd'];
break;
case 2:
//to C
$list[$val['supplierNo']]['tag_c']+=$val['total_cgd'];
$total['tag_c']+=$val['total_cgd'];
break;
}
switch ($val['status']) {
case 0:
//to B
$list[$val['supplierNo']]['dtotal_cgd']+=$val['total_cgd'];
$list[$val['supplierNo']]['dtotal_fee']=bcadd($list[$val['supplierNo']]['dtotal_fee'] , $val['total_fee'],2);
$total['dtotal_cgd']+=$val['total_cgd'];
$total['dtotal_fee']=bcadd( $total['dtotal_fee'], $val['total_fee'],2);
break;
case 1:
case 2:
//to C
$list[$val['supplierNo']]['wtotal_cgd']+=$val['total_cgd'];
$list[$val['supplierNo']]['wtotal_fee']=bcadd( $list[$val['supplierNo']]['wtotal_fee'], $val['wtotal_fee'],2);
$total['wtotal_cgd']+=$val['total_cgd'];
$total['wtotal_fee']=bcadd( $total['wtotal_fee'], $val['wtotal_fee'],2);
break;
}
$list[$val['supplierNo']]['total_cgd']+=$val['total_cgd'];
$list[$val['supplierNo']]['total_fee']=bcadd( $list[$val['supplierNo']]['total_fee'], $val['total_fee'],2);
$total['total_cgd']+=$val['total_cgd'];
$total['total_fee']=bcadd( $total['total_fee'], $val['total_fee'],2);
}
// $list = array_merge($list);//重置下标，关联数组，下标是字符，无法通过array_merge方法重置下标
$list[] =$total;
return app_show(0, '请求成功', array_values($list));
}
//【十三、供应商采购订单情况-导出】
public function purcheaseOrderMonthExport()
{
$param = $this->request->only(['token', 'supplierNo' => '', 'companyNo' => '', 'supplier_name' => '','status' => '',
'person_id' => '', 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');
$where = [['a.is_del', '=', 0]];
if ($param['supplierNo'] != '') $where[] = ['a.supplierNo', 'like', '%' . $param['supplierNo'] . '%'];
if ($param['companyNo'] != '') $where[] = ['a.companyNo', 'like', '%' . $param['companyNo'] . '%'];
if ($param['supplier_name'] != '') $where[] = ['a.supplier_name', 'like', '%' . $param['supplier_name'] . '%'];
if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
if($param['status']!==''){
$where[]=["a.status","=",$param['status']];
}
if($param['person_id']!==''){
$where[]=["a.cgder_id","=",$param['person_id']];
}
$data = Db::name('purchease_order')
->alias('a')
->field('a.id,a.supplierNo,a.supplier_name,a.order_type,a.order_source,p.use_type')
->leftJoin("order_num b", "b.cgdNo=a.cgdNo")
->leftJoin("sale s", "s.orderCode=b.orderCode AND s.is_del=0")
->leftJoin("platform p", "p.id=s.platform_id AND p.is_del=0")
->where($where)
->group("a.supplierNo,a.supplier_name,a.order_type,p.use_type,a.order_source,a.status")
->field("a.supplierNo,a.supplier_name,a.order_type,p.use_type,a.order_source,a.status,count(a.id) as total_cgd,sum(a.total_fee-a.th_fee) as total_fee,sum(a.wsend_num*a.good_price) as wtotal_fee")
->select()
->toArray();
$list = $tmp = [];
$total = [
'供应商名称' => '总计',
'是否开通供应商端'=>'',
'供应商负责人'=>'',
'采购订单总金额' => 0,
'采购订单总量' => 0,
'待确认采购订单总金额' => 0,
'待确认采购订单总数量' => 0,
'待入库采购订单总金额' => 0,
'待入库采购订单总数量' => 0,
'库存品' => 0,
'非库存品' => 0,
'采返商品' =>0,
'ToC平台' => 0,
'ToB平台' => 0,
];
$supArr=array_unique(array_column($data,'supplierNo'));
$supplier =checkHasAccountBySupplierNos($supArr);
$pers =get_personid_by_supplierNo($supArr,"person");
foreach ($data as $val) {
if (!isset($list[$val['supplierNo']])) {
$list[$val['supplierNo']] = [
'采购方' => $val['supplier_name'],
'是否开通供应商端'=>isset($supplier[$val['supplierNo']]) ?'是' :'否',
'供应商负责人'=>$pers[$val['supplierNo']]??"",
'采购订单总金额' => 0,
'采购订单总量' => 0,
'待确认采购订单总金额' => 0,
'待确认采购订单总数量' => 0,
'待入库采购订单总金额' => 0,
'待入库采购订单总数量' => 0,
'库存品' => 0,
'非库存品' => 0,
'采返商品' =>0,
'ToC平台' => 0,
'ToB平台' => 0,
];
}
switch ($val['order_type']) {
case 1:
//库存品
if ($val['order_source'] == 0) {
$list[$val['supplierNo']]['库存品']+=$val['total_cgd'];
$total['库存品']+=$val['total_cgd'];
}
break;
case 2:
//非库存品
$list[$val['supplierNo']]['非库存品']+=$val['total_cgd'];
$total['非库存品']+=$val['total_cgd'];
break;
case 3:
//咨询采反
// if ($val['order_source'] == 2) {
$list[$val['supplierNo']]['采返商品']+=$val['total_cgd'];
$total['采返商品']+=$val['total_cgd'];
// }
break;
}
switch ($val['use_type']) {
case 1:
//to B
$list[$val['supplierNo']]['ToB平台']+=$val['total_cgd'];
$total['ToB平台']+=$val['total_cgd'];
break;
case 2:
//to C
$list[$val['supplierNo']]['ToC平台']+=$val['total_cgd'];
$total['ToC平台']+=$val['total_cgd'];
break;
}
switch ($val['status']) {
case 0:
//to B
$list[$val['supplierNo']]['待确认采购订单总数量']+=$val['total_cgd'];
$list[$val['supplierNo']]['待确认采购订单总金额']+=$val['total_fee'];
$total['待确认采购订单总数量']+=$val['total_cgd'];
$total['待确认采购订单总金额']+=$val['total_fee'];
break;
case 1:
case 2:
//to C
$list[$val['supplierNo']]['待入库采购订单总数量']+=$val['total_cgd'];
$list[$val['supplierNo']]['待入库采购订单总金额']+=$val['wtotal_fee'];
$total['待入库采购订单总数量']+=$val['total_cgd'];
$total['待入库采购订单总金额']+=$val['wtotal_fee'];
break;
}
$list[$val['supplierNo']]['采购订单总量']+=$val['total_cgd'];
$list[$val['supplierNo']]['采购订单总金额']+=$val['total_fee'];
$total['采购订单总量']+=$val['total_cgd'];
$total['采购订单总金额']+=$val['total_fee'];
}
if (empty($list)) $list[] = ['没有相关可导出的数据'];
excelSave('供应商采购订单情况' . date('YmdHis'), array_keys($total), $list);
}
//【十四、供应商负责人采购订单情况】
public function purcheaseOrderPersonMonth()
{
$param = $this->request->only(['token', 'supplierNo' => '', 'supplier_name' => '', 'flag' => '', 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');
$where = [['a.is_del', '=', 0]];
if ($param['supplierNo'] != '') $where[] = ['a.supplierNo', 'like', '%' . $param['supplierNo'] . '%'];
if ($param['supplier_name'] != '') $where[] = ['a.supplier_name', 'like', '%' . $param['supplier_name'] . '%'];
if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
if ($param['flag'] != '') {
// $supplierNos = SupplierRelationUser::where(['is_del' => SupplierUser::$is_del_normal, 'status' => SupplierUser::$status_normal])
// ->group('supplierNo')
// ->column('supplierNo');
$has_sup_acc = checkHasAccountBySupplierNos();
if ($param['flag'] == 1) $where[] = ['a.supplierNo', 'in', array_keys($has_sup_acc)];//1.开通账号的
else $where[] = ['a.supplierNo', 'not in', array_keys($has_sup_acc)];//2.未开通账号的
}
$data = Db::name('purchease_order')
->alias('a')
->where($where)
->leftJoin("order_num b", "b.cgdNo=a.cgdNo")
->leftJoin("sale s", "s.orderCode=b.orderCode AND s.is_del=0")
->leftJoin("platform p", "p.id=s.platform_id AND p.is_del=0")
->group("a.cgder_id,a.cgder,a.order_type,p.use_type,a.order_source")
->field("a.cgder_id,a.cgder,a.order_type,p.use_type,a.order_source,count(a.id) as total_cgd")
->select()
->toArray();
// var_dump($data);
$list = [];
$total = ['person' => '总计', 'total_cgd' => 0, 'tag_1' => 0, 'tag_2' => 0, 'tag_c' => 0, 'tag_b' => 0, 'tag_zx' => 0, 'tag_else' => 0];
$list[]=&$total;
foreach ($data as $val) {
if(!isset($list[$val['cgder_id']]))$list[$val['cgder_id']]=['person' => '', 'total_cgd' => 0, 'tag_1' => 0, 'tag_2' => 0, 'tag_c' => 0, 'tag_b' => 0, 'tag_zx' => 0, 'tag_else' => 0];
$list[$val['cgder_id']]['person']=$val['cgder'];
$list[$val['cgder_id']]['total_cgd']+=$val['total_cgd'];
switch ($val['order_type']) {
case 1:
//库存品
$list[$val['cgder_id']]['tag_1']+=$val['total_cgd'];
$total['tag_1']+=$val['total_cgd'];
break;
case 2:
//非库存品
$list[$val['cgder_id']]['tag_2']+=$val['total_cgd'];
$total['tag_2']+=$val['total_cgd'];
break;
case 3:
//咨询采反
if ($val['order_source'] == 2) {
$list[$val['cgder_id']]['tag_zx']+=$val['total_cgd'];
$total['tag_zx']+=$val['total_cgd'];
} else {
$list[$val['cgder_id']]['tag_else']+=$val['total_cgd'];
$total['tag_else']+=$val['total_cgd'];
}
break;
}
switch ($val['use_type']) {
case 1:
//to B
$list[$val['cgder_id']]['tag_b']+=$val['total_cgd'];
$total['tag_b']+=$val['total_cgd'];
break;
case 2:
//to C
$list[$val['cgder_id']]['tag_c']+=$val['total_cgd'];
$total['tag_c']+=$val['total_cgd'];
break;
}
$total['total_cgd'] +=$val['total_cgd'];
}
$list=array_values($list);
return app_show(0, '请求成功', $list);
}
//【十四、供应商负责人采购订单情况-导出】
public function purcheaseOrderPersonMonthExport()
{
$param = $this->request->only(['token', 'supplierNo' => '', 'supplier_name' => '', 'flag' => '', 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');
$where = [['a.is_del', '=', 0]];
if ($param['supplierNo'] != '') $where[] = ['a.supplierNo', 'like', '%' . $param['supplierNo'] . '%'];
if ($param['supplier_name'] != '') $where[] = ['a.supplier_name', 'like', '%' . $param['supplier_name'] . '%'];
if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
if ($param['flag'] != '') {
$has_sup_acc = checkHasAccountBySupplierNos();
if ($param['flag'] == 1) $where[] = ['a.supplierNo', 'in', array_keys($has_sup_acc)];//1.开通账号的
else $where[] = ['a.supplierNo', 'not in', array_keys($has_sup_acc)];//2.未开通账号的
}
$data = Db::name('purchease_order')
->alias('a')
->where($where)
->leftJoin("order_num b", "b.cgdNo=a.cgdNo")
->leftJoin("sale s", "s.orderCode=b.orderCode AND s.is_del=0")
->leftJoin("platform p", "p.id=s.platform_id AND p.is_del=0")
->group("a.cgder_id,a.cgder,a.order_type,p.use_type,a.order_source")
->field("a.cgder_id,a.cgder,a.order_type,p.use_type,a.order_source,count(a.id) as total_cgd")
->select()
->toArray();
$list = [];
$total = ['采购方' => '总计', '订单总量' => 0, '库存品' => 0, '非库存品' => 0, 'toC' => 0, 'toB' => 0, '反馈单数合计' => 0, '采销录单采反' => 0];
foreach ($data as $val) {
if (!isset($list[$val['personid']])) {
$list[$val['cgder_id']] = [
'采购方' => $val['cgder'],
'订单总量' => 0,//订单总量
'库存品' => 0,//库存品
'非库存品' => 0,//非库存品
'toC' => 0,//to C
'toB' => 0,//to B
'反馈单数合计' => 0,//咨询采反总数
'采销录单采反' => 0,//采销录单采反
];
}
switch ($val['order_type']) {
case 1:
//库存品
if ($val['order_source'] == 0) {
$list[$val['cgder_id']]['库存品']+=$val['total_cgd'];
$total['库存品']+=$val['total_cgd'];
}
break;
case 2:
//非库存品
$list[$val['cgder_id']]['非库存品']+=$val['total_cgd'];
$total['非库存品']+=$val['total_cgd'];
break;
case 3:
//咨询采反
if($val['order_source'] == 2){
$list[$val['supplierNo']]['反馈单数合计']+=$val['total_cgd'];
$total['反馈单数合计']+=$val['total_cgd'];
} else {
$list[$val['cgder_id']]['采销录单采反']+=$val['total_cgd'];
$total['采销录单采反']+=$val['total_cgd'];
}
break;
}
switch ($val['use_type']) {
case 1:
//to B
$list[$val['cgder_id']]['toB']+=$val['total_cgd'];
$total['toB']+=$val['total_cgd'];
break;
case 2:
//to C
$list[$val['cgder_id']]['toC']+=$val['total_cgd'];
$total['toC']+=$val['total_cgd'];
break;
}
$list[$val['cgder_id']]['订单总量']+=$val['total_cgd'];
$total['订单总量']+=$val['total_cgd'];
}
$list[] = $total;
if (empty($list)) $list[] = ['没有相关可导出的数据'];
excelSave('供应商负责人采购订单情况' . date('YmdHis'), array_keys($total), $list);
}
//竞价单反馈情况
public function FeedBackBySupplier(){
$param = $this->request->only([
'supplierNo' => '',
'companyNo' => '',
'companyName' => '',
'supplier_name' => '',
'start_day' => date('Y-m-01'),
'end_day' => date('Y-m-t')], 'post', 'trim');
$where = [['a.is_del', '=', 0]];
if ($param['supplierNo'] != '') $where[] = ['a.supplierNo', 'like', '%' . $param['supplierNo'] . '%'];
if ($param['supplier_name'] != '') $where[] = ['a.supplierName', 'like', '%' . $param['supplier_name'] . '%'];
if ($param['companyNo'] != '') $where[] = ['b.companyNo', 'like', '%' . $param['companyNo'] . '%'];
if ($param['companyName'] != '') $where[] = ['b.companyName', 'like', '%' . $param['companyName'] . '%'];
if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
$list =Db::name("consult_bids")->alias("a")
->leftJoin("consult_order b","a.zxNo=b.zxNo")
->leftJoin("consult_info c","a.infoNo=c.infoNo")
->leftJoin("platform d","b.platform_code=d.id")
->where($where)
->group("a.supplierNo,d.use_type")
->field("a.supplierNo,a.supplierName,d.use_type,count(a.id) feed_num,sum(c.num) good_num,sum(c.num*a.total_fee) total_price,a.cgder")->select()->toArray();
$data=[];
$supArr =array_column($list,"supplierNo");
$supp=checkHasAccountBySupplierNos($supArr);
$nmer =["supplierName"=>'合计',"is_supplier"=>"","person"=>"","feed_num"=>0,"good_num"=>0,"total_price"=>0,"toc"=>0,"tob"=>0];
$data[]=&$nmer;
foreach ($list as $value){
if(!isset($data[$value['supplierNo']])){
$data[$value['supplierNo']]=[
"supplierName"=>$value['supplierName'],
"is_supplier"=>isset($supp[$value['supplierNo']])?'是':'否',
"person"=>$value['cgder'],
"feed_num"=>0,
"good_num"=>0,
"total_price"=>0,
"toc"=>0,
"tob"=>0
];
}
switch ($value['use_type']){
case 1:
$data[$value['supplierNo']]['tob']= bcadd($data[$value['supplierNo']]['tob'],$value['feed_num'],2);
$nmer['tob']=bcadd($nmer['tob'],$value['feed_num'],2);
break;
case 2:
$data[$value['supplierNo']]['toc']=bcadd($data[$value['supplierNo']]['toc'],$value['feed_num'],2);
$nmer['toc']=bcadd($nmer['toc'],$value['feed_num'],2);
break;
}
$data[$value['supplierNo']]['feed_num']=bcadd($data[$value['supplierNo']]['feed_num'],$value['feed_num'],2);
$data[$value['supplierNo']]['good_num']=bcadd($data[$value['supplierNo']]['good_num'],$value['good_num'],2);
$data[$value['supplierNo']]['total_price']=bcadd($data[$value['supplierNo']]['total_price'],$value['total_price'],2);
$nmer['feed_num']=bcadd($value['feed_num'],$nmer['feed_num'],2);
$nmer['good_num']=bcadd($value['good_num'],$nmer['good_num'],2);
$nmer['total_price']=bcadd($value['total_price'],$nmer['total_price'],2);
}
return app_show(0, '请求成功', array_values($data));
}
//当日库存销售情况
public function saleStock(){
$param = $this->request->only([
'supplierNo' => '',
'companyNo' => '',
'companyName' => '',
'supplier_name' => '',
'good_name' => '',
'page' => 1,
'size' => 15,
'start_day' => date('Y-m-01'),
'end_day' => date('Y-m-t')
], 'post', 'trim');
$where = [['a.is_del', '=', 0],["a.is_stock","=","1"]];
if ($param['supplierNo'] != '') $where[] = ['a.supNo', 'like', '%' . $param['supplierNo'] . '%'];
if ($param['supplier_name'] != '') $where[] = ['a.supName', 'like', '%' . $param['supplier_name'] . '%'];
if ($param['companyNo'] != '') $where[] = ['a.supplierNo', 'like', '%' . $param['companyNo'] . '%'];
if ($param['companyName'] != '') $where[] = ['a.supplierName', 'like', '%' . $param['companyName'] . '%'];
if ($param['good_name'] != '') $where[] = ['a.good_name', 'like', '%' . $param['good_name'] . '%'];
if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
$count = Db::name('sale')
->alias('a')
->where($where)->group("a.good_code")
->count('a.id');
$list=Db::name("sale")->alias("a")
->leftJoin('order_num on', 'a.orderCode=on.orderCode')
->leftJoin('purchease_order po', 'po.cgdNo=on.cgdNo')
->where($where)
->page($param['page'], $param['size'])
->order('a.addtime', 'desc')
->field("a.good_code,a.good_name,sum(a.good_num) as good_num,a.sale_price,a.is_stock,sum(a.send_num) as send_num,0 stock_num,a.supplierName,po.good_price,sum(po.total_fee) total_fee")
->group("a.good_code")
->cursor();
$data=[];
foreach ($list as $value){
$value['stock_num'] = Db::name("good_stock")->where("spuCode",$value['good_code'])->sum("usable_stock");
if($value['is_stock']==1){
$value['good_price'] = Db::name("purchease_order")->where(["spuCode"=>$value['good_code'],"order_source"=>0])->order('id desc')->value("good_price",0);
$value['total_fee'] = bcmul($value['good_price'],$value['good_num'],2);
}
$data[]= $value;
}
return app_show(0, '请求成功', ['list' => $data, 'count' => $count]);
}
//当日库存销售情况导出
public function saleStockExport(){
$param = $this->request->only([
'supplierNo' => '',
'companyNo' => '',
'companyName' => '',
'supplier_name' => '',
'good_name' => '',
'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');
$where = [['a.is_del', '=', 0],["a.is_stock","=","1"]];
if ($param['supplierNo'] != '') $where[] = ['a.supNo', 'like', '%' . $param['supplierNo'] . '%'];
if ($param['supplier_name'] != '') $where[] = ['a.supName', 'like', '%' . $param['supplier_name'] . '%'];
if ($param['companyNo'] != '') $where[] = ['a.supplierNo', 'like', '%' . $param['companyNo'] . '%'];
if ($param['companyName'] != '') $where[] = ['a.supplierName', 'like', '%' . $param['companyName'] . '%'];
if ($param['good_name'] != '') $where[] = ['a.good_name', 'like', '%' . $param['good_name'] . '%'];
if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
$list=Db::name("sale")->alias("a")
->leftJoin('order_num on', 'a.orderCode=on.orderCode')
->leftJoin('purchease_order po', 'po.cgdNo=on.cgdNo')
->where($where)
->order('a.addtime', 'desc')
->group("a.good_code")
->field("a.good_code 商品编号,a.good_name 产品名称,sum(a.good_num) 当日库存销售,a.is_stock,sum(a.send_num) 当日库存发货,0 当日剩余可用库存,a.supplierName 业务公司,po.good_price 库存采购单价,sum(po.total_fee) 当日库存消耗金额")
->cursor();
$data=[];
foreach ($list as $value){
$value['当日剩余可用库存'] = Db::name("good_stock")->where("spuCode",$value['商品编号'])->sum("usable_stock");
if($value['is_stock']==1){
$value['库存采购单价'] = Db::name("purchease_order")->where(["spuCode"=>$value['商品编号'],"order_source"=>0])->order('id desc')->value("good_price",0);
$value['当日库存消耗金额'] = bcmul($value['库存采购单价'],$value['当日库存销售'],2);
}
unset($value['is_stock']);
$data[]= $value;
}
if (empty($data)) $data[] = ['没有相关可导出的数据'];
excelSave('订单报表' . date('YmdHis'), array_keys($data[0]), $data);
}
//待库管发货
public function OutChildList(){
$param = $this->request->only([
'supplierNo' => '',
'companyNo' => '',
'companyName' => '',
'supplier_name' => '',
'depart_id' => '',
'person_id' => '',
'page' => 1,
'size' => 15,
// 'start_day' => date('Y-m-01'),
// 'end_day' => date('Y-m-t'),
'order_start_day' => date('Y-m-01'),
'order_end_day' => date('Y-m-t')],
'post', 'trim');
$where = [['a.is_del', '=', 0],["a.status","in",[0,1]]];
if ($param['depart_id'] != ''){
$uidArr = get_company_item_user_by_name("",$param['depart_id']);
$where[] = ['a.apply_id', 'in',$uidArr];
}
if($param['person_id'] != '') {
$where[] = ['c.cgderid', '=',$param['person_id']];
}
if ($param['supplierNo'] != '') $where[] = ['a.supplierNo', 'like', '%' . $param['supplierNo'] . '%'];
if ($param['supplier_name'] != '') $where[] = ['a.supplierName', 'like', '%' . $param['supplier_name'] . '%'];
if ($param['companyNo'] != '') $where[] = ['a.companyNo', 'like', '%' . $param['companyNo'] . '%'];
if ($param['companyName'] != '') $where[] = ['a.companyName', 'like', '%' . $param['companyName'] . '%'];
// if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
if ($param['order_start_day'] != '' && $param['order_end_day'] != '') $where[] = ['a.addtime', 'between',
[$param['order_start_day'] . ' 00:00:00', $param['order_end_day'] . ' 23:59:59']];
$count = Db::name("order_out_child")->alias("a")->leftJoin('sale c','a.orderCode=c.orderCode')->where($where)->count();
$list = Db::name("order_out_child")
->alias("a")
->leftJoin("warehouse_info b","a.wsm_code=b.wsm_code")
->leftJoin("sale c","a.orderCode=c.orderCode")
->leftJoin("order_send d","a.outCode=d.outCode")
->page($param['page'],$param['size'])
->where($where)
->field("a.outChildCode,a.outCode,a.orderCode,a.supplierNo,a.supplierName,a.companyNo,a.companyName,a.customer_code,a.customer_name,
a.status,a.order_type,a.order_source,a.num,a.wsm_code,b.name,a.spuCode,a.skuCode,a.good_name,c.addtime,a.apply_name,a.apply_id,c.platform_id,a.post_name,
a.post_code,a.post_fee,a.addrid,d.cgdNo,c.remark,datediff(now(),a.addtime) sx,b.contactor_name")
->select()
->toArray();
$supArr =array_unique( array_column($list,"supplierNo"));
$platArr =array_unique(array_column($list,"platform_id"));
$AddrArr =array_unique(array_column($list,"addrid"));
$applyArr =array_unique(array_column($list,"apply_id"));
$supp=checkHasAccountBySupplierNos($supArr);
$preson = get_personid_by_supplierNo($supArr,"person");
$plafrom = Db::name("platform")->where("id","in",$platArr)->column("use_type","id");
$addr = Db::name("order_addr")->where("id","in",$AddrArr)->column("addr,addr_code,contactor,mobile","id");
$depart = get_company_name_by_uid($applyArr);
// $depart = $applyRe['data']??[];
foreach ($list as &$value){
$value['is_supplier'] = isset($supp[$value['supplierNo']])?'已开通':'未开通';
$value['person'] = $preson[$value['supplierNo']]??"";
$value['platform_type'] = $plafrom[$value['platform_id']]??"0";
$value['addr'] = "";
$value['addr_contactor'] = "";
$value['addr_mobile'] ="";
$value['report_sx'] =report_sx($value['sx']);
if(isset($addr[$value['addrid']])){
if($addr[$value['addrid']]['addr_code']!=''){
if (!json_decode($addr[$value['addrid']]['addr_code'])) {
$temp = explode(',', $addr[$value['addrid']]['addr_code']);
$addr[$value['addrid']]['addr_code'] = json_encode(['provice_code' => $temp[0] ?? '', 'city_code' => $temp[1] ?? '', 'area_code' => $temp[2] ?? '']);
}
$value['addr'] = GetAddr($addr[$value['addrid']]['addr_code']) . $addr[$value['addrid']]['addr'];
}else{
$value['addr'] = $addr[$value['addrid']]['addr'];
}
$value['addr_contactor'] = $addr[$value['addrid']]['contactor'];
$value['addr_mobile'] =$addr[$value['addrid']]['mobile'];
}
$value['depart']=$depart[$value['apply_id']]??"";
}
return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);
}
//待库管发货
public function OutChildExport(){
$param = $this->request->only([
'supplierNo' => '',
'companyNo' => '',
'companyName' => '',
'depart_id' => '',
'person_id' => '',
'supplier_name' => '',
// 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t'),
'order_start_day' => date('Y-m-01'), 'order_end_day' => date('Y-m-t'),
], 'post', 'trim');
$where = [['a.is_del', '=', 0],["a.status","in",[0,1]]];
if ($param['depart_id'] != ''){
$uidArr = get_company_item_user_by_name("",$param['depart_id']);
$where[] = ['a.apply_id', 'in',$uidArr];
}
if($param['person_id'] != '') {
$where[] = ['c.cgderid', '=',$param['person_id']];
}
if ($param['supplierNo'] != '') $where[] = ['a.supplierNo', 'like', '%' . $param['supplierNo'] . '%'];
if ($param['supplier_name'] != '') $where[] = ['a.supplierName', 'like', '%' . $param['supplier_name'] . '%'];
if ($param['companyNo'] != '') $where[] = ['a.companyNo', 'like', '%' . $param['companyNo'] . '%'];
if ($param['companyName'] != '') $where[] = ['a.companyName', 'like', '%' . $param['companyName'] . '%'];
// if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
if ($param['order_start_day'] != '' && $param['order_end_day'] != '') $where[] = ['a.addtime', 'between',
[$param['order_start_day'] . ' 00:00:00', $param['order_end_day'] . ' 23:59:59']];
$list = Db::name("order_out_child")
->alias("a")
->leftJoin("warehouse_info b","a.wsm_code=b.wsm_code")
->leftJoin("sale c","a.orderCode=c.orderCode")
->leftJoin("order_send d","a.outCode=d.outCode")
->where($where)
->field("a.outChildCode 发货工单号,a.outCode 发货单编号,a.orderCode 销售订单号,'' 是否开通供应商端,a.status 状态,a.order_type 商品类型,a.order_source 订单来源,
a.num 总数量,a.wsm_code 仓库编号,b.name 仓库名称,a.spuCode 商品成本编号,a.skuCode 商品上线编号,a.good_name 商品名称,
a.companyNo 业务公司编号,a.companyName 业务公司名称,a.customer_code 客户编号,a.customer_name 客户名称,a.supplierNo 供应商编号,a.supplierName 供应商名称,
c.addtime 下单时间,a.apply_name 申请人,a.apply_id 申请人部门,c.platform_id 对接类型,a.post_name 物流公司,a.post_code 物流编号,a.post_fee 物流费用,
a.addrid,'' 收货地址,'' 收货联系人,'' 联系方式, d.cgdNo 采购单号,c.remark 备注,datediff(now(),a.addtime) 时效,b.contactor_name 仓库负责人,'' 供应商负责人")
->select()
->toArray();
$supArr =array_unique( array_column($list,'供应商编号'));
$platArr =array_unique(array_column($list,'对接类型'));
$AddrArr =array_unique(array_column($list,'addrid'));
$applyArr =array_unique(array_column($list,'申请人部门'));
$supp=checkHasAccountBySupplierNos($supArr);
$preson = get_personid_by_supplierNo($supArr,"person");
$plafrom = Db::name("platform")->where("id","in",$platArr)->column("use_type","id");
$addr = Db::name("order_addr")->where("id","in",$AddrArr)->column("addr,addr_code,contactor,mobile","id");
$depart = get_company_name_by_uid($applyArr);
$childstatus = [0=>"待确认",1=>"待发货"];
foreach ($list as &$value){
$value['是否开通供应商端'] = isset($supp[$value['供应商编号']])?'已开通':'未开通';
$value['供应商负责人'] = $preson[$value['供应商编号']]??"";
$value['对接类型'] =$this->plat_type[$plafrom[$value['对接类型']]??"0"];
$value['商品类型'] = $this->all_order_type[$value['商品类型']]??"";
$value['订单来源'] = $this->order_source[$value['订单来源']]??"";
$value['收货地址'] = "";
$value['收货联系人'] = "";
$value['联系方式'] ="";
$value['时效'] =report_sx($value['时效']);
$value['状态'] = $childstatus[$value['状态']]??"";
if(isset($addr[$value['addrid']])){
if($addr[$value['addrid']]['addr_code']!=''){
if (!json_decode($addr[$value['addrid']]['addr_code'])) {
$temp = explode(',', $addr[$value['addrid']]['addr_code']);
$addr[$value['addrid']]['addr_code'] = json_encode(['provice_code' => $temp[0] ?? '', 'city_code' => $temp[1] ?? '', 'area_code' => $temp[2] ?? '']);
}
$value['收货地址'] = GetAddr($addr[$value['addrid']]['addr_code']) . $addr[$value['addrid']]['addr'];
}else{
$value['收货地址'] = $addr[$value['addrid']]['addr'];
}
$value['收货联系人'] = $addr[$value['addrid']]['contactor'];
$value['联系方式'] =$addr[$value['addrid']]['mobile'];
}
$value['申请人部门']=$depart[$value['申请人部门']]??"";
unset($value['addrid']);
}
if (empty($list)) $list[] = ['没有相关可导出的数据'];
excelSave('订单报表' . date('YmdHis'), array_keys($list[0]), $list);
}
//待采购备货
public function OrderOutBk(){
$param = $this->request->only([
'supplierNo' => '',
'companyNo' => '',
'companyName' => '',
'supplier_name' => '',
'depart_id' => '',
'person_id' => '',
'page' => 1,
'size' => 15,
'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');
$where = [['a.is_del', '=', 0],['a.status', '=', 0]];
if ($param['supplierNo'] != '') $where[] = ['b.supNo', 'like', '%' . $param['supplierNo'] . '%'];
if ($param['supplier_name'] != '') $where[] = ['b.supName', 'like', '%' . $param['supplier_name'] . '%'];
if ($param['companyNo'] != '') $where[] = ['b.supplierNo', 'like', '%' . $param['companyNo'] . '%'];
if ($param['companyName'] != '') $where[] = ['b.supplierName', 'like', '%' . $param['companyName'] . '%'];
if ($param['depart_id'] != ''){
$uidArr = get_company_item_user_by_name("",$param['depart_id']);
$where[] = ['a.apply_id', 'in',$uidArr];
}
if($param['person_id'] != '') {
$where[] = ['b.cgderid', '=',$param['person_id']];
}
if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
$count= Db::name("order_out")->alias("a")
->leftJoin("sale b","a.orderCode=b.orderCode")
->leftJoin("order_num c","a.orderCode = c.orderCode")
->leftJoin("order_addr d","a.addrid = d.id")->where($where)->count("a.id");
$list =Db::name("order_out")->alias("a")
->leftJoin("sale b","a.orderCode=b.orderCode")
->leftJoin("order_num c","a.orderCode = c.orderCode")
->leftJoin("order_addr d","a.addrid = d.id")
->field("a.outCode,a.orderCode,c.cgdNo,a.send_num,b.platform_id,a.status,b.order_type,b.order_source,supName,b.cat_id,b.good_code,b.good_name
,b.customerName,b.customer_code,b.supplierName,b.supplierNo,d.contactor,d.mobile,a.post_name,a.post_code,a.post_fee,'' send_time,
a.apply_id,a.apply_name,a.addtime,b.addtime createdTime,datediff(now(),a.addtime) sx,b.cgder")
->page($param['page'],$param['size'])
->where($where)
->select()->toArray();
$data=[];
$platArr =array_unique(array_column($list,"platform_id"));
$applyArr = array_unique(array_column($list,"apply_id")) ;
$catArr =array_column($list,"cat_id");
$plafrom = Db::name("platform")->where("id","in",$platArr)->column("use_type","id");
$cat = Db::name("cat")->where("id","in",$catArr)->column("search","id");
$depart= get_company_name_by_uid($applyArr);
// $depart = $applyRe['data']??[];
foreach ($list as $item) {
$item['platform_type'] = $this->plat_type[$plafrom[$item['platform_id']]??0];
$item['depart'] = $depart[$item['apply_id']]??"";
$item['order_source'] = $this->order_source[$item['order_source']]??"";
$item['order_type'] = $this->all_order_type[$item['order_type']]??"";
$item['cat_name'] = $cat[$item['cat_id']]??"";
$item['report_sx'] =report_sx($item['sx']);
$data[]=$item;
}
return app_show(0, '请求成功', ['list' => $data, 'count' => $count]);
}
//待采购备货
public function OrderOutExport(){
$param = $this->request->only([
'supplierNo' => '',
'companyNo' => '',
'companyName' => '',
"depart_id"=>"",
"person_id"=>"",
'supplier_name' => '',
'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');
$where = [['a.is_del', '=', 0],['a.status', '=', 0]];
if ($param['supplierNo'] != '') $where[] = ['b.supNo', 'like', '%' . $param['supplierNo'] . '%'];
if ($param['supplier_name'] != '') $where[] = ['b.supName', 'like', '%' . $param['supplier_name'] . '%'];
if ($param['companyNo'] != '') $where[] = ['b.supplierNo', 'like', '%' . $param['companyNo'] . '%'];
if ($param['companyName'] != '') $where[] = ['b.supplierName', 'like', '%' . $param['companyName'] . '%'];
if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];
if ($param['depart_id'] != ''){
$uidArr = get_company_item_user_by_name("",$param['depart_id']);
$where[] = ['a.apply_id', 'in',$uidArr];
}
if($param['person_id'] != '') {
$where[] = ['b.cgderid', '=',$param['person_id']];
}
$list =Db::name("order_out")->alias("a")
->leftJoin("sale b","a.orderCode=b.orderCode")
->leftJoin("order_num c","a.orderCode = c.orderCode")
->leftJoin("order_addr d","a.addrid = d.id")
->field("
a.outCode 发货单编号,
a.orderCode 订单编号,
c.cgdNo 采购单编号,
a.send_num 发货数量,
b.platform_id 平台类型,
a.status 状态,
b.order_type 商品类型,
b.order_source 订单来源,
supName 供应商,
b.cat_id 商品分类,
b.good_code 商品编号,
b.good_name 商品名称,
b.customerName 客户名称,
b.customer_code 客户编号,
b.supplierName 业务公司名称,
b.supplierNo 业务公司编号,
d.contactor 收货联系人,
d.mobile 联系方式,
a.post_name 快递公司,
a.post_code 快递编号,
a.post_fee 快递费用,
'' 发货时间,
a.apply_id 申请人部门,
a.apply_name 申请人,
a.addtime 添加时间,
b.addtime 下单日期,
datediff(now(),a.addtime) 时效,
b.cgder 供应商负责人")
->where($where)
->cursor();
$data=[];
// $platArr =array_column($list,"平台类型");
// $applyArr =array_column($list,"申请人部门");
// $catArr =array_column($list,"商品分类");
// $plafrom = Db::name("platform")->where("id",'in',$platArr)->column("use_type","id");
// $cat = Db::name("cat")->where("id",'in',$catArr)->column("search","id");
// $depart = get_company_name_by_uid($applyArr);
$depart=[];
$cat=[];
$plafrom=[];
foreach ($list as $item) {
if(!isset($depart[$item['申请人部门']])){
$temp=get_company_name_by_uid($item['申请人部门']);
$depart[$item['申请人部门']]=$temp;
}
if(!isset($cat[$item['商品分类']])){
$cat[$item['商品分类']]= Db::name("cat")->where("id",$item['商品分类'])->value("search","");
}
if(!isset($plafrom[$item['平台类型']])){
$plafrom[$item['平台类型']]= Db::name("platform")->where("id",$item['平台类型'])->value("use_type","0");
}
$item['平台类型'] = $this->plat_type[$plafrom[$item['平台类型']]];
$item['申请人部门'] = $depart[$item['申请人部门']]??"";
$item['状态'] = $this->order_out_status[$item['状态']]??"";
$item['订单来源'] = $this->order_source[$item['订单来源']]??"";
$item['商品类型'] = $this->all_order_type[$item['商品类型']]??"";
$item['商品分类'] = $cat[$item['商品分类']]??"";
$item['时效'] =report_sx($item['时效']);
$data[]=$item;
}
if (empty($data)) $data[] = ['没有相关可导出的数据'];
excelSave('订单报表' . date('YmdHis'), array_keys($data[0]), $data);
}
}