wanyue_app/app/admin/controller/finance/WriteOffController.php

<?php

namespace app\admin\controller\finance;

use app\common\Util;
use app\model\SysCategory;
use app\model\SysDept;
use support\Db;
use support\Request;

class WriteOffController
{
    public function list(Request $request)
    {
        $page = $request->get('page',1);
        $pageSize = $request->get('pageSize',20);
        $days = $request->get('days',[]);
        $salePremisesId = $request->get('sale_premises_id','');
        $writeOffPremisesId = $request->get('write_off_premises_id','');
        $categoryId = $request->get('category_id','');
        $goodsName = $request->get('goods_name','');
        if (!empty($days)) {
            $days[0] = strtotime($days[0]);
            $days[1] = strtotime($days[1]);
            if (date('m', $days[0]) != date('m', $days[1])) {
                return json_fail('暂不支持跨月查询');
            }

            $month = date('Ym', $days[0]);
            $days[1] = strtotime(date('Y-m-d', $days[1]) . " 23:59:59");
        } else {
            $month = date('Ym');
        }

        if(!Util::schema()->hasTable("data_used_{$month}")){
            return json_fail('暂无数据');
        }
        $categorySearchIds = '';
        if (!empty($categoryId)){
            $categorySearchPath = SysCategory::where('category_id',$categoryId)->value('category_super_path');
            $categorySearchPath .= '#'. $categoryId.'#';
            
            $categorySearchIds = SysCategory::where('category_super_path','like',$categorySearchPath.'%')->pluck('category_id')->toArray();
            $categorySearchIds = array_merge($categorySearchIds,[$categoryId]);
            $categorySearchIds = implode(',',$categorySearchIds);
        }
        
        $where = " where 1 ";
        if (!empty($days)) {
            $where .= "and du.data_used_addtimes between {$days[0]} and {$days[1]} ";
        }
        if(!empty($salePremisesId)){
            $where .= " and JSON_EXTRACT(du.join_data_used_object_json,'$.salesman_premises_id') = {$salePremisesId} ";
        }
        if (!empty($writeOffPremisesId)){
            $where .= " and du.join_data_used_dept_id = {$writeOffPremisesId}";
        }
        if (!empty($categorySearchIds)){
            $where .= " and g.join_goods_category_id in({$categorySearchIds})";
        }
        if (!empty($goodsName)){
            $where .= " and g.goods_name like '%{$goodsName}%' ";
        }

        $totalSql = "
            select 
                count(*) as total 
            from app_data_used_{$month} as du
                left join app_goods as g on JSON_EXTRACT(du.join_data_used_object_json,'$.goods_id') = g.goods_id 
                {$where}";
        $total = Db::select($totalSql);

        $recordStart = ($page - 1) * 20;
        $sql = "select 
                g.goods_name,g.goods_sales_price,g.join_goods_category_id,gs.goods_sku_specs_json,wu.user_name as wu_username, su.user_name as su_username,du.data_used_addtimes,
                JSON_EXTRACT(du.join_data_used_object_json,'$.charge.charge_premises') as write_off_premises, su.join_user_dept_id as premises_id,du.data_used_count as nbr, 
                ssd.dept_id as ssd_dept_id, ssd.dept_name as ssd_dept_name 
            from app_data_used_{$month} as du 
                left join app_goods as g on REPLACE(JSON_EXTRACT(du.join_data_used_object_json,'$.goods_id'),'\"','') = CAST(g.goods_id AS CHAR)
                left join app_goods_sku as gs on JSON_EXTRACT(du.join_data_used_object_json,'$.goods_sku_id') = gs.goods_sku_id 
                left join app_sys_user as wu on du.join_data_used_user_id = wu.user_id 
                left join app_sys_user as su on JSON_EXTRACT(du.join_data_used_object_json,'$.salesman_id') = su.user_id 
                left join app_sys_dept as ssd on JSON_EXTRACT(du.join_data_used_object_json,'$.salesman_premises_id') = ssd.dept_id 
                {$where} 
            order by du.data_used_addtimes desc 
            limit " . $recordStart . ',' . $pageSize;
        $rows = Db::select($sql);

        foreach ($rows as &$row){
            $row->salesman_premises = '';
            if (!empty($row->premises_id) && $row->premises_id != 0){
                $premises = SysDept::where('dept_id',$row->premises_id)
                    ->select('dept_id','dept_name')
                    ->first();
                $row->salesman_premises = !empty($premises) ? $premises->dept_name : '';
            }
            if(!empty($row->goods_sku_specs_json)){
                $specsJson = json_decode($row->goods_sku_specs_json,true);
                $skuTitle = '';
                foreach($specsJson as $key => $item){
                    if(is_array($item)){
                        $item = implode('',$item);
                    }
                    $skuTitle .= $key.':'.$item.',';
                }

                $row->sku_title = rtrim($skuTitle,',');
            }
            $categoryClassify = 'SERVICE';
            $row->category = '';
            if(!empty($row->join_goods_category_id)){
                $category = SysCategory::where('category_id',$row->join_goods_category_id)->select('category_name','category_classify')->first();
                $row->category = $category->category_name ?? '';
                $categoryClassify = $category->category_classify ?? 'SERVICE';
            }
            $row->write_off_premises = trim($row->write_off_premises,"\"");
            $row->data_used_addtimes = date('Y-m-d H:i:s',$row->data_used_addtimes);
        }

        return json_success('', [
            'page' => $page,
            'pageSize' => $pageSize,
            'total' => $total[0]->total,
            'rows' => $rows
        ]);
    }

    public function statistics(Request $request)
    {
        $days = $request->get('days',[]);
        $salePremisesId = $request->get('sale_premises_id','');
        $writeOffPremisesId = $request->get('write_off_premises_id','');
        $categoryId = $request->get('category_id','');
        $goodsName = $request->get('goods_name','');
        if (!empty($days)) {
            $days[0] = strtotime($days[0]);
            $days[1] = strtotime($days[1]);
            if (date('m', $days[0]) != date('m', $days[1])) {
                return json_fail('暂不支持跨月查询');
            }

            $month = date('Ym', $days[0]);
            $days[1] = strtotime(date('Y-m-d', $days[1]) . " 23:59:59");
        } else {
            $month = date('Ym');
        }

        if(!Util::schema()->hasTable("data_used_{$month}")){
            return json_fail('暂无数据');
        }
        $categorySearchIds = '';
        if (!empty($categoryId)){
            $categorySearchPath = SysCategory::where('category_id',$categoryId)->value('category_super_path');
            $categorySearchPath .= '#'. $categoryId.'#';
            
            $categorySearchIds = SysCategory::where('category_super_path','like',$categorySearchPath.'%')->pluck('category_id')->toArray();
            $categorySearchIds = array_merge($categorySearchIds,[$categoryId]);
            $categorySearchIds = implode(',',$categorySearchIds);
        }
        
        $where = " where 1 ";
        if (!empty($days)) {
            $where .= "and du.data_used_addtimes between {$days[0]} and {$days[1]} ";
        }
        if(!empty($salePremisesId)){
            $where .= " and JSON_EXTRACT(du.join_data_used_object_json,'$.salesman_premises_id') = {$salePremisesId} ";
        }
        if (!empty($writeOffPremisesId)){
            $where .= " and du.join_data_used_dept_id = {$writeOffPremisesId}";
        }
        if (!empty($categorySearchIds)){
            $where .= " and g.join_goods_category_id in({$categorySearchIds})";
        }
        if (!empty($goodsName)){
            $where .= " and g.goods_name like '%{$goodsName}%' ";
        }

        $sql = "select 
                SUM(data_used_count) as total,data_used_classify 
            from app_data_used_{$month} as du
                left join app_goods as g on JSON_EXTRACT(du.join_data_used_object_json,'$.goods_id') = g.goods_id 
                {$where}
            group by data_used_classify";
        $rows = Db::select($sql);
        $rows = json_decode(json_encode($rows),true);
        
        //核销归类 ORDER-PACKAGE=套包订单|ORDER-DISHES=点餐订单|ROLE-SERVICE=角色权益服务卡|ROLE-GOODS=角色权益产品卡|APPOINTMENT=后付费预约|ORDER=预付费订单
        $statistics = ['service'=>0,'meals'=>0,'quota'=>0,'package'=>0,'goods'=>0,'appointment'=>0];
        foreach ($rows as &$row){
            if($row['data_used_classify'] == 'ORDER-DISHES'){
                $statistics['meals'] = $row['total'];
            }elseif($row['data_used_classify'] == 'ORDER-PACKAGE'){
                $statistics['package'] = $row['total'];
            }elseif($row['data_used_classify'] == 'ROLE-SERVICE'){
                $statistics['quota'] = $row['total'];
            }elseif($row['data_used_classify'] == 'ROLE-GOODS'){
                $statistics['goods'] = $row['total'];
            }elseif($row['data_used_classify'] == 'APPOINTMENT'){
                $statistics['appointment'] = $row['total'];
            }elseif($row['data_used_classify'] == 'ORDER'){
                $statistics['service'] = $row['total'];
            }
        }

        return json_success('',$statistics);
    }

    public function records()
    {
        $sql = "select a.appointment_id as benefit_id,a.appointment_addtimes as addtimes,m.member_mobile,c.member_cert_name,b.member_benefit_name as benefit_name,a.appointment_done_json as used_json,a.appointment_done_datetime from app_appointment as a  
                left join app_member as m on m.member_id = a.join_appointment_member_id
                left join app_member_cert as c on c.join_cert_member_id = a.join_appointment_member_id
                left join app_member_benefit as b on b.join_benefit_member_id = a.join_appointment_member_id
             UNION 
             select q.member_quota_id as benefit_id,q.member_quota_addtimes as addtimes,q.member_quota_used_json as used_json, q.member_quota_name as benefit_name,m.member_mobile,c.member_cert_name from app_member_quota as q 
                left join app_member as m on m.member_id = q.join_quota_member_id
                left join app_member_cert as c on c.join_cert_member_id = q.join_quota_member_id
                order by addtimes desc limit 0,10";

        $records = Db::select($sql);

        dump($records);
    }
}