| [765] | 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
|
|---|
| [883] | 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 | $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";
|
|---|
| [765] | 32 | $query = $this->db->query ( $sql );
|
|---|
| 33 | $dtns = $query->result_array ();
|
|---|
| 34 |
|
|---|
| 35 | // tinh doanh thu theo thang
|
|---|
| [838] | 36 | $from = "tblpaidlog.paid_time >= '" . $input ['year'] . "-" . $input ['month'] . "-01 00:00:00'";
|
|---|
| [765] | 37 | $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";
|
|---|
| 38 | $query = $this->db->query ( $sql );
|
|---|
| 39 | $dtts = $query->result_array ();
|
|---|
| 40 |
|
|---|
| 41 | // tinh doanh thu trong ngay hien tai
|
|---|
| 42 | if (isset ( $input ['to_month'] )) {
|
|---|
| 43 | $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";
|
|---|
| 44 | }
|
|---|
| 45 | $query = $this->db->query ( $sql );
|
|---|
| 46 | $dtng = $query->result_array ();
|
|---|
| 47 |
|
|---|
| 48 | // Tinh thue bao theo tinh theo nam
|
|---|
| [883] | 49 | //$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";
|
|---|
| 50 | $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";
|
|---|
| [765] | 51 | $query = $this->db->query ( $sql );
|
|---|
| 52 | $tbns = $query->result_array ();
|
|---|
| 53 |
|
|---|
| 54 | // tinh thue bao theo thang
|
|---|
| [818] | 55 | //$sql = "SELECT *,count(us_id) as total FROM tbluser WHERE $from AND $to GROUP BY tbluser.province";
|
|---|
| 56 | $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";
|
|---|
| [765] | 57 | $query = $this->db->query ( $sql );
|
|---|
| 58 | $tbts = $query->result_array ();
|
|---|
| 59 |
|
|---|
| 60 | // tinh thue bao theo ngay
|
|---|
| 61 | $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";
|
|---|
| 62 | $query = $this->db->query ( $sql );
|
|---|
| 63 | $tbng = $query->result_array ();
|
|---|
| 64 | $dem = 1;
|
|---|
| 65 |
|
|---|
| [818] | 66 | $totalTbng = 0;
|
|---|
| 67 | $totalTbth = 0;
|
|---|
| 68 | $totalTbn = 0;
|
|---|
| 69 | $totalDtng = 0;
|
|---|
| 70 | $totalDtth = 0;
|
|---|
| 71 | $totalDtn = 0;
|
|---|
| 72 |
|
|---|
| [765] | 73 | foreach ( $provinces as $index2 => $province ) {
|
|---|
| 74 | $provinces [$index2] = array (
|
|---|
| 75 | "stt" => ($dem),
|
|---|
| 76 | "province" => $province,
|
|---|
| 77 | "dtn" => 0,
|
|---|
| 78 | "dtt" => 0,
|
|---|
| 79 | "dtng" => 0,
|
|---|
| 80 | "tbn" => 0,
|
|---|
| 81 | "tbt" => 0,
|
|---|
| 82 | "tbng" => 0
|
|---|
| 83 | );
|
|---|
| 84 | foreach ( $dtns as $data ) {
|
|---|
| 85 | if ($index2 == $data ['province']) {
|
|---|
| 86 | $provinces [$index2] ['dtn'] = $data ['total'];
|
|---|
| 87 | }
|
|---|
| 88 | }
|
|---|
| 89 | foreach ( $dtng as $data ) {
|
|---|
| 90 | if ($index2 == $data ['province']) {
|
|---|
| 91 | $provinces [$index2] ['dtng'] = $data ['total'];
|
|---|
| 92 | }
|
|---|
| 93 | }
|
|---|
| 94 | foreach ( $dtts as $data ) {
|
|---|
| 95 | if ($index2 == $data ['province']) {
|
|---|
| 96 | $provinces [$index2] ['dtt'] = $data ['total'];
|
|---|
| 97 | }
|
|---|
| 98 | }
|
|---|
| 99 |
|
|---|
| 100 | foreach ( $tbns as $data ) {
|
|---|
| 101 | if ($index2 == $data ['province']) {
|
|---|
| 102 | $provinces [$index2] ['tbn'] = $data ['total'];
|
|---|
| 103 | }
|
|---|
| 104 | }
|
|---|
| 105 | foreach ( $tbng as $data ) {
|
|---|
| 106 | if ($index2 == $data ['province']) {
|
|---|
| 107 | $provinces [$index2] ['tbng'] = $data ['total'];
|
|---|
| 108 | }
|
|---|
| 109 | }
|
|---|
| 110 | foreach ( $tbts as $data ) {
|
|---|
| 111 | if ($index2 == $data ['province']) {
|
|---|
| 112 | $provinces [$index2] ['tbt'] = $data ['total'];
|
|---|
| 113 | }
|
|---|
| 114 | }
|
|---|
| [818] | 115 |
|
|---|
| 116 | $totalTbng = $totalTbng + $provinces [$index2] ['tbng'];
|
|---|
| 117 | $totalTbth = $totalTbth + $provinces [$index2] ['tbt'];
|
|---|
| 118 | $totalTbn = $totalTbn + $provinces [$index2] ['tbn'];
|
|---|
| 119 |
|
|---|
| 120 | $totalDtng = $totalDtng + $provinces [$index2] ['dtng'];
|
|---|
| 121 | $totalDtth = $totalDtth + $provinces [$index2] ['dtt'];
|
|---|
| 122 | $totalDtn = $totalDtn + $provinces [$index2] ['dtn'];
|
|---|
| 123 |
|
|---|
| [765] | 124 | $dem ++;
|
|---|
| 125 | }
|
|---|
| [818] | 126 |
|
|---|
| 127 | $data['provinces'] = $provinces;
|
|---|
| [765] | 128 |
|
|---|
| [818] | 129 | $data['totalTbng'] = $totalTbng;
|
|---|
| 130 | $data['totalTbth'] = $totalTbth;
|
|---|
| 131 | $data['totalTbn'] = $totalTbn;
|
|---|
| 132 |
|
|---|
| 133 | $data['totalDtng'] = $totalDtng;
|
|---|
| 134 | $data['totalDtth'] = $totalDtth;
|
|---|
| 135 | $data['totalDtn'] = $totalDtn;
|
|---|
| 136 |
|
|---|
| 137 | return $data;
|
|---|
| [765] | 138 | }
|
|---|
| 139 |
|
|---|
| 140 | /**
|
|---|
| 141 | *
|
|---|
| 142 | * @param unknown $input
|
|---|
| 143 | * @return Ambigous <multitype:, number, string>
|
|---|
| 144 | */
|
|---|
| 145 | function getPackages($input) {
|
|---|
| 146 | $provinces = lang ( '_PROVINCES_' );
|
|---|
| 147 | // tinh thue bao theo thang
|
|---|
| [818] | 148 | //$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 ";
|
|---|
| [883] | 149 | $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'";
|
|---|
| [765] | 150 | $query = $this->db->query ( $sql );
|
|---|
| 151 | $users = $query->result_array ();
|
|---|
| [883] | 152 | //write_file('./log/sql.log', date("d-m-Y H:i:s").": ".$sql."\n", FOPEN_WRITE_CREATE);
|
|---|
| [765] | 153 |
|
|---|
| 154 | $sql = "SELECT * FROM tblservicepackage WHERE p_period>0";
|
|---|
| 155 | $query = $this->db->query ( $sql );
|
|---|
| 156 | $packages = $query->result_array ();
|
|---|
| 157 | foreach ( $packages as $index => $package ) {
|
|---|
| 158 | $packages [$index] ['users'] = array ();
|
|---|
| 159 | foreach ( $users as $user ) {
|
|---|
| [818] | 160 | if ($user ['p_code'] == $package ['p_code']) {
|
|---|
| [765] | 161 | $packages [$index] ['users'] [] = $user;
|
|---|
| 162 | }
|
|---|
| 163 | }
|
|---|
| 164 | }
|
|---|
| 165 | foreach ( $provinces as $index => $province ) {
|
|---|
| 166 | $provinces [$index] = array (
|
|---|
| 167 | "province" => $province
|
|---|
| 168 | );
|
|---|
| 169 | }
|
|---|
| [818] | 170 |
|
|---|
| [765] | 171 | $dem = 1;
|
|---|
| [818] | 172 |
|
|---|
| [765] | 173 | foreach ( $provinces as $index => $province ) {
|
|---|
| 174 | $provinces [$index] ['stt'] = $dem;
|
|---|
| 175 | $provinces [$index] ['total'] = 0;
|
|---|
| 176 | $totaluser = 0;
|
|---|
| 177 | foreach ( $packages as $index2 => $package ) {
|
|---|
| 178 | $provinces [$index] ['packages'] [$package ['p_name']] ['users'] = array ();
|
|---|
| 179 | $provinces [$index] ['packages'] [$package ['p_name']] ['total'] = 0;
|
|---|
| 180 | $total = 0;
|
|---|
| 181 | foreach ( $package ['users'] as $user ) {
|
|---|
| 182 | if ($index == $user ['province']) {
|
|---|
| 183 | $total = $total + 1;
|
|---|
| 184 | $totaluser = $totaluser + 1;
|
|---|
| 185 | $provinces [$index] ['total'] = $totaluser;
|
|---|
| 186 | $provinces [$index] ['packages'] [$package ['p_name']] ['total'] = $total;
|
|---|
| [818] | 187 | $provinces [$index] ['packages'] [$package ['p_name']] ['users'] [] = $user;
|
|---|
| [765] | 188 |
|
|---|
| 189 | }
|
|---|
| 190 | }
|
|---|
| 191 | }
|
|---|
| 192 | $dem ++;
|
|---|
| 193 | }
|
|---|
| [818] | 194 |
|
|---|
| 195 | $totalByVIP = array();
|
|---|
| 196 |
|
|---|
| 197 | foreach ($packages as $index => $package){
|
|---|
| 198 | $totalByVIP[$package ['p_name']] = 0;
|
|---|
| 199 | foreach ( $provinces as $index=>$province){
|
|---|
| 200 | foreach ($province['packages'] as $index2 => $p){
|
|---|
| 201 | if ($index2 == $package['p_name'])
|
|---|
| 202 | $totalByVIP[$package ['p_name']] = $totalByVIP[$package ['p_name']] + $p['total'];
|
|---|
| 203 | }
|
|---|
| 204 | }
|
|---|
| 205 | }
|
|---|
| 206 |
|
|---|
| 207 | $totalVip = 0;
|
|---|
| 208 |
|
|---|
| 209 | foreach ($totalByVIP as $vipName=>$total){
|
|---|
| 210 | $totalVip = $totalVip + $total;
|
|---|
| 211 | }
|
|---|
| 212 |
|
|---|
| 213 | $totalByVIP['total'] = $totalVip;
|
|---|
| 214 |
|
|---|
| [765] | 215 | foreach ( $provinces as $index => $province ) {
|
|---|
| 216 | foreach ( $packages as $index2 => $package ) {
|
|---|
| 217 | $provinces [$index] ['packages'] [$package ['p_name']] ['percent'] = 0;
|
|---|
| 218 | if ($provinces [$index] ['packages'] [$package ['p_name']] ['total'] > 0 && $province ['total'] > 0) {
|
|---|
| 219 | $provinces [$index] ['packages'] [$package ['p_name']] ['percent'] = round ( $provinces [$index] ['packages'] [$package ['p_name']] ['total'] / $province ['total'], 2 ) * 100;
|
|---|
| 220 | }
|
|---|
| 221 | }
|
|---|
| 222 | }
|
|---|
| 223 |
|
|---|
| 224 | $data ['packages'] = $packages;
|
|---|
| 225 | $data ['provinces'] = $provinces;
|
|---|
| [818] | 226 | $data['totalVip'] = $totalByVIP;
|
|---|
| [765] | 227 | return $data;
|
|---|
| 228 | }
|
|---|
| 229 |
|
|---|
| 230 | /**
|
|---|
| 231 | *
|
|---|
| 232 | * @param unknown $input
|
|---|
| 233 | * @return multitype:multitype:unknown string Ambigous <Ambigous, multitype:number unknown , unknown, string>
|
|---|
| 234 | */
|
|---|
| 235 | public function exportProvince($input) {
|
|---|
| 236 | // $input['date']=1;
|
|---|
| 237 | // $input['month']=1;
|
|---|
| 238 | // $input['year']=2014;
|
|---|
| 239 | // $input['to_month']=1;
|
|---|
| 240 | // $input['to_year']=2015;
|
|---|
| 241 | // $input['to_date']=15;
|
|---|
| 242 | if (isset ( $input ['to_month'] )) {
|
|---|
| 243 | $date = $input ['to_year'] . $input ['to_month'] . $input ['to_date'];
|
|---|
| 244 | } else {
|
|---|
| 245 | $date = date ( "Ymd" );
|
|---|
| 246 | }
|
|---|
| 247 | $provinces = array ();
|
|---|
| 248 | $data = $this->getProvinces ( $input );
|
|---|
| 249 | foreach ( $data as $index => $province ) {
|
|---|
| 250 | $provinces [] = array (
|
|---|
| 251 | "stt" => $province ['stt'],
|
|---|
| 252 | "ngay" => $date,
|
|---|
| 253 | "ma" => $index,
|
|---|
| 254 | "tinh" => $province ['province'],
|
|---|
| 255 | "thue_bao_ngay" => $province ['tbng'],
|
|---|
| 256 | "thue_bao_thang" => $province ['tbt'],
|
|---|
| 257 | "thue_bao_nam" => $province ['tbn'],
|
|---|
| 258 | "doanh_thu_ngay" => $province ['dtng'],
|
|---|
| 259 | "doanh_thu_thang" => $province ['dtt'],
|
|---|
| 260 | "doanh_thu_nam" => $province ['dtn']
|
|---|
| 261 | );
|
|---|
| 262 | }
|
|---|
| 263 | return $provinces;
|
|---|
| 264 | }
|
|---|
| 265 |
|
|---|
| 266 | /**
|
|---|
| 267 | *
|
|---|
| 268 | * @param unknown $input
|
|---|
| 269 | * @return multitype:
|
|---|
| 270 | */
|
|---|
| 271 | public function exportPackage($input) {
|
|---|
| 272 | // $input['date']=1;
|
|---|
| 273 | // $input['month']=1;
|
|---|
| 274 | // $input['year']=2014;
|
|---|
| 275 | // $input['to_month']=1;
|
|---|
| 276 | // $input['to_year']=2015;
|
|---|
| 277 | // $input['to_date']=15;
|
|---|
| 278 | if (isset ( $input ['month'] )) {
|
|---|
| 279 | $date = $input ['year'] . $input ['month'];
|
|---|
| 280 | } else {
|
|---|
| 281 | $date = date ( "Ym" );
|
|---|
| 282 | }
|
|---|
| 283 | $result = array ();
|
|---|
| 284 | $data = $this->getPackages ( $input );
|
|---|
| 285 | $data = $data ['provinces'];
|
|---|
| 286 | foreach ( $data as $index => $province ) {
|
|---|
| 287 | $package = array ();
|
|---|
| 288 | foreach ( $province ['packages'] as $index2 => $packages ) {
|
|---|
| 289 | $package = array_merge ( $package, array (
|
|---|
| 290 | "tong_so_" . $index2 => $packages ['total'],
|
|---|
| 291 | "ti_le_" . $index2 => $packages ['percent']
|
|---|
| 292 | ) );
|
|---|
| 293 | }
|
|---|
| 294 | $tong = array (
|
|---|
| 295 | "tong_so_tb" => $province ['total']
|
|---|
| 296 | );
|
|---|
| 297 | $province = array (
|
|---|
| 298 | "stt" => $province ['stt'],
|
|---|
| 299 | "ngay" => $date,
|
|---|
| 300 | "ma" => $index,
|
|---|
| 301 | "tinh" => $province ['province']
|
|---|
| 302 | );
|
|---|
| 303 | $provinces [] = array_merge ( $province, $package, $tong );
|
|---|
| 304 | }
|
|---|
| 305 | return $provinces;
|
|---|
| 306 | }
|
|---|
| [702] | 307 | } |
|---|
![(please configure the [header_logo] section in trac.ini)](/space/chrome/site/your_project_logo.png){kind=logo}
