report_canceled_orders.php

Go to the documentation of this file.
00001 <?php
00002 
00003 if ( !class_exists( 'report_canceled_orders' ) ) {
00007 class Report_canceled_orders extends report_base
00008 {
00014     protected $_sThisTemplate = "report_canceled_orders.tpl";
00015 
00021     public function drawReport()
00022     {
00023         $oDb = oxDb::getDb();
00024 
00025         $oSmarty    = $this->getSmarty();
00026         $sTimeFrom = $oDb->quote( date( "Y-m-d H:i:s", strtotime( $oSmarty->_tpl_vars['time_from'] ) ) );
00027         $sTimeTo   = $oDb->quote( date( "Y-m-d H:i:s", strtotime( $oSmarty->_tpl_vars['time_to'] ) ) );
00028 
00029         // collects sessions what executed 'order' function
00030         if ( $oDb->getOne( "select 1 from `oxlogs` where oxclass = 'order' and oxfnc = 'execute' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo" ) ) {
00031             return true;
00032         }
00033 
00034         // collects sessions what executed order class
00035         if ( $oDb->getOne( "select 1 from `oxlogs` where oxclass = 'order' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo" ) ) {
00036             return true;
00037         }
00038 
00039         // collects sessions what executed payment class
00040         if ( $oDb->getOne( "select 1 from `oxlogs` where oxclass = 'payment' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo" ) ) {
00041             return true;
00042         }
00043 
00044         // collects sessions what executed 'user' class
00045         if ( $oDb->getOne( "select 1 from `oxlogs` where oxclass = 'user' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo" ) ) {
00046             return true;
00047         }
00048 
00049         // collects sessions what executed 'tobasket' function
00050         if ( $oDb->getOne( "select 1 from `oxlogs` where oxclass = 'basket' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo" ) ) {
00051             return true;
00052         }
00053 
00054         // orders made
00055         if ( $oDb->getOne( "select 1 from oxorder where oxorderdate >= $sTimeFrom and oxorderdate <= $sTimeTo" ) ) {
00056             return true;
00057         }
00058     }
00059 
00067     protected function _collectSessions( $sQ )
00068     {
00069         $aTempOrder = array();
00070         $rs = oxDb::getDb()->execute( $sQ );
00071         if ( $rs != false && $rs->recordCount() > 0) {
00072             while ( !$rs->EOF ) {
00073                 $aTempOrder[$rs->fields[1]] = $rs->fields[0];
00074                 $rs->moveNext();
00075             }
00076         }
00077         return $aTempOrder;
00078     }
00079 
00090     protected function _collectOrderSessions( $sQ, $aTempOrder, &$aDataX6, $blMonth = true )
00091     {
00092         // collects sessions what executed order class
00093         $aTempExecOrdersSessions = array();
00094         $rs = oxDb::getDb()->execute( $sQ );
00095         if ($rs != false && $rs->recordCount() > 0) {
00096             $iFirstWeekDay = $this->getConfig()->getConfigParam( 'iFirstWeekDay' );
00097             while ( !$rs->EOF ) {
00098                 if ( !isset($aTempOrder[$rs->fields[1]] ) ) {
00099                     $aTempExecOrdersSessions[$rs->fields[1]] = 1;
00100                     $sKey = strtotime( $rs->fields[0] );
00101                     $sKey = $blMonth ? date( "m/Y", $sKey ) : oxUtilsDate::getInstance()->getWeekNumber( $iFirstWeekDay, $sKey );
00102                     if ( isset( $aDataX6[$sKey] ) ) {
00103                         $aDataX6[$sKey]++;
00104                     }
00105 
00106                 }
00107                 $rs->moveNext();
00108             }
00109         }
00110 
00111         return $aTempExecOrdersSessions;
00112     }
00113 
00125     protected function _collectPaymentSessions( $sQ, $aTempOrder, $aTempExecOrdersSessions, &$aDataX2, $blMonth = true )
00126     {
00127         $aTempPaymentSessions = array();
00128         $rs = oxDb::getDb()->execute( $sQ );
00129         if ( $rs != false && $rs->recordCount() > 0 ) {
00130             $iFirstWeekDay = $this->getConfig()->getConfigParam( 'iFirstWeekDay' );
00131             while (!$rs->EOF) {
00132                 if ( !isset( $aTempOrder[$rs->fields[1]]) && !isset( $aTempExecOrdersSessions[$rs->fields[1]] ) ) {
00133                     $aTempPaymentSessions[$rs->fields[1]] = 1;
00134                     $sKey = strtotime( $rs->fields[0] );
00135                     $sKey = $blMonth ? date( "m/Y", $sKey ) : oxUtilsDate::getInstance()->getWeekNumber( $iFirstWeekDay, $sKey);
00136                     if ( isset($aDataX2[$sKey]) ) {
00137                         $aDataX2[$sKey]++;
00138                     }
00139                 }
00140                 $rs->moveNext();
00141             }
00142         }
00143         return $aTempPaymentSessions;
00144     }
00145 
00158     protected function _collectUserSessionsForVisitorMonth( $sQ, $aTempOrder, $aTempExecOrdersSessions, $aTempPaymentSessions, &$aDataX3, $blMonth = true )
00159     {
00160         $aTempUserSessions = array();
00161         $rs = oxDb::getDb()->execute( $sQ );
00162         if ($rs != false && $rs->recordCount() > 0) {
00163             $iFirstWeekDay = $this->getConfig()->getConfigParam( 'iFirstWeekDay' );
00164             while (!$rs->EOF) {
00165                 if (!isset($aTempOrder[$rs->fields[1]]) && !isset($aTempPaymentSessions[$rs->fields[1]]) && !isset($aTempExecOrdersSessions[$rs->fields[1]])) {
00166                     $aTempUserSessions[$rs->fields[1]] = 1;
00167                     $sKey = strtotime( $rs->fields[0] );
00168                     $sKey = $blMonth ? date( "m/Y", $sKey ) : oxUtilsDate::getInstance()->getWeekNumber( $iFirstWeekDay, $sKey);
00169                     if ( isset($aDataX3[$sKey]) ) {
00170                         $aDataX3[$sKey]++;
00171                     }
00172                 }
00173                 $rs->moveNext();
00174             }
00175         }
00176 
00177         return $aTempUserSessions;
00178     }
00179 
00193     protected function _collectToBasketSessions( $sSql, $aTempOrder, $aTempExecOrdersSessions, $aTempPaymentSessions, $aTempUserSessions, &$aDataX4, $blMonth = true )
00194     {
00195         $rs = oxDb::getDb()->execute( $sSql);
00196         if ($rs != false && $rs->recordCount() > 0) {
00197             $iFirstWeekDay = $this->getConfig()->getConfigParam( 'iFirstWeekDay' );
00198             while (!$rs->EOF) {
00199                 if ( !$aTempOrder[$rs->fields[1]] && !isset($aTempPaymentSessions[$rs->fields[1]]) && !isset($aTempUserSessions[$rs->fields[1]]) && !isset($aTempExecOrdersSessions[$rs->fields[1]] ) ) {
00200                     $sKey = strtotime( $rs->fields[0] );
00201                     $sKey = $blMonth ? date( "m/Y", $sKey ) : oxUtilsDate::getInstance()->getWeekNumber( $iFirstWeekDay, $sKey );
00202                     if ( isset($aDataX4[$sKey]) ) {
00203                         $aDataX4[$sKey]++;
00204                     }
00205                 }
00206                 $rs->moveNext();
00207             }
00208         }
00209     }
00210 
00220     protected function _collectOrdersMade( $sSql, &$aDataX5, $blMonth = true )
00221     {
00222         $rs = oxDb::getDb()->execute( $sSql );
00223         if ( $rs != false && $rs->recordCount() > 0 ) {
00224             $iFirstWeekDay = $this->getConfig()->getConfigParam( 'iFirstWeekDay' );
00225             while (!$rs->EOF) {
00226                 $sKey = strtotime( $rs->fields[0] );
00227                 $sKey = $blMonth ? date( "m/Y", $sKey ) : oxUtilsDate::getInstance()->getWeekNumber( $iFirstWeekDay, $sKey );
00228                 if ( isset($aDataX5[$sKey]) ) {
00229                     $aDataX5[$sKey]++;
00230                 }
00231                 $rs->moveNext();
00232             }
00233         }
00234     }
00235 
00244     protected function _collectOrdersMadeForVisitorWeek( $sQ, &$aDataX5 )
00245     {
00246         // orders made
00247         $rs = oxDb::getDb()->execute( $sQ );
00248         if ( $rs != false && $rs->recordCount() > 0 ) {
00249             while ( !$rs->EOF ) {
00250                 $sKey = oxUtilsDate::getInstance()->getWeekNumber( $myConfig->getConfigParam( 'iFirstWeekDay' ), strtotime( $rs->fields[0] ) );
00251                 if ( isset( $aDataX5[$sKey] ) ) {
00252                     $aDataX5[$sKey]++;
00253                 }
00254                 $rs->moveNext();
00255             }
00256         }
00257     }
00258 
00264     public function visitor_month()
00265     {
00266         $myConfig = $this->getConfig();
00267         $oDb = oxDb::getDb();
00268 
00269         $dTimeTo = strtotime( oxConfig::getParameter( "time_to" ) );
00270         $sTimeTo = $oDb->quote( date( "Y-m-d H:i:s", $dTimeTo ) );
00271         $dTimeFrom = mktime( 23, 59, 59, date( "m", $dTimeTo )-12, date( "d", $dTimeTo ), date( "Y", $dTimeTo ) );
00272         $sTimeFrom = $oDb->quote( date( "Y-m-d H:i:s", $dTimeFrom ) );
00273 
00274         $sSQL = "select oxtime, count(*) as nrof from oxlogs where oxtime >= {$sTimeFrom} and oxtime <= {$sTimeTo} group by oxsessid";
00275 
00276         $aTemp = array();
00277         for ( $i = 1; $i <= 12; $i++) {
00278             $aTemp[date( "m/Y", mktime( 23, 59, 59, date( "m", $dTimeFrom ) + $i, date( "d", $dTimeFrom ), date( "Y", $dTimeFrom ) ) ) ] = 0;
00279         }
00280 
00281         $rs = $oDb->execute( $sSQL );
00282 
00283         if ($rs != false && $rs->recordCount() > 0) {
00284             while (!$rs->EOF) {
00285                 $aTemp[date( "m/Y", strtotime( $rs->fields[0] ) )]++;
00286                 $rs->moveNext();
00287             }
00288         }
00289 
00290         $aDataX  = $aTemp;
00291         $aDataY  = array_keys( $aTemp );
00292         $aDataX2 = $aDataX3 = $aDataX4 = $aDataX5 = $aDataX6 = array_fill_keys( $aDataY, 0 );
00293 
00294         // collects sessions what executed 'order' function
00295         $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'order' and oxfnc = 'execute' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
00296         $aTempOrder = $this->_collectSessions( $sQ );
00297 
00298         // collects sessions what executed order class
00299         $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'order' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
00300         $aTempExecOrdersSessions = $this->_collectOrderSessions( $sQ, $aTempOrder, $aDataX6 );
00301 
00302         // collects sessions what executed payment class
00303         $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'payment' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
00304         $aTempPaymentSessions = $this->_collectPaymentSessions( $sQ, $aTempOrder, $aTempExecOrdersSessions, $aDataX2 );
00305 
00306         // collects sessions what executed 'user' class
00307         $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'user' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
00308         $aTempUserSessions = $this->_collectUserSessionsForVisitorMonth( $sQ, $aTempOrder, $aTempExecOrdersSessions, $aTempPaymentSessions, $aDataX2 );
00309 
00310         // collects sessions what executed 'tobasket' function
00311         $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'basket' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
00312         $this->_collectToBasketSessions( $sQ, $aTempOrder, $aTempExecOrdersSessions, $aTempPaymentSessions, $aTempUserSessions, $aDataX4 );
00313 
00314         // orders made
00315         $sQ = "select oxorderdate from oxorder where oxorderdate >= $sTimeFrom and oxorderdate <= $sTimeTo order by oxorderdate";
00316         $this->_collectOrdersMade( $sQ, $aDataX5 );
00317 
00318         header( "Content-type: image/png" );
00319 
00320         // New graph with a drop shadow
00321         $graph = $this->getGraph( 800, 600 );
00322 
00323         // Description
00324         $graph->xaxis->setTickLabels( $aDataY );
00325 
00326         // Set title and subtitle
00327         $graph->title->set( "Monat" );
00328 
00329         // Create the bar plot
00330         $bplot2 = new BarPlot( array_values( $aDataX2 ) );
00331         $bplot2->setFillColor( "#9966cc" );
00332         $bplot2->setLegend( "Best.Abbr. in Bezahlmethoden" );
00333 
00334         // Create the bar plot
00335         $bplot3 = new BarPlot( array_values( $aDataX3 ) );
00336         $bplot3->setFillColor( "#ffcc00" );
00337         $bplot3->setLegend( "Best.Abbr. in Benutzer" );
00338 
00339         // Create the bar plot
00340         $bplot4 = new BarPlot( array_values( $aDataX4 ) );
00341         $bplot4->setFillColor( "#6699ff" );
00342         $bplot4->setLegend( "Best.Abbr. in Warenkorb" );
00343 
00344         // Create the bar plot
00345         $bplot6 = new BarPlot( array_values( $aDataX6 ) );
00346         $bplot6->setFillColor( "#ff0099" );
00347         $bplot6->setLegend( "Best.Abbr. in Bestellbestaetigung" );
00348 
00349         // Create the bar plot
00350         $bplot5 = new BarPlot( array_values( $aDataX5 ) );
00351         $bplot5->setFillColor( "silver" );
00352         $bplot5->setLegend( "Bestellungen" );
00353 
00354         // Create the grouped bar plot
00355         $gbplot = new groupBarPlot( array( $bplot4, $bplot3, $bplot2, $bplot6, $bplot5 ) );
00356         $graph->add( $gbplot );
00357 
00358         // Finally output the  image
00359         $graph->stroke();
00360     }
00361 
00367     public function visitor_week()
00368     {
00369         $myConfig = $this->getConfig();
00370         $oDb = oxDb::getDb();
00371 
00372         $aDataX  = array();
00373         $aDataX2 = array();
00374         $aDataX3 = array();
00375         $aDataX4 = array();
00376         $aDataX5 = array();
00377         $aDataX6 = array();
00378         $aDataY  = array();
00379 
00380         $sTimeTo   = $oDb->quote( date( "Y-m-d H:i:s", strtotime( oxConfig::getParameter( "time_to" ) ) ) );
00381         $sTimeFrom = $oDb->quote( date( "Y-m-d H:i:s", strtotime( oxConfig::getParameter( "time_from")) ) );
00382 
00383         $sSQL = "select oxtime, count(*) as nrof from oxlogs where oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid order by oxtime";
00384 
00385         $aTemp = array();
00386         $rs = $oDb->execute( $sSQL);
00387 
00388         if ($rs != false && $rs->recordCount() > 0) {
00389             while (!$rs->EOF) {
00390                 $aTemp[oxUtilsDate::getInstance()->getWeekNumber($myConfig->getConfigParam( 'iFirstWeekDay' ), strtotime( $rs->fields[0]))]++;
00391                 $rs->moveNext();
00392             }
00393         }
00394 
00395         // initializing
00396         list( $iFrom, $iTo ) = $this->getWeekRange();
00397         for ( $i = $iFrom; $i < $iTo; $i++ ) {
00398             $aDataX[$i]  = 0;
00399             $aDataX2[$i] = 0;
00400             $aDataX3[$i] = 0;
00401             $aDataX4[$i] = 0;
00402             $aDataX5[$i] = 0;
00403             $aDataX6[$i] = 0;
00404             $aDataY[]    = "KW ".$i;
00405         }
00406 
00407         foreach ( $aTemp as $key => $value) {
00408             $aDataX[$key]  = $value;
00409             $aDataX2[$key] = 0;
00410             $aDataX3[$key] = 0;
00411             $aDataX4[$key] = 0;
00412             $aDataX5[$key] = 0;
00413             $aDataX6[$key] = 0;
00414             $aDataY[]      = "KW ".$key;
00415         }
00416 
00417         // collects sessions what executed 'order' function
00418         $sQ = "select oxtime, oxsessid FROM `oxlogs` where oxclass = 'order' and oxfnc = 'execute' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
00419         $aTempOrder = $this->_collectSessions( $sQ );
00420 
00421         // collects sessions what executed order class
00422         $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'order' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
00423         $aTempExecOrdersSessions = $this->_collectOrderSessions( $sQ, $aTempOrder, $aDataX6, false );
00424 
00425         // collects sessions what executed payment class
00426         $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'payment' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
00427         $aTempPaymentSessions = $this->_collectPaymentSessions( $sQ, $aTempOrder, $aTempExecOrdersSessions, $aDataX2, false );
00428 
00429         // collects sessions what executed 'user' class
00430         $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'user' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
00431         $aTempUserSessions = $this->_collectUserSessionsForVisitorMonth( $sQ, $aTempOrder, $aTempExecOrdersSessions, $aTempPaymentSessions, $aDataX2, false );
00432 
00433         // collects sessions what executed 'tobasket' function
00434         $sQ = "select oxtime, oxsessid from `oxlogs` where oxclass = 'basket' and oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
00435         $this->_collectToBasketSessions( $sQ, $aTempOrder, $aTempExecOrdersSessions, $aTempPaymentSessions, $aTempUserSessions, $aDataX4, false );
00436 
00437         // orders made
00438         $sQ = "select oxorderdate from oxorder where oxorderdate >= $sTimeFrom and oxorderdate <= $sTimeTo order by oxorderdate";
00439         $this->_collectOrdersMade( $sQ, $aDataX5, false );
00440 
00441         header( "Content-type: image/png" );
00442 
00443         // New graph with a drop shadow
00444         $graph = $this->getGraph( max( 800, count( $aDataX ) * 80 ), 600 );
00445 
00446         // Description
00447         $graph->xaxis->setTickLabels( $aDataY );
00448 
00449         // Set title and subtitle
00450         $graph->title->set( "Woche" );
00451 
00452         // Create the bar plot
00453         $bplot2 = new BarPlot( array_values( $aDataX2 ) );
00454         $bplot2->setFillColor( "#9966cc" );
00455         $bplot2->setLegend( "Best.Abbr. in Bezahlmethoden" );
00456 
00457         // Create the bar plot
00458         $bplot3 = new BarPlot( array_values( $aDataX3 ) );
00459         $bplot3->setFillColor( "#ffcc00" );
00460         $bplot3->setLegend( "Best.Abbr. in Benutzer" );
00461 
00462         // Create the bar plot
00463         $bplot4 = new BarPlot( array_values( $aDataX4 ) );
00464         $bplot4->setFillColor( "#6699ff" );
00465         $bplot4->setLegend( "Best.Abbr. in Warenkorb" );
00466 
00467         // Create the bar plot
00468         $bplot6 = new BarPlot( array_values( $aDataX6 ) );
00469         $bplot6->setFillColor( "#ff0099" );
00470         $bplot6->setLegend( "Best.Abbr. in Bestellbestaetigung" );
00471 
00472         // Create the bar plot
00473         $bplot5 = new BarPlot( array_values( $aDataX5 ) );
00474         $bplot5->setFillColor( "silver" );
00475         $bplot5->setLegend( "Bestellungen" );
00476 
00477         // Create the grouped bar plot
00478         $gbplot = new groupBarPlot( array( $bplot4, $bplot3, $bplot2, $bplot6, $bplot5 ) );
00479         $graph->add( $gbplot );
00480 
00481         // Finally output the  image
00482         $graph->stroke();
00483     }
00484 }
00485 }