- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
- 09
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
SELECT SQL_NO_CACHE IT.itemCode,
SUBSTRING_INDEX(GROUP_CONCAT(IT.ean),",", 1) as ean,
SUBSTRING_INDEX(GROUP_CONCAT(IT.artCode),",", 1) as artCode,
SUBSTRING_INDEX(GROUP_CONCAT(IT.description),",", 1) as description,
SUBSTRING_INDEX(GROUP_CONCAT(IT.siteName),",", 1) as siteName,
SUBSTRING_INDEX(GROUP_CONCAT(IT.familyName),",", 1) as familyName,
SUBSTRING_INDEX(GROUP_CONCAT(IT.familyCode),",", 1) as familyCode,
SUBSTRING_INDEX(GROUP_CONCAT(IT.SSfamilyCode),",", 1) as SSfamilyCode,
SUBSTRING_INDEX(GROUP_CONCAT(IT.SSfamilyName),",", 1) as SSfamilyName,
SUBSTRING_INDEX(GROUP_CONCAT(IT.sectorCode),",", 1) as sectorCode,
SUBSTRING_INDEX(GROUP_CONCAT(IT.sectorName),",", 1) as sectorName,
SUBSTRING_INDEX(GROUP_CONCAT(IT.radiusCode),",", 1) as radiusCode,
SUBSTRING_INDEX(GROUP_CONCAT(IT.radiusName),",", 1) as radiusName,
FI.areaName,
FI.areaCode,
ROUND(SUM(IT.price), 2) as price,
SUM(IT.quantity) as theoreticalQty,
IFNULL(FI.countedQty, 0) as countedQty,
ROUND(SUM(IT.quantity) * ROUND(SUM(IT.price), 2), 2) as theoreticalQtyValue,
ROUND(IFNULL(FI.countedQty, 0) * ROUND(SUM(IT.price), 2), 2) as countedQtyValue,
IFNULL(FI.countedQty, 0) - SUM(IT.quantity) as stockGapQty,
ROUND((IFNULL(FI.countedQty, 0) - SUM(IT.quantity)) * SUM(IT.price), 2) as stockGapValue,
DATE_FORMAT(ST.lastImport, "%d-%m-%Y %H:%i:%s") as dateOfLastImport,
DATE_FORMAT(NOW(), "%d-%m-%Y %H:%i:%s") as dateOfExport
FROM Items as IT
LEFT JOIN (
SELECT MIN(FI.ean) as ean, SUM(FI.quantity) as countedQty, IT.itemCode,
GROUP_CONCAT(DISTINCT AR.name) as areaName,
GROUP_CONCAT(DISTINCT AR.code) as areaCode
FROM FoundItems as FI
INNER JOIN Items as IT ON FI.ean = IT.ean
LEFT JOIN Areas as AR ON AR.id = FI.areaId
INNER JOIN Stores as ST ON ST.id = IT.storeId
WHERE eventId = ${args.eventId}
AND IT.storeId = (SELECT storeId FROM Events WHERE id = ${args.eventId})
GROUP BY IT.itemCode
) as FI ON IT.itemCode = FI.itemCode
INNER JOIN Stores as ST ON ST.id = IT.storeId
WHERE IT.storeId = (SELECT storeId FROM Events WHERE id = ${args.eventId})
GROUP BY IT.itemCode, FI.areaName, FI.areaCode, FI.countedQty, dateOfLastImport
а що ця конструкція вміє, а ета що, про та ще й ето потрібно впихнути сюди
Follow us!