Create a new version of paste:

package tca.fe.dao;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import tca.fe.entity.FacturaCabecera;
import tca.fe.model.ClienteFactura;
import tca.fe.model.DataFactura;

@Repository
public class FacturaCabeceraDao extends DaoHibernate<FacturaCabecera>{

	private final Long ESTADO_MAIL_ENVIADO = 4L;
	private static final int CUENTA_CORRIENTE  = 2;
	private static final int DEBITO_AUTOMATICO = 3;
	private static final int INTERBANKING      = 4;
	
	@Autowired
	public FacturaCabeceraDao(SessionFactory sessionFactory) {
		setSessionFactory(sessionFactory);
	}
	
	@Override
	protected Class<FacturaCabecera> getTypeClass() {
		return FacturaCabecera.class;
	}
	
	public int updateCabeceras(Long codEstado, List<String> facturas,String tabla, String columnaId) {
		String query = null;
		query = "update "+tabla +" set estado = "+codEstado+" where estado <> "+2L+" and "+columnaId+" in( ";
		
		for(String item : facturas){
			if(!item.equals(facturas.get(facturas.size()-1))){
				query = query + item +" , ";
			}else{
				query = query +item + ")";
			}
		}
		return 	getHibernateTemplate().bulkUpdate(query);
	}	
	
	
	public String obtengoIdGuia(String prefijo, String nguia, Session session){
			
		try {
			logger.info("ingresa a obtengoIdGuia");
		
			String sql = "select to_char(sguia) from sgui where cprefijo = '"+prefijo+"' and nguia = '"+nguia+"'";
		
			SQLQuery query = session.createSQLQuery(sql);
		
			if (query.list().size() > 0){
				return (String) query.list().get(0);
			}
			else{
				return "-1";
			}
			
		} catch (Exception e) {
			return null;
		}

	}

	public int updateCabecerasPorAfipAprobado(String tabla, Long codEstado,String sfactura,String observacion ){
		
		boolean isWhitespace = observacion.matches("^\\s*$");
		
		String query = null;
		if(isWhitespace){
			 query = "update "+tabla +" set estado = "+codEstado+" where sfactura = "+sfactura;
		}else{
			 query = "update "+tabla +" set estado = "+codEstado+" , observaciones = "+"'"+observacion+"'" +
			 " where sfactura = "+sfactura;
		}
		
		return getHibernateTemplate().bulkUpdate(query);
	}
	
	public int updateCabecerasPorAfipRechazo(String tabla, Long codEstado,String sfactura, String observacion ){
		
		boolean isWhitespace = observacion.matches("^\\s*$");
		
		String query = null;
		if(isWhitespace){
		    query = "update "+tabla +" set estado = "+codEstado+
			 " where sfactura = "+sfactura;
		}else{
			query = "update "+tabla +" set estado = "+codEstado+" , observaciones = "+"'"+observacion+"'" +
			 " where sfactura = "+sfactura;
		}
		
		 return getHibernateTemplate().bulkUpdate(query);
	}
	
	public int updateCabecerasConError(Long codEstado, List<String> comprobantesError,String tabla,String columnaId) {
		
		String query = "update " + tabla + " set estado = " + codEstado + " where estado <> " 
						+ 2L + " and " + columnaId + " in( ";

		for (String comprobante : comprobantesError) {
			if (!comprobante.equals(comprobantesError.get(comprobantesError.size() - 1))) {
				query = query + comprobante + " , ";
			} else {
				query = query + comprobante + ")";
			}
		}
		return getHibernateTemplate().bulkUpdate(query);
	}	
	
	public FacturaCabecera findFacturasbyNroPv
	(Long nroFactura, Long pv,String letra, String tipoFormulario){
		
		DetachedCriteria dc = createCriteria();
		dc.add(Restrictions.eq("nroFactura",nroFactura))
		.add(Restrictions.eq("pv", pv))
	//	.add( Restrictions.in("tipoFormulario", new String[] {"FA","NC","ND" }))
		.add(Restrictions.eq("letra",letra))
		.add(Restrictions.eq("tipoFormulario",tipoFormulario));
		
		@SuppressWarnings("unchecked")
		List<FacturaCabecera> list = getHibernateTemplate().findByCriteria(dc);
		
		if (list.size() > 0) {
			return list.get(0);
		} else {
			return null;
		}
	}
	
	private String queryCCFindIdResponsableFacturados() {
		return "select"+ 
                " scli_cliente as scli_cliente," +
                " ccli.email as email," +
                " ccli.dnomyap as dnomyap" +
                " from(" +
                " select distinct" +
                " decode" +
                " (a.scli_despachante, null," +
                " a.scli_responsable," +  
                " decode((select distinct sddt.csecobra" +
                " from fcfi b, fsei, sddt" +
                " where" +
                " b.sfactura = fsei.sfactura" +
                " and b.sfactura = a.sfactura" +
                " and fsei.sdctodest = sddt.sdctodest),1," +
                " a.scli_responsable, a.scli_despachante)) as scli_cliente" +
                " from fcfi a" +
                " where" +
                " a.cconvta = 2" +
                " and a.ctpcomprobante in ('FA','NC','ND')" +
                " and a.caea_estado = 2" +
                " and a.fhemision>=:fechaMinima),ccli" +
                " where" +
                " ccli.scli = scli_cliente" +
                " and ccli.email is not null";
	}
	
	private String queryDAFindIdResponsableFacturados(){
		return " select distinct" +
				" decode(cctadeb_responsable,null, scli_despachante,scli_responsable) as scli_cliente," +
			    " cdeb.email as email,"+
				" decode(cctadeb_responsable,null, dnomyap_despachante,dnomyap) as dnomyap" +
				" FROM fcfi, cdeb" +
				" WHERE" +
				" fhemision >=:fechaMinima" +
				" and (cctadeb_responsable =  cdeb.cctadeb or cctadeb_despachante =  cdeb.cctadeb )"+
				" and (csuc_responsable = cdeb.csuc or csuc_despachante = cdeb.csuc )"+
	            " and (cbco_responsable = cdeb.cbco or cbco_despachante = cdeb.cbco )"+
				" and cdeb.email is not null"+
				" and cconvta = 3"+
				" and ctpcomprobante in('FA','NC','ND')"+
				" and caea_estado = 2";
	}
	
	private String queryINTERBFindIdResponsableFacturados() {
		return "select distinct"
				+ " decode(ccta_responsable,null,scli_despachante,scli_responsable) as scli_cliente,"
				+ " ccli.email as email,"
				+ " decode(fcfi.ccta_responsable,null,fcfi.dnomyap_despachante,fcfi.dnomyap) as dnomyap"
				+ " FROM"
				+ " ccli INNER JOIN"
				+ " fcfi ON"
				+ " ccli.scli = fcfi.scli_responsable"
				+ " WHERE"
				+ " ctpcomprobante in('FA','NC','ND')"
				+ " and cconvta = 4"
				+ " and fhemision>=:fechaMinima"
				+ " and caea_estado = 2"
				+ " and ccli.email is not null";
	}

	@SuppressWarnings("unchecked")
	public List<ClienteFactura> findIdResponsableFacturados(Integer diasBuscar, Integer payMethod){
		
		Session session = getSessionFactory().openSession();
		Calendar cal = Calendar.getInstance();
		cal.set(Calendar.HOUR,0);
		cal.set(Calendar.AM_PM,0);
		cal.set(Calendar.MINUTE,0);
		cal.set(Calendar.SECOND,0);
		cal.set(Calendar.MILLISECOND,0);
		cal.add(Calendar.DATE, diasBuscar);
		
		try {
				logger.info("ingresa a findIdResponsableFacturados");
				SQLQuery  query = null;
			
		        switch (payMethod) {
		            case CUENTA_CORRIENTE:
						query = session.createSQLQuery(queryCCFindIdResponsableFacturados());
		                break;
		                     
		            case DEBITO_AUTOMATICO:
						 query= session.createSQLQuery(queryDAFindIdResponsableFacturados());
		                 break;
		                     
		            case INTERBANKING:
		            	 query= session.createSQLQuery(queryINTERBFindIdResponsableFacturados());
		                 break;
		        }
			
				query.setDate("fechaMinima", cal.getTime());
				
				query.addScalar("scli_cliente", StandardBasicTypes.STRING)
				.addScalar("email", StandardBasicTypes.STRING)
				.addScalar("dnomyap", StandardBasicTypes.STRING)
				.setResultTransformer(Transformers.aliasToBean(ClienteFactura.class));
				
						  
				if (query.list().size() > 0){
					return query.list();
				}
				else{
					return null;
				}
			}
		
			catch (Exception e) {
				return null;
			}finally {
				session.close();
			}
	}
	
	private String queryCCFindFacturasAprobadasByCliente(Long idCliente) {
		return "select *"+
                " from( select sfactura as idFactura,"+
                " ctpcomprobante as tipoComprobante,"+
                " ctpcomprobante||'-'||caea_ptovta||'-'||ctiplet||'-'||nfactura as nroFactura,"+
                " dnomyap_despachante as despachante,"+
                " dnomyap as importador,"+
                " fhemision as fechaEmision,"+
                " decode (fcfi.scli_despachante,"+
                " null,"+
                " decode( fcfi.scli_responsable,"+idCliente+",  'CLIENTE_OK', null),"+
                " decode(("+
                " select distinct sddt.csecobra"+
                " from fcfi b, fsei, sddt"+
                " where"+
                " b.sfactura = fsei.sfactura"+
                " and b.sfactura = fcfi.sfactura"+
                " and fsei.sdctodest = sddt.sdctodest), 1,"+
                " decode( fcfi.scli_responsable,"+idCliente+",  'CLIENTE_OK', null),"+
                " decode( fcfi.scli_despachante,"+idCliente+",  'CLIENTE_OK', null))) clienteCobrado"+
                " from fcfi"+
                " where"+
                " caea_estado = 2"+
                " and fhemision >=:fechaMinima"+
                " and  cconvta = 2"+
                " and ctpcomprobante in('FA','NC','ND')"+
                " and ((scli_despachante = "+idCliente+" )"+
                " or (scli_responsable = "+idCliente+")))";//+
                //" where";+
                //" clienteCobrado is not null"; 
	}
	
	private String queryDAFindFacturasAprobadasByCliente(Long idCliente) {
		return "select sfactura as idFactura," +
			    " ctpcomprobante as tipoComprobante,"+
			    " ctpcomprobante||'-'||caea_ptovta||'-'||ctiplet||'-'||nfactura as nroFactura,"+
			    " dnomyap_despachante as despachante,"+
			    " dnomyap as importador,"+
			    " fhemision as fechaEmision"+
		        " from fcfi"+
		        " where"+
		        " caea_estado = 2"+
		        " and fhemision >=:fechaMinima"+
		        " and  cconvta = 3"+
		        " and ctpcomprobante in('FA','NC','ND')"+
		        " and ((scli_despachante = "+ idCliente+" and cctadeb_despachante is not null)" +
		        " or (scli_responsable = "+idCliente+" and cctadeb_responsable is not null))";
	}
	
	private String queryINTERBFindFacturasAprobadasByCliente(Long idCliente) {
		return "select sfactura as idFactura, "
				+ "ctpcomprobante as tipoComprobante, "
				+ "ctpcomprobante||'-'||caea_ptovta||'-'||ctiplet||'-'||nfactura as nroFactura, "
				+ "dnomyap_despachante as despachante, "
				+ "dnomyap as importador, "
				+ "fhemision as fechaEmision "
				+ "from fcfi "
				+ "where "
				+ "caea_estado = 2 "
				+ "and fhemision >=:fechaMinima "
				+ "and cconvta = 4 "
				+ "and ctpcomprobante in('FA','NC','ND') "
				+ "and ((scli_despachante = "+idCliente+" and ccta_despachante is not null) "
				+ "or (scli_responsable = "+idCliente+" and ccta_responsable is not null));";
	}
	
	
	@SuppressWarnings("unchecked")
	public List<DataFactura> findFacturasAprobadasByCliente(Long idCliente, Integer diasBuscar, Integer payMethod){
		
		Session session = getSessionFactory().openSession();
		Calendar cal = Calendar.getInstance();
		cal.set(Calendar.HOUR,0);
		cal.set(Calendar.AM_PM,0);
		cal.set(Calendar.MINUTE,0);
		cal.set(Calendar.SECOND,0);
		cal.set(Calendar.MILLISECOND,0);
		
		cal.add(Calendar.DATE, diasBuscar);
		try {
			logger.info("ingresa a findFacturasAprobadasByCliente");
			
			SQLQuery  query = null;
			
	        switch (payMethod) {
            	case CUENTA_CORRIENTE:
            		query= session.createSQLQuery(queryCCFindFacturasAprobadasByCliente(idCliente));
                    break;
                    
            	case DEBITO_AUTOMATICO:
   				 	query= session.createSQLQuery(queryDAFindFacturasAprobadasByCliente(idCliente));
   				 	break;
                    
            	case INTERBANKING:
            		query= session.createSQLQuery(queryINTERBFindFacturasAprobadasByCliente(idCliente));
                    break;
	        }
			    
			query.setDate("fechaMinima", cal.getTime());
				      
			query.addScalar("idFactura", StandardBasicTypes.STRING)
			.addScalar("tipoComprobante", StandardBasicTypes.STRING)
			.addScalar("nroFactura", StandardBasicTypes.STRING)
			.addScalar("despachante", StandardBasicTypes.STRING)
			.addScalar("importador", StandardBasicTypes.STRING)
			.addScalar("fechaEmision", StandardBasicTypes.DATE)
			.setResultTransformer(Transformers.aliasToBean(DataFactura.class));
				
			if (query.list().size() > 0){
				return query.list();
			}
			else{
				return null;
			}
			
		} catch (Exception e) {
			logger.info("error select "+e.getMessage());
			e.printStackTrace();
			return null;
		} 
		finally {
			session.close();
		}
		
	}
	
	private String queryCCFindFacturasAprobadasByClientePortalNoNull(Long idCliente){
		return "select *"+
                " from(select distinct fcfi.sfactura as idFactura,"+
                " fcfi.ctpcomprobante as tipoComprobante,"+
                " fcfi.ctpcomprobante||'-'||fcfi.caea_ptovta||'-'||fcfi.ctiplet||'-'||fcfi.nfactura as nroFactura,"+
                " fcfi.dnomyap_despachante as despachante,"+
                " fcfi.dnomyap as importador,"+
                " 'IMPO' as sucursal,"+
                " fcfi.fhemision as fechaEmision,"+
                " decode (fcfi.scli_despachante,"+
                " null,"+
                " decode( fcfi.scli_responsable,"+idCliente+", 'CLIENTE_OK', null),"+
                " decode(("+
                " select distinct sddt.csecobra"+
                " from fcfi b, fsei, sddt"+
                " where"+
                " b.sfactura = fsei.sfactura"+
                " and b.sfactura = fcfi.sfactura"+
                " and fsei.sdctodest = sddt.sdctodest), 1,"+
                " decode( fcfi.scli_responsable,"+idCliente+",  'CLIENTE_OK', null),"+
                " decode( fcfi.scli_despachante,"+idCliente+",  'CLIENTE_OK', null))) clienteCobrado"+
                " from fcfi"+
                " where"+
                " fcfi.caea_estado in (2,4)"+
                " and fcfi.fhemision >= :fechaMinima"+
                " and fcfi.fhemision <= :fechaMaxima"+
                " and fcfi.cconvta in(1,2)"+
                " and fcfi.ctpcomprobante in('FA','NC','ND')"+
                " and ((fcfi.scli_despachante = "+idCliente+" )"+
                " or (fcfi.scli_responsable = "+idCliente+")))";//+
                //" where"+
                //" clienteCobrado is not null";
	}
	
	private String queryCCFindFacturasAprobadasByClientePortalNull(){
		return " select distinct fcfi.sfactura as idFactura,"+
                " fcfi.ctpcomprobante as tipoComprobante,"+
                " fcfi.ctpcomprobante||'-'||caea_ptovta||'-'||ctiplet||'-'||nfactura as nroFactura,"+
                " fcfi.dnomyap_despachante as despachante,"+
                " fcfi.dnomyap as importador,"+
                " 'IMPO' as sucursal,"+
                " fcfi.fhemision as fechaEmision"+
                " from fcfi"+
                " where"+
                " fcfi.caea_estado in (2,4)"+
                " and fcfi.fhemision >= :fechaMinima"+
                " and fcfi.fhemision <= :fechaMaxima"+
                " and fcfi.cconvta in(1, 2)"+
                " and fcfi.ctpcomprobante in('FA','NC','ND')";
	}
	
	private String queryDAFindFacturasAprobadasByClientePortalNull(){
		return "select DISTINCT fcfi.sfactura as idFactura," +
             	  " fcfi.ctpcomprobante as tipoComprobante," +
                  " fcfi.ctpcomprobante||'-'||fcfi.caea_ptovta||'-'||fcfi.ctiplet||'-'||fcfi.nfactura as nroFactura," +
                  " fcfi.dnomyap_despachante as despachante," +
                  " fcfi.dnomyap as importador," +
                  " 'IMPO' as sucursal," +
                  " fcfi.fhemision as fechaEmision" +
                  " from fcfi "+
                  " where fcfi.caea_estado in (2,4)" +
                  " and fcfi.fhemision >= :fechaMinima" +
                  " and fcfi.fhemision <= :fechaMaxima" +
                  " and fcfi.cconvta = 3" +
                  " and fcfi.ctpcomprobante in('FA','NC','ND')";
	}
	
	private String queryDAFindFacturasAprobadasByClientePortalNoNullAux(Long idCliente){
		return " and ((fcfi.scli_despachante = "+ idCliente+
		        " and fcfi.cctadeb_despachante is not null)" +
	            " or (fcfi.scli_responsable = "+idCliente+
	            " and fcfi.cctadeb_responsable is not null))";
	}
	
	private String queryINTERBFindFacturasAprobadasByClientePortalNull() {
		return "select DISTINCT fcfi.sfactura as idFactura, "
				+ "fcfi.ctpcomprobante as tipoComprobante, "
				+ "fcfi.ctpcomprobante||'-'||fcfi.caea_ptovta||'-'||fcfi.ctiplet||'-'||fcfi.nfactura as nroFactura, "
				+ "fcfi.dnomyap_despachante as despachante, "
				+ "fcfi.dnomyap as importador, "
				+ "'IMPO' as sucursal, "
				+ "fcfi.fhemision as fechaEmision "
				+ "from fcfi "
				+ "where fcfi.caea_estado in (2,4) "
				+ "and fcfi.fhemision >= :fechaMinima "
				+ "and fcfi.fhemision <= :fechaMaxima "
				+ "and fcfi.cconvta = 4 "
				+ "and fcfi.ctpcomprobante in('FA','NC','ND')";
	}
	
	private String queryINTERBFindFacturasAprobadasByClientePortalNoNullAux(Long idCliente){
		return " and ((fcfi.scli_despachante = "+idCliente
				+ " and fcfi.ccta_despachante is not null)"
				+ " or (fcfi.scli_responsable = "+idCliente
				+ " and fcfi.ccta_responsable is not null))";
	}

	//para solicitud desde portal
	@SuppressWarnings("unchecked")
	public List<DataFactura> findFacturasAprobadasByClientePortal(Long idCliente,Date fechaMinima,Date fechaMaxima, String prefijo, String nguia, Integer payMethod){
		
		Session session = getSessionFactory().openSession();
		
		try {
			logger.info("ingresa a findFacturasAprobadasByClientePortal");
			
			SQLQuery  query;
			String    queryString = null;
			String    sguia = "";		
			int       q_cliente = 0;
			
	        switch (payMethod) {
	        
	        	case CUENTA_CORRIENTE:
					if(idCliente != null){
						queryString = queryCCFindFacturasAprobadasByClientePortalNoNull(idCliente);
						q_cliente = 1;		
					}
					else{
						queryString = queryCCFindFacturasAprobadasByClientePortalNull(); 
					}
	                break;
	                
	        	case DEBITO_AUTOMATICO:
					queryString = queryDAFindFacturasAprobadasByClientePortalNull();		
				
					if(idCliente != null)
					  queryString=queryString+queryDAFindFacturasAprobadasByClientePortalNoNullAux(idCliente);
	                break;
	                
	        	case INTERBANKING:
					queryString = queryINTERBFindFacturasAprobadasByClientePortalNull();		
					
					if(idCliente != null)
					  queryString=queryString+queryINTERBFindFacturasAprobadasByClientePortalNoNullAux(idCliente);
	                break;
	        }
		
			// Si el usuario ingreso el numero de guia y el prefijo busco la guia
			if((!prefijo.isEmpty() || prefijo.length() > 0) && (!nguia.isEmpty() || nguia.length() > 0)){
				// Busco el id de la guia
				sguia = obtengoIdGuia(prefijo,nguia,session);
				// Si el sguia es (-1) significa que no encontro la guia
				if(sguia.equals("-1")){
					return new ArrayList<DataFactura>();
				} else {
					if(q_cliente == 0){
						queryString = queryString +	" and fcfi.sfactura in (select distinct sfactura from fdfi where sguia = "+sguia+")";
					} else {
						queryString += " and idFactura in (select distinct sfactura from fdfi where sguia = "+sguia+")";
					}
				}
				
			}
			
		    query= session.createSQLQuery(queryString);
			query.setDate("fechaMinima", fechaMinima);
			query.setDate("fechaMaxima", fechaMaxima);	 
						
			query.addScalar("idFactura", StandardBasicTypes.STRING)
			.addScalar("tipoComprobante", StandardBasicTypes.STRING)
			.addScalar("nroFactura", StandardBasicTypes.STRING)
			.addScalar("despachante", StandardBasicTypes.STRING)
			.addScalar("importador", StandardBasicTypes.STRING)
			.addScalar("sucursal", StandardBasicTypes.STRING)
			.addScalar("fechaEmision", StandardBasicTypes.DATE)
			.setResultTransformer(Transformers.aliasToBean(DataFactura.class));
			
			if (query.list().size() > 0){
				return query.list();
			}
			else{
				return new ArrayList<DataFactura>();
			}
			
	    } catch (Exception e) {
	    	logger.info("error select "+e.getMessage());
	    	e.printStackTrace();
			return new ArrayList<DataFactura>();
			
	    } finally {
			session.close();
		}
	}
	
	public String findFacturaById(BigDecimal idFactura){
		
		Session session = getSessionFactory().openSession();
		
		try {
			logger.info("ingresa a findFacturasAprobadasByCliente");
		
			String sql = "SELECT CTPCOMPROBANTE||'-'||CTIPLET||'-'||CAEA_PTOVTA||'-'||NFACTURA FROM FCFI WHERE SFACTURA = "+idFactura;
			
			SQLQuery query = session.createSQLQuery(sql);
			
			if (query.list().size() > 0){
				return (String) query.list().get(0);
			}
			else{
				return null;
			}
			  
		} catch (Exception e) {
		return null;
			
		} finally {
			session.close();
		}
	}
	
	public void updateCabecerasPorEnvioMail(String sfactura, String tabla){
		String query = "update "+tabla+" set estado = "+ESTADO_MAIL_ENVIADO +
		" where sfactura = "+sfactura;
		getHibernateTemplate().bulkUpdate(query);
	//  getHibernateTemplate().update(query);
	}
}