package dao;

import java.sql.CallableStatement;
import java.sql.Connection;
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.hibernate.jdbc.ReturningWork;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.springframework.stereotype.Repository;

import bean.AppVariables;
import common.dao.GenericDAO;
import common.dao.IDAO;
import sicura.entities.VdelegatoFirmaEstrattoreDistinte;
import sicura.entities.VdelegatoFirmaEstrattoreDistinteId;

@Repository
public class VdelegatoFirmaEstrattoreDistinteDao extends GenericDAO<VdelegatoFirmaEstrattoreDistinte, Integer> implements IDAO<VdelegatoFirmaEstrattoreDistinte, Integer> {

	public List<VdelegatoFirmaEstrattoreDistinteId> list() {
		
		boolean tx = startTransaction();
		try {
			String sql = "Select * from vDelegatoFirmaEstrattoreDistinte order by importoLimiteFirma";
			
			SQLQuery q = currentSession().createSQLQuery(sql);
			q.setResultTransformer(new AliasToBeanResultTransformer(VdelegatoFirmaEstrattoreDistinteId.class));
			q
				.addScalar("abilitaDelegatoFirmaEstrattoreDistinte")
				.addScalar("denominazione")
				.addScalar("funzioneOrdinante")
				.addScalar("importoLimiteFirma")
				.addScalar("id")
				.addScalar("dbgridSelect")
				.addScalar("emailResponsabile")
				.addScalar("userId");
			
			List<VdelegatoFirmaEstrattoreDistinteId> res = q.list();
			
			return res;
		} finally {
			if (tx)
		    	commit();
		}
	}

	public int setActive(Integer id, Boolean status, AppVariables appVariables) {
		boolean tx = startTransaction();

		try {
			int res = -1;
			Connection connection = getConnection();
	
				/*
				  CREATE PROCEDURE spAbilitaDelegatoFirmaEstrattoreDistinte
						@SecurityUserId VARCHAR(15),
						@SecurityGuid UNIQUEIDENTIFIER,
						
						@ID INT,
						@Abilitato BIT
				 */
			CallableStatement proc;
			try {
				proc = connection.prepareCall("{? = call spAbilitaDelegatoFirmaEstrattoreDistinte (?,?,?,?)}");
					
				proc.registerOutParameter(1, Types.INTEGER);
				proc.setString(2, appVariables.getUserId());
				proc.setString(3, appVariables.getGuid());
				proc.setObject(4, id);
				proc.setBoolean(5, getBoolean(status));
	
				proc.execute();
					
				res = proc.getInt(1);
					
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				res = -2;
			}
			return res;
		} finally {			
			if (tx)
				commit();
		}
	}
		
	
}
