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());
    }
示例#4
0
    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());
    }