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

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