<?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' );
	}
	
	/**
	 * 
	 * @param unknown $input
	 * @return Ambigous <multitype:number unknown , unknown, string>
	 */
	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";
		$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 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 );
		$dtns = $query->result_array ();
		
		// tinh doanh thu theo thang
		$from = "tblpaidlog.paid_time >= '" . $input ['year'] . "-" . $input ['month'] . "-01 00:00:00'";
		$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 00:00:00' AND tbluser.created_time <= '" . ($input ['year'] + 1) . "-01-01 23:59:59' GROUP BY tbluser.province";
		$sql = "SELECT *,count(us_id) as total FROM tbluser WHERE tbluser.created_time >= '" . $input ['year'] . "-01-01 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 );
		$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";
		$sql = "SELECT *,count(us_id) as total FROM tbluser WHERE created_time >= '" . $input ['year'] . "-" . $input ['month'] . "-" . '01 00:00:00' . "' AND created_time <= '" . $input ['to_year'] . "-" . $input ['to_month'] . "-" . $input ['to_date'] . " 23:59:59' 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;
		
		$totalTbng = 0;
		$totalTbth = 0;
		$totalTbn = 0;
		$totalDtng = 0;
		$totalDtth = 0;
		$totalDtn = 0;
		
		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'];
				}
			}
			
			$totalTbng = $totalTbng + $provinces [$index2] ['tbng'];
			$totalTbth = $totalTbth + $provinces [$index2] ['tbt'];
			$totalTbn = $totalTbn + $provinces [$index2] ['tbn'];
			
			$totalDtng = $totalDtng + $provinces [$index2] ['dtng'];
			$totalDtth = $totalDtth + $provinces [$index2] ['dtt'];
			$totalDtn = $totalDtn + $provinces [$index2] ['dtn'];
			
			$dem ++;
		}

		$data['provinces'] = $provinces;
		
		$data['totalTbng'] = $totalTbng;
		$data['totalTbth'] = $totalTbth;
		$data['totalTbn'] = $totalTbn;
		
		$data['totalDtng'] = $totalDtng;
		$data['totalDtth'] = $totalDtth;
		$data['totalDtn'] = $totalDtn;
		
		return $data;
	}
	
	/**
	 * 
	 * @param unknown $input
	 * @return Ambigous <multitype:, number, string>
	 */
	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 LEFT JOIN tblregisterpackagelog ON tbluser.cellphone = tblregisterpackagelog.username WHERE tbluser.updated_time >= '" . $input ['year'] . "-" . $input ['month'] . "-" . $input ['date'] . "' AND tbluser.updated_time <= '" . $input ['to_year'] . "-" . $input ['to_month'] . "-" . $input ['to_date'] . "' AND tblregisterpackagelog.created_time >= '" . $input ['year'] . "-" . $input ['month'] . "-" . $input ['date'] . "' AND tblregisterpackagelog.created_time <= '" . $input ['to_year'] . "-" . $input ['to_month'] . "-" . $input ['to_date'] . "' AND tblservicepackage.p_period > 0 ";
		$sql = "SELECT * FROM tbluser LEFT JOIN tblregisterpackagelog ON tbluser.cellphone = tblregisterpackagelog.username WHERE tbluser.updated_time >= '" . $input ['year'] . "-" . $input ['month'] . "-" . $input ['date'] . " 00:00:00' AND tbluser.updated_time <= '" . $input ['to_year'] . "-" . $input ['to_month'] . "-" . $input ['to_date'] . " 23:59:59' AND tblregisterpackagelog.created_time >= '" . $input ['year'] . "-" . $input ['month'] . "-" . $input ['date'] . " 00:00:00' AND tblregisterpackagelog.created_time <= '" . $input ['to_year'] . "-" . $input ['to_month'] . "-" . $input ['to_date'] . " 23:59:59'";
		$query = $this->db->query ( $sql );
		$users = $query->result_array ();
		//write_file('./log/sql.log', date("d-m-Y H:i:s").": ".$sql."\n", FOPEN_WRITE_CREATE);
		
		$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_code'] == $package ['p_code']) {
					$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 ++;
		}
		
		$totalByVIP = array();
		
		foreach ($packages as $index => $package){
			$totalByVIP[$package ['p_name']] = 0;
			foreach ( $provinces as $index=>$province){
				foreach ($province['packages'] as $index2 => $p){
					if ($index2 == $package['p_name'])
						$totalByVIP[$package ['p_name']] = $totalByVIP[$package ['p_name']] + $p['total'];
				}
			}
		}
		
		$totalVip = 0;
		
		foreach ($totalByVIP as $vipName=>$total){
			$totalVip = $totalVip + $total;
		}
		
		$totalByVIP['total'] = $totalVip;
		
		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;
		$data['totalVip'] = $totalByVIP;
		return $data;
	}
	
	/**
	 * 
	 * @param unknown $input
	 * @return multitype:multitype:unknown string Ambigous <Ambigous, multitype:number unknown , unknown, string>
	 */
	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;
	}
	
	/**
	 * 
	 * @param unknown $input
	 * @return multitype:
	 */
	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;
	}
}