package com.huiton.cerp.pub.util;

/**
 * Title:        WorkFlowSubsystem
 * Description:  工作流子系統相關類包
 * Copyright:    Copyright (c) 2001
 * Company:      BRITC
 * @author 高云鵬
 * @version 1.0
 */
import java.sql.*;
import javax.servlet.http.HttpSession;
import javax.servlet.http.HttpServletRequest;
import java.util.Vector;
import com.huiton.pub.dbx.JdbOp;
import com.huiton.pub.dbx.PageQuery;
import com.huiton.cerp.pub.util.WebKeys;
import com.huiton.cerp.pub.util.SubsystemKeys;
import com.huiton.cerp.pub.util.DBOperators;
import com.huiton.mainframe.util.tracer.Debug;

public class MultiDBOperator {


static private JdbOp jdbOp;
static private PageQuery pageQuery;
static private String mainDB;
static private String attachDB;
static private String tablenameMain;
static private String tablenameAttached;
static private String funcCode;
static private String sessioncode;
static private HttpServletRequest request;

static private int pageCount;
/*public MultiDBOperator( HttpServletRequest request,String mainDB,
                         String attachDB,String sessioncode,String funcCode){

                          jdbOp=getJdbOp(request,sessionCode,mainDB);
                          pageQuery=getPageQuery(request,sessionCode,mainDB,funcCode);
                         }*/
public MultiDBOperator(String mainDB,
                        String attachDB,
                        String tablenameMain,
                        String tablenameAttached,
                        String funcCode,
                        String sessioncode,
                        HttpServletRequest request)
                  throws Exception{
                          this.mainDB=mainDB;
                          this.attachDB=attachDB;
                          this.tablenameMain=tablenameMain;
                          this.tablenameAttached=tablenameAttached;
                          this.funcCode=funcCode;
                          this.sessioncode=sessioncode;
                          this.request=request;
                          this.pageQuery=DBOperators.getPageQuery(request,sessioncode,mainDB,funcCode);
                          this.jdbOp=jdbOp=DBOperators.getJdbOp(request,sessioncode,attachDB);
                          }




/*@@說明
    本方法有一定局限性，不是通用方法，須完善。
    適合于：對兩個表的查詢，其中的一個表中的某個字段為特定值時，另外一個表中有元組與之對應，須取出該元組中的某些字段
    局限：第二個表中的關鍵字為一個
         第二個表中的關鍵字為字串
    @@param:mainDB--主子系統名
            attachDB--副子系統名
            tablenameMain--主表名
            tablenameAttached--副表名
            keyWord--主表中特定字段昭示附表中有對應元組時的值
            keyFieldLocation--特定字段在取出的Vector中數組中的序號
            pagesize--取1頁的長度
            offset--無附表元組時返回數組的填充值
            mainSQL--主表查詢條件
            mainFields--主表字段：須在第一個字段中設置與附表的聯系鍵值
            mainNo--主表字段數
            attachFields--附表字段：須在第一個字段中設置與主表的聯系鍵值，該值不在最后的vector-row中出現
            attachNo--附表字段數
            currentpage--當前頁
            */
public Vector multiDBDividePage(String keyWord,
                                      int keyFieldLocation,
                                      int pagesize,
                                         String offset,
                                         String mainSQL,
                                         String[] mainFields,
                                         String[] attachFields,
                                      int currentpage)
                                       throws SQLException, Exception{
                    Debug.println("multiDBOperator:參數帶來的currentPage為："+currentpage);
                    Debug.println("multiDBOperator:mainSQL為："+mainSQL);
                    jdbOp=DBOperators.getJdbOp(request,sessioncode,attachDB);
                    pageQuery=DBOperators.getPageQuery(request,sessioncode,mainDB,funcCode);
                    Debug.println("multiDBOperator:獲得主、副數據庫代理......");

                  int mainNo=mainFields.length;
                  int attachNo=attachFields.length;
                    //獲取第一個表中的相關字段結果集
                    String fields=mainFields[0];
                    for(int i=1;i<mainNo;i++)
                                                fields+=","+mainFields[i];
                    Debug.println("multiDBOperator:主數據庫字段為："+fields);
                    Debug.println("multiDBOperator:table為："+tablenameMain);
                    Debug.println("multiDBOperator:獲得主數據庫參數為："+mainDB);
                    Debug.println("multiDBOperator:主數據庫名為:"+pageQuery.m_conn.getCatalog());
                    pageQuery.getData(fields,tablenameMain,mainSQL,pagesize);

                   this.pageCount=pageQuery.pageCount;
                    Debug.println("multiDBOperator:pagecount為："+pageCount);
                    Debug.println("multiDBOperator:pageQuery取數據完成......");
                  if(currentpage<=0)currentpage=1;
                    Debug.println("multiDBOperator:當前頁為："+currentpage);
                    Vector mainVCT=pageQuery.dividePage(currentpage,pagesize);
                   if(mainVCT==null){
                              Debug.println("multiDBOperator:無記錄，返回空......");
                            return null;
                    }
                    Debug.println("multiDBOperator:pageQuery分頁完成......");
                    Debug.println("multiDBOperator:pageQuery元素數為："+String.valueOf(mainVCT.size()));

                    //處理與第二個表的關聯
                    fields=attachFields[0];
                    Vector ultimateVCT=new Vector();
                  for(int i=1;i<attachNo;i++)
                                                fields+=","+attachFields[i];
                    Debug.println("multiDBOperator:副數據庫字段為："+fields);

                    for(int i=0;i<mainVCT.size();++i){
                            String VCTArray[]=new String[mainNo+attachNo-1];
                            String tmpVCTArray[]=new String[mainNo];
                            Debug.println("----MultiDBOperator:mainNo="+mainNo+"attachNo="+attachNo);
                            tmpVCTArray = (String[])mainVCT.elementAt(i);
                            Debug.println("----MultiDBOperator:從Vector 中取得第"+i+"個數組");
                          for(int j=0;j<mainNo;j++){
                                           VCTArray[j]=tmpVCTArray[j];
                                           Debug.println("multiDBOperator:主數據庫中的數組元素["+j+"]為："+VCTArray[j]);
                                          }
                          if(VCTArray[keyFieldLocation].toLowerCase().equals(keyWord)){
                                         String attachSQL="select "+fields+" from "+tablenameAttached+" where "
                                                        +attachFields[0]+"='"+VCTArray[0]+"'";
                                         Debug.println("multiDBOperator:副數據庫查詢SQL為："+attachSQL);
                                         ResultSet rs=jdbOp.getData(attachSQL);
                                          Debug.println("multiDBOperator:取得副數據庫結果集......");
                                   //   int appendLengh=attachFields.length;
                                         if(rs!=null){
                                                  rs.beforeFirst();
                                               if(rs.next()){
                                                       // String[] appendArray=new String[appendLength];
                                                    for(int j=1;j<attachNo;j++){
                                                                VCTArray[mainNo+j-1]=rs.getString(j+1);
                                                                Debug.println("multiDBOperator:副數據庫中的數組元素["+j+"]為："+VCTArray[mainNo+j-1]);
                                                                }
                                                    }else{
                                                    for(int j=0;j<attachNo-1;j++)
                                                                  VCTArray[mainNo+j]=offset;
                                                        Debug.println("multiDBOperator:填充1");
                                                          }
                                              }else{
                                               for(int j=0;j<attachNo-1;j++)
                                                        VCTArray[mainNo+j]=offset;
                                                   Debug.println("multiDBOperator:填充2");
                                                    }
                             }else{
                                       for(int j=0;j<attachNo-1;j++)
                                                        VCTArray[mainNo+j]=offset;
                                          Debug.println("multiDBOperator:填充3");
                              }
                              ultimateVCT.addElement(VCTArray);
                              Debug.println("multiDBOperator:即一個數組到最終返回的矢量中......");
                        }
                            return ultimateVCT;
                }

public int getPageCount()
                  {
                          return this.pageCount;
         }

public void multiDBDelete(String mainFlagField,
                            String keyWord,
                            String mainField,
                            String attachField,
                            String delSQL)
                          throws SQLException, Exception{
              Debug.println("multiDBOperator:delSQL為："+delSQL);
              jdbOp=DBOperators.getJdbOp(request,sessioncode,mainDB);
              Debug.println("multiDBOperator:獲取住數據庫代理："+mainDB);
              String tempSQL="select "+mainFlagField+","+mainField+" from "+tablenameMain+" where "+delSQL;
              Debug.println("multiDBOperator:tempSQL為："+tempSQL);
              ResultSet rs=jdbOp.getData(tempSQL);
              Debug.println("multiDBOperator:查得主數據庫數據......");
              int mainFlagFieldColNo;
              int mainFiledColNo;
              mainFlagFieldColNo=rs.findColumn(mainFlagField);
              mainFiledColNo=rs.findColumn(mainField);
              if(rs!=null){
                      Debug.println("multiDBOperator:結果集不為空......");
                      rs.beforeFirst();
                    while(rs.next()){
                          String mainFlagValue=rs.getString(mainFlagFieldColNo);
                          String mainValue=rs.getString(mainFiledColNo);
                          Debug.println("multiDBOperator:主數據庫標示--"+mainFlagValue);
                          Debug.println("multiDBOperator:主數據庫連接字段值--"+mainValue);
                          if(mainFlagValue.equals(keyWord)){
                                  tempSQL=attachField+"="+mainValue;
                                  Debug.println("multiDBOperator:有對應元組---SQL為："+tempSQL);
                                  jdbOp=DBOperators.getJdbOp(request,sessioncode,attachDB);
                                  jdbOp.delete(tablenameAttached,tempSQL);
                                  Debug.println("multiDBOperator:副數據庫刪除一條元組");
                                  }
                        }
                }
              jdbOp=DBOperators.getJdbOp(request,sessioncode,mainDB);
              jdbOp.delete(tablenameMain,delSQL);
              Debug.println("multiDBOperator:主數據庫刪除完成");
        return;

        }

public PageQuery getPageQuery(){
        return this.pageQuery;
    }

public JdbOp getJdbOp(){
        return this.jdbOp;
    }
}
 /*                   pageQuery.getData("USER_NAME,USER_UNIQUE_NO",
                                      "EPD_ADDRESS_EMPLOYEE_V", mySQL, SLIT_Vct.size());

                    jdbOp=getJdbOp(request,sessioncode,dbnameMain);
                    pageQuery=getPageQuery(request,sessioncode,dbnameMain,funcCode);
                    Vector mainVCT





  /*private DBOperators dbOperator;
  private JdbOp jdbOp;
  private PageQuery pageQuery;

  public MultiDBOperator() {
        this.dbOperator=new DBOperators();
  }

  public MultiDBOperator(String mainDB,String attachDB,sessioncode, funcCode) {
          this.dbOperator=new DBOperators();

  public JdbOp getJdbOp(HttpServletRequest request,
                          String sessionCode,
                          String subsystemKey){
        JdbOp tempJdbOp=dbOperator.getJdbOp(request,sessioncode,subsystemKey);
        this.jdbOp=tempJdbOp;
        return tempJdbOp;
  }

  public getPageQuery(HttpServletRequest request,
                        String sessionCode,
                        String subsystemKey,
                        String funcCode){
        PageQuery tempPageQuery=dbOperator.getPageQuery(request,sessioncode,subsystemKey,funcCode);
        this.pageQuery=tempPageQuery;
        return tempPageQuery;
  }
  /*用于將兩個數據庫聯起來查詢一頁
  param:

  public Vector multiDBDividePage(String dbnameMain,String dbnameAttached,String field,
                                    String keyword,int pagesize,String offset){
     Vector vct1=new Vector();
*/