|
1
|
<?php
|
|
2
|
|
|
3
|
/* --------------------------------------------------------------
|
|
4
|
$Id: campaigns.php 1176 2005-08-22 21:44:28Z mz $
|
|
5
|
|
|
6
|
XT-Commerce - community made shopping
|
|
7
|
http://www.xt-commerce.com
|
|
8
|
|
|
9
|
Copyright (c) 2005 XT-Commerce
|
|
10
|
--------------------------------------------------------------
|
|
11
|
based on:
|
|
12
|
(c) 2000-2001 The Exchange Project (earlier name of osCommerce)
|
|
13
|
(c) 2002-2003 osCommerce coding standards; www.oscommerce.com
|
|
14
|
|
|
15
|
Released under the GNU General Public License
|
|
16
|
--------------------------------------------------------------*/
|
|
17
|
|
|
18
|
class campaigns {
|
|
19
|
|
|
20
|
function campaigns(& $get_array) {
|
|
21
|
global $currencies;
|
|
22
|
|
|
23
|
if (count($get_array) == 9) {
|
|
24
|
|
|
25
|
$this->startD = $get_array['startD'];
|
|
26
|
$this->startM = $get_array['startM'];
|
|
27
|
$this->startY = $get_array['startY'];
|
|
28
|
$this->startDate = mktime(0, 0, 0, $this->startM, $this->startD, $this->startY);
|
|
29
|
$this->endD = $get_array['endD'];
|
|
30
|
$this->endM = $get_array['endM'];
|
|
31
|
$this->endY = $get_array['endY'];
|
|
32
|
$this->endDate = mktime(0, 0, 0, $this->endM, $this->endD, $this->endY);
|
|
33
|
$this->status = $get_array['status'];
|
|
34
|
$this->campaign = $get_array['campaign'];
|
|
35
|
$this->campaigns = $this->getCampaigns();
|
|
36
|
|
|
37
|
if ($get_array['campaign'] == "0") {
|
|
38
|
$this->SelectArray = $this->campaigns;
|
|
39
|
|
|
40
|
} else {
|
|
41
|
$this->SelectArray = $this->getSelectedCampaign();
|
|
42
|
}
|
|
43
|
$this->type = $get_array['report'];
|
|
44
|
|
|
45
|
$this->result = array ();
|
|
46
|
$this->total = array ();
|
|
47
|
|
|
48
|
// query data
|
|
49
|
|
|
50
|
$this->counter = 0;
|
|
51
|
$this->counterCMP = 0;
|
|
52
|
// t?glich
|
|
53
|
|
|
54
|
$this->getTotalLeads();
|
|
55
|
$this->getTotalSells();
|
|
56
|
|
|
57
|
for ($n = 0; $n < count($this->SelectArray); $n ++) {
|
|
58
|
|
|
59
|
$this->campaign = $this->SelectArray[$n]['id'];
|
|
60
|
$this->result[$this->counterCMP]['id'] = $this->campaign;
|
|
61
|
$this->result[$this->counterCMP]['text'] = $this->camp[$this->campaign];
|
|
62
|
|
|
63
|
switch ($this->type) {
|
|
64
|
|
|
65
|
// yearly
|
|
66
|
case 1 :
|
|
67
|
$start = $this->startDate;
|
|
68
|
|
|
69
|
while ($start <= $this->endDate) {
|
|
70
|
|
|
71
|
$end = mktime(0, 0, 0, date("m", $start), date("d", $start), date("Y", $start) + 1);
|
|
72
|
// get Leads
|
|
73
|
$this->getLeads($start, $end, $this->type);
|
|
74
|
// get Sells
|
|
75
|
$this->getSells($start, $end, $this->type);
|
|
76
|
|
|
77
|
$this->getHits($start, $end, $this->type);
|
|
78
|
|
|
79
|
$start = $end;
|
|
80
|
$this->counter++;
|
|
81
|
|
|
82
|
}
|
|
83
|
break;
|
|
84
|
|
|
85
|
// monthly
|
|
86
|
case 2 :
|
|
87
|
$start = $this->startDate;
|
|
88
|
|
|
89
|
while ($start <= $this->endDate) {
|
|
90
|
|
|
91
|
$end = mktime(0, 0, 0, date("m", $start) + 1, date("d", $start), date("Y", $start));
|
|
92
|
// get Leads
|
|
93
|
$this->getLeads($start, $end, $this->type);
|
|
94
|
// get Sells
|
|
95
|
$this->getSells($start, $end, $this->type);
|
|
96
|
|
|
97
|
$this->getHits($start, $end, $this->type);
|
|
98
|
|
|
99
|
$start = $end;
|
|
100
|
$this->counter++;
|
|
101
|
|
|
102
|
}
|
|
103
|
|
|
104
|
break;
|
|
105
|
|
|
106
|
// weekly
|
|
107
|
case 3 :
|
|
108
|
$start = $this->startDate;
|
|
109
|
|
|
110
|
while ($start <= $this->endDate) {
|
|
111
|
|
|
112
|
$end = mktime(0, 0, 0, date("m", $start), date("d", $start) + 7, date("Y", $start));
|
|
113
|
// get Leads
|
|
114
|
$this->getLeads($start, $end, $this->type);
|
|
115
|
// get Sells
|
|
116
|
$this->getSells($start, $end, $this->type);
|
|
117
|
|
|
118
|
$this->getHits($start, $end, $this->type);
|
|
119
|
|
|
120
|
$start = $end;
|
|
121
|
$this->counter++;
|
|
122
|
|
|
123
|
}
|
|
124
|
|
|
125
|
break;
|
|
126
|
|
|
127
|
// daily
|
|
128
|
case 4 :
|
|
129
|
$start = $this->startDate;
|
|
130
|
|
|
131
|
while ($start <= $this->endDate) {
|
|
132
|
|
|
133
|
$end = mktime(0, 0, 0, date("m", $start), date("d", $start) + 1, date("Y", $start));
|
|
134
|
// get Leads
|
|
135
|
$this->getLeads($start, '', $this->type);
|
|
136
|
// get Sells
|
|
137
|
$this->getSells($start, '', $this->type);
|
|
138
|
|
|
139
|
$this->getHits($start, '', $this->type);
|
|
140
|
|
|
141
|
$start = $end;
|
|
142
|
$this->counter++;
|
|
143
|
|
|
144
|
}
|
|
145
|
break;
|
|
146
|
|
|
147
|
}
|
|
148
|
$this->counter = 0;
|
|
149
|
$this->counterCMP++;
|
|
150
|
}
|
|
151
|
// $this->printResult();
|
|
152
|
$this->total['sum_plain'] = $this->total['sum'];
|
|
153
|
$this->total['sum'] = $currencies->format($this->total['sum']);
|
|
154
|
|
|
155
|
}
|
|
156
|
|
|
157
|
}
|
|
158
|
|
|
159
|
function getCampaigns() {
|
|
160
|
|
|
161
|
$campaign = array ();
|
|
162
|
$campaign_query = "SELECT * FROM ".TABLE_CAMPAIGNS;
|
|
163
|
$campaign_query = xtc_db_query($campaign_query);
|
|
164
|
while ($campaign_data = xtc_db_fetch_array($campaign_query)) {
|
|
165
|
$campaign[] = array ('id' => $campaign_data['campaigns_refID'], 'text' => $campaign_data['campaigns_name']);
|
|
166
|
$this->camp[$campaign_data['campaigns_refID']] = $campaign_data['campaigns_name'];
|
|
167
|
}
|
|
168
|
return $campaign;
|
|
169
|
}
|
|
170
|
|
|
171
|
function getSelectedCampaign() {
|
|
172
|
|
|
173
|
$campaign = array ();
|
|
174
|
$campaign_query = "SELECT * FROM ".TABLE_CAMPAIGNS." WHERE campaigns_refID='".$this->campaign."'";
|
|
175
|
$campaign_query = xtc_db_query($campaign_query);
|
|
176
|
while ($campaign_data = xtc_db_fetch_array($campaign_query)) {
|
|
177
|
$campaign[] = array ('id' => $campaign_data['campaigns_refID'], 'text' => $campaign_data['campaigns_name']);
|
|
178
|
|
|
179
|
}
|
|
180
|
return $campaign;
|
|
181
|
}
|
|
182
|
|
|
183
|
function getTotalLeads() {
|
|
184
|
$end = mktime(0, 0, 0, date("m", $this->endDate), date("d", $this->endDate) + 1, date("Y", $this->endDate));
|
|
185
|
$selection = " and ci.customers_info_date_account_created>'".xtc_db_input(date("Y-m-d", $this->startDate))."'"." and ci.customers_info_date_account_created<'".xtc_db_input(date("Y-m-d", $end))."'";
|
|
186
|
|
|
187
|
$lead_query = "SELECT count(*) as leads FROM ".TABLE_CUSTOMERS." c, ".TABLE_CUSTOMERS_INFO." ci WHERE c.customers_id=ci.customers_info_id".$selection;
|
|
188
|
$lead_query = xtc_db_query($lead_query);
|
|
189
|
$lead_data = xtc_db_fetch_array($lead_query);
|
|
190
|
|
|
191
|
$this->total['leads'] = $lead_data['leads'];
|
|
192
|
|
|
193
|
}
|
|
194
|
|
|
195
|
function getTotalSells() {
|
|
196
|
$end = mktime(0, 0, 0, date("m", $this->endDate), date("d", $this->endDate) + 1, date("Y", $this->endDate));
|
|
197
|
$selection = " and o.date_purchased>'".xtc_db_input(date("Y-m-d", $this->startDate))."'"." and o.date_purchased<'".xtc_db_input(date("Y-m-d", $end))."'";
|
|
198
|
$status = "";
|
|
199
|
if ($this->status > 0)
|
|
200
|
$status = " and o.orders_status='".$this->status."'";
|
|
201
|
$sale_query = "SELECT count(*) as sells, SUM(ot.value) as Summe FROM ".TABLE_ORDERS." o, ".TABLE_ORDERS_TOTAL." ot WHERE o.orders_id=ot.orders_id and ot.class='ot_total'".$selection.$status;
|
|
202
|
$sale_query = xtc_db_query($sale_query);
|
|
203
|
$sale_data = xtc_db_fetch_array($sale_query);
|
|
204
|
|
|
205
|
$this->total['sells'] = $sale_data['sells'];
|
|
206
|
$this->total['sum'] = $sale_data['Summe'];
|
|
207
|
}
|
|
208
|
|
|
209
|
function getSells($date_start, $date_end = '', $type) {
|
|
210
|
global $currencies;
|
|
211
|
|
|
212
|
switch ($type) {
|
|
213
|
|
|
214
|
case 1 :
|
|
215
|
case 2 :
|
|
216
|
case 3 :
|
|
217
|
$selection = " and o.date_purchased>'".xtc_db_input(date("Y-m-d", $date_start))."'"." and o.date_purchased<'".xtc_db_input(date("Y-m-d", $date_end))."'";
|
|
218
|
|
|
219
|
break;
|
|
220
|
|
|
221
|
// daily
|
|
222
|
case 4 :
|
|
223
|
$end = mktime(0, 0, 0, date("m", $date_start), date("d", $date_start) + 1, date("Y", $date_start));
|
|
224
|
$selection = " and o.date_purchased>'".xtc_db_input(date("Y-m-d", $date_start))."'"." and o.date_purchased<'".xtc_db_input(date("Y-m-d", $end))."'";
|
|
225
|
break;
|
|
226
|
|
|
227
|
}
|
|
228
|
|
|
229
|
$status = "";
|
|
230
|
if ($this->status > 0)
|
|
231
|
$status = " and o.orders_status='".$this->status."'";
|
|
232
|
$sell_query = "SELECT count(*) as sells, SUM(ot.value) as Summe FROM ".TABLE_ORDERS." o, ".TABLE_ORDERS_TOTAL." ot WHERE o.orders_id=ot.orders_id and ot.class='ot_total' and o.conversion_type='1' and o.refferers_id='".$this->campaign."'".$selection.$status;
|
|
233
|
$sell_query = xtc_db_query($sell_query);
|
|
234
|
$sell_data = xtc_db_fetch_array($sell_query);
|
|
235
|
|
|
236
|
$late_sell_query = "SELECT count(*) as sells, SUM(ot.value) as Summe FROM ".TABLE_ORDERS." o, ".TABLE_ORDERS_TOTAL." ot WHERE o.orders_id=ot.orders_id and ot.class='ot_total' and o.conversion_type='2' and o.refferers_id='".$this->campaign."'".$selection.$status;
|
|
237
|
$late_sell_query = xtc_db_query($late_sell_query);
|
|
238
|
$late_sell_data = xtc_db_fetch_array($late_sell_query);
|
|
239
|
|
|
240
|
|
|
241
|
$this->result[$this->counterCMP]['result'][$this->counter]['sells'] = $sell_data['sells'];
|
|
242
|
$this->result[$this->counterCMP]['result'][$this->counter]['sum'] = $currencies->format(($sell_data['Summe']+$late_sell_data['Summe']));
|
|
243
|
$this->result[$this->counterCMP]['sells_s'] += $sell_data['sells'];
|
|
244
|
$this->result[$this->counterCMP]['sum_s'] += ($sell_data['Summe']+$late_sell_data['Summe']);
|
|
245
|
if ($this->total['sells'] == 0) {
|
|
246
|
$this->result[$this->counterCMP]['result'][$this->counter]['sells_p'] = 0;
|
|
247
|
$this->result[$this->counterCMP]['result'][$this->counter]['late_sells_p'] = 0;
|
|
248
|
$this->result[$this->counterCMP]['result'][$this->counter]['sum_p'] = 0;
|
|
249
|
} else {
|
|
250
|
$this->result[$this->counterCMP]['result'][$this->counter]['sells_p'] = $sell_data['sells'] / $this->total['sells'] * 100;
|
|
251
|
$this->result[$this->counterCMP]['result'][$this->counter]['late_sells_p'] = $late_sell_data['sells'] / $this->total['sells'] * 100;
|
|
252
|
$this->result[$this->counterCMP]['result'][$this->counter]['sum_p'] = round(($sell_data['Summe']+$late_sell_data['Summe'])/$this->total['sum']*100,2);
|
|
253
|
}
|
|
254
|
$this->result[$this->counterCMP]['result'][$this->counter]['late_sells'] = $late_sell_data['sells'];
|
|
255
|
$this->result[$this->counterCMP]['late_sells_s'] += $late_sell_data['sells'];
|
|
256
|
|
|
257
|
}
|
|
258
|
|
|
259
|
function getLeads($date_start, $date_end = '', $type) {
|
|
260
|
|
|
261
|
switch ($type) {
|
|
262
|
|
|
263
|
case 1 :
|
|
264
|
case 2 :
|
|
265
|
case 3 :
|
|
266
|
$selection = " and ci.customers_info_date_account_created>'".xtc_db_input(date("Y-m-d", $date_start))."'"." and ci.customers_info_date_account_created<'".xtc_db_input(date("Y-m-d", $date_end))."'";
|
|
267
|
|
|
268
|
break;
|
|
269
|
|
|
270
|
case 4 :
|
|
271
|
$end = mktime(0, 0, 0, date("m", $date_start), date("d", $date_start) + 1, date("Y", $date_start));
|
|
272
|
$selection = " and ci.customers_info_date_account_created>'".xtc_db_input(date("Y-m-d", $date_start))."'"." and ci.customers_info_date_account_created<'".xtc_db_input(date("Y-m-d", $end))."'";
|
|
273
|
|
|
274
|
break;
|
|
275
|
|
|
276
|
}
|
|
277
|
|
|
278
|
// select leads
|
|
279
|
$lead_query = "SELECT count(*) as leads FROM ".TABLE_CUSTOMERS." c, ".TABLE_CUSTOMERS_INFO." ci WHERE c.customers_id=ci.customers_info_id AND c.refferers_id='".$this->campaign."'".$selection;
|
|
280
|
$lead_query = xtc_db_query($lead_query);
|
|
281
|
$lead_data = xtc_db_fetch_array($lead_query);
|
|
282
|
|
|
283
|
$this->result[$this->counterCMP]['result'][$this->counter]['range'] = $this->getDateFormat($date_start, $date_end);
|
|
284
|
$this->result[$this->counterCMP]['result'][$this->counter]['leads'] = $lead_data['leads'];
|
|
285
|
$this->result[$this->counterCMP]['leads_s'] += $lead_data['leads'];
|
|
286
|
if ($this->total['leads'] == 0) {
|
|
287
|
$this->result[$this->counterCMP]['result'][$this->counter]['leads_p'] = 0;
|
|
288
|
} else {
|
|
289
|
$this->result[$this->counterCMP]['result'][$this->counter]['leads_p'] = $lead_data['leads'] / $this->total['leads'] * 100;
|
|
290
|
}
|
|
291
|
}
|
|
292
|
|
|
293
|
function getHits($date_start, $date_end = '', $type) {
|
|
294
|
|
|
295
|
switch ($type) {
|
|
296
|
|
|
297
|
case 1 :
|
|
298
|
case 2 :
|
|
299
|
case 3 :
|
|
300
|
$selection = " and time>'".xtc_db_input(date("Y-m-d", $date_start))."'"." and time <'".xtc_db_input(date("Y-m-d", $date_end))."'";
|
|
301
|
|
|
302
|
break;
|
|
303
|
|
|
304
|
case 4 :
|
|
305
|
$end = mktime(0, 0, 0, date("m", $date_start), date("d", $date_start) + 1, date("Y", $date_start));
|
|
306
|
$selection = " and time>'".xtc_db_input(date("Y-m-d", $date_start))."'"." and time<'".xtc_db_input(date("Y-m-d", $end))."'";
|
|
307
|
|
|
308
|
break;
|
|
309
|
|
|
310
|
}
|
|
311
|
|
|
312
|
// select leads
|
|
313
|
$hits_query = "SELECT count(*) as hits FROM ".TABLE_CAMPAIGNS_IP." WHERE campaign='".$this->campaign."'".$selection;
|
|
314
|
$hits_query = xtc_db_query($hits_query);
|
|
315
|
$hits_data = xtc_db_fetch_array($hits_query);
|
|
316
|
|
|
317
|
$this->result[$this->counterCMP]['result'][$this->counter]['hits'] = $hits_data['hits'];
|
|
318
|
$this->result[$this->counterCMP]['hits_s'] += $hits_data['hits'];
|
|
319
|
if ($this->total['leads'] == 0) {
|
|
320
|
$this->result[$this->counterCMP]['result'][$this->counter]['leads_p'] = 0;
|
|
321
|
} else {
|
|
322
|
$this->result[$this->counterCMP]['result'][$this->counter]['leads_p'] = $lead_data['leads'] / $this->total['leads'] * 100;
|
|
323
|
}
|
|
324
|
}
|
|
325
|
|
|
326
|
function getDateFormat($date_from, $date_to) {
|
|
327
|
|
|
328
|
if ($date_from != $date_to && $date_to != '') {
|
|
329
|
return date(DATE_FORMAT, $date_from).'-'.date(DATE_FORMAT, $date_to);
|
|
330
|
} else {
|
|
331
|
return date(DATE_FORMAT, $date_from);
|
|
332
|
}
|
|
333
|
|
|
334
|
}
|
|
335
|
|
|
336
|
function printResult() {
|
|
337
|
echo '<pre>';
|
|
338
|
print_r($this->result);
|
|
339
|
|
|
340
|
print_r($this->total);
|
|
341
|
|
|
342
|
echo '</pre>';
|
|
343
|
}
|
|
344
|
|
|
345
|
}
|
|
346
|
?>
|
|
347
|
|
|
348
|
|
|
349
|
|
|
350
|
|