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

[<dai...@us...>]

Revision: 5849
          http://sourceforge.net/p/web-erp/reponame/5849
Author:   daintree
Date:     2013-04-25 04:35:54 +0000 (Thu, 25 Apr 2013)
Log Message:
-----------
Rework GLPostings.inc

Modified Paths:
--------------
    trunk/doc/Change.log
    trunk/includes/GLPostings.inc

Modified: trunk/doc/Change.log
===================================================================
--- trunk/doc/Change.log	2013-04-24 09:35:10 UTC (rev 5848)
+++ trunk/doc/Change.log	2013-04-25 04:35:54 UTC (rev 5849)
@@ -1,5 +1,6 @@
 webERP Change Log
 
+25/4/13 Phil: Rework includes/GLPostings.inc to avoid incorrect b/fwd balances on posting back to a period which did not previously exist.
 19/4/13 Phil: Reported by Bob Thomas - BOMExtendedQty.php was missing purchase orders with status='Authorised' or Printed
 18/4/13 Tim: Credit_Invoice.php missing $identifier in link causing details of credit note to be lost
 16/4/13 Ricard: Audit trail was not being purged if DB Maintenance was turned off and it should be pruned daily.

Modified: trunk/includes/GLPostings.inc
===================================================================
--- trunk/includes/GLPostings.inc	2013-04-24 09:35:10 UTC (rev 5848)
+++ trunk/includes/GLPostings.inc	2013-04-25 04:35:54 UTC (rev 5849)
@@ -19,11 +19,7 @@
 
 Notes:
 
-ChartDetail records should already exist - they are created when a new period is created or when a new GL account is created for all periods in the periods table
-
-NEED to have a function that checks the TB of a period actually balances.
-NEED to have a function that reposts from a given period having first checked the b/fwd balances balance!
-
+ChartDetail records should already exist - they are created (from includes/DateFunctions.in GetPeriod) when a new period is created or when a new GL account is created for all periods in the periods table. However, we may need to create new ones if the user posts back to a period before periods are currently set up - which is not actually possible with the config parameter ProhibitGLPostingsBefore set (However, is a problem when it is not set)
 */
 
 
@@ -33,9 +29,10 @@
 
 if (is_null($FirstPeriodRow[0])){
 	//There are no periods defined
+	$InsertFirstPeriodResult = DB_query("INSERT INTO periods VALUES (-1,'" . Date('Y-m-d',mktime(0,0,0,Date('m')-1,0,Date('Y'))) . "')",$db,_('Could not insert first period'));
 	$InsertFirstPeriodResult = DB_query("INSERT INTO periods VALUES (0,'" . Date('Y-m-d',mktime(0,0,0,Date('m')+1,0,Date('Y'))) . "')",$db,_('Could not insert first period'));
 	$InsertFirstPeriodResult = DB_query("INSERT INTO periods VALUES (1,'" . Date('Y-m-d',mktime(0,0,0,Date('m')+2,0,Date('Y'))) . "')",$db,_('Could not insert second period'));
-	$CreateFrom=0;
+	$CreateFrom=-1;
 }
 
 $LastPeriodResult = DB_query("SELECT MAX(periodno) FROM periods",$db);
@@ -44,16 +41,12 @@
 
 $CreateTo = $LastPeriodRow[0];
 
-
-
 /*First off see if there are in fact any chartdetails */
 
-$ChartDetailsResult = DB_query("SELECT * FROM chartdetails",$db);
 $sql = "SELECT chartmaster.accountcode, MIN(periods.periodno) AS startperiod
 				FROM (chartmaster CROSS JOIN periods)
 				LEFT JOIN chartdetails ON chartmaster.accountcode = chartdetails.accountcode
 				AND periods.periodno = chartdetails.period
-
 				WHERE (periods.periodno BETWEEN '"  . $CreateFrom . "' AND '" . $CreateTo . "')
 				AND chartdetails.actual IS NULL
 				GROUP BY chartmaster.accountcode";
@@ -80,42 +73,38 @@
 /*All the ChartDetail records should have been created now and be available to accept postings */
 
 for ( $CurrPeriod = $CreateFrom; $CurrPeriod <= $CreateTo; $CurrPeriod++ ) {
+	//get all the unposted transactions for the first and successive periods ordered by account
+	$sql = "SELECT counterindex,
+					periodno,
+					account,
+					amount
+				FROM gltrans
+				WHERE posted=0
+				AND periodno='" . $CurrPeriod . "'
+				ORDER BY account";
 
-		$sql = "SELECT counterindex,
-						periodno,
-						account,
-						amount
-					FROM gltrans
-					WHERE posted=0
-					AND periodno='" . $CurrPeriod . "'
-					ORDER BY account";
-
 	$UnpostedTransResult = DB_query($sql, $db);
 
 	$TransStart = DB_Txn_Begin($db);
-	$CurrentAccount=0;
+	$CurrentAccount='0';
 	$TotalAmount=0;
-	while ($UnpostedTrans=DB_fetch_array($UnpostedTransResult)){
-		if($CurrentAccount != $UnpostedTrans['account']) {
-			if($CurrentAccount != 0) {
-				$sql = "UPDATE chartdetails SET actual = actual + " . $TotalAmount . "
-						WHERE accountcode = '" . $CurrentAccount . "'
-						AND period= '" . $CurrPeriod . "'";
-				$PostPrd = DB_query($sql,$db);
-				/*Update the BFwd for all following ChartDetail records */
-				$sql = "UPDATE chartdetails SET bfwd = bfwd + " . $TotalAmount . "
-						WHERE accountcode = '" . $CurrentAccount . "'
-						AND period > '" . $CurrPeriod . "'";
-				$PostBFwds = DB_query($sql,$db);
-			}
+	while ($UnpostedTrans=DB_fetch_array($UnpostedTransResult)) {
+		if($CurrentAccount != $UnpostedTrans['account'] AND $CurrentAccount!='0') {
+			$sql = "UPDATE chartdetails SET actual = actual + " . $TotalAmount . "
+					WHERE accountcode = '" . $CurrentAccount . "'
+					AND period= '" . $CurrPeriod . "'";
+			$PostPrd = DB_query($sql,$db);
+			/*Update the BFwd for all following ChartDetail records */
+			$sql = "UPDATE chartdetails SET bfwd = bfwd + " . $TotalAmount . "
+					WHERE accountcode = '" . $CurrentAccount . "'
+					AND period > '" . $CurrPeriod . "'";
+			$PostBFwds = DB_query($sql,$db);
 			$TotalAmount = 0;
-			$CurrentAccount = $UnpostedTrans['account'];
 		}
+		$CurrentAccount = $UnpostedTrans['account'];
 		$TotalAmount += $UnpostedTrans['amount'];
-	}
-	$sql = "UPDATE gltrans SET posted = 1 WHERE periodno = '" . $CurrPeriod . "' AND posted=0";
-	$Posted = DB_query($sql,$db);
-	// There will be one chartdetail update outstanding if we processed anything
+	} 
+	// There will be one account still to post after the loop
 	if($CurrentAccount != 0) {
 		$sql = "UPDATE chartdetails SET actual = actual + " . $TotalAmount . "
 				WHERE accountcode = '" . $CurrentAccount . "'
@@ -127,49 +116,11 @@
 				AND period > '" . $CurrPeriod . "'";
 		$PostBFwds = DB_query($sql,$db);
 	}
+	
+	$sql = "UPDATE gltrans SET posted = 1 WHERE periodno = '" . $CurrPeriod . "' AND posted=0";
+	$Posted = DB_query($sql,$db);
+	
 	$TransCommit = DB_Txn_Commit($db);
 }
 
-
-if (DB_num_rows($ChartDetailsNotSetUpResult)>0){
-
-	While ($AccountRow = DB_fetch_array($ChartDetailsNotSetUpResult)){
-
-		/*Now run through each of the new chartdetail records created for each account and update them with the B/Fwd and B/Fwd budget no updates would be required where there were previously no chart details set up */
-
-
-		$sql = "SELECT actual,
-				bfwd,
-				budget,
-				bfwdbudget,
-				period
-			FROM chartdetails
-			WHERE period >='" . intval($AccountRow['startperiod']-1) . "'
-			AND accountcode='" . $AccountRow['accountcode'] . "'
-			ORDER BY period";
-		$ChartDetails = DB_query($sql,$db);
-
-		DB_Txn_Begin($db);
-		$myrow = DB_fetch_array($ChartDetails);
-
-		$BFwd = $myrow['bfwd'];
-		$BFwdBudget = $myrow['bfwdbudget'];
-
-		while ($myrow = DB_fetch_array($ChartDetails)){
-			if ($myrow['period'] < $CreateTo) {
-				$BFwd +=$myrow['actual'];
-				$BFwdBudget += $myrow['budget'];
-				$sql = "UPDATE chartdetails SET bfwd ='" . $BFwd . "',
-							bfwdbudget ='" . $BFwdBudget . "'
-					WHERE accountcode = '" . $AccountRow['accountcode'] . "'
-					AND period ='" . intval($myrow['period']+1) . "'";
-
-				$UpdChartDetails = DB_query($sql,$db, '', '', '', false);
-			}
-		}
-
-		DB_Txn_Commit($db);
-	}
-}
-
 ?>
\ No newline at end of file