1<?php 2/* Defines the general ledger account to be used for cost of sales entries */ 3 4include('includes/session.php'); 5$Title = _('Cost Of Sales GL Postings Set Up'); 6$ViewTopic = 'CreatingNewSystem'; 7$BookMark = 'COGSGLPostings'; 8include('includes/header.php'); 9 10echo '<p class="page_title_text"><img alt="" src="', $RootPath, '/css/', $Theme, 11 '/images/maintenance.png" title="', // Icon image. 12 $Title, '" /> ', // Icon title. 13 $Title, '</p> 14 <br />';// Page title. 15 16 17if (isset($_POST['SelectedCOGSPostingID'])){ 18 $SelectedCOGSPostingID=$_POST['SelectedCOGSPostingID']; 19} elseif (isset($_GET['SelectedCOGSPostingID'])){ 20 $SelectedCOGSPostingID=$_GET['SelectedCOGSPostingID']; 21} 22 23if (isset($_POST['submit'])) { 24 25 /* actions to take once the user has clicked the submit button 26 ie the page has called itself with some user input */ 27 28 if (isset($SelectedCOGSPostingID)) { 29 30 /*SelectedCOGSPostingID 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*/ 31 32 $sql = "UPDATE cogsglpostings SET 33 glcode = '" . $_POST['GLCode'] . "', 34 area = '" . $_POST['Area'] . "', 35 stkcat = '" . $_POST['StkCat'] . "', 36 salestype='" . $_POST['SalesType'] . "' 37 WHERE id ='" .$SelectedCOGSPostingID."'"; 38 39 $msg = _('Cost of sales GL posting code has been updated'); 40 } else { 41 42 /*Selected Sales GL Posting is null cos no item selected on first time round so must be adding a record must be submitting new entries in the new SalesGLPosting form */ 43 44 $sql = "INSERT INTO cogsglpostings ( 45 glcode, 46 area, 47 stkcat, 48 salestype) 49 VALUES ( 50 '" . $_POST['GLCode'] . "', 51 '" . $_POST['Area'] . "', 52 '" . $_POST['StkCat'] . "', 53 '" . $_POST['SalesType'] . "' 54 )"; 55 $msg = _('A new cost of sales posting code has been inserted') . '.'; 56 } 57 //run the SQL from either of the above possibilites 58 59 $result = DB_query($sql); 60 prnMsg ($msg,'info'); 61 unset ($SelectedCOGSPostingID); 62 63} elseif (isset($_GET['delete'])) { 64//the link to delete a selected record was clicked instead of the submit button 65 66 $sql="DELETE FROM cogsglpostings WHERE id='".$SelectedCOGSPostingID."'"; 67 $result = DB_query($sql); 68 prnMsg( _('The cost of sales posting code record has been deleted'),'info'); 69 unset ($SelectedCOGSPostingID); 70} 71 72if (!isset($SelectedCOGSPostingID)) { 73 74 $ShowLivePostingRecords = true; 75 76 $sql = "SELECT cogsglpostings.id, 77 cogsglpostings.area, 78 cogsglpostings.stkcat, 79 cogsglpostings.salestype, 80 chartmaster.accountname 81 FROM cogsglpostings LEFT JOIN chartmaster 82 ON cogsglpostings.glcode = chartmaster.accountcode 83 WHERE chartmaster.accountcode IS NULL 84 ORDER BY cogsglpostings.area, 85 cogsglpostings.stkcat, 86 cogsglpostings.salestype"; 87 88 $result = DB_query($sql); 89 if (DB_num_rows($result)>0){ 90 $ShowLivePostingRecords = false; 91 prnMsg (_('The following cost of sales posting records that do not have valid general ledger code specified - these records must be amended.'),'error'); 92 echo '<table class="selection"> 93 <tr> 94 <th>' . _('Area') . '</th> 95 <th>' . _('Stock Category') . '</th> 96 <th>' . _('Sales Type') . '</th> 97 <th>' . _('COGS Account') . '</th> 98 </tr>'; 99 100 while ($myrow = DB_fetch_array($result)) { 101 102 printf('<tr class="striped_row"> 103 <td>%s</td> 104 <td>%s</td> 105 <td>%s</td> 106 <td>%s</td> 107 <td><a href="%sSelectedCOGSPostingID=%s">' . _('Edit') . '</a></td> 108 <td><a href="%sSelectedCOGSPostingID=%s&delete=yes" onclick="return confirm(\'' . _('Are you sure you wish to delete this COGS GL posting record?') . '\');">' . _('Delete') . '</a></td></tr>', 109 $myrow['area'], 110 $myrow['stkcat'], 111 $myrow['salestype'], 112 $myrow['accountname'], 113 htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '?', 114 $myrow['id'], 115 htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8'). '?', 116 $myrow['id']); 117 }//end while 118 echo '</table>'; 119 } 120 121 $sql = "SELECT cogsglpostings.id, 122 cogsglpostings.area, 123 cogsglpostings.stkcat, 124 cogsglpostings.salestype 125 FROM cogsglpostings 126 ORDER BY cogsglpostings.area, 127 cogsglpostings.stkcat, 128 cogsglpostings.salestype"; 129 130 $result = DB_query($sql); 131 132 if (DB_num_rows($result)==0){ 133 /* there is no default set up so need to check that account 1 is not already used */ 134 /* First Check if we have at least a group_ caled Sales */ 135 $sql = "SELECT groupname FROM accountgroups WHERE groupname = 'Sales'"; 136 $result = DB_query($sql); 137 if (DB_num_rows($result)==0){ 138 /* The required group does not seem to exist so we create it */ 139 $sql = "INSERT INTO accountgroups ( groupname, 140 sectioninaccounts, 141 pandl, 142 sequenceintb, 143 accountgroups 144 ) 145 VALUES ('Sales', 146 '1', 147 '1', 148 '10', 149 ' ')"; 150 151 $result = DB_query($sql); 152 } 153 $sql = "SELECT accountcode FROM chartmaster WHERE accountcode ='1'"; 154 $result = DB_query($sql); 155 if (DB_num_rows($result)==0){ 156 /* account number 1 is not used, so insert a new account */ 157 $sql = "INSERT INTO chartmaster (accountcode, 158 accountname, 159 group_) 160 VALUES ('1', 161 'Default Sales/Discounts', 162 'Sales' 163 )"; 164 $result = DB_query($sql); 165 } 166 167 $sql = "INSERT INTO cogsglpostings ( area, 168 stkcat, 169 salestype, 170 glcode) 171 VALUES ('AN', 172 'ANY', 173 'AN', 174 '1')"; 175 $result = DB_query($sql); 176 } 177 178 if ($ShowLivePostingRecords){ 179 $sql = "SELECT cogsglpostings.id, 180 cogsglpostings.area, 181 cogsglpostings.stkcat, 182 cogsglpostings.salestype, 183 chartmaster.accountname 184 FROM cogsglpostings, 185 chartmaster 186 WHERE cogsglpostings.glcode = chartmaster.accountcode 187 ORDER BY cogsglpostings.area, 188 cogsglpostings.stkcat, 189 cogsglpostings.salestype"; 190 191 $result = DB_query($sql); 192 193 echo '<table class="selection"> 194 <tr> 195 <th>' . _('Area') . '</th> 196 <th>' . _('Stock Category') . '</th> 197 <th>' . _('Sales Type') . '</th> 198 <th>' . _('GL Account') . '</th> 199 </tr>'; 200 201 while ($myrow = DB_fetch_array($result)) { 202 203 printf('<tr class="striped_row"> 204 <td>%s</td> 205 <td>%s</td> 206 <td>%s</td> 207 <td>%s</td> 208 <td><a href="%sSelectedCOGSPostingID=%s">' . _('Edit') . '</a></td> 209 <td><a href="%sSelectedCOGSPostingID=%s&delete=yes" onclick="return confirm(\'' . _('Are you sure you wish to delete this COGS GL posting record?') . '\');">' . _('Delete') . '</a></td> 210 </tr>', 211 $myrow['area'], 212 $myrow['stkcat'], 213 $myrow['salestype'], 214 $myrow['accountname'], 215 htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '?', 216 $myrow['id'], 217 htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '?', 218 $myrow['id']); 219 220 }//END WHILE LIST LOOP 221 echo '</table>'; 222 } 223} 224//end of ifs and buts! 225 226if (isset($SelectedCOGSPostingID)) { 227 echo '<div class="centre"><a href="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') .'">' . _('Show all cost of sales posting records') . '</a></div>'; 228} 229 230echo '<br />'; 231 232echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '">'; 233echo '<div>'; 234echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; 235 236if (isset($SelectedCOGSPostingID)) { 237 //editing an existing cost of sales posting record 238 239 $sql = "SELECT stkcat, 240 glcode, 241 area, 242 salestype 243 FROM cogsglpostings 244 WHERE id='".$SelectedCOGSPostingID."'"; 245 246 $result = DB_query($sql); 247 $myrow = DB_fetch_array($result); 248 249 $_POST['GLCode'] = $myrow['glcode']; 250 $_POST['Area'] = $myrow['area']; 251 $_POST['StkCat'] = $myrow['stkcat']; 252 $_POST['SalesType'] = $myrow['salestype']; 253 254 echo '<input type="hidden" name="SelectedCOGSPostingID" value="' . $SelectedCOGSPostingID . '" />'; 255 256} //end of if $SelectedCOGSPostingID only do the else when a new record is being entered 257 258 259$sql = "SELECT areacode, 260 areadescription 261 FROM areas"; 262$result = DB_query($sql); 263 264echo '<table class="selection"> 265 <tr><td>' . _('Area') . ':</td> 266 <td><select tabindex="1" name="Area"> 267 <option value="AN">' . _('Any Other') . '</option>'; 268 269while ($myrow = DB_fetch_array($result)) { 270 if (isset($_POST['Area']) and $myrow['areacode']==$_POST['Area']) { 271 echo '<option selected="selected" value="'; 272 } else { 273 echo '<option value="'; 274 } 275 echo $myrow['areacode'] . '">' . $myrow['areadescription'] . '</option>'; 276 277} //end while loop 278DB_free_result($result); 279 280$sql = "SELECT categoryid, categorydescription FROM stockcategory"; 281$result = DB_query($sql); 282 283echo '</select></td> 284 </tr> 285 <tr> 286 <td>' . _('Stock Category') . ':</td> 287 <td><select tabindex="2" name="StkCat"> 288 <option value="ANY">' . _('Any Other') . '</option>'; 289 290while ($myrow = DB_fetch_array($result)) { 291 if (isset($_POST['StkCat']) and $myrow['categoryid']==$_POST['StkCat']) { 292 echo '<option selected="selected" value="'; 293 } else { 294 echo '<option value="'; 295 } 296 echo $myrow['categoryid'] . '">' . $myrow['categorydescription'] . '</option>'; 297 298} //end while loop 299 300DB_free_result($result); 301 302$sql = "SELECT typeabbrev, sales_type FROM salestypes"; 303$result = DB_query($sql); 304 305echo '</select></td> 306 </tr> 307 <tr> 308 <td>' . _('Sales Type') . ' / ' . _('Price List') . ':</td> 309 <td><select tabindex="3" name="SalesType"> 310 <option value="AN">' . _('Any Other') . '</option>'; 311 312while ($myrow = DB_fetch_array($result)) { 313 if (isset($_POST['SalesType']) and $myrow['typeabbrev']==$_POST['SalesType']) { 314 echo '<option selected="selected" value="'; 315 } else { 316 echo '<option value="'; 317 } 318 echo $myrow['typeabbrev'] . '">' . $myrow['sales_type'] . '</option>'; 319 320} //end while loop 321 322echo '</select></td> 323 </tr> 324 <tr> 325 <td>' . _('Post to GL account') . ':</td> 326 <td><select tabindex="4" name="GLCode">'; 327 328DB_free_result($result); 329$sql = "SELECT chartmaster.accountcode, 330 chartmaster.accountname 331 FROM chartmaster, 332 accountgroups 333 WHERE chartmaster.group_=accountgroups.groupname 334 AND accountgroups.pandl=1 335 ORDER BY accountgroups.sequenceintb, 336 chartmaster.accountcode, 337 chartmaster.accountname"; 338$result = DB_query($sql); 339 340while ($myrow = DB_fetch_array($result)) { 341 if (isset($_POST['GLCode']) and $myrow['accountcode']==$_POST['GLCode']) { 342 echo '<option selected="selected" value="'; 343 } else { 344 echo '<option value="'; 345 } 346 echo $myrow['accountcode'] . '">' . $myrow['accountcode'] . ' - ' . htmlspecialchars($myrow['accountname'],ENT_QUOTES,'UTF-8',false) . '</option>'; 347 348} //end while loop 349 350DB_free_result($result); 351 352echo '</select></td> 353 </tr> 354 </table> 355 <br /> 356 <div class="centre"> 357 <input tabindex="5" type="submit" name="submit" value="' . _('Enter Information') . '" /> 358 </div> 359 </div> 360 </form>'; 361 362include('includes/footer.php'); 363?> 364