报表传参的问题？

hbyczg

我用KSQL传参的方法做了一个报表，其中查询的where条件设置为getYear(AO_WTK.fTBRQ) = :tbyear AND getMonth(AO_WTK.fTBRQ) = :tbmonth；在报表页面上作了两个输入框分别传参数年和月。输入年月此报表可正常显示。想问的是如何才能做到当没有输入年，月时报表显示当前年的所有数据；当只输入年不输入月时报表显示输入年的所有数据？

fpj

你这样只传一个变量即可，也就是说在传之前，对条件进行处理一下；
var lsql = '(1=1)';
if (年条件不为空){ lsql = lsql + ' and  年查询条件'} else{ lsql = lsql  + ' and  本年条件'}
if (月条件不为空){ lsql = lsql + ' and  月查询条件'} else{ lsql = lsql + ' and  本月条件' }

然后将你的  lsql  传过去进行查询
以上代码还需要你进一步的完善一下，在这里跟是你说一下思路。

hbyczg

fpj 发表于 2014-5-2 14:47
你这样只传一个变量即可，也就是说在传之前，对条件进行处理一下；
var lsql = '(1=1)';
if (年条件不为空) ...

我是这样写的，但不知那里错了。烦请帮助指点一下
        var tbyear = justep.xbl('data1').getValue('year');
        var tbmonth = justep.xbl('data1').getValue('month');
        if (tbyear =''){
            if (tbmonth =''){
                var lsql = 'getYear(AO_WTK.fTBRQ) = getYear( currentDate() )  AND getMonth(AO_WTK.fTBRQ) = getMonth( currentDate() )';
                } else {
                     var lsql = 'getYear(AO_WTK.fTBRQ) = getYear( currentDate() )  AND getMonth(AO_WTK.fTBRQ) = tbmonth';
                          } else {
                          var lsql = 'getYear(AO_WTK.fTBRQ) = tbyear  AND getMonth(AO_WTK.fTBRQ) = tbmonth';
                          }
                          };
       

        justep.xbl('r1').setStringVar('lsql',lsql);
       
        justep.xbl('report1').refresh();

fpj

hbyczg 发表于 2014-5-2 17:49
我是这样写的，但不知那里错了。烦请帮助指点一下
        var tbyear = justep.xbl('data1').getValue(' ...

将你生成的SQL语句 alter 一下看看，
或者你可以 在你的代码中 设置 debugger 分步调试一下。就怎么那里出错了。

hbyczg

fpj 发表于 2014-5-2 20:04
将你生成的SQL语句 alter 一下看看，
或者你可以 在你的代码中 设置 debugger 分步调试一下。就怎么那里 ...

对编程基础很差，不太会弄。如果方便的话能否远程下

yinlun

hbyczg 发表于 2014-5-2 17:49
我是这样写的，但不知那里错了。烦请帮助指点一下
        var tbyear = justep.xbl('data1').getValue(' ...

js中判断是否相等要用==；

而且定义变量lsql的时候，不要放在if语句里面，因为if外面要用到lsql；

1.         var tbyear = justep.xbl('data1').getValue('year');
   
2.         var tbmonth = justep.xbl('data1').getValue('month');
   
3.         var lsql = '';
   
4.         if (tbyear == '') {
   
5.                 if (tbmonth == '') {
   
6.                         lsql = 'getYear(AO_WTK.fTBRQ) = getYear(:currentDate())  AND getMonth(AO_WTK.fTBRQ) = getMonth( :currentDate() )';
   
7.                 } else {
   
8.                         lsql = 'getYear(AO_WTK.fTBRQ) = getYear(:currentDate())  AND getMonth(AO_WTK.fTBRQ) = '
   
9.                                         + tbmonth;
   
10.                 }
    
11.         } else {
    
12.                 if (tbmonth == '') {
    
13.                         lsql = 'getYear(AO_WTK.fTBRQ) = ' + tbyear;
    
14.                 } else {
    
15.                         lsql = 'getYear(AO_WTK.fTBRQ) = ' + tbyear
    
16.                                         + '  AND getMonth(AO_WTK.fTBRQ) = ' + tbmonth;
    
17.                 }
    
18.         }

复制代码

hbyczg

yinlun 发表于 2014-5-3 16:21
js中判断是否相等要用==；

而且定义变量lsql的时候，不要放在if语句里面，因为if外面要用到lsql； ...

谢谢！非常感谢！！！得好好补一下java基础，不知有没有好的书或学习方法能速成？
另外想问一下，我想做一个年份值的gridselect下拉选择，基中年份值为最近三年，如：2014;2013;2012.问题是到了2015年时选择项自动变为：2015;2014;2013应如何实现？

yinlun

hbyczg 发表于 2014-5-3 21:26
谢谢！非常感谢！！！得好好补一下java基础，不知有没有好的书或学习方法能速成？
另外想问一下，我想做一 ...

这是javaScirpt语句，不是java

可以先获取当前时间，再减去1年、2年，用下面的方法：
decrease( datetime, num, interval)
根据日期的规则，为给定的日期减去指定的时间量

hbyczg

yinlun 发表于 2014-5-3 16:21
js中判断是否相等要用==；

而且定义变量lsql的时候，不要放在if语句里面，因为if外面要用到lsql； ...

按照以上代码写的，控制报错如下，我没找出问题在那儿
2014-05-04 08:52:51 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="pzwtbmfbAction"><parameters xmlns:xbiz="http://www.justep.com/xbiz#"><parameter name="variables"><xbiz:map><item key="lsql"><xbiz:simple type="http://www.w3.org/2001/XMLSchema#String">getYear(AO_WTK.fTBRQ) = 2014  AND getMonth(AO_WTK.fTBRQ) = 04</xbiz:simple></item></xbiz:map></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: 编码: JUSTEP150210; 提示: 执行KSQL出错, KSQL: 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 :lsql
        at com.justep.exception.BusinessException.create(Unknown Source)
        at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
        at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
        at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
        at com.justep.system.data.KSQL.select(Unknown Source)
        at KSQLProcedure.query(KSQLProcedure.java:17)
        ... 26 more
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, DATEPART(yyyy, AO_WTK.FTBRQ) AS tbyear, DATEPART(mm, AO_WTK.FTBRQ) AS tbmonth FROM AO_YYFX AO_YYFX LEFT JOIN AO_WTK AO_WTK ON AO_YYFX.fMasterID = AO_WTK.fID  WHERE  ? ), binds: [getYear(AO_WTK.fTBRQ) = 2014  AND getMonth(AO_WTK.fTBRQ) = 04]出错
        at com.justep.exception.BusinessException.create(Unknown Source)
        at com.justep.system.ksql.KSQLUtils.execSqlSelect(Unknown Source)
        ... 31 more
Caused by: java.sql.SQLException: 在应使用条件的上下文(在 ')' 附近)中指定了非布尔类型的表达式。
        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)
        ... 32 more
[ERROR] 2014-05-04 08:52:51,656 com.justep.report.controller.DataController - 报表数据获取失败
执行KSQL出错, KSQL: 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 :lsql
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: 执行KSQL出错, KSQL: 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 :lsql
        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

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 :lsql这个是怎么拼到KSQL中的？