报表传参的问题？

yinlun

hbyczg 发表于 2014-5-4 08:56
按照以上代码写的，控制报错如下，我没找出问题在那儿
2014-05-04 08:52:51 action:
getYear(AO_WTK.fTBR ...

你试试下面的语句，能否执行成功

1. 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  
   
2. WHERE  getYear(AO_WTK.fTBRQ) = 2014  AND getMonth(AO_WTK.fTBRQ) = 4

复制代码

hbyczg

yinlun 发表于 2014-5-4 10:22
你试试下面的语句，能否执行成功

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 tbyear1,(getMonth(AO_WTK.fTBRQ)) as tbmonth1
    from AO_YYFX AO_YYFX
     optional  join AO_WTK AO_WTK on AO_YYFX.fMasterID = AO_WTK
    where getYear(AO_WTK.fTBRQ) = 2014  AND getMonth(AO_WTK.fTBRQ) = 4 LIMIT 0, 50

我用的是KSQL，如上语句是可以执行的

hbyczg

jishuang 发表于 2014-5-4 10:20
select AO_YYFX.fWTLX as fWTLX,AO_YYFX.fWTZL as fWTZL,AO_YYFX.fZRBM as fZRBM,AO_YYFX.fZRGW as fZRGW,A ...

是用6楼的方法：
var pzwtfbtjActivity1 = {};

pzwtfbtjActivity1.gridModelLoad = function(event){
          
            var tbyear = justep.xbl('data1').getValue('year');
        var tbmonth = justep.xbl('data1').getValue('month');
        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;
                }
        }
   
     
       justep.xbl('r1').setStringVar('lsql',lsql);

justep.xbl('report1').refresh();
       
};

yinlun

hbyczg 发表于 2014-5-4 10:48
select AO_YYFX.fWTLX as fWTLX,AO_YYFX.fWTZL as fWTZL,AO_YYFX.fZRBM as fZRBM,AO_YYFX.fZRGW as fZRGW ...

能执行就好；
你把var tbmonth = justep.xbl('data1').getValue('month');改成var tbmonth = justep.xbl('data1').getValue('month') * 1;

再试试

jishuang

参数查询参考/UI/demo/report/process/property/parameterFilterReport.w

hbyczg

yinlun 发表于 2014-5-4 11:23
能执行就好；
你把var tbmonth = justep.xbl('data1').getValue('month');改成var tbmonth = justep.xbl( ...

我加了也不行，下午能不能帮忙远程一下

hbyczg

jishuang 发表于 2014-5-4 11:23
参数查询参考/UI/demo/report/process/property/parameterFilterReport.w

就是参照了这个和那个KSQL传参做的

jishuang

hbyczg 发表于 2014-5-4 12:05
就是参照了这个和那个KSQL传参做的

这个传参不是把where条件当参数传递，只传递自己需要的参数，where条件在action中拼的

hbyczg

jishuang 发表于 2014-5-4 13:18
这个传参不是把where条件当参数传递，只传递自己需要的参数，where条件在action中拼的 ...

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

jishuang

用action参考

1. public static Table parameterFilterQuery(String name, String id){
   
2.                
   
3.                 String oracle = "SELECT RPRODUCTID , RPRODUCTNAME , (SELECT RCATEGORYNAME FROM DEMO_REPORTPRODUCTCATEGORY WHERE DEMO_REPORTPRODUCTCATEGORY.RCATEGORYID = DEMO_REPORTPRODUCTS.RCATEGORYID) AS RCATEGORYNAME , (SELECT RCOMPANYNAME FROM DEMO_REPORTSUPPLIERS WHERE DEMO_REPORTSUPPLIERS.RSUPPLIERID = DEMO_REPORTPRODUCTS.RSUPPLIERID)AS RCOMPANYNAME,RQUANTITYPERUNIT,RUNITPRICE,RUNITSINSTOCK FROM DEMO_REPORTPRODUCTS";
   
4.                 String mssql = "SELECT RPRODUCTID , RPRODUCTNAME , (SELECT RCATEGORYNAME FROM DEMO_REPORTPRODUCTCATEGORY WHERE DEMO_REPORTPRODUCTCATEGORY.RCATEGORYID = DEMO_REPORTPRODUCTS.RCATEGORYID) AS RCATEGORYNAME , (SELECT RCOMPANYNAME FROM DEMO_REPORTSUPPLIERS WHERE DEMO_REPORTSUPPLIERS.RSUPPLIERID = DEMO_REPORTPRODUCTS.RSUPPLIERID)AS RCOMPANYNAME,RQUANTITYPERUNIT,RUNITPRICE,RUNITSINSTOCK FROM DEMO_REPORTPRODUCTS";
   
5.                
   
6.                 oracle += " WHERE 1=1 ";
   
7.                 mssql += " WHERE 1=1 ";
   
8.                
   
9.                 if(!"".equals(name)){
   
10.                         oracle += "and RPRODUCTNAME like '%"+name+"%' ";
    
11.                         mssql += "and RPRODUCTNAME like '%"+name+"%' ";
    
12.                 }
    
13.                 if(!"".equals(id)){
    
14.                         oracle += String.format("and RPRODUCTID = '%s' ", id);
    
15.                         mssql += String.format("and RPRODUCTID = '%s' ", id);
    
16.                 }
    
17.                
    
18.                 HashMap<String,String> sqlMap = new HashMap<String,String>();
    
19.                 sqlMap.put(SQL.DEFAULT_DB_NAME,mssql);
    
20.                 sqlMap.put(DatabaseProduct.ORACLE.name(),oracle);
    
21.                 sqlMap.put(DatabaseProduct.MSSQL.name(),mssql);
    
22.                 sqlMap.put(DatabaseProduct.KINGBASE.name(),oracle);
    
23.                 sqlMap.put(DatabaseProduct.SYBASE.name(),mssql);
    
24.                 sqlMap.put(DatabaseProduct.DB2.name(),oracle);
    
25.                 return SQL.select(sqlMap,null,"/demo/report/data");
    
26.         }

复制代码

例子中的是sql自己可以用ksql实现，不同的条件拼接不同的where条件