103 lines
2.6 KiB
SQL
103 lines
2.6 KiB
SQL
DELIMITER $$
|
|
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `bs`.`ventas_contables_add`(IN vYear INT, IN vMonth INT)
|
|
BEGIN
|
|
|
|
/**
|
|
* Reemplaza las ventas contables del último año.
|
|
* Es el origen de datos para el balance de Entradas
|
|
*
|
|
* @param vYear Año a reemplazar
|
|
* @param vMonth Mes a reemplazar
|
|
*
|
|
**/
|
|
|
|
DECLARE TIPO_PATRIMONIAL INT DEFAULT 188;
|
|
|
|
DELETE FROM bs.ventas_contables
|
|
WHERE year = vYear
|
|
AND month = vMonth;
|
|
|
|
DROP TEMPORARY TABLE IF EXISTS tmp.ticket_list;
|
|
CREATE TEMPORARY TABLE tmp.ticket_list
|
|
(PRIMARY KEY (id))
|
|
ENGINE = MEMORY
|
|
SELECT t.id
|
|
FROM vn.ticket t
|
|
JOIN vn.invoiceOut io ON io.`ref` = t.refFk
|
|
WHERE year(io.issued) = vYear
|
|
AND month(io.issued) = vMonth;
|
|
|
|
INSERT INTO bs.ventas_contables(year
|
|
, month
|
|
, venta
|
|
, grupo
|
|
, reino_id
|
|
, tipo_id
|
|
, empresa_id
|
|
, gasto)
|
|
|
|
SELECT vYear
|
|
, vMonth
|
|
, round(sum(s.quantity * s.price * (100 - s.discount)/100))
|
|
, IF(
|
|
co.companyGroupFk = co2.companyGroupFk
|
|
,1
|
|
,IF(co2.companyGroupFk,2,0)
|
|
) as grupo
|
|
, it.categoryFk
|
|
, i.typeFk
|
|
, t.companyFk
|
|
, i.expenseFk
|
|
+ IF(co.companyGroupFk = co2.companyGroupFk
|
|
,1
|
|
,IF(co2.companyGroupFk,2,0)
|
|
) * 100000
|
|
+ it.categoryFk * 1000 as Gasto
|
|
FROM vn.sale s
|
|
JOIN vn.ticket t ON t.id = s.ticketFk
|
|
JOIN vn.address a on a.id = t.addressFk
|
|
JOIN vn.client c on c.id = a.clientFk
|
|
JOIN tmp.ticket_list tt on tt.id = t.id
|
|
JOIN vn.item i on s.itemFk = i.id
|
|
JOIN vn.company co on co.id = t.companyFk
|
|
LEFT JOIN vn.company co2 on co2.clientFk = c.id
|
|
JOIN vn.itemType it on it.id = i.typeFk
|
|
WHERE s.quantity <> 0
|
|
AND s.price <> 0
|
|
AND s.discount <> 100
|
|
AND i.typeFk <> TIPO_PATRIMONIAL
|
|
GROUP BY grupo, it.categoryFk, i.typeFk, t.companyFk, Gasto;
|
|
|
|
INSERT INTO bs.ventas_contables(year
|
|
, month
|
|
, venta
|
|
, grupo
|
|
, reino_id
|
|
, tipo_id
|
|
, empresa_id
|
|
, gasto)
|
|
SELECT vYear
|
|
, vMonth
|
|
, sum(ts.quantity * ts.price)
|
|
, IF(
|
|
c.companyGroupFk = c2.companyGroupFk
|
|
,1
|
|
,IF(c2.companyGroupFk, 2, 0)
|
|
) as grupo
|
|
, NULL
|
|
, NULL
|
|
, t.companyFk
|
|
, 7050000000
|
|
FROM vn.ticketService ts
|
|
JOIN vn.ticket t ON ts.ticketFk = t.id
|
|
JOIN vn.address a on a.id = t.addressFk
|
|
JOIN vn.client cl on cl.id = a.clientFk
|
|
JOIN tmp.ticket_list tt on tt.id = t.id
|
|
JOIN vn.company c on c.id = t.companyFk
|
|
LEFT JOIN vn.company c2 on c2.clientFk = cl.id
|
|
GROUP BY grupo, t.companyFk ;
|
|
|
|
DROP TEMPORARY TABLE tmp.ticket_list;
|
|
END$$
|
|
DELIMITER ;
|