1#@!#!123s

: / home / shubmkcj / urbane.createerp.com /
Filename : expense-report-download.php
back
<?php
    session_start();
    require "config.php";
    require("PHPExcel/Classes/PHPExcel.php");
    $source_id = "";
    if(isset($_SESSION["source_id"]) && $_SESSION["source_id"] != ""){
        $source_id = $_SESSION["source_id"];
    }
    else{
        die("Something went wrong..");
    }

    $fromdate = $_POST["fromdate"];
    $todate = $_POST["todate"];
    $from = date("Y-m-d", strtotime($fromdate));
    $to = date("Y-m-d", strtotime($todate));
    $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:I1')->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', "Amount Without Tax");
    $objPHPExcel->getActiveSheet()->setCellValue('D1', "IGST");
    $objPHPExcel->getActiveSheet()->setCellValue('E1', "CGST");
    $objPHPExcel->getActiveSheet()->setCellValue('F1', "SGST");
    $objPHPExcel->getActiveSheet()->setCellValue('G1', "Amount with Tax");
    $objPHPExcel->getActiveSheet()->setCellValue('H1', "Vendor Name");
    $objPHPExcel->getActiveSheet()->setCellValue('I1', "GSTIN");
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
    $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(40);
    $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
    
    $rowCount = 2; 
    $i =1;
    $sql = "SELECT * FROM expenses WHERE source_id = '$source_id' AND is_deleted != 'YES' AND (date_of_expense BETWEEN '$from' AND '$to')";
    $result = $conn->query($sql) or die(mysqli_error($conn));
    if(mysqli_num_rows($result) > 0){
        while($row = mysqli_fetch_assoc($result)){
            

            // $objPHPExcel->getActiveSheet()->setCellValue('A1', "EXISTING LAND USE OF ".$row["ULB"]." PLANNING AREA");
            // $objPHPExcel->getActiveSheet()->mergeCells('A1:N1');
            // $objPHPExcel->getActiveSheet()->setCellValue('A2', "MOHAL : ".$row["Mohal"]);
            // $objPHPExcel->getActiveSheet()->mergeCells('A2:D2');
            // $objPHPExcel->getActiveSheet()->setCellValue('M2', "Hadbast : ".$row["Hadbast"]);
            // $objPHPExcel->getActiveSheet()->mergeCells('M2:N2');
            $total = $row["amount"] + $row["igst"] +  $row["cgst"] + $row["sgst"];
            $objPHPExcel->getActiveSheet()->setCellValue('A'.$rowCount, date('d F Y', strtotime($row["date_of_expense"])));
            $objPHPExcel->getActiveSheet()->setCellValue('B'.$rowCount, $row["invoice_no"]);
            $objPHPExcel->getActiveSheet()->setCellValue('C'.$rowCount, $row["amount"]);
            $objPHPExcel->getActiveSheet()->setCellValue('D'.$rowCount, $row["igst"]);
            $objPHPExcel->getActiveSheet()->setCellValue('E'.$rowCount, $row["cgst"]);
            $objPHPExcel->getActiveSheet()->setCellValue('F'.$rowCount, $row["sgst"]);
            $objPHPExcel->getActiveSheet()->setCellValue('G'.$rowCount, $total);
            $objPHPExcel->getActiveSheet()->getStyle('C'.$rowCount.':G'.$rowCount)->getNumberFormat()->setFormatCode('0.00');
            $objPHPExcel->getActiveSheet()->setCellValue('H'.$rowCount, $row["vendor_name"]);
            $objPHPExcel->getActiveSheet()->setCellValue('I'.$rowCount, $row["vendor_gst"]);
            $objPHPExcel->getActiveSheet()->getStyle('A'.$rowCount.':I'.$rowCount)->applyFromArray($border);
            $rowCount = $rowCount + 1;
             
        }
    }
    header('Content-Type: application/vnd.ms-excel'); 
    header("Content-Disposition: attachment;filename=expense_report_".date("Y-m-d").".xls"); 
    header('Cache-Control: max-age=0'); 
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 
    $objWriter->save('php://output');


?>