source: pro-violet-viettel/sourcecode/application/modules/admin/models/collabolator_model.php @ 741

Last change on this file since 741 was 741, checked in by quyenla, 10 years ago

fixing charts
File size: 21.7 KB
Line 
1<?php
2if (!defined('BASEPATH'))
3    exit('No direct script access allowed');
4
5class Collabolator_model extends MY_Model {
6
7    protected $table_name = 'tblcollaborator';
8    protected $id_name = 'id';
9
10    function __construct() {
11        parent::__construct();
12    }
13
14    function getCollaborators($data) {
15        $sql = "SELECT * FROM " . $this->table_name;
16        if ($data['keyword']) {
17            if ($data['search_field'] == "cellphone") {
18                                if(strlen($data['daterange_start'])>0){
19                                        $sql.=" WHERE (cellphone LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%') AND (created_time BETWEEN '".$data['daterange_start']."' AND '".$data['daterange_end']."')";
20                                }else{
21                                        $sql.=" WHERE (cellphone LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%')";
22                                }
23            } else {
24                                if(strlen($data['daterange_start'])>0){
25                                        $sql.=" WHERE (full_name LIKE '%" . $data['keyword'] . "%' OR login_name LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%') AND (created_time BETWEEN '".$data['daterange_start']."' AND '".$data['daterange_end']."')";
26                                }else{
27                                        $sql.=" WHERE (full_name LIKE '%" . $data['keyword'] . "%' OR login_name LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%')";
28                                }
29            }
30        }else{
31                        if(strlen($data['daterange_start'])>0){
32                                $sql.=" WHERE created_time BETWEEN '".$data['daterange_start']."' AND '".$data['daterange_end']."'";
33                        }
34                }
35
36        $order = "";
37        if ($data['sorting_order'] != "sorting") {
38           
39                $sort = "DESC";
40                if ($data['sorting_order'] == "sorting_asc")
41                    $sort = "ASC";
42                $order = "ORDER BY " . $data['sorting_field'] . " " . $sort;
43           
44        }
45       
46        return $this->db->query($sql . " " . $order . " LIMIT " . $data['start'] . ", " . $data['perpage'] . " ")->result_array();
47    }
48
49    function getAllCollaboratorUser($from,$id=false)
50    {
51        $from = date("Y-m-d 00:00:00",$from);
52        $where="";
53        if ($id)
54        {
55            $where = " AND tbluser.collaborator = $id ";
56        }
57        //$sql="SELECT *,tblcollaborator.created_time as collaborator_created_time, tbluser.created_time as user_created_time  FROM ".$this->table_name." INNER JOIN tbluser ON tbluser.collaborator = tblcollaborator.id WHERE tbluser.created_time > '".$from."'".$where."";
58                $sql="SELECT *,tblcollaborator.created_time as collaborator_created_time, tbluser.created_time as user_created_time  FROM ".$this->table_name." INNER JOIN tbluser ON tbluser.collaborator = tblcollaborator.cellphone WHERE tbluser.created_time > '".$from."'".$where."";
59
60        $result = $this->db->query($sql)->result_array();
61        foreach ($result as $index=>$paidlog)
62        {
63            $result[$index]['time']=  strtotime($paidlog['user_created_time']);
64        }
65       
66        return $result;
67    }
68    function countCountCollaboratorUser($data)
69    {
70        $order = "";
71        if ($data['sorting_order'] != "sorting") {
72           
73                $sort = "DESC";
74                if ($data['sorting_order'] == "sorting_asc")
75                    $sort = "ASC";
76                $order = "ORDER BY " . $data['sorting_field'] . " " . $sort;
77           
78        }
79        $where="";
80        if ($data['keyword']) {
81            if ($data['search_field'] == "cellphone") {
82                $where.=" WHERE (tblcollaborator.cellphone LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%')";
83            } else {
84                $where.=" WHERE (full_name LIKE '%" . $data['keyword'] . "%' OR login_name LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%') ";
85            }
86        }
87       // $from = date("Y-m-d 00:00:00",$from);
88        //$sql="SELECT *,tblcollaborator.created_time as collaborator_created_time,tblcollaborator.cellphone as collaborator_cellphone, tbluser.created_time as user_created_time, COUNT(tbluser.collaborator) as total_user  FROM ".$this->table_name." INNER JOIN tbluser ON tbluser.collaborator = tblcollaborator.id ".$where." GROUP BY tblcollaborator.id ".$order." ";
89
90                $sql="SELECT *,tblcollaborator.created_time as collaborator_created_time,tblcollaborator.cellphone as collaborator_cellphone, tbluser.created_time as user_created_time, COUNT(tbluser.collaborator) as total_user  FROM ".$this->table_name." INNER JOIN tbluser ON tbluser.collaborator = tblcollaborator.cellphone ".$where." GROUP BY tblcollaborator.id ".$order." ";
91       
92        $result = $this->db->query($sql)->result_array();
93       
94
95        return count($result);
96    }
97    function getCountCollaboratorUser($data)
98    {
99        $order = "";
100        if ($data['sorting_order'] != "sorting") {
101           
102                $sort = "DESC";
103                if ($data['sorting_order'] == "sorting_asc")
104                    $sort = "ASC";
105                $order = "ORDER BY " . $data['sorting_field'] . " " . $sort;
106           
107        }
108        $where="";
109        if ($data['keyword']) {
110            if ($data['search_field'] == "cellphone") {
111                $where.=" WHERE (tblcollaborator.cellphone LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%')";
112                               
113            } else {
114                $where.=" WHERE (full_name LIKE '%" . $data['keyword'] . "%' OR login_name LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%') ";
115            }
116        }
117               
118       // $from = date("Y-m-d 00:00:00",$from);
119        //$sql="SELECT *,tblcollaborator.created_time as collaborator_created_time,tblcollaborator.cellphone as collaborator_cellphone, tbluser.created_time as user_created_time, COUNT(tbluser.collaborator) as total_user  FROM ".$this->table_name." INNER JOIN tbluser ON tbluser.collaborator = tblcollaborator.id ".$where." GROUP BY tblcollaborator.id ".$order." LIMIT " . $data['start'] . ", " . $data['perpage'] . " ";
120       
121                $sql="SELECT *,tblcollaborator.created_time as collaborator_created_time,tblcollaborator.cellphone as collaborator_cellphone, tbluser.created_time as user_created_time, COUNT(tbluser.collaborator) as total_user  FROM ".$this->table_name." INNER JOIN tbluser ON tbluser.collaborator = tblcollaborator.cellphone ".$where." GROUP BY tblcollaborator.id ".$order." LIMIT " . $data['start'] . ", " . $data['perpage'] . " ";
122
123        $result = $this->db->query($sql)->result_array();
124        foreach ($result as $index=>$paidlog)
125        {
126            $result[$index]['time']=  strtotime($paidlog['user_created_time']);
127        }
128
129        return $result;
130    }
131   
132        function countCountCollaboratorUsers($data)
133    {
134        $order = "";
135        if ($data['sorting_order'] != "sorting") {
136           
137                $sort = "DESC";
138                if ($data['sorting_order'] == "sorting_asc")
139                    $sort = "ASC";
140                $order = "ORDER BY " . $data['sorting_field'] . " " . $sort;
141           
142        }
143        $where="";
144        if ($data['keyword']) {
145            if ($data['search_field'] == "cellphone") {
146                $where.=" WHERE (tbluser.cellphone LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%')";
147            }
148        }
149       // $from = date("Y-m-d 00:00:00",$from);
150        //$sql="SELECT *,tblcollaborator.created_time as collaborator_created_time,tbluser.cellphone as user_cellphone,tblcollaborator.cellphone as collaborator_cellphone, tbluser.created_time as user_created_time  FROM ".$this->table_name." INNER JOIN tbluser ON tbluser.collaborator = tblcollaborator.id ".$where." AND tbluser.collaborator = ".$data['id']." ".$order."";
151   
152        $sql="SELECT *,tblcollaborator.created_time as collaborator_created_time,tbluser.cellphone as user_cellphone,tblcollaborator.cellphone as collaborator_cellphone, tbluser.created_time as user_created_time  FROM ".$this->table_name." INNER JOIN tbluser ON tbluser.collaborator = tblcollaborator.cellphone ".$where." AND tblcollaborator.id = ".$data['id']." ".$order."";
153
154                $result = $this->db->query($sql)->result_array();
155        foreach ($result as $index=>$paidlog)
156        {
157            $result[$index]['time']=  strtotime($paidlog['user_created_time']);
158        }
159
160        return count($result);
161    }
162        function getCountCollaboratorUsers($data)
163    {
164        $order = "";
165        if ($data['sorting_order'] != "sorting") {
166           
167                $sort = "DESC";
168                if ($data['sorting_order'] == "sorting_asc")
169                    $sort = "ASC";
170                $order = "ORDER BY " . $data['sorting_field'] . " " . $sort;
171           
172        }
173        $where="";
174        if ($data['keyword']) {
175            if ($data['search_field'] == "cellphone") {
176                $where.=" WHERE (tbluser.cellphone LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%')";
177            }
178        }
179       // $from = date("Y-m-d 00:00:00",$from);
180       // $sql="SELECT *,tblcollaborator.created_time as collaborator_created_time,tbluser.cellphone as user_cellphone,tblcollaborator.cellphone as collaborator_cellphone, tbluser.created_time as user_created_time  FROM ".$this->table_name." INNER JOIN tbluser ON tbluser.collaborator = tblcollaborator.id ".$where." AND tbluser.collaborator = ".$data['id']." ".$order." LIMIT " . $data['start'] . ", " . $data['perpage'] . " ";
181               
182        $sql="SELECT *,tblcollaborator.created_time as collaborator_created_time,tbluser.cellphone as user_cellphone,tblcollaborator.cellphone as collaborator_cellphone, tbluser.created_time as user_created_time  FROM ".$this->table_name." INNER JOIN tbluser ON tbluser.collaborator = tblcollaborator.cellphone ".$where." AND tblcollaborator.id = ".$data['id']." ".$order." LIMIT " . $data['start'] . ", " . $data['perpage'] . " ";
183
184                $result = $this->db->query($sql)->result_array();
185        foreach ($result as $index=>$paidlog)
186        {
187            $result[$index]['time']=  strtotime($paidlog['user_created_time']);
188        }
189
190        return $result;
191    }
192   
193    function isExist($data) {
194        $sql = "SELECT COUNT(id) as total FROM " . $this->table_name . " WHERE " . $data['field'] . " = '" . $data['value'] . "'";
195        if (isset($data['id']))
196        {
197            $id=$data['id'];
198            $sql.=" AND id <> ".$id." LIMIT 1";
199        }
200        $result = $this->db->query($sql)->row_array();
201        if ($result['total'] == 1) {
202            return true;
203        } else {
204            return false;
205        }
206    }
207
208    function countCollaborator($data) {
209        $sql = "SELECT COUNT(id) as total FROM " . $this->table_name;
210        if ($data['keyword']) {
211            if ($data['search_field'] == "cellphone") {
212                                if(strlen($data['daterange_start'])>0){
213                                        $sql.=" WHERE (cellphone LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%') AND (created_time BETWEEN '".$data['daterange_start']."' AND '".$data['daterange_end']."')";
214                                }else{
215                                        $sql.=" WHERE (cellphone LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%')";
216                                }
217            } else {
218                                if(strlen($data['daterange_start'])>0){
219                                        $sql.=" WHERE (full_name LIKE '%" . $data['keyword'] . "%' OR login_name LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%') AND (created_time BETWEEN '".$data['daterange_start']."' AND '".$data['daterange_end']."')";
220                                }else{
221                                        $sql.=" WHERE (full_name LIKE '%" . $data['keyword'] . "%' OR login_name LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%') ";
222                                }
223            }
224        }else{
225                        if(strlen($data['daterange_start'])>0){
226                                $sql.=" WHERE created_time BETWEEN '".$data['daterange_start']."' AND '".$data['daterange_end']."'";
227                        }
228                }
229        $result = $this->db->query($sql)->row_array();
230        return $result['total'];
231    }
232    function getCollaborator($id)
233    {
234        $sql="SELECT * FROM ".$this->table_name." WHERE id=".$id." LIMIT 1";
235        $result = $this->db->query($sql)->row_array();
236        return $result;
237    }
238    function getStatistics()
239    {
240     
241       $day = date('w');
242        $data = array();
243        $last_month['start'] = strtotime(date("Y-m-d", strtotime(date("Y-m-d", mktime(0, 0, 0, date("m")-1, 1, date("Y"))))) . " 00:00:00");
244        $last_month['end'] = strtotime(date("Y-m-d", strtotime(date("Y-m-d", mktime(0, 0, 0, date("m"), 0, date("Y"))))) . " 23:59:59");
245
246        $last_week['start'] = strtotime(date("Y-m-d", strtotime(date('m-d-Y', strtotime('-'.$day.' days')))) . " 00:00:00");
247        $last_week['end'] = strtotime(date("Y-m-d", strtotime(date('m-d-Y', strtotime('+'.(6-$day).' days')))) . " 23:59:59");
248
249        $last_week['end'] = strtotime('last sunday') + (24 * 60 * 60 - 1);
250        $last_week['start'] = ($last_week['end'] - 7 * 24 * 60 * 60 + 1);
251
252        $last_year['start'] = strtotime((date("Y") - 1) . "-01-01 00:00:00");
253        $last_year['end'] = strtotime((date("Y") - 1) . "-12-31 23:59:59");
254
255
256
257        $last_quarter = $this->getLastquarter();
258        $last_quarter['start'] = $last_quarter['start'];
259        $last_quarter['end'] = $last_quarter['end'];
260
261
262        $this->load->model('paidlog_model');
263        $paidlogs = $this->paidlog_model->getAllPaidlogs($last_year['start']);
264
265        $revenue['last_week'] = 0;
266        $revenue['last_month'] = 0;
267        $revenue['last_quarter'] = 0;
268        $revenue['last_year'] = 0;
269
270        $lastmonth = strtotime(date("Y-m-d", mktime(0, 0, 0, date("m")-1, 1, date("Y"))));
271        $thismonth = strtotime(date('Y-m-01'));
272        $charts['last_month'] = array(0 => 0, 1 => 0, 2 => 0, 3 => 0, 4 => 0);
273        $charts['this_month'] = array(0 => 0, 1 => 0, 2 => 0, 3 => 0, 4 => 0);
274       
275        $top['last_month']['user']=0;
276        $top['last_month']['sms']=0;
277        $top['last_month']['card']=0;
278        $top['last_month']['collaborator']=0;
279        $top['this_month']['user']=0;
280        $top['this_month']['sms']=0;
281        $top['this_month']['card']=0;
282        $top['this_month']['collaborator']=0;
283       
284        foreach ($paidlogs as $paidlog) {
285
286           
287            if (($paidlog['time'] < $last_week['end']) && ($paidlog['time'] > $last_week['start'])) {
288               
289                $revenue['last_week']+=$paidlog['amount'];
290            }
291            if (($paidlog['time'] < $last_month['end']) && ($paidlog['time'] > $last_month['start'])) {
292           
293                $revenue['last_month']+=$paidlog['amount'];
294            }
295            if (($paidlog['time'] < $last_quarter['end']) && ($paidlog['time'] > $last_quarter['start'])) {
296             
297                $revenue['last_quarter']+=$paidlog['amount'];
298            }
299            if (($paidlog['time'] < $last_year['end']) && ($paidlog['time'] > $last_year['start'])) {
300             
301                $revenue['last_year']+=$paidlog['amount'];
302            }
303           
304           
305            if (($paidlog['time'] < $lastmonth + (7 * 24 * 60 * 60)) && ($paidlog['time'] > $lastmonth)) {
306                $charts['last_month'][0]+=$paidlog['amount'];
307            }
308            if (($paidlog['time'] < $lastmonth + (14 * 24 * 60 * 60)) && ($paidlog['time'] > $lastmonth + (7 * 24 * 60 * 60))) {
309                $charts['last_month'][1]+=$paidlog['amount'];
310            }
311            if (($paidlog['time'] < $lastmonth + (21 * 24 * 60 * 60)) && ($paidlog['time'] > $lastmonth + (14 * 24 * 60 * 60))) {
312                $charts['last_month'][2]+=$paidlog['amount'];
313            }
314            if (($paidlog['time'] < $lastmonth + (28 * 24 * 60 * 60)) && ($paidlog['time'] > $lastmonth + (21 * 24 * 60 * 60))) {
315                $charts['last_month'][3]+=$paidlog['amount'];
316            }
317            if (($paidlog['time'] < $thismonth) && ($paidlog['time'] > $lastmonth + (28 * 24 * 60 * 60))) {
318                $charts['last_month'][4]+=$paidlog['amount'];
319            }
320           
321            if (($paidlog['time'] < $thismonth) && ($paidlog['time'] > $lastmonth)) {
322                $top['last_month']['user']++;
323                if ($paidlog['paid_type']==1)
324                {
325                   $top['last_month']['sms']+=$paidlog['amount'];
326                }
327                if ($paidlog['paid_type']==2)
328                {
329                    $top['last_month']['card']+=$paidlog['amount'];
330                }
331                if ($paidlog['collaborator']!="")
332                {
333                    $top['last_month']['collaborator']+=$paidlog['amount'];
334                }
335            }
336           
337           
338
339            if (($paidlog['time'] < $thismonth + (7 * 24 * 60 * 60)) && ($paidlog['time'] > $thismonth)) {
340             
341                $charts['this_month'][0] += $paidlog['amount'];
342            }
343            if (($paidlog['time'] < $thismonth + (14 * 24 * 60 * 60)) && ($paidlog['time'] > $thismonth + (7 * 24 * 60 * 60))) {
344               
345                $charts['this_month'][1]+=$paidlog['amount'];
346            }
347            if (($paidlog['time'] < $thismonth + (21 * 24 * 60 * 60)) && ($paidlog['time'] > $thismonth + (14 * 24 * 60 * 60))) {
348               
349                $charts['this_month'][2]+=$paidlog['amount'];
350            }
351            if (($paidlog['time'] < $thismonth + (28 * 24 * 60 * 60)) && ($paidlog['time'] > $thismonth + (21 * 24 * 60 * 60))) {
352               
353                $charts['this_month'][3]+=$paidlog['amount'];
354            }
355            if (($paidlog['time'] < $thismonth + (31 * 24 * 60 * 60)) && ($paidlog['time'] > $thismonth + (28 * 24 * 60 * 60))) {
356               
357                $charts['this_month'][4]+=$paidlog['amount'];
358            }
359            if (($paidlog['time'] < $thismonth + (31 * 24 * 60 * 60)) && ($paidlog['time'] > $thismonth )) {
360                $top['this_month']['user']++;
361                if ($paidlog['paid_type']==1)
362                {
363                   $top['this_month']['sms']+=$paidlog['amount'];
364                }
365                if ($paidlog['paid_type']==2)
366                {
367                    $top['this_month']['card']+=$paidlog['amount'];
368                }
369                if ($paidlog['collaborator']!="")
370                {
371                    $top['this_month']['collaborator']+=$paidlog['amount'];
372                }
373            }
374        }
375     
376        $percent=array();
377        foreach ($top['this_month'] as $index=>$value)
378        {
379            $percent[$index]['class']="down";
380            $percent[$index]['display']="giảm";
381            $percent[$index]['value']="n/a";
382            if (($top['this_month'][$index]>0)&&($top['last_month'][$index]>0))
383            {
384            $percent[$index]['value']=round((($top['this_month'][$index] / $top['last_month'][$index])*100),1);
385            if ($top['this_month'][$index] > $top['last_month'][$index])
386            {
387               $percent[$index]['class']="up";
388               $percent[$index]['display']="tăng";
389            }
390            }
391        }
392        $data['percent']=$percent;
393        $data['top']=$top;
394        $max = 0;
395        foreach ($charts['last_month'] as $index => $total) {
396            if ($total > $max) {
397                $max = $total;
398            }
399            $chart_last_month[] = array($index, $total);
400        }
401        foreach ($charts['this_month'] as $index => $total) {
402            if ($total > $max) {
403                $max = $total;
404            }
405            $chart_this_month[] = array($index, $total);
406        }
407
408        $data['revenue'] = $revenue;
409        $data['max'] = $max;
410        $chard[] = array("color" => "#e67e22", "label" => "Tháng hiện tại", "data" => $chart_this_month);
411        $chard[] = array("color" => "#1abc9c", "label" => "Tháng trước", "data" => $chart_last_month);
412         
413        $data['chard'] = $chard;
414       
415        return $data;
416    }
417    public function getLastquarter() {
418        $current_month = date('m');
419        $current_year = date('Y');
420
421        if ($current_month >= 1 && $current_month <= 3) {
422            $start_date = strtotime('1-October-' . ($current_year - 1));  // timestamp or 1-October Last Year 12:00:00 AM
423            $end_date = strtotime('1-Janauary-' . $current_year);  // // timestamp or 1-January  12:00:00 AM means end of 31 December Last year
424        } else if ($current_month >= 4 && $current_month <= 6) {
425            $start_date = strtotime('1-January-' . $current_year);  // timestamp or 1-Janauray 12:00:00 AM
426            $end_date = strtotime('1-April-' . $current_year);  // timestamp or 1-April 12:00:00 AM means end of 31 March
427        } else if ($current_month >= 7 && $current_month <= 9) {
428            $start_date = strtotime('1-April-' . $current_year);  // timestamp or 1-April 12:00:00 AM
429            $end_date = strtotime('1-July-' . $current_year);  // timestamp or 1-July 12:00:00 AM means end of 30 June
430        } else if ($current_month >= 10 && $current_month <= 12) {
431            $start_date = strtotime('1-July-' . $current_year);  // timestamp or 1-July 12:00:00 AM
432            $end_date = strtotime('1-October-' . $current_year);  // timestamp or 1-October 12:00:00 AM means end of 30 September
433        }
434        return array("start" => $start_date, "end" => $end_date);
435    }
436
437}
Note: See TracBrowser for help on using the repository browser.