huahuibao/biz-ghs/stat/classes/StatCgClass.php

<?php
/**
 * 采购统计：花材排行 / 采购人业绩。
 * 口径对齐销售跨天售后：入库日用 nextRefundNum 算净量；通过日再扣隔天金额/数量（可为负，笔数不减）。
 */
namespace bizGhs\stat\classes;

use bizGhs\cg\classes\CgPurchaseNextDayClass;
use bizGhs\order\classes\PurchaseOrderClass;
use bizGhs\order\classes\PurchaseOrderItemClass;
use bizGhs\product\classes\ProductClass;
use common\components\arrayUtil;
use common\components\dateUtil;
use common\components\util;
use Yii;
use bizGhs\base\classes\BaseClass;

class StatCgClass extends BaseClass
{

    /**
     * 采购花材排行（兼返回采购人按明细汇总，供兼容旧调用）
     * @param int $mainId
     * @return array{itemList:array,staffCg:array}
     */
    public static function statCg($mainId)
    {
        $get = Yii::$app->request->get();
        $where = [];
        $where['mainId'] = $mainId;
        $searchTime = isset($get['searchTime']) && !empty($get['searchTime']) ? $get['searchTime'] : 'today';

        $startTime = $get['startTime'] ?? '';
        $endTime = $get['endTime'] ?? '';
        $period = dateUtil::formatTime($searchTime, $startTime, $endTime, true);
        $start = $period['startTime'];
        $end = $period['endTime'];
        $where['time'] = ['between', [$start, $end]];

        $currentStartDate = date('Y-m-d', strtotime($start));
        $currentEndDate = date('Y-m-d', strtotime($end));
        $currentStartTime = $currentStartDate . ' 00:00:00';
        $currentEndTime = $currentEndDate . ' 23:59:59';

        $sort = $get['sort'] ?? 'num';
        $rank = 'num';
        if ($sort == 'amount') {
            $rank = 'amount';
        }

        $productInfo = ProductClass::getAllByCondition(['mainId' => $mainId], null, 'id,name,py', 'id');

        // timeType=bill（默认记账 entryTime）/ send（发货 sendTime）
        // status=4 已入库；发货口径也必须带，因为 sendTime 不代表已入库
        $timeType = isset($get['timeType']) ? strval($get['timeType']) : 'bill';
        $cgTimeField = ($timeType === 'send') ? 'sendTime' : 'entryTime';

        $cgWhere = ['mainId' => $mainId, 'status' => 4];
        $cgWhere[$cgTimeField] = ['between', [$currentStartTime, $currentEndTime]];
        $cgList = PurchaseOrderClass::getAllByCondition($cgWhere, null, '*');
        $arr = [];
        $cgData = [];
        if (!empty($cgList)) {
            foreach ($cgList as $cgKey => $cgInfo) {
                $orderSn = $cgInfo['orderSn'] ?? '';
                $cgStaffId = $cgInfo['cgStaffId'] ?? 0;
                $cgStaffName = $cgInfo['cgStaffName'] ?? '';
                $itemList = PurchaseOrderItemClass::getAllByCondition(['orderSn' => $orderSn], null, '*', null, true);
                if (!empty($itemList)) {
                    foreach ($itemList as $key => $val) {
                        $productId = $val->productId ?? 0;
                        $num = $val->itemNum ?? 0;
                        $refundNum = $val->refundNum ?? 0;
                        // 入库日净量：隔天已退记在 nextRefundNum，不在入库日扣
                        $remainNum = CgPurchaseNextDayClass::payDayRemainNum(
                            $num,
                            $refundNum,
                            $val->nextRefundNum ?? 0
                        );
                        $unitPrice = $val->bigPrice ?? 0;
                        $name = $productInfo[$productId]['name'] ?? '';
                        $py = $productInfo[$productId]['py'] ?? '';
                        $totalPrice = bcmul($remainNum, $unitPrice, 2);
                        if (isset($arr[$productId])) {
                            $arr[$productId]['num'] = bcadd($arr[$productId]['num'], $remainNum);
                            $arr[$productId]['amount'] = bcadd($arr[$productId]['amount'], $totalPrice, 2);
                        } else {
                            $arr[$productId]['num'] = $remainNum;
                            $arr[$productId]['amount'] = $totalPrice;
                            $arr[$productId]['py'] = $py;
                            $arr[$productId]['name'] = $name;
                        }
                        if (isset($cgData[$cgStaffId])) {
                            $cgData[$cgStaffId]['amount'] = bcadd($cgData[$cgStaffId]['amount'], $totalPrice, 2);
                            $cgData[$cgStaffId]['itemNum'] = bcadd($cgData[$cgStaffId]['itemNum'], $remainNum);
                        } else {
                            $cgData[$cgStaffId]['cgStaffId'] = $cgStaffId;
                            $cgData[$cgStaffId]['cgStaffName'] = $cgStaffName;
                            $cgData[$cgStaffId]['amount'] = $totalPrice;
                            $cgData[$cgStaffId]['num'] = 0;
                            $cgData[$cgStaffId]['itemNum'] = $remainNum;
                        }
                    }
                    if (isset($cgData[$cgStaffId])) {
                        $cgData[$cgStaffId]['num'] = bcadd($cgData[$cgStaffId]['num'], 1);
                    } else {
                        $cgData[$cgStaffId]['num'] = 1;
                    }
                }
            }
        }

        // 跨天售后：按通过日扣花材数量/金额；无当日入库也建负行
        self::applyNextDayAdjustToCgItemRows($arr, $productInfo, $mainId, $currentStartTime, $currentEndTime);
        $nextDayDeduct = CgPurchaseNextDayClass::sumAmountByMainAndTime($mainId, $currentStartTime, $currentEndTime);

        if (!empty($arr)) {
            // 去掉数量金额都为 0 的行，保留负数行便于看见隔天扣减
            $arr = array_values(array_filter($arr, function ($row) {
                $num = bcadd((string)($row['num'] ?? '0'), '0', 2);
                $amount = bcadd((string)($row['amount'] ?? '0'), '0', 2);
                return bccomp($num, '0', 2) != 0 || bccomp($amount, '0', 2) != 0;
            }));
            $arr = arrayUtil::arraySort($arr, $rank);
        }
        if (!empty($cgData)) {
            $cgData = array_values($cgData);
        }

        $export = $get['export']??0;
        if($export == 1){
            self::exportCgItem($arr,$mainId);
        }

        return ['itemList' => $arr, 'staffCg' => $cgData, 'nextDayDeduct' => $nextDayDeduct];
    }

    /**
     * 通过日扣减采购花材：退货退款扣数量+金额；仅退款只扣金额（按原单占比分摊）
     *
     * @param array $arr productId => 行
     * @param array $productInfo 花材字典
     * @param int $mainId
     * @param string $startTime
     * @param string $endTime
     */
    private static function applyNextDayAdjustToCgItemRows(
        &$arr,
        $productInfo,
        $mainId,
        $startTime,
        $endTime
    ) {
        $applyDelta = function ($pidMap, $sign) use (&$arr, $productInfo) {
            foreach ($pidMap as $pid => $row) {
                $deltaNum = bcmul((string)($row['num'] ?? '0'), (string)$sign, 2);
                $deltaAmt = bcmul((string)($row['amount'] ?? '0'), (string)$sign, 2);
                if (bccomp($deltaAmt, '0', 2) == 0 && bccomp($deltaNum, '0', 2) == 0) {
                    continue;
                }
                if (!isset($arr[$pid])) {
                    $arr[$pid] = [
                        'num' => '0.00',
                        'amount' => '0.00',
                        'py' => $productInfo[$pid]['py'] ?? '',
                        'name' => $productInfo[$pid]['name'] ?? '已删除',
                    ];
                }
                $arr[$pid]['num'] = bcadd((string)($arr[$pid]['num'] ?? 0), $deltaNum, 2);
                $arr[$pid]['amount'] = bcadd((string)($arr[$pid]['amount'] ?? 0), $deltaAmt, 2);
            }
        };

        $applyDelta(CgPurchaseNextDayClass::sumItemByProduct($mainId, $startTime, $endTime), -1);
        $applyDelta(CgPurchaseNextDayClass::sumMoneyOnlyAmountByProduct($mainId, $startTime, $endTime), -1);
    }

    public static function exportCgItem($list, $mainId)
    {
        $phpExcelFile = Yii::getAlias("@vendor/phpoffice/phpexcel/");
        require_once($phpExcelFile . 'Classes/PHPExcel.php');

        $objPHPExcel = new \PHPExcel();

        $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
            ->setLastModifiedBy("Maarten Balliauw")
            ->setTitle("Office 2007 XLSX Document")
            ->setSubject("Office 2007 XLSX Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("file");

        $ghsTitle = '供货商';
        $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader(date('n月j日') . " " . $ghsTitle);

        $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.7);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.1);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.5);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setHeader(0.1);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setFooter(0);

        $objPHPExcel->getActiveSheet()->setCellValue('A1', '名称');
        $objPHPExcel->getActiveSheet()->setCellValue('B1', '数量');
        $objPHPExcel->getActiveSheet()->setCellValue('C1', '金额');

        //设置宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(9)->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(9)->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('C1')->getFont()->setSize(9)->setBold(true);

        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $objPHPExcel->getActiveSheet()->getStyle('C1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

        $baseRow = 2;
        foreach ($list as $key => $custom) {
            $i = $baseRow + $key;
            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $custom['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $custom['num']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $custom['amount']);
            //居左
            $objPHPExcel->getActiveSheet()->getStyle('A' . $i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
            $objPHPExcel->getActiveSheet()->getStyle('B' . $i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        }
        // 文件名带上记账/发货口径，和页面「导出记账时间」对齐
        $timeTypeName = Yii::$app->request->get('timeType') === 'send' ? '发货时间' : '记账时间';
        $fileName = '采购花材-' . $timeTypeName . '-' . date("m-d");
        $objPHPExcel->getActiveSheet()->setTitle($fileName);
        $objPHPExcel->setActiveSheetIndex(0);
        $dir = './priceTable/' . $mainId;
        if (file_exists($dir) == false) {
            mkdir($dir, 0777, true);
        }
        $date = $fileName;
        $file = $date . '.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        if (file_exists($dir . '/' . $file)) {
            unlink($dir . '/' . $file);
        }
        $objWriter->save($dir . '/' . $file);
        $fileUrl = Yii::$app->params['ghsHost'] . '/priceTable/' . $mainId . '/' . $file;
        util::success(['file' => $fileUrl, 'shortFile' => $file]);
    }

    /**
     * 采购人业绩：入库日按 actPrice（当天售后已减实付）；跨天售后按通过日扣金额，笔数不减。
     * @param int $mainId
     * @return array{staffCg:array}
     */
    public static function statCgYj($mainId)
    {
        $get = Yii::$app->request->get();
        $where = [];
        $where['mainId'] = $mainId;
        $searchTime = isset($get['searchTime']) && !empty($get['searchTime']) ? $get['searchTime'] : 'today';

        $startTime = $get['startTime'] ?? '';
        $endTime = $get['endTime'] ?? '';
        $period = dateUtil::formatTime($searchTime, $startTime, $endTime, true);
        $start = $period['startTime'];
        $end = $period['endTime'];
        $where['time'] = ['between', [$start, $end]];

        $currentStartDate = date('Y-m-d', strtotime($start));
        $currentEndDate = date('Y-m-d', strtotime($end));
        $currentStartTime = $currentStartDate . ' 00:00:00';
        $currentEndTime = $currentEndDate . ' 23:59:59';

        // timeType=bill（默认记账 entryTime）/ send（发货 sendTime）
        // status=4 已入库；发货口径也必须带，因为 sendTime 不代表已入库
        $timeType = isset($get['timeType']) ? strval($get['timeType']) : 'bill';
        $cgTimeField = ($timeType === 'send') ? 'sendTime' : 'entryTime';

        $cgWhere = ['mainId' => $mainId, 'status' => 4];
        $cgWhere[$cgTimeField] = ['between', [$currentStartTime, $currentEndTime]];
        $cgList = PurchaseOrderClass::getAllByCondition($cgWhere, null, '*');

        $cgData = [];
        if (!empty($cgList)) {
            foreach ($cgList as $cgKey => $cgInfo) {
                $cgStaffId = $cgInfo['cgStaffId'] ?? 0;
                $cgStaffName = $cgInfo['cgStaffName'] ?? '';
                $actPrice = $cgInfo['actPrice'] ?? 0;
                if (isset($cgData[$cgStaffId])) {
                    $cgData[$cgStaffId]['amount'] = bcadd($cgData[$cgStaffId]['amount'], $actPrice, 2);
                    $cgData[$cgStaffId]['num'] = bcadd($cgData[$cgStaffId]['num'], 1);
                } else {
                    $cgData[$cgStaffId]['cgStaffId'] = $cgStaffId;
                    $cgData[$cgStaffId]['cgStaffName'] = $cgStaffName;
                    $cgData[$cgStaffId]['amount'] = $actPrice;
                    $cgData[$cgStaffId]['num'] = 1;
                }
            }
        }

        // 跨天售后：按原单采购人扣金额；无当日入库也建负行（笔数不减）
        $nextDayDeduct = CgPurchaseNextDayClass::sumAmountByMainAndTime($mainId, $currentStartTime, $currentEndTime);
        $nextMap = CgPurchaseNextDayClass::sumAmountGroupByCgStaff($mainId, $currentStartTime, $currentEndTime);
        foreach ($nextMap as $sid => $row) {
            $amt = $row['amount'] ?? '0';
            if (bccomp((string)$amt, '0', 2) <= 0) {
                continue;
            }
            if (!isset($cgData[$sid])) {
                $cgData[$sid] = [
                    'cgStaffId' => $sid,
                    'cgStaffName' => $row['cgStaffName'] !== '' ? $row['cgStaffName'] : '未记名',
                    'amount' => '0.00',
                    'num' => 0,
                ];
            }
            $cgData[$sid]['amount'] = bcsub((string)$cgData[$sid]['amount'], (string)$amt, 2);
        }

        if (!empty($cgData)) {
            // 去掉订单数与金额都为 0 的行，保留纯隔天负金额行
            $cgData = array_values(array_filter($cgData, function ($row) {
                $num = bcadd((string)($row['num'] ?? '0'), '0', 2);
                $amount = bcadd((string)($row['amount'] ?? '0'), '0', 2);
                return bccomp($num, '0', 2) != 0 || bccomp($amount, '0', 2) != 0;
            }));
        }
        return ['staffCg' => $cgData, 'nextDayDeduct' => $nextDayDeduct];
    }

}