<?php
error_reporting(1); //(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
session_start();
include("../../asset/theme/css/myfunc.php");
include("../../php_crud/my_class.php");
$obj = new my_class();

extract($_REQUEST);


// $well_list = '';
// $tbl_name = "GWM_TBL";
// $cols = "WELL_ID,OLD_ID";
// $where_cond = "UPAZILA_CODE='$upazila'";
// foreach ($obj->View_colmn_By_Cond_hydro($tbl_name, $cols, $where_cond) as $well_info) {
//         $well_list .= "'" . $well_info['WELL_ID'] . "',";
// }
// $well_list = substr($well_list, 0, -1);


// foreach ($obj->load_gwt_quality_data_count_hydro($from_year, $to_year) as $quality_info) {
//         //var_dump($quality_info);
//                 $district_name = $quality_info['DISTRICT_NAME'];
//                 $upazila_name = $quality_info['UPZL_NAME'];
//  }

if (file_exists('../../asset/custom/upload/av_gwt_pump_test_' . $upazila . '_' . $from_year . '_' . $to_year . '.xlsx')) {
        unlink('../../asset/custom/upload/av_gwt_pump_test_data' . $sub_div_id . '_' . $from_year . '_' . $to_year . '.xlsx');
}

require_once('../../asset/custom/PHPExcel_class/PHPExcel.php');
require_once('../../asset/custom/PHPExcel_class/PHPExcel/Calculation.php');
require_once('../../asset/custom/PHPExcel_class/PHPExcel/Cell.php');

$filename = '../../asset/custom/upload/av_gwt_pump_test_' . $upazila . '_' . $from_year . '_' . $to_year . '.xlsx';
try {
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()->setCreator("MokNathal")
                ->setLastModifiedBy("MokNathal")
                ->setTitle("STAFF REPORT")
                ->setSubject("STAFF REPORT")
                ->setDescription("STAFF REPORT")
                ->setKeywords("STAFF REPORT")
                ->setCategory("STAFF REPORT");

        $styleArray = array(
                'font'  => array(
                        'bold'  => true,
                        'color' => array('rgb' => 'FF0000')
                ),
                'fill' => array(
                        'type' => PHPExcel_Style_Fill::FILL_SOLID,
                        'color' => array('rgb' => 'FFFF00')
                )
        );
         //heading start
         $objPHPExcel->getActiveSheet()->mergeCells('A1:E7');
         $objPHPExcel->getActiveSheet()->getStyle('A1:E7')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
         $objPHPExcel->setActiveSheetIndex(0)->getCell('A1')->setValue( "Bangladesh Water Development Board \n Processing and Flood Forecasting Circle\n 72-Green Road, Dhaka-1215, Bangladesh\nPhone: +88-02-58151530\nEmail   : se.pffc@bwdb.gov.bd\nWeb      :  www.hydrology.bwdb.gov.bd");
         $objPHPExcel->getActiveSheet()->getStyle('A1:E7')->getAlignment()->setWrapText(true);

        //  $objPHPExcel->getActiveSheet()->mergeCells('A8:F8');
        //  $objPHPExcel->getActiveSheet()->getStyle('A8:F8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //  $objPHPExcel->setActiveSheetIndex(0)->getCell('A8')->setValue( "GW Quality Data  Availability for ".  $district_name. " and ". $upazila_name);
        //  $objPHPExcel->getActiveSheet()->getStyle('A8:F8')->getAlignment()->setWrapText(true);
 
         $objPHPExcel->getActiveSheet()->mergeCells('A8:B8');
         $objPHPExcel->getActiveSheet()->getStyle('A8:B8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
         $objPHPExcel->setActiveSheetIndex(0)->getCell('A8')->setValue( "From Year: ".$from_year);
         $objPHPExcel->getActiveSheet()->getStyle('A8:B8')->getAlignment()->setWrapText(true);
 
         $objPHPExcel->getActiveSheet()->mergeCells('C8:E8');
         $objPHPExcel->getActiveSheet()->getStyle('C8:E8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
         $objPHPExcel->setActiveSheetIndex(0)->getCell('C8')->setValue( "To Year: ".$to_year);
         $objPHPExcel->getActiveSheet()->getStyle('C8:E8')->getAlignment()->setWrapText(true);

       
         //headiing end
        //	$objPHPExcel->getActiveSheet()->mergeCells('A1:L1');
        $heading_row_num = 9;
        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A'.$heading_row_num, 'Well ID')
                // ->setCellValue('B'.$heading_row_num, 'Old ID')
                ->setCellValue('B'.$heading_row_num, 'District')
                ->setCellValue('C'.$heading_row_num, 'Upazila')
                ->setCellValue('D'.$heading_row_num, 'Year')
                ->setCellValue('E'.$heading_row_num, 'Count of Data');
               
        $objPHPExcel->getActiveSheet()->getStyle('A'.$heading_row_num)->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('B'.$heading_row_num)->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('C'.$heading_row_num)->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('D'.$heading_row_num)->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('E'.$heading_row_num)->getFont()->setBold(true);
        // $objPHPExcel->getActiveSheet()->getStyle('F'.$heading_row_num)->getFont()->setBold(true);

        $objPHPExcel->getActiveSheet()->getStyle('A'.$heading_row_num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('B'.$heading_row_num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('C'.$heading_row_num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('D'.$heading_row_num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('E'.$heading_row_num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        // $objPHPExcel->getActiveSheet()->getStyle('F'.$heading_row_num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
       
        $i = 10;
        
        foreach ($obj->load_pump_test_data_count($from_year,$to_year) as $daily_info) {
        // foreach ($obj->load_gwt_quality_data_count_hydro($from_year, $to_year) as $daily_info) {
                $well_id = $daily_info['WELL_ID'];
                // $old_id = $daily_info['OLD_ID'];
                $district_name = $daily_info['DISTRICT_NAME'];
                $upazila_name = $daily_info['UPZL_NAME'];
                $year = $daily_info['YEAR_VAL'];
                $count_of_data = $daily_info['DATA_COUNT'];
              
                $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue('A' . $i, $well_id)
                        // ->setCellValue('B' . $i, $old_id)
                        ->setCellValue('B' . $i, $district_name)
                        ->setCellValue('C' . $i, $upazila_name)
                        ->setCellValue('D' . $i, $year)
                        ->setCellValue('E' . $i, $count_of_data);
                        if ($count_of_data == 0) {
                                $objPHPExcel->getActiveSheet()->getStyle('E' . $i)->applyFromArray($styleArray);
                        }

                $i++;
        }
        foreach (range('A1', 'Q1') as $columnID) {
                $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
        }
        $objPHPExcel->getActiveSheet()->setTitle('GW Quality');
        $objPHPExcel->setActiveSheetIndex(0);
        ob_clean();

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('../../asset/custom/upload/av_gw_pump_test_data' . $upazila . '_' . $from_year . '_' . $to_year . '.xlsx');
        header('Location: ../../asset/custom/upload/av_gw_pump_test_data' . $upazila . '_' . $from_year . '_' . $to_year . '.xlsx');
} catch (Exception $e) {
        echo 'Caught exception: ', $e->getMessage(), "\n";
}
