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 DashboardReportExport implements FromCollection,WithHeadings
{
    /**
    * @return \Illuminate\Support\Collection
    */
    protected $excel_name;

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

public function collection()
    {
        set_time_limit(300);
        if($this->excel_name == 'open_pr_count')
        {
            $response = PurchaseRequest::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')->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','category.name as cat_name','purchase_request_items.item_code', 'purchase_request_items.make','items.description as item_dis','items.specification','units.name as unit_name','purchase_request_items.qty','purchase_requests.id as pr_id')->where('purchase_requests.status', 'LIKE', 'Approved')->whereNotIn('purchase_requests.id', function($query){
    			$query->select('pr_id')->from('purchase_order_items')->groupBy('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]['Item Category']=$value['cat_name']; 
                $data[$key]['Item Code']=$value['item_code']; 
                $data[$key]['Item Description']=$value['item_dis']; 
                $data[$key]['Specification']=$value['specification']; 
                $data[$key]['Make']=$value['make']; 
                $data[$key]['UoM']=$value['unit_name']; 
                $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')
        {
            $response = PurchaseRequest::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')->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','category.name as cat_name','purchase_request_items.item_code', 'purchase_request_items.make','items.description as item_dis','items.specification','units.name as unit_name','purchase_request_items.qty','purchase_requests.id as pr_id')->where('purchase_requests.status', 'LIKE', 'Approved')->whereIn('purchase_requests.id', function($query){
    			$query->select('pr_id')->from('purchase_order_items')->groupBy('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]['Item Category']=$value['cat_name']; 
                $data[$key]['Item Code']=$value['item_code']; 
                $data[$key]['Item Description']=$value['item_dis']; 
                $data[$key]['Specification']=$value['specification']; 
                $data[$key]['Make']=$value['make']; 
                $data[$key]['UoM']=$value['unit_name']; 
                $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')
        {
            $response = PurchaseRequest::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')->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','category.name as cat_name','purchase_request_items.item_code', 'purchase_request_items.make','items.description as item_dis','items.specification','units.name as unit_name','purchase_request_items.qty','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]['Item Category']=$value['cat_name']; 
                $data[$key]['Item Code']=$value['item_code']; 
                $data[$key]['Item Description']=$value['item_dis']; 
                $data[$key]['Specification']=$value['specification']; 
                $data[$key]['Make']=$value['make']; 
                $data[$key]['UoM']=$value['unit_name']; 
                $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 == 'open_po_count') {
            
            $response = PurchaseOrder::join('users','users.id','purchase_orders.po_created_by')
            ->join('purchase_order_items','purchase_order_items.po_id','purchase_orders.id')
            ->join('purchase_requests','purchase_requests.id','purchase_order_items.pr_id')
             ->join('department','department.id','purchase_requests.department')
             ->join('business_units','business_units.id','purchase_orders.business_unit')
             ->join('customers','customers.id','purchase_requests.customer')
            ->join('vendors','vendors.id','purchase_orders.vendor_id')
            ->join('circles','circles.id','purchase_orders.circle')
            ->join('items','items.code','purchase_order_items.item_code')
            ->join('category','category.id','items.category')
            ->join('units','units.id','items.uom')
            ->leftJoin('irn_items', function($join){
                $join->on('irn_items.po_id','purchase_order_items.po_id')
                    ->on('irn_items.item_code','purchase_order_items.item_code');
            })
            
            ->leftJoin('irns','irns.id','irn_items.irn_id')
            ->select('purchase_requests.pr_number','purchase_requests.created_at','users.name as preparer_name','department.name as d_name','business_units.name as bu_name','customers.name as cus_name','purchase_orders.created_at','purchase_orders.po_number','purchase_orders.document_type','purchase_orders.created_at as r_date','purchase_orders.status','vendors.vendor_code','vendors.vendor_name','vendors.address','vendors.gst as gstin_no','category.name as cat_name','purchase_order_items.item_code','items.description as i_d','purchase_order_items.specification','purchase_order_items.make','units.name as unit_name','purchase_order_items.qty','purchase_order_items.rate',\DB::raw('(purchase_order_items.rate*purchase_order_items.qty) as basic_amount'),\DB::raw('(purchase_order_items.rate*purchase_order_items.qty*items.gst/100) as gst_amount'),\DB::raw('((purchase_order_items.rate*purchase_order_items.qty*items.gst/100)+(purchase_order_items.rate*purchase_order_items.qty)) as total_amount'),'items.gst','purchase_orders.id as po_id','circles.name as circle','circles.gst_number','purchase_orders.Bill_to_address','purchase_orders.delivery_address','purchase_orders.warranty_terms','purchase_orders.payment_terms','purchase_orders.delivery_terms',
                'irns.irn_no',
                'irns.receive_date',
                'irns.invoice_no',
                'irns.invoice_date',
                'irn_items.qty as irns_qty',
                'irn_items.rate as irns_rate',
               \DB::raw('(irn_items.rate*irn_items.qty) as invoice_amount'),
               'irns.status as irns_status')->whereNotIn('purchase_orders.id', function($query){
        			$query->select('po_id')->from('irn_items')->groupBy('po_id');
        		})->where('purchase_orders.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['preparer_name']; 
                $data[$key]['Department']=$value['d_name'];  
                $data[$key]['Business Unit']=$value['bu_name'];
                $data[$key]['Customer']=$value['cus_name'];
                $data[$key]['PO Creation Date']=$value['created_at'];
                $data[$key]['PO Number']=$value['po_number'];
                $data[$key]['PO Recommendated By']=$value['document_type'];
                $data[$key]['PO Date']=$value['r_date'];
                $data[$key]['PO Approved By']=getModelById('User',PoStatusCreaterName($value['po_id'],'Approved')->user_id)->name;
                $data[$key]['PO Approval Date']=PoStatusCreaterName($value['po_id'],'Approved')->created_at;
                $data[$key]['PO Status']=$value['status']; 
                $data[$key]['Vendor Code']=$value['vendor_code']; 
                $data[$key]['Vendor name']=$value['vendor_name']; 
                $data[$key]['Vendor Address']=$value['address']; 
                $data[$key]['Vendor GSTIN Number']=$value['gstin_no']; 
                $data[$key]['Item Category']=$value['cat_name']; 
                $data[$key]['Item Code']=$value['item_code']; 
                $data[$key]['Item Description']=$value['i_d']; 
                $data[$key]['Specification']=$value['specification'];
                $data[$key]['Make']=$value['make'];
                $data[$key]['UoM']=$value['unit_name']; 
                $data[$key]['PR Qty']=$value['qty']; 
                $data[$key]['Unit Price']=$value['rate']; 
                $data[$key]['Basic Amount']=$value['basic_amount']; 
                $data[$key]['GST']=$value['gst'];
                $data[$key]['Tax Amount']=$value['gst_amount'];
                $data[$key]['Total Amount']=$value['total_amount'];
                $data[$key]['Billing Circle']=$value['circle'];
                $data[$key]['Bill to Address']=$value['Bill_to_address'];
                $data[$key]['Delivery Address']=$value['delivery_address'];
                $data[$key]['Circle']=$value['circle'];
                $data[$key]['GSTIN Number']=$value['gst_number'];
                $data[$key]['Warranty Term']=$value['warranty_terms'];
                $data[$key]['Delivery Term']=$value['delivery_terms'];
                $data[$key]['Payment Term']=$value['payment_terms'];
                $data[$key]['Irn No']=$value['irn_no'];
                $data[$key]['Reciept Date']=$value['receive_date'];
                $data[$key]['Invoice Number']=$value['invoice_no'];
                $data[$key]['Invoice Date']=$value['invoice_date'];
                $data[$key]['Invoice Qty']=$value['irns_qty'];
                $data[$key]['Invoice Unit Price']=$value['irns_rate'];
                $data[$key]['Invoice Amount']=$value['invoice_amount'];
                $data[$key]['Payment Status']=$value['irns_status'];
            }
            return collect($data);

}
        
        
        
        
    }
    
    public function headings(): array
    { 
        if($this->excel_name == 'open_pr_count'){
            return [
                'Preparer Name',   
                'PR Number',   
                'PR Date', 
                'PR Amendment Number', 
                'PR Amendment Date',   
                'PR Status',   
                'Recommended By',  
                'Recommendation Date', 
                'Approved By', 
                'Approval Date',   
                'Department',  
                'Business Unit',   
                'Customer',    
                'Site',    
                'Circle',  
                'Delivery Location',   
                'Item Category' ,  
                'Item Code' ,  
                'Item Description' ,   
                'Specification',   
                'UoM', 
                'PR Qty',
            ];
        }
        
        if($this->excel_name == 'total_pr_count'){
            return [
                'Preparer Name',   
                'PR Number',   
                'PR Date', 
                'PR Amendment Number', 
                'PR Amendment Date',   
                'PR Status',   
                'Recommended By',  
                'Recommendation Date', 
                'Approved By', 
                'Approval Date',   
                'Department',  
                'Business Unit',   
                'Customer',    
                'Site',    
                'Circle',  
                'Delivery Location',   
                'Item Category' ,  
                'Item Code' ,  
                'Item Description' ,   
                'Specification',   
                'UoM', 
                'PR Qty',
            ];
        }
        
        if($this->excel_name == 'total_un_app_pr_count'){
            return [
                'Preparer Name',   
                'PR Number',   
                'PR Date', 
                'PR Amendment Number', 
                'PR Amendment Date',   
                'PR Status',   
                'Recommended By',  
                'Recommendation Date', 
                'Approved By', 
                'Approval Date',   
                'Department',  
                'Business Unit',   
                'Customer',    
                'Site',    
                'Circle',  
                'Delivery Location',   
                'Item Category' ,  
                'Item Code' ,  
                'Item Description' ,   
                'Specification',   
                'UoM', 
                'PR Qty',
            ];
        }
        
        
        if($this->excel_name == 'open_po_count'){
            return [
                'PR Number',   
                'PR Date', 
                'Preparer Name',   
                'Department',  
                'Business Unit',   
                'Customer',    
                'PO Creation Date',    
                'PO Number',   
                'PO Recommendated By', 
                'PO Date',  
                'PO Approved By',  
                'PO Approval Date',    
                'PO Status',   
                'Vendor Code', 
                'Vendor Name', 
                'Vendor Address',
                'Vendor GSTIN Number', 
                'Item Category',   
                'Item Code',   
                'Item Description',    
                'Specification',   
                'Make',    
                'UoM', 
                'PO Qty',  
                'Unit Price',  
                'Basic Amount',    
                'Tax %age',    
                'Tax Amount', 
                'Total Amount',    
                'Billing Circle',  
                'Bill to Address', 
                'Delivery Address',    
                'Circle', 
                'GSTIN Number', 
                'Warranty Term',   
                'Delivery Term',   
                'Payment Term', 
                'Irn No',
                'Reciept Date',    
                'Invoice Number',  
                'Invoice Date',    
                'Invoice Qty', 
                'Invoice Unit Price',  
                'Invoice Amount',  
                'Payment Status',  
    
            ];
        }
        
    }
    
    
}