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