000

<?php

namespace App\Exports;

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

class DashboardPOReportExport implements FromCollection,WithHeadings, WithColumnFormatting
{
    /**
    * @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_po_count') {

$query = \App\PurchaseOrder::query();
            $query->join('purchase_order_items as poi', 'poi.po_id', 'purchase_orders.id');
            if(isset($quarter) && $quarter != '') {
              $query->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$quarter_start,$quarter_end]);
            }
            $open_po = $query->select('purchase_orders.id')->where('session', $session)->where('purchase_orders.status', 'LIKE', 'Approved')->groupBy('poi.po_id')->havingRaw('SUM(qty) > SUM(rqty)')->get()->toArray();
           
            $poArr = array();
            foreach ($open_po as $key => $po) {
              array_push($poArr, $po['id']);
            }

$query = PurchaseOrder::query();
            $query->join('users','users.id','purchase_orders.po_created_by')          
            ->leftjoin('business_units','business_units.id','purchase_orders.business_unit')            
            ->leftjoin('vendors','vendors.id','purchase_orders.vendor_id')
            ->leftjoin('circles','circles.id','purchase_orders.circle');

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

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

$data=[];
            foreach($response as $key=>$value){
                                                
                $data[$key]['PO Number']=$value['po_number'];                 
                $data[$key]['PO Date']=$value['created_at'];
                $data[$key]['PO Status']=$value['status']; 
                $data[$key]['PO Current Status']= getPOCurrentStatusPOReport($value['id']);
                $data[$key]['Vendor Code']=$value['vendor_code']; 
                $data[$key]['Vendor name']=$value['vendor_name'];                     
                $data[$key]['Business Unit']=$value['bu_name'];                                          
                $data[$key]['Circle']=$value['circle'];    
                $data[$key]['Preparer Name']=$value['preparer_name'];
                
            }
            return collect($data);

}

if($this->excel_name == 'total_po_count') {

$query = PurchaseOrder::query();
            $query->join('users','users.id','purchase_orders.po_created_by')           
            ->join('business_units','business_units.id','purchase_orders.business_unit')            
            ->join('vendors','vendors.id','purchase_orders.vendor_id')
            ->join('circles','circles.id','purchase_orders.circle');

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

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

$response = $query->select('purchase_orders.id', 'users.name as preparer_name','business_units.name as bu_name','purchase_orders.created_at','purchase_orders.po_number','purchase_orders.status','vendors.vendor_code','vendors.vendor_name','circles.name as circle')->where('purchase_orders.status', 'LIKE', 'Approved')->get();
                
            $data=[];
            foreach($response as $key=>$value){
                                                
                $data[$key]['PO Number']=$value['po_number'];                 
                $data[$key]['PO Date']=$value['created_at'];
                $data[$key]['PO Status']=$value['status']; 
                $data[$key]['PO Current Status']= getPOCurrentStatusPOReport($value['id']);
                $data[$key]['Vendor Code']=$value['vendor_code']; 
                $data[$key]['Vendor name']=$value['vendor_name'];                     
                $data[$key]['Business Unit']=$value['bu_name'];                
                $data[$key]['Circle']=$value['circle'];    
                $data[$key]['Preparer Name']=$value['preparer_name'];
                
            }
            return collect($data);
        }    
        
        
        if($this->excel_name == 'total_un_app_po_count') {            
         
            $query = PurchaseOrder::query();
            $query->join('users','users.id','purchase_orders.po_created_by')            
            ->join('business_units','business_units.id','purchase_orders.business_unit')            
            ->join('vendors','vendors.id','purchase_orders.vendor_id')
            ->join('circles','circles.id','purchase_orders.circle');

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

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

$response = $query->select('purchase_orders.id', 'users.name as preparer_name','business_units.name as bu_name','purchase_orders.created_at','purchase_orders.po_number','purchase_orders.status','vendors.vendor_code','vendors.vendor_name','circles.name as circle')->where('purchase_orders.status', 'LIKE', 'requested')->get();
           
            $data=[];
            foreach($response as $key=>$value){
                                                
                $data[$key]['PO Number']=$value['po_number'];                 
                $data[$key]['PO Date']=$value['r_date'];
                $data[$key]['PO Status']=$value['status']; 
                $data[$key]['PO Current Status']= getPOCurrentStatusPOReport($value['id']);              
                $data[$key]['Vendor Code']=$value['vendor_code']; 
                $data[$key]['Vendor name']=$value['vendor_name'];                     
                $data[$key]['Business Unit']=$value['bu_name'];                
                $data[$key]['Circle']=$value['circle'];    
                $data[$key]['Preparer Name']=$value['preparer_name'];
            }
            return collect($data);
        }

if($this->excel_name == 'total_po_amount') {            
         
          $query = PurchaseOrder::query();
          $query->join('users','users.id','purchase_orders.po_created_by')
          ->leftjoin('purchase_order_items','purchase_order_items.po_id','purchase_orders.id')       
          ->join('business_units','business_units.id','purchase_orders.business_unit')        
          ->join('vendors','vendors.id','purchase_orders.vendor_id')
          ->join('circles','circles.id','purchase_orders.circle')
          ->leftjoin('items','items.code','purchase_order_items.item_code');

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

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

$response = $query->select('purchase_orders.id', 'users.name as preparer_name','business_units.name as bu_name','purchase_orders.created_at','purchase_orders.po_number','purchase_orders.status','vendors.vendor_code','vendors.vendor_name','circles.name as circle', \DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_basic_amount'), \DB::raw('sum((purchase_order_items.rate*purchase_order_items.qty*items.gst)/100) as tax_amount'))->where('purchase_orders.status', 'LIKE', 'Approved')->groupBy('purchase_order_items.po_id')->get();
         
          $data=[];
          foreach($response as $key=>$value){
                                              
              $data[$key]['PO Number']=$value['po_number'];                 
              $data[$key]['PO Date']=$value['created_at'];
              $data[$key]['PO Status']=$value['status']; 
              $data[$key]['PO Current Status']= getPOCurrentStatusPOReport($value['id']);
              $data[$key]['Vendor Code']=$value['vendor_code']; 
              $data[$key]['Vendor name']=$value['vendor_name'];     
              $data[$key]['PO Basic Amount']=$value['po_basic_amount'];     
              $data[$key]['PO Tax Amount']=$value['tax_amount'];     
              $data[$key]['PO Total Amount']=$value['po_basic_amount'] + $value['tax_amount'];
              $data[$key]['Business Unit']=$value['bu_name'];              
              $data[$key]['Circle']=$value['circle'];    
              $data[$key]['Preparer Name']=$value['preparer_name'];     
              
          }
          return collect($data);
        }

if($this->excel_name == 'total_mrn_amount') {                                 
            
            $query = \App\Irn::query();
            $query->join('irn_items', 'irn_items.irn_id', 'irns.id')
            ->join('items', 'items.code', 'irn_items.item_code')
            ->join('purchase_order_items as poi', 'poi.po_id', 'irn_items.po_id')
            ->join('purchase_orders as po', 'po.id', 'poi.po_id')
            ->join('vendors','vendors.id','po.vendor_id');
            if(isset($session)) {
                $query->where('irns.current_session', $session);
            }
  
            if(isset($quarter)) {
              $query->whereBetween(\DB::raw('date(irns.created_at)'), [$quarter_start, $quarter_end]);
            }

$response = $query->select('irns.irn_no', 'irns.receive_date', 'irns.invoice_no', 'irns.invoice_date', \DB::raw('sum(irn_items.qty*irn_items.rate) as invoice_basic_amount'), \DB::raw('sum((irn_items.qty*irn_items.rate*items.gst)/100) as invoice_tax_amount'), 'po.po_number', 'po.po_date', 'vendors.vendor_code', 'vendors.vendor_name', \DB::raw('sum(poi.qty*poi.rate) as po_basic_amount'), \DB::raw('sum((poi.qty*poi.rate*items.gst)/100) as po_tax_amount'), 'po.Bill_to_address', 'po.delivery_address', 'po.payment_terms', 'irns.generated_by')->groupBy('irn_items.irn_id')->get();
           
            $data=[];
            foreach($response as $key=>$value){
                                                
                $data[$key]['MRN Number']=$value['irn_no'];                                 
                $data[$key]['MRN Date']=$value['receive_date'];                                 
                $data[$key]['Invoice No']=$value['invoice_no'];
                $data[$key]['Invoice Date']=$value['invoice_date'];                 
                $data[$key]['Invoice Basic Amount']=$value['invoice_basic_amount'];     
                $data[$key]['Invoice Freight Amount']='';     
                $data[$key]['Invoice Tax Amount']=$value['invoice_tax_amount'];                     
                $data[$key]['Invoice Total Amount']=$value['irn_basic_amount']+$value['invoice_tax_amount']; 
                $data[$key]['PO Number']=$value['po_number']; 
                $data[$key]['PO Date']=$value['po_date']; 
                $data[$key]['Vendor Code']=$value['vendor_code']; 
                $data[$key]['Vendor Name']=$value['vendor_name']; 
                $data[$key]['PO Basic Amount']= $value['po_basic_amount']; 
                $data[$key]['PO Tax Amount']= $value['po_tax_amount']; 
                $data[$key]['PO Total Amount']= $value['po_basic_amount']+$value['po_tax_amount'];
                $data[$key]['Bill To Address']=$value['Bill_to_address']; 
                $data[$key]['Delivery Address']=$value['delivery_address']; 
                $data[$key]['Payment Terms']=$value['payment_terms']; 
                $data[$key]['Generated By']= ($value['generated_by'] == 1)?"SCM":"ASSET" ; 
            }
            return collect($data);
        }

if($this->excel_name == 'total_suppliers') {            
          
            $query = Vendor::query();
            $query->join('purchase_orders', 'purchase_orders.vendor_id', 'vendors.id');
            $query->join('vendor_categories', 'vendor_categories.id', 'vendors.category');

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

$response = $query->select('vendors.*', 'vendor_categories.name as category_name')->where('vendors.is_active',1)->groupBy('vendors.id')->get();
            
            $data=[];
            $key = 0;
            foreach($response as $key=>$value){
                                                
                $data[$key]['Category']=$value['category_name'];                 
                $data[$key]['Vendor Code']=$value['vendor_code'];
                $data[$key]['Vendor Name']=$value['vendor_name']; 
                $data[$key]['Address']=$value['address']; 
                $data[$key]['Deals In']=$value['deals_in'];     
                $data[$key]['Sub Category']=$value['sub_category'];     
                $data[$key]['PO To']=$value['po_to'];     
                $data[$key]['Contact No']=$value['contact_no'];     
                $data[$key]['Contact Person 1']=$value['contact_person_1'];  
                $data[$key]['Contact Number 1']=$value['contact_no_1'];  
                $data[$key]['Email Address 1']=$value['email_address_1'];
                $data[$key]['Contact Person 2']=$value['contact_person_2'];  
                $data[$key]['Contact Number 2']=$value['contact_no_2'];  
                $data[$key]['Other Email']=$value['other_email'];
                $data[$key]['Account Number']=$value['account_number'];           
                $data[$key]['Bank Name']=$value['bank_name'];           
                $data[$key]['IFSC Code']=$value['ifsc_code'];           
                $data[$key]['GST']=$value['gst'];                           
                $data[$key]['Payment Terms']=$value['payment_terms'];    
                $data[$key]['Remarks']=$value['remarks']; 
                $key++;
            }
            return collect($data);
        }

if($this->excel_name == 'mrn_po_count') {
            $query = \App\Irn::query();
            $query->leftjoin('irn_items', 'irn_items.irn_id', 'irns.id')
            ->leftjoin('items', 'items.code', 'irn_items.item_code')
            ->leftjoin('purchase_order_items as poi', 'poi.po_id', 'irn_items.po_id')
            ->leftjoin('purchase_orders as po', 'po.id', 'irn_items.po_id')
            ->leftjoin('vendors','vendors.id','po.vendor_id');
            if(isset($session)) {
                $query->where('irns.current_session', $session);
            }
  
            if(isset($quarter)) {
              $query->whereBetween(\DB::raw('date(irns.created_at)'), [$quarter_start, $quarter_end]);
            }

$response = $query->select('irns.irn_no', 'irns.receive_date', 'irns.invoice_no', 'irns.invoice_date', \DB::raw('sum(irn_items.qty*irn_items.rate) as invoice_basic_amount'), \DB::raw('sum((irn_items.qty*irn_items.rate*items.gst)/100) as invoice_tax_amount'), 'po.po_number', 'po.po_date', 'vendors.vendor_code', 'vendors.vendor_name', \DB::raw('sum(poi.qty*poi.rate) as po_basic_amount'), \DB::raw('sum((poi.qty*poi.rate*items.gst)/100) as po_tax_amount'), 'po.Bill_to_address', 'po.delivery_address', 'po.payment_terms', 'irns.generated_by')->groupBy('irn_items.irn_id')->get();
           
            $data=[];
            foreach($response as $key=>$value){
                                                
                $data[$key]['MRN Number']=$value['irn_no'];                                 
                $data[$key]['MRN Date']=$value['receive_date'];                                 
                $data[$key]['Invoice No']=$value['invoice_no'];
                $data[$key]['Invoice Date']=$value['invoice_date'];                 
                $data[$key]['Invoice Basic Amount']=$value['invoice_basic_amount'];     
                $data[$key]['Invoice Freight Amount']='';     
                $data[$key]['Invoice Tax Amount']=$value['invoice_tax_amount'];                     
                $data[$key]['Invoice Total Amount']=$value['irn_basic_amount']+$value['invoice_tax_amount']; 
                $data[$key]['PO Number']=$value['po_number']; 
                $data[$key]['PO Date']=$value['po_date']; 
                $data[$key]['Vendor Code']=$value['vendor_code']; 
                $data[$key]['Vendor Name']=$value['vendor_name']; 
                $data[$key]['PO Basic Amount']= $value['po_basic_amount']; 
                $data[$key]['PO Tax Amount']= $value['po_tax_amount']; 
                $data[$key]['PO Total Amount']= $value['po_basic_amount']+$value['po_tax_amount'];
                $data[$key]['Bill To Address']=$value['Bill_to_address']; 
                $data[$key]['Delivery Address']=$value['delivery_address']; 
                $data[$key]['Payment Terms']=$value['payment_terms']; 
                $data[$key]['Generated By']= ($value['generated_by'] == 1)?"SCM":"ASSET" ;               
                
            }
            return collect($data);
        }
     
    }

public function columnFormats(): array
    {
        if($this->excel_name == 'open_po_count') {                     
            return [];
        }
        if($this->excel_name == 'total_po_count') {                     
            return [];
        }
        if($this->excel_name == 'total_po_amount') {
            return [];
        }
        if($this->excel_name == 'total_un_app_po_count') {                     
            return [];
        }
        if($this->excel_name == 'total_mrn_amount') {                     
            return [
                'B' => '0',          
            ];
        }
        if($this->excel_name == 'total_suppliers') {                     
            return [];
        } 
        if($this->excel_name == 'mrn_po_count') {                     
            return [];
        }        
    }
    
    public function headings(): array
    {               
       
        if($this->excel_name == 'open_po_count'){
            return [                
                'PO Number',                   
                'PO Date',  
                'PO Status',                
                'PO Current Status',                
                'Vendor Code', 
                'Vendor Name',                     
                'Business Unit',                                            
                'Circle', 
                'Preparer Name',              
            ];
        }

if($this->excel_name == 'total_po_count'){
            return [                
                'PO Number',                   
                'PO Date',  
                'PO Status',                
                'PO Current Status',                
                'Vendor Code', 
                'Vendor Name',                     
                'Business Unit',                              
                'Circle', 
                'Preparer Name',                
            ];
        }
       
        if($this->excel_name == 'total_un_app_po_count'){
            return [                
                'PO Number',                   
                'PO Date',  
                'PO Status',                
                'PO Current Status',                
                'Vendor Code', 
                'Vendor Name',                     
                'Business Unit',                   
                'Circle', 
                'Preparer Name',                
            ];
        }

if($this->excel_name == 'total_po_amount'){
          return [                
              'PO Number',                   
              'PO Date',  
              'PO Status',                
              'PO Current Status',                
              'Vendor Code', 
              'Vendor Name',     
              'PO Basic Amount', 
              'PO Tax Amount',    
              'PO Total Amount',                 
              'Business Unit',                 
              'Circle', 
              'Preparer Name',              
          ];
        }

if($this->excel_name == 'total_mrn_amount'){
            return [                
                'MRN Number',    
                'MRN Date',                
                'Invoice No',                  
                'Invoice Date',                
                'Invoice Basic Amount',
                'Invoice Freight Amount',
                'Invoice Tax Amount',
                'Invoice Total Amount',
                'PO Number',
                'PO Date',
                'Vendor Name',
                'Vendor Code',
                'PO Basic Amount',
                'PO Tax Amount',
                'PO Total Amount',
                'Bill To Address',
                'Delivery Address',
                'Payment Terms',   
                'Generated By'    
            ];
        }

if($this->excel_name == 'total_suppliers') {
            return [
                'Category',
                'Vendor Code',
                'Vendor Name',
                'Address',
                'Deals In',
                'Sub Category',
                'PO To',
                'Contact No',
                'Contact Person 1',
                'Contact Number 1',
                'Email Address 1',
                'Contact Person 2',
                'Contact Email 2',
                'Other Email',
                'Account Number',
                'Bank Name',
                'IFSC Code',
                'GST',
                'Payment Terms',
                'Remarks'
            ];
        }

if($this->excel_name == 'mrn_po_count'){
            return [                
                'MRN Number',    
                'MRN Date',                
                'Invoice No',                  
                'Invoice Date',                
                'Invoice Basic Amount',
                'Invoice Freight Amount',
                'Invoice Tax Amount',
                'Invoice Total Amount',
                'PO Number',
                'PO Date',
                'Vendor Name',
                'Vendor Code',
                'PO Basic Amount',
                'PO Tax Amount',
                'PO Total Amount',
                'Bill To Address',
                'Delivery Address',
                'Payment Terms',   
                'Generated By'             
            ];
        }       
    }
    
    
}