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.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Repository;

import antlr.StringUtils;
import bean.AppVariables;
import bean.filters.RicercaBancaFilterBean;
import common.dao.GenericDAO;
import common.dao.IDAO;
import common.utility.StringUtilities;
import sicura.entities.ApCab;

@Repository
public class ApCabDao extends GenericDAO<ApCab, Integer> implements IDAO<ApCab, Integer> {

	public boolean existsAbiCab(String abi, String cab, boolean useTransactions) {
		
		boolean tx=false;
		if(useTransactions)
			tx = startTransaction();
		try {
			Criteria crit = currentSession().createCriteria(ApCab.class);
			crit
				.add(Restrictions.eq("abi", abi))
				.add(Restrictions.eq("cab", cab))
				.setProjection(Projections.id())
		    	.setMaxResults(1);
			
		    boolean res = crit.uniqueResult() != null;
		    return res;
		} finally {		    
			if (useTransactions)
				if (tx)
					commit();
		}
	}
	
	public boolean existsAbi(String abi, boolean useTransactions) {
		
		boolean tx = false;
		
		if (useTransactions)
			tx = startTransaction();
		try {
			Criteria crit = currentSession().createCriteria(ApCab.class);
			crit
				.add(Restrictions.eq("abi", abi))
				.setProjection(Projections.id())
		    	.setMaxResults(1);
			
		    boolean res = crit.uniqueResult() != null;
		    return res;
		}finally {		
			if (useTransactions)
				if (tx)
					commit();
		}
	}

	public boolean existsAbi(String abi) {
		return existsAbi(abi, true);
	}

	
	public List<ApCab> filterFiliale(RicercaBancaFilterBean filterBean) {
		
		boolean tx = startTransaction();
		try {
			String sql = "Select ap_cab.* FROM Ap_Abi LEFT JOIN Ap_Cab ON Ap_Abi.Abi = Ap_Cab.Abi where 1=1 ";
			
			if (!StringUtilities.empty(filterBean.getAbi()))
				sql += " and Ap_Cab.Abi='" + filterBean.getAbi() + "'";
			if (!StringUtilities.empty(filterBean.getCab()))
				sql += " and Ap_Cab.Cab='" + filterBean.getCab() + "'";
				
			if (!StringUtilities.empty(filterBean.getAgenzia()))
				sql += " and agenzia like '%" + filterBean.getAgenzia() + "%'";
			if (!StringUtilities.empty(filterBean.getIndirizzo()))
				sql += " and indir like '%" + filterBean.getIndirizzo() + "%'";
			
			if (!StringUtilities.empty(filterBean.getComune()))
				switch (filterBean.getTipoRicerca()) {
				case 0:
					sql += " and comune ='" + filterBean.getComune()+"'";
					break;
				case 1:
					sql +=  " and comune like '%" + filterBean.getComune()+"%'";
					break;
				case 2:
					sql +=  " and comune like '" + filterBean.getComune()+"%'";
					break;
				}
			if (!StringUtilities.empty(filterBean.getLocalita()))
				sql += " and localita like '%" + filterBean.getLocalita() + "%'";
			if (!StringUtilities.empty(filterBean.getProvincia()))
				sql += " and prov like '%" + filterBean.getProvincia() + "%'";
			if (!StringUtilities.empty(filterBean.getCap()))
				sql += " and cap like '%" + filterBean.getCap() + "%'";
			
			if (StringUtilities.empty(filterBean.getAbi()))
				sql += " order by descri";
			else
				sql += " order by comune";
			
			SQLQuery q = currentSession().createSQLQuery(sql);
			q.addEntity(ApCab.class);
			
			List<ApCab> res = q.list();
			return res;
		} finally {			
			if (tx)
				commit();
		}			
	}

	public String saveFiliale(ApCab filiale, AppVariables appVariables) {
		boolean tx = startTransaction();
		String res = "";
		
		try {
			
			/*
			 CREATE PROCEDURE [dbo].[spInsertAP_CAB]
				@SecurityUserId VARCHAR(15),
				@SecurityGuid UNIQUEIDENTIFIER,
			
				@ID int,
				@ABI varchar(5),
				@CAB varchar(5),
				@AGENZIA varchar(40),
				@INDIR varchar(80),
				@LOCALITA varchar(40),
				@COMUNE varchar(40),
				@PROV varchar(2),
				@CAP varchar(5)
			 */
			
			CallableStatement proc;
			try {
				proc = getConnection().prepareCall("{? = call spInsertAP_CAB (?,?,?,?,?,?,?,?,?,?,?)}");
					
				proc.registerOutParameter(1, Types.INTEGER);
				proc.setString(2, appVariables.getUserId());
				proc.setString(3, appVariables.getGuid());
				proc.setObject(4, 0); //filiale.getId() -> Null Exception se null, ma in ogni caso non viene usato
				proc.setString(5, filiale.getAbi());
				proc.setString(6, filiale.getCab());
				proc.setString(7,  filiale.getAgenzia());
				proc.setString(8, filiale.getIndir());
				proc.setString(9, filiale.getLocalita());
				proc.setString(10, filiale.getComune());
				proc.setString(11, filiale.getProv());
				proc.setString(12, filiale.getCap());
						
				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;
	}

	public String deleteFiliale(Integer idFiliale, AppVariables appVariables) {
		boolean tx = startTransaction();
		String res = "";
		
		try {
			
			ApCab filiale= readNoTransaction(idFiliale);
			if (filiale == null)
				return "Filiale non trovata";
			
			/*
			 CREATE PROCEDURE spDeleteAP_CAB
				@SecurityUserId VARCHAR(15),
				@SecurityGuid UNIQUEIDENTIFIER,
			
				@ABI varchar(5),
				@CAB varchar(5)
			 */
			
			CallableStatement proc;
			try {
				proc = getConnection().prepareCall("{? = call spDeleteAP_CAB (?,?,?,?)}");
					
				proc.registerOutParameter(1, Types.INTEGER);
				proc.setString(2, appVariables.getUserId());
				proc.setString(3, appVariables.getGuid());
				proc.setString(4, filiale.getAbi());
				proc.setString(5, filiale.getCab());
						
				proc.execute();
					
				int spRes = proc.getInt(1);
					
				res = "";
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				res = e.getMessage();
			}
		
			return res;	
		
		} finally {
			if (tx)
				tx=commit();
		}
	}
	
}

