54 lines
1.7 KiB
SQL
54 lines
1.7 KiB
SQL
SELECT
|
|
io.ref,
|
|
s.ticketFk,
|
|
ib.ediBotanic botanical,
|
|
s.quantity,
|
|
s.price,
|
|
s.discount,
|
|
s.itemFk,
|
|
s.concept,
|
|
tc.code vatType,
|
|
it.isPackaging,
|
|
i.subName
|
|
FROM vn.invoiceOut io
|
|
JOIN vn.ticket t ON t.refFk = io.ref
|
|
JOIN vn.supplier su ON su.id = io.companyFk
|
|
JOIN vn.client c ON c.id = t.clientFk
|
|
JOIN vn.payMethod pm ON pm.id = c.payMethodFk
|
|
JOIN vn.company co ON co.id = io.companyFk
|
|
JOIN vn.supplierAccount sa ON sa.id = co.supplierAccountFk
|
|
JOIN vn.sale s ON s.ticketFk = t.id
|
|
JOIN item i ON i.id = s.itemFk
|
|
LEFT JOIN itemType it ON it.id = i.typeFk
|
|
LEFT JOIN itemCategory ic ON ic.id = it.categoryFk
|
|
LEFT JOIN itemBotanicalWithGenus ib ON ib.itemFk = i.id
|
|
AND ic.code = 'plant'
|
|
AND ib.ediBotanic IS NOT NULL
|
|
JOIN vn.itemTaxCountry itc ON itc.countryFk = su.countryFk
|
|
AND itc.itemFk = s.itemFk
|
|
JOIN vn.taxClass tc ON tc.id = itc.taxClassFk
|
|
WHERE t.refFk = ?
|
|
UNION ALL
|
|
SELECT
|
|
io.ref,
|
|
t.id ticketFk,
|
|
NULL botanical,
|
|
ts.quantity,
|
|
ts.price,
|
|
0 discount,
|
|
NULL,
|
|
ts.description,
|
|
tc.code,
|
|
NULL,
|
|
NULL
|
|
FROM vn.invoiceOut io
|
|
JOIN vn.ticket t ON t.refFk = io.ref
|
|
JOIN vn.ticketService ts ON ts.ticketFk = t.id
|
|
JOIN vn.client c ON c.id = t.clientFk
|
|
JOIN vn.payMethod pm ON pm.id = c.payMethodFk
|
|
JOIN vn.company co ON co.id = io.companyFk
|
|
JOIN vn.supplierAccount sa ON sa.id = co.supplierAccountFk
|
|
JOIN vn.taxClass tc ON tc.id = ts.taxClassFk
|
|
WHERE t.refFk = ?
|
|
ORDER BY (isPackaging), concept, itemFk
|