/// <summary> /// 获取销售统计列表 /// </summary> /// <param name="PageSize">每页记录数</param> /// <param name="PageIndex">当前页数</param> /// <param name="RecordCount">总记录数</param> /// <param name="QueryModel">查询实体</param> /// <param name="QueryModel">查询实体</param> /// <param name="us">用户信息集合</param> /// <returns></returns> public IList <EyouSoft.Model.StatisticStructure.MSalesStatistics> GetSalesStatistics(int PageSize, int PageIndex , ref int RecordCount, EyouSoft.Model.StatisticStructure.MQuerySalesStatistics QueryModel, string us) { if (QueryModel == null || QueryModel.CompanyId <= 0) { return(null); } IList <EyouSoft.Model.StatisticStructure.MSalesStatistics> list = null; EyouSoft.Model.StatisticStructure.MSalesStatistics model = null; string strFile = " * "; string strPK = "ID"; string strTableName = "View_SalesStatistics"; string strOrderBy = " LeaveDate asc "; string strWhere = this.GetSqlWhere(QueryModel, us); switch (QueryModel.OrderIndex) { case 0: strOrderBy = " LeaveDate asc "; break; case 1: strOrderBy = " LeaveDate desc "; break; } using (IDataReader dr = DbHelper.ExecuteReader(this._db, PageSize, PageIndex, ref RecordCount, strTableName, strPK, strFile, strWhere, strOrderBy)) { list = new List <EyouSoft.Model.StatisticStructure.MSalesStatistics>(); while (dr.Read()) { model = new EyouSoft.Model.StatisticStructure.MSalesStatistics(); if (!dr.IsDBNull(dr.GetOrdinal("ID"))) { model.OrderId = dr.GetString(dr.GetOrdinal("ID")); } if (!dr.IsDBNull(dr.GetOrdinal("LeaveDate"))) { model.LeaveDate = dr.GetDateTime(dr.GetOrdinal("LeaveDate")); } if (!dr.IsDBNull(dr.GetOrdinal("TourClassId")) && !dr.IsDBNull(dr.GetOrdinal("AreaName"))) { if ((Model.EnumType.TourStructure.TourType)dr.GetByte(dr.GetOrdinal("TourClassId")) == EyouSoft.Model.EnumType.TourStructure.TourType.单项服务) { model.AreaName = this.GetPlanSingleAreaName(dr.GetString(dr.GetOrdinal("AreaName"))); } else { model.AreaName = dr.GetString(dr.GetOrdinal("AreaName")); } } if (!dr.IsDBNull(dr.GetOrdinal("RouteName"))) { model.RouteName = dr.GetString(dr.GetOrdinal("RouteName")); } if (!dr.IsDBNull(dr.GetOrdinal("Logistics"))) { model.Logistics = this.GetOperatorListByXML(dr.GetString(dr.GetOrdinal("Logistics")), "OperatorId", "OperatorName"); } if (!dr.IsDBNull(dr.GetOrdinal("BuyCompanyName"))) { model.CustomerName = dr.GetString(dr.GetOrdinal("BuyCompanyName")); } if (!dr.IsDBNull(dr.GetOrdinal("FinanceSum"))) { model.TotalAmount = dr.GetDecimal(dr.GetOrdinal("FinanceSum")); } if (!dr.IsDBNull(dr.GetOrdinal("PeopleNumber"))) { model.PeopleNum = dr.GetInt32(dr.GetOrdinal("PeopleNumber")); } //if (!dr.IsDBNull(dr.GetOrdinal("OperatorName"))) // model.OperatorName = dr.GetString(dr.GetOrdinal("OperatorName")); if (!dr.IsDBNull(dr.GetOrdinal("BuyerContactName"))) { model.OperatorName = dr.GetString(dr.GetOrdinal("BuyerContactName")); } //if (!dr.IsDBNull(dr.GetOrdinal("DepartName"))) // model.DepartName = dr.GetString(dr.GetOrdinal("DepartName")); if (!dr.IsDBNull(dr.GetOrdinal("ProvinceAndCity"))) { model.ProvinceAndCity = dr.GetString(dr.GetOrdinal("ProvinceAndCity")); } if (!dr.IsDBNull(dr.GetOrdinal("SalerId"))) { model.SalerId = dr.GetInt32(dr.GetOrdinal("SalerId")); } //if (!dr.IsDBNull(dr.GetOrdinal("SalerName"))) // model.SalerName = dr.GetString(dr.GetOrdinal("SalerName")); string xml = dr["PerTimeSeller"].ToString(); if (!string.IsNullOrEmpty(xml)) { XElement xroot = XElement.Parse(xml); var xrow = Utils.GetXElement(xroot, "row"); model.SalerName = Utils.GetXAttributeValue(xrow, "ContactName"); model.DepartName = Utils.GetXAttributeValue(xrow, "DepartName"); } model.ZeRenSelllerName = dr["SalerName"].ToString(); model.KeHuDanWeiId = dr.GetInt32(dr.GetOrdinal("BuyCompanyId")); list.Add(model); } } return(list); }
/// <summary> /// 根据查询实体生成SqlWhere语句 /// </summary> /// <param name="QueryModel">查询实体</param> /// <param name="us">用户信息集合</param> /// <returns>SqlWhere语句</returns> private string GetSqlWhere(EyouSoft.Model.StatisticStructure.MQuerySalesStatistics QueryModel, string us) { if (QueryModel == null || QueryModel.CompanyId <= 0) { return(string.Empty); } string strIds = string.Empty; StringBuilder strWhere = new StringBuilder(); strWhere.AppendFormat(" SellCompanyId = {0} ", QueryModel.CompanyId); if (QueryModel.LeaveDateStart.HasValue) { strWhere.AppendFormat(" and datediff(dd,'{0}',LeaveDate) >= 0 ", QueryModel.LeaveDateStart.Value.ToShortDateString()); } if (QueryModel.LeaveDateEnd.HasValue) { strWhere.AppendFormat(" and datediff(dd,LeaveDate,'{0}') >= 0 ", QueryModel.LeaveDateEnd.Value.ToShortDateString()); } //线路区域 strIds = this.GetSqlIdStrByArray(QueryModel.AreaIds); if (!string.IsNullOrEmpty(strIds)) { strWhere.AppendFormat(" and AreaId in ({0}) ", strIds); } //责任计调 //strIds = this.GetSqlIdStrByArray(QueryModel.LogisticIds); //if (!string.IsNullOrEmpty(strIds)) // strWhere.AppendFormat(" and TourId in (select TourId from tbl_TourOperator where tbl_TourOperator.OperatorId in ({0})) ", strIds); //人次统计销售员 strIds = this.GetSqlIdStrByArray(QueryModel.LogisticIds); if (!string.IsNullOrEmpty(strIds)) { strWhere.AppendFormat(" and PerTimeSellerId in ({0}) ", strIds); } if (QueryModel.ComputeOrderType == EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType.统计确认成交订单) { strWhere.AppendFormat(" and OrderState = {0} ", (int)Model.EnumType.TourStructure.OrderState.已成交); } else if (QueryModel.ComputeOrderType == EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType.统计有效订单) { strWhere.AppendFormat(" and OrderState in ({0},{1},{2}) ", (int)Model.EnumType.TourStructure.OrderState.未处理, (int)Model.EnumType.TourStructure.OrderState.已成交, (int)Model.EnumType.TourStructure.OrderState.已留位); } if (QueryModel.CustomerId > 0) { strWhere.AppendFormat(" and BuyCompanyID = {0} ", QueryModel.CustomerId); } //责任销售 strIds = this.GetSqlIdStrByArray(QueryModel.SalesClerkIds); if (!string.IsNullOrEmpty(strIds)) { strWhere.AppendFormat(" and SalerId in ({0}) ", strIds); } //对方操作员 strIds = this.GetSqlIdStrByArray(QueryModel.OperatorId); if (!string.IsNullOrEmpty(strIds)) { strWhere.AppendFormat(" and BuyerContactId in ({0}) ", strIds); } /*//所属地区 * strIds = this.GetSqlIdStrByArray(QueryModel.CityIds); * if (!string.IsNullOrEmpty(strIds)) * strWhere.AppendFormat(" and BuyCompanyID in (select Id from tbl_Customer where tbl_Customer.IsDelete = '0' and tbl_Customer.CityId in ({0})) ", strIds);*/ if ((QueryModel.CityIds != null && QueryModel.CityIds.Length > 0) || (QueryModel.ProvinceIds != null && QueryModel.ProvinceIds.Length > 0)) { strWhere.AppendFormat(" AND BuyCompanyId IN(SELECT Id FROM tbl_Customer AS A WHERE A.IsDelete='0' AND CompanyId ={0} ", QueryModel.CompanyId); if (QueryModel.CityIds != null && QueryModel.CityIds.Length > 0) { strWhere.AppendFormat(" AND A.CityId IN({0}) ", Utils.GetSqlIdStrByArray(QueryModel.CityIds)); } if (QueryModel.ProvinceIds != null && QueryModel.ProvinceIds.Length > 0) { strWhere.AppendFormat(" AND A.ProviceId IN({0}) ", Utils.GetSqlIdStrByArray(QueryModel.ProvinceIds)); } strWhere.Append(" ) "); } //组团社名称 if (!string.IsNullOrEmpty(QueryModel.CustomerName)) { strWhere.AppendFormat(" and BuyCompanyName like '%{0}%' ", QueryModel.CustomerName); } if (!string.IsNullOrEmpty(us)) { strWhere.AppendFormat(" AND EXISTS(SELECT 1 FROM tbl_Tour AS A WHERE A.TourId=View_SalesStatistics.TourId AND A.OperatorId IN({0})) ", us); } if (QueryModel.ShangDanSDate.HasValue) { strWhere.AppendFormat(" AND IssueTime>='{0}' ", QueryModel.ShangDanSDate.Value); } if (QueryModel.ShangDanEDate.HasValue) { strWhere.AppendFormat(" AND IssueTime<='{0}' ", QueryModel.ShangDanEDate.Value.AddDays(1).AddMilliseconds(-1)); } return(strWhere.ToString()); }