source: pro-violet-viettel/sourcecode/application/modules/admin/controllers/reportpackage.php @ 883

<?php

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

class Reportpackage extends MX_Controller {

    const TOKENPW = 'violet';

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

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

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

        $this->load->model('reportmodel');
        $data['date']=date("d");
        $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('month')) {
                        $data['date']= $this->input->post('date');
            $data['month'] = $this->input->post('month');
            $data['year'] = $this->input->post('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');
        }
        $data = $this->getdays($data);

        $result = $this->reportmodel->getPackages($data);
        $data['provinces'] = $result['provinces'];
        $data['packages'] = $result['packages'];
                $data['totalVip'] = $result['totalVip'];
        if ($this->input->is_ajax_request()) {
            return $this->load->view('reportpackage/province', $data);
        }
        return $this->load->view('reportpackage/province', $data, true);
    }

    public function getdays($data) {
        $data['month_days'] = 1;
        if ("" . $data['year'] . "-" . $data['month'] . "" == date("Y-m")) {
            $data['month_days'] = date("d");
        } else {
            $data['month_days'] = cal_days_in_month(CAL_GREGORIAN, $data['month'], $data['year']); // 31  
        }
        $data['year_days'] = 1;
        
        if ($data['year'] == date("Y")) {
            $data['year_days'] = date('z') + 1;
        } else {
            $data['year_days'] = date("z", mktime(0, 0, 0, 12, 31, $data['year'])) + 1;
        }
        return $data;
    }

    public function export() {
        $this->load->model('reportmodel');
                //$data['date']=1;
        $data['date'] = $this->uri->segment(4);
                $data['month'] = $this->uri->segment(5);
        $data['year'] = $this->uri->segment(6);
                $data['to_date'] = $this->uri->segment(8);
                $data['to_month'] = $this->uri->segment(9);
        $data['to_year'] = $this->uri->segment(10);
        //$data = $this->getdays($data);
        $result = $this->reportmodel->getPackages($data);
        $data['provinces'] = $result['provinces'];
        $data['packages'] = $result['packages'];
                $data['totalVip'] = $result['totalVip'];

        $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:K1');
                $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:K2');
                $sheet->getStyle('B2')->applyFromArray($style);
                
                $style = array(
                        'font' => array('bold' => true,'size' => 12, 'name' =>'Cambria'),
                        'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                                                                'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
                                                                'wrap' => true )
                );
                $sheet->setCellValue('A4', "BÁO CÁO DOANH THU THEO TỈNH");
                $sheet->mergeCells('A4:L4');
                $sheet->getStyle('A4:L4')->applyFromArray($style);
                
                
                $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:C7')->applyFromArray($style);
                
                $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->setCellValue('A9', "STT");
        $sheet->setCellValue('B9', "Mã Tỉnh/Thà nh phố");
        $sheet->setCellValue('C9', "Tỉnh/Thà nh phố");
                $sheet->getStyle('A9:C9')->applyFromArray($style);
                
        $chars = array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L");
        $dem = 2;
        $stt = 1;
        foreach ($data['packages'] as $package) {
            $dem++;
            $sheet->setCellValue($chars[$dem] . "9", $package['p_name']);
            $dem++;
            $sheet->setCellValue($chars[$dem] . "9", "Tỉ lệ %");
        
            $stt++;
        }
        $dem++;
                $sheet->setCellValue($chars[$dem] . ($index + 9), "Tổng TB");
                $sheet->getStyle('A9:'.$chars[$dem] . ($index + 9))->applyFromArray($style);
                
                //$sheet->getStyle($chars[$dem].($index + 9))->applyFromArray($style);
                
                $sheet->setCellValue('A10', "Tổng");
                $sheet->mergeCells('A10:C10');
                $sheet->getStyle('A10:'.$chars[$dem].($index + 10))->applyFromArray($style);

                $dem = 2;
                
                foreach ($data['totalVip'] as $vipName=>$totalVip){
                        $dem++;
                        $sheet->setCellValue($chars[$dem] . '10', $totalVip);
                        $sheet->getStyle($chars[$dem] . '10')->applyFromArray($style);
                        $dem++;
                }
                
                //$sheet->getStyle('A10:'.$chars[$dem].($index + 10))->applyFromArray($style);
                
                $styleSTT = array(
                        'font' => array('size' => 11, 'name' =>'Cambria'),
                        'alignment' => array('bold' => true,
                                                                'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                                                                'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
                                                                'wrap' => true ),
                        'borders' =>    array(
                                'allborders' => array(
                                        'style' => \PHPExcel_Style_Border::BORDER_THIN
                                        )
                        )
                );
                
                $style = array(
                        'font' => array('size' => 11, 'name' =>'Cambria'),
                        'alignment' => array('bold' => true,
                                                                'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
                                                                'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
                                                                'wrap' => true ),
                        'borders' =>    array(
                                'allborders' => array(
                                        'style' => \PHPExcel_Style_Border::BORDER_THIN
                                        )
                        )
                );
                
                $stt=1;
        foreach ($data['provinces'] as $index => $provinces) {

            $sheet->setCellValue('A' . ($stt + 10), $provinces['stt']);
                        $sheet->getStyle('A'.($stt + 10))->applyFromArray($styleSTT);
            $sheet->setCellValue('B' . ($stt + 10), $index);
            $sheet->setCellValue('C' . ($stt + 10), $provinces['province']);
                        $sheet->getStyle('B'.($stt + 10).":C".($stt + 10))->applyFromArray($style);
            $dem = 2;
            foreach ($provinces['packages'] as $package) {
                $dem++;
                $sheet->setCellValue($chars[$dem] . ($stt + 10), $package['total']);
                                $sheet->getStyle($chars[$dem] . ($stt + 10))->applyFromArray($style);
                $dem++;
                $sheet->setCellValue($chars[$dem] . ($stt + 10), $package['percent']);
                                $sheet->getStyle($chars[$dem] . ($stt + 10))->applyFromArray($style);
            }
            $dem++;
            
            $sheet->setCellValue($chars[$dem] . ($stt + 10), $provinces['total']);
                        $sheet->getStyle($chars[$dem] . ($stt + 10))->applyFromArray($style);
                        
            $stt++;
        }
                
                
        $writer = new PHPExcel_Writer_Excel5($this->phpexcel);
        header('Content-Disposition: attachment; filename="doanh_thu_'. $data['date'] . "_" . $data['month'] . "_" . $data['year']  .'-'. $data['to_date'] . "_" . $data['to_month'] . "_" . $data['to_year'] . '.xls"');
        $writer->save('php://output');
    }

}