/**
* Return the quota for the current user and time period
*
* @return mixed
*/
protected function getUserQuota()
{
/* @var $quota_bean Quota */
$quota_bean = BeanFactory::getBean('Quotas');
$quota = $quota_bean->getRollupQuota($this->getArg('timeperiod_id'), $this->getArg('user_id'));
return SugarCurrency::convertAmountToBase($quota['amount'], $quota['currency_id']);
}
/**
* Method for sorting the dataArray before we return it so that the tallest bar is always first and the
* lowest bar is always last.
*
* @param array $a The left side of the compare
* @param array $b The right side of the compare
* @return int
*/
protected function sortChartColumns($a, $b)
{
$sumA = 0;
$sumB = 0;
foreach ($this->dataset as $dataset) {
$sumA += SugarCurrency::convertAmountToBase($a[$dataset . '_case_adjusted'], $a['currency_id']);
$sumB += SugarCurrency::convertAmountToBase($b[$dataset . '_case_adjusted'], $b['currency_id']);
}
if (intval($sumA) > intval($sumB)) {
return -1;
} else {
if (intval($sumA) < intval($sumB)) {
return 1;
} else {
return 0;
}
}
}
public function update_currency_id($fromid, $toid)
{
$idequals = '';
$currency = BeanFactory::getBean('Currencies', $toid);
foreach ($fromid as $f) {
if (!empty($idequals)) {
$idequals .= ' or ';
}
$idequals .= "currency_id='{$f}'";
}
if (!empty($idequals)) {
$query = "select amount, id from opportunities where (" . $idequals . ") and deleted=0 and opportunities.sales_stage <> '" . self::STAGE_CLOSED_WON . "' AND opportunities.sales_stage <> '" . self::STAGE_CLOSED_LOST . "';";
$result = $this->db->query($query);
while ($row = $this->db->fetchByAssoc($result)) {
$query = sprintf("update opportunities set currency_id='%s',\n amount_usdollar='%s',\n base_rate='%s'\n where id='%s';", $currency->id, SugarCurrency::convertAmountToBase($row['amount'], $currency->id), $currency->conversion_rate, $row['id']);
$this->db->query($query);
}
}
}
/**
* Updates currency values for Product Bundles and Quotes based on the Products table
*/
public function fixQuoteAndProductBundleValues()
{
// Collect all quote, product_bundle and products data
$sql = "\n SELECT\n PBQ.quote_id AS quote_id,\n PBQ.bundle_id AS bundle_id,\n Q.currency_id AS currency_id,\n PB.shipping as shipping,\n P.quantity AS quantity,\n P.discount_price AS discount_price,\n P.discount_amount AS discount_amount,\n P.discount_select AS discount_select,\n T.value AS tax_value,\n P.tax_class AS tax_class\n FROM\n quotes Q LEFT JOIN taxrates T ON Q.taxrate_id = T.id,\n product_bundles PB,\n product_bundle_quote PBQ,\n product_bundle_product PBP,\n products P\n WHERE\n Q.id = PBQ.quote_id\n AND PBQ.bundle_id = PB.id\n AND PBQ.bundle_id = PBP.bundle_id\n AND PBP.product_id = P.id\n ";
$bundles = array();
$quotes = array();
$result = $this->db->query($sql);
while ($row = $this->db->fetchByAssoc($result)) {
if (empty($bundles[$row['bundle_id']])) {
$bundles[$row['bundle_id']] = array('tax' => 0, 'subtotal' => 0, 'deal_tot' => 0, 'new_sub' => 0);
}
$subtotal = $row['quantity'] * $row['discount_price'];
if ($row['discount_select'] != 1) {
$deal_tot = $row['quantity'] * $row['discount_amount'];
} else {
$deal_tot = $row['quantity'] * ($row['discount_amount'] * $row['discount_price'] / 100);
}
$new_sub = $subtotal - $deal_tot;
// Calculate the aggregate fields
$bundles[$row['bundle_id']]['subtotal'] += $subtotal;
$bundles[$row['bundle_id']]['deal_tot'] += $deal_tot;
$bundles[$row['bundle_id']]['new_sub'] += $new_sub;
// If field is taxable, add tax for it
if ($row['tax_class'] == 'Taxable') {
$bundles[$row['bundle_id']]['tax'] += $row['tax_value'] * $new_sub / 100;
}
$bundles[$row['bundle_id']]['shipping'] = $row['shipping'];
$bundles[$row['bundle_id']]['currency_id'] = $row['currency_id'];
$bundles[$row['bundle_id']]['quote_id'] = $row['quote_id'];
$quotes[$row['quote_id']] = array('tax' => 0, 'subtotal' => 0, 'deal_tot' => 0, 'new_sub' => 0, 'shipping' => 0, 'total' => 0, 'tax_usdollar' => 0, 'subtotal_usdollar' => 0, 'deal_tot_usdollar' => 0, 'new_sub_usdollar' => 0, 'shipping_usdollar' => 0, 'total_usdollar' => 0);
}
// Build total and _usdollar fields
foreach ($bundles as $id => $value) {
$bundles[$id]['total'] = $value['tax'] + $value['new_sub'] + $value['shipping'];
$bundles[$id]['tax_usdollar'] = SugarCurrency::convertAmountToBase($value['tax'], $value['currency_id']);
$bundles[$id]['subtotal_usdollar'] = SugarCurrency::convertAmountToBase($value['subtotal'], $value['currency_id']);
$bundles[$id]['deal_tot_usdollar'] = SugarCurrency::convertAmountToBase($value['deal_tot'], $value['currency_id']);
$bundles[$id]['new_sub_usdollar'] = SugarCurrency::convertAmountToBase($value['new_sub'], $value['currency_id']);
$bundles[$id]['shipping_usdollar'] = SugarCurrency::convertAmountToBase($bundles[$id]['shipping'], $value['currency_id']);
$bundles[$id]['total_usdollar'] = SugarCurrency::convertAmountToBase($bundles[$id]['total'], $value['currency_id']);
}
// Cycle through the bundles and update the values for them
foreach ($bundles as $id => $values) {
$quoteId = $values['quote_id'];
unset($values['currency_id']);
unset($values['quote_id']);
// Build the fields to be updated
$sqlFields = array();
foreach ($values as $fieldId => $fieldValue) {
$sqlFields[] = "{$fieldId} = '{$fieldValue}'";
// Fill in quote values
$quotes[$quoteId][$fieldId] += $fieldValue;
}
// Update the product_bundle values
$sql = "UPDATE product_bundles SET " . implode(', ', $sqlFields) . " WHERE id = '{$id}'";
$this->db->query($sql);
}
// Cycle through the quotes and update the values for them
foreach ($quotes as $id => $values) {
// Build the fields to be updated
$sqlFields = array();
foreach ($values as $fieldId => $fieldValue) {
$sqlFields[] = "{$fieldId} = '{$fieldValue}'";
}
// Update the product_bundle values
$sql = "UPDATE quotes SET " . implode(', ', $sqlFields) . " WHERE id = '{$id}'";
$this->db->query($sql);
}
}
