// ////////////////////////////////////////////////////////////////////////
// PUBLIC METHODS
//
/// <summary>
/// LoadByFmTypeInView
/// </summary>
/// <param name="fmType">fmType</param>
/// <param name="companyId">companyId</param>
/// <param name="inView">inView</param>
public void LoadByFmTypeInView(string fmType, int companyId, bool inView)
{
FmTypeViewSortGateway fmTypeViewSortGateway = new FmTypeViewSortGateway(Data);
fmTypeViewSortGateway.LoadByFmTypeInView(fmType, companyId, inView);
UpdateOrder();
}
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;
}
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;
}
/// <summary>
/// GetSortForSql
/// </summary>
/// <returns>Sort for sql</returns>
public string GetSortForSql()
{
string sort = "";
// process temp table
DataRow[] dataRowOrder = Data.Tables["FmViewSortTemp"].Select("Deleted = 0", "Order_ ASC");
foreach (DataRow row in dataRowOrder)
{
if (!(((FmViewTDS.FmViewSortTempRow)row).Deleted))
{
int order_ = ((FmViewTDS.FmViewSortTempRow)row).Order_;
string fmType = ((FmViewTDS.FmViewSortTempRow)row).FmType;
int companyId = ((FmViewTDS.FmViewSortTempRow)row).COMPANY_ID;
int sortId = ((FmViewTDS.FmViewSortTempRow)row).SortID;
FmTypeViewSort fmTypeViewSort = new FmTypeViewSort();
fmTypeViewSort.LoadByFmTypeSortId(fmType, companyId, sortId);
FmTypeViewSortGateway fmTypeViewSortGateway = new FmTypeViewSortGateway(fmTypeViewSort.Data);
string tableName = fmTypeViewSortGateway.GetTable_(fmType, companyId, sortId);
string columnName = fmTypeViewSortGateway.GetColumn_(fmType, companyId, sortId);
string conditionName = fmTypeViewSortGateway.GetName(fmType, companyId, sortId);
if (fmType == "Services")
{
if (tableName == "LFS_FM_SERVICE") tableName = "LFS";
if (tableName == "LFS_FM_RULE") tableName = "LFR";
if (tableName == "LFS_FM_UNIT") tableName = "LFU";
if (conditionName == "Created By") tableName = "LEOwner";
if (conditionName == "Assigned To") tableName = "LEAssignedTo";
}
if (fmType == "Units")
{
if (tableName == "LFS_FM_UNIT")
{
tableName = "FMU";
}
else
{
if ((tableName == "LFS_FM_COMPANYLEVEL") && (columnName == "CompanyLevel"))
{
columnName = "Name";
tableName = "FMC";
}
}
}
if (fmType == "Services")
{
if (columnName == "Number")
{
sort = sort + String.Format(" CASE WHEN 1 = IsNumeric({0}.{1}) THEN Cast({0}.{1} AS INT) END, ", tableName, columnName);
}
else
{
if (conditionName == "Problem Description")
{
sort = sort + string.Format(" CAST({0}.{1} AS nvarchar), ", tableName, columnName);
}
else
{
if (columnName == "Date")
{
sort = sort + " LFS.StartWorkDateTime DESC, ";
}
else
{
sort = sort + tableName + "." + columnName + ", ";
}
}
}
}
if (fmType == "Units")
{
if (columnName == "Notes" || columnName == "Categories")
{
sort = sort + string.Format(" CAST({0}.{1} AS nvarchar), ", tableName, columnName);
}
else
{
sort = sort + tableName + "." + columnName + ", ";
}
}
}
}
sort = sort.Substring(0, sort.Length - 2);
return sort;
}
/// <summary>
/// GetSortForSummary
/// </summary>
/// <returns>Sort clause</returns>
public string GetSortForSummary()
{
string sort = "";
DataRow[] dataRowOrder = Data.Tables["FmViewSortTemp"].Select("Deleted = 0", "Order_ ASC");
foreach (DataRow row in dataRowOrder)
{
if (!(((FmViewTDS.FmViewSortTempRow)row).Deleted))
{
string fmType = ((FmViewTDS.FmViewSortTempRow)row).FmType;
int companyId = ((FmViewTDS.FmViewSortTempRow)row).COMPANY_ID;
int sortId = ((FmViewTDS.FmViewSortTempRow)row).SortID;
FmTypeViewSort fmTypeViewSort = new FmTypeViewSort();
fmTypeViewSort.LoadByFmTypeSortId(fmType, companyId, sortId);
FmTypeViewSortGateway fmTypeViewSortGateway = new FmTypeViewSortGateway(fmTypeViewSort.Data);
sort = sort + fmTypeViewSortGateway.GetName(fmType, companyId, sortId) + ", ";
}
}
if (sort.Length > 2)
{
sort = sort.Substring(0, sort.Length - 2);
}
return sort;
}
/// <summary>
/// LoadByFmTypeSortId
/// </summary>
/// <param name="fmType">fmType</param>
/// <param name="companyId">companyId</param>
/// <param name="sortId">sortId</param>
public void LoadByFmTypeSortId(string fmType, int companyId, int sortId)
{
FmTypeViewSortGateway fmTypeViewSortGateway = new FmTypeViewSortGateway(Data);
fmTypeViewSortGateway.LoadByFmTypeSortId(fmType, companyId, sortId);
}
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));
if (tableName == "LFS_FM_UNIT")
{
tableName = "FMU";
}
else
{
if ((tableName == "LFS_FM_COMPANYLEVEL") && (columnName == "CompanyLevel"))
{
columnName = "Name";
tableName = "FMC";
}
}
// Get order by clause
string orderBy = "";
if (columnName == "Notes" || columnName == "Categories")
{
orderBy = String.Format(" CAST({0}.{1} AS nvarchar)", tableName, columnName);
}
else
{
orderBy = tableName + "." + columnName;
}
return orderBy;
}
