package dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projection;
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 common.utility.StringUtilities;
import sicura.entities.ApAbi;
import sicura.entities.RemunerationDetail;
import sicura.entities.RemunerationHeader;
import sicura.entities.RemunerationPrevisioning;

@Repository
public class RemunerationDao extends GenericDAO<RemunerationHeader, Integer> implements IDAO<RemunerationHeader, Integer> {

	public List<RemunerationHeader> getHeader(String codiceSia, int startFrom, int length) {
		
		boolean tx = startTransaction();
		try {
			Criteria crit = currentSession().createCriteria(RemunerationHeader.class);
			crit
				.addOrder(Order.desc("idremuneration"))
				.setFirstResult(startFrom)
				.setMaxResults(length);
		    	
			if (codiceSia!=null && !"".equals(codiceSia))
				crit.add(Restrictions.eq("codiceSia", codiceSia));
				
			List<RemunerationHeader> res = crit.list();
		    
		    return res;
		} finally {
			if (tx)
				tx = commit();
		}
	}
	
	public Integer getHeaderCount(String codiceSia) {
		
		boolean tx = startTransaction();
		try {
			Criteria crit = currentSession().createCriteria(RemunerationHeader.class);
			
			if (codiceSia!=null && !"".equals(codiceSia))
				crit.add(Restrictions.eq("codiceSia", codiceSia));
	
			crit.setProjection(Projections.rowCount());
			
			Integer res = ((Long)crit.uniqueResult()).intValue();
		    
		    return res;
		} finally {
			if (tx)
				tx = commit();
		}
	}
	
	
	public List<RemunerationPrevisioning> getProvisioning(int idRemuneration, int startFrom, int length) {
		
		boolean tx = startTransaction();
		try {
			Criteria crit = currentSession().createCriteria(RemunerationPrevisioning.class);
			
			//SELECT * FROM Remuneration_Previsioning WHERE IDRemuneration=? ORDER BY DataRendi DESC,Anno DESC,Trimestre DESC,CreditorID,BIC_Creditore,BIC_Debitore
			crit.add(Restrictions.eq("idremuneration", idRemuneration))
				.addOrder(Order.desc("datarendi"))
				.addOrder(Order.desc("anno"))
				.addOrder(Order.desc("trimestre"))
				.addOrder(Order.asc("creditorId"))
				.addOrder(Order.asc("bicCreditore"))
				.addOrder(Order.asc("bicDebitore"));
			
			if (startFrom>=0 && length>0)
				crit.setFirstResult(startFrom)
					.setMaxResults(length);
		    	
			List<RemunerationPrevisioning> res = crit.list();
		    
		    return res;
		} finally {
			if (tx)
				tx = commit();
		}
	}

	public List<RemunerationDetail> getDetails(int idPrevisioning, int startFrom, int length, String orderBy) {
		
		boolean tx = startTransaction();
		try {
			Criteria crit = currentSession().createCriteria(RemunerationDetail.class);
			
			if (StringUtilities.empty(orderBy))
				orderBy = "iddetail";
			
			//SELECT * FROM Remuneration_Previsioning WHERE IDRemuneration=? ORDER BY DataRendi DESC,Anno DESC,Trimestre DESC,CreditorID,BIC_Creditore,BIC_Debitore
			crit.add(Restrictions.eq("idprevisioning", idPrevisioning))
				.addOrder(getOrderBy(orderBy));
			
			
			if (startFrom>0 && length>0)
				crit
					.setFirstResult(startFrom)
					.setMaxResults(length);
		    	
			
			List<RemunerationDetail> res = null;
			
			try {
				res = crit.list();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		    
		    return res;
		} finally {
			if (tx)
				tx = commit();
		}
	}

	
	public Integer getDetailsCount(int idPrevisioning) {
		
		boolean tx = startTransaction();
		try {
			Criteria crit = currentSession().createCriteria(RemunerationDetail.class);
			
			crit.add(Restrictions.eq("idprevisioning", idPrevisioning))
				.setProjection(Projections.rowCount());	    	
			
			Integer res = ((Long)crit.uniqueResult()).intValue();
		    
		    return res;
		} finally {
			if (tx)
				tx = commit();
		}
	}


	public Integer getProvisioningCount(int idRemuneration) {
		
		boolean tx = startTransaction();
		try {
			Criteria crit = currentSession().createCriteria(RemunerationPrevisioning.class);
			
			crit.add(Restrictions.eq("idremuneration", idRemuneration))
				.setProjection(Projections.rowCount());	    	
			
			Integer res = ((Long)crit.uniqueResult()).intValue();
		    
		    return res;
		} finally {
			if (tx)
				tx = commit();
		}
	}

	public List<String> getCodiciSia() {
		boolean tx = startTransaction();
		try {
			SQLQuery q=currentSession().createSQLQuery("Select DISTINCT CodiceSIA from Remuneration_Header ORDER BY CodiceSIA");
			List<String> res = q.list();
			
			return res;
		} finally {
			if (tx)
				tx = commit();
		}
	}

	public String generaRichiestaDettaglioMandati(AppVariables appVariables,Integer id, Integer idDettaglio, Boolean all, String tipoDettaglio, String tipoRichiesta) {
		
		String parametri = (all? "1" : "0") + "|";
		parametri += id.toString() + "|";
		parametri += (idDettaglio!=null? idDettaglio : "") + "|"; // non c'e' il dettaglio in questa richiesta
		parametri += tipoDettaglio + "|" + tipoRichiesta;
		
		// CREATE PROCEDURE [dbo].[spMI_NewServiceOperation]
		//   @SecurityUserId VARCHAR(15),
		//   @SecurityGuid UNIQUEIDENTIFIER,
		//   @NomeAttivita VARCHAR(255),
		//   @Parametri VARCHAR(MAX)
		
		boolean tx = startTransaction();
		try {
			Connection connection = getConnection();
			String res = "-1";
			
			CallableStatement proc;
			try {
				proc = connection.prepareCall("{? = call spMI_NewServiceOperation (?,?,?,?)}");
					
				proc.registerOutParameter(1, Types.INTEGER);
				proc.setString(2, appVariables.getUserId());
				proc.setString(3, appVariables.getGuid());
				proc.setString(4, "GENERAREMUNERATION");
				proc.setString(5, parametri);
				
				proc.execute();
					
				//res = new Integer(proc.getInt(1)).toString();
				
				res = "";
				return res;
				
			} catch (SQLException e) {
				if (tx)
					tx = rollback();
				e.printStackTrace();
				return e.getMessage();
			}
		} finally {
			if (tx)
				tx = commit();
		}
		
	}

	public String quadra(AppVariables appVariables, Integer id, Integer idDetail) {
		
		// CREATE PROCEDURE [dbo].[spQuadraRemuneration_Previsioning]
		//	@SecurityUserId VARCHAR(15),
		//	@SecurityGuid UNIQUEIDENTIFIER,
		//	@IDRemuneration INT = NULL,
		//	@IDPrevisioning INT = NULL
		
		boolean tx = startTransaction();
		try {
			Connection connection = getConnection();
			String res = "-1";
			
			CallableStatement proc;
			try {
				if (idDetail==null)
					proc = connection.prepareCall("{? = call spQuadraRemuneration_Previsioning (?,?,?)}");
				else
					proc = connection.prepareCall("{? = call spQuadraRemuneration_Previsioning (?,?,?,?)}");
					
				proc.registerOutParameter(1, Types.INTEGER);
				proc.setString(2, appVariables.getUserId());
				proc.setString(3, appVariables.getGuid());
				proc.setObject(4, id);
				if (idDetail!=null)
					proc.setObject(5,idDetail);
				
				proc.execute();
					
				res = new Integer(proc.getInt(1)).toString();
				
			} catch (SQLException e) {
				if (tx)
					tx = rollback();
				e.printStackTrace();
				return e.getMessage();
			}
			
			return res;
		} finally {
			if (tx)
				tx = commit();
		}
	}

	public List<String> exportMessaggi(AppVariables appVariables, Integer idRemuneration) {
		List<RemunerationPrevisioning> data = getProvisioning(idRemuneration, 0, 99999);
		
		
		List<String> res = new ArrayList<String>();
				
		res.add("Data Messaggio Banca;Anno;Trimestre;Identificativo Creditore;BIC Creditore;BIC Debitore;N° CMF;Prezzo Unitario CMF;Totale CMF;N° DMF;Prezzo Unitario DMF;Totale DMF;Totale Flat;"+
				"Costo Totale Trimestre;Quadratura trimestre;N.CMF+DMF acquisiti;N.CMF+DMF calcolati;Data ultima quadratura;Richiesta Dettaglio;Esito;Descrizione Esito Richiesta Mandati;Data Richiesta;"+
				"Tipo Dettaglio;Tipo Richiesta;Data Esito;Identificativo Rendiconto Previsionale");
	
		SimpleDateFormat sdf = new SimpleDateFormat("dd/mm/yyyy");
		
		for (RemunerationPrevisioning prov: data) {
				res.add(exportCsvField(safeDate(prov.getDatarendi(), sdf)) +
						exportCsvField(prov.getAnno()) +
						exportCsvField(prov.getTrimestre()) + 
						exportCsvField(prov.getCreditorId())+
						exportCsvField(prov.getBicCreditore())+ 
						exportCsvField(prov.getBicDebitore())+ 
						exportCsvField(prov.getNumeroCmf())+ 
						exportCsvField(prov.getPrezzoUnitarioCmf())+
						exportCsvField(prov.getTotaleCmfUnitOrFlat()) +
						exportCsvField(prov.getNumeroDmf())+
						exportCsvField(prov.getPrezzoUnitarioDmf())+
						exportCsvField(prov.getTotaleDmfUnitOrFlat())+
						exportCsvField(prov.getPrezzoFlatTotale())+
						exportCsvField(prov.getImportoTotaleRemuneration())+
						exportCsvField(prov.getTotaleCmfDmfQuadratura())+
						exportCsvField(prov.getTotaleCmfDmf())+
						exportCsvField(prov.getTotaleCmfDmfCarico())+
						exportCsvField(prov.getDataUltimaQuadratura(),sdf)+
						exportCsvField(prov.isFlagRichiestaDetail()) +
						exportCsvField(prov.getEsitoRichiestaDetail())+
						exportCsvField(prov.getDescrizioneEsitoRichiestaDetail())+
						exportCsvField(prov.getDataRichiestaDetail())+
						exportCsvField(decodeTipoDettaglio(prov.getRichiestaMndtTp()))+
						exportCsvField(decodeTipoRichiesta(prov.getRichiestaRqstTp()))+
						exportCsvField(prov.getDataEsitoRichiestaDetail())+
						exportCsvField(prov.getPrevisioningIdentification())
			);			
		}

		marcaComeEsportato(idRemuneration, appVariables);
		
		return res;
	}

	
	private void marcaComeEsportato(Integer idRemuneration, AppVariables appVariables) {
		boolean tx = startTransaction();
		try {
			Connection connection = getConnection();
			String res = "-1";
			
			/*
			 CREATE PROCEDURE [dbo].[spMI_SetExportRemuneration_Header]
				@SecurityUserId VARCHAR(15),
				@SecurityGuid UNIQUEIDENTIFIER,
			
				@IDRemuneration INT
			 */
			
			CallableStatement proc;
			try {
					proc = connection.prepareCall("{? = call spMI_SetExportRemuneration_Header (?,?,?)}");
					
				proc.registerOutParameter(1, Types.INTEGER);
				proc.setString(2, appVariables.getUserId());
				proc.setString(3, appVariables.getGuid());
				proc.setObject(4, idRemuneration);
				
				proc.execute();
					
				res = new Integer(proc.getInt(1)).toString();
				
			} catch (SQLException e) {
				if (tx)
					tx = rollback();
				e.printStackTrace();
			}
			
		} finally {
			if (tx)
				tx = commit();
		}
		
	}

	private String decodeTipoDettaglio(String tipo) {
		if ("CMF".equals(tipo))
			return "Solo mandati rilasciati presso il beneficiario";
		if ("DMF".equals(tipo))
			return "Solo mandati rilasciati presso PSP (Banca) del pagatore";
		if ("ALL".equals(tipo))
			return "Tutti i mandati";
		return "";
	}
		
	private String decodeTipoRichiesta(String tipo) {
		if ("ALL".equalsIgnoreCase(tipo))
			return "Tutti i Mandati attivi nel periodo di riferimento";
		if ("CHG".equalsIgnoreCase(tipo))
			return "Mandati attivati nel periodo di riferimento e mandati revocati o disattivati nel periodo di riferimento precedente";
		return "";
	}

	public RemunerationPrevisioning getProvisioning(Integer idPrevisioning) {
		boolean tx = startTransaction();
		try {
			Criteria crit = currentSession().createCriteria(RemunerationPrevisioning.class);
			crit
				.add(Restrictions.eq("idprevisioning", idPrevisioning));
		    	
			List<RemunerationPrevisioning> res = crit.list();
		    
			if (res.size()>0)
				return res.get(0);
			else 
				return null;
		} finally {
			if (tx)
				tx = commit();
		}
	}
	


	
}
 