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.Query;
import org.hibernate.SQLQuery;
import org.hibernate.jdbc.ReturningWork;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.springframework.stereotype.Repository;

import bean.AppVariables;
import bean.LogModificheBean;
import common.dao.GenericDAO;
import common.dao.IDAO;
import sicura.entities.DistintaFlussiInput;

@Repository
public class DistintaFlussiInputDAO extends GenericDAO<DistintaFlussiInput, Integer> implements IDAO<DistintaFlussiInput, Integer> {

	public Integer getFlussiCount(AppVariables appVariables) {
		boolean tx=startTransaction();
		try {
			String sql = generateQuery(appVariables, true);
			
			SQLQuery q = currentSession().createSQLQuery(sql);
			Integer res = (Integer)q.uniqueResult();
			
			return res;
		} finally {
			if (tx)
				tx = commit();
		}
	}
	
	public List<DistintaFlussiInput> getFlussi(AppVariables appVariables, Integer startFrom, Integer size, String orderField) {
		boolean tx=startTransaction();
		try {
			
			String sql = generateQuery(appVariables, false);
			if (orderField==null || "".equals(orderField))
				sql += " ORDER BY DataCreazione DESC, ID DESC";
			else
				sql += getSQLOrderBy(orderField);	
			
			SQLQuery q = currentSession().createSQLQuery(sql);
		
			q
				.addEntity(DistintaFlussiInput.class);
			if (startFrom!=null && size!=null)  { 
				q
					.setFirstResult(startFrom)
					.setMaxResults(size);
			}
	
			List<DistintaFlussiInput> res = null;
			try {
				res = q.list();
			} catch(Exception ex) {
				ex.printStackTrace();
			}
			
			return res;		
		} finally {
			if (tx)
				tx = commit();
		}
	}

	private String generateQuery(AppVariables appVariables, boolean doCount) {
		String sql;
		if ("UR".equalsIgnoreCase(appVariables.getProfiloLocale()) || "UO".equalsIgnoreCase(appVariables.getProfiloLocale())) {
			sql = String.format("Select %s from DistintaFlussiInput WHERE MassiveLoadStatus = 3 AND ( Tipologia='AEA' OR Tipologia='RID' )",
					(doCount? "count(*)" : "*"));
		} else {
			sql = String.format("Select %s from DistintaFlussiInput WHERE MassiveLoadStatus = 3",
					(doCount? "count(*)" : "*"));
		}
		return sql;
	}

	public String annullaFlusso(Integer id, AppVariables appVariables) {
		Integer res = -1;
		//boolean tx = true;

		boolean tx = startTransaction();
		Connection connection = getConnection();
		try {
			DistintaFlussiInput distinta = readNoTransaction(id);
			
	
				/*
				  CREATE PROCEDURE [dbo].[spAnnullaFlussiInput]
						@SecurityUserId VARCHAR(15),
						@SecurityGuid UNIQUEIDENTIFIER,
					
						@IDDistintaFlussoInput int,
						@LastSaved timestamp,
						@UserID varchar(10),
						@Utente varchar(50),
						@Tipologia varchar(20)
				 */
			CallableStatement proc;
			try {
				proc = connection.prepareCall("{? = call spAnnullaFlussiInput (?,?,?,?,?,?,?)}");
					
				proc.registerOutParameter(1, Types.INTEGER);
				proc.setString(2, appVariables.getUserId());
				proc.setString(3, appVariables.getGuid());
				proc.setObject(4, distinta.getIddistintaFlusso());
				proc.setBytes(5, distinta.getLastSaved());
				proc.setString(6, appVariables.getUserId());
				proc.setString(7,  appVariables.getDenominazioneUtente());
				proc.setString(8,  distinta.getTipologia());
	
				proc.execute();
				
				res = proc.getInt(1);
					
			} catch (SQLException e) {
				tx = rollback();
				e.printStackTrace();
				return e.getMessage();
			}
			
			return res.toString();
		} finally {
			if (tx)
				tx = commit();
		}
	}

	public List<LogModificheBean> logStati(Integer id) {
		boolean tx = startTransaction();
		try {
			String sql = "select id, userId, data, ora, utente, null as nomeCampo,null as campoPreModifica, cast(campoPostModifica as varchar) as campoPostModifica " +
						" from LogDistintaFlussiInput where IDDistintaFlusso=:id"+
						" order by Data, Ora, id";
					
			Query q = currentSession().createSQLQuery(sql);
			q.setResultTransformer(new AliasToBeanResultTransformer(LogModificheBean.class));
			
			q.setParameter("id", id);	
			@SuppressWarnings("unchecked")
			List<LogModificheBean> res = q.list();
			
			return res;
		} finally {
			if (tx)
				tx = commit();
		}
	}
}