/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 *
 * @author joel
 */
/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Com;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.AbstractList;
import java.util.ArrayList;
import static java.util.Collections.list;
import java.util.Enumeration;
import java.util.List;


/**
 *
 * @author joel
 */
public class dbmanager implements Serializable {

   private static  Connection connection = null;
    private Statement statement = null;
   private  PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;






   public static void insertrequest(String begin ,String end ) throws SQLException {
       int i= 0;

       try {
         Class.forName("org.apache.derby.jdbc.ClientDriver");
      } catch(ClassNotFoundException e) {
         System.out.println("Class not found "+ e);
      }
        try {

             connection = DriverManager.getConnection("jdbc:derby://localhost:1527/STB","sa","sa");
          PreparedStatement stm = connection.prepareStatement( "INSERT INTO PERSON(PERSONID,FIRSTNAME,LASTNAME,ADDRESS,CITY,CONFIRM1,CONFIRM2,CONFIRM3) VALUES (?,?,?,?,?,?,?,?)" );
          stm.setInt(1, 123456);
          stm.setString(2, "NGatouo");
          stm.setString(3, "Joel");
          stm.setString(4,begin);
          stm.setString(5,end);
          stm .setBoolean(6, true);
          stm .setBoolean(7, false);
          stm .setBoolean(8, false);
    i =   stm.executeUpdate();
         } catch (Exception e) {
            System.out.println("error:ddddd ");
        }

             //  connection.toString();
//
//
         //  System.out.print( connection.toString());
        //   System.out.print(  connection.toString());
            System.out.print(end);
              System.out.print(i+"    the number of element add ");

//         System.out.print(i+" record sont state inseriti");
        return ;

         }
    public static void getrequest(ArrayList<String> out ,ArrayList<Boolean> bool) throws SQLException {
       int i= 0;

       try {
         Class.forName("org.apache.derby.jdbc.ClientDriver");
      } catch(ClassNotFoundException e) {
         System.out.println("Class not found "+ e);
      }
        try {

             connection = DriverManager.getConnection("jdbc:derby://localhost:1527/STB","sa","sa");
          PreparedStatement stm = connection.prepareStatement( "select LASTNAME , FIRSTNAME, ADDRESS,CITY, CONFIRM1,CONFIRM2,CONFIRM3 from SA.PERSON where PERSONID = 123456" );
       ResultSet rs = stm.executeQuery();
        if (rs.next()) {

                  out.add(rs.getString(1));
                    out.add(rs.getString(2));
                    out.add(rs.getString(3));
                    out.add(rs.getString(4));
                    bool.add(rs.getBoolean(5));
                    bool.add(rs.getBoolean(6));
                    bool.add(rs.getBoolean(7));

                 }

         } catch (Exception e) {
            System.out.println("error:ddddd ");
        }

        return ;

         }
       public static void rejectrequest() throws SQLException {
       int i= 0;

       try {
         Class.forName("org.apache.derby.jdbc.ClientDriver");
      } catch(ClassNotFoundException e) {
         System.out.println("Class not found "+ e);
      }
        try {

             connection = DriverManager.getConnection("jdbc:derby://localhost:1527/STB","sa","sa");
          PreparedStatement stm = connection.prepareStatement( "UPDATE PERSON SET CONFIRM1 = false WHERE PERSONID = 123456" );
       int rs = stm.executeUpdate();



         } catch (Exception e) {
            System.out.println("error:ddddd ");
        }

        return ;

         }
           public static void acceptrequest() throws SQLException {
       int i= 0;

       try {
         Class.forName("org.apache.derby.jdbc.ClientDriver");
      } catch(ClassNotFoundException e) {
         System.out.println("Class not found "+ e);
      }
        try {

             connection = DriverManager.getConnection("jdbc:derby://localhost:1527/STB","sa","sa");
          PreparedStatement stm = connection.prepareStatement( "UPDATE PERSON\n" +
"SET CONFIRM1 = true , CONFIRM2 = true WHERE PERSONID = 123456" );
       int rs = stm.executeUpdate();



         } catch (Exception e) {
            System.out.println("error:ddddd ");
        }

        return ;

         }

                 public static void InsertSystem() throws SQLException {
       int i= 0;

       try {
         Class.forName("org.apache.derby.jdbc.ClientDriver");
      } catch(ClassNotFoundException e) {
         System.out.println("Class not found "+ e);
      }
        try {

             connection = DriverManager.getConnection("jdbc:derby://localhost:1527/STB","sa","sa");
          PreparedStatement stm = connection.prepareStatement( "UPDATE PERSON SET CONFIRM3 = true WHERE PERSONID = 123456" );
       int rs = stm.executeUpdate();



         } catch (Exception e) {
            System.out.println("error:ddddd ");
        }

        return ;

         }


                            public static void UpdateTime(int time) throws SQLException {

       try {
         Class.forName("org.apache.derby.jdbc.ClientDriver");
      } catch(ClassNotFoundException e) {
         System.out.println("Class not found "+ e);
      }
        try {

             connection = DriverManager.getConnection("jdbc:derby://localhost:1527/STB","sa","sa");
          PreparedStatement stm = connection.prepareStatement( "UPDATE PERSONS SET PERSONID = ? " );
           stm.setInt(1, time);
       int rs = stm.executeUpdate();
         } catch (Exception e) {
            System.out.println("error:ddddd ");
        }

        return ;

         }
            public static int getTime() throws SQLException {
       int i= 0;

       try {
         Class.forName("org.apache.derby.jdbc.ClientDriver");
      } catch(ClassNotFoundException e) {
         System.out.println("Class not found "+ e);
      }
        try {

             connection = DriverManager.getConnection("jdbc:derby://localhost:1527/STB","sa","sa");
          PreparedStatement stm = connection.prepareStatement( "SELECT PERSONID FROM PERSONS" );
       ResultSet rs = stm.executeQuery();
        if (rs.next()) {

                  i = rs.getInt(1);


                 }

         } catch (Exception e) {
            System.out.println("error:ddddd ");
        }

        return i ;

         }


           public static int convertTime(String str1,String str2){
      int diffHours = 0 ;
      int diffInDays = 0 ;
      long diff = 0 ;
  java.util.Date d1 = null;
 java.util.Date d2 = null;
DateFormat formatter = new SimpleDateFormat("MM/dd/yyyy");
   System.out.print(str1 +"  questo è l'heure reduit alla fin ");
     System.out.print(str2 +"  questo è l'heure reduit alla fin ");
 try {
        d1 = formatter.parse(str1);
        d2 = formatter.parse(str2);
    } catch (ParseException e) {
        e.printStackTrace();
    }

    diff =  d2.getTime() - d1.getTime() ;
    diffInDays = (int) (( d2.getTime() - d1.getTime() ) / (1000 * 60 * 60 * 24)) +1;
    diffHours =(int)((diff / (60 * 60 * 1000)) +24) - 16*diffInDays ;

    System.out.print(diffHours +"  questo è l'heure reduit alla fin ");

           return diffHours ;}
}