import com.mysql.jdbc.Connection;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;



public class AccesBd {




//	* Definition du format utilise pour les dates
	static Locale locale = Locale.getDefault();
	static Date actuelle = new Date();

//	* Definition du format utilise pour les dates
	static DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");


    String dte;
	int photo;
    Connection conn;
    static Statement stmt;
   // static ResultSet rs;
	//Connection conn;
	public AccesBd (){
		

        	String url = "jdbc:mysql://localhost/gestion_personnel";
			
			String driver = "com.mysql.jdbc.Driver";
			String userName = "root";
			String password = "";

		try
		{


		    Class.forName(driver);
			conn =  (Connection) DriverManager.getConnection(url,userName,password);
		}
         catch ( SQLException E)
        {
              System.out.println("SQLException: " + E.getMessage());
              System.out.println("SQLState: " + E.getSQLState());
              System.out.println("VendorError: " + E.getErrorCode());
        }
         catch ( ClassNotFoundException E)
        {
              E.printStackTrace();
        }

           String dat = dateFormat.format(actuelle);
           dte = dat;
           
	}

	public void MAJ_pt(String photo,String mat) throws SQLException
	{
		
		 PreparedStatement st = conn.prepareStatement("SELECT * FROM pointage_jour_ds where date_deb <= ? <= date_fin");
		 st.setString(1,dte);
		 ResultSet rs = st.executeQuery();
		   if(rs.next())
		    {
           
			
			 photo_ds(photo,mat,dte);
		    }
		   else
		    {
			 
				
			   photo_su(photo,mat,dte);
			
		    }
		   rs.close();
		   st.close();
	}
	
    public void photo_ds(String ph,String mat,String dt) throws SQLException
    {
    	

		  PreparedStatement st = conn.prepareStatement("SELECT * FROM pointage_pers_ds where phot_deb_matin = '' and matricule = ? and date_pointage = ?");
		   st.setString(1,mat);
           st.setString(2,dt);
		   ResultSet rs = st.executeQuery();
		   if(rs.next())
	        {	
			   PreparedStatement st1;
			   st1 = conn.prepareStatement("update pointage_pers_ds set phot_deb_matin = ? where matricule = ? and date_pointage = ?");
			   st1.setString(1,ph);
			   st1.setString(2,mat);
               st1.setString(3,dt);
               st1.executeUpdate();
			   st1.close();

              
	        }
		   else
		    {
			   
				   PreparedStatement st2 = conn.prepareStatement("SELECT * FROM pointage_pers_ds where phot_fin_matin = '' and matricule = ? and date_pointage = ?");
				   
                   st2.setString(1,mat);
                   st2.setString(2,dt);
				   ResultSet rs2 = st2.executeQuery();
				   if(rs2.next())
			        {
					   PreparedStatement st3;
					   st3 = conn.prepareStatement("update pointage_pers_ds set phot_fin_matin = ? where matricule = ? and date_pointage = ?");
					   st3.setString(1,ph);
					   st3.setString(2,mat);
                       
                       st3.setString(3,dt);
					   st3.executeUpdate();
                       st3.close();
			        }
				   else
				    {
					   
						   PreparedStatement st4 = conn.prepareStatement("SELECT * FROM pointage_pers_ds where phot_deb_soir = '' and matricule = ? and date_pointage = ?");
						  
                           st4.setString(1,mat);
                           st4.setString(2,dt);
						   ResultSet rs3 = st4.executeQuery();
						   if(rs3.next())
					        {	
							   PreparedStatement st5;
							   st5 = conn.prepareStatement("update pointage_pers_ds set phot_deb_soir = ? where matricule = ? and date_pointage = ?");
							   st5.setString(1,ph);
							   st5.setString(2,mat);
                               st5.setString(3,dt);
                               st5.executeUpdate();
							   st5.close();
					        }
						   else
						    {
							   PreparedStatement st6;
							   st6 = conn.prepareStatement("update pointage_pers_ds set phot_fin_soir = ? where matricule = ? and date_pointage = ?");
							   st6.setString(1,ph);
							   st6.setString(2,mat);
                               st6.setString(3,dt);
                               st6.executeUpdate();
							   st6.close();
						    }
						   rs3.close();
                           st4.close();
					  }
				   rs2.close();
                   st2.close();
				 }
		   rs.close();
		   st.close();
					   
	      }
		    
			   public void photo_su (String ph,String mat,String dt) throws SQLException
			   {
				   
				   PreparedStatement st = conn.prepareStatement("SELECT * FROM pointage_pers_su where phot_deb_jour = '' and matricule = ? and date_pointage = ?");
				   
                   st.setString(1,mat);
                   st.setString(2,dt);
				   ResultSet rs = st.executeQuery();
				   if(rs.next())
			        {	  
					   PreparedStatement st1;
					   st1 = conn.prepareStatement("update pointage_pers_su set phot_deb_jour = ? where matricule = ? and date_pointage = ?");
					   st1.setString(1,ph);
					   st1.setString(2,mat);
                       st1.setString(3,dt);
                       st1.executeUpdate();
					   st1.close();
			        }
				   else
				    {
					   PreparedStatement st1;
					   st1 = conn.prepareStatement("update pointage_pers_su set phot_fin_jour = ? where matricule = ? and date_pointage = ?");
					   st1.setString(1,ph);
					   st1.setString(2,mat);
                       st1.setString(3,dt);
                       st1.executeUpdate();
					   st1.close();
				    }
				   rs.close();
				   st.close();
			      
					   
		        }
					
}