function GetExpiryDate($StockID, $LocCode, $BundleRef)
{
global $db;
$SQL = "SELECT expirationdate \n\t\t\t\tFROM stockserialitems\n\t\t\t\tWHERE stockid = '" . $StockID . "'\n\t\t\t\tAND loccode = '" . $LocCode . "'\n\t\t\t\tAND serialno = '" . $BundleRef . "'";
$Result = DB_query($SQL);
if (DB_num_rows($Result) == 0) {
return '0000-00-00';
} else {
$myrow = DB_fetch_row($Result);
return ConvertSQLDate($myrow[0]);
}
}
function Load($WONumber)
{
global $db;
$sql = "SELECT loccode,\n\t\t\t\t\t\trequiredby,\n\t\t\t\t\t\tstartdate,\n\t\t\t\t\t\tcostissued,\n\t\t\t\t\t\tclosed\n\t\t\t\t\tFROM workorders\n\t\t\t\t\tWHERE workorders.wo='" . $WONumber . "'";
$WOResult = DB_query($sql, $db);
if (DB_num_rows($WOResult) == 1) {
$myrow = DB_fetch_array($WOResult);
$this->StartDate = ConvertSQLDate($myrow['startdate']);
$this->CostIssued = $myrow['costissued'];
$this->Closed = $myrow['closed'];
$this->RequiredBy = ConvertSQLDate($myrow['requiredby']);
$this->LocationCode = $myrow['loccode'];
$this->OrderNumber = $WONumber;
$ErrMsg = _('Could not get the work order items');
$WOItemsResult = DB_query("SELECT stockid,\n\t\t\t\t\t\t\t\t\t\t\t\tqtyreqd,\n\t\t\t\t\t\t\t\t\t\t\t\tqtyrecd,\n\t\t\t\t\t\t\t\t\t\t\t\tstdcost,\n\t\t\t\t\t\t\t\t\t\t\t\tnextlotsnref\n\t\t\t\t\t\t\t\t\t\t\tFROM woitems\n\t\t\t\t\t\t\t\t\t\t\tWHERE wo='" . $WONumber . "'", $db, $ErrMsg);
$NumberOfOutputs = DB_num_rows($WOItemsResult);
$i = 1;
while ($WOItem = DB_fetch_array($WOItemsResult)) {
$this->Items[$i] = new WOItem($WOItem['stockid'], $WOItem['qtyreqd'], $WOItem['qtyrecd'], $WOItem['nextlotsnref'], $this->LocationCode, $i);
$i++;
}
$this->NumberOfItems = $i;
}
}
$LineItemsSQL = "SELECT salesorderdetails.orderlineno,\n\t\t\t\t\t\t\t\t\tsalesorderdetails.stkcode,\n\t\t\t\t\t\t\t\t\tstockmaster.description,\n\t\t\t\t\t\t\t\t\tstockmaster.volume,\n\t\t\t\t\t\t\t\t\tstockmaster.kgs,\n\t\t\t\t\t\t\t\t\tstockmaster.units,\n\t\t\t\t\t\t\t\t\tstockmaster.serialised,\n\t\t\t\t\t\t\t\t\tstockmaster.nextserialno,\n\t\t\t\t\t\t\t\t\tstockmaster.eoq,\n\t\t\t\t\t\t\t\t\tsalesorderdetails.unitprice,\n\t\t\t\t\t\t\t\t\tsalesorderdetails.quantity,\n\t\t\t\t\t\t\t\t\tsalesorderdetails.discountpercent,\n\t\t\t\t\t\t\t\t\tsalesorderdetails.actualdispatchdate,\n\t\t\t\t\t\t\t\t\tsalesorderdetails.qtyinvoiced,\n\t\t\t\t\t\t\t\t\tsalesorderdetails.narrative,\n\t\t\t\t\t\t\t\t\tsalesorderdetails.itemdue,\n\t\t\t\t\t\t\t\t\tsalesorderdetails.poline,\n\t\t\t\t\t\t\t\t\tsalesorderdetails.conversionfactor,\n\t\t\t\t\t\t\t\t\tsalesorderdetails.pricedecimals,\n\t\t\t\t\t\t\t\t\tlocstock.quantity/salesorderdetails.conversionfactor as qohatloc,\n\t\t\t\t\t\t\t\t\tstockmaster.mbflag,\n\t\t\t\t\t\t\t\t\tstockmaster.discountcategory,\n\t\t\t\t\t\t\t\t\tstockmaster.decimalplaces,\n\t\t\t\t\t\t\t\t\tstockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost AS standardcost,\n\t\t\t\t\t\t\t\t\tsalesorderdetails.completed\n\t\t\t\t\t\t\t\t\tFROM salesorderdetails INNER JOIN stockmaster\n\t\t\t\t\t\t\t\t\tON salesorderdetails.stkcode = stockmaster.stockid\n\t\t\t\t\t\t\t\t\tINNER JOIN locstock ON locstock.stockid = stockmaster.stockid\n\t\t\t\t\t\t\t\t\tWHERE locstock.loccode = '" . $myrow['fromstkloc'] . "'\n\t\t\t\t\t\t\t\t\tAND salesorderdetails.orderno ='" . $_GET['ModifyOrderNumber'] . "'\n\t\t\t\t\t\t\t\t\tORDER BY salesorderdetails.orderlineno";
$ErrMsg = _('The line items of the order cannot be retrieved because');
$LineItemsResult = DB_query($LineItemsSQL, $db, $ErrMsg);
if (DB_num_rows($LineItemsResult) > 0) {
while ($myrow = DB_fetch_array($LineItemsResult)) {
if ($myrow['completed'] == 0) {
$PropertiesSQL = "SELECT stkcatpropid,\n\t\t\t\t\t\t\t\t\t\t\t\tvalue\n\t\t\t\t\t\t\t\t\t\t\tFROM stockorderitemproperties\n\t\t\t\t\t\t\t\t\t\t\tWHERE orderno='" . $_GET['ModifyOrderNumber'] . "'\n\t\t\t\t\t\t\t\t\t\t\t\tAND orderlineno='" . $myrow['orderlineno'] . "'";
$PropertiesResult = DB_query($PropertiesSQL, $db);
if (DB_num_rows($PropertiesResult) == 0) {
$PropertiesArray = array();
} else {
while ($MyPropertiesRow = DB_fetch_array($PropertiesResult)) {
$PropertiesArray[$MyPropertiesRow['stkcatpropid']] = $MyPropertiesRow['value'];
}
}
$_SESSION['Items' . $identifier]->add_to_cart($myrow['stkcode'], $myrow['quantity'], $myrow['description'], $myrow['unitprice'], $myrow['discountpercent'], $myrow['units'], $myrow['volume'], $myrow['kgs'], $myrow['qohatloc'], $myrow['mbflag'], $myrow['actualdispatchdate'], $myrow['qtyinvoiced'], $myrow['discountcategory'], 0, 0, $myrow['serialised'], $myrow['decimalplaces'], $myrow['pricedecimals'], $myrow['narrative'], 'No', $myrow['orderlineno'], 0, ConvertSQLDate($myrow['itemdue']), $myrow['poline'], $myrow['standardcost'], $myrow['eoq'], $myrow['nextserialno'], $ExRate, $myrow['conversionfactor'], $PropertiesArray);
/*Just populating with existing order - no DBUpdates */
}
$LastLineNo = $myrow['orderlineno'];
}
/* line items from sales order details */
$_SESSION['Items' . $identifier]->LineCounter = $LastLineNo + 1;
}
//end of checks on returned data set
}
}
if (!isset($_SESSION['Items' . $identifier])) {
/* It must be a new order being created $_SESSION['Items'.$identifier] would be set up from the order
modification code above if a modification to an existing order. Also $ExistingOrder would be
set to 1. The delivery check screen is where the details of the order are either updated or
inserted depending on the value of ExistingOrder */
function ReSequenceEffectiveDates($Item, $PriceList, $CurrAbbrev, $QuantityBreak, $db)
{
/*This is quite complicated - the idea is that prices set up should be unique and there is no way two prices could be returned as valid - when getting a price in includes/GetPrice.inc the logic is to first look for a price of the salestype/currency within the effective start and end dates - then if not get the price with a start date prior but a blank end date (the default price). We would not want two prices where one price falls inside another effective date range except in the case of a blank end date - ie no end date - the default price for the currency/salestype.
I first thought that we would need to update the previous default price (blank end date), when a new default price is entered, to have an end date of the startdate of this new default price less 1 day - but this is converting a default price into a special price which could result in having two special prices over the same date range - best to leave it unchanged and use logic in the GetPrice.inc to ensure the correct default price is returned
*
* After further discussion (Ricard) if the new price has a blank end date - i.e. no end then the pre-existing price with no end date should be changed to have an end date just prior to the new default (no end date) price commencing
*/
//this is just the case where debtorno='' - see the Prices_Customer.php script for customer special prices
$SQL = "SELECT price,\r\n\t\t\t\t\t\tstartdate,\r\n\t\t\t\t\t\tenddate\r\n\t\t\t\tFROM pricematrix\r\n\t\t\t\tWHERE stockid='" . $Item . "'\r\n\t\t\t\tAND currabrev='" . $CurrAbbrev . "'\r\n\t\t\t\tAND salestype='" . $PriceList . "'\r\n\t\t\t\tAND quantitybreak='" . $QuantityBreak . "'\r\n\t\t\t\tORDER BY startdate, enddate";
$result = DB_query($SQL);
while ($myrow = DB_fetch_array($result)) {
if (isset($NextStartDate)) {
if (Date1GreaterThanDate2(ConvertSQLDate($myrow['startdate']), $NextStartDate)) {
$NextStartDate = ConvertSQLDate($myrow['startdate']);
//Only if the previous enddate is after the new start date do we need to look at updates
if (Date1GreaterThanDate2(ConvertSQLDate($EndDate), ConvertSQLDate($myrow['startdate']))) {
/*Need to make the end date the new start date less 1 day */
$SQL = "UPDATE pricematrix SET enddate = '" . FormatDateForSQL(DateAdd($NextStartDate, 'd', -1)) . "'\r\n\t\t\t\t\t\t\t\t\t\tWHERE stockid ='" . $Item . "'\r\n\t\t\t\t\t\t\t\t\t\tAND currabrev='" . $CurrAbbrev . "'\r\n\t\t\t\t\t\t\t\t\t\tAND salestype='" . $PriceList . "'\r\n\t\t\t\t\t\t\t\t\t\tAND startdate ='" . $StartDate . "'\r\n\t\t\t\t\t\t\t\t\t\tAND enddate = '" . $EndDate . "'\r\n\t\t\t\t\t\t\t\t\t\tAND quantitybreak ='" . $QuantityBreak . "'";
$UpdateResult = DB_query($SQL);
}
}
//end of if startdate after NextStartDate - we have a new NextStartDate
} else {
$NextStartDate = ConvertSQLDate($myrow['startdate']);
}
$StartDate = $myrow['startdate'];
$EndDate = $myrow['enddate'];
$Price = $myrow['price'];
}
// end of loop around all prices
}
$Tot_Val = 0;
$sql = 'SELECT tempbom.*,
stockmaster.description,
stockmaster.mbflag
FROM tempbom,stockmaster
WHERE tempbom.component = stockmaster.stockid
ORDER BY sortpart';
$result = DB_query($sql, $db);
// $fill is used to alternate between lines with transparent and painted background
$fill = false;
$pdf->SetFillColor(224, 235, 255);
while ($myrow = DB_fetch_array($result, $db)) {
$YPos -= $line_height;
$FontSize = 8;
$FormatedEffectiveAfter = ConvertSQLDate($myrow['effectiveafter']);
$FormatedEffectiveTo = ConvertSQLDate($myrow['effectiveto']);
if ($_POST['Fill'] == 'yes') {
$fill = !$fill;
}
// Parameters for addTextWrap are defined in /includes/class.pdf.php
// 1) X position 2) Y position 3) Width
// 4) Height 5) Text 6) Alignment 7) Border 8) Fill - True to use SetFillColor
// and False to set to transparent
$pdf->addTextWrap($Left_Margin + $myrow['level'] * 5, $YPos, 90, $FontSize, $myrow['component'], '', 0, $fill);
$pdf->addTextWrap(160, $YPos, 20, $FontSize, $myrow['mbflag'], '', 0, $fill);
$pdf->addTextWrap(180, $YPos, 180, $FontSize, $myrow['description'], '', 0, $fill);
$pdf->addTextWrap(360, $YPos, 30, $FontSize, $myrow['loccode'], 'right', 0, $fill);
$pdf->addTextWrap(390, $YPos, 25, $FontSize, $myrow['workcentreadded'], 'right', 0, $fill);
$pdf->addTextWrap(415, $YPos, 45, $FontSize, number_format($myrow['quantity'], 2), 'right', 0, $fill);
$pdf->addTextWrap(460, $YPos, 55, $FontSize, $FormatedEffectiveAfter, 'right', 0, $fill);
$pdf->addTextWrap(515, $YPos, 50, $FontSize, $FormatedEffectiveTo, 'right', 0, $fill);
<th>' . _('Order Date') . '</th>
<th>' . _('Delivery Date') . '</th>
<th>' . _('Order Amount') . '</th>
<th>' . _('Currency') . '</th>
</tr> ';
$k = 0;
while ($row = DB_fetch_array($SalesOrdersResult)) {
if ($k == 1) {
echo '<tr class="EvenTableRows">';
$k = 0;
} else {
echo '<tr class="OddTableRows">';
$k++;
}
$FormatedOrderValue = locale_number_format($row['ordervalue'], $row['currdecimalplaces']);
$OrderDate = ConvertSQLDate($row['orddate']);
$DelDate = ConvertSQLDate($row['deliverydate']);
$TotalSalesOrders += $row['ordervalue'];
echo ' <td> ' . $row['orderno'] . ' </td>
<td> ' . $row['name'] . ' </td>
<td>' . $OrderDate . '</td>
<td>' . $DelDate . '</td>
<td class="number">' . $FormatedOrderValue . '</td>
<td>' . $row['currcode'] . '</td>
</tr>';
}
echo '<tr>
<td colspan=3>' . _('Total') . '</td>
<td colspan=3 class="number">' . locale_number_format($TotalSalesOrders, $row['currdecimalplaces']) . '</td>
</tr>';
echo '</table>';
(stockmoves.price * ' . $ExchRate . ') AS fxprice,
stockmoves.narrative,
stockmaster.units
FROM stockmoves,
stockmaster
WHERE stockmoves.stockid = stockmaster.stockid
AND stockmoves.type=10
AND stockmoves.transno=' . $FromTransNo . '
AND stockmoves.show_on_inv_crds=1';
} else {
/* then its a credit note */
echo "<table WIDTH=50%><tr>\n\t\t\t\t \t\t<td align=left bgcolor='#BBBBBB'><b>" . _('Branch') . ":</b></td>\n\t\t\t\t\t\t</tr>";
echo "<tr>\n\t\t\t\t \t\t<td bgcolor='#EEEEEE'>" . $myrow['brname'] . '<br>' . $myrow['braddress1'] . '<br>' . $myrow['braddress2'] . '<br>' . $myrow['braddress3'] . '<br>' . $myrow['braddress4'] . '<br>' . $myrow['braddress5'] . '<br>' . $myrow['braddress6'] . '</td>
</tr></table>';
echo "<hr><table class='table1'><tr>\n\t\t\t\t \t\t<td align=left bgcolor='#BBBBBB'><b>" . _('Date') . "</b></td>\n\t\t\t\t\t\t<td align=left bgcolor='#BBBBBB'><b>" . _('Sales Person') . "</font></b></td>\n\t\t\t\t\t</tr>";
echo "<tr>\n\t\t\t\t \t\t<td bgcolor='#EEEEEE'>" . ConvertSQLDate($myrow['trandate']) . "</td>\n\t\t\t\t\t\t<td bgcolor='#EEEEEE'>" . $myrow['salesmanname'] . '</td>
</tr></table>';
$sql = 'SELECT stockmoves.stockid,
stockmaster.description,
stockmoves.qty as quantity,
stockmoves.discountpercent, ((1 - stockmoves.discountpercent) * stockmoves.price * ' . $ExchRate . ' * stockmoves.qty) AS fxnet,
(stockmoves.price * ' . $ExchRate . ') AS fxprice,
stockmaster.units
FROM stockmoves,
stockmaster
WHERE stockmoves.stockid = stockmaster.stockid
AND stockmoves.type=11
AND stockmoves.transno=' . $FromTransNo . '
AND stockmoves.show_on_inv_crds=1';
}
echo '<hr>';
$pdf->OutputD($_SESSION['DatabaseName'] . '_GL_Balance_Sheet_' . date('Y-m-d') . '.pdf');
$pdf->__destruct();
}
exit;
} else {
$ViewTopic = 'GeneralLedger';
$BookMark = 'BalanceSheet';
include 'includes/header.inc';
echo '<form onSubmit="return VerifyForm(this);" method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '">';
echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
echo '<input type="hidden" name="BalancePeriodEnd" value="' . $_POST['BalancePeriodEnd'] . '" />';
$RetainedEarningsAct = $_SESSION['CompanyRecord']['retainedearnings'];
$sql = "SELECT lastdate_in_period FROM periods WHERE periodno='" . $_POST['BalancePeriodEnd'] . "'";
$PrdResult = DB_query($sql, $db);
$myrow = DB_fetch_row($PrdResult);
$BalanceDate = ConvertSQLDate($myrow[0]);
/*Calculate B/Fwd retained earnings */
$SQL = "SELECT Sum(CASE WHEN chartdetails.period='" . $_POST['BalancePeriodEnd'] . "' THEN chartdetails.bfwd + chartdetails.actual ELSE 0 END) AS accumprofitbfwd,\n\t\t\tSum(CASE WHEN chartdetails.period='" . ($_POST['BalancePeriodEnd'] - 12) . "' THEN chartdetails.bfwd + chartdetails.actual ELSE 0 END) AS lyaccumprofitbfwd\n\t\tFROM chartmaster INNER JOIN accountgroups\n\t\tON chartmaster.group_ = accountgroups.groupname INNER JOIN chartdetails\n\t\tON chartmaster.accountcode= chartdetails.accountcode\n\t\tWHERE accountgroups.pandl=1";
$AccumProfitResult = DB_query($SQL, $db, _('The accumulated profits brought forward could not be calculated by the SQL because'));
$AccumProfitRow = DB_fetch_array($AccumProfitResult);
/*should only be one row returned */
$SQL = "SELECT accountgroups.sectioninaccounts,\n\t\t\taccountgroups.groupname,\n\t\t\taccountgroups.parentgroupname,\n\t\t\tchartdetails.accountcode,\n\t\t\tchartmaster.accountname,\n\t\t\tSum(CASE WHEN chartdetails.period='" . $_POST['BalancePeriodEnd'] . "' THEN chartdetails.bfwd + chartdetails.actual ELSE 0 END) AS balancecfwd,\n\t\t\tSum(CASE WHEN chartdetails.period='" . ($_POST['BalancePeriodEnd'] - 12) . "' THEN chartdetails.bfwd + chartdetails.actual ELSE 0 END) AS lybalancecfwd\n\t\tFROM chartmaster INNER JOIN accountgroups\n\t\tON chartmaster.group_ = accountgroups.groupname INNER JOIN chartdetails\n\t\tON chartmaster.accountcode= chartdetails.accountcode\n\t\tWHERE accountgroups.pandl=0\n\t\tGROUP BY accountgroups.groupname,\n\t\t\tchartdetails.accountcode,\n\t\t\tchartmaster.accountname,\n\t\t\taccountgroups.parentgroupname,\n\t\t\taccountgroups.sequenceintb,\n\t\t\taccountgroups.sectioninaccounts\n\t\tORDER BY accountgroups.sectioninaccounts,\n\t\t\taccountgroups.sequenceintb,\n\t\t\taccountgroups.groupname,\n\t\t\tchartdetails.accountcode";
$AccountsResult = DB_query($SQL, $db, _('No general ledger accounts were returned by the SQL because'));
echo '<p class="page_title_text noPrint" ><img src="' . $RootPath . '/css/' . $Theme . '/images/preview.gif" title="' . _('HTML View') . '" alt="' . _('HTML View') . '" /> ' . _('HTML View') . '</p>';
echo '<div class="invoice">
<table class="selection" summary="' . _('HTML View') . '">
<tr>
<th colspan="6">
<h2>' . _('Balance Sheet as at') . ' ' . $BalanceDate . '
<img src="' . $RootPath . '/css/' . $Theme . '/images/printer.png" class="PrintIcon noPrint" title="' . _('Print') . '" alt="' . _('Print') . '" onclick="window.print();" />
</h2>
$LocRow = DB_fetch_array($LocResult);
echo '<table class="selection">';
echo '<tr><th colspan="4" class="header">' . _('Pending Transfers Into') . ' ' . $LocRow['locationname'] . '</th></tr>';
echo '<tr>
<th>' . _('Transfer Ref') . '</th>
<th>' . _('Transfer From') . '</th>
<th>' . _('Dispatch Date') . '</th>
</tr>';
$k = 0;
while ($myrow = DB_fetch_array($TrfResult)) {
if ($k == 1) {
echo '<tr class="EvenTableRows">';
$k = 0;
} else {
echo '<tr class="OddTableRows">';
$k++;
}
echo '<td class="number">' . $myrow['reference'] . '</td>
<td>' . $myrow['trffromloc'] . '</td>
<td>' . ConvertSQLDate($myrow['shipdate']) . '</td>
<td><a href="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '?Trf_ID=' . $myrow['reference'] . '">' . _('Receive') . '</a></td></tr>';
}
echo '</table>';
} else {
if (!isset($_POST['ProcessTransfer'])) {
prnMsg(_('There are no incoming transfers to this location'), 'info');
}
}
echo '</form>';
}
include 'includes/footer.inc';
if ($WORow['closed'] == 1) {
prnMsg(_('The selected work order has been closed and variances calculated and posted. No more receipts of manufactured items can be received against this work order. You should make up a new work order to receive this item against.'), 'info');
include 'includes/footer.inc';
exit;
}
if (!isset($_POST['ReceivedDate'])) {
$_POST['ReceivedDate'] = Date($_SESSION['DefaultDateFormat']);
}
echo '<table cellpadding="2" class="selection">
<tr><td>' . _('Receive work order') . ':</td>
<td>' . $_POST['WO'] . '</td><td>' . _('Item') . ':</td>
<td>' . $_POST['StockID'] . ' - ' . $WORow['description'] . '</td></tr>
<tr><td>' . _('Manufactured at') . ':</td>
<td>' . $WORow['locationname'] . '</td>
<td>' . _('Required By') . ':</td>
<td>' . ConvertSQLDate($WORow['requiredby']) . '</td></tr>
<tr><td>' . _('Quantity Ordered') . ':</td>
<td class="number">' . locale_number_format($WORow['qtyreqd'], $WORow['decimalplaces']) . '</td>
<td colspan="2">' . $WORow['units'] . '</td></tr>
<tr><td>' . _('Already Received') . ':</td>
<td class="number">' . locale_number_format($WORow['qtyrecd'], $WORow['decimalplaces']) . '</td>
<td colspan="2">' . $WORow['units'] . '</td></tr>
<tr><td>' . _('Date Received') . ':</td>
<td>' . Date($_SESSION['DefaultDateFormat']) . '</td>
<td>' . _('Received Into') . ':</td><td>
<select name="IntoLocation">';
if (!isset($_POST['IntoLocation'])) {
$_POST['IntoLocation'] = $WORow['loccode'];
}
$LocResult = DB_query("SELECT loccode, locationname FROM locations", $db);
while ($LocRow = DB_fetch_array($LocResult)) {
$CurrCode = $PriceList['currabrev'];
$FontSize = 8;
}
if ($Category != $PriceList['categoryid']) {
$FontSize = 10;
$YPos -= 2 * $line_height;
$LeftOvers = $pdf->addTextWrap($Left_Margin, $YPos, 300 - $Left_Margin, $FontSize, $PriceList['categoryid'] . ' - ' . $PriceList['categorydescription']);
$Category = $PriceList['categoryid'];
$CategoryName = $PriceList['categorydescription'];
$FontSize = 8;
}
$YPos -= $line_height;
$LeftOvers = $pdf->addTextWrap($Left_Margin, $YPos, 80, $FontSize, $PriceList['stockid']);
$LeftOvers = $pdf->addTextWrap($Left_Margin + 100, $YPos, 47, $FontSize, ConvertSQLDate($PriceList['startdate']));
if ($PriceList['enddate'] != '0000-00-00') {
$DisplayEndDate = ConvertSQLDate($PriceList['enddate']);
} else {
$DisplayEndDate = _('No End Date');
}
$LeftOvers = $pdf->addTextWrap($Left_Margin + 100 + 47, $YPos, 47, $FontSize, $DisplayEndDate);
$LeftOvers = $pdf->addTextWrap($Left_Margin + 130 + 47 + 47, $YPos, 130, $FontSize, $PriceList['description']);
$DisplayUnitPrice = locale_number_format($PriceList['price'], $PriceList['decimalplaces']);
$LeftOvers = $pdf->addTextWrap($Left_Margin + 110 + 47 + 47 + 130, $YPos, 180, $FontSize, $DisplayUnitPrice, 'right');
if ($PriceList['price'] != 0) {
$DisplayGPPercent = locale_number_format(($PriceList['price'] - $PriceList['standardcost']) * 100 / $PriceList['price'], 1) . '%';
} else {
$DisplayGPPercent = 0;
}
if ($_POST['ShowGPPercentages'] == 'Yes') {
$LeftOvers = $pdf->addTextWrap($Left_Margin + 135 + 47 + 47 + 130 + 65, $YPos, 20, $FontSize, $DisplayGPPercent, 'right');
}
echo $TableHeader;
/* show the GRNs outstanding to be invoiced that could be reversed */
$RowCounter = 0;
$k = 0;
while ($myrow = DB_fetch_array($result)) {
if ($k == 1) {
echo '<tr class="EvenTableRows">';
$k = 0;
} else {
echo '<tr class="OddTableRows">';
$k = 1;
}
$DisplayQtyRecd = locale_number_format($myrow['qtyrecd'], $myrow['decimalplaces']);
$DisplayQtyInv = locale_number_format($myrow['quantityinv'], $myrow['decimalplaces']);
$DisplayQtyRev = locale_number_format($myrow['qtytoreverse'], $myrow['decimalplaces']);
$DisplayDateDel = ConvertSQLDate($myrow['deliverydate']);
$LinkToRevGRN = '<a href="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '?GRNNo=' . $myrow['grnno'] . '">' . _('Reverse') . '</a>';
printf('<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td class="number">%s</td>
<td class="number">%s</td>
<td class="number">%s</td>
<td>%s</td>
</tr>', $myrow['grnno'], $myrow['itemcode'], $myrow['itemdescription'], $DisplayDateDel, $DisplayQtyRecd, $DisplayQtyInv, $DisplayQtyRev, $LinkToRevGRN);
$RowCounter++;
if ($RowCounter > 20) {
$RowCounter = 0;
echo $TableHeader;
}
echo '<input type="hidden" name="StockID' . $i . '" value="' . $MyItemRow['stockid'] . '" />';
echo '<input type="hidden" name="ItemDescription' . $i . '" value="' . $MyItemRow['description'] . '" />';
echo '<td>' . $MyItemRow['suppliers_partno'] . '</td>';
echo '<td class="number">' . locale_number_format($MyItemRow['quantity'], $MyItemRow['decimalplaces']) . '</td>';
echo '<td>' . $MyItemRow['units'] . '</td>';
echo '<td>' . ConvertSQLDate($MyItemRow['requiredbydate']) . '</td>';
if ($MyItemRow['suppliersuom'] == '') {
$MyItemRow['suppliersuom'] = $MyItemRow['units'];
}
echo '<td><input type="text" class="number" size="10" name="Qty' . $i . '" value="' . locale_number_format($MyItemRow['quantity'], $MyItemRow['decimalplaces']) . '" /></td>';
echo '<input type="hidden" name="UOM' . $i . '" value="' . $MyItemRow['units'] . '" />';
echo '<input type="hidden" name="DecimalPlaces' . $i . '" value="' . $MyItemRow['decimalplaces'] . '" />';
echo '<td>' . $MyItemRow['suppliersuom'] . '</td>';
echo '<td>' . $myrow['currcode'] . '</td>';
echo '<td><input type="text" class="number" size="10" name="Price' . $i . '" value="0.00" /></td>';
echo '<td><input type="text" class="date" alt="' . $_SESSION['DefaultDateFormat'] . '" name="RequiredByDate' . $i . '" size="11" value="' . ConvertSQLDate($MyItemRow['requiredbydate']) . '" /></td>';
$i++;
}
echo '</form>';
}
echo '</table>';
}
if (isset($_POST['Search'])) {
/*ie seach for stock items */
echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'] . '?identifier=' . $identifier) . '">';
echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
echo '<p class="page_title_text"><img src="' . $rootpath . '/css/' . $theme . '/images/supplier.png" title="' . _('Tenders') . '" alt="" />' . ' ' . _('Select items to offer from') . ' ' . $Supplier . '</p>';
if ($_POST['Keywords'] and $_POST['StockCode']) {
prnMsg(_('Stock description keywords have been used in preference to the Stock code extract entered'), 'info');
}
if ($_POST['Keywords']) {
<td>' . _('Return Covering') . ':</td>
<td><select name="NoOfPeriods">
<option value="1">' . _('One Month') . '</option>' . '<option selected="selected" value="2">' . _('Two Months') . '</option>' . '<option value="3">' . _('Quarter') . '</option>' . '<option value="6">' . _('Six Months') . '</option>' . '</select></td>
</tr>';
echo '<tr>
<td>' . _('Return To') . ':</td>
<td><select name="ToPeriod">';
$DefaultPeriod = GetPeriod(Date($_SESSION['DefaultDateFormat'], Mktime(0, 0, 0, Date('m'), 0, Date('Y'))), $db);
$sql = "SELECT periodno,\n\t\t\tlastdate_in_period\n\t\tFROM periods";
$ErrMsg = _('Could not retrieve the period data because');
$Periods = DB_query($sql, $db, $ErrMsg);
while ($myrow = DB_fetch_array($Periods, $db)) {
if ($myrow['periodno'] == $DefaultPeriod) {
echo '<option selected="selected" value="' . $myrow['periodno'] . '">' . ConvertSQLDate($myrow['lastdate_in_period']) . '</option>';
} else {
echo '<option value="' . $myrow['periodno'] . '">' . ConvertSQLDate($myrow['lastdate_in_period']) . '</option>';
}
}
echo '</select></td>
</tr>';
echo '<tr>
<td>' . _('Detail Or Summary Only') . ':</td>
<td><select name="DetailOrSummary">
<option value="Detail">' . _('Detail and Summary') . '</option>
<option selected="selected" value="Summary">' . _('Summary Only') . '</option>
</select></td>
</tr>';
echo '</table>
<br />
<div class="centre">
<input type="submit" name="PrintPDF" value="' . _('Print PDF') . '" />
function ShowDays(&$db)
{
//####LISTALL_LISTALL_LISTALL_LISTALL_LISTALL_LISTALL_LISTALL_####
// List all records in date range
$FromDate = FormatDateForSQL($_POST['FromDate']);
$ToDate = FormatDateForSQL($_POST['ToDate']);
$sql = "SELECT calendardate,\n\t\t\t\t daynumber,\n\t\t\t\t manufacturingflag,\n\t\t\t\t DAYNAME(calendardate) as dayname\n\t\t\tFROM mrpcalendar\n\t\t\tWHERE calendardate >='" . $FromDate . "'\n\t\t\tAND calendardate <='" . $ToDate . "'";
$ErrMsg = _('The SQL to find the parts selected failed with the message');
$result = DB_query($sql, $ErrMsg);
echo '<br />
<table class="selection">
<tr>
<th>' . _('Date') . '</th>
<th>' . _('Manufacturing Date') . '</th>
</tr>';
$ctr = 0;
while ($myrow = DB_fetch_array($result)) {
$flag = _('Yes');
if ($myrow['manufacturingflag'] == 0) {
$flag = _('No');
}
printf('<tr>
<td>%s</td>
<td>%s</td>
<td>%s</td>
</tr>', ConvertSQLDate($myrow[0]), _($myrow[3]), $flag);
}
//END WHILE LIST LOOP
echo '</table>';
echo '<br /><br />';
unset($ChangeDate);
ShowInputForm($db, $ChangeDate);
}
function ReSequenceEffectiveDates($Item, $PriceList, $CurrAbbrev, $CustomerID, $db)
{
/*This is quite complicated - the idea is that prices set up should be unique and there is no way two prices could be returned as valid - when getting a price in includes/GetPrice.inc the logic is to first look for a price of the salestype/currency within the effective start and end dates - then if not get the price with a start date prior but a blank end date (the default price). We would not want two prices where the effective dates fall between an existing price so it is necessary to update enddates of prices - with me - I am just hanging on here myself
Prices with no end date are default prices and need to be ignored in this resquence*/
$SQL = "SELECT branchcode,\n\t\t\t\t\t\tstartdate,\n\t\t\t\t\t\tenddate\n\t\t\t\t\t\tFROM prices\n\t\t\t\t\t\tWHERE debtorno='" . $CustomerID . "'\n\t\t\t\t\t\tAND stockid='" . $Item . "'\n\t\t\t\t\t\tAND currabrev='" . $CurrAbbrev . "'\n\t\t\t\t\t\tAND typeabbrev='" . $PriceList . "'\n\t\t\t\t\t\tAND enddate<>''\n\t\t\t\t\t\tORDER BY\n\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\tstartdate,\n\t\t\t\t\t\tenddate";
$result = DB_query($SQL, $db);
unset($BranchCode);
while ($myrow = DB_fetch_array($result)) {
if (!isset($BranchCode)) {
unset($NextDefaultStartDate);
//a price with a blank end date
unset($NextStartDate);
unset($EndDate);
unset($StartDate);
$BranchCode = $myrow['branchcode'];
}
if (isset($NextStartDate)) {
if (Date1GreaterThanDate2(ConvertSQLDate($myrow['startdate']), $NextStartDate)) {
$NextStartDate = ConvertSQLDate($myrow['startdate']);
if (Date1GreaterThanDate2(ConvertSQLDate($EndDate), ConvertSQLDate($myrow['startdate']))) {
/*Need to make the end date the new start date less 1 day */
$SQL = "UPDATE prices SET enddate = '" . FormatDateForSQL(DateAdd($NextStartDate, 'd', -1)) . "'\n\t\t\t\t\t\t\t\t\t\tWHERE stockid ='" . $Item . "'\n\t\t\t\t\t\t\t\t\t\tAND currabrev='" . $CurrAbbrev . "'\n\t\t\t\t\t\t\t\t\t\tAND typeabbrev='" . $PriceList . "'\n\t\t\t\t\t\t\t\t\t\tAND startdate ='" . $StartDate . "'\n\t\t\t\t\t\t\t\t\t\tAND enddate = '" . $EndDate . "'\n\t\t\t\t\t\t\t\t\t\tAND debtorno ='" . $CustomerID . "'";
$UpdateResult = DB_query($SQL, $db);
}
}
//end of if startdate after NextStartDate - we have a new NextStartDate
} else {
$NextStartDate = ConvertSQLDate($myrow['startdate']);
}
$StartDate = $myrow['startdate'];
$EndDate = $myrow['enddate'];
}
}
case 2:
echo _('Hide Company Details/Logo');
break;
default:
echo _('Show Company Details/Logo');
break;
}
}
echo '</select></td></tr>';
if (isset($_SESSION['PrintedPackingSlip']) and $_SESSION['PrintedPackingSlip'] == 1) {
echo '<tr>
<td>' . _('Reprint packing slip') . ':</td>
<td><select name="ReprintPackingSlip">';
echo '<option value=0>' . _('Yes');
echo '<option selected value=1>' . _('No');
echo '</select> ' . _('Last printed') . ': ' . ConvertSQLDate($_SESSION['DatePackingSlipPrinted']) . '</td></tr>';
} else {
echo '<input type=hidden name="ReprintPackingSlip" value=0>';
}
echo '<tr><td>' . _('Charge Freight Cost inc tax') . ':</td>';
echo '<td><input type=text class=number size=10 maxlength=12 name="FreightCost" VALUE=' . $_SESSION['Items' . $identifier]->FreightCost . '></td>';
if ($_SESSION['DoFreightCalc'] == true) {
echo '<td><input type=submit name="Update" VALUE="' . _('Recalc Freight Cost') . '"></td></tr>';
}
if ((!isset($_POST['ShipVia']) or $_POST['ShipVia'] == '') and isset($_SESSION['Items' . $identifier]->ShipVia)) {
$_POST['ShipVia'] = $_SESSION['Items' . $identifier]->ShipVia;
}
echo '<tr><td>' . _('Freight/Shipper Method') . ':</td><td><select name="ShipVia">';
$ErrMsg = _('The shipper details could not be retrieved');
$DbgMsg = _('SQL used to retrieve the shipper details was') . ':';
$sql = 'SELECT shipper_id, shippername
$sql = "SELECT accountcode,\n\t\t\t\t accountname\n\t\t\t\tFROM chartmaster\n\t\t\t\tORDER BY accountcode ASC";
$result = DB_query($sql, $db);
while ($myrow = DB_fetch_array($result)) {
echo '<option value="' . $myrow['accountcode'] . '">' . $myrow['accountcode'] . ' - ' . $myrow['accountname'] . '</option>';
}
echo '</select></td></tr>';
echo '<tr>
<td>' . _('OR Asset ID') . '</td>
<td><select name="AssetID">';
$AssetsResult = DB_query("SELECT assetid, description, datepurchased FROM fixedassets ORDER BY assetid DESC", $db);
echo '<option selected="selected" value="Not an Asset">' . _('Not an Asset') . '</option>';
while ($AssetRow = DB_fetch_array($AssetsResult)) {
if ($AssetRow['datepurchased'] == '0000-00-00') {
$DatePurchased = _('Not yet purchased');
} else {
$DatePurchased = ConvertSQLDate($AssetRow['datepurchased']);
}
echo '<option value="' . $AssetRow['assetid'] . '">' . $AssetRow['assetid'] . ' - ' . $DatePurchased . ' - ' . $AssetRow['description'] . '</option>';
}
echo '</select><a href="FixedAssetItems.php" target=_blank>' . _('New Fixed Asset') . '</a></td></tr>
<tr>
<td>' . _('Quantity to purchase') . '</td>
<td><input type="text" class="number" name="Qty" size="10" value="1" /></td>
</tr>
<tr>
<td>' . _('Price per item') . '</td>
<td><input type="text" class="number" name="Price" size="10" /></td>
</tr>
<tr>
<td>' . _('Unit') . '</td>
<td><input type="text" name="SuppliersUnit" size="10" value="' . _('each') . '" /></td>
<?php
/* $Id: DebtorsAtPeriodEnd.php 6338 2013-09-28 05:10:46Z daintree $*/
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', _('Customer Balance Listing'));
$pdf->addInfo('Subject', _('Customer Balances'));
$FontSize = 12;
$PageNumber = 0;
$line_height = 12;
/*Get the date of the last day in the period selected */
$SQL = "SELECT lastdate_in_period FROM periods WHERE periodno = '" . $_POST['PeriodEnd'] . "'";
$PeriodEndResult = DB_query($SQL, $db, _('Could not get the date of the last day in the period selected'));
$PeriodRow = DB_fetch_row($PeriodEndResult);
$PeriodEndDate = ConvertSQLDate($PeriodRow[0]);
/*Now figure out the aged analysis for the customer range under review */
$SQL = "SELECT debtorsmaster.debtorno,\n\t\t\t\t\tdebtorsmaster.name,\n\t\t \t\t\tcurrencies.currency,\n\t\t \t\t\tcurrencies.decimalplaces,\n\t\t\t\t\tSUM((debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc)/debtortrans.rate) AS balance,\n\t\t\t\t\tSUM(debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc) AS fxbalance,\n\t\t\t\t\tSUM(CASE WHEN debtortrans.prd > '" . $_POST['PeriodEnd'] . "' THEN\n\t\t\t\t\t(debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount)/debtortrans.rate ELSE 0 END) AS afterdatetrans,\n\t\t\t\t\tSUM(CASE WHEN debtortrans.prd > '" . $_POST['PeriodEnd'] . "'\n\t\t\t\t\t\tAND (debtortrans.type=11 OR debtortrans.type=12) THEN\n\t\t\t\t\t\tdebtortrans.diffonexch ELSE 0 END) AS afterdatediffonexch,\n\t\t\t\t\tSUM(CASE WHEN debtortrans.prd > '" . $_POST['PeriodEnd'] . "' THEN\n\t\t\t\t\tdebtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount ELSE 0 END\n\t\t\t\t\t) AS fxafterdatetrans\n\t\t\tFROM debtorsmaster INNER JOIN currencies\n\t\t\tON debtorsmaster.currcode = currencies.currabrev\n\t\t\tINNER JOIN debtortrans\n\t\t\tON debtorsmaster.debtorno = debtortrans.debtorno\n\t\t\tWHERE debtorsmaster.debtorno >= '" . $_POST['FromCriteria'] . "'\n\t\t\tAND debtorsmaster.debtorno <= '" . $_POST['ToCriteria'] . "'\n\t\t\tGROUP BY debtorsmaster.debtorno,\n\t\t\t\tdebtorsmaster.name,\n\t\t\t\tcurrencies.currency,\n\t\t\t\tcurrencies.decimalplaces";
$CustomerResult = DB_query($SQL, $db, '', '', false, false);
if (DB_error_no($db) != 0) {
$Title = _('Customer Balances') . ' - ' . _('Problem Report');
include 'includes/header.inc';
prnMsg(_('The customer details 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($CustomerResult) == 0) {
$myrow = DB_fetch_array($result);
echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '" method="post">
<div>
<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />
<table cellpadding="2" class="selection">
<tr>
<th colspan="2">' . _('Item Code') . ':<input type="text" name="StockID" value="' . $StockID . '" maxlength="20" /><input type="submit" name="Show" value="' . _('Show Cost Details') . '" /></th>
</tr>
<tr>
<th colspan="2">' . $StockID . ' - ' . $myrow['description'] . '</th>
</tr>
<tr>
<th colspan="2">' . _('Total Quantity On Hand') . ': ' . $myrow['totalqoh'] . ' ' . $myrow['units'] . '</th>
</tr>
<tr>
<th colspan="2">' . _('Last Cost update on') . ': ' . ConvertSQLDate($myrow['lastcostupdate']) . '</th>
</tr>';
if ($myrow['mbflag'] == 'D' and $myrow['stocktype'] != 'L' or $myrow['mbflag'] == 'A' or $myrow['mbflag'] == 'K') {
echo '</div>
</form>';
// Close the form
if ($myrow['mbflag'] == 'D') {
echo '<br />' . $StockID . ' ' . _('is a service item');
} else {
if ($myrow['mbflag'] == 'A') {
echo '<br />' . $StockID . ' ' . _('is an assembly part');
} else {
if ($myrow['mbflag'] == 'K') {
echo '<br />' . $StockID . ' ' . _('is a kit set part');
}
}
$authresult = DB_query($authsql, $db);
$myauthrow = DB_fetch_array($authresult);
$authlevel = $myauthrow['authlevel'];
$ordervaluesql = 'SELECT sum(unitprice*quantityord) as ordervalue
FROM purchorderdetails
WHERE orderno=' . $myrow['orderno'];
$ordervalueresult = DB_query($ordervaluesql, $db);
$myordervaluerow = DB_fetch_array($ordervalueresult);
$ordervalue = $myordervaluerow['ordervalue'];
if ($authlevel >= $ordervalue) {
echo '<tr>';
echo '<td>' . $myrow['orderno'] . '</td>';
echo '<td>' . $myrow['suppname'] . '</td>';
echo '<td>' . ConvertSQLDate($myrow['orddate']) . '</td>';
echo '<td><a href="mailto:' . $myrow['email'] . '">' . $myrow['realname'] . '</td>';
echo '<td>' . ConvertSQLDate($myrow['deliverydate']) . '</td>';
echo '<td><select name=status' . $myrow['orderno'] . '>';
echo '<option selected value="' . _('Pending') . '">' . _('Pending') . '</option>';
echo '<option value="' . _('Authorised') . '">' . _('Authorised') . '</option>';
echo '<option value="' . _('Rejected') . '">' . _('Rejected') . '</option>';
echo '<option value="' . _('Cancelled') . '">' . _('Cancelled') . '</option>';
echo '</select></td>';
echo '</tr>';
echo "<input type='hidden' name='comment' value='" . $myrow['stat_comment'] . "'>";
$linesql = 'SELECT purchorderdetails.*,
stockmaster.description
FROM purchorderdetails
LEFT JOIN stockmaster
ON stockmaster.stockid=purchorderdetails.itemcode
WHERE orderno=' . $myrow['orderno'];
$lineresult = DB_query($linesql, $db);
$pdf->line($XPos, $YPos - $line_height * 2, $XPos, $YPos + $line_height * 2);
$pdf->line($Page_Width - $Right_Margin, $YPos - $line_height * 2, $Page_Width - $Right_Margin, $YPos + $line_height * 2);
$YPos -= $line_height;
if ($YPos - 2 * $line_height < $Bottom_Margin) {
/*Then set up a new page */
$PageNumber++;
include 'includes/PDFOrdersInvoicedPageHeader.inc';
}
/*end of new page header */
}
if ($myrow['orderno'] != $OrderNo or $NewPage) {
$LeftOvers = $pdf->addTextWrap($Left_Margin + 2, $YPos, 40, $FontSize, $myrow['orderno'], 'left');
$LeftOvers = $pdf->addTextWrap($Left_Margin + 40, $YPos, 150, $FontSize, html_entity_decode($myrow['name']), 'left');
$LeftOvers = $pdf->addTextWrap($Left_Margin + 190, $YPos, 110, $FontSize, $myrow['brname'], 'left');
$LeftOvers = $pdf->addTextWrap($Left_Margin + 300, $YPos, 60, $FontSize, $myrow['customerref'], 'left');
$LeftOvers = $pdf->addTextWrap($Left_Margin + 360, $YPos, 60, $FontSize, ConvertSQLDate($myrow['orddate']), 'left');
$LeftOvers = $pdf->addTextWrap($Left_Margin + 420, $YPos, 80, $FontSize, $myrow['locationname'], 'left');
if (isset($PackingSlipPrinted)) {
$LeftOvers = $pdf->addTextWrap($Left_Margin + 400, $YPos, 100, $FontSize, $PackingSlipPrinted, 'left');
}
$YPos -= $line_height;
$pdf->line($XPos, $YPos, $Page_Width - $Right_Margin, $YPos);
$YPos -= $line_height;
}
$OrderNo = $myrow['orderno'];
/*Set up the headings for the order */
$LeftOvers = $pdf->addTextWrap($Left_Margin, $YPos, 60, $FontSize, _('Code'), 'left');
$LeftOvers = $pdf->addTextWrap($Left_Margin + 60, $YPos, 120, $FontSize, _('Description'), 'left');
$LeftOvers = $pdf->addTextWrap($Left_Margin + 180, $YPos, 60, $FontSize, _('Ordered'), 'right');
$LeftOvers = $pdf->addTextWrap($Left_Margin + 240, $YPos, 60, $FontSize, _('Invoiced'), 'right');
$LeftOvers = $pdf->addTextWrap($Left_Margin + 320, $YPos, 60, $FontSize, _('Outstanding'), 'left');
echo '<table cellpadding="2" width="80%" class="selection">';
$TableHeader = '<tr>
<th>' . _('Supplier Number') . '<br />' . _('Reference') . '</th>
<th>' . _('Payment') . '<br />' . _('Reference') . '</th>
<th>' . _('Payment') . '<br />' . _('Date') . '</th>
<th>' . _('Total Payment') . '<br />' . _('Amount') . '</th></tr>';
echo $TableHeader;
$j = 1;
$k = 0;
//row colour counter
while ($myrow = DB_fetch_array($Result)) {
if ($k == 1) {
echo '<tr class="EvenTableRows">';
$k = 0;
} else {
echo '<tr class="OddTableRows">';
$k++;
}
echo '<td>' . $myrow['supplierno'] . '</td>
<td>' . $myrow['suppreference'] . '</td>
<td>' . ConvertSQLDate($myrow['trandate']) . '</td>
<td class="number">' . locale_money_format($myrow['alloc'], $myrow['currcode']) . '</td>
</tr>';
$j++;
if ($j == 18) {
$j = 1;
echo $TableHeader;
}
}
echo '</table>';
include 'includes/footer.inc';
/*dont trap errors - trapped below*/
if (DB_error_no($db) != 0) {
$Title = _('Aged Supplier Account Analysis - Problem Report');
include 'includes/header.inc';
prnMsg(_('The details of outstanding transactions for Supplier') . ' - ' . $AgedAnalysis['supplierid'] . ' ' . _('could not be retrieved because') . ' - ' . DB_error_msg($db), 'error');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . _('The SQL that failed was') . '<br />' . $sql;
}
include 'includes/footer.inc';
exit;
}
while ($DetailTrans = DB_fetch_array($DetailResult)) {
$LeftOvers = $pdf->addTextWrap($Left_Margin + 5, $YPos, 60, $FontSize, $DetailTrans['typename'], 'left');
$LeftOvers = $pdf->addTextWrap($Left_Margin + 65, $YPos, 50, $FontSize, $DetailTrans['suppreference'], 'left');
$DisplayTranDate = ConvertSQLDate($DetailTrans['trandate']);
$LeftOvers = $pdf->addTextWrap($Left_Margin + 105, $YPos, 70, $FontSize, $DisplayTranDate, 'left');
$DisplayDue = locale_number_format($DetailTrans['due'] - $DetailTrans['overdue1'], $CurrDecimalPlaces);
$DisplayCurrent = locale_number_format($DetailTrans['balance'] - $DetailTrans['due'], $CurrDecimalPlaces);
$DisplayBalance = locale_number_format($DetailTrans['balance'], $CurrDecimalPlaces);
$DisplayOverdue1 = locale_number_format($DetailTrans['overdue1'] - $DetailTrans['overdue2'], $CurrDecimalPlaces);
$DisplayOverdue2 = locale_number_format($DetailTrans['overdue2'], $CurrDecimalPlaces);
$LeftOvers = $pdf->addTextWrap(220, $YPos, 60, $FontSize, $DisplayBalance, 'right');
$LeftOvers = $pdf->addTextWrap(280, $YPos, 60, $FontSize, $DisplayCurrent, 'right');
$LeftOvers = $pdf->addTextWrap(340, $YPos, 60, $FontSize, $DisplayDue, 'right');
$LeftOvers = $pdf->addTextWrap(400, $YPos, 60, $FontSize, $DisplayOverdue1, 'right');
$LeftOvers = $pdf->addTextWrap(460, $YPos, 60, $FontSize, $DisplayOverdue2, 'right');
$YPos -= $line_height;
if ($YPos < $Bottom_Margin + $line_height) {
$PageNumber++;
include 'includes/PDFAgedSuppliersPageHeader.inc';
AND DATE_ADD(recurringsalesorders.lastrecurrence, ' . INTERVAL('365/recurringsalesorders.frequency', 'DAY') . ') <= recurringsalesorders.stopdate';
$RecurrOrdersDueResult = DB_query($sql, $db, _('There was a problem retrieving the recurring sales order templates. The database reported:'));
if (DB_num_rows($RecurrOrdersDueResult) == 0) {
prnMsg(_('There are no recurring order templates that are due to have another recurring order created'), 'warn');
include 'includes/footer.inc';
exit;
}
echo '<BR>The number of recurring orders to process is : ' . DB_num_rows($RecurrOrdersDueResult);
while ($RecurrOrderRow = DB_fetch_array($RecurrOrdersDueResult)) {
$EmailText = '';
echo '<BR>' . _('Recurring order') . ' ' . $RecurrOrderRow['recurrorderno'] . ' ' . _('for') . ' ' . $RecurrOrderRow['debtorno'] . ' - ' . $RecurrOrderRow['branchcode'] . ' ' . _('is being processed');
$result = DB_Txn_Begin($db);
/*the last recurrence was the date of the last time the order recurred
the frequency is the number of times per annum that the order should recurr
so 365 / frequency gives the number of days between recurrences */
$DelDate = FormatDateforSQL(DateAdd(ConvertSQLDate($RecurrOrderRow['lastrecurrence']), 'd', 365 / $RecurrOrderRow['frequency']));
echo '<BR>Date calculated for the next recurrence was: ' . $DelDate;
$HeaderSQL = "INSERT INTO salesorders (\n\t\t\t\tdebtorno,\n\t\t\t\tbranchcode,\n\t\t\t\tcustomerref,\n\t\t\t\tcomments,\n\t\t\t\torddate,\n\t\t\t\tordertype,\n\t\t\t\tshipvia,\n\t\t\t\tdeliverto,\n\t\t\t\tdeladd1,\n\t\t\t\tdeladd2,\n\t\t\t\tdeladd3,\n\t\t\t\tdeladd4,\n\t\t\t\tdeladd5,\n\t\t\t\tdeladd6,\n\t\t\t\tcontactphone,\n\t\t\t\tcontactemail,\n\t\t\t\tfreightcost,\n\t\t\t\tfromstkloc,\n\t\t\t\tdeliverydate )\n\t\t\tVALUES (\n\t\t\t\t'" . $RecurrOrderRow['debtorno'] . "',\n\t\t\t\t'" . $RecurrOrderRow['branchcode'] . "',\n\t\t\t\t'" . $RecurrOrderRow['customerref'] . "',\n\t\t\t\t'" . $RecurrOrderRow['comments'] . "',\n\t\t\t\t'" . $DelDate . "',\n\t\t\t\t'" . $RecurrOrderRow['ordertype'] . "',\n\t\t\t\t" . $RecurrOrderRow['shipvia'] . ",\n\t\t\t\t'" . $RecurrOrderRow['deliverto'] . "',\n\t\t\t\t'" . $RecurrOrderRow['deladd1'] . "',\n\t\t\t\t'" . $RecurrOrderRow['deladd2'] . "',\n\t\t\t\t'" . $RecurrOrderRow['deladd3'] . "',\n\t\t\t\t'" . $RecurrOrderRow['deladd4'] . "',\n\t\t\t\t'" . $RecurrOrderRow['deladd5'] . "',\n\t\t\t\t'" . $RecurrOrderRow['deladd6'] . "',\n\t\t\t\t'" . $RecurrOrderRow['contactphone'] . "',\n\t\t\t\t'" . $RecurrOrderRow['contactemail'] . "',\n\t\t\t\t" . $RecurrOrderRow['freightcost'] . ",\n\t\t\t\t'" . $RecurrOrderRow['fromstkloc'] . "',\n\t\t\t\t'" . $DelDate . "')";
$ErrMsg = _('The order cannot be added because');
$InsertQryResult = DB_query($HeaderSQL, $db, $ErrMsg, true);
$OrderNo = GetNextTransNo(30, $db);
$EmailText = _('A new order has been created from a recurring order template for customer') . ' ' . $RecurrOrderRow['debtorno'] . ' ' . $RecurrOrderRow['branchcode'] . "\n" . _('The order number is:') . ' ' . $OrderNo;
/*need to look up RecurringOrder from the template and populate the line RecurringOrder array with the sales order details records */
$LineItemsSQL = "SELECT recurrsalesorderdetails.stkcode,\n\t\t\t\trecurrsalesorderdetails.unitprice,\n\t\t\t\trecurrsalesorderdetails.quantity,\n\t\t\t\trecurrsalesorderdetails.discountpercent,\n\t\t\t\trecurrsalesorderdetails.narrative,\n\t\t\t\tstockmaster.taxcatid\n\t\t\tFROM recurrsalesorderdetails INNER JOIN stockmaster\n\t\t\t\tON recurrsalesorderdetails.stkcode = stockmaster.stockid\n\t\t\tWHERE recurrsalesorderdetails.recurrorderno =" . $RecurrOrderRow['recurrorderno'];
$ErrMsg = _('The line items of the recurring order cannot be retrieved because');
$LineItemsResult = db_query($LineItemsSQL, $db, $ErrMsg);
$LineCounter = 0;
if (db_num_rows($LineItemsResult) > 0) {
$OrderTotal = 0;
//intialise
$OrderLineTotal = 0;
$sql = "SELECT stockmoves.stockid,\n\t\tstockmaster.description,\n\t\tstockmaster.decimalplaces,\n\t\ttrandate,\n\t\tqty,\n\t\tstockmoves.standardcost\n\tFROM stockmoves INNER JOIN stockmaster\n\tON stockmoves.stockid=stockmaster.stockid\n\tWHERE stockmoves.type=28\n\tAND reference = " . $_POST['WO'] . "\n\tAND stockmoves.stockid NOT IN\n\t\t\t(SELECT worequirements.stockid\n\t\t\t\tFROM worequirements\n\t\t\tWHERE worequirements.wo=" . $_POST['WO'] . ")";
$WOIssuesResult = DB_query($sql, $db, _('Could not get issues that were not required by the BOM because'));
if (DB_num_rows($WOIssuesResult) > 0) {
while ($WOIssuesRow = DB_fetch_array($WOIssuesResult)) {
if ($k == 1) {
echo '<tr class="EvenTableRows">';
$k = 0;
} else {
echo '<tr class="OddTableRows">';
$k++;
}
echo '<td>' . $WOIssuesRow['stockid'] . '</td>
<td>' . $WOIssuesRow['description'] . '</td>
<td align="right">0</td>
<td align="right">0</td>
<td>' . ConvertSQLDate($WOIssuesRow['trandate']) . '</td>
<td align="right">' . number_format(-$WOIssuesRow['qty'], $WOIssuesRow['decimalplaces']) . '</td>
<td align="right">' . number_format(-$WOIssuesRow['qty'] * $WOIssuesRow['standardcost'], 2) . '</td>
<td align="right">' . number_format($WOIssuesRow['qty'] * $WOIssuesRow['standardcost'], 2) . '</td>
<td align="right">0</td></tr>';
$TotalUsageVar += $WOIssuesRow['qty'] * $WOIssuesRow['standardcost'];
}
}
# <!-- <td colspan="5"></td> -->
echo '<tr><td colspan="3"></td><td><hr/></td><td colspan="2"></td><td colspan="3"><hr></td></tr>';
echo '<tr><td colspan="2" align="right">' . _('Totals') . '</td>
<td></td>
<td>' . number_format($TotalReqdCost, 2) . '</td>
<td></td><td></td>
<td>' . number_format($TotalIssuedCost, 2) . '</td>
<td align="right">' . number_format($TotalUsageVar, 2) . '</td>
echo '<div>';
echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
echo '<table class="selection">
<tr>
<th>' . _('Request Number') . '</th>
<th>' . _('Department') . '</th>
<th>' . _('Location Of Stock') . '</th>
<th>' . _('Requested Date') . '</th>
<th>' . _('Narrative') . '</th>
</tr>';
while ($myrow = DB_fetch_array($result)) {
echo '<tr>
<td>' . $myrow['dispatchid'] . '</td>
<td>' . $myrow['description'] . '</td>
<td>' . $myrow['locationname'] . '</td>
<td>' . ConvertSQLDate($myrow['despatchdate']) . '</td>
<td>' . $myrow['narrative'] . '</td>
</tr>';
$LineSQL = "SELECT stockrequestitems.dispatchitemsid,\n\t\t\t\t\t\tstockrequestitems.dispatchid,\n\t\t\t\t\t\tstockrequestitems.stockid,\n\t\t\t\t\t\tstockrequestitems.decimalplaces,\n\t\t\t\t\t\tstockrequestitems.uom,\n\t\t\t\t\t\tstockmaster.description,\n\t\t\t\t\t\tstockrequestitems.quantity,\n\t\t\t\t\t\tstockrequestitems.qtydelivered\n\t\t\t\tFROM stockrequestitems\n\t\t\t\tLEFT JOIN stockmaster\n\t\t\t\tON stockmaster.stockid=stockrequestitems.stockid\n\t\t\tWHERE dispatchid='" . $myrow['dispatchid'] . "'\n\t\t\t\tAND completed=0";
$LineResult = DB_query($LineSQL, $db);
echo '<tr>
<td></td>
<td colspan="5" align="left">
<table class="selection" align="left">
<tr>
<th>' . _('Product') . '</th>
<th>' . _('Quantity') . '<br />' . _('Required') . '</th>
<th>' . _('Quantity') . '<br />' . _('Delivered') . '</th>
<th>' . _('Units') . '</th>
<th>' . _('Completed') . '</th>
<th>' . _('Tag') . '</th>
break;
/* stop displaying pirce history more than a year old once we have at least one to display */
}
}
$LastPrice = $myrow['price'];
$LastDiscount = $myrow['discountpercent'];
$ToDate = $myrow['trandate'];
$qty = 0;
}
$qty += $myrow['qty'];
$FromDate = $myrow['trandate'];
}
if (isset($qty)) {
$DateRange = ConvertSQLDate($FromDate);
if ($FromDate != $ToDate) {
$DateRange .= ' - ' . ConvertSQLDate($ToDate);
}
$PriceHistory[] = array($DateRange, $qty, $LastPrice, $LastDiscount);
}
if (isset($PriceHistory)) {
echo '<br /><table cellpadding="4" class="selection">';
echo '<tr><th colspan="4" class="header">' . _('Pricing history for sales of') . ' ' . $StockID . ' ' . _('to') . ' ' . $DebtorNo . '</th></tr>';
$tableheader = '<tr>
<th>' . _('Date Range') . '</th>
<th>' . _('Quantity') . '</th>
<th>' . _('Price') . '</th>
<th>' . _('Discount') . '</th>
</tr>';
$j = 0;
$k = 0;
//row colour counter
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
}
/* If balance of the order cancelled update sales order details quantity. Also insert log records for OrderDeliveryDifferencesLog */
foreach ($_SESSION['Items']->LineItems as $OrderLine) {
if ($_POST['BOPolicy'] == 'CAN') {
$SQL = "UPDATE salesorderdetails\r\n\t\t\t\tSET quantity = quantity - " . ($OrderLine->Quantity - $OrderLine->QtyDispatched) . " WHERE orderno = " . $_SESSION['ProcessingOrder'] . " AND stkcode = '" . $OrderLine->StockID . "'";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The sales order detail record could not be updated because');
$DbgMsg = _('The following SQL to update the sales order detail record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
if ($OrderLine->Quantity - $OrderLine->QtyDispatched > 0) {
$SQL = "INSERT INTO orderdeliverydifferenceslog (\r\n\t\t\t\t\t\torderno,\r\n\t\t\t\t\t\tinvoiceno,\r\n\t\t\t\t\t\tstockid,\r\n\t\t\t\t\t\tquantitydiff,\r\n\t\t\t\t\t\tdebtorno,\r\n\t\t\t\t\t\tbranch,\r\n\t\t\t\t\t\tcan_or_bo\r\n\t\t\t\t\t\t)\r\n\t\t\t\t\tVALUES (\r\n\t\t\t\t\t\t" . $_SESSION['ProcessingOrder'] . ",\r\n\t\t\t\t\t\t" . $InvoiceNo . ",\r\n\t\t\t\t\t\t'" . $OrderLine->StockID . "',\r\n\t\t\t\t\t\t" . ($OrderLine->Quantity - $OrderLine->QtyDispatched) . ",\r\n\t\t\t\t\t\t'" . $_SESSION['Items']->DebtorNo . "',\r\n\t\t\t\t\t\t'" . $_SESSION['Items']->Branch . "',\r\n\t\t\t\t\t\t'CAN'\r\n\t\t\t\t\t\t)";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The order delivery differences log record could not be inserted because');
$DbgMsg = _('The following SQL to insert the order delivery differences record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
}
} elseif ($OrderLine->Quantity - $OrderLine->QtyDispatched > 0 && DateDiff(ConvertSQLDate($DefaultDispatchDate), $_SESSION['Items']->DeliveryDate, 'd') > 0) {
/*The order is being short delivered after the due date - need to insert a delivery differnce log */
$SQL = "INSERT INTO orderdeliverydifferenceslog (\r\n\t\t\t\t\torderno,\r\n\t\t\t\t\tinvoiceno,\r\n\t\t\t\t\tstockid,\r\n\t\t\t\t\tquantitydiff,\r\n\t\t\t\t\tdebtorno,\r\n\t\t\t\t\tbranch,\r\n\t\t\t\t\tcan_or_bo\r\n\t\t\t\t)\r\n\t\t\t\tVALUES (\r\n\t\t\t\t\t" . $_SESSION['ProcessingOrder'] . ",\r\n\t\t\t\t\t" . $InvoiceNo . ",\r\n\t\t\t\t\t'" . $OrderLine->StockID . "',\r\n\t\t\t\t\t" . ($OrderLine->Quantity - $OrderLine->QtyDispatched) . ",\r\n\t\t\t\t\t'" . $_SESSION['Items']->DebtorNo . "',\r\n\t\t\t\t\t'" . $_SESSION['Items']->Branch . "',\r\n\t\t\t\t\t'BO'\r\n\t\t\t\t)";
$ErrMsg = '<br>' . _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The order delivery differences log record could not be inserted because');
$DbgMsg = _('The following SQL to insert the order delivery differences record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
}
/*end of order delivery differences log entries */
/*Now update SalesOrderDetails for the quantity invoiced and the actual dispatch dates. */
if ($OrderLine->QtyDispatched != 0 and $OrderLine->QtyDispatched != "" and $OrderLine->QtyDispatched) {
// Test above to see if the line is completed or not
if ($OrderLine->QtyDispatched >= $OrderLine->Quantity - $OrderLine->QtyInv or $_POST['BOPolicy'] == "CAN") {
$SQL = "UPDATE salesorderdetails\r\n\t\t\t\t\tSET qtyinvoiced = qtyinvoiced + " . $OrderLine->QtyDispatched . ",\r\n\t\t\t\t\tactualdispatchdate = '" . $DefaultDispatchDate . "',\r\n\t\t\t\t\tcompleted=1\r\n\t\t\t\t\tWHERE orderno = " . $_SESSION['ProcessingOrder'] . "\r\n\t\t\t\t\tAND orderlineno = '" . $OrderLine->LineNumber . "'";
} else {
$SQL = "UPDATE salesorderdetails\r\n\t\t\t\t\tSET qtyinvoiced = qtyinvoiced + " . $OrderLine->QtyDispatched . ",\r\n\t\t\t\t\tactualdispatchdate = '" . $DefaultDispatchDate . "'\r\n\t\t\t\t\tWHERE orderno = " . $_SESSION['ProcessingOrder'] . "\r\n\t\t\t\t\tAND orderlineno = '" . $OrderLine->LineNumber . "'";
}
</table>';
$sql = "SELECT stockmoves.stockid,\n\t\t\t\t \t\tstockmaster.description,\n\t\t\t\t\t\t-stockmoves.qty*stockmoves.conversionfactor as quantity,\n\t\t\t\t\t\tstockmoves.discountpercent,\n\t\t\t\t\t\t((1 - stockmoves.discountpercent) * stockmoves.price * " . $ExchRate . "* -stockmoves.qty) AS fxnet,\n\t\t\t\t\t\t(stockmoves.price * " . $ExchRate . ") AS fxprice,\n\t\t\t\t\t\tstockmoves.narrative,\n\t\t\t\t\t\tstockmoves.units,\n\t\t\t\t\t\tstockmoves.conversionfactor\n\t\t\t\t\tFROM stockmoves,\n\t\t\t\t\t\tstockmaster\n\t\t\t\t\tWHERE stockmoves.stockid = stockmaster.stockid\n\t\t\t\t\tAND stockmoves.type=10\n\t\t\t\t\tAND stockmoves.transno='" . $FromTransNo . "'\n\t\t\t\t\tAND stockmoves.show_on_inv_crds=1";
} else {
/* then its a credit note */
echo '<table width=50%><tr>
<td align=left bgcolor="#BBBBBB"><b>' . _('Branch') . ':</b></td>
</tr>';
echo '<tr>
<td bgcolor="#EEEEEE">' . $myrow['brname'] . '<br />' . $myrow['braddress1'] . '<br />' . $myrow['braddress2'] . '<br />' . $myrow['braddress3'] . '<br />' . $myrow['braddress4'] . '<br />' . $myrow['braddress5'] . '<br />' . $myrow['braddress6'] . '</td>
</tr></table>';
echo '<hr><table class="table1"><tr>
<td align=left bgcolor="#BBBBBB"><b>' . _('Date') . '</b></td>
<td align=left bgcolor="#BBBBBB"><b>' . _('Sales Person') . '</font></b></td>
</tr>';
echo '<tr>
<td bgcolor="#EEEEEE">' . ConvertSQLDate($myrow['trandate']) . '</td>
<td bgcolor="#EEEEEE">' . $myrow['salesmanname'] . '</td>
</tr></table>';
$sql = "SELECT stockmoves.stockid,\n\t\t\t\t \t\tstockmaster.description,\n\t\t\t\t\t\tstockmoves.qty*stockmoves.conversionfactor as quantity,\n\t\t\t\t\t\tstockmoves.discountpercent, ((1 - stockmoves.discountpercent) * stockmoves.price * " . $ExchRate . " * stockmoves.qty) AS fxnet,\n\t\t\t\t\t\t(stockmoves.price * " . $ExchRate . ") AS fxprice,\n\t\t\t\t\t\tstockmoves.units,\n\t\t\t\t\t\tstockmoves.conversionfactor\n\t\t\t\t\tFROM stockmoves,\n\t\t\t\t\t\tstockmaster\n\t\t\t\t\tWHERE stockmoves.stockid = stockmaster.stockid\n\t\t\t\t\tAND stockmoves.type=11\n\t\t\t\t\tAND stockmoves.transno='" . $FromTransNo . "'\n\t\t\t\t\tAND stockmoves.show_on_inv_crds=1";
}
echo '<hr>';
echo '<div class="centre"><font size=2>' . _('All amounts stated in') . ' ' . $myrow['currcode'] . '</font></div>';
$result = DB_query($sql, $db);
if (DB_error_no($db) != 0) {
echo '<div class="page_help_text">' . _('There was a problem retrieving the invoice or credit note stock movement details for invoice number') . ' ' . $FromTransNo . ' ' . _('from the database') . '</div>';
if ($debug == 1) {
echo '<br />' . _('The SQL used to get this information that failed was') . '<br />' . $sql;
}
exit;
}
if (DB_num_rows($result) > 0) {
