/// <summary>
/// 部门年汇总
/// </summary>
/// <param name="year"></param>
/// <returns></returns>
public List <SummaryViewModel> DeptYearSummary(bool isContainsDeletedDept = false)
{
List <int> years = new List <int>();
int year = DateTime.Today.Year;
string sql = "SELECT DeptId AS Id,[Year] AS DateUnit,SUM(HJ) AS HJ,SUM(ML) AS ML,SUM(ZMML) AS ML,SUM(XY) AS XY FROM Base_MonthReport WHERE ([Year] BETWEEN @Year-4 AND @Year) AND IsDel=0 GROUP BY DeptId,Year";
var list = db.FetchBySql <SummaryViewModel>(sql, new { Year = year });
var depts = DeptUtil.All();
var result = new List <SummaryViewModel>();
foreach (var dept in depts.OrderBy(d => d.Sort))
{
var datas = list.Where(m => m.Id == dept.Id);
for (var i = year - 4; i <= year; i++)
{
var data = datas.Where(d => d.DateUnit == i).SingleOrDefault();
if (data == null)
{
result.Add(new SummaryViewModel
{
Id = dept.Id,
Name = dept.Name,
HJ = 0,
ZMML = 0,
YJ = 0,
ML = 0,
DateUnit = i,
Year = year,
XY = 0
});
}
else
{
result.Add(new SummaryViewModel
{
Id = dept.Id,
Name = dept.Name,
HJ = data.HJ,
ZMML = data.ZMML,
YJ = data.YJ,
ML = data.ML,
DateUnit = i,
Year = year,
XY = data.XY
});
}
}
}
return(result);
}
/// <summary>
/// 批量创建空白记录
/// </summary>
/// <param name="year">年</param>
/// <param name="month">月</param>
/// <param name="user">操作人</param>
/// <returns></returns>
public Result Create(MonthReportMultiCreateDto entity, IAppUser user)
{
var quarter = 1;
var halfYear = 1;
if (entity.Month > 0 && entity.Month <= 3)
{
quarter = 1;
}
else if (entity.Month > 3 && entity.Month <= 6)
{
quarter = 2;
}
else if (entity.Month > 6 && entity.Month <= 9)
{
quarter = 3;
halfYear = 2;
}
else if (entity.Month > 9 && entity.Month <= 12)
{
quarter = 4;
halfYear = 2;
}
else
{
return(ResultUtil.Fail("无效的月度参数"));
}
var count = db.GetCount <MonthReportEntity>(MySearchUtil.New()
.AndEqual("Month", entity.Month)
.AndEqual("Year", entity.Year));
if (count > 0)
{
return(ResultUtil.Fail("已存在该年度该月记录，禁止批量添加"));
}
var depts = DeptUtil.All();
string sql = @"
INSERT INTO Base_MonthReport (SalerId,DeptId,Year,HalfYear,Quarter,Month,FJ,HC,BS,ZJC,DSF,YWZDF,ZSF,ZSJYJL,ZBFY,CCBT,TXF,QTFY,XZ,YJ,FWF,ZMML,IsDel,CreateAt,CreateBy,Creator,UpdateAt,UpdateBy,Updator)
SELECT Id,DeptId,@Year,@HalfYear,@Quarter,@Month,0,0,0,0,0,0,0,0,0,0,0,0,Salary,0,0,0,0,GETDATE(),@UserId,@UserName,GETDATE(),@UserId,@UserName
FROM Base_Saler WHERE IsDel=0 ORDER BY DeptId,Name";
var row = db.Execute(sql, new { Year = entity.Year, Month = entity.Month, HalfYear = halfYear, Quarter = quarter, UserId = user.Id, UserName = user.Name });
return(row > 0 ? ResultUtil.Success <int>(row) : ResultUtil.Fail());
}