/// <summary>
/// 根据接收对象类型和编号获取接收名称
/// </summary>
/// <param name="AcceptId">接收对象编号</param>
/// <param name="AcceptType">接收对象类型</param>
/// <returns></returns>
private string GetAcceptName(int AcceptId, EyouSoft.Model.EnumType.PersonalCenterStructure.AcceptType AcceptType)
{
string AcceptName = string.Empty;
switch (AcceptType)
{
case EyouSoft.Model.EnumType.PersonalCenterStructure.AcceptType.指定部门:
EyouSoft.Model.CompanyStructure.Department departModel = new DAL.CompanyStructure.Department().GetModel(AcceptId);
if (departModel != null)
{
AcceptName = departModel.DepartName;
}
departModel = null;
break;
case EyouSoft.Model.EnumType.PersonalCenterStructure.AcceptType.指定人:
EyouSoft.Model.CompanyStructure.ContactPersonInfo userModel = new DAL.CompanyStructure.CompanyUser().GetUserBasicInfo(AcceptId);
if (userModel != null)
{
AcceptName = userModel.ContactName;
}
userModel = null;
break;
}
return(AcceptName);
}
/// <summary>
/// 根据接收对象编号获取接收名称
/// </summary>
/// <param name="AcceptId">接收对象编号</param>
/// <returns></returns>
private string GetAcceptName(int AcceptId)
{
string AcceptName = string.Empty;
EyouSoft.Model.CompanyStructure.ContactPersonInfo userModel = new DAL.CompanyStructure.CompanyUser().GetUserBasicInfo(AcceptId);
if (userModel != null)
{
AcceptName = userModel.ContactName;
}
userModel = null;
return(AcceptName);
}
/// <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>
/// 根据查询实体生成Where子句
/// </summary>
/// <param name="model">查询实体</param>
/// <param name="HaveUserIds">用户Id集合，半角逗号分割</param>
/// <param name="strOrder">排序语句</param>
/// <returns>Where子句</returns>
private string GetSqlWhere(EyouSoft.Model.StatisticStructure.QueryInayatStatistic model, string HaveUserIds
, ref string strOrder)
{
if (model == null)
{
return(string.Empty);
}
StringBuilder strSqlWhere = new StringBuilder();
if (model.CompanyId > 0)
{
strSqlWhere.AppendFormat(" and SellCompanyId = {0} ", model.CompanyId);
}
if (model.AreaId > 0)
{
strSqlWhere.AppendFormat(" and AreaId = {0} ", model.AreaId);
}
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 TourOperatorId in ({0}) ", HaveUserIds);
}
if (model.StartTime.HasValue)
{
strSqlWhere.AppendFormat(" and datediff(dd,'{0}',IssueTime) >= 0 ", model.StartTime.Value.ToShortDateString());
}
if (model.EndTime.HasValue)
{
strSqlWhere.AppendFormat(" and datediff(dd,IssueTime,'{0}') >= 0 ", model.EndTime.Value.ToShortDateString());
}
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.已成交);
}
if (model.LeaveDateStart.HasValue)
{
strSqlWhere.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());
}
if (model.TourType.HasValue)
{
strSqlWhere.AppendFormat(" AND TourClassId={0} ", (int)model.TourType.Value);
}
switch (model.OrderIndex)
{
case 0:
strOrder = " AreaId asc ";
break;
case 1:
strOrder = " AreaId desc ";
break;
case 2:
strOrder = " DepartId asc ";
break;
case 3:
strOrder = " DepartId desc ";
break;
case 4:
strOrder = " CurrMonth asc ";
break;
case 5:
strOrder = " CurrMonth desc ";
break;
}
return(strSqlWhere.ToString());
}
/// <summary>
/// 根据查询实体生成SqlWhere语句
/// </summary>
/// <param name="model">查询实体</param>
/// <param name="us">用户信息集合</param>
/// <returns>SqlWhere语句</returns>
private string GetSqlWhereByQueryModel(EyouSoft.Model.StatisticStructure.MQueryRefundStatistic model, string us)
{
if (model == null || model.CompanyId <= 0)
{
return(string.Empty);
}
StringBuilder strWhere = new StringBuilder();
strWhere.AppendFormat(" SellCompanyId = {0} ", model.CompanyId);
if (!string.IsNullOrEmpty(model.TourNo))
{
strWhere.AppendFormat(" and TourNo like '%{0}%' ", model.TourNo);
}
if (!string.IsNullOrEmpty(model.RouteName))
{
strWhere.AppendFormat(" and RouteName like '%{0}%' ", model.RouteName);
}
if (model.LeaveDateStart.HasValue)
{
strWhere.AppendFormat(" and datediff(dd,'{0}',LeaveDate) >= 0 ", model.LeaveDateStart.Value.ToShortDateString());
}
if (model.LeaveDateEnd.HasValue)
{
strWhere.AppendFormat(" and datediff(dd,LeaveDate,'{0}') >= 0 ", model.LeaveDateEnd.Value.ToShortDateString());
}
if (!string.IsNullOrEmpty(model.FligthSegment) || model.AireLine.HasValue)
{
strWhere.Append(" and exists (select 1 from tbl_CustomerRefundFlight where tbl_CustomerRefundFlight.RefundId = ID and exists (select 1 from tbl_PlanTicketFlight where tbl_PlanTicketFlight.ID = tbl_CustomerRefundFlight.FlightId ");
if (!string.IsNullOrEmpty(model.FligthSegment))
{
strWhere.AppendFormat(" and tbl_PlanTicketFlight.FligthSegment like '%{0}%' ", model.FligthSegment);
}
if (model.AireLine.HasValue)
{
strWhere.AppendFormat(" and tbl_PlanTicketFlight.AireLine = {0} ", (int)model.AireLine.Value);
}
strWhere.Append(" )) ");
}
if (!string.IsNullOrEmpty(model.BuyCompanyName))
{
strWhere.AppendFormat(" and BuyCompanyName like '%{0}%' ", model.BuyCompanyName);
}
if (model.BuyCompanyId > 0)
{
strWhere.AppendFormat(" and BuyCompanyID = {0} ", model.BuyCompanyId);
}
if (!string.IsNullOrEmpty(us))
{
strWhere.AppendFormat(" AND TourOperatorId IN({0}) ", us);
}
var opIds = new List <int>();
if (model.OperatorIds != null && model.OperatorIds.Length > 0)
{
foreach (var i in model.OperatorIds)
{
if (i <= 0)
{
continue;
}
if (opIds.Contains(i))
{
continue;
}
opIds.Add(i);
}
}
if (model.DepIds != null && model.DepIds.Length > 0)
{
int[] userIds = new DAL.CompanyStructure.CompanyUser().GetUserIdsByDepartIds(model.DepIds);
if (userIds != null && userIds.Length > 0)
{
foreach (var i in userIds)
{
if (i <= 0)
{
continue;
}
if (opIds.Contains(i))
{
continue;
}
opIds.Add(i);
}
}
}
if (opIds != null && opIds.Count > 0)
{
string strIds = string.Empty;
foreach (int i in opIds)
{
if (i <= 0)
{
continue;
}
strIds += i + ",";
}
strIds = strIds.Trim(',');
if (!string.IsNullOrEmpty(strIds))
{
strWhere.AppendFormat(" and OperatorId in ({0}) ", strIds);
}
}
return(strWhere.ToString());
}
