private void InitEmpProfitStaList() { ////获取当月开始时间 本月的第一天 //DateTime? CurrStartTime = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1); ////当月结束时间 本月的最后一天 //DateTime? CurrEndTime = CurrStartTime.Value.AddMonths(1).AddDays(-1); #region 获取参数 PageIndex = Utils.GetInt(Utils.GetQueryStringValue("Page"), 1); SalserId = EyouSoft.Common.Utils.GetQueryStringValue("SalserId"); DepartId = Utils.GetQueryStringValue("DepartId"); AccounterId = Utils.GetQueryStringValue("AccounterId"); LeaveTourStartDate = Utils.GetDateTimeNullable(Request.QueryString["LeaveTourStartDate"]); LeaveTourEndDate = Utils.GetDateTimeNullable(Request.QueryString["LeaveTourEndDate"]); CheckOrderStartDate = Utils.GetDateTimeNullable(Request.QueryString["SignBillStartDate"]); CheckOrderEndDate = Utils.GetDateTimeNullable(Request.QueryString["SignBillEndDate"]); #endregion #region 查询Model赋值 EyouSoft.Model.StatisticStructure.QueryPersonnelStatistic model = new EyouSoft.Model.StatisticStructure.QueryPersonnelStatistic(); model.CheckDateStart = CheckOrderStartDate; model.CheckDateEnd = CheckOrderEndDate; model.LeaveDateStart = LeaveTourStartDate; model.LeaveDateEnd = LeaveTourEndDate; if (AccounterId != "" && AccounterId != "0") { model.LogisticsIds = Utils.GetIntArray(AccounterId, ","); } if (SalserId != "" && SalserId != "0") { model.SaleIds = Utils.GetIntArray(SalserId, ","); } if (DepartId != "" && DepartId != "0") { model.DepartIds = Utils.GetIntArray(DepartId, ","); } model.CompanyId = this.SiteUserInfo.CompanyID; #endregion //调用底层方法 EyouSoft.BLL.PlanStruture.TravelAgency TraveAnBLL = new EyouSoft.BLL.PlanStruture.TravelAgency(SiteUserInfo); list = EyouSoft.Common.Function.SelfExportPage.GetList <PersonalStatics>(PageIndex, PageSize, out RecordCount, TraveAnBLL.GetStaticsList(model)); if (list != null && list.Count > 0) { this.tbl_ExPageEmp.Visible = true; this.crp_GetEmpTourList.DataSource = list; this.crp_GetEmpTourList.DataBind(); BindPage(); //调用分页 } else { this.tbl_ExPageEmp.Visible = false; this.crp_GetEmpTourList.EmptyText = "<tr bgcolor='#e3f1fc'><td colspan='6' height='50px' align='center'>暂时没有数据!</td></tr>"; } TraveAnBLL = null; list = null; model = null; }
/// <summary> /// 获取某个业务员支出统计列表 /// </summary> /// <param name="SalerId"></param> /// <param name="Date"></param> /// <returns></returns> public IList <PersonalStatics> GetStaticsList(EyouSoft.Model.StatisticStructure.QueryPersonnelStatistic SearchModel) { return(dal.GetStaticsList(SearchModel, HaveUserIds)); }
private void InitEmpTourOrderList() { StaticType = Utils.GetQueryStringValue("Type"); //获取当月开始时间 本月的第一天 DateTime?CurrStartTime = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1); //当月结束时间 本月的最后一天 DateTime?CurrEndTime = CurrStartTime.Value.AddMonths(1).AddDays(-1); IList <EyouSoft.Model.TourStructure.TourOrder> list = null; EyouSoft.BLL.TourStructure.TourOrder tourOrderBLL = new EyouSoft.BLL.TourStructure.TourOrder(SiteUserInfo); EyouSoft.Model.StatisticStructure.QueryPersonnelStatistic searchModel = new EyouSoft.Model.StatisticStructure.QueryPersonnelStatistic(); //分页 PageIndex = Utils.GetInt(Utils.GetQueryStringValue("Page"), 1); SalersId = Utils.GetQueryStringValue("SalserId"); DepartId = Utils.GetQueryStringValue("DepartId"); AccounterId = Utils.GetQueryStringValue("AccounterId"); if (StaticType == "0") { LeaveTourStartDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("LeaveTourStartDate")) == null ? CurrStartTime : Utils.GetDateTimeNullable(Utils.GetQueryStringValue("LeaveTourStartDate")); LeaveTourEndDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("LeaveTourEndDate")) == null ? CurrEndTime : Utils.GetDateTimeNullable(Utils.GetQueryStringValue("LeaveTourEndDate")); SignBillStarDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("SignBillStartDate")) == null ? CurrStartTime : Utils.GetDateTimeNullable(Utils.GetQueryStringValue("SignBillStartDate"));; SignBillEndDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("SignBillEndDate")) == null ? CurrEndTime : Utils.GetDateTimeNullable(Utils.GetQueryStringValue("SignBillEndDate")); } else { LeaveTourStartDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("LeaveTourStartDate")); LeaveTourEndDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("LeaveTourEndDate")); SignBillStarDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("SignBillStartDate")); SignBillEndDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("SignBillEndDate")); } if (DepartId != "") { searchModel.DepartIds = Utils.GetIntArray(DepartId, ","); } if (SalersId != "") { searchModel.SaleIds = Utils.GetIntArray(SalersId, ","); } if (AccounterId != "") { searchModel.LogisticsIds = Utils.GetIntArray(AccounterId, ","); } searchModel.CompanyId = this.CurrentUserCompanyID; searchModel.LeaveDateStart = LeaveTourStartDate; searchModel.LeaveDateEnd = LeaveTourEndDate; searchModel.CheckDateStart = SignBillStarDate; searchModel.CheckDateEnd = SignBillEndDate; CompanyStructure.ComputeOrderType?computeOrder = new EyouSoft.BLL.CompanyStructure.CompanySetting().GetComputeOrderType(SiteUserInfo.CompanyID); if (computeOrder.HasValue) { searchModel.ComputeOrderType = computeOrder.Value; } else { searchModel.ComputeOrderType = CompanyStructure.ComputeOrderType.统计有效订单; } list = tourOrderBLL.GetOrderList(PageSize, PageIndex, ref RecordCount, searchModel); if (list != null && list.Count != 0) { this.tbl_ExPageEmp.Visible = true; this.crp_GetEmpTourList.DataSource = list; this.crp_GetEmpTourList.DataBind(); BindPage(); } else { this.tbl_ExPageEmp.Visible = false; this.crp_GetEmpTourList.EmptyText = "<tr bgcolor='#e3f1fc'><td colspan='6' height='50px' align='center'>暂时没有数据!</td></tr>"; } searchModel = null; list = null; tourOrderBLL = null; }
/// <summary> /// 获取员工业绩-收入统计 /// </summary> /// <param name="model">员工业绩统计查询实体</param> /// <param name="HaveUserIds">用户Id集合,半角逗号分割</param> /// <returns></returns> public IList <EyouSoft.Model.StatisticStructure.PersonnelIncomeStatistic> GetPersonnelIncomeStatistic(EyouSoft.Model.StatisticStructure.QueryPersonnelStatistic model, string HaveUserIds) { IList <EyouSoft.Model.StatisticStructure.PersonnelIncomeStatistic> list = new List <EyouSoft.Model.StatisticStructure.PersonnelIncomeStatistic>(); string strOrder = string.Empty; string strTourWhere = string.Empty; StringBuilder strSql = new StringBuilder(" select "); strSql.Append(" SalerId, "); strSql.Append(" (select ContactName from tbl_CompanyUser where tbl_CompanyUser.id = tro.SalerId and tbl_CompanyUser.isdelete = '0') as SalerName, "); strSql.Append(" sum(PeopleNumber - LeaguePepoleNum) as PeopleCount, "); strSql.Append(" sum(FinanceSum) as FinanceSum, "); strSql.Append(" (select DepartId,DepartName from tbl_CompanyUser where Id = SalerId and isdelete = '0' for xml auto,root('root')) as SalerInfo, "); strSql.Append(" (select Id,ContactName from tbl_CompanyUser where id in (select distinct OperatorId from tbl_TourOperator where TourId in (select distinct TourId from tbl_TourOrder as tmpTro where tmpTro.salerId = tro.salerId and tmpTro.isdelete = '0')) and tbl_CompanyUser.isdelete = '0' for xml auto,root('root')) as Logistics "); strSql.Append(" from tbl_TourOrder as tro "); strSql.AppendFormat(" where IsDelete = '0' and SalerId > 0 and exists (select 1 from tbl_CompanyUser where tbl_CompanyUser.isdelete = '0' and tbl_CompanyUser.Id = SalerId) "); strSql.AppendFormat(" {0} ", this.GetSqlWhere(model, HaveUserIds, ref strOrder, ref strTourWhere)); strSql.Append(" group by SalerId "); if (!string.IsNullOrEmpty(strOrder)) { strSql.AppendFormat(" order by {0} ", strOrder); } DbCommand dc = _db.GetSqlStringCommand(strSql.ToString()); using (IDataReader dr = DbHelper.ExecuteReader(dc, _db)) { EyouSoft.Model.StatisticStructure.PersonnelIncomeStatistic tmpModel = null; System.Xml.XmlAttributeCollection attList = null; System.Xml.XmlDocument xml = null; System.Xml.XmlNodeList xmlNodeList = null; while (dr.Read()) { tmpModel = new EyouSoft.Model.StatisticStructure.PersonnelIncomeStatistic(); if (!dr.IsDBNull(0)) { tmpModel.SalesClerk = new EyouSoft.Model.StatisticStructure.StatisticOperator(); tmpModel.SalesClerk.OperatorId = dr.GetInt32(0); if (!dr.IsDBNull(1)) { tmpModel.SalesClerk.OperatorName = dr.GetString(1); } } if (!dr.IsDBNull(2)) { tmpModel.PeopleNum = dr.GetInt32(2); } if (!dr.IsDBNull(3)) { tmpModel.Income = dr.GetDecimal(3); } if (!dr.IsDBNull(4)) { xml = new System.Xml.XmlDocument(); xml.LoadXml(dr.GetString(4)); xmlNodeList = xml.GetElementsByTagName("tbl_CompanyUser"); if (xmlNodeList != null && xmlNodeList.Count > 0) { attList = xmlNodeList[0].Attributes; if (attList["DepartId"] != null) { tmpModel.DepartId = int.Parse(attList["DepartId"].Value); } if (attList["DepartName"] != null) { tmpModel.DepartName = attList["DepartName"].Value; } } } tmpModel.Logistics = new InayatStatistic().GetStatisticOperator(dr["Logistics"].ToString(), "tbl_CompanyUser", "Id", "ContactName"); list.Add(tmpModel); } } return(list); }
/// <summary> /// 根据查询实体生成Where子句 /// </summary> /// <param name="model">查询实体</param> /// <param name="HaveUserIds">用户Id集合,半角逗号分割</param> /// <param name="strOrder">排序语句</param> /// <param name="strTourWhere">团队SqlWhere</param> /// <returns>Where子句</returns> private string GetSqlWhere(EyouSoft.Model.StatisticStructure.QueryPersonnelStatistic model, string HaveUserIds , ref string strOrder, ref string strTourWhere) { if (model == null) { return(string.Empty); } StringBuilder strSqlWhere = new StringBuilder(); StringBuilder strTmpTourWhere = new StringBuilder(); if (model.CompanyId > 0) { strSqlWhere.AppendFormat(" and SellCompanyId = {0} ", model.CompanyId); strTmpTourWhere.AppendFormat(" and CompanyId = {0} ", model.CompanyId); } if (model.LeaveDateStart.HasValue) { strSqlWhere.AppendFormat(" and datediff(dd,'{0}',LeaveDate) >= 0 ", model.LeaveDateStart.Value.ToShortDateString()); strTmpTourWhere.AppendFormat(" and datediff(dd,'{0}',LeaveDate) >= 0 ", model.LeaveDateStart.Value.ToShortDateString()); } if (model.LeaveDateEnd.HasValue) { strSqlWhere.AppendFormat(" and datediff(dd,LeaveDate,'{0}') >= 0 ", model.LeaveDateEnd.Value.ToShortDateString()); strTmpTourWhere.AppendFormat(" and datediff(dd,LeaveDate,'{0}') >= 0 ", model.LeaveDateEnd.Value.ToShortDateString()); } if (model.CheckDateStart.HasValue) { strSqlWhere.AppendFormat(" and datediff(dd,'{0}',IssueTime) >= 0 ", model.CheckDateStart.Value.ToShortDateString()); } if (model.CheckDateEnd.HasValue) { strSqlWhere.AppendFormat(" and datediff(dd,IssueTime,'{0}') >= 0 ", model.CheckDateEnd.Value.ToShortDateString()); } IList <int> SaleIds = new List <int>(); if (model.SaleIds != null && model.SaleIds.Length > 0) { foreach (int i in model.SaleIds) { if (i <= 0) { continue; } if (SaleIds.Contains(i)) { continue; } SaleIds.Add(i); } } if (model.DepartIds != null && model.DepartIds.Length > 0) { int[] UserIds = new DAL.CompanyStructure.CompanyUser().GetUserIdsByDepartIds(model.DepartIds); if (UserIds != null && UserIds.Length > 0) { foreach (int i in UserIds) { if (i <= 0) { continue; } if (SaleIds.Contains(i)) { continue; } SaleIds.Add(i); } } } if (SaleIds != null && SaleIds.Count > 0) { string strIds = string.Empty; foreach (int i in SaleIds) { if (i <= 0) { continue; } strIds += i.ToString() + ","; } strIds = strIds.Trim(','); if (!string.IsNullOrEmpty(strIds)) { strSqlWhere.AppendFormat(" and SalerId in ({0}) ", strIds); } } //if (!string.IsNullOrEmpty(HaveUserIds)) // strSqlWhere.AppendFormat(" and ViewOperatorId in ({0}) ", HaveUserIds); if (!string.IsNullOrEmpty(HaveUserIds)) { strSqlWhere.AppendFormat( " and exists (select 1 from tbl_Tour where tbl_Tour.TourId = tro.TourId and tbl_Tour.OperatorId in ({0})) ", HaveUserIds); strTmpTourWhere.AppendFormat(" and OperatorId in ({0}) ", HaveUserIds); } if (model.LogisticsIds != null && model.LogisticsIds.Length > 0) { string strIds = string.Empty; foreach (int i in model.LogisticsIds) { strIds += i.ToString() + ","; } strIds = strIds.Trim(','); if (!string.IsNullOrEmpty(strIds)) { strSqlWhere.AppendFormat(" and TourId in (select TourId from tbl_TourOperator where OperatorId in ({0})) ", strIds); } strTmpTourWhere.AppendFormat(" and TourId in (select TourId from tbl_TourOperator where OperatorId in ({0})) ", strIds); } if (model.ComputeOrderType == EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType.统计有效订单) { strSqlWhere.AppendFormat(" and OrderState in ({0},{1},{2}) ", (int)Model.EnumType.TourStructure.OrderState.未处理, (int)Model.EnumType.TourStructure.OrderState.已成交, (int)Model.EnumType.TourStructure.OrderState.已留位); } else if (model.ComputeOrderType == EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType.统计确认成交订单) { strSqlWhere.AppendFormat(" and OrderState = {0} ", (int)Model.EnumType.TourStructure.OrderState.已成交); } switch (model.OrderIndex) { case 0: strOrder = " SalerId asc "; break; case 1: strOrder = " SalerId desc "; break; } strTourWhere = strTmpTourWhere.ToString(); return(strSqlWhere.ToString()); }
/// <summary> /// 获取某个业务员支出统计列表 /// </summary> /// <param name="SearchModel">查询实体</param> /// <param name="haveUserIds">用户Ids</param> /// <returns></returns> public IList <PersonalStatics> GetStaticsList(EyouSoft.Model.StatisticStructure.QueryPersonnelStatistic SearchModel , string haveUserIds) { IList <PersonalStatics> saticsList = new List <PersonalStatics>(); PersonalStatics model = null; StringBuilder sql = new StringBuilder(); #region sql sql.AppendFormat(" select * from View_TravelAndTicketArrear where companyid={0}", SearchModel.CompanyId); if (!string.IsNullOrEmpty(SearchModel.LeaveDateStart.ToString())) { sql.AppendFormat(" and datediff(dd,LeaveDate,'{0}')<0 ", SearchModel.LeaveDateStart); } if (!string.IsNullOrEmpty(SearchModel.LeaveDateEnd.ToString())) { sql.AppendFormat(" and datediff(dd,LeaveDate,'{0}')>=0 ", SearchModel.LeaveDateEnd); } if (!string.IsNullOrEmpty(SearchModel.CheckDateStart.ToString())) { sql.AppendFormat(" and datediff(dd,RegTime,'{0}')<0 ", SearchModel.CheckDateStart.ToString()); } if (!string.IsNullOrEmpty(SearchModel.CheckDateEnd.ToString())) { sql.AppendFormat(" and datediff(dd,RegTime,'{0}')>=0 ", SearchModel.CheckDateEnd.ToString()); } if (SearchModel.LogisticsIds != null && SearchModel.LogisticsIds.Count() > 0) { string sid = ""; foreach (int i in SearchModel.LogisticsIds) { sid += i + ","; } sql.AppendFormat(" and OperateID in({0})", sid.Substring(0, (sid.Length - 1))); } if (SearchModel.SaleIds != null && SearchModel.SaleIds.Count() > 0) { string salerid = ""; foreach (int i in SearchModel.SaleIds) { salerid += i + ","; } sql.AppendFormat(" AND TourId IN(SELECT [TourId] FROM [tbl_TourOrder] WHERE [SalerId] IN({0}) AND [IsDelete]='0')", salerid.Substring(0, (salerid.Length - 1))); } if (!string.IsNullOrEmpty(haveUserIds)) { sql.AppendFormat(" and TourOperatorId in ({0}) ", haveUserIds); } #endregion DbCommand cmd = this._db.GetSqlStringCommand(sql.ToString()); using (IDataReader rd = DbHelper.ExecuteReader(cmd, this._db)) { while (rd.Read()) { model = new PersonalStatics(); model.LeaveDate = rd.IsDBNull(rd.GetOrdinal("leavedate")) ? DateTime.Parse("2000-1-1") : rd.GetDateTime(rd.GetOrdinal("leavedate")); model.PeopleCount = rd.IsDBNull(rd.GetOrdinal("PeopleCount")) ? 0 : rd.GetInt32(rd.GetOrdinal("PeopleCount")); model.RouteName = rd.IsDBNull(rd.GetOrdinal("RouteName")) ? "" : rd.GetString(rd.GetOrdinal("RouteName")); model.SellCompanyName = rd.IsDBNull(rd.GetOrdinal("Supplier")) ? "" : rd.GetString(rd.GetOrdinal("Supplier")); model.Total = rd.IsDBNull(rd.GetOrdinal("TotalAmount")) ? 0 : rd.GetDecimal(rd.GetOrdinal("TotalAmount")); model.TourNo = rd.IsDBNull(rd.GetOrdinal("TourCode")) ? "" : rd.GetString(rd.GetOrdinal("TourCode")); saticsList.Add(model); } } return(saticsList); }