FacturaCabeceraDao

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);
    }
}