package  beans.DB.base;

 import  beans.DB.DBConnectionDispatcher;

 import  java.sql.*;

/**
 * Created by IntelliJ IDEA.
 * User: oed0nv
 * Date: 21.03.2007
 * Time: 14:44:07
 * To change this template use File | Settings | File Templates.
 */
 public   class  UnifyNumberClamping {
    String qualifier;
    PreparedStatement pstmt =  null ;
    Connection conn =  null ;
    Statement stmt =  null ;
     private  ResultSet rs =  null ;
    //********************************************************
    // Декларация переменных
    String sql;
    int unNomOgr;
    private java.sql.Timestamp currentTimestamp;
//    private String currentTimestamp;
    //********************************************************


    public UnifyNumberClamping(){
        try{
            //получаем соеденение из пула
            qualifier = new DBConnectionDispatcher().getQualifier();
            conn = new DBConnectionDispatcher().getConnection();
            //создаем объект tatement,который можно использовать для выполнения SQL-запроса
            stmt = conn.createStatement();

            sql="select current timestamp from sysibm.sysdummy1";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()){
//                currentTimestamp=Timestamp.valueOf(TimestampChange(rs.getString(1)));
                currentTimestamp=rs.getTimestamp(1);
            }

/*
            sql="insert into "+qualifier+"KU_UNN_OGR (UNN_TS) values('"+timestampChange(currentTimestamp)+"')";
            stmt.executeUpdate(sql);
*/
            sql="insert into "+qualifier+"KU_UNN_OGR (UNN_TS) values(?)";
            pstmt = conn.prepareStatement(sql);
//            pstmt.setString(1,String.valueOf(currentTimestamp));
            pstmt.setTimestamp( 1 ,currentTimestamp);
            pstmt.executeUpdate(sql);


/*
            sql="select unn_ogr from "+qualifier+"KU_UNN_OGR where UNN_TS='"+timestampChange(currentTimestamp)+"'";
            rs = stmt.executeQuery(sql);

            while (rs.next()){
                unNomOgr=rs.getInt(1);
            }
*/
            sql="select unn_ogr from "+qualifier+"KU_UNN_OGR where UNN_TS=?";
            pstmt = conn.prepareStatement(sql);
//            pstmt.setString(1,String.valueOf(currentTimestamp));
            pstmt.setTimestamp( 1 ,currentTimestamp);
            rs = pstmt.executeQuery();

             while  (rs.next()){
                unNomOgr=rs.getInt( 1 );
            }

/*
            sql="delete from "+qualifier+"KU_UNN_OGR where UNN_TS='"+timestampChange(currentTimestamp)+"'";
            stmt.executeUpdate(sql);
*/
            sql="delete from "+qualifier+"KU_UNN_OGR where UNN_TS=?";
            pstmt = conn.prepareStatement(sql);
//            pstmt.setString(1,String.valueOf(currentTimestamp));
            pstmt.setTimestamp( 1 ,currentTimestamp);
            pstmt.executeUpdate(sql);

//            System.out.println("UNN"+unNomOgr);

        } catch (SQLException sqle){
            System.out.println("***Bean UnifyNumberClamping***SQLException: "+sqle.getMessage());
            sqle.printStackTrace();
             throw   new  SecurityException(sqle.getMessage());
        }  catch  (Exception e){
            System.out.println("***Bean UnifyNumberClamping***Exception: "+e.getMessage());
             throw   new  SecurityException(e.getMessage());
        }  finally {
             try {
                //при закрытии объекта Statement (оператор), закрываются все связанные с ним ResultSet
                 if (stmt!= null ){
                    stmt.close();
                }
                 if (pstmt!= null ){
                    pstmt.close();
                }
                //ОЧЕНЬ ВАЖНО!!!!!Этот код возвращает соеденение в пул
                 if (conn!= null ){
                    conn.close();
                }
            } catch (SQLException sqle){
                System.out.println("***Bean UnifyNumberClamping***SQLException: "+sqle.getMessage());
            }
        }


    }



      //приводит к виду Timestamp в DB2
     public   static  String timestampChange(Timestamp Timestamp) {
        String r = String.valueOf(Timestamp);
        String d = "-";
        String p = ".";
        // '2004-11-15 18:29:32.083961' caiaiyai ia '2004-11-15-18.29.32.083961'
        r = r.substring( 0 , 4 ) + d + r.substring( 5 , 7 )+d+r.substring( 8 , 10 )+d+r.substring( 11 , 13 )+p+
                r.substring( 14 , 16 )+p+r.substring( 17 , 19 )+p+r.substring( 20 );

         return  r;
    }

     public   int  getNum(){
         return  unNomOgr;
    }

}

           //получаем соеденение из пула
            qualifier =  new  DBConnectionDispatcher().getQualifier();
            conn =  new  DBConnectionDispatcher().getConnection();
            //создаем объект tatement,который можно использовать для выполнения SQL-запроса
            stmt = conn.createStatement();

            sql="select current timestamp from sysibm.sysdummy1";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
             while  (rs.next()){

                currentTimestamp=rs.getTimestamp( 1 );
            }

            sql="insert into "+qualifier+"KU_UNN_OGR (UNN_TS) values(?)";
            pstmt = conn.prepareStatement(sql);

            pstmt.setTimestamp( 1 ,currentTimestamp);
            pstmt.executeUpdate(sql);


            sql="select unn_ogr from "+qualifier+"KU_UNN_OGR where UNN_TS=?";
            pstmt = conn.prepareStatement(sql);

            pstmt.setTimestamp( 1 ,currentTimestamp);
            rs = pstmt.executeQuery();

             while  (rs.next()){
                unNomOgr=rs.getInt( 1 );
            }

            sql="delete from "+qualifier+"KU_UNN_OGR where UNN_TS=?";
            pstmt = conn.prepareStatement(sql);

            pstmt.setTimestamp( 1 ,currentTimestamp);
            pstmt.executeUpdate(sql);

            sql="insert into "+qualifier+"KU_UNN_OGR (UNN_TS) values(?)";
            pstmt = conn.prepareStatement(sql);

            pstmt.setTimestamp( 1 ,currentTimestamp);
            pstmt.executeUpdate(sql);

"insert into "+qualifier+"KU_UNN_OGR (UNN_TS) values('"+timestampChange(currentTimestamp)+"')"

pstmt.executeUpdate(sql);