php/app/controller/merchant/finance/Reconciliation.php

<?php


namespace app\controller\merchant\finance;

use app\common\repositories\finance\FinanceRepository;
use app\controller\api\entropybarter\User;
use crmeb\basic\BaseController;
use think\facade\Db;

class Reconciliation extends BaseController
{
    public function lst()
    {
        try {
            [$page, $limit] = $this->getPage();
            $where = $this->request->params(['date']);
            //商户ID
            $mer_id = $this->request->merId();
            $order_lst = Db::name('store_order')
                ->alias('so')
                ->order('create_time', 'desc')
                ->rightJoin('user u', 'u.uid=so.uid')
                ->where('u.test_user', '<', '1')
                ->where('so.mer_id', $mer_id)
                ->where('so.test_order', 0)
                ->where('paid', 1)
                ->page($page, $limit)
                ->where('so.status', '<>', '-1')
                ->field('order_sn,pay_time as create_time,extension_one,so.pay_price');
            $order_lst = $this->date_where($order_lst, $where['date'])->select();
            $count = Db::name('store_order')
                ->alias('so')
                ->rightJoin('user u', 'u.uid=so.uid')
                ->where('u.test_user', '<', '1')
                ->where('so.mer_id', $mer_id)
                ->where('so.test_order', 0)
                ->where('paid', 1)
                ->where('so.status', '<>', '-1')
                ->count('order_id');

            if (!$order_lst) {
                return app('json')->success([]);
            }
            $order_lst->each(function ($item) use ($mer_id) {
                $where = [
                    'type'     => 2,
                    'mer_id'   => $mer_id,
                    'pm'       => 1,
                    'status'   => 2,
                    'on_line'  => 1,
                    'order_no' => $item['order_sn']
                ];
                $commission_data = Db::name('entropy_barter_bill')->where($where)->select()->toArray();
                $item['one'] = '0.00';
                $item['two'] = '0.00';
                $item['three'] = '0.00';
                $item['four'] = '0.00';
                $item['precipitate'] = $item['extension_one'];
                if (!empty($commission_data)) {

                    foreach ($commission_data as $_k => $_v) {
//                        dump($_v);
                        if ($_v['to_user_type'] == 1) {
                            $item['one'] = $_v['number'];
                            $item['precipitate'] = bcsub($item['precipitate'], $_v['number'], 2);
                            $item['one'] = $this->jiangli_user($_v);

                        } elseif ($_v['to_user_type'] == 2) {
                            $item['two'] = $_v['number'];
                            $item['precipitate'] = bcsub($item['precipitate'], $_v['number'], 2);
                            $item['two'] = $this->jiangli_user($_v);
                        } elseif ($_v['to_user_type'] == 3) {
                            $item['three'] = $_v['number'];
                            $item['precipitate'] = bcsub($item['precipitate'], $_v['number'], 2);
                            $item['three'] = $this->jiangli_user($_v);
                        } elseif ($_v['to_user_type'] == 4) {
                            $item['four'] = $_v['number'];
                            $item['precipitate'] = bcsub($item['precipitate'], $_v['number'], 2);
                            $item['four'] = $this->jiangli_user($_v);
                        }

//                        $item['cae']=$_v;
                    }
                }
                return $item;
            });
            //总佣金
            $all_commission = Db::name('store_order')
                ->alias('so')
                ->rightJoin('user u', 'u.uid=so.uid')
                ->where('u.test_user', '<', '1')
                ->where('so.mer_id', $mer_id)
                ->where('paid', 1)
                ->page($page, $limit)
                ->where('so.status', '<>', '-1')
                ->sum('extension_one');
            //已提现佣金
            $withdrawal_where = [
                'type'    => 2,
                'mer_id'  => $mer_id,
                'pm'      => 2,
                'pm2_msg' => '提现',
                'status'  => 2,
                'on_line' => 1
            ];
            $withdrawal_commission = Db::name('entropy_barter_bill')->where($withdrawal_where)->sum('number');

            //未提现佣金
            $nowithdrawal_commission = Db::name('entropy_barter_user_290')->where('type', '>=', 3)->sum('wallet');
            //升级经理奖励
            $manager_reward = Db::name('entropy_barter_user_290')->where('type', '>=', 3)->count('eb_id');
            $jingli_bili = systemConfig('manager_reward');
            $manager_reward = bcmul($manager_reward, $jingli_bili, 2);
            //沉淀佣金
            $precipitation_amount = $all_commission - $withdrawal_commission - $nowithdrawal_commission;
            $precipitation_amount = sprintf("%.2f", $precipitation_amount);
            $return_data = [
                'all_commission'          => $all_commission,
                'withdrawal_commission'   => $withdrawal_commission,
                'nowithdrawal_commission' => $nowithdrawal_commission,
                'manager_reward'          => $manager_reward,
                'precipitation_amount'    => $precipitation_amount,
                'list'                    => $order_lst,
                'count'                   => $count
            ];
            return app('json')->success($return_data);
        } catch (\Exception $e) {
            Db::name('log')->insert(['data' => '获取佣金对账错误：' . $e->getFile() . '：' . $e->getLine() . '【' . $e->getMessage() . '】']);
            return app('json')->fail('获取佣金信息失败');
        }
    }

    /**
     * 列表中的时间条件
     */
    public function date_where($Db, $where, $prefix = 'so.create_time', $field = '-')
    {
        if (!isset($where)) return $Db;
        switch ($where) {
            case 'today':
                $Db->whereBetween($prefix, [date('Y-m-d H:i:s', strtotime('today')), date('Y-m-d H:i:s', strtotime('tomorrow -1second'))]);
                break;
            case 'week':
                $Db->whereBetween($prefix, [date('Y-m-d H:i:s', strtotime('this week 00:00:00')), date('Y-m-d H:i:s', strtotime('next week 00:00:00 -1second'))]);
                break;
            case 'month':
                $Db->whereBetween($prefix, [date('Y-m-d H:i:s', strtotime('first Day of this month 00:00:00')), date('Y-m-d H:i:s', strtotime('first Day of next month 00:00:00 -1second'))]);
                break;
            case 'year':
                $Db->whereBetween($prefix, [date('Y-m-d H:i:s', strtotime('this year 1/1')), date('Y-m-d H:i:s', strtotime('next year 1/1 -1second'))]);
                break;
            case 'yesterday':
                $Db->whereBetween($prefix, [date('Y-m-d H:i:s', strtotime('yesterday')), date('Y-m-d H:i:s', strtotime('today -1second'))]);
                break;
            case 'quarter':
                list($startTime, $endTime) = getMonth();
                $Db = $Db->where($prefix, '>', $startTime);
                $Db = $Db->where($prefix, '<', $endTime);
                break;
            case 'lately7':
                $Db = $Db->where($prefix, 'between', [date('Y-m-d', strtotime("-7 day")), date('Y-m-d H:i:s')]);
                break;
            case 'lately30':
                $Db = $Db->where($prefix, 'between', [date('Y-m-d', strtotime("-30 day")), date('Y-m-d H:i:s')]);
                break;
            default:
                if (strstr($where, $field) !== false) {
                    list($startTime, $endTime) = explode($field, $where);
                    if (strlen($startTime) == 4) {
                        $Db->whereBetweenTime($prefix, date('Y-m-d H:i:s', strtotime($startTime)), date('Y-m-d H:i:s', strtotime($startTime . ' +1day -1second')));
                    } else {
                        if ($startTime == $endTime) {
                            $Db = $Db->whereDay($prefix, $startTime);
                        } else {
                            $Db = $Db->where($prefix, '>', date('Y-m-d H:i:s', strtotime($startTime)));
                            $Db = $Db->where($prefix, '<', date('Y-m-d H:i:s', strtotime($endTime)));
                        }
                    }
                }
                break;
        }
        return $Db;
    }

    public function jiangli_user($_v)
    {
        switch ($_v['to_user_type_1']) {
            case 3:
                $number = $_v['number'] . '(经理)';
                break;
            case 4:
                $number = $_v['number'] . '(总监)';
                break;
            case 5:
                $number = $_v['number'] . '(总裁)';
                break;
            default:
                $number = $_v['number'] . '( )';
        }
        return $number;
    }

    /**
     * 订单概况
     * @return mixed
     */
    public function order_overview()
    {
        $mer_id = $this->request->merId();

        $financeRepository = app()->make(FinanceRepository::class);
        $data = $financeRepository->order_overview($mer_id);
        return app('json')->success('ok', $data);
    }


    /**
     * 用户概览
     * @return mixed
     * @throws \Exception
     */
    public function user_overview()
    {
        $mer_id = $this->request->merId();

        $financeRepository = app()->make(FinanceRepository::class);
        $data = $financeRepository->user_overview($mer_id);
        return app('json')->success('ok', $data);
    }

    public function red_integral_overview()
    {

        $mer_id = $this->request->merId();
        $where = "mer_id = " . $mer_id;
        $type = input('type', 1);
        $day = input('day', '');

        if ($type == 1 and $day != '') {
            $where .= " and DATE(create_time) = '" . $day . "'";
        }
        if ($type == 2 and $day != '') {
            $date = new \DateTime($day);
            $year = $date->format('Y');
            $month = $date->format('m');
            $where .= " and YEAR(create_time) = " . $year . " AND MONTH(create_time) = " . $month;
        }
        $reward_socre1 = Db::name('enterprise_user_score')->where($where)->where('pm', 1)->sum('reward_socre');
        $reward_socre2 = Db::name('enterprise_user_score')->where($where)->where('pm', 2)->sum('reward_socre');
        $reward_socre3 = Db::name('enterprise_user_score')->where($where)->where('pm', 3)->sum('reward_socre');
        $reward_socre4 = Db::name('enterprise_user_score')->where($where)->where('pm', 4)->sum('reward_socre');
        $list1 = array(
            array('className' => 'el-icon-s-goods', 'count' => set_price_rtrim($reward_socre1), 'name' => '系统奖励总数'),
            array('className' => 'el-icon-s-goods', 'count' => set_price_rtrim($reward_socre3), 'name' => '人工奖励总数'),
        );
        $list2 = array(
            array('className' => 'el-icon-s-goods', 'count' => set_price_rtrim($reward_socre2), 'name' => '系统销毁总数'),
            array('className' => 'el-icon-s-goods', 'count' => set_price_rtrim($reward_socre4), 'name' => '人工销毁总数'),
        );

        $data[] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($reward_socre1 + $reward_socre3), 'name' => '红积分奖励总数', 'list' => $list1];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($reward_socre2 + $reward_socre4), 'name' => '红积分销毁总数', 'list' => $list2];
        return app('json')->success('ok', $data);
    }

    public function transaction_overview()
    {
        $type = input('type', 1);
        $day = input('day', '');
        $mer_id = $this->request->merId();

        $financeRepository = app()->make(FinanceRepository::class);
        $arr = $financeRepository->jieyu($type,$day,$mer_id);

        $data['left'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($arr['yingyemoney']), 'name' => '营业总额'];
        $data['left'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($arr['shouyi']), 'name' => '收益总额'];
        $data['left'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($arr['tixinan']), 'name' => '提现到账总额'];
        $data['left'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($arr['hf_fee_amt']), 'name' => '汇付通道服务费金额'];

        $list1 = array(
            array('className' => 'el-icon-s-goods', 'count' => set_price_rtrim($arr['tixinan']), 'name' => '提现到账总额'),
            array('className' => 'el-icon-s-goods', 'count' => set_price_rtrim($arr['my_money']), 'name' => '自营订单入账总额'),
        );
        $data['right'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($arr['ruzhang']), 'name' => '入账总额', 'list' => $list1];

        return app('json')->success('ok', $data);
    }

    /**
     * 查询团队人员uid
     * @param $where
     * @param array $data
     * @return array
     */
    function jisuantuandui($where, $data = [])
    {
        $enterprise_user_team = DB::name('enterprise_user')->where($where)->column('uid');
        $arr = array_merge($data, $enterprise_user_team);
        foreach ($enterprise_user_team as $v) {
            $where['node_uid'] = $v;
            $arr = $this->jisuantuandui($where, $arr);
        }
        return $arr;
    }

    public function agent_lst()
    {
        [$page, $limit] = $this->getPage();
        $where = $this->request->params(['date', 'phone', 'level']);
        $mer_id = $this->request->merId();
        $where2['eu.mer_id'] = $mer_id;
        $where2['d.mer_id'] = $mer_id;
        $where2['d.state'] = 1;
        $query = Db::name('enterprise_user')->alias('eu')
            ->join('enterprise_agent_divvy d', 'd.id=eu.level')
            ->join('user u', 'u.uid=eu.uid')
            ->field('u.uid,u.account,u.account,u.nickname,d.name,eu.tad')
            ->where($where2)
            ->when(isset($where['phone']) && $where['phone'] !== '', function ($query) use ($where) {
                $query->whereLike('u.account', '%' . $where['phone'] . '%');
            })->when(isset($where['level']) && $where['level'] !== '', function ($query) use ($where) {
                $query->where('eu.level', $where['level']);
            });
        $date['count'] = $query->count();
        $date['list'] = $query->page($page, $limit)->select();

        return app('json')->success($date);
    }

    public function agent_detail_lst()
    {
        $uid = $this->request->param('uid');
        [$page, $limit] = $this->getPage();
        $mer_id = $this->request->merId();
        $where['mer_id'] = $mer_id;
        $where['uid'] = $uid;
        $where['type'] = 4;
        $query = Db::name('enterprise_user_bill')->where($where);
        $date['count'] = $query->count();
        $date['list'] = $query->page($page, $limit)->select();

        return app('json')->success($date);
    }

    public function day_bill_list()
    {
        [$page, $limit] = $this->getPage();
        $mer_id = $this->request->merId();
        $where['mer_id'] = $mer_id;
        $query = Db::name('enterprise_day_bill')->where($where);
        $date['count'] = $query->count();
        $date['list'] = $query->page($page, $limit)->order('day desc')->select();

        return app('json')->success($date);
    }

    public function month_bill_list()
    {
        [$page, $limit] = $this->getPage();
        $mer_id = $this->request->merId();
        $where['mer_id'] = $mer_id;
        $query = Db::name('enterprise_month_bill')->field('id,month ,flow,expenditure,accounting')->where($where);
        $date['count'] = $query->count();
        $date['list'] = $query->page($page, $limit)->order('month desc')->select();
        return app('json')->success($date);
    }

    public function day_detail_bill_list()
    {
//        $type=input('type',1);
//        $day=input('day','');
//        $mer_id=$this->request->merId();
//        $where="mer_id = ".$mer_id;
//        if($type== 1 and $day!=''){
//            $where.=" and DATE(transaction_time) = '".$day."'";
//        }
//        if($type== 2 and $day!=''){
//            $date = new \DateTime($day);
//            $year = $date->format('Y');
//            $month = $date->format('m');
//
//            $where.=" and YEAR(transaction_time) = ".$year." AND MONTH(transaction_time) = ".$month;
//        }
//        [$page, $limit] = $this->getPage();
//        $query=Db::name('enterprise_bill_item')->field('*')->where($where);
//        $date['count']=$query->count();
//        $date['list']=$query->page($page,$limit)->order('id desc')->select();
        $data = [];
        return app('json')->success($data);
    }

    //定时任务写日账单，月账单
    public function cron($day,$mer_id=496)
    {
//        $day=date('Y-m-d', strtotime('-1 days'));
//        $enterprise_bill_item=[];
//        $order= $order_query->select();
//        foreach ($order as $v) {
//            $arr = [];
//            $arr['mer_id'] = $mer_id;
//            $arr['transaction_time'] = $v['pay_time'];
//            $arr['money'] = $v['pay_price'];
//            $arr['uid'] = $v['uid'];
//            $arr['order_sn'] = $v['order_sn'];
//            $arr['type'] = 1;
//            $enterprise_bill_item[] = $arr;
//        }
//        if ($enterprise_bill_item) {
//            Db::name('enterprise_bill_item')->insertAll($enterprise_bill_item);
//        }

        Db::name('enterprise_day_bill')->where('mer_id', $mer_id)->where('day', $day)->delete();

        $day_flow_query = Db::name('enterprise_platform_income_detail')
            ->where('mer_id', $mer_id)
            ->when($mer_id, function ($query) use ($mer_id) {
                $query->where('deal_type', -1);//没有流水
            }, function ($query) use ($mer_id) {
                $query->whereIn('deal_type', [3]);
            })

            ->where('deal_type', 4)
            ->where('pm', 1);
        $day_flow_query_month = clone $day_flow_query;
        // 日订单流水
        $day_flow = $day_flow_query->where("DATE(deal_time) = '" . $day . "'")->sum('money');

        $accounting_query = Db::name('enterprise_platform_income_detail')
            ->where('mer_id', $mer_id)
            ->when($mer_id, function ($query) use ($mer_id) {
                $query->where('deal_type', 1);
            }, function ($query) use ($mer_id) {
                $query->whereIn('deal_type', [6,7]);
            })
            ->where('pm', 1);
        $accounting_query_month = clone $accounting_query;
        // 入账金额
        $day_accounting = $accounting_query->where("DATE(deal_time) = '" . $day . "'")->sum('money');

        $expenditure_query = Db::name('enterprise_platform_income_detail')
            ->when($mer_id, function ($query) use ($mer_id) {
                $query->where('mer_id', $mer_id)
                    ->where('deal_type', -1);//没有流水
            }, function ($query) use ($mer_id) {
                $query->whereIn('deal_type', [4,5]);
            });
        $expenditure_query_month = clone $expenditure_query;
        // 日支出总额
        $day_expenditure = 0;
        if($mer_id==0){
            $day_expenditure = $expenditure_query->where("DATE(deal_time) = '" . $day . "'")
                ->whereIn('deal_type', [4,5])
                ->where('mer_id',0)
                ->sum('money');
        }

        $insert = [];
        $insert['mer_id'] = $mer_id;
        $insert['day'] = $day;
        $insert['flow'] = $day_flow;
        $insert['expenditure'] = $day_expenditure;
        $insert['accounting'] = $day_accounting;

        $previousDate = date('Y-m-d', strtotime($day . ' -1 day'));
        $balance_zuotian = Db::name('enterprise_day_bill')->where('day', $previousDate)->where('mer_id', $mer_id)->value('balance');
        $balance = $balance_zuotian ? $balance_zuotian : 0;

        if($mer_id){
            $tixian = Db::name('user_extract')->where("DATE(status_time) = '" . $day . "'")->where('mer_id', $mer_id)->where('status', 1)->sum('extract_price');
            $service_money = Db::name('user_extract')->where("DATE(status_time) = '" . $day . "'")->where('mer_id', $mer_id)->where('status', 1)->sum('service_money');
            $tixian -= $service_money;
            $ylj = Db::name('user_bill')->where('mer_id', $mer_id)->where('status', 1)->where("DATE(take_time) = '" . $day . "'")->sum('number');
            $insert['balance'] = $day_accounting - $tixian + $balance - $ylj;
        }else{
            $tixian =  Db::name('enterprise_platform_income_detail')
                ->where("deal_time >'$day'")
                ->where("deal_time <'$day 23:59:59'")
                ->where('deal_type', 5)
                ->sum('money');
            $insert['balance'] = $day_accounting - $tixian + $balance ;
        }



        Db::name('enterprise_day_bill')->insertGetId($insert);


        $month_list[] = date('Y-m', strtotime('-4 month'));
        $month_list[] = date('Y-m', strtotime('-3 month'));
        $month_list[] = date('Y-m', strtotime('-2 month'));
        $month_list[] = date('Y-m', strtotime('-1 month'));
        $month_list[] = date('Y-m');
        foreach ($month_list as $month) {
            Db::name('enterprise_month_bill')->where('mer_id',$mer_id)->where('month', $month)->delete();

            $flow_query_month_clone = clone $day_flow_query_month;
            $accounting_query_month_clone = clone $accounting_query_month;
            $expenditure_query_month_clone = clone $expenditure_query_month;

            $startDate = date('Y-m-01 00:00:00', strtotime($month));
            $endDate = date("Y-m-d 00:00:00", strtotime('+1 month', strtotime($month)));

            // 月订单流水
            $money_flow = $flow_query_month_clone->whereBetween('deal_time', [$startDate, $endDate])->sum('money');

            // 月入账总额
            $money_accounting = $accounting_query_month_clone->whereBetween('deal_time', [$startDate, $endDate])->sum('money');

            // 月支出总额
            $money_expenditure = $expenditure_query_month_clone->whereBetween('deal_time', [$startDate, $endDate])->sum('money');

            $insert2 = [];
            $insert2['month'] = $month;
            $insert2['mer_id'] = $mer_id;
            $insert2['flow'] = $money_flow;
            $insert2['expenditure'] = $money_expenditure;
            $insert2['accounting'] = $money_accounting;
            Db::name('enterprise_month_bill')->insertGetId($insert2);
        }

    }
}