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