55 lines
1.4 KiB
MySQL
55 lines
1.4 KiB
MySQL
|
DELIMITER $$
|
||
|
|
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `sage`.`invoiceOut_manager`(vYear INT, vCompanyFk INT)
|
||
|
|
BEGIN
|
||
|
|
/**
|
||
|
|
* Traslada la info de contabilidad relacionada con las facturas emitidas
|
||
|
|
*
|
||
|
|
* @vYear Año contable del que se quiere trasladar la información
|
||
|
|
* @vCompany Empresa de la que se quiere trasladar datos
|
||
|
|
*/
|
||
|
|
DECLARE vDone BOOL DEFAULT FALSE;
|
||
|
|
DECLARE vInvoiceFk INT;
|
||
|
|
DECLARE vXDiarioFk INT;
|
||
|
|
DECLARE vDatedFrom DATETIME;
|
||
|
|
DECLARE vDatedTo DATETIME;
|
||
|
|
|
||
|
|
DECLARE vCursor CURSOR FOR
|
||
|
|
SELECT i.id,
|
||
|
|
sub2.XDiarioFk
|
||
|
|
FROM vn.invoiceOut i
|
||
|
|
JOIN (SELECT MIN(x.id) XDiarioFk, x.ASIEN, sub.refFk
|
||
|
|
FROM vn.XDiario x
|
||
|
|
JOIN (SELECT x.ASIEN, CONCAT(x.SERIE, x.FACTURA) refFk
|
||
|
|
FROM vn.XDiario x
|
||
|
|
WHERE x.enlazadoSage = FALSE
|
||
|
|
AND x.FACTURA
|
||
|
|
AND x.empresa_id = vCompanyFk
|
||
|
|
AND x.FECHA BETWEEN vDatedFrom AND vDatedTo
|
||
|
|
GROUP BY refFk
|
||
|
|
) sub ON sub.ASIEN = x.ASIEN
|
||
|
|
GROUP BY refFk
|
||
|
|
)sub2 ON sub2.refFk = i.ref;
|
||
|
|
|
||
|
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET vDone = TRUE;
|
||
|
|
|
||
|
|
SELECT CAST(CONCAT(vYear, '-01-01') AS DATETIME), util.dayEnd(CAST(CONCAT(vYear, '-12-31') AS DATE))
|
||
|
|
INTO vDatedFrom, vDatedTo;
|
||
|
|
|
||
|
|
OPEN vCursor;
|
||
|
|
|
||
|
|
l: LOOP
|
||
|
|
FETCH vCursor INTO vInvoiceFk, vXDiarioFk;
|
||
|
|
|
||
|
|
IF vDone THEN
|
||
|
|
LEAVE l;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
CALL invoiceOut_add(vInvoiceFk, vXDiarioFk);
|
||
|
|
|
||
|
|
END LOOP;
|
||
|
|
|
||
|
|
CLOSE vCursor;
|
||
|
|
|
||
|
|
END$$
|
||
|
|
DELIMITER ;
|