drop procedure if exists increment_bill;
DELIMITER $$
CREATE  PROCEDURE `increment_bill`(IN `sale_i` INT, IN `responseCod` INT, IN `invoiceTypeCod` INT, IN `serieNumer` VARCHAR(20), IN `issueDat` DATE, IN `issueTim` TIME, IN `documentCurrencyCod` VARCHAR(10), IN `customerDocumentI` INT, IN `supplierDocumentI` INT, IN `customerI` VARCHAR(255), IN `customerNam` VARCHAR(100), IN `taxAmoun` DECIMAL, IN `taxableAmoun` DECIMAL, IN `taxSubtota` DECIMAL, IN `payableAmoun` DECIMAL, IN `referenceI` VARCHAR(20), IN `DocumentTypeCod` INT, IN `descriptio` VARCHAR(100), IN `user_creat` INT, IN `date_creat` DATETIME, IN `estad` INT, IN `result02` INT ZEROFILL, OUT `resultado` INT, OUT `resultSERIEcorre` VARCHAR(12), IN `non_affecte` DECIMAL, IN `exonerate` DECIMAL)
    NO SQL
BEGIN
    DECLARE countmin INT;
    DECLARE correlativo INT;
    DECLARE validarMonto double;
    DECLARE invoice Varchar(12);
    DECLARE tipoComProbante varchar(1);
    DECLARE montoComprobante double;
    DECLARE validarExistencia varchar(20);
    SET countmin = 0;
    
    SET tipoComProbante := SUBSTRING(referenceI, 1, 1);
    
    /*SET validarExistencia := (SELECT id FROM tec_credit_note WHERE sale_id = sale_i limit 1);
    
    IF(validarExistencia IS NULL) THEN*/
    SET validarMonto := (SELECT IF(sum(payableAmount)is null, 0,sum(payableAmount)) as result FROM tec_credit_note WHERE sale_id = sale_i and estado = 1);
    SET montoComprobante := (SELECT amount FROM tec_sales WHERE id = sale_i);
    
    IF(montoComprobante > validarMonto) THEN
    
        IF STRCMP(tipoComProbante, 'F') = 0 THEN
    
            WHILE countmin < 1 DO
                UPDATE tec_settings SET note_credit_invoice_number = note_credit_invoice_number + 1;
                SET countmin = countmin + 1;
            END WHILE;

            SET correlativo := (SELECT note_credit_invoice_number FROM tec_settings);
            SET result02 := correlativo;
            SET invoice := (SELECT concat((SELECT SUBSTRING(note_credit_invoice_format,1,5) FROM tec_settings), SUBSTRING(result02, 4)) );
    
        ELSEIF STRCMP(tipoComProbante, 'B') = 0 THEN
    
            WHILE countmin < 1 DO
                UPDATE tec_settings SET note_credit_bill_number = note_credit_bill_number + 1;
                SET countmin = countmin + 1;
            END WHILE;

            SET correlativo := (SELECT note_credit_bill_number FROM tec_settings);
            SET result02 := correlativo;
            SET invoice := (SELECT concat((SELECT SUBSTRING(note_credit_bill_format,1,5) FROM tec_settings), SUBSTRING(result02, 4)) );
    
        END IF;
    
        INSERT INTO `tec_credit_note` (`sale_id`, `responseCode`, `invoiceTypeCode`, `serieNumero`, `issueDate`, `issueTime`, `documentCurrencyCode`, `customerDocumentID`, `supplierDocumentID`, `customerID`, `customerName`, `taxAmount`, `taxableAmount`, `non_affected`, `exonerated`, `taxSubtotal`, `payableAmount`, `referenceID`, `DocumentTypeCode`, `description`,`user_create`, `date_create`,`estado`) VALUES
(sale_i, responseCod, invoiceTypeCod, invoice, issueDat, issueTim, documentCurrencyCod, customerDocumentI, supplierDocumentI, customerI, customerNam, taxAmoun, taxableAmoun, non_affecte, exonerate, taxSubtota, payableAmoun, referenceI, DocumentTypeCod, descriptio, user_creat, date_creat, estad);
    
        -- SELECT validarExistencia;
        SET resultado := (SELECT LAST_INSERT_ID());
        SET resultSERIEcorre := (invoice);
    ELSE 
    
        SET resultado := (0);
        SET resultSERIEcorre := (0);
    
    END IF;
    
END$$
DELIMITER ;