/StockCheck.php
PHP | 366 lines | 302 code | 58 blank | 6 comment | 42 complexity | c3abb48d8eb40e78eb6c5909ad093eaf MD5 | raw file
Possible License(s): LGPL-3.0, AGPL-1.0, GPL-2.0, LGPL-2.1
- <?php
- /* $Id$*/
- include('includes/session.inc');
- if (isset($_POST['PrintPDF'])
- and isset($_POST['FromCriteria'])
- and mb_strlen($_POST['FromCriteria'])>=1
- and isset($_POST['ToCriteria'])
- and mb_strlen($_POST['ToCriteria'])>=1){
- include('includes/PDFStarter.php');
- $pdf->addInfo('Title',_('Stock Count Sheets'));
- $pdf->addInfo('Subject',_('Stock Count Sheets'));
- $FontSize=10;
- $PageNumber=1;
- $line_height=30;
- /*First off do the stock check file stuff */
- if ($_POST['MakeStkChkData']=='New'){
- $sql = "TRUNCATE TABLE stockcheckfreeze";
- $result = DB_query($sql,$db);
- $sql = "INSERT INTO stockcheckfreeze (stockid,
- loccode,
- qoh,
- stockcheckdate)
- SELECT locstock.stockid,
- locstock.loccode,
- locstock.quantity,
- '" . Date('Y-m-d') . "'
- FROM locstock,
- stockmaster
- WHERE locstock.stockid=stockmaster.stockid AND locstock.loccode='" . $_POST['Location'] . "' AND
- stockmaster.categoryid>='" . $_POST['FromCriteria'] . "' AND
- stockmaster.categoryid<='" . $_POST['ToCriteria'] . "' AND
- stockmaster.mbflag!='A' AND
- stockmaster.mbflag!='K' AND
- stockmaster.mbflag!='D'";
- $result = DB_query($sql, $db,'','',false,false);
- if (DB_error_no($db) !=0) {
- $Title = _('Stock Count Sheets - Problem Report');
- include('includes/header.inc');
- prnMsg(_('The inventory quantities could not be added to the freeze file because') . ' ' . DB_error_msg($db),'error');
- echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
- if ($debug==1){
- echo '<br />' . $sql;
- }
- include('includes/footer.inc');
- exit;
- }
- }
- if ($_POST['MakeStkChkData']=='AddUpdate'){
- $sql = "DELETE stockcheckfreeze
- FROM stockcheckfreeze
- INNER JOIN stockmaster ON stockcheckfreeze.stockid=stockmaster.stockid
- WHERE stockmaster.categoryid >='" . $_POST['FromCriteria'] . "'
- AND stockmaster.categoryid<='" . $_POST['ToCriteria'] . "'
- AND stockcheckfreeze.loccode='" . $_POST['Location'] . "'";
- $result = DB_query($sql,$db,'','',false,false);
- if (DB_error_no($db) !=0) {
- $Title = _('Stock Freeze') . ' - ' . _('Problem Report') . '.... ';
- include('includes/header.inc');
- prnMsg(_('The old quantities could not be deleted from the freeze file because') . ' ' . DB_error_msg($db),'error');
- echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
- if ($debug==1){
- echo '<br />' . $sql;
- }
- include('includes/footer.inc');
- exit;
- }
- $sql = "INSERT INTO stockcheckfreeze (stockid,
- loccode,
- qoh,
- stockcheckdate)
- SELECT locstock.stockid,
- loccode ,
- locstock.quantity,
- '" . Date('Y-m-d') . "'
- FROM locstock INNER JOIN stockmaster
- ON locstock.stockid=stockmaster.stockid
- WHERE locstock.loccode='" . $_POST['Location'] . "'
- AND stockmaster.categoryid>='" . $_POST['FromCriteria'] . "'
- AND stockmaster.categoryid<='" . $_POST['ToCriteria'] . "'
- AND stockmaster.mbflag!='A'
- AND stockmaster.mbflag!='K'
- AND stockmaster.mbflag!='G'
- AND stockmaster.mbflag!='D'";
- $result = DB_query($sql, $db,'','',false,false);
- if (DB_error_no($db) !=0) {
- $Title = _('Stock Freeze - Problem Report');
- include('includes/header.inc');
- prnMsg(_('The inventory quantities could not be added to the freeze file because') . ' ' . DB_error_msg($db),'error');
- echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
- if ($debug==1){
- echo '<br />' . $sql;
- }
- include('includes/footer.inc');
- exit;
- } else {
- $Title = _('Stock Check Freeze Update');
- include('includes/header.inc');
- echo '<p><a href="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '">' . _('Print Check Sheets') . '</a>';
- prnMsg( _('Added to the stock check file successfully'),'success');
- include('includes/footer.inc');
- exit;
- }
- }
- $SQL = "SELECT stockmaster.categoryid,
- stockcheckfreeze.stockid,
- stockmaster.description,
- stockmaster.decimalplaces,
- stockcategory.categorydescription,
- stockcheckfreeze.qoh
- FROM stockcheckfreeze INNER JOIN stockmaster
- ON stockcheckfreeze.stockid=stockmaster.stockid
- INNER JOIN stockcategory
- ON stockmaster.categoryid=stockcategory.categoryid
- WHERE stockmaster.categoryid >= '" . $_POST['FromCriteria'] . "'
- AND stockmaster.categoryid <= '" . $_POST['ToCriteria'] . "'
- AND (stockmaster.mbflag='B' OR mbflag='M')
- AND stockcheckfreeze.loccode = '" . $_POST['Location'] . "'";
- if (isset($_POST['NonZerosOnly']) and $_POST['NonZerosOnly']==true){
- $SQL .= " AND stockcheckfreeze.qoh<>0";
- }
- $SQL .= " ORDER BY stockmaster.categoryid, stockmaster.stockid";
- $InventoryResult = DB_query($SQL,$db,'','',false,false);
- if (DB_error_no($db) !=0) {
- $Title = _('Stock Sheets') . ' - ' . _('Problem Report') . '.... ';
- include('includes/header.inc');
- prnMsg( _('The inventory quantities could not be retrieved by the SQL because') . ' ' . DB_error_msg($db),'error');
- echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
- if ($debug==1){
- echo '<br />' . $SQL;
- }
- include ('includes/footer.inc');
- exit;
- }
- if (DB_num_rows($InventoryResult) ==0) {
- $Title = _('Stock Count Sheets - Problem Report');
- include('includes/header.inc');
- prnMsg(_('Before stock count sheets can be printed, a copy of the stock quantities needs to be taken - the stock check freeze. Make a stock check data file first'),'error');
- echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
- include('includes/footer.inc');
- exit;
- }
- include ('includes/PDFStockCheckPageHeader.inc');
- $Category = '';
- while ($InventoryCheckRow = DB_fetch_array($InventoryResult,$db)){
- if ($Category!=$InventoryCheckRow['categoryid']){
- $FontSize=12;
- if ($Category!=''){ /*Then it's NOT the first time round */
- /*draw a line under the CATEGORY TOTAL*/
- $pdf->line($Left_Margin, $YPos-2,$Page_Width-$Right_Margin, $YPos-2);
- $YPos -=(2*$line_height);
- }
- $LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,260-$Left_Margin,$FontSize,$InventoryCheckRow['categoryid'] . ' - ' . $InventoryCheckRow['categorydescription'], 'left');
- $Category = $InventoryCheckRow['categoryid'];
- }
- $FontSize=10;
- $YPos -=$line_height;
- if (isset($_POST['ShowInfo']) and $_POST['ShowInfo']==true){
- $SQL = "SELECT SUM(salesorderdetails.quantity - salesorderdetails.qtyinvoiced) AS qtydemand
- FROM salesorderdetails INNER JOIN salesorders
- ON salesorderdetails.orderno=salesorders.orderno
- WHERE salesorders.fromstkloc ='" . $_POST['Location'] . "'
- AND salesorderdetails.stkcode = '" . $InventoryCheckRow['stockid'] . "'
- AND salesorderdetails.completed = 0
- AND salesorders.quotation=0";
- $DemandResult = DB_query($SQL,$db,'','',false, false);
- if (DB_error_no($db) !=0) {
- $Title = _('Stock Check Sheets - Problem Report');
- include('includes/header.inc');
- prnMsg( _('The sales order demand quantities could not be retrieved by the SQL because') . ' ' . DB_error_msg($db), 'error');
- echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
- if ($debug==1){
- echo '<br />' . $SQL;
- }
- include('includes/footer.inc');
- exit;
- }
- $DemandRow = DB_fetch_array($DemandResult);
- $DemandQty = $DemandRow['qtydemand'];
- //Also need to add in the demand for components of assembly items
- $sql = "SELECT SUM((salesorderdetails.quantity-salesorderdetails.qtyinvoiced)*bom.quantity) AS dem
- FROM salesorderdetails INNER JOIN salesorders
- ON salesorders.orderno = salesorderdetails.orderno
- INNER JOIN bom
- ON salesorderdetails.stkcode=bom.parent
- INNER JOIN stockmaster
- ON stockmaster.stockid=bom.parent
- WHERE salesorders.fromstkloc='" . $_POST['Location'] . "'
- AND salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0
- AND bom.component='" . $InventoryCheckRow['stockid'] . "'
- AND stockmaster.mbflag='A'
- AND salesorders.quotation=0";
- $DemandResult = DB_query($sql,$db,'','',false,false);
- if (DB_error_no($db) !=0) {
- prnMsg(_('The demand for this product from') . ' ' . $myrow['loccode'] . ' ' . _('cannot be retrieved because') . ' - ' . DB_error_msg($db),'error');
- if ($debug==1){
- echo '<br />' . _('The SQL that failed was') . ' ' . $sql;
- }
- exit;
- }
- if (DB_num_rows($DemandResult)==1){
- $DemandRow = DB_fetch_row($DemandResult);
- $DemandQty += $DemandRow[0];
- }
- $LeftOvers = $pdf->addTextWrap(350,$YPos,60,$FontSize,locale_number_format($InventoryCheckRow['qoh'], $InventoryCheckRow['decimalplaces']), 'right');
- $LeftOvers = $pdf->addTextWrap(410,$YPos,60,$FontSize,locale_number_format($DemandQty,$InventoryCheckRow['decimalplaces']), 'right');
- $LeftOvers = $pdf->addTextWrap(470,$YPos,60,$FontSize,locale_number_format($InventoryCheckRow['qoh']-$DemandQty,$InventoryCheckRow['decimalplaces']), 'right');
- }
- $LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,150,$FontSize,$InventoryCheckRow['stockid'], 'left');
- $LeftOvers = $pdf->addTextWrap(150,$YPos,200,$FontSize,$InventoryCheckRow['description'], 'left');
- $pdf->line($Left_Margin, $YPos-2,$Page_Width-$Right_Margin, $YPos-2);
- if ($YPos < $Bottom_Margin + $line_height){
- $PageNumber++;
- include('includes/PDFStockCheckPageHeader.inc');
- }
- } /*end STOCK SHEETS while loop */
- $pdf->OutputD($_SESSION['DatabaseName'] . '_Stock_Count_Sheets_' . Date('Y-m-d') .'.pdf');
- } else { /*The option to print PDF was not hit */
- $Title=_('Stock Check Sheets');
- include('includes/header.inc');
- if (!isset($_POST['FromCriteria']) and !isset($_POST['ToCriteria'])) {
- /*if $FromCriteria is not set then show a form to allow input */
- echo '<p class="page_title_text noPrint" ><img src="'.$RootPath.'/css/'.$Theme.'/images/printer.png" title="'
- . _('print') . '" alt="" />' . ' ' . $Title.'</p><br />';
- echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post" class="noPrint">';
- echo '<div>';
- echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
- echo '<table class="selection">';
- echo '<tr><td>' . _('From Inventory Category Code') . ':</td>
- <td><select name="FromCriteria">';
- $sql="SELECT categoryid, categorydescription FROM stockcategory ORDER BY categorydescription";
- $CatResult= DB_query($sql,$db);
- while ($myrow = DB_fetch_array($CatResult)){
- echo '<option value="' . $myrow['categoryid'] . '">' . $myrow['categorydescription'] . ' - ' . $myrow['categoryid'] . '</option>';
- }
- echo '</select></td></tr>';
- echo '<tr>
- <td>' . _('To Inventory Category Code') . ':</td>
- <td><select name="ToCriteria">';
- /*Set the index for the categories result set back to 0 */
- DB_data_seek($CatResult,0);
- while ($myrow = DB_fetch_array($CatResult)){
- echo '<option value="' . $myrow['categoryid'] . '">' . $myrow['categorydescription'] . ' - ' . $myrow['categoryid'] . '</option>';
- }
- echo '</select></td></tr>';
- echo '<tr>
- <td>' . _('For Inventory in Location') . ':</td>
- <td><select name="Location">';
- $sql = "SELECT loccode, locationname FROM locations ORDER BY locationname";
- $LocnResult=DB_query($sql,$db);
- while ($myrow=DB_fetch_array($LocnResult)){
- echo '<option value="' . $myrow['loccode'] . '">' . $myrow['locationname'] . '</option>';
- }
- echo '</select>
- </td>
- </tr>';
- echo '<tr>
- <td>' . _('Action for Stock Check Freeze') . ':</td>
- <td><select name="MakeStkChkData">';
- if (!isset($_POST['MakeStkChkData'])){
- $_POST['MakeStkChkData'] = 'PrintOnly';
- }
- if ($_POST['MakeStkChkData'] =='New'){
- echo '<option selected="selected" value="New">' . _('Make new stock check data file') . '</option>';
- } else {
- echo '<option value="New">' . _('Make new stock check data file') . '</option>';
- }
- if ($_POST['MakeStkChkData'] =='AddUpdate'){
- echo '<option selected="selected" value="AddUpdate">' . _('Add/update existing stock check file') . '</option>';
- } else {
- echo '<option value="AddUpdate">' . _('Add/update existing stock check file') . '</option>';
- }
- if ($_POST['MakeStkChkData'] =='PrintOnly'){
- echo '<option selected="selected" value="PrintOnly">' . _('Print Stock Check Sheets Only') . '</option>';
- } else {
- echo '<option value="PrintOnly">' . _('Print Stock Check Sheets Only') . '</option>';
- }
- echo '</select></td></tr>';
- echo '<tr>
- <td>' . _('Show system quantity on sheets') . ':</td>
- <td>';
- if (isset($_POST['ShowInfo']) and $_POST['ShowInfo'] == false){
- echo '<input type="checkbox" name="ShowInfo" value="false" />';
- } else {
- echo '<input type="checkbox" name="ShowInfo" value="true" />';
- }
- echo '</td>
- </tr>';
- echo '<tr>
- <td>' . _('Only print items with non zero quantities') . ':</td>
- <td>';
- if (isset($_POST['NonZerosOnly']) and $_POST['NonZerosOnly'] == false){
- echo '<input type="checkbox" name="NonZerosOnly" value="false" />';
- } else {
- echo '<input type="checkbox" name="NonZerosOnly" value="true" />';
- }
- echo '</td>
- </tr>
- </table>
- <br />
- <div class="centre">
- <input type="submit" name="PrintPDF" value="' . _('Print and Process') . '" />
- </div>
- </div>
- </form>';
- }
- include('includes/footer.inc');
- } /*end of else not PrintPDF */
- ?>