<?php

namespace App\Http\Controllers;

use App\Models\MasterCategory;
use Illuminate\Http\Request;
use App\Models\CompanyAddress;
use App\Models\Account;
use App\Models\AccountAddress;
use App\Models\AccountContact;
use App\Models\Product;
use App\Models\PaymentTerm;
use App\Models\SaleInvoice;
use App\Models\SaleInvoiceDetail;
use App\Models\ProductPrice;
use App\Models\ProductTestPackage;
use App\Models\SaleInvoiceSubDetails;
use App\Models\VoucherMaster;
use App\Models\VoucherType;
use App\Models\MenuModelPermission;
use App\Models\AccountingGroup;
use App\Models\AccountLicense;
use App\Models\AccountSettlementType;
use App\Models\AccountTitle;
use App\Models\AccountTransaction;
use App\Models\Company;
use App\Models\TaskStatus;
use App\Models\AcReceipt;
use App\Models\AcReceiptDetail;
use App\Models\Country;
use App\Models\FinancialYear;
use App\Models\LicenseType;
use App\Models\MasterType;
use App\Models\PaymentMode;
use App\Models\SaleInvoiceBatch;
use App\Models\TaxRegisterCategory;
use App\Models\VoucherCollection;
use App\Models\VoucherCollectionDetail;
use Barryvdh\DomPDF\Facade\Pdf;
use Carbon\Carbon;
use Illuminate\Support\Facades\Auth;
use App\Traits\AcReceiptAndTransactionAllTrait;
use App\Traits\BillingConceptTrait;
use App\Traits\TransactionSummeryTrait;
use Maatwebsite\Excel\Facades\Excel;
use App\Exports\SaleStatementExport;
use App\Exports\ReportAndInsightsExport;
use App\Models\Stock;
use App\Models\ProductBrand;
use App\Models\ProductGroup;
use App\Models\ProductCategory;
use App\Models\BusinessDivision;

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

        \Session::forget('sales_products_cart');

        $data = SaleInvoice::with('getSaleInvoiceDelivery')
            ->where('voucher_type_code', 'SALES')
            ->with('getSaleInvoiceDetail')
            ->with('getRefferalLab')
            ->with('getCustomer')
            ->with('getOperator')
            ->with('getPaymentMode')
            ->with('getPatient')
            ->with('getDoctor')
            ->when(!in_array('Admin',\Auth()->user()->roles->pluck('name')->all()), function($query) {
                $query->where('sale_invoices.operator_id', \Auth()->id())
                    ->where('sale_invoices.company_id', \Auth()->user()->company_id);
            })
            ->when(in_array('Admin',\Auth()->user()->roles->pluck('name')->all()), function($query) {
                $query->where('sale_invoices.company_id', \Auth()->user()->company_id);
            });

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

            $data = $data
                ->when(!empty($search_query) && !empty($search_type), function ($query) use ($search_type, $search_query) {
                    if ($search_type == 'invoice_no') {
                        $query->where('main_invoice_no', 'LIKE', '%' . $search_query . '%');
                    } else if ($search_type == 'pat_name') {
                        $query->whereHas('getCustomer', 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('getCustomer.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('getCustomer', function ($patient) use ($search_query) {
                                $patient->where('accounts.unique_register_no', 'LIKE', '%' . $search_query . '%');
                            });
                        });
                    } else if ($search_type == 'ref_name') {
                        $query->where(function ($q) use ($search_query) {
                            $q->whereHas('getRefferalLab', function ($patient) use ($search_query) {
                                $patient->where('accounts.name', 'LIKE', '%' . $search_query . '%');
                            });
                        });
                    }
                })
                ->latest()
                ->paginate($sort_by);

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

            return view('sale.index', compact('data'));
        }
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        // \Session::forget('sales_products_cart');
        $company_id      = Auth::user()->company_id ?? '';
        $company_address = \Session::get('company_data')['companies_addresses'];
        $countryCode     = $country_code->country_code ?? 'us';
        $data            = \Session::get('sales_products_cart') ?? [];
        $customer        =  '';
        $doctor          =  '';

        if (!empty($data) && isset($data['customer_id']) && $data['customer_id'] != '') {
            $customer = Account::getAccount([
                'accounts.id' => $data['customer_id']
            ], ['CUSTOMER', 'PATIENT', 'DOCTOR']);
            if (isset($customer)) {
                $customer->full_name = $customer->name .
                    ($customer->code   != '' ? ', ' . $customer->code : '') .
                    ($customer->gender != '' ? ', ' . $customer->gender : '') .
                    ($customer->age    != '' ? ', ' . $customer->age . ' yrs' : '') .
                    ($customer->phone_no != '' ? ', ' . $customer->phone_no : '');
            }
        }

        if (!empty($data) && isset($data['doctor_id']) && $data['doctor_id'] != '') {
            $doctor = Account::getAccount([
                'accounts.id' => $data['doctor_id']
            ], ['PATIENT', 'DOCTOR']);
            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 : '');
            }
        }

        $voucher_master = VoucherMaster::select('tax_calculation')->where('voucher_code', 'SALES')->where(['status' => 1])->first();
        return view('sale.create', compact(
            'customer',
            'countryCode',
            'company_id',
            'company_address',
            'data',
            'voucher_master',
            'doctor'
        ));
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        $data = \Session::get('sales_products_cart') ?? [];
        if (empty($data)) {
            return redirect()->back()->with('error', 'Empty cart!');
        }

        $company_id     = Auth::user()->company_id ?? '';

        $voucher_master = VoucherMaster::leftJoin('voucher_series', 'voucher_masters.id', '=', 'voucher_series.voucher_master_id')
            ->where('voucher_masters.voucher_code', 'SALES')
            ->where('voucher_masters.status', 1)
            ->where('voucher_series.series_name', 'Sales')
            ->select('voucher_masters.*', 'voucher_series.*')
            ->first();

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


        # last voucher count for main voucher type invoice number
        $voucher_type_last_count = SaleInvoice::select('last_voucher_type_count')->where('voucher_type_code', 'SALES')->orderBy('created_at', 'DESC')->first();
        $voucher_type_count      = isset($voucher_type_last_count) && isset($voucher_type_last_count->last_voucher_type_count) ? $voucher_type_last_count->last_voucher_type_count + 1 : ($voucher_type->voucherSeries->start_from + 1 ?? 1);
        $voucher_type_invoice_no = $this->generateCode(
            $voucher_type_count,
            ($voucher_type->voucherSeries->prefix ?? 'INV'),
            ($voucher_type->voucherSeries->postfix ?? ''),
            ($voucher_type->voucherSeries->separator ?? '-'),
            ($voucher_type->voucherSeries->length ?? 5)
        );
        $discountAmount  = 0;
        /******************* Discount Calculation With Percentage ******************/
        if ($request->discount_type == 'PERCENTAGE') {
            $discountAmount = ($request->special_discount / 100) * $data['amount'];
        } else {
            $discountAmount = $request->special_discount;
        }
        /******************* End Discount Calculation With Percentage ******************/

        $payment_types = PaymentTerm::select(['id'])
            ->where('is_default', '1')
            ->first();
        $invoice_date        = $request->invoice_date != '' ? date('Y-m-d H:i:s', strtotime($request->invoice_date . ' ' . date('H:i:s'))) : '';
        $sale_invoice  = [
            'voucher_type_code'       => ($voucher_type->code ?? ''),
            'payment_term_id'         => $request->payment_term_id,
            'billing_amount'          => $data['amount'],
            'invoice_date'            => $invoice_date,
            'patient_id'              => $data['customer_id'],
            'doctor_id'               => $data['doctor_id'],
            'reff_no'                 => $request->reff_no,
            'received_amount'         => $request->amount_recieved,
            'due_amount'              => $data['due_balance_amount'],
            'sale_delivery_status'    => 'PENDING',
            'company_id'              => $company_id,
            'status'                  => 'NEWSALE',
            'remarks_for_technician'  => '',
            'remarks'                 => '',
            'special_case_id'         => $request->special_case,
            'discount_amount'         => $discountAmount,
            // 'adjustment_type'         => $request->discount_type,
            'financial_year_id'       => $this->financialyear(),
            'industry_type_id'        => 2, // pathology from bussiness divisions.
            'operator_id'             => Auth::ID(),
            'payment_mode_id'         => $request->payment_mode_id ?? '',
            'transaction_mode_id'     => $request->transaction_mode_id ?? '',
            'bussiness_source_id'     => $request->bussiness_source ?? '',
            'voucher_type_invoice_no' => $voucher_type_invoice_no,
            'last_voucher_type_count' => $voucher_type_count,
            'ship_to_id'              => $request->ship_to,
            'dispatch_from_id'        => $request->ship_from,
            'bill_to_account_id'      => $request->bill_to_account_id,
            'tpa_corporate_id'        => $request->tpa_corporate_id,
            // 'payment_term_id'         => $payment_types->id,
        ];

        $sale_invoice = SaleInvoice::create($sale_invoice);


        $voucher_count_batch      = SaleInvoiceBatch::select('last_voucher_count')->orderBy('created_at', 'DESC')->first();
        $last_voucher_count_batch = isset($voucher_count_batch) && isset($voucher_count_batch->last_voucher_count) ? $voucher_count_batch->last_voucher_count + 1 : ($voucher_master->voucherSeries->start_from + 1 ?? 1);

        $main_invoice_no = $this->generateCode(
            $last_voucher_count_batch,
            ($voucher_master->voucherSeries->prefix ?? 'BAT'),
            ($voucher_master->voucherSeries->postfix ?? ''),
            ($voucher_master->voucherSeries->separator ?? '-'),
            ($voucher_master->voucherSeries->length ?? 5)
        );

        // $data['invoice_batch_no']   = $main_invoice_no;
        $data['last_voucher_count'] = $last_voucher_count_batch;

        $invoice_batch = [
            'invoice_id'         => $sale_invoice->id,
            'invoice_batch_date' => Carbon::now(),
            'invoice_batch_no'   => $main_invoice_no,
            'last_voucher_count' => $last_voucher_count_batch,
            'account_id'         => $data['customer_id'],
            'created_by'         => Auth::user()->id,
            'consultant_id'      => $data['doctor_id'],
        ];
        //sale invoice batch
        $invoice_batch  = SaleInvoiceBatch::create($invoice_batch);
        $task_status    = TaskStatus::where('is_default', '1')->first();

        /*********************  Spacial Discount Store Calculation ***********************/
        if ($request->special_discount > 0) {
            # last voucher count for main Transaction number
            $transaction_voucher_count      = AccountTransaction::select('last_id')->orderBy('created_at', 'DESC')->first();
            $transaction_voucher_master     = VoucherMaster::select('id')
                ->with('voucherSeries')
                ->where('voucher_code', 'TRANSACTION')
                ->where(['status' => 1])
                ->first();
            $last_voucher_count = isset($transaction_voucher_count) && isset($transaction_voucher_count->last_id) ?
                $transaction_voucher_count->last_id + 1 : ($transaction_voucher_master->voucherSeries->start_from + 1 ?? 1);

            $transactions_no = $this->generateCode(
                $last_voucher_count,
                ($transaction_voucher_master->voucherSeries->prefix ?? 'TRN'),
                ($transaction_voucher_master->voucherSeries->postfix ?? ''),
                ($transaction_voucher_master->voucherSeries->separator ?? '-'),
                ($transaction_voucher_master->voucherSeries->length ?? 5)
            );
            $company_address = \Session::get('company_data')['companies_addresses'];


            $currency_id     = $company_address->currency_id ?? '';
            $this->SaleOthersTransaction(
                $sale_invoice->id,
                $transactions_no,
                'SALES',
                $request->discount_account_id,
                $discountAmount
            );
        }
        $discount['mode_id'] = $request->discount_account_id ?? '';

        $discount['amount'] = $discountAmount ?? '';
        /********************* End Spacial Discount Store Calculation ***********************/

        /*********************  Round Off Store Calculation ***********************/
        if ($request->round_off_amount != 0) {
            # last voucher count for main Transaction number
            $transaction_voucher_count      = AccountTransaction::select('last_id')->orderBy('created_at', 'DESC')->first();
            $transaction_voucher_master     = VoucherMaster::select('id')
                ->with('voucherSeries')
                ->where('voucher_code', 'TRANSACTION')
                ->where(['status' => 1])
                ->first();
            $last_voucher_count = isset($transaction_voucher_count) && isset($transaction_voucher_count->last_id) ?
                $transaction_voucher_count->last_id + 1 : ($transaction_voucher_master->voucherSeries->start_from + 1 ?? 1);

            $transactions_no = $this->generateCode(
                $last_voucher_count,
                ($transaction_voucher_master->voucherSeries->prefix ?? 'TRN'),
                ($transaction_voucher_master->voucherSeries->postfix ?? ''),
                ($transaction_voucher_master->voucherSeries->separator ?? '-'),
                ($transaction_voucher_master->voucherSeries->length ?? 5)
            );
            $company_address = \Session::get('company_data')['companies_addresses'];
            $currency_id     = $company_address->currency_id ?? '';
            $this->SaleOthersTransaction(
                $sale_invoice->id,
                $transactions_no,
                'SALES',
                $request->round_off_account_id,
                $request->round_off_amount
            );
        }
        $roundOff['mode_id'] = $request->round_off_account_id ?? '';
        $roundOff['amount'] = $request->round_off_amount ?? '';
        /********************* End  Round Off Store Calculation ***********************/
        $voucher_master = VoucherMaster::select('tax_calculation')->where('voucher_code', 'SALES')->where(['status' => 1])->first();
        foreach ($data['products'] as $key => $value) {
            $product = Product::where('id', $value['product_id'])
                ->with('getProductPrice')
                ->first();

            $item = [
                'sale_invoice_id'     => $sale_invoice->id,
                'batch_id'            => $invoice_batch->id,
                'product_id'          => $value['product_id'],
                'product_price_id'    => $value['price_id'],
                'price'               => $value['product_price'],
                'basic_amount'        => $value['product_basic'],
                'discount_percentage' => $value['per_discount'],
                'discount_amount'     => $value['flat_discount'],
                'total_amount'        => $value['amount'],
                'item_details'        => $value['product_detail'],
                'main_qty'            => $value['main_qty'],
                'alt_qty'             => $value['loose_qty'],
                'free_qty'            => $value['free_qty'],
                'item_batch'          => $value['batch'] ?? '',
                'tax_slab_id'         => $product->tax_slab_id ?? '',
                'tpa_amount'          => 0,
                'customer_amount'     => $value['amount'] ?? 0,
                'tax_calculation_type' => $voucher_master->tax_calculation ?? ''
            ];

            $sale_invoice_details = SaleInvoiceDetail::create($item);

            /**************Stock Out Function ********************/
            if ($product->manage_stock == 'Yes') {
                stockOut($sale_invoice->id, 'SALES', $value['product_id'], $value['main_qty'], $value['product_price'], $invoice_batch->id, $value['price_id']);
            }

            $product_test_package = ProductTestPackage::where('product_id', $value['product_id'])->get();

            foreach ($product_test_package as $package) {
                $addedTestPackage = SaleInvoiceSubDetails::create([
                    'sale_invoice_detail_id' => $sale_invoice_details->id,
                    'sale_invoice_id'        => $sale_invoice->id,
                    'data_referrence_id'     => $package->test_package_id,
                    'subdetails_remarks'     => '',
                    'referrence_type'        => 'LAB',
                    'status'                 => 'SAMPLE_COLLECTION',
                    'task_status_id'         => $task_status->id ?? '',
                    'review_status_id'       => $task_status->id ?? '',
                    'task_created_by_id'     => Auth()->id(),
                ]);
            }
        }

        /*********************basic Transaction Insert Into Account Transaction Table */
        // dd($discount);
        $this->createTransaction(
            'SALES',
            $sale_invoice->id,
            $invoice_batch->id,
            $voucher_type->code,
            $data['customer_id'],
            $data,
            $roundOff,
            $discount,
            $discountAmount
        );

        if ($request->amount_recieved > 0) {
            # last voucher count for main Transaction number
            $transaction_voucher_count      = AccountTransaction::select('last_id')->orderBy('created_at', 'DESC')->first();
            $transaction_voucher_master     = VoucherMaster::select('id')
                ->with('voucherSeries')
                ->where('voucher_code', 'TRANSACTION')
                ->where(['status' => 1])
                ->first();
            $last_voucher_count = isset($transaction_voucher_count) && isset($transaction_voucher_count->last_id) ?
                $transaction_voucher_count->last_id + 1 : ($transaction_voucher_master->voucherSeries->start_from + 1 ?? 1);

            $transactions_no = $this->generateCode(
                $last_voucher_count,
                ($transaction_voucher_master->voucherSeries->prefix ?? 'TRN'),
                ($transaction_voucher_master->voucherSeries->postfix ?? ''),
                ($transaction_voucher_master->voucherSeries->separator ?? '-'),
                ($transaction_voucher_master->voucherSeries->length ?? 5)
            );

            $this->createAcReceiptAndTransaction(
                $request->payment_mode_id ?? '',
                $data['customer_id'],
                $request->amount_recieved,
                $transaction->id ?? '',
                $sale_invoice->id,
                'SALES',
                $request->payment_mode,
                'received',
            );
        }

        \Session::forget('sales_products_cart');

        return redirect()->route('sale-invoice', $sale_invoice->id)->with('success', "New Bill Created 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)
    {
        \Session::forget('sales_products_cart');
        $company_id      = Auth::user()->company_id ?? '';
        $company_address = CompanyAddress::getCompanyAddress(['companies_addresses.company_id' => $company_id]);
        $countryCode     = $company_address->country_code ?? 'us';
        // $brands          = ProductBrand::select(['id','name','is_default'])->where(['status' => 1])->get();

        $sale_invoice    = SaleInvoice::with('getSaleInvoiceDetails')->find($id);
        //dd($sale_invoice);
        $data            = [];
        $customer        =  '';
        $doctor          = '';

        if ($sale_invoice) {
            $data['customer_id']            = $sale_invoice->patient_id;
            $data['doctor_id']              = $sale_invoice->doctor_id;
            $data['special_discount']       = $sale_invoice->discount_amount;
            $data['amount_recieved']        = $sale_invoice->received_amount;
            $data['due_balance_amount']     = $sale_invoice->due_amount;
            $data['discount_type']          = 'FLAT';
            $data['invoice_date']           = $sale_invoice->invoice_date;
            $data['payment_mode_id']        = $sale_invoice->payment_mode_id;

            $saleVoucher = VoucherType::select(['default_price_type'])->where(['code' => 'SALES'])->first();
            if ($saleVoucher->default_price_type == 'MRP') {
                $productPrice = 'product_prices.mrp as mrp';
            } elseif ($saleVoucher->default_price_type == 'Purchase Price') {
                $productPrice = 'product_prices.purchase_price as mrp';
            } elseif ($saleVoucher->default_price_type == 'WholeSale Price') {
                $productPrice = 'product_prices.wholesale_price as mrp';
            } elseif ($saleVoucher->default_price_type == 'Discounted/Sale Price(DP)') {
                $productPrice = 'product_prices.sale_price as mrp';
            } elseif ($saleVoucher->default_price_type == 'Min. Sale Price') {
                $productPrice = 'product_prices.min_sale_price as mrp';
            } elseif ($saleVoucher->default_price_type == 'Manufacturing Cost') {
                $productPrice = 'product_prices.mfg_cost as mrp';
            }

            $productMrp    = 0;
            $ActualRateAfterTax = 0;
            $ActualRateBeforeTax = 0;
            $totalAmount = 0;

            foreach ($sale_invoice->getSaleInvoiceDetails as $key => $value) {


                $productPriceData = ProductPrice::select('exp_date',$productPrice)->find($value->product_price_id);
                $product_price = $productPriceData->mrp;
                $flat_discount = 0;
                $per_discount = $value->discount_percentage;

                if ($product_price > 0) {
                    if ($flat_discount != '' && $flat_discount != 0) {
                        $per_discount  = ($flat_discount / $product_price) * 100;
                        $amount        = $product_price - $flat_discount;
                        $flat_discount = $flat_discount;
                    } else if ($per_discount != '' && $per_discount != 0) {
                        $flat_discount = ($per_discount / 100) * $product_price;
                        $per_discount  = $per_discount;
                        $amount        = $product_price - $flat_discount;
                    } else {
                        $amount        = $product_price;
                    }
                }

                $product_price = $amount ?? $product_price;


                $totalAmount += $value->total_amount;
                $main_qty = $value->main_qty > 0 ? $value->main_qty : ($value->alt_qty > 0 ? $value->alt_qty : 1);
                $main_qty_amount = $main_qty * $productPriceData->mrp;

                $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;

                /*************** Tax Calculation ***************** **/
                if ($value && $value->tax_calculation_type == 'EXCLUSIVE') {
                    $tax             = ($tax_per / 100) * $product_price; //(12/100)*1000;
                    $productMrp      = ($product_price + $tax) * $main_qty;  // 1000+
                    $basic_amount    = $value->price;  //1000

                    $ActualRateAfterTax = $product_price + $tax;
                    $ActualRateBeforeTax = $product_price * $main_qty;
                    $totalTax = $tax * $main_qty;
                } else {
                    $basic_amount    = $productPriceData->mrp / (($tax_per + 100) / 100);
                    $basePrice       = $product_price / (1 + ($tax_per / 100));
                    $tax             = $product_price - $basePrice;
                    $productMrp      = $product_price * $main_qty;
                    $ActualRateAfterTax = $product_price - $tax;
                    $ActualRateBeforeTax = $ActualRateAfterTax * $main_qty;
                    $totalTax = $tax * $main_qty;
                }


                $data['products'][] = array(
                    'batch'          => $value->item_batch,
                    'price_id'       => $value->product_price_id ?? '',
                    'product_id'     => $value->product_id ?? '',
                    'product'        => $value->getProduct->name ?? '',
                    'hsncode'        => $value->getProduct->hsncode ?? '',
                    'product_price'  => $basic_amount ?? '0',
                    'flat_discount'  => $value->discount_amount ?? '0',
                    'per_discount'   => $value->discount_percentage ?? '0',
                    'product_detail' => $value->item_details ?? '',
                    'amount'         => $productMrp ?? '0',
                    'main_qty'       => $value->main_qty ?? '0',
                    'loose_qty'      => $value->alt_qty ?? '0',
                    'free_qty'       => $value->free_qty ?? '0',
                    'tax_per'        => $tax_per,
                    'tax_flat'       => $totalTax ?? 0,
                    'product_mrp'    => convertDecimelPoint($ActualRateAfterTax) ?? '',
                    'product_basic'  => convertDecimelPoint($ActualRateBeforeTax) ?? '',
                    'expiry_date'    => $productPriceData->exp_date ? date('M-Y', strtotime($productPriceData->exp_date)) : '',
                );
            }

            $data['amount']                 =  $totalAmount;
        }
        // dd($data);
        \Session::put('sales_products_cart', $data);

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


        if ($sale_invoice && isset($sale_invoice->doctor_id) && $sale_invoice->doctor_id != '') {
            $doctor = Account::getAccount([
                'account_types.type_code' => 'DOCTOR',
                'accounts.id' => $sale_invoice->doctor_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 : '');
            }
        }
        // dd($sale_invoice);
        $voucher_master = VoucherMaster::select('tax_calculation')->where('voucher_code', 'SALES')->where(['status' => 1])->first();
        return view('sale.edit.edit', compact(
            'id',
            'customer',
            'countryCode',
            'doctor',
            'sale_invoice',
            'voucher_master'
        ));
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {
       
        $sale_invoice   = SaleInvoice::find($id);

        $data = \Session::get('sales_products_cart') ?? [];
        if (!$sale_invoice || !$data) {
            $data = [];
            \Session::put('sales_products_cart', $data);

            return redirect()->route('Sale.index')->with('error', 'Sale invoice not found!');
        }

        $special_discount = 0;

        if ($data['discount_type'] == 'FLAT') {
            $special_discount = $request->special_discount;
        } else {
            $special_discount = ($request->special_discount / 100) * $data['amount'];
        }

        $data               = \Session::get('sales_products_cart') ?? [];
        $received_amount    = $data['amount_recieved'] + $request->recieved;
        $due_balance_amount = $data['amount'] - $received_amount - $special_discount;

        if (empty($data)) {
            return redirect()->back()->with('error', 'Empty cart!');
        }

        $invoice_date        = $request->invoice_date != '' ? date('Y-m-d H:i:s', strtotime($request->invoice_date . ' ' . date('H:i:s'))) : '';

        $sale_invoice_update  = [
            'payment_term_id'         => $request->payment_term_id,
            'billing_amount'          => $data['amount'],
            'invoice_date'            => $invoice_date,
            'patient_id'              => $data['customer_id'],
            'doctor_id'               => $data['doctor_id'],
            'reff_no'                 => $request->reff_no,
            'received_amount'         => $received_amount,
            'due_amount'              => $due_balance_amount,
            'remarks'                 => $request->sale_remarks,
            'special_case_id'         => $request->special_case,
            'discount_amount'         => $special_discount,
            'adjustment_type'         => $request->discount_type,
            'payment_mode_id'         => $request->payment_mode_id ?? '',
            'bussiness_source_id'     => $request->bussiness_source ?? '',
            'bill_to_account_id'      => $request->bill_to_account_id,
            'tpa_corporate_id'        => $request->tpa_corporate_id,
        ];

        $sale_invoice->update($sale_invoice_update);

        $where = [
            'invoice_id'             => $sale_invoice->id,
        ];

        $invoice_batchData = [
            'sample_barcode'         => $request->sample_barcode,
            'sample_receive_date'    => $request->sample_receive_date,
            'sample_receive_time'    => $request->sample_receive_time,
            'remarks_for_technician' => $request->remarks_for_technician,
            'consultant_id'          => $data['doctor_id'],
            'updated_by'             => Auth::user()->id,
        ];
        //sale invoice batch
        $invoice_batch  = SaleInvoiceBatch::where($where)->first();
        $invoice_batch->update($invoice_batchData);


        /*********************  Spacial Discount Store Calculation ***********************/
        if ($request->special_discount > 0) {
            # last voucher count for main Transaction number
            $transaction_voucher_count      = AccountTransaction::select('last_id')->orderBy('created_at', 'DESC')->first();
            $transaction_voucher_master     = VoucherMaster::select('id')
                ->with('voucherSeries')
                ->where('voucher_code', 'TRANSACTION')
                ->where(['status' => 1])
                ->first();
            $last_voucher_count = isset($transaction_voucher_count) && isset($transaction_voucher_count->last_id) ?
                $transaction_voucher_count->last_id + 1 : ($transaction_voucher_master->voucherSeries->start_from + 1 ?? 1);

            $transactions_no = $this->generateCode(
                $last_voucher_count,
                ($transaction_voucher_master->voucherSeries->prefix ?? 'TRN'),
                ($transaction_voucher_master->voucherSeries->postfix ?? ''),
                ($transaction_voucher_master->voucherSeries->separator ?? '-'),
                ($transaction_voucher_master->voucherSeries->length ?? 5)
            );
            $company_address = \Session::get('company_data')['companies_addresses'];
            $currency_id     = $company_address->currency_id ?? '';
            $this->SaleOthersTransaction(
                $sale_invoice->id,
                $transactions_no,
                'SALES',
                $request->discount_account_id,
                $special_discount
            );
        }
        $discount['mode_id'] = $request->discount_account_id ?? '';
        $discount['amount'] = $special_discount ?? '';
        /********************* End Spacial Discount Store Calculation ***********************/

        /*********************  Round Off Store Calculation ***********************/
        if ($request->round_off_amount > 0) {
            # last voucher count for main Transaction number
            $transaction_voucher_count      = AccountTransaction::select('last_id')->orderBy('created_at', 'DESC')->first();
            $transaction_voucher_master     = VoucherMaster::select('id')
                ->with('voucherSeries')
                ->where('voucher_code', 'TRANSACTION')
                ->where(['status' => 1])
                ->first();
            $last_voucher_count = isset($transaction_voucher_count) && isset($transaction_voucher_count->last_id) ?
                $transaction_voucher_count->last_id + 1 : ($transaction_voucher_master->voucherSeries->start_from + 1 ?? 1);

            $transactions_no = $this->generateCode(
                $last_voucher_count,
                ($transaction_voucher_master->voucherSeries->prefix ?? 'TRN'),
                ($transaction_voucher_master->voucherSeries->postfix ?? ''),
                ($transaction_voucher_master->voucherSeries->separator ?? '-'),
                ($transaction_voucher_master->voucherSeries->length ?? 5)
            );
            $company_address = \Session::get('company_data')['companies_addresses'];
            $currency_id     = $company_address->currency_id ?? '';
            $this->SaleOthersTransaction(
                $sale_invoice->id,
                $transactions_no,
                'SALES',
                $request->round_off_account_id,
                $request->round_off_amount
            );
        }
        $roundOff['mode_id'] = $request->round_off_account_id ?? '';
        $roundOff['amount'] = $request->round_off_amount ?? '';
        /********************* End  Round Off Store Calculation ***********************/


        $task_status  = TaskStatus::where('is_default', '1')->first();
        $sale_invoice_detail_ids = $sale_invoice_sub_detail_ids = $product_ids = $price_ids =  [];

        $voucher_master = VoucherMaster::select('tax_calculation')->where('voucher_code', 'SALES')->where(['status' => 1])->first();

        foreach ($data['products'] as $key => $value) {
            $product = Product::select(['tax_slab_id','manage_stock'])->where('id', $value['product_id'])
                // ->with('getProductPrice')
                ->first();


            $item = [
                'batch_id'            => $invoice_batch->id,
                'price'               => $value['product_price'],
                'basic_amount'        => $value['product_basic'],
                'discount_percentage' => $value['per_discount'],
                'discount_amount'     => $value['flat_discount'],
                'total_amount'        => $value['amount'],
                'item_details'        => $value['product_detail'],
                'main_qty'            => $value['main_qty'],
                'alt_qty'             => $value['loose_qty'],
                'free_qty'            => $value['free_qty'],
                'item_batch'          => $value['batch'],
                'tax_slab_id'         => $product->tax_slab_id ?? '',
                'tpa_amount'          => 0,
                'customer_amount'     => $value['amount'] ?? 0,
                'tax_calculation_type'=> $voucher_master->tax_calculation??'',
            ];

            $sale_invoice_details     = SaleInvoiceDetail::updateOrCreate([
                'sale_invoice_id'     => $sale_invoice->id,
                'product_id'          => $value['product_id'],
                'product_price_id'    => $value['price_id']
            ], $item);

            $sale_invoice_detail_ids[] = $sale_invoice_details->id;
            $product_ids[]             = $value['product_id'];
            $price_ids[]               = $value['price_id'];


            /**************Stock Out Function ********************/
            if ($product->manage_stock == 'Yes') {
                stockOut($sale_invoice->id, 'SALES', $value['product_id'], $value['main_qty'], $value['product_price'], $invoice_batch->id, $value['price_id']);
            }

            $product_test_package = ProductTestPackage::where('product_id', $value['product_id'])->get();

            foreach ($product_test_package as $package) {
                $addedTestPackage = SaleInvoiceSubDetails::updateOrCreate(
                    [
                        'sale_invoice_detail_id' => $sale_invoice_details->id,
                        'sale_invoice_id'        => $sale_invoice->id,
                        'data_referrence_id'     => $package->test_package_id,
                    ],
                    [
                        'subdetails_remarks'     => '',
                        'referrence_type'        => 'LAB',
                        'status'                 => 'SAMPLE_COLLECTION',
                        'task_status_id'         => $task_status->id ?? '',
                        'review_status_id'       => $task_status->id ?? '',
                    ]
                );
                $sale_invoice_sub_detail_ids[] = $addedTestPackage->id;
            }
        }
        SaleInvoiceDetail::whereNotIn('id', $sale_invoice_detail_ids)
            ->where(['sale_invoice_id' => $sale_invoice->id])
            ->delete();
        Stock::whereNotIN('product_id', $product_ids)
            ->whereNotIN('batch_id', $price_ids)
            ->where(['voucher_id' => $sale_invoice->id, 'voucher_type' => 'SALES'])
            ->delete();

        /**************************Basic Transaction Edit *****************************************/
        $voucher_type   = VoucherType::with('voucherSeries')->where('code', 'SALES')->where(['status' => 1])->first();



        /***************************************Basic Transaction Update ***************/
        $this->updateTransaction(
            'SALES',
            $sale_invoice->id,
            $invoice_batch->id,
            $voucher_type->code,
            $data['customer_id'],
            $data,
            $roundOff,
            $discount,
            $special_discount
        );
        /**************************** End Basic Transaction Update *********************/

        if ($request->amount_recieved > 0) {
            $this->createAcReceiptAndTransaction(
                $request->payment_mode_id ?? '',
                $data['customer_id'],
                $request->amount_recieved,
                $transaction->id ?? '',
                $sale_invoice->id,
                'SALES',
                $request->payment_mode,
                'received',
            );
        }

        \Session::forget('sales_products_cart');

        return redirect()->route('sale-invoice', $sale_invoice->id)->with('success', "New Bill Created Successfully");
    }


    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function destroy(Request $request)
    {
        $saleInvoice = SaleInvoice::find($request->id);
        $saleInvoiceId = $saleInvoice->id;
        $saleInvoiceBatchId = $saleInvoice->batch_id ?? '';

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


        AccountTransaction::where([
            'voucher_id'  =>  $saleInvoiceId,
            'module_code' =>  'SALES',
            'batch_id'    =>   $saleInvoiceBatchId,
        ])->delete();


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

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

                AcReceiptDetail::where([
                    'voucher_id'             => $acReceiptData->id,
                    'voucher_type'           => 'SALES'
                ])->delete();

                $acReceiptData->update(['deleted_by' => \Auth::user()->id]);
                $acReceiptDataAll = AcReceipt::where([
                    'voucher_id'     => $saleInvoiceId,
                    'module_code'    => 'SALES'
                ])->get();
                foreach ($acReceiptDataAll as $row) {
                    AcReceiptDetail::where([
                        'voucher_id'   => $row->id,
                        'voucher_type' => 'SALES'
                    ])->delete();

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



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

                // AccountTransaction::where([
                //     'voucher_id'        =>  $acReceiptData->id,
                //     'module_code'      =>  'SALES'
                // ])->delete();
            }
        }
        // $acReceiptData->delete();
        $saleInvoice->delete();
        return response()->json([
            'success' => true,
            'message' => ['Deleted successfully'],
            'data' => [
                'redirect' => '',
            ]
        ]);
    }
    public function customerCreate()
    {
        $country         = Country::select(['id', 'name'])->get();

        $company_id      = Auth::user()->company_id ?? '';
        $company_address = CompanyAddress::select([
            'companies_addresses.country_id',
            'companies_addresses.state_id',
            'companies_addresses.city_id',
            'companies.id',
        ])
            ->leftjoin('companies', 'companies.id', 'companies_addresses.company_id')
            ->where('company_id', $company_id)
            ->orWhere('companies.is_default', '1')
            ->first();

        // $license_types = LicenseType::select('id','name','code')->where('is_mandatory', '1')->where('status',1)->get();

        $license_types = LicenseType::select(
            'license_types.id',
            'license_types.name',
            'license_types.code',
        )
            ->leftjoin('account_type_license_settings', 'account_type_license_settings.license_id', 'license_types.id')
            ->leftjoin('account_types', 'account_type_license_settings.account_type_id', 'account_types.id')
            ->where('account_type_license_settings.is_mandatory', '1')
            // ->where('license_types.status',1)
            ->where('account_types.type_code', 'CUSTOMER')
            ->get();

        $country_id = $company_address->country_id ?? '';
        $state_id   = $company_address->state_id ?? '';
        $city_id    = $company_address->city_id ?? '';

        return view('sale.create-customer', compact(
            'country',
            'country_id',
            'state_id',
            'city_id',
            'license_types'
        ));
    }

    public function storeCustomer(Request $request)
    {
        $validator = \Validator::make($request->all(), [
            'phone_no' => [
                'required',
                function ($attribute, $value, $fail) use ($request) {
                    $existingContact = AccountContact::where('name', $request->name)
                        ->where('phone_no', $value)
                        ->first();
                    if ($existingContact) {
                        $fail('This Name and Phone No combination already exists.');
                    }
                }
            ],
            'name' => [
                'required',
                function ($attribute, $value, $fail) use ($request) {
                    $existingContact = AccountContact::where('phone_no', $request->phone_no)
                        ->where('name', $value)
                        ->first();
                    if ($existingContact) {
                        $fail('This Name and Phone No combination already exists.');
                    }
                }
            ],
        ], [
            'required' => 'The :attribute field is required.',
        ]);

        if ($validator->fails()) {
            if ($request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => $validator->errors()->first(),
                    'data'    => []
                ]);
            }
            return redirect()->back()->with('error', $validator->errors()->first());
        }

        $type                   = MasterType::where('type_code', 'Customer')->first();
        $accounting_group       = AccountingGroup::where('name', 'Sundry Debtors')->first();
        $company_id             = Auth::user()->company_id ?? '';
        $accounting_category    = MasterCategory::select('id', 'category_name')->where('is_default', 1)->first();
        $taxRegisterCategory = TaxRegisterCategory::select('id', 'name')->where('is_default', 1)->first();
        $sattlement_type        = AccountSettlementType::select('id', 'name')->where('is_default', 1)->first();

        $account = Account::create([
            'name'                     => $request->name,
            'account_title_id'         => $request->account_title_id,
            'account_type_id'          => $type->id ?? '',
            'created_by'               => Auth::user()->id ?? '',
            'accounting_group_id'      => $accounting_group->id,
            'company_id'               => $company_id,
            'account_category_id'      => $accounting_category->id,
            'tax_register_category_id' => $taxRegisterCategory->id,
            'settlement_type'          => $sattlement_type->id,

        ]);

        $account_type = MasterType::where('type_name', 'Customer')->first();
        $count  = 1;
        $prefix = "CUS";

        if ($account_type) {
            $count  = $account_type->count != '' ? $account_type->count + 1 : 1;
            $prefix = $account_type->prefix != '' ? $account_type->prefix : "CUS";

            $account_type->update(['count' => $count]);
        }

        $code = $this->generateCode($count, $prefix);

        $account->update(['code' => $code]);

        $address = AccountAddress::create([
            'account_id' => $account->id,
            'country_id' => $request->country_id,
            'state_id'   => $request->state_id,
            'city_id'    => $request->city_id,
            'is_default' => '1'
        ]);

        $data = $request->input('license_number');

        if (!is_null($data) && is_array($data)) {
            foreach ($data as $type => $licenseInfo) {
                if (!empty($licenseInfo['license_number'])) {
                    AccountLicense::create([
                        'account_id'      => $account->id,
                        'license_number'  => $licenseInfo['license_number'],
                        'license_type_id' => $licenseInfo['license_type_id'],
                        'valid_from'      => '',
                        'valid_upto'      => '',
                    ]);
                }
            }
        }

        $contact = AccountContact::create([
            'account_id'  => $account->id,
            'name'        => $request->name ?? '',
            'phone_no'    => $request->phone_no ?? '',
            'whatsapp_no' => $request->whatsapp_no,
            'email'       => $request->email ?? '',
            'country_id'  => $request->country_id,
            'state_id'    => $request->state_id,
            'city_id'     => $request->city_id,
            'address'     => $request->address,
            'postal_code' => $request->postal_code,
        ]);

        $account_title = AccountTitle::select(['name', 'gender_id', 'id'])->find($request->account_title_id);

        $response = [
            'id' => $account->id,
            'name' => ($account_title->name ?? '') . ' ' . $request->name . ' - ' . $account->code . ', ' . $request->phone_no
        ];

        $data = \Session::get('sales_products_cart') ?? [];
        $data['customer_id'] = $account->id;

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

        if ($request->ajax()) {
            return response()->json([
                'success' => true,
                'message' => 'Customer has been created successfully',
                'data'    => $response
            ]);
        }
        return redirect()->route('accounts.index')
            ->with('success', 'Customer has been created successfully.');
    }
    public function customerEdit($id)
    {
        $account_license = AccountLicense::select('id', 'license_type_id', 'account_id', 'license_number')->where('account_id', $id)->get();
        $account_license = $account_license->keyBy('license_type_id');
        $license_types = LicenseType::select('id', 'name', 'code')->where('status', 1)->where('is_mandatory', '1')->get();

        $account         = Account::where('id', $id)->with('first_account_address', 'account_contact')->first();
        $country         = Country::select(['id', 'name'])->get();
        $account_title   = AccountTitle::where('status', '1')->where('name', '<>', 'M/s.')->with('gender')->get();
        $company_id      = Auth::user()->company_id ?? '';
        $company_address = CompanyAddress::select([
            'companies_addresses.country_id',
            'companies_addresses.state_id',
            'companies_addresses.city_id',
            'companies.id',
        ])
            ->leftjoin('companies', 'companies.id', 'companies_addresses.company_id')
            ->where('company_id', $company_id)
            ->orWhere('companies.is_default', '1')
            ->first();

        $country_id = $company_address->country_id ?? '';
        $state_id   = $company_address->state_id ?? '';
        $city_id    = $company_address->city_id ?? '';

        return view('sale.edit-customer', compact(
            'country',
            'account_title',
            'country_id',
            'state_id',
            'city_id',
            'account',
            'account_license',
            'license_types',
        ));
    }

    public function updateCustomer(Request $request, $id)
    {
        $contact = AccountContact::where(['account_id'  => $id])->first();
        $validator = \Validator::make($request->all(), [
            'phone_no'   => 'unique:account_contacts,phone_no,' . $contact->id,
            'name'       => 'required|unique:account_contacts,name,' . $contact->id,
        ], [
            'unique' => 'This Name and Phone No are already taken.',
        ]);

        if ($validator->fails()) {
            if ($request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => $validator->getMessageBag()->first(),
                    'data'    => []
                ]);
            }
            return redirect()->back()->with('error', $validator->getMessageBag()->first());
        }

        $type = MasterType::where('type_code', 'Customer')->first();

        $account = Account::updateOrCreate([
            'name'             => $request->name,
            'account_title_id' => $request->account_title_id,
            'account_type_id'  => $type->id ?? '',
            'created_by'       => Auth::user()->id ?? ''
        ]);

        // $account_type = MasterType::where('type_code','Customer');
        // dd($account_type);
        $count  = 1;
        $prefix = "CUS";

        if ($type) {
            $count  = $type->count != '' ? $type->count + 1 : 1;
            $prefix = $type->prefix != '' ? $type->prefix : "CUS";

            $type->update(['count' => $count]);
        }

        $code = $this->generateCode($count, $prefix);

        $account->update(['code' => $code]);

        $address = AccountAddress::updateOrCreate([
            'account_id' => $account->id,
            'country_id' => $request->country_id,
            'is_default' => '1'
        ]);

        $data = $request->input('license_number');

        if (!is_null($data) && is_array($data)) {
            foreach ($data as $type => $licenseInfo) {
                if (empty($licenseInfo['license_number'])) {
                    // If license_number is empty, delete the record
                    AccountLicense::where([
                        'account_id'      => $account->id,
                        'license_type_id' => $licenseInfo['license_type_id'],
                    ])->delete();
                } else {
                    // If license_number is not empty, update or create the record
                    AccountLicense::updateOrCreate(
                        [
                            'account_id'      => $account->id,
                            'license_type_id' => $licenseInfo['license_type_id'],
                        ],
                        [
                            'license_number'  => $licenseInfo['license_number'],
                            'valid_from'      => '',
                            'valid_upto'      => '',
                        ]
                    );
                }
            }
        }


        $contact = AccountContact::updateOrCreate([
            'account_id'  => $account->id,
        ], [
            'name'          => $request->name ?? '',
            'phone_no'      => $request->phone_no ?? '',
            'whatsapp_no'   => $request->whatsapp_no,
            'email'         => $request->email ?? '',
            'country_id'    => $request->country_id,
            'state_id'      => $request->state_id,
            'city_id'       => $request->city_id,
            'address'       => $request->address,
            'postal_code'   => $request->postal_code,
            'date_of_birth' => $request->date_of_birth != '' ? date('Y-m-d', strtotime($request->date_of_birth)) : '',
        ]);

        $account_title = AccountTitle::select(['name', 'gender_id', 'id'])->find($request->account_title_id);

        $response = [
            'id' => $account->id,
            'name' => ($account_title->name ?? '') . ' ' . $request->name . ' - ' . $account->code . ', ' . $request->phone_no
        ];

        $data = \Session::get('sales_products_cart') ?? [];
        $data['customer_id'] = $account->id;

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

        if ($request->ajax()) {
            return response()->json([
                'success' => true,
                'message' => 'Account has been Updated successfully',
                'data'    => $response
            ]);
        }
        return redirect()->route('accounts.index')
            ->with('success', 'Account has been Updated successfully.');
    }
   public function searchCustomer(Request $request)
    {
        $searchTerm = $request->search_text;
        $results    = Account::select([
                        'accounts.id',
                        'accounts.code',
                        'account_contacts.country_id',
                        'account_contacts.address',
                        'account_contacts.city_id',
                        \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')
                    ->whereIN('account_types.type_code', ['CUSTOMER','PATIENT','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 supplierCreate()
    {
        $country         = Country::select(['id', 'name'])->get();

        $company_id      = Auth::user()->company_id ?? '';
        $company_address = CompanyAddress::select([
            'companies_addresses.country_id',
            'companies_addresses.state_id',
            'companies_addresses.city_id',
            'companies.id',
        ])
            ->leftjoin('companies', 'companies.id', 'companies_addresses.company_id')
            ->where('company_id', $company_id)
            ->orWhere('companies.is_default', '1')
            ->first();

        $license_types = LicenseType::select(
            'license_types.id',
            'license_types.name',
            'license_types.code',
        )
            ->leftjoin('account_type_license_settings', 'account_type_license_settings.license_id', 'license_types.id')
            ->leftjoin('account_types', 'account_type_license_settings.account_type_id', 'account_types.id')
            ->where('account_type_license_settings.is_mandatory', '1')
            // ->where('license_types.status',1)
            ->where('account_types.type_code', 'VENDOR')
            ->get();

        $country_id = $company_address->country_id ?? '';
        $state_id   = $company_address->state_id ?? '';
        $city_id    = $company_address->city_id ?? '';

        return view('accounts.supplier.create', compact(
            'country',
            'country_id',
            'state_id',
            'city_id',
            'license_types'
        ));
    }

    public function storeSupplier(Request $request)
    {
        $phone_no = (string) $request->phone_no;

        $validator = \Validator::make($request->all(), [
            'phone_no' => [
                'required',
                function ($attribute, $value, $fail) use ($request, $phone_no) {
                    $existingContact = AccountContact::where('name', $request->name)
                        ->where('phone_no', $phone_no)
                        ->first();
                    if ($existingContact) {
                        $fail('This Name and Phone No combination already exists.');
                    }
                }
            ],
            'name' => [
                'required',
                function ($attribute, $value, $fail) use ($request, $phone_no) {
                    $existingContact = AccountContact::where('phone_no', $phone_no)
                        ->where('name', $value)
                        ->first();
                    if ($existingContact) {
                        $fail('This Name and Phone No combination already exists.');
                    }
                }
            ],
        ], [
            'required' => 'The :attribute field is required.',
        ]);

        if ($validator->fails()) {
            if ($request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => $validator->errors()->first(),
                    'data'    => []
                ]);
            }
            return redirect()->back()->with('error', $validator->errors()->first());
        }

        $account_type        = MasterType::where('type_code', 'VENDOR')->first();
        $accounting_group    = AccountingGroup::where('id', $account_type->accounting_group_id)->first();
        $company_id          = Auth::user()->company_id ?? '';
        $accounting_category = MasterCategory::select('id', 'category_name')->where('is_default', 1)->first();
        $taxRegisterCategory = TaxRegisterCategory::select('id', 'name')->where('is_default', 1)->first();
        $sattlement_type     = AccountSettlementType::select('id', 'name')->where('is_default', 1)->first();

        $account = Account::create([
            'name'                     => $request->name,
            'account_title_id'         => $request->account_title_id,
            'account_type_id'          => $account_type->id ?? '',
            'created_by'               => Auth::user()->id ?? '',
            'accounting_group_id'      => $accounting_group->id,
            'company_id'               => $company_id,
            'account_category_id'      => $accounting_category->id,
            'tax_register_category_id' => $taxRegisterCategory->id,
            'settlement_type'          => $sattlement_type->id,
        ]);

        $count  = 1;
        $prefix = "CUS";

        if ($account_type) {
            $count  = $account_type->count != '' ? $account_type->count + 1 : 1;
            $prefix = $account_type->prefix != '' ? $account_type->prefix : "CUS";

            $account_type->update(['count' => $count]);
        }

        $code = $this->generateCode($count, $prefix);

        $account->update(['code' => $code]);

        $address = AccountAddress::create([
            'account_id' => $account->id,
            'country_id' => $request->country_id,
            'state_id'   => $request->state_id,
            'city_id'    => $request->city_id,
            'is_default' => '1'
        ]);

        $data = $request->input('license_number');

        if (!is_null($data) && is_array($data)) {
            foreach ($data as $type => $licenseInfo) {
                if (!empty($licenseInfo['license_number'])) {
                    AccountLicense::create([
                        'account_id'      => $account->id,
                        'license_number'  => $licenseInfo['license_number'],
                        'license_type_id' => $licenseInfo['license_type_id'],
                        'valid_from'      => '',
                        'valid_upto'      => '',
                    ]);
                }
            }
        }

        $contact = AccountContact::create([
            'account_id'  => $account->id,
            'name'        => $request->name ?? '',
            'phone_no'    => $request->phone_no ?? '',
            'whatsapp_no' => $request->whatsapp_no,
            'email'       => $request->email ?? '',
            'country_id'  => $request->country_id,
            'state_id'    => $request->state_id,
            'city_id'     => $request->city_id,
            'address'     => $request->address,
            'postal_code' => $request->postal_code,
        ]);

        $account_title = AccountTitle::select(['name', 'gender_id', 'id'])->find($request->account_title_id);

        $response = [
            'id' => $account->id,
            'name' => ($account_title->name ?? '') . ' ' . $request->name . ' - ' . $account->code . ', ' . $request->phone_no
        ];

        $data = \Session::get('sales_products_cart') ?? [];
        $data['customer_id'] = $account->id;

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

        if ($request->ajax()) {
            return response()->json([
                'success' => true,
                'message' => 'Customer has been created successfully',
                'data'    => $response
            ]);
        }
        return redirect()->route('accounts.index')
            ->with('success', 'Customer has been created successfully.');
    }

    public function searchSupplier(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')
            ->whereIN('account_types.type_code', ['VENDOR'])
            ->where(function ($query) use ($searchTerm) {
                $query->where('accounts.name', 'LIKE', $searchTerm . '%')
                    ->orWhere('account_contacts.phone_no', 'LIKE', $searchTerm . '%')
                    ->orWhere('accounts.code', 'LIKE', $searchTerm . '%');
            })
            ->limit(20)
            ->get();

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

    public function supplierEdit($id)
    {
        $account_license = AccountLicense::select('id', 'license_type_id', 'account_id', 'license_number')->where('account_id', $id)->get();
        $account_license = $account_license->keyBy('license_type_id');
        $license_types = LicenseType::select('id', 'name', 'code')->where('status', 1)->where('is_mandatory', '1')->get();

        $account         = Account::where('id', $id)->with('first_account_address', 'account_contact')->first();
        $country         = Country::select(['id', 'name'])->get();
        $account_title   = AccountTitle::where('status', '1')->where('name', '<>', 'M/s.')->with('gender')->get();
        $company_id      = Auth::user()->company_id ?? '';
        $company_address = CompanyAddress::select([
            'companies_addresses.country_id',
            'companies_addresses.state_id',
            'companies_addresses.city_id',
            'companies.id',
        ])
            ->leftjoin('companies', 'companies.id', 'companies_addresses.company_id')
            ->where('company_id', $company_id)
            ->orWhere('companies.is_default', '1')
            ->first();

        $country_id = $company_address->country_id ?? '';
        $state_id   = $company_address->state_id ?? '';
        $city_id    = $company_address->city_id ?? '';

        return view('accounts.supplier.edit', compact(
            'country',
            'account_title',
            'country_id',
            'state_id',
            'city_id',
            'account',
            'account_license',
            'license_types',
        ));
    }

    public function updateSupplier(Request $request, $id)
    {
        $contact = AccountContact::where(['account_id'  => $id])->first();
        $validator = \Validator::make($request->all(), [
            'phone_no'   => 'unique:account_contacts,phone_no,' . $contact->id,
            'name'       => 'required|unique:account_contacts,name,' . $contact->id,
        ], [
            'unique' => 'This Name and Phone No are already taken.',
        ]);

        if ($validator->fails()) {
            if ($request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => $validator->getMessageBag()->first(),
                    'data'    => []
                ]);
            }
            return redirect()->back()->with('error', $validator->getMessageBag()->first());
        }

        $type = MasterType::where('type_code', 'VENDOR')->first();

        $account = Account::updateOrCreate([
            'name'             => $request->name,
            'account_title_id' => $request->account_title_id,
            'account_type_id'  => $type->id ?? '',
            'created_by'       => Auth::user()->id ?? ''
        ]);

        // $account_type = MasterType::where('type_code','Customer');
        // dd($account_type);
        $count  = 1;
        $prefix = "CUS";

        if ($type) {
            $count  = $type->count != '' ? $type->count + 1 : 1;
            $prefix = $type->prefix != '' ? $type->prefix : "CUS";

            $type->update(['count' => $count]);
        }

        $code = $this->generateCode($count, $prefix);

        $account->update(['code' => $code]);

        $address = AccountAddress::updateOrCreate([
            'account_id' => $account->id,
            'country_id' => $request->country_id,
            'is_default' => '1'
        ]);

        $data = $request->input('license_number');

        if (!is_null($data) && is_array($data)) {
            foreach ($data as $type => $licenseInfo) {
                if (empty($licenseInfo['license_number'])) {
                    // If license_number is empty, delete the record
                    AccountLicense::where([
                        'account_id'      => $account->id,
                        'license_type_id' => $licenseInfo['license_type_id'],
                    ])->delete();
                } else {
                    // If license_number is not empty, update or create the record
                    AccountLicense::updateOrCreate(
                        [
                            'account_id'      => $account->id,
                            'license_type_id' => $licenseInfo['license_type_id'],
                        ],
                        [
                            'license_number'  => $licenseInfo['license_number'],
                            'valid_from'      => '',
                            'valid_upto'      => '',
                        ]
                    );
                }
            }
        }


        $contact = AccountContact::updateOrCreate([
            'account_id'  => $account->id,
        ], [
            'name'          => $request->name ?? '',
            'phone_no'      => $request->phone_no ?? '',
            'whatsapp_no'   => $request->whatsapp_no,
            'email'         => $request->email ?? '',
            'country_id'    => $request->country_id,
            'state_id'      => $request->state_id,
            'city_id'       => $request->city_id,
            'address'       => $request->address,
            'postal_code'   => $request->postal_code,
            'date_of_birth' => $request->date_of_birth != '' ? date('Y-m-d', strtotime($request->date_of_birth)) : '',
        ]);

        $account_title = AccountTitle::select(['name', 'gender_id', 'id'])->find($request->account_title_id);

        $response = [
            'id' => $account->id,
            'name' => ($account_title->name ?? '') . ' ' . $request->name . ' - ' . $account->code . ', ' . $request->phone_no
        ];

        $data = \Session::get('sales_products_cart') ?? [];
        $data['customer_id'] = $account->id;

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

        if ($request->ajax()) {
            return response()->json([
                'success' => true,
                'message' => 'Account has been Updated successfully',
                'data'    => $response
            ]);
        }
        return redirect()->route('accounts.index')
            ->with('success', 'Account has been Updated successfully.');
    }

    public function searchProducts(Request $request)
    {
        $searchTerm = $request->search;
        $brand_id   = $request->brand_id ?? '';


        $results = Product::select(
            'products.id',
            'products.name',
            'products.shortcode',
            'products.hsncode',
            'products.manage_stock',
            \DB::raw("SUM(COALESCE(stocks.pack_qty_in, 0)) AS stock_in_count"),
            \DB::raw("SUM(COALESCE(stocks.pack_qty_out, 0)) AS stock_out_count"),
            \DB::raw("SUM(COALESCE(stocks.free_qty, 0)) AS free_qty_count"),
            'primary_units.code'
        )
        ->leftJoin('business_divisions', 'business_divisions.id', '=', 'products.business_division_id')
        ->leftJoin('primary_units','primary_units.id','products.primary_unit_id')
        ->leftJoin('stocks', 'stocks.product_id', '=', 'products.id')  // Join with Stock table
        ->where(function ($query) use ($searchTerm) {
            $query->where('products.name', 'LIKE', "%" . $searchTerm . "%")
                ->orWhere('products.product_code', 'LIKE', "%" . $searchTerm . "%")
                ->orWhere('products.shortcode', 'LIKE', "%" . $searchTerm . "%");
        })
        ->when(!empty($brand_id), function ($query) use ($brand_id) {
            $query->where('products.brand_id', $brand_id);
        })
        ->where('products.status', 1)
        ->where('business_divisions.code', 'PHARMACY')
        ->groupBy('products.id', 'products.name', 'products.shortcode', 'products.hsncode', 'products.manage_stock')  // Group by the product fields
        ->limit(15)
        ->get();
    
    // Now you can calculate the current stock for each product in the results
    foreach ($results as $result) {
        $current_stock = ($result->stock_in_count - $result->stock_out_count > 0) 
            ? $result->stock_in_count - $result->stock_out_count 
            : 0;
        
        $result->current_stock = $current_stock + $result->free_qty_count;
    }
    
    // The $results will now contain the current stock for each product.
    

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

    public function productAddToCart(Request $request)
    {
        $data = \Session::get('sales_products_cart') ?? [];

        if (!isset($data['customer_id']) || $data['customer_id'] == '') {
            $data['customer_id'] = $request->customer_id;
        }
        if (!isset($data['doctor_id']) || $data['doctor_id'] == '') {
            $data['doctor_id'] = $request->doctor_id;
        }

        if (!isset($data['amount'])) {
            $data['amount'] = 0;
        }

        if (!isset($data['special_discount'])) {
            $data['special_discount'] = 0;
        }

        if (!isset($data['amount_recieved'])) {
            $data['amount_recieved'] = 0;
        }

        if (!isset($data['due_balance_amount'])) {
            $data['due_balance_amount'] = $data['amount'];
        }

        if (!isset($data['discount_type'])) {
            $data['discount_type'] = 'FLAT';
        }

        if (!isset($data['sample_receive_time'])) {
            $data['sample_receive_time'] = date('H:i:s');
        }

        if (!isset($data['invoice_date'])) {
            $data['invoice_date'] = date('Y-m-d');
        }

        if (!isset($data['sample_receive_date'])) {
            $data['sample_receive_date'] = date('Y-m-d');
        }

        $product = Product::select('products.id', 'products.name', 'products.hsncode', 'product_brands.name as brand', 'tax_masters.name as tax', 'product_prices.batch as batch', 'product_prices.id as product_price_id')
            ->leftjoin('product_brands', 'product_brands.id', 'products.brand_id')
            ->leftjoin('tax_masters', 'tax_masters.id', 'products.tax_slab_id')
            ->leftjoin('product_prices', 'products.id', 'product_prices.product_id')
            ->where('products.id', $request->search_product)
            ->first();
        // Check if the product already exists in the cart
        if (isset($data['products'])) {
            foreach ($data['products'] as $cart_product) {
                if ($cart_product['price_id'] == $request->price_id) {
                    return response()->json(['success' => true, 'data' => $data]);
                }
            }
        }

        $product_price = $request->product_price;
        $main_qty      = $request->main_qty > 0 ? $request->main_qty : 1;
        $loose_qty     = $request->loose_qty > 0 ? $request->loose_qty : 1;
        $flat_discount = $request->flat_discount ?? '0';
        $per_discount  = $request->per_discount ?? '0';
        $amount = '0';

        if ($product_price > 0) {
            if ($flat_discount != '' && $flat_discount != 0) {
                $per_discount  = $flat_discount / ($product_price * $request->main_qty) * 100;

                $flat_discount = (($per_discount / 100) * $product_price) * $request->main_qty;
                $flat_per_discount = (($per_discount / 100) * $product_price);
                $per_discount  = $per_discount;
                $amount        = $product_price - $flat_per_discount;
            } else if ($per_discount != '' && $per_discount != 0) {
                $flat_discount = (($per_discount / 100) * $product_price) * $request->main_qty;
                $flat_per_discount = (($per_discount / 100) * $product_price);
                $per_discount  = $per_discount;
                $amount        = $product_price - $flat_per_discount;
            } else {
                $amount        = $product_price;
            }
        }



        $productMrp = 0;
        $product_price = $amount ?? $product_price;

        $main_qty_amount = $main_qty * $product_price;
        $product_price  = number_format($product_price, 2, '.', '');

        $ActualRateAfterTax = 0;
        $ActualRateBeforeTax = 0;
        $totalTax            = 0;

        $tax_per = $product->tax == 'Tax Free' ? 0 : preg_replace('/[^0-9]/', '', $product->tax);


        $voucher_master = VoucherMaster::select('tax_calculation')->where('voucher_code', 'SALES')->where(['status' => 1])->first();

        if ($voucher_master && $voucher_master->tax_calculation == 'EXCLUSIVE') {
            $tax             = ($tax_per / 100) * $product_price; //(12/100)*1000;
            $productMrp      = ($product_price + $tax) * $main_qty;  // 1000+
            $basic_amount    = $request->product_price;  //1000
            $ActualRateAfterTax = $product_price + $tax;
            $ActualRateBeforeTax = $product_price * $main_qty;
            $totalTax = $tax * $main_qty;
        } else {
            $basic_amount    = $request->product_price / (($tax_per + 100) / 100);
            $basePrice       = $product_price / (1 + ($tax_per / 100));
            $tax             = $product_price - $basePrice;
            $productMrp      = $product_price * $main_qty;
            $ActualRateAfterTax = $product_price - $tax;
            $ActualRateBeforeTax = $ActualRateAfterTax * $main_qty;
            $totalTax = $tax * $main_qty;
        }



        $basic_amount      = number_format($basic_amount, 2, '.', '');
        $flat_discount     = number_format($flat_discount, 2, '.', '');
        $per_discount      = number_format($per_discount, 2, '.', '');
        $tax               = number_format($tax, 2, '.', '');
        $convertion_factor = !empty($product->convertion_factor) ? $product->convertion_factor : 1;

        $amount                     = $main_qty * $amount / $convertion_factor;
        $data_amount                = $data['amount'] ?? 0;
        $data['amount']             = $data_amount + $productMrp;
        $data['due_balance_amount'] = number_format($data['amount'], 2, '.', '');

        $data['products'][] = [
            'main_qty'       => $request->main_qty ?? 1,
            'loose_qty'      => $request->main_qty > 0  ? 0 : $request->loose_qty ?? 0.00,
            'free_qty'       => $request->free_qty ?? '',
            'product_detail' => $request->product_detail ?? '',
            'price_id'       => $request->price_id ?? '',
            'product_id'     => $request->search_product ?? '',
            'product'        => $product->name ?? '',
            'hsncode'        => $product->hsncode ?? '',
            'batch'          => $request->batch_name ?? '',
            'product_price'  => $basic_amount ?? '',
            'flat_discount'  => $flat_discount ?? '',
            'per_discount'   => $per_discount ?? '',
            'vial_code'      => '',
            'amount'         => $productMrp ?? '',
            'tax_per'        => $tax_per,
            'tax_flat'       => convertDecimelPoint($totalTax),
            'product_mrp'    => convertDecimelPoint($ActualRateAfterTax) ?? '',
            'product_basic'  => convertDecimelPoint($ActualRateBeforeTax) ?? '',
            'expiry_date'    => $request->expiry_date ? date('M-Y', strtotime($request->expiry_date)) : '',
        ];

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

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

    public function productCart(Request $request, $id = 0)
    {
        $page = $request->page ?? '';
        $data = \Session::get('sales_products_cart') ?? [];
        return view('sale.product-cart', compact('data', 'page', 'id'));
    }
    public function getBatchInformation(Request $request)
    {
        $productId = $request->input('productId');

        $batch = ProductPrice::where('product_id', $productId)->get();

        if ($batch) {
            return response()->json(['batchInfo' => $batch]);
        } else {
            return response()->json(['error' => 'Batch information not found'], 404);
        }
    }
    public function cartProductRemove(Request $request)
    {
        $data = \Session::get('sales_products_cart') ?? [];
        $id   = $request->id;
        // dd($data);

        $amount = $data['amount'];

        $data['products'] = array_filter($data['products'], function ($product) use ($id, &$data, &$amount) {
            if ($product['price_id'] == $id) {
                $amount = $data['amount'] - $product['amount'];
            }

            return $product['price_id'] != $id;
        });

        if (count($data['products']) == 0) {
            $data['amount'] = 0;
        } else {
            $data['amount'] = $amount;
        }

        if ($data['discount_type'] == 'FLAT') {
            $due_balance_amount = $data['amount'] - $data['special_discount'];
            $data['due_balance_amount'] = round($due_balance_amount - $data['amount_recieved']);
        } else {
            $flat_discount = ($data['special_discount'] / 100) * $data['amount'];
            $due_balance_amount = $data['amount'] - $flat_discount;
            $data['due_balance_amount'] = round($due_balance_amount - $data['amount_recieved']);
        }

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

        return response()->json(['success' => true]);
    }

    public function billing()
    {
        $data = \Session::get('sales_products_cart') ?? [];
        if (empty($data) || !isset($data['products']) || count($data['products']) < 1) {
            $data = [];
            \Session::put('sales_products_cart', $data);

            return redirect()->route('sale.create')->with('error', 'No product added in cart!');
        }
        $companyDateFormate = phpToJsDateFormat($this->companyDateFormate());
        $ids = [];
        if (isset($data['doctor_id']) && $data['doctor_id'] != '') {
            $ids[] = $data['doctor_id'] ?? 0;
        }
        $ids[] = $data['customer_id'] ?? 0;
        $accounts = Account::select([
            'accounts.id',
            'accounts.account_type_id',
            'account_types.type_code',
            'account_contacts.qualifications',
            'account_contacts.phone_no as phone_no',
            'account_addresses.address_line1 as account_address',
            \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')
            ->leftjoin('account_addresses', 'account_addresses.account_id', 'accounts.id')
            ->whereIn('accounts.id', $ids)
            ->get();

        $accountAddress = AccountAddress::whereIn('account_id', $ids)->where('is_default', 1)->first();

        $payment_mode = PaymentMode::where('status', 1)->get();
        $sales_agent = MasterType::where('type_name', 'Sales Agent')->get();
        $delivery_agent = MasterType::where('type_name', 'Delivery Partner')->get();

        if (empty($accounts) || count($accounts) < 1) {
            $data = [];

            return redirect()->route('sale.create')->with('error', 'Empty cart!');
        }
        $customers = [];
        $doctors = [];


        foreach ($accounts as $item) {
            if ($item['type_code'] == 'CUSTOMER' || $item['type_code'] == 'PATIENT') {
                $customers = $item;
            } elseif ($item['type_code'] == 'DOCTOR') {
                $doctors = $item;
            }
        }

        $voucher_master   = VoucherMaster::select(['id', 'voucher_name'])->where('status', 1)->get();


        $accounting_group = Account::select(['id', 'name', 'account_category_id', 'accounting_group_id'])
            ->with('chart_of_account_sub_type')
            ->where(function ($query) {
                $query->whereHas('chart_of_account_sub_type', function ($accounting_group) {
                    $accounting_group->where('is_payment_mode', '1');
                });
            })
            ->get();
        $discount_group = Account::select(['id', 'name'])->where(['code' => 'DISCOUNT_ALLOWED'])->first();
        $round_off      = Account::select(['id', 'name'])->where(['code' => 'ROUN_OFF'])->first();



        $TpaAccounts = Account::select([
            'accounts.id',
            'accounts.account_type_id',
            'account_types.type_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
                "),
        ])
            ->leftJoin('account_titles', 'account_titles.id', '=', 'accounts.account_title_id')
            ->leftjoin('account_types', 'account_types.id', 'accounts.account_type_id')
            ->where('account_types.type_code', 'TPA')
            ->get();


        return view('sale.billing', compact([
            'data',
            'customers',
            'doctors',
            'voucher_master',
            // 'payment_types',
            'accounting_group',
            'accounts',
            'accountAddress',
            'sales_agent',
            'delivery_agent',
            'payment_mode',
            'discount_group',
            'round_off',
            'TpaAccounts',
            'companyDateFormate'
        ]));
    }

    public function cartProductUpdate(Request $request)
    {
        $data = \Session::get('sales_products_cart') ?? [];
        if (isset($request->type) && $request->type == 'billing') {
            if (isset($request->invoice_date)) {
                $data['invoice_date'] = date('Y-m-d', strtotime($request->invoice_date));
            }

            if (isset($request->payment_mode_id)) {
                $data['payment_mode_id'] = $request->payment_mode_id;
            }

            if (isset($request->payment_term_id)) {
                $data['payment_term_id'] = $request->payment_term_id;
            }

            if (isset($request->special_discount)) {
                $data['special_discount'] = $request->special_discount;
            }

            if (isset($request->discount_type)) {
                $data['discount_type'] = $request->discount_type;
            }

            if (isset($request->amount_recieved)) {
                $data['amount_recieved'] = $request->amount_recieved;
            }

            if (isset($request->special_discount) || isset($request->discount_type) || isset($request->amount_recieved)) {
                if (isset($data['discount_type']) && $data['discount_type'] == 'FLAT') {
                    $due_balance_amount = $data['amount'] - $data['special_discount'];
                    $data['due_balance_amount'] = convertDecimelPoint($due_balance_amount - $data['amount_recieved']);
                } else {
                    $flat_discount = ($data['special_discount'] / 100) * $data['amount'];
                    $due_balance_amount = $data['amount'] - $flat_discount;
                    $data['due_balance_amount'] = convertDecimelPoint($due_balance_amount - $data['amount_recieved']);
                }
            }
        } else {
            $amount = 0;
            if (isset($data['products'])) {
                foreach ($data['products'] as $key => $value) {

                    $saleVoucher = VoucherType::select(['default_price_type'])->where(['code' => 'SALES'])->first();

                    if ($saleVoucher->default_price_type == 'MRP') {
                        $productPrice = 'product_prices.mrp as mrp';
                    } elseif ($saleVoucher->default_price_type == 'Purchase Price') {
                        $productPrice = 'product_prices.purchase_price as mrp';
                    } elseif ($saleVoucher->default_price_type == 'WholeSale Price') {
                        $productPrice = 'product_prices.wholesale_price as mrp';
                    } elseif ($saleVoucher->default_price_type == 'Discounted/Sale Price(DP)') {
                        $productPrice = 'product_prices.sale_price as mrp';
                    } elseif ($saleVoucher->default_price_type == 'Min. Sale Price') {
                        $productPrice = 'product_prices.min_sale_price as mrp';
                    } elseif ($saleVoucher->default_price_type == 'Manufacturing Cost') {
                        $productPrice = 'product_prices.mfg_cost as mrp';
                    }

                    $product = Product::select('products.id', 'products.name', 'products.hsncode', 'product_brands.name as brand', 'tax_masters.name as tax', 'product_prices.batch as batch', $productPrice)
                        ->leftjoin('product_brands', 'product_brands.id', 'products.brand_id')
                        ->leftjoin('tax_masters', 'tax_masters.id', 'products.tax_slab_id')
                        ->leftjoin('product_prices', 'products.id', 'product_prices.product_id')
                        ->where('product_prices.id', $value['price_id'])
                        ->where('products.id', $value['product_id'])
                        ->first();

                    $tax_per = $product->tax == 'Tax Free' ? 0 : preg_replace('/[^0-9]/', '', $product->tax);

                    $voucher_master = VoucherMaster::select('tax_calculation')
                        ->where('voucher_code', 'SALES')
                        ->where(['status' => 1])
                        ->first();

                    if ($value['price_id'] == $request->id && isset($request->product_detail)) {
                        $data['products'][$key]['product_detail'] = $request->product_detail;
                    }

                    if ($value['price_id'] == $request->id) {
                        $data['amount'] = $data['amount'] - $value['amount'];

                        if (isset($request->main_qty) && $request->main_qty > 0) {
                            $data['products'][$key]['main_qty']  = $request->main_qty;
                            $data['products'][$key]['loose_qty'] = 0;

                            $main_qty_amount = $request->main_qty * $product->mrp;
                            $product_price   = number_format($main_qty_amount, 2, '.', '');
                            $per_discount    = $value['per_discount'];
                        } elseif (isset($request->loose_qty) && $request->loose_qty > 0) {
                            $data['products'][$key]['loose_qty'] = $request->loose_qty;
                            $data['products'][$key]['main_qty']  = 0;

                            $main_qty_amount = $request->loose_qty * $product->mrp;
                            $product_price    = number_format($main_qty_amount, 2, '.', '');
                            $per_discount     = $value['per_discount'];
                        } else if (isset($request->per_discount) && !empty($request->per_discount)) {
                            $main_qty_amount = $data['products'][$key]['main_qty'] * $product->mrp;
                            $product_price   = number_format($main_qty_amount, 2, '.', '');
                            $per_discount    = $request->per_discount;
                        } else {
                            $main_qty_amount = $data['products'][$key]['main_qty'] * $product->mrp;
                            $product_price   = number_format($main_qty_amount, 2, '.', '');
                            $per_discount    = $value['per_discount'];
                        }

                        $product_price = $product->mrp;
                        $flat_discount = $request->flat_discount ?? '0';
                        $per_discount  = $request->per_discount ?? $data['products'][$key]['per_discount'];

                        $productMrp = $basic_amount = 0;

                        if ($product_price > 0) {
                            if ($flat_discount != '' && $flat_discount != 0) {
                                $per_discount  = $flat_discount / ($product_price * $data['products'][$key]['main_qty']) * 100;

                                $flat_discount = (($per_discount / 100) * $product_price) * $data['products'][$key]['main_qty'];
                                $flat_per_discount = (($per_discount / 100) * $product_price);
                                $per_discount  = $per_discount;
                                $amount        = $product_price - $flat_per_discount;
                            } else if ($per_discount != '' && $per_discount != 0) {

                                $flat_discount = (($per_discount / 100) * $product_price) * $data['products'][$key]['main_qty'];
                                $flat_per_discount = (($per_discount / 100) * $product_price);
                                $per_discount  = $per_discount;
                                $amount        = $product_price - $flat_per_discount;
                            } else {
                                $amount        = $product_price;
                            }
                        }

                        $product_price = $amount ?? $product_price;

                        if ($voucher_master && $voucher_master->tax_calculation == 'EXCLUSIVE') {
                            $tax   =  $flat_tax        = ($tax_per / 100) * $product_price; //(12/100)*1000;
                            $productMrp      = ($product_price + $tax) * $data['products'][$key]['main_qty'];  // 1000+
                            $basic_amount    = $product->mrp;  //1000

                            $ActualRateAfterTax = $product_price + $tax;
                            $ActualRateBeforeTax = $product_price * $data['products'][$key]['main_qty'];
                            $totalTax = $tax * $data['products'][$key]['main_qty'];
                        } else {
                            $basic_amount    = $product->mrp / (($tax_per + 100) / 100);
                            $basePrice       = $product_price / (1 + ($tax_per / 100));
                            $tax  =    $flat_tax       = $product_price - $basePrice;
                            $productMrp      = $product_price * $data['products'][$key]['main_qty'];
                            $ActualRateAfterTax = $product_price - $tax;
                            $ActualRateBeforeTax = $ActualRateAfterTax * $data['products'][$key]['main_qty'];
                            $totalTax = $tax * $data['products'][$key]['main_qty'];
                        }


                        $amount = ($basic_amount - $flat_discount);
                        $amount = number_format($amount, 2, '.', '');

                        $convertion_factor = !empty($product->convertion_factor) ? $product->convertion_factor : 1;
                        $amount            = $main_qty_amount * $amount / $convertion_factor;
                        $flat_discount     = number_format($flat_discount, 2, '.', '');
                        $flat_tax          = number_format($totalTax, 2, '.', '');
                        $data['products'][$key]['flat_discount'] = $flat_discount;
                        $data['products'][$key]['per_discount']  = $per_discount;
                        $data['products'][$key]['tax_flat']      = $flat_tax;
                        $data['products'][$key]['amount']        = convertDecimelPoint($productMrp);
                        $data['products'][$key]['product_price'] = convertDecimelPoint($basic_amount);
                        $data['products'][$key]['product_mrp']   = convertDecimelPoint($ActualRateAfterTax) ?? '';
                        $data['products'][$key]['product_basic'] = convertDecimelPoint($ActualRateBeforeTax) ?? '';
                        $data['amount']                          = $data['amount'] + $productMrp;
                        $due_balance_amount                      = $data['amount'] - $flat_discount;
                        $data['due_balance_amount']              = number_format($due_balance_amount - $data['amount_recieved'], 2, '.', '');
                    }

                    if ($value['price_id'] == $request->id && isset($request->product_price)) {
                        $data['amount'] = $data['amount'] - $value['amount'];
                        $product_price = $request->product_price;
                        $flat_discount = $request->flat_discount ?? '0';
                        $per_discount  = $request->per_discount ?? $data['products'][$key]['per_discount'];

                        $productMrp = $basic_amount = 0;

                        if ($product_price > 0) {
                            if ($flat_discount != '' && $flat_discount != 0) {
                                $per_discount  = $flat_discount / ($product_price * $data['products'][$key]['main_qty']) * 100;

                                $flat_discount = (($per_discount / 100) * $product_price) * $data['products'][$key]['main_qty'];
                                $flat_per_discount = (($per_discount / 100) * $product_price);
                                $per_discount  = $per_discount;
                                $amount        = $product_price - $flat_per_discount;
                            } else if ($per_discount != '' && $per_discount != 0) {

                                $flat_discount = (($per_discount / 100) * $product_price) * $data['products'][$key]['main_qty'];
                                $flat_per_discount = (($per_discount / 100) * $product_price);
                                $per_discount  = $per_discount;
                                $amount        = $product_price - $flat_per_discount;
                            } else {
                                $amount        = $product_price;
                            }
                        }

                        $product_price = $amount ?? $product_price;

                        if ($voucher_master && $voucher_master->tax_calculation == 'EXCLUSIVE') {
                            $tax   =  $flat_tax        = ($tax_per / 100) * $product_price; //(12/100)*1000;
                            $productMrp      = ($product_price + $tax) * $data['products'][$key]['main_qty'];  // 1000+
                            $basic_amount    = $request->product_price;  //1000

                            $ActualRateAfterTax = $product_price + $tax;
                            $ActualRateBeforeTax = $product_price * $data['products'][$key]['main_qty'];
                            $totalTax = $tax * $data['products'][$key]['main_qty'];
                        } else {
                            $basic_amount    = $request->product_price / (($tax_per + 100) / 100);
                            $basePrice       = $product_price / (1 + ($tax_per / 100));
                            $tax  =    $flat_tax       = $product_price - $basePrice;
                            $productMrp      = $product_price * $data['products'][$key]['main_qty'];
                            $ActualRateAfterTax = $product_price - $tax;
                            $ActualRateBeforeTax = $ActualRateAfterTax * $data['products'][$key]['main_qty'];
                            $totalTax = $tax * $data['products'][$key]['main_qty'];
                        }


                        $amount = ($basic_amount - $flat_discount);
                        $amount = number_format($amount, 2, '.', '');

                        $convertion_factor = !empty($product->convertion_factor) ? $product->convertion_factor : 1;
                        $amount            = $main_qty_amount * $amount / $convertion_factor;
                        $flat_discount     = number_format($flat_discount, 2, '.', '');
                        $flat_tax          = number_format($totalTax, 2, '.', '');
                        $data['products'][$key]['flat_discount'] = $flat_discount;
                        $data['products'][$key]['per_discount']  = $per_discount;
                        $data['products'][$key]['tax_flat']      = $flat_tax;
                        $data['products'][$key]['amount']        = convertDecimelPoint($productMrp);
                        $data['products'][$key]['product_price'] = convertDecimelPoint($basic_amount);
                        $data['products'][$key]['product_mrp']   = convertDecimelPoint($ActualRateAfterTax) ?? '';
                        $data['products'][$key]['product_basic'] = convertDecimelPoint($ActualRateBeforeTax) ?? '';
                        $data['amount']                          = $data['amount'] + $productMrp;
                        $due_balance_amount                      = $data['amount'] - $flat_discount;
                        $data['due_balance_amount']              = number_format($due_balance_amount - $data['amount_recieved'], 2, '.', '');
                    }

                    if (isset($request->special_discount) || isset($request->discount_type) || isset($request->amount_recieved)) {
                        if (isset($data['discount_type']) && $data['discount_type'] == 'FLAT') {
                            $due_balance_amount = $data['amount'] - $data['special_discount'];
                            $data['due_balance_amount'] = round($due_balance_amount - $data['amount_recieved']);
                        } else {
                            $flat_discount = ($data['special_discount'] / 100) * $data['amount'];
                            $due_balance_amount = $data['amount'] - $flat_discount;
                            $data['due_balance_amount'] = round($due_balance_amount - $data['amount_recieved']);
                        }
                    }
                }
            }

            if (isset($request->customer_id)) {
                $data['customer_id'] = $request->customer_id;
            }

            if (isset($request->doctor_id)) {
                $data['doctor_id'] = $request->doctor_id;
            }
        }

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

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

    public function saleInvoice($id)
    {
        $sale_invoice = SaleInvoice::with('getSaleInvoiceDetails')
            ->with('getSaleInvoiceDelivery')
            ->with('getCustomer')
            ->with('getPaymentTerm')
            ->with('getCompany')
            ->find($id);
        if (!$sale_invoice) {
            return redirect()->route('sale.index')->with('error', 'Sale Invoice not found');
        }

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

        $transactionSummery = $this->TransactionSummery($id, 'SALES');
        $transactionHistory = $this->transactionHistory($id, 'SALES');

        return view('sale.sale-billing-invoice', compact('sale_invoice', 'voucher_type', 'transactionSummery', 'transactionHistory'));
    }
    public function SaleeditBilling($id)
    {
        $sale_invoice = SaleInvoice::find($id);
        // dd($sale_invoice);
        $companyDateFormate = phpToJsDateFormat($this->companyDateFormate());
        if (!$sale_invoice) {
            $data = [];
            \Session::put('sales_products_cart', $data);

            return redirect()->route('sale.index')->with('error', 'Sale billing invoice not found!');
        }

        $data = \Session::get('sales_products_cart') ?? [];
        if (empty($data) || !isset($data['products']) || count($data['products']) < 1) {
            $data = [];
            \Session::put('sales_products_cart', $data);

            return redirect()->route('sale.edit', $id)->with('error', 'No product added in cart!');
        }

        $ids = [];

        if (isset($data['doctor_id']) && $data['doctor_id'] != '') {
            $ids[] = $data['doctor_id'] ?? 0;
        }

        $ids[] = $data['customer_id'] ?? 0;

        $accounts = Account::select([
            'accounts.id',
            'accounts.account_type_id',
            'account_types.type_code',
            'account_contacts.qualifications',
            'account_contacts.phone_no as phone_no',
            \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("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('account_types', 'account_types.id', 'accounts.account_type_id')
            ->whereIn('accounts.id', $ids)
            ->get();
        if (empty($accounts) || count($accounts) < 1) {
            $data = [];

            return redirect()->route('sale.create')->with('error', 'Empty cart!');
        }
        // dd($accounts);

        $customer = [];
        $doctors  = [];

        foreach ($accounts as $item) {
            if ($item['type_code'] == 'CUSTOMER' || $item['type_code'] == 'PATIENT') {
                $customer = $item;
            } elseif ($item['type_code'] == 'DOCTOR') {
                $doctors = $item;
            }
        }


        // $payment_types  = PaymentTerm::select(['id','is_default','payment_term_name'])
        //     ->orderBy('priority', 'ASC')
        //     ->get();

        $sales_agent = MasterType::where('type_name', 'Sales Agent')->get();
        $delivery_agent = MasterType::where('type_name', 'Delivery Partner')->get();

        $accounting_group = Account::select(['id', 'name', 'account_category_id', 'accounting_group_id'])
            ->with('chart_of_account_sub_type')
            ->where(function ($query) {
                $query->whereHas('chart_of_account_sub_type', function ($accounting_group) {
                    $accounting_group->where('is_payment_mode', '1');
                });
            })
            ->get();

        $receipt = AcReceipt::where('voucher_id', $id)->first();
        $creditTransaction = [];
        $debitTransaction = [];

        if ($receipt) {
            $creditTransaction = AcReceiptDetail::with('account')
                ->where(['voucher_id' => $receipt->id, 'transaction_type' => 'DEBIT'])
                ->get();

            $debitTransaction = AcReceiptDetail::with('account')
                ->where(['voucher_id' => $receipt->id, 'transaction_type' => 'DEBIT'])
                ->first();
        }

        $discount_group = Account::select(['id', 'name'])->where(['code' => 'DISCOUNT_ALLOWED'])->first();
        $round_off      = Account::select(['id', 'name'])->where(['code' => 'ROUN_OFF'])->first();

        $transactionSummery = $this->TransactionSummery($id, 'SALES');
        $transactionHistory = $this->transactionHistory($id, 'SALES');
        $TpaAccounts = Account::select([
            'accounts.id',
            'accounts.account_type_id',
            'account_types.type_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
                "),
        ])
            ->leftJoin('account_titles', 'account_titles.id', '=', 'accounts.account_title_id')
            ->leftjoin('account_types', 'account_types.id', 'accounts.account_type_id')
            ->where('account_types.type_code', 'TPA')
            ->get();

        $allTransactionData = AcReceipt::with('accountReceiptDetail', 'accountReceiptTransaction')->where('voucher_id', $id)->where('module_code', 'SALES')->latest()->get();

        return view('sale.edit.sale-billing', compact([
            'id',
            'data',
            'customer',
            'doctors',
            'sale_invoice',
            // 'voucher_master',
            'delivery_agent',
            'sales_agent',
            'accounting_group',
            'receipt',
            'creditTransaction',
            'debitTransaction',
            'discount_group',
            'round_off',
            'transactionHistory',
            'transactionSummery',
            'TpaAccounts',
            'companyDateFormate',
            'allTransactionData'
        ]));
    }

    public function saleCollection($id)
    {
        $data = AcReceipt::where('voucher_id', $id)->first();
        $sale_invoice = SaleInvoice::find($id);
        // dd($data);

        if (!$sale_invoice) {
            return redirect()->route('sale.index')->with('error', 'Record Not Found!');
        }

        $creditTransaction = $debitTransaction = [];

        if ($data && isset($data->id)) {
            $creditTransaction = AcReceiptDetail::with('account')
                ->where(['voucher_id' => $data->id, 'transaction_type' => 'CREDIT'])
                ->get();

            $debitTransaction = AcReceiptDetail::with('account')
                ->where(['voucher_id' => $data->id, 'transaction_type' => 'DEBIT'])
                ->first();
        }

        $accounting_group = Account::select(['id', 'name', 'account_category_id'])
            ->with('chart_of_account_sub_type')
            ->where(function ($query) {
                $query->whereHas('chart_of_account_sub_type', function ($accounting_group) {
                    $accounting_group->where('is_payment_mode', '1');
                });
            })
            ->get();

        $financialYears = FinancialYear::select(['financialyearid', 'financial_year_name', 'is_default'])->where(['status' => '1'])->orderBy('financialyearid', 'desc')->get();
        $redirect = 'sale';

        return view('sale.edit.collect', compact([
            'data',
            'sale_invoice',
            'accounting_group',
            'creditTransaction',
            'debitTransaction',
            'financialYears',
            'redirect'
        ]));
    }

    public function saleCollectionUpdate(Request $request, $id)
    {
        $receiptVoucher = AcReceipt::find($id);

        if (!$receiptVoucher) {
            return redirect()->back()->with('error', 'Invalid Receipt Id!');
        }

        $item = [
            'receipt_date'      => date('Y-m-d', strtotime($request->receipt_date)),
            'remarks'           => $request->remarks,
            'financial_year_id' => $request->financial_year_id,
        ];

        $receiptVoucher->update($item);

        $item1 = [
            'account_id' => $request->account_id ?? '',
            'amount'     => $request->total_amount,
        ];

        $acDEBITReceiptTransaction = AcReceiptDetail::where([
            'transaction_type' => 'DEBIT',
            'voucher_id'       => $id ?? ''
        ])->first();
        $acDEBITReceiptTransaction->update($item1);

        $voucher_collection = VoucherCollection::where([
            'money_receipt_id'  => $receiptVoucher->id,
        ])->first();

        $voucher_collection->update(['updated_by' => Auth::user()->id]);

        $moduleReceipt = MenuModelPermission::where('code', 'SALES')->first();
        $transaction   = AccountTransaction::where([
            'module_code'       => 'SALES',
            'voucher_id'        => $id,
            'voucher_type'      => 'SALES', // SALE, PURCHASE, PAYMENT
            'transaction_type'  => 'DEBIT',
        ])
            ->first();

        $transactions_no = $transaction->transactions_no;

        if (isset($request->payment_mode) && count($request->payment_mode) > 0) {
            foreach ($request->payment_mode as $key => $item) {
                if ($item['amount'] > 0) {
                    $item1 = [
                        'module_id'              => $moduleReceipt->id ?? '',
                        'voucher_id'             => $id,
                        'voucher_type'           => 'SALES', // SALE, PURCHASE, PAYMENT
                        'account_id'             => $key,
                        'transaction_type'       => 'CREDIT',
                        'details_narration'      => '',
                        'amount'                 => $item['amount'],
                        'transaction_referrence' => $item['transaction_referrence'],
                        'created_by'             => Auth::user()->id,
                        'company_id'             => Auth::user()->company_id,
                        'transaction_date'       => date('Y-m-d', strtotime($item['date'])),
                        'transactions_status'    => true
                    ];
                    $acReceiptTransaction = AcReceiptDetail::create($item1);

                    $transaction = AccountTransaction::createTransaction([
                        'transactions_no'   => $transactions_no,
                        'transaction_date'  => date('Y-m-d', strtotime($item['date'])),
                        'module_code'       => 'SALES',
                        'voucher_id'        => $id,
                        'voucher_sub_id'    => $acReceiptTransaction->id,
                        'voucher_type'      => 'SALES', // SALE, PURCHASE, PAYMENT
                        'account_id'        => $request->account_id,
                        'transaction_type'  => 'CREDIT',
                        'details_narration' => '',
                        'amount'            => $item['amount']
                    ]);

                    $transaction = AccountTransaction::createTransaction([
                        'transactions_no'   => $transactions_no,
                        'transaction_date'  => date('Y-m-d', strtotime($item['date'])),
                        'module_code'       => 'SALES',
                        'voucher_id'        => $id,
                        'voucher_sub_id'    => $acReceiptTransaction->id,
                        'voucher_type'      => 'SALES', // SALE, PURCHASE, PAYMENT
                        'account_id'        => $key ?? '',
                        'transaction_type'  => 'DEBIT',
                        'details_narration' => '',
                        'amount'            => $item['amount']
                    ]);

                    $voucher_collection_detail = VoucherCollectionDetail::create([
                        'voucher_collection_id' => $voucher_collection->id,
                        'transaction_id'        => $transaction->id,
                    ]);
                }
            }
        }
        if (isset($request->update_payment_mode) && count($request->update_payment_mode) > 0) {
            foreach ($request->update_payment_mode as $key => $item) {
                $item1 = [
                    'module_id'              => $moduleReceipt->id ?? '',
                    'voucher_type'           => 'SALES', // SALE, PURCHASE, PAYMENT
                    'transaction_type'       => 'CREDIT',
                    'details_narration'      => '',
                    'amount'                 => $item['amount'],
                    'created_by'             => Auth::user()->id,
                    'company_id'             => Auth::user()->company_id,
                    'transaction_date'       => date('Y-m-d', strtotime($item['date'])),
                    'transaction_referrence' => $item['transaction_referrence'],
                    'transactions_status'    => true
                ];

                $acReceiptTransaction = AcReceiptDetail::updateOrCreate([
                    'id'         => $key,
                    'voucher_id' => $id
                ], $item1);

                $transaction = AccountTransaction::updateTransaction([
                    'module_code'       => 'SALES',
                    'voucher_id'        => $id,
                    'voucher_sub_id'    => $key,
                    'voucher_type'      => 'SALES', // SALE, PURCHASE, PAYMENT
                    'transaction_type'  => 'DEBIT',
                ], [
                    'transaction_date'  => date('Y-m-d', strtotime($item['date'])),
                    'amount'            => $item['amount']
                ]);

                AccountTransaction::updateTransaction([
                    'module_code'      => 'SALES',
                    'voucher_type'     => 'SALES', // SALE, PURCHASE, PAYMENT
                    'account_id'       => $request->account_id,
                    'transaction_type' => 'CREDIT',
                    'transactions_no'  => $transaction->transactions_no,
                ], [
                    'transaction_date'  => date('Y-m-d', strtotime($request->receipt_date)),
                    'amount'            => $item['amount']
                ]);
            }
        }

        $module = MenuModelPermission::find($acDEBITReceiptTransaction->module_id);

        $sale_invoice = SaleInvoice::find($receiptVoucher->voucher_id);

        if ($sale_invoice) {
            $received_amount = $request->total_amount;
            $due = $sale_invoice->billing_amount - $sale_invoice->discount_amount - $received_amount;

            $sale_invoice->update([
                'due_amount'      => $due,
                'received_amount' => $received_amount,
            ]);
        }
        return redirect()->route('sale.index')->with('success', 'Collection Updated Successfully');
    }
    public function getPaymentModes(Request $request)
    {
        $transaction =   $request->transaction_mode_id;
        $paymentModes = PaymentMode::where('accounting_group_id', $transaction)
            // ->orderByRaw('is_default DESC, type_name ASC')
            ->get();
        return response()->json($paymentModes);
    }

    public function saleStatement(Request $request)
    {
        if ($request->ajax()) {
            $from_date         = $request->from_date ? date('Y-m-d', strtotime($request->from_date)) : date('Y-m-d');
            $to_date           = $request->to_date ? date('Y-m-d', strtotime($request->to_date)) : date('Y-m-d');
            $company           = $request->company ?? '';
            $search_account    = $request->search_account ?? '';
            $created_by        = $request->created_by ?? '';

            $voucher_type_code = $request->voucher_type_code ?? '';

            $transaction_mode = Account::select(['id', 'name', 'account_category_id'])
                ->with('chart_of_account_sub_type')
                ->where(function ($query) {
                    $query->whereHas('chart_of_account_sub_type', function ($accounting_group) {
                        $accounting_group->where('is_payment_mode', '1');
                    });
                })
                ->get()->pluck('id');

            $columns = [
                'sale_invoices.id',
                'sale_invoices.invoice_date',
                'sale_invoices.main_invoice_no',
                'sale_invoices.patient_id',
                'sale_invoices.voucher_type_code',
                'sale_invoices.payment_mode_id',
                'tax_masters.total_percentage as tax_percentage',
                'payment_terms.payment_term_name',
                'sale_others_transactions.price as discount_amount',
                'ac_receipts.id as ac_receipt_id',
                \DB::raw('SUM(sale_invoice_details.total_amount) as billing_amount'),
                \DB::raw('SUM(ac_receipt_details.amount) as ac_receipt_amount')
            ];

            $saleInvoices = SaleInvoice::leftJoin('sale_others_transactions', 'sale_others_transactions.sale_invoice_id', 'sale_invoices.id')
                ->leftJoin('payment_terms', 'payment_terms.id', 'sale_invoices.transaction_mode_id')
                ->leftJoin('sale_invoice_details', 'sale_invoice_details.sale_invoice_id', 'sale_invoices.id')
                ->leftJoin('tax_masters', 'sale_invoice_details.tax_slab_id', 'tax_masters.id')
                ->leftJoin('accounts', 'sale_invoices.patient_id', 'accounts.id')
                ->leftJoin('ac_receipts', function ($join) {
                    $join->on('ac_receipts.voucher_id', '=', 'sale_invoices.id');
                })
                ->leftJoin('ac_receipt_details', function ($join) use ($transaction_mode) {
                    $join->on('ac_receipt_details.ac_receipt_id', '=', 'ac_receipts.id')
                        ->whereIn('ac_receipt_details.account_id', $transaction_mode)
                        ->where('ac_receipt_details.transaction_type', 'DEBIT');
                })
                ->when(!empty($search_account), function ($saleInvoices) use ($search_account) {
                    $saleInvoices->where('accounts.name', 'LIKE', '%' . $search_account . '%');
                })
                ->when(!empty($voucher_type_code), function ($saleInvoices) use ($voucher_type_code) {
                    $saleInvoices->where('sale_invoices.voucher_type_code', '=', $voucher_type_code);
                })
                ->when(!empty($created_by), function ($saleInvoices) use ($created_by) {
                    $saleInvoices->where('sale_invoices.operator_id', '=', $created_by);
                })
                ->when(!empty($company), function ($saleInvoices) use ($company) {
                    $saleInvoices->where('sale_invoices.company_id', '=', $company);
                })
                ->when(!empty($from_date) && !empty($to_date), function ($query) use ($from_date, $to_date) {
                    $query->when($from_date == $to_date, function ($query2) use ($from_date) {
                        $query2->whereDate('sale_invoices.invoice_date', $from_date);
                    })
                        ->when($from_date != $to_date, function ($query2) use ($from_date, $to_date) {
                            $query2->whereBetween('sale_invoices.invoice_date', [$from_date, $to_date]);
                        });
                })
                ->select($columns)
                ->groupBy('sale_invoices.main_invoice_no')
                ->get();

            return view('invoice-formats.sales.statement-table', compact([
                'voucher_type_code',
                'saleInvoices',
                'from_date',
                'to_date',
                'transaction_mode',
            ]));
        }


        $company       = Company::find(Auth::user()->company_id);
        $companies     = Company::get();
        $voucher_type  = VoucherType::where('code', 'SALES')->where(['status' => 1])->first();
        $voucher_types = VoucherType::select('name', 'code')->where(['status' => 1])->get();

        return view('invoice-formats.sales.statement', compact(
            'voucher_types',
            'companies',
            'company',
        ));
    }

    public function saleStatementExport(Request $request)
    {
        $companyDateFormate = phpToJsDateFormat($this->companyDateFormate());

        $from_date         = $request->from_date ? date('Y-m-d', strtotime($request->from_date)) : date('Y-m-d');
        $to_date           = $request->to_date ? date('Y-m-d', strtotime($request->to_date)) : date('Y-m-d');
        $company           = $request->company ?? '';
        $search_account    = $request->search_account ?? '';
        $created_by        = $request->created_by ?? '';

        $voucher_type_code = $request->voucher_type_code ?? '';

        $transaction_mode = Account::select(['id', 'name', 'account_category_id'])
            ->with('chart_of_account_sub_type')
            ->where(function ($query) {
                $query->whereHas('chart_of_account_sub_type', function ($accounting_group) {
                    $accounting_group->where('is_payment_mode', '1');
                });
            })
            ->get()->pluck('id');

        $columns = [
            'sale_invoices.id',
            'sale_invoices.invoice_date',
            'sale_invoices.main_invoice_no',
            'sale_invoices.patient_id',
            'sale_invoices.voucher_type_code',
            'sale_invoices.payment_mode_id',
            'tax_masters.total_percentage as tax_percentage',
            'payment_terms.payment_term_name',
            'sale_others_transactions.price as discount_amount',
            'accounts.name as patient',
            'accounts.code as patient_code',
            'ac_receipts.id as ac_receiptst_amount',
            \DB::raw('SUM(sale_invoice_details.price) as billing_amount'),
            \DB::raw('SUM(ac_receipt_details.amount) as ac_receipt_amount')
        ];

        $saleInvoices = SaleInvoice::with(
            'getPatient',
        )
            ->leftJoin('sale_others_transactions', 'sale_others_transactions.sale_invoice_id', 'sale_invoices.id')
            ->leftJoin('payment_terms', 'payment_terms.id', 'sale_invoices.transaction_mode_id')
            ->leftJoin('sale_invoice_details', 'sale_invoice_details.sale_invoice_id', 'sale_invoices.id')
            ->leftJoin('tax_masters', 'sale_invoice_details.tax_slab_id', 'tax_masters.id')
            ->leftJoin('accounts', 'sale_invoices.patient_id', 'accounts.id')
            ->leftJoin('ac_receipts', function ($join) {
                $join->on('ac_receipts.voucher_id', '=', 'sale_invoices.id')
                    ->where('ac_receipts.module_code', '=', 'SALES');
            })
            ->leftJoin('ac_receipt_details', function ($join) use ($transaction_mode) {
                $join->on('ac_receipt_details.ac_receipt_id', '=', 'ac_receipts.id')
                    ->whereIn('ac_receipt_details.account_id', $transaction_mode);
            })
            ->when(!empty($search_account), function ($saleInvoices) use ($search_account) {
                $saleInvoices->where('accounts.name', 'LIKE', '%' . $search_account . '%');
            })
            ->when(!empty($voucher_type_code), function ($saleInvoices) use ($voucher_type_code) {
                $saleInvoices->where('sale_invoices.voucher_type_code', '=', $voucher_type_code);
            })
            ->when(!empty($created_by), function ($saleInvoices) use ($created_by) {
                $saleInvoices->where('sale_invoices.operator_id', '=', $created_by);
            })
            ->when(!empty($company), function ($saleInvoices) use ($company) {
                $saleInvoices->where('sale_invoices.company_id', '=', $company);
            })
            ->when(!empty($from_date) && !empty($to_date), function ($query) use ($from_date, $to_date) {
                $query->when($from_date == $to_date, function ($query2) use ($from_date) {
                    $query2->whereDate('sale_invoices.invoice_date', $from_date);
                })
                    ->when($from_date != $to_date, function ($query2) use ($from_date, $to_date) {
                        $query2->whereBetween('sale_invoices.invoice_date', [$from_date, $to_date]);
                    });
            })
            ->where(function ($query) {
                $query->where('sale_invoices.voucher_type_code', 'IPD_ADMISSION')
                    ->orWhere('sale_invoices.voucher_type_code', 'PATHOLOGY');
            })
            ->select($columns)
            ->groupBy('main_invoice_no')
            ->get()
            ->toArray();

        $fileName = 'Sales-Statements' . ' From ' . $from_date . ' To ' . $to_date . '.xlsx';

        return Excel::download(new SaleStatementExport($saleInvoices), $fileName);
    }

    public function getBillTo(Request $request)
    {
        $accounts = Account::whereIn('id', [$request->tpa_corporate_id, $request->customer_id])->get(['id', 'name']);
        return response()->json($accounts);
    }


    public function consolidatedSaleStatement(Request $request)
    {
        if ($request->ajax()) {
            $from_date         = $request->from_date ? date('Y-m-d', strtotime($request->from_date)) : date('Y-m-d');
            $to_date           = $request->to_date ? date('Y-m-d', strtotime($request->to_date)) : date('Y-m-d');
            $company           = $request->company ?? '';
            $search_account    = $request->search_account ?? '';
            $created_by        = $request->created_by ?? '';

            $voucher_type_code = $request->voucher_type_code ?? '';

            $transaction_mode = Account::select(['id', 'name', 'account_category_id'])
                ->with('chart_of_account_sub_type')
                ->where(function ($query) {
                    $query->whereHas('chart_of_account_sub_type', function ($accounting_group) {
                        $accounting_group->where('is_payment_mode', '1');
                    });
                })
                ->get()->pluck('id');

            $columns = [
                'products.id',
                'products.name as product',
                'sale_invoices.invoice_date',
                'sale_invoices.main_invoice_no',
                'sale_invoices.patient_id',
                'sale_invoices.voucher_type_code',
                'sale_invoices.payment_mode_id',
                'tax_masters.total_percentage as tax_percentage',
                'payment_terms.payment_term_name',
                'ac_receipts.id as ac_receiptst_amount',
            ];

            $saleInvoices = Product::leftjoin('sale_invoice_details', 'sale_invoice_details.product_id', 'products.id')
                ->leftJoin('sale_invoices', 'sale_invoice_details.sale_invoice_id', 'sale_invoices.id')
                ->leftjoin('product_prices', 'product_prices.product_id', 'products.id')
                ->leftJoin('payment_terms', 'payment_terms.id', 'sale_invoices.transaction_mode_id')
                ->leftJoin('tax_masters', 'sale_invoice_details.tax_slab_id', 'tax_masters.id')
                ->leftJoin('accounts', 'sale_invoices.patient_id', 'accounts.id')
                ->leftJoin('ac_receipts', function ($join) {
                    $join->on('ac_receipts.voucher_id', '=', 'sale_invoices.id')
                        ->where('ac_receipts.module_code', '=', 'SALES');
                })
                ->leftJoin('ac_receipt_details', function ($join) use ($transaction_mode) {
                    $join->on('ac_receipt_details.ac_receipt_id', '=', 'ac_receipts.id')
                        ->whereIn('ac_receipt_details.account_id', $transaction_mode);
                })
                ->when(!empty($search_account), function ($saleInvoices) use ($search_account) {
                    $saleInvoices->where('accounts.name', 'LIKE', '%' . $search_account . '%');
                })
                ->when(!empty($voucher_type_code), function ($saleInvoices) use ($voucher_type_code) {
                    $saleInvoices->where('sale_invoices.voucher_type_code', '=', $voucher_type_code);
                })
                ->when(!empty($created_by), function ($saleInvoices) use ($created_by) {
                    $saleInvoices->where('sale_invoices.operator_id', '=', $created_by);
                })
                ->when(!empty($company), function ($saleInvoices) use ($company) {
                    $saleInvoices->where('sale_invoices.company_id', '=', $company);
                })
                ->when(!empty($from_date) && !empty($to_date), function ($query) use ($from_date, $to_date) {
                    $query->when($from_date == $to_date, function ($query2) use ($from_date) {
                        $query2->whereDate('sale_invoices.invoice_date', $from_date);
                    })
                        ->when($from_date != $to_date, function ($query2) use ($from_date, $to_date) {
                            $query2->whereBetween('sale_invoices.invoice_date', [$from_date, $to_date]);
                        });
                })
                ->select($columns)
                ->groupBy('main_invoice_no')
                ->get();

            return view('invoice-formats.sales.consolidated-sale-statement.statement-table', compact([
                'voucher_type_code',
                'saleInvoices',
                'from_date',
                'to_date',
                'transaction_mode',
            ]));
        }


        $company       = Company::find(Auth::user()->company_id);
        $companies     = Company::get();
        $voucher_type  = VoucherType::where('code', 'SALES')->where(['status' => 1])->first();
        $voucher_types = VoucherType::select('name', 'code')->where(['status' => 1])->get();

        return view('invoice-formats.sales.consolidated-sale-statement.statement', compact(
            'voucher_type',
            'voucher_types',
            'companies',
            'company',
        ));
    }

    public function reportsAndInsights(Request $request)
    {
        $company       = Company::find(Auth::user()->company_id);
        if ($request->ajax()) {
            $filters["from_date"]         = $from_date         = $request->from_date ? date('Y-m-d', strtotime($request->from_date)) : date('Y-m-d');
            $filters["to_date"]           = $to_date           = $request->to_date ? date('Y-m-d', strtotime($request->to_date)) : date('Y-m-d');
            $filters["company"]           = $company           = $request->company ?? '';
            $filters["search_account"]    = $search_account    = $request->search_account ?? '';
            $filters["created_by"]        = $created_by        = $request->created_by ?? '';
            $filters["voucher_type_code"] = $voucher_type_code = $request->voucher_type_code ?? '';
            $filters["brand_id"]          = $brand_id     = $request->get('brand_id');
            $filters["group_id"]          = $group_id     = $request->get('group_id');
            $filters["category_id"]       = $category_id  = $request->get('category_id');
            $filters["division_id"]       = $division_id  = $request->get('division_id');
            $filters["doctor_id"]         = $doctor_id  = $request->get('doctor_id');

            $filters["transaction_mode"] = $transaction_mode = Account::select(['id', 'name', 'account_category_id'])
                ->with('chart_of_account_sub_type')
                ->where(function ($query) {
                    $query->whereHas('chart_of_account_sub_type', function ($accounting_group) {
                        $accounting_group->where('is_payment_mode', '1');
                    });
                })
                ->get()->pluck('id');

            $saleInvoices = SaleInvoiceDetail::getReportsAndInsightsStatement([], $filters);
            $company       = Company::find(Auth::user()->company_id);
            return view('invoice-formats.sales.item-wise-statement.statement-table', compact([
                'voucher_type_code',
                'saleInvoices',
                'from_date',
                'to_date',
                'transaction_mode',
                'company'
            ]));
        }

        
        $companies     = Company::get();
        $voucher_type  = VoucherType::where('code', 'SALES')->where(['status' => 1])->first();
        $voucher_types = VoucherType::select('name', 'code')->where(['status' => 1])->get();

        $business_division = BusinessDivision::select('id', 'name')->where('status', 1)->get();
        $brand             = ProductBrand::select('id', 'name')->where('status', 1)->get();
        $category          = ProductCategory::select('id', 'name')->where('status', 1)->get();
        $group             = ProductGroup::select('id', 'name')->where('status', 1)->get();

        return view('invoice-formats.sales.item-wise-statement.statement', compact(
            'voucher_types',
            'companies',
            'company',
            'business_division',
            'brand',
            'category',
            'group'
        ));
    }

    public function reportsAndInsightsExport(Request $request)
    {
        $filters["from_date"]         = $from_date         = $request->from_date ? date('Y-m-d', strtotime($request->from_date)) : date('Y-m-d');
        $filters["to_date"]           = $to_date           = $request->to_date ? date('Y-m-d', strtotime($request->to_date)) : date('Y-m-d');
        $filters["company"]           = $company           = $request->company ?? '';
        $filters["search_account"]    = $search_account    = $request->search_account ?? '';
        $filters["created_by"]        = $created_by        = $request->created_by ?? '';
        $filters["voucher_type_code"] = $voucher_type_code = $request->voucher_type_code ?? '';
        $filters["brand_id"]          = $brand_id     = $request->get('brand_id');
        $filters["group_id"]          = $group_id     = $request->get('group_id');
        $filters["category_id"]       = $category_id  = $request->get('category_id');
        $filters["division_id"]       = $division_id  = $request->get('division_id');
        $filters["doctor_id"]         = $doctor_id     = $request->get('doctor_id');

        $filters["transaction_mode"] = $transaction_mode = Account::select(['id', 'name', 'account_category_id'])
            ->with('chart_of_account_sub_type')
            ->where(function ($query) {
                $query->whereHas('chart_of_account_sub_type', function ($accounting_group) {
                    $accounting_group->where('is_payment_mode', '1');
                });
            })
            ->get()->pluck('id');

        $saleInvoices = SaleInvoiceDetail::getReportsAndInsightsStatement([], $filters);
        $company       = Company::find(Auth::user()->company_id);
        $fileName = 'Reports And Insights-' . ' From ' . $from_date . ' To ' . $to_date . '.xlsx';

        return Excel::download(new ReportAndInsightsExport($saleInvoices,$company), $fileName);
    }

    public function singleProductStatement(Request $request, $id = '')
    {
        $product = '';
        $id = !empty($id) ? $id : ($request->product_id ?? '');

        if (!empty($id) || !empty($request->product_id)) {
            $product = Product::find($id);
        }


        // if (!$product) {
        //     return redirect()->back()->with('error', 'Product not found.');
        // }

        $filters["from_date"]  = $from_date = $request->from_date ? date('Y-m-d', strtotime($request->from_date)) : date('Y-m-d');
        $filters["to_date"]    = $to_date = $request->to_date ? date('Y-m-d', strtotime($request->to_date)) : date('Y-m-d');
        $filters["product_id"] = $id;

        if ($request->ajax()) {
            $filters["company"]           = $company           = $request->company ?? '';
            $filters["search_account"]    = $search_account    = $request->search_account ?? '';
            $filters["created_by"]        = $created_by        = $request->created_by ?? '';
            $filters["voucher_type_code"] = $voucher_type_code = $request->voucher_type_code ?? '';

            $filters["transaction_mode"] = $transaction_mode = Account::select(['id', 'name', 'account_category_id'])
                ->with('chart_of_account_sub_type')
                ->where(function ($query) {
                    $query->whereHas('chart_of_account_sub_type', function ($accounting_group) {
                        $accounting_group->where('is_payment_mode', '1');
                    });
                })
                ->get()->pluck('id');

            $saleInvoices = SaleInvoiceDetail::getReportsAndInsightsStatement([], $filters);

            return view('invoice-formats.sales.single-product-statement.statement-table', compact([
                'voucher_type_code',
                'saleInvoices',
                'from_date',
                'to_date',
                'transaction_mode',
                'product',
            ]));
        }

        $company       = Company::find(Auth::user()->company_id);
        $companies     = Company::get();
        $voucher_type  = VoucherType::where('code', 'SALES')->where(['status' => 1])->first();
        $voucher_types = VoucherType::select('name', 'code')->where(['status' => 1])->get();

        return view('invoice-formats.sales.single-product-statement.statement', compact(
            'voucher_types',
            'companies',
            'company',
            'from_date',
            'to_date',
            'id',
            'product',
        ));
    }

    public function workBookRegister(Request $request)
    {
        if ($request->ajax()) {
            $from_date         = $request->from_date ? date('Y-m-d', strtotime($request->from_date)) : date('Y-m-d');
            $to_date           = $request->to_date ? date('Y-m-d', strtotime($request->to_date)) : date('Y-m-d');
            $company           = $request->company ?? '';
            $search_account    = $request->search_account ?? '';
            $created_by        = $request->created_by ?? '';

            $voucher_type_code = $request->voucher_type_code ?? '';

            $transaction_mode = Account::select(['id', 'name', 'account_category_id'])
                ->with('chart_of_account_sub_type')
                ->where(function ($query) {
                    $query->whereHas('chart_of_account_sub_type', function ($accounting_group) {
                        $accounting_group->where('is_payment_mode', '1');
                    });
                })
                ->get()->pluck('id');

            $columns = [
                'sale_invoices.id',
                'sale_invoices.invoice_date',
                'sale_invoices.main_invoice_no',
                'sale_invoices.patient_id',
                'sale_invoices.voucher_type_code',
                'sale_invoices.payment_mode_id',
                'accounts.name as patient_name',
                'accounts.code as patient_code',
                'refferal.name as refferal_name',
                'refferal.code as refferal_code',
                'account_titles.name as patient_title',
                'refferal_titles.name as refferal_title',
                'voucher_types.name as voucher_type',
            ];

            $saleInvoices = SaleInvoice::leftJoin('sale_others_transactions','sale_others_transactions.sale_invoice_id','sale_invoices.id')
                ->leftJoin('sale_invoice_details','sale_invoice_details.sale_invoice_id','sale_invoices.id')
                ->leftJoin('accounts','sale_invoices.patient_id','accounts.id')
                ->leftJoin('accounts as refferal','sale_invoices.refferal_lab_id','refferal.id')
                ->leftJoin('account_titles', 'account_titles.id', '=', 'accounts.account_title_id')
                ->leftJoin('account_titles as refferal_titles', 'refferal_titles.id', '=', 'refferal.account_title_id')
                ->leftJoin('voucher_types','sale_invoices.voucher_type_code','voucher_types.code')
                ->when(!empty($search_account), function($saleInvoices) use($search_account) {
                    $saleInvoices->where('accounts.name', 'LIKE', '%' . $search_account . '%');
                })
                ->when(!empty($voucher_type_code), function ($saleInvoices) use ($voucher_type_code) {
                    $saleInvoices->where('sale_invoices.voucher_type_code', '=', $voucher_type_code);
                })
                ->when(!empty($created_by), function ($saleInvoices) use ($created_by) {
                    $saleInvoices->where('sale_invoices.operator_id', '=', $created_by);
                })
                ->when(!empty($company), function ($saleInvoices) use ($company) {
                    $saleInvoices->where('sale_invoices.company_id', '=', $company);
                })
                ->when(!empty($from_date) && !empty($to_date), function ($query) use ($from_date, $to_date) {
                    $query->when($from_date == $to_date, function ($query2) use ($from_date) {
                        $query2->whereDate('sale_invoices.invoice_date', $from_date);
                    })
                        ->when($from_date != $to_date, function ($query2) use ($from_date, $to_date) {
                            $query2->whereBetween('sale_invoices.invoice_date', [$from_date, $to_date]);
                        });
                })
                ->select($columns)
                ->groupBy('sale_invoices.main_invoice_no')
                ->get()
                ->groupBy('id')
                ->map(function ($saleInvoices, $id) {
                    $details = SaleInvoiceDetail::select([
                            'sale_invoice_details.id',
                            'sale_invoice_details.total_amount',
                            'products.name as product',
                        ])
                        ->leftjoin('products','sale_invoice_details.product_id', '=', 'products.id')
                        ->where(['sale_invoice_id' => $id])
                        ->get();

                    return [
                        'details'      => $details,
                        'saleInvoices' => $saleInvoices[0]
                    ];
                });

            return view('invoice-formats.sales.work-book-register.statement-table', compact([
                'voucher_type_code',
                'saleInvoices',
                'from_date',
                'to_date',
                'transaction_mode',
            ]));
        }

        $company       = Company::find(Auth::user()->company_id);
        $companies     = Company::get();
        $voucher_type  = VoucherType::where('code', 'SALES')->where(['status' => 1])->first();
        $voucher_types = VoucherType::select('name', 'code')->where(['status' => 1])->get();

        return view('invoice-formats.sales.work-book-register.statement', compact(
            'voucher_types',
            'companies',
            'company',
        ));
    }
}
