private string modifyWhereClauseForCondition(string whereClause, int conditionId, string textForSearch)
{
int companyId = Int32.Parse(hdfCompanyId.Value.Trim());
string fmType = hdfFmType.Value;
FmTypeViewConditionGateway fmTypeViewConditionGateway = new FmTypeViewConditionGateway();
fmTypeViewConditionGateway.LoadByFmTypeConditionId(fmType, companyId, conditionId);
string conditionValue = fmTypeViewConditionGateway.GetColumn_(fmType, companyId, conditionId);
string conditionName = fmTypeViewConditionGateway.GetName(fmType, companyId, conditionId);
string tableName = fmTypeViewConditionGateway.GetTable_(fmType, companyId, conditionId);
if (tableName == "LFS_FM_TODOLIST") tableName = "LFTDL";
if (tableName == "LFS_EMPLOYEE") tableName = "LRE";
if (tableName == "LFS_FM_UNIT") tableName = "LFU";
if (tableName == "LFS_FM_TODOLIST_ACTIVITY") tableName = "LFTDLA";
if (conditionName == "Created By") tableName = "LEOwner";
// FOR TEXT FIELDS. (Subject, Unit Code)
if ((conditionValue == "Subject") || (conditionValue == "UnitCode"))
{
// ... Search
if (textForSearch == "%")
{
whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " LIKE '%')";
whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))";
}
else
{
if (textForSearch == "")
{
whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " IS NULL )";
}
else
{
if (textForSearch.Contains("\""))
{
if (conditionValue == "LastComment")
{
textForSearch = textForSearch.Replace("'", "''");
whereClause = whereClause + "AND (" + tableName + "." + conditionValue + " LIKE '%" + textForSearch + "%')";
}
else
{
textForSearch = textForSearch.Replace("\"", "");
whereClause = whereClause + "AND (" + tableName + "." + conditionValue + " = '" + textForSearch + "')";
}
}
else
{
textForSearch = textForSearch.Replace("'", "''");
whereClause = whereClause + "AND (" + tableName + "." + conditionValue + " LIKE '%" + textForSearch + "%')";
}
}
}
}
// FOR DATE FIELDS. (CreationDate, DueDate)
if ((conditionValue == "CreationDate") || (conditionValue == "DueDate"))
{
// ... Search
if (textForSearch == "")
{
whereClause = whereClause + " AND ( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NULL)";
}
else
{
if (textForSearch == "%")
{
whereClause = whereClause + " AND (( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NOT NULL) OR ";
whereClause = whereClause + "( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NULL))";
}
else
{
if ((Validator.IsValidDate(textForSearch)) && (textForSearch.Length > 7))
{
whereClause = whereClause + " AND ( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) = '" + textForSearch + "')";
}
else
{
whereClause = whereClause + " AND ( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) LIKE '%" + textForSearch + "%')";
}
}
}
}
string state = ddlState.SelectedValue;
if (state != "(All)")
{
if (state != "New & In Progress")
{
whereClause = whereClause + "AND (LFTDL.State = '" + state + "')";
}
else
{
whereClause = whereClause + "AND (LFTDL.State = 'New' OR LFTDL.State = 'In Progress')";
}
}
return whereClause;
}
private ToDoListNavigatorTDS SubmitSearch()
{
// Retrieve clauses
string whereClause = GetWhereClause();
string orderByClause = GetOrderByClause();
string conditionValue = "";
string conditionName = "";
ToDoListNavigator toDolistNavigator = new ToDoListNavigator();
string fmType = hdfFmType.Value.Trim();
int companyId = Int32.Parse(hdfCompanyId.Value.Trim());
// ... Load data
FmTypeViewConditionGateway fmTypeViewConditionGateway = new FmTypeViewConditionGateway();
fmTypeViewConditionGateway.LoadByFmTypeConditionId(fmType, companyId, int.Parse(ddlCondition1.SelectedValue));
conditionValue = fmTypeViewConditionGateway.GetColumn_(fmType, companyId, int.Parse(ddlCondition1.SelectedValue));
conditionName = fmTypeViewConditionGateway.GetName(fmType, companyId, int.Parse(ddlCondition1.SelectedValue));
toDolistNavigator.Load(whereClause, orderByClause, conditionValue, conditionName, tbxCondition1.Text.Trim(), companyId, fmType);
return (ToDoListNavigatorTDS)toDolistNavigator.Data;
}
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"));
FmViewTDS dataSet = new FmViewTDS();
dataSet.FmViewConditionNew.Merge(fmViewConditionNew, true);
FmViewConditionNew model = new FmViewConditionNew(dataSet);
int conditionNumber = model.GetNewConditionNumber();
ddlConditionNumber.SelectedValue = conditionNumber.ToString();
FmTypeViewConditionGateway fmTypeViewConditionGateway = new FmTypeViewConditionGateway();
fmTypeViewConditionGateway.LoadByFmTypeConditionId(hdfFmType.Value, int.Parse(hdfCompanyId.Value), conditionId);
string type = fmTypeViewConditionGateway.GetType(hdfFmType.Value, int.Parse(hdfCompanyId.Value), conditionId);
if (ddlOperator.SelectedItem == null)
{
FmTypeViewOperatorList fmTypeViewOperatorList = new FmTypeViewOperatorList(new DataSet());
fmTypeViewOperatorList.LoadAndAddItem(type, Int32.Parse(hdfCompanyId.Value));
ddlOperator.DataSource = fmTypeViewOperatorList.Table;
ddlOperator.DataValueField = "Sign";
ddlOperator.DataTextField = "Operator";
ddlOperator.DataBind();
ddlOperator.SelectedIndex = 0;
}
if ((type == "String") || (type == "Date") || (type == "Int") || (type == "Decimal"))
{
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))))
{
FmViewTDS dataSet = new FmViewTDS();
dataSet.FmViewConditionNew.Merge(fmViewConditionNew, true);
FmViewConditionNewGateway gateway = new FmViewConditionNewGateway(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)
{
FmTypeViewConditionGateway fmTypeViewConditionGateway = new FmTypeViewConditionGateway();
fmTypeViewConditionGateway.LoadByFmTypeConditionId(hdfFmType.Value, int.Parse(hdfCompanyId.Value), conditionId);
string type = fmTypeViewConditionGateway.GetType(hdfFmType.Value, int.Parse(hdfCompanyId.Value), conditionId);
ddlName.DataBind();
ddlName.SelectedValue = conditionId.ToString();
FmTypeViewOperatorList fmTypeViewOperatorList = new FmTypeViewOperatorList(new DataSet());
fmTypeViewOperatorList.LoadAndAddItem(type, Int32.Parse(hdfCompanyId.Value));
ddlOperator.DataSource = fmTypeViewOperatorList.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
FmTypeViewConditionItemList fmTypeViewConditionItemList = new FmTypeViewConditionItemList(new DataSet());
fmTypeViewConditionItemList.LoadAndAddItemInView(hdfFmType.Value, Int32.Parse(hdfCompanyId.Value), conditionId);
ddlValue.DataSource = fmTypeViewConditionItemList.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
if (ddlName.SelectedItem.Text == "Category")
{
//Category for Units
Category category = new Category(categoriesTDS);
category.LoadAllUnitTypes(Int32.Parse(hdfCompanyId.Value));
Session["categoriesTDSForAddView"] = categoriesTDS;
if (category.Table.Rows.Count > 0)
{
GetNodeForCategory(0, ddlValue);//Equipments
level = -1;
GetNodeForCategory(-1, ddlValue);//Vehicles
ddlValue.DataBind();
string categorySelected = gateway.GetValue_(id);
foreach (ListItem li in ddlValue.Items)
{
if (li.Text.Contains(categorySelected))
{
li.Selected = true;
}
}
}
else
{
ddlValue.Visible = false;
}
}
else
{
//State for Services
FmTypeViewStateList fmTypeViewStateList = new FmTypeViewStateList(new DataSet());
fmTypeViewStateList.LoadAndAddItem(Int32.Parse(hdfCompanyId.Value));
if (fmTypeViewStateList.Table.Rows.Count > 0)
{
ddlValue.DataSource = fmTypeViewStateList.Table;
ddlValue.DataValueField = "State";
ddlValue.DataTextField = "State";
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 GetOrderByClause()
{
// Get tableName
int companyId = Int32.Parse(hdfCompanyId.Value.Trim());
string fmType = hdfFmType.Value;
FmTypeViewSortGateway fmTypeViewSortGateway = new FmTypeViewSortGateway();
fmTypeViewSortGateway.LoadByFmTypeSortId(fmType, companyId, int.Parse(ddlSortBy.SelectedValue));
string tableName = fmTypeViewSortGateway.GetTable_(fmType, companyId, int.Parse(ddlSortBy.SelectedValue));
string columnName = fmTypeViewSortGateway.GetColumn_(fmType, companyId, int.Parse(ddlSortBy.SelectedValue));
string conditionName = fmTypeViewSortGateway.GetName(fmType, companyId, int.Parse(ddlSortBy.SelectedValue));
if (tableName == "LFS_FM_TODOLIST") tableName = "LFTDL";
if (tableName == "LFS_EMPLOYEE") tableName = "LRE";
if (tableName == "LFS_FM_UNIT") tableName = "LFU";
if (tableName == "LFS_FM_TODOLIST_ACTIVITY") tableName = "LFTDLA";
FmTypeViewConditionGateway fmTypeViewConditionGateway = new FmTypeViewConditionGateway();
fmTypeViewConditionGateway.LoadByFmTypeConditionId(fmType, companyId, int.Parse(ddlCondition1.SelectedValue));
string conditionValue = fmTypeViewConditionGateway.GetColumn_(fmType, companyId, int.Parse(ddlCondition1.SelectedValue));
// Get order by clause
string orderBy = "";
// ... For Names Assigned To
if (ddlSortBy.SelectedValue == "3")
{
orderBy = " ORDER BY LastAssignedTeamMemberName ASC";
}
else
{
// .... For CreatedBy
if (ddlSortBy.SelectedValue == "2")
{
orderBy = " ORDER BY OwnerName ASC";
}
else
{
// For Fields
if (columnName == "CreationDate")
{
orderBy = " ORDER BY LFTDL.CreationDate DESC";
}
else
{
if (columnName == "DueDate")
{
orderBy = " ORDER BY LFTDL.DueDate DESC";
}
else
{
if (conditionName == "State")
{
orderBy = " ORDER BY LFTDL.State ASC";
}
else
{
orderBy = " ORDER BY " + tableName + "." + columnName;
}
}
}
}
}
return orderBy;
}
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);
}
FmTypeViewConditionGateway fmTypeViewConditionGateway = new FmTypeViewConditionGateway();
fmTypeViewConditionGateway.LoadByFmTypeConditionId(hdfFmType.Value, int.Parse(hdfCompanyId.Value), conditionId);
type = fmTypeViewConditionGateway.GetType(hdfFmType.Value, int.Parse(hdfCompanyId.Value), conditionId);
// Date fields validate
if (type == "Date")
{
// For complete date and 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.Text = "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)";
}
}
}
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"));
FmTypeViewConditionGateway fmTypeViewConditionGateway = new FmTypeViewConditionGateway();
fmTypeViewConditionGateway.LoadByFmTypeConditionId(hdfFmType.Value, int.Parse(hdfCompanyId.Value), int.Parse(ddlName.SelectedValue));
string type = fmTypeViewConditionGateway.GetType(hdfFmType.Value, int.Parse(hdfCompanyId.Value), int.Parse(ddlName.SelectedValue));
int index = grdConditions.SelectedIndex;
FmTypeViewOperatorList fmTypeViewOperatorList = new FmTypeViewOperatorList(new DataSet());
fmTypeViewOperatorList.LoadAndAddItem(type, Int32.Parse(hdfCompanyId.Value));
ddlOperator.DataSource = fmTypeViewOperatorList.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
FmTypeViewConditionItemList fmTypeViewConditionItemList = new FmTypeViewConditionItemList(new DataSet());
fmTypeViewConditionItemList.LoadAndAddItemInView(hdfFmType.Value, Int32.Parse(hdfCompanyId.Value), int.Parse(ddlName.SelectedValue));
ddlValue.DataSource = fmTypeViewConditionItemList.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
if (ddlName.SelectedItem.Text == "Category")
{//Category for Units
Category category = new Category(categoriesTDS);
category.LoadAllUnitTypes(Int32.Parse(hdfCompanyId.Value));
Session["categoriesTDSForAddView"] = categoriesTDS;
if (category.Table.Rows.Count > 0)
{
GetNodeForCategory(0, ddlValue);//Equipments
level = -1;
GetNodeForCategory(-1, ddlValue);//Vehicles
ddlValue.DataBind();
ddlValue.SelectedIndex = 0;
ibtnAdd.Visible = true;
}
else
{
ddlValue.Visible = false;
ibtnAdd.Visible = false;
lblValidateValue.Visible = true;
}
}
else
{
//State for Services
FmTypeViewStateList fmTypeViewStateList = new FmTypeViewStateList(new DataSet());
fmTypeViewStateList.LoadAndAddItem(Int32.Parse(hdfCompanyId.Value));
if (fmTypeViewStateList.Table.Rows.Count > 0)
{
ddlValue.DataSource = fmTypeViewStateList.Table;
ddlValue.DataValueField = "State";
ddlValue.DataTextField = "State";
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 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);
FmTypeViewConditionGateway fmTypeViewConditionGateway = new FmTypeViewConditionGateway();
fmTypeViewConditionGateway.LoadByFmTypeConditionId(hdfFmType.Value, int.Parse(hdfCompanyId.Value), conditionId);
string type = fmTypeViewConditionGateway.GetType(hdfFmType.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.Replace("-", "");
value = value.Trim();
}
if (type == "Boolean")
{
if (((RadioButton)grdConditions.FooterRow.FindControl("rbtnYes")).Checked)
value = "Yes";
if (((RadioButton)grdConditions.FooterRow.FindControl("rbtnNo")).Checked)
value = "No";
}
FmViewTDS dataSet = new FmViewTDS();
dataSet.FmViewConditionNew.Merge(fmViewConditionNew, true);
FmViewConditionNew model = new FmViewConditionNew(dataSet);
model.Insert(conditionId, name, operator_, sign, conditionNumber, value, false, false);
Session.Remove("fmViewConditionNewDummy");
fmViewConditionNew = dataSet.FmViewConditionNew;
Session["fmViewConditionNew"] = dataSet.FmViewConditionNew;
grdConditions.DataBind();
grdConditions.PageIndex = grdConditions.PageCount - 1;
}
}
}
private bool ValidateFooterNext()
{
int conditionId = int.Parse(((DropDownList)grdConditions.FooterRow.FindControl("ddlName")).SelectedValue);
FmTypeViewConditionGateway fmTypeViewConditionGateway = new FmTypeViewConditionGateway();
fmTypeViewConditionGateway.LoadByFmTypeConditionId(hdfFmType.Value, int.Parse(hdfCompanyId.Value), conditionId);
string type = fmTypeViewConditionGateway.GetType(hdfFmType.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;
}
/// <summary>
/// ParserLogic
/// </summary>
/// <param name="originalLogic">originalLogic</param>
/// <param name="fmType">fmType</param>
/// <param name="companyId">companyId</param>
/// <returns>parser logic string</returns>
public string ParserLogic(string originalLogic, string fmType, int companyId)
{
string newLogic = "";
if (fmType == "Services")
{
if (originalLogic.Length > 0)
{
newLogic = "(LFS.Deleted = 0) AND (LFU.Deleted = 0) AND (LFS.COMPANY_ID = {0}) AND (LFU.COMPANY_ID = {0}) AND ";
}
else
{
newLogic = "(LFS.Deleted = 0) AND (LFU.Deleted = 0) AND (LFS.COMPANY_ID = {0}) AND (LFU.COMPANY_ID = {0})";
}
}
if (fmType == "Units")
{
if (originalLogic.Length > 0)
{
newLogic = "(FMU.Deleted = 0) AND (FMU.State <> 'Archived') AND (FMC.Deleted = 0) AND (FMU.COMPANY_ID = {0}) AND (FMC.COMPANY_ID = {0}) AND ";
}
else
{
newLogic = "(FMU.Deleted = 0) AND (FMU.State <> 'Archived') AND (FMC.Deleted = 0) AND (FMU.COMPANY_ID = {0}) AND (FMU.COMPANY_ID = {0})";
}
}
foreach (FmViewTDS.FmViewConditionNewRow rowNew in (FmViewTDS.FmViewConditionNewDataTable)Data.Tables["FmViewConditionNew"])
{
if (!rowNew.Deleted)
{
string originalCondition = "Condition" + rowNew.ConditionNumber;
FmTypeViewCondition fmTypeViewCondition = new FmTypeViewCondition();
fmTypeViewCondition.LoadByFmTypeConditionId(fmType, companyId, rowNew.ConditionID);
FmTypeViewConditionGateway fmTypeViewConditionGateway = new FmTypeViewConditionGateway(fmTypeViewCondition.Data);
string tableName = fmTypeViewConditionGateway.GetTable_(fmType, companyId, rowNew.ConditionID);
string conditionName = fmTypeViewConditionGateway.GetName(fmType, companyId, rowNew.ConditionID);
string column = fmTypeViewConditionGateway.GetColumn_(fmType, companyId, rowNew.ConditionID);
if (fmType == "Services")
{
switch (tableName)
{
case "LFS_FM_SERVICE":
tableName = "LFS";
break;
case "LFS_FM_SERVICE_VEHICLE":
tableName = "LFSV";
break;
case "LFS_FM_UNIT":
tableName = "LFU";
break;
case "LFS_FM_RULE":
tableName = "LFR";
break;
case "LFS_FM_CHECKLIST":
tableName = "LFC";
break;
case "LFS_FM_COMPANYLEVEL":
tableName = "LFCL";
break;
}
if (conditionName == "Created By") tableName = "LEOwner";
if (conditionName == "Assigned To") tableName = "LEAssignedTo";
}
if (fmType == "Units")
{
switch (tableName)
{
case "LFS_FM_UNIT":
tableName = "FMU";
break;
case "LFS_FM_COMPANYLEVEL":
if (column == "CompanyLevel")
{
column = "Name";
tableName = "FMC";
}
break;
case "LFS_FM_UNIT_VEHICLE":
tableName = "FMUV";
break;
case "LFS_COUNTRY":
if (column == "LicenseCountry")
{
tableName = "LCL";
}
if (column == "OwnerCountry")
{
tableName = "LCO";
}
column = "Name";
break;
case "LFS_PROVINCE":
if (column == "LicenseState")
{
tableName = "LPL";
}
if (column == "OwnerState")
{
tableName = "LPO";
}
column = "Name";
break;
}
}
string type = fmTypeViewConditionGateway.GetType(fmType, companyId, rowNew.ConditionID);
string sign = rowNew.Sign;
string conditionValue = rowNew.Value_;
string newCondition = "";
// Search
if (conditionValue == "%")
{
if (type == "Date")
{
if (sign == "=")
{
newCondition = newCondition + " ((CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime) IS NOT NULL)";
newCondition = newCondition + " OR (CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime) IS NULL))";
}
else
{
if (sign == "<>")
{
newCondition = newCondition + " (CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime) IS NULL)";
}
else
{
newCondition = newCondition + " ((CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime) IS NOT NULL)";
newCondition = newCondition + " OR (CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime) IS NULL))";
}
}
}
else
{
newCondition = newCondition + " ((" + tableName + "." + column + " LIKE '%')";
newCondition = newCondition + " OR (" + tableName + "." + column + " IS NULL))";
}
}
else
{
if (conditionValue == "")
{
if (sign == "<>")
{
newCondition = newCondition + tableName + "." + column + " IS NOT NULL ";
}
else
{
newCondition = newCondition + tableName + "." + column + " IS NULL ";
}
}
else
{
conditionValue = conditionValue.Replace("'", "''");
if ((type == "Int") || (type == "Decimal") || (type == "Boolean"))
{
if (type == "Boolean")
{
if (conditionValue == "Yes") conditionValue = "1";
if (conditionValue == "No") conditionValue = "0";
if (fmType == "Units")
{
if (column != "WithAlarms" && column != "WithServicesLate" && column != "WithChecklistInUnknownState")
{
newCondition = newCondition + tableName + "." + column + sign + conditionValue;
}
else
{
if (column == "WithAlarms")
{
if (conditionValue == "1")
{
newCondition = newCondition + " (FMU.UnitID IN " +
" (SELECT DISTINCT FMU1.UnitID " +
" FROM LFS_FM_UNIT FMU1 INNER JOIN " +
" LFS_FM_CHECKLIST FMCL1 ON FMCL1.UnitID = FMU1.UnitID INNER JOIN " +
" LFS_FM_RULE FMR1 ON FMCL1.RuleID = FMR1.RuleID " +
" WHERE (FMR1.Alarm = 1) AND (FMCL1.State = 'Warning' OR FMCL1.State = 'Expired') AND (FMCL1.Deleted = 0) AND (FMU1.Deleted = 0) AND (FMR1.Deleted = 0)" +
" ) )";
}
if (conditionValue == "0")
{
newCondition = newCondition + " (FMU.UnitID NOT IN " +
" (SELECT DISTINCT FMU1.UnitID " +
" FROM LFS_FM_UNIT FMU1 INNER JOIN " +
" LFS_FM_CHECKLIST FMCL1 ON FMCL1.UnitID = FMU1.UnitID INNER JOIN " +
" LFS_FM_RULE FMR1 ON FMCL1.RuleID = FMR1.RuleID " +
" WHERE (FMCL1.State = 'Warning' OR FMCL1.State = 'Expired') AND (FMCL1.Deleted = 0) AND (FMU1.Deleted = 0) AND (FMR1.Deleted = 0)" +
" ) )";
}
}
if (column == "WithServicesLate")
{
if (conditionValue == "1")
{
newCondition = newCondition + " (FMU.UnitID IN " +
" (SELECT DISTINCT FMU1.UnitID " +
" FROM LFS_FM_UNIT FMU1 INNER JOIN " +
" LFS_FM_SERVICE FMS1 ON FMU1.UnitID = FMS1.UnitID " +
" WHERE CONVERT(VARCHAR(10), FMS1.AssignDeadlineDate, 101) < CONVERT(VARCHAR(10), getdate(), 101) AND (FMS1.CompleteWorkDateTime IS NULL)" +
" AND (FMS1.State <> 'Unassigned') AND (FMS1.State <> 'Completed') AND (FMS1.State <> 'Rejected') OR (FMS1.State = 'Assigned/Expired') OR (FMS1.State = 'In Progress/Expired') " +
" OR (FMS1.CompleteWorkDateTime IS NOT NULL) AND (FMS1.CompleteWorkDateTime > FMS1.AssignDeadlineDate) " +
" ) )";
}
if (conditionValue == "0")
{
newCondition = newCondition + " (FMU.UnitID NOT IN " +
" (SELECT DISTINCT FMU1.UnitID " +
" FROM LFS_FM_UNIT FMU1 INNER JOIN " +
" LFS_FM_SERVICE FMS1 ON FMU1.UnitID = FMS1.UnitID " +
" WHERE CONVERT(VARCHAR(10), FMS1.AssignDeadlineDate, 101) < CONVERT(VARCHAR(10), getdate(), 101) AND (FMS1.CompleteWorkDateTime IS NULL)" +
" AND (FMS1.State <> 'Unassigned') AND (FMS1.State <> 'Completed') AND (FMS1.State <> 'Rejected') OR (FMS1.State = 'Assigned/Expired') OR (FMS1.State = 'In Progress/Expired') " +
" OR (FMS1.CompleteWorkDateTime IS NOT NULL) AND (FMS1.CompleteWorkDateTime > FMS1.AssignDeadlineDate) " +
" ) )";
}
}
if (column == "WithChecklistInUnknownState")
{
if (conditionValue == "1")
{
newCondition = newCondition + " (FMU.UnitID IN " +
" (SELECT DISTINCT FMU1.UnitID " +
" FROM LFS_FM_UNIT FMU1 INNER JOIN " +
" LFS_FM_CHECKLIST FMCL1 ON FMU1.UnitID = FMCL1.UnitID " +
" WHERE (FMU1.Deleted = 0) AND (FMCL1.Deleted = 0) AND (FMCL1.State = 'Unknown') " +
" ) )";
}
if (conditionValue == "0")
{
newCondition = newCondition + " (FMU.UnitID NOT IN " +
" (SELECT DISTINCT FMU1.UnitID " +
" FROM LFS_FM_UNIT FMU1 INNER JOIN " +
" LFS_FM_CHECKLIST FMCL1 ON FMU1.UnitID = FMCL1.UnitID " +
" WHERE (FMU1.Deleted = 0) AND (FMCL1.Deleted = 0) AND (FMCL1.State = 'Unknown') " +
" ) )";
}
}
}
}
else
{
newCondition = newCondition + tableName + "." + column + sign + conditionValue;
}
}
else
{
newCondition = newCondition + tableName + "." + column + sign + conditionValue;
}
}
else
{
if (sign == "=")
{
if (type != "Date")
{
if (conditionValue.Contains("\""))
{
conditionValue = conditionValue.Replace("\"", "");
newCondition = newCondition + " (" + tableName + "." + column + " = '" + conditionValue + "')";
}
else
{
if (column == "Categories")
{
ArrayList categoriesId = new ArrayList();
categoriesId = GetConditionForCategory(conditionValue, companyId);
newCondition = newCondition + " (FMU.UnitID IN " +
" (SELECT DISTINCT FMU1.UnitID " +
" FROM LFS_FM_UNIT FMU1 INNER JOIN " +
" LFS_FM_UNIT_CATEGORY FMUC ON FMU1.UnitID = FMUC.UnitID " +
" WHERE (FMU1.Deleted = 0) AND (FMUC.Deleted = 0) AND ";
int cantOfCategory = categoriesId.Count;
int auxOfCantOfCategory = 0;
foreach (int categoryId in categoriesId)
{
auxOfCantOfCategory++;
if (auxOfCantOfCategory != cantOfCategory)
{
newCondition = newCondition + " (FMUC.CategoryID = " + categoryId + " ) OR ";
}
else
{
newCondition = newCondition + " (FMUC.CategoryID = " + categoryId + " ) ";
}
}
newCondition = newCondition + " ) )";
}
else
{
newCondition = newCondition + " (" + tableName + "." + column + " LIKE '%" + conditionValue + "%')";
}
}
}
else
{
// Date
if (conditionValue.Length > 7)
{
newCondition = newCondition + " CAST(CONVERT(varchar,"+ tableName + "." + column +", 101) AS smalldatetime) "+ sign + "'" + conditionValue + "'";
}
else
{
newCondition = newCondition + " CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime) LIKE '%" + conditionValue + "%'";
}
}
}
else
{
if (column == "Notes")
{
newCondition = newCondition + tableName + "." + column + " NOT LIKE '%" + conditionValue + "%'";
}
else
{
if (type != "Date")
{
newCondition = newCondition + tableName + "." + column + sign + "'" + conditionValue + "'";
}
else
{
// Date
if (conditionValue.Length > 7)
{
if (sign == "<>")
{
newCondition = newCondition + " ((CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime) " + sign + "'" + conditionValue + "')";
newCondition = newCondition + " OR (CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime) IS NULL))";
}
else
{
newCondition = newCondition + " CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime) " + sign + "'" + conditionValue + "'";
}
}
else
{
if (sign == "<>")
{
newCondition = newCondition + " ((CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime) NOT LIKE '%" + conditionValue + "%')";
newCondition = newCondition + " OR (CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime) IS NULL))";
}
else
{
if (sign == ">" || sign == "<=")
{
newCondition = newCondition + " CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime)" + sign + " '12/31/" + conditionValue + "'";
}
else
{
newCondition = newCondition + " CAST(CONVERT(varchar," + tableName + "." + column + ", 101) AS smalldatetime)" + sign + " '" + conditionValue + "'";
}
}
}
}
}
}
}
}
}
originalLogic = originalLogic.Replace(originalCondition, newCondition);
}
}
if (originalLogic.Length > 0)
{
newLogic = newLogic + originalLogic;
}
return newLogic;
}
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);
FmTypeViewConditionGateway fmTypeViewConditionGateway = new FmTypeViewConditionGateway();
fmTypeViewConditionGateway.LoadByFmTypeConditionId(hdfFmType.Value, int.Parse(hdfCompanyId.Value), conditionId);
string type = fmTypeViewConditionGateway.GetType(hdfFmType.Value, int.Parse(hdfCompanyId.Value), conditionId);
string value = "";
if ((type == "String") || (type == "Date") || (type == "Int") || (type == "Decimal") )
{
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.Replace("-", "");
value = value.Trim();
}
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";
}
FmViewTDS dataSet = new FmViewTDS();
dataSet.FmViewConditionNew.Merge(fmViewConditionNew, true);
FmViewConditionNew model = new FmViewConditionNew(dataSet);
model.Update(id, conditionId, name, operator_, sign, conditionNumber, value, false, false);
Session["fmViewConditionNew"] = dataSet.FmViewConditionNew;
fmViewConditionNew = dataSet.FmViewConditionNew;
}
else
{
e.Cancel = true;
}
}
/// <summary>
/// GetConditionsForSummary
/// </summary>
/// <param name="fmType">fmType</param>
/// <param name="companyId">companyId</param>
/// <returns>Conditins for Summary</returns>
public string GetConditionsForSummary(string fmType, int companyId)
{
string conditions = "";
foreach (FmViewTDS.FmViewConditionNewRow row in (FmViewTDS.FmViewConditionNewDataTable)Data.Tables["FmViewConditionNew"])
{
if (!row.Deleted)
{
FmTypeViewCondition fmTypeViewCondition = new FmTypeViewCondition();
fmTypeViewCondition.LoadByFmTypeConditionId(fmType, companyId, row.ConditionID);
FmTypeViewConditionGateway fmTypeViewConditionGateway = new FmTypeViewConditionGateway(fmTypeViewCondition.Data);
if (row.Value_ == "")
{
conditions = conditions + "Condition" + row.ConditionNumber + ": " + fmTypeViewConditionGateway.GetName(fmType, companyId, row.ConditionID) + " " + row.Sign + " (empty), ";
}
else
{
conditions = conditions + "Condition" + row.ConditionNumber + ": " + fmTypeViewConditionGateway.GetName(fmType, companyId, row.ConditionID) + " " + row.Sign + " " + row.Value_ + ", ";
}
}
}
if (conditions.Length > 2)
{
conditions = conditions.Substring(0, conditions.Length - 2);
}
return conditions;
}
private string modifyWhereClauseForCondition(string whereClause, int conditionId, string textForSearch)
{
int companyId = Int32.Parse(hdfCompanyId.Value.Trim());
string fmType = hdfFmType.Value;
FmTypeViewConditionGateway fmTypeViewConditionGateway = new FmTypeViewConditionGateway();
fmTypeViewConditionGateway.LoadByFmTypeConditionId(fmType, companyId, conditionId);
string conditionValue = fmTypeViewConditionGateway.GetColumn_(fmType, companyId, conditionId);
string conditionName = fmTypeViewConditionGateway.GetName(fmType, companyId, conditionId);
string tableName = fmTypeViewConditionGateway.GetTable_(fmType, companyId, conditionId);
if (tableName == "LFS_FM_SERVICE") tableName = "LFS";
if (tableName == "LFS_FM_SERVICE_VEHICLE") tableName = "LFSV";
if (tableName == "LFS_FM_UNIT") tableName = "LFU";
if (tableName == "LFS_FM_RULE") tableName = "LFR";
if (tableName == "LFS_FM_CHECKLIST") tableName = "LFC";
if (tableName == "LFS_FM_COMPANYLEVEL") tableName = "LFCL";
if (conditionName == "Created By") tableName = "LEOwner";
if (conditionName == "Assigned To") tableName = "LEAssignedTo";
// FOR TEXT FIELDS. (Service Number, Unit Code, Unit Description, Notes, Created by, Assigned to, Checklist Rules)
if ((conditionValue == "Number") || (conditionValue == "UnitCode") || (conditionValue == "Description") || (conditionValue == "VIN") || (conditionValue == "Notes") || (conditionValue == "FullName") || (conditionValue == "Name") || (conditionValue == "VIN") || (conditionValue == "State") || (conditionValue == "Mileage") || (conditionValue == "StartWorkOutOfServiceTime") || (conditionValue == "StartWorkMileage") || (conditionValue == "CompleteWorkBackToServiceTime") || (conditionValue == "CompleteWorkMileage") || (conditionValue == "CompleteWorkDetailDescription"))
{
// ... Search
if (textForSearch == "%")
{
whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " LIKE '%')";
whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))";
}
else
{
if (textForSearch == "")
{
whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " IS NULL )";
}
else
{
if (textForSearch.Contains("\""))
{
if (conditionValue == "Notes")
{
textForSearch = textForSearch.Replace("'", "''");
whereClause = whereClause + "AND (" + tableName + "." + conditionValue + " LIKE '%" + textForSearch + "%')";
}
else
{
textForSearch = textForSearch.Replace("\"", "");
whereClause = whereClause + "AND (" + tableName + "." + conditionValue + " = '" + textForSearch + "')";
}
}
else
{
textForSearch = textForSearch.Replace("'", "''");
whereClause = whereClause + "AND (" + tableName + "." + conditionValue + " LIKE '%" + textForSearch + "%')";
}
}
}
}
// FOR DATE FIELDS. (StartWorkDateTime, CompleteWorkDateTime, AssignDeadlineDate)
if ((conditionValue == "StartWorkDateTime") || (conditionValue == "CompleteWorkDateTime") || (conditionValue == "AssignDeadlineDate") || (conditionValue == "DateTime_") || (conditionValue == "AssignDateTime") || (conditionValue == "AcceptDateTime") || (conditionValue == "StartWorkOutOfServiceDate") || (conditionValue == "CompleteWorkBackToServiceDate"))
{
// ... Search
if (textForSearch == "")
{
whereClause = whereClause + " AND ( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NULL)";
}
else
{
if (textForSearch == "%")
{
whereClause = whereClause + " AND (( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NOT NULL) OR ";
whereClause = whereClause + "( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NULL))";
}
else
{
if ((Validator.IsValidDate(textForSearch)) && (textForSearch.Length > 7))
{
//whereClause = whereClause + " AND ( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) = '" + textForSearch + "')";
whereClause = whereClause + " AND ( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) LIKE '%" + textForSearch + "%')";
}
else
{
whereClause = whereClause + " AND ( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) LIKE '%" + textForSearch + "%')";
}
}
}
}
// FOR BOOLEAN FIELDS
if ((conditionValue == "MTO") || (conditionValue == "CompleteWorkDetailPreventable"))
{
if (textForSearch != "")
{
if ((textForSearch.ToUpper() == "Y") || (textForSearch.ToUpper() == "YES"))
{
whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " = 1)";
}
else
{
if ((textForSearch.ToUpper() == "N") || (textForSearch.ToUpper() == "NO"))
{
whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " = 0)";
}
else
{
if (textForSearch == "%")
{
whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " = 1) OR (" + tableName + "." + conditionValue + " = 0))";
}
}
}
}
}
// FOR DECIMAL FIELDS
if (conditionValue == "CompleteWorkDetailTMLabourHours")
{
// ... Search
if (textForSearch == "%")
{
whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " LIKE '%')";
whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))";
}
else
{
if (textForSearch == "")
{
whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " IS NULL )";
}
else
{
whereClause = whereClause + " AND (" + tableName + "." + conditionValue + "=" + textForSearch + ")";
}
}
}
return whereClause;
}
private string GetOrderByClause()
{
// Get tableName
int companyId = Int32.Parse(hdfCompanyId.Value.Trim());
string fmType = hdfFmType.Value;
FmTypeViewSortGateway fmTypeViewSortGateway = new FmTypeViewSortGateway();
fmTypeViewSortGateway.LoadByFmTypeSortId(fmType, companyId, int.Parse(ddlSortBy.SelectedValue));
string tableName = fmTypeViewSortGateway.GetTable_(fmType, companyId, int.Parse(ddlSortBy.SelectedValue));
string columnName = fmTypeViewSortGateway.GetColumn_(fmType, companyId, int.Parse(ddlSortBy.SelectedValue));
string conditionName = fmTypeViewSortGateway.GetName(fmType, companyId, int.Parse(ddlSortBy.SelectedValue));
if (tableName == "LFS_FM_SERVICE") tableName = "LFS";
if (tableName == "LFS_FM_UNIT") tableName = "LFU";
if (tableName == "LFS_FM_RULE") tableName = "LFR";
if (conditionName == "Created By") tableName = "LEOwner";
if (conditionName == "Assigned To") tableName = "LEAssignedTo";
FmTypeViewConditionGateway fmTypeViewConditionGateway = new FmTypeViewConditionGateway();
fmTypeViewConditionGateway.LoadByFmTypeConditionId(fmType, companyId, int.Parse(ddlCondition1.SelectedValue));
string conditionValue = fmTypeViewConditionGateway.GetColumn_(fmType, companyId, int.Parse(ddlCondition1.SelectedValue));
// Get order by clause
string orderBy = "";
if (columnName == "Date")
{
switch (conditionValue)
{
case "StartWorkDateTime":
orderBy = " ORDER BY LFS.StartWorkDateTime DESC";
break;
case "CompleteWorkDateTime":
orderBy = " ORDER BY LFS.CompleteWorkDateTime DESC";
break;
case "AssignDeadlineDate":
orderBy = " ORDER BY LFS.AssignDeadlineDate DESC";
break;
case "DateTime_":
orderBy = " ORDER BY LFS.DateTime_ DESC";
break;
case "AssignDateTime":
orderBy = " ORDER BY LFS.AssignDateTime DESC";
break;
case "AcceptDateTime":
orderBy = " ORDER BY LFS.AcceptDateTime DESC";
break;
case "StartWorkOutOfServiceDate":
orderBy = " ORDER BY LFS.StartWorkOutOfServiceDate DESC";
break;
case "CompleteWorkBackToServiceDate":
orderBy = " ORDER BY LFS.CompleteWorkBackToServiceDate DESC";
break;
default:
orderBy = " ORDER BY LFS.ServiceID ASC";
break;
}
}
else
{
if (columnName == "Number")
{
orderBy = String.Format(" ORDER BY CASE WHEN 1 = IsNumeric({0}.{1}) THEN Cast({0}.{1} AS INT) END ", tableName, columnName);
}
else
{
if (conditionName == "Problem Description")
{
orderBy = String.Format(" ORDER BY CAST({0}.{1} AS nvarchar) ", tableName, columnName);
}
else
{
orderBy = " ORDER BY " + tableName + "." + columnName;
}
}
}
return orderBy;
}
private string modifyWhereClauseForCondition(string whereClause, int conditionId, string textForSearch)
{
int companyId = Int32.Parse(hdfCompanyId.Value.Trim());
string fmType = hdfFmType.Value;
FmTypeViewConditionGateway fmTypeViewConditionGateway = new FmTypeViewConditionGateway();
fmTypeViewConditionGateway.LoadByFmTypeConditionId(fmType, companyId, conditionId);
string conditionValue = fmTypeViewConditionGateway.GetColumn_(fmType, companyId, conditionId);
string tableName = fmTypeViewConditionGateway.GetTable_(fmType, companyId, conditionId);
switch (tableName)
{
case "LFS_FM_UNIT":
tableName = "FMU";
break;
case "LFS_FM_UNIT_COST_HISTORY":
tableName = "FMUCH";
break;
case "LFS_FM_UNIT_VEHICLE":
tableName = "FMUV";
break;
case "LFS_FM_COMPANYLEVEL":
if (conditionValue == "CompanyLevel")
{
conditionValue = "Name";
tableName = "FMC";
}
break;
case "LFS_COUNTRY":
if (conditionValue == "LicenseCountry")
{
tableName = "LCL";
}
if (conditionValue == "OwnerCountry")
{
tableName = "LCO";
}
conditionValue = "Name";
break;
case "LFS_PROVINCE":
if (conditionValue == "LicenseState")
{
tableName = "LPL";
}
if (conditionValue == "OwnerState")
{
tableName = "LPO";
}
conditionValue = "Name";
break;
}
// FOR TEXT FIELDS.
if ((conditionValue == "UnitCode") || (conditionValue == "VIN") || (conditionValue == "Description") || (conditionValue == "State") || (conditionValue == "Manufacturer") || (conditionValue == "Model") || (conditionValue == "Year_") || (conditionValue == "Categories") || (conditionValue == "Name") || (conditionValue == "OwnerType") || (conditionValue == "Notes") || (conditionValue == "LicenseCountry") || (conditionValue == "LicenseState") || (conditionValue == "LicensePlateNumbver") || (conditionValue == "AportionedTagNumber") || (conditionValue == "ActualWeight") || (conditionValue == "RegisteredWeight") || (conditionValue == "TireSizeFront") || (conditionValue == "TireSizeBack") || (conditionValue == "NumberOfAxes") || (conditionValue == "FuelType") || (conditionValue == "BeginningOdometer") || (conditionValue == "OwnerCountry") || (conditionValue == "OwnerState") || (conditionValue == "OwnerName") || (conditionValue == "OwnerContact") || (conditionValue == "NotQualifiedExplain") || (conditionValue == "InsuranceClass") || (conditionValue == "InsuranceClassRyderSpecified"))
{
// ... Search
if (textForSearch == "%")
{
whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " LIKE '%')";
whereClause = whereClause + " OR (" + tableName + "." + conditionValue + " IS NULL))";
}
else
{
if (textForSearch == "")
{
whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " IS NULL )";
}
else
{
if (textForSearch.Contains("\""))
{
if (conditionValue == "Notes")
{
textForSearch = textForSearch.Replace("'", "''");
whereClause = whereClause + "AND (" + tableName + "." + conditionValue + " LIKE '%" + textForSearch + "%')";
}
else
{
textForSearch = textForSearch.Replace("\"", "");
whereClause = whereClause + "AND (" + tableName + "." + conditionValue + " = '" + textForSearch + "')";
}
}
else
{
textForSearch = textForSearch.Replace("'", "''");
whereClause = whereClause + "AND (" + tableName + "." + conditionValue + " LIKE '%" + textForSearch + "%')";
}
}
}
}
// FOR BOOLEAN FIELDS
if ((conditionValue == "IsTowable") || (conditionValue == "IsReeferEquipped") || (conditionValue == "IsPTOEquipped"))
{
if (textForSearch != "")
{
if ((textForSearch.ToUpper() == "Y") || (textForSearch.ToUpper() == "YES"))
{
whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " = 1)";
}
else
{
if ((textForSearch.ToUpper() == "N") || (textForSearch.ToUpper() == "NO"))
{
whereClause = whereClause + " AND (" + tableName + "." + conditionValue + " = 0)";
}
else
{
if (textForSearch == "%")
{
whereClause = whereClause + " AND ((" + tableName + "." + conditionValue + " = 1) OR (" + tableName + "." + conditionValue + " = 0))";
}
}
}
}
}
// FOR DATE FIELDS. (AcquisitionDate, QualifiedDate, NotQualifiedDate)
if ((conditionValue == "AcquisitionDate") || (conditionValue == "QualifiedDate") || (conditionValue == "NotQualifiedDate"))
{
// ... Search
if (textForSearch == "")
{
whereClause = whereClause + " AND ( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NULL)";
}
else
{
if (textForSearch == "%")
{
whereClause = whereClause + " AND (( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NOT NULL) OR ";
whereClause = whereClause + "( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) IS NULL))";
}
else
{
if ((Validator.IsValidDate(textForSearch)) && (textForSearch.Length > 7))
{
whereClause = whereClause + " AND ( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) = '" + textForSearch + "')";
}
else
{
whereClause = whereClause + " AND ( CAST(CONVERT(varchar," + tableName + "." + conditionValue + ", 101) AS smalldatetime) LIKE '%" + textForSearch + "%')";
}
}
}
}
return whereClause;
}
// ////////////////////////////////////////////////////////////////////////
// PUBLIC METHODS
//
/// <summary>
/// LoadByFmTypeConditionId
/// </summary>
/// <param name="fmType">fmType</param>
/// <param name="companyId">companyId</param>
/// <param name="conditionId">conditionId</param>
public void LoadByFmTypeConditionId(string fmType, int companyId, int conditionId)
{
FmTypeViewConditionGateway fmTypeViewConditionGateway = new FmTypeViewConditionGateway(Data);
fmTypeViewConditionGateway.LoadByFmTypeConditionId(fmType, companyId, conditionId);
}
