protected void Page_Load(object sender, EventArgs e) { ticketmodel = new EyouSoft.Model.StatisticStructure.TicketOutStatisticOffice(); TicketBll = new EyouSoft.BLL.StatisticStructure.TicketOutStatistic(SiteUserInfo); //权限判断 if (!CheckGrant(global::Common.Enum.TravelPermission.机票管理_出票统计_栏目)) { EyouSoft.Common.Utils.ResponseNoPermit(global::Common.Enum.TravelPermission.机票管理_出票统计_栏目, false); return; } if (!IsPostBack) { BindAirlines(); BindList(); if (EyouSoft.Common.Utils.GetQueryStringValue("action") == "toexcel") { CreateExcel("TicketOutNum" + DateTime.Now.ToShortDateString()); } } }
/// <summary> /// 获取出票--售票处统计 /// </summary> /// <param name="model">出票统计查询实体</param> /// <param name="HaveUserIds">用户Id集合,半角逗号分割</param> /// <returns></returns> public IList <EyouSoft.Model.StatisticStructure.TicketOutStatisticOffice> GetTicketOutStatisticOffice(EyouSoft.Model.StatisticStructure.QueryTicketOutStatisti model, string HaveUserIds) { if (model == null) { return(null); } IList <EyouSoft.Model.StatisticStructure.TicketOutStatisticOffice> list = new List <EyouSoft.Model.StatisticStructure.TicketOutStatisticOffice>(); EyouSoft.Model.StatisticStructure.TicketOutStatisticOffice tmpModel = null; var strSql = new StringBuilder(); var strWhere = new StringBuilder(); #region Sql strWhere.AppendFormat(" A.[State] = {0} ", (int)Model.EnumType.PlanStructure.TicketState.已出票); //供应商被删除,按售票处统计不统计,其他统计 strWhere.Append(" and exists (select 1 from tbl_CompanySupplier where tbl_CompanySupplier.Id = A.TicketOfficeId and tbl_CompanySupplier.IsDelete = '0') "); if (model.CompanyId > 0) { strWhere.AppendFormat(" and A.CompanyID = {0} ", model.CompanyId); } if ((model.DepartIds != null && model.DepartIds.Length > 0) || !string.IsNullOrEmpty(model.DepartName)) { strWhere.Append(" and exists (select 1 from tbl_CompanyUser where tbl_CompanyUser.ID = A.RegisterOperatorId "); if (model.DepartIds != null && model.DepartIds.Length > 0) { string strIds = GetIdsByIdArr(model.DepartIds); if (!string.IsNullOrEmpty(strIds)) { strWhere.AppendFormat(" and tbl_CompanyUser.DepartId in ({0}) ", strIds); } } if (!string.IsNullOrEmpty(model.DepartName)) { strWhere.AppendFormat(" and tbl_CompanyUser.DepartName like '%{0}%' ", model.DepartName); } strWhere.Append(" ) "); } if (model.OfficeId > 0) { strWhere.AppendFormat(" and A.TicketOfficeId = {0} ", model.OfficeId); } if (!string.IsNullOrEmpty(model.OfficeName)) { strWhere.AppendFormat(" and A.TicketOffice like '%{0}%' ", model.OfficeName); } if (model.StartTicketOutTime.HasValue) { strWhere.AppendFormat(" and datediff(dd,'{0}',A.TicketOutTime) >= 0 ", model.StartTicketOutTime.Value); } if (model.EndTicketOutTime.HasValue) { strWhere.AppendFormat(" and datediff(dd,A.TicketOutTime,'{0}') >= 0 ", model.EndTicketOutTime.Value); } if (!string.IsNullOrEmpty(HaveUserIds) || model.LeaveDateStart.HasValue || model.LeaveDateEnd.HasValue) { strWhere.Append(" and exists (select 1 from tbl_Tour as T where T.TourId = A.TourId "); if (!string.IsNullOrEmpty(HaveUserIds)) { strWhere.AppendFormat(" and T.OperatorId in ({0}) ", HaveUserIds); } if (model.LeaveDateStart.HasValue) { strWhere.AppendFormat(" and T.LeaveDate >= '{0}' ", model.LeaveDateStart.Value); } if (model.LeaveDateEnd.HasValue) { strWhere.AppendFormat(" and T.LeaveDate <= '{0}' ", model.LeaveDateEnd.Value); } strWhere.Append(" ) "); } if (model.AirLineIds != null && model.AirLineIds.Length > 0) { string strAirIds = GetIdsByIdArr(model.AirLineIds); if (!string.IsNullOrEmpty(strAirIds)) { strWhere.AppendFormat( " and exists (select 1 from tbl_planticketflight as C where C.ticketid = A.Id and C.AireLine in ({0})) ", strAirIds); } } strSql.Append(" select ticketofficeid,TicketOffice,sum(peoplecount) as peoplecount,sum(TotalAmount) as TotalAmount "); strSql.Append(" ,sum(PayAmount) as PayAmount "); strSql.Append(" from ( "); strSql.Append(" select ticketofficeid,TotalAmount,PayAmount "); strSql.Append(" ,(select UnitName from tbl_CompanySupplier where Id = A.TicketOfficeId) as TicketOffice "); strSql.Append( " ,(select isnull(SUM(peoplecount),0) from tbl_planticketkind AS B where A.id = B.ticketid ) as peoplecount "); strSql.Append(" from tbl_planticketout AS A "); strSql.AppendFormat(" where {0} ", strWhere.ToString()); strSql.Append(" ) as pto "); strSql.Append(" group by ticketofficeid,TicketOffice "); switch (model.OrderIndex) { case 0: strSql.Append(" order by TicketOfficeId asc "); break; case 1: strSql.Append(" order by TicketOfficeId desc "); break; default: strSql.Append(" order by TicketOfficeId asc "); break; } #endregion DbCommand dc = _db.GetSqlStringCommand(strSql.ToString()); #region 实体赋值 using (IDataReader dr = DbHelper.ExecuteReader(dc, _db)) { while (dr.Read()) { tmpModel = new EyouSoft.Model.StatisticStructure.TicketOutStatisticOffice(); if (!dr.IsDBNull(0)) { tmpModel.OfficeId = dr.GetInt32(0); } if (!dr.IsDBNull(1)) { tmpModel.OfficeName = dr.GetString(1); } if (!dr.IsDBNull(2)) { tmpModel.TicketOutNum = dr.GetInt32(2); } if (!dr.IsDBNull(3)) { tmpModel.TotalAmount = dr.GetDecimal(3); } if (!dr.IsDBNull(4)) { tmpModel.PayAmount = dr.GetDecimal(4); } list.Add(tmpModel); } } #endregion return(list); }