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 org.springframework.util.MultiValueMap;

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

@Repository
public class ContiDestinatariDao extends GenericDAO<ContiDestinatari, Integer> implements IDAO<ContiDestinatari, Integer> {
	
	
	public List<ContiDestinatari> listForDestinatario(Integer idDestinatario, Integer startFrom, Integer length, String orderField) {
		boolean tx = startTransaction();
		try {
			Criteria crit = currentSession().createCriteria(ContiDestinatari.class);
			crit
				.add(Restrictions.eq("iddestinatario", idDestinatario))
				.setFirstResult(startFrom)
				.setMaxResults(length);
			
			if (orderField==null || "".equals(orderField))
				crit.addOrder(Order.asc("id"));
			else
				crit.addOrder(getOrderBy(orderField));
			
			try {
				List<ContiDestinatari> res = crit.list(); 
				return res;
			} catch (Exception ex) {
				ex.printStackTrace();
				return null;
			}		
			

		} finally {
			if (tx)
				tx=commit();
		}			
	}
	
	public Integer countForDestinatario(Integer idDestinatario) {
		boolean tx = startTransaction();
		try {
			Criteria crit = currentSession().createCriteria(ContiDestinatari.class);
			crit
				.add(Restrictions.eq("iddestinatario", idDestinatario))
				.setProjection(Projections.rowCount());			
			
			Integer res = ((Long) crit.uniqueResult()).intValue();
			
			return res;
		}finally {
			if (tx)
				tx=commit();
		}
	}
	
	public Integer save(ContiDestinatari conto, AppVariables appVariables) throws Exception {
		
		/*
		 CREATE PROCEDURE [dbo].[spAddContiDestinatari]
			@SecurityUserId VARCHAR(15),
			@SecurityGuid UNIQUEIDENTIFIER,
		
			@IDOrdinante  int,
			@IDDestinatario int 
		  
		  CREATE PROCEDURE [dbo].[spModifyContiDestinatari]
			@SecurityUserId VARCHAR(15),
			@SecurityGuid UNIQUEIDENTIFIER,
		
			@ID			int,
			@IDOrdinante		int,
			@IDDestinatario		int,
			@Abi			varchar(5),
			@Cab			varchar(5),
			@Conto			varchar(12),
			@Cin			varchar(1),
			@CoordinateIBAN	varchar(42),
			@CodiceBIC		varchar(11),
			@DescrizioneBanca	varchar(50),
			@IntestatarioConto	varchar(50),
			@CodiceFiscaleSottoscrittoreRIA	varchar(16),
			@Conto_Default		bit,
			@LastSaved	timestamp
		 */
		
		
		
		boolean tx = startTransaction();
		try {
			Integer res = null;
			Connection connection = getConnection();
	
			CallableStatement proc;
			try {
				if (conto.getId()==null || conto.getId()==0) {
					proc = connection.prepareCall("{? = call spAddContiDestinatari (?,?,?,?)}");
					proc.registerOutParameter(1, Types.INTEGER);
					proc.setString(2, appVariables.getUserId());
					proc.setString(3, appVariables.getGuid());
					proc.setObject(4, conto.getId());
					proc.setObject(5, conto.getIdordinante());
					
					proc.execute();
					proc.getMoreResults();
						
					res = proc.getInt(1);
					if (res>0) {
						conto.setId(res);
						ContiDestinatari contoTest = readNoTransaction(conto.getId());
						conto.setLastSaved(contoTest.getLastSaved());
					} else {
						res = -99;
					}
				}
				
				if (conto.getId()!=null && conto.getId()>0) {
						proc = connection.prepareCall("{? = call spModifyContiDestinatari (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
							
						proc.registerOutParameter(1, Types.VARCHAR);
						proc.setString(2, appVariables.getUserId());
						proc.setString(3, appVariables.getGuid());
						proc.setObject(4, conto.getId());
						proc.setObject(5, conto.getIdordinante());
						proc.setObject(6, conto.getIddestinatario());
						proc.setString(7, conto.getAbi());
						proc.setString(8, conto.getCab());
						proc.setString(9, conto.getConto());
						proc.setString(10, conto.getCin());
						proc.setString(11, conto.getCoordinateIban());
						proc.setString(12, conto.getCodiceBic());
						proc.setString(13, conto.getDescrizioneBanca());
						proc.setString(14, conto.getIntestatarioConto());
						proc.setString(15, conto.getCodiceFiscaleSottoscrittoreRia());
						proc.setBoolean(16, conto.isContoDefault());
						proc.setBytes(17,conto.getLastSaved());
						
						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 delete (Integer id, AppVariables appVariables) {
		/*
		 * CREATE PROCEDURE [dbo].[spDeleteContiDestinatari]
			@SecurityUserId VARCHAR(15),
			@SecurityGuid UNIQUEIDENTIFIER,
		
			@ID		int
		 */
		boolean tx = startTransaction();
		try {
			Integer res = null;
			Connection connection = getConnection();
			CallableStatement proc;
			try {
				proc = connection.prepareCall("{? = call spDeleteContiDestinatari (?,?,?)}");
					
				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();
		}
	}

}
