| Line 71... |
Line 71... |
| 71 |
$date = $data['Report']['date']['year'].'-'.$data['Report']['date']['month'].'-'.$data['Report']['date']['day'];
|
71 |
$date = $data['Report']['date']['year'].'-'.$data['Report']['date']['month'].'-'.$data['Report']['date']['day'];
|
| 72 |
$otherSql="SELECT a.name, COUNT( r.id ) AS count FROM `retailerlinks` r LEFT JOIN agents a ON r.agent_id = a.id join users u on r.user_id=u.id WHERE DATE( r.activated ) = '$date' and r.user_id is not null and date(u.activation_time)='$date' GROUP BY a.id";
|
72 |
$otherSql="SELECT a.name, COUNT( r.id ) AS count FROM `retailerlinks` r LEFT JOIN agents a ON r.agent_id = a.id join users u on r.user_id=u.id WHERE DATE( r.activated ) = '$date' and r.user_id is not null and date(u.activation_time)='$date' GROUP BY a.id";
|
| 73 |
$sql = "SELECT DATE(activation_time ) AS date, utm_campaign,referrer , COUNT( id ) AS count FROM users WHERE DATE(activation_time) = '$date' AND (utm_campaign IS NOT NULL OR referrer IS NOT NULL) GROUP BY DATE(activation_time) ,utm_campaign,referrer";
|
73 |
$sql = "SELECT DATE(activation_time ) AS date, utm_campaign,referrer , COUNT( id ) AS count FROM users WHERE DATE(activation_time) = '$date' AND (utm_campaign IS NOT NULL OR referrer IS NOT NULL) GROUP BY DATE(activation_time) ,utm_campaign,referrer";
|
| 74 |
} else{
|
74 |
} else{
|
| 75 |
$otherSql="SELECT a.name, COUNT( r.id ) AS count FROM `retailerlinks` r LEFT JOIN agents a ON r.agent_id = a.id join users u on r.user_id=u.id WHERE DATE( r.activated ) = CURDATE( ) and r.user_id is not null and date(u.activation_time)=curdate() GROUP BY a.id";
|
75 |
$otherSql="SELECT a.name, COUNT( r.id ) AS count FROM `retailerlinks` r LEFT JOIN agents a ON r.agent_id = a.id join users u on r.user_id=u.id WHERE DATE( r.activated ) = CURDATE( ) and r.user_id is not null and date(u.activation_time)=curdate() GROUP BY a.id";
|
| 76 |
$sql = "SELECT DATE(activation_time) AS date, utm_campaign,referrer , COUNT( id ) AS count FROM users WHERE DATE(activation_time) = CURDATE() AND (utm_campaign IS NOT NULL OR referrer IS NOT NULL) GROUP BY DATE(activation_time) ,utm_campaign,referrer";
|
76 |
$sql = "SELECT DATE(activation_time) AS date, id, utm_campaign,referrer , COUNT( id ) AS count FROM users WHERE DATE(activation_time) = CURDATE() AND (utm_campaign IS NOT NULL OR referrer IS NOT NULL) GROUP BY DATE(activation_time) ,utm_campaign,referrer, id";
|
| 77 |
}
|
77 |
}
|
| 78 |
$this->loadModel('User');
|
78 |
$this->loadModel('User');
|
| 79 |
$activations = $this->User->query($sql);
|
79 |
$activations = $this->User->query($sql);
|
| 80 |
foreach ($activations as $key => $value) {
|
80 |
foreach ($activations as $key => $value) {
|
| 81 |
if(isset($value['users']['utm_campaign'])){
|
81 |
if(isset($value['users']['utm_campaign'])){
|
| 82 |
#debug($value['users']['utm_campaign']);
|
82 |
#debug($value['users']['utm_campaign']);
|
| 83 |
$val=$value['users']['utm_campaign'];
|
83 |
$val=$value['users']['id'];
|
| 84 |
$sql = "SELECT name from agents where id = (select agent_id from retailerlinks where code=upper('$val'));";
|
84 |
$sql = "SELECT name from agents where id = (select agent_id from retailerlinks where user_id=$val);";
|
| 85 |
$this->loadModel('Agent');
|
85 |
$this->loadModel('Agent');
|
| 86 |
$agentname = $this->Agent->query($sql);
|
86 |
$agentname = $this->Agent->query($sql);
|
| 87 |
$citySql = "SELECT city from retailers where id = (select retailer_id from retailerlinks where code=upper('$val'));";
|
87 |
$citySql = "SELECT city from retailers where id = (select retailer_id from retailerlinks where user_id=$val);";
|
| 88 |
$this->loadModel('Retailer');
|
88 |
$this->loadModel('Retailer');
|
| 89 |
$retailerCity = $this->Retailer->query($citySql);
|
89 |
$retailerCity = $this->Retailer->query($citySql);
|
| 90 |
array_push($activations[$key], $agentname[0]['agents']['name']);
|
90 |
array_push($activations[$key], $agentname[0]['agents']['name']);
|
| 91 |
array_push($activations[$key], $retailerCity[0]['retailers']['city']);
|
91 |
array_push($activations[$key], $retailerCity[0]['retailers']['city']);
|
| 92 |
}else{
|
92 |
}else{
|
| 93 |
#debug($value['users']['referrer']);
|
93 |
#debug($value['users']['referrer']);
|
| 94 |
$val=$value['users']['referrer'];
|
94 |
$val=$value['users']['id'];
|
| 95 |
$sql1 = "SELECT name from agents where id = (select agent_id from retailerlinks where code=upper('$val'));";
|
95 |
$sql1 = "SELECT name from agents where id = (select agent_id from retailerlinks where user_id=$val);";
|
| 96 |
$this->loadModel('Agent');
|
96 |
$this->loadModel('Agent');
|
| 97 |
$agentname = $this->Agent->query($sql1);
|
97 |
$agentname = $this->Agent->query($sql1);
|
| 98 |
$citySql = "SELECT city from retailers where id = (select retailer_id from retailerlinks where code=upper('$val'));";
|
98 |
$citySql = "SELECT city from retailers where id = (select retailer_id from retailerlinks where user_id=$val);";
|
| 99 |
$this->loadModel('Retailer');
|
99 |
$this->loadModel('Retailer');
|
| 100 |
$retailerCity = $this->Retailer->query($citySql);
|
100 |
$retailerCity = $this->Retailer->query($citySql);
|
| 101 |
array_push($activations[$key], $agentname[0]['agents']['name']);
|
101 |
array_push($activations[$key], $agentname[0]['agents']['name']);
|
| 102 |
array_push($activations[$key], $retailerCity[0]['retailers']['city']);
|
102 |
array_push($activations[$key], $retailerCity[0]['retailers']['city']);
|
| 103 |
}
|
103 |
}
|