function GetReports($GroupID)
{
global $db, $rootpath;
$Title = array(_('Custom Reports'), _('Default Reports'));
$RptForm = '<form name="ReportList" method="post" action="' . $rootpath . '/reportwriter/ReportMaker.php?action=go">';
$RptForm .= '<table align="center" border="0" cellspacing="1" cellpadding="1" class="table_index">';
for ($Def = 1; $Def >= 0; $Def--) {
$RptForm .= '<tr><td colspan="2"><div align="center">' . $Title[$Def] . '</div></td></tr>';
$sql = "SELECT id, reportname FROM reports \n\t\t\tWHERE defaultreport='" . $Def . "' AND groupname='" . $GroupID . "' \n\t\t\tORDER BY reportname";
$Result = DB_query($sql, $db, '', '', false, true);
if (DB_num_rows($Result) > 0) {
$RptForm .= '<tr><td><select name="ReportID" size="10" onChange="submit()">';
while ($Temp = DB_fetch_array($Result)) {
$RptForm .= '<option value="' . $Temp['id'] . '">' . $Temp['reportname'] . '</option>';
}
$RptForm .= '</select></td></tr>';
} else {
$RptForm .= '<tr><td colspan="2">' . _('There are no reports to show!') . '</td></tr>';
}
}
$RptForm .= '</table></form>';
return $RptForm;
}
function display_children($parent, $level, &$BOMTree)
{
global $db;
global $i;
// retrive all children of parent
$c_result = DB_query("SELECT parent,\n\t\t\t\t\tcomponent\n\t\t\t\tFROM bom WHERE parent='" . $parent . "'", $db);
if (DB_num_rows($c_result) > 0) {
//echo ("<UL>\n");
while ($row = DB_fetch_array($c_result)) {
//echo '<br />Parent: ' . $parent . ' Level: ' . $level . ' row[component]: ' . $row['component'] .'<br />';
if ($parent != $row['component']) {
// indent and display the title of this child
$BOMTree[$i]['Level'] = $level;
// Level
if ($level > 15) {
prnMsg(_('A maximum of 15 levels of bill of materials only can be displayed'), 'error');
exit;
}
$BOMTree[$i]['Parent'] = $parent;
// Assemble
$BOMTree[$i]['Component'] = $row['component'];
// Component
// call this function again to display this
// child's children
$i++;
display_children($row['component'], $level + 1, $BOMTree);
}
}
}
}
function DisplayBOMItems($UltimateParent, $Parent, $Component, $Level, $db)
{
global $ParentMBflag;
if ($_SESSION['RestrictLocations'] == 0) {
$sql = "SELECT bom.component,\n\t\t\t\t\t\t\tstockmaster.description as itemdescription,\n\t\t\t\t\t\t\tlocations.locationname,\n\t\t\t\t\t\t\tlocations.loccode,\n\t\t\t\t\t\t\tworkcentres.description as workcentrename,\n\t\t\t\t\t\t\tworkcentres.code as workcentrecode,\n\t\t\t\t\t\t\tbom.quantity,\n\t\t\t\t\t\t\tbom.effectiveafter,\n\t\t\t\t\t\t\tbom.effectiveto,\n\t\t\t\t\t\t\tbom.sequence,\n\t\t\t\t\t\t\tstockmaster.mbflag,\n\t\t\t\t\t\t\tbom.autoissue,\n\t\t\t\t\t\t\tstockmaster.controlled,\n\t\t\t\t\t\t\tlocstock.quantity AS qoh,\n\t\t\t\t\t\t\tstockmaster.decimalplaces\n\t\t\t\t\t\tFROM bom\n\t\t\t\t\t\tINNER JOIN stockmaster\n\t\t\t\t\t\t\tON bom.component=stockmaster.stockid\n\t\t\t\t\t\tINNER JOIN locations\n\t\t\t\t\t\t\tON bom.loccode = locations.loccode\n\t\t\t\t\t\tINNER JOIN workcentres\n\t\t\t\t\t\t\tON bom.workcentreadded=workcentres.code\n\t\t\t\t\t\tINNER JOIN locstock\n\t\t\t\t\t\t\tON bom.loccode=locstock.loccode\n\t\t\t\t\t\t\tAND bom.component = locstock.stockid\n\t\t\t\t\t\tWHERE bom.component='" . $Component . "'\n\t\t\t\t\t\t\tAND bom.parent = '" . $Parent . "'\n\t\t\t\t\t\tORDER BY bom.sequence ASC";
} else {
$sql = "SELECT bom.component,\n\t\t\t\t\t\t\tstockmaster.description as itemdescription,\n\t\t\t\t\t\t\tlocations.locationname,\n\t\t\t\t\t\t\tlocations.loccode,\n\t\t\t\t\t\t\tworkcentres.description as workcentrename,\n\t\t\t\t\t\t\tworkcentres.code as workcentrecode,\n\t\t\t\t\t\t\tbom.quantity,\n\t\t\t\t\t\t\tbom.effectiveafter,\n\t\t\t\t\t\t\tbom.effectiveto,\n\t\t\t\t\t\t\tbom.sequence,\n\t\t\t\t\t\t\tstockmaster.mbflag,\n\t\t\t\t\t\t\tbom.autoissue,\n\t\t\t\t\t\t\tstockmaster.controlled,\n\t\t\t\t\t\t\tlocstock.quantity AS qoh,\n\t\t\t\t\t\t\tstockmaster.decimalplaces\n\t\t\t\t\t\tFROM bom\n\t\t\t\t\t\tINNER JOIN stockmaster\n\t\t\t\t\t\t\tON bom.component=stockmaster.stockid\n\t\t\t\t\t\tINNER JOIN locations\n\t\t\t\t\t\t\tON bom.loccode = locations.loccode\n\t\t\t\t\t\tINNER JOIN workcentres\n\t\t\t\t\t\t\tON bom.workcentreadded=workcentres.code\n\t\t\t\t\t\tINNER JOIN locstock\n\t\t\t\t\t\t\tON bom.loccode=locstock.loccode\n\t\t\t\t\t\t\tAND bom.component = locstock.stockid\n\t\t\t\t\t\tINNER JOIN www_users\n\t\t\t\t\t\t\tON locations.loccode=www_users.defaultlocation\n\t\t\t\t\t\tWHERE bom.component='" . $Component . "'\n\t\t\t\t\t\t\tAND bom.parent = '" . $Parent . "'\n\t\t\t\t\t\t\tAND www_users.userid='" . $_SESSION['UserID'] . "'\n\t\t\t\t\t\tORDER BY bom.sequence ASC";
}
$ErrMsg = _('Could not retrieve the BOM components because');
$DbgMsg = _('The SQL used to retrieve the components was');
$result = DB_query($sql, $db, $ErrMsg, $DbgMsg);
$RowCounter = 0;
while ($myrow = DB_fetch_array($result)) {
$Level1 = str_repeat('- ', $Level - 1) . $Level;
if ($myrow['mbflag'] == 'B' or $myrow['mbflag'] == 'K' or $myrow['mbflag'] == 'D') {
$DrillText = '%s%s';
$DrillLink = '<div class="centre">' . _('No lower levels') . '</div>';
$DrillID = '';
} else {
$DrillText = '<a href="%s&Select=%s">' . _('Drill Down') . '</a>';
$DrillLink = htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '?';
$DrillID = $myrow['component'];
}
if ($ParentMBflag != 'M' and $ParentMBflag != 'G') {
$AutoIssue = _('N/A');
} elseif ($myrow['controlled'] == 0 and $myrow['autoissue'] == 1) {
//autoissue and not controlled
$AutoIssue = _('Yes');
} elseif ($myrow['controlled'] == 1) {
$AutoIssue = _('No');
} else {
$AutoIssue = _('N/A');
}
if ($myrow['mbflag'] == 'D' or $myrow['mbflag'] == 'K' or $myrow['mbflag'] == 'A' or $myrow['mbflag'] == 'G') {
$QuantityOnHand = _('N/A');
} else {
$QuantityOnHand = locale_number_format($myrow['qoh'], $myrow['decimalplaces']);
}
printf('<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td class="number">%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td class="number">%s</td>
<td><a href="%s&Select=%s&SelectedComponent=%s">' . _('Edit') . '</a></td>
<td>' . $DrillText . '</td>
<td><a href="%s&Select=%s&SelectedComponent=%s&delete=1&ReSelect=%s&Location=%s&WorkCentre=%s" onclick="return MakeConfirm(\'' . _('Are you sure you wish to delete this component from the bill of material?') . '\', \'Confirm Delete\', this);">' . _('Delete') . '</a></td>
</tr>', $Level1, $myrow['sequence'], $myrow['component'], $myrow['itemdescription'], $myrow['locationname'], $myrow['workcentrename'], locale_number_format($myrow['quantity'], 'Variable'), ConvertSQLDate($myrow['effectiveafter']), ConvertSQLDate($myrow['effectiveto']), $AutoIssue, $QuantityOnHand, htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '?', $Parent, $myrow['component'], $DrillLink, $DrillID, htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '?', $Parent, $myrow['component'], $UltimateParent, $myrow['loccode'], $myrow['workcentrecode']);
}
//END WHILE LIST LOOP
}
function VerifyAccountSection($AccountSection, $i, $Errors, $db)
{
$Searchsql = "SELECT count(sectionid)\n\t\t\t\tFROM accountsection\n\t\t\t\tWHERE sectionid='" . $AccountSection . "'";
$SearchResult = DB_query($Searchsql, $db);
$answer = DB_fetch_array($SearchResult);
if ($answer[0] > 0) {
$Errors[$i] = GLAccountSectionAlreadyExists;
}
return $Errors;
}
function VerifyParentGroupExists($AccountGroup, $i, $Errors, $db)
{
$Searchsql = "SELECT count(groupname)\n\t\t\t\tFROM accountgroups\n\t\t\t\tWHERE groupname='" . $AccountGroup . "'";
$SearchResult = DB_query($Searchsql, $db);
$answer = DB_fetch_array($SearchResult);
if ($answer[0] == 0 and $AccountGroup != '') {
$Errors[$i] = AccountGroupDoesntExist;
}
return $Errors;
}
function GetShipperDetails($Shipper, $User, $Password)
{
$Errors = array();
$db = db($User, $Password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
$sql = "SELECT * FROM shippers WHERE shipper_id='" . $Shipper . "'";
$result = DB_query($sql, $db);
return DB_fetch_array($result);
}
function GetCustomerTypeDetails($typeid, $user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
$sql = "SELECT * FROM debtortype WHERE typeid='" . $typeid . "'";
$result = DB_query($sql, $db);
return DB_fetch_array($result);
}
function GetHoldReasonDetails($holdreason, $user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
$sql = "SELECT * FROM holdreasons WHERE reasoncode='" . $holdreason . "'";
$result = DB_query($sql, $db);
return DB_fetch_array($result);
}
function GetTaxgroupDetails($taxgroup, $user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
$sql = 'SELECT * FROM taxgroups WHERE taxgroupid="' . $taxgroup . '"';
$result = DB_query($sql, $db);
return DB_fetch_array($result);
}
function GetCurrencyDetails($currency, $user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
$sql = "SELECT * FROM currencies WHERE currabrev='" . $currency . "'";
$result = DB_query($sql, $db);
return DB_fetch_array($result);
}
function GetLocationDetails($location, $user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
$sql = 'SELECT * FROM locations WHERE loccode="' . $location . '"';
$result = DB_query($sql, $db);
return DB_fetch_array($result);
}
function GetTaxes()
{
global $db;
/*Gets the Taxes and rates applicable to the tax group of the supplier
and SESSION['DefaultTaxCategory'] and the taxprovince of the location that the user is setup to use*/
$SQL = "SELECT taxgrouptaxes.calculationorder,\n\t\t\t\t\ttaxauthorities.description,\n\t\t\t\t\ttaxgrouptaxes.taxauthid,\n\t\t\t\t\ttaxauthorities.purchtaxglaccount,\n\t\t\t\t\ttaxgrouptaxes.taxontax,\n\t\t\t\t\ttaxauthrates.taxrate\n\t\t\tFROM taxauthrates INNER JOIN taxgrouptaxes ON\n\t\t\t\ttaxauthrates.taxauthority=taxgrouptaxes.taxauthid\n\t\t\t\tINNER JOIN taxauthorities ON\n\t\t\t\ttaxauthrates.taxauthority=taxauthorities.taxid\n\t\t\tWHERE taxgrouptaxes.taxgroupid=" . $this->TaxGroup . " \n\t\t\tAND taxauthrates.dispatchtaxprovince=" . $this->LocalTaxProvince . " \n\t\t\tAND taxauthrates.taxcatid = " . $_SESSION['DefaultTaxCategory'] . "\n\t\t\tORDER BY taxgrouptaxes.calculationorder";
$ErrMsg = _('The taxes and rates for this item could not be retreived because');
$GetTaxRatesResult = DB_query($SQL, $db, $ErrMsg);
while ($myrow = DB_fetch_array($GetTaxRatesResult)) {
$this->Taxes[$myrow['calculationorder']] = new Tax($myrow['calculationorder'], $myrow['taxauthid'], $myrow['description'], $myrow['taxrate'], $myrow['taxontax'], $myrow['purchtaxglaccount']);
}
}
function GetPaymentTermsDetails($paymentterms, $user, $password)
{
$Errors = array();
if (!isset($db)) {
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
}
$sql = "SELECT * FROM paymentterms WHERE termsindicator='" . $paymentterms . "'";
$result = DB_query($sql, $db);
return DB_fetch_array($result);
}
function GetPaymentMethodDetails($PaymentMethod, $User, $Password)
{
$Errors = array();
if (!isset($db)) {
$db = db($User, $Password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
}
$sql = "SELECT * FROM paymentmethods WHERE paymentid='" . $PaymentMethod . "'";
$result = DB_query($sql, $db);
return DB_fetch_array($result);
}
function Offer($Supplier)
{
/*Constructor function initialises a new purchase offer object */
global $db;
$this->LineItems = array();
$this->total = 0;
$this->LinesOnOffer = 0;
$this->SupplierID = $Supplier;
$sql = "SELECT suppname,\n\t\t\t\t\temail,\n\t\t\t\t\tcurrcode\n\t\t\t\tFROM suppliers\n\t\t\t\tWHERE supplierid='" . $this->SupplierID . "'";
$result = DB_query($sql, $db);
$myrow = DB_fetch_array($result);
$this->SupplierName = $myrow['suppname'];
$this->EmailAddress = $myrow['email'];
$this->CurrCode = $myrow['currcode'];
}
function GetDefaultLocation($user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
$sql = "SELECT defaultlocation FROM www_users WHERE userid='" . $user . "'";
$result = DB_query($sql, $db);
$answer = DB_fetch_array($result);
$ReturnValue[0] = 0;
$ReturnValue[1] = $answer;
return $ReturnValue;
}
function Receipt($Amt, $Cust, $Disc, $Narr, $id, $GLCode, $PayeeBankDetail, $CustomerName, $Tag)
{
global $db;
/* Constructor function to add a new Receipt object with passed params */
$this->Amount = $Amt;
$this->Customer = $Cust;
$this->CustomerName = $CustomerName;
$this->Discount = $Disc;
$this->Narrative = $Narr;
$this->GLCode = $GLCode;
$this->PayeeBankDetail = $PayeeBankDetail;
$this->ID = $id;
$this->tag = $Tag;
$result = DB_query("SELECT tagdescription FROM tags WHERE tagref='" . $Tag . "'");
if (DB_num_rows($result) == 1) {
$TagRow = DB_fetch_array($result);
$this->TagName = $TagRow['tagdescription'];
}
}
function GetSalesTypeDetails($salestype, $user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
$Errors = VerifySalesType($salestype, sizeof($Errors), $Errors, $db);
if (sizeof($Errors) == 0) {
$sql = 'SELECT * FROM salestypes WHERE typeabbrev="' . $salestype . '"';
$result = DB_query($sql, $db);
$Errors[0] = 0;
$Errors[1] = DB_fetch_array($result);
return $Errors;
} else {
return $Errors;
}
}
function GetRptLinks($GroupID)
{
global $db, $RootPath;
$Title = array(_('Custom Reports'), _('Standard Reports'));
$RptLinks = '';
for ($Def = 1; $Def >= 0; $Def--) {
$RptLinks .= '<tr><td class="menu_group_headers"><div align="center">' . $Title[$Def] . '</div></td></tr>';
$sql = "SELECT id, reportname FROM reports\n\t\t\tWHERE defaultreport='" . $Def . "' AND groupname='" . $GroupID . "'\n\t\t\tORDER BY reportname";
$Result = DB_query($sql, $db, '', '', false, true);
if (DB_num_rows($Result) > 0) {
while ($Temp = DB_fetch_array($Result)) {
$RptLinks .= '<tr><td class="menu_group_item">';
$RptLinks .= '<a href="' . $RootPath . '/reportwriter/ReportMaker.php?action=go&reportid=' . $Temp['id'] . '"><li>' . _($Temp['reportname']) . '</li></a>';
$RptLinks .= '</td></tr>';
}
} else {
$RptLinks .= '<tr><td class="menu_group_item">' . _('There are no reports to show!') . '</td></tr>';
}
}
return $RptLinks;
}
function GetSalesOrderLine($OrderNo, $user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
$Errors = VerifyOrderHeaderExists($OrderNo, sizeof($Errors), $Errors, $db);
if (sizeof($Errors) != 0) {
return $Errors;
}
$sql = 'SELECT * FROM salesorderdetails WHERE orderno="' . $OrderNo . '"';
$result = api_DB_Query($sql, $db);
if (sizeof($Errors) == 0) {
return DB_fetch_array($result);
} else {
return $Errors;
}
}
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Location stock record could not be updated because');
$DbgMsg = _('The following SQL to update the location stock record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
} else {
if ($MBFlag == 'A') {
/* its an assembly */
/*Need to get the BOM for this part and make
stock moves for the components then update the Location stock balances */
$Assembly = True;
$StandardCost = 0;
/*To start with - accumulate the cost of the comoponents for use in journals later on */
$SQL = "SELECT bom.component,\r\n\t\t\t\t\t\tbom.quantity,\r\n\t\t\t\t\t\tstockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost AS standard\r\n\t\t\t\t\tFROM bom,\r\n\t\t\t\t\t\tstockmaster\r\n\t\t\t\t\tWHERE bom.component=stockmaster.stockid\r\n\t\t\t\t\tAND bom.parent='" . $OrderLine->StockID . "'\r\n\t\t\t\t\tAND bom.effectiveto > '" . Date("Y-m-d") . "'\r\n\t\t\t\t\tAND bom.effectiveafter < '" . Date("Y-m-d") . "'";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Could not retrieve assembly components from the database for') . ' ' . $OrderLine->StockID . _('because') . ' ';
$DbgMsg = _('The SQL that failed was');
$AssResult = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
while ($AssParts = DB_fetch_array($AssResult, $db)) {
$StandardCost += $AssParts['standard'] * $AssParts['quantity'];
/* Need to get the current location quantity
will need it later for the stock movement */
$SQL = "SELECT locstock.quantity\r\n\t\t\t\t\t\tFROM locstock\r\n\t\t\t\t\t\tWHERE locstock.stockid='" . $AssParts['component'] . "'\r\n\t\t\t\t\t\tAND loccode= '" . $_SESSION['Items']->Location . "'";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Can not retrieve assembly components location stock quantities because ');
$DbgMsg = _('The SQL that failed was');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
if (DB_num_rows($Result) == 1) {
$LocQtyRow = DB_fetch_row($Result);
$QtyOnHandPrior = $LocQtyRow[0];
} else {
/*There must be some error this should never happen */
$QtyOnHandPrior = 0;
}
if (empty($AssParts['standard'])) {
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
}
echo '</table>
<div class="centre">';
if (!isset($_POST['CategoryID'])) {
$_POST['CategoryID'] = '';
}
$sql = "SELECT stkcatpropid,\n label,\n controltype,\n defaultvalue,\n numericvalue,\n minimumvalue,\n maximumvalue\n FROM stockcatproperties\n WHERE categoryid ='" . $_POST['CategoryID'] . "'\n AND reqatsalesorder =0\n ORDER BY stkcatpropid";
$PropertiesResult = DB_query($sql, $db);
$PropertyCounter = 0;
$PropertyWidth = array();
if (DB_num_rows($PropertiesResult) > 0) {
echo '<br />
<table class="selection">';
echo '<tr>
<th colspan="2">' . _('Item Category Properties') . '</th>
</tr>';
while ($PropertyRow = DB_fetch_array($PropertiesResult)) {
if (isset($_POST['StockID']) && !empty($_POST['StockID'])) {
$PropValResult = DB_query("SELECT value FROM\n stockitemproperties\n WHERE stockid='" . $_POST['StockID'] . "'\n AND stkcatpropid ='" . $PropertyRow['stkcatpropid'] . "'", $db);
$PropValRow = DB_fetch_row($PropValResult);
$PropertyValue = $PropValRow[0];
} else {
$PropertyValue = '';
}
echo '<tr>
<td>';
echo '<input type="hidden" name="PropID' . $PropertyCounter . '" value="' . $PropertyRow['stkcatpropid'] . '" />';
echo '<input type="hidden" name="PropNumeric' . $PropertyCounter . '" value="' . $PropertyRow['numericvalue'] . '" />';
echo $PropertyRow['label'] . '</td>
<td>';
switch ($PropertyRow['controltype']) {
echo '<tr><td>' . _('Inventory Category') . ':</td>
<td><select name="CategoryID">';
echo '<option selected="selected" value="All">' . _('All Stock Categories') . '</option>';
$sql = "SELECT categoryid,\n\t\t\t categorydescription\n\t\tFROM stockcategory";
$result = DB_query($sql, $db);
while ($myrow = DB_fetch_array($result)) {
echo '<option value="' . $myrow['categoryid'] . '">' . $myrow['categoryid'] . ' - ' . $myrow['categorydescription'] . '</option>';
}
//end while loop
echo '</select></td>
</tr>';
echo '<tr><td>' . _('Inventory Location') . ':</td>
<td><select name="Location">';
echo '<option selected="selected" value="All">' . _('All Locations') . '</option>';
$result = DB_query("SELECT loccode,\n\t\t\t\t\t\t locationname\n\t\t\t\t\tFROM locations", $db);
while ($myrow = DB_fetch_array($result)) {
echo '<option value="' . $myrow['loccode'] . '">' . $myrow['locationname'] . '</option>';
}
echo '</select></td></tr>';
if (!isset($_POST['FromDate'])) {
$_POST['FromDate'] = date($_SESSION['DefaultDateFormat']);
}
if (!isset($_POST['ToDate'])) {
$_POST['ToDate'] = date($_SESSION['DefaultDateFormat']);
}
if (!isset($_POST['DistDate'])) {
$_POST['DistDate'] = date($_SESSION['DefaultDateFormat']);
}
echo '<tr>
<td>' . _('From Sales Date') . ':</td>
<td><input type="text" class="date" alt="' . $_SESSION['DefaultDateFormat'] . '" name="FromDate" size="10" value="' . $_POST['FromDate'] . '" /> ' . _('To Sales Date') . ':<input type="text" class="date" alt="' . $_SESSION['DefaultDateFormat'] . '" name="ToDate" size="10" value="' . $_POST['ToDate'] . '" /></td>
<th>' . _('Units') . '</th>
<th>' . _('Completed') . '</th>
<th>' . _('Tag') . '</th>
</tr>';
while ($LineRow = DB_fetch_array($LineResult)) {
echo '<tr>
<td>' . $LineRow['description'] . '</td>
<td class="number">' . locale_number_format($LineRow['quantity'] - $LineRow['qtydelivered'], $LineRow['decimalplaces']) . '</td>
<td class="number"><input type="text" class="number" name="' . $LineRow['dispatchid'] . 'Qty' . $LineRow['dispatchitemsid'] . '" value="' . locale_number_format($LineRow['quantity'] - $LineRow['qtydelivered'], $LineRow['decimalplaces']) . '" /></td>
<td>' . $LineRow['uom'] . '</td>
<td><input type="checkbox" name="' . $LineRow['dispatchid'] . 'Completed' . $LineRow['dispatchitemsid'] . '" /></td>
<td><select minlength="0" name="' . $LineRow['dispatchid'] . 'Tag' . $LineRow['dispatchitemsid'] . '">';
$SQL = "SELECT tagref,\n\t\t\t\t\t\t\ttagdescription\n\t\t\t\t\t\tFROM tags\n\t\t\t\t\t\tORDER BY tagref";
$TagResult = DB_query($SQL, $db);
echo '<option value=0>0 - None</option>';
while ($mytagrow = DB_fetch_array($TagResult)) {
if (isset($_SESSION['Adjustment']->tag) and $_SESSION['Adjustment']->tag == $mytagrow['tagref']) {
echo '<option selected="selected" value="' . $mytagrow['tagref'] . '">' . $mytagrow['tagref'] . ' - ' . $myrow['tagdescription'] . '</option>';
} else {
echo '<option value="' . $mytagrow['tagref'] . '">' . $mytagrow['tagref'] . ' - ' . $mytagrow['tagdescription'] . '</option>';
}
}
echo '</select></td>';
// End select tag
echo '</tr>';
echo '<input type="hidden" class="number" name="' . $LineRow['dispatchid'] . 'StockID' . $LineRow['dispatchitemsid'] . '" value="' . $LineRow['stockid'] . '" />';
echo '<input type="hidden" class="number" name="' . $LineRow['dispatchid'] . 'Location' . $LineRow['dispatchitemsid'] . '" value="' . $_POST['Location'] . '" />';
echo '<input type="hidden" class="number" name="' . $LineRow['dispatchid'] . 'RequestedQuantity' . $LineRow['dispatchitemsid'] . '" value="' . locale_number_format($LineRow['quantity'] - $LineRow['qtydelivered'], $LineRow['decimalplaces']) . '" />';
echo '<input type="hidden" class="number" name="' . $LineRow['dispatchid'] . 'Department' . $LineRow['dispatchitemsid'] . '" value="' . $myrow['description'] . '" />';
}
// end while order line detail
$AllSerials = array();
foreach ($LineItem->SerialItems as $Itm) {
$AllSerials[$Itm->BundleRef] = $Itm->BundleQty;
}
echo '<td valign="top"><b>' . _('Select Existing Items') . '</b><br />';
echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '?identifier=' . $identifier . '" method="post">';
echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
echo '<input type="hidden" name="LineNo" value="' . $LineNo . '">
<input type="hidden" name="StockID" value="' . $StockID . '">
<input type="hidden" name="EntryType" value="KEYED">
<input type="hidden" name="identifier" value="' . $identifier . '">
<input type="hidden" name="EditControlled" value="true">
<select name=Bundles[] multiple="multiple">';
$id = 0;
$ItemsAvailable = 0;
while ($myrow = DB_fetch_array($Bundles, $db)) {
if ($LineItem->Serialised == 1) {
if (!array_key_exists($myrow['serialno'], $AllSerials)) {
echo '<option value="' . $myrow['serialno'] . '">' . $myrow['serialno'] . '</option>';
$ItemsAvailable++;
}
} else {
if (!array_key_exists($myrow['serialno'], $AllSerials) or $myrow['quantity'] - $AllSerials[$myrow['serialno']] >= 0) {
$RecvQty = $myrow['quantity'] - $AllSerials[$myrow['serialno']];
echo '<option value="' . $myrow['serialno'] . '/|/' . $RecvQty . '">' . $myrow['serialno'] . ' - ' . _('Qty left') . ': ' . $RecvQty . '</option>';
$ItemsAvailable += $RecvQty;
}
}
}
echo '</select>
<br />';
if ($myrow['offhold'] == 0) {
$OffHold = 'Yes';
} else {
$OffHold = 'No';
}
$currencysql = "SELECT currency FROM currencies WHERE currabrev='" . $Currency . "'";
$currencyresult = DB_query($currencysql, $db);
$myrow = DB_fetch_array($currencyresult);
echo '<tr><td>' . _('Currency') . '</td><td>' . $myrow['currency'] . '</td></tr>';
echo '<input type="hidden" name="CurrCode" value="' . $Currency . '" />';
} else {
echo '<tr><td>' . _('Currency') . '</td><td><select name="CurrCode">';
echo '<option value=""></option>';
$currencysql = "SELECT currabrev,currency FROM currencies";
$currencyresult = DB_query($currencysql, $db);
while ($myrow = DB_fetch_array($currencyresult)) {
if ($myrow['currabrev'] == $Currency) {
echo '<option selected="True" value="' . $myrow['currabrev'] . '">' . $myrow['currency'] . '</option>';
} else {
echo '<option value="' . $myrow['currabrev'] . '">' . $myrow['currency'] . '</option>';
}
}
echo '</select></td></tr>';
}
echo '<tr><td>' . _('User can create orders') . '</td>';
if (isset($CanCreate) and $CanCreate == 'No') {
echo '<td><input type="checkbox" name="CanCreate" /></td></tr>';
} else {
echo '<td><input type="checkbox" checked="True" name="CanCreate" /></td></tr>';
}
echo '<tr><td>' . _('User can release invoices') . '</td>';
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\t\t\t\t\tVALUES (21,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $SQLCreditNoteDate . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $StockGLCode['purchpricevaract'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['SuppTrans']->SupplierID . ' - ' . _('GRN') . ' ' . $EnteredGRN->GRNNo . ' - ' . $EnteredGRN->ItemCode . ' x ' . $EnteredGRN->This_QuantityInv . ' x ' . _('price var of') . ' ' . locale_number_format($EnteredGRN->ChgPrice / $_SESSION['SuppTrans']->ExRate - $EnteredGRN->StdCostUnit, $_SESSION['CompanyRecord']['decimalplaces']) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . -$PurchPriceVar . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction could not be added for the price variance of the stock item because');
$DbgMsg = _('The following SQL to insert the GL transaction was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, True);
}
} else {
/* its a nominal purchase order item that is not on a shipment so post the whole lot to the GLCode specified in the order, the purchase price var is actually the diff between the
order price and the actual invoice price since the std cost was made equal to the order price in local currency at the time
the goods were received */
$GLCode = $EnteredGRN->GLCode;
//by default
if ($EnteredGRN->AssetID != 0) {
//then it is an asset
/*Need to get the asset details for posting */
$result = DB_query("SELECT costact\n\t\t\t\t\t\t\t\t\t\t\t\t\tFROM fixedassets INNER JOIN fixedassetcategories\n\t\t\t\t\t\t\t\t\t\t\t\t\tON fixedassets.assetcategoryid= fixedassetcategories.categoryid\n\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE assetid='" . $EnteredGRN->AssetID . "'", $db);
$AssetRow = DB_fetch_array($result);
$GLCode = $AssetRow['costact'];
}
//the item was an asset
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\t\tVALUES (21,\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $SQLCreditNoteDate . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $GLCode . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['SuppTrans']->SupplierID . ' - ' . _('GRN') . ' ' . $EnteredGRN->GRNNo . ' - ' . $EnteredGRN->ItemDescription . ' x ' . $EnteredGRN->This_QuantityInv . ' x ' . _('price var') . ' ' . locale_number_format($EnteredGRN->ChgPrice / $_SESSION['SuppTrans']->ExRate - $EnteredGRN->StdCostUnit, $_SESSION['CompanyRecord']['decimalplaces']) . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . -$PurchPriceVar . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction could not be added for the price variance of the stock item because');
$DbgMsg = _('The following SQL to insert the GL transaction was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, True);
}
}
} else {
/*then its a purchase order item on a shipment - whole charge amount to GRN suspense pending closure of the shipment when the variance is calculated and the GRN act cleared up for the shipment */
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t\t\t21,\n\t\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\n\t\t\t\t\t\t\t\t\t'" . $SQLCreditNoteDate . "',\n\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['SuppTrans']->GRNAct . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['SuppTrans']->SupplierID . ' - ' . _('GRN') . ' ' . $EnteredGRN->GRNNo . ' - ' . $EnteredGRN->ItemCode . ' x ' . $EnteredGRN->This_QuantityInv . ' @ ' . $_SESSION['SuppTrans']->CurrCode . ' ' . $EnteredGRN->ChgPrice . ' @ ' . _('a rate of') . ' ' . $_SESSION['SuppTrans']->ExRate . "',\n\t\t\t\t\t\t\t\t\t'" . -$EnteredGRN->ChgPrice * $EnteredGRN->This_QuantityInv / $_SESSION['SuppTrans']->ExRate . "'\n\t\t\t\t\t\t\t\t)";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction could not be added because');
$DbgMsg = _('The following SQL to insert the GL transaction was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, True);
//end of ifs and buts!
if (isset($SelectedReport)) {
echo '<a href="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '">' . _('Show All Defined Reports') . '</a>';
}
echo '<br />';
if (!isset($_GET['delete'])) {
echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '">';
echo '<div>';
echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
if (isset($SelectedReport)) {
//editing an existing Report
$sql = "SELECT reportid,\n\t\t\t\t\t\treportheading,\n\t\t\t\t\t\tgroupbydata1,\n\t\t\t\t\t\tnewpageafter1,\n\t\t\t\t\t\tupper1,\n\t\t\t\t\t\tlower1,\n\t\t\t\t\t\tgroupbydata2,\n\t\t\t\t\t\tnewpageafter2,\n\t\t\t\t\t\tupper2,\n\t\t\t\t\t\tlower2,\n\t\t\t\t\t\tgroupbydata3,\n\t\t\t\t\t\tupper3,\n\t\t\t\t\t\tlower3,\n\t\t\t\t\t\tnewpageafter3,\n\t\t\t\t\t\tgroupbydata4,\n\t\t\t\t\t\tupper4,\n\t\t\t\t\t\tlower4\n\t\t\t\tFROM reportheaders\n\t\t\t\tWHERE reportid='" . $SelectedReport . "'";
$ErrMsg = _('The reports for display could not be retrieved because');
$DbgMsg = _('The SQL used to retrieve the report headers was');
$result = DB_query($sql, $ErrMsg, $DbgMsg);
$myrow = DB_fetch_array($result);
$ReportID = $myrow['reportid'];
$_POST['ReportHeading'] = $myrow['reportheading'];
$_POST['GroupByData1'] = $myrow['groupbydata1'];
$_POST['NewPageAfter1'] = $myrow['newpageafter1'];
$_POST['Upper1'] = $myrow['upper1'];
$_POST['Lower1'] = $myrow['lower1'];
$_POST['GroupByData2'] = $myrow['groupbydata2'];
$_POST['NewPageAfter2'] = $myrow['newpageafter2'];
$_POST['Upper2'] = $myrow['upper2'];
$_POST['Lower2'] = $myrow['lower2'];
$_POST['GroupByData3'] = $myrow['groupbydata3'];
$_POST['Upper3'] = $myrow['upper3'];
$_POST['Lower3'] = $myrow['lower3'];
$_POST['GroupByData4'] = $myrow['groupbydata4'];
$_POST['Upper4'] = $myrow['upper4'];
$SQL = "BEGIN";
$Result = DB_query($SQL, $db);
$TotalFXNetInvoice = 0;
$TotalFXTax = 0;
DB_data_seek($LineItemsResult, 0);
$LineCounter = 0;
while ($RecurrOrderLineRow = DB_fetch_array($LineItemsResult)) {
$LineNetAmount = $RecurrOrderLineRow['unitprice'] * $RecurrOrderLineRow['quantity'] * (1 - floatval($RecurrOrderLineRow['discountpercent']));
/*Gets the Taxes and rates applicable to this line from the TaxGroup of the branch and TaxCategory of the item
and the taxprovince of the dispatch location */
$SQL = "SELECT taxgrouptaxes.calculationorder,\n\t\t\t\t\ttaxauthorities.description,\n\t\t\t\t\ttaxgrouptaxes.taxauthid,\n\t\t\t\t\ttaxauthorities.taxglcode,\n\t\t\t\t\ttaxgrouptaxes.taxontax,\n\t\t\t\t\ttaxauthrates.taxrate\n\t\t\tFROM taxauthrates INNER JOIN taxgrouptaxes ON\n\t\t\t\ttaxauthrates.taxauthority=taxgrouptaxes.taxauthid\n\t\t\t\tINNER JOIN taxauthorities ON\n\t\t\t\ttaxauthrates.taxauthority=taxauthorities.taxid\n\t\t\tWHERE taxgrouptaxes.taxgroupid=" . $RecurrOrderRow['taxgroupid'] . "\n\t\t\tAND taxauthrates.dispatchtaxprovince=" . $DispTaxProvinceID . "\n\t\t\tAND taxauthrates.taxcatid = " . $RecurrOrderLineRow['taxcatid'] . "\n\t\t\tORDER BY taxgrouptaxes.calculationorder";
$ErrMsg = _('The taxes and rates for this item could not be retreived because');
$GetTaxRatesResult = DB_query($SQL, $db, $ErrMsg);
$LineTaxAmount = 0;
$TaxTotals = array();
while ($myrow = DB_fetch_array($GetTaxRatesResult)) {
if (!isset($TaxTotals[$myrow['taxauthid']]['FXAmount'])) {
$TaxTotals[$myrow['taxauthid']]['FXAmount'] = 0;
}
$TaxAuthID = $myrow['taxauthid'];
$TaxTotals[$myrow['taxauthid']]['GLCode'] = $myrow['taxglcode'];
$TaxTotals[$myrow['taxauthid']]['TaxRate'] = $myrow['taxrate'];
$TaxTotals[$myrow['taxauthid']]['TaxAuthDescription'] = $myrow['description'];
if ($myrow['taxontax'] == 1) {
$TaxAuthAmount = ($LineNetAmount + $LineTaxAmount) * $myrow['taxrate'];
$TaxTotals[$myrow['taxauthid']]['FXAmount'] += ($LineNetAmount + $LineTaxAmount) * $myrow['taxrate'];
} else {
$TaxAuthAmount = $LineNetAmount * $myrow['taxrate'];
$TaxTotals[$myrow['taxauthid']]['FXAmount'] += $LineNetAmount * $myrow['taxrate'];
}
/*Make an array of the taxes and amounts including GLcodes for later posting - need debtortransid
