1<?php 2 3include('includes/session.php'); 4include('includes/phplot/phplot.php'); 5$Title=_('Sales Report Graph'); 6 7$ViewTopic = 'ARInquiries'; 8$BookMark = 'SalesGraph'; 9 10include('includes/header.php'); 11 12$SelectADifferentPeriod =''; 13 14if (isset($_POST['FromPeriod']) AND isset($_POST['ToPeriod'])){ 15 16 if ($_POST['FromPeriod'] > $_POST['ToPeriod']){ 17 prnMsg(_('The selected period from is actually after the period to! Please re-select the reporting period'),'error'); 18 $SelectADifferentPeriod =_('Select A Different Period'); 19 } 20/* There is no PHPlot reason to restrict the graph to 12 months... 21 if ($_POST['ToPeriod'] - $_POST['FromPeriod'] >12){ 22 prnMsg(_('The selected period range is more than 12 months - only graphs for a period less than 12 months can be created'),'error'); 23 $SelectADifferentPeriod= _('Select A Different Period'); 24 } 25*/ if ((!isset($_POST['ValueFrom']) OR $_POST['ValueFrom']='' OR !isset($_POST['ValueTo']) OR $_POST['ValueTo']='') AND $_POST['GraphOn'] !='All'){ 26 prnMsg(_('For graphs including either a customer or item range - the range must be specified. Please enter the value from and the value to for the range'),'error'); 27 $SelectADifferentPeriod= _('Select A Different Period'); 28 } 29} 30 31if ((! isset($_POST['FromPeriod']) OR ! isset($_POST['ToPeriod'])) 32 OR $SelectADifferentPeriod==_('Select A Different Period')){ 33 34 echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '">'; 35 echo '<div>'; 36 echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; 37 38 echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/maintenance.png" title="' . _('Search') . '" alt="" />' . ' ' . $Title . '</p>'; 39 40 echo '<table class="selection"> 41 <tr><td>' . _('Select Period From') . ':</td> 42 <td><select name="FromPeriod">'; 43 44 if (Date('m') > $_SESSION['YearEnd']){ 45 /*Dates in SQL format */ 46 $DefaultFromDate = Date ('Y-m-d', Mktime(0,0,0,$_SESSION['YearEnd'] + 2,0,Date('Y'))); 47 } else { 48 $DefaultFromDate = Date ('Y-m-d', Mktime(0,0,0,$_SESSION['YearEnd'] + 2,0,Date('Y')-1)); 49 } 50 $sql = "SELECT periodno, lastdate_in_period FROM periods ORDER BY periodno"; 51 $Periods = DB_query($sql); 52 53 while ($myrow=DB_fetch_array($Periods)){ 54 if(isset($_POST['FromPeriod']) AND $_POST['FromPeriod']!=''){ 55 if( $_POST['FromPeriod']== $myrow['periodno']){ 56 echo '<option selected="selected" value="' . $myrow['periodno'] . '">' .MonthAndYearFromSQLDate($myrow['lastdate_in_period']) . '</option>'; 57 } else { 58 echo '<option value="' . $myrow['periodno'] . '">' . MonthAndYearFromSQLDate($myrow['lastdate_in_period']) . '</option>'; 59 } 60 } else { 61 if($myrow['lastdate_in_period']==$DefaultFromDate){ 62 echo '<option selected="selected" value="' . $myrow['periodno'] . '">' . MonthAndYearFromSQLDate($myrow['lastdate_in_period']) . '</option>'; 63 } else { 64 echo '<option value="' . $myrow['periodno'] . '">' . MonthAndYearFromSQLDate($myrow['lastdate_in_period']) . '</option>'; 65 } 66 } 67 } 68 69 echo '</select></td></tr>'; 70 if (!isset($_POST['ToPeriod']) OR $_POST['ToPeriod']==''){ 71 $DefaultToPeriod = GetPeriod(DateAdd(ConvertSQLDate($DefaultFromDate),'m',11)); 72 } else { 73 $DefaultToPeriod = $_POST['ToPeriod']; 74 } 75 76 echo '<tr> 77 <td>' . _('Select Period To') . ':</td> 78 <td><select name="ToPeriod">'; 79 80 $RetResult = DB_data_seek($Periods,0); 81 82 while ($myrow=DB_fetch_array($Periods)){ 83 84 if($myrow['periodno']==$DefaultToPeriod){ 85 echo '<option selected="selected" value="' . $myrow['periodno'] . '">' . MonthAndYearFromSQLDate($myrow['lastdate_in_period']) . '</option>'; 86 } else { 87 echo '<option value ="' . $myrow['periodno'] . '">' . MonthAndYearFromSQLDate($myrow['lastdate_in_period']) . '</option>'; 88 } 89 } 90 echo '</select></td></tr>'; 91 92 echo '<tr> 93 <td> 94 <h3>', _('OR'), '</h3> 95 </td> 96 </tr>'; 97 98 if (!isset($_POST['Period'])) { 99 $_POST['Period'] = ''; 100 } 101 102 echo '<tr> 103 <td>', _('Select Period'), '</td> 104 <td>', ReportPeriodList($_POST['Period'], array('l', 't')), '</td> 105 </tr>'; 106 107 $AreasResult = DB_query("SELECT areacode, areadescription FROM areas ORDER BY areadescription"); 108 109 if (!isset($_POST['SalesArea'])){ 110 $_POST['SalesArea']=''; 111 } 112 echo '<tr> 113 <td>' . _('For Sales Area/Region:') . '</td> 114 <td><select name="SalesArea">'; 115 if($_POST['SalesArea']=='All'){ 116 echo '<option selected="selected" value="All">' . _('All') . '</option>'; 117 } else { 118 echo '<option value="All">' . _('All') . '</option>'; 119 } 120 while ($myrow=DB_fetch_array($AreasResult)){ 121 if($myrow['areacode']==$_POST['SalesArea']){ 122 echo '<option selected="selected" value="' . $myrow['areacode'] . '">' . $myrow['areadescription'] . '</option>'; 123 } else { 124 echo '<option value="' . $myrow['areacode'] . '">' . $myrow['areadescription'] . '</option>'; 125 } 126 } 127 echo '</select></td></tr>'; 128 129 $CategoriesResult = DB_query("SELECT categoryid, categorydescription FROM stockcategory ORDER BY categorydescription"); 130 131 if (!isset($_POST['CategoryID'])){ 132 $_POST['CategoryID']=''; 133 } 134 echo '<tr> 135 <td>' . _('For Stock Category') . ':</td> 136 <td><select name="CategoryID">'; 137 if($_POST['CategoryID']=='All'){ 138 echo '<option selected="selected" value="All">' . _('All') . '</option>'; 139 } else { 140 echo '<option value="All">' . _('All') . '</option>'; 141 } 142 while ($myrow=DB_fetch_array($CategoriesResult)){ 143 if($myrow['categoryid']==$_POST['CategoryID']){ 144 echo '<option selected="selected" value="' . $myrow['categoryid'] . '">' . $myrow['categorydescription'] . '</option>'; 145 } else { 146 echo '<option value="' . $myrow['categoryid'] . '">' . $myrow['categorydescription'] . '</option>'; 147 } 148 } 149 echo '</select></td></tr>'; 150 151 $SalesFolkResult = DB_query("SELECT salesmancode, salesmanname FROM salesman ORDER BY salesmanname"); 152 153 if (! isset($_POST['SalesmanCode'])){ 154 $_POST['SalesmanCode'] = ''; 155 } 156 157 echo '<tr> 158 <td>' . _('For Salesperson:') . '</td> 159 <td><select name="SalesmanCode">'; 160 161 if($_POST['SalesmanCode']=='All'){ 162 echo '<option selected="selected" value="All">' . _('All') . '</option>'; 163 } else { 164 echo '<option value="All">' . _('All') . '</option>'; 165 } 166 while ($myrow=DB_fetch_array($SalesFolkResult)){ 167 if ($myrow['salesmancode']== $_POST['SalesmanCode']){ 168 echo '<option selected="selected" value="' . $myrow['salesmancode'] . '">' . $myrow['salesmanname'] . '</option>'; 169 } else { 170 echo '<option value="' . $myrow['salesmancode'] . '">' . $myrow['salesmanname'] . '</option>'; 171 } 172 } 173 echo '</select></td> 174 <td>' . $_POST['SalesmanCode'] . '</td> 175 </tr>'; 176 177 echo '<tr> 178 <td>' . _('Graph Type') . '</td> 179 <td><select name="GraphType"> 180 <option value="bars">' . _('Bar Graph') . '</option> 181 <option value="stackedbars">' . _('Stacked Bar Graph') . '</option> 182 <option value="lines">' . _('Line Graph') . '</option> 183 <option value="linepoints">' . _('Line Point Graph') . '</option> 184 <option value="area">' . _('Area Graph') . '</option> 185 <option value="points">' . _('Points Graph') . '</option> 186 <option value="pie">' . _('Pie Graph') . '</option> 187 <option value="thinbarline">' . _('Thin Bar Line Graph') . '</option> 188 <option value="squared">' . _('Squared Graph') . '</option> 189 <option value="stackedarea">' . _('Stacked Area Graph') . '</option> 190 </select></td> 191 </tr>'; 192 193 if (!isset($_POST['ValueFrom'])){ 194 $_POST['ValueFrom']=''; 195 } 196 if (!isset($_POST['ValueTo'])){ 197 $_POST['ValueTo']=''; 198 } 199 echo '<tr><td>' . _('Graph On:') . '</td><td> 200 <input type="radio" name="GraphOn" value="All" checked="checked" />' . _('All') . '<br /> 201 <input type="radio" name="GraphOn" value="Customer" />' . _('Customer') . '<br /> 202 <input type="radio" name="GraphOn" value="StockID" />' . _('Item Code') . '</td></tr>'; 203 echo '<tr><td>' . _('From:') . ' <input type="text" name="ValueFrom" value="' . $_POST['ValueFrom'] . '" /></td> 204 <td>' . _('To:') . ' <input type="text" name="ValueTo" value="' . $_POST['ValueTo'] . '" /></td></tr>'; 205 206 echo '<tr> 207 <td>' . _('Graph Value:') . '</td> 208 <td><input type="radio" name="GraphValue" value="Net" checked="checked" />' . _('Net Sales Value') . '<br /> 209 <input type="radio" name="GraphValue" value="GP" />' . _('Gross Profit') . '<br /> 210 <input type="radio" name="GraphValue" value="Quantity" />' . _('Quantity') . '</td> 211 </tr> 212 </table> 213 <br /> 214 <div class="centre"><input type="submit" name="ShowGraph" value="' . _('Show Sales Graph') .'" /></div> 215 </div> 216 </form>'; 217 include('includes/footer.php'); 218} else { 219 220 $graph = new PHPlot(950,450); 221 $SelectClause =''; 222 $WhereClause =''; 223 $GraphTitle =''; 224 if ($_POST['GraphValue']=='Net') { 225 $GraphTitle = _('Sales Value'); 226 $SelectClause = 'amt - disc'; 227 } elseif ($_POST['GraphValue']=='GP'){ 228 $GraphTitle = _('Gross Profit'); 229 $SelectClause = '(amt - disc - cost)'; 230 } else { 231 $GraphTitle = _('Unit Sales'); 232 $SelectClause = 'qty'; 233 } 234 235 if ($_POST['Period'] != '') { 236 $_POST['FromPeriod'] = ReportPeriod($_POST['Period'], 'From'); 237 $_POST['ToPeriod'] = ReportPeriod($_POST['Period'], 'To'); 238 } 239 240 $SQL = "SELECT YEAR(`lastdate_in_period`) AS year, MONTHNAME(`lastdate_in_period`) AS month 241 FROM `periods` 242 WHERE `periodno`='" . $_POST['FromPeriod'] . "' OR periodno='" . $_POST['ToPeriod'] . "'"; 243 244 $result = DB_query($SQL); 245 246 $fromPeriod = DB_fetch_array($result); 247 $starting = $fromPeriod['month'] . ' ' . $fromPeriod['year']; 248 249 $toPeriod = DB_fetch_array($result); 250 $ending = $toPeriod['month'] . ' ' . $toPeriod['year']; 251 252 $GraphTitle .= ' ' . _('From Period') . ' ' . $starting . ' ' . _('to') . ' ' . $ending . "\n\r"; 253 254 if ($_POST['SalesArea']=='All'){ 255 $GraphTitle .= ' ' . _('For All Sales Areas'); 256 } else { 257 $result = DB_query("SELECT areadescription FROM areas WHERE areacode='" . $_POST['SalesArea'] . "'"); 258 $myrow = DB_fetch_row($result); 259 $GraphTitle .= ' ' . _('For') . ' ' . $myrow[0]; 260 $WhereClause .= " area='" . $_POST['SalesArea'] . "' AND"; 261 } 262 if ($_POST['CategoryID']=='All'){ 263 $GraphTitle .= ' ' . _('For All Stock Categories'); 264 } else { 265 $result = DB_query("SELECT categorydescription FROM stockcategory WHERE categoryid='" . $_POST['CategoryID'] . "'"); 266 $myrow = DB_fetch_row($result); 267 $GraphTitle .= ' ' . _('For') . ' ' . $myrow[0]; 268 $WhereClause .= " stkcategory='" . $_POST['CategoryID'] . "' AND"; 269 270 } 271 if ($_POST['SalesmanCode']=='All'){ 272 $GraphTitle .= ' ' . _('For All Salespeople'); 273 } else { 274 $result = DB_query("SELECT salesmanname FROM salesman WHERE salesmancode='" . $_POST['SalesmanCode'] . "'"); 275 $myrow = DB_fetch_row($result); 276 $GraphTitle .= ' ' . _('For Salesperson:') . ' ' . $myrow[0]; 277 $WhereClause .= " salesperson='" . $_POST['SalesmanCode'] . "' AND"; 278 279 } 280 if ($_POST['GraphOn']=='Customer'){ 281 $GraphTitle .= ' ' . _('For Customers from') . ' ' . $_POST['ValueFrom'] . ' ' . _('to') . ' ' . $_POST['ValueTo']; 282 $WhereClause .= " cust >='" . $_POST['ValueFrom'] . "' AND cust <='" . $_POST['ValueTo'] . "' AND"; 283 } 284 if ($_POST['GraphOn']=='StockID'){ 285 $GraphTitle .= ' ' . _('For Items from') . ' ' . $_POST['ValueFrom'] . ' ' . _('to') . ' ' . $_POST['ValueTo']; 286 $WhereClause .= " stockid >='" . $_POST['ValueFrom'] . "' AND stockid <='" . $_POST['ValueTo'] . "' AND"; 287 } 288 289 $WhereClause = "WHERE " . $WhereClause . " salesanalysis.periodno>='" . $_POST['FromPeriod'] . "' AND salesanalysis.periodno <= '" . $_POST['ToPeriod'] . "'"; 290 291 $SQL = "SELECT salesanalysis.periodno, 292 periods.lastdate_in_period, 293 SUM(CASE WHEN budgetoractual=1 THEN " . $SelectClause . " ELSE 0 END) AS sales, 294 SUM(CASE WHEN budgetoractual=0 THEN " . $SelectClause . " ELSE 0 END) AS budget 295 FROM salesanalysis INNER JOIN periods ON salesanalysis.periodno=periods.periodno " . $WhereClause . " 296 GROUP BY salesanalysis.periodno, 297 periods.lastdate_in_period 298 ORDER BY salesanalysis.periodno"; 299 300 301 $graph->SetTitle($GraphTitle); 302 $graph->SetTitleColor('blue'); 303 $graph->SetOutputFile('companies/' .$_SESSION['DatabaseName'] . '/reports/salesgraph.png'); 304 $graph->SetXTitle(_('Month')); 305 if ($_POST['GraphValue']=='Net'){ 306 $graph->SetYTitle(_('Sales Value')); 307 } elseif ($_POST['GraphValue']=='GP'){ 308 $graph->SetYTitle(_('Gross Profit')); 309 } else { 310 $graph->SetYTitle(_('Quantity')); 311 } 312 $graph->SetXTickPos('none'); 313 $graph->SetXTickLabelPos('none'); 314 $graph->SetXLabelAngle(90); 315 $graph->SetBackgroundColor('white'); 316 $graph->SetTitleColor('blue'); 317 $graph->SetFileFormat('png'); 318 $graph->SetPlotType($_POST['GraphType']); 319 $graph->SetIsInline('1'); 320 $graph->SetShading(5); 321 $graph->SetDrawYGrid(TRUE); 322 $graph->SetDataType('text-data'); 323 $graph->SetNumberFormat($DecimalPoint, $ThousandsSeparator); 324 $graph->SetPrecisionY($_SESSION['CompanyRecord']['decimalplaces']); 325 326 $SalesResult = DB_query($SQL); 327 if (DB_error_no() !=0) { 328 329 prnMsg(_('The sales graph data for the selected criteria could not be retrieved because') . ' - ' . DB_error_msg(),'error'); 330 include('includes/footer.php'); 331 exit; 332 } 333 if (DB_num_rows($SalesResult)==0){ 334 prnMsg(_('There is not sales data for the criteria entered to graph'),'info'); 335 include('includes/footer.php'); 336 exit; 337 } 338 339 $GraphArray = array(); 340 $i = 0; 341 while ($myrow = DB_fetch_array($SalesResult)){ 342 $GraphArray[$i] = array(MonthAndYearFromSQLDate($myrow['lastdate_in_period']),$myrow['sales'],$myrow['budget']); 343 $i++; 344 } 345 346 $graph->SetDataValues($GraphArray); 347 $graph->SetDataColors( 348 array('grey','wheat'), //Data Colors 349 array('black') //Border Colors 350 ); 351 $graph->SetLegend(array(_('Actual'),_('Budget'))); 352 $graph->SetYDataLabelPos('plotin'); 353 354 //Draw it 355 $graph->DrawGraph(); 356 echo '<table class="selection"> 357 <tr> 358 <td><p><img src="companies/' .$_SESSION['DatabaseName'] . '/reports/salesgraph.png" alt="Sales Report Graph"></img></p></td> 359 </tr> 360 </table>'; 361 include('includes/footer.php'); 362} 363?> 364