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