1<?php 2 3 4include('includes/session.php'); 5$Title=_('Apply Current Cost to Sales Analysis'); 6include('includes/header.php'); 7 8$Period = 42; 9 10echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '">'; 11echo '<div>'; 12echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; 13 14$SQL = "SELECT MonthName(lastdate_in_period) AS mnth, 15 YEAR(lastdate_in_period) AS yr, 16 periodno 17 FROM periods"; 18echo '<br /><div class="centre">' . _('Select the Period to update the costs for') . ':<select name="PeriodNo">'; 19$result = DB_query($SQL); 20 21echo '<option selected="selected" value="0">' . _('No Period Selected') . '</option>'; 22 23while ($PeriodInfo=DB_fetch_array($result)){ 24 25 echo '<option value="' . $PeriodInfo['periodno'] . '">' . $PeriodInfo['mnth'] . ' ' . $PeriodInfo['Yr'] . '</option>'; 26 27} 28 29echo '</select>'; 30 31echo '<br /><input type="submit" name="UpdateSalesAnalysis" value="' . _('Update Sales Analysis Costs') .'" /></div>'; 32echo '</div></form>'; 33 34if (isset($_POST['UpdateSalesAnalysis']) AND $_POST['PeriodNo']!=0){ 35 $sql = "SELECT stockmaster.stockid, 36 materialcost+overheadcost+labourcost AS standardcost, 37 stockmaster.mbflag 38 FROM salesanalysis INNER JOIN stockmaster 39 ON salesanalysis.stockid=stockmaster.stockid 40 WHERE periodno='" . $_POST['PeriodNo'] . "' 41 AND stockmaster.mbflag<>'D' 42 GROUP BY stockmaster.stockid, 43 stockmaster.materialcost, 44 stockmaster.overheadcost, 45 stockmaster.labourcost, 46 stockmaster.mbflag"; 47 48 49 $ErrMsg = _('Could not retrieve the sales analysis records to be updated because'); 50 $result = DB_query($sql,$ErrMsg); 51 52 while ($ItemsToUpdate = DB_fetch_array($result)){ 53 54 if ($ItemsToUpdate['mbflag']=='A'){ 55 $SQL = "SELECT SUM(materialcost + labourcost + overheadcost) AS standardcost 56 FROM stockmaster INNER JOIN BOM 57 ON stockmaster.stockid = bom.component 58 WHERE bom.parent = '" . $ItemsToUpdate['stockid'] . "' 59 AND bom.effectiveto > '" . Date('Y-m-d') . "' 60 AND bom.effectiveafter < '" . Date('Y-m-d') . "'"; 61 62 $ErrMsg = _('Could not recalculate the current cost of the assembly item') . $ItemsToUpdate['stockid'] . ' ' . _('because'); 63 $AssemblyCostResult = DB_query($SQL,$ErrMsg); 64 $AssemblyCost = DB_fetch_row($AssemblyCostResult); 65 $Cost = $AssemblyCost[0]; 66 } else { 67 $Cost = $ItemsToUpdate['standardcost']; 68 } 69 70 $SQL = "UPDATE salesanalysis SET cost = (qty * " . $Cost . ") 71 WHERE stockid='" . $ItemsToUpdate['stockid'] . "' 72 AND periodno ='" . $_POST['PeriodNo'] . "'"; 73 74 $ErrMsg = _('Could not update the sales analysis records for') . ' ' . $ItemsToUpdate['stockid'] . ' ' . _('because'); 75 $UpdResult = DB_query($SQL,$ErrMsg); 76 77 78 prnMsg(_('Updated sales analysis for period') . ' ' . $_POST['PeriodNo'] . ' ' . _('and stock item') . ' ' . $ItemsToUpdate['stockid'] . ' ' . _('using a cost of') . ' ' . $Cost,'success'); 79 } 80 81 82 prnMsg(_('Updated the sales analysis cost data for period') . ' '. $_POST['PeriodNo'],'success'); 83} 84include('includes/footer.php'); 85?>