/** 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, $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\tvtiger_contactdetails.contactid, vtiger_contactdetails.salutation,\n\t\t\t\tvtiger_contactdetails.email,vtiger_contactdetails.title,\n\t\t\t\tvtiger_contactdetails.mobile,vtiger_account.accountname,\n\t\t\t\tvtiger_account.accountid as accountid from vtiger_contactdetails\n\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_contactdetails.contactid\n\t\t\tinner join vtiger_users on vtiger_users.id=vtiger_crmentity.smownerid\n\t\t\tleft join vtiger_account on vtiger_account.accountid=vtiger_contactdetails.accountid\n\t\t\tleft join vtiger_contactaddress on vtiger_contactaddress.contactaddressid=vtiger_contactdetails.contactid\n\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= getNonAdminAccessControlQuery('Contacts', $current_user); $query .= "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 != '')"; } $log->debug("Exiting get_searchbyemailid method ..."); return $this->plugin_process_list_query($query); }
/** to get the comparator value for the given comparator and value * @param $comparator :: type string * @param $value :: type string * @returns $rtvalue in the format $comparator $value */ function getAdvComparator($comparator, $value, $datatype = '') { global $adb, $default_charset; $value = html_entity_decode(trim($value), ENT_QUOTES, $default_charset); $value = $adb->sql_escape_string($value); if ($comparator == "e") { if (trim($value) == "NULL") { $rtvalue = " is NULL"; } elseif (trim($value) != "") { $rtvalue = " = " . $adb->quote($value); } elseif (trim($value) == "" && ($datatype == "V" || $datatype == "E")) { $rtvalue = " = " . $adb->quote($value); } else { $rtvalue = " is NULL"; } } if ($comparator == "n") { if (trim($value) == "NULL") { $rtvalue = " is NOT NULL"; } elseif (trim($value) != "") { $rtvalue = " <> " . $adb->quote($value); } elseif (trim($value) == "" && $datatype == "V") { $rtvalue = " <> " . $adb->quote($value); } elseif (trim($value) == "" && $datatype == "E") { $rtvalue = " <> " . $adb->quote($value); } else { $rtvalue = " is NOT NULL"; } } if ($comparator == "s") { if (trim($value) == "" && ($datatype == "V" || $datatype == "E")) { $rtvalue = " like '" . formatForSqlLike($value, 3) . "'"; } else { $rtvalue = " like '" . formatForSqlLike($value, 2) . "'"; } } if ($comparator == "ew") { if (trim($value) == "" && ($datatype == "V" || $datatype == "E")) { $rtvalue = " like '" . formatForSqlLike($value, 3) . "'"; } else { $rtvalue = " like '" . formatForSqlLike($value, 1) . "'"; } } if ($comparator == "c") { if (trim($value) == "" && ($datatype == "V" || $datatype == "E")) { $rtvalue = " like '" . formatForSqlLike($value, 3) . "'"; } else { $rtvalue = " like '" . formatForSqlLike($value) . "'"; } } if ($comparator == "k") { if (trim($value) == "" && ($datatype == "V" || $datatype == "E")) { $rtvalue = " not like ''"; } else { $rtvalue = " not like '" . formatForSqlLike($value) . "'"; } } if ($comparator == "l") { $rtvalue = " < " . $adb->quote($value); } if ($comparator == "g") { $rtvalue = " > " . $adb->quote($value); } if ($comparator == "m") { $rtvalue = " <= " . $adb->quote($value); } if ($comparator == "h") { $rtvalue = " >= " . $adb->quote($value); } return $rtvalue; }
/** * Function creates HTML to display Events ListView * @param array $entry_list - collection of strings(Event Information) * return string $list_view - html tags in string format */ function constructEventListView(&$cal, $entry_list, $navigation_array = '') { global $mod_strings, $app_strings, $adb, $cal_log, $current_user, $theme; $cal_log->debug("Entering constructEventListView() method..."); $format = $cal['calendar']->hour_format; $date_format = $current_user->date_format; $hour_startat = timeString(array('hour' => date('H:i'), 'minute' => 0), '24'); $hour_endat = timeString(array('hour' => date('H:i', time() + 60 * 60), 'minute' => 0), '24'); $time_arr = getaddEventPopupTime($hour_startat, $hour_endat, $format); $temp_ts = $cal['calendar']->date_time->ts; //to get date in user selected date format $temp_date = $date_format == 'dd-mm-yyyy' ? date('d-m-Y', $temp_ts) : ($date_format == 'mm-dd-yyyy' ? date('m-d-Y', $temp_ts) : ($date_format == 'yyyy-mm-dd' ? date('Y-m-d', $temp_ts) : '')); if ($cal['calendar']->day_start_hour != 23) { $endtemp_date = $temp_date; } else { $endtemp_ts = $temp_ts + 1 * 24 * 60 * 60; $endtemp_date = $date_format == 'dd-mm-yyyy' ? date('d-m-Y', $endtemp_ts) : ($date_format == 'mm-dd-yyyy' ? date('m-d-Y', $endtemp_ts) : ($date_format == 'yyyy-mm-dd' ? date('Y-m-d', $endtemp_ts) : '')); } $list_view = ""; $start_datetime = $app_strings['LBL_START_DATE_TIME']; $end_datetime = $app_strings['LBL_END_DATE_TIME']; //Events listview header labels $header = array('0' => '#', '1' => $start_datetime, '2' => $end_datetime, '3' => $mod_strings['LBL_EVENTTYPE'], '4' => $mod_strings['LBL_EVENTDETAILS']); $header_width = array('0' => '5%', '1' => '15%', '2' => '15%', '3' => '10%', '4' => '33%'); /*if(getFieldVisibilityPermission('Events',$current_user->id,'parent_id') == '0') { array_push($header,$mod_strings['LBL_RELATEDTO']); array_push($header_width,'15%'); }*/ if (isPermitted("Calendar", "EditView") == "yes" || isPermitted("Calendar", "Delete") == "yes") { array_push($header, $mod_strings['LBL_ACTION']); array_push($header_width, '10%'); } if (getFieldVisibilityPermission('Events', $current_user->id, 'eventstatus') == '0') { array_push($header, $mod_strings['LBL_STATUS']); array_push($header_width, '$10%'); } array_push($header, $mod_strings['LBL_ASSINGEDTO']); array_push($header_width, '15%'); $list_view .= "<table style='background-color: rgb(204, 204, 204);' class='small' align='center' border='0' cellpadding='5' cellspacing='1' width='98%'>\n <tr>"; $header_rows = count($header); $navigationOutput = getTableHeaderNavigation($navigation_array, $url_string, "Calendar", "index"); if ($navigationOutput != '') { $list_view .= "<tr width=100% bgcolor=white><td align=center colspan={$header_rows}>"; $list_view .= "<table align=center width='98%'><tr>" . $navigationOutput . "</tr></table></td></tr>"; } $list_view .= "<tr>"; for ($i = 0; $i < $header_rows; $i++) { $list_view .= "<td nowrap='nowrap' class='lvtCol' width='" . $header_width[$i] . "'>" . $header[$i] . "</td>"; } $list_view .= "</tr>"; $rows = count($entry_list); if ($rows != 0) { for ($i = 0; $i < count($entry_list); $i++) { $list_view .= "<tr class='lvtColData' onmouseover='this.className=\"lvtColDataHover\"' onmouseout='this.className=\"lvtColData\"' bgcolor='white'>"; foreach ($entry_list[$i] as $key => $entry) { $assigned_role_query = $adb->pquery("select vtiger_user2role.roleid,vtiger_user2role.userid from vtiger_user2role INNER JOIN vtiger_users where vtiger_users.id=vtiger_user2role.userid and vtiger_users.user_name=?", array($entry_list[$i]['assignedto'])); $assigned_user_role_id = $adb->query_result($assigned_role_query, 0, "roleid"); $assigned_user_id = $adb->query_result($assigned_role_query, 0, "userid"); $role_list = $adb->pquery("SELECT * from vtiger_role WHERE parentrole LIKE '" . formatForSqlLike($current_user->column_fields['roleid']) . formatForSqlLike($assigned_user_role_id) . "'", array()); $is_shared = $adb->pquery("SELECT * from vtiger_sharedcalendar where userid=? and sharedid=?", array($assigned_user_id, $current_user->id)); if ($key != 'visibility') { if (($key == 'eventdetail' || $key == 'action') && ($current_user->column_fields['is_admin'] != 'on' && $adb->num_rows($role_list) == 0 && ($adb->num_rows($is_shared) == 0 && ($entry_list[$i]['visibility'] == 'Public' || $entry_list[$i]['visibility'] == 'Private') || $entry_list[$i]['visibility'] == 'Private')) && $current_user->column_fields['user_name'] != $entry_list[$i]['assignedto']) { if ($key == 'eventdetail') { $list_view .= "<td nowrap='nowrap'><font color='red'><b>" . $entry_list[$i]['assignedto'] . " - " . $mod_strings['LBL_BUSY'] . "</b></font></td>"; } else { $list_view .= "<td nowrap='nowrap'><font color='red'>" . $app_strings['LBL_NOT_ACCESSIBLE'] . "</font></td>"; } } else { $list_view .= "<td nowrap='nowrap'>{$entry}</td>"; } } } $list_view .= "</tr>"; } } else { $list_view .= "<tr><td style='background-color:#efefef;height:340px' align='center' colspan='9'>\n\t\t\t\t"; $list_view .= "<div style='border: 3px solid rgb(153, 153, 153); background-color: rgb(255, 255, 255); width: 45%; position: relative; z-index: 5000;'>\n\t\t\t\t\t<table border='0' cellpadding='5' cellspacing='0' width='98%'>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td rowspan='2' width='25%'>\n\t\t\t\t\t\t\t\t<img src='" . vtiger_imageurl('empty.jpg', $theme) . "' height='60' width='61'></td>\n\t\t\t\t\t\t\t<td style='border-bottom: 1px solid rgb(204, 204, 204);' nowrap='nowrap' width='75%'><span class='genHeaderSmall'>" . $app_strings['LBL_NO'] . " " . $app_strings['Events'] . " " . $app_strings['LBL_FOUND'] . " !</span></td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr>"; //checking permission for Create/Edit Operation if (isPermitted("Calendar", "EditView") == "yes") { $list_view .= "<td class='small' align='left' nowrap='nowrap'>" . $app_strings['LBL_YOU_CAN_CREATE'] . " " . $app_strings['LBL_AN'] . " " . $app_strings['Event'] . " " . $app_strings['LBL_NOW'] . ".<br>\n\t\t\t\t\t -<a href='javascript:void(0);' onClick='gshow(\"addEvent\",\"Call\",\"" . $temp_date . "\",\"" . $endtemp_date . "\",\"" . $time_arr['starthour'] . "\",\"" . $time_arr['startmin'] . "\",\"" . $time_arr['startfmt'] . "\",\"" . $time_arr['endhour'] . "\",\"" . $time_arr['endmin'] . "\",\"" . $time_arr['endfmt'] . "\",\"listview\",\"event\");'>" . $app_strings['LBL_CREATE'] . " " . $app_strings['LBL_AN'] . " " . $app_strings['Event'] . "</a><br>\n\t\t\t\t\t</td>"; } else { $list_view .= "<td class='small' align='left' nowrap='nowrap'>" . $app_strings['LBL_YOU_ARE_NOT_ALLOWED_TO_CREATE'] . " " . $app_strings['LBL_AN'] . " " . $app_strings['Event'] . "<br></td>"; } $list_view .= "</tr>\n </table>\n\t\t\t\t</div>"; $list_view .= "</td></tr>"; } $list_view .= "</table>"; $cal_log->debug("Exiting constructEventListView() method..."); return $list_view; }
/** Function to get advanced comparator in query form for the given Comparator and value * @ param $comparator : Type String * @ param $value : Type String * returns the check query for the comparator */ function getAdvComparator($comparator, $value, $datatype = "", $columnName = '') { global $log, $adb, $default_charset, $ogReport; $value = html_entity_decode(trim($value), ENT_QUOTES, $default_charset); $value_len = strlen($value); $is_field = false; if ($value_len > 1 && $value[0] == '$' && $value[$value_len - 1] == '$') { $temp = str_replace('$', '', $value); $is_field = true; } if ($datatype == 'C') { $value = str_replace("yes", "1", str_replace("no", "0", $value)); } if ($is_field == true) { $value = $this->getFilterComparedField($temp); } if ($comparator == "e") { if (trim($value) == "NULL") { $rtvalue = " is NULL"; } elseif (trim($value) != "") { $rtvalue = " = " . $adb->quote($value); } elseif (trim($value) == "" && $datatype == "V") { $rtvalue = " = " . $adb->quote($value); } else { $rtvalue = " is NULL"; } } if ($comparator == "n") { if (trim($value) == "NULL") { $rtvalue = " is NOT NULL"; } elseif (trim($value) != "") { if ($columnName) { $rtvalue = " <> " . $adb->quote($value) . " OR " . $columnName . " IS NULL "; } else { $rtvalue = " <> " . $adb->quote($value); } } elseif (trim($value) == "" && $datatype == "V") { $rtvalue = " <> " . $adb->quote($value); } else { $rtvalue = " is NOT NULL"; } } if ($comparator == "s") { $rtvalue = " like '" . formatForSqlLike($value, 2, $is_field) . "'"; } if ($comparator == "ew") { $rtvalue = " like '" . formatForSqlLike($value, 1, $is_field) . "'"; } if ($comparator == "c") { $rtvalue = " like '" . formatForSqlLike($value, 0, $is_field) . "'"; } if ($comparator == "k") { $rtvalue = " not like '" . formatForSqlLike($value, 0, $is_field) . "'"; } if ($comparator == "l") { $rtvalue = " < " . $adb->quote($value); } if ($comparator == "g") { $rtvalue = " > " . $adb->quote($value); } if ($comparator == "m") { $rtvalue = " <= " . $adb->quote($value); } if ($comparator == "h") { $rtvalue = " >= " . $adb->quote($value); } if ($comparator == "b") { $rtvalue = " < " . $adb->quote($value); } if ($comparator == "a") { $rtvalue = " > " . $adb->quote($value); } if ($is_field == true) { $rtvalue = str_replace("'", "", $rtvalue); $rtvalue = str_replace("\\", "", $rtvalue); } $log->info("ReportRun :: Successfully returned getAdvComparator"); return $rtvalue; }
/** Function to get advanced comparator in query form for the given Comparator and value * @ param $comparator : Type String * @ param $value : Type String * returns the check query for the comparator */ function getAdvComparator($comparator, $value, $datatype = "") { $adb = PEARDatabase::getInstance(); global $default_charset; $value = html_entity_decode(trim($value), ENT_QUOTES, $default_charset); $value_len = strlen($value); $is_field = false; if ($value[0] == '$' && $value[$value_len - 1] == '$') { $temp = str_replace('$', '', $value); $is_field = true; } if ($datatype == 'C') { $value = str_replace("yes", "1", str_replace("no", "0", $value)); } if ($is_field == true) { $value = $this->getFilterComparedField($temp); } if ($comparator == "e") { if (trim($value) == "NULL") { $rtvalue = " is NULL"; } elseif (trim($value) != "") { $rtvalue = " = " . $adb->quote($value); } elseif (trim($value) == "" && $datatype == "V") { $rtvalue = " = " . $adb->quote($value); } else { $rtvalue = " is NULL"; } } if ($comparator == "n") { if (trim($value) == "NULL") { $rtvalue = " is NOT NULL"; } elseif (trim($value) != "") { $rtvalue = " <> " . $adb->quote($value); } elseif (trim($value) == "" && $datatype == "V") { $rtvalue = " <> " . $adb->quote($value); } else { $rtvalue = " is NOT NULL"; } } if ($comparator == "s") { $rtvalue = " like '" . formatForSqlLike($value, 2, $is_field) . "'"; } if ($comparator == "ew") { $rtvalue = " like '" . formatForSqlLike($value, 1, $is_field) . "'"; } if ($comparator == "c") { $rtvalue = " like '" . formatForSqlLike($value, 0, $is_field) . "'"; } if ($comparator == "k") { $rtvalue = " not like '" . formatForSqlLike($value, 0, $is_field) . "'"; } if ($comparator == "l") { $rtvalue = " < " . $adb->quote($value); } if ($comparator == "g") { $rtvalue = " > " . $adb->quote($value); } if ($comparator == "m") { $rtvalue = " <= " . $adb->quote($value); } if ($comparator == "h") { $rtvalue = " >= " . $adb->quote($value); } if ($comparator == "b") { $rtvalue = " < " . $adb->quote($value); } if ($comparator == "a") { $rtvalue = " > " . $adb->quote($value); } if ($is_field == true) { $rtvalue = str_replace("'", "", $rtvalue); $rtvalue = str_replace("\\", "", $rtvalue); } return $rtvalue; }
/** 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, $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 ..."); //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_contactdetails.lastname,vtiger_contactdetails.firstname,\n\t\t\t\tvtiger_contactdetails.contactid, vtiger_contactdetails.salutation,\n\t\t\t\tvtiger_contactdetails.email,vtiger_contactdetails.title,\n\t\t\t\tvtiger_contactdetails.mobile,vtiger_account.accountname,\n\t\t\t\tvtiger_account.accountid as accountid from vtiger_contactdetails\n\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_contactdetails.contactid\n\t\t\tinner join vtiger_users on vtiger_users.id=vtiger_crmentity.smownerid\n\t\t\tleft join vtiger_account on vtiger_account.accountid=vtiger_contactdetails.accountid\n\t\t\tleft join vtiger_contactaddress on vtiger_contactaddress.contactaddressid=vtiger_contactdetails.contactid\n\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= getNonAdminAccessControlQuery('Contacts', $current_user); $query .= "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 != '')"; 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 . "'"; } } $log->debug("Exiting get_searchbyemailid method ..."); return $this->plugin_process_list_query($query); }
} if ($viewnamedesc['viewname'] == 'All') { $smarty->assign("ALL", 'All'); } global $email_title; $display_title = $mod_strings['LBL_LIST_FORM_TITLE']; if ($email_title) { $display_title = $email_title; } //to get the search vtiger_field if exists if (isset($_REQUEST['search']) && $_REQUEST['search'] != '' && $_REQUEST['search_text'] != '') { $url_string .= "&search=" . vtlib_purify($_REQUEST['search']) . "&search_field=" . vtlib_purify($_REQUEST['search_field']) . "&search_text=" . vtlib_purify($_REQUEST['search_text']); if ($_REQUEST['search_field'] != 'join') { $where = $adb->sql_escape_string($_REQUEST['search_field']) . " like '" . formatForSqlLike($_REQUEST['search_text']) . "'"; } else { $where = "(subject like '" . formatForSqlLike($_REQUEST['search_text']) . "' OR vtiger_users.user_name like '" . formatForSqlLike($_REQUEST['search_text']) . "')"; } } //Retreive the list from Database //<<<<<<<<<customview>>>>>>>>> if ($viewid != "0") { $listquery = getListQuery("Emails"); $list_query = $oCustomView->getModifiedCvListQuery($viewid, $listquery, "Emails"); } else { $list_query = getListQuery("Emails"); } //<<<<<<<<customview>>>>>>>>> if (isset($where) && $where != '') { $list_query .= " AND " . $where; } if ($_REQUEST['folderid'] == '2') {
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 basicRBsearch($module, $search_field, $search_string) { global $log; $log->debug("Entering basicRBsearch(" . $module . "," . $search_field . "," . $search_string . ") method ..."); global $adb; global $rb_column_array, $rb_table_col_array; if ($search_field == 'crmid') { $column_name = 'crmid'; $table_name = 'vtiger_entity'; $where = "{$table_name}.{$column_name} like '" . formatForSqlLike($search_string) . "'"; } else { //Check added for tickets by accounts/contacts in dashboard $search_field_first = $search_field; if ($module == 'HelpDesk' && ($search_field == 'contactid' || $search_field == 'account_id')) { $search_field = "parent_id"; } //Check ends $tabid = getTabid($module); $qry = "select vtiger_field.columnname,tablename from vtiger_field where tabid=? and (fieldname=? or columnname=?) and vtiger_field.presence in (0,2)"; $result = $adb->pquery($qry, array($tabid, $search_field, $search_field)); $noofrows = $adb->num_rows($result); if ($noofrows != 0) { $column_name = $adb->query_result($result, 0, 'columnname'); //Check added for tickets by accounts/contacts in dashboard if ($column_name == 'parent_id') { if ($search_field_first == 'account_id') { $search_field_first = 'accountid'; } if ($search_field_first == 'contactid') { $search_field_first = 'contact_id'; } $column_name = $search_field_first; } //Check ends $table_name = $adb->query_result($result, 0, 'tablename'); if ($table_name == "vtiger_crmentity" && $column_name == "smownerid") { $where = get_usersid($table_name, $column_name, $search_string); } elseif ($table_name == "vtiger_activity" && $column_name == "status") { $where = "{$table_name}.{$column_name} like '" . formatForSqlLike($search_string) . "' or vtiger_activity.eventstatus like '" . formatForSqlLike($search_string) . "'"; } elseif ($table_name == "vtiger_pricebook" && $column_name == "active") { if (stristr('yes', $search_string)) { $where = "{$table_name}.{$column_name} = 1"; } else { if (stristr('no', $search_string)) { $where = "{$table_name}.{$column_name} is NULL"; } else { //here where condition is added , since the $where query must go as differently so that it must give an empty set, either than Yes or No... $where = "{$table_name}.{$column_name} = 2"; } } } elseif ($table_name == "vtiger_activity" && $column_name == "status") { $where = "{$table_name}.{$column_name} like '%" . $search_string . "%' or vtiger_activity.eventstatus like '" . formatForSqlLike($search_string) . "'"; } $sql = "select concat(tablename,':',fieldname) as tablename from vtiger_entityname where entityidfield='{$column_name}' or entityidcolumn='{$column_name}'"; $no_of_rows = $adb->num_rows($adb->query($sql)); if ($no_of_rows >= 1) { $where = getValuesforRBColumns($column_name, $search_string); } else { if (($column_name != "status" || $table_name != 'vtiger_activity') && ($table_name != 'vtiger_crmentity' || $column_name != 'smownerid') && ($table_name != 'vtiger_pricebook' || $column_name != 'active')) { $tableName = explode(":", $table_name); $where = "{$table_name}.{$column_name} like '" . formatForSqlLike($search_string) . "'"; } } } } if ($_REQUEST['type'] == 'entchar') { $search = array('Un Assigned', '%', 'like'); $replace = array('', '', '='); $where = str_replace($search, $replace, $where); } if ($_REQUEST['type'] == 'alpbt') { $where = str_replace_once("%", "", $where); } $log->debug("Exiting basicRBsearch method ..."); return $where; }
/** * Function to get the where condition for a module based on the field table entries * @param string $listquery -- ListView query for the module * @param string $module -- module name * @param string $search_val -- entered search string value * @return string $where -- where condition for the module based on field table entries */ function getUnifiedWhere($listquery, $module, $search_val) { global $adb, $current_user; require 'user_privileges/user_privileges_' . $current_user->id . '.php'; $search_val = $adb->sql_escape_string($search_val); if ($is_admin == true || $profileGlobalPermission[1] == 0 || $profileGlobalPermission[2] == 0) { $query = "SELECT columnname, tablename FROM vtiger_field WHERE tabid = ? and vtiger_field.presence in (0,2)"; $qparams = array(getTabid($module)); } else { $profileList = getCurrentUserProfileList(); $query = "SELECT columnname, tablename FROM vtiger_field INNER JOIN vtiger_profile2field ON vtiger_profile2field.fieldid = vtiger_field.fieldid INNER JOIN vtiger_def_org_field ON vtiger_def_org_field.fieldid = vtiger_field.fieldid WHERE vtiger_field.tabid = ? AND vtiger_profile2field.visible = 0 AND vtiger_profile2field.profileid IN (" . generateQuestionMarks($profileList) . ") AND vtiger_def_org_field.visible = 0 and vtiger_field.presence in (0,2) GROUP BY vtiger_field.fieldid"; $qparams = array(getTabid($module), $profileList); } $result = $adb->pquery($query, $qparams); $noofrows = $adb->num_rows($result); $where = ''; for ($i = 0; $i < $noofrows; $i++) { $columnname = $adb->query_result($result, $i, 'columnname'); $tablename = $adb->query_result($result, $i, 'tablename'); // Search / Lookup customization if ($module == 'Contacts' && $columnname == 'accountid') { $columnname = "accountname"; $tablename = "vtiger_account"; } // END //Before form the where condition, check whether the table for the field has been added in the listview query if (strstr($listquery, $tablename)) { if ($where != '') { $where .= " OR "; } $where .= $tablename . "." . $columnname . " LIKE '" . formatForSqlLike($search_val) . "'"; } } return $where; }
/** 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); }