Menu
▾
▴
[Weberp-cvs] SF.net SVN: web-erp:[3697] trunk
|
From: <dai...@us...> - 2010-08-22 08:06:21
|
Revision: 3697
http://web-erp.svn.sourceforge.net/web-erp/?rev=3697&view=rev
Author: daintree
Date: 2010-08-22 08:06:14 +0000 (Sun, 22 Aug 2010)
Log Message:
-----------
contract costing changes - quite a few scripts changed
Modified Paths:
--------------
trunk/ContractBOM.php
trunk/ContractCosting.php
trunk/Contracts.php
trunk/CounterSales.php
trunk/SelectContract.php
trunk/SupplierCredit.php
trunk/doc/Change.log.html
trunk/includes/Contract_Readin.php
trunk/includes/DefineContractClass.php
trunk/includes/SQL_CommonFunctions.inc
trunk/sql/mysql/upgrade3.11.1-3.12.sql
Modified: trunk/ContractBOM.php
===================================================================
--- trunk/ContractBOM.php 2010-08-21 04:53:42 UTC (rev 3696)
+++ trunk/ContractBOM.php 2010-08-22 08:06:14 UTC (rev 3697)
@@ -48,7 +48,7 @@
if (isset($_POST['Search'])){ /*ie seach for stock items */
if ($_POST['Keywords'] AND $_POST['StockCode']) {
- prnMsg=(_('Stock description keywords have been used in preference to the Stock code extract entered'), 'info');
+ prnMsg(_('Stock description keywords have been used in preference to the Stock code extract entered'), 'info');
}
if ($_POST['Keywords']) {
Modified: trunk/ContractCosting.php
===================================================================
--- trunk/ContractCosting.php 2010-08-21 04:53:42 UTC (rev 3696)
+++ trunk/ContractCosting.php 2010-08-22 08:06:14 UTC (rev 3697)
@@ -9,7 +9,6 @@
/* Session started in header.inc for password checking and authorisation level check */
include('includes/header.inc');
-
if (empty($_GET['identifier'])) {
$identifier=date('U');
} else {
@@ -53,8 +52,15 @@
}
echo '<p class="page_title_text">
- <img src="'.$rootpath.'/css/'.$theme.'/images/contract.png" title="' . _('Contract') . '" alt="">
- ' . $_SESSION['Contract'.$identifier]->CustomerName . '<br>' . $_SESSION['Contract'.$identifier]->ContractDescription;
+ <img src="'.$rootpath.'/css/'.$theme.'/images/contract.png" title="' . _('Contract') . '" alt="">';
+if ($_SESSION['Contract'.$identifier]->Status==3){
+ echo _('Closed') . ' ';
+} elseif ($_SESSION['Contract'.$identifier]->Status==2){
+ echo _('Current Confirmed') . ' ';
+} elseif ($_SESSION['Contract'.$identifier]->Status==1){
+ echo _('Quoted') . ' ';
+}
+echo _('Contract') . '<br>' . $_SESSION['Contract'.$identifier]->CustomerName . '<br>' . $_SESSION['Contract'.$identifier]->ContractDescription;
echo '<table>
<tr>
@@ -185,6 +191,226 @@
<td class="number"><b>' . number_format($OtherReqtsActual+$ContractBOMActual,2) . '</b></td></tr>';
echo '</table>';
+
+
+//Do the processing here after the variances are all calculated above
+if (isset($_POST['CloseContract']) AND $_SESSION['Contract'.$identifier]->Status==2){
+
+ include('includes/SQL_CommonFunctions.inc');
+ $GLCodes = GetStockGLCode($_SESSION['Contract'.$identifier]->ContractRef,$db);
+//Compare actual costs to original budgeted contract costs - if actual > budgeted - CR WIP and DR usage variance
+ $Variance = ($OtherReqtsBudget+$ContractBOMBudget)-($OtherReqtsActual+$ContractBOMActual);
+
+ $ContractCloseNo = GetNextTransNo( 32 ,$db);
+ $PeriodNo = GetPeriod(Date($_SESSION['DefaultDateFormat']), $db);
+
+ DB_Txn_Begin($db);
+
+ $SQL = "INSERT INTO gltrans ( type,
+ typeno,
+ trandate,
+ periodno,
+ account,
+ narrative,
+ amount)
+ VALUES ( 32,
+ " . $ContractCloseNo . ",
+ '" . Date('Y-m-d') . "',
+ '" . $PeriodNo . "',
+ '" . $GLCodes['wipact'] . "',
+ '" . _('Variance on contract') . ' ' . $_SESSION['Contract'.$identifier]->ContractRef . "',
+ '" . -$Variance . "')";
+
+ $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The gl entry of WIP for the variance on closing the contract could not be inserted because');
+ $DbgMsg = _('The following SQL to insert the GLTrans record was used');
+ $Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
+ $SQL = "INSERT INTO gltrans ( type,
+ typeno,
+ trandate,
+ periodno,
+ account,
+ narrative,
+ amount)
+ VALUES ( 32,
+ " . $ContractCloseNo . ",
+ '" . Date('Y-m-d') . "',
+ '" . $PeriodNo . "',
+ '" . $GLCodes['materialuseagevarac'] . "',
+ '" . _('Variance on contract') . ' ' . $_SESSION['Contract'.$identifier]->ContractRef . "',
+ '" . $Variance . "')";
+
+ $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The gl entry of WIP for the variance on closing the contract could not be inserted because');
+ $DbgMsg = _('The following SQL to insert the GLTrans record was used');
+ $Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
+
+//Now update the status of the contract to closed
+ $SQL = "UPDATE contracts SET status=3 WHERE contractref='" . $_SESSION['Contract'.$identifier]->ContractRef . "'";
+ $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The status of the contract could not be updated to closed because');
+ $DbgMsg = _('The following SQL to change the status of the contract was used');
+ $Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
+
+/*Check if the contract work order is still open */
+ $CheckIfWOOpenResult = DB_query("SELECT closed
+ FROM workorders
+ WHERE wo='" . $_SESSION['Contract'.$identifier]->WO . "'",
+ $db);
+ $CheckWORow=DB_fetch_row($CheckIfWOOpenResult);
+ if ($CheckWORow[0]==0){
+ //then close the work order
+ $CloseWOResult =DB_query("UPDATE workorders
+ SET closed=1
+ WHERE wo='" . $_SESSION['Contract'.$identifier]->WO . "'",
+ $db,
+ _('Could not update the work order to closed because:'),
+ _('The SQL used to close the work order was:'),
+ true);
+
+
+ /* Check if the contract BOM has received the contract item manually
+ * If not then process this as by closing the contract the user is saying it is complete
+ * If work done on the contract is a write off then the user must also write off the stock of the contract item as a separate job
+ */
+
+ $result =DB_query("SELECT qtyrecd FROM woitems
+ WHERE stockid='" . $_SESSION['Contract'.$identifier]->ContractRef . "'
+ AND wo='" . $_SESSION['Contract'.$identifier]->WO . "'",$db);
+ if (DB_num_rows($result)==1) {
+ $myrow=DB_fetch_row($result);
+ if ($myrow[0]==0){ //then the contract wo has not been received (it will only ever be for 1 item)
+
+ $WOReceiptNo = GetNextTransNo(26, $db);
+
+ /* Need to get the current location quantity will need it later for the stock movement */
+ $SQL = "SELECT locstock.quantity
+ FROM locstock
+ WHERE locstock.stockid='" . $_SESSION['Contract'.$identifier]->ContractRef . "'
+ AND loccode= '" . $_SESSION['Contract'.$identifier]->LocCode . "'";
+
+ $Result = DB_query($SQL, $db);
+ if (DB_num_rows($Result)==1){
+ $LocQtyRow = DB_fetch_row($Result);
+ $QtyOnHandPrior = $LocQtyRow[0];
+ } else {
+ /*There must actually be some error this should never happen */
+ $QtyOnHandPrior = 0;
+ }
+
+ $SQL = "UPDATE locstock
+ SET quantity = locstock.quantity + 1
+ WHERE locstock.stockid = '" . $_SESSION['Contract'.$identifier]->ContractRef . "'
+ AND loccode= '" . $_SESSION['Contract'.$identifier]->LocCode . "'";
+
+ $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The location stock record could not be updated because');
+ $DbgMsg = _('The following SQL to update the location stock record was used');
+ $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
+
+ /*Insert stock movements - with unit cost */
+
+ $SQL = "INSERT INTO stockmoves (stockid,
+ type,
+ transno,
+ loccode,
+ trandate,
+ price,
+ prd,
+ reference,
+ qty,
+ standardcost,
+ newqoh)
+ VALUES ('" . $_SESSION['Contract'.$identifier]->ContractRef . "',
+ 26,
+ " . $WOReceiptNo . ",
+ '" . $_SESSION['Contract'.$identifier]->LocCode . "',
+ '" . Date('Y-m-d') . "',
+ " . ($OtherReqtsBudget+$ContractBOMBudget) . ",
+ " . $PeriodNo . ",
+ '" . $_SESSION['Contract'.$identifier]->WO . "',
+ 1,
+ " . ($OtherReqtsBudget+$ContractBOMBudget) . ",
+ " . ($QtyOnHandPrior + 1) . ")";
+
+ $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('stock movement records could not be inserted when processing the work order receipt because');
+ $DbgMsg = _('The following SQL to insert the stock movement records was used');
+ $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
+
+ /*Get the ID of the StockMove... */
+ $StkMoveNo = DB_Last_Insert_ID($db,'stockmoves','stkmoveno');
+
+ /* If GLLink_Stock then insert GLTrans to debit the GL Code and credit GRN Suspense account at standard cost*/
+ if ($_SESSION['CompanyRecord']['gllink_stock']==1 AND ($OtherReqtsBudget+$ContractBOMBudget)!=0){
+ /*GL integration with stock is activated so need the GL journals to make it so */
+
+ /*first the debit the finished stock of the item received from the WO
+ the appropriate account was already retrieved into the $StockGLCode variable as the Processing code is kicked off
+ it is retrieved from the stock category record of the item by a function in SQL_CommonFunctions.inc*/
+
+ $SQL = "INSERT INTO gltrans (type,
+ typeno,
+ trandate,
+ periodno,
+ account,
+ narrative,
+ amount)
+ VALUES (26,
+ " . $WOReceiptNo . ",
+ '" . Date('Y-m-d') . "',
+ " . $PeriodNo . ",
+ " . $GLCodes['stockact'] . ",
+ '" . $_SESSION['Contract'.$identifier]->WO . ' ' . $_SESSION['Contract'.$identifier]->ContractRef . ' - x 1 @ ' . number_format(($OtherReqtsBudget+$ContractBOMBudget),2) . "',
+ " . ($OtherReqtsBudget+$ContractBOMBudget) . ')';
+
+ $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The receipt of contract work order finished stock GL posting could not be inserted because');
+ $DbgMsg = _('The following SQL to insert the work order receipt of finished items GLTrans record was used');
+ $Result = DB_query($SQL,$db,$ErrMsg, $DbgMsg, true);
+
+ /*now the credit WIP entry*/
+ $SQL = "INSERT INTO gltrans (type,
+ typeno,
+ trandate,
+ periodno,
+ account,
+ narrative,
+ amount)
+ VALUES (26,
+ " . $WOReceiptNo . ",
+ '" . Date('Y-m-d') . "',
+ " . $PeriodNo . ",
+ " . $GLCodes['wipact'] . ",
+ '" . $_SESSION['Contract'.$identifier]->WO . ' ' . $_SESSION['Contract'.$identifier]->ContractRef . ' - x 1 @ ' . number_format(($OtherReqtsBudget+$ContractBOMBudget),2) . "',
+ " . -($OtherReqtsBudget+$ContractBOMBudget) . ")";
+
+ $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The WIP credit on receipt of finished items from a work order GL posting could not be inserted because');
+ $DbgMsg = _('The following SQL to insert the WIP GLTrans record was used');
+ $Result = DB_query($SQL,$db, $ErrMsg, $DbgMsg,true);
+
+ } /* end of if GL and stock integrated and standard cost !=0 */
+
+ //update the wo with the new qtyrecd
+ $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' ._('Could not update the work order item record with the total quantity received because');
+ $DbgMsg = _('The following SQL was used to update the work order');
+ $UpdateWOResult =DB_query("UPDATE woitems
+ SET qtyrecd=qtyrecd+1
+ WHERE wo=" . $_SESSION['Contract'.$identifier]->WO . "
+ AND stockid='" . $_SESSION['Contract'.$identifier]->ContractRef . "'",
+ $db,$ErrMsg,$DbgMsg,true);
+ }//end if the contract wo was not received - work order item received/processed above if not
+ }//end if there was a row returned from the woitems query
+ } //end if the work order was still open (so end of closing it and processing receipt if necessary)
+
+ DB_Txn_Commit($db);
+
+ $_SESSION['Contract'.$identifier]->Status=3;
+ prnMsg(_('The contract has been closed. No further charges can be posted against this contract.'),'success');
+
+} //end if Closing the contract Close Contract button hit
+
+if ($_SESSION['Contract'.$identifier]->Status ==2){//the contract is an order being processed now
+
+ echo '<form method="post" action="' . $_SERVER['PHP_SELF'] . '?' .SID .'&SelectedContract=' . $_SESSION['Contract'.$identifier]->ContractRef . '&identifier=' . $identifier . '">';
+ echo '<br><div class="centre"><input type="submit" name="CloseContract" Value="' . _('Close Contract') . '" onclick="return confirm(\'' . _('Closing the contract will prevent further stock being issued to it and charges being made against it. Variances will be taken to the profit and loss account. Are You Sure?') . '\');"></div>';
+ echo '</form>';
+}
+
include('includes/footer.inc');
?>
Modified: trunk/Contracts.php
===================================================================
--- trunk/Contracts.php 2010-08-21 04:53:42 UTC (rev 3696)
+++ trunk/Contracts.php 2010-08-22 08:06:14 UTC (rev 3697)
@@ -27,7 +27,7 @@
* unix timestamp will be sufficient.
*/
-if (empty($_GET['identifier'])) {
+if (!isset($_GET['identifier'])) {
$identifier=date('U');
} else {
$identifier=$_GET['identifier'];
@@ -65,6 +65,7 @@
$_SESSION['Contract'.$identifier]->ContractRef=$_POST['ContractRef'];
$_SESSION['Contract'.$identifier]->ContractDescription=$_POST['ContractDescription'];
$_SESSION['Contract'.$identifier]->CategoryID = $_POST['CategoryID'];
+ $_SESSION['Contract'.$identifier]->LocCode = $_POST['LocCode'];
$_SESSION['Contract'.$identifier]->RequiredDate = $_POST['RequiredDate'];
$_SESSION['Contract'.$identifier]->Margin = $_POST['Margin'];
$_SESSION['Contract'.$identifier]->CustomerRef = $_POST['CustomerRef'];
@@ -201,7 +202,6 @@
* code above if a modification to an existing contract. Also
* $ExistingContract would be set to the ContractRef
* */
-
$_SESSION['ExistingContract']= 0;
$_SESSION['Contract'.$identifier] = new Contract;
@@ -256,6 +256,7 @@
$_SESSION['Contract'.$identifier]->ContractRef=$_POST['ContractRef'];
$_SESSION['Contract'.$identifier]->ContractDescription=$_POST['ContractDescription'];
$_SESSION['Contract'.$identifier]->CategoryID = $_POST['CategoryID'];
+ $_SESSION['Contract'.$identifier]->LocCode = $_POST['LocCode'];
$_SESSION['Contract'.$identifier]->RequiredDate = $_POST['RequiredDate'];
$_SESSION['Contract'.$identifier]->Margin = $_POST['Margin'];
$_SESSION['Contract'.$identifier]->Status = $_POST['Status'];
@@ -263,17 +264,17 @@
$_SESSION['Contract'.$identifier]->ExRate = $_POST['ExRate'];
/*Get the first work centre for the users location - until we set this up properly */
- $result = DB_query("SELECT code FROM workcentres WHERE location='" . $_SESSION['UserStockLocation'] ."'",$db);
+ $result = DB_query("SELECT code FROM workcentres WHERE location='" . $_SESSION['Contract'.$identifier]->LocCode ."'",$db);
if (DB_num_rows($result)>0){
$WorkCentreRow = DB_fetch_row($result);
$WorkCentre = $WorkCentreRow[0];
} else { //need to add a default work centre for the location
$result = DB_query("INSERT INTO workcentres (code, location, description, overheadrecoveryact)
- VALUES ('" . $_SESSION['UserStockLocation'] . "',
- '" . $_SESSION['UserStockLocation'] . "',
- '" . _('Default for') . ' ' . $_SESSION['UserStockLocation'] . "',
+ VALUES ('" . $_SESSION['Contract'.$identifier]->LocCode . "',
+ '" . $_SESSION['Contract'.$identifier]->LocCode . "',
+ '" . _('Default for') . ' ' . $_SESSION['Contract'.$identifier]->LocCode . "',
'" . '1' . "')",$db);
- $WorkCentre = $_SESSION['UserStockLocation'];
+ $WorkCentre = $_SESSION['Contract'.$identifier]->LocCode;
}
/*The above is a bit of a hack to get a default workcentre for a location based on the users default location*/
@@ -283,6 +284,7 @@
debtorno,
branchcode,
categoryid,
+ loccode,
requireddate,
margin,
customerref,
@@ -303,6 +305,7 @@
//then we can accept any changes at all do an update on the whole lot
$sql = "UPDATE contracts SET categoryid = '" . $_POST['CategoryID'] ."',
requireddate = '" . FormatDateForSQL($_POST['RequiredDate']) . "',
+ loccode='" . $_POST['LocCode'] . "',
margin = " . $_POST['Margin'] . ",
customerref = '" . $_POST['CustomerRef'] . "',
exrate = " . $_POST['ExRate'] . "
@@ -386,23 +389,25 @@
} elseif (!$InputError) { /*Its a new contract - so insert */
$sql = "INSERT INTO contracts ( contractref,
- debtorno,
- branchcode,
- contractdescription,
- categoryid,
- requireddate,
- margin,
- customerref,
- exrate)
+ debtorno,
+ branchcode,
+ contractdescription,
+ categoryid,
+ loccode,
+ requireddate,
+ margin,
+ customerref,
+ exrate)
VALUES ('" . $_POST['ContractRef'] . "',
- '" . $_SESSION['Contract'.$identifier]->DebtorNo . "',
- '" . $_SESSION['Contract'.$identifier]->BranchCode . "',
- '" . $_POST['ContractDescription'] . "',
- '" . $_POST['CategoryID'] . "',
- '" . FormatDateForSQL($_POST['RequiredDate']) . "',
- " . $_POST['Margin'] . ",
- '" . $_POST['CustomerRef'] . "',
- ". $_POST['ExRate'] .")";
+ '" . $_SESSION['Contract'.$identifier]->DebtorNo . "',
+ '" . $_SESSION['Contract'.$identifier]->BranchCode . "',
+ '" . $_POST['ContractDescription'] . "',
+ '" . $_POST['CategoryID'] . "',
+ '" . $_POST['LocCode'] . "',
+ '" . FormatDateForSQL($_POST['RequiredDate']) . "',
+ " . $_POST['Margin'] . ",
+ '" . $_POST['CustomerRef'] . "',
+ ". $_POST['ExRate'] .")";
$ErrMsg = _('The new contract could not be added because');
$result = DB_query($sql,$db,$ErrMsg);
@@ -411,26 +416,26 @@
$ErrMsg = _('Could not add a component to the contract bill of material');
foreach ($_SESSION['Contract'.$identifier]->ContractBOM as $Component){
$sql = "INSERT INTO contractbom (contractref,
- stockid,
- workcentreadded,
- quantity)
+ stockid,
+ workcentreadded,
+ quantity)
VALUES ( '" . $_POST['ContractRef'] . "',
- '" . $Component->StockID . "',
- '" . $WorkCentre . "',
- " . $Component->Quantity . ")";
+ '" . $Component->StockID . "',
+ '" . $WorkCentre . "',
+ " . $Component->Quantity . ")";
$result = DB_query($sql,$db,$ErrMsg);
}
$ErrMsg = _('Could not add a requirement to the contract requirements');
foreach ($_SESSION['Contract'.$identifier]->ContractReqts as $Requirement){
$sql = "INSERT INTO contractreqts (contractref,
- requirement,
- costperunit,
- quantity)
+ requirement,
+ costperunit,
+ quantity)
VALUES ( '" . $_POST['ContractRef'] . "',
- '" . $Requirement->Requirement . "',
- '" . $Requirement->CostPerUnit . "',
- " . $Requirement->Quantity . ")";
+ '" . $Requirement->Requirement . "',
+ '" . $Requirement->CostPerUnit . "',
+ " . $Requirement->Quantity . ")";
$result = DB_query($sql,$db,$ErrMsg);
}
prnMsg(_('The new contract has been added to the database'),'success');
@@ -554,7 +559,7 @@
'" . DB_escape_string($CustomerDetailsRow['braddress6']) . "',
'" . DB_escape_string($CustomerDetailsRow['phoneno']) . "',
'" . DB_escape_string($CustomerDetailsRow['email']) . "',
- '" . DB_escape_string($CustomerDetailsRow['defaultlocation']) ."',
+ '" . DB_escape_string($_SESSION['Contract'.$identifier]->LocCode) ."',
'" . FormatDateForSQL($_SESSION['Contract'.$identifier]->RequiredDate) . "',
'" . Date('Y-m-d') . "',
'1' )";
@@ -595,7 +600,6 @@
} //end of if making a quotation
-
if (isset($_POST['SearchCustomers'])){
if (($_POST['CustKeywords']!='') AND (($_POST['CustCode']!='') OR ($_POST['CustPhone']!=''))) {
@@ -613,51 +617,51 @@
$SearchString = '%' . str_replace(' ', '%', $_POST['CustKeywords']) . '%';
$SQL = "SELECT custbranch.brname,
- custbranch.contactname,
- custbranch.phoneno,
- custbranch.faxno,
- custbranch.branchcode,
- custbranch.debtorno,
- debtorsmaster.name
- FROM custbranch
- LEFT JOIN debtorsmaster
- ON custbranch.debtorno=debtorsmaster.debtorno
- WHERE custbranch.brname " . LIKE . " '$SearchString'
- AND custbranch.disabletrans=0
- ORDER BY custbranch.debtorno, custbranch.branchcode";
+ custbranch.contactname,
+ custbranch.phoneno,
+ custbranch.faxno,
+ custbranch.branchcode,
+ custbranch.debtorno,
+ debtorsmaster.name
+ FROM custbranch
+ LEFT JOIN debtorsmaster
+ ON custbranch.debtorno=debtorsmaster.debtorno
+ WHERE custbranch.brname " . LIKE . " '$SearchString'
+ AND custbranch.disabletrans=0
+ ORDER BY custbranch.debtorno, custbranch.branchcode";
} elseif (strlen($_POST['CustCode'])>0){
$_POST['CustCode'] = strtoupper(trim($_POST['CustCode']));
$SQL = "SELECT custbranch.brname,
- custbranch.contactname,
- custbranch.phoneno,
- custbranch.faxno,
- custbranch.branchcode,
- custbranch.debtorno,
- debtorsmaster.name
- FROM custbranch
- LEFT JOIN debtorsmaster
- ON custbranch.debtorno=debtorsmaster.debtorno
- WHERE custbranch.debtorno " . LIKE . " '%" . $_POST['CustCode'] . "%' OR custbranch.branchcode " . LIKE . " '%" . $_POST['CustCode'] . "%'
- AND custbranch.disabletrans=0
- ORDER BY custbranch.debtorno";
-
+ custbranch.contactname,
+ custbranch.phoneno,
+ custbranch.faxno,
+ custbranch.branchcode,
+ custbranch.debtorno,
+ debtorsmaster.name
+ FROM custbranch
+ LEFT JOIN debtorsmaster
+ ON custbranch.debtorno=debtorsmaster.debtorno
+ WHERE custbranch.debtorno " . LIKE . " '%" . $_POST['CustCode'] . "%' OR custbranch.branchcode " . LIKE . " '%" . $_POST['CustCode'] . "%'
+ AND custbranch.disabletrans=0
+ ORDER BY custbranch.debtorno";
+
} elseif (strlen($_POST['CustPhone'])>0){
$SQL = "SELECT custbranch.brname,
- custbranch.contactname,
- custbranch.phoneno,
- custbranch.faxno,
- custbranch.branchcode,
- custbranch.debtorno,
- debtorsmaster.name
- FROM custbranch
- LEFT JOIN debtorsmaster
- ON custbranch.debtorno=debtorsmaster.debtorno
- WHERE custbranch.phoneno " . LIKE . " '%" . $_POST['CustPhone'] . "%'
- AND custbranch.disabletrans=0
- ORDER BY custbranch.debtorno";
+ custbranch.contactname,
+ custbranch.phoneno,
+ custbranch.faxno,
+ custbranch.branchcode,
+ custbranch.debtorno,
+ debtorsmaster.name
+ FROM custbranch
+ LEFT JOIN debtorsmaster
+ ON custbranch.debtorno=debtorsmaster.debtorno
+ WHERE custbranch.phoneno " . LIKE . " '%" . $_POST['CustPhone'] . "%'
+ AND custbranch.disabletrans=0
+ ORDER BY custbranch.debtorno";
}
$ErrMsg = _('The searched customer records requested cannot be retrieved because');
@@ -683,20 +687,20 @@
$_SESSION['Contract'.$identifier]->BranchCode = trim($CustomerBranchArray[1]);
$sql = "SELECT debtorsmaster.name,
- custbranch.brname,
- debtorsmaster.currcode,
- debtorsmaster.holdreason,
- holdreasons.dissallowinvoices,
- currencies.rate
- FROM debtorsmaster INNER JOIN currencies
- ON debtorsmaster.currcode=currencies.currabrev
- INNER JOIN custbranch
- ON debtorsmaster.debtorno=custbranch.debtorno
- INNER JOIN holdreasons
- ON debtorsmaster.holdreason=holdreasons.reasoncode
- WHERE debtorsmaster.debtorno='" . $_SESSION['Contract'.$identifier]->DebtorNo . "'
- AND custbranch.branchcode='" . $_SESSION['Contract'.$identifier]->BranchCode . "'" ;
-
+ custbranch.brname,
+ debtorsmaster.currcode,
+ debtorsmaster.holdreason,
+ holdreasons.dissallowinvoices,
+ currencies.rate
+ FROM debtorsmaster INNER JOIN currencies
+ ON debtorsmaster.currcode=currencies.currabrev
+ INNER JOIN custbranch
+ ON debtorsmaster.debtorno=custbranch.debtorno
+ INNER JOIN holdreasons
+ ON debtorsmaster.holdreason=holdreasons.reasoncode
+ WHERE debtorsmaster.debtorno='" . $_SESSION['Contract'.$identifier]->DebtorNo . "'
+ AND custbranch.branchcode='" . $_SESSION['Contract'.$identifier]->BranchCode . "'" ;
+
$ErrMsg = _('The customer record selected') . ': ' . $_POST['SelectedCustomer'] . ' ' .
_('cannot be retrieved because');
$DbgMsg = _('The SQL used to retrieve the customer details and failed was');
@@ -825,7 +829,7 @@
echo '<input type="hidden" name="ContractRef" value="' . $_SESSION['Contract'.$identifier]->ContractRef . '">' . $_SESSION['Contract'.$identifier]->ContractRef;
}
echo '</td></tr>';
- echo '<tr><td>' . _('Category') . ':</td><td><select name="CategoryID" onChange="ReloadForm(ItemForm.UpdateCategories)">';
+ echo '<tr><td>' . _('Category') . ':</td><td><select name="CategoryID" >';
$sql = 'SELECT categoryid, categorydescription FROM stockcategory WHERE stocktype!="A"';
$ErrMsg = _('The stock categories could not be retrieved because');
@@ -842,7 +846,22 @@
echo '</select><a target="_blank" href="'. $rootpath . '/StockCategories.php?' . SID . '">' . _('Add or Modify Contract Categories') . '</a></td></tr>';
+ $sql = 'SELECT loccode, locationname FROM locations';
+ $ErrMsg = _('The stock locations could not be retrieved because');
+ $DbgMsg = _('The SQL used to retrieve stock locations and failed was');
+ $result = DB_query($sql,$db,$ErrMsg,$DbgMsg);
+
+ echo '<tr><td>' . _('Location') . ':</td><td><select name="LocCode" >';
+ while ($myrow=DB_fetch_array($result)){
+ if (!isset($_SESSION['Contract'.$identifier]->LocCode) or $myrow['loccode']==$_SESSION['Contract'.$identifier]->LocCode){
+ echo '<option selected VALUE="'. $myrow['loccode'] . '">' . $myrow['locationname'] . '</option>';
+ } else {
+ echo '<option VALUE="'. $myrow['loccode'] . '">' . $myrow['locationname'] . '</option>';
+ }
+ }
+ echo '</select></td></tr>';
+
echo '<tr><td>' . _('Contract Description');
echo ':</td><td><textarea name="ContractDescription" style="width:100%" rows=5>' . $_SESSION['Contract'.$identifier]->ContractDescription . '</textarea></td></tr>';
Modified: trunk/CounterSales.php
===================================================================
--- trunk/CounterSales.php 2010-08-21 04:53:42 UTC (rev 3696)
+++ trunk/CounterSales.php 2010-08-22 08:06:14 UTC (rev 3697)
@@ -1300,34 +1300,34 @@
$AssParts['standard']=0;
}
$SQL = "INSERT INTO stockmoves (
- stockid,
- type,
- transno,
- loccode,
- trandate,
- debtorno,
- branchcode,
- prd,
- reference,
- qty,
- standardcost,
- show_on_inv_crds,
- newqoh
- ) VALUES (
- '" . $AssParts['component'] . "',
- 10,
- " . $InvoiceNo . ",
- '" . $_SESSION['Items'.$identifier]->Location . "',
- '" . $DefaultDispatchDate . "',
- '" . $_SESSION['Items'.$identifier]->DebtorNo . "',
- '" . $_SESSION['Items'.$identifier]->Branch . "',
- " . $PeriodNo . ",
- '" . _('Assembly') . ': ' . $OrderLine->StockID . ' ' . _('Order') . ': ' . $OrderNo . "',
- " . -$AssParts['quantity'] * $OrderLine->Quantity . ",
- " . $AssParts['standard'] . ",
- 0,
- newqoh-" . ($AssParts['quantity'] * $OrderLine->Quantity) . "
- )";
+ stockid,
+ type,
+ transno,
+ loccode,
+ trandate,
+ debtorno,
+ branchcode,
+ prd,
+ reference,
+ qty,
+ standardcost,
+ show_on_inv_crds,
+ newqoh
+ ) VALUES (
+ '" . $AssParts['component'] . "',
+ 10,
+ " . $InvoiceNo . ",
+ '" . $_SESSION['Items'.$identifier]->Location . "',
+ '" . $DefaultDispatchDate . "',
+ '" . $_SESSION['Items'.$identifier]->DebtorNo . "',
+ '" . $_SESSION['Items'.$identifier]->Branch . "',
+ " . $PeriodNo . ",
+ '" . _('Assembly') . ': ' . $OrderLine->StockID . ' ' . _('Order') . ': ' . $OrderNo . "',
+ " . -$AssParts['quantity'] * $OrderLine->Quantity . ",
+ " . $AssParts['standard'] . ",
+ 0,
+ newqoh-" . ($AssParts['quantity'] * $OrderLine->Quantity) . "
+ )";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Stock movement records for the assembly components of'). ' '. $OrderLine->StockID . ' ' . _('could not be inserted because');
$DbgMsg = _('The following SQL to insert the assembly components stock movement records was used');
@@ -1357,70 +1357,70 @@
}
if ($MBFlag=='B' OR $MBFlag=='M'){
$SQL = "INSERT INTO stockmoves (
- stockid,
- type,
- transno,
- loccode,
- trandate,
- debtorno,
- branchcode,
- price,
- prd,
- reference,
- qty,
- discountpercent,
- standardcost,
- newqoh,
- narrative )
+ stockid,
+ type,
+ transno,
+ loccode,
+ trandate,
+ debtorno,
+ branchcode,
+ price,
+ prd,
+ reference,
+ qty,
+ discountpercent,
+ standardcost,
+ newqoh,
+ narrative )
VALUES ('" . $OrderLine->StockID . "',
- 10,
- " . $InvoiceNo . ",
- '" . $_SESSION['Items'.$identifier]->Location . "',
- '" . $DefaultDispatchDate . "',
- '" . $_SESSION['Items'.$identifier]->DebtorNo . "',
- '" . $_SESSION['Items'.$identifier]->Branch . "',
- " . $LocalCurrencyPrice . ",
- " . $PeriodNo . ",
- '" . $OrderNo . "',
- " . -$OrderLine->Quantity . ",
- " . $OrderLine->DiscountPercent . ",
- " . $OrderLine->StandardCost . ",
- " . ($QtyOnHandPrior - $OrderLine->Quantity) . ",
- '" . DB_escape_string($OrderLine->Narrative) . "' )";
+ 10,
+ " . $InvoiceNo . ",
+ '" . $_SESSION['Items'.$identifier]->Location . "',
+ '" . $DefaultDispatchDate . "',
+ '" . $_SESSION['Items'.$identifier]->DebtorNo . "',
+ '" . $_SESSION['Items'.$identifier]->Branch . "',
+ " . $LocalCurrencyPrice . ",
+ " . $PeriodNo . ",
+ '" . $OrderNo . "',
+ " . -$OrderLine->Quantity . ",
+ " . $OrderLine->DiscountPercent . ",
+ " . $OrderLine->StandardCost . ",
+ " . ($QtyOnHandPrior - $OrderLine->Quantity) . ",
+ '" . DB_escape_string($OrderLine->Narrative) . "' )";
} else {
// its an assembly or dummy and assemblies/dummies always have nil stock (by definition they are made up at the time of dispatch so new qty on hand will be nil
if (empty($OrderLine->StandardCost)) {
$OrderLine->StandardCost = 0;
}
$SQL = "INSERT INTO stockmoves (
- stockid,
- type,
- transno,
- loccode,
- trandate,
- debtorno,
- branchcode,
- price,
- prd,
- reference,
- qty,
- discountpercent,
- standardcost,
- narrative )
+ stockid,
+ type,
+ transno,
+ loccode,
+ trandate,
+ debtorno,
+ branchcode,
+ price,
+ prd,
+ reference,
+ qty,
+ discountpercent,
+ standardcost,
+ narrative )
VALUES ('" . $OrderLine->StockID . "',
- 10,
- " . $InvoiceNo . ",
- '" . $_SESSION['Items'.$identifier]->Location . "',
- '" . $DefaultDispatchDate . "',
- '" . $_SESSION['Items'.$identifier]->DebtorNo . "',
- '" . $_SESSION['Items'.$identifier]->Branch . "',
- " . $LocalCurrencyPrice . ",
- " . $PeriodNo . ",
- '" . $OrderNo . "',
- " . -$OrderLine->Quantity . ",
- " . $OrderLine->DiscountPercent . ",
- " . $OrderLine->StandardCost . ",
- '" . DB_escape_string($OrderLine->Narrative) . "')";
+ 10,
+ " . $InvoiceNo . ",
+ '" . $_SESSION['Items'.$identifier]->Location . "',
+ '" . $DefaultDispatchDate . "',
+ '" . $_SESSION['Items'.$identifier]->DebtorNo . "',
+ '" . $_SESSION['Items'.$identifier]->Branch . "',
+ " . $LocalCurrencyPrice . ",
+ " . $PeriodNo . ",
+ '" . $OrderNo . "',
+ " . -$OrderLine->Quantity . ",
+ " . $OrderLine->DiscountPercent . ",
+ " . $OrderLine->StandardCost . ",
+ '" . DB_escape_string($OrderLine->Narrative) . "')";
}
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Stock movement records could not be inserted because');
Modified: trunk/SelectContract.php
===================================================================
--- trunk/SelectContract.php 2010-08-21 04:53:42 UTC (rev 3696)
+++ trunk/SelectContract.php 2010-08-22 08:06:14 UTC (rev 3697)
@@ -187,12 +187,15 @@
echo '<td>' . _('n/a') . '</td>';
}
if ($myrow['status']==2){ //the customer has accepted the quote but not completed contract yet
- echo '<td><a href="' . $IssueToWOPage . '">' . $myrow['wo'] . '</a></td>
- <td><a href="' . $CostingPage . '">' . _('View') . '<a></td>';
+ echo '<td><a href="' . $IssueToWOPage . '">' . $myrow['wo'] . '</a></td>';
} else {
- echo '<td>' . _('n/a') . '</td>
- <td>' . _('n/a') . '</td>';
+ echo '<td>' . _('n/a') . '</td>';
}
+ if ($myrow['status']==2 OR $myrow['status']==3){
+ echo '<td><a href="' . $CostingPage . '">' . _('View') . '<a></td>';
+ } else {
+ echo '<td>' . _('n/a') . '</td>';
+ }
echo '<td>' . $myrow['contractref'] . '</td>
<td>' . $myrow['contractdescription'] . '</td>
<td>' . $myrow['customername'] . '</td>
Modified: trunk/SupplierCredit.php
===================================================================
--- trunk/SupplierCredit.php 2010-08-21 04:53:42 UTC (rev 3696)
+++ trunk/SupplierCredit.php 2010-08-22 08:06:14 UTC (rev 3697)
@@ -687,7 +687,7 @@
amount)
VALUES (21, ' .
$CreditNoteNo . ",
- '" . SQLCreditNoteDate. "',
+ '" . $SQLCreditNoteDate. "',
'" . $PeriodNo . "',
'". $WIPAccount . "',
'" . $_SESSION['SuppTrans']->SupplierID . ' ' . _('Contract charge against') . ' ' . $Contract->ContractRef . "',
Modified: trunk/doc/Change.log.html
===================================================================
--- trunk/doc/Change.log.html 2010-08-21 04:53:42 UTC (rev 3696)
+++ trunk/doc/Change.log.html 2010-08-22 08:06:14 UTC (rev 3697)
@@ -1,5 +1,6 @@
<p><font SIZE=4 COLOR=BLUE><b>webERP Change Log</b></font></p>
<p></p>
+<p>21/08/10 Phil: Contract closing changes all the contracts scripts
<p>20/08/10 Paul Thursby: More $msg cleanup. Unused eliminated; Others replaced with prnMsg().</p>
<p>15/08/10 Phil: Decided to have contracts as part of orders module since not really enough links to warrant a new module changes to index.php WWW_Users.php and sql upgrade.</p>
<p>15/08/10 Phil: New script for ContractCosting.php comparison of contract costs budgeted vs incurred. Lot of work on contracts</p>
Modified: trunk/includes/Contract_Readin.php
===================================================================
--- trunk/includes/Contract_Readin.php 2010-08-21 04:53:42 UTC (rev 3696)
+++ trunk/includes/Contract_Readin.php 2010-08-22 08:06:14 UTC (rev 3697)
@@ -5,6 +5,7 @@
$ContractHeaderSQL = "SELECT contractdescription,
contracts.debtorno,
contracts.branchcode,
+ contracts.loccode,
status,
categoryid,
orderno,
@@ -21,7 +22,8 @@
INNER JOIN currencies
ON debtorsmaster.currcode=currencies.currabrev
INNER JOIN custbranch
- ON debtorsmaster.debtorno=custbranch.debtorno
+ ON debtorsmaster.debtorno=custbranch.debtorno
+ AND contracts.branchcode=custbranch.branchcode
WHERE contractref= '" . $ContractRef . "'";
$ErrMsg = _('The contract cannot be retrieved because');
@@ -35,6 +37,7 @@
$_SESSION['Contract'.$identifier]->ContractDescription = $myrow['contractdescription'];
$_SESSION['Contract'.$identifier]->DebtorNo = $myrow['debtorno'];
$_SESSION['Contract'.$identifier]->BranchCode = $myrow['branchcode'];
+ $_SESSION['Contract'.$identifier]->LocCode = $myrow['loccode'];
$_SESSION['Contract'.$identifier]->Status = $myrow['status'];
$_SESSION['Contract'.$identifier]->CategoryID = $myrow['categoryid'];
$_SESSION['Contract'.$identifier]->OrderNo = $myrow['orderno'];
Modified: trunk/includes/DefineContractClass.php
===================================================================
--- trunk/includes/DefineContractClass.php 2010-08-21 04:53:42 UTC (rev 3696)
+++ trunk/includes/DefineContractClass.php 2010-08-22 08:06:14 UTC (rev 3697)
@@ -11,9 +11,10 @@
var $CustomerName;
var $BranchCode;
var $BranchName;
- var $Status; /* 100 = initiated - 1=quoted - 2=completed */
+ var $Status; /* 100 = initiated - 1=quoted - 2=ordered - 3=completed */
var $CategoryID; /* the category where the contract will be when converted to an item for quotation*/
var $OrderNo; /* the order number created when the contract is quoted */
+ var $LocCode; /* the inventory location where the contract is to be performed */
var $CustomerRef;
var $Margin; /*the margin used in quoting for the contract */
var $WO; /*the wo created when the quotation is converted to an order */
Modified: trunk/includes/SQL_CommonFunctions.inc
===================================================================
--- trunk/includes/SQL_CommonFunctions.inc 2010-08-21 04:53:42 UTC (rev 3696)
+++ trunk/includes/SQL_CommonFunctions.inc 2010-08-22 08:06:14 UTC (rev 3697)
@@ -41,14 +41,14 @@
/*Gets the GL Codes relevant to the stock item account from the stock category record */
$QuerySQL = "SELECT stockact,
- adjglact,
- purchpricevaract,
- materialuseagevarac,
- wipact
- FROM stockmaster,
- stockcategory
- WHERE stockmaster.categoryid=stockcategory.categoryid
- AND stockmaster.stockid = '" . $StockID . "'";
+ adjglact,
+ purchpricevaract,
+ materialuseagevarac,
+ wipact
+ FROM stockmaster,
+ stockcategory
+ WHERE stockmaster.categoryid=stockcategory.categoryid
+ AND stockmaster.stockid = '" . $StockID . "'";
$ErrMsg = _('The stock GL codes could not be retrieved because');
$GetStkGLResult = DB_query($QuerySQL, $db, $ErrMsg);
Modified: trunk/sql/mysql/upgrade3.11.1-3.12.sql
===================================================================
--- trunk/sql/mysql/upgrade3.11.1-3.12.sql 2010-08-21 04:53:42 UTC (rev 3696)
+++ trunk/sql/mysql/upgrade3.11.1-3.12.sql 2010-08-22 08:06:14 UTC (rev 3697)
@@ -206,6 +206,7 @@
`contractdescription` text NOT NULL DEFAULT '',
`debtorno` varchar(10) NOT NULL DEFAULT '',
`branchcode` varchar(10) NOT NULL DEFAULT '',
+ `loccode` varchar(5) NOT NULL DEFAULT '',
`status` tinyint NOT NULL DEFAULT 0,
`categoryid` varchar(6) NOT NULL DEFAULT '',
`orderno` int(11) NOT NULL DEFAULT '0',
@@ -220,9 +221,11 @@
KEY `CategoryID` (`categoryid`),
KEY `Status` (`status`),
KEY `WO` (`wo`),
+ KEY `loccode` (`loccode`),
KEY `DebtorNo` (`debtorno`,`branchcode`),
CONSTRAINT `contracts_ibfk_1` FOREIGN KEY (`debtorno`, `branchcode`) REFERENCES `custbranch` (`debtorno`, `branchcode`),
- CONSTRAINT `contracts_ibfk_2` FOREIGN KEY (`categoryid`) REFERENCES `stockcategory` (`categoryid`)
+ CONSTRAINT `contracts_ibfk_2` FOREIGN KEY (`categoryid`) REFERENCES `stockcategory` (`categoryid`),
+ CONSTRAINT `contracts_ibfk_3` FOREIGN KEY (`loccode`) REFERENCES `locations` (`loccode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
@@ -297,3 +300,4 @@
CONSTRAINT `contractcharges_ibfk_2` FOREIGN KEY (`transtype`) REFERENCES `systypes` (`typeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO `systypes` (`typeid`, `typename`, `typeno`) VALUES ('32', 'Contract Close', '1');
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
