1<?php 2 3 4include('includes/session.php'); 5 6$Title = _('Sales Analysis Reports Maintenance'); 7/* webERP manual links before header.php */ 8$ViewTopic= 'SalesAnalysis'; 9$BookMark = 'SalesAnalysis'; 10 11include('includes/header.php'); 12 13echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/supplier.png" title="' . _('Search') . '" alt="" />' . ' ' . $Title . '</p>'; 14 15function GrpByDataOptions($GroupByDataX) { 16 17/*Sales analysis headers group by data options */ 18 if ($GroupByDataX == 'Sales Area') { 19 echo '<option selected="selected" value="Sales Area">' . _('Sales Area') . '</option>'; 20 } else { 21 echo '<option value="Sales Area">' . _('Sales Area') . '</option>'; 22 } 23 if ($GroupByDataX == 'Product Code') { 24 echo '<option selected="selected" value="Product Code">' . _('Product Code') . '</option>'; 25 } else { 26 echo '<option value="Product Code">' . _('Product Code') . '</option>'; 27 } 28 if ($GroupByDataX == 'Customer Code') { 29 echo '<option selected="selected" value="Customer Code">' . _('Customer Code') . '</option>'; 30 } else { 31 echo '<option value="Customer Code">' . _('Customer Code') . '</option>'; 32 } 33 if ($GroupByDataX == 'Sales Type') { 34 echo '<option selected="selected" value="Sales Type">' . _('Sales Type') . '</option>'; 35 } else { 36 echo '<option value="Sales Type">' . _('Sales Type') . '</option>'; 37 } 38 if ($GroupByDataX == 'Product Type') { 39 echo '<option selected="selected" value="Product Type">' . _('Product Type') . '</option>'; 40 } else { 41 echo '<option value="Product Type">' . _('Product Type') . '</option>'; 42 } 43 if ($GroupByDataX == 'Customer Branch') { 44 echo '<option selected="selected" value="Customer Branch">' . _('Customer Branch') . '</option>'; 45 } else { 46 echo '<option value="Customer Branch">' . _('Customer Branch') . '</option>'; 47 } 48 if ($GroupByDataX == 'Sales Person') { 49 echo '<option selected="selected" value="Sales Person">' . _('Sales Person') . '</option>'; 50 } else { 51 echo '<option value="Sales Person">' . _('Sales Person') . '</option>'; 52 } 53 if ($GroupByDataX=='Not Used' OR $GroupByDataX == '' OR ! isset($GroupByDataX) OR is_null($GroupByDataX)){ 54 echo '<option selected="selected" value="Not Used">' . _('Not Used') . '</option>'; 55 } else { 56 echo '<option value="Not Used">' . _('Not Used') . '</option>'; 57 } 58} 59 60/* end of function */ 61 62echo '<br />'; 63 64if (isset($_GET['SelectedReport'])) { 65 $SelectedReport = $_GET['SelectedReport']; 66} elseif (isset($_POST['SelectedReport'])) { 67 $SelectedReport = $_POST['SelectedReport']; 68} 69 70 71if (isset($_POST['submit'])) { 72 73 //initialise no input errors assumed initially before we test 74 $InputError = 0; 75 76 /* actions to take once the user has clicked the submit button 77 ie the page has called itself with some user input */ 78 79 //first off validate inputs sensible 80 81 if (mb_strlen($_POST['ReportHeading']) <2) { 82 $InputError = 1; 83 prnMsg(_('The report heading must be more than two characters long') . '. ' . _('No report heading was entered'),'error',_('Heading too long')); 84 } 85 if ($_POST['GroupByData1']=='' OR !isset($_POST['GroupByData1']) OR $_POST['GroupByData1']=='Not Used') { 86 $InputError = 1; 87 prnMsg (_('A group by item must be specified for the report to have any output'),'error',_('No Group By selected')); 88 } 89 if ($_POST['GroupByData3']=='Not Used' AND $_POST['GroupByData4']!='Not Used') { 90 // If GroupByData3 is blank but GroupByData4 is used then move GroupByData3 to GroupByData2 91 $_POST['GroupByData3'] = $_POST['GroupByData4']; 92 $_POST['Lower3'] = $_POST['Lower4']; 93 $_POST['Upper3'] = $_POST['Upper4']; 94 } 95 if ($_POST['GroupByData2']=='Not Used' AND $_POST['GroupByData3']!='Not Used') { 96 /*If GroupByData2 is blank but GroupByData3 is used then move GroupByData3 to GroupByData2 */ 97 $_POST['GroupByData2'] = $_POST['GroupByData3']; 98 $_POST['Lower2'] = $_POST['Lower3']; 99 $_POST['Upper2'] = $_POST['Upper3']; 100 } 101 if (($_POST['Lower1']=='' OR $_POST['Upper1']=='')) { 102 $InputError = 1; 103 prnMsg (_('Group by Level 1 is set but the upper and lower limits are not set') . ' - ' . _('these must be specified for the report to have any output'),'error',_('Upper/Lower limits not set')); 104 } 105 if (($_POST['GroupByData2']!='Not Used') AND ($_POST['Lower2']=='' OR $_POST['Upper2']=='')) { 106 $InputError = 1; 107 prnMsg( _('Group by Level 2 is set but the upper and lower limits are not set') . ' - ' . _('these must be specified for the report to have any output'),'error',_('Upper/Lower Limits not set')); 108 } 109 if (($_POST['GroupByData3']!='Not Used') AND ($_POST['Lower3']=='' OR $_POST['Upper3']=='')) { 110 $InputError = 1; 111 prnMsg( _('Group by Level 3 is set but the upper and lower limits are not set') . ' - ' . _('these must be specified for the report to have any output'),'error',_('Upper/Lower Limits not set')); 112 } 113 if (($_POST['GroupByData4']!='Not Used') AND ($_POST['Lower4']=='' OR $_POST['Upper4']=='')) { 114 $InputError = 1; 115 prnMsg( _('Group by Level 4 is set but the upper and lower limits are not set') . ' - ' . _('these must be specified for the report to have any output'),'error',_('Upper/Lower Limits not set')); 116 } 117 if ($_POST['GroupByData1']!='Not Used' AND $_POST['Lower1'] > $_POST['Upper1']) { 118 $InputError = 1; 119 prnMsg(_('Group by Level 1 is set but the lower limit is greater than the upper limit') . ' - ' . _('the report will have no output'),'error',_('Lower Limit > Upper Limit')); 120 } 121 if ($_POST['GroupByData2']!='Not Used' AND $_POST['Lower2'] > $_POST['Upper2']) { 122 $InputError = 1; 123 prnMsg(_('Group by Level 2 is set but the lower limit is greater than the upper limit') . ' - ' . _('the report will have no output'),'error',_('Lower Limit > Upper Limit')); 124 } 125 if ($_POST['GroupByData3']!='Not Used' AND $_POST['Lower3'] > $_POST['Upper3']) { 126 $InputError = 1; 127 prnMsg(_('Group by Level 3 is set but the lower limit is greater than the upper limit') . ' - ' . _('the report will have no output'),'error',_('Lower Limit > Upper Limit')); 128 } 129 if ($_POST['GroupByData4']!='Not Used' AND $_POST['Lower4'] > $_POST['Upper4']) { 130 $InputError = 1; 131 prnMsg(_('Group by Level 4 is set but the lower limit is greater than the upper limit') . ' - ' . _('the report will have no output'),'error',_('Lower Limit > Upper Limit')); 132 } 133 134 135 136 if (isset($SelectedReport) AND $InputError !=1) { 137 138 /*SelectedReport could also exist if submit had not been clicked this code 139 would not run in this case cos submit is false of course see the 140 delete code below*/ 141 142 $sql = "UPDATE reportheaders SET 143 reportheading='" . $_POST['ReportHeading'] . "', 144 groupbydata1='" . $_POST['GroupByData1'] . "', 145 groupbydata2='" . $_POST['GroupByData2'] . "', 146 groupbydata3='" . $_POST['GroupByData3'] . "', 147 groupbydata4='" . $_POST['GroupByData4'] . "', 148 newpageafter1='" . $_POST['NewPageAfter1'] . "', 149 newpageafter2='" . $_POST['NewPageAfter2'] . "', 150 newpageafter3='" . $_POST['NewPageAfter3'] . "', 151 lower1='" . filter_number_format($_POST['Lower1']) . "', 152 upper1='" . filter_number_format($_POST['Upper1']) . "', 153 lower2='" . filter_number_format($_POST['Lower2']) . "', 154 upper2='" . filter_number_format($_POST['Upper2']) . "', 155 lower3='" . filter_number_format($_POST['Lower3']) . "', 156 upper3='" . filter_number_format($_POST['Upper3']) . "', 157 lower4='" . filter_number_format($_POST['Lower4']) . "', 158 upper4='" . filter_number_format($_POST['Upper4']) . "' 159 WHERE reportid = " . $SelectedReport; 160 161 $ErrMsg = _('The report could not be updated because'); 162 $DbgMsg = _('The SQL used to update the report headers was'); 163 $result = DB_query($sql,$ErrMsg,$DbgMsg); 164 165 prnMsg( _('The') .' ' . $_POST['ReportHeading'] . ' ' . _('report has been updated'),'success', 'Report Updated'); 166 unset($SelectedReport); 167 unset($_POST['ReportHeading']); 168 unset($_POST['GroupByData1']); 169 unset($_POST['GroupByData2']); 170 unset($_POST['GroupByData3']); 171 unset($_POST['GroupByData4']); 172 unset($_POST['NewPageAfter1']); 173 unset($_POST['NewPageAfter2']); 174 unset($_POST['NewPageAfter3']); 175 unset($_POST['Lower1']); 176 unset($_POST['Upper1']); 177 unset($_POST['Lower2']); 178 unset($_POST['Upper2']); 179 unset($_POST['Lower3']); 180 unset($_POST['Upper3']); 181 unset($_POST['Lower4']); 182 unset($_POST['Upper4']); 183 184 } elseif ($InputError !=1) { 185 186 /*SelectedReport is null cos no item selected on first time round so must be adding a new report */ 187 188 $sql = "INSERT INTO reportheaders ( 189 reportheading, 190 groupbydata1, 191 groupbydata2, 192 groupbydata3, 193 groupbydata4, 194 newpageafter1, 195 newpageafter2, 196 newpageafter3, 197 lower1, 198 upper1, 199 lower2, 200 upper2, 201 lower3, 202 upper3, 203 lower4, 204 upper4 ) 205 VALUES ( 206 '" . $_POST['ReportHeading'] . "', 207 '" . $_POST['GroupByData1']. "', 208 '" . $_POST['GroupByData2'] . "', 209 '" . $_POST['GroupByData3'] . "', 210 '" . $_POST['GroupByData4'] . "', 211 '" . $_POST['NewPageAfter1'] . "', 212 '" . $_POST['NewPageAfter2'] . "', 213 '" . $_POST['NewPageAfter3'] . "', 214 '" . filter_number_format($_POST['Lower1']) . "', 215 '" . filter_number_format($_POST['Upper1']) . "', 216 '" . filter_number_format($_POST['Lower2']) . "', 217 '" . filter_number_format($_POST['Upper2']) . "', 218 '" . filter_number_format($_POST['Lower3']) . "', 219 '" . filter_number_format($_POST['Upper3']) . "', 220 '" . filter_number_format($_POST['Lower4']) . "', 221 '" . filter_number_format($_POST['Upper4']) . "' 222 )"; 223 224 $ErrMsg = _('The report could not be added because'); 225 $DbgMsg = _('The SQL used to add the report header was'); 226 $result = DB_query($sql,$ErrMsg,$DbgMsg); 227 228 prnMsg(_('The') . ' ' . $_POST['ReportHeading'] . ' ' . _('report has been added to the database'),'success','Report Added'); 229 230 unset($SelectedReport); 231 unset($_POST['ReportHeading']); 232 unset($_POST['GroupByData1']); 233 unset($_POST['GroupByData2']); 234 unset($_POST['GroupByData3']); 235 unset($_POST['GroupByData4']); 236 unset($_POST['NewPageAfter1']); 237 unset($_POST['NewPageAfter2']); 238 unset($_POST['NewPageAfter3']); 239 unset($_POST['Lower1']); 240 unset($_POST['Upper1']); 241 unset($_POST['Lower2']); 242 unset($_POST['Upper2']); 243 unset($_POST['Lower3']); 244 unset($_POST['Upper3']); 245 unset($_POST['Lower4']); 246 unset($_POST['Upper4']); 247 248 } 249 250 251} elseif (isset($_GET['delete'])) { 252//the link to delete a selected record was clicked instead of the submit button 253 254 $sql="DELETE FROM reportcolumns WHERE reportid='".$SelectedReport."'"; 255 $ErrMsg = _('The deletion of the report column failed because'); 256 $DbgMsg = _('The SQL used to delete the report column was'); 257 258 $result = DB_query($sql,$ErrMsg,$DbgMsg); 259 260 $sql="DELETE FROM reportheaders WHERE reportid='".$SelectedReport."'"; 261 $ErrMsg = _('The deletion of the report heading failed because'); 262 $DbgMsg = _('The SQL used to delete the report headers was'); 263 $result = DB_query($sql,$ErrMsg,$DbgMsg); 264 265 prnMsg(_('Report Deleted') ,'info'); 266 unset($SelectedReport); 267 include ('includes/footer.php'); 268 exit; 269 270} 271 272if (!isset($SelectedReport)) { 273 274/* It could still be the second time the page has been run and a record has been selected for modification - SelectedReport will exist because it was sent with the new call. If its the first time the page has been displayed with no parameters 275then none of the above are true and the list of Reports will be displayed with 276links to delete or edit each. These will call the same page again and allow update/input 277or deletion of the records*/ 278 279 280 $result = DB_query("SELECT reportid, reportheading FROM reportheaders ORDER BY reportid"); 281 282 echo '<table class="selection">'; 283 echo '<tr> 284 <th>' . _('Report No') . '</th> 285 <th>' . _('Report Title') . '</th> 286 </tr>'; 287 288while ($myrow = DB_fetch_array($result)) { 289 290 printf('<tr class="striped_row"> 291 <td>%s</td> 292 <td>%s</td> 293 <td><a href="%s&SelectedReport=%s">' . _('Design') . '</a></td> 294 <td><a href="%s/SalesAnalReptCols.php?ReportID=%s">' . _('Define Columns') . '</a></td> 295 <td><a href="%s/SalesAnalysis_UserDefined.php?ReportID=%s&ProducePDF=True">' . _('Make PDF Report') . '</a></td> 296 <td><a href="%s/SalesAnalysis_UserDefined.php?ReportID=%s&ProduceCVSFile=True">' . _('Make CSV File') . '</a></td> 297 <td><a href="%s&SelectedReport=%s&delete=1" onclick="return confirm(\'' . _('Are you sure you wish to remove this report design?') . '\');">' . _('Delete') . '</a></td> 298 </tr>', 299 $myrow[0], 300 $myrow[1], 301 htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '?', 302 $myrow[0], 303 $RootPath, 304 $myrow[0], 305 $RootPath, 306 $myrow[0], 307 $RootPath, 308 $myrow[0], 309 htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '?', 310 $myrow[0]); 311 312 } 313 //END WHILE LIST LOOP 314 echo '</table><br />'; 315} 316 317//end of ifs and buts! 318 319 320 321if (isset($SelectedReport)) { 322 echo '<a href="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '">' . _('Show All Defined Reports') . '</a>'; 323} 324 325echo '<br />'; 326 327 328if (!isset($_GET['delete'])) { 329 echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '">'; 330 echo '<div>'; 331 echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; 332 333 if (isset($SelectedReport)) { 334 //editing an existing Report 335 336 $sql = "SELECT reportid, 337 reportheading, 338 groupbydata1, 339 newpageafter1, 340 upper1, 341 lower1, 342 groupbydata2, 343 newpageafter2, 344 upper2, 345 lower2, 346 groupbydata3, 347 upper3, 348 lower3, 349 newpageafter3, 350 groupbydata4, 351 upper4, 352 lower4 353 FROM reportheaders 354 WHERE reportid='".$SelectedReport."'"; 355 356 $ErrMsg = _('The reports for display could not be retrieved because'); 357 $DbgMsg = _('The SQL used to retrieve the report headers was'); 358 $result = DB_query($sql, $ErrMsg, $DbgMsg); 359 360 $myrow = DB_fetch_array($result); 361 362 $ReportID = $myrow['reportid']; 363 $_POST['ReportHeading'] = $myrow['reportheading']; 364 $_POST['GroupByData1'] = $myrow['groupbydata1']; 365 $_POST['NewPageAfter1'] = $myrow['newpageafter1']; 366 $_POST['Upper1'] = $myrow['upper1']; 367 $_POST['Lower1'] = $myrow['lower1']; 368 $_POST['GroupByData2'] = $myrow['groupbydata2']; 369 $_POST['NewPageAfter2'] = $myrow['newpageafter2']; 370 $_POST['Upper2'] = $myrow['upper2']; 371 $_POST['Lower2'] = $myrow['lower2']; 372 $_POST['GroupByData3'] = $myrow['groupbydata3']; 373 $_POST['Upper3'] = $myrow['upper3']; 374 $_POST['Lower3'] = $myrow['lower3']; 375 $_POST['GroupByData4'] = $myrow['groupbydata4']; 376 $_POST['Upper4'] = $myrow['upper4']; 377 $_POST['Lower4'] = $myrow['lower4']; 378 379 echo '<input type="hidden" name="SelectedReport" value="' . $SelectedReport . '" />'; 380 echo '<input type="hidden" name="ReportID" value="' . $ReportID . '" />'; 381 echo '<table width="98%" class="selection"> 382 <tr> 383 <th colspan="8"><h3>' . _('Edit The Selected Report') . '</h3></th> 384 </tr>'; 385 } else { 386 echo '<table width="98%" class="selection"> 387 <tr> 388 <th colspan="8"><h3>' . _('Define A New Report') . '</h3></th> 389 </tr>'; 390 } 391 392 if (!isset($_POST['ReportHeading'])) { 393 $_POST['ReportHeading']=''; 394 } 395 echo '<tr> 396 <td class="number">' . _('Report Heading') . ':</td> 397 <td colspan="2"><input type="text" size="80" maxlength="80" name="ReportHeading" value="' . $_POST['ReportHeading'] . '" /></td> 398 </tr>'; 399 400 echo '<tr> 401 <td>' . _('Group By 1') . ': <select name="GroupByData1">'; 402 403 GrpByDataOptions($_POST['GroupByData1']); 404 405 echo '</select></td> 406 <td>' . _('Page Break After') . ': <select name="NewPageAfter1">'; 407 408 if ($_POST['NewPageAfter1']==0){ 409 echo '<option selected="selected" value="0">' . _('No') . '</option>'; 410 echo '<option value="1">' . _('Yes') . '</option>'; 411 } Else { 412 echo '<option value="0">' . _('No') . '</option>'; 413 echo '<option selected="selected" value="1">' . _('Yes') . '</option>'; 414 } 415 416 echo '</select></td>'; 417 418 if (!isset($_POST['Lower1'])) { 419 $_POST['Lower1'] = ''; 420 } 421 422 if (!isset($_POST['Upper1'])) { 423 $_POST['Upper1'] = ''; 424 } 425 echo '<td>' . _('From') . ': <input type="text" name="Lower1" size="10" maxlength="10" value="' . $_POST['Lower1'] . '" /></td> 426 <td>' . _('To') . ': <input type="text" name="Upper1" size="10" maxlength="10" value="' . $_POST['Upper1'] .'" /></td> 427 </tr> 428 <tr> 429 <td>' . _('Group By 2') . ': <select name="GroupByData2">'; 430 431 GrpByDataOptions($_POST['GroupByData2']); 432 433 echo '</select></td> 434 <td>' . _('Page Break After') . ': <select name="NewPageAfter2">'; 435 436 if ($_POST['NewPageAfter2']==0){ 437 echo '<option selected="selected" value="0">' . _('No') . '</option>'; 438 echo '<option value="1">' . _('Yes') . '</option>'; 439 } Else { 440 echo '<option value="0">' . _('No') . '</option>'; 441 echo '<option selected="selected" value="1">' . _('Yes') . '</option>'; 442 } 443 444 if (!isset($_POST['Lower2'])) { 445 $_POST['Lower2'] = ''; 446 } 447 448 if (!isset($_POST['Upper2'])) { 449 $_POST['Upper2'] = ''; 450 } 451 452 echo '</select></td>'; 453 echo '<td>' . _('From') . ': <input type="text" name="Lower2" size="10" maxlength="10" value="' . $_POST['Lower2'] . '" /></td> 454 <td>' . _('To') . ': <input type="text" name="Upper2" size="10" maxlength="10" value="' . $_POST['Upper2'] . '" /></td> 455 </tr> 456 <tr> 457 <td>' . _('Group By 3') . ': <select name="GroupByData3">'; 458 459 GrpByDataOptions($_POST['GroupByData3']); 460 461 echo '</select></td> 462 <td>' . _('Page Break After') . ': <select name="NewPageAfter3">'; 463 464 if ($_POST['NewPageAfter3']==0){ 465 echo '<option selected="selected" value="0">' . _('No') . '</option>'; 466 echo '<option value="1">' . _('Yes') . '</option>'; 467 } else { 468 echo '<option value="0">' . _('No') . '</option>'; 469 echo '<option selected="selected" value="1">' . _('Yes') . '</option>'; 470 } 471 472 echo '</select></td>'; 473 474 if (!isset($_POST['Lower3'])) { 475 $_POST['Lower3'] = ''; 476 } 477 478 if (!isset($_POST['Upper3'])) { 479 $_POST['Upper3'] = ''; 480 } 481 482 echo '<td>' . _('From') . ': <input type="text" name="Lower3" size="10" maxlength="10" value="' . $_POST['Lower3'] . '" /></td> 483 <td>' . _('To') . ': <input type="text" name="Upper3" size="10" maxlength="10" value="' . $_POST['Upper3'] . '" /></td> 484 </tr> 485 <tr> 486 <td>' . _('Group By 4') . ': <select name="GroupByData4">'; 487 488 GrpByDataOptions($_POST['GroupByData4']); 489 490 echo '</select></td> 491 <td></td>'; 492 493 if (!isset($_POST['Lower4'])) { 494 $_POST['Lower4'] = ''; 495 } 496 497 if (!isset($_POST['Upper4'])) { 498 $_POST['Upper4'] = ''; 499 } 500 501 echo '<td>' . _('From') .': <input type="text" name="Lower4" size="10" maxlength="10" value="' . $_POST['Lower4'] . '" /></td> 502 <td>' . _('To') . ': <input type="text" name="Upper4" size="10" maxlength="10" value="' . $_POST['Upper4'] . '" /></td> 503 </tr>'; 504 505 echo '</table>'; 506 507 echo '<br /> 508 <div class="centre"> 509 <input type="submit" name="submit" value="' . _('Enter Information') . '" /> 510 </div> 511 </div> 512 </form>'; 513 514} //end if record deleted no point displaying form to add record 515 516include('includes/footer.php'); 517?>
