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 ForecastDirectReports extends SugarBean
{
    public $user_id;
    public $reports_to_id;
    public $id;
    public $timeperiod_id;
    public $opp_count;
    public $opp_weigh_value;
    public $opp_commit_value;
    public $user_name;
    public $ref_user_id;
    public $ref_timeperiod_id;
    public $forecast_type;

public $likely_case;
    public $total_likely_case;
    public $total_likely_case_number;
    public $total_wk_likely_case_number;

public $best_case;
    public $total_best_case;
    public $total_best_case_number;
    public $total_wk_best_case_number;

public $worst_case;
    public $total_worst_case;
    public $total_worst_case_number;
    public $total_wk_worst_case_number;

public $total_opp_count;
    public $total_weigh_value;
    public $total_commit_value;

public $total_opp_count_number;
    public $total_weigh_value_number;
    public $total_commit_value_number;

public $current_user_id;
    public $current_timeperiod_id;
    public $currency;
    public $currencysymbol;
    public $currency_id;

public $pipeline_amount;
    public $pipeline_opp_count;

public $object_name = 'ForecastDirectReports';
    public $module_dir = 'Forecasts';
    public $disable_custom_fields = true;

public $table_name = 'users';

public $encodeFields = [];

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->user_name";
    }

public function retrieve($id = '-1', $encode = false, $deleted = true)
    {
        $ret = parent::retrieve($id, $encode, $deleted);

return $ret;
    }

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->get_forecasts();
    }

//get forecasts submitted by the user.
    //if the user is a manager then select the rollup forecast.
    //			except for when the current user is the manager.
    //if the user is a sales person select the direct forecasts.
    //select the most recent forcast based on the date committed.
    public function get_forecasts()
    {
        global $timedate;

$comm1 = new Common();
        $comm1->set_current_user($this->id);
        $comm1->setup();

//fetch rollup forecast for managers, but if the manager is the current user
        //fetch direct forecast.
        if ($comm1->is_user_manager() && $this->id != $this->current_user_id) {
            $type = 'Rollup';
        } else {
            $type = 'Direct';
        }

$query = $this->create_forecast_query_for_user($this->id, $type, $this->current_user_id, 'Rollup');
        $result = $this->db->query($query, true, 'Error fetching forecasts:');
        $row = $this->db->fetchByAssoc($result);
        if ($row != null) {
            $this->opp_count = $row['opp_count'];
            $this->opp_best_case = $row['best_case'];
            $this->opp_worst_case = $row['worst_case'];
            $this->opp_likely_case = $row['likely_case'];
            $this->date_entered = $timedate->to_display_date_time($row['date_entered']);
            $this->date_committed = $timedate->to_display_date($row['date_entered']);

$this->opp_weigh_value = $row['opp_weigh_value'];
            $this->forecast_type = $row['forecast_type'];

if (empty($row['worksheet_id'])) {
                $this->wk_likely_case = $row['likely_case'];
                $this->wk_best_case = $row['best_case'];
                $this->wk_worst_case = $row['worst_case'];
            } else {
                $this->wk_likely_case = $row['wk_likely_case'];
                $this->wk_best_case = $row['wk_best_case'];
                $this->wk_worst_case = $row['wk_worst_case'];
            }
        } else {
            $this->opp_count = 0;
            $this->opp_best_case = 0;
            $this->opp_worst_case = 0;
            $this->opp_likely_case = 0;
            $this->wk_likely_case = 0;
            $this->wk_best_case = 0;
            $this->wk_worst_case = 0;
            $this->date_entered = '';
            $this->date_committed = '';
            $this->opp_weigh_value = 0;
            $this->forecast_type = $type;
        }

//convert amounts to user's preferred currency.
        $this->opp_best_case = $this->currency->convertFromDollar($this->opp_best_case);
        $this->opp_worst_case = $this->currency->convertFromDollar($this->opp_worst_case);
        $this->opp_likely_case = $this->currency->convertFromDollar($this->opp_likely_case);
        $this->wk_likely_case = $this->currency->convertFromDollar($this->wk_likely_case);
        $this->wk_best_case = $this->currency->convertFromDollar($this->wk_best_case);
        $this->wk_worst_case = $this->currency->convertFromDollar($this->wk_worst_case);
    }

//query returns the amount committed by the user and any override value specified by the
    //logged in user.
    //select forecasts in descending order. the system shows only the most recent forecast.
    //join with the worksheet table to get the overide created for that  user's commit amount
    public function create_forecast_query_for_user($this_user, $type, $cur_user_id, $cur_user_forecast_type)
    {
        global $current_user;

$query = <<<SQL
SELECT forecasts.id, forecasts.opp_count, forecasts.pipeline_opp_count, forecasts.pipeline_amount, 
forecasts.opp_weigh_value, forecasts.best_case,forecasts.likely_case,forecasts.worst_case, forecasts.date_entered,
%s forecast_type, worksheet.id worksheet_id, worksheet.best_case wk_best_case, 
worksheet.likely_case wk_likely_case, worksheet.worst_case wk_worst_case
FROM forecasts
LEFT JOIN worksheet ON forecasts.user_id = worksheet.related_id AND worksheet.user_id=%s AND worksheet.forecast_type=%s 
AND worksheet.timeperiod_id=%s AND related_forecast_type=%s
WHERE forecasts.timeperiod_id = %s AND forecasts.forecast_type = %s AND forecasts.user_id = %s
ORDER BY forecasts.date_entered DESC
SQL;

return sprintf(
            $query,
            $this->db->quoted($type),
            $this->db->quoted($current_user->id),
            $this->db->quoted($cur_user_forecast_type),
            $this->db->quoted($this->current_timeperiod_id),
            $this->db->quoted($type),
            $this->db->quoted($this->current_timeperiod_id),
            $this->db->quoted($type),
            $this->db->quoted($this_user)
        );
    }

public function get_list_view_data($filter_fields = [])
    {
        global $locale;
        $forecast_fields = $this->get_list_view_array();

$forecast_fields['ID'] = $this->id;
        $forecast_fields['OPP_COUNT'] = $this->opp_count;
        $forecast_fields['DATE_ENTERED'] = $this->date_entered;
        $forecast_fields['DATE_COMMITTED'] = $this->date_committed;

$forecast_fields['OPP_WEIGH_VALUE'] = $this->currency->convertFromDollar($this->opp_weigh_value);

$forecast_fields['REF_USER_ID'] = $this->current_user_id;
        $forecast_fields['REF_TIMEPERIOD_ID'] = $this->current_timeperiod_id;

$forecast_fields['FORECAST_TYPE'] = $this->forecast_type;

$forecast_fields['USER_NAME'] = $locale->formatName(
            'Users',
            [
                'first_name' => $forecast_fields['FIRST_NAME'],
                'last_name' => $forecast_fields['LAST_NAME'],
            ]
        );

$forecast_fields['BEST_CASE'] = $this->opp_best_case;
        $forecast_fields['WORST_CASE'] = $this->opp_worst_case;
        $forecast_fields['LIKELY_CASE'] = $this->opp_likely_case;

$forecast_fields['WK_BEST_CASE'] = $this->wk_best_case;
        $forecast_fields['WK_WORST_CASE'] = $this->wk_worst_case;
        $forecast_fields['WK_LIKELY_CASE'] = $this->wk_likely_case;

//currency conversions and formatting.
        $forecast_fields['OPP_WEIGH_VALUE'] = $this->currency->symbol . format_number($forecast_fields['OPP_WEIGH_VALUE'], 0, 0);

$forecast_fields['BEST_CASE'] = $this->currency->symbol . format_number($forecast_fields['BEST_CASE'], 0, 0);
        $forecast_fields['WORST_CASE'] = $this->currency->symbol . format_number($forecast_fields['WORST_CASE'], 0, 0);
        $forecast_fields['LIKELY_CASE'] = $this->currency->symbol . format_number($forecast_fields['LIKELY_CASE'], 0, 0);

return $forecast_fields;
    }

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

public function create_list_query($order_by, $where, $show_deleted = 0)
    {
        //build a list of users that report to this user, including this user
        //forecast details will be filled in later.
        $query = 'SELECT id, users.first_name ,users.last_name, users.id as user_id';
        $query .= ' FROM users ';

if (empty($where)) {
            $query .= " where users.status = 'Active'";
        } else {
            $query .= " where $where AND users.status = 'Active'";
        }

if ($order_by != '') {
            $query .= " ORDER BY $order_by";
        } else {
            $query .= ' ORDER BY users.first_name, users.last_name';
        }

return $query;
    }

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)
    {
        $ret_array = [];
        $ret_array['select'] = 'SELECT id, users.first_name ,users.last_name, users.id as user_id';
        $ret_array['from'] = ' FROM users  ';
        $us = BeanFactory::newBean('Users');
        $us->addVisibilityFrom($ret_array['from'], ['where_condition' => true]);
        $ret_array['where'] = " where $where AND users.status = 'Active'";
        $us->addVisibilityFrom($ret_array['where'], ['where_condition' => true]);
        $ret_array['order_by'] = !empty($order_by) ? ' ORDER BY ' . $order_by : '  ORDER BY users.last_name';
        if ($return_array) {
            return $ret_array;
        } else {
            return $ret_array['select'] . $ret_array['from'] . $ret_array['where'] . $ret_array['order_by'];
        }
    }

//returns a sum of (opportunity count, weighted value and commit value) for the forecast.
    //committed by the logged in user's downline. This also inludes the user's direct forecast.
    public function compute_rollup_totals($order_by, $where, $currency_format = true)
    {

$query = $this->create_new_list_query($order_by, $where);
        $result = $this->db->query($query, true, 'Error fetching forecasts:');

$this->total_opp_count = 0;
        $this->total_weigh_value_number = 0;
        $this->total_likely_case_number = 0;
        $this->total_best_case_number = 0;
        $this->total_worst_case_number = 0;
        $this->pipeline_opp_count = 0;
        $this->pipeline_amount = 0;

while (($row = $this->db->fetchByAssoc($result)) != null) {
            $comm1 = new Common();
            $comm1->set_current_user($row['user_id']);
            $comm1->setup();
            if ($comm1->is_user_manager() && $row['user_id'] != $this->current_user_id) {
                $type = 'Rollup';
            } else {
                $type = 'Direct';
            }

$fquery = $this->create_forecast_query_for_user($row['user_id'], $type, $this->current_user_id, 'Rollup');

$fresult = $this->db->query($fquery, true, 'Error fetching forecasts:');
            $frow = $this->db->fetchByAssoc($fresult);
            if ($frow != null) {
                $this->total_opp_count += empty($frow['opp_count']) ? 0 : $frow['opp_count'];
                $this->total_weigh_value_number += empty($frow['opp_weigh_value']) ? 0 : $frow['opp_weigh_value'];
                $this->total_likely_case_number += empty($frow['likely_case']) ? 0 : $frow['likely_case'];
                $this->total_best_case_number += empty($frow['best_case']) ? 0 : $frow['best_case'];
                $this->total_worst_case_number += empty($frow['worst_case']) ? 0 : $frow['worst_case'];
                $this->pipeline_opp_count += empty($frow['pipeline_opp_count']) ? 0 : $frow['pipeline_opp_count'];
                $this->pipeline_amount += empty($frow['pipeline_amount']) ? 0 : $frow['pipeline_amount'];

if (empty($frow['worksheet_id'])) {
                    $this->total_wk_likely_case_number += empty($frow['likely_case']) ? 0 : $frow['likely_case'];
                    $this->total_wk_best_case_number += empty($frow['best_case']) ? 0 : $frow['best_case'];
                    $this->total_wk_worst_case_number += empty($frow['worst_case']) ? 0 : $frow['worst_case'];
                } else {
                    $this->total_wk_likely_case_number += empty($frow['wk_likely_case']) ? 0 : $frow['wk_likely_case'];
                    $this->total_wk_best_case_number += empty($frow['wk_best_case']) ? 0 : $frow['wk_best_case'];
                    $this->total_wk_worst_case_number += empty($frow['wk_worst_case']) ? 0 : $frow['wk_worst_case'];
                }
            }
        }
        if (empty($this->total_opp_count)) {
            $this->total_opp_count = 0;
        }
        if (empty($this->total_commit_value_number)) {
            $this->total_commit_value_number = 0;
        }
        if (empty($this->total_weigh_value_number)) {
            $this->total_weigh_value_number = 0;
        }
        if (empty($this->total_likely_case_number)) {
            $this->total_likely_case_number = 0;
        }
        if (empty($this->total_best_case_number)) {
            $this->total_best_case_number = 0;
        }
        if (empty($this->total_worst_case_number)) {
            $this->total_worst_case_number = 0;
        }
        if (empty($this->total_wk_likely_case_number)) {
            $this->total_wk_likely_case_number = 0;
        }
        if (empty($this->total_wk_best_case_number)) {
            $this->total_wk_best_case_number = 0;
        }
        if (empty($this->total_wk_worst_case_number)) {
            $this->total_wk_worst_case_number = 0;
        }

if ($currency_format) {
            //convert amounts to preferred currency
            $this->total_commit_value_number = $this->currency->convertFromDollar($this->total_commit_value_number);
            $this->total_weigh_value_number = $this->currency->convertFromDollar($this->total_weigh_value_number);
            $this->total_likely_case_number = $this->currency->convertFromDollar($this->total_likely_case_number);
            $this->total_best_case_number = $this->currency->convertFromDollar($this->total_best_case_number);
            $this->total_worst_case_number = $this->currency->convertFromDollar($this->total_worst_case_number);
            $this->total_wk_likely_case_number = $this->currency->convertFromDollar($this->total_wk_likely_case_number);
            $this->total_wk_best_case_number = $this->currency->convertFromDollar($this->total_wk_best_case_number);
            $this->total_wk_worst_case_number = $this->currency->convertFromDollar($this->total_wk_worst_case_number);

//format number and currency.
            $this->total_commit_value_number = $this->currency->symbol . format_number($this->total_commit_value_number, 0, 0);
            $this->total_weigh_value_number = $this->currency->symbol . format_number($this->total_weigh_value_number, 0, 0);
            $this->total_likely_case_number = $this->currency->symbol . format_number($this->total_likely_case_number, 0, 0);
            $this->total_best_case_number = $this->currency->symbol . format_number($this->total_best_case_number, 0, 0);
            $this->total_worst_case_number = $this->currency->symbol . format_number($this->total_worst_case_number, 0, 0);
        }
    }

/**
     * @deprecated Will be removed in 25.2.0
     * @param $this_forecast_type
     * @return array
     */
    public function get_last_committed_forecast($this_forecast_type = 'Rollup')
    {
        $GLOBALS['log']->fatal("Deprecated method get_last_committed_forecast called.");
        $last_committed = [];
        global $timedate;

$query = <<<SQL
            SELECT forecasts.best_case, forecasts.worst_case, forecasts.likely_case, forecasts.date_entered
            FROM forecasts
            WHERE forecasts.timeperiod_id = {$this->db->quoted($this->current_timeperiod_id)}
              AND forecasts.forecast_type = {$this->db->quoted($this_forecast_type)}
              AND forecasts.user_id = {$this->db->quoted($this->current_user_id)}
            ORDER BY forecasts.date_entered DESC
        SQL;

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

$last_committed['LIKELY_CASE'] = $this->currency->symbol . format_number($this->currency->convertFromDollar($last_committed['LIKELY_CASE']), 0, 0);
        $last_committed['BEST_CASE'] = $this->currency->symbol . format_number($this->currency->convertFromDollar($last_committed['BEST_CASE']), 0, 0);
        $last_committed['WORST_CASE'] = $this->currency->symbol . format_number($this->currency->convertFromDollar($last_committed['WORST_CASE']), 0, 0);

//print_r($last_committed);
        return $last_committed;
    }

/**
     * @deprecated Will be removed in 25.2.0
     * Returns quota amount given the user id and timeperiod id. Will return the Direct quota value.
     */
    public function get_quota()
    {
        $GLOBALS['log']->fatal("Deprecated method get_quota called.");
        $query = <<<SQL
            SELECT amount_base_currency 
            FROM quotas 
            WHERE deleted=0 
              AND user_id={$this->db->quoted($this->current_user_id)} 
              AND quota_type='Rollup' 
              AND timeperiod_id={$this->db->quoted($this->current_timeperiod_id)}
        SQL;
        $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);
        }
        return null;
    }
}