/// <summary>
/// 拼接查询条件
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
string GetSQL(EyouSoft.Model.StatisticStructure.QueryEarningsStatistic model)
{
StringBuilder cmdText = new StringBuilder();
if (model.TourType.HasValue)
{
cmdText.AppendFormat(" AND A.TourType={0} ", (int)model.TourType.Value);
}
if (model.LeaveDateEnd.HasValue)
{
cmdText.AppendFormat(" AND A.LeaveDate<'{0}' ", model.LeaveDateEnd.Value.AddDays(1));
}
if (model.LeaveDateStart.HasValue)
{
cmdText.AppendFormat(" AND A.LeaveDate>'{0}' ", model.LeaveDateStart.Value.AddDays(-1));
}
if (model.CheckDateEnd.HasValue)
{
cmdText.AppendFormat(" AND A.EndDateTime<'{0}' ", model.CheckDateEnd.Value.AddDays(1));
}
if (model.CheckDateStart.HasValue)
{
cmdText.AppendFormat(" AND A.EndDateTime>'{0}' ", model.CheckDateStart.Value.AddDays(-1));
}
if (model.SaleIds != null && model.SaleIds.Length > 0)
{
cmdText.AppendFormat(" AND A.OperatorId IN({0}) ", EyouSoft.Toolkit.Utils.GetSqlIdStrByArray(model.SaleIds));
}
if (model.DepartIds != null && model.DepartIds.Length > 0)
{
cmdText.AppendFormat(" AND A.DepartId IN({0}) ", EyouSoft.Toolkit.Utils.GetSqlIdStrByArray(model.DepartIds));
}
if (model.AreaId > 0)
{
cmdText.AppendFormat(" AND A.AreaId={0} ", model.AreaId);
}
return(cmdText.ToString());
}
private void IntiTourPerList()
{
EyouSoft.BLL.TourStructure.Tour TourBll = new EyouSoft.BLL.TourStructure.Tour(SiteUserInfo);
//查询实体
//默认获取当年的数据
DateTime?CurrStartTime = new DateTime(DateTime.Now.Year, 1, 1);
DateTime?CurrEndTime = new DateTime(DateTime.Now.Year, 12, 31);
EyouSoft.Model.StatisticStructure.QueryEarningsStatistic model = new EyouSoft.Model.StatisticStructure.QueryEarningsStatistic();
PageIndex = Utils.GetInt(Utils.GetQueryStringValue("Page"), 1);
TourType = Utils.GetQueryStringValue("TourType");
if (Utils.GetQueryStringValue("AreaId") == "0")
{
TourType = "2";
}
if (Request.QueryString["IsByTime"] != null && Request.QueryString["IsByTime"] == "1")
{
LeaveTourStartDate = Utils.GetDateTimeNullable(Request.QueryString["LeaveTourStartDate"], CurrStartTime);
LeaveTourEndDate = Utils.GetDateTimeNullable(Request.QueryString["LeaveTourEndDate"], CurrEndTime);
}
else
{
LeaveTourStartDate = Utils.GetDateTimeNullable(Request.QueryString["LeaveStartDate"]);
LeaveTourEndDate = Utils.GetDateTimeNullable(Request.QueryString["LeaveEndDate"]);
}
CheckStartDate = Utils.GetDateTimeNullable(Request.QueryString["CheckStartTime"]);
CheckEndDate = Utils.GetDateTimeNullable(Request.QueryString["CheckEndTime"]);
AreaId = Utils.GetInt(Utils.GetQueryStringValue("AreaId"), 0);
DepartId = Utils.GetQueryStringValue("DepartId");
Year = Utils.GetInt(Request.QueryString["CuYear"], 0); //年份
Month = Utils.GetInt(Request.QueryString["CuMoth"], 0); //月份
if (TourType != "" && int.Parse(TourType) > 0)
{
model.TourType = (EyouSoft.Model.EnumType.TourStructure.TourType) int.Parse(TourType);
}
model.OrderIndex = 0;
model.LeaveDateStart = LeaveTourStartDate;
model.LeaveDateEnd = LeaveTourEndDate;
model.CheckDateStart = CheckStartDate;
model.CheckDateEnd = CheckEndDate;
model.AreaId = AreaId;
model.CurrYear = Year;
model.CurrMonth = Month;
model.DepartIds = Utils.GetIntArray(DepartId, ",");
list = TourBll.GetToursLYTJ(this.SiteUserInfo.CompanyID, PageSize, PageIndex, ref RecordCount, model);
if (list != null && list.Count > 0)
{
this.crp_GetTourDetailList.DataSource = list;
this.crp_GetTourDetailList.DataBind();
BindPage();
this.tbl_ExportPage.Visible = true;
}
else
{
this.crp_GetTourDetailList.EmptyText = "<tr><td colspan='8' bgcolor='#e3f1fc' height='50px' align='center'>暂时没有数据！</td></tr>";
this.tbl_ExportPage.Visible = false;
}
//释放资源
list = null;
model = null;
TourBll = null;
}
/// <summary>
/// 根据订单XML获取订单部分信息
/// </summary>
/// <param name="strOrderXML">订单XML</param>
/// <param name="model">查询实体</param>
/// <param name="TotalAllIncome">团队总收入</param>
/// <param name="TourPeopleNum">团队人数</param>
/// <param name="tmpList">销售员集合</param>
/// <param name="IsIList">是否为集合赋值</param>
private void GetOrderInfoByXML(string strOrderXML, EyouSoft.Model.StatisticStructure.QueryEarningsStatistic model
, ref decimal TotalAllIncome, ref int TourPeopleNum
, ref IList <Model.StatisticStructure.StatisticOperator> tmpList, bool IsIList)
{
if (string.IsNullOrEmpty(strOrderXML))
{
return;
}
XElement xRoot = XElement.Parse(strOrderXML);
var xRows = Utils.GetXElements(xRoot, "tmpVes");
if (xRows == null || xRows.Count() <= 0)
{
return;
}
TotalAllIncome = 0;
TourPeopleNum = 0;
if (tmpList == null && IsIList)
{
tmpList = new List <Model.StatisticStructure.StatisticOperator>();
}
Model.StatisticStructure.StatisticOperator tmpModel = null;
foreach (var t in xRows)
{
string strTmpXML = Utils.GetXAttributeValue(t, "Orders");
if (string.IsNullOrEmpty(strTmpXML))
{
continue;
}
var TmpXRows = Utils.GetXElements(XElement.Parse(strTmpXML), "row");
if (TmpXRows == null || TmpXRows.Count() <= 0)
{
continue;
}
foreach (var tmp in TmpXRows)
{
int SaleId = Utils.GetInt(Utils.GetXAttributeValue(tmp, "SalerId"));
int OrderState = Utils.GetInt(Utils.GetXAttributeValue(tmp, "OrderState"));
if (model != null)
{
//销售员查询
if (SaleId > 0 && model.SaleIds != null && model.SaleIds.Length > 0 && !model.SaleIds.Contains(SaleId))
{
continue;
}
//订单统计方式
if (model.ComputeOrderType == EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType.统计有效订单)
{
if (OrderState == (int)Model.EnumType.TourStructure.OrderState.受理 || OrderState == (int)Model.EnumType.TourStructure.OrderState.留位过期)
{
continue;
}
}
else if (model.ComputeOrderType == EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType.统计确认成交订单 && OrderState != (int)Model.EnumType.TourStructure.OrderState.已成交)
{
continue;
}
}
TourPeopleNum += (Utils.GetInt(Utils.GetXAttributeValue(tmp, "PeopleNumber")) - Utils.GetInt(Utils.GetXAttributeValue(tmp, "LeaguePepoleNum")));
TotalAllIncome += Utils.GetDecimal(Utils.GetXAttributeValue(tmp, "FinanceSum"));
if (!IsIList)
{
continue;
}
tmpModel = new EyouSoft.Model.StatisticStructure.StatisticOperator();
tmpModel.OperatorId = Utils.GetInt(Utils.GetXAttributeValue(tmp, "SalerId"));
tmpModel.OperatorName = Utils.GetXAttributeValue(tmp, "SalerName");
tmpList.Add(tmpModel);
}
}
if (tmpList != null)
{
tmpList = tmpList.Distinct(new DistinctByStatisticOperator()).ToList();
}
}
/// <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.QueryEarningsStatistic model, string HaveUserIds
, ref string strOrder)
{
if (model == null)
{
return(string.Empty);
}
StringBuilder strSqlWhere = new StringBuilder();
string strIds = string.Empty;
if (model.CompanyId > 0)
{
strSqlWhere.AppendFormat(" and ves.CompanyId = {0} ", model.CompanyId);
}
if (model.TourType.HasValue)
{
strSqlWhere.AppendFormat(" and ves.TourType = {0} ", (int)model.TourType.Value);
}
if (model.DepartIds != null && model.DepartIds.Length > 0)
{
strIds = string.Empty;
foreach (int i in model.DepartIds)
{
if (i <= 0)
{
continue;
}
strIds += i.ToString() + ",";
}
strIds = strIds.Trim(',');
if (!string.IsNullOrEmpty(strIds))
{
strSqlWhere.AppendFormat(" and ves.DepartId in ({0}) ", strIds);
}
}
if (model.SaleIds != null && model.SaleIds.Length > 0)
{
strIds = string.Empty;
foreach (int i in model.SaleIds)
{
if (i <= 0)
{
continue;
}
strIds += i.ToString() + ",";
}
strIds = strIds.Trim(',');
if (!string.IsNullOrEmpty(strIds))
{
strSqlWhere.AppendFormat(" and ves.TourId in (select distinct tbl_TourOrder.TourId from tbl_TourOrder where SalerId in ({0})) ", strIds);
}
}
if (!string.IsNullOrEmpty(HaveUserIds))
{
strSqlWhere.AppendFormat(" and ves.OperatorId in ({0}) ", HaveUserIds);
}
if (model.LeaveDateStart.HasValue)
{
strSqlWhere.AppendFormat(" and datediff(dd,'{0}',ves.LeaveDate) >= 0 ", model.LeaveDateStart.Value.ToShortDateString());
}
if (model.LeaveDateEnd.HasValue)
{
strSqlWhere.AppendFormat(" and datediff(dd,ves.LeaveDate,'{0}') >= 0 ", model.LeaveDateEnd.Value.ToShortDateString());
}
if (model.CheckDateStart.HasValue)
{
strSqlWhere.AppendFormat(" and datediff(dd,'{0}',ves.EndDateTime) >= 0 ", model.CheckDateStart.Value.ToShortDateString());
}
if (model.CheckDateEnd.HasValue)
{
strSqlWhere.AppendFormat(" and datediff(dd,ves.EndDateTime,'{0}') >= 0 ", model.CheckDateEnd.Value.ToShortDateString());
}
if (model.AreaId > 0)
{
strSqlWhere.AppendFormat(" and ves.AreaId = {0} ", model.AreaId);
}
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 = " TourType asc ";
break;
case 5:
strOrder = " TourType desc ";
break;
case 6:
strOrder = " CurrYear asc,CurrMonth asc ";
break;
case 7:
strOrder = " CurrYear desc,CurrMonth desc ";
break;
}
return(strSqlWhere.ToString());
}
/// <summary>
/// 获取利润--区域统计
/// </summary>
/// <param name="model">利润统计查询实体</param>
/// <param name="HaveUserIds">用户Id集合，半角逗号分割</param>
/// <returns></returns>
public IList <EyouSoft.Model.StatisticStructure.EarningsAreaStatistic> GetEarningsAreaStatistic(EyouSoft.Model.StatisticStructure.QueryEarningsStatistic model, string HaveUserIds)
{
IList <EyouSoft.Model.StatisticStructure.EarningsAreaStatistic> list = new List <EyouSoft.Model.StatisticStructure.EarningsAreaStatistic>();
string strOrder = string.Empty;
string strWhere = this.GetSqlWhere(model, HaveUserIds, ref strOrder);
StringBuilder strSql = new StringBuilder(" select ");
strSql.Append(" AreaId,AreaName, ");
strSql.Append(" count(TourId) as TourCount, ");
strSql.Append(" sum(TotalAllExpenses) as TotalAllExpenses, ");
strSql.Append(" 0 as GrossProfit, ");
strSql.Append(" sum(DistributionAmount) as DistributionAmount, ");
strSql.AppendFormat(" (select Logistics from View_EarningsStatistic as tmpVes where tmpVes.AreaId = ves.AreaId {0} for xml auto,root('root')) as Logistics, ", strWhere.Replace("ves.", "tmpVes."));
strSql.AppendFormat(" (select Orders from View_EarningsStatistic as tmpVes where tmpVes.AreaId = ves.AreaId and len(Orders) > 1 {0} for xml auto,root('root')) as Orders, ", strWhere.Replace("ves.", "tmpVes."));
strSql.Append(" Sum(TotalOtherIncome) as TotalOtherIncome");
strSql.Append(" from View_EarningsStatistic as ves ");
strSql.AppendFormat(" where IsDelete = '0' ");
strSql.AppendFormat(" {0} ", strWhere);
strSql.Append(" group by AreaId,AreaName ");
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.EarningsAreaStatistic tmpModel = null;
decimal FinanceSum = 0;
int PeopleNumber = 0;
while (dr.Read())
{
tmpModel = new EyouSoft.Model.StatisticStructure.EarningsAreaStatistic();
if (!dr.IsDBNull(0))
{
tmpModel.AreaId = dr.GetInt32(0);
}
if (!dr.IsDBNull(1))
{
tmpModel.AreaName = dr.GetString(1);
}
if (!dr.IsDBNull(2))
{
tmpModel.TourNum = dr.GetInt32(2);
}
if (!dr.IsDBNull(3))
{
tmpModel.GrossOut = dr.GetDecimal(3);
}
if (!dr.IsDBNull(4))
{
tmpModel.TourGross = 0;//dr.GetDecimal(4);
}
if (!dr.IsDBNull(5))
{
tmpModel.TourShare = dr.GetDecimal(5);
}
if (!dr.IsDBNull(6))
{
tmpModel.Logistics = this.GetStatisticOperatorByXML(dr.GetString(6));
}
FinanceSum = 0;
PeopleNumber = 0;
IList <Model.StatisticStructure.StatisticOperator> tmp = null;
if (!dr.IsDBNull(7))
{
this.GetOrderInfoByXML(dr.GetString(7), model, ref FinanceSum, ref PeopleNumber, ref tmp, false);
}
tmpModel.GrossIncome = FinanceSum; //此处只计算团款收入
tmpModel.TourPeopleNum = PeopleNumber;
if (!dr.IsDBNull(8))
{
tmpModel.GrossIncome += dr.GetDecimal(8); //此处加上杂费收入
}
//团队毛利=总收入-总支出
tmpModel.TourGross = tmpModel.GrossIncome - tmpModel.GrossOut;
list.Add(tmpModel);
}
}
return(list);
}
private void InitProAreaStaticlist()
{
#region 页面参数初始化
PageIndex = Utils.GetInt(Request.QueryString["Page"], 1);
DeapartName = Utils.GetQueryStringValue("departName");
OperatorName = Utils.GetQueryStringValue("OperatorName");
OperatorId = Utils.GetQueryStringValue("OperatorId");
StartDate = Utils.GetDateTimeNullable(Request.QueryString["StartDate"], new DateTime(DateTime.Now.Year, 1, 1));
EndDate = Utils.GetDateTimeNullable(Request.QueryString["EndDate"], new DateTime(DateTime.Now.Year, 12, 31));
DepartId = Utils.GetQueryStringValue("DepartmentId");
CheckSDate = Utils.GetDateTimeNullable(Request.QueryString["CheckSDate"]);
CheckEDate = Utils.GetDateTimeNullable(Request.QueryString["CheckEDate"]);
#endregion
#region 表单控件初始化
UCSelectDepartment1.GetDepartmentName = DeapartName;
UCSelectDepartment1.GetDepartId = DepartId;
SelectOperator.OperName = OperatorName;
SelectOperator.OperId = OperatorId;
txtStartDate.Value = StartDate.HasValue ? StartDate.Value.ToString("yyyy-MM-dd") : "";
txtEndDate.Value = EndDate.HasValue ? EndDate.Value.ToString("yyyy-MM-dd") : "";
txtCheckStartTime.Value = CheckSDate.HasValue ? CheckSDate.Value.ToString("yyyy-MM-dd") : "";
txtCheckEndTime.Value = CheckEDate.HasValue ? CheckEDate.Value.ToString("yyyy-MM-dd") : "";
#endregion
#region 调用底层方法 查询Model赋值
EyouSoft.Model.StatisticStructure.QueryEarningsStatistic model = new EyouSoft.Model.StatisticStructure.QueryEarningsStatistic();
EyouSoft.BLL.StatisticStructure.EarningsStatistic EarningBll = new EyouSoft.BLL.StatisticStructure.EarningsStatistic(this.SiteUserInfo);
model.CompanyId = base.SiteUserInfo.CompanyID;
model.OrderIndex = 4;
model.LeaveDateStart = StartDate;
model.LeaveDateEnd = EndDate;
if (!string.IsNullOrEmpty(OperatorId))
{
model.SaleIds = Utils.GetIntArray(OperatorId, ",");
}
if (!string.IsNullOrEmpty(DepartId))
{
model.DepartIds = Utils.GetIntArray(DepartId, ",");
}
//设置查询model的ComputeOrderType值
EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType?computerOrderType = new EyouSoft.BLL.CompanyStructure.CompanySetting().GetComputeOrderType(SiteUserInfo.CompanyID);
if (computerOrderType.HasValue)
{
model.ComputeOrderType = computerOrderType.Value;
}
model.CheckDateStart = CheckSDate;
model.CheckDateEnd = CheckEDate;
IList <EyouSoft.Model.StatisticStructure.EarningsTypeStatistic> statisticList = new List <EyouSoft.Model.StatisticStructure.EarningsTypeStatistic>();
statisticList = EarningBll.GetEarningsTypeStatistic(model);
#region 统计 by 田想兵 3.9
int teamNum = 0;
decimal inMoney = 0;
decimal outMoney = 0;
decimal teamGross = 0;
decimal pepoleGross = 0;
decimal profitallot = 0;
decimal comProfit = 0;
int peopleNum = 0;
int propleSum = 0;
foreach (var v in statisticList)
{
peopleNum += v.TourPeopleNum;
teamNum += v.TourNum;
inMoney += v.GrossIncome;
outMoney += v.GrossOut;
teamGross += v.TourGross;
//pepoleGross += v.PeopleGross;
profitallot += v.TourShare;
comProfit += v.CompanyShare;
propleSum += v.TourPeopleNum;
}
if (peopleNum == 0)
{
pepoleGross = 0;
}
else
{
pepoleGross = teamGross / peopleNum;
}
lt_teamNum.Text = teamNum.ToString();
lt_InMoney.Text = inMoney.ToString("###,##0.00");
lt_outMoney.Text = outMoney.ToString("###,##0.00");
lt_teamgross_profit.Text = teamGross.ToString("###,##0.00");
lt_pepolegross_profit.Text = pepoleGross.ToString("###,##0.00");
lt_profitallot.Text = profitallot.ToString("###,##0.00");
lt_comProfit.Text = comProfit.ToString("###,##0.00");
lt_peopleSum.Text = propleSum.ToString();
if (inMoney != 0)
{
lt_lirunlv.Text = (Convert.ToDouble(comProfit) / Convert.ToDouble(inMoney)).ToString("0.00%");
}
else
{
lt_lirunlv.Text = "0.00%";
}
#endregion
ProTypeStaList = EyouSoft.Common.Function.SelfExportPage.GetList <EyouSoft.Model.StatisticStructure.EarningsTypeStatistic>(PageIndex, PageSize, out RecordCount, EarningBll.GetEarningsTypeStatistic(model));
#endregion
#region 数据源绑定
if (ProTypeStaList != null && ProTypeStaList.Count != 0)
{
this.crp_GetProTypeList.DataSource = ProTypeStaList;
this.crp_GetProTypeList.DataBind();
BindPage();//绑定分页
}
else
{
this.tbl_ExportPage.Visible = false;
this.crp_GetProTypeList.EmptyText = "<tr><td colspan='9' id=\"EmptyData\" bgcolor='#e3f1fc' height='50px' align='center'>暂时没有数据！</td></tr>";
}
#endregion
RegisterScript(string.Format("var recordCount={0};", RecordCount));
}
private void InitProTimeStaticlist()
{
#region 获取参数
PageIndex = Utils.GetInt(Request.QueryString["Page"], 1);
RouteAreaName = Utils.GetQueryStringValue("RouteAreaName");
leaveTourStartDate = Utils.GetDateTimeNullable(Request.QueryString["leaveTourStarDate"], new DateTime(DateTime.Now.Year, 1, 1));
leaveTourEndDate = Utils.GetDateTimeNullable(Request.QueryString["leaveTourEndDate"], new DateTime(DateTime.Now.Year, 12, 31));
checkStartDate = Utils.GetDateTimeNullable(Request.QueryString["checkStartDate"]);
checkEndDate = Utils.GetDateTimeNullable(Request.QueryString["checkEndDate"]);
TourType = Utils.GetQueryStringValue("TourType");
OperatorName = Utils.GetQueryStringValue("OperatorName");
OperatorId = Utils.GetQueryStringValue("OperatorId");
DepartName = Utils.GetQueryStringValue("deptnames");
DepartId = Utils.GetQueryStringValue("deptids");
#endregion
#region 初始化表单值
this.txtcheckStartDate.Value = checkStartDate.HasValue ? checkStartDate.Value.ToString("yyyy-MM-dd") : "";
this.txtcheckEndDate.Value = checkEndDate.HasValue ? checkEndDate.Value.ToString("yyyy-MM-dd") : "";
this.txtLeaveStarDate.Value = leaveTourStartDate.HasValue ? leaveTourStartDate.Value.ToString("yyyy-MM-dd") : "";
this.txtLeaveEndDate.Value = leaveTourEndDate.HasValue ? leaveTourEndDate.Value.ToString("yyyy-MM-dd") : "";
if (TourType != "")//团队类型
{
this.TourTypeList1.TourType = int.Parse(TourType);
}
if (RouteAreaName != "")//线路区域名称
{
this.RouteAreaList1.RouteAreaId = int.Parse(RouteAreaName);
}
this.SelectOperator.OperName = OperatorName; //销售员
this.SelectOperator.OperId = OperatorId;
this.UCSelectDepartment1.GetDepartmentName = DepartName; //部门
this.UCSelectDepartment1.GetDepartId = DepartId;
#endregion
#region 调用底层方法 Model赋值
EyouSoft.Model.StatisticStructure.QueryEarningsStatistic model = new EyouSoft.Model.StatisticStructure.QueryEarningsStatistic();
EyouSoft.BLL.StatisticStructure.EarningsStatistic EarningBll = new EyouSoft.BLL.StatisticStructure.EarningsStatistic(this.SiteUserInfo);
if (RouteAreaName != "" && RouteAreaName != "0")
{
model.AreaId = int.Parse(RouteAreaName);
}
if (TourType != "" && TourType != "-1")
{
model.TourType = (EyouSoft.Model.EnumType.TourStructure.TourType) int.Parse(TourType);
}
model.OrderIndex = 6;
model.LeaveDateStart = leaveTourStartDate;
model.LeaveDateEnd = leaveTourEndDate;
//设置查询model的ComputeOrderType值
EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType?computerOrderType = new EyouSoft.BLL.CompanyStructure.CompanySetting().GetComputeOrderType(SiteUserInfo.CompanyID);
if (computerOrderType.HasValue)
{
model.ComputeOrderType = computerOrderType.Value;
}
if (OperatorId != "")
{
model.SaleIds = Utils.GetIntArray(OperatorId, ",");
}
if (DepartId != "")
{
model.DepartIds = Utils.GetIntArray(DepartId, ",");
}
model.CheckDateStart = checkStartDate;
model.CheckDateEnd = checkEndDate;
IList <EyouSoft.Model.StatisticStructure.EarningsTimeStatistic> statisticList = new List <EyouSoft.Model.StatisticStructure.EarningsTimeStatistic>();
model.CompanyId = CurrentUserCompanyID;
statisticList = EarningBll.GetEarningsTimeStatistic(model);
#region 统计 by 田想兵 3.9
int teamNum = 0;
decimal inMoney = 0;
decimal outMoney = 0;
decimal teamGross = 0;
decimal pepoleGross = 0;
decimal profitallot = 0;
decimal comProfit = 0;
int peopleNum = 0;
int propleSum = 0;
foreach (var v in statisticList)
{
peopleNum += v.TourPeopleNum;
teamNum += v.TourNum;
inMoney += v.GrossIncome;
outMoney += v.GrossOut;
teamGross += v.TourGross;
//pepoleGross += v.PeopleGross;
profitallot += v.TourShare;
comProfit += v.CompanyShare;
propleSum += v.TourPeopleNum;
}
if (peopleNum == 0)
{
pepoleGross = 0;
}
else
{
pepoleGross = teamGross / peopleNum;
}
lt_teamNum.Text = teamNum.ToString();
lt_InMoney.Text = inMoney.ToString("###,##0.00");
lt_outMoney.Text = outMoney.ToString("###,##0.00");
lt_teamgross_profit.Text = teamGross.ToString("###,##0.00");
lt_pepolegross_profit.Text = pepoleGross.ToString("###,##0.00");
lt_profitallot.Text = profitallot.ToString("###,##0.00");
lt_comProfit.Text = comProfit.ToString("###,##0.00");
lt_peopleSum.Text = propleSum.ToString();
if (inMoney != 0)
{
lt_lirunlv.Text = (Convert.ToDouble(comProfit) / Convert.ToDouble(inMoney)).ToString("0.00%");
}
else
{
lt_lirunlv.Text = "0.00%";
}
#endregion
list = EyouSoft.Common.Function.SelfExportPage.GetList <EyouSoft.Model.StatisticStructure.EarningsTimeStatistic>(PageIndex, PageSize, out RecordCount, statisticList);
#endregion
if (list != null && list.Count != 0)
{
this.tbl_ExportPage.Visible = true;
this.crp_GetProTimeList.DataSource = list;
this.crp_GetProTimeList.DataBind();
BindPage();//绑定分页
}
else
{
this.tbl_ExportPage.Visible = false;
this.crp_GetProTimeList.EmptyText = "<tr><td colspan='8' id=\"EmptyData\" bgcolor='#e3f1fc' height='50px' align='center'>暂时没有数据！</td></tr>";
}
//释放资源
model = null;
EarningBll = null;
RegisterScript(string.Format("var recordCount={0};", RecordCount));
}
/// <summary>
/// 获取利润--区域统计
/// </summary>
/// <param name="model">利润统计查询实体</param>
/// <param name="HaveUserIds">用户Id集合，半角逗号分割</param>
/// <returns></returns>
public IList <EyouSoft.Model.StatisticStructure.EarningsAreaStatistic> GetEarningsAreaStatistic(EyouSoft.Model.StatisticStructure.QueryEarningsStatistic model, string HaveUserIds)
{
IList <EyouSoft.Model.StatisticStructure.EarningsAreaStatistic> items = new List <EyouSoft.Model.StatisticStructure.EarningsAreaStatistic>();
StringBuilder cmdText = new StringBuilder();
#region SQL
cmdText.Append(" SELECT A.AreaId ");
cmdText.Append(" ,A.AreaName ");
cmdText.Append(" ,COUNT(A.TourId) AS TuanDuiShu ");
cmdText.Append(" ,SUM(A.TotalAllExpenses) AS ZhiChuJinE ");
cmdText.Append(" ,SUM(A.DistributionAmount) AS LiRunFenPeiJinE ");
if (model.ComputeOrderType == EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType.统计确认成交订单)
{
cmdText.Append(" ,SUM(A.TotalOtherIncome+A.ChengJiaoJinE) AS ShouRuJinE ");
cmdText.Append(" ,SUM(A.ChengJiaoRenShu) AS RenShu ");
}
else
{
cmdText.Append(" ,SUM(A.TotalOtherIncome+A.YouXiaoJinE) AS ShouRuJinE ");
cmdText.Append(" ,SUM(A.YouXiaoRenShu) AS RenShu ");
}
cmdText.Append(" FROM [View_EarningsStatistic] AS A ");
cmdText.AppendFormat(" WHERE A.CompanyId={0} ", model.CompanyId);
if (!string.IsNullOrEmpty(HaveUserIds))
{
cmdText.AppendFormat(" AND A.OperatorId IN({0}) ", HaveUserIds);
}
cmdText.Append(GetSQL(model));
cmdText.Append(" GROUP BY A.AreaId,A.AreaName ");
cmdText.Append(" ORDER BY A.AreaId ");
if (model.OrderIndex == 1)
{
cmdText.Append(" DESC ");
}
#endregion
DbCommand cmd = _db.GetSqlStringCommand(cmdText.ToString());
using (IDataReader rdr = DbHelper.ExecuteReader(cmd, _db))
{
//int index0 = rdr.GetOrdinal("AreaId");
while (rdr.Read())
{
var item = new EyouSoft.Model.StatisticStructure.EarningsAreaStatistic();
item.AreaId = rdr.GetInt32(rdr.GetOrdinal("AreaId"));
item.AreaName = rdr["AreaName"].ToString();
if (!rdr.IsDBNull(rdr.GetOrdinal("ShouRuJinE")))
{
item.GrossIncome = rdr.GetDecimal(rdr.GetOrdinal("ShouRuJinE"));
}
if (!rdr.IsDBNull(rdr.GetOrdinal("ZhiChuJinE")))
{
item.GrossOut = rdr.GetDecimal(rdr.GetOrdinal("ZhiChuJinE"));
}
if (!rdr.IsDBNull(rdr.GetOrdinal("TuanDuiShu")))
{
item.TourNum = rdr.GetInt32(rdr.GetOrdinal("TuanDuiShu"));
}
if (!rdr.IsDBNull(rdr.GetOrdinal("LiRunFenPeiJinE")))
{
item.TourShare = rdr.GetDecimal(rdr.GetOrdinal("LiRunFenPeiJinE"));
}
if (!rdr.IsDBNull(rdr.GetOrdinal("RenShu")))
{
item.TourPeopleNum = rdr.GetInt32(rdr.GetOrdinal("RenShu"));
}
item.TourGross = item.GrossIncome - item.GrossOut;
items.Add(item);
}
}
return(items);
}
