public static TicketCounts[] GetData()
{
List <TicketCounts> result = new List <TicketCounts>();
TicketTypes ticketTypes = new TicketTypes(UserSession.LoginUser);
ticketTypes.LoadByOrganizationID(UserSession.LoginUser.OrganizationID, UserSession.CurrentUser.ProductType);
Users users = new Users(UserSession.LoginUser);
users.LoadByOrganizationID(UserSession.LoginUser.OrganizationID, true);
foreach (User user in users)
{
string value = user.DisplayName;
int count = 0;
foreach (TicketType ticketType in ticketTypes)
{
count += Tickets.GetUserOpenTicketCount(UserSession.LoginUser, user.UserID, ticketType.TicketTypeID);
}
TicketCounts counts = new TicketCounts();
counts.Name = value;
counts.Count = count;
result.Add(counts);
}
return(result.OrderBy(ticketcount => ticketcount.Name).ToArray());
}
public static TicketCounts[] GetData(DateTime start, DateTime end)
{
List <TicketCounts> result = new List <TicketCounts>();
SqlCommand commandCreated = new SqlCommand(@"SELECT CONVERT(VARCHAR,dt.dtime,1) AS 'Date', ISNULL(TicketsClosed,0) AS 'TicketsClosed', ISNULL(TicketsCreated,0) AS 'TicketsCreated'
FROM dbo.udfDateTimes(@StartDate,@EndDate,1,'day') AS dt
LEFT JOIN (SELECT CAST(FLOOR(CAST(DateClosed AS FLOAT)) AS DATETIME) AS 'Date', COUNT(*) AS 'TicketsClosed' FROM Tickets WHERE organizationid = @organizationid AND DateClosed BETWEEN DATEADD(DAY,-1,@StartDate) AND DATEADD(DAY,1,@EndDate)
GROUP BY CAST(FLOOR(CAST(DateClosed AS FLOAT)) AS DATETIME)) t ON t.Date = CAST(FLOOR(CAST(dt.dtime AS FLOAT)) AS DATETIME)
LEFT JOIN (SELECT CAST(FLOOR(CAST(DateCreated AS FLOAT)) AS DATETIME) AS 'Date', COUNT(*) AS 'TicketsCreated' FROM Tickets WHERE organizationid = @organizationid AND DateCreated BETWEEN DATEADD(DAY,-1,@StartDate) AND DATEADD(DAY,1,@EndDate)
GROUP BY CAST(FLOOR(CAST(DateCreated AS FLOAT)) AS DATETIME)) t2 ON t2.Date = CAST(FLOOR(CAST(dt.dtime AS FLOAT)) AS DATETIME)
ORDER BY dt.dtime");
commandCreated.Parameters.AddWithValue("@organizationid", UserSession.LoginUser.OrganizationID);
commandCreated.Parameters.AddWithValue("@StartDate", start);
commandCreated.Parameters.AddWithValue("@EndDate", end);
DataTable createdClosed = SqlExecutor.ExecuteQuery(UserSession.LoginUser, commandCreated);
foreach (DataRow thisRow in createdClosed.Rows)
{
TicketCounts counts = new TicketCounts();
counts.TicketDate = (string)thisRow["Date"];
counts.ClosedCount = (int)thisRow["TicketsClosed"];
counts.CreatedCount = (int)thisRow["TicketsCreated"];
result.Add(counts);
}
return(result.ToArray());
}
public static TicketCounts[] GetData()
{
List <TicketCounts> result = new List <TicketCounts>();
TicketTypes ticketTypes = new TicketTypes(UserSession.LoginUser);
ticketTypes.LoadByOrganizationID(UserSession.LoginUser.OrganizationID, UserSession.CurrentUser.ProductType);
foreach (TicketType ticketType in ticketTypes)
{
int count = Tickets.GetUserOpenTicketCount(UserSession.LoginUser, UserSession.LoginUser.UserID, ticketType.TicketTypeID);
string value = ticketType.Name;
if (value.Length > 8)
{
value = value.Substring(0, 8);
}
value = value + " (" + count.ToString() + ")";
TicketCounts counts = new TicketCounts();
counts.Name = value;
counts.Count = count;
result.Add(counts);
}
return(result.ToArray());
}
public static TicketCounts[] GetData()
{
List <TicketCounts> result = new List <TicketCounts>();
SqlCommand command = new SqlCommand(@"select p.name as ProductName,
(select count(*) from tickets as t, products as p2, ticketstatuses as ts, tickettypes as tt
where t.productid = p2.productid
and t.organizationid = @OrganizationID
and t.ticketstatusid = ts.ticketstatusid
and ts.isclosed = 0
and t.tickettypeid = tt.tickettypeid
and tt.name = 'issues'
and p.productid = p2.productid) as NumIssues,
(select count(*) from tickets as t, products as p2, ticketstatuses as ts, tickettypes as tt
where t.productid = p2.productid
and t.organizationid = @OrganizationID
and t.ticketstatusid = ts.ticketstatusid
and ts.isclosed = 0
and t.tickettypeid = tt.tickettypeid
and tt.name = 'tasks'
and p.productid = p2.productid) as NumTasks,
(select count(*) from tickets as t, products as p2, ticketstatuses as ts, tickettypes as tt
where t.productid = p2.productid
and t.organizationid = @OrganizationID
and t.ticketstatusid = ts.ticketstatusid
and ts.isclosed = 0
and t.tickettypeid = tt.tickettypeid
and tt.name = 'bugs'
and p.productid = p2.productid) as NumBugs,
(select count(*) from tickets as t, products as p2, ticketstatuses as ts, tickettypes as tt
where t.productid = p2.productid
and t.organizationid = @OrganizationID
and t.ticketstatusid = ts.ticketstatusid
and ts.isclosed = 0
and t.tickettypeid = tt.tickettypeid
and tt.name = 'features'
and p.productid = p2.productid) as NumFeatures
From Products as p
where p.organizationid = @OrganizationID
order by p.name");
command.Parameters.AddWithValue("@OrganizationID", UserSession.LoginUser.OrganizationID);
DataTable productTickets = SqlExecutor.ExecuteQuery(UserSession.LoginUser, command);
foreach (DataRow thisRow in productTickets.Rows)
{
TicketCounts counts = new TicketCounts();
counts.Product = (string)thisRow["ProductName"];
counts.Issues = (int)thisRow["NumIssues"];
counts.Tasks = (int)thisRow["NumTasks"];
counts.Bugs = (int)thisRow["NumBugs"];
counts.Features = (int)thisRow["NumFeatures"];
result.Add(counts);
}
return(result.ToArray());
}
