package dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import common.dao.GenericDAO;
import common.dao.IDAO;
import bean.filters.DelegheSepaFilterBean;
import bean.query.AggregatoAbiDelegaSepaQueryBean;
import bean.query.DelegaSepaQueryBean;
import bean.query.DelegheSepaExportBean;
import sicura.entities.DelegheSepa;

@Repository
public class DelegheSepaDAO extends GenericDAO<DelegheSepa, Integer> implements IDAO<DelegheSepa, Integer>  {

	private final String K_IN_CARICO = "0";
	private final String K_NON_ATTIVE = "1";
	private final String K_TUTTE = "2";
	
	public Integer count(DelegheSepaFilterBean disposizioneSepaFilterBean) {
			
		boolean tx = startTransaction();
		Integer res = 0;
		try {
		
			SQLQuery q = currentSession().createSQLQuery(getQuery(disposizioneSepaFilterBean, true));
			q = resolveParams(q, disposizioneSepaFilterBean);
			
			res = (Integer)q.uniqueResult();
		} finally {			
			if(tx)
				commit();
		}
		return res;
	}
	
	@SuppressWarnings("unchecked")
	public List<DelegaSepaQueryBean> list(Integer from, Integer length, DelegheSepaFilterBean disposizioneSepaFilterBean) {
		List<DelegaSepaQueryBean> res = null;
		boolean tx = startTransaction();
		try {
			SQLQuery q = currentSession().createSQLQuery(getQuery(disposizioneSepaFilterBean, false));
			q = resolveParams(q, disposizioneSepaFilterBean);
	
				q
					.setFirstResult(from)
					.setMaxResults(length);
				
			q.setResultTransformer(new AliasToBeanResultTransformer(DelegaSepaQueryBean.class));	
			
			q.addScalar("ID");                 
			q.addScalar("IDOrdinante");         
			q.addScalar("CodiceSIA");          
			q.addScalar("TipoCodiceDestinatario");
			q.addScalar("CodiceDestinatario");   
			q.addScalar("Abi");                  
			q.addScalar("dataAperturaMandato");  
			q.addScalar("dataChiusuraMandato");  
			q.addScalar("statoAllineamento");   
			q.addScalar("dataCreazioneMandato"); 
			
			
	//		Object res1 = q.list();
			res = q.list();
		}finally {			
			if(tx)
				commit();
		}
		return res;
	}
	
	@SuppressWarnings("unchecked")
	public List<AggregatoAbiDelegaSepaQueryBean> listAggregatoAbi(Integer from, Integer length, DelegheSepaFilterBean disposizioneSepaFilterBean) {
		List<AggregatoAbiDelegaSepaQueryBean> res = null;
		
		boolean tx = startTransaction();
		
		try {
			SQLQuery q = currentSession().createSQLQuery(getQuery(disposizioneSepaFilterBean, false));
			q = resolveParams(q, disposizioneSepaFilterBean);
			
			if (from!=null && length!=null)
				q.setFirstResult(from).setMaxResults(length);
			
//				q
//					.setParameter("firstResult", from)
//					.setParameter("maxResult", length);
			
			q
				.addScalar("Abi")
				.addScalar("banca")
				.addScalar("numeroMandati")
				.addScalar("statoAllineamento");
			
			q.setResultTransformer(new AliasToBeanResultTransformer(AggregatoAbiDelegaSepaQueryBean.class));	

			res = q.list();
			
			
		} finally {
			if (tx)
				tx = commit();			
		}
		return res;
		
	}
	
	public String getQuery(DelegheSepaFilterBean disposizioneSepaFilterBean, boolean doCount) {
		
		String sql = "select "+(doCount? "count(*)": "*")  + " from (";
				
		if (!disposizioneSepaFilterBean.isAggregazioneABI()) {
			
			if (K_IN_CARICO.equals(disposizioneSepaFilterBean.getTipo())) {
				sql += " SELECT ID,IDOrdinante,CodiceSIA,TipoCodiceDestinatario,CodiceDestinatario,Abi,Data_Apertura_Mandato as dataAperturaMandato,null as dataChiusuraMandato,StatoAllineamento,Data_Creazione_Mandato as dataCreazioneMandato FROM DelegheSEPA";	
			} else if (K_NON_ATTIVE.equals(disposizioneSepaFilterBean.getTipo())) {
				sql += " SELECT ID,IDOrdinante,CodiceSIA,TipoCodiceDestinatario,CodiceDestinatario,Abi,Data_Apertura_Mandato as dataAperturaMandato,Data_Chiusura_Mandato as dataChiusuraMandato,StatoAllineamento,Data_Creazione_Mandato as dataCreazioneMandato FROM DelegheSEPAStorico ";
			} else if (K_TUTTE.equals(disposizioneSepaFilterBean.getTipo())) {
				sql += "SELECT ID,IDOrdinante,CodiceSIA,TipoCodiceDestinatario,CodiceDestinatario,Abi,Data_Apertura_Mandato as dataAperturaMandato,null as  dataChiusuraMandato,StatoAllineamento,Data_Creazione_Mandato as dataCreazioneMandato FROM DelegheSEPA "
				     + " UNION ALL" 
					 + " SELECT ID,IDOrdinante,CodiceSIA,TipoCodiceDestinatario,CodiceDestinatario,Abi,Data_Apertura_Mandato as dataAperturaMandato,Data_Chiusura_Mandato as dataChiusuraMandato,StatoAllineamento,Data_Creazione_Mandato as dataCreazioneMandato FROM DelegheSEPAStorico";
			}
			sql += ") as t where 1=1 ";
			
			if (!StringUtils.isEmpty(disposizioneSepaFilterBean.getCodiceClienteDebitore()))
				sql += " and t.CodiceDestinatario = :codiceDestinatario";
	
			if (!StringUtils.isEmpty(disposizioneSepaFilterBean.getCodiceDelega()))
				sql += " and t.CodiceDestinatario = :codDelega";
	
			if (disposizioneSepaFilterBean.isCbDaDataApertura() && disposizioneSepaFilterBean.getDaDataApertura()!=null)
				sql += " and t.DataAperturaMandato>=:daDataApertura";
			if (disposizioneSepaFilterBean.isCbADataApertura() && disposizioneSepaFilterBean.getaDataApertura()!=null)
				sql += " and t.DataAperturaMandato<=:aDataApertura";
			
			if (disposizioneSepaFilterBean.isCbDaDataChiusura() && disposizioneSepaFilterBean.getDaDataChiusura()!=null)
				sql += " and t.DataChiusuraMandato>=:daDataChiusura";
			if (disposizioneSepaFilterBean.isCbADataChiusura() && disposizioneSepaFilterBean.getaDataChiusura()!=null)
				sql += " and t.DataChiusuraMandato<=:aDataChiusura";
			
			if (disposizioneSepaFilterBean.getStatoDelega() != null && !"-1".equals(disposizioneSepaFilterBean.getStatoDelega()))
				sql += " and t.StatoAllineamento=:statoAllineamento";
			
			if (!StringUtils.isEmpty(disposizioneSepaFilterBean.getCodiceAbi()))
				sql += " and t.ABI=:codiceAbi";
		} else  {
			if (K_IN_CARICO.equals(disposizioneSepaFilterBean.getTipo())) {
				sql = " SELECT DelegheSEPA.Abi, Count(DelegheSEPA.Abi) as NumeroMandati,Descri as Banca,StatoAllineamento from DelegheSEPA " +
				" join AP_ABI on AP_ABI.Abi = DelegheSEPA.Abi "+	
				" GROUP BY DelegheSEPA.Abi,Descri,StatoAllineamento " +
				(doCount? "": " ORDER BY DelegheSEPA.Abi");

				if (doCount)
					sql = "select count(*) from (" + sql +" ) t";  
			
				            
			} else if (K_NON_ATTIVE.equals(disposizioneSepaFilterBean.getTipo())) {

				sql = " SELECT DelegheSEPAStorico.Abi, Descri as Banca, Count(DelegheSEPAStorico.Abi) as NumeroMandati,StatoAllineamento FROM DelegheSEPAStorico " +
				" join AP_ABI on AP_ABI.Abi = DelegheSEPAStorico.Abi "+
				" GROUP BY DelegheSEPAStorico.Abi,Descri, StatoAllineamento"+
				(doCount?"": " ORDER BY DelegheSEPAStorico.Abi ");
				
				if (doCount)
					sql = "select count(*) from (" + sql +" ) t";  
					
//				String subSQL = " (SELECT DelegheSEPAStorico.Abi, Descri as Banca, Count(DelegheSEPAStorico.Abi) as NumeroMandati,StatoAllineamento FROM DelegheSEPAStorico " +
//							" join AP_ABI on AP_ABI.Abi = DelegheSEPAStorico.Abi "+
//							" GROUP BY DelegheSEPAStorico.Abi,Descri, StatoAllineamento ORDER BY DelegheSEPAStorico.Abi) t ";
//				
//				sql = "SELECT  " + (doCount? "count(*)": "*") + " FROM    ( SELECT  ROW_NUMBER() OVER ( ORDER BY Abi, StatoAllineamento  ) AS RowNum, * FROM (";
//				sql += subSQL;
//				sql += ") AS RowConstrainedResult";
//				if (!doCount) {
//					sql += " WHERE   RowNum >= :firstResult"+
//						   " AND RowNum < :maxResult"+
//						   " ORDER BY RowNum  ";
//				}
			} else if (K_TUTTE.equals(disposizioneSepaFilterBean.getTipo())) {
				sql = 
						" select DelegheSEPA.abi, Descri as Banca,  Count(DelegheSEPA.Abi) as NumeroMandati, StatoAllineamento "+ 
			              " from DelegheSEPA "+        
			              " join AP_ABI on AP_ABI.Abi = DelegheSEPA.Abi "+
			              " GROUP BY DelegheSEPA.Abi, descri, StatoAllineamento "+        
			              " UNION  ALL "+        
			              " SELECT DelegheSEPAStorico.Abi, Descri as Banca, Count(DelegheSEPAStorico.Abi) as NumeroMandati, StatoAllineamento "+        
			             " FROM  DelegheSEPAStorico "+
			             " join AP_ABI on AP_ABI.Abi = DelegheSEPAStorico.Abi "+
			             " GROUP BY DelegheSEPAStorico.Abi, descri, StatoAllineamento ";

				if (doCount)
					sql = "select count(*) from (" + sql + ") t";
				else 
					sql += " order by DelegheSEPA.abi";
					
//				String subSQL = " ( select DelegheSEPA.abi, Descri as Banca,  Count(DelegheSEPA.Abi) as NumeroMandati, StatoAllineamento "+ 
//		              " from DelegheSEPA "+        
//		              " join AP_ABI on AP_ABI.Abi = DelegheSEPA.Abi "+
//		              " GROUP BY DelegheSEPA.Abi, descri, StatoAllineamento "+        
//		              " UNION  ALL "+        
//		              " SELECT DelegheSEPAStorico.Abi, Descri as Banca, Count(DelegheSEPAStorico.Abi) as NumeroMandati, StatoAllineamento "+        
//		             " FROM  DelegheSEPAStorico "+
//		             " join AP_ABI on AP_ABI.Abi = DelegheSEPAStorico.Abi "+
//		             " GROUP BY DelegheSEPAStorico.Abi, descri, StatoAllineamento ) t";
//			
//				sql = "SELECT  " + (doCount? "count(*)": "*") + " FROM    ( SELECT  ROW_NUMBER() OVER ( ORDER BY Abi, StatoAllineamento  ) AS RowNum, * FROM ";
//				sql += subSQL;
//				sql += ") AS RowConstrainedResult";
//				if (!doCount) {
//					sql += " WHERE   RowNum >= :firstResult"+
//						   " AND RowNum < :maxResult"+
//						   " ORDER BY RowNum  ";
//				}
			}
		}
		
		return sql;
	}
	
	public SQLQuery resolveParams(SQLQuery q, DelegheSepaFilterBean disposizioneSepaFilterBean) {
		if (!StringUtils.isEmpty(disposizioneSepaFilterBean.getCodiceClienteDebitore()))
			q.setParameter("codiceDestinatario", disposizioneSepaFilterBean.getCodiceClienteDebitore());

		if (!StringUtils.isEmpty(disposizioneSepaFilterBean.getCodiceDelega()))
			q.setParameter("codDelega", disposizioneSepaFilterBean.getCodiceDelega());

		
		if (disposizioneSepaFilterBean.isCbDaDataApertura() && disposizioneSepaFilterBean.getaDataApertura() != null)
			q.setParameter("daDataApertura", disposizioneSepaFilterBean.getDaDataApertura());
		if (disposizioneSepaFilterBean.isCbADataApertura() && disposizioneSepaFilterBean.getaDataApertura()!=null)
			q.setParameter("aDataApertura",disposizioneSepaFilterBean.getaDataApertura());
		
		if (disposizioneSepaFilterBean.isCbDaDataChiusura() && disposizioneSepaFilterBean.getDaDataChiusura()!=null)
			q.setParameter("daDataChiusura",disposizioneSepaFilterBean.getDaDataChiusura());
		
		if (disposizioneSepaFilterBean.isCbADataChiusura() && disposizioneSepaFilterBean.getaDataChiusura()!=null)
			q.setParameter("aDataChiusura",disposizioneSepaFilterBean.getaDataChiusura());
		
		if (disposizioneSepaFilterBean.getStatoDelega() != null && !"-1".equals(disposizioneSepaFilterBean.getStatoDelega()))
			q.setParameter("statoAllineamento",disposizioneSepaFilterBean.getStatoDelega());
		
		if (!StringUtils.isEmpty(disposizioneSepaFilterBean.getCodiceAbi()))
			q.setParameter("codiceAbi",disposizioneSepaFilterBean.getCodiceAbi());
		
		return q;
	}
	
	public List<String> export (DelegheSepaFilterBean disposizioneSepaFilterBean) {
		
		List<String> res = new ArrayList<String>();
		String sql = getExportQuery(disposizioneSepaFilterBean);
		boolean tx = startTransaction();
		try {
			SQLQuery q = currentSession().createSQLQuery(getExportQuery(disposizioneSepaFilterBean));
			q.setResultTransformer(new AliasToBeanResultTransformer(DelegheSepaExportBean.class));	

			List<DelegheSepaExportBean> deleghe = q.list();
			
			res.add("Abi;Banca;In attesa;Non attive;Attive;Attivate SDD");
			for(DelegheSepaExportBean delega: deleghe) {
				String linea = safeString(delega.getAbi())+";"
						+safeString(delega.getBanca())+";"
						+safeIntToString(delega.getNumeroInAttesa()) + ";"
						+safeIntToString(delega.getNumeroInattivi())+";"
						+safeIntToString(delega.getNumeroAttivi())+";"
						+safeIntToString(delega.getNumeroAttivatiSDD())
						;
				res.add(linea);
			}
			return res;
		} finally {
			if (tx)
				commit();
		}
		
	}

	private String getExportQuery(DelegheSepaFilterBean disposizioneSepaFilterBean) {
		
		String sql = "";
		
		if (K_IN_CARICO.equals(disposizioneSepaFilterBean.getTipo())) {
			sql = " SELECT d.Abi, descri as Banca, " +
				"	sum(case when StatoAllineamento=0 then 1 else 0 end) as numeroInAttesa," + 
				"	sum(case when StatoAllineamento=1 then 1 else 0 end) as numeroAttivi," + 
				"	sum(case when StatoAllineamento=2 then 1 else 0 end) as numeroInattivi," + 
				"	sum(case when StatoAllineamento=6 then 1 else 0 end) as numeroAttivatiSDD" + 
				" from DelegheSEPA d" + 
				" join AP_ABI on AP_ABI.Abi = d.Abi "+
				" GROUP BY d.Abi, descri" +
				" order by d.abi";
		} else if (K_NON_ATTIVE.equals(disposizioneSepaFilterBean.getTipo())) {
			sql = " SELECT d.Abi, descri as Banca, " +
					"	sum(case when StatoAllineamento=0 then 1 else 0 end) as numeroInAttesa," + 
					"	sum(case when StatoAllineamento=1 then 1 else 0 end) as numeroAttivi," + 
					"	sum(case when StatoAllineamento=2 then 1 else 0 end) as numeroInattivi," + 
					"	sum(case when StatoAllineamento=6 then 1 else 0 end) as numeroAttivatiSDD" + 
					" from DelegheSEPAStorico d" + 
					" join AP_ABI on AP_ABI.Abi = d.Abi "+
					" GROUP BY d.Abi, descri" +
					" order by d.abi";
		} else if (K_TUTTE.equals(disposizioneSepaFilterBean.getTipo())) {
			sql = "select abi, Banca, sum(stato0) numeroInAttesa, sum(stato1) numeroAttivi, sum(stato2) numeroInattivi,sum(stato6) numeroAttivatiSDD from (" + 
				" select d.abi, Descri as Banca," + 
				"	sum(case when StatoAllineamento=0 then 1 else 0 end) as stato0," + 
				"	sum(case when StatoAllineamento=1 then 1 else 0 end) as stato1," + 
				"	sum(case when StatoAllineamento=2 then 1 else 0 end) as stato2," + 
				"	sum(case when StatoAllineamento=6 then 1 else 0 end) as stato6" + 
				" from DelegheSEPA d" + 
				" join AP_ABI on AP_ABI.Abi = d.Abi" + 
				" GROUP BY d.Abi, descri" + 
				" UNION  ALL " + 
				" SELECT DelegheSEPAStorico.Abi, Descri as Banca," + 
				"	sum(case when StatoAllineamento=0 then 1 else 0 end) as stato0," + 
				"	sum(case when StatoAllineamento=1 then 1 else 0 end) as stato1," + 
				"	sum(case when StatoAllineamento=2 then 1 else 0 end) as stato2," + 
				"	sum(case when StatoAllineamento=6 then 1 else 0 end) as stato6" + 
				" FROM  DelegheSEPAStorico " +
				" join AP_ABI on AP_ABI.Abi = DelegheSEPAStorico.Abi" + 
				" GROUP BY DelegheSEPAStorico.Abi, descri" + 
				" ) as a" + 
				" GROUP BY abi, Banca"  
				;
		}
		return sql;
	}
}
