From: <dai...@us...> - 2011-05-29 09:38:53
|
Revision: 4581 http://web-erp.svn.sourceforge.net/web-erp/?rev=4581&view=rev Author: daintree Date: 2011-05-29 09:38:46 +0000 (Sun, 29 May 2011) Log Message: ----------- various Modified Paths: -------------- trunk/PDFTopItems.php trunk/PricesByCost.php trunk/Stocks.php trunk/TopItems.php trunk/doc/Change.log Added Paths: ----------- trunk/sql/mysql/upgrade4.04-4.04.1.sql Removed Paths: ------------- trunk/companies/weberpdemo/part_pics/HON90021.jpg trunk/companies/weberpdemo/part_pics/HON90022.jpg trunk/companies/weberpdemo/part_pics/PHIS01.jpg Modified: trunk/PDFTopItems.php =================================================================== --- trunk/PDFTopItems.php 2011-05-29 04:45:11 UTC (rev 4580) +++ trunk/PDFTopItems.php 2011-05-29 09:38:46 UTC (rev 4581) @@ -1,5 +1,5 @@ <?php -/* $Revision: 1.2 $ */ + /* $Id$*/ include ('includes/session.inc'); @@ -18,14 +18,15 @@ SUM(salesorderdetails.qtyinvoiced) totalinvoiced, SUM(salesorderdetails.qtyinvoiced * salesorderdetails.unitprice ) AS valuesales, stockmaster.description, - stockmaster.units + stockmaster.units, + stockmaster.decimalplaces FROM salesorderdetails, salesorders, debtorsmaster,stockmaster WHERE salesorderdetails.orderno = salesorders.orderno AND salesorderdetails.stkcode = stockmaster.stockid AND salesorders.debtorno = debtorsmaster.debtorno AND salesorderdetails.actualdispatchdate >='" . $FromDate . "' GROUP BY salesorderdetails.stkcode - ORDER BY '" . $_GET['Sequence'] . "' DESC + ORDER BY " . $_GET['Sequence'] . " DESC LIMIT " . $_GET['NumberOfTopItems'] ; } else { //the situation if only location type selected "All" if ($_GET['Location'] == 'All') { @@ -41,7 +42,7 @@ AND debtorsmaster.typeid = '" . $_GET['Customers'] . "' AND salesorderdetails.ActualDispatchDate >= '" . $FromDate . "' GROUP BY salesorderdetails.stkcode - ORDER BY '" . $_GET['Sequence'] . "' DESC + ORDER BY " . $_GET['Sequence'] . " DESC LIMIT " . $_GET['NumberOfTopItems']; } else { //the situation if the customer type selected "All" @@ -50,7 +51,8 @@ SUM(salesorderdetails.qtyinvoiced) totalinvoiced, SUM(salesorderdetails.qtyinvoiced * salesorderdetails.unitprice ) AS valuesales, stockmaster.description, - stockmaster.units + stockmaster.units, + stockmaster.decimalplaces FROM salesorderdetails, salesorders, debtorsmaster,stockmaster WHERE salesorderdetails.orderno = salesorders.orderno AND salesorderdetails.stkcode = stockmaster.stockid @@ -58,7 +60,7 @@ AND salesorders.fromstkloc = '" . $_GET['Location'] . "' AND salesorderdetails.ActualDispatchDate >= '" . $FromDate . "' GROUP BY salesorderdetails.stkcode - ORDER BY '" . $_GET['Sequence'] . "' DESC + ORDER BY " . $_GET['Sequence'] . " DESC LIMIT 0," . $_GET['NumberOfTopItems']; } else { //the situation if the location and customer type not selected "All" @@ -66,7 +68,8 @@ SUM(salesorderdetails.qtyinvoiced) totalinvoiced, SUM(salesorderdetails.qtyinvoiced * salesorderdetails.unitprice ) AS valuesales, stockmaster.description, - stockmaster.units + stockmaster.units, + stockmaster.decimalplaces FROM salesorderdetails, salesorders, debtorsmaster,stockmaster WHERE salesorderdetails.orderno = salesorders.orderno AND salesorderdetails.stkcode = stockmaster.stockid @@ -75,57 +78,43 @@ AND debtorsmaster.typeid = '" . $_GET['Customers'] . "' AND salesorderdetails.actualdispatchdate >= '" . $FromDate . "' GROUP BY salesorderdetails.stkcode - ORDER BY '" . $_GET['Sequence'] . "' DESC + ORDER BY " . $_GET['Sequence'] . " DESC LIMIT " . $_GET['NumberOfTopItems']; } } } $result = DB_query($SQL, $db); - -$YPos = $YPos - 6; -while ($myrow = DB_fetch_array($result)) { - //find the quantity onhand item - $sqloh = "SELECT sum(quantity)as qty - FROM `locstock` - WHERE stockid='" . $myrow['0'] . "'"; - $oh = db_query($sqloh, $db); - $ohRow = db_fetch_row($oh); - $LeftOvers = $pdf->addTextWrap($Left_Margin + 1, $YPos, 300 - $Left_Margin, $FontSize, $myrow['stkcode']); - $LeftOvers = $pdf->addTextWrap($Left_Margin + 100, $YPos, 270 - $Left_Margin, $FontSize, $myrow['description']); - $LeftOvers = $pdf->addTextWrap($Left_Margin + 330, $YPos, 30, $FontSize, $myrow['totalinvoiced'], 'right'); - $LeftOvers = $pdf->addTextWrap($Left_Margin + 370, $YPos, 300 - $Left_Margin, $FontSize, $myrow['units'], 'left'); - $LeftOvers = $pdf->addTextWrap($Left_Margin + 400, $YPos, 70, $FontSize, number_format($myrow['valuesales'], 2), 'right'); - $LeftOvers = $pdf->addTextWrap($Left_Margin + 490, $YPos, 30, $FontSize, $ohRow[0], 'right'); - if (strlen($LeftOvers) > 1) { - $LeftOvers = $pdf->addTextWrap($Left_Margin + 1 + 94, $YPos - $line_height, 270, $FontSize, $LeftOvers, 'left'); +if (DB_num_rows($result)>0){ + $YPos = $YPos - 6; + while ($myrow = DB_fetch_array($result)) { + //find the quantity onhand item + $sqloh = "SELECT sum(quantity)as qty + FROM locstock + WHERE stockid='" . $myrow['stkcode'] . "'"; + $oh = db_query($sqloh, $db); + $ohRow = db_fetch_row($oh); + $LeftOvers = $pdf->addTextWrap($Left_Margin + 1, $YPos, 80, $FontSize, $myrow['stkcode']); + $LeftOvers = $pdf->addTextWrap($Left_Margin + 100, $YPos, 100, $FontSize, $myrow['description']); + $LeftOvers = $pdf->addTextWrap($Left_Margin + 330, $YPos, 30, $FontSize, number_format($myrow['totalinvoiced'],$myrow['decimalplaces']), 'right'); + $LeftOvers = $pdf->addTextWrap($Left_Margin + 370, $YPos, 300 - $Left_Margin, $FontSize, $myrow['units'], 'left'); + $LeftOvers = $pdf->addTextWrap($Left_Margin + 400, $YPos, 70, $FontSize, number_format($myrow['valuesales'], $_SESSION['CompanyRecord']['decimalplaces']), 'right'); + $LeftOvers = $pdf->addTextWrap($Left_Margin + 490, $YPos, 30, $FontSize, number_format($ohRow[0],$myrow['decimalplaces']), 'right'); + if (strlen($LeftOvers) > 1) { + $LeftOvers = $pdf->addTextWrap($Left_Margin + 1 + 94, $YPos - $line_height, 270, $FontSize, $LeftOvers, 'left'); + $YPos-= $line_height; + } + if ($YPos - $line_height <= $Bottom_Margin) { + /* We reached the end of the page so finish off the page and start a newy */ + $PageNumber++; + include ('includes/PDFTopItemsHeader.inc'); + $FontSize = 10; + } //end if need a new page headed up + /*increment a line down for the next line item */ $YPos-= $line_height; } - if ($YPos - $line_height <= $Bottom_Margin) { - /* We reached the end of the page so finish off the page and start a newy */ - $PageNumber++; - include ('includes/PDFTopItemsHeader.inc'); - $FontSize = 10; - } //end if need a new page headed up - /*increment a line down for the next line item */ - $YPos-= $line_height; + + $pdf->OutputD($_SESSION['DatabaseName'] . '_TopItemsListing_' . date('Y-m-d').'.pdf'); + $pdf->__destruct(); } -$pdfcode = $pdf->output(); -$len = strlen($pdfcode); -if (DB_num_rows($result) == 0) { - $title = _('Print Price List Error'); - include ('includes/header.inc'); - prnMsg(_('There were no records returned '), 'warn'); - echo '<br><a href="' . $rootpath . '/index.php">' . _('Back to the menu') . '</a>'; - include ('includes/footer.inc'); - exit; -} else { - header('Content-type: application/pdf'); - header('Content-Length: ' . $len); - header('Content-Disposition: inline; filename=TopItems.pdf'); - header('Expires: 0'); - header('Cache-Control: must-revalidate, post-check=0, pre-check=0'); - header('Pragma: public'); - $pdf->Output('TopItems.pdf', 'I'); -} /*end of else not PrintPDF */ ?> Modified: trunk/PricesByCost.php =================================================================== --- trunk/PricesByCost.php 2011-05-29 04:45:11 UTC (rev 4580) +++ trunk/PricesByCost.php 2011-05-29 09:38:46 UTC (rev 4581) @@ -7,7 +7,7 @@ echo '<p class="page_title_text"><img src="' . $rootpath . '/css/' . $theme . '/images/inventory.png" title="' . _('Inventory') . '" alt="" />' . ' ' . _('Update Price By Cost') . '</p>'; -if (isset($_POST['submit']) or isset($_POST['update'])) { +if (isset($_POST['submit']) OR isset($_POST['update'])) { if ($_POST['Margin'] == '') { header('Location: PricesByCost.php'); } @@ -27,12 +27,19 @@ prices.debtorno, prices.branchcode, (stockmaster.materialcost + stockmaster.labourcost + stockmaster.overheadcost) as cost, - prices.price as price, prices.debtorno as customer, prices.branchcode as branch, + prices.price as price, + prices.debtorno AS customer, + prices.branchcode AS branch, prices.startdate, - prices.enddate - FROM stockmaster, prices - WHERE stockmaster.stockid=prices.stockid" . $Category . " - AND stockmaster.discontinued = 0 + prices.enddate, + currencies.decimalplaces, + currencies.rate + FROM stockmaster INNER JOIN prices + ON stockmaster.stockid=prices.stockid + INNER JOIN currencies + ON prices.currabrev=currencies.currabrev + WHERE stockmaster.discontinued = 0 + " . $Category . " AND prices.price" . $Comparator . "(stockmaster.materialcost + stockmaster.labourcost + stockmaster.overheadcost) * '" . $_POST['Margin'] . "' AND prices.typeabbrev ='" . $_POST['SalesType'] . "' AND prices.currabrev ='" . $_POST['CurrCode'] . "' @@ -46,44 +53,54 @@ while ($myrow = DB_fetch_array($result)) { $SQLTestExists = "SELECT price FROM prices - WHERE stockid = '" . $_POST['StockID_' . $PriceCounter] . "' - AND prices.typeabbrev ='" . $_POST['SalesType'] . "' - AND prices.currabrev ='" . $_POST['CurrCode'] . "' - AND prices.debtorno ='" . $_POST['DebtorNo_' . $PriceCounter] . "' - AND prices.branchcode ='" . $_POST['BranchCode_' . $PriceCounter] . "' - AND prices.startdate ='" . date('Y-m-d') . "'"; + WHERE stockid = '" . $_POST['StockID_' . $PriceCounter] . "' + AND prices.typeabbrev ='" . $_POST['SalesType'] . "' + AND prices.currabrev ='" . $_POST['CurrCode'] . "' + AND prices.debtorno ='" . $_POST['DebtorNo_' . $PriceCounter] . "' + AND prices.branchcode ='" . $_POST['BranchCode_' . $PriceCounter] . "' + AND prices.startdate ='" . date('Y-m-d') . "'"; $TestExistsResult = DB_query($SQLTestExists,$db); if (DB_num_rows($TestExistsResult)==1){ //then we are updating - $SQLUpdate = "UPDATE prices SET price = '" . $_POST['Price_' . $PriceCounter] . "' - WHERE stockid = '" . $_POST['StockID_' . $PriceCounter] . "' - AND prices.typeabbrev ='" . $_POST['SalesType'] . "' - AND prices.currabrev ='" . $_POST['CurrCode'] . "' - AND prices.debtorno ='" . $_POST['DebtorNo_' . $PriceCounter] . "' - AND prices.branchcode ='" . $_POST['BranchCode_' . $PriceCounter] . "' - AND prices.startdate ='" . date('Y-m-d') . "' - AND prices.enddate ='" . $_POST['EndDate_' . $PriceCounter] . "'"; + $SQLUpdate = "UPDATE prices SET price = '" . $_POST['Price_' . $PriceCounter] . "' + WHERE stockid = '" . $_POST['StockID_' . $PriceCounter] . "' + AND prices.typeabbrev ='" . $_POST['SalesType'] . "' + AND prices.currabrev ='" . $_POST['CurrCode'] . "' + AND prices.debtorno ='" . $_POST['DebtorNo_' . $PriceCounter] . "' + AND prices.branchcode ='" . $_POST['BranchCode_' . $PriceCounter] . "' + AND prices.startdate ='" . date('Y-m-d') . "' + AND prices.enddate ='" . $_POST['EndDate_' . $PriceCounter] . "'"; $ResultUpdate = DB_query($SQLUpdate, $db); } else { - //we need to add a new price from today - $SQLInsert = "INSERT INTO prices ( - stockid, - price, - typeabbrev, - currabrev, - debtorno, - branchcode, - startdate - ) VALUES ( - '" . $_POST['StockID_' . $PriceCounter] . "', - '" . $_POST['Price_' . $PriceCounter] . "', - '" . $_POST['SalesType'] . "', - '" . $_POST['CurrCode'] . "', - '" . $_POST['DebtorNo_' . $PriceCounter] . "', - '" . $_POST['BranchCode_' . $PriceCounter] . "', - '" . date('Y-m-d') . "' - )"; - $ResultInsert = DB_query($SQLInsert, $db); + //update the old price to have an end date of yesterday too + $SQLUpdate = "UPDATE prices SET enddate = '" . FormatDateForSQL(DateAdd(Date($_SESSION['DefaultDateFormat']),'d',-1)) . "' + WHERE stockid = '" . $_POST['StockID_' . $PriceCounter] . "' + AND prices.typeabbrev ='" . $_POST['SalesType'] . "' + AND prices.currabrev ='" . $_POST['CurrCode'] . "' + AND prices.debtorno ='" . $_POST['DebtorNo_' . $PriceCounter] . "' + AND prices.branchcode ='" . $_POST['BranchCode_' . $PriceCounter] . "' + AND prices.startdate ='" . $_POST['StartDate_' . $PriceCounter] . "' + AND prices.enddate ='" . $_POST['EndDate_' . $PriceCounter] . "'"; + $Result = DB_query($SQLUpdate, $db); + //we need to add a new price from today + $SQLInsert = "INSERT INTO prices ( stockid, + price, + typeabbrev, + currabrev, + debtorno, + branchcode, + startdate + ) VALUES ( + '" . $_POST['StockID_' . $PriceCounter] . "', + '" . $_POST['Price_' . $PriceCounter] . "', + '" . $_POST['SalesType'] . "', + '" . $_POST['CurrCode'] . "', + '" . $_POST['DebtorNo_' . $PriceCounter] . "', + '" . $_POST['BranchCode_' . $PriceCounter] . "', + '" . date('Y-m-d') . "' + )"; + $ResultInsert = DB_query($SQLInsert, $db); + } $PriceCounter++; }//end while loop @@ -159,12 +176,12 @@ <input type="hidden" value=' . $myrow['enddate'] . ' name="EndDate_' . $PriceCounter .'">'; //variable for current margin if ($myrow['price'] != 0){ - $CurrentGP = ($myrow['price']-$Cost)*100 / $myrow['price']; + $CurrentGP = (($myrow['price']/$myrow['rate'])-$Cost)*100 / ($myrow['price']/$myrow['rate']); } else { $CurrentGP = 0; } //variable for proposed - $Proposed = $Cost * $_POST['Margin']; + $ProposedPrice = $Cost * $_POST['Margin']; if ($myrow['enddate']=='0000-00-00'){ $EndDateDisplay = _('No End Date'); } else { @@ -176,16 +193,16 @@ <td>' . $myrow['branch'] . '</td> <td>' . ConvertSQLDate($myrow['startdate']) . '</td> <td>' . $EndDateDisplay . '</td> - <td class="number">' . number_format($Cost, 2) . '</td> + <td class="number">' . number_format($Cost, $_SESSION['CompanyRecord']['decimalplaces']) . '</td> <td class="number">' . number_format($CurrentGP, 1) . '%</td> - <td class="number">' . number_format($Proposed, 2) . '</td> + <td class="number">' . number_format($ProposedPrice, $myrow['decimalplaces']) . '</td> <td><input type="text" class="number" name="Price_' . $PriceCounter . '" maxlength=14 size=10 value="' . $myrow['price'] . '"></td> </tr> '; $PriceCounter++; } //end of looping echo '<tr> - <td style="text-align:right" colspan=4><input type=submit name=submit value=' . _('Update') . '></td> - <td style="text-align:left" colspan=3><a href="' . $_SERVER['PHP_SELF'] . '"><input type=submit value=' . _('Back') . '><a/></td> + <td style="text-align:right" colspan=4><input type="submit" name="submit" value=' . _('Update') . ' onclick="return confirm(\'' . _('If the prices above do not have a commencement date as today, this will create new prices with commencement date of today at the entered figures and update the existing prices with historical start dates to have an end date of yesterday. Are You Sure?') . '\');"></td> + <td style="text-align:left" colspan=3><a href="' . $_SERVER['PHP_SELF'] . '"><input type="submit" value="' . _('Back') . '"><a/></td> </tr></form>'; } else { prnMsg(_('There were no prices meeting the criteria specified to review'),'info'); @@ -196,9 +213,9 @@ echo '</br></br><form action="' . $_SERVER['PHP_SELF'] . '" method="post"><table class=selection>'; echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; - $SQL = 'SELECT categoryid, categorydescription + $SQL = "SELECT categoryid, categorydescription FROM stockcategory - ORDER BY categorydescription'; + ORDER BY categorydescription"; $result1 = DB_query($SQL, $db); echo '<tr> <td>' . _('Category') . ':</td> @@ -221,7 +238,7 @@ $_POST['Margin']=1; } echo '<td><input type="text" class="number" name="Margin" MAXLENGTH =8 size=8 value=' .$_POST['Margin'] . '></td></tr>'; - $result = DB_query('SELECT typeabbrev, sales_type FROM salestypes ', $db); + $result = DB_query("SELECT typeabbrev, sales_type FROM salestypes", $db); echo '<tr><td>' . _('Sales Type') . '/' . _('Price List') . ':</td> <td><select name="SalesType">'; while ($myrow = DB_fetch_array($result)) { @@ -232,7 +249,7 @@ } } //end while loop DB_data_seek($result, 0); - $result = DB_query('SELECT currency, currabrev FROM currencies', $db); + $result = DB_query("SELECT currency, currabrev FROM currencies", $db); echo '</select></td></tr> <tr><td>' . _('Currency') . ':</td> <td><select name="CurrCode">'; Modified: trunk/Stocks.php =================================================================== --- trunk/Stocks.php 2011-05-29 04:45:11 UTC (rev 4580) +++ trunk/Stocks.php 2011-05-29 09:38:46 UTC (rev 4581) @@ -692,7 +692,7 @@ $teller++; } } - $mydir .= ""; + $mydir .= ''; } return $mydir; } @@ -713,7 +713,7 @@ '" >'; } else { if( isset($StockID) and file_exists($_SESSION['part_pics_dir'] . '/' .$StockID.'.jpg') ) { - $StockImgLink = '<img src="' . $_SESSION['part_pics_dir'] . '/' .$StockID.'.jpg" >'; + $StockImgLink = '<img src="GetStockImage.php?automake=1&textcolor=FFFFFF&bgcolor=CCCCCC&StockID=' . $StockID . '&text=&width=120&height=120">'; } else { $StockImgLink = _('No Image'); } Modified: trunk/TopItems.php =================================================================== --- trunk/TopItems.php 2011-05-29 04:45:11 UTC (rev 4580) +++ trunk/TopItems.php 2011-05-29 09:38:46 UTC (rev 4581) @@ -15,7 +15,7 @@ echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; echo '<table cellpadding="3" colspan="4" class="selection">'; //to view store location - echo '<tr><td width="150">' . _('Select Location') . ' </td><td>:</td><td><select name=Location>'; + echo '<tr><td width="150">' . _('Select Location') . ' </td><td>:</td><td><select name="Location">'; $sql = "SELECT loccode, locationname FROM `locations`"; @@ -43,8 +43,8 @@ echo '<tr> <td width="150">' . _('Select Order By ') . ' </td> <td>:</td> <td><select name="Sequence">'; - echo ' <option value="TotalInvoiced">' . _('Total Pieces') . '</option>'; - echo ' <option value="ValueSales">' . _('Value of Sales') . '</option>'; + echo ' <option value="totalinvoiced">' . _('Total Pieces') . '</option>'; + echo ' <option value="valuesales">' . _('Value of Sales') . '</option>'; echo ' </select></td> </tr>'; //View number of days @@ -71,8 +71,8 @@ if (($_POST['Location'] == 'All') and ($_POST['Customers'] == 'All')) { $SQL = "SELECT salesorderdetails.stkcode, - SUM(salesorderdetails.qtyinvoiced) TotalInvoiced, - SUM(salesorderdetails.qtyinvoiced * salesorderdetails.unitprice ) AS ValueSales, + SUM(salesorderdetails.qtyinvoiced) AS totalinvoiced, + SUM(salesorderdetails.qtyinvoiced * salesorderdetails.unitprice/currencies.rate ) AS valuesales, stockmaster.description, stockmaster.units, currencies.rate, @@ -85,42 +85,41 @@ AND debtorsmaster.currcode = currencies.currabrev AND salesorderdetails.actualdispatchdate >= '" . $FromDate . "' GROUP BY salesorderdetails.stkcode - ORDER BY '" . $_POST['Sequence'] . "' DESC - LIMIT " . $_POST['NumberOfTopItems'] . ""; + ORDER BY " . $_POST['Sequence'] . " DESC + LIMIT " . $_POST['NumberOfTopItems']; } else { //the situation if only location type selected "All" if ($_POST['Location'] == 'All') { - $SQL = " - SELECT salesorderdetails.stkcode, - SUM(salesorderdetails.qtyinvoiced) TotalInvoiced, - SUM(salesorderdetails.qtyinvoiced * salesorderdetails.unitprice ) AS ValueSales, + $SQL = "SELECT salesorderdetails.stkcode, + SUM(salesorderdetails.qtyinvoiced) AS totalinvoiced, + SUM(salesorderdetails.qtyinvoiced * salesorderdetails.unitprice/currencies.rate ) AS valuesales, stockmaster.description, stockmaster.units, currencies.rate, debtorsmaster.currcode, stockmaster.decimalplaces - FROM salesorderdetails, salesorders, debtorsmaster,stockmaster, currencies - WHERE salesorderdetails.orderno = salesorders.orderno + FROM salesorderdetails, salesorders, debtorsmaster,stockmaster, currencies + WHERE salesorderdetails.orderno = salesorders.orderno AND salesorderdetails.stkcode = stockmaster.stockid AND salesorders.debtorno = debtorsmaster.debtorno AND debtorsmaster.currcode = currencies.currabrev AND debtorsmaster.typeid = '" . $_POST['Customers'] . "' AND salesorderdetails.actualdispatchdate >= '" . $FromDate . "' GROUP BY salesorderdetails.stkcode - ORDER BY '" . $_POST['Sequence'] . "' DESC - LIMIT " . $_POST[NumberOfTopItems] . ""; + ORDER BY " . $_POST['Sequence'] . " DESC + LIMIT " . $_POST[NumberOfTopItems]; } else { //the situation if the customer type selected "All" if ($_POST['Customers'] == 'All') { $SQL = "SELECT salesorderdetails.stkcode, - SUM(salesorderdetails.qtyinvoiced) TotalInvoiced, - SUM(salesorderdetails.qtyinvoiced * salesorderdetails.unitprice ) AS ValueSales, + SUM(salesorderdetails.qtyinvoiced) AS totalinvoiced, + SUM(salesorderdetails.qtyinvoiced * salesorderdetails.unitprice/currencies.rate ) AS valuesales, stockmaster.description, stockmaster.units, currencies.rate, debtorsmaster.currcode, stockmaster.decimalplaces - FROM salesorderdetails, salesorders, debtorsmaster,stockmaster, currencies - WHERE salesorderdetails.orderno = salesorders.orderno + FROM salesorderdetails, salesorders, debtorsmaster,stockmaster, currencies + WHERE salesorderdetails.orderno = salesorders.orderno AND salesorderdetails.stkcode = stockmaster.stockid AND salesorders.debtorno = debtorsmaster.debtorno AND debtorsmaster.currcode = currencies.currabrev @@ -128,19 +127,19 @@ AND salesorderdetails.actualdispatchdate >= '" . $FromDate . "' GROUP BY salesorderdetails.stkcode ORDER BY " . $_POST['Sequence'] . " DESC - LIMIT " . $_POST['NumberOfTopItems'] . ""; + LIMIT " . $_POST['NumberOfTopItems']; } else { //the situation if the location and customer type not selected "All" $SQL = "SELECT salesorderdetails.stkcode, - SUM(salesorderdetails.qtyinvoiced) TotalInvoiced, - SUM(salesorderdetails.qtyinvoiced * salesorderdetails.unitprice ) AS ValueSales, + SUM(salesorderdetails.qtyinvoiced) AS totalinvoiced, + SUM(salesorderdetails.qtyinvoiced * salesorderdetails.unitprice/currencies.rate ) AS valuesales, stockmaster.description, stockmaster.units, currencies.rate, debtorsmaster.currcode, stockmaster.decimalplaces - FROM salesorderdetails, salesorders, debtorsmaster,stockmaster, currencies - WHERE salesorderdetails.orderno = salesorders.orderno + FROM salesorderdetails, salesorders, debtorsmaster,stockmaster, currencies + WHERE salesorderdetails.orderno = salesorders.orderno AND salesorderdetails.stkcode = stockmaster.stockid AND salesorders.debtorno = debtorsmaster.debtorno AND debtorsmaster.currcode = currencies.currabrev @@ -148,23 +147,25 @@ AND debtorsmaster.typeid = '" . $_POST['Customers'] . "' AND salesorderdetails.actualdispatchdate >= '" . $FromDate . "' GROUP BY salesorderdetails.stkcode - ORDER BY '" . $_POST['Sequence'] . "' DESC - LIMIT " . $_POST['NumberOfTopItems'] . ""; + ORDER BY " . $_POST['Sequence'] . " DESC + LIMIT " . $_POST['NumberOfTopItems']; } } } + $result = DB_query($SQL, $db); echo '<p class="page_title_text" align="center"><strong>' . _('Top Sales Items List') . '</strong></p>'; echo '<form action="PDFTopItems.php" method="GET"><table class="selection">'; echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; $TableHeader = '<tr><th>' . _('#') . '</th> - <th>' . _('Code') . '</th> - <th>' . _('Description') . '</th> - <th>' . _('Total Invoiced') . '</th> - <th>' . _('Units') . '</th> - <th>' . _('Value Sales') . '</th> - <th>' . _('On Hand') . '</th>'; + <th>' . _('Code') . '</th> + <th>' . _('Description') . '</th> + <th>' . _('Total Invoiced') . '</th> + <th>' . _('Units') . '</th> + <th>' . _('Value Sales') . '</th> + <th>' . _('On Hand') . '</th> + </tr>'; echo $TableHeader; echo '<input type="hidden" value=' . $_POST['Location'] . ' name="Location" /> <input type="hidden" value=' . $_POST['Sequence'] . ' name="Sequence" /> @@ -175,11 +176,12 @@ $i = 1; while ($myrow = DB_fetch_array($result)) { //find the quantity onhand item - $sqloh = "SELECT sum(quantity)as qty - FROM `locstock` - WHERE stockid='" . $myrow['0'] . "'"; - $oh = db_query($sqloh, $db); - $ohRow = db_fetch_row($oh); + $sqloh = "SELECT sum(quantity) AS qty + FROM locstock + WHERE stockid='" . $myrow['stkcode'] . "'"; + + $oh = DB_query($sqloh, $db); + $ohRow = DB_fetch_row($oh); if ($k == 1) { echo '<tr class="EvenTableRows">'; $k = 0; @@ -188,25 +190,25 @@ $k = 1; } printf('<td class="number">%s</td> - <td>%s</font></td> - <td>%s</td> - <td class="number">%s</td> - <td>%s</td> - <td class="number">%s</td> - <td class="number">%s</td> - </tr>', - $i, - $myrow['0'], - $myrow['3'], - $myrow['1'], //total invoice here - $myrow['4'], //unit - number_format($myrow['2']/$myrow['5'],2), //value sales here - number_format($ohRow[0], $myrow['7']) //on hand - ); + <td>%s</td> + <td>%s</td> + <td class="number">%s</td> + <td>%s</td> + <td class="number">%s</td> + <td class="number">%s</td> + </tr>', + $i, + $myrow['stkcode'], + $myrow['description'], + number_format($myrow['totalinvoiced'],$myrow['decimalplaces']), //total invoice here + $myrow['units'], //unit + number_format($myrow['valuesales'],$_SESSION['CompanyRecord']['decimalplaces']), //value sales here + number_format($ohRow[0], $myrow['decimalplaces']) //on hand + ); $i++; } echo '</table>'; - echo '<br /><div class="centre"><input type=Submit Name="PrintPDF" Value="' . _('Print To PDF') . '"></div>'; + echo '<br /><div class="centre"><input type=Submit Name="PrintPDF" value="' . _('Print To PDF') . '"></div>'; echo '</form>'; } include ('includes/footer.inc'); Deleted: trunk/companies/weberpdemo/part_pics/HON90021.jpg =================================================================== (Binary files differ) Deleted: trunk/companies/weberpdemo/part_pics/HON90022.jpg =================================================================== (Binary files differ) Deleted: trunk/companies/weberpdemo/part_pics/PHIS01.jpg =================================================================== (Binary files differ) Modified: trunk/doc/Change.log =================================================================== --- trunk/doc/Change.log 2011-05-29 04:45:11 UTC (rev 4580) +++ trunk/doc/Change.log 2011-05-29 09:38:46 UTC (rev 4581) @@ -1,5 +1,7 @@ webERP Change Log +29/5/11 PricesByCost.php made it so the existing prices had end dates set as yesterday and new prices created from today +29/5/11 TopItems.php fixed sequence and birthday to script 28/5/11 Could not set controlled item batches/serial numbers on ConfirmDispatch_Invoice.php 28/5/11 Ricard PO_SelectOSPurchOrder.php was showing select location with gaps between locations - no slash before <option> fixed 28/5/11 Ricard added new field assigner to petty cash module and changes to PcTabs.php and PcAssignCashToTab.php Added: trunk/sql/mysql/upgrade4.04-4.04.1.sql =================================================================== --- trunk/sql/mysql/upgrade4.04-4.04.1.sql (rev 0) +++ trunk/sql/mysql/upgrade4.04-4.04.1.sql 2011-05-29 09:38:46 UTC (rev 4581) @@ -0,0 +1,7 @@ +INSERT INTO scripts (script, pagesecurity, description) VALUES ('SalesTopItemsInquiry.php', 2, 'Shows top selling items either by quantity or sales value by user selectable period range'); +INSERT INTO scripts (script, pagesecurity, description) VALUES ('SalesCategoryPeriodInquiry.php', 2, 'Shows sales by caetgory for a user selectable period range'); +INSERT INTO scripts (script, pagesecurity, description) VALUES ('SalesByTypePeriodInquiry.php', 2, 'Shows sales value by sales type by user selectable period range'); +ALTER TABLE `scripts` CHANGE `pagesecurity` `pagesecurity` INT( 11 ) NOT NULL DEFAULT '1'; +ALTER TABLE `pctabs` ADD `assigner` VARCHAR( 20 ) NOT NULL COMMENT 'Cash assigner for the tab' AFTER `tablimit`; +UPDATE pctabs SET assigner = authorizer; +UPDATE config SET confvalue='4.04.1' WHERE confname='VersionNumber'; \ No newline at end of file This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |