private string modifyWhereClauseForCondition(string whereClause, int conditionId, string textForSearch)
{
int companyId = Int32.Parse(hdfCompanyId.Value.Trim());
string resourceType = hdfResourceType.Value;
ResourceTypeViewConditionGateway resourceTypeViewConditionGateway = new ResourceTypeViewConditionGateway();
resourceTypeViewConditionGateway.LoadByResourceTypeConditionId(resourceType, companyId, conditionId);
string conditionValue = resourceTypeViewConditionGateway.GetColumn_(resourceType, companyId, conditionId);
string tableName = resourceTypeViewConditionGateway.GetTable_(resourceType, companyId, conditionId);
if (tableName == "LFS_RESOURCES_EMPLOYEE") tableName = "LRE";
if (tableName == "LFS_RESOURCES_EMPLOYEE_TYPE") tableName = "LRET";
if (tableName == "LFS_RESOURCES_EMPLOYEE_STATE") tableName = "LRES";
if (tableName == "LFS_RESOURCES_EMPLOYEE_COST_HISTORY") tableName = "LRECH";
// FOR TEXT FIELDS. (FirstName, LastName, Email, Type, State, JobClassType)
if ((conditionValue == "FirstName") || (conditionValue == "LastName") || (conditionValue == "eMail") || (conditionValue == "Description") || (conditionValue == "Type") || (conditionValue == "State") || (conditionValue == "Category") || (conditionValue == "JobClassType") || (conditionValue == "PersonalAgencyName"))
{
// ... Search
if (textForSearch == "%")
{
whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " LIKE '%')";
whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))";
}
else
{
if (textForSearch == "")
{
whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " IS NULL )";
}
else
{
if (textForSearch.Contains("\""))
{
if (conditionValue == "Comments")
{
textForSearch = textForSearch.Replace("'", "''");
whereClause = whereClause + "AND (" + tableName + "." + conditionValue + " LIKE '%" + textForSearch + "%')";
}
else
{
textForSearch = textForSearch.Replace("\"", "");
whereClause = whereClause + "AND (" + tableName + "." + conditionValue + " = '" + textForSearch + "')";
}
}
else
{
textForSearch = textForSearch.Replace("'", "''");
whereClause = whereClause + "AND (" + tableName + "." + conditionValue + " LIKE '%" + textForSearch + "%')";
}
}
}
}
// FOR BOOLEAN FIELDS
if ((conditionValue == "IsSalesman") || (conditionValue == "ApproveTimesheets") || (conditionValue == "RequestProjectTime") || (conditionValue == "Salaried") || (conditionValue == "AssignableSRS") || (conditionValue == "IsVacationsManager"))
{
if (textForSearch != "")
{
if ((textForSearch.ToUpper() == "Y") || (textForSearch.ToUpper() == "YES"))
{
whereClause = whereClause + " AND (" + conditionValue + " = 1)";
}
else
{
if ((textForSearch.ToUpper() == "N") || (textForSearch.ToUpper() == "NO"))
{
whereClause = whereClause + " AND (" + conditionValue + " = 0)";
}
else
{
if (textForSearch == "%")
{
whereClause = whereClause + " AND ((" + conditionValue + " = 1) OR (" + conditionValue + " = 0))";
}
}
}
}
}
return whereClause;
}
private EmployeeNavigatorTDS SubmitSearch()
{
// Retrieve clauses
string whereClause = GetWhereClause();
string orderByClause = GetOrderByClause();
string conditionValue = "";
EmployeeNavigator employeeNavigator = new EmployeeNavigator();
int companyId = Int32.Parse(hdfCompanyId.Value.Trim());
string resourceType = hdfResourceType.Value.Trim();
// ... Load data
ResourceTypeViewConditionGateway resourceTypeViewConditionGateway = new ResourceTypeViewConditionGateway();
resourceTypeViewConditionGateway.LoadByResourceTypeConditionId(resourceType, companyId, int.Parse(ddlCondition1.SelectedValue));
conditionValue = resourceTypeViewConditionGateway.GetColumn_(resourceType, companyId, int.Parse(ddlCondition1.SelectedValue));
employeeNavigator.Load(whereClause, orderByClause, conditionValue, tbxCondition1.Text.Trim(), companyId, resourceType);
return (EmployeeNavigatorTDS)employeeNavigator.Data;
}
private string modifyWhereClauseForCondition(string whereClause, int conditionId, string textForSearch)
{
int companyId = Int32.Parse(hdfCompanyId.Value.Trim());
string resourceType = hdfResourceType.Value;
ResourceTypeViewConditionGateway resourceTypeViewConditionGateway = new ResourceTypeViewConditionGateway();
resourceTypeViewConditionGateway.LoadByResourceTypeConditionId(resourceType, companyId, conditionId);
string conditionValue = resourceTypeViewConditionGateway.GetColumn_(resourceType, companyId, conditionId);
string tableName = resourceTypeViewConditionGateway.GetTable_(resourceType, companyId, conditionId);
if (tableName == "LFS_RESOURCES_MATERIAL") tableName = "LRM";
if (tableName == "LFS_RESOURCES_MATERIAL_COST_HISTORY") tableName = "LRMCH";
// FOR TEXT FIELDS. (Type, Description, Size, Length, Thickness, State)
if ((conditionValue == "Type") || (conditionValue == "Description") || (conditionValue == "Size") || (conditionValue == "Length") || (conditionValue == "Thickness") || (conditionValue == "State"))
{
// ... Search
if (textForSearch == "%")
{
whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " LIKE '%')";
whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))";
}
else
{
if (textForSearch == "")
{
whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " IS NULL )";
}
else
{
if (textForSearch.Contains("\""))
{
if (conditionValue == "Comments")
{
textForSearch = textForSearch.Replace("'", "''");
whereClause = whereClause + "AND (" + tableName + "." + conditionValue + " LIKE '%" + textForSearch + "%')";
}
else
{
textForSearch = textForSearch.Replace("\"", "");
whereClause = whereClause + "AND (" + tableName + "." + conditionValue + " = '" + textForSearch + "')";
}
}
else
{
textForSearch = textForSearch.Replace("'", "''");
whereClause = whereClause + "AND (" + tableName + "." + conditionValue + " LIKE '%" + textForSearch + "%')";
}
}
}
}
return whereClause;
}