function CreateCreditNote($Header, $LineDetails, $User, $Password)
{
/* Create a customer credit note in KwaMoja.
* Needs an associative array for the $Header
* and an array of assocative arrays for the $LineDetails
* $Header contains an associative array in the format:
* Header['debtorno'] - the customer code
* Header['branchcode'] - the branch code
* Header['trandate'] - the date of the credit note
* Header['tpe'] - the sales type
* Header['fromstkloc'] - the inventory location where the stock is put back into
* Header['customerref'] - the customer's reference
* Header['shipvia'] - the shipper required by KwaMoja
*
* and $LineDetails contains an array of associative arrays of the format:
*
* $LineDetails[0]['stockid']
* $LineDetails[0]['price']
* $LineDetails[0]['qty'] - expected to be a negative quantity (a negative sale)
* $LineDetails[0]['discountpercent']
*/
$Errors = array();
$db = db($User, $Password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
$Errors = VerifyDebtorExists($Header['debtorno'], sizeof($Errors), $Errors, $db);
$Errors = VerifyBranchNoExists($Header['debtorno'], $Header['branchcode'], sizeof($Errors), $Errors, $db);
/*Does not deal with serialised/lot track items - for use by POS */
/*Get Company Defaults */
$ReadCoyResult = api_DB_query("SELECT debtorsact,\n\t\t\t\t\t\t\t\t\t\t\t\tfreightact,\n\t\t\t\t\t\t\t\t\t\t\t\tgllink_debtors,\n\t\t\t\t\t\t\t\t\t\t\t\tgllink_stock\n\t\t\t\t\t\t\t\t\t\tFROM companies\n\t\t\t\t\t\t\t\t\t\tWHERE coycode=1", $db);
$CompanyRecord = DB_fetch_array($ReadCoyResult);
if (DB_error_no($db) != 0) {
$Errors[] = NoCompanyRecord;
}
$HeaderSQL = "SELECT custbranch.area,\n\t\t\t\t\t\t\t custbranch.taxgroupid,\n\t\t\t\t\t\t\t debtorsmaster.currcode,\n\t\t\t\t\t\t\t rate,\n\t\t\t\t\t\t\t salesman\n\t\t\t\t\t\t\tFROM debtorsmaster\n\t\t\t\t\t\t\tINNER JOIN custbranch\n\t\t\t\t\t\t\tON debtorsmaster.debtorno = custbranch.debtorno\n\t\t\t\t\t\t\tINNER JOIN currencies\n\t\t\t\t\t\t\tON debtorsmaster.currcode=currencies.currabrev\n\t\t\t\t\t\t\tWHERE custbranch.debtorno = '" . $Header['debtorno'] . "'\n\t\t\t\t\t\t\tAND custbranch.branchcode='" . $Header['branchcode'] . "'";
$HeaderResult = api_DB_query($HeaderSQL, $db);
if (DB_error_no($db) != 0) {
$Errors[] = NoReadCustomerBranch;
}
$CN_Header = DB_fetch_array($HeaderResult);
$TaxProvResult = api_DB_query("SELECT taxprovinceid FROM locations WHERE loccode='" . $Header['fromstkloc'] . "'", $db);
if (DB_error_no($db) != 0) {
$Errors[] = NoTaxProvince;
}
$myrow = DB_fetch_row($TaxProvResult);
$DispTaxProvinceID = $myrow[0];
/*Start an SQL transaction */
$result = DB_Txn_Begin($db);
/*Now Get the next credit note number - function in SQL_CommonFunctions*/
$CreditNoteNo = GetNextTransNo(11, $db);
$PeriodNo = GetCurrentPeriod($db);
$TotalFXNetCredit = 0;
$TotalFXTax = 0;
$TaxTotals = array();
$LineCounter = 0;
foreach ($LineDetails as $CN_Line) {
$LineSQL = "SELECT taxcatid,\n\t\t\t\t\t\t\t\tmbflag,\n\t\t\t\t\t\t\t\tmaterialcost+labourcost+overheadcost AS standardcost\n\t\t\t\t\t\tFROM stockmaster\n\t\t\t\t\t\tWHERE stockid ='" . $CN_Line['stockid'] . "'";
$LineResult = api_DB_query($LineSQL, $db);
if (DB_error_no($db) != 0 or DB_num_rows($LineResult) == 0) {
$Errors[] = NoReadItem;
return $Errors;
}
$LineRow = DB_fetch_array($LineResult);
$StandardCost = $LineRow['standardcost'];
$LocalCurrencyPrice = $CN_Line['price'] * (1 - floatval($CN_Line['discountpercent'])) / $CN_Header['rate'];
$LineNetAmount = $CN_Line['price'] * $CN_Line['qty'] * (1 - floatval($CN_Line['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\t\t\ttaxauthorities.description,\n\t\t\t\t\t\t\ttaxgrouptaxes.taxauthid,\n\t\t\t\t\t\t\ttaxauthorities.taxglcode,\n\t\t\t\t\t\t\ttaxgrouptaxes.taxontax,\n\t\t\t\t\t\t\ttaxauthrates.taxrate\n\t\t\t\t\tFROM taxauthrates INNER JOIN taxgrouptaxes ON\n\t\t\t\t\t\ttaxauthrates.taxauthority=taxgrouptaxes.taxauthid\n\t\t\t\t\t\tINNER JOIN taxauthorities ON\n\t\t\t\t\t\ttaxauthrates.taxauthority=taxauthorities.taxid\n\t\t\t\t\tWHERE taxgrouptaxes.taxgroupid='" . $CN_Header['taxgroupid'] . "'\n\t\t\t\t\tAND taxauthrates.dispatchtaxprovince='" . $DispTaxProvinceID . "'\n\t\t\t\t\tAND taxauthrates.taxcatid = '" . $LineRow['taxcatid'] . "'\n\t\t\t\t\tORDER BY taxgrouptaxes.calculationorder";
$GetTaxRatesResult = api_DB_query($SQL, $db);
if (DB_error_no($db) != 0) {
$Errors[] = TaxRatesFailed;
}
$LineTaxAmount = 0;
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'];
} else {
$TaxAuthAmount = $LineNetAmount * $myrow['taxrate'];
}
$TaxTotals[$myrow['taxauthid']]['FXAmount'] += $TaxAuthAmount;
/*Make an array of the taxes and amounts including GLcodes for later posting - need debtortransid
so can only post once the debtor trans is posted - can only post debtor trans when all tax is calculated */
$LineTaxes[$LineCounter][$myrow['calculationorder']] = array('TaxCalculationOrder' => $myrow['calculationorder'], 'TaxAuthID' => $myrow['taxauthid'], 'TaxAuthDescription' => $myrow['description'], 'TaxRate' => $myrow['taxrate'], 'TaxOnTax' => $myrow['taxontax'], 'TaxAuthAmount' => $TaxAuthAmount);
$LineTaxAmount += $TaxAuthAmount;
}
//end loop around Taxes
$TotalFXNetCredit += $LineNetAmount;
$TotalFXTax += $LineTaxAmount;
if ($LineRow['mbflag'] == 'B' or $LineRow['mbflag'] == 'M') {
$Assembly = False;
/* Need to get the current location quantity
will need it later for the stock movement */
$SQL = "SELECT locstock.quantity\n\t\t\t\t\t\tFROM locstock\n\t\t\t\t\t\tWHERE locstock.stockid='" . $CN_Line['stockid'] . "'\n\t\t\t\t\t\tAND loccode= '" . $Header['fromstkloc'] . "'";
$Result = api_DB_query($SQL, $db);
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;
}
$SQL = "UPDATE locstock\n\t\t\t\t\t\tSET quantity = locstock.quantity - " . $CN_Line['qty'] . "\n\t\t\t\t\t\tWHERE locstock.stockid = '" . $CN_Line['stockid'] . "'\n\t\t\t\t\t\tAND loccode = '" . $Header['fromstkloc'] . "'";
$Result = api_DB_query($SQL, $db, '', '', true);
$SQL = "INSERT INTO stockmoves (stockid,\n\t\t\t\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\t\t\t\ttransno,\n\t\t\t\t\t\t\t\t\t\t\t\tloccode,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\t\t\t\tprice,\n\t\t\t\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\t\t\t\t\tdiscountpercent,\n\t\t\t\t\t\t\t\t\t\t\t\tstandardcost,\n\t\t\t\t\t\t\t\t\t\t\t\tnewqoh)\n\t\t\t\t\t\tVALUES ('" . $CN_Line['stockid'] . "',\n\t\t\t\t\t\t\t\t'11',\n\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\n\t\t\t\t\t\t\t\t'" . $Header['fromstkloc'] . "',\n\t\t\t\t\t\t\t\t'" . $Header['trandate'] . "',\n\t\t\t\t\t\t\t\t'" . $Header['debtorno'] . "',\n\t\t\t\t\t\t\t\t'" . $Header['branchcode'] . "',\n\t\t\t\t\t\t\t\t'" . $LocalCurrencyPrice . "',\n\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t'" . $Header['customerref'] . "',\n\t\t\t\t\t\t\t\t'" . -$CN_Line['qty'] . "',\n\t\t\t\t\t\t\t\t'" . $CN_Line['discountpercent'] . "',\n\t\t\t\t\t\t\t\t'" . $StandardCost . "',\n\t\t\t\t\t\t\t\t'" . ($QtyOnHandPrior - $CN_Line['qty']) . "' )";
$Result = api_DB_query($SQL, $db, '', '', true);
} else {
if ($LineRow['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,\n\t\t\t\t\t\t\t\tbom.quantity,\n\t\t\t\t\t\t\t\tstockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost AS standard\n\t\t\t\t\t\t\tFROM bom INNER JOIN stockmaster\n\t\t\t\t\t\t\tON bom.component=stockmaster.stockid\n\t\t\t\t\t\t\tWHERE bom.parent='" . $CN_Line['stockid'] . "'\n\t\t\t\t\t\t\tAND bom.effectiveto >= '" . Date('Y-m-d') . "'\n\t\t\t\t\t\t\tAND bom.effectiveafter < '" . Date('Y-m-d') . "'";
$AssResult = api_DB_query($SQL, $db);
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\n\t\t\t\t\t\t\tFROM locstock\n\t\t\t\t\t\t\tWHERE locstock.stockid='" . $AssParts['component'] . "'\n\t\t\t\t\t\t\tAND loccode= '" . $Header['fromstkloc'] . "'";
$Result = api_DB_query($SQL, $db);
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'])) {
$AssParts['standard'] = 0;
}
$SQL = "INSERT INTO stockmoves (stockid,\n\t\t\t\t\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\t\t\t\t\ttransno,\n\t\t\t\t\t\t\t\t\t\t\t\t\tloccode,\n\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\tdebtorno,\n\t\t\t\t\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\t\t\t\t\t\tstandardcost,\n\t\t\t\t\t\t\t\t\t\t\t\t\tshow_on_inv_crds,\n\t\t\t\t\t\t\t\t\t\t\t\t\tnewqoh)\n\t\t\t\t\t\t\t\t\t\tVALUES ('" . $AssParts['component'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t 11,\n\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 '" . $Header['fromstkloc'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $Header['trandate'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $Header['debtorno'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $Header['branchcode'] . "',\n\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 '" . _('Assembly') . ': ' . $CN_Line['stockid'] . ' ' . $Header['customerref'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . -$AssParts['quantity'] * $CN_Line['qty'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $AssParts['standard'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t 0,\n\t\t\t\t\t\t\t\t\t\t\t\t '" . ($QtyOnHandPrior - $AssParts['quantity'] * $CN_Line['qty']) . "'\t)";
$Result = DB_query($SQL, $db, '', '', true);
$SQL = "UPDATE locstock\n\t\t\t\t\t\t\tSET quantity = locstock.quantity - " . $AssParts['quantity'] * $CN_Line['qty'] . "\n\t\t\t\t\t\t\tWHERE locstock.stockid = '" . $AssParts['component'] . "'\n\t\t\t\t\t\t\tAND loccode = '" . $Header['fromlocstk'] . "'";
$Result = DB_query($SQL, $db, '', '', true);
}
/* end of assembly explosion and updates */
}
}
/* end of its an assembly */
if ($LineRow['mbflag'] == 'A' or $LineRow['mbflag'] == 'D') {
/*it's a Dummy/Service item or an Assembly item - still need stock movement record
* but quantites on hand are always nil */
$SQL = "INSERT INTO stockmoves (stockid,\n\t\t\t\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\t\t\t\ttransno,\n\t\t\t\t\t\t\t\t\t\t\t\tloccode,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\t\t\t\tprice,\n\t\t\t\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\t\t\t\t\tdiscountpercent,\n\t\t\t\t\t\t\t\t\t\t\t\tstandardcost,\n\t\t\t\t\t\t\t\t\t\t\t\tnewqoh)\n\t\t\t\t\t\tVALUES ('" . $CN_Line['stockid'] . "',\n\t\t\t\t\t\t\t\t'11',\n\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\n\t\t\t\t\t\t\t\t'" . $Header['fromstkloc'] . "',\n\t\t\t\t\t\t\t\t'" . $Header['trandate'] . "',\n\t\t\t\t\t\t\t\t'" . $Header['debtorno'] . "',\n\t\t\t\t\t\t\t\t'" . $Header['branchcode'] . "',\n\t\t\t\t\t\t\t\t'" . $LocalCurrencyPrice . "',\n\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t'" . $Header['customerref'] . "',\n\t\t\t\t\t\t\t\t'" . -$CN_Line['qty'] . "',\n\t\t\t\t\t\t\t\t'" . $CN_Line['discountpercent'] . "',\n\t\t\t\t\t\t\t\t'" . $StandardCost . "',\n\t\t\t\t\t\t\t\t'0' )";
$Result = api_DB_query($SQL, $db, '', '', true);
}
/*Get the ID of the StockMove... */
$StkMoveNo = DB_Last_Insert_ID($db, 'stockmoves', 'stkmoveno');
/*Insert the taxes that applied to this line */
foreach ($LineTaxes[$LineCounter] as $Tax) {
$SQL = "INSERT INTO stockmovestaxes (stkmoveno,\n\t\t\t\t\t\t\t\t\ttaxauthid,\n\t\t\t\t\t\t\t\t\ttaxrate,\n\t\t\t\t\t\t\t\t\ttaxcalculationorder,\n\t\t\t\t\t\t\t\t\ttaxontax)\n\t\t\t\t\t\tVALUES ('" . $StkMoveNo . "',\n\t\t\t\t\t\t\t'" . $Tax['TaxAuthID'] . "',\n\t\t\t\t\t\t\t'" . $Tax['TaxRate'] . "',\n\t\t\t\t\t\t\t'" . $Tax['TaxCalculationOrder'] . "',\n\t\t\t\t\t\t\t'" . $Tax['TaxOnTax'] . "')";
$Result = DB_query($SQL, $db, '', '', true);
}
/*Insert Sales Analysis records */
$SQL = "SELECT COUNT(*),\n\t\t\t\t\t\tsalesanalysis.stkcategory,\n\t\t\t\t\t\tsalesanalysis.area,\n\t\t\t\t\t\tsalesanalysis.salesperson,\n\t\t\t\t\t\tsalesanalysis.periodno,\n\t\t\t\t\t\tsalesanalysis.typeabbrev,\n\t\t\t\t\t\tsalesanalysis.cust,\n\t\t\t\t\t\tsalesanalysis.custbranch,\n\t\t\t\t\t\tsalesanalysis.stockid\n\t\t\t\t\tFROM salesanalysis,\n\t\t\t\t\t\tcustbranch,\n\t\t\t\t\t\tstockmaster\n\t\t\t\t\tWHERE salesanalysis.stkcategory=stockmaster.categoryid\n\t\t\t\t\tAND salesanalysis.stockid=stockmaster.stockid\n\t\t\t\t\tAND salesanalysis.cust=custbranch.debtorno\n\t\t\t\t\tAND salesanalysis.custbranch=custbranch.branchcode\n\t\t\t\t\tAND salesanalysis.area=custbranch.area\n\t\t\t\t\tAND salesanalysis.salesperson=custbranch.salesman\n\t\t\t\t\tAND salesanalysis.typeabbrev ='" . $Header['tpe'] . "'\n\t\t\t\t\tAND salesanalysis.periodno='" . $PeriodNo . "'\n\t\t\t\t\tAND salesanalysis.cust " . LIKE . " '" . $Header['debtorno'] . "'\n\t\t\t\t\tAND salesanalysis.custbranch " . LIKE . " '" . $Header['branchcode'] . "'\n\t\t\t\t\tAND salesanalysis.stockid " . LIKE . " '" . $CN_Line['stockid'] . "'\n\t\t\t\t\tAND salesanalysis.budgetoractual='1'\n\t\t\t\t\tGROUP BY salesanalysis.stockid,\n\t\t\t\t\t\tsalesanalysis.stkcategory,\n\t\t\t\t\t\tsalesanalysis.cust,\n\t\t\t\t\t\tsalesanalysis.custbranch,\n\t\t\t\t\t\tsalesanalysis.area,\n\t\t\t\t\t\tsalesanalysis.periodno,\n\t\t\t\t\t\tsalesanalysis.typeabbrev,\n\t\t\t\t\t\tsalesanalysis.salesperson";
$Result = api_DB_query($SQL, $db, '', '', true);
$myrow = DB_fetch_row($Result);
if ($myrow[0] > 0) {
/*Update the existing record that already exists */
$SQL = "UPDATE salesanalysis\n\t\t\t\t\t\tSET amt=amt+" . $CN_Line['price'] * $CN_Line['qty'] / $CN_Header['rate'] . ",\n\t\t\t\t\t\tqty=qty +" . $CN_Line['qty'] . ",\n\t\t\t\t\t\tdisc=disc+" . $CN_Line['discountpercent'] * $CN_Line['price'] * $CN_Line['qty'] / $CN_Header['rate'] . "\n\t\t\t\t\t\tWHERE salesanalysis.area='" . $myrow[2] . "'\n\t\t\t\t\t\tAND salesanalysis.salesperson='" . $myrow[3] . "'\n\t\t\t\t\t\tAND typeabbrev ='" . $Header['tpe'] . "'\n\t\t\t\t\t\tAND periodno = '" . $PeriodNo . "'\n\t\t\t\t\t\tAND cust " . LIKE . " '" . $Header['debtorno'] . "'\n\t\t\t\t\t\tAND custbranch " . LIKE . " '" . $Header['branchcode'] . "'\n\t\t\t\t\t\tAND stockid " . LIKE . " '" . $CN_Line['stockid'] . "'\n\t\t\t\t\t\tAND salesanalysis.stkcategory ='" . $myrow[1] . "'\n\t\t\t\t\t\tAND budgetoractual='1'";
} else {
/* insert a new sales analysis record */
$SQL = "INSERT INTO salesanalysis (\ttypeabbrev,\n\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\tamt,\n\t\t\t\t\t\t\t\t\t\t\t\t\tcost,\n\t\t\t\t\t\t\t\t\t\t\t\t\tcust,\n\t\t\t\t\t\t\t\t\t\t\t\t\tcustbranch,\n\t\t\t\t\t\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\t\t\t\t\t\tdisc,\n\t\t\t\t\t\t\t\t\t\t\t\t\tstockid,\n\t\t\t\t\t\t\t\t\t\t\t\t\tarea,\n\t\t\t\t\t\t\t\t\t\t\t\t\tbudgetoractual,\n\t\t\t\t\t\t\t\t\t\t\t\t\tsalesperson,\n\t\t\t\t\t\t\t\t\t\t\t\t\tstkcategory )\n\t\t\t\t\t\t\t\tSELECT '" . $Header['tpe'] . "',\n\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t'" . $CN_Line['price'] * $CN_Line['qty'] / $CN_Header['rate'] . "',\n\t\t\t\t\t\t\t\t\t0,\n\t\t\t\t\t\t\t\t\t'" . $Header['debtorno'] . "',\n\t\t\t\t\t\t\t\t\t'" . $Header['branchcode'] . "',\n\t\t\t\t\t\t\t\t\t'" . $CN_Line['qty'] . "',\n\t\t\t\t\t\t\t\t\t'" . $CN_Line['discountpercent'] * $CN_Line['price'] * $CN_Line['qty'] / $CN_Header['rate'] . "',\n\t\t\t\t\t\t\t\t\t'" . $CN_Line['stockid'] . "',\n\t\t\t\t\t\t\t\t\tcustbranch.area,\n\t\t\t\t\t\t\t\t\t1,\n\t\t\t\t\t\t\t\t\tcustbranch.salesman,\n\t\t\t\t\t\t\t\t\tstockmaster.categoryid\n\t\t\t\t\t\t\t\tFROM stockmaster, custbranch\n\t\t\t\t\t\t\t\tWHERE stockmaster.stockid = '" . $CN_Line['stockid'] . "'\n\t\t\t\t\t\t\t\tAND custbranch.debtorno = '" . $Header['debtorno'] . "'\n\t\t\t\t\t\t\t\tAND custbranch.branchcode='" . $Header['branchcode'] . "'";
}
$Result = api_DB_query($SQL, $db, '', '', true);
if ($CompanyRecord['gllink_stock'] == 1 and $StandardCost != 0) {
/*first the cost of sales entry - GL accounts are retrieved using the function GetCOGSGLAccount from includes/GetSalesTransGLCodes.inc */
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\tVALUES (11,\n\t\t\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Header['trandate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . GetCOGSGLAccount($CN_Header['area'], $CN_Line['stockid'], $Header['tpe'], $db) . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Header['debtorno'] . " - " . $CN_Line['stockid'] . " x " . $CN_Line['qty'] . " @ " . $StandardCost . "',\n\t\t\t\t\t\t\t\t\t\t'" . $StandardCost * $CN_Line['qty'] . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
/*now the stock entry - this is set to the cost act in the case of a fixed asset disposal */
$StockGLCode = GetStockGLCode($CN_Line['stockid'], $db);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\tVALUES (11,\n\t\t\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Header['trandate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $StockGLCode['stockact'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Header['debtorno'] . " - " . $CN_Line['stockid'] . " x " . $CN_Line['qty'] . " @ " . $StandardCost . "',\n\t\t\t\t\t\t\t\t\t\t'" . -$StandardCost * $CN_Line['qty'] . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
}
/* end of if GL and stock integrated and standard cost !=0 and not an asset */
if ($CompanyRecord['gllink_debtors'] == 1 and $CN_Line['price'] != 0) {
//Post sales transaction to GL credit sales
$SalesGLAccounts = GetSalesGLAccount($CN_Header['area'], $CN_Line['stockid'], $Header['tpe'], $db);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount )\n\t\t\t\t\tVALUES ('11',\n\t\t\t\t\t\t'" . $CreditNoteNo . "',\n\t\t\t\t\t\t'" . $Header['trandate'] . "',\n\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t'" . $SalesGLAccounts['salesglcode'] . "',\n\t\t\t\t\t\t'" . $Header['debtorno'] . " - " . $CN_Line['stockid'] . " x " . $CN_Line['qty'] . " @ " . $CN_Line['price'] . "',\n\t\t\t\t\t\t'" . -$CN_Line['price'] * $CN_Line['qty'] / $CN_Header['rate'] . "'\n\t\t\t\t\t)";
$Result = api_DB_query($SQL, $db, '', '', true);
if ($CN_Line['discountpercent'] != 0) {
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\tVALUES (11,\n\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\n\t\t\t\t\t\t\t\t'" . $Header['trandate'] . "',\n\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t'" . $SalesGLAccounts['discountglcode'] . "',\n\t\t\t\t\t\t\t\t'" . $Header['debtorno'] . " - " . $CN_Line['stockid'] . " @ " . $CN_Line['discountpercent'] * 100 . "%',\n\t\t\t\t\t\t\t\t'" . $CN_Line['price'] * $CN_Line['qty'] * $CN_Line['discountpercent'] / $CN_Header['rate'] . "')";
$Result = DB_query($SQL, $db, '', '', true);
}
/*end of if discount !=0 */
}
/*end of if sales integrated with gl */
$LineCounter++;
//needed for the array of taxes by line
}
/*end of OrderLine loop */
$TotalCreditLocalCurr = ($TotalFXNetCredit + $TotalFXTax) / $CN_Header['rate'];
if ($CompanyRecord['gllink_debtors'] == 1) {
/*Now post the tax to the GL at local currency equivalent */
if ($CompanyRecord['gllink_debtors'] == 1 and $TaxAuthAmount != 0) {
/*Loop through the tax authorities array to post each total to the taxauth glcode */
foreach ($TaxTotals as $Tax) {
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\t\tamount )\n\t\t\t\t\t\t\t\t\t\t\tVALUES (11,\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'" . $Header['trandate'] . "',\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'" . $Tax['GLCode'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $Header['debtorno'] . "-" . $Tax['TaxAuthDescription'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . -$Tax['FXAmount'] / $CN_Header['rate'] . "' )";
$Result = api_DB_query($SQL, $db, '', '', true);
}
}
/*Post debtors transaction to GL credit debtors, and debit sales */
if ($TotalCreditLocalCurr != 0) {
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\tVALUES ('11',\n\t\t\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Header['trandate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $CompanyRecord['debtorsact'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Header['debtorno'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $TotalCreditLocalCurr . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
}
EnsureGLEntriesBalance(11, $CreditNoteNo, $db);
}
/*end of if Sales and GL integrated */
/*Now insert the DebtorTrans */
$SQL = "INSERT INTO debtortrans (transno,\n\t\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\tinputdate,\n\t\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\t\t\ttpe,\n\t\t\t\t\t\t\t\t\t\tovamount,\n\t\t\t\t\t\t\t\t\t\tovgst,\n\t\t\t\t\t\t\t\t\t\trate,\n\t\t\t\t\t\t\t\t\t\tshipvia)\n\t\t\t\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\n\t\t\t\t\t\t\t\t\t\t11,\n\t\t\t\t\t\t\t\t\t\t'" . $Header['debtorno'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Header['branchcode'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Header['trandate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . date('Y-m-d H-i-s') . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Header['customerref'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Header['tpe'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $TotalFXNetCredit . "',\n\t\t\t\t\t\t\t\t\t\t'" . $TotalFXTax . "',\n\t\t\t\t\t\t\t\t\t\t'" . $CN_Header['rate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Header['shipvia'] . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
$DebtorTransID = DB_Last_Insert_ID($db, 'debtortrans', 'id');
/*for each Tax - need to insert into debtortranstaxes */
foreach ($TaxTotals as $TaxAuthID => $Tax) {
$SQL = "INSERT INTO debtortranstaxes (debtortransid,\n\t\t\t\t\t\t\t\t\t\t\t\ttaxauthid,\n\t\t\t\t\t\t\t\t\t\t\t\ttaxamount)\n\t\t\t\t\t\t\t\tVALUES ('" . $DebtorTransID . "',\n\t\t\t\t\t\t\t\t\t\t'" . $TaxAuthID . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Tax['FXAmount'] / $CN_Header['rate'] . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
}
#Now figure out if there was an invoice in the same POS transaction to allocate against?
$SQL = "SELECT id,\n\t\t\t\t\tovamount+ovgst AS total,\n\t\t\t\t\talloc\n\t\t\t\tFROM debtortrans\n\t\t\t\tWHERE customerref='" . $Header['customerref'] . "'\n\t\t\t\tAND type=10\n\t\t\t\tAND settled=0";
$Result = api_DB_query($SQL, $db, '', '', true);
$TotalCreditFX = $TotalFXNetCredit + $TotalFXTax;
#Should be negative number
$Allocated = 0;
if (DB_num_rows($Result) > 0) {
while ($InvoiceRow = DB_fetch_array($Result) and $Allocated > $TotalCreditFX) {
if ($InvoiceRow['total'] - $InvoiceRow['alloc'] + $TotalCreditFX - $Allocated > 0) {
/*Then we can allocate all of the (remaining) credit against this invoice */
$AllocateAmount = $InvoiceRow['total'] - $InvoiceRow['alloc'] + $TotalCreditFX - $Allocated;
} elseif ($InvoiceRow['total'] - $InvoiceRow['alloc'] - $Allocated > 0) {
$AllocateAmount = $InvoiceRow['total'] - $InvoiceRow['alloc'] - $Allocated;
} else {
$AllocateAmount = 0;
}
if ($AllocateAmount > 0) {
$SQL = "INSERT INTO\tcustallocns (datealloc,\n\t\t\t\t\t\t\t\t\t\t\t\t\t amt,\n\t\t\t\t\t\t\t\t\t\t\t\t\t transid_allocfrom,\n\t\t\t\t\t\t\t\t\t\t\t\t\t transid_allocto)\n\t\t\t\t\t\t\tVALUES ('" . date('Y-m-d') . "',\n\t\t\t\t\t\t\t\t\t'" . $AllocateAmount . "',\n\t\t\t\t\t\t\t\t\t'" . $DebtorTransID . "',\n\t\t\t\t\t\t\t\t\t'" . $InvoiceRow['id'] . "')";
$InsertAllocResult = api_DB_query($SQL, $db, '', '', true);
}
if (abs($InvoiceRow['total'] - $InvoiceRow['alloc'] - $AllocateAmount) < 0.005) {
$Settled = 1;
} else {
$Settled = 0;
}
$SQL = "UPDATE debtortrans SET alloc = alloc + " . $AllocateAmount . ",\n\t\t\t\t\t\t\t\t\t\t\t\tsettled = '" . $Settled . "'\n\t\t\t\t\t\tWHERE id = '" . $InvoiceRow['id'] . "'";
$UpdateAllocResult = api_DB_query($SQL, $db, '', '', true);
$Allocated -= $AllocateAmount;
}
if (abs($TotalCreditFX - $Allocated) < 0.005) {
$Settled = 1;
} else {
$Settled = 0;
}
$SQL = "UPDATE debtortrans SET alloc = alloc + " . $Allocated . ",\n\t\t\t\t\t\t\t\t\t\t\t\tsettled = '" . $Settled . "'\n\t\t\t\t\tWHERE id = '" . $DebtorTransID . "'";
$UpdateAllocResult = api_DB_query($SQL, $db, '', '', true);
}
if (sizeof($Errors) == 0) {
$Result = DB_Txn_Commit($db);
$Errors[0] = 0;
$Errors[1] = $CreditNoteNo;
} else {
$Result = DB_Txn_Rollback($db);
}
return $Errors;
}
}
if ($_SESSION['CreditItems' . $identifier]->FreightCost != 0) {
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\tVALUES (11,\n\t\t\t\t\t\t\t\t'" . $CreditNo . "',\n\t\t\t\t\t\t\t\t'" . $SQLCreditDate . "',\n\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t'" . $_SESSION['CompanyRecord']['freightact'] . "',\n\t\t\t\t\t\t\t\t'" . $_SESSION['CreditItems' . $identifier]->DebtorNo . "',\n\t\t\t\t\t\t\t\t'" . $_SESSION['CreditItems' . $identifier]->FreightCost / $_SESSION['CurrencyRate'] . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The freight GL posting for this credit note could not be inserted because');
$DbgMsg = _('The following SQL to insert the GLTrans record was used');
$Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
}
foreach ($TaxTotals as $TaxAuthID => $TaxAmount) {
if ($TaxAmount != 0) {
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount )\n\t\t\t\t\t\t\t\t\t\tVALUES (11,\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $SQLCreditDate . "',\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'" . $TaxGLCodes[$TaxAuthID] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['CreditItems' . $identifier]->DebtorNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $TaxAmount / $_SESSION['CurrencyRate'] . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The tax GL posting could not be inserted because');
$DbgMsg = _('The following SQL to insert the GLTrans record was used');
$Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
}
}
EnsureGLEntriesBalance(11, $CreditNo, $db);
}
/*end of if Sales and GL integrated */
DB_Txn_Commit();
unset($_SESSION['CreditItems' . $identifier]->LineItems);
unset($_SESSION['CreditItems' . $identifier]);
echo _('Credit Note number') . ' ' . $CreditNo . ' ' . _('processed') . '<br />';
echo '<a target="_blank" href="' . $RootPath . '/PrintCustTrans.php?FromTransNo=' . $CreditNo . '&InvOrCredit=Credit">' . _('Show this Credit Note on screen') . '</a><br />';
if ($_SESSION['InvoicePortraitFormat'] == 0) {
echo '<a href="' . $RootPath . '/PrintCustTrans.php?FromTransNo=' . $CreditNo . '&InvOrCredit=Credit&PrintPDF=True">' . _('Print this Credit Note') . '</a>';
} else {
echo '<a href="' . $RootPath . '/PrintCustTransPortrait.php?FromTransNo=' . $CreditNo . '&InvOrCredit=Credit&PrintPDF=True">' . _('Print this Credit Note') . '</a>';
}
echo '<br /><a href="' . $RootPath . '/SelectCreditItems.php">' . _('Enter Another Credit Note') . '</a>';
}
/*end of process credit note */
$result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
}
// end if discount
}
// end if gl creditors
}
// end if supplier
if ($_SESSION['CompanyRecord']['gllink_creditors'] == 1) {
/* then do the common GLTrans */
if ($_SESSION['PaymentDetail' . $identifier]->Amount != 0) {
/* Bank account entry first */
$SQL = "INSERT INTO gltrans ( type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\tVALUES ('" . $TransType . "',\n\t\t\t\t\t\t\t\t'" . $TransNo . "',\n\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_SESSION['PaymentDetail' . $identifier]->DatePaid) . "',\n\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t'" . $_SESSION['PaymentDetail' . $identifier]->Account . "',\n\t\t\t\t\t\t\t\t'" . $_SESSION['PaymentDetail' . $identifier]->Narrative . "',\n\t\t\t\t\t\t\t\t'" . -$_SESSION['PaymentDetail' . $identifier]->Amount / $_SESSION['PaymentDetail' . $identifier]->ExRate / $_SESSION['PaymentDetail' . $identifier]->FunctionalExRate . "')";
$ErrMsg = _('Cannot insert a GL transaction for the bank account credit because');
$DbgMsg = _('Cannot insert a GL transaction for the bank account credit using the SQL');
$result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
EnsureGLEntriesBalance($TransType, $TransNo, $db);
}
}
/*now enter the BankTrans entry */
if ($TransType == 22) {
$SQL = "INSERT INTO banktrans (transno,\n\t\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\t\tbankact,\n\t\t\t\t\t\t\t\t\t\tref,\n\t\t\t\t\t\t\t\t\t\texrate,\n\t\t\t\t\t\t\t\t\t\tfunctionalexrate,\n\t\t\t\t\t\t\t\t\t\ttransdate,\n\t\t\t\t\t\t\t\t\t\tbanktranstype,\n\t\t\t\t\t\t\t\t\t\tamount,\n\t\t\t\t\t\t\t\t\t\tcurrcode)\n\t\t\t\t\t\t\tVALUES ('" . $TransNo . "',\n\t\t\t\t\t\t\t\t\t'" . $TransType . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['PaymentDetail' . $identifier]->Account . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['PaymentDetail' . $identifier]->Narrative . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['PaymentDetail' . $identifier]->ExRate . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['PaymentDetail' . $identifier]->FunctionalExRate . "',\n\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_SESSION['PaymentDetail' . $identifier]->DatePaid) . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['PaymentDetail' . $identifier]->Paymenttype . "',\n\t\t\t\t\t\t\t\t\t'" . -$_SESSION['PaymentDetail' . $identifier]->Amount . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['PaymentDetail' . $identifier]->Currency . "'\n\t\t\t\t\t\t\t\t)";
$ErrMsg = _('Cannot insert a bank transaction because');
$DbgMsg = _('Cannot insert a bank transaction using the SQL');
$result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
} else {
foreach ($_SESSION['PaymentDetail' . $identifier]->GLItems as $PaymentItem) {
$SQL = "INSERT INTO banktrans (transno,\n\t\t\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\t\t\tbankact,\n\t\t\t\t\t\t\t\t\t\t\tref,\n\t\t\t\t\t\t\t\t\t\t\texrate,\n\t\t\t\t\t\t\t\t\t\t\tfunctionalexrate,\n\t\t\t\t\t\t\t\t\t\t\ttransdate,\n\t\t\t\t\t\t\t\t\t\t\tbanktranstype,\n\t\t\t\t\t\t\t\t\t\t\tamount,\n\t\t\t\t\t\t\t\t\t\t\tcurrcode)\n\t\t\t\t\t\tVALUES ('" . $TransNo . "',\n\t\t\t\t\t\t\t\t'" . $TransType . "',\n\t\t\t\t\t\t\t\t'" . $_SESSION['PaymentDetail' . $identifier]->Account . "',\n\t\t\t\t\t\t\t\t'" . $_SESSION['PaymentDetail' . $identifier]->Narrative . "',\n\t\t\t\t\t\t\t\t'" . $_SESSION['PaymentDetail' . $identifier]->ExRate . "',\n\t\t\t\t\t\t\t\t'" . $_SESSION['PaymentDetail' . $identifier]->FunctionalExRate . "',\n\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_SESSION['PaymentDetail' . $identifier]->DatePaid) . "',\n\t\t\t\t\t\t\t\t'" . $_SESSION['PaymentDetail' . $identifier]->Paymenttype . "',\n\t\t\t\t\t\t\t\t'" . -$PaymentItem->Amount . "',\n\t\t\t\t\t\t\t\t'" . $_SESSION['PaymentDetail' . $identifier]->Currency . "' )";
$ErrMsg = _('Cannot insert a bank transaction because');
$DbgMsg = _('Cannot insert a bank transaction using the SQL');
$result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
}
$SQL = "UPDATE locstock SET quantity = quantity + " . floatval($_SESSION['Adjustment' . $identifier]->Quantity) . "\n\t\t\t\tWHERE stockid='" . $_SESSION['Adjustment' . $identifier]->StockID . "'\n\t\t\t\tAND loccode='" . $_SESSION['Adjustment' . $identifier]->StockLocation . "'";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The location stock record could not be updated because');
$DbgMsg = _('The following SQL to update the stock record was used');
$Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
if ($_SESSION['CompanyRecord']['gllink_stock'] == 1 and $_SESSION['Adjustment' . $identifier]->StandardCost > 0) {
$StockGLCodes = GetStockGLCode($_SESSION['Adjustment' . $identifier]->StockID, $db);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\tamount,\n\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\ttag)\n\t\t\t\t\t\t\t\tVALUES (17,\n\t\t\t\t\t\t\t\t\t'" . $AdjustmentNumber . "',\n\t\t\t\t\t\t\t\t\t'" . $SQLAdjustmentDate . "',\n\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t'" . $StockGLCodes['adjglact'] . "',\n\t\t\t\t\t\t\t\t\t'" . round($_SESSION['Adjustment' . $identifier]->StandardCost * -$_SESSION['Adjustment' . $identifier]->Quantity, $_SESSION['CompanyRecord']['decimalplaces']) . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['Adjustment' . $identifier]->StockID . " x " . $_SESSION['Adjustment' . $identifier]->Quantity . " @ " . $_SESSION['Adjustment' . $identifier]->StandardCost . " " . $_SESSION['Adjustment' . $identifier]->Narrative . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['Adjustment' . $identifier]->tag . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction entries could not be added because');
$DbgMsg = _('The following SQL to insert the GL entries was used');
$Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\tamount,\n\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\ttag)\n\t\t\t\t\t\t\t\tVALUES (17,\n\t\t\t\t\t\t\t\t\t'" . $AdjustmentNumber . "',\n\t\t\t\t\t\t\t\t\t'" . $SQLAdjustmentDate . "',\n\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t'" . $StockGLCodes['stockact'] . "',\n\t\t\t\t\t\t\t\t\t'" . round($_SESSION['Adjustment' . $identifier]->StandardCost * $_SESSION['Adjustment' . $identifier]->Quantity, $_SESSION['CompanyRecord']['decimalplaces']) . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['Adjustment' . $identifier]->StockID . ' x ' . $_SESSION['Adjustment' . $identifier]->Quantity . ' @ ' . $_SESSION['Adjustment' . $identifier]->StandardCost . ' ' . $_SESSION['Adjustment' . $identifier]->Narrative . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['Adjustment' . $identifier]->tag . "'\n\t\t\t\t\t\t\t\t\t)";
$Errmsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction entries could not be added because');
$DbgMsg = _('The following SQL to insert the GL entries was used');
$Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
}
EnsureGLEntriesBalance(17, $AdjustmentNumber, $db);
$Result = DB_Txn_Commit();
$ConfirmationText = _('A stock adjustment for') . ' ' . $_SESSION['Adjustment' . $identifier]->StockID . ' - ' . $_SESSION['Adjustment' . $identifier]->ItemDescription . ' ' . _('has been created from location') . ' ' . $_SESSION['Adjustment' . $identifier]->StockLocation . ' ' . _('for a quantity of') . ' ' . locale_number_format($_SESSION['Adjustment' . $identifier]->Quantity, $_SESSION['Adjustment' . $identifier]->DecimalPlaces);
prnMsg($ConfirmationText, 'success');
if ($_SESSION['InventoryManagerEmail'] != '') {
$ConfirmationText = $ConfirmationText . ' ' . _('by user') . ' ' . $_SESSION['UserID'] . ' ' . _('at') . ' ' . Date('Y-m-d H:i:s');
$EmailSubject = _('Stock adjustment for') . ' ' . $_SESSION['Adjustment' . $identifier]->StockID;
if ($_SESSION['SmtpSetting'] == 0) {
mail($_SESSION['InventoryManagerEmail'], $EmailSubject, $ConfirmationText);
} else {
include 'includes/htmlMimeMail.php';
$mail = new htmlMimeMail();
$mail->setSubject($EmailSubject);
$mail->setText($ConfirmationText);
$result = SendmailBySmtp($mail, array($_SESSION['InventoryManagerEmail']));
}
$SQL = "INSERT INTO gltrans (\n\t\t\t\t\t\ttype,\n\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\taccount,\n\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\tamount\t)\n\t\t\t\tVALUES (\n\t\t\t\t\t10,\n\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t'" . $DefaultDispatchDate . "',\n\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t'" . $_SESSION['CompanyRecord']['freightact'] . "',\n\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->DebtorNo . "',\n\t\t\t\t\t'" . -$_SESSION['Items' . $identifier]->FreightCost / $_SESSION['CurrencyRate'] . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The freight GL posting could not be inserted because');
$DbgMsg = _('The following SQL to insert the GLTrans record was used');
$Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
}
foreach ($TaxTotals as $TaxAuthID => $TaxAmount) {
if ($TaxAmount != 0) {
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t\t\t\t\t10,\n\t\t\t\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $DefaultDispatchDate . "',\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'" . $TaxGLCodes[$TaxAuthID] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->DebtorNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . -$TaxAmount / $_SESSION['CurrencyRate'] . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The tax GL posting could not be inserted because');
$DbgMsg = _('The following SQL to insert the GLTrans record was used');
$Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
}
}
}
/*end of if Sales and GL integrated */
EnsureGLEntriesBalance(10, $InvoiceNo, $db);
DB_Txn_Commit();
// *************************************************************************
// E N D O F I N V O I C E S Q L P R O C E S S I N G
// *************************************************************************
unset($_SESSION['Items' . $identifier]->LineItems);
unset($_SESSION['Items' . $identifier]);
unset($_SESSION['ProcessingOrder']);
echo prnMsg(_('Invoice number') . ' ' . $InvoiceNo . ' ' . _('processed'), 'success');
echo '<br /><div class="centre">';
if ($_SESSION['InvoicePortraitFormat'] == 0) {
echo '<img src="' . $RootPath . '/css/' . $Theme . '/images/printer.png" title="' . _('Print') . '" alt="" />' . ' ' . '<a target="_blank" href="' . $RootPath . '/PrintCustTrans.php?FromTransNo=' . $InvoiceNo . '&InvOrCredit=Invoice&PrintPDF=True">' . _('Print this invoice') . ' (' . _('Landscape') . ')</a><br /><br />';
} else {
echo '<img src="' . $RootPath . '/css/' . $Theme . '/images/printer.png" title="' . _('Print') . '" alt="" />' . ' ' . '<a target="_blank" href="' . $RootPath . '/PrintCustTransPortrait.php?FromTransNo=' . $InvoiceNo . '&InvOrCredit=Invoice&PrintPDF=True">' . _('Print this invoice') . ' (' . _('Portrait') . ')</a><br /><br />';
}
echo '<a href="' . $RootPath . '/SelectSalesOrder.php">' . _('Select another order for invoicing') . '</a><br /><br />';
/*Also if GL is linked to debtors need to process the debit to bank and credit to debtors for the payment */
/*Need to figure out the cross rate between customer currency and bank account currency */
if ($_POST['AmountPaid'] != 0) {
$ReceiptNumber = GetNextTransNo(12, $db);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\tVALUES (12,\n\t\t\t\t\t\t\t'" . $ReceiptNumber . "',\n\t\t\t\t\t\t\t'" . $DefaultDispatchDate . "',\n\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t'" . $_POST['BankAccount'] . "',\n\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->LocationName . ' ' . _('Counter Sale') . ' ' . $InvoiceNo . "',\n\t\t\t\t\t\t\t'" . filter_number_format($_POST['AmountPaid']) / $ExRate . "')";
$DbgMsg = _('The SQL that failed to insert the GL transaction for the bank account debit was');
$ErrMsg = _('Cannot insert a GL transaction for the bank account debit');
$result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
/* Now Credit Debtors account with receipt */
$SQL = "INSERT INTO gltrans ( type,\n\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\taccount,\n\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\tamount)\n\t\t\t\tVALUES (12,\n\t\t\t\t\t'" . $ReceiptNumber . "',\n\t\t\t\t\t'" . $DefaultDispatchDate . "',\n\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t'" . $_SESSION['CompanyRecord']['debtorsact'] . "',\n\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->LocationName . ' ' . _('Counter Sale') . ' ' . $InvoiceNo . "',\n\t\t\t\t\t'" . -(filter_number_format($_POST['AmountPaid']) / $ExRate) . "')";
$DbgMsg = _('The SQL that failed to insert the GL transaction for the debtors account credit was');
$ErrMsg = _('Cannot insert a GL transaction for the debtors account credit');
$result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
}
//amount paid we not zero
EnsureGLEntriesBalance(12, $ReceiptNumber, $db);
}
/*end of if Sales and GL integrated */
if ($_POST['AmountPaid'] != 0) {
if (!isset($ReceiptNumber)) {
$ReceiptNumber = GetNextTransNo(12, $db);
}
//Now need to add the receipt banktrans record
//First get the account currency that it has been banked into
$result = DB_query("SELECT rate FROM currencies\n\t\t\t\t\t\t\t\tINNER JOIN bankaccounts\n\t\t\t\t\t\t\t\tON currencies.currabrev=bankaccounts.currcode\n\t\t\t\t\t\t\t\tWHERE bankaccounts.accountcode='" . $_POST['BankAccount'] . "'", $db);
$myrow = DB_fetch_row($result);
$BankAccountExRate = $myrow[0];
/*
* Some interesting exchange rate conversion going on here
* Say :
* The business's functional currency is NZD
$DbgMsg = _('The following SQL to insert the GRN Suspense GLTrans record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
}
/* end of if GL and stock integrated and standard cost !=0 */
}
/*Quantity received is != 0 */
}
/*end of OrderLine loop */
if ($_SESSION['PO' . $identifier]->AllLinesReceived() == 1 or $OrderCompleted) {
//all lines on the purchase order are now completed
$StatusComment = date($_SESSION['DefaultDateFormat']) . ' - ' . _('Order Completed on entry of GRN') . '<br />' . $_SESSION['PO' . $identifier]->StatusComments;
$sql = "UPDATE purchorders\n\t\t\t\tSET status='Completed',\n\t\t\t\tstat_comment='" . $StatusComment . "'\n\t\t\t\tWHERE orderno='" . $_SESSION['PO' . $identifier]->OrderNo . "'";
$result = DB_query($sql, $db);
}
if ($_SESSION['PO' . $identifier]->GLLink == 1) {
EnsureGLEntriesBalance(25, $GRN, $db);
}
$Result = DB_Txn_Commit($db);
$PONo = $_SESSION['PO' . $identifier]->OrderNo;
unset($_SESSION['PO' . $identifier]->LineItems);
unset($_SESSION['PO' . $identifier]);
unset($_POST['ProcessGoodsReceived']);
echo '<br />
<div class="centre">
' . prnMsg(_('GRN number') . ' ' . $GRN . ' ' . _('has been processed'), 'success') . '
<br />
<br />
<a href="PDFGrn.php?GRNNo=' . $GRN . '&PONo=' . $PONo . '">' . _('Print this Goods Received Note (GRN)') . '</a>
<br />
<br />
<a href="' . $RootPath . '/PO_SelectOSPurchOrder.php">' . _('Select a different purchase order for receiving goods against') . '</a>
include 'footer.inc';
exit;
}
/*Do the GL trans for the exch diff */
if ($AccumDiffOnExch != 0) {
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\tamount )\n\t\t\t\t\t\t\t\t\tVALUES (22,\n\t\t\t\t\t\t\t\t\t\t'" . $SuppPaymentNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_POST['AmountsDueBy']) . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['CompanyRecord']['purchasesexchangediffact'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $SupplierID . ' - ' . $SupplierName . ' ' . _('payment run on') . ' ' . Date($_SESSION['DefaultDateFormat']) . " - " . $PaytReference . "',\n\t\t\t\t\t\t\t\t\t\t'" . -$AccumDiffOnExch . "')";
$ProcessResult = DB_query($SQL, $db, '', '', false, false);
if (DB_error_no($db) != 0) {
$Title = _('Payment Processing - Problem Report');
include 'header.inc';
prnMsg(_('None of the payments will be processed since the general ledger posting for the exchange difference on') . ' ' . $SupplierName . ' ' . _('could not be inserted because') . ' - ' . DB_error_msg($db), 'error');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
prnMsg(_('The SQL that failed was: ') . '<br />' . $SQL, 'error');
}
$ProcessResult = DB_Txn_Rollback($db);
include 'footer.inc';
exit;
}
}
EnsureGLEntriesBalance(22, $SuppPaymentNo, $db);
}
/*end if GL linked to creditors */
}
$YPos -= 1.5 * $line_height;
$pdf->line($Left_Margin, $YPos + $line_height, $Page_Width - $Right_Margin, $YPos + $line_height);
$YPos -= $line_height;
if ($YPos < $Bottom_Margin + $line_height) {
$PageNumber++;
include 'PDFPaymentRunPageHeader.inc';
}
function InvoiceSalesOrder($OrderNo, $User, $Password)
{
$Errors = array();
$db = db($User, $Password);
if (gettype($db) == 'integer') {
$Errors[] = NoAuthorisation;
return $Errors;
}
$Errors = VerifyOrderHeaderExists($OrderNo, sizeof($Errors), $Errors, $db);
if (sizeof($Errors) != 0) {
return $Errors;
}
/*Does not deal with assembly items or serialise/lot track items - for use by POS */
/*Get Company Defaults */
$ReadCoyResult = api_DB_query("SELECT debtorsact,\n\t\t\t\t\t\t\t\t\t\t\t\tfreightact,\n\t\t\t\t\t\t\t\t\t\t\t\tgllink_debtors,\n\t\t\t\t\t\t\t\t\t\t\t\tgllink_stock\n\t\t\t\t\t\t\t\t\t\tFROM companies\n\t\t\t\t\t\t\t\t\t\tWHERE coycode=1", $db);
$CompanyRecord = DB_fetch_array($ReadCoyResult);
if (DB_error_no($db) != 0) {
$Errors[] = NoCompanyRecord;
}
$OrderHeaderSQL = "SELECT salesorders.debtorno,\n\t\t\t\t \t\t\t\t debtorsmaster.name,\n\t\t\t\t\t\t\t\t salesorders.branchcode,\n\t\t\t\t\t\t\t\t salesorders.customerref,\n\t\t\t\t\t\t\t\t salesorders.orddate,\n\t\t\t\t\t\t\t\t salesorders.ordertype,\n\t\t\t\t\t\t\t\t salesorders.shipvia,\n\t\t\t\t\t\t\t\t custbranch.area,\n\t\t\t\t\t\t\t\t custbranch.taxgroupid,\n\t\t\t\t\t\t\t\t debtorsmaster.currcode,\n\t\t\t\t\t\t\t\t currencies.rate,\n\t\t\t\t\t\t\t\t salesorders.fromstkloc,\n\t\t\t\t\t\t\t\t custbranch.salesman\n\t\t\t\t\t\t\tFROM salesorders\n\t\t\t\t\t\t\tINNER JOIN debtorsmaster\n\t\t\t\t\t\t\tON salesorders.debtorno = debtorsmaster.debtorno\n\t\t\t\t\t\t\tINNER JOIN custbranch\n\t\t\t\t\t\t\tON salesorders.debtorno = custbranch.debtorno\n\t\t\t\t\t\t\tAND salesorders.branchcode = custbranch.branchcode\n\t\t\t\t\t\t\tINNER JOIN locations\n\t\t\t\t\t\t\tON locations.loccode=salesorders.fromstkloc\n\t\t\t\t\t\t\tINNER JOIN currencies\n\t\t\t\t\t\t\tON debtorsmaster.currcode=currencies.currabrev\n\t\t\t\t\t\t\tWHERE salesorders.orderno = '" . $OrderNo . "'";
$OrderHeaderResult = api_DB_query($OrderHeaderSQL, $db);
if (DB_error_no($db) != 0) {
$Errors[] = NoReadOrder;
}
$OrderHeader = DB_fetch_array($OrderHeaderResult);
$TaxProvResult = api_DB_query("SELECT taxprovinceid FROM locations WHERE loccode='" . $OrderHeader['fromstkloc'] . "'", $db);
if (DB_error_no($db) != 0) {
$Errors[] = NoTaxProvince;
}
$myrow = DB_fetch_row($TaxProvResult);
$DispTaxProvinceID = $myrow[0];
$LineItemsSQL = "SELECT stkcode,\n\t\t\t\t\t\t\t\tunitprice,\n\t\t\t\t\t\t\t\tquantity,\n\t\t\t\t\t\t\t\tdiscountpercent,\n\t\t\t\t\t\t\t\ttaxcatid,\n\t\t\t\t\t\t\t\tmbflag,\n\t\t\t\t\t\t\t\tmaterialcost+labourcost+overheadcost AS standardcost\n\t\t\t\t\t\tFROM salesorderdetails INNER JOIN stockmaster\n\t\t\t\t\t\tON salesorderdetails.stkcode = stockmaster.stockid\n\t\t\t\t\t\tWHERE orderno ='" . $OrderNo . "'\n\t\t\t\t\t\tAND completed=0";
$LineItemsResult = api_DB_query($LineItemsSQL, $db);
if (DB_error_no($db) != 0 or DB_num_rows($LineItemsResult) == 0) {
$Errors[] = NoReadOrderLines;
return $Errors;
}
/*Start an SQL transaction */
$result = DB_Txn_Begin($db);
/*Now Get the next invoice number - function in SQL_CommonFunctions*/
$InvoiceNo = GetNextTransNo(10, $db);
$PeriodNo = GetCurrentPeriod($db);
$TaxTotals = array();
$TotalFXNetInvoice = 0;
$TotalFXTax = 0;
$LineCounter = 0;
while ($OrderLineRow = DB_fetch_array($LineItemsResult)) {
$StandardCost = $OrderLineRow['standardcost'];
$LocalCurrencyPrice = $OrderLineRow['unitprice'] * (1 - floatval($OrderLineRow['discountpercent'])) / $OrderHeader['rate'];
$LineNetAmount = $OrderLineRow['unitprice'] * $OrderLineRow['quantity'] * (1 - floatval($OrderLineRow['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\t\t\ttaxauthorities.description,\n\t\t\t\t\t\t\ttaxgrouptaxes.taxauthid,\n\t\t\t\t\t\t\ttaxauthorities.taxglcode,\n\t\t\t\t\t\t\ttaxgrouptaxes.taxontax,\n\t\t\t\t\t\t\ttaxauthrates.taxrate\n\t\t\t\t\tFROM taxauthrates INNER JOIN taxgrouptaxes ON\n\t\t\t\t\t\ttaxauthrates.taxauthority=taxgrouptaxes.taxauthid\n\t\t\t\t\t\tINNER JOIN taxauthorities ON\n\t\t\t\t\t\ttaxauthrates.taxauthority=taxauthorities.taxid\n\t\t\t\t\tWHERE taxgrouptaxes.taxgroupid='" . $OrderHeader['taxgroupid'] . "'\n\t\t\t\t\tAND taxauthrates.dispatchtaxprovince='" . $DispTaxProvinceID . "'\n\t\t\t\t\tAND taxauthrates.taxcatid = '" . $OrderLineRow['taxcatid'] . "'\n\t\t\t\t\tORDER BY taxgrouptaxes.calculationorder";
$GetTaxRatesResult = api_DB_query($SQL, $db);
if (DB_error_no($db) != 0) {
$Errors[] = TaxRatesFailed;
}
$LineTaxAmount = 0;
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'];
} else {
$TaxAuthAmount = $LineNetAmount * $myrow['taxrate'];
}
$TaxTotals[$myrow['taxauthid']]['FXAmount'] += $TaxAuthAmount;
/*Make an array of the taxes and amounts including GLcodes for later posting - need debtortransid
so can only post once the debtor trans is posted - can only post debtor trans when all tax is calculated */
$LineTaxes[$LineCounter][$myrow['calculationorder']] = array('TaxCalculationOrder' => $myrow['calculationorder'], 'TaxAuthID' => $myrow['taxauthid'], 'TaxAuthDescription' => $myrow['description'], 'TaxRate' => $myrow['taxrate'], 'TaxOnTax' => $myrow['taxontax'], 'TaxAuthAmount' => $TaxAuthAmount);
$LineTaxAmount += $TaxAuthAmount;
}
//end loop around Taxes
$TotalFXNetInvoice += $LineNetAmount;
$TotalFXTax += $LineTaxAmount;
/*Now update SalesOrderDetails for the quantity invoiced and the actual dispatch dates. */
$SQL = "UPDATE salesorderdetails\n\t\t\t\t\tSET qtyinvoiced = qtyinvoiced + " . $OrderLineRow['quantity'] . ",\n\t\t\t\t\t\tactualdispatchdate = '" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\tcompleted='1'\n\t\t\t\t\tWHERE orderno = '" . $OrderNo . "'\n\t\t\t\t\tAND stkcode = '" . $OrderLineRow['stkcode'] . "'";
$Result = api_DB_query($SQL, $db, '', '', true);
if ($OrderLineRow['mbflag'] == 'B' or $OrderLineRow['mbflag'] == 'M') {
$Assembly = False;
/* Need to get the current location quantity
will need it later for the stock movement */
$SQL = "SELECT locstock.quantity\n\t\t\t\t\t\tFROM locstock\n\t\t\t\t\t\tWHERE locstock.stockid='" . $OrderLineRow['stkcode'] . "'\n\t\t\t\t\t\tAND loccode= '" . $OrderHeader['fromstkloc'] . "'";
$Result = api_DB_query($SQL, $db);
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;
}
$SQL = "UPDATE locstock\n\t\t\t\t\t\tSET quantity = locstock.quantity - " . $OrderLineRow['quantity'] . "\n\t\t\t\t\t\tWHERE locstock.stockid = '" . $OrderLineRow['stkcode'] . "'\n\t\t\t\t\t\tAND loccode = '" . $OrderHeader['fromstkloc'] . "'";
$Result = api_DB_query($SQL, $db, '', '', true);
$SQL = "INSERT INTO stockmoves (stockid,\n\t\t\t\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\t\t\t\ttransno,\n\t\t\t\t\t\t\t\t\t\t\t\tloccode,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\t\t\t\tprice,\n\t\t\t\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\t\t\t\t\tdiscountpercent,\n\t\t\t\t\t\t\t\t\t\t\t\tstandardcost,\n\t\t\t\t\t\t\t\t\t\t\t\tnewqoh)\n\t\t\t\t\t\tVALUES ('" . $OrderLineRow['stkcode'] . "',\n\t\t\t\t\t\t\t\t'10',\n\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['fromstkloc'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['branchcode'] . "',\n\t\t\t\t\t\t\t\t'" . $LocalCurrencyPrice . "',\n\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t'" . $OrderNo . "',\n\t\t\t\t\t\t\t\t'" . -$OrderLineRow['quantity'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderLineRow['discountpercent'] . "',\n\t\t\t\t\t\t\t\t'" . $StandardCost . "',\n\t\t\t\t\t\t\t\t'" . ($QtyOnHandPrior - $OrderLineRow['quantity']) . "' )";
$Result = api_DB_query($SQL, $db, '', '', true);
} else {
if ($OrderLineRow['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,\n\t\t\t\t\t\t\t\tbom.quantity,\n\t\t\t\t\t\t\t\tstockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost AS standard\n\t\t\t\t\t\t\tFROM bom INNER JOIN stockmaster\n\t\t\t\t\t\t\tON bom.component=stockmaster.stockid\n\t\t\t\t\t\t\tWHERE bom.parent='" . $OrderLineRow['stkcode'] . "'\n\t\t\t\t\t\t\tAND bom.effectiveto >= '" . Date('Y-m-d') . "'\n\t\t\t\t\t\t\tAND bom.effectiveafter < '" . Date('Y-m-d') . "'";
$AssResult = api_DB_query($SQL, $db);
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\n\t\t\t\t\t\t\tFROM locstock\n\t\t\t\t\t\t\tWHERE locstock.stockid='" . $AssParts['component'] . "'\n\t\t\t\t\t\t\tAND loccode= '" . $OrderHeader['fromstkloc'] . "'";
$Result = api_DB_query($SQL, $db);
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'])) {
$AssParts['standard'] = 0;
}
$SQL = "INSERT INTO stockmoves (stockid,\n\t\t\t\t\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\t\t\t\t\ttransno,\n\t\t\t\t\t\t\t\t\t\t\t\t\tloccode,\n\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\tdebtorno,\n\t\t\t\t\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\t\t\t\t\t\tstandardcost,\n\t\t\t\t\t\t\t\t\t\t\t\t\tshow_on_inv_crds,\n\t\t\t\t\t\t\t\t\t\t\t\t\tnewqoh)\n\t\t\t\t\t\t\t\t\t\tVALUES ('" . $AssParts['component'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t 10,\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $OrderHeader['fromstkloc'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $DefaultDispatchDate . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $OrderHeader['debtorno'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $OrderHeader['branchcode'] . "',\n\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 '" . _('Assembly') . ': ' . $OrderLineRow['stkcode'] . ' ' . _('Order') . ': ' . $OrderNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . -$AssParts['quantity'] * $OrderLineRow['quantity'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $AssParts['standard'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t 0,\n\t\t\t\t\t\t\t\t\t\t\t\t '" . ($QtyOnHandPrior - $AssParts['quantity'] * $OrderLineRow['quantity']) . "'\t)";
$Result = DB_query($SQL, $db, '', '', true);
$SQL = "UPDATE locstock\n\t\t\t\t\t\t\tSET quantity = locstock.quantity - " . $AssParts['quantity'] * $OrderLineRow['quantity'] . "\n\t\t\t\t\t\t\tWHERE locstock.stockid = '" . $AssParts['component'] . "'\n\t\t\t\t\t\t\tAND loccode = '" . $OrderHeader['fromlocstk'] . "'";
$Result = DB_query($SQL, $db, '', '', true);
}
/* end of assembly explosion and updates */
}
}
/* end of its an assembly */
if ($OrderLineRow['mbflag'] == 'A' or $OrderLineRow['mbflag'] == 'D') {
/*it's a Dummy/Service item or an Assembly item - still need stock movement record
* but quantites on hand are always nil */
$SQL = "INSERT INTO stockmoves (stockid,\n\t\t\t\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\t\t\t\ttransno,\n\t\t\t\t\t\t\t\t\t\t\t\tloccode,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\t\t\t\tprice,\n\t\t\t\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\t\t\t\t\tdiscountpercent,\n\t\t\t\t\t\t\t\t\t\t\t\tstandardcost,\n\t\t\t\t\t\t\t\t\t\t\t\tnewqoh)\n\t\t\t\t\t\tVALUES ('" . $OrderLineRow['stkcode'] . "',\n\t\t\t\t\t\t\t\t'10',\n\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['fromstkloc'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['branchcode'] . "',\n\t\t\t\t\t\t\t\t'" . $LocalCurrencyPrice . "',\n\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t'" . $OrderNo . "',\n\t\t\t\t\t\t\t\t'" . -$OrderLineRow['quantity'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderLineRow['discountpercent'] . "',\n\t\t\t\t\t\t\t\t'" . $StandardCost . "',\n\t\t\t\t\t\t\t\t'0' )";
$Result = api_DB_query($SQL, $db, '', '', true);
}
/*Get the ID of the StockMove... */
$StkMoveNo = DB_Last_Insert_ID($db, 'stockmoves', 'stkmoveno');
/*Insert the taxes that applied to this line */
foreach ($LineTaxes[$LineCounter] as $Tax) {
$SQL = "INSERT INTO stockmovestaxes (stkmoveno,\n\t\t\t\t\t\t\t\t\ttaxauthid,\n\t\t\t\t\t\t\t\t\ttaxrate,\n\t\t\t\t\t\t\t\t\ttaxcalculationorder,\n\t\t\t\t\t\t\t\t\ttaxontax)\n\t\t\t\t\t\tVALUES ('" . $StkMoveNo . "',\n\t\t\t\t\t\t\t'" . $Tax['TaxAuthID'] . "',\n\t\t\t\t\t\t\t'" . $Tax['TaxRate'] . "',\n\t\t\t\t\t\t\t'" . $Tax['TaxCalculationOrder'] . "',\n\t\t\t\t\t\t\t'" . $Tax['TaxOnTax'] . "')";
$Result = DB_query($SQL, $db, '', '', true);
}
/*Insert Sales Analysis records */
$SQL = "SELECT COUNT(*),\n\t\t\t\t\t\tsalesanalysis.stkcategory,\n\t\t\t\t\t\tsalesanalysis.area,\n\t\t\t\t\t\tsalesanalysis.salesperson,\n\t\t\t\t\t\tsalesanalysis.periodno,\n\t\t\t\t\t\tsalesanalysis.typeabbrev,\n\t\t\t\t\t\tsalesanalysis.cust,\n\t\t\t\t\t\tsalesanalysis.custbranch,\n\t\t\t\t\t\tsalesanalysis.stockid\n\t\t\t\t\tFROM salesanalysis,\n\t\t\t\t\t\tcustbranch,\n\t\t\t\t\t\tstockmaster\n\t\t\t\t\tWHERE salesanalysis.stkcategory=stockmaster.categoryid\n\t\t\t\t\tAND salesanalysis.stockid=stockmaster.stockid\n\t\t\t\t\tAND salesanalysis.cust=custbranch.debtorno\n\t\t\t\t\tAND salesanalysis.custbranch=custbranch.branchcode\n\t\t\t\t\tAND salesanalysis.area=custbranch.area\n\t\t\t\t\tAND salesanalysis.salesperson=custbranch.salesman\n\t\t\t\t\tAND salesanalysis.typeabbrev ='" . $OrderHeader['ordertype'] . "'\n\t\t\t\t\tAND salesanalysis.periodno='" . $PeriodNo . "'\n\t\t\t\t\tAND salesanalysis.cust " . LIKE . " '" . $OrderHeader['debtorno'] . "'\n\t\t\t\t\tAND salesanalysis.custbranch " . LIKE . " '" . $OrderHeader['branchcode'] . "'\n\t\t\t\t\tAND salesanalysis.stockid " . LIKE . " '" . $OrderLineRow['stkcode'] . "'\n\t\t\t\t\tAND salesanalysis.budgetoractual='1'\n\t\t\t\t\tGROUP BY salesanalysis.stockid,\n\t\t\t\t\t\tsalesanalysis.stkcategory,\n\t\t\t\t\t\tsalesanalysis.cust,\n\t\t\t\t\t\tsalesanalysis.custbranch,\n\t\t\t\t\t\tsalesanalysis.area,\n\t\t\t\t\t\tsalesanalysis.periodno,\n\t\t\t\t\t\tsalesanalysis.typeabbrev,\n\t\t\t\t\t\tsalesanalysis.salesperson";
$ErrMsg = _('The count of existing Sales analysis records could not run because');
$DbgMsg = _('SQL to count the no of sales analysis records');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
$myrow = DB_fetch_row($Result);
if ($myrow[0] > 0) {
/*Update the existing record that already exists */
$SQL = "UPDATE salesanalysis\n\t\t\t\t\t\tSET amt=amt+" . filter_number_format($OrderLineRow['unitprice'] * $OrderLineRow['quantity'] / $OrderHeader['rate']) . ",\n\t\t\t\t\t\tqty=qty +" . $OrderLineRow['quantity'] . ",\n\t\t\t\t\t\tdisc=disc+" . filter_number_format($OrderLineRow['discountpercent'] * $OrderLineRow['unitprice'] * $OrderLineRow['quantity'] / $OrderHeader['rate']) . "\n\t\t\t\t\t\tWHERE salesanalysis.area='" . $myrow[2] . "'\n\t\t\t\t\t\tAND salesanalysis.salesperson='" . $myrow[3] . "'\n\t\t\t\t\t\tAND typeabbrev ='" . $OrderHeader['ordertype'] . "'\n\t\t\t\t\t\tAND periodno = '" . $PeriodNo . "'\n\t\t\t\t\t\tAND cust " . LIKE . " '" . $OrderHeader['debtorno'] . "'\n\t\t\t\t\t\tAND custbranch " . LIKE . " '" . $OrderHeader['branchcode'] . "'\n\t\t\t\t\t\tAND stockid " . LIKE . " '" . $OrderLineRow['stkcode'] . "'\n\t\t\t\t\t\tAND salesanalysis.stkcategory ='" . $myrow[1] . "'\n\t\t\t\t\t\tAND budgetoractual='1'";
} else {
/* insert a new sales analysis record */
$SQL = "INSERT INTO salesanalysis (\ttypeabbrev,\n\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\tamt,\n\t\t\t\t\t\t\t\t\t\t\t\t\tcost,\n\t\t\t\t\t\t\t\t\t\t\t\t\tcust,\n\t\t\t\t\t\t\t\t\t\t\t\t\tcustbranch,\n\t\t\t\t\t\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\t\t\t\t\t\tdisc,\n\t\t\t\t\t\t\t\t\t\t\t\t\tstockid,\n\t\t\t\t\t\t\t\t\t\t\t\t\tarea,\n\t\t\t\t\t\t\t\t\t\t\t\t\tbudgetoractual,\n\t\t\t\t\t\t\t\t\t\t\t\t\tsalesperson,\n\t\t\t\t\t\t\t\t\t\t\t\t\tstkcategory )\n\t\t\t\t\t\t\t\tSELECT '" . $OrderHeader['ordertype'] . "',\n\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t'" . $OrderLineRow['unitprice'] * $OrderLineRow['quantity'] / $OrderHeader['rate'] . "',\n\t\t\t\t\t\t\t\t\t0,\n\t\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . "',\n\t\t\t\t\t\t\t\t\t'" . $OrderHeader['branchcode'] . "',\n\t\t\t\t\t\t\t\t\t'" . $OrderLineRow['quantity'] . "',\n\t\t\t\t\t\t\t\t\t'" . $OrderLineRow['discountpercent'] * $OrderLineRow['unitprice'] * $OrderLineRow['quantity'] / $OrderHeader['rate'] . "',\n\t\t\t\t\t\t\t\t\t'" . $OrderLineRow['stkcode'] . "',\n\t\t\t\t\t\t\t\t\tcustbranch.area,\n\t\t\t\t\t\t\t\t\t1,\n\t\t\t\t\t\t\t\t\tcustbranch.salesman,\n\t\t\t\t\t\t\t\t\tstockmaster.categoryid\n\t\t\t\t\t\t\t\tFROM stockmaster, custbranch\n\t\t\t\t\t\t\t\tWHERE stockmaster.stockid = '" . $OrderLineRow['stkcode'] . "'\n\t\t\t\t\t\t\t\tAND custbranch.debtorno = '" . $OrderHeader['debtorno'] . "'\n\t\t\t\t\t\t\t\tAND custbranch.branchcode='" . $OrderHeader['branchcode'] . "'";
}
$Result = api_DB_query($SQL, $db, '', '', true);
if ($CompanyRecord['gllink_stock'] == 1 and $StandardCost != 0) {
/*first the cost of sales entry - GL accounts are retrieved using the function GetCOGSGLAccount from includes/GetSalesTransGLCodes.inc */
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\tVALUES (10,\n\t\t\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . GetCOGSGLAccount($OrderHeader['area'], $OrderLineRow['stkcode'], $OrderHeader['ordertype'], $db) . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . " - " . $OrderLineRow['stkcode'] . " x " . $OrderLineRow['quantity'] . " @ " . $StandardCost . "',\n\t\t\t\t\t\t\t\t\t\t'" . $StandardCost * $OrderLineRow['quantity'] . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
/*now the stock entry - this is set to the cost act in the case of a fixed asset disposal */
$StockGLCode = GetStockGLCode($OrderLineRow['stkcode'], $db);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\tVALUES (10,\n\t\t\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $StockGLCode['stockact'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . " - " . $OrderLineRow['stkcode'] . " x " . $OrderLineRow['quantity'] . " @ " . $StandardCost . "',\n\t\t\t\t\t\t\t\t\t\t'" . -$StandardCost * $OrderLineRow['quantity'] . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
}
/* end of if GL and stock integrated and standard cost !=0 and not an asset */
if ($CompanyRecord['gllink_debtors'] == 1 and $OrderLineRow['unitprice'] != 0) {
//Post sales transaction to GL credit sales
$SalesGLAccounts = GetSalesGLAccount($OrderHeader['area'], $OrderLineRow['stkcode'], $OrderHeader['ordertype'], $db);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount )\n\t\t\t\t\tVALUES ('10',\n\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t'" . $SalesGLAccounts['salesglcode'] . "',\n\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . " - " . $OrderLineRow['stkcode'] . " x " . $OrderLineRow['quantity'] . " @ " . $OrderLineRow['unitprice'] . "',\n\t\t\t\t\t\t'" . -$OrderLineRow['unitprice'] * $OrderLineRow['quantity'] / $OrderHeader['rate'] . "'\n\t\t\t\t\t)";
$Result = api_DB_query($SQL, $db, '', '', true);
if ($OrderLineRow['discountpercent'] != 0) {
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\tVALUES (10,\n\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t'" . $SalesGLAccounts['discountglcode'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . " - " . $OrderLineRow['stkcode'] . " @ " . $OrderLineRow['discountpercent'] * 100 . "%',\n\t\t\t\t\t\t\t\t'" . $OrderLineRow['unitprice'] * $OrderLineRow['quantity'] * $OrderLineRow['discountpercent'] / $OrderHeader['rate'] . "')";
$Result = DB_query($SQL, $db, '', '', true);
}
/*end of if discount !=0 */
}
/*end of if sales integrated with gl */
$LineCounter++;
//needed for the array of taxes by line
}
/*end of OrderLine loop */
$TotalInvLocalCurr = ($TotalFXNetInvoice + $TotalFXTax) / $OrderHeader['rate'];
if ($CompanyRecord['gllink_debtors'] == 1) {
/*Now post the tax to the GL at local currency equivalent */
if ($CompanyRecord['gllink_debtors'] == 1 and $TaxAuthAmount != 0) {
/*Loop through the tax authorities array to post each total to the taxauth glcode */
foreach ($TaxTotals as $Tax) {
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\t\tamount )\n\t\t\t\t\t\t\t\t\t\t\tVALUES (10,\n\t\t\t\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\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'" . $Tax['GLCode'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . "-" . $Tax['TaxAuthDescription'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . -$Tax['FXAmount'] / $OrderHeader['rate'] . "' )";
$Result = api_DB_query($SQL, $db, '', '', true);
}
}
/*Post debtors transaction to GL debit debtors, credit freight re-charged and credit sales */
if ($TotalInvLocalCurr != 0) {
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\tVALUES ('10',\n\t\t\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $CompanyRecord['debtorsact'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $TotalInvLocalCurr . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
}
EnsureGLEntriesBalance(10, $InvoiceNo, $db);
}
/*end of if Sales and GL integrated */
/*Update order header for invoice charged on */
$SQL = "UPDATE salesorders SET comments = CONCAT(comments,' Inv ','" . $InvoiceNo . "') WHERE orderno= '" . $OrderNo . "'";
$Result = api_DB_query($SQL, $db, '', '', true);
/*Now insert the DebtorTrans */
$SQL = "INSERT INTO debtortrans (transno,\n\t\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\tinputdate,\n\t\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\t\t\ttpe,\n\t\t\t\t\t\t\t\t\t\torder_,\n\t\t\t\t\t\t\t\t\t\tovamount,\n\t\t\t\t\t\t\t\t\t\tovgst,\n\t\t\t\t\t\t\t\t\t\trate,\n\t\t\t\t\t\t\t\t\t\tshipvia,\n\t\t\t\t\t\t\t\t\t\tsalesperson)\n\t\t\t\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t\t10,\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['branchcode'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . date('Y-m-d H-i-s') . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['customerref'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['ordertype'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $TotalFXNetInvoice . "',\n\t\t\t\t\t\t\t\t\t\t'" . $TotalFXTax . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['rate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['shipvia'] . "',\n+\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['salesman'] . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
$DebtorTransID = DB_Last_Insert_ID($db, 'debtortrans', 'id');
/*for each Tax - need to insert into debtortranstaxes */
foreach ($TaxTotals as $TaxAuthID => $Tax) {
$SQL = "INSERT INTO debtortranstaxes (debtortransid,\n\t\t\t\t\t\t\t\t\t\t\t\ttaxauthid,\n\t\t\t\t\t\t\t\t\t\t\t\ttaxamount)\n\t\t\t\t\t\t\t\tVALUES ('" . $DebtorTransID . "',\n\t\t\t\t\t\t\t\t\t\t'" . $TaxAuthID . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Tax['FXAmount'] / $OrderHeader['rate'] . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
}
if (sizeof($Errors) == 0) {
$Result = DB_Txn_Commit($db);
$Errors[0] = 0;
$Errors[1] = $InvoiceNo;
} else {
$Result = DB_Txn_Rollback($db);
}
return $Errors;
}
if ($BatchReceiptsTotal != 0) {
/* Bank account entry first */
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\tVALUES (\n\t\t\t\t\t12,\n\t\t\t\t\t'" . $_SESSION['ReceiptBatch']->BatchNo . "',\n\t\t\t\t\t'" . FormatDateForSQL($_SESSION['ReceiptBatch']->DateBanked) . "',\n\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t'" . $_SESSION['ReceiptBatch']->Account . "',\n\t\t\t\t\t'" . $_SESSION['ReceiptBatch']->Narrative . "',\n\t\t\t\t\t'" . $BatchReceiptsTotal . "'\n\t\t\t\t)";
$DbgMsg = _('The SQL that failed to insert the GL transaction fro the bank account debit was');
$ErrMsg = _('Cannot insert a GL transaction for the bank account debit');
$result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
}
if ($BatchDiscount != 0) {
/* Now Debit Discount account with discounts allowed*/
$SQL = "INSERT INTO gltrans ( type,\n\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t\t12,\n\t\t\t\t\t\t\t\t'" . $_SESSION['ReceiptBatch']->BatchNo . "',\n\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_SESSION['ReceiptBatch']->DateBanked) . "',\n\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t'" . $_SESSION['CompanyRecord']['pytdiscountact'] . "',\n\t\t\t\t\t\t\t\t'" . $_SESSION['ReceiptBatch']->Narrative . "',\n\t\t\t\t\t\t\t\t'" . $BatchDiscount . "'\n\t\t\t\t\t\t\t)";
$DbgMsg = _('The SQL that failed to insert the GL transaction for the payment discount debit was');
$ErrMsg = _('Cannot insert a GL transaction for the payment discount debit');
$result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
}
//end if there is some discount
EnsureGLEntriesBalance(12, $_SESSION['ReceiptBatch']->BatchNo, $db);
}
//end if there is GL work to be done - ie config is to link to GL
$ErrMsg = _('Cannot commit the changes');
$DbgMsg = _('The SQL that failed was');
$result = DB_Txn_Commit($db);
echo '<br />';
prnMsg(_('Receipt batch') . ' ' . $_SESSION['ReceiptBatch']->BatchNo . ' ' . _('has been successfully entered into the database'), 'success');
echo '<p class="page_title_text noPrint" ><img src="' . $RootPath . '/css/' . $Theme . '/images/printer.png" title="' . _('Print') . '" alt="" />' . ' ' . '<a href="' . $RootPath . '/PDFBankingSummary.php?BatchNo=' . $_SESSION['ReceiptBatch']->BatchNo . '">' . _('Print PDF Batch Summary') . '</a></p>';
echo '<p class="page_title_text noPrint" ><img src="' . $RootPath . '/css/' . $Theme . '/images/allocation.png" title="' . _('Allocate') . '" alt="" />' . ' ' . '<a href="' . $RootPath . '/CustomerAllocations.php">' . _('Allocate Receipts') . '</a></p>';
echo '<p class="page_title_text noPrint" ><a href="' . $RootPath . '/CustomerReceipt.php?NewReceipt=Yes&Type=Customer">' . _('Enter Receipts') . '</a>
</p>';
unset($_SESSION['ReceiptBatch']);
include 'includes/footer.inc';
exit;
}
/*Also if GL is linked to debtors need to process the debit to bank and credit to debtors for the payment */
/*Need to figure out the cross rate between customer currency and bank account currency */
if ($_POST['AmountPaid'] != 0) {
$PaymentNumber = GetNextTransNo(12, $db);
$SQL = "INSERT INTO gltrans (type,\r\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\r\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\t\t\t\t\t\taccount,\r\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\r\n\t\t\t\t\t\t\t\t\t\t\tamount)\r\n\t\t\t\t\t\tVALUES (12,\r\n\t\t\t\t\t\t\t'" . $PaymentNumber . "',\r\n\t\t\t\t\t\t\t'" . $ReturnDate . "',\r\n\t\t\t\t\t\t\t'" . $PeriodNo . "',\r\n\t\t\t\t\t\t\t'" . $_POST['BankAccount'] . "',\r\n\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->LocationName . ' ' . _('Counter Return') . ' ' . $CreditNoteNo . "',\r\n\t\t\t\t\t\t\t'" . -(filter_number_format($_POST['AmountPaid']) / $ExRate) . "')";
$DbgMsg = _('The SQL that failed to insert the GL transaction for the bank account debit was');
$ErrMsg = _('Cannot insert a GL transaction for the bank account debit');
$result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
/* Now Debit Debtors account with negative receipt/payment to customer */
$SQL = "INSERT INTO gltrans ( type,\r\n\t\t\t\t\t\ttypeno,\r\n\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\taccount,\r\n\t\t\t\t\t\tnarrative,\r\n\t\t\t\t\t\tamount)\r\n\t\t\t\tVALUES (12,\r\n\t\t\t\t\t'" . $PaymentNumber . "',\r\n\t\t\t\t\t'" . $ReturnDate . "',\r\n\t\t\t\t\t'" . $PeriodNo . "',\r\n\t\t\t\t\t'" . $_SESSION['CompanyRecord']['debtorsact'] . "',\r\n\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->LocationName . ' ' . _('Counter Return') . ' ' . $CreditNoteNo . "',\r\n\t\t\t\t\t'" . filter_number_format($_POST['AmountPaid']) / $ExRate . "')";
$DbgMsg = _('The SQL that failed to insert the GL transaction for the debtors account credit was');
$ErrMsg = _('Cannot insert a GL transaction for the debtors account credit');
$result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
}
//amount paid was not zero
EnsureGLEntriesBalance(12, $PaymentNumber, $db);
}
/*end of if Sales and GL integrated */
if ($_POST['AmountPaid'] != 0) {
if (!isset($PaymentNumber)) {
$PaymentNumber = GetNextTransNo(12, $db);
}
//Now need to add the receipt banktrans record
//First get the account currency that it has been banked into
$result = DB_query("SELECT rate FROM currencies\r\n\t\t\t\t\t\t\t\tINNER JOIN bankaccounts\r\n\t\t\t\t\t\t\t\tON currencies.currabrev=bankaccounts.currcode\r\n\t\t\t\t\t\t\t\tWHERE bankaccounts.accountcode='" . $_POST['BankAccount'] . "'", $db);
$myrow = DB_fetch_row($result);
$BankAccountExRate = $myrow[0];
/*
* Some interesting exchange rate conversion going on here
* Say :
* The business's functional currency is NZD
