public IEnumerable <AgentSearch> SearchAgents(AgentSearch AgentSearch) { string sql = ""; string sqlWhere = ""; if (AgentSearch.AGT_CODE != null && AgentSearch.AGT_CODE != "") { sqlWhere = sqlWhere + "(UPPER(t.agt_code) LIKE '%" + AgentSearch.AGT_CODE.ToUpper() + "%') AND"; } if (AgentSearch.AGT_NAME != null && AgentSearch.AGT_NAME != "") { sqlWhere = sqlWhere + "(UPPER(t.AGT_FULL_NAME) LIKE '%" + AgentSearch.AGT_NAME.ToUpper() + "%') AND"; } if (AgentSearch.AGT_ADDRESS != null && AgentSearch.AGT_ADDRESS != "") { sqlWhere = sqlWhere + "(UPPER(t.AGT_FULL_ADDRESS) LIKE '%" + AgentSearch.AGT_ADDRESS.ToUpper() + "%') AND"; } if (AgentSearch.AGT_MOBILE != null && AgentSearch.AGT_MOBILE != "") { sqlWhere = sqlWhere + "(UPPER(t.AGT_MOBILE) LIKE '%" + AgentSearch.AGT_MOBILE.ToUpper() + "%') AND"; } if (AgentSearch.AGT_NIC_NO != null && AgentSearch.AGT_NIC_NO != "") { sqlWhere = sqlWhere + "(UPPER(t.AGT_NIC_NO) LIKE '%" + AgentSearch.AGT_NIC_NO.ToUpper() + "%') AND"; } if (sqlWhere.Length > 0) { sqlWhere = sqlWhere.Substring(0, sqlWhere.Length - 3); } sqlWhere = sqlWhere + "and t.agt_effective_end_date is null"; List <AgentSearch> AgentList = new List <AgentSearch>(); DataTable dataTable = new DataTable(); OracleDataReader dataReader = null; OracleConnection connection = new OracleConnection(ConnectionString); OracleCommand command; sql = "select t.agt_id,t.agt_code,t.AGT_FULL_NAME,t.AGT_FULL_ADDRESS,t.agt_nic_no,t.agt_mobile from hci_tbl_agent t where (" + sqlWhere + ")"; command = new OracleCommand(sql, connection); try { connection.Open(); dataReader = command.ExecuteReader(); dataTable.Load(dataReader); dataReader.Close(); connection.Close(); AgentList = (from DataRow drow in dataTable.Rows select new AgentSearch() { AGT_ID = drow[0].ToString(), AGT_CODE = drow[1].ToString(), AGT_NAME = drow[2].ToString(), AGT_ADDRESS = drow[3].ToString(), AGT_NIC_NO = drow[4].ToString(), AGT_MOBILE = drow[5].ToString() }).ToList(); } catch (Exception exception) { if (dataReader != null) { dataReader.Close(); } if (connection.State == ConnectionState.Open) { connection.Close(); } } return(AgentList); }
public ActionResult Search(AgentsSearchViewModel viewModel) { CaptchaImage image = CaptchaImageCache.GetAndRemoveCachedCaptcha(viewModel.CaptchaId); // If image exists in cache (wasn't removed during timeout) if (image == null) { LogContext.LogInformation("Image is not found in cache (AgentsController method Search[HttpPost])", "AgentsController", "captchaImage.Id = " + viewModel.CaptchaId); // Redirect to search again return(RedirectToAction("Search")); } if (string.Equals(image.Text, viewModel.CaptchaText, StringComparison.OrdinalIgnoreCase)) { string searchText = viewModel.SearchText; if (!string.IsNullOrWhiteSpace(searchText)) { searchText = searchText.Trim(); try { using (new Performance(ms => ViewBag.DbTime = ms)) { // Data access viewModel.Agents = AgentSearch.GetAgents(searchText); } if (viewModel.Agents == null) { ViewBag.Error = "Поиск может быть выполнен по ФИО или номеру сертификата. Пожалуйста уточните запрос."; } else if (viewModel.Agents.Count == 0) { ViewBag.Error = "По вашему запросу не найдено ни одного совпадения."; } else if (viewModel.Agents.Count > ConfigurationHelper.MaxAgentsSearchResultSetLength) { ViewBag.Error = "Вашему запросу соответствует слишком много результатов поиска. Пожалуйста, уточните свой запрос."; viewModel.Agents = null; } } catch (Exception ex) { LogContext.LogException(ex, "AgentRegisterOpenPart", "Was handled in AgentsController Search catch block"); ViewBag.Error = "Произошла системная ошибка. Попробуйте воспользоваться поиском позднее. Приносим извинения за неудобства."; viewModel.Agents = null; } } else { ViewBag.Error = "Поисковая строка не задана."; } } else { ViewBag.CaptchaError = "Текст не совпадает с указанным на изображении."; } viewModel.CaptchaImage = CaptchaImageCache.GenerateCaptchaImage(); return(View(viewModel)); }