public HttpResponseMessage GetEmployees(FilterRequestDTO filterRequest)
{
if (!ModelState.IsValid)
{
return(Request.CreateErrorResponse(HttpStatusCode.NotFound, "--No Data. Please Add Data.-- "));
}
return(Request.CreateResponse(HttpStatusCode.OK, this.employeeDBAccess.EmployeeList(filterRequest)));
}
private static StringBuilder GetPropertyValueForSQL(FilterRequestDTO filterDTO, string prependValue, string postpendValue)
{
StringBuilder current = new StringBuilder();
if (!string.IsNullOrEmpty(prependValue))
{
current.Append(prependValue);
}
else if (filterDTO.DataType == typeof(string) || filterDTO.DataType == typeof(DateTime))
{
current.Append(@"'");
}
if (filterDTO.DataType == typeof(bool))
{
if (filterDTO.PropertyValue.ToLower() == "true")
{
current.Append('1');
}
else if (filterDTO.PropertyValue.ToLower() == "false")
{
current.Append('0');
}
else
{
current.Append(filterDTO.PropertyValue);
}
}
else
{
current.Append(filterDTO.PropertyValue);
}
if (!string.IsNullOrEmpty(postpendValue))
{
current.Append(postpendValue + " ");
}
else if (filterDTO.DataType == typeof(string) || filterDTO.DataType == typeof(DateTime))
{
current.Append(@"' ");
}
return(current);
}
private static StringBuilder GetPropertyValue(FilterRequestDTO filterDTO)
{
StringBuilder current = new StringBuilder();
if (filterDTO.DataType == typeof(string))
{
current.Append(@"""" + filterDTO.PropertyValue + @""" ");
}
else if (filterDTO.DataType == typeof(DateTime))
{
current.Append(@"DateTime(" + filterDTO.PropertyValue + @") ");
}
else if (filterDTO.DataType == typeof(bool) ||
filterDTO.DataType == typeof(int) ||
filterDTO.DataType == typeof(byte) ||
filterDTO.DataType == typeof(double) ||
filterDTO.DataType == typeof(decimal))
{
current.Append(filterDTO.PropertyValue);
}
return(current);
}
//-----------------WITH PARAMETERS---------------------------------
public List <RespoonseData> EmployeeList(FilterRequestDTO filterRequest)
{
var empData = entity.EmployeeMasters.Select(a => new RespoonseData
{
page = 1,
pageSize = 10,
data = a.RoleMaster.EmployeeMasters.Select(b => new EmployeeResponseModel
{
employeeName = b.employeeName,
roleName = b.RoleMaster.roleName,
reportsTo = "-"
}).ToList(),
}).ToList();
if (!string.IsNullOrEmpty(filterRequest.employeeName))
{
empData = empData.Where(a => a.data.Select(b => b.employeeName).Contains(filterRequest.employeeName)).ToList();
}
if (!string.IsNullOrEmpty(filterRequest.roleName))
{
empData = empData.Where(a => a.data.Select(b => b.roleName).Contains(filterRequest.roleName)).ToList();
}
return(empData);
}
private static StringBuilder GetWhereClauseForFilter(FilterRequestDTO filterDTO, bool forLinq)
{
StringBuilder current = new StringBuilder();
if (!string.IsNullOrWhiteSpace(filterDTO.PropertyName))
{
switch (filterDTO.FilterType)
{
case FilterTypes.IsLessThan:
current.Append(filterDTO.PropertyName + " <");
if (forLinq)
{
current.Append(GetPropertyValue(filterDTO));
}
else
{
current.Append(GetPropertyValueForSQL(filterDTO, null, null));
}
break;
case FilterTypes.IsLessThanOrEqualTo:
current.Append(filterDTO.PropertyName + " <=");
if (forLinq)
{
current.Append(GetPropertyValue(filterDTO));
}
else
{
current.Append(GetPropertyValueForSQL(filterDTO, null, null));
}
break;
case FilterTypes.IsEqualTo:
if (forLinq)
{
current.Append(filterDTO.PropertyName + " ==");
current.Append(GetPropertyValue(filterDTO));
}
else
{
current.Append(filterDTO.PropertyName + " =");
current.Append(GetPropertyValueForSQL(filterDTO, null, null));
}
break;
case FilterTypes.IsNotEqualTo:
if (forLinq)
{
current.Append(filterDTO.PropertyName + " !=");
current.Append(GetPropertyValue(filterDTO));
}
else
{
current.Append(filterDTO.PropertyName + " <>");
current.Append(GetPropertyValueForSQL(filterDTO, null, null));
}
break;
case FilterTypes.IsGreaterThanOrEqualTo:
current.Append(filterDTO.PropertyName + " >=");
if (forLinq)
{
current.Append(GetPropertyValue(filterDTO));
}
else
{
current.Append(GetPropertyValueForSQL(filterDTO, null, null));
}
break;
case FilterTypes.IsGreaterThan:
current.Append(filterDTO.PropertyName + " >");
if (forLinq)
{
current.Append(GetPropertyValue(filterDTO));
}
else
{
current.Append(GetPropertyValueForSQL(filterDTO, null, null));
}
break;
case FilterTypes.StartsWith:
current.Append(filterDTO.PropertyName);
if (forLinq)
{
current.AppendFormat(".StartsWith({0})", GetPropertyValue(filterDTO));
}
else
{
current.Append(GetPropertyValueForSQL(filterDTO, " like '", "%'"));
}
break;
case FilterTypes.EndsWith:
current.Append(filterDTO.PropertyName);
if (forLinq)
{
current.AppendFormat(".EndsWith({0})", GetPropertyValue(filterDTO));
}
else
{
current.Append(GetPropertyValueForSQL(filterDTO, " like '%", "'"));
}
break;
case FilterTypes.Contains:
current.Append(filterDTO.PropertyName);
if (forLinq)
{
current.AppendFormat(".Contains({0})", GetPropertyValue(filterDTO));
}
else
{
current.Append(GetPropertyValueForSQL(filterDTO, " like '%", "%'"));
}
break;
case FilterTypes.IsContainedIn:
throw new Exception("unknown filter type"); //not sure how to do this one!!!
case FilterTypes.DoesNotContain:
if (forLinq)
{
current.Append("!" + filterDTO.PropertyName);
current.AppendFormat(".Contains({0})", GetPropertyValue(filterDTO));
}
else
{
current.Append(filterDTO.PropertyName);
current.Append(GetPropertyValueForSQL(filterDTO, " not like '%", "%'"));
}
break;
default:
throw new Exception("unknown filter type");
}
current.Append(" ");
}
return(current);
}
