Context Navigation

reportmodel.php

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

File size: 8.7 KB

Line
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: