public DataProviderCollection GetRedmineData(string projectId, string sqlCommand, DateTime fromDate, DateTime toDate, string trackerIDs) { DataProviderCollection collection = new DataProviderCollection(); DbCommandSetDataProvider provider = CreateProvider(projectId, sqlCommand, trackerIDs); collection.Add(provider); collection.Add(new AdoDataProvider(CreateIssueHistory(projectId, sqlCommand, fromDate, toDate, trackerIDs))); collection.Add(new AdoDataProvider(CreateChangeSetTable(projectId, fromDate, toDate))); collection.Add(new AdoDataProvider(GetOpenTicketTimeSpan(projectId, sqlCommand, trackerIDs))); return(collection); }
private DbCommandSetDataProvider CreateProvider(string _projectId, string _sqlCommand, string _trackerIDs) { DbCommandSetDataProvider provider = new DbCommandSetDataProvider(); provider.MinimalSelect = false; IDbCommand cmd = _connection.CreateCommand(); cmd.CommandText = "SELECT issues.tracker_id AS TrackerID, issues.id AS IssueID, issues.subject AS IssueName, issue_statuses.name AS IssueStatus, u1.login AS LoginName, u1.status AS LoginNameStatus , u1.firstname AS FirstName, u1.lastname AS LastName, versions.name AS Version, projects.name AS ProjectName, enumerations.name AS Priority, issue_statuses.is_closed AS IsClosed, u2.login AS AssignedToUser, u2.status AS AssignedToUserStatus, u2.lastname AS AssignedToUserLastname, u2.type AS AssignedToUserType, issue_categories.name AS Category" + " FROM (issues INNER JOIN issue_statuses ON issues.status_id = issue_statuses.id)" + " INNER JOIN users u1 ON issues.author_id = u1.id" + " LEFT OUTER JOIN users u2 ON issues.assigned_to_id = u2.id" + " INNER JOIN projects ON issues.project_id = projects.id" + " LEFT OUTER JOIN versions ON issues.fixed_version_id = versions.id" + " INNER JOIN enumerations ON issues.priority_id = enumerations.id" + " LEFT OUTER JOIN issue_categories ON issues.category_id = issue_categories.id" + " WHERE issues.project_id = '" + _projectId + "'" + _sqlCommand + " and issues.tracker_id IN(" + _trackerIDs + ")"; provider.AddCommand(cmd, "Issues", "`{0}`", "?{0}"); cmd.CommandText = "SELECT issues.tracker_id AS TrackerID, issues.id AS IssueID, issues.subject as IssueName, issue_statuses.name as IssueStatus, u1.login as LoginName, u1.status AS LoginNameStatus, u1.firstname as FirstName, u1.lastname as LastName, versions.name AS Version, projects.name AS ProjectName, enumerations.name AS Priority, issue_statuses.is_closed AS IsClosed, u2.login AS AssignedToUser,u2.lastname AS AssignedToUserLastname, u2.type AS AssignedToUserType, u2.status AS AssignedToUserStatus, issue_categories.name AS Category" + " FROM (issues INNER JOIN issue_statuses ON issues.status_id = issue_statuses.id)" + " INNER JOIN users u1 ON issues.author_id = u1.id" + " LEFT OUTER JOIN users u2 ON issues.assigned_to_id = u2.id" + " INNER JOIN projects ON issues.project_id = projects.id" + " LEFT OUTER JOIN versions ON issues.fixed_version_id = versions.id" + " INNER JOIN enumerations ON issues.priority_id = enumerations.id" + " LEFT OUTER JOIN issue_categories ON issues.category_id = issue_categories.id" + " WHERE issues.project_id = '" + _projectId + "'" + _sqlCommand + " AND issue_statuses.is_closed = '0' AND issues.done_ratio != '100' and issues.tracker_id IN(" + _trackerIDs + ")"; provider.AddCommand(cmd, "OpenIssues", "`{0}`", "?{0}"); cmd.CommandText = "SELECT issues.tracker_id AS TrackerID, Count(status_id) AS Count, issue_statuses.name AS StatusName" + " FROM issues INNER JOIN issue_statuses ON issues.status_id = issue_statuses.id" + " WHERE issues.project_id = " + _projectId + "" + _sqlCommand + " and issues.tracker_id IN(" + _trackerIDs + ") GROUP BY status_id"; provider.AddCommand(cmd, "IssuesByStatus", "`{0}`", "?{0}"); cmd.CommandText = "SELECT issues.tracker_id AS TrackerID, issues.id AS IssueID, issues.subject as IssueName, issue_statuses.name as IssueStatus, u1.login as LoginName, u1.status AS LoginNameStatus, u1.firstname as FirstName, u1.lastname as LastName, versions.name AS Version, projects.name AS ProjectName, enumerations.name AS Priority, issue_statuses.is_closed AS IsClosed, u2.login AS AssignedToUser, u2.lastname AS AssignedToUserLastname, u2.type AS AssignedToUserType, u2.status AS AssignedToUserStatus, issue_categories.name AS Category" + " FROM (issues INNER JOIN issue_statuses ON issues.status_id = issue_statuses.id)" + " INNER JOIN users u1 ON issues.author_id = u1.id" + " LEFT OUTER JOIN users u2 ON issues.assigned_to_id = u2.id" + " INNER JOIN projects ON issues.project_id = projects.id" + " LEFT OUTER JOIN versions ON issues.fixed_version_id = versions.id" + " INNER JOIN enumerations ON issues.priority_id = enumerations.id" + " LEFT OUTER JOIN issue_categories ON issues.category_id = issue_categories.id" + " WHERE issues.project_id = '" + _projectId + "'" + _sqlCommand + " AND issue_statuses.is_closed = '0' AND issues.done_ratio = '100'" + " and issues.tracker_id IN(" + _trackerIDs + ")"; provider.AddCommand(cmd, "FixedIssues", "`{0}`", "?{0}"); provider.SupportSorting = true; return(provider); }