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;
        }