package dao;

import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Repository;

import bean.AppVariables;
import common.dao.GenericDAO;
import common.dao.IDAO;
import sicura.entities.ApAbi;

@Repository
public class ApAbiDao extends GenericDAO<ApAbi, Integer> implements IDAO<ApAbi, Integer> {

	public List<ApAbi> filterIstituto(String filtro, Integer maxResult) {
		
		boolean tx = startTransaction();
		try {
			Criteria crit = currentSession().createCriteria(ApAbi.class);
			crit
				.add(Restrictions.ilike("descri", "%"+filtro+"%"))
				.addOrder(Order.asc("descri"))
				.setMaxResults(maxResult);
		    	
			
			List<ApAbi> res = crit.list();
		    
		    
		    return res;
		} finally {
		    if (tx)
		    	commit();
		}
	}

	public String saveBanca(ApAbi banca,AppVariables appVariables) {
		String res = "";
		boolean tx = startTransaction();
		try {
		
			if (banca.getAbiNew()!=null && !"".equals(banca.getAbiNew()) && "1".equals(banca.getTipo())) {
				// check esistenza nuovo abi
				if (!abiEsiste(banca.getAbiNew()))
					return "Attenzione, la banca risulta assorbita ma il nuovo codice ABI non è presente in archivio";
			}
			
			// controlli superati, chiama la stored per salvare
			
			/*
			 	CREATE PROCEDURE [dbo].[spInsertAP_ABI]
					@SecurityUserId VARCHAR(15),
					@SecurityGuid UNIQUEIDENTIFIER,
				
					@ID int,
					@ABI VARCHAR(5),
					@DESCRI VARCHAR(80),
					@TIPO VARCHAR(1),
					@ABI_NEW VARCHAR(5)
			 */
			CallableStatement proc;
			try {
				proc = getConnection().prepareCall("{? = call spInsertAP_ABI (?,?,?,?,?,?,?)}");
					
				proc.registerOutParameter(1, Types.INTEGER);
				proc.setString(2, appVariables.getUserId());
				proc.setString(3, appVariables.getGuid());
				proc.setObject(4, (banca.getId()!=null ? banca.getId(): 0));
				proc.setString(5, banca.getAbi());
				proc.setString(6, banca.getDescri());
				proc.setString(7,  banca.getTipo());
				proc.setString(8, banca.getAbiNew());
				
				proc.execute();
					
				int spRes = proc.getInt(1);
					
				res = "";
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				res = e.getMessage();
			}
			
		
		}finally {
			if (tx)
				tx = commit();
		}
		
		return res;
	}

	private boolean abiEsiste(String newAbi) {
		SQLQuery q = currentSession().createSQLQuery("select count(*) from ap_abi where abi=:abi");
		q.setParameter("abi", newAbi);
		
		Integer res = (Integer) q.uniqueResult();
		
		return res>0;
	}

	public ApAbi getByAbi(String abi) {
		boolean tx = startTransaction();
		try {
			Criteria crit = currentSession().createCriteria(ApAbi.class);
			crit.add(Restrictions.eq("abi", abi));
			
			List<ApAbi> res = crit.list();
			
			if (res.size()>0)
				return res.get(0);
			else
				return null;
		} finally {
			if (tx)
				tx = commit();
		}
	}	
	
}
