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;
}
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;
}
/// <summary>
/// 保险对账
/// 统计方式：'专线'
/// </summary>
/// <param name="queryCond"></param>
/// <returns></returns>
public CustomPagedList<InsuranceCombineBasicViewModel> CombineListBySpecial(PagedParam<InsuranceCombineQuery> queryCond)
{
this.ValidateUser(queryCond);
if (queryCond.QueryDto.DisplayType != InsuranceCombineDisplayType.Special)
throw new DataValidationException("统计方式错误");
//排序
List<OrderDto> order = new List<OrderDto>();
order.Add(new OrderDto() { OrderField = "BuyTime", OrderSort = "desc" });
queryCond.OrderListDto = order;
var tmp = this._insuranceRepository.GetModel().Include(c => c.Buyer)
.GetPagedList(queryCond.PageDto.PageNo, queryCond.PageDto.PageSize, (e) =>
{
if (queryCond.QueryDto.StartDate.HasValue && queryCond.QueryDto.EndDate.HasValue)
{
e = e.Where(c => c.BuyTime >= queryCond.QueryDto.StartDate && c.BuyTime <= queryCond.QueryDto.EndDate);
}
else
{
if (queryCond.QueryDto.StartDate.HasValue)
e = e.Where(c => c.BuyTime >= queryCond.QueryDto.StartDate);
if (queryCond.QueryDto.EndDate.HasValue)
e = e.Where(c => c.BuyTime <= queryCond.QueryDto.EndDate);
}
if (!string.IsNullOrWhiteSpace(queryCond.QueryDto.BranchID))
{
var arrB = Array.ConvertAll<string, string>(queryCond.QueryDto.BranchID.Split(','), x => x);
e = e.Where(c => arrB.Contains(c.BranchId.ToString()));
}
return e;
},
queryCond.SortField).ToCustomPagedList<Insurance, InsuranceCombineBasicViewModel>((u) =>
{
var s = new InsuranceCombineBasicViewModel()
{
DisplayType = InsuranceCombineDisplayType.Special
};
s.DataBySpecial = new List<InsuranceCombineViewModelBySpecial>();
s.DataBySpecial.Add(new InsuranceCombineViewModelBySpecial()
{
ID = u.ID,
BranchName = u.BranchName,
SpecialName = u.SpecialName,
Money = u.Money,
BuyName = u.Buyer.Name,
BuyTime = u.BuyTime,
InsuranceNumber = u.InsuranceNumber,
InsuranceUrl = u.InsuranceUrl
});
s.DisplaySumMoney = s.DataBySpecial.Sum(c => c.Money);
return s;
});
return tmp;
}