[Web-erp-svn] SF.net SVN: web-erp:[7449] trunk

[<ex...@us...>]

Revision: 7449
          http://sourceforge.net/p/web-erp/reponame/7449
Author:   exsonqu
Date:     2016-01-14 10:08:51 +0000 (Thu, 14 Jan 2016)
Log Message:
-----------
14/01/16 Exson: Add Supplier transaction allocation inquiry in SuppWhereAlloc.php and add a link to in SupplierInquiry.php.

Modified Paths:
--------------
    trunk/SupplierInquiry.php
    trunk/includes/MainMenuLinksArray.php
    trunk/sql/mysql/upgrade4.12.3-4.13.sql

Added Paths:
-----------
    trunk/SuppWhereAlloc.php

Added: trunk/SuppWhereAlloc.php
===================================================================
--- trunk/SuppWhereAlloc.php	                        (rev 0)
+++ trunk/SuppWhereAlloc.php	2016-01-14 10:08:51 UTC (rev 7449)
@@ -0,0 +1,200 @@
+<?php
+
+include('includes/session.inc');
+$Title = _('Supplier How Paid Inquiry');
+
+$ViewTopic = 'APInquiries';
+$BookMark = 'WhereAllocated';
+
+include('includes/header.inc');
+if (isset($_GET['TransNo']) AND isset($_GET['TransType'])) {
+	$_POST['TransNo'] = (int)$_GET['TransNo'];
+	$_POST['TransType'] = (int)$_GET['TransType'];
+	$_POST['ShowResults'] = true;
+}
+
+echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post">
+	<div>
+	<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />
+	<p class="page_title_text noprint">
+		<img src="'.$RootPath.'/css/'.$Theme.'/images/money_add.png" title="' .	_('Supplier Where Allocated'). '" alt="" />' . $Title . '
+	</p>
+	<table class="selection noprint">
+	<tr>
+		<td>' . _('Type') . ':</td>
+		<td><select tabindex="1" name="TransType"> ';
+
+if (!isset($_POST['TransType'])){
+	$_POST['TransType']='20';
+}
+if ($_POST['TransType']==20){
+	 echo '<option selected="selected" value="20">' . _('Purchase Invoice') . '</option>
+			<option value="22">' . _('Payment') . '</option>
+			<option value="21">' . _('Debit Note') . '</option>';
+} elseif ($_POST['TransType'] == 22) {
+	echo '<option selected="selected" value="22">' . _('Payment') . '</option>
+			<option value="20">' . _('Purchase Invoice') . '</option>
+			<option value="21">' . _('Debit Note') . '</option>';
+} elseif ($_POST['TransType'] == 21) {
+	echo '<option selected="selected" value="21">' . _('Debit Note') . '</option>
+		<option value="20">' . _('Purchase Invoice') . '</option>
+		<option value="22">' . _('Payment') . '</option>';
+}
+
+echo '</select></td>';
+
+if (!isset($_POST['TransNo'])) {$_POST['TransNo']='';}
+echo '<td>' . _('Transaction Number').':</td>
+		<td><input tabindex="2" type="text" class="number" name="TransNo"  required="required" maxlength="20" size="20" value="'. $_POST['TransNo'] . '" /></td>
+	</tr>
+	</table>
+	<br />
+	<div class="centre noprint">
+		<input tabindex="3" type="submit" name="ShowResults" value="' . _('Show How Allocated') . '" />
+	</div>';
+
+if (isset($_POST['ShowResults']) AND  $_POST['TransNo']==''){
+	echo '<br />';
+	prnMsg(_('The transaction number to be queried must be entered first'),'warn');
+}
+
+if (isset($_POST['ShowResults']) AND $_POST['TransNo']!=''){
+
+
+/*First off get the DebtorTransID of the transaction (invoice normally) selected */
+	$sql = "SELECT supptrans.id,
+				ovamount+ovgst AS totamt,
+				currencies.decimalplaces AS currdecimalplaces,
+				suppliers.currcode
+			FROM supptrans INNER JOIN suppliers
+			ON supptrans.supplierno=suppliers.supplierid
+			INNER JOIN currencies
+			ON suppliers.currcode=currencies.currabrev
+			WHERE type='" . $_POST['TransType'] . "'
+			AND transno = '" . $_POST['TransNo']."'";
+	
+	if ($_SESSION['SalesmanLogin'] != '') {
+			$sql .= " AND supptrans.salesperson='" . $_SESSION['SalesmanLogin'] . "'";
+	}
+	$result = DB_query($sql);
+
+	if (DB_num_rows($result)==1){
+		$myrow = DB_fetch_array($result);
+		$AllocToID = $myrow['id'];
+		$CurrCode = $myrow['currcode'];
+		$CurrDecimalPlaces = $myrow['currdecimalplaces'];
+		$sql = "SELECT type,
+					transno,
+					trandate,
+					supptrans.supplierno,
+					suppreference,
+					supptrans.rate,
+					ovamount+ovgst as totalamt,
+					suppallocs.amt
+				FROM supptrans
+				INNER JOIN suppallocs ";
+		if ($_POST['TransType']==22 OR $_POST['TransType'] == 21){
+
+			$TitleInfo = ($_POST['TransType'] == 22)?_('Payment'):_('Debit Note');
+			$sql .= "ON supptrans.id = suppallocs.transid_allocto 
+				WHERE suppallocs.transid_allocfrom = '" . $AllocToID . "'";
+		} else {
+			$TitleInfo = _('invoice');
+			$sql .= "ON supptrans.id = suppallocs.transid_allocfrom
+				WHERE suppallocs.transid_allocto = '" . $AllocToID . "'";
+		}
+		$sql .= " ORDER BY transno ";
+
+		$ErrMsg = _('The customer transactions for the selected criteria could not be retrieved because');
+		$TransResult = DB_query($sql, $ErrMsg);
+
+		if (DB_num_rows($TransResult)==0){
+
+			if ($myrow['totamt']>0 AND ($_POST['TransType']==22 OR $_POST['TransType'] == 21)){
+					prnMsg(_('This transaction was a receipt of funds and there can be no allocations of receipts or credits to a receipt. This inquiry is meant to be used to see how a payment which is entered as a negative receipt is settled against credit notes or receipts'),'info');
+			} else {
+				prnMsg(_('There are no allocations made against this transaction'),'info');
+			}
+		} else {
+			$Printer = true;
+			echo '<br />
+			<div id="Report">
+				<table class="selection">';
+
+			echo '<tr>
+					<th colspan="6">
+					<div class="centre">
+						<b>' . _('Allocations made against') . ' ' . $TitleInfo . ' ' . _('number') . ' ' . $_POST['TransNo'] . '<br />' . _('Transaction Total').': '. locale_number_format($myrow['totamt'],$CurrDecimalPlaces) . ' ' . $CurrCode . '</b>
+					</div>
+					</th>
+				</tr>';
+
+			$TableHeader = '<tr>
+								<th>' . _('Type') . '</th>
+								<th>' . _('Number') . '</th>
+								<th>' . _('Reference') . '</th>
+								<th>' . _('Ex Rate') . '</th>
+								<th>' . _('Amount') . '</th>
+								<th>' . _('Alloc') . '</th>
+							</tr>';
+			echo $TableHeader;
+
+			$RowCounter = 1;
+			$k = 0; //row colour counter
+			$AllocsTotal = 0;
+
+			while ($myrow=DB_fetch_array($TransResult)) {
+				if ($k==1){
+					echo '<tr class="EvenTableRows">';
+					$k=0;
+				} else {
+					echo '<tr class="OddTableRows">';
+					$k++;
+				}
+
+				if ($myrow['type']==21){
+					$TransType = _('Debit Note');
+				} elseif ($myrow['type'] == 20){
+					$TransType = _('Purchase Invoice');
+				} else {
+					$TransType = _('Payment');
+				}
+				echo '<td>' . $TransType . '</td>
+					<td>' . $myrow['transno'] . '</td>
+					<td>' . $myrow['suppreference'] . '</td>
+					<td>' . $myrow['rate'] . '</td>
+					<td class="number">' . locale_number_format($myrow['totalamt'],$CurrDecimalPlaces) . '</td>
+					<td class="number">' . locale_number_format($myrow['amt'],$CurrDecimalPlaces) . '</td>
+					</tr>';
+
+				$RowCounter++;
+				If ($RowCounter == 22){
+					$RowCounter=1;
+					echo $TableHeader;
+				}
+				//end of page full new headings if
+				$AllocsTotal +=$myrow['amt'];
+			}
+			//end of while loop
+			echo '<tr>
+					<td colspan="5" class="number">' . _('Total allocated') . '</td>
+					<td class="number">' . locale_number_format($AllocsTotal,$CurrDecimalPlaces) . '</td>
+				</tr>
+				</table>
+			</div>';
+		} // end if there are allocations against the transaction
+	} //got the ID of the transaction to find allocations for
+}
+echo '</div>';
+echo '</form>';
+if (isset($Printer)) {
+	echo '<div class="centre noprint">
+		<button onclick="javascript:window.print()" type="button"><img alt="" src="' . $RootPath . '/css/' . $Theme .
+					'/images/printer.png" /> ' .
+					_('Print This') . '
+		</button>
+		</div>';// "Print This" button.
+}
+include('includes/footer.inc');
+
+?>

Modified: trunk/SupplierInquiry.php
===================================================================
--- trunk/SupplierInquiry.php	2016-01-14 08:19:54 UTC (rev 7448)
+++ trunk/SupplierInquiry.php	2016-01-14 10:08:51 UTC (rev 7449)
@@ -255,7 +255,7 @@
 
 	$BaseTD8 = '<td>' . ConvertSQLDate($myrow['trandate']) . '</td>
 		<td>' . _($myrow['typename']) . '</td>
-		<td class="number">' . $myrow['transno'] . '</td>
+		<td class="number"><a href="' . $RootPath . '/SuppWhereAlloc.php?TransType=' . $myrow['type'] . '&TransNo=' . $myrow['transno'] . '">' . $myrow['transno'] . '</a></td>
 		<td>' . $myrow['suppreference'] . '</td>
 		<td>' . $myrow['transtext'] . '</td>
 		<td class="number">' . locale_number_format($myrow['totalamount'],$SupplierRecord['currdecimalplaces']) . '</td>
@@ -315,7 +315,7 @@
 				echo $BaseTD8 . '
 					<td class="noprint"><a href="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES,'UTF-8') . '/PaymentAllocations.php?SuppID=' .
 						$myrow['type'] . '&amp;InvID=' . $myrow['transno'] . '">' . _('View Payments') . '</a></td>
-					<td class="noprint"><a href="' . $HoldValue . '?HoldType=' . $_POST['TransAfterDate'] . '&amp;HoldTrans=' . $HoldValue . '&amp;HoldStatus=' .
+					<td class="noprint"><a href="' .htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES,'UTF-8') . '?HoldType=' . $_POST['TransAfterDate'] . '&amp;HoldTrans=' . $HoldValue . '&amp;HoldStatus=' .
 						$RootPath . '&amp;FromDate='. $myrow['supplierno'] . '">' . $myrow['suppreference'] . '</a></td></tr>';
 			}
 		}
@@ -341,4 +341,4 @@
 
 echo '</tbody></table>';
 include('includes/footer.inc');
-?>
\ No newline at end of file
+?>

Modified: trunk/includes/MainMenuLinksArray.php
===================================================================
--- trunk/includes/MainMenuLinksArray.php	2016-01-14 08:19:54 UTC (rev 7448)
+++ trunk/includes/MainMenuLinksArray.php	2016-01-14 10:08:51 UTC (rev 7449)
@@ -144,7 +144,8 @@
 $MenuItems['AP']['Transactions']['URL'] = array('/SelectSupplier.php',
 												'/SupplierAllocations.php');
 
-$MenuItems['AP']['Reports']['Caption'] = array(	_('Aged Supplier Report'),
+$MenuItems['AP']['Reports']['Caption'] = array(	_('Where Allocated Inquiry.php'),
+												_('Aged Supplier Report'),
 												_('Payment Run Report'),
 												_('Remittance Advices'),
 												_('Outstanding GRNs Report'),
@@ -152,7 +153,8 @@
 												_('List Daily Transactions'),
 												_('Supplier Transaction Inquiries'));
 
-$MenuItems['AP']['Reports']['URL'] = array(	'/AgedSuppliers.php',
+$MenuItems['AP']['Reports']['URL'] = array( '/SuppWhereAlloc.php',	
+											'/AgedSuppliers.php',
 											'/SuppPaymentRun.php',
 											'/PDFRemittanceAdvice.php',
 											'/OutstandingGRNs.php',

Modified: trunk/sql/mysql/upgrade4.12.3-4.13.sql
===================================================================
--- trunk/sql/mysql/upgrade4.12.3-4.13.sql	2016-01-14 08:19:54 UTC (rev 7448)
+++ trunk/sql/mysql/upgrade4.12.3-4.13.sql	2016-01-14 10:08:51 UTC (rev 7449)
@@ -50,6 +50,7 @@
 ALTER table stockrequest DROP FOREIGN KEY `stockrequest_ibfk_4`;
 INSERT INTO scripts VALUES('CollectiveWorkOrderCost.php',2,'Multiple work orders cost review');
 ALTER table BOM ADD remark varchar(500) NOT NULL DEFAULT '';
+INSERT INTO scripts VALUES ('SuppWhereAlloc.php',3,'Suppliers Where allocated');
 
 
 -- Update version number: