码

   1 package com.kingdee.eas.custom;   2 import java.io.File;   3 import java.io.FileNotFoundException;   4 import java.io.FileOutputStream;   5 import java.io.IOException;   6 import java.io.InputStream;   7 import java.io.PRintWriter;   8 import java.io.StringWriter;   9 import java.math.BigDecimal;  10 import java.sql.SQLException;  11 import java.text.ParseException;  12 import java.text.SimpleDateFormat;  13 import java.util.ArrayList;  14 import java.util.Date;  15 import java.util.HashMap;  16 import java.util.Iterator;  17 import java.util.Map;  18 import javax.swing.JFileChooser;  19 import javax.swing.JOptionPane;  20 import org.mozilla.javascript.Context;  21 import jxl.Cell;  22 import jxl.Sheet;  23 import jxl.Workbook;  24 import jxl.format.Alignment;  25 import jxl.format.Border;  26 import jxl.format.BorderLineStyle;  27 import jxl.format.Colour;  28 import jxl.read.biff.BiffException;  29 import jxl.write.Label;  30 import jxl.write.WritableCellFormat;  31 import jxl.write.WritableSheet;  32 import jxl.write.WritableWorkbook;  33 import jxl.write.WriteException;  34 import bsh.This;  35 import com.kingdee.bos.BOSException;  36 import com.kingdee.bos.ctrl.extendcontrols.KDBizPromptBox;  37 import com.kingdee.bos.ctrl.kdf.table.ICell;  38 import com.kingdee.bos.ctrl.kdf.table.IColumn;  39 import com.kingdee.bos.ctrl.kdf.table.KDTDefaultCellEditor;  40 import com.kingdee.bos.ctrl.kdf.table.KDTMergeManager;  41 import com.kingdee.bos.ctrl.kdf.table.KDTSelectBlock;  42 import com.kingdee.bos.ctrl.kdf.table.KDTable;  43 import com.kingdee.bos.ctrl.kdf.table.util.KDTableUtil;  44 import com.kingdee.bos.ctrl.swing.KDDatePicker;  45 import com.kingdee.bos.dao.IObjectCollection;  46 import com.kingdee.bos.dao.IObjectPK;  47 import com.kingdee.bos.dao.IObjectValue;  48 import com.kingdee.bos.metadata.bot.BOTMappingCollection;  49 import com.kingdee.bos.metadata.bot.BOTMappingFactory;  50 import com.kingdee.bos.metadata.bot.BOTMappingInfo;  51 import com.kingdee.bos.metadata.bot.BOTRelationCollection;  52 import com.kingdee.bos.metadata.entity.EntityViewInfo;  53 import com.kingdee.bos.metadata.entity.FilterInfo;  54 import com.kingdee.bos.metadata.entity.FilterItemInfo;  55 import com.kingdee.bos.metadata.query.util.CompareType;  56 import com.kingdee.bos.sql.ParserException;  57 import com.kingdee.bos.ui.face.CoreUIObject;  58 import com.kingdee.bos.ui.face.IUIWindow;  59 import com.kingdee.bos.ui.face.UIException;  60 import com.kingdee.bos.ui.face.UIFactory;  61 import com.kingdee.bos.util.BOSUuid;  62 import com.kingdee.eas.base.btp.BTPManagerFactory;  63 import com.kingdee.eas.base.btp.BTPTransformResult;  64 import com.kingdee.eas.base.btp.IBTPManager;  65 import com.kingdee.eas.base.codingrule.CodingRuleManagerFactory;  66 import com.kingdee.eas.base.codingrule.ICodingRuleManager;  67 import com.kingdee.eas.base.message.BMCMessageFactory;  68 import com.kingdee.eas.base.message.BMCMessageInfo;  69 import com.kingdee.eas.base.message.IBMCMessage;  70 import com.kingdee.eas.base.message.MsgBizType;  71 import com.kingdee.eas.base.message.MsgPriority;  72 import com.kingdee.eas.base.message.MsgStatus;  73 import com.kingdee.eas.base.message.MsgType;  74 import com.kingdee.eas.base.netctrl.IMutexServiceControl;  75 import com.kingdee.eas.base.netctrl.MutexParameter;  76 import com.kingdee.eas.base.netctrl.MutexServiceControlFactory;  77 import com.kingdee.eas.base.permission.UserInfo;  78 import com.kingdee.eas.basedata.master.auxacct.GeneralAsstActTypeGroupCollection;  79 import com.kingdee.eas.basedata.master.auxacct.GeneralAsstActTypeGroupFactory;  80 import com.kingdee.eas.basedata.org.AdminOrgUnitCollection;  81 import com.kingdee.eas.basedata.org.AdminOrgUnitFactory;  82 import com.kingdee.eas.basedata.org.AdminOrgUnitInfo;  83 import com.kingdee.eas.basedata.org.CtrlUnitCollection;  84 import com.kingdee.eas.basedata.org.CtrlUnitFactory;  85 import com.kingdee.eas.basedata.org.CtrlUnitInfo;  86 import com.kingdee.eas.basedata.org.PositionInfo;  87 import com.kingdee.eas.basedata.person.PersonCollection;  88 import com.kingdee.eas.basedata.person.PersonFactory;  89 import com.kingdee.eas.basedata.person.PersonInfo;  90 import com.kingdee.eas.common.client.OprtState;  91 import com.kingdee.eas.common.client.SysContext;  92 import com.kingdee.eas.common.client.UIContext;  93 import com.kingdee.eas.common.client.UIFactoryName;  94 import com.kingdee.eas.fm.common.FMIsqlFacadeFactory;  95 import com.kingdee.eas.fm.common.IFMIsqlFacade;  96 import com.kingdee.eas.framework.CoreBillBaseInfo;  97 import com.kingdee.eas.framework.client.CoreBillEditUI;  98 import com.kingdee.eas.framework.client.EditUI;  99 import com.kingdee.eas.hr.emp.PersonPositionFactory; 100 import com.kingdee.eas.hr.emp.PersonPositionInfo; 101 import com.kingdee.eas.util.SysUtil; 102 import com.kingdee.eas.util.client.EASResource; 103 import com.kingdee.eas.util.client.MsgBox; 104 import com.kingdee.jdbc.rowset.IRowSet; 105 import com.kingdee.util.Uuid; 106 /*** 107  * EAS开发工具类 108  * @author 赵戬 109  * 110  */ 111 public class UtilClass { 112     /*** 113      * 信息提示框 114      * @param value 提示内容 115      */ 116     public static void alert(String value){ 117         MsgBox.showInfo(value); 118         SysUtil.abort(); 119     } 120  121     /*** 122      * 信息提示框(带详细信息) 123      * @param value 提示内容 124      */ 125     public static void alert2(String Title,String info){ 126         MsgBox.showDetailAndOK(null, Title, info, 0); 127         SysUtil.abort(); 128     } 129  130     /*** 131      * 信息提示框（带提示） 132      * @return value 提示内容 133      */ 134     public static boolean alertReturn(String value){ 135         return MsgBox.isYes(MsgBox.showConfirm2(value)); 136     } 137  138     /*** 139      * 程序停止运行 140      */ 141     public static void Stop(){ 142         SysUtil.abort(); 143     } 144  145     /** 146      * 单据数据加锁 147      * @param billId 单据ID 148      */ 149     public static void addBillDataLock(String BillFID){ 150         IMutexServiceControl mutex = MutexServiceControlFactory.getRemoteInstance(); 151         mutex.requestBizObjIDForUpdate(BillFID); 152     } 153  154     /** 155      * 单据数据解锁 156      * @param billId 单据ID 157      */ 158     public static void removeBillDataLock(String BillFID){ 159         IMutexServiceControl mutex = MutexServiceControlFactory.getRemoteInstance(); 160         mutex.releaSEObjIDForUpdate(BillFID); 161     } 162  163     /** 164      * 根据ID获取数据是否加锁 165      * @param id 单据编号 166      * @return true 已加锁 or false 未加锁 167      */ 168     public static boolean getBillDataLockStatus(String BillFID){ 169         IMutexServiceControl mutex = MutexServiceControlFactory.getRemoteInstance(); 170         boolean returnvalue = false; 171         HashMap map = mutex.getObjIDForUpdateList(); 172         for (Iterator iter = map.keySet().iterator(); iter.hasNext();) { 173             String key = iter.next().toString(); 174             if(BillFID.equals(key.substring(0, 28))){ 175                 returnvalue = true; 176             } 177         } 178         return returnvalue; 179     } 180  181     /** 182      * 表格获取选中行某列值   183      * 返回ArrayList集合 184      * @param tblMain 列表对象 185      * @param CellName 列名 186      * @return ArrayList集合 187      *  188      * 返回遍历 189             try { 190                 ArrayList list = UtilClass.getTableCellsValue(kDTable1, "cell1"); 191                 if(list.size()>0){ 192                     for (Iterator iter = list.iterator(); iter.hasNext();) {  193                         System.out.println((String)iter.next());  194                     } 195                 }else{ 196                     UtilClass.alert("请选择要操作的记录"); 197                 } 198             } catch (Exception e1) { 199             } 200      */ 201     public static ArrayList getTableCellsValue(KDTable tblMain, String CellName){ 202         ICell cellstr; 203         Object strObj = null; 204         KDTSelectBlock block = null; 205         ArrayList returnValue = new ArrayList(); 206         int size = tblMain.getSelectManager().size(); 207         for (int i = 0; i < size; i++) { 208             block = tblMain.getSelectManager().get(i); 209             for (int j = block.getTop(); j <= block.getBottom(); j++) { 210                 cellstr = tblMain.getRow(j).getCell(CellName); 211                 strObj =  cellstr.getValue(); 212                 returnValue.add(strObj); 213             } 214         } 215         return returnValue; 216     } 217  218     /** 219      * 设置List默认查询条件 220      * @param mainQuery List MainQuery 221      * @param Filter 条件 222      * 例：UtilClass.setListQueryFilter(mainQuery, "orderid.batchnum is not null"); 223      */ 224     public static void setListQueryFilter(EntityViewInfo mainQuery,String Filter){ 225         try { 226             mainQuery.setFilter(Filter); 227         } catch (ParserException e) { 228             alert2("设置List默认查询条件出错！",e.getMessage()); 229         } 230     } 231     /** 232      * 获取KDTable选择行的行号(选择行的第一行) 233      * @param tblMain 234      * @return 行号 235      */ 236     public static int getRowNumFirst(KDTable tblMain){ 237         return tblMain.getSelectManager().get(0).getTop(); 238     } 239     /** 240      * 获取KDTable选择行的行号 241      * @param tblMain 242      * @return 行号 243      */ 244     public static int[] getRowNum(KDTable tblMain){ 245         return KDTableUtil.getSelectedRows(tblMain); 246     } 247     /** 248      * 导出KDTable表格数据到Excel文件 249      * @param table KDTable 250      * @param RowNums 行号集合 如果RowNums行数为0，导出全部数据 251      * return 生成文件目录 252      * 例：UtilClass.TableExpot(kDTable1, new int[0], null); 253      */ 254     public static String TableExpot(KDTable table,int[] RowNums,String FileName){ 255         String returnvaleu = ""; 256         String Filepath = ""; 257         //打开目录选择器 258         try { 259             Filepath = UtilClass.OpenPathSelect(); 260             String File = ""; 261             if("".equals(Filepath)){ 262                 return returnvaleu; 263             }else{ 264                 if(FileName==null||"".equals(FileName)){ 265                     FileName = "temp"; 266                 } 267                 File = Filepath+"//"+FileName+".xls"; 268             } 269             File file = new File(File); 270             //如果找到相同的文件，执行删除 271             if(file.exists() && file.isFile()){ 272                 file.delete(); 273             } 274             WritableWorkbook wwb = Workbook.createWorkbook(new File(File)); 275             //创建工作表 276             wwb.createSheet("sheet1", 0); 277             //获取工作表  278             WritableSheet ws = wwb.getSheet(0); 279  280             //表头行样式 281             WritableCellFormat TableHead = new WritableCellFormat(); 282             TableHead.setBorder(Border.ALL, BorderLineStyle.THIN); 283             TableHead.setAlignment(Alignment.CENTRE); 284             TableHead.setBackground(Colour.GRAY_25); 285  286             //表体数据行样式 287             WritableCellFormat TableRow = new WritableCellFormat(); 288             TableRow.setAlignment(Alignment.CENTRE); 289  290             if(RowNums==null){ 291                 //生成表头 292                 for(int i=0;i<table.getColumnCount();i++){ 293                     if(table.getHeadRow(0).getCell(i).getValue()!=null){ 294                         ws.addCell(new Label(i,0,table.getHeadRow(0).getCell(i).getValue().toString(),TableHead)); 295                     } 296                 } 297                 //生成表体数据 298                 for(int i=0;i<table.getRowCount();i++){ 299                     for(int j=0;j<table.getColumnCount();j++){ 300                         if(table.getRow(i).getCell(j).getValue()!=null){ 301                             ws.addCell(new Label(j,i+1,table.getRow(i).getCell(j).getValue().toString(),TableRow)); 302                         } 303                     } 304                 } 305             }else{ 306                 //生成表头 307                 for(int i=0;i<table.getColumnCount();i++){ 308                     if(table.getHeadRow(0).getCell(i).getValue()!=null){ 309                         ws.addCell(new Label(i,0,table.getHeadRow(0).getCell(i).getValue().toString(),TableHead)); 310                     } 311                 } 312                 //生成表体数据 313                 for(int z=0;z<RowNums.length;z++){ 314                     int i = RowNums[z]; 315                     for(int j=0;j<table.getColumnCount();j++){ 316                         if(table.getRow(i).getCell(j).getValue()!=null){ 317                             ws.addCell(new Label(j,z+1,table.getRow(i).getCell(j).getValue().toString(),TableRow)); 318                         } 319                     } 320                 } 321             } 322             wwb.write();     323             wwb.close(); 324             returnvaleu = File; 325         } catch (Exception e) { 326             alert2("生成Excel文件出错",Filepath); 327         }  328         return returnvaleu; 329     } 330     /*** 331      * 设置表格列名 332      * @param Table Table名称 333      * @param Colunm 列名 334      * @param name 值 335      *  336      */ 337     public static void setTableColumnName(KDTable Table,String ColunmID,String ColunmName){ 338         KDTable kt = new KDTable(); 339         kt = Table; 340         kt.getHeadRow(0).getCell(ColunmID).setValue(ColunmName); 341         Table = kt; 342     } 343     /** 344      * 设置表格融合方式 345      * @param tblMain 表格 346      * @param type 融合方式 0 ：行融合 1：列融合 2：自由融合 347      */ 348     public static void setTableMergeMode(KDTable tblMain,int type){ 349         if(type==0){ 350             tblMain.getMergeManager().setMergeMode(KDTMergeManager.FREE_ROW_MERGE); 351         } 352         if(type==1){ 353             tblMain.getMergeManager().setMergeMode(KDTMergeManager.FREE_COLUMN_MERGE); 354         } 355         if(type==2){ 356             tblMain.getMergeManager().setMergeMode(KDTMergeManager.FREE_MERGE); 357         } 358     } 359     /*** 360      * 表格行选择器，从第R1行选到R2行 361      * @param tblMain 表格 362      * @param R1 开始行号 363      * @param R2 结束行号 364      */ 365     public static void setTableSelectRows(KDTable tblMain,int R1,int R2){ 366         tblMain.getSelectManager().select(R1-1, 0, R2-1, 0); 367     } 368  369  370     /** 371      * 设置按钮图片名称 372      * @param btnName 按钮名称 373      * @param imgName 图片名称 374      * EAS图片名称保存位置：BOS环境下   工程/lib/client/eas/eas_resource_common_ico.jar 375      */ 376     public static void setButtonImg(com.kingdee.bos.ctrl.swing.KDWorkButton ButtonName,String imgName){ 377         ButtonName.setIcon(EASResource.getIcon(imgName)); 378         ButtonName.setEnabled(true); 379     } 380     /** 381      * 设置F7 382      * @param F7Name F7名称 383      * @param ConditionSQL 过滤条件SQL(如果输入值不等于""，则添加条件) 384      *        例   " fbilltypestatr = '1' and (entrys.id is null or entrys.seq = 1)" 385      * @param Query 属性 例："com.kingdee.eas.cmt.basedata.app.OperatorOrgQuery" 386      * @param EditFrmat 属性 例："$number$" 387      * @param DisplayFormat 属性 例："$name$" 388      * @param CommitFormat 属性 例："$number$" 389      * @throws BOSException  390      */ 391     public static void setF7(KDBizPromptBox F7Name,String ConditionSQL ,String Query,String EditFrmat,String DisplayFormat,String CommitFormat){ 392         //添加分录过滤条件 393         try { 394             EntityViewInfo view = new EntityViewInfo(); 395             if(ConditionSQL != ""){ 396                 view.setFilter(ConditionSQL); 397             } 398             //设置F7属性 399             F7Name.setQueryInfo(Query);//关联Query 400             F7Name.setEditFormat(EditFrmat);//编辑样式 401             F7Name.setDisplayFormat(DisplayFormat);//展现样式 402             F7Name.setCommitFormat(CommitFormat);//提交样式 403             F7Name.setEntityViewInfo(view); 404             F7Name.setEnabledMultiSelection(false); 405         } catch (Exception e) { 406             alert2("F7["+F7Name+"]初始化出错，请联系管理员！",e.getMessage()); 407         } 408     } 409     /** 410      * 设置分录F7 411      * @param col 列名 kdtEntrys.getColumn("boxType") 412      * @param ConditionSQL 过滤条件SQL(如果输入值不等于""，则添加条件) 413      *        例   " fbilltypestatr = '1' and (entrys.id is null or entrys.seq = 1)" 414      * @param Query 属性 例："com.kingdee.eas.cmt.basedata.app.OperatorOrgQuery" 415      * @param EditFrmat 属性 例："$number$" 416      * @param DisplayFormat 属性 例："$name$" 417      * @param CommitFormat 属性 例："$number$" 418      */ 419     public static void setEntryF7(IColumn col,String ConditionSQL ,String Query,String EditFrmat,String DisplayFormat,String CommitFormat){ 420         try { 421             KDBizPromptBox prmt = new KDBizPromptBox(); 422             EntityViewInfo view = new EntityViewInfo(); 423             if(ConditionSQL != ""){ 424                 view.setFilter(ConditionSQL); 425             } 426             prmt.setQueryInfo(Query); 427             prmt.setEditFormat(EditFrmat); 428             prmt.setCommitFormat(CommitFormat); 429             prmt.setDisplayFormat(DisplayFormat); 430             prmt.setEntityViewInfo(view); 431             prmt.setEnabledMultiSelection(false); 432             KDTDefaultCellEditor editor = new KDTDefaultCellEditor(prmt); 433             col.setEditor(editor); 434         } catch (ParserException e) { 435             alert2("分录F7初始化出错，请联系管理员！",e.getMessage()); 436         } 437  438     } 439     /** 440      * 日期转换字符串 441      * @param date 日期 442      * @param type 显示格式 yyyy-MM-dd yyyy-MM-dd HH:mm:ss 443      * String returnvalue = UtilClass.DateToString(this.Startdatetest.getValue(), "yyyy-MM-dd"); 444      */ 445     public static String DateToString (Object date,String type){ 446         String returnvalue = ""; 447         if(date != null){ 448             try{ 449                 SimpleDateFormat sdf = new SimpleDateFormat(type); 450                 returnvalue = sdf.format(date); 451             } 452             catch(Exception ex){ 453                 alert("日期格式转换出错"); 454             } 455         } 456         return returnvalue; 457     } 458     /** 459      * 字符串转为日期 460      * @param DateStr 字符串 461      * @param type 类型 "yyyy-MM-dd HH:mm:ss" 462      * @return Date  java.util.Date 463      */ 464     public static Date StringToDate(String DateStr,String type){ 465         Date returnvalue = null; 466         try { 467             SimpleDateFormat sdf = new SimpleDateFormat(type); 468             returnvalue = sdf.parse(DateStr); 469         } catch (ParseException e) { 470             alert2("日期转换出错",DateStr+"-->"+type); 471         } 472         return returnvalue; 473     } 474     /** 475      * 设置DKDatePicker控件显示格式 476      * @param date 日期控制  477      * @param dateType 格式样式 例："yyyy-MM-dd HH:mm:ss"      "yyyy-MM-dd" 478      */ 479     public static void setKDDatePicker(KDDatePicker date,String dateType){ 480         date.setDatePattern(dateType); 481     } 482     /** 483      * 获取当前时间(KDDatePicker控件)默认值 例如：创建时间 修改时间 审核时间 484      * @return java.sql.Timestamp 当前时间 485      */ 486     public static java.sql.Timestamp getTime(){ 487         java.sql.Timestamp time = new java.sql.Timestamp(new java.util.Date().getTime()); 488         return time; 489     } 490  491     /** 492      * 打开文件 493      * @param FilePath 文件路径 494      */ 495     public static void OpenFile(String FilePath){ 496         try { 497             Runtime.getRuntime().exec("cmd /c start /"/" /""+FilePath.replaceAll("////", "////////")+"/""); 498         } catch (IOException e) { 499             alert2("打开文件出错",FilePath); 500         } 501     } 502     /** 503      * 打开文件选择器 504      * @return 文件路径 505      */ 506     public static String OpenFilesSelect(){ 507         String returnvalue = ""; 508         JFileChooser chooser = new JFileChooser(); 509         chooser.setDialogTitle("请选择文件"); 510         chooser.showDialog(null, "确定"); 511         if(chooser.getSelectedFile()!=null){ 512             File file = chooser.getSelectedFile(); 513             returnvalue = file.getPath(); 514         } 515         return returnvalue; 516     } 517  518     /** 519      * 打开目录选择器 520      * @return 521      */ 522     public static String OpenPathSelect(){ 523         String returnvalue = ""; 524         JFileChooser chooser = new JFileChooser(); 525         chooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY); 526         chooser.setDialogTitle("请选择目录"); 527         chooser.showDialog(null,"保存"); 528         if(chooser.getSelectedFile()!=null){ 529             File file = chooser.getSelectedFile(); 530             returnvalue = file.getPath(); 531         } 532         return returnvalue; 533     } 534     /** 535      * 向Excel文件插入数据 536      * @param File  537      * @param sheetNum 工作表序号 538      * @param y 行 539      * @param x 列 540      * @param value 内容 541      */ 542     public static void setExcelValue(String File,int sheetNum,int x,int y,String value){ 543         try { 544             File file = new File(File); 545             //如果找到相同的文件，执行删除 546             if(!file.exists() && !file.isFile()){ 547                 return; 548             } 549             //Excel获得文件  550             Workbook wb = Workbook.getWorkbook(new File(File));  551             //打开一个文件的副本，并且指定数据写回到原文件  552             WritableWorkbook book = Workbook.createWorkbook(new File(File),wb);  553             //获取工作表  554             WritableSheet sheet=book.getSheet(sheetNum); 555             sheet.addCell(new Label(y,x,value));  556             book.write();  557             book.close();  558         } catch (Exception e) { 559         }   560     } 561     /** 562      * 读取Excel文件 563      * @param File 文件名 564      * @param sheetNum 工作表序号 565      * @param y 行 566      * @param x 列 567      */ 568     public static String getExcelValue(String File,int sheetNum,int y,int x){ 569         String result = ""; 570         try { 571             File file = new File(File); 572             //如果找到相同的文件，执行删除 573             if(!file.exists() && !file.isFile()){ 574                 alert(File+"文件没找到！"); 575             } 576             Workbook book= Workbook.getWorkbook(new File(File));  577             //获得第一个工作表对象  578             Sheet sheet=book.getSheet(sheetNum);  579             //得到第一列第一行的单元格  580             Cell cell1=sheet.getCell(x,y);  581             result=cell1.getContents().toString();  582             book.close(); 583         } catch (FileNotFoundException e) { 584             alert2("读取Excel文件出错","请关闭当前打开的Excel文件"); 585         } catch (BiffException e) { 586             alert2("读取Excel文件出错",e.toString()); 587         } catch (IOException e) { 588             alert2("读取Excel文件出错",e.toString()); 589         } 590         return result; 591     } 592  593     /** 594      * 读取Excel文件(第一sheet页中的内容) 595      * @param File 文件路径 596      * @param sheetNum sheet页号 597      * @return 二维数组 598      *  599      */ 600     public static Object[][] getExcelValue(String File,int sheetNum){ 601         Object [][] returnvalue = null; 602         try { 603             Workbook book= Workbook.getWorkbook(new File(File));  604             Sheet sheet=book.getSheet(sheetNum); 605             returnvalue = new Object[sheet.getRows()][sheet.getColumns()]; 606             for(int i=1;i<sheet.getRows();i++){ 607                 for(int j=0;j<sheet.getColumns();j++){ 608                     returnvalue[i][j]=sheet.getCell(j,i).getContents(); 609                 } 610             } 611         } catch (FileNotFoundException e) { 612             alert2("读取Excel文件出错","请关闭当前打开的Excel文件"); 613         } catch (BiffException e) { 614             alert2("读取Excel文件出错",e.toString()); 615         } catch (IOException e) { 616             alert2("读取Excel文件出错",e.toString()); 617         } 618         return returnvalue; 619     } 620  621     /*** 622      * 发送即时消息 623      * @param FSuser 发送人ID 624      * @param JSuser 接收人ID 625      * @param MessageTitle 标题 626      * @param Messages 内容 627      */ 628     public static void addMessage(String FSuser,String JSuser,String MessageTitle,String Messages){ 629         try { 630             IBMCMessage i = BMCMessageFactory.getRemoteInstance(); 631             BMCMessageInfo info = new BMCMessageInfo();  632             info.setType(MsgType.ONLINE);// 消息类型，例如通知消息，任务消息，状态更新消息 633             info.setBizType(MsgBizType.ONLINE);// 业务类型，例如工作流，预警平台 634             info.setPriority(MsgPriority.HIGH); // 优先级 635             info.setStatus(MsgStatus.UNREADED); // 消息状态 636             info.setReceiver(JSuser); // 接收人ID （User的ID，不是Person的ID） 637             info.setSender(FSuser);// 消息发送人 638             info.setTitle(MessageTitle); // 消息标题 639             info.setBody(Messages);// 消息内容 640             i.submit(info); 641         } catch (Exception e) { 642             alert2("发送即时消息出错","标题："+MessageTitle+"  内容:"+Messages); 643         } 644     } 645  646  647  648     /** 649      * 列转行 650      * @param list 数据集合 651      * @param delimiter 分隔符 例："," 652      * @param bracketsLeft 左括号符号 653      * @param bracketsRight 右括号符号 654      * @return String 655      */ 656     public static String CellToRow(ArrayList list,String delimiter,String bracketsLeft,String bracketsRight){ 657         String returnvalue = ""; 658         for (Iterator iter = list.iterator(); iter.hasNext();){ 659             if(!"".equals(bracketsLeft) && bracketsLeft != null && !"".equals(bracketsRight) && bracketsRight != null){ 660                 returnvalue += bracketsLeft + (String)iter.next() + bracketsRight; 661             } 662             returnvalue+=delimiter; 663         } 664         return returnvalue.substring(0, returnvalue.length()-1); 665     } 666  667     /** 668      * 打开窗口 669      * @param URL UI地址 670      * @param ctx 参数集合 671      * @param openType 打开窗口类型 例:UIFactoryName.MODEL 672      * @param billStatus 单据状态  例：OprtState.ADDNEW 673      *  674      *   675         打开普通UI页面 676         HashMap cix = new HashMap(); 677         String orderid = "asiofjlqkjwfklaslkasdf=" 678         cix.put("orderid", orderid); 679         UtilClass.openUI("com.kingdee.eas.cmt.commission.client.GoodsUI", cix, UIFactoryName.MODEL, OprtState.ADDNEW); 680  681         打开单据EditUI页面 682          HashMap cix = new HashMap(); 683         cix.put("ID", fid); 684         UtilClass.openUI("com.kingdee.eas.cmt.commission.client.CmtTranConsignEditUI", cix, UIFactoryName.NEWWIN, OprtState.VIEW); 685  686  687         在打开的单据获取前面页面传来的参数 688         this.getUIContext().get("orderid").toString() 689      * 690      * 691      */ 692     public static void openUI(String URL,HashMap ctx,String openType,String billStatus){ 693         try { 694             IUIWindow ui = UIFactory.createUIFactory(openType).create(URL, ctx, null, billStatus); 695             ui.show(); 696         } catch (UIException e) { 697             alert2("弹出UI程序出错:",URL); 698         } 699     } 700  701     /** 702      * 发送参数 703      * @param ContextID 参数编号 704      * @param values 参数值 705      */ 706     public static void setSysContext(String ContextID,Object values){ 707         SysContext.getSysContext().setProperty(ContextID, values); 708     } 709  710     /** 711      * 获取参数 712      * @param ContextID 参数编号 713      * @return 参数值(Object) 714      */ 715     public static Object getSysContext(String ContextID){ 716         return SysContext.getSysContext().getProperty(ContextID); 717     } 718  719     /** 720      * 获取UI参数 721      * @param UI 722      * @return 723      */ 724     public static String getUIContext(CoreUIObject UI){ 725         String returnvalue = ""; 726         if(UI.getUIContext().get("UIClassParam")!=null){ 727             returnvalue = UI.getUIContext().get("UIClassParam").toString(); 728         } 729         return returnvalue; 730     } 731  732     /** 733      * 创建单据编号 734      * @param Parameter 规则参数 735      *        参数说明: 736      *               logo          编号头标示 737      *               date          日期时间 738      *               Delimiter  分隔符 739      *               digit         序号位数 740      *               isTissueIsolation   是否组织隔离(0:不隔离1:隔离) 741      *               table      表名 742      * @return String 单据编号 743      *  744      *  HashMap Parameter = new HashMap(); 745         //编号头标示符号 746         Parameter.put("logo", "CMT"); 747         //日期 748         Parameter.put("date", UtilClass.DateToString(UtilClass.getTime(), "yyyyMMdd")); 749         //分隔符 750         Parameter.put("Delimiter", "-"); 751         //序号位数 752         Parameter.put("digit", "5"); 753         //是否隔离组织0为不隔离1为隔离 754         Parameter.put("isTissueIsolation", "0"); 755         //单据表名 756         Parameter.put("table", "T_BAS_VehicleType"); 757         String billNum = UtilClass.createrBillNumber(Parameter); 758      *  759      */ 760     public static String createrBillNumber(HashMap Parameter){ 761         StringBuffer returnvalue = new StringBuffer(); 762         //编号头Logo 763         if(Parameter.get("logo")!=null){ 764             returnvalue.append(Parameter.get("logo")); 765         } 766         //添加时间 767         if(Parameter.get("date")!=null){ 768             if(Parameter.get("Delimiter")!=null){ 769                 returnvalue.append(Parameter.get("Delimiter")); 770             } 771             returnvalue.append(Parameter.get("date")); 772         } 773         //创建序号位(digit:序号位数) 774         if(Parameter.get("digit")!=null){ 775             StringBuffer getDigitSQL = new StringBuffer(); 776             Integer digit = new Integer(Parameter.get("digit").toString()); 777             StringBuffer digitValue = new StringBuffer(); 778             for(int i=0;i<digit.intValue();i++){ 779                 digitValue.append("0"); 780             } 781             getDigitSQL.append("select trim(to_char(count(*)+1,'"+digitValue+"')) from "+Parameter.get("table")+"  "); 782             //是否组织隔离 783             if("1".equals(Parameter.get("isTissueIsolation"))){ 784                 getDigitSQL.append(" where FControlUnitID = '"+getCU().getId()+"'"); 785             } 786             if(Parameter.get("Delimiter")!=null){ 787                 returnvalue.append(Parameter.get("Delimiter")); 788             } 789             //获取数据库记录数 790             returnvalue.append(executeQueryString(getDigitSQL.toString())); 791         } 792         return returnvalue.toString(); 793     } 794  795     /** 796      *  当前登录组织 797      * @return 798      */ 799     public static CtrlUnitInfo getCU(){ 800         return SysContext.getSysContext().getCurrentCtrlUnit(); 801     } 802  803     /** 804      * 判断当前组织是否为集团要目录 805      * @return boolean 806      */ 807     public static boolean isRootCU(){ 808         if("1".equals(executeQueryString("select tc.flevel from t_org_baseunit tc where tc.fid = '"+getCU().getId()+"'"))){ 809             return true; 810         }else{ 811             return false; 812         } 813     } 814  815     /** 816      * 获取集团组织ID 817      * @return 818      */ 819     public static CtrlUnitInfo getRootCU(){ 820         CtrlUnitInfo cinfo = new CtrlUnitInfo(); 821         try { 822             CtrlUnitCollection cinfos = CtrlUnitFactory.getRemoteInstance().getCtrlUnitCollection("select * where level = 1 "); 823             cinfo = cinfos.get(0); 824         } catch (BOSException e) { 825             alert2("获取集团组织ID出错",e.getMessage()); 826         } 827         return cinfo; 828     } 829     /** 830      * 当前登录用户 831      * @return 832      */ 833     public static UserInfo getUser(){ 834         return SysContext.getSysContext().getCurrentUserInfo(); 835     } 836     /** 837      * 当前登陆人员 838      * @return 839      */ 840     public static PersonInfo getPerson(){ 841         PersonInfo personinfo = null; 842         try { 843             PersonCollection Personcollection = PersonFactory.getRemoteInstance().getPersonCollection(" select * where name = '" + SysContext.getSysContext().getCurrentUserInfo().getName() +"'"); 844             personinfo=Personcollection.get(0); 845         } catch (Exception e1) { 846         } 847         return personinfo; 848     } 849     /** 850      * 当前登录人员部门 851      * @return 852      */ 853     public static AdminOrgUnitInfo getDepartment(){ 854         AdminOrgUnitInfo returnvalue = null; 855         try { 856             PersonPositionInfo PersonPosition = PersonPositionFactory.getRemoteInstance().getPersonPositionInfo("select primaryPosition.* where person = '" + getPerson().getId() + "'"); 857             PositionInfo Position = PersonPosition.getPrimaryPosition(); 858             AdminOrgUnitCollection collection = AdminOrgUnitFactory.getRemoteInstance().getAdminOrgUnitCollection(" select * where id= '" + Position.getAdminOrgUnit().getId() +"'"); 859             returnvalue = collection.get(0); 860         } catch (Exception e2) { 861         } 862         return returnvalue; 863     } 864  865     /** 866      * 通过fid获取表名 867      * @param fid 868      * @return 表名 869      */ 870     public static String getDBTableName(String fid){ 871         String Tablename = ""; 872         com.kingdee.bos.util.BOSObjectType bosType = BOSUuid.read(fid).getType(); 873         try { 874             Tablename = FMIsqlFacadeFactory.getRemoteInstance().getTableNameByBosType(bosType); 875         } catch (BOSException e) { 876             alert2("获取表名出错",fid); 877         } 878         return Tablename; 879     } 880  881     /** 882      * 执行SQL（查询，返回集合） 883      * @param sql 884      * @return  885       IRowSet rs = UtilClass.executeQuery(sql.toString()); 886       while(rs.next()){ 887          rs.getObject(1).toString(); 888       } 889      */ 890     public static IRowSet executeQuery(String sql){ 891         IRowSet returnvalue = null; 892         try { 893             IFMIsqlFacade db = com.kingdee.eas.fm.common.FMIsqlFacadeFactory.getRemoteInstance(); 894             returnvalue = db.executeQuery(" /*dialect*/ "+sql, null); 895         } catch (Exception e) { 896             alert2("执行SQL出错",sql); 897         } 898         return returnvalue; 899     } 900     /** 901      * 执行SQL(查询，返回单看值) 902      * @param sql 903      * @return String 904      */ 905     public static String executeQueryString(String sql){ 906         String returnvalue = null; 907         try { 908             IFMIsqlFacade db = com.kingdee.eas.fm.common.FMIsqlFacadeFactory.getRemoteInstance(); 909             IRowSet rs = db.executeQuery(" /*dialect*/ "+sql, null); 910             while(rs.next()){ 911                 if(rs.getObject(1)!=null){ 912                     returnvalue = rs.getObject(1).toString(); 913                 } 914             } 915         } catch (Exception e) { 916             alert2("执行SQL出错",sql); 917         } 918         return returnvalue; 919     } 920     /** 921      * 执行SQL(新增，修改) 922      * @param sql 923      */ 924     public static void executeSql(String sql){ 925         try { 926             IFMIsqlFacade db = com.kingdee.eas.fm.common.FMIsqlFacadeFactory.getRemoteInstance(); 927             db.executeSql(" /*dialect*/ "+sql); 928         } catch (Exception e) { 929             alert2("执行SQL出错",sql); 930         } 931     } 932  933     /** 934      * SQL数据导出到Excel文件 935      * @param sql 936      * @param FileName 文件名 937      */ 938     public static String SQLExpot(String sql,String FileName){ 939         String returnvalue =""; 940         try { 941             if("".equals(sql)||sql==null){ 942                 return  returnvalue; 943             } 944             if(FileName==null||"".equals(FileName)){ 945                 FileName = "temp"; 946             } 947             String Filepath = UtilClass.OpenPathSelect(); 948             String File = ""; 949             if("".equals(Filepath)){ 950                 return returnvalue; 951             }else{ 952                 File = Filepath+"//"+FileName+".xls"; 953             } 954             File file = new File(File); 955             //如果找到相同的文件，执行删除 956             if(file.exists() && file.isFile()){ 957                 file.delete(); 958             } 959             WritableWorkbook wwb = Workbook.createWorkbook(new File(File)); 960             //创建工作表 961             wwb.createSheet("sheet1", 0); 962             //获取工作表  963             WritableSheet ws = wwb.getSheet(0); 964  965             //表头行样式 966             WritableCellFormat TableHead = new WritableCellFormat(); 967             TableHead.setBorder(Border.ALL, BorderLineStyle.THIN); 968             TableHead.setAlignment(Alignment.CENTRE); 969             TableHead.setBackground(Colour.GRAY_25); 970  971             //表体数据行样式 972             WritableCellFormat TableRow = new WritableCellFormat(); 973             TableRow.setAlignment(Alignment.CENTRE); 974  975             IRowSet rs = UtilClass.executeQuery(sql); 976             //生成列名 977             for(int i=0;i<rs.getRowSetMetaData().getColumnCount();i++){ 978                 String columnName = rs.getRowSetMetaData().getColumnName(i+1); 979                 ws.addCell(new Label(i,0,columnName,TableHead)); 980             } 981             int z=1; 982             while(rs.next()){ 983                 for(int j=1;j<=rs.getRowSetMetaData().getColumnCount();j++){ 984                     if(rs.getObject(j)!=null){ 985                         ws.addCell(new Label(j-1,z,rs.getObject(j).toString(),TableRow)); 986                     } 987                 } 988                 z++; 989             } 990             wwb.write();     991             wwb.close(); 992         } catch (Exception e) { 993             alert2("导出数据生成Excel文件出错",sql); 994         } 995         return returnvalue; 996     } 997  998  999     /**1000      * 集装箱箱号正确性验证1001      * @param BoxNum 箱号1002      * @return Boolean1003        if(!UtilClass.BoxVerification(boxnum)){1004             if(!UtilClass.alertReturn("箱号："+boxnum+" 不是国际标准箱号，是否保存？")){1005                 UtilClass.Stop();1006             }1007         }1008      */1009     public static boolean BoxVerification(String BoxNum){1010         HashMap bj = new HashMap();1011         bj.put("0", 0);1012         bj.put("1", 1);1013         bj.put("2", 2);1014         bj.put("3", 3);1015         bj.put("4", 4);1016         bj.put("5", 5);1017         bj.put("6", 6);1018         bj.put("7", 7);1019         bj.put("8", 8);1020         bj.put("9", 9);1021         bj.put("A", 10);1022         bj.put("B", 12);1023         bj.put("C", 13);1024         bj.put("D", 14);1025         bj.put("E", 15);1026         bj.put("F", 16);1027         bj.put("G", 17);1028         bj.put("H", 18);1029         bj.put("I", 19);1030         bj.put("J", 20);1031         bj.put("K", 21);1032         bj.put("L", 23);1033         bj.put("M", 24);1034         bj.put("N", 25);1035         bj.put("O", 26);1036         bj.put("P", 27);1037         bj.put("Q", 28);1038         bj.put("R", 29);1039         bj.put("S", 30);1040         bj.put("T", 31);1041         bj.put("U", 32);1042         bj.put("V", 34);1043         bj.put("W", 35);1044         bj.put("X", 36);1045         bj.put("Y", 37);1046         bj.put("Z", 38);1047 1048         //去掉箱号两边空格1049         String newBoxNum = BoxNum.trim();1050 1051         //判断箱号是否为国际标准11位1052         if(newBoxNum.length() != 11){1053             return false;1054         }1055 1056         //判断前四位为字母  區別小寫1057         for(int i=0;i<4;i++){1058             String Nums = newBoxNum.substring(i, i+1);1059             char chs[] = Nums.toCharArray();1060             if((int)chs[0]<65 || (int)chs[0]>90){1061                 return false;1062             }1063         }1064 1065         //判断后7位为数字1066         for(int i=4;i<11;i++){1067             String Nums = newBoxNum.substring(i, i+1);1068             char chs[] = Nums.toCharArray();1069             if((int)chs[0]<48 || (int)chs[0]>57){1070                 return false;1071             }1072         }1073 1074         //判断第11数验证码是否正确1075         double VerificationNumSum = 0;1076         for(int i=0;i<10;i++){1077             //获取当前位字母或数字对应的数值1078             double bjdata = ((Integer)bj.get(newBoxNum.substring(i, i+1))).doubleValue();1079             //获取当前位对应的2的i次方1080             double jfdata = Math.pow(2,i);1081             VerificationNumSum+=bjdata*jfdata;1082         }1083         int VerificationNum =(int)VerificationNumSum%11;1084         int sub11 = new Integer(newBoxNum.substring(10)).intValue();1085         //如果计算结果与第11位数是否相等1086         if(VerificationNum!=sub11){1087             return false;1088         }1089         return true;1090     }1091 1092     /**1093      * 从Oracle数据读取图片字节码生成图片文件1094      * @param img_num 图片编号(参数表编号)1095      * @param file 文件地址 "c://bmw.png"1096 1097             系统参数表1098             -- 创建表1099             create table T_SYS_FUTVAN1100             (1101               FNUMBER             NVARCHAR2(55),1102               FNAME               NVARCHAR2(256),1103               PARAMETER_STRING    NVARCHAR2(256),1104               PARAMETER_NUMBER    NUMBER,1105               PARAMETER_DATE      DATE,1106               PARAMETER_TIMESTAMP TIMESTAMP(6),1107               PARAMETER_BLOB      BLOB1108             )1109             -- 创建字段说明 1110             comment on column T_SYS_FUTVAN.FNUMBER1111               is '参数编号';1112             comment on column T_SYS_FUTVAN.FNAME1113               is '参数名称';1114             comment on column T_SYS_FUTVAN.PARAMETER_STRING1115               is '文本型参数';1116             comment on column T_SYS_FUTVAN.PARAMETER_NUMBER1117               is '数据型参数';1118             comment on column T_SYS_FUTVAN.PARAMETER_DATE1119               is '日期型参数';1120             comment on column T_SYS_FUTVAN.PARAMETER_TIMESTAMP1121               is '时间型参数';1122             comment on column T_SYS_FUTVAN.PARAMETER_BLOB1123               is '字节型参数';1124      * 1125      */1126     public static void getImgFromOracle(String img_num,String file){1127         try {1128             IRowSet rs = executeQuery("select parameter_blob from t_sys_futvan where fnumber = '"+img_num+"'"); 1129             while(rs.next()){1130                 java.sql.Blob blob = rs.getBlob(1);1131                 InputStream ins = blob.getBinaryStream();1132                 File f = new File(file);1133                 FileOutputStream fout = new FileOutputStream(f);1134                 byte[]b = new byte[10244];1135                 int len = 0;1136                 while((len = ins.read(b)) != -1){1137                     fout.write(b,0,len);1138                 }1139                 fout.close();1140                 ins.close();1141             }1142         } catch (Exception e) {1143             System.out.println();1144         }1145     }1146 1147     /**1148      * BOTP单据转换1149      * @param botpNum 转换规则编号1150      * @param BillInfo 原单据1151      */1152     public static void BOTP(String botpNum,CoreBillBaseInfo BillInfo){1153         String error = "";1154         try {1155             // 取得BOPT的映射1156             BOTMappingCollection botmapping = BOTMappingFactory.getRemoteInstance().getBOTMappingCollection("select * where    name = '"+botpNum+"'  ");1157             BOTMappingInfo btpMappingInfo = null;1158             if (botmapping !=null && botmapping.size() == 1) {1159                 btpMappingInfo = botmapping.get(0);1160             } else {1161                 if(botmapping==null || botmapping.size()<1){1162                     error = "未找到转换规则  规则编号:"+botpNum;1163                 }1164                 if(botmapping.size()>1){1165                     error = "找到多条转换规则，请删除重复规则。   规则编号:"+botpNum;1166                 }1167                 throw new Exception();1168             }1169             //执行单据转换1170             BTPTransformResult transformResult = BTPManagerFactory.getRemoteInstance().transform(BillInfo, btpMappingInfo);1171 1172             //取得目标单据列表1173             IObjectCollection toBillList = transformResult.getBills();1174             1175             //保存目标单据1176             for (int i = 0; i < toBillList.size(); i++) {   1177                 CoreBillBaseInfo destBillInfo = (CoreBillBaseInfo) toBillList.getObject(i);1178                 BTPManagerFactory.getRemoteInstance().saveRelations(destBillInfo, transformResult.getBOTRelationCollection());1179             }1180             1181         } catch (Exception e) {1182             if("".equals(error) || error == null){1183                 alert2("单据转换出错",e.getMessage());1184             }else{1185                 alert2("单据转换出错",error);1186             }1187         }1188     }1189     1190     /**1191      * 分摊功能1192      * @param Total 总数1193      * @param Allocations 分摊明细1194      * @param newScale 小数位数1195      * @return 分摊分集合1196         String [][]Allocations = new String[3][2];1197         Allocations[0][0] = "001";1198         Allocations[0][1] = "3";1199         Allocations[1][0] = "002";1200         Allocations[1][1] = "3";1201         Allocations[2][0] = "003";1202         Allocations[2][1] = "3";1203         String [][] rv = UtilClass.mathAllocation(new BigDecimal("100"), Allocations,2);1204      */1205     public static String[][] mathAllocation(BigDecimal Total,String[][] Allocations,int newScale){1206         String[][] returnvalue = new String[Allocations.length][2];1207         BigDecimal sum = new BigDecimal("0");1208         BigDecimal Allocationsum = new BigDecimal("0");1209         try {1210             //获取明细总数1211             for(int i=0;i<Allocations.length;i++){1212                 if(Allocations[i][1]!=null&&!"".equals(Allocations[i][1])){1213                     sum = sum.add(new BigDecimal(Allocations[i][1]));1214                 }1215             }1216             1217             //按比例分摊1218             for(int i=0;i<Allocations.length;i++){1219                 if(Allocations[i][1]!=null&&!"".equals(Allocations[i][1])){1220                     BigDecimal thisValue = new BigDecimal(Allocations[i][1]);1221                     BigDecimal AllocationValue = thisValue.divide(sum,16, BigDecimal.ROUND_HALF_UP).multiply(Total).setScale(newScale, BigDecimal.ROUND_HALF_UP);1222                     returnvalue[i][0] = Allocations[i][0];1223                     returnvalue[i][1] = AllocationValue.toString();1224                 }else{1225                     returnvalue[i][0] = Allocations[i][0];1226                     returnvalue[i][1] = "0";1227                 }1228             }1229             1230             //判断分摊后每条记录数据是否与总数相同1231             for(int i=0;i<returnvalue.length;i++){1232                 if(returnvalue[i][1]!=null&&!"".equals(returnvalue[i][1])){1233                     Allocationsum = Allocationsum.add(new BigDecimal(returnvalue[i][1]));1234                 }1235             }1236             1237             //如果分摊后结果与分摊前总数不一等。在分摊记录最后一条。加上。分摊后总数与分摊总数的差值1238             if(Allocationsum.compareTo(Total)!=0){1239                 BigDecimal xz = new BigDecimal(returnvalue[returnvalue.length-1][1]).subtract(Allocationsum.subtract(Total));1240                 returnvalue[returnvalue.length-1][1] = xz.toString();1241             }1242         } catch (Exception e) {1243             StringBuffer er = new StringBuffer();1244             for(int i=0;i<Allocations.length;i++){1245                 er.append("| "+Allocations[i][0]+"   "+Allocations[i][1]+" |");1246             }1247             alert2("分摊出错","分摊总数:"+Total.toString()+"  明细:"+er.toString());1248         }1249         return returnvalue;1250     }1251     1252 }1253 1254 /*1255 1256 --BOTP单据查询1257 select tbotpm.fname               转换规则编号,1258        tbotpm.fsrcentityalias_l2  源单据名称,1259        tbotpm.FSrcEntityFullName  源单据对象,1260        tbotpd.FSrcObjectID 源单据fid,1261        tbotpm.fdestentityalias_l2 目录单据名称,1262        tbotpm.FDestEntityFullName 目标单据对象,1263        tbotpd.FDestObjectID 目标单据fid1264   from T_BOT_Relation tbotpc1265  inner join t_bot_mapping tbotpm1266     on tbotpc.FBOTMappingID = tbotpm.fid1267  inner join t_bot_relationentry tbotpd1268     on tbotpc.fid = tbotpd.FKeyID1269            1270            1271 1272 --凭证查询1273 SELECT1274 DISTINCT VOUCHER.Fid  主表FID,1275  VOUCHER.Fnumber 凭证号,1276  TO_CHAR(VOUCHER.FBookedDate, 'YYYY-MM-DD') 记账日期,1277  ENTRIES.Fid 分录fid1278  1279   FROM T_GL_Voucher VOUCHER1280 1281   LEFT OUTER JOIN T_PM_User CASHIER1282     ON VOUCHER.FCashierID = CASHIER.FID1283 1284   LEFT OUTER JOIN T_BD_VoucherTypes  VOUCHERTYPE1285     ON VOUCHER.FVoucherTypeID = VOUCHERTYPE.FID1286 1287   LEFT OUTER JOIN T_BD_Period  PERIOD1288     ON VOUCHER.FPeriodID = PERIOD.FID1289 1290   LEFT OUTER JOIN T_PM_User  AUDITOR1291     ON VOUCHER.FAuditorID = AUDITOR.FID1292 1293   LEFT OUTER JOIN T_PM_User  HANDLER1294     ON VOUCHER.FHandlerID = HANDLER.FID1295 1296   LEFT OUTER JOIN T_PM_User  CREATOR1297     ON VOUCHER.FCreatorID = CREATOR.FID1298 1299   LEFT OUTER JOIN T_PM_User  POSTER1300     ON VOUCHER.FPosterID = POSTER.FID1301 1302   LEFT OUTER JOIN T_PM_User  CANCELLER1303     ON VOUCHER.FCancellerID = CANCELLER.FID1304 1305  INNER JOIN T_GL_VoucherEntry  ENTRIES1306     ON VOUCHER.FID = ENTRIES.FBillID1307 1308   LEFT OUTER JOIN T_ORG_BaseUnit  COMPANY1309     ON VOUCHER.FCompanyID = COMPANY.FID1310 1311  left JOIN (SELECT1312              1313              DISTINCT VOUCHER.FID  ID,1314                       ASSISTRECORDS.FDescription FDescriptions,1315                       ASSGRP.fnumbergroup_l2 fnumbergroup_l2s,1316                       ASSISTRECORDS.FSettlementCode FSettlementCodes,1317                       ASSISTRECORDS.Fid assFid1318                       VOUCHER.FIsCheck  ISCHECK,1319                       VOUCHER.FCompanyID  COMPANY.ID,1320                       COMPANY.FName_l2  COMPANY.NAME,1321                       PERIOD.FNumber  PERIOD.NUMBER,1322                       ((TO_CHAR(PERIOD.FPeriodYear) || '.') ||1323                       TO_CHAR(PERIOD.FPeriodNumber))  PERIOD,1324                       VOUCHERTYPE.FName_l2  VOUCHERTYPE.NAME,1325                       VOUCHERTYPE.FNumber  VOUCHERTYPE.NUMBER,1326                       VOUCHER.FNumber  NUMBER,1327                       VOUCHER.FBookedDate  BOOKEDDATE,1328                       VOUCHER.FBizDate  BIZDATE,1329                       VOUCHER.fsourceSys  SOURCESYS,1330                       VOUCHER.FSourceType  SOURCETYPE,1331                       CASHIER.FName_l2  CASHIER.NAME,1332                       AUDITOR.FName_l2  AUDITOR.NAME,1333                       CREATOR.FName_l2  CREATOR.NAME,1334                       POSTER.FName_l2  POSTER.NAME,1335                       VOUCHER.FBizStatus  BIZSTATUS,1336                       VOUCHER.FEntryCount  ENTRYCOUNT,1337                       ENTRIES.FID  ENTRIES.ID,1338                       ENTRIES.FSeq  ENTRIES.SEQ,1339                       ENTRIES.FDescription  ENTRIES.DESCRIPTION,1340                       ACCOUNT.FNumber  ACCOUNT.NUMBER,1341                       ACCOUNT.FLongName_l2  ACCOUNT.LONGNAME,1342                       CURRENCY.FName_l2  CURRENCY.NAME,1343                       CURRENCY.FPrecision  CURRENCY.PRECISION,1344                       ENTRIES.FOriginalAmount  ENTRIES.ORIGINALAMOUNT,1345                       CASE ENTRIES.FEntryDC1346                         WHEN 1 THEN1347                          ENTRIES.FLocalAmount1348                         ELSE1349                          NULL1350                       END AS LOCALAMOUNTDEBIT,1351                       CASE ENTRIES.FEntryDC1352                         WHEN 1 THEN1353                          NULL1354                         ELSE1355                          ENTRIES.FLocalAmount1356                       END AS LOCALAMOUNTCREDIT,1357                       CASE ENTRIES.FEntryDC1358                         WHEN 1 THEN1359                          ENTRIES.FReportingAmount1360                         ELSE1361                          NULL1362                       END AS REPORTINGAMOUNTDEBIT,1363                       CASE ENTRIES.FEntryDC1364                         WHEN 1 THEN1365                          NULL1366                         ELSE1367                          ENTRIES.FReportingAmount1368                       END AS REPORTINGAMOUNTCREDIT,1369                       VOUCHER.FHasCashAccount AS HASCASHACCOUNT,1370                       VOUCHER.FDescription AS DESCRIPTION,1371                       ENTRIES.FIsCussent AS ENTRIES.CUSSENT,1372                       ASSGRP.FLongNameGroup_l2 AS ASSGRP.LONGNAMEGROUP,1373                       VOUCHER.FAttachments AS ATTACHMENTS,1374                       VOUCHER.FCreateTime AS CREATETIME,1375                       ASSISTRECORDS.FDescription AS ASSISTRECORDS.DESCRIPTION,1376                       ASSISTRECORDS.FOriginalAmount AS ASSISTRECORDS.ORIGINALAMOUNT,1377                       ASSISTRECORDS.FLocalAmount AS ASSISTRECORDS.LOCALAMOUNT,1378                       ASSISTRECORDS.FReportingAmount AS ASSISTRECORDS.REPORTINGAMOUNT,1379                       ASSISTRECORDS.FQuantity AS ASSISTRECORDS.QUANTITY,1380                       ENTRIES.FQuantity AS ENTRIES.QUANTITY,1381                       VOUCHER.FIsCussent AS ISCUSSENT,1382                       VOUCHER.FIsMgCtrl AS ISMGCTRL,1383                       ASSISTRECORDS.FSettlementCode AS ASSISTRECORDS.SETTLEMENTCODE,1384                       ASSISTRECORDS.FBizNumber AS ASSISTRECORDS.BIZNUMBER,1385                       ASSISTRECORDS.FInvoiceNumber AS ASSISTRECORDS.INVOICENUMBER,1386                       ASSISTRECORDS.FTicketNumber AS ASSISTRECORDS.TICKETNUMBER,1387                       CUSTOMER.FNumber AS CUSTOMER.NUMBER,1388                       SETTLEMENTTYPE.FNumber AS SETTLEMENTTYPE.NUMBER,1389                       MATERIAL.FNumber AS MATERIAL.NUMBER,1390                       ORGUNIT.FNumber AS ORGUNIT.NUMBER,1391                       SUPPLIER.FNumber AS SUPPLIER.NUMBER,1392                       ASSISTRECORDS.FHasEffected AS ASSISTRECORDS.HASEFFECTED,1393                       ASSISTRECORDS.FFeeType AS ASSISTRECORDS.FEETYPE1394              1395                FROM T_GL_Voucher  VOUCHER1396              1397                LEFT OUTER JOIN T_PM_User  CASHIER1398                  ON VOUCHER.FCashierID = CASHIER.FID1399              1400                LEFT OUTER JOIN T_BD_VoucherTypes  VOUCHERTYPE1401                  ON VOUCHER.FVoucherTypeID = VOUCHERTYPE.FID1402              1403                LEFT OUTER JOIN T_BD_Period  PERIOD1404                  ON VOUCHER.FPeriodID = PERIOD.FID1405              1406                LEFT OUTER JOIN T_PM_User  AUDITOR1407                  ON VOUCHER.FAuditorID = AUDITOR.FID1408              1409                LEFT OUTER JOIN T_PM_User  HANDLER1410                  ON VOUCHER.FHandlerID = HANDLER.FID1411              1412                LEFT OUTER JOIN T_PM_User  CREATOR1413                  ON VOUCHER.FCreatorID = CREATOR.FID1414              1415                LEFT OUTER JOIN T_PM_User  POSTER1416                  ON VOUCHER.FPosterID = POSTER.FID1417              1418                LEFT OUTER JOIN T_PM_User  CANCELLER1419                  ON VOUCHER.FCancellerID = CANCELLER.FID1420              1421               INNER JOIN T_GL_VoucherEntry  ENTRIES1422                  ON VOUCHER.FID = ENTRIES.FBillID1423              1424                LEFT OUTER JOIN T_ORG_BaseUnit  COMPANY1425                  ON VOUCHER.FCompanyID = COMPANY.FID1426              1427                LEFT OUTER JOIN T_BD_Currency  CURRENCY1428                  ON ENTRIES.FCurrencyID = CURRENCY.FID1429              1430                LEFT OUTER JOIN T_BD_AccountView  ACCOUNT1431                  ON ENTRIES.FAccountID = ACCOUNT.FID1432              1433                LEFT OUTER JOIN T_GL_VoucherAssistRecord  ASSISTRECORDS1434                  ON ENTRIES.FID = ASSISTRECORDS.FEntryID1435              1436                LEFT OUTER JOIN T_BD_AssistantHG  ASSGRP1437                  ON ASSISTRECORDS.FAssGrpID = ASSGRP.FID1438              1439                LEFT OUTER JOIN T_BD_Customer  CUSTOMER1440                  ON ASSISTRECORDS.FCustomerID = CUSTOMER.FID1441              1442                LEFT OUTER JOIN T_BD_SettlementType  SETTLEMENTTYPE1443                  ON ASSISTRECORDS.FSettlementTypeID = SETTLEMENTTYPE.FID1444              1445                LEFT OUTER JOIN T_BD_Material  MATERIAL1446                  ON ASSISTRECORDS.FMaterialID = MATERIAL.FID1447              1448                LEFT OUTER JOIN T_ORG_Company  ORGUNIT1449                  ON ASSISTRECORDS.FOrgUnitID = ORGUNIT.FID1450              1451                LEFT OUTER JOIN T_BD_Supplier  SUPPLIER1452                  ON ASSISTRECORDS.FSupplierID = SUPPLIER.FID1453              1454                LEFT OUTER JOIN T_PM_User  HANDLER11455                  ON ASSISTRECORDS.FHandlerID = HANDLER1.FID)  VCHIDQUERY1456     ON VOUCHER.FID = VCHIDQUERY.ID1457 1458   LEFT OUTER JOIN T_BD_Currency  CURRENCY1459     ON ENTRIES.FCurrencyID = CURRENCY.FID1460 1461   LEFT OUTER JOIN T_BD_AccountView  ACCOUNT1462     ON ENTRIES.FAccountID = ACCOUNT.FID1463  where COMPANY.Fname_L2 = '中床国际物流集团有限公司'  1464  and TO_DATE(TO_CHAR(VOUCHER.FBookedDate, 'YYYY-MM-DD'), 'YYYY-MM-DD') >=1465                            TO_DATE('2009-01-01', 'YYYY-MM-DD')1466  AND TO_DATE(TO_CHAR(VOUCHER.FBookedDate, 'YYYY-MM-DD'), 'YYYY-MM-DD') <=1467                            TO_DATE('2009-12-31', 'YYYY-MM-DD')1468                            1469 1470 */