private string modifyWhereClauseForCondition(string whereClause, int conditionId, string textForSearch)
{
int companyId = Int32.Parse(hdfCompanyId.Value.Trim());
string fmType = hdfFmType.Value;
FmTypeViewConditionGateway fmTypeViewConditionGateway = new FmTypeViewConditionGateway();
fmTypeViewConditionGateway.LoadByFmTypeConditionId(fmType, companyId, conditionId);
string conditionValue = fmTypeViewConditionGateway.GetColumn_(fmType, companyId, conditionId);
string conditionName = fmTypeViewConditionGateway.GetName(fmType, companyId, conditionId);
string tableName = fmTypeViewConditionGateway.GetTable_(fmType, companyId, conditionId);
if (tableName == "LFS_FM_TODOLIST") tableName = "LFTDL";
if (tableName == "LFS_EMPLOYEE") tableName = "LRE";
if (tableName == "LFS_FM_UNIT") tableName = "LFU";
if (tableName == "LFS_FM_TODOLIST_ACTIVITY") tableName = "LFTDLA";
if (conditionName == "Created By") tableName = "LEOwner";
// FOR TEXT FIELDS. (Subject, Unit Code)
if ((conditionValue == "Subject") || (conditionValue == "UnitCode"))
{
// ... 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 == "LastComment")
{
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 DATE FIELDS. (CreationDate, DueDate)
if ((conditionValue == "CreationDate") || (conditionValue == "DueDate"))
{
// ... Search
if (textForSearch == "")
{
whereClause = whereClause + " AND ( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NULL)";
}
else
{
if (textForSearch == "%")
{
whereClause = whereClause + " AND (( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NOT NULL) OR ";
whereClause = whereClause + "( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NULL))";
}
else
{
if ((Validator.IsValidDate(textForSearch)) && (textForSearch.Length > 7))
{
whereClause = whereClause + " AND ( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) = '" + textForSearch + "')";
}
else
{
whereClause = whereClause + " AND ( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) LIKE '%" + textForSearch + "%')";
}
}
}
}
string state = ddlState.SelectedValue;
if (state != "(All)")
{
if (state != "New & In Progress")
{
whereClause = whereClause + "AND (LFTDL.State = '" + state + "')";
}
else
{
whereClause = whereClause + "AND (LFTDL.State = 'New' OR LFTDL.State = 'In Progress')";
}
}
return whereClause;
}
private string modifyWhereClauseForCondition(string whereClause, int conditionId, string textForSearch)
{
int companyId = Int32.Parse(hdfCompanyId.Value.Trim());
string fmType = hdfFmType.Value;
FmTypeViewConditionGateway fmTypeViewConditionGateway = new FmTypeViewConditionGateway();
fmTypeViewConditionGateway.LoadByFmTypeConditionId(fmType, companyId, conditionId);
string conditionValue = fmTypeViewConditionGateway.GetColumn_(fmType, companyId, conditionId);
string conditionName = fmTypeViewConditionGateway.GetName(fmType, companyId, conditionId);
string tableName = fmTypeViewConditionGateway.GetTable_(fmType, companyId, conditionId);
if (tableName == "LFS_FM_SERVICE") tableName = "LFS";
if (tableName == "LFS_FM_SERVICE_VEHICLE") tableName = "LFSV";
if (tableName == "LFS_FM_UNIT") tableName = "LFU";
if (tableName == "LFS_FM_RULE") tableName = "LFR";
if (tableName == "LFS_FM_CHECKLIST") tableName = "LFC";
if (tableName == "LFS_FM_COMPANYLEVEL") tableName = "LFCL";
if (conditionName == "Created By") tableName = "LEOwner";
if (conditionName == "Assigned To") tableName = "LEAssignedTo";
// FOR TEXT FIELDS. (Service Number, Unit Code, Unit Description, Notes, Created by, Assigned to, Checklist Rules)
if ((conditionValue == "Number") || (conditionValue == "UnitCode") || (conditionValue == "Description") || (conditionValue == "VIN") || (conditionValue == "Notes") || (conditionValue == "FullName") || (conditionValue == "Name") || (conditionValue == "VIN") || (conditionValue == "State") || (conditionValue == "Mileage") || (conditionValue == "StartWorkOutOfServiceTime") || (conditionValue == "StartWorkMileage") || (conditionValue == "CompleteWorkBackToServiceTime") || (conditionValue == "CompleteWorkMileage") || (conditionValue == "CompleteWorkDetailDescription"))
{
// ... 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 == "Notes")
{
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 DATE FIELDS. (StartWorkDateTime, CompleteWorkDateTime, AssignDeadlineDate)
if ((conditionValue == "StartWorkDateTime") || (conditionValue == "CompleteWorkDateTime") || (conditionValue == "AssignDeadlineDate") || (conditionValue == "DateTime_") || (conditionValue == "AssignDateTime") || (conditionValue == "AcceptDateTime") || (conditionValue == "StartWorkOutOfServiceDate") || (conditionValue == "CompleteWorkBackToServiceDate"))
{
// ... Search
if (textForSearch == "")
{
whereClause = whereClause + " AND ( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NULL)";
}
else
{
if (textForSearch == "%")
{
whereClause = whereClause + " AND (( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NOT NULL) OR ";
whereClause = whereClause + "( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NULL))";
}
else
{
if ((Validator.IsValidDate(textForSearch)) && (textForSearch.Length > 7))
{
//whereClause = whereClause + " AND ( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) = '" + textForSearch + "')";
whereClause = whereClause + " AND ( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) LIKE '%" + textForSearch + "%')";
}
else
{
whereClause = whereClause + " AND ( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) LIKE '%" + textForSearch + "%')";
}
}
}
}
// FOR BOOLEAN FIELDS
if ((conditionValue == "MTO") || (conditionValue == "CompleteWorkDetailPreventable"))
{
if (textForSearch != "")
{
if ((textForSearch.ToUpper() == "Y") || (textForSearch.ToUpper() == "YES"))
{
whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " = 1)";
}
else
{
if ((textForSearch.ToUpper() == "N") || (textForSearch.ToUpper() == "NO"))
{
whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " = 0)";
}
else
{
if (textForSearch == "%")
{
whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " = 1) OR (" + tableName + "." + conditionValue + " = 0))";
}
}
}
}
}
// FOR DECIMAL FIELDS
if (conditionValue == "CompleteWorkDetailTMLabourHours")
{
// ... 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
{
whereClause = whereClause + " AND (" + tableName + "." + conditionValue + "=" + textForSearch + ")";
}
}
}
return whereClause;
}
/// <summary>
/// ParserLogic
/// </summary>
/// <param name="originalLogic">originalLogic</param>
/// <param name="fmType">fmType</param>
/// <param name="companyId">companyId</param>
/// <returns>parser logic string</returns>
public string ParserLogic(string originalLogic, string fmType, int companyId)
{
string newLogic = "";
if (fmType == "Services")
{
if (originalLogic.Length > 0)
{
newLogic = "(LFS.Deleted = 0) AND (LFU.Deleted = 0) AND (LFS.COMPANY_ID = {0}) AND (LFU.COMPANY_ID = {0}) AND ";
}
else
{
newLogic = "(LFS.Deleted = 0) AND (LFU.Deleted = 0) AND (LFS.COMPANY_ID = {0}) AND (LFU.COMPANY_ID = {0})";
}
}
if (fmType == "Units")
{
if (originalLogic.Length > 0)
{
newLogic = "(FMU.Deleted = 0) AND (FMU.State <> 'Archived') AND (FMC.Deleted = 0) AND (FMU.COMPANY_ID = {0}) AND (FMC.COMPANY_ID = {0}) AND ";
}
else
{
newLogic = "(FMU.Deleted = 0) AND (FMU.State <> 'Archived') AND (FMC.Deleted = 0) AND (FMU.COMPANY_ID = {0}) AND (FMU.COMPANY_ID = {0})";
}
}
foreach (FmViewTDS.FmViewConditionNewRow rowNew in (FmViewTDS.FmViewConditionNewDataTable)Data.Tables["FmViewConditionNew"])
{
if (!rowNew.Deleted)
{
string originalCondition = "Condition" + rowNew.ConditionNumber;
FmTypeViewCondition fmTypeViewCondition = new FmTypeViewCondition();
fmTypeViewCondition.LoadByFmTypeConditionId(fmType, companyId, rowNew.ConditionID);
FmTypeViewConditionGateway fmTypeViewConditionGateway = new FmTypeViewConditionGateway(fmTypeViewCondition.Data);
string tableName = fmTypeViewConditionGateway.GetTable_(fmType, companyId, rowNew.ConditionID);
string conditionName = fmTypeViewConditionGateway.GetName(fmType, companyId, rowNew.ConditionID);
string column = fmTypeViewConditionGateway.GetColumn_(fmType, companyId, rowNew.ConditionID);
if (fmType == "Services")
{
switch (tableName)
{
case "LFS_FM_SERVICE":
tableName = "LFS";
break;
case "LFS_FM_SERVICE_VEHICLE":
tableName = "LFSV";
break;
case "LFS_FM_UNIT":
tableName = "LFU";
break;
case "LFS_FM_RULE":
tableName = "LFR";
break;
case "LFS_FM_CHECKLIST":
tableName = "LFC";
break;
case "LFS_FM_COMPANYLEVEL":
tableName = "LFCL";
break;
}
if (conditionName == "Created By") tableName = "LEOwner";
if (conditionName == "Assigned To") tableName = "LEAssignedTo";
}
if (fmType == "Units")
{
switch (tableName)
{
case "LFS_FM_UNIT":
tableName = "FMU";
break;
case "LFS_FM_COMPANYLEVEL":
if (column == "CompanyLevel")
{
column = "Name";
tableName = "FMC";
}
break;
case "LFS_FM_UNIT_VEHICLE":
tableName = "FMUV";
break;
case "LFS_COUNTRY":
if (column == "LicenseCountry")
{
tableName = "LCL";
}
if (column == "OwnerCountry")
{
tableName = "LCO";
}
column = "Name";
break;
case "LFS_PROVINCE":
if (column == "LicenseState")
{
tableName = "LPL";
}
if (column == "OwnerState")
{
tableName = "LPO";
}
column = "Name";
break;
}
}
string type = fmTypeViewConditionGateway.GetType(fmType, companyId, rowNew.ConditionID);
string sign = rowNew.Sign;
string conditionValue = rowNew.Value_;
string newCondition = "";
// Search
if (conditionValue == "%")
{
if (type == "Date")
{
if (sign == "=")
{
newCondition = newCondition + " ((CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime) IS NOT NULL)";
newCondition = newCondition + " OR (CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime) IS NULL))";
}
else
{
if (sign == "<>")
{
newCondition = newCondition + " (CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime) IS NULL)";
}
else
{
newCondition = newCondition + " ((CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime) IS NOT NULL)";
newCondition = newCondition + " OR (CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime) IS NULL))";
}
}
}
else
{
newCondition = newCondition + " ((" + tableName + "." + column + " LIKE '%')";
newCondition = newCondition + " OR (" + tableName + "." + column + " IS NULL))";
}
}
else
{
if (conditionValue == "")
{
if (sign == "<>")
{
newCondition = newCondition + tableName + "." + column + " IS NOT NULL ";
}
else
{
newCondition = newCondition + tableName + "." + column + " IS NULL ";
}
}
else
{
conditionValue = conditionValue.Replace("'", "''");
if ((type == "Int") || (type == "Decimal") || (type == "Boolean"))
{
if (type == "Boolean")
{
if (conditionValue == "Yes") conditionValue = "1";
if (conditionValue == "No") conditionValue = "0";
if (fmType == "Units")
{
if (column != "WithAlarms" && column != "WithServicesLate" && column != "WithChecklistInUnknownState")
{
newCondition = newCondition + tableName + "." + column + sign + conditionValue;
}
else
{
if (column == "WithAlarms")
{
if (conditionValue == "1")
{
newCondition = newCondition + " (FMU.UnitID IN " +
" (SELECT DISTINCT FMU1.UnitID " +
" FROM LFS_FM_UNIT FMU1 INNER JOIN " +
" LFS_FM_CHECKLIST FMCL1 ON FMCL1.UnitID = FMU1.UnitID INNER JOIN " +
" LFS_FM_RULE FMR1 ON FMCL1.RuleID = FMR1.RuleID " +
" WHERE (FMR1.Alarm = 1) AND (FMCL1.State = 'Warning' OR FMCL1.State = 'Expired') AND (FMCL1.Deleted = 0) AND (FMU1.Deleted = 0) AND (FMR1.Deleted = 0)" +
" ) )";
}
if (conditionValue == "0")
{
newCondition = newCondition + " (FMU.UnitID NOT IN " +
" (SELECT DISTINCT FMU1.UnitID " +
" FROM LFS_FM_UNIT FMU1 INNER JOIN " +
" LFS_FM_CHECKLIST FMCL1 ON FMCL1.UnitID = FMU1.UnitID INNER JOIN " +
" LFS_FM_RULE FMR1 ON FMCL1.RuleID = FMR1.RuleID " +
" WHERE (FMCL1.State = 'Warning' OR FMCL1.State = 'Expired') AND (FMCL1.Deleted = 0) AND (FMU1.Deleted = 0) AND (FMR1.Deleted = 0)" +
" ) )";
}
}
if (column == "WithServicesLate")
{
if (conditionValue == "1")
{
newCondition = newCondition + " (FMU.UnitID IN " +
" (SELECT DISTINCT FMU1.UnitID " +
" FROM LFS_FM_UNIT FMU1 INNER JOIN " +
" LFS_FM_SERVICE FMS1 ON FMU1.UnitID = FMS1.UnitID " +
" WHERE CONVERT(VARCHAR(10), FMS1.AssignDeadlineDate, 101) < CONVERT(VARCHAR(10), getdate(), 101) AND (FMS1.CompleteWorkDateTime IS NULL)" +
" AND (FMS1.State <> 'Unassigned') AND (FMS1.State <> 'Completed') AND (FMS1.State <> 'Rejected') OR (FMS1.State = 'Assigned/Expired') OR (FMS1.State = 'In Progress/Expired') " +
" OR (FMS1.CompleteWorkDateTime IS NOT NULL) AND (FMS1.CompleteWorkDateTime > FMS1.AssignDeadlineDate) " +
" ) )";
}
if (conditionValue == "0")
{
newCondition = newCondition + " (FMU.UnitID NOT IN " +
" (SELECT DISTINCT FMU1.UnitID " +
" FROM LFS_FM_UNIT FMU1 INNER JOIN " +
" LFS_FM_SERVICE FMS1 ON FMU1.UnitID = FMS1.UnitID " +
" WHERE CONVERT(VARCHAR(10), FMS1.AssignDeadlineDate, 101) < CONVERT(VARCHAR(10), getdate(), 101) AND (FMS1.CompleteWorkDateTime IS NULL)" +
" AND (FMS1.State <> 'Unassigned') AND (FMS1.State <> 'Completed') AND (FMS1.State <> 'Rejected') OR (FMS1.State = 'Assigned/Expired') OR (FMS1.State = 'In Progress/Expired') " +
" OR (FMS1.CompleteWorkDateTime IS NOT NULL) AND (FMS1.CompleteWorkDateTime > FMS1.AssignDeadlineDate) " +
" ) )";
}
}
if (column == "WithChecklistInUnknownState")
{
if (conditionValue == "1")
{
newCondition = newCondition + " (FMU.UnitID IN " +
" (SELECT DISTINCT FMU1.UnitID " +
" FROM LFS_FM_UNIT FMU1 INNER JOIN " +
" LFS_FM_CHECKLIST FMCL1 ON FMU1.UnitID = FMCL1.UnitID " +
" WHERE (FMU1.Deleted = 0) AND (FMCL1.Deleted = 0) AND (FMCL1.State = 'Unknown') " +
" ) )";
}
if (conditionValue == "0")
{
newCondition = newCondition + " (FMU.UnitID NOT IN " +
" (SELECT DISTINCT FMU1.UnitID " +
" FROM LFS_FM_UNIT FMU1 INNER JOIN " +
" LFS_FM_CHECKLIST FMCL1 ON FMU1.UnitID = FMCL1.UnitID " +
" WHERE (FMU1.Deleted = 0) AND (FMCL1.Deleted = 0) AND (FMCL1.State = 'Unknown') " +
" ) )";
}
}
}
}
else
{
newCondition = newCondition + tableName + "." + column + sign + conditionValue;
}
}
else
{
newCondition = newCondition + tableName + "." + column + sign + conditionValue;
}
}
else
{
if (sign == "=")
{
if (type != "Date")
{
if (conditionValue.Contains("\""))
{
conditionValue = conditionValue.Replace("\"", "");
newCondition = newCondition + " (" + tableName + "." + column + " = '" + conditionValue + "')";
}
else
{
if (column == "Categories")
{
ArrayList categoriesId = new ArrayList();
categoriesId = GetConditionForCategory(conditionValue, companyId);
newCondition = newCondition + " (FMU.UnitID IN " +
" (SELECT DISTINCT FMU1.UnitID " +
" FROM LFS_FM_UNIT FMU1 INNER JOIN " +
" LFS_FM_UNIT_CATEGORY FMUC ON FMU1.UnitID = FMUC.UnitID " +
" WHERE (FMU1.Deleted = 0) AND (FMUC.Deleted = 0) AND ";
int cantOfCategory = categoriesId.Count;
int auxOfCantOfCategory = 0;
foreach (int categoryId in categoriesId)
{
auxOfCantOfCategory++;
if (auxOfCantOfCategory != cantOfCategory)
{
newCondition = newCondition + " (FMUC.CategoryID = " + categoryId + " ) OR ";
}
else
{
newCondition = newCondition + " (FMUC.CategoryID = " + categoryId + " ) ";
}
}
newCondition = newCondition + " ) )";
}
else
{
newCondition = newCondition + " (" + tableName + "." + column + " LIKE '%" + conditionValue + "%')";
}
}
}
else
{
// Date
if (conditionValue.Length > 7)
{
newCondition = newCondition + " CAST(CONVERT(varchar,"+ tableName + "." + column +", 101) AS smalldatetime) "+ sign + "'" + conditionValue + "'";
}
else
{
newCondition = newCondition + " CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime) LIKE '%" + conditionValue + "%'";
}
}
}
else
{
if (column == "Notes")
{
newCondition = newCondition + tableName + "." + column + " NOT LIKE '%" + conditionValue + "%'";
}
else
{
if (type != "Date")
{
newCondition = newCondition + tableName + "." + column + sign + "'" + conditionValue + "'";
}
else
{
// Date
if (conditionValue.Length > 7)
{
if (sign == "<>")
{
newCondition = newCondition + " ((CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime) " + sign + "'" + conditionValue + "')";
newCondition = newCondition + " OR (CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime) IS NULL))";
}
else
{
newCondition = newCondition + " CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime) " + sign + "'" + conditionValue + "'";
}
}
else
{
if (sign == "<>")
{
newCondition = newCondition + " ((CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime) NOT LIKE '%" + conditionValue + "%')";
newCondition = newCondition + " OR (CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime) IS NULL))";
}
else
{
if (sign == ">" || sign == "<=")
{
newCondition = newCondition + " CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime)" + sign + " '12/31/" + conditionValue + "'";
}
else
{
newCondition = newCondition + " CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime)" + sign + " '" + conditionValue + "'";
}
}
}
}
}
}
}
}
}
originalLogic = originalLogic.Replace(originalCondition, newCondition);
}
}
if (originalLogic.Length > 0)
{
newLogic = newLogic + originalLogic;
}
return newLogic;
}
private string modifyWhereClauseForCondition(string whereClause, int conditionId, string textForSearch)
{
int companyId = Int32.Parse(hdfCompanyId.Value.Trim());
string fmType = hdfFmType.Value;
FmTypeViewConditionGateway fmTypeViewConditionGateway = new FmTypeViewConditionGateway();
fmTypeViewConditionGateway.LoadByFmTypeConditionId(fmType, companyId, conditionId);
string conditionValue = fmTypeViewConditionGateway.GetColumn_(fmType, companyId, conditionId);
string tableName = fmTypeViewConditionGateway.GetTable_(fmType, companyId, conditionId);
switch (tableName)
{
case "LFS_FM_UNIT":
tableName = "FMU";
break;
case "LFS_FM_UNIT_COST_HISTORY":
tableName = "FMUCH";
break;
case "LFS_FM_UNIT_VEHICLE":
tableName = "FMUV";
break;
case "LFS_FM_COMPANYLEVEL":
if (conditionValue == "CompanyLevel")
{
conditionValue = "Name";
tableName = "FMC";
}
break;
case "LFS_COUNTRY":
if (conditionValue == "LicenseCountry")
{
tableName = "LCL";
}
if (conditionValue == "OwnerCountry")
{
tableName = "LCO";
}
conditionValue = "Name";
break;
case "LFS_PROVINCE":
if (conditionValue == "LicenseState")
{
tableName = "LPL";
}
if (conditionValue == "OwnerState")
{
tableName = "LPO";
}
conditionValue = "Name";
break;
}
// FOR TEXT FIELDS.
if ((conditionValue == "UnitCode") || (conditionValue == "VIN") || (conditionValue == "Description") || (conditionValue == "State") || (conditionValue == "Manufacturer") || (conditionValue == "Model") || (conditionValue == "Year_") || (conditionValue == "Categories") || (conditionValue == "Name") || (conditionValue == "OwnerType") || (conditionValue == "Notes") || (conditionValue == "LicenseCountry") || (conditionValue == "LicenseState") || (conditionValue == "LicensePlateNumbver") || (conditionValue == "AportionedTagNumber") || (conditionValue == "ActualWeight") || (conditionValue == "RegisteredWeight") || (conditionValue == "TireSizeFront") || (conditionValue == "TireSizeBack") || (conditionValue == "NumberOfAxes") || (conditionValue == "FuelType") || (conditionValue == "BeginningOdometer") || (conditionValue == "OwnerCountry") || (conditionValue == "OwnerState") || (conditionValue == "OwnerName") || (conditionValue == "OwnerContact") || (conditionValue == "NotQualifiedExplain") || (conditionValue == "InsuranceClass") || (conditionValue == "InsuranceClassRyderSpecified"))
{
// ... 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 == "Notes")
{
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 == "IsTowable") || (conditionValue == "IsReeferEquipped") || (conditionValue == "IsPTOEquipped"))
{
if (textForSearch != "")
{
if ((textForSearch.ToUpper() == "Y") || (textForSearch.ToUpper() == "YES"))
{
whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " = 1)";
}
else
{
if ((textForSearch.ToUpper() == "N") || (textForSearch.ToUpper() == "NO"))
{
whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " = 0)";
}
else
{
if (textForSearch == "%")
{
whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " = 1) OR (" + tableName + "." + conditionValue + " = 0))";
}
}
}
}
}
// FOR DATE FIELDS. (AcquisitionDate, QualifiedDate, NotQualifiedDate)
if ((conditionValue == "AcquisitionDate") || (conditionValue == "QualifiedDate") || (conditionValue == "NotQualifiedDate"))
{
// ... Search
if (textForSearch == "")
{
whereClause = whereClause + " AND ( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NULL)";
}
else
{
if (textForSearch == "%")
{
whereClause = whereClause + " AND (( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NOT NULL) OR ";
whereClause = whereClause + "( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NULL))";
}
else
{
if ((Validator.IsValidDate(textForSearch)) && (textForSearch.Length > 7))
{
whereClause = whereClause + " AND ( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) = '" + textForSearch + "')";
}
else
{
whereClause = whereClause + " AND ( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) LIKE '%" + textForSearch + "%')";
}
}
}
}
return whereClause;
}