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