<?php

namespace App\Http\Controllers\OpdAppointment;

use App\Exports\Opd\OpdStatementExport;
use App\Http\Controllers\Controller;
use App\Models\Account;
use App\Models\AccountTransaction;
use App\Models\AcReceipt;
use App\Models\AcReceiptDetail;
use App\Models\AppointmentShift;
use App\Models\BookingCalendar;
use App\Models\Company;
use App\Models\Doctor;
use App\Models\DoctorChamber;
use App\Models\DocumentManagement;
use App\Models\OpdBlockSerial;
use App\Models\OpdBookingDetail;
use App\Models\OpdBookings;
use App\Models\OpdPrescriptionImage;
use App\Models\ProductBrand;
use App\Models\ProductCategory;
use App\Models\SaleInvoice;
use App\Models\SaleInvoiceDetail;
use App\Models\VoucherCollection;
use App\Models\VoucherCollectionDetail;
use App\Models\VoucherType;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Maatwebsite\Excel\Facades\Excel;

class AppointmentController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index(Request $request, $id = 0)
    {
        if (!$this->checkPermission('opd-appointment-list')) {
            return redirect()->back()->with('info', 'You don\'t have enough permission to access this page.');
        }

        \Session::forget('opd_billing_products_cart');
        $companyDateFormate = phpToJsDateFormat($this->companyDateFormate());
        $data = OpdBookings::select([
            'opd_bookings.*',
            'sale_invoices.main_invoice_no',
            'sale_invoices.billing_amount',
            'sale_invoices.received_amount',
            'sale_invoices.due_amount',
        ])
        ->with(['getDoctorAccount', 'getPatient'])
        ->leftJoin('sale_invoices', 'opd_bookings.sale_invoice_id', '=', 'sale_invoices.id')
        ->when(!in_array('Admin',\Auth()->user()->roles->pluck('name')->all()), function($query) {
            $query->where(function($where) {
                $where->where('opd_bookings.operator_id', \Auth()->id())
                    ->orWhere('opd_bookings.doctor_ac_id',\Auth()->user()->account_id);
            })
            ->where('opd_bookings.company_id', \Auth()->user()->company_id);
        });

        $sort_by = $request->get('sortby') ?? 10;

        if ($request->ajax()) {
            $sort_type    = $request->get('sorttype');
            $search_query = $request->get('query');
            $search_type  = $request->get('search_type');
            $from_date       = isset($request->from_date) && $request->from_date!='' ?Carbon::createFromFormat($this->companyDateFormate(), $request->from_date)->format('Y-m-d'):'';
            if(isset($from_date) && $from_date!=''){
                $data->whereDate('appointment_date_time', $from_date);
            }

            $data = $data->when(!empty($search_query) && !empty($search_type),
                function ($query) use ($search_type, $search_query) {
                    if ($search_type == 'invoice_no') {
                        $query->where('opd_bookings.booking_no', 'LIKE', '%'.$search_query .'%');
                    } else if ($search_type == 'pat_name') {
                        $query->whereHas('getPatient', function ($patient) use ($search_query) {
                            $patient->where('accounts.name', 'LIKE', '%'.$search_query .'%');
                        });
                    } else if ($search_type == 'phone') {
                        $query->where(function ($q) use ($search_query) {
                            $q->whereHas('getPatient.account_contact', function ($patient) use ($search_query) {
                                $patient->where('phone_no', 'LIKE', '%'.$search_query .'%');
                            });
                        });
                    } else if ($search_type == 'abha_no') {
                        $query->where(function ($q) use ($search_query) {
                            $q->whereHas('getPatient', function ($patient) use ($search_query) {
                                $patient->where('accounts.abha_no', 'LIKE', '%'.$search_query .'%');
                            });
                        });
                    } else if ($search_type == 'pat_code') {
                        $query->where(function ($q) use ($search_query) {
                            $q->whereHas('getPatient', function ($patient) use ($search_query) {
                                $patient->where('accounts.unique_register_no', 'LIKE', '%' . $search_query . '%');
                            });
                        });
                    }
                    else if ($search_type == 'dr_name') {
                        $query->where(function ($q) use ($search_query) {
                            $q->whereHas('getDoctorAccount', function ($patient) use ($search_query) {
                                $patient->where('accounts.name', 'LIKE', '%'.$search_query .'%');
                            });
                        });
                    }
                }
            );
        }

        $data = $data->where('opd_bookings.appointment_status', 'Done')
            ->orderBy('opd_bookings.id', 'DESC')
            ->paginate($sort_by);

        if ($request->ajax()) {
            return view('opd-appointment.table', compact('data'));
        }

        return view('opd-appointment.index', compact('data','companyDateFormate'));
    }
    public function reports(Request $request)
    {
        $shifts             = AppointmentShift::select('id', 'name')->get();
        $doctors            = Doctor::with('getAccount')->select('account_id')->get();
        $company            = Company::find(Auth::user()->company_id);
        $companies          = Company::get();
        $chambers           = DoctorChamber::get(['id','name','is_default']);
        $companyDateFormate = phpToJsDateFormat($this->companyDateFormate());
        $data = [];
        
        if ($request->ajax()) {
            $from_date       = $request->from_date?Carbon::createFromFormat($this->companyDateFormate(), $request->from_date)->format('Y-m-d'):date('Y-m-d');
            $to_date         = $request->to_date?Carbon::createFromFormat($this->companyDateFormate(), $request->to_date)->format('Y-m-d'):date('Y-m-d');

            $data = $this->queryFilter($request);
            $bookingType = $request->appointment_status=='Done'?'Appointment':'Booking';
            $fromDate = date('Y-m-d H:i:s',strtotime($from_date));
            $toDate   = date('Y-m-d H:i:s',strtotime($to_date));
            if ($request->shift_id && $request->shift_id != 'all') {
                $shift = AppointmentShift::select('name')->find($request->shift_id);
                $shiftName = $shift->name;
            }else{
                $shiftName = "All";
            }

            if ($request->doctor_ac_id && $request->doctor_ac_id != 'all') {
                $doctor = Doctor::with('getAccount')->select('account_id')->where('account_id',$request->doctor_ac_id)->first();
                $doctorName = $doctor->getAccount->name??'';
            }else{
                $doctorName = "All";
            }

            return view('invoice-formats.opd.statement-table', compact('data', 'companies', 'company','chambers','fromDate','toDate','shiftName','doctorName','bookingType'));
        }
        return view('invoice-formats.opd.statement', compact('shifts', 'doctors', 'data', 'companies', 'company','chambers','companyDateFormate'));
    }
    public function patientHeadPrint(Request $request, $id)
    {
        $sale_invoice = OpdBookings::with('getOpdBookingDetails')
            ->with('getPatient')
            ->with('getDoctorAccount')
            ->with('getCompany')
            ->find($id);
        $voucher_type   = VoucherType::with('voucherSeries')->where('code', 'OPD_BOOKING')->first();

        return view('invoice-formats.opd.opd-patient-head', compact('sale_invoice', 'voucher_type'));
    }
    public function prescriptionPrint(Request $request, $id)
    {

        $sale_invoice = OpdBookings::with('getOpdBookingDetails')
            ->with('getPatient')
            ->with('getDoctorAccount')
            ->with('getCompany')
            ->find($id);
        $voucher_type       = VoucherType::with('voucherSeries')->where('code', 'OPD_BOOKING')->first();
        $doctors = Doctor::select('id')->where('account_id', $sale_invoice->doctor_ac_id)->first();
        $prescriptionImage  = OpdPrescriptionImage::where('doctor_id', $doctors->id)->where('status','1')->get();
        $prescriptionImageHeader  = OpdPrescriptionImage::where('doctor_id', $doctors->id)
        ->where(['status'=>'1','position'=>'Header'])->first();
        return view('invoice-formats.opd.opd-prescription', compact('sale_invoice', 'voucher_type', 'prescriptionImage','prescriptionImageHeader'));
    }

    public function show($id)
    {
        $company_address = \Session::get('company_data')['companies_addresses'];
        $countryCode = $country_code->country_code ?? 'us';

        $sale_invoice = OpdBookings::with('getOpdBookingDetails')
            ->with('getPatient')
            ->with('getDoctorAccount')
            ->with('getCollectionAgent')
            ->with('getRefferalLab')
            ->with('getCompany')
            ->find($id);

        $voucher_type = VoucherType::with('voucherSeries')->where('code', 'OPD_BOOKING')->first();

        $documents    = DocumentManagement::where(['account_id' => $id, 'voucher_type' => 'OPD_BOOKING'])->get();

        return view('opd-appointment.show', compact(['sale_invoice', 'countryCode', 'voucher_type', 'documents']));
    }


    public function editPatients($id)
    {
        $sale_invoice = OpdBookings::where('id', $id)->first();

        if (!$sale_invoice) {
            return response()->json([
                'success' => false,
                'message' => 'Sale Invoice Not Found!',
            ]);
        }

        $patient = Account::getAccount([
            'account_types.type_code' => 'PATIENT',
            'accounts.id' => $sale_invoice->patient_id
        ]);

        if (isset($patient)) {
            $patient->full_name = $patient->name .
                ($patient->code   != '' ? ', ' . $patient->code : '') .
                ($patient->gender != '' ? ', ' . $patient->gender : '') .
                ($patient->age    != '' ? ', ' . $patient->age . ' yrs' : '');
        }

        return view('opd-appointment.edit.patient', compact('patient', 'sale_invoice'));
    }

    public function updatePatient(Request $request, $id)
    {
        $sale_invoice = OpdBookings::where('id', $id)->first();

        if (!$sale_invoice) {
            return response()->json([
                'success' => false,
                'message' => 'Sale Invoice Not Found!',
            ]);
        }

        $sale_invoice = $sale_invoice->update(['patient_id' => $request->patient_id]);

        return response()->json([
            'success' => true,
            'message' => 'Sale Invoice Patient Updated Successfully.',
        ]);
    }

    public function editDoctor($id)
    {
        $sale_invoice = OpdBookings::where('id', $id)->first();

        if (!$sale_invoice) {
            return response()->json([
                'success' => false,
                'message' => 'Sale Invoice Not Found!',
            ]);
        }

        $doctor = Account::getAccount([
            'account_types.type_code' => 'DOCTOR',
            'accounts.id' => $sale_invoice->doctor_ac_id
        ]);

        if (isset($doctor)) {
            $doctor->full_name = $doctor->name .
                ($doctor->code   != '' ? ', ' . $doctor->code : '') .
                ($doctor->gender != '' ? ', ' . $doctor->gender : '') .
                ($doctor->age    != '' ? ', ' . $doctor->age . ' yrs' : '');
        }

        return view('opd-appointment.edit.doctor', compact('doctor', 'sale_invoice'));
    }

    public function updateDoctor(Request $request, $id)
    {
        $sale_invoice = OpdBookings::where('id', $id)->first();

        if (!$sale_invoice) {
            return response()->json([
                'success' => false,
                'message' => 'Sale Invoice Not Found!',
            ]);
        }

        $sale_invoice = $sale_invoice->update(['doctor_ac_id' => $request->doctor_id]);

        return response()->json([
            'success' => true,
            'message' => 'Sale Invoice Doctor Updated Successfully.',
        ]);
    }

    public function editCollectionAgent($invoice_id)
    {
        $sale_invoice = OpdBookings::where('id', $invoice_id)->first();

        if (!$sale_invoice) {
            return response()->json([
                'success' => false,
                'message' => 'Sale Invoice Not Found!',
            ]);
        }

        $collection_agent = Account::getAccount([
            'account_types.type_code' => 'COLLECTION_AGENT',
            'accounts.id' => $sale_invoice->collection_agent_id
        ]);
        if (isset($collection_agent)) {
            $collection_agent->full_name = $collection_agent->name .
                ($collection_agent->code   != '' ? ', ' . $collection_agent->code : '') .
                ($collection_agent->gender != '' ? ', ' . $collection_agent->gender : '') .
                ($collection_agent->age    != '' ? ', ' . $collection_agent->age . ' yrs' : '');
        }

        return view('opd-appointment.edit.collection-agent', compact('sale_invoice', 'collection_agent'));
    }

    public function updateCollectionAgent(Request $request, $invoice_id)
    {
        $sale_invoice = OpdBookings::where('id', $invoice_id)->first();

        if (!$sale_invoice) {
            return response()->json([
                'success' => false,
                'message' => 'Sale Invoice Not Found!',
            ]);
        }

        $sale_invoice = $sale_invoice->update(['collection_agent_id' => $request->collection_id]);

        return response()->json([
            'success' => true,
            'message' => 'Sale Invoice Collection Agent Updated Successfully.',
        ]);
    }

    public function editReferralLab($invoice_id)
    {
        $sale_invoice = OpdBookings::where('id', $invoice_id)->first();

        if (!$sale_invoice) {
            return response()->json([
                'success' => false,
                'message' => 'Sale Invoice Not Found!',
            ]);
        }

        $refferal_lab = Account::getAccount([
            'account_types.type_code' => 'REFERRAL_PARTNER',
            'accounts.id' => $sale_invoice->refferal_lab_id
        ]);
        if (isset($refferal_lab)) {
            $refferal_lab->full_name = $refferal_lab->name .
                ($refferal_lab->code   != '' ? ', ' . $refferal_lab->code : '') .
                ($refferal_lab->gender != '' ? ', ' . $refferal_lab->gender : '') .
                ($refferal_lab->age    != '' ? ', ' . $refferal_lab->age . ' yrs' : '');
        }

        return view('opd-appointment.edit.referral-lab', compact('sale_invoice', 'refferal_lab'));
    }

    public function updateReferralLab(Request $request, $invoice_id)
    {
        $sale_invoice = OpdBookings::where('id', $invoice_id)->first();

        if (!$sale_invoice) {
            return response()->json([
                'success' => false,
                'message' => 'Sale Invoice Not Found!',
            ]);
        }

        $sale_invoice = $sale_invoice->update(['refferal_lab_id' => $request->refferal_id]);

        return response()->json([
            'success' => true,
            'message' => 'Sale Invoice Referral Lab Updated Successfully.',
        ]);
    }

    public function destroy(Request $request)
    {

        $opdBooking = OpdBookings::find($request->id);
        $opb_booking_id = $request->id;
        $saleInvoiceId = $opdBooking->sale_invoice_id;
        $saleInvoiceBatchId = $opdBooking->batch_id;
        OpdBookingDetail::where('opd_booking_id', $request->id)->delete();
        $doctor  = Doctor::where('account_id', $opdBooking->doctor_ac_id)->first();

        $appointment_date_time = Carbon::parse($opdBooking->appointment_date_time);

        BookingCalendar::where('booking_id', $request->id)->delete();

        $opdBlockSerial = OpdBlockSerial::where([
            'slot_no'   => $opdBooking->appointment_serial_no,
            'shift_id'  => $opdBooking->shift_id,
            'doctor_id' => $doctor->id,
            'cdate'     => $appointment_date_time->format('Y-m-d'),
            'ctime'     => $appointment_date_time->format('H:i:s'),
        ]);

        if(isset($saleInvoiceId))
        {
            $saleInvoice = SaleInvoice::find($saleInvoiceId);
            $saleInvoice->update(['deleted_by'=>\Auth::user()->id]);
            $saleInvoice->delete();
            SaleInvoiceDetail::where('sale_invoice_id',$saleInvoiceId)->delete();
        }


       
        $acReceiptData = AcReceipt::where([
            'voucher_id'     => $opb_booking_id,
            'module_code'    => 'OPD_BOOKING'
        ])->first();

        
        AccountTransaction::where([
            'voucher_id'        =>  $opdBooking->id,
            'module_code'       =>  'OPD_BOOKING',
            'batch_id'          =>   $saleInvoiceBatchId,
        ])->delete();
        // dd($accountTransactionData);
        if ($acReceiptData) {
            $accountTransactionData = AccountTransaction::where([
                'voucher_id'        =>  $acReceiptData->id,
                'module_code'       =>  'OPD_BOOKING',
                'transaction_type'  =>  'CREDIT'
            ])->first();
            if ($acReceiptData) {
                $voucherCollectionData = VoucherCollection::where('money_receipt_id', $acReceiptData->id)->first();
                VoucherCollectionDetail::where([
                    'voucher_collection_id' =>  $voucherCollectionData->id,
                    'transaction_id'        =>  $accountTransactionData->id
                ])->delete();
                $voucherCollectionData->update(['deleted_by' => \Auth::user()->id]);
                $voucherCollectionData->delete();

                $acReceiptDataAll = AcReceipt::where([
                    'voucher_id'     => $opb_booking_id,
                    'module_code'    => 'OPD_BOOKING'
                ])->get();

                foreach($acReceiptDataAll as $row){
                    AcReceiptDetail::where([
                        'voucher_id'   => $row->id,
                        'voucher_type' => 'OPD_BOOKING'
                    ])->delete();

                    AccountTransaction::where([
                        'voucher_id'  =>   $row->id,
                        'module_code' =>  'OPD_BOOKING'
                    ])->delete();

                    $row->update(['deleted_by' => \Auth::user()->id]);
                    $row->delete();
                }
            }

            // AccountTransaction::where([
            //     'voucher_id'        =>  $acReceiptData->id,
            //     'module_code'      =>  'OPD_BOOKING'
            // ])->delete();
        }

        $opdBlockSerial->delete();

        $opdBooking->delete();

        return response()->json([
            'success' => true,
            'message' => ['Deleted successfully'],
            'data' => [
                'redirect' => '',
            ]
        ]);
    }

    public function edit($id)
    {
        $company_address = \Session::get('company_data')['companies_addresses'];
        $countryCode = $country_code->country_code ?? 'us';
        $productCategory  = ProductCategory::select(['id', 'name', 'is_default'])->where(['status' => 1])->get();

        $opdBooking    = OpdBookings::with('getOpdBookingDetails')->find($id);
        $data            = [];
        $patient         = $doctor = $collection_agent = $refferal_lab = '';

        if ($opdBooking) {

            $appointment_date_time = Carbon::parse($opdBooking->appointment_date_time);
            $data['shift_id']               = $opdBooking->shift_id;
            $data['time']                   = $appointment_date_time->format('H:i:s');
            $data['date']                   = $appointment_date_time->format('Y-m-d');
            $data['block_no']               = $opdBooking->appointment_serial_no;
            $data['patient_id']             = $opdBooking->patient_id;
            $data['doctor_id']              = $opdBooking->doctor_ac_id;
            $data['refferal_id']            = $opdBooking->refferal_lab_id;
            $data['collection_id']          = $opdBooking->collection_agent_id;
            $data['billing_amount']         = $opdBooking->total_billing;
            $data['discount_amount']        = $opdBooking->special_discount;
            $data['received_amount']        = $opdBooking->amount_recieved;
            $data['due_amount']             = $opdBooking->due_balance_amount;
            $data['discount_type']          = 'FLAT';
            $data['sample_receive_time']    = $opdBooking->sample_receive_time;
            $data['invoice_date']           = $opdBooking->invoice_date;
            $data['sample_receive_date']    = $opdBooking->sample_receive_date;
            $data['payment_mode_id']        = $opdBooking->payment_mode_id;
            $data['payment_term_id']        = $opdBooking->payment_term_id;
            $data['special_case']           = $opdBooking->special_case_id;
            $data['sample_barcode']         = $opdBooking->sample_barcode;
            $data['reff_no']                = $opdBooking->reff_no;
            $data['sale_remarks']           = $opdBooking->remarks;
            $data['remarks_for_technician'] = $opdBooking->remarks_for_technician;
            $data['amount'] = $opdBooking->total_billing;
            $data['special_discount'] = $opdBooking->special_discount;


            foreach ($opdBooking->getOpdBookingDetails as $key => $value) {

                $main_qty = 1;
                $main_qty_amount = $main_qty * $value->price;

                $tax_per = $value->getProduct->getTaxMaster->name != 'Tax Free' ? preg_replace('/[^0-9]/', '', $value->getProduct->getTaxMaster->name ?? 0,) : 0;

                $tax  = (($tax_per ?? 0) / 100) * $main_qty_amount;
                
                $data['products'][] = array(
                    'booking_details_id' => $value->id,
                    'brand_id'        => $value->getProduct->brand_id ?? '',
                    'price_id'        => $value->price_id ?? '',
                    'product_id'      => $value->product_id ?? '',
                    'product'         => $value->getProduct->name ?? '',
                    'hsncode'         => $value->getProduct->hsncode ?? '',
                    'brand'           => $value->getProduct->getProductBrand->name ?? '',
                    'product_price'   => $value->product_price ?? '0',
                    'flat_discount'   => $value->flat_discount ?? '0',
                    'per_discount'    => $value->per_discount ?? '0',
                    'product_details' => '',
                    'vial_code'       => '',
                    'amount'          => $value->amount ?? '0',
                    'tax_per'        => $tax_per,
                    'tax_flat'        => $tax ?? 0,
                );
            }
        }

        \Session::put('opd_billing_products_cart', $data);

        if ($opdBooking && isset($opdBooking->patient_id) && $opdBooking->patient_id != '') {
            $patient = Account::getAccount([
                'account_types.type_code' => 'PATIENT',
                'accounts.id' => $opdBooking->patient_id
            ]);
            if (isset($patient)) {
                $patient->full_name = $patient->name .
                    ($patient->code   != '' ? ', ' . $patient->code : '') .
                    ($patient->gender != '' ? ', ' . $patient->gender : '') .
                    ($patient->age    != '' ? ', ' . $patient->age . ' yrs' : '') .
                    ($patient->phone_no    != '' ? ', ' . $patient->phone_no : '');
            }
        }

        if ($opdBooking && isset($opdBooking->doctor_ac_id) && $opdBooking->doctor_ac_id != '') {
            $doctor = Account::getAccount([
                'account_types.type_code' => 'DOCTOR',
                'accounts.id' => $opdBooking->doctor_ac_id
            ]);
            if (isset($doctor)) {
                $doctor->full_name = $doctor->name .
                    ($doctor->code   != '' ? ', ' . $doctor->code : '') .
                    ($doctor->gender != '' ? ', ' . $doctor->gender : '') .
                    ($doctor->age    != '' ? ', ' . $doctor->age . ' yrs' : '') .
                    ($doctor->phone_no    != '' ? ', ' . $doctor->phone_no : '');
            }
        }

        if ($opdBooking && isset($opdBooking->collection_id) && $opdBooking->collection_id != '') {
            $collection_agent = Account::getAccount([
                'account_types.type_code' => 'COLLECTION_AGENT',
                'accounts.id' => $opdBooking->collection_id
            ]);
            if (isset($collection_agent)) {
                $collection_agent->full_name = $collection_agent->name .
                    ($collection_agent->code   != '' ? ', ' . $collection_agent->code : '') .
                    ($collection_agent->gender != '' ? ', ' . $collection_agent->gender : '') .
                    ($collection_agent->age    != '' ? ', ' . $collection_agent->age . ' yrs' : '') .
                    ($collection_agent->phone_no    != '' ? ', ' . $collection_agent->phone_no : '');
            }
        }

        if ($opdBooking && isset($opdBooking->refferal_id) && $opdBooking->refferal_id != '') {
            $refferal_lab = Account::getAccount([
                'account_types.type_code' => 'REFERRAL_PARTNER',
                'accounts.id' => $opdBooking->refferal_id
            ]);
            if (isset($refferal_lab)) {
                $refferal_lab->full_name = $refferal_lab->name .
                    ($refferal_lab->code   != '' ? ', ' . $refferal_lab->code : '') .
                    ($refferal_lab->gender != '' ? ', ' . $refferal_lab->gender : '') .
                    ($refferal_lab->age    != '' ? ', ' . $refferal_lab->age . ' yrs' : '') .
                    ($refferal_lab->phone_no    != '' ? ', ' . $refferal_lab->phone_no : '');
            }
        }

        return view('opd-appointment.edit', compact(
            'id',
            'productCategory',
            'doctor',
            'patient',
            'countryCode',
            'refferal_lab',
            'collection_agent',
            'data'
        ));
    }

    public function checkIn(Request $request)
    {
        if ($request->ajax()) {
            if($request->status==1)
            {
                $data = array('check_in_status' => $request->status,'check_in_time'=>date('Y-m-d H:i:s', time()));
            }else{
                $data = array('check_in_status' => $request->status,'check_in_time'=>null);
            }
            
            $Update = OpdBookings::where('id', '=', $request->id)->update($data);

            if ($Update) {
                return response()->json([
                    'success' => true,
                    'message' => ['Check In status successfully change'],
                    'data' => [
                        'redirect' => '',
                        'reload' => true,
                    ]
                ]);
            } else {
                return response()->json([
                    'success' => false,
                    'message' => ['Error for change status'],
                    'data' => [
                        'redirect' => '',
                    ]
                ]);
            }
        }
    }


    public function exportStatement(Request $request)
    {
        $from_date       = $request->from_date?Carbon::createFromFormat($this->companyDateFormate(), $request->from_date)->format('Y-m-d'):date('Y-m-d');
        $to_date         = $request->to_date?Carbon::createFromFormat($this->companyDateFormate(), $request->to_date)->format('Y-m-d'):date('Y-m-d');
        $data = $this->queryFilter($request);
        $fileName = 'OPD-Statements'.' From '.$from_date.' To '.$to_date.'.xlsx';
        return Excel::download(new OpdStatementExport($data->toArray()), $fileName);
    }
    public function queryFilter(Request $request){
        $from_date       = $request->from_date?Carbon::createFromFormat($this->companyDateFormate(), $request->from_date)->format('Y-m-d'):date('Y-m-d');
        $to_date         = $request->to_date?Carbon::createFromFormat($this->companyDateFormate(), $request->to_date)->format('Y-m-d'):date('Y-m-d');
        //\DB::enableQueryLog();
        $data = OpdBookings::select([
                'opd_bookings.booking_date_time',
                'opd_bookings.id',
                'opd_bookings.booking_no',
                'opd_bookings.appointment_date_time',
                'opd_bookings.appointment_serial_no',
                'opd_bookings.total_billing',
                'opd_bookings.amount_recieved',
                'opd_bookings.due_balance_amount',
                'opd_bookings.appointment_status',
                'patient.name as patient',
                'patient_title.name as patient_title',
                'patient_genders.name as patient_genders',
                'patient_contact.phone_no as patient_phone_no',
                'patient_contact.date_of_birth as patient_date_of_birth',
                'doctor.name as doctor',
                'doctor_title.name as doctor_title',
                'appointment_shifts.name as appointment_shift',
            ])
            ->leftjoin('accounts as patient','patient.id','opd_bookings.patient_id')
            ->leftjoin('account_titles as patient_title','patient.account_title_id','patient_title.id')
            ->leftjoin('account_contacts as patient_contact','patient.id','patient_contact.account_id')
            ->leftjoin('genders as patient_genders','patient_contact.account_gender_id','patient_genders.id')
            ->leftjoin('accounts as doctor','doctor.id','opd_bookings.doctor_ac_id')
            ->leftjoin('account_titles as doctor_title','doctor.account_title_id','doctor_title.id')
            ->leftjoin('account_contacts as doctor_contact','doctor.id','doctor_contact.account_id')
            ->leftjoin('appointment_shifts','appointment_shifts.id','opd_bookings.shift_id')
            ->when(!in_array('Admin',\Auth()->user()->roles->pluck('name')->all()), function($query) {
                $query->where(function($where) {
                    $where->where('opd_bookings.operator_id', \Auth()->id())
                        ->orWhere('opd_bookings.doctor_ac_id',\Auth()->user()->account_id);
                })
                ->where('opd_bookings.company_id', \Auth()->user()->company_id);
            });
        // if ($request->appointment_status) {
        //     $data = $data->where('appointment_status', $request->appointment_status);
        // }
        if ($request->shift_id && $request->shift_id != 'all') {
            $data = $data->where('opd_bookings.shift_id', $request->shift_id);
        }
        if ($request->doctor_ac_id && $request->doctor_ac_id != 'all') {
            $data = $data->where('opd_bookings.doctor_ac_id', $request->doctor_ac_id);
        }
        if($request->chamber_id!=''){
            $data = $data->where('opd_bookings.chamber_id',$request->chamber_id);
        }
        if ($from_date && $to_date) {
            if ($request->appointment_status == 'Done') {
                $data = $data->whereDate('opd_bookings.appointment_date_time', '>=', date('Y-m-d', strtotime($from_date)))
                    ->whereDate('opd_bookings.appointment_date_time', '<=', date('Y-m-d', strtotime($to_date)));
            } else {
                $data = $data->whereDate('booking_date_time', '>=', date('Y-m-d', strtotime($from_date)))
                    ->whereDate('opd_bookings.booking_date_time', '<=', date('Y-m-d', strtotime($to_date)));
            }
        }
        $data = $data->orderBy('opd_bookings.id', 'asc')->get();
       // dd(\DB::getQueryLog());
       return $data;
    }

}