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