package dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import bean.AppVariables;
import bean.ParamBean;
import common.dao.GenericDAO;
import common.dao.IDAO;
import sicura.entities.Destinatari;

@Repository
public class ParamDAO extends GenericDAO<ParamBean, String> implements IDAO<ParamBean, String> {
	
	private final String K_PATH_EXPORT_TASC = "PathExportDistinteTasc";
	private final String K_PATH_EXPORT_REMUNEARATION_SEDA = "PathExportRemunerationSeda";
	private final String K_EMAIL_ALERT = "@mailServiceTo";
	private final String K_EMAIL_ERRORS = "@mailServiceToWithLogErrori";
	private final String K_ORE_GIACENZA = "@OreGiacenzaDistEmesse";
	
	public ParamBean getParams(AppVariables appVariables) {
		ParamBean params = new ParamBean();
		
		boolean tx = startTransaction();
		try {
			params.setPathExportTASC(getAppParam(K_PATH_EXPORT_TASC, appVariables));
			params.setPathExportRemunerationSEDA(getAppParam(K_PATH_EXPORT_REMUNEARATION_SEDA, appVariables));
			params.setEmailAlert(getAppParam(K_EMAIL_ALERT, appVariables));
			params.setEmailToServiceWithLogErrori(getAppParam(K_EMAIL_ERRORS, appVariables));
			try {
				params.setOreGiacenzaDistEmesse(Integer.parseInt(getAppParam(K_ORE_GIACENZA, appVariables)));
			} catch(NumberFormatException ex) {
				params.setOreGiacenzaDistEmesse(null);
			}

		} catch (Exception e) {
			Logger.getRootLogger().error("Errore in lettura parametri applicazione: ", e);
			e.printStackTrace();
			
			tx = false;
			rollback();
		} finally {
			if (tx)
				commit();
		}
		return params;		
	}
	
	public String setParams (ParamBean params, AppVariables appVariables) {
		boolean tx = startTransaction();
		String res = null;
		try {
			res = setAppParam(K_PATH_EXPORT_TASC, params.getPathExportTASC(), appVariables);
			//if (res!=null)
				res = setAppParam(K_PATH_EXPORT_REMUNEARATION_SEDA, params.getPathExportRemunerationSEDA(), appVariables);
			//if (res!=null)
				res = setAppParam(K_EMAIL_ALERT, params.getEmailAlert(), appVariables);
			//if (res!=null)
				res = setAppParam(K_EMAIL_ERRORS, params.getEmailToServiceWithLogErrori(), appVariables);
			//if (res!=null)
				res = setAppParam(K_ORE_GIACENZA, params.getOreGiacenzaDistEmesse().toString(), appVariables);			
			
		} catch (Exception e) {
			Logger.getRootLogger().error("Errore in salvataggio parametri applicazione: ", e);
			e.printStackTrace();
			
			tx = false;
			rollback();
		} finally {
			if (tx)
				commit();
		}
		
		return res;
	}
	

	private String getAppParam (String nomeParametro, AppVariables appVariables) {
		//boolean tx = startTransaction();
		
		try {
			String res = null;
			Connection connection = getConnection();
	
			/*
			  CREATE PROC [dbo].[spGetAppParam]
				@SecurityUserId VARCHAR(15),
				@SecurityGuid UNIQUEIDENTIFIER,
				@Parametro varchar(50)
			 */
			CallableStatement proc;
			try {
				proc = connection.prepareCall("{? = call spGetAppParam (?,?,?)}");
					
				proc.registerOutParameter(1, Types.VARCHAR);
				proc.setString(2, appVariables.getUserId());
				proc.setString(3, appVariables.getGuid());
				proc.setString(4, nomeParametro);
				
				proc.execute();
				
				ResultSet rs = proc.getResultSet();
				rs.next();
				res = rs.getString(1);
//				proc.getMoreResults();
//					
//				res = proc.getString(1);
					
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
			
			return res;
		} finally {
//			if (tx)
//				tx = commit();
		}
	}
	
	private String setAppParam(String paramName, String paramValue,AppVariables appVariables) throws SQLException {
		/*
 			CREATE PROC [dbo].[spUCBase_AppParam_update]
				@SecurityUserId VARCHAR(15),
				@SecurityGuid UNIQUEIDENTIFIER,
		
				@Parametro varchar(50),
				@Valore varchar(200)
		*/
		
		String res = null;
		Connection connection = getConnection();
		
		CallableStatement proc;
		proc = connection.prepareCall("{? = call spUCBase_AppParam_update (?,?,?,?)}");
		
		proc.registerOutParameter(1, Types.VARCHAR);
		proc.setString(2, appVariables.getUserId());
		proc.setString(3, appVariables.getGuid());	
		proc.setString(4, paramName);
		proc.setString(5, paramValue);
		
		
		boolean bres = proc.execute();
		if (bres)
			res = "OK";
		if (proc.getMoreResults())
			res = proc.getString(1);
			
		return res;
	}
	
	
}
