77 lines
1.6 KiB
MySQL
77 lines
1.6 KiB
MySQL
|
DELIMITER $$
|
||
|
|
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `vn`.`invoiceOutTaxAndExpense`()
|
||
|
|
BEGIN
|
||
|
|
|
||
|
|
/* Para tickets ya facturados, vuelve a repetir el proceso de facturación.
|
||
|
|
*
|
||
|
|
* @param vInvoice Numero de factura
|
||
|
|
*/
|
||
|
|
|
||
|
|
DECLARE vInvoice INT;
|
||
|
|
DECLARE vInvoiceRef VARCHAR(15);
|
||
|
|
DECLARE vCountry INT;
|
||
|
|
DECLARE vTaxArea VARCHAR(15);
|
||
|
|
DECLARE vContador INT DEFAULT 0;
|
||
|
|
|
||
|
|
DECLARE done BOOL DEFAULT FALSE;
|
||
|
|
|
||
|
|
DECLARE rs CURSOR FOR
|
||
|
|
SELECT id,ref
|
||
|
|
FROM invoiceOut io
|
||
|
|
|
||
|
|
WHERE issued >= '2017-07-01'
|
||
|
|
AND companyFk = 1381
|
||
|
|
AND io.amount IS NULL
|
||
|
|
;
|
||
|
|
|
||
|
|
|
||
|
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
|
||
|
|
|
||
|
|
OPEN rs;
|
||
|
|
|
||
|
|
FETCH rs INTO vInvoice ,vInvoiceRef;
|
||
|
|
|
||
|
|
WHILE NOT done DO
|
||
|
|
|
||
|
|
SELECT s.countryFk
|
||
|
|
INTO vCountry
|
||
|
|
FROM supplier s
|
||
|
|
JOIN invoiceOut io ON io.companyFk = s.id
|
||
|
|
WHERE io.id = vInvoice;
|
||
|
|
|
||
|
|
SELECT IF(c.isEqualizated AND i.taxAreaFk = 'NATIONAL','EQU',i.taxAreaFk)
|
||
|
|
INTO vTaxArea
|
||
|
|
FROM invoiceOutSerial i
|
||
|
|
JOIN invoiceOut io ON io.serial = i.code
|
||
|
|
JOIN client c ON c.id = io.clientFk
|
||
|
|
WHERE io.id = vInvoice;
|
||
|
|
|
||
|
|
DROP TEMPORARY TABLE IF EXISTS tmp.ticketToInvoice;
|
||
|
|
|
||
|
|
CREATE TEMPORARY TABLE tmp.ticketToInvoice
|
||
|
|
SELECT id
|
||
|
|
FROM ticket
|
||
|
|
WHERE refFk = vInvoiceRef;
|
||
|
|
|
||
|
|
CALL invoiceExpenseMake(vInvoice);
|
||
|
|
CALL invoiceTaxMake(vInvoice,vCountry,vTaxArea);
|
||
|
|
|
||
|
|
FETCH rs INTO vInvoice ,vInvoiceRef;
|
||
|
|
|
||
|
|
SET vContador = vContador + 1;
|
||
|
|
|
||
|
|
IF vContador MOD 50 = 0 THEN
|
||
|
|
|
||
|
|
SELECT CONCAT(vContador, ' de momento') AS FACTURAS_ASENTADAS;
|
||
|
|
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
END WHILE;
|
||
|
|
|
||
|
|
CLOSE rs;
|
||
|
|
|
||
|
|
SELECT CONCAT(vContador, ' total') AS FACTURAS_ASENTADAS;
|
||
|
|
|
||
|
|
END$$
|
||
|
|
DELIMITER ;
|