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

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

public function collection()
    {
        set_time_limit(300);
        //dd($this->to_date);
        if($this->excel_name == 1){
            // dd('stop');
       $response = PurchaseRequest::join('users','users.id','purchase_requests.prepared_by')
        ->join('purchase_request_items','purchase_request_items.pr_id','purchase_requests.id')
        // ->join('purchase_request_logs','purchase_request_logs.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('projects','projects.id','purchase_requests.project')
        ->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')
        //->join('purchase_order_items','purchase_order_items.pr_id','purchase_requests.id')        
        ->leftjoin("purchase_order_items",function($join){
            $join->on("purchase_order_items.item_code","=","purchase_request_items.item_code")
                ->on("purchase_order_items.pr_id","=","purchase_request_items.pr_id");
        })
        ->leftjoin('purchase_orders','purchase_orders.id','purchase_order_items.po_id')
        //->join('purchase_order_logs','purchase_order_logs.po_id','purchase_orders.id')
        ->leftjoin('vendors','vendors.id','purchase_orders.vendor_id')
        // ->leftjoin('irn_items','irn_items.po_id','purchase_order_items.po_id')
        ->leftJoin('irn_items', function($join){
            $join->on('irn_items.item_code', '=', 'purchase_order_items.item_code')
            ->on('irn_items.po_id', '=', 'purchase_order_items.po_id');
        })
        ->leftjoin('irns','irns.id','irn_items.irn_id')
        
        ->select('purchase_requests.pr_number',
            'purchase_requests.created_at',
            'users.name',
            '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',
            'department.name as dep_name',
            'business_units.name as bus_unit',
            'customers.name as cust_name',
            'circles.name as cir_name',
            'purchase_requests.delivery_location',
            'purchase_order_items.created_at as po_creation_date',
            'purchase_orders.po_number',
            'purchase_requests.site',
            'purchase_order_items.created_at as po_recom_date',
            'users.name as Appr_by',
            //'purchase_order_logs.created_at as ap_date',
            'purchase_orders.status as po_status',
            'vendors.vendor_code',
            'vendors.vendor_name',
            'vendors.address as ven_address',
            'vendors.gst as ven_gstin',
            'category.name as cat_name',
            'purchase_request_items.item_code',
            'items.description as item_dis',
            'purchase_request_items.specification',
            'items.make',
            'units.name as unit_name',
            'purchase_request_items.qty as pr_qty',
            'purchase_order_items.qty as po_qty',
            'purchase_order_items.item_code as po_item_code',
            'purchase_request_items.rate as pr_unitp',
            \DB::raw('(purchase_request_items.rate*purchase_request_items.qty) as basic_amount'),
            'items.gst as item_gst',
            \DB::raw('((purchase_request_items.rate*purchase_request_items.qty)*items.gst/100) as tax_amount'),
            \DB::raw('((purchase_request_items.rate*purchase_request_items.qty)+((purchase_request_items.rate*purchase_request_items.qty)*items.gst/100)) as total_amount'),
            'purchase_orders.Bill_to_address',
            'purchase_orders.delivery_address',
            'purchase_orders.warranty_terms',
            'purchase_orders.delivery_terms',
            'purchase_orders.payment_terms',
            'purchase_requests.id as pr_id',
            'purchase_orders.id as po_id',
            'circles.gst_number',
            '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')
        ->whereBetween(DB::raw('date(purchase_requests.created_at)'),[$this->from_date, $this->to_date])
        ->get();

// dd($response);
       
        $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_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']; 
            $data[$key]['PO Creation Date']=$value['po_creation_date']; 
            $data[$key]['PO Number']=$value['po_number'];
            $data[$key]['PO Recommendated By']=$value['re_by'];
            $data[$key]['PO Recommendation Date']=$value['po_creation_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['po_status'];

$data[$key]['PO Item code']=$value['po_item_code'];

$data[$key]['Vendor Code']=$value['vendor_code']; 
            $data[$key]['Vendor name']=$value['vendor_name']; 
            $data[$key]['Vendor Address']=$value['ven_address']; 
            $data[$key]['Vendor GSTIN Number']=$value['ven_gstin'];             
            $data[$key]['PR Qty']=$value['pr_qty']; 
            $data[$key]['PO Qty']=$value['po_qty']; 
            $data[$key]['Unit Price']=$value['pr_unitp']; 
            $data[$key]['Basic Amount']=$value['basic_amount']; 
            $data[$key]['Tax %age']=$value['item_gst'];
            $data[$key]['Tax Amount']=$value['tax_amount'];
            $data[$key]['Total Amount']=$value['total_amount'];
            //$data[$key]['Billing Circle']=$value['cir_name'];
            $data[$key]['Bill to Address']=$value['Bill_to_address'];
            $data[$key]['Delivery Address']=$value['delivery_address'];
            $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]['Reciever Name']=$value['gst'];
            $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 Tax %age']=$value['gst'];
            $data[$key]['Invoice Amount']=$value['invoice_amount'];
            $data[$key]['Payment Status']=$value['irns_status'];
            //$data[$key]['Payment Due Date']=$value['gst']; 
        }
        //dd($data[0]);
        return collect($data);
        //dd($response);
        //return $response;
    }
    if($this->excel_name == 2){
        //dd($this->from_date);
        $response = PurchaseRequest::join('users','users.id','purchase_requests.prepared_by')
        ->join('purchase_request_items','purchase_request_items.pr_id','purchase_requests.id')
        //->join('purchase_request_logs','purchase_request_logs.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('projects','projects.id','purchase_requests.project')
        ->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.pr_date','purchase_requests.description','purchase_requests.amendment_number','purchase_requests.amendment_number')
        // ->whereBetween('purchase_requests.pr_date',[$this->from_date, $this->to_date])
        // ->get();
        ->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.project','category.name as cat_name','purchase_request_items.item_code','items.description as item_dis','items.specification','units.name as unit_name','purchase_request_items.qty','purchase_requests.id as pr_id')
        ->whereBetween('purchase_requests.created_at',[$this->from_date, $this->to_date])
        // ->groupBy('purchase_requests.id')
        ->get();
         $data=[];
         foreach($response as $key=>$value){
            $data[$key]['Preparer Name']=$value['name']; 
            $data[$key]['PR Number']=$value['pr_number']; 
            $data[$key]['PR Date']=$value['created_at']; 
            // $data[$key]['PR Description']=$value['pr_desc']; 
            $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]['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]['Project Name']=$value['project'];
            $data[$key]['Site']=$value['site']; 
            $data[$key]['Circle']=$value['cir_name']; 
            $data[$key]['Delivery Location']=$value['delivery_location']; 
            $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]['UoM']=$value['unit_name']; 
            $data[$key]['PR Qty']=$value['qty']; 
        }
        return collect($data);
        //return $response;
    }
    if($this->excel_name == 3){
        $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('projects','projects.id','purchase_requests.project')
        //->join('purchase_order_logs','purchase_order_logs.po_id','purchase_orders.id')
        //->join('users','users.id','purchase_order_logs.user_id')
        ->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','irn_items.po_id','purchase_orders.id')
        ->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')

->whereBetween('purchase_orders.created_at',[$this->from_date, $this->to_date])
        //->groupBy('purchase_orders.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['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]['Reciever Name']=$value['gst'];
            $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 Tax %age']=$value['gst'];
            $data[$key]['Invoice Amount']=$value['invoice_amount'];
            $data[$key]['Payment Status']=$value['irns_status'];
            //$data[$key]['Payment Due Date']=$value['gst'];
        }
        //dd($data[5]);
        return collect($data);
        //return $response;
    }
    if($this->excel_name == 4){
        //$response = Item::whereBetween('created_at',[$this->from_date, $this->to_date])->get();
        // DB::enableQueryLog();
        $response = Item::join('category','category.id','items.category')
        ->join('units','units.id','items.uom')
        // ->join('purchase_order_items','purchase_order_items.item_code','items.code')
        
        ->leftjoin('purchase_order_items','purchase_order_items.item_code','items.code')
        ->join('purchase_orders', 'purchase_orders.id', 'purchase_order_items.po_id')
        ->join('vendors', 'purchase_orders.vendor_id', 'vendors.id')
        ->select('items.code as code', 'category.name as c_name', 'items.description', 'items.uom as uom', 'items.rate', 'items.specification', DB::raw('sum(purchase_order_items.qty) as qty'), DB::raw('max(purchase_order_items.rate) as max_rate'), 'purchase_orders.id as p_id', DB::raw('min(purchase_order_items.rate) as min_rate'), 'vendors.vendor_name')
        // ->select('category.name as c_name','items.code','items.description','items.specification','items.uom as uom','units.name as u_name','purchase_order_items.qty','purchase_order_items.rate', DB::raw('sum(purchase_order_items.qty) as po_qty'))
        ->where('purchase_orders.status', 'Approved')->groupBy('purchase_order_items.item_code')
        ->whereBetween('purchase_orders.created_at',[$this->from_date, $this->to_date])
        ->get();
        // dd(DB::getQueryLog());
        // dd($response);

$data=[];
        foreach($response as $key=>$value){
            
            $data[$key]['Itme Catagory']=$value['c_name'];             
            $data[$key]['Item Code']=$value['code'];
            $data[$key]['Description']=$value['description'];
            $data[$key]['Specification']=$value['specification'];
            $data[$key]['UOM']=$value['uom'];
            $data[$key]['Rate']=$value['rate'];            
            $data[$key]['Qty']=$value['qty'];            
            $data[$key]['Max Rate']=$value['max_rate'];    
            $data[$key]['Vendor Name1']=getVendorByRate($value['max_rate'])->vendor_name;         
            $data[$key]['Min Rate']=$value['min_rate']; 
            $data[$key]['Vendor Name2']=getVendorByRate($value['min_rate'])->vendor_name;           
            // $data[$key]['PO id']=$value['po_id'];            
            // $data[$key]['Vendor Name']=$value['vendor_name'];            
            // $data[$key]['PO Qty']=$value['po_qty'];            
        }
     
        return collect($data);
    }
    
    
        if($this->excel_name == 5){
            $response = PurchaseOrder::join('purchase_order_items','purchase_order_items.po_id','purchase_orders.id')
            ->join('vendors','vendors.id','purchase_orders.vendor_id')
            ->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');
            })
            ->join('items', 'items.code', 'purchase_order_items.item_code')
            ->leftJoin('irns','irns.id','irn_items.irn_id')
            ->select('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','purchase_order_items.item_code','purchase_order_items.specification','purchase_order_items.make','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',
                'irn_items.qty as irns_qty',
                \DB::raw('(irn_items.rate*irn_items.qty) as mrn_basic_amount'),
                \DB::raw('(irn_items.rate*irn_items.qty*items.gst/100) as mrn_gst_amount'),
                \DB::raw('((irn_items.rate*irn_items.qty*items.gst/100)+(irn_items.rate*irn_items.qty)) as mrn_total_amount'),
                'irn_items.rate as irns_rate',
               \DB::raw('(irn_items.rate*irn_items.qty) as invoice_amount'),
               'irns.status as irns_status')
    
            ->whereBetween('purchase_orders.created_at',[$this->from_date, $this->to_date])
            ->get();        
            $data=[];
             foreach($response as $key=>$value){
                
                $data[$key]['PO Number']=$value['po_number'];
                $data[$key]['PO Creation Date']=$value['created_at'];
                $data[$key]['PO Date']=$value['created_at'];
                $data[$key]['PO Approved By']=getModelById('User',PoStatusCreaterName($value['po_id'],'Approved')->user_id)->name;
                $data[$key]['Vendor Code']=$value['vendor_code']; 
                $data[$key]['Vendor name']=$value['vendor_name']; 
                $data[$key]['Basic Amount']=$value['basic_amount']; 
                $data[$key]['Tax Amount']=$value['gst_amount'];
                $data[$key]['PO Total Amount']=$value['total_amount'];
                $data[$key]['MRN Basic Amount']=$value['mrn_basic_amount'];
                $data[$key]['MRN Frieght Charges']=$value['freight_charge'];
                $data[$key]['MRN Tax Amount']=$value['mrn_gst_amount'];
                $data[$key]['MRN Total Amount']=$value['mrn_total_amount'];
                
                $mrn_status = getMRNStatus($value['po_id']);
                if($mrn_status ==1) {
                    $mrn_status = 'Done';
                } elseif($mrn_status == 2) {
                    $mrn_status = 'Partially Done';
                } elseif($mrn_status == 3) {
                    $mrn_status = 'Not Done';
                }
                $data[$key]['MRN Status for PO'] = $mrn_status;
            }
            return collect($data);
            
        }
        
        
        
        if($this->excel_name == 6){
            $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')
            ->leftjoin("purchase_order_items",function($join){
                $join->on("purchase_order_items.item_code","=","purchase_request_items.item_code")
                    ->on("purchase_order_items.pr_id","=","purchase_request_items.pr_id");
            })
            ->leftjoin('purchase_orders','purchase_orders.id','purchase_order_items.po_id')
            ->leftjoin('vendors','vendors.id','purchase_orders.vendor_id')
            ->leftJoin('irn_items', function($join){
                $join->on('irn_items.item_code', '=', 'purchase_order_items.item_code')
                ->on('irn_items.po_id', '=', 'purchase_order_items.po_id');
            })
            ->leftjoin('irns','irns.id','irn_items.irn_id')
            
            ->select('purchase_requests.pr_number',
                'purchase_requests.created_at',
                'users.name',
                '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',
                'department.name as dep_name',
                'business_units.name as bus_unit',
                'customers.name as cust_name',
                'circles.name as cir_name',
                'purchase_requests.delivery_location',
                'purchase_order_items.created_at as po_creation_date',
                'purchase_orders.po_number',
                'purchase_requests.site',
                'purchase_order_items.created_at as po_recom_date',
                'users.name as Appr_by',
                'purchase_orders.status as po_status',
                'purchase_orders.circle as po_circle',
                'vendors.vendor_code',
                'vendors.vendor_name',
                'vendors.address as ven_address',
                'vendors.gst as ven_gstin',
                'category.name as cat_name',
                'purchase_request_items.item_code',
                'items.description as item_dis',
                'purchase_request_items.specification',
                'items.make',
                // 'units.name as unit_name',
                'purchase_request_items.qty as pr_qty',
                'purchase_order_items.qty as po_qty',
                'purchase_order_items.rqty as po_rqty',
                'purchase_order_items.item_code as po_item_code',
                'purchase_request_items.rate as pr_unitp',
                \DB::raw('(purchase_request_items.rate*purchase_request_items.qty) as basic_amount'),
                'items.gst as item_gst',
                \DB::raw('((purchase_request_items.rate*purchase_request_items.qty)*items.gst/100) as tax_amount'),
                \DB::raw('((purchase_request_items.rate*purchase_request_items.qty)+((purchase_request_items.rate*purchase_request_items.qty)*items.gst/100)) as total_amount'),
                'purchase_orders.Bill_to_address',
                'purchase_orders.delivery_address',
                'purchase_orders.warranty_terms',
                'purchase_orders.delivery_terms',
                'purchase_orders.payment_terms',
                'purchase_requests.id as pr_id',
                'purchase_orders.id as po_id',
                'circles.gst_number',
                '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')
            ->whereBetween(DB::raw('date(purchase_requests.created_at)'),[$this->from_date, $this->to_date])
            ->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 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]['PR Qty']=$value['pr_qty']; 
                $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]['PO Number']=$value['po_number'];
                $data[$key]['PO Date']=$value['po_creation_date']; 
                $data[$key]['PO Status']=$value['po_status']; 
                $data[$key]['Vendor Code']=$value['vendor_code']; 
                $data[$key]['Vendor name']=$value['vendor_name']; 
                $data[$key]['PO Circle']=$value['po_circle']; 
                $data[$key]['Delivery Address']=$value['delivery_address'];
                $data[$key]['PO Qty']=$value['po_qty'];
                $data[$key]['Item Unit Price']=$value['pr_unitp'];
                $data[$key]['Item Basic Amount']=$value['basic_amount']; 
                $data[$key]['Tax %age']=$value['item_gst'];
                $data[$key]['Tax Amount']=$value['tax_amount'];
                $data[$key]['Total Amount after Tax']=$value['total_amount'];
                $data[$key]['MRN Qty']=$value['irns_qty'];
                
                $mrn_status = getMRNStatus($value['po_id']);
                if($mrn_status ==1) {
                    $mrn_status = 'Done';
                } elseif($mrn_status == 2) {
                    $mrn_status = 'Partially Done';
                } elseif($mrn_status == 3) {
                    $mrn_status = 'Not Done';
                }
                
                if($value['po_qty'] == $value['po_rqty']) {
                    $po_status = 'Not Closed';
                } elseif($value['po_rqty'] == 0) {
                    $po_status = 'Closed';
                } else {
                    $po_status = 'Partially Closed';
                }
                
                
                
                $data[$key]['Line MRN Status'] = $mrn_status;
                $data[$key]['PO Status1'] = $po_status;
                $data[$key]['Pending Qty for MRN'] = $value['po_rqty'];
                
            }
            return collect($data);
        }
    }

public function headings(): array
     { 
        if($this->excel_name == 1){
        return [
            'PR Number',
            'PR Date',
            'Preparer Name',
            //'PR Description',
            'PR Amendment Number',
            'PR Amendment Date',
            'PR Status',

'Item Category',
            'PR ItemCode',
            'Item Description',
            'Specification',
            'Make',
            'UoM',

'Recommended By',
            'Recommendation Date',
            'Approved By',
            'Approval Date',
            'Department',
            'Business Unit',
            'Customer',
            'Site',
            // 'Project',
            'Circle',
            'Delivery Location',
            'PO Creation Date',
            'PO Number',
            'PO Recommendated By',
            'PO Recommendation Date',
            'PO Approved By',
            'PO Approval Date',
            'PO Status',
            'PO ItemCode',
            'Vendor Code',
            'Vendor Name',
            'Vendor Address',
            'Vendor GSTIN Number',            
            'PR Qty',
            'PO Qty',
            'Unit Price',
             'Basic Amount',
             'Tax %age',
             'Tax Amount',  
             'Total Amount',    
             // 'Billing', 'Circle',  
             'Bill to Address', 
             'Delivery Address',  
             'GSTIN Number', 
             'Warranty Term',   
             'Delivery Term',   
             'Payment Term',    
             // 'Reciever Name',   
             'Reciept Date',    
             'Invoice Number',  
             'Invoice Date',    
             'Invoice Qty', 
             'Invoice Unit Price', 
             // 'Invoice Tax %age',    
             'Invoice Amount',  
             'Payment Status',  
             // 'Payment Due Date',

];
    }
    if($this->excel_name == 2){
        return [
            'Preparer Name',   
            'PR Number',   
            'PR Date', 
            // 'PR Description',  
            'PR Amendment Number', 
            'PR Amendment Date',   
            'PR Status',   
            'Recommended By',  
            'Recommendation Date', 
            'Approved By', 
            'Approval Date',   
            'Department',  
            'Business Unit',   
            'Customer',    
            'Project Name', 
            'Site',    
            'Circle',  
            'Delivery Location',   
            'Item Category' ,  
            'Item Code' ,  
            'Item Description' ,   
            'Specification',   
            'UoM', 
            'PR Qty',
        ];
    }

if($this->excel_name == 3){
        return [
            'PR Number',   
            'PR Date', 
            'Preparer Name',   
            'Department',  
            'Business Unit',   
            'Customer',    
            // 'Project', 
            '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', 
            //'Reciever Name',  
            'Irn No',
            'Reciept Date',    
            'Invoice Number',  
            'Invoice Date',    
            'Invoice Qty', 
            'Invoice Unit Price',  
            //'Invoice Tax %age ',   
            'Invoice Amount',  
            'Payment Status',  
            //'Payment Due Date',

];
    }
    if($this->excel_name == 4){
        return [
            'Item Category',
            'Item Code',
            'Item Description',
            'Specification',
            'Uom',
            'Rate',
            'Total PO Qty',            
            'Maximum Rate',  
            'Vendor with Max. Rate',          
            'Minimum Rate',
            'Vendor with Min. Rate',
            // 'Qty',
            // 'Unit Price',
            // //'Basic Amount',
            // 'Tax %age',
           // 'Tax Amount',
            // 'Total Amount',
            // 'Department',
            //'Business Unit',
            // 'Customer',
            // 'Project',
             //'Circle',
        ];
    }
    
    
    if($this->excel_name == 5){
        return [
            'PO Number',   
            'PO Creation Date',    
            'PO Date',  
            'PO Approved By',  
            'Vendor Code', 
            'Vendor Name', 
            'PO Basic Amount',    
            'PO Tax Amount', 
            'PO Total Amount',   
            'MRN Basic Amount',
            'MRN Frieght Charges',
            'MRN Tax Amount',
            'MRN Total Amount',
            'MRN Status for PO',
        ];
    }
    
    
    
    if($this->excel_name == 6){
        return [
            'PR Number',
            'PR Date',
            'Preparer Name',
            'PR Status',
            'Item Category',
            'PR ItemCode',
            'Item Description',
            'Specification',
            'Make',
            'UoM',
            'PR Qty',
            'Department',
            'Business Unit',
            'Customer',
            'Site',
            'Circle',
            'Delivery Location',
            'PO Number',
            'PO Date',
            'PO Status',
            'Vendor Code',
            'Vendor Name',
            'PO Circle',
            'Delivery Address',
            'PO Qty',
            'Item Unit Price',
            'Item Basic Amount',
            'Tax %age',
            'Tax Amount',
            'Total Amount After Tax',
            'MRN Qty',
            'Line MRN Status',
            'PO Status',
            'Pending Qty for MRN',
        ];
    }
    
    
    
}
}