qianniaoApis/Model/Finance/MAccount.Class.php

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

namespace JinDouYun\Model\Finance;

use JinDouYun\Dao\Finance\DAccountDetail;
use JinDouYun\Dao\Order\DOrder;
use JinDouYun\Dao\Stock\DInventoryOut;
use JinDouYun\Model\MBaseModel;
use Mall\Framework\Core\ErrorCode;
use Mall\Framework\Core\StatusCode;
use Mall\Framework\Core\ResultWrapper;

use JinDouYun\Dao\Finance\DAccount;

class MAccount extends MBaseModel {

    private $objDAccount;
    private $enterpriseId;
    private $userCenterId;
    private $objDAccountDetail;
    private $cutTable = 100;

    public function __construct($enterpriseId, $userCenterId)
    {
        $this->userCenterId = $userCenterId;
        $this->enterpriseId = $enterpriseId;
        parent::__construct($enterpriseId, $userCenterId);
        $this->objDAccount = new DAccount('finance');
        $this->objDAccountDetail = new DAccountDetail('finance');
    }

    /**
     * 添加账户
     *
     * @param array $params 账户数据
     *
     * @return ResultWrapper
     */
    public function addAccount($params)
    {
        if($params['isDefault'] == StatusCode::$standard) {
            $result = self::updateDefaultStatus();
            if($result->isSuccess() == false) {
                return ResultWrapper::fail($result->getData(), $result->getErrorCode());
            }
        }

        $AccountId = $this->objDAccount->insert($params);

        if($AccountId === false){
            return ResultWrapper::fail($this->objDAccount->error(), ErrorCode::$dberror);
        }else{
            return ResultWrapper::success($AccountId);
        }
    }

    /**
     * 获取指定账户信息
     */
    public function getAccountInfo($AccountIds)
    {
        $where = ['id'=>$AccountIds, 'enterpriseId'=>$this->enterpriseId];
        $dbResult = $this->objDAccount->get($where);

        if($dbResult === false){
            return ResultWrapper::fail($this->objDAccount->error(), ErrorCode::$dberror);
        }else{
            return ResultWrapper::success($dbResult);
        }
    }


    /**
     * 编辑账户
     *
     * @param int|array $params 修改账户的数据
     *
     * @return ResultWrapper
     */
    public function editAccount($params)
    {
        if( empty($params['id']) ){
            return ResultWrapper::fail('没有指定要修改的Accountid', ErrorCode::$paramError);
        }

        if(isset($params['isDefault']) && $params['isDefault'] == StatusCode::$standard) {
            $result = self::updateDefaultStatus();
            if($result->isSuccess() == false) {
                return ResultWrapper::fail($result->getData(), $result->getErrorCode());
            }
        }

        $updateAccountId = $params['id'];
        unset($params['id']);

        $dbResult = $this->objDAccount->update($params, $updateAccountId);

        if($dbResult === false){
            return ResultWrapper::fail($this->objDAccount->error(), ErrorCode::$dberror);
        }else{
            return ResultWrapper::success($dbResult);
        }
    }

    /**
     * 给账户加减金额
     * @param $id
     * @param $changedMoney
     * @return ResultWrapper
     */
    public function updateMoney($id, $changedMoney) {
        $dbResult = $this->objDAccount->set_inc('money', $id, $changedMoney);
        if($dbResult === false){
            return ResultWrapper::fail($this->objDAccount->error(), ErrorCode::$dberror);
        }else{
            return ResultWrapper::success($dbResult);
        }
    }

    /**
     * 删除账户
     *
     * @param array $delFinanceIds  要删除的账户id
     *
     * @return ResultWrapper
     */
    public function delAccount($delAccountIds)
    {
        if (empty($delAccountIds)) {
            return ResultWrapper::fail('参数错误', ErrorCode::$paramError);
        }
        $dbResult = $this->objDAccount->update( ['deleteStatus'=>StatusCode::$delete], $delAccountIds );

        if($dbResult === false){
            return ResultWrapper::fail($this->objDAccount->error(), ErrorCode::$dberror);
        }else{
            return ResultWrapper::success($dbResult);
        }
    }

    /**
     * 账户启用和禁用
     * @param array $params
     * @return ResultWrapper
     */
    public function updateAccountStatus($params)
    {
        $dbResult = $this->objDAccount->update( ['enableStatus'=>$params['enableStatus']], $params['id'] );

        if($dbResult === false){
            return ResultWrapper::fail($this->objDAccount->error(), ErrorCode::$dberror);
        }else{
            return ResultWrapper::success($dbResult);
        }
    }

    /**
     * 账户是否默认
     * @param array $params
     * @return ResultWrapper
     */
    public function updateAccountDefaultStatus($params)
    {
        $result = self::updateDefaultStatus();
        if($result->isSuccess() == false) {
            return ResultWrapper::fail($result->getData(), $result->getErrorCode());
        }

        $dbResult = $this->objDAccount->update( ['isDefault'=>$params['isDefault']], $params['id'] );

        if($dbResult === false){
            return ResultWrapper::fail($this->objDAccount->error(), ErrorCode::$dberror);
        }else{
            return ResultWrapper::success($dbResult);
        }
    }

    public function updateDefaultStatus() {
        $result = $this->objDAccount->update(['isDefault'=>StatusCode::$delete]);

        if($result === false){
            return ResultWrapper::fail($this->objDAccount->error(), ErrorCode::$dberror);
        }else{
            return ResultWrapper::success($result);
        }
    }

    /**
     * 获取所有账户数据
     *
     * @param array $selectParams 过滤条件
     *
     * @return ResultWrapper
     */
    public function getAllAccount($selectParams,$includeMaster = true)
    {
        $limit = $selectParams['limit'];
        unset($selectParams['limit']);
        $offset = $selectParams['offset'];
        unset($selectParams['offset']);

        $whereSql = ' where  enterpriseId = '.$this->enterpriseId .' and deleteStatus = '.StatusCode::$standard;
        if (isset($selectParams['shopId']) && !empty($selectParams['shopId'])) {
            $inMasterSql = $includeMaster ? ",0" : "";
            $whereSql .= ' and shopId in ( ' . $selectParams['shopId'] .$inMasterSql.')';
        }

        $totalSql = 'SELECT COUNT(1) as count FROM ' .$this->objDAccount->get_Table() . $whereSql;
        $dbTotalResult = $this->objDAccount->query($totalSql);
        if ($dbTotalResult === false) {
            return ResultWrapper::fail($this->objDAccount->error(), ErrorCode::$dberror);
        }
        if(empty($dbTotalResult)){
            return ResultWrapper::success([]);
        }

        $sql = 'select * from '. $this->objDAccount->get_Table(). $whereSql.' ORDER BY createTime DESC LIMIT ' . $offset . ' , ' . $limit;
        $dbResult = $this->objDAccount->query($sql);
        if($dbResult === false){
            return ResultWrapper::fail($this->objDAccount->error(), ErrorCode::$dberror);
        }

        // isMem 是为了区分后台会员充值返回不需要余额支付
        if (isset($selectParams['isMem']) && !empty($selectParams['isMem'])) {
            foreach ($dbResult as $accountKey => $accountValue){
                if ($accountValue['name'] == '余额支付'){
                    unset($dbResult[$accountKey]);
                }
            }
            $dbTotalResult[0]['count'] = $dbTotalResult[0]['count']-1;
        }

        //$total = $this->objDAccount->count($selectParams);
        //传时间代表需要重现更新数据
        unset($whereSql);

        foreach ($dbResult as $key => $value){
            $whereSql = '';
            if ( (isset($selectParams['start']) && !empty($selectParams['start']))&&(isset($selectParams['end']) && !empty($selectParams['end'])) ) {
                $where = empty($whereSql) ? ' WHERE ' : '  AND ';
                $whereSql .= $where . ' createTime BETWEEN ' . $selectParams['start'] . ' AND '. $selectParams['end'] .' and accountId = '.$value['id'];
            }else{
                $whereSql .= ' where accountId = '.$value['id'];
            }
            $tableName = $this->objDAccountDetail->getTableName('qianniao_account_detail_' . $this->enterpriseId, $value['id'], $this->cutTable);
            $this->objDAccountDetail->setTable($tableName);
            // 查询流水表拿到时间段的收入，支出
            $detailSql = 'select beginBalance,SUM(income) as income,SUM(expend) as expend from '.$this->objDAccountDetail->get_Table().$whereSql;
            $detailDbResult = $this->objDAccountDetail->query($detailSql);
            if($detailDbResult === false){
                return ResultWrapper::fail($this->objDAccountDetail->error(), ErrorCode::$dberror);
            }
            unset($whereSql);
            //如果是新增的账户，没有detail
            if ( empty($detailDbResult[0]['beginBalance']) && empty($detailDbResult[0]['income']) || empty($detailDbResult[0]['expend']) ){
                $dbResult[$key]['beginMoney'] = isset($value['beginMoney']) ? $value['beginMoney'] : 0.00;
                $dbResult[$key]['income'] = isset($detailDbResult[0]['income']) ? $detailDbResult[0]['income'] : 0.00;
                $dbResult[$key]['expend'] = isset($detailDbResult[0]['expend']) ? $detailDbResult[0]['expend'] : 0.00;
            }else{
                //源数据加本期收入，本期支出
                $dbResult[$key]['beginMoney'] = isset($detailDbResult[0]['endBalance']) ? $detailDbResult[0]['endBalance'] : 0.00;
                $dbResult[$key]['income'] = isset($detailDbResult[0]['income']) ? $detailDbResult[0]['income'] : 0.00;
                $dbResult[$key]['expend'] = isset($detailDbResult[0]['expend']) ? $detailDbResult[0]['expend'] : 0.00;
            }
        }

        $return = [
            'data'  => self::format(array_values($dbResult)),
            'total' => $dbTotalResult[0]['count'],
        ];

        return ResultWrapper::success($return);

    }


    public function format($data) {
        foreach ($data as $k=>$v) {
            $data[$k]['accountCode'] = createCode(StatusCode::$code['account']['prefix'], $v['id'], StatusCode::$code['account']['length']);
        }
        return $data;
    }

    /**
     * 获取特定账户
     * @param int $type  0普通账户  1微信支付  2支付宝支付  3银行卡支付
     * @return ResultWrapper
     */
    public function getDefaultAccount($type=1) {
        $selectParams['enterpriseId'] = $this->enterpriseId;
        $selectParams['deleteStatus'] = StatusCode::$standard;
        $selectParams['type'] = $type;
        // 如果是货到付款 默认退到余额账户
        if($selectParams['type'][0] == StatusCode::$payType['cashPay']){
            $selectParams['type'] = StatusCode::$payType['balance'];
        }
        $dbResult = $this->objDAccount->get($selectParams);

        if($dbResult === false){
            return ResultWrapper::fail($this->objDAccount->error(), ErrorCode::$dberror);
        }else{
            return ResultWrapper::success($dbResult);
        }

    }

    /**
     * 财务今日报表
     */
    public function getTodayStatistics($selectParams)
    {
        $starTime = strtotime(date('Y-m-d 00:00:00'));
        $endTime = strtotime(date('Y-m-d 23:59:59'));

        if (isset($selectParams['start']) && !empty($selectParams['start'])){
            $starTime = $selectParams['start'];
        }

        if (isset($selectParams['end']) && !empty($selectParams['end'])){
            $endTime = $selectParams['end'];
        }
        $tableName = '_'.$this->enterpriseId.'_'.date('Y',$starTime).'_'.ceil(date('m',$starTime) / 3);

        // 查询之前切一下表,避免表不存在导致的sql报错问题
        $this->objDAccount->setTable('qianniao_receive_receipt'.$tableName);
        $this->objDAccount->setTable('qianniao_received'.$tableName);
        $this->objDAccount->setTable('qianniao_pay_receipt'.$tableName);
        $this->objDAccount->setTable('qianniao_paid'.$tableName);
        $this->objDAccount->setTable('qianniao_received_account'.$tableName);

        //应收总额
        $sql = 'select sum(receiveMoney) as money from qianniao_receive_receipt'.$tableName.' where deleteStatus = 5 and auditStatus = 2 and createTime between '.$starTime.' and '.$endTime;
        $dbResult = $this->objDAccount->query($sql);
        if($dbResult === false){
            return ResultWrapper::fail($this->objDAccount->error(), ErrorCode::$dberror);
        }
        $return['getMoney'] = $dbResult[0]['money'] ? $dbResult[0]['money'] : 0;
        //实收总额
        $sql = 'select sum(totalFinalMoney) as money from qianniao_received'.$tableName.' where auditStatus = 2 and createTime between '.$starTime.' and '.$endTime;
        $dbResult = $this->objDAccount->query($sql);
        if($dbResult === false){
            return ResultWrapper::fail($this->objDAccount->error(), ErrorCode::$dberror);
        }
        $return['shouldGetMoney'] = $dbResult[0]['money'] ? $dbResult[0]['money'] : 0;
        //欠款总额
        $return['oweMoney'] = $return['getMoney'] - $return['shouldGetMoney'];
        //毛利总额
        $objDOrder = new DOrder();
        $sql = 'select sum(g.buyNum * g.outCostPrice) as money from qianniao_order_'.$this->enterpriseId.'_1 as o left join qianniao_order_goods_'.$this->enterpriseId.'_1 as g on o.id = g.orderId where o.outStatus = 5 and o.deleteStatus = 5 and g.returnStatus = 0 and o.createTime between '.$starTime.' and '.$endTime;
        $dbResult = $objDOrder->query($sql);
        if($dbResult === false){
            return ResultWrapper::fail($objDOrder->error(), ErrorCode::$dberror);
        }
        $costMoney = $dbResult[0]['money'] ? $dbResult[0]['money'] : 0;
        $return['profitMoney'] = $return['getMoney'] - $costMoney;
        //应付总额
        $sql = 'select sum(payMoney) as money from qianniao_pay_receipt'.$tableName.' where auditStatus = 2 and deleteStatus = 5 and createTime between '.$starTime.' and '.$endTime;
        $dbResult = $this->objDAccount->query($sql);
        if($dbResult === false){
            return ResultWrapper::fail($this->objDAccount->error(), ErrorCode::$dberror);
        }
        $return['payMoney'] = $dbResult[0]['money'] ? $dbResult[0]['money'] : 0;
        //实付总额
        $sql = 'select sum(totalFinalMoney) as money from qianniao_paid'.$tableName.' where auditStatus = 2 and createTime between '.$starTime.' and '.$endTime;
        $dbResult = $this->objDAccount->query($sql);
        if($dbResult === false){
            return ResultWrapper::fail($this->objDAccount->error(), ErrorCode::$dberror);
        }
        $return['shouldPayMoney'] = $dbResult[0]['money'] ? $dbResult[0]['money'] : 0;
        foreach($return as &$value){
            $value = sprintf('%.2f',$value);
        }


        // 各个财务账户今天收款金额
        $sql = "select a.accountId,sum(r.totalFinalMoney) as totalMoney,a.accountName  from qianniao_received".$tableName." r left JOIN qianniao_received_account".$tableName." a on r.id = a.receivedId where r.auditStatus = ".StatusCode::$auditStatus['auditPass']."  and r.createTime between ".$starTime." and ".$endTime." GROUP BY a.accountId";
        $dbResult = $this->objDAccount->query($sql);
        if($dbResult === false){
            return ResultWrapper::fail($this->objDAccount->error(), ErrorCode::$dberror);
        }
        $return['todayreceivedMoney'] = !empty($dbResult) ? $dbResult : [];


        /*
        //微信应收总额 账户类型 0普通账户 1支付宝账户  2微信账户 3银行账户
        $sql = 'select sum(receiveMoney) as money from qianniao_receive_receipt'.$tableName.' r left join qianniao_received_account'.$tableName.' a on a.receivedId = r.id left join qianniao_account aa on a.accountId = aa.id where r.deleteStatus = 5 and r.auditStatus = 2 and aa.type = 2 and r.createTime between '.$starTime.' and '.$endTime;
        $dbResult = $this->objDAccount->query($sql);
        if($dbResult === false){
            return ResultWrapper::fail($this->objDAccount->error(), ErrorCode::$dberror);
        }
        $return['wxGetMoney'] = $dbResult[0]['money'] ? $dbResult[0]['money'] : 0;
        //现金应收总额
        $sql = 'select sum(receiveMoney) as money from qianniao_receive_receipt'.$tableName.' r left join qianniao_received_account'.$tableName.' a on a.receivedId = r.id left join qianniao_account aa on a.accountId = aa.id where r.deleteStatus = 5 and r.auditStatus = 2 and aa.type = 0 and r.createTime between '.$starTime.' and '.$endTime;
        $dbResult = $this->objDAccount->query($sql);
        if($dbResult === false){
            return ResultWrapper::fail($this->objDAccount->error(), ErrorCode::$dberror);
        }
        $return['cashGetMoney'] = $dbResult[0]['money'] ? $dbResult[0]['money'] : 0;
        //支付宝应收总额
        $sql = 'select sum(receiveMoney) as money from qianniao_receive_receipt'.$tableName.' r left join qianniao_received_account'.$tableName.' a on a.receivedId = r.id left join qianniao_account aa on a.accountId = aa.id where r.deleteStatus = 5 and r.auditStatus = 2 and aa.type = 1 and r.createTime between '.$starTime.' and '.$endTime;
        $dbResult = $this->objDAccount->query($sql);
        if($dbResult === false){
            return ResultWrapper::fail($this->objDAccount->error(), ErrorCode::$dberror);
        }
        $return['aliGetMoney'] = $dbResult[0]['money'] ? $dbResult[0]['money'] : 0; */



        return ResultWrapper::success($return);
    }

    /**
     * 计算表名
     * @param $params
     * @param string $table
     * @return ResultWrapper
     * @throws Exception
     */
    public function calTableName($params,$table = 'qianniao_receive_receipt')
    {
        if(empty($params['start'])) return  ResultWrapper::fail('开始时间参数错误', ErrorCode::$paramError);
        if(empty($params['end'])) return ResultWrapper::fail('结束时间参数错误', ErrorCode::$paramError);
        $table && $table = $table.'_'.$this->enterpriseId.'_';
        $startTableTerm = self::setDetailsTableTerm($params['start']);
        $endTableTerm = self::setDetailsTableTerm($params['end']);
        $startYear = substr(date('Y', $params['start']), -2);
        $endYear = substr(date('Y', $params['end']), -2);

        if($startTableTerm == $endTableTerm){//同一个表
            $dbResult = $this->objDAccount->existsTable($table.$startTableTerm);
            if($dbResult){
                return ResultWrapper::success([$table.$startTableTerm]);
            }
            return ResultWrapper::success([]);
        }
        //不同表
        $tableNameArray = [];
        for($ii = $startYear; $ii <= $endYear; $ii++){
            for($i = 1; $i <= 4; $i++){
                $term = $ii.$i;
                if($startTableTerm <= $term && $term <= $endTableTerm){
                    $tableName = $table.$term;
                    $dbResult = $this->objDAccount->existsTable($tableName);
                    if($dbResult){//存在
                        $tableNameArray[] = $tableName;
                    }else{//不存在
                        continue;
                    }
                }
            }
        }
        return ResultWrapper::success($tableNameArray);
    }

    /**
     * Doc: (des="")
     * User: XMing
     * Date: 2021/1/25
     * Time: 11:23 上午
     * @param int $createTime
     * @return string
     */
    public function setDetailsTableTerm($createTime = 0)
    {
        if(!$createTime) $createTime = time();
        return substr(date('Y', $createTime), -2) . ceil(date('m', $createTime) / 3);
    }


}