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