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

Last change on this file since 737 was 737, checked in by dungnv, 10 years ago
File size: 21.4 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
59                $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."";
60
61        $result = $this->db->query($sql)->result_array();
62        foreach ($result as $index=>$paidlog)
63        {
64            $result[$index]['time']=  strtotime($paidlog['user_created_time']);
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        $data = array();
241        $last_month['start'] = strtotime(date("Y-m-d", strtotime("first day of previous month")) . " 00:00:00");
242        $last_month['end'] = strtotime(date("Y-m-d", strtotime("last day of previous month")) . " 23:59:59");
243
244        $last_week['start'] = strtotime(date("Y-m-d", strtotime("first day of previous week")) . " 00:00:00");
245        $last_week['end'] = strtotime(date("Y-m-d", strtotime("last day of previous week")) . " 23:59:59");
246
247        $last_week['end'] = strtotime('last sunday') + (24 * 60 * 60 - 1);
248        $last_week['start'] = ($last_week['end'] - 7 * 24 * 60 * 60 + 1);
249
250        $last_year['start'] = strtotime((date("Y") - 1) . "-01-01 00:00:00");
251        $last_year['end'] = strtotime((date("Y") - 1) . "-12-31 23:59:59");
252
253
254
255        $last_quarter = $this->getLastquarter();
256        $last_quarter['start'] = $last_quarter['start'];
257        $last_quarter['end'] = $last_quarter['end'];
258
259
260        $this->load->model('paidlog_model');
261        $paidlogs = $this->paidlog_model->getAllPaidlogs($last_year['start']);
262
263        $revenue['last_week'] = 0;
264        $revenue['last_month'] = 0;
265        $revenue['last_quarter'] = 0;
266        $revenue['last_year'] = 0;
267
268        $lastmonth = strtotime("first day of previous month");
269        $thismonth = strtotime("first day of this month");
270        $charts['last_month'] = array(0 => 0, 1 => 0, 2 => 0, 3 => 0, 4 => 0);
271        $charts['this_month'] = array(0 => 0, 1 => 0, 2 => 0, 3 => 0, 4 => 0);
272       
273        $top['last_month']['user']=0;
274        $top['last_month']['sms']=0;
275        $top['last_month']['card']=0;
276        $top['last_month']['collaborator']=0;
277        $top['this_month']['user']=0;
278        $top['this_month']['sms']=0;
279        $top['this_month']['card']=0;
280        $top['this_month']['collaborator']=0;
281        foreach ($paidlogs as $paidlog) {
282
283           
284            if (($paidlog['time'] < $last_week['end']) && ($paidlog['time'] > $last_week['start'])) {
285                $revenue['last_week']+=$paidlog['amount'];
286            }
287            if (($paidlog['time'] < $last_month['end']) && ($paidlog['time'] > $last_month['start'])) {
288                $revenue['last_month']+=$paidlog['amount'];
289            }
290            if (($paidlog['time'] < $last_quarter['end']) && ($paidlog['time'] > $last_quarter['start'])) {
291                $revenue['last_quarter']+=$paidlog['amount'];
292            }
293            if (($paidlog['time'] < $last_year['end']) && ($paidlog['time'] > $last_year['start'])) {
294                $revenue['last_year']+=$paidlog['amount'];
295            }
296           
297           
298            if (($paidlog['time'] < $lastmonth + (7 * 24 * 60 * 60)) && ($paidlog['time'] > $lastmonth)) {
299                $charts['last_month'][0]+=$paidlog['amount'];
300            }
301            if (($paidlog['time'] < $lastmonth + (14 * 24 * 60 * 60)) && ($paidlog['time'] > $lastmonth + (7 * 24 * 60 * 60))) {
302                $charts['last_month'][1]+=$paidlog['amount'];
303            }
304            if (($paidlog['time'] < $lastmonth + (21 * 24 * 60 * 60)) && ($paidlog['time'] > $lastmonth + (14 * 24 * 60 * 60))) {
305                $charts['last_month'][2]+=$paidlog['amount'];
306            }
307            if (($paidlog['time'] < $lastmonth + (28 * 24 * 60 * 60)) && ($paidlog['time'] > $lastmonth + (21 * 24 * 60 * 60))) {
308                $charts['last_month'][3]+=$paidlog['amount'];
309            }
310            if (($paidlog['time'] < $thismonth) && ($paidlog['time'] > $lastmonth + (28 * 24 * 60 * 60))) {
311                $charts['last_month'][4]+=$paidlog['amount'];
312            }
313           
314            if (($paidlog['time'] < $thismonth) && ($paidlog['time'] > $lastmonth)) {
315                $top['last_month']['user']++;
316                if ($paidlog['paid_type']==1)
317                {
318                   $top['last_month']['sms']+=$paidlog['amount'];
319                }
320                if ($paidlog['paid_type']==2)
321                {
322                    $top['last_month']['card']+=$paidlog['amount'];
323                }
324                if ($paidlog['collaborator']!="")
325                {
326                    $top['last_month']['collaborator']+=$paidlog['amount'];
327                }
328            }
329           
330           
331
332            if (($paidlog['time'] < $thismonth + (7 * 24 * 60 * 60)) && ($paidlog['time'] > $thismonth)) {
333                $charts['this_month'][0]+=$paidlog['amount'];
334            }
335            if (($paidlog['time'] < $thismonth + (14 * 24 * 60 * 60)) && ($paidlog['time'] > $thismonth + (7 * 24 * 60 * 60))) {
336                $charts['this_month'][1]+=$paidlog['amount'];
337            }
338            if (($paidlog['time'] < $thismonth + (21 * 24 * 60 * 60)) && ($paidlog['time'] > $thismonth + (14 * 24 * 60 * 60))) {
339                $charts['this_month'][2]+=$paidlog['amount'];
340            }
341            if (($paidlog['time'] < $thismonth + (28 * 24 * 60 * 60)) && ($paidlog['time'] > $thismonth + (21 * 24 * 60 * 60))) {
342                $charts['this_month'][3]+=$paidlog['amount'];
343            }
344            if (($paidlog['time'] < $thismonth + (31 * 24 * 60 * 60)) && ($paidlog['time'] > $thismonth + (28 * 24 * 60 * 60))) {
345                $charts['this_month'][4]+=$paidlog['amount'];
346            }
347            if (($paidlog['time'] < $thismonth + (31 * 24 * 60 * 60)) && ($paidlog['time'] > $thismonth )) {
348                $top['this_month']['user']++;
349                if ($paidlog['paid_type']==1)
350                {
351                   $top['this_month']['sms']+=$paidlog['amount'];
352                }
353                if ($paidlog['paid_type']==2)
354                {
355                    $top['this_month']['card']+=$paidlog['amount'];
356                }
357                if ($paidlog['collaborator']!="")
358                {
359                    $top['this_month']['collaborator']+=$paidlog['amount'];
360                }
361            }
362        }
363        $percent=array();
364        foreach ($top['this_month'] as $index=>$value)
365        {
366            $percent[$index]['class']="down";
367            $percent[$index]['display']="giảm";
368            $percent[$index]['value']="n/a";
369            if (($top['this_month'][$index]>0)&&($top['last_month'][$index]>0))
370            {
371            $percent[$index]['value']=round((($top['this_month'][$index] / $top['last_month'][$index])*100),1);
372            if ($top['this_month'][$index] > $top['last_month'][$index])
373            {
374               $percent[$index]['class']="up";
375               $percent[$index]['display']="tăng";
376            }
377            }
378        }
379        $data['percent']=$percent;
380        $data['top']=$top;
381        $max = 0;
382        foreach ($charts['last_month'] as $index => $total) {
383            if ($total > $max) {
384                $max = $total;
385            }
386            $chart_last_month[] = array($index, $total);
387        }
388        foreach ($charts['this_month'] as $index => $total) {
389            if ($total > $max) {
390                $max = $total;
391            }
392            $chart_this_month[] = array($index, $total);
393        }
394
395        $data['revenue'] = $revenue;
396        $data['max'] = 50000;
397        $chard[] = array("color" => "#e67e22", "label" => "Tháng hiện tại", "data" => $chart_this_month);
398        $chard[] = array("color" => "#1abc9c", "label" => "Tháng trước", "data" => $chart_last_month);
399
400        $data['chard'] = $chard;
401        return $data;
402    }
403    public function getLastquarter() {
404        $current_month = date('m');
405        $current_year = date('Y');
406
407        if ($current_month >= 1 && $current_month <= 3) {
408            $start_date = strtotime('1-October-' . ($current_year - 1));  // timestamp or 1-October Last Year 12:00:00 AM
409            $end_date = strtotime('1-Janauary-' . $current_year);  // // timestamp or 1-January  12:00:00 AM means end of 31 December Last year
410        } else if ($current_month >= 4 && $current_month <= 6) {
411            $start_date = strtotime('1-January-' . $current_year);  // timestamp or 1-Janauray 12:00:00 AM
412            $end_date = strtotime('1-April-' . $current_year);  // timestamp or 1-April 12:00:00 AM means end of 31 March
413        } else if ($current_month >= 7 && $current_month <= 9) {
414            $start_date = strtotime('1-April-' . $current_year);  // timestamp or 1-April 12:00:00 AM
415            $end_date = strtotime('1-July-' . $current_year);  // timestamp or 1-July 12:00:00 AM means end of 30 June
416        } else if ($current_month >= 10 && $current_month <= 12) {
417            $start_date = strtotime('1-July-' . $current_year);  // timestamp or 1-July 12:00:00 AM
418            $end_date = strtotime('1-October-' . $current_year);  // timestamp or 1-October 12:00:00 AM means end of 30 September
419        }
420        return array("start" => $start_date, "end" => $end_date);
421    }
422
423}
Note: See TracBrowser for help on using the repository browser.