package dao;

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

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

import bean.AppVariables;
import bean.query.TabellaFlussiNonSedaBean;
import common.dao.GenericDAO;
import common.dao.IDAO;
import sicura.entities.DistintaFlussiInputNotSeda;

@Repository
public class DistintaFlussiInputNotSedaDAO extends GenericDAO<DistintaFlussiInputNotSeda, Integer> implements IDAO<DistintaFlussiInputNotSeda, Integer> {

	public List<TabellaFlussiNonSedaBean> list(){
		boolean tx = startTransaction();
		try {
			String sql = "Select id,tipologia,ltrim(rtrim(NomeSupporto)) nomeSupporto,nonAderenteSEDA,ltrim(rtrim(cast(Descrizione as varchar(2048)))) as descrizione from DistintaFlussiInput_Not_SEDA";
			SQLQuery q = currentSession().createSQLQuery(sql);
			q.setResultTransformer(new AliasToBeanResultTransformer(TabellaFlussiNonSedaBean.class));
			q
				.addScalar("id")
				.addScalar("tipologia")
				.addScalar("nomeSupporto")
				.addScalar("nonAderenteSeda")
				.addScalar("descrizione");
			
			List<TabellaFlussiNonSedaBean> res = q.list();

			return res;
		} finally {
			if (tx)
				tx = commit();
		}			
	}

	public Integer count(){
		boolean tx = startTransaction();
		try {
			String sql = "Select count(*) from DistintaFlussiInput_Not_SEDA";
			SQLQuery q = currentSession().createSQLQuery(sql);
			
			Integer res = (Integer) q.uniqueResult();
			
			return res;
		} finally {
			if (tx)
				tx = commit();
		}
	}
	
	public Integer delete(Integer id, AppVariables appVariables) {
		boolean tx = startTransaction();
		try {
			
			/*
				CREATE PROC [dbo].[spUCBase_DistintaFlussiInput_Not_SEDA_delete]
					@SecurityUserId VARCHAR(15),
					@SecurityGuid UNIQUEIDENTIFIER,
					@ID int
			 */
			Integer res = null;
			Connection connection = getConnection();
			CallableStatement proc;
			try {
				proc = connection.prepareCall("{? = call spUCBase_DistintaFlussiInput_Not_SEDA_delete (?,?,?)}");
					
				proc.registerOutParameter(1, Types.VARCHAR);
				proc.setString(2, appVariables.getUserId());
				proc.setString(3, appVariables.getGuid());
				proc.setObject(4, id);
				
				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();
		}
	}

	public Integer save(TabellaFlussiNonSedaBean tbl, AppVariables appVariables) throws Exception {
		boolean tx = startTransaction();
		try {
			Integer res = null;
			Connection connection = getConnection();
	
			/*
			  CREATE PROC [dbo].[spUCBase_DistintaFlussiInput_Not_SEDA_update]
				@SecurityUserId VARCHAR(15),
				@SecurityGuid UNIQUEIDENTIFIER,
			
				@ID int,
				@Tipologia varchar(20),
				@NomeSupporto varchar(20),
				@NonAderenteSEDA BIT,
				@Descrizione varchar(Max)
			 */
			CallableStatement proc;
			try {
				proc = connection.prepareCall("{? = call spUCBase_DistintaFlussiInput_Not_SEDA_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.getNomeSupporto());
				proc.setBoolean(7, tbl.isNonAderenteSeda());
				proc.setString(8, tbl.getDescrizione());
				
				proc.execute();
				proc.getMoreResults();
					
				res = proc.getInt(1);
					
				
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}
			
			return res;
		} finally {
			if (tx)
				tx = commit();
		}		
	}

}
