/// <summary> /// 查询分页后的商家成功充值的记录信息 /// </summary> /// <UpdateBy>zhaohailong</UpdateBy> /// <UpdateTime>20150623</UpdateTime> /// <param name="queryInfo"></param> /// <returns></returns> public PageInfo <BusinessBalanceInfo> QueryBusinessBalance(BussinessBalanceQuery queryInfo) { string columnList = @" bbr.Id ,bbr.RelationNo ,bbr.BusinessId ,b.Name ,b.PhoneNo ,b.Address ,bbr.OperateTime ,bbr.Amount ,bbr.Balance"; string tables = " dbo.BusinessBalanceRecord bbr(nolock) join dbo.business b(nolock) on bbr.BusinessId = b.Id"; var sbSqlWhere = GetQueryWhere(queryInfo); //0为充值时间倒序,1为充值金额降序,2为充值金额升序 string orderByColumn = " bbr.operatetime desc "; switch (queryInfo.OrderType) { case 1: orderByColumn = " bbr.Amount desc "; break; case 2: orderByColumn = " bbr.Amount asc "; break; default: break; } return(new PageHelper().GetPages <BusinessBalanceInfo>(SuperMan_Read, queryInfo.PageIndex, sbSqlWhere, orderByColumn, columnList, tables, ETS.Const.SystemConst.PageSize, true)); }
/// <summary> /// 查询给定条件下充值成功的商户的个数 /// </summary> /// <UpdateBy>zhaohailong</UpdateBy> /// <UpdateTime>20150623</UpdateTime> /// <returns></returns> public long QueryBusinessNum(BussinessBalanceQuery queryInfo) { string sql = @" SELECT count(distinct bbr.BusinessId) as num from BusinessBalanceRecord bbr(nolock) join business b(nolock) on bbr.BusinessId = b.Id where "; var sbSqlWhere = GetQueryWhere(queryInfo); object obj = DbHelper.ExecuteScalar(SuperMan_Read, sql + sbSqlWhere); return(ParseHelper.ToLong(obj, 0)); }
/// <summary> /// 查询给定条件下商家成功充值的总金额 /// </summary> /// <UpdateBy>zhaohailong</UpdateBy> /// <UpdateTime>20150623</UpdateTime> /// <param name="queryInfo"></param> /// <returns></returns> public decimal QueryBusinessTotalAmount(BussinessBalanceQuery queryInfo) { string sql = @" SELECT SUM(bbr.amount) AS totalAmount FROM BusinessBalanceRecord bbr ( NOLOCK ) INNER JOIN dbo.business b ( NOLOCK ) ON bbr.BusinessId = b.Id where "; var sbSqlWhere = GetQueryWhere(queryInfo); object obj = DbHelper.ExecuteScalar(SuperMan_Read, sql + sbSqlWhere); return(ParseHelper.ToDecimal(obj, 0)); }
/// <summary> /// 分页或查询时调用的post方法(获取商家充值信息) /// </summary> /// <UpdateBy>zhaohailong</UpdateBy> /// <UpdateTime>20150623</UpdateTime> /// <param name="PageIndex"></param> /// <returns></returns> public ActionResult PostBussinessBalanceStatistical(int PageIndex = 1) { var criteria = new BussinessBalanceQuery(); TryUpdateModel(criteria); criteria.PageIndex = PageIndex; GetStaticData(criteria); PageInfo <BusinessBalanceInfo> resultData = statisticsProvider.QueryBusinessBalance(criteria); return(PartialView("BussinessBalanceList", resultData)); }
/// <summary> /// 查询给定条件下商家成功充值的总金额 /// </summary> /// <UpdateBy>zhaohailong</UpdateBy> /// <UpdateTime>20150623</UpdateTime> /// <param name="queryInfo"></param> /// <returns></returns> public decimal QueryBusinessTotalAmount(BussinessBalanceQuery queryInfo) { if (queryInfo != null && !string.IsNullOrWhiteSpace(queryInfo.StartDate) && !string.IsNullOrWhiteSpace(queryInfo.EndDate)) { if (ParseHelper.ToDatetime(queryInfo.StartDate) > ParseHelper.ToDatetime(queryInfo.EndDate)) { return(0); } } return(statisticsDao.QueryBusinessTotalAmount(queryInfo)); }
/// <summary> /// 页面第一次加载时调用的获取商家充值信息方法 /// </summary> /// <UpdateBy>zhaohailong</UpdateBy> /// <UpdateTime>20150623</UpdateTime> /// <returns></returns> public ActionResult BussinessBalanceStatistical() { var criteria = new BussinessBalanceQuery(); TryUpdateModel(criteria); criteria.PageIndex = 1; ViewBag.openCityList = areaProvider.GetOpenCityOfSingleCity(0); GetStaticData(criteria); PageInfo <BusinessBalanceInfo> resultData = statisticsProvider.QueryBusinessBalance(criteria); return(View(resultData)); }
/// <summary> /// 根据查询条件组装过滤的sql语句 /// </summary> /// <UpdateBy>zhaohailong</UpdateBy> /// <UpdateTime>20150623</UpdateTime> /// <param name="queryInfo"></param> /// <returns></returns> private static string GetQueryWhere(BussinessBalanceQuery queryInfo) { var sbSqlWhere = new StringBuilder(" bbr.RecordType=9 and bbr.status=1 "); if (!string.IsNullOrWhiteSpace(queryInfo.BusinessId)) { sbSqlWhere.AppendFormat(" AND bbr.BusinessId='{0}' ", queryInfo.BusinessId); } if (!string.IsNullOrWhiteSpace(queryInfo.StartDate)) { sbSqlWhere.AppendFormat(" AND bbr.operatetime>='{0}' ", queryInfo.StartDate); } if (!string.IsNullOrWhiteSpace(queryInfo.EndDate)) { DateTime finalDt = ParseHelper.ToDatetime(queryInfo.EndDate); if (finalDt != DateTime.MaxValue) { finalDt = finalDt.AddDays(1); } sbSqlWhere.AppendFormat(" AND bbr.operatetime<='{0}' ", finalDt.ToString("yyyy-MM-dd")); } if (!string.IsNullOrWhiteSpace(queryInfo.Name)) { sbSqlWhere.AppendFormat(" AND Name='{0}' ", queryInfo.Name); } if (!string.IsNullOrWhiteSpace(queryInfo.PhoneNo)) { sbSqlWhere.AppendFormat(" AND PhoneNo='{0}' ", queryInfo.PhoneNo); } if (!string.IsNullOrWhiteSpace(queryInfo.CityId)) { sbSqlWhere.AppendFormat(" AND CityId='{0}' ", queryInfo.CityId); } if (queryInfo.RechargePrice > 0) { sbSqlWhere.AppendFormat(" AND Amount>='{0}' ", queryInfo.RechargePrice); } if (queryInfo.RechargeType > 0) { if (queryInfo.RechargeType == 1)//系统充值 { sbSqlWhere.AppendFormat(" AND Remark!='商家客户端充值'"); } else if (queryInfo.RechargeType == 2)//客户端充值 { sbSqlWhere.AppendFormat(" AND Remark='商家客户端充值'"); } } return(sbSqlWhere.ToString()); }
/// <summary> /// 查看商户详细信息 /// danny-20150512 /// </summary> /// <param name="businessId">商户Id</param> /// <returns></returns> public ActionResult BusinessDetail(string businessId) { var criteria = new BusinessBalanceRecordSerchCriteria() { BusinessId = Convert.ToInt32(businessId) }; ViewBag.businessBalanceRecord = iBusinessFinanceProvider.GetBusinessBalanceRecordListOfPaging(criteria); var queryCriteria = new BussinessBalanceQuery(); queryCriteria.BusinessId = businessId; ViewBag.TotalAmount = statisticsProvider.QueryBusinessTotalAmount(queryCriteria); var businessWithdrawFormModel = iBusinessProvider.GetBusinessDetailById(businessId); return(View(businessWithdrawFormModel)); }
/// <summary> /// 查询给定条件下充值成功的商户的个数 /// </summary> /// <UpdateBy>zhaohailong</UpdateBy> /// <UpdateTime>20150623</UpdateTime> /// <returns></returns> public long QueryBusinessNum(BussinessBalanceQuery queryInfo) { return(statisticsDao.QueryBusinessNum(queryInfo)); }
/// <summary> /// 查询分页后的商家成功充值的记录信息 /// </summary> /// <UpdateBy>zhaohailong</UpdateBy> /// <UpdateTime>20150623</UpdateTime> /// <param name="queryInfo"></param> /// <returns></returns> ETS.Data.PageData.PageInfo <BusinessBalanceInfo> IStatisticsProvider.QueryBusinessBalance(BussinessBalanceQuery queryInfo) { if (queryInfo != null && !string.IsNullOrWhiteSpace(queryInfo.StartDate) && !string.IsNullOrWhiteSpace(queryInfo.EndDate)) { if (ParseHelper.ToDatetime(queryInfo.StartDate) > ParseHelper.ToDatetime(queryInfo.EndDate)) { return(null); } } return(statisticsDao.QueryBusinessBalance(queryInfo)); }
/// <summary> /// 获取商家充值统计数据 /// </summary> /// <UpdateBy>zhaohailong</UpdateBy> /// <UpdateTime>20150623</UpdateTime> /// <param name="criteria"></param> private void GetStaticData(BussinessBalanceQuery criteria) { ViewBag.TotalAmount = statisticsProvider.QueryBusinessTotalAmount(criteria); ViewBag.TotalBalance = bussinessProvider.QueryAllBusinessTotalBalance(); ViewBag.TotalNum = statisticsProvider.QueryBusinessNum(criteria); }