Réinstallation & Mise à jour système
Indisponible
Ce produit n'est plus disponible.
Vérifier la disponibilité en magasin
-
19.95€
1267 - Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '='
WITH params AS ( SELECT 'RéPAPS4MAJSYST' AS product_model ), -- shortage from transfers (negative because it's stock to be moved out) tfts AS ( SELECT nt.product_model, nt.product_owner_id, nt.product_supplier_id, nt.product_price_stock, nt.product_origin AS product_location, -SUM( nt.product_quantity_requested - nt.product_quantity_declined - nt.product_quantity ) AS qty FROM netshop_transfer nt JOIN params p ON p.product_model = nt.product_model WHERE (nt.product_quantity_requested - nt.product_quantity_declined) > nt.product_quantity GROUP BY nt.product_model, nt.product_owner_id, nt.product_supplier_id, nt.product_price_stock, nt.product_origin ), -- physical stock (non-defect) stocks AS ( SELECT ns.product_model, ns.product_owner_id, ns.product_supplier_id, ns.product_price_stock, ns.product_location, SUM(ns.product_quantity) AS qty FROM netshop_stock ns JOIN params p ON p.product_model = ns.product_model WHERE ns.product_isdefect = 0 GROUP BY ns.product_model, ns.product_owner_id, ns.product_supplier_id, ns.product_price_stock, ns.product_location ), -- combine stock and transfer shortages merged AS ( SELECT * FROM stocks UNION ALL SELECT * FROM tfts ), -- net available per (owner, supplier, price, location) but only for visible shops and positive net stock_minus_transfer AS ( SELECT m.product_model, m.product_location, m.product_owner_id, m.product_supplier_id, m.product_price_stock, SUM(m.qty) AS product_quantity FROM merged m JOIN netshop_shops sh ON sh.shops_id = m.product_location WHERE sh.shops_visible = 1 GROUP BY m.product_model, m.product_location, m.product_owner_id, m.product_supplier_id, m.product_price_stock HAVING SUM(m.qty) > 0 ), -- NEW stock: both owner and supplier < 100000 stock_new AS ( SELECT s.product_location, nppl.price AS local_price, SUM(s.product_quantity) AS q_new FROM stock_minus_transfer s JOIN params p ON p.product_model = s.product_model JOIN products prd ON prd.products_model = s.product_model LEFT JOIN netshop_product_price_local nppl ON nppl.product_id = prd.products_id AND nppl.shop_id=s.product_location AND ABS(nppl.price_ht-prd.products_price)>0.10 WHERE s.product_owner_id < 100000 AND s.product_supplier_id < 100000 GROUP BY s.product_location ), -- USED stock: anything else; also compute min price there stock_used AS ( SELECT s.product_location, MIN(s.product_price_stock) AS min_price, SUM(s.product_quantity) AS q_used FROM stock_minus_transfer s JOIN params p ON p.product_model = s.product_model WHERE NOT (s.product_owner_id < 100000 AND s.product_supplier_id < 100000) GROUP BY s.product_location ), -- reservations split once into new vs used with conditional aggregation reservations AS ( SELECT c.customers_shop_id AS product_location, SUM(CASE WHEN nca.customers_alerts_used = 0 THEN -nca.customers_alerts_quantity ELSE 0 END) AS q_res_new, SUM(CASE WHEN nca.customers_alerts_used > 0 THEN -nca.customers_alerts_quantity ELSE 0 END) AS q_res_used FROM netshop_customers_alerts nca JOIN products p ON p.products_model = nca.customers_alerts_products_model JOIN params prm ON prm.product_model = p.products_model JOIN customers c ON c.customers_nbr = nca.customers_alerts_customers_nbr WHERE nca.customers_alerts_status < 3 GROUP BY c.customers_shop_id ) SELECT s.shops_id, nsd.shops_name, COALESCE(sn.q_new, 0) AS stock_new, COALESCE(r.q_res_new, 0) AS stock_new_reserved, su.min_price, COALESCE(su.q_used, 0) AS stock_used, COALESCE(r.q_res_used, 0) AS stock_used_reserved, sn.local_price AS stock_new_local_price FROM netshop_shops s JOIN netshop_shops_description nsd ON nsd.shops_id = s.shops_id AND nsd.shops_language_id = 1 LEFT JOIN stock_new sn ON sn.product_location = s.shops_id LEFT JOIN stock_used su ON su.product_location = s.shops_id LEFT JOIN reservations r ON r.product_location = s.shops_id WHERE s.shops_visible = 1 ORDER BY nsd.shops_name ASC;
[TEP STOP]