* 账号交易明细查询(分页)
* @author
*
*/
public class BusiSubAcctTransLogListServiceImpl implements BusiSubAcctTransLogListService {
@Autowired
private AcctTranInfoMapper mapper;
@Autowired
private SubAccountService subAccountService;
@Override
public BusiSubAcctTranLogListRspBO list(BusiSubAcctTransLogReqBO busiSubAcctTransLogReqBO) {
BusiSubAcctTranLogListRspBO resp=new BusiSubAcctTranLog ListRspBO();
String subAcctNo=busiSubAcctTransLogReqBO.getSubAcctNo();
BigDecimal balance=BigDecimal.ZERO;
if( subAcctNo!=null ) {
SubAcctInfoExt actInfoExt=subAccountService.getWithMainAccount(subAcctNo); 子账号联动主账号查询,查询的SQL语句见下面绿色背景
if( actInfoExt==null )
throw new BusinessException("RSP_CODE_PARA_NOT_NULL","子账号不存在");
resp.setBalance(actInfoExt.getBalance()); //取得余额
resp.setMainacctNo(actInfoExt.getMainAcctNo());
resp.setSubAcctName(actInfoExt.getSubAcctName());
resp.setSubAcctNo(actInfoExt.getSubAcctNo());
}
Pagination<AcctTranInfoExt> pageInfo=queryTranLog(busiSubAcctTransLogReqBO); 见黄色部分
resp.setTotal(pageInfo.getPageCount()); //总页数
resp.setRecordsTotal(pageInfo.getTotalCount()); //总记录数
resp.setRows(new ArrayList<BusiSubAcctTransLogRspBO>()); //数据行
//数据填充到返回对象
List<AcctTranInfoExt> ls=pageInfo.getRows();
for(int i=0;i<ls.size();i++) {
AcctTranInfoExt inf=ls.get(i);
BusiSubAcctTransLogRspBO respBo=new BusiSubAcctTransLogRspBO();
resp.getRows().add(respBo);
BeanUtils.copyProperties(inf, respBo);
if( respBo.getTranAmt()!=null ) {
if( "DR".equals(inf.getDrcrFlag()) ) //借方变成负数
respBo.setTranAmt(respBo.getTranAmt().negate());
}
}
return resp;
}
private Pagination<AcctTranInfoExt> queryTranLog(BusiSubAcctTransLogReqBO bo) {
AcctTranInfoExt extInfo=new AcctTranInfoExt();
BeanUtils.copyProperties(bo, extInfo);
Pagination<AcctTranInfoExt> pg=new Pagination<AcctTranInfoExt>(); 分页信息详情见蓝色部分
pg.setPage(bo.getPageNo());
pg.setPageSize(bo.getPageSize()); //每页笔数
int tot=mapper.selectForCount(extInfo); //计算总记录数 见红色部分
pg.setTotalCount(tot); //设置(计算页数)
int rowIndex= pg.getRowIndex(); //计算起始记录的位置
extInfo.setPageSize(pg.getPageSize()); //每页多少条
extInfo.setRowIndex(rowIndex); //当前的行序号(从0起)
List<AcctTranInfoExt> ls=mapper.queryPageSelective(extInfo); //分页查询 见红色部分
pg.setRows(ls);
return pg;
}
}
<!-- 子账号联动主账号查询 -->
<select id="selectWihtMainAcct" parameterType="Map" resultMap="ExtResultMap">
select
*
from
(
select
s.*,
m.org_id as SUPERIOR_ORG_ID,m.MAIN_ACCT_NAME,m.source
from d_sub_acct_info s
inner join d_main_acct_info m
on s.main_acct_no=m.MAIN_ACCT_NO
where 1=1
<if test="subAcctNo != null">
AND SUB_ACCT_NO=#{subAcctNo,jdbcType=VARCHAR}
</if>
<if test="serviceType != null">
AND SERVICE_TYPE=#{serviceType,jdbcType=INTEGER}
</if>
<if test="orgId != null">
AND s.ORG_ID=#{orgId,jdbcType=BIGINT}
</if>
<if test="serviceType != null">
AND SERVICE_TYPE=#{serviceType,jdbcType=INTEGER}
</if>
<if test="projectId != null">
AND PROJECT_ID=#{projectId,jdbcType=BIGINT}
</if>
) as n
where 1=1
<if test="superiorOrgId != null">
AND SUPERIOR_ORG_ID=#{superiorOrgId,jdbcType=BIGINT}
</if>
<if test="source != null">
AND SOURCE=#{source,jdbcType=VARCHAR}
</if>
</select>
/**
* 分页信息对象
* @author 2017年9月6日
*
* @param <T> 指定List元素里的类型
*/
public class Pagination<T> {
private int page; //页码
private int pageCount; //页数
private int totalCount; //总记录数
private int pageSize; //每页多少条
private String orderBy;
private List<T> rows;
public int getPage() {
if( page<1 ) page=1;
return page;
}
public void setPage(int page) {
if( page<1) page=1;
this.page = page;
}
public int getPageCount() {
return pageCount;
}
public void setPageCount(int pageCount) {
this.pageCount = pageCount;
}
public int getTotalCount() {
return totalCount;
}
public void setTotalCount(int totalCount) {
int pgCnt= totalCount / getPageSize();
if( pgCnt % getPageSize()>0 ) pgCnt++;
pageCount=pgCnt; //同时更新总页数
if( page>pageCount ) setPage(pageCount); //页面超出总页数
this.totalCount = totalCount;
}
public int getPageSize() {
if( pageSize<=0 ) pageSize=10;
return pageSize;
}
public void setPageSize(int countPerPage) {
if( countPerPage<=0 ) countPerPage=1;
this.pageSize = countPerPage;
}
public String getOrderBy() {
return orderBy;
}
public void setOrderBy(String orderBy) {
this.orderBy = orderBy;
}
public List<T> getRows() {
return rows;
}
public void setRows(List<T> rows) {
this.rows = rows;
}
public int getRowIndex() {
int idx=(getPage()-1) * getPageSize();
if( idx<0) idx=0;
return idx;
}
@Override
public String toString() {
return "Pagination [page=" + page + ", pageCount=" + pageCount + ", totalCount=" + totalCount + ", pageSize="
+ pageSize + ", rowIndex=" + getRowIndex() + ", orderBy=" + orderBy + "]";
}
}
<!-- 根据条件统计笔数 By 20170906 -->
<select id="selectForCount" parameterType="Map" resultType="Integer">
select count(seq) as cnt
from d_acct_tran_info
where 1=1
<if test="subAcctNo != null">
AND SUB_ACCT_NO=#{subAcctNo,jdbcType=VARCHAR}
</if>
<if test="txnno != null">
AND txnno=#{TXNNO,jdbcType=VARCHAR}
</if>
<if test="tranAmt!= null"><![CDATA[
AND TRAN_AMT >=#{subAcctNo,jdbcType=VARCHAR}
]]></if>
<if test="maxTranAmt!= null"><![CDATA[
AND TRAN_AMT <=#{subAcctNo,jdbcType=VARCHAR}
]]></if>
<if test="tranDate!= null"><![CDATA[
and TRAN_DATE >=to_date(#{tranDate}, 'yyyy-mm-dd')
]]></if>
<if test="endTranDate!= null"><![CDATA[
and TRAN_DATE <=to_date(#{tranDate}, 'yyyy-mm-dd')
]]></if>
</select>
<!-- 根据条件分页查询记录 By Xie.Kaizu 20170906 -->
<select id="queryPageSelective" parameterType="com.cgd.pay.atom.bo.AcctTranInfoExt" resultMap="ExtResultMap">
select
tr.*
,sub.org_id as acct_org_id,sub.sub_acct_name,sub.service_type,sub.main_acct_no,sub.project_id
,main.org_id as superior_org_id,main.source,main.main_acct_name
from
(
(
select
<include refid="Base_Column_List" />
from d_acct_tran_info
where 1=1
<if test="subAcctNo != null">
AND SUB_ACCT_NO=#{subAcctNo,jdbcType=VARCHAR}
</if>
<if test="txnno != null">
AND txnno=#{TXNNO,jdbcType=VARCHAR}
</if>
<if test="tranAmt!= null"><![CDATA[
AND TRAN_AMT >=#{subAcctNo,jdbcType=VARCHAR}
]]></if>
<if test="maxTranAmt!= null"><![CDATA[
AND TRAN_AMT <=#{subAcctNo,jdbcType=VARCHAR}
]]></if>
<if test="tranDate!= null"><![CDATA[
and TRAN_DATE >=to_date(#{tranDate}, 'yyyy-mm-dd')
]]></if>
<if test="endTranDate!= null"><![CDATA[
and TRAN_DATE <=to_date(#{tranDate}, 'yyyy-mm-dd')
]]></if>
limit #{rowIndex},#{pageSize}
) tr
inner join d_sub_acct_info sub
on sub.sub_acct_no= tr.sub_acct_no
)
inner join d_main_acct_info main
on sub.main_acct_no=main.main_acct_no
</select>