seaBlueApi/Model/Charts/MCharts.Class.php

<?php
namespace JinDouYun\Model\Charts;

use JinDouYun\Controller\Common\Logger;
use JinDouYun\Dao\Order\DOrder;
use JinDouYun\Dao\Order\DOrderGoods;
use JinDouYun\Dao\Order\DOrderReceive;
use JinDouYun\Dao\SysAreaChina\DSysAreaChina;
use JinDouYun\Dao\System\DCustomerSource;
use JinDouYun\Model\GoodsManage\MGoodsBasic;
use Mall\Framework\Core\ErrorCode;
use Mall\Framework\Core\ResultWrapper;
use Mall\Framework\Core\StatusCode;


/**
 * @copyright   Copyright (c) https://www.qianniaovip.com All rights reserved
 * Description: 报表
 * Class MCharts
 * @package JinDouYun\Model\Cart
 */
class MCharts
{
    private $onlineUserId;

    private $onlineEnterpriseId;

    private $objDOrderGoods;

    private $objDOrder;

    private $objDOrderReceive;

    /**
     * MCharts constructor.
     * @param $onlineUserId
     * @param $onlineEnterpriseId
     * @throws \Exception
     */
    public function __construct($onlineUserId, $onlineEnterpriseId)
    {
        $this->onlineEnterpriseId = $onlineEnterpriseId;
        $this->onlineUserId = $onlineUserId;
        $this->objDOrderGoods = new DOrderGoods();
        $this->objDOrderGoods->setTable('qianniao_order_goods_'.$this->onlineEnterpriseId);
        $this->objDOrder = new DOrder();
        $this->objDOrder->setTable('qianniao_order_'.$this->onlineEnterpriseId);
        $this->objDOrderReceive = new DOrderReceive();
        $this->objDOrderReceive->setTable('qianniao_order_receive_'.$this->onlineEnterpriseId);
    }

    /**
     * Doc: (des="")
     * User: XMing
     * Date: 2021/3/29
     * Time: 10:59 上午
     * @param array $params
     * @return ResultWrapper
     * @throws \Exception
     */
    public function goods(array $params): ResultWrapper
    {
        //todo(后期要改成根据最大userId计算最大fix使用UNION ALL联合查询)
        $this->objDOrderGoods->setTable('qianniao_order_goods_'.$this->onlineEnterpriseId.'_1');
        $fields = '';
        switch ($params['type']){
            case 1:
                //商品
                $fields = ' goodsBasicId,goodsName as title,SUM(buyNum) as goodsNum,SUM(totalMoney) as goodsAmount ,SUM(JSON_EXTRACT(extends,"$.u_1_buy")) AS otherNum ';
                $group = ' GROUP BY goodsId ';
                break;
            case 2:
                //商品分类
                $fields = ' goodsBasicId,categoryName as title,SUM(buyNum) as goodsNum,SUM(totalMoney) as goodsAmount ,SUM(JSON_EXTRACT(extends,"$.u_1_buy")) AS otherNum ';
                $group = ' GROUP BY categoryName ';
                break;
            default:
                return ResultWrapper::fail('统计方式异常',ErrorCode::$paramError);
                break;
        }
        $where = '';
        if(isset($params['startTime']) && !empty($params['startTime']) && isset($params['endTime']) && !empty($params['endTime'])){
            $where .= ' AND createTime BETWEEN '.$params['startTime'].' AND '.$params['endTime'];
        }
        if(isset($params['categoryId']) && !empty($params['categoryId'])){
            $where .= ' AND FIND_IN_SET('.$params['categoryId'].',`categoryPath`)';
        }
        if(isset($params['brandId']) && !empty($params['brandId'])){
            $where .= ' AND brandId = '.$params['brandId'];
        }
        if(isset($params['shopId']) && !empty($params['shopId'])){
            $where .= ' AND shopId = '.$params['shopId'];
        }
        $sql = 'SELECT '.$fields.' FROM '.$this->objDOrderGoods->get_Table().' WHERE deleteStatus = '.StatusCode::$standard.$where.$group;
        $sql .= ' ORDER BY goodsNum DESC ';

        $count = $this->objDOrderGoods->query($sql);
        if ($count === false){
            return ResultWrapper::fail($this->objDOrderGoods->error(),ErrorCode::$dberror);
        }
        if (isset($params['limit']) && !empty($params['limit'])){
            $sql .= ' LIMIT '.$params['offset'].','.$params['limit'];
        }
        $lists = $this->objDOrderGoods->query($sql);
        if ($lists === false){
            return ResultWrapper::fail($this->objDOrderGoods->error(),ErrorCode::$dberror);
        }
        $goodsBasicDate = [];
        if (!empty($lists)){
            //获取商品资料
            $goodsBasicIds = array_column($lists,'goodsBasicId');
            $objGoodsDate = new MGoodsBasic($this->onlineUserId,$this->onlineEnterpriseId);
            $result = $objGoodsDate->getBasicGoodsFieldByIds($goodsBasicIds);
            if ( !$result->isSuccess() ){
                return ResultWrapper::fail($result->getData(),$result->getErrorCode());
            }
            $goodsBasicDate = $result->getData();
        }
        foreach ($lists as &$list){
            $list['isEq'] = isset($goodsBasicDate[$list['goodsBasicId']]['isEq']) ? $goodsBasicDate[$list['goodsBasicId']]['isEq'] : 4;
            $list['goodsNum'] = sprintf("%.2f", $list['goodsNum']);
            $list['goodsAmount'] = sprintf("%.2f", $list['goodsAmount']);
        }
        return ResultWrapper::success(['data' => $lists,'total' => count($count)]);
    }

    /**
     * Doc: (des="")
     * User: XMing
     * Date: 2021/3/29
     * Time: 2:23 下午
     * @param array $params
     * @return ResultWrapper
     * @throws \Exception
     */
    public function customerGoods(array $params): ResultWrapper
    {
        //todo(后期要改成根据最大userId计算最大fix使用UNION ALL联合查询)
        $this->objDOrderGoods->setTable('qianniao_order_goods_'.$this->onlineEnterpriseId.'_1');
        $this->objDOrder->setTable('qianniao_order_'.$this->onlineEnterpriseId.'_1');
        $where = '';
        switch ($params['type']){
            case 1:
                //客户
                $fields = ' g.goodsBasicId, o.customerName as title,SUM(g.buyNum) as goodsNum,SUM(g.totalMoney) as goodsAmount ,SUM(JSON_EXTRACT(g.extends,"$.u_1_buy")) AS otherNum ';
                $group = ' GROUP BY o.customerName ';
                break;
            case 2:
                //客户类型
                $fields = ' g.goodsBasicId, o.customerType as customerType,SUM(g.buyNum) as goodsNum,SUM(g.totalMoney) as goodsAmount ,SUM(JSON_EXTRACT(g.extends,"$.u_1_buy")) AS otherNum ';
                $group = ' GROUP BY o.customerType ';
                $where .= ' AND o.customerType != 0 ';
                break;
            default:
                return ResultWrapper::fail('统计方式异常',ErrorCode::$paramError);
                break;
        }
        if (isset($params['categoryId']) && !empty($params['categoryId'])){
            $where .= ' AND FIND_IN_SET('.$params['categoryId'].',g.categoryPath)';
        }
        if(isset($params['salesManId']) && !empty($params['salesManId'])){
            $where .= ' AND o.salesManId = '.$params['salesManId'];
        }
        if(isset($params['customerType']) && !empty($params['customerType'])){
            $where .= ' AND o.customerType = '.$params['salesManId'];
        }
        if(isset($params['startTime']) && !empty($params['startTime']) && isset($params['endTime']) && !empty($params['endTime'])){
            $where .= ' AND o.createTime BETWEEN '.$params['startTime'].' AND '.$params['endTime'];
        }
        if(isset($params['auditStatus']) && !empty($params['auditStatus'])){
            $where .= ' AND o.auditStatus = '.$params['auditStatus'];
        }
        if(isset($params['orderStatus']) && !empty($params['orderStatus'])){
            $where .= ' AND o.orderStatus = '.$params['orderStatus'];
        }

        $sql = 'SELECT '.$fields.' FROM '.$this->objDOrder->get_Table().' AS o 
                LEFT JOIN '.$this->objDOrderGoods->get_Table().' as g 
                ON o.id = g.orderId 
                WHERE o.deleteStatus = '.StatusCode::$standard.' 
                AND g.deleteStatus = '.StatusCode::$standard.$where.$group;
        $sql .= ' ORDER BY goodsNum DESC ';
        $count = $this->objDOrderGoods->query($sql);
        if ($count === false){
            return ResultWrapper::fail($this->objDOrderGoods->error(),ErrorCode::$dberror);
        }
        if (isset($params['limit']) && !empty($params['limit'])){
            $sql .= ' LIMIT '.$params['offset'].','.$params['limit'];
        }
        $lists = $this->objDOrderGoods->query($sql);
        if ($lists === false){
            return ResultWrapper::fail($this->objDOrderGoods->error(),ErrorCode::$dberror);
        }
        $map  = [];
        if ($params['type'] == 2){
            $customerTypeIds = [];
            foreach ($lists as $val){
                if (!empty($val['customerType']) && !in_array($val['customerType'].$customerTypeIds)){
                    $customerTypeIds[] = $val['customerType'];
                }
            }

            $objDCustomerSource = new DCustomerSource();
            if (!empty($customerTypeIds)){
                $result = $objDCustomerSource->select(['id' => $customerTypeIds]);
                if ($result === false){
                    return ResultWrapper::fail($objDCustomerSource->error(),ErrorCode::$dberror);
                }
                foreach ($result as $value){
                    $map[$value['id']] = $value['name'];
                }
            }
        }
        $goodsBasicDate = [];
        //获取商品资料
        $goodsBasicIds = array_column($lists,'goodsBasicId');
        $objGoodsDate = new MGoodsBasic($this->onlineUserId,$this->onlineEnterpriseId);
        $result = $objGoodsDate->getBasicGoodsFieldByIds($goodsBasicIds);
        if ( !$result->isSuccess() ){
            return ResultWrapper::fail($result->getData(),$result->getErrorCode());
        }
        $goodsBasicDate = $result->getData();
        foreach ($lists as &$list){
            $list['isEq'] = isset($goodsBasicDate[$list['goodsBasicId']]['isEq']) ? $goodsBasicDate[$list['goodsBasicId']]['isEq'] : 4;
            $list['goodsNum'] = sprintf("%.2f", $list['goodsNum']);
            $list['goodsAmount'] = sprintf("%.2f", $list['goodsAmount']);
            if ($params['type'] == 2){
                $list['title'] = getArrayItem($map,$list['customerType'],'');
            }

        }
        return ResultWrapper::success(['data' => $lists,'total' => count($count)]);
    }

    /**
     * Doc: (des="")
     * User: XMing
     * Date: 2021/3/29
     * Time: 3:13 下午
     * @param array $params
     * @return ResultWrapper
     * @throws \Exception
     */
    public function order(array $params): ResultWrapper
    {
        //todo(后期要改成根据最大userId计算最大fix使用UNION ALL联合查询)
        $this->objDOrderGoods->setTable('qianniao_order_goods_'.$this->onlineEnterpriseId.'_1');
        $this->objDOrder->setTable('qianniao_order_'.$this->onlineEnterpriseId.'_1');
        $where = '';
        switch ($params['type']){
            case 1:
                //日
                $fields = ' FROM_UNIXTIME(createTime,"%Y-%m-%d") as day,count(id) as num,sum(payAmount) as amount ';
                $group = ' GROUP BY day ';
                break;
            case 2:
                //月
                $fields = ' FROM_UNIXTIME(createTime,"%Y-%m") as month,count(id) as num,sum(payAmount) as amount ';
                $group = ' GROUP BY month ';
                break;
            default:
                return ResultWrapper::fail('统计方式异常',ErrorCode::$paramError);
                break;
        }
        if(isset($params['customerId']) && !empty($params['customerId'])){
            $where .= ' AND customerId = '.$params['customerId'];
        }
        if(isset($params['customerType']) && !empty($params['customerType'])){
            $where .= ' AND customerType = '.$params['salesManId'];
        }
        if(isset($params['auditStatus']) && !empty($params['auditStatus'])){
            $where .= ' AND auditStatus = '.$params['auditStatus'];
        }
        if(isset($params['orderStatus']) && !empty($params['orderStatus'])){
            $where .= ' AND orderStatus = '.$params['orderStatus'];
        }
        if(isset($params['startTime']) && !empty($params['startTime']) && isset($params['endTime']) && !empty($params['endTime'])){
            $where .= ' AND createTime BETWEEN '.$params['startTime'].' AND '.$params['endTime'];
        }
        $sql = 'SELECT '.$fields.' FROM '.$this->objDOrder->get_Table().' WHERE deleteStatus = '.StatusCode::$standard.$where.$group;
        $sql .= ' ORDER BY createTime DESC ';
        $count = $this->objDOrderGoods->query($sql);
        if ($count === false){
            return ResultWrapper::fail($this->objDOrderGoods->error(),ErrorCode::$dberror);
        }
        if (isset($params['limit']) && !empty($params['limit'])){
            $sql .= ' LIMIT '.$params['offset'].','.$params['limit'];
        }
        $lists = $this->objDOrderGoods->query($sql);
        if ($lists === false){
            return ResultWrapper::fail($this->objDOrderGoods->error(),ErrorCode::$dberror);
        }
        foreach ($lists as &$list){
            $list['num'] = sprintf("%.2f", $list['num']);
            $list['amount'] = sprintf("%.2f", $list['amount']);
        }
        return ResultWrapper::success(['data' => $lists,'total' => count($count)]);
    }

    /**
     * Doc: (des="")
     * User: XMing
     * Date: 2021/3/29
     * Time: 4:02 下午
     * @param array $params
     * @return ResultWrapper
     * @throws \Exception
     */
    public function customerOrder(array $params): ResultWrapper
    {
        //todo(后期要改成根据最大userId计算最大fix使用UNION ALL联合查询)
        $this->objDOrder->setTable('qianniao_order_'.$this->onlineEnterpriseId.'_1');
        $where = '';
        $fields = ' customerName,customerMobile,count(id) as num,sum(payAmount) as amount ';
        $group = ' GROUP BY customerName ';
        if(isset($params['startTime']) && !empty($params['startTime']) && isset($params['endTime']) && !empty($params['endTime'])){
            $where .= ' AND createTime BETWEEN '.$params['startTime'].' AND '.$params['endTime'];
        }
        if(isset($params['customerType']) && !empty($params['customerType'])){
            $where .= ' AND customerType = '.$params['customerType'];
        }
        if(isset($params['auditStatus']) && !empty($params['auditStatus'])){
            $where .= ' AND auditStatus = '.$params['auditStatus'];
        }
        if(isset($params['orderStatus']) && !empty($params['orderStatus'])){
            $where .= ' AND orderStatus = '.$params['orderStatus'];
        }
        $where .= ' AND customerName != "" ';
        $sql = 'SELECT '.$fields.' FROM '.$this->objDOrder->get_Table().' WHERE deleteStatus = '.StatusCode::$standard.$where.$group;
        $sql .= ' ORDER BY num DESC ';
        $count = $this->objDOrderGoods->query($sql);
        if ($count === false){
            return ResultWrapper::fail($this->objDOrderGoods->error(),ErrorCode::$dberror);
        }
        if (isset($params['limit']) && !empty($params['limit'])){
            $sql .= ' LIMIT '.$params['offset'].','.$params['limit'];
        }
        $lists = $this->objDOrderGoods->query($sql);
        if ($lists === false){
            return ResultWrapper::fail($this->objDOrderGoods->error(),ErrorCode::$dberror);
        }
        foreach ($lists as &$list){
            $list['num'] = sprintf("%.2f", $list['num']);
            $list['amount'] = sprintf("%.2f", $list['amount']);
        }
        return ResultWrapper::success(['data' => $lists,'total' => count($count)]);
    }

    /**
     * Doc: (des="")
     * User: XMing
     * Date: 2021/3/29
     * Time: 4:11 下午
     * @param array $params
     * @return ResultWrapper\
     * @throws \Exception
     */
    public function salesManOrder(array $params): ResultWrapper
    {
        //todo(后期要改成根据最大userId计算最大fix使用UNION ALL联合查询)
        $this->objDOrder->setTable('qianniao_order_'.$this->onlineEnterpriseId.'_1');
        $where = '';
        $fields = ' salesManName,count(id) as num,COUNT(DISTINCT customerId) as dealCustomerNum,sum(payAmount) as amount ';
        $group = ' GROUP BY salesManName ';
        if(isset($params['startTime']) && !empty($params['startTime']) && isset($params['endTime']) && !empty($params['endTime'])){
            $where .= ' AND createTime BETWEEN '.$params['startTime'].' AND '.$params['endTime'];
        }
        if(isset($params['auditStatus']) && !empty($params['auditStatus'])){
            $where .= ' AND auditStatus = '.$params['auditStatus'];
        }
        $where .= ' AND salesManName != "" ';
        $sql = 'SELECT '.$fields.' FROM '.$this->objDOrder->get_Table().' WHERE deleteStatus = '.StatusCode::$standard.' AND orderStatus = '.StatusCode::$standard.$where.$group;
        $sql .= ' ORDER BY amount DESC ';
        $count = $this->objDOrderGoods->query($sql);
        if ($count === false){
            return ResultWrapper::fail($this->objDOrderGoods->error(),ErrorCode::$dberror);
        }
        if (isset($params['limit']) && !empty($params['limit'])){
            $sql .= ' LIMIT '.$params['offset'].','.$params['limit'];
        }

        Logger::logs(E_USER_ERROR,'salesManOrder sql',__CLASS__,__LINE__,$sql);
        $lists = $this->objDOrderGoods->query($sql);
        if ($lists === false){
            return ResultWrapper::fail($this->objDOrderGoods->error(),ErrorCode::$dberror);
        }
        foreach ($lists as &$list){
            $list['num'] = sprintf("%.2f", $list['num']);
            $list['amount'] = sprintf("%.2f", $list['amount']);
        }
        return ResultWrapper::success(['data' => $lists,'total' => count($count)]);
    }

    /**
     * Doc: (des="地区订单报表")
     * User: XMing
     * Date: 2021/3/30
     * Time: 10:09 上午
     * @param array $params
     * @return ResultWrapper
     * @throws \Exception
     */
    public function areaOrder(array $params): ResultWrapper
    {
        //todo(后期要改成根据最大userId计算最大fix使用UNION ALL联合查询)
        $this->objDOrder->setTable('qianniao_order_'.$this->onlineEnterpriseId.'_1');
        $this->objDOrderReceive->setTable('qianniao_order_receive_'.$this->onlineEnterpriseId.'_1');
        $fields = ' r.provinceCode,COUNT(DISTINCT o.id) as num,COUNT(DISTINCT o.customerId) as dealCustomerNum,sum(o.payAmount) as amount ';
        $where = '';
        $group = ' GROUP BY r.provinceCode ';
        if(isset($params['startTime']) && !empty($params['startTime']) && isset($params['endTime']) && !empty($params['endTime'])){
            $where .= ' AND o.createTime BETWEEN '.$params['startTime'].' AND '.$params['endTime'];
        }
        if(isset($params['customerType']) && !empty($params['customerType'])){
            $where .= ' AND o.customerType = '.$params['customerType'];
        }
        if(isset($params['provinceCode']) && !empty($params['provinceCode'])){
            $where .= ' AND r.provinceCode = '.$params['provinceCode'];
        }
        if(isset($params['auditStatus']) && !empty($params['auditStatus'])){
            $where .= ' AND o.auditStatus = '.$params['auditStatus'];
        }
        if(isset($params['orderStatus']) && !empty($params['orderStatus'])){
            $where .= ' AND o.orderStatus = '.$params['orderStatus'];
        }
        $where .= ' AND r.provinceCode IS NOT NULL AND r.provinceCode != 0 ';
        $sql = 'SELECT '.$fields.' FROM '.$this->objDOrder->get_Table().' AS o 
                LEFT JOIN '.$this->objDOrderReceive->get_Table().' AS r 
                ON o.id = r.orderId 
                WHERE o.deleteStatus = '.StatusCode::$standard.$where.$group;
        $sql .= ' ORDER BY dealCustomerNum DESC ';
        $count = $this->objDOrderGoods->query($sql);
        if ($count === false){
            return ResultWrapper::fail($this->objDOrderGoods->error(),ErrorCode::$dberror);
        }
        if (isset($params['limit']) && !empty($params['limit'])){
            $sql .= ' LIMIT '.$params['offset'].','.$params['limit'];
        }
        $lists = $this->objDOrderGoods->query($sql);
        if ($lists === false){
            return ResultWrapper::fail($this->objDOrderGoods->error(),ErrorCode::$dberror);
        }
        $provinceCodes = [];
        foreach ($lists as $item){
            if (!in_array($item['provinceCode'],$provinceCodes)){
                $provinceCodes[] = $item['provinceCode'];
            }
        }
        $map = [];
        if (!empty($provinceCodes)){
            $objDSysAreaChina = new DSysAreaChina();
            $result = $objDSysAreaChina->select(['code'=>$provinceCodes],'code,name');
            if ($result === false){
                return ResultWrapper::fail($objDSysAreaChina->error(),ErrorCode::$dberror);
            }
            foreach ($result as $value){
                $map[$value['code']] = $value['name'];
            }
        }

        foreach ($lists as &$list){
            $list['num'] = sprintf("%.2f", $list['num']);
            $list['amount'] = sprintf("%.2f", $list['amount']);
            $list['title'] = getArrayItem($map,$list['provinceCode'],'');
        }
        return ResultWrapper::success(['data' => $lists,'total' => count($count)]);
    }
}