/**
 * 
 */
package org.mynonglam.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.mynonglam.connectdb.ConnectionDTB;
import org.mynonglam.entitys.Professtion;

/**
 * @author Dao
 *
 */
public class ProfesstionDAO {
	
	private static Connection connection;
	private static PreparedStatement statement = null;
	private ResultSet resultSet;

	/**
	 * 
	 */
	public ProfesstionDAO() {
		connection = ConnectionDTB.getConnection();
	}
	
	public List<Professtion> getListProfesstionForAccount(String idacc) {
		List<Professtion> result = new ArrayList<Professtion>();
		
		String sql = "SELECT idpro, companyname, city, designation, startyear, endyear, isworkinghere FROM professtion where email=?";
		
		try {
			statement = connection.prepareStatement(sql);
			statement.setString(1, idacc);
			
			resultSet = statement.executeQuery();
			while(resultSet.next()) {
				Professtion temp = new Professtion();
				temp.setIdpro(resultSet.getString("idpro"));
				temp.setCompanyname(resultSet.getString("companyname"));
				temp.setCity(resultSet.getString("city"));
				temp.setDesignation(resultSet.getString("designation"));
				temp.setStartyear(resultSet.getString("startyear"));
				temp.setEndyear(resultSet.getString("endyear"));
				temp.setIsworkinghere(resultSet.getBoolean("isworkinghere"));
				
				result.add(temp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public Professtion getProfesstion(String idpro) {
		Professtion result = null;
		
		String sql = "SELECT idpro, companyname, city, designation, startyear, endyear FROM professtion where idpro=?";
		try {
			statement = connection.prepareStatement(sql);
			statement.setString(1, idpro);
			
			resultSet = statement.executeQuery();
			
			if(resultSet.next()) {
				result = new Professtion();
				result.setIdpro(resultSet.getString("idpro"));
				result.setCompanyname(resultSet.getString("companyname"));
				result.setCity(resultSet.getString("city"));
				result.setDesignation(resultSet.getString("designation"));
				result.setStartyear(resultSet.getString("startyear"));
				result.setEndyear(resultSet.getString("endyear"));
			}
		} catch (SQLException e) {
			result = null;
			e.printStackTrace();
		}
		
		
		return result;
	}
	
	public boolean updateProfesstionForAccount(Professtion professtion) {
		boolean result = false;
		String sql = "UPDATE professtion SET companyname=?, city=?, designation=?, startyear=?, endyear=? WHERE idpro=?";
		try {
			statement = connection.prepareStatement(sql);
			statement.setString(1, professtion.getCompanyname());
			statement.setString(2, professtion.getCity());
			statement.setString(3, professtion.getDesignation());
			statement.setString(4, professtion.getStartyear());
			statement.setString(5, professtion.getEndyear());
			statement.setString(6, professtion.getIdpro());
			
			if(statement.executeUpdate() > 0) {
				result = true;
			}
		} catch (SQLException e) {
			result = false;
			e.printStackTrace();
		}
		
		return result;
	}
	
	public boolean insertProfesstionForAccount(Professtion professtion) {
		boolean result = false;
		String sql = "INSERT INTO professtion(idpro, email, companyname, city, designation, startyear, endyear, isworkinghere) VALUES (?,?,?,?,?,?,?,1)";
		try {
			statement = connection.prepareStatement(sql);
			statement.setString(1, professtion.getIdpro());
			statement.setString(2, professtion.getAccount().getEmail());
			statement.setString(3, professtion.getCompanyname());
			statement.setString(4, professtion.getCity());
			statement.setString(5, professtion.getDesignation());
			statement.setString(6, professtion.getStartyear());
			statement.setString(7, professtion.getEndyear());
			
			if(statement.executeUpdate() > 0) {
				result = true;
			}
		} catch (SQLException e) {
			result = false;
			e.printStackTrace();
		}
		
		return result;
	}
	
	public boolean deleteProfesstionAccount(String idpro, String email) {
		boolean result = false;
		String sql = "DELETE FROM professtion WHERE idpro=? AND email=?";
		
		try {
			statement = connection.prepareStatement(sql);
			statement.setString(1, idpro);
			statement.setString(2, email);
			
			if(statement.executeUpdate() > 0) {
				result = true;
			}
		} catch (SQLException e) {
			result = false;
			e.printStackTrace();
		}
		
		return result;
	}
	
	public String getIDProfesLast() {
		String result = "";
		
		String sql = "SELECT idpro FROM professtion ORDER BY idpro DESC LIMIT 0,1";
		try {
			statement = connection.prepareStatement(sql);
			resultSet = statement.executeQuery();
			
			if(resultSet.next()) {
				result = resultSet.getString("idpro");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public static void main(String[] args) {
		System.out.println(new ProfesstionDAO().getIDProfesLast());
	}

}
