//START NEW PERIODIC DASHBOARD

    public function lat_multi_get_scorecard_count($module_name, $date_field = "", $dom_field = "", $dom_name = "", $getall, $assigned_user_id, $user_wise_enable)
    {
        //$GLOBALS['log']->fatal("----------------------------------------------------------------------------".$module_name);
        global $app_list_strings, $sugar_config, $current_user, $timedate, $beanList, $beanFiles;

        $class_name = $beanList[$module_name];
        require_once $beanFiles[$class_name];
        $seed = new $class_name();

        $record_array      = array();
        $where             = array();
        $return_date_where = array();
        $return_dom_where  = array();
        $default_where     = $this->lat_default_where($module_name);
        if (!empty($default_where)) {
            $where[] = $default_where;
        }

        if ($getall == "0" || $module_name == 'SG_Out_Mail' || $module_name == "SG_Out_Mail_Recipient") {
            if ($assigned_user_id == 'null') {
                //$where[] = strtolower($module_name) . ".assigned_user_id IS NULL";
                $where[] = $seed->table_name . ".assigned_user_id IS NULL";
            } else {
                //$where[] = ($assigned_user_id == "0") ? strtolower($module_name) . ".assigned_user_id = '$current_user->id'" : strtolower($module_name) . ".assigned_user_id = '$assigned_user_id'";
                $where[] = ($assigned_user_id == "0") ? $seed->table_name . ".assigned_user_id = '$current_user->id'" : $seed->table_name . ".assigned_user_id = '$assigned_user_id'";
            }
        }
        $where[] = "deleted = 0";

        // $GLOBALS['log']->fatal("/**********DateWise Field Count Start**********/");
        if (!empty($date_field) && $date_field !== "") {
            //$temp_date_field = ($this->endsWith($date_field, '_c') == 1) ? $date_field : strtolower($module_name) . ".$date_field";
            $temp_date_field = ($this->endsWith($date_field, '_c') == 1) ? $date_field : $seed->table_name . ".$date_field";
            $user_date       = new DateTime($timedate->getNow());            
            $user_date->setTimezone(new DateTimeZone($current_user->getPreference('timezone')));
            $bean                = BeanFactory::newBean($module_name);
            $bean_field_defs = $bean->field_defs[$date_field];
            if($bean_field_defs['type'] == "date"){
                $actual_user_date = $user_date->format('Y-m-d');
            }
            else{
                $actual_user_date_1 = $user_date->format('Y-m-d');
                $actual_user_date = $this->convert_to_gmt($actual_user_date_1." 00:00:00");
            }
            //$GLOBALS['log']->fatal("Assigned UserWise ".$actual_user_date);    
            $date_field_array["over_due"]["label"]    = "Over Due";
            $date_field_array["over_due"]["color"]    = "#ff4c4c";
            $date_field_array["today"]["label"]       = "Today";
            $date_field_array["today"]["color"]       = "#4ca64c";
            $date_field_array["tomorrow"]["label"]    = "Tomorrow";
            $date_field_array["tomorrow"]["color"]    = "#ffc107";
            $date_field_array["upcoming"]["label"]    = "Upcoming";
            $date_field_array["upcoming"]["color"]    = "#607d8d";
            $date_field_array["empty_dates"]["label"] = "Empty Dates";
            $date_field_array["empty_dates"]["color"] = "#536dfe";
            //$date_field_array = array("today" => array("label" => "Today", "color" => "#4ca64c"), "tomorrow" => array("label" => "Tomorrow", "color" => "#ffc107"), "over_due" => array("label" => "Over Due", "color" => "#ff4c4c"), "upcoming" => array("label" => "Upcoming", "color" => "#607d8d"), "empty_dates" => array("label" => "Empty Dates", "color" => "#536dfe"));

            foreach ($date_field_array as $date_field_key => $date_field_label) {
                $date_where = array();
                switch ($date_field_key) {
                    case 'over_due':
                        $date_where[] = "DATE($temp_date_field) < DATE('$actual_user_date')";
                        break;
                    case 'today':
                        $date_where[] = "DATE($temp_date_field) = DATE('$actual_user_date')";
                        break;
                    case 'tomorrow':
                        $date_where[] = "DATE($temp_date_field) = DATE('$actual_user_date') + INTERVAL 1 DAY";
                        break;
                    case 'upcoming':
                        $date_where[] = "DATE($temp_date_field) > DATE('$actual_user_date') + INTERVAL 1 DAY";
                        break;
                    case 'empty_dates':
                        $date_where[] = "(DATE($temp_date_field) IN('') OR DATE($temp_date_field) IS NULL)";
                        break;
                    default:
                        break;
                }
                $sql                                = $this->generate_select($seed, array_merge($where, $date_where));                
                $sql_final[$date_field_key]         = $sql;
                $return_date_where[$date_field_key] = explode('WHERE ', $sql)[1];
            }

            // $GLOBALS['log']->fatal("DateWise WHERE : " . print_r($sql_final, true));
            $output = ' SELECT (' . implode(', (', array_map(
                function ($v, $k) {return sprintf("%s) AS `%s`", $v, $k);},
                $sql_final,
                array_keys($sql_final)
            ));
            $res           = $GLOBALS['db']->query($output);
            $row           = $GLOBALS['db']->fetchByAssoc($res);
            $final_ret_row = array();
            foreach ($date_field_array as $date_field_key => $date_field_label) {
                if ($row[$date_field_key] != 0) {
                    $final_ret_row[] = array("count" => $row[$date_field_key], "key" => $date_field_key, "label" => $date_field_label["label"], "color" => $date_field_label["color"], "query" => $return_date_where[$date_field_key]);
                }
            }
            $record_array['primary_grouping'] = $this->replace_null_to_blank($final_ret_row);
            // $GLOBALS['log']->fatal("DateWise ROW : " . print_r($row, true));
        }
        // $GLOBALS['log']->fatal("/**********DateWise Field Count End**********/");

        $sql_final                = array();
        $secondary_grouping_array = array();
        $field_array              = array();

        //MSB
        $domfield = explode(",", $dom_field);
        foreach ($domfield as $key => $value) {

            $dom_field      = $value;
            $module_bean    = BeanFactory::newBean($module_name);
            $dom_name       = $module_bean->field_defs[$dom_field]['options'];
            $dom_name_value = $module_bean->field_defs[$dom_field]['vname'];

            // $GLOBALS['log']->fatal("IF OUT  " . $dom_name . "<>" . $dom_field . "<> " . print_r($dom_name_value, true));
            if (!empty($dom_field) && !empty($dom_name)) {

                $dom_field_array = $app_list_strings[$dom_name];

                if ($dom_name == 'reminder_time_options') {
                    $dom_field_array['-1'] = '-1';
                }

                if (!empty($dom_field_array)) {
                    $dom_field_array['NULL'] = 'NULL';
                }

                // $GLOBALS['log']->fatal("DOM VALUES");
                // $GLOBALS['log']->fatal("--------------------------------");
                foreach ($dom_field_array as $dom_key => $dom_value) {
                    //$GLOBALS['log']->fatal("$dom_key : $dom_value");
                    $dom_stat    = ($dom_key == "NULL") ? "$dom_field IS NULL" : "$dom_field = '$dom_key'";
                    $dom_where   = array();
                    //$dom_where[] = ($this->endsWith($dom_field, '_c') == 1) ? $dom_stat : strtolower($module_name) . ".$dom_stat";
                    $dom_where[] = ($this->endsWith($dom_field, '_c') == 1) ? $dom_stat : $seed->table_name . ".$dom_stat";
                    // $GLOBALS['log']->fatal(print_r($where, true) . "<>" . print_r($dom_where, true));

                    $sql                        = $this->generate_select($seed, array_merge($where, $dom_where));
                    $sql_final[$dom_key]        = $sql;
                    $return_dom_where[$dom_key] = explode('WHERE ', $sql)[1];
                }
                // $GLOBALS['log']->fatal("--------------------------------" . print_r($return_dom_where, true));

                // $GLOBALS['log']->fatal("DomWise WHERE : " . print_r($sql_final, true));
                $output = ' SELECT (' . implode(', (', array_map(
                    function ($v, $k) {return sprintf("%s) AS `%s`", $v, $k);},
                    $sql_final,
                    array_keys($sql_final)
                ));
                $res12 = $GLOBALS['db']->query($output);
                $row12 = $GLOBALS['db']->fetchByAssoc($res12);
                //$GLOBALS['log']->fatal("DomWise ROW : " . print_r($row12, true));
                $final_ret_row = array();
                foreach ($dom_field_array as $dom_field_key => $dom_field_label) {
                    // $GLOBALS['log']->fatal("MSBdom - " . $dom_field_key);
                    if ($row12[$dom_field_key] != 0) {
                        $final_ret_row[] = array("count" => $row12[$dom_field_key], "key" => $dom_field_key, "label" => $dom_field_label, "query" => $return_dom_where[$dom_field_key]);
                    }

                }

                //$field_label[] = array($dom_field => translate($dom_name_value, $module_name));
                $field_label   = translate($dom_name_value, $module_name);
                $field_array[] = array("key" => $dom_field, "label" => rtrim($field_label, ":"), "data" => $this->replace_null_to_blank($final_ret_row));
                // $record_array['secondary_grouping']['label']    = $field_label;
                // $record_array['secondary_grouping'][$dom_field] = $this->replace_null_to_blank($final_ret_row);
            }

        }
        $record_array['secondary_grouping'] = $field_array;
        //MSB
        // $GLOBALS['log']->fatal("/**********DomWise Field Count End**********/");

        if ($getall != "0" && $user_wise_enable == "1") {
            // $GLOBALS['log']->fatal("/**********Assigned UserWise Count Start**********/");
            $record_array['user_grouping'] = $this->lat_get_scorecard_assigned_user_count($seed, $where);
            // $GLOBALS['log']->fatal("/**********Assigned UserWise Count End**********/");
        }
        // $GLOBALS['log']->fatal("----------------------------------------------------------------------------");
        return $record_array;

    }

    public function convert_to_gmt($time)
    {
        global $app_list_strings, $sugar_config, $current_user, $timedate, $beanList, $beanFiles;

        $userTimezone = new DateTimeZone($current_user->getPreference('timezone'));        
        $myDateTime = new DateTime(date($time));
        $offset = $userTimezone->getOffset($myDateTime) * -1;
        $myInterval=DateInterval::createFromDateString((string)$offset . 'seconds');
        $myDateTime->add($myInterval);
        return $myDateTime->format('Y-m-d H:i:s'); 

    }

    public function periodic_get_scorecard_count($module_name, $date_field = "", $dom_field = "", $dom_name = "", $getall, $assigned_user_id, $user_wise_enable, $start_date = "", $end_date = "")
    {
        //$GLOBALS['log']->fatal("startdate>>".$start_date. " end_date>>".$end_date);        
        global $app_list_strings, $sugar_config, $current_user, $timedate, $beanList, $beanFiles, $mod_strings;

        $class_name = $beanList[$module_name];
        require_once $beanFiles[$class_name];
        $seed = new $class_name();

        $record_array      = array();
        $where             = array();
        $return_date_where = array();
        $return_dom_where  = array();
        $default_where     = $this->periodic_default_where($module_name);
        if (!empty($default_where)) {
            $where[] = $default_where;
        }

        //for start date
        /*$start_user_date = new DateTime($start_date." H:i:s");
        $start_user_date->setTimezone(new DateTimeZone($current_user->getPreference('timezone')));
        $start_actual_user_date = $start_user_date->format('Y-m-d H:i:s');*/
        $start_actual_user_date = $this->convert_to_gmt($start_date." 00:00:00");        
        $end_actual_user_date = $this->convert_to_gmt($end_date." 23:59:59");        

        //$GLOBALS['log']->fatal($start_date."startdate>>".$start_actual_user_date."  end_date>>".$end_actual_user_date);
        if ($getall == "0" || $module_name == 'SG_Out_Mail' || $module_name == "SG_Out_Mail_Recipient") {
            if ($assigned_user_id == 'null') {
                //$where[] = strtolower($module_name) . ".assigned_user_id IS NULL";
                $where[] = $seed->table_name . ".assigned_user_id IS NULL";
            } else {
                //$where[] = ($assigned_user_id == "0") ? strtolower($module_name) . ".assigned_user_id = '$current_user->id'" : strtolower($module_name) . ".assigned_user_id = '$assigned_user_id'";
                $where[] = ($assigned_user_id == "0") ? $seed->table_name . ".assigned_user_id = '$current_user->id'" : $seed->table_name . ".assigned_user_id = '$assigned_user_id'";
            }
        }
        if (!empty($start_date) && !empty($end_date)) {
            //$lmod    = strtolower($module_name);
            $lmod = $seed->table_name;
            $where[] = "(" . $lmod . ".date_entered between '" . $start_actual_user_date . "' AND '" . $end_actual_user_date . "')";
        }
        $where[] = "deleted = 0";

        // $GLOBALS['log']->fatal("/**********DateWise Field Count Start**********/");
        if (!empty($date_field) && $date_field !== "") {
            //$temp_date_field = ($this->endsWith($date_field, '_c') == 1) ? $date_field : strtolower($module_name) . ".$date_field";
            $temp_date_field = ($this->endsWith($date_field, '_c') == 1) ? $date_field : $seed->table_name . ".$date_field";
            $user_date       = new DateTime($timedate->getNow());
            $user_date->setTimezone(new DateTimeZone($current_user->getPreference('timezone')));
            $actual_user_date = $user_date->format('Y-m-d');

            $date_field_array["over_due"]["label"]    = "Over Due";
            $date_field_array["over_due"]["color"]    = "#ff4c4c";
            $date_field_array["today"]["label"]       = "Today";
            $date_field_array["today"]["color"]       = "#4ca64c";
            $date_field_array["tomorrow"]["label"]    = "Tomorrow";
            $date_field_array["tomorrow"]["color"]    = "#ffc107";
            $date_field_array["upcoming"]["label"]    = "Upcoming";
            $date_field_array["upcoming"]["color"]    = "#607d8d";
            $date_field_array["empty_dates"]["label"] = "Empty Dates";
            $date_field_array["empty_dates"]["color"] = "#536dfe";

            //$date_field_array = array("today" => array("label" => "Today", "color" => "#4ca64c"), "tomorrow" => array("label" => "Tomorrow", "color" => "#ffc107"), "over_due" => array("label" => "Over Due", "color" => "#ff4c4c"), "upcoming" => array("label" => "Upcoming", "color" => "#607d8d"), "empty_dates" => array("label" => "Empty Dates", "color" => "#536dfe"));

            foreach ($date_field_array as $date_field_key => $date_field_label) {
                $date_where = array();
                switch ($date_field_key) {
                    case 'over_due':
                        $date_where[] = "DATE($temp_date_field) < DATE('$actual_user_date')";
                        break;
                    case 'today':
                        $date_where[] = "DATE($temp_date_field) = DATE('$actual_user_date')";
                        break;
                    case 'tomorrow':
                        $date_where[] = "DATE($temp_date_field) = DATE('$actual_user_date') + INTERVAL 1 DAY";
                        break;
                    case 'upcoming':
                        $date_where[] = "DATE($temp_date_field) > DATE('$actual_user_date') + INTERVAL 1 DAY";
                        break;
                    case 'empty_dates':
                        $date_where[] = "(DATE($temp_date_field) IN('') OR DATE($temp_date_field) IS NULL)";
                        break;
                    default:
                        break;
                }
                $sql                                = $this->generate_select($seed, array_merge($where, $date_where));
                $sql_final[$date_field_key]         = $sql;
                $return_date_where[$date_field_key] = explode('WHERE ', $sql)[1];
            }

            //$GLOBALS['log']->fatal("DateWise WHERE : " . print_r($sql_final, true));

            $output = ' SELECT (' . implode(', (', array_map(
                function ($v, $k) {return sprintf("%s) AS `%s`", $v, $k);},
                $sql_final,
                array_keys($sql_final)
            ));
            $res           = $GLOBALS['db']->query($output);
            $row           = $GLOBALS['db']->fetchByAssoc($res);
            $final_ret_row = array();
            foreach ($date_field_array as $date_field_key => $date_field_label) {
                if ($row[$date_field_key] != 0) {
                    $final_ret_row[] = array("count" => $row[$date_field_key], "key" => $date_field_key, "label" => $date_field_label["label"], "color" => $date_field_label["color"], "query" => $return_date_where[$date_field_key]);
                }
            }
            $record_array['primary_grouping'] = $this->replace_null_to_blank($final_ret_row);
            // $GLOBALS['log']->fatal("DateWise ROW : " . print_r($row, true));
        }
        // $GLOBALS['log']->fatal("/**********DateWise Field Count End**********/");

        $sql_final = array();

        // $GLOBALS['log']->fatal("/**********DomWise Field Count Start**********/");
        $secondary_grouping_array = array();
        $field_array              = array();

        //MSB
        $domfield = explode(",", $dom_field);
        foreach ($domfield as $key => $value) {

            $dom_field      = $value;
            $module_bean    = BeanFactory::newBean($module_name);
            $dom_name       = $module_bean->field_defs[$dom_field]['options'];
            $dom_name_value = $module_bean->field_defs[$dom_field]['vname'];

            // $GLOBALS['log']->fatal("IF OUT  " . $dom_name . "<>" . $dom_field . "<> " . print_r($dom_name_value, true));
            if (!empty($dom_field) && !empty($dom_name)) {

                $dom_field_array = $app_list_strings[$dom_name];

                if ($dom_name == 'reminder_time_options') {
                    $dom_field_array['-1'] = '-1';
                }

                if (!empty($dom_field_array)) {
                    $dom_field_array['NULL'] = 'NULL';
                }

                // $GLOBALS['log']->fatal("DOM VALUES");
                // $GLOBALS['log']->fatal("--------------------------------");
                foreach ($dom_field_array as $dom_key => $dom_value) {
                    //$GLOBALS['log']->fatal("$dom_key : $dom_value");
                    $dom_stat    = ($dom_key == "NULL") ? "$dom_field IS NULL" : "$dom_field = '$dom_key'";
                    $dom_where   = array();
                    $dom_where[] = ($this->endsWith($dom_field, '_c') == 1) ? $dom_stat : $seed->table_name . ".$dom_stat";
                    // $GLOBALS['log']->fatal(print_r($where, true) . "<>" . print_r($dom_where, true));

                    $sql                        = $this->generate_select($seed, array_merge($where, $dom_where));
                    $sql_final[$dom_key]        = $sql;
                    $return_dom_where[$dom_key] = explode('WHERE ', $sql)[1];
                }
                // $GLOBALS['log']->fatal("--------------------------------" . print_r($return_dom_where, true));

                // $GLOBALS['log']->fatal("DomWise WHERE : " . print_r($sql_final, true));
                $output = ' SELECT (' . implode(', (', array_map(
                    function ($v, $k) {return sprintf("%s) AS `%s`", $v, $k);},
                    $sql_final,
                    array_keys($sql_final)
                ));
                $res12 = $GLOBALS['db']->query($output);
                $row12 = $GLOBALS['db']->fetchByAssoc($res12);
                //$GLOBALS['log']->fatal("DomWise ROW : " . print_r($row12, true));
                $final_ret_row = array();
                foreach ($dom_field_array as $dom_field_key => $dom_field_label) {
                    // $GLOBALS['log']->fatal("MSBdom - " . $dom_field_key);
                    if ($row12[$dom_field_key] != 0) {
                        $final_ret_row[] = array("count" => $row12[$dom_field_key], "key" => $dom_field_key, "label" => $dom_field_label, "query" => $return_dom_where[$dom_field_key]);
                    }

                }

                //$field_label[] = array($dom_field => translate($dom_name_value, $module_name));

                $field_label = translate($dom_name_value, $module_name);

                $field_array[] = array("key" => $dom_field, "label" => rtrim($field_label, ":"), "data" => $this->replace_null_to_blank($final_ret_row));
                // $record_array['secondary_grouping']['label']    = $field_label;
                // $record_array['secondary_grouping'][$dom_field] = $this->replace_null_to_blank($final_ret_row);
            }

        }
        $record_array['secondary_grouping'] = $field_array;
        //MSB
        // $GLOBALS['log']->fatal("/**********DomWise Field Count End**********/");

        if ($getall != "0" && $user_wise_enable == "1") {
            // $GLOBALS['log']->fatal("/**********Assigned UserWise Count Start**********/");
            $record_array['user_grouping'] = $this->lat_get_scorecard_assigned_user_count($seed, $where);
            // $GLOBALS['log']->fatal("/**********Assigned UserWise Count End**********/");
        }
        // $GLOBALS['log']->fatal("----------------------------------------------------------------------------");
        return $record_array;
    }

    public function periodic_get_dashboard_count($module_name, $field_name = "", $getall, $start_date = "", $end_date = "")
    {
        // $GLOBALS['log']->fatal("----------------------------------------------------------------------------");
        // $error = new SoapError(); //creating default error object
        global $app_list_strings, $sugar_config, $current_user, $timedate, $beanList, $beanFiles;
                
        $start_actual_user_date = $this->convert_to_gmt($start_date." 00:00:00");        
        $end_actual_user_date = $this->convert_to_gmt($end_date." 23:59:59");        

        $class_name = $beanList[$module_name];
        require_once $beanFiles[$class_name];
        $seed = new $class_name();

        $where         = array();
        $default_where = $this->periodic_default_where($module_name);
        if (!empty($default_where)) {
            $where[] = $default_where;
        }    
        
        if ($getall == "0" || $module_name == 'SG_Out_Mail' || $module_name == "SG_Out_Mail_Recipient") {
            //$where[] = strtolower($module_name) . ".assigned_user_id = '$current_user->id'";
            $where[] = $seed->table_name . ".assigned_user_id = '$current_user->id'";
        }
        if (!empty($start_actual_user_date) && !empty($end_actual_user_date)) {
            //$lmod    = strtolower($module_name);
            $lmod    = $seed->table_name;            
            $query_strings = ($module_name=="Calls") ? '.date_start' : '.date_entered';
            $where[] = "(" . $lmod .$query_strings." between '" . $start_actual_user_date . "' AND '" . $end_actual_user_date . "')";
        }
        $where[] = "deleted = 0";        
        //die(print_r($where, true));    

        /*Calls are not shown in today's activity instead the default count for calls shows for today's only*/
        // $GLOBALS['log']->fatal("/**********Individual Module Count Start**********/");
        $return['default_count']       = $this->lat_query_db($module_name, $seed, $where);
        $return['default_count_query'] = (!empty($where)) ? implode(' AND ', $where) : "";
        // $GLOBALS['log']->fatal("/**********Individual Module Count End**********/");

        /*todays_activity*/
        if ($field_name !== "") {
            // $GLOBALS['log']->fatal("/**********Todays Activity Count Start**********/");
            $user_date = new DateTime($timedate->getNow());
            $user_date->setTimezone(new DateTimeZone($current_user->getPreference('timezone')));
            $actual_user_date = $user_date->format('Y-m-d');

            // if ($module_name != "Calls") {
            //$where[] = ($this->endsWith($field_name, '_c') == 1) ? "DATE($field_name) = DATE('$actual_user_date')" : "DATE(" . strtolower($module_name) . ".$field_name) = DATE('$actual_user_date')";
            $where[] = ($this->endsWith($field_name, '_c') == 1) ? "DATE($field_name) = DATE('$actual_user_date')" : "DATE(" . $seed->tab . ".$field_name) = DATE('$actual_user_date')";
            //}

            //MMM
            // if ($module_name == "Calls") {
            //     $return['default_count']   = $this->lat_query_db($module_name, $seed, $where);
            //     $return['todays_activity'] = "0";
            // } else {
            $return['todays_activity'] = $this->lat_query_db($module_name, $seed, $where);
            // }

            // if ($module_name == "Calls") {
            //     $where[]                         = "(enjay_log_type_c = 'call' OR enjay_log_type_c = 'sms')";
            //     $return['default_count_query']   = (!empty($where)) ? implode(' AND ', $where) : "";
            //     $return['todays_activity_query'] = "";
            // } else {
            $return['todays_activity_query'] = (!empty($where)) ? implode(' AND ', $where) : "";
            // }

            // $GLOBALS['log']->fatal("/**********Todays Activity Count End**********/");
        } else {
            $return['todays_activity']       = "0";
            $return['todays_activity_query'] = "";
        }
        /*todays_activity*/
        //$return['module_label'] = ($module_name == "Calls") ? "Today's " . $app_list_strings['moduleList'][$module_name] : $app_list_strings['moduleList'][$module_name];
        $return['module_label'] = $app_list_strings['moduleList'][$module_name];
        $return['module_key']   = $module_name;

        // $GLOBALS['log']->fatal("return : " . print_r($return, true));
        // $GLOBALS['log']->fatal("----------------------------------------------------------------------------");
        //return
        return $this->replace_null_to_blank($return);
    }

    public function periodic_get_scorecard_count_call($module_name, $date_field = "", $getall, $assigned_user_id, $user_wise_enable, $start_date = "", $end_date = "")
    {
        // $GLOBALS['log']->fatal("----------------------------------------------------------------------------");
        // $GLOBALS['log']->fatal("/**********Calls Scorecard Start**********/");
        global $sugar_config, $current_user, $timedate, $beanList, $beanFiles;
        $return_calls_where = array();
        $class_name         = $beanList[$module_name];
        require_once $beanFiles[$class_name];
        $seed = new $class_name();

        $where[] = $this->lat_default_where($module_name);

        $calls_label_array["in_held"]["label"]      = "Held";
        $calls_label_array["in_held"]["color"]      = "#4ca64c";
        $calls_label_array["in_not_held"]["label"]  = "Not Held";
        $calls_label_array["in_not_held"]["color"]  = "#ff4c4c";
        $calls_label_array["out_held"]["label"]     = "Held";
        $calls_label_array["out_held"]["color"]     = "#4ca64c";
        $calls_label_array["out_not_held"]["label"] = "Not Held";
        $calls_label_array["out_not_held"]["color"] = "#ff4c4c";
        $calls_label_array["in_sms"]["label"]       = "SMS";
        $calls_label_array["in_sms"]["color"]       = "#607d8d";
        $calls_label_array["out_sms"]["label"]      = "SMS";
        $calls_label_array["out_sms"]["color"]      = "#607d8d";
        $calls_label_array["in_sms_wa"]["label"]    = "WhatsApp";
        $calls_label_array["in_sms_wa"]["color"]    = "#4dc85c";
        $calls_label_array["out_sms_wa"]["label"]   = "WhatsApp";
        $calls_label_array["out_sms_wa"]["color"]   = "#4dc85c";
        //$calls_label_array = array("in_held" => array("label"=>"Held", "color"=>"#4ca64c"), "in_not_held" => array("label"=>"Not Held", "color"=>"#ff4c4c"), "out_held" => array("label"=>"Held", "color"=>"#4ca64c"), "out_not_held" => array("label"=>"Not Held", "color"=>"#ff4c4c"), "in_sms" => array("label"=>"SMS", "color"=>"#607d8d"), "out_sms" => array("label"=>"SMS", "color"=>"#607d8d"));

        $start_actual_user_date = $this->convert_to_gmt($start_date." 00:00:00");
        $end_actual_user_date = $this->convert_to_gmt($end_date." 23:59:59");
        if ($getall == "0") {
            if ($assigned_user_id == 'null') {
                $where[] = $seed->table_name . ".assigned_user_id IS NULL";
            } else {
                $where[] = ($assigned_user_id == "0") ? $seed->table_name . ".assigned_user_id = '$current_user->id'" : $seed->table_name . ".assigned_user_id = '$assigned_user_id'";
            }

        }
        if (!empty($start_date) && !empty($end_date)) {
                //$lmod    = strtolower($module_name);
                $lmod = $seed->table_name;
                $where[] = "(" . $lmod . ".date_start between '" . $start_actual_user_date . " ' AND '" . $end_actual_user_date . " ')";
            }

        $where[] = "deleted = 0";
        foreach ($calls_label_array as $condition_key => $condition_name) {
            $calls_where = array();
            switch ($condition_key) {
                case 'in_held':
                    $calls_where[] = "(calls.status = 'Held' AND calls.direction = 'Inbound' AND enjay_log_type_c = 'call')";
                    break;
                case 'in_not_held':
                    $calls_where[] = "(calls.status = 'Not Held' AND calls.direction = 'Inbound' AND enjay_log_type_c = 'call')";
                    break;
                case 'out_held':
                    $calls_where[] = "(calls.status = 'Held' AND calls.direction = 'Outbound' AND enjay_log_type_c = 'call')";
                    break;
                case 'out_not_held':
                    $calls_where[] = "(calls.status = 'Not Held' AND calls.direction = 'Outbound' AND enjay_log_type_c = 'call')";
                    break;
                case 'in_sms':
                    $calls_where[] = "(calls.direction = 'Inbound' AND enjay_log_type_c = 'sms')";
                    break;
                case 'out_sms':
                    $calls_where[] = "(calls.direction = 'Outbound' AND enjay_log_type_c = 'sms')";
                    break;
                case 'in_sms_wa':
                    $calls_where[] = "(calls.direction = 'Inbound' AND enjay_log_type_c = 'whatsapp')";
                    break;
                case 'out_sms_wa':
                    $calls_where[] = "(calls.direction = 'Outbound' AND enjay_log_type_c = 'whatsapp')";
                    break;
                default:
                    break;                    
            }
            $sql                                = $this->generate_select($seed, array_merge($where, $calls_where));
            $sql_final[$condition_key]          = $sql;
            $return_calls_where[$condition_key] = explode('WHERE ', $sql)[1];
        }

        // $GLOBALS['log']->fatal("SQLFINAL : " . print_r($sql_final, true));
        $output = ' SELECT (' . implode(', (', array_map(
            function ($v, $k) {return sprintf("%s) AS `%s`", $v, $k);},
            $sql_final,
            array_keys($sql_final)
        ));
        // $GLOBALS['log']->fatal("SQLFINAL : " . print_r($output, true));
        $res = $GLOBALS['db']->query($output);
        $row = $GLOBALS['db']->fetchByAssoc($res);
        // $GLOBALS['log']->fatal("Calls ROW : " . print_r($row, true));
        $final_ret_row = array();
        foreach ($calls_label_array as $condition_key => $condition_name) {
            if (in_array($condition_key, array("in_held", "in_not_held", "in_sms","in_sms_wa"))) {
                $final_ret_row['primary_grouping'][] = array("count" => $row[$condition_key], "key" => $condition_key, "label" => $condition_name["label"], "color" => $condition_name["color"], "query" => $return_calls_where[$condition_key]);
            } else {
                $final_ret_row['secondary_grouping'][] = array("count" => $row[$condition_key], "key" => $condition_key, "label" => $condition_name["label"], "color" => $condition_name["color"], "query" => $return_calls_where[$condition_key]);
            }
        }

        if ($getall != "0" && $user_wise_enable == "1") {
            // $GLOBALS['log']->fatal("/**********Assigned UserWise Count Start**********/");
            $where[]                        = "(enjay_log_type_c = 'call' OR enjay_log_type_c = 'sms')";
            $final_ret_row['user_grouping'] = $this->lat_get_scorecard_assigned_user_count($seed, $where);
            // $GLOBALS['log']->fatal("/**********Assigned UserWise Count End**********/");
        }

        // $GLOBALS['log']->fatal("/**********Calls Scorecard End**********/");
        // $GLOBALS['log']->fatal("----------------------------------------------------------------------------");
        return $final_ret_row;
    }    

    public function periodic_default_where($module_name)
    {
        switch ($module_name) {
            // case 'Bugs':
            //     $where = strtolower($module_name) . ".status NOT IN ('Closed', 'Rejected')";
            //     break;
            case 'Calls':
                $where = strtolower($module_name) . ".status IN ('Held', 'Not Held')";
                break;
            // case 'Cases':
            //     $where = strtolower($module_name) . ".status NOT IN ('Closed')";
            //     break;
            // // case 'Leads':
            // //     $where = strtolower($module_name) . ".status NOT IN ('Dead', 'Converted', 'Recycled')";
            // //     break;
            // case 'Meetings':
            //     $where = strtolower($module_name) . ".status IN ('Planned', 'Not Held')";
            //     break;
            // case 'Opportunities':
            //     $where = strtolower($module_name) . ".sales_stage NOT IN ('Closed Lost', 'Closed Won')";
            //     break;
            // case 'Tasks':
            //     $where = strtolower($module_name) . ".status NOT IN ('Completed')";
            //     break;
            // case 'Project':
            //     $where = strtolower($module_name) . ".status NOT IN ('Completed')";
            //     break;
            default:
                break;
        }
        return $where;
    }

    //END NEW PERIODIC DASHBOARD