<?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
];
}
}