<?php

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

class Reportmodel extends MY_Model {

    protected $table_name = 'tblpaidlog';
    protected $id_name = 'id';

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

    function getProvinces($input) {

        $from="tblpaidlog.paid_time >= '" . $input['year'] . "-" . $input['month'] . "-" . $input['date'] . " 00:00:00'";
        $to="tblpaidlog.paid_time <= '" . $input['to_year'] . "-" . $input['to_month'] . "-" . $input['to_date'] . " 23:59:59'";
        
        $provinces = lang('_PROVINCES_');
        // Tinh doanh thu theo tinh theo nam
        $sql = "SELECT *,SUM(amount) as total FROM tblpaidlog LEFT JOIN tbluser ON tbluser.us_id=tblpaidlog.us_id WHERE tblpaidlog.paid_time >= '" . $input['year'] . "-01-01' AND tblpaidlog.paid_time <= '" . ($input['year'] + 1) . "-01-01' GROUP BY tbluser.province";
        $query = $this->db->query($sql);
        $dtns = $query->result_array();
        
        // tinh doanh thu theo thang
        $sql = "SELECT *,SUM(amount) as total FROM tblpaidlog LEFT JOIN tbluser ON tbluser.us_id=tblpaidlog.us_id WHERE $from AND $to  GROUP BY tbluser.province";
        $query = $this->db->query($sql);
        $dtts = $query->result_array();
        
        // tinh doanh thu trong ngay hien tai
        if (isset($input['to_month']))
        {
        $sql = "SELECT *,SUM(amount) as total FROM tblpaidlog LEFT JOIN tbluser ON tbluser.us_id=tblpaidlog.us_id WHERE tblpaidlog.paid_time > '" . $input['to_year'] . "-" . $input['to_month'] . "-".$input['to_date']." 00:00:00' AND  tblpaidlog.paid_time <'" . $input['to_year'] . "-" . $input['to_month'] . "-".$input['to_date']." 23:59:59'  GROUP BY tbluser.province";
        }
        $query = $this->db->query($sql);
        $dtng = $query->result_array();

        // Tinh thue bao theo tinh theo nam
        $sql = "SELECT *,count(us_id) as total FROM tbluser WHERE tbluser.created_time >'" . $input['year'] . "-01-01' AND tbluser.created_time <'" . ($input['year'] + 1) . "-01-01' GROUP BY tbluser.province";
        $query = $this->db->query($sql);
        $tbns = $query->result_array();

        // tinh thue bao theo thang
        $sql = "SELECT *,count(us_id) as total FROM tbluser WHERE $from AND  $to GROUP BY tbluser.province";
        $query = $this->db->query($sql);
        $tbts = $query->result_array();
        
         // tinh thue bao theo ngay
        $sql = "SELECT *,count(us_id) as total FROM tbluser WHERE tbluser.created_time >'" . $input['to_year'] . "-" . $input['to_month'] . "-".$input['to_date']." 00:00:00' AND tbluser.created_time < '" . $input['to_year'] . "-" . $input['to_month'] . "-".$input['to_date']." 23:59:59'  GROUP BY tbluser.province";
        
        $query = $this->db->query($sql);
        $tbng = $query->result_array(); 
        

        $dem=1;
        foreach ($provinces as $index2 => $province) {
            $provinces[$index2] = array("stt" => ($dem), "province" => $province, "dtn" => 0, "dtt" => 0, "dtng" => 0, "tbn" => 0, "tbt" => 0, "tbng" => 0);
            foreach ($dtns as $data) {
                if ($index2 == $data['province']) {
                    $provinces[$index2]['dtn'] = $data['total'];
                }
            } 
            foreach ($dtng as $data) {
                if ($index2 == $data['province']) {
                    $provinces[$index2]['dtng'] = $data['total'];
                }
            }
            foreach ($dtts as $data) {
                if ($index2 == $data['province']) {
                    $provinces[$index2]['dtt'] = $data['total'];
                    
                }
            }

            foreach ($tbns as $data) {
                if ($index2 == $data['province']) {
                    $provinces[$index2]['tbn'] = $data['total'];
                }
            }
            foreach ($tbng as $data) {
                if ($index2 == $data['province']) {
                    $provinces[$index2]['tbng'] = $data['total'];
                }
            }
            foreach ($tbts as $data) {
                if ($index2 == $data['province']) {
                    $provinces[$index2]['tbt'] = $data['total'];
                }
            }
            $dem++;
        }
       
        return $provinces;
    }

    function getPackages($input) {

        $provinces = lang('_PROVINCES_');
        // tinh thue bao theo thang
        $sql = "SELECT * FROM tbluser LEFT JOIN tblservicepackage ON tbluser.p_id=tblservicepackage.p_id  WHERE tbluser.created_time >= '" . $input['year'] . "-" . $input['month'] . "-" . $input['date'] . "' AND tbluser.created_time <= '" . $input['to_year'] . "-" . $input['to_month'] . "-" . $input['to_date'] . "' AND tblservicepackage.p_period > 0 ";
        $query = $this->db->query($sql);
        $users = $query->result_array();


        $sql = "SELECT * FROM tblservicepackage WHERE p_period>0";
        $query = $this->db->query($sql);
        $packages = $query->result_array();
        foreach ($packages as $index => $package) {
            $packages[$index]['users'] = array();
            foreach ($users as $user) {
                if ($user['p_id'] == $package['p_id']) {
                    $packages[$index]['users'][] = $user;
                }
            }
        }

        foreach ($provinces as $index => $province) {
            $provinces[$index] = array("province" => $province);
        }
        $dem=1;
        foreach ($provinces as $index => $province) {
            $provinces[$index]['stt'] = $dem;
            $provinces[$index]['total'] = 0;
            $totaluser = 0;
            foreach ($packages as $index2 => $package) {
                $provinces[$index]['packages'][$package['p_name']]['users'] = array();
                $provinces[$index]['packages'][$package['p_name']]['total'] = 0;
                $total = 0;
                foreach ($package['users'] as $user) {
                    if ($index == $user['province']) {
                        $total = $total + 1;
                        $totaluser = $totaluser + 1;
                        $provinces[$index]['total'] = $totaluser;
                        $provinces[$index]['packages'][$package['p_name']]['total'] = $total;

                        $provinces[$index]['packages'][$package['p_name']]['users'][] = $user;
                    }
                }
            }
            $dem++;
        }
        foreach ($provinces as $index => $province) {
            foreach ($packages as $index2 => $package) {
                $provinces[$index]['packages'][$package['p_name']]['percent'] = 0;
                if ($provinces[$index]['packages'][$package['p_name']]['total'] > 0 && $province['total'] > 0) {
                    $provinces[$index]['packages'][$package['p_name']]['percent'] = round($provinces[$index]['packages'][$package['p_name']]['total'] / $province['total'], 2) * 100;
                }
            }
        }

        $data['packages'] = $packages;
        $data['provinces'] = $provinces;
        return $data;
    }
    public function exportProvince($input)
    {   
        //$input['date']=1;
        //$input['month']=1;
        //$input['year']=2014;
        //$input['to_month']=1;
        //$input['to_year']=2015;
        //$input['to_date']=15;
        if (isset($input['to_month']))
        {
            $date=$input['to_year'].$input['to_month'].$input['to_date'];
        }
        else {
            $date=date("Ymd");
        }
        $provinces=array();
        $data=$this->getProvinces($input);
        foreach ($data as $index=>$province)
        {
            $provinces[]=array("stt"=>$province['stt'],"ngay"=>$date,"ma"=>$index,"tinh"=>$province['province'],"thue_bao_ngay"=>$province['tbng'],"thue_bao_thang"=>$province['tbt'],"thue_bao_nam"=>$province['tbn'],"doanh_thu_ngay"=>$province['dtng'],"doanh_thu_thang"=>$province['dtt'],"doanh_thu_nam"=>$province['dtn']);
        }
        return $provinces;
    }
    public function exportPackage($input)
    {
       // $input['date']=1;
        //$input['month']=1;
      //  $input['year']=2014;
      // $input['to_month']=1;
       // $input['to_year']=2015;
       // $input['to_date']=15;
        if (isset($input['month']))
        {
            $date=$input['year'].$input['month'];
        }
        else {
            $date=date("Ym");
        }
        $result=array();
        $data=$this->getPackages($input);
        $data=$data['provinces'];
        foreach ($data as $index=>$province)
        {
                $package=array();
                foreach ($province['packages'] as $index2=>$packages)
                {
                    $package=  array_merge($package,array("tong_so_".$index2=>$packages['total'],"ti_le_".$index2=>$packages['percent']));
                }
                $tong=array("tong_so_tb"=>$province['total']);
                $province=array("stt"=>$province['stt'],"ngay"=>$date,"ma"=>$index,"tinh"=>$province['province']);
                $provinces[]= array_merge($province,$package,$tong);
        }
        return $provinces;
    }

}