42 lines
1.1 KiB
MySQL
42 lines
1.1 KiB
MySQL
|
DELIMITER $$
|
||
|
|
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `hedera`.`myTicket_get`(vSelf INT)
|
||
|
|
BEGIN
|
||
|
|
/**
|
||
|
|
* Returns a current user ticket header.
|
||
|
|
*
|
||
|
|
* @param vSelf The ticket identifier
|
||
|
|
*/
|
||
|
|
DECLARE vTaxBase DECIMAL(10,2);
|
||
|
|
DECLARE vTax DECIMAL(10,2);
|
||
|
|
|
||
|
|
DROP TEMPORARY TABLE IF EXISTS tmp.ticket;
|
||
|
|
CREATE TEMPORARY TABLE tmp.ticket
|
||
|
|
ENGINE = MEMORY
|
||
|
|
SELECT id ticketFk FROM myTicket
|
||
|
|
WHERE id = vSelf;
|
||
|
|
|
||
|
|
CALL vn.ticket_getTax(NULL);
|
||
|
|
|
||
|
|
SELECT IFNULL(SUM(taxableBase), 0.0), IFNULL(SUM(tax), 0.0)
|
||
|
|
INTO vTaxBase, vTax
|
||
|
|
FROM tmp.ticketAmount;
|
||
|
|
|
||
|
|
SELECT t.id, t.landed, t.shipped, t.refFk, t.nickname,
|
||
|
|
ag.description agency, m.code method,
|
||
|
|
a.street, a.postalCode, a.city, p.name province,
|
||
|
|
vTaxBase taxBase, vTaxBase + vTax AS total
|
||
|
|
FROM tmp.ticket v
|
||
|
|
JOIN vn.ticket t ON t.id = v.ticketFk
|
||
|
|
JOIN vn.address a ON a.id = t.addressFk
|
||
|
|
JOIN vn.agencyMode ag ON ag.id = t.agencyModeFk
|
||
|
|
LEFT JOIN vn.deliveryMethod m ON m.id = ag.deliveryMethodFk
|
||
|
|
LEFT JOIN vn.province p ON p.id = a.provinceFk
|
||
|
|
WHERE t.id = vSelf;
|
||
|
|
|
||
|
|
DROP TEMPORARY TABLE
|
||
|
|
tmp.ticket,
|
||
|
|
tmp.ticketTax,
|
||
|
|
tmp.ticketAmount;
|
||
|
|
END$$
|
||
|
|
DELIMITER ;
|