/// <summary> /// 统计中心-获取欠款金额汇总信息(统计分析-支出对账单总支出、已付、未付明细列表合计) /// </summary> /// <param name="searchModel">查询实体</param> /// <param name="totalAmount">总支出</param> /// <param name="arrear">未付</param> /// <returns></returns> public void GetArrearAllMoeny(ArrearSearchInfo searchModel, ref decimal totalAmount, ref decimal arrear) { if (searchModel == null) { return; } searchModel = new ArrearSearchInfo() { AreaId = searchModel.AreaId, CompanyId = searchModel.CompanyId, IsAccount = searchModel.IsAccount, LeaveDate1 = searchModel.LeaveDate1, LeaveDate2 = searchModel.LeaveDate2, OperateId = searchModel.OperateId, SalerId = searchModel.SalerId, SeachType = searchModel.SeachType, SupplierId = searchModel.SupplierId, SupplierName = searchModel.SupplierName, TourType = searchModel.TourType }; if (!searchModel.LeaveDate2.HasValue) { searchModel.LeaveDate2 = new DateTime(DateTime.Now.Year + 1, 1, 1).AddMilliseconds(-1); } else { searchModel.LeaveDate2 = searchModel.LeaveDate2.Value.AddDays(1).AddMilliseconds(-1); } if (!searchModel.LeaveDate1.HasValue) { searchModel.LeaveDate1 = new DateTime(DateTime.Now.Year, 1, 1); } dal.GetArrearAllMoeny(searchModel, HaveUserIds, ref totalAmount, ref arrear); }
/// <summary> /// 统计中心-获取欠款列表(总支出;已付;未付)(统计分析-支出对账单总支出、已付、未付明细列表) /// </summary> /// <param name="SearchModel"></param> /// <returns></returns> public IList <ArrearInfo> GetArrear(EyouSoft.Model.PlanStructure.ArrearSearchInfo SearchModel) { if (SearchModel != null) { SearchModel = new ArrearSearchInfo() { AreaId = SearchModel.AreaId, CompanyId = SearchModel.CompanyId, IsAccount = SearchModel.IsAccount, LeaveDate1 = SearchModel.LeaveDate1, LeaveDate2 = SearchModel.LeaveDate2, OperateId = SearchModel.OperateId, SalerId = SearchModel.SalerId, SeachType = SearchModel.SeachType, SupplierId = SearchModel.SupplierId, SupplierName = SearchModel.SupplierName, TourType = SearchModel.TourType }; if (!SearchModel.LeaveDate2.HasValue) { SearchModel.LeaveDate2 = new DateTime(DateTime.Now.Year + 1, 1, 1).AddMilliseconds(-1); } else { SearchModel.LeaveDate2 = SearchModel.LeaveDate2.Value.AddDays(1).AddMilliseconds(-1); } if (!SearchModel.LeaveDate1.HasValue) { SearchModel.LeaveDate1 = new DateTime(DateTime.Now.Year, 1, 1); } } return(dal.GetArrear(SearchModel, HaveUserIds)); }
/// <summary> /// 统计中心-获取欠款金额汇总信息(统计分析-支出对账单总支出、已付、未付明细列表合计) /// </summary> /// <param name="searchModel">查询实体</param> /// <param name="haveUserIds">用户Ids</param> /// <param name="totalAmount">总支出</param> /// <param name="arrear">未付</param> /// <returns></returns> public void GetArrearAllMoeny(ArrearSearchInfo searchModel, string haveUserIds, ref decimal totalAmount, ref decimal arrear) { if (searchModel.CompanyId <= 0) { return; } totalAmount = 0; arrear = 0; var searchCon = new StringBuilder(); searchCon.AppendFormat("select TotalAmount,Arrear from View_TravelAndTicketArrear where companyid={0} ", searchModel.CompanyId); if (searchModel.SeachType == 1) //总支出 { searchCon.Append(" and TotalAmount <> 0 "); } if (searchModel.SeachType == 2) //已付 { searchCon.Append(" and PayAmount <> 0 "); } if (searchModel.SeachType == 3) //未付 { searchCon.Append(" and arrear <> 0 "); } if (searchModel.LeaveDate1 != null) { searchCon.AppendFormat(" and leaveDate>='{0}' ", searchModel.LeaveDate1.Value); } if (searchModel.LeaveDate1 != null) { searchCon.AppendFormat(" and leaveDate<='{0}' ", searchModel.LeaveDate2.Value); } if (searchModel.OperateId > 0) { searchCon.AppendFormat(" and operateid={0}", searchModel.OperateId); } if (searchModel.SalerId > 0) { searchCon.AppendFormat(" and salerid={0}", searchModel.SalerId); } if (searchModel.AreaId > 0) { searchCon.AppendFormat(" and areaid={0}", searchModel.AreaId); } if (searchModel.TourType > 0) { searchCon.AppendFormat(" and TourType={0}", searchModel.TourType); } if (searchModel.SupplierId != null && searchModel.SupplierId.Length > 0) { string strIds = string.Empty; foreach (int i in searchModel.SupplierId) { if (i > 0) { strIds += i.ToString() + ","; } } strIds = strIds.Trim(','); if (!string.IsNullOrEmpty(strIds)) { searchCon.AppendFormat(" and ServerId in ({0})", strIds); } } if (!string.IsNullOrEmpty(searchModel.SupplierName)) { searchCon.AppendFormat(" and Supplier like '%{0}%' ", searchModel.SupplierName); } if (searchModel.IsAccount.HasValue) { searchCon.Append(searchModel.IsAccount.Value ? " and Arrear = 0 " : " and Arrear <> 0 "); } /*if (!string.IsNullOrEmpty(haveUserIds)) * searchCon.AppendFormat(" and TourOperatorId in ({0}) ", haveUserIds);*/ DbCommand cmd = this._db.GetSqlStringCommand(searchCon.ToString()); using (IDataReader dr = DbHelper.ExecuteReader(cmd, _db)) { while (dr.Read()) { if (!dr.IsDBNull(0)) { totalAmount += dr.GetDecimal(0); } if (!dr.IsDBNull(1)) { arrear += dr.GetDecimal(1); } } } }