报表传参的问题？

hbyczg

jishuang 发表于 2014-5-4 14:53
用action参考例子中的是sql自己可以用ksql实现，不同的条件拼接不同的where条件 ...

实在是没看懂，是说这种查询传参只能用sql吗，不能用ksql吗？实在想不出当年，月参数都有值时
“where getYear(AO_WTK.fTBRQ) = 年参数  AND getMonth(AO_WTK.fTBRQ) = 月参数”
当没有输入年，月时报表显示当前年的所有数据
“where getYear(AO_WTK.fTBRQ) =当前年 “
当只输入年不输入月时报表显示输入年的所有数据
“where getYear(AO_WTK.fTBRQ) = 年参数 ”
这样的where 条件在action中只拼参数，不拼where条件应怎么写才行？还望能详细指点一下

jishuang

可以用ksql别要自己用ksqlQueryProcedure
自己定义，自定义action参考http://www.justep.com/x5knowledg ... 4A8A7527FE1FA8.html

然后在java代码中判断你传过来的为空不为空的情况去拼where条件

hbyczg

jishuang 发表于 2014-5-5 10:27
可以用ksql别要自己用ksqlQueryProcedure
自己定义，自定义action参考http://www.justep.com/x5knowledge/k ...

可以理解为自已建一个action,然后把以下这段码放在ACTION中，不要放在lUI中，对吗？
  var lsql = '';
        if (tbyear == '') {
                if (tbmonth == '') {
                        lsql = 'getYear(AO_WTK.fTBRQ) = getYear(:currentDate())  AND getMonth(AO_WTK.fTBRQ) = getMonth( :currentDate())';
              
                justep.xbl('r1').setStringVar('lsql',lsql);
                justep.xbl('report1').refresh();
                } else {
                        lsql = 'getYear(AO_WTK.fTBRQ) = getYear(:currentDate())  AND getMonth(AO_WTK.fTBRQ) = '+ tbmonth;
                }
        } else {
                if (tbmonth == '') {
                        lsql = 'getYear(AO_WTK.fTBRQ) = ' + tbyear;
                     
                } else {
                        lsql = 'getYear(AO_WTK.fTBRQ) = ' + tbyear
                                        + '  AND getMonth(AO_WTK.fTBRQ) = ' + tbmonth;
                }
        }
   

jishuang

hbyczg 发表于 2014-5-5 10:57
可以理解为自已建一个action,然后把以下这段码放在ACTION中，不要放在lUI中，对吗？
  var lsql = '';
   ...

是的

hbyczg

jishuang 发表于 2014-5-5 11:08
是的

我照着写了一个Action代码如下：最后两句被打上了红色波浪线，不知怎么改，求指点。其它的地方也不知有没有错？基础太差，努力学习中，望不吝赐教。

        public static void pzwtcscx(String tbyear,String tbmonth){

                String mssql = "select AO_YYFX.fWTLX as fWTLX,AO_YYFX.fWTZL as fWTZL,AO_YYFX.fZRBM as fZRBM,AO_YYFX.fZRGW as fZRGW,AO_YYFX.fZRR as fZRR,AO_WTK.fWTBH as fWTBH,AO_WTK.fTBRQ as fTBRQ,AO_WTK.fDDH as fDDH,AO_WTK.fKHBH as fKHBH,AO_WTK.fBPBH as fBPBH,AO_WTK.fBPMC as fBPMC,AO_WTK.fCPBH as fCPBH,AO_WTK.fCPMC as fCPMC,AO_YYFX.fYZCD as fYZCD,(getYear(AO_WTK.fTBRQ)) as tbyear,(getMonth(AO_WTK.fTBRQ)) as tbmonth from AO_YYFX AO_YYFX optional  join AO_WTK AO_WTK on AO_YYFX.fMasterID = AO_WTK";
               
                mssql += " WHERE 1=1 ";
               
                if(!"".equals(tbyear)){
                        if(!"".equals(tbmonth)){
                                mssql += "and getYear(AO_WTK.fTBRQ) = getYear(:currentDate())  AND getMonth(AO_WTK.fTBRQ) = getMonth( :currentDate())";
                               
                        } else {
                                mssql += "and getYear(AO_WTK.fTBRQ) = getYear(:currentDate())  AND getMonth(AO_WTK.fTBRQ) = +tbmonth";
                        }
                } else {
                        mssql += "and getYear(AO_WTK.fTBRQ) = +tbyear  AND getMonth(AO_WTK.fTBRQ) = +tbmonth";
                }
                HashMap<String,String> sqlMap = new HashMap<String,String>();
                sqlMap.put(SQL.DEFAULT_DB_NAME,mssql);
                sqlMap.put(DatabaseProduct.MSSQL.name(),mssql);
                return SQL.select(sqlMap,null,"/AOZESYSTEM/pzwtgl/data");
        }

jishuang

java文件右键用作源码路径，有错误就会有提示，根据提示修改就可以

hbyczg

jishuang 发表于 2014-5-5 17:21
java文件右键用作源码路径，有错误就会有提示，根据提示修改就可以

呵呵，现在就是还不知道怎么改，只在原码路径中看到有红线。能否远程下呢

jishuang

public static void pzwtcscx(String tbyear,String tbmonth){

改为
public static Table pzwtcscx(String tbyear,String tbmonth){

hbyczg

jishuang 发表于 2014-5-5 17:33
public static void pzwtcscx(String tbyear,String tbmonth){

改为

以下动作代码在执行时控制台报错，错误代码也一并附上，求指点如何修改？
        public static Table pzwtcscx(String tbyear,String tbmonth){

                String mssql = "select AO_YYFX.fWTLX as fWTLX,AO_YYFX.fWTZL as fWTZL,AO_YYFX.fZRBM as fZRBM,AO_YYFX.fZRGW as fZRGW,AO_YYFX.fZRR as fZRR,AO_WTK.fWTBH as fWTBH,AO_WTK.fTBRQ as fTBRQ,AO_WTK.fDDH as fDDH,AO_WTK.fKHBH as fKHBH,AO_WTK.fBPBH as fBPBH,AO_WTK.fBPMC as fBPMC,AO_WTK.fCPBH as fCPBH,AO_WTK.fCPMC as fCPMC,AO_YYFX.fYZCD as fYZCD,(getYear(AO_WTK.fTBRQ)) as tbyear,(getMonth(AO_WTK.fTBRQ)) as tbmonth from AO_YYFX AO_YYFX optional  join AO_WTK AO_WTK on AO_YYFX.fMasterID = AO_WTK";
               
                mssql += " WHERE 1=1 ";
               
                if(!"".equals(tbyear)){
                        if(!"".equals(tbmonth)){
                                mssql += "and getYear(AO_WTK.fTBRQ) = getYear(:currentDate())  AND getMonth(AO_WTK.fTBRQ) = getMonth( :currentDate())";
                               
                        } else {
                                mssql += "and getYear(AO_WTK.fTBRQ) = getYear(:currentDate())  AND getMonth(AO_WTK.fTBRQ) = +tbmonth";
                        }
                } else {
                        mssql += "and getYear(AO_WTK.fTBRQ) = +tbyear  AND getMonth(AO_WTK.fTBRQ) = +tbmonth";
                }
                HashMap<String,String> sqlMap = new HashMap<String,String>();
                sqlMap.put(SQL.DEFAULT_DB_NAME,mssql);
                sqlMap.put(DatabaseProduct.MSSQL.name(),mssql);
                return SQL.select(sqlMap,null,"/AOZESYSTEM/pzwtgl/data");
        }

控制台错误代码如下：
2014-05-06 08:48:10 开始-WINDOW生成中间文件: /UI/AOZESYSTEM/pzwtgl/process/report/pzwtfbtjActivity1.w
2014-05-06 08:48:10 结束-WINDOW生成中间文件: /UI/AOZESYSTEM/pzwtgl/process/report/pzwtfbtjActivity1.w
2014-05-06 08:48:10 开始-中间文件生成缓存文件: /UI/AOZESYSTEM/pzwtgl/process/report/pzwtfbtjActivity1.w
2014-05-06 08:48:10 结束-中间文件生成缓存文件: /UI/AOZESYSTEM/pzwtgl/process/report/pzwtfbtjActivity1.w
2014-05-06 08:48:20 action:<?xml version="1.0" encoding="UTF-8"?>
<action direct-execute="true" process="/AOZESYSTEM/pzwtgl/process/report/reportProcess" activity="pzwtfbtjActivity1" action-flag="__action_0__" executor="PSN01" execute-context="" name="pzwtcscx"><parameters xmlns:xbiz="http://www.justep.com/xbiz#"><parameter name="tbyear"><xbiz:simple type="http://www.w3.org/2001/XMLSchema#String">2014</xbiz:simple></parameter><parameter name="tbmonth"><xbiz:simple type="http://www.w3.org/2001/XMLSchema#String">04</xbiz:simple></parameter></parameters></action>
java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:592)
        at com.justep.system.action.Engine.invokeActions(Native Method)
        at com.justep.system.action.Engine.invokeActions(Unknown Source)
        at com.justep.business.server.BusinessServer.doAction(Unknown Source)
        at com.justep.business.server.BusinessServer.doExcute(Unknown Source)
        at com.justep.business.server.BusinessServer.excute(Unknown Source)
        at com.justep.business.server.BusinessServerServlet.execService(Unknown Source)
        at com.justep.business.server.BusinessServerServlet.service(Unknown Source)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at com.justep.x.bs.BusinessServerServlet.service(Unknown Source)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
        at java.lang.Thread.run(Thread.java:595)
Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150039; 提示: 执行sql: select AO_YYFX.fWTLX as fWTLX,AO_YYFX.fWTZL as fWTZL,AO_YYFX.fZRBM as fZRBM,AO_YYFX.fZRGW as fZRGW,AO_YYFX.fZRR as fZRR,AO_WTK.fWTBH as fWTBH,AO_WTK.fTBRQ as fTBRQ,AO_WTK.fDDH as fDDH,AO_WTK.fKHBH as fKHBH,AO_WTK.fBPBH as fBPBH,AO_WTK.fBPMC as fBPMC,AO_WTK.fCPBH as fCPBH,AO_WTK.fCPMC as fCPMC,AO_YYFX.fYZCD as fYZCD,(getYear(AO_WTK.fTBRQ)) as tbyear,(getMonth(AO_WTK.fTBRQ)) as tbmonth from AO_YYFX AO_YYFX optional  join AO_WTK AO_WTK on AO_YYFX.fMasterID = AO_WTK WHERE 1=1 and getYear(AO_WTK.fTBRQ) = getYear(:currentDate())  AND getMonth(AO_WTK.fTBRQ) = getMonth( :currentDate()), binds: []出错
        at com.justep.exception.BusinessException.create(Unknown Source)
        at com.justep.system.data.SQL.select(Unknown Source)
        at com.justep.system.data.SQL.select(Unknown Source)
        at Pzwtgl.pzwtcscx(Pzwtgl.java:67)
        ... 26 more
Caused by: java.sql.SQLException: 'getYear' 不是可以识别的 内置函数名称。
        at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
        at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
        at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
        at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:418)
        at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:693)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        ... 29 more
[ERROR] 2014-05-06 08:48:20,796 com.justep.report.controller.DataController - 报表数据获取失败
执行sql: select AO_YYFX.fWTLX as fWTLX,AO_YYFX.fWTZL as fWTZL,AO_YYFX.fZRBM as fZRBM,AO_YYFX.fZRGW as fZRGW,AO_YYFX.fZRR as fZRR,AO_WTK.fWTBH as fWTBH,AO_WTK.fTBRQ as fTBRQ,AO_WTK.fDDH as fDDH,AO_WTK.fKHBH as fKHBH,AO_WTK.fBPBH as fBPBH,AO_WTK.fBPMC as fBPMC,AO_WTK.fCPBH as fCPBH,AO_WTK.fCPMC as fCPMC,AO_YYFX.fYZCD as fYZCD,(getYear(AO_WTK.fTBRQ)) as tbyear,(getMonth(AO_WTK.fTBRQ)) as tbmonth from AO_YYFX AO_YYFX optional  join AO_WTK AO_WTK on AO_YYFX.fMasterID = AO_WTK WHERE 1=1 and getYear(AO_WTK.fTBRQ) = getYear(:currentDate())  AND getMonth(AO_WTK.fTBRQ) = getMonth( :currentDate()), binds: []出错
com.justep.report.exception.ReportServerException: 报表数据获取失败
        at com.justep.report.controller.DataController.execute(Unknown Source)
        at com.justep.report.controller.GenericControllerChain.execute(Unknown Source)
        at com.justep.report.controller.InitController.execute(Unknown Source)
        at com.justep.report.controller.GenericControllerChain.execute(Unknown Source)
        at com.justep.report.Servlet.a(Unknown Source)
        at com.justep.report.Servlet.doPost(Unknown Source)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
        at java.lang.Thread.run(Thread.java:595)
Caused by: com.justep.report.exception.ReportServerException: 执行sql: select AO_YYFX.fWTLX as fWTLX,AO_YYFX.fWTZL as fWTZL,AO_YYFX.fZRBM as fZRBM,AO_YYFX.fZRGW as fZRGW,AO_YYFX.fZRR as fZRR,AO_WTK.fWTBH as fWTBH,AO_WTK.fTBRQ as fTBRQ,AO_WTK.fDDH as fDDH,AO_WTK.fKHBH as fKHBH,AO_WTK.fBPBH as fBPBH,AO_WTK.fBPMC as fBPMC,AO_WTK.fCPBH as fCPBH,AO_WTK.fCPMC as fCPMC,AO_YYFX.fYZCD as fYZCD,(getYear(AO_WTK.fTBRQ)) as tbyear,(getMonth(AO_WTK.fTBRQ)) as tbmonth from AO_YYFX AO_YYFX optional  join AO_WTK AO_WTK on AO_YYFX.fMasterID = AO_WTK WHERE 1=1 and getYear(AO_WTK.fTBRQ) = getYear(:currentDate())  AND getMonth(AO_WTK.fTBRQ) = getMonth( :currentDate()), binds: []出错
        at com.justep.report.controller.DataController.a(Unknown Source)
        at com.justep.report.controller.DataController.a(Unknown Source)
        ... 20 more
Caused by: java.lang.Exception
        ... 22 more

jishuang

你好楼主，该问题准备远程。（已远程）