Care2007/modules/weberp/AccountGroups.php

   1 <?php
   2 /* $Revision: 1.13 $ */
   3
   4 $PageSecurity = 10;
   5
   6 include('includes/session.inc');
   7
   8 $title = _('Account Groups');
   9
  10 include('includes/header.inc');
  11 include('includes/SQL_CommonFunctions.inc');
  12
  13 function CheckForRecursiveGroup ($ParentGroupName, $GroupName, $db) {
  14
  15 /* returns true ie 1 if the group contains the parent group as a child group
  16 ie the parent group results in a recursive group structure otherwise false ie 0 */
  17
  18         $ErrMsg = _('An error occurred in retrieving the account groups of the parent account group during the check for recursion');
  19         $DbgMsg = _('The SQL that was used to retrieve the account groups of the parent account group and that failed in the process was');
  20
  21         do {
  22                 $sql = "SELECT parentgroupname FROM accountgroups WHERE groupname='" . $GroupName ."'";
  23
  24                 $result = DB_query($sql,$db,$ErrMsg,$DbgMsg);
  25                 $myrow = DB_fetch_row($result);
  26                 if ($ParentGroupName == $myrow[0]){
  27                         return true;
  28                 }
  29                 $GroupName = $myrow[0];
  30         } while ($myrow[0]!='');
  31
  32         return false;
  33 } //end of function CheckForRecursiveGroupName
  34
  35
  36 if (isset($_POST['submit'])) {
  37
  38         //initialise no input errors assumed initially before we test
  39
  40         $InputError = 0;
  41
  42         /* actions to take once the user has clicked the submit button
  43         ie the page has called itself with some user input */
  44
  45         //first off validate inputs sensible
  46
  47
  48
  49         if (ContainsIllegalCharacters($_POST['GroupName'])) {
  50                 $InputError = 1;
  51                 prnMsg( _('The account group name cannot contain the character') . " '&' " . _('or the character') ." '",'error');
  52         }
  53         if (strlen($_POST['GroupName'])==0){
  54                 $InputError = 1;
  55                 prnMsg( _('The account group name must be at least one character long'),'error');
  56         }
  57         if ($_POST['ParentGroupName'] !=''){
  58                 if (CheckForRecursiveGroup($_POST['GroupName'],$_POST['ParentGroupName'],$db)) {
  59                         $InputError =1;
  60                         prnMsg(_('The parent account group selected appears to result in a recursive account structure - select an alternative parent account group or make this group a top level account group'),'error');
  61                 } else {
  62                         $sql = "SELECT pandl,
  63                                 sequenceintb,
  64                                 sectioninaccounts
  65                         FROM accountgroups
  66                         WHERE groupname='" . $_POST['ParentGroupName'] . "'";
  67
  68                         $result = DB_query($sql,$db);
  69                         $ParentGroupRow = DB_fetch_array($result);
  70                         $_POST['SequenceInTB'] = $ParentGroupRow['sequenceintb'];
  71                         $_POST['PandL'] = $ParentGroupRow['pandl'];
  72                         $_POST['SectionInAccounts']= $ParentGroupRow['sectioninaccounts'];
  73                 }
  74         } elseif (!is_long((int) $_POST['SectionInAccounts'])) {
  75                 $InputError = 1;
  76                 prnMsg( _('The section in accounts must be an integer'),'error');
  77         } elseif (!is_long((int) $_POST['SequenceInTB'])) {
  78                 $InputError = 1;
  79                 prnMsg( _('The sequence in the trial balance must be an integer'),'error');
  80         } elseif ($_POST['SequenceInTB'] > 10000) {
  81                 $InputError = 1;
  82                 prnMsg( _('The sequence in the TB must be less than') . ' 10,000','error');
  83         }
  84
  85
  86         if ($_POST['SelectedAccountGroup']!='' AND $InputError !=1) {
  87
  88                 /*SelectedAccountGroup 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*/
  89
  90                 $sql = "UPDATE accountgroups
  91                                 SET groupname='" . $_POST['GroupName'] . "',
  92                                         sectioninaccounts=" . $_POST['SectionInAccounts'] . ",
  93                                         pandl=" . $_POST['PandL'] . ",
  94                                         sequenceintb=" . $_POST['SequenceInTB'] . ",
  95                                         parentgroupname='" . $_POST['ParentGroupName'] . "'
  96                                 WHERE groupname = '" . $_POST['SelectedAccountGroup'] . "'";
  97
  98                 $msg = _('Record Updated');
  99         } elseif ($InputError !=1) {
 100
 101         /*Selected group 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 group form */
 102
 103                 $sql = "INSERT INTO accountgroups (
 104                                         groupname,
 105                                         sectioninaccounts,
 106                                         sequenceintb,
 107                                         pandl,
 108                                         parentgroupname)
 109                         VALUES (
 110                                 '" . $_POST['GroupName'] . "',
 111                                 " . $_POST['SectionInAccounts'] . ",
 112                                 " . $_POST['SequenceInTB'] . ",
 113                                 " . $_POST['PandL'] . ",
 114                                 '" . $_POST['ParentGroupName'] . "'
 115                                 )";
 116                 $msg = _('Record inserted');
 117         }
 118
 119         if ($InputError!=1){
 120                 //run the SQL from either of the above possibilites
 121                 $result = DB_query($sql,$db);
 122                 prnMsg($msg,'success');
 123         }
 124         unset ($_POST['SelectedAccountGroup']);
 125         unset ($_POST['GroupName']);
 126         unset ($_POST['SequenceInTB']);
 127 } elseif (isset($_GET['delete'])) {
 128 //the link to delete a selected record was clicked instead of the submit button
 129
 130 // PREVENT DELETES IF DEPENDENT RECORDS IN 'ChartMaster'
 131
 132         $sql= "SELECT COUNT(*) FROM chartmaster WHERE chartmaster.group_='" . $_GET['SelectedAccountGroup'] . "'";
 133         $result = DB_query($sql,$db);
 134         $myrow = DB_fetch_row($result);
 135         if ($myrow[0]>0) {
 136                 prnMsg( _('Cannot delete this account group because general ledger accounts have been created using this group'),'warn');
 137                 echo '<br>' . _('There are') . ' ' . $myrow[0] . ' ' . _('general ledger accounts that refer to this account group') . '</FONT>';
 138
 139         } else {
 140                 $sql = "SELECT COUNT(groupname) FROM accountgroups WHERE parentgroupname = '" . $_GET['SelectedAccountGroup'] . "'";
 141                 $result = DB_query($sql,$db);
 142                 $myrow = DB_fetch_row($result);
 143                 if ($myrow[0]>0) {
 144                         prnMsg( _('Cannot delete this account group because it is a parent account group of other account group(s)'),'warn');
 145                         echo '<br>' . _('There are') . ' ' . $myrow[0] . ' ' . _('account groups that have this group as its/there parent account group') . '</FONT>';
 146                 } else {
 147                         $sql="DELETE FROM accountgroups WHERE groupname='" . $_GET['SelectedAccountGroup'] . "'";
 148                         $result = DB_query($sql,$db);
 149                         prnMsg( $_GET['SelectedAccountGroup'] . ' ' . _('group has been deleted') . '!','success');
 150                 }
 151
 152         } //end if account group used in GL accounts
 153
 154 }
 155
 156  if (!isset($_GET['SelectedAccountGroup']) OR !isset($_POST['SelectedAccountGroup'])) {
 157
 158 /* An account group could be posted when one has been edited and is being updated or GOT when selected for modification
 159  SelectedAccountGroup will exist because it was sent with the page in a GET .
 160  If its the first time the page has been displayed with no parameters
 161 then none of the above are true and the list of account groups will be displayed with
 162 links to delete or edit each. These will call the same page again and allow update/input
 163 or deletion of the records*/
 164
 165         $sql = "SELECT groupname,
 166                         sectionname,
 167                         sequenceintb,
 168                         pandl,
 169                         parentgroupname
 170                 FROM accountgroups
 171                 LEFT JOIN accountsection ON sectionid = sectioninaccounts
 172                 ORDER BY sequenceintb";
 173
 174         $ErrMsg = _('Could not get account groups because');
 175         $result = DB_query($sql,$db,$ErrMsg);
 176
 177         echo "<center><table>
 178                 <tr>
 179                 <td class='tableheader'>" . _('Group Name') . "</td>
 180                 <td class='tableheader'>" . _('Section') . "</td>
 181                 <td class='tableheader'>" . _('Sequence In TB') . "</td>
 182                 <td class='tableheader'>" . _('Profit and Loss') . "</td>
 183                 <td class='tableheader'>" . _('Parent Group') . "</td>
 184                 </tr>";
 185
 186         $k=0; //row colour counter
 187         while ($myrow = DB_fetch_row($result)) {
 188
 189                 if ($k==1){
 190                         echo "<tr bgcolor='#CCCCCC'>";
 191                         $k=0;
 192                 } else {
 193                         echo "<tr bgcolor='#EEEEEE'>";
 194                         $k++;
 195                 }
 196
 197                 switch ($myrow[3]) {
 198                 case -1:
 199                         $PandLText=_('Yes');
 200                         break;
 201                 case 1:
 202                         $PandLText=_('Yes');
 203                         break;
 204                 case 0:
 205                         $PandLText=_('No');
 206                         break;
 207                 } //end of switch statment
 208
 209                 echo '<TD>' . $myrow[0] . '</TD>
 210                         <TD>' . $myrow[1] . '</TD>
 211                         <TD>' . $myrow[2] . '</TD>
 212                         <TD>' . $PandLText . '</TD>
 213                         <TD>' . $myrow[4] . '</TD>';
 214                 echo '<TD><A HREF="' . $_SERVER['PHP_SELF'] . '?' . SID . '&SelectedAccountGroup=' . $myrow[0] . '">' . _('Edit') . '</A></TD>';
 215                 echo '<TD><A HREF="' . $_SERVER['PHP_SELF'] . '?' . SID . '&SelectedAccountGroup=' . $myrow[0] . '&delete=1">' . _('Delete') .'</A></TD>';
 216
 217         } //END WHILE LIST LOOP
 218         echo '</table></CENTER><p>';
 219 } //end of ifs and buts!
 220
 221
 222 if (isset($_POST['SelectedAccountGroup']) OR isset($_GET['SelectedAccountGroup'])) {
 223         echo '<CENTER><A HREF=' . $_SERVER['PHP_SELF'] . '?' . SID .'>' . _('Review Account Groups') . '</a></Center>';
 224 }
 225
 226 echo '<P>';
 227
 228 if (! isset($_GET['delete'])) {
 229
 230         echo "<FORM METHOD='post' action=" . $_SERVER['PHP_SELF'] . '?' . SID . '>';
 231
 232         if (isset($_GET['SelectedAccountGroup'])) {
 233                 //editing an existing account group
 234
 235                 $sql = "SELECT groupname,
 236                                 sectioninaccounts,
 237                                 sequenceintb,
 238                                 pandl,
 239                                 parentgroupname
 240                         FROM accountgroups
 241                         WHERE groupname='" . $_GET['SelectedAccountGroup'] ."'";
 242
 243                 $result = DB_query($sql, $db);
 244                 $myrow = DB_fetch_array($result);
 245
 246                 $_POST['GroupName'] = $myrow['groupname'];
 247                 $_POST['SectionInAccounts']  = $myrow['sectioninaccounts'];
 248                 $_POST['SequenceInTB']  = $myrow['sequenceintb'];
 249                 $_POST['PandL']  = $myrow['pandl'];
 250                 $_POST['ParentGroupName'] = $myrow['parentgroupname'];
 251
 252                 echo "<INPUT TYPE=HIDDEN NAME='SelectedAccountGroup' VALUE='" . $_GET['SelectedAccountGroup'] . "'>";
 253                 echo "<INPUT TYPE=HIDDEN NAME='GroupName' VALUE='" . $_POST['GroupName'] . "'>";
 254
 255                 echo "<CENTER><TABLE>
 256                  <TR><TD>" . _('Account Group') . ':' . '</TD>';
 257
 258                 echo '<TD>' . $_POST['GroupName'] . '</TD></TR>';
 259
 260         } else { //end of if $_POST['SelectedAccountGroup'] only do the else when a new record is being entered
 261
 262                 if (!isset($_POST['SelectedAccountGroup'])){
 263                         $_POST['SelectedAccountGroup']='';
 264                 }
 265                 if (!isset($_POST['GroupName'])){
 266                         $_POST['GroupName']='';
 267                 }
 268                 if (!isset($_POST['SectionInAccounts'])){
 269                         $_POST['SectionInAccounts']='';
 270                 }
 271                 if (!isset($_POST['SequenceInTB'])){
 272                         $_POST['SequenceInTB']='';
 273                 }
 274                 if (!isset($_POST['PandL'])){
 275                         $_POST['PandL']='';
 276                 }
 277
 278                 echo "<INPUT TYPE=HIDDEN NAME='SelectedAccountGroup' VALUE='" . $_POST['SelectedAccountGroup'] . "'>";
 279                 echo "<CENTER><TABLE><TR><TD>" . _('Acount Group Name') . ':' . "</TD><TD><input type='Text' name='GroupName' SIZE=30 MAXLENGTH=30 value='" . $_POST['GroupName'] . "'></TD></TR>";
 280         }
 281         echo '<TR><TD>' . _('Parent Group') . ':' . '</TD>
 282         <TD><SELECT name="ParentGroupName">';
 283
 284         $sql = 'SELECT groupname FROM accountgroups';
 285         $groupresult = DB_query($sql, $db);
 286         if (!isset($_POST['ParentGroupName'])){
 287                 echo "<OPTION SELECTED VALUE=''>" ._('Top Level Group');
 288         } else {
 289                 echo "<OPTION VALUE=''>" ._('Top Level Group');
 290         }
 291
 292         while ( $grouprow = DB_fetch_array($groupresult) ) {
 293
 294                 if ($_POST['ParentGroupName']==$grouprow['groupname']) {
 295                         echo "<OPTION SELECTED VALUE='".$grouprow['groupname']."'>" .$grouprow['groupname'];
 296                 } else {
 297                         echo "<OPTION VALUE='".$grouprow['groupname']."'>" .$grouprow['groupname'];
 298                 }
 299         }
 300         echo '</SELECT>';
 301         echo '</TD></TR>';
 302
 303         echo '<TR><TD>' . _('Section In Accounts') . ':' . '</TD>
 304         <TD><SELECT name=SectionInAccounts>';
 305
 306         $sql = 'SELECT sectionid, sectionname FROM accountsection ORDER BY sectionid';
 307         $secresult = DB_query($sql, $db);
 308         while( $secrow = DB_fetch_array($secresult) ) {
 309                 if ($_POST['SectionInAccounts']==$secrow['sectionid']) {
 310                         echo "<OPTION SELECTED VALUE=".$secrow['sectionid'].">".$secrow['sectionname'].' ('.$secrow['sectionid'].')';
 311                 } else {
 312                         echo "<OPTION VALUE=".$secrow['sectionid'].">".$secrow['sectionname'].' ('.$secrow['sectionid'].')';
 313                 }
 314         }
 315         echo '</SELECT>';
 316         echo '</TD></TR>';
 317
 318         echo '<TR><TD>' . _('Profit and Loss') . ':' . '</TD>
 319         <TD><SELECT name=PandL>';
 320
 321         if ($_POST['PandL']!=0 ) {
 322                 echo '<OPTION SELECTED VALUE=1>' . _('Yes');
 323         } else {
 324                 echo '<OPTION VALUE=1>' . _('Yes');
 325         }
 326         if ($_POST['PandL']==0) {
 327                 echo '<OPTION SELECTED VALUE=0>' . _('No');
 328         } else {
 329                 echo '<OPTION VALUE=0>' . _('No');
 330         }
 331
 332         echo '</SELECT></TD></TR>';
 333
 334         echo '<TR><TD>' . _('Sequence In TB') . ':' . '</TD>';
 335         echo '<TD><INPUT TYPE=Text name=SequenceInTB VALUE=' . (int) $_POST['SequenceInTB'] . '></TD></TR>';
 336
 337         echo '</TABLE>';
 338
 339         echo '<CENTER><input type=Submit name=submit value=' . _('Enter Information') . '>';
 340
 341         echo '</FORM>';
 342
 343 } //end if record deleted no point displaying form to add record
 344
 345 include('includes/footer.inc');
 346 ?>