function getWhereCondition($currentModule) { global $log; $column_array = getColumnOrTableArr(); $table_col_array = getColumnOrTableArr(false); $log->debug("Entering getWhereCondition(" . $currentModule . ") method ..."); if ($_REQUEST['searchtype'] != 'advance') { $where = Search($currentModule); } else { $adv_string = ''; $url_string = ''; if (isset($_REQUEST['search_cnt'])) { $tot_no_criteria = $_REQUEST['search_cnt']; } if ($_REQUEST['matchtype'] == 'all') { $matchtype = "and"; } else { $matchtype = "or"; } for ($i = 0; $i < $tot_no_criteria; $i++) { if ($i == $tot_no_criteria - 1) { $matchtype = ""; } $table_colname = 'Fields' . $i; $search_condition = 'Condition' . $i; $search_value = 'Srch_value' . $i; $tab_col = ''; $tab_col = str_replace('\'', '', stripslashes($_REQUEST[$table_colname])); $tab_col = str_replace('\\', '', $tab_col); $srch_cond = str_replace('\'', '', stripslashes($_REQUEST[$search_condition])); $srch_cond = str_replace('\\', '', $srch_cond); $srch_val = $_REQUEST[$search_value]; list($tab_name, $column_name) = split("[.]", $tab_col); $url_string .= "&Fields" . $i . "=" . $tab_col . "&Condition" . $i . "=" . $srch_cond . "&Srch_value" . $i . "=" . $srch_val; if ($tab_col == "smownerid") { $adv_string .= getSearch_criteria($srch_cond, $srch_val, 'ec_users.user_name') . $matchtype; } elseif ($tab_col == "smcreatorid") { $user_id = getUserId_Ol($srch_val); $adv_string .= " smcreatorid='" . $user_id . "' " . $matchtype; } elseif ($tab_col == "approvedby") { $user_id = getUserId_Ol($srch_val); $adv_string .= " approvedby='" . $user_id . "' " . $matchtype; } elseif ($tab_col == "approved") { $srch_val = getApproveIdByStatus($srch_val); $adv_string .= " approved='" . $srch_val . "' " . $matchtype; } elseif ($tab_col == "ec_activity.status") { $adv_string .= " (" . getSearch_criteria($srch_cond, $srch_val, 'ec_activity.status') . " or"; $adv_string .= " " . getSearch_criteria($srch_cond, $srch_val, 'ec_activity.eventstatus') . " )" . $matchtype; } elseif ($tab_col == "ec_cntactivityrel.contactid") { $adv_string .= " (" . getSearch_criteria($srch_cond, $srch_val, 'ec_contactdetails.firstname') . " or"; $adv_string .= " " . getSearch_criteria($srch_cond, $srch_val, 'ec_contactdetails.lastname') . " )" . $matchtype; } elseif ($tab_col == "ec_products.catalogid") { $adv_string .= getSearch_criteria($srch_cond, $srch_val, 'ec_catalog.catalogname') . " " . $matchtype; } elseif ($tab_col == "ec_faq.faqcategoryid") { $adv_string .= getSearch_criteria($srch_cond, $srch_val, 'ec_faqcategory.faqcategoryname') . " " . $matchtype; } elseif (in_array($column_name, $column_array)) { $adv_string .= getValuesforColumns($column_name, $srch_val) . " " . $matchtype; } else { $adv_string .= " " . getSearch_criteria($srch_cond, $srch_val, $tab_col) . " " . $matchtype; } } $where = "(" . $adv_string . ")#@@#" . $url_string . "&searchtype=advance&search_cnt=" . $tot_no_criteria . "&matchtype=" . $_REQUEST['matchtype']; } /* elseif($_REQUEST['type']=='dbrd') { $where = getdashboardcondition(); } else { $where=Search($currentModule); } */ $log->info("getWhereCondition method where condition:" . $where); $log->debug("Exiting getWhereCondition method ..."); return $where; }
function BasicSearch($module, $search_field, $search_string, $input = '') { global $log, $mod_strings, $current_user; $log->debug("Entering BasicSearch(" . $module . "," . $search_field . "," . $search_string . ") method ..."); global $adb; $search_string = ltrim(rtrim($adb->sql_escape_string($search_string))); global $column_array, $table_col_array; if (empty($input)) { $input = $_REQUEST; } if ($search_field == 'crmid') { $column_name = 'crmid'; $table_name = 'vtiger_crmentity'; $where = "{$table_name}.{$column_name} like '" . formatForSqlLike($search_string) . "'"; } elseif ($search_field == 'currency_id' && ($module == 'PriceBooks' || $module == 'PurchaseOrder' || $module == 'SalesOrder' || $module == 'Invoice' || $module == 'Quotes')) { $column_name = 'currency_name'; $table_name = 'vtiger_currency_info'; $where = "{$table_name}.{$column_name} like '" . formatForSqlLike($search_string) . "'"; } elseif ($search_field == 'folderid' && $module == 'Documents') { $column_name = 'foldername'; $table_name = 'vtiger_attachmentsfolder'; $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') { if ($search_field == 'contactid') { $where = "(vtiger_contactdetails.contact_no like '" . formatForSqlLike($search_string) . "')"; return $where; } elseif ($search_field == 'account_id') { $search_field = "parent_id"; } } //Check ends //Added to search contact name by lastname if (($module == "Calendar" || $module == "Invoice" || $module == "Documents" || $module == "SalesOrder" || $module == "PurchaseOrder") && $search_field == "contact_id") { $module = 'Contacts'; $search_field = 'lastname'; } if ($search_field == "accountname" && $module != "Accounts") { $search_field = "account_id"; } if ($search_field == 'productname' && $module == 'Campaigns') { $search_field = "product_id"; } $qry = "select vtiger_field.columnname,tablename from vtiger_tab inner join vtiger_field on vtiger_field.tabid=vtiger_tab.tabid where vtiger_tab.name=? and (fieldname=? or columnname=?)"; $result = $adb->pquery($qry, array($module, $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'); $uitype = getUItype($module, $column_name); //Added for Member of search in Accounts if ($column_name == "parentid" && $module == "Accounts") { $table_name = "vtiger_account2"; $column_name = "accountname"; } if ($column_name == "parentid" && $module == "Products") { $table_name = "vtiger_products2"; $column_name = "productname"; } if ($column_name == "reportsto" && $module == "Contacts") { $table_name = "vtiger_contactdetails2"; $column_name = "lastname"; } if ($column_name == "inventorymanager" && ($module = "Quotes")) { $table_name = "vtiger_usersQuotes"; $column_name = "user_name"; } //Added to support user date format in basic search if ($uitype == 5 || $uitype == 6 || $uitype == 23 || $uitype == 70) { if ($search_string != '' && $search_string != '0000-00-00') { $date = new DateTimeField($search_string); $value = $date->getDisplayDate(); if (strpos($search_string, ' ') > -1) { $value .= ' ' . $date->getDisplayTime(); } } else { $value = $search_string; } } // Added to fix errors while searching check box type fields(like product active. ie. they store 0 or 1. we search them as yes or no) in basic search. if ($uitype == 56) { if (strtolower($search_string) == 'yes') { $where = "{$table_name}.{$column_name} = '1'"; } elseif (strtolower($search_string) == 'no') { $where = "{$table_name}.{$column_name} = '0'"; } else { $where = "{$table_name}.{$column_name} = '-1'"; } } elseif ($uitype == 15 || $uitype == 16) { if (is_uitype($uitype, '_picklist_')) { // Get all the keys for the for the Picklist value $mod_keys = array_keys($mod_strings, $search_string); if (sizeof($mod_keys) >= 1) { // Iterate on the keys, to get the first key which doesn't start with LBL_ (assuming it is not used in PickList) foreach ($mod_keys as $mod_idx => $mod_key) { $stridx = strpos($mod_key, 'LBL_'); // Use strict type comparision, refer strpos for more details if ($stridx !== 0) { $search_string = $mod_key; if ($input['operator'] == 'e' && getFieldVisibilityPermission("Calendar", $current_user->id, 'taskstatus') == '0' && ($column_name == "status" || $column_name == "eventstatus")) { $where = "(vtiger_activity.status ='" . $search_string . "' or vtiger_activity.eventstatus ='" . $search_string . "')"; } else { if (getFieldVisibilityPermission("Calendar", $current_user->id, 'taskstatus') == '0' && ($column_name == "status" || $column_name == "eventstatus")) { $where = "(vtiger_activity.status like '" . formatForSqlLike($search_string) . "' or vtiger_activity.eventstatus like '" . formatForSqlLike($search_string) . "')"; } else { $where = "{$table_name}.{$column_name} like '" . formatForSqlLike($search_string) . "'"; } } break; } else { //if the mod strings cointains LBL , just return the original search string. Not the key $where = "{$table_name}.{$column_name} like '" . formatForSqlLike($search_string) . "'"; } } } else { if (getFieldVisibilityPermission("Calendar", $current_user->id, 'taskstatus') == '0' && ($table_name == "vtiger_activity" && ($column_name == "status" || $column_name == "eventstatus"))) { $where = "(vtiger_activity.status like '" . formatForSqlLike($search_string) . "' or vtiger_activity.eventstatus like '" . formatForSqlLike($search_string) . "')"; } else { $where = "{$table_name}.{$column_name} like '" . formatForSqlLike($search_string) . "'"; } } } } elseif ($table_name == "vtiger_crmentity" && $column_name == "smownerid") { $where = get_usersid($table_name, $column_name, $search_string); } elseif ($table_name == "vtiger_crmentity" && $column_name == "modifiedby") { $concatSql = getSqlForNameInDisplayFormat(array('last_name' => 'vtiger_users2.last_name', 'first_name' => 'vtiger_users2.first_name'), 'Users'); $where .= "(trim({$concatSql}) like '" . formatForSqlLike($search_string) . "' or vtiger_groups2.groupname like '" . formatForSqlLike($search_string) . "')"; } else { if (in_array($column_name, $column_array)) { $where = getValuesforColumns($column_name, $search_string, 'cts', $input); } else { if ($input['type'] == 'entchar') { $where = "{$table_name}.{$column_name} = '" . $search_string . "'"; } else { $where = "{$table_name}.{$column_name} like '" . formatForSqlLike($search_string) . "'"; } } } } } if (stristr($where, "like '%%'")) { $where_cond0 = str_replace("like '%%'", "like ''", $where); $where_cond1 = str_replace("like '%%'", "is NULL", $where); if ($module == "Calendar") { $where = "(" . $where_cond0 . " and " . $where_cond1 . ")"; } else { $where = "(" . $where_cond0 . " or " . $where_cond1 . ")"; } } // commented to support searching "%" with the search string. if ($input['type'] == 'alpbt') { $where = str_replace_once("%", "", $where); } //uitype 10 handling if ($uitype == 10) { $where = array(); $sql = "select fieldid from vtiger_field where tabid=? and fieldname=?"; $result = $adb->pquery($sql, array(getTabid($module), $search_field)); if ($adb->num_rows($result) > 0) { $fieldid = $adb->query_result($result, 0, "fieldid"); $sql = "select * from vtiger_fieldmodulerel where fieldid=?"; $result = $adb->pquery($sql, array($fieldid)); $count = $adb->num_rows($result); $searchString = formatForSqlLike($search_string); for ($i = 0; $i < $count; $i++) { $relModule = $adb->query_result($result, $i, "relmodule"); $relInfo = getEntityField($relModule); $relTable = $relInfo["tablename"]; $relField = $relInfo["fieldname"]; if (strpos($relField, 'concat') !== false) { $where[] = "{$relField} like '{$searchString}'"; } else { $where[] = "{$relTable}.{$relField} like '{$searchString}'"; } } $where = implode(" or ", $where); } $where = "({$where}) "; } $log->debug("Exiting BasicSearch method ..."); return $where; }
function getWhereCondition($currentModule) { global $log, $default_charset, $adb; global $column_array, $table_col_array, $mod_strings, $current_user; $log->debug("Entering getWhereCondition(" . $currentModule . ") method ..."); if ($_REQUEST['searchtype'] == 'advance') { $adv_string = ''; $url_string = ''; if (isset($_REQUEST['search_cnt'])) { $tot_no_criteria = vtlib_purify($_REQUEST['search_cnt']); } if ($_REQUEST['matchtype'] == 'all') { $matchtype = "and"; } else { $matchtype = "or"; } for ($i = 0; $i < $tot_no_criteria; $i++) { if ($i == $tot_no_criteria - 1) { $matchtype = ""; } $table_colname = 'Fields' . $i; $search_condition = 'Condition' . $i; $search_value = 'Srch_value' . $i; list($tab_col_val, $typeofdata) = split("::::", $_REQUEST[$table_colname]); $tab_col = str_replace('\'', '', stripslashes($tab_col_val)); $srch_cond = str_replace('\'', '', stripslashes($_REQUEST[$search_condition])); $srch_val = $_REQUEST[$search_value]; $srch_val = function_exists(iconv) ? @iconv("UTF-8", $default_charset, $srch_val) : $srch_val; $url_string .= "&Fields" . $i . "=" . $tab_col . "&Condition" . $i . "=" . $srch_cond . "&Srch_value" . $i . "=" . urlencode($srch_val); $srch_val = $adb->sql_escape_string($srch_val); list($tab_name, $column_name) = split("[.]", $tab_col); $uitype = getUItype($currentModule, $column_name); //added to allow search in check box type fields(ex: product active. it will contain 0 or 1) using yes or no instead of 0 or 1 if ($uitype == 56) { if (strtolower($srch_val) == 'yes') { $adv_string .= " " . getSearch_criteria($srch_cond, "1", $tab_name . '.' . $column_name) . " " . $matchtype; } elseif (strtolower($srch_val) == 'no') { $adv_string .= " " . getSearch_criteria($srch_cond, "0", $tab_name . '.' . $column_name) . " " . $matchtype; } else { $adv_string .= " " . getSearch_criteria($srch_cond, "-1", $tab_name . '.' . $column_name) . " " . $matchtype; } } elseif ($uitype == 15 || $uitype == 16) { if (is_uitype($uitype, '_picklist_')) { // Get all the keys for the for the Picklist value $mod_keys = array_keys($mod_strings, $srch_val); if (sizeof($mod_keys) >= 1) { // Iterate on the keys, to get the first key which doesn't start with LBL_ (assuming it is not used in PickList) foreach ($mod_keys as $mod_idx => $mod_key) { $stridx = strpos($mod_key, 'LBL_'); // Use strict type comparision, refer strpos for more details if ($stridx !== 0) { $srch_val = $mod_key; if (getFieldVisibilityPermission("Calendar", $current_user->id, 'taskstatus') == '0' && ($tab_col == "vtiger_activity.status" || $tab_col == "vtiger_activity.eventstatus")) { if ($srch_cond == 'dcts' || $srch_cond == 'isn' || $srch_cond == 'is') { $re_cond = "and"; } else { $re_cond = "or"; } if ($srch_cond == 'is' && $srch_val != '') { $re_cond = "or"; } $adv_string .= " (" . getSearch_criteria($srch_cond, $srch_val, 'vtiger_activity.status') . " " . $re_cond; $adv_string .= " " . getSearch_criteria($srch_cond, $srch_val, 'vtiger_activity.eventstatus') . " )" . $matchtype; } else { $adv_string .= " " . getSearch_criteria($srch_cond, $srch_val, $tab_name . '.' . $column_name) . " " . $matchtype; } break; } else { //if the key contains the LBL, then return the original srch_val. $adv_string .= " " . getSearch_criteria($srch_cond, $srch_val, $tab_name . '.' . $column_name) . " " . $matchtype; } } } else { if (getFieldVisibilityPermission("Calendar", $current_user->id, 'taskstatus') == '0' && ($tab_col == "vtiger_activity.status" || $tab_col == "vtiger_activity.eventstatus")) { if ($srch_cond == 'dcts' || $srch_cond == 'isn' || $srch_cond == 'is') { $re_cond = "and"; } else { $re_cond = "or"; } if ($srch_cond == 'is' && $srch_val != '') { $re_cond = "or"; } $adv_string .= " (" . getSearch_criteria($srch_cond, $srch_val, 'vtiger_activity.status') . " " . $re_cond; $adv_string .= " " . getSearch_criteria($srch_cond, $srch_val, 'vtiger_activity.eventstatus') . " )" . $matchtype; } else { $adv_string .= " " . getSearch_criteria($srch_cond, $srch_val, $tab_col) . " " . $matchtype; } } } } elseif ($tab_col == "vtiger_crmentity.smownerid") { $adv_string .= " (" . getSearch_criteria($srch_cond, $srch_val, 'vtiger_users.user_name') . " or"; $adv_string .= " " . getSearch_criteria($srch_cond, $srch_val, 'vtiger_groups.groupname') . " )" . $matchtype; } elseif ($tab_col == "vtiger_cntactivityrel.contactid") { $adv_string .= " (" . getSearch_criteria($srch_cond, $srch_val, 'vtiger_contactdetails.firstname') . " or"; $adv_string .= " " . getSearch_criteria($srch_cond, $srch_val, 'vtiger_contactdetails.lastname') . " )" . $matchtype; } elseif (in_array($column_name, $column_array)) { $adv_string .= " " . getValuesforColumns($column_name, $srch_val, $srch_cond) . " " . $matchtype; } else { $adv_string .= " " . getSearch_criteria($srch_cond, $srch_val, $tab_col) . " " . $matchtype; } } $where = "(" . $adv_string . ")#@@#" . $url_string . "&searchtype=advance&search_cnt=" . $tot_no_criteria . "&matchtype=" . vtlib_purify($_REQUEST['matchtype']); } elseif ($_REQUEST['type'] == 'dbrd') { $where = getdashboardcondition(); } else { $where = Search($currentModule); } $log->debug("Exiting getWhereCondition method ..."); return $where; }