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.TabellaFirmeBean;
import common.dao.GenericDAO;
import common.dao.IDAO;
import sicura.entities.DistintaFlussiInputRequiresFirmatario;

@Repository
public class FirmaDao extends GenericDAO<DistintaFlussiInputRequiresFirmatario, Integer> implements IDAO<DistintaFlussiInputRequiresFirmatario, Integer> {

	public List<TabellaFirmeBean> list(){
		boolean tx = startTransaction();
		try {
			String sql = "Select id,tipologia,ltrim(rtrim(prefissoFlussoInput)) prefissoFlussoInput,attivaRequiresFirmatario,ltrim(rtrim(cast(Descrizione as varchar(2048)))) as descrizione from DistintaFlussiInput_RequiresFirmatario";
			SQLQuery q = currentSession().createSQLQuery(sql);
			q.setResultTransformer(new AliasToBeanResultTransformer(TabellaFirmeBean.class));
			q
				.addScalar("id")
				.addScalar("tipologia")
				.addScalar("prefissoFlussoInput")
				.addScalar("attivaRequiresFirmatario")
				.addScalar("descrizione");
			
			List<TabellaFirmeBean> res = q.list();
			
			return res;
		} finally {
			if (tx)
				tx = commit();
		}
	}

	public Integer count(){
		boolean tx = startTransaction();
		try {
			String sql = "Select count(*) from DistintaFlussiInput_RequiresFirmatario";
			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_RequiresFirmatario_delete]
							@SecurityUserId VARCHAR(15),
							@SecurityGuid UNIQUEIDENTIFIER,
						
							@ID int
				 */
				Integer res = null;
				Connection connection = getConnection();
				CallableStatement proc;
				try {
					proc = connection.prepareCall("{? = call spUCBase_DistintaFlussiInput_RequiresFirmatario_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) {
					e.printStackTrace();
					return null;
				}
				
				return res;

				
			} finally {
				if (tx)
					tx = commit();
			}
			
//		boolean tx = startTransaction();
//		try {
//			String sql = "delete from DistintaFlussiInput_RequiresFirmatario where id=:id";
//			
//			SQLQuery q = currentSession().createSQLQuery(sql);
//			q.setParameter("id", id);
//			
//			q.executeUpdate();
//			
//		} finally {
//			if (tx)
//				tx = commit();
//		}
	}

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

	}

}
