1<?php 2 3include ('includes/session.php'); 4$Title = _('Create Chart Details Records'); 5include ('includes/header.php'); 6 7/*Script to insert ChartDetails records where one should already exist 8only necessary where manual entry of chartdetails has stuffed the system */ 9 10$FirstPeriodResult = DB_query("SELECT MIN(periodno) FROM periods"); 11$FirstPeriodRow = DB_fetch_row($FirstPeriodResult); 12 13$LastPeriodResult = DB_query("SELECT MAX(periodno) FROM periods"); 14$LastPeriodRow = DB_fetch_row($LastPeriodResult); 15 16$CreateFrom = $FirstPeriodRow[0]; 17$CreateTo = $LastPeriodRow[0];; 18 19 20/*First off see if there are any chartdetails missing create recordset of */ 21 22$sql = "SELECT chartmaster.accountcode, MIN(periods.periodno) AS startperiod 23 FROM chartmaster CROSS JOIN periods 24 LEFT JOIN chartdetails ON chartmaster.accountcode = chartdetails.accountcode 25 AND periods.periodno = chartdetails.period 26 WHERE (periods.periodno BETWEEN '" . $CreateFrom . "' AND '" . $CreateTo . "') 27 AND chartdetails.accountcode IS NULL 28 GROUP BY chartmaster.accountcode"; 29 30$ChartDetailsNotSetUpResult = DB_query($sql,_('Could not test to see that all chart detail records properly initiated')); 31 32if(DB_num_rows($ChartDetailsNotSetUpResult)>0){ 33 34 /*Now insert the chartdetails records that do not already exist */ 35 $sql = "INSERT INTO chartdetails (accountcode, period) 36 SELECT chartmaster.accountcode, periods.periodno 37 FROM chartmaster CROSS JOIN periods 38 LEFT JOIN chartdetails ON chartmaster.accountcode = chartdetails.accountcode 39 AND periods.periodno = chartdetails.period 40 WHERE (periods.periodno BETWEEN '" . $CreateFrom . "' AND '" . $CreateTo . "') 41 AND chartdetails.accountcode IS NULL"; 42 43 $ErrMsg = _('Inserting new chart details records required failed because'); 44 $InsChartDetailsRecords = DB_query($sql,$ErrMsg); 45 46 47 While ($AccountRow = DB_fetch_array($ChartDetailsNotSetUpResult)){ 48 49 /*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 ie FirstPeriodPostedTo > 0 */ 50 51 $sql = "SELECT actual, 52 bfwd, 53 budget, 54 bfwdbudget, 55 period 56 FROM chartdetails 57 WHERE period >='" . ($AccountRow['period']-1) . "' 58 AND accountcode='" . $AccountRow['accountcode'] . "' 59 ORDER BY period"; 60 $ChartDetails = DB_query($sql); 61 62 DB_Txn_Begin(); 63 $BFwd = ''; 64 $BFwdBudget =''; 65 $CFwd=0; 66 $CFwdBudget=0; 67 while ($myrow = DB_fetch_array($ChartDetails)){ 68 if ($BFwd =''){ 69 $BFwd = $myrow['bfwd']; 70 $BFwdBudget = $myrow['bfwdbudget']; 71 } else { 72 $BFwd +=$myrow['actual']; 73 $BFwdBudget += $myrow['budget']; 74 $sql = "UPDATE chartdetails SET bfwd ='" . $BFwd . "', 75 bfwdbudget ='" . $BFwdBudget . "' 76 WHERE accountcode = '" . $AccountRow['accountcode'] . "' 77 AND period ='" . ($myrow['period']+1) . "'"; 78 79 $UpdChartDetails = DB_query($sql, '', '', '', false); 80 } 81 } 82 83 DB_Txn_Commit(); 84 85 DB_free_result($ChartDetailsCFwd); 86 } 87 88 prnMsg(_('Chart Details Created successfully'),'success'); 89 90} else { 91 92 prnMsg(_('No additional chart details were required to be added'),'success'); 93} 94 95include('includes/footer.php'); 96?>