IList <EyouSoft.Model.StatisticStructure.TicketOutStatisticAirLine> list = null;//未收集合
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (!CheckGrant(TravelPermission.机票管理_机票管理_出票统计))
{
Utils.ResponseNoPermit(TravelPermission.机票管理_机票管理_出票统计, false);
}
Bind();
#region 导出报表请求
if (Utils.GetInt(Request.QueryString["isExport"], 0) == 1)
{
var bll = new EyouSoft.BLL.StatisticStructure.TicketOutStatistic(SiteUserInfo);
var model = new EyouSoft.Model.StatisticStructure.QueryTicketOutStatisti();
model.OfficeName = Utils.GetQueryStringValue("OfficeName");
model.CompanyId = SiteUserInfo.CompanyID;
model.DepartName = Utils.GetQueryStringValue("DepartMents");
string strDepIds = Utils.GetQueryStringValue("DepartIds");
model.DepartIds = JiPiao_TuiList.GetIntArrByStr(strDepIds);
model.StartTicketOutTime = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("DateTime"));
model.EndTicketOutTime = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("endtime"));
model.LeaveDateStart = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("leaDateS"));
model.LeaveDateEnd = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("leaDateE"));
list = bll.GetTicketOutStatisticAirLine(model);
if (list != null && list.Count != 0)
{
ToExcel(this.rpt_list, list);
}
}
#endregion
}
}
void Bind()
{
pageIndex = EyouSoft.Common.Utils.GetInt(Request.QueryString["page"], 1);
EyouSoft.BLL.StatisticStructure.TicketOutStatistic bll = new EyouSoft.BLL.StatisticStructure.TicketOutStatistic(SiteUserInfo);
EyouSoft.Model.StatisticStructure.QueryTicketOutStatisti model = new EyouSoft.Model.StatisticStructure.QueryTicketOutStatisti();
model.OfficeName = Utils.GetQueryStringValue("OfficeName");
model.CompanyId = SiteUserInfo.CompanyID;
model.DepartName = Utils.GetQueryStringValue("DepartMents");
string strDepIds = Utils.GetQueryStringValue("DepartIds");
model.DepartIds = JiPiao_TuiList.GetIntArrByStr(strDepIds);
model.StartTicketOutTime = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("DateTime"));
model.EndTicketOutTime = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("endtime"));
model.LeaveDateStart = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("leaDateS"));
model.LeaveDateEnd = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("leaDateE"));
list = bll.GetTicketOutStatisticAirLine(model);
if (list != null && list.Count > 0)
{
recordCount = list.Count;
rpt_list.DataSource = list.Skip((pageIndex - 1) * pageSize).Take(pageSize);
rpt_list.DataBind();
#region 设置总计
//总票数
this.lblAllTickets = list.Sum(p => p.TicketOutNum).ToString();
//应付机票款
this.lblNeedMoney = list.Sum(p => p.TotalAmount).ToString("￥#,###0.00");
//已付机票款
this.lblOverMoney = list.Sum(p => p.PayAmount).ToString("￥#,###0.00");
//未付机票款
this.lblNoMoney = list.Sum(p => p.UnPaidAmount).ToString("￥#,###0.00");
#endregion
#region 设置分页
ExportPageInfo1.intPageSize = pageSize;
ExportPageInfo1.intRecordCount = recordCount;
ExportPageInfo1.PageLinkURL = Request.Path + "?";
ExportPageInfo1.UrlParams = Request.QueryString;
ExportPageInfo1.CurrencyPage = pageIndex;
#endregion
}
else
{
//没有数据隐藏控件
ExportPageInfo1.Visible = false;
}
UCselectDepart.GetDepartmentName = model.DepartName;
UCselectDepart.GetDepartId = strDepIds;
txt_date.Value = model.StartTicketOutTime.HasValue ? model.StartTicketOutTime.Value.ToString("yyyy-MM-dd") : "";
txt_endDate.Value = model.EndTicketOutTime.HasValue ? model.EndTicketOutTime.Value.ToString("yyyy-MM-dd") : "";
txt_spq.Value = model.OfficeName;
txtLeaveDateStart.Text = model.LeaveDateStart.HasValue ? model.LeaveDateStart.Value.ToString("yyyy-MM-dd") : string.Empty;
txtLeaveDateEnd.Text = model.LeaveDateEnd.HasValue ? model.LeaveDateEnd.Value.ToString("yyyy-MM-dd") : string.Empty;
}
/// <summary>
/// 导出Excel
/// </summary>
public void toExcel(string FileName)
{
EyouSoft.SSOComponent.Entity.UserInfo UserModel = new EyouSoft.SSOComponent.Entity.UserInfo();
UserModel.CompanyID = SiteUserInfo.CompanyID;
EyouSoft.Model.StatisticStructure.QueryTicketOutStatisti ssModel = new EyouSoft.Model.StatisticStructure.QueryTicketOutStatisti();
//查询条件
ssModel.CompanyId = SiteUserInfo.CompanyID;
string departId = Utils.GetQueryStringValue("DepartIds");
string OfficeName = Utils.GetQueryStringValue("OfficeName");//获取航空公司查询条件
int[] AirLineIds = new int[1];
AirLineIds[0] = Utils.GetInt(Utils.GetQueryStringValue("areaId"));//获取售票处查询条件
if (AirLineIds[0] > 0)
{
ssModel.AirLineIds = AirLineIds;
}
ssModel.DepartIds = JiPiao_TuiList.GetIntArrByStr(departId);
ssModel.OfficeName = OfficeName;
ssModel.LeaveDateStart = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("leaDateS"));
ssModel.LeaveDateEnd = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("leaDateE"));
EyouSoft.BLL.StatisticStructure.TicketOutStatistic ssBLL = new EyouSoft.BLL.StatisticStructure.TicketOutStatistic(UserModel);
IList <EyouSoft.Model.StatisticStructure.TicketOutStatisticTime> ssList = null;
ssList = ssBLL.GetTicketOutStatisticTime(ssModel);
Response.Clear();
Response.AppendHeader("Content-Disposition", "attachment;filename=" + FileName + ".xls");
Response.ContentEncoding = System.Text.Encoding.Default;
Response.ContentType = "application/ms-excel";
//取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符
StringBuilder sb = new StringBuilder();
sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}\n", "月份", "出票量", "应付机票款", "已付机票款", "未付机票款");
foreach (EyouSoft.Model.StatisticStructure.TicketOutStatisticTime cs in ssList)
{
sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}\n",
cs.CurrYear + "年" + cs.CurrMonth + "月",
cs.TicketOutNum + "(张)",
cs.TotalAmount.ToString("￥#,##0.00 "),
cs.PayAmount.ToString("￥#,##0.00 "),
cs.UnPaidAmount.ToString("￥#,##0.00 "));
}
sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}\n", "总计", lblAllTickets.Text + "(张)", lblNeedMoney.Text, lblOverMoney.Text, lblNoMoney.Text);
Response.Write(sb.ToString());
Response.End();
}
/// <summary>
/// 导出Excel
/// </summary>
public void CreateExcel(string FileName)
{
AirlinesValue = EyouSoft.Common.Utils.GetInt(EyouSoft.Common.Utils.GetQueryStringValue("AirlinesValue"));
string DepartId = EyouSoft.Common.Utils.GetQueryStringValue("DepartIds");
Datetime = EyouSoft.Common.Utils.GetDateTimeNullable(EyouSoft.Common.Utils.GetQueryStringValue("DateTime"));
ticketendtime = EyouSoft.Common.Utils.GetDateTimeNullable(EyouSoft.Common.Utils.GetQueryStringValue("endtime"));
LeaDateS = EyouSoft.Common.Utils.GetDateTimeNullable(EyouSoft.Common.Utils.GetQueryStringValue("leaDateS"));
LeaDateE = EyouSoft.Common.Utils.GetDateTimeNullable(EyouSoft.Common.Utils.GetQueryStringValue("leaDateE"));
//列表数据绑定
Response.Clear();
Response.AppendHeader("Content-Disposition", "attachment;filename=" + FileName + ".xls");
Response.ContentEncoding = System.Text.Encoding.Default;
Response.ContentType = "application/ms-excel";
EyouSoft.Model.StatisticStructure.QueryTicketOutStatisti QueryTicket = new EyouSoft.Model.StatisticStructure.QueryTicketOutStatisti();
QueryTicket.CompanyId = SiteUserInfo.CompanyID;
if (AirlinesValue > 0)
{
QueryTicket.AirLineIds = new int[1] {
AirlinesValue
}
}
;
QueryTicket.DepartIds = JiPiao_TuiList.GetIntArrByStr(DepartId);
QueryTicket.EndTicketOutTime = ticketendtime;
QueryTicket.StartTicketOutTime = Datetime;
QueryTicket.LeaveDateStart = LeaDateS;
QueryTicket.LeaveDateEnd = LeaDateE;
IList <EyouSoft.Model.StatisticStructure.TicketOutStatisticOffice> ticketlist = ticketlist = TicketBll.GetTicketOutStatisticOffice(QueryTicket);
if (ticketlist != null && ticketlist.Count > 0)
{
//取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}\n", "售票处", "出票量", "应付机票款", "已付机票款", "未付机票款");
foreach (EyouSoft.Model.StatisticStructure.TicketOutStatisticOffice sh in ticketlist)
{
sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}\n", sh.OfficeName, sh.TicketOutNum, "￥" + sh.TotalAmount, "￥" + sh.PayAmount, "￥" + sh.UnPaidAmount);
}
sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}\n", "总计", ticketlist.Sum(p => p.TicketOutNum).ToString(), "￥" + ticketlist.Sum(p => p.TotalAmount).ToString(), "￥" + ticketlist.Sum(p => p.PayAmount).ToString(), "￥" + ticketlist.Sum(p => p.UnPaidAmount).ToString());
Response.Write(sb.ToString());
Response.End();
}
}
/// <summary>
/// 初始化方法
/// </summary>
/// <param name="id"></param>
/// <param name="departIds"></param>
/// <param name="areaIds"></param>
/// <param name="beginDate"></param>
/// <param name="endDate"></param>
/// <param name="leaDateStart"></param>
/// <param name="leaDateEnd"></param>
/// <param name="officeName"></param>
protected void DataInit(int id, string departIds, string areaIds, DateTime?beginDate, DateTime?endDate, DateTime?leaDateStart
, DateTime?leaDateEnd, string officeName)
{
//部门ID 的集合
int[] departIdList = JiPiao_TuiList.GetIntArrByStr(departIds);
//航空公司 ID 集合
int[] areaIdList = JiPiao_TuiList.GetIntArrByStr(areaIds);
//声明查询Model
EyouSoft.Model.StatisticStructure.QueryTicketOutStatisti searchModel = new EyouSoft.Model.StatisticStructure.QueryTicketOutStatisti();
//查询Model 赋值
searchModel.CompanyId = SiteUserInfo.CompanyID;
searchModel.DepartIds = departIdList;
searchModel.AirLineIds = areaIdList;
searchModel.StartTicketOutTime = beginDate;
searchModel.EndTicketOutTime = endDate;
searchModel.OfficeId = id;
searchModel.LeaveDateStart = leaDateStart;
searchModel.LeaveDateEnd = leaDateEnd;
searchModel.OfficeName = officeName;
//声明机票 bll 操作对象
EyouSoft.BLL.PlanStruture.PlaneTicket bll = new EyouSoft.BLL.PlanStruture.PlaneTicket(SiteUserInfo);
//声明出票量集合对象
IList <EyouSoft.Model.PlanStructure.TicketOutStatisticInfo> list = bll.GetTicketOutStatisticList(pageSize, pageIndex, ref recordCount, searchModel);
//判断list内数据条数大于0
if (list != null && list.Count > 0)
{
//绑定控件
this.rptList.DataSource = list;
this.rptList.DataBind();
//设置分页
BindPage();
//隐藏提示
this.lblMsg.Visible = false;
}
else
{
//隐藏分页
this.ExportPageInfo1.Visible = false;
//显示提示
this.lblMsg.Visible = true;
}
}
//导出
protected void ToExcel(string title)
{
EyouSoft.BLL.StatisticStructure.TicketOutStatistic ticketBll = new EyouSoft.BLL.StatisticStructure.TicketOutStatistic(SiteUserInfo);
IList <EyouSoft.Model.StatisticStructure.TicketOutStatisticDepart> list = null;
EyouSoft.Model.StatisticStructure.QueryTicketOutStatisti searchInfo = new EyouSoft.Model.StatisticStructure.QueryTicketOutStatisti();
int tmpAirLineId = Utils.GetInt(Utils.GetQueryStringValue("areaId"), -1);
if (tmpAirLineId > 0)
{
searchInfo.AirLineIds = new int[1] {
tmpAirLineId
};
}
searchInfo.CompanyId = SiteUserInfo.CompanyID;
searchInfo.OfficeName = Utils.GetQueryStringValue("OfficeName");
searchInfo.StartTicketOutTime = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("DateTime"));
searchInfo.EndTicketOutTime = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("endTime"));
searchInfo.LeaveDateStart = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("leaDateS"));
searchInfo.LeaveDateEnd = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("leaDateE"));
//用gerList方法取得总记录的条数
list = ticketBll.GetTicketOutStatisticDepart(searchInfo);
Response.Clear();
Response.AppendHeader("Content-Disposition", "attachment;filename=" + title + ".xls");
Response.ContentEncoding = System.Text.Encoding.Default;
Response.ContentType = "application/ms-excel";
//取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符
StringBuilder sb = new StringBuilder();
sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}\n", "部门", "出票量", "应付机票款", "已付机票款", "未付机票款");
foreach (EyouSoft.Model.StatisticStructure.TicketOutStatisticDepart ticket in list)
{
sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}\n",
ticket.DepartName,
ticket.TicketOutNum,
"￥" + ticket.TotalAmount,
"￥" + ticket.PayAmount,
"￥" + ticket.UnPaidAmount);
}
sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}\n", "总计", list.Sum(p => p.TicketOutNum).ToString(), list.Sum(p => p.TotalAmount).ToString("c2"), list.Sum(p => p.PayAmount).ToString("c2"), list.Sum(p => p.UnPaidAmount).ToString("c2"));
Response.Write(sb.ToString());
Response.End();
}
//绑定数据
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.TicketOutStatisticTime> GetTicketOutStatisticTime(EyouSoft.Model.StatisticStructure.QueryTicketOutStatisti model, string HaveUserIds)
{
if (model == null)
{
return(null);
}
IList <EyouSoft.Model.StatisticStructure.TicketOutStatisticTime> list = new List <EyouSoft.Model.StatisticStructure.TicketOutStatisticTime>();
EyouSoft.Model.StatisticStructure.TicketOutStatisticTime 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 year(View_TicketOutStatisticDepart.TicketOutTime) = year(pto.TicketOutTime) and month(View_TicketOutStatisticDepart.TicketOutTime) = month(pto.TicketOutTime) 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 year(TicketOutTime) as CurrYear,month(TicketOutTime) as CurrMonth ");
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 year(TicketOutTime),month(TicketOutTime) ");
switch (model.OrderIndex)
{
case 4:
strSql.Append(" order by CurrYear asc,CurrMonth asc ");
break;
case 5:
strSql.Append(" order by CurrYear desc,CurrMonth desc ");
break;
default:
strSql.Append(" order by CurrYear asc,CurrMonth 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.TicketOutStatisticTime();
if (!dr.IsDBNull(0))
{
tmpModel.CurrYear = dr.GetInt32(0);
}
if (!dr.IsDBNull(1))
{
tmpModel.CurrMonth = dr.GetInt32(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);
}
/// <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);
}
/// <summary>
/// 获取出票--航空公司统计
/// </summary>
/// <param name="model">出票统计查询实体</param>
/// <param name="HaveUserIds">用户Id集合，半角逗号分割</param>
/// <returns></returns>
public IList <EyouSoft.Model.StatisticStructure.TicketOutStatisticAirLine> GetTicketOutStatisticAirLine(EyouSoft.Model.StatisticStructure.QueryTicketOutStatisti model, string HaveUserIds)
{
if (model == null)
{
return(null);
}
IList <EyouSoft.Model.StatisticStructure.TicketOutStatisticAirLine> list = new List <EyouSoft.Model.StatisticStructure.TicketOutStatisticAirLine>();
EyouSoft.Model.StatisticStructure.TicketOutStatisticAirLine tmpModel = null;
StringBuilder strSql = new StringBuilder();
StringBuilder strWhere = new StringBuilder();
#region Sql
strWhere.AppendFormat(" C.[State] = {0} ", (int)Model.EnumType.PlanStructure.TicketState.已出票);
if (model.CompanyId > 0)
{
strWhere.AppendFormat(" and C.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 = C.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 C.TicketOfficeId = {0} ", model.OfficeId);
}
if (!string.IsNullOrEmpty(model.OfficeName))
{
strWhere.AppendFormat(" and C.TicketOffice like '%{0}%' ", model.OfficeName);
}
if (model.StartTicketOutTime.HasValue)
{
strWhere.AppendFormat(" and datediff(dd,'{0}',C.TicketOutTime) >= 0 ", model.StartTicketOutTime.Value);
}
if (model.EndTicketOutTime.HasValue)
{
strWhere.AppendFormat(" and datediff(dd,C.TicketOutTime,'{0}') >= 0 ", model.EndTicketOutTime.Value);
}
if (model.AirLineIds != null && model.AirLineIds.Length > 0)
{
string strAirIds = GetIdsByIdArr(model.AirLineIds);
if (!string.IsNullOrEmpty(strAirIds))
{
strWhere.AppendFormat(" and B.AireLine in ({0}) ", strAirIds);
}
}
if (!string.IsNullOrEmpty(HaveUserIds) || model.LeaveDateStart.HasValue || model.LeaveDateEnd.HasValue)
{
strWhere.Append(" and exists (select 1 from tbl_Tour as T where T.TourId = C.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(" ) ");
}
/*
* 说明：按照航空公司统计，如果一个机票申请有两个航段的话，会导致机票款和人数统计多次
* 即分别统计到不同的航空公司头上；所以统计出两个值,有case语句的是将机票款和人数统计到机票申请的第一个航段的航空公司上面
* 没有case语句的是重复统计的值（每个航段的航空公司都加了机票申请的机票款和人数）
*/
strSql.Append(" select AireLine ");
strSql.Append(" ,Sum(case rownumber when 1 then TotalAmount else 0 end) as TotalAmount ");
strSql.Append(" ,Sum(case rownumber when 1 then PayAmount else 0 end) as PayAmount ");
strSql.Append(" ,Sum(case rownumber when 1 then peoplecount else 0 end) as peoplecount ");
strSql.Append(" ,sum(TotalAmount) as TotalAmountAll ");
strSql.Append(" ,sum(PayAmount) as PayAmount ");
strSql.Append(" ,sum(peoplecount) as peoplecount ");
strSql.Append(" from ( ");
strSql.Append(" select AireLine,C.ID,C.TotalAmount,C.PayAmount ");
strSql.Append(" ,ROW_NUMBER() OVER(partition by C.ID order by C.ID asc) as rownumber ");
strSql.Append(" ,(select isnull(SUM(peoplecount),0) from tbl_planticketkind AS B where C.id = B.ticketid ) as peoplecount ");
strSql.Append(" from tbl_PlanTicketFlight as B inner join tbl_planticketout as C on B.ticketid = C.Id ");
strSql.AppendFormat(" where {0} ", strWhere.ToString());
strSql.Append(" ) as A ");
strSql.Append(" group by AireLine ");
#endregion
DbCommand dc = _db.GetSqlStringCommand(strSql.ToString());
#region 实体赋值
using (IDataReader dr = DbHelper.ExecuteReader(dc, _db))
{
while (dr.Read())
{
tmpModel = new EyouSoft.Model.StatisticStructure.TicketOutStatisticAirLine();
if (!dr.IsDBNull(0))
{
tmpModel.AirLineId = dr.GetByte(0);
}
if (!dr.IsDBNull(1))
{
tmpModel.TotalAmount = dr.GetDecimal(1);
}
if (!dr.IsDBNull(2))
{
tmpModel.PayAmount = dr.GetDecimal(2);
}
if (!dr.IsDBNull(3))
{
tmpModel.TicketOutNum = dr.GetInt32(3);
}
list.Add(tmpModel);
}
}
#endregion
return(list);
}
/// <summary>
/// 绑定列表
/// </summary>
private void DataBindLs()
{
//获取显示页数
pageIndex = Utils.GetInt(Utils.GetQueryStringValue("page"), 1);
//用户信息实体
EyouSoft.SSOComponent.Entity.UserInfo UserModel = new EyouSoft.SSOComponent.Entity.UserInfo();
//公司编号
UserModel.CompanyID = SiteUserInfo.CompanyID;
//出票统计查询实体
EyouSoft.Model.StatisticStructure.QueryTicketOutStatisti ssModel = new EyouSoft.Model.StatisticStructure.QueryTicketOutStatisti();
//查询条件
ssModel.CompanyId = SiteUserInfo.CompanyID;
string DepartName = Utils.GetQueryStringValue("DepartMents"); //获取部门查询条件
string departId = Utils.GetQueryStringValue("DepartIds");
string OfficeName = Utils.GetQueryStringValue("OfficeName"); //获取航空公司查询条件
int[] AirLineIds = new int[1];
AirLineIds[0] = Utils.GetInt(Utils.GetQueryStringValue("areaId"));//获取售票处查询条件
if (AirLineIds[0] > 0)
{
ssModel.AirLineIds = AirLineIds;
}
ssModel.DepartIds = JiPiao_TuiList.GetIntArrByStr(departId);
ssModel.OfficeName = OfficeName;
ssModel.LeaveDateStart = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("leaDateS"));
ssModel.LeaveDateEnd = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("leaDateE"));
//出票统计BLL
EyouSoft.BLL.StatisticStructure.TicketOutStatistic ssBLL = new EyouSoft.BLL.StatisticStructure.TicketOutStatistic(UserModel);
//按日期获取出票统计
IList <EyouSoft.Model.StatisticStructure.TicketOutStatisticTime> ssList = ssBLL.GetTicketOutStatisticTime(ssModel);
if (ssList != null && ssList.Count > 0)
{
retList.DataSource = ssList.Skip((pageIndex - 1) * pageSize).Take(pageSize);;
retList.DataBind();
recordCount = ssList.Count;
BindPage();
#region 设置总计
//总票数
this.lblAllTickets.Text = ssList.Sum(p => p.TicketOutNum).ToString();
//应付机票款
this.lblNeedMoney.Text = ssList.Sum(p => p.TotalAmount).ToString("￥#,##0.00");
//已付机票款
this.lblOverMoney.Text = ssList.Sum(p => p.PayAmount).ToString("￥#,##0.00");
//未付机票款
this.lblNoMoney.Text = ssList.Sum(p => p.UnPaidAmount).ToString("￥#,##0.00");
#endregion
this.lblMsg.Visible = false;
}
else
{
this.ExportPageInfo1.Visible = false;
}
ssList = null;
Bindddl(AirLineIds);
txt_spq.Value = ssModel.OfficeName;
if (ssModel.LeaveDateStart.HasValue)
{
txtLeaveDateStart.Text = ssModel.LeaveDateStart.Value.ToShortDateString();
}
if (ssModel.LeaveDateEnd.HasValue)
{
txtLeaveDateEnd.Text = ssModel.LeaveDateEnd.Value.ToShortDateString();
}
UCselectDepart.GetDepartmentName = DepartName;
UCselectDepart.GetDepartId = departId;
}
protected void BindList()
{
//分页
PageIndex = EyouSoft.Common.Utils.GetInt(EyouSoft.Common.Utils.GetQueryStringValue("Page"), 1);
AirlinesValue = EyouSoft.Common.Utils.GetInt(EyouSoft.Common.Utils.GetQueryStringValue("AirlinesValue"));
string DepartMent = EyouSoft.Common.Utils.GetQueryStringValue("DepartMents");
string DepartId = EyouSoft.Common.Utils.GetQueryStringValue("DepartIds");
Datetime = EyouSoft.Common.Utils.GetDateTimeNullable(EyouSoft.Common.Utils.GetQueryStringValue("DateTime"));
ticketendtime = EyouSoft.Common.Utils.GetDateTimeNullable(EyouSoft.Common.Utils.GetQueryStringValue("endtime"));
LeaDateS = EyouSoft.Common.Utils.GetDateTimeNullable(EyouSoft.Common.Utils.GetQueryStringValue("leaDateS"));
LeaDateE = EyouSoft.Common.Utils.GetDateTimeNullable(EyouSoft.Common.Utils.GetQueryStringValue("leaDateE"));
EyouSoft.Model.StatisticStructure.QueryTicketOutStatisti QueryTicket = new EyouSoft.Model.StatisticStructure.QueryTicketOutStatisti();
QueryTicket.CompanyId = SiteUserInfo.CompanyID;
if (AirlinesValue > 0)
{
QueryTicket.AirLineIds = new int[1] {
AirlinesValue
};
if (this.Airlineslist.Items.FindByValue(AirlinesValue.ToString()) != null)
{
this.Airlineslist.Items.FindByValue(AirlinesValue.ToString()).Selected = true;
}
}
QueryTicket.DepartIds = JiPiao_TuiList.GetIntArrByStr(DepartId);
QueryTicket.EndTicketOutTime = ticketendtime;
QueryTicket.StartTicketOutTime = Datetime;
QueryTicket.LeaveDateStart = LeaDateS;
QueryTicket.LeaveDateEnd = LeaDateE;
IList <EyouSoft.Model.StatisticStructure.TicketOutStatisticOffice> ticketlist = TicketBll.GetTicketOutStatisticOffice(QueryTicket);
if (ticketlist != null && ticketlist.Count > 0)
{
this.prtticketlist.DataSource = EyouSoft.Common.Function.SelfExportPage.GetList <EyouSoft.Model.StatisticStructure.TicketOutStatisticOffice>(PageIndex, PageSize, out RecordCount, ticketlist);
this.prtticketlist.DataBind();
BindPage();
lenght = ticketlist.Count;
#region 设置总计
//总票数
this.lblAllTickets.Text = ticketlist.Sum(p => p.TicketOutNum).ToString();
//应付机票款
this.lblNeedMoney.Text = ticketlist.Sum(p => p.TotalAmount).ToString("c2");
//已付机票款
this.lblOverMoney.Text = ticketlist.Sum(p => p.PayAmount).ToString("c2");
//未付机票款
this.lblNoMoney.Text = ticketlist.Sum(p => p.UnPaidAmount).ToString("c2");
#endregion
this.lblMsg.Visible = false;
}
else
{
this.lblMsg.Visible = true;
this.ExportPageInfo1.Visible = false;
}
#region 设置查询值
UCselectDepart.GetDepartmentName = DepartMent;
UCselectDepart.GetDepartId = DepartId;
if (QueryTicket.LeaveDateStart.HasValue)
{
txtLeaveDateStart.Text = QueryTicket.LeaveDateStart.Value.ToShortDateString();
}
if (QueryTicket.LeaveDateEnd.HasValue)
{
txtLeaveDateEnd.Text = QueryTicket.LeaveDateEnd.Value.ToShortDateString();
}
#endregion
}
