/** 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);
}
