帮助类-从tfs获取数据

using JC.Helper;
using Microsoft.TeamFoundation.Client;
using Microsoft.TeamFoundation.Framework.Client;
using Microsoft.TeamFoundation.Framework.Common;
using Microsoft.TeamFoundation.WorkItemTracking.Client;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.DirectoryServices;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading;
using System.Web;

namespace DJRS.UI.Common
{
    public class TFSHelper
    {
        #region 初始化变量
        private string _TFSUserName;
        private string _TFSPWD;
        private string _TFSURL;
        public string TFSUserName
        {
            get
            {
                if (string.IsNullOrEmpty(_TFSUserName))
                {
                    _TFSUserName = AppSettingHelper.Get<string>("TFSUserName");
                }
                return _TFSUserName;
            }
        }
        public string TFSPWD
        {
            get
            {
                if (string.IsNullOrEmpty(_TFSPWD))
                {
                    _TFSPWD = AppSettingHelper.Get<string>("TFSPWD");
                }
                return _TFSPWD;
            }
        }
        public string TFSURL
        {
            get
            {
                if (string.IsNullOrEmpty(_TFSURL))
                {
                    _TFSURL = AppSettingHelper.Get<string>("TFSURL");
                }
                return _TFSURL;
            }
        } 
        #endregion
        /// <summary>
        /// 获取最近两个星期的任务和bug
        /// </summary>
        /// <param name="projectName"></param> 
        /// <param name="userName"></param>
        /// <param name="days">查询最近多少天的数据（默认14天，也就是两个星期内的）</param>
        public List<WorkItemView> GetRecentTaskAndBug(string projectName, string userName, int days = 14)
        {
            TfsTeamProjectCollection server = null;
            try
            {
                var ret = new List<WorkItemView>();
                //返回id,创建时间，创建人，描述
                Uri tfsUri = new Uri(TFSURL);
                NetworkCredential networkCredential = new NetworkCredential(TFSUserName, TFSPWD);
                ICredentials credential = (ICredentials)networkCredential;
                server = new TfsTeamProjectCollection(tfsUri, credential);
                var workStore = server.GetService<WorkItemStore>();

                string sql = "";
                if (string.IsNullOrEmpty(projectName))
                {
                    sql = $@"select [System.Id], [工作项类型], [System.Title], [System.AssignedTo], [System.State], [zzz.BugModule] 
                from WorkItems
                where  ((([System.WorkItemType] = '任务' or[System.WorkItemType] = '用户情景') and[System.State] <> '已删除' and[System.AssignedTo] = '{userName}' and[System.CreatedDate] >= @today - {days}) or([System.WorkItemType] = 'Bug' and not[System.Title] contains '版本生成失败:' and(ever[System.ChangedBy] = '{userName}' or[System.AssignedTo] = '{userName}') and[System.State] <> '已删除' and[System.CreatedDate] >= @today - {days})) order by[System.CreatedDate] desc";
                }
                else
                {
                    sql = @"select [System.Id], [工作项类型], [System.Title], [System.AssignedTo], [System.State], [zzz.BugModule] 
                from WorkItems
                where
                [System.TeamProject] = '" + projectName + $"' and((([System.WorkItemType] = '任务' or[System.WorkItemType] = '用户情景') and[System.State] <> '已删除' and[System.AssignedTo] = '{userName}' and[System.CreatedDate] >= @today - {days}) or([System.WorkItemType] = 'Bug' and not[System.Title] contains '版本生成失败:' and(ever[System.ChangedBy] = '{userName}' or[System.AssignedTo] = '{userName}') and[System.State] <> '已删除' and[System.CreatedDate] >= @today - {days})) order by[System.CreatedDate] desc";
                }


                var res = workStore.Query(sql);
                foreach (WorkItem item in res)
                {
                    var obj = new WorkItemView();
                    if (item.Fields.Contains("标题")) obj.标题 = (string)item.Fields["标题"].Value;
                    if (item.Fields.Contains("工作项类型")) obj.工作项类型 = (string)item.Fields["工作项类型"].Value;
                    if (item.Fields.Contains("团队项目")) obj.团队项目 = (string)item.Fields["团队项目"].Value;
                    if (item.Fields.Contains("ID")) obj.ID = (int)item.Fields["ID"].Value;
                    if (item.Fields.Contains("区域路径")) obj.区域路径 = (string)item.Fields["区域路径"].Value;
                    if (item.Fields.Contains("迭代路径")) obj.迭代路径 = (string)item.Fields["迭代路径"].Value;
                    if (item.Fields.Contains("指派给")) obj.指派给 = (string)item.Fields["指派给"].Value;
                    if (item.Fields.Contains("创建者")) obj.创建者 = (string)item.Fields["创建者"].Value;
                    if (item.Fields.Contains("创建日期")) obj.创建日期 = (DateTime?)item.Fields["创建日期"]?.Value;
                    if (item.Fields.Contains("激活者")) obj.激活者 = (string)item.Fields["激活者"].Value;
                    if (item.Fields.Contains("激活日期")) obj.激活日期 = (DateTime?)item.Fields["激活日期"]?.Value;
                    if (item.Fields.Contains("关闭者")) obj.关闭者 = (string)item.Fields["关闭者"].Value;
                    if (item.Fields.Contains("关闭日期")) obj.关闭日期 = (DateTime?)item.Fields["关闭日期"]?.Value;
                    if (item.Fields.Contains("解决者")) obj.解决者 = (string)item.Fields["解决者"].Value;
                    if (item.Fields.Contains("解决日期")) obj.解决日期 = (DateTime?)item.Fields["解决日期"]?.Value;
                    if (item.Fields.Contains("所属模块")) obj.所属模块 = (string)item.Fields["所属模块"].Value;
                    if (item.Fields.Contains("重现步骤")) obj.重现步骤 = (string)item.Fields["重现步骤"].Value; 
                    ret.Add(obj);
                }

                return ret;
            }
            finally
            {
                server.Dispose();
            }
        }


        /// <summary>
        /// 获取项目的模块
        /// </summary>
        public List<string> GetModuleInProject(string projectName)
        {
            TfsTeamProjectCollection server = null;
            try
            {
                var res = new List<string>();

                Uri tfsUri = new Uri(TFSURL);
                NetworkCredential networkCredential = new NetworkCredential(TFSUserName, TFSPWD);
                ICredentials credential = (ICredentials)networkCredential;
                server = new TfsTeamProjectCollection(tfsUri, credential);
                var workStore = server.GetService<WorkItemStore>();
                var project = workStore.Projects[projectName];

                WorkItemType bugType = project.WorkItemTypes["Bug"];
                WorkItem itemw = new WorkItem(bugType);
                var allowVals = itemw.Fields["所属模块"].AllowedValues;
                foreach (string val in allowVals)
                {
                    res.Add(val);
                }

                return res;
            }catch  (Exception ex)
            {
                return new List<string>();
            }
            finally
            {
                server.Dispose();
            }
        }
        /// <summary>
        /// 获取tfs中的项目名列表
        /// </summary>
        public List<string> GetProjects()
        {
            TfsTeamProjectCollection server = null;
            WorkItemStore workStore = null;
            try
            {
                Uri tfsUri = new Uri(TFSURL);
                NetworkCredential networkCredential = new NetworkCredential(TFSUserName, TFSPWD);
                ICredentials credential = (ICredentials)networkCredential;
                server = new TfsTeamProjectCollection(tfsUri, credential);

                workStore = server.GetService<WorkItemStore>();

                var res = new List<string>();
                TfsConfigurationServer configserver = TfsConfigurationServerFactory.GetConfigurationServer(tfsUri);
                ReadOnlyCollection<CatalogNode> collectNodes =
                    configserver.CatalogNode.QueryChildren(new[] { CatalogResourceTypes.ProjectCollection },
                    false, CatalogQueryOptions.None);
                foreach (CatalogNode collect in collectNodes)
                {
                    Guid collectid = new Guid(collect.Resource.Properties["InstanceId"]);
                    TfsTeamProjectCollection teamproject = configserver.GetTeamProjectCollection(collectid);
                    ReadOnlyCollection<CatalogNode> projNodes = collect.QueryChildren(new[] { CatalogResourceTypes.TeamProject },
                         false, CatalogQueryOptions.None);
                    foreach (CatalogNode projnode in projNodes)
                    {
                        res.Add(projnode.Resource.DisplayName);
                    }
                }

                return res;
            }
            catch (Exception ex)
            {
                while (ex.InnerException != null)
                {
                    ex = ex.InnerException;
                }
                throw ex;
            }
            finally
            {
                server.Dispose();
            }
        }
        /// <summary>
        /// 获取tfs中的项目名列表
        /// </summary>
        public List<string> GetAllProjects()
        {
            TfsTeamProjectCollection server = null;
            WorkItemStore workStore = null;
            try
            {
                Uri tfsUri = new Uri(TFSURL);
                NetworkCredential networkCredential = new NetworkCredential(TFSUserName, TFSPWD);
                ICredentials credential = (ICredentials)networkCredential;
                server = new TfsTeamProjectCollection(tfsUri, credential);

                workStore = server.GetService<WorkItemStore>();
                var res = new List<string>();

                foreach (Project item in workStore.Projects)
                {
                    res.Add(item.Name);
                }

                return res;
            }
            catch (Exception ex)
            {
                while (ex.InnerException != null)
                {
                    ex = ex.InnerException;
                }
                throw ex;
            }
            finally
            {
                server.Dispose();
            }
        }
        /// <summary>
        /// 获取项目成员
        /// </summary>
        public List<TeamMember> GetProjectMembers(string projectName)
        {
            TfsTeamProjectCollection server = null;
            try
            {
                Uri tfsUri = new Uri(TFSURL);
                NetworkCredential networkCredential = new NetworkCredential(TFSUserName, TFSPWD);
                ICredentials credential = (ICredentials)networkCredential;
                server = new TfsTeamProjectCollection(tfsUri, credential);
                var teamService = server.GetService<TfsTeamService>();
                var ms = teamService.QueryTeams(projectName);
                HashSet<TeamMember> ls = new HashSet<TeamMember>(new TeamMember());
                foreach (var item in ms)
                {
                    var members = item.GetMembers(server, MembershipQuery.Direct);
                    Console.WriteLine(item.Name);
                    foreach (var m in members)
                    {
                        if (m.IsContainer)
                        {
                            continue;
                        }
                        ls.Add(new TeamMember { UniqueName = m.UniqueName, DisplayName = m.DisplayName, TeamName = item.Name });
                    }

                }

                return ls.ToList();
            }
            finally
            {
                server.Dispose();
            }
        }


    }
    public class TeamMember : IEqualityComparer<TeamMember>
    {
        public string TeamName { get; set; }
        public string DisplayName { get; set; }
        public string UniqueName { get; set; }

        public bool Equals(TeamMember x, TeamMember y)
        {
            return x.UniqueName == y.UniqueName;
        }

        public int GetHashCode(TeamMember obj)
        {
            return obj.UniqueName.GetHashCode();
        }
    }
    public class WorkItemView
    {
        public int ID { get; set; }
        public string 标题 { get; set; }
        public string 工作项类型 { get; set; }
        public string 团队项目 { get; set; }
        public string 区域路径 { get; set; }
        public string 迭代路径 { get; set; }
        public string 指派给 { get; set; }
        public string 创建者 { get; set; }
        public DateTime? 创建日期 { get; set; }
        public string 激活者 { get; set; }
        public DateTime? 激活日期 { get; set; }
        public string 关闭者 { get; set; }
        public DateTime? 关闭日期 { get; set; }
        public string 解决者 { get; set; }
        public DateTime? 解决日期 { get; set; }
        public string 所属模块 { get; set; }
        public string 重现步骤 { get; set; }
        public string 项目名
        {
            get
            {
                return 迭代路径.Split('\')[0];
            }
        }
    }
}