![(please configure the [header_logo] section in trac.ini)](/space/chrome/site/your_project_logo.png){kind=logo}
- Timestamp:
- Feb 14, 2015 10:38:40 AM (10 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
pro-violet-viettel/sourcecode/application/modules/admin/models/reportmodel.php
r764 r765 1 1 <?php 2 3 if (!defined('BASEPATH')) 4 exit('No direct script access allowed'); 2 if (! defined ( 'BASEPATH' )) 3 exit ( 'No direct script access allowed' ); 5 4 6 5 class Reportmodel extends MY_Model { 7 8 protected $table_name = 'tblpaidlog'; 9 protected $id_name = 'id'; 10 11 function __construct() { 12 parent::__construct(); 13 $this->load->helper('language'); 14 $this->lang->load('messages', 'message'); 15 } 16 17 function getProvinces($input) { 18 19 $from="tblpaidlog.paid_time >= '" . $input['year'] . "-" . $input['month'] . "-" . $input['date'] . " 00:00:00'"; 20 $to="tblpaidlog.paid_time <= '" . $input['to_year'] . "-" . $input['to_month'] . "-" . $input['to_date'] . " 23:59:59'"; 21 22 $provinces = lang('_PROVINCES_'); 23 // Tinh doanh thu theo tinh theo nam 24 $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"; 25 $query = $this->db->query($sql); 26 $dtns = $query->result_array(); 27 28 // tinh doanh thu theo thang 29 $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"; 30 $query = $this->db->query($sql); 31 $dtts = $query->result_array(); 32 33 // tinh doanh thu trong ngay hien tai 34 if (isset($input['to_month'])) 35 { 36 $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"; 37 } 38 $query = $this->db->query($sql); 39 $dtng = $query->result_array(); 40 41 // Tinh thue bao theo tinh theo nam 42 $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"; 43 $query = $this->db->query($sql); 44 $tbns = $query->result_array(); 45 46 // tinh thue bao theo thang 47 $sql = "SELECT *,count(us_id) as total FROM tbluser WHERE $from AND $to GROUP BY tbluser.province"; 48 $query = $this->db->query($sql); 49 $tbts = $query->result_array(); 50 51 // tinh thue bao theo ngay 52 $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"; 53 54 $query = $this->db->query($sql); 55 $tbng = $query->result_array(); 56 57 58 $dem=1; 59 foreach ($provinces as $index2 => $province) { 60 $provinces[$index2] = array("stt" => ($dem), "province" => $province, "dtn" => 0, "dtt" => 0, "dtng" => 0, "tbn" => 0, "tbt" => 0, "tbng" => 0); 61 foreach ($dtns as $data) { 62 if ($index2 == $data['province']) { 63 $provinces[$index2]['dtn'] = $data['total']; 64 } 65 } 66 foreach ($dtng as $data) { 67 if ($index2 == $data['province']) { 68 $provinces[$index2]['dtng'] = $data['total']; 69 } 70 } 71 foreach ($dtts as $data) { 72 if ($index2 == $data['province']) { 73 $provinces[$index2]['dtt'] = $data['total']; 74 75 } 76 } 77 78 foreach ($tbns as $data) { 79 if ($index2 == $data['province']) { 80 $provinces[$index2]['tbn'] = $data['total']; 81 } 82 } 83 foreach ($tbng as $data) { 84 if ($index2 == $data['province']) { 85 $provinces[$index2]['tbng'] = $data['total']; 86 } 87 } 88 foreach ($tbts as $data) { 89 if ($index2 == $data['province']) { 90 $provinces[$index2]['tbt'] = $data['total']; 91 } 92 } 93 $dem++; 94 } 95 96 return $provinces; 97 } 98 99 function getPackages($input) { 100 101 $provinces = lang('_PROVINCES_'); 102 // tinh thue bao theo thang 103 $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 "; 104 $query = $this->db->query($sql); 105 $users = $query->result_array(); 106 107 108 $sql = "SELECT * FROM tblservicepackage WHERE p_period>0"; 109 $query = $this->db->query($sql); 110 $packages = $query->result_array(); 111 foreach ($packages as $index => $package) { 112 $packages[$index]['users'] = array(); 113 foreach ($users as $user) { 114 if ($user['p_id'] == $package['p_id']) { 115 $packages[$index]['users'][] = $user; 116 } 117 } 118 } 119 120 foreach ($provinces as $index => $province) { 121 $provinces[$index] = array("province" => $province); 122 } 123 $dem=1; 124 foreach ($provinces as $index => $province) { 125 $provinces[$index]['stt'] = $dem; 126 $provinces[$index]['total'] = 0; 127 $totaluser = 0; 128 foreach ($packages as $index2 => $package) { 129 $provinces[$index]['packages'][$package['p_name']]['users'] = array(); 130 $provinces[$index]['packages'][$package['p_name']]['total'] = 0; 131 $total = 0; 132 foreach ($package['users'] as $user) { 133 if ($index == $user['province']) { 134 $total = $total + 1; 135 $totaluser = $totaluser + 1; 136 $provinces[$index]['total'] = $totaluser; 137 $provinces[$index]['packages'][$package['p_name']]['total'] = $total; 138 139 $provinces[$index]['packages'][$package['p_name']]['users'][] = $user; 140 } 141 } 142 } 143 $dem++; 144 } 145 foreach ($provinces as $index => $province) { 146 foreach ($packages as $index2 => $package) { 147 $provinces[$index]['packages'][$package['p_name']]['percent'] = 0; 148 if ($provinces[$index]['packages'][$package['p_name']]['total'] > 0 && $province['total'] > 0) { 149 $provinces[$index]['packages'][$package['p_name']]['percent'] = round($provinces[$index]['packages'][$package['p_name']]['total'] / $province['total'], 2) * 100; 150 } 151 } 152 } 153 154 $data['packages'] = $packages; 155 $data['provinces'] = $provinces; 156 return $data; 157 } 158 public function exportProvince($input) 159 { 160 //$input['date']=1; 161 //$input['month']=1; 162 //$input['year']=2014; 163 //$input['to_month']=1; 164 //$input['to_year']=2015; 165 //$input['to_date']=15; 166 if (isset($input['to_month'])) 167 { 168 $date=$input['to_year'].$input['to_month'].$input['to_date']; 169 } 170 else { 171 $date=date("Ymd"); 172 } 173 $provinces=array(); 174 $data=$this->getProvinces($input); 175 foreach ($data as $index=>$province) 176 { 177 $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']); 178 } 179 return $provinces; 180 } 181 public function exportPackage($input) 182 { 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['month'])) 190 { 191 $date=$input['year'].$input['month']; 192 } 193 else { 194 $date=date("Ym"); 195 } 196 $result=array(); 197 $data=$this->getPackages($input); 198 $data=$data['provinces']; 199 foreach ($data as $index=>$province) 200 { 201 $package=array(); 202 foreach ($province['packages'] as $index2=>$packages) 203 { 204 $package= array_merge($package,array("tong_so_".$index2=>$packages['total'],"ti_le_".$index2=>$packages['percent'])); 205 } 206 $tong=array("tong_so_tb"=>$province['total']); 207 $province=array("stt"=>$province['stt'],"ngay"=>$date,"ma"=>$index,"tinh"=>$province['province']); 208 $provinces[]= array_merge($province,$package,$tong); 209 } 210 return $provinces; 211 } 212 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 } 213 254 }
Note: See TracChangeset
for help on using the changeset viewer.
