private static string GetWhereOperational(NameValueCollection requestData, FieldGroupDetail field, List<SqlParameter> listParames, string groupName) { string strWhere = ""; string fieldValue = requestData[field.FieldName]; //Change FieldGroup's value for supplier search sql if (new FieldGroup(groupName).SourceName.ToUpper() == "V_SGPFORSUPPLIER" && field.DisplayName.ToUpper() == "SUBMITSTATUS") { switch (fieldValue) { case "Launch": fieldValue = "0";break; case "Closed": fieldValue = "9";break; } } if (!string.IsNullOrEmpty(fieldValue) && fieldValue.Trim() != "") { switch (field.DataType) { case FieldInfo.DATATYPE_DATETIME: string[] values = fieldValue.Split('-'); if (values.Length == 2) { DateTime dt; string startDate = values[0].Trim(); string endDate = values[1].Trim(); if (DateTime.TryParse(startDate, out dt) && DateTime.TryParse(endDate, out dt)) { strWhere = String.Format(" AND ({0} >= @Start_{0} AND {0} <= @End_{0})", field.FieldName); listParames.Add(new SqlParameter("@Start_" + field.FieldName, startDate + " 00:00:00")); listParames.Add(new SqlParameter("@End_" + field.FieldName, endDate + " 23:59:59")); } } break; default: strWhere = GetSplitWhere(fieldValue, field, listParames); break; } } return strWhere; }
private static string GetSplitWhere(string fieldValue, FieldGroupDetail field, List<SqlParameter> listParames) { string strWhere = ""; string[] moreValues = fieldValue.Trim().Split(';', ','); if (moreValues != null && moreValues.Length > 0) { strWhere += " AND ("; for (int i = 0; i < moreValues.Length; i++) { if (i > 0) { strWhere += " OR "; } switch (field.DataType) { case FieldInfo.DATATYPE_INT: case FieldInfo.DATATYPE_FLOAT: case FieldInfo.DATATYPE_DOUBLE: case FieldInfo.DATATYPE_LIST: strWhere += String.Format("{0} = @{0}{1}", field.FieldName, i); listParames.Add(new SqlParameter("@" + field.FieldName + i, moreValues[i].Trim())); break; case FieldInfo.DATATYPE_SUMMARY: strWhere += String.Format("({0}) LIKE @{1}{2}", field.KeyValueSource, field.FieldName, i); listParames.Add(new SqlParameter("@" + field.FieldName + i, "%" + moreValues[i].Trim() + "%")); break; default: strWhere += String.Format("{0} LIKE @{0}{1}", field.FieldName, i); listParames.Add(new SqlParameter("@" + field.FieldName + i, "%" + moreValues[i].Trim() + "%")); break; } } strWhere += ")"; } return strWhere; }