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