post(); $start = isset($post['start']) && $post['start'] != "" ? $post['start']." 00:00:00" : ''; $end = isset($post['end']) && $post['end'] != "" ? $post['end']." 23:59:59" : ''; $where=''; if($start!=""){ $where.=" and ordertime >='{$start}'"; } if($end!=""){ $where.=" and ordertime <='{$end}'"; } $sql = "SELECT a.productNo 'QRD-CP确认单产品编号', a.companyName '`KH.2客户名称', a.supperinfo '公司名称', a.suppitem 'PT.1平台类型', a.khzzxz 'PT.2平台编码', a.poNo 'PT.4PO编号', a.qrdNo 'QRD.4确认单编号', a.qrd_type 'QRD.确认单类型', a.ordertime 'QRD.确认单下单时间', a.sale_name 'QRD.业务员', a.product_name 'QRD.产品名称', a.product_code 'QRD.产品编号', a.cat_f 'QRD.一级分类', a.cat_t 'QRD.二级分类', a.cat_d 'QRD.三级分类', a.fund_code 'QRD.财务核算码', a.unit 'QRD.单位', a.tax 'QRD.税率', a.diff_weight 'QRD.重量工差', a.diff_price 'QRD.金额工差', a.paytime 'QRD.承诺回款时间', a.sale_price 'QRD.销售单价', a.order_num 'QRD.下单数量', a.sale_total 'QRD.销售总额', a.income 'QRD.不含税收入', a.zxcode 'QRD.咨询单号', a.zxtype 'QRD.咨询单类型', a.qrd_delivery 'qrd发货方式', a.qrd_stock 'qrd.是否库存产品', a.cgdNo 'CGD采购单单号', a.cgdtime 'CGD采购单下单日期', a.cgdtype 'CGD采购单类型', a.cgd_saler 'CGD采购单业务员', a.suplier_name 'CGD供货商名称', a.paking 'CGD出库包装库存', a.cgd_tax 'CGD采购单税点', a.pakage_fee 'CGD包装费', a.cert_fee 'CGD证书费', a.mark_fee 'CGD加标费', a.open_fee 'CGD开模费', a.cost_fee 'CGD成本工艺费', a.naked_fee 'CGD裸价', a.delivery_fee 'CGD物流费', a.cgd_price 'CGD采购单单价', a.cgd_num 'CGD采购单数量', a.cgd_total 'CGD采购单金额', a.cgd_cost 'CGD采购单成本', ROUND( b.Number1618249146997, 2 ) 'QRD已回款', ROUND( b.number1618249149738+b.paying_fee, 2 ) 'QRD未回款', IF ( ak.rela_form = 2 || ak.rela_form = 3, ak.addtime, '' ) 'QRD本次回款时间', IF ( ak.rela_form = 2 || ak.rela_form = 3, ak.cancel_total, '' ) 'QRD本次回款金额', ROUND( b.Number1618249202608, 2 ) 'QRD已开票', ROUND( b.Number1618249205231+b.inving_fee, 2 ) 'QRD未开票', IF ( ak.rela_form = 1, ak.addtime, '' ) 'QRD本次开票时间', IF ( ak.rela_form = 1, ak.cancel_total, '' ) 'QRD本次开票金额', ROUND( c.Number1618330470625, 2 ) 'CGD已付款', ROUND( c.Number1618330472961, 2 ) 'CGD未付款', pc.total_fee 'CGD.本次付款金额', ps.paytime 'CGD.本次付款时间', ROUND( c.Number1618330541286, 2 ) 'CGD已回票', ROUND( c.Number1618330543270, 2 ) 'CGD未回票', ps.inv_fee 'CGD.本次回票金额', ps.invtime 'CGD.本次回票时间', a.delivery_status 'QRD.发货状态', ( SELECT sum( send ) FROM fh_source WHERE a.cgdNo = fh_source.cgdNo ) AS 'CGD总发货数量', f.send 'CGD本次发货', f.fhNo 'CGD发货单号', f.addtime 'CGD本次发货时间' FROM source_all a LEFT JOIN cfp_qrd_info b ON a.productNo = b.sequenceNo LEFT JOIN cfp_cgd_info c ON a.cgdNo = c.ShortText1618315935182 LEFT JOIN fh_source f ON a.cgdNo = f.cgdNo LEFT JOIN cfp_order_info AS oi ON ( oi.goodNo = b.sequenceNo AND oi.STATUS = 1 ) LEFT JOIN cfp_order_pool AS po ON (oi.orderNo = po.orderNo AND po.is_del = 0 ) LEFT JOIN ( SELECT `k`.`id` AS `aid`, `k`.`assocNo` AS `assocNo`, `k`.`companyNo` AS `companyNo`, `k`.`type` AS `type`, `k`.`rela_form` AS `rela_form`, `k`.`codeNo` AS `codeNo`, `k`.`total_fee` AS `total_fee`, `k`.`balance` AS `balance`, `k`.`cancel_total` AS `cancel_total`, `k`.`is_del` AS `is_del`, `k`.`status` AS `status`, `k`.`exam_remark` AS `exam_remark`, `k`.`addtime` AS `addtime`, `t`.`viceNo` AS `viceNo`, `t`.`vice_fee` AS `vice_fee`, `t`.`cancel_fee` AS `cancel_fee`, `t`.`id` AS `id`, `k`.`source` AS `source`, `t`.`vice_total` AS `vice_total`, `k`.`apply_name` AS `apply_name`, `k`.`apply_id` AS `apply_id`, `t`.`status` AS `bstatus` FROM ( `cfp_assoc_key` `k` JOIN `cfp_assoc_rela` `t` ON (( `k`.`assocNo` = `t`.`assocNo` ))) ) AS ak ON ( ( ak.codeNo = oi.orderNo OR ak.viceNo = oi.orderNo ) AND ( rela_form IN ( 1, 2, 3 ) AND ak.STATUS = 3 AND ak.is_del = 0 ) ) left join cfp_pay_info as pc on (pc.sequenceNo = c.sequenceNo and pc.`status`=1) LEFT JOIN cfp_pay_stages as ps on (ps.payNo= pc.payNo and ps.is_del =0) left JOIN cfp_pay as p on (p.payNo=pc.payNo and (p.`status`=2 or p.status=1)) WHERE a.productNo <> '' {$where} order by ordertime"; $list = Db::query($sql); if(empty($list)){ $list=[["未找到数据"=>""]]; } $header = array_keys($list[0]); array_walk($list, function (&$v) { $v = array_values($v); }); excelSave(date("Y-m-d",strtotime($start)).'至'.date("Y-m-d",strtotime($end))."确认单明细", $header, $list); } /** * 采购单明细 * @throws \think\db\exception\BindParamException * @throws \think\exception\PDOException */ public function downreportCGD() { $post = request()->post(); $start = isset($post['start']) && $post['start'] != "" ? $post['start']." 00:00:00" : ''; $end = isset($post['end']) && $post['end'] != "" ? $post['end']." 23:59:59" : ''; $where=''; if($start!=""){ $where.=" and cgdtime >='{$start}'"; } if($end!=""){ $where.=" and cgdtime <='{$end}'"; } $sql = "SELECT b.ShortText1618315935182 AS 'CGD.2采购单号', b.ShortText1618270466672 AS 'CGD.14公司主体', b.Date1618315953443 AS 'CGD.3采购单下单日期', b.ShortText1618270412964 AS 'CGD.1采购单类型', b.StaffSelector1620899427104 AS 'CGD.采购员', b.ShortText1620399144946 AS 'CGD.45供应商名称', b.ShortText1619625566608 AS 'CGD.出库包装库存', b.ShortText1617865688485 AS 'CGD.40税点', ROUND( b.Number1617865810822, 2 ) AS 'CGD.包装费', ROUND( b.Number1617865813284, 2 ) AS 'CGD.证书费', ROUND( b.Number1617865807879, 2 ) AS 'CGD.加标费', ROUND( b.Number1617865816181, 2 ) AS 'CGD.开模费', ROUND( b.number1618240204358, 2 ) AS 'CGD.成本工艺费', ROUND( b.Number1617865804813, 2 ) AS 'CGD.成本裸价', ROUND( b.number1618240287778, 2 ) AS 'CGD.物流费', ROUND( b.Number1619632830397, 2 ) AS 'CGD.工差', ROUND( b.Number1619632826654, 2 ) AS 'CGD.采购工差金额', ROUND( b.Number1617865818517, 2 ) AS 'CGD.采购单价', CAST( b.Number1618240600907 AS SIGNED ) AS 'CGD.采购数量', ROUND( b.Number1618240685904, 2 ) AS 'CGD.采购金额', ROUND( 0, 2 ) AS 'CGD.不含税采购成本-这个怎么计算', b.ShortText1618859161646 AS 'BK.10备库单号', b.Date1618859180209 AS 'BK.11备库下单日期', b.StaffSelector1618885082387 AS 'BK.15业务员', b.ShortText1618900704399 AS 'BK.13备库仓库', b.ShortText1617861966146 AS 'CGD.CP.26产品名称', b.ShortText1617861001482 AS 'CGD.CP.27产品编号', b.ShortText1617865626160 AS 'CGD.CP.28一级分类', a.cgd_cat_s 'CGD.二级分类', a.cgd_cat_t 'CGD.三级分类', a.financial 'CGD.财务核算码', b.ShortText1617865685744 AS 'CGD.CP.25单位', b.ShortText1618240134229 AS 'CGD.CP.36贵金属种类', ROUND( b.Number1618240458074, 2 ) AS 'CGD.CP.38实时金价', ROUND( b.Number1618240480148, 2 ) AS 'CGD.CP.37商品重量（g）', a.is_stock `CGD.是否库存`, b.ShortText1618465932373 `CGD.物流方式`, ( ROUND( ifnull( b.Number1618330470625, 0 ), 2 )+ ( SELECT ifnull( round(sum( pc.wait_fee ),2), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND (p.`status` = 2 or p.`status` = 1) and ps.pay_status=4 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.paytime DESC ) ) AS '已付款', ( ROUND( ifnull( b.Number1618330472961, 0 ), 2 )- ( SELECT ifnull( round(sum( pc.wait_fee ),2), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND (p.`status` = 2 or p.`status` = 1) and ps.pay_status=4 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.paytime DESC ) ) AS '未付款', ( ROUND( ifnull( b.Number1618330541286, 0 ), 2 )+ ( SELECT ifnull( round(sum( pc.winv_fee),2), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND (p.`status` = 2 or p.`status` = 1) and ps.pay_status=7 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.paytime DESC ) ) AS '已回票', ( ROUND( ifnull( b.Number1618330543270, 0 ), 2 )-( SELECT ifnull( round(sum( pc.winv_fee),2), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND (p.`status` = 2 or p.`status` = 1) and ps.pay_status=7 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.invtime DESC ) ) AS '待回票', a.cgd_delivery_status 'CGD.发货状态', pk.total_fee '本次付款金额', pk.ainv_fee '本次回票金额', if(pk.pay_status=4,'已付','待付') '本次付款状态', if(pk.status=7,'已回','待回') '本次回票状态', pk.paytime '本次付款时间', pk.invtime '本次回票时间', ( SELECT sum( send ) FROM fh_source WHERE a.cgdNo = fh_source.cgdNo ) AS 'CGD.总发货数量', f.send 'CGD.本次发货', f.fhNo 'CGD.发货单号', f.addtime 'CGD.本次发货时间' FROM source_all AS a LEFT JOIN cfp_cgd_info AS b ON a.cgdNo = b.ShortText1618315935182 LEFT JOIN fh_source f ON a.cgdNo = f.cgdNo LEFT JOIN (select pc.total_fee,pc.ainv_fee,pc.sequenceNo,ps.pay_status,ps.status,ps.inv_fee,ps.paytime,ps.invtime from cfp_pay_info AS pc inner JOIN cfp_pay_stages AS ps ON (ps.payNo = pc.payNo AND ps.is_del = 0 and (ps.pay_status=4 or ps.pay_status=7)) inner JOIN cfp_pay AS p ON p.payNo = pc.payNo AND (p.`status` = 2 or p.`status` = 1) where pc.STATUS = 1) as pk on pk.sequenceNo =a.cgdjlNo WHERE a.cgdNo <> '' {$where} order by cgdtime"; $list = Db::query($sql); if(empty($list)){ $list=[["未找到数据"=>""]]; } $header = array_keys($list[0]); array_walk($list, function (&$v) { $v = array_values($v); }); excelSave(date("Y-m-d",strtotime($start)).'至'.date("Y-m-d",strtotime($end))."采购单明细", $header, $list); } public function downreportQRDTZ(){ $post = request()->post(); $start = isset($post['start']) && $post['start'] != "" ? $post['start']." 00:00:00" : ''; $end = isset($post['end']) && $post['end'] != "" ? $post['end']." 23:59:59" : ''; $where=''; if($start!=""){ $where.=" and ordertime >='{$start}'"; } if($end!=""){ $where.=" and ordertime <='{$end}'"; } $sql="SELECT '已完成' AS 'QRD.单据状态', DATE_FORMAT(b.Date1617081795606, '%Y年') AS '年', DATE_FORMAT(b.Date1617081795606, '%m月') AS '月', DATE_FORMAT(b.Date1617081795606, '%d日') AS '日', CONCAT( '第', WEEK( DATE_ADD( b.Date1617081795606, INTERVAL 6 DAY ), 2 ), '周' ) AS '周', b.ShortText1618447165317 AS '公司名称', b.ShortText1619523892833 AS 'KH.2客户名称', b.ShortText1619542951283 AS 'KH.3客户编码', b.ShortText1619413394855 AS 'KH.4客户属性', b.ShortText1619413401534 AS 'KH.6分公司', b.ShortText1619413388848 AS 'KH.5大区', b.ShortText1619543001392 AS 'KH.7中支', b.ShortText1619523693355 AS 'KH.8县支', b.ShortText1619523689586 AS 'PT.1平台类型', b.ShortText1617366186330 AS 'PT.2平台编码', b.ShortText1617023369580 AS 'PT.3业管系统咨询单号', b.text1617365646297 AS 'PT.4PO编号', b.ShortText1617650701648 AS 'QRD.4确认单编号', b.ShortText1617650669915 AS 'QRD.3确认单类型', b.Date1617081795606 AS 'QRD.5确认单下单时间', DATE_FORMAT(b.Date1617081795606, '%Y-%m-%d') AS 'TZ.2.8确认单下单日期2', b.StaffSelector1618445844046 As 'QRD.1业务人员', `b`.`sequenceNo` AS `QRD-CP确认单产品编号`, `b`.`ShortText1617365292699` AS `CP.1产品名称`, `b`.`text1617499162303` AS `CP.2产品编码`, `b`.`ShortText1617499192065` AS `CP.3.0一级分类`, `source_all`.`cat_t` AS `CP.C二级分类`, `source_all`.`cat_d` AS `CP.C三级分类`, `source_all`.`fund_code` AS `CP.28财务核算码`, `b`.`text1617365597522` AS `CP.7单位`, `b`.`ShortText1617367958909` AS `CP.8税点`, `b`.`ShortText1619146965777` AS `QRD.6确认单唯一识别码`, round( `b`.`Number1619403905454`, 2 ) AS `CP.26工差重量`, round( `b`.`Number1619403908740`, 2 ) AS `CP.27工差金额`, `b`.`Date1619542686640` AS `QRD.7承诺回款日期`, round( `b`.`Number1618248810624`, 2 ) AS `CP.19销售单价`, cast( `b`.`Number1617365688048` AS signed ) AS `CP.20下单数量`, round( `b`.`Number1618248813613`, 2 ) AS `CP.21销售总额`, ROUND( b.Number1618248813613 - IF( LENGTH(b.ShortText1617367958909) > 1, b.Number1618248813613 * ( CAST( LEFT( b.ShortText1617367958909, LENGTH(b.ShortText1617367958909) - 1 ) AS DECIMAL ) / 100 ), 0 ), 2 ) AS 'CP.NEW不含税收入-查看一下这个计算的是否正确', '-' AS 'CP.NEW毛利润-不知道怎么计算', b.ShortText1617111725596 AS 'ZXD.2咨询单编号', b.Date1619626098213 AS 'ZXD.4咨询日期', b.ShortText1617670912205 AS 'ZXD.3咨询类型', '已完成' AS 'CGD.单据状态', c.ShortText1618315935182 AS 'CGD.2采购单号', c.Date1618315953443 AS 'CGD.3采购单下单日期', c.ShortText1618270412964 AS 'CGD.1采购单类型', c.StaffSelector1620899427104 AS 'CGD.采购员', c.ShortText1620399144946 AS 'CGD.45供应商名称', c.ShortText1617861287265 AS 'CGD.44供应商编号', c.ShortText1619625566608 AS 'CGD.出库包装库存', c.ShortText1617865688485 AS 'CGD.40税点', ROUND(c.Number1617865810822, 2) AS 'CGD.包装费', ROUND(c.Number1617865813284, 2) AS 'CGD.证书费', ROUND(c.Number1617865807879, 2) AS 'CGD.加标费', ROUND(c.Number1617865816181, 2) AS 'CGD.开模费', ROUND(c.number1618240204358, 2) AS 'CGD.成本工艺费', ROUND(c.Number1617865804813, 2) AS 'CGD.成本裸价', ROUND(c.number1618240287778, 2) AS 'CGD.物流费', ROUND(c.Number1619632830397, 2) AS 'CGD.工差', ROUND(c.Number1619632826654, 2) AS 'CGD.采购工差金额', ROUND(c.Number1617865818517, 2) AS 'CGD.采购单价', CAST(c.Number1618240600907 AS SIGNED) AS 'CGD.采购数量', ROUND(c.Number1618240685904, 2) AS 'CGD.采购金额', ROUND(0, 2) AS 'CGD.不含税采购成本-这个怎么计算', ( ROUND( ifnull( c.Number1618330470625, 0 ), 2 )+ ( SELECT ifnull( sum( pc.wait_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.pay_status = 4 AND pc.sequenceNo = c.sequenceNo ORDER BY ps.paytime DESC ) ) AS 'CGD.FK.总-已付款金额', ( ROUND( ifnull( c.Number1618330472961, 0 ), 2 )- ( SELECT ifnull( sum( pc.wait_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.pay_status = 4 AND pc.sequenceNo = c.sequenceNo ORDER BY ps.paytime DESC ) ) AS 'CGD.FK.总-未付款金额', CASE WHEN ( ROUND( ifnull( c.Number1618330470625, 0 ), 2 )+ ( SELECT ifnull( sum( pc.wait_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.pay_status = 4 AND pc.sequenceNo = c.sequenceNo ORDER BY ps.paytime DESC ) ) =0 THEN '未付款' WHEN ( ROUND( ifnull( c.Number1618330472961, 0 ), 2 )- ( SELECT ifnull( sum( pc.wait_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.pay_status = 4 AND pc.sequenceNo = c.sequenceNo ORDER BY ps.paytime DESC ) ) =0 THEN '全部付款' ELSE '部分付款' END AS 'CGD.FK.总-付款情况', ( ROUND( ifnull( c.Number1618330470625, 0 ), 2 )+ ( SELECT ifnull( sum( pc.wait_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.pay_status = 4 AND pc.sequenceNo = c.sequenceNo ORDER BY ps.paytime DESC ) ) AS 'CGD.FK.MX.总-已付款金额', ( SELECT paytime FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.pay_status = 4 AND pc.sequenceNo = c.sequenceNo ORDER BY ps.paytime DESC LIMIT 1 ) AS 'CGD.FK.MX.总-最近一次付款时间', ( ROUND( ifnull( c.Number1618330541286, 0 ), 2 )+ ( SELECT ifnull( sum( pc.winv_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.STATUS = 7 AND pc.sequenceNo = c.sequenceNo ORDER BY ps.paytime DESC ) ) AS 'CGD.HP.总-已回票金额', ( ROUND( ifnull( c.Number1618330543270, 0 ), 2 )-( SELECT ifnull( sum( pc.winv_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.STATUS = 7 AND pc.sequenceNo = c.sequenceNo ORDER BY ps.invtime DESC ) ) AS 'CGD.HP.总-未回票金额', CASE WHEN ( ROUND( ifnull( c.Number1618330541286, 0 ), 2 )+ ( SELECT ifnull( sum( pc.winv_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.STATUS = 7 AND pc.sequenceNo = c.sequenceNo ORDER BY ps.paytime DESC ) )=0 THEN '未回票' WHEN ( ROUND( ifnull( c.Number1618330543270, 0 ), 2 )-( SELECT ifnull( sum( pc.winv_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.STATUS = 7 AND pc.sequenceNo = c.sequenceNo ORDER BY ps.invtime DESC ) ) =0 THEN '全部回票' ELSE '部分回票' END 'CGD.HP.总-回票情况', ( ROUND( ifnull( c.Number1618330543270, 0 ), 2 )-( SELECT ifnull( sum( pc.winv_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.STATUS = 7 AND pc.sequenceNo = c.sequenceNo ORDER BY ps.invtime DESC ) ) AS 'CGD.HP.MX.总-已回票金额', ( SELECT invtime FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.STATUS = 7 AND pc.sequenceNo = c.sequenceNo ORDER BY ps.invtime DESC LIMIT 1 ) AS 'CGD.HP.MX.总-最近一次回票时间', c.ShortText1620753234895 AS 'CGD.回票方式', c.ShortText1620753237335 AS 'CGD.结算方式', c.ShortText1617866144054 AS 'CGD.42库房性质', c.ShortText1618465932373 AS 'CGD.62发货方式', source_all.delivery_num AS 'QRD-CP.FH.总-已发货', source_all.delivery_wnum AS 'QRD-CP.FH.总-未发货', source_all.delivery_status AS 'QRD-CP.FH.总-发货状态', source_all.delivery_send AS 'CGD.FH.总-已发货', source_all.delivery_wsend AS 'CGD.FH.总-未发货', source_all.cgd_delivery_status AS 'CGD.FH.总-发货状态', (SELECT SUM( send ) FROM `fh_source` e_ WHERE e_.cgdNo = source_all.cgdNo) AS 'CGD.FH.MX.总-已发货', (SELECT MAX(e_.addtime) FROM `fh_source` e_ WHERE e_.cgdNo = source_all.cgdNo) AS 'CGD.FH.MX.总-最近一次发货时间', ROUND(b.Number1618249202608, 2) AS 'QRD-CP.KP.总-已开票金额', ROUND(b.Number1618249205231+b.inving_fee, 2) AS 'QRD-CP.KP.总-未开票金额', if(b.ShortText1618559274859=3,'已开','未开') AS 'QRD-CP.KP.总-开票状态', ROUND( (SELECT sum(oi.ainv_fee) FROM cfp_order_info AS oi LEFT JOIN cfp_order_pool AS po ON oi.orderNo = po.orderNo LEFT JOIN cfp_assoc_key AS ak ON ( ak.codeNo = oi.orderNo AND rela_form = 1 ) LEFT JOIN cfp_assoc_rela AS al ON ( ak.assocNo =al.assocNo AND al.`status` = 1 ) WHERE po.is_del = 0 AND oi.STATUS = 1 AND ak.STATUS = 3 AND ak.is_del = 0 and oi.goodNo =b.sequenceNo), 2 ) AS 'QRD-CP.KP.MX.总-已开票金额', (SELECT GROUP_CONCAT( al.viceNo ) FROM cfp_order_info AS oi LEFT JOIN cfp_order_pool AS po ON oi.orderNo = po.orderNo LEFT JOIN cfp_assoc_key AS ak ON ( ak.codeNo = oi.orderNo AND rela_form = 1 ) LEFT JOIN cfp_assoc_rela AS al ON ( ak.assocNo =al.assocNo AND al.`status` = 1 ) WHERE po.is_del = 0 AND oi.STATUS = 1 AND ak.STATUS = 3 AND ak.is_del = 0 and oi.goodNo =b.sequenceNo) AS 'QRD-CP.KP.MX.总-发票号', (SELECT ak.addtime FROM cfp_order_info AS oi LEFT JOIN cfp_order_pool AS po ON oi.orderNo = po.orderNo LEFT JOIN cfp_assoc_key AS ak ON ( ak.codeNo = oi.orderNo AND rela_form = 1 ) WHERE po.is_del = 0 AND oi.STATUS = 1 AND ak.STATUS = 3 AND ak.is_del = 0 and oi.goodNo =b.sequenceNo ORDER BY ak.addtime DESC LIMIT 1) AS 'QRD-CP.KP.MX.最新开票时间', ROUND(b.Number1618249146997, 2) AS 'QRD-CP.HK.总-已回款金额', ROUND(b.number1618249149738+b.paying_fee, 2) AS 'QRD-CP.HK.总-未回款金额', if(b.ShortText1618559043560=3,'已回','未回') AS 'QRD-CP.HK.总-回款状态', (SELECT sum(oi.afund_fee) FROM cfp_order_info AS oi LEFT JOIN cfp_order_pool AS po ON oi.orderNo = po.orderNo LEFT JOIN cfp_assoc_key AS ak ON ( ak.codeNo = oi.orderNo AND (rela_form = 2 or rela_form = 3)) WHERE po.is_del = 0 AND oi.STATUS = 1 AND ak.STATUS = 3 AND ak.is_del = 0 and oi.goodNo =b.sequenceNo ORDER BY ak.addtime) as 'QRD-CP.HK.MX.总-已回款金额', (SELECT ak.addtime FROM cfp_order_info AS oi LEFT JOIN cfp_order_pool AS po ON oi.orderNo = po.orderNo LEFT JOIN cfp_assoc_key AS ak ON ( ak.codeNo = oi.orderNo AND (rela_form = 2 or rela_form = 3)) WHERE po.is_del = 0 AND oi.STATUS = 1 AND ak.STATUS = 3 AND ak.is_del = 0 and oi.goodNo =b.sequenceNo ORDER BY ak.addtime DESC LIMIT 1) as 'QRD-CP.HK.MX.最新回款时间' FROM source_all left JOIN cfp_qrd_info b on source_all.productNo = b.sequenceNo LEFT JOIN cfp_cgd_info c on source_all.cgdNo = c.ShortText1618315935182 where source_all.productNo<>'' {$where} order by ordertime"; $list = Db::query($sql); if(empty($list)){ $list=[["未找到数据"=>""]]; } $header = array_keys($list[0]); array_walk($list, function (&$v) { $v = array_values($v); }); excelSave(date("Y-m-d",strtotime($start)).'至'.date("Y-m-d",strtotime($end))."确认单台账", $header, $list); } public function downreportCGDTZ(){ $post = request()->post(); $start = isset($post['start']) && $post['start'] != "" ? $post['start']." 00:00:00" : ''; $end = isset($post['end']) && $post['end'] != "" ? $post['end']." 23:59:59" : ''; $where=''; if($start!=""){ $where.=" and cgdtime >='{$start}'"; } if($end!=""){ $where.=" and cgdtime <='{$end}'"; } $sql="SELECT b.ShortText1618315935182 AS 'CGD.2采购单号', b.ShortText1618270466672 AS 'CGD.14公司主体', b.Date1618315953443 AS 'CGD.3采购单下单日期', b.ShortText1618270412964 AS 'CGD.1采购单类型', b.StaffSelector1620899427104 AS 'CGD.采购员', b.ShortText1620399144946 AS 'CGD.45供应商名称', b.ShortText1617861287265 AS 'CGD.44供应商编号', b.ShortText1619625566608 AS 'CGD.出库包装库存', b.ShortText1617865688485 AS 'CGD.40税点', ROUND( b.Number1617865810822, 2 ) AS 'CGD.包装费', ROUND( b.Number1617865813284, 2 ) AS 'CGD.证书费', ROUND( b.Number1617865807879, 2 ) AS 'CGD.加标费', ROUND( b.Number1617865816181, 2 ) AS 'CGD.开模费', ROUND( b.number1618240204358, 2 ) AS 'CGD.成本工艺费', ROUND( b.Number1617865804813, 2 ) AS 'CGD.成本裸价', ROUND( b.number1618240287778, 2 ) AS 'CGD.物流费', ROUND( b.Number1619632830397, 2 ) AS 'CGD.工差', ROUND( b.Number1619632826654, 2 ) AS 'CGD.采购工差金额', ROUND( b.Number1617865818517, 2 ) AS 'CGD.采购单价', CAST( b.Number1618240600907 AS SIGNED ) AS 'CGD.采购数量', ROUND( b.Number1618240685904, 2 ) AS 'CGD.采购金额', ROUND( 0, 2 ) AS 'CGD.不含税采购成本-这个怎么计算', b.ShortText1617866362204 AS 'QRD-CP.4确认单类型', b.ShortText1617866360004 AS 'QRD-CP.5确认单编号', b.Date1618324547052 AS 'QRD-CP.6确认单下单日期', b.ShortText1618239976714 AS 'QRD.ZX.7咨询单编号', b.ShortText1617866364821 AS 'QRD.ZX.8咨询单类型', b.Date1619624209148 AS 'QRD.ZX.9咨询单下单时间', b.ShortText1618859161646 AS 'BK.10备库单号', b.Date1618859180209 AS 'BK.11备库下单日期', b.StaffSelector1618885082387 AS 'BK.15业务员', b.ShortText1618900704399 AS 'BK.13备库仓库', b.ShortText1617861966146 AS 'CGD.CP.26产品名称', b.ShortText1617861001482 AS 'CGD.CP.27产品编号', b.ShortText1617865626160 AS 'CGD.CP.28一级分类', a.cgd_cat_s AS 'CGD.CP.C二级分类', a.cgd_cat_t AS 'CGD.CP.C三级分类', a.financial AS 'CGD.CP.C财务核算编码', b.ShortText1617865685744 AS 'CGD.CP.25单位', b.ShortText1618240134229 AS 'CGD.CP.36贵金属种类', ROUND( b.Number1618240458074, 2 ) AS 'CGD.CP.38实时金价', ROUND( b.Number1618240480148, 2 ) AS 'CGD.CP.37商品重量（g）', ( ROUND( ifnull( b.Number1618330470625, 0 ), 2 )+ ( SELECT ifnull( sum( pc.wait_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.pay_status = 4 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.paytime DESC ) ) AS 'CGD.FK.总-已付款金额', ( ROUND( ifnull( b.Number1618330472961, 0 ), 2 )-( SELECT ifnull( sum( pc.wait_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.pay_status = 4 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.paytime DESC ) ) AS 'CGD.FK.总-未付款金额', CASE WHEN ( ROUND( ifnull( b.Number1618330470625, 0 ), 2 )+ ( SELECT ifnull( sum( pc.wait_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.pay_status = 4 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.paytime DESC ) ) = 0 THEN '未付款' WHEN ( ROUND( ifnull( b.Number1618330472961, 0 ), 2 )- ( SELECT ifnull( sum( pc.wait_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.pay_status = 4 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.paytime DESC ) ) = 0 THEN '全部付款' ELSE '部分付款' END AS 'CGD.FK.总-付款情况', ( round( ifnull( b.Number1618330470625, 0 ), 2 )+ ( SELECT ifnull( sum( pc.wait_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.pay_status = 4 AND pc.sequenceNo = b.sequenceNo ) ) AS 'CGD.FK.MX.总-已付款金额', ( SELECT paytime FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.pay_status = 4 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.paytime DESC LIMIT 1 ) AS 'CGD.HP.MX.总最近一次付款时间', ROUND( ROUND( ifnull( b.Number1618330541286, 0 ), 2 )+ ( SELECT ifnull( sum( pc.winv_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.STATUS = 7 AND pc.sequenceNo = b.sequenceNo ), 2 ) AS 'CGD.HP.总-已回票金额', ROUND( ifnull( b.Number1618330543270, 0 )-( SELECT ifnull( sum( pc.winv_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.STATUS = 7 AND pc.sequenceNo = b.sequenceNo ), 2 ) AS 'CGD.HP.总-未回票金额', CASE WHEN ( ROUND( ifnull( b.Number1618330541286, 0 ), 2 )+ ( SELECT ifnull( sum( pc.winv_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.STATUS = 7 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.paytime DESC ) )= 0 THEN '未回票' WHEN ( ROUND( ifnull( b.Number1618330543270, 0 ), 2 )-( SELECT ifnull( sum( pc.winv_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.STATUS = 7 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.invtime DESC LIMIT 1 ) ) = 0 THEN '全部回票' ELSE '部分回票' END AS 'CGD.HP.总-回票情况', ROUND( ROUND( ifnull( b.Number1618330541286, 0 ), 2 )+ ifnull(( SELECT ifnull( sum( pc.winv_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.STATUS = 7 AND pc.sequenceNo = b.sequenceNo ), 0 ), 2 ) AS 'CGD.HP.MX.总-已回票金额', ( SELECT ps.invtime FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.STATUS = 7 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.invtime DESC LIMIT 1 ) AS 'CGD.HP.MX.总最近一次回票时间', CAST( a.delivery_send AS SIGNED ) AS 'CGD.FH.总-已发货', CAST( a.delivery_wsend AS SIGNED ) AS 'CGD.FH.总-未发货', a.cgd_delivery_status AS 'CGD.FH.总-发货状态', ( SELECT sum( send ) FROM fh_source WHERE a.cgdNo = fh_source.cgdNo ) AS 'CGD.FH.MX.总-已发货', ( SELECT max( addtime ) FROM fh_source WHERE a.cgdNo = fh_source.cgdNo ) AS 'CGD.FH.MX.总-最近一次发货时间' FROM source_all a LEFT JOIN cfp_cgd_info b ON a.cgdNo = b.ShortText1618315935182 WHERE a.cgdNo <> '' {$where} order by cgdtime"; $list = Db::query($sql); if(empty($list)){ $list=[["未找到数据"=>""]]; } $header = array_keys($list[0]); array_walk($list, function (&$v) { $v = array_values($v); }); excelSave(date("Y-m-d",strtotime($start)).'至'.date("Y-m-d",strtotime($end))."采购单台账", $header, $list); } /**退货台账、明细一样的 * @throws \think\db\exception\BindParamException * @throws \think\exception\PDOException */ public function downreportTHTZ(){ $post = request()->post(); $start = isset($post['start']) && $post['start'] != "" ? $post['start']." 00:00:00" : ''; $end = isset($post['end']) && $post['end'] != "" ? $post['end']." 23:59:59" : ''; $where=''; if($start!=""){ $where.=" and t.createtime >='{$start}'"; } if($end!=""){ $where.=" and t.createtime <='{$end}'"; } $sql="select t.thNo `退货单号`, case t.th_status WHEN 1 THEN '已完成' WHEN 2 THEN '进行中' WHEN 3 THEN '已取消' else '' end AS `流程进度`, t.createtime `退货发起日期`, t.th_company '公司名称', t.th_user '退货人', t.th_num '25退货数量', t.th_qrd_fee '26退货销售货款', t.th_cgd_fee '42退货采购货款', IF (( t.`is_th` = '1' ), '是', '否' ) '50供应商是否可以退货', t.th_remark '27退货备注', '' as '52退货备注', t.th_post_company '退货物流信息-53物流公司', t.th_post_code '退货物流信息-54物流单编号', `t`.`th_post_fee` AS `退货物流信息-55物流费用`, `t`.`th_receiver` AS `退货物流信息-56收货人`, `t`.`th_phone` AS `退货物流信息-57收货人电话`, '' AS `退货物流信息-58收货人电话2`, `t`.`th_addr` AS `退货物流信息-59收货人地址`, companyName AS `KH.2客户名称`, companyNo AS `KH.3客户编码`, khzzxz AS `KH.4客户属性`, khcomp AS `KH.6分公司`, '' AS `KH.5大区`, middle_branch AS `KH.7中支`, area_branch AS `KH.8县支`, suppitem AS `PT.1平台类型`, itemcode AS `PT.2平台编码`, workNo AS `PT.3业管系统咨询单号`, poNo AS `PT.4PO编号`, source_all.qrdNo AS `QRD.4确认单编号`, qrd_type AS `QRD.3确认单类型`, ordertime AS `QRD.5确认单下单时间`, date_format( ordertime, '%Y-%m-%d' ) AS `TZ.2.8确认单下单日期2`, sale_name AS `QRD.1业务人员`, productNo AS `QRD-CP确认单产品编号`, product_name AS `CP.1产品名称`, product_code AS `CP.2产品编码`, cat_f AS `CP.3.0一级分类`, cat_t AS `CP.C二级分类`, cat_d AS `CP.C三级分类`, fund_code AS `CP.28财务核算码`, unit AS `CP.7单位`, tax AS `CP.8税点`, qrdkey AS `QRD.6确认单唯一识别码`, round( diff_weight, 2 ) AS `CP.26工差重量`, round( diff_price, 2 ) AS `CP.27工差金额`, paytime AS `QRD.7承诺回款日期`, round( sale_price, 2 ) AS `CP.19销售单价`, cast( order_num AS signed ) AS `CP.20下单数量`, round( sale_total, 2 ) AS `CP.21销售总额`, income as `CP.NEW不含税收入-查看一下这个计算的是否正确`, '-' AS `CP.NEW毛利润-不知道怎么计算`, zxcode AS `ZXD.2咨询单编号`, zxfktime AS `ZXD.4咨询日期`, zxtype AS `ZXD.3咨询类型`, cgdNo AS `CGD.2采购单号`, cgdtime AS `CGD.3采购单下单日期`, cgdtype AS `CGD.1采购单类型`, cgd_saler AS `CGD.采购员`, supplier AS `CGD.45供应商名称`, suppierNo AS `CGD.44供应商编号`, paking AS `CGD.出库包装库存`, cgd_tax AS `CGD.40税点`, round( pakage_fee, 2 ) AS `CGD.包装费`, round( cert_fee, 2 ) AS `CGD.证书费`, round( mark_fee, 2 ) AS `CGD.加标费`, round(open_fee, 2 ) AS `CGD.开模费`, round( cost_fee, 2 ) AS `CGD.成本工艺费`, round( naked_fee, 2 ) AS `CGD.成本裸价`, round( delivery_fee, 2 ) AS `CGD.物流费`, round( cgd_diff_weight, 2 ) AS `CGD.工差`, round( diff_fee, 2 ) AS `CGD.采购工差金额`, round(cgd_price, 2 ) AS `CGD.采购单价`, cast( cgd_num AS signed ) AS `CGD.采购数量`, round(cgd_total, 2 ) AS `CGD.采购金额`, round( 0, 2 ) AS `CGD.不含税采购成本-这个怎么计算`, (ROUND( ifnull( c.Number1618330470625, 0 ), 2 )+ ( SELECT ifnull( round(sum( pc.wait_fee ),2), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND (p.`status` = 2 or p.`status` = 1) and ps.pay_status=4 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.paytime DESC )) AS `CGD.FK.总-已付款金额`, (ROUND( ifnull( c.Number1618330472961, 0 ), 2 )- ( SELECT ifnull( round(sum( pc.wait_fee ),2), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND (p.`status` = 2 or p.`status` = 1) and ps.pay_status=4 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.paytime DESC ) ) AS `CGD.FK.总-未付款金额`, CASE WHEN ( ROUND( ifnull( c.Number1618330470625, 0 ), 2 )+ ( SELECT ifnull( sum( pc.wait_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.pay_status = 4 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.paytime DESC ) ) = 0 THEN '未付款' WHEN ( ROUND( ifnull( c.Number1618330472961, 0 ), 2 )- ( SELECT ifnull( sum( pc.wait_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.pay_status = 4 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.paytime DESC ) ) = 0 THEN '全部付款' ELSE '部分付款' END AS 'CGD.FK.总-付款情况', (ROUND( ifnull( c.Number1618330541286, 0 ), 2 )+ ( SELECT ifnull( sum( pc.winv_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.STATUS = 7 AND pc.sequenceNo = b.sequenceNo GROUP BY pc.sequenceNo ) ) AS 'CGD.HP.总-已回票金额', ROUND( ifnull( c.Number1618330543270, 0 ) -( SELECT ifnull( sum( pc.winv_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.STATUS = 7 AND pc.sequenceNo = b.sequenceNo GROUP BY pc.sequenceNo ), 2 ) AS 'CGD.HP.总-未回票金额', CASE WHEN ( ROUND( ifnull( c.Number1618330541286, 0 ), 2 )+ ( SELECT ifnull( sum( pc.winv_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.STATUS = 7 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.paytime DESC ) )= 0 THEN '未回票' WHEN ( ROUND( ifnull( c.Number1618330543270, 0 ), 2 )-( SELECT ifnull( sum( pc.winv_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND ( p.`status` = 2 OR p.STATUS = 1 ) AND ps.STATUS = 7 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.invtime DESC limit 1 ) ) = 0 THEN '全部回票' ELSE '部分回票' END AS 'CGD.HP.总-回票情况', '' AS `CGD.HP.MX.总-已回票金额`, return_ticket AS `CGD.回票方式`, return_trade AS `CGD.结算方式`, c.ShortText1617866144054 AS `CGD.42库房性质`, `c`.`ShortText1618465932373` AS `CGD.62发货方式`, cast( `b`.`Number1618249015661` AS signed ) AS `QRD-CP.FH.总-已发货`, cast( `b`.`Number1618249019294` AS signed ) AS `QRD-CP.FH.总-未发货`, `b`.`ShortText1618559007040` AS `QRD-CP.FH.总-发货状态`, cast( `c`.`Number1618316157066` AS signed ) AS `CGD.FH.总-已发货`, cast( `c`.`Number1618316171848` AS signed ) AS `CGD.FH.总-未发货`, if(`c`.`ShortText1618859321070`=1,'未发',if(`c`.`ShortText1618859321070`=3,'已发','部分')) AS `CGD.FH.总-发货状态`, ( SELECT sum( send ) FROM fh_source WHERE source_all.cgdNo = fh_source.cgdNo ) AS `CGD.FH.MX.总-已发货`, round( `b`.`Number1618249202608`, 2 ) AS `QRD-CP.KP.总-已开票金额`, round( `b`.`Number1618249205231`+b.inving_fee, 2 ) AS `QRD-CP.KP.总-未开票金额`, if(`b`.`ShortText1618559274859`=1,'未开',if(`b`.`ShortText1618559274859`=3,'已开','部分')) AS `QRD-CP.KP.总-开票状态`, '' AS `QRD-CP.KP.MX.总-已开票金额`, round( `b`.`Number1618249146997`, 2 ) AS `QRD-CP.HK.总-已回款金额`, round( `b`.`number1618249149738`+b.paying_fee, 2 ) AS `QRD-CP.HK.总-未回款金额`, if(`b`.`ShortText1618559043560`=1,'未回',if(`b`.`ShortText1618559043560`=3,'已回','部分')) AS `QRD-CP.HK.总-回款状态`, '' AS `QRD-CP.HK.MX.总-已回款金额` from cfp_th_source as t left join source_all on ((source_all.productNo=t.th_qrdcpNo and t.th_qrdcpNo<>'') or (t.th_cgdNo=source_all.cgdNo and t.th_cgdNo<>'')) left JOIN cfp_qrd_info b on t.th_qrdcpNo = b.sequenceNo LEFT JOIN cfp_cgd_info c on t.th_cgdNo = c.ShortText1618315935182 where t.th_status<>3 {$where} order by t.createtime"; $list = Db::query($sql); if(empty($list)){ $list=[["未找到数据"=>""]]; } $header = array_keys($list[0]); array_walk($list, function (&$v) { $v = array_values($v); }); excelSave(date("Y-m-d",strtotime($start)).'至'.date("Y-m-d",strtotime($end))."退货台账", $header, $list); } /** * @throws \think\db\exception\BindParamException * @throws \think\exception\PDOException */ public function downreportCW(){ $post = request()->post(); $condition=""; $qrd_start = isset($post['qrd_start']) && $post['qrd_start'] != "" ? $post['qrd_start'] :"" ; if($qrd_start!=""){ $condition .=" and ordertime>='". $qrd_start." 00:00:00'"; } $qrd_end = isset($post['qrd_end']) && $post['qrd_end'] != "" ? $post['qrd_end'] :"" ; if($qrd_end!=""){ $condition .=" and ordertime <='". $qrd_end." 23:59:59'"; } $hk_start = isset($post['hk_start']) && $post['hk_start'] != "" ? $post['hk_start'] :"" ; $hk_end = isset($post['hk_end']) && $post['hk_end'] != "" ? $post['hk_end'] :"" ; if($hk_start!=""){ $condition .=" and addtime >='". $hk_start." 00:00:00'"; } if($hk_end!=""){ $condition .=" and addtime <='". $hk_end." 23:59:59'"; } if($qrd_start==""&&$hk_start==""){ $condition .=" and ordertime>='". date("Y-m-d")." 00:00:00'"; } $sql="SELECT supperinfo '公司名称', sale_name '销售人员', depart '部门', qrd_type '订单类型', qrdNo '确认单编号', cgdNo '采购单单号' , khzzxz '客户属性', khcomp '客户分公司', companyName '客户名称', product_name '产品名称', cat_f '一级分类', sale_price '销售单价', order_num '下单数量', sale_total '销售总额', ordertime '确认单下单时间', addtime '回款日期', fund_fee '回款金额', pay_day '账期' FROM cfp_cw_report WHERE 1=1 {$condition} order by ordertime"; $list = Db::query($sql); if(empty($list)){ $list=[["未找到数据"=>""]]; } $header = array_keys($list[0]); array_walk($list, function (&$v) { $v = array_values($v); }); excelSave("财务台账", $header, $list); } public function downreportCWTZ(){ $post = request()->post(); $start = isset($post['start']) && $post['start'] != "" ? $post['start']." 00:00:00" : ''; $end = isset($post['end']) && $post['end'] != "" ? $post['end']." 23:59:59" : ''; $where=''; if($start!=""){ $where.=" and ordertime >='{$start}'"; } if($end!=""){ $where.=" and ordertime <='{$end}'"; } $sql="SELECT DATE_FORMAT( ordertime, '%Y' ) '年', DATE_FORMAT( ordertime, '%m' ) '月', DATE_FORMAT( ordertime, '%d' ) '日', productNo 'QRD确认单产品编号', supperinfo 'QRD公司名称', companyName 'QRD客户名称', suppitem 'QRD平台', khzzxz 'QRD客户属性', khcomp 'QRD客户分公司', poNo 'PT.4PO编号', zxtype 'QRD线上线下', source_all.qrdNo 'QRD.4确认单编号', qrd_type 'QRD.确认单类型', ordertime 'QRD.确认单下单时间', sale_name 'QRD.业务员', product_name 'QRD.产品名称', cat_f 'QRD.一级分类', tax 'QRD.税率', sale_price 'QRD.销售单价', order_num 'QRD.下单数量', sale_total 'QRD.销售总额', source_all.cgdNo 'CGD采购单单号', ROUND( b.Number1618249146997, 2 ) 'QRD已回款', ROUND( b.number1618249149738, 2 ) 'QRD未回款', ROUND( b.paying_fee, 2 ) 'QRD回款进行中', ROUND( b.Number1618249202608, 2 ) 'QRD已开票', ROUND( b.Number1618249205231, 2 ) 'QRD未开票', ROUND( b.inving_fee, 2 ) 'QRD开票进行中', CASE b.ShortText1618559043560 WHEN 1 THEN '未回款' WHEN 2 THEN '部分回款' WHEN 3 THEN '全部回款' ELSE '' END 'QRD.回款状态', CASE b.ShortText1618559274859 WHEN 1 THEN '未开票' WHEN 2 THEN '部分开票' WHEN 3 THEN '全部开票' ELSE '' END 'QRD.开票状态', ( SELECT ak.addtime FROM cfp_order_info AS oi LEFT JOIN cfp_order_pool AS po ON oi.orderNo = po.orderNo LEFT JOIN cfp_assoc_list AS ak ON ( ak.codeNo = oi.orderNo AND rela_form = 1 ) WHERE po.is_del = 0 AND oi.STATUS = 1 AND ak.STATUS = 3 AND ak.is_del = 0 and oi.qrdNo = source_all.qrdNo ORDER BY ak.addtime DESC LIMIT 1 ) AS 'QRD.最近开票时间', ( SELECT ak.addtime FROM cfp_order_info AS oi LEFT JOIN cfp_order_pool AS po ON oi.orderNo = po.orderNo LEFT JOIN cfp_assoc_list AS ak ON ( ak.codeNo = oi.orderNo AND ( rela_form = 2 OR rela_form = 3 )) WHERE po.is_del = 0 AND oi.STATUS = 1 AND ak.STATUS = 3 AND ak.is_del = 0 and oi.qrdNo = source_all.qrdNo ORDER BY ak.addtime DESC LIMIT 1 ) AS 'QRD.最近回款时间', if(ISNULL( ( SELECT ak.addtime FROM cfp_order_info AS oi LEFT JOIN cfp_order_pool AS po ON oi.orderNo = po.orderNo LEFT JOIN cfp_assoc_list AS ak ON ( ak.codeNo = oi.orderNo AND ( rela_form = 2 OR rela_form = 3 )) WHERE po.is_del = 0 AND oi.STATUS = 1 AND ak.STATUS = 3 AND ak.is_del = 0 and oi.qrdNo = source_all.qrdNo ORDER BY ak.addtime DESC LIMIT 1 ) ),ceil(DATEDIFF(NOW(),ordertime)),ceil(DATEDIFF(( SELECT ak.addtime FROM cfp_order_info AS oi LEFT JOIN cfp_order_pool AS po ON oi.orderNo = po.orderNo LEFT JOIN cfp_assoc_list AS ak ON ( ak.codeNo = oi.orderNo AND ( rela_form = 2 OR rela_form = 3 )) WHERE po.is_del = 0 AND oi.STATUS = 1 AND ak.STATUS = 3 AND ak.is_del = 0 and oi.qrdNo = source_all.qrdNo ORDER BY ak.addtime DESC LIMIT 1 ),ordertime))) as '账期', if(ISNULL( ( SELECT ak.addtime FROM cfp_order_info AS oi LEFT JOIN cfp_order_pool AS po ON oi.orderNo = po.orderNo LEFT JOIN cfp_assoc_list AS ak ON ( ak.codeNo = oi.orderNo AND ( rela_form = 2 OR rela_form = 3 )) WHERE po.is_del = 0 AND oi.STATUS = 1 AND ak.STATUS = 3 AND ak.is_del = 0 and oi.qrdNo = source_all.qrdNo ORDER BY ak.addtime DESC LIMIT 1 ) ),if(ceil(DATEDIFF(NOW(),ordertime)/30)>6,'是','否'),if(ceil(DATEDIFF(( SELECT ak.addtime FROM cfp_order_info AS oi LEFT JOIN cfp_order_pool AS po ON oi.orderNo = po.orderNo LEFT JOIN cfp_assoc_list AS ak ON ( ak.codeNo = oi.orderNo AND ( rela_form = 2 OR rela_form = 3 )) WHERE po.is_del = 0 AND oi.STATUS = 1 AND ak.STATUS = 3 AND ak.is_del = 0 and oi.qrdNo = source_all.qrdNo ORDER BY ak.addtime DESC LIMIT 1 ),ordertime)/30)>6,'是','否')) as '账期是否超期', IF ( DATEDIFF( NOW(), ordertime )> 60, '是', '否' ) AS '下单是否超期', IF ( ISNULL(( SELECT ak.addtime FROM cfp_order_info AS oi LEFT JOIN cfp_order_pool AS po ON oi.orderNo = po.orderNo LEFT JOIN cfp_assoc_list AS ak ON ( ak.codeNo = oi.orderNo AND rela_form = 1 ) WHERE po.is_del = 0 AND oi.STATUS = 1 AND ak.STATUS = 3 AND ak.is_del = 0 and oi.qrdNo = source_all.qrdNo ORDER BY ak.addtime DESC LIMIT 1 ) ), if(DATEDIFF(( SELECT ak.addtime FROM cfp_order_info AS oi LEFT JOIN cfp_order_pool AS po ON oi.orderNo = po.orderNo LEFT JOIN cfp_assoc_list AS ak ON ( ak.codeNo = oi.orderNo AND rela_form = 1 ) WHERE po.is_del = 0 AND oi.STATUS = 1 AND ak.STATUS = 3 AND ak.is_del = 0 and oi.qrdNo = source_all.qrdNo ORDER BY ak.addtime DESC LIMIT 1 ), ordertime )>15,'是','否'), if(DATEDIFF( NOW(), ordertime )>15,'是','否')) AS '开票是否超期', delivery_status 'QRD.发货状态', ( SELECT sum( send ) FROM fh_source WHERE source_all.qrdNo = fh_source.qrdNo ) AS 'qrd.总发货数量', ( SELECT max( addtime ) FROM fh_source WHERE source_all.qrdNo = fh_source.qrdNo ) AS 'qrd.最近一次发货时间' FROM source_all LEFT JOIN cfp_qrd_info b ON source_all.productNo = b.sequenceNo LEFT JOIN cfp_cgd_info c ON source_all.cgdNo = c.ShortText1618315935182 WHERE source_all.productNo <> '' {$where} ORDER BY ordertime DESC "; $list = Db::query($sql); if(empty($list)){ $list=[["未找到数据"=>""]]; } $header = array_keys($list[0]); array_walk($list, function (&$v) { $v = array_values($v); }); excelSave(date("Y-m-d",strtotime($start)).'至'.date("Y-m-d",strtotime($end))."应收台账", $header, $list); } /** * @throws \think\db\exception\BindParamException * @throws \think\exception\PDOException */ public function downQRD(){ $post = request()->post(); $start = isset($post['start']) && $post['start'] != "" ? $post['start']." 00:00:00" : ''; $end = isset($post['end']) && $post['end'] != "" ? $post['end']." 23:59:59" : ''; $where=''; if($start!=""){ $where.=" and ordertime >='{$start}'"; } if($end!=""){ $where.=" and ordertime <='{$end}'"; } $sql = "SELECT DATE_FORMAT(ordertime,'%Y') '年', DATE_FORMAT(ordertime,'%m') '月', DATE_FORMAT(ordertime,'%d') '日', a.supperinfo 'QRD公司名称', a.zxtype 'QRD.咨询单类型', a.qrdNo 'QRD.4确认单编号', a.sale_name 'QRD.业务员', a.suppitem 'QRD平台', a.khzzxz 'QRD客户属性', a.khcomp 'QRD客户分公司', a.product_code 'QRD.产品编号', a.cat_f 'QRD.一级分类', a.fund_code 'QRD.财务核算码', a.product_name 'QRD.产品名称', a.unit 'QRD.单位', a.sale_price 'QRD.销售单价', a.order_num 'QRD.下单数量', a.sale_total 'QRD.销售总额', ROUND( b.Number1618249202608, 2 ) 'QRD已开票', ROUND( b.Number1618249205231+b.inving_fee, 2 ) 'QRD未开票', IF ( ak.rela_form = 1, ak.addtime, '' ) 'QRD本次开票时间', IF ( ak.rela_form = 1, ak.cancel_total, '' ) 'QRD本次开票金额', if(b.ShortText1618559274859=1,'未开票',if(b.ShortText1618559274859=3,'已开票','部分开票')) 'QRD开票完成状态', a.cgdNo 'CGD采购单单号', pl.invNo '确认单发票申请编号', (select ci.company_name from cfp_company_info ci where ci.companyNo=pl.inv_out) '发票开具全称', li.inv_number '发票号码' FROM source_all a LEFT JOIN cfp_qrd_info b ON a.productNo = b.sequenceNo LEFT JOIN cfp_cgd_info c ON a.cgdNo = c.ShortText1618315935182 LEFT JOIN fh_source f ON a.cgdNo = f.cgdNo LEFT JOIN cfp_order_info AS oi ON ( oi.goodNo = b.sequenceNo AND oi.STATUS = 1 ) LEFT JOIN cfp_order_pool AS po ON (oi.orderNo = po.orderNo AND po.is_del = 0 ) LEFT JOIN ( SELECT `k`.`id` AS `aid`, `k`.`assocNo` AS `assocNo`, `k`.`companyNo` AS `companyNo`, `k`.`type` AS `type`, `k`.`rela_form` AS `rela_form`, `k`.`codeNo` AS `codeNo`, `k`.`total_fee` AS `total_fee`, `k`.`balance` AS `balance`, `k`.`cancel_total` AS `cancel_total`, `k`.`is_del` AS `is_del`, `k`.`status` AS `status`, `k`.`exam_remark` AS `exam_remark`, `k`.`addtime` AS `addtime`, `t`.`viceNo` AS `viceNo`, `t`.`vice_fee` AS `vice_fee`, `t`.`cancel_fee` AS `cancel_fee`, `t`.`id` AS `id`, `k`.`source` AS `source`, `t`.`vice_total` AS `vice_total`, `k`.`apply_name` AS `apply_name`, `k`.`apply_id` AS `apply_id`, `t`.`status` AS `bstatus` FROM ( `cfp_assoc_key` `k` JOIN `cfp_assoc_rela` `t` ON (( `k`.`assocNo` = `t`.`assocNo` ))) ) AS ak ON ( ( ak.codeNo = oi.orderNo OR ak.viceNo = oi.orderNo ) AND ( rela_form IN ( 1, 2, 3 ) AND ak.STATUS = 3 AND ak.is_del = 0 ) ) left join cfp_pay_info as pc on (pc.sequenceNo = c.sequenceNo and pc.`status`=1) LEFT JOIN cfp_pay_stages as ps on (ps.payNo= pc.payNo and ps.is_del =0) left JOIN cfp_pay as p on (p.payNo=pc.payNo and (p.`status`=2 or p.status=1)) LEFT JOIN cfp_invoice_pool as pl on (ak.viceNo = pl.invNo and ak.rela_form=1) LEFT JOIN cfp_invoice_list as li on (pl.invNo =li.invNo and li.`status`=1 and li.is_del=0) WHERE a.productNo <> '' {$where} order by ordertime"; $list = Db::query($sql); if(empty($list)){ $list=[["未找到数据"=>""]]; } $header = array_keys($list[0]); array_walk($list, function (&$v) { $v = array_values($v); }); excelSave(date("Y-m-d",strtotime($start)).'至'.date("Y-m-d",strtotime($end))."确认单核算", $header, $list); } public function downCGD() { $post = request()->post(); $start = isset($post['start']) && $post['start'] != "" ? $post['start']." 00:00:00" : ''; $end = isset($post['end']) && $post['end'] != "" ? $post['end']." 23:59:59" : ''; $where=''; if($start!=""){ $where.=" and cgdtime >='{$start}'"; } if($end!=""){ $where.=" and cgdtime <='{$end}'"; } $sql = "SELECT a.cgd_saler 'CGD.采购员', a.cgdtype 'CGD.采购单类型', a.cgdNo 'CGD.采购单单号', a.bkNo 'CGD.备库单号', ifnull( `a`.`pakage_fee`, 0 ) AS `CGD.包装费`, ifnull( `a`.`cert_fee`, 0 ) AS `CGD.证书费`, ifnull( `a`.`mark_fee`, 0 ) AS `CGD.加标费`, ifnull( `a`.`open_fee`, 0 ) AS `CGD.开模费`, ifnull( `a`.`cost_fee`, 0 ) AS `CGD.成本工艺费`, ifnull( `a`.`naked_fee`, '' ) AS `CGD.成本裸价`, ifnull( `a`.`delivery_fee`, 0 ) AS `CGD.物流费`, ifnull( `a`.`cgd_price`, '' ) AS `CGD.采购单价`, cast( `a`.`cgd_num` AS signed ) AS `CGD.采购数量`, `a`.`cgd_total` AS `CGD.采购金额`, '' as '毛利率', a.is_stock `CGD.是否库存`, a.paking 'CGD.出库包装库存', a.cgd_tax 'CGD.采购单税率', a.supplier 'CGD.供货商', a.delivery_type `CGD.物流方式`, ( ROUND( ifnull( b.Number1618330470625, 0 ), 2 )+ ( SELECT ifnull( round(sum( pc.wait_fee ),2), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND p.`status` = 2 and ps.pay_status=4 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.paytime DESC ) ) AS '已付款', ( ROUND( ifnull( b.Number1618330472961, 0 ), 2 )- ( SELECT ifnull( round(sum( pc.wait_fee ),2), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND p.`status` = 2 and ps.pay_status=4 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.paytime DESC ) ) AS '未付款', ( ROUND( ifnull( b.Number1618330541286, 0 ), 2 )+ ( SELECT ifnull( sum( pc.winv_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND p.`status` = 2 and ps.status=7 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.paytime DESC ) ) AS '已回票', ( ROUND( ifnull( b.Number1618330543270, 0 ), 2 )-( SELECT ifnull( sum( pc.winv_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND p.`status` = 2 and ps.status=7 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.invtime DESC ) ) AS '待回票', a.cgd_delivery_status 'CGD.发货状态', pc.total_fee '本次付款金额', pc.ainv_fee '本次回票金额', ps.paytime '本次付款时间', ps.invtime '本次回票时间', f.send_date '本次发货时间', f.send '本次发货数量', a.return_ticket '回票方式', a.return_trade '结算方式', if(( ROUND( ifnull( b.Number1618330543270, 0 ), 2 )-( SELECT ifnull( sum( pc.winv_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND p.`status` = 2 and ps.status=7 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.invtime DESC ) )=0,'已回','未回' )AS '回票状态', if(( ROUND( ifnull( b.Number1618330472961, 0 ), 2 )- ( SELECT ifnull( round(sum( pc.wait_fee ),2), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND p.`status` = 2 and ps.status=4 AND pc.sequenceNo = b.sequenceNo ORDER BY ps.paytime DESC ) )=0,'已付','未付' ) AS '付款状态' FROM source_all AS a LEFT JOIN cfp_cgd_info AS b ON a.cgdNo = b.ShortText1618315935182 LEFT JOIN fh_source f ON a.cgdNo = f.cgdNo LEFT JOIN cfp_pay_info AS pc ON pc.sequenceNo = b.sequenceNo AND pc.STATUS = 1 LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo AND ps.is_del = 0 LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo AND p.`status` = 2 WHERE a.cgdNo <> '' {$where} order by cgdtime"; $list = Db::query($sql); if(empty($list)){ $list=[["未找到数据"=>""]]; } $header = array_keys($list[0]); array_walk($list, function (&$v) { $v = array_values($v); }); excelSave(date("Y-m-d",strtotime($start)).'至'.date("Y-m-d",strtotime($end))."采购单核算", $header, $list); } public function downQRDTZ(){ $post = request()->post(); $start = isset($post['start']) && $post['start'] != "" ? $post['start']." 00:00:00" : ''; $end = isset($post['end']) && $post['end'] != "" ? $post['end']." 23:59:59" : ''; $where=''; if($start!=""){ $where.=" and ordertime >='{$start}'"; } if($end!=""){ $where.=" and ordertime <='{$end}'"; } $sql="SELECT DATE_FORMAT(ordertime,'%Y') '年', DATE_FORMAT(ordertime,'%m') '月', DATE_FORMAT(ordertime,'%d') '日', supperinfo 'QRD公司名称', zxcode 'QRD.咨询单号', zxtype 'QRD.咨询单类型', source_all.qrdNo 'QRD.4确认单编号', sale_name 'QRD.业务员', suppitem 'QRD平台', khzzxz 'QRD客户属性', khcomp 'QRD客户分公司', product_name 'QRD.产品名称', product_code 'QRD.产品编号', cat_f 'QRD.一级分类', unit 'QRD.单位', tax 'QRD.税率', sale_price 'QRD.销售单价', order_num 'QRD.下单数量', sale_total 'QRD.销售总额', income 'QRD.不含税收入', ROUND(b.Number1618249202608,2) 'QRD已开票', ROUND(b.Number1618249205231 ,2) 'QRD未开票', b.inving_fee 'QRD开票中', source_all.cgdNo 'CGD采购单单号', cgdtype 'CGD采购单类型', cgd_saler 'CGD采购单业务员', suplier_name 'CGD供货商名称', cgd_tax 'CGD采购单税点', pakage_fee 'CGD包装费', cert_fee 'CGD证书费', mark_fee 'CGD加标费', open_fee 'CGD开模费', cost_fee 'CGD成本工艺费', naked_fee 'CGD裸价', delivery_fee 'CGD物流费', cgd_price 'CGD采购单单价', cgd_num 'CGD采购单数量', cgd_total 'CGD采购单金额', ( ROUND( ifnull( c.Number1618330470625, 0 ), 2 )+ ( SELECT ifnull( sum( pc.wait_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND p.`status` = 2 and ps.pay_status=4 AND pc.sequenceNo = c.sequenceNo ORDER BY ps.paytime DESC ) ) AS 'CGD已付款', ( ROUND( ifnull( c.Number1618330472961, 0 ), 2 )- ( SELECT ifnull( sum( pc.wait_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND p.`status` = 2 and ps.pay_status=4 AND pc.sequenceNo = c.sequenceNo ORDER BY ps.paytime DESC ) ) AS 'CGD未付款', ( ROUND( ifnull( c.Number1618330541286, 0 ), 2 )+ ( SELECT ifnull( sum( pc.winv_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND p.`status` = 2 and ps.status=7 AND pc.sequenceNo = c.sequenceNo ORDER BY ps.paytime DESC ) ) AS 'CGD已回票', ( ROUND( ifnull( c.Number1618330543270, 0 ), 2 )-( SELECT ifnull( sum( pc.winv_fee ), 0 ) FROM cfp_pay_info AS pc LEFT JOIN cfp_pay_stages AS ps ON ps.payNo = pc.payNo LEFT JOIN cfp_pay AS p ON p.payNo = pc.payNo WHERE pc.STATUS = 1 AND ps.is_del = 0 AND p.`status` = 2 and ps.status=7 AND pc.sequenceNo = c.sequenceNo ORDER BY ps.invtime DESC ) ) AS 'CGD待回票', ( SELECT source_all.cgd_num-sum( send ) FROM fh_source WHERE source_all.cgdNo = fh_source.cgdNo ) AS 'CGD.未发货数量' , ( SELECT sum( send ) FROM fh_source WHERE source_all.cgdNo = fh_source.cgdNo ) AS 'CGD.总发货数量' FROM source_all left JOIN cfp_qrd_info b on source_all.productNo = b.sequenceNo LEFT JOIN cfp_cgd_info c on source_all.cgdNo = c.ShortText1618315935182 where source_all.productNo<>'' {$where} order by ordertime"; $list = Db::query($sql); if(empty($list)){ $list=[["未找到数据"=>""]]; } $header = array_keys($list[0]); array_walk($list, function (&$v) { $v = array_values($v); }); excelSave(date("Y-m-d",strtotime($start)).'至'.date("Y-m-d",strtotime($end))."确认采购单业绩", $header, $list); } public function downTH(){ $post = request()->post(); $start = isset($post['start']) && $post['start'] != "" ? $post['start']." 00:00:00" : ''; $end = isset($post['end']) && $post['end'] != "" ? $post['end']." 23:59:59" : ''; $where=''; if($start!=""){ $where.=" and t.createtime >='{$start}'"; } if($end!=""){ $where.=" and t.createtime <='{$end}'"; } $sql = "select t.thNo 'TH.退货单号', case t.th_status WHEN 1 THEN '已完成' WHEN 2 THEN '进行中' WHEN 3 THEN '已取消' else '' end'TH.退货状态', t.createtime 'TH.退货时间', t.th_company 'TH.公司', t.th_user 'TH.退货人', t.th_remark 'TH.退货备注', suppitem 'QRD平台', khzzxz 'QRD客户属性', source_all.qrdNo 'QRD.4确认单编号', sale_name 'QRD.业务员', product_name 'QRD.产品名称', product_code 'QRD.产品编号', cat_f 'QRD.一级分类', financial 'CGD.财务核算码', unit 'QRD.单位', tax 'QRD.税率', sale_price 'QRD.销售单价', t.th_num 'TH.退货数量', t.th_qrd_fee 'TH.销售货款', t.th_cgd_fee 'TH.采购货款', income 'QRD.不含税收入', zxcode 'QRD.咨询单号', zxtype 'QRD.咨询单类型', source_all.cgdNo 'CGD采购单单号' from cfp_th_source as t left join source_all on ((source_all.productNo=t.th_qrdcpNo and t.th_qrdcpNo<>'') or (t.th_cgdNo=source_all.cgdNo and t.th_cgdNo<>'')) left JOIN cfp_qrd_info b on t.th_qrdcpNo = b.sequenceNo LEFT JOIN cfp_cgd_info c on t.th_cgdNo = c.ShortText1618315935182 where t.th_status<>3 {$where} order by t.createtime"; $list = Db::query($sql); if(empty($list)){ $list=[["未找到数据"=>""]]; } $header = array_keys($list[0]); array_walk($list, function (&$v) { $v = array_values($v); }); excelSave(date("Y-m-d",strtotime($start)).'至'.date("Y-m-d",strtotime($end))."退货单业绩", $header, $list); } /** * @throws \think\db\exception\BindParamException * @throws \think\exception\PDOException */ public function QRDPAY(){ $post = request()->post(); $start = isset($post['start']) && $post['start'] != "" ? $post['start']." 00:00:00" : ''; $end = isset($post['end']) && $post['end'] != "" ? $post['end']." 23:59:59" : ''; $where=''; if($start!=""){ $where.=" and a.ordertime >='{$start}'"; } if($end!=""){ $where.=" and a.ordertime <='{$end}'"; } $sql = "SELECT a.qrdNo AS 'QRD.4确认单编号', a.productNo AS 'QRD确认单产品编号', a.supperinfo AS 'QRD公司名称', a.companyName AS 'QRD客户名称', a.suppitem AS 'QRD平台', a.khzzxz AS 'QRD客户属性', a.khcomp AS 'QRD客户分公司', a.poNo AS 'PT.4PO编号', a.zxtype AS 'QRD线上线下', a.sale_name AS 'QRD.业务员', a.product_name AS 'QRD.产品名称', a.sale_price AS 'QRD.销售单价', a.order_num AS 'QRD.下单数量', a.sale_total AS '销售总额', a.ordertime AS 'QRD.下单时间', b.Number1618249146997 '已收款', b.paying_fee '结算中', ( a.sale_total - ifnull( b.Number1618249146997, 0 )- ifnull( b.paying_fee, 0 )-( SELECT ifnull( sum( th_qrd_fee ), 0 ) FROM cfp_th_source WHERE th_qrdcpNo = a.productNo AND th_status = 1 )) '未收款', ( SELECT ak.addtime FROM cfp_order_info AS oi LEFT JOIN cfp_order_pool AS po ON oi.orderNo = po.orderNo LEFT JOIN cfp_assoc_list AS ak ON ( ak.codeNo = oi.orderNo AND ak.rela_form = 2 ) OR ( ak.viceNo = oi.orderNo AND ak.rela_form = 3 ) WHERE po.is_del = 0 AND ak.rela_form IN ( 2, 3 ) AND oi.STATUS = 1 AND ak.STATUS = 3 AND ak.is_del = 0 AND oi.goodNo = a.productNo ORDER BY ak.addtime DESC LIMIT 1 ) AS '回款日期', DATEDIFF( ifnull( ( SELECT ak.addtime FROM cfp_order_info AS oi LEFT JOIN cfp_order_pool AS po ON oi.orderNo = po.orderNo LEFT JOIN cfp_assoc_list AS ak ON ( ak.codeNo = oi.orderNo AND ak.rela_form = 2 ) OR ( ak.viceNo = oi.orderNo AND ak.rela_form = 3 ) WHERE po.is_del = 0 AND ak.rela_form IN ( 2, 3 ) AND oi.STATUS = 1 AND ak.STATUS = 3 AND ak.is_del = 0 AND oi.goodNo = a.productNo ORDER BY ak.addtime DESC LIMIT 1 ), NOW()), a.ordertime ) AS '账期', a.delivery_status 'QRD.发货状态', ( SELECT sum( send ) FROM fh_source WHERE a.cgdNo = fh_source.cgdNo ) AS 'qrd.总发货数量', ( SELECT max( addtime ) FROM fh_source WHERE a.cgdNo = fh_source.cgdNo ) AS 'qrd.最近一次发货时间', ( SELECT sum( th_num ) FROM cfp_th_source WHERE th_qrdcpNo = a.productNo AND th_status = 1 ) AS 'qrd.总退货数量', ( SELECT sum( th_qrd_fee ) FROM cfp_th_source WHERE th_qrdcpNo = a.productNo AND th_status = 1 ) AS '退货金额', ( SELECT max( addtime ) FROM cfp_th_source WHERE th_qrdcpNo = a.productNo AND th_status = 1 ) AS 'qrd.退货时间', ( a.sale_total -( SELECT ifnull( sum( th_qrd_fee ), 0 ) FROM cfp_th_source WHERE th_qrdcpNo = a.productNo AND th_status = 1 ) ) AS '减退货付款额', IF ((( a.sale_total -( SELECT ifnull( sum( th_qrd_fee ), 0 ) FROM cfp_th_source WHERE th_qrdcpNo = a.productNo AND th_status = 1 ))<>( ifnull( b.Number1618249146997, 0 )+ ifnull( b.paying_fee, 0 )+ ifnull( b.number1618249149738, 0 ))), '否', '是' ) AS '是否减退货' FROM `source_all` AS a LEFT JOIN `cfp_qrd_info` `b` ON (( `a`.`productNo` = `b`.`sequenceNo` )) WHERE a.productNo <> '' AND sale_total <> 0 {$where} HAVING 减退货付款额 <> 0 order by a.ordertime desc"; $list = Db::query($sql); if(empty($list)){ $list=[["未找到数据"=>""]]; } $header = array_keys($list[0]); array_walk($list, function (&$v) { $v = array_values($v); }); excelSave(date("Y-m-d",strtotime($start)).'至'.date("Y-m-d",strtotime($end))."应收账款账龄表", $header, $list); } /** * @throws \think\db\exception\BindParamException * @throws \think\exception\PDOException */ public function QRDINV(){ $post = request()->post(); $start = isset($post['start']) && $post['start'] != "" ? $post['start']." 00:00:00" : ''; $end = isset($post['end']) && $post['end'] != "" ? $post['end']." 23:59:59" : ''; $where=''; if($start!=""){ $where.=" and a.ordertime >='{$start}'"; } if($end!=""){ $where.=" and a.ordertime <='{$end}'"; } $sql = "SELECT a.qrdNo as 'QRD.4确认单编号', a.productNo as 'QRD确认单产品编号', a.supperinfo as 'QRD公司名称', a.companyName as 'QRD客户名称', a.suppitem as 'QRD平台', a.khzzxz as 'QRD客户属性', a.khcomp as 'QRD客户分公司', a.poNo as 'PT.4PO编号', a.zxtype as 'QRD线上线下', a.sale_name as 'QRD.业务员', a.product_name as 'QRD.产品名称', a.sale_price as 'QRD.销售单价', a.order_num as 'QRD.下单数量', a.sale_total as 'QRD.销售总额', a.ordertime as 'QRD.下单时间', b.Number1618249202608 '已开票', b.inving_fee '开票中', ( a.sale_total - ifnull( b.Number1618249202608, 0 )- ifnull( b.inving_fee, 0 )-( SELECT ifnull( sum( th_qrd_fee ), 0 ) FROM cfp_th_source WHERE th_qrdcpNo = a.productNo AND th_status = 1 )) '未开票', ( SELECT ak.addtime FROM cfp_order_info AS oi LEFT JOIN cfp_order_pool AS po ON oi.orderNo = po.orderNo LEFT JOIN cfp_assoc_list AS ak ON ( ak.codeNo = oi.orderNo AND rela_form = 1 ) WHERE po.is_del = 0 AND oi.STATUS = 1 AND ak.STATUS = 3 AND ak.is_del = 0 and oi.goodNo = a.productNo ORDER BY ak.addtime DESC LIMIT 1 ) AS 'QRD.最近开票时间', a.delivery_status 'QRD.发货状态', ( SELECT sum( send ) FROM fh_source WHERE a.cgdNo = fh_source.cgdNo ) AS 'qrd.总发货数量', ( SELECT max( addtime ) FROM fh_source WHERE a.cgdNo = fh_source.cgdNo ) AS 'qrd.最近一次发货时间', ( SELECT sum( th_num ) FROM cfp_th_source WHERE th_qrdcpNo = a.productNo AND th_status = 1 ) AS 'qrd.总退货数量', ( SELECT sum( th_qrd_fee ) FROM cfp_th_source WHERE th_qrdcpNo = a.productNo AND th_status = 1 ) AS '退货金额', ( SELECT max( addtime ) FROM cfp_th_source WHERE th_qrdcpNo = a.productNo AND th_status = 1 ) AS 'qrd.退货时间', ( a.sale_total -( SELECT ifnull( sum( th_qrd_fee ), 0 ) FROM cfp_th_source WHERE th_qrdcpNo = a.productNo AND th_status = 1 ) ) AS '减退货付款额', IF ((( a.sale_total -( SELECT ifnull( sum( th_qrd_fee ), 0 ) FROM cfp_th_source WHERE th_qrdcpNo = a.productNo AND th_status = 1 ))<>( ifnull( b.Number1618249146997, 0 )+ ifnull( b.paying_fee, 0 )+ ifnull( b.number1618249149738, 0 ))), '否', '是' ) AS '是否减退货' FROM `source_all` as a LEFT JOIN `cfp_qrd_info` `b` ON (( `a`.`productNo` = `b`.`sequenceNo` )) WHERE a.productNo <> '' AND sale_total <> 0 {$where} HAVING 减退货付款额 <> 0 order by a.ordertime asc"; $list = Db::query($sql); if(empty($list)){ $list=[["未找到数据"=>""]]; } $header = array_keys($list[0]); array_walk($list, function (&$v) { $v = array_values($v); }); excelSave(date("Y-m-d",strtotime($start)).'至'.date("Y-m-d",strtotime($end))."应收账款开票汇总表", $header, $list); } /** * @throws \think\db\exception\BindParamException * @throws \think\exception\PDOException */ public function jxreport(){ $post = request()->post(); $start = isset($post['start']) && $post['start'] != "" ? $post['start']." 00:00:00" : ''; $end = isset($post['end']) && $post['end'] != "" ? $post['end']." 23:59:59" : ''; $where=''; if($start!=""){ $where.=" and ordertime >='{$start}'"; } if($end!=""){ $where.=" and ordertime <='{$end}'"; } $sql = "SELECT date_format( `source_all`.`ordertime`, '%Y' ) AS `年`, date_format( `source_all`.`ordertime`, '%m' ) AS `月`, date_format( `source_all`.`ordertime`, '%d' ) AS `日`, `source_all`.`suppitem` AS `1.12平台类型`, `source_all`.`sale_name` AS `业务人员`, `source_all`.`depart` AS `业务部门`, `source_all`.`cat_f` AS `3.4产品一级分类`, `source_all`.`sale_total` AS `产品货款总额` FROM `source_all` WHERE ( `source_all`.`suppitem` <> '预付卡' and productNo<>'' {$where}) order by ordertime asc"; $list = Db::query($sql); if(empty($list)){ $list=[["未找到数据"=>""]]; } $header = array_keys($list[0]); array_walk($list, function (&$v) { $v = array_values($v); }); excelSave(date("Y-m-d",strtotime($start)).'至'.date("Y-m-d",strtotime($end))."确认单绩效报表", $header, $list); } public function jxthreport(){ $post = request()->post(); $start = isset($post['start']) && $post['start'] != "" ? $post['start']." 00:00:00" : ''; $end = isset($post['end']) && $post['end'] != "" ? $post['end']." 23:59:59" : ''; $where=''; if($start!=""){ $where.=" and a.createtime >='{$start}'"; } if($end!=""){ $where.=" and a.createtime <='{$end}'"; } $sql = "SELECT `a`.`th_user` AS `业务人员`, `b`.`suppitem` AS `1.12平台类型`, `b`.`depart` AS `业务部门`, `b`.`cat_f` AS `3.4产品一级分类`, a.th_qrdNo as '确认单编号', b.sale_total AS `货款总额`, `a`.`th_qrd_fee` AS `退货金额` FROM ( `cfp_th_source` `a` LEFT JOIN `source_all` `b` ON (( `a`.`th_qrdcpNo` = `b`.`productNo` ))) WHERE `b`.`suppitem` <> '预付卡' {$where} ORDER BY `a`.`th_user`"; $list = Db::query($sql); if(empty($list)){ $list=[["未找到数据"=>""]]; } $header = array_keys($list[0]); array_walk($list, function (&$v) { $v = array_values($v); }); excelSave(date("Y-m-d",strtotime($start)).'至'.date("Y-m-d",strtotime($end))."退款统计绩效报表", $header, $list); } /**出入库明细 */ public function OutInReport(){ $post = request()->post(); $start = isset($post['start']) && $post['start'] != "" ? $post['start']." 00:00:00" : ''; $end = isset($post['end']) && $post['end'] != "" ? $post['end']." 23:59:59" : ''; $where=''; if($start!=""){ $where.=" and a.ordertime >='{$start}'"; } if($end!=""){ $where.=" and a.ordertime <='{$end}'"; } $sql = "SELECT `a`.`supperinfo` AS `公司名称`, `a`.`cgdtype` AS `CGD采购单类型`, `a`.`qrdNo` AS `QRD.4确认单编号`, `a`.`cgdNo` AS `CGD采购单单号`, `a`.`financial` AS `QRD.财务核算码`, `a`.`product_name` AS `QRD.产品名称`, `a`.`order_num` AS `QRD.下单数量`, `a`.`qrd_delivery` AS `qrd发货方式`, `a`.`qrd_stock` AS `qrd.是否库存产品`, `a`.`cgd_tax` AS `CGD采购单税点`, `a`.`pakage_fee` AS `CGD包装费`, `a`.`cert_fee` AS `CGD证书费`, `a`.`mark_fee` AS `CGD加标费`, `a`.`open_fee` AS `CGD开模费`, `a`.`cost_fee` AS `CGD成本工艺费`, `a`.`naked_fee` AS `CGD裸价`, `a`.`delivery_fee` AS `CGD物流费`, `a`.`cgd_price` AS `CGD采购单单价`, `a`.`cgd_total` AS `CGD采购单金额`, `c`.`send` AS `CGD本次发货`, `c`.`send_date` AS `CGD本次发货时间`,( SELECT `im`.`buyer_name` FROM (((( `cfp_pay_info` `pc` LEFT JOIN `cfp_pay_stages` `ps` ON (( `ps`.`payNo` = `pc`.`payNo` ))) LEFT JOIN `cfp_pay` `p` ON (( `p`.`payNo` = `pc`.`payNo` ))) LEFT JOIN `cfp_invoice_img` `m` ON ((( `m`.`payid` = `ps`.`id` ) AND ( `m`.`is_del` = 0 )))) LEFT JOIN `cfp_invoice_info` `im` ON (( `im`.`invid` = `m`.`id` ))) WHERE (( `pc`.`status` = 1 ) AND ( `ps`.`is_del` = 0 ) AND (( `p`.`status` = 2 ) OR ( `p`.`status` = 1 )) AND ( `ps`.`status` = 7 ) AND ( `m`.`is_del` = 0 ) AND ( `pc`.`sequenceNo` = `b`.`sequenceNo` )) LIMIT 1 ) AS `CGD.HP.MX.回票抬头` FROM (( `source_all` `a` LEFT JOIN `cfp_qrd_info` `b` ON (( `a`.`productNo` = `b`.`sequenceNo` ))) LEFT JOIN `fh_source` `c` ON (( `c`.`cgdNo` = `a`.`cgdNo` ))) WHERE (( `a`.`productNo` <> '' ) {$where}) "; $list = Db::query($sql); if(empty($list)){ $list=[["未找到数据"=>""]]; } $header = array_keys($list[0]); array_walk($list, function (&$v) { $v = array_values($v); }); excelSave(date("Y-m-d",strtotime($start)).'至'.date("Y-m-d",strtotime($end))."出入库数据报表", $header, $list); } /** 收入明细 */ public function InfoReport(){ $post = request()->post(); $start = isset($post['start']) && $post['start'] != "" ? $post['start']." 00:00:00" : ''; $end = isset($post['end']) && $post['end'] != "" ? $post['end']." 23:59:59" : ''; $where=''; if($start!=""){ $where.=" and a.ordertime >='{$start}'"; } if($end!=""){ $where.=" and a.ordertime <='{$end}'"; } $sql = "SELECT `a`.`qrdNo` AS `QRD.4确认单编号`, `a`.`companyName` AS `KH.2.客户名称`, `a`.`company` AS `公司名称`, `a`.`supplier` AS `供应商名称`, `a`.`cgdNo` AS `CGD采购单单号`, ifnull( `a`.`financial`, `a`.`fund_code` ) AS `QRD.财务核算码`, `a`.`cat_f` AS `QRD.一级分类`, `a`.`product_name` AS `QRD.产品名称`, `a`.`order_num` AS `QRD.下单数量`, `a`.`sale_price` AS `QRD销售单价`, `a`.`sale_total` AS `QRD销售总额`, `ak`.`viceNo` AS `发票号`, IF (( `ak`.`status` = 3 ), `oi`.`ainv_fee`, `oi`.`inv_fee` ) AS `本次开票金额`,( SELECT `l`.`addtime` FROM `invoice`.`cfp_invoice_list` `l` WHERE ( `l`.`invNo` = `ak`.`viceNo` ) LIMIT 1 ) AS `本次开票时间`, `a`.`qrd_delivery` AS `qrd发货方式`, `a`.`qrd_stock` AS `qrd.是否库存产品`, `a`.`cgd_saler` AS `CGD.采购单业务员`, `a`.`pakage_fee` AS `CGD包装费`, `a`.`cert_fee` AS `CGD证书费`, `a`.`mark_fee` AS `CGD加标费`, `a`.`open_fee` AS `CGD开模费`, `a`.`cost_fee` AS `CGD成本工艺费`, `a`.`naked_fee` AS `CGD裸价`, `a`.`delivery_fee` AS `CGD物流费`, `a`.`cgd_tax` AS `CGD采购单税点`, `a`.`cgd_price` AS `CGD采购单单价`, `a`.`cgd_total` AS `CGD采购单金额`, `a`.`cgd_tax` AS `CGD采购税点`,( SELECT `im`.`buyer_name` FROM (((( `invoice`.`cfp_pay_info` `pc` LEFT JOIN `invoice`.`cfp_pay_stages` `ps` ON (( `ps`.`payNo` = `pc`.`payNo` ))) LEFT JOIN `invoice`.`cfp_pay` `p` ON (( `p`.`payNo` = `pc`.`payNo` ))) LEFT JOIN `invoice`.`cfp_invoice_img` `m` ON ((( `m`.`payid` = `ps`.`id` ) AND ( `m`.`is_del` = 0 )))) LEFT JOIN `invoice`.`cfp_invoice_info` `im` ON (( `im`.`invid` = `m`.`id` ))) WHERE (( `pc`.`status` = 1 ) AND ( `ps`.`is_del` = 0 ) AND (( `p`.`status` = 2 ) OR ( `p`.`status` = 1 )) AND ( `ps`.`status` = 7 ) AND ( `m`.`is_del` = 0 ) AND ( `pc`.`sequenceNo` = `a`.`cgdjlNo` )) LIMIT 1 ) AS `CGD.HP.MX.回票抬头`, IF (( `p`.`status` = 1 ), '待审核', IF (( `p`.`status` = 2 ), '审核未通过', IF (( `p`.`status` = 3 ), '待开票', IF (( `p`.`status` = 4 ), '待填写物流', IF (( `p`.`status` = 5 ), '开票完成', '未申请' ))))) AS `开票状态` FROM ((((( `invoice`.`source_all` `a` LEFT JOIN `invoice`.`cfp_qrd_info` `b` ON (( `a`.`productNo` = `b`.`sequenceNo` ))) LEFT JOIN `invoice`.`cfp_order_info` `oi` ON ((( `oi`.`goodNo` = `b`.`sequenceNo` ) AND ( `oi`.`status` = 1 )))) LEFT JOIN `invoice`.`cfp_order_pool` `po` ON ((( `oi`.`orderNo` = `po`.`orderNo` ) AND ( `po`.`is_del` = 0 )))) LEFT JOIN ( SELECT `k`.`id` AS `aid`, `k`.`assocNo` AS `assocNo`, `k`.`companyNo` AS `companyNo`, `k`.`type` AS `type`, `k`.`rela_form` AS `rela_form`, `k`.`codeNo` AS `codeNo`, `k`.`total_fee` AS `total_fee`, `k`.`balance` AS `balance`, `k`.`cancel_total` AS `cancel_total`, `k`.`is_del` AS `is_del`, `k`.`status` AS `status`, `k`.`exam_remark` AS `exam_remark`, `k`.`addtime` AS `addtime`, `t`.`viceNo` AS `viceNo`, `t`.`vice_fee` AS `vice_fee`, `t`.`cancel_fee` AS `cancel_fee`, `t`.`id` AS `id`, `k`.`source` AS `source`, `t`.`vice_total` AS `vice_total`, `k`.`apply_name` AS `apply_name`, `k`.`apply_id` AS `apply_id`, `t`.`status` AS `bstatus` FROM ( `invoice`.`cfp_assoc_key` `k` JOIN `invoice`.`cfp_assoc_rela` `t` ON (( `k`.`assocNo` = `t`.`assocNo` ))) WHERE (( `k`.`is_del` = 0 ) AND ( `t`.`is_del` = 0 ) AND ( `t`.`status` = 1 ) AND ( `k`.`rela_form` = 1 ))) `ak` ON (( `ak`.`codeNo` = `oi`.`orderNo` ))) LEFT JOIN `invoice`.`cfp_invoice_pool` `p` ON (( `ak`.`viceNo` = `p`.`invNo` ))) WHERE (( `a`.`productNo` <> '' ) AND (( `po`.`type` = 1 ) OR isnull( `po`.`type` )) ) {$where} "; $list = Db::query($sql); if(empty($list)){ $list=[["未找到数据"=>""]]; } $header = array_keys($list[0]); array_walk($list, function (&$v) { $v = array_values($v); }); excelSave(date("Y-m-d",strtotime($start)).'至'.date("Y-m-d",strtotime($end))."收入明细数据报表", $header, $list); } /** 回票公明细 */ public function InvComReport(){ $post = request()->post(); $start = isset($post['start']) && $post['start'] != "" ? $post['start']." 00:00:00" : ''; $end = isset($post['end']) && $post['end'] != "" ? $post['end']." 23:59:59" : ''; $where=''; if($start!=""){ $where.=" and a.cgdtime >='{$start}'"; } if($end!=""){ $where.=" and a.cgdtime <='{$end}'"; } $sql = "SELECT `b`.`ShortText1618270466672` AS `公司名称`, `b`.`ShortText1618315935182` AS `采购单编号`, round( `b`.`Number1618240685904`, 2 ) AS `货款`, `b`.`ShortText1620399144946` AS `供应商名称`, `b`.`StaffSelector1620899427104` AS `采购员`, `a`.`cgd_delivery_status` AS `发货状态`, '' AS `退货状态`,( round( ifnull( `b`.`Number1618330470625`, 0 ), 2 ) + ( SELECT ifnull( sum( `pc`.`total_fee` ), 0 ) FROM (( `cfp_pay_info` `pc` LEFT JOIN `cfp_pay_stages` `ps` ON (( `ps`.`payNo` = `pc`.`payNo` ))) LEFT JOIN `cfp_pay` `p` ON (( `p`.`payNo` = `pc`.`payNo` ))) WHERE (( `pc`.`status` = 1 ) AND ( `ps`.`is_del` = 0 ) AND (( `p`.`status` = 2 ) OR ( `p`.`status` = 1 )) AND ( `ps`.`pay_status` = 4 ) AND ( `pc`.`sequenceNo` = `b`.`sequenceNo` )))) AS `已付款金额`,( CASE WHEN (( round( ifnull( `b`.`Number1618330541286`, 0 ), 2 ) + ( SELECT ifnull( sum(( `pc`.`winv_fee` + `pc`.`ainv_fee` )), 0 ) FROM (( `cfp_pay_info` `pc` LEFT JOIN `cfp_pay_stages` `ps` ON (( `ps`.`payNo` = `pc`.`payNo` ))) LEFT JOIN `cfp_pay` `p` ON (( `p`.`payNo` = `pc`.`payNo` ))) WHERE (( `pc`.`status` = 1 ) AND ( `ps`.`is_del` = 0 ) AND (( `p`.`status` = 2 ) OR ( `p`.`status` = 1 )) AND ( `ps`.`status` = 7 ) AND ( `pc`.`sequenceNo` = `b`.`sequenceNo` )))) = 0 ) THEN '未回票' WHEN (( round( ifnull( `b`.`Number1618330543270`, 0 ), 2 ) - ( SELECT ifnull( sum(( `pc`.`winv_fee` + `pc`.`ainv_fee` )), 0 ) FROM (( `cfp_pay_info` `pc` LEFT JOIN `cfp_pay_stages` `ps` ON (( `ps`.`payNo` = `pc`.`payNo` ))) LEFT JOIN `cfp_pay` `p` ON (( `p`.`payNo` = `pc`.`payNo` ))) WHERE (( `pc`.`status` = 1 ) AND ( `ps`.`is_del` = 0 ) AND (( `p`.`status` = 2 ) OR ( `p`.`status` = 1 )) AND ( `ps`.`status` = 7 ) AND ( `pc`.`sequenceNo` = `b`.`sequenceNo` )) LIMIT 1 )) = 0 ) THEN '全部回票' ELSE '部分回票' END ) AS `回票状态`, round(( round( ifnull( `b`.`Number1618330541286`, 0 ), 2 ) + ( SELECT ifnull( sum(( `pc`.`winv_fee` + `pc`.`ainv_fee` )), 0 ) FROM (( `cfp_pay_info` `pc` LEFT JOIN `cfp_pay_stages` `ps` ON (( `ps`.`payNo` = `pc`.`payNo` ))) LEFT JOIN `cfp_pay` `p` ON (( `p`.`payNo` = `pc`.`payNo` ))) WHERE (( `pc`.`status` = 1 ) AND ( `ps`.`is_del` = 0 ) AND (( `p`.`status` = 2 ) OR ( `p`.`status` = 1 )) AND ( `ps`.`status` = 7 ) AND ( `pc`.`sequenceNo` = `b`.`sequenceNo` )))), 2 ) AS `已回票金额`, round(( ifnull( `b`.`Number1618330543270`, 0 ) - ( SELECT ifnull( sum(( `pc`.`winv_fee` + `pc`.`ainv_fee` )), 0 ) FROM (( `cfp_pay_info` `pc` LEFT JOIN `cfp_pay_stages` `ps` ON (( `ps`.`payNo` = `pc`.`payNo` ))) LEFT JOIN `cfp_pay` `p` ON (( `p`.`payNo` = `pc`.`payNo` ))) WHERE (( `pc`.`status` = 1 ) AND ( `ps`.`is_del` = 0 ) AND (( `p`.`status` = 2 ) OR ( `p`.`status` = 1 )) AND ( `ps`.`status` = 7 ) AND ( `pc`.`sequenceNo` = `b`.`sequenceNo` )))), 2 ) AS `未回票金额`,( SELECT `im`.`buyer_name` FROM (((( `cfp_pay_info` `pc` LEFT JOIN `cfp_pay_stages` `ps` ON (( `ps`.`payNo` = `pc`.`payNo` ))) LEFT JOIN `cfp_pay` `p` ON (( `p`.`payNo` = `pc`.`payNo` ))) LEFT JOIN `cfp_invoice_img` `m` ON ((( `m`.`payid` = `ps`.`id` ) AND ( `m`.`is_del` = 0 )))) LEFT JOIN `cfp_invoice_info` `im` ON (( `im`.`invid` = `m`.`id` ))) WHERE (( `pc`.`status` = 1 ) AND ( `ps`.`is_del` = 0 ) AND (( `p`.`status` = 2 ) OR ( `p`.`status` = 1 )) AND ( `ps`.`status` = 7 ) AND ( `m`.`is_del` = 0 ) AND ( `pc`.`sequenceNo` = `b`.`sequenceNo` )) LIMIT 1 ) AS `回票公司`, `b`.`Date1618315953443` AS `下单时间`, `b`.`ShortText1617861966146` AS `产品名称`, `b`.`ShortText1617865685744` AS `单位`, cast( `b`.`Number1618240600907` AS signed ) AS `数量`, round( `b`.`Number1618240685904`, 2 ) AS `成本合计`, `b`.`ShortText1617865688485` AS `税点`,( SELECT `ps`.`invtime` FROM (( `cfp_pay_info` `pc` LEFT JOIN `cfp_pay_stages` `ps` ON (( `ps`.`payNo` = `pc`.`payNo` ))) LEFT JOIN `cfp_pay` `p` ON (( `p`.`payNo` = `pc`.`payNo` ))) WHERE (( `pc`.`status` = 1 ) AND ( `ps`.`is_del` = 0 ) AND (( `p`.`status` = 2 ) OR ( `p`.`status` = 1 )) AND ( `ps`.`status` = 7 ) AND ( `pc`.`sequenceNo` = `b`.`sequenceNo` )) ORDER BY `ps`.`invtime` DESC LIMIT 1 ) AS `财务审批时间`, round( 0, 2 ) AS `CGD.不含税采购成本-这个怎么计算`, `a`.`qrdNo` AS `确认单编号`, `a`.`financial` AS `财务核算码`, `a`.`sale_price` AS `确认单销售单价` FROM ( `source_all` `a` LEFT JOIN `cfp_cgd_info` `b` ON (( `a`.`cgdNo` = `b`.`ShortText1618315935182` ))) WHERE (( `a`.`cgdNo` <> '' ) {$where}) "; $list = Db::query($sql); if(empty($list)){ $list=[["未找到数据"=>""]]; } $header = array_keys($list[0]); array_walk($list, function (&$v) { $v = array_values($v); }); excelSave(date("Y-m-d",strtotime($start)).'至'.date("Y-m-d",strtotime($end))."回票公司数据报表", $header, $list); } /** 回票公明细 */ public function InvReport(){ $post = request()->post(); $start = isset($post['start']) && $post['start'] != "" ? $post['start']." 00:00:00" : ''; $end = isset($post['end']) && $post['end'] != "" ? $post['end']." 23:59:59" : ''; $where=''; if($start!=""){ $where.=" and a.cgdtime >='{$start}'"; } if($end!=""){ $where.=" and a.cgdtime <='{$end}'"; } $sql = "SELECT `b`.`ShortText1618315935182` AS `CGD.2采购单号`, `b`.`ShortText1618270466672` AS `CGD.14公司主体`, `b`.`Date1618315953443` AS `CGD.3采购单下单日期`, `b`.`ShortText1618270412964` AS `CGD.1采购单类型`, `b`.`StaffSelector1620899427104` AS `CGD.采购员`, `b`.`ShortText1620399144946` AS `CGD.45供应商名称`, `b`.`ShortText1617861287265` AS `CGD.44供应商编号`, `b`.`ShortText1619625566608` AS `CGD.出库包装库存`, `b`.`ShortText1617865688485` AS `CGD.40税点`, `a`.`financial` AS `CGD财务核算码`, round( `b`.`Number1617865810822`, 2 ) AS `CGD.包装费`, round( `b`.`Number1617865813284`, 2 ) AS `CGD.证书费`, round( `b`.`Number1617865807879`, 2 ) AS `CGD.加标费`, round( `b`.`Number1617865816181`, 2 ) AS `CGD.开模费`, round( `b`.`number1618240204358`, 2 ) AS `CGD.成本工艺费`, round( `b`.`Number1617865804813`, 2 ) AS `CGD.成本裸价`, round( `b`.`number1618240287778`, 2 ) AS `CGD.物流费`, round( `b`.`Number1619632830397`, 2 ) AS `CGD.工差`, round( `b`.`Number1619632826654`, 2 ) AS `CGD.采购工差金额`, round( `b`.`Number1617865818517`, 2 ) AS `CGD.采购单价`, cast( `b`.`Number1618240600907` AS signed ) AS `CGD.采购数量`, round( `b`.`Number1618240685904`, 2 ) AS `CGD.采购金额`, round( 0, 2 ) AS `CGD.不含税采购成本-这个怎么计算`, `b`.`ShortText1618859161646` AS `BK.10备库单号`, `b`.`Date1618859180209` AS `BK.11备库下单日期`, `b`.`StaffSelector1618885082387` AS `BK.15业务员`, `b`.`ShortText1618900704399` AS `BK.13备库仓库`, `b`.`ShortText1617861966146` AS `CGD.CP.26产品名称`, `b`.`ShortText1617861001482` AS `CGD.CP.27产品编号`, `b`.`ShortText1617865626160` AS `CGD.CP.28一级分类`, `b`.`ShortText1617865685744` AS `CGD.CP.25单位`, `b`.`ShortText1618240134229` AS `CGD.CP.36贵金属种类`, round(( round( ifnull( `b`.`Number1618330541286`, 0 ), 2 ) + ( SELECT ifnull( sum( pc.winv_fee ), 0 ) FROM (( `cfp_pay_info` `pc` LEFT JOIN `cfp_pay_stages` `ps` ON (( `ps`.`payNo` = `pc`.`payNo` ))) LEFT JOIN `cfp_pay` `p` ON (( `p`.`payNo` = `pc`.`payNo` ))) WHERE (( `pc`.`status` = 1 ) AND ( `ps`.`is_del` = 0 ) AND (( `p`.`status` = 2 ) OR ( `p`.`status` = 1 )) AND ( `ps`.`status` = 7 ) AND ( `pc`.`sequenceNo` = `b`.`sequenceNo` )))), 2 ) AS `CGD.HP.总-已回票金额`, round(( ifnull( `b`.`Number1618330543270`, 0 ) - ( SELECT ifnull( sum( pc.winv_fee ), 0 ) FROM (( `cfp_pay_info` `pc` LEFT JOIN `cfp_pay_stages` `ps` ON (( `ps`.`payNo` = `pc`.`payNo` ))) LEFT JOIN `cfp_pay` `p` ON (( `p`.`payNo` = `pc`.`payNo` ))) WHERE (( `pc`.`status` = 1 ) AND ( `ps`.`is_del` = 0 ) AND (( `p`.`status` = 2 ) OR ( `p`.`status` = 1 )) AND ( `ps`.`status` = 7 ) AND ( `pc`.`sequenceNo` = `b`.`sequenceNo` )))), 2 ) AS `CGD.HP.总-未回票金额`,( CASE WHEN (( round( ifnull( `b`.`Number1618330541286`, 0 ), 2 ) + ( SELECT ifnull( sum( pc.winv_fee ), 0 ) FROM (( `cfp_pay_info` `pc` LEFT JOIN `cfp_pay_stages` `ps` ON (( `ps`.`payNo` = `pc`.`payNo` ))) LEFT JOIN `cfp_pay` `p` ON (( `p`.`payNo` = `pc`.`payNo` ))) WHERE (( `pc`.`status` = 1 ) AND ( `ps`.`is_del` = 0 ) AND (( `p`.`status` = 2 ) OR ( `p`.`status` = 1 )) AND ( `ps`.`status` = 7 ) AND ( `pc`.`sequenceNo` = `b`.`sequenceNo` )))) = 0 ) THEN '未回票' WHEN (( round( ifnull( `b`.`Number1618330543270`, 0 ), 2 ) - ( SELECT ifnull( sum( pc.winv_fee ), 0 ) FROM (( `cfp_pay_info` `pc` LEFT JOIN `cfp_pay_stages` `ps` ON (( `ps`.`payNo` = `pc`.`payNo` ))) LEFT JOIN `cfp_pay` `p` ON (( `p`.`payNo` = `pc`.`payNo` ))) WHERE (( `pc`.`status` = 1 ) AND ( `ps`.`is_del` = 0 ) AND (( `p`.`status` = 2 ) OR ( `p`.`status` = 1 )) AND ( `ps`.`status` = 7 ) AND ( `pc`.`sequenceNo` = `b`.`sequenceNo` )) LIMIT 1 )) = 0 ) THEN '全部回票' ELSE '部分回票' END ) AS `CGD.HP.总-回票情况`,( SELECT `ps`.`invtime` FROM (( `cfp_pay_info` `pc` LEFT JOIN `cfp_pay_stages` `ps` ON (( `ps`.`payNo` = `pc`.`payNo` ))) LEFT JOIN `cfp_pay` `p` ON (( `p`.`payNo` = `pc`.`payNo` ))) WHERE (( `pc`.`status` = 1 ) AND ( `ps`.`is_del` = 0 ) AND (( `p`.`status` = 2 ) OR ( `p`.`status` = 1 )) AND ( `ps`.`status` = 7 ) AND ( `pc`.`sequenceNo` = `b`.`sequenceNo` )) ORDER BY `ps`.`invtime` DESC LIMIT 1 ) AS `CGD.HP.MX.总最近一次回票时间`,( SELECT sum( `pc`.`winv_fee`) FROM (((( `cfp_pay_info` `pc` LEFT JOIN `cfp_pay_stages` `ps` ON (( `ps`.`payNo` = `pc`.`payNo` ))) LEFT JOIN `cfp_pay` `p` ON (( `p`.`payNo` = `pc`.`payNo` ))) LEFT JOIN `cfp_invoice_img` `m` ON ((( `m`.`payid` = `ps`.`id` ) AND ( `m`.`is_del` = 0 )))) LEFT JOIN `cfp_invoice_info` `im` ON (( `im`.`invid` = `m`.`id` ))) WHERE (( `pc`.`status` = 1 ) AND ( `ps`.`is_del` = 0 ) AND (( `p`.`status` = 2 ) OR ( `p`.`status` = 1 )) AND ( `ps`.`status` = 7 ) AND ( `m`.`is_del` = 0 ) AND ( `pc`.`sequenceNo` = `b`.`sequenceNo` )) ) AS `CGD.HP.MX.回票金额`,( SELECT `im`.`buyer_name` FROM (((( `cfp_pay_info` `pc` LEFT JOIN `cfp_pay_stages` `ps` ON (( `ps`.`payNo` = `pc`.`payNo` ))) LEFT JOIN `cfp_pay` `p` ON (( `p`.`payNo` = `pc`.`payNo` ))) LEFT JOIN `cfp_invoice_img` `m` ON ((( `m`.`payid` = `ps`.`id` ) AND ( `m`.`is_del` = 0 )))) LEFT JOIN `cfp_invoice_info` `im` ON (( `im`.`invid` = `m`.`id` ))) WHERE (( `pc`.`status` = 1 ) AND ( `ps`.`is_del` = 0 ) AND (( `p`.`status` = 2 ) OR ( `p`.`status` = 1 )) AND ( `ps`.`status` = 7 ) AND ( `m`.`is_del` = 0 ) AND ( `pc`.`sequenceNo` = `b`.`sequenceNo` )) LIMIT 1 ) AS `CGD.HP.MX.回票抬头` FROM ( `source_all` `a` LEFT JOIN `cfp_cgd_info` `b` ON (( `a`.`cgdNo` = `b`.`ShortText1618315935182` ))) WHERE (( `a`.`cgdNo` <> '' ) {$where}) "; // echo $sql; die(); $list = Db::query($sql); if(empty($list)){ $list=[["未找到数据"=>""]]; } $header = array_keys($list[0]); array_walk($list, function (&$v) { $v = array_values($v); }); excelSave(date("Y-m-d",strtotime($start)).'至'.date("Y-m-d",strtotime($end))."回票数据报表", $header, $list); } /** 发货报表 */ public function FhReport(){ $post = request()->post(); $start = isset($post['start']) && $post['start'] != "" ? $post['start']." 00:00:00" : ''; $end = isset($post['end']) && $post['end'] != "" ? $post['end']." 23:59:59" : ''; $where=''; if($start!=""){ $where.=" and F.addtime >='{$start}'"; } if($end!=""){ $where.=" and F.addtime <='{$end}'"; } $sql = "SELECT F.fhNo AS '发货单号', F.cgdNO AS 'CGD采购单单号', F.qrdNo AS '确认单号', S.suppitem AS '平台类型', S.depart AS '业务部门', S.zxtype AS '咨询类型', F.addtime AS '发货时间', S.cat_f AS '一级品类', F.good_name AS '产品名称', F.post_company AS '物流公司', F.post_code AS '物流单号', F.send AS '本次发货数量', F.send * S.sale_price AS '本次发货金额', F.bala AS '剩余发货数量', F.bala * S.sale_price AS '剩余发货金额', S.sale_price AS '销售单价', S.order_num AS '销售数量', S.sale_total AS '销售金额', S.zxcode AS '咨询单号', S.supplier AS '供应商名称', S.cgd_saler AS '采购员' FROM fh_source AS F, source_all AS S WHERE F.cgdNO = S.cgdNO AND S.cgd_saler NOT IN ('张凯旋','骆喜龙') {$where} "; $list = Db::query($sql); if(empty($list)){ $list=[["未找到数据"=>""]]; } $header = array_keys($list[0]); array_walk($list, function (&$v) { $v = array_values($v); }); excelSave(date("Y-m-d",strtotime($start)).'至'.date("Y-m-d",strtotime($end))."发货数据报表", $header, $list); } }