public static DataTable TicketsCount(int DId, Fltr filter, bool OnHoldStatus, bool PartsTracking, string SortExpession, string GlobalFilterSQL, bool IsSLA) { DateTime start = filter.StartDate; DateTime end = filter.EndDate; string _sqlPreSelect = string.Empty; string _sqlSelect = string.Empty; string _sqlWhere = "WHERE T.company_id=" + DId.ToString() + " AND ((T.CreateTime BETWEEN '" + Functions.FormatSQLDateTime(start) + "' AND '" + Functions.FormatSQLDateTime(end) + "') OR (T.ClosedTime BETWEEN '" + Functions.FormatSQLDateTime(start) + "' AND '" + Functions.FormatSQLDateTime(end) + "' AND T.Status='Closed')) "; string _sqlGroup = string.Empty; string _sqlOrder = string.Empty; if (!OnHoldStatus) _sqlWhere += "AND T.Status<>'On Hold' "; if (!PartsTracking) _sqlWhere += "AND T.Status<>'Parts On Order' "; if (SortExpession.Length > 0 && SortExpession.IndexOf("YAxis") >= 0 && filter.YAxis == Grouping.Month) { _sqlOrder = "ORDER BY Month " + SortExpession.Replace("YAxis", string.Empty); } else if (SortExpession.Length > 0) _sqlOrder = "ORDER BY " + SortExpession; string _range = "'" + Functions.FormatSQLDateTime(start) + "' AND '" + Functions.FormatSQLDateTime(end) + "'"; string _sqlSelectFields = "SUM(CASE WHEN T.CreateTime BETWEEN " + _range + " THEN 1 ELSE 0 END) AS TotalCount, " + "SUM(CASE WHEN T.status = 'Open' AND T.CreateTime BETWEEN " + _range + " THEN 1 ELSE 0 END) AS OpenCount, " + "SUM(CASE WHEN T.status = 'Closed' AND T.ClosedTime BETWEEN " + _range + " THEN 1 ELSE 0 END) AS ClosedCount, " + "SUM(CASE WHEN T.status = 'On Hold' AND T.CreateTime BETWEEN " + _range + " THEN 1 ELSE 0 END) AS HoldCount, " + "SUM(CASE WHEN T.status = 'Parts On Order' AND T.CreateTime BETWEEN " + _range + " THEN 1 ELSE 0 END) AS PartsCount, "; if (IsSLA) { _sqlPreSelect += "DECLARE @WorkDays char(7); "; _sqlPreSelect += "DECLARE @StartBusinnessTime int; "; _sqlPreSelect += "DECLARE @EndBusinnessTime int; "; _sqlPreSelect += "SELECT @WorkDays = I.WorkingDays, @StartBusinnessTime = dbo.fxGetConfigValueStr(" + DId.ToString() + ", 'tinyBusHourStart')*60 + dbo.fxGetConfigValueStr(" + DId.ToString() + ", 'tinyBusMinStart'), @EndBusinnessTime = dbo.fxGetConfigValueStr(" + DId.ToString() + ", 'tinyBusHourStop')*60 + dbo.fxGetConfigValueStr(" + DId.ToString() + ", 'tinyBusMinStop') FROM tbl_company C INNER JOIN Mc_Instance I ON I.InstanceId = C.company_guid WHERE C.company_id = " + DId.ToString() + "; "; _sqlPreSelect += "IF @WorkDays IS NULL SET @WorkDays = '1111100'; "; _sqlPreSelect += "IF @StartBusinnessTime IS NULL SET @StartBusinnessTime = 0; "; _sqlPreSelect += "IF @EndBusinnessTime IS NULL SET @EndBusinnessTime = 1439; "; _sqlSelectFields += "AVG(cast(CASE T.status WHEN 'Open' THEN dbo.fxGetOperationalMinutes(" + DId.ToString() + ",T.CreateTime, GETUTCDATE(), @WorkDays, @StartBusinnessTime, @EndBusinnessTime) ELSE NULL END as bigint)) AS AvgOpen, " + "AVG(cast(CASE WHEN T.status='Closed' AND T.ClosedTime BETWEEN '" + Functions.FormatSQLDateTime(start) + "' AND '" + Functions.FormatSQLDateTime(end) + "' THEN dbo.fxGetOperationalMinutes(" + DId.ToString() + ",T.createtime,T.closedtime, @WorkDays, @StartBusinnessTime, @EndBusinnessTime) ELSE null END as bigint)) AS AvgClosed, " + "AVG(cast(CASE T.status WHEN 'On Hold' THEN dbo.fxGetOperationalMinutes(" + DId.ToString() + ",T.CreateTime, GETUTCDATE(), @WorkDays, @StartBusinnessTime, @EndBusinnessTime) ELSE null END as bigint)) AS AvgHold, " + "AVG(cast(CASE T.status WHEN 'Parts On Order' THEN dbo.fxGetOperationalMinutes(" + DId.ToString() + ",T.CreateTime, GETUTCDATE(), @WorkDays, @StartBusinnessTime, @EndBusinnessTime) ELSE null END as bigint)) AS AvgParts, "; } else { _sqlSelectFields += "AVG(cast(CASE T.status WHEN 'Open' THEN DATEDIFF(minute, T.createtime, GETUTCDATE()) ELSE null END as bigint)) AS AvgOpen, " + "AVG(cast(CASE WHEN T.status='Closed' AND T.ClosedTime BETWEEN '" + Functions.FormatSQLDateTime(start) + "' AND '" + Functions.FormatSQLDateTime(end) + "' THEN DATEDIFF(minute, T.createtime, T.closedtime) ELSE null END as bigint)) AS AvgClosed, " + "AVG(cast(CASE T.status WHEN 'On Hold' THEN DATEDIFF(minute, T.createtime, GETUTCDATE()) ELSE null END as bigint)) AS AvgHold, " + "AVG(cast(CASE T.status WHEN 'Parts On Order' THEN DATEDIFF(minute, T.createtime, GETUTCDATE()) ELSE null END as bigint)) AS AvgParts, "; } _sqlSelectFields += "0 AS Level "; if (_sqlOrder == string.Empty) _sqlOrder = "ORDER BY YAxis ASC"; switch (filter.YAxis) { case Grouping.Account: _sqlSelect = "SELECT ISNULL(A.Id, CASE WHEN ISNULL(T.btNoAccount, 0)=0 THEN -1 ELSE -2 END) AS ID, MAX(A.vchName) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T LEFT OUTER JOIN Accounts A ON A.DId=" + DId.ToString() + " AND A.Id=T.intAcctId "; _sqlGroup = "GROUP BY ISNULL(A.Id, CASE WHEN ISNULL(T.btNoAccount, 0)=0 THEN -1 ELSE -2 END) "; break; case Grouping.AccountLocation: _sqlSelect = "SELECT ISNULL(T.intAcctId,CASE WHEN ISNULL(T.btNoAccount, 0)=0 THEN -1 ELSE -2 END) AS ID, ISNULL(T.AccountLocationId,-1) AS SubID, MAX(ISNULL((CASE WHEN ISNULL(T.btNoAccount,0) = 1 THEN '(No Account)' ELSE dbo.fxGetAccountName(" + DId.ToString() + ", T.intAcctId) END),'') + ISNULL(' / ' + dbo.fxGetUserLocationName(" + DId.ToString() + ", T.AccountLocationId),'')) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T "; _sqlGroup = "GROUP BY ISNULL(T.intAcctId, CASE WHEN ISNULL(T.btNoAccount, 0)=0 THEN -1 ELSE -2 END), T.AccountLocationId "; break; case Grouping.Class: _sqlSelect = "SELECT ISNULL(C.GroupId,0) AS ID, MAX(C.Name) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T LEFT OUTER JOIN dbo.fxMapClassesByLevel(" + DId.ToString() + "," + filter.ClassLevel.ToString() + ") C ON T.class_id=C.Id "; _sqlGroup = "GROUP BY C.GroupId "; break; case Grouping.CreationCategory: _sqlSelect = "SELECT ISNULL(CC.Id,-1) AS ID, MAX(CC.vchName) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T LEFT OUTER JOIN CreationCats CC ON CC.DId=" + DId.ToString() + " AND CC.Id=T.CreationCatsId "; _sqlGroup = "GROUP BY CC.Id "; break; case Grouping.SubmissionCategory: _sqlSelect = "SELECT ISNULL(SC.Id,-1) AS ID, MAX(SC.vchName) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T LEFT OUTER JOIN SubmissionCategories SC ON T.intSubmissionCatId=SC.Id "; _sqlGroup = "GROUP BY SC.Id "; break; case Grouping.ResolutionCategory: _sqlSelect = "SELECT ISNULL(RC.Id,-1) AS ID, MAX(CASE WHEN RC.btResolved=1 THEN 'Resolved-' ELSE 'Unresolved-' END+RC.vchName) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T LEFT OUTER JOIN ResolutionCats RC ON RC.DId=" + DId.ToString() + " AND RC.Id=T.ResolutionCatsId "; _sqlGroup = "GROUP BY RC.Id "; break; case Grouping.Location: _sqlSelect = "SELECT ISNULL(LC.GroupId,-" + filter.LocationTypeID.ToString() + ") AS ID, MAX(LC.Name) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T LEFT OUTER JOIN fxMapLocationsByType(" + DId.ToString() + ",NULL, NULL," + filter.LocationTypeID.ToString() + ",NULL) LC ON T.LocationId=LC.Id "; _sqlGroup = "GROUP BY LC.GroupId "; break; case Grouping.Month: _sqlSelect = "SELECT CAST(CAST(YEAR(T.CreateTime) AS nvarchar(4))+'/'+CAST(MONTH(T.CreateTime) AS nvarchar(2))+'/1' AS datetime) AS Month, MAX(CAST(CAST(YEAR(T.CreateTime) AS nvarchar(4))+CAST(MONTH(T.CreateTime) AS nvarchar(2)) AS int)) AS ID, MAX(DATENAME(month,T.CreateTime)+' '+CAST(YEAR(T.CreateTime) AS nvarchar(4))) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T "; _sqlGroup = "GROUP BY CAST(CAST(YEAR(T.CreateTime) AS nvarchar(4))+'/'+CAST(MONTH(T.CreateTime) AS nvarchar(2))+'/1' AS datetime) "; break; case Grouping.Priority: _sqlSelect = "SELECT ISNULL(P.Id, -1) AS ID, MAX(CAST(P.tintPriority as nvarchar(5))+'-'+P.Name) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T LEFT OUTER JOIN Priorities P ON P.DId=" + DId.ToString() + " AND P.Id=T.PriorityId "; _sqlGroup = "GROUP BY P.Id "; break; //tkt #3949: Level Filter added to Ticket Count Report case Grouping.TicketLevel: _sqlSelect = "SELECT ISNULL(CAST(L.tintLevel as int), -1) AS ID, MAX(CAST(L.tintLevel as nvarchar(5))+ CASE WHEN L.LevelName IS NOT NULL THEN '-'+L.LevelName ELSE '' END ) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T LEFT OUTER JOIN TktLevels L ON L.DId=" + DId.ToString() + " AND L.tintLevel=T.tintLevel "; _sqlGroup = "GROUP BY L.tintLevel "; break; //tkt #3632: Add Support Groups to Ticket Count Report criteria case Grouping.SupportGroup: _sqlSelect = "SELECT ISNULL(CAST(SG.id as int), -1) AS ID, MAX(SG.vchName) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T LEFT OUTER JOIN Accounts AC ON AC.DId=" + DId.ToString() + " AND AC.id=T.intAcctId INNER JOIN tbl_LoginCompanyJunc LJT ON LJT.company_id=" + DId.ToString() + " AND LJT.id=T.Technician_id LEFT OUTER JOIN SupportGroups SG ON SG.DId=" + DId.ToString() + " AND SG.Id=LJT.SupGroupId "; _sqlGroup = "Group By SG.id "; break; case Grouping.Technician: _sqlSelect = "SELECT ISNULL(LJT.id,-1) AS ID, MAX(dbo.fxGetUserName(L.FirstName, L.LastName, L.Email)) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T " + "INNER JOIN tbl_LoginCompanyJunc LJT ON LJT.company_id=" + DId.ToString() + " AND LJT.id=T.Technician_id " + "INNER JOIN tbl_Logins L ON LJT.login_id = L.id "; _sqlGroup = "GROUP BY LJT.id "; break; } _sqlSelect += "INNER JOIN tbl_LoginCompanyJunc LJU ON LJU.company_id=" + DId.ToString() + " AND LJU.id=T.user_id "; if (filter.AccountID > 0) _sqlWhere += "AND T.intAcctId=" + filter.AccountID.ToString() + " "; else if (filter.AccountID < 0) _sqlWhere += "AND T.intAcctId IS NULL AND ISNULL(t.btNoAccount, 0) = " + (filter.AccountID == -2 ? "1" : "0") + " "; if (filter.AccountLocationId > 0) _sqlWhere += "AND T.AccountLocationId=" + filter.AccountLocationId.ToString() + " "; else if (filter.AccountLocationId < 0) _sqlWhere += "AND T.AccountLocationId IS NULL "; if (filter.AccountParentLocationId > 0) _sqlWhere += "AND T.AccountLocationId IN (SELECT Id FROM dbo.fxGetAllChildLocations(" + DId.ToString() + "," + filter.AccountParentLocationId.ToString() + ")) "; else if (filter.AccountParentLocationId < 0) _sqlWhere += "AND T.AccountLocationId IS NULL "; if (filter.ClassID > 0) _sqlWhere += "AND T.class_id IN (SELECT Id FROM dbo.fxGetAllChildClasses(" + DId.ToString() + "," + filter.ClassID.ToString() + ",NULL)) "; else if (filter.ClassID < 0) _sqlWhere += "AND T.class_id=" + Convert.ToString(-filter.ClassID).ToString(); else if (filter.ClassID == 0 && filter.ClassIsNull) _sqlWhere += "AND T.class_id IS NULL "; if (filter.CreationCategoryID > 0) _sqlWhere += "AND T.CreationCatsId=" + filter.CreationCategoryID.ToString() + " "; else if (filter.CreationCategoryID < 0) _sqlWhere += "AND T.CreationCatsId IS NULL "; if (filter.SubmissionCategoryID > 0) _sqlWhere += "AND T.intSubmissionCatId=" + filter.SubmissionCategoryID.ToString() + " "; else if (filter.SubmissionCategoryID < 0) _sqlWhere += "AND T.intSubmissionCatId IS NULL "; if (filter.ResolutionCategoryID > 0) _sqlWhere += "AND T.ResolutionCatsId=" + filter.ResolutionCategoryID.ToString() + " "; else if (filter.ResolutionCategoryID == -1) _sqlWhere += "AND T.ResolutionCatsId IS NULL "; else if (filter.ResolutionCategoryID == -2) _sqlWhere += "AND T.btResolved=0 "; else if (filter.ResolutionCategoryID == -3) _sqlWhere += "AND T.btResolved=1 "; if (filter.LocationID > 0) _sqlWhere += "AND T.LocationId IN (SELECT Id FROM dbo.fxGetAllChildLocations(" + DId.ToString() + "," + filter.LocationID.ToString() + ")) "; else if (filter.LocationID < 0) _sqlWhere += "AND (T.LocationId IS NULL OR T.LocationId IN (SELECT Id FROM fxMapLocationsByType(" + DId.ToString() + ",NULL,NULL," + Convert.ToString(-filter.LocationID) + ",NULL) WHERE GroupId IS NULL)) "; if (filter.MonthID > 0) _sqlWhere += "AND YEAR(T.CreateTime)=" + filter.MonthID.ToString().Substring(0, 4) + " AND MONTH(T.CreateTime)=" + filter.MonthID.ToString().Substring(4) + " "; if (filter.PriorityID != 0) _sqlWhere += "AND T.PriorityId=" + filter.PriorityID.ToString() + " "; else if (filter.PriorityID < 0) _sqlWhere += "AND T.PriorityId IS NULL "; if (filter.AssetFilter.Length > 0) { string _assets = string.Empty; string[] _arrSN = filter.AssetFilter.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); foreach (string _sn in _arrSN) { DataTable _dt = Data.Assets.SelectAssetByUniqueField(DId, _sn); foreach (DataRow _row in _dt.Rows) _assets = _assets + _row["Id"].ToString() + ","; }; if (_assets.Length > 0) _assets = _assets.Remove(_assets.Length - 1, 1); string _asset_tickets = string.Empty; if (_assets.Length > 0) { DataTable _asset_tickets_table = Data.Logins.SelectByQueryExt("Select Distinct TicketId from TicketAssets where DId=" + DId.ToString() + " AND AssetId in (" + _assets + ")"); if (_asset_tickets_table != null) { for (int _index = 0; _index < _asset_tickets_table.Rows.Count; _index++) { _asset_tickets = _asset_tickets + _asset_tickets_table.Rows[_index]["TicketId"].ToString() + ","; }; if (_asset_tickets.Length > 0) _asset_tickets = _asset_tickets.Remove(_asset_tickets.Length - 1, 1); if (_asset_tickets.Length > 0) { _sqlWhere = _sqlWhere + " AND T.Id in (" + _asset_tickets + ") "; } else { _sqlWhere = _sqlWhere + " AND T.Id=0 "; }; }; } else { _sqlWhere = _sqlWhere + " AND T.Id=0 "; }; }; //tkt #3949: Level Filter added to Ticket Count Report if (filter.TicketLevelID > 0) _sqlWhere += "AND T.tintLevel=" + filter.TicketLevelID.ToString() + " "; else if (filter.TicketLevelID < 0) _sqlWhere += "AND T.tintLevel IS NULL "; //tkt #3632: Add Support Groups to Ticket Count Report criteria if (filter.SupportGroupID > 0) _sqlWhere += "AND Exists(select INNER_SG.id from tbl_ticket INNER_T INNER JOIN tbl_LoginCompanyJunc INNER_LCJ ON INNER_T.Technician_id = INNER_LCJ.id LEFT OUTER JOIN SupportGroups INNER_SG ON INNER_LCJ.SupGroupId=INNER_SG.Id where INNER_T.company_id=T.company_id and INNER_T.id=T.id and INNER_SG.id=" + filter.SupportGroupID.ToString() + ") "; else if (filter.SupportGroupID < 0) _sqlWhere += "AND Exists(select INNER_SG.id from tbl_ticket INNER_T INNER JOIN tbl_LoginCompanyJunc INNER_LCJ ON INNER_T.Technician_id = INNER_LCJ.id LEFT OUTER JOIN SupportGroups INNER_SG ON INNER_LCJ.SupGroupId=INNER_SG.Id where INNER_T.company_id=T.company_id and INNER_T.id=T.id and INNER_SG.id IS NULL) "; if (filter.TechnicianType != TechnicianType.All && filter.YAxis != Grouping.Technician && filter.YAxis != Grouping.SupportGroup) { _sqlSelect += "INNER JOIN tbl_LoginCompanyJunc LJT ON LJT.company_id=" + DId.ToString() + " AND LJT.id=T.Technician_id "; } switch (filter.TechnicianType) { case TechnicianType.Filtered: _sqlWhere += "AND (LJT.btGlobalFilterEnabled=1 OR LJT.btLimitToAssignedTkts=1 OR LJT.btDisabledReports=1) "; break; case TechnicianType.Global: _sqlWhere += "AND (LJT.btGlobalFilterEnabled=0 AND LJT.btLimitToAssignedTkts=0 AND LJT.btDisabledReports=0 AND LJT.btCfgCCRep=0) "; break; case TechnicianType.CallCenterRep: _sqlWhere += "AND LJT.btCfgCCRep=1 "; break; //btCfgCCRep } if (filter.HandledByCallCenter != HandledByCallCenter.All) _sqlWhere += string.Format("AND T.btHandledByCC={0} ", ((int)filter.HandledByCallCenter)).ToString(); if (filter.TechnicianID > 0) _sqlWhere += "AND T.Technician_id=" + filter.TechnicianID.ToString() + " "; else if (filter.TechnicianID < 0) _sqlWhere += "AND T.Technician_id IS NULL "; if (filter.SubmittedByID > 0) _sqlWhere += "AND T.Created_id=" + filter.SubmittedByID.ToString() + " "; else if (filter.SubmittedByID < 0) _sqlWhere += "AND T.Created_id IS NULL "; if (filter.ClosedByID > 0) _sqlWhere += "AND T.Closed_id=" + filter.ClosedByID.ToString() + " "; else if (filter.ClosedByID < 0) _sqlWhere += "AND T.Closed_id IS NULL "; if (filter.AgeDays != -1) { string _date_diff = "(CASE T.status WHEN 'Closed' then DATEDIFF(day, T.CreateTime, T.ClosedTime) ELSE DATEDIFF(day, T.CreateTime, GETUTCDATE()) END)"; switch (filter.AgeRange) { case EqualRange.Less: _sqlWhere += " AND " + _date_diff + "<" + filter.AgeDays.ToString(); break; case EqualRange.Equal: _sqlWhere += " AND " + _date_diff + "=" + filter.AgeDays.ToString(); break; case EqualRange.Greater: _sqlWhere += " AND " + _date_diff + ">" + filter.AgeDays.ToString(); break; }; }; _sqlWhere += GlobalFilterSQL; if (filter.SubYAxis == Grouping.None || (filter.SubYAxis == filter.YAxis && filter.ClassLevel == filter.SubClassLevel && filter.LocationTypeID == filter.SubLocationTypeID)) { DataTable ResultDT = SelectByQuery(_sqlPreSelect + _sqlSelect + _sqlWhere + _sqlGroup + _sqlOrder); if (isRecursive == false) { int total_created = 0; int total_opened = 0; int total_closed = 0; int total_holded = 0; int total_parts = 0; long total_avg_opened = 0; long total_avg_closed = 0; long total_avg_holded = 0; long total_avg_parts = 0; int _avg_opened_count = 0; int _avg_closed_count = 0; int _avg_holded_count = 0; int _avg_parts_count = 0; string row_name = "Total:"; for (int i = 0; i < ResultDT.Rows.Count; i++) { DataRow current_row = ResultDT.Rows[i]; total_created += (int)current_row["TotalCount"]; total_opened += (int)current_row["OpenCount"]; total_closed += (int)current_row["ClosedCount"]; total_holded += (int)current_row["HoldCount"]; total_parts += (int)current_row["PartsCount"]; long _avg_opened = GetLongValue(ref current_row, "AvgOpen"); if (_avg_opened > 0) _avg_opened_count++; long _avg_closed = GetLongValue(ref current_row, "AvgClosed"); if (_avg_closed > 0) _avg_closed_count++; long _avg_holded = GetLongValue(ref current_row, "AvgHold"); if (_avg_holded > 0) _avg_holded_count++; long _avg_parts = GetLongValue(ref current_row, "AvgParts"); if (_avg_parts > 0) _avg_parts_count++; total_avg_opened += _avg_opened; total_avg_closed += _avg_closed; total_avg_holded += _avg_holded; total_avg_parts += _avg_parts; }; DataRow new_row = ResultDT.NewRow(); new_row["ID"] = 0; new_row["YAxis"] = row_name; new_row["TotalCount"] = total_created; new_row["OpenCount"] = total_opened; new_row["ClosedCount"] = total_closed; new_row["HoldCount"] = total_holded; new_row["PartsCount"] = total_parts; double _float_number = 0; if (_avg_opened_count > 0) { _float_number = (double)total_avg_opened / (double)_avg_opened_count; total_avg_opened = (int)_float_number; }; if (_avg_closed_count > 0) { _float_number = (double)total_avg_closed / (double)_avg_closed_count; total_avg_closed = (int)_float_number; }; if (_avg_holded_count > 0) { _float_number = (double)total_avg_holded / (double)_avg_holded_count; total_avg_holded = (int)_float_number; }; if (_avg_parts_count > 0) { _float_number = (double)total_avg_parts / (double)_avg_parts_count; total_avg_parts = (int)_float_number; }; if (total_avg_opened > 0) new_row["AvgOpen"] = total_avg_opened; if (total_avg_closed > 0) new_row["AvgClosed"] = total_avg_closed; if (total_avg_holded > 0) new_row["AvgHold"] = total_avg_holded; if (total_avg_parts > 0) new_row["AvgParts"] = total_avg_parts; new_row["Level"] = 0; ResultDT.Rows.InsertAt(new_row, ResultDT.Rows.Count + 1); }; return ResultDT; }; int full_total_created = 0; int full_total_opened = 0; int full_total_closed = 0; int full_total_holded = 0; int full_total_parts = 0; long full_total_avg_opened = 0; long full_total_avg_closed = 0; long full_total_avg_holded = 0; long full_total_avg_parts = 0; int _full_avg_opened_count = 0; int _full_avg_closed_count = 0; int _full_avg_holded_count = 0; int _full_avg_parts_count = 0; DataTable _DT = SelectByQuery(_sqlPreSelect + _sqlSelect + _sqlWhere + _sqlGroup + _sqlOrder); Fltr _f = (Fltr)filter.Clone(); _f.SubYAxis = Grouping.None; for (int i = 0; i < _DT.Rows.Count; i++) { DataRow _row = _DT.Rows[i]; DataTable _subDT = null; _f.YAxis = filter.SubYAxis; _f.LocationTypeID = _f.SubLocationTypeID; _f.ClassLevel = _f.SubClassLevel; switch (filter.YAxis) { case Grouping.Account: if (filter.SubYAxis == Grouping.Location) _f.YAxis = Grouping.AccountLocation; _f.AccountID = (int)_row["ID"]; break; case Grouping.AccountLocation: _f.AccountID = (int)_row["ID"]; _f.AccountLocationId = (int)_row["SubId"]; break; case Grouping.Class: _f.ClassID = (int)_row["ID"]; if (_f.ClassID == 0) _f.ClassIsNull = true; break; case Grouping.CreationCategory: _f.CreationCategoryID = (int)_row["ID"]; break; case Grouping.SubmissionCategory: _f.SubmissionCategoryID = (int)_row["ID"]; break; case Grouping.ResolutionCategory: _f.ResolutionCategoryID = (int)_row["ID"]; break; case Grouping.Location: _f.LocationID = (int)_row["ID"]; break; case Grouping.Month: _f.MonthID = (int)_row["ID"]; break; case Grouping.Priority: _f.PriorityID = (int)_row["ID"]; break; //tkt #3949: Level Filter added to Ticket Count Report case Grouping.TicketLevel: _f.TicketLevelID = (int)_row["ID"]; break; //tkt #3632: Add Support Groups to Ticket Count Report criteria case Grouping.SupportGroup: _f.SupportGroupID = (int)_row["ID"]; break; case Grouping.Technician: _f.TechnicianID = (int)_row["ID"]; break; } isRecursive = true; _subDT = TicketsCount(DId, _f, OnHoldStatus, PartsTracking, SortExpession, GlobalFilterSQL, IsSLA); isRecursive = false; if (_subDT.Columns.Contains("SubId") && !_DT.Columns.Contains("SubId")) _DT.Columns.Add("SubId", typeof(int)); foreach (DataRow _srow in _subDT.Rows) { i++; DataRow _newrow = _DT.NewRow(); _newrow["ID"] = _srow["ID"]; if (_subDT.Columns.Contains("SubId")) _newrow["SubId"] = _srow["SubId"]; _newrow["YAxis"] = _srow["YAxis"]; _newrow["TotalCount"] = _srow["TotalCount"]; _newrow["OpenCount"] = _srow["OpenCount"]; _newrow["ClosedCount"] = _srow["ClosedCount"]; _newrow["HoldCount"] = _srow["HoldCount"]; _newrow["PartsCount"] = _srow["PartsCount"]; _newrow["AvgOpen"] = _srow["AvgOpen"]; _newrow["AvgClosed"] = _srow["AvgClosed"]; _newrow["AvgHold"] = _srow["AvgHold"]; _newrow["AvgParts"] = _srow["AvgParts"]; _newrow["Level"] = (int)_row["ID"] == 0 ? -1 : _row["ID"]; full_total_created += (int)_srow["TotalCount"]; full_total_opened += (int)_srow["OpenCount"]; full_total_closed += (int)_srow["ClosedCount"]; full_total_holded += (int)_srow["HoldCount"]; full_total_parts += (int)_srow["PartsCount"]; long _full_avg_opened = GetLongValue(_srow, "AvgOpen"); if (_full_avg_opened > 0) _full_avg_opened_count++; long _full_avg_closed = GetLongValue(_srow, "AvgClosed"); if (_full_avg_closed > 0) _full_avg_closed_count++; long _full_avg_holded = GetLongValue(_srow, "AvgHold"); if (_full_avg_holded > 0) _full_avg_holded_count++; long _full_avg_parts = GetLongValue(_srow, "AvgParts"); if (_full_avg_parts > 0) _full_avg_parts_count++; full_total_avg_opened += _full_avg_opened; full_total_avg_closed += _full_avg_closed; full_total_avg_holded += _full_avg_holded; full_total_avg_parts += _full_avg_parts; _DT.Rows.InsertAt(_newrow, i); } } string full_row_name = "Total:"; DataRow full_new_row = _DT.NewRow(); full_new_row["ID"] = 0; full_new_row["YAxis"] = full_row_name; full_new_row["TotalCount"] = full_total_created; full_new_row["OpenCount"] = full_total_opened; full_new_row["ClosedCount"] = full_total_closed; full_new_row["HoldCount"] = full_total_holded; full_new_row["PartsCount"] = full_total_parts; double _full_float_number = 0; if (_full_avg_opened_count > 0) { _full_float_number = (double)full_total_avg_opened / (double)_full_avg_opened_count; full_total_avg_opened = (int)_full_float_number; }; if (_full_avg_closed_count > 0) { _full_float_number = (double)full_total_avg_closed / (double)_full_avg_closed_count; full_total_avg_closed = (int)_full_float_number; }; if (_full_avg_holded_count > 0) { _full_float_number = (double)full_total_avg_holded / (double)_full_avg_holded_count; full_total_avg_holded = (int)_full_float_number; }; if (_full_avg_parts_count > 0) { _full_float_number = (double)full_total_avg_parts / (double)_full_avg_parts_count; full_total_avg_parts = (int)_full_float_number; }; if (full_total_avg_opened > 0) full_new_row["AvgOpen"] = full_total_avg_opened; if (full_total_avg_closed > 0) full_new_row["AvgClosed"] = full_total_avg_closed; if (full_total_avg_holded > 0) full_new_row["AvgHold"] = full_total_avg_holded; if (full_total_avg_parts > 0) full_new_row["AvgParts"] = full_total_avg_parts; full_new_row["Level"] = 0; _DT.Rows.InsertAt(full_new_row, _DT.Rows.Count + 1); return _DT; }
public static DataTable TicketsCount(int DId, Fltr filter, bool OnHoldStatus, bool PartsTracking, string SortExpession, string GlobalFilterSQL, bool IsSLA) { DateTime start = filter.StartDate; DateTime end = filter.EndDate; string _sqlPreSelect = string.Empty; string _sqlSelect = string.Empty; string _sqlWhere = "WHERE T.company_id=" + DId.ToString() + " AND ((T.CreateTime BETWEEN '" + Functions.FormatSQLDateTime(start) + "' AND '" + Functions.FormatSQLDateTime(end) + "') OR (T.ClosedTime BETWEEN '" + Functions.FormatSQLDateTime(start) + "' AND '" + Functions.FormatSQLDateTime(end) + "' AND T.Status='Closed')) "; string _sqlGroup = string.Empty; string _sqlOrder = string.Empty; if (!OnHoldStatus) { _sqlWhere += "AND T.Status<>'On Hold' "; } if (!PartsTracking) { _sqlWhere += "AND T.Status<>'Parts On Order' "; } if (SortExpession.Length > 0 && SortExpession.IndexOf("YAxis") >= 0 && filter.YAxis == Grouping.Month) { _sqlOrder = "ORDER BY Month " + SortExpession.Replace("YAxis", string.Empty); } else if (SortExpession.Length > 0) { _sqlOrder = "ORDER BY " + SortExpession; } string _range = "'" + Functions.FormatSQLDateTime(start) + "' AND '" + Functions.FormatSQLDateTime(end) + "'"; string _sqlSelectFields = "SUM(CASE WHEN T.CreateTime BETWEEN " + _range + " THEN 1 ELSE 0 END) AS TotalCount, " + "SUM(CASE WHEN T.status = 'Open' AND T.CreateTime BETWEEN " + _range + " THEN 1 ELSE 0 END) AS OpenCount, " + "SUM(CASE WHEN T.status = 'Closed' AND T.ClosedTime BETWEEN " + _range + " THEN 1 ELSE 0 END) AS ClosedCount, " + "SUM(CASE WHEN T.status = 'On Hold' AND T.CreateTime BETWEEN " + _range + " THEN 1 ELSE 0 END) AS HoldCount, " + "SUM(CASE WHEN T.status = 'Parts On Order' AND T.CreateTime BETWEEN " + _range + " THEN 1 ELSE 0 END) AS PartsCount, "; if (IsSLA) { _sqlPreSelect += "DECLARE @WorkDays char(7); "; _sqlPreSelect += "DECLARE @StartBusinnessTime int; "; _sqlPreSelect += "DECLARE @EndBusinnessTime int; "; _sqlPreSelect += "SELECT @WorkDays = I.WorkingDays, @StartBusinnessTime = dbo.fxGetConfigValueStr(" + DId.ToString() + ", 'tinyBusHourStart')*60 + dbo.fxGetConfigValueStr(" + DId.ToString() + ", 'tinyBusMinStart'), @EndBusinnessTime = dbo.fxGetConfigValueStr(" + DId.ToString() + ", 'tinyBusHourStop')*60 + dbo.fxGetConfigValueStr(" + DId.ToString() + ", 'tinyBusMinStop') FROM tbl_company C INNER JOIN Mc_Instance I ON I.InstanceId = C.company_guid WHERE C.company_id = " + DId.ToString() + "; "; _sqlPreSelect += "IF @WorkDays IS NULL SET @WorkDays = '1111100'; "; _sqlPreSelect += "IF @StartBusinnessTime IS NULL SET @StartBusinnessTime = 0; "; _sqlPreSelect += "IF @EndBusinnessTime IS NULL SET @EndBusinnessTime = 1439; "; _sqlSelectFields += "AVG(cast(CASE T.status WHEN 'Open' THEN dbo.fxGetOperationalMinutes(" + DId.ToString() + ",T.CreateTime, GETUTCDATE(), @WorkDays, @StartBusinnessTime, @EndBusinnessTime) ELSE NULL END as bigint)) AS AvgOpen, " + "AVG(cast(CASE WHEN T.status='Closed' AND T.ClosedTime BETWEEN '" + Functions.FormatSQLDateTime(start) + "' AND '" + Functions.FormatSQLDateTime(end) + "' THEN dbo.fxGetOperationalMinutes(" + DId.ToString() + ",T.createtime,T.closedtime, @WorkDays, @StartBusinnessTime, @EndBusinnessTime) ELSE null END as bigint)) AS AvgClosed, " + "AVG(cast(CASE T.status WHEN 'On Hold' THEN dbo.fxGetOperationalMinutes(" + DId.ToString() + ",T.CreateTime, GETUTCDATE(), @WorkDays, @StartBusinnessTime, @EndBusinnessTime) ELSE null END as bigint)) AS AvgHold, " + "AVG(cast(CASE T.status WHEN 'Parts On Order' THEN dbo.fxGetOperationalMinutes(" + DId.ToString() + ",T.CreateTime, GETUTCDATE(), @WorkDays, @StartBusinnessTime, @EndBusinnessTime) ELSE null END as bigint)) AS AvgParts, "; } else { _sqlSelectFields += "AVG(cast(CASE T.status WHEN 'Open' THEN DATEDIFF(minute, T.createtime, GETUTCDATE()) ELSE null END as bigint)) AS AvgOpen, " + "AVG(cast(CASE WHEN T.status='Closed' AND T.ClosedTime BETWEEN '" + Functions.FormatSQLDateTime(start) + "' AND '" + Functions.FormatSQLDateTime(end) + "' THEN DATEDIFF(minute, T.createtime, T.closedtime) ELSE null END as bigint)) AS AvgClosed, " + "AVG(cast(CASE T.status WHEN 'On Hold' THEN DATEDIFF(minute, T.createtime, GETUTCDATE()) ELSE null END as bigint)) AS AvgHold, " + "AVG(cast(CASE T.status WHEN 'Parts On Order' THEN DATEDIFF(minute, T.createtime, GETUTCDATE()) ELSE null END as bigint)) AS AvgParts, "; } _sqlSelectFields += "0 AS Level "; if (_sqlOrder == string.Empty) { _sqlOrder = "ORDER BY YAxis ASC"; } switch (filter.YAxis) { case Grouping.Account: _sqlSelect = "SELECT ISNULL(A.Id, CASE WHEN ISNULL(T.btNoAccount, 0)=0 THEN -1 ELSE -2 END) AS ID, MAX(A.vchName) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T LEFT OUTER JOIN Accounts A ON A.DId=" + DId.ToString() + " AND A.Id=T.intAcctId "; _sqlGroup = "GROUP BY ISNULL(A.Id, CASE WHEN ISNULL(T.btNoAccount, 0)=0 THEN -1 ELSE -2 END) "; break; case Grouping.AccountLocation: _sqlSelect = "SELECT ISNULL(T.intAcctId,CASE WHEN ISNULL(T.btNoAccount, 0)=0 THEN -1 ELSE -2 END) AS ID, ISNULL(T.AccountLocationId,-1) AS SubID, MAX(ISNULL((CASE WHEN ISNULL(T.btNoAccount,0) = 1 THEN '(No Account)' ELSE dbo.fxGetAccountName(" + DId.ToString() + ", T.intAcctId) END),'') + ISNULL(' / ' + dbo.fxGetUserLocationName(" + DId.ToString() + ", T.AccountLocationId),'')) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T "; _sqlGroup = "GROUP BY ISNULL(T.intAcctId, CASE WHEN ISNULL(T.btNoAccount, 0)=0 THEN -1 ELSE -2 END), T.AccountLocationId "; break; case Grouping.Class: _sqlSelect = "SELECT ISNULL(C.GroupId,0) AS ID, MAX(C.Name) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T LEFT OUTER JOIN dbo.fxMapClassesByLevel(" + DId.ToString() + "," + filter.ClassLevel.ToString() + ") C ON T.class_id=C.Id "; _sqlGroup = "GROUP BY C.GroupId "; break; case Grouping.CreationCategory: _sqlSelect = "SELECT ISNULL(CC.Id,-1) AS ID, MAX(CC.vchName) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T LEFT OUTER JOIN CreationCats CC ON CC.DId=" + DId.ToString() + " AND CC.Id=T.CreationCatsId "; _sqlGroup = "GROUP BY CC.Id "; break; case Grouping.SubmissionCategory: _sqlSelect = "SELECT ISNULL(SC.Id,-1) AS ID, MAX(SC.vchName) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T LEFT OUTER JOIN SubmissionCategories SC ON T.intSubmissionCatId=SC.Id "; _sqlGroup = "GROUP BY SC.Id "; break; case Grouping.ResolutionCategory: _sqlSelect = "SELECT ISNULL(RC.Id,-1) AS ID, MAX(CASE WHEN RC.btResolved=1 THEN 'Resolved-' ELSE 'Unresolved-' END+RC.vchName) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T LEFT OUTER JOIN ResolutionCats RC ON RC.DId=" + DId.ToString() + " AND RC.Id=T.ResolutionCatsId "; _sqlGroup = "GROUP BY RC.Id "; break; case Grouping.Location: _sqlSelect = "SELECT ISNULL(LC.GroupId,-" + filter.LocationTypeID.ToString() + ") AS ID, MAX(LC.Name) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T LEFT OUTER JOIN fxMapLocationsByType(" + DId.ToString() + ",NULL, NULL," + filter.LocationTypeID.ToString() + ",NULL) LC ON T.LocationId=LC.Id "; _sqlGroup = "GROUP BY LC.GroupId "; break; case Grouping.Month: _sqlSelect = "SELECT CAST(CAST(YEAR(T.CreateTime) AS nvarchar(4))+'/'+CAST(MONTH(T.CreateTime) AS nvarchar(2))+'/1' AS datetime) AS Month, MAX(CAST(CAST(YEAR(T.CreateTime) AS nvarchar(4))+CAST(MONTH(T.CreateTime) AS nvarchar(2)) AS int)) AS ID, MAX(DATENAME(month,T.CreateTime)+' '+CAST(YEAR(T.CreateTime) AS nvarchar(4))) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T "; _sqlGroup = "GROUP BY CAST(CAST(YEAR(T.CreateTime) AS nvarchar(4))+'/'+CAST(MONTH(T.CreateTime) AS nvarchar(2))+'/1' AS datetime) "; break; case Grouping.Priority: _sqlSelect = "SELECT ISNULL(P.Id, -1) AS ID, MAX(CAST(P.tintPriority as nvarchar(5))+'-'+P.Name) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T LEFT OUTER JOIN Priorities P ON P.DId=" + DId.ToString() + " AND P.Id=T.PriorityId "; _sqlGroup = "GROUP BY P.Id "; break; //tkt #3949: Level Filter added to Ticket Count Report case Grouping.TicketLevel: _sqlSelect = "SELECT ISNULL(CAST(L.tintLevel as int), -1) AS ID, MAX(CAST(L.tintLevel as nvarchar(5))+ CASE WHEN L.LevelName IS NOT NULL THEN '-'+L.LevelName ELSE '' END ) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T LEFT OUTER JOIN TktLevels L ON L.DId=" + DId.ToString() + " AND L.tintLevel=T.tintLevel "; _sqlGroup = "GROUP BY L.tintLevel "; break; //tkt #3632: Add Support Groups to Ticket Count Report criteria case Grouping.SupportGroup: _sqlSelect = "SELECT ISNULL(CAST(SG.id as int), -1) AS ID, MAX(SG.vchName) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T LEFT OUTER JOIN Accounts AC ON AC.DId=" + DId.ToString() + " AND AC.id=T.intAcctId INNER JOIN tbl_LoginCompanyJunc LJT ON LJT.company_id=" + DId.ToString() + " AND LJT.id=T.Technician_id LEFT OUTER JOIN SupportGroups SG ON SG.DId=" + DId.ToString() + " AND SG.Id=LJT.SupGroupId "; _sqlGroup = "Group By SG.id "; break; case Grouping.Technician: _sqlSelect = "SELECT ISNULL(LJT.id,-1) AS ID, MAX(dbo.fxGetUserName(L.FirstName, L.LastName, L.Email)) AS YAxis, " + _sqlSelectFields + "FROM tbl_ticket T " + "INNER JOIN tbl_LoginCompanyJunc LJT ON LJT.company_id=" + DId.ToString() + " AND LJT.id=T.Technician_id " + "INNER JOIN tbl_Logins L ON LJT.login_id = L.id "; _sqlGroup = "GROUP BY LJT.id "; break; } _sqlSelect += "INNER JOIN tbl_LoginCompanyJunc LJU ON LJU.company_id=" + DId.ToString() + " AND LJU.id=T.user_id "; if (filter.AccountID > 0) { _sqlWhere += "AND T.intAcctId=" + filter.AccountID.ToString() + " "; } else if (filter.AccountID < 0) { _sqlWhere += "AND T.intAcctId IS NULL AND ISNULL(t.btNoAccount, 0) = " + (filter.AccountID == -2 ? "1" : "0") + " "; } if (filter.AccountLocationId > 0) { _sqlWhere += "AND T.AccountLocationId=" + filter.AccountLocationId.ToString() + " "; } else if (filter.AccountLocationId < 0) { _sqlWhere += "AND T.AccountLocationId IS NULL "; } if (filter.AccountParentLocationId > 0) { _sqlWhere += "AND T.AccountLocationId IN (SELECT Id FROM dbo.fxGetAllChildLocations(" + DId.ToString() + "," + filter.AccountParentLocationId.ToString() + ")) "; } else if (filter.AccountParentLocationId < 0) { _sqlWhere += "AND T.AccountLocationId IS NULL "; } if (filter.ClassID > 0) { _sqlWhere += "AND T.class_id IN (SELECT Id FROM dbo.fxGetAllChildClasses(" + DId.ToString() + "," + filter.ClassID.ToString() + ",NULL)) "; } else if (filter.ClassID < 0) { _sqlWhere += "AND T.class_id=" + Convert.ToString(-filter.ClassID).ToString(); } else if (filter.ClassID == 0 && filter.ClassIsNull) { _sqlWhere += "AND T.class_id IS NULL "; } if (filter.CreationCategoryID > 0) { _sqlWhere += "AND T.CreationCatsId=" + filter.CreationCategoryID.ToString() + " "; } else if (filter.CreationCategoryID < 0) { _sqlWhere += "AND T.CreationCatsId IS NULL "; } if (filter.SubmissionCategoryID > 0) { _sqlWhere += "AND T.intSubmissionCatId=" + filter.SubmissionCategoryID.ToString() + " "; } else if (filter.SubmissionCategoryID < 0) { _sqlWhere += "AND T.intSubmissionCatId IS NULL "; } if (filter.ResolutionCategoryID > 0) { _sqlWhere += "AND T.ResolutionCatsId=" + filter.ResolutionCategoryID.ToString() + " "; } else if (filter.ResolutionCategoryID == -1) { _sqlWhere += "AND T.ResolutionCatsId IS NULL "; } else if (filter.ResolutionCategoryID == -2) { _sqlWhere += "AND T.btResolved=0 "; } else if (filter.ResolutionCategoryID == -3) { _sqlWhere += "AND T.btResolved=1 "; } if (filter.LocationID > 0) { _sqlWhere += "AND T.LocationId IN (SELECT Id FROM dbo.fxGetAllChildLocations(" + DId.ToString() + "," + filter.LocationID.ToString() + ")) "; } else if (filter.LocationID < 0) { _sqlWhere += "AND (T.LocationId IS NULL OR T.LocationId IN (SELECT Id FROM fxMapLocationsByType(" + DId.ToString() + ",NULL,NULL," + Convert.ToString(-filter.LocationID) + ",NULL) WHERE GroupId IS NULL)) "; } if (filter.MonthID > 0) { _sqlWhere += "AND YEAR(T.CreateTime)=" + filter.MonthID.ToString().Substring(0, 4) + " AND MONTH(T.CreateTime)=" + filter.MonthID.ToString().Substring(4) + " "; } if (filter.PriorityID != 0) { _sqlWhere += "AND T.PriorityId=" + filter.PriorityID.ToString() + " "; } else if (filter.PriorityID < 0) { _sqlWhere += "AND T.PriorityId IS NULL "; } if (filter.AssetFilter.Length > 0) { string _assets = string.Empty; string[] _arrSN = filter.AssetFilter.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); foreach (string _sn in _arrSN) { DataTable _dt = Data.Assets.SelectAssetByUniqueField(DId, _sn); foreach (DataRow _row in _dt.Rows) { _assets = _assets + _row["Id"].ToString() + ","; } } ; if (_assets.Length > 0) { _assets = _assets.Remove(_assets.Length - 1, 1); } string _asset_tickets = string.Empty; if (_assets.Length > 0) { DataTable _asset_tickets_table = Data.Logins.SelectByQueryExt("Select Distinct TicketId from TicketAssets where DId=" + DId.ToString() + " AND AssetId in (" + _assets + ")"); if (_asset_tickets_table != null) { for (int _index = 0; _index < _asset_tickets_table.Rows.Count; _index++) { _asset_tickets = _asset_tickets + _asset_tickets_table.Rows[_index]["TicketId"].ToString() + ","; } ; if (_asset_tickets.Length > 0) { _asset_tickets = _asset_tickets.Remove(_asset_tickets.Length - 1, 1); } if (_asset_tickets.Length > 0) { _sqlWhere = _sqlWhere + " AND T.Id in (" + _asset_tickets + ") "; } else { _sqlWhere = _sqlWhere + " AND T.Id=0 "; }; } ; } else { _sqlWhere = _sqlWhere + " AND T.Id=0 "; }; } ; //tkt #3949: Level Filter added to Ticket Count Report if (filter.TicketLevelID > 0) { _sqlWhere += "AND T.tintLevel=" + filter.TicketLevelID.ToString() + " "; } else if (filter.TicketLevelID < 0) { _sqlWhere += "AND T.tintLevel IS NULL "; } //tkt #3632: Add Support Groups to Ticket Count Report criteria if (filter.SupportGroupID > 0) { _sqlWhere += "AND Exists(select INNER_SG.id from tbl_ticket INNER_T INNER JOIN tbl_LoginCompanyJunc INNER_LCJ ON INNER_T.Technician_id = INNER_LCJ.id LEFT OUTER JOIN SupportGroups INNER_SG ON INNER_LCJ.SupGroupId=INNER_SG.Id where INNER_T.company_id=T.company_id and INNER_T.id=T.id and INNER_SG.id=" + filter.SupportGroupID.ToString() + ") "; } else if (filter.SupportGroupID < 0) { _sqlWhere += "AND Exists(select INNER_SG.id from tbl_ticket INNER_T INNER JOIN tbl_LoginCompanyJunc INNER_LCJ ON INNER_T.Technician_id = INNER_LCJ.id LEFT OUTER JOIN SupportGroups INNER_SG ON INNER_LCJ.SupGroupId=INNER_SG.Id where INNER_T.company_id=T.company_id and INNER_T.id=T.id and INNER_SG.id IS NULL) "; } if (filter.TechnicianType != TechnicianType.All && filter.YAxis != Grouping.Technician && filter.YAxis != Grouping.SupportGroup) { _sqlSelect += "INNER JOIN tbl_LoginCompanyJunc LJT ON LJT.company_id=" + DId.ToString() + " AND LJT.id=T.Technician_id "; } switch (filter.TechnicianType) { case TechnicianType.Filtered: _sqlWhere += "AND (LJT.btGlobalFilterEnabled=1 OR LJT.btLimitToAssignedTkts=1 OR LJT.btDisabledReports=1) "; break; case TechnicianType.Global: _sqlWhere += "AND (LJT.btGlobalFilterEnabled=0 AND LJT.btLimitToAssignedTkts=0 AND LJT.btDisabledReports=0 AND LJT.btCfgCCRep=0) "; break; case TechnicianType.CallCenterRep: _sqlWhere += "AND LJT.btCfgCCRep=1 "; break; //btCfgCCRep } if (filter.HandledByCallCenter != HandledByCallCenter.All) { _sqlWhere += string.Format("AND T.btHandledByCC={0} ", ((int)filter.HandledByCallCenter)).ToString(); } if (filter.TechnicianID > 0) { _sqlWhere += "AND T.Technician_id=" + filter.TechnicianID.ToString() + " "; } else if (filter.TechnicianID < 0) { _sqlWhere += "AND T.Technician_id IS NULL "; } if (filter.SubmittedByID > 0) { _sqlWhere += "AND T.Created_id=" + filter.SubmittedByID.ToString() + " "; } else if (filter.SubmittedByID < 0) { _sqlWhere += "AND T.Created_id IS NULL "; } if (filter.ClosedByID > 0) { _sqlWhere += "AND T.Closed_id=" + filter.ClosedByID.ToString() + " "; } else if (filter.ClosedByID < 0) { _sqlWhere += "AND T.Closed_id IS NULL "; } if (filter.AgeDays != -1) { string _date_diff = "(CASE T.status WHEN 'Closed' then DATEDIFF(day, T.CreateTime, T.ClosedTime) ELSE DATEDIFF(day, T.CreateTime, GETUTCDATE()) END)"; switch (filter.AgeRange) { case EqualRange.Less: _sqlWhere += " AND " + _date_diff + "<" + filter.AgeDays.ToString(); break; case EqualRange.Equal: _sqlWhere += " AND " + _date_diff + "=" + filter.AgeDays.ToString(); break; case EqualRange.Greater: _sqlWhere += " AND " + _date_diff + ">" + filter.AgeDays.ToString(); break; } ; } ; _sqlWhere += GlobalFilterSQL; if (filter.SubYAxis == Grouping.None || (filter.SubYAxis == filter.YAxis && filter.ClassLevel == filter.SubClassLevel && filter.LocationTypeID == filter.SubLocationTypeID)) { DataTable ResultDT = SelectByQuery(_sqlPreSelect + _sqlSelect + _sqlWhere + _sqlGroup + _sqlOrder); if (isRecursive == false) { int total_created = 0; int total_opened = 0; int total_closed = 0; int total_holded = 0; int total_parts = 0; long total_avg_opened = 0; long total_avg_closed = 0; long total_avg_holded = 0; long total_avg_parts = 0; int _avg_opened_count = 0; int _avg_closed_count = 0; int _avg_holded_count = 0; int _avg_parts_count = 0; string row_name = "Total:"; for (int i = 0; i < ResultDT.Rows.Count; i++) { DataRow current_row = ResultDT.Rows[i]; total_created += (int)current_row["TotalCount"]; total_opened += (int)current_row["OpenCount"]; total_closed += (int)current_row["ClosedCount"]; total_holded += (int)current_row["HoldCount"]; total_parts += (int)current_row["PartsCount"]; long _avg_opened = GetLongValue(ref current_row, "AvgOpen"); if (_avg_opened > 0) { _avg_opened_count++; } long _avg_closed = GetLongValue(ref current_row, "AvgClosed"); if (_avg_closed > 0) { _avg_closed_count++; } long _avg_holded = GetLongValue(ref current_row, "AvgHold"); if (_avg_holded > 0) { _avg_holded_count++; } long _avg_parts = GetLongValue(ref current_row, "AvgParts"); if (_avg_parts > 0) { _avg_parts_count++; } total_avg_opened += _avg_opened; total_avg_closed += _avg_closed; total_avg_holded += _avg_holded; total_avg_parts += _avg_parts; } ; DataRow new_row = ResultDT.NewRow(); new_row["ID"] = 0; new_row["YAxis"] = row_name; new_row["TotalCount"] = total_created; new_row["OpenCount"] = total_opened; new_row["ClosedCount"] = total_closed; new_row["HoldCount"] = total_holded; new_row["PartsCount"] = total_parts; double _float_number = 0; if (_avg_opened_count > 0) { _float_number = (double)total_avg_opened / (double)_avg_opened_count; total_avg_opened = (int)_float_number; } ; if (_avg_closed_count > 0) { _float_number = (double)total_avg_closed / (double)_avg_closed_count; total_avg_closed = (int)_float_number; } ; if (_avg_holded_count > 0) { _float_number = (double)total_avg_holded / (double)_avg_holded_count; total_avg_holded = (int)_float_number; } ; if (_avg_parts_count > 0) { _float_number = (double)total_avg_parts / (double)_avg_parts_count; total_avg_parts = (int)_float_number; } ; if (total_avg_opened > 0) { new_row["AvgOpen"] = total_avg_opened; } if (total_avg_closed > 0) { new_row["AvgClosed"] = total_avg_closed; } if (total_avg_holded > 0) { new_row["AvgHold"] = total_avg_holded; } if (total_avg_parts > 0) { new_row["AvgParts"] = total_avg_parts; } new_row["Level"] = 0; ResultDT.Rows.InsertAt(new_row, ResultDT.Rows.Count + 1); } ; return(ResultDT); } ; int full_total_created = 0; int full_total_opened = 0; int full_total_closed = 0; int full_total_holded = 0; int full_total_parts = 0; long full_total_avg_opened = 0; long full_total_avg_closed = 0; long full_total_avg_holded = 0; long full_total_avg_parts = 0; int _full_avg_opened_count = 0; int _full_avg_closed_count = 0; int _full_avg_holded_count = 0; int _full_avg_parts_count = 0; DataTable _DT = SelectByQuery(_sqlPreSelect + _sqlSelect + _sqlWhere + _sqlGroup + _sqlOrder); Fltr _f = (Fltr)filter.Clone(); _f.SubYAxis = Grouping.None; for (int i = 0; i < _DT.Rows.Count; i++) { DataRow _row = _DT.Rows[i]; DataTable _subDT = null; _f.YAxis = filter.SubYAxis; _f.LocationTypeID = _f.SubLocationTypeID; _f.ClassLevel = _f.SubClassLevel; switch (filter.YAxis) { case Grouping.Account: if (filter.SubYAxis == Grouping.Location) { _f.YAxis = Grouping.AccountLocation; } _f.AccountID = (int)_row["ID"]; break; case Grouping.AccountLocation: _f.AccountID = (int)_row["ID"]; _f.AccountLocationId = (int)_row["SubId"]; break; case Grouping.Class: _f.ClassID = (int)_row["ID"]; if (_f.ClassID == 0) { _f.ClassIsNull = true; } break; case Grouping.CreationCategory: _f.CreationCategoryID = (int)_row["ID"]; break; case Grouping.SubmissionCategory: _f.SubmissionCategoryID = (int)_row["ID"]; break; case Grouping.ResolutionCategory: _f.ResolutionCategoryID = (int)_row["ID"]; break; case Grouping.Location: _f.LocationID = (int)_row["ID"]; break; case Grouping.Month: _f.MonthID = (int)_row["ID"]; break; case Grouping.Priority: _f.PriorityID = (int)_row["ID"]; break; //tkt #3949: Level Filter added to Ticket Count Report case Grouping.TicketLevel: _f.TicketLevelID = (int)_row["ID"]; break; //tkt #3632: Add Support Groups to Ticket Count Report criteria case Grouping.SupportGroup: _f.SupportGroupID = (int)_row["ID"]; break; case Grouping.Technician: _f.TechnicianID = (int)_row["ID"]; break; } isRecursive = true; _subDT = TicketsCount(DId, _f, OnHoldStatus, PartsTracking, SortExpession, GlobalFilterSQL, IsSLA); isRecursive = false; if (_subDT.Columns.Contains("SubId") && !_DT.Columns.Contains("SubId")) { _DT.Columns.Add("SubId", typeof(int)); } foreach (DataRow _srow in _subDT.Rows) { i++; DataRow _newrow = _DT.NewRow(); _newrow["ID"] = _srow["ID"]; if (_subDT.Columns.Contains("SubId")) { _newrow["SubId"] = _srow["SubId"]; } _newrow["YAxis"] = _srow["YAxis"]; _newrow["TotalCount"] = _srow["TotalCount"]; _newrow["OpenCount"] = _srow["OpenCount"]; _newrow["ClosedCount"] = _srow["ClosedCount"]; _newrow["HoldCount"] = _srow["HoldCount"]; _newrow["PartsCount"] = _srow["PartsCount"]; _newrow["AvgOpen"] = _srow["AvgOpen"]; _newrow["AvgClosed"] = _srow["AvgClosed"]; _newrow["AvgHold"] = _srow["AvgHold"]; _newrow["AvgParts"] = _srow["AvgParts"]; _newrow["Level"] = (int)_row["ID"] == 0 ? -1 : _row["ID"]; full_total_created += (int)_srow["TotalCount"]; full_total_opened += (int)_srow["OpenCount"]; full_total_closed += (int)_srow["ClosedCount"]; full_total_holded += (int)_srow["HoldCount"]; full_total_parts += (int)_srow["PartsCount"]; long _full_avg_opened = GetLongValue(_srow, "AvgOpen"); if (_full_avg_opened > 0) { _full_avg_opened_count++; } long _full_avg_closed = GetLongValue(_srow, "AvgClosed"); if (_full_avg_closed > 0) { _full_avg_closed_count++; } long _full_avg_holded = GetLongValue(_srow, "AvgHold"); if (_full_avg_holded > 0) { _full_avg_holded_count++; } long _full_avg_parts = GetLongValue(_srow, "AvgParts"); if (_full_avg_parts > 0) { _full_avg_parts_count++; } full_total_avg_opened += _full_avg_opened; full_total_avg_closed += _full_avg_closed; full_total_avg_holded += _full_avg_holded; full_total_avg_parts += _full_avg_parts; _DT.Rows.InsertAt(_newrow, i); } } string full_row_name = "Total:"; DataRow full_new_row = _DT.NewRow(); full_new_row["ID"] = 0; full_new_row["YAxis"] = full_row_name; full_new_row["TotalCount"] = full_total_created; full_new_row["OpenCount"] = full_total_opened; full_new_row["ClosedCount"] = full_total_closed; full_new_row["HoldCount"] = full_total_holded; full_new_row["PartsCount"] = full_total_parts; double _full_float_number = 0; if (_full_avg_opened_count > 0) { _full_float_number = (double)full_total_avg_opened / (double)_full_avg_opened_count; full_total_avg_opened = (int)_full_float_number; } ; if (_full_avg_closed_count > 0) { _full_float_number = (double)full_total_avg_closed / (double)_full_avg_closed_count; full_total_avg_closed = (int)_full_float_number; } ; if (_full_avg_holded_count > 0) { _full_float_number = (double)full_total_avg_holded / (double)_full_avg_holded_count; full_total_avg_holded = (int)_full_float_number; } ; if (_full_avg_parts_count > 0) { _full_float_number = (double)full_total_avg_parts / (double)_full_avg_parts_count; full_total_avg_parts = (int)_full_float_number; } ; if (full_total_avg_opened > 0) { full_new_row["AvgOpen"] = full_total_avg_opened; } if (full_total_avg_closed > 0) { full_new_row["AvgClosed"] = full_total_avg_closed; } if (full_total_avg_holded > 0) { full_new_row["AvgHold"] = full_total_avg_holded; } if (full_total_avg_parts > 0) { full_new_row["AvgParts"] = full_total_avg_parts; } full_new_row["Level"] = 0; _DT.Rows.InsertAt(full_new_row, _DT.Rows.Count + 1); return(_DT); }