function InsertGLAccountSection($AccountSectionDetails, $user, $password) { $Errors = array(); $db = db($user, $password); if (gettype($db) == 'integer') { $Errors[0] = NoAuthorisation; return $Errors; } foreach ($AccountSectionDetails as $key => $value) { $AccountSectionDetails[$key] = DB_escape_string($value); } $Errors = VerifyAccountSection($AccountSectionDetails['sectionname'], sizeof($Errors), $Errors, $db); if (isset($AccountSectionDetails['accountname'])) { $Errors = VerifySectionName($AccountSectionDetails['sectionname'], sizeof($Errors), $Errors); } $FieldNames = ''; $FieldValues = ''; foreach ($AccountSectionDetails as $key => $value) { $FieldNames .= $key . ', '; $FieldValues .= '"' . $value . '", '; } if (sizeof($Errors) == 0) { $sql = "INSERT INTO accountsection ('" . mb_substr($FieldNames, 0, -2) . "')\n\t\t\t\t\tVALUES ('" . mb_substr($FieldValues, 0, -2) . "')"; $result = DB_Query($sql, $db); if (DB_error_no($db) != 0) { $Errors[0] = DatabaseUpdateFailed; } else { $Errors[0] = 0; } } return $Errors; }
function InsertGLAccount($AccountDetails, $user, $password) { $Errors = array(); $db = db($user, $password); if (gettype($db) == 'integer') { $Errors[0] = NoAuthorisation; return $Errors; } foreach ($AccountDetails as $key => $value) { $AccountDetails[$key] = DB_escape_string($value); } $Errors = VerifyAccountCode($AccountDetails['accountcode'], sizeof($Errors), $Errors, $db); if (isset($AccountDetails['accountname'])) { $Errors = VerifyAccountName($AccountDetails['accountname'], sizeof($Errors), $Errors); } $Errors = VerifyAccountGroupExists($AccountDetails['group_'], sizeof($Errors), $Errors, $db); $FieldNames = ''; $FieldValues = ''; foreach ($AccountDetails as $key => $value) { $FieldNames .= $key . ', '; $FieldValues .= '"' . $value . '", '; } if (sizeof($Errors) == 0) { $sql = "INSERT INTO chartmaster (" . mb_substr($FieldNames, 0, -2) . ") " . "VALUES ('" . mb_substr($FieldValues, 0, -2) . "') "; $result = DB_Query($sql, $db); $sql = "INSERT INTO chartdetails (accountcode,\n\t\t\t\t\t\t\tperiod)\n\t\t\t\tSELECT " . $AccountDetails['accountcode'] . ",\n\t\t\t\t\tperiodno\n\t\t\t\tFROM periods"; $result = DB_query($sql, $db, '', '', '', false); if (DB_error_no($db) != 0) { $Errors[0] = DatabaseUpdateFailed; } else { $Errors[0] = 0; } } return $Errors; }
function db($user, $password) { $_SESSION['UserID'] = $user; $sql = "SELECT userid,\n\t\t\t\t\t\taccesslevel\n\t\t\t\tFROM www_users\n\t\t\t\tWHERE userid='" . DB_escape_string($user) . "'\n\t\t\t\tAND (password='******'\n\t\t\t\tOR password='******')"; $Auth_Result = DB_query($sql, $_SESSION['db']); $myrow = DB_fetch_row($Auth_Result); if (DB_num_rows($Auth_Result) > 0) { $sql = 'SELECT tokenid FROM securitygroups WHERE secroleid = ' . $_SESSION['AccessLevel']; $Sec_Result = DB_query($sql, $db); $_SESSION['AllowedPageSecurityTokens'] = array(); if (DB_num_rows($Sec_Result) == 0) { return NoAuthorisation; } else { $i = 0; while ($myrow = DB_fetch_row($Sec_Result)) { $_SESSION['AllowedPageSecurityTokens'][$i] = $myrow[0]; $i++; } } return $_SESSION['db']; } else { return NoAuthorisation; } }
function InsertGLAccountGroup($AccountGroupDetails, $user, $password) { $Errors = array(); $db = db($user, $password); if (gettype($db) == 'integer') { $Errors[0] = NoAuthorisation; return $Errors; } foreach ($AccountGroupDetails as $key => $value) { $AccountGroupDetails[$key] = DB_escape_string($value); } $Errors = VerifyAccountGroup($AccountGroupDetails['groupname'], sizeof($Errors), $Errors, $db); $Errors = VerifyAccountSectionExists($AccountGroupDetails['sectioninaccounts'], sizeof($Errors), $Errors, $db); if (isset($AccountGroupDetails['pandl'])) { $Errors = VerifyPandL($AccountGroupDetails['pandl'], sizeof($Errors), $Errors); } $Errors = VerifyParentGroupExists($AccountGroupDetails['parentgroupname'], sizeof($Errors), $Errors, $db); $FieldNames = ''; $FieldValues = ''; foreach ($AccountGroupDetails as $key => $value) { $FieldNames .= $key . ', '; $FieldValues .= '"' . $value . '", '; } if (sizeof($Errors) == 0) { $sql = 'INSERT INTO accountgroups (' . substr($FieldNames, 0, -2) . ') ' . 'VALUES (' . substr($FieldValues, 0, -2) . ') '; $result = DB_Query($sql, $db); if (DB_error_no($db) != 0) { $Errors[0] = DatabaseUpdateFailed; } else { $Errors[0] = 0; } } return $Errors; }
function db($user, $password) { $_SESSION['UserID'] = $user; $sql = "SELECT userid\n\t\t\tFROM www_users\n\t\t\tWHERE userid='" . DB_escape_string($user) . "'\n\t\t\tAND (password='******'\n\t\t\tOR password='******')"; $Auth_Result = DB_query($sql, $_SESSION['db']); $myrow = DB_fetch_row($Auth_Result); if (DB_num_rows($Auth_Result) > 0) { return $_SESSION['db']; } else { return NoAuthorisation; } }
function ModifyStockCategory($CategoryDetails, $user, $password) { $Errors = array(); $db = db($user, $password); if (gettype($db) == 'integer') { $Errors[0] = NoAuthorisation; return $Errors; } foreach ($CategoryDetails as $key => $value) { $CategoryDetails[$key] = DB_escape_string($value); } $Errors = VerifyStockCategoryExists($CategoryDetails['categoryid'], sizeof($Errors), $Errors, $db); $Errors = VerifyCategoryID($CategoryDetails['categoryid'], sizeof($Errors), $Errors); $Errors = VerifyCategoryDescription($CategoryDetails['categorydescription'], sizeof($Errors), $Errors); $Errors = VerifyStockType($CategoryDetails['stocktype'], sizeof($Errors), $Errors); $Errors = VerifyAccountCodeExists($CategoryDetails['stockact'], sizeof($Errors), $Errors, $db); $Errors = VerifyAccountCodeExists($CategoryDetails['adjglact'], sizeof($Errors), $Errors, $db); $Errors = VerifyAccountCodeExists($CategoryDetails['purchpricevaract'], sizeof($Errors), $Errors, $db); $Errors = VerifyAccountCodeExists($CategoryDetails['materialuseagevarac'], sizeof($Errors), $Errors, $db); $Errors = VerifyAccountCodeExists($CategoryDetails['wipact'], sizeof($Errors), $Errors, $db); $FieldNames = ''; $FieldValues = ''; foreach ($CategoryDetails as $key => $value) { $FieldNames .= $key . ', '; $FieldValues .= '"' . $value . '", '; } $sql = "UPDATE stockcategory SET "; foreach ($CategoryDetails as $key => $value) { $sql .= $key . "='" . $value . "', "; } $sql = mb_substr($sql, 0, -2) . " WHERE categoryid='" . $CategoryDetails['categoryid'] . "'"; if (sizeof($Errors) == 0) { $result = DB_Query($sql, $db); echo DB_error_no($db); if (DB_error_no($db) != 0) { $Errors[0] = DatabaseUpdateFailed; } else { $Errors[0] = 0; } } return $Errors; }
function ModifyBranch($BranchDetails, $user, $password) { $Errors = array(); $db = db($user, $password); if (gettype($db) == 'integer') { $Errors[0] = NoAuthorisation; return $Errors; } foreach ($BranchDetails as $key => $value) { $BranchDetails[$key] = DB_escape_string($value); } $Errors = VerifyBranchNoExists($BranchDetails['debtorno'], $BranchDetails['branchcode'], sizeof($Errors), $Errors, $db); $Errors = VerifyBranchName($BranchDetails['brname'], sizeof($Errors), $Errors, $db); if (isset($BranchDetails['address1'])) { $Errors = VerifyBranchAddressLine($BranchDetails['address1'], 40, sizeof($Errors), $Errors, $db); } if (isset($BranchDetails['address2'])) { $Errors = VerifyBranchAddressLine($BranchDetails['address2'], 40, sizeof($Errors), $Errors, $db); } if (isset($BranchDetails['address3'])) { $Errors = VerifyBranchAddressLine($BranchDetails['address3'], 40, sizeof($Errors), $Errors, $db); } if (isset($BranchDetails['address4'])) { $Errors = VerifyBranchAddressLine($BranchDetails['address4'], 50, sizeof($Errors), $Errors, $db); } if (isset($BranchDetails['address5'])) { $Errors = VerifyBranchAddressLine($BranchDetails['address5'], 20, sizeof($Errors), $Errors, $db); } if (isset($BranchDetails['address6'])) { $Errors = VerifyBranchAddressLine($BranchDetails['address6'], 15, sizeof($Errors), $Errors, $db); } if (isset($BranchDetails['lat'])) { $Errors = VerifyLatitude($BranchDetails['lat'], sizeof($Errors), $Errors); } if (isset($BranchDetails['lng'])) { $Errors = VerifyLongitude($BranchDetails['lng'], sizeof($Errors), $Errors); } if (isset($BranchDetails['estdeliverydays'])) { $Errors = VerifyEstDeliveryDays($BranchDetails['estdeliverydays'], sizeof($Errors), $Errors); } if (isset($BranchDetails['area'])) { $Errors = VerifyAreaCode($BranchDetails['area'], sizeof($Errors), $Errors, $db); } if (isset($BranchDetails['salesman'])) { $Errors = VerifySalesmanCode($BranchDetails['salesman'], sizeof($Errors), $Errors, $db); } if (isset($BranchDetails['fwddate'])) { $Errors = VerifyFwdDate($BranchDetails['fwddate'], sizeof($Errors), $Errors); } if (isset($BranchDetails['phoneno'])) { $Errors = VerifyPhoneNumber($BranchDetails['phoneno'], sizeof($Errors), $Errors); } if (isset($BranchDetails['faxno'])) { $Errors = VerifyFaxNumber($BranchDetails['faxno'], sizeof($Errors), $Errors); } if (isset($BranchDetails['contactname'])) { $Errors = VerifyContactName($BranchDetails['contactname'], sizeof($Errors), $Errors); } if (isset($BranchDetails['email'])) { $Errors = VerifyEmailAddress($BranchDetails['email'], sizeof($Errors), $Errors); } if (isset($BranchDetails['defaultlocation'])) { $Errors = VerifyDefaultLocation($BranchDetails['defaultlocation'], sizeof($Errors), $Errors, $db); } if (isset($BranchDetails['taxgroupid'])) { $Errors = VerifyTaxGroupId($BranchDetails['taxgroupid'], sizeof($Errors), $Errors, $db); } if (isset($BranchDetails['defaultshipvia'])) { $Errors = VerifyDefaultShipVia($BranchDetails['defaultshipvia'], sizeof($Errors), $Errors, $db); } if (isset($BranchDetails['deliverblind'])) { $Errors = VerifyDeliverBlind($BranchDetails['deliverblind'], sizeof($Errors), $Errors); } if (isset($BranchDetails['disabletrans'])) { $Errors = VerifyDisableTrans($BranchDetails['disabletrans'], sizeof($Errors), $Errors); } if (isset($BranchDetails['brpostaddr1'])) { $Errors = VerifyBranchAddressLine($BranchDetails['brpostaddr1'], 40, sizeof($Errors), $Errors, $db); } if (isset($BranchDetails['brpostaddr2'])) { $Errors = VerifyBranchAddressLine($BranchDetails['brpostaddr2'], 40, sizeof($Errors), $Errors, $db); } if (isset($BranchDetails['brpostaddr3'])) { $Errors = VerifyBranchAddressLine($BranchDetails['brpostaddr3'], 30, sizeof($Errors), $Errors, $db); } if (isset($BranchDetails['brpostaddr4'])) { $Errors = VerifyBranchAddressLine($BranchDetails['brpostaddr4'], 20, sizeof($Errors), $Errors, $db); } if (isset($BranchDetails['brpostaddr5'])) { $Errors = VerifyBranchAddressLine($BranchDetails['brpostaddr5'], 20, sizeof($Errors), $Errors, $db); } if (isset($BranchDetails['brpostaddr6'])) { $Errors = VerifyBranchAddressLine($BranchDetails['brpostaddr6'], 15, sizeof($Errors), $Errors, $db); } if (isset($BranchDetails['specialinstructions'])) { $Errors = VerifySpecialInstructions($BranchDetails['specialinstructions'], sizeof($Errors), $Errors); } if (isset($BranchDetails['custbranchcode'])) { $Errors = VerifyCustBranchCode($BranchDetails['custbranchcode'], sizeof($Errors), $Errors); } $FieldNames = ''; $FieldValues = ''; foreach ($BranchDetails as $key => $value) { $FieldNames .= $key . ', '; $FieldValues .= '"' . $value . '", '; } $sql = 'UPDATE custbranch SET '; foreach ($BranchDetails as $key => $value) { $sql .= $key . '="' . $value . '", '; } $sql = mb_substr($sql, 0, -2) . " WHERE debtorno='" . $BranchDetails['debtorno'] . "'\n AND branchcode='" . $BranchDetails['branchcode'] . "'"; if (sizeof($Errors) == 0) { $result = DB_Query($sql, $db); if (DB_error_no() != 0) { $Errors[0] = DatabaseUpdateFailed; } else { $Errors[0] = 0; } } return $Errors; }
function InsertSalesCredit($CreditDetails, $user, $password) { $Errors = array(); $db = db($user, $password); if (gettype($db) == 'integer') { $Errors[0] = NoAuthorisation; return $Errors; } foreach ($CreditDetails as $key => $value) { $CreditDetails[$key] = DB_escape_string($value); } $PartCode = $CreditDetails['partcode']; $Errors = VerifyStockCodeExists($PartCode, sizeof($Errors), $Errors, $db); unset($CreditDetails['partcode']); $SalesArea = $CreditDetails['salesarea']; unset($CreditDetails['salesarea']); $CreditDetails['transno'] = GetNextTransactionNo(11, $db); $CreditDetails['type'] = 10; $Errors = VerifyDebtorExists($CreditDetails['debtorno'], sizeof($Errors), $Errors, $db); $Errors = VerifyBranchNoExists($CreditDetails['debtorno'], $CreditDetails['branchcode'], sizeof($Errors), $Errors, $db); $Errors = VerifyTransNO($CreditDetails['transno'], 10, sizeof($Errors), $Errors, $db); $Errors = VerifyTransactionDate($CreditDetails['trandate'], sizeof($Errors), $Errors, $db); if (isset($CreditDetails['settled'])) { $Errors = VerifySettled($CreditDetails['settled'], sizeof($Errors), $Errors); } if (isset($CreditDetails['reference'])) { $Errors = VerifyReference($CreditDetails['reference'], sizeof($Errors), $Errors); } if (isset($CreditDetails['tpe'])) { $Errors = VerifyTpe($CreditDetails['tpe'], sizeof($Errors), $Errors); } if (isset($CreditDetails['order_'])) { $Errors = VerifyOrderNumber($CreditDetails['order_'], sizeof($Errors), $Errors); } if (isset($CreditDetails['rate'])) { $Errors = VerifyExchangeRate($CreditDetails['rate'], sizeof($Errors), $Errors); } if (isset($CreditDetails['ovamount'])) { $Errors = VerifyOVAmount($CreditDetails['ovamount'], sizeof($Errors), $Errors); } if (isset($CreditDetails['ovgst'])) { $Errors = VerifyOVGst($CreditDetails['ovgst'], sizeof($Errors), $Errors); } if (isset($CreditDetails['ovfreight'])) { $Errors = VerifyOVFreight($CreditDetails['ovfreight'], sizeof($Errors), $Errors); } if (isset($CreditDetails['ovdiscount'])) { $Errors = VerifyOVDiscount($CreditDetails['ovdiscount'], sizeof($Errors), $Errors); } if (isset($CreditDetails['diffonexch'])) { $Errors = VerifyDiffOnExchange($CreditDetails['diffonexch'], sizeof($Errors), $Errors); } if (isset($CreditDetails['alloc'])) { $Errors = VerifyAllocated($CreditDetails['alloc'], sizeof($Errors), $Errors); } if (isset($CreditDetails['invtext'])) { $Errors = VerifyInvoiceText($CreditDetails['invtext'], sizeof($Errors), $Errors); } if (isset($CreditDetails['shipvia'])) { $Errors = VerifyShipVia($CreditDetails['shipvia'], sizeof($Errors), $Errors); } if (isset($CreditDetails['edisent'])) { $Errors = VerifyEdiSent($CreditDetails['edisent'], sizeof($Errors), $Errors); } if (isset($CreditDetails['consignment'])) { $Errors = VerifyConsignment($CreditDetails['consignment'], sizeof($Errors), $Errors); } $FieldNames = ''; $FieldValues = ''; $CreditDetails['trandate'] = ConvertToSQLDate($CreditDetails['trandate']); $CreditDetails['prd'] = GetPeriodFromTransactionDate($CreditDetails['trandate'], sizeof($Errors), $Errors, $db); foreach ($CreditDetails as $key => $value) { $FieldNames .= $key . ', '; $FieldValues .= '"' . $value . '", '; } if (sizeof($Errors) == 0) { $result = DB_Txn_Begin($db); $sql = "INSERT INTO debtortrans (" . mb_substr($FieldNames, 0, -2) . ")\n\t\t\t\t\t\tVALUES ('" . mb_substr($FieldValues, 0, -2) . "') "; $result = DB_Query($sql, $db); $sql = "UPDATE systypes SET typeno='" . GetNextTransactionNo(11, $db) . "' WHERE typeid=10"; $result = DB_Query($sql, $db); $SalesGLCode = GetSalesGLCode($SalesArea, $PartCode, $db); $DebtorsGLCode = GetDebtorsGLCode($db); $sql = "INSERT INTO gltrans VALUES(null,\n\t\t\t\t\t\t\t\t\t\t\t10,\n\t\t\t\t\t\t\t\t\t\t\t'" . GetNextTransactionNo(11, $db) . "',\n\t\t\t\t\t\t\t\t\t\t\t0,\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditDetails['trandate'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditDetails['prd'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $DebtorsGLCode . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . _('Invoice for') . ' - ' . $CreditDetails['debtorno'] . ' ' . -'Total' . ' - ' . $CreditDetails['ovamount'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditDetails['ovamount'] . "',\n\t\t\t\t\t\t\t\t\t\t\t0,\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditDetails['jobref'] . "')"; $result = DB_Query($sql, $db); $sql = "INSERT INTO gltrans VALUES(null,\n\t\t\t\t\t\t\t\t\t\t\t10,\n\t\t\t\t\t\t\t\t\t\t\t'" . GetNextTransactionNo(11, $db) . "',\n\t\t\t\t\t\t\t\t\t\t\t0,\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditDetails['trandate'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditDetails['prd'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $SalesGLCode . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . _('Invoice for') . ' - ' . $CreditDetails['debtorno'] . ' ' . _('Total') . ' - ' . $CreditDetails['ovamount'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . -intval($CreditDetails['ovamount']) . "',\n\t\t\t\t\t\t\t\t\t\t\t0,\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditDetails['jobref'] . "')"; $result = DB_Query($sql, $db); $result = DB_Txn_Commit($db); if (DB_error_no($db) != 0) { $Errors[0] = DatabaseUpdateFailed; } else { $Errors[0] = 0; } return $Errors; } else { return $Errors; } }
function ModifyCustomer($CustomerDetails, $user, $password) { $Errors = array(); $db = db($user, $password); if (gettype($db) == 'integer') { $Errors[0] = NoAuthorisation; return $Errors; } foreach ($CustomerDetails as $key => $value) { $CustomerDetails[$key] = DB_escape_string($value); } if (!isset($CustomerDetails['debtorno'])) { $Errors[sizeof($Errors)] = NoDebtorNumber; return $Errors; } $Errors = VerifyDebtorExists($CustomerDetails['debtorno'], sizeof($Errors), $Errors, $db); if (in_array(DebtorDoesntExist, $Errors)) { return $Errors; } if (isset($CustomerDetails['name'])) { $Errors = VerifyDebtorName($CustomerDetails['name'], sizeof($Errors), $Errors); } if (isset($CustomerDetails['address1'])) { $Errors = VerifyAddressLine($CustomerDetails['address1'], 40, sizeof($Errors), $Errors); } if (isset($CustomerDetails['address2'])) { $Errors = VerifyAddressLine($CustomerDetails['address2'], 40, sizeof($Errors), $Errors); } if (isset($CustomerDetails['address3'])) { $Errors = VerifyAddressLine($CustomerDetails['address3'], 40, sizeof($Errors), $Errors); } if (isset($CustomerDetails['address4'])) { $Errors = VerifyAddressLine($CustomerDetails['address4'], 50, sizeof($Errors), $Errors); } if (isset($CustomerDetails['address5'])) { $Errors = VerifyAddressLine($CustomerDetails['address5'], 20, sizeof($Errors), $Errors); } if (isset($CustomerDetails['address6'])) { $Errors = VerifyAddressLine($CustomerDetails['address6'], 15, sizeof($Errors), $Errors); } if (isset($CustomerDetails['currcode'])) { $Errors = VerifyCurrencyCode($CustomerDetails['currcode'], sizeof($Errors), $Errors, $db); } if (isset($CustomerDetails['salestype'])) { $Errors = VerifySalesType($CustomerDetails['salestype'], sizeof($Errors), $Errors, $db); } if (isset($CustomerDetails['clientsince'])) { $Errors = VerifyClientSince($CustomerDetails['clientsince'], sizeof($Errors), $Errors); } if (isset($CustomerDetails['holdreason'])) { $Errors = VerifyHoldReason($CustomerDetails['holdreason'], sizeof($Errors), $Errors, $db); } if (isset($CustomerDetails['paymentterms'])) { $Errors = VerifyPaymentTerms($CustomerDetails['paymentterms'], sizeof($Errors), $Errors, $db); } if (isset($CustomerDetails['discount'])) { $Errors = VerifyDiscount($CustomerDetails['discount'], sizeof($Errors), $Errors); } if (isset($CustomerDetails['pymtdiscount'])) { $Errors = VerifyPymtDiscount($CustomerDetails['pymtdiscount'], sizeof($Errors), $Errors); } if (isset($CustomerDetails['lastpaid'])) { $Errors = VerifyLastPaid($CustomerDetails['lastpaid'], sizeof($Errors), $Errors); } if (isset($CustomerDetails['lastpaiddate'])) { $Errors = VerifyLastPaidDate($CustomerDetails['lastpaiddate'], sizeof($Errors), $Errors); } if (isset($CustomerDetails['creditlimit'])) { $Errors = VerifyCreditLimit($CustomerDetails['creditlimit'], sizeof($Errors), $Errors); } if (isset($CustomerDetails['invaddrbranch'])) { $Errors = VerifyInvAddrBranch($CustomerDetails['invaddrbranch'], sizeof($Errors), $Errors); } if (isset($CustomerDetails['discountcode'])) { $Errors = VerifyDiscountCode($CustomerDetails['discountcode'], sizeof($Errors), $Errors); } if (isset($CustomerDetails['ediinvoices'])) { $Errors = VerifyEDIInvoices($CustomerDetails['ediinvoices'], sizeof($Errors), $Errors); } if (isset($CustomerDetails['ediorders'])) { $Errors = VerifyEDIOrders($CustomerDetails['ediorders'], sizeof($Errors), $Errors); } if (isset($CustomerDetails['edireference'])) { $Errors = VerifyEDIReference($CustomerDetails['edireference'], sizeof($Errors), $Errors); } if (isset($CustomerDetails['editransport'])) { $Errors = VerifyEDITransport($CustomerDetails['editransport'], sizeof($Errors), $Errors); } if (isset($CustomerDetails['ediserveruser'])) { $Errors = VerifyEDIServerUser($CustomerDetails['ediserveruser'], sizeof($Errors), $Errors); } if (isset($CustomerDetails['ediserverpwd'])) { $Errors = VerifyEDIServerPassword($CustomerDetails['ediserverpwd'], sizeof($Errors), $Errors); } if (isset($CustomerDetails['taxref'])) { $Errors = VerifyTaxRef($CustomerDetails['taxref'], sizeof($Errors), $Errors); } if (isset($CustomerDetails['customerpoline'])) { $Errors = VerifyCustomerPOLine($CustomerDetails['customerpoline'], sizeof($Errors), $Errors); } if (isset($CustomerDetails['typeid'])) { $Errors = VerifyCustomerType($CustomerDetails['typeid'], sizeof($Errors), $Errors, $db); } $sql = "UPDATE debtorsmaster SET "; foreach ($CustomerDetails as $key => $value) { $sql .= $key . '="' . $value . '", '; } $sql = mb_substr($sql, 0, -2) . " WHERE debtorno='" . $CustomerDetails['debtorno'] . "'"; if (sizeof($Errors) == 0) { $result = DB_Query($sql, $db); if (DB_error_no($db) != 0) { $Errors[0] = DatabaseUpdateFailed; } else { $Errors[0] = 0; } } return $Errors; }
$SQL[] = "UPDATE config SET confvalue = '" . DB_escape_string($_POST['X_ShopManagerEmail']) . "' WHERE confname = 'ShopManagerEmail'"; } if ($_SESSION['ShopPrivacyStatement'] != $_POST['X_ShopPrivacyStatement']) { $SQL[] = "UPDATE config SET confvalue = '" . DB_escape_string($_POST['X_ShopPrivacyStatement']) . "' WHERE confname = 'ShopPrivacyStatement'"; } if ($_SESSION['ShopFreightPolicy'] != $_POST['X_ShopFreightPolicy']) { $SQL[] = "UPDATE config SET confvalue = '" . DB_escape_string($_POST['X_ShopFreightPolicy']) . "' WHERE confname = 'ShopFreightPolicy'"; } if ($_SESSION['ShopTermsConditions'] != $_POST['X_ShopTermsConditions']) { $SQL[] = "UPDATE config SET confvalue = '" . DB_escape_string($_POST['X_ShopTermsConditions']) . "' WHERE confname = 'ShopTermsConditions'"; } if ($_SESSION['ShopAboutUs'] != $_POST['X_ShopAboutUs']) { $SQL[] = "UPDATE config SET confvalue = '" . DB_escape_string($_POST['X_ShopAboutUs']) . "' WHERE confname = 'ShopAboutUs'"; } if ($_SESSION['ShopContactUs'] != $_POST['X_ShopContactUs']) { $SQL[] = "UPDATE config SET confvalue = '" . DB_escape_string($_POST['X_ShopContactUs']) . "' WHERE confname = 'ShopContactUs'"; } if ($_SESSION['ShopDebtorNo'] != $_POST['X_ShopDebtorNo']) { $SQL[] = "UPDATE config SET confvalue = '" . $_POST['X_ShopDebtorNo'] . "' WHERE confname = 'ShopDebtorNo'"; } if ($_SESSION['ShopBranchCode'] != $_POST['X_ShopBranchCode']) { $SQL[] = "UPDATE config SET confvalue = '" . $_POST['X_ShopBranchCode'] . "' WHERE confname = 'ShopBranchCode'"; } if ($_SESSION['ShopShowOnlyAvailableItems'] != $_POST['X_ShopShowOnlyAvailableItems']) { $SQL[] = "UPDATE config SET confvalue = '" . $_POST['X_ShopShowOnlyAvailableItems'] . "' WHERE confname = 'ShopShowOnlyAvailableItems'"; } if ($_SESSION['ShopShowQOHColumn'] != $_POST['X_ShopShowQOHColumn']) { $SQL[] = "UPDATE config SET confvalue = '" . $_POST['X_ShopShowQOHColumn'] . "' WHERE confname = 'ShopShowQOHColumn'"; } if (isset($_POST['X_ShopStockLocations'])) { $ShopStockLocations = '';
$MailText = _('This email has been automatically generated by the webERP installation at') . ' ' . $_SESSION['CompanyRecord']['coyname'] . "\n"; $MailText .= _('The following offers you made have been accepted') . "\n"; $MailText .= _('An official order will be sent to you in due course') . "\n\n"; $sql = "SELECT rate FROM currencies where currabrev='" . $CurrCode . "'"; $result = DB_query($sql); $myrow = DB_fetch_array($result); $Rate = $myrow['rate']; $OrderNo = GetNextTransNo(18, $db); $sql = "INSERT INTO purchorders (\n\t\t\t\t\torderno,\n\t\t\t\t\tsupplierno,\n\t\t\t\t\torddate,\n\t\t\t\t\trate,\n\t\t\t\t\tinitiator,\n\t\t\t\t\tintostocklocation,\n\t\t\t\t\tdeliverydate,\n\t\t\t\t\tstatus,\n\t\t\t\t\tstat_comment,\n\t\t\t\t\tpaymentterms)\n\t\t\t\tVALUES (\n\t\t\t\t\t'" . $OrderNo . "',\n\t\t\t\t\t'" . $_POST['supplierid'] . "',\n\t\t\t\t\t'" . date('Y-m-d') . "',\n\t\t\t\t\t'" . $Rate . "',\n\t\t\t\t\t'" . $_SESSION['UserID'] . "',\n\t\t\t\t\t'" . $_SESSION['DefaultFactoryLocation'] . "',\n\t\t\t\t\t'" . date('Y-m-d') . "',\n\t\t\t\t\t'" . _('Pending') . "',\n\t\t\t\t\t'" . _('Automatically generated from tendering system') . "',\n\t\t\t\t\t'" . $PaymentTerms . "')"; DB_query($sql); foreach ($Accepts as $AcceptID) { $sql = "SELECT offers.quantity,\n\t\t\t\t\t\t\toffers.price,\n\t\t\t\t\t\t\toffers.uom,\n\t\t\t\t\t\t\tstockmaster.description,\n\t\t\t\t\t\t\tstockmaster.stockid\n\t\t\t\t\t\tFROM offers\n\t\t\t\t\t\tLEFT JOIN stockmaster\n\t\t\t\t\t\t\tON offers.stockid=stockmaster.stockid\n\t\t\t\t\t\tWHERE offerid='" . $AcceptID . "'"; $result = DB_query($sql); $myrow = DB_fetch_array($result); $MailText .= $myrow['description'] . "\t" . _('Quantity') . ' ' . $myrow['quantity'] . "\t" . _('Price') . ' ' . locale_number_format($myrow['price']) . "\n"; $sql = "INSERT INTO purchorderdetails (orderno,\n\t\t\t\t\t\t\t\t\t\t\t\titemcode,\n\t\t\t\t\t\t\t\t\t\t\t\tdeliverydate,\n\t\t\t\t\t\t\t\t\t\t\t\titemdescription,\n\t\t\t\t\t\t\t\t\t\t\t\tunitprice,\n\t\t\t\t\t\t\t\t\t\t\t\tactprice,\n\t\t\t\t\t\t\t\t\t\t\t\tquantityord,\n\t\t\t\t\t\t\t\t\t\t\t\tsuppliersunit)\n\t\t\t\t\t\t\t\t\tVALUES ('" . $OrderNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $myrow['stockid'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . date('Y-m-d') . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . DB_escape_string($myrow['description']) . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $myrow['price'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $myrow['price'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $myrow['quantity'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $myrow['uom'] . "')"; $result = DB_query($sql); $sql = "DELETE FROM offers WHERE offerid='" . $AcceptID . "'"; $result = DB_query($sql); } $mail = new htmlMimeMail(); $mail->setSubject(_('Your offer to') . ' ' . $_SESSION['CompanyRecord']['coyname'] . ' ' . _('has been accepted')); $mail->setText($MailText); $Recipients = GetMailList('OffersReceivedResultRecipients'); if (sizeOf($Recipients) == 0) { prnMsg(_('There are no members of the Offers Received Result Recipients email group'), 'warn'); include 'includes/footer.inc'; exit; } array_push($Recipients, $Email); if ($_SESSION['SmtpSetting'] == 0) {
if ($InvOrCredit == 'Invoice') { $DisplaySubTot = number_format($myrow['ovamount'], 2); $DisplayFreight = number_format($myrow['ovfreight'], 2); $DisplayTax = number_format($myrow['ovgst'], 2); $DisplayTotal = number_format($myrow['ovfreight'] + $myrow['ovgst'] + $myrow['ovamount'], 2); } else { $DisplaySubTot = number_format(-$myrow['ovamount'], 2); $DisplayFreight = number_format(-$myrow['ovfreight'], 2); $DisplayTax = number_format(-$myrow['ovgst'], 2); $DisplayTotal = number_format(-$myrow['ovfreight'] - $myrow['ovgst'] - $myrow['ovamount'], 2); } /* Print out the payment terms */ $pdf->addTextWrap($FormDesign->PaymentTerms->x, $Page_Height - $FormDesign->PaymentTerms->y, $FormDesign->PaymentTerms->Length, $FormDesign->PaymentTerms->FontSize, _('Payment Terms') . ': ' . $myrow['terms']); // $pdf->addText($Page_Width-$Right_Margin-392, $YPos - ($line_height*3)+22,$FontSize, _('Bank Code:***** Bank Account:*****')); // $FontSize=10; $LeftOvers = explode('\\r\\n', DB_escape_string($myrow['invtext'])); for ($i = 0; $i < sizeOf($LeftOvers); $i++) { $pdf->addText($FormDesign->InvoiceText->x, $Page_Height - $FormDesign->InvoiceText->y - $i * 10, $FormDesign->InvoiceText->FontSize, $LeftOvers[$i]); } $pdf->addText($FormDesign->SubTotalCaption->x, $Page_Height - $FormDesign->SubTotalCaption->y, $FormDesign->SubTotalCaption->FontSize, _('Sub Total')); $LeftOvers = $pdf->addTextWrap($FormDesign->SubTotal->x, $Page_Height - $FormDesign->SubTotal->y, $FormDesign->SubTotal->Length, $FormDesign->SubTotal->FontSize, $DisplaySubTot, 'right'); $pdf->addText($FormDesign->FreightCaption->x, $Page_Height - $FormDesign->FreightCaption->y, $FormDesign->FreightCaption->FontSize, _('Freight')); $LeftOvers = $pdf->addTextWrap($FormDesign->Freight->x, $Page_Height - $FormDesign->Freight->y, $FormDesign->Freight->Length, $FormDesign->Freight->FontSize, $DisplayFreight, 'right'); $pdf->addText($FormDesign->TaxCaption->x, $Page_Height - $FormDesign->TaxCaption->y, $FormDesign->TaxCaption->FontSize, _('Tax')); $LeftOvers = $pdf->addTextWrap($FormDesign->Tax->x, $Page_Height - $FormDesign->Tax->y, $FormDesign->Tax->Length, $FormDesign->Tax->FontSize, $DisplayTax, 'right'); /*rule off for total */ $pdf->line($FormDesign->TotalLine->startx, $Page_Height - $FormDesign->TotalLine->starty, $FormDesign->TotalLine->endx, $Page_Height - $FormDesign->TotalLine->endy); /*vertical to separate totals from comments and ROMALPA */ $pdf->line($FormDesign->RomalpaLine->startx, $Page_Height - $FormDesign->RomalpaLine->starty, $FormDesign->RomalpaLine->endx, $Page_Height - $FormDesign->RomalpaLine->endy); if ($InvOrCredit == 'Invoice') { $pdf->addText($FormDesign->TotalCaption->x, $Page_Height - $FormDesign->TotalCaption->y, $FormDesign->TotalCaption->FontSize, _('TOTAL INVOICE'));
function ModifyPurchData($PurchDataDetails, $user, $password) { $Errors = array(); $db = db($user, $password); if (gettype($db) == 'integer') { $Errors[0] = NoAuthorisation; return $Errors; } foreach ($PurchDataDetails as $key => $value) { $PurchDataDetails[$key] = DB_escape_string($value); } $Errors = VerifyPurchDataLineExists($PurchDataDetails['supplierno'], $PurchDataDetails['stockid'], sizeof($Errors), $Errors, $db); $Errors = VerifyStockCodeExists($PurchDataDetails['stockid'], sizeof($Errors), $Errors, $db); $Errors = VerifySupplierNoExists($PurchDataDetails['supplierno'], sizeof($Errors), $Errors, $db); if (isset($StockItemDetails['price'])) { $Errors = VerifyUnitPrice($PurchDataDetails['price'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['suppliersuom'])) { $Errors = VerifySuppliersUOM($PurchDataDetails['suppliersuom'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['conversionfactor'])) { $Errors = VerifyConversionFactor($PurchDataDetails['conversionfactor'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['supplierdescription'])) { $Errors = VerifySupplierDescription($PurchDataDetails['supplierdescription'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['leadtime'])) { $Errors = VerifyLeadTime($PurchDataDetails['leadtime'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['preferred'])) { $Errors = VerifyPreferredFlag($PurchDataDetails['preferred'], sizeof($Errors), $Errors); } $sql = "UPDATE purchdata SET "; foreach ($PurchDataDetails as $key => $value) { $sql .= $key . "='" . $value . "', "; } $sql = mb_substr($sql, 0, -2) . " WHERE stockid='" . $PurchDataDetails['stockid'] . "'\n\t\t\t\t\t\t\t\tAND supplierno='" . $PurchDataDetails['supplierno'] . "'"; if (sizeof($Errors) == 0) { $result = DB_Query($sql, $db); echo DB_error_no($db); if (DB_error_no($db) != 0) { $Errors[0] = DatabaseUpdateFailed; } else { $Errors[0] = 0; } } return $Errors; }
$msg[] = _('You must select at least one test'); } if ($InputError == 1) { foreach ($msg as $message) { prnMsg($message, 'info'); $_POST['ChangeItem'] = 'Yes'; $Patient[0] = $_POST['PatientNo']; $Patient[1] = $_POST['BranchNo']; } } else { DB_Txn_Begin($db); /*First off create the sales order * entries in the database */ $OrderNo = GetNextTransNo(30, $db); $HeaderSQL = "INSERT INTO salesorders (\torderno,\n\t\t\t\t\t\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\t\t\tcomments,\n\t\t\t\t\t\t\t\t\t\t\torddate,\n\t\t\t\t\t\t\t\t\t\t\tshipvia,\n\t\t\t\t\t\t\t\t\t\t\tdeliverto,\n\t\t\t\t\t\t\t\t\t\t\tfromstkloc,\n\t\t\t\t\t\t\t\t\t\t\tdeliverydate,\n\t\t\t\t\t\t\t\t\t\t\tconfirmeddate,\n\t\t\t\t\t\t\t\t\t\t\tdeliverblind)\n\t\t\t\t\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t\t\t\t\t'" . $OrderNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $_POST['PatientNo'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $_POST['BranchNo'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . DB_escape_string($_POST['Comments']) . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_POST['AdmissionDate']) . "',\n\t\t\t\t\t\t\t\t\t\t\t'1',\n\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['UserStockLocation'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['UserStockLocation'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_POST['AdmissionDate']) . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_POST['AdmissionDate']) . "',\n\t\t\t\t\t\t\t\t\t\t\t0\n\t\t\t\t\t\t\t\t\t\t)"; $ErrMsg = _('The order cannot be added because'); $InsertQryResult = DB_query($HeaderSQL, $db, $ErrMsg); for ($i = 0; $i < $_SESSION['Items']['Lines']; $i++) { if (isset($_SESSION['Items'][$i]['StockID'])) { $LineItemSQL = "INSERT INTO salesorderdetails (orderlineno,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\torderno,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tstkcode,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tunitprice,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tquantity,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tdiscountpercent,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\titemdue,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tactualdispatchdate,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tqtyinvoiced,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tcompleted)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $i . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $OrderNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items'][$i]['StockID'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items'][$i]['Price'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'1',\n\t\t\t\t\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\t\t\t\t'" . _('Sales order for radiology transaction') . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_POST['AdmissionDate']) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_POST['AdmissionDate']) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'1',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t)"; $DbgMsg = _('Trouble inserting a line of a sales order. The SQL that failed was'); $Ins_LineItemResult = DB_query($LineItemSQL, $db, $ErrMsg, $DbgMsg, true); if ($_SESSION['Care2xDatabase'] != 'None') { $SQL = "UPDATE " . $_SESSION['Care2xDatabase'] . ".care_encounter_prescription SET bill_number='" . $OrderNo . "'\n\t\t\t\t\t\t\t\tWHERE nr='" . $_SESSION['Items'][$i]['Care2x'] . "'"; $DbgMsg = _('Trouble inserting a line of a sales order. The SQL that failed was'); $UpdateCare2xResult = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); } } } if (isset($_POST['Doctor'])) {
$sql = "INSERT INTO locstock (loccode,\n\t\t\t\t\t\t\t\t\t\t\t\t\tstockid)\n\t\t\t\t\t\t\t\t\t\tSELECT locations.loccode,\n\t\t\t\t\t\t\t\t\t\t'" . $_POST['StockID'] . "'\n\t\t\t\t\t\t\t\t\t\tFROM locations"; $ErrMsg = _('The locations for the item') . ' ' . $_POST['StockID'] . ' ' . _('could not be added because'); $DbgMsg = _('NB Locations records can be added by opening the utility page') . ' <i>Z_MakeStockLocns.php</i> ' . _('The SQL that was used to add the location records that failed was'); $InsResult = DB_query($sql, $db, $ErrMsg, $DbgMsg); DB_Txn_Commit($db); //check for any purchase data $sql = "SELECT purchdata.supplierno,\n suppliers.suppname,\n purchdata.price,\n suppliers.currcode,\n purchdata.effectivefrom,\n purchdata.suppliersuom,\n purchdata.supplierdescription,\n purchdata.leadtime,\n purchdata.suppliers_partno,\n purchdata.minorderqty,\n purchdata.preferred,\n purchdata.conversionfactor,\n currencies.decimalplaces AS currdecimalplaces\n FROM purchdata INNER JOIN suppliers\n ON purchdata.supplierno=suppliers.supplierid\n INNER JOIN currencies\n ON suppliers.currcode=currencies.currabrev\n WHERE purchdata.stockid = '" . $_POST['OldStockID'] . "'\n ORDER BY purchdata.effectivefrom DESC"; $ErrMsg = _('The supplier purchasing details for the selected part could not be retrieved because'); $PurchDataResult = DB_query($sql, $db, $ErrMsg); if (DB_num_rows($PurchDataResult) == 0 and $_POST['OldStockID'] != '') { //prnMsg(_('There is no purchasing data set up for the part selected'), 'info'); $NoPurchasingData = 1; } else { while ($myrow = DB_fetch_array($PurchDataResult)) { //clone the purchase data $sql = "INSERT INTO purchdata (supplierno,\n\t\t\t\t\t\t\t\t\t\tstockid,\n\t\t\t\t\t\t\t\t\t\tprice,\n\t\t\t\t\t\t\t\t\t\teffectivefrom,\n\t\t\t\t\t\t\t\t\t\tsuppliersuom,\n\t\t\t\t\t\t\t\t\t\tconversionfactor,\n\t\t\t\t\t\t\t\t\t\tsupplierdescription,\n\t\t\t\t\t\t\t\t\t\tsuppliers_partno,\n\t\t\t\t\t\t\t\t\t\tleadtime,\n\t\t\t\t\t\t\t\t\t\tminorderqty,\n\t\t\t\t\t\t\t\t\t\tpreferred)\n VALUES ('" . $myrow['supplierno'] . "',\n '" . $_POST['StockID'] . "',\n '" . $myrow['price'] . "',\n '" . $myrow['effectivefrom'] . "',\n '" . $myrow['suppliersuom'] . "',\n '" . $myrow['conversionfactor'] . "',\n '" . DB_escape_string($myrow['supplierdescription']) . "',\n '" . $myrow['suppliers_partno'] . "',\n '" . $myrow['leadtime'] . "',\n '" . $myrow['minorderqty'] . "',\n '" . $myrow['preferred'] . "')"; $ErrMsg = _('The cloned supplier purchasing details could not be added to the database because'); $DbgMsg = _('The SQL that failed was'); $AddResult = DB_query($sql, $db, $ErrMsg, $DbgMsg); } } //For both the following - assume the data taken from the tables has already been validated. //check for price data $sql = "SELECT currencies.currency,\n salestypes.sales_type,\n prices.price,\n prices.stockid,\n prices.typeabbrev,\n prices.currabrev,\n prices.startdate,\n prices.enddate,\n prices.debtorno,\n currencies.decimalplaces AS currdecimalplaces\n FROM prices\n INNER JOIN salestypes\n ON prices.typeabbrev = salestypes.typeabbrev\n INNER JOIN currencies\n ON prices.currabrev=currencies.currabrev\n WHERE prices.stockid='" . $_POST['OldStockID'] . "'\n\n ORDER BY prices.currabrev,\n prices.typeabbrev,\n prices.startdate"; $PricingDataResult = DB_query($sql, $db); //AND prices.debtorno='' if (DB_num_rows($PricingDataResult) == 0 and $_POST['OldStockID'] != '') { prnMsg(_('There is no purchasing data set up for the part selected'), 'info'); $NoPricingData = 1; } else { while ($myrow = DB_fetch_array($PricingDataResult)) {
$Result = DB_query($sql, $db, '', '', false, true); } else { // copy the report and all fields to the new report name $OrigID = $ReportID; // Set the report id to 0 to prepare to copy $sql = "UPDATE " . DBReports . " SET id=0 WHERE id=" . $ReportID . ";"; $Result = DB_query($sql, $db, '', '', false, true); $sql = "INSERT INTO " . DBReports . " SELECT * FROM " . DBReports . " WHERE id = 0;"; $Result = DB_query($sql, $db, '', '', false, true); // Fetch the id entered $ReportID = DB_Last_Insert_ID($db, DBReports, 'id'); // Restore original report ID from 0 $sql = "UPDATE " . DBReports . " SET id=" . $OrigID . " WHERE id=0;"; $Result = DB_query($sql, $db, '', '', false, true); // Set the report name and group name per the form $sql = "UPDATE " . DBReports . " SET\n\t\t\t\t\t\t\treportname = '" . DB_escape_string($_POST['ReportName']) . "'\n\t\t\t\t\t\tWHERE id =" . $ReportID . ";"; $Result = DB_query($sql, $db, '', '', false, true); // fetch the fields and duplicate $sql = "SELECT * FROM " . DBRptFields . " WHERE reportid=" . $OrigID . ";"; $Result = DB_query($sql, $db, '', '', false, true); while ($temp = DB_fetch_array($Result)) { $field[] = $temp; } foreach ($field as $row) { $sql = "INSERT INTO " . DBRptFields . " (reportid, entrytype, seqnum, fieldname,\n\t\t\t\t\t\t\t\tdisplaydesc, visible, columnbreak, params)\n\t\t\t\t\t\t\tVALUES (" . $ReportID . ", '" . $row['entrytype'] . "', " . $row['seqnum'] . ",\n\t\t\t\t\t\t\t\t'" . $row['fieldname'] . "', '" . $row['displaydesc'] . "', '" . $row['visible'] . "',\n\t\t\t\t\t\t\t\t'" . $row['columnbreak'] . "', '" . $row['params'] . "');"; $Result = DB_query($sql, $db, '', '', false, true); } } // read back in new data for next screen (will set defaults as defined in the db) $sql = "SELECT * FROM " . DBReports . " WHERE id='" . $ReportID . "'"; $Result = DB_query($sql, $db, '', '', false, true);
} /* foreach controlled item in the serialitems array */ } /*end if the transferred item is a controlled item */ /* 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='" . $TrfLine->StockID . "'\n\t\t\t\t\t\tAND loccode= '" . $_SESSION['Transfer']->StockLocationTo . "'"; $Result = DB_query($SQL, $db, _('Could not retrieve the quantity on hand at the location being transferred to')); if (DB_num_rows($Result) == 1) { $LocQtyRow = DB_fetch_row($Result); $QtyOnHandPrior = $LocQtyRow[0]; } else { // There must actually be some error this should never happen $QtyOnHandPrior = 0; } // Insert the stock movement for the stock coming into the to location $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\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\tnewqoh)\n\t\t\t\t\tVALUES (\n\t\t\t\t\t\t'" . $TrfLine->StockID . "',\n\t\t\t\t\t\t16,\n\t\t\t\t\t\t'" . $_SESSION['Transfer']->TrfID . "',\n\t\t\t\t\t\t'" . $_SESSION['Transfer']->StockLocationTo . "',\n\t\t\t\t\t\t'" . $SQLTransferDate . "',\n\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t'" . _('From') . ' ' . DB_escape_string($_SESSION['Transfer']->StockLocationFromName) . "',\n\t\t\t\t\t\t'" . round($TrfLine->Quantity, $TrfLine->DecimalPlaces) . "',\n\t\t\t\t\t\t'" . round($QtyOnHandPrior + $TrfLine->Quantity, $TrfLine->DecimalPlaces) . "'\n\t\t\t\t\t\t)"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The stock movement record for the incoming stock cannot be added because'); $DbgMsg = _('The following SQL to insert the stock movement record was used'); $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); /*Get the ID of the StockMove... */ $StkMoveNo = DB_Last_Insert_ID($db, 'stockmoves', 'stkmoveno'); /*Insert the StockSerialMovements and update the StockSerialItems for controlled items*/ if ($TrfLine->Controlled == 1) { foreach ($TrfLine->SerialItems as $Item) { /*We need to add or update the StockSerialItem record and The StockSerialMoves as well */ /*First need to check if the serial items already exists or not in the location to */ $SQL = "SELECT COUNT(*)\n\t\t\t\t\t\t\tFROM stockserialitems\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\tstockid='" . $TrfLine->StockID . "'\n\t\t\t\t\t\t\tAND loccode='" . $_SESSION['Transfer']->StockLocationTo . "'\n\t\t\t\t\t\t\tAND serialno='" . $Item->BundleRef . "'"; $Result = DB_query($SQL, $db, '<br />' . _('Could not determine if the serial item exists')); $SerialItemExistsRow = DB_fetch_row($Result); if ($SerialItemExistsRow[0] == 1) {
$k = 0; //row colour counter $i = 1; while ($myrow = DB_fetch_array($result)) { $QOH = 0; $QOO = 0; switch ($myrow['mbflag']) { case 'A': case 'D': case 'K': $QOH = _('N/A'); $QOO = _('N/A'); break; case 'M': case 'B': $QOHResult = DB_query("SELECT sum(quantity)\n\t\t\t\t\t\t\t\tFROM locstock\n\t\t\t\t\t\t\t\tINNER JOIN locationusers ON locationusers.loccode=locstock.loccode AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1\n\t\t\t\t\t\t\t\tWHERE stockid = '" . DB_escape_string($myrow['stkcode']) . "'", $db); $QOHRow = DB_fetch_row($QOHResult); $QOH = $QOHRow[0]; // Get the QOO due to Purchase orders for all locations. Function defined in SQL_CommonFunctions.inc $QOO = GetQuantityOnOrderDueToPurchaseOrders($myrow['stkcode'], ''); // Get the QOO due to Work Orders for all locations. Function defined in SQL_CommonFunctions.inc $QOO += GetQuantityOnOrderDueToWorkOrders($myrow['stkcode'], ''); break; } if (is_numeric($QOH) and is_numeric($QOO)) { $DaysOfStock = ($QOH + $QOO) / ($myrow['totalinvoiced'] / $_POST['NumberOfDays']); } elseif (is_numeric($QOH)) { $DaysOfStock = $QOH / ($myrow['totalinvoiced'] / $_POST['NumberOfDays']); } elseif (is_numeric($QOO)) { $DaysOfStock = $QOO / ($myrow['totalinvoiced'] / $_POST['NumberOfDays']); } else {
} /*reset the pointer on this resultset ... will need it later */ DB_data_seek($GetStockMoveResult, 0); } else { $Controlled = false; } /*Start an SQL transaction */ $Result = DB_Txn_Begin(); $PeriodNo = GetPeriod(ConvertSQLDate($GRN['deliverydate']), $db); /*Now the SQL to do the update to the PurchOrderDetails */ $SQL = "UPDATE purchorderdetails\n\t\t\tSET quantityrecd = quantityrecd - '" . $QtyToReverse . "',\n\t\t\tcompleted=0\n\t\t\tWHERE purchorderdetails.podetailitem = '" . $GRN['podetailitem'] . "'"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The purchase order detail record could not be updated with the quantity reversed because'); $DbgMsg = _('The following SQL to update the purchase order detail record was used'); $Result = DB_query($SQL, $ErrMsg, $DbgMsg, true); /*Now the purchorder header status in case it was completed - now incomplete - just printed */ $SQL = "UPDATE purchorders\n\t\t\tSET status = 'Printed',\n\t\t\t\tstat_comment = CONCAT('" . Date($_SESSION['DefaultDateFormat']) . ' ' . _('GRN Reversed for') . ' ' . DB_escape_string(stripslashes($GRN['itemdescription'])) . ' ' . _('by') . ' ' . $_SESSION['UsersRealName'] . "<br />', stat_comment )\n\t\t\tWHERE orderno = '" . $GRN['orderno'] . "'"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The purchase order statusand status comment could not be changed because'); $DbgMsg = _('The following SQL to update the purchase order header record was used'); $Result = DB_query($SQL, $ErrMsg, $DbgMsg, true); /*Need to update or delete the existing GRN item */ if ($QtyToReverse == $GRN['qtyrecd']) { //then ok to delete the whole thing /* if this is not deleted then the purchorderdetail line cannot be deleted subsequentely */ $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The GRN record could not be deleted because'); $DbgMsg = _('The following SQL to delete the GRN record was used'); $result = DB_query("DELETE FROM grns WHERE grnno='" . $_GET['GRNNo'] . "'", $ErrMsg, $DbgMsg, true); } else { $SQL = "UPDATE grns\tSET qtyrecd = qtyrecd - " . $QtyToReverse . "\n\t\t\t\tWHERE grns.grnno='" . $_GET['GRNNo'] . "'"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The GRN record could not be updated') . '. ' . _('This reversal of goods received has not been processed because'); $DbgMsg = _('The following SQL to insert the GRN record was used'); $Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
//figure out the SQL required from the inputs available if (!isset($_POST['Status']) or $_POST['Status'] == 'Pending_Authorised') { $StatusCriteria = " AND (purchorders.status='Pending' OR purchorders.status='Authorised' OR purchorders.status='Printed') "; } elseif ($_POST['Status'] == 'Authorised') { $StatusCriteria = " AND (purchorders.status='Authorised' OR purchorders.status='Printed')"; } elseif ($_POST['Status'] == 'Pending') { $StatusCriteria = " AND purchorders.status='Pending' "; } elseif ($_POST['Status'] == 'Rejected') { $StatusCriteria = " AND purchorders.status='Rejected' "; } elseif ($_POST['Status'] == 'Cancelled') { $StatusCriteria = " AND purchorders.status='Cancelled' "; } //$_POST['Status'] == 'Cancelled' //If searching on supplier code if (isset($SelectedSupplier) and $SelectedSupplier != '') { $SupplierSearchString = " AND purchorders.supplierno='" . DB_escape_string($SelectedSupplier) . "' "; } else { $SupplierSearchString = ''; } //If searching on order number if (isset($OrderNumber) and $OrderNumber != '') { $OrderNumberSearchString = " AND purchorders.orderno='" . $OrderNumber . "' "; } else { $OrderNumberSearchString = ''; } //If searching on order number if (isset($SelectedStockItem) and $SelectedStockItem != '') { $StockItemSearchString = " AND purchorderdetails.itemcode='" . $SelectedStockItem . "' "; } else { $StockItemSearchString = ''; }
$DbgMsg = _('The following SQL to insert the serial stock item records was used'); $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); /** end of handle stockserialitems records */ /** now insert the serial stock movement **/ $SQL = "INSERT INTO stockserialmoves (stockmoveno,\n\t\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\t\tserialno,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tmoveqty)\n\t\t\t\t\t\t\t\t\tVALUES ('" . $StkMoveNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $_POST['StockID'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . DB_escape_string($_POST['BatchRef' . $i]) . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $_POST['Qty' . $i] . "')"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The serial stock movement record could not be inserted because'); $DbgMsg = _('The following SQL to insert the serial stock movement records was used'); $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); if ($_SESSION['DefineControlledOnWOEntry'] == 1) { //check how many of the batch/bundle/lot has been received $SQL = "SELECT sum(moveqty) AS totalquantity FROM stockserialmoves\n\t\t\t\t\t\t\t\t\t\tINNER JOIN stockmoves ON stockserialmoves.stockmoveno=stockmoves.stkmoveno\n\t\t\t\t\t\t\t\t\t\tWHERE stockmoves.type=26\n\t\t\t\t\t\t\t\t\t\tAND stockserialmoves.stockid='" . $_POST['StockID'] . "'\n\t\t\t\t\t\t\t\t\t\tAND stockserialmoves.serialno='" . DB_escape_string($_POST['BatchRef' . $i]) . "'"; $BatchTotQtyResult = DB_query($SQL, $db); $BatchTotQtyRow = DB_fetch_array($BatchTotQtyResult); if ($BatchTotQtyRow['totalquantity'] >= $_POST['QtyReqd' . $i]) { //need to delete the item from woserialnos $SQL = "DELETE FROM\twoserialnos\n\t\t\t\t\t\t\t\t\t\tWHERE wo='" . $_POST['WO'] . "'\n\t\t\t\t\t\t\t\t\t\tAND stockid='" . $_POST['StockID'] . "'\n\t\t\t\t\t\t\t\t\t\tAND serialno='" . DB_escape_string($_POST['BatchRef' . $i]) . "'"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The predefined batch/lot/bundle record could not be deleted because'); $DbgMsg = _('The following SQL to delete the predefined work order batch/bundle/lot record was used'); $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); } } } //non blank BundleRef } //end for all of the potential batch/lot fields received } //end of the batch controlled stuff } //end if the woitem received here is a controlled item /* If GLLink_Stock then insert GLTrans to debit the GL Code and credit GRN Suspense account at standard cost*/ if ($_SESSION['CompanyRecord']['gllink_stock'] == 1 and $WORow['stdcost'] * $QuantityReceived != 0) {
function InsertWorkOrder($WorkOrderDetails, $user, $password) { $Errors = array(); $db = db($user, $password); if (gettype($db) == 'integer') { $Errors[0] = NoAuthorisation; return $Errors; } foreach ($WorkOrderDetails as $key => $value) { $WorkOrderDetails[$key] = DB_escape_string($value); } $WorkOrder['wo'] = GetNextTransactionNo(40, $db); $WorkOrderItem['wo'] = $WorkOrder['wo']; if (isset($WorkOrderDetails['loccode'])) { $Errors = VerifyFromStockLocation($WorkOrderDetails['loccode'], sizeof($Errors), $Errors, $db); $WorkOrder['loccode'] = $WorkOrderDetails['loccode']; } if (isset($WorkOrderDetails['requiredby'])) { // $Errors=VerifyRequiredByDate($WorkOrderDetails['requiredby'], sizeof($Errors), $Errors, $db); $WorkOrder['requiredby'] = $WorkOrderDetails['requiredby']; } if (isset($WorkOrderDetails['startdate'])) { // $Errors=VerifyStartDate($WorkOrderDetails['startdate'], sizeof($Errors), $Errors, $db); $WorkOrder['startdate'] = $WorkOrderDetails['startdate']; } if (isset($WorkOrderDetails['costissued'])) { $Errors = VerifyCostIssued($WorkOrderDetails['costissued'], sizeof($Errors), $Errors, $db); $WorkOrder['costissued'] = $WorkOrderDetails['costissued']; } if (isset($WorkOrderDetails['closed'])) { $Errors = VerifyCompleted($WorkOrderDetails['closed'], sizeof($Errors), $Errors); $WorkOrder['closed'] = $WorkOrderDetails['closed']; } if (isset($WorkOrderDetails['stockid'])) { $Errors = VerifyStockCodeExists($WorkOrderDetails['stockid'], sizeof($Errors), $Errors, $db); $WorkOrderItem['stockid'] = $WorkOrderDetails['stockid']; } if (isset($WorkOrderDetails['qtyreqd'])) { $Errors = VerifyQtyReqd($WorkOrderDetails['qtyreqd'], sizeof($Errors), $Errors); $WorkOrderItem['qtyreqd'] = $WorkOrderDetails['qtyreqd']; } if (isset($WorkOrderDetails['qtyrecd'])) { $Errors = VerifyQtyRecd($WorkOrderDetails['qtyrecd'], sizeof($Errors), $Errors); $WorkOrderItem['qtyrecd'] = $WorkOrderDetails['qtyrecd']; } if (isset($WorkOrderDetails['stdcost'])) { $Errors = VerifyStdCost($WorkOrderDetails['stdcost'], sizeof($Errors), $Errors); $WorkOrderItem['stdcost'] = $WorkOrderDetails['stdcost']; } if (isset($WorkOrderDetails['nextlotsnref'])) { $Errors = VerifyLotSerialNumber($WorkOrderDetails['nextlotsnref'], sizeof($Errors), $Errors); $WorkOrderItem['nextlotsnref'] = $WorkOrderDetails['nextlotsnref']; } $WOFieldNames = ''; $WOFieldValues = ''; foreach ($WorkOrder as $key => $value) { $WOFieldNames .= $key . ', '; $WOFieldValues .= '"' . $value . '", '; } $ItemFieldNames = ''; $ItemFieldValues = ''; foreach ($WorkOrderItem as $key => $value) { $ItemFieldNames .= $key . ', '; $ItemFieldValues .= '"' . $value . '", '; } if (sizeof($Errors) == 0) { $wosql = 'INSERT INTO workorders (' . substr($WOFieldNames, 0, -2) . ') ' . 'VALUES (' . substr($WOFieldValues, 0, -2) . ') '; $itemsql = 'INSERT INTO woitems (' . substr($ItemFieldNames, 0, -2) . ') ' . 'VALUES (' . substr($ItemFieldValues, 0, -2) . ') '; $systypessql = 'UPDATE systypes set typeno=' . GetNextTransactionNo(40, $db) . ' where typeid=40'; DB_Txn_Begin($db); $woresult = DB_Query($wosql, $db); $itemresult = DB_Query($itemsql, $db); $systyperesult = DB_Query($systypessql, $db); DB_Txn_Commit($db); if (DB_error_no($db) != 0) { $Errors[0] = DatabaseUpdateFailed; } else { $Errors[0] = 0; $Errors[1] = $WorkOrder['wo']; } } return $Errors; }
$ErrMsg = _('The purchase order detail line could not be deleted because'); $DbgMsg = _('The SQL statement used to delete the purchase order detail record, that failed was'); $result = DB_query($sql, $db, $ErrMsg, $DbgMsg, true); } } else { if ($POLine->PODetailRec == '') { /*When the purchase order line is an existing record the auto-increment * field PODetailRec is given to the session for that POLine * So it will only be a new POLine if PODetailRec is empty */ $sql = "INSERT INTO purchorderdetails ( orderno,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\titemcode,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tdeliverydate,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\titemdescription,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tglcode,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tunitprice,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tquantityord,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tshiptref,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tjobref,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tsuppliersunit,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tsuppliers_partno,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tassetid,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tconversionfactor)\n\t\t\t\t\t\t\t\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['PO' . $identifier]->OrderNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $POLine->StockID . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($POLine->ReqDelDate) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . DB_escape_string($POLine->ItemDescription) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $POLine->GLCode . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $POLine->Price . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $POLine->Quantity . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $POLine->ShiptRef . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $POLine->JobRef . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $POLine->SuppliersUnit . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $POLine->Suppliers_PartNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $POLine->AssetID . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $POLine->ConversionFactor . "')"; } else { if ($POLine->Quantity == $POLine->QtyReceived) { $sql = "UPDATE purchorderdetails SET itemcode='" . $POLine->StockID . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tdeliverydate ='" . FormatDateForSQL($POLine->ReqDelDate) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\titemdescription='" . DB_escape_string($POLine->ItemDescription) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tglcode='" . $POLine->GLCode . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tunitprice='" . $POLine->Price . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tquantityord='" . $POLine->Quantity . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tshiptref='" . $POLine->ShiptRef . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tjobref='" . $POLine->JobRef . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tsuppliersunit='" . $POLine->SuppliersUnit . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tsuppliers_partno='" . DB_escape_string($POLine->Suppliers_PartNo) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tcompleted=1,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tassetid='" . $POLine->AssetID . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tconversionfactor = '" . $POLine->ConversionFactor . "' \n\t\t\t\t\t\t\t\tWHERE podetailitem='" . $POLine->PODetailRec . "'"; } else { $sql = "UPDATE purchorderdetails SET itemcode='" . $POLine->StockID . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tdeliverydate ='" . FormatDateForSQL($POLine->ReqDelDate) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\titemdescription='" . DB_escape_string($POLine->ItemDescription) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tglcode='" . $POLine->GLCode . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tunitprice='" . $POLine->Price . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tquantityord='" . $POLine->Quantity . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tshiptref='" . $POLine->ShiptRef . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tjobref='" . $POLine->JobRef . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tsuppliersunit='" . $POLine->SuppliersUnit . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tsuppliers_partno='" . $POLine->Suppliers_PartNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tassetid='" . $POLine->AssetID . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tconversionfactor = '" . $POLine->ConversionFactor . "'\n\t\t\t\t\t\t\t\tWHERE podetailitem='" . $POLine->PODetailRec . "'"; } } } $ErrMsg = _('One of the purchase order detail records could not be updated because'); $DbgMsg = _('The SQL statement used to update the purchase order detail record that failed was'); $result = DB_query($sql, $db, $ErrMsg, $DbgMsg, true); } /* end of the loop round the detail line items on the order */ echo '<br /><br />'; prnMsg(_('Purchase Order') . ' ' . $_SESSION['PO' . $identifier]->OrderNo . ' ' . _('has been updated'), 'success'); if ($_SESSION['PO' . $identifier]->AllowPrintPO == 1 and ($_SESSION['PO' . $identifier]->Status == 'Authorised' or $_SESSION['PO' . $identifier]->Status == 'Printed')) { echo '<br /><div class="centre"><a target="_blank" href="' . $rootpath . '/PO_PDFPurchOrder.php?OrderNo=' . $_SESSION['PO' . $identifier]->OrderNo . '">' . _('Print Purchase Order') . '</a></div>'; } } /*end of if its a new order or an existing one */
function ModifyStockItem($StockItemDetails, $user, $password) { $Errors = array(); $db = db($user, $password); if (gettype($db) == 'integer') { $Errors[0] = NoAuthorisation; return $Errors; } foreach ($StockItemDetails as $key => $value) { $StockItemDetails[$key] = DB_escape_string($value); } $Errors = VerifyStockCodeExists($StockItemDetails['stockid'], sizeof($Errors), $Errors, $db); if (in_array(StockCodeDoesntExist, $Errors)) { return $Errors; } if (isset($StockItemDetails['description'])) { $Errors = VerifyStockDescription($StockItemDetails['decription'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['longdescription'])) { $Errors = VerifyStockLongDescription($StockItemDetails['longdescription'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['categoryid'])) { $Errors = VerifyStockCategoryExists($StockItemDetails['categoryid'], sizeof($Errors), $Errors, $db); } if (isset($StockItemDetails['units'])) { $Errors = VerifyUnits($StockItemDetails['units'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['mbflag'])) { $Errors = VerifyMBFlag($StockItemDetails['mbflag'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['lastcurcostdate'])) { $Errors = VerifyLastCurCostDate($StockItemDetails['lascurcostdate'], sizeof($Errors), $Errors, $db); } if (isset($StockItemDetails['actualcost'])) { $Errors = VerifyActualCost($StockItemDetails['actualcost'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['lastcost'])) { $Errors = VerifyLastCost($StockItemDetails['lastcost'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['materialcost'])) { $Errors = VerifyMaterialCost($StockItemDetails['materialcost'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['labourcost'])) { $Errors = VerifyLabourCost($StockItemDetails['labourcost'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['overheadcost'])) { $Errors = VerifyOverheadCost($StockItemDetails['overheadcost'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['lowestlevel'])) { $Errors = VerifyLowestLevel($StockItemDetails['lowestlevel'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['discontinued'])) { $Errors = VerifyDiscontinued($StockItemDetails['discontinued'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['controlled'])) { $Errors = VerifyControlled($StockItemDetails['controlled'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['eoq'])) { $Errors = VerifyEOQ($StockItemDetails['eoq'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['volume'])) { $Errors = VerifyVolume($StockItemDetails['volume'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['grossweight'])) { $Errors = VerifyKgs($StockItemDetails['grossweight'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['barcode'])) { $Errors = VerifyBarCode($StockItemDetails['barcode'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['discountcategory'])) { $Errors = VerifyDiscountCategory($StockItemDetails['discountcategory'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['taxcatid'])) { $Errors = VerifyTaxCatExists($StockItemDetails['taxcatid'], sizeof($Errors), $Errors, $db); } if (isset($StockItemDetails['serialised'])) { $Errors = VerifySerialised($StockItemDetails['serialised'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['appendfile'])) { $Errors = VerifyApendFile($StockItemDetails['apendfile'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['perishable'])) { $Errors = VerifyPerishable($StockItemDetails['perishable'], sizeof($Errors), $Errors); } if (isset($StockItemDetails['decimalplaces'])) { $Errors = VerifyDecimalPlaces($StockItemDetails['decimalplaces'], sizeof($Errors), $Errors); } $sql = 'UPDATE stockmaster SET '; foreach ($StockItemDetails as $key => $value) { $sql .= $key . '="' . $value . '", '; } $sql = mb_substr($sql, 0, -2) . " WHERE stockid='" . $StockItemDetails['stockid'] . "'"; if (sizeof($Errors) == 0) { $result = DB_Query($sql, $db); echo DB_error_no($db); if (DB_error_no($db) != 0) { $Errors[0] = DatabaseUpdateFailed; } else { $Errors[0] = 0; } } return $Errors; }
SET debtorno = ' . "'" . $_SESSION['Items' . $identifier]->DebtorNo . "'" . ', branchcode = ' . "'" . $_SESSION['Items' . $identifier]->Branch . "'" . ', customerref = ' . "'" . DB_escape_string($_SESSION['Items' . $identifier]->CustRef) . "'" . ', comments = ' . "'" . DB_escape_string($_SESSION['Items' . $identifier]->Comments) . "'" . ', ordertype = ' . "'" . $_SESSION['Items' . $identifier]->DefaultSalesType . "'" . ', shipvia = ' . $_POST['ShipVia'] . ', deliverydate = ' . "'" . FormatDateForSQL(DB_escape_string($_SESSION['Items' . $identifier]->DeliveryDate)) . "'" . ', quotedate = ' . "'" . FormatDateForSQL(DB_escape_string($_SESSION['Items' . $identifier]->QuoteDate)) . "'" . ', confirmeddate = ' . "'" . FormatDateForSQL(DB_escape_string($_SESSION['Items' . $identifier]->ConfirmedDate)) . "'" . ', deliverto = ' . "'" . DB_escape_string($_SESSION['Items' . $identifier]->DeliverTo) . "'" . ', deladd1 = ' . "'" . DB_escape_string($_SESSION['Items' . $identifier]->DelAdd1) . "'" . ', deladd2 = ' . "'" . DB_escape_string($_SESSION['Items' . $identifier]->DelAdd2) . "'" . ', deladd3 = ' . "'" . DB_escape_string($_SESSION['Items' . $identifier]->DelAdd3) . "'" . ', deladd4 = ' . "'" . DB_escape_string($_SESSION['Items' . $identifier]->DelAdd4) . "'" . ', deladd5 = ' . "'" . DB_escape_string($_SESSION['Items' . $identifier]->DelAdd5) . "'" . ', deladd6 = ' . "'" . DB_escape_string($_SESSION['Items' . $identifier]->DelAdd6) . "'" . ', contactphone = ' . "'" . $_SESSION['Items' . $identifier]->PhoneNo . "'" . ', contactemail = ' . "'" . $_SESSION['Items' . $identifier]->Email . "'" . ', freightcost = ' . $_SESSION['Items' . $identifier]->FreightCost . ', fromstkloc = ' . "'" . $_SESSION['Items' . $identifier]->Location . "'" . ', deliverydate = ' . "'" . $DelDate . "'" . ', quotedate = ' . "'" . $QuotDate . "'" . ', confirmeddate = ' . "'" . $ConfDate . "'" . ', printedpackingslip = ' . $_POST['ReprintPackingSlip'] . ', quotation = ' . $_SESSION['Items' . $identifier]->Quotation . ', deliverblind = ' . $_SESSION['Items' . $identifier]->DeliverBlind . ' WHERE salesorders.orderno=' . $_SESSION['ExistingOrder']; $DbgMsg = _('The SQL that was used to update the order and failed was'); $ErrMsg = _('The order cannot be updated because'); $InsertQryResult = DB_query($HeaderSQL, $db, $ErrMsg, $DbgMsg, true); foreach ($_SESSION['Items' . $identifier]->LineItems as $StockItem) {
} //end of the batch controlled stuff } //end if the woitem received here is a controlled item /* If GLLink_Stock then insert GLTrans to debit the GL Code and credit GRN Suspense account at standard cost*/ if ($_SESSION['CompanyRecord']['gllink_stock'] == 1 and $WORow['stdcost'] * $QuantityReceived != 0) { /*GL integration with stock is activated so need the GL journals to make it so */ /*first the debit the finished stock of the item received from the WO the appropriate account was already retrieved into the $StockGLCode variable as the Processing code is kicked off it is retrieved from the stock category record of the item by a function in SQL_CommonFunctions.inc*/ $SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\tVALUES (26,\n\t\t\t\t\t\t\t\t'" . $WOReceiptNo . "',\n\t\t\t\t\t\t\t\t'" . Date('Y-m-d') . "',\n\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t'" . $StockGLCode['stockact'] . "',\n\t\t\t\t\t\t\t\t'" . $_POST['WO'] . " " . $_POST['StockID'] . " - " . DB_escape_string($WORow['description']) . ' x ' . $QuantityReceived . " @ " . locale_number_format($WORow['stdcost'], $_SESSION['CompanyRecord']['decimalplaces']) . "',\n\t\t\t\t\t\t\t\t'" . $WORow['stdcost'] * $QuantityReceived . "')"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The receipt of work order finished stock GL posting could not be inserted because'); $DbgMsg = _('The following SQL to insert the work order receipt of finished items GLTrans record was used'); $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); /*now the credit WIP entry*/ $SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\tVALUES (26,\n\t\t\t\t\t\t\t\t'" . $WOReceiptNo . "',\n\t\t\t\t\t\t\t\t'" . Date('Y-m-d') . "',\n\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t'" . $StockGLCode['wipact'] . "',\n\t\t\t\t\t\t\t\t'" . $_POST['WO'] . " " . $_POST['StockID'] . " - " . DB_escape_string($WORow['description']) . ' x ' . $QuantityReceived . " @ " . locale_number_format($WORow['stdcost'], $_SESSION['CompanyRecord']['decimalplaces']) . "',\n\t\t\t\t\t\t\t\t'" . -($WORow['stdcost'] * $QuantityReceived) . "')"; $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The WIP credit on receipt of finished items from a work order GL posting could not be inserted because'); $DbgMsg = _('The following SQL to insert the WIP GLTrans record was used'); $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true); } /* end of if GL and stock integrated and standard cost !=0 */ if (!isset($LastRef)) { $LastRef = ''; } //update the wo with the new qtyrecd $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Could not update the work order item record with the total quantity received because'); $DbgMsg = _('The following SQL was used to update the work order'); $UpdateWOResult = DB_query("UPDATE woitems\n\t\t\t\t\t\t\t\t\tSET qtyrecd=qtyrecd+" . $QuantityReceived . ",\n\t\t\t\t\t\t\t\t\t\tnextlotsnref='" . $LastRef . "'\n\t\t\t\t\t\t\t\t\tWHERE wo='" . $_POST['WO'] . "'\n\t\t\t\t\t\t\t\t\tAND stockid='" . $_POST['StockID'] . "'", $db, $ErrMsg, $DbgMsg, true); $Result = DB_Txn_Commit($db); prnMsg(_('The receipt of') . ' ' . $QuantityReceived . ' ' . $WORow['units'] . ' ' . _('of') . ' ' . $_POST['StockID'] . ' - ' . $WORow['description'] . ' ' . _('against work order') . ' ' . $_POST['WO'] . ' ' . _('has been processed'), 'info'); echo '<a href="' . $RootPath . '/SelectWorkOrder.php">' . _('Select a different work order for receiving finished stock against') . '</a>';
function ModifySalesOrderLine($OrderLine, $user, $password) { $Errors = array(); $db = db($user, $password); if (gettype($db) == 'integer') { $Errors[0] = NoAuthorisation; return $Errors; } foreach ($OrderLine as $key => $value) { $OrderLine[$key] = DB_escape_string($value); } $Errors = VerifyOrderHeaderExists($OrderLine['orderno'], sizeof($Errors), $Errors, $db); $Errors = VerifyStockCodeExists($OrderLine['stkcode'], sizeof($Errors), $Errors, $db); if (isset($OrderLine['unitprice'])) { $Errors = VerifyUnitPrice($OrderLine['unitprice'], sizeof($Errors), $Errors); } if (isset($OrderLine['quantity'])) { $Errors = VerifyQuantity($OrderLine['quantity'], sizeof($Errors), $Errors); } if (isset($OrderLine['discountpercent'])) { //$OrderLine['discountpercent'] = $OrderLine['discountpercent'] * 100; $Errors = VerifyDiscountPercent($OrderLine['discountpercent'], sizeof($Errors), $Errors); $OrderLine['discountpercent'] = $OrderLine['discountpercent'] / 100; } if (isset($OrderLine['narrative'])) { $Errors = VerifyNarrative($OrderLine['narrative'], sizeof($Errors), $Errors); } if (isset($OrderLine['itemdue'])) { $Errors = VerifyItemDueDate($OrderLine['itemdue'], sizeof($Errors), $Errors); } if (isset($OrderLine['poline'])) { $Errors = VerifyPOLine($OrderLine['poline'], sizeof($Errors), $Errors); } $sql = 'UPDATE salesorderdetails SET '; foreach ($OrderLine as $key => $value) { if ($key == 'actualdispatchdate') { $value = FormatDateWithTimeForSQL($value); } elseif ($key == 'itemdue') { $value = FormatDateForSQL($value); } $sql .= $key . '="' . $value . '", '; } //$sql = substr($sql,0,-2).' WHERE orderno="'.$OrderLine['orderno'].'" and // " orderlineno='.$OrderLine['orderlineno']; $sql = substr($sql, 0, -2) . ' WHERE orderno="' . $OrderLine['orderno'] . '" and stkcode="' . $OrderLine['stkcode'] . '"'; //echo $sql; //exit; if (sizeof($Errors) == 0) { $result = api_DB_Query($sql, $db); echo DB_error_no($db); if (DB_error_no($db) != 0) { $Errors[0] = DatabaseUpdateFailed; } else { $Errors[0] = 0; } } return $Errors; }
function ModifySupplier($SupplierDetails, $user, $password) { $Errors = array(); $db = db($user, $password); if (gettype($db) == 'integer') { $Errors[0] = NoAuthorisation; return $Errors; } foreach ($SupplierDetails as $key => $value) { $SupplierDetails[$key] = DB_escape_string($value); } $Errors = VerifySupplierNoExists($SupplierDetails['supplierid'], sizeof($Errors), $Errors, $db); $Errors = VerifySupplierName($SupplierDetails['suppname'], sizeof($Errors), $Errors); if (isset($SupplierDetails['address1'])) { $Errors = VerifyAddressLine($SupplierDetails['address1'], 40, sizeof($Errors), $Errors); } if (isset($SupplierDetails['address2'])) { $Errors = VerifyAddressLine($SupplierDetails['address2'], 40, sizeof($Errors), $Errors); } if (isset($SupplierDetails['address3'])) { $Errors = VerifyAddressLine($SupplierDetails['address3'], 40, sizeof($Errors), $Errors); } if (isset($SupplierDetails['address4'])) { $Errors = VerifyAddressLine($SupplierDetails['address4'], 50, sizeof($Errors), $Errors); } if (isset($SupplierDetails['address5'])) { $Errors = VerifyAddressLine($SupplierDetails['address5'], 20, sizeof($Errors), $Errors); } if (isset($SupplierDetails['address6'])) { $Errors = VerifyAddressLine($SupplierDetails['address6'], 15, sizeof($Errors), $Errors); } if (isset($SupplierDetails['lat'])) { $Errors = VerifyLatitude($SupplierDetails['lat'], sizeof($Errors), $Errors); } if (isset($SupplierDetails['lng'])) { $Errors = VerifyLongitude($SupplierDetails['lng'], sizeof($Errors), $Errors); } if (isset($SupplierDetails['currcode'])) { $Errors = VerifyCurrencyCode($SupplierDetails['currcode'], sizeof($Errors), $Errors, $db); } if (isset($SupplierDetails['suppliersince'])) { $Errors = VerifySupplierSince($SupplierDetails['suppliersince'], sizeof($Errors), $Errors, $db); } if (isset($SupplierDetails['paymentterms'])) { $Errors = VerifyPaymentTerms($SupplierDetails['paymentterms'], sizeof($Errors), $Errors, $db); } if (isset($SupplierDetails['lastpaid'])) { $Errors = VerifyLastPaid($SupplierDetails['lastpaid'], sizeof($Errors), $Errors); } if (isset($SupplierDetails['lastpaiddate'])) { $Errors = VerifyLastPaidDate($SupplierDetails['lastpaiddate'], sizeof($Errors), $Errors); } if (isset($SupplierDetails['bankact'])) { $Errors = VerifyBankAccount($SupplierDetails['bankact'], sizeof($Errors), $Errors); } if (isset($SupplierDetails['bankref'])) { $Errors = VerifyBankRef($SupplierDetails['bankref'], sizeof($Errors), $Errors); } if (isset($SupplierDetails['bankpartics'])) { $Errors = VerifyBankPartics($SupplierDetails['bankpartics'], sizeof($Errors), $Errors); } if (isset($SupplierDetails['remittance'])) { $Errors = VerifyRemittance($SupplierDetails['remittance'], sizeof($Errors), $Errors); } if (isset($SupplierDetails['taxgroupid'])) { $Errors = VerifyTaxGroupId($SupplierDetails['taxgroupid'], sizeof($Errors), $Errors, $db); } if (isset($SupplierDetails['factorcompanyid'])) { $Errors = VerifyFactorCompany($SupplierDetails['factorcompanyid'], sizeof($Errors), $Errors, $db); } if (isset($CustomerDetails['taxref'])) { $Errors = VerifyTaxRef($CustomerDetails['taxref'], sizeof($Errors), $Errors); } $sql = 'UPDATE suppliers SET '; foreach ($SupplierDetails as $key => $value) { $sql .= $key . '="' . $value . '", '; } $sql = mb_substr($sql, 0, -2) . " WHERE supplierid='" . $SupplierDetails['supplierid'] . "'"; if (sizeof($Errors) == 0) { $result = DB_Query($sql, $db); echo DB_error_no($db); if (DB_error_no($db) != 0) { $Errors[0] = DatabaseUpdateFailed; } else { $Errors[0] = 0; } } return $Errors; }
} } /* end of its an assembly */ // Insert stock movements - with unit cost $LocalCurrencyPrice = $OrderLine->Price / $_SESSION['CurrencyRate']; if (empty($OrderLine->StandardCost)) { $OrderLine->StandardCost = 0; } if ($MBFlag == 'B' or $MBFlag == 'M') { $SQL = "INSERT INTO stockmoves (\r\n\t\t\t\t\t\tstockid,\r\n\t\t\t\t\t\ttype,\r\n\t\t\t\t\t\ttransno,\r\n\t\t\t\t\t\tloccode,\r\n\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\tdebtorno,\r\n\t\t\t\t\t\tbranchcode,\r\n\t\t\t\t\t\tprice,\r\n\t\t\t\t\t\tprd,\r\n\t\t\t\t\t\treference,\r\n\t\t\t\t\t\tqty,\r\n\t\t\t\t\t\tdiscountpercent,\r\n\t\t\t\t\t\tstandardcost,\r\n\t\t\t\t\t\tnewqoh,\r\n\t\t\t\t\t\tnarrative )\r\n\t\t\t\t\tVALUES ('" . $OrderLine->StockID . "',\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'" . $_SESSION['Items']->Location . "',\r\n\t\t\t\t\t\t'" . $DefaultDispatchDate . "',\r\n\t\t\t\t\t\t'" . $_SESSION['Items']->DebtorNo . "',\r\n\t\t\t\t\t\t'" . $_SESSION['Items']->Branch . "',\r\n\t\t\t\t\t\t" . $LocalCurrencyPrice . ",\r\n\t\t\t\t\t\t" . $PeriodNo . ",\r\n\t\t\t\t\t\t'" . $_SESSION['ProcessingOrder'] . "',\r\n\t\t\t\t\t\t" . -$OrderLine->QtyDispatched . ",\r\n\t\t\t\t\t\t" . $OrderLine->DiscountPercent . ",\r\n\t\t\t\t\t\t" . $OrderLine->StandardCost . ",\r\n\t\t\t\t\t\t" . ($QtyOnHandPrior - $OrderLine->QtyDispatched) . ",\r\n\t\t\t\t\t\t'" . DB_escape_string($OrderLine->Narrative) . "' )"; } else { // its an assembly or dummy and assemblies/dummies always have nil stock (by definition they are made up at the time of dispatch so new qty on hand will be nil if (empty($OrderLine->StandardCost)) { $OrderLine->StandardCost = 0; } $SQL = "INSERT INTO stockmoves (\r\n\t\t\t\t\t\tstockid,\r\n\t\t\t\t\t\ttype,\r\n\t\t\t\t\t\ttransno,\r\n\t\t\t\t\t\tloccode,\r\n\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\tdebtorno,\r\n\t\t\t\t\t\tbranchcode,\r\n\t\t\t\t\t\tprice,\r\n\t\t\t\t\t\tprd,\r\n\t\t\t\t\t\treference,\r\n\t\t\t\t\t\tqty,\r\n\t\t\t\t\t\tdiscountpercent,\r\n\t\t\t\t\t\tstandardcost,\r\n\t\t\t\t\t\tnarrative )\r\n\t\t\t\t\tVALUES ('" . $OrderLine->StockID . "',\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'" . $_SESSION['Items']->Location . "',\r\n\t\t\t\t\t\t'" . $DefaultDispatchDate . "',\r\n\t\t\t\t\t\t'" . $_SESSION['Items']->DebtorNo . "',\r\n\t\t\t\t\t\t'" . $_SESSION['Items']->Branch . "',\r\n\t\t\t\t\t\t" . $LocalCurrencyPrice . ",\r\n\t\t\t\t\t\t" . $PeriodNo . ",\r\n\t\t\t\t\t\t'" . $_SESSION['ProcessingOrder'] . "',\r\n\t\t\t\t\t\t" . -$OrderLine->QtyDispatched . ",\r\n\t\t\t\t\t\t" . $OrderLine->DiscountPercent . ",\r\n\t\t\t\t\t\t" . $OrderLine->StandardCost . ",\r\n\t\t\t\t\t\t'" . DB_escape_string($OrderLine->Narrative) . "')"; } $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Stock movement records 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); /*Get the ID of the StockMove... */ $StkMoveNo = DB_Last_Insert_ID($db, 'stockmoves', 'stkmoveno'); /*Insert the taxes that applied to this line */ foreach ($OrderLine->Taxes as $Tax) { $SQL = 'INSERT INTO stockmovestaxes (stkmoveno, taxauthid, taxrate, taxcalculationorder, taxontax) VALUES (' . $StkMoveNo . ', ' . $Tax->TaxAuthID . ',
$AccountTo = $myrow['glaccountpcash']; $TagTo = 0; } else { $type = 1; $Amount = -$Amount; $AccountFrom = $myrow['glaccountpcash']; $SQLAccExp = "SELECT glaccount,\n\t\t\t\t\t\t\t\t\ttag\n\t\t\t\t\t\t\t\tFROM pcexpenses\n\t\t\t\t\t\t\t\tWHERE codeexpense = '" . $myrow['codeexpense'] . "'"; $ResultAccExp = DB_query($SQLAccExp, $db); $myrowAccExp = DB_fetch_array($ResultAccExp); $AccountTo = $myrowAccExp['glaccount']; $TagTo = $myrowAccExp['tag']; } //get typeno $typeno = GetNextTransNo($type, $db); //build narrative $Narrative = _('PettyCash') . ' - ' . $myrow['tabcode'] . ' - ' . $myrow['codeexpense'] . ' - ' . DB_escape_string($myrow['notes']) . ' - ' . $myrow['receipt']; //insert to gltrans DB_Txn_Begin($db); $sqlFrom = "INSERT INTO `gltrans` (`counterindex`,\n\t\t\t\t\t\t\t\t\t\t\t`type`,\n\t\t\t\t\t\t\t\t\t\t\t`typeno`,\n\t\t\t\t\t\t\t\t\t\t\t`chequeno`,\n\t\t\t\t\t\t\t\t\t\t\t`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`account`,\n\t\t\t\t\t\t\t\t\t\t\t`narrative`,\n\t\t\t\t\t\t\t\t\t\t\t`amount`,\n\t\t\t\t\t\t\t\t\t\t\t`posted`,\n\t\t\t\t\t\t\t\t\t\t\t`jobref`,\n\t\t\t\t\t\t\t\t\t\t\t`tag`)\n\t\t\t\t\t\t\t\t\tVALUES (NULL,\n\t\t\t\t\t\t\t\t\t\t\t'" . $type . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $typeno . "',\n\t\t\t\t\t\t\t\t\t\t\t0,\n\t\t\t\t\t\t\t\t\t\t\t'" . $myrow['date'] . "',\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'" . $AccountFrom . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $Narrative . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . -$Amount . "',\n\t\t\t\t\t\t\t\t\t\t\t0,\n\t\t\t\t\t\t\t\t\t\t\t'',\n\t\t\t\t\t\t\t\t\t\t\t'" . $TagTo . "')"; $ResultFrom = DB_Query($sqlFrom, $db, '', '', true); $sqlTo = "INSERT INTO `gltrans` (`counterindex`,\n\t\t\t\t\t\t\t\t\t\t`type`,\n\t\t\t\t\t\t\t\t\t\t`typeno`,\n\t\t\t\t\t\t\t\t\t\t`chequeno`,\n\t\t\t\t\t\t\t\t\t\t`trandate`,\n\t\t\t\t\t\t\t\t\t\t`periodno`,\n\t\t\t\t\t\t\t\t\t\t`account`,\n\t\t\t\t\t\t\t\t\t\t`narrative`,\n\t\t\t\t\t\t\t\t\t\t`amount`,\n\t\t\t\t\t\t\t\t\t\t`posted`,\n\t\t\t\t\t\t\t\t\t\t`jobref`,\n\t\t\t\t\t\t\t\t\t\t`tag`)\n\t\t\t\t\t\t\t\tVALUES (NULL,\n\t\t\t\t\t\t\t\t\t\t'" . $type . "',\n\t\t\t\t\t\t\t\t\t\t'" . $typeno . "',\n\t\t\t\t\t\t\t\t\t\t0,\n\t\t\t\t\t\t\t\t\t\t'" . $myrow['date'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $AccountTo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Narrative . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Amount . "',\n\t\t\t\t\t\t\t\t\t\t0,\n\t\t\t\t\t\t\t\t\t\t'',\n\t\t\t\t\t\t\t\t\t\t'" . $TagTo . "')"; $ResultTo = DB_Query($sqlTo, $db, '', '', true); if ($myrow['codeexpense'] == 'ASSIGNCASH') { // if it's a cash assignation we need to updated banktrans table as well. $ReceiptTransNo = GetNextTransNo(2, $db); $SQLBank = "INSERT INTO banktrans (transno,\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\tbankact,\n\t\t\t\t\t\t\t\t\t\t\t\tref,\n\t\t\t\t\t\t\t\t\t\t\t\texrate,\n\t\t\t\t\t\t\t\t\t\t\t\tfunctionalexrate,\n\t\t\t\t\t\t\t\t\t\t\t\ttransdate,\n\t\t\t\t\t\t\t\t\t\t\t\tbanktranstype,\n\t\t\t\t\t\t\t\t\t\t\t\tamount,\n\t\t\t\t\t\t\t\t\t\t\t\tcurrcode)\n\t\t\t\t\t\t\t\t\t\tVALUES ('" . $ReceiptTransNo . "',\n\t\t\t\t\t\t\t\t\t\t\t1,\n\t\t\t\t\t\t\t\t\t\t\t'" . $AccountFrom . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $Narrative . "',\n\t\t\t\t\t\t\t\t\t\t\t1,\n\t\t\t\t\t\t\t\t\t\t\t'" . $myrow['rate'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $myrow['date'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'Cash',\n\t\t\t\t\t\t\t\t\t\t\t'" . -$myrow['amount'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $myrow['currency'] . "'\n\t\t\t\t\t\t\t\t\t\t)"; $ErrMsg = _('Cannot insert a bank transaction because'); $DbgMsg = _('Cannot insert a bank transaction with the SQL'); $resultBank = DB_query($SQLBank, $db, $ErrMsg, $DbgMsg, true); } $sql = "UPDATE pcashdetails\n\t\t\t\t\tSET authorized = '" . Date('Y-m-d') . "',\n\t\t\t\t\tposted = 1\n\t\t\t\t\tWHERE counterindex = '" . $myrow['counterindex'] . "'";