package dao;

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

import javax.persistence.ParameterMode;

import org.apache.log4j.Logger;
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.procedure.ProcedureCall;
import org.hibernate.result.Outputs;
import org.hibernate.result.UpdateCountOutput;
import org.springframework.stereotype.Repository;
import org.springframework.util.MultiValueMap;

import bean.AppVariables;
import common.dao.GenericDAO;
import common.dao.IDAO;
import sicura.entities.ContiDestinatari;
import sicura.entities.Ordinanti;

@Repository
public class OrdinantiDao extends GenericDAO<Ordinanti, Integer> implements IDAO<Ordinanti, Integer> {

	public List<Ordinanti> list(Integer startFrom, Integer length, String filter, String orderField) {
		boolean tx = startTransaction();
		try {
			Criteria crit = currentSession().createCriteria(Ordinanti.class);
			crit
				.setFirstResult(startFrom)
				.setMaxResults(length);
			
			if (filter!=null)
				crit.add(Restrictions.ilike("ordinante", "%"+filter+"%"));
			
			if (orderField==null || "".equals(orderField))
				crit.addOrder(Order.asc("id"));
			else
				crit.addOrder(getOrderBy(orderField));
			
			List<Ordinanti> res = null;
			
			try {
				res = crit.list();
			} catch(Exception ex) {
				ex.printStackTrace();
			}
			
			return res;
		} finally {
			if (tx)
				tx = commit();
		}
	}
	
	public Integer count(String filter) {
		boolean tx = startTransaction();
		try {
			Criteria crit = currentSession().createCriteria(Ordinanti.class);

			if (filter!=null)
				crit.add(Restrictions.ilike("ordinante", "%"+filter+"%"));

			crit
				.setProjection(Projections.rowCount());			
			
			Integer res = ((Long) crit.uniqueResult()).intValue();
			
			return res;
		} finally {
			if (tx)
				tx = commit();
		}
	}

	public Integer save(Ordinanti ordinante,AppVariables appVariables) throws Exception {
		
		/*
		 CREATE PROCEDURE [dbo].[spModifyOrdinanti]
			@SecurityUserId VARCHAR(15),
			@SecurityGuid UNIQUEIDENTIFIER,
		
			@IDOrdinante	int,
			@Ordinante	varchar(96),
			@CodiceSocietà	varchar(3),
			@Indirizzo	varchar(30),
			@Località	varchar(30),
			@Comune	varchar(30),
			@Cap	varchar(5),
			@Provincia	varchar(2),
			@Abi	varchar(5),
			@Cab	varchar(5),
			@Conto	varchar(12),
			@CodiceFiscale	varchar(16),
			@CodiceSia	varchar(5),
			@CodiceSiaRIDutenze	varchar(5),
			@Firma_Emittente	varchar(20),
			@TipoRiferimentoCreditore	varchar,
			@CounterRiferimentoCreditore	varchar(10),
			@ChiaveProfili	varchar(120),
			@FlagAggiornaDistintadaArchivio	bit,
			@FlagAggiornaArchiviodaDistinta	bit,
			@FlagStampa	bit,
			@FlagNuoviEsiti	bit,
			@IdentificativoCreditore varchar(35),
			@Codice_CUC_Ordinante varchar(35),
			@LastSaved	timestamp
		 */
		
		boolean tx = startTransaction();
		Integer res = null;

		byte[] lastSaved;
		
		try {
			
			try {
				try {
					
					 if (ordinante.getIdordinante()==null || ordinante.getIdordinante()==0) {
						Integer id=addOrdinante(ordinante, appVariables);
						ordinante.setIdordinante(id);
						Ordinanti newOrd = readNoTransaction(id);
						ordinante.setLastSaved(newOrd.getLastSaved());
						lastSaved = newOrd.getLastSaved();
					} else
						lastSaved = ordinante.getLastSaved();

					 				 
					Connection connection = getConnection();
					
					CallableStatement proc;

					proc = connection.prepareCall("{? = call spModifyOrdinanti (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
						
					proc.registerOutParameter(1, Types.VARCHAR);
					proc.setString(2, appVariables.getUserId());
					proc.setString(3, appVariables.getGuid());	
					proc.setObject(4, ordinante.getIdordinante());
					proc.setString(5, ordinante.getOrdinante());
					proc.setString(6, ordinante.getCodiceSocietà());
					proc.setString(7, ordinante.getIndirizzo());
					proc.setString(8, ordinante.getLocalità());
					proc.setString(9, ordinante.getComune());
					proc.setString(10, ordinante.getCap());
					proc.setString(11, ordinante.getProvincia());
					proc.setString(12, ordinante.getAbi());
					proc.setString(13, ordinante.getCab());
					proc.setString(14, ordinante.getConto());
					proc.setString(15, ordinante.getCodiceFiscale());
					proc.setString(16, ordinante.getCodiceSia());
					proc.setString(17, ordinante.getCodiceSiaRidutenze());
					proc.setString(18, ordinante.getFirmaEmittente());
					proc.setString(19, ordinante.getTipoRiferimentoCreditore());
					proc.setString(20, ordinante.getCounterRiferimentoCreditore());
					proc.setString(21, ordinante.getChiaveProfili());
					proc.setBoolean(22, ordinante.isFlagAggiornaDistintadaArchivio());
					proc.setBoolean(23, ordinante.isFlagAggiornaArchiviodaDistinta());
					proc.setBoolean(24, ordinante.isFlagStampa());
					proc.setBoolean(25, ordinante.isFlagNuoviEsiti());
					proc.setString(26, ordinante.getIdentificativoCreditore());
					proc.setString(27, ordinante.getCodiceCucOrdinante());
					proc.setString(28, ordinante.getCodiceSocietaBancoPosta());
					proc.setBytes(29, lastSaved);
					
					
					proc.execute();
					proc.getMoreResults();
						
					res = proc.getInt(1);					
					
				} catch (SQLException ex) {
					Logger.getRootLogger().error ("Exception in OrdinantiDao.save: "+ex);
					ex.printStackTrace();
					throw ex;
				}
				return res;
			} finally {
			}		
					
		
		} finally {
			if (tx) {
				commit();
				Logger.getRootLogger().error ("OrdinantiDao.save: Transaction committed: ");
			}
		}
	}
	private Integer addOrdinante(Ordinanti ordinante, AppVariables appVariables) throws SQLException {
	
		/*
		 CREATE PROCEDURE [dbo].[spAddOrdinanti]
				@SecurityUserId VARCHAR(15),
				@SecurityGuid UNIQUEIDENTIFIER
		 */
		
		Connection connection = getConnection();
		
		CallableStatement proc;
		proc = connection.prepareCall("{? = call spAddOrdinanti (?,?)}");
		
		proc.registerOutParameter(1, Types.VARCHAR);
		proc.setString(2, appVariables.getUserId());
		proc.setString(3, appVariables.getGuid());	
		
		
		proc.execute();
		proc.getMoreResults();
			
		int res = proc.getInt(1);
		
//		commit();
//		startTransaction();
//		
		return res;
	}

	public Integer delete(Integer idOrdinante,AppVariables appVariables) throws Exception {
		/*
		 CREATE PROCEDURE spDeleteOrdinanti
			@SecurityUserId VARCHAR(15),
			@SecurityGuid UNIQUEIDENTIFIER,
			
			@IDOrdinante INT
		 */
		
		boolean tx = startTransaction();
		Integer res = null;

		try {
			
			try {
				Connection connection = getConnection();
				
				CallableStatement proc;
				try {
					proc = connection.prepareCall("{? = call spDeleteOrdinanti (?,?,?)}");
						
					proc.registerOutParameter(1, Types.VARCHAR);
					proc.setString(2, appVariables.getUserId());
					proc.setString(3, appVariables.getGuid());	
					proc.setObject(4, idOrdinante);
					proc.execute();
					proc.getMoreResults();
						
					res = proc.getInt(1);
						
					
				} catch (SQLException ex) {
					throw ex;
				}
				return res;
			} finally {
			}		
					
		
		} finally {
			if (tx)
				commit();
		}				
					
	}
	
}
