package com.zhanghao.cmpp;
/**
 * <p>Title:CMPPDBAccess</p>
 * <p>Description: 湖南移動短信網(wǎng)關(guān)通訊程序</p>
 * <p>數(shù)據(jù)庫訪問類</p>
 * <p>Copyright: Copyright (c) 2004</p>
 * <p>Company: sunun tech ltd.</p>
 * @author zhanghao
 * @version 1.0
 */

import java.util.*;
import java.sql.*;

import com.zhanghao.common.database.*;
import com.zhanghao.common.util.*;

public class CMPPDBAccess {

  private static CMPPDBAccess DBAccess;

  public boolean isAvail = false;

  /** 上行數(shù)據(jù)緩沖,當(dāng)CMPPMOReceiveThread接受到上行數(shù)據(jù)后,將數(shù)據(jù)保存在該緩沖中,由
   *  CMPPMODataSaveThread線程將數(shù)據(jù)再保存到數(shù)據(jù)庫中 **/
  public Vector vctMOData = new Vector(1,1);

  /** 下行數(shù)據(jù)緩沖,當(dāng)CMPPDBAccess從數(shù)據(jù)庫中取出數(shù)據(jù)后,保存在該緩沖中,由
   *  單連接時的CMPPMOSendThread線程或雙連接時的CMPPMTSendThread線程將數(shù)據(jù)發(fā)送到ISMG **/
  public Vector vctMTData = new Vector(1,1);

  /** 網(wǎng)關(guān)回應(yīng)信息緩沖 **/
  public Vector vctRespMsg = new Vector(1,1);

  public String inertSql  = "";

  private CMPPMODataSaveThread moSaveThread;

  public CMPPDBAccess() {
    if(CMPParameter.DBType==1) inertSql = this.strInsertOracle;
    else inertSql = this.strInsertSqlServer;
  }


  /**取得CMPPMOSocketProcess的唯一實例 **/
  public static CMPPDBAccess getInstance(){
    if(DBAccess == null){
      DBAccess = new CMPPDBAccess();
    }
    if(!DBAccess.isAvail){
      DBAccess.connect();
    }
    else{

    }
    return DBAccess;
  }




  /**
   * 連接到數(shù)據(jù)庫
   * @return true(成功)or false（失敗）
   */
  public boolean connect(){

    Connection conn = null;
    ResultSet   rs  = null;
    try {
//      Class.forName(DBDriver);
//      conn = DriverManager.getConnection(Connect_URL, DBUser, DBPass);
      conn = ConnectionPool.getConnection();
      //從連接池取得連接并測試連接是否成功
      String strSql = "";
      if(CMPParameter.DBType ==1)
        strSql = "SELECT SYSDATE FROM DUAL";
      else if(CMPParameter.DBType ==2)
        strSql = "select getdate()";
      else if(CMPParameter.DBType ==3)
        strSql = "select getdate()";

      rs = conn.createStatement().executeQuery(strSql);
      rs.next();
      rs.getString(1);
      Debug.outInfo("[CMPPMaster]"+PublicFunction.getFormatTime()+" 通訊程序連接數(shù)據(jù)庫成功!");
      rs.close();
      this.isAvail = true;

      moSaveThread = new CMPPMODataSaveThread();
      moSaveThread.start();
      Debug.outInfo("[CMPPMaster]"+PublicFunction.getFormatTime()+" 緩沖數(shù)據(jù)線程啟動...");
    }
//    catch (ClassNotFoundException cnfe) {
//      disconnect();
//      System.out.println("Not Found Database Connect Driver " + cnfe);
//    }
    catch (Exception ex) {
      System.out.println("[CMPPMaster]連接數(shù)據(jù)庫失敗DBAccess.connect() 異常：" + ex);
      this.isAvail = false;
    }
    finally{
      discloseconn(conn,null,null);  //關(guān)閉connection(放回連接池)
    }

    return this.isAvail;
  }



    String strInsertOracle
        = "insert into cmpp_from_ismg ( from_ismg_id, deliver_msg_id, deliver_destnation_id," +
          "deliver_service_id, deliver_msg_fmt, deliver_src_terminal_id, deliver_registered_delivery,"+
          "deliver_msg_length, deliver_msg_content values(seq_sms.nextval,";
    String strInsertSqlServer
          = "insert into cmpp_from_ismg (deliver_msg_id, deliver_destnation_id," +
            "deliver_service_id, deliver_msg_fmt, deliver_src_terminal_id, deliver_registered_delivery,"+
            "deliver_msg_length, deliver_msg_content values(";

        /**
         * 將smgpMsg保存到數(shù)據(jù)庫中
         * @param smgpMsg
         * @throws java.lang.Exception
         */

    public void saveMOData(CMPP cmppMsg) throws Exception {

          Connection conn = null;
          PreparedStatement pstmt = null;

          try {

            conn = ConnectionPool.getConnection();

            //將狀態(tài)報告,Deliver消息插入到數(shù)據(jù)庫得SQL語句
            CMPP submitMsg = cmppMsg;
            if (submitMsg.Registered_Delivery == 1) {
              //當(dāng)為狀態(tài)報告時
              String strSql = "insert into cmpp_report (Msg_Id,Stat,Submit_time,Done_time,Dest_terminal_Id,SMSC_sequence) ";
              strSql += "values(?,?,?,?,?,?)";
              pstmt = conn.prepareStatement(strSql);
              pstmt.setLong(1, submitMsg.Sequence_Id);
              pstmt.setString(2, submitMsg.Report_Stat);
              pstmt.setString(3, submitMsg.Report_Submit_time);
              pstmt.setString(4, submitMsg.Report_Done_time);
              pstmt.setString(5, submitMsg.Report_Dest_terminal_Id);
              pstmt.setInt(6, submitMsg.Report_SMSC_sequence);
              pstmt.execute();
              pstmt.close();
            }
            else {
              //當(dāng)為Deliver消息時
              StringBuffer sbSql = new StringBuffer("");

              //CMPP3.0適應(yīng)性修改，修改了表結(jié)構(gòu)
              sbSql.append("insert into cmpp30_from_ismg (");
              sbSql.append(
                  "msg_id,isreport, service_code,src_number, src_number_type");
              sbSql.append(
                  "tp_pid,tp_udhi,dest_number,msg_fmt, msg_content,");
              sbSql.append(
                  "sys_time,link_id ) values (?,?,?,?,?,?,?,?,?,?,sysdate,?)");

              pstmt = conn.prepareStatement(sbSql.toString());
              pstmt.setString(1, String.valueOf(submitMsg.Msg_Id));
              pstmt.setString(2, String.valueOf(submitMsg.Registered_Delivery));
              pstmt.setString(3, submitMsg.Service_Id);
              pstmt.setString(4, submitMsg.Src_terminal_Id);
              pstmt.setByte(5, submitMsg.Src_terminal_type);
              pstmt.setByte(6, submitMsg.TP_pid);
              pstmt.setByte(7, submitMsg.TP_udhi);
              pstmt.setString(8, submitMsg.Dest_terminal_Id);
              pstmt.setByte(9, submitMsg.Msg_Fmt);
              pstmt.setString(10, submitMsg.Msg_Content);
              pstmt.setString(11, submitMsg.LinkID);
              pstmt.execute();
              pstmt.close();
            }
          }
          catch (Exception ex) {
            throw ex;
          }
          finally {
            discloseconn(conn, pstmt, null);
          }

        }

  /**
   * 將vctMOData中的MO數(shù)據(jù)保存到數(shù)據(jù)庫中
   * @param vctMOData
   * @throws java.lang.Exception
   */

  public void saveMOData(Vector vctMOData) throws Exception{

    Connection conn = null;
    PreparedStatement pstmt = null;

    try{

      conn = ConnectionPool.getConnection();

      //將狀態(tài)報告,Deliver消息插入到數(shù)據(jù)庫得SQL語句
      while(vctMOData.size()>0){

        CMPP submitMsg = (CMPP)vctMOData.elementAt(0);
        if(submitMsg.Registered_Delivery == 1){
          //當(dāng)為狀態(tài)報告時
          String strSql = "insert into cmpp_report (Msg_Id,Stat,Submit_time,Done_time,Dest_terminal_Id,SMSC_sequence) ";
                 strSql+= "values(?,?,?,?,?,?)";
          pstmt = conn.prepareStatement(strSql);
          //System.out.println("Report "+ submitMsg.Report_Msg_Id);
          pstmt.setLong(1,submitMsg.Report_Msg_Id);
          pstmt.setString(2,submitMsg.Report_Stat);
          pstmt.setString(3,submitMsg.Report_Submit_time);
          pstmt.setString(4,submitMsg.Report_Done_time);
          pstmt.setString(5,submitMsg.Report_Dest_terminal_Id);
          pstmt.setInt(6,submitMsg.Report_SMSC_sequence);
          pstmt.execute();
          pstmt.close();
        }
        else{
          //當(dāng)為Deliver消息時
          StringBuffer sbSql = new StringBuffer("");
//          sbSql.append("insert into cmpp_from_ismg ( from_ismg_id, deliver_msg_id, deliver_destnation_id,");
//          sbSql.append("deliver_service_id, deliver_msg_fmt, deliver_src_terminal_id, deliver_registered_delivery,");
//          sbSql.append("deliver_msg_length, deliver_msg_content");
//          sbSql.append(") values (seq_sms.nextval,");
//
//          sbSql.append("" +submitMsg.Msg_Id+",");
//          sbSql.append("'"+submitMsg.Dest_terminal_Id+"',");
//          sbSql.append("'"+submitMsg.Service_Id+"',");
//          sbSql.append("" +submitMsg.Msg_Fmt+",");
//          sbSql.append("'"+submitMsg.Src_terminal_Id+"',");
//          sbSql.append("'"+submitMsg.Registered_Delivery+"',");
//          sbSql.append("'"+submitMsg.Msg_Length+"',");
//          sbSql.append("'"+submitMsg.Msg_Content+"'");
//          sbSql.append(")");
//          stmt.addBatch(sbSql.toString());

          //CMPP3.0適應(yīng)性修改，修改了表結(jié)構(gòu)
          sbSql.append("insert into cmpp_mo_hn ( msg_id,dest_number,service_code,msg_fmt,tp_pid,tp_udhi,src_number_type,src_number,");
          sbSql.append("isreport,msg_content, sys_time, link_id ) ");;
          sbSql.append("values (?,?,?,?,?,?,?,?,?,?,sysdate,?)");

          pstmt = conn.prepareStatement(sbSql.toString());
          pstmt.setString(1,String.valueOf(submitMsg.Msg_Id));
          pstmt.setString(2,submitMsg.Dest_terminal_Id);
          pstmt.setString(3,submitMsg.Service_Id);
          pstmt.setByte(4,submitMsg.Msg_Fmt);
          pstmt.setByte(5,submitMsg.TP_pid);
          pstmt.setByte(6,submitMsg.TP_udhi);
          pstmt.setByte(7,submitMsg.Src_terminal_type);
          pstmt.setString(8,submitMsg.Src_terminal_Id);
          pstmt.setByte(9,submitMsg.Registered_Delivery);
          pstmt.setString(10,submitMsg.Msg_Content);
          pstmt.setString(11,submitMsg.LinkID);
          pstmt.execute();
          pstmt.close();
        }
        vctMOData.removeElementAt(0);
      }
    }
    catch(Exception ex){
      CMPP deliverMsg = (CMPP)vctMOData.elementAt(0);
      Debug.outInfo("\n[ERROR]"+PublicFunction.getFormatTime()+" 保存數(shù)據(jù)失敗:("+deliverMsg.Src_terminal_Id+") ServiceID:"
                    +deliverMsg.Service_Id + " SrcUserType:"+deliverMsg.Src_terminal_type+" MsgContent:"+deliverMsg.Msg_Content+ "  LinkId:"
                    +deliverMsg.LinkID + " DestID:"+ deliverMsg.Dest_terminal_Id);

      vctMOData.removeElementAt(0);
      throw ex;
    }
    finally{
      discloseconn(conn,pstmt,null);
    }

  }




  /**
   * 將待發(fā)送數(shù)據(jù)放入緩沖
   * @return
   */
  public boolean fectchMTDataToBuffer(){

    String strSql = "";
//    if(CMPParameter.DBType ==1)
//    strSql = "select * from (select rownum as my_rownum,a.* from( "+
//             "SELECT * FROM cmpp_to_ismg WHERE submit_status = 1"+
//             ") a where rownum<= 10 )where my_rownum> 0";
//    else
//    strSql = "select top 10 * from cmpp_to_ismg where submit_status = 1";

    if(CMPParameter.DBType ==1)
    strSql = "select * from (select rownum as my_rownum,a.* from( "+
             "SELECT * FROM cmpp_mt_hn WHERE status = -1 and sys_time < sysdate"+
             ") a where rownum<= 10 )where my_rownum> 0";
    else
    strSql = "select top 10 * from cmpp_mt_hn where status = -1";

    ResultSet rs    = null;
    Statement stmt  = null;
    Connection conn = null;

    try {
        //System.out.println("[CMPPMaster]讀取待發(fā)送信息到緩沖..." + strSql);
        conn = ConnectionPool.getConnection();
        stmt = conn.createStatement();
        rs   = stmt.executeQuery(strSql);
    }
    catch (Exception ex) {
        this.isAvail = false;
        Debug.outWarn("[CMPPMaster]Fetch data from cmpp_mt_hn table failed: " + ex) ;
        return false;
    }

    byte[] msg;
    try {
         int num        = 0;   //取出待發(fā)送數(shù)據(jù)的計數(shù)器
         String content = "";
         while( rs.next()) {