000

<?php

namespace App\Exports;

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

class DashboardReportExport 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);
                
        $session_year = substr($this->request['session'], 2, 2);
        
        $year = '20'.$session_year;		
        $session = substr($this->request['session'], 2,2).substr($this->request['session'], 7,2);
        $quarter = $this->request['quarter'];
        
        if($quarter == 1) {
            $quarter_start = $year.'-04-01';
            $quarter_end = $year.'-06-30';
        } elseif($quarter == 2) {
            $quarter_start = $year.'-07-01';
            $quarter_end = $year.'-09-30';
        } elseif($quarter == 3) {
            $quarter_start = $year.'-10-01';
            $quarter_end = $year.'-12-31';
        } else {
            $quarter_start = ($year+1).'-01-01';
            $quarter_end = ($year+1).'-03-31';
        }

if($this->excel_name == 'open_pr_count')
        {            
          
          $query = \App\PurchaseRequest::query();
          $query->join('purchase_request_items as pri', 'pri.pr_id', 'purchase_requests.id');
          if(isset($quarter) && $quarter != '') {
            $query->whereBetween(DB::raw('date(purchase_requests.pr_date)'),[$quarter_start,$quarter_end]);
          }
          $open_pr = $query->select('purchase_requests.id')->where('session', $session)->where('purchase_requests.status', 'LIKE', 'Approved')->groupBy('pri.pr_id')->havingRaw('sum(rqty) > 0')->get()->toArray();
          
          $prArr = array();
          foreach ($open_pr as $key => $pr) {
            array_push($prArr, $pr['id']);
          }
        
            $query = PurchaseRequest::query();
            $query->join('users','users.id','purchase_requests.prepared_by')
            // ->join('purchase_request_items','purchase_request_items.pr_id','purchase_requests.id')
            ->join('verticals','verticals.id','purchase_requests.department')
            ->join('business_units','business_units.id','purchase_requests.business_unit')
            ->join('customers','customers.id','purchase_requests.customer')
            ->join('circles','circles.id','purchase_requests.circle');            
            $query->where('purchase_requests.status', 'LIKE', 'Approved')->whereIn('purchase_requests.id', $prArr);

$response = $query->select('users.name','purchase_requests.pr_number','purchase_requests.created_at','purchase_requests.description as pr_desc','purchase_requests.amendment_number','purchase_requests.amendment_number',\DB::raw('DATE_FORMAT(purchase_requests.updated_at,"%d %b %Y") as amendment_date'),'purchase_requests.status','users.name as re_by','purchase_requests.created_at as re_date','users.name as Approved_by','purchase_requests.created_at as app_date','verticals.name as dep_name','business_units.name as bus_unit','customers.name as cust_name','purchase_requests.site','circles.name as cir_name','purchase_requests.delivery_location','purchase_requests.id as pr_id')->get(); 
            
            $data=[];
            foreach($response as $key=>$value){                
                $data[$key]['PR Number']=$value['pr_number']; 
                $data[$key]['PR Date']=$value['created_at']; 
                $data[$key]['Preparer Name']=$value['name']; 
                $data[$key]['PR Amendment Number']=$value['amendment_number']; 
                $data[$key]['PR Amendment Date']=($value['amendment_number'] > 0)?$value['amendment_date']:""; 
                $data[$key]['PR Status']=$value['status']; 
                $data[$key]['PR Current Status']=getPRCurrentStatus2($value['pr_id']);
                $data[$key]['Recommended By']=$value['re_by']; 
                $data[$key]['Recommendation Date']=$value['re_date'];
                $data[$key]['Approved_by']=getModelById('User',PrStatusCreaterName($value['pr_id'],'Approved')->user_id)->name;
                $data[$key]['Approval Date']=PrStatusCreaterName($value['pr_id'],'Approved')->created_at;
                $data[$key]['Department']=$value['dep_name']; 
                $data[$key]['Business Unit']=$value['bus_unit']; 
                $data[$key]['Customer']=$value['cust_name'];
                $data[$key]['Site']=$value['site']; 
                $data[$key]['Circle']=$value['cir_name']; 
                $data[$key]['Delivery Location']=$value['delivery_location'];          
            }

return collect($data);
        }
        
        if($this->excel_name == 'total_pr_count')
        {
            $query = PurchaseRequest::query();
            $query->join('users','users.id','purchase_requests.prepared_by')
            // ->join('purchase_request_items','purchase_request_items.pr_id','purchase_requests.id')
            ->join('department','department.id','purchase_requests.department')
            ->join('business_units','business_units.id','purchase_requests.business_unit')
            ->join('customers','customers.id','purchase_requests.customer')
            ->join('circles','circles.id','purchase_requests.circle');
            // ->join('items','items.code','purchase_request_items.item_code')
            // ->join('category','category.id','items.category')
            // ->join('units','units.id','items.uom');
            
            if(isset($session)) {
                $query->where('purchase_requests.session', $session);
            }

if(isset($quarter)) {
                $query->whereBetween(\DB::raw('date(purchase_requests.pr_date)'), [$quarter_start, $quarter_end]);
            }
            
            $response = $query->select('users.name','purchase_requests.pr_number','purchase_requests.created_at','purchase_requests.description as pr_desc','purchase_requests.amendment_number','purchase_requests.amendment_number',\DB::raw('DATE_FORMAT(purchase_requests.updated_at,"%d %b %Y") as amendment_date'),'purchase_requests.status','users.name as re_by','purchase_requests.created_at as re_date','users.name as Approved_by','purchase_requests.created_at as app_date','department.name as dep_name','business_units.name as bus_unit','customers.name as cust_name','purchase_requests.site','circles.name as cir_name','purchase_requests.delivery_location','purchase_requests.id as pr_id')->where('purchase_requests.status', 'LIKE', 'Approved')->get();
            
            $data=[];
            foreach($response as $key=>$value){                
                $data[$key]['PR Number']=$value['pr_number']; 
                $data[$key]['PR Date']=$value['created_at']; 
                $data[$key]['Preparer Name']=$value['name']; 
                $data[$key]['PR Amendment Number']=$value['amendment_number']; 
                $data[$key]['PR Amendment Date']=($value['amendment_number'] > 0)?$value['amendment_date']:""; 
                $data[$key]['PR Status']=$value['status'];    
                $data[$key]['PR Current Status']=getPRCurrentStatus2($value['pr_id']);             
                $data[$key]['Recommended By']=$value['re_by']; 
                $data[$key]['Recommendation Date']=$value['re_date'];
                $data[$key]['Approved_by']=getModelById('User',PrStatusCreaterName($value['pr_id'],'Approved')->user_id)->name;
                $data[$key]['Approval Date']=PrStatusCreaterName($value['pr_id'],'Approved')->created_at;
                $data[$key]['Department']=$value['dep_name']; 
                $data[$key]['Business Unit']=$value['bus_unit']; 
                $data[$key]['Customer']=$value['cust_name'];
                $data[$key]['Site']=$value['site']; 
                $data[$key]['Circle']=$value['cir_name']; 
                $data[$key]['Delivery Location']=$value['delivery_location'];                  
            }

return collect($data);
        }
        
        
        if($this->excel_name == 'total_un_app_pr_count')
        {
            $query = PurchaseRequest::query();
            $query->join('users','users.id','purchase_requests.prepared_by')
            // ->join('purchase_request_items','purchase_request_items.pr_id','purchase_requests.id')
            ->leftjoin('department','department.id','purchase_requests.department')
            ->leftjoin('business_units','business_units.id','purchase_requests.business_unit')
            ->leftjoin('customers','customers.id','purchase_requests.customer')
            ->leftjoin('circles','circles.id','purchase_requests.circle');

if(isset($session)) {
                $query->where('purchase_requests.session', $session);
            }

if(isset($quarter)) {
                $query->whereBetween(\DB::raw('date(purchase_requests.pr_date)'), [$quarter_start, $quarter_end]);
            }

$response = $query->select('users.name','purchase_requests.pr_number','purchase_requests.created_at','purchase_requests.description as pr_desc','purchase_requests.amendment_number','purchase_requests.amendment_number',\DB::raw('DATE_FORMAT(purchase_requests.updated_at,"%d %b %Y") as amendment_date'),'purchase_requests.status','users.name as re_by','purchase_requests.created_at as re_date','users.name as Approved_by','purchase_requests.created_at as app_date','department.name as dep_name','business_units.name as bus_unit','customers.name as cust_name','purchase_requests.site','circles.name as cir_name','purchase_requests.delivery_location','purchase_requests.id as pr_id')->where('purchase_requests.status', 'LIKE', 'requested')->get();
            
            $data=[];
            foreach($response as $key=>$value){                
                $data[$key]['PR Number']=$value['pr_number']; 
                $data[$key]['PR Date']=$value['created_at']; 
                $data[$key]['Preparer Name']=$value['name']; 
                $data[$key]['PR Amendment Number']=$value['amendment_number']; 
                $data[$key]['PR Amendment Date']=($value['amendment_number'] > 0)?$value['amendment_date']:""; 
                $data[$key]['PR Status']=$value['status'];  
                $data[$key]['PR Current Status']=getPRCurrentStatus2($value['pr_id']);                 
                $data[$key]['Recommended By']=$value['re_by']; 
                $data[$key]['Recommendation Date']=$value['re_date'];
                $data[$key]['Approved_by']=getModelById('User',PrStatusCreaterName($value['pr_id'],'Approved')->user_id)->name;
                $data[$key]['Approval Date']=PrStatusCreaterName($value['pr_id'],'Approved')->created_at;
                $data[$key]['Department']=$value['dep_name']; 
                $data[$key]['Business Unit']=$value['bus_unit']; 
                $data[$key]['Customer']=$value['cust_name'];
                $data[$key]['Site']=$value['site']; 
                $data[$key]['Circle']=$value['cir_name']; 
                $data[$key]['Delivery Location']=$value['delivery_location'];                                
            }

return collect($data);
        }

if($this->excel_name == 'total_pr_amount')
        {
            $query = PurchaseRequest::query();
            $query->join('users','users.id','purchase_requests.prepared_by')
            ->join('purchase_request_items','purchase_request_items.pr_id','purchase_requests.id')
            ->join('department','department.id','purchase_requests.department')
            ->join('business_units','business_units.id','purchase_requests.business_unit')
            ->join('customers','customers.id','purchase_requests.customer')
            ->join('circles','circles.id','purchase_requests.circle')
            ->join('items','items.code','purchase_request_items.item_code')
            ->join('category','category.id','items.category')
            ->join('units','units.id','items.uom');
            
            if(isset($session)) {
                $query->where('purchase_requests.session', $session);
            }

if(isset($quarter)) {
                $query->whereBetween(\DB::raw('date(purchase_requests.pr_date)'), [$quarter_start, $quarter_end]);
            }
            
            $response = $query->select('users.name','purchase_requests.pr_number','purchase_requests.created_at','purchase_requests.description as pr_desc','purchase_requests.amendment_number','purchase_requests.amendment_number',\DB::raw('DATE_FORMAT(purchase_requests.updated_at,"%d %b %Y") as amendment_date'),'purchase_requests.status','users.name as re_by','purchase_requests.created_at as re_date','users.name as Approved_by','purchase_requests.created_at as app_date','department.name as dep_name','business_units.name as bus_unit','customers.name as cust_name','purchase_requests.site','circles.name as cir_name','purchase_requests.delivery_location','purchase_requests.id as pr_id', \DB::raw('sum(purchase_request_items.qty*purchase_request_items.rate) as pr_amount'))->where('purchase_requests.status', 'LIKE', 'Approved')->groupBy('purchase_request_items.pr_id')->get();
            
            $data=[];
            foreach($response as $key=>$value){                
                $data[$key]['PR Number']=$value['pr_number']; 
                $data[$key]['PR Date']=$value['created_at']; 
                $data[$key]['Preparer Name']=$value['name']; 
                $data[$key]['PR Amendment Number']=$value['amendment_number']; 
                $data[$key]['PR Amendment Date']=($value['amendment_number'] > 0)?$value['amendment_date']:""; 
                $data[$key]['PR Status']=$value['status'];  
                $data[$key]['PR Current Status']=getPRCurrentStatus2($value['pr_id']);               
                $data[$key]['Recommended By']=$value['re_by']; 
                $data[$key]['Recommendation Date']=$value['re_date'];
                $data[$key]['Approved_by']=getModelById('User',PrStatusCreaterName($value['pr_id'],'Approved')->user_id)->name;
                $data[$key]['Approval Date']=PrStatusCreaterName($value['pr_id'],'Approved')->created_at;
                $data[$key]['Department']=$value['dep_name']; 
                $data[$key]['Business Unit']=$value['bus_unit']; 
                $data[$key]['Customer']=$value['cust_name'];
                $data[$key]['Site']=$value['site']; 
                $data[$key]['Circle']=$value['cir_name']; 
                $data[$key]['Delivery Location']=$value['delivery_location'];                  
                $data[$key]['PR Amount']=$value['pr_amount'];                  
            }

return collect($data);
        }                
     
    }
    
    public function headings(): array
    { 
        if($this->excel_name == 'open_pr_count'){
            return [                 
                'PR Number',   
                'PR Date', 
                'Preparer Name', 
                'PR Amendment Number', 
                'PR Amendment Date',   
                'PR Status',  
                'PR Current Status',
                'Recommended By',  
                'Recommendation Date', 
                'Approved By', 
                'Approval Date',   
                'Department',  
                'Business Unit',   
                'Customer',    
                'Site',    
                'Circle',  
                'Delivery Location',                                 
            ];
        }
        
        if($this->excel_name == 'total_pr_count'){
            return [
                'PR Number',   
                'PR Date', 
                'Preparer Name',  
                'PR Amendment Number', 
                'PR Amendment Date',   
                'PR Status',   
                'PR Current Status',
                'Recommended By',  
                'Recommendation Date', 
                'Approved By', 
                'Approval Date',   
                'Department',  
                'Business Unit',   
                'Customer',    
                'Site',    
                'Circle',  
                'Delivery Location',                  
            ];
        }
        
        if($this->excel_name == 'total_un_app_pr_count'){
            return [
                'PR Number',   
                'PR Date', 
                'Preparer Name',   
                'PR Amendment Number', 
                'PR Amendment Date',   
                'PR Status',  
                'PR Current Status', 
                'Recommended By',  
                'Recommendation Date', 
                'Approved By', 
                'Approval Date',   
                'Department',  
                'Business Unit',   
                'Customer',    
                'Site',    
                'Circle',  
                'Delivery Location',                   
            ];
        }

if($this->excel_name == 'total_pr_amount'){
            return [
                'PR Number',   
                'PR Date', 
                'Preparer Name',  
                'PR Amendment Number', 
                'PR Amendment Date',   
                'PR Status',
                'PR Current Status',   
                'Recommended By',  
                'Recommendation Date', 
                'Approved By', 
                'Approval Date',   
                'Department',  
                'Business Unit',   
                'Customer',    
                'Site',    
                'Circle',  
                'Delivery Location',       
                'PR Amount',           
            ];
        }                        
        
    }
    
    
}