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