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