<?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) {

        $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 tblpaidlog.paid_time >'" . $input['year'] . "-" . $input['month'] . "-01'  GROUP BY tbluser.province";
        $query = $this->db->query($sql);
        $dtts = $query->result_array();
        // tinh doanh thu trong ngay hien tai
        $sql = "SELECT *,SUM(amount) as total FROM tblpaidlog LEFT JOIN tbluser ON tbluser.us_id=tblpaidlog.us_id WHERE tblpaidlog.paid_time > '" . date('Y') . "-" . date('m') . "-".date('d')." 00:00:00' AND  tblpaidlog.paid_time <'" . date('Y') . "-" . date('m') . "-".date('d')." 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 tbluser.created_time >'" . $input['year'] . "-" . $input['month'] . "-01'  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 >'" . date('Y') . "-" . date('m') . "-".date('d')." 00:00:00' AND tbluser.created_time < '" . date('Y') . "-" . date('m') . "-".date('d')." 23:59:59'  GROUP BY tbluser.province";
        $query = $this->db->query($sql);
        $tbng = $query->result_array(); 
        

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


            foreach ($tbns as $data) {
                if ($province == $data['province']) {
                    $provinces[$index2]['tbn'] = $data['total'];
                }
            }
            foreach ($tbng as $data) {
                if ($province == $data['province']) {
                    $provinces[$index2]['tbng'] = $data['total'];
                }
            }
            foreach ($tbts as $data) {
                if ($province == $data['province']) {
                    $provinces[$index2]['tbt'] = $data['total'];
                }
            }
        }
       
        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'] . "-01' AND tbluser.created_time <'" . $input['year'] . "-" . $input['month'] . "-31' 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);
        }

        foreach ($provinces as $index => $province) {
            $provinces[$index]['stt'] = $index + 1;
            $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 ($province['province'] == $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;
                    }
                }
            }
        }
        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['month']=1;
        //$input['year']=2014;
        $data=$this->getProvinces($input);
        return $data;
    }
    public function exportPackage($input)
    {
       // $input['month']=1;
       // $input['year']=2014;
        $data=$this->getPackages($input);
        return $data;
    }

}