Menu
▾
▴
[Web-erp-svn] SF.net SVN: web-erp:[7804] trunk
|
From: <ex...@us...> - 2017-07-04 07:05:12
|
Revision: 7804
http://sourceforge.net/p/web-erp/reponame/7804
Author: exsonqu
Date: 2017-07-04 07:05:09 +0000 (Tue, 04 Jul 2017)
Log Message:
-----------
14/07/17 Exson: Fixed RecurringSalesOrdersProcess.php main menu failure bug. Port Tim's suppliers allocation at payment stage and fixed the cheque no has not stored bug in Payments.php
Modified Paths:
--------------
trunk/Payments.php
trunk/RecurringSalesOrdersProcess.php
trunk/javascripts/MiscFunctions.js
Added Paths:
-----------
trunk/sql/mysql/upgrade4.14.1-4.14.2.sql
Modified: trunk/Payments.php
===================================================================
--- trunk/Payments.php 2017-06-29 23:47:49 UTC (rev 7803)
+++ trunk/Payments.php 2017-07-04 07:05:09 UTC (rev 7804)
@@ -3,7 +3,6 @@
/* Entry of bank account payments either against an AP account or a general ledger payment - if the AP-GL link in company preferences is set */
include('includes/DefinePaymentClass.php');
-
include('includes/session.php');
$Title = _('Payment Entry');
if(isset($_GET['SupplierID'])) {
@@ -163,6 +162,27 @@
}
if(isset($_POST['Paymenttype']) AND $_POST['Paymenttype']!='') {
$_SESSION['PaymentDetail'.$identifier]->Paymenttype = $_POST['Paymenttype'];
+ //lets validate the paymenttype here
+ $sql = "SELECT usepreprintedstationery
+ FROM paymentmethods
+ WHERE paymentname='" . $_SESSION['PaymentDetail' . $identifier]->Paymenttype . "'";
+ $result = DB_query($sql);
+ $myrow = DB_fetch_row($result);
+ if ($myrow[0] == 1) {
+ if (empty($_POST['ChequeNum'])) {
+ prnMsg(_('The cheque number should not be empty'),'warn');
+ $Errors[] = 'ChequeNum';
+ } else {
+ $cheqsql = "SELECT count(chequeno) FROM supptrans WHERE chequeno='" . $_POST['ChequeNum'] . "'";
+ $ErrMsg = _('Failed to retrieve cheque number data');
+ $cheqresult = DB_query($cheqsql,$ErrMsg);
+ $cheqrow = DB_fetch_row($cheqresult);
+ if ($cheqrow[0]>0) {
+ prnMsg(_('The cheque has already been used'),'warn');
+ $Errors[] = 'ChequeNum';
+ }
+ }
+ }
}
if(isset($_POST['Currency']) AND $_POST['Currency']!='') {
@@ -258,7 +278,7 @@
}
-if(isset($_POST['CommitBatch'])) {
+if(isset($_POST['CommitBatch']) AND empty($Errors)) {
/* once the GL analysis of the payment is entered (if the Creditors_GLLink is active),
process all the data in the session cookie into the DB creating a banktrans record for
@@ -319,7 +339,42 @@
AND (!isset($_POST['PaymentCancelled']))
AND ($myrow[0] == 1)) {
// it is a supplier payment by cheque and haven't printed yet so print cheque
+ //check the cheque number
+ if (empty($_POST['ChequeNum'])) {
+ prnMsg(_('There are no Check Number input'),'error');
+ include('includes/footer.inc');
+ exit;
+ } elseif (!is_numeric($_POST['ChequeNum'])){//check if this cheque no has been used
+ prnMsg(_('The cheque no should be numeric'),'error');
+ include('includes/footer.inc');
+ exit;
+ } else {
+ $cheqsql = "SELECT count(chequeno) FROM supptrans WHERE chequeno='" . $_POST['ChequeNum'] . "'";
+ $ErrMsg = _('Failed to retrieve cheque number data');
+ $cheqresult = DB_query($cheqsql,$ErrMsg);
+ $cheqrow = DB_fetch_row($cheqresult);
+ if ($cheqrow[0]>0) {
+ prnMsg(_('The cheque has already been used'),'error');
+ include('includes/footer.inc');
+ exit;
+ }
+ }
+ //store the paid array here;
+ $PaidArray = array();
+ foreach ($_POST as $name=>$Value) {
+ if (substr($name, 0, 4) == 'paid' AND $Value>0) {
+ $PaidArray[substr($name, 4)] = $Value;
+ }
+ }
+ if (!empty($PaidArray)) {
+ $PaidJ = base64_encode(serialize($PaidArray));
+ $PaidInput = '<input type="hidden" name="PaidArray" value="' . $PaidJ . '" />';
+ } else {
+ $PaidInput = '';
+ }
+
+
echo '<br />
<a href="' . $RootPath . '/PrintCheque.php?ChequeNum=' . $_POST['ChequeNum'] . '&identifier=' . $identifier . '">' . _('Print Cheque using pre-printed stationery') . '</a>
<br />
@@ -330,7 +385,10 @@
echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
echo _('Has the cheque been printed') . '?
<br />
- <br />
+ <br />'
+ . $PaidInput . '
+ <input type="hidden" name="BankTransRef" value="' . $_POST['BankTransRef'] . '" />
+ <input type="hidden" name="ChequeNum" value="' . $_POST['ChequeNum'] . '" />
<input type="hidden" name="CommitBatch" value="' . $_POST['CommitBatch'] . '" />
<input type="hidden" name="BankAccount" value="' . $_POST['BankAccount'] . '" />
<input type="submit" name="ChequePrinted" value="' . _('Yes / Continue') . '" />
@@ -416,6 +474,12 @@
/*Make sure to use the same rate if the transfer is between two bank accounts in the same currency */
$TrfToBankExRate = $_SESSION['PaymentDetail'.$identifier]->FunctionalExRate;
}
+ if ($_SESSION['PaymentDetail' . $identifier]->Currency == $TrfToBankCurrCode) {
+ $ExRate = 1;
+ $TrfToBankExRate = $_SESSION['PaymentDetail' . $identifier]->ExRate;
+ } else {
+ $ExRate = ($_SESSION['PaymentDetail'.$identifier]->ExRate * $_SESSION['PaymentDetail' . $identifier]->FunctionalExRate)/$TrfFromBankExRate;
+ }
/*Consider an example
functional currency NZD
@@ -460,8 +524,8 @@
$ReceiptTransNo . "',
2,'" .
$PaymentItem->GLCode . "','" .
- '@' . $TransNo . ' ' . _('Act Transfer From ') . $_SESSION['PaymentDetail'.$identifier]->Account . ' - ' . $PaymentItem->Narrative . "','" .
- (($_SESSION['PaymentDetail'.$identifier]->ExRate * $_SESSION['PaymentDetail'.$identifier]->FunctionalExRate)/$TrfToBankExRate). "','" .
+ '@' . $TransNo . ' ' . _('Act Transfer From') .' '. $_SESSION['PaymentDetail'.$identifier]->Account . ' - ' . $PaymentItem->Narrative . "','" .
+ $ExRate . "','" .
$TrfToBankExRate . "','" .
FormatDateForSQL($_SESSION['PaymentDetail'.$identifier]->DatePaid) . "','" .
$_SESSION['PaymentDetail'.$identifier]->Paymenttype . "','" .
@@ -473,7 +537,20 @@
$result = DB_query($SQL,$ErrMsg,$DbgMsg,true);
}
}
- } else {
+ } else {
+
+ /* Get an array of suuptans id fields that were paid */
+ if (!isset($_POST['PaidArray'])) {
+ $PaidArray = array();
+ foreach ($_POST as $name=>$Value) {
+ if (substr($name, 0, 4) == 'paid' AND $Value>0) {
+ $PaidArray[substr($name, 4)] = $Value;
+ }
+ }
+ } else {
+ $PaidArray = unserialize(base64_decode($_POST['PaidArray']));
+ }
+
/*Its a supplier payment type 22 */
$CreditorTotal = (($_SESSION['PaymentDetail'.$identifier]->Discount + $_SESSION['PaymentDetail'.$identifier]->Amount)/$_SESSION['PaymentDetail'.$identifier]->ExRate)/$_SESSION['PaymentDetail'.$identifier]->FunctionalExRate;
@@ -490,7 +567,8 @@
suppreference,
rate,
ovamount,
- transtext
+ transtext,
+ chequeno
) VALUES ('" .
$TransNo . "',
22,'" .
@@ -501,11 +579,48 @@
($_SESSION['PaymentDetail'.$identifier]->FunctionalExRate * $_SESSION['PaymentDetail'.$identifier]->ExRate) . "','" .
(-$_SESSION['PaymentDetail'.$identifier]->Amount-$_SESSION['PaymentDetail'.$identifier]->Discount) . "','" .
$_SESSION['PaymentDetail'.$identifier]->supptrans_transtext .
- "')";
+ "',
+ '" . $_POST['ChequeNum'] . "')";
$ErrMsg = _('Cannot insert a payment transaction against the supplier because');
$DbgMsg = _('Cannot insert a payment transaction against the supplier using the SQL');
$result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
+ $SQL = "SELECT id FROM supptrans WHERE transno='" . $TransNo . "' AND type=22";
+ $Result = DB_query($SQL,'','',true);
+ $MyRow = DB_fetch_array($Result);
+ $PaymentID = $MyRow['id'];
+ if (sizeof($PaidArray) > 0) {
+ foreach ($PaidArray as $PaidID=>$PaidAmount) {
+ /* Firstly subtract from the payment the amount of the invoice */
+ $SQL = "UPDATE supptrans SET alloc=alloc-" . $PaidAmount . " WHERE id='" . $PaymentID . "'";
+ $ErrMsg = _('Cannot update an allocation against the supplier because');
+ $DbgMsg = _('Cannot update an allocation against the supplier using the SQL');
+ $Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
+ /* Then add theamount of the invoice to the invoice allocation */
+ $SQL = "UPDATE supptrans SET alloc=alloc+" . $PaidAmount . " WHERE id='" . $PaidID . "'";
+ $ErrMsg = _('Cannot update an allocation against the supplier because');
+ $DbgMsg = _('Cannot update an allocation against the supplier using the SQL');
+ $Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
+ /* Finally update the supplier allocations table */
+ $SQL = "INSERT INTO suppallocs (amt,
+ datealloc,
+ transid_allocfrom,
+ transid_allocto
+ ) VALUES (
+ '" . $PaidAmount . "',
+ '" . FormatDateForSQL($_SESSION['PaymentDetail' . $identifier]->DatePaid) . "',
+ '" . $PaymentID . "',
+ '" . $PaidID . "'
+ )";
+ $ErrMsg = _('Cannot update an allocation against the supplier because');
+ $DbgMsg = _('Cannot update an allocation against the supplier using the SQL');
+ $Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
+ }
+ }
+
+
+
+
/*Update the supplier master with the date and amount of the last payment made */
$SQL = "UPDATE suppliers
SET lastpaiddate = '" . FormatDateForSQL($_SESSION['PaymentDetail'.$identifier]->DatePaid) . "',
@@ -607,7 +722,8 @@
transdate,
banktranstype,
amount,
- currcode
+ currcode,
+ chequeno
) VALUES ('" .
$TransNo . "','" .
$TransType . "','" .
@@ -618,7 +734,8 @@
FormatDateForSQL($_SESSION['PaymentDetail'.$identifier]->DatePaid) . "','" .
$_SESSION['PaymentDetail'.$identifier]->Paymenttype . "','" .
-$_SESSION['PaymentDetail'.$identifier]->Amount . "','" .
- $_SESSION['PaymentDetail'.$identifier]->Currency .
+ $_SESSION['PaymentDetail'.$identifier]->Currency . "','" .
+ $_POST['ChequeNum'] .
"')";
$ErrMsg = _('Cannot insert a bank transaction because');
$DbgMsg = _('Cannot insert a bank transaction using the SQL');
@@ -640,6 +757,7 @@
unset($_POST['supptrans_transtext']);
unset($_POST['Amount']);
unset($_POST['Discount']);
+ unset($_POST['FunctionalExRate']);
unset($_SESSION['PaymentDetail'.$identifier]->GLItems);
unset($_SESSION['PaymentDetail'.$identifier]->SupplierID);
unset($_SESSION['PaymentDetail'.$identifier]);
@@ -677,10 +795,11 @@
$_POST['Narrative'] = $_SESSION['PaymentDetail'.$identifier]->Narrative;
} elseif(isset($_POST['Process']) AND !$BankAccountEmpty) { //user hit submit a new GL Analysis line into the payment
+ if (!empty($_POST['Cheque'])) {
+ $ChequeNoSQL="SELECT transno FROM supptrans WHERE chequeno='" . $_POST['Cheque'] ."'";
+ $ChequeNoResult=DB_query($ChequeNoSQL);
+ }
- $ChequeNoSQL="SELECT account FROM gltrans WHERE chequeno='" . $_POST['Cheque'] ."'";
- $ChequeNoResult=DB_query($ChequeNoSQL);
-
if(is_numeric($_POST['GLManualCode'])) {
$SQL = "SELECT accountname
@@ -692,7 +811,7 @@
if(DB_num_rows($Result)==0) {
prnMsg( _('The manual GL code entered does not exist in the database') . ' - ' . _('so this GL analysis item could not be added'),'warn');
unset($_POST['GLManualCode']);
- } elseif(DB_num_rows($ChequeNoResult)!=0 AND $_POST['Cheque']!='') {
+ } elseif(isset($ChequeNoResult) AND DB_num_rows($ChequeNoResult)!=0 AND $_POST['Cheque']!='') {
prnMsg( _('The Cheque/Voucher number has already been used') . ' - ' . _('This GL analysis item could not be added'),'error');
} else {
$myrow = DB_fetch_array($Result);
@@ -794,7 +913,7 @@
<br />
<table class="selection">
<tr>
- <th colspan="2"><h3>' . _('Payment');
+ <th colspan="4"><h3>' . _('Payment');
if($_SESSION['PaymentDetail'.$identifier]->SupplierID!='') {
echo ' ' . _('to') . ' ' . $_SESSION['PaymentDetail'.$identifier]->SuppName;
@@ -900,11 +1019,11 @@
}
if($_SESSION['PaymentDetail'.$identifier]->AccountCurrency != $_SESSION['PaymentDetail'.$identifier]->Currency AND isset($_SESSION['PaymentDetail'.$identifier]->AccountCurrency)) {
if (isset($SuggestedExRate) AND ($_POST['ExRate'] == 1 OR $_POST['Currency'] != $_POST['PreviousCurrency'] OR $_POST['PreviousBankAccount'] != $_SESSION['PaymentDetail' . $identifier]->Account)) {
- $_POST['ExRate'] = locale_number_format($SuggestedExRate,8);
+ $_POST['ExRate'] = locale_number_format($SuggestedExRate,12);
}
if(isset($SuggestedExRate)) {
- $SuggestedExRateText = '<b>' . _('Suggested rate:') . ' 1 ' . $_SESSION['PaymentDetail'.$identifier]->AccountCurrency . ' = ' . locale_number_format($SuggestedExRate,8) . ' ' . $_SESSION['PaymentDetail'.$identifier]->Currency . '</b>';
+ $SuggestedExRateText = '<b>' . _('Suggested rate:') . ' 1 ' . $_SESSION['PaymentDetail'.$identifier]->AccountCurrency . ' = ' . locale_number_format($SuggestedExRate,12) . ' ' . $_SESSION['PaymentDetail'.$identifier]->Currency . '</b>';
} else {
$SuggestedExRateText = '1 ' . $_SESSION['PaymentDetail'.$identifier]->AccountCurrency . ' = ? ' . $_SESSION['PaymentDetail'.$identifier]->Currency;
}
@@ -951,9 +1070,15 @@
if(!isset($_POST['ChequeNum'])) {
$_POST['ChequeNum']='';
}
+if (!empty($Errors)) {
+ $ErrClass = 'class="error"';
+} else {
+ $ErrClass = '';
+}
+
echo '<tr>
<td>' . _('Cheque Number') . ':</td>
- <td><input maxlength="8" name="ChequeNum" size="10" type="text" value="' . $_POST['ChequeNum'] . '" /> ' . _('(if using pre-printed stationery)') . '</td>
+ <td><input maxlength="8" name="ChequeNum" size="10" type="text" value="' . $_POST['ChequeNum'] . '" ' . $ErrClass . ' /> ' . _('(if using pre-printed stationery)') . '</td>
</tr>';
// Info to be inserted on `banktrans`.`ref` varchar(50):
@@ -969,9 +1094,12 @@
if(!isset($_POST['Narrative'])) {
$_POST['Narrative'] = '';
}
+if (!isset($_POST['Currency'])) {
+ $_POST['Currency'] = '';
+}
echo '<tr>
<td>', _('Narrative'), ':</td>
- <td><input maxlength="200" name="Narrative" size="52" type="text" value="', stripslashes($_POST['Narrative']), '" /> ', _('Narrative in general ledger transactions'), '</td>
+ <td><input maxlength="200" name="Narrative" size="52" type="text" value="', stripslashes($_POST['Narrative']), '" /> ', _('Narrative on General Ledger Account Inquiry'), '</td>
</tr>';
echo '<tr>
@@ -1176,7 +1304,53 @@
<tr>
<th colspan="2"><h3>', _('Supplier Transactions Payment Entry'), '</h3></th>
</tr>';
+ echo '</table>';
+ $SQL = "SELECT systypes.typename,
+ supptrans.id,
+ supptrans.transno,
+ supptrans.suppreference,
+ supptrans.trandate,
+ supptrans.ovamount+supptrans.ovgst+supptrans.diffonexch-supptrans.alloc AS amount
+ FROM supptrans
+ INNER JOIN systypes
+ ON systypes.typeid=supptrans.type
+ WHERE settled=0 AND (systypes.typeid=20 OR systypes.typeid=21 OR (systypes.typeid=22 AND (supptrans.ovamount+supptrans.ovgst+supptrans.diffonexch-supptrans.alloc)>0))
+ AND supplierno='" . $_SESSION['PaymentDetail'.$identifier]->SupplierID . "'
+ AND (supptrans.ovamount+supptrans.ovgst+supptrans.diffonexch-supptrans.alloc)<>0";
+ $Result = DB_query($SQL);
+ echo '<table class="selection">
+ <tr>
+ <th class="Ascending">' . _('Date') . '</th>
+ <th class="Ascending">' . _('Transaction Type') . '</th>
+ <th class="Ascending">' . _('Transaction Number') . '</th>
+ <th class="Ascending">' . _('Reference') . '</th>
+ <th class="Ascending">' . _('Amount') . '</th>
+ <th class="Ascending">' . _('This time to pay') . '</th>
+ </tr>';
+ $ids = '';
+ while ($myrow = DB_fetch_array($Result)) {
+ $ids .= $i>0?';' . $myrow['id']:$myrow['id'];
+ if (!isset($_POST['paid'.$myrow['id']])) {
+ $_POST['paid'.$myrow['id']] = 0;
+ }
+ echo '<tr>
+ <td>' . ConvertSQLDate($myrow['trandate']) . '</td>
+ <td>' . $myrow['typename'] . '</td>
+ <td>' . $myrow['transno'] . '</td>
+ <td>' . $myrow['suppreference'] . '</td>
+ <td class="number">' . locale_number_format($myrow['amount'],$_SESSION['PaymentDetail'.$identifier]->CurrDecimalPlaces) . '</td>
+ <td><input onclick="AddAmount(this,'.$myrow['id'].');" type="checkbox" name="check'.$myrow['id'] . '" value="' . $myrow['amount'] . '" />' . _('Pay') . '</td>
+ <td><input type="text" class="number" id="'.$myrow['id'].'" name="paid'.$myrow['id'].'" value="'.$_POST['paid'.$myrow['id']].'" />
+ <input type="hidden" name="remainamt'.$myrow['id'] .'" value="'.$myrow['amount'].'" />
+ </td>
+ </tr>';
+ $i++;
+ }//end of while loop;
+ echo '</table>';
+ echo '<p>' . _('Total Input Amount').':<input type="text" id="ttl" value="0" readonly>' .' <input type="button" data-ids="' . $ids . '" value="' . _('Update') . '" onclick="update1(\''.$ids.'\')" id="update" /></p>';
+ echo '<table class="selection">';
+
// If the script was called with a SupplierID, it allows to input a customised gltrans.narrative, supptrans.suppreference and supptrans.transtext:
// Info to be inserted on `gltrans`.`narrative` varchar(200):
if(!isset($_POST['gltrans_narrative'])) {
@@ -1206,7 +1380,7 @@
echo '<tr>
<td>',
_('Amount of Payment'), ' ', $_SESSION['PaymentDetail'.$identifier]->Currency, ':</td>
- <td><input class="number" maxlength="12" name="Amount" size="13" type="text" value="', $_SESSION['PaymentDetail'.$identifier]->Amount, '" /></td>
+ <td><input class="number" maxlength="12" id="Amount" name="Amount" size="13" type="text" value="', $_SESSION['PaymentDetail'.$identifier]->Amount, '" /></td>
</tr>';
/* if(isset($_SESSION['PaymentDetail'.$identifier]->SupplierID)) {//included in a if with same condition.*/ /*So it is a supplier payment so show the discount entry item */
@@ -1219,7 +1393,7 @@
echo '<input type="hidden" name="Discount" value="0" />';
}*/
echo '</table><br />';
- echo '<div class="centre"><input type="submit" name="CommitBatch" value="' . _('Accept and Process Payment') . '" /></div>';
+ echo '<div class="centre"><input type="submit" name="CommitBatch" onClick=payVerify("Amount","ttl") value="' . _('Accept and Process Payment') . '" /></div>';
}
echo '</div>';
echo '</form>';
Modified: trunk/RecurringSalesOrdersProcess.php
===================================================================
--- trunk/RecurringSalesOrdersProcess.php 2017-06-29 23:47:49 UTC (rev 7803)
+++ trunk/RecurringSalesOrdersProcess.php 2017-07-04 07:05:09 UTC (rev 7804)
@@ -2,7 +2,7 @@
/* $Id$*/
/*need to allow this script to run from Cron or windows scheduler */
-$AllowAnyone = true;
+//$AllowAnyone = true;
/* Get this puppy to run from cron (cd webERP && php -f RecurringSalesOrdersProcess.php "weberpdemo") or direct URL (RecurringSalesOrdersProcess.php?Database=weberpdemo) */
if (isset($_GET['Database'])) {
Modified: trunk/javascripts/MiscFunctions.js
===================================================================
--- trunk/javascripts/MiscFunctions.js 2017-06-29 23:47:49 UTC (rev 7803)
+++ trunk/javascripts/MiscFunctions.js 2017-07-04 07:05:09 UTC (rev 7804)
@@ -399,4 +399,28 @@
if(d) document.getElementById(d).required="";
}
}
+function update1(s) {
+ var ss=s.split(';');
+ var sss=ss.map((a)=>document.getElementById(a).value);
+ var ttl = sss.reduce((a,b)=>parseFloat(a)+parseFloat(b));
+ document.getElementById('ttl').value = ttl;
+}
+function payVerify(b,a) {
+ var s=document.getElementById('update');
+ var s=s.getAttribute('data-ids');
+ update1(s);
+ var cs=document.getElementById('Amount').getAttribute('class');
+ if (parseFloat(document.getElementById(b).value) < parseFloat(parseFloat(document.getElementById(a).value))){
+ if (cs.indexOf('error') == -1) {
+ document.getElementById('Amount').className="error" + ' ' + cs;
+ }
+ event.preventDefault();
+ } else {
+ if (cs.indexOf('error') != -1) {
+ document.getElementById('Amount').className="number";
+ }
+ return true;
+ }
+}
+
window.onload=initial;
Added: trunk/sql/mysql/upgrade4.14.1-4.14.2.sql
===================================================================
--- trunk/sql/mysql/upgrade4.14.1-4.14.2.sql (rev 0)
+++ trunk/sql/mysql/upgrade4.14.1-4.14.2.sql 2017-07-04 07:05:09 UTC (rev 7804)
@@ -0,0 +1,5 @@
+ALTER table supptrans ADD chequeno varchar(16) NOT NULL DEFAULT '';
+ALTER table supptrans ADD void tinyint(1) NOT NULL DEFAULT 0;
+ALTER table banktrans ADD chequeno varchar(16) NOT NULL DEFAULT '';
+
+
|
