public async Task <ActionResult <ApiResult <GroupPivot> > > ProjectDistribution(bool?activeOnly, string categories)
{
var dutyQuery = await Helper.GetRerencesByType(Connector, ReferenceTypeEnum.Duty, activeOnly);
var duties = string.IsNullOrWhiteSpace(categories)?
dutyQuery.Records:
from d in dutyQuery.Records
where (d.ReferenceCode != null && d.ReferenceCode.Length > 0) &&
categories.Contains(d.ReferenceCode.ToCharArray()[0], StringComparison.OrdinalIgnoreCase)
select d;
StringBuilder dutyListBuilder = new StringBuilder();
foreach (var duty in duties)
{
dutyListBuilder.Append($", [{duty.ReferenceValue}]");
}
if (dutyListBuilder.Length > 0)
{
dutyListBuilder.Remove(0, 2);
}
string hideInactive = activeOnly == null?"":activeOnly.Value?"[ActiveStatus]=1":"";
string sql = @"
WITH base AS (
SELECT ProjectName, DutyName,
STUFF((SELECT ',' + CONVERT(nvarchar(max), ID)+ '|' + Name + '|'
+ CONVERT(NVARCHAR(max), ActiveStatus) + '|' + CONVERT(NVARCHAR(max), InDutyID)
FROM tb_employee
WHERE ProjectName = e.ProjectName
AND DutyName = e.DutyName
FOR xml PATH('')
), 1, 1, '') AS EmployeeName
FROM tb_employee e
WHERE ISNULL(ProjectName, '') <> ''"
+ (string.IsNullOrEmpty(hideInactive)?"":(" AND " + hideInactive)) + @"
GROUP BY ProjectName, DutyName
)
SELECT ProjectName AS [项目名称]," + dutyListBuilder.ToString() + @"
FROM base
PIVOT (
MAX(EmployeeName)
FOR DutyName
IN (" + dutyListBuilder.ToString() + @")
) AS final_result
";
GroupPivot pivot = await Connector.ExecuteSqlQueryTable(sql);
return(Success(pivot));
}
public async Task <ActionResult <ApiResult <GroupPivot> > > OrganizationDistribution(bool?activeOnly)
{
var qualifQuery = await Helper.GetRerencesByType(Connector, ReferenceTypeEnum.Qualification, activeOnly);
StringBuilder qualifListBuilder = new StringBuilder();
foreach (var duty in qualifQuery.Records)
{
qualifListBuilder.Append($", [{duty.ReferenceValue}]");
}
if (qualifListBuilder.Length > 0)
{
qualifListBuilder.Remove(0, 2);
}
string hideInactive = activeOnly == null?"":activeOnly.Value?"e.[ActiveStatus]=1":"";
string sql = @"
WITH src AS (
SELECT o.Name AS OrgName,
r.ReferenceValue AS QualifName,
CONVERT(NVARCHAR(MAX), e.ID) + '|' + e.Name
+ '|' + CONVERT(NVARCHAR(max), e.ActiveStatus) AS EmployeeName
FROM tb_employee e INNER JOIN tb_organization o ON e.OrganizationID=o.ID
INNER JOIN tb_certification c ON e.ID=c.EmployeeID
INNER JOIN tb_reference r ON c.QualificationID = r.ID"
+ (string.IsNullOrEmpty(hideInactive)?"":(" WHERE " + hideInactive)) + @"
)
SELECT OrgName AS [单位名称], " + qualifListBuilder.ToString() + @"
FROM (
SELECT OrgName, QualifName,
STUFF(
(
SELECT ',' + EmployeeName FROM src
WHERE OrgName = src1.OrgName
AND QualifName = src1.QualifName
FOR xml PATH('')
), 1, 1, ''
) AS StaffList
FROM src src1
GROUP BY OrgName, QualifName
) base PIVOT (
MAX(StaffList)
FOR QualifName
IN (" + qualifListBuilder.ToString() + @")
) AS final_result;";
GroupPivot pivot = await Connector.ExecuteSqlQueryTable(sql);
return(Success(pivot));
}