【企业报表数据读取与传递】【第二节】报表数据读取规则界面开发

界面：

1. 创建后台表结构：

参考sql：

-- Create tablecreate table IUFO_SF_CONFIG(  pk_config  VARCHAR2(20) not null,  rowno      VARCHAR2(5),  colno      VARCHAR2(5),  showname   VARCHAR2(500),  sfcode     VARCHAR2(50),  activecode VARCHAR2(200),  pk_report  VARCHAR2(20),  dr         NUMBER default 0,  ts         CHAR(19) default to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),  offsetcode VARCHAR2(500))tablespace NNC_DATA01  pctfree 10  initrans 1  maxtrans 255  storage  (    initial 256K    next 256K    minextents 1    maxextents unlimited    pctincrease 0  );

2. 界面代码

public class RepDataRuleUI extends PFToftPanel{private List<String> dellist;private List<String> dellistact;private BaseCard card;private BaseList list;private BaseList listact;private ISjkSQLHelper helper;private ButtonObject btnAdd = new ButtonObject("增加","增加","Add");private ButtonObject btnPasteHoriz = new ButtonObject("横向黏贴","横向黏贴","Paste");private ButtonObject btnPasteVert = new ButtonObject("纵向黏贴","纵向黏贴","Paste");private ButtonObject btnDel = new ButtonObject("删除", "删除", "Del");private ButtonObject btnSave = new ButtonObject("保存","保存","Save");private ButtonObject btnAddAct = new ButtonObject("增加","增加","AddAct");private ButtonObject btnDelAct = new ButtonObject("删除", "删除", "Del");private ButtonObject btnSaveAct = new ButtonObject("保存","保存","Save");private ButtonObject[] btns = new ButtonObject[]{btnAdd,btnPasteHoriz,btnPasteVert,btnDel,btnSave,new SeparatorButtonObject(),btnAddAct,btnDelAct,btnSaveAct};@Overridepublic void init(){helper = NCLocator.getInstance().lookup(ISjkSQLHelper.class);dellist =new ArrayList<String>();dellistact =new ArrayList<String>();card = new BaseCard("SJSCPZC");card.addBillEditListenerHeadTail(new BillEditListener() {@Overridepublic void bodyRowChange(BillEditEvent e) {// TODO 自动生成的方法存根}@Overridepublic void afterEdit(BillEditEvent e) {try {reportChange(e);} catch (Exception ex) {MessageBox.showError(ex);}}});list = new BaseList("SJSCPZL");list.getHeadBillModel().setEnabled(true);list.getHeadTable().setSelectionMode(ListSelectionModel.MULTIPLE_INTERVAL_SELECTION);list.addHeadEditListener(new BillEditListener(){@Overridepublic void bodyRowChange(BillEditEvent e){}@Overridepublic void afterEdit(BillEditEvent e){listEdit(e,list);}});listact = new BaseList("SJSCPZL2");listact.getHeadBillModel().setEnabled(true);listact.getHeadTable().setSelectionMode(ListSelectionModel.MULTIPLE_INTERVAL_SELECTION);listact.addHeadEditListener(new BillEditListener(){@Overridepublic void bodyRowChange(BillEditEvent e){}@Overridepublic void afterEdit(BillEditEvent e){listEdit(e,listact);}});UISplitPane pane2 = new UISplitPane(0,list,listact);pane2.setDividerLocation(500);UIRefPane refpane = (UIRefPane) card.getHeadItem("vdef1").getComponent();refpane.getRefModel().setWherePart(" pk_dir='1001A11000000000BPFV'");UISplitPane pane = new UISplitPane(0, card, pane2);pane.setDividerLocation(40);add(pane,"Center");setButtons(btns);}protected void reportChange(BillEditEvent e) throws Exception {String[] pks = (String[]) e.getValue();String pk_report = pks[0];loadData(pk_report);}private void loadData(String pk_report) throws Exception{String sql = "select pk_config vdef1,'N' vdef2,rowno vdef3,colno vdef4,showname vdef5,sfcode vdef6,activecode vdef7"+",offsetcode vdef8 from iufo_sf_config where dr=0 and pk_report='"+pk_report+"'";SFContBookSupVO[] vos = SQLHelper.getVosBySQL(sql, SFContBookSupVO.class);list.getHeadBillModel().setBodyDataVO(vos);String sql2 = "select pk_config_act vdef1,'N' vdef2,cellname vdef3,activecode vdef4,numcol vdef5,nummode vdef6 from" +" iufo_sf_config_act where dr=0 and pk_report='"+pk_report+"'";SFContBookSupVO[] vos2 = SQLHelper.getVosBySQL(sql2, SFContBookSupVO.class);listact.getHeadBillModel().setBodyDataVO(vos2);}protected void listEdit(BillEditEvent e, BaseList list){list.getHeadBillModel().setValueAt(new UFBoolean(true), e.getRow(),"vdef2");list.getHeadBillModel().getBodyValueRowVO(e.getRow(), SFContBookSupVO.class.getName()).setAttributeValue("vdef2", new UFBoolean(true));}private void doSave(BaseList list, List<String> dellist) throws Exception{if(null!=list.getHeadTable().getCellEditor())list.getHeadTable().getCellEditor().stopCellEditing();SFContBookSupVO[] vos = (SFContBookSupVO[]) list.getHeadBillModel().getBodyValueVOs(SFContBookSupVO.class.getName());String pk_report = card.getHeadItem("vdef1").getValue();for(SFContBookSupVO vo : vos){String tempcode = list.getM_billtempcode();if(tempcode.equals("SJSCPZL")){if(vo.getVdef1()==null){String insert = "insert into iufo_sf_config(pk_config,rowno,colno,showname,sfcode,activecode,offsetcode,pk_report) values ("+"get_ncpk(''),'"+vo.getVdef3()+"','"+vo.getVdef4()+"','"+vo.getVdef5()+"','"+vo.getVdef6()+"','"+vo.getVdef7()+"','"+vo.getVdef8()+"','"+pk_report+"')";insert = insert.replace("null", "");helper.updateSQL(insert);}else{if(vo.getVdef2()!=null&&vo.getVdef2().equals("Y")){String update = "update iufo_sf_config set rowno='"+vo.getVdef3()+"',colno='"+vo.getVdef4()+"',showname='"+vo.getVdef5()+"',sfcode='"+vo.getVdef6()+"',activecode='"+vo.getVdef7()+"',offsetcode='"+vo.getVdef8()+"' where pk_config='"+vo.getVdef1()+"'";update = update.replace("null", "");helper.updateSQL(update);}}}else{if(vo.getVdef1()==null){String insert = "insert into iufo_sf_config_act(pk_config_act,cellname,activecode,numcol,nummode,pk_report) values ("+"get_ncpk(''),'"+vo.getVdef3()+"','"+vo.getVdef4()+"','"+vo.getVdef5()+"','"+vo.getVdef6()+"','"+pk_report+"')";insert = insert.replace("null", "");helper.updateSQL(insert);}else{if(vo.getVdef2()!=null&&vo.getVdef2().equals("Y")){String update = "update iufo_sf_config_act set cellname='"+vo.getVdef3()+"',activecode='"+vo.getVdef4()+"',numcol='"+vo.getVdef5()+"',nummode='"+vo.getVdef6()+"' where pk_config_act='"+vo.getVdef1()+"'";update = update.replace("null", "");helper.updateSQL(update);}}}}for(String str : dellist){String tempcode = list.getM_billtempcode();String tablename = "iufo_sf_config";String pkname = "pk_config";if(!tempcode.equals("SJSCPZL")){tablename = "iufo_sf_config_act";pkname = "pk_config_act";}String updatesql = "update "+tablename+" set dr=1 where  "+pkname+"='"+str+"'";helper.updateSQL(updatesql);}MessageBox.showMessageDialog("提示！", "保存成功！");loadData(pk_report);}private void doDel(BaseList list, List<String> dellist){int selrow = list.getHeadTable().getSelectedRow();if(selrow==-1){MessageBox.showMessageDialog("提示", "请选择要删除的行");return;}String pk_id=(String) list.getHeadBillModel().getValueAt(selrow, "vdef1");dellist.add(pk_id);list.getHeadBillModel().delLine(new int[]{selrow});if(list.getHeadTable().getRowCount()>0)list.getHeadTable().setRowSelectionInterval(selrow-1, selrow-1);}private void doAdd(BaseList list) throws BusinessException{String pk_report = card.getHeadItem("vdef1").getValue();if(pk_report==null)throw new BusinessException("请先选择报表！");list.getHeadBillModel().addLine();}private void doPasteHoriz() {if(null!=list.getHeadTable().getCellEditor())list.getHeadTable().getCellEditor().stopCellEditing();int[] selrows = list.getHeadTable().getSelectedRows();if(ArrayUtil.isArrayIsNull(selrows)){MessageBox.showMessageDialog("提示", "请选择需要复制的行！");return;}for(int i=0;i<selrows.length;i++){int selrow = selrows[i];int rowcount = list.getHeadTable().getRowCount();list.getHeadBillModel().addLine();for(int j=3;j<9;j++){String colname = "vdef"+j;Object value = list.getHeadBillModel().getValueAt(selrow, colname);if(colname.equals("vdef4")){UFDouble ufd = new UFDouble(value.toString());ufd = ufd.add(1);value = ufd;}list.getHeadBillModel().setValueAt(value, rowcount, colname);}}reFocusCell();}private void doPasteVert() {if(null!=list.getHeadTable().getCellEditor())list.getHeadTable().getCellEditor().stopCellEditing();int[] selrows = list.getHeadTable().getSelectedRows();if(ArrayUtil.isArrayIsNull(selrows)){MessageBox.showMessageDialog("提示", "请选择需要复制的行！");return;}for(int i=0;i<selrows.length;i++){int selrow = selrows[i];int rowcount = list.getHeadTable().getRowCount();list.getHeadBillModel().addLine();for(int j=3;j<9;j++){String colname = "vdef"+j;Object value = list.getHeadBillModel().getValueAt(selrow, colname);if(colname.equals("vdef3")){UFDouble ufd = new UFDouble(value.toString());ufd = ufd.add(1);value = ufd;}list.getHeadBillModel().setValueAt(value, rowcount, colname);}}reFocusCell();}private void reFocusCell(){int rowcount = list.getHeadTable().getRowCount();list.getHeadTable().setRowSelectionInterval(rowcount-1, rowcount-1);list.getHeadTable().setColumnSelectionInterval(2, 2);list.getHeadTable().editCellAt(rowcount-1, 2);BillCellEditor editor = (BillCellEditor)list.getHeadTable().getCellEditor();UIRefPane pane = (UIRefPane) editor.getComponent();UIRefPaneTextField txtfield = (UIRefPaneTextField) pane.getComponents()[0];txtfield.requestFocus();txtfield.selectAll();UITable table = list.getHeadTable();Rectangle rect = table.getCellRect(rowcount-1, 0, true);table.scrollRectToVisible(rect);}@Overridepublic String getTitle(){return null;}@Overridepublic void onButtonClicked(ButtonObject bo){try{if(bo==btnAdd)doAdd(list);if(bo==btnPasteHoriz)doPasteHoriz();if(bo==btnPasteVert)doPasteVert();if(bo==btnDel)doDel(list,dellist);if(bo==btnSave)doSave(list,dellist);if(bo==btnAddAct)doAdd(listact);if(bo==btnDelAct)doDel(listact,dellistact);if(bo==btnSaveAct)doSave(listact,dellistact);}catch(Exception ex){MessageBox.showError(ex);return;}}}