Care2007/modules/weberp/GLAccounts.php

   1 <?php
   2 /* $Revision: 1.16 $ */
   3
   4 $PageSecurity = 10;
   5 include('includes/session.inc');
   6 $title = _('Chart of Accounts Maintenance');
   7
   8 include('includes/header.inc');
   9
  10 if (isset($_POST['SelectedAccount'])){
  11         $SelectedAccount = $_POST['SelectedAccount'];
  12 } elseif (isset($_GET['SelectedAccount'])){
  13         $SelectedAccount = $_GET['SelectedAccount'];
  14 }
  15
  16 if (isset($_POST['submit'])) {
  17
  18         //initialise no input errors assumed initially before we test
  19         $InputError = 0;
  20
  21         /* actions to take once the user has clicked the submit button
  22         ie the page has called itself with some user input */
  23
  24         //first off validate inputs sensible
  25
  26         if (!is_long((integer)$_POST['AccountCode'])) {
  27                 $InputError = 1;
  28                 prnMsg(_('The account code must be an integer'),'warn');
  29         } elseif (strlen($_POST['AccountName']) >50) {
  30                 $InputError = 1;
  31                 prnMsg( _('The account name must be fifty characters or less long'),'warn');
  32         }
  33
  34         if ($SelectedAccount AND $InputError !=1) {
  35
  36                 $sql = "UPDATE chartmaster SET accountname='" . DB_escape_string($_POST['AccountName']) . "',
  37                                                 group_='" . $_POST['Group'] . "'
  38                                         WHERE accountcode = $SelectedAccount";
  39
  40                 $ErrMsg = _('Could not update the account because');
  41                 $result = DB_query($sql,$db,$ErrMsg);
  42                 prnMsg (_('The general ledger account has been updated'),'success');
  43         } elseif ($InputError !=1) {
  44
  45         /*SelectedAccount is null cos no item selected on first time round so must be adding a  record must be submitting new entries */
  46
  47                 $ErrMsg = _('Could not add the new account code');
  48                 $sql = 'INSERT INTO chartmaster (accountcode,
  49                                                 accountname,
  50                                                 group_)
  51                                         VALUES (' . DB_escape_string($_POST['AccountCode']) . ",
  52                                                 '" . DB_escape_string($_POST['AccountName']) . "',
  53                                                 '" . $_POST['Group'] . "')";
  54                 $result = DB_query($sql,$db,$ErrMsg);
  55
  56                 /*Add the new chart details records for existing periods first */
  57
  58                 $ErrMsg = _('Could not add the chart details for the new account');
  59
  60                 $sql = 'INSERT INTO chartdetails (accountcode,
  61                                                         period)
  62                                 SELECT ' . $_POST['AccountCode'] . ',
  63                                         periodno
  64                                 FROM periods';
  65                 $result = DB_query($sql,$db,$ErrMsg);
  66
  67                 prnMsg(_('The new general ledger account has been added'),'success');
  68         }
  69
  70         unset ($_POST['Group']);
  71         unset ($_POST['AccountCode']);
  72         unset ($_POST['AccountName']);
  73         unset($SelectedAccount);
  74
  75 } elseif (isset($_GET['delete'])) {
  76 //the link to delete a selected record was clicked instead of the submit button
  77
  78 // PREVENT DELETES IF DEPENDENT RECORDS IN 'ChartDetails'
  79
  80
  81
  82         $sql= "SELECT COUNT(*) FROM chartdetails WHERE chartdetails.accountcode = $SelectedAccount AND chartdetails.actual <>0";
  83         $result = DB_query($sql,$db);
  84         $myrow = DB_fetch_row($result);
  85         if ($myrow[0]>0) {
  86                 $CancelDelete = 1;
  87                 prnMsg(_('Cannot delete this account because chart details have been created using this account and at least one period has postings to it'),'warn');
  88                 echo '<br>' . _('There are') . ' ' . $myrow[0] . ' ' . _('chart details that require this account code');
  89
  90         } else {
  91 // PREVENT DELETES IF DEPENDENT RECORDS IN 'GLTrans'
  92                 $sql= "SELECT COUNT(*) FROM gltrans WHERE gltrans.account = $SelectedAccount";
  93
  94                 $ErrMsg = _('Could not test for existing transactions because');
  95
  96                 $result = DB_query($sql,$db,$ErrMsg);
  97
  98                 $myrow = DB_fetch_row($result);
  99                 if ($myrow[0]>0) {
 100                         $CancelDelete = 1;
 101                         prnMsg( _('Cannot delete this account because transactions have been created using this account'),'warn');
 102                         echo '<BR>' . _('There are') . ' ' . $myrow[0] . ' ' . _('transactions that require this account code');
 103
 104                 } else {
 105                         //PREVENT DELETES IF Company default accounts set up to this account
 106                         $sql= "SELECT COUNT(*) FROM companies
 107                                 WHERE debtorsact=$SelectedAccount
 108                                 OR pytdiscountact=$SelectedAccount
 109                                 OR creditorsact=$SelectedAccount
 110                                 OR payrollact=$SelectedAccount
 111                                 OR grnact=$SelectedAccount
 112                                 OR exchangediffact=$SelectedAccount
 113                                 OR purchasesexchangediffact=$SelectedAccount
 114                                 OR retainedearnings=$SelectedAccount";
 115
 116
 117                         $ErrMsg = _('Could not test for default company GL codes because');
 118
 119                         $result = DB_query($sql,$db,$ErrMsg);
 120
 121                         $myrow = DB_fetch_row($result);
 122                         if ($myrow[0]>0) {
 123                                 $CancelDelete = 1;
 124                                 prnMsg( _('Cannot delete this account because it is used as one of the company default accounts'),'warn');
 125
 126                         } else  {
 127                                 //PREVENT DELETES IF Company default accounts set up to this account
 128                                 $sql= "SELECT COUNT(*) FROM taxauthorities
 129                                         WHERE taxglcode=$SelectedAccount
 130                                         OR purchtaxglaccount =$SelectedAccount";
 131
 132                                 $ErrMsg = _('Could not test for tax authority GL codes because');
 133                                 $result = DB_query($sql,$db,$ErrMsg);
 134
 135                                 $myrow = DB_fetch_row($result);
 136                                 if ($myrow[0]>0) {
 137                                         $CancelDelete = 1;
 138                                         prnMsg( _('Cannot delete this account because it is used as one of the tax authority accounts'),'warn');
 139                                 } else {
 140 //PREVENT DELETES IF SALES POSTINGS USE THE GL ACCOUNT
 141                                         $sql= "SELECT COUNT(*) FROM salesglpostings
 142                                                 WHERE salesglcode=$SelectedAccount
 143                                                 OR discountglcode=$SelectedAccount";
 144
 145                                         $ErrMsg = _('Could not test for existing sales interface GL codes because');
 146
 147                                         $result = DB_query($sql,$db,$ErrMsg);
 148
 149                                         $myrow = DB_fetch_row($result);
 150                                         if ($myrow[0]>0) {
 151                                                 $CancelDelete = 1;
 152                                                 prnMsg( _('Cannot delete this account because it is used by one of the sales GL posting interface records'),'warn');
 153                                         } else {
 154 //PREVENT DELETES IF COGS POSTINGS USE THE GL ACCOUNT
 155                                                 $sql= "SELECT COUNT(*) FROM cogsglpostings WHERE glcode=$SelectedAccount";
 156
 157                                                 $ErrMsg = _('Could not test for existing cost of sales interface codes because');
 158
 159                                                 $result = DB_query($sql,$db,$ErrMsg);
 160
 161                                                 $myrow = DB_fetch_row($result);
 162                                                 if ($myrow[0]>0) {
 163                                                         $CancelDelete = 1;
 164                                                         prnMsg(_('Cannot delete this account because it is used by one of the cost of sales GL posting interface records'),'warn');
 165
 166                                                 } else {
 167 //PREVENT DELETES IF STOCK POSTINGS USE THE GL ACCOUNT
 168                                                         $sql= "SELECT COUNT(*) FROM stockcategory
 169                                                                 WHERE stockact=$SelectedAccount
 170                                                                 OR adjglact=$SelectedAccount
 171                                                                 OR purchpricevaract=$SelectedAccount
 172                                                                 OR materialuseagevarac=$SelectedAccount
 173                                                                 OR wipact=$SelectedAccount";
 174
 175                                                         $Errmsg = _('Could not test for existing stock GL codes because');
 176
 177                                                         $result = DB_query($sql,$db,$ErrMsg);
 178
 179                                                         $myrow = DB_fetch_row($result);
 180                                                         if ($myrow[0]>0) {
 181                                                                 $CancelDelete = 1;
 182                                                                 prnMsg( _('Cannot delete this account because it is used by one of the stock GL posting interface records'),'warn');
 183                                                         } else {
 184 //PREVENT DELETES IF STOCK POSTINGS USE THE GL ACCOUNT
 185                                                                 $sql= "SELECT COUNT(*) FROM bankaccounts
 186                                                                 WHERE accountcode=$SelectedAccount";
 187                                                                 $ErrMsg = _('Could not test for existing bank account GL codes because');
 188
 189                                                                 $result = DB_query($sql,$db,$ErrMsg);
 190
 191                                                                 $myrow = DB_fetch_row($result);
 192                                                                 if ($myrow[0]>0) {
 193                                                                         $CancelDelete = 1;
 194                                                                         prnMsg( _('Cannot delete this account because it is used by one the defined bank accounts'),'warn');
 195                                                                 } else {
 196
 197                                                                         $sql = 'DELETE FROM chartdetails WHERE accountcode=' . $SelectedAccount;
 198                                                                         $result = DB_query($sql,$db);
 199                                                                         $sql="DELETE FROM chartmaster WHERE accountcode= $SelectedAccount";
 200                                                                         $result = DB_query($sql,$db);
 201                                                                         prnMsg( _('Account') . ' ' . $SelectedAccount . ' ' . _('has been deleted'),'succes');
 202                                                                 }
 203                                                         }
 204                                                 }
 205                                         }
 206                                 }
 207                         }
 208                 }
 209         }
 210 }
 211
 212 if (!isset($_GET['delete'])) {
 213
 214         echo "<FORM METHOD='post' action='" . $_SERVER['PHP_SELF'] .  '?' . SID . "'>";
 215
 216         if ($SelectedAccount) {
 217                 //editing an existing account
 218
 219                 $sql = "SELECT accountcode, accountname, group_ FROM chartmaster WHERE accountcode=$SelectedAccount";
 220
 221                 $result = DB_query($sql, $db);
 222                 $myrow = DB_fetch_array($result);
 223
 224                 $_POST['AccountCode'] = $myrow['accountcode'];
 225                 $_POST['AccountName']   = $myrow['accountname'];
 226                 $_POST['Group'] = $myrow['group_'];
 227
 228                 echo "<INPUT TYPE=HIDDEN NAME='SelectedAccount' VALUE=$SelectedAccount>";
 229                 echo "<INPUT TYPE=HIDDEN NAME='AccountCode' VALUE=" . $_POST['AccountCode'] .">";
 230                 echo "<CENTER><TABLE><TR><TD>" . _('Account Code') . ":</TD><TD>" . $_POST['AccountCode'] . "</TD></TR>";
 231         } else {
 232                 echo "<CENTER><TABLE>";
 233                 echo "<TR><TD>" . _('Account Code') . ":</TD><TD><INPUT TYPE=TEXT NAME='AccountCode' SIZE=11 MAXLENGTH=10></TD></TR>";
 234         }
 235
 236
 237         echo '<TR><TD>' . _('Account Name') . ":</TD><TD><input type='Text' SIZE=51 MAXLENGTH=50 name='AccountName' value='" . $_POST['AccountName'] . "'></TD></TR>";
 238
 239         $sql = 'SELECT groupname FROM accountgroups ORDER BY sequenceintb';
 240         $result = DB_query($sql, $db);
 241
 242         echo '<TR><TD>' . _('Account Group') . ':</TD><TD><SELECT NAME=Group>';
 243
 244         while ($myrow = DB_fetch_array($result)){
 245                 if ($myrow[0]==$_POST['Group']){
 246                         echo "<OPTION SELECTED VALUE='";
 247                 } else {
 248                         echo "<OPTION VALUE='";
 249                 }
 250                 echo $myrow[0] . "'>" . $myrow[0];
 251         }
 252
 253         ?>
 254
 255         </SELECT></TD></TR>
 256
 257         </TABLE></CENTER>
 258
 259         <CENTER><input type="Submit" name="submit" value="<?php echo _('Enter Information'); ?>"></CENTER>
 260
 261         </FORM>
 262
 263 <?php } //end if record deleted no point displaying form to add record
 264
 265
 266 if (!isset($SelectedAccount)) {
 267 /* It could still be the second time the page has been run and a record has been selected for modification - SelectedAccount will exist because it was sent with the new call. If its the first time the page has been displayed with no parameters
 268 then none of the above are true and the list of ChartMaster will be displayed with
 269 links to delete or edit each. These will call the same page again and allow update/input
 270 or deletion of the records*/
 271
 272         $sql = "SELECT accountcode,
 273                         accountname,
 274                         group_,
 275                         CASE WHEN pandl=0 THEN '" . _('Balance Sheet') . "' ELSE '" . _('Profit/Loss') . "' END AS acttype
 276                 FROM chartmaster,
 277                         accountgroups
 278                 WHERE chartmaster.group_=accountgroups.groupname
 279                 ORDER BY chartmaster.accountcode";
 280
 281         $ErrMsg = _('The chart accounts could not be retrieved because');
 282
 283         $result = DB_query($sql,$db,$ErrMsg);
 284
 285         echo '<CENTER><table border=1>';
 286         echo "<tr>
 287                 <td class='tableheader'>" . _('Account Code') . "</td>
 288                 <td class='tableheader'>" . _('Account Name') . "</td>
 289                 <td class='tableheader'>" . _('Account Group') . "</td>
 290                 <td class='tableheader'>" . _('P/L or B/S') . "</td>
 291         </tr>";
 292
 293         $k=0; //row colour counter
 294
 295         while ($myrow = DB_fetch_row($result)) {
 296                 if ($k==1){
 297                         echo "<tr bgcolor='#CCCCCC'>";
 298                         $k=0;
 299                 } else {
 300                         echo "<tr bgcolor='#EEEEEE'>";
 301                         $k=1;
 302                 }
 303
 304
 305         printf("<td>%s</td>
 306                 <td>%s</td>
 307                 <td>%s</td>
 308                 <td>%s</td>
 309                 <td><a href=\"%s&SelectedAccount=%s\">" . _('Edit') . "</td>
 310                 <td><a href=\"%s&SelectedAccount=%s&delete=1\" onclick=\"return confirm('" . _('Are you sure you wish to delete this account? Additional checks will be performed in any event to ensure data integrity is not compromised.') . "');\">" . _('Delete') . "</td>
 311                 </tr>",
 312                 $myrow[0],
 313                 $myrow[1],
 314                 $myrow[2],
 315                 $myrow[3],
 316                 $_SERVER['PHP_SELF'] . '?' . SID,
 317                 $myrow[0],
 318                 $_SERVER['PHP_SELF'] . '?' . SID,
 319                 $myrow[0]);
 320
 321         }
 322         //END WHILE LIST LOOP
 323         echo '</CENTER></TABLE>';
 324 } //END IF SELECTED ACCOUNT
 325
 326 //end of ifs and buts!
 327
 328 echo '<P>';
 329
 330 if (isset($SelectedAccount)) {
 331         echo "<Center><a href='" . $_SERVER['PHP_SELF'] . '?' . SID ."'>" .  _('Show All Accounts') . '</a></CENTER>';
 332 }
 333
 334 echo '<P>';
 335
 336 include('includes/footer.inc');
 337 ?>