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环境下 工程libclienteaseas_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 Boolean 1003 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_FUTVAN 1100 ( 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 BLOB 1108 ) 1109 -- 创建字段说明 1110 comment on column T_SYS_FUTVAN.FNUMBER 1111 is '参数编号'; 1112 comment on column T_SYS_FUTVAN.FNAME 1113 is '参数名称'; 1114 comment on column T_SYS_FUTVAN.PARAMETER_STRING 1115 is '文本型参数'; 1116 comment on column T_SYS_FUTVAN.PARAMETER_NUMBER 1117 is '数据型参数'; 1118 comment on column T_SYS_FUTVAN.PARAMETER_DATE 1119 is '日期型参数'; 1120 comment on column T_SYS_FUTVAN.PARAMETER_TIMESTAMP 1121 is '时间型参数'; 1122 comment on column T_SYS_FUTVAN.PARAMETER_BLOB 1123 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 目标单据fid 1264 from T_BOT_Relation tbotpc 1265 inner join t_bot_mapping tbotpm 1266 on tbotpc.FBOTMappingID = tbotpm.fid 1267 inner join t_bot_relationentry tbotpd 1268 on tbotpc.fid = tbotpd.FKeyID 1269 1270 1271 1272 --凭证查询 1273 SELECT 1274 DISTINCT VOUCHER.Fid 主表FID, 1275 VOUCHER.Fnumber 凭证号, 1276 TO_CHAR(VOUCHER.FBookedDate, 'YYYY-MM-DD') 记账日期, 1277 ENTRIES.Fid 分录fid 1278 1279 FROM T_GL_Voucher VOUCHER 1280 1281 LEFT OUTER JOIN T_PM_User CASHIER 1282 ON VOUCHER.FCashierID = CASHIER.FID 1283 1284 LEFT OUTER JOIN T_BD_VoucherTypes VOUCHERTYPE 1285 ON VOUCHER.FVoucherTypeID = VOUCHERTYPE.FID 1286 1287 LEFT OUTER JOIN T_BD_Period PERIOD 1288 ON VOUCHER.FPeriodID = PERIOD.FID 1289 1290 LEFT OUTER JOIN T_PM_User AUDITOR 1291 ON VOUCHER.FAuditorID = AUDITOR.FID 1292 1293 LEFT OUTER JOIN T_PM_User HANDLER 1294 ON VOUCHER.FHandlerID = HANDLER.FID 1295 1296 LEFT OUTER JOIN T_PM_User CREATOR 1297 ON VOUCHER.FCreatorID = CREATOR.FID 1298 1299 LEFT OUTER JOIN T_PM_User POSTER 1300 ON VOUCHER.FPosterID = POSTER.FID 1301 1302 LEFT OUTER JOIN T_PM_User CANCELLER 1303 ON VOUCHER.FCancellerID = CANCELLER.FID 1304 1305 INNER JOIN T_GL_VoucherEntry ENTRIES 1306 ON VOUCHER.FID = ENTRIES.FBillID 1307 1308 LEFT OUTER JOIN T_ORG_BaseUnit COMPANY 1309 ON VOUCHER.FCompanyID = COMPANY.FID 1310 1311 left JOIN (SELECT 1312 1313 DISTINCT VOUCHER.FID ID, 1314 ASSISTRECORDS.FDescription FDescriptions, 1315 ASSGRP.fnumbergroup_l2 fnumbergroup_l2s, 1316 ASSISTRECORDS.FSettlementCode FSettlementCodes, 1317 ASSISTRECORDS.Fid assFid 1318 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.FEntryDC 1346 WHEN 1 THEN 1347 ENTRIES.FLocalAmount 1348 ELSE 1349 NULL 1350 END AS LOCALAMOUNTDEBIT, 1351 CASE ENTRIES.FEntryDC 1352 WHEN 1 THEN 1353 NULL 1354 ELSE 1355 ENTRIES.FLocalAmount 1356 END AS LOCALAMOUNTCREDIT, 1357 CASE ENTRIES.FEntryDC 1358 WHEN 1 THEN 1359 ENTRIES.FReportingAmount 1360 ELSE 1361 NULL 1362 END AS REPORTINGAMOUNTDEBIT, 1363 CASE ENTRIES.FEntryDC 1364 WHEN 1 THEN 1365 NULL 1366 ELSE 1367 ENTRIES.FReportingAmount 1368 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.FEETYPE 1394 1395 FROM T_GL_Voucher VOUCHER 1396 1397 LEFT OUTER JOIN T_PM_User CASHIER 1398 ON VOUCHER.FCashierID = CASHIER.FID 1399 1400 LEFT OUTER JOIN T_BD_VoucherTypes VOUCHERTYPE 1401 ON VOUCHER.FVoucherTypeID = VOUCHERTYPE.FID 1402 1403 LEFT OUTER JOIN T_BD_Period PERIOD 1404 ON VOUCHER.FPeriodID = PERIOD.FID 1405 1406 LEFT OUTER JOIN T_PM_User AUDITOR 1407 ON VOUCHER.FAuditorID = AUDITOR.FID 1408 1409 LEFT OUTER JOIN T_PM_User HANDLER 1410 ON VOUCHER.FHandlerID = HANDLER.FID 1411 1412 LEFT OUTER JOIN T_PM_User CREATOR 1413 ON VOUCHER.FCreatorID = CREATOR.FID 1414 1415 LEFT OUTER JOIN T_PM_User POSTER 1416 ON VOUCHER.FPosterID = POSTER.FID 1417 1418 LEFT OUTER JOIN T_PM_User CANCELLER 1419 ON VOUCHER.FCancellerID = CANCELLER.FID 1420 1421 INNER JOIN T_GL_VoucherEntry ENTRIES 1422 ON VOUCHER.FID = ENTRIES.FBillID 1423 1424 LEFT OUTER JOIN T_ORG_BaseUnit COMPANY 1425 ON VOUCHER.FCompanyID = COMPANY.FID 1426 1427 LEFT OUTER JOIN T_BD_Currency CURRENCY 1428 ON ENTRIES.FCurrencyID = CURRENCY.FID 1429 1430 LEFT OUTER JOIN T_BD_AccountView ACCOUNT 1431 ON ENTRIES.FAccountID = ACCOUNT.FID 1432 1433 LEFT OUTER JOIN T_GL_VoucherAssistRecord ASSISTRECORDS 1434 ON ENTRIES.FID = ASSISTRECORDS.FEntryID 1435 1436 LEFT OUTER JOIN T_BD_AssistantHG ASSGRP 1437 ON ASSISTRECORDS.FAssGrpID = ASSGRP.FID 1438 1439 LEFT OUTER JOIN T_BD_Customer CUSTOMER 1440 ON ASSISTRECORDS.FCustomerID = CUSTOMER.FID 1441 1442 LEFT OUTER JOIN T_BD_SettlementType SETTLEMENTTYPE 1443 ON ASSISTRECORDS.FSettlementTypeID = SETTLEMENTTYPE.FID 1444 1445 LEFT OUTER JOIN T_BD_Material MATERIAL 1446 ON ASSISTRECORDS.FMaterialID = MATERIAL.FID 1447 1448 LEFT OUTER JOIN T_ORG_Company ORGUNIT 1449 ON ASSISTRECORDS.FOrgUnitID = ORGUNIT.FID 1450 1451 LEFT OUTER JOIN T_BD_Supplier SUPPLIER 1452 ON ASSISTRECORDS.FSupplierID = SUPPLIER.FID 1453 1454 LEFT OUTER JOIN T_PM_User HANDLER1 1455 ON ASSISTRECORDS.FHandlerID = HANDLER1.FID) VCHIDQUERY 1456 ON VOUCHER.FID = VCHIDQUERY.ID 1457 1458 LEFT OUTER JOIN T_BD_Currency CURRENCY 1459 ON ENTRIES.FCurrencyID = CURRENCY.FID 1460 1461 LEFT OUTER JOIN T_BD_AccountView ACCOUNT 1462 ON ENTRIES.FAccountID = ACCOUNT.FID 1463 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 */