seaBlueApi/Model/Commission/MCommissionOrder.Class.php

<?php


namespace JinDouYun\Model\Commission;

use JinDouYun\Controller\Common\Logger;
use JinDouYun\Dao\Commission\DCommissionOrderIndex;
use JinDouYun\Dao\Customer\DCustomer;
use JinDouYun\Dao\Order\DOrderGoods;
use JinDouYun\Dao\Order\DOrderIndex;
use Mall\Framework\Core\StatusCode;
use JinDouYun\Dao\Commission\DCommissionOrder;
use Mall\Framework\Core\ErrorCode;
use Mall\Framework\Core\ResultWrapper;

/**
 * Description:
 * Class MCommissionOrder
 * @package JinDouYun\Model\Commission
 */
class MCommissionOrder
{
    /**
     * @var DCommissionOrder
     */
    private $objDCommissionOrder;

    /**
     * @var int
     */
    private $onlineEnterpriseId;

    /**
     * @var int
     */
    private $onlineUserId;

    private static $retLevel = [
        0 => '',
        1 => '一级返佣',
        2 => '二级返佣',
        3 => '三级返佣'
    ];

    /**
     * @var array
     */
    private static $isAccount = [
        3 => '已取消',
        4 => '等待入账',
        5 => '已入账'
    ];

    /**
     * @var DCustomer
     */
    private $objDCustomer;

    /**
     * @var DCommissionOrderIndex
     */
    private $objDCommissionOrderIndex;

    /**
     * @var DOrderGoods
     */
    private $objDOrderGoods;

    /**
     * @var int 单表数据量
     */
    private $cutTable = 200000;

    /**
     * @var DOrderIndex
     */
    private $objDOrderIndex;

    /**
     * MCommissionOrder constructor.
     * @param $onlineEnterpriseId
     * @param $onlineUserId
     * @throws \Exception
     */
    public function __construct($onlineEnterpriseId, $onlineUserId)
    {
        $this->onlineEnterpriseId = $onlineEnterpriseId;
        $this->onlineUserId = $onlineUserId;
        $this->objDCommissionOrder = new DCommissionOrder();
        $this->objDCommissionOrder->setTable('qianniao_commission_order_' . $this->onlineEnterpriseId . '_' . date('Y') . '_' . ceil(date('m') / 3));
        $this->objDCustomer = new DCustomer();
        $this->objDCustomer->setTable('qianniao_customer_'.$this->onlineEnterpriseId);
        $this->objDCommissionOrderIndex = new DCommissionOrderIndex();
        $this->objDCommissionOrderIndex->setTable('qianniao_commission_order_index_'.$this->onlineEnterpriseId);
        $this->objDOrderIndex = new DOrderIndex();
        $this->objDOrderIndex->setTable('qianniao_order_index_'.$this->onlineEnterpriseId);
        $this->objDOrderGoods = new DOrderGoods();
    }

    public function __destruct()
    {
        // TODO: Implement __destruct() method.
    }

    /**
     * Doc: (des="分销商品记录")
     * User: XMing
     * Date: 2020/7/24
     * Time: 4:23 下午
     * @param array $selectParams
     * @return ResultWrapper
     */
    public function getAll(array $selectParams)
    {
        $fields = 'c.*,o.auditStatus,o.orderStatus,i.isAccount';
        $commissionOrderTableName = 'qianniao_commission_order_' . $this->onlineEnterpriseId . '_' . date('Y') . '_' . ceil(date('m') / 3);
        $sql = 'SELECT '.$fields.' FROM '.$commissionOrderTableName.' as c
                LEFT JOIN qianniao_order_index_'.$this->onlineEnterpriseId. ' as o 
                ON o.id = c.orderId 
                LEFT JOIN qianniao_commission_order_index_'.$this->onlineEnterpriseId.' as i 
                ON i.orderId = c.orderId 
                WHERE c.id IS NOT NULL';

        if(isset($selectParams['state']) && !empty($selectParams['state'])){
            $stateSql = self::stateSql($selectParams['state']);
            $sql .= empty($stateSql) ? '' : ' and '.$stateSql;
        }
        if (isset($selectParams['customerId']) && !empty($selectParams['customerId'])){
            $sql .= ' AND c.customerId = '.$selectParams['customerId'];
        }
        if (isset($selectParams['no']) && !empty($selectParams['no'])){
            $sql .= ' AND c.orderNo = '.$selectParams['no'];
        }
        if (isset($selectParams['goodsName']) && !empty($selectParams['goodsName'])){
            $sql .= ' AND c.goodsName = "'.$selectParams['goodsName'].'"';
        }
        if (isset($selectParams['orderStatus']) && !empty($selectParams['orderStatus'])){
            $sql .= ' AND o.orderStatus = '.$selectParams['orderStatus'];
        }
        if (isset($selectParams['isAccount']) && !empty($selectParams['isAccount'])){
            $sql .= ' AND i.isAccount = '.$selectParams['isAccount'];
        }
        if (isset($selectParams['startTime']) && !empty($selectParams['startTime']) && isset($selectParams['endTime']) && !empty($selectParams['endTime'])){
            $sql .= ' AND c.createTime BETWEEN '.$selectParams['startTime'].' and '.$selectParams['endTime'];
        }

        $total = count((array) $this->objDCommissionOrder->query($sql));
        //排序规则
        if (isset($selectParams['orderBy']) && empty($selectParams['orderBy'])){
            $sql .= ' GROUP BY c.id order by '.$selectParams['orderBy'];
        }else{
            $sql .= ' GROUP BY c.id order by c.createTime DESC';
        }

        //导出不需要分页
        if( isset($selectParams['export']) && !empty($selectParams['export']) ){ //导出
            $commissionList = $this->objDCommissionOrder->query($sql);
            if ($commissionList === false){
                return ResultWrapper::fail($this->objDCommissionOrder->error(),ErrorCode::$dberror);
            }
            if(empty($commissionList)){
                return ResultWrapper::success('导出数据为空');
            }
            self::exportCommissionOrder($commissionList);
        }

        $sql .= ' limit '.$selectParams['offset'].','.$selectParams['limit'];//分页

        $commissionList = $this->objDCommissionOrder->query($sql);
        if ($commissionList === false){
            return ResultWrapper::fail($this->objDCommissionOrder->error(),ErrorCode::$dberror);
        }
        $formatResult = self::formatList((array)$commissionList);
        if (!$formatResult->isSuccess()){
            return ResultWrapper::fail($formatResult->getData(),$formatResult->getErrorCode());
        }
        $return = [
            'total' => $total,
            'data' => $formatResult->getData()
        ];
        return ResultWrapper::success($return);
    }

    /**
     * Doc: (des="state标示对应sql")
     * User: XMing
     * Date: 2020/7/13
     * Time: 9:43 上午
     * @param string $state
     * @return string
     */
    private static function stateSql(string $state)
    {
        $sql = '';
        switch ($state) {
            case 'waitAudit'://待审核
                $sql .= ' (o.auditStatus = ' . StatusCode::$auditStatus['auditing'] . ' and o.orderStatus != ' . StatusCode::$orderStatus['close'] . ')';
                break;
            case 'waitOutStock'://待出库
                $sql .= ' (o.auditStatus = ' . StatusCode::$auditStatus['auditPass'] . ' and (o.orderStatus = ' . StatusCode::$orderStatus['waitPay'] . ' or o.orderStatus = ' . StatusCode::$orderStatus['waitDelivery'] . ')) ';
                break;
            case 'hasOutStock'://已出库
                $sql .= ' (o.auditStatus = ' . StatusCode::$auditStatus['auditPass'] . ' and o.orderStatus = ' . StatusCode::$orderStatus['waitReceive'] . ')';
                break;
            case 'finish'://已完成
                $sql .= ' (o.auditStatus = ' . StatusCode::$auditStatus['auditPass'] . ' and o.orderStatus = ' . StatusCode::$orderStatus['finish'] . ')';
                break;
            case 'close'://已关闭
                $sql .= ' (o.orderStatus = ' . StatusCode::$orderStatus['close'] . ')';
                break;
            case 'all'://全部订单
                break;
        }
        return $sql;
    }

    /**
     * Doc: (des="格式化")
     * User: XMing
     * Date: 2020/7/29
     * Time: 4:06 下午
     * @param array $data
     * @return ResultWrapper
     */
    public function formatList(array $data)
    {
        if (empty($data)){
            return ResultWrapper::success([]);
        }

        $customerIds = [];
        $retCustomerIds = [];
        foreach ($data as $datum){
            $customerIds[] = $datum['customerId'];
            $retCustomerIds[] = $datum['retCustomerId'];
        }
        $allCustomerIds = array_merge($customerIds,$retCustomerIds);//全部客户id
        //获取客户的名称
        $customerLists = $this->objDCustomer->select(['id'=>$allCustomerIds],'id,name');
        if ($customerIds === false){
            return ResultWrapper::fail($this->objDCustomer->error(),ErrorCode::$dberror);
        }
        $customerRelNameMap = [];
        foreach ($customerLists as $list){
            $customerRelNameMap[$list['id']] = $list['name'];
        }
        $data = self::formatOrderStatus($data);
        foreach ($data as $key => &$value){
            is_string($value['specGroup']) && $data[$key]['specGroup'] = json_decode($value['specGroup'],true);
            $value['selfMsg'] = $value['isSelf'] == StatusCode::$standard ? '自购返佣' : '';
            $value['retLevelMsg'] = self::$retLevel[$value['retLevel']];
            $value['customerName'] = isset($customerRelNameMap[$value['customerId']]) ? $customerRelNameMap[$value['customerId']] : '';
            $value['retCustomerName'] = isset($customerRelNameMap[$value['retCustomerId']]) ? $customerRelNameMap[$value['retCustomerId']] : '';
            $value['retMsg'] = isset(self::$isAccount[$value['isAccount']]) ? self::$isAccount[$value['isAccount']] : '';
        }

        return ResultWrapper::success($data);
    }

    /**
     * 格式化订单状态
     * @param $data
     * @return mixed
     * @return ResultWrapper
     */
    private static function formatOrderStatus($data)
    {
        if (empty($data)) return $data;
        foreach ($data as $key => $value) {
            if ($value['auditStatus'] == StatusCode::$auditStatus['auditing']) {
                $data[$key]['orderMsg'] = '待审核';
            }
            if ($value['auditStatus'] == StatusCode::$auditStatus['auditPass']) {
                $data[$key]['orderMsg'] = '待出库';
                if ($value['orderStatus'] == StatusCode::$orderStatus['waitReceive']) {
                    $data[$key]['orderMsg'] = '已出库';
                }
                if ($value['orderStatus'] == StatusCode::$orderStatus['finish']) {
                    $data[$key]['orderMsg'] = '已完成';
                }
            }
            if ($value['orderStatus'] == StatusCode::$orderStatus['close']) {
                $data[$key]['orderMsg'] = '已关闭';
            }
        }
        return $data;
    }

    /**
     * Doc: (des="分销中心订单")
     * User: XMing
     * Date: 2020/7/30
     * Time: 10:51 上午
     * @param array $selectParams
     * @return ResultWrapper
     *
     * @throws \Exception
     */
    public function getCommissionOrder(array $selectParams)
    {
        $fields = 'c.id,o.id as orderId,o.orderStatus,o.payStatus,o.createTime,o.payType,o.deliveryType,
                   c.payAmount,c.orderNo,c.retMoney,c.retLevel,c.retUserCenterId,c.retCustomerId,c.userCenterId,c.customerId';
        $sql = 'SELECT '.$fields.' FROM qianniao_commission_order_index_'.$this->onlineEnterpriseId.' as c 
                LEFT JOIN qianniao_order_index_'.$this->onlineEnterpriseId.' as o 
                ON o.id = c.orderId 
                WHERE c.retUserCenterId = '.$this->onlineUserId;

        if (isset($selectParams['orderStatus']) && !empty($selectParams['orderStatus'])){
            if (is_array($selectParams['orderStatus'])){
                $orderStatusStr = implode(',',$selectParams['orderStatus']);
                $sql .= ' AND o.orderStatus IN('.$orderStatusStr.') ';
            }else{
                $sql .= ' AND o.orderStatus = '.$selectParams['orderStatus'];
            }
        }

        $sql .= ' order by c.createTime desc';

        $count = count((array)$this->objDCommissionOrderIndex->query($sql));

        //分页
        if (isset($selectParams['limit']) && isset($selectParams['offset'])) {
            $sql .= ' limit ' . $selectParams['offset'] . ',' . $selectParams['limit'];
        }

        $list = $this->objDCommissionOrderIndex->query($sql);
        if ($list === false){
            return ResultWrapper::fail($this->objDCommissionOrderIndex->error(),ErrorCode::$dberror);
        }
        if (empty($list)){
            return ResultWrapper::success([
                'data' => [],
                'total' => 0
            ]);
        }
        $formatResult = self::formatApiOrderList((array)$list);
        if (!$formatResult->isSuccess()){
            return ResultWrapper::fail($formatResult->getData(),$formatResult->getErrorCode());
        }

        $ret = [
            'data' => $formatResult->getData(),
            'total' => $count
        ];
        return ResultWrapper::success($ret);
    }

    /**
     * Doc: (des="")
     * User: XMing
     * Date: 2020/7/30
     * Time: 3:41 下午
     * @param array $data
     * @return ResultWrapper
     * @throws \Exception
     */
    private function formatApiOrderList(array $data)
    {
        $allGroupData = [];
        $allCustomerIds = [];
        foreach ($data as $key => $val) {
            $tableNum = ceil($val['userCenterId'] / $this->cutTable);
            $allGroupData[$tableNum][] = $val['orderId'];
            $allCustomerIds[] = $val['customerId'];
            $allCustomerIds[] = $val['retCustomerId'];
        }

        //查询这些订单的商品数据
        $allOrderGoodsData = [];
        foreach ($allGroupData as $k => $orderId) {
            $this->objDOrderGoods->setTable('qianniao_order_goods_' . $this->onlineEnterpriseId . '_' . $k);
            $dbResult = $this->objDOrderGoods->select(
                ['orderId' => $orderId],
                'orderId,goodsName,goodsId,goodsCode,skuId,shopName,price,buyNum,preferential,totalMoney,specGroup,goodsImages,unitName',
                'createTime desc'
            );//查询订单
            if ($dbResult === false) {
                Logger::logs(E_USER_ERROR, 'sql错误', __CLASS__, __LINE__, $this->objDOrderGoods->error());
                return ResultWrapper::fail($this->objDOrderGoods->error(), ErrorCode::$dberror);
            }
            $allOrderGoodsData = array_merge($allOrderGoodsData,$dbResult);
        }

        $orderGoodsMap = [];//订单商品数据集合
        foreach ($allOrderGoodsData as $value){
            $value['specGroup'] = empty($value['specGroup']) ? [] : json_decode($value['specGroup'],true);
            $orderGoodsMap[$value['orderId']][] = $value;
        }


        $customer = $this->objDCustomer->select(['id' => $allCustomerIds],'id,name');
        if ($customer === false){
            return ResultWrapper::fail($this->objDCustomer->error(),ErrorCode::$dberror);
        }
        $customerMap = [];
        foreach ($customer as $value){
            $customerMap[$value['id']] = $value['name'];
        }
        foreach ($data as &$datum){
            $datum['selfMsg'] = '';
            if ($datum['userCenterId'] == $datum['retUserCenterId']){
                $datum['selfMsg'] = '自购返佣';
            }
            $datum['retLevelMsg'] = isset(self::$retLevel[$datum['retLevel']]) ?  self::$retLevel[$datum['retLevel']] : '';
            $datum['orderGoods'] = isset($orderGoodsMap[$datum['orderId']]) ? $orderGoodsMap[$datum['orderId']] : [];
            $datum['customerName'] = isset($customerMap[$datum['customerId']]) ? $customerMap[$datum['customerId']] : '';
            $datum['retCustomerName'] = isset($customerMap[$datum['retCustomerId']]) ? $customerMap[$datum['retCustomerId']] : '';
        }

        return ResultWrapper::success($data);
    }

    /**
     * Doc: (des="分销订单详情")
     * User: XMing
     * Date: 2020/7/30
     * Time: 4:07 下午
     * @param int $id
     *
     * @throws \Exception
     * @return ResultWrapper
     */
    public function getCommissionOrderInfo(int $id)
    {
        $fields = 'c.id,o.id as orderId,o.orderStatus,o.payStatus,o.createTime,o.payType,o.deliveryType,
                   c.payAmount,c.orderNo,c.retMoney,c.retLevel,c.retUserCenterId,c.retCustomerId,c.userCenterId,c.customerId';
        $sql = 'SELECT '.$fields.' FROM qianniao_commission_order_index_'.$this->onlineEnterpriseId.' as c 
                LEFT JOIN qianniao_order_index_'.$this->onlineEnterpriseId.' as o 
                ON o.id = c.orderId 
                WHERE c.id = '.$id;
        $info = $this->objDCommissionOrderIndex->query($sql);
        if ($info === false){
            return ResultWrapper::fail($this->objDCommissionOrderIndex->error(),ErrorCode::$dberror);
        }
        $info = array_shift($info);
        if (empty($info)){
            return ResultWrapper::fail('未找到订单信息',ErrorCode::$paramError);
        }
        $formatResult = self::formatDetails($info);
        if (!$formatResult->isSuccess()){
            return ResultWrapper::fail($formatResult->getData(),$formatResult->getErrorCode());
        }

        return ResultWrapper::success($formatResult->getData());
    }

    /**
     * Doc: (des="")
     * User: XMing
     * Date: 2020/7/30
     * Time: 4:15 下午
     * @param array $data
     * @throws \Exception
     * @return ResultWrapper
     */
    private function formatDetails(array $data)
    {
        $fix = ceil($data['userCenterId'] / $this->cutTable);
        $this->objDOrderGoods->setTable('qianniao_order_goods_' . $this->onlineEnterpriseId . '_' . $fix);
        $orderGoods = $this->objDOrderGoods->select(['orderId' => $data['orderId']],'orderId,goodsName,goodsId,goodsCode,skuId,shopName,price,buyNum,preferential,totalMoney,specGroup,goodsImages,unitName');
        if ($orderGoods === false) {
            Logger::logs(E_USER_ERROR, 'sql错误', __CLASS__, __LINE__, $this->objDOrderGoods->error());
            return ResultWrapper::fail($this->objDOrderGoods->error(), ErrorCode::$dberror);
        }
        foreach ($orderGoods as &$value){
            $value['specGroup'] = empty($value['specGroup']) ? [] : json_decode($value['specGroup'],true);
        }
        unset($value);
        $data['orderGoods'] = $orderGoods;
        //获取客户名称
        $allCustomerIds = [
            $data['customerId'],
            $data['retCustomerId']
        ];
        $customer = $this->objDCustomer->select(['id' => $allCustomerIds],'id,name,avatar');
        if ($customer === false){
            return ResultWrapper::fail($this->objDCustomer->error(),ErrorCode::$dberror);
        }
        $customerMap = [];
        foreach ($customer as $value){
            $customerMap[$value['id']] = $value;
        }

        $data['customerName'] = isset($customerMap[$data['customerId']]) ? $customerMap[$data['customerId']]['name'] : '';
        $data['customerAvatar'] = isset($customerMap[$data['customerId']]) ? $customerMap[$data['customerId']]['avatar'] : '';
        $data['retCustomerName'] = isset($customerMap[$data['retCustomerId']]) ? $customerMap[$data['retCustomerId']]['name'] : '';
        $data['retCustomerAvatar'] = isset($customerMap[$data['retCustomerId']]) ? $customerMap[$data['retCustomerId']]['avatar'] : '';
        $data['deliveryMsg'] = isset(StatusCode::$deliveryType[$data['deliveryType']]) ? StatusCode::$deliveryType[$data['deliveryType']] : '';
        $data['selfMsg'] = '';
        if ($data['userCenterId'] == $data['retCustomerId']){
            $data['selfMsg'] = '自购返佣';
        }
        $data['payTypeMsg'] = StatusCode::$payType[$data['payType']];
        $data['retLevelMsg'] = self::$retLevel[$data['retLevel']];
        return ResultWrapper::success($data);
    }

    /**
     * Doc: (des="获取分销订单数据")
     * User: XMing
     * Date: 2020/8/5
     * Time: 10:38 上午
     * @param array $userCenterIds
     * @return ResultWrapper
     */
    public function getCommissionOrderData(array $userCenterIds)
    {
        $CommissionOrder = $this->objDCommissionOrderIndex->select(['userCenterId' => $userCenterIds],'userCenterId,payAmount');
        if ($CommissionOrder === false){
            return ResultWrapper::fail($this->objDCommissionOrderIndex->error(),ErrorCode::$dberror);
        }
        $retMap = [];
        foreach ($CommissionOrder as $value){
            $retMap[$value['userCenterId']][] = $value['payAmount'];
        }

        $map = [];
        foreach ($retMap as $userCenterId =>  $item){
            $map[$userCenterId] = [
                'totalOrderAmount' => array_sum($item),
                'total' => count($item),
            ];
        }

        return ResultWrapper::success($map);
    }

    public function exportCommissionOrder($date)
    {
        //导出到本地
        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 ($date as $value) {//循环数据
            $num++;
            if ($num == $limit) {
                ob_flush();//释放内存
                flush();
            }
            $rows['goodsName'] = isset($value['goodsName']) ? $value['goodsName'] : null; // 商品信息
            $rows['goodsPrice'] = isset($value['goodsPrice']) ? $value['goodsPrice'] : null; // 单价
            $rows['buyNum'] = isset($value['buyNum']) ? $value['createTime'] : 0; // 数量
            $rows['payAmount'] = bcmul($rows['goodsPrice'],$rows['buyNum'],2); // 实付款
            $rows['customerName'] = isset($value['customerName']) ? $value['customerName'] : null; // 订单来源
            $rows['customerMobile'] = isset($value['customerMobile']) ? $value['customerMobile'] : null; // 买家
            $rows['sourceNo'] = isset($value['sourceNo']) ? $value['sourceNo'] : null; // 订单状态
            $rows['createTime'] = isset($value['createTime']) ? date("Y-m-d H:i:s",$value['createTime']) : null; // 时间
            $rows['typeName'] = isset($value['typeName']) ? $value['typeName'] : null; // 分销商
            $rows['retMoney'] = isset($value['retMoney']) ? $value['retMoney'] : null; // 佣金
            $rows['isSettled'] = ($value['isSettled'] == StatusCode::$standard) ? '已结算': '未结算';// 佣金状态

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

}