chinwan Posted July 26, 2009 Share Posted July 26, 2009 I know that this is from a php script, but the developer is not that helpful, so I hope someone here may be able to help me. And I hope that this file is enough. The following php file is from a e-commerce script. This part of the script handles the paying of "vendors". The script is supposed to add up all purchases from that vendor since the last payment, subtract "profitmargin" add any previous remaing "paymentforwardbalance" and give the total so that you can make a payment. Upon making the payment, it then subtract the amount you paid from the balance and then save that number in the "paymentforwardbalance". However, for some reason, if either the "paymentforwardbalance" or the total of purchases is $1000 or more, it messes up it calculations and save a weird number back into the "paymentforwardbalance" section. Any 3 digit number is not a problem, but four digits are. It is not a problem when the "paymentforwardbalance" and the totalpurchases add upto be be over 1000, only if one of them is individually. When it pulls in the numbers to display it, it does not have a problem, only when doing the math. Just below I have what I think is the pertinent section of code, however, in case not, below that I have the entire file. Thank You /** * Actually commit a vendor payment to the database. * * @param array An array of details about the vendor payment. * @return int The ID of the new vendor payment that was just created. */ private function CommitVendorPayment($data) { if(!isset($data['paymentdeducted'])) { $data['paymentdeducted'] = 0; } if(!isset($data['paymentcomments'])) { $data['paymentcomments'] = ''; } $paymentDetails = $this->CalculateOutstandingVendorBalance($data['paymentvendorid']); $balanceForward = number_format($paymentDetails['balanceForward'], GetConfig('DecimalPlaces')); $totalOrders = number_format($paymentDetails['totalOrders'], GetConfig('DecimalPlaces')); $profitMargin = number_format($paymentDetails['profitMargin'], GetConfig('DecimalPlaces')); $forwardBalance = $balanceForward + $totalOrders - $profitMargin; if($data['paymentdeducted']) { $forwardBalance -= $data['paymentamount']; } $data['paymentamount'] = CNumeric($data['paymentamount']); $newPayment = array( 'paymentfrom' => $data['paymentfrom'], 'paymentto' => $data['paymentto'], 'paymentvendorid' => $data['paymentvendorid'], 'paymentamount' => $data['paymentamount'], 'paymentforwardbalance' => $forwardBalance, 'paymentmethod' => $data['paymentmethod'], 'paymentdate' => time(), 'paymentdeducted' => $data['paymentdeducted'], 'paymentcomments' => $data['paymentcomments'] ); $paymentId = $GLOBALS['ISC_CLASS_DB']->InsertQuery('vendor_payments', $newPayment); Here is the entire file: <?php /** * Vendor Payment Manager. */ class ISC_ADMIN_VENDOR_PAYMENTS { /** * The constructor. */ public function __construct() { $GLOBALS['ISC_CLASS_ADMIN_ENGINE']->LoadLangFile('vendor.payments'); } /** * Handle the incoming action. * * @param string The action to perform. */ public function HandleToDo($do) { if(!$GLOBALS['ISC_CLASS_ADMIN_AUTH']->HasPermission(AUTH_Manage_Vendors)) { exit; } if(!gzte11(ISC_HUGEPRINT)) { ob_end_clean(); header('Location: index.php'); exit; } // Set up some generic breadcrumb entries as these will be used on most pages $GLOBALS['BreadcrumEntries'] = array( GetLang('Home') => 'index.php', GetLang('VendorPayments') => 'index.php?ToDo=viewVendorPayments' ); switch(strtolower($do)) { case 'addvendorpayment': $this->AddVendorPayment(); break; case 'savenewvendorpayment': $this->SaveNewVendorPayment(); break; case 'exportvendorpayments': $this->ExportVendorPayments(); break; case 'deletevendorpayments': $this->DeleteVendorPayments(); default: $this->ManageVendorPayments(); break; } } /** * Delete one or more selected vendor payments from the database. */ private function DeleteVendorPayments() { if(!isset($_POST['payments']) || !is_array($_POST['payments'])) { ob_end_clean(); header('Location: index.php?ToDo=viewPayments'); exit; } // Now it's safe to delete the payments $paymentIds = implode(',', array_map('intval', $_POST['payments'])); $GLOBALS['ISC_CLASS_DB']->DeleteQuery('vendor_payments', "WHERE paymentid IN (".$paymentIds.")"); if(!$GLOBALS['ISC_CLASS_DB']->GetErrorMsg()) { $GLOBALS['ISC_CLASS_LOG']->LogAdminAction(count($_POST['payments'])); FlashMessage(GetLang('VendorPaymentsDeleted'), MSG_SUCCESS, 'index.php?ToDo=viewVendorPayments'); } // If there was an error, redirect and show the error else { FlashMessage($GLOBALS['ISC_CLASS_DB']->GetErrorMsg(), MSG_ERROR, 'index.php?ToDo=viewVendorPayments'); } } /** * Export vendor payments (for all vendors or a specific vendor) to a CSV or XML file. */ private function ExportVendorPayments() { // Validate the sort order if(isset($_REQUEST['sortOrder']) && $_REQUEST['sortOrder'] == 'asc') { $sortOrder = 'asc'; } else { $sortOrder = 'desc'; } // Which fields can we sort by? $validSortFields = array( 'paymentid', 'paymentfrom', 'vendorname', 'paymentamount', 'paymentmethod', 'paymentdate' ); if(isset($_REQUEST['sortField']) && in_array($_REQUEST['sortField'], $validSortFields)) { $sortField = $_REQUEST['sortField']; SaveDefaultSortField('ManageVendorPayments', $_REQUEST['sortField'], $sortOrder); } else { list($sortField, $sortOrder) = GetDefaultSortField('ManageVendorPayments', 'paymentid', $sortOrder); } ob_end_clean(); // Grab the queries we'll be executing $paymentQueries = $this->BuildVendorPaymentSearchQuery(0, $sortField, $sortOrder, false); $numPayments = $GLOBALS['ISC_CLASS_DB']->FetchOne($paymentQueries['countQuery']); if(!$numPayments) { header('Location: index.php?ToDo=viewVendorPayments'); exit; } // Set up the list of columns $columns = array( 'paymentid' => 'PAYMENT ID', 'paymentfrom' => 'PAYMENT FROM', 'paymentto' => 'PAYMENT TO', 'paymentvendorid' => 'PAYMENT VENDOR ID', 'vendorname' => 'PAYMENT VENDOR NAME', 'paymentamount' => 'PAYMENT AMOUNT', 'paymentforwardbalance' => 'OUTSTANDING BALANCE', 'paymentdate' => 'PAYMENT DATE', 'paymentmethod' => 'PAYMENT METHOD', 'paymentcomments' => 'PAYMENT COMMENTS' ); if(!isset($_GET['format']) || $_GET['format'] == "csv") { $ext = 'csv'; } else { $ext = 'xml'; } $GLOBALS['ISC_CLASS_LOG']->LogAdminAction(isc_strtoupper($_REQUEST['format'])); header("Content-Type: application/force-download"); header("Content-Type: application/octet-stream"); header("Content-Type: application/download"); header("Content-Disposition: attachment; filename=\"payments-".isc_date("Y-m-d").".".$ext."\";"); if($ext == 'csv') { $row = ''; foreach($columns as $field) { $row .= EXPORT_FIELD_ENCLOSURE.$field.EXPORT_FIELD_ENCLOSURE.EXPORT_FIELD_SEPARATOR; } echo rtrim($row, EXPORT_FIELD_SEPARATOR); echo EXPORT_RECORD_SEPARATOR; } else { echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n"; echo "<payments>\n"; } // Export the payments $result = $GLOBALS['ISC_CLASS_DB']->Query($paymentQueries['query']); while($payment = $GLOBALS['ISC_CLASS_DB']->Fetch($result)) { // If CSV export, handle that now if($ext == 'csv') { $row = ''; foreach($columns as $k => $v) { switch($k) { case 'paymentfrom': case 'paymentto': case 'paymentdate': $value = isc_date(GetConfig('ExportDateFormat'), $payment[$k]); break; case 'paymentamount': case 'paymentforwardbalance': $value = FormatPrice($payment[$k]); default: $value = $payment[$k]; } $value = str_replace(EXPORT_FIELD_ENCLOSURE, EXPORT_FIELD_ENCLOSURE . EXPORT_FIELD_ENCLOSURE, $value); $row .= EXPORT_FIELD_ENCLOSURE.$value.EXPORT_FIELD_ENCLOSURE.EXPORT_FIELD_SEPARATOR; } echo rtrim($row, EXPORT_FIELD_SEPARATOR); echo EXPORT_RECORD_SEPARATOR; @flush(); } // XML is easy! else { echo "\t<payment paymentid=\"".$payment['paymentid']."\">\n"; foreach($columns as $k => $v) { switch($k) { case 'paymentfrom': case 'paymentto': case 'paymentdate': $value = isc_date(GetConfig('ExportDateFormat'), $payment[$k]); break; case 'paymentamount': case 'paymentforwardbalance': $value = FormatPrice($payment[$k]); default: $value = $payment[$k]; } echo "\t\t<".$k."><![CDATA[".$value."]]></".$k.">\n"; flush(); } echo "\t</payment>\n"; } } if($ext == 'xml') { echo "</payments>"; } exit; } /** * Log a payment to a vendor. */ private function AddVendorPayment() { $GLOBALS['VendorList'] = $this->BuildVendorList(); if (GetConfig('CurrencyLocation') == 'right') { $GLOBALS['RightCurrencyToken'] = GetConfig('CurrencyToken'); } else { $GLOBALS['LeftCurrencyToken'] = GetConfig('CurrencyToken'); } // Grab a unique list of all of the previously used payment methods $paymentMethods = array(); $query = " SELECT DISTINCT paymentmethod FROM [|PREFIX|]vendor_payments ORDER BY paymentmethod "; $result = $GLOBALS['ISC_CLASS_DB']->Query($query); while($method = $GLOBALS['ISC_CLASS_DB']->Fetch($result)) { if(isset($paymentMethods[strtolower($method['paymentmethod'])])) { continue; } $paymentMethods[strtolower($method['paymentmethod'])] = $method['paymentmethod']; } $GLOBALS['PaymentMethodList'] = ''; foreach($paymentMethods as $method) { $GLOBALS['PaymentMethodList'] .= "'".addslashes($method)."', "; } $GLOBALS['PaymentMethodList'] = rtrim($GLOBALS['PaymentMethodList'], ', '); $GLOBALS['BreadcrumEntries'][GetLang('AddVendorPayment')] = ''; $GLOBALS['ISC_CLASS_ADMIN_ENGINE']->PrintHeader(); $GLOBALS['ISC_CLASS_TEMPLATE']->SetTemplate('vendorpayments.form'); $GLOBALS['ISC_CLASS_TEMPLATE']->ParseTemplate(); $GLOBALS['ISC_CLASS_ADMIN_ENGINE']->PrintFooter(); } /** * Actually save the new payment for the vendor in the database. */ private function SaveNewVendorPayment() { $message = ''; if(isset($_POST['paymentvendorid'])) { $paymentDetails = $this->CalculateOutstandingVendorBalance($_POST['paymentvendorid']); $_POST['paymentfrom'] = $paymentDetails['lastPaymentDate']; $_POST['paymentto'] = time(); } if(!$this->ValidateVendorPayment($_POST, $message)) { FlashMessage($message, MSG_ERROR); $this->AddVendorPayment(); return; } if(!$this->CommitVendorPayment($_POST)) { $error = $GLOBALS['ISC_CLASS_DB']->GetErrorMsg(); FlashMessage(GetLang('ProblemSavingPayment').$error, MSG_ERROR); $this->AddVendorPayment(); return; } else { // Log this action $GLOBALS['ISC_CLASS_LOG']->LogAdminAction($_POST['paymentamount']); FlashMessage(GetLang('VendorPaymentCreated'), MSG_SUCCESS, 'index.php?ToDo=viewVendorPayments'); } } /** * Validate the new vendor payment before it's saved. * * @param array An array of details about the payment. * @param string Any error message, passed back by reference. * @return boolean True if the payment is valid, false if not. */ private function ValidateVendorPayment($data, &$error) { $requiredFields = array( 'paymentfrom' => GetLang('EnterPaymentFromPeriod'), 'paymentto' => GetLang('EnterPaymentToPeriod'), 'paymentvendorid' => GetLang('SelectPaymentVendor'), 'paymentamount' => GetLang('EnterPaymentAmount'), 'paymentmethod' => GetLang('EnterPaymentMethod') ); foreach($requiredFields as $field => $message) { if(!isset($data[$field]) || $data[$field] === '') { $error = $message; echo $field; return false; } } // Was an invalid payment amount entered? if(CNumeric($data['paymentamount']) <= 0) { $error = GetLang('EnterPaymentAmount'); return false; } // Otherwise it's valid return true; } /** * Actually commit a vendor payment to the database. * * @param array An array of details about the vendor payment. * @return int The ID of the new vendor payment that was just created. */ private function CommitVendorPayment($data) { if(!isset($data['paymentdeducted'])) { $data['paymentdeducted'] = 0; } if(!isset($data['paymentcomments'])) { $data['paymentcomments'] = ''; } $paymentDetails = $this->CalculateOutstandingVendorBalance($data['paymentvendorid']); $balanceForward = number_format($paymentDetails['balanceForward'], GetConfig('DecimalPlaces')); $totalOrders = number_format($paymentDetails['totalOrders'], GetConfig('DecimalPlaces')); $profitMargin = number_format($paymentDetails['profitMargin'], GetConfig('DecimalPlaces')); $forwardBalance = $balanceForward + $totalOrders - $profitMargin; if($data['paymentdeducted']) { $forwardBalance -= $data['paymentamount']; } $data['paymentamount'] = CNumeric($data['paymentamount']); $newPayment = array( 'paymentfrom' => $data['paymentfrom'], 'paymentto' => $data['paymentto'], 'paymentvendorid' => $data['paymentvendorid'], 'paymentamount' => $data['paymentamount'], 'paymentforwardbalance' => $forwardBalance, 'paymentmethod' => $data['paymentmethod'], 'paymentdate' => time(), 'paymentdeducted' => $data['paymentdeducted'], 'paymentcomments' => $data['paymentcomments'] ); $paymentId = $GLOBALS['ISC_CLASS_DB']->InsertQuery('vendor_payments', $newPayment); if(isset($data['notifyvendor'])) { $query = " SELECT vendorname, vendoremail FROM [|PREFIX|]vendors WHERE vendorid='".(int)$data['paymentvendorid']."' "; $result = $GLOBALS['ISC_CLASS_DB']->Query($query); $vendor = $GLOBALS['ISC_CLASS_DB']->Fetch($result); $emailTemplate = FetchEmailTemplateParser(); $GLOBALS['VendorName'] = isc_html_escape($vendor['vendorname']); $GLOBALS['VendorPaymentEmail1'] = sprintf(GetLang('VendorPaymentEmail1'), isc_html_escape(GetConfig('StoreName')), CDate($data['paymentfrom']), CDate($data['paymentto'])); $GLOBALS['SalesFrom'] = CDate($data['paymentfrom']); $GLOBALS['SalesTo'] = CDate($data['paymentto']); $GLOBALS['OrderTotal'] = FormatPrice($paymentDetails['totalOrders']); $GLOBALS['PaymentAmount'] = FormatPrice($data['paymentamount']); $GLOBALS['PaymentMethod'] = isc_html_escape($data['paymentmethod']); if($data['paymentcomments']) { $GLOBALS['Comments'] = '<strong>'.GetLang('Comments').':</strong><br />'.isc_html_escape($data['paymentcomments']); } $GLOBALS['AccountBalance'] = FormatPrice($forwardBalance); $emailTemplate->SetTemplate("vendor_payment"); $message = $emailTemplate->ParseTemplate(true); // Create a new email API object to send the email $storeName = GetConfig('StoreName'); $subject = sprintf(GetLang('VendorPaymentEmailSubject'), $storeName); require_once(ISC_BASE_PATH . "/lib/email.php"); $objEmail = GetEmailClass(); $objEmail->Set('CharSet', GetConfig('CharacterSet')); $objEmail->From(GetConfig('AdminEmail'), $storeName); $objEmail->Set('Subject', $subject); $objEmail->AddBody("html", $message); $objEmail->AddRecipient($vendor['vendoremail'], '', "h"); $objEmail->Send(); } if(!$paymentId) { return false; } return $paymentId; } /** * Show the page listing the vendor payments. */ private function ManageVendorPayments() { // Fetch any payments and place them in a data grid $GLOBALS['PaymentDataGrid'] = $this->ManageVendorPaymentsGrid(); // Was this an ajax based sort? Return the table now if(isset($_REQUEST['ajax']) && $_REQUEST['ajax'] == 1) { echo $GLOBALS['PaymentDataGrid']; return; } $GLOBALS['HideClearResults'] = 'display: none'; if(isset($_REQUEST['vendorId']) && $_REQUEST['vendorId'] != 0) { $GLOBALS['HideClearResults'] = ""; $selectedVendor = $_REQUEST['vendorId']; } else { $selectedVendor = 0; } $GLOBALS['VendorList'] = $this->BuildVendorList($selectedVendor); $GLOBALS['Message'] = GetFlashMessageBoxes(); // Do we need to disable the export button and hide the search options? if(!$GLOBALS['PaymentDataGrid']) { $GLOBALS['DisableExport'] = 'disabled="disabled"'; $GLOBALS['DisplayGrid'] = 'display: none'; $GLOBALS['DisableDelete'] = 'disabled="disabled"'; $vendorCache = $GLOBALS['ISC_CLASS_DATA_STORE']->Read('Vendors'); if(count($_GET) > 1) { $GLOBALS['Message'] = MessageBox(GetLang('NoPaymentsForVendor'), MSG_ERROR); } else if(empty($vendorCache)) { $GLOBALS['DisableAdd'] = 'disabled="disabled"'; $GLOBALS['DisplaySearch'] = 'display: none'; $GLOBALS['Message'] = MessageBox(GetLang('NoVendorsConfigured'), MSG_ERROR); } else { $GLOBALS['Message'] = MessageBox(GetLang('NoVendorPayments'), MSG_SUCCESS); $GLOBALS['DisplaySearch'] = 'display: none'; } } $GLOBALS['ISC_CLASS_ADMIN_ENGINE']->PrintHeader(); $GLOBALS['ISC_CLASS_TEMPLATE']->SetTemplate('vendorpayments.manage'); $GLOBALS['ISC_CLASS_TEMPLATE']->ParseTemplate(); $GLOBALS['ISC_CLASS_ADMIN_ENGINE']->PrintFooter(); } /** * Generate a grid containing vendor payments for the current result set. * * @return string The generated payment grid. */ private function ManageVendorPaymentsGrid() { $page = 0; $start = 0; $numPages = 0; $paymentGrid = ''; $GLOBALS['Nav'] = ''; // Validate the sort order if(isset($_REQUEST['sortOrder']) && $_REQUEST['sortOrder'] == 'asc') { $sortOrder = 'asc'; } else { $sortOrder = 'desc'; } // Which fields can we sort by? $validSortFields = array( 'paymentid', 'paymentfrom', 'vendorname', 'paymentamount', 'paymentmethod', 'paymentdate' ); if(isset($_REQUEST['sortField']) && in_array($_REQUEST['sortField'], $validSortFields)) { $sortField = $_REQUEST['sortField']; SaveDefaultSortField('ManageVendorPayments', $_REQUEST['sortField'], $sortOrder); } else { list($sortField, $sortOrder) = GetDefaultSortField('ManageVendorPayments', 'paymentid', $sortOrder); } if (isset($_GET['page'])) { $page = (int)$_GET['page']; } else { $page = 1; } // Build the pagination and sort URL $searchURL = ''; foreach($_GET as $k => $v) { if($k == "sortField" || $k == "sortOrder" || $k == "page" || $k == "new" || $k == "ToDo" || !$v) { continue; } $searchURL .= '&'.$k.'='.urlencode($v); } $sortURL = $searchURL.'&sortField='.$sortField.'&sortOrder='.$sortOrder; $GLOBALS['SortURL'] = $sortURL; // Limit the number of payments returned if($page == 1) { $start = 0; } else { $start = ($page-1) * ISC_VENDOR_PAYMENTS_PER_PAGE; } // Grab the queries we'll be executing $paymentQueries = $this->BuildVendorPaymentSearchQuery($start, $sortField, $sortOrder); // How many results do we have? $numPayments = $GLOBALS['ISC_CLASS_DB']->FetchOne($paymentQueries['countQuery']); $numPages = ceil($numPayments / ISC_VENDOR_PAYMENTS_PER_PAGE); // Add the "(Page x of y)" label if($numPayments > ISC_VENDOR_PAYMENTS_PER_PAGE) { $GLOBALS['Nav'] = '('.GetLang('Page').' '.$page.' '.GetLang('Of').' '.$numPages.') '; $GLOBALS['Nav'] .= BuildPagination($numPayments, ISC_VENDOR_PAYMENTS_PER_PAGE, $page, 'index.php?ToDo=viewVendorPayments'.$sortURL); } else { $GLOBALS['Nav'] = ''; } $GLOBALS['SortField'] = $sortField; $GLOBALS['SortOrder'] = $sortOrder; $sortLinks = array( 'Id' => 'paymentid', 'Date' => 'paymentfrom', 'Vendor' => 'vendorname', 'Amount' => 'paymentamount', 'Method' => 'paymentmethod', 'PaymentDate' => 'paymentdate' ); BuildAdminSortingLinks($sortLinks, 'index.php?ToDo=viewVendorPayments&'.$searchURL.'&page='.$page, $sortField, $sortOrder); // Display the payments $result = $GLOBALS['ISC_CLASS_DB']->Query($paymentQueries['query']); while($payment = $GLOBALS['ISC_CLASS_DB']->Fetch($result)) { $GLOBALS['PaymentId'] = $payment['paymentid']; $GLOBALS['PaymentAmount'] = FormatPrice($payment['paymentamount']); $GLOBALS['PaymentMethod'] = isc_html_escape($payment['paymentmethod']); $GLOBALS['PaymentDate'] = CDate($payment['paymentdate']); $GLOBALS['PaymentFrom'] = CDate($payment['paymentfrom']); $GLOBALS['PaymentTo'] = CDate($payment['paymentto']); $GLOBALS['PaymentComments'] = nl2br(isc_html_escape($payment['paymentcomments'])); $GLOBALS['Vendor'] = isc_html_escape($payment['vendorname']); if(!$GLOBALS['PaymentComments']) { $GLOBALS['HideExpandLink'] = 'display: none'; } else { $GLOBALS['HideExpandLink'] = ''; } $GLOBALS['ISC_CLASS_TEMPLATE']->SetTemplate('vendorpayments.manage.row'); $paymentGrid .= $GLOBALS['ISC_CLASS_TEMPLATE']->ParseTemplate(true); } if(!$paymentGrid) { return ''; } $GLOBALS['PaymentGrid'] = $paymentGrid; $GLOBALS['ISC_CLASS_TEMPLATE']->SetTemplate('vendorpayments.manage.grid'); return $GLOBALS['ISC_CLASS_TEMPLATE']->ParseTemplate(true); } /** * Build a list of all of the vendors in the store. * * @param int The ID of the vendor to select in the list, if there is one. * @return string The generated <option> tags for the list of vendors. */ private function BuildVendorList($selectedVendor=0) { $vendorList = ''; $query = " SELECT vendorid, vendorname FROM [|PREFIX|]vendors ORDER BY vendorname "; $result = $GLOBALS['ISC_CLASS_DB']->Query($query); while($vendor = $GLOBALS['ISC_CLASS_DB']->Fetch($result)) { $sel = ''; if($vendor['vendorid'] == $selectedVendor) { $sel = 'selected="selected"'; } $vendorList .= '<option value="'.$vendor['vendorid'].'" '.$sel.'>'.isc_html_escape($vendor['vendorname']).'</option>'; } return $vendorList; } /** * Calculate information about a specific vendor's outstanding balance & last payment. * * @param int The vendor ID to generate the information for. * @return array Array containing the total amount of orders, forward balance, last payment date and the outstanding balance. */ public function CalculateOutstandingVendorBalance($vendorId) { // Grab the date of the last payment sent to the vendor and the balance owing at the time $query = " SELECT paymentdate, paymentforwardbalance, vendorprofitmargin FROM [|PREFIX|]vendors LEFT JOIN [|PREFIX|]vendor_payments ON (paymentvendorid=vendorid AND paymentdeducted='1') WHERE vendorid='".($vendorId)."' ORDER BY paymentdate DESC "; $query .= $GLOBALS['ISC_CLASS_DB']->AddLimit(0, 1); $result = $GLOBALS['ISC_CLASS_DB']->Query($query); $vendorPaymentDetails = $GLOBALS['ISC_CLASS_DB']->Fetch($result); if(!$vendorPaymentDetails['paymentdate']) { $vendorPaymentDetails['paymentdate'] = 0; $vendorPaymentDetails['paymentforwardbalance'] = 0; // Try and grab the date of the first order for this vendor $query = " SELECT orddate FROM [|PREFIX|]orders WHERE ordvendorid='".(int)$vendorId."' ORDER BY orddate ASC LIMIT 1 "; $vendorPaymentDetails['paymentdate'] = $GLOBALS['ISC_CLASS_DB']->FetchOne($query); } if(!$vendorPaymentDetails['paymentdate']) { $vendorPaymentDetails['paymentdate'] = time(); } // Grab the total amount of orders since the last payment $query = " SELECT SUM(ordtotalamount) FROM [|PREFIX|]orders WHERE ordvendorid='".(int)$vendorId."' AND orddate >= '".(int)$vendorPaymentDetails['paymentdate']."' AND ordstatus IN (".implode(',', GetPaidOrderStatusArray()).") "; $GLOBALS['ISC_CLASS_DB']->Query($query); $totalOrders = $GLOBALS['ISC_CLASS_DB']->FetchOne($query); $profitMargin = 0; if($vendorPaymentDetails['vendorprofitmargin'] > 0) { $profitMargin = ($totalOrders/100)*$vendorPaymentDetails['vendorprofitmargin']; } // check if the vendor has issued any store credit for a return, we need to deduct that from the total $query = " SELECT cc.creditamount FROM [|PREFIX|]returns r LEFT JOIN [|PREFIX|]customer_credits cc ON cc.creditrefid = r.returnid WHERE cc.credittype = 'return' AND r.retreceivedcredit = 1 AND r.retvendorid = '" . (int)$vendorId . "' AND cc.creditdate >= '" . (int)$vendorPaymentDetails['paymentdate'] . "' "; $result = $GLOBALS['ISC_CLASS_DB']->Query($query); $issuedCredit = 0; while ($row = $GLOBALS['ISC_CLASS_DB']->Fetch($result)) { $issuedCredit += $row['creditamount']; } $summary = array( 'totalOrders' => $totalOrders, 'balanceForward' => $vendorPaymentDetails['paymentforwardbalance'], 'issuedCredit' => $issuedCredit, 'lastPaymentDate' => $vendorPaymentDetails['paymentdate'], 'outstandingBalance' => ($totalOrders-$profitMargin) + $vendorPaymentDetails['paymentforwardbalance'] - $issuedCredit, 'profitMargin' => $profitMargin, 'profitMarginPercentage' => number_format($vendorPaymentDetails['vendorprofitmargin'], GetConfig('DecimalPlaces'), GetConfig('DecimalToken'), '') ); return $summary; } /** * Generate the database select and count queries for building the list of vendors. * * @param int The starting position to add to the query LIMIT. * @param string The field to sort by. * @param string The direction to sort in. * @param boolean True to add the LIMIT clause to the select query, false to not. * @return array Array containing the select & count queries. */ private function BuildVendorPaymentSearchQuery($start, $sortField, $sortOrder, $addLimit=true) { $query = " SELECT p.*, v.vendorname FROM [|PREFIX|]vendor_payments p INNER JOIN [|PREFIX|]vendors v ON (v.vendorid=p.paymentvendorid) "; $countQuery = " SELECT COUNT(paymentid) FROM [|PREFIX|]vendor_payments "; // Let's add in any search arguments $queryWhere = ''; if(isset($_REQUEST['vendorId']) && $_REQUEST['vendorId'] != 0) { $queryWhere .= " AND paymentvendorid='".(int)$_REQUEST['vendorId']."'"; } // Construct the actual query $query .= " WHERE 1=1 ".$queryWhere; $countQuery .= " WHERE 1=1 ".$queryWhere; $query .= " ORDER BY ".$sortField." ".$sortOrder; if($addLimit) { $query .= $GLOBALS['ISC_CLASS_DB']->AddLimit($start, ISC_VENDOR_PAYMENTS_PER_PAGE); } // Return or generated queries return array( 'query' => $query, 'countQuery' => $countQuery ); } } ?> Quote Link to comment Share on other sites More sharing options...
chinwan Posted July 26, 2009 Author Share Posted July 26, 2009 Looking at the "weird number". I think I figured out what is happening, but the question is why: If it is a four digit number, it gets rid of the first 3 digits, and only makes the caluclations basd on the fourh digit. For example: Payment forward = 3,000 New purchases = 2,000 total =, 5000 If make a payment of 4,000, it calculates it as: 5-4,000 so = -3995 Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted July 26, 2009 Share Posted July 26, 2009 Payment forward = 3,000 New purchases = 2,000 total =, 5000 If make a payment of 4,000, it calculates it as: 5-4,000 so = -3995 I fail to see what's wrong. Surely 2000 + 3000 is 5000 and, similarly, 5 - 4000 is -3995 ? Quote Link to comment Share on other sites More sharing options...
chinwan Posted July 26, 2009 Author Share Posted July 26, 2009 Sorry, let me explain a little better: Payment forward = 3,000 New purchases = 2,000 total = 5000 And that is how it displays it. Now if I enter a payment of 4,000. It should store that there is a remaining balance of 1000. 3000+ 2000 = 5000 - 4000 = 1000. But with it actually stores id -3995. Because what it is doing is: 3 + 2 = 5 - 4000 = -3995. It is using only the first digit of the four digit number. Thanks Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted July 26, 2009 Share Posted July 26, 2009 Well i can't see anything in particular that looks odd at first glance. My first suggestion would therefore be to check the lengths of the relevant fields in your database to ensure that no truncation is going on at any point. Quote Link to comment Share on other sites More sharing options...
akitchin Posted July 26, 2009 Share Posted July 26, 2009 it's possible that this is related to number_format() spitting out a string rather than a numerical type, probably due to the comma thousands separator. perhaps you could perform the calculation on the numbers without using number_format() and see if that fixes things. Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted July 26, 2009 Share Posted July 26, 2009 Commas , are stop characters in computer language parsers. In computers, numbers are 3000, 2000, not 3,000, 2,000. The thousands comma is only there to make the number more human friendly. You should only use number_format() as the final step when you display numbers, not when you store or use numbers in calculations. Quote Link to comment Share on other sites More sharing options...
chinwan Posted July 26, 2009 Author Share Posted July 26, 2009 That was the problem. Thanks a million. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.