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