function getTopAccounts($maxval, $calCnt)
{
$log = LoggerManager::getLogger('top accounts_list');
$log->debug("Entering getTopAccounts() method ...");
require_once "data/Tracker.php";
require_once 'modules/Potentials/Potentials.php';
require_once 'include/logging.php';
require_once 'include/ListView/ListView.php';
global $app_strings;
global $adb;
global $current_language;
global $current_user;
$current_module_strings = return_module_language($current_language, "Accounts");
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
require 'user_privileges/sharing_privileges_' . $current_user->id . '.php';
$list_query = "select vtiger_account.accountid, vtiger_account.accountname, vtiger_account.tickersymbol, sum(vtiger_potential.amount) as amount from vtiger_potential inner join vtiger_crmentity on (vtiger_potential.potentialid=vtiger_crmentity.crmid) left join vtiger_account on (vtiger_potential.related_to=vtiger_account.accountid) left join vtiger_groups on (vtiger_groups.groupid = vtiger_crmentity.smownerid) where vtiger_crmentity.deleted=0 AND vtiger_crmentity.smownerid='" . $current_user->id . "' and vtiger_potential.sales_stage not in ('Closed Won', 'Closed Lost','" . $app_strings['LBL_CLOSE_WON'] . "','" . $app_strings['LBL_CLOSE_LOST'] . "')";
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[6] == 3) {
$sec_parameter = getListViewSecurityParameter('Accounts');
$list_query .= $sec_parameter;
}
$list_query .= " group by vtiger_account.accountid, vtiger_account.accountname, vtiger_account.tickersymbol order by amount desc";
$list_query .= " LIMIT 0," . $adb->sql_escape_string($maxval);
if ($calCnt == 'calculateCnt') {
$list_result_rows = $adb->query(mkCountQuery($list_query));
return $adb->query_result($list_result_rows, 0, 'count');
}
$list_result = $adb->query($list_query);
$open_accounts_list = array();
$noofrows = $adb->num_rows($list_result);
if ($noofrows) {
for ($i = 0; $i < $noofrows; $i++) {
$open_accounts_list[] = array('accountid' => $adb->query_result($list_result, $i, 'accountid'), 'accountname' => $adb->query_result($list_result, $i, 'accountname'), 'amount' => $adb->query_result($list_result, $i, 'amount'), 'tickersymbol' => $adb->query_result($list_result, $i, 'tickersymbol'));
}
}
$title = array();
$title[] = 'myTopAccounts.gif';
$title[] = $current_module_strings['LBL_TOP_ACCOUNTS'];
$title[] = 'home_myaccount';
$header = array();
$header[] = $current_module_strings['LBL_LIST_ACCOUNT_NAME'];
$currencyid = fetchCurrency($current_user->id);
$rate_symbol = getCurrencySymbolandCRate($currencyid);
$rate = $rate_symbol['rate'];
$curr_symbol = $rate_symbol['symbol'];
$header[] = $current_module_strings['LBL_LIST_AMOUNT'] . '(' . $curr_symbol . ')';
$entries = array();
foreach ($open_accounts_list as $account) {
$value = array();
$account_fields = array('ACCOUNT_ID' => $account['accountid'], 'ACCOUNT_NAME' => $account['accountname'], 'AMOUNT' => $account['amount']);
$Top_Accounts = strlen($account['accountname']) > 20 ? substr($account['accountname'], 0, 20) . '...' : $account['accountname'];
$value[] = '<a href="index.php?action=DetailView&module=Accounts&record=' . $account['accountid'] . '">' . $Top_Accounts . '</a>';
$value[] = convertFromDollar($account['amount'], $rate);
$entries[$account['accountid']] = $value;
}
$values = array('ModuleName' => 'Accounts', 'Title' => $title, 'Header' => $header, 'Entries' => $entries);
$log->debug("Exiting getTopAccounts method ...");
if ($display_empty_home_blocks && count($entries) == 0 || count($entries) > 0) {
return $values;
}
}
/** Function to export the notes in CSV Format
* @param reference variable - order by is passed when the query is executed
* @param reference variable - where condition is passed when the query is executed
* Returns Export Relsettings Query.
*/
function create_export_query(&$order_by, &$where)
{
global $log;
$log->debug("Entering create_export_query(" . $order_by . "," . $where . ") method ...");
include "include/utils/ExportUtils.php";
//To get the Permitted fields query and the permitted fields list
$module = "Relsettings";
$sql = getPermittedFieldsQuery($module, "detail_view");
global $mod_strings;
global $current_language;
if (empty($mod_strings)) {
$mod_strings = return_module_language($current_language, "Relsettings");
}
$fields_list = $this->getFieldsListFromQuery($sql, $mod_strings);
$query = "SELECT {$fields_list} FROM ec_relsettings\n\t\t\t\tLEFT JOIN ec_users\n\t\t\t\t\tON ec_relsettings.smownerid = ec_users.id\n\t\t\t\tLEFT JOIN ec_users as ua\n\t\t\t\t\tON ec_relsettings.approvedby = ua.id\n\t\t\t\tLEFT JOIN ec_users as ucreator\n\t\t\t\t\tON ec_relsettings.smcreatorid = ucreator.id\n\t\t\t\tLEFT JOIN ec_approvestatus ON ec_relsettings.approved = ec_approvestatus.statusid ";
$query .= " left join ec_account ON ec_relsettings.accountid=ec_account.accountid ";
$query .= " left join ec_contactdetails ON ec_relsettings.contact_id=ec_contactdetails.contactid ";
$query .= " left join ec_potential ON ec_relsettings.potentialid=ec_potential.potentialid ";
$query .= " left join ec_salesorder ON ec_relsettings.salesorderid=ec_salesorder.salesorderid ";
$query .= " left join ec_vendor ON ec_vendor.vendorid=ec_relsettings.vendorid ";
$query .= " left join ec_purchaseorder ON ec_purchaseorder.purchaseorderid=ec_relsettings.purchaseorderid ";
$query_rel = "SELECT ec_entityname.* FROM ec_crmentityrel inner join ec_entityname on ec_entityname.modulename=ec_crmentityrel.relmodule WHERE ec_crmentityrel.module='" . $module . "'";
$fldmod_result = $this->db->query($query_rel);
$rownum = $this->db->num_rows($fldmod_result);
for ($i = 0; $i < $rownum; $i++) {
$rel_modulename = $this->db->query_result($fldmod_result, $i, 'modulename');
$rel_tablename = $this->db->query_result($fldmod_result, $i, 'tablename');
$rel_entityname = $this->db->query_result($fldmod_result, $i, 'fieldname');
$rel_entityid = $this->db->query_result($fldmod_result, $i, 'entityidfield');
$query .= " left join " . $rel_tablename . " ON ec_relsettings." . $rel_entityid . "=" . $rel_tablename . "." . $rel_entityid;
}
$where_auto = " ec_relsettings.deleted = 0 ";
if ($where != "") {
$query .= " WHERE ({$where}) AND " . $where_auto;
} else {
$query .= " WHERE " . $where_auto;
}
$tab_id = getTabid($module);
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3 || isset($_REQUEST['viewscope']) || isset($_SESSION[$module . '_viewscope'])) {
$sec_parameter = getListViewSecurityParameter($module, $isSearchAll);
$query .= $sec_parameter;
}
$log->debug("Exiting create_export_query method ...");
return $query;
}
/** To get security parameter for a particular module -- By Pavani*/
function getSecParameterforMerge($module)
{
global $current_user;
$tab_id = getTabid($module);
$sec_parameter = "";
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
require 'user_privileges/sharing_privileges_' . $current_user->id . '.php';
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
if ($module == "Accounts") {
$sec_parameter .= " AND (vtiger_crmentity.smownerid IN (" . $current_user->id . ")\n\t\t\t\t\tOR vtiger_crmentity.smownerid IN (\n\t\t\t\t\tSELECT vtiger_user2role.userid\n\t\t\t\t\tFROM vtiger_user2role\n\t\t\t\t\tINNER JOIN vtiger_users ON vtiger_users.id = vtiger_user2role.userid\n\t\t\t\t\tINNER JOIN vtiger_role ON vtiger_role.roleid = vtiger_user2role.roleid\n\t\t\t\t\tWHERE vtiger_role.parentrole LIKE '" . $current_user_parent_role_seq . "::%')\n\t\t\t\t\tOR vtiger_crmentity.smownerid IN (\n\t\t\t\t\tSELECT shareduserid\n\t\t\t\t\tFROM vtiger_tmp_read_user_sharing_per\n\t\t\t\t\tWHERE userid=" . $current_user->id . "\n\t\t\t\t\tAND tabid=" . $tab_id . ")\n\t\t\t\t\tOR (vtiger_crmentity.smownerid in (0)\n\t\t\t\t\tAND (";
if (sizeof($current_user_groups) > 0) {
$sec_parameter .= " vtiger_groups.groupname IN (\n\t\t\t\t\t\t\t\tSELECT groupname\n\t\t\t\t\t\t\t\tFROM vtiger_groups\n\t\t\t\t\t\t\t\tWHERE groupid IN (" . implode(",", getCurrentUserGroupList()) . ")) OR ";
}
$sec_parameter .= " vtiger_groups.groupname IN (\n\t\t\t\tSELECT vtiger_groups.groupname\n\t\t\t\tFROM vtiger_tmp_read_group_sharing_per\n\t\t\t\tINNER JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_tmp_read_group_sharing_per.sharedgroupid\n\t\t\t\tWHERE userid=" . $current_user->id . " AND tabid=" . $tab_id . ")))) ";
}
}
return $sec_parameter;
}
/**
* Function to get todos list scheduled between specified dates
* @param array $calendar - collection of objects and strings
* @param string $start_date - date string
* @param string $end_date - date string
* @param string $info - string 'listcnt' or empty string. if 'listcnt' means it returns no. of todos and no. of pending todos in array format else it returns todos list in array format
* return array $Entries - todolists in array format
*/
function getTodoList(&$calendar, $start_date, $end_date, $info = '')
{
global $log, $app_strings, $theme;
$Entries = array();
$category = getParentTab();
global $adb, $current_user, $mod_strings, $cal_log, $list_max_entries_per_page;
$cal_log->debug("Entering getTodoList() method...");
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
require 'user_privileges/sharing_privileges_' . $current_user->id . '.php';
$count_qry = "SELECT count(*) as count FROM vtiger_activity\n\t\tINNER JOIN vtiger_crmentity\n\t\tON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\tLEFT JOIN vtiger_cntactivityrel\n\t\tON vtiger_cntactivityrel.activityid = vtiger_activity.activityid\n\t\tLEFT JOIN vtiger_groups\n\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\tLEFT JOIN vtiger_users\n\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n\t\tWHERE vtiger_crmentity.deleted = 0\n\t\tAND vtiger_activity.activitytype = 'Task'\n\t\tAND (vtiger_activity.date_start BETWEEN ? AND ?) AND vtiger_crmentity.smownerid = " . $current_user->id;
$query = "SELECT vtiger_groups.groupname, vtiger_users.user_name, vtiger_crmentity.crmid, vtiger_cntactivityrel.contactid, \n\t\t\t\tvtiger_activity.* FROM vtiger_activity\n INNER JOIN vtiger_crmentity\n\t\t\t\t\tON vtiger_crmentity.crmid = vtiger_activity.activityid\n LEFT JOIN vtiger_cntactivityrel\n\t\t\t\t\tON vtiger_cntactivityrel.activityid = vtiger_activity.activityid\n\t\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\tLEFT JOIN vtiger_users\n\t\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n WHERE vtiger_crmentity.deleted = 0\n\t\t\t\t\tAND vtiger_activity.activitytype = 'Task'\n\t\t\t\t\tAND (vtiger_activity.date_start BETWEEN ? AND ?) AND vtiger_crmentity.smownerid = " . $current_user->id;
$list_query = $query;
// User Select Customization
/*$only_for_user = calendarview_getSelectedUserId();
if($only_for_user != 'ALL') {
$query .= " AND vtiger_crmentity.smownerid = " . $only_for_user;
$count_qry .= " AND vtiger_crmentity.smownerid = " . $only_for_user;
}*/
// END
$params = $info_params = array($start_date, $end_date);
if ($info != '') {
//added to fix #4816
$groupids = explode(",", fetchUserGroupids($current_user->id));
if (count($groupids) > 0) {
$com_q = " AND (vtiger_crmentity.smownerid = ?\n\t\t\t\t\tOR vtiger_groups.groupid in (" . generateQuestionMarks($groupids) . "))";
} else {
$com_q = " AND vtiger_crmentity.smownerid = ?";
}
//end
$pending_query = $query . " AND (vtiger_activity.status != 'Completed')" . $com_q;
$total_q = $query . "" . $com_q;
array_push($info_params, $current_user->id);
if (count($groupids) > 0) {
array_push($info_params, $groupids);
}
if ($adb->dbType == "pgsql") {
$pending_query = fixPostgresQuery($pending_query, $log, 0);
$total_q = fixPostgresQuery($total_q, $log, 0);
}
$total_res = $adb->pquery($total_q, $info_params);
$total = $adb->num_rows($total_res);
$res = $adb->pquery($pending_query, $info_params);
$pending_rows = $adb->num_rows($res);
$cal_log->debug("Exiting getTodoList() method...");
return array('totaltodo' => $total, 'pendingtodo' => $pending_rows);
}
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[9] == 3) {
$sec_parameter = getListViewSecurityParameter('Calendar');
$query .= $sec_parameter;
$list_query .= $sec_parameter;
$count_qry .= $sec_parameter;
}
$group_cond = '';
$count_res = $adb->pquery($count_qry, $params);
$total_rec_count = $adb->query_result($count_res, 0, 'count');
$group_cond .= " ORDER BY vtiger_activity.date_start,vtiger_activity.time_start ASC";
if (isset($_REQUEST['start']) && $_REQUEST['start'] != '') {
$start = vtlib_purify($_REQUEST['start']);
} else {
$start = 1;
}
$navigation_array = getNavigationValues($start, $total_rec_count, $list_max_entries_per_page);
$start_rec = $navigation_array['start'];
$end_rec = $navigation_array['end_val'];
if ($start_rec <= 1) {
$start_rec = 0;
} else {
$start_rec = $start_rec - 1;
}
$query .= $group_cond . " limit {$start_rec},{$list_max_entries_per_page}";
$list_query .= $group_cond;
if ($adb->dbType == "pgsql") {
$query = fixPostgresQuery($query, $log, 0);
$list_query = fixPostgresQuery($list_query, $log, 0);
}
$list_query = $adb->convert2Sql($list_query, $params);
$_SESSION['Calendar_listquery'] = $list_query;
$result = $adb->pquery($query, $params);
$rows = $adb->num_rows($result);
$c = 0;
if ($start > 1) {
$c = ($start - 1) * $list_max_entries_per_page;
}
for ($i = 0; $i < $rows; $i++) {
$element = array();
$contact_name = '';
$element['no'] = $c + 1;
$more_link = "";
$start_time = $adb->query_result($result, $i, "time_start");
$format = $calendar['calendar']->hour_format;
$value = getaddEventPopupTime($start_time, $start_time, $format);
$element['starttime'] = $value['starthour'] . ':' . $value['startmin'] . '' . $value['startfmt'];
$date_start = $adb->query_result($result, $i, "date_start");
$due_date = $adb->query_result($result, $i, "due_date");
if ($calendar['view'] != 'day') {
$element['startdate'] = getDisplayDate($date_start);
}
$element['duedate'] = getDisplayDate($due_date);
$id = $adb->query_result($result, $i, "activityid");
$subject = $adb->query_result($result, $i, "subject");
//CHANGE : TO IMPROVE PERFORMANCE
/*$contact_id = $adb->query_result($result,$i,"contactid");
if($contact_id!='')
{
$contact_name = getContactName($contact_id);
}*/
$more_link = "<a href='index.php?action=DetailView&module=Calendar&record=" . $id . "&activity_mode=Task&viewtype=calendar&parenttab=" . $category . "' class='webMnu'>" . $subject . "</a>";
$element['tododetail'] = $more_link;
/*if(getFieldVisibilityPermission('Calendar',$current_user->id,'parent_id') == '0')
{
$element['task_relatedto'] = getRelatedTo('Calendar',$result,$i);
}
if(getFieldVisibilityPermission('Calendar',$current_user->id,'contact_id') == '0')
{
$element['task_contact'] = "<a href=\"index.php?module=Contacts&action=DetailView&record=".$contact_id."\">".$contact_name."</a>";
}*/
if (getFieldVisibilityPermission('Calendar', $current_user->id, 'taskstatus') == '0') {
$taskstatus = $adb->query_result($result, $i, "status");
if (!$is_admin && $taskstatus != '') {
$roleid = $current_user->roleid;
$roleids = array();
$subrole = getRoleSubordinates($roleid);
if (count($subrole) > 0) {
$roleids = $subrole;
}
array_push($roleids, $roleid);
//here we are checking wheather the table contains the sortorder column .If sortorder is present in the main picklist table, then the role2picklist will be applicable for this table...
$sql = "select * from vtiger_taskstatus where taskstatus=?";
$res = $adb->pquery($sql, array(decode_html($taskstatus)));
$picklistvalueid = $adb->query_result($res, 0, 'picklist_valueid');
if ($picklistvalueid != null) {
$pick_query = "select * from vtiger_role2picklist where picklistvalueid={$picklistvalueid} and roleid in (" . generateQuestionMarks($roleids) . ")";
$res_val = $adb->pquery($pick_query, array($roleids));
$num_val = $adb->num_rows($res_val);
}
if ($num_val > 0) {
$element['status'] = getTranslatedString(decode_html($taskstatus));
} else {
$element['status'] = "<font color='red'>" . $app_strings['LBL_NOT_ACCESSIBLE'] . "</font>";
}
} else {
$element['status'] = getTranslatedString(decode_html($taskstatus));
}
}
if (isPermitted("Calendar", "EditView") == "yes" || isPermitted("Calendar", "Delete") == "yes") {
$element['action'] = "<img onClick='getcalAction(this,\"taskcalAction\"," . $id . ",\"" . $calendar['view'] . "\",\"" . $calendar['calendar']->date_time->hour . "\",\"" . $calendar['calendar']->date_time->get_formatted_date() . "\",\"todo\");' src='" . vtiger_imageurl('cal_event.jpg', $theme) . "' border='0'>";
}
$assignedto = $adb->query_result($result, $i, "user_name");
if (!empty($assignedto)) {
$element['assignedto'] = $assignedto;
} else {
$element['assignedto'] = $adb->query_result($result, $i, "groupname");
}
$c++;
$Entries[] = $element;
}
$ret_arr[0] = $Entries;
$ret_arr[1] = $navigation_array;
$cal_log->debug("Exiting getTodoList() method...");
return $ret_arr;
}
/** Function to get related list entries in detailed array format
* @param $parentmodule -- parentmodulename:: Type string
* @param $query -- query:: Type string
* @param $id -- id:: Type string
* @returns $return_data -- return data:: Type string array
*/
function getHistory($parentmodule, $query, $id)
{
global $log;
$log->debug("Entering getHistory(" . $parentmodule . "," . $query . "," . $id . ") method ...");
$parentaction = vtlib_purify($_REQUEST['action']);
global $theme;
$theme_path = "themes/" . $theme . "/";
$image_path = $theme_path . "images/";
global $adb;
global $mod_strings;
global $app_strings;
//Appending the security parameter
global $current_user;
$rel_tab_id = getTabid("Calendar");
global $current_user;
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
require 'user_privileges/sharing_privileges_' . $current_user->id . '.php';
$tab_id = getTabid('Calendar');
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter('Calendar');
$query .= ' ' . $sec_parameter;
}
$query .= ' ' . "ORDER BY vtiger_activity.date_start DESC,vtiger_activity.time_start DESC";
$result = $adb->query($query);
$noofrows = $adb->num_rows($result);
if ($noofrows == 0) {
//There is no entries for history
} else {
//Form the header columns
$header[] = $app_strings['LBL_TYPE'];
$header[] = $app_strings['LBL_SUBJECT'];
$header[] = $app_strings['LBL_RELATED_TO'];
$header[] = $app_strings['LBL_START_DATE'] . " & " . $app_strings['LBL_TIME'];
$header[] = $app_strings['LBL_END_DATE'] . " & " . $app_strings['LBL_TIME'];
//$header[] = $app_strings['LBL_DESCRIPTION'];
$header[] = $app_strings['LBL_STATUS'];
$header[] = $app_strings['LBL_ASSIGNED_TO'];
$i = 1;
while ($row = $adb->fetch_array($result)) {
$entries = array();
if ($row['activitytype'] == 'Task') {
$activitymode = 'Task';
$icon = 'Tasks.gif';
$status = $row['status'];
$status = $app_strings[$status];
} else {
$activitymode = 'Events';
$icon = 'Activities.gif';
$status = $row['eventstatus'];
$status = $app_strings[$status];
}
$typeofactivity = $row['activitytype'];
$typeofactivity = getTranslatedString($typeofactivity, 'Calendar');
$entries[] = $typeofactivity;
$activity = '<a href="index.php?module=Calendar4You&action=EventDetailView&return_module=' . $parentmodule . '&return_action=DetailView&record=' . $row["activityid"] . '&activity_mode=' . $activitymode . '&return_id=' . vtlib_purify($_REQUEST['record']) . '&parenttab=' . vtlib_purify($_REQUEST['parenttab']) . '">' . $row['subject'] . '</a></td>';
$entries[] = $activity;
$parentname = getRelatedTo('Calendar', $result, $i - 1);
$entries[] = $parentname;
$date = new DateTimeField($row['date_start'] . ' ' . $row['time_start']);
$entries[] = $date->getDisplayDateTimeValue();
$date = new DateTimeField($row['due_date'] . ' ' . $row['time_end']);
$entries[] = $date->getDisplayDate();
$entries[] = $status;
if ($row['user_name'] == null && $row['groupname'] != null) {
$entries[] = $row['groupname'];
} else {
$entries[] = $row['user_name'];
}
$i++;
$entries_list[] = $entries;
}
$return_data = array('header' => $header, 'entries' => $entries_list);
$log->debug("Exiting getHistory method ...");
return $return_data;
}
}
/** Function to get the Contacts assigned to a user with a valid email address.
* @param varchar $username - User Name
* @param varchar $emailaddress - Email Addr for each contact.
* Used By vtigerCRM Outlook Plugin
* Returns the Query
*/
function get_searchbyemailid($username, $emailaddress)
{
global $log;
global $current_user;
require_once "modules/Users/Users.php";
$seed_user = new Users();
$user_id = $seed_user->retrieve_user_id($username);
$current_user = $seed_user;
$current_user->retrieve_entity_info($user_id, 'Users');
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
require 'user_privileges/sharing_privileges_' . $current_user->id . '.php';
$log->debug("Entering get_searchbyemailid(" . $username . "," . $emailaddress . ") method ...");
$query = "select vtiger_contactdetails.lastname,vtiger_contactdetails.firstname,\n\t\t\t\t\tvtiger_contactdetails.contactid, vtiger_contactdetails.salutation, \n\t\t\t\t\tvtiger_contactdetails.email,vtiger_contactdetails.title,\n\t\t\t\t\tvtiger_contactdetails.mobile,vtiger_account.accountname,\n\t\t\t\t\tvtiger_account.accountid as accountid from vtiger_contactdetails \n\t\t\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_contactdetails.contactid \n\t\t\t\t\t\tinner join vtiger_users on vtiger_users.id=vtiger_crmentity.smownerid \n\t\t\t\t\t\tleft join vtiger_account on vtiger_account.accountid=vtiger_contactdetails.accountid \n\t\t\t\t\t\tleft join vtiger_contactaddress on vtiger_contactaddress.contactaddressid=vtiger_contactdetails.contactid\n\t\t\t LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t where vtiger_crmentity.deleted=0";
if (trim($emailaddress) != '') {
$query .= " and ((vtiger_contactdetails.email like '" . formatForSqlLike($emailaddress) . "') or vtiger_contactdetails.lastname REGEXP REPLACE('" . $emailaddress . "',' ','|') or vtiger_contactdetails.firstname REGEXP REPLACE('" . $emailaddress . "',' ','|')) and vtiger_contactdetails.email != ''";
} else {
$query .= " and (vtiger_contactdetails.email like '" . formatForSqlLike($emailaddress) . "' and vtiger_contactdetails.email != '')";
}
$tab_id = getTabid("Contacts");
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter("Contacts");
$query .= $sec_parameter;
}
$log->debug("Exiting get_searchbyemailid method ...");
return $this->plugin_process_list_query($query);
}
/** Function to export the notes in CSV Format
* @param reference variable - where condition is passed when the query is executed
* Returns Export Documents Query.
*/
function create_export_query($where)
{
global $log, $current_user;
$log->debug("Entering create_export_query(" . $where . ") method ...");
include "include/utils/ExportUtils.php";
//To get the Permitted fields query and the permitted fields list
$sql = getPermittedFieldsQuery("Documents", "detail_view");
$fields_list = getFieldsListFromQuery($sql);
$query = "SELECT {$fields_list}, case when (vtiger_users.user_name not like '') then vtiger_users.user_name else vtiger_groups.groupname end as user_name" . " FROM vtiger_notes\n\t\t\t\tinner join vtiger_crmentity \n\t\t\t\t\ton vtiger_crmentity.crmid=vtiger_notes.notesid \n\t\t\t\tLEFT JOIN vtiger_attachmentsfolder on vtiger_notes.folderid=vtiger_attachmentsfolder.folderid\n\t\t\t\tLEFT JOIN vtiger_users ON vtiger_crmentity.smownerid=vtiger_users.id " . " LEFT JOIN vtiger_groups ON vtiger_crmentity.smownerid=vtiger_groups.groupid ";
$where_auto = " vtiger_crmentity.deleted=0";
if ($where != "") {
$query .= " WHERE ({$where}) AND " . $where_auto;
} else {
$query .= " WHERE " . $where_auto;
}
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
require 'user_privileges/sharing_privileges_' . $current_user->id . '.php';
//we should add security check when the user has Private Access
$tabid = getTabid("Documents");
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tabid] == 3) {
//Added security check to get the permitted records only
$query = $query . " " . getListViewSecurityParameter("Documents");
}
$log->debug("Exiting create_export_query method ...");
return $query;
}
/**
* Function to get Pending/Upcoming activities
* @param integer $mode - number to differentiate upcoming and pending activities
* return array $values - activities record in array format
*/
function getPendingActivities($mode, $view = '')
{
global $log;
$log->debug("Entering getPendingActivities() method ...");
require_once 'data/Tracker.php';
require_once 'include/utils/utils.php';
require_once 'user_privileges/default_module_view.php';
global $currentModule;
global $singlepane_view;
global $theme;
global $focus;
global $action;
global $adb;
global $app_strings;
global $current_language;
global $current_user;
$current_module_strings = return_module_language($current_language, 'Calendar');
$theme_path = "themes/" . $theme . "/";
$image_path = $theme_path . "images/";
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
require 'user_privileges/sharing_privileges_' . $current_user->id . '.php';
if ($_REQUEST['activity_view'] == '') {
$activity_view = 'today';
} else {
$activity_view = vtlib_purify($_REQUEST['activity_view']);
}
$today = date("Y-m-d", time());
if ($view == 'today') {
$upcoming_condition = " AND (date_start = '{$today}' OR vtiger_recurringevents.recurringdate = '{$today}')";
$pending_condition = " AND (due_date = '{$today}' OR vtiger_recurringevents.recurringdate = '{$today}')";
} else {
if ($view == 'all') {
$upcoming_condition = " AND (date_start >= '{$today}' OR vtiger_recurringevents.recurringdate >= '{$today}')";
$pending_condition = " AND (due_date <= '{$today}' OR vtiger_recurringevents.recurringdate <= '{$today}')";
}
}
if ($mode != 1) {
$list_query = " select vtiger_crmentity.crmid,vtiger_crmentity.smownerid,vtiger_crmentity.setype, vtiger_recurringevents.recurringdate, \n\t\tvtiger_activity.activityid, vtiger_activity.activitytype, vtiger_activity.date_start, vtiger_activity.due_date,\n\t\tfrom vtiger_activity inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_activity.activityid LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid left outer join vtiger_recurringevents on vtiger_recurringevents.activityid=vtiger_activity.activityid WHERE vtiger_crmentity.deleted=0 and vtiger_activity.activitytype not in ('Emails') AND ( vtiger_activity.status is NULL OR vtiger_activity.status not in ('Completed','Deferred')) and ( vtiger_activity.eventstatus is NULL OR vtiger_activity.eventstatus not in ('Held','Not Held') )" . $upcoming_condition;
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[16] == 3) {
$sec_parameter = getListViewSecurityParameter('Calendar');
$list_query .= $sec_parameter;
}
} else {
$list_query = "select vtiger_crmentity.crmid,vtiger_crmentity.smownerid,vtiger_crmentity.setype, vtiger_recurringevents.recurringdate, \n\t\tvtiger_activity.activityid, vtiger_activity.activitytype, vtiger_activity.date_start, vtiger_activity.due_date, \n\t\tfrom vtiger_activity inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_activity.activityid LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid left outer join vtiger_recurringevents on vtiger_recurringevents.activityid=vtiger_activity.activityid WHERE vtiger_crmentity.deleted=0 and (vtiger_activity.activitytype not in ('Emails')) AND (vtiger_activity.status is NULL OR vtiger_activity.status not in ('Completed','Deferred')) and (vtiger_activity.eventstatus is NULL OR vtiger_activity.eventstatus not in ('Held','Not Held')) " . $pending_condition;
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[16] == 3) {
$sec_parameter = getListViewSecurityParameter('Calendar');
$list_query .= $sec_parameter;
}
$list_query .= " GROUP BY vtiger_activity.activityid";
$list_query .= " ORDER BY date_start,time_start ASC";
$res = $adb->query($list_query);
$noofrecords = $adb->num_rows($res);
$open_activity_list = array();
$noofrows = $adb->num_rows($res);
if (count($res) > 0) {
for ($i = 0; $i < $noofrows; $i++) {
$open_activity_list[] = array('name' => $adb->query_result($res, $i, 'subject'), 'id' => $adb->query_result($res, $i, 'activityid'), 'type' => $adb->query_result($res, $i, 'activitytype'), 'module' => $adb->query_result($res, $i, 'setype'), 'date_start' => getDisplayDate($adb->query_result($res, $i, 'date_start')), 'due_date' => getDisplayDate($adb->query_result($res, $i, 'due_date')), 'recurringdate' => getDisplayDate($adb->query_result($res, $i, 'recurringdate')), 'priority' => $adb->query_result($res, $i, 'priority'));
}
}
$title = array();
$title[] = $view;
$title[] = 'myUpcoPendAct.gif';
$title[] = 'home_myact';
$title[] = 'showActivityView';
$title[] = 'MyUpcumingFrm';
$title[] = 'activity_view';
$header = array();
$header[] = $current_module_strings['LBL_LIST_SUBJECT'];
$header[] = 'Type';
$return_url = "&return_module={$currentModule}&return_action=DetailView&return_id=" . (is_object($focus) ? $focus->id : "");
$oddRow = true;
$entries = array();
foreach ($open_activity_list as $event) {
$recur_date = ereg_replace('--', '', $event['recurringdate']);
if ($recur_date != "") {
$event['date_start'] = $event['recurringdate'];
}
$font_color_high = "color:#00DD00;";
$font_color_medium = "color:#DD00DD;";
switch ($event['priority']) {
case 'High':
$font_color = $font_color_high;
break;
case 'Medium':
$font_color = $font_color_medium;
break;
default:
$font_color = '';
}
if ($event['type'] != 'Task' && $event['type'] != 'Emails' && $event['type'] != '') {
$activity_type = 'Events';
} else {
$activity_type = 'Task';
}
}
$entries[$event['id']] = array('0' => '<a href="index.php?action=DetailView&module=' . $event["module"] . '&activity_mode=' . $activity_type . '&record=' . $event["id"] . '' . $return_url . '" style="' . $font_color . ';">' . $event["name"] . '</a>', 'IMAGE' => '<IMG src="' . $image_path . $event["type"] . 's.gif">');
}
$values = array('noofactivities' => $noofrecords, 'Title' => $title, 'Header' => $header, 'Entries' => $entries);
$log->debug("Exiting getPendingActivities method ...");
return $values;
}
/** function to get query for the given reportid,filterlist,type
* @ param $reportid : Type integer
* @ param $filterlist : Type Array
* @ param $module : Type String
* this returns join query for the report
*/
function sGetSQLforReport($reportid, $filterlist, $type = '')
{
global $log;
$columnlist = $this->getQueryColumnsList($reportid);
$groupslist = $this->getGroupingList($reportid);
$stdfilterlist = $this->getStdFilterList($reportid);
$columnstotallist = $this->getColumnsTotal($reportid);
$advfilterlist = $this->getAdvFilterList($reportid);
$this->totallist = $columnstotallist;
global $current_user;
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
require 'user_privileges/sharing_privileges_' . $current_user->id . '.php';
$tab_id = getTabid($this->primarymodule);
//Fix for ticket #4915.
$selectlist = $columnlist;
//columns list
if (isset($selectlist)) {
$selectedcolumns = implode(", ", $selectlist);
}
//groups list
if (isset($groupslist)) {
$groupsquery = implode(", ", $groupslist);
}
//standard list
if (isset($stdfilterlist)) {
$stdfiltersql = implode(", ", $stdfilterlist);
}
if (isset($filterlist)) {
$stdfiltersql = implode(", ", $filterlist);
}
//columns to total list
if (isset($columnstotallist)) {
$columnstotalsql = implode(", ", $columnstotallist);
}
//advanced filterlist
if (isset($advfilterlist)) {
$advfiltersql = implode(" and ", $advfilterlist);
}
if ($stdfiltersql != "") {
$wheresql = " and " . $stdfiltersql;
}
if ($advfiltersql != "") {
$wheresql .= " and " . $advfiltersql;
}
$reportquery = $this->getReportsQuery($this->primarymodule);
// If we don't have access to any columns, let us select one column and limit result to shown we have not results
// Fix for: http://trac.vtiger.com/cgi-bin/trac.cgi/ticket/4758 - Prasad
$allColumnsRestricted = false;
if ($type == 'COLUMNSTOTOTAL') {
if ($columnstotalsql != '') {
$reportquery = "select " . $columnstotalsql . " " . $reportquery . " " . $wheresql;
}
} else {
if ($selectedcolumns == '') {
// Fix for: http://trac.vtiger.com/cgi-bin/trac.cgi/ticket/4758 - Prasad
$selectedcolumns = "''";
// "''" to get blank column name
$allColumnsRestricted = true;
}
$reportquery = "select " . $selectedcolumns . " " . $reportquery . " " . $wheresql;
}
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($this->primarymodule);
$reportquery .= " " . $sec_parameter;
}
$sec_modules = split(":", $this->secondarymodule);
foreach ($sec_modules as $i => $key) {
$table_id = getTabid($key);
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$table_id] == 3) {
$sec_parameter = getSecListViewSecurityParameter($key);
$reportquery .= " " . $sec_parameter;
}
}
//if($tab_id == 9 || $tab_id == 16)
//$reportquery.=" group by vtiger_activity.activityid ";
if (trim($groupsquery) != "" && empty($type)) {
$reportquery .= " order by " . $groupsquery;
}
// Prasad: No columns selected so limit the number of rows directly.
if ($allColumnsRestricted) {
$reportquery .= " limit 0";
}
$log->info("ReportRun :: Successfully returned sGetSQLforReport" . $reportid);
return $reportquery;
}
/**
* function to get pending activities for today
* @param integer $maxval - the maximum number of records to display
* @param integer $calCnt - returns the count query if this is set
* return array $values - activities record in array format
*/
function homepage_getPendingActivities($maxval, $calCnt)
{
require_once "data/Tracker.php";
require_once "include/utils/utils.php";
require_once 'include/utils/CommonUtils.php';
global $adb;
global $current_user;
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
require 'user_privileges/sharing_privileges_' . $current_user->id . '.php';
$today = date("Y-m-d", time());
$pending_condition = " AND (due_date = '{$today}' OR vtiger_recurringevents.recurringdate = '{$today}')";
$list_query = "select vtiger_crmentity.crmid,vtiger_crmentity.smownerid,vtiger_crmentity.setype, vtiger_recurringevents.recurringdate, vtiger_activity.* from vtiger_activity inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_activity.activityid LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid left outer join vtiger_recurringevents on vtiger_recurringevents.activityid=vtiger_activity.activityid WHERE vtiger_crmentity.deleted=0 and (vtiger_activity.activitytype not in ('Emails')) AND (vtiger_activity.status is NULL OR vtiger_activity.status not in ('Completed','Deferred')) and (vtiger_activity.eventstatus is NULL OR vtiger_activity.eventstatus not in ('Held','Not Held')) " . $pending_condition;
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[16] == 3) {
$sec_parameter = getListViewSecurityParameter('Calendar');
$list_query .= $sec_parameter;
}
$list_query .= " GROUP BY vtiger_activity.activityid";
$list_query .= " ORDER BY date_start,time_start ASC";
$list_query .= " limit {$maxval}";
$res = $adb->query($list_query);
$noofrecords = $adb->num_rows($res);
if ($calCnt == 'calculateCnt') {
return $noofrecords;
}
$open_activity_list = array();
$noofrows = $adb->num_rows($res);
if (count($res) > 0) {
for ($i = 0; $i < $noofrows; $i++) {
$open_activity_list[] = array('name' => $adb->query_result($res, $i, 'subject'), 'id' => $adb->query_result($res, $i, 'activityid'), 'type' => $adb->query_result($res, $i, 'activitytype'), 'module' => $adb->query_result($res, $i, 'setype'), 'date_start' => getDisplayDate($adb->query_result($res, $i, 'date_start')), 'due_date' => getDisplayDate($adb->query_result($res, $i, 'due_date')), 'recurringdate' => getDisplayDate($adb->query_result($res, $i, 'recurringdate')), 'priority' => $adb->query_result($res, $i, 'priority'));
}
}
$values = getActivityEntries($open_activity_list);
$values['ModuleName'] = 'Calendar';
$values['search_qry'] = "&action=ListView&from_homepage=pending_activities";
return $values;
}
/** Function to export the account records in CSV Format
* @param reference variable - where condition is passed when the query is executed
* Returns Export Accounts Query.
*/
function create_export_query($where)
{
global $log;
global $current_user;
$log->debug("Entering create_export_query(" . $where . ") method ...");
include "include/utils/ExportUtils.php";
//To get the Permitted fields query and the permitted fields list
$sql = getPermittedFieldsQuery("Accounts", "detail_view");
$fields_list = getFieldsListFromQuery($sql);
$query = "SELECT {$fields_list},case when (vtiger_users.user_name not like '') then vtiger_users.user_name else vtiger_groups.groupname end as user_name \n\t \t\t\tFROM " . $this->entity_table . "\n\t\t\t\tINNER JOIN vtiger_account\n\t\t\t\t\tON vtiger_account.accountid = vtiger_crmentity.crmid\n\t\t\t\tLEFT JOIN vtiger_accountbillads\n\t\t\t\t\tON vtiger_accountbillads.accountaddressid = vtiger_account.accountid\n\t\t\t\tLEFT JOIN vtiger_accountshipads\n\t\t\t\t\tON vtiger_accountshipads.accountaddressid = vtiger_account.accountid\n\t\t\t\tLEFT JOIN vtiger_accountscf\n\t\t\t\t\tON vtiger_accountscf.accountid = vtiger_account.accountid\n\t LEFT JOIN vtiger_groups\n \t ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\tLEFT JOIN vtiger_users\n\t\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid and vtiger_users.status = 'Active'\n\t\t\t\tLEFT JOIN vtiger_account vtiger_account2 \n\t\t\t\t\tON vtiger_account2.accountid = vtiger_account.parentid\n\t\t\t\t";
//vtiger_account2 is added to get the Member of account
$where_auto = " vtiger_crmentity.deleted = 0 ";
if ($where != "") {
$query .= " WHERE ({$where}) AND " . $where_auto;
} else {
$query .= " WHERE " . $where_auto;
}
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
require 'user_privileges/sharing_privileges_' . $current_user->id . '.php';
//we should add security check when the user has Private Access
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[6] == 3) {
//Added security check to get the permitted records only
$query = $query . " " . getListViewSecurityParameter("Accounts");
}
$log->debug("Exiting create_export_query method ...");
return $query;
}
function get_searchbyemailid($username, $emailaddress)
{
//crm-now added $adb to provide db access
global $log, $adb;
global $current_user;
require_once "modules/Users/Users.php";
$seed_user = new Users();
$user_id = $seed_user->retrieve_user_id($username);
$current_user = $seed_user;
$current_user->retrieve_entity_info($user_id, 'Users');
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
require 'user_privileges/sharing_privileges_' . $current_user->id . '.php';
$log->debug("Entering get_searchbyemailid(" . $username . "," . $emailaddress . ") Leads method ...");
//get users group ID's
$gquery = 'SELECT groupid FROM vtiger_users2group WHERE userid=?';
$gresult = $adb->pquery($gquery, array($user_id));
for ($j = 0; $j < $adb->num_rows($gresult); $j++) {
$groupidlist .= "," . $adb->query_result($gresult, $j, 'groupid');
}
//crm-now changed query to search in groups too and make only owned contacts available
$query = "SELECT vtiger_leaddetails.lastname,\n\t\t\t\t\t\tvtiger_leaddetails.firstname,\n\t\t\t\t\t\tvtiger_leaddetails.leadid, \n\t\t\t\t\t\tvtiger_leaddetails.email, \n\t\t\t\t\t\tvtiger_leaddetails.company \n\t\t\t\t\tFROM vtiger_leaddetails \n\t\t\t\t\tINNER JOIN vtiger_crmentity on vtiger_crmentity.crmid=vtiger_leaddetails.leadid \n\t\t\t\t\tLEFT JOIN vtiger_users on vtiger_users.id=vtiger_crmentity.smownerid \n\t\t\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\t\tWHERE vtiger_crmentity.deleted=0 AND vtiger_leaddetails.converted=0";
if (trim($emailaddress) != '') {
$query .= " AND ((vtiger_leaddetails.email like '" . formatForSqlLike($emailaddress) . "') or vtiger_leaddetails.lastname REGEXP REPLACE('" . $emailaddress . "',' ','|') or vtiger_leaddetails.firstname REGEXP REPLACE('" . $emailaddress . "',' ','|')) and vtiger_leaddetails.email != ''";
} else {
$query .= " AND (vtiger_leaddetails.email like '" . formatForSqlLike($emailaddress) . "' and vtiger_leaddetails.email != '')";
}
if (isset($groupidlist)) {
$query .= " AND (vtiger_users.user_name='" . $username . "' OR vtiger_crmentity.smownerid IN (" . substr($groupidlist, 1) . "))";
} else {
$query .= " AND vtiger_users.user_name='" . $username . "'";
}
$tab_id = getTabid("Leads");
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter("Leads");
$query .= $sec_parameter;
}
$log->debug("Exiting get_searchbyemailid method ...");
return $this->plugin_process_list_query($query);
}
function dashboard_check($module)
{
global $current_user;
$sec_parameter = '';
$tab_id = getTabid($module);
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
require 'user_privileges/sharing_privileges_' . $current_user->id . '.php';
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
}
return $sec_parameter;
}
function getReadEntityIds($module)
{
global $log;
$log->debug("Entering getReadEntityIds(" . $module . ") method ...");
global $current_user;
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
require 'user_privileges/sharing_privileges_' . $current_user->id . '.php';
$tab_id = getTabid($module);
if ($module == "Leads") {
$query = "SELECT vtiger_crmentity.crmid\n\t\t\tFROM vtiger_leaddetails\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_leaddetails.leadid\n\t\t\tLEFT JOIN vtiger_groups\n ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tWHERE vtiger_crmentity.deleted = 0\n\t\t\tAND vtiger_leaddetails.converted = 0 ";
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
$query .= $sec_parameter;
}
}
if ($module == "Accounts") {
//Query modified to sort by assigned to
$query = "SELECT vtiger_crmentity.crmid\n\t\t\tFROM vtiger_account\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_account.accountid\n\t\t\tLEFT JOIN vtiger_groups\n ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tWHERE vtiger_crmentity.deleted = 0 ";
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
$query .= $sec_parameter;
}
}
if ($module == "Potentials") {
//Query modified to sort by assigned to
$query = "SELECT vtiger_crmentity.crmid\n\t\t\tFROM vtiger_potential\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_potential.potentialid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tWHERE vtiger_crmentity.deleted = 0 ";
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
$query .= $sec_parameter;
}
}
if ($module == "Contacts") {
//Query modified to sort by assigned to
$query = "SELECT vtiger_crmentity.crmid\n\t\t\tFROM vtiger_contactdetails\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_contactdetails.contactid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tWHERE vtiger_crmentity.deleted = 0 ";
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
$query .= $sec_parameter;
}
}
if ($module == "Products") {
$query = "SELECT DISTINCT vtiger_crmentity.crmid\n\t\t\tFROM vtiger_products\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_products.productid\n\t\t\tLEFT JOIN vtiger_seproductsrel\n\t\t\t\tON vtiger_seproductsrel.productid = vtiger_products.productid\n\t\t\tWHERE vtiger_crmentity.deleted = 0\n\t\t\tAND (vtiger_seproductsrel.crmid IS NULL\n\t\t\t\tOR vtiger_seproductsrel.crmid IN (" . getReadEntityIds('Leads') . ")\n\t\t\t\tOR vtiger_seproductsrel.crmid IN (" . getReadEntityIds('Accounts') . ")\n\t\t\t\tOR vtiger_seproductsrel.crmid IN (" . getReadEntityIds('Potentials') . ")\n\t\t\t\tOR vtiger_seproductsrel.crmid IN (" . getReadEntityIds('Contacts') . ")) ";
}
if ($module == "PurchaseOrder") {
//Query modified to sort by assigned to
$query = "SELECT vtiger_crmentity.crmid\n\t\t\tFROM vtiger_purchaseorder\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_purchaseorder.purchaseorderid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tWHERE vtiger_crmentity.deleted = 0 ";
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
$query .= $sec_parameter;
}
}
if ($module == "SalesOrder") {
//Query modified to sort by assigned to
$query = "SELECT vtiger_crmentity.crmid\n\t\t\tFROM vtiger_salesorder\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_salesorder.salesorderid\n\t\t\tLEFT JOIN vtiger_groups\n ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tWHERE vtiger_crmentity.deleted = 0 " . $where;
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
$query .= $sec_parameter;
}
}
if ($module == "Invoice") {
$query = "SELECT vtiger_crmentity.crmid\n\t\t\tFROM vtiger_invoice\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_invoice.invoiceid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tWHERE vtiger_crmentity.deleted = 0 " . $where;
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
$query .= $sec_parameter;
}
}
if ($module == "Quotes") {
$query = "SELECT vtiger_crmentity.crmid\n\t\t FROM vtiger_quotes\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t ON vtiger_crmentity.crmid = vtiger_quotes.quoteid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tWHERE vtiger_crmentity.deleted = 0 " . $where;
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
$query .= $sec_parameter;
}
}
if ($module == "HelpDesk") {
$query = "SELECT vtiger_crmentity.crmid\n\t\t\tFROM vtiger_troubletickets\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_troubletickets.ticketid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tWHERE vtiger_crmentity.deleted = 0 " . $where;
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
$query .= $sec_parameter;
}
}
$log->debug("Exiting getReadEntityIds method ...");
return $query;
}
/**
* Create query to export the records.
*/
function create_export_query($where)
{
global $current_user;
include "include/utils/ExportUtils.php";
//To get the Permitted fields query and the permitted fields list
$sql = getPermittedFieldsQuery('OSSMailView', "detail_view");
$fields_list = getFieldsListFromQuery($sql);
$query = "SELECT {$fields_list}, vtiger_users.user_name AS user_name\n\t\t\t\t\tFROM vtiger_crmentity INNER JOIN {$this->table_name} ON vtiger_crmentity.crmid={$this->table_name}.{$this->table_index}";
if (!empty($this->customFieldTable)) {
$query .= " INNER JOIN " . $this->customFieldTable[0] . " ON " . $this->customFieldTable[0] . '.' . $this->customFieldTable[1] . " = {$this->table_name}.{$this->table_index}";
}
$query .= " LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid";
$query .= " LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id and vtiger_users.status='Active'";
$where_auto = " vtiger_crmentity.deleted=0";
if ($where != '') {
$query .= " WHERE ({$where}) AND {$where_auto}";
} else {
$query .= " WHERE {$where_auto}";
}
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
require 'user_privileges/sharing_privileges_' . $current_user->id . '.php';
// Security Check for Field Access
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[getTabid('OSSMailView')] == 3) {
//Added security check to get the permitted records only
$query = $query . " " . getListViewSecurityParameter($thismodule);
}
return $query;
}
/** To get the events of the specified user and shared events
* @param $userid -- The user Id:: Type integer
* @param $from_datetime -- The start date Obj :: Type Array
* @param $to_datetime -- The end date Obj :: Type Array
* @param $view -- The calendar view :: Type String
* @returns $list :: Type Array
*/
function readAppointment($userid, &$from_datetime, &$to_datetime, $view)
{
global $current_user, $adb;
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
require 'user_privileges/sharing_privileges_' . $current_user->id . '.php';
$and = "AND ((vtiger_activity.date_start between ? AND ?)\n\t\t\tOR (vtiger_activity.date_start < ? AND vtiger_activity.due_date > ?)\n\t\t\tOR (vtiger_activity.due_date between ? AND ?))";
$q = "select vtiger_activity.*, vtiger_crmentity.*, case when (vtiger_users.user_name not like '') then vtiger_users.user_name else vtiger_groups.groupname end as user_name FROM vtiger_activity inner join vtiger_crmentity on vtiger_activity.activityid = vtiger_crmentity.crmid left join vtiger_recurringevents on vtiger_activity.activityid=vtiger_recurringevents.activityid left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid WHERE vtiger_crmentity.deleted = 0 and vtiger_activity.activitytype not in ('Emails','Task') {$and} ";
// User Select Customization: Changes should made also in (calendayLaout getEventList) and one more BELOW
$query_filter_prefix = calendarview_getSelectedUserFilterQuerySuffix();
$q .= $query_filter_prefix;
// END
$params = array($from_datetime->get_formatted_date(), $to_datetime->get_formatted_date(), $from_datetime->get_formatted_date(), $from_datetime->get_formatted_date(), $from_datetime->get_formatted_date(), $to_datetime->get_formatted_date());
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[16] == 3) {
//Added for User Based Custom View for Calendar
$sec_parameter = getCalendarViewSecurityParameter();
$q .= $sec_parameter;
}
$q .= " AND vtiger_recurringevents.activityid is NULL ";
$q .= " group by vtiger_activity.activityid ORDER by vtiger_activity.date_start,vtiger_activity.time_start";
$r = $adb->pquery($q, $params);
$n = $adb->getRowCount($r);
$a = 0;
$list = array();
while ($a < $n) {
$result = $adb->fetchByAssoc($r);
$start_timestamp = strtotime($result["date_start"]);
$end_timestamp = strtotime($result["due_date"]);
if ($from_datetime->ts <= $start_timestamp) {
$from = $start_timestamp;
} else {
$from = $from_datetime->ts;
}
if ($to_datetime->ts <= $end_timestamp) {
$to = $to_datetime->ts;
} else {
$to = $end_timestamp;
}
for ($j = $from; $j <= $to; $j = $j + 60 * 60 * 24) {
$obj =& new Appointment();
$temp_start = date("Y-m-d", $j);
$result["date_start"] = $temp_start;
list($obj->temphour, $obj->tempmin) = explode(":", $result["time_start"]);
if ($start_timestamp != $end_timestamp && $view == 'day') {
if ($j == $start_timestamp) {
$result["duration_hours"] = 24 - $obj->temphour;
} elseif ($j > $start_timestamp && $j < $end_timestamp) {
list($obj->temphour, $obj->tempmin) = $current_user->start_hour != '' ? explode(":", $current_user->start_hour) : explode(":", "08:00");
$result["duration_hours"] = 24 - $obj->temphour;
} elseif ($j == $end_timestamp) {
list($obj->temphour, $obj->tempmin) = $current_user->start_hour != '' ? explode(":", $current_user->start_hour) : explode(":", "08:00");
list($ehr, $emin) = explode(":", $result["time_end"]);
$result["duration_hours"] = $ehr - $obj->temphour;
}
}
$obj->readResult($result, $view);
$list[] = $obj;
unset($obj);
}
$a++;
}
//Get Recurring events
$q = "SELECT vtiger_activity.*, vtiger_crmentity.*, case when (vtiger_users.user_name not like '') then vtiger_users.user_name else vtiger_groups.groupname end as user_name , vtiger_recurringevents.recurringid, vtiger_recurringevents.recurringdate as date_start ,vtiger_recurringevents.recurringtype,vtiger_groups.groupname from vtiger_activity inner join vtiger_crmentity on vtiger_activity.activityid = vtiger_crmentity.crmid inner join vtiger_recurringevents on vtiger_activity.activityid=vtiger_recurringevents.activityid left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid ";
$q .= " where vtiger_crmentity.deleted = 0 and vtiger_activity.activitytype not in ('Emails','Task') AND (recurringdate between ? and ?) ";
// User Select Customization
$q .= $query_filter_prefix;
// END
$params = array($from_datetime->get_formatted_date(), $to_datetime->get_formatted_date());
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[16] == 3) {
$sec_parameter = getListViewSecurityParameter('Calendar');
$q .= $sec_parameter;
}
$q .= " ORDER by vtiger_recurringevents.recurringid";
$r = $adb->pquery($q, $params);
$n = $adb->getRowCount($r);
$a = 0;
while ($a < $n) {
$obj =& new Appointment();
$result = $adb->fetchByAssoc($r);
list($obj->temphour, $obj->tempmin) = explode(":", $result["time_start"]);
$obj->readResult($result, $view);
$a++;
$list[] = $obj;
unset($obj);
}
usort($list, 'compare');
return $list;
}
/**
* Create query to export the records.
*/
function create_export_query($where)
{
global $current_user;
$thismodule = $_REQUEST['module'];
include "include/utils/ExportUtils.php";
//To get the Permitted fields query and the permitted fields list
$sql = getPermittedFieldsQuery($thismodule, "detail_view");
$fields_list = getFieldsListFromQuery($sql);
$query = "SELECT {$fields_list}, vtiger_users.user_name AS user_name \n\t\t\t\t\tFROM vtiger_crmentity INNER JOIN {$this->table_name} ON vtiger_crmentity.crmid={$this->table_name}.{$this->table_index}";
if (!empty($this->customFieldTable)) {
$query .= " INNER JOIN " . $this->customFieldTable[0] . " ON " . $this->customFieldTable[0] . '.' . $this->customFieldTable[1] . " = {$this->table_name}.{$this->table_index}";
}
$query .= " LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid";
$query .= " LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id and vtiger_users.status='Active'";
$linkedModulesQuery = $this->db->pquery("SELECT distinct fieldname, columnname, relmodule FROM vtiger_field" . " INNER JOIN vtiger_fieldmodulerel ON vtiger_fieldmodulerel.fieldid = vtiger_field.fieldid" . " WHERE uitype='10' AND vtiger_fieldmodulerel.module=?", array($thismodule));
$linkedFieldsCount = $this->db->num_rows($linkedModulesQuery);
for ($i = 0; $i < $linkedFieldsCount; $i++) {
$related_module = $this->db->query_result($linkedModulesQuery, $i, 'relmodule');
$fieldname = $this->db->query_result($linkedModulesQuery, $i, 'fieldname');
$columnname = $this->db->query_result($linkedModulesQuery, $i, 'columnname');
$other = CRMEntity::getInstance($related_module);
vtlib_setup_modulevars($related_module, $other);
$query .= " LEFT JOIN {$other->table_name} ON {$other->table_name}.{$other->table_index} = {$this->table_name}.{$columnname}";
}
$where_auto = " vtiger_crmentity.deleted=0";
if ($where != '') {
$query .= " WHERE ({$where}) AND {$where_auto}";
} else {
$query .= " WHERE {$where_auto}";
}
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
require 'user_privileges/sharing_privileges_' . $current_user->id . '.php';
// Security Check for Field Access
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[7] == 3) {
//Added security check to get the permitted records only
$query = $query . " " . getListViewSecurityParameter($thismodule);
}
return $query;
}
/**
* Returns a list of the Emails to be exported
*/
function create_export_query(&$order_by, &$where)
{
global $log;
global $current_user;
$log->debug("Entering create_export_query(" . $order_by . "," . $where . ") method ...");
include "include/utils/ExportUtils.php";
//To get the Permitted fields query and the permitted fields list
$sql = getPermittedFieldsQuery("Emails", "detail_view");
$fields_list = getFieldsListFromQuery($sql);
$query = "SELECT {$fields_list} FROM vtiger_activity \n\t\t\tINNER JOIN vtiger_crmentity \n\t\t\t\tON vtiger_crmentity.crmid=vtiger_activity.activityid \n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_seactivityrel\n\t\t\t\tON vtiger_seactivityrel.activityid = vtiger_activity.activityid\n\t\t\tLEFT JOIN vtiger_contactdetails\n\t\t\t\tON vtiger_contactdetails.contactid = vtiger_seactivityrel.crmid\n\t\t\tLEFT JOIN vtiger_cntactivityrel\n\t\t\t\tON vtiger_cntactivityrel.activityid = vtiger_activity.activityid\n\t\t\t\tAND vtiger_cntactivityrel.contactid = vtiger_cntactivityrel.contactid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_salesmanactivityrel\n\t\t\t\tON vtiger_salesmanactivityrel.activityid = vtiger_activity.activityid\n\t\t\tLEFT JOIN vtiger_emaildetails\n\t\t\t\tON vtiger_emaildetails.emailid = vtiger_activity.activityid\n\t\t\tLEFT JOIN vtiger_seattachmentsrel \n\t\t\t\tON vtiger_activity.activityid=vtiger_seattachmentsrel.crmid \n\t\t\tLEFT JOIN vtiger_attachments \n\t\t\t\tON vtiger_seattachmentsrel.attachmentsid = vtiger_attachments.attachmentsid \n\t\t\tWHERE vtiger_activity.activitytype='Emails' AND vtiger_crmentity.deleted=0 ";
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
require 'user_privileges/sharing_privileges_' . $current_user->id . '.php';
//we should add security check when the user has Private Access
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1) {
$sec_parameter = getListViewSecurityParameter("Emails");
$query .= $sec_parameter;
}
$log->debug("Exiting create_export_query method ...");
return $query;
}
