1<?php
2
3
4include('includes/session.php');
5include('includes/SQL_CommonFunctions.inc');
6
7$InputError=0;
8
9if (isset($_POST['FromDate']) AND !Is_Date($_POST['FromDate'])){
10	$msg = _('The date from must be specified in the format') . ' ' . $_SESSION['DefaultDateFormat'];
11	$InputError=1;
12	unset($_POST['FromDate']);
13}
14if (isset($_POST['ToDate']) AND !Is_Date($_POST['ToDate'])){
15	$msg = _('The date to must be specified in the format') . ' ' . $_SESSION['DefaultDateFormat'];
16	$InputError=1;
17	unset($_POST['ToDate']);
18}
19
20if (!isset($_POST['FromDate']) OR !isset($_POST['ToDate'])){
21
22	$Title = _('Order Status Report');
23	include ('includes/header.php');
24
25	if ($InputError==1){
26		prnMsg($msg,'error');
27	}
28
29	echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/transactions.png" title="' . $Title . '" alt="" />' . ' ' . _('Order Status Report') . '</p>';
30
31	echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '">';
32    echo '<div>
33		<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />
34		<table class="selection">
35		<tr>
36			<td>' . _('Enter the date from which orders are to be listed') . ':</td>
37			<td><input type="text" required="required" autofocus="autofocus" class="date" name="FromDate" maxlength="10" size="11" value="' . Date($_SESSION['DefaultDateFormat'], Mktime(0,0,0,Date('m'),Date('d')-1,Date('y'))) . '" /></td>
38		</tr>
39		<tr>
40			<td>' . _('Enter the date to which orders are to be listed') . ':</td>
41			<td><input type="text" required="required" class="date" name="ToDate" maxlength="10" size="11" value="' . Date($_SESSION['DefaultDateFormat']) . '" /></td>
42		</tr>
43		<tr>
44			<td>' . _('Inventory Category') . '</td>
45			<td>';
46
47	$sql = "SELECT categorydescription, categoryid FROM stockcategory WHERE stocktype<>'D' AND stocktype<>'L'";
48	$result = DB_query($sql);
49
50
51	echo '<select required="required" name="CategoryID">
52		<option selected="selected" value="All">' . _('Over All Categories') . '</option>';
53
54	while ($myrow=DB_fetch_array($result)){
55		echo '<option value="' . $myrow['categoryid'] . '">' . $myrow['categorydescription'] . '</option>';
56	}
57	echo '</select></td>
58		</tr>
59		<tr>
60			<td>' . _('Inventory Location') . ':</td>
61			<td><select name="Location">
62				<option selected="selected" value="All">' . _('All Locations') . '</option>';
63
64	$result= DB_query("SELECT locations.loccode, locationname FROM locations INNER JOIN locationusers ON locationusers.loccode=locations.loccode AND locationusers.userid='" .  $_SESSION['UserID'] . "' AND locationusers.canview=1");
65	while ($myrow=DB_fetch_array($result)){
66		echo '<option value="' . $myrow['loccode'] . '">' . $myrow['locationname'] . '</option>';
67	}
68	echo '</select></td></tr>';
69
70	echo '<tr>
71			<td>' . _('Back Order Only') . ':</td>
72			<td><select name="BackOrders">
73				<option selected="selected" value="Yes">' . _('Only Show Back Orders') . '</option>
74				<option value="No">' . _('Show All Orders') . '</option>
75			</select></td>
76		</tr>
77		</table>
78		<br />
79		<div class="centre">
80			<input type="submit" name="Go" value="' . _('Create PDF') . '" />
81		</div>
82		</div>
83	</form>';
84
85	include('includes/footer.php');
86	exit;
87} else {
88	include('includes/PDFStarter.php');
89	$pdf->addInfo('Title',_('Order Status Report'));
90	$pdf->addInfo('Subject',_('Orders from') . ' ' . $_POST['FromDate'] . ' ' . _('to') . ' ' . $_POST['ToDate']);
91	$line_height=12;
92	$PageNumber = 1;
93	$TotalDiffs = 0;
94}
95
96
97if ($_POST['CategoryID']=='All' AND $_POST['Location']=='All'){
98	$sql= "SELECT salesorders.orderno,
99				  salesorders.debtorno,
100				  salesorders.branchcode,
101				  salesorders.customerref,
102				  salesorders.orddate,
103				  salesorders.fromstkloc,
104				  salesorders.printedpackingslip,
105				  salesorders.datepackingslipprinted,
106				  salesorderdetails.stkcode,
107				  stockmaster.description,
108				  stockmaster.units,
109				  stockmaster.decimalplaces,
110				  salesorderdetails.quantity,
111				  salesorderdetails.qtyinvoiced,
112				  salesorderdetails.completed,
113				  debtorsmaster.name,
114				  custbranch.brname,
115				  locations.locationname
116			 FROM salesorders
117				 INNER JOIN salesorderdetails
118				 ON salesorders.orderno = salesorderdetails.orderno
119				 INNER JOIN stockmaster
120				 ON salesorderdetails.stkcode = stockmaster.stockid
121				 INNER JOIN debtorsmaster
122				 ON salesorders.debtorno=debtorsmaster.debtorno
123				 INNER JOIN custbranch
124				 ON custbranch.debtorno=salesorders.debtorno
125				 AND custbranch.branchcode=salesorders.branchcode
126				 INNER JOIN locations
127				 ON salesorders.fromstkloc=locations.loccode
128				 INNER JOIN locationusers ON locationusers.loccode=locations.loccode AND locationusers.userid='" .  $_SESSION['UserID'] . "' AND locationusers.canview=1
129			 WHERE salesorders.orddate >='" . FormatDateForSQL($_POST['FromDate']) . "'
130				  AND salesorders.orddate <='" . FormatDateForSQL($_POST['ToDate']) . "'
131			 AND salesorders.quotation=0";
132
133} elseif ($_POST['CategoryID']!='All' AND $_POST['Location']=='All') {
134	$sql= "SELECT salesorders.orderno,
135				  salesorders.debtorno,
136				  salesorders.branchcode,
137				  salesorders.customerref,
138				  salesorders.orddate,
139				  salesorders.fromstkloc,
140				  salesorders.printedpackingslip,
141				  salesorders.datepackingslipprinted,
142				  salesorderdetails.stkcode,
143				  stockmaster.description,
144				  stockmaster.units,
145				  stockmaster.decimalplaces,
146				  salesorderdetails.quantity,
147				  salesorderdetails.qtyinvoiced,
148				  salesorderdetails.completed,
149				  debtorsmaster.name,
150				  custbranch.brname,
151				  locations.locationname
152			 FROM salesorders
153				 INNER JOIN salesorderdetails
154				 ON salesorders.orderno = salesorderdetails.orderno
155				 INNER JOIN stockmaster
156				 ON salesorderdetails.stkcode = stockmaster.stockid
157				 INNER JOIN debtorsmaster
158				 ON salesorders.debtorno=debtorsmaster.debtorno
159				 INNER JOIN custbranch
160				 ON custbranch.debtorno=salesorders.debtorno
161				 AND custbranch.branchcode=salesorders.branchcode
162				 INNER JOIN locations
163				 ON salesorders.fromstkloc=locations.loccode
164				 INNER JOIN locationusers ON locationusers.loccode=locations.loccode AND locationusers.userid='" .  $_SESSION['UserID'] . "' AND locationusers.canview=1
165			 WHERE stockmaster.categoryid ='" . $_POST['CategoryID'] . "'
166				  AND orddate >='" . FormatDateForSQL($_POST['FromDate']) . "'
167				  AND orddate <='" . FormatDateForSQL($_POST['ToDate']) . "'
168			 AND salesorders.quotation=0";
169
170
171} elseif ($_POST['CategoryID']=='All' AND $_POST['Location']!='All') {
172	$sql= "SELECT salesorders.orderno,
173				  salesorders.debtorno,
174				  salesorders.branchcode,
175				  salesorders.customerref,
176				  salesorders.orddate,
177				  salesorders.fromstkloc,
178				  salesorders.printedpackingslip,
179				  salesorders.datepackingslipprinted,
180				  salesorderdetails.stkcode,
181				  stockmaster.description,
182				  stockmaster.units,
183				  stockmaster.decimalplaces,
184				  salesorderdetails.quantity,
185				  salesorderdetails.qtyinvoiced,
186				  salesorderdetails.completed,
187				  debtorsmaster.name,
188				  custbranch.brname,
189				  locations.locationname
190			 FROM salesorders
191				 INNER JOIN salesorderdetails
192				 ON salesorders.orderno = salesorderdetails.orderno
193				 INNER JOIN stockmaster
194				 ON salesorderdetails.stkcode = stockmaster.stockid
195				 INNER JOIN debtorsmaster
196				 ON salesorders.debtorno=debtorsmaster.debtorno
197				 INNER JOIN custbranch
198				 ON custbranch.debtorno=salesorders.debtorno
199				 AND custbranch.branchcode=salesorders.branchcode
200				 INNER JOIN locations
201				 ON salesorders.fromstkloc=locations.loccode
202				 INNER JOIN locationusers ON locationusers.loccode=locations.loccode AND locationusers.userid='" .  $_SESSION['UserID'] . "' AND locationusers.canview=1
203			 WHERE salesorders.fromstkloc ='" . $_POST['Location'] . "'
204				  AND salesorders.orddate >='" . FormatDateForSQL($_POST['FromDate']) . "'
205				  AND salesorders.orddate <='" . FormatDateForSQL($_POST['ToDate']) . "'
206			 AND salesorders.quotation=0";
207
208
209} elseif ($_POST['CategoryID']!='All' AND $_POST['location']!='All'){
210
211	$sql= "SELECT salesorders.orderno,
212				  salesorders.debtorno,
213				  salesorders.branchcode,
214				  salesorders.customerref,
215				  salesorders.orddate,
216				  salesorders.fromstkloc,
217				  salesorders.printedpackingslip,
218				  salesorders.datepackingslipprinted,
219				  salesorderdetails.stkcode,
220				  stockmaster.description,
221				  stockmaster.units,
222				  stockmaster.decimalplaces,
223				  salesorderdetails.quantity,
224				  salesorderdetails.qtyinvoiced,
225				  salesorderdetails.completed,
226				  debtorsmaster.name,
227				  custbranch.brname,
228				  locations.locationname
229			 FROM salesorders
230				 INNER JOIN salesorderdetails
231				 ON salesorders.orderno = salesorderdetails.orderno
232				 INNER JOIN stockmaster
233				 ON salesorderdetails.stkcode = stockmaster.stockid
234				 INNER JOIN debtorsmaster
235				 ON salesorders.debtorno=debtorsmaster.debtorno
236				 INNER JOIN custbranch
237				 ON custbranch.debtorno=salesorders.debtorno
238				 AND custbranch.branchcode=salesorders.branchcode
239				 INNER JOIN locations
240				 ON salesorders.fromstkloc=locations.loccode
241				 INNER JOIN locationusers ON locationusers.loccode=locations.loccode AND locationusers.userid='" .  $_SESSION['UserID'] . "' AND locationusers.canview=1
242			 WHERE stockmaster.categoryid ='" . $_POST['CategoryID'] . "'
243				  AND salesorders.fromstkloc ='" . $_POST['Location'] . "'
244				  AND salesorders.orddate >='" . FormatDateForSQL($_POST['FromDate']) . "'
245				  AND salesorders.orddate <='" . FormatDateForSQL($_POST['ToDate']) . "'
246			 AND salesorders.quotation=0";
247}
248
249if ($_POST['BackOrders']=='Yes'){
250		$sql .= " AND salesorderdetails.quantity-salesorderdetails.qtyinvoiced >0";
251}
252//Add salesman role control
253if ($_SESSION['SalesmanLogin'] != '') {
254		$sql .= " AND salesorders.salesperson='" . $_SESSION['SalesmanLogin'] . "'";
255}
256
257$sql .= " ORDER BY salesorders.orderno";
258
259$Result=DB_query($sql,'','',false,false); //dont trap errors here
260
261if (DB_error_no()!=0){
262	include('includes/header.php');
263	echo '<br />' . _('An error occurred getting the orders details');
264	if ($debug==1){
265		echo '<br />' . _('The SQL used to get the orders that failed was') . '<br />' . $sql;
266	}
267	include ('includes/footer.php');
268	exit;
269} elseif (DB_num_rows($Result)==0){
270	$Title=_('Order Status Report - No Data');
271  	include('includes/header.php');
272	prnMsg(_('There were no orders found in the database within the period from') . ' ' . $_POST['FromDate'] . ' ' . _('to') . ' '. $_POST['ToDate'] . '. ' . _('Please try again selecting a different date range'),'info');
273	include('includes/footer.php');
274	exit;
275}
276
277include ('includes/PDFOrderStatusPageHeader.inc');
278
279$OrderNo =0; /*initialise */
280
281while ($myrow=DB_fetch_array($Result)){
282
283	$pdf->line($XPos, $YPos,$Page_Width-$Right_Margin, $YPos);
284
285	$YPos -= $line_height;
286	/*Set up headings */
287	/*draw a line */
288
289	if ($myrow['orderno']!=$OrderNo	){
290		$LeftOvers = $pdf->addTextWrap($Left_Margin+2,$YPos,40,$FontSize,_('Order'), 'left');
291		$LeftOvers = $pdf->addTextWrap($Left_Margin+40,$YPos,150,$FontSize,_('Customer'), 'left');
292		$LeftOvers = $pdf->addTextWrap($Left_Margin+190,$YPos,110,$FontSize,_('Branch'), 'left');
293		$LeftOvers = $pdf->addTextWrap($Left_Margin+300,$YPos,60,$FontSize,_('Ord Date'), 'left');
294		$LeftOvers = $pdf->addTextWrap($Left_Margin+360,$YPos,60,$FontSize,_('Location'), 'left');
295		$LeftOvers = $pdf->addTextWrap($Left_Margin+420,$YPos,80,$FontSize,_('Status'), 'left');
296
297		$YPos-=$line_height;
298
299		/*draw a line */
300		$pdf->line($XPos, $YPos,$Page_Width-$Right_Margin, $YPos);
301		$pdf->line($XPos, $YPos-$line_height*2,$XPos, $YPos+$line_height*2);
302		$pdf->line($Page_Width-$Right_Margin, $YPos-$line_height*2,$Page_Width-$Right_Margin, $YPos+$line_height*2);
303
304
305		if ($YPos - (2 *$line_height) < $Bottom_Margin){
306			/*Then set up a new page */
307			$PageNumber++;
308			include ('includes/PDFOrderStatusPageHeader.inc');
309			$OrderNo=0;
310		} /*end of new page header  */
311		$YPos -= $line_height;
312
313		$LeftOvers = $pdf->addTextWrap($Left_Margin+2,$YPos,40,$FontSize,$myrow['orderno'], 'left');
314		$LeftOvers = $pdf->addTextWrap($Left_Margin+40,$YPos,150,$FontSize,html_entity_decode($myrow['name'],ENT_QUOTES,'UTF-8'), 'left');
315		$LeftOvers = $pdf->addTextWrap($Left_Margin+190,$YPos,110,$FontSize,$myrow['brname'], 'left');
316
317		$LeftOvers = $pdf->addTextWrap($Left_Margin+300,$YPos,60,$FontSize,ConvertSQLDate($myrow['orddate']), 'left');
318		$LeftOvers = $pdf->addTextWrap($Left_Margin+360,$YPos,80,$FontSize,$myrow['locationname'], 'left');
319
320		if ($myrow['printedpackingslip']==1){
321			$PackingSlipPrinted = _('Printed') . ' ' . ConvertSQLDate($myrow['datepackingslipprinted']);
322		} else {
323			$PackingSlipPrinted =_('Not yet printed');
324		}
325
326		$LeftOvers = $pdf->addTextWrap($Left_Margin+420,$YPos,100,$FontSize,$PackingSlipPrinted, 'left');
327		$YPos -= $line_height;
328		$pdf->line($XPos, $YPos,$Page_Width-$Right_Margin, $YPos);
329
330		$YPos -= ($line_height);
331
332		 /*Its not the first line */
333		$OrderNo = $myrow['orderno'];
334		$LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,60,$FontSize,_('Code'), 'left');
335		$LeftOvers = $pdf->addTextWrap($Left_Margin+60,$YPos,120,$FontSize,_('Description'), 'left');
336		$LeftOvers = $pdf->addTextWrap($Left_Margin+180,$YPos,60,$FontSize,_('Ordered'), 'right');
337		$LeftOvers = $pdf->addTextWrap($Left_Margin+240,$YPos,60,$FontSize,_('Invoiced'), 'right');
338		$LeftOvers = $pdf->addTextWrap($Left_Margin+320,$YPos,60,$FontSize,_('Outstanding'), 'center');
339		$YPos -= ($line_height);
340
341	}
342
343	$LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,60,$FontSize,$myrow['stkcode'], 'left');
344	$LeftOvers = $pdf->addTextWrap($Left_Margin+60,$YPos,120,$FontSize,$myrow['description'], 'left');
345	$LeftOvers = $pdf->addTextWrap($Left_Margin+180,$YPos,60,$FontSize,locale_number_format($myrow['quantity'],$myrow['decimalplaces']), 'right');
346	$LeftOvers = $pdf->addTextWrap($Left_Margin+240,$YPos,60,$FontSize,locale_number_format($myrow['qtyinvoiced'],$myrow['decimalplaces']), 'right');
347
348	  if ($myrow['quantity']>$myrow['qtyinvoiced']){
349		   $LeftOvers = $pdf->addTextWrap($Left_Margin+320,$YPos,60,$FontSize,locale_number_format($myrow['quantity']-$myrow['qtyinvoiced'],$myrow['decimalplaces']), 'right');
350	  } else {
351		   $LeftOvers = $pdf->addTextWrap($Left_Margin+320,$YPos,60,$FontSize,_('Complete'), 'left');
352	  }
353
354	 $YPos -= ($line_height);
355	 if ($YPos - (2 *$line_height) < $Bottom_Margin){
356		/*Then set up a new page */
357		$PageNumber++;
358		include ('includes/PDFOrderStatusPageHeader.inc');
359		$OrderNo=0;
360	 } /*end of new page header  */
361} /* end of while there are delivery differences to print */
362$pdf->OutputD($_SESSION['DatabaseName'] . '_OrderStatus_' . date('Y-m-d') . '.pdf');
363$pdf->__destruct();
364?>
365