Projekt

Allgemein

Profil

GX-Bug #39855 » campaigns.php

Till Tepelmann, 07.05.2014 09:29

 
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

    
    (1-1/1)