package com.fgm.statusreport.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.fgm.statusreport.db.PoolManager;
import com.fgm.statusreport.domain.Suggestion;

public class SuggestionsMySqlDAO {
	private String chargeCode;
	private String suggestedTask;
	private Vector checkDuplicate = null;

	public void insertIntoDb(String cCode, String sTask)
	{
		chargeCode = cCode;
		suggestedTask = sTask;
		 
		try {
			checkDuplicate = new Vector();
			checkDuplicate = getSuggestions(chargeCode);
			
			if(checkDuplicate.isEmpty()){
				PoolManager manager = PoolManager.getInstance();
				Connection conn = manager.getConnection("general");			
				String sql_string = "INSERT INTO suggestedtasks ( code , task ) VALUES ('" + chargeCode + "', '" + suggestedTask + "')";
				PreparedStatement prepStmt = conn.prepareStatement(sql_string);
				prepStmt.execute();
				prepStmt.close();
				manager.freeConnection("general", conn);								
			} 										
			else if (noRepeat()){													
				PoolManager manager1 = PoolManager.getInstance();
				Connection conn1 = manager1.getConnection("general");			
				String sql_str = "INSERT INTO suggestedtasks ( code , task ) VALUES ('" + chargeCode + "', '" + suggestedTask + "')";
				PreparedStatement prepStatemt = conn1.prepareStatement(sql_str);
				prepStatemt.execute();
				prepStatemt.close();
				manager1.freeConnection("general", conn1);
			}
					
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}		
	}
		
	public Vector getSuggestions(String chargeCode)
	{
		Vector suggestions = new Vector();
		try {
			PoolManager manager = PoolManager.getInstance();
			Connection conn = manager.getConnection("general");
			String sql_str = "SELECT * FROM suggestedtasks where code = '" + chargeCode + "' ORDER BY task ASC";
			PreparedStatement prepStmt = conn.prepareStatement(sql_str);
			ResultSet rs = prepStmt.executeQuery();
			while (rs.next()) {
				suggestions.addElement(populate(rs));
			} 
			prepStmt.close();
			manager.freeConnection("general", conn);
			
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}		
		
		return suggestions;
	}
	
	private Suggestion populate(ResultSet rs) throws SQLException {
			Suggestion suggest = new Suggestion();
			suggest.setCode((rs.getString("code")));
			suggest.setTask((rs.getString("task")));			
			return suggest;
	}
		
	private boolean noRepeat(){
		int flag = 0;
		String tempSuggestedTask = suggestedTask.replaceAll(" ", "");
		String elementString = null;
		for (int a = 0; a < checkDuplicate.size(); a++){
			elementString = (((Suggestion)(checkDuplicate.elementAt(a))).getTask());
			elementString = elementString.replaceAll(" ", "");		
		
			if (tempSuggestedTask.equalsIgnoreCase(elementString)){
				flag = 1;
			}		
		}
		
		if(flag == 1)
			return false;
		else
			return true;
	}	
}