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', ['2024-06-01','2024-06-30']);
        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('暂无数据');
        }

        $totalSql = "
            select count(*) as total from app_data_used_{$month}";
        $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 
            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 
                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 
            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,"\"");
        }

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

    public function statistics(Request $request)
    {
        $days = $request->get('days', ['2024-06-01','2024-06-30']);
        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('暂无数据');
        }

        $totalSql = "
            select count(*) as total from app_data_used_{$month}";
        $total = Db::select($totalSql);
        $sql = "select 
                COUNT(*) as count,g.join_goods_category_id 
            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 
            ";
        $rows = Db::select($sql);
        dump($rows);

        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,"\"");
        }
    }

    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);
    }
}