Changeset 818 for pro-violet-viettel/sourcecode/application/modules/admin/models/reportmodel.php

Timestamp:

Mar 24, 2015 2:15:02 PM (10 years ago)

Author:

namnd

Message:

 

File:

• pro-violet-viettel/sourcecode/application/modules/admin/models/reportmodel.php (modified) (9 diffs)

pro-violet-viettel/sourcecode/application/modules/admin/models/reportmodel.php

@@ -45 +45 @@
                 
                 // Tinh thue bao theo tinh theo nam
-                $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";
-                $query = $this->db->query ( $sql );
+                $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";
+                $query = $this->db->query ( $sql );
+                write_file('./log/test.log', date('d-m-Y H:i:s').": ".$sql."\n", FOPEN_WRITE_CREATE);
                 $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 $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 ();
@@ -56 +58 @@
                 // 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 ) {
@@ -104 +111 @@
                                 }
                         }
+                        
+                        $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 ++;
                 }
-                
-                return $provinces;
+
+                $data['provinces'] = $provinces;
+                
+                $data['totalTbng'] = $totalTbng;
+                $data['totalTbth'] = $totalTbth;
+                $data['totalTbn'] = $totalTbn;
+                
+                $data['totalDtng'] = $totalDtng;
+                $data['totalDtth'] = $totalDtth;
+                $data['totalDtn'] = $totalDtn;
+                
+                //return $provinces;
+                return $data;
         }
         
@@ -118 +145 @@
                 $provinces = lang ( '_PROVINCES_' );
                 // tinh thue bao theo thang
-                $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 ";
+                //$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'] . "' 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'] . "'";
                 $query = $this->db->query ( $sql );
                 $users = $query->result_array ();
@@ -128 +156 @@
                         $packages [$index] ['users'] = array ();
                         foreach ( $users as $user ) {
-                                if ($user ['p_id'] == $package ['p_id']) {
+                                if ($user ['p_code'] == $package ['p_code']) {
                                         $packages [$index] ['users'] [] = $user;
                                 }
                         }
                 }
-                
                 foreach ( $provinces as $index => $province ) {
                         $provinces [$index] = array (
@@ -139 +166 @@
                         );
                 }
+                
                 $dem = 1;
+                
                 foreach ( $provinces as $index => $province ) {
                         $provinces [$index] ['stt'] = $dem;
@@ -154 +183 @@
                                                 $provinces [$index] ['total'] = $totaluser;
                                                 $provinces [$index] ['packages'] [$package ['p_name']] ['total'] = $total;
+                                                $provinces [$index] ['packages'] [$package ['p_name']] ['users'] [] = $user;
                                                 
-                                                $provinces [$index] ['packages'] [$package ['p_name']] ['users'] [] = $user;
                                         }
                                 }
@@ -161 +190 @@
                         $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 ) {
@@ -172 +222 @@
                 $data ['packages'] = $packages;
                 $data ['provinces'] = $provinces;
+                $data['totalVip'] = $totalByVIP;
                 return $data;
         }