/// <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; }
/// <summary> /// GetConditionsForSummary /// </summary> /// <param name="fmType">fmType</param> /// <param name="companyId">companyId</param> /// <returns>Conditins for Summary</returns> public string GetConditionsForSummary(string fmType, int companyId) { string conditions = ""; foreach (FmViewTDS.FmViewConditionNewRow row in (FmViewTDS.FmViewConditionNewDataTable)Data.Tables["FmViewConditionNew"]) { if (!row.Deleted) { FmTypeViewCondition fmTypeViewCondition = new FmTypeViewCondition(); fmTypeViewCondition.LoadByFmTypeConditionId(fmType, companyId, row.ConditionID); FmTypeViewConditionGateway fmTypeViewConditionGateway = new FmTypeViewConditionGateway(fmTypeViewCondition.Data); if (row.Value_ == "") { conditions = conditions + "Condition" + row.ConditionNumber + ": " + fmTypeViewConditionGateway.GetName(fmType, companyId, row.ConditionID) + " " + row.Sign + " (empty), "; } else { conditions = conditions + "Condition" + row.ConditionNumber + ": " + fmTypeViewConditionGateway.GetName(fmType, companyId, row.ConditionID) + " " + row.Sign + " " + row.Value_ + ", "; } } } if (conditions.Length > 2) { conditions = conditions.Substring(0, conditions.Length - 2); } return conditions; }