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 GetOrderByClause() { // For tableName string workType = hdfWorkType.Value.Trim(); int companyId = Int32.Parse(hdfCompanyId.Value.Trim()); WorkTypeViewSortGateway workTypeViewSortGateway = new WorkTypeViewSortGateway(); workTypeViewSortGateway.LoadByWorkTypeSortId(workType, companyId, int.Parse(ddlSortBy.SelectedValue)); string tableName = workTypeViewSortGateway.GetTable_(workType, companyId, int.Parse(ddlSortBy.SelectedValue)); string columnName = workTypeViewSortGateway.GetColumn_(workType, companyId, int.Parse(ddlSortBy.SelectedValue)); WorkTypeViewConditionGateway workTypeViewConditionGateway = new WorkTypeViewConditionGateway(); workTypeViewConditionGateway.LoadByWorkTypeConditionId(workType, int.Parse(ddlCondition1.SelectedValue), companyId); string conditionValue = workTypeViewConditionGateway.GetColumn_(workType, companyId, int.Parse(ddlCondition1.SelectedValue)); 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"; string orderBy = ""; if (columnName == "Date") { switch (conditionValue) { case "VideoInspection": orderBy = "LWJLL.VideoInspection DESC"; break; case "PipeLocated": orderBy = "LWJLL.PipeLocated DESC"; break; case "ServicesLocated": orderBy = "LWJLL.ServicesLocated DESC"; break; case "CoInstalled": orderBy = "LWJLL.CoInstalled DESC"; break; case "BackfilledConcrete": orderBy = "LWJLL.BackfilledConcrete DESC"; break; case "BackfilledSoil": orderBy = "LWJLL.BackfilledSoil DESC"; break; case "Grouted": orderBy = "LWJLL.Grouted DESC"; break; case "Cored": orderBy = "LWJLL.Cored DESC"; break; case "Prepped": orderBy = "LWJLL.Prepped DESC"; break; case "Measured": orderBy = "LWJLL.Measured DESC"; break; case "InProcess": orderBy = "LWJLL.InProcess DESC"; break; case "InStock": orderBy = "LWJLL.InStock DESC"; break; case "Delivered": orderBy = "LWJLL.Delivered DESC"; break; case "PreVideo": orderBy = "LWJLL.PreVideo DESC"; break; case "LinerInstalled": orderBy = "LWJLL.LinerInstalled DESC"; break; case "FinalVideo": orderBy = "LWJLL.FinalVideo DESC"; break; case "CoCutDown": orderBy = "LWJLL.CoCutDown DESC"; break; case "FinalRestoration": orderBy = "LWJLL.FinalRestoration DESC"; break; case "NoticeDelivered": orderBy = "LWJLL.NoticeDelivered DESC"; break; default: orderBy = "AASS.FlowOrderID ASC"; break; } } else { orderBy = tableName + "." + columnName; } return orderBy; }
private string GetOrderByClause() { string workType = hdfWorkType.Value.Trim(); int companyId = Int32.Parse(hdfCompanyId.Value.Trim()); WorkTypeViewSortGateway workTypeViewSortGateway = new WorkTypeViewSortGateway(); workTypeViewSortGateway.LoadByWorkTypeSortId(workType, companyId, int.Parse(ddlSortBy.SelectedValue)); string tableName = workTypeViewSortGateway.GetTable_(workType, companyId, int.Parse(ddlSortBy.SelectedValue)); string columnName = workTypeViewSortGateway.GetColumn_(workType, companyId, int.Parse(ddlSortBy.SelectedValue)); WorkTypeViewConditionGateway workTypeViewConditionGateway = new WorkTypeViewConditionGateway(); workTypeViewConditionGateway.LoadByWorkTypeConditionId(workType, int.Parse(ddlCondition1.SelectedValue), companyId); string conditionValue1 = workTypeViewConditionGateway.GetColumn_(workType, companyId, int.Parse(ddlCondition1.SelectedValue)); if (tableName == "AM_ASSET_SEWER_SECTION") tableName = "AASS"; if (tableName == "LFS_ASSET_SEWER_SECTION") tableName = "LASS"; if (tableName == "LFS_WORK_POINT_REPAIRS") tableName = "LWPR"; // Get order by clause string orderBy = ""; if (columnName == "Date") { switch (conditionValue1) { case "ProposedLiningDate": orderBy = "LWPR.ProposedLiningDate DESC"; break; case "DeadlineLiningDate": orderBy = "LWPR.DeadlineLiningDate DESC"; break; case "FinalVideoDate": orderBy = "LWPR.FinalVideoDate DESC"; break; default: orderBy = "LWPR.WorkID ASC"; break; } } else { orderBy = tableName + "." + columnName; } return orderBy; }
protected void grdConditions_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.Footer) { int conditionId = 0; conditionId = int.Parse(((DropDownList)e.Row.FindControl("ddlName")).SelectedValue); DropDownList ddlOperator = ((DropDownList)e.Row.FindControl("ddlOperator")); if (conditionId > 0) { DropDownList ddlConditionNumber = ((DropDownList)e.Row.FindControl("ddlConditionNumber")); WorkViewTDS dataSet = new WorkViewTDS(); dataSet.WorkViewConditionNew.Merge(workViewConditionNew, true); WorkViewConditionNew model = new WorkViewConditionNew(dataSet); int conditionNumber = model.GetNewConditionNumber(); ddlConditionNumber.SelectedValue = conditionNumber.ToString(); WorkTypeViewConditionGateway workTypeViewConditionGateway = new WorkTypeViewConditionGateway(); workTypeViewConditionGateway.LoadByWorkTypeConditionId(hdfWorkType.Value, conditionId, int.Parse(hdfCompanyId.Value)); string type = workTypeViewConditionGateway.GetType(hdfWorkType.Value, int.Parse(hdfCompanyId.Value), conditionId); if (ddlOperator.SelectedItem == null) { WorkTypeViewOperatorList workTypeViewOperatorList = new WorkTypeViewOperatorList(new DataSet()); workTypeViewOperatorList.LoadAndAddItem(type, Int32.Parse(hdfCompanyId.Value)); ddlOperator.DataSource = workTypeViewOperatorList.Table; ddlOperator.DataValueField = "Sign"; ddlOperator.DataTextField = "Operator"; ddlOperator.DataBind(); ddlOperator.SelectedIndex = 0; } if ((type == "String") || (type == "Date") || (type == "Int") || (type == "Decimal") || (type == "Distance")) { ddlOperator.Enabled = true; TextBox tbxValue = ((TextBox)e.Row.FindControl("tbxValue")); tbxValue.Visible = true; DropDownList ddlValue = ((DropDownList)e.Row.FindControl("ddlValue")); ddlValue.Visible = false; RadioButton rbtnYes = ((RadioButton)e.Row.FindControl("rbtnYes")); rbtnYes.Visible = false; RadioButton rbtnNo = ((RadioButton)e.Row.FindControl("rbtnNo")); rbtnNo.Visible = false; } if ((type == "FixedItems") || (type == "DynamicItems") || (type == "Boolean")) { ddlOperator.Enabled = false; if (type == "FixedItems") { DropDownList ddlValue = ((DropDownList)e.Row.FindControl("ddlValue")); ddlValue.Visible = true; TextBox tbxValue = ((TextBox)e.Row.FindControl("tbxValue")); tbxValue.Visible = false; RadioButton rbtnYes = ((RadioButton)e.Row.FindControl("rbtnYes")); rbtnYes.Visible = false; RadioButton rbtnNo = ((RadioButton)e.Row.FindControl("rbtnNo")); rbtnNo.Visible = false; } if (type == "DynamicItems") { DropDownList ddlValue = ((DropDownList)e.Row.FindControl("ddlValue")); ddlValue.Visible = true; TextBox tbxValue = ((TextBox)e.Row.FindControl("tbxValue")); tbxValue.Visible = false; RadioButton rbtnYes = ((RadioButton)e.Row.FindControl("rbtnYes")); rbtnYes.Visible = false; RadioButton rbtnNo = ((RadioButton)e.Row.FindControl("rbtnNo")); rbtnNo.Visible = false; } if (type == "Boolean") { DropDownList ddlValue = ((DropDownList)e.Row.FindControl("ddlValue")); ddlValue.Visible = false; TextBox tbxValue = ((TextBox)e.Row.FindControl("tbxValue")); tbxValue.Visible = false; RadioButton rbtnYes = ((RadioButton)e.Row.FindControl("rbtnYes")); rbtnYes.Visible = true; RadioButton rbtnNo = ((RadioButton)e.Row.FindControl("rbtnNo")); rbtnNo.Visible = true; } } } } if ((e.Row.RowType == DataControlRowType.DataRow) && ((e.Row.RowState == DataControlRowState.Edit) || (e.Row.RowState == (DataControlRowState.Edit | DataControlRowState.Alternate)))) { WorkViewTDS dataSet = new WorkViewTDS(); dataSet.WorkViewConditionNew.Merge(workViewConditionNew, true); WorkViewConditionNewGateway gateway = new WorkViewConditionNewGateway(dataSet); int id = int.Parse(((Label)e.Row.FindControl("lblId")).Text); int conditionId = gateway.GetConditionId(id); DropDownList ddlName = ((DropDownList)e.Row.FindControl("ddlName")); DropDownList ddlOperator = ((DropDownList)e.Row.FindControl("ddlOperator")); if (conditionId > 0) { WorkTypeViewConditionGateway workTypeViewConditionGateway = new WorkTypeViewConditionGateway(); workTypeViewConditionGateway.LoadByWorkTypeConditionId(hdfWorkType.Value, conditionId, int.Parse(hdfCompanyId.Value)); string type = workTypeViewConditionGateway.GetType(hdfWorkType.Value, int.Parse(hdfCompanyId.Value), conditionId); ddlName.DataBind(); ddlName.SelectedValue = conditionId.ToString(); WorkTypeViewOperatorList workTypeViewOperatorList = new WorkTypeViewOperatorList(new DataSet()); workTypeViewOperatorList.LoadAndAddItem(type, Int32.Parse(hdfCompanyId.Value)); ddlOperator.DataSource = workTypeViewOperatorList.Table; ddlOperator.DataValueField = "Sign"; ddlOperator.DataTextField = "Operator"; ddlOperator.DataBind(); ddlOperator.SelectedValue = gateway.GetSign(id); if ((type == "String") || (type == "Date") || (type == "Int") || (type == "Decimal") || (type == "Distance")) { ddlOperator.Enabled = true; TextBox tbxValue = ((TextBox)e.Row.FindControl("tbxValue")); tbxValue.Visible = true; tbxValue.Text = gateway.GetValue_(id); DropDownList ddlValue = ((DropDownList)e.Row.FindControl("ddlValue")); ddlValue.Visible = false; RadioButton rbtnYes = ((RadioButton)e.Row.FindControl("rbtnYes")); rbtnYes.Visible = false; RadioButton rbtnNo = ((RadioButton)e.Row.FindControl("rbtnNo")); rbtnNo.Visible = false; } if ((type == "FixedItems") || (type == "DynamicItems") || (type == "Boolean")) { ddlOperator.Enabled = false; if (type == "FixedItems") { DropDownList ddlValue = ((DropDownList)e.Row.FindControl("ddlValue")); ddlValue.Visible = true; // Prepare initial data // ... For view type WorkTypeViewConditionItemList workTypeViewConditionItemList = new WorkTypeViewConditionItemList(new DataSet()); workTypeViewConditionItemList.LoadAndAddItemInView(hdfWorkType.Value, Int32.Parse(hdfCompanyId.Value), conditionId); ddlValue.DataSource = workTypeViewConditionItemList.Table; ddlValue.DataValueField = "Name"; ddlValue.DataTextField = "Name"; ddlValue.DataBind(); ddlValue.SelectedValue = gateway.GetValue_(id); TextBox tbxValue = ((TextBox)e.Row.FindControl("tbxValue")); tbxValue.Visible = false; RadioButton rbtnYes = ((RadioButton)e.Row.FindControl("rbtnYes")); rbtnYes.Visible = false; RadioButton rbtnNo = ((RadioButton)e.Row.FindControl("rbtnNo")); rbtnNo.Visible = false; } if (type == "DynamicItems") { DropDownList ddlValue = ((DropDownList)e.Row.FindControl("ddlValue")); ddlValue.Visible = true; // Prepare initial data // ... For view type WorkTypeViewSubAreaList workTypeViewSubAreaList = new WorkTypeViewSubAreaList(new DataSet()); workTypeViewSubAreaList.LoadAndAddItem(hdfWorkType.Value, Int32.Parse(hdfCompanyId.Value), int.Parse(hdfProjectId.Value)); if (workTypeViewSubAreaList.Table.Rows.Count > 0) { ddlValue.DataSource = workTypeViewSubAreaList.Table; ddlValue.DataValueField = "SubArea"; ddlValue.DataTextField = "SubArea"; ddlValue.DataBind(); ddlValue.SelectedValue = gateway.GetValue_(id); } else { ddlValue.Visible = false; } TextBox tbxValue = ((TextBox)e.Row.FindControl("tbxValue")); tbxValue.Visible = false; RadioButton rbtnYes = ((RadioButton)e.Row.FindControl("rbtnYes")); rbtnYes.Visible = false; RadioButton rbtnNo = ((RadioButton)e.Row.FindControl("rbtnNo")); rbtnNo.Visible = false; } if (type == "Boolean") { DropDownList ddlValue = ((DropDownList)e.Row.FindControl("ddlValue")); ddlValue.Visible = false; TextBox tbxValue = ((TextBox)e.Row.FindControl("tbxValue")); tbxValue.Visible = false; RadioButton rbtnYes = ((RadioButton)e.Row.FindControl("rbtnYes")); rbtnYes.Visible = true; RadioButton rbtnNo = ((RadioButton)e.Row.FindControl("rbtnNo")); rbtnNo.Visible = true; if (gateway.GetValue_(id) == "Yes") { rbtnYes.Checked = true; } else { rbtnNo.Checked = true; } } } } } }
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 MrNavigatorTDS SubmitSearch() { // Retrieve clauses string whereClause = GetWhereClause(); string orderByClause = GetOrderByClause(); string workType = hdfWorkType.Value.Trim(); string conditionValue1 = ""; string conditionValue2 = ""; int companyId = Int32.Parse(hdfCompanyId.Value.Trim()); int currentProjectId = Int32.Parse(hdfCurrentProjectId.Value.Trim()); bool inProject = bool.Parse(hdfInProject.Value); MrNavigator mrNavigator = new MrNavigator(); // ... Load data WorkTypeViewConditionGateway workTypeViewConditionGateway = new WorkTypeViewConditionGateway(); workTypeViewConditionGateway.LoadByWorkTypeConditionId(workType, int.Parse(ddlCondition1.SelectedValue), companyId); conditionValue1 = workTypeViewConditionGateway.GetColumn_(workType, companyId, int.Parse(ddlCondition1.SelectedValue)); // ... If condition 2 exists if (ddlCondition2.SelectedValue != "-1") { // ... Load data for condition 2 WorkTypeViewConditionGateway workTypeViewConditionGateway2 = new WorkTypeViewConditionGateway(); workTypeViewConditionGateway2.LoadByWorkTypeConditionId(workType, int.Parse(ddlCondition2.SelectedValue), companyId); conditionValue2 = workTypeViewConditionGateway2.GetColumn_(workType, companyId, int.Parse(ddlCondition2.SelectedValue)); } // ... Load data mrNavigator.Load(whereClause, orderByClause, conditionValue1, conditionValue2, tbxCondition1.Text.Trim(), tbxCondition2.Text.Trim(), companyId, currentProjectId, workType, inProject); return (MrNavigatorTDS)mrNavigator.Data; }
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; }
private void GrdConditionsAdd() { if (ValidateFooterAdd()) { Page.Validate("Conditions"); if (Page.IsValid) { int conditionId = int.Parse(((DropDownList)grdConditions.FooterRow.FindControl("ddlName")).SelectedValue); string name = ((DropDownList)grdConditions.FooterRow.FindControl("ddlName")).SelectedItem.Text; string operator_ = ((DropDownList)grdConditions.FooterRow.FindControl("ddlOperator")).SelectedItem.Text; string sign = ((DropDownList)grdConditions.FooterRow.FindControl("ddlOperator")).SelectedValue; int conditionNumber = int.Parse(((DropDownList)grdConditions.FooterRow.FindControl("ddlConditionNumber")).SelectedValue); WorkTypeViewConditionGateway workTypeViewConditionGateway = new WorkTypeViewConditionGateway(); workTypeViewConditionGateway.LoadByWorkTypeConditionId(hdfWorkType.Value, conditionId, int.Parse(hdfCompanyId.Value)); string type = workTypeViewConditionGateway.GetType(hdfWorkType.Value, int.Parse(hdfCompanyId.Value), conditionId); string value = ""; if ((type == "String") || (type == "Date") || (type == "Int") || (type == "Decimal") || (type == "Distance")) { value = ((TextBox)grdConditions.FooterRow.FindControl("tbxValue")).Text; } if ((type == "FixedItems") || (type == "DynamicItems")) { value = ((DropDownList)grdConditions.FooterRow.FindControl("ddlValue")).SelectedItem.Text; } if (type == "Boolean") { if (((RadioButton)grdConditions.FooterRow.FindControl("rbtnYes")).Checked) value = "Yes"; if (((RadioButton)grdConditions.FooterRow.FindControl("rbtnNo")).Checked) value = "No"; } WorkViewTDS dataSet = new WorkViewTDS(); dataSet.WorkViewConditionNew.Merge(workViewConditionNew, true); WorkViewConditionNew model = new WorkViewConditionNew(dataSet); model.Insert(conditionId, name, operator_, sign, conditionNumber, value, false, false); Session.Remove("workViewConditionNewDummy"); workViewConditionNew = dataSet.WorkViewConditionNew; Session["workViewConditionNew"] = dataSet.WorkViewConditionNew; grdConditions.DataBind(); grdConditions.PageIndex = grdConditions.PageCount - 1; } } }
private bool ValidateFooterNext() { int conditionId = int.Parse(((DropDownList)grdConditions.FooterRow.FindControl("ddlName")).SelectedValue); WorkTypeViewConditionGateway workTypeViewConditionGateway = new WorkTypeViewConditionGateway(); workTypeViewConditionGateway.LoadByWorkTypeConditionId(hdfWorkType.Value, conditionId, int.Parse(hdfCompanyId.Value)); string type = workTypeViewConditionGateway.GetType(hdfWorkType.Value, int.Parse(hdfCompanyId.Value), conditionId); string value = ""; if ((type == "String") || (type == "Date") || (type == "Int") || (type == "Decimal")) { value = ((TextBox)grdConditions.FooterRow.FindControl("tbxValue")).Text; } if ((type == "FixedItems") || (type == "DynamicItems")) { value = ((DropDownList)grdConditions.FooterRow.FindControl("ddlValue")).SelectedItem.Text; } if (type == "Boolean") { if (((RadioButton)grdConditions.FooterRow.FindControl("rbtnYes")).Checked) value = "Yes"; if (((RadioButton)grdConditions.FooterRow.FindControl("rbtnNo")).Checked) value = "No"; } if (value != "") return true; else return false; }
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; }
protected void grdConditions_RowUpdating(object sender, GridViewUpdateEventArgs e) { Page.Validate("ConditionsUpdate"); if (Page.IsValid) { int id = (int)e.Keys["ID"]; int conditionId = int.Parse(((DropDownList)grdConditions.Rows[e.RowIndex].Cells[3].FindControl("ddlName")).SelectedValue); string name = ((DropDownList)grdConditions.Rows[e.RowIndex].Cells[3].FindControl("ddlName")).SelectedItem.Text; DropDownList ddlOperator = ((DropDownList)grdConditions.Rows[grdConditions.EditIndex].FindControl("ddlOperator")); string operator_ = ddlOperator.SelectedItem.Text; string sign = ddlOperator.SelectedValue; int conditionNumber = int.Parse(((DropDownList)grdConditions.Rows[e.RowIndex].Cells[2].FindControl("ddlConditionNumber")).SelectedValue); WorkTypeViewConditionGateway workTypeViewConditionGateway = new WorkTypeViewConditionGateway(); workTypeViewConditionGateway.LoadByWorkTypeConditionId(hdfWorkType.Value, conditionId, int.Parse(hdfCompanyId.Value)); string type = workTypeViewConditionGateway.GetType(hdfWorkType.Value, int.Parse(hdfCompanyId.Value), conditionId); string value = ""; if ((type == "String") || (type == "Date") || (type == "Int") || (type == "Decimal") || (type == "Distance")) { value = ((TextBox)grdConditions.Rows[e.RowIndex].Cells[5].FindControl("tbxValue")).Text; } if ((type == "FixedItems") || (type == "DynamicItems")) { value = ((DropDownList)grdConditions.Rows[e.RowIndex].Cells[5].FindControl("ddlValue")).SelectedItem.Text; } if (type == "Boolean") { if (((RadioButton)grdConditions.Rows[e.RowIndex].Cells[5].FindControl("rbtnYes")).Checked) value = "Yes"; if (((RadioButton)grdConditions.Rows[e.RowIndex].Cells[5].FindControl("rbtnNo")).Checked) value = "No"; } WorkViewTDS dataSet = new WorkViewTDS(); dataSet.WorkViewConditionNew.Merge(workViewConditionNew, true); WorkViewConditionNew model = new WorkViewConditionNew(dataSet); model.Update(id, conditionId, name, operator_, sign, conditionNumber, value, false, false); Session["workViewConditionNew"] = dataSet.WorkViewConditionNew; workViewConditionNew = dataSet.WorkViewConditionNew; } else { e.Cancel = true; } }
private RaNavigatorTDS SubmitSearch() { // Retrieve clauses string whereClause = GetWhereClause(); string orderByClause = GetOrderByClause(); string workType = hdfWorkType.Value.Trim(); string conditionValue = ""; int companyId = Int32.Parse(hdfCompanyId.Value.Trim()); int currentProjectId = Int32.Parse(hdfCurrentProjectId.Value.Trim()); RaNavigator raNavigator = new RaNavigator(); // ... Load data WorkTypeViewConditionGateway workTypeViewConditionGateway = new WorkTypeViewConditionGateway(); workTypeViewConditionGateway.LoadByWorkTypeConditionId(workType, int.Parse(ddlCondition1.SelectedValue), companyId); conditionValue = workTypeViewConditionGateway.GetColumn_(workType, companyId, int.Parse(ddlCondition1.SelectedValue)); raNavigator.Load(whereClause, orderByClause, conditionValue, tbxCondition1.Text.Trim(), companyId, currentProjectId, workType); return (RaNavigatorTDS)raNavigator.Data; }
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 GetOrderByClause() { // Get tableName string workType = hdfWorkType.Value.Trim(); int companyId = Int32.Parse(hdfCompanyId.Value.Trim()); WorkTypeViewSortGateway workTypeViewSortGateway = new WorkTypeViewSortGateway(); workTypeViewSortGateway.LoadByWorkTypeSortId(workType, companyId, int.Parse(ddlSortBy.SelectedValue)); string tableName = workTypeViewSortGateway.GetTable_(workType, companyId, int.Parse(ddlSortBy.SelectedValue)); string columnName = workTypeViewSortGateway.GetColumn_(workType, companyId, int.Parse(ddlSortBy.SelectedValue)); if (tableName == "AM_ASSET_SEWER_SECTION") tableName = "AASS"; if (tableName == "LFS_WORK_FULLLENGTHLINING") tableName = "LWF"; if (tableName == "LFS_ASSET_SEWER_SECTION") tableName = "LASS"; WorkTypeViewConditionGateway workTypeViewConditionGateway = new WorkTypeViewConditionGateway(); workTypeViewConditionGateway.LoadByWorkTypeConditionId(workType, int.Parse(ddlCondition1.SelectedValue), companyId); string conditionValue = workTypeViewConditionGateway.GetColumn_(workType, companyId, int.Parse(ddlCondition1.SelectedValue)); // Get order by clause string orderBy = ""; if (columnName == "Date") { switch (conditionValue) { case "ProposedLiningDate": orderBy = "LWF.ProposedLiningDate DESC"; break; case "DeadlineLiningDate": orderBy = "LWF.DeadlineLiningDate DESC"; break; case "P1Date": orderBy = "LWF.P1Date DESC"; break; case "M1Date": orderBy = "LWF.M1Date DESC"; break; case "M2Date": orderBy = "LWF.M2Date DESC"; break; case "InstallDate": orderBy = "LWF.InstallDate DESC"; break; case "FinalVideoDate": orderBy = "LWF.FinalVideoDate DESC"; break; case "PreFlushDate": orderBy = "LWR.PreFlushDate DESC"; break; case "PreVideoDate": orderBy = "LWR.PreVideoDate DESC"; break; default: orderBy = "AASS.FlowOrderID ASC"; break; } } else { orderBy = tableName + "." + columnName; } return orderBy; }
private void RestoreNavigatorState() { // Search condition 1 // Columns To Display string columnsToDisplay = Request.QueryString["columns_to_display"]; string columnsToDisplay2 = Request.QueryString["columns_to_display2"]; // Search condition // ... For Condition 1 odsViewForDisplayList.DataBind(); ddlCondition1.DataSourceID = "odsViewForDisplayList"; ddlCondition1.DataValueField = "ConditionID"; ddlCondition1.DataTextField = "Name"; ddlCondition1.DataBind(); ddlCondition1.SelectedValue = Request.QueryString["search_ddlCondition1"]; tbxCondition1.Text = Request.QueryString["search_tbxCondition1"]; ddlOperator1.SelectedValue = Request.QueryString["search_ddlOperator1"]; // ... For Condition 2 odsViewForDisplayList2.DataBind(); ddlCondition2.DataSourceID = "odsViewForDisplayList2"; ddlCondition2.DataValueField = "ConditionID"; ddlCondition2.DataTextField = "Name"; ddlCondition2.DataBind(); ddlCondition2.SelectedValue = Request.QueryString["search_ddlCondition2"]; tbxCondition2.Text = Request.QueryString["search_tbxCondition2"]; ddlOperator2.SelectedValue = Request.QueryString["search_ddlOperator2"]; // ... For View int companyId = Int32.Parse(hdfCompanyId.Value.Trim()); string workType = hdfWorkType.Value; int loginId = Convert.ToInt32(Session["loginID"]); string viewTypeGlobal = ""; string viewTypePersonal = "Personal"; // Global Views check if (Convert.ToBoolean(Session["sgLFS_GLOBALVIEWS_VIEW"])) { viewTypeGlobal = "Global"; } WorkViewList workViewList = new WorkViewList(); workViewList.LoadAndAddItem(workType, viewTypeGlobal, viewTypePersonal, loginId, companyId); ddlView.DataSource = workViewList.Table; ddlView.DataValueField = "ViewID"; ddlView.DataTextField = "Name"; ddlView.DataBind(); ddlView.SelectedValue = Request.QueryString["search_ddlView"]; //Other values //... For SortBy odsSortByList.DataBind(); ddlSortBy.DataSourceID = "odsSortByList"; ddlSortBy.DataValueField = "SortID"; ddlSortBy.DataTextField = "Name"; ddlSortBy.DataBind(); ddlSortBy.SelectedValue = Request.QueryString["search_sort_by"]; // ... For BtnOrigin hdfBtnOrigin.Value = Request.QueryString["btn_origin"]; cbxIdSection.Checked = (columnsToDisplay2.IndexOf("FlowOrderID") >= 0 ? true : false); cbxIdRepair.Checked = (columnsToDisplay2.IndexOf("RepairPointID") >= 0 ? true : false); cbxSubArea.Checked = (columnsToDisplay2.IndexOf("SubArea") >= 0 ? true : false); cbxStreet.Checked = (columnsToDisplay2.IndexOf("Street") >= 0 ? true : false); cbxUsmh.Checked = (columnsToDisplay2.IndexOf(" USMH,") >= 0 ? true : false); cbxUsmhAddress.Checked = (columnsToDisplay2.IndexOf("USMHAddress") >= 0 ? true : false); cbxDsmh.Checked = (columnsToDisplay2.IndexOf(" DSMH") >= 0 ? true : false); cbxDsmhAddress.Checked = (columnsToDisplay2.IndexOf("DSMHAddress") >= 0 ? true : false); cbxMapSize.Checked = (columnsToDisplay2.IndexOf("MapSize") >= 0 ? true : false); cbxConfirmedSize.Checked = (columnsToDisplay2.IndexOf("Size_") >= 0 ? true : false); cbxMapLength.Checked = (columnsToDisplay2.IndexOf("MapLength") >= 0 ? true : false); cbxSteelTapeLength.Checked = (columnsToDisplay2.IndexOf("Length") >= 0 ? true : false); cbxVideoLength.Checked = (columnsToDisplay2.IndexOf("VideoLength") >= 0 ? true : false); cbxLaterals.Checked = (columnsToDisplay2.IndexOf("Laterals") >= 0 ? true : false); cbxLiveLaterals.Checked = (columnsToDisplay2.IndexOf("LiveLaterals") >= 0 ? true : false); cbxClientId.Checked = (columnsToDisplay2.IndexOf("ClientID") >= 0 ? true : false); cbxMeasurementsTakenBy.Checked = (columnsToDisplay2.IndexOf("MeasurementTakenBy") >= 0 ? true : false); cbxPreFlushDate.Checked = (columnsToDisplay2.IndexOf("PreFlushDate") >= 0 ? true : false); cbxPreVideoDate.Checked = (columnsToDisplay2.IndexOf("PreVideoDate") >= 0 ? true : false); cbxP1Date.Checked = (columnsToDisplay2.IndexOf("P1Date") >= 0 ? true : false); cbxRepairConfirmationDate.Checked = (columnsToDisplay2.IndexOf("RepairConfirmationDate") >= 0 ? true : false); cbxTrafficControl.Checked = (columnsToDisplay2.IndexOf("TrafficControl") >= 0 ? true : false); cbxMaterial.Checked = (columnsToDisplay2.IndexOf("MaterialType") >= 0 ? true : false); cbxBypassRequired.Checked = (columnsToDisplay2.IndexOf("BypassRequired") >= 0 ? true : false); cbxRoboticPrepRequired.Checked = (columnsToDisplay2.IndexOf("RoboticPrepRequired") >= 0 ? true : false); cbxCXIsRemoved.Checked = (columnsToDisplay2.IndexOf("CXIsRemoved") >= 0 ? true : false); cbxRoboticDistances.Checked = (columnsToDisplay2.IndexOf("RoboticDistances") >= 0 ? true : false); cbxProposedLiningDate.Checked = (columnsToDisplay2.IndexOf("ProposedLiningDate") >= 0 ? true : false); cbxDeadlineLiningDate.Checked = (columnsToDisplay2.IndexOf("DeadlineLiningDate") >= 0 ? true : false); cbxGroutDate.Checked = (columnsToDisplay2.IndexOf("GroutDate") >= 0 ? true : false); cbxFinalVideo.Checked = (columnsToDisplay2.IndexOf("FinalVideoDate") >= 0 ? true : false); cbxEstimatedJoints.Checked = (columnsToDisplay2.IndexOf("EstimatedJoints") >= 0 ? true : false); cbxJointsTestSealed.Checked = (columnsToDisplay2.IndexOf("JointsTestSealed") >= 0 ? true : false); cbxIssueIdentified.Checked = (columnsToDisplay2.IndexOf("IssueIdentified") >= 0 ? true : false); cbxLFSIssue.Checked = (columnsToDisplay2.IndexOf("IssueLFS") >= 0 ? true : false); cbxClientIssue.Checked = (columnsToDisplay2.IndexOf("IssueClient") >= 0 ? true : false); cbxSalesIssue.Checked = (columnsToDisplay2.IndexOf("IssueSales") >= 0 ? true : false); cbxIssueGivenToClient.Checked = (columnsToDisplay2.IndexOf("IssueGivenToClient") >= 0 ? true : false); cbxIssueInvestigation.Checked = (columnsToDisplay2.IndexOf("IssueInvestigation") >= 0 ? true : false); cbxIssueResolved.Checked = (columnsToDisplay2.IndexOf("IssueResolved") >= 0 ? true : false); cbxComments.Checked = (columnsToDisplay2.IndexOf("Comments") >= 0 ? true : false); cbxRepairType.Checked = (columnsToDisplay2.IndexOf("Type") >= 0 ? true : false); cbxDefectQualifier.Checked = (columnsToDisplay2.IndexOf("DefectQualifier") >= 0 ? true : false); cbxDefectDetails.Checked = (columnsToDisplay2.IndexOf("DefectDetails") >= 0 ? true : false); cbxExtraRepair.Checked = (columnsToDisplay2.IndexOf("ExtraRepair") >= 0 ? true : false); cbxCancelled.Checked = (columnsToDisplay2.IndexOf("Cancelled") >= 0 ? true : false); cbxApproval.Checked = (columnsToDisplay2.IndexOf("Approval") >= 0 ? true : false); cbxRepairComments.Checked = (columnsToDisplay2.IndexOf("Comments") >= 0 ? true : false); cbxReamDistance.Checked = (columnsToDisplay2.IndexOf("ReamDistance") >= 0 ? true : false); cbxReamDate.Checked = (columnsToDisplay2.IndexOf("ReamDate") >= 0 ? true : false); cbxLinerDistance.Checked = (columnsToDisplay2.IndexOf("LinerDistance") >= 0 ? true : false); cbxDirection.Checked = (columnsToDisplay2.IndexOf("Direction") >= 0 ? true : false); cbxReinstates.Checked = (columnsToDisplay2.IndexOf("Reinstates") >= 0 ? true : false); cbxLtMh.Checked = (columnsToDisplay2.IndexOf("LTMH") >= 0 ? true : false); cbxVtMh.Checked = (columnsToDisplay2.IndexOf("VTMH") >= 0 ? true : false); cbxDistance.Checked = (columnsToDisplay2.IndexOf("Distance") >= 0 ? true : false); cbxRepairSize.Checked = (columnsToDisplay2.IndexOf("Size_") >= 0 ? true : false); cbxRepairLength.Checked = (columnsToDisplay2.IndexOf("Length") >= 0 ? true : false); cbxInstallDate.Checked = (columnsToDisplay2.IndexOf("InstallDate") >= 0 ? true : false); cbxMhShot.Checked = (columnsToDisplay2.IndexOf("MHShot") >= 0 ? true : false); cbxGroutDistance.Checked = (columnsToDisplay2.IndexOf("GroutDistance") >= 0 ? true : false); // Grid's columns grdPrNavigator.Columns[2].Visible = (columnsToDisplay.IndexOf("FlowOrderID") >= 0 ? true : false); grdPrNavigator.Columns[3].Visible = (columnsToDisplay.IndexOf("SubArea") >= 0 ? true : false); grdPrNavigator.Columns[4].Visible = (columnsToDisplay.IndexOf("Street") >= 0 ? true : false); grdPrNavigator.Columns[5].Visible = (columnsToDisplay.IndexOf(" USMH,") >= 0 ? true : false); grdPrNavigator.Columns[6].Visible = (columnsToDisplay.IndexOf("USMHAddress") >= 0 ? true : false); grdPrNavigator.Columns[7].Visible = (columnsToDisplay.IndexOf(" DSMH") >= 0 ? true : false); grdPrNavigator.Columns[8].Visible = (columnsToDisplay.IndexOf("DSMHAddress") >= 0 ? true : false); grdPrNavigator.Columns[9].Visible = (columnsToDisplay.IndexOf("MapSize") >= 0 ? true : false); grdPrNavigator.Columns[10].Visible = (columnsToDisplay.IndexOf("Size_") >= 0 ? true : false); grdPrNavigator.Columns[11].Visible = (columnsToDisplay.IndexOf("MapLength") >= 0 ? true : false); grdPrNavigator.Columns[12].Visible = (columnsToDisplay.IndexOf("Length") >= 0 ? true : false); grdPrNavigator.Columns[13].Visible = (columnsToDisplay.IndexOf("VideoLength") >= 0 ? true : false); grdPrNavigator.Columns[14].Visible = (columnsToDisplay.IndexOf("Laterals") >= 0 ? true : false); grdPrNavigator.Columns[15].Visible = (columnsToDisplay.IndexOf("LiveLaterals") >= 0 ? true : false); grdPrNavigator.Columns[16].Visible = (columnsToDisplay.IndexOf("ClientID") >= 0 ? true : false); grdPrNavigator.Columns[17].Visible = (columnsToDisplay.IndexOf("MeasurementTakenBy") >= 0 ? true : false); grdPrNavigator.Columns[18].Visible = (columnsToDisplay.IndexOf("PreFlushDate") >= 0 ? true : false); grdPrNavigator.Columns[19].Visible = (columnsToDisplay.IndexOf("PreVideoDate") >= 0 ? true : false); grdPrNavigator.Columns[20].Visible = (columnsToDisplay.IndexOf("P1Date") >= 0 ? true : false); grdPrNavigator.Columns[21].Visible = (columnsToDisplay.IndexOf("RepairConfirmationDate") >= 0 ? true : false); grdPrNavigator.Columns[22].Visible = (columnsToDisplay.IndexOf("TrafficControl") >= 0 ? true : false); grdPrNavigator.Columns[23].Visible = (columnsToDisplay.IndexOf("MaterialType") >= 0 ? true : false); grdPrNavigator.Columns[24].Visible = (columnsToDisplay.IndexOf("BypassRequired") >= 0 ? true : false); grdPrNavigator.Columns[25].Visible = (columnsToDisplay.IndexOf("RoboticPrepRequired") >= 0 ? true : false); grdPrNavigator.Columns[26].Visible = (columnsToDisplay.IndexOf("CXIsRemoved") >= 0 ? true : false); grdPrNavigator.Columns[27].Visible = (columnsToDisplay.IndexOf("RoboticDistances") >= 0 ? true : false); grdPrNavigator.Columns[28].Visible = (columnsToDisplay.IndexOf("ProposedLiningDate") >= 0 ? true : false); grdPrNavigator.Columns[29].Visible = (columnsToDisplay.IndexOf("DeadlineLiningDate") >= 0 ? true : false); grdPrNavigator.Columns[30].Visible = (columnsToDisplay.IndexOf("FinalVideoDate") >= 0 ? true : false); grdPrNavigator.Columns[31].Visible = (columnsToDisplay.IndexOf("EstimatedJoints") >= 0 ? true : false); grdPrNavigator.Columns[32].Visible = (columnsToDisplay.IndexOf("JointsTestSealed") >= 0 ? true : false); grdPrNavigator.Columns[33].Visible = (columnsToDisplay.IndexOf("IssueIdentified") >= 0 ? true : false); grdPrNavigator.Columns[34].Visible = (columnsToDisplay.IndexOf("IssueLFS") >= 0 ? true : false); grdPrNavigator.Columns[35].Visible = (columnsToDisplay.IndexOf("IssueClient") >= 0 ? true : false); grdPrNavigator.Columns[36].Visible = (columnsToDisplay.IndexOf("IssueSales") >= 0 ? true : false); grdPrNavigator.Columns[37].Visible = (columnsToDisplay.IndexOf("IssueGivenToClient") >= 0 ? true : false); grdPrNavigator.Columns[38].Visible = (columnsToDisplay.IndexOf("IssueInvestigation") >= 0 ? true : false); grdPrNavigator.Columns[39].Visible = (columnsToDisplay.IndexOf("IssueResolved") >= 0 ? true : false); grdPrNavigator.Columns[40].Visible = (columnsToDisplay.IndexOf("Comments") >= 0 ? true : false); grdPrNavigator.Columns[41].Visible = false; grdPrNavigator.Columns[41].Visible = (columnsToDisplay.IndexOf("RepairPointID") >= 0 ? true : false); // Special case for repairs // For condition 1 WorkTypeViewConditionGateway workTypeViewConditionGateway = new WorkTypeViewConditionGateway(); workTypeViewConditionGateway.LoadByWorkTypeConditionId(workType, int.Parse(ddlCondition1.SelectedValue), companyId); string name = workTypeViewConditionGateway.GetName(workType, companyId, int.Parse(ddlCondition1.SelectedValue)); string conditionValue1 = workTypeViewConditionGateway.GetColumn_(workType, companyId, int.Parse(ddlCondition1.SelectedValue)); if ((conditionValue1 == "RepairPointID") || (conditionValue1 == "Type") || (conditionValue1 == "DefectQualifier") || (conditionValue1 == "DefectDetails") || (conditionValue1 == "ExtraRepair") || (conditionValue1 == "Cancelled") || (conditionValue1 == "Approval") || (conditionValue1 == "Comments" && name == "Repair Comments") || (conditionValue1 == "ReamDistance") || (conditionValue1 == "ReamDate") || (conditionValue1 == "LinerDistance") || (conditionValue1 == "Direction") || (conditionValue1 == "Reinstates") || (conditionValue1 == "LTMH") || (conditionValue1 == "VTMH") || (conditionValue1 == "Distance") || (conditionValue1 == "Size_" && name == "Repair Size") || (conditionValue1 == "Length" && name == "Repair Length") || (conditionValue1 == "InstallDate") || (conditionValue1 == "MHShot") || (conditionValue1 == "GroutDistance") || (conditionValue1 == "GroutDate")) { grdPrNavigator.Columns[41].Visible = true; } if (ddlCondition2.SelectedValue != "-1") { WorkTypeViewConditionGateway workTypeViewConditionGateway2 = new WorkTypeViewConditionGateway(); workTypeViewConditionGateway2.LoadByWorkTypeConditionId(workType, int.Parse(ddlCondition2.SelectedValue), companyId); string name2 = workTypeViewConditionGateway2.GetName(workType, companyId, int.Parse(ddlCondition2.SelectedValue)); string conditionValue2 = workTypeViewConditionGateway2.GetColumn_(workType, companyId, int.Parse(ddlCondition2.SelectedValue)); if ((conditionValue2 == "RepairPointID") || (conditionValue2 == "Type") || (conditionValue2 == "DefectQualifier") || (conditionValue2 == "DefectDetails") || (conditionValue2 == "ExtraRepair") || (conditionValue2 == "Cancelled") || (conditionValue2 == "Approval") || (conditionValue2 == "Comments" && name == "Repair Comments") || (conditionValue2 == "ReamDistance") || (conditionValue2 == "ReamDate") || (conditionValue2 == "LinerDistance") || (conditionValue2 == "Direction") || (conditionValue2 == "Reinstates") || (conditionValue2 == "LTMH") || (conditionValue2 == "VTMH") || (conditionValue2 == "Distance") || (conditionValue2 == "Size_" && name == "Repair Size") || (conditionValue2 == "Length" && name == "Repair Length") || (conditionValue2 == "InstallDate") || (conditionValue2 == "MHShot") || (conditionValue2 == "GroutDistance") || (conditionValue2 == "GroutDate")) { grdPrNavigator.Columns[41].Visible = true; } } }
protected void cvValueData_ServerValidate(object source, ServerValidateEventArgs args) { // Initialize CustomValidator cvConditions = (CustomValidator)source; args.IsValid = true; string type = "Date"; int conditionId = 0; if (cvConditions.ValidationGroup == "Conditions") { conditionId = int.Parse(((DropDownList)grdConditions.FooterRow.FindControl("ddlName")).SelectedValue); } else { conditionId = int.Parse(((DropDownList)grdConditions.Rows[grdConditions.EditIndex].Cells[3].FindControl("ddlName")).SelectedValue); } WorkTypeViewConditionGateway workTypeViewConditionGateway = new WorkTypeViewConditionGateway(); workTypeViewConditionGateway.LoadByWorkTypeConditionId(hdfWorkType.Value, conditionId, int.Parse(hdfCompanyId.Value)); type = workTypeViewConditionGateway.GetType(hdfWorkType.Value, int.Parse(hdfCompanyId.Value), conditionId); // Date fields validate if (type == "Date") { // for complete date or only year if (((Validator.IsValidDate(args.Value.Trim()) && (args.Value.Trim().Length > 7))) || ((Validator.IsValidInt32(args.Value.Trim())) && (args.Value.Trim().Length == 4)) || (args.Value.Trim() == "%") || (args.Value.Trim() == "")) { args.IsValid = true; } else { args.IsValid = false; cvConditions.ErrorMessage = "Invalid date (use mm/dd/yyyy, yyyy, %, or leave the field empty)"; } // For dates before 1900 if (args.IsValid) { string[] date = (args.Value.Trim()).Split('/'); if (((Validator.IsValidDate(args.Value.Trim())) && (Int32.Parse(date[2]) >= 1900)) || ((args.Value.Trim().Length == 4) && (Validator.IsValidInt32(args.Value.Trim())) && (Int32.Parse(args.Value.Trim()) >= 1900)) || (args.Value.Trim() == "%") || (args.Value.Trim() == "")) { args.IsValid = true; } else { args.IsValid = false; cvConditions.ErrorMessage = "Invalid date (use a date over 1900)"; } } } if (type == "Int") { if (Validator.IsValidInt32(args.Value.Trim()) || (args.Value.Trim() == "%") || (args.Value.Trim() == "")) { args.IsValid = true; } else { args.IsValid = false; cvConditions.ErrorMessage = "Invalid data (use an integer number, % or leave the field empty)"; } } if (type == "Decimal") { if (Validator.IsValidDecimal(args.Value.Trim()) || (args.Value.Trim() == "%") || (args.Value.Trim() == "")) { args.IsValid = true; } else { args.IsValid = false; cvConditions.ErrorMessage = "Invalid data (use a decimal number, % or leave the field empty)"; } } }
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; }
protected void ddlName_SelectedIndexChanged(object sender, EventArgs e) { Label lblValidateValue = ((Label)grdConditions.Rows[grdConditions.EditIndex].FindControl("lblValidateValue")); lblValidateValue.Visible = false; ImageButton ibtnAdd = ((ImageButton)grdConditions.Rows[grdConditions.EditIndex].FindControl("ibtnAccept")); ibtnAdd.Visible = true; DropDownList ddlName = ((DropDownList)grdConditions.Rows[grdConditions.EditIndex].FindControl("ddlName")); DropDownList ddlOperator = ((DropDownList)grdConditions.Rows[grdConditions.EditIndex].FindControl("ddlOperator")); WorkTypeViewConditionGateway workTypeViewConditionGateway = new WorkTypeViewConditionGateway(); workTypeViewConditionGateway.LoadByWorkTypeConditionId(hdfWorkType.Value, int.Parse(ddlName.SelectedValue), int.Parse(hdfCompanyId.Value)); string type = workTypeViewConditionGateway.GetType(hdfWorkType.Value, int.Parse(hdfCompanyId.Value), int.Parse(ddlName.SelectedValue)); int index = grdConditions.SelectedIndex; WorkTypeViewOperatorList workTypeViewOperatorList = new WorkTypeViewOperatorList(new DataSet()); workTypeViewOperatorList.LoadAndAddItem(type, Int32.Parse(hdfCompanyId.Value)); ddlOperator.DataSource = workTypeViewOperatorList.Table; ddlOperator.DataValueField = "Sign"; ddlOperator.DataTextField = "Operator"; ddlOperator.DataBind(); ddlOperator.SelectedIndex = 0; if ((type == "String") || (type == "Date") || (type == "Int") || (type == "Decimal") || (type == "Distance")) { ddlOperator.Enabled = true; TextBox tbxValue = ((TextBox)grdConditions.Rows[grdConditions.EditIndex].FindControl("tbxValue")); tbxValue.Visible = true; DropDownList ddlValue = ((DropDownList)grdConditions.Rows[grdConditions.EditIndex].FindControl("ddlValue")); ddlValue.Visible = false; RadioButton rbtnYes = ((RadioButton)grdConditions.Rows[grdConditions.EditIndex].FindControl("rbtnYes")); rbtnYes.Visible = false; RadioButton rbtnNo = ((RadioButton)grdConditions.Rows[grdConditions.EditIndex].FindControl("rbtnNo")); rbtnNo.Visible = false; } if ((type == "FixedItems") || (type == "DynamicItems") || (type == "Boolean")) { ddlOperator.Enabled = false; if (type == "FixedItems") { DropDownList ddlValue = ((DropDownList)grdConditions.Rows[grdConditions.EditIndex].FindControl("ddlValue")); ddlValue.Visible = true; // Prepare initial data // ... For view type WorkTypeViewConditionItemList workTypeViewConditionItemList = new WorkTypeViewConditionItemList(new DataSet()); workTypeViewConditionItemList.LoadAndAddItemInView(hdfWorkType.Value, Int32.Parse(hdfCompanyId.Value), int.Parse(ddlName.SelectedValue)); ddlValue.DataSource = workTypeViewConditionItemList.Table; ddlValue.DataValueField = "Name"; ddlValue.DataTextField = "Name"; ddlValue.DataBind(); ddlValue.SelectedIndex = 0; TextBox tbxValue = ((TextBox)grdConditions.Rows[grdConditions.EditIndex].FindControl("tbxValue")); tbxValue.Visible = false; RadioButton rbtnYes = ((RadioButton)grdConditions.Rows[grdConditions.EditIndex].FindControl("rbtnYes")); rbtnYes.Visible = false; RadioButton rbtnNo = ((RadioButton)grdConditions.Rows[grdConditions.EditIndex].FindControl("rbtnNo")); rbtnNo.Visible = false; } if (type == "DynamicItems") { DropDownList ddlValue = ((DropDownList)grdConditions.Rows[grdConditions.EditIndex].FindControl("ddlValue")); // Prepare initial data // ... For view type WorkTypeViewSubAreaList workTypeViewSubAreaList = new WorkTypeViewSubAreaList(new DataSet()); workTypeViewSubAreaList.LoadAndAddItem(hdfWorkType.Value, int.Parse(hdfCompanyId.Value), int.Parse(hdfProjectId.Value)); if (workTypeViewSubAreaList.Table.Rows.Count > 0) { ddlValue.Visible = true; ddlValue.DataSource = workTypeViewSubAreaList.Table; ddlValue.DataValueField = "SubArea"; ddlValue.DataTextField = "SubArea"; ddlValue.DataBind(); ddlValue.SelectedIndex = 0; ibtnAdd.Visible = true; } else { ddlValue.Visible = false; ibtnAdd.Visible = false; lblValidateValue.Visible = true; } TextBox tbxValue = ((TextBox)grdConditions.Rows[grdConditions.EditIndex].FindControl("tbxValue")); tbxValue.Visible = false; RadioButton rbtnYes = ((RadioButton)grdConditions.Rows[grdConditions.EditIndex].FindControl("rbtnYes")); rbtnYes.Visible = false; RadioButton rbtnNo = ((RadioButton)grdConditions.Rows[grdConditions.EditIndex].FindControl("rbtnNo")); rbtnNo.Visible = false; } if (type == "Boolean") { DropDownList ddlValue = ((DropDownList)grdConditions.Rows[grdConditions.EditIndex].FindControl("ddlValue")); ddlValue.Visible = false; TextBox tbxValue = ((TextBox)grdConditions.Rows[grdConditions.EditIndex].FindControl("tbxValue")); tbxValue.Visible = false; RadioButton rbtnYes = ((RadioButton)grdConditions.Rows[grdConditions.EditIndex].FindControl("rbtnYes")); rbtnYes.Visible = true; RadioButton rbtnNo = ((RadioButton)grdConditions.Rows[grdConditions.EditIndex].FindControl("rbtnNo")); rbtnNo.Visible = true; } } }
private string GetOrderByClause() { // Get tableName string workType = hdfWorkType.Value.Trim(); int companyId = Int32.Parse(hdfCompanyId.Value.Trim()); WorkTypeViewSortGateway workTypeViewSortGateway = new WorkTypeViewSortGateway(); workTypeViewSortGateway.LoadByWorkTypeSortId(workType, companyId, int.Parse(ddlSortBy.SelectedValue)); string tableName = workTypeViewSortGateway.GetTable_(workType, companyId, int.Parse(ddlSortBy.SelectedValue)); string columnName = workTypeViewSortGateway.GetColumn_(workType, companyId, int.Parse(ddlSortBy.SelectedValue)); if (tableName == "AM_ASSET_SEWER_SECTION") tableName = "AASS"; if (tableName == "LFS_ASSET_SEWER_MH") tableName = "LASMH"; if (tableName == "LFS_WORK_MANHOLE_REHABILITATION") tableName = "LWMR"; if (tableName == "LFS_WORK_MANHOLE_REHABILITATION_BATCH") tableName = "LWMRB"; if (tableName == "AM_ASSET_SEWER_MH") tableName = "AASMH"; WorkTypeViewConditionGateway workTypeViewConditionGateway = new WorkTypeViewConditionGateway(); workTypeViewConditionGateway.LoadByWorkTypeConditionId(workType, int.Parse(ddlCondition1.SelectedValue), companyId); string conditionValue = workTypeViewConditionGateway.GetColumn_(workType, companyId, int.Parse(ddlCondition1.SelectedValue)); // Get order by clause string orderBy = ""; if (columnName == "Date") { switch (conditionValue) { case "PreppedDate": orderBy = "LWMR.PreppedDate DESC"; break; case "SprayedDate": orderBy = "LWMR.SprayedDate DESC"; break; case "Date": orderBy = "LWMRB.Date DESC"; break; default: orderBy = "AASMH.MHID ASC"; break; } } else { orderBy = tableName + "." + columnName; } return orderBy; }
// //////////////////////////////////////////////////////////////////////// // PUBLIC METHODS // /// <summary> /// LoadByWorkTypeConditionId /// </summary> /// <param name="workType">workType</param> /// <param name="conditionId">conditionId</param> /// <param name="companyId">companyId</param> public void LoadByWorkTypeConditionId(string workType, int conditionId, int companyId) { WorkTypeViewConditionGateway workTypeViewConditionGateway = new WorkTypeViewConditionGateway(Data); workTypeViewConditionGateway.LoadByWorkTypeConditionId(workType, conditionId, companyId); }