<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Product;
use App\ProductPurchase;
use App\Product_Sale;
use App\ProductQuotation;
use App\Sale;
use App\Purchase;
use App\Quotation;
use App\Transfer;
use Illuminate\Support\Collection;
use App\Returns;
use App\ProductReturn;
use App\ReturnPurchase;
use App\ProductTransfer;
use App\PurchaseProductReturn;
use App\Payment;
use App\Warehouse;
use App\Product_Warehouse;
use App\Expense;
use App\Payroll;
use App\User;
use App\Unit;
use App\Customer;
use App\Supplier;
use App\Variant;
use App\Biller;
use App\ProductVariant;
use App\ProductWarranty;
use App\Warranty;
use DB;
use Carbon\Carbon;
use Auth;use App\PosSetting;use App\GiftCard;use App\Account;use App\GeneralSetting;
use Spatie\Permission\Models\Role;
use Spatie\Permission\Models\Permission;

class ReportController extends Controller
{

    public function productQuantityAlert()
    {
        $role = Role::find(Auth::user()->role_id);
        if($role->hasPermissionTo('product-qty-alert')){
            $lims_product_data = Product::select('name','code', 'image', 'qty', 'alert_quantity')->where('is_active', true)->whereColumn('alert_quantity', '>', 'qty')->get();
            return view('report.qty_alert_report', compact('lims_product_data'));
        }
        else
            return redirect()->back()->with('not_permitted', 'Sorry! You are not allowed to access this module');
    }

    public function warehouseStock()
    {
        $role = Role::find(Auth::user()->role_id);
        if($role->hasPermissionTo('warehouse-stock-report')){
            $total_item = DB::table('product_warehouse')
                        ->join('products', 'product_warehouse.product_id', '=', 'products.id')
                        ->where([
                            ['products.is_active', true],
                            ['product_warehouse.qty', '>' , 0]
                        ])->count();

            $total_qty = Product::where('is_active', true)->sum('qty');
            $total_price = DB::table('products')->where('is_active', true)->sum(DB::raw('price * qty'));
            $total_cost = DB::table('products')->where('is_active', true)->sum(DB::raw('cost * qty'));
            $lims_warehouse_list = Warehouse::where('is_active', true)->get();
            $warehouse_id = 0;
            return view('report.warehouse_stock', compact('total_item', 'total_qty', 'total_price', 'total_cost', 'lims_warehouse_list', 'warehouse_id'));
        }
        else
            return redirect()->back()->with('not_permitted', 'Sorry! You are not allowed to access this module');
    }

    public function warehouseStockById(Request $request)
    {
        $data = $request->all();
        if($data['warehouse_id'] == 0)
            return redirect()->back();

        $total_item = DB::table('product_warehouse')
                        ->join('products', 'product_warehouse.product_id', '=', 'products.id')
                        ->where([
                            ['products.is_active', true],
                            ['product_warehouse.qty', '>' , 0],
                            ['product_warehouse.warehouse_id', $data['warehouse_id']]
                        ])->count();
        $total_qty = DB::table('product_warehouse')
                        ->join('products', 'product_warehouse.product_id', '=', 'products.id')
                        ->where([
                            ['products.is_active', true],
                            ['product_warehouse.warehouse_id', $data['warehouse_id']]
                        ])->sum('product_warehouse.qty');
        $total_price = DB::table('product_warehouse')
                        ->join('products', 'product_warehouse.product_id', '=', 'products.id')
                        ->where([
                            ['products.is_active', true],
                            ['product_warehouse.warehouse_id', $data['warehouse_id']]
                        ])->sum(DB::raw('products.price * product_warehouse.qty'));
        $total_cost = DB::table('product_warehouse')
                        ->join('products', 'product_warehouse.product_id', '=', 'products.id')
                        ->where([
                            ['products.is_active', true],
                            ['product_warehouse.warehouse_id', $data['warehouse_id']]
                        ])->sum(DB::raw('products.cost * product_warehouse.qty'));
        $lims_warehouse_list = Warehouse::where('is_active', true)->get();
        $warehouse_id = $data['warehouse_id'];
        return view('report.warehouse_stock', compact('total_item', 'total_qty', 'total_price', 'total_cost', 'lims_warehouse_list', 'warehouse_id'));
    }

    public function dailySale($year, $month)
    {
        $role = Role::find(Auth::user()->role_id);
        if($role->hasPermissionTo('daily-sale')){
            $start = 1;
            $number_of_day = cal_days_in_month(CAL_GREGORIAN,$month,$year);
            while($start <= $number_of_day)
            {
                if($start < 10)
                    $date = $year.'-'.$month.'-0'.$start;
                else
                    $date = $year.'-'.$month.'-'.$start;
                $query1 = array(
                    'SUM(total_discount) AS total_discount',
                    'SUM(order_discount) AS order_discount',
                    'SUM(total_tax) AS total_tax',
                    'SUM(order_tax) AS order_tax',
                    'SUM(shipping_cost) AS shipping_cost',
                    'SUM(grand_total) AS grand_total'
                );
                $sale_data = Sale::whereDate('created_at', $date)->selectRaw(implode(',', $query1))->get();
                $total_discount[$start] = $sale_data[0]->total_discount;
                $order_discount[$start] = $sale_data[0]->order_discount;
                $total_tax[$start] = $sale_data[0]->total_tax;
                $order_tax[$start] = $sale_data[0]->order_tax;
                $shipping_cost[$start] = $sale_data[0]->shipping_cost;
                $grand_total[$start] = $sale_data[0]->grand_total;
                $start++;
            }
            $start_day = date('w', strtotime($year.'-'.$month.'-01')) + 1;
            $prev_year = date('Y', strtotime('-1 month', strtotime($year.'-'.$month.'-01')));
            $prev_month = date('m', strtotime('-1 month', strtotime($year.'-'.$month.'-01')));
            $next_year = date('Y', strtotime('+1 month', strtotime($year.'-'.$month.'-01')));
            $next_month = date('m', strtotime('+1 month', strtotime($year.'-'.$month.'-01')));
            $lims_warehouse_list = Warehouse::where('is_active', true)->get();
            $warehouse_id = 0;
            return view('report.daily_sale', compact('total_discount','order_discount', 'total_tax', 'order_tax', 'shipping_cost', 'grand_total', 'start_day', 'year', 'month', 'number_of_day', 'prev_year', 'prev_month', 'next_year', 'next_month', 'lims_warehouse_list', 'warehouse_id'));
        }
        else
            return redirect()->back()->with('not_permitted', 'Sorry! You are not allowed to access this module');
    }

    public function dailySaleByWarehouse(Request $request,$year,$month)
    {
        $data = $request->all();
        if($data['warehouse_id'] == 0)
            return redirect()->back();
        $start = 1;
        $number_of_day = cal_days_in_month(CAL_GREGORIAN,$month,$year);
        while($start <= $number_of_day)
        {
            if($start < 10)
                $date = $year.'-'.$month.'-0'.$start;
            else
                $date = $year.'-'.$month.'-'.$start;
            $query1 = array(
                'SUM(total_discount) AS total_discount',
                'SUM(order_discount) AS order_discount',
                'SUM(total_tax) AS total_tax',
                'SUM(order_tax) AS order_tax',
                'SUM(shipping_cost) AS shipping_cost',
                'SUM(grand_total) AS grand_total'
            );
            $sale_data = Sale::where('warehouse_id', $data['warehouse_id'])->whereDate('created_at', $date)->selectRaw(implode(',', $query1))->get();
            $total_discount[$start] = $sale_data[0]->total_discount;
            $order_discount[$start] = $sale_data[0]->order_discount;
            $total_tax[$start] = $sale_data[0]->total_tax;
            $order_tax[$start] = $sale_data[0]->order_tax;
            $shipping_cost[$start] = $sale_data[0]->shipping_cost;
            $grand_total[$start] = $sale_data[0]->grand_total;
            $start++;
        }
        $start_day = date('w', strtotime($year.'-'.$month.'-01')) + 1;
        $prev_year = date('Y', strtotime('-1 month', strtotime($year.'-'.$month.'-01')));
        $prev_month = date('m', strtotime('-1 month', strtotime($year.'-'.$month.'-01')));
        $next_year = date('Y', strtotime('+1 month', strtotime($year.'-'.$month.'-01')));
        $next_month = date('m', strtotime('+1 month', strtotime($year.'-'.$month.'-01')));
        $lims_warehouse_list = Warehouse::where('is_active', true)->get();
        $warehouse_id = $data['warehouse_id'];
        return view('report.daily_sale', compact('total_discount','order_discount', 'total_tax', 'order_tax', 'shipping_cost', 'grand_total', 'start_day', 'year', 'month', 'number_of_day', 'prev_year', 'prev_month', 'next_year', 'next_month', 'lims_warehouse_list', 'warehouse_id'));

    }

    public function dailyPurchase($year, $month)
    {
        $role = Role::find(Auth::user()->role_id);
        if($role->hasPermissionTo('daily-purchase')){
            $start = 1;
            $number_of_day = cal_days_in_month(CAL_GREGORIAN,$month,$year);
            while($start <= $number_of_day)
            {
                if($start < 10)
                    $date = $year.'-'.$month.'-0'.$start;
                else
                    $date = $year.'-'.$month.'-'.$start;
                $query1 = array(
                    'SUM(total_discount) AS total_discount',
                    'SUM(order_discount) AS order_discount',
                    'SUM(total_tax) AS total_tax',
                    'SUM(order_tax) AS order_tax',
                    'SUM(shipping_cost) AS shipping_cost',
                    'SUM(grand_total) AS grand_total'
                );
                $purchase_data = Purchase::whereDate('created_at', $date)->selectRaw(implode(',', $query1))->get();
                $total_discount[$start] = $purchase_data[0]->total_discount;
                $order_discount[$start] = $purchase_data[0]->order_discount;
                $total_tax[$start] = $purchase_data[0]->total_tax;
                $order_tax[$start] = $purchase_data[0]->order_tax;
                $shipping_cost[$start] = $purchase_data[0]->shipping_cost;
                $grand_total[$start] = $purchase_data[0]->grand_total;
                $start++;
            }
            $start_day = date('w', strtotime($year.'-'.$month.'-01')) + 1;
            $prev_year = date('Y', strtotime('-1 month', strtotime($year.'-'.$month.'-01')));
            $prev_month = date('m', strtotime('-1 month', strtotime($year.'-'.$month.'-01')));
            $next_year = date('Y', strtotime('+1 month', strtotime($year.'-'.$month.'-01')));
            $next_month = date('m', strtotime('+1 month', strtotime($year.'-'.$month.'-01')));
            $lims_warehouse_list = Warehouse::where('is_active', true)->get();
            $warehouse_id = 0;
            return view('report.daily_purchase', compact('total_discount','order_discount', 'total_tax', 'order_tax', 'shipping_cost', 'grand_total', 'start_day', 'year', 'month', 'number_of_day', 'prev_year', 'prev_month', 'next_year', 'next_month', 'lims_warehouse_list', 'warehouse_id'));
        }
        else
            return redirect()->back()->with('not_permitted', 'Sorry! You are not allowed to access this module');
    }

    public function dailyPurchaseByWarehouse(Request $request, $year, $month)
    {        
        $data = $request->all();
        if($data['warehouse_id'] == 0)
            return redirect()->back();
        $start = 1;
        $number_of_day = cal_days_in_month(CAL_GREGORIAN,$month,$year);
        while($start <= $number_of_day)
        {
            if($start < 10)
                $date = $year.'-'.$month.'-0'.$start;
            else
                $date = $year.'-'.$month.'-'.$start;
            $query1 = array(
                'SUM(total_discount) AS total_discount',
                'SUM(order_discount) AS order_discount',
                'SUM(total_tax) AS total_tax',
                'SUM(order_tax) AS order_tax',
                'SUM(shipping_cost) AS shipping_cost',
                'SUM(grand_total) AS grand_total'
            );
            $purchase_data = Purchase::where('warehouse_id', $data['warehouse_id'])->whereDate('created_at', $date)->selectRaw(implode(',', $query1))->get();
            $total_discount[$start] = $purchase_data[0]->total_discount;
            $order_discount[$start] = $purchase_data[0]->order_discount;
            $total_tax[$start] = $purchase_data[0]->total_tax;
            $order_tax[$start] = $purchase_data[0]->order_tax;
            $shipping_cost[$start] = $purchase_data[0]->shipping_cost;
            $grand_total[$start] = $purchase_data[0]->grand_total;
            $start++;
        }
        $start_day = date('w', strtotime($year.'-'.$month.'-01')) + 1;
        $prev_year = date('Y', strtotime('-1 month', strtotime($year.'-'.$month.'-01')));
        $prev_month = date('m', strtotime('-1 month', strtotime($year.'-'.$month.'-01')));
        $next_year = date('Y', strtotime('+1 month', strtotime($year.'-'.$month.'-01')));
        $next_month = date('m', strtotime('+1 month', strtotime($year.'-'.$month.'-01')));
        $lims_warehouse_list = Warehouse::where('is_active', true)->get();
        $warehouse_id = $data['warehouse_id'];

        return view('report.daily_purchase', compact('total_discount','order_discount', 'total_tax', 'order_tax', 'shipping_cost', 'grand_total', 'start_day', 'year', 'month', 'number_of_day', 'prev_year', 'prev_month', 'next_year', 'next_month', 'lims_warehouse_list', 'warehouse_id'));
    }

    public function monthlySale($year)
    {
        $role = Role::find(Auth::user()->role_id);
        if($role->hasPermissionTo('monthly-sale')){
            $start = strtotime($year .'-01-01');
            $end = strtotime($year .'-12-31');
            while($start <= $end)
            {
                $start_date = $year . '-'. date('m', $start).'-'.'01';
                $end_date = $year . '-'. date('m', $start).'-'.'31';

                $temp_total_discount = Sale::whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->sum('total_discount');
                $total_discount[] = number_format((float)$temp_total_discount, 0, '.', '');

                $temp_order_discount = Sale::whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->sum('order_discount');
                $order_discount[] = number_format((float)$temp_order_discount, 0, '.', '');

                $temp_total_tax = Sale::whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->sum('total_tax');
                $total_tax[] = number_format((float)$temp_total_tax, 0, '.', '');

                $temp_order_tax = Sale::whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->sum('order_tax');
                $order_tax[] = number_format((float)$temp_order_tax, 0, '.', '');

                $temp_shipping_cost = Sale::whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->sum('shipping_cost');
                $shipping_cost[] = number_format((float)$temp_shipping_cost, 0, '.', '');

                $temp_total = Sale::whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->sum('grand_total');
                $total[] = number_format((float)$temp_total, 0, '.', '');
                $start = strtotime("+1 month", $start);
            }
            $lims_warehouse_list = Warehouse::where('is_active',true)->get();
            $warehouse_id = 0;
            return view('report.monthly_sale', compact('year', 'total_discount', 'order_discount', 'total_tax', 'order_tax', 'shipping_cost', 'total', 'lims_warehouse_list', 'warehouse_id'));
        }
        else
            return redirect()->back()->with('not_permitted', 'Sorry! You are not allowed to access this module');
    }

    public function monthlySaleByWarehouse(Request $request, $year)
    {
        $data = $request->all();
        if($data['warehouse_id'] == 0)
            return redirect()->back();

        $start = strtotime($year .'-01-01');
        $end = strtotime($year .'-12-31');
        while($start <= $end)
        {
            $start_date = $year . '-'. date('m', $start).'-'.'01';
            $end_date = $year . '-'. date('m', $start).'-'.'31';

            $temp_total_discount = Sale::where('warehouse_id', $data['warehouse_id'])->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->sum('total_discount');
            $total_discount[] = number_format((float)$temp_total_discount, 0, '.', '');

            $temp_order_discount = Sale::where('warehouse_id', $data['warehouse_id'])->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->sum('order_discount');
            $order_discount[] = number_format((float)$temp_order_discount, 0, '.', '');

            $temp_total_tax = Sale::where('warehouse_id', $data['warehouse_id'])->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->sum('total_tax');
            $total_tax[] = number_format((float)$temp_total_tax, 0, '.', '');

            $temp_order_tax = Sale::where('warehouse_id', $data['warehouse_id'])->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->sum('order_tax');
            $order_tax[] = number_format((float)$temp_order_tax, 0, '.', '');

            $temp_shipping_cost = Sale::where('warehouse_id', $data['warehouse_id'])->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->sum('shipping_cost');
            $shipping_cost[] = number_format((float)$temp_shipping_cost, 0, '.', '');

            $temp_total = Sale::where('warehouse_id', $data['warehouse_id'])->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->sum('grand_total');
            $total[] = number_format((float)$temp_total, 0, '.', '');
            $start = strtotime("+1 month", $start);
        }
        $lims_warehouse_list = Warehouse::where('is_active',true)->get();
        $warehouse_id = $data['warehouse_id'];
        return view('report.monthly_sale', compact('year', 'total_discount', 'order_discount', 'total_tax', 'order_tax', 'shipping_cost', 'total', 'lims_warehouse_list', 'warehouse_id'));
    }

    public function monthlyPurchase($year)
    {
        $role = Role::find(Auth::user()->role_id);
        if($role->hasPermissionTo('monthly-purchase')){
            $start = strtotime($year .'-01-01');
            $end = strtotime($year .'-12-31');
            while($start <= $end)
            {
                $start_date = $year . '-'. date('m', $start).'-'.'01';
                $end_date = $year . '-'. date('m', $start).'-'.'31';

                $query1 = array(
                    'SUM(total_discount) AS total_discount',
                    'SUM(order_discount) AS order_discount',
                    'SUM(total_tax) AS total_tax',
                    'SUM(order_tax) AS order_tax',
                    'SUM(shipping_cost) AS shipping_cost',
                    'SUM(grand_total) AS grand_total'
                );
                $purchase_data = Purchase::whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->selectRaw(implode(',', $query1))->get();
                
                $total_discount[] = number_format((float)$purchase_data[0]->total_discount, 0, '.', '');
                $order_discount[] = number_format((float)$purchase_data[0]->order_discount, 0, '.', '');
                $total_tax[] = number_format((float)$purchase_data[0]->total_tax, 0, '.', '');
                $order_tax[] = number_format((float)$purchase_data[0]->order_tax, 0, '.', '');
                $shipping_cost[] = number_format((float)$purchase_data[0]->shipping_cost, 0, '.', '');
                $grand_total[] = number_format((float)$purchase_data[0]->grand_total, 0, '.', '');
                $start = strtotime("+1 month", $start);
            }
            $lims_warehouse_list = Warehouse::where('is_active', true)->get();
            $warehouse_id = 0;
            return view('report.monthly_purchase', compact('year', 'total_discount', 'order_discount', 'total_tax', 'order_tax', 'shipping_cost', 'grand_total', 'lims_warehouse_list', 'warehouse_id'));
        }
        else
            return redirect()->back()->with('not_permitted', 'Sorry! You are not allowed to access this module');
    }

    public function monthlyPurchaseByWarehouse(Request $request, $year)
    {
        $data = $request->all();
        if($data['warehouse_id'] == 0)
            return redirect()->back();

        $start = strtotime($year .'-01-01');
        $end = strtotime($year .'-12-31');
        while($start <= $end)
        {
            $start_date = $year . '-'. date('m', $start).'-'.'01';
            $end_date = $year . '-'. date('m', $start).'-'.'31';

            $query1 = array(
                'SUM(total_discount) AS total_discount',
                'SUM(order_discount) AS order_discount',
                'SUM(total_tax) AS total_tax',
                'SUM(order_tax) AS order_tax',
                'SUM(shipping_cost) AS shipping_cost',
                'SUM(grand_total) AS grand_total'
            );
            $purchase_data = Purchase::where('warehouse_id', $data['warehouse_id'])->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->selectRaw(implode(',', $query1))->get();
            
            $total_discount[] = number_format((float)$purchase_data[0]->total_discount, 0, '.', '');
            $order_discount[] = number_format((float)$purchase_data[0]->order_discount, 0, '.', '');
            $total_tax[] = number_format((float)$purchase_data[0]->total_tax, 0, '.', '');
            $order_tax[] = number_format((float)$purchase_data[0]->order_tax, 0, '.', '');
            $shipping_cost[] = number_format((float)$purchase_data[0]->shipping_cost, 0, '.', '');
            $grand_total[] = number_format((float)$purchase_data[0]->grand_total, 0, '.', '');
            $start = strtotime("+1 month", $start);
        }
        $lims_warehouse_list = Warehouse::where('is_active', true)->get();
        $warehouse_id = $data['warehouse_id'];
        return view('report.monthly_purchase', compact('year', 'total_discount', 'order_discount', 'total_tax', 'order_tax', 'shipping_cost', 'grand_total', 'lims_warehouse_list', 'warehouse_id'));
    }

    public function bestSeller()
    {
        $role = Role::find(Auth::user()->role_id);
        if($role->hasPermissionTo('best-seller')){
            $start = strtotime(date("Y-m", strtotime("-2 months")).'-01');
            $end = strtotime(date("Y").'-'.date("m").'-31');
            
            while($start <= $end)
            {
                $start_date = date("Y-m", $start).'-'.'01';
                $end_date = date("Y-m", $start).'-'.'31';

                $best_selling_qty = Product_Sale::select(DB::raw('product_id, sum(qty) as sold_qty'))->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->groupBy('product_id')->orderBy('sold_qty', 'desc')->take(1)->get();
                if(!count($best_selling_qty)){
                    $product[] = '';
                    $sold_qty[] = 0;
                }
                foreach ($best_selling_qty as $best_seller) {
                    $product_data = Product::find($best_seller->product_id);
                    $product[] = $product_data->name.': '.$product_data->code;
                    $sold_qty[] = $best_seller->sold_qty;
                }
                $start = strtotime("+1 month", $start);
            }
            $start_month = date("F Y", strtotime('-2 month'));
            $lims_warehouse_list = Warehouse::where('is_active', true)->get();
            $warehouse_id = 0;
            return view('report.best_seller', compact('product', 'sold_qty', 'start_month', 'lims_warehouse_list', 'warehouse_id'));
        }
        else
            return redirect()->back()->with('not_permitted', 'Sorry! You are not allowed to access this module');
    }

    public function bestSellerByWarehouse(Request $request)
    {
        $data = $request->all();
        if($data['warehouse_id'] == 0)
            return redirect()->back();

        $start = strtotime(date("Y-m", strtotime("-2 months")).'-01');
        $end = strtotime(date("Y").'-'.date("m").'-31');

        while($start <= $end)
        {
            $start_date = date("Y-m", $start).'-'.'01';
            $end_date = date("Y-m", $start).'-'.'31';

            $best_selling_qty = DB::table('sales')
                                ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')->select(DB::raw('product_sales.product_id, sum(product_sales.qty) as sold_qty'))->where('sales.warehouse_id', $data['warehouse_id'])->whereDate('sales.created_at', '>=' , $start_date)->whereDate('sales.created_at', '<=' , $end_date)->groupBy('product_id')->orderBy('sold_qty', 'desc')->take(1)->get();
                                
            if(!count($best_selling_qty)) {
                $product[] = '';
                $sold_qty[] = 0;
            }
            foreach ($best_selling_qty as $best_seller) {
                $product_data = Product::find($best_seller->product_id);
                $product[] = $product_data->name.': '.$product_data->code;
                $sold_qty[] = $best_seller->sold_qty;
            }
            $start = strtotime("+1 month", $start);
        }
        $start_month = date("F Y", strtotime('-2 month'));
        $lims_warehouse_list = Warehouse::where('is_active', true)->get();
        $warehouse_id = $data['warehouse_id'];
        return view('report.best_seller', compact('product', 'sold_qty', 'start_month', 'lims_warehouse_list', 'warehouse_id'));
    }

    public function profitLoss(Request $request)
    {
        $start_date = $request['start_date'];
        $end_date = $request['end_date'];
        $query1 = array(
            'SUM(grand_total) AS grand_total',
            'SUM(paid_amount) AS paid_amount',
            'SUM(total_tax + order_tax) AS tax'
        );
        $query2 = array(
            'SUM(grand_total) AS grand_total',
            'SUM(total_tax + order_tax) AS tax'
        );
        $purchase = Purchase::whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->selectRaw(implode(',', $query1))->get();
        $total_purchase = Purchase::whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->count();
        $sale = Sale::whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->selectRaw(implode(',', $query1))->get();
        $total_sale = Sale::whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->count();
        $return = Returns::whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->selectRaw(implode(',', $query2))->get();
        $total_return = Returns::whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->count();
        $purchase_return = ReturnPurchase::whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->selectRaw(implode(',', $query2))->get();
        $total_purchase_return = ReturnPurchase::whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->count();
        $expense = Expense::whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->sum('amount');
        $total_expense = Expense::whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->count();
        $payroll = Payroll::whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->sum('amount');
        $total_payroll = Payroll::whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->count();
        $total_item = DB::table('product_warehouse')
                    ->join('products', 'product_warehouse.product_id', '=', 'products.id')
                    ->where([
                        ['products.is_active', true],
                        ['product_warehouse.qty', '>' , 0]
                    ])->count();
        $payment_recieved_number = DB::table('payments')->whereNotNull('sale_id')->whereDate('created_at', '>=' , $start_date)
            ->whereDate('created_at', '<=' , $end_date)->count();
        $payment_recieved = DB::table('payments')->whereNotNull('sale_id')->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->sum('payments.amount');
        $credit_card_payment_sale = DB::table('payments')
                            ->where('paying_method', 'Credit Card')
                            ->whereNotNull('payments.sale_id')
                            ->whereDate('payments.created_at', '>=' , $start_date)
                            ->whereDate('payments.created_at', '<=' , $end_date)->sum('payments.amount');
        $cheque_payment_sale = DB::table('payments')
                            ->where('paying_method', 'Cheque')
                            ->whereNotNull('payments.sale_id')
                            ->whereDate('payments.created_at', '>=' , $start_date)
                            ->whereDate('payments.created_at', '<=' , $end_date)->sum('payments.amount');
        $gift_card_payment_sale = DB::table('payments')
                            ->where('paying_method', 'Gift Card')
                            ->whereNotNull('sale_id')
                            ->whereDate('created_at', '>=' , $start_date)
                            ->whereDate('created_at', '<=' , $end_date)
                            ->sum('amount');
        $paypal_payment_sale = DB::table('payments')
                            ->where('paying_method', 'Paypal')
                            ->whereNotNull('sale_id')
                            ->whereDate('created_at', '>=' , $start_date)
                            ->whereDate('created_at', '<=' , $end_date)
                            ->sum('amount');
        $deposit_payment_sale = DB::table('payments')
                            ->where('paying_method', 'Deposit')
                            ->whereNotNull('sale_id')
                            ->whereDate('created_at', '>=' , $start_date)
                            ->whereDate('created_at', '<=' , $end_date)
                            ->sum('amount');
        $cash_payment_sale =  $payment_recieved - $credit_card_payment_sale - $cheque_payment_sale - $gift_card_payment_sale - $paypal_payment_sale - $deposit_payment_sale;
        $payment_sent_number = DB::table('payments')->whereNotNull('purchase_id')->whereDate('created_at', '>=' , $start_date)
            ->whereDate('created_at', '<=' , $end_date)->count();
        $payment_sent = DB::table('payments')->whereNotNull('purchase_id')->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->sum('payments.amount');
        $credit_card_payment_purchase = DB::table('payments')
                            ->where('paying_method', 'Gift Card')
                            ->whereNotNull('payments.purchase_id')
                            ->whereDate('payments.created_at', '>=' , $start_date)
                            ->whereDate('payments.created_at', '<=' , $end_date)->sum('payments.amount');
        $cheque_payment_purchase = DB::table('payments')
                            ->where('paying_method', 'Cheque')
                            ->whereNotNull('payments.purchase_id')
                            ->whereDate('payments.created_at', '>=' , $start_date)
                            ->whereDate('payments.created_at', '<=' , $end_date)->sum('payments.amount');
        $cash_payment_purchase =  $payment_sent - $credit_card_payment_purchase - $cheque_payment_purchase;
        $lims_warehouse_all = Warehouse::where('is_active',true)->get();
        $warehouse_name = [];
        foreach ($lims_warehouse_all as $warehouse) {
            $warehouse_name[] = $warehouse->name;
            $warehouse_sale[] = Sale::where('warehouse_id', $warehouse->id)->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->selectRaw(implode(',', $query2))->get();
            $warehouse_purchase[] = Purchase::where('warehouse_id', $warehouse->id)->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->selectRaw(implode(',', $query2))->get();
            $warehouse_return[] = Returns::where('warehouse_id', $warehouse->id)->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->selectRaw(implode(',', $query2))->get();
            $warehouse_purchase_return[] = ReturnPurchase::where('warehouse_id', $warehouse->id)->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->selectRaw(implode(',', $query2))->get();
            $warehouse_expense[] = Expense::where('warehouse_id', $warehouse->id)->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->sum('amount');
        }

        return view('report.profit_loss', compact('purchase', 'total_purchase', 'sale', 'total_sale', 'return', 'purchase_return', 'total_return', 'total_purchase_return', 'expense', 'payroll', 'total_expense', 'total_payroll', 'payment_recieved', 'payment_recieved_number', 'cash_payment_sale', 'cheque_payment_sale', 'credit_card_payment_sale', 'gift_card_payment_sale', 'paypal_payment_sale', 'deposit_payment_sale', 'payment_sent', 'payment_sent_number', 'cash_payment_purchase', 'cheque_payment_purchase', 'credit_card_payment_purchase', 'warehouse_name', 'warehouse_sale', 'warehouse_purchase', 'warehouse_return', 'warehouse_purchase_return', 'warehouse_expense', 'start_date', 'end_date'));
    }

    public function productReport(Request $request)
    {
        $data = $request->all();
        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
        $warehouse_id = $data['warehouse_id'];
        $product_id = [];
        $product_code = [];
        $variant_id = [];
        $product_name = [];
        $product_qty = [];
        $warehouse_name = "Tất cả kho";
        if($warehouse_id != 0){
            $warehouse_name = Warehouse::find($warehouse_id)->name;
        }
        
        $lims_product_all = Product::select('id', 'code', 'name', 'qty', 'is_variant')
            ->where('is_active', true)
            ->get();
            
        $lims_product_data = [];
        foreach ($lims_product_all as $product) {
            if($product->is_variant){
                $product_variant_data = ProductVariant::where('product_id', $product->id)->get();
                foreach ($product_variant_data as $key => $product_variant) {
                    $variant_data = Variant::find($product_variant->variant_id);
                    $product_variant->product_name = $variant_data->name;
                    $product_variant->product_code = $product_variant->item_code;
                    array_push($lims_product_data, $product_variant);
                }
            } else {
                $product->variant_id = null;
                $product->product_id = $product->id;
                $product->product_name = $product->name;
                $product->product_code = $product->code;
                array_push($lims_product_data, $product);
            }
        }

        $lims_data = array();
        foreach ($lims_product_data as $key => $product) { 
            $product_purchase_data = [];
            $product_sale_data = [];
            $purchased_cost = 0;

            $begin_product_purchase_data = [];
            $begin_product_sale_data = [];
            $begin_purchased_cost = 0;
            $begin_sold_price = 0;

            if($warehouse_id == 0){
                $begin_product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.qty', 'product_purchases.purchase_unit_id', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                    ])
                    ->whereNull('purchases.method')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->orderBy('purchases.created_at', 'desc')
                    ->get();
                
                $begin_purchased_cost = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                    ])
                    ->whereNull('purchases.method')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('total');

                $begin_product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.sale_unit_id', 'product_sales.qty', 'sales.created_at')
                    ->where([
                        ['product_sales.product_id', $product->product_id],
                        ['product_sales.variant_id', $product->variant_id]
                    ])
                    ->whereNull('sales.method')
                    ->whereDate('sales.created_at','<', $start_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();

                $begin_sold_price = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at')
                    ->where([
                        ['product_sales.product_id', $product->product_id],
                        ['product_sales.variant_id', $product->variant_id]
                    ])
                    ->whereNull('sales.method')                  
                    ->whereDate('sales.created_at','<', $start_date)
                    ->sum('total');

                $product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.purchase_unit_id', 'product_purchases.qty', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                    ])
                    ->whereNull('purchases.method')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->orderBy('purchases.created_at', 'desc')
                    ->get();
               
                $purchased_cost = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                    ])
                    ->whereNull('purchases.method')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->sum('total');

                $product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.sale_unit_id', 'product_sales.qty', 'sales.created_at')
                    ->where([
                        ['product_sales.product_id', $product->product_id],
                        ['product_sales.variant_id', $product->variant_id]
                    ])
                    ->whereNull('sales.method')
                    ->whereDate('sales.created_at','>=', $start_date)
                    ->whereDate('sales.created_at','<=', $end_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();

            } else {
                $begin_product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.purchase_unit_id', 'product_purchases.qty', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->whereNull('purchases.method')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->orderBy('purchases.created_at', 'desc')
                    ->get();
            
                $begin_purchased_cost = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->whereNull('purchases.method')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('total');

                $begin_product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.sale_unit_id', 'product_sales.qty', 'sales.created_at')
                    ->where([
                        ['product_sales.product_id', $product->product_id],
                        ['product_sales.variant_id', $product->variant_id],
                        ['sales.warehouse_id', $warehouse_id],

                    ])
                    ->whereNull('sales.method')
                    ->whereDate('sales.created_at','<', $start_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();

                $begin_sold_price = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at')
                    ->where([
                        ['product_sales.product_id', $product->product_id],
                        ['product_sales.variant_id', $product->variant_id],
                        ['sales.warehouse_id', $warehouse_id],
                    ])
                    ->whereNull('sales.method')
                    ->whereDate('sales.created_at','<', $start_date)
                    ->sum('total');

                $product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.purchase_unit_id', 'product_purchases.qty', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->whereNull('purchases.method')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->orderBy('purchases.created_at', 'desc')
                    ->get();
            
                $purchased_cost = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->whereNull('purchases.method')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->sum('total');

                $product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.sale_unit_id', 'product_sales.qty', 'sales.created_at')
                    ->where([
                        ['product_sales.product_id', $product->product_id],
                        ['product_sales.variant_id', $product->variant_id],
                        ['sales.warehouse_id', $warehouse_id],

                    ])
                    ->whereNull('sales.method')
                    ->whereDate('sales.created_at','>=', $start_date)
                    ->whereDate('sales.created_at','<=', $end_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();

            }

            $purchased_qty = 0;
            $unit_name = '';
            $sold_qty = 0;
            $created_at = '';
            $profit_unit = 0;
            $total_purchased_amount = 0;

            $begin_purchased_qty = 0;
            $begin_sold_qty = 0;
            $begin_inventory_qty = 0;
            $begin_inventory_amount = 0;
            $end_inventory_qty = 0;
            $end_inventory_amount = 0;
            
            foreach ($product_purchase_data as $product_purchase) {
                $created_at = $product_purchase->created_at;
                $unit = DB::table('units')->find($product_purchase->purchase_unit_id);
                if($unit->operator == '*'){
                    $purchased_qty += $product_purchase->qty * $unit->operation_value;
                    $unit_name = $unit->unit_name;
                }
                elseif($unit->operator == '/'){
                    $purchased_qty += $product_purchase->qty / $unit->operation_value;
                    $unit_name = $unit->unit_name;
                }
            }
            
            foreach ($product_sale_data as $product_sale) {
                $created_at = $product_sale->created_at;
                $unit = DB::table('units')->find($product_sale->sale_unit_id);
                if($unit){
                    if($unit->operator == '*'){
                        $sold_qty += $product_sale->qty * $unit->operation_value;
                        $unit_name = $unit->unit_name;
                    }
                        
                    elseif($unit->operator == '/'){
                        $sold_qty += $product_sale->qty / $unit->operation_value;
                        $unit_name = $unit->unit_name;
                    }
                }
                else
                    $sold_qty += $product_sale->qty;
            }
            
            if(!empty($begin_product_purchase_data)){
                foreach ($begin_product_purchase_data as $product_purchase) {
                    $unit = DB::table('units')->find($product_purchase->purchase_unit_id);
                    if($unit->operator == '*'){
                        $begin_purchased_qty += $product_purchase->qty * $unit->operation_value;
                    }
                    elseif($unit->operator == '/'){
                        $begin_purchased_qty += $product_purchase->qty / $unit->operation_value;
                    }
                }
            }
            
            if(!empty($begin_product_sale_data)){
                foreach ($begin_product_sale_data as $product_sale) {
                    $unit = DB::table('units')->find($product_sale->sale_unit_id);
                    if($unit){
                        if($unit->operator == '*'){
                            $begin_sold_qty += $product_sale->qty * $unit->operation_value;
                        }
                            
                        elseif($unit->operator == '/'){
                            $begin_sold_qty += $product_sale->qty / $unit->operation_value;
                        }
                    }
                    else
                        $begin_sold_qty += $product_sale->qty;
                }
            }

            if ($purchased_qty){
                $purchased_cost_qty = $purchased_cost/ $purchased_qty;
            } else {
                $purchased_cost_qty = $purchased_cost;
            }

            $total_purchased_amount = $purchased_cost_qty * $purchased_qty;          
            if(!$begin_purchased_cost){
                $begin_purchased_cost = 0;
            }
            if ($begin_purchased_qty){
                $begin_purchased_cost_qty = $begin_purchased_cost/ $begin_purchased_qty;
            } else {
                $begin_purchased_cost_qty = $begin_purchased_cost;
            }

            $begin_inventory_qty = $begin_purchased_qty - $begin_sold_qty;
            $begin_inventory_amount = $begin_purchased_cost_qty * $begin_inventory_qty;

            
            $unit_price_buy = 0;
            if($begin_inventory_qty + $purchased_qty > 0){
                $unit_price_buy = ($begin_inventory_amount + $total_purchased_amount) / ($begin_inventory_qty + $purchased_qty);
            }

            $total_sold_price = $unit_price_buy * $sold_qty;

            $end_inventory_qty = $begin_inventory_qty +  $purchased_qty - $sold_qty;
            $end_inventory_amount = $end_inventory_qty * $unit_price_buy;

            if($created_at){
                $modified_product = (object) [
                    'created_at' => $created_at,
                    'unit_name' => $unit_name,
                    'product_id' => $product->product_id,
                    'variant_id' => $product->variant_id,
                    'product_code' => $product->product_code,
                    'product_name' => $product->product_name,

                    'begin_inventory_qty' => number_format((float) $begin_inventory_qty, 0, '.', '.'),
                    'begin_inventory_amount' => number_format((float) $begin_inventory_amount, 0, '.', '.'),
                    
                    'purchased_qty' => number_format((float) $purchased_qty, 0, '.', '.'),
                    'total_purchased_amount' => number_format((float) $total_purchased_amount, 0, '.', '.'),
                    
                    'sold_qty' => number_format((float) $sold_qty, 0, '.', '.'),
                    'total_sold_price' => number_format((float) $total_sold_price, 0, '.', '.'),
                    
                    'end_inventory_qty' => number_format((float) $end_inventory_qty, 0, '.', '.'),
                    'end_inventory_amount' => number_format((float) $end_inventory_amount, 0, '.', '.'),
                    
                    'unit_price_buy' => number_format((float) ($unit_price_buy), 0, '.', '.'),
                    'sold_price' => number_format((float) $purchased_cost_qty, 0, '.', '.'),
                ];
    
                array_push($lims_data, $modified_product);
                
            }
        }
        
        $collection = new Collection($lims_data);
        $lims_data = $collection->sortBy('created_at')->values()->all();
        $lims_warehouse_list = Warehouse::where('is_active', true)->get();
        return view('report.product_report',compact('lims_data', 'start_date', 'end_date', 'lims_warehouse_list', 'warehouse_id', 'warehouse_name'));
    }

    public function productReportID($id, $variant_id, $start_date, $end_date, $warehouse_id)
    {
        if($warehouse_id != 0){
            if( $variant_id != -1){
                $lims_product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id', 'purchases.content', 'purchases.inventory_receiving_voucher')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', $variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->whereNull('purchases.method')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->orderBy('purchases.created_at', 'asc')
                    ->get();
                $total_begin_qty_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', $variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->whereNull('purchases.method')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('qty');
                $total_begin_amount_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', $variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->whereNull('purchases.method')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('total');
                $lims_product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id', 'sales.content', 'sales.inventory_delivery_voucher')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', $variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->whereNull('sales.method')
                    ->whereDate('sales.created_at','>=', $start_date)
                    ->whereDate('sales.created_at','<=', $end_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();
                $total_begin_qty_sales = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', $variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->whereNull('sales.method')
                    ->whereDate('sales.created_at','<', $start_date)
                    ->sum('qty');
                
            } else {
                $lims_product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id', 'purchases.content', 'purchases.inventory_receiving_voucher')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', NULL],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->whereNull('purchases.method')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->orderBy('purchases.created_at', 'asc')
                    ->get();
                $total_begin_qty_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', NULL],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->whereNull('purchases.method')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('qty');
                $total_begin_amount_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', NULL],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->whereNull('purchases.method')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('total');
                $lims_product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id', 'sales.content', 'sales.inventory_delivery_voucher')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', NULL],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->whereNull('sales.method')
                    ->whereDate('sales.created_at','>=', $start_date)
                    ->whereDate('sales.created_at','<=', $end_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();
                $total_begin_qty_sales = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', NULL],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->whereNull('sales.method')
                    ->whereDate('sales.created_at','<', $start_date)
                    ->sum('qty');
                
            }
        } else {
            if( $variant_id != -1){
                $lims_product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id', 'purchases.content', 'purchases.inventory_receiving_voucher')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', $variant_id],
                    ])
                    ->whereNull('purchases.method')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->orderBy('purchases.created_at', 'asc')
                    ->get();
                $total_begin_qty_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', $variant_id],
                    ])
                    ->whereNull('purchases.method')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('qty');
                $total_begin_amount_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', $variant_id],
                    ])
                    ->whereNull('purchases.method')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('total');
                $lims_product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id', 'sales.content', 'sales.inventory_delivery_voucher')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', $variant_id],
                    ])
                    ->whereNull('sales.method')
                    ->whereDate('sales.created_at','>=', $start_date)
                    ->whereDate('sales.created_at','<=', $end_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();
                $total_begin_qty_sales = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', $variant_id],
                    ])
                    ->whereNull('sales.method')
                    ->whereDate('sales.created_at','<', $start_date)
                    ->sum('qty');
                
            } else {
                $lims_product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id', 'purchases.content', 'purchases.inventory_receiving_voucher')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', NULL],
                    ])
                    ->whereNull('purchases.method')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->orderBy('purchases.created_at', 'asc')
                    ->get();
                $total_begin_qty_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', NULL],
                    ])
                    ->whereNull('purchases.method')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('qty');
                $total_begin_amount_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', NULL],
                    ])
                    ->whereNull('purchases.method')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('total');
                $lims_product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id', 'sales.content', 'sales.inventory_delivery_voucher')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', NULL],
                    ])
                    ->whereNull('sales.method')
                    ->whereDate('sales.created_at','>=', $start_date)
                    ->whereDate('sales.created_at','<=', $end_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();
                $total_begin_qty_sales = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', NULL],
                    ])
                    ->whereNull('sales.method')
                    ->whereDate('sales.created_at','<', $start_date)
                    ->sum('qty');
               
            }
        }
        
        foreach ($lims_product_purchase_data as $key => $product_purchase_data) {
            $purchase = Purchase::find($product_purchase_data->purchase_id);
            $product = Product::find($product_purchase_data->product_id);
            
            if($purchase){
                $purchase_customer = Customer::find($purchase->user_id);
                $purchase_warehouse = Warehouse::find($purchase->warehouse_id);
                $purchase_supplier = Supplier::find($purchase->supplier_id);
            }

            $unit_data = Unit::find($product_purchase_data->purchase_unit_id);
            if($unit_data){
                $unit = $unit_data->unit_code;
            }
            else
                $unit = '';
            
            $product_report[0][$key] = '[' . $purchase_customer->name .'] ' . $purchase_customer->company_name ;
            $product_report[1][$key] = $purchase_warehouse == null ? '' : $purchase_warehouse->name;
            // $product_report[2][$key] = $purchase_supplier == null ? '' : $purchase_supplier->name;
            $product_report[2][$key] = $purchase->content  == null ? '' : $purchase->content;
            $product_report[3][$key] = $product_purchase_data->tax;
            $product_report[4][$key] = $product_purchase_data->qty;
            $product_report[5][$key] = $product_purchase_data->tax_rate;
            $product_report[6][$key] = $product_purchase_data->discount;
            $product_report[7][$key] = $product_purchase_data->total;
            $product_report[8][$key] = $purchase->created_at;
            $product_report[9][$key] = $product_purchase_data->invoice_number;
            $product_report[10][$key] = $product_purchase_data->serial_number;
            $product_report[11][$key] = $product_purchase_data->lot_number;
            $product_report[12][$key] = $product_purchase_data->batch_number;
            $product_report[13][$key] = $product_purchase_data->tr_number;
            $product_report[14][$key] = $unit;
            $product_report[42][$key] = $product_purchase_data->total/$product_purchase_data->qty;

        }

        foreach ($lims_product_sale_data as $key => $product_sale_data) {
            $sale = Sale::find($product_sale_data->sale_id);  
            $product = Product::find($product_sale_data->product_id);  
                    
            if($sale){
                $sale_warehouse = Warehouse::find($sale->warehouse_id);
                $sale_customer = Customer::find($sale->customer_id);
                $sale_biller = Biller::find($sale->biller_id);
            }
            $unit_data = Unit::find($product_sale_data->sale_unit_id);
            if($unit_data){
                $unit = $unit_data->unit_code;
            }
            else
                $unit = '';

            $product_report[15][$key] = $sale->id;
            $product_report[16][$key] = $sale_warehouse == null ? '' : $sale_warehouse->name;
            $product_report[17][$key] = $sale_customer == null ? '' : $sale_customer->name;
            $product_report[18][$key] = $product_sale_data->tax;
            $product_report[19][$key] = $product_sale_data->qty;
            $product_report[20][$key] = $product_sale_data->tax_rate;
            $product_report[21][$key] = $product_sale_data->discount;
            $product_report[22][$key] = $product_sale_data->total;
            $product_report[23][$key] = $sale->created_at;
            $product_report[24][$key] = $sale->sale_note;
            $product_report[25][$key] = $sale_biller == null ? '' : $sale_biller->name ;
            $product_report[26][$key] = $unit;
            $product_report[27][$key] = $product_sale_data->total/$product_sale_data->qty;
            $product_report[28][$key] = $product_sale_data->invoice_number;
            $product_report[29][$key] = $product_sale_data->serial_number;
            $product_report[30][$key] = $product_sale_data->batch_number;
            $product_report[31][$key] = $product_sale_data->tr_number;
        }

        $begin_qty = $total_begin_qty_purchases - $total_begin_qty_sales;
        $begin_total = 0;
        if($total_begin_qty_purchases > 0){
            $begin_total = $begin_qty * ($total_begin_amount_purchases / $total_begin_qty_purchases);
        }

        // Tạo mảng kết quả để lưu các object đã được kết hợp
        $resultArray = [];

        // Lặp qua mảng A và thực hiện kết hợp
        foreach ($lims_product_purchase_data as $itemA) {
            $unit = "";
            $unit_data = Unit::find($itemA->purchase_unit_id);
            if($unit_data){
                $unit = $unit_data->unit_code;
            }
            $mergedItem = (object)[
                'created_at' => $itemA->created_at, 
                'quantity_buy' => $itemA->qty, 
                'total_buy' => $itemA->total,
                'quantity_sale' => 0,
                'total_sale' => 0,
                'content' => $itemA->content ?? "",
                'document_number' => $itemA->inventory_receiving_voucher ?? '',
                'unit' => $unit,
                'unit_price' => $itemA->net_unit_cost,
            ];
            
            // Tìm ngày trong mảng B
            foreach ($lims_product_sale_data as $itemB) {
                if ($itemB->created_at === $itemA->created_at) {
                    $mergedItem->quantity_sale = $itemB->qty;
                    $mergedItem->total_sale = $itemB->total;
                    $mergedItem->content = $itemB->content ?? "";
                    $mergedItem->document_number = $itemB->inventory_delivery_voucher ?? '';
                    $mergedItem->unit_price = $itemB->net_unit_price;
                    break;
                }
            }

            $resultArray[] = $mergedItem;
        }

        // Kiểm tra các ngày trong mảng B có trong mảng A không và thêm vào kết quả nếu chưa có
        foreach ($lims_product_sale_data as $itemB) {
            $found = false;
            foreach ($resultArray as $itemResult) {
                if ($itemResult->created_at === $itemB->created_at) {
                    $found = true;
                    break;
                }
            }

            if (!$found) {
                $unit = "";
                $unit_data = Unit::find($itemB->sale_unit_id);
                if($unit_data){
                    $unit = $unit_data->unit_code;
                }
                $resultArray[] = (object)[
                    'created_at' => $itemB->created_at, 
                    'quantity_buy' => 0, 
                    'total_buy' => 0, 
                    'quantity_sale' => $itemB->qty,
                    'total_sale' => $itemB->total,
                    'content' => $itemB->content ?? "",
                    'document_number' => $itemB->inventory_delivery_voucher ?? '',
                    'unit' => $unit,
                    'unit_price' => $itemB->net_unit_price
                ];
            }
        }

        // Sắp xếp kết quả theo ngày
        usort($resultArray, function ($a, $b) {
            return strtotime($a->created_at) - strtotime($b->created_at);
        });

        foreach ($resultArray as $key => $product_data) {
            $product_report[32][$key] = $product_data->created_at;
            $product_report[33][$key] = $product_data->quantity_buy;
            $product_report[34][$key] = $product_data->total_buy;
            $product_report[35][$key] = $product_data->quantity_sale;
            $product_report[36][$key] = $product_data->total_sale;
            $product_report[37][$key] = $product_data->content;
            $product_report[38][$key] = $product_data->unit;
            $product_report[39][$key] = $product_data->unit_price;
            $product_report[40][$key] = $begin_qty;
            $product_report[41][$key] = $begin_total;
            $product_report[43][$key] = $product_data->document_number;

        }

        return $product_report;
    }

    public function productLendReport(Request $request)
    {
        $data = $request->all();
        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
        $warehouse_id = $data['warehouse_id'];
        $product_id = [];
        $product_code = [];
        $variant_id = [];
        $product_name = [];
        $product_qty = [];
        $warehouse_name = "Tất cả kho";
        if($warehouse_id != 0){
            $warehouse_name = Warehouse::find($warehouse_id)->name;
        }
        
        $lims_product_all = Product::select('id', 'code', 'name', 'qty', 'is_variant')
            ->where('is_active', true)
            ->get();
            
        $lims_product_data = [];
        foreach ($lims_product_all as $product) {
            if($product->is_variant){
                $product_variant_data = ProductVariant::where('product_id', $product->id)->get();
                foreach ($product_variant_data as $key => $product_variant) {
                    $variant_data = Variant::find($product_variant->variant_id);
                    $product_variant->product_name = $variant_data->name;
                    $product_variant->product_code = $product_variant->item_code;
                    array_push($lims_product_data, $product_variant);
                }
            } else {
                $product->variant_id = null;
                $product->product_id = $product->id;
                $product->product_name = $product->name;
                $product->product_code = $product->code;
                array_push($lims_product_data, $product);
            }
        }

        $lims_data = array();
        foreach ($lims_product_data as $key => $product) { 
            $product_purchase_data = [];
            $product_sale_data = [];
            $purchased_cost = 0;

            $begin_product_purchase_data = [];
            $begin_product_sale_data = [];
            $begin_purchased_cost = 0;
            $begin_sold_price = 0;

            if($warehouse_id == 0){
                $begin_product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.qty', 'product_purchases.purchase_unit_id', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->orderBy('purchases.created_at', 'desc')
                    ->get();
                
                $begin_purchased_cost = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('total');

                $begin_product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.sale_unit_id', 'product_sales.qty', 'sales.created_at')
                    ->where([
                        ['product_sales.product_id', $product->product_id],
                        ['product_sales.variant_id', $product->variant_id]
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','<', $start_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();

                $begin_sold_price = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at')
                    ->where([
                        ['product_sales.product_id', $product->product_id],
                        ['product_sales.variant_id', $product->variant_id]
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','<', $start_date)
                    ->sum('total');

                $product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.purchase_unit_id', 'product_purchases.qty', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->orderBy('purchases.created_at', 'desc')
                    ->get();
               
                $purchased_cost = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->sum('total');

                $product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.sale_unit_id', 'product_sales.qty', 'sales.created_at')
                    ->where([
                        ['product_sales.product_id', $product->product_id],
                        ['product_sales.variant_id', $product->variant_id]
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','>=', $start_date)
                    ->whereDate('sales.created_at','<=', $end_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();

            } else {
                $begin_product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.purchase_unit_id', 'product_purchases.qty', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->orderBy('purchases.created_at', 'desc')
                    ->get();
            
                $begin_purchased_cost = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('total');

                $begin_product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.sale_unit_id', 'product_sales.qty', 'sales.created_at')
                    ->where([
                        ['product_sales.product_id', $product->product_id],
                        ['product_sales.variant_id', $product->variant_id],
                        ['sales.warehouse_id', $warehouse_id],

                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','<', $start_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();

                $begin_sold_price = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at')
                    ->where([
                        ['product_sales.product_id', $product->product_id],
                        ['product_sales.variant_id', $product->variant_id],
                        ['sales.warehouse_id', $warehouse_id],
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','<', $start_date)
                    ->sum('total');

                $product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.purchase_unit_id', 'product_purchases.qty', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->orderBy('purchases.created_at', 'desc')
                    ->get();
            
                $purchased_cost = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->sum('total');

                $product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.sale_unit_id', 'product_sales.qty', 'sales.created_at')
                    ->where([
                        ['product_sales.product_id', $product->product_id],
                        ['product_sales.variant_id', $product->variant_id],
                        ['sales.warehouse_id', $warehouse_id],

                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','>=', $start_date)
                    ->whereDate('sales.created_at','<=', $end_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();

            }

            $purchased_qty = 0;
            $unit_name = '';
            $sold_qty = 0;
            $created_at = '';
            $profit_unit = 0;
            $total_purchased_amount = 0;

            $begin_purchased_qty = 0;
            $begin_sold_qty = 0;
            $begin_inventory_qty = 0;
            $begin_inventory_amount = 0;
            $end_inventory_qty = 0;
            $end_inventory_amount = 0;
            
            foreach ($product_purchase_data as $product_purchase) {
                $created_at = $product_purchase->created_at;
                $unit = DB::table('units')->find($product_purchase->purchase_unit_id);
                if($unit->operator == '*'){
                    $purchased_qty += $product_purchase->qty * $unit->operation_value;
                    $unit_name = $unit->unit_name;
                }
                elseif($unit->operator == '/'){
                    $purchased_qty += $product_purchase->qty / $unit->operation_value;
                    $unit_name = $unit->unit_name;
                }
            }
            
            foreach ($product_sale_data as $product_sale) {
                $created_at = $product_sale->created_at;
                $unit = DB::table('units')->find($product_sale->sale_unit_id);
                if($unit){
                    if($unit->operator == '*'){
                        $sold_qty += $product_sale->qty * $unit->operation_value;
                        $unit_name = $unit->unit_name;
                    }
                        
                    elseif($unit->operator == '/'){
                        $sold_qty += $product_sale->qty / $unit->operation_value;
                        $unit_name = $unit->unit_name;
                    }
                }
                else
                    $sold_qty += $product_sale->qty;
            }
            
            if(!empty($begin_product_purchase_data)){
                foreach ($begin_product_purchase_data as $product_purchase) {
                    $unit = DB::table('units')->find($product_purchase->purchase_unit_id);
                    if($unit->operator == '*'){
                        $begin_purchased_qty += $product_purchase->qty * $unit->operation_value;
                    }
                    elseif($unit->operator == '/'){
                        $begin_purchased_qty += $product_purchase->qty / $unit->operation_value;
                    }
                }
            }
            
            if(!empty($begin_product_sale_data)){
                foreach ($begin_product_sale_data as $product_sale) {
                    $unit = DB::table('units')->find($product_sale->sale_unit_id);
                    if($unit){
                        if($unit->operator == '*'){
                            $begin_sold_qty += $product_sale->qty * $unit->operation_value;
                        }
                            
                        elseif($unit->operator == '/'){
                            $begin_sold_qty += $product_sale->qty / $unit->operation_value;
                        }
                    }
                    else
                        $begin_sold_qty += $product_sale->qty;
                }
            }

            if ($purchased_qty){
                $purchased_cost_qty = $purchased_cost/ $purchased_qty;
            } else {
                $purchased_cost_qty = $purchased_cost;
            }

            $total_purchased_amount = $purchased_cost_qty * $purchased_qty;          
            if(!$begin_purchased_cost){
                $begin_purchased_cost = 0;
            }
            if ($begin_purchased_qty){
                $begin_purchased_cost_qty = $begin_purchased_cost/ $begin_purchased_qty;
            } else {
                $begin_purchased_cost_qty = $begin_purchased_cost;
            }

            $begin_inventory_qty = $begin_purchased_qty - $begin_sold_qty;
            $begin_inventory_amount = $begin_purchased_cost_qty * $begin_inventory_qty;

            
            $unit_price_buy = 0;
            if($begin_inventory_qty > 0 || $purchased_qty > 0){
                $unit_price_buy = ($begin_inventory_amount + $total_purchased_amount) / ($begin_inventory_qty + $purchased_qty);
            }

            $total_sold_price = $unit_price_buy * $sold_qty;

            $end_inventory_qty = $begin_inventory_qty +  $purchased_qty - $sold_qty;
            $end_inventory_amount = $end_inventory_qty * $unit_price_buy;

            if($created_at){
                $modified_product = (object) [
                    'created_at' => $created_at,
                    'unit_name' => $unit_name,
                    'product_id' => $product->product_id,
                    'variant_id' => $product->variant_id,
                    'product_code' => $product->product_code,
                    'product_name' => $product->product_name,

                    'begin_inventory_qty' => number_format((float) $begin_inventory_qty, 0, '.', '.'),
                    'begin_inventory_amount' => number_format((float) $begin_inventory_amount, 0, '.', '.'),
                    
                    'purchased_qty' => number_format((float) $purchased_qty, 0, '.', '.'),
                    'total_purchased_amount' => number_format((float) $total_purchased_amount, 0, '.', '.'),
                    
                    'sold_qty' => number_format((float) $sold_qty, 0, '.', '.'),
                    'total_sold_price' => number_format((float) $total_sold_price, 0, '.', '.'),
                    
                    'end_inventory_qty' => number_format((float) $end_inventory_qty, 0, '.', '.'),
                    'end_inventory_amount' => number_format((float) $end_inventory_amount, 0, '.', '.'),
                    
                    'unit_price_buy' => number_format((float) ($unit_price_buy), 0, '.', '.'),
                    'sold_price' => number_format((float) $purchased_cost_qty, 0, '.', '.'),
                ];
    
                array_push($lims_data, $modified_product);
                
            }
        }
        
        $collection = new Collection($lims_data);
        $lims_data = $collection->sortBy('created_at')->values()->all();
        $lims_warehouse_list = Warehouse::where('is_active', true)->get();
        return view('report.product_lend_report',compact('lims_data', 'start_date', 'end_date', 'lims_warehouse_list', 'warehouse_id', 'warehouse_name'));
    }

    public function productLendReportID($id, $variant_id, $start_date, $end_date, $warehouse_id)
    {
        if($warehouse_id != 0){
            if( $variant_id != -1){
                $lims_product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id', 'purchases.content', 'purchases.inventory_receiving_voucher')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', $variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->orderBy('purchases.created_at', 'asc')
                    ->get();
                $total_begin_qty_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', $variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('qty');
                $total_begin_amount_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', $variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('total');
                $lims_product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id', 'sales.content', 'sales.inventory_delivery_voucher')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', $variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','>=', $start_date)
                    ->whereDate('sales.created_at','<=', $end_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();
                $total_begin_qty_sales = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', $variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','<', $start_date)
                    ->sum('qty');
                
            } else {
                $lims_product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id', 'purchases.content', 'purchases.inventory_receiving_voucher')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', NULL],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->orderBy('purchases.created_at', 'asc')
                    ->get();
                $total_begin_qty_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', NULL],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('qty');
                $total_begin_amount_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', NULL],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('total');
                $lims_product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id', 'sales.content', 'sales.inventory_delivery_voucher')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', NULL],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','>=', $start_date)
                    ->whereDate('sales.created_at','<=', $end_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();
                $total_begin_qty_sales = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', NULL],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','<', $start_date)
                    ->sum('qty');
                
            }
        } else {
            if( $variant_id != -1){
                $lims_product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id', 'purchases.content', 'purchases.inventory_receiving_voucher')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', $variant_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->orderBy('purchases.created_at', 'asc')
                    ->get();
                $total_begin_qty_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', $variant_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('qty');
                $total_begin_amount_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', $variant_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('total');
                $lims_product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id', 'sales.content', 'sales.inventory_delivery_voucher')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', $variant_id],
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','>=', $start_date)
                    ->whereDate('sales.created_at','<=', $end_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();
                $total_begin_qty_sales = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', $variant_id],
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','<', $start_date)
                    ->sum('qty');
                
            } else {
                $lims_product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id', 'purchases.content', 'purchases.inventory_receiving_voucher')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', NULL],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->orderBy('purchases.created_at', 'asc')
                    ->get();
                $total_begin_qty_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', NULL],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('qty');
                $total_begin_amount_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', NULL],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('total');
                $lims_product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id', 'sales.content', 'sales.inventory_delivery_voucher')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', NULL],
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','>=', $start_date)
                    ->whereDate('sales.created_at','<=', $end_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();
                $total_begin_qty_sales = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', NULL],
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','<', $start_date)
                    ->sum('qty');
               
            }
        }
        
        foreach ($lims_product_purchase_data as $key => $product_purchase_data) {
            $purchase = Purchase::find($product_purchase_data->purchase_id);
            $product = Product::find($product_purchase_data->product_id);
            
            if($purchase){
                $purchase_customer = Customer::find($purchase->user_id);
                $purchase_warehouse = Warehouse::find($purchase->warehouse_id);
                $purchase_supplier = Supplier::find($purchase->supplier_id);
            }

            $unit_data = Unit::find($product_purchase_data->purchase_unit_id);
            if($unit_data){
                $unit = $unit_data->unit_code;
            }
            else
                $unit = '';
            
            $product_report[0][$key] = '[' . $purchase_customer->name .'] ' . $purchase_customer->company_name ;
            $product_report[1][$key] = $purchase_warehouse == null ? '' : $purchase_warehouse->name;
            // $product_report[2][$key] = $purchase_supplier == null ? '' : $purchase_supplier->name;
            $product_report[2][$key] = $purchase->content  == null ? '' : $purchase->content;
            $product_report[3][$key] = $product_purchase_data->tax;
            $product_report[4][$key] = $product_purchase_data->qty;
            $product_report[5][$key] = $product_purchase_data->tax_rate;
            $product_report[6][$key] = $product_purchase_data->discount;
            $product_report[7][$key] = $product_purchase_data->total;
            $product_report[8][$key] = $purchase->created_at;
            $product_report[9][$key] = $product_purchase_data->invoice_number;
            $product_report[10][$key] = $product_purchase_data->serial_number;
            $product_report[11][$key] = $product_purchase_data->lot_number;
            $product_report[12][$key] = $product_purchase_data->batch_number;
            $product_report[13][$key] = $product_purchase_data->tr_number;
            $product_report[14][$key] = $unit;
            $product_report[42][$key] = $product_purchase_data->net_unit_cost;

        }

        foreach ($lims_product_sale_data as $key => $product_sale_data) {
            $sale = Sale::find($product_sale_data->sale_id);  
            $product = Product::find($product_sale_data->product_id);  
                    
            if($sale){
                $sale_warehouse = Warehouse::find($sale->warehouse_id);
                $sale_customer = Customer::find($sale->customer_id);
                $sale_biller = Biller::find($sale->biller_id);
            }
            $unit_data = Unit::find($product_sale_data->sale_unit_id);
            if($unit_data){
                $unit = $unit_data->unit_code;
            }
            else
                $unit = '';

            $product_report[15][$key] = $sale->id;
            $product_report[16][$key] = $sale_warehouse == null ? '' : $sale_warehouse->name;
            $product_report[17][$key] = $sale_customer == null ? '' : $sale_customer->name;
            $product_report[18][$key] = $product_sale_data->tax;
            $product_report[19][$key] = $product_sale_data->qty;
            $product_report[20][$key] = $product_sale_data->tax_rate;
            $product_report[21][$key] = $product_sale_data->discount;
            $product_report[22][$key] = $product_sale_data->total;
            $product_report[23][$key] = $sale->created_at;
            $product_report[24][$key] = $sale->sale_note;
            $product_report[25][$key] = $sale_biller == null ? '' : $sale_biller->name ;
            $product_report[26][$key] = $unit;
            $product_report[27][$key] = $product_sale_data->net_unit_price;
            $product_report[28][$key] = $product_sale_data->invoice_number;
            $product_report[29][$key] = $product_sale_data->serial_number;
            $product_report[30][$key] = $product_sale_data->batch_number;
            $product_report[31][$key] = $product_sale_data->tr_number;
        }

        $begin_qty = $total_begin_qty_purchases - $total_begin_qty_sales;
        $begin_total = 0;
        if($total_begin_qty_purchases > 0){
            $begin_total = $begin_qty * ($total_begin_amount_purchases / $total_begin_qty_purchases);
        }

        // Tạo mảng kết quả để lưu các object đã được kết hợp
        $resultArray = [];

        // Lặp qua mảng A và thực hiện kết hợp
        foreach ($lims_product_purchase_data as $itemA) {
            $unit = "";
            $unit_data = Unit::find($itemA->purchase_unit_id);
            if($unit_data){
                $unit = $unit_data->unit_code;
            }
            $mergedItem = (object)[
                'created_at' => $itemA->created_at, 
                'quantity_buy' => $itemA->qty, 
                'total_buy' => $itemA->total,
                'quantity_sale' => 0,
                'total_sale' => 0,
                'content' => $itemA->content ?? "",
                'document_number' => $itemA->inventory_receiving_voucher ?? '',
                'unit' => $unit,
                'unit_price' => $itemA->net_unit_cost,
            ];
            
            // Tìm ngày trong mảng B
            foreach ($lims_product_sale_data as $itemB) {
                if ($itemB->created_at === $itemA->created_at) {
                    $mergedItem->quantity_sale = $itemB->qty;
                    $mergedItem->total_sale = $itemB->total;
                    $mergedItem->content = $itemB->content ?? "";
                    $mergedItem->document_number = $itemB->inventory_delivery_voucher ?? '';
                    $mergedItem->unit_price = $itemB->net_unit_price;
                    break;
                }
            }

            $resultArray[] = $mergedItem;
        }

        // Kiểm tra các ngày trong mảng B có trong mảng A không và thêm vào kết quả nếu chưa có
        foreach ($lims_product_sale_data as $itemB) {
            $found = false;
            foreach ($resultArray as $itemResult) {
                if ($itemResult->created_at === $itemB->created_at) {
                    $found = true;
                    break;
                }
            }

            if (!$found) {
                $unit = "";
                $unit_data = Unit::find($itemB->sale_unit_id);
                if($unit_data){
                    $unit = $unit_data->unit_code;
                }
                $resultArray[] = (object)[
                    'created_at' => $itemB->created_at, 
                    'quantity_buy' => 0, 
                    'total_buy' => 0, 
                    'quantity_sale' => $itemB->qty,
                    'total_sale' => $itemB->total,
                    'content' => $itemB->content ?? "",
                    'document_number' => $itemB->inventory_delivery_voucher ?? '',
                    'unit' => $unit,
                    'unit_price' => $itemB->net_unit_price
                ];
            }
        }

        // Sắp xếp kết quả theo ngày
        usort($resultArray, function ($a, $b) {
            return strtotime($a->created_at) - strtotime($b->created_at);
        });

        foreach ($resultArray as $key => $product_data) {
            $product_report[32][$key] = $product_data->created_at;
            $product_report[33][$key] = $product_data->quantity_buy;
            $product_report[34][$key] = $product_data->total_buy;
            $product_report[35][$key] = $product_data->quantity_sale;
            $product_report[36][$key] = $product_data->total_sale;
            $product_report[37][$key] = $product_data->content;
            $product_report[38][$key] = $product_data->unit;
            $product_report[39][$key] = $product_data->unit_price;
            $product_report[40][$key] = $begin_qty;
            $product_report[41][$key] = $begin_total;
            $product_report[43][$key] = $product_data->document_number;

        }

        return $product_report;
    }

    public function productWarrantyReport(Request $request)
    {
        $data = $request->all();
        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
        $warehouse_id = $data['warehouse_id'];
        $product_id = [];
        $product_code = [];
        $variant_id = [];
        $product_name = [];
        $product_qty = [];
        $warehouse_name = "Tất cả kho";
        if($warehouse_id != 0){
            $warehouse_name = Warehouse::find($warehouse_id)->name;
        }
        
        $lims_product_all = Product::select('id', 'code', 'name', 'qty', 'is_variant')
            ->where('is_active', true)
            ->get();
            
        $lims_product_data = [];
        foreach ($lims_product_all as $product) {
            if($product->is_variant){
                $product_variant_data = ProductVariant::where('product_id', $product->id)->get();
                foreach ($product_variant_data as $key => $product_variant) {
                    $variant_data = Variant::find($product_variant->variant_id);
                    $product_variant->product_name = $variant_data->name;
                    $product_variant->product_code = $product_variant->item_code;
                    array_push($lims_product_data, $product_variant);
                }
            } else {
                $product->variant_id = null;
                $product->product_id = $product->id;
                $product->product_name = $product->name;
                $product->product_code = $product->code;
                array_push($lims_product_data, $product);
            }
        }

        $lims_data = array();
        foreach ($lims_product_data as $key => $product) { 
            $product_purchase_data = [];
            $product_sale_data = [];
            $purchased_cost = 0;

            $begin_product_purchase_data = [];
            $begin_product_sale_data = [];
            $begin_purchased_cost = 0;
            $begin_sold_price = 0;

            if($warehouse_id == 0){
                $begin_product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.qty', 'product_purchases.purchase_unit_id', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->orderBy('purchases.created_at', 'desc')
                    ->get();
                
                $begin_purchased_cost = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('total');

                $begin_product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.sale_unit_id', 'product_sales.qty', 'sales.created_at')
                    ->where([
                        ['product_sales.product_id', $product->product_id],
                        ['product_sales.variant_id', $product->variant_id]
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','<', $start_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();

                $begin_sold_price = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at')
                    ->where([
                        ['product_sales.product_id', $product->product_id],
                        ['product_sales.variant_id', $product->variant_id]
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','<', $start_date)
                    ->sum('total');

                $product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.purchase_unit_id', 'product_purchases.qty', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->orderBy('purchases.created_at', 'desc')
                    ->get();
               
                $purchased_cost = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->sum('total');

                $product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.sale_unit_id', 'product_sales.qty', 'sales.created_at')
                    ->where([
                        ['product_sales.product_id', $product->product_id],
                        ['product_sales.variant_id', $product->variant_id]
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','>=', $start_date)
                    ->whereDate('sales.created_at','<=', $end_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();

            } else {
                $begin_product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.purchase_unit_id', 'product_purchases.qty', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->orderBy('purchases.created_at', 'desc')
                    ->get();
            
                $begin_purchased_cost = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('total');

                $begin_product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.sale_unit_id', 'product_sales.qty', 'sales.created_at')
                    ->where([
                        ['product_sales.product_id', $product->product_id],
                        ['product_sales.variant_id', $product->variant_id],
                        ['sales.warehouse_id', $warehouse_id],

                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','<', $start_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();

                $begin_sold_price = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at')
                    ->where([
                        ['product_sales.product_id', $product->product_id],
                        ['product_sales.variant_id', $product->variant_id],
                        ['sales.warehouse_id', $warehouse_id],
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','<', $start_date)
                    ->sum('total');

                $product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.purchase_unit_id', 'product_purchases.qty', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->orderBy('purchases.created_at', 'desc')
                    ->get();
            
                $purchased_cost = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at')
                    ->where([
                        ['product_purchases.product_id', $product->product_id],
                        ['product_purchases.variant_id', $product->variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->sum('total');

                $product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.sale_unit_id', 'product_sales.qty', 'sales.created_at')
                    ->where([
                        ['product_sales.product_id', $product->product_id],
                        ['product_sales.variant_id', $product->variant_id],
                        ['sales.warehouse_id', $warehouse_id],

                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','>=', $start_date)
                    ->whereDate('sales.created_at','<=', $end_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();

            }

            $purchased_qty = 0;
            $unit_name = '';
            $sold_qty = 0;
            $created_at = '';
            $profit_unit = 0;
            $total_purchased_amount = 0;

            $begin_purchased_qty = 0;
            $begin_sold_qty = 0;
            $begin_inventory_qty = 0;
            $begin_inventory_amount = 0;
            $end_inventory_qty = 0;
            $end_inventory_amount = 0;
            
            foreach ($product_purchase_data as $product_purchase) {
                $created_at = $product_purchase->created_at;
                $unit = DB::table('units')->find($product_purchase->purchase_unit_id);
                if($unit->operator == '*'){
                    $purchased_qty += $product_purchase->qty * $unit->operation_value;
                    $unit_name = $unit->unit_name;
                }
                elseif($unit->operator == '/'){
                    $purchased_qty += $product_purchase->qty / $unit->operation_value;
                    $unit_name = $unit->unit_name;
                }
            }
            
            foreach ($product_sale_data as $product_sale) {
                $created_at = $product_sale->created_at;
                $unit = DB::table('units')->find($product_sale->sale_unit_id);
                if($unit){
                    if($unit->operator == '*'){
                        $sold_qty += $product_sale->qty * $unit->operation_value;
                        $unit_name = $unit->unit_name;
                    }
                        
                    elseif($unit->operator == '/'){
                        $sold_qty += $product_sale->qty / $unit->operation_value;
                        $unit_name = $unit->unit_name;
                    }
                }
                else
                    $sold_qty += $product_sale->qty;
            }
            
            if(!empty($begin_product_purchase_data)){
                foreach ($begin_product_purchase_data as $product_purchase) {
                    $unit = DB::table('units')->find($product_purchase->purchase_unit_id);
                    if($unit->operator == '*'){
                        $begin_purchased_qty += $product_purchase->qty * $unit->operation_value;
                    }
                    elseif($unit->operator == '/'){
                        $begin_purchased_qty += $product_purchase->qty / $unit->operation_value;
                    }
                }
            }
            
            if(!empty($begin_product_sale_data)){
                foreach ($begin_product_sale_data as $product_sale) {
                    $unit = DB::table('units')->find($product_sale->sale_unit_id);
                    if($unit){
                        if($unit->operator == '*'){
                            $begin_sold_qty += $product_sale->qty * $unit->operation_value;
                        }
                            
                        elseif($unit->operator == '/'){
                            $begin_sold_qty += $product_sale->qty / $unit->operation_value;
                        }
                    }
                    else
                        $begin_sold_qty += $product_sale->qty;
                }
            }

            if ($purchased_qty){
                $purchased_cost_qty = $purchased_cost/ $purchased_qty;
            } else {
                $purchased_cost_qty = $purchased_cost;
            }

            $total_purchased_amount = $purchased_cost_qty * $purchased_qty;          
            if(!$begin_purchased_cost){
                $begin_purchased_cost = 0;
            }
            if ($begin_purchased_qty){
                $begin_purchased_cost_qty = $begin_purchased_cost/ $begin_purchased_qty;
            } else {
                $begin_purchased_cost_qty = $begin_purchased_cost;
            }

            $begin_inventory_qty = $begin_purchased_qty - $begin_sold_qty;
            $begin_inventory_amount = $begin_purchased_cost_qty * $begin_inventory_qty;

            
            $unit_price_buy = 0;
            if($begin_inventory_qty > 0 || $purchased_qty > 0){
                $unit_price_buy = ($begin_inventory_amount + $total_purchased_amount) / ($begin_inventory_qty + $purchased_qty);
            }

            $total_sold_price = $unit_price_buy * $sold_qty;

            $end_inventory_qty = $begin_inventory_qty +  $purchased_qty - $sold_qty;
            $end_inventory_amount = $end_inventory_qty * $unit_price_buy;

            if($created_at){
                $modified_product = (object) [
                    'created_at' => $created_at,
                    'unit_name' => $unit_name,
                    'product_id' => $product->product_id,
                    'variant_id' => $product->variant_id,
                    'product_code' => $product->product_code,
                    'product_name' => $product->product_name,

                    'begin_inventory_qty' => number_format((float) $begin_inventory_qty, 0, '.', '.'),
                    'begin_inventory_amount' => number_format((float) $begin_inventory_amount, 0, '.', '.'),
                    
                    'purchased_qty' => number_format((float) $purchased_qty, 0, '.', '.'),
                    'total_purchased_amount' => number_format((float) $total_purchased_amount, 0, '.', '.'),
                    
                    'sold_qty' => number_format((float) $sold_qty, 0, '.', '.'),
                    'total_sold_price' => number_format((float) $total_sold_price, 0, '.', '.'),
                    
                    'end_inventory_qty' => number_format((float) $end_inventory_qty, 0, '.', '.'),
                    'end_inventory_amount' => number_format((float) $end_inventory_amount, 0, '.', '.'),
                    
                    'unit_price_buy' => number_format((float) ($unit_price_buy), 0, '.', '.'),
                    'sold_price' => number_format((float) $purchased_cost_qty, 0, '.', '.'),
                ];
    
                array_push($lims_data, $modified_product);
                
            }
        }
        
        $collection = new Collection($lims_data);
        $lims_data = $collection->sortBy('created_at')->values()->all();
        $lims_warehouse_list = Warehouse::where('is_active', true)->get();
        return view('report.warranty_report',compact('lims_data', 'start_date', 'end_date', 'lims_warehouse_list', 'warehouse_id', 'warehouse_name'));
    }

    public function productWarrantyReportID($id, $variant_id, $start_date, $end_date, $warehouse_id)
    {
        if($warehouse_id != 0){
            if( $variant_id != -1){
                $lims_product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id', 'purchases.content', 'purchases.inventory_receiving_voucher')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', $variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->orderBy('purchases.created_at', 'asc')
                    ->get();
                $total_begin_qty_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', $variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('qty');
                $total_begin_amount_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', $variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('total');
                $lims_product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id', 'sales.content', 'sales.inventory_delivery_voucher')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', $variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','>=', $start_date)
                    ->whereDate('sales.created_at','<=', $end_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();
                $total_begin_qty_sales = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', $variant_id],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','<', $start_date)
                    ->sum('qty');
                
            } else {
                $lims_product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id', 'purchases.content', 'purchases.inventory_receiving_voucher')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', NULL],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->orderBy('purchases.created_at', 'asc')
                    ->get();
                $total_begin_qty_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', NULL],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('qty');
                $total_begin_amount_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', NULL],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('total');
                $lims_product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id', 'sales.content', 'sales.inventory_delivery_voucher')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', NULL],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','>=', $start_date)
                    ->whereDate('sales.created_at','<=', $end_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();
                $total_begin_qty_sales = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', NULL],
                        ['purchases.warehouse_id', $warehouse_id],
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','<', $start_date)
                    ->sum('qty');
                
            }
        } else {
            if( $variant_id != -1){
                $lims_product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id', 'purchases.content', 'purchases.inventory_receiving_voucher')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', $variant_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->orderBy('purchases.created_at', 'asc')
                    ->get();
                $total_begin_qty_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', $variant_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('qty');
                $total_begin_amount_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', $variant_id],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('total');
                $lims_product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id', 'sales.content', 'sales.inventory_delivery_voucher')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', $variant_id],
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','>=', $start_date)
                    ->whereDate('sales.created_at','<=', $end_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();
                $total_begin_qty_sales = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', $variant_id],
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','<', $start_date)
                    ->sum('qty');
                
            } else {
                $lims_product_purchase_data = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id', 'purchases.content', 'purchases.inventory_receiving_voucher')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', NULL],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','>=', $start_date)
                    ->whereDate('purchases.created_at','<=', $end_date)
                    ->orderBy('purchases.created_at', 'asc')
                    ->get();
                $total_begin_qty_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', NULL],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('qty');
                $total_begin_amount_purchases = DB::table('purchases')
                    ->join('product_purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
                    ->select('product_purchases.*', 'purchases.created_at', 'purchases.supplier_id')
                    ->where([
                        ['product_purchases.product_id', $id],
                        ['product_purchases.variant_id', NULL],
                    ])
                    ->where('purchases.method', 'return')
                    ->whereDate('purchases.created_at','<', $start_date)
                    ->sum('total');
                $lims_product_sale_data = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id', 'sales.content', 'sales.inventory_delivery_voucher')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', NULL],
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','>=', $start_date)
                    ->whereDate('sales.created_at','<=', $end_date)
                    ->orderBy('sales.created_at', 'asc')
                    ->get();
                $total_begin_qty_sales = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.*', 'sales.created_at', 'sales.customer_id')
                    ->where([
                        ['product_sales.product_id', $id],
                        ['product_sales.variant_id', NULL],
                    ])
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at','<', $start_date)
                    ->sum('qty');
               
            }
        }
        
        foreach ($lims_product_purchase_data as $key => $product_purchase_data) {
            $purchase = Purchase::find($product_purchase_data->purchase_id);
            $product = Product::find($product_purchase_data->product_id);
            
            if($purchase){
                $purchase_customer = Customer::find($purchase->user_id);
                $purchase_warehouse = Warehouse::find($purchase->warehouse_id);
                $purchase_supplier = Supplier::find($purchase->supplier_id);
            }

            $unit_data = Unit::find($product_purchase_data->purchase_unit_id);
            if($unit_data){
                $unit = $unit_data->unit_code;
            }
            else
                $unit = '';
            
            $product_report[0][$key] = '[' . $purchase_customer->name .'] ' . $purchase_customer->company_name ;
            $product_report[1][$key] = $purchase_warehouse == null ? '' : $purchase_warehouse->name;
            // $product_report[2][$key] = $purchase_supplier == null ? '' : $purchase_supplier->name;
            $product_report[2][$key] = $purchase->content  == null ? '' : $purchase->content;
            $product_report[3][$key] = $product_purchase_data->tax;
            $product_report[4][$key] = $product_purchase_data->qty;
            $product_report[5][$key] = $product_purchase_data->tax_rate;
            $product_report[6][$key] = $product_purchase_data->discount;
            $product_report[7][$key] = $product_purchase_data->total;
            $product_report[8][$key] = $purchase->created_at;
            $product_report[9][$key] = $product_purchase_data->invoice_number;
            $product_report[10][$key] = $product_purchase_data->serial_number;
            $product_report[11][$key] = $product_purchase_data->lot_number;
            $product_report[12][$key] = $product_purchase_data->batch_number;
            $product_report[13][$key] = $product_purchase_data->tr_number;
            $product_report[14][$key] = $unit;
            $product_report[42][$key] = $product_purchase_data->net_unit_cost;

        }

        foreach ($lims_product_sale_data as $key => $product_sale_data) {
            $sale = Sale::find($product_sale_data->sale_id);  
            $product = Product::find($product_sale_data->product_id);  
                    
            if($sale){
                $sale_warehouse = Warehouse::find($sale->warehouse_id);
                $sale_customer = Customer::find($sale->customer_id);
                $sale_biller = Biller::find($sale->biller_id);
            }
            $unit_data = Unit::find($product_sale_data->sale_unit_id);
            if($unit_data){
                $unit = $unit_data->unit_code;
            }
            else
                $unit = '';

            $product_report[15][$key] = $sale->id;
            $product_report[16][$key] = $sale_warehouse == null ? '' : $sale_warehouse->name;
            $product_report[17][$key] = $sale_customer == null ? '' : $sale_customer->name;
            $product_report[18][$key] = $product_sale_data->tax;
            $product_report[19][$key] = $product_sale_data->qty;
            $product_report[20][$key] = $product_sale_data->tax_rate;
            $product_report[21][$key] = $product_sale_data->discount;
            $product_report[22][$key] = $product_sale_data->total;
            $product_report[23][$key] = $sale->created_at;
            $product_report[24][$key] = $sale->sale_note;
            $product_report[25][$key] = $sale_biller == null ? '' : $sale_biller->name ;
            $product_report[26][$key] = $unit;
            $product_report[27][$key] = $product_sale_data->net_unit_price;
            $product_report[28][$key] = $product_sale_data->invoice_number;
            $product_report[29][$key] = $product_sale_data->serial_number;
            $product_report[30][$key] = $product_sale_data->batch_number;
            $product_report[31][$key] = $product_sale_data->tr_number;
        }

        $begin_qty = $total_begin_qty_purchases - $total_begin_qty_sales;
        $begin_total = 0;
        if($total_begin_qty_purchases > 0){
            $begin_total = $begin_qty * ($total_begin_amount_purchases / $total_begin_qty_purchases);
        }

        // Tạo mảng kết quả để lưu các object đã được kết hợp
        $resultArray = [];

        // Lặp qua mảng A và thực hiện kết hợp
        foreach ($lims_product_purchase_data as $itemA) {
            $unit = "";
            $unit_data = Unit::find($itemA->purchase_unit_id);
            if($unit_data){
                $unit = $unit_data->unit_code;
            }
            $mergedItem = (object)[
                'created_at' => $itemA->created_at, 
                'quantity_buy' => $itemA->qty, 
                'total_buy' => $itemA->total,
                'quantity_sale' => 0,
                'total_sale' => 0,
                'content' => $itemA->content ?? "",
                'document_number' => $itemA->inventory_receiving_voucher ?? '',
                'unit' => $unit,
                'unit_price' => $itemA->net_unit_cost,
            ];
            
            // Tìm ngày trong mảng B
            foreach ($lims_product_sale_data as $itemB) {
                if ($itemB->created_at === $itemA->created_at) {
                    $mergedItem->quantity_sale = $itemB->qty;
                    $mergedItem->total_sale = $itemB->total;
                    $mergedItem->content = $itemB->content ?? "";
                    $mergedItem->document_number = $itemB->inventory_delivery_voucher ?? '';
                    $mergedItem->unit_price = $itemB->net_unit_price;
                    break;
                }
            }

            $resultArray[] = $mergedItem;
        }

        // Kiểm tra các ngày trong mảng B có trong mảng A không và thêm vào kết quả nếu chưa có
        foreach ($lims_product_sale_data as $itemB) {
            $found = false;
            foreach ($resultArray as $itemResult) {
                if ($itemResult->created_at === $itemB->created_at) {
                    $found = true;
                    break;
                }
            }

            if (!$found) {
                $unit = "";
                $unit_data = Unit::find($itemB->sale_unit_id);
                if($unit_data){
                    $unit = $unit_data->unit_code;
                }
                $resultArray[] = (object)[
                    'created_at' => $itemB->created_at, 
                    'quantity_buy' => 0, 
                    'total_buy' => 0, 
                    'quantity_sale' => $itemB->qty,
                    'total_sale' => $itemB->total,
                    'content' => $itemB->content ?? "",
                    'document_number' => $itemB->inventory_delivery_voucher ?? '',
                    'unit' => $unit,
                    'unit_price' => $itemB->net_unit_price
                ];
            }
        }

        // Sắp xếp kết quả theo ngày
        usort($resultArray, function ($a, $b) {
            return strtotime($a->created_at) - strtotime($b->created_at);
        });

        foreach ($resultArray as $key => $product_data) {
            $product_report[32][$key] = $product_data->created_at;
            $product_report[33][$key] = $product_data->quantity_buy;
            $product_report[34][$key] = $product_data->total_buy;
            $product_report[35][$key] = $product_data->quantity_sale;
            $product_report[36][$key] = $product_data->total_sale;
            $product_report[37][$key] = $product_data->content;
            $product_report[38][$key] = $product_data->unit;
            $product_report[39][$key] = $product_data->unit_price;
            $product_report[40][$key] = $begin_qty;
            $product_report[41][$key] = $begin_total;
            $product_report[43][$key] = $product_data->document_number;

        }

        return $product_report;
    }

    public function purchaseReport(Request $request)
    {
    	$data = $request->all();
        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
        $warehouse_id = $data['warehouse_id'];
        $product_id = [];
        $variant_id = [];
        $product_name = [];
        $product_qty = [];

        $lims_product_purchase_all = DB::table('product_purchases')
            ->select('product_purchases.product_id', 'product_purchases.variant_id')
            ->join('purchases', 'purchases.id', '=', 'product_purchases.purchase_id')
            ->where('purchases.method', '=', NULL)
            ->whereDate('purchases.created_at','>=', $start_date)
            ->whereDate('purchases.created_at','<=', $end_date)
            ->orderBy('purchases.created_at', 'asc')
            ->groupBy('product_purchases.product_id', 'product_purchases.variant_id')
            ->get();
           
        foreach ($lims_product_purchase_all as $product_purchase) {
            $lims_product_purchase_data = null;
            $variant_id_all = [];
            $product = Product::select('id', 'name', 'code', 'qty', 'is_variant')->where('id', $product_purchase->product_id)->where('is_active', true)->first();

            if($product && $product_purchase->variant_id) {
                $variant_data = Variant::find($product_purchase->variant_id);       
                $product_variant = ProductVariant::FindExactProduct($product->id, $variant_data->id)->first();
                $product_variant_code = $product_variant ? $product_variant->item_code : '';
                $variant_data_name = $variant_data ? $variant_data->name : '';
                $product_name[] = ' ['.$product_variant_code.'] '.$variant_data_name;
                $product_id[] = $product->id;
                $variant_id[] = $variant_data->id;
                if($warehouse_id == 0)
                    $product_qty[] = ProductVariant::FindExactProduct($product->id, $variant_data->id)->first()->qty;
                else
                    $product_qty[] = Product_Warehouse::where([
                                    ['product_id', $product->id],
                                    ['variant_id', $variant_data->id],
                                    ['warehouse_id', $warehouse_id]
                                ])->first()->qty;
            }
            elseif($product)  {
                $product_name[] = ' ['.$product->code.'] '.$product->name;
                $product_id[] = $product->id;
                $variant_id[] = null;
                if($warehouse_id == 0)
                    $product_qty[] = $product->qty;
                else
                    $product_qty[] = Product_Warehouse::where([
                                    ['product_id', $product->id],
                                    ['warehouse_id', $warehouse_id]
                                ])->sum('qty');
            }
            
        }

        $lims_warehouse_list = Warehouse::where('is_active', true)->get();
        return view('report.purchase_report',compact('product_id', 'variant_id', 'product_name', 'product_qty', 'start_date', 'end_date', 'lims_warehouse_list', 'warehouse_id'));
    }

    public function productPurchaseReport($id, $variant_id, $start_date, $end_date)
    {
        if( $variant_id != -1){
            $lims_product_purchase_data = DB::table('product_purchases')
                ->where([
                    ['product_id', $id],
                    ['variant_id', $variant_id]
                ])
                ->whereDate('created_at','>=', $start_date)
                ->whereDate('created_at','<=', $end_date)
                ->orderBy('created_at', 'asc')
                ->get();
        } else {
            $lims_product_purchase_data = DB::table('product_purchases')
                ->where([
                    ['product_id', $id],
                    ['variant_id', NULL]
                ])
                ->whereDate('created_at','>=', $start_date)
                ->whereDate('created_at','<=', $end_date)
                ->orderBy('created_at', 'asc')
                ->get();
        }
        
        foreach ($lims_product_purchase_data as $key => $product_purchase_data) {
            $purchase = Purchase::find($product_purchase_data->purchase_id);
            $product = Product::find($product_purchase_data->product_id);
            
            if($purchase){
                $purchase_warehouse = Warehouse::find($purchase->warehouse_id);
                $purchase_supplier = Supplier::find($purchase->supplier_id);
            }

            $unit_data = Unit::find($product_purchase_data->purchase_unit_id);
            if($unit_data){
                $unit = $unit_data->unit_code;
            }
            else
                $unit = '';

            $product_purchase_data->allocation_cost = $product_purchase_data->allocation_cost ?? 0;
            $product_purchase_data->tax_import = $product_purchase_data->tax_import ?? 0;
            $total_price_buy = $product_purchase_data->total + $product_purchase_data->discount - $product_purchase_data->allocation_cost - $product_purchase_data->tax_import - $product_purchase_data->tax;
            
            $product_purchase[0][$key] = '';
            $product_purchase[1][$key] = $purchase_warehouse == null ? '' : $purchase_warehouse->name;
            $product_purchase[2][$key] = $purchase_supplier == null ? '' : $purchase_supplier->name;
            $product_purchase[3][$key] = $product_purchase_data->tax;
            $product_purchase[4][$key] = $product_purchase_data->qty;
            $product_purchase[5][$key] = $product_purchase_data->tax_rate;
            $product_purchase[6][$key] = $product_purchase_data->discount;
            $product_purchase[7][$key] = $product_purchase_data->total;
            $product_purchase[8][$key] = date(config('date_format'), strtotime($purchase->created_at->toDateString()));
            $product_purchase[9][$key] = $product_purchase_data->invoice_number;
            $product_purchase[10][$key] = $product_purchase_data->serial_number;
            $product_purchase[11][$key] = $product_purchase_data->package_number;
            $product_purchase[12][$key] = $product_purchase_data->batch_number;
            $product_purchase[13][$key] = $product_purchase_data->tr_number;
            $product_purchase[14][$key] = $unit;
            $product_purchase[15][$key] = $product_purchase_data->oc_number;
            $product_purchase[16][$key] = $total_price_buy/$product_purchase_data->qty;

        }
        return $product_purchase;
    }

    public function saleReport(Request $request)
    {
    	$data = $request->all();
        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
        $warehouse_id = $data['warehouse_id'];
        $product_id = [];
        $variant_id = [];
        $product_name = [];
        $product_qty = [];

        $lims_product_sale_all = DB::table('product_sales')
            ->select('product_sales.product_id', 'product_sales.variant_id')
            ->join('sales', 'sales.id', '=', 'product_sales.sale_id')
            ->where('sales.method', '=', NULL)
            ->whereDate('sales.created_at','>=', $start_date)
            ->whereDate('sales.created_at','<=', $end_date)
            ->orderBy('sales.created_at', 'asc')
            ->groupBy('product_sales.product_id', 'product_sales.variant_id')
            ->get();

        foreach ($lims_product_sale_all as $product_sale) {
            $lims_product_sale_data = null;
            $variant_id_all = [];
            $product = Product::select('id', 'name', 'code', 'qty', 'is_variant')->where('id', $product_sale->product_id)->where('is_active', true)->first();

            if($product && $product_sale->variant_id) {
                $variant_data = Variant::find($product_sale->variant_id);       
                $product_variant = ProductVariant::FindExactProduct($product->id, $variant_data->id)->first();
                $product_variant_code = $product_variant->item_code;
  
                $product_name[] = ' ['.$product_variant_code.'] '.$variant_data->name;
                $product_id[] = $product->id;
                $variant_id[] = $variant_data->id;
                if($warehouse_id == 0)
                    $product_qty[] = ProductVariant::FindExactProduct($product->id, $variant_data->id)->first()->qty;
                else
                    $product_qty[] = Product_Warehouse::where([
                                    ['product_id', $product->id],
                                    ['variant_id', $variant_data->id],
                                    ['warehouse_id', $warehouse_id]
                                ])->first()->qty;
            }
            elseif($product)  {
                $product_name[] = ' ['.$product->code.'] '.$product->name;
                $product_id[] = $product->id;
                $variant_id[] = null;
                if($warehouse_id == 0)
                    $product_qty[] = $product->qty;
                else
                    $product_qty[] = Product_Warehouse::where([
                                    ['product_id', $product->id],
                                    ['warehouse_id', $warehouse_id]
                                ])->sum('qty');
            }
        }

        $lims_warehouse_list = Warehouse::where('is_active', true)->get();
        return view('report.sale_report',compact('product_id', 'variant_id', 'product_name', 'product_qty', 'start_date', 'end_date', 'lims_warehouse_list','warehouse_id'));
    }

    public function productSaleReport($id, $variant_id, $start_date, $end_date)
    {
        if( $variant_id != -1){
            $lims_product_sale_data = DB::table('product_sales')
                ->where([
                    ['product_id', $id],
                    ['variant_id', $variant_id]
                ])
                ->whereDate('created_at','>=', $start_date)
                ->whereDate('created_at','<=', $end_date)
                ->orderBy('created_at', 'asc')
                ->get();
        } else {
            $lims_product_sale_data = DB::table('product_sales')
                ->where([
                    ['product_id', $id],
                    ['variant_id', NULL]
                ])
                ->whereDate('created_at','>=', $start_date)
                ->whereDate('created_at','<=', $end_date)
                ->orderBy('created_at', 'asc')
                ->get();
        }
        
        foreach ($lims_product_sale_data as $key => $product_sale_data) {
            $sale = Sale::find($product_sale_data->sale_id);  
            $product = Product::find($product_sale_data->product_id);  
                    
            if($sale){
                $sale_warehouse = Warehouse::find($sale->warehouse_id);
                $sale_customer = Customer::find($sale->customer_id);
                $sale_biller = Biller::find($sale->biller_id);
            }
            $unit_data = Unit::find($product_sale_data->sale_unit_id);
            if($unit_data){
                $unit = $unit_data->unit_code;
            }
            else
                $unit = '';

            $role = Role::find(Auth::user()->role_id);
            $permission_view_price = $role->hasPermissionTo('view_price');
            if(!$permission_view_price) {
                $product_sale_data->total = 0;
            }
            $product_sale[0][$key] = $sale->id;
            $product_sale[1][$key] = $sale_warehouse == null ? '' : $sale_warehouse->name;
            $product_sale[2][$key] = $sale_customer == null ? '' : $sale_customer->name;
            $product_sale[3][$key] = $product_sale_data->tax;
            $product_sale[4][$key] = $product_sale_data->qty;
            $product_sale[5][$key] = $product_sale_data->tax_rate;
            $product_sale[6][$key] = $product_sale_data->discount;
            $product_sale[7][$key] = $product_sale_data->total;
            $product_sale[8][$key] = date(config('date_format'), strtotime($sale->created_at->toDateString()));
            $product_sale[9][$key] = $sale->reference_no;
            $product_sale[10][$key] = $sale_biller == null ? '' : $sale_biller->name;
            $product_sale[11][$key] = $unit;
            $product_sale[12][$key] = $product_sale_data->total/$product_sale_data->qty;
        }
        return $product_sale;
    }

    public function paymentReportByDate(Request $request)
    {
        $data = $request->all();
        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
        
        $lims_payment_data = Payment::whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->get();
        return view('report.payment_report',compact('lims_payment_data', 'start_date', 'end_date'));
    }

    public function warehouseReport(Request $request)
    {
        $data = $request->all();
        $warehouse_id = $data['warehouse_id'];
        $start_date = $data['start_date'];
        $end_date = $data['end_date'];

        if($warehouse_id == 0){
            $lims_purchase_data = Purchase::
                whereNull('method')
                ->whereDate('created_at', '>=' , $start_date)
                ->whereDate('created_at', '<=' , $end_date)
                ->orderBy('created_at', 'asc')
                ->get();
            $lims_sale_data = Sale::with('customer', 'biller')
                ->whereNull('method')
                ->whereDate('created_at', '>=' , $start_date)
                ->whereDate('created_at', '<=' , $end_date)
                ->orderBy('created_at', 'asc')
                ->get();
            $lims_return_data = Sale::with('customer', 'biller')
                ->where('method', 'return')
                ->whereDate('created_at', '>=' , $start_date)
                ->whereDate('created_at', '<=' , $end_date)
                ->orderBy('created_at', 'asc')
                ->get();
        } else {
            $lims_purchase_data = Purchase::where('warehouse_id', $warehouse_id)
                ->whereNull('method')
                ->whereDate('created_at', '>=' , $start_date)
                ->whereDate('created_at', '<=' , $end_date)
                ->orderBy('created_at', 'asc')
                ->get();
            $lims_sale_data = Sale::with('customer', 'biller')
                ->whereNull('method')
                ->where('warehouse_id', $warehouse_id)
                ->whereNull('method')
                ->whereDate('created_at', '>=' , $start_date)
                ->whereDate('created_at', '<=' , $end_date)
                ->orderBy('created_at', 'asc')
                ->get();
            $lims_return_data = Sale::with('customer', 'biller')
                ->where('warehouse_id', $warehouse_id)
                ->where('method', 'return')
                ->whereDate('created_at', '>=' , $start_date)
                ->whereDate('created_at', '<=' , $end_date)
                ->orderBy('created_at', 'asc')
                ->get();
        }

        $lims_product_purchase_data = [];
        $lims_product_sale_data = [];
        $lims_product_return_data = [];

        foreach ($lims_purchase_data as $key => $purchase) {
            $lims_product_purchase_data[$key] = ProductPurchase::where('purchase_id', $purchase->id)->get();
        }
        foreach ($lims_sale_data as $key => $sale) {
            $lims_product_sale_data[$key] = Product_Sale::where('sale_id', $sale->id)->get();
        }
        foreach ($lims_return_data as $key => $return) {
            $lims_product_return_data[$key] = Product_Sale::where('sale_id', $return->id)->get();
        }
        $lims_warehouse_list = Warehouse::where('is_active', true)->get();
        return view('report.warehouse_report', compact('warehouse_id', 'start_date', 'end_date', 'lims_purchase_data', 'lims_product_purchase_data', 'lims_sale_data', 'lims_product_sale_data', 'lims_warehouse_list', 'lims_return_data', 'lims_product_return_data'));
    }

    public function userReport(Request $request)
    {
        $data = $request->all();
        $user_id = $data['user_id'];
        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
        $lims_product_sale_data = [];
        $lims_product_purchase_data = [];
        $lims_product_quotation_data = [];
        $lims_product_transfer_data = [];

        $lims_sale_data = Sale::with('customer', 'warehouse')->where('user_id', $user_id)->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->orderBy('created_at', 'desc')->get();
        $lims_purchase_data = Purchase::with('warehouse')->where('user_id', $user_id)->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->orderBy('created_at', 'desc')->get();        
        $lims_quotation_data = Quotation::with('customer', 'warehouse')->where('user_id', $user_id)->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->orderBy('created_at', 'desc')->get();
        $lims_transfer_data = Transfer::with('fromWarehouse', 'toWarehouse')->where('user_id', $user_id)->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->orderBy('created_at', 'desc')->get();        
        $lims_payment_data = DB::table('payments')
                           ->where('user_id', $user_id)
                           ->whereDate('payments.created_at', '>=' , $start_date)
                           ->whereDate('payments.created_at', '<=' , $end_date)
                           ->orderBy('created_at', 'desc')
                           ->get();
        $lims_expense_data = Expense::with('warehouse', 'expenseCategory')->where('user_id', $user_id)->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->orderBy('created_at', 'desc')->get();
        $lims_payroll_data = Payroll::with('employee')->where('user_id', $user_id)->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->orderBy('created_at', 'desc')->get();

        foreach ($lims_sale_data as $key => $sale) {
            $lims_product_sale_data[$key] = Product_Sale::where('sale_id', $sale->id)->get();
        }
        foreach ($lims_purchase_data as $key => $purchase) {
            $lims_product_purchase_data[$key] = ProductPurchase::where('purchase_id', $purchase->id)->get();
        }
        foreach ($lims_quotation_data as $key => $quotation) {
            $lims_product_quotation_data[$key] = ProductQuotation::where('quotation_id', $quotation->id)->get();
        }
        foreach ($lims_transfer_data as $key => $transfer) {
            $lims_product_transfer_data[$key] = ProductTransfer::where('transfer_id', $transfer->id)->get();
        }

        $lims_user_list = User::where('is_active', true)->get();
        return view('report.user_report', compact('lims_sale_data','user_id', 'start_date', 'end_date', 'lims_product_sale_data', 'lims_payment_data', 'lims_user_list', 'lims_purchase_data', 'lims_product_purchase_data', 'lims_quotation_data', 'lims_product_quotation_data', 'lims_transfer_data', 'lims_product_transfer_data', 'lims_expense_data', 'lims_payroll_data') );
    }

    public function customerReport(Request $request)
    {
    	$data = $request->all();
        $customer_id = $data['customer_id'];
        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
        $lims_data_sale = [];
        $lims_data_return = [];
        $lims_payment_data = [];

        $sale_customer = Customer::find($customer_id);
        
        $data_sale_customer = [];
        $data_return_customer = [];
        
        if($customer_id == 0){
            $lims_sale_product_all = DB::table('sales')
                ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                ->select('product_sales.product_id', 'product_sales.variant_id', 'product_sales.net_unit_price', DB::raw('SUM(product_sales.qty) as total_quantity'), DB::raw('SUM(product_sales.total) as total_amount'))
                ->whereNull('sales.method')
                ->whereDate('sales.created_at', '>=', $start_date)
                ->whereDate('sales.created_at', '<=', $end_date)
                ->groupBy('product_sales.product_id', 'product_sales.variant_id', 'product_sales.net_unit_price')
                ->orderBy('sales.created_at', 'asc')
                ->get();

            $lims_return_product_all = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.product_id', 'product_sales.variant_id', DB::raw('SUM(product_sales.qty) as total_quantity'), DB::raw('SUM(product_sales.total) as total_amount'))
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at', '>=', $start_date)
                    ->whereDate('sales.created_at', '<=', $end_date)
                    ->groupBy('product_sales.product_id', 'product_sales.variant_id')
                    ->orderBy('sales.created_at', 'asc')
                    ->get();

            $lims_payment_data = DB::table('payments')
                    ->join('sales', 'payments.sale_id', '=', 'sales.id')
                    ->whereNull('sales.method')
                    ->whereDate('payments.created_at', '>=' , $start_date)
                    ->whereDate('payments.created_at', '<=' , $end_date)
                    ->select('payments.*', 'sales.reference_no as sale_reference')
                    ->orderBy('payments.created_at', 'desc')
                    ->get();

            foreach ($lims_sale_product_all as $key => $sale_product) {
                $latest_record_sale_product = DB::table('product_sales')
                    ->join('sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->whereNull('sales.method')
                    ->where('product_sales.product_id', $sale_product->product_id)
                    ->where('product_sales.variant_id', $sale_product->variant_id)
                    ->whereDate('product_sales.created_at', '>=' , $start_date)
                    ->whereDate('product_sales.created_at', '<=' , $end_date)
                    ->orderBy('product_sales.created_at', 'desc')
                    ->first();

                $lims_sale_data = Sale::with('biller', 'warehouse')
                ->find($latest_record_sale_product->sale_id);
                
                $lims_sale_product_all[$key]->product_name = "";
                $lims_sale_product_all[$key]->product_code = "";
                $lims_product_data = Product::select('name', 'code')->find($sale_product->product_id);
                if($lims_product_data){
                    $lims_sale_product_all[$key]->product_name = $lims_product_data->name;
                    $lims_sale_product_all[$key]->product_code = $lims_product_data->code;
                    if($sale_product->variant_id){
                        $lims_variant_data = Variant::find($sale_product->variant_id);
                        $lims_product_variant = ProductVariant::where('product_id', $sale_product->product_id)
                        ->where('variant_id', $sale_product->variant_id)
                        ->first();
                        if($lims_variant_data){
                            $lims_sale_product_all[$key]->product_name = $lims_variant_data->name;
                            $lims_sale_product_all[$key]->product_code = $lims_product_variant->item_code;
                        }
                    }
                }
                $lims_sale_product_all[$key]->biller = $lims_sale_data->biller == null ? "" : $lims_sale_data->biller->name;
                $lims_sale_product_all[$key]->warehouse = $lims_sale_data->warehouse->name;
                $lims_sale_product_all[$key]->created_at = $lims_sale_data->created_at;
                $lims_sale_product_all[$key]->unit = "";
                $unit_data = Unit::find($latest_record_sale_product->sale_unit_id);
                if($unit_data){
                    $lims_sale_product_all[$key]->unit = $unit_data->unit_code;
                }

            }

            $collection = new Collection($lims_sale_product_all);
            $lims_sale_product_all = $collection->sortBy('created_at');
            array_push($data_sale_customer, $lims_sale_product_all);
            array_push($data_sale_customer, $sale_customer);
            array_push($lims_data_sale, $data_sale_customer);
                
            foreach ($lims_return_product_all as $key => $return_product) {
                $latest_record_return_product = DB::table('product_sales')
                    ->join('sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->where('sales.method', 'return')
                    ->where('product_sales.product_id', $return_product->product_id)
                    ->where('product_sales.variant_id', $return_product->variant_id)
                    ->whereDate('product_sales.created_at', '>=' , $start_date)
                    ->whereDate('product_sales.created_at', '<=' , $end_date)
                    ->orderBy('product_sales.created_at', 'desc')
                    ->first();
                
                $lims_return_data = Sale::with('biller', 'warehouse')
                    ->find($latest_record_return_product->sale_id);
                
                $lims_return_product_all[$key]->product_name = "";
                $lims_return_product_all[$key]->product_code = "";
                $lims_product_data = Product::select('name', 'code')->find($return_product->product_id);
                if($lims_product_data){
                    $lims_return_product_all[$key]->product_name = $lims_product_data->name;
                    $lims_return_product_all[$key]->product_code = $lims_product_data->code;
                    if($return_product->variant_id){
                        $lims_variant_data = Variant::find($return_product->variant_id);
                        $lims_product_variant = ProductVariant::where('product_id', $return_product->product_id)
                        ->where('variant_id', $return_product->variant_id)
                        ->first();
                        if($lims_variant_data){
                            $lims_return_product_all[$key]->product_name = $lims_variant_data->name;
                            $lims_return_product_all[$key]->product_code = $lims_product_variant->item_code;
                        }
                    }
                }
                $lims_return_product_all[$key]->biller = $lims_return_data->biller == null ? "" : $lims_return_data->biller->name;
                $lims_return_product_all[$key]->warehouse = $lims_return_data->warehouse->name;
                $lims_return_product_all[$key]->created_at = $lims_return_data->created_at;
                $lims_return_product_all[$key]->unit = "";
                $unit_data = Unit::find($latest_record_return_product->sale_unit_id);
                if($unit_data){
                    $lims_return_product_all[$key]->unit = $unit_data->unit_code;
                }

            }
            $collection = new Collection($lims_return_product_all);
            $lims_return_product_all = $collection->sortBy('created_at');
            array_push($data_return_customer, $lims_return_product_all);
            array_push($data_return_customer, $sale_customer);
            array_push($lims_data_return, $data_return_customer);
        } else {
            $lims_sale_product_all = DB::table('sales')
                ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                ->select('product_sales.product_id', 'product_sales.variant_id', 'product_sales.net_unit_price', DB::raw('SUM(product_sales.qty) as total_quantity'), DB::raw('SUM(product_sales.total) as total_amount'))
                ->where('sales.customer_id', $customer_id)
                ->whereNull('sales.method')
                ->whereDate('sales.created_at', '>=', $start_date)
                ->whereDate('sales.created_at', '<=', $end_date)
                ->groupBy('product_sales.product_id', 'product_sales.variant_id', 'product_sales.net_unit_price')
                ->orderBy('sales.created_at', 'asc')
                ->get();

            $lims_return_product_all = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.product_id', 'product_sales.variant_id', DB::raw('SUM(product_sales.qty) as total_quantity'), DB::raw('SUM(product_sales.total) as total_amount'))
                    ->where('sales.customer_id', $customer_id)
                    ->where('sales.method', 'return')
                    ->whereDate('sales.created_at', '>=', $start_date)
                    ->whereDate('sales.created_at', '<=', $end_date)
                    ->groupBy('product_sales.product_id', 'product_sales.variant_id')
                    ->orderBy('sales.created_at', 'asc')
                    ->get();

            $lims_payment_data = DB::table('payments')
                    ->join('sales', 'payments.sale_id', '=', 'sales.id')
                    ->whereNull('sales.method')
                    ->where('customer_id', $customer_id)
                    ->whereDate('payments.created_at', '>=' , $start_date)
                    ->whereDate('payments.created_at', '<=' , $end_date)
                    ->select('payments.*', 'sales.reference_no as sale_reference')
                    ->orderBy('payments.created_at', 'desc')
                    ->get();

            foreach ($lims_sale_product_all as $key => $sale_product) {
                $latest_record_sale_product = DB::table('product_sales')
                    ->join('sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->where('sales.customer_id', $customer_id)
                    ->whereNull('sales.method')
                    ->where('product_sales.product_id', $sale_product->product_id)
                    ->where('product_sales.variant_id', $sale_product->variant_id)
                    ->whereDate('product_sales.created_at', '>=' , $start_date)
                    ->whereDate('product_sales.created_at', '<=' , $end_date)
                    ->orderBy('product_sales.created_at', 'desc')
                    ->first();

                $lims_sale_data = Sale::with('biller', 'warehouse')
                ->find($latest_record_sale_product->sale_id);
                
                $lims_sale_product_all[$key]->product_name = "";
                $lims_sale_product_all[$key]->product_code = "";
                $lims_product_data = Product::select('name', 'code')->find($sale_product->product_id);
                if($lims_product_data){
                    $lims_sale_product_all[$key]->product_name = $lims_product_data->name;
                    $lims_sale_product_all[$key]->product_code = $lims_product_data->code;
                    if($sale_product->variant_id){
                        $lims_variant_data = Variant::find($sale_product->variant_id);
                        $lims_product_variant = ProductVariant::where('product_id', $sale_product->product_id)
                        ->where('variant_id', $sale_product->variant_id)
                        ->first();
                        if($lims_variant_data){
                            $lims_sale_product_all[$key]->product_name = $lims_variant_data->name;
                            $lims_sale_product_all[$key]->product_code = $lims_product_variant->item_code;
                        }
                    }
                }
                $lims_sale_product_all[$key]->biller = $lims_sale_data->biller == null ? "" : $lims_sale_data->biller->name;
                $lims_sale_product_all[$key]->warehouse = $lims_sale_data->warehouse->name;
                $lims_sale_product_all[$key]->created_at = $lims_sale_data->created_at;
                $lims_sale_product_all[$key]->unit = "";
                $unit_data = Unit::find($latest_record_sale_product->sale_unit_id);
                if($unit_data){
                    $lims_sale_product_all[$key]->unit = $unit_data->unit_code;
                }

            }

            $collection = new Collection($lims_sale_product_all);
            $lims_sale_product_all = $collection->sortBy('created_at');
            array_push($data_sale_customer, $lims_sale_product_all);
            array_push($data_sale_customer, $sale_customer);
            array_push($lims_data_sale, $data_sale_customer);
                
            foreach ($lims_return_product_all as $key => $return_product) {
                $latest_record_return_product = DB::table('product_sales')
                    ->join('sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->where('sales.customer_id', $customer_id)
                    ->where('sales.method', 'return')
                    ->where('product_sales.product_id', $return_product->product_id)
                    ->where('product_sales.variant_id', $return_product->variant_id)
                    ->whereDate('product_sales.created_at', '>=' , $start_date)
                    ->whereDate('product_sales.created_at', '<=' , $end_date)
                    ->orderBy('product_sales.created_at', 'desc')
                    ->first();
                
                $lims_return_data = Sale::with('biller', 'warehouse')
                    ->find($latest_record_return_product->sale_id);
                
                $lims_return_product_all[$key]->product_name = "";
                $lims_return_product_all[$key]->product_code = "";
                $lims_product_data = Product::select('name', 'code')->find($return_product->product_id);
                if($lims_product_data){
                    $lims_return_product_all[$key]->product_name = $lims_product_data->name;
                    $lims_return_product_all[$key]->product_code = $lims_product_data->code;
                    if($return_product->variant_id){
                        $lims_variant_data = Variant::find($return_product->variant_id);
                        $lims_product_variant = ProductVariant::where('product_id', $return_product->product_id)
                        ->where('variant_id', $return_product->variant_id)
                        ->first();
                        if($lims_variant_data){
                            $lims_return_product_all[$key]->product_name = $lims_variant_data->name;
                            $lims_return_product_all[$key]->product_code = $lims_product_variant->item_code;
                        }
                    }
                }
                $lims_return_product_all[$key]->biller = $lims_return_data->biller == null ? "" : $lims_return_data->biller->name;
                $lims_return_product_all[$key]->warehouse = $lims_return_data->warehouse->name;
                $lims_return_product_all[$key]->created_at = $lims_return_data->created_at;
                $lims_return_product_all[$key]->unit = "";
                $unit_data = Unit::find($latest_record_return_product->sale_unit_id);
                if($unit_data){
                    $lims_return_product_all[$key]->unit = $unit_data->unit_code;
                }

            }
            $collection = new Collection($lims_return_product_all);
            $lims_return_product_all = $collection->sortBy('created_at');
            array_push($data_return_customer, $lims_return_product_all);
            array_push($data_return_customer, $sale_customer);
            array_push($lims_data_return, $data_return_customer);
        }


        $lims_customer_list = Customer::where('is_active', true)->get();
        return view('report.customer_report', compact('lims_data_sale', 'lims_data_return','customer_id', 'sale_customer', 'start_date', 'end_date', 'lims_payment_data', 'lims_customer_list'));
    }

    public function saleAllCustomerReport(Request $request)
    {
    	$data = $request->all();
        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
        $customer_id = $data['customer_id'];
        $lims_list_customer = [];
        $lims_data = [];
        $lims_sale_data = [];
        
        if($customer_id != 0){
            $sale_customer = Customer::find($customer_id);
            if($sale_customer != null){
                $data_customer = [];
                $limit_cost = Sale::with('warehouse')
                    ->where('customer_id', $customer_id)
                    ->whereDate('created_at', '>=' , $start_date)
                    ->whereDate('created_at', '<=' , $end_date)
                    ->sum('grand_total');
                $limit_qty = Sale::with('warehouse')
                    ->where('customer_id', $customer_id)
                    ->whereDate('created_at', '>=' , $start_date)
                    ->whereDate('created_at', '<=' , $end_date)
                    ->sum('total_qty');
                $lims_sale_product_all = DB::table('sales')
                    ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                    ->select('product_sales.product_id', 'product_sales.variant_id', DB::raw('SUM(product_sales.qty) as total_quantity'), DB::raw('SUM(product_sales.total) as total_amount'))
                    ->where('sales.customer_id', $customer_id)
                    ->whereDate('sales.created_at', '>=', $start_date)
                    ->whereDate('sales.created_at', '<=', $end_date)
                    ->groupBy('product_sales.product_id', 'product_sales.variant_id')
                    ->orderBy('sales.created_at', 'asc')
                    ->get();

                foreach ($lims_sale_product_all as $key => $sale_product) {
                    $latest_record_sale_product = DB::table('product_sales')
                        ->join('sales', 'sales.id', '=', 'product_sales.sale_id')
                        ->where('sales.customer_id', $customer_id)
                        ->where('product_sales.product_id', $sale_product->product_id)
                        ->where('product_sales.variant_id', $sale_product->variant_id)
                        ->whereDate('product_sales.created_at', '>=' , $start_date)
                        ->whereDate('product_sales.created_at', '<=' , $end_date)
                        ->orderBy('product_sales.created_at', 'desc')
                        ->first();

                    $lims_sale_data = Sale::with('biller', 'warehouse')
                    ->find($latest_record_sale_product->sale_id);
                   
                    $lims_sale_product_all[$key]->product_name = "";
                    $lims_sale_product_all[$key]->product_code = "";
                    $lims_product_data = Product::select('name', 'code')->find($sale_product->product_id);
                    if($lims_product_data){
                        $lims_sale_product_all[$key]->product_name = $lims_product_data->name;
                        $lims_sale_product_all[$key]->product_code = $lims_product_data->code;
                        if($sale_product->variant_id){
                            $lims_variant_data = Variant::find($sale_product->variant_id);
                            $lims_product_variant = ProductVariant::where('product_id', $sale_product->product_id)
                            ->where('variant_id', $sale_product->variant_id)
                            ->first();
                            if($lims_variant_data){
                                $lims_sale_product_all[$key]->product_name = $lims_variant_data->name;
                                $lims_sale_product_all[$key]->product_code = $lims_product_variant->item_code;
                            }
                        }
                    }
                    $lims_sale_product_all[$key]->biller = $lims_sale_data->biller == null ? "" : $lims_sale_data->biller->name;
                    $lims_sale_product_all[$key]->warehouse = $lims_sale_data->warehouse->name;
                    $lims_sale_product_all[$key]->created_at = $lims_sale_data->created_at;
                    $lims_sale_product_all[$key]->unit = "";
                    $unit_data = Unit::find($latest_record_sale_product->sale_unit_id);
                    if($unit_data){
                        $lims_sale_product_all[$key]->unit = $unit_data->unit_code;
                    }

                }
            
                $collection = new Collection($lims_sale_product_all);
                $lims_sale_product_all = $collection->sortBy('created_at');

                array_push($data_customer, $lims_sale_product_all);
                array_push($data_customer, $sale_customer);
                array_push($data_customer, $limit_qty);
                array_push($data_customer, $limit_cost);
                array_push($lims_data, $data_customer);
            }
        } else {
            $lims_list_customer = Sale::with('warehouse')
            ->select('customer_id')
            ->whereDate('created_at', '>=' , $start_date)
            ->whereDate('created_at', '<=' , $end_date)
            ->groupBy('customer_id')
            ->get();
        
            foreach ($lims_list_customer as $key => $customer) {
                $sale_customer = Customer::find($customer->customer_id);
                if($sale_customer != null){
                    $data_customer = [];
                    $limit_cost = Sale::with('warehouse')
                        ->where('customer_id', $customer->customer_id)
                        ->whereDate('created_at', '>=' , $start_date)
                        ->whereDate('created_at', '<=' , $end_date)
                        ->sum('grand_total');
                    $limit_qty = Sale::with('warehouse')
                        ->where('customer_id', $customer->customer_id)
                        ->whereDate('created_at', '>=' , $start_date)
                        ->whereDate('created_at', '<=' , $end_date)
                        ->sum('total_qty');
                    $lims_sale_product_all = DB::table('sales')
                        ->join('product_sales', 'sales.id', '=', 'product_sales.sale_id')
                        ->select('product_sales.product_id', 'product_sales.variant_id', DB::raw('SUM(product_sales.qty) as total_quantity'), DB::raw('SUM(product_sales.total) as total_amount'))
                        ->where('sales.customer_id', $customer->customer_id)
                        ->whereDate('sales.created_at', '>=', $start_date)
                        ->whereDate('sales.created_at', '<=', $end_date)
                        ->groupBy('product_sales.product_id', 'product_sales.variant_id')
                        ->orderBy('sales.created_at', 'asc')
                        ->get();
    
                    foreach ($lims_sale_product_all as $key => $sale_product) {
                        $latest_record_sale_product = DB::table('product_sales')
                            ->join('sales', 'sales.id', '=', 'product_sales.sale_id')
                            ->where('sales.customer_id', $customer->customer_id)
                            ->where('product_sales.product_id', $sale_product->product_id)
                            ->where('product_sales.variant_id', $sale_product->variant_id)
                            ->whereDate('product_sales.created_at', '>=' , $start_date)
                            ->whereDate('product_sales.created_at', '<=' , $end_date)
                            ->orderBy('product_sales.created_at', 'desc')
                            ->first();
    
                        $lims_sale_data = Sale::with('biller', 'warehouse')
                        ->find($latest_record_sale_product->sale_id);
                       
                        $lims_sale_product_all[$key]->product_name = "";
                        $lims_sale_product_all[$key]->product_code = "";
                        $lims_product_data = Product::select('name', 'code')->find($sale_product->product_id);
                        if($lims_product_data){
                            $lims_sale_product_all[$key]->product_name = $lims_product_data->name;
                            $lims_sale_product_all[$key]->product_code = $lims_product_data->code;
                            if($sale_product->variant_id){
                                $lims_variant_data = Variant::find($sale_product->variant_id);
                                $lims_product_variant = ProductVariant::where('product_id', $sale_product->product_id)
                                ->where('variant_id', $sale_product->variant_id)
                                ->first();
                                if($lims_variant_data){
                                    $lims_sale_product_all[$key]->product_name = $lims_variant_data->name;
                                    $lims_sale_product_all[$key]->product_code = $lims_product_variant->item_code;
                                }
                            }
                        }
                        $lims_sale_product_all[$key]->biller = $lims_sale_data->biller == null ? "" : $lims_sale_data->biller->name;
                        $lims_sale_product_all[$key]->warehouse = $lims_sale_data->warehouse->name;
                        $lims_sale_product_all[$key]->created_at = $lims_sale_data->created_at;
                        $lims_sale_product_all[$key]->unit = "";
                        $unit_data = Unit::find($latest_record_sale_product->sale_unit_id);
                        if($unit_data){
                            $lims_sale_product_all[$key]->unit = $unit_data->unit_code;
                        }
    
                    }
                
                    $collection = new Collection($lims_sale_product_all);
                    $lims_sale_product_all = $collection->sortBy('created_at');
    
                    array_push($data_customer, $lims_sale_product_all);
                    array_push($data_customer, $sale_customer);
                    array_push($data_customer, $limit_qty);
                    array_push($data_customer, $limit_cost);
                    array_push($lims_data, $data_customer);
                }
            } 
             
        }

        $lims_customer_list = Customer::where('is_active', true)->get();
        return view('report.sale_all_customer', compact('customer_id', 'lims_customer_list', 'lims_data', 'lims_sale_data', 'start_date', 'end_date'));
    }

    public function saleAllProductReport(Request $request)
    {
    	$data = $request->all();
        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
        $product_code = $data['product_code'];
        $lims_product_list = [];
        $lims_data = [];
        $lims_sale_product_data = [];

        if($product_code != 0){
            $lims_product_data = Product::where('code', $product_code)->first();
            
            if(!$lims_product_data){
                $product_variant_data = ProductVariant::where('item_code', $product_code)->first();
                if($product_variant_data){
                    $product_data = Product::find($product_variant_data->product_id);
                    if($product_data != null){
                        $data_product = [];
                        $lims_sale_product_data = Product_Sale::where('product_id', $product_variant_data->product_id)
                            ->where('variant_id', $product_variant_data->variant_id)
                            ->whereDate('created_at', '>=' , $start_date)
                            ->whereDate('created_at', '<=' , $end_date)
                            ->orderBy('created_at', 'asc')
                            ->get();
                    
                        $lims_total_cost = 0;
                        $lims_total_qty = 0;
        
                        foreach ($lims_sale_product_data as $key => $sale_product) {
                            $sale_data = Sale::find($sale_product->sale_id);
                            $sale_customer_data = Customer::find($sale_data->customer_id);
                            $sale_warehouse_data = Warehouse::find($sale_data->warehouse_id);
                            $sale_biller_data = Biller::find($sale_data->biller_id);
        
                            $lims_sale_product_data[$key]->customer = $sale_customer_data == null ? '' : $sale_customer_data->name;
                            $lims_sale_product_data[$key]->biller = $sale_biller_data == null ? '' : $sale_biller_data->name;
                            $lims_sale_product_data[$key]->warehouse = $sale_warehouse_data == null ? '' : $sale_warehouse_data->name;
                            if($sale_customer_data->phone_number){
                                $sale_customer_data->name .= ' ['.$sale_customer_data->phone_number.']';
                                $lims_sale_product_data[$key]->customer = $sale_customer_data->name;
                            }
                            else {
                                $lims_sale_product_data[$key]->customer = $sale_customer_data->name;
                            }
        
                            $lims_sale_product_data[$key]->reference_no = $sale_data->reference_no;
                            $lims_total_qty += $sale_product->qty;
                            $lims_total_cost += $sale_product->total;
                        }          
        
                        if($product_variant_data->variant_id){
                            $variant_data = Variant::find($product_variant_data->variant_id);
                            $product_variant_code = $product_variant_data->item_code;
                            $product_data->name = ' [' . $product_variant_code.'] ' .$variant_data->name;     
                        }
                        else {
                            $product_data->name = ' [' . $product_data->code.'] ' . $product_data->name;
                        }
        
                        array_push($data_product, $lims_sale_product_data);
                        array_push($data_product, $product_data->name);
                        array_push($data_product, $lims_total_qty);
                        array_push($data_product, $lims_total_cost);
                        array_push($lims_data, $data_product);
                    }
                    
                }
            } else {
                $data_product = [];
                $lims_sale_product_data = Product_Sale::where('product_id', $lims_product_data->id)                      
                    ->whereDate('created_at', '>=' , $start_date)
                    ->whereDate('created_at', '<=' , $end_date)
                    ->orderBy('created_at', 'asc')
                    ->get();
            
                $lims_total_cost = 0;
                $lims_total_qty = 0;

                foreach ($lims_sale_product_data as $key => $sale_product) {
                    $sale_data = Sale::find($sale_product->sale_id);
                    $sale_customer_data = Customer::find($sale_data->customer_id);
                    $sale_warehouse_data = Warehouse::find($sale_data->warehouse_id);
                    $sale_biller_data = Biller::find($sale_data->biller_id);

                    $lims_sale_product_data[$key]->customer = $sale_customer_data == null ? '' : $sale_customer_data->name;
                    $lims_sale_product_data[$key]->biller = $sale_biller_data == null ? '' : $sale_biller_data->name;
                    $lims_sale_product_data[$key]->warehouse = $sale_warehouse_data == null ? '' : $sale_warehouse_data->name;
                    if($sale_customer_data->phone_number){
                        $sale_customer_data->name .= ' ['.$sale_customer_data->phone_number.']';
                        $lims_sale_product_data[$key]->customer = $sale_customer_data->name;
                    }
                    else {
                        $lims_sale_product_data[$key]->customer = $sale_customer_data->name;
                    }

                    $lims_sale_product_data[$key]->reference_no = $sale_data->reference_no;
                    $lims_total_qty += $sale_product->qty;
                    $lims_total_cost += $sale_product->total;
                }          

                $lims_product_data->name = ' [' . $lims_product_data->code.'] ' . $lims_product_data->name;

                array_push($data_product, $lims_sale_product_data);
                array_push($data_product, $lims_product_data->name);
                array_push($data_product, $lims_total_qty);
                array_push($data_product, $lims_total_cost);
                array_push($lims_data, $data_product);
            }
        } else{
            $lims_list_sale_all = DB::table('product_sales')
                ->select('product_sales.product_id', 'product_sales.variant_id')
                ->join('sales', 'sales.id', '=', 'product_sales.sale_id')
                ->whereDate('sales.created_at','>=', $start_date)
                ->whereDate('sales.created_at','<=', $end_date)
                ->groupBy('product_sales.product_id', 'product_sales.variant_id')
                ->get();
        
            foreach ($lims_list_sale_all as $key => $product) {
                $product_data = Product::find($product->product_id);
                if($product_data != null){
                    $data_product = [];
                    $lims_sale_product_data = Product_Sale::where('product_id', $product->product_id)
                        ->where('variant_id', $product->variant_id)
                        ->whereDate('created_at', '>=' , $start_date)
                        ->whereDate('created_at', '<=' , $end_date)
                        ->orderBy('created_at', 'asc')
                        ->get();

                    $lims_total_cost = 0;
                    $lims_total_qty = 0;
    
                    foreach ($lims_sale_product_data as $key => $sale_product) {
                        $sale_data = Sale::find($sale_product->sale_id);
                        $sale_customer_data = Customer::find($sale_data->customer_id);
                        $sale_warehouse_data = Warehouse::find($sale_data->warehouse_id);
                        $sale_biller_data = Biller::find($sale_data->biller_id);
    
                        $lims_sale_product_data[$key]->customer = $sale_customer_data == null ? '' : $sale_customer_data->name;
                        $lims_sale_product_data[$key]->biller = $sale_biller_data == null ? '' : $sale_biller_data->name;
                        $lims_sale_product_data[$key]->warehouse = $sale_warehouse_data == null ? '' : $sale_warehouse_data->name;
                        if($sale_customer_data->phone_number){
                            $sale_customer_data->name .= ' ['.$sale_customer_data->phone_number.']';
                            $lims_sale_product_data[$key]->customer = $sale_customer_data->name;
                        }
                        else {
                            $lims_sale_product_data[$key]->customer = $sale_customer_data->name;
                        }
    
                        $lims_sale_product_data[$key]->reference_no = $sale_data->reference_no;
                        $lims_total_qty += $sale_product->qty;
                        $lims_total_cost += $sale_product->total;
                    }          
    
                    if($product->variant_id){
                        $variant_data = Variant::find($product->variant_id);
                        $product_variant = ProductVariant::FindExactProduct($product->product_id, $variant_data->id)->first();
                        $product_variant_code = $product_variant->item_code;
                        $product_data->name = ' [' . $product_variant_code.'] ' .$variant_data->name;     
                    }
                    else {
                        $product_data->name = ' [' . $product_data->code.'] ' . $product_data->name;
                    }
    
                    array_push($data_product, $lims_sale_product_data);
                    array_push($data_product, $product_data->name);
                    array_push($data_product, $lims_total_qty);
                    array_push($data_product, $lims_total_cost);
                    array_push($lims_data, $data_product);
                }
            } 
        }

        $lims_product_data = Product::where('is_active', true)->get();
        if(count($lims_product_data) > 0){
            foreach ($lims_product_data as $key => $product) {
                if($product->is_variant == 1){
                    $lims_product_variant_data = Product::join('product_variants', 'products.id', '=', 'product_variants.product_id')
                        ->select('products.name', 'product_variants.item_code as code', 'product_variants.variant_id')
                        ->where('product_id', $product->id)
                        ->get();

                    if(count($lims_product_variant_data) > 0){
                        foreach ($lims_product_variant_data as $key => $product_variant) {
                            $variant_name = Variant::find($product_variant->variant_id)->name;
                            $product_variant->name = $variant_name;
                            $lims_product_list[] = $product_variant;
                        }
                    }
                } else {
                    $lims_product_list[] = $product;
                }
            }
        }

        return view('report.sale_all_product', compact('product_code', 'lims_product_list', 'lims_data', 'lims_sale_product_data', 'start_date', 'end_date'));
    }

    public function purchaseAllSupplierReport(Request $request)
    {
    	$data = $request->all();
        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
        $supplier_id = $data['supplier_id'];
        $lims_list_supplier = [];
        $lims_data = [];
        $lims_purchase_data = [];

        if($supplier_id != 0){
            $purchase_supplier = Supplier::find($supplier_id);
            if($purchase_supplier->is_active == 1){
                $data_supplier = [];
                $lims_purchase_data = Purchase::with('warehouse')
                    ->where('method', NULL)
                    ->where('supplier_id', $supplier_id)
                    ->whereDate('created_at', '>=' , $start_date)
                    ->whereDate('created_at', '<=' , $end_date)
                    ->orderBy('created_at', 'asc')
                    ->get();
                $limit_cost = Purchase::with('warehouse')
                    ->where('method', NULL)
                    ->where('supplier_id', $supplier_id)
                    ->whereDate('created_at', '>=' , $start_date)
                    ->whereDate('created_at', '<=' , $end_date)
                    ->sum('grand_total');
                $limit_qty = Purchase::with('warehouse')
                    ->where('method', NULL)
                    ->where('supplier_id', $supplier_id)
                    ->whereDate('created_at', '>=' , $start_date)
                    ->whereDate('created_at', '<=' , $end_date)
                    ->sum('total_qty');

                foreach ($lims_purchase_data as $key => $purchase) {
                    $lims_purchase_data[$key]->product = ProductPurchase::where('purchase_id', $purchase->id)->get();
                }

                array_push($data_supplier, $lims_purchase_data);
                array_push($data_supplier, $purchase_supplier);
                array_push($data_supplier, $limit_qty);
                array_push($data_supplier, $limit_cost);
                array_push($lims_data, $data_supplier);
            }
        } else {
            $lims_list_supplier = Purchase::with('warehouse')
                ->where('method', NULL)
                ->select('supplier_id')
                ->whereDate('created_at', '>=' , $start_date)
                ->whereDate('created_at', '<=' , $end_date)
                ->groupBy('supplier_id')
                ->get();

            foreach ($lims_list_supplier as $key => $supplier) {
                if($supplier->supplier_id != null){
                    $purchase_supplier = Supplier::find($supplier->supplier_id);
                    if($purchase_supplier->is_active == 1){
                        $data_supplier = [];
                        $lims_purchase_data = Purchase::with('warehouse')->where('method', NULL)->where('supplier_id', $supplier->supplier_id)->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->orderBy('created_at', 'asc')->get();
                        $limit_cost = Purchase::with('warehouse')->where('method', NULL)->where('supplier_id', $supplier->supplier_id)->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->sum('grand_total');
                        $limit_qty = Purchase::with('warehouse')->where('method', NULL)->where('supplier_id', $supplier->supplier_id)->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->sum('total_qty');
    
                        foreach ($lims_purchase_data as $key => $purchase) {
                            $lims_purchase_data[$key]->product = ProductPurchase::where('purchase_id', $purchase->id)->get();
                        }
    
                        array_push($data_supplier, $lims_purchase_data);
                        array_push($data_supplier, $purchase_supplier);
                        array_push($data_supplier, $limit_qty);
                        array_push($data_supplier, $limit_cost);
                        array_push($lims_data, $data_supplier);
                    }
                }
            } 
        }
       
        $lims_supplier_list = Supplier::where('is_active', true)->get();
        return view('report.purchase_all_supplier', compact('supplier_id', 'lims_supplier_list', 'lims_data', 'lims_purchase_data', 'start_date', 'end_date'));
    }

    public function purchaseAllProductReport(Request $request)
    {
    	$data = $request->all();
        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
        $product_id = $data['product_id'];
        $lims_data = [];
        $lims_purchase_product_data = [];

        if($product_id != 0){
            $lims_list_product = ProductPurchase::select('product_id')
            ->where('product_id', $product_id)
            ->whereDate('created_at', '>=' , $start_date)
            ->whereDate('created_at', '<=' , $end_date)
            ->groupBy('product_id')
            ->get();
            
            foreach ($lims_list_product as $key => $product) {
                $product_data = Product::find($product->product_id);
                
                if($product_data != null ){
                    $data_product = [];
                    $lims_purchase_product_data = ProductPurchase::where('product_id', $product->product_id)                      
                        ->whereDate('created_at', '>=' , $start_date)
                        ->whereDate('created_at', '<=' , $end_date)
                        ->orderBy('created_at', 'asc')
                        ->get();

                    $count_variant = 1;
                    if($product_data->is_variant == 1) {
                        $product_variant_data = ProductVariant::where('product_id', $product_data->id)->get();
                        $count_variant = count($product_variant_data);
                    }
                    $lims_total_cost = 0;
                    $lims_total_qty = 0;

                    if(count($lims_purchase_product_data) > 0){
                        foreach ($lims_purchase_product_data as $key => $purchase_product) {
                            $purchase_data = Purchase::where('id', $purchase_product->purchase_id)->where('method', NULL)->first();
                            if($purchase_data){
                                $purchase_supplier_data = Supplier::find($purchase_data->supplier_id);
                                $purchase_warehouse_data = Warehouse::find($purchase_data->warehouse_id);
        
                                $lims_purchase_product_data[$key]->warehouse = $purchase_warehouse_data == null ? '' : $purchase_warehouse_data->name;
                                if( $purchase_supplier_data && $purchase_supplier_data->phone_number){
                                    $purchase_supplier_data->name .= ' ['.$purchase_supplier_data->phone_number.']';
                                    $lims_purchase_product_data[$key]->supplier = $purchase_supplier_data->name;
                                }
                                else {
                                    $lims_purchase_product_data[$key]->supplier = $purchase_supplier_data == null ? '' : $purchase_supplier_data->name;
                                }
        
                                $lims_purchase_product_data[$key]->content = $purchase_data->content ?? '';
                                $lims_total_qty += $purchase_product->qty;
                                $lims_total_cost += $purchase_product->total;
                            } else {
                                unset($lims_purchase_product_data[$key]);
                            }
                            
                        }
                    }


                    if(count($lims_purchase_product_data) > 0){
                        $product_data_name = ' [' . $product_data->code.'] ' . $product_data->name;
                        
                        array_push($data_product, $lims_purchase_product_data);
                        array_push($data_product, $product_data_name);
                        array_push($data_product, $lims_total_qty/$count_variant);
                        array_push($data_product, $lims_total_cost);
                        array_push($lims_data, $data_product);
                    }
                }
            } 
        } else{
            $lims_list_product = ProductPurchase::select('product_id')
            ->whereDate('created_at', '>=' , $start_date)
            ->whereDate('created_at', '<=' , $end_date)
            ->groupBy('product_id')
            ->get();

            foreach ($lims_list_product as $key => $product) {
                $product_data = Product::find($product->product_id);
                
                if($product_data != null ){
                    $data_product = [];
                    $lims_purchase_product_data = ProductPurchase::
                        where('product_id', $product->product_id)
                        ->whereDate('created_at', '>=' , $start_date)
                        ->whereDate('created_at', '<=' , $end_date)
                        ->orderBy('created_at', 'asc')
                        ->get();

                    $count_variant = 1;
                    if($product_data->is_variant == 1) {
                        $product_variant_data = ProductVariant::where('product_id', $product_data->id)->get();
                        $count_variant = count($product_variant_data);
                    }
                    $lims_total_cost = 0;
                    $lims_total_qty = 0;

                    if(count($lims_purchase_product_data) > 0){
                        foreach ($lims_purchase_product_data as $key => $purchase_product) {
                            $purchase_data = Purchase::where('id', $purchase_product->purchase_id)->where('method', NULL)->first();
                            if($purchase_data ){
                                $purchase_supplier_data = Supplier::find($purchase_data->supplier_id);
                                $purchase_warehouse_data = Warehouse::find($purchase_data->warehouse_id);
    
                                $lims_purchase_product_data[$key]->warehouse = $purchase_warehouse_data == null ? '' : $purchase_warehouse_data->name;
                                if( $purchase_supplier_data && $purchase_supplier_data->phone_number){
                                    $purchase_supplier_data->name .= ' ['.$purchase_supplier_data->phone_number.']';
                                    $lims_purchase_product_data[$key]->supplier = $purchase_supplier_data->name;
                                }
                                else {
                                    $lims_purchase_product_data[$key]->supplier = $purchase_supplier_data == null ? '' : $purchase_supplier_data->name;
                                }
    
                                $lims_purchase_product_data[$key]->content = $purchase_data->content ?? '';
                                $lims_total_qty += $purchase_product->qty;
                                $lims_total_cost += $purchase_product->total;
                            } else {
                                unset($lims_purchase_product_data[$key]);
                            }
                        }
                    }

                    if(count($lims_purchase_product_data) > 0){
                        $product_data_name = ' [' . $product_data->code.'] ' . $product_data->name;
                        
                        array_push($data_product, $lims_purchase_product_data);
                        array_push($data_product, $product_data_name);
                        array_push($data_product, $lims_total_qty/$count_variant);
                        array_push($data_product, $lims_total_cost);
                        array_push($lims_data, $data_product);
                    }
                }
            } 
        }

        $lims_product_list = Product::where('is_active', true)->get();
        return view('report.purchase_all_product', compact('product_id', 'lims_product_list', 'lims_data', 'lims_purchase_product_data', 'start_date', 'end_date'));
    }

    public function lendAllSupplierReport(Request $request)
    {
    	$data = $request->all();
        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
        $supplier_id = $data['supplier_id'];
        $lims_list_supplier = [];
        $lims_data = [];
        $lims_purchase_data = [];

        if($supplier_id != 0){
            $purchase_supplier = Supplier::find($supplier_id);
            if($purchase_supplier->is_active == 1){
                $data_supplier = [];
                $lims_purchase_data = Purchase::with('warehouse')
                    ->where('method', 'return')
                    ->where('supplier_id', $supplier_id)
                    ->whereDate('created_at', '>=' , $start_date)
                    ->whereDate('created_at', '<=' , $end_date)
                    ->orderBy('created_at', 'asc')
                    ->get();
                $limit_cost = Purchase::with('warehouse')
                    ->where('method', 'return')
                    ->where('supplier_id', $supplier_id)
                    ->whereDate('created_at', '>=' , $start_date)
                    ->whereDate('created_at', '<=' , $end_date)
                    ->sum('grand_total');
                $limit_qty = Purchase::with('warehouse')
                    ->where('method', 'return')
                    ->where('supplier_id', $supplier_id)
                    ->whereDate('created_at', '>=' , $start_date)
                    ->whereDate('created_at', '<=' , $end_date)
                    ->sum('total_qty');

                foreach ($lims_purchase_data as $key => $purchase) {
                    $lims_purchase_data[$key]->product = ProductPurchase::where('purchase_id', $purchase->id)->get();
                }

                array_push($data_supplier, $lims_purchase_data);
                array_push($data_supplier, $purchase_supplier);
                array_push($data_supplier, $limit_qty);
                array_push($data_supplier, $limit_cost);
                array_push($lims_data, $data_supplier);
            }
        } else {
            $lims_list_supplier = Purchase::with('warehouse')
                ->select('supplier_id')
                ->where('method', 'return')
                ->whereDate('created_at', '>=' , $start_date)
                ->whereDate('created_at', '<=' , $end_date)
                ->groupBy('supplier_id')
                ->get();

            foreach ($lims_list_supplier as $key => $supplier) {
                if($supplier->supplier_id != null){
                    $purchase_supplier = Supplier::find($supplier->supplier_id);
                    if($purchase_supplier->is_active == 1){
                        $data_supplier = [];
                        $lims_purchase_data = Purchase::with('warehouse')->where('method', 'return')->where('supplier_id', $supplier->supplier_id)->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->orderBy('created_at', 'asc')->get();
                        $limit_cost = Purchase::with('warehouse')->where('method', 'return')->where('supplier_id', $supplier->supplier_id)->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->sum('grand_total');
                        $limit_qty = Purchase::with('warehouse')->where('method', 'return')->where('supplier_id', $supplier->supplier_id)->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->sum('total_qty');
    
                        foreach ($lims_purchase_data as $key => $purchase) {
                            $lims_purchase_data[$key]->product = ProductPurchase::where('purchase_id', $purchase->id)->get();
                        }
    
                        array_push($data_supplier, $lims_purchase_data);
                        array_push($data_supplier, $purchase_supplier);
                        array_push($data_supplier, $limit_qty);
                        array_push($data_supplier, $limit_cost);
                        array_push($lims_data, $data_supplier);
                    }
                }
            } 
        }
       
        $lims_supplier_list = Supplier::where('is_active', true)->get();
        return view('report.lend_all_supplier', compact('supplier_id', 'lims_supplier_list', 'lims_data', 'lims_purchase_data', 'start_date', 'end_date'));
    }

    public function lendAllProductReport(Request $request)
    {
    	$data = $request->all();
        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
        $product_id = $data['product_id'];
        $lims_data = [];
        $lims_purchase_product_data = [];

        if($product_id != 0){
            $lims_list_product = ProductPurchase::select('product_id')
            ->where('product_id', $product_id)
            ->whereDate('created_at', '>=' , $start_date)
            ->whereDate('created_at', '<=' , $end_date)
            ->groupBy('product_id')
            ->get();
            
            foreach ($lims_list_product as $key => $product) {
                $product_data = Product::find($product->product_id);
                
                if($product_data != null ){
                    $data_product = [];
                    $lims_purchase_product_data = ProductPurchase::where('product_id', $product->product_id)                      
                        ->whereDate('created_at', '>=' , $start_date)
                        ->whereDate('created_at', '<=' , $end_date)
                        ->orderBy('created_at', 'asc')
                        ->get();

                    $count_variant = 1;
                    if($product_data->is_variant == 1) {
                        $product_variant_data = ProductVariant::where('product_id', $product_data->id)->get();
                        $count_variant = count($product_variant_data);
                    }
                    $lims_total_cost = 0;
                    $lims_total_qty = 0;

                    if(count($lims_purchase_product_data) > 0){
                        foreach ($lims_purchase_product_data as $key => $purchase_product) {
                            $purchase_data = Purchase::where('id', $purchase_product->purchase_id)->where('method', 'return')->first();
                            if($purchase_data){
                                $purchase_supplier_data = Supplier::find($purchase_data->supplier_id);
                                $purchase_warehouse_data = Warehouse::find($purchase_data->warehouse_id);
        
                                $lims_purchase_product_data[$key]->warehouse = $purchase_warehouse_data == null ? '' : $purchase_warehouse_data->name;
                                if( $purchase_supplier_data && $purchase_supplier_data->phone_number){
                                    $purchase_supplier_data->name .= ' ['.$purchase_supplier_data->phone_number.']';
                                    $lims_purchase_product_data[$key]->supplier = $purchase_supplier_data->name;
                                }
                                else {
                                    $lims_purchase_product_data[$key]->supplier = $purchase_supplier_data == null ? '' : $purchase_supplier_data->name;
                                }
        
                                $lims_purchase_product_data[$key]->content = $purchase_data->content ?? '';
                                $lims_total_qty += $purchase_product->qty;
                                $lims_total_cost += $purchase_product->total;
                            } else {
                                unset($lims_purchase_product_data[$key]);
                            }
                        }
                    }

                    if(count($lims_purchase_product_data) > 0){
                        $product_data_name = ' [' . $product_data->code.'] ' . $product_data->name;
                        
                        array_push($data_product, $lims_purchase_product_data);
                        array_push($data_product, $product_data_name);
                        array_push($data_product, $lims_total_qty/$count_variant);
                        array_push($data_product, $lims_total_cost);
                        array_push($lims_data, $data_product);
                    }
                }
            } 
        } else{
            $lims_list_product = ProductPurchase::select('product_id')
            ->whereDate('created_at', '>=' , $start_date)
            ->whereDate('created_at', '<=' , $end_date)
            ->groupBy('product_id')
            ->get();

            foreach ($lims_list_product as $key => $product) {
                $product_data = Product::find($product->product_id);
                
                if($product_data != null ){
                    $data_product = [];
                    $lims_purchase_product_data = ProductPurchase::
                        where('product_id', $product->product_id)
                        ->whereDate('created_at', '>=' , $start_date)
                        ->whereDate('created_at', '<=' , $end_date)
                        ->orderBy('created_at', 'asc')
                        ->get();

                    $count_variant = 1;
                    if($product_data->is_variant == 1) {
                        $product_variant_data = ProductVariant::where('product_id', $product_data->id)->get();
                        $count_variant = count($product_variant_data);
                    }
                    $lims_total_cost = 0;
                    $lims_total_qty = 0;

                    if(count($lims_purchase_product_data) > 0){
                        foreach ($lims_purchase_product_data as $key => $purchase_product) {
                            $purchase_data = Purchase::where('id', $purchase_product->purchase_id)->where('method', 'return')->first();
                            if($purchase_data){
                                $purchase_supplier_data = Supplier::find($purchase_data->supplier_id);
                                $purchase_warehouse_data = Warehouse::find($purchase_data->warehouse_id);
    
                                $lims_purchase_product_data[$key]->warehouse = $purchase_warehouse_data == null ? '' : $purchase_warehouse_data->name;
                                if( $purchase_supplier_data && $purchase_supplier_data->phone_number){
                                    $purchase_supplier_data->name .= ' ['.$purchase_supplier_data->phone_number.']';
                                    $lims_purchase_product_data[$key]->supplier = $purchase_supplier_data->name;
                                }
                                else {
                                    $lims_purchase_product_data[$key]->supplier = $purchase_supplier_data == null ? '' : $purchase_supplier_data->name;
                                }
    
                                $lims_purchase_product_data[$key]->content = $purchase_data->content ?? '';
                                $lims_total_qty += $purchase_product->qty;
                                $lims_total_cost += $purchase_product->total;
                            } else {
                                unset($lims_purchase_product_data[$key]);
                            }
                        }
                    }

                    if(count($lims_purchase_product_data) > 0){
                        $product_data_name = ' [' . $product_data->code.'] ' . $product_data->name;
    
                        array_push($data_product, $lims_purchase_product_data);
                        array_push($data_product, $product_data_name);
                        array_push($data_product, $lims_total_qty/$count_variant);
                        array_push($data_product, $lims_total_cost);
                        array_push($lims_data, $data_product);
                    }
                }
            } 
        }

        $lims_product_list = Product::where('is_active', true)->get();
        return view('report.lend_all_product', compact('product_id', 'lims_product_list', 'lims_data', 'lims_purchase_product_data', 'start_date', 'end_date'));
    }

    public function warrantyAllCustomerReport(Request $request)
    {
    	$data = $request->all();
        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
        $customer_id = $data['customer_id'];
        $lims_list_customer = [];
        $lims_data = [];
        $lims_warranty_data = [];
        
        if($customer_id != 0){
            $warranty_customer = Customer::find($customer_id);
            if($warranty_customer != null){
                $data_customer = [];
                $limit_qty = Warranty::with('warehouse')
                    ->where('customer_id', $customer_id)
                    ->whereDate('created_at', '>=' , $start_date)
                    ->whereDate('created_at', '<=' , $end_date)
                    ->sum('total_qty');
                $lims_warranty_product_all = DB::table('warranties')
                    ->join('product_warranties', 'warranties.id', '=', 'product_warranties.warranty_id')
                    ->select('product_warranties.product_id', 'product_warranties.variant_id', DB::raw('SUM(product_warranties.qty) as total_quantity'))
                    ->where('warranties.customer_id', $customer_id)
                    ->whereDate('warranties.created_at', '>=', $start_date)
                    ->whereDate('warranties.created_at', '<=', $end_date)
                    ->groupBy('product_warranties.product_id', 'product_warranties.variant_id')
                    ->orderBy('warranties.created_at', 'asc')
                    ->get();

                foreach ($lims_warranty_product_all as $key => $warranty_product) {
                    $latest_record_warranty_product = DB::table('product_warranties')
                        ->join('warranties', 'warranties.id', '=', 'product_warranties.warranty_id')
                        ->where('warranties.customer_id', $customer_id)
                        ->where('product_warranties.product_id', $warranty_product->product_id)
                        ->where('product_warranties.variant_id', $warranty_product->variant_id)
                        ->whereDate('product_warranties.created_at', '>=' , $start_date)
                        ->whereDate('product_warranties.created_at', '<=' , $end_date)
                        ->orderBy('product_warranties.created_at', 'desc')
                        ->first();

                    $lims_warranty_data = Warranty::find($latest_record_warranty_product->warranty_id);
                   
                    $lims_warranty_product_all[$key]->product_name = "";
                    $lims_warranty_product_all[$key]->product_code = "";
                    $lims_product_data = Product::select('name', 'code')->find($warranty_product->product_id);
                    if($lims_product_data){
                        $lims_warranty_product_all[$key]->product_name = $lims_product_data->name;
                        $lims_warranty_product_all[$key]->product_code = $lims_product_data->code;
                        if($warranty_product->variant_id){
                            $lims_variant_data = Variant::find($warranty_product->variant_id);
                            $lims_product_variant = ProductVariant::where('product_id', $warranty_product->product_id)
                            ->where('variant_id', $warranty_product->variant_id)
                            ->first();
                            if($lims_variant_data){
                                $lims_warranty_product_all[$key]->product_name = $lims_variant_data->name;
                                $lims_warranty_product_all[$key]->product_code = $lims_product_variant->item_code;
                            }
                        }
                    }

                    $lims_warranty_product_all[$key]->created_at = $lims_warranty_data->created_at;
                    $lims_warranty_product_all[$key]->unit = "";
                    $unit_data = Unit::find($latest_record_warranty_product->unit_id);
                    if($unit_data){
                        $lims_warranty_product_all[$key]->unit = $unit_data->unit_code;
                    }

                }
            
                $collection = new Collection($lims_warranty_product_all);
                $lims_warranty_product_all = $collection->sortBy('created_at');

                array_push($data_customer, $lims_warranty_product_all);
                array_push($data_customer, $warranty_customer);
                array_push($data_customer, $limit_qty);
                array_push($lims_data, $data_customer);
            }
        } else {
            $lims_list_customer = Warranty::select('customer_id')
            ->whereDate('created_at', '>=' , $start_date)
            ->whereDate('created_at', '<=' , $end_date)
            ->groupBy('customer_id')
            ->get();
        
            foreach ($lims_list_customer as $key => $customer) {
                $warranty_customer = Customer::find($customer->customer_id);
                if($warranty_customer != null){
                    $data_customer = [];
                    $limit_qty = Warranty::where('customer_id', $customer->customer_id)
                        ->whereDate('created_at', '>=' , $start_date)
                        ->whereDate('created_at', '<=' , $end_date)
                        ->sum('total_qty');
                    $lims_warranty_product_all = DB::table('warranties')
                        ->join('product_warranties', 'warranties.id', '=', 'product_warranties.warranty_id')
                        ->select('product_warranties.product_id', 'product_warranties.variant_id', DB::raw('SUM(product_warranties.qty) as total_quantity'))
                        ->where('warranties.customer_id', $customer->customer_id)
                        ->whereDate('warranties.created_at', '>=', $start_date)
                        ->whereDate('warranties.created_at', '<=', $end_date)
                        ->groupBy('product_warranties.product_id', 'product_warranties.variant_id')
                        ->orderBy('warranties.created_at', 'asc')
                        ->get();
    
                    foreach ($lims_warranty_product_all as $key => $warranty_product) {
                        $latest_record_warranty_product = DB::table('product_warranties')
                            ->join('warranties', 'warranties.id', '=', 'product_warranties.warranty_id')
                            ->where('warranties.customer_id', $customer->customer_id)
                            ->where('product_warranties.product_id', $warranty_product->product_id)
                            ->where('product_warranties.variant_id', $warranty_product->variant_id)
                            ->whereDate('product_warranties.created_at', '>=' , $start_date)
                            ->whereDate('product_warranties.created_at', '<=' , $end_date)
                            ->orderBy('product_warranties.created_at', 'desc')
                            ->first();
    
                        $lims_warranty_data = Warranty::find($latest_record_warranty_product->warranty_id);
                       
                        $lims_warranty_product_all[$key]->product_name = "";
                        $lims_warranty_product_all[$key]->product_code = "";
                        $lims_product_data = Product::select('name', 'code')->find($warranty_product->product_id);
                        if($lims_product_data){
                            $lims_warranty_product_all[$key]->product_name = $lims_product_data->name;
                            $lims_warranty_product_all[$key]->product_code = $lims_product_data->code;
                            if($warranty_product->variant_id){
                                $lims_variant_data = Variant::find($warranty_product->variant_id);
                                $lims_product_variant = ProductVariant::where('product_id', $warranty_product->product_id)
                                ->where('variant_id', $warranty_product->variant_id)
                                ->first();
                                if($lims_variant_data){
                                    $lims_warranty_product_all[$key]->product_name = $lims_variant_data->name;
                                    $lims_warranty_product_all[$key]->product_code = $lims_product_variant->item_code;
                                }
                            }
                        }

                        $lims_warranty_product_all[$key]->created_at = $lims_warranty_data->created_at;
                        if($lims_warranty_data->type = "import")
                            $lims_warranty_product_all[$key]->voucher = $lims_warranty_data->inventory_receiving_voucher;
                        else 
                            $lims_warranty_product_all[$key]->voucher = $lims_warranty_data->inventory_delivery_voucher;
                        $lims_warranty_product_all[$key]->note = $lims_warranty_data->note;
                        $lims_warranty_product_all[$key]->unit = "";
                        $unit_data = Unit::find($latest_record_warranty_product->unit_id);
                        if($unit_data){
                            $lims_warranty_product_all[$key]->unit = $unit_data->unit_code;
                        }
    
                    }
                
                    $collection = new Collection($lims_warranty_product_all);
                    $lims_warranty_product_all = $collection->sortBy('created_at');
    
                    array_push($data_customer, $lims_warranty_product_all);
                    array_push($data_customer, $warranty_customer);
                    array_push($data_customer, $limit_qty);
                    array_push($lims_data, $data_customer);
                }
            } 
             
        }

        $lims_customer_list = Customer::where('is_active', true)->get();
        return view('report.warranty_all_customer', compact('customer_id', 'lims_customer_list', 'lims_data', 'lims_warranty_data', 'start_date', 'end_date'));
    }

    public function warrantyAllProductReport(Request $request)
    {
    	$data = $request->all();
        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
        $product_id = $data['product_id'];
        $lims_data = [];
        $lims_warranty_product_data = [];

        if($product_id != 0){
            $lims_list_product = ProductWarranty::select('product_id')
            ->where('product_id', $product_id)
            ->whereDate('created_at', '>=' , $start_date)
            ->whereDate('created_at', '<=' , $end_date)
            ->groupBy('product_id')
            ->get();
     
            foreach ($lims_list_product as $key => $product) {
                $product_data = Product::find($product->product_id);
                
                if($product_data != null ){
                    $data_product = [];
                    $lims_warranty_product_data = ProductWarranty::where('product_id', $product->product_id)                      
                        ->whereDate('created_at', '>=' , $start_date)
                        ->whereDate('created_at', '<=' , $end_date)
                        ->orderBy('created_at', 'asc')
                        ->get();
                        
                    $count_variant = 1;
                    if($product_data->is_variant == 1) {
                        $product_variant_data = ProductVariant::where('product_id', $product_data->id)->get();
                        $count_variant = count($product_variant_data);
                    }
                    $lims_total_cost = 0;
                    $lims_total_qty = 0;
              
                    if(count($lims_warranty_product_data) > 0){
                        foreach ($lims_warranty_product_data as $key => $warranty_product) {
                            $warranty_data = Warranty::where('id', $warranty_product->warranty_id)->first();
                            if($warranty_data){
                                $warranty_customer_data = Customer::find($warranty_data->customer_id);                              
        
                                if( $warranty_customer_data && $warranty_customer_data->phone_number){
                                    $warranty_customer_data->name .= ' ['.$warranty_customer_data->phone_number.']';
                                    $lims_warranty_product_data[$key]->customer = $warranty_customer_data->name;
                                }
                                else {
                                    $lims_warranty_product_data[$key]->customer = $warranty_customer_data == null ? '' : $warranty_customer_data->name;
                                }
        
                                $lims_warranty_product_data[$key]->content = $warranty_data->content ?? '';
                                $lims_total_qty += $warranty_product->qty;
                                $lims_total_cost += $warranty_product->total;
                            } else {
                                unset($lims_warranty_product_data[$key]);
                            }
                            
                        }
                    }

                    if(count($lims_warranty_product_data) > 0){
                        $product_data_name = ' [' . $product_data->code.'] ' . $product_data->name;
                        
                        array_push($data_product, $lims_warranty_product_data);
                        array_push($data_product, $product_data_name);
                        array_push($data_product, $lims_total_qty/$count_variant);
                        array_push($data_product, $lims_total_cost);
                        array_push($lims_data, $data_product);
                    }
                }
            } 

            
        } else{
            $lims_list_product = ProductWarranty::select('product_id')
            ->whereDate('created_at', '>=' , $start_date)
            ->whereDate('created_at', '<=' , $end_date)
            ->groupBy('product_id')
            ->get();

            foreach ($lims_list_product as $key => $product) {
                $product_data = Product::find($product->product_id);
                
                if($product_data != null ){
                    $data_product = [];
                    $lims_warranty_product_data = ProductWarranty::
                        where('product_id', $product->product_id)
                        ->whereDate('created_at', '>=' , $start_date)
                        ->whereDate('created_at', '<=' , $end_date)
                        ->orderBy('created_at', 'asc')
                        ->get();

                    $count_variant = 1;
                    if($product_data->is_variant == 1) {
                        $product_variant_data = ProductVariant::where('product_id', $product_data->id)->get();
                        $count_variant = count($product_variant_data);
                    }
                    $lims_total_cost = 0;
                    $lims_total_qty = 0;

                    if(count($lims_warranty_product_data) > 0){
                        foreach ($lims_warranty_product_data as $key => $warranty_product) {
                            $warranty_data = Warranty::where('id', $warranty_product->warranty_id)->first();
                            if($warranty_data ){
                                $warranty_customer_data = Customer::find($warranty_data->customer_id);

                                if( $warranty_customer_data && $warranty_customer_data->phone_number){
                                    $warranty_customer_data->name .= ' ['.$warranty_customer_data->phone_number.']';
                                    $lims_warranty_product_data[$key]->customer = $warranty_customer_data->name;
                                }
                                else {
                                    $lims_warranty_product_data[$key]->customer = $warranty_customer_data == null ? '' : $warranty_customer_data->name;
                                }
    
                                $lims_warranty_product_data[$key]->content = $warranty_data->content ?? '';
                                $lims_total_qty += $warranty_product->qty;
                                $lims_total_cost += $warranty_product->total;
                            } else {
                                unset($lims_warranty_product_data[$key]);
                            }
                        }
                    }

                    if(count($lims_warranty_product_data) > 0){
                        $product_data_name = ' [' . $product_data->code.'] ' . $product_data->name;
                        
                        array_push($data_product, $lims_warranty_product_data);
                        array_push($data_product, $product_data_name);
                        array_push($data_product, $lims_total_qty/$count_variant);
                        array_push($data_product, $lims_total_cost);
                        array_push($lims_data, $data_product);
                    }
                }
            } 
        }

        $lims_product_list = Product::where('is_active', true)->get();
        return view('report.warranty_all_product', compact('product_id', 'lims_product_list', 'lims_data', 'lims_warranty_product_data', 'start_date', 'end_date'));
    }

    public function supplierReport(Request $request)
    {
        $data = $request->all();
        $supplier_id = $data['supplier_id'];
        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
       
        if($supplier_id == 0){
            $lims_purchase_data = Purchase::with('warehouse')->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->orderBy('created_at', 'asc')->get();
            $lims_return_data = ReturnPurchase::with('warehouse')->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->orderBy('created_at', 'asc')->get();
            $lims_payment_data = DB::table('payments')
                            ->join('purchases', 'payments.purchase_id', '=', 'purchases.id')
                            ->whereDate('payments.created_at', '>=' , $start_date)
                            ->whereDate('payments.created_at', '<=' , $end_date)
                            ->select('payments.*', 'purchases.reference_no as purchase_reference')
                            ->orderBy('payments.created_at', 'asc')
                            ->get(); 
        } else {
            $lims_purchase_data = Purchase::with('warehouse')->where('supplier_id', $supplier_id)->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->orderBy('created_at', 'asc')->get();
            $lims_return_data = ReturnPurchase::with('warehouse')->where('supplier_id', $supplier_id)->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->orderBy('created_at', 'asc')->get();
            $lims_payment_data = DB::table('payments')
                               ->join('purchases', 'payments.purchase_id', '=', 'purchases.id')
                               ->where('supplier_id', $supplier_id)
                               ->whereDate('payments.created_at', '>=' , $start_date)
                               ->whereDate('payments.created_at', '<=' , $end_date)
                               ->select('payments.*', 'purchases.reference_no as purchase_reference')
                               ->orderBy('payments.created_at', 'asc')
                               ->get();
        }
        $lims_product_purchase_data = [];
        $lims_product_quotation_data = [];
        $lims_product_return_data = [];

        foreach ($lims_purchase_data as $key => $purchase) {
            $lims_product_purchase_data[$key] = ProductPurchase::where('purchase_id', $purchase->id)->get();
        }
        foreach ($lims_return_data as $key => $return) {
            $lims_product_return_data[$key] = PurchaseProductReturn::where('return_id', $return->id)->get();
        }
        $lims_supplier_list = Supplier::where('is_active', true)->get();
        return view('report.supplier_report', compact('lims_purchase_data', 'lims_product_purchase_data', 'lims_payment_data', 'supplier_id', 'start_date', 'end_date', 'lims_supplier_list', 'lims_return_data', 'lims_product_return_data'));
    }

    public function dueReportByDate(Request $request)
    {
    	$data = $request->all();
        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
        $lims_sale_data = Sale::where('payment_status', '!=', 4)->whereDate('created_at', '>=' , $start_date)->whereDate('created_at', '<=' , $end_date)->get();

        return view('report.due_report', compact('lims_sale_data', 'start_date', 'end_date'));
    }

    public function productAlert(Request $request)
    {
        $role = Role::find(Auth::user()->role_id);
        if($role->hasPermissionTo('product-qty-alert')){
            $lims_product_data = Product::select('name','code', 'image', 'qty', 'alert_quantity')->where('is_active', true)->whereColumn('alert_quantity', '>', 'qty')->get();
            // bán hàng thiết lập cảnh báo hàng giao quá 07 ngày chưa xuất hóa đơn
            $sevenDaysAgo = Carbon::now()->subDays(7);
            $lims_sale_data = Sale::with('customer')
                ->where('created_at', '<', $sevenDaysAgo)
                ->whereNull('biller_id')
                ->where('method', '=', NULL)
                ->orderBy('created_at', 'asc')
                ->get();

            // Hàng cho mượn quá 01 tháng thiết lập cảnh báo chưa lấy hàng về
            $monthAgo = Carbon::now()->subDays(30);
            $lims_return_data = Sale::with('customer')
                ->where('created_at', '<', $monthAgo)
                ->where('method', '=', 'return')
                ->orderBy('created_at', 'asc')
                ->get();

            // Hàng mượn của khách hàng quá 01 tháng thiết lập cảnh báo chưa trả hàng
            $lims_lend_data = Purchase::with('supplier')
                ->where('created_at', '<', $monthAgo)
                ->where('method', '=', 'return')
                ->orderBy('created_at', 'asc')
                ->get();

            // Hàng xuất đi sửa chữa, bảo hành quá 01 tháng thiết lập cảnh báo chưa nhập hàng về lại
            $lims_export_warranty_data = Warranty::with('customer')
                ->where('created_at', '<', $monthAgo)
                ->where('type', '=', 'export')
                ->orderBy('created_at', 'asc')
                ->get();
            return view('report.alert_report', compact('lims_product_data', 'lims_sale_data', 'lims_lend_data', 'lims_return_data', 'lims_export_warranty_data'));
        }
        else
            return redirect()->back()->with('not_permitted', 'Sorry! You are not allowed to access this module');
    }
}