protected void BindQuote()
{
EyouSoft.Model.TourStructure.LineInquireQuoteSearchInfo searchInfo = new EyouSoft.Model.TourStructure.LineInquireQuoteSearchInfo();
searchInfo.RouteName = Utils.GetQueryStringValue("teamName"); //获取团队名称
searchInfo.TourNo = Utils.GetQueryStringValue("teamNum"); //获取团号
searchInfo.DayNum = Utils.GetInt(Utils.GetQueryStringValue("dayCount"), 0); //获取天数
searchInfo.SDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("begTime")); //获取出团日期区间_开始
searchInfo.EDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("endTime")); //获取出团日期区间_结束
searchInfo.XunTuanSTime = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("askbegin"));
searchInfo.XunTuanETime = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("askend"));
pageIndex = Utils.GetInt(Utils.GetQueryStringValue("Page"), 1);
IList <EyouSoft.Model.TourStructure.LineInquireQuoteInfo> lineQuotes = lineTourBll.GetInquireList(SiteUserInfo.CompanyID, pageSize, pageIndex, ref recordCount, false, searchInfo);
//人数合计
int peopleSum = 0;
lineTourBll.GetInquireListHeJi(SiteUserInfo.CompanyID, searchInfo, out peopleSum);
peopleCount.Text = peopleSum.ToString();
this.rpTour.DataSource = lineQuotes;
this.rpTour.DataBind();
this.BindPage();
this.txt_beginDate.Value = Utils.GetQueryStringValue("begTime");
this.txt_dayCount.Value = Utils.GetQueryStringValue("dayCount");
this.txt_endDate.Value = Utils.GetQueryStringValue("endTime");
this.txt_teamName.Value = Utils.GetQueryStringValue("teamName");
this.txt_teamNum.Value = Utils.GetQueryStringValue("teamNum");
this.txt_AskBegin.Value = Utils.GetQueryStringValue("askbegin");
this.txt_AskEnd.Value = Utils.GetQueryStringValue("askend");
}
/// <summary>
/// 获取组团社询价列表合计
/// </summary>
/// <param name="companyId">公司编号</param>
/// <param name="searchInfo">查询信息</param>
/// <param name="renShuHeJi">人数合计</param>
public void GetInquireListHeJi(int companyId, EyouSoft.Model.TourStructure.LineInquireQuoteSearchInfo searchInfo, out int renShuHeJi)
{
renShuHeJi = 0;
if (companyId < 1)
{
return;
}
dal.GetInquireListHeJi(companyId, searchInfo, out renShuHeJi);
}
protected void bind()
{
pageIndex = Utils.GetInt(Utils.GetQueryStringValue("page"), 1);
EyouSoft.BLL.TourStructure.LineInquireQuoteInfo tsBLL = new EyouSoft.BLL.TourStructure.LineInquireQuoteInfo();
EyouSoft.Model.TourStructure.LineInquireQuoteSearchInfo SearchInfo = null;
IList <EyouSoft.Model.TourStructure.LineInquireQuoteInfo> GetInquireList;
GetInquireList = tsBLL.GetInquireList(SiteUserInfo.TourCompany.TourCompanyId, pageSize, pageIndex, ref recordCount, true, SearchInfo);
//绑定
retList.DataSource = GetInquireList;
retList.DataBind();
GetInquireList = null;
BindPage();
}
/// <summary>
/// 获取询价列表
/// </summary>
/// <param name="companyId">公司编号（专线：专线公司编号，组团：组团公司编号）</param>
/// <param name="pageSize">每页显示的记录数</param>
/// <param name="pageIndex">返回第几页</param>
/// <param name="recordCount">返回的记录数</param>
/// <param name="isZhuTuan">专线：False,组团：True</param>
/// <returns></returns>
public IList <EyouSoft.Model.TourStructure.LineInquireQuoteInfo> GetInquireList(int companyId, int pageSize, int pageIndex, ref int recordCount, bool isZhuTuan, EyouSoft.Model.TourStructure.LineInquireQuoteSearchInfo SearchInfo)
{
return(dal.GetInquireList(companyId, pageSize, pageIndex, ref recordCount, isZhuTuan, SearchInfo));
}
/// <summary>
/// 获取组团社询价列表合计
/// </summary>
/// <param name="companyId">公司编号</param>
/// <param name="searchInfo">查询信息</param>
/// <param name="renShuHeJi">人数合计</param>
public void GetInquireListHeJi(int companyId, EyouSoft.Model.TourStructure.LineInquireQuoteSearchInfo searchInfo, out int renShuHeJi)
{
renShuHeJi = 0;
StringBuilder cmdText = new StringBuilder();
#region SQL
cmdText.Append(" SELECT SUM(AdultNumber+ChildNumber) AS RenShuHeJi FROM tbl_CustomerQuote ");
cmdText.AppendFormat(" WHERE CompanyId={0} AND IsDelete='0' ", companyId);
if (searchInfo != null)
{
if (!string.IsNullOrEmpty(searchInfo.TourNo) || searchInfo.DayNum != 0)
{
cmdText.Append(" AND EXISTS(SELECT 1 FROM tbl_Tour AS A WHERE A.TourId=tbl_CustomerQuote.BuildTourId ");
if (!string.IsNullOrEmpty(searchInfo.TourNo))
{
cmdText.AppendFormat(" AND A.TourCode LIKE '%{0}%' ", searchInfo.TourNo);
}
if (searchInfo.DayNum != 0)
{
cmdText.AppendFormat(" AND A.TourDays={0} ", searchInfo.DayNum);
}
cmdText.Append(" ) ");
}
if (!String.IsNullOrEmpty(searchInfo.RouteName))
{
cmdText.AppendFormat(" AND RouteName like '%{0}%'", searchInfo.RouteName);
}
if (searchInfo.SDate.HasValue && searchInfo.SDate != DateTime.MinValue)
{
cmdText.AppendFormat(" AND LeaveDate>'{0}' ", searchInfo.SDate.Value.AddDays(-1));
}
if (searchInfo.EDate.HasValue && searchInfo.EDate != DateTime.MinValue)
{
cmdText.AppendFormat(" AND LeaveDate<'{0}' ", searchInfo.EDate.Value.AddDays(1));
}
if (searchInfo.XunTuanETime.HasValue)
{
cmdText.AppendFormat(" AND IssueTime<'{0}' ", searchInfo.XunTuanETime.Value.AddDays(1));
}
if (searchInfo.XunTuanSTime.HasValue)
{
cmdText.AppendFormat(" AND IssueTime>'{0}' ", searchInfo.XunTuanSTime.Value);
}
}
#endregion
DbCommand cmd = DB.GetSqlStringCommand(cmdText.ToString());
using (IDataReader rdr = DbHelper.ExecuteReader(cmd, DB))
{
if (rdr.Read())
{
if (!rdr.IsDBNull(0))
{
renShuHeJi = rdr.GetInt32(0);
}
}
}
}
/// <summary>
/// 获取询价列表
/// </summary>
/// <param name="companyId">公司编号（专线：专线公司编号，组团：组团公司编号）</param>
/// <param name="pageSize">每页显示的记录数</param>
/// <param name="pageIndex">返回第几页</param>
/// <param name="recordCount">返回的记录数</param>
/// <param name="isZhuTuan">专线：False,组团：True</param>
/// <returns></returns>
public IList <EyouSoft.Model.TourStructure.LineInquireQuoteInfo> GetInquireList(int companyId, int pageSize, int pageIndex, ref int recordCount, bool isZhuTuan, EyouSoft.Model.TourStructure.LineInquireQuoteSearchInfo SearchInfo)
{
IList <EyouSoft.Model.TourStructure.LineInquireQuoteInfo> items = new List <EyouSoft.Model.TourStructure.LineInquireQuoteInfo>();
EyouSoft.Model.TourStructure.LineInquireQuoteInfo item = null;
StringBuilder cmdQuery = new StringBuilder();
string tableName = "tbl_CustomerQuote";
string primaryKey = "Id";
string orderByString = "IssueTime DESC";
StringBuilder fields = new StringBuilder();
#region 要查询的字段
fields.Append(" Id,LeaveDate,RouteName,CustomerId,AdultNumber+ChildNumber AS PeopleNum,(select name from tbl_Customer where Id=tbl_CustomerQuote.CustomerId) as CustomerName,ContactName,ContactTel,QuoteState,");
fields.Append("(select TourCode from tbl_Tour where TourId=tbl_CustomerQuote.BuildTourId) as TourCode,");
//fields.Append("(select RouteName from tbl_Tour where TourId=tbl_CustomerQuote.BuildTourId) as RouteName,");
fields.Append("(select TourDays from tbl_Tour where TourId=tbl_CustomerQuote.BuildTourId) as TourDays");
//fields.Append("(select LeaveDate from tbl_Tour where TourId=tbl_CustomerQuote.BuildTourId) as ChuTuanDate");
fields.Append(" ,IssueTime ");
#endregion
#region 拼接查询条件
if (isZhuTuan)
{
cmdQuery.AppendFormat(" CustomerId={0} AND IsDelete=0", companyId);
}
else
{
cmdQuery.AppendFormat(" CompanyId={0} AND IsDelete=0", companyId);
}
if (SearchInfo != null)
{
if (!string.IsNullOrEmpty(SearchInfo.TourNo) || SearchInfo.DayNum != 0)
{
cmdQuery.Append(" AND EXISTS(SELECT 1 FROM tbl_Tour AS A WHERE A.TourId=tbl_CustomerQuote.BuildTourId ");
if (!string.IsNullOrEmpty(SearchInfo.TourNo))
{
cmdQuery.AppendFormat(" AND A.TourCode LIKE '%{0}%' ", SearchInfo.TourNo);
}
if (SearchInfo.DayNum != 0)
{
cmdQuery.AppendFormat(" AND A.TourDays={0} ", SearchInfo.DayNum);
}
cmdQuery.Append(" ) ");
}
if (!String.IsNullOrEmpty(SearchInfo.RouteName))
{
cmdQuery.AppendFormat(" AND RouteName like '%{0}%'", SearchInfo.RouteName);
}
if (SearchInfo.SDate.HasValue && SearchInfo.SDate != DateTime.MinValue)
{
cmdQuery.AppendFormat(" AND LeaveDate>'{0}' ", SearchInfo.SDate.Value.AddDays(-1));
}
if (SearchInfo.EDate.HasValue && SearchInfo.EDate != DateTime.MinValue)
{
cmdQuery.AppendFormat(" AND LeaveDate<'{0}' ", SearchInfo.EDate.Value.AddDays(1));
}
if (SearchInfo.XunTuanETime.HasValue)
{
cmdQuery.AppendFormat(" AND IssueTime<'{0}' ", SearchInfo.XunTuanETime.Value.AddDays(1));
}
if (SearchInfo.XunTuanSTime.HasValue)
{
cmdQuery.AppendFormat(" AND IssueTime>'{0}' ", SearchInfo.XunTuanSTime.Value);
}
}
#endregion
using (IDataReader rdr = DbHelper.ExecuteReader(this.DB, pageSize, pageIndex, ref recordCount, tableName, primaryKey, fields.ToString(), cmdQuery.ToString(), orderByString))
{
while (rdr.Read())
{
item = new EyouSoft.Model.TourStructure.LineInquireQuoteInfo()
{
Id = rdr.GetInt32(rdr.GetOrdinal("Id")),
CustomerId = rdr.GetInt32(rdr.GetOrdinal("CustomerId")),
RouteName = rdr["RouteName"].ToString(),
LeaveDate = rdr.IsDBNull(rdr.GetOrdinal("LeaveDate")) ? System.DateTime.Now : rdr.GetDateTime(rdr.GetOrdinal("LeaveDate")),
PeopleNum = rdr.IsDBNull(rdr.GetOrdinal("PeopleNum")) ? 0 : rdr.GetInt32(rdr.GetOrdinal("PeopleNum")),
CustomerName = rdr["CustomerName"].ToString(),
ContactName = rdr["ContactName"].ToString(),
ContactTel = rdr["ContactTel"].ToString(),
QuoteState = (EyouSoft.Model.EnumType.TourStructure.QuoteState)rdr.GetByte(rdr.GetOrdinal("QuoteState")),
IssueTime = rdr.GetDateTime(rdr.GetOrdinal("IssueTime"))
};
items.Add(item);
}
}
return(items);
}