public ActionResult SearchGrid(SearchViewModel model) { List <TR01AViewModel> list = new List <TR01AViewModel>(); using (var ctx = new PURSysEntities()) { string sqlCmd = "Select * from TR01A "; string filterStr = "Where "; foreach (PropertyInfo propertyInfo in model.GetType().GetProperties()) { var value = propertyInfo.GetValue(model, null); if (value != null && value.ToString().Length > 0) { var name = propertyInfo.Name; if (name.Contains("StrDT")) { if (name.Contains("Beg")) { filterStr += "PUR_DT" + " >= '" + value + "' and "; } else { filterStr += "PUR_DT" + " <= '" + value + "' and "; } } else if (propertyInfo.PropertyType == typeof(int)) { var key = value.ToString(); if (key != "0") { filterStr += name.Remove(0, 2) + " = '" + key + "' and "; } } else if (propertyInfo.PropertyType == typeof(string)) { filterStr += name.Remove(0, 2) + " = '" + value + "' and "; } } } if (filterStr.Length > 6) { sqlCmd = sqlCmd + filterStr.Substring(0, filterStr.Length - 4);; } else { //sqlCmd = "Select * from TR01A "; var yyyyMM = DateTime.Now.ToString("yyyyMM"); //sqlCmd = $"Select * from TR01A where PUR_NO like '%{yyyyMM}%'"; sqlCmd = $"Select top 30 * from TR01A order by PUR_NO DESC"; } var tempList = ctx.TR01A.SqlQuery(sqlCmd).OrderByDescending(x => x.PUR_NO).Take(100).ToList(); list.AddRange(TR01Business.FromEntity(tempList)); } return(PartialView("_SearchGrid", list)); }
public ActionResult GridTR01A(SearchViewModel search) { //var data = TR01Business.FromEntity(_Service.GetA(x => x.TR01A_ID == 26 || x.TR01A_ID == 27 || x.TR01A_ID == 23 || x.TR01A_ID == 28 || x.TR01A_ID == 35).ToList()); List <TR01AModel> list = new List <TR01AModel>(); using (var ctx = new PURSysEntities()) { string sqlCmd = "Select * from TR01A "; string filterStr = "Where "; foreach (PropertyInfo propertyInfo in search.GetType().GetProperties()) { var value = propertyInfo.GetValue(search, null); if (value != null && value.ToString().Length > 0) { var name = propertyInfo.Name; if (name.Contains("ConfirmStatus")) { filterStr += "CFN_YN = '" + value + "' and "; } else if (name.Contains("StrDT")) { if (name.Contains("Beg")) { filterStr += "PUR_DT" + " >= '" + value + "' and "; } else { filterStr += "PUR_DT" + " <= '" + value + "' and "; } } else if (propertyInfo.PropertyType == typeof(int)) { var key = value.ToString(); if (key != "0") { filterStr += name.Remove(0, 2) + " = '" + key + "' and "; } } else if (propertyInfo.PropertyType == typeof(string)) { filterStr += name.Remove(0, 2) + " = '" + value + "' and "; } } } if (filterStr.Length > 6) { sqlCmd = sqlCmd + filterStr.Substring(0, filterStr.Length - 4) + " and PUR_NO > '2018'"; } else { var yyyyMM = DateTime.Now.ToString("yyyyMM"); //sqlCmd = $"Select * from TR01A where PUR_NO like '%{yyyyMM}%'"; sqlCmd = $"Select * from TR01A where PUR_NO > '2018' order by PUR_NO DESC"; } var tempList = ctx.TR01A.SqlQuery(sqlCmd).OrderByDescending(x => x.PUR_NO).Take(100).ToList(); list.AddRange(TR01Business.FromEntity(tempList)); } return(PartialView("_GridTR01A", list)); }