source: pro-violet-viettel/sourcecode/application/modules/admin/controllers/report.php @ 818

<?php

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

class Report extends MX_Controller {

    const TOKENPW = 'violet';

    public function __construct() {
        parent::__construct();
        $this->load->helper('cookie');
    }

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

    public function getStatistics() {
        $this->load->model('collabolator_model');
        $data = $this->collabolator_model->getStatistics();
        return $this->load->view('report/statistics', $data, true);
    }


    public function getPaidlogs($filters = array()) {
        $this->load->helper('pagging');
        $this->load->model('paidlog_model');
        $data['current_page'] = $this->uri->segment(4, 1);
        $data['itemsoptions'] = array(10, 25, 50, 100);
        $data['perpage'] = 10;
        $data['keyword'] = "";
        $data['sorting_order'] = "sorting_desc";
        $data['sorting_field'] = "paid_id";

        if ($this->input->post('sorting_order')) {
            if ($this->input->post('sorting_order') != "sorting") {
                $data['sorting_order'] = $this->input->post('sorting_order');
                $data['sorting_field'] = $this->input->post('sorting_field');
            }
        }
        if ($this->input->post('items')) {
            $data['perpage'] = $this->input->post('items');
        }
        if ($this->input->post('keyword')) {
            $data['search_field'] = "";
            $data['keyword'] = $this->input->post('keyword');

            if (preg_match("/[0-9]/", $data['keyword'])) {
                $data['search_field'] = "cellphone";
            }
        }
                
                $data ['paid_types'] = array (
                                        0 => "Tất cả",
                                        1 => "SMS",
                                        2 => "Card",
                );
                $data ['paid_type'] = 0;
                if ($this->input->post ( 'paid_type' )) {
                        $data ['paid_type'] = ( int ) $this->input->post ( 'paid_type' );
                }
                
                $data['daterange_start'] = "";
                $data['daterange_end'] = "";
                if ($this->input->post('daterange')){
                        $daterange = explode(" - ", $this->input->post('daterange'));
                        $data['daterange_start'] = date('Y-m-d H:i:s', strtotime($daterange[0]));
                        $data['daterange_end'] = date('Y-m-d 23:59:59', strtotime($daterange[1]));
                }
        $data['start'] = ($data['current_page'] - 1) * $data['perpage'];
        $data['total'] = $this->paidlog_model->countPaidlog($data);
        $data['paidlogs'] = $this->paidlog_model->getPaidlogs($data)['paid_logs'];
                $data['total_amount'] = $this->paidlog_model->getPaidlogs($data)['total_amount'];
                
        $data['paging_url'] = base_url() . "/admin/doanh_thu_tong_hop/trang/";
        $data['num_links'] = 2;
        $data['paging'] = pagging($data);
        foreach ($data['paidlogs'] as $index => $paidlog) {
            $data['paidlogs'][$index]['username'] = @$this->get_fullname($paidlog['us_id']); 
        }
                
        if ($this->input->is_ajax_request()) {
            return $this->load->view('report/listview', $data);
        }
        return $this->load->view('report/listview', $data, true);
    }

    private function get_fullname($us_id) {
        $fullname="";
        $src = 'violet';
        $token = md5($us_id . self::TOKENPW);
        $this->load->model('user_model');
        $data = $this->user_model->get_user_info($src, $us_id, $token);
        parse_str($data,$data);
        if (isset($data['fullname']))
        {
            $fullname=$data['fullname'];
        }
        return $fullname;
    }

    public function test() {

        /*
          for ($i = 1; $i <= 5000; $i++) {
          $input['us_id'] = rand(1, 100);
          $input['paid_type'] = rand(1, 2);
          $input['amount'] = rand(3, 10) * 1000;
          $time = (rand((time() - (12 * 30 * 24 * 60 * 60)), time()));
          $input['paid_time'] = date("Y-m-d H:i:s", $time);
          $this->load->model('paidlog_model');
          $this->paidlog_model->insert($input);
          }

          die();
         */
        for ($i = 1; $i <= 200; $i++) {
            $input['us_id'] = $i;
            $input['cellphone'] = "01234567" . (100 + $i);
            $input['collaborator'] = rand(1, 100);
            $input['acc_balanced'] = rand(100000, 1000000);
            $input['expire_date'] = date("Y-m-d H:i:s", time());
            $time = (rand((time() - (12 * 30 * 24 * 60 * 60)), time()));
            $input['created_time'] = date("Y-m-d H:i:s", $time);
            $input['updated_time'] = date("Y-m-d H:i:s", $time);
            $this->load->model('user_model');
            $this->user_model->insert($input);
        }
    }
        
        public function export(){
        $this->load->model('paidlog_model');
                $data ['paid_types'] = array (
                                        0 => "Tất cả",
                                        1 => "SMS",
                                        2 => "Card",
                );
                $data ['paid_type'] = 0;
                if ($this->input->post ( 'paid_type' )) {
                        $data ['paid_type'] = ( int ) $this->input->post ( 'paid_type' );
                }
                
                $data['daterange_start'] = "";
                $data['daterange_end'] = "";
                if ($this->input->post('daterange')){
                        $daterange = explode(" - ", $this->input->post('daterange'));
                        $data['daterange_start'] = date('Y-m-d H:i:s', strtotime($daterange[0]));
                        $data['daterange_end'] = date('Y-m-d 23:59:59', strtotime($daterange[1]));
                }
        $data['total'] = $this->paidlog_model->countPaidlog($data);
        $data['paidlogs'] = $this->paidlog_model->getPaidlogs($data)['paid_logs'];
                $data['total_amount'] = $this->paidlog_model->getPaidlogs($data)['total_amount'];
                
        foreach ($data['paidlogs'] as $index => $paidlog) {
            $data['paidlogs'][$index]['username'] = $this->get_fullname($paidlog['us_id']); 
        }
                
                
        $this->load->library("PHPExcel");
        $sheet = $this->phpexcel->getActiveSheet();
                
        $sheet->setTitle("bao cao tong hop ");
        $sheet->getColumnDimension('A')->setWidth(20);
        $sheet->getColumnDimension('B')->setWidth(25);
        $sheet->getColumnDimension('C')->setWidth(15);
        $sheet->getColumnDimension('D')->setWidth(15);
        $sheet->getColumnDimension('E')->setWidth(25); 

                
        
                $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:E1');
                $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:E2');
                $sheet->getStyle('B2')->applyFromArray($style);
                
                
                $sheet->setCellValue('A4', "BÁO CÁO DOANH THU TỔNG HỢP");
                
                $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['daterange_start']);
                $sheet->setCellValue('C6', "Đến ngà y: ". $data['daterange_end']);
                $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:E4');
                $sheet->getStyle('A4')->applyFromArray($style);
                
                $sheet->setCellValue('A9', 'Số điện thoại');
                $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('B9', 'Họ tên');
                $sheet->setCellValue('C9', "Số tiền (VNĐ)");
                $sheet->setCellValue('D9', "SMS/Card");
                $sheet->setCellValue('E9', "Ngà y");            
                
                $sheet->getStyle('A9:E9')->applyFromArray($style);
                
                $sheet->setCellValue('A10', "Tổng");
                $sheet->mergeCells('A10:B10');
                $sheet->getStyle('A10:B10')->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('C10', $data['total_amount']);
                $sheet->getStyle('C10:E10')->applyFromArray($style);

                $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
                                        )
                        )
                );

        $dem=0;
        foreach ($data['paidlogs'] as $index => $paidlog) {
                        $type = "";
           // $sheet->setCellValue('A' . ($dem + 11), $paidlog['cellphone']);
                    if ($paidlog['paid_type'] == 1) { 
                                $type = "SMS";
                        }else {
                                $type = "Card";
                        }
                        $sheet->setCellValueExplicit('A' . ($dem + 11), $paidlog['cellphone'], PHPExcel_Cell_DataType::TYPE_STRING);
                        
            $sheet->setCellValue('B' . ($dem + 11), $paidlog['username']);
            $sheet->setCellValue('C' . ($dem + 11), $paidlog['amount']);
            $sheet->setCellValue('D' . ($dem + 11), $type);
                        $sheet->setCellValue('E' . ($dem + 11), $paidlog['paid_time']);
                        $sheet->getStyle('A'. ($dem + 11).':E'.($dem + 11))->applyFromArray($style);
            $dem++;
        }
        $writer = new PHPExcel_Writer_Excel5($this->phpexcel);
                header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="bao_cao_tong_hop_'.date('d-m-Y').'.xls"');
        $writer->save('php://output');
        }
}