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.springframework.stereotype.Repository;

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

@Repository
public class TipologiaDao extends GenericDAO<Tipologia, Integer> implements IDAO<Tipologia, Integer> {

	public List<Tipologia> list(){
		boolean tx = startTransaction();
		try {
			String sql = "Select * from Tipologia" ;// WHERE Tipologia = 'BONIFICI' OR Tipologia = 'RID' OR Tipologia = 'AEA' OR Tipologia = 'DOM' OR Tipologia = 'SCT_INT' OR Tipologia = 'SCT_INPS' OR Tipologia = 'SCT_ESTERI' OR Tipologia = 'ASSEGNI_INTESA'" ;
			SQLQuery q = currentSession().createSQLQuery(sql);
			q.addEntity(Tipologia.class);
			
			List<Tipologia> res = q.list();
			
			return res;
		} finally {
			if (tx)
				tx = rollback();
		}
	}

	public Integer count(){
		boolean tx = startTransaction();
		try {
			String sql = "Select count(*) from Tipologia"; // WHERE Tipologia = 'BONIFICI' OR Tipologia = 'RID' OR Tipologia = 'AEA' OR Tipologia = 'DOM' OR Tipologia = 'SCT_INT' OR Tipologia = 'SCT_INPS' OR Tipologia = 'ASSEGNI_INTESA'";
			SQLQuery q = currentSession().createSQLQuery(sql);
			
			Integer res = (Integer) q.uniqueResult();
			
			return res;
		} finally {
			if (tx)
				tx = commit();
		}
	}

	public Integer save(Tipologia tbl, AppVariables appVariables) {
		/*
		  CREATE PROC [dbo].[spUCBase_Tipologia_update]
			@SecurityUserId VARCHAR(15),
			@SecurityGuid UNIQUEIDENTIFIER,
		
			@ID int,
			@Tipologia varchar(20),
			@Descrizione varchar(50),
			@MIGeneraFlusso BIT,
			@OverrideTipoSequenzaRID varchar(4),
			@OverrideRichiestaEsitoSCT varchar(3)
		 */
		boolean tx = startTransaction();
		try {
			Integer res = null;
			Connection connection = getConnection();
	
			
			CallableStatement proc;
			try {
				proc = connection.prepareCall("{? = call spUCBase_Tipologia_update (?,?,?,?,?,?,?,?,?,?,?)}");
					
				proc.registerOutParameter(1, Types.VARCHAR);
				proc.setString(2, appVariables.getUserId());
				proc.setString(3, appVariables.getGuid());
				proc.setObject(4, tbl.getId());
				proc.setString(5, tbl.getTipologia());
				proc.setString(6, tbl.getDescrizione());
				proc.setBoolean(7, tbl.isMigeneraFlusso());
				proc.setString(8, tbl.getOverrideTipoSequenzaRid());
				proc.setString(9, tbl.getOverrideRichiestaEsitoSct());
				proc.setObject(10,tbl.getNumGiorniGenFlusso());
				proc.setBoolean(11, tbl.isSoloGiorniLavorativi());
				proc.setObject(12, dateToSqlTimestamp(tbl.getOraGenerazioneFlusso()));
				
				proc.execute();
				proc.getMoreResults();
					
				res = proc.getInt(1);
					
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
			
			return res;
		} finally {
			if (tx)
				tx = commit();
		}	
	}
	
	
	
}
