1<?php 2 3 4// Add, Edit, Delete, and List MRP demand records. Table is mrpdemands. 5// Have separate functions for each routine. Use pass-by-reference - (&$StockID) - 6// to pass value of $StockID to functions. 7 8include('includes/session.php'); 9$Title = _('MRP Demands'); 10$ViewTopic= 'MRP'; 11$BookMark = 'MRP_MasterSchedule'; 12include('includes/header.php'); 13 14if (isset($_POST['DemandID'])){ 15 $DemandID =$_POST['DemandID']; 16} elseif (isset($_GET['DemandID'])){ 17 $DemandID =$_GET['DemandID']; 18} 19 20if (isset($_POST['StockID'])){ 21 $StockID =trim(mb_strtoupper($_POST['StockID'])); 22} elseif (isset($_GET['StockID'])){ 23 $StockID =trim(mb_strtoupper($_GET['StockID'])); 24} 25 26echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/inventory.png" title="' . 27 _('Inventory') . '" alt="" />' . ' ' . $Title . '</p>'; 28 29if (isset($_POST['Search'])) { 30 search($StockID); 31} elseif (isset($_POST['submit'])) { 32 submit($StockID,$DemandID); 33} elseif (isset($_GET['delete'])) { 34 delete($DemandID,'',$StockID); 35} elseif (isset($_POST['deletesome'])) { 36 delete('',$_POST['MRPDemandtype'],$StockID); 37} elseif (isset($_GET['listall'])) { 38 listall('',''); 39} elseif (isset($_POST['listsome'])) { 40 listall($StockID,$_POST['MRPDemandtype']); 41} else { 42 display($StockID,$DemandID); 43} 44 45function search(&$StockID) { //####SEARCH_SEARCH_SEARCH_SEARCH_SEARCH_SEARCH_SEARCH_##### 46 47// Search by partial part number or description. Display the part number and description from 48// the stockmaster so user can select one. If the user clicks on a part number 49// MRPDemands.php is called again, and it goes to the display() routine. 50 51 // Work around to auto select 52 if ($_POST['Keywords']=='' AND $_POST['StockCode']=='') { 53 $_POST['StockCode']='%'; 54 } 55 if ($_POST['Keywords'] AND $_POST['StockCode']) { 56 $msg=_('Stock description keywords have been used in preference to the Stock code extract entered'); 57 } 58 if ($_POST['Keywords']=='' AND $_POST['StockCode']=='') { 59 $msg=_('At least one stock description keyword or an extract of a stock code must be entered for the search'); 60 } else { 61 if (mb_strlen($_POST['Keywords'])>0) { 62 //insert wildcard characters in spaces 63 $SearchString = '%' . str_replace(' ', '%', $_POST['Keywords']) . '%'; 64 65 $sql = "SELECT stockmaster.stockid, 66 stockmaster.description 67 FROM stockmaster 68 WHERE stockmaster.description " . LIKE . " '" . $SearchString ."' 69 ORDER BY stockmaster.stockid"; 70 71 } elseif (mb_strlen($_POST['StockCode'])>0){ 72 $sql = "SELECT stockmaster.stockid, 73 stockmaster.description 74 FROM stockmaster 75 WHERE stockmaster.stockid " . LIKE . "'%" . $_POST['StockCode'] . "%' 76 ORDER BY stockmaster.stockid"; 77 78 } 79 80 $ErrMsg = _('The SQL to find the parts selected failed with the message'); 81 $result = DB_query($sql,$ErrMsg); 82 83 } //one of keywords or StockCode was more than a zero length string 84 85 // If the SELECT found records, display them 86 if (DB_num_rows($result) > 0) { 87 echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post">'; 88 echo '<div>'; 89 echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; 90 echo '<table cellpadding="2" class="selection">'; 91 $TableHeader = '<tr><th>' . _('Code') . '</th> 92 <th>' . _('Description') . '</th> 93 </tr>'; 94 echo $TableHeader; 95 96 $j = 1; 97 98 while ($myrow=DB_fetch_array($result)) { 99 $tabindex=$j+4; 100 echo '<tr class="striped_row"> 101 <td><input tabindex="' . $tabindex . '" type="submit" name="StockID" value="' . $myrow['stockid'] .'" /></td> 102 <td>' . $myrow['description'] . '</td> 103 </tr>'; 104 $j++; 105 } //end of while loop 106 107 echo '</table>'; 108 echo '</div>'; 109 echo '</form>'; 110 111} else { 112 prnMsg(_('No record found in search'),'error'); 113 unset ($StockID); 114 display($StockID,$DemandID); 115} 116 117 118} // End of function search() 119 120 121function submit(&$StockID,&$DemandID) //####SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT#### 122{ 123// In this section if hit submit button. Do edit checks. If all checks pass, see if record already 124// exists for StockID/Duedate/MRPDemandtype combo; that means do an Update, otherwise, do INSERT. 125//initialise no input errors assumed initially before we test 126 // echo "<br/>Submit - DemandID = $DemandID<br/>"; 127 $FormatedDuedate = FormatDateForSQL($_POST['Duedate']); 128 $InputError = 0; 129 130 /* actions to take once the user has clicked the submit button 131 ie the page has called itself with some user input */ 132 133 //first off validate inputs sensible 134 135 if (!is_numeric(filter_number_format($_POST['Quantity']))) { 136 $InputError = 1; 137 prnMsg(_('Quantity must be numeric'),'error'); 138 } 139 if (filter_number_format($_POST['Quantity']) <= 0) { 140 $InputError = 1; 141 prnMsg(_('Quantity must be greater than 0'),'error'); 142 } 143 if (!Is_Date($_POST['Duedate'])) { 144 $InputError = 1; 145 prnMsg(_('Invalid due date'),'error'); 146 } 147 $sql = "SELECT * FROM mrpdemandtypes 148 WHERE mrpdemandtype='" . $_POST['MRPDemandtype'] . "'"; 149 $result = DB_query($sql); 150 151 if (DB_num_rows($result) == 0){ 152 $InputError = 1; 153 prnMsg(_('Invalid demand type'),'error'); 154 } 155// Check if valid part number - Had done a Select Count(*), but that returned a 1 in DB_num_rows 156// even if there was no record. 157 $sql = "SELECT * FROM stockmaster 158 WHERE stockid='" . $StockID . "'"; 159 $result = DB_query($sql); 160 161 if (DB_num_rows($result) == 0){ 162 $InputError = 1; 163 prnMsg($StockID . ' ' . _('is not a valid item code'),'error'); 164 unset ($_POST['StockID']); 165 unset($StockID); 166 } 167// Check if part number/demand type/due date combination already exists 168 $sql = "SELECT * FROM mrpdemands 169 WHERE stockid='" . $StockID . "' 170 AND mrpdemandtype='" . $_POST['MRPDemandtype'] . "' 171 AND duedate='" . $FormatedDuedate . "' 172 AND demandid <> '" . $DemandID . "'"; 173 $result = DB_query($sql); 174 175 if (DB_num_rows($result) > 0){ 176 $InputError = 1; 177 prnMsg(_('Record already exists for part number/demand type/date'),'error'); 178 } 179 180 if ($InputError !=1){ 181 $sql = "SELECT COUNT(*) FROM mrpdemands 182 WHERE demandid='" . $DemandID . "' 183 GROUP BY demandid"; 184 $result = DB_query($sql); 185 $myrow = DB_fetch_row($result); 186 187 if ($myrow[0]>0) { 188 //If $myrow[0] > 0, it means this is an edit, so do an update 189 $sql = "UPDATE mrpdemands SET quantity = '" . filter_number_format($_POST['Quantity']) . "', 190 mrpdemandtype = '" . trim(mb_strtoupper($_POST['MRPDemandtype'])) . "', 191 duedate = '" . $FormatedDuedate . "' 192 WHERE demandid = '" . $DemandID . "'"; 193 $msg = _("The MRP demand record has been updated for").' '.$StockID; 194 } else { 195 196 // If $myrow[0] from SELECT count(*) is zero, this is an entry of a new record 197 $sql = "INSERT INTO mrpdemands (stockid, 198 mrpdemandtype, 199 quantity, 200 duedate) 201 VALUES ('" . $StockID . "', 202 '" . trim(mb_strtoupper($_POST['MRPDemandtype'])) . "', 203 '" . filter_number_format($_POST['Quantity']) . "', 204 '" . $FormatedDuedate . "' 205 )"; 206 $msg = _('A new MRP demand record has been added to the database for') . ' ' . $StockID; 207 } 208 209 210 $result = DB_query($sql,_('The update/addition of the MRP demand record failed because')); 211 prnMsg($msg,'success'); 212 echo '<br />'; 213 unset ($_POST['MRPDemandtype']); 214 unset ($_POST['Quantity']); 215 unset ($_POST['StockID']); 216 unset ($_POST['Duedate']); 217 unset ($StockID); 218 unset ($DemandID); 219 } // End of else where DB_num_rows showed there was a valid stockmaster record 220 221 display($StockID,$DemandID); 222} // End of function submit() 223 224 225function delete($DemandID,$DemandType,$StockID) { //####DELETE_DELETE_DELETE_DELETE_DELETE_DELETE_#### 226 227// If wanted to have a Confirm routine before did actually deletion, could check if 228// deletion = "yes"; if it did, display link that redirects back to this page 229// like this - <a href=" ' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '?&delete=confirm&StockID=' . "$StockID" . ' "> 230// that sets delete=confirm. If delete=confirm, do actually deletion. 231// This deletes an individual record by DemandID if called from a listall that shows 232// edit/delete or deletes all of a particular demand type if press Delete Demand Type button. 233 $where = " "; 234 if ($DemandType) { 235 $where = " WHERE mrpdemandtype ='" . $DemandType . "'"; 236 } 237 if ($DemandID) { 238 $where = " WHERE demandid ='" . $DemandID . "'"; 239 } 240 $sql="DELETE FROM mrpdemands 241 $where"; 242 $result = DB_query($sql); 243 if ($DemandID) { 244 prnMsg(_('The MRP demand record for') .' '. $StockID .' '. _('has been deleted'),'succes'); 245 } else { 246 prnMsg(_('All records for demand type') .' '. $DemandType .' ' . _('have been deleted'),'succes'); 247 } 248 unset ($DemandID); 249 unset ($StockID); 250 display($stockID,$DemandID); 251 252} // End of function delete() 253 254 255function listall($part,$DemandType) {//####LISTALL_LISTALL_LISTALL_LISTALL_LISTALL_LISTALL_LISTALL_#### 256 257// List all mrpdemands records, with anchors to Edit or Delete records if hit List All anchor 258// Lists some in hit List Selection submit button, and uses part number if it is entered or 259// demandtype 260 261 echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') .'" method="post">'; 262 echo '<div>'; 263 echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; 264 $where = " "; 265 if ($DemandType) { 266 $where = " WHERE mrpdemandtype ='" . $DemandType . "'"; 267 } 268 if ($part) { 269 $where = " WHERE mrpdemands.stockid ='" . $part . "'"; 270 } 271 // If part is entered, it overrides demandtype 272 $sql = "SELECT mrpdemands.demandid, 273 mrpdemands.stockid, 274 mrpdemands.mrpdemandtype, 275 mrpdemands.quantity, 276 mrpdemands.duedate, 277 stockmaster.description, 278 stockmaster.decimalplaces 279 FROM mrpdemands 280 LEFT JOIN stockmaster on mrpdemands.stockid = stockmaster.stockid" . 281 $where . " ORDER BY mrpdemands.stockid, mrpdemands.duedate"; 282 283 $ErrMsg = _('The SQL to find the parts selected failed with the message'); 284 $result = DB_query($sql,$ErrMsg); 285 286 echo '<table class="selection"> 287 <tr> 288 <th>' . _('Part Number') . '</th> 289 <th>' . _('Description') . '</th> 290 <th>' . _('Demand Type') . '</th> 291 <th>' . _('Quantity') . '</th> 292 <th>' . _('Due Date') . '</th> 293 </tr>'; 294 $ctr = 0; 295 while ($myrow = DB_fetch_array($result)) { 296 $displaydate = ConvertSQLDate($myrow[4]); 297 $ctr++; 298 echo '<tr><td>' . $myrow['stockid'] . '</td> 299 <td>' . $myrow['description'] . '</td> 300 <td>' . $myrow['mrpdemandtype'] . '</td> 301 <td>' . locale_number_format($myrow['quantity'],$myrow['decimalplaces']) . '</td> 302 <td>' . $displaydate . '</td> 303 <td><a href="' .htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') .'?DemandID=' . $myrow['demandid'] . '&StockID=' . $myrow['stockid'] . '">' . _('Edit') . '</a></td> 304 <td><a href="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '?DemandID=' . $myrow['demandid'] . '&StockID=' . $myrow['stockid'].'&delete=yes" onclick="return confirm(\'' . _('Are you sure you wish to delete this demand?') . '\');">' . _('Delete') . '</a></td> 305 </tr>'; 306 } 307 308 //END WHILE LIST LOOP 309 echo '<tr><td>' . _('Number of Records') . '</td> 310 <td>' . $ctr . '</td></tr>'; 311 echo '</table>'; 312 echo '</div>'; 313 echo '</form><br/><br/><br/><br/>'; 314 unset ($StockID); 315 display($StockID,$DemandID); 316 317} // End of function listall() 318 319 320function display(&$StockID,&$DemandID) { //####DISPLAY_DISPLAY_DISPLAY_DISPLAY_DISPLAY_DISPLAY_##### 321 322// Display Seach fields at top and Entry form below that. This function is called the first time 323// the page is called, and is also invoked at the end of all of the other functions. 324// echo "<br/>DISPLAY - DemandID = $DemandID<br/>"; 325 echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post">'; 326 echo '<div>'; 327 echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; 328 if (!isset($StockID)) { 329 echo'<table cellpadding="3" class="selection"><tr> 330 <td>' . _('Enter text extracts in the') . ' <b>' . _('description') . '</b>:</td> 331 <td><input tabindex="1" type="text" name="Keywords" size="20" maxlength="25" /></td> 332 <td><b>' . _('OR') . '</b></td> 333 <td>' . _('Enter extract of the') . ' <b>' . _('Stock Code') . '</b>:</td> 334 <td><input tabindex="2" type="text" name="StockCode" size="15" maxlength="20" /></td> 335 <td><b>' . _('OR') . '</b></td> 336 <td><a href="'. htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '?listall=yes">' . _('List All Demands') . '</a></td></tr> 337 <tr><td colspan="7"><div class="centre"><input tabindex="3" type="submit" name="Search" value="' . _('Search Now') . 338 '" /></div></td></tr></table>'; 339 } else { 340 if (isset($DemandID)) { 341 //editing an existing MRP demand 342 343 $sql = "SELECT demandid, 344 stockid, 345 mrpdemandtype, 346 quantity, 347 duedate 348 FROM mrpdemands 349 WHERE demandid='" . $DemandID . "'"; 350 $result = DB_query($sql); 351 $myrow = DB_fetch_array($result); 352 353 if (DB_num_rows($result) > 0){ 354 $_POST['DemandID'] = $myrow['demandid']; 355 $_POST['StockID'] = $myrow['stockid']; 356 $_POST['MRPDemandtype'] = $myrow['mrpdemandtype']; 357 $_POST['Quantity'] = locale_number_format($myrow['quantity'],'Variable'); 358 $_POST['Duedate'] = ConvertSQLDate($myrow['duedate']); 359 } 360 361 echo '<input type="hidden" name="DemandID" value="' . $_POST['DemandID'] . '" />'; 362 echo '<input type="hidden" name="StockID" value="' . $_POST['StockID'] . '" />'; 363 echo '<table class="selection"> 364 <tr> 365 <td>' ._('Part Number') . ':</td> 366 <td>' . $_POST['StockID'] . '</td> 367 </tr>'; 368 369 } else { 370 if (!isset($_POST['StockID'])) { 371 $_POST['StockID'] = ''; 372 } 373 echo '<table class="selection"> 374 <tr> 375 <td>' . _('Part Number') . ':</td> 376 <td><input type="text" name="StockID" size="21" maxlength="20" value="' . $_POST['StockID'] . '" /></td> 377 </tr>'; 378 } 379 380 381 if (!isset($_POST['Quantity'])) { 382 $_POST['Quantity']=0; 383 } 384 385 if (!isset($_POST['Duedate'])) { 386 $_POST['Duedate']=' '; 387 } 388 389 echo '<tr><td>' . _('Quantity') . ':</td> 390 <td><input type="text" name="Quantity" class="number" size="6" maxlength="6" value="' . $_POST['Quantity'] . '" /></td> 391 </tr> 392 <tr> 393 <td>' . _('Due Date') . ':</td> 394 <td><input type="text" class="date" name="Duedate" size="11" maxlength="10" value="' . $_POST['Duedate'] . '" /></td> 395 </tr>'; 396 // Generate selections for Demand Type 397 echo '<tr> 398 <td>' . _('Demand Type') . '</td> 399 <td><select name="MRPDemandtype">'; 400 401 $sql = "SELECT mrpdemandtype, 402 description 403 FROM mrpdemandtypes"; 404 $result = DB_query($sql); 405 while ($myrow = DB_fetch_array($result)) { 406 if (isset($_POST['MRPDemandtype']) and $myrow['mrpdemandtype']==$_POST['MRPDemandtype']) { 407 echo '<option selected="selected" value="'; 408 } else { 409 echo '<option value="'; 410 } 411 echo $myrow['mrpdemandtype'] . '">' . $myrow['mrpdemandtype'] . ' - ' .$myrow['description'] . '</option>'; 412 } //end while loop 413 echo '</select></td> 414 </tr> 415 </table> 416 <br /> 417 <div class="centre"> 418 <input type="submit" name="submit" value="' . _('Enter Information') . '" /> 419 <input type="submit" name="listsome" value="' . _('List Selection') . '" /> 420 <input type="submit" name="deletesome" value="' . _('Delete Demand Type') . '" />'; 421 // If mrpdemand record exists, display option to delete it 422 if ((isset($DemandID)) AND (DB_num_rows($result) > 0)) { 423 echo '<br/><br/><a href="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '?delete=yes&StockID='.$StockID.'&DemandID=' . $DemandID . '" onclick="return confirm(\'' . _('Are you sure you wish to delete this demand?') . '\');">' . _('Or Delete Record') . '</a>'; 424 } 425 echo '</div>'; 426 } 427 echo '</div> 428 </form>'; 429 430} // End of function display() 431 432include('includes/footer.php'); 433?>
