Elep

<?php

/*
 * Your installation or use of this SugarCRM file is subject to the applicable
 * terms available at
 * http://support.sugarcrm.com/Resources/Master_Subscription_Agreements/.
 * If you do not agree to all of the applicable terms or do not have the
 * authority to bind the entity as an authorized representative, then do not
 * install or use this SugarCRM file.
 *
 * Copyright (C) SugarCRM Inc. All rights reserved.
 */

class ForecastOpportunities extends SugarBean
{
    public $id;
    public $name;
    public $revenue;
    public $probability;
    public $account_name;
    public $weighted_value;
    public $current_user_id;
    public $start_date;
    public $end_date;

public $currency;
    public $currencysymbol;
    public $currency_id;

public $current_timeperiod_id;

public $object_name = 'ForecastOpportunities';
    public $module_name = 'ForecastOpportunities';
    public $module_dir = 'Forecasts';

public $table_name = 'opportunities';
    public $disable_custom_fields = true;

public $encodeFields = [];

// This is used to retrieve related fields from form posts.
    public $additional_column_fields = [];

public $fo_user_id;
    public $fo_timeperiod_id;
    public $fo_forecast_type;

public $new_schema = true;

public function __construct()
    {
        global $current_user, $db;

parent::__construct();
        $this->disable_row_level_security = true;

$this->currency = BeanFactory::newBean('Currencies')->getUserCurrency();
        $this->currencysymbol = $this->currency->symbol;
    }

public function save($check_notify = false)
    {
        parent::save($check_notify);
    }

public function get_summary_text()
    {
        return $this->name;
    }

public function is_authenticated()
    {
        return $this->authenticated;
    }

public function fill_in_additional_list_fields()
    {
        $this->fill_in_additional_detail_fields();
    }

public function fill_in_additional_detail_fields()
    {
        $this->account_name = $this->get_opportunity_account_name($this->id);

//get adjustment amount for the opportunity.
    }

public function get_list_view_data($filter_fields = [])
    {

/* amounts here are in base currency we need to convert them to user default
         * currency.
         */

$temp_array = $this->get_list_view_array();

$temp_array['ACCOUNT_NAME'] = $this->account_name;
        if (empty($temp_array['PROBABILITY'])) {
            $temp_array['PROBABILITY'] = 0;
        }
        if (empty($temp_array['WEIGHTED_VALUE'])) {
            $temp_array['WEIGHTED_VALUE'] = 0;
        }
        if (empty($temp_array['REVENUE'])) {
            $temp_array['REVENUE'] = 0;
        }

//convert amount from base current to user's preferred currency
        $temp_array['WEIGHTED_VALUE'] = $this->currency->convertFromDollar($temp_array['WEIGHTED_VALUE'], 0);
        $temp_array['REVENUE'] = $this->currency->convertFromDollar($temp_array['REVENUE'], 0);

if (empty($temp_array['WORKSHEET_ID'])) {
            $temp_array['WK_LIKELY_CASE'] = $temp_array['WEIGHTED_VALUE'];
            $temp_array['WK_WORST_CASE'] = $temp_array['WEIGHTED_VALUE'];
            $temp_array['WK_BEST_CASE'] = $temp_array['WEIGHTED_VALUE'];
        } else {
            $temp_array['WK_LIKELY_CASE'] = $this->currency->convertFromDollar($this->likely_case);
            $temp_array['WK_WORST_CASE'] = $this->currency->convertFromDollar($this->worst_case);
            $temp_array['WK_BEST_CASE'] = $this->currency->convertFromDollar($this->best_case);
        }

//format numbers and add currency symbols.
        $temp_array['WEIGHTED_VALUE'] = $this->currency->symbol . format_number($temp_array['WEIGHTED_VALUE'], 0, 0);
        $temp_array['REVENUE'] = $this->currency->symbol . format_number($temp_array['REVENUE'], 0, 0);

return $temp_array;
    }

public function list_view_parse_additional_sections(&$list_form)
    {
        return $list_form;
    }

public function create_new_list_query($order_by, $where, $filter = [], $params = [], $show_deleted = 0, $join_type = '', $return_array = false, $parentbean = null, $singleSelect = false, $ifListForExport = false)
    {
        // Workaround due to fix for Bug 14232. date_entered is ambiguous in this case, so we specify it if it is the default sort
        if (strpos($order_by, 'date_entered') !== false) {
            $order_by = str_replace('date_entered', 'opportunities.date_entered', $order_by);
        }
        $opp = BeanFactory::newBean('Opportunities');
        $ret_array = [];
        $ret_array['select'] = 'SELECT  opportunities.id, opportunities.name ,opportunities.assigned_user_id opportunity_owner, opportunities.amount_usdollar as revenue,  ((opportunities.amount_usdollar * opportunities.probability)/100) as weighted_value, opportunities.probability,opportunities.description, opportunities.next_step,opportunities.opportunity_type';
        $ret_array['select'] .= ' ,worksheet.id worksheet_id, opportunities.best_case,opportunities.worst_case ';
        $ret_array['from'] = ' FROM opportunities  ';
        $opp->addVisibilityFrom($ret_array['from'], ['where_condition' => true]);
        $ret_array['where'] = " INNER JOIN timeperiods on 1=1 LEFT JOIN worksheet on opportunities.id = worksheet.related_id and worksheet.user_id='{$this->fo_user_id}' and worksheet.timeperiod_id='{$this->fo_timeperiod_id}' and worksheet.forecast_type='{$this->fo_forecast_type}'";
        $opp->addVisibilityFrom($where, ['where_condition' => true]);
        $ret_array['where'] .= ' WHERE ' . $where;

$ret_array['order_by'] = !empty($order_by) ? ' ORDER BY ' . $order_by : ' ORDER BY opportunities.name ';
        return $ret_array;
    }

//get opportunity forecast summary
    public function get_opportunity_summary($currency_format = true)
    {

$abc = [];
        $amount_usdollar = $this->db->convert('opportunities.amount_usdollar', 'IFNULL', [0]);
        $probability = $this->db->convert('opportunities.probability', 'IFNULL', [0]);
        $query1 = 'SELECT count(*) as opportunitycount, sum(amount_usdollar) as total_amount,
            sum((amount_usdollar * opportunities.probability)/100) as weightedvalue,
            sum(' . $this->db->convert('opportunities.best_case', 'IFNULL', ["(($amount_usdollar * $probability)/100)"]) . ') total_best_case,
            sum(' . $this->db->convert('opportunities.worst_case', 'IFNULL', ["(($amount_usdollar * $probability)/100)"]) . ') total_worst_case';

$query1 .= ' FROM timeperiods, opportunities ';
        $query1 .= " LEFT JOIN worksheet on opportunities.id = worksheet.related_id and worksheet.user_id='{$this->fo_user_id}' and worksheet.timeperiod_id='{$this->fo_timeperiod_id}' and worksheet.forecast_type='{$this->fo_forecast_type}'";
        $query1 .= ' WHERE date_closed >= timeperiods.start_date';
        $query1 .= ' AND date_closed <= timeperiods.end_date';
        $query1 .= " AND assigned_user_id = '$this->current_user_id'";
        $query1 .= ' AND opportunities.deleted = 0';
        $query1 .= ' AND opportunities.probability >= 70';
        $query1 .= " AND timeperiods.id = '$this->current_timeperiod_id'";
        $query1 .= " AND opportunities.sales_stage != '" . Opportunity::STAGE_CLOSED_LOST . "'";

$query2 = 'SELECT sum(o.amount * o.base_rate) as amount, count(*) as rows FROM opportunities o ' .
            'INNER JOIN timeperiods t ' .
            "ON t.id = '{$this->current_timeperiod_id}' " .
            "WHERE o.sales_stage in ('" . Opportunity::STAGE_CLOSED_WON . "', '" . Opportunity::STAGE_CLOSED_LOST . "') " .
            "AND o.assigned_user_id = '{$this->current_user_id}' " .
            'AND o.date_closed >= t.start_date ' .
            'AND o.date_closed <= t.end_date ';

$result1 = $this->db->query($query1, true, 'Error filling in opportunity details: ');
        $row1 = $this->db->fetchByAssoc($result1);
        $result2 = $this->db->query($query2, true, 'Error getting close lost/won count: ');
        $row2 = $this->db->fetchByAssoc($result2);

if ($row1 == null) {
            $abc['OPPORTUNITYCOUNT'] = 0;
            $abc['WEIGHTEDVALUE'] = 0;
            $abc['COMMITVALUE'] = 0;
            $abc['WEIGHTEDVALUENUMBER'] = 0;
            $abc['TIMEPERIOD_ID'] = $this->current_timeperiod_id;
            $abc['USER_ID'] = $this->current_user_id;
            $abc['TOTAL_AMOUNT'] = 0;
            $abc['TOTAL_WK_BEST_CASE'] = 0;
            $abc['TOTAL_WK_LIKELY_CASE'] = 0;
            $abc['TOTAL_WK_WORST_CASE'] = 0;
        } else {
            //make sure that worksheet records were created.

$abc['OPPORTUNITYCOUNT'] = $row1['opportunitycount'];
            $abc['WEIGHTEDVALUE'] = empty($row1['weightedvalue']) ? 0 : $row1['weightedvalue'];
            $abc['WEIGHTEDVALUENUMBER'] = empty($row1['weightedvalue']) ? 0 : $row1['weightedvalue'];
            $abc['TIMEPERIOD_ID'] = $this->current_timeperiod_id;
            $abc['USER_ID'] = $this->current_user_id;
            $abc['TOTAL_AMOUNT'] = (empty($row1['total_amount']) ? 0 : $row1['total_amount']);
            $abc['TOTAL_WK_BEST_CASE'] = (empty($row1['total_best_case']) ? 0 : $row1['total_best_case']);
            $abc['TOTAL_WK_LIKELY_CASE'] = (empty($row1['total_likely_case']) ? 0 : $row1['total_likely_case']);
            $abc['TOTAL_WK_WORST_CASE'] = (empty($row1['total_worst_case']) ? 0 : $row1['total_worst_case']);
        }
        if ($currency_format) {
            //convert to preferred currency and format for $ and ,
            $abc['WEIGHTEDVALUE'] = $this->currency->convertFromDollar($abc['WEIGHTEDVALUE'], 0);
            $abc['TOTAL_AMOUNT'] = $this->currency->convertFromDollar($abc['TOTAL_AMOUNT'], 0);
            $abc['TOTAL_WK_BEST_CASE'] = $this->currency->convertFromDollar($abc['TOTAL_WK_BEST_CASE'], 0);
            $abc['TOTAL_WK_LIKELY_CASE'] = $this->currency->convertFromDollar($abc['TOTAL_WK_LIKELY_CASE'], 0);
            $abc['TOTAL_WK_WORST_CASE'] = $this->currency->convertFromDollar($abc['TOTAL_WK_WORST_CASE'], 0);

//format currency and number selectively
            $abc['WEIGHTEDVALUE'] = $this->currency->symbol . format_number($abc['WEIGHTEDVALUE'], 0, 0);
            $abc['TOTAL_AMOUNT'] = $this->currency->symbol . format_number($abc['TOTAL_AMOUNT'], 0, 0);
        }

if ($row2 == null) {
            $abc['CLOSED_OPP_COUNT'] = 0;
            $abc['CLOSED_AMOUNT'] = 0;
        } else {
            $abc['CLOSED_OPP_COUNT'] = $row2['rows'];
            $abc['CLOSED_AMOUNT'] = empty($row2['amount']) ? 0 : $row2['amount'];
        }

return $abc;
    }

//get name of the account associated with the opportunity.
    //since there is a many to many relationhsip first account fetched
    //will be returned, the accounts_opportunities table should at least
    //have an date entered.
    public function get_opportunity_account_name($opportunity_id)
    {

$query = 'SELECT name FROM accounts, accounts_opportunities';
        $query .= ' WHERE accounts.id = accounts_opportunities.account_id';
        $query .= ' AND accounts_opportunities.opportunity_id = ? AND accounts_opportunities.deleted = 0';

$conn = $this->db->getConnection();
        $stmt = $conn->executeQuery($query, [$opportunity_id]);
        $row = $stmt->fetchAssociative();

if ($row) {
            return $row['name'];
        }

return '';
    }

public function get_last_committed_direct_forecast()
    {
        $last_committed = [];
        global $timedate;

$query = 'SELECT ';
        $query .= ' forecasts.best_case, likely_case, worst_case, forecasts.date_entered ';
        $query .= ' FROM forecasts';
        $query .= " WHERE forecasts.timeperiod_id = '$this->current_timeperiod_id'";
        $query .= " AND forecasts.forecast_type = 'Direct'";
        $query .= " AND forecasts.user_id = '$this->current_user_id'";
        $query .= ' ORDER BY forecasts.date_entered desc';

$result = $this->db->query($query, true, 'Error fetching last committed forecast:');
        if (($row = $this->db->fetchByAssoc($result)) != null) {
            $last_committed['BEST_CASE'] = $this->currency->symbol . format_number($this->currency->convertFromDollar($row['best_case']), 0, 0);
            $last_committed['WORST_CASE'] = $this->currency->symbol . format_number($this->currency->convertFromDollar($row['worst_case']), 0, 0);
            $last_committed['LIKELY_CASE'] = $this->currency->symbol . format_number($this->currency->convertFromDollar($row['likely_case']), 0, 0);
            $last_committed['DATE_ENTERED'] = $timedate->to_display_date_time($this->db->fromConvert($row['date_entered'], 'datetime'));
        } else {
            $last_committed['BEST_CASE'] = '';
            $last_committed['WORST_CASE'] = '';
            $last_committed['LIKELY_CASE'] = '';
            $last_committed['DATE_ENTERED'] = '';
        }

return $last_committed;
    }

public function listviewACLHelper()
    {
        $array_assign = parent::listviewACLHelper();
        $is_owner = false;
        if (!empty($this->name)) {
            if (!empty($this->opportunity_owner)) {
                global $current_user;
                $is_owner = $current_user->id == $this->opportunity_owner;
            }
        }
        if (ACLController::checkAccess('Opportunities', 'view', $is_owner)) {
            $array_assign['OPPORTUNITY'] = 'a';
        } else {
            $array_assign['OPPORTUNITY'] = 'span';
        }

return $array_assign;
    }

/**
     * Returns quota amount given the user id and timeperiod id. Will return the Direct quota value.
     */
    public function get_quota()
    {

$query = "select amount_base_currency from quotas where deleted=0 and user_id='$this->current_user_id' and quota_type='Direct' and timeperiod_id='$this->current_timeperiod_id'";
        $result = $this->db->query($query, true, 'Error fetching quota');
        $row = $this->db->fetchByAssoc($result);
        if (!empty($row)) {
            return $this->currency->symbol . format_number($this->currency->convertFromDollar($row['amount_base_currency'], 0), 0, 0);
        }
        return null;
    }
}