package ec.edu.epn.consumoFact;

import ec.edu.epn.conexion.conexionPostgres;
import ec.edu.epn.facturacionDTO.*;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class ConsultasFacturacion implements Serializable {

    private static final long serialVersionUID = 1L;


   public void consumoFactura(String numAutorizacion){

       FacturaDTO facturaDTO= new FacturaDTO();
       PreparedStatement ps = null;
       try {

           Statement stmt = coneccionSQL().createStatement();
           String sql = "SELECT id_factura as id, " +
                   "fechafac_f as fechaFac, " +
                   "subtotal_f as subtotal, descuento_f as descuento, " +
                   "iva_f as iva, total_f as total, " +
                   "id_estado_factura as estado," +
                   "id_punto_facturacion as puntofact," +
                   "id_caja as caja, tipo_f as tipo," +
                   "porcentajeiva as porcentajeiva," +
                   "nombre_e as nombree, direccion_e as direccione," +
                   "telefono_e as telefonoe, carrera_e as carrerae," +
                   "cedula_e as cedulae," +
                   "nropagos, " +
                   "email_cl as emailcli," +
                   "estadomail, " +
                   "claveacceso," +
                   "numautoriza, " +
                   "ambiente, emision," +
                   "estado_sri as estadosri, xml " +
                   "FROM \"Facturacion\".factura;" +
                   "WHERE trim(numautoriza)= ?";


           ps= coneccionSQL().prepareStatement(sql);
           ps.setString(1, numAutorizacion);

           ResultSet rs = ps.executeQuery(sql);

           while (rs.next()) {
              facturaDTO.setIdFactura(rs.getString(1));
              facturaDTO.setFechafacF(rs.getTimestamp(2));
              facturaDTO.setSubtotalF(rs.getDouble(3));
              facturaDTO.setDescuentoF(rs.getDouble(4));
              facturaDTO.setIvaF(rs.getDouble(5));
              facturaDTO.setTotalF(rs.getDouble(6));
              facturaDTO.setEstadoFactura(rs.getString(7));
              facturaDTO.setPuntoFacturacion(rs.getString(8));
              facturaDTO.setIdCaja(rs.getInt(9));
              facturaDTO.setTipoF(rs.getString(10));
              facturaDTO.setPorcentajeiva(rs.getBigDecimal(11));
              facturaDTO.setNombreE(rs.getString(12));
              facturaDTO.setNombreCli(rs.getString(12));
              facturaDTO.setDireccionE(rs.getString(13));
              facturaDTO.setDireccionCli(rs.getString(13));
              facturaDTO.setTelefonoE(rs.getString(14));
              facturaDTO.setTelefonoCli(rs.getString(14));
              facturaDTO.setCarreraE(rs.getString(15));
              facturaDTO.setCedulaE(rs.getString(16));
              facturaDTO.setCedulaCli(rs.getString(16));
              facturaDTO.setRucCli(rs.getString(16));
              facturaDTO.setIdEstudiante(rs.getString(16));
              facturaDTO.setNropagos(rs.getInt(17));
              facturaDTO.setEmailCl(rs.getString(18)==null?"":rs.getString(18));
              facturaDTO.setEstadomail(rs.getString(19));
              facturaDTO.setClaveacceso(rs.getString(20));
              facturaDTO.setNumautoriza(rs.getString(21));
              facturaDTO.setAmbiente(rs.getString(22));
              facturaDTO.setEmision(rs.getString(23));
              facturaDTO.setEstadoSri(rs.getString(24));
              facturaDTO.setXml(rs.getString(25));


           }
           conexionPostgres conexionSQL = new conexionPostgres();
           conexionSQL.closeConnection();


       }catch (Exception e){
            facturaDTO= null;
       }
   }


   public void consumoDetalleFact(String idFactura){
       List<DetallefacturaDTO> listDetalleFAct= new ArrayList<DetallefacturaDTO>();
       PreparedStatement ps = null;
       try {


           String sql ="SELECT id_servicio," +
                   "id_factura," +
                   "id_detallefactura," +
                   "costo_df," +
                   "unidades_df," +
                   "costot_df," +
                   "id_metodo," +
                   "id_concepto," +
                   "porcentajeiva," +
                   "valor_iva,n" +
                   "subtotal," +
                   "nombreser" +
                   "FROM \"Facturacion\".detallefactura WHERE id_factura= ?";


           ps= coneccionSQL().prepareStatement(sql);
           ps.setString(1, idFactura);

           ResultSet rs = ps.executeQuery(sql);

           while (rs.next()) {
               DetallefacturaDTO detalleDTO= new DetallefacturaDTO();
               detalleDTO.setIdServicio(rs.getString(1)== null?"":rs.getString(1));
               detalleDTO.setIdFactura(rs.getString(2));
               detalleDTO.setIdDetallefactura(rs.getString(3));
               detalleDTO.setCostoDf(rs.getDouble(4));
               detalleDTO.setUnidadesDf(rs.getInt(5));
               detalleDTO.setCostotDf(rs.getDouble(6));
               detalleDTO.setIdMetodo(rs.getString(7)== null?"":rs.getString(7));
               detalleDTO.setConcepto(rs.getString(8));
               detalleDTO.setPorcentajeiva(rs.getBigDecimal(9));
               detalleDTO.setValorIva(rs.getDouble(10));
               detalleDTO.setSubtotal(rs.getDouble(11));
               detalleDTO.setNombreser(rs.getString(12)== null?"":rs.getString(12));

               detalleDTO.setConceptoDTO(this.consumoConcepto(detalleDTO.getConcepto()));

                listDetalleFAct.add(detalleDTO);
           }

           conexionPostgres conexionSQL = new conexionPostgres();
           conexionSQL.closeConnection();

       }catch (Exception e){
           listDetalleFAct= null;
       }



   }



   public void consumoPagos(String idFactura){

       List<PagosDTO> listPagosDTO= new ArrayList<PagosDTO>();
       PreparedStatement ps = null;
       try {

           String sql ="SELECT id_pagos," +
                   "id_factura," +
                   "numero_documento_pa," +
                   "monto_pa," +
                   "saldo_pa," +
                   "id_bancos," +
                   "id_forma_pago," +
                   "id_tc," +
                   "comision_tc" +
                   "  FROM \"Facturacion\".pagos WHERE id_factura=?";


           ps= coneccionSQL().prepareStatement(sql);
           ps.setString(1, idFactura);

           ResultSet rs = ps.executeQuery(sql);

           while (rs.next()) {
               PagosDTO pagosDTO= new PagosDTO();

               pagosDTO.setIdPagos(rs.getString(1));
               pagosDTO.setIdFactura(rs.getString(2));
               pagosDTO.setNumeroDocumentoPa(rs.getString(3)==null?"":rs.getString(3));
               pagosDTO.setMontoPa(rs.getBigDecimal(4));
               pagosDTO.setSaldoPa(rs.getBigDecimal(5));
               pagosDTO.setIdBancos(rs.getInt(6));
               pagosDTO.setIdFormaPago(rs.getString(7));
               pagosDTO.setIdTc(rs.getString(8));
               pagosDTO.setComisionTc(rs.getDouble(9));

               pagosDTO.setFormaPagoDTO(this.consumoFormaPago(pagosDTO.getIdFormaPago()));

               listPagosDTO.add(pagosDTO);
           }


       }catch (Exception e){
           listPagosDTO= null;
       }
   }


   public FormaPagoDTO consumoFormaPago(String idFormaPago){
        FormaPagoDTO formaPagoDTO= new FormaPagoDTO();
       PreparedStatement ps = null;

       try {

           String sql= "SELECT id_forma_pago," +
                   "nombre_fp," +
                   "descr_fp," +
                   "codigo_sri," +
                   "activo," +
                   "codigo_sae" +
                   "FROM \"Facturacion\".forma_pago WHERE id_forma_pago=?;";

           ps= coneccionSQL().prepareStatement(sql);
           ps.setString(1, idFormaPago);

           ResultSet rs = ps.executeQuery(sql);

           while (rs.next()) {
                formaPagoDTO.setIdFormaPago(rs.getString(1));
                formaPagoDTO.setNombreFp(rs.getString(2));
                formaPagoDTO.setDescrFp(rs.getString(3));
                formaPagoDTO.setCodigoSri(rs.getString(4));
                formaPagoDTO.setActivo(rs.getString(5));
                formaPagoDTO.setCodigoSae(rs.getString(6));
           }

           return formaPagoDTO;

       }catch (Exception e){
            return null;
       }
   }


   public ConceptoDTO consumoConcepto(String idConcepto){
       ConceptoDTO conceptoDTO = new ConceptoDTO();
       PreparedStatement ps = null;
       try {

           String sql= "SELECT id_concepto, nombre_concp" +
                   "  FROM \"Facturacion\".concepto WHERE id_concepto= ?;";


           ps= coneccionSQL().prepareStatement(sql);
           ps.setString(1, idConcepto);

           ResultSet rs = ps.executeQuery(sql);

           while (rs.next()) {
               conceptoDTO.setIdConcepto(rs.getString(1));
               conceptoDTO.setNombreConcp(rs.getString(2));
           }


           return conceptoDTO;

       }catch (Exception e){
           return null;
       }
   }


   public ServicioDTO consumoServicio(String idServicio){

       ServicioDTO servicioDTO= new ServicioDTO();
       PreparedStatement ps = null;
       try {
           String sql= "SELECT id_servicio, id_laboratorio," +
                   "id_tiposerv, nombre_s," +
                   "descr_s, aux_id_servicio," +
                   "precio_s" +
                   "  FROM \"Laboratorios\".servicio WHERE id_servicio= ?;";

           ps= coneccionSQL().prepareStatement(sql);
           ps.setString(1, idServicio);

           ResultSet rs = ps.executeQuery(sql);

           while (rs.next()) {
               servicioDTO.setIdServicio(rs.getString(1));
               servicioDTO.setLaboratorio(rs.getString(2));
               servicioDTO.setTiposervicio(rs.getString(3));
               servicioDTO.setNombreS(rs.getString(4));
               servicioDTO.setDescrS(rs.getString(5));
               servicioDTO.setAuxIdServicio(rs.getInt(6));
               servicioDTO.setPrecioS(rs.getFloat(7));

           }

           return servicioDTO;
       }catch (Exception e){
           return null;
       }
   }



    private Connection coneccionSQL() {
        try {
            conexionPostgres conexionSQL = new conexionPostgres();
            Connection con = conexionSQL.getConnection();
            return con;
        } catch (Exception e) {
            return null;
        }

    }


}