NC6X报表数据加工做语义模型（返回SQL篇）(7)

private double hkd=0.83687; /********马币汇率********/ private double mb=1.55; /********澳元汇率********/ private double ob=1.7; /********新币汇率********/ private double xb=1.2; /********加币汇率********/ private double jb=1.3;

/**

* 资金管理>现金管理报表>集团现金流简表（新）(性能优化-由原来的语义脚本

改成数据加工)

*

* @author WYR * @since 2017-09-25 * */

public String getQuerySql(IContext context) throws Exception {

return null;

} /***

* 拼接查询SQL *

* @param context 报表参数上下文 * @author WYR * @since 2017-09-25 * @return

* @throws Exception */

private String getConvertSql(IContext context)throws Exception{ } /***

TempTableName=null;

this.getQueryParameters(context); this.checkQueryParametersIsNotNull(); this.getCreateTempTableName(); this.buildSelectSql(); this.getEmbeddedSql();

return this.builderSQL.toString();

* 获取第二层嵌套的SQL *

* @author WYR * @since 2017-09-26 * @throws Exception */

private void getEmbeddedSql() throws Exception{

StringBuilder bdSql = new StringBuilder(); bdSql.append(\); bdSql.append(\); bdSql.append(\);

bdSql.append(\+getSameCasewhenPartSql(\经营\,\经营流入\)+\); bdSql.append(\);

bdSql.append(\+getSameCasewhenPartSql(\经营\,\经营流出\)+\); bdSql.append(\);

bdSql.append(\);

bdSql.append(\+getSameCasewhenPartSql(\土地费用\,\土地费用

\)+\);

bdSql.append(\); bdSql.append(\);

bdSql.append(\+getSameCasewhenPartSql(\筹资\,\筹资流入\)+\); bdSql.append(\);

bdSql.append(\+getSameCasewhenPartSql(\筹资\,\筹资流出\)+\); bdSql.append(\);

bdSql.append(\); bdSql.append(\);

bdSql.append(\+getSameCasewhenPartSql(\投资\,\投资流入\)+\); bdSql.append(\);

bdSql.append(\+getSameCasewhenPartSql(\投资\,\投资流出\)+\); bdSql.append(\);

bdSql.append(\); bdSql.append(\);

bdSql.append(\+getSameCasewhenPartSql(\集团内部\,\集团内部抵

消项\)+\);

bdSql.append(\);

bdSql.append(\);

bdSql.append(\+getSameCasewhenPartSql(\理财收支\,\理财收支

\)+\);

}

/**获取查询 Union all (里面的子查询SQL) 改用公共方法 *

bdSql.append(\);

this.builderSQL.append(bdSql);

* @param sqlField 默认的inouttype值 'F.理财收支' * @param cashflow 项目 '理财收支'

* @例子 select pk_group,'F.理财收支' inouttype,'理财收支' as

aitem,inoutname as fitem,

* @author WYR * @since 2017-09-26 * @return

* @throws Exception */

private String getSameCasewhenPartSql(String sqlField,String

cashflow)throws Exception{

StringBuilder samePartSql = new StringBuilder(); samePartSql.append(\+sqlField+\

inouttype,'\+cashflow+\);

samePartSql.append(\

usd,sum(mb) as mb,sum(xb) as xm,\);

samePartSql.append(\); samePartSql.append(\);

samePartSql.append(\

cuname='美元' then money else 0 end as usd,\);

samePartSql.append(\马币' then money else 0 end

as mb,\);