/admin/includes/classes/campaigns.php

https://bitbucket.org/flth/xtcm · PHP · 355 lines · 224 code · 94 blank · 37 comment · 32 complexity · 0afa9c602c03c0455e4b71335ef98181 MD5 · raw file 

    

  1. <?php
    2. 

  2. 

  3. /* -----------------------------------------------------------------------------------------
    4. 

  4.    $Id: campaigns.php 1180 2007-04-23 11:13:00Z Hetfield $
    5. 

    6. 

  6.    XT-Commerce - community made shopping
    7. 

  7.    http://www.xt-commerce.com
    8. 

    9. 

  9.    Copyright (c) 2005 XT-Commerce
    10. 

  10.    -----------------------------------------------------------------------------------------
    11. 

  11.    based on:
    12. 

  12.    (c) 2000-2001 The Exchange Project  (earlier name of osCommerce)
    13. 

  13.    (c) 2002-2003 osCommerce coding standards; www.oscommerce.com
    14. 

  14.    
    15. 

  15.    Released under the GNU General Public License
    16. 

  16.    -----------------------------------------------------------------------------------------
    17. 

  17.    Third Party contribution:
    18. 

  18.    (c) 2007 Hits for weekly, monthly and yearly for campaigns-report by Hetfield
    19. 

    20. 

  20.    Released under the GNU General Public License
    21. 

  21.    ---------------------------------------------------------------------------------------*/
    22. 

  22. 

  23. class campaigns {
    24. 

  24. 

  25. 	function campaigns(& $get_array) {
    26. 

  26. 		global $currencies;
    27. 

  27. 

  28. 		if (count($get_array) == 9) {
    29. 

  29. 

  30. 			$this->startD = $get_array['startD'];
    31. 

  31. 			$this->startM = $get_array['startM'];
    32. 

  32. 			$this->startY = $get_array['startY'];
    33. 

  33. 			$this->startDate = mktime(0, 0, 0, $this->startM, $this->startD, $this->startY);
    34. 

  34. 			$this->endD = $get_array['endD'];
    35. 

  35. 			$this->endM = $get_array['endM'];
    36. 

  36. 			$this->endY = $get_array['endY'];
    37. 

  37. 			$this->endDate = mktime(0, 0, 0, $this->endM, $this->endD, $this->endY);
    38. 

  38. 			$this->status = $get_array['status'];
    39. 

  39. 			$this->campaign = $get_array['campaign'];
    40. 

  40. 			$this->campaigns = $this->getCampaigns();
    41. 

  41. 

  42. 			if ($get_array['campaign'] == "0") {
    43. 

  43. 				$this->SelectArray = $this->campaigns;
    44. 

  44. 

  45. 			} else {
    46. 

  46. 				$this->SelectArray = $this->getSelectedCampaign();
    47. 

  47. 			}
    48. 

  48. 			$this->type = $get_array['report'];
    49. 

  49. 

  50. 			$this->result = array ();
    51. 

  51. 			$this->total = array ();
    52. 

  52. 

  53. 			// query data
    54. 

  54. 

  55. 			$this->counter = 0;
    56. 

  56. 			$this->counterCMP = 0;
    57. 

  57. 			// tŠglich
    58. 

  58. 

  59. 			$this->getTotalLeads();
    60. 

  60. 			$this->getTotalSells();
    61. 

  61. 

  62. 			for ($n = 0; $n < count($this->SelectArray); $n ++) {
    63. 

  63. 

  64. 				$this->campaign = $this->SelectArray[$n]['id'];
    65. 

  65. 				$this->result[$this->counterCMP]['id'] = $this->campaign;
    66. 

  66. 				$this->result[$this->counterCMP]['text'] = $this->camp[$this->campaign];
    67. 

  67. 

  68. 				switch ($this->type) {
    69. 

  69. 

  70. 					// yearly
    71. 

  71. 					case 1 :
    72. 

  72. 						$start = $this->startDate;
    73. 

  73. 

  74. 						while ($start <= $this->endDate) {
    75. 

  75. 

  76. 							$end = mktime(0, 0, 0, date("m", $start), date("d", $start), date("Y", $start) + 1);
    77. 

  77. 							// get Leads
    78. 

  78. 							$this->getLeads($start, $end, $this->type);
    79. 

  79. 							// get Sells
    80. 

  80. 							$this->getSells($start, $end, $this->type);
    81. 

  81. 							
    82. 

  82. 							$this->getHits($start, $end, $this->type);
    83. 

  83. 

  84. 							$start = $end;
    85. 

  85. 							$this->counter++;
    86. 

  86. 

  87. 						}
    88. 

  88. 						break;
    89. 

  89. 

  90. 						// monthly
    91. 

  91. 					case 2 :
    92. 

  92. 						$start = $this->startDate;
    93. 

  93. 

  94. 						while ($start <= $this->endDate) {
    95. 

  95. 

  96. 							$end = mktime(0, 0, 0, date("m", $start) + 1, date("d", $start), date("Y", $start));
    97. 

  97. 							// get Leads
    98. 

  98. 							$this->getLeads($start, $end, $this->type);
    99. 

  99. 							// get Sells
    100. 

  100. 							$this->getSells($start, $end, $this->type);
    101. 

  101. 							
    102. 

  102. 							$this->getHits($start, $end, $this->type);
    103. 

  103. 

  104. 							$start = $end;
    105. 

  105. 							$this->counter++;
    106. 

  106. 

  107. 						}
    108. 

  108. 

  109. 						break;
    110. 

  110. 

  111. 						// weekly
    112. 

  112. 					case 3 :
    113. 

  113. 						$start = $this->startDate;
    114. 

  114. 

  115. 						while ($start <= $this->endDate) {
    116. 

  116. 

  117. 							$end = mktime(0, 0, 0, date("m", $start), date("d", $start) + 7, date("Y", $start));
    118. 

  118. 							// get Leads
    119. 

  119. 							$this->getLeads($start, $end, $this->type);
    120. 

  120. 							// get Sells
    121. 

  121. 							$this->getSells($start, $end, $this->type);
    122. 

  122. 							
    123. 

  123. 							$this->getHits($start, $end, $this->type);
    124. 

  124. 

  125. 							$start = $end;
    126. 

  126. 							$this->counter++;
    127. 

  127. 

  128. 						}
    129. 

  129. 

  130. 						break;
    131. 

  131. 

  132. 						// daily
    133. 

  133. 					case 4 :
    134. 

  134. 						$start = $this->startDate;
    135. 

  135. 

  136. 						while ($start <= $this->endDate) {
    137. 

  137. 

  138. 							$end = mktime(0, 0, 0, date("m", $start), date("d", $start) + 1, date("Y", $start));
    139. 

  139. 							// get Leads
    140. 

  140. 							$this->getLeads($start, '', $this->type);
    141. 

  141. 							// get Sells
    142. 

  142. 							$this->getSells($start, '', $this->type);
    143. 

  143. 							
    144. 

  144. 							$this->getHits($start, '', $this->type);
    145. 

  145. 

  146. 							$start = $end;
    147. 

  147. 							$this->counter++;
    148. 

  148. 

  149. 						}
    150. 

  150. 						break;
    151. 

  151. 

  152. 				}
    153. 

  153. 				$this->counter = 0;
    154. 

  154. 				$this->counterCMP++;
    155. 

  155. 			}
    156. 

  156. 			//			$this->printResult();
    157. 

  157. 			$this->total['sum_plain'] = $this->total['sum'];
    158. 

  158. 			$this->total['sum'] = $currencies->format($this->total['sum']);
    159. 

  159. 			
    160. 

  160. 		}
    161. 

  161. 

  162. 	}
    163. 

  163. 

  164. 	function getCampaigns() {
    165. 

  165. 

  166. 		$campaign = array ();
    167. 

  167. 		$campaign_query = "SELECT * FROM ".TABLE_CAMPAIGNS;
    168. 

  168. 		$campaign_query = xtc_db_query($campaign_query);
    169. 

  169. 		while ($campaign_data = xtc_db_fetch_array($campaign_query)) {
    170. 

  170. 			$campaign[] = array ('id' => $campaign_data['campaigns_refID'], 'text' => $campaign_data['campaigns_name']);
    171. 

  171. 			$this->camp[$campaign_data['campaigns_refID']] = $campaign_data['campaigns_name'];
    172. 

  172. 		}
    173. 

  173. 		return $campaign;
    174. 

  174. 	}
    175. 

  175. 

  176. 	function getSelectedCampaign() {
    177. 

  177. 

  178. 		$campaign = array ();
    179. 

  179. 		$campaign_query = "SELECT * FROM ".TABLE_CAMPAIGNS." WHERE campaigns_refID='".$this->campaign."'";
    180. 

  180. 		$campaign_query = xtc_db_query($campaign_query);
    181. 

  181. 		while ($campaign_data = xtc_db_fetch_array($campaign_query)) {
    182. 

  182. 			$campaign[] = array ('id' => $campaign_data['campaigns_refID'], 'text' => $campaign_data['campaigns_name']);
    183. 

  183. 

  184. 		}
    185. 

  185. 		return $campaign;
    186. 

  186. 	}
    187. 

  187. 

  188. 	function getTotalLeads() {
    189. 

  189. 		$end = mktime(0, 0, 0, date("m", $this->endDate), date("d", $this->endDate) + 1, date("Y", $this->endDate));
    190. 

  190. 		$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))."'";
    191. 

  191. 

  192. 		$lead_query = "SELECT count(*) as leads FROM ".TABLE_CUSTOMERS." c, ".TABLE_CUSTOMERS_INFO." ci WHERE c.customers_id=ci.customers_info_id".$selection;
    193. 

  193. 		$lead_query = xtc_db_query($lead_query);
    194. 

  194. 		$lead_data = xtc_db_fetch_array($lead_query);
    195. 

  195. 

  196. 		$this->total['leads'] = $lead_data['leads'];
    197. 

  197. 

  198. 	}
    199. 

  199. 

  200. 	function getTotalSells() {
    201. 

  201. 		$end = mktime(0, 0, 0, date("m", $this->endDate), date("d", $this->endDate) + 1, date("Y", $this->endDate));
    202. 

  202. 		$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))."'";
    203. 

  203. 		$status = "";
    204. 

  204. 		if ($this->status > 0)
    205. 

  205. 			$status = " and o.orders_status='".$this->status."'";
    206. 

  206. 		$sale_query = "SELECT count(*) as sells, SUM(ot.value/o.currency_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;
    207. 

  207. 		$sale_query = xtc_db_query($sale_query);
    208. 

  208. 		$sale_data = xtc_db_fetch_array($sale_query);
    209. 

  209. 

  210. 		$this->total['sells'] = $sale_data['sells'];
    211. 

  211. 		$this->total['sum'] = $sale_data['Summe'];
    212. 

  212. 	}
    213. 

  213. 

  214. 	function getSells($date_start, $date_end = '', $type) {
    215. 

  215. 		global $currencies;
    216. 

  216. 

  217. 		switch ($type) {
    218. 

  218. 

  219. 			case 1 :
    220. 

  220. 			case 2 :
    221. 

  221. 			case 3 :
    222. 

  222. 				$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))."'";
    223. 

  223. 

  224. 				break;
    225. 

  225. 

  226. 				// daily
    227. 

  227. 			case 4 :
    228. 

  228. 				$end = mktime(0, 0, 0, date("m", $date_start), date("d", $date_start) + 1, date("Y", $date_start));
    229. 

  229. 				$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))."'";
    230. 

  230. 				break;
    231. 

  231. 

  232. 		}
    233. 

  233. 

  234. 		$status = "";
    235. 

  235. 		if ($this->status > 0)
    236. 

  236. 			$status = " and o.orders_status='".$this->status."'";
    237. 

  237. 		$sell_query = "SELECT count(*) as sells, SUM(ot.value/o.currency_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;
    238. 

  238. 		$sell_query = xtc_db_query($sell_query);
    239. 

  239. 		$sell_data = xtc_db_fetch_array($sell_query);
    240. 

  240. 

  241. 		$late_sell_query = "SELECT count(*) as sells, SUM(ot.value/o.currency_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;
    242. 

  242. 		$late_sell_query = xtc_db_query($late_sell_query);
    243. 

  243. 		$late_sell_data = xtc_db_fetch_array($late_sell_query);
    244. 

  244. 

  245. 

  246. 		$this->result[$this->counterCMP]['result'][$this->counter]['sells'] = $sell_data['sells'];
    247. 

  247. 		$this->result[$this->counterCMP]['result'][$this->counter]['sum'] =  $currencies->format(($sell_data['Summe']+$late_sell_data['Summe']));
    248. 

  248. 		$this->result[$this->counterCMP]['sells_s'] += $sell_data['sells'];
    249. 

  249. 		$this->result[$this->counterCMP]['sum_s'] += ($sell_data['Summe']+$late_sell_data['Summe']);
    250. 

  250. 		if ($this->total['sells'] == 0) {
    251. 

  251. 			$this->result[$this->counterCMP]['result'][$this->counter]['sells_p'] = 0;
    252. 

  252. 			$this->result[$this->counterCMP]['result'][$this->counter]['late_sells_p'] = 0;
    253. 

  253. 			$this->result[$this->counterCMP]['result'][$this->counter]['sum_p'] = 0;
    254. 

  254. 		} else {
    255. 

  255. 			$this->result[$this->counterCMP]['result'][$this->counter]['sells_p'] = $sell_data['sells'] / $this->total['sells'] * 100;
    256. 

  256. 			$this->result[$this->counterCMP]['result'][$this->counter]['late_sells_p'] = round($late_sell_data['sells'] / $this->total['sells'] * 100,2);
    257. 

  257. 			$this->result[$this->counterCMP]['result'][$this->counter]['sum_p'] = round(($sell_data['Summe']+$late_sell_data['Summe'])/$this->total['sum']*100,2);
    258. 

  258. 		}
    259. 

  259. 		$this->result[$this->counterCMP]['result'][$this->counter]['late_sells'] = $late_sell_data['sells'];
    260. 

  260. 		$this->result[$this->counterCMP]['late_sells_s'] += $late_sell_data['sells'];
    261. 

  261. 

  262. 	}
    263. 

  263. 

  264. 	function getLeads($date_start, $date_end = '', $type) {
    265. 

  265. 

  266. 		switch ($type) {
    267. 

  267. 

  268. 			case 1 :
    269. 

  269. 			case 2 :
    270. 

  270. 			case 3 :
    271. 

  271. 				$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))."'";
    272. 

  272. 

  273. 				break;
    274. 

  274. 

  275. 			case 4 :
    276. 

  276. 				$end = mktime(0, 0, 0, date("m", $date_start), date("d", $date_start) + 1, date("Y", $date_start));
    277. 

  277. 				$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))."'";
    278. 

  278. 

  279. 				break;
    280. 

  280. 

  281. 		}
    282. 

  282. 

  283. 		// select leads
    284. 

  284. 		$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;
    285. 

  285. 		$lead_query = xtc_db_query($lead_query);
    286. 

  286. 		$lead_data = xtc_db_fetch_array($lead_query);
    287. 

  287. 

  288. 		$this->result[$this->counterCMP]['result'][$this->counter]['range'] = $this->getDateFormat($date_start, $date_end);
    289. 

  289. 		$this->result[$this->counterCMP]['result'][$this->counter]['leads'] = $lead_data['leads'];
    290. 

  290. 		$this->result[$this->counterCMP]['leads_s'] += $lead_data['leads'];
    291. 

  291. 		if ($this->total['leads'] == 0) {
    292. 

  292. 			$this->result[$this->counterCMP]['result'][$this->counter]['leads_p'] = 0;
    293. 

  293. 		} else {
    294. 

  294. 			$this->result[$this->counterCMP]['result'][$this->counter]['leads_p'] = $lead_data['leads'] / $this->total['leads'] * 100;
    295. 

  295. 		}
    296. 

  296. 	}
    297. 

  297. 	
    298. 

  298. 	function getHits($date_start, $date_end = '', $type) {
    299. 

  299. 

  300. 		switch ($type) {
    301. 

  301. 

  302. 			case 1 :
    303. 

  303. 			case 2 :
    304. 

  304. 			case 3 :
    305. 

  305. 				$selection = " and time>'".xtc_db_input(date("Y-m-d", $date_start))."'"." and time <'".xtc_db_input(date("Y-m-d", $date_end))."'";
    306. 

  306. 

  307. 				break;
    308. 

  308. 

  309. 			case 4 :
    310. 

  310. 				$end = mktime(0, 0, 0, date("m", $date_start), date("d", $date_start) + 1, date("Y", $date_start));
    311. 

  311. 				$selection = " and time>'".xtc_db_input(date("Y-m-d", $date_start))."'"." and time<'".xtc_db_input(date("Y-m-d", $end))."'";
    312. 

  312. 

  313. 				break;
    314. 

  314. 

  315. 		}
    316. 

  316. 

  317. 		// select leads
    318. 

  318. 		$hits_query = "SELECT count(*) as hits FROM ".TABLE_CAMPAIGNS_IP."  WHERE campaign='".$this->campaign."'".$selection;
    319. 

  319. 		$hits_query = xtc_db_query($hits_query);
    320. 

  320. 		$hits_data = xtc_db_fetch_array($hits_query);
    321. 

  321. 

  322. 		$this->result[$this->counterCMP]['result'][$this->counter]['hits'] = $hits_data['hits'];
    323. 

  323. 		$this->result[$this->counterCMP]['hits_s'] += $hits_data['hits'];
    324. 

  324. 		if ($this->total['hits'] == 0) {
    325. 

  325. 			$this->result[$this->counterCMP]['result'][$this->counter]['hits_s'] = 0;
    326. 

  326. 		} else {
    327. 

  327. 			$this->result[$this->counterCMP]['result'][$this->counter]['hits_s'] = $hits_data['hits'] / $this->total['hits'] * 100;
    328. 

  328. 		}
    329. 

  329. 	}
    330. 

  330. 

  331. 	function getDateFormat($date_from, $date_to) {
    332. 

  332. 

  333. 		if ($date_from != $date_to && $date_to != '') {
    334. 

  334. 			return date(DATE_FORMAT, $date_from).'-'.date(DATE_FORMAT, $date_to);
    335. 

  335. 		} else {
    336. 

  336. 			return date(DATE_FORMAT, $date_from);
    337. 

  337. 		}
    338. 

  338. 

  339. 	}
    340. 

  340. 

  341. 	function printResult() {
    342. 

  342. 		echo '<pre>';
    343. 

  343. 		print_r($this->result);
    344. 

  344. 

  345. 		print_r($this->total);
    346. 

  346. 

  347. 		echo '</pre>';
    348. 

  348. 	}
    349. 

  349. 

  350. }
    351. 

  351. ?>
    352.