report_conversion_rate.php

Go to the documentation of this file.
00001 <?php
00002 
00003 if ( !class_exists( "report_conversion_rate")) {
00007 class Report_conversion_rate extends report_base
00008 {
00014     protected $_sThisTemplate = "report_conversion_rate.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         // orders
00030         if ( $oDb->getOne( "select * from oxlogs where oxtime >= $sTimeFrom and oxtime <= $sTimeTo" ) ) {
00031             return true;
00032         }
00033 
00034         // orders
00035         if ( $oDb->getOne( "select 1 from oxorder where oxorderdate >= $sTimeFrom and oxorderdate <= $sTimeTo" ) ) {
00036             return true;
00037         }
00038     }
00039 
00045     public function visitor_month()
00046     {
00047         $myConfig = $this->getConfig();
00048         $oDb = oxDb::getDb();
00049 
00050         $aDataX = array();
00051         $aDataY = array();
00052 
00053         $sTimeTo    = $oDb->quote( date( "Y-m-d H:i:s", strtotime( oxConfig::getParameter( "time_to" ) ) ) );
00054         $sTimeFrom = $oDb->quote( date( "Y-m-d H:i:s", mktime( 23, 59, 59, date( "m", $dTimeTo)-12, date( "d", $dTimeTo), date( "Y", $dTimeTo)) ) );
00055 
00056         // orders
00057         $sSQL = "select oxtime, count(*) as nrof from oxlogs where oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid";
00058         $aTemp = array();
00059         for ( $i = 1; $i <= 12; $i++)
00060             $aTemp[date( "m/Y", mktime( 23, 59, 59, date( "m", $dTimeFrom)+$i, date( "d", $dTimeFrom), date( "Y", $dTimeFrom)) )] = 0;
00061 
00062         $rs = $oDb->execute( $sSQL);
00063         if ($rs != false && $rs->recordCount() > 0) {
00064             while (!$rs->EOF) {
00065                 $aTemp[date( "m/Y", strtotime( $rs->fields[0]))]++;
00066                 $rs->moveNext();
00067             }
00068         }
00069 
00070         $aDataX2  = array();
00071         $aDataX3  = array();
00072 
00073         foreach ( $aTemp as $key => $value) {
00074             $aDataX[$key]   = $value;
00075             $aDataX2[$key]  = 0;
00076             $aDataX3[$key]  = 0;
00077             $aDataY[]       = $key;
00078         }
00079 
00080         // orders
00081         $sSQL = "select oxorderdate from oxorder where oxorderdate >= $sTimeFrom and oxorderdate <= $sTimeTo order by oxorderdate";
00082         $rs = $oDb->execute( $sSQL);
00083         if ($rs != false && $rs->recordCount() > 0) {
00084             while (!$rs->EOF) {
00085                 $sKey = date( "m/Y", strtotime( $rs->fields[0]));
00086                 if (isset($aDataX2[$sKey])) {
00087                     $aDataX2[$sKey]++;
00088                 }
00089                 $rs->moveNext();
00090             }
00091         }
00092 
00093         header ("Content-type: image/png" );
00094 
00095         // New graph with a drop shadow
00096         $graph = new Graph(800, 600, "auto");
00097 
00098         $graph->setBackgroundImage( $myConfig->getAbsAdminImageDir()."/reportbgrnd.jpg", BGIMG_FILLFRAME);
00099 
00100         // Use a "text" X-scale
00101         $graph->setScale("textlin");
00102         $graph->setY2Scale("lin");
00103         $graph->y2axis->setColor("red");
00104 
00105         // Label align for X-axis
00106         $graph->xaxis->setLabelAlign('center', 'top', 'right');
00107 
00108         // Label align for Y-axis
00109         $graph->yaxis->setLabelAlign('right', 'bottom');
00110 
00111         $graph->setShadow();
00112         // Description
00113         $graph->xaxis->setTickLabels( $aDataY);
00114 
00115 
00116         // Set title and subtitle
00117         $graph->title->set("Monat");
00118 
00119         // Use built in font
00120         $graph->title->setFont(FF_FONT1, FS_BOLD);
00121 
00122         $aDataFinalX = array();
00123         foreach ( $aDataX as $dData)
00124             $aDataFinalX[] = $dData;
00125 
00126         // Create the bar plot
00127         $l2plot=new LinePlot($aDataFinalX);
00128         $l2plot->setColor("navy");
00129         $l2plot->setWeight(2);
00130         $l2plot->setLegend("Besucher");
00131         //$l1plot->SetBarCenter();
00132         $l2plot->value->setColor("navy");
00133         $l2plot->value->setFormat('% d');
00134         $l2plot->value->hideZero();
00135         $l2plot->value->show();
00136 
00137         $aDataFinalX2 = array();
00138         foreach ( $aDataX2 as $dData)
00139             $aDataFinalX2[] = $dData;
00140 
00141         // Create the bar plot
00142         $l3plot=new LinePlot($aDataFinalX2);
00143         $l3plot->setColor("orange");
00144         $l3plot->setWeight(2);
00145         $l3plot->setLegend("Bestellungen");
00146         //$l1plot->SetBarCenter();
00147         $l3plot->value->setColor('orange');
00148         $l3plot->value->setFormat('% d');
00149         $l3plot->value->hideZero();
00150         $l3plot->value->show();
00151 
00152         //conversion rate graph
00153         $l1datay = array();
00154         for ($iCtr = 0; $iCtr < count($aDataFinalX); $iCtr++) {
00155             if ($aDataFinalX[$iCtr] != 0 && $aDataFinalX2[$iCtr] != 0) {
00156                 $l1datay[] = 100/($aDataFinalX[$iCtr]/$aDataFinalX2[$iCtr]);
00157             } else
00158                 $l1datay[] = 0;
00159         }
00160 
00161         $l1plot=new LinePlot($l1datay);
00162         $l1plot->setColor("red");
00163         $l1plot->setWeight(2);
00164         $l1plot->setLegend("Conversion rate (%)");
00165         $l1plot->value->setColor('red');
00166         $l1plot->value->setFormat('% 0.2f%%');
00167         $l1plot->value->hideZero();
00168         $l1plot->value->show();
00169 
00170         // Create the grouped bar plot1
00171         $graph->addY2( $l1plot );
00172         $graph->add( $l2plot );
00173         $graph->add( $l3plot );
00174 
00175         // Finally output the  image
00176         $graph->stroke();
00177 
00178     }
00179 
00185     public function visitor_week()
00186     {
00187         $myConfig = $this->getConfig();
00188         $oDb = oxDb::getDb();
00189 
00190         $aDataX = array();
00191         $aDataX2  = array();
00192         $aDataX3  = array();
00193         $aDataY = array();
00194 
00195         $sTimeTo    = $oDb->quote( date( "Y-m-d H:i:s", strtotime( oxConfig::getParameter( "time_to" ) ) ) );
00196         $sTimeFrom = $oDb->quote( date( "Y-m-d H:i:s", strtotime( oxConfig::getParameter( "time_from") ) ) );
00197 
00198         $sSQL = "select oxtime, count(*) as nrof from oxlogs where oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid order by oxtime";
00199         $aTemp = array();
00200         $rs = $oDb->execute( $sSQL);
00201         if ($rs != false && $rs->recordCount() > 0) {
00202             while (!$rs->EOF) {
00203                 //$aTemp[date( "W", strtotime( $rs->fields[0]))]++;
00204                 $aTemp[oxUtilsDate::getInstance()->getWeekNumber($myConfig->getConfigParam( 'iFirstWeekDay' ), strtotime( $rs->fields[0]))]++;
00205                 $rs->moveNext();
00206             }
00207         }
00208 
00209 
00210         // initializing
00211         list( $iFrom, $iTo ) = $this->getWeekRange();
00212         for ( $i = $iFrom; $i < $iTo; $i++ ) {
00213             $aDataX[$i]  = 0;
00214             $aDataX2[$i] = 0;
00215             $aDataX3[$i] = 0;
00216             $aDataY[]    = "KW ".$i;
00217         }
00218 
00219         foreach ( $aTemp as $key => $value) {
00220             $aDataX[$key]   = $value;
00221             $aDataX2[$key]  = 0;
00222             $aDataX3[$key]  = 0;
00223             $aDataY[]       = "KW ".$key;
00224         }
00225 
00226         // buyer
00227         $sSQL = "select oxorderdate from oxorder where oxorderdate >= $sTimeFrom and oxorderdate <= $sTimeTo order by oxorderdate";
00228         $rs = $oDb->execute( $sSQL);
00229         if ($rs != false && $rs->recordCount() > 0) {
00230             while (!$rs->EOF) {
00231                 $sKey = oxUtilsDate::getInstance()->getWeekNumber($myConfig->getConfigParam( 'iFirstWeekDay' ), strtotime( $rs->fields[0]));
00232                 if (isset($aDataX2[$sKey])) {
00233                     $aDataX2[$sKey]++;
00234                 }
00235                 $rs->moveNext();
00236             }
00237         }
00238 
00239         // newcustomer
00240         $sSQL = "select oxtime, oxsessid from oxlogs where oxtime >= $sTimeFrom and oxtime <= $sTimeTo group by oxsessid order by oxtime";
00241         $rs = $oDb->execute( $sSQL);
00242         if ($rs != false && $rs->recordCount() > 0) {
00243             while (!$rs->EOF) {
00244                 $sKey = oxUtilsDate::getInstance()->getWeekNumber($myConfig->getConfigParam( 'iFirstWeekDay' ), strtotime( $rs->fields[0]));
00245                 if ( isset( $aDataX3[$sKey] ) ) {
00246                     $aDataX3[$sKey]++;
00247                 }
00248                 $rs->moveNext();
00249             }
00250         }
00251 
00252         header ("Content-type: image/png" );
00253 
00254         // New graph with a drop shadow
00255         $graph = new Graph( max( 800, count( $aDataX) * 80), 600);
00256 
00257         $graph->setBackgroundImage( $myConfig->getAbsAdminImageDir()."/reportbgrnd.jpg", BGIMG_FILLFRAME);
00258 
00259         // Use a "text" X-scale
00260         $graph->setScale("textlin");
00261         $graph->setY2Scale("lin");
00262         $graph->y2axis->setColor("red");
00263 
00264         // Label align for X-axis
00265         $graph->xaxis->setLabelAlign('center', 'top', 'right');
00266 
00267         // Label align for Y-axis
00268         $graph->yaxis->setLabelAlign('right', 'bottom');
00269 
00270         $graph->setShadow();
00271         // Description
00272         $graph->xaxis->setTickLabels( $aDataY);
00273 
00274 
00275         // Set title and subtitle
00276         $graph->title->set("Woche");
00277 
00278         // Use built in font
00279         $graph->title->setFont(FF_FONT1, FS_BOLD);
00280 
00281         $aDataFinalX = array();
00282         foreach ( $aDataX as $dData)
00283             $aDataFinalX[] = $dData;
00284 
00285         // Create the bar plot
00286         $l2plot=new LinePlot($aDataFinalX);
00287         $l2plot->setColor("navy");
00288         $l2plot->setWeight(2);
00289         $l2plot->setLegend("Besucher");
00290         $l2plot->value->setColor("navy");
00291         $l2plot->value->setFormat('% d');
00292         $l2plot->value->hideZero();
00293         $l2plot->value->show();
00294 
00295         $aDataFinalX2 = array();
00296         foreach ( $aDataX2 as $dData)
00297             $aDataFinalX2[] = $dData;
00298 
00299         // Create the bar plot
00300         $l3plot=new LinePlot($aDataFinalX2);
00301         $l3plot->setColor("orange");
00302         $l3plot->setWeight(2);
00303         $l3plot->setLegend("Bestellungen");
00304         //$l1plot->SetBarCenter();
00305         $l3plot->value->setColor("orange");
00306         $l3plot->value->setFormat('% d');
00307         $l3plot->value->hideZero();
00308         $l3plot->value->show();
00309 
00310         //conversion rate graph
00311         $l1datay = array();
00312         for ($iCtr = 0; $iCtr < count($aDataFinalX); $iCtr++) {
00313             if ($aDataFinalX[$iCtr] != 0 && $aDataFinalX2[$iCtr] != 0) {
00314                 $l1datay[] = 100/($aDataFinalX[$iCtr]/$aDataFinalX2[$iCtr]);
00315             } else
00316                 $l1datay[] = 0;
00317         }
00318         $l1plot=new LinePlot($l1datay);
00319         $l1plot->setColor("red");
00320         $l1plot->setWeight(2);
00321         $l1plot->setLegend("Conversion rate (%)");
00322         $l1plot->value->setColor('red');
00323         $l1plot->value->setFormat('% 0.4f%%');
00324         $l1plot->value->hideZero();
00325         $l1plot->value->show();
00326 
00327         // Create the grouped bar plot
00328         $graph->addY2($l1plot);
00329         $graph->add($l2plot);
00330         $graph->add($l3plot);
00331 
00332         // Finally output the  image
00333         $graph->stroke();
00334     }
00335 }
00336 }