- modules/fotolab updated imagej to current version & some cod fixes to make it work
[care2x.git] / Care2007 / modules / weberp / SelectCompletedOrder.php
blob91648b499c6f6aae7935c0e8d426f67267c53a24
1 <?php
3 /* $Revision: 1.11 $ */
5 $PageSecurity = 1;
7 include('includes/session.inc');
9 $title = _('Search All Sales Orders');
11 include('includes/header.inc');
13 echo "<FORM ACTION='" . $_SERVER['PHP_SELF'] . '?' . SID ."' METHOD=POST>";
15 if (isset($_GET['SelectedStockItem'])){
16 $SelectedStockItem = $_GET['SelectedStockItem'];
17 } elseif (isset($_POST['SelectedStockItem'])){
18 $SelectedStockItem = $_POST['SelectedStockItem'];
20 if (isset($_GET['OrderNumber'])){
21 $OrderNumber = $_GET['OrderNumber'];
22 } elseif (isset($_POST['OrderNumber'])){
23 $OrderNumber = $_POST['OrderNumber'];
25 if (isset($_GET['CustomerRef'])){
26 $CustomerRef = $_GET['CustomerRef'];
27 } elseif (isset($_POST['CustomerRef'])){
28 $CustomerRef = $_POST['CustomerRef'];
30 if (isset($_GET['SelectedCustomer'])){
31 $SelectedCustomer = $_GET['SelectedCustomer'];
32 } elseif (isset($_POST['SelectedCustomer'])){
33 $SelectedCustomer = $_POST['SelectedCustomer'];
36 if ($SelectedStockItem==''){
37 unset($SelectedStockItem);
39 if ($OrderNumber==''){
40 unset($OrderNumber);
42 if ($CustomerRef==''){
43 unset($CustomerRef);
45 if ($SelectedCustomer==''){
46 unset($SelectedCustomer);
48 If ($_POST['ResetPart']){
49 unset($SelectedStockItem);
52 If (isset($OrderNumber)) {
53 echo _('Order Number') . ' - ' . $OrderNumber;
54 } elseif (isset($CustomerRef)) {
55 echo _('Customer Ref') . ' - ' . $CustomerRef;
56 } else {
57 If (isset($SelectedCustomer)) {
58 echo _('For customer') . ': ' . $SelectedCustomer .' ' . _('and') . ' ';
59 echo "<input type=hidden name='SelectedCustomer' value='$SelectedCustomer'>";
62 If (isset($SelectedStockItem)) {
64 echo _('for the part') . ': ' . $SelectedStockItem . ' ' . _('and') . ' ' ."<input type=hidden name='SelectedStockItem' value='$SelectedStockItem'>";
70 if ($_POST['SearchParts']!=''){
72 If ($_POST['Keywords']!='' AND $_POST['StockCode']!='') {
73 echo _('Stock description keywords have been used in preference to the Stock code extract entered');
75 If ($_POST['Keywords']!='') {
76 //insert wildcard characters in spaces
78 $i=0;
79 $SearchString = '%';
80 while (strpos($_POST['Keywords'], ' ', $i)) {
81 $wrdlen=strpos($_POST['Keywords'],' ',$i) - $i;
82 $SearchString=$SearchString . substr($_POST['Keywords'],$i,$wrdlen) . '%';
83 $i=strpos($_POST['Keywords'],' ',$i) +1;
85 $SearchString = $SearchString. substr($_POST['Keywords'],$i).'%';
87 $SQL = "SELECT stockmaster.stockid,
88 stockmaster.description,
89 SUM(locstock.quantity) AS qoh,
90 stockmaster.units,
91 SUM(salesorderdetails.quantity - salesorderdetails.qtyinvoiced) AS qdem
92 FROM stockmaster,
93 locstock,
94 salesorderdetails
95 WHERE stockmaster.stockid=locstock.stockid
96 AND stockmaster.stockid = salesorderdetails.stkcode
97 AND salesorderdetails.completed =0
98 AND stockmaster.description " . LIKE . "'$SearchString'
99 AND stockmaster.categoryid='" . $_POST['StockCat'] . "'
100 GROUP BY stockmaster.stockid,
101 stockmaster.description,
102 stockmaster.units
103 ORDER BY stockmaster.stockid";
105 } elseif ($_POST['StockCode']!=''){
107 $SQL = "SELECT stockmaster.stockid,
108 stockmaster.description,
109 SUM(locstock.quantity) AS qoh,
110 SUM(salesorderdetails.quantity - salesorderdetails.qtyinvoiced) AS qdem,
111 stockmaster.units
112 FROM stockmaster,
113 locstock,
114 salesorderdetails
115 WHERE stockmaster.stockid=locstock.stockid
116 AND stockmaster.stockid = salesorderdetails.stkcode
117 AND salesorderdetails.completed =0
118 AND stockmaster.stockid " . LIKE . " '%" . $_POST['StockCode'] . "%'
119 AND stockmaster.categoryid='" . $_POST['StockCat'] . "'
120 GROUP BY stockmaster.stockid,
121 stockmaster.description,
122 stockmaster.units
123 ORDER BY stockmaster.stockid";
125 } elseif ($_POST['StockCode']=='' AND $_POST['Keywords']=='' AND $_POST['StockCat']!='') {
127 $SQL = "SELECT stockmaster.stockid,
128 stockmaster.description,
129 SUM(locstock.quantity) AS qoh,
130 SUM(salesorderdetails.quantity - salesorderdetails.qtyinvoiced) AS qdem,
131 stockmaster.units
132 FROM stockmaster,
133 locstock,
134 salesorderdetails
135 WHERE stockmaster.stockid=locstock.stockid
136 AND stockmaster.stockid = salesorderdetails.stkcode
137 AND salesorderdetails.completed =0
138 AND stockmaster.categoryid='" . $_POST['StockCat'] . "'
139 GROUP BY stockmaster.stockid,
140 stockmaster.description,
141 stockmaster.units
142 ORDER BY stockmaster.stockid";
146 if (strlen($SQL)<2){
147 prnMsg(_('No selections have been made to search for parts') . ' - ' . _('choose a stock category or enter some characters of the code or description then try again'),'warn');
148 } else {
150 $ErrMsg = _('No stock items were returned by the SQL because');
151 $DbgMsg = _('The SQL used to retrieve the searched parts was');
152 $StockItemsResult = DB_query($SQL,$db,$ErrMsg,$DbgMsg);
154 if (DB_num_rows($StockItemsResult)==1){
155 $myrow = DB_fetch_row($StockItemsResult);
156 $SelectedStockItem = $myrow[0];
157 $_POST['SearchOrders']='True';
158 unset($StockItemsResult);
159 echo '<BR>' . _('For the part') . ': ' . $SelectedStockItem . ' ' . _('and') . " <input type=hidden name='SelectedStockItem' value='$SelectedStockItem'>";
162 } else if (isset($_POST['SearchOrders']) AND Is_Date($_POST['OrdersAfterDate'])==1) {
164 //figure out the SQL required from the inputs available
165 if (isset($OrderNumber)) {
166 $SQL = "SELECT salesorders.orderno,
167 debtorsmaster.name,
168 custbranch.brname,
169 salesorders.customerref,
170 salesorders.orddate,
171 salesorders.deliverydate,
172 salesorders.deliverto, SUM(salesorderdetails.unitprice*salesorderdetails.quantity*(1-salesorderdetails.discountpercent)) AS ordervalue
173 FROM salesorders,
174 salesorderdetails,
175 debtorsmaster,
176 custbranch
177 WHERE salesorders.orderno = salesorderdetails.orderno
178 AND salesorders.branchcode = custbranch.branchcode
179 AND salesorders.debtorno = debtorsmaster.debtorno
180 AND debtorsmaster.debtorno = custbranch.debtorno
181 AND salesorders.orderno=". $OrderNumber ."
182 AND salesorders.quotation=0
183 GROUP BY salesorders.orderno,
184 debtorsmaster.name,
185 custbranch.brname,
186 salesorders.customerref,
187 salesorders.orddate,
188 salesorders.deliverydate,
189 salesorders.deliverto
190 ORDER BY salesorders.orderno";
191 } elseif (isset($CustomerRef)) {
192 $SQL = "SELECT salesorders.orderno,
193 debtorsmaster.name,
194 custbranch.brname,
195 salesorders.customerref,
196 salesorders.orddate,
197 salesorders.deliverydate,
198 salesorders.deliverto, SUM(salesorderdetails.unitprice*salesorderdetails.quantity*(1-salesorderdetails.discountpercent)) AS ordervalue
199 FROM salesorders,
200 salesorderdetails,
201 debtorsmaster,
202 custbranch
203 WHERE salesorders.orderno = salesorderdetails.orderno
204 AND salesorders.branchcode = custbranch.branchcode
205 AND salesorders.debtorno = debtorsmaster.debtorno
206 AND debtorsmaster.debtorno = custbranch.debtorno
207 AND salesorders.customerref like '%". $CustomerRef."%'
208 AND salesorders.quotation=0
209 GROUP BY salesorders.orderno,
210 debtorsmaster.name,
211 custbranch.brname,
212 salesorders.customerref,
213 salesorders.orddate,
214 salesorders.deliverydate,
215 salesorders.deliverto
216 ORDER BY salesorders.orderno";
218 } else {
219 $DateAfterCriteria = FormatDateforSQL($_POST['OrdersAfterDate']);
221 if (isset($SelectedCustomer) AND !isset($OrderNumber) AND !isset($CustomerRef)) {
223 if (isset($SelectedStockItem)) {
224 $SQL = "SELECT salesorders.orderno,
225 debtorsmaster.name,
226 custbranch.brname,
227 salesorders.customerref,
228 salesorders.orddate,
229 salesorders.deliverydate,
230 salesorders.deliverto, SUM(salesorderdetails.unitprice*salesorderdetails.quantity*(1-salesorderdetails.discountpercent)) AS ordervalue
231 FROM salesorders,
232 salesorderdetails,
233 debtorsmaster,
234 custbranch
235 WHERE salesorders.orderno = salesorderdetails.orderno
236 AND salesorders.branchcode = custbranch.branchcode
237 AND salesorders.debtorno = debtorsmaster.debtorno
238 AND debtorsmaster.debtorno = custbranch.debtorno
239 AND salesorderdetails.stkcode='". $SelectedStockItem ."'
240 AND salesorders.debtorno='" . $SelectedCustomer ."'
241 AND salesorders.orddate >= '" . $DateAfterCriteria ."'
242 AND salesorders.quotation=0
243 GROUP BY salesorders.orderno,
244 debtorsmaster.name,
245 custbranch.brname,
246 salesorders.customerref,
247 salesorders.orddate,
248 salesorders.deliverydate,
249 salesorders.deliverto
250 ORDER BY salesorders.orderno";
251 } else {
252 $SQL = "SELECT salesorders.orderno,
253 debtorsmaster.name,
254 custbranch.brname,
255 salesorders.customerref,
256 salesorders.orddate,
257 salesorders.deliverto,
258 salesorders.deliverydate, SUM(salesorderdetails.unitprice*salesorderdetails.quantity*(1-salesorderdetails.discountpercent)) AS ordervalue
259 FROM salesorders,
260 salesorderdetails,
261 debtorsmaster,
262 custbranch
263 WHERE salesorders.orderno = salesorderdetails.orderno
264 AND salesorders.debtorno = debtorsmaster.debtorno
265 AND salesorders.branchcode = custbranch.branchcode
266 AND debtorsmaster.debtorno = custbranch.debtorno
267 AND salesorders.debtorno='" . $SelectedCustomer . "'
268 AND salesorders.orddate >= '" . $DateAfterCriteria . "'
269 AND salesorders.quotation=0
270 GROUP BY salesorders.orderno,
271 debtorsmaster.name,
272 custbranch.brname,
273 salesorders.customerref,
274 salesorders.orddate,
275 salesorders.deliverydate,
276 salesorders.deliverto
277 ORDER BY salesorders.orderno";
279 } else { //no customer selected
280 if (isset($SelectedStockItem)) {
281 $SQL = "SELECT salesorders.orderno,
282 debtorsmaster.name,
283 custbranch.brname,
284 salesorders.customerref,
285 salesorders.orddate,
286 salesorders.deliverto,
287 salesorders.deliverydate, SUM(salesorderdetails.unitprice*salesorderdetails.quantity*(1-salesorderdetails.discountpercent)) AS ordervalue
288 FROM salesorders,
289 salesorderdetails,
290 debtorsmaster,
291 custbranch
292 WHERE salesorders.orderno = salesorderdetails.orderno
293 AND salesorders.debtorno = debtorsmaster.debtorno
294 AND salesorders.branchcode = custbranch.branchcode
295 AND debtorsmaster.debtorno = custbranch.debtorno
296 AND salesorderdetails.stkcode='". $SelectedStockItem ."'
297 AND salesorders.orddate >= '" . $DateAfterCriteria . "'
298 AND salesorders.quotation=0
299 GROUP BY salesorders.orderno,
300 debtorsmaster.name,
301 custbranch.brname,
302 salesorders.customerref,
303 salesorders.orddate,
304 salesorders.deliverydate,
305 salesorders.deliverto
306 ORDER BY salesorders.orderno";
307 } else {
308 $SQL = "SELECT salesorders.orderno,
309 debtorsmaster.name,
310 custbranch.brname,
311 salesorders.customerref,
312 salesorders.orddate,
313 salesorders.deliverto,
314 salesorders.deliverydate, SUM(salesorderdetails.unitprice*salesorderdetails.quantity*(1-salesorderdetails.discountpercent)) AS ordervalue
315 FROM salesorders,
316 salesorderdetails,
317 debtorsmaster,
318 custbranch
319 WHERE salesorders.orderno = salesorderdetails.orderno
320 AND salesorders.debtorno = debtorsmaster.debtorno
321 AND salesorders.branchcode = custbranch.branchcode
322 AND debtorsmaster.debtorno = custbranch.debtorno
323 AND salesorders.orddate >= '$DateAfterCriteria'
324 AND salesorders.quotation=0
325 GROUP BY salesorders.orderno,
326 debtorsmaster.name,
327 custbranch.brname,
328 salesorders.customerref,
329 salesorders.orddate,
330 salesorders.deliverydate,
331 salesorders.deliverto
332 ORDER BY salesorders.orderno";
335 } //end selected customer
336 } //end not order number selected
338 $SalesOrdersResult = DB_query($SQL,$db);
340 if (DB_error_no($db) !=0) {
341 echo '<BR>' . _('No orders were returned by the SQL because') . ' ' . DB_error_msg($db);
342 echo "<BR>$SQL";
345 }//end of which button clicked options
347 if (!isset($_POST['OrdersAfterDate']) OR $_POST['OrdersAfterDate'] == '' OR ! Is_Date($_POST['OrdersAfterDate'])){
348 $_POST['OrdersAfterDate'] = Date($_SESSION['DefaultDateFormat'],Mktime(0,0,0,Date('m')-2,Date('d'),Date('Y')));
350 echo "<TABLE>";
351 if ($OrderNumber=='' OR !isset($OrderNumber)){
352 echo '<TR><TD>' . _('Order Number') . ':</TD><TD>' . "<INPUT type=text name='OrderNumber' MAXLENGTH =8 SIZE=9></TD><TD rowspan=2>" . _('for all orders placed after') .
353 ": </TD><TD rowspan=2><INPUT type=text name='OrdersAfterDate' MAXLENGTH =10 SIZE=11 value=" . $_POST['OrdersAfterDate'] . "></td><td rowspan=2>" .
354 "<INPUT TYPE=SUBMIT NAME='SearchOrders' VALUE='" . _('Search Orders') . "'></TD></TR>";
355 echo '<TR><TD>' . _('Customer Ref') . ':</TD><TD>' . "<INPUT type=text name='CustomerRef' MAXLENGTH =8 SIZE=9></TD></TR>";
357 echo '</TABLE>';
359 if (!isset($SelectedStockItem)) {
360 $SQL='SELECT categoryid, categorydescription FROM stockcategory ORDER BY categorydescription';
361 $result1 = DB_query($SQL,$db);
363 echo '<HR>';
364 echo '<FONT SIZE=1>' . _('To search for sales orders for a specific part use the part selection facilities below') . '</FONT>';
365 echo '<INPUT TYPE=SUBMIT NAME="SearchParts" VALUE="' . _('Search Parts Now') . '">';
367 if (count($_SESSION['AllowedPageSecurityTokens'])>1){
368 echo '<INPUT TYPE=SUBMIT NAME="ResetPart" VALUE="' . _('Show All') . '">';
370 echo '<TABLE>';
371 echo '<TR><TD><FONT SIZE=1>' . _('Select a stock category') . ':</FONT>';
372 echo '<SELECT NAME="StockCat">';
374 while ($myrow1 = DB_fetch_array($result1)) {
375 if ($myrow1['categoryid'] == $_POST['StockCat']){
376 echo "<OPTION SELECTED VALUE='". $myrow1['categoryid'] . "'>" . $myrow1['categorydescription'];
377 } else {
378 echo "<OPTION VALUE='". $myrow1['categoryid'] . "'>" . $myrow1['categorydescription'];
382 echo '</SELECT>';
383 echo '<TD><FONT SIZE=1>' . _('Enter text extracts in the description') . ':</FONT></TD>';
384 echo '<TD><INPUT TYPE="Text" NAME="Keywords" SIZE=20 MAXLENGTH=25></TD></TR>';
385 echo '<TR><TD></TD>';
386 echo '<TD><FONT SIZE 3><B> ' ._('OR') . ' </B></FONT><FONT SIZE=1>' . _('Enter extract of the Stock Code') . ':</FONT></TD>';
387 echo '<TD><INPUT TYPE="Text" NAME="StockCode" SIZE=15 MAXLENGTH=18></TD>';
388 echo '</TR>';
389 echo '</TABLE>';
391 echo '<HR>';
395 If (isset($StockItemsResult)) {
397 echo '<TABLE CELLPADDING=2 COLSPAN=7 BORDER=2>';
399 $TableHeadings = "<TR><TD class='tableheader'>" . _('Code') . "</TD>" .
400 "<TD class='tableheader'>" . _('Description') . "</TD>" .
401 "<TD class='tableheader'>" . _('On Hand') . '</TD>' .
402 "<TD class='tableheader'>" . _('Purchase Orders') . '</TD>' .
403 "<TD class='tableheader'>" . _('Sales Orders') . "</TD>" .
404 "<TD class='tableheader'>" . _('Units') . '</TD></TR>';
406 echo $TableHeadings;
408 $j = 1;
409 $k=0; //row colour counter
411 while ($myrow=DB_fetch_array($StockItemsResult)) {
413 if ($k==1){
414 echo "<tr bgcolor='#CCCCCC'>";
415 $k=0;
416 } else {
417 echo "<tr bgcolor='#EEEEEE'>";
418 $k++;
421 printf("<td><FONT SIZE=1><INPUT TYPE=SUBMIT NAME='SelectedStockItem' VALUE='%s'</FONT></td>
422 <td><FONT SIZE=1>%s</FONT></td>
423 <td ALIGN=RIGHT><FONT SIZE=1>%s</FONT></td>
424 <td ALIGN=RIGHT><FONT SIZE=1>%s</FONT></td>
425 <td ALIGN=RIGHT><FONT SIZE=1>%s</FONT></td>
426 <td><FONT SIZE=1>%s</FONT></td></tr>",
427 $myrow['stockid'],
428 $myrow['description'],
429 $myrow['qoh'],
430 $myrow['qoo'],
431 $myrow['qdem'],
432 $myrow['units']);
434 $j++;
435 If ($j == 12){
436 $j=1;
437 echo $TableHeadings;
439 //end of page full new headings if
441 //end of while loop
443 echo '</TABLE>';
446 //end if stock search results to show
448 If ($SalesOrdersResult) {
450 /*show a table of the orders returned by the SQL */
452 echo '<TABLE CELLPADDING=2 COLSPAN=6 WIDTH=100%>';
454 $tableheader = "<TR><TD class='tableheader'>" . _('Order') . " #</TD>
455 <TD class='tableheader'>" . _('Customer') . "</TD>
456 <TD class='tableheader'>" . _('Branch') . "</TD>
457 <TD class='tableheader'>" . _('Cust Order') . " #</TD>
458 <TD class='tableheader'>" . _('Order Date') . "</TD>
459 <TD class='tableheader'>" . _('Req Del Date') . "</TD>
460 <TD class='tableheader'>" . _('Delivery To') . "</TD>
461 <TD class='tableheader'>" . _('Order Total') . "</TD></TR>";
463 echo $tableheader;
465 $j = 1;
466 $k=0; //row colour counter
467 while ($myrow=DB_fetch_array($SalesOrdersResult)) {
470 if ($k==1){
471 echo "<tr bgcolor='#CCCCCC'>";
472 $k=0;
473 } else {
474 echo "<tr bgcolor='#EEEEEE'>";
475 $k=1;
478 $ViewPage = $rootpath . '/OrderDetails.php?' .SID . '&OrderNumber=' . $myrow['orderno'];
479 $FormatedDelDate = ConvertSQLDate($myrow['deliverydate']);
480 $FormatedOrderDate = ConvertSQLDate($myrow['orddate']);
481 $FormatedOrderValue = number_format($myrow['ordervalue'],2);
483 printf("<td><A target='_blank' HREF='%s'>%s</A></td>
484 <td>%s</td>
485 <td>%s</td>
486 <td>%s</td>
487 <td>%s</td>
488 <td>%s</td>
489 <td>%s</td>
490 <td ALIGN=RIGHT>%s</td>
491 </tr>",
492 $ViewPage,
493 $myrow['orderno'],
494 $myrow['name'],
495 $myrow['brname'],
496 $myrow['customerref'],
497 $FormatedOrderDate,
498 $FormatedDelDate,
499 $myrow['deliverto'],
500 $FormatedOrderValue);
502 $j++;
503 If ($j == 12){
504 $j=1;
505 echo $tableheader;
507 //end of page full new headings if
509 //end of while loop
511 echo '</TABLE>';
515 echo '</form>';
516 include('includes/footer.inc');