<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Reportprovince extends MX_Controller {

    const TOKENPW = 'violet';

    public function __construct() {
        parent::__construct();
        $this->load->helper('cookie');
        $this->load->helper('language');
        $this->lang->load('messages', 'message');
        $this->load->model('reportmodel');
    }

    public function index() {
        $data = array();
        $admin_info = $this->session->userdata('adminInfo');
        if ($admin_info) {
            $data['content'] = $this->getProvince();
            $this->load->view('reportprovince/index', $data);
        } else {
            $this->load->view('login');
        }
    }

    public function getProvince($filters = array()) {

        $this->load->model('reportmodel');
        $data['date'] = 1;
        $data['month'] = date("m");
        $data['year'] = date("Y");
        $data['to_date'] = date("d");
        $data['to_month'] = date("m");
        $data['to_year'] = date("Y");
        if ($this->input->post()) {
            //$data['month'] = $this->input->post('month');
            //$data['year'] = $this->input->post('year');
			$data['month'] = $this->input->post('to_month');
            $data['year'] = $this->input->post('to_year');
			$data['to_date'] = $this->input->post('to_date');
            $data['to_month'] = $this->input->post('to_month');
            $data['to_year'] = $this->input->post('to_year');
        }
		$result = $this->reportmodel->getProvinces($data);
        $data['provinces'] =  $result['provinces'];
		$data['totalTbng'] = $result['totalTbng'];
		$data['totalTbth'] = $result['totalTbth'];
		$data['totalTbn'] = $result['totalTbn'];
		
		$data['totalDtng'] = $result['totalDtng'];
		$data['totalDtth'] = $result['totalDtth'];
		$data['totalDtn'] = $result['totalDtn'];
		
        if ($this->input->is_ajax_request()) {
            return $this->load->view('reportprovince/province', $data);
        }
        return $this->load->view('reportprovince/province', $data, true);
    }

 

    public function export() {
        $this->load->model('reportmodel');
        $data['month'] = $this->uri->segment(4);
        $data['year'] = $this->uri->segment(5);
       
        $data['provinces'] = $this->reportmodel->getProvinces($data);

        $this->load->library("PHPExcel");
        $sheet = $this->phpexcel->getActiveSheet();
        $sheet->setTitle("bao cao thue bao " . $data['month'] . " - " . $data['year']);
        $sheet->getColumnDimension('A')->setWidth(10);
        $sheet->getColumnDimension('B')->setWidth(25);
        $sheet->getColumnDimension('C')->setWidth(25);
        $sheet->getColumnDimension('D')->setWidth(15);
        $sheet->getColumnDimension('E')->setWidth(15);
        $sheet->getColumnDimension('F')->setWidth(15);
        $sheet->getColumnDimension('G')->setWidth(15);
        $sheet->getColumnDimension('H')->setWidth(15); 
        $sheet->getColumnDimension('I')->setWidth(15);
        $sheet->getColumnDimension('J')->setWidth(15);
        $sheet->setCellValue('A1', "Báo cáo thuê bao theo tỉnh");
        $sheet->setCellValue('A2', "Tháng: " . $data['month'] . " - " . $data['year']);
        //$sheet->setCellValue('C2', "Ngày: ".date('d')." - ".date('m')." - ". date('Y').""); 
        $sheet->setCellValue('A4', "STT");
        $sheet->setCellValue('B4', "Ngày");
        $sheet->setCellValue('C4', "Mã Tỉnh/Thành Phố");
        $sheet->setCellValue('D4', "Tỉnh/Thành phố");
        $sheet->setCellValue('E4', "TB Ngày");
        $sheet->setCellValue('F4', "Lũy kế TB tháng");
        $sheet->setCellValue('G4', "Tổng TB Ngày");
        $sheet->setCellValue('H4', "DT Ngày");
        $sheet->setCellValue('I4', "Lũy kế DT tháng");
        $sheet->setCellValue('J4', "Tổng DT năm");
        $dem=0;
        foreach ($data['provinces'] as $index => $provinces) {
            $sheet->setCellValue('A' . ($dem + 5), $provinces['stt']);
            $sheet->setCellValue('B' . ($dem + 5), date('d')." - ".date('m')." - ". date('Y'));
            $sheet->setCellValue('C' . ($dem + 5), $index);
            $sheet->setCellValue('D' . ($dem + 5), $provinces['province']);
            $sheet->setCellValue('E' . ($dem + 5), $provinces['tbng']);
            $sheet->setCellValue('F' . ($dem + 5), $provinces['tbt']);
            $sheet->setCellValue('G' . ($dem + 5), $provinces['tbn']);
            $sheet->setCellValue('H' . ($dem + 5), $provinces['dtng']);
            $sheet->setCellValue('I' . ($dem + 5), $provinces['dtt']);
            $sheet->setCellValue('J' . ($dem + 5), $provinces['dtn']);
            $dem++;
        }
        $writer = new PHPExcel_Writer_Excel5($this->phpexcel);
		header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="thue_bao_' . $data['month'] . "_" . $data['year'] . '.xls"');
        $writer->save('php://output');
    }
    public function test()
    {
        $input['date']=1;
        $input['month']=1;
        $input['year']=2014;
        $input['to_month']=1;
        $input['to_year']=2015;
        $input['to_date']=15;
        $data=$this->reportmodel->exportPackage($input);
        echo "<pre>";
        print_r ($data);
        echo "</pre>";
    }
	
	public function exportProvince(){
		$this->load->model('reportmodel');
		$data['date'] = 1;
        $data['month'] = $this->uri->segment(5);
        $data['year'] = $this->uri->segment(6);
        $data['to_date'] = $this->uri->segment(4);
        $data['to_month'] = date("m");
        $data['to_year'] = date("Y");
		/*
        if ($this->input->post()) {
			//$data['date'] = $this->input->post('date');
            $data['month'] = $this->input->post('to_month');
            $data['year'] = $this->input->post('to_year');
			$data['to_date'] = $this->input->post('to_date');
            $data['to_month'] = $this->input->post('to_month');
            $data['to_year'] = $this->input->post('to_year');
        }*/
		
        $result = $this->reportmodel->getProvinces($data);
        $data['provinces'] =  $result['provinces'];
		
		$data['totalTbng'] = $result['totalTbng'];
		$data['totalTbth'] = $result['totalTbth'];
		$data['totalTbn'] = $result['totalTbn'];
		
		$data['totalDtng'] = $result['totalDtng'];
		$data['totalDtth'] = $result['totalDtth'];
		$data['totalDtn'] = $result['totalDtn'];

        $this->load->library("PHPExcel");
        $sheet = $this->phpexcel->getActiveSheet();
		
        $sheet->setTitle("bao cao thue bao " . $data['month'] . " - " . $data['year']);
        $sheet->getColumnDimension('A')->setWidth(10);
        $sheet->getColumnDimension('B')->setWidth(25);
        $sheet->getColumnDimension('C')->setWidth(25);
        $sheet->getColumnDimension('D')->setWidth(15);
        $sheet->getColumnDimension('E')->setWidth(15);
        $sheet->getColumnDimension('F')->setWidth(15);
        $sheet->getColumnDimension('G')->setWidth(15);
        $sheet->getColumnDimension('H')->setWidth(15); 
        $sheet->getColumnDimension('I')->setWidth(15);
        $sheet->getColumnDimension('J')->setWidth(15);
		
		
		$sheet->setCellValue('B1', "TẬP ĐOÀN VIỄN THÔNG QUÂN ĐỘI");
		$style = array(
			'font' => array('bold' => true, 'size' => 12, 'name' =>'Cambria', 'color' => array('rgb' => '0070bf')),
			'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
								'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
								'wrap' => true )
		);
		$sheet->mergeCells('B1:I1');
		$sheet->getStyle('B1')->applyFromArray($style);
		
		$sheet->setCellValue('B2', "TỔNG CÔNG TY VIỄN THÔNG VIETTEL");
		$style = array(
			'font' => array('size' => 12, 'name' =>'Cambria', 'color' => array('rgb' => '0070bf')),
			'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
								'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
								'wrap' => true )
		);
		$sheet->mergeCells('B2:I2');
		$sheet->getStyle('B2')->applyFromArray($style);
		
		
		$sheet->setCellValue('A4', "BÁO CÁO TỔNG HỢP PHÁT TRIỂN THUÊ BAO VÀ DOANH THU DỊCH VỤ SBG THEO TỈNH");
		
		$style = array(
			'font' => array('size' => 11, 'name' =>'Cambria'),
			'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
								'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
								'wrap' => true ),
			'borders' =>	array(
				'allborders' => array(
					'style' => \PHPExcel_Style_Border::BORDER_THIN
					)
			)
		);
		
		$sheet->setCellValue('B6', "Từ ngày: ".$data['date']."/".$data['month']."/".$data['year']);
		$sheet->setCellValue('C6', "Đến ngày: ".$data['to_date']."/".$data['to_month']."/".$data['to_year']);
		$sheet->setCellValue('B7', "Ngày kết xuất: ".date("d")."/".date("m")."/".date("Y"));
		$sheet->setCellValue('C7', "Nhân viên kết xuất: ");
		
		$sheet->getStyle('B6')->applyFromArray($style);
		$sheet->getStyle('C6')->applyFromArray($style);
		$sheet->getStyle('B7')->applyFromArray($style);
		$sheet->getStyle('C7')->applyFromArray($style);
		
		
		$style = array(
			'font' => array('bold' => true, 'size' => 14, 'name' =>'Cambria'),
			'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
								'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
								'wrap' => true )
		);
		$sheet->mergeCells('A4:I4');
		$sheet->getStyle('A4')->applyFromArray($style);
		
		$sheet->setCellValue('A9', 'STT');
		$style = array(
			'font' => array('bold' => true, 'size' => 11, 'name' =>'Cambria'),
			'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
								'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
								'wrap' => true),
			'borders' =>	array(
				'allborders' => array(
					'style' => \PHPExcel_Style_Border::BORDER_THIN
					)
			)
		);
		$sheet->mergeCells('A9:A10');
		$sheet->getStyle('A9')->applyFromArray($style);
		
		$sheet->setCellValue('B9', 'Mã Tỉnh/Thành Phố');
		$sheet->mergeCells('B9:B10');
		$sheet->getStyle('B9:B10')->applyFromArray($style);
		
		$sheet->setCellValue('C9', "Tỉnh/Thành phố");
		$sheet->mergeCells('C9:C10');
		$sheet->getStyle('C9:C10')->applyFromArray($style);
		
		$sheet->setCellValue('D9', "THUÊ BAO");
		$sheet->mergeCells('D9:F9');
		$sheet->getStyle('D9:F9')->applyFromArray($style);
		
		$sheet->setCellValue('G9', "DOANH THU");
		$sheet->mergeCells('G9:I9');
		$sheet->getStyle('G9:I9')->applyFromArray($style);
		
		$sheet->setCellValue('A11', "Tổng");
		$sheet->mergeCells('A11:C11');
		$sheet->getStyle('A11:C11')->applyFromArray($style);
		
		$style = array(
			'font' => array('bold' => true, 'size' => 11, 'name' =>'Cambria'),
			'alignment' => array('vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER),
			'borders' =>	array(
				'allborders' => array(
					'style' => \PHPExcel_Style_Border::BORDER_THIN
					)
			)
		);
		
		$sheet->setCellValue('D11', $data['totalTbng']);
		$sheet->setCellValue('E11', $data['totalTbth']);
		$sheet->setCellValue('F11', $data['totalTbn']);
		$sheet->setCellValue('G11', $data['totalDtng']);
		$sheet->setCellValue('H11', $data['totalDtth']);	
		$sheet->setCellValue('I11', $data['totalDtn']);
	
		$sheet->getStyle('D11')->applyFromArray($style);
		$sheet->getStyle('E11')->applyFromArray($style);
		$sheet->getStyle('F11')->applyFromArray($style);
		$sheet->getStyle('G11')->applyFromArray($style);
		$sheet->getStyle('H11')->applyFromArray($style);
		$sheet->getStyle('I11')->applyFromArray($style);
		
		$styleSTT = array(
			'font' => array('size' => 11, 'name' =>'Cambria'),
			'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
								'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER),
			'borders' =>	array(
				'allborders' => array(
					'style' => \PHPExcel_Style_Border::BORDER_THIN
					)
			)
		);
		
		$style = array(
			'font' => array('size' => 11, 'name' =>'Cambria'),
			'alignment' => array('vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER),
			'borders' =>	array(
				'allborders' => array(
					'style' => \PHPExcel_Style_Border::BORDER_THIN
					)
			)
		);
		
		$sheet->setCellValue('D10', "Thực hiện Ngày");
		$sheet->getStyle('D10')->applyFromArray($style);
		$sheet->setCellValue('E10', "Lũy kế Tháng");
		$sheet->getStyle('E10')->applyFromArray($style);
		$sheet->setCellValue('F10', "Lũy kế Năm");
		$sheet->getStyle('F10')->applyFromArray($style);
		$sheet->setCellValue('G10', "Thực hiện Ngày");
		$sheet->getStyle('G10')->applyFromArray($style);
		$sheet->setCellValue('H10', "Lũy kế Tháng");
		$sheet->getStyle('H10')->applyFromArray($style);
		$sheet->setCellValue('I10', "Lũy kế Năm");
		$sheet->getStyle('I10')->applyFromArray($style);
		
		/*
        $sheet->setCellValue('A1', "Báo cáo thuê bao theo tỉnh");
        $sheet->setCellValue('A2', "Tháng: " . $data['month'] . " - " . $data['year']);
        //$sheet->setCellValue('C2', "Ngày: ".date('d')." - ".date('m')." - ". date('Y').""); 
        $sheet->setCellValue('A4', "STT");
        $sheet->setCellValue('B4', "Ngày");
        $sheet->setCellValue('C4', "Mã Tỉnh/Thành Phố");
        $sheet->setCellValue('D4', "Tỉnh/Thành phố");
        $sheet->setCellValue('E4', "TB Ngày");
        $sheet->setCellValue('F4', "Lũy kế TB tháng");
        $sheet->setCellValue('G4', "Tổng TB Ngày");
        $sheet->setCellValue('H4', "DT Ngày");
        $sheet->setCellValue('I4', "Lũy kế DT tháng");
        $sheet->setCellValue('J4', "Tổng DT năm");
		*/
        $dem=0;
        foreach ($data['provinces'] as $index => $provinces) {
            $sheet->setCellValue('A' . ($dem + 12), $provinces['stt']);
            $sheet->setCellValue('B' . ($dem + 12), $index);
            $sheet->setCellValue('C' . ($dem + 12), $provinces['province']);
            $sheet->setCellValue('D' . ($dem + 12), $provinces['tbng']);
            $sheet->setCellValue('E' . ($dem + 12), $provinces['tbt']);
            $sheet->setCellValue('F' . ($dem + 12), $provinces['tbn']);
            $sheet->setCellValue('G' . ($dem + 12), $provinces['dtng']);
            $sheet->setCellValue('H' . ($dem + 12), $provinces['dtt']);
            $sheet->setCellValue('I' . ($dem + 12), $provinces['dtn']);
			
			$sheet->getStyle('A'. ($dem + 12))->applyFromArray($styleSTT);
			$sheet->getStyle('B'. ($dem + 12))->applyFromArray($style);
			$sheet->getStyle('C'. ($dem + 12))->applyFromArray($style);
			$sheet->getStyle('D'. ($dem + 12))->applyFromArray($style);
			$sheet->getStyle('E'. ($dem + 12))->applyFromArray($style);
			$sheet->getStyle('F'. ($dem + 12))->applyFromArray($style);
			$sheet->getStyle('G'. ($dem + 12))->applyFromArray($style);
			$sheet->getStyle('H'. ($dem + 12))->applyFromArray($style);
			$sheet->getStyle('I'. ($dem + 12))->applyFromArray($style);
			
            $dem++;
        }
		
        $writer = new PHPExcel_Writer_Excel5($this->phpexcel);
		header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="thue_bao_va_doanh_thu_theo_tinh_' . $data["to_date"].'_'. $data['to_month'].'_'. $data['to_year'] . '.xls"');
        $writer->save('php://output');
	}
}