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);
}
