Care2007/modules/weberp/GLBudget.php

   1 <?php
   2
   3 /* $Revision: 1.17 $ */
   4
   5
   6 $PageSecurity = 8;
   7 include ('includes/session.inc');
   8 $title = _('General Ledger Budget Entry');
   9 include('includes/header.inc');
  10
  11 if (isset($_POST['Account'])){
  12         $SelectedAccount = $_POST['Account'];
  13 } elseif (isset($_GET['Account'])){
  14         $SelectedAccount = $_GET['Account'];
  15 }
  16
  17 if (isset($_POST['Period'])){
  18         $SelectedPeriod = $_POST['Period'];
  19 } elseif (isset($_GET['Period'])){
  20         $SelectedPeriod = $_GET['Period'];
  21 }
  22
  23 echo "<FORM METHOD='POST' ACTION=" . $_SERVER['PHP_SELF'] . '?' . SID . '>';
  24
  25 /*Dates in SQL format for the last day of last month*/
  26 $DefaultPeriodDate = Date ('Y-m-d', Mktime(0,0,0,Date('m'),0,Date('Y')));
  27
  28 /*Show a form to allow input of criteria for TB to show */
  29 echo '<CENTER><TABLE>
  30         <TR>
  31          <TD>'._('Account').":</TD>
  32          <TD><SELECT Name='Account'>";
  33          $sql = 'SELECT accountcode, accountname FROM chartmaster ORDER BY accountcode';
  34          $Account = DB_query($sql,$db);
  35          while ($myrow=DB_fetch_array($Account,$db)){
  36             if($myrow['accountcode'] == $SelectedAccount){
  37                 echo '<OPTION SELECTED VALUE=' . $myrow['accountcode'] . '>' . $myrow['accountcode'] . ' ' . $myrow['accountname'];
  38             } else {
  39                 echo '<OPTION VALUE=' . $myrow['accountcode'] . '>' . $myrow['accountcode'] . ' ' . $myrow['accountname'];
  40             }
  41          }
  42          echo '</SELECT></TD></TR>
  43          <TR>
  44          <TD>'._('For Period range').':</TD>
  45          <TD><SELECT Name=Period[] multiple>';
  46          $sql = 'SELECT periodno, lastdate_in_period FROM periods';
  47          $Periods = DB_query($sql,$db);
  48          $id=0;
  49          while ($myrow=DB_fetch_array($Periods,$db)){
  50
  51             if($myrow['periodno'] == $SelectedPeriod[$id]){
  52               echo '<OPTION SELECTED VALUE=' . $myrow['periodno'] . '>' . _(MonthAndYearFromSQLDate($myrow['lastdate_in_period']));
  53             $id++;
  54             } else {
  55               echo '<OPTION VALUE=' . $myrow['periodno'] . '>' . _(MonthAndYearFromSQLDate($myrow['lastdate_in_period']));
  56             }
  57
  58          }
  59          echo "</SELECT></TD>
  60         </TR>
  61 </TABLE><P>
  62 <INPUT TYPE=SUBMIT NAME='Show' VALUE='"._('Show Account Transactions')."'></CENTER>";
  63
  64 /* End of the Form  rest of script is what happens if the show button is hit*/
  65
  66 if (isset($_POST['Show'])){
  67
  68         if (!isset($SelectedPeriod)){
  69                 prnMsg(_('A period or range of periods must be selected from the list box'),'info');
  70                 include('includes/footer.inc');
  71                 exit;
  72         }
  73         /*Is the account a balance sheet or a profit and loss account */
  74         $result = DB_query("SELECT pandl
  75                                 FROM accountgroups
  76                                 INNER JOIN chartmaster ON accountgroups.groupname=chartmaster.group_
  77                                 WHERE chartmaster.accountcode=$SelectedAccount",$db);
  78         $PandLRow = DB_fetch_row($result);
  79         if ($PandLRow[0]==1){
  80                 $PandLAccount = True;
  81         }else{
  82                 $PandLAccount = False; /*its a balance sheet account */
  83         }
  84
  85         $FirstPeriodSelected = min($SelectedPeriod);
  86         $LastPeriodSelected = max($SelectedPeriod);
  87
  88         $sql="SELECT *, chartmaster.accountname from chartdetails
  89                         INNER JOIN chartmaster on chartmaster.accountcode = chartdetails.accountcode
  90                         WHERE chartdetails.accountcode = $SelectedAccount
  91                         AND period>=$FirstPeriodSelected
  92                 AND period<=$LastPeriodSelected
  93                 ORDER BY period";
  94         $ErrMsg = _('The transactions for account') . ' ' . $SelectedAccount . ' ' . _('could not be retrieved because') ;
  95         $TransResult = DB_query($sql,$db,$ErrMsg);
  96 $TransCount = DB_num_rows ($TransResult);
  97                                 echo '<p>row count ' . $TransCount . '</p>';
  98         echo '<table>';
  99
 100         $TableHeader = "<TR>
 101                         <TD class='tableheader'>" . _('Month') . "</TD>
 102                         <TD class='tableheader'>" . _('Actual') . "</TD>
 103                         <TD class='tableheader'>" . _('Budget') . "</TD>
 104                         <TD class='tableheader'>" . _('Variance') . "</TD>
 105                         <TD class='tableheader'>" . _('Period Actual') . "</TD>
 106                         <TD class='tableheader'>" . _('Period Budget') . "</TD>
 107                         <TD class='tableheader'>" . _('Revised Budget') . '</TD>
 108                         </TR>';
 109
 110         echo $TableHeader;
 111
 112         if ($PandLAccount==True) {
 113                 $RunningTotal = 0;
 114         } else {
 115                // added to fix bug with Brought Forward Balance always being zero
 116                                         $sql = "SELECT *
 117                                         FROM chartdetails
 118                                         WHERE chartdetails.accountcode= $SelectedAccount
 119                                         AND chartdetails.period=" . $FirstPeriodSelected;
 120
 121                                 $ErrMsg = _('The chart details for account') . ' ' . $SelectedAccount . ' ' . _('could not be retrieved');
 122                                 $ChartDetailsResult = DB_query($sql,$db,$ErrMsg);
 123                                 $ChartDetailRow = DB_fetch_array($ChartDetailsResult);
 124                                 $ChartDetailCount = DB_num_rows ($ChartDetailsResult);
 125
 126                                 // --------------------
 127
 128                 $RunningTotal =$ChartDetailRow['bfwd'];
 129                 if ($RunningTotal < 0 ){ //its a credit balance b/fwd
 130                         echo "<TR bgcolor='#FDFEEF'>
 131                                 <TD COLSPAN=3><B>" . _('Brought Forward Balance') . '</B><TD>
 132                                 </TD></TD>
 133                                 <TD ALIGN=RIGHT><B>' . number_format(-$RunningTotal,2) . '</B></TD>
 134                                 <TD></TD>
 135                                 </TR>';
 136                 } else { //its a debit balance b/fwd
 137                         echo "<TR bgcolor='#FDFEEF'>
 138                                 <TD COLSPAN=3><B>" . _('Brought Forward Balance') . '</B></TD>
 139                                 <TD ALIGN=RIGHT><B>' . number_format($RunningTotal,2) . '</B></TD>
 140                                 <TD COLSPAN=2></TD>
 141                                 </TR>';
 142                 }
 143
 144                 $RunningBTotal =$ChartDetailRow['bfwdbudget'];
 145                 if ($RunningBTotal < 0 ){ //its a credit balance b/fwd
 146                         echo "<TR bgcolor='#FDFEEF'>
 147                                 <TD COLSPAN=3><B>" . _('Brought Forward Budget Balance') . '</B><TD>
 148                                 </TD></TD>
 149                                 <TD ALIGN=RIGHT><B>' . number_format(-$RunningBTotal,2) . '</B></TD>
 150                                 <TD></TD>
 151                                 </TR>';
 152                 } else { //its a debit balance b/fwd
 153                         echo "<TR bgcolor='#FDFEEF'>
 154                                 <TD COLSPAN=3><B>" . _('Brought Forward Budget Balance') . '</B></TD>
 155                                 <TD ALIGN=RIGHT><B>' . number_format($RunningBTotal,2) . '</B></TD>
 156                                 <TD COLSPAN=2></TD>
 157                                 </TR>';
 158                 }
 159         }
 160         $PeriodTotal = 0;
 161         $PeriodNo = -9999;
 162         $ShowIntegrityReport = False;
 163         $j = 1;
 164         $k=0; //row colour counter
 165
 166         while ($myrow=DB_fetch_array($TransResult)) {
 167
 168                 if ($myrow['periodno']!=$PeriodNo){
 169                         if ($PeriodNo!=-9999){ //ie its not the first time around
 170                                 /*Get the ChartDetails balance b/fwd and the actual movement in the account for the period as recorded in the chart details - need to ensure integrity of transactions to the chart detail movements. Also, for a balance sheet account it is the balance carried forward that is important, not just the transactions*/
 171
 172                                 $sql = "SELECT *,periods.lastdate_in_period,periods.periodno
 173                                         FROM chartdetails,periods
 174                                         INNER JOIN periods on periods.periodno=chartdetails.period
 175                                         WHERE chartdetails.accountcode=" . $PeriodNo;
 176
 177                                 $ErrMsg = _('The chart details for account') . ' ' . $SelectedAccount . ' ' . _('could not be retrieved');
 178                                 $ChartDetailsResult = DB_query($sql,$db,$ErrMsg);
 179                                 $ChartDetailRow = DB_fetch_array($ChartDetailsResult);
 180
 181                                 echo "<TR bgcolor='#FDFEEF'>
 182                                         <TD COLSPAN=3><B>" . _('Total for period') . ' ' . $PeriodNo . '</B></TD>';
 183                                 if ($PeriodTotal < 0 ){ //its a credit balance b/fwd
 184                                         echo '<TD></TD>
 185                                                 <TD ALIGN=RIGHT><B>' . number_format(-$PeriodTotal,2) . '</B></TD>
 186                                                 <TD></TD>
 187                                                 </TR>';
 188                                 } else { //its a debit balance b/fwd
 189                                         echo '<TD ALIGN=RIGHT><B>' . number_format($PeriodTotal,2) . '</B></TD>
 190                                                 <TD COLSPAN=2></TD>
 191                                                 </TR>';
 192                                 }
 193                                 $IntegrityReport .= '<BR>' . _('Period') . ': ' . $PeriodNo  . _('Account movement per transaction') . ': '  . number_format($PeriodTotal,2) . ' ' . _('Movement per ChartDetails record') . ': ' . number_format($ChartDetailRow['actual'],2) . ' ' . _('Period difference') . ': ' . number_format($PeriodTotal -$ChartDetailRow['actual'],3);
 194
 195                                 if (ABS($PeriodTotal -$ChartDetailRow['actual'])>0.01){
 196                                         $ShowIntegrityReport = True;
 197                                 }
 198                         }
 199                         $PeriodNo = $myrow['periodno'];
 200                         $PeriodTotal = 0;
 201                 }
 202
 203                 if ($k==1){
 204                         echo "<tr bgcolor='#CCCCCC'>";
 205                         $k=0;
 206                 } else {
 207                         echo "<tr bgcolor='#EEEEEE'>";
 208                         $k++;
 209                 }
 210
 211                 $RunningTotal += $myrow['actual'];
 212                 $RunningBTotal += $myrow['budget'];
 213                 $PeriodTotal += $myrow['actual'];
 214
 215                 if($myrow['actual']>=0){
 216                         $ActualAmount = number_format($myrow['actual'],2);
 217                         } else {
 218                         $ActualAmount = number_format(-$myrow['actual'],2);
 219                         }
 220
 221                 if($myrow['budget']>=0){
 222                         $BudgetAmount = number_format($myrow['budget'],2);
 223                         } else {
 224                         $BudgetAmount = number_format(-$myrow['budget'],2);
 225
 226                 }
 227 //$Variance = $BudgetAmount-$ActualAmount;
 228 //if ($Variance == "" || $Variance == 0)
 229 //      {
 230                         if ($myrow['actual']<=0)
 231                         {
 232                          $Actual1 = $myrow['actual']*(-1);
 233                          $Budget1 = $myrow['budget']*(-1);
 234                          $Variance = $Budget1 - $Actual1;
 235                         }
 236                         else
 237                         {
 238                          $Variance = $Budget1 - $Actual1;
 239                         }
 240             $Variance1 = number_format($Variance,2);
 241
 242
 243                                         $sql = "SELECT lastdate_in_period FROM periods
 244                                         WHERE periods.periodno=" . $myrow['period'];
 245
 246                                 $ErrMsg = _('The Period dates') . ' ' . $myrow['period'] . ' ' . _('could not be retrieved');
 247                                 $PeriodResult = DB_query($sql,$db,$ErrMsg);
 248                                 $PeriodRow = DB_fetch_array($PeriodResult);
 249
 250                                 $PeriodText = MonthAndYearFromSQLDate($PeriodRow['lastdate_in_period']);
 251
 252
 253                 printf('<td>%s</td>
 254                         <td>%s</td>
 255                         <td><input name="budget" type="text" size="15" value="%s"></td>
 256                         <td ALIGN=RIGHT>%s</td>
 257                         <td ALIGN=RIGHT>%s</td>
 258                         <td ALIGN=RIGHT>%s</td>
 259                         <td><input name="revised_budget" type="text" size="15" value="%s"></td>
 260                         </tr>',
 261                         $PeriodText,
 262                         $ActualAmount,
 263                         $BudgetAmount,
 264                         $Variance1,
 265                         $RunningTotal,
 266                         $RunningBTotal,
 267                         $myrow['budget']);
 268
 269                 $j++;
 270
 271                 If ($j == 18){
 272                         echo $TableHeader;
 273                         $j=1;
 274                 }
 275
 276         }
 277
 278         echo "<TR bgcolor='#FDFEEF'><TD COLSPAN=3><B>";
 279         if ($PandLAccount==True){
 280                 echo _('Total Period Movement');
 281         } else { /*its a balance sheet account*/
 282                 echo _('Balance C/Fwd');
 283         }
 284         echo '</B></TD>';
 285
 286         if ($RunningTotal >0){
 287                 echo '<TD ALIGN=RIGHT><B>' . number_format(($RunningTotal),2) . '</B></TD><TD COLSPAN=2></TD></TR>';
 288         }else {
 289                 echo '<TD></TD><TD ALIGN=RIGHT><B>' . number_format((-$RunningTotal),2) . '</B></TD><TD COLSPAN=2></TD></TR>';
 290         }
 291                 echo '<tr><td colspan=6><input NAME="BudgetUpdate" type="submit" value="Update Budget">';
 292                 printf ("%s",$ChartDetailCount);
 293                 echo '</td></tr>';
 294
 295
 296         echo '</table>';
 297         echo $ChartDetailCount;
 298 } /* end of if Show button hit */
 299
 300 if (isset($_POST['BudgetUpdate'])){
 301
 302         prnMsg(_('SUCCESS'),'info');
 303         $sql = "UPDATE chartdetails
 304                                 SET budget='" . $_POST['budget'] . "'
 305                                 WHERE period='" . $ChartDetailRow['period'] . "'
 306                                 AND accountcode = " . $ChartDetailRow['accountcode'];
 307 // $result = mysql_query($sql, $connection);
 308 $result = DB_query($sql,$db,$ErrMsg,$DbgMsg);
 309 //
 310
 311 // stick an echo in here:
 312 echo $sql;
 313
 314         exit;
 315
 316         }
 317 if (isset($_POST['submit'])) {
 318
 319         //initialise no input errors assumed initially before we test
 320
 321         $InputError = 0;
 322
 323         /* actions to take once the user has clicked the submit button
 324         ie the page has called itself with some user input */
 325
 326         //first off validate inputs sensible
 327
 328         if (strpos($_POST['SectionName'],'&')>0 OR strpos($_POST['SectionName'],"'")>0) {
 329                 $InputError = 1;
 330                 prnMsg( _('The account section name cannot contain the character') . " '&' " . _('or the character') ." '",'error');
 331         } elseif (isset($_POST['SectionID']) && (!is_long((int) $_POST['SectionID']))) {
 332                 $InputError = 1;
 333                 prnMsg( _('The section number must be an integer'),'error');
 334         }
 335
 336         if ($_POST['SelectedSectionID']!='' AND $InputError !=1) {
 337
 338                 /*SelectedSectionID could also exist if submit had not been clicked this code would not run in this case cos submit is false of course  see the delete code below*/
 339
 340                 $sql = "UPDATE accountsection
 341                                 SET sectionname='" . $_POST['SectionName'] . "'
 342                                 WHERE sectionid = " . $_POST['SelectedSectionID'];
 343
 344                 $msg = _('Record Updated');
 345         } elseif ($InputError !=1) {
 346
 347         /*SelectedSectionID is null cos no item selected on first time round so must be adding a record must be submitting new entries in the new account section form */
 348
 349                 $sql = "INSERT INTO accountsection (
 350                                         sectionid,
 351                                         sectionname )
 352                         VALUES (
 353                                 " . $_POST['SectionID'] . ",
 354                                 '" . $_POST['SectionName'] ."'
 355                                 )";
 356                 $msg = _('Record inserted');
 357         }
 358
 359         if ($InputError!=1){
 360                 //run the SQL from either of the above possibilites
 361                 $result = DB_query($sql,$db);
 362                 prnMsg($msg,'success');
 363         }
 364         unset ($_POST['SelectedSectionID']);
 365         unset ($_POST['SectionID']);
 366         unset ($_POST['SectionName']);
 367
 368 }
 369
 370
 371 echo "</FORM>";
 372 include('includes/footer.inc');
 373 ?>