private List<LabourTrendItem> GetLabourTrends(string projectId)
{
#region
string sql = string.Empty;
string sqlIn = string.Empty;
string sqlOut = string.Empty;
string sqlZC = string.Empty;
//获取最近12个月的月份
var beginMonth = DateTime.Now.AddMonths(-11).ToString("yyyy-MM");
var endMonth = DateTime.Now.AddMonths(1).ToString("yyyy-MM");
DateTime varDate = DateTime.Now.AddMonths(2);
string curMonth = string.Empty;
string curMonthJT = string.Empty;
for (int i = 1; i < 13; i++)//遍历12次
{
curMonth = varDate.AddMonths(-i).ToString("yyyy-MM") + "-01";
int j = i+1;
curMonthJT = varDate.AddMonths(-j).ToString("yyyy-MM");
#region 累计在场
sqlZC += @" select IFNULL(sum(t2.ZcNum),0) as ZcNum, DATE_FORMAT('" + curMonth + @"','%Y-%m-%d') as curMonth
from
(
select t1.identity_card,case when sum(t1.InNum)-sum(t1.OutNum) >0 then 1 else 0 end ZcNum
from
(
select b.identity_card,0 resumetype,count(r.id) InNum, 0 OutNum
from hr_staff_pro_resume r
LEFT JOIN hr_staff_base b on r.base_id = b.id
LEFT JOIN project_info p on r.project_id = p.id and p.id= '" + projectId + @"'
where r.resume_type = '已进场'
AND r.in_date < DATE_FORMAT('" + curMonth + @"','%Y-%m-%d')
GROUP BY b.identity_card
union all
select b.identity_card,1 resumetype,0 InNum,count(r.id) OutNum
from hr_staff_pro_resume r
LEFT JOIN hr_staff_base b on r.base_id = b.id
LEFT JOIN project_info p on r.project_id = p.id and p.id= '" + projectId + @"'
where r.resume_type = '已退场'
AND r.out_date < DATE_FORMAT('" + curMonth + @"','%Y-%m-%d')
GROUP BY b.identity_card
) t1
group by t1.identity_card
) t2 ";//累计在场
#endregion
#region 进场
sqlIn += @" SELECT '" + curMonthJT + @"' AS curMonth, COUNT(*) AS InNum
FROM hr_staff_pro_resume t
WHERE t.project_id = '" + projectId + @"'
AND t.base_id NOT IN (SELECT id FROM hr_staff_base WHERE isblacklist in (0,2))
AND t.subpack_id IN (SELECT id FROM subcontractor_base WHERE isblacklist is null or isblacklist = 0)
AND t.in_date IS NOT NULL
AND DATE_FORMAT(t.in_date,'%Y-%m') = '" + curMonthJT + @"' ";//进场
#endregion
#region 退场
sqlOut += @" SELECT '" + curMonthJT + @"' AS curMonth, COUNT(*) AS OutNum
FROM hr_staff_pro_resume t
WHERE t.project_id = '" + projectId + @"'
AND t.base_id NOT IN (SELECT id FROM hr_staff_base WHERE isblacklist in (0,2))
AND t.subpack_id IN (
SELECT id FROM subcontractor_base WHERE isblacklist is null or isblacklist = 0)
AND t.out_date IS NOT NULL
AND DATE_FORMAT(t.out_date,'%Y-%m') = '" + curMonthJT + @"' ";//退场
#endregion
if (i < 12)
{
sqlZC += @"
UNION ALL
";
sqlIn += @"
UNION ALL
";
sqlOut += @"
UNION ALL
";
}
}
DataTable dtZC = Context.FromSql(sqlZC).ToDataTable();
DataTable dtIn = Context.FromSql(sqlIn).ToDataTable();
DataTable dtOut = Context.FromSql(sqlOut).ToDataTable();
List<LabourTrendItem> labourTrendList = new List<LabourTrendItem>();
for (int i = 1; i < 13; i++)//遍历12次
{
curMonth = varDate.AddMonths(-i).ToString("yyyy-MM") + "-01";
int j = i + 1;
curMonthJT = varDate.AddMonths(-j).ToString("yyyy-MM");
LabourTrendItem item = new LabourTrendItem();
if (dtZC != null && dtZC.Rows.Count > 0)
{
DataRow[] dr = dtZC.Select("curMonth ='" + curMonth + "'");
foreach (DataRow row in dr)
{
item.Month = Convert.ToDateTime(row["curMonth"]);
item.AvailableCount = row["ZcNum"].ToString();//在场
}
}
if (dtIn != null && dtIn.Rows.Count > 0)
{
DataRow[] dr = dtIn.Select("curMonth ='"+ curMonthJT + "'");
foreach (DataRow row in dr)
{
item.Month = Convert.ToDateTime(row["curMonth"].ToString());
item.ComeCount = row["InNum"].ToString();//进场
}
}
if (dtOut != null && dtOut.Rows.Count > 0)
{
DataRow[] dr = dtOut.Select("curMonth ='" + curMonthJT + "'");
foreach (DataRow row in dr)
{
item.Month = Convert.ToDateTime(row["curMonth"].ToString());
item.OutCount = row["OutNum"].ToString();//退场
}
}
labourTrendList.Add(item);
}
return labourTrendList.OrderBy(t => t.Month).ToList();
#endregion
}
