<?php

/* $Revision: 1.19 $ */

$PageSecurity = 2;

include('includes/session.inc');
$title = _('Search Outstanding Quotation');
include('includes/header.inc');

echo '<p class="page_title_text"><img src="'.$rootpath.'/css/'.$theme.'/images/sales.png" title="' . _('Sales') . '" alt="">' . ' ' . _('Outstanding Quotation') . '</p> ';

echo '<form action=' . $_SERVER['PHP_SELF'] .'?' .SID . ' method=post>';


If (isset($_POST['ResetPart'])){
     unset($_REQUEST['SelectedStockItem']);
}

echo '<p><div class="centre">';

If (isset($_REQUEST['quotation']) AND $_REQUEST['quotation']!='') {
	$_REQUEST['quotation'] = trim($_REQUEST['quotation']);
	if (!is_numeric($_REQUEST['quotation'])){
		  echo '<br><b>' . _('The Quotation entered MUST be numeric') . '</b><br>';
		  unset ($_REQUEST['quotation']);
		  include('includes/footer.inc');
		  exit;
	} else {
		echo _('Quotation Number') . ' - ' . $_REQUEST['quotation'];
	}
} else {
	If (isset($_REQUEST['SelectedCustomer'])) {
		echo _('For customer') . ': ' . $_REQUEST['SelectedCustomer'] . ' ' . _('and') . ' ';
		echo "<input type=hidden name='SelectedCustomer' value=" . $_REQUEST['SelectedCustomer'] . '>';
	}
	If (isset($_REQUEST['SelectedStockItem'])) {
		 echo _('for the part') . ': ' . $_REQUEST['SelectedStockItem'] . ' ' . _('and') . " <input type=hidden name='SelectedStockItem' value='" . $_REQUEST['SelectedStockItem'] . "'>";
	}
}

if (isset($_POST['SearchParts']))
{

	If ($_POST['Keywords'] AND $_POST['StockCode'])
	 {
	echo _('Stock description keywords have been used in preference to the Stock code extract entered');
	}
	If ($_POST['Keywords'])
	 {
		//insert wildcard characters in spaces
		$i=0;
		$SearchString = '%';
		while (strpos($_POST['Keywords'], ' ', $i)) {
			$wrdlen=strpos($_POST['Keywords'],' ',$i) - $i;
			$SearchString=$SearchString . substr($_POST['Keywords'],$i,$wrdlen) . '%';
			$i=strpos($_POST['Keywords'],' ',$i) +1;
		}
		$SearchString = $SearchString . substr($_POST['Keywords'],$i).'%';

		$SQL = "SELECT stockmaster.stockid,
				stockmaster.description,
				SUM(locstock.quantity) AS qoh,
				stockmaster.units
			FROM stockmaster,
				locstock
			WHERE stockmaster.stockid=locstock.stockid
			AND stockmaster.description " . LIKE . " '" . $SearchString . "'
			AND stockmaster.categoryid='" . $_POST['StockCat']. "'
			GROUP BY stockmaster.stockid,
				stockmaster.description,
				stockmaster.units
			ORDER BY stockmaster.stockid";

	 } elseif (isset($_POST['StockCode']))
	 {
		$SQL = "SELECT stockmaster.stockid,
				stockmaster.description,
				sum(locstock.quantity) as qoh,
				stockmaster.units
			FROM stockmaster,
				locstock
			WHERE stockmaster.stockid=locstock.stockid
			AND stockmaster.stockid " . LIKE . " '%" . $_POST['StockCode'] . "%'
			AND stockmaster.categoryid='" . $_POST['StockCat'] . "'
			GROUP BY stockmaster.stockid,
				stockmaster.description,
				stockmaster.units
			ORDER BY stockmaster.stockid";

	 } elseif (!isset($_POST['StockCode']) AND !isset($_POST['Keywords'])) 
	 {
		$SQL = "SELECT stockmaster.stockid,
				stockmaster.description,
				sum(locstock.quantity) as qoh,
				stockmaster.units
			FROM stockmaster,
				locstock
			WHERE stockmaster.stockid=locstock.stockid
			AND stockmaster.categoryid='" . $_POST['StockCat'] ."'
			GROUP BY stockmaster.stockid,
				stockmaster.description,
				stockmaster.units
			ORDER BY stockmaster.stockid";
	 }

	$ErrMsg =  _('No stock items were returned by the SQL because');
	$DbgMsg = _('The SQL used to retrieve the searched parts was');
	$StockItemsResult = DB_query($SQL,$db,$ErrMsg,$DbgMsg);

}

if (isset($_POST['StockID'])){
	$StockID = trim(strtoupper($_POST['StockID']));
} elseif (isset($_GET['StockID'])){
	$StockID = trim(strtoupper($_GET['StockID']));
}

if (!isset($StockID)) {

     /* Not appropriate really to restrict search by date since may miss older
     ouststanding orders
	$OrdersAfterDate = Date('d/m/Y',Mktime(0,0,0,Date('m')-2,Date('d'),Date('Y')));
     */

	if (!isset($_REQUEST['quotation']) or $_REQUEST['quotation']==''){

		echo _('Quotation number') . ": <input type=text name='quotation' maxlength=8 size=9>&nbsp " . _('From Stock Site') . ":<select name='StockLocation'> ";
		
		$sql = 'SELECT loccode, locationname FROM locations';
		
		$resultStkLocs = DB_query($sql,$db);
		
		while ($myrow=DB_fetch_array($resultStkLocs)){
			if (isset($_POST['StockLocation'])){
				if ($myrow['loccode'] == $_POST['StockLocation']){
				     echo "<option selected Value='" . $myrow['loccode'] . "'>" . $myrow['locationname'];
				} else {
				     echo "<option Value='" . $myrow['loccode'] . "'>" . $myrow['locationname'];
				}
			} elseif ($myrow['loccode']==$_SESSION['UserStockLocation']){
				 echo "<option selected Value='" . $myrow['loccode'] . "'>" . $myrow['locationname'];
			} else {
				 echo "<option Value='" . $myrow['loccode'] . "'>" . $myrow['locationname'];
			}
		}

		echo '</select> &nbsp&nbsp';
/*		echo '<select name="Quotations">';
		
		if ($_GET['Quotations']=='Quotes_Only'){
			$_POST['Quotations']='Quotes_Only';
		}
		
		if ($_POST['Quotations']=='Quotes_Only'){
			echo '<option selected VALUE="Quotes_Only">' . _('Quotations Only');
			echo '<option VALUE="Orders_Only">' . _('Orders Only');
		} else {
			echo '<option selected VALUE="Orders_Only">' . _('Orders Only');
			echo '<option VALUE="Quotes_Only">' . _('Quotations Only');
		}
		
		echo '</select> &nbsp&nbsp';*/
		echo "<input type=submit name='SearchOrders' VALUE='" . _('Search') . "'>";
    echo '&nbsp;&nbsp;<a href="' . $rootpath . '/SelectOrderItems.php?' . SID . '&NewOrder=Yes">' . _('Add Sales Order') . '</a>';
	}

	$SQL='SELECT categoryid,
			categorydescription
		FROM stockcategory
		ORDER BY categorydescription';

	$result1 = DB_query($SQL,$db);

	echo '<hr>
		<font size=1>' . _('To search for Quotation for a specific part use the part selection facilities below') . "</font>     <input type=submit name='SearchParts' VALUE='" . _('Search Parts Now') . "'><input type=submit name='ResetPart' VALUE='" . _('Show All') . "'>
      </div><table>
      	<tr>
      		<td><font size=1>" . _('Select a stock category') . ":</font>
      			<select name='StockCat'>";

	while ($myrow1 = DB_fetch_array($result1)) {
		echo "<option VALUE='". $myrow1['categoryid'] . "'>" . $myrow1['categorydescription'];
	}

      echo '</select>
      		<td><font size=1>' . _('Enter text extract(s) in the description') . ":</font></td>
      		<td><input type='Text' name='Keywords' size=20 maxlength=25></td>
	</tr>
      	<tr><td></td>
      		<td><font SIZE 3><b>" . _('OR') . ' </b></font><font size=1>' . _('Enter extract of the Stock Code') . "</b>:</font></td>
      		<td><input type='Text' name='StockCode' size=15 maxlength=18></td>
      	</tr>
      </table>
      <hr>";

If (isset($StockItemsResult)) {

	echo '<table cellpadding=2 colspan=7 BORDER=2>';
	$TableHeader = "<tr>
				<th>" . _('Code') . "</th>
				<th>" . _('Description') . "</th>
				<th>" . _('On Hand') . "</th>
				<th>" . _('Units') . "</th>
			</tr>";
	echo $TableHeader;

	$j = 1;
	$k=0; //row colour counter

	while ($myrow=DB_fetch_array($StockItemsResult)) {

		if ($k==1){
			echo '<tr class="EvenTableRows">';
			$k=0;
		} else {
			echo '<tr class="OddTableRows">';
			$k++;
		}

		printf("<td><input type=submit name='SelectedStockItem' VALUE='%s'</td>
			<td>%s</td>
			<td align=right>%s</td>
			<td>%s</td>
			</tr>",
			$myrow['stockid'],
			$myrow['description'],
			$myrow['qoh'],
			$myrow['units']);

		$j++;
		If ($j == 12){
			$j=1;
			echo $TableHeader;
		}
//end of page full new headings if
	}
//end of while loop

	echo '</table>';

}
//end if stock search results to show
  else {

	//figure out the SQL required from the inputs available
		$Quotations =1;
	if(!isset($_POST['StockLocation'])) {
		$_POST['StockLocation'] = '';
	}
	
if(isset($_REQUEST['SearchOrders'])){	
	
	if (isset($_REQUEST['quotation']) && $_REQUEST['quotation'] !='') {
		 $SQL = "SELECT salesquotation.quotationorderno,
					debtorsmaster.name,
					custbranch.brname,
					salesquotation.customerref,
					salesquotation.orddate,
					salesquotation.deliverto,
					salesquotation.printedpackingslip,
					salesquotation.confirmeddate,
					SUM(salesquotationdetails.unitprice*salesquotationdetails.quantity*(1-salesquotationdetails.discountpercent)) AS ordervalue
				FROM salesquotation,
					salesquotationdetails,
					debtorsmaster,
					custbranch
				WHERE salesquotation.quotationorderno = salesquotationdetails.quotationorderno
				AND salesquotation.branchcode = custbranch.branchcode
				AND salesquotation.debtorno = debtorsmaster.debtorno
				AND debtorsmaster.debtorno = custbranch.debtorno
				AND salesquotationdetails.completed=0
				AND salesquotation.quotationorderno=". $_REQUEST['quotation'] ."
				AND salesquotation.quotation =" .$Quotations . " 
				GROUP BY salesquotation.quotationorderno,
					debtorsmaster.name,
					custbranch.brname,
					salesquotation.customerref,
					salesquotation.orddate,
					
					salesquotation.deliverto,
					salesquotation.printedpackingslip
				ORDER BY salesquotation.quotationorderno";
	} else {
	      /* $DateAfterCriteria = FormatDateforSQL($OrdersAfterDate); */

		if (isset($_REQUEST['SelectedCustomer'])) {

			if (isset($_REQUEST['SelectedStockItem'])) {
				$SQL = "SELECT salesquotation.quotationorderno,
						debtorsmaster.name,
						custbranch.brname,
						salesquotation.customerref,
						salesquotation.orddate,
						salesquotation.deliverto,
					  salesquotation.printedpackingslip,
					  salesquotation.confirmeddate,
						salesquotationdetails.unitprice*salesquotationdetails.quantity*(1-salesquotationdetails.discountpercent) AS ordervalue
					FROM salesquotation,
						salesquotationdetails,
						debtorsmaster,
						custbranch
					WHERE salesquotation.quotationorderno = salesquotationdetails.quotationorderno
					AND salesquotation.debtorno = debtorsmaster.debtorno
					AND debtorsmaster.debtorno = custbranch.debtorno
					AND salesquotation.branchcode = custbranch.branchcode
					AND salesquotationdetails.completed=0
					AND salesquotation.quotation =" .$Quotations . "
					AND salesquotationdetails.stkcode='". $_REQUEST['SelectedStockItem'] ."'
					AND salesquotation.debtorno='" . $_REQUEST['SelectedCustomer'] ."'
					AND salesquotation.fromstkloc = '". $_POST['StockLocation'] . "'
					ORDER BY salesquotation.quotationorderno";
						

			} else {
				$SQL = "SELECT salesquotation.quotationorderno,
						debtorsmaster.name,
						custbranch.brname,
						salesquotation.customerref,
						salesquotation.orddate,
						salesquotation.deliverto,
					  salesquotation.printedpackingslip,
					  salesquotation.confirmeddate,
						 SUM(salesquotationdetails.unitprice*salesquotationdetails.quantity*(1-salesquotationdetails.discountpercent)) AS ordervalue
					FROM salesquotation,
						salesquotationdetails,
						debtorsmaster,
						custbranch
					WHERE salesquotation.quotationorderno = salesquotationdetails.quotationorderno
					AND salesquotation.debtorno = debtorsmaster.debtorno
					AND debtorsmaster.debtorno = custbranch.debtorno
					AND salesquotation.branchcode = custbranch.branchcode
					AND salesquotation.quotation =" .$Quotations . "
					AND salesquotationdetails.completed=0
					AND salesquotation.debtorno='" . $_REQUEST['SelectedCustomer'] . "'
					AND salesquotation.fromstkloc = '". $_POST['StockLocation'] . "'
					GROUP BY salesquotation.quotationorderno,
						debtorsmaster.name,
						salesquotation.debtorno,
						custbranch.brname,
						salesquotation.customerref,
						salesquotation.orddate,
						salesquotation.deliverto,
						salesquotation.deliverydate
					ORDER BY salesquotation.quotationorderno";

			}
		} else { //no customer selected
			if (isset($_REQUEST['SelectedStockItem'])) {
				$SQL = "SELECT salesquotation.quotationorderno,
						debtorsmaster.name,
						custbranch.brname,
						salesquotation.customerref,
						salesquotation.orddate,
						salesquotation.deliverto,
					  	salesquotation.printedpackingslip,
						salesquotation.confirmeddate,
						 SUM(salesquotationdetails.unitprice*salesquotationdetails.quantity*(1-salesquotationdetails.discountpercent)) AS ordervalue
					FROM salesquotation,
						salesquotationdetails,
						debtorsmaster,
						custbranch
					WHERE salesquotation.quotationorderno = salesquotationdetails.quotationorderno
					AND salesquotation.debtorno = debtorsmaster.debtorno
					AND debtorsmaster.debtorno = custbranch.debtorno
					AND salesquotation.branchcode = custbranch.branchcode
					AND salesquotationdetails.completed=0
					AND salesquotation.quotation =" .$Quotations . "
					AND salesquotationdetails.stkcode='". $_REQUEST['SelectedStockItem'] . "'
					AND salesquotation.fromstkloc = '". $_POST['StockLocation'] . "'
					GROUP BY salesquotation.quotationorderno,
						debtorsmaster.name,
						custbranch.brname,
						salesquotation.customerref,
						salesquotation.orddate,
						salesquotation.deliverto,
						
						salesquotation.printedpackingslip
					ORDER BY salesquotation.quotationorderno";
			} else {
				$SQL = "SELECT salesquotation.quotationorderno,
						debtorsmaster.name,
						custbranch.brname,
						salesquotation.customerref,
						salesquotation.orddate,
						salesquotation.deliverto,
					  salesquotation.printedpackingslip,
					  salesquotation.confirmeddate,
						SUM(salesquotationdetails.unitprice*salesquotationdetails.quantity*(1-salesquotationdetails.discountpercent)) AS ordervalue
					FROM salesquotation,
						salesquotationdetails,
						debtorsmaster,
						custbranch
					WHERE salesquotation.quotationorderno = salesquotationdetails.quotationorderno
					AND salesquotation.debtorno = debtorsmaster.debtorno
					AND debtorsmaster.debtorno = custbranch.debtorno
					AND salesquotation.branchcode = custbranch.branchcode
					AND salesquotationdetails.completed=0
					AND salesquotation.fromstkloc = '". $_POST['StockLocation'] . "'
					GROUP BY salesquotation.quotationorderno,
						debtorsmaster.name,
						custbranch.brname,
						salesquotation.customerref,
						salesquotation.orddate,
						salesquotation.deliverto,
						salesquotation.printedpackingslip
					ORDER BY salesquotation.quotationorderno";
			}

		} //end selected customer
	} //end not order number selected
}else{
if(isset($_REQUEST['ResetPart'])){
$SQL = "SELECT salesquotation.quotationorderno,
						debtorsmaster.name,
						custbranch.brname,
						salesquotation.customerref,
						salesquotation.orddate,
						salesquotation.deliverto,
					  salesquotation.printedpackingslip,
					  salesquotation.confirmeddate,
						SUM(salesquotationdetails.unitprice*salesquotationdetails.quantity*(1-salesquotationdetails.discountpercent)) AS ordervalue
					FROM salesquotation,
						salesquotationdetails,
						debtorsmaster,
						custbranch
					WHERE salesquotation.quotationorderno = salesquotationdetails.quotationorderno
					AND salesquotation.debtorno = debtorsmaster.debtorno
					AND debtorsmaster.debtorno = custbranch.debtorno
					AND salesquotation.branchcode = custbranch.branchcode
					AND salesquotationdetails.completed=0
					GROUP BY salesquotation.quotationorderno,
						debtorsmaster.name,
						custbranch.brname,
						salesquotation.customerref,
						salesquotation.orddate,
						salesquotation.deliverto,
						salesquotation.printedpackingslip
					ORDER BY salesquotation.quotationorderno";
}
}
	$ErrMsg = _('No quotations were returned by the SQL because');
	$SalesOrdersResult = DB_query($SQL,$db,$ErrMsg);
	
	/*show a table of the orders returned by the SQL */

	echo '<table cellpadding=2 colspan=7 WIDTH=100%>';

	if (isset($_POST['SearchOrders']) or $_POST['ResetPart']){
		$tableheader = "<tr>
				<th>" . _('Create Sales Order') . "</th>
				<th>" . _('Print Quote') . "</th>
				<th>" . _('Customer') . "</th>
				<th>" . _('Branch') . "</th>
				<th>" . _('Cust Ref') . " #</th>
				<th>" . _('Quote Date') . "</th>
				<th>" . _('Req Del Date') . "</th>
				<th>" . _('Delivery To') . "</th>
				<th>" . _('Quote Total') . "</th></tr>";
	}
	
	echo $tableheader;

	$j = 1;
	$k=0; //row colour counter
	while ($myrow=DB_fetch_array($SalesOrdersResult)) {


		if ($k==1){
			echo '<tr class="EvenTableRows">';
			$k=0;
		} else {
			echo '<tr class="OddTableRows">';
			$k++;
		}

		$ModifyPage = $rootpath . "/Quotationitems.php?" . SID . '&ModifyOrderNumber=' . $myrow['quotationorderno'];
		$Confirm_Invoice = $rootpath . '/ConfirmDispatch_Invoice.php?' . SID . '&quotation=' .$myrow['quotationorderno'];
		
		if ($_SESSION['PackNoteFormat']==1){ /*Laser printed A4 default */
			$PrintDispatchNote = $rootpath . '/PrintCustOrder_generic.php?' . SID . '&TransNo=' . $myrow['quotationorderno'];
		} else { /*pre-printed stationery default */
			$PrintDispatchNote = $rootpath . '/PrintCustOrder.php?' . SID . '&TransNo=' . $myrow['quotationorderno'];
		}
		$PrintQuotation = $rootpath . '/PDFQuotation.php?' . SID . '&QuotationNo=' . $myrow['quotationorderno'];
		$FormatedDelDate = ConvertSQLDate($myrow['confirmeddate']);
		$FormatedOrderDate = ConvertSQLDate($myrow['orddate']);
		$FormatedOrderValue = number_format($myrow['ordervalue'],2);
//confirmeddate
		if ($myrow['printedpackingslip']==0) {
		  $PrintText = _('Print');
		} else {
		  $PrintText = _('Reprint');
		}
		
		if (isset($_POST['SearchOrders']) or $_POST['ResetPart']){
			/*must be quotes only */
			printf("<td><a href='%s'>%s</a></td>
				<td><a href='%s'>" . $PrintText . "</a></td>
				<td>%s</td>
				<td>%s</td>
				<td>%s</td>
				<td>%s</td>
				<td>%s</td>
				<td>%s</td>
				<td align=right>%s</td>
				</tr>",
				$ModifyPage,
				$myrow['quotationorderno'],
				$PrintQuotation,
				$myrow['name'],
				$myrow['brname'],
				$myrow['customerref'],
				$FormatedOrderDate,
				$FormatedDelDate,
				$myrow['deliverto'],
				$FormatedOrderValue);
		}
		
		$j++;
		If ($j == 12){
			$j=1;
			echo $tableheader;
		}
	//end of page full new headings if
	}
	//end of while loop

	echo '</table>';
}

?>
</form>

<?php } //end StockID already selected

include('includes/footer.inc');
?>
