000

<?php

namespace App\Exports;

use App\Item;
use DB;
use App\PurchaseRequest;
use App\PurchaseOrder;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;

class DashboardGraphReportExport implements FromCollection,WithHeadings
{
    /**
    * @return \Illuminate\Support\Collection
    */
    protected $excel_name;
    protected $request;

function __construct($excel_name, $request) {
        $this->excel_name = $excel_name;
        $this->request = $request;        
    }

public function collection()
    {
        set_time_limit(300);
         
        $get_session = '';
        $get_week = '';
        $get_month = '';
        $get_quarter = '';

$monthArr = [0=>'', 1=>'January', 2=>'February', 3=>'March', 4=>'April', 5=>'May', 6=>'June', 7=>'July', 8=>'August', 9=>'September', 10=>'October', 11=>'November', 12=>'December'];

if($this->excel_name == 'total_purchase_item_category')
        {            
            if($this->request['item_cat_week']!=null){
                // $get_week = $this->request['item_cat_week'];

$now = new \DateTime();
				$weekStart = $now->modify($this->request['item_cat_week'])->format('Y-m-d');
				$weekEnd = $now->modify('this week +6 days')->format('Y-m-d');

$get_week = $weekStart.' -- '.$weekEnd;
				//dd($weekStart,$weekEnd);
				$fy = $this->request['item_cat_fy'];
				$fy = $fy[2].$fy[3].$fy[7].$fy[8];
				
				$response = \App\PurchaseOrderItem::select('category.id','category.name as category_name', DB::raw('sum(purchase_order_items.qty) as category_count'), DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_amount'), DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate)/100 as tax_amount'))->join('items', 'items.code', 'purchase_order_items.item_code')->join('category', 'category.id', 'items.category')->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->groupBy('items.category')->where('purchase_orders.session',$fy)->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$weekStart,$weekEnd])->orderBy('po_amount', 'desc')->get()->toArray();		
                // dd($response);		
	
			}else if($this->request['item_cat_month']!=null){
                $get_month = $this->request['item_cat_month'];

$month = $this->request['item_cat_month'];
			
				$fy = $this->request['item_cat_fy'];
				$fy = $fy[2].$fy[3].$fy[7].$fy[8];
				
				$response = \App\PurchaseOrderItem::select('category.id','category.name as category_name', DB::raw('sum(purchase_order_items.qty) as category_count'), DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_amount'), DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate)/100 as tax_amount'))->join('items', 'items.code', 'purchase_order_items.item_code')->join('category', 'category.id', 'items.category')->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->groupBy('items.category')->where('purchase_orders.session',$fy)->whereMonth('purchase_orders.po_date','=',$month)->orderBy('po_amount', 'desc')->get()->toArray();			
	
			}else if($this->request['item_cat_quarterly']!=null){

$quarter = $this->request['item_cat_quarterly'];
				$fyear = $this->request['item_cat_fy'];
				if($quarter==1){
					$start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."04"."-"."01";
					$end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."06"."-"."31";
				}else if($quarter==2){
					$start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."07"."-"."01";
					$end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."09"."-"."30";
				}else if($quarter==3){
					$start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."10"."-"."01";
					$end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."12"."-"."31";
				}else if($quarter==4){
					$start_date = $fyear[5].$fyear[6].$fyear[7].$fyear[8]."-"."01"."-"."01";
					$end_date = $fyear[5].$fyear[6].$fyear[7].$fyear[8]."-"."03"."-"."31";
				}
				
                $get_quarter = $start_date.'  '.$end_date;

$fy = $this->request['item_cat_fy'];
				$fy = $fy[2].$fy[3].$fy[7].$fy[8];
				
				$response = \App\PurchaseOrderItem::select('category.id','category.name as category_name',DB::raw('sum(purchase_order_items.qty) as category_count'), DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_amount'), DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate)/100 as tax_amount'))->join('items', 'items.code', 'purchase_order_items.item_code')->join('category', 'category.id', 'items.category')->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->groupBy('items.category')->where('purchase_orders.session',$fy)->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$start_date,$end_date])->orderBy('po_amount', 'desc')->get()->toArray();
	
			}else{
                $get_session = $this->request['item_cat_fy'];

$fy = $this->request['item_cat_fy'];
				$fy = $fy[2].$fy[3].$fy[7].$fy[8];
				                
				$response = \App\PurchaseOrderItem::select('category.id','category.name as category_name',DB::raw('sum(purchase_order_items.qty) as category_count'), DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_amount'), DB::raw('sum((purchase_order_items.qty*purchase_order_items.rate*items.gst)/100) as tax_amount'))->join('items', 'items.code', 'purchase_order_items.item_code')->join('category', 'category.id', 'items.category')->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->groupBy('items.category')->where('purchase_orders.session',$fy)->orderBy('po_amount', 'desc')->get()->toArray();

}                                    
            
            $sum = 0;
            foreach($response as $key=>$value){
                $sum = $sum + $value['po_amount'];
            }

$data=[];
            foreach($response as $key=>$value){

$data[$key]['session']= $this->request['item_cat_fy'];                 
                $data[$key]['week']= $get_week;
                $data[$key]['month']= $monthArr[isset($get_month)?(int)$get_month:0];                
                $data[$key]['quarter']= $get_quarter;

$data[$key]['category_name']=$value['category_name']; 
                $data[$key]['po_amount']=$value['po_amount']; 
                $data[$key]['tax_amount']=$value['tax_amount']; 
                $data[$key]['total_amount']=$value['po_amount']+$value['tax_amount']; 
                $data[$key]['category_count']=$value['category_count'];  
                if($sum > 0) {
                    $data[$key]['category_percent']= round(((int)$value['po_amount']*100)/$sum, 3);
                } else {
                    $data[$key]['category_percent']= '0%';
                }          
            }

return collect($data);
        }

if($this->excel_name == 'total_purchase_department')
        {                       
            if($this->request['item_cat_week'] !=null){
                
                $now = new DateTime();
                $weekStart = $now->modify($this->request['item_cat_week'])->format('Y-m-d');
                $weekEnd = $now->modify('this week +6 days')->format('Y-m-d');
                
                $get_week = $weekStart.' -- '.$weekEnd;

$fy = $this->request['item_cat_fy'];
                $fy = $fy[2].$fy[3].$fy[7].$fy[8];
                
                $response = \App\PurchaseOrderItem::select('department.id as department_id','department.name as department_name', DB::raw('sum(purchase_order_items.qty) as department_count'), \DB::raw('sum(purchase_order_items.rate*purchase_order_items.qty) as po_amount'), DB::raw('sum((purchase_order_items.qty*purchase_order_items.rate*items.gst)/100) as tax_amount'))->join('purchase_orders', 'purchase_orders.id', 'purchase_order_items.po_id')->join('purchase_requests','purchase_requests.id','purchase_order_items.pr_id')->join('items', 'items.code', 'purchase_order_items.item_code')->join('department', 'department.id', 'purchase_requests.department')->groupBy('purchase_requests.department')->where('purchase_requests.session',$fy)->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$weekStart,$weekEnd])->orderBy('po_amount', 'DESC')->get()->toArray();
            
            }else if($this->request['item_cat_month'] !=null){
                $get_month = $this->request['item_cat_month'];

$month = $this->request['item_cat_month'];
            
                $fy = $this->request['item_cat_fy'];
                $fy = $fy[2].$fy[3].$fy[7].$fy[8];

$response = \App\PurchaseOrderItem::select('department.id as department_id','department.name as department_name', DB::raw('sum(purchase_order_items.qty) as department_count'), \DB::raw('sum(purchase_order_items.rate*purchase_order_items.qty) as po_amount'), DB::raw('sum((purchase_order_items.qty*purchase_order_items.rate*items.gst)/100) as tax_amount'))->join('purchase_orders', 'purchase_orders.id', 'purchase_order_items.po_id')->join('purchase_requests','purchase_requests.id','purchase_order_items.pr_id')->join('items', 'items.code', 'purchase_order_items.item_code')->join('department', 'department.id', 'purchase_requests.department')->groupBy('purchase_requests.department')->where('purchase_requests.session',$fy)->whereMonth('purchase_orders.po_date','=',$month)->orderBy('po_amount', 'DESC')->get()->toArray();               
            
            }else if($this->request['item_cat_quarterly'] !=null){
                
                $quarter = $this->request['item_cat_quarterly'];
                $fyear = $this->request['item_cat_fy'];
                if($quarter==1){
                    $start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."04"."-"."01";
                    $end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."06"."-"."31";
                }else if($quarter==2){
                    $start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."07"."-"."01";
                    $end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."09"."-"."30";
                }else if($quarter==3){
                    $start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."10"."-"."01";
                    $end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."12"."-"."31";
                }else if($quarter==4){
                    $start_date = $fyear[5].$fyear[6].$fyear[7].$fyear[8]."-"."01"."-"."01";
                    $end_date = $fyear[5].$fyear[6].$fyear[7].$fyear[8]."-"."03"."-"."31";
                }

$get_quarter = $start_date.' -- '.$end_date;
                
                $fy = $this->request['item_cat_fy'];
                $fy = $fy[2].$fy[3].$fy[7].$fy[8];
                
                $response = \App\PurchaseOrderItem::select('department.id as department_id','department.name as department_name', DB::raw('sum(purchase_order_items.qty) as department_count'), \DB::raw('sum(purchase_order_items.rate*purchase_order_items.qty) as po_amount'), DB::raw('sum((purchase_order_items.qty*purchase_order_items.rate*items.gst)/100) as tax_amount'))->join('purchase_orders', 'purchase_orders.id', 'purchase_order_items.po_id')->join('purchase_requests','purchase_requests.id','purchase_order_items.pr_id')->join('items', 'items.code', 'purchase_order_items.item_code')->join('department', 'department.id', 'purchase_requests.department')->groupBy('purchase_requests.department')->where('purchase_requests.session',$fy)->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$start_date,$end_date])->orderBy('po_amount', 'DESC')->get()->toArray();
            
            }else{
            
                $fy = $this->request['item_cat_fy'];
                $fy = $fy[2].$fy[3].$fy[7].$fy[8];
                
                $response = \App\PurchaseOrderItem::select('department.id as department_id','department.name as department_name', DB::raw('sum(purchase_order_items.qty) as department_count'), \DB::raw('sum(purchase_order_items.rate*purchase_order_items.qty) as po_amount'), DB::raw('sum((purchase_order_items.qty*purchase_order_items.rate*items.gst)/100) as tax_amount'))->join('purchase_orders', 'purchase_orders.id', 'purchase_order_items.po_id')->join('purchase_requests','purchase_requests.id','purchase_order_items.pr_id')->join('items', 'items.code', 'purchase_order_items.item_code')->join('department', 'department.id', 'purchase_requests.department')->groupBy('purchase_requests.department')->where('purchase_requests.session',$fy)->orderBy('po_amount', 'DESC')->get()->toArray();
               
            }

$sum = 0;
            foreach($response as $key=>$value){ 
                $sum += $value['po_amount'];
            }
            
            $data=[];
            foreach($response as $key=>$value){ 
                $data[$key]['session']= $this->request['item_cat_fy'];                 
                $data[$key]['week']= $get_week;
                $data[$key]['month']= $monthArr[isset($get_month)?(int)$get_month:0];                
                $data[$key]['quarter']= $get_quarter;

$data[$key]['department_name']=$value['department_name']; 
                $data[$key]['po_amount']=$value['po_amount']; 
                $data[$key]['tax_amount']=$value['tax_amount']; 
                $data[$key]['total_amount']=$value['po_amount']+$value['tax_amount']; 
                $data[$key]['department_count']=$value['department_count'];

if($sum > 0) {
                    $data[$key]['department_percent']= round(((int)$value['po_amount']*100)/$sum, 3);
                } else {
                    $data[$key]['department_percent']= '0%';
                }  
            }

return collect($data);       
        } 
        if($this->excel_name == 'total_purchase_circle') {

if($this->request['item_cat_week'] !=null){
                      
                  $now = new DateTime();
                  $weekStart = $now->modify($this->request['item_cat_week'])->format('Y-m-d');
                  $weekEnd = $now->modify('this week +6 days')->format('Y-m-d');
                
                  $get_week = $weekStart.' '.$weekEnd;

$month = $this->request['item_cat_month'];
              
                  $fy = $this->request['item_cat_fy'];
                  $fy = $fy[2].$fy[3].$fy[7].$fy[8];
  
                  $response = \App\PurchaseOrderItem::select('circles.id as circle_id','circles.name as circle_name', DB::raw('sum(purchase_order_items.qty) as circle_count'), DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_amount'), DB::raw('sum((purchase_order_items.qty*purchase_order_items.rate*items.gst)/100) as tax_amount'))->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->join('items', 'items.code', 'purchase_order_items.item_code')->join('circles', 'circles.id', 'purchase_orders.circle')->groupBy('purchase_orders.circle')->where('purchase_orders.session',$fy)->whereMonth('purchase_orders.po_date','=',$month)->get()->toArray();
  
                  usort($response, function($a, $b) {
                      return $b['po_amount'] - $a['po_amount'];
                  });
              
            }else if($this->request['item_cat_quarterly'] !=null){
                  
                  $quarter = $this->request['item_cat_quarterly'];
                  $fyear = $this->request['item_cat_fy'];
                  if($quarter==1){
                      $start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."04"."-"."01";
                      $end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."06"."-"."31";
                  }else if($quarter==2){
                      $start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."07"."-"."01";
                      $end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."09"."-"."30";
                  }else if($quarter==3){
                      $start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."10"."-"."01";
                      $end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."12"."-"."31";
                  }else if($quarter==4){
                      $start_date = $fyear[5].$fyear[6].$fyear[7].$fyear[8]."-"."01"."-"."01";
                      $end_date = $fyear[5].$fyear[6].$fyear[7].$fyear[8]."-"."03"."-"."31";
                  }
                  
                  $get_quarter = $start_date.' -- '.$end_date;

$fy = $this->request['item_cat_fy'];
                  $fy = $fy[2].$fy[3].$fy[7].$fy[8];
                  
                  $response = \App\PurchaseOrderItem::select('circles.id as circle_id','circles.name as circle_name', DB::raw('sum(purchase_order_items.qty) as circle_count'), DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_amount'), DB::raw('sum((purchase_order_items.qty*purchase_order_items.rate*items.gst)/100) as tax_amount'))->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->join('items', 'items.code', 'purchase_order_items.item_code')->join('circles', 'circles.id', 'purchase_orders.circle')->groupBy('purchase_orders.circle')->where('purchase_orders.session',$fy)->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$start_date,$end_date])->get()->toArray();
  
                  usort($response, function($a, $b) {
                      return $b['po_amount'] - $a['po_amount'];
                  });
              
            }else{                
                $fy = $this->request['item_cat_fy'];
                $fy = $fy[2].$fy[3].$fy[7].$fy[8];
                  
                $response = \App\PurchaseOrderItem::select('circles.id as circle_id','circles.name as circle_name', DB::raw('sum(purchase_order_items.qty) as circle_count'), DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_amount'), DB::raw('sum((purchase_order_items.qty*purchase_order_items.rate*items.gst)/100) as tax_amount'))->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->join('items', 'items.code', 'purchase_order_items.item_code')->join('circles', 'circles.id', 'purchase_orders.circle')->groupBy('purchase_orders.circle')->where('purchase_orders.session',$fy)->get()->toArray();
                
                usort($response, function($a, $b) {
                    return $b['po_amount'] - $a['po_amount'];
                });                  
            }

$sum =0;
            foreach ($response as $key=>$value){ 
                $sum += $value['po_amount'];
            }

$data=[];
            foreach($response as $key=>$value){ 
                $data[$key]['session']= $this->request['item_cat_fy'];                 
                $data[$key]['week']= $get_week;
                $data[$key]['month']= $monthArr[isset($get_month)?(int)$get_month:0];                
                $data[$key]['quarter']= $get_quarter;

$data[$key]['circle_name']=$value['circle_name']; 
                $data[$key]['po_amount']=$value['po_amount']; 
                $data[$key]['tax_amount']=$value['tax_amount']; 
                $data[$key]['total_amount']=$value['po_amount']+$value['tax_amount']; 
                $data[$key]['Quantity']=$value['circle_count'];

if($sum > 0) {
                    $data[$key]['amount_percent']= round(((int)$value['po_amount']*100)/$sum, 3);
                } else {
                    $data[$key]['amount_percent']= '0%';
                }
            }            
          
            return collect($data);   
          
        }
        if($this->excel_name == 'top_five_supplier') {
            if($this->request['item_cat_week'] !=null){
				
				$now = new DateTime();
				$weekStart = $now->modify($this->request['item_cat_week'])->format('Y-m-d');
				$weekEnd = $now->modify('this week +6 days')->format('Y-m-d');
                
                $get_week = $weekStart.' -- '.$weekEnd;

}else if($this->request['item_cat_month'] !=null){
				$get_month = $this->request['item_cat_month'];

$month = $this->request['item_cat_month'];
			
				$fy = $this->request['item_cat_fy'];
				$fy = $fy[2].$fy[3].$fy[7].$fy[8];
			
				$vendors = \App\PurchaseOrderItem::select('vendors.vendor_code','vendors.vendor_name as vendor_name', \DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_amount'),\DB::raw('sum((purchase_order_items.qty*purchase_order_items.rate*items.gst)/100) as po_tax_amount'))->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->join('vendors','vendors.id','purchase_orders.vendor_id')->join('items', 'items.code', 'purchase_order_items.item_code')->groupBy('purchase_orders.vendor_id')->where('purchase_orders.session',$fy)->whereMonth('purchase_orders.po_date','=',$month)->orderBy('po_amount', 'desc')->get()->toArray();
									
			}else if($this->request['item_cat_quarterly']!=null){
			
				$quarter = $this->request['item_cat_quarterly'];
				$fyear = $this->request['item_cat_fy'];
				if($quarter==1){
					$start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."04"."-"."01";
					$end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."06"."-"."31";
				}else if($quarter==2){					
					$start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."07"."-"."01";					
					$end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."09"."-"."30";					
				}else if($quarter==3){
					$start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."10"."-"."01";
					$end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."12"."-"."31";
				}else if($quarter==4){
					$start_date = $fyear[5].$fyear[6].$fyear[7].$fyear[8]."-"."01"."-"."01";
					$end_date = $fyear[5].$fyear[6].$fyear[7].$fyear[8]."-"."03"."-"."31";
				}
				
                $get_quarter = $start_date.' -- '.$end_date;

$fy = $this->request['item_cat_fy'];
				$fy = $fy[2].$fy[3].$fy[7].$fy[8];
			
				$vendors = \App\PurchaseOrderItem::select('vendors.vendor_code','vendors.vendor_name as vendor_name', \DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_amount'), \DB::raw('sum((purchase_order_items.qty*purchase_order_items.rate*items.gst)/100) as po_tax_amount'))->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->join('vendors','vendors.id','purchase_orders.vendor_id')->join('items', 'items.code', 'purchase_order_items.item_code')->groupBy('purchase_orders.vendor_id')->where('purchase_orders.session',$fy)->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$start_date,$end_date])->orderBy('po_amount', 'desc')->get()->toArray();
															
			}else{
			
				$fy = $this->request['item_cat_fy'];
				$fy = $fy[2].$fy[3].$fy[7].$fy[8];
				
                $vendors = \App\PurchaseOrderItem::select('vendors.vendor_code','vendors.vendor_name as vendor_name', \DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_amount'), \DB::raw('sum((purchase_order_items.qty*purchase_order_items.rate*items.gst)/100) as po_tax_amount'))->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->join('vendors','vendors.id','purchase_orders.vendor_id')->join('items', 'items.code', 'purchase_order_items.item_code')->groupBy('purchase_orders.vendor_id')->where('purchase_orders.session',$fy)->orderBy('po_amount', 'desc')->get()->toArray();
								
			}

$sum = 0;
            foreach($vendors as $key=>$value){  
                $sum += $value['po_amount'];
            }

$data=[];
            foreach($vendors as $key=>$value){ 
                $data[$key]['session']= $this->request['item_cat_fy'];                 
                $data[$key]['week']= $get_week;
                $data[$key]['month']= $monthArr[isset($get_month)?(int)$get_month:0];                
                $data[$key]['quarter']= $get_quarter;

$data[$key]['vendor_code']=$value['vendor_code'];                 
                $data[$key]['vendor_name']=$value['vendor_name'];                 
                $data[$key]['po_amount']=$value['po_amount']; 
                $data[$key]['tax_amount']=$value['po_tax_amount'];
                $data[$key]['total_amount']=$value['po_amount']+$value['po_tax_amount'];

if($sum > 0) {
                    $data[$key]['amount_percent']= round(((int)$value['po_amount']*100)/$sum, 3);
                } else {
                    $data[$key]['amount_percent']= '0%';
                }                    
            }

return collect($data);   
        }
        if($this->excel_name == 'top_five_items') {

if($this->request['item_cat_week'] !=null){
		
                $now = new DateTime();
                $weekStart = $now->modify($this->request['item_cat_week'])->format('Y-m-d');
                $weekEnd = $now->modify('this week +6 days')->format('Y-m-d');
            
                $get_week = $weekStart.' -- '.$weekEnd;

$fy = $this->request['item_cat_fy'];
                $fy = $fy[2].$fy[3].$fy[7].$fy[8];
                
                $data=[];
                $items_data = \App\PurchaseOrderItem::select('purchase_order_items.item_code as item_code', \DB::raw('sum(purchase_order_items.qty) as total_item_qty'), 'items.description', \DB::raw('sum(purchase_order_items.rate*purchase_order_items.qty) as po_amount'), \DB::raw('sum((purchase_order_items.rate*purchase_order_items.qty*items.gst)/100) as po_tax_amount'))->join('purchase_orders','purchase_orders.id', 'purchase_order_items.po_id')->join('items', 'items.code', 'purchase_order_items.item_code')->where('purchase_orders.session',$fy)->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$weekStart,$weekEnd])->groupBy('purchase_order_items.item_code')->orderBy('po_amount', 'DESC')->get()->toArray();
                                
    
            }else if($this->request['item_cat_month'] !=null){
                $get_month = $this->request['item_cat_month'];
                $month = $this->request['item_cat_month'];
            
                $fy = $this->request['item_cat_fy'];
                $fy = $fy[2].$fy[3].$fy[7].$fy[8];
                  
                $data=[];
                
                $items_data = \App\PurchaseOrderItem::select('purchase_order_items.item_code as item_code', \DB::raw('sum(purchase_order_items.qty) as total_item_qty'), 'items.description', \DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_amount'), \DB::raw('sum((purchase_order_items.qty*purchase_order_items.rate*items.gst)/100) as po_tax_amount'))->join('purchase_orders','purchase_orders.id', 'purchase_order_items.po_id')->join('items', 'items.code', 'purchase_order_items.item_code')->where('purchase_orders.session',$fy)->whereMonth('purchase_orders.po_date','=',$month)->groupBy('purchase_order_items.item_code')->orderBy('po_amount', 'DESC')->get()->toArray();
                            
            }else if($this->request['item_cat_quarterly'] !=null){
                
                $quarter = $this->request['item_cat_quarterly'];
                $fyear = $this->request['item_cat_fy'];
                if($quarter==1){
                    $start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."04"."-"."01";
                    $end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."06"."-"."31";
                }else if($quarter==2){
                    $start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."07"."-"."01";
                    $end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."09"."-"."30";
                }else if($quarter==3){
                    $start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."10"."-"."01";
                    $end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."12"."-"."31";
                }else if($quarter==4){
                    $start_date = $fyear[5].$fyear[6].$fyear[7].$fyear[8]."-"."01"."-"."01";
                    $end_date = $fyear[5].$fyear[6].$fyear[7].$fyear[8]."-"."03"."-"."31";
                }
                
                $get_quarter = $start_date.' -- '.$end_date;

$sum = 0;
            foreach($vendors as $key=>$value){  
                $sum += $value['po_amount'];
            }

$data=[];
            foreach($items_data as $key=>$value){ 
                $data[$key]['session']= $this->request['item_cat_fy'];                 
                $data[$key]['week']= $get_week;
                $data[$key]['month']= $monthArr[isset($get_month)?(int)$get_month:0];                
                $data[$key]['quarter']= $get_quarter;

$data[$key]['item_code']=$value['item_code'];                                 
                $data[$key]['description']=$value['description']; 
                $data[$key]['po_amount']=$value['po_amount']; 
                $data[$key]['tax_amount']=$value['po_tax_amount']; 
                $data[$key]['total_amount']=$value['po_amount']+$value['po_tax_amount']; 
                $data[$key]['Item Quantity']=$value['total_item_qty'];

return collect($data);

}	
    }
    
    public function headings(): array
    { 
        if($this->excel_name == 'total_purchase_item_category'){
            return [ 
                'Financial Year',
                'Week',                
                'Month', 
                'Quarter Period',
                'Item Category',   
                'Total Unit Price against POs', 
                'Total GST Amount against POs',                
                'Total Amount against POs',
                'Item Quantity',
                'Item Amount %',
            ];
        }
        
        if($this->excel_name == 'total_purchase_department'){
            return [                 
                'Financial Year',
                'Week',                
                'Month', 
                'Quarter Period',
                'Department',   
                'Total Unit Price against POs', 
                'Total GST Amount against POs',                
                'Total Amount against POs',
                'Item Quantity',
                'Item Amount %',               
            ];
        }

if($this->excel_name == 'total_purchase_circle'){
            return [                 
                'Financial Year',
                'Week',                
                'Month', 
                'Quarter Period',
                'Circle',   
                'Total Unit Price against POs', 
                'Total GST Amount against POs',                
                'Total Amount against POs',
                'Item Quantity',
                'Item Amount %',                 
            ];
        }

if($this->excel_name == 'top_five_supplier'){
            return [    
                'Financial Year',
                'Week',
                'Month', 
                'Quarter Period',
                'Vendor Code',
                'Vendor Name',   
                'Total Unit Price against POs', 
                'Total GST Amount against POs',                
                'Total Amount against POs', 
                'Item Amount %',             
            ];
        }

if($this->excel_name == 'top_five_items'){
            return [ 
                'Financial Year',
                'Week',
                'Month', 
                'Quarter Period',                
                'Item Code',                                                 
                'Description',
                'Total Unit Price against POs', 
                'Total GST Amount against POs',                
                'Total Amount against POs',   
                'Item Quantity',  
                'Item Amount %',         
            ];
        }
    }
    
    
}