1<?php 2 3 4/*Functions to get the GL codes for customer transactions based on 5$Area, $StockID to determine the stock category and the SalesType (Price List) 6 7Function returns the relavent GL Code to post COGS entries to*/ 8 9Function GetCOGSGLAccount ($Area, $StockID, $SalesType) { 10 11 $ErrMsg = _('Can not retrieve the cost of sales GL code because'); 12 $DbgMsg =_('SQL to get the cost of sales GL Code'); 13 14 /*Get the StockCategory for this item */ 15 16 $SQL = "SELECT categoryid FROM stockmaster WHERE stockid='" . $StockID . "'"; 17 $Result=DB_query($SQL); 18 $myrow = DB_fetch_row($Result); 19 $StockCategory = $myrow[0]; 20 21 /*Gets the GL Code for the COGS for a specific area and stock category. */ 22 23 $SQL = "SELECT glcode FROM cogsglpostings 24 WHERE area = '" . $Area . "' 25 AND stkcat = '" . $StockCategory . "' 26 AND salestype='" . $SalesType . "'"; 27 /*Need to determine if COGS GL codes set up for the stk cat, area and sales type of the item/customer branch and use the most appropriate GL Code. 28 If no match for all fields area, sales type, stock category then the rules for choosing the nearest match 29 are 30 31 - goes for gold a match for salestype stock category and area then - 32 - matching Area, stock category and AN Sales type 33 - see if matching Area, stock category - AN sales type 34 - see if matching Area, saletype and ANY StockCategory 35 - see if matching saletype , StockCategory and AN Area 36 - see if mathcing Area, ANY stock category and AN salestype 37 - see if matching stockcategory, AN area and AN salestype 38 - if still no record is found then the GL Code for the default area, sales type and default stock category is used 39 40 */ 41 42 43 $Result = DB_query($SQL,$ErrMsg,$DbgMsg); 44 45 if (DB_num_rows($Result)==0){ 46 47 DB_free_result($Result); 48 $SQL = "SELECT glcode FROM cogsglpostings 49 WHERE area = '" . $Area . "' 50 AND stkcat = '" . $StockCategory . "' 51 AND salestype = 'AN'"; 52 53 $Result = DB_query($SQL,$ErrMsg,$DbgMsg); 54 55 } 56 57 if (DB_num_rows($Result)==0){ 58 59 DB_free_result($Result); 60 $SQL = "SELECT glcode FROM cogsglpostings 61 WHERE area = '" . $Area . "' 62 AND stkcat = 'ANY' AND salestype = '" . $SalesType . "'"; 63 64 $Result = DB_query($SQL,$ErrMsg,$DbgMsg); 65 } 66 if (DB_num_rows($Result)==0){ 67 68 DB_free_result($Result); 69 $SQL = "SELECT glcode FROM cogsglpostings 70 WHERE area = 'AN' 71 AND stkcat = '" . $StockCategory . "' 72 AND salestype = '" . $SalesType . "'"; 73 74 $Result = DB_query($SQL,$ErrMsg,$DbgMsg); 75 } 76 77 if (DB_num_rows($Result)==0){ 78 79 DB_free_result($Result); 80 $SQL = "SELECT glcode 81 FROM cogsglpostings 82 WHERE area = 'AN' 83 AND salestype='AN' 84 AND stkcat = '" . $StockCategory . "'"; 85 86 $Result = DB_query($SQL,$ErrMsg,$DbgMsg); 87 } 88 89 if (DB_num_rows($Result)==0){ 90 91 DB_free_result($Result); 92 $SQL = "SELECT glcode 93 FROM cogsglpostings 94 WHERE area = '" . $Area . "' 95 AND stkcat = 'ANY' 96 AND salestype='AN'"; 97 $Result = DB_query($SQL,$ErrMsg,$DbgMsg); 98 } 99 100 if (DB_num_rows($Result)==0) { 101 DB_free_result($Result); 102 $SQL = "SELECT glcode 103 FROM cogsglpostings 104 WHERE area = 'AN' 105 AND stkcat = 'ANY' 106 AND salestype = '" . $SalesType . "'"; 107 $Result = DB_query($SQL,$ErrMsg,$DbgMsg); 108 } 109 110 if (DB_num_rows($Result)==0){ 111 112 DB_free_result($Result); 113 $SQL = "SELECT glcode 114 FROM cogsglpostings 115 WHERE area = 'AN' 116 AND stkcat = 'ANY' 117 AND salestype='AN'"; 118 $Result = DB_query($SQL,$ErrMsg,$DbgMsg); 119 } 120 121 if (DB_num_rows($Result)==0){ /*STILL!*/ 122 /*The default if all else fails */ 123 /*Check GL account 1 exists */ 124 prnMsg(_('Could not determine the correct general ledger account to use for posting the cost of this sale. Go to the setup menu and define appropriate COGS (Cost Of Goods Sold) accounts. To enable this invoice to be posted it has been posted to default sales and COGS - account number 1'),'warn'); 125 $SQL = "SELECT accountcode FROM chartmaster WHERE accountcode=1"; 126 $Result = DB_query($SQL); 127 if (DB_num_rows($Result)==0){ /*It doesn't exist so create it */ 128 $Result = DB_query("SELECT groupname FROM accountgroups WHERE groupname='Sales'"); 129 if (DB_num_rows($Result)==0) { 130 $Result = DB_query("INSERT INTO accountgroups (groupname, 131 sectioninaccounts, 132 pandl, 133 sequenceintb) 134 VALUES ('Sales', 135 1, 136 1, 137 5)"); 138 } 139 $SQL = "INSERT INTO chartmaster VALUES (1, 'Default Sales and COGS', 'Sales')"; 140 $Result = DB_query($SQL); 141 } 142 return 1; 143 } 144 145 $myrow = DB_fetch_row($Result); 146 Return $myrow[0]; 147} 148 149Function GetSalesGLAccount ($Area, $StockID, $SalesType) { 150 151/*Gets the Sales GL Code for a specific area, sales type and stock category */ 152 153 $ErrMsg = _('There was a problem retrieving the sales general ledger code because'); 154 $DbgMsg = _('SQL to get the sales GL Codes for sales and discounts'); 155 156 157 /*Get the StockCategory for this item */ 158 $SQL = "SELECT categoryid FROM stockmaster WHERE stockid='" . $StockID . "'"; 159 $Result=DB_query($SQL); 160 $myrow = DB_fetch_row($Result); 161 $StockCategory = $myrow[0]; 162 163 164 /*Need to determine if Sales GL codes set up for the stk cat, area and sales type of the item/customer branch and use the most appropriate GL Code. 165 If no match for all fields area, sales type, stock category then the rules for choosing the nearest match 166 are 167 168 - goes for gold a match for salestype stock category and area then - 169 - matching Area, stock category and AN Sales type 170 - see if matching Area, stock category - AN sales type 171 - see if matching Area, saletype and ANY StockCategory 172 - see if mathcing Area, ANY stock category and AN salestype 173 - see if matching stockcategory, AN area and AN salestype 174 - if still no record is found then the GL Code for the default area, sales type and default stock category is used 175 176 */ 177 178 $SQL = "SELECT salesglcode, 179 discountglcode 180 FROM salesglpostings 181 WHERE area = '" . $Area . "' 182 AND stkcat = '" . $StockCategory . "' 183 AND salestype = '". $SalesType . "'"; 184 185 $Result = DB_query($SQL,$ErrMsg,$DbgMsg); 186 187 if (DB_num_rows($Result)==0){ 188 DB_free_result($Result); 189 $SQL = "SELECT salesglcode, 190 discountglcode 191 FROM salesglpostings 192 WHERE area = '" . $Area . "' 193 AND stkcat = '" . $StockCategory . "' 194 AND salestype = 'AN'"; 195 196 $Result = DB_query($SQL,$ErrMsg,$DbgMsg); 197 } 198 199 if (DB_num_rows($Result)==0){ 200 DB_free_result($Result); 201 $SQL = "SELECT salesglcode, 202 discountglcode 203 FROM salesglpostings 204 WHERE area = '" . $Area . "' 205 AND stkcat = 'ANY' 206 AND salestype = '" . $SalesType . "'"; 207 208 $Result = DB_query($SQL,$ErrMsg,$DbgMsg); 209 } 210 211 if (DB_num_rows($Result)==0){ 212 DB_free_result($Result); 213 $SQL = "SELECT salesglcode, 214 discountglcode 215 FROM salesglpostings 216 WHERE area = 'AN' 217 AND salestype='" . $SalesType . "' 218 AND stkcat = '" . $StockCategory . "'"; 219 220 $Result = DB_query($SQL,$ErrMsg,$DbgMsg); 221 } 222 223 if (DB_num_rows($Result)==0){ 224 DB_free_result($Result); 225 $SQL = "SELECT salesglcode, 226 discountglcode 227 FROM salesglpostings 228 WHERE area = 'AN' 229 AND salestype='AN' 230 AND stkcat = '" . $StockCategory . "'"; 231 232 $Result = DB_query($SQL,$ErrMsg,$DbgMsg); 233 } 234 235 if (DB_num_rows($Result)==0){ 236 DB_free_result($Result); 237 $SQL = "SELECT salesglcode, 238 discountglcode 239 FROM salesglpostings 240 WHERE area = '" . $Area . "' 241 AND stkcat = 'ANY' 242 AND salestype='AN'"; 243 244 $Result = DB_query($SQL,$ErrMsg,$DbgMsg); 245 } 246 247 if (DB_num_rows($Result)==0) { 248 DB_free_result($Result); 249 $SQL = "SELECT salesglcode, 250 discountglcode 251 FROM salesglpostings 252 WHERE area = 'AN' 253 AND stkcat = 'ANY' 254 AND salestype = '" . $SalesType . "'"; 255 $Result = DB_query($SQL,$ErrMsg,$DbgMsg); 256 } 257 258 if (DB_num_rows($Result)==0){ 259 260 DB_free_result($Result); 261 $SQL = "SELECT salesglcode, 262 discountglcode 263 FROM salesglpostings 264 WHERE area = 'AN' 265 AND stkcat = 'ANY' 266 AND salestype='AN'"; 267 268 $Result = DB_query($SQL,$ErrMsg,$DbgMsg); 269 } 270 if (DB_num_rows($Result)==0){ /*STILL!*/ 271 /*The default if all else fails */ 272 prnMsg(_('Could not determine the correct general ledger account to use for posting this sale to. Go to the setup menu and define appropriate Sale GL Posting accounts. To enable this invoice to be posted it has been posted to default sales and COGS - account number 1'),'warn'); 273 /*Check GL account 1 exists */ 274 $SQL = "SELECT accountcode FROM chartmaster WHERE accountcode=1"; 275 $Result = DB_query($SQL); 276 if (DB_num_rows($Result)==0){ /*It doesn't exist so create it */ 277 /*First check the account group sales exists */ 278 $Result = DB_query("SELECT groupname FROM accountgroups WHERE groupname='Sales'"); 279 if (DB_num_rows($Result)==0) { 280 $Result = DB_query("INSERT INTO accountgroups (groupname, 281 sectioninaccounts, 282 pandl, 283 sequenceintb) 284 VALUES ('Sales', 285 1, 286 1, 287 5)"); 288 } 289 $SQL = "INSERT INTO chartmaster VALUES (1, 'Default Sales and COGS', 'Sales')"; 290 $Result = DB_query($SQL); 291 } 292 return array('salesglcode'=>1, 293 'discountglcode'=>1); 294 } 295 $myrow = DB_fetch_array($Result); 296 return $myrow; 297} 298 299?> 300