report_canceled_orders.php

Go to the documentation of this file.

<?php

if (!class_exists('report_canceled_orders')) {
    class Report_canceled_orders extends report_base
    {

        protected $_sThisTemplate = "report_canceled_orders.tpl";

        public function drawReport()
        {
            $oDb = oxDb::getDb();

            $oSmarty = $this->getSmarty();
            $sTimeFrom = $oDb->quote(date("Y-m-d H:i:s", strtotime($oSmarty->_tpl_vars['time_from'])));
            $sTimeTo = $oDb->quote(date("Y-m-d H:i:s", strtotime($oSmarty->_tpl_vars['time_to'])));

            // collects sessions what executed 'order' function
            $sSql = "select 1 from `oxlogs` where oxclass = 'order' and
                       oxfnc = 'execute' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo";
            if ($oDb->getOne($sSql)) {
                return true;
            }

            // collects sessions what executed order class
            $sSql = "select 1 from `oxlogs` where oxclass = 'order' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo";
            if ($oDb->getOne($sSql)) {
                return true;
            }

            // collects sessions what executed payment class
            $sSql = "select 1 from `oxlogs` where oxclass = 'payment' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo";
            if ($oDb->getOne($sSql)) {
                return true;
            }

            // collects sessions what executed 'user' class
            $sSql = "select 1 from `oxlogs` where oxclass = 'user' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo";
            if ($oDb->getOne($sSql)) {
                return true;
            }

            // collects sessions what executed 'tobasket' function
            $sSql = "select 1 from `oxlogs` where oxclass = 'basket' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo";
            if ($oDb->getOne($sSql)) {
                return true;
            }

            // orders made
            $sSql = "select 1 from oxorder where oxorderdate >= $sTimeFrom and oxorderdate <= $sTimeTo";
            if ($oDb->getOne($sSql)) {
                return true;
            }
        }

        protected function _collectSessions($sQ)
        {
            $aTempOrder = array();
            $rs = oxDb::getDb()->execute($sQ);
            if ($rs != false && $rs->recordCount() > 0) {
                while (!$rs->EOF) {
                    $aTempOrder[$rs->fields[1]] = $rs->fields[0];
                    $rs->moveNext();
                }
            }

            return $aTempOrder;
        }

        protected function _collectOrderSessions($sQ, $aTempOrder, &$aData, $blMonth = true)
        {
            // collects sessions what executed order class
            $aTempExecOrdersSessions = array();
            $rs = oxDb::getDb()->execute($sQ);
            if ($rs != false && $rs->recordCount() > 0) {
                $iFirstWeekDay = $this->getConfig()->getConfigParam('iFirstWeekDay');
                while (!$rs->EOF) {
                    if (!isset($aTempOrder[$rs->fields[1]])) {
                        $aTempExecOrdersSessions[$rs->fields[1]] = 1;
                        $sKey = strtotime($rs->fields[0]);
                        $oUtilsData = oxRegistry::get("oxUtilsDate");
                        $sKey = $blMonth ? date("m/Y", $sKey) : $oUtilsData->getWeekNumber($iFirstWeekDay, $sKey);
                        if (isset($aData[$sKey])) {
                            $aData[$sKey]++;
                        }

                    }
                    $rs->moveNext();
                }
            }

            return $aTempExecOrdersSessions;
        }

        protected function _collectPaymentSessions(
            $sQ,
            $aTempOrder,
            $aTempExecOrdersSessions,
            &$aDataX2,
            $blMonth = true
        ) {
            $aTempPaymentSessions = array();
            $rs = oxDb::getDb()->execute($sQ);
            if ($rs != false && $rs->recordCount() > 0) {
                $iFirstWeekDay = $this->getConfig()->getConfigParam('iFirstWeekDay');
                while (!$rs->EOF) {
                    if (!isset($aTempOrder[$rs->fields[1]]) && !isset($aTempExecOrdersSessions[$rs->fields[1]])) {
                        $aTempPaymentSessions[$rs->fields[1]] = 1;
                        $sKey = strtotime($rs->fields[0]);
                        $sKey = $blMonth ? date("m/Y", $sKey) : oxRegistry::get("oxUtilsDate")->getWeekNumber($iFirstWeekDay, $sKey);
                        if (isset($aDataX2[$sKey])) {
                            $aDataX2[$sKey]++;
                        }
                    }
                    $rs->moveNext();
                }
            }

            return $aTempPaymentSessions;
        }

        protected function _collectUserSessionsForVisitorMonth($sQ, $aTempOrder, $aTempExecOrdersSessions, $aTempPaymentSessions, &$aDataX3, $blMonth = true)
        {
            $aTempUserSessions = array();
            $rs = oxDb::getDb()->execute($sQ);
            if ($rs != false && $rs->recordCount() > 0) {
                $iFirstWeekDay = $this->getConfig()->getConfigParam('iFirstWeekDay');
                while (!$rs->EOF) {
                    if (!isset($aTempOrder[$rs->fields[1]]) && !isset($aTempPaymentSessions[$rs->fields[1]]) && !isset($aTempExecOrdersSessions[$rs->fields[1]])) {
                        $aTempUserSessions[$rs->fields[1]] = 1;
                        $sKey = strtotime($rs->fields[0]);
                        $sKey = $blMonth ? date("m/Y", $sKey) : oxRegistry::get("oxUtilsDate")->getWeekNumber($iFirstWeekDay, $sKey);
                        if (isset($aDataX3[$sKey])) {
                            $aDataX3[$sKey]++;
                        }
                    }
                    $rs->moveNext();
                }
            }

            return $aTempUserSessions;
        }

        protected function _collectToBasketSessions($sSql, $aTempOrder, $aTempExecOrdersSessions, $aTempPaymentSessions, $aTempUserSessions, &$aDataX4, $blMonth = true)
        {
            $rs = oxDb::getDb()->execute($sSql);
            if ($rs != false && $rs->recordCount() > 0) {
                $iFirstWeekDay = $this->getConfig()->getConfigParam('iFirstWeekDay');
                while (!$rs->EOF) {
                    if (!$aTempOrder[$rs->fields[1]] && !isset($aTempPaymentSessions[$rs->fields[1]]) && !isset($aTempUserSessions[$rs->fields[1]]) && !isset($aTempExecOrdersSessions[$rs->fields[1]])) {
                        $sKey = strtotime($rs->fields[0]);
                        $sKey = $blMonth ? date("m/Y", $sKey) : oxRegistry::get("oxUtilsDate")->getWeekNumber($iFirstWeekDay, $sKey);
                        if (isset($aDataX4[$sKey])) {
                            $aDataX4[$sKey]++;
                        }
                    }
                    $rs->moveNext();
                }
            }
        }

        protected function _collectOrdersMade($sSql, &$aDataX5, $blMonth = true)
        {
            $rs = oxDb::getDb()->execute($sSql);
            if ($rs != false && $rs->recordCount() > 0) {
                $iFirstWeekDay = $this->getConfig()->getConfigParam('iFirstWeekDay');
                while (!$rs->EOF) {
                    $sKey = strtotime($rs->fields[0]);
                    $sKey = $blMonth ? date("m/Y", $sKey) : oxRegistry::get("oxUtilsDate")->getWeekNumber($iFirstWeekDay, $sKey);
                    if (isset($aDataX5[$sKey])) {
                        $aDataX5[$sKey]++;
                    }
                    $rs->moveNext();
                }
            }
        }

        protected function _collectOrdersMadeForVisitorWeek($sQ, &$aDataX5)
        {
            // orders made
            $rs = oxDb::getDb()->execute($sQ);
            if ($rs != false && $rs->recordCount() > 0) {
                while (!$rs->EOF) {
                    $sKey = oxRegistry::get("oxUtilsDate")->getWeekNumber(oxConfig::getConfigParam('iFirstWeekDay'), strtotime($rs->fields[0]));
                    if (isset($aDataX5[$sKey])) {
                        $aDataX5[$sKey]++;
                    }
                    $rs->moveNext();
                }
            }
        }

        public function visitor_month()
        {
            $myConfig = $this->getConfig();
            $oDb = oxDb::getDb();

            $dTimeTo = strtotime(oxRegistry::getConfig()->getRequestParameter("time_to"));
            $sTimeTo = $oDb->quote(date("Y-m-d H:i:s", $dTimeTo));
            $dTimeFrom = mktime(23, 59, 59, date("m", $dTimeTo) - 12, date("d", $dTimeTo), date("Y", $dTimeTo));
            $sTimeFrom = $oDb->quote(date("Y-m-d H:i:s", $dTimeFrom));

            $sSQL = "select oxtime, count(*) as nrof from oxlogs where oxtime >= {$sTimeFrom} and oxtime <= {$sTimeTo} group by oxsessid";

            $aTemp = array();
            for ($i = 1; $i <= 12; $i++) {
                $aTemp[date("m/Y", mktime(23, 59, 59, date("m", $dTimeFrom) + $i, date("d", $dTimeFrom), date("Y", $dTimeFrom)))] = 0;
            }

            $rs = $oDb->execute($sSQL);

            if ($rs != false && $rs->recordCount() > 0) {
                while (!$rs->EOF) {
                    $aTemp[date("m/Y", strtotime($rs->fields[0]))]++;
                    $rs->moveNext();
                }
            }

            $aDataX = $aTemp;
            $aDataY = array_keys($aTemp);
            $aDataX2 = $aDataX3 = $aDataX4 = $aDataX5 = $aDataX6 = array_fill_keys($aDataY, 0);

            // collects sessions what executed 'order' function
            $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'order' and oxfnc = 'execute' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
            $aTempOrder = $this->_collectSessions($sQ);

            // collects sessions what executed order class
            $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'order' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
            $aTempExecOrdersSessions = $this->_collectOrderSessions($sQ, $aTempOrder, $aDataX6);

            // collects sessions what executed payment class
            $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'payment' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
            $aTempPaymentSessions = $this->_collectPaymentSessions($sQ, $aTempOrder, $aTempExecOrdersSessions, $aDataX2);

            // collects sessions what executed 'user' class
            $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'user' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
            $aTempUserSessions = $this->_collectUserSessionsForVisitorMonth($sQ, $aTempOrder, $aTempExecOrdersSessions, $aTempPaymentSessions, $aDataX2);

            // collects sessions what executed 'tobasket' function
            $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'basket' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
            $this->_collectToBasketSessions($sQ, $aTempOrder, $aTempExecOrdersSessions, $aTempPaymentSessions, $aTempUserSessions, $aDataX4);

            // orders made
            $sQ = "select oxorderdate from oxorder where oxorderdate >= $sTimeFrom and oxorderdate <= $sTimeTo order by oxorderdate";
            $this->_collectOrdersMade($sQ, $aDataX5);

            header("Content-type: image/png");

            // New graph with a drop shadow
            $graph = $this->getGraph(800, 600);

            // Description
            $graph->xaxis->setTickLabels($aDataY);

            // Set title and subtitle
            $graph->title->set("Monat");

            // Create the bar plot
            $bplot2 = new BarPlot(array_values($aDataX2));
            $bplot2->setFillColor("#9966cc");
            $bplot2->setLegend("Best.Abbr. in Bezahlmethoden");

            // Create the bar plot
            $bplot3 = new BarPlot(array_values($aDataX3));
            $bplot3->setFillColor("#ffcc00");
            $bplot3->setLegend("Best.Abbr. in Benutzer");

            // Create the bar plot
            $bplot4 = new BarPlot(array_values($aDataX4));
            $bplot4->setFillColor("#6699ff");
            $bplot4->setLegend("Best.Abbr. in Warenkorb");

            // Create the bar plot
            $bplot6 = new BarPlot(array_values($aDataX6));
            $bplot6->setFillColor("#ff0099");
            $bplot6->setLegend("Best.Abbr. in Bestellbestaetigung");

            // Create the bar plot
            $bplot5 = new BarPlot(array_values($aDataX5));
            $bplot5->setFillColor("silver");
            $bplot5->setLegend("Bestellungen");

            // Create the grouped bar plot
            $gbplot = new groupBarPlot(array($bplot4, $bplot3, $bplot2, $bplot6, $bplot5));
            $graph->add($gbplot);

            // Finally output the  image
            $graph->stroke();
        }

        public function visitor_week()
        {
            $myConfig = $this->getConfig();
            $oDb = oxDb::getDb();

            $aDataX = array();
            $aDataX2 = array();
            $aDataX3 = array();
            $aDataX4 = array();
            $aDataX5 = array();
            $aDataX6 = array();
            $aDataY = array();

            $sTimeTo = $oDb->quote(date("Y-m-d H:i:s", strtotime(oxRegistry::getConfig()->getRequestParameter("time_to"))));
            $sTimeFrom = $oDb->quote(date("Y-m-d H:i:s", strtotime(oxRegistry::getConfig()->getRequestParameter("time_from"))));

            $sSQL = "select oxtime, count(*) as nrof from oxlogs where oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid order by oxtime";

            $aTemp = array();
            $rs = $oDb->execute($sSQL);

            if ($rs != false && $rs->recordCount() > 0) {
                while (!$rs->EOF) {
                    $aTemp[oxRegistry::get("oxUtilsDate")->getWeekNumber($myConfig->getConfigParam('iFirstWeekDay'), strtotime($rs->fields[0]))]++;
                    $rs->moveNext();
                }
            }

            // initializing
            list($iFrom, $iTo) = $this->getWeekRange();
            for ($i = $iFrom; $i < $iTo; $i++) {
                $aDataX[$i] = 0;
                $aDataX2[$i] = 0;
                $aDataX3[$i] = 0;
                $aDataX4[$i] = 0;
                $aDataX5[$i] = 0;
                $aDataX6[$i] = 0;
                $aDataY[] = "KW " . $i;
            }

            foreach ($aTemp as $key => $value) {
                $aDataX[$key] = $value;
                $aDataX2[$key] = 0;
                $aDataX3[$key] = 0;
                $aDataX4[$key] = 0;
                $aDataX5[$key] = 0;
                $aDataX6[$key] = 0;
                $aDataY[] = "KW " . $key;
            }

            // collects sessions what executed 'order' function
            $sQ = "select oxtime, oxsessid FROM `oxlogs` where oxclass = 'order' and oxfnc = 'execute' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
            $aTempOrder = $this->_collectSessions($sQ);

            // collects sessions what executed order class
            $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'order' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
            $aTempExecOrdersSessions = $this->_collectOrderSessions($sQ, $aTempOrder, $aDataX6, false);

            // collects sessions what executed payment class
            $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'payment' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
            $aTempPaymentSessions = $this->_collectPaymentSessions($sQ, $aTempOrder, $aTempExecOrdersSessions, $aDataX2, false);

            // collects sessions what executed 'user' class
            $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'user' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
            $aTempUserSessions = $this->_collectUserSessionsForVisitorMonth($sQ, $aTempOrder, $aTempExecOrdersSessions, $aTempPaymentSessions, $aDataX2, false);

            // collects sessions what executed 'tobasket' function
            $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'basket' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
            $this->_collectToBasketSessions($sQ, $aTempOrder, $aTempExecOrdersSessions, $aTempPaymentSessions, $aTempUserSessions, $aDataX4, false);

            // orders made
            $sQ = "select oxorderdate from oxorder where oxorderdate >= $sTimeFrom and oxorderdate <= $sTimeTo order by oxorderdate";
            $this->_collectOrdersMade($sQ, $aDataX5, false);

            header("Content-type: image/png");

            // New graph with a drop shadow
            $graph = $this->getGraph(max(800, count($aDataX) * 80), 600);

            // Description
            $graph->xaxis->setTickLabels($aDataY);

            // Set title and subtitle
            $graph->title->set("Woche");

            // Create the bar plot
            $bplot2 = new BarPlot(array_values($aDataX2));
            $bplot2->setFillColor("#9966cc");
            $bplot2->setLegend("Best.Abbr. in Bezahlmethoden");

            // Create the bar plot
            $bplot3 = new BarPlot(array_values($aDataX3));
            $bplot3->setFillColor("#ffcc00");
            $bplot3->setLegend("Best.Abbr. in Benutzer");

            // Create the bar plot
            $bplot4 = new BarPlot(array_values($aDataX4));
            $bplot4->setFillColor("#6699ff");
            $bplot4->setLegend("Best.Abbr. in Warenkorb");

            // Create the bar plot
            $bplot6 = new BarPlot(array_values($aDataX6));
            $bplot6->setFillColor("#ff0099");
            $bplot6->setLegend("Best.Abbr. in Bestellbestaetigung");

            // Create the bar plot
            $bplot5 = new BarPlot(array_values($aDataX5));
            $bplot5->setFillColor("silver");
            $bplot5->setLegend("Bestellungen");

            // Create the grouped bar plot
            $gbplot = new groupBarPlot(array($bplot4, $bplot3, $bplot2, $bplot6, $bplot5));
            $graph->add($gbplot);

            // Finally output the  image
            $graph->stroke();
        }
    }
}