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