1#@!#!123s

: / home / shubmkcj / urbane.createerp.com /
Filename : invoice-report-download-old.php
back
<?php
 
ob_start();
session_start();
require "config.php";
require ("PHPExcel/Classes/PHPExcel.php");
$source_id = "";
$bill_pay_name = '';

if (isset($_SESSION["source_id"]) && $_SESSION["source_id"] != "")
{
    $source_id = $_SESSION["source_id"];
}
else
{
    die("Something went wrong..");
}
function create_slug($string){ 
    $slug=strtolower(preg_replace('/[^A-Za-z0-9-]+/', '-', $string)); 
    return $slug; 
}

$report_type = $_POST["report"];
$fromdate = $_POST["fromdate"];
$todate = $_POST["todate"]; 
$bill_pay_name = $_POST['bill_pay_name'];
$from = date("Y-m-d", strtotime($fromdate));
$to = date("Y-m-d", strtotime($todate));
//////////////////////////////////////
//////////// start if invoice/////////
//////////////////////////////////////

if ($report_type == 'All_Invoice'){
    

    $objPHPExcel = new PHPExcel();

    // Set the active Excel worksheet to sheet 0
    $objPHPExcel->setActiveSheetIndex(0);
    $style = array(
        'font' => array(
            'bold' => true
        ) ,
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => '000000'
                )
            )
        )
    );
    $border = array(
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => '000000'
                )
            )
        )
    );
    $style1 = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )

    );
    $headerStyle = array(
        'font' => array(
            'bold' => true,
            'color' => array(
                'rgb' => '000000'
            ) ,
            'size' => 20,
            'name' => 'Verdana'
        )
    );
    $secondLineStyle = array(
        'font' => array(
            'bold' => true,
            'color' => array(
                'rgb' => '000000'
            ) ,
            'size' => 15,
            'name' => 'Verdana'
        )
    );
    $objPHPExcel->getDefaultStyle()
        ->applyFromArray(array(
        'borders' => array(
            'top' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'bottom' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'left' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'right' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            )
        )
    ));
    $objPHPExcel->getDefaultStyle()
        ->applyFromArray($style1);

    //apply the style on column A row 1 to Column B row 1
    $objPHPExcel->getActiveSheet()
        ->getStyle('A1:M1')
        ->applyFromArray($style);
    // $objPHPExcel->getActiveSheet()->getStyle('A4:I4')->applyFromArray($style);
    // $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->applyFromArray($headerStyle);
    // $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->applyFromArray($secondLineStyle);
    $objPHPExcel->getActiveSheet()
        ->setCellValue('A1', "Date");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('B1', "Invoice Number");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('C1', "Proforma Number");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('D1', "Company Name");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('E1', "Contact Person");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('F1', "Mobile Number");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('G1', "GSTIN Number");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('H1', "Bank Charges");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('I1', "Other Charges");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('J1', "Invoice Value");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('K1', "Payment Received");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('L1', "Due Payment");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('M1', "Status");
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('A')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('B')
        ->setWidth(25);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('C')
        ->setWidth(25);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('D')
        ->setWidth(40);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('E')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('F')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('G')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('H')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('I')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('J')
        ->setWidth(30);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('K')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('L')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('M')
        ->setWidth(20);

    $rowCount = 2;
    $i = 1;
    $totalInvoiceAmtPaid=0;
    $inv_due_amt=0;
    $inv_payment_recived=0;
    $inv_other_chges=0;
    $inv_bank_chages=0;
            
            
            
    $sql = "SELECT * FROM  generate_invoice WHERE data_delete != 'yes' AND `marge_status`!='yes' ";
    $result = $conn->query($sql) or die(mysqli_error($conn));
    if (mysqli_num_rows($result) > 0)
    {
        while ($row = mysqli_fetch_assoc($result))
        {
            if (date("Y-m-d", strtotime($row["date"])) >= $from && date("Y-m-d", strtotime($row["date"])) <= $to)
            {

                $status = "";
                if ($row["data_delete"] == "cancel")
                {
                    $status = "Cancelled";
                }

                $payment = 0.00;
                $tax = 0.00;
                $invoice = $row["invoice_Id"];
                $sqlGetPayment = "SELECT amount, tax_deduction FROM payment WHERE reference = '$invoice' AND transaction_type = 'Credit' AND is_deleted !='yes'";
                $resultGetPayment = $conn->query($sqlGetPayment);
                if ($resultGetPayment)
                {
                    if (mysqli_num_rows($resultGetPayment) > 0)
                    {
                        while ($rowGetPayment = mysqli_fetch_assoc($resultGetPayment))
                        {
                            $payment = $rowGetPayment["amount"];
                            $tax = $rowGetPayment["tax_deduction"];
                        }
                    }
                }

                $helping_id = $row['bill_pay_name'];

                $get_bpn = mysqli_query($conn, "SELECT * FROM `contact` where id='$helping_id'");
                $get_bpn_row = mysqli_fetch_assoc($get_bpn);

                $final_bill_pay_name = $get_bpn_row['bill_display_name'];

                $sub_total = sprintf('%0.2f', $row["sub_total"]);
                $b_chagred=$row["bank_charge"];;
             
                $amount_paid = sprintf('%0.2f', $row["amount_paid"]);
                if($b_chagred==35.00){
                    $amount_paid=$amount_paid-35.00;
                }
                
                $totalInvoiceAmtPaid+=$amount_paid;
                $duAmt=$amount_paid-$payment;
                $inv_due_amt+=$duAmt;
                $inv_payment_recived+=$payment;
    $inv_other_chges+=$row["other_charges"];
    $inv_bank_chages+=$row["bank_charge"];
    
    
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('A' . $rowCount, $row["date"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('B' . $rowCount, $row["invoice_Id"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('C' . $rowCount, $row["proforma_id"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('D' . $rowCount, $final_bill_pay_name);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('E' . $rowCount, $row["client_name"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('F' . $rowCount, $row["mobile_no"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('G' . $rowCount, $row["gstin"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('H' . $rowCount, $row["bank_charge"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('I' . $rowCount, $row["other_charges"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('J' . $rowCount, $amount_paid );
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('K' . $rowCount, $payment );
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('L' . $rowCount, $amount_paid-$payment);
                $objPHPExcel->getActiveSheet()
                    ->getStyle('B' . $rowCount . ':L' . $rowCount)->getNumberFormat()
                    ->setFormatCode('0.00');
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('M' . $rowCount, $status);
                $objPHPExcel->getActiveSheet()
                    ->getStyle('A' . $rowCount . ':M' . $rowCount)->applyFromArray($border);
                $rowCount = $rowCount + 1;
            }
        }
    }
    $objPHPExcel->getActiveSheet()
                    ->setCellValue('H' . $rowCount, $inv_bank_chages);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('I' . $rowCount, $inv_other_chges);
    $objPHPExcel->getActiveSheet()
                    ->setCellValue('J' . $rowCount, $totalInvoiceAmtPaid );
         $objPHPExcel->getActiveSheet()
                    ->setCellValue('K' . $rowCount, $inv_payment_recived );
    $objPHPExcel->getActiveSheet()
                    ->setCellValue('L' . $rowCount, $inv_due_amt);

    ///////////////////////////////////////////
    ///////////// End if  All invoice //////////////
    ///////////////////////////////////////////
    
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=invoice_report_from_" . $from. "_to_".$to . ".xls");
  
    
}
//////////////////////////////////////
//////////// start if invoice/////////
//////////////////////////////////////

else if ($report_type == 'Invoice'){
    

    $objPHPExcel = new PHPExcel();

    // Set the active Excel worksheet to sheet 0
    $objPHPExcel->setActiveSheetIndex(0);
    $style = array(
        'font' => array(
            'bold' => true
        ) ,
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => '000000'
                )
            )
        )
    );
    $border = array(
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => '000000'
                )
            )
        )
    );
    $style1 = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )

    );
    $headerStyle = array(
        'font' => array(
            'bold' => true,
            'color' => array(
                'rgb' => '000000'
            ) ,
            'size' => 20,
            'name' => 'Verdana'
        )
    );
    $secondLineStyle = array(
        'font' => array(
            'bold' => true,
            'color' => array(
                'rgb' => '000000'
            ) ,
            'size' => 15,
            'name' => 'Verdana'
        )
    );
    $objPHPExcel->getDefaultStyle()
        ->applyFromArray(array(
        'borders' => array(
            'top' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'bottom' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'left' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'right' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            )
        )
    ));
    $objPHPExcel->getDefaultStyle()
        ->applyFromArray($style1);

    //apply the style on column A row 1 to Column B row 1
    $objPHPExcel->getActiveSheet()
        ->getStyle('A1:M1')
        ->applyFromArray($style);
    // $objPHPExcel->getActiveSheet()->getStyle('A4:I4')->applyFromArray($style);
    // $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->applyFromArray($headerStyle);
    // $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->applyFromArray($secondLineStyle);
    $objPHPExcel->getActiveSheet()
        ->setCellValue('A1', "Date");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('B1', "Invoice Number");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('C1', "Proforma Number");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('D1', "Company Name");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('E1', "Contact Person");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('F1', "Mobile Number");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('G1', "GSTIN Number");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('H1', "Bank Charges");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('I1', "Other Charges");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('J1', "Invoice Value");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('K1', "Payment Received");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('L1', "Due Payment");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('M1', "Status");
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('A')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('B')
        ->setWidth(25);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('C')
        ->setWidth(25);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('D')
        ->setWidth(40);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('E')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('F')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('G')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('H')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('I')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('J')
        ->setWidth(30);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('K')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('L')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('M')
        ->setWidth(20);

    $rowCount = 2;
    $i = 1;
    $totalInvoiceAmtPaid=0;
    $inv_due_amt=0;
    $inv_payment_recived=0;
    $inv_other_chges=0;
    $inv_bank_chages=0;
            
            
            
    $sql = "SELECT * FROM  generate_invoice WHERE data_delete != 'yes' AND `marge_status`!='yes' AND `bill_pay_name` = $bill_pay_name";
    $result = $conn->query($sql) or die(mysqli_error($conn));
    if (mysqli_num_rows($result) > 0)
    {
        while ($row = mysqli_fetch_assoc($result))
        {
            if (date("Y-m-d", strtotime($row["date"])) >= $from && date("Y-m-d", strtotime($row["date"])) <= $to)
            {

                $status = "";
                if ($row["data_delete"] == "cancel")
                {
                    $status = "Cancelled";
                }

                $payment = 0.00;
                $tax = 0.00;
                $invoice = $row["invoice_Id"];
                $sqlGetPayment = "SELECT amount, tax_deduction FROM payment WHERE reference = '$invoice' AND transaction_type = 'Credit' AND is_deleted !='yes'";
                $resultGetPayment = $conn->query($sqlGetPayment);
                if ($resultGetPayment)
                {
                    if (mysqli_num_rows($resultGetPayment) > 0)
                    {
                        while ($rowGetPayment = mysqli_fetch_assoc($resultGetPayment))
                        {
                            $payment = $rowGetPayment["amount"];
                            $tax = $rowGetPayment["tax_deduction"];
                        }
                    }
                }

                $helping_id = $row['bill_pay_name'];

                $get_bpn = mysqli_query($conn, "SELECT * FROM `contact` where id='$helping_id'");
                $get_bpn_row = mysqli_fetch_assoc($get_bpn);

                $final_bill_pay_name = $get_bpn_row['bill_display_name'];

                $sub_total = sprintf('%0.2f', $row["sub_total"]);
                $b_chagred=$row["bank_charge"];;
             
                $amount_paid = sprintf('%0.2f', $row["amount_paid"]);
                if($b_chagred==35.00){
                    $amount_paid=$amount_paid-35.00;
                }
                
                $totalInvoiceAmtPaid+=$amount_paid;
                $duAmt=$amount_paid-$payment;
                $inv_due_amt+=$duAmt;
                $inv_payment_recived+=$payment;
    $inv_other_chges+=$row["other_charges"];
    $inv_bank_chages+=$row["bank_charge"];
    
    
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('A' . $rowCount, $row["date"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('B' . $rowCount, $row["invoice_Id"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('C' . $rowCount, $row["proforma_id"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('D' . $rowCount, $final_bill_pay_name);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('E' . $rowCount, $row["client_name"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('F' . $rowCount, $row["mobile_no"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('G' . $rowCount, $row["gstin"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('H' . $rowCount, $row["bank_charge"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('I' . $rowCount, $row["other_charges"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('J' . $rowCount, $amount_paid );
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('K' . $rowCount, $payment );
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('L' . $rowCount, $amount_paid-$payment);
                $objPHPExcel->getActiveSheet()
                    ->getStyle('B' . $rowCount . ':L' . $rowCount)->getNumberFormat()
                    ->setFormatCode('0.00');
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('M' . $rowCount, $status);
                $objPHPExcel->getActiveSheet()
                    ->getStyle('A' . $rowCount . ':M' . $rowCount)->applyFromArray($border);
                $rowCount = $rowCount + 1;
            }
        }
    }
    $objPHPExcel->getActiveSheet()
                    ->setCellValue('H' . $rowCount, $inv_bank_chages);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('I' . $rowCount, $inv_other_chges);
    $objPHPExcel->getActiveSheet()
                    ->setCellValue('J' . $rowCount, $totalInvoiceAmtPaid );
         $objPHPExcel->getActiveSheet()
                    ->setCellValue('K' . $rowCount, $inv_payment_recived );
    $objPHPExcel->getActiveSheet()
                    ->setCellValue('L' . $rowCount, $inv_due_amt);

    ///////////////////////////////////////////
    ///////////// End if invoice //////////////
    ///////////////////////////////////////////
    
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=invoice_report_from_" . $from. "_to_".$to . ".xls");
    
}
else if ($report_type == 'Proforma'){

    //////////////////////////////////////
    //////////// strat If Proforma/////////
    //////////////////////////////////////
    

    $objPHPExcel = new PHPExcel();

    // Set the active Excel worksheet to sheet 0
    $objPHPExcel->setActiveSheetIndex(0);
    $style = array(
        'font' => array(
            'bold' => true
        ) ,
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => '000000'
                )
            )
        )
    );
    $border = array(
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => '000000'
                )
            )
        )
    );
    $style1 = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )

    );
    $headerStyle = array(
        'font' => array(
            'bold' => true,
            'color' => array(
                'rgb' => '000000'
            ) ,
            'size' => 20,
            'name' => 'Verdana'
        )
    );
    $secondLineStyle = array(
        'font' => array(
            'bold' => true,
            'color' => array(
                'rgb' => '000000'
            ) ,
            'size' => 15,
            'name' => 'Verdana'
        )
    );
    $objPHPExcel->getDefaultStyle()
        ->applyFromArray(array(
        'borders' => array(
            'top' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'bottom' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'left' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'right' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            )
        )
    ));
    $objPHPExcel->getDefaultStyle()
        ->applyFromArray($style1);

    //apply the style on column A row 1 to Column B row 1
    $objPHPExcel->getActiveSheet()
        ->getStyle('A1:L1')
        ->applyFromArray($style);
    // $objPHPExcel->getActiveSheet()->getStyle('A4:I4')->applyFromArray($style);
    // $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->applyFromArray($headerStyle);
    // $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->applyFromArray($secondLineStyle);
    $objPHPExcel->getActiveSheet()
        ->setCellValue('A1', "Date");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('B1', "Proforma Number");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('C1', "Company Name");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('D1', "Contact Person");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('E1', "Mobile Number");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('F1', "GSTIN Number");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('G1', "Bank Charges");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('H1', "Other Charges");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('I1', "Total Amount");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('J1', "Status");
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('A')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('B')
        ->setWidth(25);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('C')
        ->setWidth(40);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('D')
        ->setWidth(25);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('E')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('F')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('G')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('H')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('I')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('J')
        ->setWidth(30);

    $rowCount = 2;
    $i = 1;
    $pro_totalAmt=0;
    $inv_other_chges=0;
    $inv_bank_chages=0;
    $sql = "SELECT * FROM  proforma WHERE data_delete != 'yes' AND `bill_pay_name` = $bill_pay_name";
    $result = $conn->query($sql) or die(mysqli_error($conn));
    if (mysqli_num_rows($result) > 0)
    {
        while ($row = mysqli_fetch_assoc($result))
        {
            if (date("Y-m-d", strtotime($row["date"])) >= $from && date("Y-m-d", strtotime($row["date"])) <= $to)
            {

                $status = "";
                if ($row["data_delete"] == "cancel")
                {
                    $status = "Cancelled";
                }

                $payment = 0.00;
                $tax = 0.00;
                $invoice = $row["invoice_Id"];
                $sqlGetPayment = "SELECT amount, tax_deduction FROM payment WHERE reference = '$invoice'";
                $resultGetPayment = $conn->query($sqlGetPayment);
                if ($resultGetPayment)
                {
                    if (mysqli_num_rows($resultGetPayment) > 0)
                    {
                        while ($rowGetPayment = mysqli_fetch_assoc($resultGetPayment))
                        {
                            $payment = $rowGetPayment["amount"];
                            $tax = $rowGetPayment["tax_deduction"];
                        }
                    }
                }

                $helping_id = $row['bill_pay_name'];

                $get_bpn = mysqli_query($conn, "SELECT * FROM `contact` where id='$helping_id'");
                $get_bpn_row = mysqli_fetch_assoc($get_bpn);

                $final_bill_pay_name = $get_bpn_row['bill_display_name'];

                $sub_total = sprintf('%0.2f', $row["sub_total"]);
                $amount_paid = sprintf('%0.2f', $row["amount_paid"]);
                $pro_totalAmt+=$amount_paid;
                $inv_other_chges+=$row["other_charges"];
                $inv_bank_chages+=$row["bank_charge"];
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('A' . $rowCount, $row["date"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('B' . $rowCount, $row["proforma_id"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('C' . $rowCount, $final_bill_pay_name);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('D' . $rowCount, $row["client_name"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('E' . $rowCount, $row["mobile_no"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('F' . $rowCount, $row["gstin"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('G' . $rowCount, $row["bank_charge"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('H' . $rowCount, $row["other_charges"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('I' . $rowCount, $amount_paid);
                $objPHPExcel->getActiveSheet()
                    ->getStyle('B' . $rowCount . ':I' . $rowCount)->getNumberFormat()
                    ->setFormatCode('0.00');
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('J' . $rowCount, $status);
                $objPHPExcel->getActiveSheet()
                    ->getStyle('A' . $rowCount . ':J' . $rowCount)->applyFromArray($border);
                $rowCount = $rowCount + 1;
            }
        }
    }
    
    $objPHPExcel->getActiveSheet()
                    ->setCellValue('G' . $rowCount, $inv_bank_chages);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('H' . $rowCount, $inv_other_chges);

 $objPHPExcel->getActiveSheet()
                    ->setCellValue('I' . $rowCount, $pro_totalAmt);
    ///////////////////////////////////////////
    ///////////// End If Proforma //////////////
    ///////////////////////////////////////////
    
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=proforma_report_from_" . $from. "_to_".$to . ".xls");
    
}
else if ($report_type == 'All_Proforma'){

    //////////////////////////////////////
    //////////// strat If All Proforma/////////
    //////////////////////////////////////
    

    $objPHPExcel = new PHPExcel();

    // Set the active Excel worksheet to sheet 0
    $objPHPExcel->setActiveSheetIndex(0);
    $style = array(
        'font' => array(
            'bold' => true
        ) ,
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => '000000'
                )
            )
        )
    );
    $border = array(
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => '000000'
                )
            )
        )
    );
    $style1 = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )

    );
    $headerStyle = array(
        'font' => array(
            'bold' => true,
            'color' => array(
                'rgb' => '000000'
            ) ,
            'size' => 20,
            'name' => 'Verdana'
        )
    );
    $secondLineStyle = array(
        'font' => array(
            'bold' => true,
            'color' => array(
                'rgb' => '000000'
            ) ,
            'size' => 15,
            'name' => 'Verdana'
        )
    );
    $objPHPExcel->getDefaultStyle()
        ->applyFromArray(array(
        'borders' => array(
            'top' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'bottom' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'left' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'right' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            )
        )
    ));
    $objPHPExcel->getDefaultStyle()
        ->applyFromArray($style1);

    //apply the style on column A row 1 to Column B row 1
    $objPHPExcel->getActiveSheet()
        ->getStyle('A1:L1')
        ->applyFromArray($style);
    // $objPHPExcel->getActiveSheet()->getStyle('A4:I4')->applyFromArray($style);
    // $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->applyFromArray($headerStyle);
    // $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->applyFromArray($secondLineStyle);
    $objPHPExcel->getActiveSheet()
        ->setCellValue('A1', "Date");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('B1', "Proforma Number");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('C1', "Company Name");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('D1', "Contact Person");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('E1', "Mobile Number");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('F1', "GSTIN Number");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('G1', "Bank Charges");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('H1', "Other Charges");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('I1', "Total Amount");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('J1', "Status");
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('A')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('B')
        ->setWidth(25);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('C')
        ->setWidth(40);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('D')
        ->setWidth(25);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('E')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('F')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('G')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('H')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('I')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('J')
        ->setWidth(30);

    $rowCount = 2;
    $i = 1;
    $pro_totalAmt=0;
    $inv_other_chges=0;
    $inv_bank_chages=0;
    $sql = "SELECT * FROM  proforma WHERE data_delete != 'yes' ";
    $result = $conn->query($sql) or die(mysqli_error($conn));
    if (mysqli_num_rows($result) > 0)
    {
        while ($row = mysqli_fetch_assoc($result))
        {
            if (date("Y-m-d", strtotime($row["date"])) >= $from && date("Y-m-d", strtotime($row["date"])) <= $to)
            {

                $status = "";
                if ($row["data_delete"] == "cancel")
                {
                    $status = "Cancelled";
                }

                $payment = 0.00;
                $tax = 0.00;
                $invoice = $row["invoice_Id"];
                $sqlGetPayment = "SELECT amount, tax_deduction FROM payment WHERE reference = '$invoice'";
                $resultGetPayment = $conn->query($sqlGetPayment);
                if ($resultGetPayment)
                {
                    if (mysqli_num_rows($resultGetPayment) > 0)
                    {
                        while ($rowGetPayment = mysqli_fetch_assoc($resultGetPayment))
                        {
                            $payment = $rowGetPayment["amount"];
                            $tax = $rowGetPayment["tax_deduction"];
                        }
                    }
                }

                $helping_id = $row['bill_pay_name'];

                $get_bpn = mysqli_query($conn, "SELECT * FROM `contact` where id='$helping_id'");
                $get_bpn_row = mysqli_fetch_assoc($get_bpn);

                $final_bill_pay_name = $get_bpn_row['bill_display_name'];

                $sub_total = sprintf('%0.2f', $row["sub_total"]);
                $amount_paid = sprintf('%0.2f', $row["amount_paid"]);
                $pro_totalAmt+=$amount_paid;
                $inv_other_chges+=$row["other_charges"];
                $inv_bank_chages+=$row["bank_charge"];
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('A' . $rowCount, $row["date"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('B' . $rowCount, $row["proforma_id"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('C' . $rowCount, $final_bill_pay_name);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('D' . $rowCount, $row["client_name"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('E' . $rowCount, $row["mobile_no"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('F' . $rowCount, $row["gstin"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('G' . $rowCount, $row["bank_charge"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('H' . $rowCount, $row["other_charges"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('I' . $rowCount, $amount_paid);
                $objPHPExcel->getActiveSheet()
                    ->getStyle('B' . $rowCount . ':I' . $rowCount)->getNumberFormat()
                    ->setFormatCode('0.00');
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('J' . $rowCount, $status);
                $objPHPExcel->getActiveSheet()
                    ->getStyle('A' . $rowCount . ':J' . $rowCount)->applyFromArray($border);
                $rowCount = $rowCount + 1;
            }
        }
    }
    
    $objPHPExcel->getActiveSheet()
                    ->setCellValue('G' . $rowCount, $inv_bank_chages);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('H' . $rowCount, $inv_other_chges);

 $objPHPExcel->getActiveSheet()
                    ->setCellValue('I' . $rowCount, $pro_totalAmt);
    ///////////////////////////////////////////
    ///////////// End If All Proforma //////////////
    ///////////////////////////////////////////
    
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=proforma_report_from_" . $from. "_to_".$to . ".xls");
    
}
else if ($report_type == 'Packing List'){

    ////////////////////////////////////////////
    //////////// strat If Packing List /////////
    ////////////////////////////////////////////
    

    $objPHPExcel = new PHPExcel();

    // Set the active Excel worksheet to sheet 0
    $objPHPExcel->setActiveSheetIndex(0);
    $style = array(
        'font' => array(
            'bold' => true
        ) ,
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => '000000'
                )
            )
        )
    );
    $border = array(
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => '000000'
                )
            )
        )
    );
    $style1 = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )

    );
    $headerStyle = array(
        'font' => array(
            'bold' => true,
            'color' => array(
                'rgb' => '000000'
            ) ,
            'size' => 20,
            'name' => 'Verdana'
        )
    );
    $secondLineStyle = array(
        'font' => array(
            'bold' => true,
            'color' => array(
                'rgb' => '000000'
            ) ,
            'size' => 15,
            'name' => 'Verdana'
        )
    );
    $objPHPExcel->getDefaultStyle()
        ->applyFromArray(array(
        'borders' => array(
            'top' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'bottom' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'left' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'right' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            )
        )
    ));
    $objPHPExcel->getDefaultStyle()
        ->applyFromArray($style1);

    //apply the style on column A row 1 to Column B row 1
    $objPHPExcel->getActiveSheet()
        ->getStyle('A1:f1')
        ->applyFromArray($style);
    // $objPHPExcel->getActiveSheet()->getStyle('A4:I4')->applyFromArray($style);
    // $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->applyFromArray($headerStyle);
    // $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->applyFromArray($secondLineStyle);
    $objPHPExcel->getActiveSheet()
        ->setCellValue('A1', "Date");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('B1', "Company Name");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('C1', "Mobile Number");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('D1', "Proforma Number");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('E1', "Invoice Number");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('F1', "Invoice Reference");
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('A')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('B')
        ->setWidth(35);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('C')
        ->setWidth(35);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('D')
        ->setWidth(25);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('E')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('F')
        ->setWidth(20);

    $rowCount = 2;
    $i = 1;
  
    $sql = "SELECT * FROM  packing_list WHERE data_delete != 'yes' AND `bill_display_name` = ' $final_bill_pay_name'";
    $result = $conn->query($sql) or die(mysqli_error($conn));
    if (mysqli_num_rows($result) > 0)
    {
        while ($row = mysqli_fetch_assoc($result))
        {
            if (date("Y-m-d", strtotime($row["date"])) >= $from && date("Y-m-d", strtotime($row["date"])) <= $to)
            {

                $final_bill_pay_name = $row['bill_display_name'];

                $objPHPExcel->getActiveSheet()
                    ->setCellValue('A' . $rowCount, $row["date"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('B' . $rowCount, $final_bill_pay_name);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('C' . $rowCount, $row["mobile"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('D' . $rowCount, $row["proforma_id"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('E' . $rowCount, $row["invoice_id"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('F' . $rowCount, $row["invoice_reference"]);
                $objPHPExcel->getActiveSheet()
                    ->getStyle('A' . $rowCount . ':F' . $rowCount)->applyFromArray($border);
                $rowCount = $rowCount + 1;
            }
        }
    }

    ////////////////////////////////////////////////
    ///////////// End If Packing List //////////////
    ////////////////////////////////////////////////
    header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=packing_list_report_from_" . $from. "_to_".$to . ".xls");

    
}
else if ($report_type == 'Customer'){
    
    ////////////////////////////////////////////
    //////////// strat If Customer /////////
    ////////////////////////////////////////////
    
 
    $objPHPExcel = new PHPExcel();

    // Set the active Excel worksheet to sheet 0
    $objPHPExcel->setActiveSheetIndex(0);
    $style = array(
        'font' => array(
            'bold' => true
        ) ,
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => '000000'
                )
            )
        )
    );
    $border = array(
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => '000000'
                )
            )
        )
    );
    $style1 = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )

    );
    $headerStyle = array(
        'font' => array(
            'bold' => true,
            'color' => array(
                'rgb' => '000000'
            ) ,
            'size' => 20,
            'name' => 'Verdana'
        )
    );
    $secondLineStyle = array(
        'font' => array(
            'bold' => true,
            'color' => array(
                'rgb' => '000000'
            ) ,
            'size' => 15,
            'name' => 'Verdana'
        )
    );
    $objPHPExcel->getDefaultStyle()
        ->applyFromArray(array(
        'borders' => array(
            'top' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'bottom' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'left' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'right' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            )
        )
    ));
    $objPHPExcel->getDefaultStyle()
        ->applyFromArray($style1);

    //apply the style on column A row 1 to Column B row 1
    $objPHPExcel->getActiveSheet()
        ->getStyle('A1:M1')
        ->applyFromArray($style);
    // $objPHPExcel->getActiveSheet()->getStyle('A4:I4')->applyFromArray($style);
    // $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->applyFromArray($headerStyle);
    // $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->applyFromArray($secondLineStyle);
    $objPHPExcel->getActiveSheet()
        ->setCellValue('A1', "Date");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('B1', "Invoice Number");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('C1', "Proforma Number");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('D1', "Company Name");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('E1', "Contact Person");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('F1', "Mobile Number");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('G1', "GSTIN Number");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('H1', "Bank Charges");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('I1', "Other Charges");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('J1', "Payment Received");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('K1', "Due Payment");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('L1', "Total Amount");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('M1', "Status");
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('A')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('B')
        ->setWidth(25);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('C')
        ->setWidth(25);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('D')
        ->setWidth(40);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('E')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('F')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('G')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('H')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('I')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('J')
        ->setWidth(30);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('K')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('L')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('M')
        ->setWidth(20);

    $rowCount = 2;
    $i = 1;
    $customer_payment_recived=0;
    $customer_payment_due=0;
    $customer_total_amt=0;
    $customer_other_chges=0;
    $customer_bank_chages=0;
    $sql = "SELECT * FROM  generate_invoice WHERE bill_pay_name='$bill_pay_name' and data_delete != 'yes'";
    $result = $conn->query($sql) or die(mysqli_error($conn));

                
                
    if (mysqli_num_rows($result) > 0)
    {
        while ($row = mysqli_fetch_assoc($result))
        {
              $helping_id = $row['bill_pay_name'];

                $get_bpn = mysqli_query($conn, "SELECT * FROM `contact` where id='$helping_id'");
                $get_bpn_row = mysqli_fetch_assoc($get_bpn);

                $final_bill_pay_name = $get_bpn_row['bill_display_name'];
            if (date("Y-m-d", strtotime($row["date"])) >= $from && date("Y-m-d", strtotime($row["date"])) <= $to)
            {  

                $status = "";
                if ($row["data_delete"] == "cancel")
                {
                    $status = "Cancelled";
                }

                $payment = 0.00;
                $tax = 0.00;
                $invoice = $row["invoice_Id"];
                $sqlGetPayment = "SELECT amount, tax_deduction FROM payment WHERE reference = '$invoice'";
                $resultGetPayment = $conn->query($sqlGetPayment);
                if ($resultGetPayment)
                {
                    if (mysqli_num_rows($resultGetPayment) > 0)
                    {
                        while ($rowGetPayment = mysqli_fetch_assoc($resultGetPayment))
                        {
                            $payment = $rowGetPayment["amount"];
                            $tax = $rowGetPayment["tax_deduction"];
                        }
                    }
                }

              

                $sub_total = sprintf('%0.2f', $row["sub_total"]);
                $amount_paid = sprintf('%0.2f', $row["amount_paid"]);
                $customer_payment_recived+=$payment;
                $customer_total_amt+=$amount_paid;
                $p_due=$amount_paid-$payment;
                $customer_payment_due+=$p_due;
                
                 $customer_other_chges+=$row["other_charges"];
            $customer_bank_chages+=$row["bank_charge"];
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('A' . $rowCount, $row["date"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('B' . $rowCount, $row["invoice_Id"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('C' . $rowCount, $row["proforma_id"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('D' . $rowCount, $final_bill_pay_name);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('E' . $rowCount, $row["client_name"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('F' . $rowCount, $row["mobile_no"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('G' . $rowCount, $row["gstin"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('H' . $rowCount, $row["bank_charge"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('I' . $rowCount, $row["other_charges"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('J' . $rowCount, $payment);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('K' . $rowCount, $amount_paid-$payment);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('L' . $rowCount, $amount_paid);
                $objPHPExcel->getActiveSheet()
                    ->getStyle('B' . $rowCount . ':L' . $rowCount)->getNumberFormat()
                    ->setFormatCode('0.00');
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('M' . $rowCount, $status);
                $objPHPExcel->getActiveSheet()
                    ->getStyle('A' . $rowCount . ':M' . $rowCount)->applyFromArray($border);
                $rowCount = $rowCount + 1;
        }
            
        }
    }
    $objPHPExcel->getActiveSheet()
                    ->setCellValue('H' . $rowCount, $customer_bank_chages);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('I' . $rowCount, $customer_other_chges);
    $objPHPExcel->getActiveSheet()
                    ->setCellValue('J' . $rowCount, $customer_payment_recived);
    $objPHPExcel->getActiveSheet()
                    ->setCellValue('K' . $rowCount, $customer_payment_due);
    
    $objPHPExcel->getActiveSheet()
                    ->setCellValue('L' . $rowCount, $customer_total_amt);
   

    ////////////////////////////////////////////////
    ///////////// End If Customer //////////////
    ////////////////////////////////////////////////
    $fName=create_slug($final_bill_pay_name);
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=".$fName."_report_from_" . $from. "_to_".$to . ".xls");
    
}
else if ($report_type == 'purchase_order'){
    

    $objPHPExcel = new PHPExcel();

    // Set the active Excel worksheet to sheet 0
    $objPHPExcel->setActiveSheetIndex(0);
    $style = array(
        'font' => array(
            'bold' => true
        ) ,
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => '000000'
                )
            )
        )
    );
    $border = array(
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => '000000'
                )
            )
        )
    );
    $style1 = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )

    );
    $headerStyle = array(
        'font' => array(
            'bold' => true,
            'color' => array(
                'rgb' => '000000'
            ) ,
            'size' => 20,
            'name' => 'Verdana'
        )
    );
    $secondLineStyle = array(
        'font' => array(
            'bold' => true,
            'color' => array(
                'rgb' => '000000'
            ) ,
            'size' => 15,
            'name' => 'Verdana'
        )
    );
    $objPHPExcel->getDefaultStyle()
        ->applyFromArray(array(
        'borders' => array(
            'top' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'bottom' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'left' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'right' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            )
        )
    ));
    $objPHPExcel->getDefaultStyle()
        ->applyFromArray($style1);

    //apply the style on column A row 1 to Column B row 1
    $objPHPExcel->getActiveSheet()
        ->getStyle('A1:M1')
        ->applyFromArray($style);
    // $objPHPExcel->getActiveSheet()->getStyle('A4:I4')->applyFromArray($style);
    // $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->applyFromArray($headerStyle);
    // $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->applyFromArray($secondLineStyle);
    $objPHPExcel->getActiveSheet()
        ->setCellValue('A1', "Date");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('B1', "Purchase Order");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('C1', "Manufacturer");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('D1', "Bank Charges (in USD)");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('E1', "Sub Total Amount (in USD)");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('F1', "Total Bill Amount (in USD)");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('G1', "Paid Amount (in USD)");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('H1', "Remain Amount (in USD)");
    
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('A')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('B')
        ->setWidth(25);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('C')
        ->setWidth(25);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('D')
        ->setWidth(40);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('E')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('F')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('G')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('H')
        ->setWidth(20);
  
   

    $rowCount = 2;
    $i = 1;
    $totalPoAmtPaid=0;
    $inv_due_amt=0;
    $inv_payment_recived=0;
    $inv_other_chges=0;
    $inv_bank_chages=0;
    $subtotal_price=0;
    $remain_payment_amt=0;
            
            
    $sql = "SELECT * FROM  `purchase_order` WHERE `data_delete` != 'yes' AND  ORDER BY `id` DESC";
    $result = $conn->query($sql) or die(mysqli_error($conn));
    if (mysqli_num_rows($result) > 0)
    {
        while ($row = mysqli_fetch_assoc($result))
        {
            if (date("Y-m-d", strtotime($row["order_date"])) >= $from && date("Y-m-d", strtotime($row["order_date"])) <= $to)
            {

                $status = "";
                if ($row["data_delete"] == "yes")
                {
                    $status = "Cancelled";
                }

                $payment = 0.00;
                $tax = 0.00;
                $po_id = $row["po_id"];
                $manufacturer_id = $row['manufacturer'];
                $amt_in_usd = $row['amt_in_usd'];
                $sub_total_amt_in_usd = $row['sub_total_amt_in_usd'];
                $sub_total = sprintf('%0.2f', $row["subtotal_price"]);
                $b_chagred=$row["bank_charges"];
                $amount_paid = sprintf('%0.2f', $row["amount_paind"]);
                if($b_chagred==''){
                    $b_chagred=0.0;
                }else{
                    $b_chagred=$b_chagred;
                }
                
                
                $sqlGetPayment = "SELECT amount FROM `purchase_order_payments` WHERE `po_no` = '$po_id'";
                $resultGetPayment = $conn->query($sqlGetPayment);
                if ($resultGetPayment)
                {
                    if (mysqli_num_rows($resultGetPayment) > 0)
                    {
                        while ($rowGetPayment = mysqli_fetch_assoc($resultGetPayment))
                        {
                            $payment = $rowGetPayment["amount"];
                            $payment_in_usd = $rowGetPayment["amount_in_usd"];
                        }
                    }
                }
                
                
                
                $get_bpn = mysqli_query($conn, "SELECT * FROM `manufacturer` where id='$manufacturer_id'");
                $get_bpn_row = mysqli_fetch_assoc($get_bpn);

                $final_bill_pay_name = $get_bpn_row['manufacturer_display_name'];
                $m_currency = $get_bpn_row['currency'];
                if($m_currency=='RMB'){
                    $query_c="SELECT * FROM `conversion_rate` ORDER BY `conversion_rate`.`id` DESC LIMIT 1";
                    $result_c = mysqli_query($conn, $query_c);
                    $row_c = mysqli_fetch_assoc($result_c);
                    $last_rmb = $row_c["rmb"];
                    
                    // $payment1 = $payment/$last_rmb;
                    
                    
                    // $b_chagred=$b_chagred/$last_rmb;
                    // $sub_total=$sub_total/$last_rmb;
             
                    // $amount_paid=$amount_paid/$last_rmb;
               
                    
                }else{
                    $last_rmb=1;
                    // $b_chagred=$b_chagred/$last_rmb;
                    // $payment1 = $payment/$last_rmb;
                    // $sub_total=$sub_total/$last_rmb;
             
                    // $amount_paid=$amount_paid/$last_rmb;

                }
                
                if($payment_in_usd==''){
                    $payment=$payment;
                    $payment1 = $payment/$last_rmb;
                }else{
                    $payment=$payment_in_usd;
                    $payment1=$payment;
                }
                
                if($amt_in_usd==''){
                    $amount_paid=$amount_paid;
                    $sub_total=$sub_total;
                    $sub_total=$sub_total/$last_rmb;
                    $amount_paid=$amount_paid/$last_rmb;
                }else{
                    $amount_paid=$amt_in_usd;
                    $sub_total=$sub_total_amt_in_usd;
                }
                    
                $b_chagred=$b_chagred/$last_rmb;
                
                $totalPoAmtPaid+=$amount_paid;
                
                
                
                $remain_amt=$amount_paid-$payment1;
                
                $inv_payment_recived+=$payment1;
                $subtotal_price+=$sub_total;
                $inv_bank_chages+=$b_chagred;
                $remain_payment_amt+=$remain_amt;
    
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('A' . $rowCount, $row["order_date"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('B' . $rowCount, $row["po_id"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('C' . $rowCount, $final_bill_pay_name);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('D' . $rowCount, $b_chagred);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('E' . $rowCount, $sub_total);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('F' . $rowCount, $amount_paid );
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('G' . $rowCount, $payment);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('H' . $rowCount, $amount_paid-$payment);
                    
                
                
                $objPHPExcel->getActiveSheet()
                    ->getStyle('B' . $rowCount . ':H' . $rowCount)->getNumberFormat()
                    ->setFormatCode('0.00');
                
                $objPHPExcel->getActiveSheet()
                    ->getStyle('A' . $rowCount . ':H' . $rowCount)->applyFromArray($border);
                $rowCount = $rowCount + 1;
            }
        }
    }
    $objPHPExcel->getActiveSheet()
                    ->setCellValue('D' . $rowCount, $inv_bank_chages);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('E' . $rowCount, $subtotal_price);
    $objPHPExcel->getActiveSheet()
                    ->setCellValue('F' . $rowCount, $totalPoAmtPaid);
         $objPHPExcel->getActiveSheet()
                    ->setCellValue('G' . $rowCount, $inv_payment_recived);
    $objPHPExcel->getActiveSheet()
                    ->setCellValue('H' . $rowCount, $remain_payment_amt);

    ///////////////////////////////////////////
    ///////////// End if invoice //////////////
    ///////////////////////////////////////////
    
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=po_report_from_" . $from. "_to_".$to . ".xls");
    
}
else if ($report_type == 'in_out_report'){

    //////////////////////////////////////
    //////////// strat If in  out report/////////
    //////////////////////////////////////
    

    $objPHPExcel = new PHPExcel();

    // Set the active Excel worksheet to sheet 0
    $objPHPExcel->setActiveSheetIndex(0);
    $style = array(
        'font' => array(
            'bold' => true
        ) ,
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => '000000'
                )
            )
        )
    );
    $border = array(
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => '000000'
                )
            )
        )
    );
    $style1 = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )

    );
    $headerStyle = array(
        'font' => array(
            'bold' => true,
            'color' => array(
                'rgb' => '000000'
            ) ,
            'size' => 20,
            'name' => 'Verdana'
        )
    );
    $secondLineStyle = array(
        'font' => array(
            'bold' => true,
            'color' => array(
                'rgb' => '000000'
            ) ,
            'size' => 15,
            'name' => 'Verdana'
        )
    );
    $objPHPExcel->getDefaultStyle()
        ->applyFromArray(array(
        'borders' => array(
            'top' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'bottom' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'left' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'right' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            )
        )
    ));
    $objPHPExcel->getDefaultStyle()
        ->applyFromArray($style1);

    //apply the style on column A row 1 to Column B row 1
    $objPHPExcel->getActiveSheet()
        ->getStyle('A1:P1')
        ->applyFromArray($style);
    // $objPHPExcel->getActiveSheet()->getStyle('A4:I4')->applyFromArray($style);
    // $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->applyFromArray($headerStyle);
    // $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->applyFromArray($secondLineStyle);
    $objPHPExcel->getActiveSheet()
        ->setCellValue('A1', "PI NO");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('B1', "PI Date");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('C1', "PI Value");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('D1', "Client Name");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('E1', "PO No");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('F1', "PO Date");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('G1', "PO Issue to");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('H1', "PO Value");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('I1', "Payment to Vendor");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('J1', "Payment Date");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('K1', "Invoice No");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('L1', "Invoice Date");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('M1', "Invoice Value");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('N1', "Client Name");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('O1', "Payment Received");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('P1', "Payment Date");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('Q1', "Remarks, if any");
        
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('A')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('B')
        ->setWidth(25);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('C')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('D')
        ->setWidth(40);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('E')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('F')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('G')
        ->setWidth(40);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('H')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('I')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('J')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('K')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('L')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('M')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('N')
        ->setWidth(40);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('O')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('P')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('Q')
        ->setWidth(40);

    $rowCount = 2;
    $i = 1;
    $pro_totalAmt=0;
    $inv_other_chges=0;
    $inv_bank_chages=0;
    $totalPoAmtPaid=0;
    $sql = "SELECT * FROM `proforma` WHERE data_delete != 'yes' ORDER BY Date ASC";
    $result = $conn->query($sql) or die(mysqli_error($conn));
    if (mysqli_num_rows($result) > 0)
    {
        while ($row = mysqli_fetch_assoc($result))
        {
            if (date("Y-m-d", strtotime($row["date"])) >= $from && date("Y-m-d", strtotime($row["date"])) <= $to)
            {

                $status = "";
                if ($row["data_delete"] == "cancel")
                {
                    $status = "Cancelled";
                }

                
                $helping_id = $row['bill_pay_name'];
                $proforma_id = $row['proforma_id'];
                $proforma_date = $row['date'];
                $p_bank_chages=$row["bank_charge"];
                $amount_paid = sprintf('%0.2f', $row["amount_paid"]);
                if($p_bank_chages>0){
                    $amount_paid=$amount_paid-$p_bank_chages;
                }
                $get_bpn = mysqli_query($conn, "SELECT * FROM `contact` where id='$helping_id'");
                $get_bpn_row = mysqli_fetch_assoc($get_bpn);

                $final_bill_pay_name = $get_bpn_row['bill_display_name'];
                $pro_totalAmt+=$amount_paid;
                
              
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('A' . $rowCount, $proforma_id);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('B' . $rowCount, $proforma_date);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('C' . $rowCount, $amount_paid);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('D' . $rowCount, $final_bill_pay_name);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('E' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('F' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('G' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('H' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('I' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('J' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('K' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('L' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('M' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('N' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('O' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('P' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('Q' . $rowCount, "");
                    
                $sql_po = "SELECT * FROM  `purchase_order` WHERE `purchase_order`.`proforma_id`='$proforma_id' AND `data_delete` != 'yes'";
                $result_po = $conn->query($sql_po) or die(mysqli_error($conn));
                if (mysqli_num_rows($result_po) > 0){
                    while ($row_po = mysqli_fetch_assoc($result_po)){
                        $po_id = $row_po["po_id"];
                        $po_order_date = $row_po["order_date"];
                        $po_manufacturer_id = $row_po['manufacturer'];
                        $po_amt_in_usd = $row_po['amt_in_usd'];
                        $po_sub_total_amt_in_usd = $row_po['sub_total_amt_in_usd'];
                        $po_sub_total = sprintf('%0.2f', $row_po["subtotal_price"]);
                        $po_b_chagred=$row_po["bank_charges"];
                        $po_amount_paid = sprintf('%0.2f', $row_po["amount_paind"]);
                        $po_proforma_id = $row_po["proforma_id"];
                        
                        $po_get_bpn = mysqli_query($conn, "SELECT * FROM `manufacturer` where id='$po_manufacturer_id'");
                        $po_get_bpn_row = mysqli_fetch_assoc($po_get_bpn);

                        $po_final_bill_pay_name = $po_get_bpn_row['manufacturer_display_name'];
                        $po_currency = $po_get_bpn_row['currency'];
                        
                        if($po_amt_in_usd==''){
                            $po_amount_paid=$po_amount_paid;
                        }else{
                            $po_amount_paid=$po_amt_in_usd;
                        }
                        $totalPoAmtPaid+=$po_amount_paid;
                        
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('A' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('B' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('C' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('D' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('E' . $rowCount, $po_id ." ".$po_proforma_id);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('F' . $rowCount, $po_order_date);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('G' . $rowCount, $po_final_bill_pay_name);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('H' . $rowCount, $po_amount_paid);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('I' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('J' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('K' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('L' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('M' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('N' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('O' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('P' . $rowCount, "");
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('Q' . $rowCount, "");
                    $rowCount = $rowCount + 1;
                    }
                }
                    
                $objPHPExcel->getActiveSheet()
                    ->getStyle('B' . $rowCount . ':I' . $rowCount)->getNumberFormat()
                    ->setFormatCode('0.00');
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('Q' . $rowCount, $status);
                $objPHPExcel->getActiveSheet()
                    ->getStyle('A' . $rowCount . ':Q' . $rowCount)->applyFromArray($border);
                $rowCount = $rowCount + 1;
            }
        }
    }
    
    $objPHPExcel->getActiveSheet()
                    ->setCellValue('G' . $rowCount, $inv_bank_chages);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('H' . $rowCount, $inv_other_chges);

 $objPHPExcel->getActiveSheet()
                    ->setCellValue('C' . $rowCount, $pro_totalAmt);
    ///////////////////////////////////////////
    ///////////// End If Proforma //////////////
    ///////////////////////////////////////////
    
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=in_out_report_from_" . $from. "_to_".$to . ".xls");
    
}
else if ($report_type == 'all_transaction'){
    
    //////////////////////////////////////
    //////////// start payment invoice/////////
    //////////////////////////////////////
    $objPHPExcel = new PHPExcel();

    // Set the active Excel worksheet to sheet 0
    $objPHPExcel->setActiveSheetIndex(0);
    $style = array(
        'font' => array(
            'bold' => true
        ) ,
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => '000000'
                )
            )
        )
    );
    $border = array(
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => '000000'
                )
            )
        )
    );
    $style1 = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )

    );
    $headerStyle = array(
        'font' => array(
            'bold' => true,
            'color' => array(
                'rgb' => '000000'
            ) ,
            'size' => 20,
            'name' => 'Verdana'
        )
    );
    $secondLineStyle = array(
        'font' => array(
            'bold' => true,
            'color' => array(
                'rgb' => '000000'
            ) ,
            'size' => 15,
            'name' => 'Verdana'
        )
    );
    $objPHPExcel->getDefaultStyle()
        ->applyFromArray(array(
        'borders' => array(
            'top' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'bottom' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'left' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            ) ,
            'right' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array(
                    'rgb' => PHPExcel_Style_Color::COLOR_WHITE
                )
            )
        )
    ));
    $objPHPExcel->getDefaultStyle()
        ->applyFromArray($style1);

    //apply the style on column A row 1 to Column B row 1
    $objPHPExcel->getActiveSheet()
        ->getStyle('A1:M1')
        ->applyFromArray($style);
    // $objPHPExcel->getActiveSheet()->getStyle('A4:I4')->applyFromArray($style);
    // $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->applyFromArray($headerStyle);
    // $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->applyFromArray($secondLineStyle);
    $objPHPExcel->getActiveSheet()
        ->setCellValue('A1', "Date");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('B1', "Customer Name");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('C1', "Amount");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('D1', "Tax Deduction");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('E1', "Invoice No.");
    $objPHPExcel->getActiveSheet()
        ->setCellValue('F1', "Transaction Mode");
    
  

    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('A')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('B')
        ->setWidth(25);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('C')
        ->setWidth(25);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('D')
        ->setWidth(40);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('E')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('F')
        ->setWidth(20);
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension('G')
        ->setWidth(20);


    $rowCount = 2;
    $i = 1;
            
          
            
    $sql = "SELECT payment.* ,contact.bill_display_name FROM  payment left join contact on payment.company_id = contact.id WHERE payment.payment_date >= '$fromdate' AND payment.payment_date <= '$todate' order by payment_date desc ";
    $result = $conn->query($sql) or die(mysqli_error($conn));
    if (mysqli_num_rows($result) > 0)
    {
        while ($row = mysqli_fetch_assoc($result))
        {
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('A' . $rowCount, $row["payment_date"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('B' . $rowCount, $row["bill_display_name"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('C' . $rowCount, $row["amount"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('D' . $rowCount, $row["tax_deduction"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('E' . $rowCount, $row["reference"]);
                $objPHPExcel->getActiveSheet()
                    ->setCellValue('F' . $rowCount, $row["mode"]);
                    
                    $rowCount ++;
                
        }
    }


    ///////////////////////////////////////////
    ///////////// End if  All payment //////////////
    ///////////////////////////////////////////
    
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=payment_report_from_" . $from. "_to_".$to . ".xls");
    
}
// header('Content-Type: application/vnd.ms-excel');
// header("Content-Disposition: attachment;filename=invoice_report_" . date("Y-m-d") . ".xls");
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
ob_end_clean();
$objWriter->save('php://output');

?>