seaBlueApi/Model/Order/MOrderStatistics.Class.php

<?php
/**
 * 订单统计相关的
 * Created by PhpStorm.
 * User: phperstar
 * Date: 2021/04/19
 * Time: 11:40
 */
namespace JinDouYun\Model\Order;


use JinDouYun\Dao\Order\DOrder;
use Mall\Framework\Core\StatusCode;
use Mall\Framework\Core\ResultWrapper;
use Mall\Framework\Core\ErrorCode;
use Mall\Framework\Factory;

use Jindouyun\Model\MBaseModel;
use JinDouYun\Model\GoodsManage\MSku;

use JinDouYun\Dao\Order\DOrderGoods;


class MOrderStatistics extends MBaseModel
{
    /**
     * @var int 单表数据量
     */
    private $cutTable = 200000;


    private $userCenterId;
    private $enterPriseId;
    private $objDOrderGoods;
    private $objDOrder;
    private $orderStatisticsTableName = 'order_statistics'; // 订单统计视图名字

    public function __construct($userCenterId, $enterPriseId)
    {
        parent::__construct($enterPriseId, $userCenterId);
        $this->userCenterId = $userCenterId;
        $this->enterPriseId = $enterPriseId;
        $this->objDOrder = new DOrder('default');

        $this->objDOrderGoods = new DOrderGoods('default');
        self::orderSubTable($enterPriseId, $userCenterId);

    }

    /**
     * 订单分表 分表规则:企业id_(客户/200000)
     * @param $enterpriseId (企业id)
     * @param $userId (用户id)
     * @throws \Exception
     */
    public function orderSubTable($enterpriseId, $userId)
    {
        $tableName = $this->objDOrder->getTableName('qianniao_order_' . $enterpriseId, $userId, $this->cutTable);
        self::existsTable($tableName);
        $this->objDOrder->setTable($tableName);
    }

    /**
     * 判断表是否存在
     * @param $tableName
     */
    public function existsTable($tableName)
    {
        $exists = $this->objDOrder->existsTable($tableName);
        if (!$exists) {
            //不存在删除视图
            self::dropView();
        }
    }

    /**
     * 删除订单视图
     */
    public function dropView()
    {
        $sql = 'DROP VIEW ' . $this->orderStatisticsTableName . '_' . $this->enterPriseId;
        $dbResult = $this->objDOrder->query($sql);
        if ($dbResult === false) {
            return ResultWrapper::fail($this->objDOrder->error(), ErrorCode::$dberror);
        }
        return ResultWrapper::success('删除成功');
    }

    /**
     * 订单汇总
     * @param $selectParams
     * @param $type
     * @return ResultWrapper
     * @throws \Exception
     */
    public function getAllOrderData($selectParams, $type, $export = 0)
    {

        $limit = $selectParams['limit'];
        unset($selectParams['limit']);
        $offset = $selectParams['offset'];
        unset($selectParams['offset']);
        if($export){
            $limit = 99999;
            $offset = 0;
        }
        $returnWhere = ' ';//查所有数据
        if ($selectParams['onReturn']) {//为真去除退货打开 查没退的数据
            $returnWhere = ' AND returnStatus != ' . StatusCode::$orderReturn['allReturn'] . ' AND detailsReturnStatus = ' . StatusCode::$orderReturn['notReturn'];//未退货
        }
        if ($type == 1) {//商品
            $groupBySql = ' GROUP BY goodsBasicId';
        } elseif ($type == 2) {//客户
            $groupBySql = ' GROUP BY customerId,goodsBasicId';
        } elseif ($type == 3) {//业务员
            $groupBySql = ' GROUP BY salesManId,goodsBasicId';
        } else {
            return ResultWrapper::fail('type参数错误', ErrorCode::$paramError);
        }

        $whereSql = '';
        $where = ' AND ';
        if (isset($selectParams['outStatus']) && !empty($selectParams['outStatus'])) {
            $whereSql .= $where . ' outStatus = ' . $selectParams['outStatus'];
        }
        if (isset($selectParams['goodsName']) && !empty($selectParams['goodsName'])) {
            $whereSql .= $where . ' goodsName LIKE "%' . $selectParams['goodsName'] . '%" ';
        }
        if (isset($selectParams['categoryId']) && !empty($selectParams['categoryId'])) {
            $whereSql .= $where . ' FIND_IN_SET(' . $selectParams['categoryId'] . ',categoryPath)';
        }
        if (isset($selectParams['customerId']) && !empty($selectParams['customerId'])) {
            $whereSql .= $where . ' customerId = ' . $selectParams['customerId'];
        }
        if (isset($selectParams['shopId']) && !empty($selectParams['shopId'])) {
            $whereSql .= $where . ' shopId = ' . $selectParams['shopId'];
        }
        if (isset($selectParams['start']) && !empty($selectParams['start']) && isset($selectParams['end']) && !empty($selectParams['end'])) {
            $whereSql .= $where . ' createTime BETWEEN ' . $selectParams['start'] . ' AND ' . $selectParams['end'];
        }
        //判断订单商品表是否存在
        $dbResult = $this->objDOrderGoods->existsTable('qianniao_order_goods_' . $this->enterPriseId . '_1');
        if (!$dbResult) {
            $return = [
                'data'  => [],
                'total' => 0,
            ];
            return ResultWrapper::success($return);
        }

        $sql = "SELECT outStatus,customerId,customerName,salesManId,salesManName,goodsBasicId,goodsId,goodsCode,goodsName,skuId,unitName,shopId,shopName,SUM(detailsBuyNum) AS num,SUM(detailsTotalMoney) AS totalMoney,originPrice,SUM(outCostPrice * detailsBuyNum) AS totalCost, SUM(outCostPrice * detailsBuyNum) / SUM(detailsBuyNum) AS costPrice,SUM(detailsTotalMoney - (outCostPrice * detailsBuyNum)) AS totalProfit, SUM(detailsTotalMoney - (outCostPrice * detailsBuyNum)) / SUM(detailsBuyNum) AS profit, SUM(otherNum) AS otherNum "
                . "FROM " . $this->orderStatisticsTableName . '_' . $this->enterPriseId . ' '
                . 'WHERE deleteStatus = ' . StatusCode::$standard . ' AND detailsDeleteStatus = ' . StatusCode::$standard . ' AND outStatus = ' . StatusCode::$standard 
                . $whereSql . ' ' 
                . $returnWhere 
                . $groupBySql . ' '
                . 'ORDER BY detailsCreateTime DESC '
                . 'LIMIT ' . $offset . ',' . $limit;
        //判断视图是否存在
        $databaseName = Factory::config()->get('db')['default']['dbname'];

        $existsSql = "select table_name from information_schema.views where TABLE_SCHEMA = '" . $databaseName . "' and table_name = '" . $this->orderStatisticsTableName . "_" . $this->enterPriseId . "'";
        $dbResult = $this->objDOrderGoods->query($existsSql);

        if($dbResult === false) {
            return ResultWrapper::fail($this->objDOrderGoods->error(), ErrorCode::$dberror);
        }
        $existsView = array_shift($dbResult);
        unset($dbResult);
        if (empty($existsView['TABLE_NAME'])) {
            $modelResult = self::setOrderView();
            if (!$modelResult->isSuccess()) {
                return ResultWrapper::fail($modelResult->getData(), $modelResult->getErrorCode());
            }
        }
        if($export){
            $dbResult = $this->objDOrderGoods->exportQuery($sql);
            if ($dbResult === false) {
                return ResultWrapper::fail($this->objDOrderGoods->error(), ErrorCode::$dberror);
            }
            $modelResult = self::formatOrderStatistics($dbResult);
            if(!$modelResult->isSuccess()){
                return ResultWrapper::fail($modelResult->getData(), $modelResult->getErrorCode());
            }
            $orderData = $modelResult->getData();
            unset($modelResult);
            self::exportOrderStatistics($orderData);
            exit;
        }
        $dbResult = $this->objDOrderGoods->query($sql);
        
        if ($dbResult === false) {
            return ResultWrapper::fail($this->objDOrderGoods->error(), ErrorCode::$dberror);
        }
        $modelResult = self::formatOrderStatistics($dbResult);
        if(!$modelResult->isSuccess()){
            return ResultWrapper::fail($modelResult->getData(), $modelResult->getErrorCode());
        }
        $orderData = $modelResult->getData();
        unset($modelResult);
        $countSql = 'SELECT COUNT(*) AS `count` FROM (SELECT orderId FROM ' . $this->orderStatisticsTableName . '_' . $this->enterPriseId . ' WHERE deleteStatus = ' . StatusCode::$standard . ' AND detailsDeleteStatus = ' . StatusCode::$standard . ' AND outStatus = ' . StatusCode::$standard . $whereSql . ' ' . $returnWhere . $groupBySql . ') AS selectData';
        $dbResult = $this->objDOrderGoods->query($countSql);
        if ($dbResult === false) {
            return ResultWrapper::fail($this->objDOrderGoods->error(), ErrorCode::$dberror);
        }
        $totalData = array_shift($dbResult);
        unset($dbResult);
        $total = $totalData['count'];
        //查询结果需要格式化分类名称
        $return = [
            'data'  => $orderData,
            'total' => ($total) ? intval($total) : 0,
        ];
        return ResultWrapper::success($return);
    }

    /**
     * 把所有的订单分包合并起来创建一个所有数据的视图
     * @throws \Exception
     */
    public function setOrderView()
    {
        $unionSql = '';
        $i = 1;
        while (true) {
            $orderTableName = 'qianniao_order_' . $this->enterPriseId . '_' . $i;
            $orderGoodsTableName = 'qianniao_order_goods_' . $this->enterPriseId . '_' . $i;
            $existsTable = $this->objDOrderGoods->existsTable($orderTableName);
            if (!$existsTable) {
                break;
            }

            //拼接sql
            $orderSql = "SELECT o.id AS orderId,o.no AS orderNo,o.shopId,o.shopName,o.outerTradeNo,o.totalMoney,o.payAmount,o.buyTotal,o.customerName,o.customerId,o.payStatus ,o.orderStatus,o.payType,o.deliveryType,o.source,o.remark,o.deleteStatus,o.auditStatus,o.returnStatus,o.outStatus,o.payTime,o.createTime,o.updateTime,o.preferentialActivityId AS orderPreferentialActivityId,o.salesManId,o.salesManName,o.customerType,o.userCenterId,g.id AS detailsId,g.goodsCode,g.goodsId,g.goodsName,g.originPrice,g.BuyNum AS detailsBuyNum,g.preferential,g.deliverNum,g.deleteStatus AS detailsDeleteStatus,g.returnStatus AS detailsReturnStatus,g.createTime AS detailsCreateTime,g.updateTime AS detailsUpdateTime,g.preferentialActivityId AS detailsPreferentialActivityId,g.totalMoney AS detailsTotalMoney,g.goodsBasicId,g.skuId,g.unitName,g.conversion,g.outCostPrice,g.categoryPath , JSON_EXTRACT(g.extends,'$.u_1_buy') AS otherNum FROM " . $orderTableName . ' o LEFT JOIN ' . $orderGoodsTableName . ' g ON o.id = g.orderId ';
            if (empty($unionSql)) {
                $unionSql = 'CREATE VIEW ' . $this->orderStatisticsTableName . '_' . $this->enterPriseId . ' AS ';
            } else {
                $unionSql .= ' UNION ALL ';
            }
            $unionSql .= $orderSql;
            $i++;
        }
        $dbResult = $this->objDOrderGoods->query($unionSql);
        if ($dbResult === false) {
            return ResultWrapper::fail($this->objDOrderGoods->error(), ErrorCode::$dberror);
        }
        return ResultWrapper::success('创建视图成功');
    }

    /**
     * 格式化销售报表
     * @param $params
     * @return ResultWrapper
     * @throws \Exception
     */
    public function formatOrderStatistics($params)
    {
        $skuIds = array_column($params, 'skuId');
        $skuData = [];
        if (!empty($skuIds)) {
            $objMSku = new MSku($this->userCenterId, $this->enterPriseId);
            $modelResult = $objMSku->getConversion($skuIds);
            if (!$modelResult->isSuccess()) {
                return ResultWrapper::fail($modelResult->getData(), $modelResult->getErrorCode());
            }
            $skuData = $modelResult->getData();
            unset($modelResult);
        }
        foreach ($params as &$value) {
            unset($value['unitId']);
            unset($value['unitName']);
            unset($value['skuName']);
            unset($value['skuBarCode']);
            $value['skuId'] = isset($skuData[$value['skuId']]['masterSkuId']) ? $skuData[$value['skuId']]['masterSkuId'] : $value['skuId'];
        }
        $return = parent::formatOrderMan($this->enterPriseId, $params);
        return ResultWrapper::success($return);
    }

    /**
     * 订单汇总导出方法
     * @param $result
     */
    public function exportOrderStatistics($result)
    {
        //导出到本地
        header("Content-type:application/vnd.ms-excel");
        header("Content-Disposition:filename=销售报表.csv");
        header('Cache-Control: max-age=0');

        $fp = fopen('php://output', 'a');

        $head = ['客户', '商品名称','商品规格', '店铺', '数量','其他单位', '销售收入', '成本', '总成本', '毛利', '总毛利'];       //定义标题

        foreach ($head as $i => $v) {
            $head[$i] = mb_convert_encoding($v, 'GBK', 'utf-8');     //将中文标题转换编码，否则乱码
        }

        fputcsv($fp, $head);

        $limit = 10000;
        $num = 0;//计数器
        foreach ($result as $value) {//循环数据
            $num++;
            if ($num == $limit) {
                ob_flush();//释放内存
                flush();
            }
            $rows['customerName'] = isset($value['customerName']) ? $value['customerName'] : null;
            $rows['goodsName'] = isset($value['goodsName']) ? $value['goodsName'] : null;
            $rows['unitName'] = isset($value['unitName']) ? $value['skuName'].(!empty($value['skuName']) ? '_' : '').$value['unitName'] : null;
            $rows['shopName'] = isset($value['shopName']) ? $value['shopName'] : null;
            $rows['num'] = isset($value['num']) ? $value['num'] : null;
            $rows['otherNum'] = isset($value['otherNum']) ? $value['otherNum'] : null;
            $rows['totalMoney'] = isset($value['totalMoney']) ? $value['totalMoney'] : null;
            $rows['costPrice'] = isset($value['costPrice']) ? $value['costPrice'] : null;
            $rows['totalCost'] = isset($value['totalCost']) ? $value['totalCost'] : null;
            $rows['profit'] = isset($value['profit']) ? $value['profit'] : null;
            $rows['totalProfit'] = isset($value['totalProfit']) ? $value['totalProfit'] : null;

            foreach ($rows as $kk => $vv) {
                $rs[$kk] = mb_convert_encoding($vv, 'GBK', 'utf-8');  //转译编码
            }
            fputcsv($fp, $rs);
            $rows = [];
        }
    }
}