private string modifyWhereClauseForCondition(string whereClause, int conditionId, string textForSearch, string operatorValue) { string workType = hdfWorkType.Value.Trim(); int companyId = Int32.Parse(hdfCompanyId.Value.Trim()); WorkTypeViewConditionGateway workTypeViewConditionGateway = new WorkTypeViewConditionGateway(); workTypeViewConditionGateway.LoadByWorkTypeConditionId(workType, conditionId, companyId); string name = workTypeViewConditionGateway.GetName(workType, companyId, conditionId); string conditionValue = workTypeViewConditionGateway.GetColumn_(workType, companyId, conditionId); string tableName = workTypeViewConditionGateway.GetTable_(workType, companyId, conditionId); if (tableName == "AM_ASSET_SEWER_MATERIAL") tableName = "AASM"; if (tableName == "AM_ASSET_SEWER_MH") tableName = "AASMH"; if (tableName == "AM_ASSET_SEWER_SECTION") tableName = "AASS"; if (tableName == "LFS_ASSET_SEWER_SECTION") tableName = "LASS"; if (tableName == "LFS_WORK") tableName = "LW"; if (tableName == "LFS_WORK_FULLLENGTHLINING") tableName = "LWFLL"; if (tableName == "LFS_WORK_FULLLENGTHLINING_M1") tableName = "LWFLLM1"; if (tableName == "LFS_WORK_FULLLENGTHLINING_M2") tableName = "LWFLLM2"; if (tableName == "LFS_WORK_FULLLENGTHLINING_P1") tableName = "LWFLP1"; if (tableName == "LFS_WORK_POINT_REPAIRS") tableName = "LWPR"; if (tableName == "LFS_WORK_POINT_REPAIRS_REPAIR") tableName = "LWPRR"; if (tableName == "LFS_WORK_REHABASSESSMENT") tableName = "LWRA"; if (((conditionValue == "USMH") || (conditionValue == "DSMH") || (conditionValue == "CXIsRemoved") || (conditionValue == "USMHAddress") || (conditionValue == "DSMHAddress") || (conditionValue == "VideoLength") || (conditionValue == "TrafficControl") || (conditionValue == "MaterialType") || (conditionValue == "RoboticPrepRequired")) && (textForSearch == "%")) { whereClause = whereClause + " AND (( AASS.AssetID LIKE '%')"; whereClause = whereClause + " OR ( AASS.AssetID IS NULL))"; } // FOR TEXT FIELDS. (Id(Section), SubArea, Street, Comments, MapSize, Size_, MapLength, Length, ClientID, MeasurementTakenBy, RoboticDistances, RepairPointID, Type, DefectQualifier, DefectDetails, Approval, ReamDistance, LinerDistance, Direction, LTMH, VTMH, Distance, MHShot, GroutDistance, Repair Comments, Repair Size, Repair Length ) if ((conditionValue == "FlowOrderID") || (conditionValue == "SubArea") || (conditionValue == "Street") || (conditionValue == "Comments" && name == "Comments") || (conditionValue == "MapSize") || (conditionValue == "Size_" && name == "Confirmed Size") || (conditionValue == "MapLength") || (conditionValue == "Length" && name == "Steel Tape Length") || (conditionValue == "ClientID") || (conditionValue == "MeasurementTakenBy") || (conditionValue == "RoboticDistances") || (conditionValue == "RepairPointID") || (conditionValue == "Type") || (conditionValue == "DefectQualifier") || (conditionValue == "DefectDetails") || (conditionValue == "Approval") || (conditionValue == "ReamDistance") || (conditionValue == "LinerDistance") || (conditionValue == "Direction") || (conditionValue == "LTMH") || (conditionValue == "VTMH") || (conditionValue == "Distance") || (conditionValue == "MHShot") || (conditionValue == "GroutDistance") || (conditionValue == "Comments" && name == "Repair Comments") || (conditionValue == "Size_" && name == "Repair Size") || (conditionValue == "Length" && name == "Repair Length")) { // ... For operator = if (operatorValue == "=") { // 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 + "%')"; } } } } else { // ... For operator <> if (operatorValue == "<>") { if (textForSearch == "") { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " LIKE '%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NOT 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 + " NOT LIKE '%" + textForSearch + "%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } else { textForSearch = textForSearch.Replace("\"", ""); whereClause = whereClause + "AND ((" + tableName + "." + conditionValue + " <> '" + textForSearch + "')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } } else { textForSearch = textForSearch.Replace("'", "''"); whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " NOT LIKE '%" + textForSearch + "%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } } } } } } else { if (((conditionValue == "USMH") || (conditionValue == "DSMH")) && (textForSearch == "%")) { whereClause = whereClause + " AND (( AASS.AssetID LIKE '%')"; whereClause = whereClause + " OR ( AASS.AssetID IS NULL))"; } } // FOR DATE FIELDS. (ProposedLiningDate, DeadlineLiningDate, FinalVideoDate, RepairConfirmationDate, ReamDate, InstallDate, GroutDate) if ((conditionValue == "ProposedLiningDate") || (conditionValue == "DeadlineLiningDate") || (conditionValue == "FinalVideoDate") || (conditionValue == "RepairConfirmationDate") || (conditionValue == "ReamDate") || (conditionValue == "InstallDate") || (conditionValue == "GroutDate")) { // ... For operator = if (operatorValue == "=") { // ... 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 + "%')"; } } } } else { // ... For operator <> if (operatorValue == "<>") { // ... Search if (textForSearch == "") { whereClause = whereClause + " AND (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NOT NULL)"; } else { if (textForSearch == "%") { whereClause = whereClause + " AND (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 + " OR (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NULL))"; } else { whereClause = whereClause + " AND ((CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) NOT LIKE '%" + textForSearch + "%')"; whereClause = whereClause + " OR (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NULL))"; } } } } else { // ... For other operators // ... Determine the operator string whereOperator = ""; if (operatorValue == "'>='") { whereOperator = ">="; } else { if (operatorValue == "'<='") { whereOperator = "<="; } else { whereOperator = operatorValue; } } // ... Determine the where clause if ((Validator.IsValidDate(textForSearch)) && (textForSearch.Length > 7)) { whereClause = whereClause + " AND (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime)" + whereOperator + " '" + textForSearch + "')"; } else { if (operatorValue == ">" || operatorValue == "'<='") { whereClause = whereClause + " AND (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime)" + whereOperator + " '12/31/" + textForSearch + "')"; } else { whereClause = whereClause + " AND (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime)" + whereOperator + " '" + textForSearch + "')"; } } } } } // ... FOR INTEGER AND DOUBLE FIELDS. (Laterals, LiveLaterals, EstimatedJoints, JointsTestSealed, Reinstates) if ((conditionValue == "Laterals") || (conditionValue == "LiveLaterals") || (conditionValue == "EstimatedJoints") || (conditionValue == "JointsTestSealed") || (conditionValue == "Reinstates")) { // ... For operator = if (operatorValue == "=") { if (textForSearch == "") { whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " IS NULL )"; } else { if (textForSearch == "%") { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " LIKE '%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } else { whereClause = whereClause + " AND (" + tableName + "." + conditionValue + operatorValue + textForSearch + ")"; } } } else { // ... For operator <> if (operatorValue == "<>") { if (textForSearch == "") { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " LIKE '%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NOT NULL))"; } else { if (textForSearch == "%") { whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " IS NULL )"; } else { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + operatorValue + textForSearch + ")"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } } } else { // ... For other operators // ... Determine the operator string whereOperator = ""; if (operatorValue == "'>='") { whereOperator = ">="; } else { if (operatorValue == "'<='") { whereOperator = "<="; } else { whereOperator = operatorValue; } } whereClause = whereClause + " AND (" + tableName + "." + conditionValue + whereOperator + textForSearch + ")"; } } } // FOR BOOLEAN FIELDS (BypassRequired, IssueIdentified, IssueLFS, IssueClient, IssueSales, IssueGivenToClient, IssueInvestigation, IssueResolved, ExtraRepair, Cancelled) if ((conditionValue == "BypassRequired") || (conditionValue == "IssueIdentified") || (conditionValue == "IssueLFS") || (conditionValue == "IssueClient") || (conditionValue == "IssueSales") || (conditionValue == "IssueGivenToClient") || (conditionValue == "IssueInvestigation") || (conditionValue == "IssueResolved") || (conditionValue == "ExtraRepair") || (conditionValue == "Cancelled")) { // ... For operator = if (operatorValue == "=") { 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))"; } } } } } else { // ... For operator <> if (operatorValue == "<>") { if (textForSearch != "") { if ((textForSearch.ToUpper() == "Y") || (textForSearch.ToUpper() == "YES")) { whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " = 0)"; } else { if ((textForSearch.ToUpper() == "N") || (textForSearch.ToUpper() == "NO")) { whereClause = whereClause + " AND(" + tableName + "." + conditionValue + " = 1)"; } else { if (textForSearch == "%") { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " = 1) OR (" + tableName + "." + conditionValue + " = 0))"; } } } } } } } return whereClause; }
private string modifyWhereClauseForCondition(string whereClause, int conditionId, string textForSearch, string operatorValue) { string workType = hdfWorkType.Value.Trim(); int companyId = Int32.Parse(hdfCompanyId.Value.Trim()); WorkTypeViewConditionGateway workTypeViewConditionGateway = new WorkTypeViewConditionGateway(); workTypeViewConditionGateway.LoadByWorkTypeConditionId(workType, conditionId, companyId); string conditionValue = workTypeViewConditionGateway.GetColumn_(workType, companyId, conditionId); string tableName = workTypeViewConditionGateway.GetTable_(workType, companyId, conditionId); if (tableName == "AM_ASSET_SEWER_SECTION") tableName = "AASS"; if (tableName == "AM_ASSET_SEWER_MH") tableName = "AASMH"; if (tableName == "LFS_WORK_MANHOLE_REHABILITATION_BATCH") tableName = "LWMRB"; if (tableName == "LFS_WORK_MANHOLE_REHABILITATION") tableName = "LWMR"; if (tableName == "LFS_ASSET_SEWER_MH") tableName = "LASMH"; if (tableName == "LFS_WORK") tableName = "LW"; // FOR TEXT FIELDS. (MHID, Address, Latitud, Comments, Longitude, ManholeShape, Location) if ((conditionValue == "MHID") || (conditionValue == "Address") || (conditionValue == "Latitud") || (conditionValue == "Comments") || (conditionValue == "Longitude") || (conditionValue == "ManholeShape") || (conditionValue == "Location")) { // ... For operator = if (operatorValue == "=") { // 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 + "%')"; } } } } else { // ... For operator <> if (operatorValue == "<>") { if (textForSearch == "") { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " LIKE '%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NOT 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 + " NOT LIKE '%" + textForSearch + "%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } else { textForSearch = textForSearch.Replace("\"", ""); whereClause = whereClause + "AND ((" + tableName + "." + conditionValue + " <> '" + textForSearch + "')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } } else { textForSearch = textForSearch.Replace("'", "''"); whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " NOT LIKE '%" + textForSearch + "%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } } } } } } // FOR DATE FIELDS. (PreppedDate, SprayedDate, Date) if ((conditionValue == "PreppedDate") || (conditionValue == "SprayedDate") || (conditionValue == "Date")) { // ... For operator = if (operatorValue == "=") { // ... 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 + "%')"; } } } } else { // ... For operator <> if (operatorValue == "<>") { // ... Search if (textForSearch == "") { whereClause = whereClause + " AND (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NOT NULL)"; } else { if (textForSearch == "%") { whereClause = whereClause + " AND (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 + " OR (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NULL))"; } else { whereClause = whereClause + " AND ((CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) NOT LIKE '%" + textForSearch + "%')"; whereClause = whereClause + " OR (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NULL))"; } } } } else { // ... For other operators // ... Determine the operator string whereOperator = ""; if (operatorValue == "'>='") { whereOperator = ">="; } else { if (operatorValue == "'<='") { whereOperator = "<="; } else { whereOperator = operatorValue; } } // ... Determine the where clause if ((Validator.IsValidDate(textForSearch)) && (textForSearch.Length > 7)) { whereClause = whereClause + " AND (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime)" + whereOperator + " '" + textForSearch + "')"; } else { if (operatorValue == ">" || operatorValue == "'<='") { whereClause = whereClause + " AND (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime)" + whereOperator + " '12/31/" + textForSearch + "')"; } else { whereClause = whereClause + " AND (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime)" + whereOperator + " '" + textForSearch + "')"; } } } } } // FOR INTEGER AND DOUBLE FIELDS. (ConditionRating) if (conditionValue == "ConditionRating") { // ... For operator = if (operatorValue == "=") { if (textForSearch == "") { whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " IS NULL )"; } else { if (textForSearch == "%") { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " LIKE '%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } else { whereClause = whereClause + " AND (" + tableName + "." + conditionValue + operatorValue + textForSearch + ")"; } } } else { // ... For operator <> if (operatorValue == "<>") { if (textForSearch == "") { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " LIKE '%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NOT NULL))"; } else { if (textForSearch == "%") { whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " IS NULL )"; } else { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + operatorValue + textForSearch + ")"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } } } else { // ... For other operators // ... Determine the operator string whereOperator = ""; if (operatorValue == "'>='") { whereOperator = ">="; } else { if (operatorValue == "'<='") { whereOperator = "<="; } else { whereOperator = operatorValue; } } whereClause = whereClause + " AND (" + tableName + "." + conditionValue + whereOperator + textForSearch + ")"; } } } return whereClause; }
/// <summary> /// ParserLogic /// </summary> /// <param name="originalLogic">originalLogic</param> /// <param name="workType">workType</param> /// <param name="companyId">companyId</param> /// <returns></returns> public string ParserLogic(string originalLogic, string workType, int companyId) { string newLogic = ""; if (workType == "Junction Lining") { if (originalLogic.Length > 0) { newLogic = "(AASS.Deleted = 0) AND (LW.Deleted = 0) AND (AASL.Deleted = 0) AND (AASS.COMPANY_ID = {0}) AND (LW.ProjectID = {1}) AND (LW.WorkType = '{2}') AND "; } else { newLogic = "(AASS.Deleted = 0) AND (LW.Deleted = 0) AND (AASL.Deleted = 0) AND (AASS.COMPANY_ID = {0}) AND (LW.ProjectID = {1}) AND (LW.WorkType = '{2}')"; } } else { if (workType == "Manhole Rehabilitation") { if (originalLogic.Length > 0) { newLogic = "(AASMHP.ProjectID = {1}) AND "; } else { newLogic = "(AASMHP.ProjectID = {1}) "; } } else { if (originalLogic.Length > 0) { newLogic = "(AASS.Deleted = 0) AND (LW.Deleted = 0) AND (AASS.COMPANY_ID = {0}) AND (LW.ProjectID = {1}) AND (LW.WorkType = '{2}') AND "; } else { newLogic = "(AASS.Deleted = 0) AND (LW.Deleted = 0) AND (AASS.COMPANY_ID = {0}) AND (LW.ProjectID = {1}) AND (LW.WorkType = '{2}')"; } } } WorkViewTDS.WorkViewConditionNewDataTable tableSorted = (WorkViewTDS.WorkViewConditionNewDataTable)Data.Tables["WorkViewConditionNew"]; foreach (WorkViewTDS.WorkViewConditionNewRow rowNew in tableSorted.Select("", "ConditionNumber DESC")) { if (!rowNew.Deleted) { string originalCondition = "Condition" + rowNew.ConditionNumber; WorkTypeViewCondition workTypeViewCondition = new WorkTypeViewCondition(); workTypeViewCondition.LoadByWorkTypeConditionId(workType, rowNew.ConditionID, companyId); WorkTypeViewConditionGateway workTypeViewConditionGateway = new WorkTypeViewConditionGateway(workTypeViewCondition.Data); string tableName = workTypeViewConditionGateway.GetTable_(workType, companyId, rowNew.ConditionID); if (tableName == "AM_ASSET_SEWER_SECTION") tableName = "AASS"; if (tableName == "AM_ASSET_SEWER_LATERAL") tableName = "AASL"; if (tableName == "LFS_ASSET_SEWER_SECTION") tableName = "LASS"; if (tableName == "LFS_ASSET_SEWER_LATERAL_CLIENT") tableName = "LASLC"; if (tableName == "LFS_WORK") tableName = "LW"; if (workType == "Rehab Assessment") { if (tableName == "LFS_WORK_FULLLENGTHLINING") tableName = "LWFLL"; } else { if (tableName == "LFS_WORK_FULLLENGTHLINING") tableName = "LWF"; } if (tableName == "LFS_WORK_FULLLENGTHLINING_M1") tableName = "LWFM1"; if (tableName == "LFS_WORK_FULLLENGTHLINING_M2") tableName = "LWFLLM2"; if (tableName == "LFS_WORK_FULLLENGTHLINING_P1") tableName = "LWFLP1"; if (tableName == "LFS_WORK_JUNCTIONLINING_SECTION") tableName = "LWJLS"; if (tableName == "LFS_WORK_JUNCTIONLINING_LATERAL") tableName = "LWJLL"; if (tableName == "LFS_WORK_REHABASSESSMENT") tableName = "LWR"; if (tableName == "LFS_WORK_POINT_REPAIRS") tableName = "LWPR"; if (tableName == "LFS_WORK_POINT_REPAIRS_REPAIR") tableName = "LWPRR"; if (tableName == "LFS_MIGRATED_SECTIONS") tableName = "LMS"; if (tableName == "LFS_WORK_MANHOLE_REHABILITATION") tableName = "LWMR"; if (tableName == "AM_ASSET_SEWER_MH") tableName = "AASMH"; string column = workTypeViewConditionGateway.GetColumn_(workType, companyId, rowNew.ConditionID); string type = workTypeViewConditionGateway.GetType(workType, companyId, rowNew.ConditionID); string sign = rowNew.Sign; string conditionValue = rowNew.Value_; string newCondition = ""; if (column == "USMH") { column = "MHID"; tableName = "AASUSMH"; } if (column == "DSMH") { column = "MHID"; tableName = "AASDSMH"; } if (column == "MN#") { column = "Address"; } if (column == "USMHAddress") { column = "Address"; tableName = "AASUSMH"; } if (column == "DSMHAddress") { column = "Address"; tableName = "AASDSMH"; } // Search if (conditionValue == "%") { if (type == "Boolean") { newCondition = newCondition + " ((" + tableName + "." + column + " = 1) OR (" + tableName + "." + column + " = 0))"; } else { if ((workType == "Junction Lining") && ((column == "FlowOrderID") || (column == "LateralID"))) { if (column == "FlowOrderID") { newCondition = newCondition + " (((" + tableName + "." + column + " LIKE '%')"; newCondition = newCondition + " OR (" + tableName + "." + column + " IS NULL))"; newCondition = newCondition + " OR ((AASL.LateralID LIKE '%')"; newCondition = newCondition + " OR (AASL.LateralID IS NULL)))"; } else { if (column == "LateralID") { newCondition = newCondition + " ((AASS.SectionID LIKE '%')"; newCondition = newCondition + " OR (AASS.SectionID IS NULL))"; } } } else { 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 { if (column != "CXIsRemoved") { if (sign == "=") { newCondition = newCondition + " ((" + tableName + "." + column + " LIKE '%')"; newCondition = newCondition + " OR (" + tableName + "." + column + " IS NULL))"; } if (sign == "<>") { newCondition = newCondition + " (" + tableName + "." + column + " IS NULL)"; } else { if (sign != "=") { newCondition = newCondition + " ((" + tableName + "." + column + " LIKE '%')"; newCondition = newCondition + " OR (" + tableName + "." + column + " IS NULL))"; } } } } } } } else { if (conditionValue == "") { if (sign == "<>") { if ((workType == "Junction Lining") && ((column == "FlowOrderID") || (column == "LateralID"))) { if (column == "FlowOrderID") { newCondition = newCondition + " ((" + tableName + "." + column + " IS NOT NULL)"; newCondition = newCondition + " OR (AASL.LateralID IS NOT NULL))"; } else { if (column == "LateralID") { newCondition = newCondition + " (AASS.SectionID IS NOT NULL)"; } } } else { if (column != "CXIsRemoved") { newCondition = newCondition + tableName + "." + column + " IS NOT NULL "; } else { newCondition = newCondition + " (AASS.AssetID IN " + " (SELECT AASS1.AssetID" + " FROM AM_ASSET_SEWER_SECTION AASS1 INNER JOIN " + " LFS_WORK LW1 ON (AASS1.AssetID = LW1.AssetID) AND (LW1.WorkType = 'Full Length Lining') AND (LW1.ProjectID = LW.ProjectID) INNER JOIN " + " LFS_WORK_FULLLENGTHLINING_P1 LWFLP1 ON LW1.WorkID = LWFLP1.WorkID INNER JOIN LFS_WORK LW2 ON (AASS1.AssetID = LW2.AssetID) AND (LW2.WorkType = 'Rehab Assessment') AND (LW2.ProjectID = LW1.ProjectID) " + " WHERE ( (LWFLP1.CXIsRemoved IS NOT NULL) AND (AASS1.Deleted = 0) AND (LW1.Deleted = 0) AND (LWFLP1.Deleted = 0)) " + " ) )"; } } } else { if ((workType == "Junction Lining") && ((column == "FlowOrderID") || (column == "LateralID"))) { if (column == "FlowOrderID") { newCondition = newCondition + " ((" + tableName + "." + column + " IS NULL)"; newCondition = newCondition + " OR (AASL.LateralID IS NULL))"; } if (column == "LateralID") { newCondition = newCondition + " (AASS.SectionID IS NULL)"; } } else { if (column != "CXIsRemoved") { newCondition = newCondition + tableName + "." + column + " IS NULL "; } else { newCondition = newCondition + " (AASS.AssetID IN " + " (SELECT AASS1.AssetID" + " FROM AM_ASSET_SEWER_SECTION AASS1 INNER JOIN " + " LFS_WORK LW1 ON (AASS1.AssetID = LW1.AssetID) AND (LW1.WorkType = 'Full Length Lining') AND (LW1.ProjectID = LW.ProjectID) INNER JOIN " + " LFS_WORK_FULLLENGTHLINING_P1 LWFLP1 ON LW1.WorkID = LWFLP1.WorkID INNER JOIN LFS_WORK LW2 ON (AASS1.AssetID = LW2.AssetID) AND (LW2.WorkType = 'Rehab Assessment') AND (LW2.ProjectID = LW1.ProjectID) " + " WHERE ( (LWFLP1.CXIsRemoved IS NULL) AND (AASS1.Deleted = 0) AND (LW1.Deleted = 0) AND (LWFLP1.Deleted = 0)) " + " ) )"; } } } } else { conditionValue = conditionValue.Replace("'", "''"); if ((type == "Int") || (type == "Decimal") || (type == "Boolean")) { if (conditionValue == "Yes") conditionValue = "1"; if (conditionValue == "No") conditionValue = "0"; if (column != "CXIsRemoved") { newCondition = newCondition + tableName + "." + column + sign + conditionValue; } else { newCondition = newCondition + " (AASS.AssetID IN " + " (SELECT AASS1.AssetID" + " FROM AM_ASSET_SEWER_SECTION AASS1 INNER JOIN " + " LFS_WORK LW1 ON (AASS1.AssetID = LW1.AssetID) AND (LW1.WorkType = 'Full Length Lining') AND (LW1.ProjectID = LW.ProjectID) INNER JOIN " + " LFS_WORK_FULLLENGTHLINING_P1 LWFLP1 ON LW1.WorkID = LWFLP1.WorkID INNER JOIN LFS_WORK LW2 ON (AASS1.AssetID = LW2.AssetID) AND (LW2.WorkType = 'Rehab Assessment') AND (LW2.ProjectID = LW1.ProjectID) " + " WHERE ( (LWFLP1.CXIsRemoved " + sign + conditionValue + ") AND (AASS1.Deleted = 0) AND (LW1.Deleted = 0) AND (LWFLP1.Deleted = 0)) " + " ) )"; } } else { if (sign == "=") { if ((workType == "Junction Lining") && ((column == "FlowOrderID") || (column == "LateralID"))) { if (column == "FlowOrderID") { newCondition = newCondition + " ((" + tableName + "." + column + " LIKE '%" + conditionValue + "%')"; newCondition = newCondition + " OR (AASL.LateralID LIKE '%" + conditionValue + "%'))"; } else { if (column == "LateralID") { newCondition = newCondition + "(AASS.FlowOrderID + '-JL-' + AASL.LateralID LIKE '%" + conditionValue + "%')"; } } } else { if (type != "Date") { if (conditionValue.Contains("\"")) { if (column != "Gasket") { if (column != "Size_" && column != "VideoLengthToPropertyLine" && column != "DepthOfLocated") { conditionValue = conditionValue.Replace("\"", ""); } } newCondition = newCondition + " (" + tableName + "." + column + " = '" + conditionValue + "')"; } else { if (column == "Issue" && conditionValue == "(All)") { newCondition = newCondition + " ((LWJLL.Issue LIKE '%')"; newCondition = newCondition + " OR (LWJLL.Issue IS NULL))"; } 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 ((workType == "Junction Lining") && (column == "FlowOrderID")) { newCondition = newCondition + " ((" + tableName + "." + column + sign + "'" + conditionValue + "')"; newCondition = newCondition + " OR (AASL.LateralID " + sign + "'" + conditionValue + "'))"; } else { if ((workType == "Junction Lining") && (column == "LateralID")) { newCondition = newCondition + " ((AASS.FlowOrderID + '-JL-' + AASL.LateralID NOT LIKE '%" + conditionValue + "%')"; newCondition = newCondition + "OR (AASS.FlowOrderID + '-JL-' + AASL.LateralID IS NULL))"; } else { if (column == "Comments" || column == "History") { 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, string operatorValue) { string workType = hdfWorkType.Value.Trim(); int companyId = Int32.Parse(hdfCompanyId.Value.Trim()); WorkTypeViewConditionGateway workTypeViewConditionGateway = new WorkTypeViewConditionGateway(); workTypeViewConditionGateway.LoadByWorkTypeConditionId(workType, conditionId, companyId); string conditionValue = workTypeViewConditionGateway.GetColumn_(workType, companyId, conditionId); string tableName = workTypeViewConditionGateway.GetTable_(workType, companyId, conditionId); if (tableName == "AM_ASSET_SEWER_SECTION") tableName = "AASS"; if (tableName == "AM_ASSET_SEWER_LATERAL") tableName = "AASL"; if (tableName == "LFS_ASSET_SEWER_SECTION") tableName = "LASS"; if (tableName == "LFS_WORK_JUNCTIONLINING_SECTION") tableName = "LWJLS"; if (tableName == "LFS_WORK_JUNCTIONLINING_LATERAL") tableName = "LWJLL"; if (tableName == "LFS_WORK") tableName = "LW"; if (tableName == "LFS_ASSET_SEWER_LATERAL_CLIENT") tableName = "LASLC"; if (tableName == "LFS_MIGRATED_SECTIONS") tableName = "LMS"; if (conditionValue == "MN#") conditionValue = "Address"; // ... FOR TEXT FIELDS if ((conditionValue == "SubArea") || (conditionValue == "Street") || (conditionValue == "FlowOrderID") || (conditionValue == "OriginalSectionID") || (conditionValue == "CoPitLocation") || (conditionValue == "LinerSize") || (conditionValue == "Address") || (conditionValue == "Comments") || (conditionValue == "History") || (conditionValue == "Issue") || (conditionValue == "ClientLateralID") || (conditionValue == "HamiltonInspectionNumber") || (conditionValue == "LateralID") || (conditionValue == "ConnectionType") || (conditionValue == "Flange") || (conditionValue == "Gasket") || (conditionValue == "PrepType") || (conditionValue == "LinerType") || (conditionValue == "ContractYear")) { // ... For operator = if (operatorValue == "=") { // Search if (textForSearch == "%") { if (conditionValue != "LateralID") { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " LIKE '%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } else { whereClause = whereClause + " AND ((AASS.SectionID LIKE '%')"; whereClause = whereClause + " OR (AASS.SectionID IS NULL))"; } } else { if (textForSearch == "") { if (conditionValue != "LateralID") { whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " IS NULL )"; } else { whereClause = whereClause + " AND (AASS.SectionID IS NULL)"; } } else { if (textForSearch.Contains("\"")) { if ((conditionValue == "Comments") || (conditionValue == "History")) { textForSearch = textForSearch.Replace("'", "''"); whereClause = whereClause + "AND (" + tableName + "." + conditionValue + " LIKE '%" + textForSearch + "%')"; } else { if (conditionValue != "Gasket") { textForSearch = textForSearch.Replace("\"", ""); } if (conditionValue != "LateralID") { whereClause = whereClause + "AND (" + tableName + "." + conditionValue + " = '" + textForSearch + "')"; } else { whereClause = whereClause + "AND (AASS.FlowOrderID + '-JL-' + AASL.LateralID = " + textForSearch + ")"; } } } else { textForSearch = textForSearch.Replace("'", "''"); if (conditionValue != "LateralID") { whereClause = whereClause + "AND (" + tableName + "." + conditionValue + " LIKE '%" + textForSearch + "%')"; } else { whereClause = whereClause + "AND (AASS.FlowOrderID + '-JL-' + AASL.LateralID LIKE '%" + textForSearch + "%')"; } } } } } else { // ... For operator <> if (operatorValue == "<>") { if (textForSearch == "") { if (conditionValue != "LateralID") { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " LIKE '%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NOT NULL))"; } else { whereClause = whereClause + " AND ((AASS.SectionID LIKE '%')"; whereClause = whereClause + " OR (AASS.SectionID IS NOT NULL))"; } } else { if (textForSearch == "%") { if (conditionValue != "LateralID") { whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " IS NULL)"; } else { whereClause = whereClause + " AND (AASS.SectionID IS NULL)"; } } else { if (textForSearch.Contains("\"")) { if ((conditionValue == "Comments") || (conditionValue == "History")) { textForSearch = textForSearch.Replace("'", "''"); whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " NOT LIKE '%" + textForSearch + "%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } else { textForSearch = textForSearch.Replace("\"", ""); if (conditionValue != "LateralID") { whereClause = whereClause + "AND ((" + tableName + "." + conditionValue + " <> '" + textForSearch + "')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } else { whereClause = whereClause + "AND (( AASS.FlowOrderID + '-JL-' + AASL.LateralID <> " + textForSearch + ")"; whereClause = whereClause + "OR (AASS.FlowOrderID + '-JL-' + AASL.LateralID IS NULL))"; } } } else { textForSearch = textForSearch.Replace("'", "''"); if (conditionValue != "LateralID") { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " NOT LIKE '%" + textForSearch + "%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } else { whereClause = whereClause + "AND ((AASS.FlowOrderID + '-JL-' + AASL.LateralID NOT LIKE '%" + textForSearch + "%')"; whereClause = whereClause + "OR (AASS.FlowOrderID + '-JL-' + AASL.LateralID IS NULL))"; } } } } } } } else { if (((conditionValue == "USMH") || (conditionValue == "DSMH")) && (textForSearch == "%")) { whereClause = whereClause + " AND (( AASS.AssetID LIKE '%')"; whereClause = whereClause + " OR ( AASS.AssetID IS NULL))"; } } // FOR DATE FIELDS if ((conditionValue == "PipeLocated") || (conditionValue == "ServicesLocated") || (conditionValue == "CoInstalled") || (conditionValue == "BackfilledConcrete") || (conditionValue == "BackfilledSoil") || (conditionValue == "Grouted") || (conditionValue == "Cored") || (conditionValue == "Prepped") || (conditionValue == "Measured") || (conditionValue == "InProcess") || (conditionValue == "InStock") || (conditionValue == "Delivered") || (conditionValue == "PreVideo") || (conditionValue == "LinerInstalled") || (conditionValue == "FinalVideo") || (conditionValue == "CoCutDown") || (conditionValue == "VideoInspection") || (conditionValue == "FinalRestoration") || (conditionValue == "NoticeDelivered") || (conditionValue == "DigRequiredPriorToLiningCompleted") || (conditionValue == "DigRequiredAfterLiningCompleted") || (conditionValue == "HoldClientIssueResolved") || (conditionValue == "HoldLFSIssueResolved") || (conditionValue == "LateralRequiresRoboticPrepCompleted") || (conditionValue == "DyeTestComplete")) { // ... For operator = if (operatorValue == "=") { // ... 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 + "%')"; } } } } else { // ... For operator <> if (operatorValue == "<>") { // ... Search if (textForSearch == "") { whereClause = whereClause + " AND (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NOT NULL)"; } else { if (textForSearch == "%") { whereClause = whereClause + " AND (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 + " OR (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NULL))"; } else { whereClause = whereClause + " AND ((CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) NOT LIKE '%" + textForSearch + "%')"; whereClause = whereClause + " OR (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NULL))"; } } } } else { // ... For other operators // ... Determine the operator string whereOperator = ""; if (operatorValue == "'>='") { whereOperator = ">="; } else { if (operatorValue == "'<='") { whereOperator = "<="; } else { whereOperator = operatorValue; } } // ... Determine the where clause if ((Validator.IsValidDate(textForSearch)) && (textForSearch.Length > 7)) { whereClause = whereClause + " AND (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime)" + whereOperator + " '" + textForSearch + "')"; } else { if (operatorValue == ">" || operatorValue == "'<='") { whereClause = whereClause + " AND (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime)" + whereOperator + " '12/31/" + textForSearch + "')"; } else { whereClause = whereClause + " AND (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime)" + whereOperator + " '" + textForSearch + "')"; } } } } } // ... FOR BOOLEAN FIELDS if ((conditionValue == "CoRequired") || (conditionValue == "PitRequired") || (conditionValue == "PostContractDigRequired") || (conditionValue == "LiningThruCo") || (conditionValue == "DigRequiredPriorToLining") || (conditionValue == "DigRequiredAfterLining") || (conditionValue == "OutOfScope") || (conditionValue == "HoldClientIssue") || (conditionValue == "HoldLFSIssue") || (conditionValue == "LateralRequiresRoboticPrep") || (conditionValue == "DyeTestReq")) { // ... For operator = if (operatorValue == "=") { 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))"; } } } } } else { // ... For operator <> if (operatorValue == "<>") { if (textForSearch != "") { if ((textForSearch.ToUpper() == "Y") || (textForSearch.ToUpper() == "YES")) { whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " = 0)"; } else { if ((textForSearch.ToUpper() == "N") || (textForSearch.ToUpper() == "NO")) { whereClause = whereClause + " AND(" + tableName + "." + conditionValue + " = 1)"; } else { if (textForSearch == "%") { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " = 1) OR (" + tableName + "." + conditionValue + " = 0))"; } } } } } } } // ... FOR INTEGER AND DOUBLE FIELDS. (Build / Rebuild #, Cost) if ((conditionValue == "BuildRebuild") || (conditionValue == "Cost")) { // ... For operator = if (operatorValue == "=") { if (textForSearch == "") { whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " IS NULL )"; } else { if (textForSearch == "%") { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " LIKE '%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } else { whereClause = whereClause + " AND (" + tableName + "." + conditionValue + operatorValue + textForSearch + ")"; } } } else { // ... For operator <> if (operatorValue == "<>") { if (textForSearch == "") { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " LIKE '%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NOT NULL))"; } else { if (textForSearch == "%") { whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " IS NULL )"; } else { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + operatorValue + textForSearch + ")"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } } } else { // ... For other operators // ... Determine the operator string whereOperator = ""; if (operatorValue == "'>='") { whereOperator = ">="; } else { if (operatorValue == "'<='") { whereOperator = "<="; } else { whereOperator = operatorValue; } } whereClause = whereClause + " AND (" + tableName + "." + conditionValue + whereOperator + textForSearch + ")"; } } } // ... FOR DISTANCE FIELDS (Video Length To PL, Depth Of Pipe, Main Size) if ((conditionValue == "VideoLengthToPropertyLine") || (conditionValue == "DepthOfLocated") || (conditionValue == "Size_")) { string textForSearch4 = textForSearch; // ... For operator = if (operatorValue == "=") { if (textForSearch == "") { whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " IS NULL )"; } else { if (textForSearch == "%") { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " LIKE '%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } else { try { if (Convert.ToDouble(textForSearch) > 99) { double newMainSize = Convert.ToDouble(textForSearch) * 0.03937; textForSearch = Convert.ToString(Math.Ceiling(newMainSize)) + "\""; } } catch { } string textForSearch2 = textForSearch; string textForSearch3 = textForSearch; if (textForSearch.Contains("\"")) { textForSearch2 = textForSearch2.Replace("\"", ""); textForSearch3 = textForSearch3.Replace("\"", "in"); } else { if (!textForSearch.Contains("in")) { textForSearch2 = textForSearch2 + "\""; textForSearch3 = textForSearch3 + "in"; } else { textForSearch2 = textForSearch2.Replace("in", "\""); textForSearch3 = textForSearch3.Replace("in", ""); } } whereClause = whereClause + "AND ((" + tableName + "." + conditionValue + " = N'" + textForSearch + "') OR (" + tableName + "." + conditionValue + " = N'" + textForSearch2 + "') OR (" + tableName + "." + conditionValue + " = N'" + textForSearch3 + "') OR (" + tableName + "." + conditionValue + " = N'" + textForSearch4 + "') )"; } } } else { // ... For operator <> if (operatorValue == "<>") { if (textForSearch == "") { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " LIKE '%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NOT NULL))"; } else { if (textForSearch == "%") { whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " IS NULL )"; } else { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + operatorValue + "'" +textForSearch + "'" + ")"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } } } else { // ... For other operators // ... Determine the operator string whereOperator = ""; if (operatorValue == "'>='") { whereOperator = ">="; } else { if (operatorValue == "'<='") { whereOperator = "<="; } else { whereOperator = operatorValue; } } whereClause = whereClause + " AND (" + tableName + "." + conditionValue + whereOperator + textForSearch + ")"; } } } return whereClause; }
private string modifyWhereClauseForCondition(string whereClause, int conditionId, string textForSearch) { string workType = hdfWorkType.Value.Trim(); int companyId = Int32.Parse(hdfCompanyId.Value.Trim()); WorkTypeViewConditionGateway workTypeViewConditionGateway = new WorkTypeViewConditionGateway(); workTypeViewConditionGateway.LoadByWorkTypeConditionId(workType, conditionId, companyId); string conditionValue = workTypeViewConditionGateway.GetColumn_(workType, companyId, conditionId); string tableName = workTypeViewConditionGateway.GetTable_(workType, companyId, conditionId); if (tableName == "AM_ASSET_SEWER_SECTION") tableName = "AASS"; if (tableName == "LFS_WORK_REHABASSESSMENT") tableName = "LWR"; if (tableName == "LFS_ASSET_SEWER_SECTION") tableName = "LASS"; if (tableName == "LFS_WORK") tableName = "LW"; if (tableName == "LFS_MIGRATED_SECTIONS") tableName = "LMS"; if (tableName == "LFS_WORK_FULLLENGTHLINING") tableName = "LWFLL"; if (tableName == "LFS_WORK_FULLLENGTHLINING_M1") tableName = "LWFM1"; if (tableName == "LFS_WORK_FULLLENGTHLINING_M2") tableName = "LWFLLM2"; if (tableName == "LFS_WORK_FULLLENGTHLINING_P1") tableName = "LWFLP1"; if (conditionValue == "USMHAddress") { tableName = "AASUSMH"; conditionValue = "Address"; } if (conditionValue == "DSMHAddress") { tableName = "AASDSMH"; conditionValue = "Address"; } // FOR TEXT FIELDS. (SubArea, Street, Id(Section), OriginalSectionID, Comments) if ((conditionValue == "SubArea") || (conditionValue == "Street") || (conditionValue == "FlowOrderID") || (conditionValue == "OriginalSectionID") || (conditionValue == "Comments") || (conditionValue == "MapSize") || (conditionValue == "Size_") || (conditionValue == "MapLength") || (conditionValue == "Length") || (conditionValue == "VideoLength") || (conditionValue == "ClientID") || (conditionValue == "MeasurementTakenBy") || (conditionValue == "Address") || (conditionValue == "USMHDepth") || (conditionValue == "USMHMouth12") || (conditionValue == "USMHMouth1") || (conditionValue == "USMHMouth2") || (conditionValue == "USMHMouth3") || (conditionValue == "USMHMouth4") || (conditionValue == "USMHMouth5") || (conditionValue == "DSMHDepth") || (conditionValue == "DSMHMouth12") || (conditionValue == "DSMHMouth1") || (conditionValue == "DSMHMouth2") || (conditionValue == "DSMHMouth3") || (conditionValue == "DSMHMouth4") || (conditionValue == "DSMHMouth5") || (conditionValue == "TrafficControl") || (conditionValue == "SiteDetails") || (conditionValue == "StandardBypassComments") || (conditionValue == "TrafficControlDetails") || (conditionValue == "MeasurementType") || (conditionValue == "MeasurementFromMH") || (conditionValue == "VideoDoneFromMH") || (conditionValue == "VideoDoneToMH") || (conditionValue == "Thickness")) { // ... 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 DATE FIELDS. (PreFlushDate, PreVideoDate, P1Date, M1Date, RoboticPrepCompletedDate) if ((conditionValue == "PreFlushDate") || (conditionValue == "PreVideoDate") || (conditionValue == "P1Date") || (conditionValue == "M1Date") || (conditionValue == "RoboticPrepCompletedDate")) { // ... 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 + "%')"; } } } } // FOR INTEGER AND DOUBLE FIELDS. (Laterals, LiveLaterals, CXIsRemoved) if ((conditionValue == "Laterals") || (conditionValue == "LiveLaterals") || (conditionValue == "CXIsRemoved")) { string operatorValue = "="; // ... For operator = if (textForSearch == "") { whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " IS NULL )"; } else { if (textForSearch == "%") { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " LIKE '%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } else { whereClause = whereClause + " AND (" + tableName + "." + conditionValue + operatorValue + textForSearch + ")"; } } } // FOR BOOLEAN FIELDS if ((conditionValue == "PipeSizeChange") || (conditionValue == "StandardBypass") || (conditionValue == "RoboticPrepCompleted")) { 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 string modifyWhereClauseForCondition(string whereClause, int conditionId, string textForSearch, string operatorValue) { string workType = hdfWorkType.Value.Trim(); int companyId = Int32.Parse(hdfCompanyId.Value.Trim()); WorkTypeViewConditionGateway workTypeViewConditionGateway = new WorkTypeViewConditionGateway(); workTypeViewConditionGateway.LoadByWorkTypeConditionId(workType, conditionId, companyId); string conditionValue = workTypeViewConditionGateway.GetColumn_(workType, companyId, conditionId); string tableName = workTypeViewConditionGateway.GetTable_(workType, companyId, conditionId); if (tableName == "AM_ASSET_SEWER_SECTION") tableName = "AASS"; if (tableName == "LFS_WORK_REHABASSESSMENT") tableName = "LWR"; if (tableName == "LFS_ASSET_SEWER_SECTION") tableName = "LASS"; if (tableName == "LFS_WORK") tableName = "LW"; if (tableName == "LFS_MIGRATED_SECTIONS") tableName = "LMS"; if (tableName == "LFS_WORK_FULLLENGTHLINING") tableName = "LWF"; if (tableName == "LFS_WORK_FULLLENGTHLINING_M1") tableName = "LWFM1"; if (tableName == "LFS_WORK_FULLLENGTHLINING_M1_LATERAL") tableName = "LWFLLM1L"; if (tableName == "LFS_WORK_FULLLENGTHLINING_M2") tableName = "LWFLLM2"; if (tableName == "LFS_WORK_FULLLENGTHLINING_P1") tableName = "LWFLP1"; if (conditionValue == "USMHAddress") { tableName = "AASUSMH"; conditionValue = "Address"; } if (conditionValue == "DSMHAddress") { tableName = "AASDSMH"; conditionValue = "Address"; } // FOR TEXT FIELDS. (SubArea, Street, Id(Section), OriginalSectionID, Comments, .....) if ((conditionValue == "SubArea") || (conditionValue == "Street") || (conditionValue == "FlowOrderID") || (conditionValue == "OriginalSectionID") || (conditionValue == "Comments") || (conditionValue == "MapSize") || (conditionValue == "Size_") || (conditionValue == "MapLength") || (conditionValue == "Length") || (conditionValue == "VideoLength") || (conditionValue == "ClientID") || (conditionValue == "MeasurementTakenBy") || (conditionValue == "Address") || (conditionValue == "USMHDepth") || (conditionValue == "USMHMouth12") || (conditionValue == "USMHMouth1") || (conditionValue == "USMHMouth2") || (conditionValue == "USMHMouth3") || (conditionValue == "USMHMouth4") || (conditionValue == "USMHMouth5") || (conditionValue == "DSMHDepth") || (conditionValue == "DSMHMouth12") || (conditionValue == "DSMHMouth1") || (conditionValue == "DSMHMouth2") || (conditionValue == "DSMHMouth3") || (conditionValue == "DSMHMouth4") || (conditionValue == "DSMHMouth5") || (conditionValue == "TrafficControl") || (conditionValue == "SiteDetails") || (conditionValue == "StandardBypassComments") || (conditionValue == "TrafficControlDetails") || (conditionValue == "MeasurementType") || (conditionValue == "MeasurementFromMH") || (conditionValue == "VideoDoneFromMH") || (conditionValue == "VideoDoneToMH") || (conditionValue == "Thickness")) { // ... For operator = if (operatorValue == "=") { // 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 + "%')"; } } } } else { // ... For operator <> if (operatorValue == "<>") { if (textForSearch == "") { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " LIKE '%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NOT 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 + " NOT LIKE '%" + textForSearch + "%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } else { textForSearch = textForSearch.Replace("\"", ""); whereClause = whereClause + "AND ((" + tableName + "." + conditionValue + " <> '" + textForSearch + "')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } } else { textForSearch = textForSearch.Replace("'", "''"); whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " NOT LIKE '%" + textForSearch + "%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } } } } } } // FOR DATE FIELDS. (ProposedLiningDate, DeadlineLiningDate, P1Date, M1Date, M2Date, InstallDate, FinalVideoDate, PreFlushDate and PreVideoDate) if ((conditionValue == "ProposedLiningDate") || (conditionValue == "DeadlineLiningDate") || (conditionValue == "P1Date") || (conditionValue == "M1Date") || (conditionValue == "M2Date") || (conditionValue == "InstallDate") || (conditionValue == "FinalVideoDate") || (conditionValue == "PreFlushDate") || (conditionValue == "Reinstate") || (conditionValue == "TimeOpened") || (conditionValue == "PreVideoDate") || (conditionValue == "RoboticPrepCompletedDate")) { // ... For operator = if (operatorValue == "=") { // ... 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 + "%')"; } } } } else { // ... For operator <> if (operatorValue == "<>") { // ... Search if (textForSearch == "") { whereClause = whereClause + " AND (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NOT NULL)"; } else { if (textForSearch == "%") { whereClause = whereClause + " AND (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 + " OR (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NULL))"; } else { whereClause = whereClause + " AND ((CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) NOT LIKE '%" + textForSearch + "%')"; whereClause = whereClause + " OR (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NULL))"; } } } } else { // ... For other operators // ... Determine the operator string whereOperator = ""; if (operatorValue == "'>='") { whereOperator = ">="; } else { if (operatorValue == "'<='") { whereOperator = "<="; } else { whereOperator = operatorValue; } } // ... Determine the where clause if ((Validator.IsValidDate(textForSearch)) && (textForSearch.Length > 7)) { whereClause = whereClause + " AND (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime)" + whereOperator + " '" + textForSearch + "')"; } else { if (operatorValue == ">" || operatorValue == "'<='") { whereClause = whereClause + " AND (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime)" + whereOperator + " '12/31/" + textForSearch + "')"; } else { whereClause = whereClause + " AND (CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime)" + whereOperator + " '" + textForSearch + "')"; } } } } } // FOR BOOLEAN FIELDS if ((conditionValue == "IssueIdentified") || (conditionValue == "IssueLFS") || (conditionValue == "IssueClient") || (conditionValue == "IssueSales") || (conditionValue == "IssueGivenToClient") || (conditionValue == "IssueInvestigation") || (conditionValue == "IssueResolved") || (conditionValue == "PipeSizeChange") || (conditionValue == "StandardBypass") || (conditionValue == "DropPipe") || (conditionValue == "HydroPulley") || (conditionValue == "FridgeCart") || (conditionValue == "TwoPump") || (conditionValue == "SixBypass") || (conditionValue == "Scaffolding") || (conditionValue == "WinchExtention") || (conditionValue == "ExtraGenerator") || (conditionValue == "GreyCableExtension") || (conditionValue == "EasementMats") || (conditionValue == "RampRequired") || (conditionValue == "CameraSkid") || (conditionValue == "RoboticPrepCompleted")) { // ... For operator = if (operatorValue == "=") { 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))"; } } } } } else { // ... For operator <> if (operatorValue == "<>") { if (textForSearch != "") { if ((textForSearch.ToUpper() == "Y") || (textForSearch.ToUpper() == "YES")) { whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " = 0)"; } else { if ((textForSearch.ToUpper() == "N") || (textForSearch.ToUpper() == "NO")) { whereClause = whereClause + " AND(" + tableName + "." + conditionValue + " = 1)"; } else { if (textForSearch == "%") { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " = 1) OR (" + tableName + "." + conditionValue + " = 0))"; } } } } } } } // FOR INTEGER AND DOUBLE FIELDS. (Laterals, LiveLaterals, CXIsRemoved and CappedLaterals) if ((conditionValue == "Laterals") || (conditionValue == "LiveLaterals") || (conditionValue == "CXIsRemoved") || (conditionValue == "CappedLaterals")) { // ... For operator = if (operatorValue == "=") { if (textForSearch == "") { whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " IS NULL )"; } else { if (textForSearch == "%") { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " LIKE '%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } else { whereClause = whereClause + " AND (" + tableName + "." + conditionValue + operatorValue + textForSearch + ")"; } } } else { // ... For operator <> if (operatorValue == "<>") { if (textForSearch == "") { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " LIKE '%')"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NOT NULL))"; } else { if (textForSearch == "%") { whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " IS NULL )"; } else { whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + operatorValue + textForSearch + ")"; whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))"; } } } else { // ... For other operators // ... Determine the operator string whereOperator = ""; if (operatorValue == "'>='") { whereOperator = ">="; } else { if (operatorValue == "'<='") { whereOperator = "<="; } else { whereOperator = operatorValue; } } whereClause = whereClause + " AND (" + tableName + "." + conditionValue + whereOperator + textForSearch + ")"; } } } return whereClause; }
private string modifyWhereClauseForCondition(string whereClause, int conditionId, string textForSearch) { int companyId = Int32.Parse(hdfCompanyId.Value.Trim()); WorkTypeViewConditionGateway workTypeViewConditionGateway = new WorkTypeViewConditionGateway(); workTypeViewConditionGateway.LoadByWorkTypeConditionId("Junction Lining Section", conditionId, companyId); string conditionValue = workTypeViewConditionGateway.GetColumn_("Junction Lining Section", companyId, conditionId); string tableName = workTypeViewConditionGateway.GetTable_("Junction Lining Section", companyId, conditionId); if (tableName == "AM_ASSET_SEWER_SECTION") tableName = "AASS"; if ((tableName == "AM_ASSET_SEWER_MH") && (conditionValue == "USMH")) { conditionValue = "MHID"; tableName = "AASUSMH"; } if ((tableName == "AM_ASSET_SEWER_MH") && (conditionValue == "DSMH")) { conditionValue = "MHID"; tableName = "AASDSMH"; } if (tableName == "LFS_WORK_JUNCTIONLINING_SECTION") tableName = "LWJLS"; if (tableName == "LFS_ASSET_SEWER_SECTION") tableName = "LASS"; if (tableName == "LFS_MIGRATED_SECTIONS") tableName = "LMS"; // ... FOR TEXT FIELDS if ((conditionValue == "FlowOrderID") || (conditionValue == "SubArea") || (conditionValue == "Street") || (conditionValue == "OriginalSectionID") || (conditionValue == "MHID")) { // 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 { textForSearch = textForSearch.Replace("'", "''"); whereClause = whereClause + "AND (" + tableName + "." + conditionValue + " LIKE '%" + textForSearch + "%')"; } } } return whereClause; }