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