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

import bean.AppVariables;
import bean.CambioOrdinanteBean;
import bean.query.DestinatarioForPrinting;
import common.dao.GenericDAO;
import common.dao.IDAO;
import common.utility.StringUtilities;
import sicura.entities.Destinatari;
import sicura.entities.Ordinanti;

@Repository
public class DestinatariDao extends GenericDAO<Destinatari, Integer> implements IDAO<Destinatari, Integer> {
	
	
	public List<Destinatari> listForOrdinante(Integer idOrdinante, Integer startFrom, Integer length, String filter, String orderField) {
		List<Destinatari> res =null;
		boolean tx = startTransaction();
		try {
			Criteria crit = currentSession().createCriteria(Destinatari.class);
			crit
				.add(Restrictions.eq("idordinante", idOrdinante))
				.setFirstResult(startFrom)
				.setMaxResults(length);
			
			if (!StringUtilities.empty(filter)) {
				crit.add(Restrictions.or(
						Restrictions.ilike("destinatario", "%"+filter+"%"),
						Restrictions.ilike("codiceFiscale", "%"+filter+"%")
				));			
			}
			
			if (orderField==null || "".equals(orderField))
				crit.addOrder(Order.asc("id"));
			else
				crit.addOrder(getOrderBy(orderField));
			try {
				res = crit.list();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		} finally {
			if (tx)
				tx=commit();
		}
		return res;
	}
	public Integer countForOrdinante(Integer idOrdinante, String filter) {
		boolean tx = startTransaction();
		try {
			Criteria crit = currentSession().createCriteria(Destinatari.class);
			crit
				.add(Restrictions.eq("idordinante", idOrdinante))
				.setProjection(Projections.rowCount());			
			
			if (!StringUtilities.empty(filter)) {
				crit.add(Restrictions.or(
						Restrictions.ilike("destinatario", "%"+filter+"%"),
						Restrictions.ilike("codiceFiscale", "%"+filter+"%")
				));		
			}
	
			Integer res = ((Long) crit.uniqueResult()).intValue();
			return res;

		} finally {
			if (tx)
				tx=commit();
		}
	}
	public Integer save(Destinatari destinatario, AppVariables appVariables) throws Exception {
		/*
		 CREATE PROCEDURE [dbo].[spModifyDestinatario]
			@SecurityUserId VARCHAR(15),
			@SecurityGuid UNIQUEIDENTIFIER,
		
			@IDDestinatario	int,
			@IDOrdinante int,
			@Destinatario varchar(90),
			@CodiceFiscale varchar(16),
			@Indirizzo varchar(40),
			@Cap varchar(5),
			@Città varchar(25),
			@Provincia varchar(2),
			@CodiceDestinatario	varchar(16),
			@TipoCodiceDestinatario	varchar(1),
			@SIA varchar(5),
			@FlagStampa	bit,
			@FlagExportBollettinoPDF bit,
			@LastSaved timestamp,
			@FlagAEA bit,
			@UpdateFlags int,
			@BonificoHigPriority bit,
			@CoordinateIBANordinante varchar(42)
		 */
		
		boolean tx = startTransaction();
		Integer res = null;
		Integer updateFlags=1;

		try {
			
			try {
				Connection connection = getConnection();
				
				CallableStatement proc;
				try {
					
					if (destinatario.getIddestinatario()==null || destinatario.getIddestinatario()==0) {
						int id=creaDestinatario(destinatario,appVariables);
						destinatario.setIddestinatario(id);
						Destinatari newDestinatario=readNoTransaction(id);
						destinatario.setLastSaved(newDestinatario.getLastSaved());
						updateFlags = 0;
					}
				
					
					proc = connection.prepareCall("{? = call spModifyDestinatario (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
						
					proc.registerOutParameter(1, Types.VARCHAR);
					proc.setString(2, appVariables.getUserId());
					proc.setString(3, appVariables.getGuid());	
					proc.setObject(4, destinatario.getIddestinatario());
					proc.setObject(5, destinatario.getIdordinante());
					proc.setString(6, destinatario.getDestinatario());
					proc.setString(7, destinatario.getCodiceFiscale());
					proc.setString(8, destinatario.getIndirizzo());
					proc.setString(9, destinatario.getCap());
					proc.setString(10, destinatario.getCitta());
					proc.setString(11, destinatario.getProvincia());
					proc.setString(12, destinatario.getCodiceDestinatario());
					proc.setString(13, destinatario.getTipoCodiceDestinatario());
					proc.setString(14, destinatario.getSia());
					proc.setBoolean(15, destinatario.isFlagStampa());
					proc.setBoolean(16, destinatario.isFlagExportBollettinoPdf());
					proc.setBytes(17, destinatario.getLastSaved());
					proc.setBoolean(18, destinatario.isFlagAea());
					proc.setInt(19, updateFlags); // Updateflags
					proc.setBoolean(20, destinatario.isBonificoHigPriority());
					proc.setString(21, destinatario.getCoordinateIbanordinante());
					
					
					proc.execute();
					proc.getMoreResults();
						
					res = proc.getInt(1);
						
					
				} catch (SQLException ex) {
					throw ex;
				}
				return res;
			} finally {
			}		
					
		
		} finally {
			if (tx)
				commit();
		}	
		
	}
	
	private Integer creaDestinatario(Destinatari destinatario, AppVariables appVariables) throws SQLException {
		/*
		  CREATE PROCEDURE [dbo].[spAddDestinatario]
				@SecurityUserId VARCHAR(15),
				@SecurityGuid UNIQUEIDENTIFIER,
			
				@IDOrdinante  int,
				@Destinatario varchar(90),
				@CodiceFiscale varchar(16)
		 */
		Connection connection = getConnection();
		
		CallableStatement proc;
		proc = connection.prepareCall("{? = call spAddDestinatario (?,?,?,?,?)}");
		
		proc.registerOutParameter(1, Types.VARCHAR);
		proc.setString(2, appVariables.getUserId());
		proc.setString(3, appVariables.getGuid());	
		proc.setObject(4, destinatario.getIdordinante());
		proc.setString(5, destinatario.getDestinatario());
		proc.setString(6, destinatario.getCodiceFiscale());
		
		
		proc.execute();
		proc.getMoreResults();
			
		int res = proc.getInt(1);
		return res;
	}
	public void cambioOrdinante(CambioOrdinanteBean cambioOrdinanteBean, AppVariables appVariables) {
		boolean tx = startTransaction();
	
		/*
		 * CREATE PROCEDURE [dbo].[spCambiaOrdinante]
				@SecurityUserId VARCHAR(15),
				@SecurityGuid UNIQUEIDENTIFIER,
			
				@NEWIDOrdinante int,
				@mode	int
		 */
		

		try {
			Connection connection = getConnection(); 
			
			String sql = "";

			
//			sql = generateCreateTableSQL();

			sql += generateFillTableSQL();

			sql += String.format(" exec spCambiaOrdinante '%s', '%s', %d, %d",appVariables.getUserId(), appVariables.getGuid(), cambioOrdinanteBean.getOrdinanteDestinazione(), cambioOrdinanteBean.getTipoCambio());
			sql += " drop table #TempTabCambioOrdinante";
			
			SQLQuery q = currentSession().createSQLQuery(sql);
			q.setParameterList("idDestinatari", cambioOrdinanteBean.getDestinatari());
			q.executeUpdate();
			
/*			
			
			
			SQLQuery q = currentSession().createSQLQuery(generateFillTableSQL());
			//q.addEntity(Destinatari.class);
			q.setParameterList("idDestinatari", cambioOrdinanteBean.getDestinatari());
			q.executeUpdate();
//			if (tx)
//				tx = commit();
//			tx = startTransaction();

			


		
			CallableStatement proc;
			try {
				proc = connection.prepareCall("{? = call spCambiaOrdinante (?,?,?,?)}");
					
				proc.registerOutParameter(1, Types.INTEGER);
				proc.setString(2, appVariables.getUserId());
				proc.setString(3, appVariables.getGuid());
				proc.setObject(4, cambioOrdinanteBean.getOrdinanteDestinazione());
				proc.setObject(5, cambioOrdinanteBean.getTipoCambio());
				
				proc.execute();
					
				int res = proc.getInt(1);
*/				
			} catch(Exception ex) {
				ex.printStackTrace();
				if (tx)
					tx=rollback();
			}
		   finally {
			if (tx)
				tx=commit();
		}
			
	}
	
	private String generateCreateTableSQL() {
		String sql = " if OBJECT_ID('#TempTabCambioOrdinante') is null begin "
				 + "CREATE TABLE #TempTabCambioOrdinante ( "
				 + "[IDOrdinante] [int] NULL , "
				 + "[IDDestinatario] [int], "
				 + "[Destinatario] [varchar] (90), "
				 + "[CodiceFiscale] [varchar] (16), "
				 + "[Indirizzo] [varchar] (40), "
				 + "[Cap] [varchar] (5), "
				 + "[Città] [varchar] (25), "
				 + "[Provincia] [varchar] (2), "
				 + "[CodiceDestinatario] [varchar] (16), "
				 + "[TipoCodiceDestinatario] [varchar] (1), "
				 + "[SIA] [varchar] (5), "
				 + "[FlagStampa] [bit] NOT NULL , "
				 + "[FlagExportBollettinoPDF] [bit] NOT NULL , "
				 + "[FlagAEA] [bit] NOT NULL , "
				 + "[LastSaved] [Binary](8) , "
				 + "[BonificoHigPriority] [bit] NOT NULL , "
				 + "[CoordinateIBANordinante] [varchar] (42) "
				 + ") "
				 + "end ";
				
		return sql;
		
	}
	
	private String generateFillTableSQL() {
		String sql = 
//				"insert into #TempTabCambioOrdinante ( "
//				 + "[IDOrdinante] , "
//				 + "[IDDestinatario], "
//				 + "[Destinatario] , "
//				 + "[CodiceFiscale], "
//				 + "[Indirizzo], "
//				 + "[Cap], "
//				 + "[Città], "
//				 + "[Provincia], "
//				 + "[CodiceDestinatario], "
//				 + "[TipoCodiceDestinatario], "
//				 + "[SIA] , "
//				 + "[FlagStampa] , "
//				 + "[FlagExportBollettinoPDF], "
//				 + "[FlagAEA] , "
//				 + "[LastSaved] , "
//				 + "[BonificoHigPriority] , "
//				 + "[CoordinateIBANordinante] "
//				 + ")"
				 "select "
						 + "[IDOrdinante] , "
						 + "[IDDestinatario], "
						 + "[Destinatario] , "
						 + "[CodiceFiscale], "
						 + "[Indirizzo], "
						 + "[Cap], "
						 + "[Città], "
						 + "[Provincia], "
						 + "[CodiceDestinatario], "
						 + "[TipoCodiceDestinatario], "
						 + "[SIA] , "
						 + "[FlagStampa] , "
						 + "[FlagExportBollettinoPDF], "
						 + "[FlagAEA] , "
						 + "[LastSaved] , "
						 + "[BonificoHigPriority] , "
						 + "[CoordinateIBANordinante] "
						 + "  into #TempTabCambioOrdinante from destinatari where iddestinatario in (:idDestinatari) ";
				
		return sql;
		
	}
	public Integer delete(Integer idDestinatario, AppVariables appVariables) throws SQLException {
		/*
		 CREATE PROCEDURE [dbo].[spDeleteDestinatario]
			@SecurityUserId VARCHAR(15),
			@SecurityGuid UNIQUEIDENTIFIER,
		
			@IDDestinatario		int
		 */
		boolean tx = startTransaction();
		Integer res = null;

		try {
			
			try {
				Connection connection = getConnection();
				
				CallableStatement proc;
				try {
					proc = connection.prepareCall("{? = call spDeleteDestinatario (?,?,?)}");
						
					proc.registerOutParameter(1, Types.VARCHAR);
					proc.setString(2, appVariables.getUserId());
					proc.setString(3, appVariables.getGuid());	
					proc.setObject(4, idDestinatario);
					
					proc.execute();
					proc.getMoreResults();
						
					res = proc.getInt(1);
						
					
				} catch (SQLException ex) {
					throw ex;
				}
				return res;
			} finally {
			}		
					
		
		} finally {
			if (tx)
				commit();
		}	
	}
	
	public List<DestinatarioForPrinting> listForPrinting(Integer idOrdinante) {
		boolean tx = startTransaction();
		List<DestinatarioForPrinting> res = null;
		try {
			String sql = "select  " +
				"d.iddestinatario," +
				"d.destinatario,"+
				"d.codiceFiscale,"+
				"d.indirizzo,"+
				"d.cap,"+
				"d.città as citta,"+
				"d.provincia,"+
				"d.codiceDestinatario,"+
				"d.tipoCodiceDestinatario,"+
				"d.sia,"+
				"d.coordinateIbanordinante,"+
				
				"c.abi,"+
				"c.cab,"+
				"c.conto,"+
				"c.cin,"+
				"c.coordinateIban,"+
				"c.codiceBic,"+
				"c.descrizioneBanca " +
								
					" from destinatari d " + 
					" left join dbo.Conti_Destinatari c on c.iddestinatario=d.IDDestinatario" + 
					" where d.IDOrdinante=" + idOrdinante.toString() + 
					" order by d.Destinatario, c.Abi, c.Cab, c.conto";
			
			SQLQuery q = currentSession().createSQLQuery(sql);
			q.setResultTransformer(new AliasToBeanResultTransformer(DestinatarioForPrinting.class));
			
			q
				.addScalar("iddestinatario")
				.addScalar("destinatario")
				.addScalar("codiceFiscale")
				.addScalar("indirizzo")
				.addScalar("cap")
				.addScalar("citta")
				.addScalar("provincia")
				.addScalar("codiceDestinatario")
				.addScalar("tipoCodiceDestinatario")
				.addScalar("sia")
				.addScalar("coordinateIbanordinante")
				
				.addScalar("abi")
				.addScalar("cab")
				.addScalar("conto")
				.addScalar("cin")
				.addScalar("coordinateIban")
				.addScalar("codiceBic")
				.addScalar("descrizioneBanca");

			
			res = q.list();
		} finally {
			if (tx)
				commit();
		}

		return res;
	}
	
}
