/// <summary>
/// 获得显示在团队回访页面上的列表数据
/// </summary>
/// <param name="model">团队回访列表查询Model</param>
/// <param name="startDate">开始时间</param>
/// <param name="endDate">结束时间</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">当前显示记录数</param>
/// <param name="recordCount">总记录数</param>
/// <returns></returns>
public IList <Model.CrmStructure.MVisitListModel> GetVisitShowModel(Model.CrmStructure.MVisitListModel model, DateTime?startDate, DateTime?endDate, int pageIndex, int pageSize, ref int recordCount)
{
if (model == null)
{
return(null);
}
if (string.IsNullOrEmpty(model.CompanyId))
{
return(null);
}
return(visitDal.GetVisitShowModel(model, startDate, endDate, pageIndex, pageSize, ref recordCount));
}
/// <summary>
/// 获得显示在团队回访页面上的列表数据
/// </summary>
/// <param name="model">团队回访列表显示Model</param>
/// <param name="startDate">开始时间</param>
/// <param name="endDate">结束时间</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">当前显示记录数</param>
/// <param name="recordCount">总记录数</param>
/// <returns></returns>
public IList <Model.CrmStructure.MVisitListModel> GetVisitShowModel(Model.CrmStructure.MVisitListModel model, DateTime?startDate, DateTime?endDate, int pageIndex, int pageSize, ref int recordCount)
{
IList <Model.CrmStructure.MVisitListModel> list = new List <Model.CrmStructure.MVisitListModel>();
var field = new StringBuilder();
var query = new StringBuilder();
field.Append(" TourId ,");
field.Append(" TourCode ,");
field.Append(" RouteId ,");
field.Append(" RouteName ,");
field.Append(" RDate ,");
field.Append(" Customer = STUFF(( SELECT ',' + BuyCompanyName");
field.Append(" FROM ( SELECT BuyCompanyName");
field.Append(" FROM dbo.tbl_TourOrder");
field.Append(" WHERE TourId = dbo.tbl_Tour.TourId");
field.Append(" AND IsDelete = '0'");
field.AppendFormat(" AND Status = {0}", (int)OrderStatus.已成交);
field.Append(" ) AS A");
field.Append(" FOR");
field.Append(" XML PATH('')");
field.Append(" ), 1, 1, '') ,");
field.Append(" SellerName ,");
field.Append(" GuideName = STUFF(( SELECT ',' + SourceName");
field.Append(" FROM ( SELECT SourceName");
field.Append(" FROM dbo.tbl_Plan");
field.Append(" WHERE TourId = dbo.tbl_Tour.TourId");
field.Append(" AND IsDelete = '0'");
field.AppendFormat(" AND Type = {0}", (int)PlanProject.导游);
field.AppendFormat(" AND Status = {0}", (int)PlanState.已落实);
field.Append(" ) AS A");
field.Append(" FOR");
field.Append(" XML PATH('')");
field.Append(" ), 1, 1, '') ,");
field.Append(" Planer = STUFF(( SELECT ',' + Planer");
field.Append(" FROM ( SELECT Planer");
field.Append(" FROM tbl_TourPlaner");
field.Append(" WHERE TourId = dbo.tbl_Tour.TourId");
field.Append(" ) AS A");
field.Append(" FOR");
field.Append(" XML PATH('')");
field.Append(" ), 1, 1, '') ,");
field.Append(" TourStatus ,");
field.Append(" QualityScore = ISNULL(( SELECT ISNULL(SUM(QualityScore), 0)");
field.Append(" / COUNT(*)");
field.Append(" FROM dbo.tbl_CrmVisit");
field.Append(" WHERE TourId = dbo.tbl_Tour.TourId");
field.Append(" HAVING COUNT(*) > 0");
field.Append(" ), 0)");
query.AppendFormat(" CompanyId = '{0}' AND IsDelete = '0'", model.CompanyId);
query.AppendFormat(" AND TourStatus BETWEEN {0} AND {1}", (int)TourStatus.计调配置完毕, (int)TourStatus.封团);
query.Append(" AND ISNULL(ParentId, '') <> ''");
if (!string.IsNullOrEmpty(model.TourCode))
{
query.AppendFormat(" AND TourCode LIKE '%{0}%'", model.TourCode);
}
if (!string.IsNullOrEmpty(model.RouteName))
{
query.AppendFormat(" AND RouteName LIKE '%{0}%'", model.RouteName);
}
if (startDate != null)
{
query.AppendFormat(" AND RDate >= '{0}'", startDate);
}
if (endDate != null)
{
query.AppendFormat(" AND RDate <= '{0}'", endDate);
}
//if (!string.IsNullOrEmpty(model.UnitName))
//{query.Append(" AND BuyCompanyId = '1'");}
//else
if (!string.IsNullOrEmpty(model.UnitName))
{
query.Append(" AND EXISTS ( SELECT 1");
query.Append(" FROM dbo.tbl_TourOrder");
query.Append(" WHERE TourId = dbo.tbl_Tour.TourId");
query.Append(" AND IsDelete = '0'");
query.AppendFormat(" AND Status = {0}", (int)OrderStatus.已成交);
query.AppendFormat(" AND BuyCompanyName LIKE '%{0}%'", model.UnitName);
query.Append(" )");
}
//query.Append(" AND SourceName LIKE '%%' )");
if (model.GuideName != null && model.GuideName.Length != 0)
{
query.Append(" AND EXISTS ( SELECT 1");
query.Append(" FROM dbo.tbl_Plan");
query.Append(" WHERE TourId = dbo.tbl_Tour.TourId");
query.Append(" AND IsDelete = '0'");
query.AppendFormat(" AND Status = {0}", (int)PlanState.已落实);
query.AppendFormat(" AND Type = {0}", (int)PlanProject.导游);
query.Append(" and ");
for (var i = 0; i < model.GuideName.Length; i++)
{
query.AppendFormat(
i == model.GuideName.Length - 1 ? " SourceName like '%{0}%' " : " SourceName like '%{0}%' or",
model.GuideName[i]);
}
query.Append(" ) ");
}
//XmlDocument xml = null;
using (var reader = DbHelper.ExecuteReader(_db, pageSize, pageIndex, ref recordCount, "tbl_Tour", "TourId", field.ToString(), query.ToString(), " IssueTime desc"))
{
while (reader.Read())
{
var visitListModel = new EyouSoft.Model.CrmStructure.MVisitListModel {
CompanyId = model.CompanyId
};
if (!reader.IsDBNull(reader.GetOrdinal("GuideName")))
{
var lst = reader["GuideName"].ToString().Split(',').ToList();
//xml = new XmlDocument();
//xml.LoadXml(reader["GuideName"].ToString());
visitListModel.GuideName = new string[lst.Count];
for (var i = 0; i < lst.Count; i++)
{
visitListModel.GuideName[i] = lst[i];
}
}
visitListModel.PlanerName = reader["Planer"].ToString();
visitListModel.RouteId = !reader.IsDBNull(reader.GetOrdinal("RouteId")) ? (string)reader["RouteId"] : string.Empty;
visitListModel.RouteName = !reader.IsDBNull(reader.GetOrdinal("RouteName")) ? (string)reader["RouteName"] : string.Empty;
visitListModel.SalesmanName = !reader.IsDBNull(reader.GetOrdinal("SellerName")) ? (string)reader["SellerName"] : string.Empty;
visitListModel.RDate = !reader.IsDBNull(reader.GetOrdinal("RDate")) ? (DateTime?)reader["RDate"] : null;
visitListModel.TourCode = !reader.IsDBNull(reader.GetOrdinal("TourCode")) ? (string)reader["TourCode"] : string.Empty;
visitListModel.TourId = !reader.IsDBNull(reader.GetOrdinal("TourId")) ? (string)reader["TourId"] : string.Empty;
visitListModel.TourStatus = (EyouSoft.Model.EnumType.TourStructure.TourStatus)(int.Parse(reader["TourStatus"].ToString()));
visitListModel.UnitName = !reader.IsDBNull(reader.GetOrdinal("Customer")) ? reader["Customer"].ToString() : string.Empty;
visitListModel.QualityScore = float.Parse(reader["QualityScore"].ToString());
//if (!reader.IsDBNull(reader.GetOrdinal("ReturnType")))
//{
// visitListModel.ReturnType = (Model.EnumType.CrmStructure.CrmReturnType)((int)reader["ReturnType"]);
//}
//visitListModel.Total = !reader.IsDBNull(reader.GetOrdinal("Total")) ? (string)reader["Total"] : string.Empty;
//visitListModel.Id = (int)reader["Id"];
list.Add(visitListModel);
}
}
return(list);
}
