创建临时表进行性能优化
public void updatePurAndSaleOrderByARAP(Context ctx, IObjectPK pk,boolean isAudit) throws EASBizException, BOSException {//boolean isAR = com.kingdee.eas.fi.ar.OtherBillFactory.getLocalInstance(ctx).exists(pk);//if (isAR) {String tempTableDefine[][] = {{ "APentryID", "VARCHAR(44)" },{ "TOBentryID", "VARCHAR(44)" }};Connection connection = CommonServerUtils.getConnection(ctx);SysTempTableHelper tem = new SysTempTableHelper(ctx, connection);String tempTable = tem.createTempTable(ctx, tempTableDefine);if(tempTable==null || tempTable.trim().length()==0){System.out.println("临时表创建失败");return;}StringBuffer sql = new StringBuffer();sql.append("INSERT INTO "+tempTable+"\n");sql.append("select \n");sql.append("\n");sql.append("APentry.FID APentryID,\n"); // 应付单分录IDsql.append("TOBentry.FID TOBentryID\n"); // 采购订单分录IDsql.append("\n");sql.append("from T_AP_OtherBillentry APentry \n"); // 应付单分录sql.append("\n");sql.append("inner join T_IM_PurInWarehsEntry PIWentry \n"); // 应付单源单采购入库单分录sql.append("on PIWentry.FID = APentry.FSourceBillEntryId\n");sql.append("\n");sql.append("inner join T_IM_TransferOrderBillEntry TOBentry\n"); // 采购入库单源单调拨订单分录sql.append("on TOBentry.FID = PIWentry.FSourceBillEntryId\n");sql.append("\n");sql.append("inner join T_IM_TransferOrderBill TOB\n");sql.append("on TOB.FID = TOBentry.FParentID\n");sql.append("\n");sql.append("inner join T_SCM_BizType BType\n");sql.append("on BType.FID = TOB.FBizTypeID\n");sql.append("\n");sql.append("where APentry.FParentID='"+pk+"'\n");sql.append("and (BType.FNumber = '370' or BType.FNumber = '310')\n");tem.execute(sql.toString());StringBuffer sqla = new StringBuffer();sqla.append("select \n");sqla.append("\n");sqla.append("temp.APentryID APentryID,\n");sqla.append("SOentry.FID SPOentryID,\n");sqla.append("'C48A423A' entryID\n");sqla.append("\n");sqla.append("from "+tempTable+" temp\n");sqla.append("\n");sqla.append("inner join T_SD_SaleOrderEntry SOentry \n");sqla.append("on SOentry.FSourceBillEntryId = temp.TOBentryID\n");sqla.append("\n");sqla.append("union all \n");sqla.append("\n");sqla.append("select \n");sqla.append("\n");sqla.append("temp.APentryID APentryID,\n");sqla.append("POentry.FID SPOentryID,\n");sqla.append("'3171BFAD' entryID\n");sqla.append("\n");sqla.append("from "+tempTable+" temp\n");sqla.append("\n");sqla.append("inner join T_SM_PurOrderEntry POentry \n");sqla.append("on POentry.FSourceBillEntryId = temp.TOBentryID\n");ResultSet result = DbUtil.executeQuery(ctx,sqla.toString());try {while (result.next()) {String entryID = result.getString("entryID");if("C48A423A".equals(entryID)){SynergyBillAssistUtils.updateSaleByAP(ctx, result.getString("SPOentryID"), result.getString("APentryID"), isAudit);}else if("3171BFAD".equals(entryID)){SynergyBillAssistUtils.updatePurByAP(ctx, result.getString("SPOentryID"), result.getString("APentryID"), isAudit);}}} catch (SQLException e) {e.printStackTrace();}//释放临时表if(tempTable==null || tempTable.trim().length()==0){tem.releaseTempTable(tempTable);}/*// 2.应收单反写采购订单updatePurByAR(ctx, tempTable,pk, isAudit);// 3.应收单反写销售updateSaleByAR(ctx, tempTable,pk, isAudit);//释放临时表if(tempTable==null || tempTable.trim().length()==0){tem.releaseTempTable(tempTable);}//} else {// 1.应付单反写采购updatePurByAP(ctx, pk, isAudit);//}*/}