protected void DataInit(string tourNo, string routeName, DateTime?outStartDate, DateTime?outEndDate, int airCompany , int supplieriId, string supplierName, string airSegment, string strDepIds, string strOpIds) { EyouSoft.Model.StatisticStructure.MQueryRefundStatistic searchModel = new EyouSoft.Model.StatisticStructure.MQueryRefundStatistic(); if (airCompany == 0) { searchModel.AireLine = null; } else { searchModel.AireLine = (EyouSoft.Model.EnumType.PlanStructure.FlightCompany)airCompany; } searchModel.BuyCompanyId = supplieriId; searchModel.BuyCompanyName = supplierName; searchModel.CompanyId = SiteUserInfo.CompanyID; searchModel.FligthSegment = airSegment; searchModel.LeaveDateEnd = outEndDate; searchModel.LeaveDateStart = outStartDate; searchModel.RouteName = routeName; searchModel.TourNo = tourNo; searchModel.DepIds = GetIntArrByStr(strDepIds); searchModel.OperatorIds = GetIntArrByStr(strOpIds); IList <EyouSoft.Model.StatisticStructure.MRefundStatistic> list = new EyouSoft.BLL.StatisticStructure.BRefundStatistic(SiteUserInfo, true).GetRefundStatistic(pageSize, pageIndex, ref recordCount, searchModel); if (list != null && list.Count > 0) { //计算 new EyouSoft.BLL.StatisticStructure.BRefundStatistic(SiteUserInfo, true).GetSumRefundStatistic( ref RefundAmount, searchModel); this.rptList.DataSource = list; this.rptList.DataBind(); this.lblMsg.Visible = false; } else { RefundAmount = 0M; this.lblMsg.Visible = true; this.ExporPageInfoSelect1.Visible = false; } BindPage(); }
/// <summary> /// 获取退票统计 /// </summary> /// <param name="PageSize">每页条数</param> /// <param name="PageIndex">当前页数</param> /// <param name="RecordCount">总记录数</param> /// <param name="model">查询实体</param> /// <param name="us">用户信息集合</param> /// <returns></returns> public IList <EyouSoft.Model.StatisticStructure.MRefundStatistic> GetRefundStatistic(int PageSize, int PageIndex, ref int RecordCount , EyouSoft.Model.StatisticStructure.MQueryRefundStatistic model, string us) { if (model == null || model.CompanyId <= 0) { return(null); } IList <EyouSoft.Model.StatisticStructure.MRefundStatistic> list = null; EyouSoft.Model.StatisticStructure.MRefundStatistic TMPModel = null; string strFile = " Id,CustormerId,OperatorName,VisitorName,TourNo,RouteName,LeaveDate,BuyCompanyName,OrderOperatorName,TotalAmount,FligthInfo,RefundAmount "; string strPK = "Id"; string strTableName = "View_RefundStatistics"; string strOrderBy = " TourNo asc "; string strWhere = this.GetSqlWhereByQueryModel(model, us); switch (model.OrderIndex) { case 0: strOrderBy = " TourNo asc "; break; case 1: strOrderBy = " TourNo desc "; break; case 2: strOrderBy = " IssueTime asc "; break; case 3: strOrderBy = " IssueTime desc "; break; } using (IDataReader dr = DbHelper.ExecuteReader(this._db, PageSize, PageIndex, ref RecordCount, strTableName, strPK, strFile, strWhere, strOrderBy)) { list = new List <EyouSoft.Model.StatisticStructure.MRefundStatistic>(); while (dr.Read()) { //Id,CustormerId,OperatorName,VisitorName,TourNo,RouteName,LeaveDate,BuyCompanyName,OrderOperatorName,TotalAmount,FligthInfo,RefundAmount TMPModel = new EyouSoft.Model.StatisticStructure.MRefundStatistic(); if (!dr.IsDBNull(0)) { TMPModel.RefundId = dr.GetString(0); } if (!dr.IsDBNull(1)) { TMPModel.CustomerId = dr.GetString(1); } if (!dr.IsDBNull(2)) { TMPModel.OperatorName = dr.GetString(2); } if (!dr.IsDBNull(3)) { TMPModel.RefundName = dr.GetString(3); } if (!dr.IsDBNull(4)) { TMPModel.TourNo = dr.GetString(4); } if (!dr.IsDBNull(5)) { TMPModel.RouteName = dr.GetString(5); } if (!dr.IsDBNull(6)) { TMPModel.LeaveDate = dr.GetDateTime(6); } if (!dr.IsDBNull(7)) { TMPModel.BuyCompanyName = dr.GetString(7); } if (!dr.IsDBNull(8)) { TMPModel.BuyOrderOperatorName = dr.GetString(8); } if (!dr.IsDBNull(9)) { TMPModel.TicketPrice = dr.GetDecimal(9); } if (!dr.IsDBNull(10)) { TMPModel.RefundFlight = this.GetTicketFlightXML(dr.GetString(10)); } if (!dr.IsDBNull(11)) { TMPModel.RefundAmount = dr.GetDecimal(11); } list.Add(TMPModel); } } return(list); }
/// <summary> /// 根据查询实体生成SqlWhere语句 /// </summary> /// <param name="model">查询实体</param> /// <param name="us">用户信息集合</param> /// <returns>SqlWhere语句</returns> private string GetSqlWhereByQueryModel(EyouSoft.Model.StatisticStructure.MQueryRefundStatistic model, string us) { if (model == null || model.CompanyId <= 0) { return(string.Empty); } StringBuilder strWhere = new StringBuilder(); strWhere.AppendFormat(" SellCompanyId = {0} ", model.CompanyId); if (!string.IsNullOrEmpty(model.TourNo)) { strWhere.AppendFormat(" and TourNo like '%{0}%' ", model.TourNo); } if (!string.IsNullOrEmpty(model.RouteName)) { strWhere.AppendFormat(" and RouteName like '%{0}%' ", model.RouteName); } if (model.LeaveDateStart.HasValue) { strWhere.AppendFormat(" and datediff(dd,'{0}',LeaveDate) >= 0 ", model.LeaveDateStart.Value.ToShortDateString()); } if (model.LeaveDateEnd.HasValue) { strWhere.AppendFormat(" and datediff(dd,LeaveDate,'{0}') >= 0 ", model.LeaveDateEnd.Value.ToShortDateString()); } if (!string.IsNullOrEmpty(model.FligthSegment) || model.AireLine.HasValue) { strWhere.Append(" and exists (select 1 from tbl_CustomerRefundFlight where tbl_CustomerRefundFlight.RefundId = ID and exists (select 1 from tbl_PlanTicketFlight where tbl_PlanTicketFlight.ID = tbl_CustomerRefundFlight.FlightId "); if (!string.IsNullOrEmpty(model.FligthSegment)) { strWhere.AppendFormat(" and tbl_PlanTicketFlight.FligthSegment like '%{0}%' ", model.FligthSegment); } if (model.AireLine.HasValue) { strWhere.AppendFormat(" and tbl_PlanTicketFlight.AireLine = {0} ", (int)model.AireLine.Value); } strWhere.Append(" )) "); } if (!string.IsNullOrEmpty(model.BuyCompanyName)) { strWhere.AppendFormat(" and BuyCompanyName like '%{0}%' ", model.BuyCompanyName); } if (model.BuyCompanyId > 0) { strWhere.AppendFormat(" and BuyCompanyID = {0} ", model.BuyCompanyId); } if (!string.IsNullOrEmpty(us)) { strWhere.AppendFormat(" AND TourOperatorId IN({0}) ", us); } var opIds = new List <int>(); if (model.OperatorIds != null && model.OperatorIds.Length > 0) { foreach (var i in model.OperatorIds) { if (i <= 0) { continue; } if (opIds.Contains(i)) { continue; } opIds.Add(i); } } if (model.DepIds != null && model.DepIds.Length > 0) { int[] userIds = new DAL.CompanyStructure.CompanyUser().GetUserIdsByDepartIds(model.DepIds); if (userIds != null && userIds.Length > 0) { foreach (var i in userIds) { if (i <= 0) { continue; } if (opIds.Contains(i)) { continue; } opIds.Add(i); } } } if (opIds != null && opIds.Count > 0) { string strIds = string.Empty; foreach (int i in opIds) { if (i <= 0) { continue; } strIds += i + ","; } strIds = strIds.Trim(','); if (!string.IsNullOrEmpty(strIds)) { strWhere.AppendFormat(" and OperatorId in ({0}) ", strIds); } } return(strWhere.ToString()); }