<?php

namespace App\Http\Controllers\backend;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\Auth;
use Carbon\Carbon;
use App\Models\User;
use App\Models\Income;
use App\Models\Incometype;
use App\Models\Expensetype;
use App\Models\Expense;
use App\Models\Product;
use App\Models\Purchase;
use App\Models\PurchaseReturn;
use App\Models\PurchaseDetail;
use App\Models\PurchaseReturnDetail;
use App\Models\Supplier;
use App\Models\Sale;
use App\Models\SaleDetail;
use App\Models\SaleReturn;
use App\Models\SaleReturnDetail;
use App\Models\Customer;
use App\Models\SupplierAccount;
use App\Models\CustomerAccount;
use App\Models\CustomerLedger;
use App\Models\CustomerRecord;
use App\Models\Productcategory;
use App\Models\Type;
use App\Models\Wastage;
use DB;

class ReportController extends Controller
{
    function __construct(){
       //  $this->middleware('permission:report-village',['only'=>['ReportVIndex','ReportVSearch']]);
       //  $this->middleware('permission:report-user', ['only' => ['ReportUIndex','ReportUSearch']]);
       //  $this->middleware('permission:report-personal', ['only' => ['personalReport','personalReportSearch']]);
     //    $this->middleware('permission:report-fail', ['only' => ['dueInstallReport','dueInstallReportSearch']]);
        //$this->middleware('permission:report-income', ['only' => ['incomeReportIndex','incomeReportsSearch']]);
       // $this->middleware('permission:expense-report', ['only' => ['expenseReportIndex','expenseReportSearch']]);

    }
    public function incomeReportIndex(Request $request){
        $type = Incometype::orderBy('type_name','ASC')->get();
        $incomes = '';
        return view('backend.report.incomeReportIndex',compact('type','incomes'));
    }
    public function incomeReportSearch(Request $request){
          $from = $request->from_date;
          $to = $request->to_date;
  
          $from_date = date_create($request->from_date);
          $to_date = date_create($request->to_date);

            $type = Incometype::orderBy('type_name','ASC')->get();
           
            if($request->type_id == "All"){
                $ty = $request->type_id ;
                  $incomes = Income::whereDate('income_date', '>=', $from_date)
                              ->whereDate('income_date', '<=', $to_date)
                              ->get();
               }
               else{
                   $types = Incometype::find($request->type_id);
                   $ty = $types->type_name;
  
                  $incomes = Income::where('incometype_id',$request->type_id)
                              ->whereDate('income_date', '>=', $from_date)
                              ->whereDate('income_date', '<=', $to_date)
                              ->get();
                  }
            return view('backend.report.incomeReportIndex',compact('type','incomes','ty','from','to'));
    }
    public function expenseReportIndex(Request $request){
      $from =  date('Y-m-01') ;
      $to = date('Y-m-d');
      $ty = 'All';
      $expensetype = Expensetype::orderBy('type_name','ASC')->get();
      $expenses = Expense::whereDate('expense_date', '>=', $from)
                            ->whereDate('expense_date', '<=', $to)
                            ->get();
      return view('backend.report.expense.expenseReportIndex',compact('expensetype','expenses','from','to','ty'));
    }
    public function expenseReportSearch(Request $request){
            $from = $request->from_date;
            $to = $request->to_date;

          $from_date = date_create($request->from_date);
          $to_date = date_create($request->to_date);

          
          $expensetype = Expensetype::orderBy('type_name','ASC')->get();
          
          if($request->type_id == "All"){
              $ty = $request->type_id ;
                $expenses = Expense::whereDate('expense_date', '>=', $from_date)
                            ->whereDate('expense_date', '<=', $to_date)
                            ->get();
              }
              else{
                  $types = Expensetype::find($request->type_id);
                  $ty = $types->type_name;

                $expenses = Expense::where('expensetype_id',$request->type_id)
                            ->whereDate('expense_date', '>=', $from_date)
                            ->whereDate('expense_date', '<=', $to_date)
                            ->get();
                }
          return view('backend.report.expense.expenseReportIndex',compact('expensetype','expenses','ty','from','to'));
    }
    /*-
    * =>Chalan wise purchase report
    * cpri => c = chalan, p = purchase, r = report , i = index
    * cprs => c = chalan, p = purchase, r = report , s = search
    */
    public function cpri(Request $request){
      $chalans = DB::select("SELECT DISTINCT purchases.id, purchases.chalan_no from purchases ");
      $purchases = '';
      return view('backend.report.purchase.chalan_purchase_report_index',compact('chalans','purchases'));
    }
    public function cprs(Request $request){
          $from = $request->from_date;
          $to = $request->to_date;

          $from_date = date_create($request->from_date);
          $to_date = date_create($request->to_date);

          
          /*---------purchase report----------- */
        if($request->report_type == 'Purchase') {
          $chalans = DB::select("SELECT DISTINCT purchases.id, purchases.chalan_no from purchases ");
         
          if($request->chalan_no == "All"){
              $ty = $request->chalan_no ;
                $purchases = Purchase::whereDate('purchase_date', '>=', $from_date)
                            ->whereDate('purchase_date', '<=', $to_date)
                            ->get();
             }
             else{
                 //$types = Product::find($request->product_id);
                 $ty =  $request->chalan_no;

                $purchases = Purchase::where('id',$request->chalan_no)
                            ->whereDate('purchase_date', '>=', $from_date)
                            ->whereDate('purchase_date', '<=', $to_date)
                            ->get();
                }
                return view('backend.report.purchase.chalan_purchase_report_index',compact('chalans','purchases','ty','from','to'));
          }
          /*-------------Purchase return report---------------------*/
          if($request->report_type == 'Return') {

            $chalans = DB::select("SELECT DISTINCT purchases.id, purchases.chalan_no from purchases ");
           
            if($request->chalan_no == "All"){
                $ty = $request->chalan_no ;
                  $purchases = PurchaseReturn::whereDate('return_date', '>=', $from_date)
                              ->whereDate('return_date', '<=', $to_date)
                              ->get();
               }
               else{
                   //$types = Product::find($request->product_id);
                   $ty =  $request->chalan_no;
  
                  $purchases = PurchaseReturn::where('chalan_no',$request->chalan_no)
                              ->whereDate('return_date', '>=', $from_date)
                              ->whereDate('return_date', '<=', $to_date)
                              ->get();
                  }
                  return view('backend.report.purchase.chalan_return_report_index',compact('chalans','purchases','ty','from','to'));
            }
          
    }
    /*-
    * pri => p = purchase, r = report , i = index
    * prs => p = purchase, r = report , s = search
    */
    public function pri(Request $request){
     
        $products = DB::select("SELECT DISTINCT products.product_name,products.code,purchase_details.product_id from products,purchase_details where products.id = purchase_details.product_id");
        $purchases = '';
        return view('backend.report.purchase.product_purchase_report_index',compact('products','purchases'));
    }
    public function prs(Request $request){
          $from = $request->from_date;
          $to = $request->to_date;

          $from_date = date_create($request->from_date);
          $to_date = date_create($request->to_date);

         
          /*----------purchase report----------------*/
        if($request->report_type == 'Purchase'){
          $products = DB::select("SELECT DISTINCT products.product_name,products.code,purchase_details.product_id from products,purchase_details where products.id = purchase_details.product_id");
          
          if($request->product_id == "All"){
              $ty = $request->product_id ;
                $purchases = PurchaseDetail::whereDate('purchase_date', '>=', $from_date)
                            ->whereDate('purchase_date', '<=', $to_date)
                            ->get();
              }
              else{
                  $types = Product::find($request->product_id);
                  $ty = $types->product_name;

                $purchases = PurchaseDetail::where('product_id',$request->product_id)
                            ->whereDate('purchase_date', '>=', $from_date)
                            ->whereDate('purchase_date', '<=', $to_date)
                            ->get();
                }
          return view('backend.report.purchase.product_purchase_report_index',compact('products','purchases','ty','from','to'));
      }
       /*----------purchase return report----------------*/
       if($request->report_type == 'Return'){
        $products = DB::select("SELECT DISTINCT products.product_name,purchase_details.model_no,purchase_details.product_id from products,purchase_details where products.id = purchase_details.product_id");
        
        if($request->product_id == "All"){
            $ty = $request->product_id ;
              $purchases = PurchaseReturnDetail::whereDate('return_date', '>=', $from_date)
                          ->whereDate('return_date', '<=', $to_date)
                          ->get();
            }
            else{
                $types = Product::find($request->product_id);
                $ty = $types->product_name;

              $purchases = PurchaseReturnDetail::where('product_id',$request->product_id)
                          ->whereDate('return_date', '>=', $from_date)
                          ->whereDate('return_date', '<=', $to_date)
                          ->get();
              }
        return view('backend.report.purchase.product_purchase_return_report_index',compact('products','purchases','ty','from','to'));
      }
    }
    /*-
    * => supplier wise purchase report
    * spri => s = supplier, p = purchase, r = report , i = index
    * sprs => s = supplier, p = purchase, r = report , s = search
    */
    public function spri(Request $request){
      $suppliers = DB::select("SELECT DISTINCT suppliers.name as supplier_name,purchases.supplier_id from suppliers,purchases where suppliers.id = purchases.supplier_id");
      $purchases = '';
      return view('backend.report.purchase.supplier_chalan_report_index',compact('suppliers','purchases'));
    }
    public function sprs(Request $request){
          $from = $request->from_date;
          $to = $request->to_date;

          $from_date = date_create($request->from_date);
          $to_date = date_create($request->to_date);

          
          $suppliers = DB::select("SELECT DISTINCT suppliers.name as supplier_name,purchases.supplier_id from suppliers,purchases where suppliers.id = purchases.supplier_id");
        /*----------purchase report supplier wise------------------- */
        if($request->report_type == 'Chalan') {
          if($request->supplier_id == "All"){
              $ty = $request->supplier_id ;
                $purchases = Purchase::whereDate('purchase_date', '>=', $from_date)
                            ->whereDate('purchase_date', '<=', $to_date)
                            ->get();
             }
             else{
                 $types = Supplier::find($request->supplier_id);
                 $ty = $types->name;
                $purchases = Purchase::where('supplier_id',$request->supplier_id)
                            ->whereDate('purchase_date', '>=', $from_date)
                            ->whereDate('purchase_date', '<=', $to_date)
                            ->get();
                }
          return view('backend.report.purchase.supplier_chalan_report_index',compact('suppliers','purchases','ty','from','to'));
        }
        if($request->report_type == 'Product') {
          if($request->supplier_id == "All"){
              $ty = $request->supplier_id ;
                $purchases = PurchaseDetail::whereDate('purchase_date', '>=', $from_date)
                            ->whereDate('purchase_date', '<=', $to_date)
                            ->get();
             }
             else{
                 $types = Supplier::find($request->supplier_id);
                 $ty = $types->name;
                $purchases = PurchaseDetail::where('supplier_id',$request->supplier_id)
                            ->whereDate('purchase_date', '>=', $from_date)
                            ->whereDate('purchase_date', '<=', $to_date)
                            ->get();
                }
          return view('backend.report.purchase.supplier_product_report_index',compact('suppliers','purchases','ty','from','to'));
        }
        /*-----------purchase return report supplier wise----------------*/
      /*  if($request->report_type == 'Return') {
          if($request->supplier_id == "All"){
              $ty = $request->supplier_id ;
                $purchases = PurchaseReturnDetail::whereDate('return_date', '>=', $from_date)
                            ->whereDate('return_date', '<=', $to_date)
                            ->get();
             }
             else{
                 $types = Supplier::find($request->supplier_id);
                 $ty = $types->supplier_name;

                $purchases = PurchaseReturnDetail::where('supplier_id',$request->supplier_id)
                            ->whereDate('return_date', '>=', $from_date)
                            ->whereDate('return_date', '<=', $to_date)
                            ->get();
                }
          return view('backend.report.purchase.supplier_return_report_index',compact('suppliers','purchases','ty','from','to'));
        }*/
        if($request->report_type == 'Payment') {
          if($request->supplier_id == "All"){
              $ty = $request->supplier_id ;
                $purchases = SupplierAccount::whereDate('payment_date', '>=', $from_date)
                            ->whereDate('payment_date', '<=', $to_date)
                            ->get();
             }
             else{
                 $types = Supplier::find($request->supplier_id);
                 $ty = $types->name;

                $purchases = SupplierAccount::where('supplier_id',$request->supplier_id)
                            ->whereDate('payment_date', '>=', $from_date)
                            ->whereDate('payment_date', '<=', $to_date)
                            ->get();
                }
          return view('backend.report.purchase.supplier_payment_report_index',compact('suppliers','purchases','ty','from','to'));
        }
    }
    /*-
    * isri => i = invoice, s = sale, r = report , i = index
    * isrs => i = invoice, s = sale, r = report , s = search
    */
    public function isri(Request $request){
      
      $invoices = DB::select("SELECT DISTINCT sales.id, sales.invoice_no from sales ");
      $sales = '';
      return view('backend.report.sale.invoice_report_index',compact('invoices','sales'));
    }
    public function isrs(Request $request){
          $from = $request->from_date;
          $to = $request->to_date;

          $from_date = date_create($request->from_date);
          $to_date = date_create($request->to_date);

          
          $invoices = DB::select("SELECT DISTINCT sales.id, sales.invoice_no from sales ");
        /*--------------invoice wise sale report-----------------------*/
        if($request->report_type == 'Sale') {
          if($request->invoice_no == "All"){
              $ty = $request->invoice_no ;
                $sales = Sale::whereDate('sale_date', '>=', $from_date)
                            ->whereDate('sale_date', '<=', $to_date)
                            ->orderBy('sale_date')
                            ->get();
             }
             else{
                 //$types = Product::find($request->product_id);
                 $ty =  $request->invoice_no;

                $sales = Sale::where('invoice_no',$request->invoice_no)
                            ->whereDate('sale_date', '>=', $from_date)
                            ->whereDate('sale_date', '<=', $to_date)
                            ->orderBy('sale_date')
                            ->get();
                }
          return view('backend.report.sale.invoice_report_index',compact('invoices','sales','ty','from','to'));
        }
        /*--------------------invoice wise return report-------------------------*/
        if($request->report_type == 'Return') {
          if($request->invoice_no == "All"){
              $ty = $request->invoice_no ;
                $sales = SaleReturn::whereDate('return_date', '>=', $from_date)
                            ->whereDate('return_date', '<=', $to_date)
                            ->get();
             }
             else{
                 //$types = Product::find($request->product_id);
                 $ty =  $request->invoice_no;

                $sales = SaleReturn::where('invoice_no',$request->invoice_no)
                            ->whereDate('return_date', '>=', $from_date)
                            ->whereDate('return_date', '<=', $to_date)
                            ->get();
                }
          return view('backend.report.sale.invoice_return_report_index',compact('invoices','sales','ty','from','to'));
        }

    }
    /*-
    * psri => p = product, s = sale, r = report , i = index
    * psrs => p = product, s = sale, r = report , s = search
    */
    public function psri(Request $request){
        $products = DB::select("SELECT DISTINCT products.product_name,products.code,sale_details.product_id from products,sale_details where products.id = sale_details.product_id");
        $sales = '';
        return view('backend.report.sale.sale_report_index',compact('products','sales'));
    }
    public function psrs(Request $request){
          $from = $request->from_date;
          $to = $request->to_date;

          $from_date = date_create($request->from_date);
          $to_date = date_create($request->to_date);

         
          $products = DB::select("SELECT DISTINCT products.product_name,products.code,sale_details.product_id from products,sale_details where products.id = sale_details.product_id");
        /*------------------sale Report product wise------------------------*/
          if($request->report_type == 'Sale')  {
            if($request->product_id == "All"){
              $ty = $request->product_id ;
                $sales = SaleDetail::whereDate('sale_date', '>=', $from_date)
                            ->whereDate('sale_date', '<=', $to_date)
                            ->get();
            }else{
                $types = Product::find($request->product_id);
                $ty = $types->product_name;

                $sales = SaleDetail::where('product_id',$request->product_id)
                            ->whereDate('sale_date', '>=', $from_date)
                            ->whereDate('sale_date', '<=', $to_date)
                            ->get();
            }
          return view('backend.report.sale.sale_report_index',compact('products','sales','ty','from','to'));
        }
        /*------------------Sale Return Report product wise------------------------*/
        if($request->report_type == 'Return')  {
          if($request->product_id == "All"){
              $ty = $request->product_id ;
                $sales = SaleReturnDetail::whereDate('return_date', '>=', $from_date)
                            ->whereDate('return_date', '<=', $to_date)
                            ->get();
              }
              else{
                  $types = Product::find($request->product_id);
                  $ty = $types->product_name;

                $sales = SaleReturnDetail::where('product_id',$request->product_id)
                            ->whereDate('return_date', '>=', $from_date)
                            ->whereDate('return_date', '<=', $to_date)
                            ->get();
                }
          return view('backend.report.sale.sale_return_report_index',compact('products','sales','ty','from','to'));
        }
    }
    /*-
    * csri => c = customer, s = sale, r = report , i = index
    * csrs => c = customer, s = sale, r = report , s = search
    */
    public function csri(Request $request){
      $customers = DB::select("SELECT DISTINCT customers.name as customer_name,sales.customer_id from customers,sales where customers.id = sales.customer_id");
      $types = Type::all();
      $reports = '';
      return view('backend.report.customer_report',compact('customers','reports','types'));
    }
    public function csrs(Request $request){
        
        if($request->from_date != '' || $request->to_date != ''){
            $from_date = date_create($request->from_date);
            $to_date = date_create($request->to_date);
            $from = date('Y-m-d',strtotime($request->from_date));
            $to = date('Y-m-d',strtotime($request->to_date));
        }else{
            $from_date = date_create(date('01-m-Y'));
            $to_date = date_create(date('d-m-Y'));
            $from = date('01-m-Y');
            $to = date('d-m-Y');
        }
        $types = Type::all();
        $customers = DB::select("SELECT DISTINCT customers.name as customer_name,sales.customer_id from customers,sales where customers.id = sales.customer_id");
        /*-------------sale report customer wise---------------------*/
        if($request->report_type == 'Invoice'){ 
          if($request->customer_id == "All"){
              $ty = $request->customer_id ;
              /*$sales = Sale::whereHas('customer', function ($query) {
                    $query->orderBy('name','ASC');
                })->whereDate('sale_date', '>=', $from_date)->whereDate('sale_date', '<=', $to_date)->orderBy('sale_date','ASC')->get();
                
             $sales =   Sale::whereHas('customer', function ($query) {
                        $query->orderBy('name', 'ASC');
                    })
                    ->whereDate('sale_date', '>=', $from_date)
                    ->whereDate('sale_date', '<=', $to_date)
                    ->orderBy('sale_date', 'ASC') // Move orderBy to the main query
                    ->get();*/
                /*$reports = Sale::join('customers', 'sales.customer_id', '=', 'customers.id')
                    ->whereDate('sales.sale_date', '>=', $from_date)
                    ->whereDate('sales.sale_date', '<=', $to_date)
                    ->orderBy('customers.name', 'ASC')
                    ->orderBy('sales.sale_date', 'ASC')
                    ->get();*/
                if($request->type_id != ''){    
                    $reports = Sale::join('customers', 'sales.customer_id', '=', 'customers.id')
                        ->whereDate('sales.sale_date', '>=', $from_date)
                        ->whereDate('sales.sale_date', '<=', $to_date)
                        ->where('sales.type_id',$request->type_id)
                        ->orderBy('customers.name', 'ASC')
                        ->orderBy('sales.sale_date', 'ASC')
                        ->get();
                }else{//this is only PVC 
                    $reports = Sale::join('customers', 'sales.customer_id', '=', 'customers.id')
                        ->whereDate('sales.sale_date', '>=', $from_date)
                        ->whereDate('sales.sale_date', '<=', $to_date)
                        ->orderBy('customers.name', 'ASC')
                        ->orderBy('sales.sale_date', 'ASC')
                        ->get();
                }

            }else{
                $ty = Customer::find($request->customer_id);
                //$ty = $types->name;
                if($request->type_id != ''){
                    $reports = Sale::where('customer_id',$request->customer_id)->where('type_id',$request->type_id)->whereDate('sale_date', '>=', $from_date)->whereDate('sale_date', '<=', $to_date)->orderBy('sale_date','ASC')->get();
                }else{
                    $reports = Sale::where('customer_id',$request->customer_id)->whereDate('sale_date', '>=', $from_date)->whereDate('sale_date', '<=', $to_date)->orderBy('sale_date','ASC')->get();   
                }
             }
          return view('backend.report.customer_report',compact('customers','types','reports','ty','from','to'));
        }
        if($request->report_type == 'Sales_Due'){ 
            if($request->customer_id == "All"){
                $ty = $request->customer_id ;
                $reports = Sale::where('type_id',$request->type_id)->where('due_amount','>',0)->whereDate('sale_date', '>=', $from_date)->whereDate('sale_date', '<=', $to_date)->orderBy('sale_date','ASC')->get();
            }else{
                $ty = Customer::find($request->customer_id);
                //$ty = $types->name;
                $reports = Sale::where('type_id',$request->type_id)->where('customer_id',$request->customer_id)->where('due_amount','>',0)->whereDate('sale_date', '>=', $from_date)->whereDate('sale_date', '<=', $to_date)->orderBy('sale_date','ASC')->get();
            }
          return view('backend.report.customer_report',compact('customers','types','reports','ty','from','to'));
        }
        if($request->report_type == 'All_Due'){ 
            if($request->customer_id == "All"){
              $ty = $request->customer_id ;
              $reports = DB::select("SELECT c.name,c.phone,c.address,SUM(s.grand_total_price) as total_price,SUM(s.payment) as payment,sum(s.due_amount) as due_amount FROM `customers` AS c, sales as s WHERE s.type_id = $request->type_id AND s.customer_id = c.id AND s.due_amount > 0 GROUP by s.customer_id ");
            }
            return view('backend.report.customer_report',compact('customers','types','reports','ty','from','to'));
        }
        if($request->report_type == 'Product'){ 
            if($request->customer_id === "All"){
                $ty = $request->customer_id ;
                $reports = SaleDetail::where('type_id',$request->type_id)->whereDate('sale_date', '>=', $from_date)->whereDate('sale_date', '<=', $to_date)->orderBy('sale_date','ASC')->get();
            }else{
                $ty = Customer::find($request->customer_id);
                $reports = SaleDetail::where('customer_id',$request->customer_id)->where('type_id',$request->type_id)->whereDate('sale_date', '>=', $from_date)->whereDate('sale_date', '<=', $to_date)->orderBy('sale_date','ASC')->get();
            }
            return view('backend.report.customer_report',compact('customers','types','reports','ty','from','to'));
        }
        if($request->report_type == 'Return'){ 
          if($request->customer_id == "All"){
              $ty = $request->customer_id ;
              $reports = SaleReturnDetail::whereDate('return_date', '>=', $from_date)->whereDate('return_date', '<=', $to_date)->latest('return_date')->get();
             }
             else{
                $types = Customer::find($request->customer_id);
                $ty = $types->name;
                $reports = SaleReturnDetail::where('customer_id',$request->customer_id)->whereDate('return_date', '>=', $from_date)->whereDate('return_date', '<=', $to_date)->latest('return_date')->get();
              }
          return view('backend.report.customer_report',compact('customers','types','reports','ty','from','to'));
        }
        if($request->report_type == 'Payment'){ 
            if($request->customer_id == "All"){
              $ty = $request->customer_id ;
              //$sales = CustomerAccount::whereDate('payment_date', '>=', $from_date)->whereDate('payment_date', '<=', $to_date)->orderBy('payment_date','ASC')->get();
              $sales = DB::select("SELECT p.payment_date, c.name,SUM(p.amount) AS amount,p.note FROM `customer_accounts` AS p, customers AS c WHERE c.id = p.customer_id AND p.payment_date >= '$from' AND p.payment_date <= '$to' GROUP BY p.customer_id ");
              $reports = collect($sales);
            }else{
                $ty = Customer::find($request->customer_id);
                //$ty = $types->name;
                $reports = CustomerAccount::where('customer_id',$request->customer_id)->whereDate('payment_date', '>=', $from_date)->whereDate('payment_date', '<=', $to_date)->orderBy('payment_date','ASC')->get();
            }
            //return $sales;
          return view('backend.report.customer_report',compact('customers','types','reports','ty','from','to'));
        }
        if($request->report_type == 'Statement'){
            if($request->customer_id == "All"){
                $ty = $request->customer_id ;
                $reports = CustomerLedger::whereDate('transaction_date', '>=', $from_date)->whereDate('transaction_date', '<=', $to_date)->orderBy('transaction_date','ASC')->orderBy('transaction_date')->get();
            }else{
                $ty = Customer::find($request->customer_id);
                $reports = CustomerLedger::where('customer_id',$request->customer_id)->whereDate('transaction_date', '>=', $from_date)->whereDate('transaction_date', '<=', $to_date)->orderBy('transaction_date')->get();
            }
            //return $new;
            return view('backend.report.customer_report',compact('customers','types','reports','ty','from','to'));
        }
        if($request->report_type == 'Record'){ 
            if($request->customer_id == "All"){
                $ty = $request->customer_id ;
                $reports = CustomerRecord::whereDate('transaction_date', '>=', $from_date)->whereDate('transaction_date', '<=', $to_date)->orderBy('transaction_date','ASC')->get();
            }else{
                $ty = Customer::find($request->customer_id);
                $reports = CustomerRecord::where('customer_id',$request->customer_id)->whereDate('transaction_date', '>=', $from_date)->whereDate('transaction_date', '<=', $to_date)->orderBy('id','ASC')->get();
            }
          return view('backend.report.customer_report',compact('customers','types','reports','ty','from','to'));
        }
    }
    /**
     * wastage return 
     */
    /*-
    * pri => p = purchase, r = report , i = index
    * prs => p = purchase, r = report , s = search
    */
    public function wri(Request $request){
      $products = DB::select("SELECT DISTINCT products.product_name,products.code,purchase_details.product_id from products,purchase_details where products.id = purchase_details.product_id");
      $purchases = '';
      return view('backend.report.wastage.wastage_report_index',compact('products','purchases'));
    }
    public function wrs(Request $request){
        $from = $request->from_date;
        $to = $request->to_date;

        $from_date = date_create($request->from_date);
        $to_date = date_create($request->to_date);

        
        $products = DB::select("SELECT DISTINCT products.product_name,products.code,purchase_details.product_id from products,purchase_details where products.id = purchase_details.product_id");
        
        if($request->product_id == "All"){
            $ty = $request->product_id ;
              $purchases = Wastage::whereDate('return_date', '>=', $from_date)
                          ->whereDate('return_date', '<=', $to_date)
                          ->get();
            }
            else{
                $types = Product::find($request->product_id);
                $ty = $types->product_name;

              $purchases = Wastage::where('product_id',$request->product_id)
                          ->whereDate('return_date', '>=', $from_date)
                          ->whereDate('return_date', '<=', $to_date)
                          ->get();
              }
        return view('backend.report.wastage.wastage_report_index',compact('products','purchases','ty','from','to'));
  }
    /*--------category wise sale and purchase report-----------*/
    public function category_report(Request $request){
      $categories = Productcategory::where('status',1)->get();
      $reports = '';
      return view('backend.report.category.category_report',compact('categories','reports'));
    }
    public function category_reports(Request $request){
        $from = date('Y-m-d',strtotime($request->from_date));
        $to = date('Y-m-d',strtotime($request->to_date));

        $from_date = date_create($request->from_date);
        $to_date = date_create($request->to_date);

        $categories = Productcategory::where('status',1)->get();
        $report_type = $request->report_type;

        if($request->report_type == 'Purchase'){
          if($request->category_id == "All"){
            $ty = $request->category_id;
            $reports = PurchaseDetail::whereDate('purchase_date', '>=', $from_date)->whereDate('purchase_date', '<=', $to_date)->get();
          }else{
            $types = Productcategory::find($request->category_id);
            $ty = $types->name;
            $reports = PurchaseDetail::where('productcategory_id',$request->category_id)->whereDate('purchase_date', '>=', $from_date)->whereDate('purchase_date', '<=', $to_date)->get();
          }
          return view('backend.report.category.category_report',compact('categories','reports','ty','report_type','from','to'));
        }
        if($request->report_type == 'Sale'){
          if($request->category_id == "All"){
            $ty = $request->category_id ;
            $reports = SaleDetail::whereDate('sale_date', '>=', $from_date)->whereDate('sale_date', '<=', $to_date)->get();
          }else{
            $types = Productcategory::find($request->category_id);
            $ty = $types->name;
            $reports = SaleDetail::where('productcategory_id',$request->category_id)->whereDate('sale_date', '>=', $from_date)->whereDate('sale_date', '<=', $to_date)->get();
          }
          return view('backend.report.category.category_report',compact('categories','reports','ty','report_type','from','to'));
        }
        if($request->report_type == 'PurchaseSale'){
            if($request->category_id == "All"){
                $ty = $request->category_id ;
                $reports = SaleDetail::whereDate('sale_date', '>=', $from_date)->whereDate('sale_date', '<=', $to_date)->get();
            }else{
                $types = Productcategory::find($request->category_id);
                $ty = $types->name;
                if($request->view_type == 'Daily'){
                    $sum = array();
                    $purchases = DB::select("SELECT p.product_name, p.code, c.name,pd.product_id,pd.purchase_date, pd.rate, SUM(pd.square_foot) AS square_foot, SUM(pd.now_stock) AS now_stock FROM `purchase_details` AS pd, products AS p, productcategories AS c WHERE pd.purchase_date >= '$from' AND pd.purchase_date <= '$to' AND c.id = pd.productcategory_id AND pd.productcategory_id = $request->category_id AND pd.product_id = p.id GROUP BY pd.purchase_date,pd.product_id");
                    foreach($purchases as $p){
                        $sum[] = ['transaction_date'=>$p->purchase_date ,'product_id'=>$p->product_id ,'product_name' =>$p->product_name, 'pcode' => $p->code,'category_name' => $p->name,'psquare_foot'=> $p->square_foot ,'now_stock'=> $p->now_stock,'purchase_price' =>$p->rate,'unit_price'=>0,'ssquare_foot'=>0,'sale_price'=>0,'sale_purchase_price'=>0,'profit'=>0];
                    }
                    /*$stocks = DB::select("SELECT p.product_name, p.code, c.name,pd.product_id,pd.purchase_date, pd.rate, SUM(pd.square_foot) AS square_foot, SUM(pd.now_stock) AS now_stock FROM `purchase_details` AS pd, products AS p, productcategories AS c WHERE pd.purchase_date >= '$from' AND pd.purchase_date <= '$to' AND c.id = pd.productcategory_id AND pd.productcategory_id = $request->category_id AND pd.product_id = p.id GROUP BY pd.purchase_date,pd.product_id");
                    foreach($stocks as $stock){
                        $sum[] = ['transaction_date'=>$p->purchase_date ,'product_id'=>$p->product_id ,'product_name' =>$p->product_name, 'pcode' => $p->code,'category_name' => $p->name,'psquare_foot'=> $p->square_foot ,'now_stock'=> $p->now_stock,'purchase_price' =>$p->rate,'unit_price'=>0,'ssquare_foot'=>0,'sale_price'=>0,'sale_purchase_price'=>0,'profit'=>0];
                    }*/
                    $sales = DB::select("SELECT p.product_name, p.code, c.name,pd.product_id, pd.rate,sd.sale_date,sd.unit_price, SUM(sd.tsqf) AS square_foot,SUM(sd.tsqf * pd.rate) AS purchase_price, SUM(sd.tsqf * sd.unit_price) AS sale_price FROM `sale_details` AS sd, products AS p, productcategories AS c,purchase_details AS pd WHERE sd.sale_date >= '$from' AND sd.sale_date <= '$to' AND c.id = sd.productcategory_id AND sd.productcategory_id = $request->category_id AND sd.product_id = p.id AND pd.id = sd.purchase_detail_id GROUP BY sd.sale_date,sd.product_id");
                    foreach($sales as $s){
                        $sum[] = ['transaction_date'=>$s->sale_date ,'product_id'=>$s->product_id ,'product_name' =>$s->product_name, 'pcode' => $s->code,'category_name' => $s->name,'psquare_foot'=> 0 ,'now_stock'=> 0,'purchase_price' =>0,'unit_price'=>$s->unit_price,'ssquare_foot'=>$s->square_foot,'sale_price'=>$s->sale_price,'sale_purchase_price'=> $s->purchase_price,'profit'=>$s->sale_price - $s->purchase_price ];
                    }
                   /* $reports = array() ;
                    foreach ($sum as $traffic){
                        $key = $traffic['product_id'] ;
                        $key = $traffic['transaction_date'] ;
                        if (isset($reports[$key])){
                            if ($reports[$key]['product_id'] == $traffic['product_id'] && $reports[$key]['transaction_date'] == $traffic['transaction_date']){
                                $reports[$key]['psquare_foot'] += $traffic['psquare_foot'];
                                $reports[$key]['now_stock'] += $traffic['now_stock'];
                                $reports[$key]['purchase_price'] += $traffic['purchase_price'];
                                $reports[$key]['sale_purchase_price'] += $traffic['sale_purchase_price'];
                                $reports[$key]['ssquare_foot'] += $traffic['ssquare_foot'];
                                $reports[$key]['sale_price'] += $traffic['sale_price'];
                                $reports[$key]['profit'] += $traffic['profit'];
                            }
                        } else {
                            $reports[$key] = $traffic ;
                        }
                    }*/
                    //$reports = collect($reports);
                    $reports =  collect($sum)->sortBy('transaction_date')->groupBy('transaction_date');
                }
                if($request->view_type == 'Monthly'){
                    $sum = array();
                    $purchases = DB::select("SELECT p.product_name, p.code, c.name, pd.rate, SUM(pd.square_foot) AS square_foot, SUM(pd.now_stock) AS now_stock FROM `purchase_details` AS pd, products AS p, productcategories AS c WHERE pd.purchase_date >= '$from' AND pd.purchase_date <= '$to' AND c.id = pd.productcategory_id AND pd.productcategory_id = $request->category_id AND pd.product_id = p.id GROUP BY pd.product_id");
                    foreach($purchases as $p){
                        $sum[] = ['product_name' =>$p->product_name, 'pcode' => $p->code,'category_name' => $p->name,'psquare_foot'=> $p->square_foot ,'now_stock'=> $p->now_stock,'purchase_price' =>$p->rate,'unit_price'=>0,'ssquare_foot'=>0,'sale_price'=>0,'sale_purchase_price'=>0,'profit'=>0];
                    }
                    $sales = DB::select("SELECT p.product_name, p.code, c.name, pd.rate,sd.unit_price, SUM(sd.tsqf) AS square_foot,SUM(sd.tsqf * pd.rate) AS purchase_price, SUM(sd.tsqf * sd.unit_price) AS sale_price FROM `sale_details` AS sd, products AS p, productcategories AS c,purchase_details AS pd WHERE sd.sale_date >= '$from' AND sd.sale_date <= '$to' AND c.id = sd.productcategory_id AND sd.productcategory_id = $request->category_id AND sd.product_id = p.id AND pd.id = sd.purchase_detail_id GROUP BY sd.product_id");
                    foreach($sales as $s){
                        $sum[] = ['product_name' =>$s->product_name, 'pcode' => $s->code,'category_name' => $s->name,'psquare_foot'=> 0 ,'now_stock'=> 0,'purchase_price' =>0,'unit_price'=>$s->unit_price,'ssquare_foot'=>$s->square_foot,'sale_price'=>$s->sale_price,'sale_purchase_price'=> $s->purchase_price,'profit'=>$s->sale_price - $s->purchase_price ];
                    }
                    $reports = array() ;
                    foreach ($sum as $traffic){
                        $key = $traffic['pcode'] ;
                        if (isset($reports[$key])){
                            if ($reports[$key]['pcode'] == $traffic['pcode']){
                                $reports[$key]['psquare_foot'] += $traffic['psquare_foot'];
                                $reports[$key]['now_stock'] += $traffic['now_stock'];
                                $reports[$key]['purchase_price'] += $traffic['purchase_price'];
                                $reports[$key]['sale_purchase_price'] += $traffic['sale_purchase_price'];
                                $reports[$key]['ssquare_foot'] += $traffic['ssquare_foot'];
                                $reports[$key]['sale_price'] += $traffic['sale_price'];
                                $reports[$key]['profit'] += $traffic['profit'];
                            }
                        } else {
                            $reports[$key] = $traffic ;
                        }
                    }
                    $reports = collect($reports);
                }
            }
            return view('backend.report.category.category_report',compact('categories','reports','ty','report_type','from','to'));
        }
        if($request->report_type == 'Customer'){
          if($request->category_id == "All"){
            $ty = $request->category_id ;
            $reports = SaleDetail::whereDate('sale_date', '>=', $from_date)->whereDate('sale_date', '<=', $to_date)->get();
          }else{
            $types = Productcategory::find($request->category_id);
            $ty = $types->name;
            $rp  = DB::select("SELECT c.name, c.phone, c.address, SUM(s.grand_total_price) AS total_price, SUM(s.payment) AS payment, SUM(s.due_amount) AS due_amount FROM `customers` AS c, sales AS s WHERE c.id = s.customer_id AND s.id IN(SELECT sd.sale_id FROM sale_details AS sd WHERE sd.productcategory_id = $request->category_id) GROUP BY s.customer_id ");
            $reports = collect($rp)->where('due_amount','>',0);
          }
          return view('backend.report.category.category_report',compact('categories','reports','ty','report_type','from','to'));
        }
    }
    /*--------category wise sale and purchase report-----------*/
    public function daily_report(Request $request){
      $reports = '';
      return view('backend.report.daily_report',compact('reports'));
    }
    public function daily_reports(Request $request){
        $from = date('Y-m-d',strtotime($request->from_date));
        $to = date('Y-m-d',strtotime($request->to_date));

        $sum = array();
        $sales = DB::select("SELECT sl.sale_date,SUM(sl.grand_total_price) as sale_price FROM sales AS sl WHERE sl.sale_date >= '$from' AND sl.sale_date <= '$to' GROUP BY sl.sale_date;");
        foreach($sales as $sl){
            $sum[] = ['report_date'=>$sl->sale_date, 'sale_total' =>$sl->sale_price,'customer_payment' => 0,'purchase_total' => 0, 'supplier_payment' => 0,'expense_total'=>0];
        }
        $purchases = DB::select("SELECT pur.purchase_date,SUM(pur.grand_total) as purchase_price FROM purchases AS pur WHERE pur.purchase_date >= '$from' AND pur.purchase_date <= '$to' GROUP BY pur.purchase_date;");
        foreach($purchases as $pur){
            $sum[] = ['report_date'=>$pur->purchase_date, 'sale_total' =>0,'customer_payment' => 0,'purchase_total' => $pur->purchase_price, 'supplier_payment' => 0,'expense_total'=>0];
        }
        $cpayments = DB::select("SELECT cp.payment_date,SUM(cp.amount) as payment_amount FROM customer_accounts AS cp WHERE cp.payment_date >= '$from' AND cp.payment_date <= '$to' GROUP BY cp.payment_date;");
        foreach($cpayments as $cp){
            $sum[] = ['report_date'=>$cp->payment_date, 'sale_total' =>0,'customer_payment' => $cp->payment_amount,'purchase_total' => 0, 'supplier_payment' => 0,'expense_total'=>0];
        }
        $spayments = DB::select("SELECT sp.payment_date,SUM(sp.amount) as spayment FROM supplier_accounts AS sp WHERE sp.payment_date >= '$from' AND sp.payment_date <= '$to' GROUP BY sp.payment_date;");
        foreach($spayments as $sp){
            $sum[] = ['report_date'=>$sp->payment_date, 'sale_total' =>0,'customer_payment' => 0,'purchase_total' => 0, 'supplier_payment' => $sp->spayment,'expense_total'=>0];
        }
        $expenses = DB::select("SELECT exp.expense_date,SUM(exp.expense_amount) as expense_amount FROM expenses AS exp WHERE exp.expense_date >= '$from' AND exp.expense_date <= '$to' GROUP BY exp.expense_date;");
        foreach($expenses as $exp){
            $sum[] = ['report_date'=>$exp->expense_date, 'sale_total' =>0,'customer_payment' => 0,'purchase_total' => 0, 'supplier_payment' => 0,'expense_total'=>$exp->expense_amount];
        }
        $reports = array() ;
        foreach ($sum as $traffic){
            $key = $traffic['report_date'] ;
            if (isset($reports[$key])){
                if ($reports[$key]['report_date'] == $traffic['report_date']){
                    $reports[$key]['supplier_payment'] += $traffic['supplier_payment'];
                    $reports[$key]['customer_payment'] += $traffic['customer_payment'];
                    $reports[$key]['purchase_total'] += $traffic['purchase_total'];
                    $reports[$key]['expense_total'] += $traffic['expense_total'];
                    $reports[$key]['sale_total'] += $traffic['sale_total'];
                }
            } else {
                $reports[$key] = $traffic ;
            }
        }
        $reports = collect($reports)->sortBy('report_date');
        //return $reports;
        return view('backend.report.daily_report',compact('reports'));
    }
    
}
