Thread: [Web-erp-svn] SF.net SVN: web-erp:[6139] trunk/GLAccountInquiry.php

Accounting & Best Practice Business Administration System

Brought to you by: daintree, exsonqu, rchacon, tim_schofield, turbopt

web-erp-svn

[Web-erp-svn] SF.net SVN: web-erp:[6139] trunk/GLAccountInquiry.php

From: <te...@us...> - 2013-07-24 03:03:50

Revision: 6139
          http://sourceforge.net/p/web-erp/reponame/6139
Author:   tehonu
Date:     2013-07-24 03:03:47 +0000 (Wed, 24 Jul 2013)
Log Message:
-----------
Simplify query

Modified Paths:
--------------
    trunk/GLAccountInquiry.php

Modified: trunk/GLAccountInquiry.php
===================================================================
--- trunk/GLAccountInquiry.php	2013-07-22 17:46:59 UTC (rev 6138)
+++ trunk/GLAccountInquiry.php	2013-07-24 03:03:47 UTC (rev 6139)
@@ -130,47 +130,30 @@
 	$FirstPeriodSelected = min($SelectedPeriod);
 	$LastPeriodSelected = max($SelectedPeriod);
 
-	if ($_POST['tag']==0) {
- 		$sql= "SELECT type,
-					typename,
-					gltrans.typeno,
-					trandate,
-					narrative,
-					amount,
-					periodno,
-					gltrans.tag,
-					tagdescription
-				FROM gltrans INNER JOIN systypes
-				ON systypes.typeid=gltrans.type
-				LEFT JOIN tags
-				ON gltrans.tag = tags.tagref
-				WHERE gltrans.account = '" . $SelectedAccount . "'
-				AND posted=1
-				AND periodno>='" . $FirstPeriodSelected . "'
-				AND periodno<='" . $LastPeriodSelected . "'
-				ORDER BY periodno, gltrans.trandate, counterindex";
+	$sql= "SELECT counterindex,
+				type,
+				typename,
+				gltrans.typeno,
+				trandate,
+				narrative,
+				amount,
+				periodno,
+				gltrans.tag,
+				tagdescription
+			FROM gltrans INNER JOIN systypes
+			ON systypes.typeid=gltrans.type
+			LEFT JOIN tags
+			ON gltrans.tag = tags.tagref
+			WHERE gltrans.account = '" . $SelectedAccount . "'
+			AND posted=1
+			AND periodno>='" . $FirstPeriodSelected . "'
+			AND periodno<='" . $LastPeriodSelected . "'";
 
-	} else {
- 		$sql= "SELECT type,
-					typename,
-					gltrans.typeno,
-					trandate,
-					narrative,
-					amount,
-					periodno,
-					gltrans.tag,
-					tagdescription
-				FROM gltrans INNER JOIN systypes
-				ON systypes.typeid=gltrans.type
-				LEFT JOIN tags
-				ON gltrans.tag = tags.tagref
-				WHERE gltrans.account = '" . $SelectedAccount . "'
-				AND posted=1
-				AND periodno>= '" . $FirstPeriodSelected . "'
-				AND periodno<= '" . $LastPeriodSelected . "'
-				AND tag='" . $_POST['tag'] . "'
-				ORDER BY periodno, gltrans.trandate, counterindex";
+	if ($_POST['tag']!=0) {
+ 		$sql = $sql . " AND tag='" . $_POST['tag'] . "'";
 	}
+			
+	$sql = $sql . " ORDER BY periodno, gltrans.trandate, counterindex";
 
 	$namesql = "SELECT accountname FROM chartmaster WHERE accountcode='" . $SelectedAccount . "'";
 	$nameresult = DB_query($namesql, $db);

[Web-erp-svn] SF.net SVN: web-erp:[6390] trunk/GLAccountInquiry.php

From: <ex...@us...> - 2013-11-05 01:30:52

Revision: 6390
          http://sourceforge.net/p/web-erp/reponame/6390
Author:   exsonqu
Date:     2013-11-05 01:30:50 +0000 (Tue, 05 Nov 2013)
Log Message:
-----------
5/11/2013 Phil: Fixed the warning error in GLAccountInquiry.php add change variable type to array to make min() and max() reasonable. Reported by Jo

Modified Paths:
--------------
    trunk/GLAccountInquiry.php

Modified: trunk/GLAccountInquiry.php
===================================================================
--- trunk/GLAccountInquiry.php	2013-11-04 03:03:55 UTC (rev 6389)
+++ trunk/GLAccountInquiry.php	2013-11-05 01:30:50 UTC (rev 6390)
@@ -16,7 +16,7 @@
 if (isset($_POST['Period'])){
 	$SelectedPeriod = $_POST['Period'];
 } elseif (isset($_GET['Period'])){
-	$SelectedPeriod = $_GET['Period'];
+	$SelectedPeriod = array($_GET['Period']);
 }
 
 /* Get the start and periods, depending on how this script was called*/
@@ -336,4 +336,4 @@
 	echo '<p>' . $IntegrityReport;
 }
 include('includes/footer.inc');
-?>
\ No newline at end of file
+?>

[Web-erp-svn] SF.net SVN: web-erp:[6499] trunk/GLAccountInquiry.php

From: <rc...@us...> - 2013-12-14 13:50:25

Revision: 6499
          http://sourceforge.net/p/web-erp/reponame/6499
Author:   rchacon
Date:     2013-12-14 13:50:20 +0000 (Sat, 14 Dec 2013)
Log Message:
-----------
Completes the construction of the table (missing table-datacells).

Modified Paths:
--------------
    trunk/GLAccountInquiry.php

Modified: trunk/GLAccountInquiry.php
===================================================================
--- trunk/GLAccountInquiry.php	2013-12-14 13:39:51 UTC (rev 6498)
+++ trunk/GLAccountInquiry.php	2013-12-14 13:50:20 UTC (rev 6499)
@@ -197,18 +197,18 @@
 		$ChartDetailRow = DB_fetch_array($ChartDetailsResult);
 
 		$RunningTotal =$ChartDetailRow['bfwd'];
+			echo '<tr>
+					<td colspan="3"><b>' . _('Brought Forward Balance') . '</b></td>';
 		if ($RunningTotal < 0 ){ //its a credit balance b/fwd
-			echo '<tr>
-					<td colspan="3"><b>' . _('Brought Forward Balance') . '</b></td>
+			echo '
 					<td></td>
 					<td class="number"><b>' . locale_number_format(-$RunningTotal,$_SESSION['CompanyRecord']['decimalplaces']) . '</b></td>
-					<td></td>
+					<td colspan="3">&nbsp;</td>
 				</tr>';
 		} else { //its a debit balance b/fwd
-			echo '<tr>
-					<td colspan="3"><b>' . _('Brought Forward Balance') . '</b></td>
+			echo '
 					<td class="number"><b>' . locale_number_format($RunningTotal,$_SESSION['CompanyRecord']['decimalplaces']) . '</b></td>
-					<td colspan="2"></td>
+					<td colspan="4">&nbsp;</td>
 				</tr>';
 		}
 	}
@@ -242,15 +242,15 @@
 					}
 					echo '<td></td>
 						<td class="number"><b>' . locale_number_format(-$PeriodTotal,$_SESSION['CompanyRecord']['decimalplaces']) . '</b></td>
-						<td></td>
-						</tr>';
+						<td colspan="3">&nbsp;</td>
+				</tr>';
 				} else { //its a debit balance b/fwd
 					if ($PandLAccount==True) {
 						$RunningTotal = 0;
 					}
 					echo '<td class="number"><b>' . locale_number_format($PeriodTotal,$_SESSION['CompanyRecord']['decimalplaces']) . '</b></td>
-							<td colspan="2"></td>
-						</tr>';
+							<td colspan="4">&nbsp;</td>
+				</tr>';
 				}
 				$IntegrityReport .= '<br />' . _('Period') . ': ' . $PeriodNo  . _('Account movement per transaction') . ': '  . locale_number_format($PeriodTotal,$_SESSION['CompanyRecord']['decimalplaces']) . ' ' . _('Movement per ChartDetails record') . ': ' . locale_number_format($ChartDetailRow['actual'],$_SESSION['CompanyRecord']['decimalplaces']) . ' ' . _('Period difference') . ': ' . locale_number_format($PeriodTotal -$ChartDetailRow['actual'],3);

[Web-erp-svn] SF.net SVN: web-erp:[7369] trunk/GLAccountInquiry.php

From: <ex...@us...> - 2015-10-28 09:28:33

Revision: 7369
          http://sourceforge.net/p/web-erp/reponame/7369
Author:   exsonqu
Date:     2015-10-28 09:28:30 +0000 (Wed, 28 Oct 2015)
Log Message:
-----------
28/10/15 Exson: Add bank default currency, original amount and check no data to GL account inquiry in GLAccountInquiry.php.

Modified Paths:
--------------
    trunk/GLAccountInquiry.php

Modified: trunk/GLAccountInquiry.php
===================================================================
--- trunk/GLAccountInquiry.php	2015-10-08 01:55:14 UTC (rev 7368)
+++ trunk/GLAccountInquiry.php	2015-10-28 09:28:30 UTC (rev 7369)
@@ -173,6 +173,9 @@
 						<th>' . _('Date') . '</th>
 						<th>' . _('Debit') . '</th>
 						<th>' . _('Credit') . '</th>
+						<th>' . _('Org Currency') . '</th>
+						<th>' . _('Amount in Org Currency') . '</th>
+						<th>' . _('Bank Ref') .'</th>
 						<th>' . _('Narrative') . '</th>
 						<th>' . _('Balance') . '</th>
 						<th>' . _('Tag') . '</th>
@@ -268,6 +271,21 @@
 			echo '<tr class="OddTableRows">';
 			$k++;
 		}
+		if ($myrow['type'] == 12 OR $myrow['type'] == 22 OR $myrow['type'] == 2 OR $myrow['type'] == 1) {
+			$banksql = "SELECT ref,currcode,amount FROM banktrans WHERE type='" .$myrow['type']."' and transno='" . $myrow['typeno'] . "'";
+			$ErrMsg = _('Failed to retrieve bank data');
+			$bankresult = DB_query($banksql,$ErrMsg);
+			if (DB_num_rows($bankresult)>0) {
+				$bankrow = DB_fetch_array($bankresult);
+				$BankRef = $bankrow['ref'];
+				$OrgAmt = $bankrow['amount'];
+				$Currency = $bankrow['currcode'];
+			}
+		} else {
+			$BankRef = '';
+			$OrgAmt = '';
+			$Currency = '';
+		}
 
 		$RunningTotal += $myrow['amount'];
 		$PeriodTotal += $myrow['amount'];
@@ -291,6 +309,9 @@
 				<td>%s</td>
 				<td class="number"><b>%s</b></td>
 				<td>%s</td>
+				<td>%s</td>
+				<td class="number">%s</td>
+				<td>%s</td>
 				</tr>',
 				_($myrow['typename']),
 				$URL_to_TransDetail,
@@ -298,9 +319,13 @@
 				$FormatedTranDate,
 				$DebitAmount,
 				$CreditAmount,
+				$Currency,
+				locale_number_format($OrgAmt,$_SESSION['CompanyRecord']['decimalplaces']),
+				$BankRef,
 				$myrow['narrative'],
 				locale_number_format($RunningTotal,$_SESSION['CompanyRecord']['decimalplaces']),
-				$myrow['tagdescription']);
+				$myrow['tagdescription']
+				);
 
 	}
 
@@ -335,4 +360,4 @@
 	echo '<p>' . $IntegrityReport;
 }
 include('includes/footer.inc');
-?>
\ No newline at end of file
+?>

[Web-erp-svn] SF.net SVN: web-erp:[7439] trunk/GLAccountInquiry.php

From: <ex...@us...> - 2016-01-11 10:35:27

Revision: 7439
          http://sourceforge.net/p/web-erp/reponame/7439
Author:   exsonqu
Date:     2016-01-11 10:35:24 +0000 (Mon, 11 Jan 2016)
Log Message:
-----------
11/01/16 Exson: Fixed the bug of bank account original amount data error. Reported by Tim, Richard and make this data only available for bank account. 

Modified Paths:
--------------
    trunk/GLAccountInquiry.php

Modified: trunk/GLAccountInquiry.php
===================================================================
--- trunk/GLAccountInquiry.php	2016-01-07 10:17:30 UTC (rev 7438)
+++ trunk/GLAccountInquiry.php	2016-01-11 10:35:24 UTC (rev 7439)
@@ -50,13 +50,18 @@
 			<td><select name="Account">';
 
 $sql = "SELECT chartmaster.accountcode, 
+			bankaccounts.accountcode AS bankact,
 			   chartmaster.accountname
-		FROM chartmaster 
+		FROM chartmaster LEFT JOIN bankaccounts
+		ON chartmaster.accountcode=bankaccounts.accountcode
 		INNER JOIN glaccountusers ON glaccountusers.accountcode=chartmaster.accountcode AND glaccountusers.userid='" .  $_SESSION['UserID'] . "' AND glaccountusers.canview=1
 		ORDER BY chartmaster.accountcode";
 $Account = DB_query($sql);
 while ($myrow=DB_fetch_array($Account,$db)){
 	if($myrow['accountcode'] == $SelectedAccount){
+		if (!is_null($myrow['bankact'])) {
+			$BankAccount = true;
+		}
 		echo '<option selected="selected" value="' . $myrow['accountcode'] . '">' . $myrow['accountcode'] . ' ' . htmlspecialchars($myrow['accountname'], ENT_QUOTES, 'UTF-8', false) . '</option>';
 	} else {
 		echo '<option value="' . $myrow['accountcode'] . '">' . $myrow['accountcode'] . ' ' . htmlspecialchars($myrow['accountname'], ENT_QUOTES, 'UTF-8', false) . '</option>';
@@ -166,7 +171,11 @@
 	$SelectedAccountName=$namerow['accountname'];
 	$ErrMsg = _('The transactions for account') . ' ' . $SelectedAccount . ' ' . _('could not be retrieved because') ;
 	$TransResult = DB_query($sql,$ErrMsg);
+	$BankAccountInfo = isset($BankAccount)?'<th>' . _('Org Currency') . '</th>
+						<th>' . _('Amount in Org Currency') . '</th>	
+						<th>' . _('Bank Ref') .'</th>':'';
 
+
 	echo '<br />
 		<table class="selection">
 		<thead>
@@ -178,10 +187,8 @@
 				<th class="number">', _('Number'), '</th>
 				<th class="centre">', ('Date'), '</th>
 				<th class="number">', _('Debit'), '</th>
-				<th class="number">', _('Credit'), '</th>
-				<th class="text">', _('Org Currency'), '</th>
-				<th class="number">', _('Amount in Org Currency'), '</th>
-				<th class="text">', _('Bank Reference'), '</th>
+				<th class="number">', _('Credit'), '</th>' . 
+				$BankAccountInfo .'
 				<th class="text">', _('Narrative'), '</th>
 				<th class="number">', _('Balance'), '</th>
 				<th class="text">', _('Tag'), '</th>
@@ -276,7 +283,8 @@
 			$k++;
 		}
 		if ($myrow['type'] == 12 OR $myrow['type'] == 22 OR $myrow['type'] == 2 OR $myrow['type'] == 1) {
-			$banksql = "SELECT ref,currcode,amount FROM banktrans WHERE type='" .$myrow['type']."' and transno='" . $myrow['typeno'] . "'";
+			$banksql = "SELECT ref,currcode,amount FROM banktrans 
+				WHERE type='" .$myrow['type']."' AND transno='" . $myrow['typeno'] . "' AND bankact='" . $SelectedAccount . "'";
 			$ErrMsg = _('Failed to retrieve bank data');
 			$bankresult = DB_query($banksql,$ErrMsg);
 			if (DB_num_rows($bankresult)>0) {
@@ -304,8 +312,8 @@
 
 		$FormatedTranDate = ConvertSQLDate($myrow['trandate']);
 		$URL_to_TransDetail = $RootPath . '/GLTransInquiry.php?TypeID=' . $myrow['type'] . '&amp;TransNo=' . $myrow['typeno'];
-
-		printf('<td class="text">%s</td>
+		if (isset($BankAccount)) {
+			printf('<td class="text">%s</td>
 				<td class="number"><a href="%s">%s</a></td>
 				<td class="centre">%s</td>
 				<td class="number">%s</td>
@@ -329,7 +337,28 @@
 				$myrow['narrative'],
 				locale_number_format($RunningTotal,$_SESSION['CompanyRecord']['decimalplaces']),
 				$myrow['tagdescription']
-				);
+			);
+		} else {
+			printf('<td class="text">%s</td>
+				<td class="number"><a href="%s">%s</a></td>
+				<td class="centre">%s</td>
+				<td class="number">%s</td>
+				<td class="number">%s</td>
+				<td class="text">%s</td>
+				<td class="number">%s</td>
+				<td class="text">%s</td>
+				</tr>',
+				_($myrow['typename']),
+				$URL_to_TransDetail,
+				$myrow['typeno'],
+				$FormatedTranDate,
+				$DebitAmount,
+				$CreditAmount,
+				$myrow['narrative'],
+				locale_number_format($RunningTotal,$_SESSION['CompanyRecord']['decimalplaces']),
+				$myrow['tagdescription']
+			);
+		}
 
 	}

[Web-erp-svn] SF.net SVN: web-erp:[7441] trunk/GLAccountInquiry.php

From: <ex...@us...> - 2016-01-13 02:38:20

Revision: 7441
          http://sourceforge.net/p/web-erp/reponame/7441
Author:   exsonqu
Date:     2016-01-13 02:38:17 +0000 (Wed, 13 Jan 2016)
Log Message:
-----------
13/01/16 Exson: Fixed the variables non-refresh bugs in GLAccountInquiry.php. Reported by Richard. 

Modified Paths:
--------------
    trunk/GLAccountInquiry.php

Modified: trunk/GLAccountInquiry.php
===================================================================
--- trunk/GLAccountInquiry.php	2016-01-11 10:36:19 UTC (rev 7440)
+++ trunk/GLAccountInquiry.php	2016-01-13 02:38:17 UTC (rev 7441)
@@ -282,6 +282,9 @@
 			echo '<tr class="OddTableRows">';
 			$k++;
 		}
+		$BankRef = '';
+		$OrgAmt = '';
+		$Currency = '';
 		if ($myrow['type'] == 12 OR $myrow['type'] == 22 OR $myrow['type'] == 2 OR $myrow['type'] == 1) {
 			$banksql = "SELECT ref,currcode,amount FROM banktrans 
 				WHERE type='" .$myrow['type']."' AND transno='" . $myrow['typeno'] . "' AND bankact='" . $SelectedAccount . "'";
@@ -293,11 +296,7 @@
 				$OrgAmt = $bankrow['amount'];
 				$Currency = $bankrow['currcode'];
 			}
-		} else {
-			$BankRef = '';
-			$OrgAmt = '';
-			$Currency = '';
-		}
+		} 
 
 		$RunningTotal += $myrow['amount'];
 		$PeriodTotal += $myrow['amount'];

[Web-erp-svn] SF.net SVN: web-erp:[7475] trunk/GLAccountInquiry.php

From: <ex...@us...> - 2016-03-18 07:53:55

Revision: 7475
          http://sourceforge.net/p/web-erp/reponame/7475
Author:   exsonqu
Date:     2016-03-18 07:53:53 +0000 (Fri, 18 Mar 2016)
Log Message:
-----------
18/03/2016 Exson: Fixed the bug that transaction between bank shows wrong original currency and amount in GLAccountInquiry.php.

Modified Paths:
--------------
    trunk/GLAccountInquiry.php

Modified: trunk/GLAccountInquiry.php
===================================================================
--- trunk/GLAccountInquiry.php	2016-03-09 23:02:09 UTC (rev 7474)
+++ trunk/GLAccountInquiry.php	2016-03-18 07:53:53 UTC (rev 7475)
@@ -51,7 +51,8 @@
 
 $sql = "SELECT chartmaster.accountcode, 
 			bankaccounts.accountcode AS bankact,
-			   chartmaster.accountname
+			bankaccounts.currcode,
+			chartmaster.accountname
 		FROM chartmaster LEFT JOIN bankaccounts
 		ON chartmaster.accountcode=bankaccounts.accountcode
 		INNER JOIN glaccountusers ON glaccountusers.accountcode=chartmaster.accountcode AND glaccountusers.userid='" .  $_SESSION['UserID'] . "' AND glaccountusers.canview=1
@@ -61,6 +62,7 @@
 	if($myrow['accountcode'] == $SelectedAccount){
 		if (!is_null($myrow['bankact'])) {
 			$BankAccount = true;
+			$BankCurrency = $myrow['currcode'];
 		}
 		echo '<option selected="selected" value="' . $myrow['accountcode'] . '">' . $myrow['accountcode'] . ' ' . htmlspecialchars($myrow['accountname'], ENT_QUOTES, 'UTF-8', false) . '</option>';
 	} else {
@@ -295,6 +297,10 @@
 				$BankRef = $bankrow['ref'];
 				$OrgAmt = $bankrow['amount'];
 				$Currency = $bankrow['currcode'];
+			} elseif(isset($BankCurrency)){
+				$BankRef = '';
+				$OrgAmt = $myrow['amount'];
+				$Currency = $BankCurrency;
 			}
 		}

[Web-erp-svn] SF.net SVN: web-erp:[7477] trunk/GLAccountInquiry.php

From: <ex...@us...> - 2016-03-18 08:09:24

Revision: 7477
          http://sourceforge.net/p/web-erp/reponame/7477
Author:   exsonqu
Date:     2016-03-18 08:09:22 +0000 (Fri, 18 Mar 2016)
Log Message:
-----------
18/03/2016 Exson: Correct the currency code for transaction between bank account in GLAccountInquiry.php.

Modified Paths:
--------------
    trunk/GLAccountInquiry.php

Modified: trunk/GLAccountInquiry.php
===================================================================
--- trunk/GLAccountInquiry.php	2016-03-18 07:54:29 UTC (rev 7476)
+++ trunk/GLAccountInquiry.php	2016-03-18 08:09:22 UTC (rev 7477)
@@ -62,7 +62,6 @@
 	if($myrow['accountcode'] == $SelectedAccount){
 		if (!is_null($myrow['bankact'])) {
 			$BankAccount = true;
-			$BankCurrency = $myrow['currcode'];
 		}
 		echo '<option selected="selected" value="' . $myrow['accountcode'] . '">' . $myrow['accountcode'] . ' ' . htmlspecialchars($myrow['accountname'], ENT_QUOTES, 'UTF-8', false) . '</option>';
 	} else {
@@ -297,10 +296,10 @@
 				$BankRef = $bankrow['ref'];
 				$OrgAmt = $bankrow['amount'];
 				$Currency = $bankrow['currcode'];
-			} elseif(isset($BankCurrency)){
+			} elseif(isset($BankAccount)){
 				$BankRef = '';
 				$OrgAmt = $myrow['amount'];
-				$Currency = $BankCurrency;
+				$Currency = $_SESSION['CompanyRecord']['currencydefault'];
 			}
 		}