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