/*
 * 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 interfacePharmacie;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Random;

/**
 *
 * @author awn27
 */
public class BDPharmacie {
    static String driver="com.mysql.jdbc.Driver" ;
    static String urlBD="jdbc:mysql://localhost:3306/gespharma";
    static String userID="root";
    static String password="";
    static Connection conex;

public static Connection connectToBD(){
    try 
    {
        Class.forName(driver).newInstance();
        conex=DriverManager.getConnection(urlBD, userID,password);
        return conex;     
    } catch (Exception e) {
        e.printStackTrace();
        return null;
     }
    }
public boolean saveUser(String login,String nom,String password,String profil){
    try{
        connectToBD();
        String sql="INSERT into user(login,nom,password,profil, id_pharmacie)values(?,?,?,?,?)";
        PreparedStatement stm=conex.prepareStatement(sql);
        stm.setString(1,login);
        stm.setString(2, nom);
        stm.setString(3,password);
        stm.setString(4,profil);
        //stm.setString (5,id);
        stm.executeUpdate();
        return true;
        
    }catch(Exception e){
        e.printStackTrace();
        return false;
    }
}

public boolean saveFirstUserPharma(String login,String password,String nom, int idPharm){
    try{
        String statut = "Pharmacien";
        connectToBD();
        String sql="INSERT into user(login,password,nom, profil, id_pharmacie)values(?,?,?,?,?)";
        PreparedStatement stm=conex.prepareStatement(sql);
        stm.setString(1, login);
        stm.setString(2, password);
        stm.setString(3, nom);
        stm.setString(4, statut);
        stm.setInt(5, idPharm);
        stm.executeUpdate();
        return true;
        
    }catch(Exception e){
        e.printStackTrace();
        return false;
    }
}

public static String checkUser(String login, String password)
{
    try
    {
        connectToBD();
        String sql = "SELECT * FROM user WHERE (login=? AND password=? AND id_pharmacie=? )";
        PreparedStatement stm = conex.prepareStatement(sql);
        stm.setString(1, login);
        stm.setString(2, password);
        stm.setInt(3, InterfaceLogin.idPharmacie);
        ResultSet rs=stm.executeQuery();
        
            if (rs.next())
            {
                return rs.getString("id_user");
            }
            else
            {
                return null;
            }
    }
    catch (Exception e) {
        e.printStackTrace();
        return null;
        }
}

public static String checkLoginUser(String login)
{
    try 
    {
        connectToBD();
        String sql="SELECT * FROM user WHERE (login=? AND id_pharmacie=?)";
        PreparedStatement stm = conex.prepareStatement(sql);
        stm.setString(1,login);
        stm.setInt(2, InterfaceLogin.idPharmacie);
        
        ResultSet rs=stm.executeQuery();
         
        if(rs.next())
        {
            
            return rs.getString("id_user");
            //return rs.getString("nom_pharmacie");
        }
        else
        {
            return null;
        }
    }
    catch (Exception e) {
        e.printStackTrace();
        return null;
        
    }
}

public static String checkNomUser (String nom)
{
    try 
    {
        connectToBD();
        String sql="SELECT * FROM user WHERE (login=? AND id_pharmacie=?)";
        PreparedStatement stm = conex.prepareStatement(sql);
        stm.setString(1,nom);
        stm.setInt(2, InterfaceLogin.idPharmacie);
        
        ResultSet rs=stm.executeQuery();
         
        if(rs.next())
        {
            
            return rs.getString("nom");
            
        }
        else
        {
            return null;
        }
    }
    catch (Exception e) {
        e.printStackTrace();
        return null;
        
    }
}


public static String checkCodeCarte (String code)
{
    try 
    {
        connectToBD();
        String sql="SELECT * FROM carte_pharmacie WHERE (code_cartep=?)";
        PreparedStatement stm = conex.prepareStatement(sql);
        stm.setString(1,code);
        
        ResultSet rs=stm.executeQuery();
         
        if(rs.next())
        {
            
            return rs.getString("statut_carte");
            
        }
        else
        {
            return null;
        }
    }
    catch (Exception e) {
        e.printStackTrace();
        return null;
        
    }
}

public static String checkMontantCarte (String code)
{
    try 
    {
        connectToBD();
        String sql="SELECT * FROM carte_pharmacie WHERE (code_cartep=?)";
        PreparedStatement stm = conex.prepareStatement(sql);
        stm.setString(1,code);
        
        ResultSet rs=stm.executeQuery();
         
        if(rs.next())
        {
            
            return rs.getString("montant_cartep");
            
            
        }
        else
        {
            return null;
        }
    }
    catch (Exception e) {
        e.printStackTrace();
        return null;
        
    }
}

public static String checkIDCarte (String code)
{
    try 
    {
        connectToBD();
        String sql="SELECT * FROM carte_pharmacie WHERE (code_cartep=?)";
        PreparedStatement stm = conex.prepareStatement(sql);
        stm.setString(1,code);
        
        ResultSet rs=stm.executeQuery();
         
        if(rs.next())
        {
            
            return rs.getString("id_cartep");
            
            
        }
        else
        {
            return null;
        }
    }
    catch (Exception e) {
        e.printStackTrace();
        return null;
        
    }
}

public boolean saveCodeCarteClient(int montant){
    try{
        connectToBD();
        String sql="UPDATE pharmacie set montant_pharmacie=montant_pharmacie+? WHERE id_pharmacie = ? ";
        PreparedStatement stm=conex.prepareStatement(sql);
        stm.setInt(1,montant);
        stm.setInt(2, InterfaceLogin.idPharmacie);
        stm.executeUpdate();
        return true;
        
    }catch(Exception e){
        e.printStackTrace();
        return false;
    }
}

public boolean updateStatutCarte(String code){
    try{
        connectToBD();
        String sql="UPDATE carte_pharmacie set statut_carte='oui' WHERE code_cartep= ? ";
        PreparedStatement stm=conex.prepareStatement(sql);
        stm.setString(1,code);
        stm.executeUpdate();
        return true;
        
    }catch(Exception e){
        e.printStackTrace();
        return false;
    }
}

public static String checkCreditActuelPharmacie (int id)
{
    try 
    {
        connectToBD();
        String sql="SELECT * FROM pharmacie WHERE id_pharmacie=?";
        PreparedStatement stm = conex.prepareStatement(sql);
        stm.setInt(1, InterfaceLogin.idPharmacie);
        
        ResultSet rs=stm.executeQuery();
         
        if(rs.next())
        {
            
            return rs.getString("montant_pharmacie");
            
        }
        else
        {
            return null;
        }
    }
    catch (Exception e) {
        e.printStackTrace();
        return null;
        
    }
}

public boolean saveCarteAchete(int id, int idPharm){
    try{
        connectToBD();
        String sql="INSERT into cartep_achete(id_cartep, id_pharmacie,date_achat_carte)values(?,?,NOW())";
        PreparedStatement stm=conex.prepareStatement(sql);
        stm.setInt(1, id);
        stm.setInt(2, idPharm);
        stm.executeUpdate();
        return true;
        
    }catch(Exception e){
        e.printStackTrace();
        return false;
    }
}

public static String checkProfil(String level)
{
        try 
    {
        connectToBD();
        String sql="SELECT * FROM user WHERE (login=? AND id_pharmacie=?)";
        PreparedStatement stm = conex.prepareStatement(sql);
        stm.setString(1,level);
        stm.setInt(2, InterfaceLogin.idPharmacie);
        
        ResultSet rs=stm.executeQuery();
         
        if(rs.next())
        {
            
            return rs.getString("profil");
            
        }
        else
        {
            return null;
        }
    }
    catch (Exception e) {
        e.printStackTrace();
        return null;
        
    }
}

public static String checkProfilPharma(String level0)
{
        try 
    {
        connectToBD();
        String sql="SELECT * FROM pharmacie WHERE (code_pharmacie=?)";
        PreparedStatement stm = conex.prepareStatement(sql);
        stm.setString(1,level0);
        //stm.setInt(2, InterfaceLogin.idPharmacie);
        
        ResultSet rs=stm.executeQuery();
         
        if(rs.next())
        {
            
            return rs.getString("profil");
            
        }
        else
        {
            return null;
        }
    }
    catch (Exception e) {
        e.printStackTrace();
        return null;
        
    }
}

public static String checkPharmacie(String login,String password)
{
    try 
    {
        connectToBD();
        String sql="SELECT * FROM pharmacie WHERE code_pharmacie=? AND password_pharmacie=?";
        PreparedStatement stm = conex.prepareStatement(sql);
        stm.setString(1,login);
        stm.setString(2,password);
        ResultSet rs=stm.executeQuery();
         
        if(rs.next())
        {
            
            return rs.getString("id_pharmacie");
        }
        else
        {
            return null;
        }
    }
    catch (Exception e) {
        e.printStackTrace();
        return null;
        
    }
}


public static String checkLoginPharmacie(String login)
{
    try 
    {
        connectToBD();
        String sql="SELECT * FROM pharmacie WHERE code_pharmacie=?";
        PreparedStatement stm = conex.prepareStatement(sql);
        stm.setString(1,login);
        
        ResultSet rs=stm.executeQuery();
         
        if(rs.next())
        {
            
            return rs.getString("id_pharmacie");
            //return rs.getString("nom_pharmacie");
        }
        else
        {
            return null;
        }
    }
    catch (Exception e) {
        e.printStackTrace();
        return null;
        
    }
}

public static String checkSoldePharmacie(String login)
{
    try 
    {
        connectToBD();
        String sql="SELECT * FROM pharmacie WHERE code_pharmacie=?";
        PreparedStatement stm = conex.prepareStatement(sql);
        stm.setString(1,login);
        
        ResultSet rs=stm.executeQuery();
         
        if(rs.next())
        {
            
            return rs.getString("montant_pharmacie");
            //return rs.getString("nom_pharmacie");
        }
        else
        {
            return null;
        }
    }
    catch (Exception e) {
        e.printStackTrace();
        return null;
        
    }
}

public static String checkNomPharmacie(String login)
{
    try 
    {
        connectToBD();
        String sql="SELECT * FROM pharmacie WHERE code_pharmacie=?";
        PreparedStatement stm = conex.prepareStatement(sql);
        stm.setString(1,login);
        
        ResultSet rs=stm.executeQuery();
         
        if(rs.next())
        {
            
            return rs.getString("nom_pharmacie");
            //return rs.getString("nom_pharmacie");
        }
        else
        {
            return null;
        }
    }
    catch (Exception e) {
        e.printStackTrace();
        return null;
        
    }
}
public boolean saveUser(String login,String password,String nom,String profil, int idPharma){
    try{
        connectToBD();
        String sql="INSERT into user(login, password, nom, profil, id_pharmacie)values(?,?,?,?,?)";
        PreparedStatement stm=conex.prepareStatement(sql);
        stm.setString(1,login);
        stm.setString(2, password);
        stm.setString(3,nom);
        stm.setString(4,profil);
        stm.setInt(5,idPharma);
        stm.executeUpdate();
        return true;
        
    }catch(Exception e){
        e.printStackTrace();
        return false;
    }
}
  public static String checkCodePharmacie (String code){
        try {
            String sql = "SELECT * FROM pharmacie WHERE code_pharmacie = ?";
            PreparedStatement stm = conex.prepareStatement(sql);
            stm.setString(1, code);
            ResultSet rs = stm.executeQuery();
            
            if(rs.next()) {
                return rs.getString("code_pharmacie");
               } else return null;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    
}
  
public boolean savePharmacie(String code,String nom,String adresse, String telephone,String password){
    try{
        connectToBD();
        String sql="INSERT INTO pharmacie(code_pharmacie, nom_pharmacie, adresse_pharmacie, telephone_pharmacie, password_pharmacie) VALUES (?,?,?,?,?)";
        PreparedStatement stm=conex.prepareStatement(sql);
        stm.setString(1,code);
        stm.setString(2, nom);
        stm.setString(3,adresse);
        stm.setString(4,telephone);
        stm.setString(5,password);
        stm.executeUpdate();
        return true;
        
    }catch(Exception e){
        e.printStackTrace();
        return false;
    }
}

public boolean saveClient(String prenom,String nom,String adresse,String telephone, int idPharma){
    try{
connectToBD();
        String sql="INSERT into client (prenom, nom, adresse, telephone, id_pharmacie)VALUES(?,?,?,?,?)";
        PreparedStatement stm=conex.prepareStatement(sql);
        stm.setString(1,prenom);
        stm.setString(2, nom);
        stm.setString(3,adresse);
        stm.setString(4,telephone);
        stm.setInt(5,idPharma);
        stm.executeUpdate();
               return true;
        
    }catch(Exception e){
        e.printStackTrace();
        return false;
    }
}

public boolean saveNewCarteCredit (int montant, String code){
    
    try {
        connectToBD();
        String sql = "INSERT into carte_pharmacie(montant_cartep, code_cartep) \n"
                + "VALUES (?,?)";
        PreparedStatement stm = conex.prepareStatement(sql);
        stm.setInt(1,montant);
        stm.setString(2,code);
        stm.executeUpdate();
        return true;
        
    } catch(Exception e){
        e.printStackTrace();
        return false;
    }
}
public boolean saveMedicament(String nom,String code,String type,String description, String personne,String contenance, String posologie,
        String fabricant, String date, float pu, int stock, byte [] image, int idPharma){
    try{
        connectToBD();
        String sql="INSERT into medicament(nom_medicament, code, type_medicament, description_medicament, personne,"
                + "contenance_medicament, posologie_medicament, fabriquant_medicament, date_expiration, pu, stock,"
                + "image, id_pharmacie)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement stm=conex.prepareStatement(sql);
        stm.setString(1,nom);
        stm.setString(2, code);
        stm.setString(3,type);
        stm.setString(4,description);
        stm.setString(5, personne);
        stm.setString(6,contenance);
        stm.setString(7, posologie);
        stm.setString(8,fabricant);
        stm.setString(9,date);
        stm.setFloat(10, pu);
        stm.setInt(11,stock);
        stm.setBytes(12,image);
        stm.setInt(13,idPharma);
        stm.executeUpdate();
        return true;
        
    }catch(Exception e){
        e.printStackTrace();
        return false;
    }
}

    public static String checkCodeMedicament (String code){
        try {
            String sql = "SELECT * FROM medicament WHERE code = ?";
            PreparedStatement stm = conex.prepareStatement(sql);
            stm.setString(1, code);
            ResultSet rs = stm.executeQuery();
            
            if(rs.next()) {
                return rs.getString("code");
               } else return null;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    
}
    
     public static String checkTelephoneClient (String telephone){
        try {
            String sql = "SELECT * FROM client WHERE telephone = ?";
            PreparedStatement stm = conex.prepareStatement(sql);
            stm.setString(1, telephone);
            ResultSet rs = stm.executeQuery();
            
            if(rs.next()) {
                return rs.getString("telephone");
               } else return null;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    
}
     
    private String personne;
}

