seaBlueQueue/Jobs/Model/MTopic/Finance/MReceivable.Class.php

<?php
/**
 * 财务应收生成和检测脚本
 * Created by PhpStorm.
 * User: phperstar
 * Date: 2019/12/19
 * Time: 10:38 AM
 */

namespace Jobs\Model\MTopic\Finance;

use Jobs\Dao\DShop;
use Mall\Framework\Factory;
use Jobs\Cache\OverviewCache;
use Jobs\Dao\Purchase\DPurchase;
use Jobs\Dao\Stock\DInventoryBatch;
use Jobs\Dao\Stock\DWarehouse;
use Mall\Framework\Core\StatusCode;
use Jobs\Dao\Stock\DInventoryDetails;
use Jobs\Dao\Order\DOrder;
use Jobs\Dao\Stock\DInventoryIn;
use Jobs\Dao\Stock\DInventoryOut;
use Jobs\Dao\Finance\DReceiveReceiptIndex;
use Jobs\Dao\Finance\DReceive;
use Jobs\Cache\FinanceCache;


class MReceivable
{
    private $objDOrder;
    private $objDInventoryIn;
    private $objDReceive;
    private $objDReceiveReceiptIndex;
    private $objFinanceCache;
    private $objDInventoryDetails;
    private $objDWarehouse;
    private $enterpriseId;
    private $objOverviewCache;
    private $objDInventoryBatch;
    private $objDPurchase;
    private $objDShop;

    private $cutTable = 200000; // 订单表切割基数

    public function __construct()
    {
        $this->objDOrder = new DOrder('default');
        $this->objDInventoryIn = new DInventoryIn('stock');
        $this->objDReceive = new DReceive('finance');
        $this->objDReceiveReceiptIndex = new DReceiveReceiptIndex('finance');
        $this->objFinanceCache = new FinanceCache();
        $this->objDInventoryDetails = new DInventoryDetails();
        $this->objDWarehouse = new DWarehouse();
        $this->objOverviewCache = new OverviewCache();
        $this->objDInventoryBatch = new DInventoryBatch();
        $this->objDPurchase = new DPurchase();
        $this->objDShop = new DShop();

        $this->objDReceive->setSearchIndex('should_receive_receipt_search')->setType('should_receive_receipt');

    }

    /**
     * @param $params
     */
    public function checkExistCreate($params)
    {
        echo '检查应收单开始'.PHP_EOL;
        print_r($params);
        self::checkSaleOutCreateReceivable($params['enterpriseId']);
        echo '检查成功'.PHP_EOL;
    }

    /**
     * 检测销售出库单是否都生成应收单了
     */
    public function checkSaleOutCreateReceivable($enterpriseId)
    {
        $financeDbName = Factory::config()->get('db.finance.dbname');
        $stockDbName = Factory::config()->get('db.stock.dbname');
        $defaultDbName = Factory::config()->get('db.default.dbname');

        if (!$financeDbName || !$stockDbName || !$defaultDbName) {
            echo "dbname获取错误";
            return false;
        }

        //查出该企业下 审核通过但未生成应收单的销售出库单
        $sql = 'select a.originId,a.customerId,a.type,b.userCenterId,a.id from `'. $stockDbName .'`.`qianniao_inventory_out_'. $enterpriseId .'` as a JOIN `'. $defaultDbName .'`.qianniao_customer_'. $enterpriseId .' AS b ON a.customerId=b.id AND a.sourceNo NOT IN (select sourceNo from `'. $financeDbName .'`.`qianniao_receive_receipt_index_'. $enterpriseId .'`) and a.auditStatus = '. StatusCode::$auditStatus['auditPass'].' AND a.type ='.StatusCode::$orderType['saleOut'];
        $salesOutData = $this->objDOrder->query($sql);
        if (!empty($salesOutData)) {
            foreach ($salesOutData as $salesOut) {
                $result = self::createReceivable([
                    'userCenterId' => $salesOut['userCenterId'],
                    'enterpriseId' => $enterpriseId,
                    'id'           => $salesOut['id'],//订单id
                    'type'         => StatusCode::$orderType['saleOut'],
                    'checkOldData'  => false
                ]);
                print_r('订单'.$salesOut['originId'].'生成应收'.$result);
            }
        }


        //查出该企业下 审核通过但未生成应收单的销售出库单
        $sql = 'select a.id,a.type,b.userCenterId from `'. $stockDbName .'`.`qianniao_inventory_in_'. $enterpriseId .'` as a JOIN `'. $defaultDbName .'`.qianniao_order_index_'. $enterpriseId .' AS b ON a.originId=b.id AND a.originNo NOT IN (select sourceNo from `'. $financeDbName .'`.`qianniao_receive_receipt_index_'. $enterpriseId .'`) and a.auditStatus = '. StatusCode::$auditStatus['auditPass'].' AND a.type ='.StatusCode::$orderType['saleReturnIn'];
        $saleReturnInData = $this->objDOrder->query($sql);
        if (!empty($saleReturnInData)) {
            foreach ($saleReturnInData as $saleReturnIn) {
                $result = self::createReceivable([
                    'userCenterId' => $saleReturnIn['userCenterId'],
                    'enterpriseId' => $enterpriseId,
                    'id'           => $saleReturnIn['id'],//入库单Id
                    'type'         => StatusCode::$orderType['saleReturnIn'],
                    'checkOldData'  => false
                ]);
                print_r('入库单'.$saleReturnIn['id'].'生成应收'.$result);
            }
        }
    }

    /**
     * 销售出库单生成应收单
     * @param $params
     * @return bool
     * @throws \Exception
     */
    public function createReceivable($params)
    {
        // 参数校验
        if (empty($params)) {
            echo '请求创建应收单参数为空' . PHP_EOL;
            return false;
        }

        foreach ($params as $key => $value){
            if(empty($value)){
                echo $key.'参数错误' . PHP_EOL;
                return false;
            }
        }
        $this->enterpriseId = $params['enterpriseId'];

        $receiveExtraData = [];
        $orderData = [];
        $receiveData = [];
        //销售出库生成应收
        if ($params['type'] == StatusCode::$orderType['saleOut']) {
            // 查询销售出库单数据
            $objDInventoryOut  = new DInventoryOut('stock');
            $objDInventoryOut->setTable('qianniao_inventory_out_'.$params['enterpriseId']);
            $inventoryOutData = $objDInventoryOut->get(['id'=>$params['id']]);
            if($inventoryOutData === false){
                echo 'sql错误'.$objDInventoryOut->error().PHP_EOL;
                return false;
            }

            if (empty($inventoryOutData)) {
                echo '销售出库单id:' . $params['id'] . '的数据为空'.PHP_EOL;
                return false;
            }

            // 查询订单信息
            $tableName = $this->objDOrder->getTableName('qianniao_order_' . $params['enterpriseId'], $params['userCenterId'], $this->cutTable);
            $this->objDOrder->setTable($tableName);
            $orderData = $this->objDOrder->get(['id' => $inventoryOutData['originId']]);
            if ($orderData === false) {
                echo 'sql错误' . $this->objDOrder->error() . PHP_EOL;
                return false;
            }

            if (empty($orderData)) {
                echo '源销售订单id:' . $inventoryOutData['originId'] . '的数据为空' . PHP_EOL;
                return false;
            }

            // 统计首页概况今日毛利
            self::getInfo($orderData);

            // 组装应收单数据
            $receiveExtraData = [
                'orderId'       => $orderData['id'], // 销售出库单对应的应收单订单id为销售订单id
                'customerId'    => $orderData['customerId'],
                'customerName'  => $orderData['customerName'],
                'sourceNo'      => $orderData['no'],
                'receiveMoney'  => $inventoryOutData['amount'],
                'notOffsetMoney'=> $inventoryOutData['amount'],
                'discountMoney' => 0,
                'receiptTypeId' => StatusCode::$orderType['saleOrder'],

            ];
            // 创建应收单数据
            $receiveData = [
                'shopId'        => $orderData['shopId'],
                'shopName'      => $orderData['shopName'],
                'financeTypeId' => StatusCode::$orderType['saleOut'],
                'financeType'   => '销售单',
                'auditStatus'   => StatusCode::$auditStatus['auditing'],
                'createTime'    => time(),
                'updateTime'    => time(),
            ];
        }


        //销售退货入库生成应收
        if ($params['type'] == StatusCode::$orderType['saleReturnIn']) {
            // 查询销售退货入库单信息
            $this->objDInventoryIn->setTable('qianniao_inventory_in_' . $params['enterpriseId']);
            $inventoryInData = $this->objDInventoryIn->get(['id' => $params['id']]);
            if ($inventoryInData === false) {
                echo 'sql错误' . $this->objDInventoryIn->error() . PHP_EOL;
                return false;
            }

            if (empty($inventoryInData)) {
                echo '销售退货-入库单id:' . $params['id'] . '的数据为空' . PHP_EOL;
                return false;
            }

            //源销售订单的信息
            $tableName = $this->objDOrder->getTableName('qianniao_order_' . $params['enterpriseId'], $params['userCenterId'], $this->cutTable);
            $this->objDOrder->setTable($tableName);
            $orderData = $this->objDOrder->get(['id' => $inventoryInData['originId']]);
            if ($orderData === false) {
                echo 'sql错误' . $this->objDOrder->error() . PHP_EOL;
                return false;
            }

            if (empty($orderData)) {
                echo '源销售订单id:' . $inventoryInData['originId'] . '的数据为空' . PHP_EOL;
                return false;
            }

            $receiveExtraData = [
                'orderId'       => $inventoryInData['sourceId'], // 销售退货应收订单id为销售退货单id
                'sourceNo'      => $inventoryInData['sourceNo'],
                'customerId'    => $orderData['customerId'],
                'customerName'  => $orderData['customerName'],
                'receiveMoney'  => '-' . $inventoryInData['amount'],
                'discountMoney' => 0.00,
                'receiptTypeId' => StatusCode::$orderType['saleReturn'],
                'notOffsetMoney'=>'-' . $inventoryInData['amount'],
            ];
            // 创建应收单数据
            $receiveData = [
                'shopId'        => $orderData['shopId'],
                'shopName'      => $orderData['shopName'],
                'financeTypeId' => StatusCode::$orderType['saleOut'],
                'financeType'   => '销售退款单',
                'auditStatus'   => StatusCode::$auditStatus['auditing'],
                'createTime'    => time(),
                'updateTime'    => time(),
            ];
        }
        

        $receiveData = array_merge($receiveData, $receiveExtraData);
        // 添加应收单
        $beginTransactionStatus = $this->objDReceive->beginTransaction();
        $this->objDReceive->setTable('qianniao_receive_receipt_' . $params['enterpriseId'] . '_' . date('Y') . '_' . ceil(date('m') / 3));
        $dbResult =  $this->objDReceive->get('createTime >='.strtotime(date('Ymd'.'0:0:0')), 'no', 'createTime desc');
        if ($dbResult === false) {
            echo 'sql错误' . $this->objDReceive->error() . PHP_EOL;
            return false;
        }
        if(empty($dbResult)){
            $receiveData['no'] =  createSerialNumberByDate('');
        }else{
            $receiveData['no'] =  createSerialNumberByDate($dbResult['no']);
        }

        //索引表数据
        $indexData = [
            'receiveReceiptId' => 0,
            'customerId'       => $receiveData['customerId'],
            'sourceNo'         => $receiveData['sourceNo'],
            'auditStatus'      => $receiveData['auditStatus'],
            'financeTypeId'    => $receiveData['financeTypeId'],
            'financeType'      => $receiveData['financeType'],
            'shopId'           => $receiveData['shopId'],
            'createTime'       => $receiveData['createTime'],
            'updateTime'       => $receiveData['updateTime'],
        ];
        $this->objDReceiveReceiptIndex->setTable('qianniao_receive_receipt_index_' . $params['enterpriseId']);
        $receiveReceiptId = $this->objDReceiveReceiptIndex->insert($indexData);
        if ($receiveReceiptId === false) {
            $this->objDReceive->rollBack();
            echo 'sql错误' . $this->objDReceiveReceiptIndex->error() . PHP_EOL;
            return false;
        }

        //判断收款金额是否为负数，核销状态直接给5
        $receiveData['offsetStatus'] = ($receiveData['receiveMoney']<0) ? 5 : 4;
        $receiveData['id'] = $receiveReceiptId;
        $dbResult = $this->objDReceive->insert($receiveData);
        if ($dbResult === false) {
            $this->objDReceive->rollBack();
            echo 'sql错误' . $this->objDReceive->error() . PHP_EOL;
            return false;
        }

        if($beginTransactionStatus){
            $this->objDReceive->commit();
        }

        echo '数据库添加应收单成功' . PHP_EOL;

        /*
        $_id = self::createEsDocumentId($ReceiveId, $params['enterpriseId']);
        $esData = $receiveData;
        $esData['id'] = $ReceiveId;
        $esData['enterpriseId'] = $params['enterpriseId'];
        $result = $this->objDReceive->addUpSearchIndexDocument($esData, $_id);
        if (isset($result['_shards']) && isset($result['_shards']['successful']) && $result['_shards']['successful'] == 1) {
            echo "es操作成功";//die;
        }else{
            echo "es操作失败";
            file_put_contents('/apps/logs/elasticsearch.log',date('Y-m-d H:i:s').'生成应收es错误，错误原因'.var_export($result,true).PHP_EOL,FILE_APPEND);
        }

        echo 'es添加应收单成功' . PHP_EOL;*/

        // 添加应收队列缓存,计划任务自动审核应收用
        $this->objFinanceCache->saveSalesOutReceive($params['enterpriseId'], $receiveReceiptId, $receiveData['createTime']);


        if(!isset($params['checkOldData'])) {
            echo '检测销售出库单是否都生成应收单了';
            self::checkSaleOutCreateReceivable($params['enterpriseId']);
        }

        return true;
    }

    private function createEsDocumentId($receiveId, $enterpriseId)
    {
        $t = date('Y') . '_' . ceil(date('m') / 3);
        return 'EnterpriseId_' . $enterpriseId . '_' . $t . '_receiveId_' . $receiveId;
    }

    /**
     * @param $orderData
     * @throws \Exception
     */
    private function getInfo($orderData)
    {
        // 查询订单对应店铺绑定的仓库id
        $this->objDShop->setTable('qianniao_shop_1');
        $warehouseId = $this->objDShop->get_field('warehouseId',['id'=>$orderData['shopId']]);

        // 获取当前订单的shopId对应的仓库id
        $this->objDWarehouse->setTable('qianniao_warehouse_' . $this->enterpriseId);
        if ($warehouseId === false || empty($warehouseId)) {
            echo '商铺id'. $orderData['shopId'] .'获取仓库id时出错' . $this->objDWarehouse->error() . PHP_EOL;
            return false;
        }
        $warehouseId = strpos($warehouseId,',') ? explode(',', $warehouseId) : [$warehouseId];
        $orderData['warehouseId'] = $warehouseId;

        // 查询当前订单的流水记录
        $this->objDPurchase->setTable('qianniao_purchase_' . $this->enterpriseId);//切换分表
        $inventoryDetails = [];
        foreach($warehouseId as $wid){
            self::setDetailsTable($this->enterpriseId, $wid, 'qianniao_inventory_details');//切换分表
            self::setBatchTable($this->enterpriseId, $wid, 'qianniao_inventory_batch');//切换分表
            $dbResult = $this->objDInventoryDetails->select(['actionType' => StatusCode::$delete, 'originId' => $orderData['id']]);
            if ($dbResult === false) {
                echo '库存流水查询出错' . $this->objDInventoryDetails->error() . PHP_EOL;
                return false;
            }
            if(!empty($dbResult)){
                $inventoryDetails = array_merge($inventoryDetails, $dbResult);
            }
        }
        if (empty($inventoryDetails)) {
            echo '库存流水数据为空' . PHP_EOL;
            return false;
        }


        self::todayGrossProfit($inventoryDetails, $orderData);
        //self::supplierRanking($inventoryDetails, $orderData);
    }



    /**
     * 缓存订单毛利
     * @param $inventoryDetails
     * @param $orderData
     */
    private function todayGrossProfit($inventoryDetails, $orderData)
    {
        $averageCostTotal = 0;
        $batchCostTotal = 0;
        foreach ($inventoryDetails as $detail) {
            switch ($detail['costType']) {
                case StatusCode::$costType['mwa']://平均值
                    $averageCostTotal += bcmul($detail['inventoryNum'], $detail['averageCost'], 2);
                    break;
                case StatusCode::$costType['sp']://批次成本
                    $batch = json_decode($detail['batch'], true);
                    foreach ($batch as $val) {
                        $batchCostTotal += bcmul($val['num'], $val['batchCost'], 2);
                    }
                    break;
            }
        }

        //TODO(这里暂时不回写订单商品的出库成本)
        $costTotal = bcadd($averageCostTotal, $batchCostTotal, 2);//此订单的总成本
        $grossProfit = bcsub($orderData['payAmount'], $costTotal, 2);
        $this->objOverviewCache->saveBusinessOverview($this->enterpriseId, 'todayGrossProfit', $grossProfit);//订单毛利
        $this->objOverviewCache->saveBusinessOverview($this->enterpriseId, 'todayGrossProfit', $grossProfit, $orderData['shopId']);
        echo '订单' . $orderData['no'] . ':毛利缓存成功' . PHP_EOL;
    }

    /**
     * @param $inventoryDetails
     * @param $orderData
     */
    private function supplierRanking($inventoryDetails, $orderData)
    {
        $batchNo = [];
        foreach ($inventoryDetails as $detail) {
            $batch = json_decode($detail['batch'], true);
            foreach ($batch as $val) {
                $batchNo[] = $val['batch'];
            }
        }
        $fields = 'p.supplierId,p.supplierName,p.warehouseId,b.materielId,b.skuId,b.batchNo';
        $sql = 'SELECT ' . $fields . ' FROM ' . $this->objDInventoryBatch->get_Table() . ' AS b LEFT JOIN ' . $this->objDPurchase->get_Table() . ' AS p ON b.originId=p.id WHERE b.batchNo IN(' . implode(',', $batchNo) . ')';
        $supplier = $this->objDInventoryBatch->query($sql);
        if ($supplier === false) {
            echo '查询订单商品所属供应商时出错' . $this->objDInventoryBatch->error() . PHP_EOL;
            die;
        }
        if (empty($supplier)) {
            echo '查询订单商品对应供应商数据为空' . PHP_EOL;
            die;
        }

        $allSupplier = [];
        foreach ($supplier as $val) {
            $allSupplier[$val['batchNo']] = $val;
        }
        foreach ($inventoryDetails as &$detail) {
            $batch = json_decode($detail['batch'], true);
            foreach ($batch as &$val) {
                $val['supplierId'] = isset($allSupplier[$val['batch']]['supplierId']) ? $allSupplier[$val['batch']]['supplierId'] : 0;
                $this->objOverviewCache->saveRanking($this->enterpriseId, 'supplierRanking', $val['supplierId'], $val['num']);//销量
                $this->objOverviewCache->saveRanking($this->enterpriseId, 'supplierRanking', $val['supplierId'], $val['num'], $orderData['shopId']);

                $this->objOverviewCache->saveSalesMoneyRanking($this->enterpriseId, 'supplierRanking', $val['supplierId'], $orderData['payAmount']);//销额
                $this->objOverviewCache->saveSalesMoneyRanking($this->enterpriseId, 'supplierRanking', $val['supplierId'], $orderData['payAmount'], $orderData['shopId']);
            }

        }
        echo '出库供应商数据统计成功' . PHP_EOL;
    }

    /**
     * 库存批次按照企业商铺分表
     * @param $enterpriseId
     * @param $warehouseId
     * @param $tableName
     * @throws \Exception
     */
    public function setBatchTable($enterpriseId, $warehouseId, $tableName)
    {
        $tableName = $tableName . '_' . $enterpriseId . '_' . $warehouseId;
        $this->objDInventoryBatch->setTable($tableName);
    }


    /**
     * 库存流水按照仓库+季度分表
     * @param $enterpriseId
     * @param $warehouseId
     * @param $tableName
     * @throws \Exception
     */
    private function setDetailsTable($enterpriseId, $warehouseId, $tableName)
    {
        $tableName = $tableName . '_' . $enterpriseId . '_' . $warehouseId . '_' . substr(date('Y'), -2) . ceil(date('m') / 3);
        $this->objDInventoryDetails->setTable($tableName);
    }
}