EAS BOS sql 查询

在EditUI中查询

private BigDecimal getBatchIncomeBillAmount(String projectId, String createTime, String type) throws BOSException, SQLException {
		StringBuilder sqlAimCostResoure = new StringBuilder();
		sqlAimCostResoure.append("select sum(b.famount) amount from T_EC_BatchIncomeBill a")
			.append(" left join T_EC_BatchIncomeEntry b on a.fid = b.fparentid ")
			.append("where a.fprojectid = '"+projectId+"' and a.fbillsate = '03' and fAuditDate = '"+createTime+"'")
			.append(" and CFRECEIVABLESTYPE = '"+type+"'");
		
		ISQLExecutor executor = SQLExecutorFactory.getRemoteInstance(sqlAimCostResoure.toString());
		IRowSet irRowSet = executor.executeSQL(); 
		if (irRowSet.size() > 0) {
			while(irRowSet.next()) {
				BigDecimal amount = irRowSet.getBigDecimal("amount");
				return amount != null ? amount : BigDecimal.ZERO;
			}
		}
		return BigDecimal.ZERO;
	}

在ControllerBean中查询

public BigDecimal getAimHunmanTotalAmount(Context ctx,String projectId) throws BOSException, SQLException{
		StringBuilder sqlAimCostResoure = new StringBuilder();
		
		sqlAimCostResoure.append("select sum(acr.famount) amount from T_EC_AimCostResource acr left join T_EC_AimCostBill acb ")
			.append("on acb.fid = acr.fbillId left join T_EC_CostAccount ca on ca.fid = acr.fcostAccountid ")
			.append("where acb.fprojectID = ? and acb.fbillSate = '03' and ca.fnumber = '01.01' and acb.fisvalid = 1 ");
		IRowSet irRowSet = DbUtil.executeQuery(ctx, sqlAimCostResoure.toString(), new Object[]{projectId});
		if(irRowSet.size() > 0){
			while(irRowSet.next()){
				return irRowSet.getBigDecimal("amount");
			}
		}
		return BigDecimal.ZERO;
	}