/// <summary>
/// 工时统计构造方法，实现调用后，返回带有工时信息的wbs信息
/// </summary>
/// <param name="wbsInfo"></param>
public TimeHelper(WBSInfo wbsInfo)
{
ReleaseFolders = GetAllChildFolderIds(wbsInfo);
//用来保存哪些部门项目 项目名称，开发项目编号
Dictionary <string, int> InteralDevProject = new Dictionary <string, int>();
//写死特殊需要处理的部门项目
InteralDevProject.Add("通用产品事业部部门公共项目", 24038); //。。。继续写死其他开发部门项目
InteralDevProject.Add("数字家庭事业部部门公共项目", 24045); //
InteralDevProject.Add("研究院部门公共项目", 24052);
InteralDevProject.Add("3D传感技术研发中心部门公共项目", 24059);
InteralDevProject.Add("芯片部部门公共项目", 24066);
InteralDevProject.Add("新拓三维部门公共项目", 24073);
//GetCostByOwner(wbsInfo);//调用获取工时方法，实现补全wbs信息
//GetDevTimeTaskCostByOwner(wbsInfo);//获取devtime表单中已审核过任务的工时
if (InteralDevProject.ContainsValue(wbsInfo.DevSpaceID))
{
GetBuMenTaskCostByOwner(wbsInfo);
GetDevTimeCostByOwner(wbsInfo);//获取DevTime工单审批后的公用时间数据插入到部门项目信息中
}
else
{
GetDevTimeTaskCostByOwner(wbsInfo);//获取devtime表单中已审核过任务的工时
}
}
/// <summary>
/// 根据传入的项目，获取项目的wbs结构，封装成wbs泛型实体类
/// </summary>
/// <param name="projectBinder"></param>
/// <returns></returns>
public WBSInfo GetWBS(ProjectBinder projectBinder)
{
//获取项目下的链接的开发空间中的space下一层目录
//根据app.config记录的名称和编号的匹配关系，封装成wbs
WBSInfo wbs = null;
//根据传入的获取项目信息
try
{
wbs = new WBSInfo(projectBinder);
//获取项目的基础信息，也要通过app.config记录的匹配信息，获取指定字段的匹配信息
}
catch (Exception ex)
{
//LogHelper.WriteLog($"wbs初始化发生异常：" + ex.Message);
LogHelper.Error($"wbs初始化发生异常：" + ex.Message, ex);
}
return(wbs);
}
/// <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);
}
/// <summary>
/// 调用SAP提供的RFC函数ZPSRFC03传递项目信息和wbs信息给SAP
/// </summary>
/// <param name="project">项目信息</param>
/// <param name="wbsInfo">wbs信息</param>
public List <ReturnMessage> InvokeRFCFunctionZPSRFC03(Project project, WBSInfo wbsInfo)
{
IRfcFunction function = null;
List <ReturnMessage> returnMessage = new List <ReturnMessage>();
try
{
try
{
function = _rfcDestination.Repository.CreateFunction("ZPSRFC03");
// function.Invoke(_rfcDestination);
// IRfcStructure itb = function.GetStructure("ZSPS_003");
IRfcTable projectTable = function.GetTable("LT_XMDY");
projectTable.Insert();
//IRfcStructure struSAP = projectTable.Metadata.LineType.CreateStructure();
//struSAP.SetValue("POSID", project.ProjectUniqueID);//传入项目编码
//struSAP.SetValue("POST1", project.ProjectName);//传入项目名称
//struSAP.SetValue("PROFL", project.ProjectTypeID);//传入项目编码
//struSAP.SetValue("VERNR", project.ProjectManager);//传入项目经理
//struSAP.SetValue("PXMDH", project.ProjectCodeName);//传入项目代号
//struSAP.SetValue("PXMLX", project.ProjectType);//传入项目类型
//struSAP.SetValue("PWTBM", project.DelegateDepartment);//传入委托部门
//struSAP.SetValue("PCDBM", project.UnderTaskDepartment);//传入承担部门
//struSAP.SetValue("FZDDJ", project.ProjectComplexLevel);//传入项目复杂度等级
//struSAP.SetValue("PXYXJ", project.ProjectPriority);//传入项目优先级
//struSAP.SetValue("ESTAT", project.ProjectStatus);//传入项目状态
//projectTable.Append(struSAP);
//function.SetValue("LT_XMDY", projectTable);
//function.Invoke(_rfcDestination);
projectTable.CurrentRow.SetValue("POSID", project.ProjectUniqueID); //传入项目编码
//projectTable.CurrentRow.SetValue("POSID", "B1909007");//传入项目编码
projectTable.CurrentRow.SetValue("POST1", project.ProjectName); //传入项目名称
projectTable.CurrentRow.SetValue("PROFL", project.ProjectTypeID); //传入项目编码
//projectTable.CurrentRow.SetValue("VERNR", project.ProjectManager);//传入项目经理（20190926讨论后，决定不需要传项目经理）
//projectTable.CurrentRow.SetValue("VERNR", 00000002);//传入项目经理
projectTable.CurrentRow.SetValue("PXMDH", project.ProjectCodeName); //传入项目代号
projectTable.CurrentRow.SetValue("PXMLX", project.ProjectType); //传入项目类型
projectTable.CurrentRow.SetValue("PWTBM", project.DelegateDepartment); //传入委托部门
//projectTable.CurrentRow.SetValue("PWTBM", "10000010");//传入委托部门
projectTable.CurrentRow.SetValue("PCDBM", project.UnderTaskDepartment); //传入承担部门
//projectTable.CurrentRow.SetValue("PCDBM", "10000010");//传入承担部门
projectTable.CurrentRow.SetValue("FZDDJ", project.ProjectComplexLevel); //传入项目复杂度等级
projectTable.CurrentRow.SetValue("PXYXJ", project.ProjectPriority); //传入项目优先级
projectTable.CurrentRow.SetValue("ESTAT", project.ProjectStatus); //传入项目状态
// function = _rfcDestination.Repository.CreateFunction("ZPSRFC03");
function.SetValue("LT_XMDY", projectTable);
// function.Invoke(_rfcDestination);
IRfcTable wbsTable = function.GetTable("LT_WBSD");
//for (int i = 0; i < wbsInfo.WbsFolders.Count(); i++)
//{
// IRfcStructure struSAP2 = wbsTable.Metadata.LineType.CreateStructure();
// struSAP2.SetValue("ZPOSID", wbsInfo.WbsFolders[i].WBSUniqueNo);//传入wbs的DS 阶段 ID
// struSAP2.SetValue("POSIDNAME", wbsInfo.WbsFolders[i].WBSName);//传入wbs的DS 阶段名称
// struSAP2.SetValue("PSPID", wbsInfo.ProjectUniqueID);
// wbsTable.Append(struSAP2);
//}
for (int i = 0; i < wbsInfo.WbsFolders.Count(); i++)
{
wbsTable.Insert();
wbsTable.CurrentRow.SetValue("ZPOSID", wbsInfo.WbsFolders[i].WBSUniqueNo); //传入wbs的DS 阶段 ID
wbsTable.CurrentRow.SetValue("POSIDNAME", wbsInfo.WbsFolders[i].WBSName); //传入wbs的DS 阶段名称
wbsTable.CurrentRow.SetValue("PSPID", wbsInfo.ProjectUniqueID);
//wbsTable.CurrentRow.SetValue("PSPID", "B1909007");
}
// function = _rfcDestination.Repository.CreateFunction("ZPSRFC03");
function.SetValue("LT_WBSD", wbsTable);
// function.SetParameterActive(0, true);
function.Invoke(_rfcDestination);
}
catch (Exception ex)
{
//LogHelper.WriteLog("连接SAP远程服务器出错：" + ex.Message);
LogHelper.Error("连接SAP远程服务器出错：" + ex.Message, ex);
}
// function.GetElementMetadata(2).ValueMetadataAsTableMetadata.LineType.Name.ElementAt.SetValue("POSID", project.ProjectUniqueID);//传入项目编码
//function.SetValue("POSID", project.ProjectUniqueID);//传入项目编码
//function.SetValue("POST1", project.ProjectName);//传入项目名称
//function.SetValue("PROFL", project.ProjectTypeID);//传入项目编码
//function.SetValue("VERNR", project.ProjectManager);//传入项目经理
//function.SetValue("PXMDH", project.ProjectCodeName);//传入项目代号
//function.SetValue("PXMLX", project.ProjectType);//传入项目类型
//function.SetValue("PWTBM", project.DelegateDepartment);//传入委托部门
//function.SetValue("PCDBM", project.UnderTaskDepartment);//传入承担部门
//function.SetValue("FZDDJ", project.ProjectComplexLevel);//传入项目复杂度等级
//function.SetValue("PXYXJ", project.ProjectPriority);//传入项目优先级
//function.SetValue("ESTAT", project.ProjectStatus);//传入项目状态
// var Nos = from c in wbsInfo.WbsFolders select c.WBSUniqueNo;
// var Names = from n in wbsInfo.WbsFolders select n.WBSName;
// string wbsUniqueNos = string.Join("、",Nos.ToArray());
//string wbsNames = string.Join("、",Names.ToArray());
//foreach (var item in wbsInfo.WbsFolders)
//{
// function.SetValue("ZPOSID", item.WBSUniqueNo);//传入wbs的DS 阶段 ID
// function.SetValue("POSIDNAME", item.WBSName);//传入wbs的DS 阶段名称
//}
//function.SetValue("PSPID", wbsInfo.ProjectUniqueID);//传入项目编码
//function.SetParameterActive(0,true);
//function.Invoke(_rfcDestination);
//获取调用SAP的ZPSRFC03返回值到DataTable
IRfcTable myrfcTable = function.GetTable("LT_RETURN");//rfc server function 返回值table结构名称
int liElement = 0;
for (liElement = 0; liElement <= myrfcTable.ElementCount - 1; liElement++)
{
RfcElementMetadata metadata = myrfcTable.GetElementMetadata(liElement);
dtr.Columns.Add(metadata.Name); //循环创建列
}
foreach (IRfcStructure dr in myrfcTable) //循环table结构表
{
DataRow row = dtr.NewRow(); //创建新行
for (liElement = 0; liElement <= myrfcTable.ElementCount - 1; liElement++)
{
RfcElementMetadata metadata = myrfcTable.GetElementMetadata(liElement);
row[metadata.Name] = dr.GetString(metadata.Name).Trim();
}
dtr.Rows.Add(row);
}
//输出调用返回日志
for (int i = 0; i < dtr.Rows.Count; i++)
{
returnMessage.Add(new ReturnMessage()
{
Type = dtr.Rows[i]["TYPE"].ToString(),
Message = dtr.Rows[i]["MESSAGE"].ToString(),
});
LogHelper.WriteLog("SAP返回结果：消息类型：" + dtr.Rows[i]["TYPE"].ToString() + "\t消息文本" + dtr.Rows[i]["MESSAGE"].ToString());
}
}
catch (Exception ex)
{
// LogHelper.WriteLog("调用SAP函数ZPSRFC03出错：" + ex.Message);
LogHelper.Error("调用SAP函数ZPSRFC03出错：" + ex.Message, ex);
}
return(returnMessage);
}
public void InvokeRFCFunctionZPSRFC04(WBSInfo wbsInfo)
{
IRfcFunction function = null;
try
{
try
{
function = _rfcDestination.Repository.CreateFunction("ZPSRFC04");
IRfcTable wbsTable = function.GetTable("LT_GSDATA");
for (int i = 0; i < wbsInfo.WbsFolders.Count(); i++)
{
for (int j = 0; j < wbsInfo.WbsFolders[i].TimeCosts.Count(); j++)
{
wbsTable.Insert();
wbsTable.CurrentRow.SetValue("PSPID", wbsInfo.ProjectUniqueID); //项目定义编码
wbsTable.CurrentRow.SetValue("ZPOSID", wbsInfo.WbsFolders[i].WBSUniqueNo); //传入wbs的DS 阶段 ID
wbsTable.CurrentRow.SetValue("SYGID", wbsInfo.WbsFolders[i].TimeCosts[j].LoginName); //员工ID 00000002
wbsTable.CurrentRow.SetValue("MJAHR", wbsInfo.WbsFolders[i].TimeCosts[j].Yeart); //年
wbsTable.CurrentRow.SetValue("SGSYF", wbsInfo.WbsFolders[i].TimeCosts[j].Montht); //月
wbsTable.CurrentRow.SetValue("SJGSZ", wbsInfo.WbsFolders[i].TimeCosts[j].TotalTime);
}
}
function.SetValue("LT_GSDATA", wbsTable);
function.Invoke(_rfcDestination);
}
catch (Exception ex)
{
//LogHelper.WriteLog("连接SAP远程服务器出错：" + ex.Message);
LogHelper.Error("连接SAP远程服务器出错：" + ex.Message, ex);
}
//获取调用SAP的ZPSRFC04返回值到DataTable
IRfcTable myrfcTable = function.GetTable("LT_RETURN");//rfc server function 返回值table结构名称
int liElement = 0;
for (liElement = 0; liElement <= myrfcTable.ElementCount - 1; liElement++)
{
RfcElementMetadata metadata = myrfcTable.GetElementMetadata(liElement);
dtr.Columns.Add(metadata.Name); //循环创建列
}
foreach (IRfcStructure dr in myrfcTable) //循环table结构表
{
DataRow row = dtr.NewRow(); //创建新行
for (liElement = 0; liElement <= myrfcTable.ElementCount - 1; liElement++)
{
RfcElementMetadata metadata = myrfcTable.GetElementMetadata(liElement);
row[metadata.Name] = dr.GetString(metadata.Name).Trim();
}
dtr.Rows.Add(row);
}
//输出调用返回日志
for (int i = 0; i < dtr.Rows.Count; i++)
{
LogHelper.WriteLog("SAP返回结果：消息类型：" + dtr.Rows[i]["TYPE"].ToString() + "\t消息文本" + dtr.Rows[i]["MESSAGE"].ToString());
}
}
catch (Exception ex)
{
// LogHelper.WriteLog("调用SAP函数ZPSRFC04出错：" + ex.Message);
LogHelper.Error("调用SAP函数ZPSRFC04出错：" + ex.Message, ex);
}
}
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);//待实现
}