/// <summary> /// 获取交通出票统计信息 /// </summary> /// <param name="companyId">公司编号</param> /// <param name="search">查询实体</param> /// <returns></returns> public IList <Model.PlanStructure.JiaoTongChuPiao> GetJiaoTongChuPiao(int companyId , Model.PlanStructure.JiaoTongChuPiaoSearch search) { if (companyId <= 0) { return(null); } return(Idal.GetJiaoTongChuPiao(companyId, search)); }
/// <summary> /// 获取交通出票统计信息 /// </summary> /// <param name="companyId">公司编号</param> /// <param name="search">查询实体</param> /// <returns></returns> public IList <Model.PlanStructure.JiaoTongChuPiao> GetJiaoTongChuPiao(int companyId , Model.PlanStructure.JiaoTongChuPiaoSearch search) { IList <Model.PlanStructure.JiaoTongChuPiao> list = null; if (companyId <= 0) { return(list); } var strSql = new StringBuilder(); //出团时间查询 string strLeaveDate = string.Empty; //出票时间查询 string strShenQingDate = string.Empty; strSql.Append(" SELECT t.TrafficId,t.TrafficName "); strSql.Append(" ,( "); strSql.Append(" SELECT isnull(SUM(to1.PeopleNumber - to1.LeaguePepoleNum),0) FROM tbl_TourOrder to1 "); strSql.Append(" WHERE to1.IsDelete = '0' @ "); strSql.Append( " AND EXISTS (SELECT 1 FROM tbl_TourOrderTraffic tot WHERE tot.OrderId = to1.ID AND tot.TrafficId = t.TrafficId) "); strSql.AppendFormat( " AND EXISTS (SELECT 1 FROM tbl_PlanTicketOut pto WHERE pto.OrderId = to1.ID AND pto.[State] = {0} $) ", (int)Model.EnumType.PlanStructure.TicketState.已出票); strSql.Append(" ) AS ChuPiaoShu "); strSql.Append(" ,("); strSql.Append(" SELECT ISNULL(SUM(ptk.AgencyPrice),0) FROM tbl_PlanTicketKind ptk WHERE "); strSql.AppendFormat(" EXISTS (SELECT 1 FROM tbl_PlanTicketOut pto WHERE pto.ID = ptk.TicketId AND pto.[State]={0} AND pto.OrderId IN ", (int)Model.EnumType.PlanStructure.TicketState.已出票); strSql.Append(" ( "); strSql.Append(" SELECT to1.ID FROM tbl_TourOrder to1 WHERE to1.IsDelete = '0' "); strSql.Append(" AND EXISTS (SELECT 1 FROM tbl_TourOrderTraffic tot WHERE tot.OrderId = to1.ID AND tot.TrafficId = t.TrafficId $ ))) "); strSql.Append(" ) AS DaiLiFei"); strSql.AppendFormat( " FROM tbl_Traffic t WHERE t.IsDelete = '0' and t.[Status] = 0 and CompanyId = {0} ", companyId); if (search != null) { if (search.StartTime.HasValue) { strLeaveDate += string.Format( " and datediff(dd,'{0}',to1.LeaveDate) >= 0 ", search.StartTime.Value.ToShortDateString()); } if (search.EndTime.HasValue) { strLeaveDate += string.Format( " and datediff(dd,'{0}',to1.LeaveDate) <= 0 ", search.EndTime.Value.ToShortDateString()); } } strSql.Append(" ORDER BY t.InsueTime "); DbCommand dc = _db.GetSqlStringCommand(strSql.ToString().Replace("@", strLeaveDate).Replace("$", strLeaveDate)); list = new List <Model.PlanStructure.JiaoTongChuPiao>(); using (IDataReader dr = DbHelper.ExecuteReader(dc, _db)) { while (dr.Read()) { var model = new Model.PlanStructure.JiaoTongChuPiao(); if (!dr.IsDBNull(dr.GetOrdinal("TrafficId"))) { model.TrafficId = dr.GetInt32(dr.GetOrdinal("TrafficId")); } if (!dr.IsDBNull(dr.GetOrdinal("TrafficName"))) { model.TrafficName = dr.GetString(dr.GetOrdinal("TrafficName")); } if (!dr.IsDBNull(dr.GetOrdinal("ChuPiaoShu"))) { model.ChuPiaoShu = dr.GetInt32(dr.GetOrdinal("ChuPiaoShu")); } if (!dr.IsDBNull(dr.GetOrdinal("DaiLiFei"))) { model.AgencyPrice = dr.GetDecimal(dr.GetOrdinal("DaiLiFei")); } list.Add(model); } } return(list); }