<?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\CustomerRecord;
use App\Models\Productcategory;
use App\Models\Production;
use App\Models\ProductionDetail;
use App\Models\Wastage;
use App\Models\Worker;
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'));
        }
    }
    /**
     * sale Report section
     */
    /*-
    * 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");
      $sales = '';
      return view('backend.report.sale.customer_invoice_report_index',compact('customers','sales'));
    }
    public function csrs(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);

        $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::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;
                $sales = 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.sale.customer_invoice_report_index',compact('customers','sales','ty','from','to'));
        }
        if($request->report_type == 'Sales_Due'){ 
          if($request->customer_id == "All"){
              $ty = $request->customer_id ;
              $sales = Sale::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;
                $sales = Sale::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.sale.customer_invoice_report_index',compact('customers','sales','ty','from','to'));
        }
        if($request->report_type == 'All_Due'){ 
            if($request->customer_id == "All"){
              $ty = $request->customer_id ;
              $sales = 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.customer_id = c.id and s.due_amount > 0 GROUP by s.customer_id ");
            }else{
                $ty = Customer::find($request->customer_id);
                $sales = 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.customer_id = c.id and s.due_amount > 0 AND c.id = $request->customer_id GROUP by s.customer_id ");
            }
            return view('backend.report.sale.customer_all_due_report_index',compact('customers','sales','ty','from','to'));
        }
        if($request->report_type == 'Product'){ 
            if($request->customer_id === "All"){
                $ty = $request->customer_id ;
                 $sales = SaleDetail::whereDate('sale_date', '>=', $from_date)->whereDate('sale_date', '<=', $to_date)->orderBy('sale_date','ASC')->get();
              }else{
                  $ty = Customer::find($request->customer_id);
                  $sales = SaleDetail::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.sale.customer_product_report_index',compact('customers','sales','ty','from','to'));
        }
        if($request->report_type == 'Return'){ 
          if($request->customer_id == "All"){
              $ty = $request->customer_id ;
              $sales = 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;
                $sales = 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.sale.customer_return_report_index',compact('customers','sales','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 ");
            $sales = collect($sales);
            }else{
                  $ty = Customer::find($request->customer_id);
                  //$ty = $types->name;
                  $sales = CustomerAccount::where('customer_id',$request->customer_id)->whereDate('payment_date', '>=', $from_date)->whereDate('payment_date', '<=', $to_date)->orderBy('payment_date','ASC')->get();
                }
          return view('backend.report.sale.customer_payment_report_index',compact('customers','sales','ty','from','to'));
        }
        if($request->report_type == 'Statement'){
          if($request->customer_id == "All"){
              return back()->with('warning','Woops please select customer');
          }else{
            $ty = Customer::find($request->customer_id);
            //$ty = $type->name;

            $sum = array();
            $sales = DB::select("SELECT  SUM(s.grand_total_price) AS price,s.sale_date FROM sales AS s WHERE  s.customer_id = $request->customer_id AND s.sale_date >= '$from' AND s.sale_date <= '$to' GROUP BY s.sale_date");
            foreach($sales as $sale){
                $sum[] = ['description' => 'Invoice','transaction_date' => $sale->sale_date, 'sale' => $sale->price, 'payment' => 0 ];
            }
            $payments = DB::select("SELECT  SUM(c.amount) AS payment,c.payment_date FROM customer_accounts AS c WHERE  c.customer_id = $request->customer_id AND c.payment_date >= '$from' AND c.payment_date <= '$to' GROUP BY c.payment_date");
            foreach($payments as $ca){
                $sum[] = ['description' => 'Payment','transaction_date' => $ca->payment_date, 'sale' => 0, 'payment' => $ca->payment ];
            }
            $new = array();
            foreach ($sum as $traffic){
                $key = $traffic['transaction_date'] ;
                if (isset($new[$key])){
                    if($new[$key]['transaction_date'] === $traffic['transaction_date']){
                      $new[$key]['sale'] += $traffic['sale'];
                      $new[$key]['payment'] += $traffic['payment'];
                    }
                } else {
                    $new[$key] = $traffic ;
                }
            }
            //return $new;
            return view('backend.report.sale.customer_statement',compact('customers','new','ty','from','to'));
          }
        }
        if($request->report_type == 'Record'){ 
          if($request->customer_id == "All"){
              $ty = $request->customer_id ;
              $sales = CustomerRecord::whereDate('transaction_date', '>=', $from_date)->whereDate('transaction_date', '<=', $to_date)->orderBy('transaction_date','ASC')->get();
             }
             else{
                  $ty = Customer::find($request->customer_id);
                  $sales = CustomerRecord::where('customer_id',$request->customer_id)->whereDate('transaction_date', '>=', $from_date)->whereDate('transaction_date', '<=', $to_date)->get();
                }
          return view('backend.report.sale.customer_record',compact('customers','sales','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_purchase_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_purchase_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_sale_report',compact('categories','reports','ty','report_type','from','to'));
      }
      if($request->report_type == 'Production&Sale'){
        $sum = array();
        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;
          $production = DB::select("SELECT p.product_name,p.code,pd.production_date,SUM(pd.rate) AS pdcost,SUM(pd.quantity) AS quantity,SUM(pd.wage) AS wage FROM `production_details` AS pd, products AS p WHERE p.id = pd.product_id AND pd.productcategory_id = $request->category_id AND pd.production_date >= '$from' AND pd.production_date <= '$to' GROUP BY pd.production_date;");
          foreach($production as $pd){
            $sum[] = ['product' => $pd->product_name,'code'=>$pd->code,'report_date'=>$pd->production_date, 'pdcost' => $pd->pdcost, 'pd_quantity' => $pd->quantity,'wage'=>$pd->wage,'now_stock'=>0,'sale_quantity' =>0,'sale_price' =>0 ];
          }
          $stocks = DB::select("SELECT p.product_name,p.code,SUM(pd.rate) AS pdcost,SUM(pd.now_stock) AS now_stock FROM `production_stocks` AS pd, products AS p WHERE p.id = pd.product_id AND pd.productcategory_id = $request->category_id GROUP BY pd.product_id;");
          foreach($stocks as $stock){
            $sum[] = ['product' => $stock->product_name,'code'=>$stock->code,'report_date'=>0, 'pdcost' => 0, 'pd_quantity' => 0,'wage'=>0,'now_stock'=>$stock->now_stock,'sale_quantity' =>0,'sale_price' =>0 ];
          }
          $sales = DB::select("SELECT p.product_name,p.code,sl.sale_date,SUM(sl.unit_price) AS sale_price,SUM(sl.quantity) AS quantity FROM `sale_details` AS sl, products AS p WHERE p.id = sl.product_id AND sl.productcategory_id = $request->category_id AND sl.sale_date >= '$from' AND sl.sale_date <= '$to' GROUP BY sl.sale_date;");
          foreach($sales as $sl){
            $sum[] = ['product' => $sl->product_name,'code'=>$sl->code,'report_date'=>$sl->sale_date, 'sale_price' => $sl->quantity * $sl->sale_price,'pdcost' => 0,'pd_quantity' => 0, 'sale_quantity' => $sl->quantity,'wage'=>0,'now_stock'=>0 ];
          }
        }
        $reports = array() ;
        foreach ($sum as $traffic){
            $key = $traffic['report_date'] ;
            if (isset($reports[$key])){
                if ($reports[$key]['report_date'] == $traffic['report_date']){
                    $reports[$key]['pdcost'] += $traffic['pdcost'];
                    $reports[$key]['pd_quantity'] += $traffic['pd_quantity'];
                    $reports[$key]['wage'] += $traffic['wage'];
                    $reports[$key]['sale_quantity'] += $traffic['sale_quantity'];
                    $reports[$key]['sale_price'] += $traffic['sale_price'];
                }
            } else {
                $reports[$key] = $traffic ;
            }
        }
        $reports = collect($reports)->sortBy('report_date');
        //return $reports;
        return view('backend.report.category.production_sale_report',compact('categories','reports','ty','report_type','from','to'));
      }
    }
    public function worker_report(){
      $workers = Worker::where('status',1)->get();
      $reports = '';
      return view('backend.report.worker.wage_report',compact('workers','reports'));
    }
    public function worker_reports(Request $request){
        $from = date('Y-m-d',strtotime($request->from_date));
        $to = date('Y-m-d',strtotime($request->to_date));
        $workers = Worker::where('status',1)->get();
        if($request->report_type == 'Product'){
            if($request->worker_id == "All"){
            $ty = $request->worker_id;
            $reports = ProductionDetail::whereDate('production_date', '>=', $from)->whereDate('production_date', '<=', $to)->orderBy('production_date','ASC')->get();
          }else{
            $types = Worker::find($request->worker_id);
            $ty = $types->name;
            $reports = ProductionDetail::where('worker_id',$request->worker_id)->whereDate('production_date', '>=', $from)->whereDate('production_date', '<=', $to)->orderBy('production_date','ASC')->get();
          }
        }
        if($request->report_type == 'Wage'){
            if($request->worker_id == "All"){
            $ty = $request->worker_id;
            $reports = DB::select("SELECT w.name,p.payment_date,SUM(p.amount) AS amount FROM `worker_accounts` AS p,workers AS w WHERE p.worker_id = w.id AND p.payment_date >= '$from' AND p.payment_date <= '$to' GROUP BY p.payment_date;");
          }else{
            $types = Worker::find($request->worker_id);
            $ty = $types->name;
            $reports = DB::select("SELECT payment_date,SUM(amount) AS amount FROM `worker_accounts` WHERE worker_id = $request->worker_id AND payment_date >= '$from' AND  payment_date <= '$to' GROUP BY payment_date;");
          }
        }
      return view('backend.report.worker.wage_report',compact('workers','reports','ty','from','to'));
    }
}
