/*first the cost of sales entry*/ $SQL = "INSERT INTO gltrans (\r\n\t\t\t\t\t\t\ttype,\r\n\t\t\t\t\t\t\ttypeno,\r\n\t\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\t\taccount,\r\n\t\t\t\t\t\t\tnarrative,\r\n\t\t\t\t\t\t\tamount\r\n\t\t\t\t\t\t\t)\r\n\t\t\t\t\tVALUES (\r\n\t\t\t\t\t\t10,\r\n\t\t\t\t\t\t" . $InvoiceNo . ",\r\n\t\t\t\t\t\t'" . $DefaultDispatchDate . "',\r\n\t\t\t\t\t\t" . $PeriodNo . ",\r\n\t\t\t\t\t\t" . GetCOGSGLAccount($Area, $OrderLine->StockID, $_SESSION['Items']->DefaultSalesType, $db) . ",\r\n\t\t\t\t\t\t'" . $_SESSION['Items']->DebtorNo . " - " . $OrderLine->StockID . " x " . $OrderLine->QtyDispatched . " @ " . $OrderLine->StandardCost . "',\r\n\t\t\t\t\t\t" . $OrderLine->StandardCost * $OrderLine->QtyDispatched . "\r\n\t\t\t\t\t)"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The cost of sales GL posting could not be inserted because'); $DbgMsg = _('The following SQL to insert the GLTrans record was used'); $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); /*now the stock entry*/ $StockGLCode = GetStockGLCode($OrderLine->StockID, $db); $SQL = "INSERT INTO gltrans (\r\n\t\t\t\t\t\t\ttype,\r\n\t\t\t\t\t\t\ttypeno,\r\n\t\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\t\taccount,\r\n\t\t\t\t\t\t\tnarrative,\r\n\t\t\t\t\t\t\tamount)\r\n\t\t\t\t\tVALUES (\r\n\t\t\t\t\t\t10,\r\n\t\t\t\t\t\t" . $InvoiceNo . ",\r\n\t\t\t\t\t\t'" . $DefaultDispatchDate . "',\r\n\t\t\t\t\t\t" . $PeriodNo . ",\r\n\t\t\t\t\t\t" . $StockGLCode['stockact'] . ",\r\n\t\t\t\t\t\t'" . $_SESSION['Items']->DebtorNo . " - " . $OrderLine->StockID . " x " . $OrderLine->QtyDispatched . " @ " . $OrderLine->StandardCost . "',\r\n\t\t\t\t\t\t" . -$OrderLine->StandardCost * $OrderLine->QtyDispatched . "\r\n\t\t\t\t\t)"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The stock side of the cost of sales GL posting could not be inserted because'); $DbgMsg = _('The following SQL to insert the GLTrans record was used'); $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); } /* end of if GL and stock integrated and standard cost !=0 */ if ($_SESSION['CompanyRecord']['gllink_debtors'] == 1 and $OrderLine->Price != 0) { //Post sales transaction to GL credit sales $SalesGLAccounts = GetSalesGLAccount($Area, $OrderLine->StockID, $_SESSION['Items']->DefaultSalesType, $db); $SQL = "INSERT INTO gltrans (\r\n\t\t\t\t\t\t\ttype,\r\n\t\t\t\t\t\t\ttypeno,\r\n\t\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\t\taccount,\r\n\t\t\t\t\t\t\tnarrative,\r\n\t\t\t\t\t\t\tamount\r\n\t\t\t\t\t\t)\r\n\t\t\t\t\tVALUES (\r\n\t\t\t\t\t\t10,\r\n\t\t\t\t\t\t" . $InvoiceNo . ",\r\n\t\t\t\t\t\t'" . $DefaultDispatchDate . "',\r\n\t\t\t\t\t\t" . $PeriodNo . ",\r\n\t\t\t\t\t\t" . $SalesGLAccounts['salesglcode'] . ",\r\n\t\t\t\t\t\t'" . $_SESSION['Items']->DebtorNo . " - " . $OrderLine->StockID . " x " . $OrderLine->QtyDispatched . " @ " . $OrderLine->Price . "',\r\n\t\t\t\t\t\t" . -$OrderLine->Price * $OrderLine->QtyDispatched / $_SESSION['CurrencyRate'] . "\r\n\t\t\t\t\t)"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The sales GL posting could not be inserted because'); $DbgMsg = '<br>' . _('The following SQL to insert the GLTrans record was used'); $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); if ($OrderLine->DiscountPercent != 0) { $SQL = "INSERT INTO gltrans (\r\n\t\t\t\t\t\t\ttype,\r\n\t\t\t\t\t\t\ttypeno,\r\n\t\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\t\taccount,\r\n\t\t\t\t\t\t\tnarrative,\r\n\t\t\t\t\t\t\tamount\r\n\t\t\t\t\t\t)\r\n\t\t\t\t\t\tVALUES (\r\n\t\t\t\t\t\t\t10,\r\n\t\t\t\t\t\t\t" . $InvoiceNo . ",\r\n\t\t\t\t\t\t\t'" . $DefaultDispatchDate . "',\r\n\t\t\t\t\t\t\t" . $PeriodNo . ",\r\n\t\t\t\t\t\t\t" . $SalesGLAccounts['discountglcode'] . ",\r\n\t\t\t\t\t\t\t'" . $_SESSION['Items']->DebtorNo . " - " . $OrderLine->StockID . " @ " . $OrderLine->DiscountPercent * 100 . "%',\r\n\t\t\t\t\t\t\t" . $OrderLine->Price * $OrderLine->QtyDispatched * $OrderLine->DiscountPercent / $_SESSION['CurrencyRate'] . "\r\n\t\t\t\t\t\t)"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The sales discount GL posting could not be inserted because'); $DbgMsg = _('The following SQL to insert the GLTrans record was used'); $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); } /*end of if discount !=0 */ } /*end of if sales integrated with debtors */ } /*Quantity dispatched is more than 0 */
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The cost of the stock credited 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); } else { /*the goods are coming back into stock so debit the stock account*/ $StockGLCode = GetStockGLCode($CreditLine->StockID, $db); $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\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'" . $StockGLCode['stockact'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['CreditItems' . $identifier]->DebtorNo . " - " . $CreditLine->StockID . " x " . $CreditLine->Quantity . " @ " . $CreditLine->StandardCost . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditLine->StandardCost * $CreditLine->Quantity . "'\n\t\t\t\t\t\t\t\t\t\t\t)"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The stock side (or write off) of the cost of sales 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 GL and stock integrated and standard cost !=0 */ if ($_SESSION['CompanyRecord']['gllink_debtors'] == 1 and $CreditLine->Price != 0) { //Post sales transaction to GL credit sales $SalesGLAccounts = GetSalesGLAccount($Area, $CreditLine->StockID, $_SESSION['CreditItems' . $identifier]->DefaultSalesType, $db); $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\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'" . $SalesGLAccounts['salesglcode'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['CreditItems' . $identifier]->DebtorNo . " - " . $CreditLine->StockID . " x " . $CreditLine->Quantity . " @ " . $CreditLine->Price . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditLine->Price * $CreditLine->Quantity / $_SESSION['CurrencyRate'] . "'\n\t\t\t\t\t\t\t\t\t\t\t)"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The credit note 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); if ($CreditLine->DiscountPercent != 0) { $SQL = "INSERT INTO gltrans (type,\n\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\ttrandate,\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\taccount,\n\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\tamount)\n\t\t\t\t\t\t\t\t\tVALUES (11,\n\t\t\t\t\t\t\t\t\t\t'" . $CreditNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $SQLCreditDate . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $SalesGLAccounts['discountglcode'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['CreditItems' . $identifier]->DebtorNo . " - " . $CreditLine->StockID . " @ " . $CreditLine->DiscountPercent * 100 . "%',\n\t\t\t\t\t\t\t\t\t\t'" . -($CreditLine->Price * $CreditLine->Quantity * $CreditLine->DiscountPercent / $_SESSION['CurrencyRate']) . "'\n\t\t\t\t\t\t\t\t\t\t)"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The credit note discount 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 discount not equal to 0 */ } /*end of if sales integrated with debtors */ } /*Quantity credited is more than 0 */
$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\tSET amt=amt+" . $RecurrOrderLineRow['unitprice'] * $RecurrOrderLineRow['quantity'] / $CurrencyRate . ",\n\t\t\t\t\tqty=qty +" . $RecurrOrderLineRow['quantity'] . ",\n\t\t\t\t\tdisc=disc+" . $RecurrOrderLineRow['discountpercent'] * $RecurrOrderLineRow['unitprice'] * $RecurrOrderLineRow['quantity'] / $CurrencyRate . "\n\t\t\t\t\tWHERE salesanalysis.area='" . $myrow[2] . "'\n\t\t\t\t\tAND salesanalysis.salesperson='" . $myrow[3] . "'\n\t\t\t\t\tAND typeabbrev ='" . $RecurrOrderRow['ordertype'] . "'\n\t\t\t\t\tAND periodno = " . $PeriodNo . "\n\t\t\t\t\tAND cust " . LIKE . " '" . $RecurrOrderRow['debtorno'] . "'\n\t\t\t\t\tAND custbranch " . LIKE . " '" . $RecurrOrderRow['branchcode'] . "'\n\t\t\t\t\tAND stockid " . LIKE . " '" . $RecurrOrderLineRow['stkcode'] . "'\n\t\t\t\t\tAND salesanalysis.stkcategory ='" . $myrow[1] . "'\n\t\t\t\t\tAND budgetoractual=1"; } else { /* insert a new sales analysis record */ $SQL = "INSERT INTO salesanalysis (\n\t\t\t\t\t\ttypeabbrev,\n\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\tamt,\n\t\t\t\t\t\tcost,\n\t\t\t\t\t\tcust,\n\t\t\t\t\t\tcustbranch,\n\t\t\t\t\t\tqty,\n\t\t\t\t\t\tdisc,\n\t\t\t\t\t\tstockid,\n\t\t\t\t\t\tarea,\n\t\t\t\t\t\tbudgetoractual,\n\t\t\t\t\t\tsalesperson,\n\t\t\t\t\t\tstkcategory\n\t\t\t\t\t\t)\n\t\t\t\t\tSELECT '" . $RecurrOrderRow['ordertype'] . "',\n\t\t\t\t\t\t" . $PeriodNo . ",\n\t\t\t\t\t\t" . $RecurrOrderLineRow['unitprice'] * $RecurrOrderLineRow['quantity'] / $CurrencyRate . ",\n\t\t\t\t\t\t0,\n\t\t\t\t\t\t'" . $RecurrOrderRow['debtorno'] . "',\n\t\t\t\t\t\t'" . $RecurrOrderRow['branchcode'] . "',\n\t\t\t\t\t\t" . $RecurrOrderLineRow['quantity'] . ",\n\t\t\t\t\t\t" . $RecurrOrderLineRow['discountpercent'] * $RecurrOrderLineRow['unitprice'] * $RecurrOrderLineRow['quantity'] / $CurrencyRate . ",\n\t\t\t\t\t\t'" . $RecurrOrderLineRow['stkcode'] . "',\n\t\t\t\t\t\tcustbranch.area,\n\t\t\t\t\t\t1,\n\t\t\t\t\t\tcustbranch.salesman,\n\t\t\t\t\t\tstockmaster.categoryid\n\t\t\t\t\tFROM stockmaster,\n\t\t\t\t\t\tcustbranch\n\t\t\t\t\tWHERE stockmaster.stockid = '" . $RecurrOrderLineRow['stkcode'] . "'\n\t\t\t\t\tAND custbranch.debtorno = '" . $RecurrOrderRow['debtorno'] . "'\n\t\t\t\t\tAND custbranch.branchcode='" . $RecurrOrderRow['branchcode'] . "'"; } $ErrMsg = _('Sales analysis record could not be added or updated because'); $DbgMsg = _('The following SQL to insert the sales analysis record was used'); $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); if ($_SESSION['CompanyRecord']['gllink_debtors'] == 1 && $RecurrOrderLineRow['unitprice'] != 0) { //Post sales transaction to GL credit sales $SalesGLAccounts = GetSalesGLAccount($Area, $RecurrOrderLineRow['stkcode'], $RecurrOrderRow['ordertype'], $db); $SQL = "INSERT INTO gltrans (\n\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\tamount\n\t\t\t\t\t\t)\n\t\t\t\t\tVALUES (\n\t\t\t\t\t\t10,\n\t\t\t\t\t\t" . $InvoiceNo . ",\n\t\t\t\t\t\t'" . $DelDate . "',\n\t\t\t\t\t\t" . $PeriodNo . ",\n\t\t\t\t\t\t" . $SalesGLAccounts['salesglcode'] . ",\n\t\t\t\t\t\t'" . $RecurrOrderRow['debtorno'] . " - " . $RecurrOrderLineRow['stkcode'] . " x " . $RecurrOrderLineRow['quantity'] . " @ " . $RecurrOrderLineRow['unitprice'] . "',\n\t\t\t\t\t\t" . -$RecurrOrderLineRow['unitprice'] * $RecurrOrderLineRow['quantity'] / $CurrencyRate . "\n\t\t\t\t\t)"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The sales GL posting could not be inserted because'); $DbgMsg = '<BR>' . _('The following SQL to insert the GLTrans record was used'); $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); /* Don't care about COGS because it can only be a dummy items being invoiced ... no cost of sales to mess with */ if ($RecurrOrderLineRow['discountpercent'] != 0) { $SQL = "INSERT INTO gltrans (\n\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\tamount\n\t\t\t\t\t\t)\n\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t10,\n\t\t\t\t\t\t\t" . $InvoiceNo . ",\n\t\t\t\t\t\t\t'" . $DelDate . "',\n\t\t\t\t\t\t\t" . $PeriodNo . ",\n\t\t\t\t\t\t\t" . $SalesGLAccounts['discountglcode'] . ",\n\t\t\t\t\t\t\t'" . $RecurrOrderRow['debtorno'] . " - " . $RecurrOrderLineRow['stkcode'] . " @ " . $RecurrOrderLineRow['discountpercent'] * 100 . "%',\n\t\t\t\t\t\t\t" . $RecurrOrderLineRow['unitprice'] * $RecurrOrderLineRow['quantity'] * $RecurrOrderLineRow['discountpercent'] / $CurrencyRate . "\n\t\t\t\t\t\t)"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The sales discount GL posting could not be inserted because'); $DbgMsg = _('The following SQL to insert the GLTrans record was used'); $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); } /*end of if discount !=0 */ } /*end of if sales integrated with gl */ $LineCounter++;
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; }
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The debtor transaction record could not be inserted because'); $DbgMsg = _('The following SQL to insert the debtor transaction record was used'); $Result = DB_query($sql, $db, $ErrMsg, $DbgMsg, true); for ($i = 0; $i < $_SESSION['Items']['Lines']; $i++) { if (isset($_SESSION['Items'][$i]['StockID'])) { $SQL = "INSERT INTO stockmoves (\n\t\t\t\t\t\tstockid,\n\t\t\t\t\t\ttype,\n\t\t\t\t\t\ttransno,\n\t\t\t\t\t\tloccode,\n\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\tprd,\n\t\t\t\t\t\treference,\n\t\t\t\t\t\tqty,\n\t\t\t\t\t\tprice,\n\t\t\t\t\t\tshow_on_inv_crds,\n\t\t\t\t\t\tnewqoh\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'" . $_SESSION['Items'][$i]['StockID'] . "',\n\t\t\t\t\t\t 10,\n\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t'" . $_SESSION['UserStockLocation'] . "',\n\t\t\t\t\t\t'" . FormatDateForSQL($_POST['AdmissionDate']) . "',\n\t\t\t\t\t\t'" . $_POST['PatientNo'] . "',\n\t\t\t\t\t\t'" . $_POST['BranchNo'] . "',\n\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t'" . _('Invoice for radiology test of Patient number') . ' ' . $_POST['PatientNo'] . "',\n\t\t\t\t\t\t-1,\n\t\t\t\t\t\t'" . $_SESSION['Items'][$i]['Price'] . "',\n\t\t\t\t\t\t1,\n\t\t\t\t\t\t0\n\t\t\t\t\t)"; $BaseStockID = $_SESSION['Items'][$i]['StockID']; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Stock movement records for') . ' ' . $_POST['StockID'] . ' ' . _('could not be inserted because'); $DbgMsg = _('The following SQL to insert the stock movement records was used'); $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); } } $SQL = "SELECT salestype\n\t\t\t\tFROM debtorsmaster\n\t\t\t\tWHERE debtorno='" . $_POST['PatientNo'] . "'"; $Result = DB_query($SQL, $db); $myrow = DB_fetch_array($Result); $SalesGLAccounts = GetSalesGLAccount('AN', $BaseStockID, $myrow['salestype'], $db); $SQL = "INSERT INTO gltrans (\ttype,\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\tdefaulttag,\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\tVALUES ( 10,\n\t\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_POST['AdmissionDate']) . "',\n\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t'" . $SalesGLAccounts['salesglcode'] . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['DefaultTag'] . "',\n\t\t\t\t\t\t\t\t\t'" . _('Invoice for radiology test of Patient number') . ' ' . $_POST['PatientNo'] . "',\n\t\t\t\t\t\t\t\t\t'" . -$_SESSION['Items']['Value'] . "')"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The cost of sales GL posting could not be inserted because'); $DbgMsg = _('The following SQL to insert the GLTrans record was used'); $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); $SQL = "INSERT INTO gltrans (\ttype,\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\tdefaulttag,\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 ( 10,\n\t\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_POST['AdmissionDate']) . "',\n\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['CompanyRecord']['debtorsact'] . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['DefaultTag'] . "',\n\t\t\t\t\t\t\t\t\t'" . _('Invoice for radiology test of Patient number') . ' ' . $_POST['PatientNo'] . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items']['Value'] . "')"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The stock side of the cost of sales GL posting could not be inserted because'); $DbgMsg = _('The following SQL to insert the GLTrans record was used'); $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); if (isset($_POST['SubmitCash'])) { $ReceiptNumber = GetNextTransNo(12, $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\tdefaulttag,\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 (12,\n\t\t\t\t\t\t\t\t\t\t'" . $ReceiptNumber . "',\n\t\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_POST['AdmissionDate']) . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_POST['BankAccount'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['DefaultTag'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . _('Payment of radiology test for Patient number') . ' ' . $_POST['PatientNo'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items']['Value'] . "')"; $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 */
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; }
$_POST['Received'] = 0; } else { $_POST['InsuranceRef'] = ''; } $sql = "INSERT INTO debtortrans (\n\t\t\t\ttransno,\n\t\t\t\ttype,\n\t\t\t\tdebtorno,\n\t\t\t\tbranchcode,\n\t\t\t\ttrandate,\n\t\t\t\tinputdate,\n\t\t\t\tprd,\n\t\t\t\torder_,\n\t\t\t\tovamount,\n\t\t\t\tovgst,\n\t\t\t\trate,\n\t\t\t\tinvtext,\n\t\t\t\treference,\n\t\t\t\tshipvia,\n\t\t\t\talloc )\n\t\t\tVALUES (\n\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t10,\n\t\t\t\t'" . $_POST['PatientNo'] . "',\n\t\t\t\t'" . $_POST['BranchNo'] . "',\n\t\t\t\t'" . FormatDateForSQL($_POST['AdmissionDate']) . "',\n\t\t\t\t'" . FormatDateForSQL($_POST['AdmissionDate']) . "',\n\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t'" . $OrderNo . "',\n\t\t\t\t'" . $_POST['Price'] . "',\n\t\t\t\t'0',\n\t\t\t\t'1',\n\t\t\t\t'" . _('Invoice for admission of Patient number') . ' ' . $_POST['PatientNo'] . "',\n\t\t\t\t'" . $_POST['InsuranceRef'] . "',\n\t\t\t\t'1',\n\t\t\t\t'" . $_POST['Received'] . "')"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The debtor transaction record could not be inserted because'); $DbgMsg = _('The following SQL to insert the debtor transaction record was used'); $Result = DB_query($sql, $db, $ErrMsg, $DbgMsg, true); $SQL = "INSERT INTO stockmoves (\n\t\t\t\t\t\tstockid,\n\t\t\t\t\t\ttype,\n\t\t\t\t\t\ttransno,\n\t\t\t\t\t\tloccode,\n\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\tprd,\n\t\t\t\t\t\treference,\n\t\t\t\t\t\tqty,\n\t\t\t\t\t\tprice,\n\t\t\t\t\t\tshow_on_inv_crds,\n\t\t\t\t\t\tnewqoh\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'" . $_POST['StockID'] . "',\n\t\t\t\t\t\t 10,\n\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t'" . $_SESSION['UserStockLocation'] . "',\n\t\t\t\t\t\t'" . FormatDateForSQL($_POST['AdmissionDate']) . "',\n\t\t\t\t\t\t'" . $_POST['PatientNo'] . "',\n\t\t\t\t\t\t'" . $_POST['BranchNo'] . "',\n\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t'" . _('Invoice for admission of Patient number') . ' ' . $_POST['PatientNo'] . "',\n\t\t\t\t\t\t-1,\n\t\t\t\t\t\t'" . $_POST['Price'] . "',\n\t\t\t\t\t\t1,\n\t\t\t\t\t\t0\n\t\t\t\t\t)"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Stock movement records for') . ' ' . $_POST['StockID'] . ' ' . _('could not be inserted because'); $DbgMsg = _('The following SQL to insert the stock movement records was used'); $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); $SQL = "SELECT salestype\n\t\t\t\tFROM debtorsmaster\n\t\t\t\tWHERE debtorno='" . $_POST['PatientNo'] . "'"; $Result = DB_query($SQL, $db); $myrow = DB_fetch_array($Result); $SalesGLAccounts = GetSalesGLAccount('AN', $_POST['StockID'], $myrow['salestype'], $db); $SQL = "INSERT INTO gltrans (\ttype,\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\tdefaulttag,\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\tVALUES ( 10,\n\t\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_POST['AdmissionDate']) . "',\n\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t'" . $SalesGLAccounts['salesglcode'] . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['DefaultTag'] . "',\n\t\t\t\t\t\t\t\t\t'" . _('Invoice for admission of Patient number') . ' ' . $_POST['PatientNo'] . "',\n\t\t\t\t\t\t\t\t\t'" . -$_POST['Price'] . "')"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The cost of sales GL posting could not be inserted because'); $DbgMsg = _('The following SQL to insert the GLTrans record was used'); $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); $SQL = "INSERT INTO gltrans (\ttype,\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\tdefaulttag,\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 ( 10,\n\t\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_POST['AdmissionDate']) . "',\n\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['CompanyRecord']['debtorsact'] . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['DefaultTag'] . "',\n\t\t\t\t\t\t\t\t\t'" . _('Invoice for admission of Patient number') . ' ' . $_POST['PatientNo'] . "',\n\t\t\t\t\t\t\t\t\t'" . $_POST['Price'] . "')"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The stock side of the cost of sales GL posting could not be inserted because'); $DbgMsg = _('The following SQL to insert the GLTrans record was used'); $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); if (isset($_POST['SubmitCash'])) { $ReceiptNumber = GetNextTransNo(12, $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\tdefaulttag,\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 (12,\n\t\t\t\t\t\t\t\t\t\t'" . $ReceiptNumber . "',\n\t\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_POST['AdmissionDate']) . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_POST['BankAccount'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['DefaultTag'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . _('Payment of invoice for admission of Patient number') . ' ' . $_POST['PatientNo'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_POST['Received'] . "')"; $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 */
/*first the cost of sales entry*/ $SQL = "INSERT INTO gltrans (\ttype,\r\n\t\t\t\t\t\t\t\t\t\t\t\ttypeno,\r\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\t\t\t\t\t\t\taccount,\r\n\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\r\n\t\t\t\t\t\t\t\t\t\t\t\tamount)\r\n\t\t\t\t\t\t\t\t\t\tVALUES ( 11,\r\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\r\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $ReturnDate . "',\r\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\r\n\t\t\t\t\t\t\t\t\t\t\t\t'" . GetCOGSGLAccount($Area, $ReturnItemLine->StockID, $_SESSION['Items' . $identifier]->DefaultSalesType, $db) . "',\r\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->DebtorNo . " - " . $ReturnItemLine->StockID . " x " . -$ReturnItemLine->Quantity . " @ " . $ReturnItemLine->StandardCost . "',\r\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $ReturnItemLine->StandardCost * -$ReturnItemLine->Quantity . "')"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The cost of sales GL posting could not be inserted because'); $DbgMsg = _('The following SQL to insert the GLTrans record was used'); $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); /*now the stock entry*/ $StockGLCode = GetStockGLCode($ReturnItemLine->StockID, $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\t\t\t\t\tVALUES ( 11,\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $ReturnDate . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $StockGLCode['stockact'] . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->DebtorNo . " - " . $ReturnItemLine->StockID . " x " . -$ReturnItemLine->Quantity . " @ " . $ReturnItemLine->StandardCost . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $ReturnItemLine->StandardCost * $ReturnItemLine->Quantity . "')"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The stock side of the cost of sales GL posting could not be inserted because'); $DbgMsg = _('The following SQL to insert the GLTrans record was used'); $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); } /* end of if GL and stock integrated and standard cost !=0 */ if ($_SESSION['CompanyRecord']['gllink_debtors'] == 1 and $ReturnItemLine->Price != 0) { //Post sales transaction to GL credit sales $SalesGLAccounts = GetSalesGLAccount($Area, $ReturnItemLine->StockID, $_SESSION['Items' . $identifier]->DefaultSalesType, $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\t\t\t\t\tVALUES ( 11,\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $ReturnDate . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $SalesGLAccounts['salesglcode'] . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->DebtorNo . " - " . $ReturnItemLine->StockID . " x " . -$ReturnItemLine->Quantity . " @ " . $ReturnItemLine->Price . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $ReturnItemLine->Price * $ReturnItemLine->Quantity / $ExRate . "')"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The sales GL posting could not be inserted because'); $DbgMsg = '<br />' . _('The following SQL to insert the GLTrans record was used'); $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); if ($ReturnItemLine->DiscountPercent != 0) { $SQL = "INSERT INTO gltrans (type,\r\n\t\t\t\t\t\t\t\t\t\t\t\ttypeno,\r\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\t\t\t\t\t\t\taccount,\r\n\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\r\n\t\t\t\t\t\t\t\t\t\t\t\tamount )\r\n\t\t\t\t\t\t\t\t\t\t\t\tVALUES ( 11,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $ReturnDate . "',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $SalesGLAccounts['discountglcode'] . "',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->DebtorNo . " - " . $ReturnItemLine->StockID . " @ " . $ReturnItemLine->DiscountPercent * 100 . "%',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . -($ReturnItemLine->Price * $ReturnItemLine->Quantity * $ReturnItemLine->DiscountPercent / $ExRate) . "')"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The sales discount GL posting could not be inserted because'); $DbgMsg = _('The following SQL to insert the GLTrans record was used'); $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); } /*end of if discount !=0 */ } /*end of if sales integrated with debtors */ } /*end of OrderLine loop */