Example #1
0
function BasicSearch($module, $search_field, $search_string)
{
    global $log;
    $log->debug("Entering BasicSearch() method ...");
    global $adb;
    $column_array = getColumnOrTableArr();
    $table_col_array = getColumnOrTableArr(false);
    if ($search_field == 'crmid') {
        $column_name = 'crmid';
        $table_name = 'ec_crmentity';
        $where = "{$table_name}.{$column_name}='" . $search_string . "'";
    } else {
        if (empty($search_field)) {
            return "";
        }
        //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 columnname,tablename from ec_field where tabid='" . $tabid . "' and (fieldname='" . $search_field . "' or columnname='" . $search_field . "')";
        $result = $adb->query($qry);
        $noofrows = $adb->num_rows($result);
        if ($noofrows != 0) {
            $column_name = $adb->query_result($result, 0, 'columnname');
            if (empty($column_name)) {
                return "";
            }
            //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 ($column_name == "smownerid") {
                $where = get_usersid($table_name, $column_name, $search_string);
            } elseif ($column_name == "smcreatorid") {
                $where = get_usersid($table_name, $column_name, $search_string);
            } elseif ($column_name == "approvedby") {
                $where = get_usersid($table_name, $column_name, $search_string);
            } elseif ($column_name == "approved") {
                $search_string = getApproveIdByStatus($search_string);
                $where = "{$table_name}.{$column_name} = '" . $search_string . "'";
            } elseif ($table_name == "ec_activity" && $column_name == "status") {
                $where = "{$table_name}.{$column_name} like '%" . $search_string . "%' or ec_activity.eventstatus like '%" . $search_string . "%'";
            } elseif ($table_name == "ec_pricebook" && $column_name == "active") {
                if (stristr($search_string, 'yes')) {
                    $where = "{$table_name}.{$column_name} = 1";
                }
                if (stristr($search_string, 'no')) {
                    $where = "{$table_name}.{$column_name} is NULL";
                }
            } elseif ($table_name == "ec_activity" && $column_name == "status") {
                $where = "{$table_name}.{$column_name} like '%" . $search_string . "%' or ec_activity.eventstatus like '%" . $search_string . "%'";
            } elseif ($column_name == "catalogid") {
                $parent_arr = explode("::", $search_string);
                $parent_count = count($parent_arr);
                $catalogname = $parent_arr[$parent_count - 1];
                $where = "({$table_name}.{$column_name}='" . $catalogname . "' or ec_catalog.catalogname like '%" . $search_string . "%' or ec_catalog.parentcatalog like '" . $search_string . "::%')";
            } elseif ($column_name == "faqcategoryid") {
                $parent_arr = explode("::", $search_string);
                $parent_count = count($parent_arr);
                $categoryname = $parent_arr[$parent_count - 1];
                $where = "({$table_name}.{$column_name}='" . $categoryname . "' or ec_faqcategory.faqcategoryname like '%" . $search_string . "%' or ec_faqcategory.parentfaqcategory like '" . $search_string . "::%')";
            } elseif ($module == 'Accounts' && $column_name == "parentid") {
                $where = " ec_account2.accountname like '%" . $search_string . "%' ";
            } else {
                if (in_array($column_name, $column_array)) {
                    $where = getValuesforColumns($column_name, $search_string);
                } elseif ($module == 'Accounts' && $column_name == "accountname") {
                    $where = " (ec_account.accountname like '%" . $search_string . "%') ";
                } else {
                    $where = "{$table_name}.{$column_name} like '%" . $search_string . "%'";
                }
            }
        } else {
            $where = $search_field . " like '%" . $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);
        $where = $search_field . " like '" . $search_string . "%'";
    }
    $log->debug("Exiting BasicSearch method ...");
    return $where;
}
Example #2
0
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 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;
}