seaBlueApi/Model/Finance/MCustomerBalance.Class.php

<?php
/**
 * 客户余额管理模块
 * Created by PhpStorm.
 * User: wxj
 * Date: 2019/10/30
 * Time: 14:02
 */

namespace JinDouYun\Model\Finance;

use JinDouYun\Controller\Common\Logger;
use JinDouYun\Dao\Finance\DReceive;
use JinDouYun\Dao\Finance\DReceived;
use JinDouYun\Dao\Finance\DReceivedIndex;
use JinDouYun\Dao\Finance\DReceiveReceiptIndex;
use Mall\Framework\Core\ErrorCode;
use Mall\Framework\Core\StatusCode;
use Mall\Framework\Core\ResultWrapper;
use JinDouYun\Model\MBaseModel;
use JinDouYun\Dao\Finance\DCustomerBalance;
use JinDouYun\Dao\Finance\DCustomerBalanceIndex;
use JinDouYun\Dao\Customer\DCustomer;
use JinDouYun\Model\Customer\MCustomer;
use JinDouYun\Cache\OverviewCache;

class MCustomerBalance extends MBaseModel
{

    private $objDCustomerBalance;
    private $objDCustomerBalanceIndex;
    private $objDCustomer;
    private $objMCustomer;
    private $objOverviewCache;
    private $enterpriseId;
    private $userCenterId;
    private $cutTable = 200000;

    public function __construct($enterpriseId, $userCenterId)
    {
        $this->userCenterId = $userCenterId;
        $this->enterpriseId = $enterpriseId;
        parent::__construct($enterpriseId, $userCenterId);
        $this->objDCustomerBalance = new DCustomerBalance('finance');
        $this->objDCustomerBalanceIndex = new DCustomerBalanceIndex('finance');
        $this->objDCustomer = new DCustomer('default');
        $this->objMCustomer = new MCustomer($enterpriseId, $userCenterId);
        $this->objOverviewCache = new OverviewCache();

        $this->objDCustomerBalanceIndex->setTable('qianniao_customer_balance_index_' . $enterpriseId);
        $this->objDCustomer->setTable('qianniao_customer_' . $enterpriseId);
    }

    /**
     * 添加客户余额
     * @param int $customerId
     * @param int $changedMoney
     * @return ResultWrapper
     * @throws \Exception
     */
    public function addCustomerBalance($customerId, $changedMoney)
    {
        $tableName = $this->objDCustomerBalance->getTableName('qianniao_customer_balance_' . $this->enterpriseId, $customerId, $this->cutTable);
        $this->objDCustomerBalance->setTable($tableName);

        //获取客户目前的余额
        $money = $this->objDCustomer->get_field('money', $customerId);


        //新增一条客户余额
        $balanceData = [
            'customerId'     => $customerId,
            'openingBalance' => $money,
            'interimBalance' => $changedMoney,
            'endingBalance'  => bcadd($money, $changedMoney, 4),
            'createTime'     => time(),
            'updateTime'     => time()
        ];
        $detailId = $this->objDCustomerBalance->insert($balanceData);
        
        if ($detailId === false) {
            return ResultWrapper::fail($this->objDCustomerBalance->error(), ErrorCode::$dberror);
        }

        //新增一条客户余额索引数据
        $indexData = [
            'customerId' => $customerId,
            'detailId'   => $detailId,
            'createTime' => time(),
            'updateTime' => time()
        ];
        $indexId = $this->objDCustomerBalanceIndex->insert($indexData);
        if ($indexId === false) {
            return ResultWrapper::fail($this->objDCustomerBalanceIndex->error(), ErrorCode::$dberror);
        }
        //更新客户的最新余额
        $result = self::updateCustomerBalance($customerId, $changedMoney);
        if ($result->isSuccess() === false) {
            return ResultWrapper::fail($result->getData(), $result->getErrorCode());
        }
        return ResultWrapper::success($result->getData());

    }

    /**
     * 获取某一日期的余额
     * @param $time
     * @return int
     * @throws \Exception
     */
    public function getShouldReceiveMoneyByTime($time, $customerId)
    {
        $tableName = $this->objDCustomerBalanceIndex->get_Table();
        $sql = "select * from $tableName where createTime <= $time AND customerId = $customerId order by id desc limit 1";
        $result = $this->objDCustomerBalanceIndex->query($sql);
        if (empty($result)) {
            //没有发生过应收应付
            return 0;
        }
        $data = array_shift($result);
        $tableName = $this->objDCustomerBalance->getTableName('qianniao_customer_balance_' . $this->enterpriseId, $customerId, $this->cutTable);
        $this->objDCustomerBalance->setTable($tableName);
        return $this->objDCustomerBalance->get_field('endingBalance', $data['detailId']);
    }

    /**
     * 获取所有客户余额数据
     * @param array $selectParams 过滤条件
     * @return ResultWrapper
     * @throws \Exception
     */
    public function getAllCustomerBalance($selectParams,$export = 0)
    {
        $limit = $selectParams['limit'];
        unset($selectParams['limit']);
        $offset = $selectParams['offset'];
        unset($selectParams['offset']);
        if($export){
             $limit = null;
             $offset = null;
        }
        $customerId = $selectParams['customerId'];
        unset($selectParams['customerId']);
        $tag = $selectParams['tag'];
        unset($selectParams['tag']);

        $start = $selectParams['start'];
        unset($selectParams['start']);
        $end = $selectParams['end'];
        unset($selectParams['end']);

        //默认进来不筛选，查出客户当前的余额
        if (!$start && !$end) {
            $where = ['limit' => $limit, 'offset' => $offset];
            if ($customerId) {
                $where['id'] = $customerId;
            }
            if (!empty($tag)) {
                $where['tag'] = $tag;
            }
            $result = $this->objMCustomer->getCustomerMoney($where);
            if ($result->isSuccess() === false) {
                return ResultWrapper::fail($result->getData(), $result->getErrorCode());
            }
            // if (!$start && !$end)情况下导出
            if($export){
                self::exportCustomerBalance($result->getData()['data']);
                exit;
            }
            return ResultWrapper::success($result->getData());
        }

        //期初余额
        $startResult = self::getShouldReceiveMoneyByTime($start, $customerId);
        //期末余额
        $endResult = self::getShouldReceiveMoneyByTime($end, $customerId);
        // 销售金额 / 收款金额
        $saleResult = self::getSaleMoneyByTime($start,$end,$customerId);

        $customerName = $this->objDCustomer->get($customerId);
        $return = [
            'data'  => [
                [
                    'customerId'     => $customerId,
                    'name'           => $customerName['name'],
                    'memberBalance'  => $customerName['memberBalance'],
                    'openingBalance' => $startResult,
                    'saleMoney'=>$saleResult['saleMoney'],
                    'collectionMoney'=>$saleResult['collectionMoney'],
                    'endingBalance'  => $endResult
                ]
            ],
            'total' => 1,
        ];
        //导出
        if($export){
            self::exportCustomerBalance($return['data']);
            exit;
        }
        return ResultWrapper::success($return);

    }

    private function format($data)
    {
        $customerIds = [];
        foreach ($data as $k => $v) {
            $customerIds[] = $v['customerId'];
        }
        $customerInfo = $this->objMCustomer->getCustomer(array_unique($customerIds));
        $customerData = $customerInfo->getData();

        foreach ($data as $k => $v) {
            $data[$k]['customerName'] = isset($customerData[$v['customerId']]) ? $customerData[$v['customerId']]['name'] : '';
        }
        return $data;
    }

    /**
     * 修改客户余额
     * @param $customerId
     * @param $changedMoney
     * @return ResultWrapper
     */
    public function updateCustomerBalance($customerId, $changedMoney)
    {
        $dbResult = $this->objDCustomer->set_inc('money', $customerId, $changedMoney);
        
        if ($dbResult === false) {
            return ResultWrapper::fail($this->objDCustomer->error(), ErrorCode::$dberror);
        }

        //修改应收款总金额
        $this->objOverviewCache->saveAggregateStatistics($this->enterpriseId, 'totalShouldReceive', $changedMoney);
        return ResultWrapper::success($dbResult);
    }

    /**
     * 修改客户付款总额
     * @param $customerId
     * @param $money
     * @return ResultWrapper
     */
    public function updateCustomerTotalPayMoney($customerId, $money)
    {
        $dbResult = $this->objDCustomer->set_inc('totalPayMoney', $customerId, $money);
        if ($dbResult === false) {
            return ResultWrapper::fail($this->objDCustomer->error(), ErrorCode::$dberror);
        }
        return ResultWrapper::success($dbResult);
    }

    /**
     * 获取客户余额
     * @param $customerId
     * @return ResultWrapper
     */
    public function getCustomerBalance($customerId, $field = 'money')
    {
        $money = $this->objDCustomer->get_field($field, $customerId);
        return $money ? $money : 0;
    }
    
    /**
     * 导出方法
     * @param $result
     * @return void
     * @throws Exception
     */
    public function exportCustomerBalance($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 $v) {//循环数据
            $num++;
            if ($num == $limit) {
                ob_flush();         //释放内存
                flush();
            }
            $rows['name'] = isset($v['name']) ? $v['name'] : '';//客户名称
            $rows['openingBalance'] = isset($v['openingBalance']) ? $v['openingBalance'] : '';//初期余额
            $rows['interimBalance1'] = isset($v['interimBalance']) ? $v['interimBalance'] : '';//销售金额
            $rows['interimBalance2'] = isset($v['interimBalance']) ? $v['interimBalance'] : '';//收款金额
            $rows['endingBalance'] = isset($v['endingBalance']) ? $v['endingBalance'] : '';//期末金额$rows['openingBalance']+$rows['interimBalance']-$rows['interimBalance'];//期末金额  应该是期初+销售-收款=期末
            $rows['interimBalance3'] = isset($v['interimBalance']) ? $v['interimBalance'] : '';//会员余额
            foreach ($rows as $kk => $vv) {
                $rs[$kk] = mb_convert_encoding($vv, 'GBK', 'utf-8');  //转译编码
            }
            fputcsv($fp, $rs);
            
            $rows = [];
        }
    }

    /**
     * 根据时间获取销售金额，收款金额
     */
    public function getSaleMoneyByTime($start,$end,$customerId)
    {
        $saleMoney = 0;// 销售金额
        $collectionMoney = 0;// 收款金额

        $objReceiveTable = new DReceive('finance');
        $objReceivedTable = new DReceived('finance');

        $objReceiveIndexTable = new DReceiveReceiptIndex('finance');
        $objReceiveIndexTable->setTable('qianniao_receive_receipt_index_'.$this->enterpriseId);
        $objReceivedIndexTable = new DReceivedIndex('finance');
        $objReceivedIndexTable->setTable('qianniao_received_index_'.$this->enterpriseId);

        $receiveIndexSql = 'select * from '.$objReceiveIndexTable->get_Table().' WHERE createTime BETWEEN '.$start.' AND '.$end .' AND customerId='.$customerId .' AND offsetStatus='.StatusCode::$standard;
        $receiveIndexDate = $objReceiveIndexTable->query($receiveIndexSql);
        if($receiveIndexDate === false){
            return ResultWrapper::fail($objReceiveIndexTable->error(), ErrorCode::$dberror);
        }
        if(!empty($receiveIndexDate)){
            foreach ($receiveIndexDate as $receiveKey => $receiveValue){
                $suffix = date('Y', $receiveValue['createTime']) . '_' . ceil(date('m', $receiveValue['createTime']) / 3);
                $objReceiveTable->setTable('qianniao_receive_receipt_' . $this->enterpriseId . '_' . $suffix);
                $receiveDate = $objReceiveTable->get($receiveValue['id']);
                $saleMoney = bcadd($saleMoney,$receiveDate['receiveMoney'],2);
            }
        }

        $receivedIndexSql = 'select * from '.$objReceivedIndexTable->get_Table().' WHERE createTime BETWEEN '.$start.' AND '.$end.' AND customerId='.$customerId .' AND offsetStatus='.StatusCode::$standard;
        $receivedIndexDate = $objReceivedIndexTable->query($receivedIndexSql);
        if($receiveIndexDate === false){
            return ResultWrapper::fail($objReceiveIndexTable->error(), ErrorCode::$dberror);
        }
        if(!empty($receivedIndexDate)){
            foreach ($receivedIndexDate as $receivedKey => $receivedValue){
                $objReceivedTable->setTable('qianniao_received_' . $this->enterpriseId . '_' . date('Y', $receivedValue['createTime']) . '_' . ceil(date('m', $receivedValue['createTime']) / 3));
                $receivedDate = $objReceivedTable->get($receivedValue['id']);
                $collectionMoney = bcadd($collectionMoney,isset($receivedDate['totalFinalMoney']) ? $receivedDate['totalFinalMoney'] :0,2);
            }
        }
        return [
            'saleMoney'=>$saleMoney,
            'collectionMoney'=>$collectionMoney
        ];
    }
}