Context Navigation

reportmodel.php

Last change on this file was 780, checked in by dungnv, 10 years ago

File size: 8.7 KB

Rev	Line
[780]	1	<?php
	2	if (! defined ( 'BASEPATH' ))
	3	exit ( 'No direct script access allowed' );
	4
	5	class Reportmodel extends MY_Model {
	6
	7	protected $table_name = 'tblpaidlog';
	8	protected $id_name = 'id';
	9
	10	/**
	11	*
	12	*/
	13	function __construct() {
	14	parent::__construct ();
	15	$this->load->helper ( 'language' );
	16	$this->lang->load ( 'messages', 'message' );
	17	}
	18
	19	/**
	20	*
	21	* @param unknown $input
	22	* @return Ambigous <multitype:number unknown , unknown, string>
	23	*/
	24	function getProvinces($input) {
	25	$from = "tblpaidlog.paid_time >= '" . $input ['year'] . "-" . $input ['month'] . "-" . $input ['date'] . " 00:00:00'";
	26	$to = "tblpaidlog.paid_time <= '" . $input ['to_year'] . "-" . $input ['to_month'] . "-" . $input ['to_date'] . " 23:59:59'";
	27
	28	$provinces = lang ( '_PROVINCES_' );
	29	// Tinh doanh thu theo tinh theo nam
	30	$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";
	31	$query = $this->db->query ( $sql );
	32	$dtns = $query->result_array ();
	33
	34	// tinh doanh thu theo thang
	35	$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";
	36	$query = $this->db->query ( $sql );
	37	$dtts = $query->result_array ();
	38
	39	// tinh doanh thu trong ngay hien tai
	40	if (isset ( $input ['to_month'] )) {
	41	$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";
	42	}
	43	$query = $this->db->query ( $sql );
	44	$dtng = $query->result_array ();
	45
	46	// Tinh thue bao theo tinh theo nam
	47	$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";
	48	$query = $this->db->query ( $sql );
	49	$tbns = $query->result_array ();
	50
	51	// tinh thue bao theo thang
	52	$sql = "SELECT *,count(us_id) as total FROM tbluser WHERE $from AND $to GROUP BY tbluser.province";
	53	$query = $this->db->query ( $sql );
	54	$tbts = $query->result_array ();
	55
	56	// tinh thue bao theo ngay
	57	$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";
	58
	59	$query = $this->db->query ( $sql );
	60	$tbng = $query->result_array ();
	61
	62	$dem = 1;
	63
	64	foreach ( $provinces as $index2 => $province ) {
	65	$provinces [$index2] = array (
	66	"stt" => ($dem),
	67	"province" => $province,
	68	"dtn" => 0,
	69	"dtt" => 0,
	70	"dtng" => 0,
	71	"tbn" => 0,
	72	"tbt" => 0,
	73	"tbng" => 0
	74	);
	75	foreach ( $dtns as $data ) {
	76	if ($index2 == $data ['province']) {
	77	$provinces [$index2] ['dtn'] = $data ['total'];
	78	}
	79	}
	80	foreach ( $dtng as $data ) {
	81	if ($index2 == $data ['province']) {
	82	$provinces [$index2] ['dtng'] = $data ['total'];
	83	}
	84	}
	85	foreach ( $dtts as $data ) {
	86	if ($index2 == $data ['province']) {
	87	$provinces [$index2] ['dtt'] = $data ['total'];
	88	}
	89	}
	90
	91	foreach ( $tbns as $data ) {
	92	if ($index2 == $data ['province']) {
	93	$provinces [$index2] ['tbn'] = $data ['total'];
	94	}
	95	}
	96	foreach ( $tbng as $data ) {
	97	if ($index2 == $data ['province']) {
	98	$provinces [$index2] ['tbng'] = $data ['total'];
	99	}
	100	}
	101	foreach ( $tbts as $data ) {
	102	if ($index2 == $data ['province']) {
	103	$provinces [$index2] ['tbt'] = $data ['total'];
	104	}
	105	}
	106	$dem ++;
	107	}
	108
	109	return $provinces;
	110	}
	111
	112	/**
	113	*
	114	* @param unknown $input
	115	* @return Ambigous <multitype:, number, string>
	116	*/
	117	function getPackages($input) {
	118	$provinces = lang ( '_PROVINCES_' );
	119	// tinh thue bao theo thang
	120	$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 ";
	121	$query = $this->db->query ( $sql );
	122	$users = $query->result_array ();
	123
	124	$sql = "SELECT * FROM tblservicepackage WHERE p_period>0";
	125	$query = $this->db->query ( $sql );
	126	$packages = $query->result_array ();
	127	foreach ( $packages as $index => $package ) {
	128	$packages [$index] ['users'] = array ();
	129	foreach ( $users as $user ) {
	130	if ($user ['p_id'] == $package ['p_id']) {
	131	$packages [$index] ['users'] [] = $user;
	132	}
	133	}
	134	}
	135
	136	foreach ( $provinces as $index => $province ) {
	137	$provinces [$index] = array (
	138	"province" => $province
	139	);
	140	}
	141	$dem = 1;
	142	foreach ( $provinces as $index => $province ) {
	143	$provinces [$index] ['stt'] = $dem;
	144	$provinces [$index] ['total'] = 0;
	145	$totaluser = 0;
	146	foreach ( $packages as $index2 => $package ) {
	147	$provinces [$index] ['packages'] [$package ['p_name']] ['users'] = array ();
	148	$provinces [$index] ['packages'] [$package ['p_name']] ['total'] = 0;
	149	$total = 0;
	150	foreach ( $package ['users'] as $user ) {
	151	if ($index == $user ['province']) {
	152	$total = $total + 1;
	153	$totaluser = $totaluser + 1;
	154	$provinces [$index] ['total'] = $totaluser;
	155	$provinces [$index] ['packages'] [$package ['p_name']] ['total'] = $total;
	156
	157	$provinces [$index] ['packages'] [$package ['p_name']] ['users'] [] = $user;
	158	}
	159	}
	160	}
	161	$dem ++;
	162	}
	163	foreach ( $provinces as $index => $province ) {
	164	foreach ( $packages as $index2 => $package ) {
	165	$provinces [$index] ['packages'] [$package ['p_name']] ['percent'] = 0;
	166	if ($provinces [$index] ['packages'] [$package ['p_name']] ['total'] > 0 && $province ['total'] > 0) {
	167	$provinces [$index] ['packages'] [$package ['p_name']] ['percent'] = round ( $provinces [$index] ['packages'] [$package ['p_name']] ['total'] / $province ['total'], 2 ) * 100;
	168	}
	169	}
	170	}
	171
	172	$data ['packages'] = $packages;
	173	$data ['provinces'] = $provinces;
	174	return $data;
	175	}
	176
	177	/**
	178	*
	179	* @param unknown $input
	180	* @return multitype:multitype:unknown string Ambigous <Ambigous, multitype:number unknown , unknown, string>
	181	*/
	182	public function exportProvince($input) {
	183	// $input['date']=1;
	184	// $input['month']=1;
	185	// $input['year']=2014;
	186	// $input['to_month']=1;
	187	// $input['to_year']=2015;
	188	// $input['to_date']=15;
	189	if (isset ( $input ['to_month'] )) {
	190	$date = $input ['to_year'] . $input ['to_month'] . $input ['to_date'];
	191	} else {
	192	$date = date ( "Ymd" );
	193	}
	194	$provinces = array ();
	195	$data = $this->getProvinces ( $input );
	196	foreach ( $data as $index => $province ) {
	197	$provinces [] = array (
	198	"stt" => $province ['stt'],
	199	"ngay" => $date,
	200	"ma" => $index,
	201	"tinh" => $province ['province'],
	202	"thue_bao_ngay" => $province ['tbng'],
	203	"thue_bao_thang" => $province ['tbt'],
	204	"thue_bao_nam" => $province ['tbn'],
	205	"doanh_thu_ngay" => $province ['dtng'],
	206	"doanh_thu_thang" => $province ['dtt'],
	207	"doanh_thu_nam" => $province ['dtn']
	208	);
	209	}
	210	return $provinces;
	211	}
	212
	213	/**
	214	*
	215	* @param unknown $input
	216	* @return multitype:
	217	*/
	218	public function exportPackage($input) {
	219	// $input['date']=1;
	220	// $input['month']=1;
	221	// $input['year']=2014;
	222	// $input['to_month']=1;
	223	// $input['to_year']=2015;
	224	// $input['to_date']=15;
	225	if (isset ( $input ['month'] )) {
	226	$date = $input ['year'] . $input ['month'];
	227	} else {
	228	$date = date ( "Ym" );
	229	}
	230	$result = array ();
	231	$data = $this->getPackages ( $input );
	232	$data = $data ['provinces'];
	233	foreach ( $data as $index => $province ) {
	234	$package = array ();
	235	foreach ( $province ['packages'] as $index2 => $packages ) {
	236	$package = array_merge ( $package, array (
	237	"tong_so_" . $index2 => $packages ['total'],
	238	"ti_le_" . $index2 => $packages ['percent']
	239	) );
	240	}
	241	$tong = array (
	242	"tong_so_tb" => $province ['total']
	243	);
	244	$province = array (
	245	"stt" => $province ['stt'],
	246	"ngay" => $date,
	247	"ma" => $index,
	248	"tinh" => $province ['province']
	249	);
	250	$provinces [] = array_merge ( $province, $package, $tong );
	251	}
	252	return $provinces;
	253	}
	254	}

Note: See TracBrowser for help on using the repository browser.

Context Navigation

source: pro-violet-viettel/www/deploy/20150304/application/modules/admin/models/reportmodel.php

Download in other formats: