如何在KSQL自定义字段中使用toChineseMoney（）函数？

abetsy

select orderMain.*,Supplier,Supplier.fSupplierCode as fSupplierCode,Supplier.fSupplierName as fSupplierName,Supplier.fFindCode as fFindCode,Supplier.fFullName as fFullName,Supplier.fAreaCode as fAreaCode,Supplier.fContactor as fContactor,Supplier.fPhone as fPhone,Supplier.fFax as fFax,Supplier.fAddress as fAddress,Supplier.fPostCode as fPostCode,Supplier.fBank as fBank,Supplier.fBankId as fBankId,Supplier.fTaxId as fTaxId,Supplier.fEmail as fEmail,Supplier.fUrl as fUrl,Supplier.fMobilePhone as fMobilePhone,Supplier.fMemo as fMemo,Supplier.fFirstPay as fFirstPay,Supplier.fPrePay as fPrePay,Supplier.fPayable as fPayable,Supplier.fIsSent as fIsSent,Supplier.fSupplyGoods as fSupplyGoods,Supplier.fLicenseNo as fLicenseNo,Supplier.fRemark as fRemark,Customer.fCustomerCode as fCustomerCode,Customer.fCustomerName as fCustomerName,Customer.fFullName as fFullName1,(:toChineseMoney( orderMain.fTotalAmount )) as fChineseMoney
    from orderMain orderMain
     optional  join Supplier Supplier on orderMain.fSupplierId = Supplier
     optional  join Customer Customer on orderMain.fSupplierId = Customer
    where orderMain =

在报表的KSQL中自定义字段(:toChineseMoney( orderMain.fTotalAmount )) as fChineseMoney 出错，不能获取数据 。谁有报表用这个函数的案例吗？toChineseMoney

jishuang

内存函数不能在select部分用，执行的时候先执行内存函数，然后执行sql,执行内存函数的时是取不到关系的值的，可以用SQL.调用数据库函数实现

abetsy

import org.dom4j.*;

import com.justep.message.CommonMessages;
import com.justep.model.ModelUtils;
import com.justep.system.data.*;
import com.justep.system.util.BizUtils;
import com.justep.util.Utils;
import com.justep.system.util.CommonUtils;

public class TccOptics {


        public static void ksqlOrderMain(String ksql,Map variables,String dataModel,String fnModel){
                Utils.check(Utils.isNotEmptyString(ksql) && Utils.isNotEmptyString(dataModel),
                                CommonMessages.class, CommonMessages.PARAM_NULL1, "ksql, dataModel");
                Table table = KSQL.select(ksql, variables, dataModel, Utils.isEmptyString(fnModel) ? null : ModelUtils.getModel(fnModel));
                //拿到所有记录数组
                Iterator<Row> rows=table.iterator();
                while (rows.hasNext()){
                        Row row=rows.next();
                        //给fToChineseMoney字段赋值
                        row.setString("fToChineseMoney", String CommonUtils.toChineseMoney(row.getValue("fTotalAmount")));
          
                }
                return table;
        }
}

以上 action 代码执行出错

warning: [options] bootstrap class path not set in conjunction with -source 1.5
warning: [options] source value 1.5 is obsolete and will be removed in a future release
warning: [options] target value 1.5 is obsolete and will be removed in a future release
warning: [options] To suppress warnings about obsolete options, use -Xlint:-options.
E:\BeX5_V3.6\model\BIZ\a2016\TccOptics\logic\code\dsrc\TccOptics.java:26: error: ')' expected
                        row.setString("fToChineseMoney", String CommonUtils.toChineseMoney(row.getValue("fTotalAmount")));
                                                               ^
E:\BeX5_V3.6\model\BIZ\a2016\TccOptics\logic\code\dsrc\TccOptics.java:26: error: illegal start of expression
                        row.setString("fToChineseMoney", String CommonUtils.toChineseMoney(row.getValue("fTotalAmount")));
                                                                           ^
E:\BeX5_V3.6\model\BIZ\a2016\TccOptics\logic\code\dsrc\TccOptics.java:26: error: ';' expected
                        row.setString("fToChineseMoney", String CommonUtils.toChineseMoney(row.getValue("fTotalAmount")));
                                                                                          ^
E:\BeX5_V3.6\model\BIZ\a2016\TccOptics\logic\code\dsrc\TccOptics.java:26: error: ';' expected
                        row.setString("fToChineseMoney", String CommonUtils.toChineseMoney(row.getValue("fTotalAmount")));
                                                                                                                       ^
4 errors
4 warnings

        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

String CommonUtils.toChineseMoney(row.getValue("fTotalAmount")这个改为定义变量的方式看看

abetsy

改成以下代码，已经OK啦，谢谢jishuang老师。可以结贴。

public static Table ksqlOrderMain(String ksql,Map<String, Object> variables,String dataModel,String fnModel){
                               
                        Utils.check(Utils.isNotEmptyString(ksql) && Utils.isNotEmptyString(dataModel),
                                        CommonMessages.class, CommonMessages.PARAM_NULL1, "ksql, dataModel");
                        Table table= KSQL.select(ksql, variables, dataModel, Utils.isEmptyString(fnModel) ? null : ModelUtils.getModel(fnModel));
                        Iterator<Row> rows = table.iterator();
                         while (rows.hasNext()) {
                                Row row = rows.next();
                                //给fToChineseMoney字段赋值
                                Number number= (Number) row.getValue("fTotalAmount");
                               
                row.setString("fToChineseMoney", "总计金额(大写,人民币):"+CommonUtils.toChineseMoney(number));
                         }
                       
                        return table ;
        }