Time: 241022 12:53:55
User@Host: root[root] @ localhost []
Thread_id: 145054 Schema: npcwebsite QC_hit: No
Query_time: 13.480539 Lock_time: 0.000252 Rows_sent: 1 Rows_examined: 81279
Rows_affected: 0 Bytes_sent: 207
SET timestamp=1729619635;
SELECT SUM( t.items_stocks_counter ) items_stocks_counter,
SUM( t.items_purchase_price_total ) items_purchase_price_total,
SUM( t.items_without_purchase_price ) items_without_purchase_price
FROM ( SELECT p0.ID,
IFNULL( SUM( IF( NOT EXISTS(
SELECT pch1.ID FROM npcsite_posts pch1
WHERE pch1.post_parent = p0.ID AND pch1.post_type = 'product_variation'
) OR EXISTS(
SELECT pch2.ID FROM npcsite_posts pch2
LEFT JOIN npcsite_postmeta pmchild2 ON pch2.ID = pmchild2.post_id AND pmchild2.meta_key = 'manage_stock'
WHERE pch2.post_parent = p0.ID AND pmchild2.meta_value = 'no'
), IF( IF( ais.is_main = 0 AND (apd0.multi_inventory IS NULL OR apd0.multi_inventory <> 0), IF( aism.is_expired = 0 AND aism.manage_stock = 1 AND aism.stock_quantity > 0, aism.stock_quantity, 0 ), IF( ms.meta_value = 'yes', CAST( st.meta_value AS DECIMAL(10,6) ), 0 ) ) > 0, IF( ais.is_main = 0 AND (apd0.multi_inventory IS NULL OR apd0.multi_inventory <> 0), IF( aism.is_expired = 0 AND aism.manage_stock = 1 AND aism.stock_quantity > 0, aism.stock_quantity, 0 ), IF( ms.meta_value = 'yes', CAST( st.meta_value AS DECIMAL(10,6) ), 0 ) ), 0 ), 0 ) ), 0 ) items_stocks_counter,
IFNULL( SUM( IF( (apd0.multi_inventory IS NULL OR apd0.multi_inventory <> 0) AND (apd0.price_per_inventory IS NULL OR apd0.price_per_inventory <> 0) AND ais.is_main = 0, aism.purchase_price * aism.stock_quantity, apd0.purchase_price * IF( NOT EXISTS(
SELECT pch1.ID FROM npcsite_posts pch1
WHERE pch1.post_parent = p0.ID AND pch1.post_type = 'product_variation'
) OR EXISTS(
SELECT pch2.ID FROM npcsite_posts pch2
LEFT JOIN npcsite_postmeta pmchild2 ON pch2.ID = pmchild2.post_id AND pmchild2.meta_key = 'manage_stock'
WHERE pch2.post_parent = p0.ID AND pmchild2.meta_value = 'no'
), IF( IF( ais.is_main = 0 AND (apd0.multi_inventory IS NULL OR apd0.multi_inventory <> 0), IF( aism.is_expired = 0 AND aism.manage_stock = 1 AND aism.stock_quantity > 0, aism.stock_quantity, 0 ), IF( ms.meta_value = 'yes', CAST( st.meta_value AS DECIMAL(10,6) ), 0 ) ) > 0, IF( ais.is_main = 0 AND (apd0.multi_inventory IS NULL OR apd0.multi_inventory <> 0), IF( aism.is_expired = 0 AND aism.manage_stock = 1 AND aism.stock_quantity > 0, aism.stock_quantity, 0 ), IF( ms.meta_value = 'yes', CAST( st.meta_value AS DECIMAL(10,6) ), 0 ) ), 0 ), 0 ) ) ), 0 ) items_purchase_price_total,
IFNULL( SUM( IF( NOT EXISTS(
SELECT pch3.ID FROM npcsite_posts pch3
LEFT JOIN npcsite_atum_product_data apd3 ON pch3.ID = apd3.product_id
WHERE pch3.post_parent = p0.ID AND apd3.purchase_price IS NOT NULL AND apd3.purchase_price > 0
) AND (NOT EXISTS(
SELECT pch1.ID FROM npcsite_posts pch1
WHERE pch1.post_parent = p0.ID AND pch1.post_type = 'product_variation'
) OR EXISTS(
SELECT pch2.ID FROM npcsite_posts pch2
LEFT JOIN npcsite_postmeta pmchild2 ON pch2.ID = pmchild2.post_id AND pmchild2.meta_key = '_manage_stock'
WHERE pch2.post_parent = p0.ID AND pmchild2.meta_value = 'no'
)), IF( ( (apd0.multi_inventory IS NULL OR apd0.multi_inventory <> 0) AND (apd0.price_per_inventory IS NULL OR apd0.price_per_inventory <> 0) AND ais.is_main = 0 AND aism.purchase_price > 0 AND aism.purchase_price IS NOT NULL AND aism.manage_stock = 1 ) OR ( apd0.purchase_price > 0 AND (ais.id IS NULL OR aism.id IS NULL OR ((apd0.multi_inventory IS NULL OR apd0.multi_inventory <> 0) AND apd0.price_per_inventory = 0)) ), 0, IF( ais.is_main = 0 AND (apd0.multi_inventory IS NULL OR apd0.multi_inventory <> 0), IF( aism.is_expired = 0 AND aism.manage_stock = 1 AND aism.stock_quantity > 0, aism.stock_quantity, 0 ), IF( ms.meta_value = 'yes', CAST( st.meta_value AS DECIMAL(10,6) ), 0 ) ) ), 0 ) ), 0 ) items_without_purchase_price
FROM npcsite_posts p0 LEFT JOIN npcsite_postmeta st ON p0.ID = st.post_id AND st.meta_key = 'stock'
LEFT JOIN npcsite_postmeta ms ON p0.ID = ms.post_id AND ms.meta_key = 'manage_stock'
LEFT JOIN npcsite_atum_product_data apd0 ON p0.ID = apd0.product_id
LEFT JOIN npcsite_atum_inventories ais ON p0.ID = ais.product_id AND ais.write_off=0 AND (apd0.multi_inventory IS NULL OR apd0.multi_inventory <> 0)
LEFT JOIN npcsite_atum_inventory_meta aism ON ais.id = aism.inventory_id
WHERE p0.ID IN ( SELECT npcsite_posts.ID
FROM npcsite_posts INNER JOIN npcsite_atum_product_data ON ( npcsite_posts.ID = npcsite_atum_product_data.product_id )
WHERE 1=1 AND (
npcsite_posts.ID NOT IN (
SELECT object_id
FROM npcsite_term_relationships
WHERE term_taxonomy_id IN (3)
)
) AND npcsite_posts.post_type IN ('product', 'product_variation') AND ((npcsite_posts.post_status = 'publish' OR npcsite_posts.post_status = 'future' OR npcsite_posts.post_status = 'private')) AND (
(
npcsite_atum_product_data.atum_stock_status IN ('instock','onbackorder')
)
)
GROUP BY npcsite_posts.ID
ORDER BY npcsite_posts.post_date DESC
)
GROUP BY p0.ID ) t;
can you please check it