Monday, 11 February 2013

Compare Closed vs Won opportunities of selected timeframe

SugarCRM by default provides a Dashlet "My Closed Opportunities", which only gives you overview of Closed Won opportunity vs Total number of Opportunities and that too doesn't have a filter.

Lets create a new dashlet extending functionality a little more, where you can filter Opportunities by 'This month', 'This Year', etc. and give you in results "Closed Won" and "Closed Lost" numbers to easily compare.

Step 1:  Create folder custom/modules/Opportunities/Dashlets/MySummaryOpportunitiesDashlet.

Step 2: Create custom/modules/Opportunities/Dashlets/MySummaryOpportunitiesDashlet/MySummaryOpportunitiesDashlet.meta.php and write following code into it.

<?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
global $app_strings;
$dashletMeta['MySummaryOpportunitiesDashlet'] = array('module'        => 'Opportunities',
                                                      'title'       => translate('LBL_MY_SUMMARY_OPPORTUNITIES', 'Opportunities'),
                                                      'description' => 'Indicate Opportunity On Closed Won and Closed Lost Sales Stage',
                                                      'category'    => 'Module Views');

Step 3: Create custom/modules/Opportunities/Dashlets/MySummaryOpportunitiesDashlet/MySummaryOpportunitiesDashlet.php and write following code into it.

<?php

if (!defined('sugarEntry') || !sugarEntry)
    die('Not A Valid Entry Point');

require_once('include/Dashlets/Dashlet.php');

class MySummaryOpportunitiesDashlet extends Dashlet {

    var $height = '100'; // height of the pad
    var $opportunitySelectedRange = array();
    var $opportunityDateModifiedWhere = array();
    protected $total_opportunities;
    protected $total_opportunities_won;
    protected $total_opportunities_lost;

    /**
     * Constructor
     *
     * @global string current language
     * @param guid $id id for the current dashlet (assigned from Home module)
     * @param array $def options saved for this dashlet
     */
    function MySummaryOpportunitiesDashlet($id, $def) {
        global $current_user, $app_strings, $app_list_strings,$app_strings,$current_language;
        parent::Dashlet($id, $def);
        if (empty($def['title']))
            $this->title = translate('LBL_MY_SUMMARY_OPPORTUNITIES', 'Opportunities');
        if (!empty($def['height'])) // set a default height if none is set
            $this->height = $def['height'];

        parent::Dashlet($id); // call parent constructor

        $this->isConfigurable = true; // dashlet is configurable
        $this->hasScript = false;  // dashlet has javascript attached to it

        $selectedOpportunityDateOption = array();
        if (!empty($def['opportunity_date_range']))
            $selectedOpportunityDateOption = $def['opportunity_date_range'];
        $this->opportunityDateModifiedWhere = $selectedOpportunityDateOption;
        //$home_mod_strings = return_module_language($current_language, 'Home');
        $this->opportunitySelectedRange = $selectedOpportunityDateOption;
    }

    /**
     * Displays the dashlet
     *
     * @return string html to display dashlet
     */
    function display() {

        require_once('modules/Administration/Administration.php');
        $admin = new Administration();
        $admin->retrieveSettings();

        global $db, $current_language, $timedate,$current_user;
        $mod_strings = return_module_language($current_language, 'Opportunities');
        $ss = new Sugar_Smarty();

        $opportunitySearchWhere = "";
        if (!empty($this->opportunityDateModifiedWhere)) {
            if($this->opportunityDateModifiedWhere == "TP_today")
            {
                $oSugarDateTime = new SugarDateTime();
                $begin = $oSugarDateTime->get_day_begin();
                $end = $oSugarDateTime->get_day_end();
                $opportunitySearchWhere .= " AND opportunities.date_modified >='".$begin->asDb()."' AND opportunities.date_modified <= '".$end->asDb()."'";
            }
            else if($this->opportunityDateModifiedWhere == "TP_yesterday")
            {
                $oSugarDateTime = new SugarDateTime();
                $begin = $oSugarDateTime->get("-1 day")->get_day_begin();
                $end = $oSugarDateTime->get("-1 day")->get_day_end();
                $opportunitySearchWhere .= " AND opportunities.date_modified >='".$begin->asDb()."' AND opportunities.date_modified <= '".$end->asDb()."'";
            }
            else if($this->opportunityDateModifiedWhere == "TP_tomorrow")
            {
                $oSugarDateTime = new SugarDateTime();
                $begin = $oSugarDateTime->get("+1 day")->get_day_begin();
                $end = $oSugarDateTime->get("+1 day")->get_day_end();
                $opportunitySearchWhere .= " AND opportunities.date_modified >='".$begin->asDb()."' AND opportunities.date_modified <= '".$end->asDb()."'";
            }
            else if($this->opportunityDateModifiedWhere == "TP_this_month")
            {
                $oSugarDateTime = new SugarDateTime();
                $begin = $oSugarDateTime->get_day_by_index_this_month(0)->setTime(0, 0, 0);
                $end = clone($begin);
                $end->setDate($begin->year, $begin->month, $begin->days_in_month)->setTime(23, 59, 59);
                $opportunitySearchWhere .= " AND opportunities.date_modified >='".$begin->asDb()."' AND opportunities.date_modified <= '".$end->asDb()."'";
            }
            else if($this->opportunityDateModifiedWhere == "TP_this_year")
            {
                $oSugarDateTime = new SugarDateTime();
                $begin = $oSugarDateTime->setDate($oSugarDateTime->year, 1, 1)->setTime(0, 0, 0);
                $end = clone($begin);
                $end->setDate($begin->year, 12, 31)->setTime(23, 59, 59);
                $opportunitySearchWhere .= " AND opportunities.date_modified >='".$begin->asDb()."' AND opportunities.date_modified <= '".$end->asDb()."'";
            }
            else if($this->opportunityDateModifiedWhere == "TP_last_30_days")
            {
                $oSugarDateTime = new SugarDateTime();
                $begin = $oSugarDateTime->get("-29 days")->get_day_begin();
                $end = $oSugarDateTime->get_day_end();
                $opportunitySearchWhere .= " AND opportunities.date_modified >='".$begin->asDb()."' AND opportunities.date_modified <= '".$end->asDb()."'";
            }
            else if($this->opportunityDateModifiedWhere == "TP_last_7_days")
            {
                $oSugarDateTime = new SugarDateTime();
                $begin = $oSugarDateTime->get("-6 days")->get_day_begin();
                $end = $oSugarDateTime->get_day_end();
                $opportunitySearchWhere .= " AND opportunities.date_modified >='".$begin->asDb()."' AND opportunities.date_modified <= '".$end->asDb()."'";
            }
            else if($this->opportunityDateModifiedWhere == "TP_last_month")
            {
                $oSugarDateTime = new SugarDateTime();
                $begin = $oSugarDateTime->setDate($oSugarDateTime->year, $oSugarDateTime->month-1, 1)->setTime(0, 0, 0);
                $end = clone($begin);
                $end->setDate($begin->year, $begin->month, $begin->days_in_month)->setTime(23, 59, 59);
                $opportunitySearchWhere .= " AND opportunities.date_modified >='".$begin->asDb()."' AND opportunities.date_modified <= '".$end->asDb()."'";
            }
            else if($this->opportunityDateModifiedWhere == "TP_last_year")
            {
                $oSugarDateTime = new SugarDateTime();
                $begin = $oSugarDateTime->setDate($oSugarDateTime->year-1, 1, 1)->setTime(0, 0, 0);
                $end = clone($begin);
                $end->setDate($begin->year, 12, 31)->setTime(23, 59, 59);
                $opportunitySearchWhere .= " AND opportunities.date_modified >='".$begin->asDb()."' AND opportunities.date_modified <= '".$end->asDb()."'";
            }
            else if($this->opportunityDateModifiedWhere == "TP_next_30_days")
            {
                $oSugarDateTime = new SugarDateTime();
                $begin = $oSugarDateTime->get_day_begin();
                $end = $oSugarDateTime->get("+29 days")->get_day_end();
                $opportunitySearchWhere .= " AND opportunities.date_modified >='".$begin->asDb()."' AND opportunities.date_modified <= '".$end->asDb()."'";
            }
            else if($this->opportunityDateModifiedWhere == "TP_next_7_days")
            {
                $oSugarDateTime = new SugarDateTime();
                $begin = $oSugarDateTime->get_day_begin();
                $end = $oSugarDateTime->get("+6 days")->get_day_end();
                $opportunitySearchWhere .= " AND opportunities.date_modified >='".$begin->asDb()."' AND opportunities.date_modified <= '".$end->asDb()."'";
            }
            else if($this->opportunityDateModifiedWhere == "TP_next_month")
            {
                $oSugarDateTime = new SugarDateTime();
                $begin = $oSugarDateTime->setDate($oSugarDateTime->year, $oSugarDateTime->month+1, 1)->setTime(0, 0, 0);
                $end = clone($begin);
                $end->setDate($begin->year, $begin->month, $begin->days_in_month)->setTime(23, 59, 59);
                $opportunitySearchWhere .= " AND opportunities.date_modified >='".$begin->asDb()."' AND opportunities.date_modified <= '".$end->asDb()."'";
            }
            else if($this->opportunityDateModifiedWhere == "TP_next_year")
            {
                $oSugarDateTime = new SugarDateTime();
                $begin = $oSugarDateTime->setDate($oSugarDateTime->year+1, 1, 1)->setTime(0, 0, 0);
                $end = clone($begin);
                $end->setDate($begin->year, 12, 31)->setTime(23, 59, 59);
                $opportunitySearchWhere .= " AND opportunities.date_modified >='".$begin->asDb()."' AND opportunities.date_modified <= '".$end->asDb()."'";
            }
        }

        $this->seedBean = new Opportunity();

        $qry = "SELECT * from opportunities WHERE assigned_user_id = '" . $current_user->id . "' AND deleted=0".$opportunitySearchWhere;
        $result = $this->seedBean->db->query($this->seedBean->create_list_count_query($qry));
        $row = $this->seedBean->db->fetchByAssoc($result);

        $this->total_opportunities = $row['c'];
        $qry = "SELECT * from opportunities WHERE assigned_user_id = '" . $current_user->id . "' AND sales_stage = 'Closed Won'  AND deleted=0".$opportunitySearchWhere;
        $result = $this->seedBean->db->query($this->seedBean->create_list_count_query($qry));
        $row = $this->seedBean->db->fetchByAssoc($result);

        $this->total_opportunities_won = $row['c'];

        $qry = "SELECT * from opportunities WHERE assigned_user_id = '" . $current_user->id . "' AND sales_stage = 'Closed LOST'  AND deleted=0".$opportunitySearchWhere;
        $result = $this->seedBean->db->query($this->seedBean->create_list_count_query($qry));
        $row = $this->seedBean->db->fetchByAssoc($result);

        $this->total_opportunities_lost = $row['c'];


        $ss->assign('id', $this->id);
        $ss->assign('height', $this->height);
        $ss->assign('opportunitySelectedRange', $this->opportunitySelectedRange);
        $ss->assign('lblTotalOpportunities', translate('LBL_TOTAL_OPPORTUNITIES', 'Opportunities'));
        $ss->assign('lblClosedWonOpportunities', translate('LBL_CLOSED_WON_OPPORTUNITIES', 'Opportunities'));
        $ss->assign('lblClosedLostOpportunities', translate('LBL_CLOSED_LOST_OPPORTUNITIES', 'Opportunities'));
        $ss->assign('total_opportunities', $this->total_opportunities);
        $ss->assign('total_opportunities_won', $this->total_opportunities_won);
        $ss->assign('total_opportunities_lost', $this->total_opportunities_lost);
        $str = $ss->fetch('custom/modules/Opportunities/Dashlets/MySummaryOpportunitiesDashlet/MySummaryOpportunitiesDashlet.tpl');
        return parent::display($this->dashletStrings['LBL_DBLCLICK_HELP']) . $str; // return parent::display for title and such
    }

    function displayOptions() {
        global $app_strings, $app_list_strings,$current_user,$current_language;
        $ss = new Sugar_Smarty();
        $ss->assign('saveLbl', $app_strings['LBL_SAVE_BUTTON_LABEL']);
        $ss->assign('clearLbl', $app_strings['LBL_SAVE_BUTTON_LABEL']);
        $ss->assign('title', translate('LBL_MY_SUMMARY_OPPORTUNITIES', 'Opportunities'));
        $ss->assign('id', $this->id);

        $home_mod_strings = return_module_language($current_language, 'Home');
        $filterTypes = array(''                 => $app_strings['LBL_NONE'],
                             'TP_today'         => $home_mod_strings['LBL_TODAY'],
                             'TP_yesterday'     => $home_mod_strings['LBL_YESTERDAY'],
                             'TP_tomorrow'      => $home_mod_strings['LBL_TOMORROW'],
                             'TP_this_month'    => $home_mod_strings['LBL_THIS_MONTH'],
                             'TP_this_year'     => $home_mod_strings['LBL_THIS_YEAR'],
                             'TP_last_30_days'  => $home_mod_strings['LBL_LAST_30_DAYS'],
                             'TP_last_7_days'   => $home_mod_strings['LBL_LAST_7_DAYS'],
                             'TP_last_month'    => $home_mod_strings['LBL_LAST_MONTH'],
                             'TP_last_year'     => $home_mod_strings['LBL_LAST_YEAR'],
                             'TP_next_30_days'  => $home_mod_strings['LBL_NEXT_30_DAYS'],
                             'TP_next_7_days'   => $home_mod_strings['LBL_NEXT_7_DAYS'],
                             'TP_next_month'    => $home_mod_strings['LBL_NEXT_MONTH'],
                             'TP_next_year'     => $home_mod_strings['LBL_NEXT_YEAR'],
                             );

        $ss->assign('opportunitySelectedRange', get_select_options_with_id($filterTypes, $this->opportunitySelectedRange));
        $ss->assign('opportunityDateModified', $app_strings['LBL_DATE_MODIFIED']);

        return parent::displayOptions() . $ss->fetch('custom/modules/Opportunities/Dashlets/MySummaryOpportunitiesDashlet/MySummaryOpportunitiesDashletOptions.tpl');
    }

    function saveOptions($req) {
        global $sugar_config, $timedate, $current_user, $theme;
        $options = array();
        $options['opportunity_date_range'] = $_REQUEST['opportunity_date_range'];
        return $options;
    }

}

Step 4:  Create custom/modules/Opportunities/Dashlets/MySummaryOpportunitiesDashlet/MySummaryOpportunitiesDashlet.tpl and write following code into it.

<div style="width:100%;vertical-align:middle;">
<table width="100%" border="0" align="center" class="list view" cellspacing="0" cellpadding="0">
    <tr>
        <th  align="center">{$lblTotalOpportunities}</td>
        <th  align="center">{$lblClosedWonOpportunities}</td>
        <th  align="center">{$lblClosedLostOpportunities}</td>
    </tr>
    <tr class="oddListRowS1">
        <td valign="top">{$total_opportunities}</td>
        <td valign="top"><b>{$total_opportunities_won}</b></td>
        <td valign="top"><b>{$total_opportunities_lost}</b></td>
    </tr>
</table>
</div>

Step 5:  Create custom/modules/Opportunities/Dashlets/MySummaryOpportunitiesDashlet/MySummaryOpportunitiesDashletOptions.tpl and write following code into it

<div>
<form name='configure_{$id}' action="index.php" method="post" onSubmit='return SUGAR.dashlets.postForm("configure_{$id}", SUGAR.mySugar.uncoverPage);'>
<input type='hidden' name='id' value='{$id}'>
<input type='hidden' name='module' value='Home'>
<input type='hidden' name='action' value='ConfigureDashlet'>
<input type='hidden' name='to_pdf' value='true'>
<input type='hidden' name='configure' value='true'>
<table width="100%" cellpadding="0" cellspacing="0" border="0" class="edit view" align="center">
<tr>
    <td valign='top' nowrap class='dataLabel'>{$opportunityDateModified}</td>
    <td valign='top' class='dataField'>
        <select name="opportunity_date_range">{$opportunitySelectedRange}</select>
    </td>
    <td valign='top' nowrap class='dataLabel'>&nbsp;</td>
    <td valign='top' class='dataField'>&nbsp;</td>
</tr>
<tr>
    <td colspan='4' align='right'>
        <input type='submit' class='button' value='{$saveLbl}'>
        <input type="submit" onclick="SUGAR.searchForm.clear_form(this.form,['dashletTitle','displayRows','autoRefresh']);return false;" value="Clear" class="button">
       </td>
</tr>
</table>
</form>
</div>

Step 6: Create custom/Extension/modules/Opportunities/Ext/Language/en_us.<any_name>.php and write the following custom label into it.

$mod_strings['LBL_MY_SUMMARY_OPPORTUNITIES'] = "Summary Opportunities";
$mod_strings['LBL_CLOSED_LOST_OPPORTUNITIES'] = "Closed Lost Opportunities ";

Step 7: Do "Quick Repair And Rebuild" from admin.

Step 8: Add new dashlet on Home page titled as "Summary Opportunities".

Voila!! Hope this works out as easily as possible for you.

Feel free to drop your valuable comments.

No comments:

Post a Comment

Urdhva Tech Pvt. Ltd. Powered by Blogger.

Urdhva Tech

My photo
SugarCRM Consultants Contact us at contact@urdhva-tech.com Skype : urdhvatech

Search This Blog

Stay connected Socially

     

Total Pageviews

Popular Posts