/// <summary>
/// 保险对账
/// 统计方式：'月、季度、年'
/// </summary>
/// <param name="queryCond"></param>
/// <returns></returns>
public InsuranceCombineBasicViewModel CombineList(InsuranceCombineQuery queryCond)
{
InsuranceCombineBasicViewModel result = null;
//统计方式：月份
if (queryCond.DisplayType == InsuranceCombineDisplayType.Month)
{
result = this.CombineByMonth(queryCond);
}
//统计方式：季度（年份单选、季度多选）
if (queryCond.DisplayType == InsuranceCombineDisplayType.Quarter)
{
result = this.CombineByQuarter(queryCond);
}
//统计方式：年份（多选）
if (queryCond.DisplayType == InsuranceCombineDisplayType.Year)
{
result = this.CombineByYear(queryCond);
}
return result;
}
private User ValidateUser(InsuranceCombineQuery queryCond)
{
var user = this._userRepository.GetModel().Include(c => c.Branch).Where(c => c.ID == queryCond.Sys_LoginUserID).SingleOrDefault();
if (user == null)
throw new DataOperationPermissions("用户无权限访问该资源，请求失败");
//总公司、系统管理员 可以选择机构
if (user.Type == UserType.Company || (int)user.Type == 0)
{
if ((int)user.Type != 0)
{
queryCond.BranchID = user.Branch.ID.ToString();
}
}
else
{
if (!string.IsNullOrWhiteSpace(queryCond.BranchID))
throw new BusinessException("非总公司账号,不能选择机构进行查询");
}
return user;
}
private InsuranceCombineBasicViewModel CombineByQuarter(InsuranceCombineQuery queryCond)
{
#region validate
if (string.IsNullOrWhiteSpace(queryCond.Quarter))
throw new DataValidationException("请选择季度");
if (string.IsNullOrWhiteSpace(queryCond.Year))
throw new DataValidationException("请选择年份");
#endregion
#region field
User user = this.ValidateUser(queryCond);
InsuranceCombineBasicViewModel result = new InsuranceCombineBasicViewModel();
result.DisplayType = InsuranceCombineDisplayType.Quarter;
result.DataByDate = new List<InsuranceCombineViewModel>();
#endregion
#region sql
//季度
var arrQ = Array.ConvertAll<string, int>(queryCond.Quarter.Split(','), x => int.Parse(x));
//季度对应的月份
Dictionary<int, List<int>> arrM = new Dictionary<int, List<int>>();
string strM = string.Empty;
foreach (var item in arrQ)
{
#region
List<int> l = new List<int>();
//第一季度
if (item == 1)
{
l.Add(1); l.Add(2); l.Add(3);
arrM.Add(1, l);
strM += "1,2,3,";
}
//第二季度
if (item == 2)
{
l.Add(4); l.Add(5); l.Add(6);
arrM.Add(2, l);
strM += "4,5,6,";
}
//第三季度
if (item == 3)
{
l.Add(7); l.Add(8); l.Add(9);
arrM.Add(3, l);
strM += "7,8,9,";
}
//第四季度
if (item == 4)
{
l.Add(10); l.Add(11); l.Add(12);
arrM.Add(4, l);
strM += "10,11,12,";
}
#endregion
}
strM = strM.Remove(strM.Length - 1, 1);
string sql = @"SELECT BranchId,BranchName,DATEPART(YEAR,BuyTime) AS Years,DATEPART(MONTH,BuyTime) AS Months,COUNT(BranchId) AS TotalCount,SUM(Money) AS Money
FROM dbo.Insurance
WHERE ";
if ((int)user.Type != 0)
{
var arrB = Array.ConvertAll<string, string>(queryCond.BranchID.Split(','), x => x);
sql += string.Format(" BranchId IN ({0}) AND", string.Join(",", arrB));
}
sql += string.Format(@" DATEPART(YEAR,BuyTime)={0} AND DATEPART(MONTH,BuyTime) IN({1})
GROUP BY BranchId,DATEPART(YEAR,BuyTime),DATEPART(MONTH,BuyTime),BranchName
ORDER BY Months DESC", queryCond.Year, strM);
#endregion
#region result
var list = this._insuranceRepository.ExecQuerySQL<InsuranceCombineViewModelByDataBase>(sql);
//季度
foreach (var quarter in arrQ)
{
string qStr = queryCond.Year + "年第" + quarter + "季度";
var itemTmp = list.Where(c => arrM[quarter].Contains(c.Months));
InsuranceCombineViewModel model = new InsuranceCombineViewModel()
{
DisplayTime = qStr,
DisplayCopies = itemTmp.Sum(c => c.TotalCount),
DisplayMoney = itemTmp.Sum(c => c.Money),
Data = new List<InsuranceCombineViewModelByDate>()
};
var branch = itemTmp.Select(c => c.BranchId).Distinct();
foreach (var d in branch)
{
var singleBranch = itemTmp.Where(c => c.BranchId == d);
model.Data.Add(new InsuranceCombineViewModelByDate()
{
BranchName = singleBranch.FirstOrDefault().BranchName,
Copies = singleBranch.Sum(c => c.TotalCount),
Money = singleBranch.Sum(c => c.Money)
});
}
result.DataByDate.Add(model);
}
result.DisplaySumMoney = result.DataByDate.Sum(c => c.DisplayMoney);
#endregion
return result;
}
private InsuranceCombineBasicViewModel CombineByYear(InsuranceCombineQuery queryCond)
{
#region validate
if (string.IsNullOrWhiteSpace(queryCond.Year))
throw new DataValidationException("请选择年份");
User user = this.ValidateUser(queryCond);
InsuranceCombineBasicViewModel result = new InsuranceCombineBasicViewModel();
result.DisplayType = InsuranceCombineDisplayType.Year;
result.DataByDate = new List<InsuranceCombineViewModel>();
#endregion
#region sql
var arrM = Array.ConvertAll<string, string>(queryCond.Year.Split(','), x => x);
string sql = @"SELECT BranchId,BranchName,DATEPART(YEAR,BuyTime) AS Years,COUNT(BranchId) AS TotalCount,SUM(Money) AS Money
FROM dbo.Insurance
WHERE";
if ((int)user.Type != 0)
{
var arrB = Array.ConvertAll<string, string>(queryCond.BranchID.Split(','), x => x);
sql += string.Format(" BranchId IN ({0}) AND", string.Join(",", arrB));
}
sql += string.Format(@" DATEPART(YEAR,BuyTime) IN({0})
GROUP BY BranchId,DATEPART(YEAR,BuyTime),BranchName
ORDER BY Years DESC", string.Join(",", arrM));
#endregion
#region result
var list = this._insuranceRepository.ExecQuerySQL<InsuranceCombineViewModelByDataBase>(sql);
//年份
var years = (from q in list select q.Years).Distinct();
foreach (var year in years)
{
var itemTmp = list.Where(c => c.Years == year);
InsuranceCombineViewModel model = new InsuranceCombineViewModel()
{
DisplayTime = year + "年",
DisplayCopies = itemTmp.Sum(c => c.TotalCount),
DisplayMoney = itemTmp.Sum(c => c.Money),
Data = new List<InsuranceCombineViewModelByDate>()
};
foreach (var item in itemTmp)
{
model.Data.Add(new InsuranceCombineViewModelByDate()
{
BranchName = item.BranchName,
Copies = item.TotalCount,
Money = item.Money
});
}
result.DataByDate.Add(model);
}
result.DisplaySumMoney = result.DataByDate.Sum(c => c.DisplayMoney);
#endregion
return result;
}
private InsuranceCombineBasicViewModel CombineByMonth(InsuranceCombineQuery queryCond)
{
#region validate
if (!queryCond.StartDate.HasValue)
throw new DataValidationException("请选择开始时间");
if (!queryCond.EndDate.HasValue)
throw new DataValidationException("请选择结束时间");
#endregion
#region field
User user = this.ValidateUser(queryCond);
DateTime startDate = queryCond.StartDate.HasValue ? queryCond.StartDate.Value : DateTime.Now;
DateTime endDate = queryCond.EndDate.HasValue ? queryCond.EndDate.Value : DateTime.Now;
InsuranceCombineBasicViewModel result = new InsuranceCombineBasicViewModel();
result.DisplayType = InsuranceCombineDisplayType.Month;
result.DataByDate = new List<InsuranceCombineViewModel>();
#endregion
#region sql
string sql = @"SELECT BranchId,BranchName,DATEPART(YEAR,BuyTime) AS Years,DATEPART(MONTH,BuyTime) AS Months,
(CONVERT(VARCHAR(20),DATEPART(YEAR,BuyTime))+'年'+CONVERT(VARCHAR(20),DATEPART(MONTH,BuyTime))+'月') AS TimeStr
,COUNT(BranchId) AS TotalCount,SUM(Money) AS Money
FROM dbo.Insurance
WHERE ";
if ((int)user.Type != 0)
{
var arrB = Array.ConvertAll<string, string>(queryCond.BranchID.Split(','), x => x);
sql += string.Format(" BranchId IN ({0}) AND", string.Join(",", arrB));
}
sql += string.Format(@" BuyTime>='{0}' AND BuyTime<='{1}'
GROUP BY BranchId,DATEPART(YEAR,BuyTime),DATEPART(MONTH,BuyTime),BranchName
ORDER BY Years,Months DESC", string.Format("{0:d}", startDate), string.Format("{0:d}", endDate));
#endregion
#region result
var list = this._insuranceRepository.ExecQuerySQL<InsuranceCombineViewModelByDataBase>(sql);
//年份
var years = (from q in list select q.Years).Distinct().OrderByDescending(c => c);
foreach (var year in years)
{
//月份
var monthData = list.Where(c => c.Years == year).Select(c => c.Months).Distinct().OrderByDescending(c => c);
foreach (var month in monthData)
{
//根据 年、月查询
var itemTmp = list.Where(c => c.Years == year && c.Months == month);
InsuranceCombineViewModel model = new InsuranceCombineViewModel()
{
DisplayTime = itemTmp.FirstOrDefault().TimeStr,
DisplayCopies = itemTmp.Sum(c => c.TotalCount),
DisplayMoney = itemTmp.Sum(c => c.Money),
Data = new List<InsuranceCombineViewModelByDate>()
};
foreach (var item in itemTmp)
{
model.Data.Add(new InsuranceCombineViewModelByDate()
{
BranchName = item.BranchName,
Copies = item.TotalCount,
Money = item.Money
});
}
result.DataByDate.Add(model);
}
}
result.DisplaySumMoney = result.DataByDate.Sum(c => c.DisplayMoney);
#endregion
return result;
}