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?>