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