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

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