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

<?php

namespace app\admin\controller\member;

use app\controller\Curd;
use app\model\MemberBenefit;
use app\model\RuleAddedComponent;
use app\model\SysUser;
use support\Db;
use support\Request;

class WriteOffController extends Curd
{
    public function __construct()
    {
        // $this->model = new ;
    }

    public function list(Request $request)
    {
        $page = $request->get('page', 1);
        $pageSize = $request->get('pageSize', 50);
        $keywords = $request->get('keywords','');
        $userId = $request->get('user_id','');
        $premises = $request->get('premises','');
        $date = $request->get('date',[]);

        $appointmentList = Db::table('appointment')
            ->leftJoin('member','member.member_id' ,'=','appointment.join_appointment_member_id')
            ->leftJoin('member_cert','member_cert.join_cert_member_id' ,'=','appointment.join_appointment_member_id')
            ->select('appointment.appointment_id as quota_id', 'appointment.join_appointment_member_id as member_id','appointment.appointment_classify as classify', 'appointment.appointment_done_json as used_json', 'appointment.appointment_done_datetime as used_time','appointment.join_appointment_member_benefit_id as benefit_id',
                'member.member_mobile','member_cert.member_cert_name'
            )
            ->when($keywords != '',function($query) use ($keywords){
                $query->where('member.member_mobile','like','%'.$keywords.'%');
            })->when(!empty($date),function($query) use ($date){
                $date[0] = date('Y-m-d',strtotime($date[0])).' 00:00:00';
                $date[1] = date('Y-m-d',strtotime($date[1])). ' 23:59:59';
                $query->whereBetween('appointment.appointment_done_datetime',$date);
            })
            ->when($userId != '',function($query) use ($userId){
                $query->whereJsonContains('appointment.appointment_done_json->charge->charge_user_id',$userId);
            })
            ->when($premises != '',function($query) use ($premises){
                $query->whereJsonContains('appointment.appointment_done_json->charge->charge_premises',$premises);
            })
            ->where('appointment_status', 'DONE');
        $quotaList = Db::table('member_quota')
            ->leftJoin('member','member.member_id' ,'=','member_quota.join_quota_member_id')
            ->leftJoin('member_cert','member_cert.join_cert_member_id' ,'=','member_quota.join_quota_member_id')
            ->select('member_quota.member_quota_id as quota_id', 'member_quota.join_quota_member_id as member_id','member_quota.member_quota_category as classify', 'member_quota.member_quota_used_json as used_json', "member_quota.member_quota_extend_json->writeOffTime as used_time","member_quota.join_member_rule_added_component_id as benefit_id",
                'member.member_mobile','member_cert.member_cert_name')
                
            ->when($keywords != '',function($query) use ($keywords){
                $query->where('member.member_mobile','like','%'.$keywords.'%');
            })->when(!empty($date),function($query) use ($date){
                $date[0] = date('Y-m-d',strtotime($date[0])).' 00:00:00';
                $date[1] = date('Y-m-d',strtotime($date[1])). ' 23:59:59';
                $query->whereBetween('member_quota.member_quota_extend_json->writeOffTime',$date);
            })
            ->when($userId != '',function($query) use ($userId){
                $query->whereJsonContains('member_quota.member_quota_used_json->charge->charge_user_id',$userId);
            })
            ->when($premises != '',function($query) use ($premises){
                $query->whereJsonContains('member_quota.member_quota_used_json->charge->charge_premises',$premises);
            })
            ->where('member_quota_status', 'USED')
//            ->toSql();
            ->union($appointmentList);
            $total = $quotaList->count('*');

            $quotaList = $quotaList->orderBy('used_time', 'DESC')
            ->forPage($page, $pageSize)
            ->get();

        $rows = [];
        foreach ($quotaList as $item) {
            $username = '';
            $premises = '';
            $benefitName = '';
            if (!empty($item->used_json)) {
                $usedJson = json_decode($item->used_json,true);
                if (isset($usedJson['charge']) && isset($usedJson['charge']['charge_user_id'])){
                    $username = SysUser::where('user_id',$usedJson['charge']['charge_user_id'])->value('user_name');
                }
                if (isset($usedJson['charge']) && isset($usedJson['charge']['charge_premises'])){
                    $premises = $usedJson['charge']['charge_premises'];
                }
                if (substr($item->quota_id,0,2) == 'AP'){
                    $benefitName = MemberBenefit::where('member_benefit_id',$item->benefit_id)->value('member_benefit_name');
                }elseif (substr($item->quota_id,0,2) == 'MQ'){
                    $benefitName = RuleAddedComponent::where('rule_added_component_id',$item->benefit_i)->value('rule_added_component_name');
                }
            }
            $classify = "SERVICE";
            if(substr($item->quota_id,0,2) == "MQ"){
                $classify = "QUOTA";
            }elseif(!empty($item->classify) && $item->classify == 'PACKAGE'){
                $classify = "PACKAGE";
            }
            $rows[] = [
                'quota_id' => $item->quota_id,
                'username' => $username,
                'premises' => $premises,
                'benefit_name' => $benefitName,
                'used_time'=>$item->used_time,
                'member_name' => $item->member_cert_name ?  $item->member_cert_name.'-' : '',
                'member_mobile' => $item->member_mobile ?  $item->member_mobile : '',
                'classify'=>$classify,
                'nbr'=>1
            ];

        }

        return json_success('', compact('rows', 'page', 'pageSize', 'total'));
    }
}