000

<?php

namespace App\Exports;

use App\Item;
use DB;
use App\PurchaseRequest;
use App\PurchaseOrder;
use App\PurchaseOrderItem;
use App\Irn;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithCustomStartCell;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;

class ReportExport implements FromCollection,WithHeadings,WithEvents,WithCustomStartCell,ShouldAutoSize
{
    /**
    * @return \Illuminate\Support\Collection
    */
    protected $from_date;
    protected $to_date;
    protected $excel_name;
    protected $date_type;

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

public function collection()
    {
        set_time_limit(1200);
        //dd($this->to_date);
        if($this->excel_name == 1){

//\DB::enableQueryLog();
            // dd('stop');
           $query = PurchaseRequest::query();
           $query->leftjoin('users','users.id','purchase_requests.prepared_by')
            ->leftjoin('purchase_request_items','purchase_request_items.pr_id','purchase_requests.id')
            // ->leftjoin('purchase_request_logs','purchase_request_logs.pr_id','purchase_requests.id')
            ->leftjoin('verticals','verticals.id','purchase_requests.department')
            
            ->leftjoin('business_units','business_units.id','purchase_requests.business_unit')
            ->leftjoin('customers','customers.id','purchase_requests.customer')
            //->leftjoin('projects','projects.id','purchase_requests.project')
            ->leftjoin('circles','circles.id','purchase_requests.circle')
            ->leftjoin('items','items.code','purchase_request_items.item_code')
            ->leftjoin('category','category.id','items.category')
            ->leftjoin('units','units.id','items.uom')
            //->leftjoin('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');

if($this->date_type == 'po_date') {
                $query->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$this->from_date, $this->to_date]);           
            } elseif($this->date_type == 'pr_date') {
                $query->whereBetween(DB::raw('date(purchase_requests.pr_date)'),[$this->from_date, $this->to_date]);           
            } elseif($this->date_type == 'mrn_date'){
                $query->whereBetween(DB::raw('date(irns.created_date)'),[$this->from_date, $this->to_date]);           
            } else {
                $query->whereBetween(DB::raw('date(purchase_requests.pr_date)'),[$this->from_date, $this->to_date]);           
            }

$response = $query->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',
                'verticals.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_order_items.rate as po_unitp',
                '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.irn_no',
                \DB::raw('date(irns.created_at) as mrn_date'),    
                'purchase_order_items.qty as mrn_po_qty',       
                'purchase_order_items.rqty as mrn_po_rqty',    
                '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')->get();                                    
            
            //dd(\DB::getQueryLog());
            // dd($response);
           
            $data=[];
             foreach($response as $key=>$value){
                $data[$key]['S. No.'] = $key + 1;
                $data[$key]['PR Number']=$value['pr_number'] ?? NULL; 
                $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']=getPRCurrentStatusForReport($value['pr_id'], $value['item_code']);

$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]['Current Status']= getPOCurrentStatusReport($value['po_id'], $value['po_item_code']); 
                $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]['Invoice Qty']=$value['irns_qty'];
                $data[$key]['Unit Price']=$value['po_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]['GRN No']=$value['irn_no'];
                $data[$key]['GRN Date']=$value['mrn_date'];               
                
                $data[$key]['Invoice Number']=$value['invoice_no'];
                $data[$key]['Invoice Date']=$value['invoice_date'];
                $data[$key]['Invoice_Qty1']=$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'];

if(($value['mrn_po_qty'] == $value['mrn_po_rqty']) && $value['mrn_po_qty'] != '') {
                    $status = 'Done';
                } elseif($value['mrn_po_rqty'] > 0) {
                    $status = 'Partially Done';
                } elseif($value['mrn_po_rqty'] == 0) {
                    $status = 'Not Done';
                }

$data[$key]['GRN Status']=$status;
                $item_code = $value['po_item_code'];
                //$low_rate = DB::table('purchase_order_items')->where('item_code', $item_code)->min(DB::raw('cast(rate AS DECIMAL(10,2))'));

$low_rate = DB::table('purchase_order_items')->where('item_code', $item_code)->whereBetween('purchase_order_items.created_at', [$this->from_date, $this->to_date])->orderBy(DB::raw('cast(rate AS DECIMAL(10,2))'), 'asc')->first();
                // min(DB::raw('cast(rate AS DECIMAL(10,2))'));                            
                if(!is_null($low_rate)) {                    
                    $low_po_data = getModelById('PurchaseOrder', $low_rate->po_id);
                    
                    $data[$key]['Lowest Unit Price'] = $low_rate->rate;
                    $data[$key]['Lowest Unit Price Date'] = $low_po_data->po_date;
                    $data[$key]['Lowest Unit Price PO No.'] = $low_po_data->po_number;
                } else {
                    $data[$key]['Lowest Unit Price'] = '';
                    $data[$key]['Lowest Unit Price Date'] = '';
                    $data[$key]['Lowest Unit Price PO No.'] = '';
                }

$high_rate = DB::table('purchase_order_items')->where('item_code', $item_code)->orderBy(DB::raw('cast(rate AS DECIMAL(10,2))'), 'desc')->first();   
                if(!is_null($high_rate)) {                    
                    $high_po_data = getModelById('PurchaseOrder', $high_rate->po_id);
                    
                    $data[$key]['Highest Unit Price'] = $high_rate->rate;
                    $data[$key]['Highest Unit Price Date'] = $high_po_data->po_date;
                    $data[$key]['Highest Unit Price PO No.'] = $high_po_data->po_number;
                } else {
                    $data[$key]['Highest Unit Price'] = '';
                    $data[$key]['Highest Unit Price Date'] = '';
                    $data[$key]['Highest Unit Price PO No.'] = '';
                }                
                          
            }
            //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('verticals','verticals.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.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','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_request_items.rqty','purchase_request_items.rate','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]['S. No.'] = $key + 1;
                $data[$key]['PR Number']=$value['pr_number'] ?? NULL; 
                $data[$key]['PR Date']=$value['created_at'] ?? NULL; 
                $data[$key]['Preparer Name']=$value['name'] ?? NULL;  
                // $data[$key]['PR Description']=$value['pr_desc']; 
                $data[$key]['PR Status']=$value['status'] ?? NULL; 
                $data[$key]['PR Current Status']=getPRCurrentStatusForReport($value['pr_id'], $value['item_code']) ?? NULL; 
                $data[$key]['Item Category']=$value['cat_name'] ?? NULL; 
                $data[$key]['Item Code']=$value['item_code'] ?? NULL; 
                $data[$key]['Item Description']=$value['item_dis'] ?? NULL; 
                $data[$key]['Specification']=$value['specification'] ?? NULL; 
                $data[$key]['Make']=$value['make'] ?? NULL; 
                $data[$key]['PR Qty']=$value['qty'] ?? NULL; 
                $data[$key]['PR Pending Qty']=$value['rqty'] ?? NULL;
                $data[$key]['PR Rate']=$value['rate'] ?? NULL;
                $data[$key]['UoM']=$value['unit_name'] ?? NULL; 
                $data[$key]['Recommended By']=$value['re_by'] ?? NULL; 
                $data[$key]['Recommendation Date']=$value['re_date'] ?? NULL;
                $data[$key]['Approved_by']=getModelById('User',PrStatusCreaterName($value['pr_id'],'Approved')->user_id)->name ?? NULL;
                $data[$key]['Approval Date']=PrStatusCreaterName($value['pr_id'],'Approved')->created_at ?? NULL;
                $data[$key]['Department']=$value['dep_name'] ?? NULL; 
                $data[$key]['Business Unit']=$value['bus_unit'] ?? NULL; 
                $data[$key]['Customer']=$value['cust_name'] ?? NULL;
                $data[$key]['Site']=$value['site'] ?? NULL; 
                $data[$key]['Circle']=$value['cir_name'] ?? NULL; 
                $data[$key]['Delivery Location']=$value['delivery_location'] ?? NULL; 
                $data[$key]['PR Amendment Number']=$value['amendment_number'] ?? NULL; 
                $data[$key]['PR Amendment Date']=($value['amendment_number'] > 0)?$value['amendment_date']:"" ?? NULL; 
            }
            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('verticals','verticals.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')
          
            ->select('purchase_requests.id as pr_id', 'purchase_requests.pr_number','purchase_requests.status as pr_status','purchase_requests.created_at','users.name as preparer_name','verticals.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','purchase_orders.current_status','purchase_orders.po_amount','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.rqty','purchase_order_items.rate',\DB::raw('sum(purchase_order_items.rate*purchase_order_items.qty) as basic_amount'),\DB::raw('sum(purchase_order_items.rate*purchase_order_items.qty*items.gst/100) as gst_amount'),\DB::raw('(sum(purchase_order_items.rate*purchase_order_items.qty*items.gst/100)+sum(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', 'purchase_orders.po_created_by')          
            ->whereBetween('purchase_orders.created_at',[$this->from_date, $this->to_date])->groupBy('purchase_orders.po_number')
            //->groupBy('purchase_orders.id')
            ->get();        
            $data=[];
             foreach($response as $key=>$value){
                
                $data[$key]['S. No.'] = $key + 1;
                $data[$key]['PR Number']=$value['pr_number']; 
                $data[$key]['PR Date']=$value['created_at'];
                $data[$key]['PR Status']=$value['pr_status'];
                $data[$key]['PR Current Status']=getPRCurrentStatus2($value['pr_id']);
                $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]['Circle']=$value['circle'];
                $data[$key]['ATS GSTIN Number']=$value['gst_number'];
                $data[$key]['PO Creater']= getModelById('User', $value['po_created_by'])->name;
                $data[$key]['PO Number']=$value['po_number'];
                $data[$key]['PO Creation Date']=$value['created_at'];                
                $data[$key]['PO Approval Status']=$value['status']; 
                 if($value['qty'] == $value['rqty']) {
                    $status = 'Completed';
                } elseif($value['rqty'] > 0) {
                    $status = 'Partially Completed';
                } else {
                    $status = 'Processing';
                }

$data[$key]['PO Current Status']= $status;
                $data[$key]['Vendor Code']=$value['vendor_code']; 
                $data[$key]['Vendor name']=$value['vendor_name']; 
             
                $data[$key]['Vendor GSTIN Number']=$value['gstin_no'];              
                
                $data[$key]['PO Basic Amount']=$value['basic_amount']; 
                $data[$key]['PO Tax Amount']=$value['gst_amount']; 
                $data[$key]['PO 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]['Warranty Term']=$value['warranty_terms'];
                $data[$key]['Delivery Term']=$value['delivery_terms'];
                $data[$key]['Payment Term']=$value['payment_terms'];               
            
            }            
            return collect($data);          
        }
        
        if($this->excel_name == 4){
        
            $itemcodes = Item::join('category','category.id','items.category')
            ->join('units','units.id','items.uom')
            ->leftjoin('purchase_order_items','purchase_order_items.item_code','items.code')
            ->join('purchase_orders', 'purchase_orders.id', 'purchase_order_items.po_id')
            ->select('category.name as c_name', 'items.code as code', 'items.description', 'items.specification','purchase_order_items.make', 'units.name as uom')->where('purchase_orders.status', 'Approved')->groupBy('purchase_order_items.item_code')->whereBetween('purchase_orders.created_at',[$this->from_date, $this->to_date])->get();
           // dd($itemcodes);
           $data=[];
         
           foreach($itemcodes as $key=>$itemcode){
            
                $rates =  DB::table('purchase_order_items')->select(DB::raw('MIN(rate) as min_rate, MAX(rate) as max_rate'))->where('item_code', '=', $itemcode->code)->whereBetween('created_at', [$this->from_date, $this->to_date])->first();
          
                    $min_rate_min_qty =  DB::table('purchase_order_items')->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->join('circles','circles.id','purchase_orders.circle')->join('business_units','business_units.id','purchase_orders.business_unit')->join('vendors','vendors.id','purchase_orders.vendor_id')->leftjoin('purchase_requests','purchase_requests.pr_number','purchase_orders.reference_no')->leftjoin('customers','customers.id','purchase_requests.customer')->leftjoin('verticals','verticals.id','purchase_requests.department')->select('purchase_order_items.rate as min_rate','purchase_orders.po_number as po_number',DB::raw('DATE_FORMAT(purchase_orders.po_date,"%d-%m-%Y %h:%i %p") as po_date'),'business_units.short_name as business_name','circles.name as circle','vendors.vendor_name as vendor','verticals.name as department','customers.name as customer','purchase_order_items.qty as min_qty')->where([['purchase_order_items.item_code', '=', $itemcode->code],['purchase_order_items.rate', '=', $rates->min_rate]])->whereBetween('purchase_order_items.created_at', [$this->from_date, $this->to_date])->orderBy('qty')->first();
                
                    $min_rate_max_qty = DB::table('purchase_order_items')->join('purchase_orders', 'purchase_orders.id', 'purchase_order_items.po_id')->select('purchase_orders.po_number as po_number', 'purchase_order_items.qty as max_qty')->where([['purchase_order_items.item_code', '=', $itemcode->code],['purchase_order_items.rate', '=', $rates->min_rate]])->whereBetween('purchase_order_items.created_at', [$this->from_date, $this->to_date])->orderBy('qty', 'desc')->first();

$max_rate_min_qty = DB::table('purchase_order_items')->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->join('circles','circles.id','purchase_orders.circle')->join('business_units','business_units.id','purchase_orders.business_unit')->join('vendors','vendors.id','purchase_orders.vendor_id')->leftjoin('purchase_requests','purchase_requests.pr_number','purchase_orders.reference_no')->leftjoin('customers','customers.id','purchase_requests.customer')->leftjoin('verticals','verticals.id','purchase_requests.department')->select('purchase_order_items.rate as max_rate','purchase_orders.po_number as po_number',DB::raw('DATE_FORMAT(purchase_orders.po_date,"%d-%m-%Y %h:%i %p") as po_date'),'business_units.short_name as business_name','circles.name as circle','vendors.vendor_name as vendor','verticals.name as department','customers.name as customer','purchase_order_items.qty as min_qty')->where([['purchase_order_items.item_code', '=', $itemcode->code],['purchase_order_items.rate', '=', $rates->max_rate]])->whereBetween('purchase_order_items.created_at', [$this->from_date, $this->to_date])->orderBy('qty')->first();

$max_rate_max_qty =  DB::table('purchase_order_items')->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->join('circles','circles.id','purchase_orders.circle')->join('business_units','business_units.id','purchase_orders.business_unit')->join('vendors','vendors.id','purchase_orders.vendor_id')->leftjoin('purchase_requests','purchase_requests.pr_number','purchase_orders.reference_no')->leftjoin('customers','customers.id','purchase_requests.customer')->leftjoin('verticals','verticals.id','purchase_requests.department')->select('purchase_order_items.rate as max_rate','purchase_orders.po_number as po_number',DB::raw('DATE_FORMAT(purchase_orders.po_date,"%d-%m-%Y %h:%i %p") as po_date'),'business_units.short_name as business_name','circles.name as circle','vendors.vendor_name as vendor','verticals.name as department','customers.name as customer','purchase_order_items.qty as max_qty')->where([['purchase_order_items.item_code', '=', $itemcode->code],['purchase_order_items.rate', '=', $rates->max_rate]])->whereBetween('purchase_order_items.created_at', [$this->from_date, $this->to_date])->orderBy('qty', 'desc')->first();
                    
                    $data[$key]['S. No.'] = $key + 1;
                    $data[$key]['date range'] = $this->from_date.' -- '.$this->to_date;
                    $data[$key]['Itme Catagory']=$itemcode['c_name'];             
                    $data[$key]['PR ItemCode']=$itemcode['code'];
                    $data[$key]['Description']=$itemcode['description'];
                    $data[$key]['Specification']=$itemcode['specification'];
                    $data[$key]['Make']=$itemcode['make'];
                    $data[$key]['UOM']=$itemcode['uom'];
                    $data[$key]['Lowest Unit Rate']=$rates->min_rate;
                    $data[$key]['Min Qty at Lowest Rate']=$min_rate_min_qty->min_qty;
                    $data[$key]['PO No Against Lowest Rate & Min Qty']=$min_rate_min_qty->po_number;
                    $data[$key]['Max Qty at Lowest Rate']=$min_rate_max_qty->max_qty;
                    $data[$key]['PO No Against Lowest Rate & Max Qty']=$min_rate_max_qty->po_number;
                    $data[$key]['PO Date-1']=$min_rate_min_qty->po_date;
                    $data[$key]['Department-1']=$min_rate_min_qty->department;
                    $data[$key]['Business Unit-1']=$min_rate_min_qty->business_name;
                    $data[$key]['Vendor-1']=$min_rate_min_qty->vendor;
                    $data[$key]['Customer-1']=$min_rate_min_qty->customer;
                    $data[$key]['Circle-1']=$min_rate_min_qty->circle;
                    $data[$key]['Highest Unit Rate']=$rates->max_rate;
                    $data[$key]['Min Qty at Highest Rate']=$max_rate_min_qty->min_qty;
                    $data[$key]['PO No Against Highest Rate & Min Qty']=$max_rate_min_qty->po_number;
                    $data[$key]['Max Qty at Highest Rate']=$max_rate_max_qty->max_qty;
                    $data[$key]['PO No Against Highest Rate & Max Qty']=$max_rate_max_qty->po_number;
                    $data[$key]['PO Date-2']=$max_rate_min_qty->po_date;
                    $data[$key]['Department-2']=$max_rate_min_qty->department;
                    $data[$key]['Business Unit-2']=$max_rate_min_qty->business_name;
                    $data[$key]['Vendor-2']=$max_rate_min_qty->vendor;
                    $data[$key]['Customer-2']=$max_rate_min_qty->customer;
                    $data[$key]['Circle-2']=$max_rate_min_qty->circle;
                            
            }
         
            return collect($data);
        }

if($this->excel_name == 5){

$po_data = PurchaseOrder::whereBetween('purchase_orders.created_at', [$this->from_date, $this->to_date])->get();

$data=[];
            $key = 0;
            foreach ($po_data as $key1 => $value) {
                $po_item = PurchaseOrderItem::select('purchase_order_items.po_id', 'purchase_order_items.item_code','purchase_order_items.qty','purchase_order_items.rate',\DB::raw('(sum(purchase_order_items.rate*purchase_order_items.qty)) as basic_amount'),\DB::raw('(sum(purchase_order_items.rate*purchase_order_items.qty*items.gst/100)) as gst_amount'),\DB::raw('((sum(purchase_order_items.rate*purchase_order_items.qty*items.gst/100))+sum((purchase_order_items.rate*purchase_order_items.qty))) as total_amount'),'items.gst')->join('items', 'items.code', 'purchase_order_items.item_code')->where('po_id', $value->id)->groupBy('po_id')->first();     
                                
                // $data[$key]['PO Number'] = '';           
                // $data[$key]['PO Date'] = '';
                // $data[$key]['PO Approved By']= '';
                // $data[$key]['Approval Status'] = ''; 
                // $data[$key]['Vendor Code'] = ''; 
                // $data[$key]['Vendor name'] = ''; 
                // $data[$key]['Basic Amount'] = ''; 
                // $data[$key]['Tax Amount']= '';              
                // $data[$key]['PO Total Amount']= '';
                if(!is_null($po_item)) {
                    $po_item = $po_item->toArray();
                    $data[$key]['S. No.'] = $key + 1;
                    $data[$key]['PO Number'] = $value->po_number;           
                    $data[$key]['PO Date'] = $value->created_at;
                    $data[$key]['PO Approved By']=getModelById('User',PoStatusCreaterName($po_item['po_id'],'Approved')->user_id)->name;
                    $data[$key]['Approval Status'] = $value->status; 
                    $data[$key]['Current Status'] = getPOCurrentStatusPOReport($value->id);
                    $vendor = getModelById('Vendor', $value->vendor_id);
                    $data[$key]['Vendor Code'] = $vendor->vendor_code; 
                    $data[$key]['Vendor name'] = $vendor->vendor_name; 
                    $data[$key]['Basic Amount'] = $po_item['basic_amount']; 
                    $data[$key]['Tax Amount']=$po_item['gst_amount'];              
                    $data[$key]['PO Total Amount']=$po_item['total_amount'];
                }
                               
               
                 $irn_data = Irn::select('irn_items.qty as irns_qty',\DB::raw('(sum(irn_items.rate * irn_items.qty)) as mrn_basic_amount'),\DB::raw('(sum(irn_items.rate * irn_items.qty * items.gst / 100)) as mrn_gst_amount'),\DB::raw('((sum(irn_items.rate * irn_items.qty * items.gst / 100)) + sum((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','irns.freight_charge','irns.irn_no','irns.invoice_no',          'irns.invoice_date','irns.receive_date','irns.warehouse as unit','irns.business_type','irns.status as quality_check_status','irns.receiver_name','irns.gate_entry_number','irns.gate_entry_date')
                     ->join('irn_items', 'irn_items.irn_id', 'irns.id')
                     ->join('items', 'items.code', 'irn_items.item_code')
                     ->where('po_id', $value->id)
                     ->groupBy('irn_items.po_id')->first();
                     
                // $data[$key]['MRN Basic Amount']= '';
                // $data[$key]['MRN Frieght Charges']= '';
                // $data[$key]['MRN Tax Amount']= '';
                // $data[$key]['MRN Total Amount']= '';
                // $data[$key]['MRN Status for PO'] = '';
                if(!is_null($irn_data)) {
                    $irn_data = $irn_data->toArray();               
                    $data[$key]['GRN Number'] = $irn_data['irn_no'];
                    $data[$key]['Invoice No'] = $irn_data['invoice_no'];
                    $data[$key]['Invoice Date'] = $irn_data['invoice_date'];
                    $data[$key]['GRN Receiver Name'] = $irn_data['receiver_name'];
                    $data[$key]['GRN Receive Date'] = $irn_data['receive_date'];
                    $data[$key]['GRN Unit'] = $irn_data['unit'];
                    $data[$key]['GRN Business Type'] = $irn_data['business_type'];
                     $data[$key]['GRN Quality Check Status'] = $irn_data['quality_check_status'];
                    $data[$key]['GRN Gate Entry No'] = $irn_data['gate_entry_number'];
                    $data[$key]['GRN Gate Entry Date'] = $irn_data['gate_entry_date'];
                    $data[$key]['GRN Basic Amount']=$irn_data['mrn_basic_amount'];
                    $data[$key]['GRN Frieght Charges']=$irn_data['freight_charge'];
                    $data[$key]['GRN Tax Amount']=$irn_data['mrn_gst_amount'];
                    $data[$key]['GRN Total Amount']=$irn_data['mrn_total_amount'] + $irn_data['freight_charge'];

$mrn_status = getMRNStatus($value->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]['GRN Status for PO'] = $mrn_status;
                }

$key++;
            }

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('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')
            ->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',
                'verticals.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.irn_no','irns.invoice_no','irns.invoice_date','irns.receive_date','irns.warehouse as unit','irns.business_type','irns.status as quality_check_status','irns.receiver_name','irns.gate_entry_number','irns.gate_entry_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]['S. No.'] = $key + 1;
                $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]['PR Current Status']=getPRCurrentStatusForReport($value['pr_id'], $value['item_code']); 
                $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]['Current Status']= getPOCurrentStatusReport($value['po_id'], $value['item_code']);
                $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['cir_name']; 
                $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]['GRN Number'] = $value['irn_no'];
                $data[$key]['Invoice No'] = $value['invoice_no'];
                $data[$key]['Invoice Date'] = $value['invoice_date'];
                $data[$key]['GRN Receiver Name'] = $value['receiver_name'];
                $data[$key]['GRN Receive Date'] = $value['receive_date'];
                $data[$key]['GRN Unit'] = $value['unit'];
                $data[$key]['GRN Business Type'] = $value['business_type'];
                $data[$key]['GRN Quality Check Status'] = $value['quality_check_status'];
                $data[$key]['GRN Gate Entry No'] = $value['gate_entry_number'];
                $data[$key]['GRN Gate Entry Date'] = $value['gate_entry_date'];
                $data[$key]['GRN Qty']=$value['irns_qty'];
                
                $data[$key]['Line GRN Status'] = getMRNStatusReport($value['po_id'], $value['item_code']);
                // $data[$key]['PO Status1'] = $po_status;
                $data[$key]['Pending Qty for GRN'] = $value['po_qty'] - $value['po_rqty'];
                
            }
            return collect($data);
        }

}

public function headings(): array
     { 
        if($this->excel_name == 1){
        return [
            'S. No.',
            'PR Number',
            'PR Date',
            'Preparer Name',
            //'PR Description',
            'PR Amendment Number',
            'PR Amendment Date',
            'Current Status',
            '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 Current Status',
            'PO ItemCode',
            'Vendor Code',
            'Vendor Name',
            'Vendor Address',
            'Vendor GSTIN Number',            
            'PR Qty',
            'PO Qty',
            'GRN 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',    
             'GRN Number',
             'GRN Date',
             'Invoice Number',  
             'Invoice Date',    
             'Invoice Qty', 
             'Invoice Unit Price', 
             // 'Invoice Tax %age',    
             'Invoice Amount',  
             'Payment Status', 
             'GRN Status', 
             'Lowest Unit Price',
             'Lowest Unit Price Date',
             'Lowest Unit Price PO No',
             'Highest Unit Price',
             'Highest Unit Price Date',
             'Highest Unit Price PO No',
             // 'Payment Due Date',

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

if($this->excel_name == 3){
        return [
            'S. No.',
            'PR Number',   
            'PR Date', 
            'PR Status', 
            'PR Current Status', 
            'Preparer Name',   
            'Department',  
            'Business Unit',   
            'Customer',   
            'Circle', 
            'ATS GSTIN Number',
            'PO Creater',
   
            'PO Number',   
            // 'PO Recommendated By', 
            'PO Date',  
            'PO Approval Status',      
            'PO Current Status',
            'Vendor Code', 
            'Vendor Name', 
            // 'Vendor Address',
            'Vendor GSTIN Number', 
            'PO Basic Amount',
            'PO Tax Amount',
            'PO Total Amount',         
            'Billing Circle',  
            'Bill to Address', 
            'Delivery Address',    
            //'Circle', 
            // 'GSTIN Number', 
            'Warranty Term',   
            'Delivery Term',   
            'Payment Term', 
           // 'GRN Number',
           // 'GRN Status for PO',
            // 'PO Status',

];
    }
    if($this->excel_name == 4){
        return [
            'S. No',
            'Date Range',
            'Item Category',
            'PR ItemCode',
            'Item Description',
            'Specification',
            'Make',
            'Uom',
            'Lowest Unit Rate',
            'Min Qty at Lowest Rate',
            'PO No Against Lowest Rate & Min Qty',
            'Max Qty at Lowest Rate',
            'PO No Against Lowest Rate & Max Qty',
            'PO Date-1',
            'Department-1',
            'Business Unit-1',
            'Vendor-1',
            'Customer-1',
            'Circle-1',
            'Highest Unit Rate',
            'Min Qty at Highest Rate',
            'PO No Against Highest Rate & Min Qty',
            'Max Qty at Highest Rate',
            'PO No Against Highest Rate & Max Qty',
            'PO Date-2',
            'Department-2',
            'Business Unit-2',
            'Vendor-2',
            'Customer-2',
            'Circle-2',         
        ];
    }

if($this->excel_name == 5){
        return [
            'S. No',
            'PO Number',             
            'PO Date',  
            'PO Approved By',  
            'PO Approval Status',
            'Current Status',
            'Vendor Code', 
            'Vendor Name', 
            'PO Basic Amount',    
            'PO Tax Amount', 
            'PO Total Amount',
            'GRN Number',
            'Invoice No',
            'Invoice Date',
            'GRN Receiver Name',
            'GRN Receive Date',
            'GRN Unit',
            'GRN Busines Type',
            'GRN Quality Check Status',
            'GRN Gate Entry No',
            'GRN Gate Entry Date',
            'GRN Basic Amount',
            'GRN Frieght Charges',
            'GRN Tax Amount',
            'GRN Total Amount',
            'GRN Status for PO',
        ];
    }
    
    
    
    if($this->excel_name == 6){
        return [
            'S. No.',
            'PR Number',
            'PR Date',
            'Preparer Name',
            'PR Status',
            'Current Status',
            'Item Category',
            'PR ItemCode',
            'Item Description',
            'Specification',
            'Make',
            'UoM',
            'PR Qty',
            'Department',
            'Business Unit',
            'Customer',
            'Site',
            'Circle',
            'Delivery Location',
            'PO Number',
            'PO Date',
            'Current Status',
            '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',
            'GRN Number',
            'Invoice No',
            'Invoice Date',
            'GRN Receiver Name',
            'GRN Receive Date',
            'GRN Unit',
            'GRN Business Type',
            'GRN Quality Check Status',
            'GRN Gate Entry No',
            'GRN Gate Entry Date',
            'GRN Qty',
            'Line GRN Status',
            // 'PO Status',
            'Pending Qty for GRN',
        ];
    }

}

public function startCell(): string
    {
        return 'A2'; // Data starts from row 3
    }

public function registerEvents(): array
    {
        return [
            AfterSheet::class => function(AfterSheet $event) {
                $sheet = $event->sheet->getDelegate();
                $lastColumn = $sheet->getHighestColumn();

// 1. Title styling: apply background from A1 to last header column
                $titleRange = "A1:{$lastColumn}1";
                $sheet->setCellValue('A1', $this->getReportTitle());
                $sheet->getStyle($titleRange)->applyFromArray([
                    'font' => [
                        'bold' => true,
                        'size' => 18,
                        'color' => ['rgb' => 'FFFFFF'],
                    ],
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_LEFT,
                        'vertical' => Alignment::VERTICAL_CENTER,
                    ],
                    'fill' => [
                        'fillType' => Fill::FILL_SOLID,
                        'startColor' => ['rgb' => '428BCA'], // Light blue
                    ],
                ]);

// 2. Header styling (A2:??2)
                 // Header row (A2:??2)
            $lastColumn = $sheet->getHighestColumn();
            $headerRange = "A2:{$lastColumn}2";
            $sheet->getStyle($headerRange)->applyFromArray([
                'font' => [
                    'bold' => true,
                    'size' => 14,
                    'color' => ['rgb' => '000000'],
                ],
                'fill' => [
                    'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                    'startColor' => ['rgb' => '90EE90'], // Light green
                ],
            ]);

// 3. Alternating row colors for data (striped effect)
                $rowCount = $sheet->getHighestRow();
                // Data starts from row 3 (A3)
                for ($row = 3; $row <= $rowCount; $row++) {
                    $fillColor = ($row % 2 == 0) ? 'FFFFFF' : 'EDEDED'; // White for even, light gray for odd
                    $dataRange = "A{$row}:{$lastColumn}{$row}";
                    $sheet->getStyle($dataRange)->applyFromArray([
                        'fill' => [
                            'fillType' => Fill::FILL_SOLID,
                            'startColor' => ['rgb' => $fillColor],
                        ],
                    ]);
                }
            },
        ];
    }

protected function getReportTitle()
    {
        switch ($this->excel_name) {
            case 1: return 'Master Report';
            case 2: return 'PR Report';
            case 3: return 'PO Report';
            case 4: return 'Item Wise Report';
            case 5: return 'PO Wise GRN Report';
            case 6: return 'Item Wise GRN Report';
            default: return 'Report';
        }
    }
}