//绑定数据
protected void Bind()
{
pageIndex = EyouSoft.Common.Utils.GetInt(Request.QueryString["page"], 1);
EyouSoft.BLL.StatisticStructure.TicketOutStatistic ticketBll = new EyouSoft.BLL.StatisticStructure.TicketOutStatistic(SiteUserInfo);
EyouSoft.Model.StatisticStructure.TicketOutStatisticDepart ticketDepart = new EyouSoft.Model.StatisticStructure.TicketOutStatisticDepart();
EyouSoft.Model.StatisticStructure.QueryTicketOutStatisti queryTicket = new EyouSoft.Model.StatisticStructure.QueryTicketOutStatisti();
int tmpAirLineId = Utils.GetInt(Utils.GetQueryStringValue("areaId"), -1);
if (tmpAirLineId > 0)
{
queryTicket.AirLineIds = new int[1] {
tmpAirLineId
};
}
queryTicket.CompanyId = SiteUserInfo.CompanyID;
queryTicket.OfficeName = Utils.GetQueryStringValue("OfficeName");
queryTicket.StartTicketOutTime = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("DateTime"));
queryTicket.EndTicketOutTime = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("endtime"));
queryTicket.LeaveDateStart = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("leaDateS"));
queryTicket.LeaveDateEnd = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("leaDateE"));
IList <EyouSoft.Model.StatisticStructure.TicketOutStatisticDepart> list = ticketBll.GetTicketOutStatisticDepart(queryTicket);
if (list != null && list.Count > 0)
{
recordCount = list.Count;
retList.DataSource = list.Skip((pageIndex - 1) * pageSize).Take(pageSize);
this.retList.DataBind();
BindPage();
this.lblMsg.Visible = false;
#region 设置总计
//总票数
this.lblAllTickets.Text = list.Sum(p => p.TicketOutNum).ToString();
//应付机票款
this.lblNeedMoney.Text = list.Sum(p => p.TotalAmount).ToString("c2");
//已付机票款
this.lblOverMoney.Text = list.Sum(p => p.PayAmount).ToString("c2");
//未付机票款
this.lblNoMoney.Text = list.Sum(p => p.UnPaidAmount).ToString("c2");
#endregion
}
else
{
this.lblMsg.Visible = true;
this.ExportPageInfo1.Visible = false;
}
this.ddlAirLineIds.SelectedValue = tmpAirLineId.ToString();
if (queryTicket.StartTicketOutTime.HasValue)
{
this.txt_date.Value = queryTicket.StartTicketOutTime.Value.ToShortDateString();
}
if (queryTicket.EndTicketOutTime.HasValue)
{
this.txt_endDate.Value = queryTicket.EndTicketOutTime.Value.ToShortDateString();
}
if (queryTicket.LeaveDateStart.HasValue)
{
txtLeaveDateStart.Text = queryTicket.LeaveDateStart.Value.ToShortDateString();
}
if (queryTicket.LeaveDateEnd.HasValue)
{
txtLeaveDateEnd.Text = queryTicket.LeaveDateEnd.Value.ToShortDateString();
}
this.txt_spq.Value = queryTicket.OfficeName;
}
/// <summary>
/// 获取出票--部门统计
/// </summary>
/// <param name="model">出票统计查询实体</param>
/// <param name="HaveUserIds">用户Id集合，半角逗号分割</param>
/// <returns></returns>
public IList <EyouSoft.Model.StatisticStructure.TicketOutStatisticDepart> GetTicketOutStatisticDepart(EyouSoft.Model.StatisticStructure.QueryTicketOutStatisti model, string HaveUserIds)
{
if (model == null)
{
return(null);
}
IList <EyouSoft.Model.StatisticStructure.TicketOutStatisticDepart> list = new List <EyouSoft.Model.StatisticStructure.TicketOutStatisticDepart>();
EyouSoft.Model.StatisticStructure.TicketOutStatisticDepart tmpModel = null;
StringBuilder strSql = new StringBuilder();
StringBuilder strWhere = new StringBuilder("");
strWhere.AppendFormat(" pto.[State] = {0} ", (int)Model.EnumType.PlanStructure.TicketState.已出票);
#region SqlWhere
if (model.CompanyId > 0)
{
strWhere.AppendFormat(" and pto.CompanyID = {0} ", model.CompanyId);
}
if (model.DepartIds != null && model.DepartIds.Length > 0)
{
strWhere.AppendFormat(" and pto.DepartId in ({0}) ", this.GetIdsByIdArr(model.DepartIds));
}
if (!string.IsNullOrEmpty(model.DepartName))
{
strWhere.AppendFormat(" and pto.DepartName like '%{0}%' ", model.DepartName);
}
if (model.OfficeId > 0)
{
strWhere.AppendFormat(" and pto.TicketOfficeId = {0} ", model.OfficeId);
}
if (!string.IsNullOrEmpty(model.OfficeName))
{
strWhere.AppendFormat(" and pto.TicketOffice like '%{0}%' ", model.OfficeName);
}
if (model.StartTicketOutTime.HasValue)
{
strWhere.AppendFormat(" and datediff(dd,'{0}',pto.TicketOutTime) >= 0 ", model.StartTicketOutTime.Value);
}
if (model.EndTicketOutTime.HasValue)
{
strWhere.AppendFormat(" and datediff(dd,pto.TicketOutTime,'{0}') >= 0 ", model.EndTicketOutTime.Value);
}
if (model.AirLineIds != null && model.AirLineIds.Length > 0)
{
strWhere.AppendFormat(" and exists (select 1 from tbl_PlanTicketFlight where tbl_PlanTicketFlight.TicketId in (select distinct ID from View_TicketOutStatisticDepart where View_TicketOutStatisticDepart.DepartId = pto.DepartId and {0}) ", strWhere.ToString().Replace("pto.", "View_TicketOutStatisticDepart."));
if (model.AirLineIds != null && model.AirLineIds.Length > 0)
{
strWhere.AppendFormat(" and tbl_PlanTicketFlight.AireLine in ({0}) ", this.GetIdsByIdArr(model.AirLineIds));
}
strWhere.Append(") ");
}
if (model.LeaveDateStart.HasValue || model.LeaveDateEnd.HasValue || (!string.IsNullOrEmpty(HaveUserIds)))
{
strWhere.Append(" and exists (select 1 from tbl_Tour where tbl_Tour.TourId = pto.TourId ");
if (!string.IsNullOrEmpty(HaveUserIds))
{
strWhere.AppendFormat(" and tbl_Tour.OperatorId in ({0}) ", HaveUserIds);
}
if (model.LeaveDateStart.HasValue)
{
strWhere.AppendFormat(" and tbl_Tour.LeaveDate >= '{0}' ", model.LeaveDateStart.Value);
}
if (model.LeaveDateEnd.HasValue)
{
strWhere.AppendFormat(" and tbl_Tour.LeaveDate <= '{0}' ", model.LeaveDateEnd.Value);
}
strWhere.Append(" ) ");
}
#endregion
#region Sql
strSql.Append(" select DepartId,DepartName ");
strSql.Append(" ,sum(PeopleCount) as PeopleCount,sum(TotalAmount) as TotalAmount,sum(PayAmount) as PayAmount ");
strSql.Append(" from View_TicketOutStatisticDepart as pto ");
strSql.AppendFormat(" where {0} ", strWhere.ToString());
strSql.Append(" group by DepartId,DepartName ");
switch (model.OrderIndex)
{
case 4:
strSql.Append(" order by DepartId asc ");
break;
case 5:
strSql.Append(" order by DepartId desc ");
break;
default:
strSql.Append(" order by DepartId 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.TicketOutStatisticDepart();
if (!dr.IsDBNull(0))
{
tmpModel.DepartId = dr.GetInt32(0);
}
if (!dr.IsDBNull(1))
{
tmpModel.DepartName = 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);
}