|
|
楼主 |
发表于 2015-4-20 14:41:40
|
显示全部楼层
public static Table queryHI_BXZXTJ(String concept,String select,String from,String aggregate,String dataModel,String fnModel,String condition,List range,Boolean distinct,String idColumn,String filter,Integer limit,Integer offset,String columns,String orderBy,String aggregateColumns,Map variables) throws Exception{
//System.out.println("filter=" + filter);
String fName=ContextHelper.getPersonMember().getOgn().getFName();
String ognID=ContextHelper.getPersonMember().getOgn().getID();
String pType="全部";
if(hasRoleByCode("BXLDCK")){
pType="全部";
}else{
if(fName.indexOf("枢纽场站")>0){
pType="场站";
}else{
pType="公司机关";
ognID=ContextHelper.getPersonMember().getDept().getID();
}
}
//System.out.println("year11=" + variables.size());
String year=variables.get("year").toString();
String lastYear=(Integer.parseInt(year)-1)+"";
String bDate=variables.get("bdate").toString();
String eDate=variables.get("edate").toString();
//System.out.println("year=" + year);
String sql = "select * from (SELECT T1.FID,T1.VERSION,T1.FNAME,T1.FSEQUENCE,T1.FPARENT AS FSJKM,getReimbursemonth(t1.fid,'"+year+"','"+bDate+"','"+eDate+"','"+ognID+"','"+pType+"')/10000 AS FBXJE,getReimbursemonth(t1.fid,'"+lastYear+"','"+bDate+"','"+eDate+"','"+ognID+"','"+pType+"')/10000 AS FSNBX,getReimbursemonth(t1.fid,'"+year+"','"+year+"-01-01','"+eDate+"','"+ognID+"','"+pType+"')/10000 AS FBNLJBX, getBuget(t1.fid,'"+ognID+"','"+year+"','"+pType+"') AS FSUMPRICE,'"+pType+"' as fJGLX FROM HIMS_FL_BUDGETITEM T1) HI_BXZXTJ where "+ filter;
Connection conn = null;
ResultSet rs = null;
String tableColNames = "";
String tableColTpyes = "";
String tableColIds = "";
int RowNum = 0;
Table table = null;
try {
conn = ModelUtils.getConnection(data_model);
Model model = ModelUtils.getRootModel();
rs = ExecSQL(conn, sql);
ResultSetMetaData rmd = rs.getMetaData();
List<String> names = new ArrayList<String>();
List<String> types = new ArrayList<String>();
for (int i = 1; i <= rmd.getColumnCount(); i++) {
names.add(rmd.getColumnName(i));
types.add(ColumnTypes.transSqlType(rmd.getColumnType(i)));
if (i == 1) {
tableColIds = rmd.getColumnName(i);
tableColTpyes = ColumnTypes.transSqlType(rmd.getColumnType(i));
//tableColNames="字段"+i;
} else {
tableColIds = tableColIds + "," + rmd.getColumnName(i);
tableColTpyes = tableColTpyes + "," + ColumnTypes.transSqlType(rmd.getColumnType(i));
//tableColNames=tableColNames+","+"字段"+i;
}
}
table = TableUtils.createTable(model, names, types);
table.getProperties().put(Table.PROP_NAME_ROWID, "FID");
table.getProperties().put(Table.PROP_DB_COUNT, RowNum);
table.getProperties().put("relations", tableColIds);
table.getProperties().put("relation-types", tableColTpyes);
table.getProperties().put("relation-names", tableColNames);
while (rs.next()) {
Row row = table.appendRow();
for (ColumnMetaData cmd : table.getMetaData().getColumnMetaDatas()) {
if (ColumnTypes.STRING.equals(cmd.getType()))
row.setString(cmd.getName(), rs.getString(cmd.getName()));
else if (ColumnTypes.INTEGER.equals(cmd.getType())) {
rs.getObject(cmd.getName());
if (rs.wasNull())
row.setInteger(cmd.getName(), null);
else
row.setInt(cmd.getName(), rs.getInt(cmd.getName()));
} else if (ColumnTypes.BLOB.equals(cmd.getType()))
row.setBlob(cmd.getName(), rs.getBlob(cmd.getName()));
else if (ColumnTypes.DATE.equals(cmd.getType()))
row.setDate(cmd.getName(), rs.getDate(cmd.getName()));
else if (ColumnTypes.DATETIME.equals(cmd.getType()))
row.setDateTime(cmd.getName(), rs.getTimestamp(cmd.getName()));
else if (ColumnTypes.DECIMAL.equals(cmd.getType()))
row.setDecimal(cmd.getName(), rs.getBigDecimal(cmd.getName()));
else if (ColumnTypes.FLOAT.equals(cmd.getType())) {
rs.getObject(cmd.getName());
if (rs.wasNull())
row.setFloatObject(cmd.getName(), null);
else
row.setFloat(cmd.getName(), rs.getFloat(cmd.getName()));
} else if (ColumnTypes.OBJECT.equals(cmd.getType()))
row.setValue(cmd.getName(), rs.getObject(cmd.getName()));
else if (ColumnTypes.TEXT.equals(cmd.getType()))
row.setText(cmd.getName(), rs.getString(cmd.getName()));
else if (ColumnTypes.TIME.equals(cmd.getType()))
row.setTime(cmd.getName(), rs.getTime(cmd.getName()));
else
throw new ModelException("接收到错误的columnType：" + cmd.getType());
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
rs.close();
rs = null;
}
if (conn != null) {
conn.close();
}
}
return table;
}
源代码 |
|
IP卡
狗仔卡
发表于 2015-4-20 14:31:09
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
显身卡
发表于 2015-4-21 09:28:32
