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 */