public List <ExpenseAccountMonthCateDTO> GetMonthCateList(MonthExpenseStatisticsSM sm)
{
var sql = @" select SUM(Money) as SumMoney ,c.CateName , year(OccurDate) as ECYear ,month(OccurDate) as ECMonth from EM_ExpenseAccount_Detail a
join EM_Charge_Cate b on a.CateId=b.Id
join EM_Charge_Cate c on b.ParentId=c.Id
where CateId in (select * from dbo.FC_GetRoleChildrenCateIds(@RoleType))
and a.CompanyId in ({0}) ";
sql = string.Format(sql, sm.CompanyIds);
if (sm.SDate.HasValue)
{
sql += " and a.OccurDate>=@SDate ";
}
if (sm.EDate.HasValue)
{
sql += " and a.OccurDate<=@EDate ";
}
sql += @" group by c.CateName, year(OccurDate),month(OccurDate) order by ECYear, ECMonth";
var result = DapperHelper.SqlQuery <ExpenseAccountMonthCateDTO>(sql, sm).ToList();
return(result);
}
public async Task <ActionResult> MonthExpenseStatistics(MonthExpenseStatisticsSM sm)
{
if (Request.IsAjaxRequest())
{
var nameList = new List <string>();
if (!sm.SDate.HasValue)
{
sm.SDate = DateTime.Now.AddYears(-1);
}
if (!sm.EDate.HasValue)
{
sm.EDate = DateTime.Now;
}
var cateList = changeCateRepo.GetList(ViewHelp.GetRoleType(), CateDropType.Report, ViewHelp.GetCateIds());
sm.CompanyIds = ViewHelp.GetCompanyIds();
sm.RoleType = ViewHelp.GetRoleType();
var dtos = expenseAccountRepo.GetMonthCateList(sm);
var list = new Dictionary <string, List <ExpenseAccountMonthCateDTO> >();
if (dtos.Count > 0)
{
//补全信息
foreach (var cate in cateList)
{
var BeginDate = new DateTime(sm.SDate.Value.Year, sm.SDate.Value.Month, 1);
var EndDate = new DateTime(sm.EDate.Value.Year, sm.EDate.Value.Month, 1);
list.Add(cate.Value, new List <ExpenseAccountMonthCateDTO>());
if (BeginDate != EndDate)
{
//不是同月的
while (BeginDate != EndDate)
{
var Dto = new ExpenseAccountMonthCateDTO()
{
CateName = cate.Value, ECMonth = BeginDate.Month, ECYear = BeginDate.Year, SumMoney = 0
};
Dto.SumMoney = dtos.Where(o => o.CateName == cate.Value && o.ECYear == BeginDate.Year && o.ECMonth == BeginDate.Month).Select(o => o.SumMoney).FirstOrDefault();
Dto.SumMoney = Dto.SumMoney ?? 0;
list[cate.Value].Add(Dto);
nameList.Add(BeginDate.GetMonthName());
BeginDate = BeginDate.AddMonths(1);
}
}
else
{
//同月的
var Dto = new ExpenseAccountMonthCateDTO()
{
CateName = cate.Value, ECMonth = BeginDate.Month, ECYear = BeginDate.Year, SumMoney = 0
};
Dto.SumMoney = dtos.Where(o => o.CateName == cate.Value && o.ECYear == BeginDate.Year && o.ECMonth == BeginDate.Month).Select(o => o.SumMoney).FirstOrDefault();
Dto.SumMoney = Dto.SumMoney ?? 0;
list[cate.Value].Add(Dto);
nameList.Add(BeginDate.GetMonthName());
BeginDate = BeginDate.AddMonths(1);
}
}
}
return(Json(new { NameList = nameList, List = list }, JsonRequestBehavior.AllowGet));
}
else
{
ViewBag.MonthList = ViewHelp.GetRecentlyMonthList();
}
return(View());
}
