protected void BindSum(EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic model)
{
Decimal total = 0; //总收入
Decimal ys = 0; //应收
Decimal ws = 0; //未收
//BLL声明
EyouSoft.BLL.StatisticStructure.StatAllOut outBLL = new EyouSoft.BLL.StatisticStructure.StatAllOut(SiteUserInfo);
//获取总计数据
outBLL.GetAllTotalAmount(model, ref total, ref ys);
if (total != 0)
{
res = true;
}
//计算未收金额
ws = total - ys;
//总金额赋值_已结清
this.sum_total_account.Text = EyouSoft.Common.Utils.FilterEndOfTheZeroString(EyouSoft.Common.Utils.GetDecimal(total.ToString()).ToString("0.00"));
//总金额赋值_所有
this.sum_total_noaccount.Text = EyouSoft.Common.Utils.FilterEndOfTheZeroString(EyouSoft.Common.Utils.GetDecimal(total.ToString()).ToString("0.00"));
//应收金额赋值_已结清
this.sum_ys_account.Text = EyouSoft.Common.Utils.FilterEndOfTheZeroString(EyouSoft.Common.Utils.GetDecimal(ys.ToString()).ToString("0.00"));
//应收金额赋值_所有
this.sum_ys_noaccount.Text = EyouSoft.Common.Utils.FilterEndOfTheZeroString(EyouSoft.Common.Utils.GetDecimal(ys.ToString()).ToString("0.00"));
//未收金额赋值_已结清
this.sum_ws_account.Text = EyouSoft.Common.Utils.FilterEndOfTheZeroString(EyouSoft.Common.Utils.GetDecimal(ws.ToString()).ToString("0.00"));
//未收金额赋值_所有
this.sum_ws_noaccount.Text = EyouSoft.Common.Utils.FilterEndOfTheZeroString(EyouSoft.Common.Utils.GetDecimal(ws.ToString()).ToString("0.00"));
}
/// <summary>
/// 获取支出对帐单的汇总信息(统计分析-支出对账单列表合计)
/// </summary>
/// <param name="queryInfo">查询实体</param>
/// <param name="totalAmount">总支出</param>
/// <param name="hasCheckAmount">已付</param>
/// <param name="haveUserIds">用户Id集合，半角逗号分割</param>
public void GetAllTotalAmount(EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic queryInfo
, ref decimal totalAmount, ref decimal hasCheckAmount, string haveUserIds)
{
if (queryInfo == null || queryInfo.CompanyId <= 0)
{
return;
}
string orderByStr = string.Empty;
string Query = this.GetSqlWhereByQuery(queryInfo, haveUserIds, ref orderByStr);
StringBuilder strSql = new StringBuilder();
strSql.Append(" select sum(a.TotalAmount) as AllTotalAmount,sum(a.HasCheckAmount) as AllHasCheckAmount from (select sum(TotalAmount) as TotalAmount,sum(HasCheckAmount) as HasCheckAmount from tbl_StatAllOut ");
strSql.Append(" where " + Query);
strSql.Append(" ) as a ");
DbCommand dc = this._db.GetSqlStringCommand(strSql.ToString());
using (IDataReader dr = DbHelper.ExecuteReader(dc, this._db))
{
if (dr.Read())
{
if (!dr.IsDBNull(0))
{
totalAmount = dr.GetDecimal(0);
}
if (!dr.IsDBNull(1))
{
hasCheckAmount = dr.GetDecimal(1);
}
}
}
}
/// <summary>
/// 获取支出对帐单的汇总信息(统计分析-支出对账单列表合计)
/// </summary>
/// <param name="queryInfo">查询实体</param>
/// <param name="TotalAmount">总支出</param>
/// <param name="HasCheckAmount">已付</param>
public void GetAllTotalAmount(EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic queryInfo
, ref decimal TotalAmount, ref decimal HasCheckAmount)
{
if (queryInfo == null || queryInfo.CompanyId <= 0)
{
return;
}
DateTime?_StartDate = null;
DateTime?_EndDate = null;
_EndDate = queryInfo.LeaveDateEnd;
_StartDate = queryInfo.LeaveDateStart;
if (!queryInfo.LeaveDateEnd.HasValue)
{
queryInfo.LeaveDateEnd = new DateTime(DateTime.Now.Year + 1, 1, 1).AddMilliseconds(-1);
}
else
{
queryInfo.LeaveDateEnd = queryInfo.LeaveDateEnd.Value.AddDays(1).AddMilliseconds(-1);
}
if (!queryInfo.LeaveDateStart.HasValue)
{
queryInfo.LeaveDateStart = new DateTime(DateTime.Now.Year, 1, 1);
}
idal.GetAllTotalAmount(queryInfo, ref TotalAmount, ref HasCheckAmount, HaveUserIds);
queryInfo.LeaveDateEnd = _EndDate;
queryInfo.LeaveDateStart = _StartDate;
}
/// <summary>
/// 获取收入对帐单的汇总信息
/// </summary>
/// <param name="queryInfo">查询实体</param>
/// <param name="TotalAmount">总收入</param>
/// <param name="HasCheckAmount">已收</param>
public void GetAllTotalAmount(EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic queryInfo
, ref decimal TotalAmount, ref decimal HasCheckAmount)
{
if (queryInfo == null || queryInfo.CompanyId <= 0)
{
return;
}
idal.GetAllTotalAmount(queryInfo, ref TotalAmount, ref HasCheckAmount, HaveUserIds);
}
private void InitOutDepartStaticlist()
{
#region 获取表单值
PageIndex = Utils.GetInt(Utils.GetQueryStringValue("Page"), 1);
RouteAreaName = Utils.GetQueryStringValue("RouteArea");
OperatorName = Utils.GetQueryStringValue("OperatorName");
OperatorId = Utils.GetQueryStringValue("OperatorId");
StartDate = Utils.GetDateTimeNullable(Request.QueryString["StartDate"]);
EndDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("EndDate"));
TourType = Utils.GetQueryStringValue("TourType");
CompanyName = Utils.GetQueryStringValue("CompanyName");
isAccount = Utils.GetQueryStringValue("IsAccount");
#endregion
EyouSoft.BLL.StatisticStructure.StatAllOut OutlayBll = new EyouSoft.BLL.StatisticStructure.StatAllOut(SiteUserInfo);
#region 查询实体赋值
EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic SearchModel = new EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic();
SearchModel.LeaveDateStart = StartDate;
SearchModel.LeaveDateEnd = EndDate;
SearchModel.CompanyId = this.SiteUserInfo.CompanyID;
SearchModel.SaleIds = Utils.GetIntArray(OperatorId, ",");
if (RouteAreaName != "" && RouteAreaName != "0")
{
SearchModel.AreaId = int.Parse(RouteAreaName);
}
if (TourType != "" && TourType != "-1")
{
SearchModel.TourType = (EyouSoft.Model.EnumType.TourStructure.TourType) int.Parse(TourType);
}
SearchModel.BuyCompanyName = CompanyName;
#endregion
#region 表单初始化
this.txtStartDate.Value = StartDate.HasValue ? StartDate.Value.ToString("yyyy-MM-dd") : new DateTime(DateTime.Now.Year, 1, 1).ToString("yyyy-MM-dd");
this.txtEndDate.Value = EndDate.HasValue ? EndDate.Value.ToString("yyyy-MM-dd") : new DateTime(DateTime.Now.Year, 12, 31).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.txt_com.Value = CompanyName;
if (isAccount == "-1")
{
SearchModel.IsAccount = null;
}
else if (isAccount == "0")
{
SearchModel.IsAccount = true;
}
else if (isAccount == "1")
{
SearchModel.IsAccount = false;
}
BindSum(SearchModel);
if (isAccount != "")
{
this.drpIsAccount.SelectedValue = isAccount;
}
#endregion
//调用查询方法 获取列表
list = OutlayBll.GetList(PageSize, PageIndex, ref RecordCount, SearchModel);
if (Utils.GetInt(Request.QueryString["IsCartogram"], 0) == 1)//异步请求统计图时不执行列表绑定
{
return;
}
#region 绑定列表
if (list != null && list.Count != 0)
{
this.tbl_ExportPage.Visible = true;
this.crp_OutDepartStatiList.DataSource = list;
this.crp_OutDepartStatiList.DataBind();
BindPage();//绑定分页
}
else
{
this.tbl_ExportPage.Visible = false;
this.crp_OutDepartStatiList.EmptyText = "<tr bgcolor='#e3f1fc'><td colspan='4' id=\"EmptyData\" height='50px' align='center'>暂时没有数据！</td></tr>";
}
#endregion
}
/// <summary>
/// 分页获取收入明细列表
/// </summary>
/// <param name="PageSize">每页显示条数</param>
/// <param name="PageIndex">当前页码</param>
/// <param name="RecordCount">总记录数</param>
/// <param name="queryInfo">查询实体</param>
/// <param name="haveUserIds">用户Id集合，半角逗号分割</param>
/// <returns></returns>
public IList <EyouSoft.Model.StatisticStructure.StatAllIncomeList> GetList(int PageSize, int PageIndex, ref int RecordCount, EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic queryInfo, string haveUserIds)
{
if (queryInfo == null || queryInfo.CompanyId <= 0)
{
return(null);
}
IList <EyouSoft.Model.StatisticStructure.StatAllIncomeList> list = new List <EyouSoft.Model.StatisticStructure.StatAllIncomeList>();
#region 构造分页存储过程参数
string tableName = "view_StatAllIncomeStatement";
string primaryKey = "salerid";
string orderByStr = string.Empty;
StringBuilder strfield = new StringBuilder();
strfield.Append(" sum(totalamount) as TotalAmount,sum(hascheckamount) as AccountAmount,salerid,(select contactname from tbl_companyuser as a where a.id=view_StatAllIncomeStatement.salerid) as SalerName");
string Query = this.GetSqlWhereByQuery(queryInfo, haveUserIds, ref orderByStr);
#endregion
using (IDataReader dr = DbHelper.ExecuteReaderIsGroupBy(this._db, PageSize, PageIndex, ref RecordCount, tableName, primaryKey, strfield.ToString(), Query, orderByStr, true))
{
while (dr.Read())
{
EyouSoft.Model.StatisticStructure.StatAllIncomeList model = new EyouSoft.Model.StatisticStructure.StatAllIncomeList();
if (!dr.IsDBNull(dr.GetOrdinal("SalerId")))
{
model.OperatorId = dr.GetInt32(dr.GetOrdinal("SalerId"));
}
if (!dr.IsDBNull(dr.GetOrdinal("SalerName")))
{
model.OperatorName = dr[dr.GetOrdinal("SalerName")].ToString();
}
if (!dr.IsDBNull(dr.GetOrdinal("AccountAmount")))
{
model.AccountAmount = dr.GetDecimal(dr.GetOrdinal("AccountAmount"));
}
if (!dr.IsDBNull(dr.GetOrdinal("TotalAmount")))
{
model.TotalAmount = dr.GetDecimal(dr.GetOrdinal("TotalAmount"));
}
list.Add(model);
model = null;
}
}
return(list);
}
/// <summary>
/// 根据查询条件生成SqlWhere语句
/// </summary>
/// <param name="queryInfo">查询条件实体</param>
/// <param name="orderByStr">排序字符串</param>
/// <param name="haveUserIds">用户Id集合，半角逗号分割</param>
/// <returns>SqlWhere语句</returns>
private string GetSqlWhereByQuery(EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic queryInfo
, string haveUserIds, ref string orderByStr)
{
if (queryInfo == null || queryInfo.CompanyId <= 0)
{
return(" group by salerid ");
}
StringBuilder Query = new StringBuilder();
Query.AppendFormat(" CompanyId={0} ", queryInfo.CompanyId);
orderByStr = queryInfo.OrderIndex == 0 ? " salerid asc" : " salerid desc ";
EyouSoft.DAL.TourStructure.TourOrder ConvertDal = new EyouSoft.DAL.TourStructure.TourOrder();
if (queryInfo.AreaId > 0)
{
Query.AppendFormat(" AND areaid={0} ", queryInfo.AreaId);
}
if (queryInfo.SaleIds != null && queryInfo.SaleIds.Length > 0)
{
string salerIds = ConvertDal.ConvertIntArrayTostring(queryInfo.SaleIds);
Query.AppendFormat(" AND SalerId in({0})", salerIds);
}
if (queryInfo.BuyCompanyId != null && queryInfo.BuyCompanyId.Length > 0)
{
string BuyCompanyIds = ConvertDal.ConvertIntArrayTostring(queryInfo.BuyCompanyId);
Query.AppendFormat(" AND BuyCompanyId in({0})", BuyCompanyIds);
}
if (!string.IsNullOrEmpty(queryInfo.BuyCompanyName))
{
Query.AppendFormat(" AND BuyCompanyName LIKE '%{0}%' ", queryInfo.BuyCompanyName);
}
if (queryInfo.TourType.HasValue)
{
Query.AppendFormat(" AND TourType={0}", (int)queryInfo.TourType.Value);
}
if (queryInfo.LeaveDateStart.HasValue)
{
Query.AppendFormat(" AND DATEDIFF(DAY,'{0}',LeaveDate)>=0", queryInfo.LeaveDateStart);
}
if (queryInfo.LeaveDateEnd.HasValue)
{
Query.AppendFormat(" AND DATEDIFF(DAY,LeaveDate,'{0}')>=0", queryInfo.LeaveDateEnd);
}
if (queryInfo.ComputeOrderType == EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType.统计有效订单)
{
Query.AppendFormat(" AND OrderState in ({0},{1},{2})", (int)Model.EnumType.TourStructure.OrderState.未处理, (int)Model.EnumType.TourStructure.OrderState.已成交, (int)Model.EnumType.TourStructure.OrderState.已留位);
}
else if (queryInfo.ComputeOrderType == EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType.统计确认成交订单)
{
Query.AppendFormat(" AND OrderState = {0} ", (int)Model.EnumType.TourStructure.OrderState.已成交);
}
if (queryInfo.IsAccount.HasValue)
{
if (queryInfo.IsAccount.Value)
{
Query.Append(" and TotalAmount <> 0 and TotalAmount = HasCheckAmount ");// + NotCheckAmount
}
else
{
Query.Append(" and TotalAmount <> 0 and TotalAmount <> HasCheckAmount ");//+ + NotCheckAmount
}
}
if (!string.IsNullOrEmpty(haveUserIds))
{
Query.AppendFormat(" and TourOperatorId in ({0}) ", haveUserIds);
}
Query.Append(" group by salerid ");
return(Query.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.QueryAccountAgeStatistic 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 SellCompanyId = {0} ", model.CompanyId);
}
if (model.TourType.HasValue)
{
strSqlWhere.AppendFormat(" and TourClassId = {0} ", (int)model.TourType.Value);
}
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 SalerId in ({0}) ", strIds);
}
}
if (!string.IsNullOrEmpty(HaveUserIds))
{
strSqlWhere.AppendFormat(" and ViewOperatorId in ({0}) ", HaveUserIds);
}
if (model.LeaveDateStart.HasValue)
{
strSqlWhere.AppendFormat(" and LeaveDate>='{0}' ", model.LeaveDateStart.Value);
}
if (model.LeaveDateEnd.HasValue)
{
strSqlWhere.AppendFormat(" and LeaveDate<='{0}' ", model.LeaveDateEnd.Value.AddDays(1).AddMilliseconds(-1));
}
if (model.AreaId > 0)
{
strSqlWhere.AppendFormat(" and AreaId = {0} ", model.AreaId);
}
switch (model.OrderIndex)
{
case 0:
strOrder = " SalerId asc ";
break;
case 1:
strOrder = " SalerId desc ";
break;
}
return(strSqlWhere.ToString());
}
/// <summary>
/// 获取帐龄分析统计
/// </summary>
/// <param name="model">帐龄分析查询实体</param>
/// <returns></returns>
public IList <EyouSoft.Model.StatisticStructure.AccountAgeStatistic> GetAccountAgeStatistic(EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic model, string HaveUserIds)
{
IList <EyouSoft.Model.StatisticStructure.AccountAgeStatistic> list = new List <EyouSoft.Model.StatisticStructure.AccountAgeStatistic>();
string strOrder = string.Empty;
StringBuilder strSql = new StringBuilder(" select ");
strSql.Append(" SalerId, ");
strSql.Append(" (select ContactName from tbl_CompanyUser where tbl_CompanyUser.id = tbl_TourOrder.SalerId and tbl_CompanyUser.isdelete = '0') as SalerName, ");
strSql.Append(" sum(FinanceSum - HasCheckMoney) as ArrearageSum, ");
strSql.Append(" min(IssueTime) as MaxArrearageTime ");
strSql.Append(" from tbl_TourOrder ");
//strSql.AppendFormat(" where IsDelete = '0' and (FinanceSum - HasCheckMoney - NotCheckMoney) > 0 and SalerId > 0 and exists (select 1 from tbl_CompanyUser where tbl_CompanyUser.isdelete = '0') ");
strSql.AppendFormat(" where IsDelete = '0' and (FinanceSum - HasCheckMoney) <> 0 and SalerId > 0 ");
strSql.AppendFormat(" {0} ", this.GetSqlWhere(model, HaveUserIds, ref strOrder));
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.AccountAgeStatistic tmpModel = null;
while (dr.Read())
{
tmpModel = new EyouSoft.Model.StatisticStructure.AccountAgeStatistic();
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.ArrearageSum = dr.GetDecimal(2);
}
if (!dr.IsDBNull(3))
{
tmpModel.MaxArrearageTime = dr.GetDateTime(3);
}
list.Add(tmpModel);
}
}
return(list);
}
private void InitIncDepartAreaStaticlist()
{
#region 获取参数
PageIndex = Utils.GetInt(Request.QueryString["Page"], 1);
TourType = Utils.GetQueryStringValue("TourType");
RouteArea = Utils.GetQueryStringValue("RouteArea");
Salser = Utils.GetQueryStringValue("Salser");
SalserID = Utils.GetQueryStringValue("SalserId");
LeaveTourStarDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("LeaveTourStartDate"));
LeaveTourEndDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("LeaveTourEndDate"));
CompanyName = Utils.GetQueryStringValue("CompanyName");
isAccount = Utils.GetQueryStringValue("IsAccount");
#endregion
//私有变量
EyouSoft.BLL.StatisticStructure.StatAllIncome IncomeBLL = new EyouSoft.BLL.StatisticStructure.StatAllIncome(SiteUserInfo);
#region 实体赋值
EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic model = new EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic();
model.CompanyId = this.SiteUserInfo.CompanyID;
model.SaleIds = Utils.GetIntArray(SalserID, ",");
if (TourType != "" && TourType != "-1")
{
model.TourType = (EyouSoft.Model.EnumType.TourStructure.TourType) int.Parse(TourType);
}
if (RouteArea != "" && RouteArea != "0")
{
model.AreaId = int.Parse(RouteArea);
}
//设置查询model的ComputeOrderType值
EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType?computerOrderType = new EyouSoft.BLL.CompanyStructure.CompanySetting().GetComputeOrderType(SiteUserInfo.CompanyID);
if (computerOrderType.HasValue)
{
model.ComputeOrderType = computerOrderType.Value;
}
model.LeaveDateStart = LeaveTourStarDate;
model.LeaveDateEnd = LeaveTourEndDate;
model.BuyCompanyName = CompanyName;
if (isAccount == "-1")
{
model.IsAccount = null;
}
else if (isAccount == "0")
{
model.IsAccount = true;
}
else if (isAccount == "1")
{
model.IsAccount = false;
}
BindSum(model);
#endregion
#region 表单初始化
if (TourType != "")
{
this.TourTypeList1.TourType = int.Parse(TourType);
}
if (RouteArea != "")
{
this.RouteAreaList1.RouteAreaId = int.Parse(RouteArea);
}
this.SelectSalser.OperName = Salser;
this.SelectSalser.OperId = SalserID;
this.txtLeaveTourStarTime.Value = LeaveTourStarDate.HasValue ? LeaveTourStarDate.Value.ToString("yyyy-MM-dd") : "";
this.txtLeaveTourEndTime.Value = LeaveTourEndDate.HasValue ? LeaveTourEndDate.Value.ToString("yyyy-MM-dd") : "";
this.txt_com.Value = CompanyName;
if (isAccount != "")
{
this.drpIsAccount.SelectedValue = isAccount;
}
#endregion
//调用底层方法
IncomeDepartList = IncomeBLL.GetList(PageSize, PageIndex, ref RecordCount, model);
//如果为统计图异步请求时，不执行列表绑定
if (Utils.GetInt(Request.QueryString["IsCartogram"], 0) == 1)
{
return;
}
#region 列表绑定
if (IncomeDepartList != null && IncomeDepartList.Count != 0)
{
this.tbl_ExportPage.Visible = true;
this.crp_IncDepartStaList.DataSource = IncomeDepartList;
this.crp_IncDepartStaList.DataBind();
BindPage();//绑定分页
}
else
{
this.tbl_ExportPage.Visible = false;
this.crp_IncDepartStaList.EmptyText = "<tr bgcolor='#e3f1fc'><td colspan='4' id=\"EmptyData\" height='50px' align='center'>暂时没有数据！</td></tr>";
}
#endregion
}
private void InitAgeAreaStaList()
{
#region 获取参数
PageIndex = Utils.GetInt(Request.QueryString["Page"], 1);
TourType = Utils.GetQueryStringValue("tourType");
RouteAreaName = Utils.GetQueryStringValue("routeAreaName");
Salser = Utils.GetQueryStringValue("Salser");
SalerId = Utils.GetQueryStringValue("SalserId");
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));
#endregion
//调用底层方法
EyouSoft.BLL.StatisticStructure.AccountAgeStatistic AgeStaBll = new EyouSoft.BLL.StatisticStructure.AccountAgeStatistic(this.SiteUserInfo);
EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic model = new EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic();
#region 查询实体赋值
if (TourType != "" && TourType != "-1")
{
model.TourType = (EyouSoft.Model.EnumType.TourStructure.TourType) int.Parse(TourType);
}
model.LeaveDateStart = StartDate;
model.LeaveDateEnd = EndDate;
if (RouteAreaName != "" && RouteAreaName != "0")
{
model.AreaId = int.Parse(RouteAreaName);
}
if (SalerId != "")
{
model.SaleIds = Utils.GetIntArray(SalerId, ",");
}
#endregion
#region 初始化表单赋值
txtStartDate.Value = StartDate.HasValue ? StartDate.Value.ToString("yyyy-MM-dd") : "";
txtEndDate.Value = EndDate.HasValue ? EndDate.Value.ToString("yyyy-MM-dd") : "";
if (TourType != "")
{
this.TourTypeList1.TourType = int.Parse(TourType);
}
if (RouteAreaName != "")
{
this.RouteAreaList1.RouteAreaId = int.Parse(RouteAreaName);
}
this.SelectSalers.OperName = Salser;
this.SelectSalers.OperId = SalerId;
#endregion
#region 绑定列表
list = EyouSoft.Common.Function.SelfExportPage.GetList <EyouSoft.Model.StatisticStructure.AccountAgeStatistic>(PageIndex, PageSize, out RecordCount, AgeStaBll.GetAccountAgeStatistic(model));
if (list != null && list.Count != 0)
{
this.tbl_ExportPage.Visible = true;
this.crp_GetAgeDepartStaList.DataSource = list;
this.crp_GetAgeDepartStaList.DataBind();
BindPage();
}
else
{
this.tbl_ExportPage.Visible = false;
this.crp_GetAgeDepartStaList.EmptyText = "<tr bgcolor='#e3f1fc'><td id=\"EmptyData\" colspan='3' height='50px' align='center'>暂时没有数据！</td></tr>";
}
#endregion
}
/// <summary>
/// 分页获取收入明细列表
/// </summary>
/// <param name="PageSize">每页显示条数</param>
/// <param name="PageIndex">当前页码</param>
/// <param name="RecordCount">总记录数</param>
/// <param name="queryInfo">查询实体</param>
/// <returns></returns>
public IList <EyouSoft.Model.StatisticStructure.StatAllIncomeList> GetList(int PageSize, int PageIndex, ref int RecordCount, EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic queryInfo)
{
return(idal.GetList(PageSize, PageIndex, ref RecordCount, queryInfo, HaveUserIds));
}
/// <summary>
/// 分页获取支出明细列表(统计分析-支出对账单列表)
/// </summary>
/// <param name="PageSize">每页现实条数</param>
/// <param name="PageIndex">当前页码</param>
/// <param name="RecordCount">总记录数</param>
/// <param name="queryInfo">查询实体</param>
/// <param name="haveUserIds">用户Id集合，半角逗号分割</param>
/// <returns></returns>
public IList <EyouSoft.Model.StatisticStructure.StatAllOutList> GetList(int PageSize, int PageIndex, ref int RecordCount, EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic queryInfo, string haveUserIds)
{
if (queryInfo == null || queryInfo.CompanyId <= 0)
{
return(null);
}
IList <EyouSoft.Model.StatisticStructure.StatAllOutList> list = new List <EyouSoft.Model.StatisticStructure.StatAllOutList>();
#region 构造分页存储过程参数
string tableName = "tbl_StatAllOut";
string primaryKey = "Id";
string orderByStr = string.Empty;
StringBuilder strfield = new StringBuilder();
strfield.Append("OperatorId,");
strfield.Append("sum(TotalAmount) as TotalAmount,");
strfield.Append("sum(HasCheckAmount) as PaidAmount,");
strfield.Append("(select ContactName from tbl_CompanyUser where id=tbl_StatAllOut.OperatorId) as OperatorName");
string strWhere = this.GetSqlWhereByQuery(queryInfo, haveUserIds, ref orderByStr);
#endregion
using (IDataReader dr = DbHelper.ExecuteReaderIsGroupBy(this._db, PageSize, PageIndex, ref RecordCount, tableName, primaryKey, strfield.ToString(), strWhere, orderByStr, true))
{
while (dr.Read())
{
EyouSoft.Model.StatisticStructure.StatAllOutList model = new EyouSoft.Model.StatisticStructure.StatAllOutList();
if (!dr.IsDBNull(dr.GetOrdinal("OperatorId")))
{
model.OperatorId = dr.GetInt32(dr.GetOrdinal("OperatorId"));
}
if (!dr.IsDBNull(dr.GetOrdinal("OperatorName")))
{
model.OperatorName = dr[dr.GetOrdinal("OperatorName")].ToString();
}
if (!dr.IsDBNull(dr.GetOrdinal("PaidAmount")))
{
model.PaidAmount = dr.GetDecimal(dr.GetOrdinal("PaidAmount"));
}
if (!dr.IsDBNull(dr.GetOrdinal("TotalAmount")))
{
model.TotalAmount = dr.GetDecimal(dr.GetOrdinal("TotalAmount"));
}
list.Add(model);
model = null;
}
}
return(list);
}
/// <summary>
/// 根据查询条件生成SqlWhere语句
/// </summary>
/// <param name="queryInfo">查询条件实体</param>
/// <param name="orderByStr">排序字符串</param>
/// <param name="haveUserIds">用户Id集合，半角逗号分割</param>
/// <returns>SqlWhere语句</returns>
private string GetSqlWhereByQuery(EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic queryInfo, string haveUserIds, ref string orderByStr)
{
StringBuilder strWhere = new StringBuilder(" (ItemType=1 OR ItemType=2 OR ItemType=4) AND IsDelete='0' ");
if (queryInfo != null)
{
orderByStr = queryInfo.OrderIndex == 0 ? " OperatorId asc" : " OperatorId desc ";
//StringBuilder strAreaSql = new StringBuilder("select areaid from tbl_StatAllOut where IsDelete='0' ");
if (queryInfo.AreaId > 0)
{
strWhere.AppendFormat(" and areaid={0} ", queryInfo.AreaId);
}
if (queryInfo.CompanyId > 0)
{
strWhere.AppendFormat(" and CompanyId={0} ", queryInfo.CompanyId);
}
if (queryInfo.SaleIds != null && queryInfo.SaleIds.Length > 0)
{
string SaleIds = string.Empty;
foreach (int i in queryInfo.SaleIds)
{
if (i > 0)
{
SaleIds += i.ToString() + ",";
}
}
SaleIds = SaleIds.TrimEnd(',');
strWhere.AppendFormat(" and OperatorId in({0})", SaleIds);
}
if (queryInfo.TourType.HasValue)
{
strWhere.AppendFormat(" and TourType={0}", (int)queryInfo.TourType.Value);
}
if (queryInfo.BuyCompanyId != null && queryInfo.BuyCompanyId.Length > 0)
{
string strBuyCompanyIds = string.Empty;
if (queryInfo.BuyCompanyId != null && queryInfo.BuyCompanyId.Length > 0)
{
foreach (int i in queryInfo.BuyCompanyId)
{
if (i > 0)
{
strBuyCompanyIds += i.ToString() + ",";
}
}
}
strBuyCompanyIds = strBuyCompanyIds.Trim(',');
if (!string.IsNullOrEmpty(strBuyCompanyIds))
{
strWhere.AppendFormat(" and SupplierId in ({0}) ", strBuyCompanyIds);
}
}
if (!string.IsNullOrEmpty(queryInfo.BuyCompanyName))
{
strWhere.AppendFormat(" and SupplierName like '%{0}%' ", queryInfo.BuyCompanyName);
}
if (queryInfo.IsAccount.HasValue)
{
if (queryInfo.IsAccount.Value)
{
strWhere.Append(" and TotalAmount <> 0 and TotalAmount = HasCheckAmount ");// + NotCheckAmount
}
else
{
strWhere.Append(" and TotalAmount <> 0 and TotalAmount <> HasCheckAmount ");// + NotCheckAmount
}
}
/*if (!string.IsNullOrEmpty(haveUserIds))
* strWhere.AppendFormat(@" and (TourId is null and len(TourId) <> 36 and OperatorId in ({0}) or (TourId is not null and len(TourId) = 36 and TourId in (select TourId from tbl_Tour where tbl_Tour.OperatorId in ({0})))) ", haveUserIds);*/
//strWhere.AppendFormat(" and areaid in({0})", strAreaSql.ToString());
if (queryInfo.LeaveDateStart.HasValue || queryInfo.LeaveDateEnd.HasValue)
{
strWhere.AppendFormat(" AND EXISTS(SELECT 1 FROM tbl_Tour AS A WHERE 1=1 AND A.TourId=tbl_StatAllOut.TourId ");
if (queryInfo.LeaveDateStart.HasValue)
{
strWhere.AppendFormat(" AND A.LeaveDate>='{0}' ", queryInfo.LeaveDateStart.Value);
}
if (queryInfo.LeaveDateEnd.HasValue)
{
strWhere.AppendFormat(" AND A.LeaveDate<='{0}' ", queryInfo.LeaveDateEnd.Value);
}
strWhere.AppendFormat(" ) ");
}
}
strWhere.Append(" group by OperatorId ");
return(strWhere.ToString());
}
/// <summary>
/// 分页获取支出明细列表(统计分析-支出对账单列表)
/// </summary>
/// <param name="PageSize">每页显示条数</param>
/// <param name="PageIndex">当前页码</param>
/// <param name="RecordCount">总记录数</param>
/// <param name="queryInfo">查询实体</param>
/// <returns></returns>
public IList <EyouSoft.Model.StatisticStructure.StatAllOutList> GetList(int PageSize, int PageIndex, ref int RecordCount, EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic queryInfo)
{
DateTime?_StartDate = null;
DateTime?_EndDate = null;
if (queryInfo != null)
{
_StartDate = queryInfo.LeaveDateStart;
_EndDate = queryInfo.LeaveDateEnd;
if (!queryInfo.LeaveDateEnd.HasValue)
{
queryInfo.LeaveDateEnd = new DateTime(DateTime.Now.Year + 1, 1, 1).AddMilliseconds(-1);
}
else
{
queryInfo.LeaveDateEnd = queryInfo.LeaveDateEnd.Value.AddDays(1).AddMilliseconds(-1);
}
if (!queryInfo.LeaveDateStart.HasValue)
{
queryInfo.LeaveDateStart = new DateTime(DateTime.Now.Year, 1, 1);
}
}
var items = idal.GetList(PageSize, PageIndex, ref RecordCount, queryInfo, HaveUserIds);
queryInfo.LeaveDateEnd = _EndDate;
queryInfo.LeaveDateStart = _StartDate;
return(items);
}