/// <summary>
/// 施工日志汇总表
/// </summary>
/// <param name="ajson"></param>
/// <returns></returns>
[HttpPost]
[Route("BuildLog/Summary")]
public O34007 QueryBuildLogList(I34007 ajson)
{
O34007 fResult = new O34007();
var fmode = db.PRJ_BuildLog.FirstOrDefault(p => p.PlanID == ajson.PlanID);
if (fmode == null)
{
fResult.Msg = "找不到对应的记录";
return fResult;
}
var q = (from p in db.PRJ_BuildLogInfo.Where(p => p.BuildLogID == fmode.BuildLogID)
group p by p.TeamName into g
select new
{
TeamName=g.Key,
TotalsPeopleNum = g.Sum(p => p.PeopleNum),
TotalsDay = g.Count(),
TotalsCraneMoney = g.Sum(p => p.CraneMoney)
}).ToList();
fResult.HeadRows = q;
fResult.Success = 1;
return fResult;
}
sql创建视图语句
SELECT ISNULL(NEWID(), N'5757E7EF-2F19-4408-B413-8F1B33B9895F') AS id, dbo.PRJ_BuildLogInfo.TeamName,
SUM(dbo.PRJ_BuildLogInfo.PeopleNum) AS TotalsPeopleNum, SUM(dbo.PRJ_BuildLogInfo.CraneMoney)
AS TotalsCraneMoney, dbo.PRJ_BuildLog.PlanName, dbo.PRJ_BuildLog.PlanID, COUNT(*) AS TotalsDay,
dbo.PRJ_BuildLog.BuildLogName, dbo.PRJ_BuildLog.CreateUser, dbo.PRJ_BuildLog.CreateTime,
dbo.PRJ_BuildLog.AuditUserName, dbo.PRJ_BuildLog.AuditTime, dbo.PRJ_BuildLog.BuildLogID
FROM dbo.PRJ_BuildLogInfo INNER JOIN
dbo.PRJ_BuildLog ON dbo.PRJ_BuildLogInfo.BuildLogID = dbo.PRJ_BuildLog.BuildLogID
WHERE (dbo.PRJ_BuildLog.Status <> '新建') AND (dbo.PRJ_BuildLog.Status <> '作废')
GROUP BY dbo.PRJ_BuildLogInfo.TeamName, dbo.PRJ_BuildLog.PlanName, dbo.PRJ_BuildLog.PlanID,
dbo.PRJ_BuildLog.BuildLogName, dbo.PRJ_BuildLog.CreateUser, dbo.PRJ_BuildLog.CreateTime,
dbo.PRJ_BuildLog.AuditUserName, dbo.PRJ_BuildLog.AuditTime, dbo.PRJ_BuildLog.BuildLogID