public IEnumerable <int> GetSonID(int projectID, int p_id)
{
using (ABZG_DSEntities dbcontext = new ABZG_DSEntities())
{
var query = from c in dbcontext.SubProjectTree
where c.ProjectID == projectID &&
c.ParentID == p_id
select c.ChildID;
return(query.ToList().Concat(query.ToList().SelectMany(t => GetSonID(projectID, t))));
}
}
/// <summary>
/// 根据ProjectID，SpaceID获取立项编号
/// </summary>
/// <param name="projectBinder"></param>
/// <returns></returns>
public int?GetProjectRequestTask(ProjectBinder projectBinder)
{
using (ABZG_DSEntities dbcontext = new ABZG_DSEntities())
{
LogHelper.WriteLog("ready to get linked specid");
int?linkedSpecID = (from b in dbcontext.Bug where b.ProjectID == projectBinder.ProjectID && b.SubProjectID == -(1500000001 + projectBinder.SpaceID) select b).SingleOrDefault <Bug>().LinkedSpecID;
projectBinder.ProjectRequestID = -linkedSpecID;
LogHelper.WriteLog("linked specid" + projectBinder.ProjectRequestID);
return(projectBinder.ProjectRequestID);
}
}
/// <summary>
/// 获取所有wbs元素对应的子目录
/// </summary>
/// <param name="wbsInfo"></param>
/// <returns></returns>
public Dictionary <string, List <int> > GetAllChildFolderIds(WBSInfo wbsInfo)
{
Dictionary <string, List <int> > ReleaseFolders = new Dictionary <string, List <int> >();
using (ABZG_DSEntities dbcontext = new ABZG_DSEntities())
{
foreach (var item in wbsInfo.WbsFolders)
{
List <int> folders = GetSonID(wbsInfo.DevProjectID, item.WBSFolderID).ToList();
folders.Add(item.WBSFolderID);
if (ReleaseFolders.ContainsKey(item.WBSUniqueNo))
{
ReleaseFolders[item.WBSUniqueNo] = ReleaseFolders[item.WBSUniqueNo].Union(folders).ToList <int>();
}
else
{
ReleaseFolders.Add(item.WBSUniqueNo, folders);
}
}
}
return(ReleaseFolders);
}
private void GetBuMenTaskCostByOwner(WBSInfo wbsInfo)
{
using (ABZG_DSEntities dbcontent = new ABZG_DSEntities())
{
// 定位时间段
//取上月第一天
DateTime checkMonthFrom = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-01 00:00:00.000")).AddMonths(-1);
//上月最后一天是当月第一天-1天
DateTime checkMonthTo = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-01 23:59:59.000")).AddDays(-1);
var month = DateTime.Parse(checkMonthFrom.ToString("yyyy-MM"));
//2. 获取所有审过的表单
var confirmSheets = from t in dbcontent.TimeSheet
join status in dbcontent.ProgressStatusTypes
on t.StatusID equals status.ProgressStatusID
where t.TSProjectID == 191 && status.ProjectID == 191 && status.ProgressStatusName == "已审核"
select t;
//3. 获取审核过表单内涉及到的人员
var users = confirmSheets.Select(p => p.TimeSheetUserID).Distinct().ToList();
List <int> folders = new List <int>();//保存部门项目下的所有目录
foreach (WBSFolder item in wbsInfo.WbsFolders)
{
folders.AddRange(ReleaseFolders[item.WBSUniqueNo]);
}
//4. 获取对应的子目录Id
List <MonthCostByOwner> monthCostsByOwner = new List <MonthCostByOwner>();
foreach (var user in users) //根据用户遍历
{
int currentUser = Convert.ToInt32(user);
//5. 获取每个用户对应的已审核的sheets
var mySheets = from mysheet in confirmSheets
where mysheet.TimeSheetUserID == currentUser
select mysheet;
//6. 从这些sheets中过滤出添加的任务的所属的timeentryid
var mySheetEntrys = from s in dbcontent.TimeSheetEntry
join sheet in mySheets
on s.TimeSheetID equals sheet.TimeSheetID
join f in folders
on s.SubProjectID equals f
where s.IssueID > 0
select s;
//7. 从timesheethour中获取对应的工时记录
var mysheetHours = from s in dbcontent.TimeSheetHour
join entry in mySheetEntrys
on s.TimeEntryID equals entry.TimeEntryID
where s.TSProjectID == 191 && s.TimeEntryDate > checkMonthFrom &&
s.TimeEntryDate < checkMonthTo
select s;
//8. 获取当前用户的统计工时
var TotalTime = mysheetHours.Sum(s => s.TimeEntryHours);
if (TotalTime > 0)
{
//遍历。。添加工时
monthCostsByOwner.Add(new MonthCostByOwner()
{
PersonID = currentUser,
LoginName = (from s in dbcontent.LogIn where s.PersonID == currentUser select s.Login1).SingleOrDefault <string>(),
TotalTime = TotalTime,
Month = month,
Yeart = month.Year,
Montht = month.Month
});
}
}
wbsInfo.WbsFolders = null;
List <WBSFolder> wbsFolders = new List <WBSFolder>()
{
new WBSFolder()
{
WBSFolderID = 01, WBSName = "部门虚拟wbs节点", WBSUniqueNo = "01", TimeCosts = monthCostsByOwner
}
};
wbsInfo.WbsFolders = wbsFolders;
}
}
private WBSInfo GetDevTimeCostByOwner(WBSInfo wbsInfo)
{
//定义存储时间的泛型列表，统计每个人当月在DevTime中花费的总工时
List <MonthCostByOwner> monthCostByOwners = new List <MonthCostByOwner>();
//取上月第一天
DateTime checkMonthFrom = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-01 00:00:00.000")).AddMonths(-1);
//上月最后一天是当月第一天-1天
DateTime checkMonthTo = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-01 23:59:59.000")).AddDays(-1);
var month = DateTime.Parse(checkMonthFrom.ToString("yyyy-MM"));
//直接获取该项目中指定人员在上月中在已审批的时间表单中花费的工时
using (ABZG_DSEntities dbcontent = new ABZG_DSEntities())
{
//获取参与部门项目开发空间的人员编号
var subProjectOwners = from owners in dbcontent.SubProjectOwners
where owners.ProjectID == wbsInfo.DevProjectID && owners.SubProjectID == wbsInfo.DevSpaceID
select owners;
if (subProjectOwners.Count() > 0)//代表当前开发空间有参与人员，需要统计devtime中工时
{
foreach (var item in subProjectOwners)
{
//获取当前遍历用户所属的已审批过的时间表单
var timeSheets = from timesheet in dbcontent.TimeSheet
join status in dbcontent.ProgressStatusTypes
on timesheet.StatusID equals status.ProgressStatusID
where status.ProgressStatusName == "已审核" && status.ProjectID == 191 &&//devtime编号
timesheet.TimeSheetUserID == item.TeamMemberID && timesheet.TSProjectID == 191
select timesheet;
if (timeSheets.Count() > 0)//如果有表单数据
{
//找到所属这些时间表单中的属于devtime公共部分的数据
var timeSheetEntry = from entry in dbcontent.TimeSheetEntry
join sheets in timeSheets
on new { entry.TSProjectID, entry.TimeSheetID } equals new { sheets.TSProjectID, sheets.TimeSheetID }
where entry.TSProjectID == 191 && entry.TimeEntryType == 3 && entry.StandTimeEntryID > 3
select entry;
if (timeSheetEntry.Count() > 0)//如果有需要检查的表单中的行数据
{
var timesheetHours = from hours in dbcontent.TimeSheetHour
join entrys in timeSheetEntry
on new { hours.TSProjectID, hours.TimeSheetID, hours.TimeEntryID } equals new { entrys.TSProjectID, entrys.TimeSheetID, entrys.TimeEntryID }
where hours.TimeEntryDate > checkMonthFrom && hours.TimeEntryDate < checkMonthTo
select hours;
monthCostByOwners.Add(new MonthCostByOwner()
{
PersonID = item.TeamMemberID,
LoginName = (from l in dbcontent.LogIn
where l.PersonID == item.TeamMemberID
select l.Login1).SingleOrDefault(),
Month = month,
Yeart = month.Year,
Montht = month.Month,
TotalTime = (from c in timesheetHours
select c.TimeEntryHours).Sum()
});
}
}
}
}
//判断部门的虚拟的节点的时间
var tr1times = (from currentr1 in wbsInfo.WbsFolders
where currentr1.WBSUniqueNo == "01"
select currentr1).SingleOrDefault().TimeCosts;
tr1times.AddRange(monthCostByOwners);
// List<MonthCostByOwner> newmonthCostByOwners = new List<MonthCostByOwner>();
var newmonthCostByOwners = from costs in tr1times
group costs
by new { costs.PersonID, costs.LoginName } into g
select new MonthCostByOwner
{
PersonID = g.Key.PersonID,
LoginName = g.Key.LoginName,
Month = month,
Yeart = month.Year,
Montht = month.Month,
TotalTime = g.Sum(x => x.TotalTime),
};
List <MonthCostByOwner> monthCostByOwners2 = new List <MonthCostByOwner>();
monthCostByOwners2 = newmonthCostByOwners.ToList();
foreach (var item in wbsInfo.WbsFolders)
{
if (item.WBSUniqueNo == "01")
{
item.TimeCosts = null;
item.TimeCosts = new List <MonthCostByOwner>();
item.TimeCosts.AddRange(monthCostByOwners2);
}
}
}
return(wbsInfo);
}
/// <summary>
/// 获取每个wbs节点的工时统计并按人员分组
/// </summary>
/// <param name="wbsInfo">参数为wbs信息</param>
/// <returns>返回带有工时统计的wbs信息</returns>
private WBSInfo GetCostByOwner(WBSInfo wbsInfo)
{
using (ABZG_DSEntities dbcontent = new ABZG_DSEntities())
{
// 对应的sql语句
// -- - 获取对应目录中的所有任务
//select BugID from Bug where projectid = 563 and subprojectid in (19929, 19930, 19931, 19950, 19924)
// --获取对应任务的所有工时
//select* from IssueTimeTracking where ProjectID = 563 and issueid in (
// select BugID from Bug where projectid = 563 and subprojectid in (19929, 19930, 19931, 19950, 19924))
//--找出满足时间的所有工时
//select* from IssueTimeTracking where ProjectID = 563 and issueid in (
// select BugID from Bug where projectid = 563 and subprojectid in (19929, 19930, 19931, 19950, 19924)) and DateAdded > '2019-08-01 00:00:00.000' and DateAdded< '2019-08-31 11:59:59.000'
// --分组
// select TimeItemOwnerID,l.Login,SUM(TimeHours) as 'total time','2019-08' as 'Month' from IssueTimeTracking time join LogIn l on time.TimeItemOwnerID = l.PersonID where ProjectID = 563 and issueid in (
// select BugID from Bug where projectid = 563 and subprojectid in (19929, 19930, 19931, 19950, 19924)) and DateAdded > '2019-08-01 00:00:00.000' and DateAdded< '2019-08-31 11:59:59.000'
//group by TimeItemOwnerID,l.Login
foreach (WBSFolder item in wbsInfo.WbsFolders)
{
//1. 获取对应的子目录Id
List <int> folders = ReleaseFolders[item.WBSUniqueNo];
//2. 根据所有的子目录，获取该wbs节点中所有的任务
var allTasks = from t in dbcontent.Bug
join id in folders
on t.SubProjectID equals id
where t.ProjectID == wbsInfo.DevProjectID
select t;
//3. 根据任务获取指定时间段内所有的工时记录
//取上月第一天
DateTime checkMonthFrom = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-01 00:00:00.000")).AddMonths(-1);
//上月最后一天是当月第一天-1天
DateTime checkMonthTo = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-01 23:59:59.000")).AddDays(-1);
////测试数据，取当月
//DateTime checkMonthFrom = DateTime.Parse(DateTime.Now.ToString("2019-08-01 00:00:00.000"));
////测试数据，取当月
//DateTime checkMonthTo = DateTime.Parse(DateTime.Now.ToString("2019-08-31 23:59:59.000"));
var alltimetracking = from track in dbcontent.IssueTimeTracking
join tasks in allTasks
on track.IssueID equals tasks.BugID
where track.ProjectID == wbsInfo.DevProjectID
// && track.DateAdded > checkMonthFrom && track.DateAdded < checkMonthTo
&& track.TimeItemFromDate > checkMonthFrom && track.TimeItemFromDate < checkMonthTo
select track;
// 4.工时统计，并按人员分组，显示每位员工花费的总工时，月份以及统计的月份
var month = DateTime.Parse(checkMonthFrom.ToString("yyyy-MM"));
var groupbytimetracking = (from track in alltimetracking
join lg in dbcontent.LogIn
on track.TimeItemOwnerID equals lg.PersonID
where track.ProjectID == wbsInfo.DevProjectID
group new { track.TimeItemOwnerID, lg.Login1, track.TimeHours, Month = month }
by new { track.TimeItemOwnerID, lg.Login1 } into g
select new
{
PersonID = g.Key.TimeItemOwnerID,
LoginName = g.Key.Login1,
TotalTime = g.Sum(c => c.TimeHours),
CheckMonth = month
});
//遍历。。添加工时
List <MonthCostByOwner> monthCostsByOwner = new List <MonthCostByOwner>();
foreach (var item3 in groupbytimetracking)
{
monthCostsByOwner.Add(new MonthCostByOwner()
{
PersonID = item3.PersonID,
LoginName = item3.LoginName,
TotalTime = item3.TotalTime,
Month = item3.CheckMonth,
Yeart = item3.CheckMonth.Year,
Montht = item3.CheckMonth.Month
});
}
item.TimeCosts = monthCostsByOwner;
}
}
return(wbsInfo);//待实现
}
//构造函数实现按照指定的项目SpaceID获取具体的项目信息
public Project(ProjectBinder projectBinder)
{
using (ABZG_DSEntities dbcontext = new ABZG_DSEntities())
{
//立项任务编号
//int? BugID = new ProjectHelper().GetProjectRequestTask(projectBinder);
int?BugID = projectBinder.ProjectRequestID;
//指定立项任务存储在表CustomerFieldTrackExt中的信息集合
var customerFieldTrackExt = (from c in dbcontext.CustomerFieldTrackExt where c.ProjectID == projectBinder.ProjectID && c.BugID == BugID select c).SingleOrDefault <CustomerFieldTrackExt>();
//指定SpaceID所在的表Subproject的单条数据
var subproject = (from p in dbcontext.SubProject where p.ProjectID == projectBinder.ProjectID && p.SubProjectID == projectBinder.SpaceID select p).SingleOrDefault <SubProject>();
//项目类型和编码的字典集合
code4ProjectType = (from code in dbcontext.CustomerFieldListValue where code.ProjectID == projectBinder.ProjectID && code.CustomFieldID == 505 select code).ToDictionary(k => k.ChoiceName.Split(' ')[0], v => v.ChoiceName.Split(' ')[1]);
//部门类型和编码的字典集合
code4Depart = (from code in dbcontext.CustomerFieldListValue where code.ProjectID == projectBinder.ProjectID && code.CustomFieldID == 503 select code).ToDictionary(k => k.ChoiceName.Split(' ')[0], v => v.ChoiceName.Split(' ')[1]);
//用户表集合
var login = (from l in dbcontext.LogIn select l);
//获取项目经理对应的personID
var bugSelection = (from b in dbcontext.BugSelectionInfo select b);
int loginID = (from c in bugSelection where c.ProjectID == projectBinder.ProjectID && c.BugID == BugID && c.FieldID == 7 select c.FieldSelectionID).SingleOrDefault();
//-------------以下是给项目属性赋值---------------------------
//PPM基础项目编号
BaseProjectID = projectBinder.ProjectID;
ProjectSpaceID = projectBinder.SpaceID;
//项目编码
ProjectUniqueID = customerFieldTrackExt.Desc_Custom_1;
//项目名称
ProjectName = subproject.Title;
//项目类型
ProjectType = customerFieldTrackExt.Desc_Custom_3 is null?"": customerFieldTrackExt.Desc_Custom_3;
//项目类型对应的编码
if (code4ProjectType.ContainsKey(ProjectType))
{
ProjectTypeID = code4ProjectType[ProjectType];
}
;
//项目经理 need improve it..
ProjectManager = (from c in login where c.PersonID == loginID select c.Login1).SingleOrDefault().ToString();
//ProjectManager = 00000002;
//项目代号
ProjectCodeName = subproject.Title;
//委托部门名称
var code4DepartName = customerFieldTrackExt.Custom_4 == null ? "" : customerFieldTrackExt.Custom_4;
//委托部门
if (!(code4DepartName == ""))
{
if (code4Depart.ContainsKey(customerFieldTrackExt.Custom_4))
{
DelegateDepartment = code4Depart[customerFieldTrackExt.Custom_4];
}
;
}
else
{
DelegateDepartment = "";
}
//承担部门
if (code4Depart.ContainsKey(customerFieldTrackExt.Custom_6))
{
UnderTaskDepartment = code4Depart[customerFieldTrackExt.Custom_6];
}
;
//项目复杂度等级
ProjectComplexLevel = customerFieldTrackExt.Custom_10;
//项目优先级
ProjectPriority = customerFieldTrackExt.Custom_11;
//项目状态
switch (projectBinder.MyFlag)
{
case Flag.insert:
ProjectStatus = "下达";
break;
case Flag.delay:
ProjectStatus = "暂停";
break;
case Flag.close:
ProjectStatus = "关闭";
break;
default:
ProjectStatus = "";
break;
}
}
}
public WBSInfo(ProjectBinder projectBinder)
{
//wbs编码赋值，初始化wbs编码字典，后续这里通过获取表的值，分析得到
//code4Wbs = new Dictionary<string, string>()
// {
// { "TR1/TR2","01"},
// { "TR3","02"},
// { "TR4A","03"},
// { "TR5","04"},
// { "TR6","05"},
// { "概念与计划阶段","06"},
// { "开发阶段","07"},
// { "验证阶段","08"},
// { "结项阶段","09"},
// { "立项准备","10"}
// };
using (ABZG_DSEntities dbcontext = new ABZG_DSEntities())
{
// code4Wbs = (from code in dbcontext.CustomerFieldListValue where code.ProjectID == projectBinder.ProjectID && code.CustomFieldID == 508 select code).ToDictionary(k => k.ChoiceName.Split(' ')[0], v => v.ChoiceName.Split(' ')[1]);
//立项任务编号
// int? BugID = new ProjectHelper().GetProjectRequestTask(projectBinder);
int?BugID = projectBinder.ProjectRequestID;
//指定立项任务存储在表CustomerFieldTrackExt中的信息集合
var customerFieldTrackExt = (from c in dbcontext.CustomerFieldTrackExt where c.ProjectID == projectBinder.ProjectID && c.BugID == BugID select c).SingleOrDefault <CustomerFieldTrackExt>();
var spaceLink = (from link in dbcontext.SpaceLink where link.ProjectID == projectBinder.ProjectID && link.ProjectTypeID == 41 && link.SpaceID == projectBinder.SpaceID select link);
//项目类型------
ProjectType = customerFieldTrackExt.Desc_Custom_3 is null ? "" : customerFieldTrackExt.Desc_Custom_3;
//项目编码
ProjectUniqueID = customerFieldTrackExt.Desc_Custom_1;
if (spaceLink.Where(s => s.ToProjectTypeID == 1).Count() > 0)
{
DevProjectID = spaceLink.Where(s => s.ToProjectTypeID == 1).SingleOrDefault().ToProjectID;
DevSpaceID = spaceLink.Where(s => s.ToProjectTypeID == 1).SingleOrDefault().ToSpaceID;
//把sql的in查询用linq的嵌套查询来实现
//第一步：获取项目下的child folder id列表
var releaseIDList = from f in dbcontext.SubProjectTree where f.ProjectID == DevProjectID && f.ParentID == DevSpaceID select f.ChildID;
//第二步：获取所有的sub folders
// var allreleaseFolders = from sub in dbcontext.SubProject where sub.ProjectID == DevProjectID && sub.SubProjectType == 99 select sub;
var allreleaseFolders = from sub in dbcontext.SubProject where sub.ProjectID == DevProjectID && sub.SubProjectType != 2002
select sub;
//第三步：获取对应项目下的满足的folders
var releaseFolders = from s in allreleaseFolders where releaseIDList.Contains(s.SubProjectID) select new { s.Title, s.ProjectID, s.SubProjectID };
WbsFolders = new List <WBSFolder>();
if (releaseFolders.Count() > 0)
{
foreach (var item in releaseFolders)
{
WbsFolders.Add(new WBSFolder()
{
WBSFolderID = item.SubProjectID,
WBSName = item.Title,
// WBSUniqueNo = code4Wbs.ContainsKey(item.Title) ? code4Wbs[item.Title] : "NoneMatchCode"
WBSUniqueNo = item.SubProjectID.ToString()
});
}
}
else
{
}
}
;
}
}
