[Weberp-svn] SF.net SVN: weberp:[9440] trunk
Brought to you by:
sotandeka,
tim_schofield
Menu
▾
▴
[Weberp-svn] SF.net SVN: weberp:[9440] trunk
|
From: <tim...@us...> - 2012-07-18 10:01:56
|
Revision: 9440
http://weberp.svn.sourceforge.net/weberp/?rev=9440&view=rev
Author: tim_schofield
Date: 2012-07-18 10:01:45 +0000 (Wed, 18 Jul 2012)
Log Message:
-----------
Partially fixes the delete scripts.
Modified Paths:
--------------
trunk/Z_DeleteCreditNote.php
trunk/Z_DeleteInvoice.php
Modified: trunk/Z_DeleteCreditNote.php
===================================================================
--- trunk/Z_DeleteCreditNote.php 2012-07-18 08:44:22 UTC (rev 9439)
+++ trunk/Z_DeleteCreditNote.php 2012-07-18 10:01:45 UTC (rev 9440)
@@ -18,15 +18,20 @@
if (!isset($_GET['CreditNoteNo'])){
- prnMsg(_('This page must be called with the credit note number') . ' - ' . _('it is not intended for use by non-system administrators'),'info');
+ prnMsg(_('This page must be called with the credit note number') . ' - ' . _('it is not intended for use by non-system administrators'),'info');
}
/*get the order number that was credited */
-$SQL = "SELECT order_ FROM debtortrans WHERE transno='" . $_GET['CreditNoteNo'] . "' AND type='11'";
+$SQL = "SELECT order_,
+ id
+ FROM debtortrans
+ WHERE transno='" . $_GET['CreditNoteNo'] . "'
+ AND type='11'";
$Result = DB_query($SQL, $db);
$myrow = DB_fetch_array($Result);
$OrderNo = $myrow['order_'];
+$IDDebtorTrans = $myrow['id'];
/*Now get the stock movements that were credited into an array */
@@ -51,10 +56,32 @@
$Result = DB_Txn_Begin($db); /* commence a database transaction */
+
+/*Now delete the custallocns */
+
+$SQL = "DELETE custallocns FROM custallocns
+ WHERE transid_allocto ='" . $IDDebtorTrans . "'";
+
+$DbgMsg = _('The SQL that failed was');
+$ErrMsg = _('The custallocns record could not be deleted') . ' - ' . _('the sql server returned the following error');
+$Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
+
+prnMsg(_('The custallocns record has been deleted'),'info');
+
+/*Now delete the debtortranstaxes */
+
+$SQL = "DELETE debtortranstaxes FROM debtortranstaxes
+ WHERE debtortransid ='" . $IDDebtorTrans . "'";
+$DbgMsg = _('The SQL that failed was');
+$ErrMsg = _('The debtortranstaxes record could not be deleted') . ' - ' . _('the sql server returned the following error');
+$Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
+
+prnMsg(_('The debtortranstaxes record has been deleted'),'info');
+
/*Now delete the DebtorTrans */
$SQL = "DELETE FROM debtortrans
- WHERE transno ='" . $_GET['CreditNoteNo'] . "' AND Type=11";
+ WHERE transno ='" . $_GET['CreditNoteNo'] . "' AND Type=11";
$DbgMsg = _('The SQL that failed was');
$ErrMsg = _('A problem was encountered trying to delete the Debtor transaction record');
$Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
@@ -64,8 +91,8 @@
foreach ($StockMovement as $CreditLine) {
$SQL = "UPDATE salesorderdetails SET qtyinvoiced = qtyinvoiced - " . $CreditLine['qty'] . "
- WHERE orderno = '" . $OrderNo . "'
- AND stkcode = '" . $CreditLine['stockid'] . "'";
+ WHERE orderno = '" . $OrderNo . "'
+ AND stkcode = '" . $CreditLine['stockid'] . "'";
$ErrMsg =_('A problem was encountered attempting to reverse the update the sales order detail record') . ' - ' . _('the SQL server returned the following error message');
$Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg, true);
@@ -80,13 +107,14 @@
$Result = DB_query($SQL, $db,$ErrMsg,$DbgMsg, true);
-/*Delete Sales Analysis records */
+/*Delete Sales Analysis records
+ * This is unreliable as the salesanalysis record contains totals for the item cust custbranch periodno */
$SQL = "DELETE FROM salesanalysis
- WHERE periodno = '" . $CreditLine['prd'] . "'
- AND cust='" . $CreditLine['debtorno'] . "'
- AND custbranch = '" . $CreditLine['branchcode'] . "'
- AND qty = '" . $CreditLine['qty'] . "'
- AND stockid = '" . $CreditLine['stockid'] . "'";
+ WHERE periodno = '" . $CreditLine['prd'] . "'
+ AND cust='" . $CreditLine['debtorno'] . "'
+ AND custbranch = '" . $CreditLine['branchcode'] . "'
+ AND qty = '" . $CreditLine['qty'] . "'
+ AND stockid = '" . $CreditLine['stockid'] . "'";
$ErrMsg = _('The SQL to delete the sales analysis records with the message');
@@ -96,7 +124,7 @@
/* Delete the stock movements */
$SQL = "DELETE FROM stockmoves
- WHERE type=11 AND transno = '" . $_GET['CreditNoteNo'] . "'";
+ WHERE type=11 AND transno = '" . $_GET['CreditNoteNo'] . "'";
$ErrMsg = _('SQL to delete the stock movement record failed with the message');
$Result = DB_query($SQL, $db,$ErrMsg,$DbgMsg,true);
Modified: trunk/Z_DeleteInvoice.php
===================================================================
--- trunk/Z_DeleteInvoice.php 2012-07-18 08:44:22 UTC (rev 9439)
+++ trunk/Z_DeleteInvoice.php 2012-07-18 10:01:45 UTC (rev 9440)
@@ -5,7 +5,7 @@
/* Script to delete an invoice expects and invoice number to delete
not included on any menu for obvious reasons
*
-* STRONGLY RECOMMEND NOT USING THIS -CREDIT THE INVOICE AND RE INVOICE
+* STRONGLY RECOMMEND NOT USING THIS - CREDIT THE INVOICE AND RE INVOICE
* *
*
This page must be called directly using path/Z_DeleteInvoice.php?InvoiceNo=????? !! */
@@ -16,13 +16,14 @@
include('includes/header.inc');
if (!isset($_GET['InvoiceNo'])){
- prnMsg(_('This page must be called with the InvoiceNo to delete Z_DeleteInvoice.php?InvoiceNo=XX') . '. ' . _('This page should not be run by non-system administrators'),'info');
- include('includes/footer.inc');
- exit;
+ prnMsg(_('This page must be called with the InvoiceNo to delete Z_DeleteInvoice.php?InvoiceNo=XX') . '. ' . _('This page should not be run by non-system administrators'),'info');
+ include('includes/footer.inc');
+ exit;
}
/*Get the order number that was invoiced */
-$SQL = "SELECT order_
+$SQL = "SELECT order_,
+ id
FROM debtortrans
WHERE debtortrans.type = 10
AND transno = '" . $_GET['InvoiceNo'] . "'";
@@ -31,6 +32,7 @@
$myrow = DB_fetch_array($Result);
$ProcessingOrder = $myrow['order_'];
+$IDDebtorTrans = $myrow['id'];
/*Now get the stock movements that were invoiced into an array */
@@ -45,15 +47,15 @@
// We now use fully qualified column names
$SQL = "SELECT stockmoves.stockid,
- stockmoves.loccode,
- stockmoves.debtorno,
- stockmoves.branchcode,
- stockmoves.prd,
- stockmoves.qty,
- stockmaster.mbflag
- FROM stockmoves INNER JOIN stockmaster
- ON stockmoves.stockid = stockmaster.stockid
- WHERE transno ='" .$_GET['InvoiceNo'] . "' AND type=10";
+ stockmoves.loccode,
+ stockmoves.debtorno,
+ stockmoves.branchcode,
+ stockmoves.prd,
+ stockmoves.qty,
+ stockmaster.mbflag
+ FROM stockmoves INNER JOIN stockmaster
+ ON stockmoves.stockid = stockmaster.stockid
+ WHERE transno ='" .$_GET['InvoiceNo'] . "' AND type=10";
$Result = DB_query($SQL,$db);
@@ -72,18 +74,40 @@
/*Delete any log entries */
$SQL = "DELETE FROM orderdeliverydifferenceslog
- WHERE orderno = '". $ProcessingOrder . "'
- AND invoiceno = '" . $_GET['InvoiceNo'] . "'";
+ WHERE orderno = '". $ProcessingOrder . "'
+ AND invoiceno = '" . $_GET['InvoiceNo'] . "'";
$ErrMsg = _('The SQL to delete the delivery differences records failed because');
$Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
prnMsg(_('Any order delivery differences records have been deleted'),'info');
+/*Now delete the custallocns */
+
+$SQL = "DELETE custallocns FROM custallocns
+ WHERE transid_allocto ='" . $IDDebtorTrans . "'";
+
+$DbgMsg = _('The SQL that failed was');
+$ErrMsg = _('The custallocns record could not be deleted') . ' - ' . _('the sql server returned the following error');
+$Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
+
+prnMsg(_('The custallocns record has been deleted'),'info');
+
+/*Now delete the debtortranstaxes */
+
+$SQL = "DELETE debtortranstaxes FROM debtortranstaxes
+ WHERE debtortransid ='" . $IDDebtorTrans . "'";
+$DbgMsg = _('The SQL that failed was');
+$ErrMsg = _('The debtortranstaxes record could not be deleted') . ' - ' . _('the sql server returned the following error');
+$Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
+
+prnMsg(_('The debtortranstaxes record has been deleted'),'info');
+
+
/*Now delete the DebtorTrans */
$SQL = "DELETE FROM debtortrans
- WHERE transno ='" . $_GET['InvoiceNo'] . "'
- AND debtortrans.type=10";
+ WHERE transno ='" . $_GET['InvoiceNo'] . "'
+ AND debtortrans.type=10";
$DbgMsg = _('The SQL that failed was');
$ErrMsg = _('The debtorTrans record could not be deleted') . ' - ' . _('the sql server returned the following error');
$Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
@@ -96,30 +120,30 @@
foreach ($StockMovement as $OrderLine) {
$SQL = "UPDATE salesorderdetails SET qtyinvoiced = qtyinvoiced - " . $OrderLine['qty'] . ",
- completed = 0
- WHERE orderno = '" . $ProcessingOrder . "'
- AND stkcode = '" . $OrderLine['stockid'] . "'";
+ completed = 0
+ WHERE orderno = '" . $ProcessingOrder . "'
+ AND stkcode = '" . $OrderLine['stockid'] . "'";
$ErrMsg = _('The SQL to reverse the update of the sales order detail records failed because');
$Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
- prnMsg(_('The sales order records have been updated as not invoiced'),'info');
+ prnMsg(_('The sales order records have been updated as not invoiced'),'info');
/*reverse the update to LocStock */
- if ($OrderLine['mbflag']!='A' AND $OrderLine['mbflag']!='D'){
+ if ($OrderLine['mbflag']!='A' AND $OrderLine['mbflag']!='D'){
- $ErrMsg = _('The SQL to reverse update to the location stock records failed because');
- $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
- prnMsg(_('Reversed the location stock quantities for items that decreased'),'info');
- }
+ $ErrMsg = _('The SQL to reverse update to the location stock records failed because');
+ $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
+ prnMsg(_('Reversed the location stock quantities for items that decreased'),'info');
+ }
/*This is a problem - should only update sales analysis what happens where there
have been previous sales to the same customer/branch for the same item
Delete Sales Analysis records */
$SQL = "DELETE FROM salesanalysis
- WHERE periodno = '" . $OrderLine['prd'] . "'
- AND cust='" . $OrderLine['debtorno'] . "'
- AND custbranch = '" . $OrderLine['branchcode'] . "'
- AND qty = '" . $OrderLine['qty'] . "'
- AND stockid = '" . $OrderLine['stockid'] . "'";
+ WHERE periodno = '" . $OrderLine['prd'] . "'
+ AND cust='" . $OrderLine['debtorno'] . "'
+ AND custbranch = '" . $OrderLine['branchcode'] . "'
+ AND qty = '" . $OrderLine['qty'] . "'
+ AND stockid = '" . $OrderLine['stockid'] . "'";
$ErrMsg = _('The SQL to delete the sales analysis records failed because');
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
