OXID eShop CE  4.10.1
 All Classes Namespaces Files Functions Variables Pages
report_canceled_orders.php
Go to the documentation of this file.
1 <?php
2 
3 if (!class_exists('report_canceled_orders')) {
9  class Report_canceled_orders extends report_base
10  {
11 
17  protected $_sThisTemplate = "report_canceled_orders.tpl";
18 
24  public function drawReport()
25  {
26  $oDb = oxDb::getDb();
27 
28  $oSmarty = $this->getSmarty();
29  $sTimeFrom = $oDb->quote(date("Y-m-d H:i:s", strtotime($oSmarty->_tpl_vars['time_from'])));
30  $sTimeTo = $oDb->quote(date("Y-m-d H:i:s", strtotime($oSmarty->_tpl_vars['time_to'])));
31 
32  // collects sessions what executed 'order' function
33  $sSql = "select 1 from `oxlogs` where oxclass = 'order' and
34  oxfnc = 'execute' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo";
35  if ($oDb->getOne($sSql)) {
36  return true;
37  }
38 
39  // collects sessions what executed order class
40  $sSql = "select 1 from `oxlogs` where oxclass = 'order' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo";
41  if ($oDb->getOne($sSql)) {
42  return true;
43  }
44 
45  // collects sessions what executed payment class
46  $sSql = "select 1 from `oxlogs` where oxclass = 'payment' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo";
47  if ($oDb->getOne($sSql)) {
48  return true;
49  }
50 
51  // collects sessions what executed 'user' class
52  $sSql = "select 1 from `oxlogs` where oxclass = 'user' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo";
53  if ($oDb->getOne($sSql)) {
54  return true;
55  }
56 
57  // collects sessions what executed 'tobasket' function
58  $sSql = "select 1 from `oxlogs` where oxclass = 'basket' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo";
59  if ($oDb->getOne($sSql)) {
60  return true;
61  }
62 
63  // orders made
64  $sSql = "select 1 from oxorder where oxorderdate >= $sTimeFrom and oxorderdate <= $sTimeTo";
65  if ($oDb->getOne($sSql)) {
66  return true;
67  }
68  }
69 
77  protected function _collectSessions($sQ)
78  {
79  $aTempOrder = array();
80  $rs = oxDb::getDb()->execute($sQ);
81  if ($rs != false && $rs->recordCount() > 0) {
82  while (!$rs->EOF) {
83  $aTempOrder[$rs->fields[1]] = $rs->fields[0];
84  $rs->moveNext();
85  }
86  }
87 
88  return $aTempOrder;
89  }
90 
101  protected function _collectOrderSessions($sQ, $aTempOrder, &$aData, $blMonth = true)
102  {
103  // collects sessions what executed order class
104  $aTempExecOrdersSessions = array();
105  $rs = oxDb::getDb()->execute($sQ);
106  if ($rs != false && $rs->recordCount() > 0) {
107  $iFirstWeekDay = $this->getConfig()->getConfigParam('iFirstWeekDay');
108  while (!$rs->EOF) {
109  if (!isset($aTempOrder[$rs->fields[1]])) {
110  $aTempExecOrdersSessions[$rs->fields[1]] = 1;
111  $sKey = strtotime($rs->fields[0]);
113  $oUtilsData = oxRegistry::get("oxUtilsDate");
114  $sKey = $blMonth ? date("m/Y", $sKey) : $oUtilsData->getWeekNumber($iFirstWeekDay, $sKey);
115  if (isset($aData[$sKey])) {
116  $aData[$sKey]++;
117  }
118 
119  }
120  $rs->moveNext();
121  }
122  }
123 
124  return $aTempExecOrdersSessions;
125  }
126 
138  protected function _collectPaymentSessions(
139  $sQ,
140  $aTempOrder,
141  $aTempExecOrdersSessions,
142  &$aDataX2,
143  $blMonth = true
144  ) {
145  $aTempPaymentSessions = array();
146  $rs = oxDb::getDb()->execute($sQ);
147  if ($rs != false && $rs->recordCount() > 0) {
148  $iFirstWeekDay = $this->getConfig()->getConfigParam('iFirstWeekDay');
149  while (!$rs->EOF) {
150  if (!isset($aTempOrder[$rs->fields[1]]) && !isset($aTempExecOrdersSessions[$rs->fields[1]])) {
151  $aTempPaymentSessions[$rs->fields[1]] = 1;
152  $sKey = strtotime($rs->fields[0]);
153  $sKey = $blMonth ? date("m/Y", $sKey) : oxRegistry::get("oxUtilsDate")->getWeekNumber($iFirstWeekDay, $sKey);
154  if (isset($aDataX2[$sKey])) {
155  $aDataX2[$sKey]++;
156  }
157  }
158  $rs->moveNext();
159  }
160  }
161 
162  return $aTempPaymentSessions;
163  }
164 
177  protected function _collectUserSessionsForVisitorMonth($sQ, $aTempOrder, $aTempExecOrdersSessions, $aTempPaymentSessions, &$aDataX3, $blMonth = true)
178  {
179  $aTempUserSessions = array();
180  $rs = oxDb::getDb()->execute($sQ);
181  if ($rs != false && $rs->recordCount() > 0) {
182  $iFirstWeekDay = $this->getConfig()->getConfigParam('iFirstWeekDay');
183  while (!$rs->EOF) {
184  if (!isset($aTempOrder[$rs->fields[1]]) && !isset($aTempPaymentSessions[$rs->fields[1]]) && !isset($aTempExecOrdersSessions[$rs->fields[1]])) {
185  $aTempUserSessions[$rs->fields[1]] = 1;
186  $sKey = strtotime($rs->fields[0]);
187  $sKey = $blMonth ? date("m/Y", $sKey) : oxRegistry::get("oxUtilsDate")->getWeekNumber($iFirstWeekDay, $sKey);
188  if (isset($aDataX3[$sKey])) {
189  $aDataX3[$sKey]++;
190  }
191  }
192  $rs->moveNext();
193  }
194  }
195 
196  return $aTempUserSessions;
197  }
198 
210  protected function _collectToBasketSessions($sSql, $aTempOrder, $aTempExecOrdersSessions, $aTempPaymentSessions, $aTempUserSessions, &$aDataX4, $blMonth = true)
211  {
212  $rs = oxDb::getDb()->execute($sSql);
213  if ($rs != false && $rs->recordCount() > 0) {
214  $iFirstWeekDay = $this->getConfig()->getConfigParam('iFirstWeekDay');
215  while (!$rs->EOF) {
216  if (!$aTempOrder[$rs->fields[1]] && !isset($aTempPaymentSessions[$rs->fields[1]]) && !isset($aTempUserSessions[$rs->fields[1]]) && !isset($aTempExecOrdersSessions[$rs->fields[1]])) {
217  $sKey = strtotime($rs->fields[0]);
218  $sKey = $blMonth ? date("m/Y", $sKey) : oxRegistry::get("oxUtilsDate")->getWeekNumber($iFirstWeekDay, $sKey);
219  if (isset($aDataX4[$sKey])) {
220  $aDataX4[$sKey]++;
221  }
222  }
223  $rs->moveNext();
224  }
225  }
226  }
227 
235  protected function _collectOrdersMade($sSql, &$aDataX5, $blMonth = true)
236  {
237  $rs = oxDb::getDb()->execute($sSql);
238  if ($rs != false && $rs->recordCount() > 0) {
239  $iFirstWeekDay = $this->getConfig()->getConfigParam('iFirstWeekDay');
240  while (!$rs->EOF) {
241  $sKey = strtotime($rs->fields[0]);
242  $sKey = $blMonth ? date("m/Y", $sKey) : oxRegistry::get("oxUtilsDate")->getWeekNumber($iFirstWeekDay, $sKey);
243  if (isset($aDataX5[$sKey])) {
244  $aDataX5[$sKey]++;
245  }
246  $rs->moveNext();
247  }
248  }
249  }
250 
257  protected function _collectOrdersMadeForVisitorWeek($sQ, &$aDataX5)
258  {
259  // orders made
260  $rs = oxDb::getDb()->execute($sQ);
261  if ($rs != false && $rs->recordCount() > 0) {
262  while (!$rs->EOF) {
263  $sKey = oxRegistry::get("oxUtilsDate")->getWeekNumber(oxConfig::getConfigParam('iFirstWeekDay'), strtotime($rs->fields[0]));
264  if (isset($aDataX5[$sKey])) {
265  $aDataX5[$sKey]++;
266  }
267  $rs->moveNext();
268  }
269  }
270  }
271 
275  public function visitor_month()
276  {
277  $myConfig = $this->getConfig();
278  $oDb = oxDb::getDb();
279 
280  $dTimeTo = strtotime(oxRegistry::getConfig()->getRequestParameter("time_to"));
281  $sTimeTo = $oDb->quote(date("Y-m-d H:i:s", $dTimeTo));
282  $dTimeFrom = mktime(23, 59, 59, date("m", $dTimeTo) - 12, date("d", $dTimeTo), date("Y", $dTimeTo));
283  $sTimeFrom = $oDb->quote(date("Y-m-d H:i:s", $dTimeFrom));
284 
285  $sSQL = "select oxtime, count(*) as nrof from oxlogs where oxtime >= {$sTimeFrom} and oxtime <= {$sTimeTo} group by oxsessid";
286 
287  $aTemp = array();
288  for ($i = 1; $i <= 12; $i++) {
289  $aTemp[date("m/Y", mktime(23, 59, 59, date("m", $dTimeFrom) + $i, date("d", $dTimeFrom), date("Y", $dTimeFrom)))] = 0;
290  }
291 
292  $rs = $oDb->execute($sSQL);
293 
294  if ($rs != false && $rs->recordCount() > 0) {
295  while (!$rs->EOF) {
296  $aTemp[date("m/Y", strtotime($rs->fields[0]))]++;
297  $rs->moveNext();
298  }
299  }
300 
301  $aDataX = $aTemp;
302  $aDataY = array_keys($aTemp);
303  $aDataX2 = $aDataX3 = $aDataX4 = $aDataX5 = $aDataX6 = array_fill_keys($aDataY, 0);
304 
305  // collects sessions what executed 'order' function
306  $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'order' and oxfnc = 'execute' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
307  $aTempOrder = $this->_collectSessions($sQ);
308 
309  // collects sessions what executed order class
310  $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'order' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
311  $aTempExecOrdersSessions = $this->_collectOrderSessions($sQ, $aTempOrder, $aDataX6);
312 
313  // collects sessions what executed payment class
314  $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'payment' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
315  $aTempPaymentSessions = $this->_collectPaymentSessions($sQ, $aTempOrder, $aTempExecOrdersSessions, $aDataX2);
316 
317  // collects sessions what executed 'user' class
318  $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'user' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
319  $aTempUserSessions = $this->_collectUserSessionsForVisitorMonth($sQ, $aTempOrder, $aTempExecOrdersSessions, $aTempPaymentSessions, $aDataX2);
320 
321  // collects sessions what executed 'tobasket' function
322  $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'basket' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
323  $this->_collectToBasketSessions($sQ, $aTempOrder, $aTempExecOrdersSessions, $aTempPaymentSessions, $aTempUserSessions, $aDataX4);
324 
325  // orders made
326  $sQ = "select oxorderdate from oxorder where oxorderdate >= $sTimeFrom and oxorderdate <= $sTimeTo order by oxorderdate";
327  $this->_collectOrdersMade($sQ, $aDataX5);
328 
329  header("Content-type: image/png");
330 
331  // New graph with a drop shadow
332  $graph = $this->getGraph(800, 600);
333 
334  // Description
335  $graph->xaxis->setTickLabels($aDataY);
336 
337  // Set title and subtitle
338  $graph->title->set("Monat");
339 
340  // Create the bar plot
341  $bplot2 = new BarPlot(array_values($aDataX2));
342  $bplot2->setFillColor("#9966cc");
343  $bplot2->setLegend("Best.Abbr. in Bezahlmethoden");
344 
345  // Create the bar plot
346  $bplot3 = new BarPlot(array_values($aDataX3));
347  $bplot3->setFillColor("#ffcc00");
348  $bplot3->setLegend("Best.Abbr. in Benutzer");
349 
350  // Create the bar plot
351  $bplot4 = new BarPlot(array_values($aDataX4));
352  $bplot4->setFillColor("#6699ff");
353  $bplot4->setLegend("Best.Abbr. in Warenkorb");
354 
355  // Create the bar plot
356  $bplot6 = new BarPlot(array_values($aDataX6));
357  $bplot6->setFillColor("#ff0099");
358  $bplot6->setLegend("Best.Abbr. in Bestellbestaetigung");
359 
360  // Create the bar plot
361  $bplot5 = new BarPlot(array_values($aDataX5));
362  $bplot5->setFillColor("silver");
363  $bplot5->setLegend("Bestellungen");
364 
365  // Create the grouped bar plot
366  $gbplot = new groupBarPlot(array($bplot4, $bplot3, $bplot2, $bplot6, $bplot5));
367  $graph->add($gbplot);
368 
369  // Finally output the image
370  $graph->stroke();
371  }
372 
376  public function visitor_week()
377  {
378  $myConfig = $this->getConfig();
379  $oDb = oxDb::getDb();
380 
381  $aDataX = array();
382  $aDataX2 = array();
383  $aDataX3 = array();
384  $aDataX4 = array();
385  $aDataX5 = array();
386  $aDataX6 = array();
387  $aDataY = array();
388 
389  $sTimeTo = $oDb->quote(date("Y-m-d H:i:s", strtotime(oxRegistry::getConfig()->getRequestParameter("time_to"))));
390  $sTimeFrom = $oDb->quote(date("Y-m-d H:i:s", strtotime(oxRegistry::getConfig()->getRequestParameter("time_from"))));
391 
392  $sSQL = "select oxtime, count(*) as nrof from oxlogs where oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid order by oxtime";
393 
394  $aTemp = array();
395  $rs = $oDb->execute($sSQL);
396 
397  if ($rs != false && $rs->recordCount() > 0) {
398  while (!$rs->EOF) {
399  $aTemp[oxRegistry::get("oxUtilsDate")->getWeekNumber($myConfig->getConfigParam('iFirstWeekDay'), strtotime($rs->fields[0]))]++;
400  $rs->moveNext();
401  }
402  }
403 
404  // initializing
405  list($iFrom, $iTo) = $this->getWeekRange();
406  for ($i = $iFrom; $i < $iTo; $i++) {
407  $aDataX[$i] = 0;
408  $aDataX2[$i] = 0;
409  $aDataX3[$i] = 0;
410  $aDataX4[$i] = 0;
411  $aDataX5[$i] = 0;
412  $aDataX6[$i] = 0;
413  $aDataY[] = "KW " . $i;
414  }
415 
416  foreach ($aTemp as $key => $value) {
417  $aDataX[$key] = $value;
418  $aDataX2[$key] = 0;
419  $aDataX3[$key] = 0;
420  $aDataX4[$key] = 0;
421  $aDataX5[$key] = 0;
422  $aDataX6[$key] = 0;
423  $aDataY[] = "KW " . $key;
424  }
425 
426  // collects sessions what executed 'order' function
427  $sQ = "select oxtime, oxsessid FROM `oxlogs` where oxclass = 'order' and oxfnc = 'execute' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
428  $aTempOrder = $this->_collectSessions($sQ);
429 
430  // collects sessions what executed order class
431  $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'order' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
432  $aTempExecOrdersSessions = $this->_collectOrderSessions($sQ, $aTempOrder, $aDataX6, false);
433 
434  // collects sessions what executed payment class
435  $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'payment' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
436  $aTempPaymentSessions = $this->_collectPaymentSessions($sQ, $aTempOrder, $aTempExecOrdersSessions, $aDataX2, false);
437 
438  // collects sessions what executed 'user' class
439  $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'user' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
440  $aTempUserSessions = $this->_collectUserSessionsForVisitorMonth($sQ, $aTempOrder, $aTempExecOrdersSessions, $aTempPaymentSessions, $aDataX2, false);
441 
442  // collects sessions what executed 'tobasket' function
443  $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'basket' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
444  $this->_collectToBasketSessions($sQ, $aTempOrder, $aTempExecOrdersSessions, $aTempPaymentSessions, $aTempUserSessions, $aDataX4, false);
445 
446  // orders made
447  $sQ = "select oxorderdate from oxorder where oxorderdate >= $sTimeFrom and oxorderdate <= $sTimeTo order by oxorderdate";
448  $this->_collectOrdersMade($sQ, $aDataX5, false);
449 
450  header("Content-type: image/png");
451 
452  // New graph with a drop shadow
453  $graph = $this->getGraph(max(800, count($aDataX) * 80), 600);
454 
455  // Description
456  $graph->xaxis->setTickLabels($aDataY);
457 
458  // Set title and subtitle
459  $graph->title->set("Woche");
460 
461  // Create the bar plot
462  $bplot2 = new BarPlot(array_values($aDataX2));
463  $bplot2->setFillColor("#9966cc");
464  $bplot2->setLegend("Best.Abbr. in Bezahlmethoden");
465 
466  // Create the bar plot
467  $bplot3 = new BarPlot(array_values($aDataX3));
468  $bplot3->setFillColor("#ffcc00");
469  $bplot3->setLegend("Best.Abbr. in Benutzer");
470 
471  // Create the bar plot
472  $bplot4 = new BarPlot(array_values($aDataX4));
473  $bplot4->setFillColor("#6699ff");
474  $bplot4->setLegend("Best.Abbr. in Warenkorb");
475 
476  // Create the bar plot
477  $bplot6 = new BarPlot(array_values($aDataX6));
478  $bplot6->setFillColor("#ff0099");
479  $bplot6->setLegend("Best.Abbr. in Bestellbestaetigung");
480 
481  // Create the bar plot
482  $bplot5 = new BarPlot(array_values($aDataX5));
483  $bplot5->setFillColor("silver");
484  $bplot5->setLegend("Bestellungen");
485 
486  // Create the grouped bar plot
487  $gbplot = new groupBarPlot(array($bplot4, $bplot3, $bplot2, $bplot6, $bplot5));
488  $graph->add($gbplot);
489 
490  // Finally output the image
491  $graph->stroke();
492  }
493  }
494 }