<?php

namespace App\Http\Controllers\ReferralVoucher;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use App\Models\ReferralVoucher;
use App\Models\ReferralVoucherDetail;
use App\Models\SaleInvoiceDetail;
use App\Models\IncentiveSlabDetail;
use App\Models\IncentiveSlab;
use App\Models\Account;
use App\Models\AccountContact;
use App\Models\BusinessDivision;
use App\Models\SaleInvoiceBatch;
use App\Models\VoucherType;
use App\Models\SaleInvoice;
use App\Exports\ReferralVoucherOverAllStatement;
use Carbon\Carbon;
use Maatwebsite\Excel\Facades\Excel;

class ReferralVouchersController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index(Request $request)
    {
        $data = ReferralVoucher::select([
            'referral_vouchers.id',
            'referral_vouchers.voucher_no',
            'referral_vouchers.incentive_date',
            'referral_vouchers.from_date',
            'referral_vouchers.to_date',
            'referral_voucher_details.revenue_amount',
            'referral_voucher_details.incentive_amount',
            'accounts.name as consultant_name',
            'account_titles.name as account_title',
        ])
        ->leftjoin('accounts','referral_vouchers.consultant_id','accounts.id')
        ->leftjoin('referral_voucher_details','referral_vouchers.id','referral_voucher_details.referral_voucher_id')
        ->leftjoin('account_titles','account_titles.id','accounts.account_title_id');

        if ($request->ajax()) {
            $sort_by      = $request->get('sortby') ?? 10;
            $sort_type    = $request->get('sorttype');
            $search_query = $request->get('query');

            $data = $data->when(!empty($search_query), function ($query) use ($search_query) {
                return $query->where('date_formats.name', 'like', '%'.$search_query.'%');
            })
            ->paginate($sort_by);

            return view('referral-vouchers.table', compact('data','sort_by'));
        }
        else
        {
            $data = $data->paginate(10);
            $sort_by = 10;

            return view('referral-vouchers.index',compact('data','sort_by'));
        }
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create(Request $request)
    {
        if ($request->ajax()) {
            $sort_by      = $request->get('sortby') ?? 10;
            $sort_type    = $request->get('sorttype');
            $search_query = $request->get('query');
            $start_date   = date('Y-m-1', strtotime($request->get('month_year')));
            $end_date     = date('Y-m-t', strtotime($request->get('month_year')));
            $doctor       = $request->get('doctor');

            $data = $this->saleInvoiceData($start_date, $end_date, $doctor);

            $incentive_settings = IncentiveSlabDetail::select(
                'incentive_slab_details.id',
                'incentive_slab_details.product_id',
                'incentive_slab_details.calculation_type',
                'incentive_slab_details.percentage',
                'incentive_slab_details.price',
                'incentive_slabs.calculation_type as default_calculation_type',
                'incentive_slabs.percentage as default_percentage',
                'incentive_slabs.price as default_price'
            )
            ->leftjoin('incentive_slabs','incentive_slab_details.slab_id','incentive_slabs.id')
            ->leftjoin('account_types','account_types.id','incentive_slabs.account_type_id')
            ->where('account_types.type_code', 'DOCTOR')
            ->groupBy('incentive_slab_details.product_id')
            ->orderBy('incentive_slabs.is_default', 'DESC')
            ->get()
            ->keyBy('product_id');

            $incentive_slab = IncentiveSlab::select(
                'incentive_slabs.calculation_type',
                'incentive_slabs.percentage',
                'incentive_slabs.price'
            )
            ->leftjoin('account_types','account_types.id','incentive_slabs.account_type_id')
            ->where('account_types.type_code', 'DOCTOR')
            ->orderBy('incentive_slabs.is_default', 'DESC')
            ->first();

            return view('referral-vouchers.commission.table', compact('data','incentive_settings','incentive_slab'));
        }
        /*if ($request->ajax()) {
            $sort_by      = $request->get('sortby') ?? 10;
            $sort_type    = $request->get('sorttype');
            $search_query = $request->get('query');
            $start_date   = date('Y-m-d', strtotime($request->get('start_date')));
            $end_date     = date('Y-m-d', strtotime($request->get('end_date')));
            $doctor       = $request->get('doctor');

            $data = SaleInvoiceDetail::select([
                'sale_invoice_batches.consultant_id',
                'sale_invoice_details.id',
                'accounts.id as doctor_id',
                'products.id as product_id',
                'sale_invoice_details.basic_amount',
                'accounts.name as doctor',
                'accounts.code as doctor_code',
                'incentive_settings.date_upto',
                'incentive_slabs.slab_name',
                'sale_others_transactions.price as discount_amount',
                'incentive_slab_details.calculation_type',
                'incentive_slab_details.percentage',
                'incentive_slab_details.price',
            ])
                ->leftjoin('sale_invoice_batches','sale_invoice_details.sale_invoice_id', '=', 'sale_invoice_batches.invoice_id')
                ->leftjoin('accounts','sale_invoice_batches.consultant_id', '=', 'accounts.id')
                ->leftjoin('sale_invoices','sale_invoice_details.sale_invoice_id', '=', 'sale_invoices.id')
                ->leftjoin('incentive_settings','incentive_settings.account_id', '=', 'accounts.id')
                ->leftjoin('products','sale_invoice_details.product_id', '=', 'products.id')
                ->leftjoin('incentive_slabs','incentive_slabs.id', '=', 'incentive_settings.slab_id')

                ->leftjoin('incentive_slab_details', 'incentive_slab_details.slab_id','incentive_slabs.id')

                ->leftjoin('sale_others_transactions', function($join) {
                    $join->on('sale_others_transactions.sale_invoice_batch_id', '=', 'sale_invoice_batches.id')
                        ->where('transaction_type', 'DEBIT');
                })
                ->when(!empty($doctor), function($query) use ($doctor) {
                    $query->where('sale_invoice_batches.consultant_id', $doctor);
                })
                ->when(!empty($start_date) && empty($end_date), function($query) use ($start_date) {
                    $query->whereDate('sale_invoice_details.created_at', '>', date('Y-m-d', strtotime($start_date)));
                })
                ->when(empty($start_date) && !empty($end_date), function($query) use ($end_date) {
                    $query->whereDate('sale_invoice_details.created_at', '<', date('Y-m-d', strtotime($end_date)));
                })
                ->when(!empty($start_date) && !empty($end_date), function($query) use ($doctor, $start_date, $end_date) {
                    $query->whereBetween('sale_invoice_details.created_at', [
                        date('Y-m-d', strtotime($start_date)),
                        date('Y-m-d', strtotime($end_date))
                    ])
                    ->whereNotIn('sale_invoice_details.sale_invoice_id', function ($subquery) use ($doctor, $start_date, $end_date) {
                        $subquery->select('sale_invoice_batches.invoice_id')
                            ->from('sale_invoice_batches')
                            ->join('referral_vouchers', 'sale_invoice_batches.consultant_id', '=', 'referral_vouchers.consultant_id')
                            ->whereColumn('sale_invoice_batches.consultant_id', 'accounts.id')
                            ->where(function ($query) use ($start_date, $end_date) {
                                $query->whereBetween('referral_vouchers.from_date', [$start_date, $end_date])
                                    ->orWhereBetween('referral_vouchers.to_date', [$start_date, $end_date])
                                    ->orWhere(function ($query) use ($start_date, $end_date) {
                                        $query->whereDate('referral_vouchers.from_date', '<=', $start_date)
                                            ->whereDate('referral_vouchers.to_date', '>=', $end_date);
                                    });
                            });
                    });
                })
                ->whereNotNull('sale_invoice_batches.consultant_id')
                ->whereNotNull('accounts.id')
                ->whereNotNull('products.id')
                ->groupBy('sale_invoice_batches.consultant_id')
                ->groupBy('products.id')
                ->get()
                ->groupBy('consultant_id')
                ->map(function ($data, $consultantId) {
                    $doctor      = $data[0]->doctor ?? '';
                    $doctor_code = $data[0]->doctor_code ?? '';

                    return [
                        'doctor' => $doctor . (!empty($doctor_code) ? ' ('.$doctor_code.')' : ''),
                        'vouchers' => $data,
                    ];
                });

                $incentive_settings = IncentiveSlabDetail::select(
                    'incentive_slab_details.id',
                    'incentive_slab_details.product_id',
                    'incentive_slab_details.calculation_type',
                    'incentive_slab_details.percentage',
                    'incentive_slab_details.price'
                )
                ->leftjoin('incentive_slabs','incentive_slab_details.slab_id','incentive_slabs.id')
                ->leftjoin('account_types','account_types.id','incentive_slabs.account_type_id')
                ->where('account_types.type_code', 'DOCTOR')
                ->groupBy('incentive_slab_details.product_id')
                ->orderBy('incentive_slabs.is_default', 'DESC')
                ->get()
                ->keyBy('product_id');

            return view('referral-vouchers.commission.table', compact('data','incentive_settings'));
        }*/
        else
        {
            $data   = [];
            $doctor = Account::select([
                    'accounts.id',
                    'accounts.name',
                    'accounts.code'
                ])
                ->leftjoin('account_types', 'account_types.id', 'accounts.account_type_id')
                ->where('account_types.type_code', 'DOCTOR')
                ->where('accounts.status', '1')
                ->groupBy('accounts.id')
                ->orderBy('accounts.name')
                ->get();

            $divisions = BusinessDivision::select('id','name')->where('status','1')->get();

            return view('referral-vouchers.create', compact('data', 'doctor','divisions'));
        }
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        $division   = $request->get('division') ?? '';
        $start_date = date('Y-m-1', strtotime($request->get('month_year')));
        $end_date   = date('Y-m-t', strtotime($request->get('month_year')));
        $doctor     = $request->get('doctor');

        $data = $this->saleInvoiceData($start_date, $end_date, $doctor);

        $incentive_settings = IncentiveSlabDetail::select(
            'incentive_slab_details.id',
            'incentive_slab_details.product_id',
            'incentive_slab_details.calculation_type',
            'incentive_slab_details.percentage',
            'incentive_slab_details.price',
            'incentive_slabs.id as slab_id',
            'incentive_slabs.calculation_type as default_calculation_type',
            'incentive_slabs.percentage as default_percentage',
            'incentive_slabs.price as default_price'
        )
        ->leftjoin('incentive_slabs','incentive_slab_details.slab_id','incentive_slabs.id')
        ->leftjoin('account_types','account_types.id','incentive_slabs.account_type_id')
        ->where('account_types.type_code', 'DOCTOR')
        ->groupBy('incentive_slab_details.product_id')
        ->orderBy('incentive_slabs.is_default', 'DESC')
        ->get()
        ->keyBy('product_id');

        $incentive_slab = IncentiveSlab::select(
            'incentive_slabs.id',
            'incentive_slabs.calculation_type',
            'incentive_slabs.percentage',
            'incentive_slabs.price'
        )
        ->leftjoin('account_types','account_types.id','incentive_slabs.account_type_id')
        ->where('account_types.type_code', 'DOCTOR')
        ->orderBy('incentive_slabs.is_default', 'DESC')
        ->first();

        foreach($data as $key=> $row) {
            $total_basic_amount = $total_commission = $incentive_slab_id = 0 ;

            foreach($row['vouchers'] as $value) {
                $incentive_setting = !empty($incentive_settings[$value->product_id]) ? $incentive_settings[$value->product_id] : [];

                $amount = $value->basic_amount ?? 0 ;
                $discount   = !empty($value->discount_amount) ? $value->discount_amount : (!empty($value->sale_discount_amount) ? $value->sale_discount_amount : '0');
                $amount     = $amount - $discount;
                $commission = 0 ;

                if (!empty($value->calculation_type) && (!empty($value->percentage) || !empty($value->price))) {
                    $type       = $value->calculation_type ?? '' ;
                    $percentage = $value->percentage ?? 0 ;
                    $price      = $value->price ?? 0 ;
                    $commission = 0 ;

                    if ($type == 'quantity') {
                        $commission = $price;
                    } else if ($type == 'percentage') {
                        $commission = ($percentage / 100) * $amount;
                    }

                    $incentive_slab_id = $value->slab_id;
                } else if (!empty($incentive_setting)) {
                    $type = $incentive_setting->calculation_type != '' ? $incentive_setting->calculation_type : ($incentive_setting->default_calculation_type ?? '') ;
                    $percentage = $incentive_setting->percentage != '' ? $incentive_setting->percentage : ($incentive_setting->default_percentage ?? '0') ;
                    $price = $incentive_setting->price != '' ? $incentive_setting->price : ($incentive_setting->default_price ?? '0') ;
                    $commission = 0 ;
                    $commission = 0 ;

                    if ($type == 'quantity') {
                        $commission = $price;
                    } else if ($type == 'percentage') {
                        $commission = ($percentage / 100) * $amount;
                    }
                    $incentive_slab_id = $incentive_setting->slab_id;
                } else if (!empty($incentive_slab)) {
                    $type = $incentive_slab->calculation_type != '' ? $incentive_slab->calculation_type : '';
                    $percentage = $incentive_slab->percentage != '' ? $incentive_slab->percentage : '0';
                    $price = $incentive_slab->price != '' ? $incentive_slab->price : '0';
                    $commission = 0 ;

                    if ($type == 'quantity') {
                        $commission = $price;
                    } else if ($type == 'percentage') {
                        $commission = ($percentage / 100) * $amount;
                    }
                    $incentive_slab_id = $incentive_slab->id;
                }

                $total_basic_amount += $amount;
                $total_commission += $commission;
            }

                $voucher_type = VoucherType::with('voucherSeries')->where('code', 'REFERRAL_VOUCHER')->where(['status' => 1])->first();

                # last voucher count for main voucher type invoice number
                $voucher_type_last_count      = ReferralVoucher::max('last_voucher_type_count');

                $voucher_type_count = !empty($voucher_type_last_count) ? $voucher_type_last_count + 1 : (($voucher_type->voucherSeries->start_from ?? 0) + 1 ?? 1);

                $voucher_no = $this->generateCode(
                    $voucher_type_count,
                    ($voucher_type->voucherSeries->prefix ?? 'INV'),
                    ($voucher_type->voucherSeries->postfix ?? ''),
                    ($voucher_type->voucherSeries->separator ?? '-'),
                    ($voucher_type->voucherSeries->length ?? 5)
                );

                $voucher = ReferralVoucher::create([
                    'incentive_date'     => date('Y-m-d'),
                    'month'              => date('m'),
                    'year'               => date('Y'),
                    'from_date'          => date('Y-m-d', strtotime($start_date)),
                    'to_date'            => date('Y-m-d', strtotime($end_date)),
                    'consultant_id'      => $value->doctor_id,
                    'financial_year_id'  => $this->financialyear(),
                    'voucher_no'         => $voucher_no,
                    'voucher_type_code'  => 'REFERRAL_VOUCHER',
                    'incentive_slab_id'  => $incentive_slab_id,
                    'created_by'         => \Auth()->id(),
                    'last_voucher_type_count' => $voucher_type_count,
                    'payment_status'     => 0,
                ]);

                ReferralVoucherDetail::create([
                    'referral_voucher_id'    => $voucher->id,
                    'Bussiness_divission_id' => $division,
                    'revenue_amount'         => $total_basic_amount,
                    'incentive_amount'       => $total_commission
                ]);
        }

        return response()->json([
            'success' => true,
            'message' => "Referral voucher generated successfully."
        ]);
    }

    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
        //
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function edit($id)
    {
        $voucher = ReferralVoucher::find($id);

        if (!$voucher) {
            return redirect()->back()->with('error','Referral Voucher not found.');
        }

        $divisions = BusinessDivision::select('id','name')->where('status','1')->get();

        return view('referral-vouchers.edit', compact('voucher','divisions'));
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {
        $validator = \Validator::make($request->all(), [
            'from_date' => 'required',
            'to_date'   => 'required',
            'division'  => 'required',
        ]);

        if ($validator->fails()) {
            return redirect()->back()->with('error', $validator->getMessageBag()->first())->withInput();
        }

        $voucher = ReferralVoucher::find($id);

        if (!$voucher) {
            return redirect()->back()->with('error','Referral Voucher not found.');
        }

        $division   = $request->division ?? '';
        $start_date = date('Y-m-1', strtotime($request->get('month_year')));
        $end_date   = date('Y-m-t', strtotime($request->get('month_year')));
        $doctor     = $voucher->consultant_id;

        $data = $this->saleInvoiceData($start_date,$end_date,$voucher->consultant_id,$id);

        $incentive_settings = IncentiveSlabDetail::select(
            'incentive_slab_details.id',
            'incentive_slab_details.product_id',
            'incentive_slab_details.calculation_type',
            'incentive_slab_details.percentage',
            'incentive_slab_details.price',
            'incentive_slabs.calculation_type as default_calculation_type',
            'incentive_slabs.percentage as default_percentage',
            'incentive_slabs.price as default_price'
        )
        ->leftjoin('incentive_slabs','incentive_slab_details.slab_id','incentive_slabs.id')
        ->leftjoin('account_types','account_types.id','incentive_slabs.account_type_id')
        ->where('account_types.type_code', 'DOCTOR')
        ->groupBy('incentive_slab_details.product_id')
        ->orderBy('incentive_slabs.is_default', 'DESC')
        ->get()
        ->keyBy('product_id');

        $incentive_slab = IncentiveSlab::select(
            'incentive_slabs.id',
            'incentive_slabs.calculation_type',
            'incentive_slabs.percentage',
            'incentive_slabs.price'
        )
        ->leftjoin('account_types','account_types.id','incentive_slabs.account_type_id')
        ->where('account_types.type_code', 'DOCTOR')
        ->orderBy('incentive_slabs.is_default', 'DESC')
        ->first();

        $total_basic_amount = $total_commission = $incentive_slab_id = 0 ;

        if (isset($data[$doctor]['vouchers'])) {
            foreach($data[$doctor]['vouchers'] as $value) {
                $incentive_setting = !empty($incentive_settings[$value->product_id]) ? $incentive_settings[$value->product_id] : [];

                $amount     = $value->basic_amount ?? 0 ;
                $discount   = !empty($value->discount_amount) ? $value->discount_amount : (!empty($value->sale_discount_amount) ? $value->sale_discount_amount : '0');
                $amount     = $amount - $discount;
                $commission = 0 ;

                if (!empty($value->calculation_type) && (!empty($value->percentage) || !empty($value->price))) {
                    $type       = $value->calculation_type ?? '' ;
                    $percentage = $value->percentage ?? 0 ;
                    $price      = $value->price ?? 0 ;
                    $commission = 0 ;

                    if ($type == 'quantity') {
                        $commission = $price;
                    } else if ($type == 'percentage') {
                        $commission = ($percentage / 100) * $amount;
                    }

                } else if (!empty($incentive_setting)) {
                    $type = $incentive_setting->calculation_type != '' ? $incentive_setting->calculation_type : ($incentive_setting->default_calculation_type ?? '') ;
                    $percentage = $incentive_setting->percentage != '' ? $incentive_setting->percentage : ($incentive_setting->default_percentage ?? '0') ;
                    $price = $incentive_setting->price != '' ? $incentive_setting->price : ($incentive_setting->default_price ?? '0') ;
                    $commission = 0 ;
                    $commission = 0 ;

                    if ($type == 'quantity') {
                        $commission = $price;
                    } else if ($type == 'percentage') {
                        $commission = ($percentage / 100) * $amount;
                    }
                } else if (!empty($incentive_slab)) {
                    $type = $incentive_slab->calculation_type != '' ? $incentive_slab->calculation_type : '';
                    $percentage = $incentive_slab->percentage != '' ? $incentive_slab->percentage : '0';
                    $price = $incentive_slab->price != '' ? $incentive_slab->price : '0';
                    $commission = 0 ;

                    if ($type == 'quantity') {
                        $commission = $price;
                    } else if ($type == 'percentage') {
                        $commission = ($percentage / 100) * $amount;
                    }
                }

                $total_basic_amount += $amount;
                $total_commission += $commission;
            }
        }

        $voucher->update([
            'from_date' => $start_date,
            'to_date'   => $end_date
        ]);

        ReferralVoucherDetail::updateOrCreate([
            'referral_voucher_id' => $id,
        ],[
            'Bussiness_divission_id' => $division,
            'revenue_amount'         => $total_basic_amount,
            'incentive_amount'       => $total_commission
        ]);

        return redirect()->route('referral-vouchers.index')->with('success','Referral Voucher updated successfully.');
    }

    public function dateFormat($dateString, $format)
    {
        //dd($format);
        try {
            if (!empty($dateString)) {
                $dateTime = Carbon::createFromFormat($format, $dateString);
                //dd($dateTime->format('Y-m-d H:i:s'));

                return $dateTime->format('Y-m-d H:i:s');
            } else {
                return null;
            }
        } catch (InvalidFormatException $e) {
            Log::error('Date format error: ' . $e->getMessage() . ' Input date: ' . $dateString);

            return null; // Or any other appropriate action
        }
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function destroy(Request $request)
    {
        ReferralVoucher::find($request->id)->delete();
        ReferralVoucherDetail::where('referral_voucher_id',$request->id)->delete();

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

    public function saleInvoiceData($start_date='',$end_date='',$doctor='',$referral_voucher_id='')
    {
        $excludedDatesQuery = ReferralVoucher::select('consultant_id','from_date','to_date')
            ->where(function($query) use ($start_date, $end_date) {
                $query->whereBetween('from_date', [$start_date, $end_date])
                    ->orWhereBetween('to_date', [$start_date, $end_date])
                    ->orWhere(function($query) use ($start_date, $end_date) {
                        $query->where('from_date', '<=', $start_date)
                            ->where('to_date', '>=', $start_date);
                    })
                    ->orWhere(function($query) use ($start_date, $end_date) {
                        $query->where('from_date', '<=', $end_date)
                            ->where('to_date', '>=', $end_date);
                    });
            })
            ->when(!empty($referral_voucher_id), function($query) use ($referral_voucher_id) {
                $query->where('id', '<>', $referral_voucher_id);
            })
            ->when(!empty($doctor), function($query) use ($doctor) {
                $query->where('consultant_id', $doctor);
            });

        $data = SaleInvoiceDetail::select([
            'sale_invoice_batches.consultant_id',
            'sale_invoice_details.id',
            'accounts.id as doctor_id',
            'products.id as product_id',
            'sale_invoice_details.basic_amount',
            'sale_invoice_details.discount_amount as sale_discount_amount',
            'accounts.name as doctor',
            'accounts.code as doctor_code',
            'incentive_settings.date_upto',
            'incentive_slabs.slab_name',
            'sale_others_transactions.price as discount_amount',
            'incentive_slabs.id as slab_id',
            'incentive_slab_details.calculation_type',
            'incentive_slab_details.percentage',
            'incentive_slab_details.price',
            'sale_invoice_details.created_at'
        ])
            ->leftjoin('sale_invoice_batches','sale_invoice_details.sale_invoice_id', '=', 'sale_invoice_batches.invoice_id')
            ->leftjoin('accounts','sale_invoice_batches.consultant_id', '=', 'accounts.id')
            ->leftjoin('sale_invoices','sale_invoice_details.sale_invoice_id', '=', 'sale_invoices.id')
            ->leftjoin('incentive_settings','incentive_settings.account_id', '=', 'accounts.id')
            ->leftjoin('products','sale_invoice_details.product_id', '=', 'products.id')
            ->leftjoin('incentive_slabs','incentive_slabs.id', '=', 'incentive_settings.slab_id')

            ->leftjoin('incentive_slab_details', 'incentive_slab_details.slab_id','incentive_slabs.id')

            ->leftjoin('sale_others_transactions', function($join) {
                $join->on('sale_others_transactions.sale_invoice_batch_id', '=', 'sale_invoice_batches.id')
                    ->where('transaction_type', 'DEBIT');
            })
            /*->leftJoinSub($excludedDatesQuery, 'excluded_dates', function($join) {
                $join->on('sale_invoice_batches.consultant_id', '=', 'excluded_dates.consultant_id');
            })
            ->where(function($query) {
                $query->whereNull('excluded_dates.consultant_id')
                      ->orWhereRaw('sale_invoice_details.created_at NOT BETWEEN excluded_dates.from_date AND excluded_dates.to_date');
            })*/
            ->whereNotExists(function ($query) use ($excludedDatesQuery) {
                $query->select(\DB::raw(1))
                      ->from('referral_vouchers')
                      ->whereColumn('referral_vouchers.consultant_id', 'sale_invoice_batches.consultant_id')
                      ->whereRaw('sale_invoice_details.created_at between referral_vouchers.from_date and referral_vouchers.to_date');
            })
            ->when(!empty($doctor), function($query) use ($doctor) {
                $query->where('accounts.id', $doctor);
            })
            ->when(!empty($start_date) && empty($end_date), function($query) use ($start_date) {
                $query->whereDate('sale_invoice_details.created_at', '>', $start_date);
            })
            ->when(empty($start_date) && !empty($end_date), function($query) use ($end_date) {
                $query->whereDate('sale_invoice_details.created_at', '<', $end_date);
            })
            ->when(!empty($start_date) && !empty($end_date), function($query) use ($doctor, $start_date, $end_date) {
                if ($start_date == $end_date) {
                    $query->whereDate('sale_invoice_details.created_at', $start_date);
                } else {
                    $query->whereBetween('sale_invoice_details.created_at', [
                        $start_date,
                        $end_date
                    ]);
                }
            })
            ->whereNotNull('sale_invoice_batches.consultant_id')
            ->whereNotNull('accounts.id')
            ->whereNotNull('products.id')
            ->groupBy('sale_invoice_batches.consultant_id')
            ->groupBy('sale_invoice_details.id')
            ->get()
            ->groupBy('consultant_id')
            ->map(function ($data, $consultantId) {
                $doctor      = $data[0]->doctor ?? '';
                $doctor_code = $data[0]->doctor_code ?? '';

                return [
                    'doctor' => $doctor . (!empty($doctor_code) ? ' ('.$doctor_code.')' : ''),
                    'vouchers' => $data,
                ];
            });

        return $data;
    }

    public function printDoctorStatement(Request $request, $id)
    {
        $voucher = ReferralVoucher::select(
                'referral_vouchers.*',
                'accounts.name as doctor',
                'accounts.code as doctor_code'
            )
            ->leftjoin('accounts','referral_vouchers.consultant_id', '=', 'accounts.id')
            ->where('referral_vouchers.id', $id)
            ->first();

        if (!$voucher) {
            return redirect()->back()->with('error','Referral Voucher not found.');
        }

        $details = ReferralVoucherDetail::where('referral_voucher_id',$id)->first();

        $start_date   = $voucher->from_date;
        $end_date     = $voucher->to_date;
        $doctor       = $voucher->consultant_id;
        $statement_by       = $request->statement_by ?? 'product';

        $data = SaleInvoiceDetail::select([
            'sale_invoice_details.id',
            \DB::raw('sum(sale_invoice_details.basic_amount) as basic_amount'),
            \DB::raw('sum(sale_invoice_details.discount_amount) as sale_discount_amount'),
            \DB::raw('count(sale_invoice_details.id) as referral_count'),
            // 'sale_invoice_details.discount_amount as sale_discount_amount',
            'sale_invoice_details.product_id',
            'sale_invoice_details.created_at',
            'sale_invoices.main_invoice_no',
            'products.name as product',
            'accounts.name as doctor',
            'accounts.code as doctor_code',
            'incentive_settings.date_upto',
            'incentive_slabs.slab_name',
            'patient.name as patient_name',
            'patient.code as patient_code',
            'product_brands.name as brand',
            'sale_others_transactions.price as discount_amount',
        ])
            ->leftjoin('sale_invoice_batches','sale_invoice_details.sale_invoice_id', '=', 'sale_invoice_batches.invoice_id')
            ->leftjoin('accounts','sale_invoice_batches.consultant_id', '=', 'accounts.id')
            ->leftjoin('sale_invoices','sale_invoice_details.sale_invoice_id', '=', 'sale_invoices.id')
            ->leftjoin('incentive_settings','incentive_settings.account_id', '=', 'accounts.id')
            ->leftjoin('products','sale_invoice_details.product_id', '=', 'products.id')
            ->leftjoin('product_brands','product_brands.id', '=', 'products.brand_id')
            ->leftjoin('incentive_slabs','incentive_slabs.id', '=', 'incentive_settings.slab_id')
            ->leftjoin('accounts as patient','sale_invoices.patient_id', '=', 'patient.id')
            ->leftjoin('sale_others_transactions', function($join) {
                $join->on('sale_others_transactions.sale_invoice_batch_id', '=', 'sale_invoice_batches.id')
                    ->where('transaction_type', 'DEBIT');
            })
            ->where('sale_invoice_batches.consultant_id', $doctor)
            ->when(!empty($start_date) && !empty($end_date), function($query) use ($start_date, $end_date) {
                $query->whereBetween('sale_invoice_details.created_at', [
                    date('Y-m-d', strtotime($start_date)),
                    date('Y-m-d', strtotime($end_date))
                ]);
            });

        if ($statement_by == 'patient') {
            $data = $data->groupBy('sale_invoices.patient_id');
        } else {
            $data = $data->groupBy('products.id');
        }

        $data = $data->whereNotNull('products.id')
            ->get()
            ->map(function ($saleInvoiceDetails, $productId) {
                $productId = $saleInvoiceDetails->product_id ?? '';
                $id = $saleInvoiceDetails->id ?? '';

                $details = IncentiveSlabDetail::select([
                        'calculation_type',
                        'percentage',
                        'price',
                    ])
                    ->where(['product_id' => $productId, 'slab_id' => $id])->first();

                return [
                    'incentive_slab_details' => $details,
                    'sale_invoice_details'   => $saleInvoiceDetails
                ];
            });

        $incentive_slab = IncentiveSlab::select(
            'incentive_slabs.id',
            'incentive_slabs.calculation_type',
            'incentive_slabs.percentage',
            'incentive_slabs.price'
        )
        ->leftjoin('account_types','account_types.id','incentive_slabs.account_type_id')
        ->where('account_types.type_code', 'DOCTOR')
        ->orderBy('incentive_slabs.is_default', 'DESC')
        ->first();

        return view('referral-vouchers.print.dr-commission', compact('voucher','data','details','incentive_slab','statement_by'));
    }

    public function overAllStatement(Request $request, $id = '')
    {
        if ($request->ajax()) {
            $filter['start_date']   = $start_date = date('Y-m-1', strtotime($request->get('month_year')));
            $filter['end_date']     = $end_date   = date('Y-m-t', strtotime($request->get('month_year')));
            $filter['doctor']       = $doctor     = $request->doctor;
            $filter['statement_by'] = $statement_by = $request->statement_by ?? 'product';

            $data = SaleInvoiceDetail::getReferralVoucherStatement([], $filter);

            $incentive_slab = IncentiveSlab::select(
                'incentive_slabs.id',
                'incentive_slabs.calculation_type',
                'incentive_slabs.percentage',
                'incentive_slabs.price'
            )
            ->leftjoin('account_types','account_types.id','incentive_slabs.account_type_id')
            ->where('account_types.type_code', 'DOCTOR')
            ->orderBy('incentive_slabs.is_default', 'DESC')
            ->first();

            $doctor         = Account::select([
                'accounts.id',
                'accounts.name',
                'accounts.code'
            ])
            ->leftjoin('account_types', 'account_types.id', 'accounts.account_type_id')
            ->where('account_types.type_code', 'DOCTOR')
            ->where('accounts.status', '1')
            ->groupBy('accounts.id')
            ->orderBy('accounts.name')
            ->get();

            return view('referral-vouchers.print.overall-table', compact('data','incentive_slab','statement_by','start_date','end_date','doctor'));
        }
        else
        {
            $month                  = $request->get('month') ?? date('Y-m');
            $filter['start_date']   = $start_date = date('Y-m-1', strtotime($month));
            $filter['end_date']     = $end_date   = date('Y-m-t', strtotime($month));
            $filter['statement_by'] = $statement_by = $request->statement_by ?? 'product';

            if (empty($id)) {
                $incentive_slab = [];
                $data           = [];
                $doctor         = Account::select([
                        'accounts.id',
                        'accounts.name',
                        'accounts.code'
                    ])
                    ->leftjoin('account_types', 'account_types.id', 'accounts.account_type_id')
                    ->where('account_types.type_code', 'DOCTOR')
                    ->where('accounts.status', '1')
                    ->groupBy('accounts.id')
                    ->orderBy('accounts.name')
                    ->get();
            } else {
                $doctor = Account::find($id);
                $filter['doctor'] = $id;
                $data = SaleInvoiceDetail::getReferralVoucherStatement([], $filter);

                $incentive_slab = IncentiveSlab::select(
                    'incentive_slabs.id',
                    'incentive_slabs.calculation_type',
                    'incentive_slabs.percentage',
                    'incentive_slabs.price'
                )
                ->leftjoin('account_types','account_types.id','incentive_slabs.account_type_id')
                ->where('account_types.type_code', 'DOCTOR')
                ->orderBy('incentive_slabs.is_default', 'DESC')
                ->first();
            }

            return view('referral-vouchers.print.overall-commission-statement',compact(
                'doctor',
                'month',
                'start_date',
                'end_date',
                'id',
                'data',
                'incentive_slab',
                'statement_by'
            ));
        }
    }

    public function exportOverAllStatement(Request $request)
    {
        $month   = date('M-Y', strtotime($request->get('month_year')));
        $dr_id   = $request->doctor ?? '';
        $dr      = Account::find($dr_id);
        $dr_name = '';

        if ($dr) {
            $dr_name = !empty($dr->name) ? $dr->name .'-' : '';
        }

        return Excel::download(new ReferralVoucherOverAllStatement(request()), 'Dr-'.ucfirst($dr_name).'Referral-List-'.$month.'.xlsx');
    }

    public function drReferralList(Request $request)
    {
        if ($request->ajax()) {
            $month = $request->get('month_year');
            $filter['start_date'] = $start_date = date('Y-m-1', strtotime($request->get('month_year')));
            $filter['end_date']   = $end_date   = date('Y-m-t', strtotime($request->get('month_year')));

            $doctor_id   = $request->get('agent_id') ?? '';
            $where = [];

            if (!empty($agent_id)) {
                $where['accounts.id'] = $agent_id;
            }

            $data = Account::getReferralVoucherDoctors($where, $filter);

            return view('referral-vouchers.dr-list.table', compact('data','start_date','end_date','month'));
        }
        else
        {
            $month = date('Y-m', strtotime('-1 months'));

            return view('referral-vouchers.dr-list.index', compact('month'));
        }
    }

    public function searchDoctors(Request $request)
    {
        $searchTerm = $request->search_text;
        $results    = Account::select([
            'accounts.id',
            'accounts.code',
            \DB::raw("
                CONCAT(
                    COALESCE(account_titles.name, ''),
                    CASE WHEN account_titles.name IS NOT NULL AND accounts.name IS NOT NULL THEN ' ' ELSE '' END,
                    COALESCE(accounts.name, '')
                ) AS name"),
            \DB::raw("account_contacts.phone_no as phone_no"),
            \DB::raw("genders.name as gender"),
            \DB::raw("account_contacts.date_of_birth")
        ])
            ->leftJoin('account_contacts', 'account_contacts.account_id', '=', 'accounts.id')
            ->leftJoin('account_titles', 'account_titles.id', '=', 'accounts.account_title_id')
            ->leftJoin('genders', 'genders.id', '=', 'account_contacts.account_gender_id')
            ->leftjoin('account_types', 'account_types.id', 'accounts.account_type_id')
            ->where('account_types.type_code', 'DOCTOR')
            ->where(function ($query) use ($searchTerm) {
                $query->where('accounts.name', 'LIKE', $searchTerm . '%')
                    ->orWhere('account_contacts.phone_no', 'LIKE', $searchTerm . '%')
                    ->orWhere('accounts.code', 'LIKE', $searchTerm . '%');
            })
            ->get();

        return response()->json(['result' => $results, 'status' => true]);
    }

    public function exportLabStatement(Request $request)
    {
        $month   = date('M-Y', strtotime($request->get('month_year')));
        $lab_id   = $request->lab ?? '';
        $lab_partner      = Account::find($lab_id);
        $lab =AccountContact::select('organization as name')->where('account_id',$lab_partner->id)->first();
        $lab_name = '';

        if ($lab) {
            $lab_name = !empty($lab->name) ? $lab->name .'-' : '';
        }

        return Excel::download(new ReferralVoucherOverAllStatement(request()), 'lab-'.ucfirst($lab_name).'Referral-List-'.$month.'.xlsx');
    }

            public function labReferralList(Request $request)
                    {
                        if ($request->ajax()) {
                            $month = $request->get('month_year');
                            $filter['start_date'] = $start_date = date('Y-m-1', strtotime($request->get('month_year')));
                            $filter['end_date']   = $end_date   = date('Y-m-t', strtotime($request->get('month_year')));

                             $lab_id   = $request->get('lab_id') ?? '';
                             $where = [];

                            if (!empty($lab_id)) {
                                $where['account_contacts.id'] = $lab_id;
                            }

                            $lab = Account::getReferralVoucherLabs($where, $filter);

                            return view('referral-vouchers.lab-list.table', compact('lab','start_date','end_date','month'));
                        }
                        else
                        {
                            $month = date('Y-m', strtotime('-1 months'));

                            return view('referral-vouchers.lab-list.index', compact('month'));
                        }
                    }

            public function searchLab(Request $request)
            {
                $searchTerm = $request->search_text;
                $results    = AccountContact::select([
                    'account_contacts.id',
                    'account_contacts.organization as name',
                ])
                ->where(function ($query) use ($searchTerm) {
                        $query->where('account_contacts.organization', 'LIKE', $searchTerm . '%');
                    })
                    ->get();

                return response()->json(['result' => $results, 'status' => true]);
            }
  
        public function labOverAllStatement(Request $request, $id = '')
            {
                if ($request->ajax()) {
                   
                    $filter['start_date']   = $start_date = date('Y-m-1', strtotime($request->get('month_year')));
                    $filter['end_date']     = $end_date   = date('Y-m-t', strtotime($request->get('month_year')));
                    $filter['lab']          = $lab        = $id; 
                    $filter['statement_by'] = $statement_by = $request->statement_by ?? 'product';

                    $data = SaleInvoiceDetail::getReferralLabVoucherStatement([], $filter);

                    $incentive_slab = IncentiveSlab::select(
                        'incentive_slabs.id',
                        'incentive_slabs.calculation_type',
                        'incentive_slabs.percentage',
                        'incentive_slabs.price'
                    )
                    ->leftjoin('account_types', 'account_types.id', 'incentive_slabs.account_type_id')
                    ->where('account_types.type_code', 'REFERRAL_PARTNER') // Ensuring we only get labs
                    ->orderBy('incentive_slabs.is_default', 'DESC')
                    ->first();

                    // Join account_contacts to filter labs
                    $labs = Account::select([
                        'accounts.id',
                        'account_contacts.organization'
                    ])
                    ->leftjoin('account_types', 'account_types.id', 'accounts.account_type_id')
                    ->leftjoin('account_contacts', 'account_contacts.account_id', 'accounts.id') // Join with account_contacts
                    ->where('account_types.type_code', 'REFERRAL_PARTNER') // Filtering for labs
                    ->where('accounts.status', '1')
                    ->groupBy('accounts.id')
                    // ->orderBy('account_contacts.organization')
                    ->get();

                    return view('referral-vouchers.print.lab-table', compact('data', 'incentive_slab', 'statement_by', 'start_date', 'end_date', 'labs'));
                } else {

                    $month                  = $request->get('month') ?? date('Y-m');
                    $filter['start_date']   = $start_date = date('Y-m-1', strtotime($month));
                    $filter['end_date']     = $end_date   = date('Y-m-t', strtotime($month));
                    $filter['statement_by'] = $statement_by = $request->statement_by ?? 'product';

                    if (empty($id)) {

                        $incentive_slab = [];
                        $data           = [];
                        $labs = Account::select([
                            'accounts.id',
                             'account_contacts.organization'
                        ])
                        ->leftjoin('account_types', 'account_types.id', 'accounts.account_type_id')
                        ->leftjoin('account_contacts', 'account_contacts.account_id', 'accounts.id') // Join with account_contacts
                        ->where('account_types.type_code', 'REFERRAL_PARTNER') // Filtering for labs
                        ->where('accounts.status', '1')
                        ->groupBy('accounts.id')
                        ->orderBy('account_contacts.organization')
                        ->get();
                    } else {
                      
                        $lab = Account::find($id); // Find specific lab by ID
                        $labs = AccountContact::select('organization')->where('account_id',$lab->id)->first(); 
                        $filter['lab'] = $id; // Update filter for the specific lab
                        $data = SaleInvoiceDetail::getReferralLabVoucherStatement([], $filter);

                        $incentive_slab = IncentiveSlab::select(
                            'incentive_slabs.id',
                            'incentive_slabs.calculation_type',
                            'incentive_slabs.percentage',
                            'incentive_slabs.price'
                        )
                        ->leftjoin('account_types', 'account_types.id', 'incentive_slabs.account_type_id')
                        ->where('account_types.type_code', 'REFERRAL_PARTNER') // Ensuring we only get labs
                        ->orderBy('incentive_slabs.is_default', 'DESC')
                        ->first();
                    }

                    return view('referral-vouchers.print.lab-commission-statement', compact(
                        'labs', // Passing the lab details
                        'month',
                        'start_date',
                        'end_date',
                        'id',
                        'data',
                        'incentive_slab',
                        'statement_by'
                    ));
                }
            }


    public function agentOverAllStatement(Request $request, $id = '')
    {
        if ($request->ajax()) {
            $filter['start_date']   = $start_date = date('Y-m-1', strtotime($request->get('month_year')));
            $filter['end_date']     = $end_date   = date('Y-m-t', strtotime($request->get('month_year')));
            $filter['agent']       = $agent     = $request->agent;
            $filter['statement_by'] = $statement_by = $request->statement_by ?? 'product';

            $data = SaleInvoiceDetail::getReferralAgentVoucherStatement([], $filter);

            $incentive_slab = IncentiveSlab::select(
                'incentive_slabs.id',
                'incentive_slabs.calculation_type',
                'incentive_slabs.percentage',
                'incentive_slabs.price'
            )
            ->leftjoin('account_types','account_types.id','incentive_slabs.account_type_id')
            ->where('account_types.type_code', 'agent')
            ->orderBy('incentive_slabs.is_default', 'DESC')
            ->first();

            $agent         = Account::select([
                'accounts.id',
                'accounts.name',
                'accounts.code'
            ])
            ->leftjoin('account_types', 'account_types.id', 'accounts.account_type_id')
            ->where('account_types.type_code', 'COLLECTION_AGENT')
            ->where('accounts.status', '1')
            ->groupBy('accounts.id')
            ->orderBy('accounts.name')
            ->get();
            return view('referral-vouchers.print.agent-table', compact('data','incentive_slab','statement_by','start_date','end_date','agent'));
        }
        else
        {
            $month                  = $request->get('month') ?? date('Y-m');
            $filter['start_date']   = $start_date = date('Y-m-1', strtotime($month));
            $filter['end_date']     = $end_date   = date('Y-m-t', strtotime($month));
            $filter['statement_by'] = $statement_by = $request->statement_by ?? 'product';

            if (empty($id)) {
                $incentive_slab = [];
                $data           = [];
                $agent         = Account::select([
                        'accounts.id',
                        'accounts.name',
                        'accounts.code'
                    ])
                    ->leftjoin('account_types', 'account_types.id', 'accounts.account_type_id')
                    ->where('account_types.type_code', 'COLLECTION_AGENT')
                    ->where('accounts.status', '1')
                    ->groupBy('accounts.id')
                    ->orderBy('accounts.name')
                    ->get();
            } else {
             
                $agent = Account::find($id);
                $filter['agent'] = $id;
                $data = SaleInvoiceDetail::getReferralAgentVoucherStatement([], $filter);

                $incentive_slab = IncentiveSlab::select(
                    'incentive_slabs.id',
                    'incentive_slabs.calculation_type',
                    'incentive_slabs.percentage',
                    'incentive_slabs.price'
                )
                ->leftjoin('account_types','account_types.id','incentive_slabs.account_type_id')
                ->where('account_types.type_code', 'agent')
                ->orderBy('incentive_slabs.is_default', 'DESC')
                ->first();
            }
           
            return view('referral-vouchers.print.agent-commission-statement',compact(
                'agent',
                'month',
                'start_date',
                'end_date',
                'id',
                'data',
                'incentive_slab',
                'statement_by'
            ));
        }
    }

    public function exportAgentStatement(Request $request)
    {
        $month   = date('M-Y', strtotime($request->get('month_year')));
        $agent_id   = $request->agent ?? '';
        $agent      = Account::find($agent_id);
        $agent_name = '';

        if ($agent) {
            $agent_name = !empty($agent->name) ? $agent->name .'-' : '';
        }

        return Excel::download(new ReferralVoucherOverAllStatement(request()), 'Agent-'.ucfirst($agent_name).'Referral-List-'.$month.'.xlsx');
    }

    public function agentReferralList(Request $request)
    {
        if ($request->ajax()) {
            $month = $request->get('month_year');
            $filter['start_date'] = $start_date = date('Y-m-1', strtotime($request->get('month_year')));
            $filter['end_date']   = $end_date   = date('Y-m-t', strtotime($request->get('month_year')));

            $agent_id   = $request->get('agent_id') ?? '';
            $where = [];

            if (!empty($agent_id)) {
                $where['accounts.id'] = $agent_id;
            }

            $data = Account::getReferralVoucherAgents($where, $filter);

            return view('referral-vouchers.agent-list.table', compact('data','start_date','end_date','month'));
        }
        else
        {
            $month = date('Y-m', strtotime('-1 months'));

            return view('referral-vouchers.agent-list.index', compact('month'));
        }
    }

            public function searchAgents(Request $request)
            {
                $searchTerm = $request->search_text;
                $results    = Account::select([
                    'accounts.id',
                    'accounts.code',
                    \DB::raw("
                        CONCAT(
                            COALESCE(account_titles.name, ''),
                            CASE WHEN account_titles.name IS NOT NULL AND accounts.name IS NOT NULL THEN ' ' ELSE '' END,
                            COALESCE(accounts.name, '')
                        ) AS name"),
                    \DB::raw("account_contacts.phone_no as phone_no"),
                    \DB::raw("genders.name as gender"),
                    \DB::raw("account_contacts.date_of_birth")
                ])
                    ->leftJoin('account_contacts', 'account_contacts.account_id', '=', 'accounts.id')
                    ->leftJoin('account_titles', 'account_titles.id', '=', 'accounts.account_title_id')
                    ->leftJoin('genders', 'genders.id', '=', 'account_contacts.account_gender_id')
                    ->leftjoin('account_types', 'account_types.id', 'accounts.account_type_id')
                    ->where('account_types.type_code', 'COLLECTION_AGENT')
                    ->where(function ($query) use ($searchTerm) {
                        $query->where('accounts.name', 'LIKE', $searchTerm . '%')
                            ->orWhere('account_contacts.phone_no', 'LIKE', $searchTerm . '%')
                            ->orWhere('accounts.code', 'LIKE', $searchTerm . '%');
                    })
                    ->get();

                return response()->json(['result' => $results, 'status' => true]);
            }

}
