-- apps_master.spSelectProductRekomendasiPharosNonPharosNutrisains (PROCEDURE)
BEGIN
	SELECT IFNULL(SupplyFrom, '981') 
	INTO @gudangid
	FROM apps_master.`m_outlet1` 
	WHERE OutletID = outletID1;
	SELECT
	       ppp.ProductPharosID,
	       ppp.ProductNonPharosID,
	       p.ProductID,
	       p1.ProductID,
	       p.ProductImage AS ProductImagePharos,
	       p1.ProductImage AS ProductImageNonPharos,
	       p.ProductName AS ProductNamePharos,
	       p1.ProductName AS ProductNameNonPharos,
	       p.ProductID AS ProductIDPharos,
	       p1.ProductID AS ProductIDNonPharos,
	       CASE WHEN pop.ProductID IS NOT NULL THEN pop.ProductPriceAfterDiscount
	       ELSE p.ProductPriceAfterDiscount END AS ProductPriceAfterDiscountPharos,
	       CASE WHEN pop.ProductID IS NOT NULL THEN pop.ProductPriceAfterDiscount
	       ELSE p1.ProductPriceAfterDiscount END AS ProductPriceAfterDiscountNonPharos,
	       p.ProductPrice AS ProductPricePharos,
	       p1.ProductPrice AS ProductPriceNonPharos,
	       g.GenericName AS GenericNamePharos,
	       g1.GenericName AS GenericNameNonPharos,
	       p.ProductTypeClass AS ProductTypePharos,
	       p1.ProductTypeClass AS ProductTypeNonPharos,
	       IFNULL(ps.ProductStock,0) AS ProductStockPharos,
	       IFNULL(ps1.ProductStock,0) AS ProductStockNonPharos
	FROM apps_master.recommendation_product_outlet_showed rpos
	LEFT JOIN apps_master.m_pharos_product_paired ppp   ON rpos.ProductID = ppp.ProductNonPharosID  OR rpos.ProductID = ppp.ProductPharosID
	LEFT JOIN apps_master.m_product1 p              ON p.ProductID = ppp.ProductPharosID         
	LEFT JOIN apps_master.m_product1 p1         ON p1.ProductID = ppp.ProductNonPharosID
	LEFT JOIN apps_master.m_generic g               ON ppp.GenericID = g.GenericID
	LEFT JOIN apps_master.m_generic g1              ON ppp.GenericID = g1.GenericID
	LEFT JOIN apps_master.pharmanet_stock ps        ON ps.OutletID = @gudangid AND ps.ProductID = ppp.ProductPharosID
	LEFT JOIN apps_master.pharmanet_stock ps1       ON ps1.OutletID = @gudangid AND ps1.ProductID = ppp.ProductNonPharosID
	LEFT JOIN apps_master.product_outlet_promo pop      ON pop.ProductID = ppp.ProductPharosID      OR pop.ProductID = ppp.ProductNonPharosID AND pop.OutletID = outletID1
	WHERE 
	    rpos.ActiveYN = 'Y' 
	    AND 
	    rpos.OutletID = outletID1 
	    AND 
	    ppp.ActiveYN ='Y' 
	GROUP BY 
	    ProductNamePharos, 
	    ProductNameNonPharos
	    
	UNION
	SELECT
	       NULL AS ProductPharosID,
	       p1.ProductID AS ProductNonPharosID,
	       NULL AS ProductID,
	       p1.ProductID,
	       NULL AS ProductImagePharos,
	       p1.ProductImage AS ProductImageNonPharos,
	       NULL AS ProductNamePharos,
	       p1.ProductName AS ProductNameNonPharos,
	       NULL AS ProductIDPharos,
	       p1.ProductID AS ProductIDNonPharos,
	       IFNULL(p1.ProductPriceAfterDiscount,0) AS ProductPriceAfterDiscountPharos,
	       CASE WHEN pop.ProductID IS NOT NULL THEN pop.ProductPriceAfterDiscount
	       ELSE p1.ProductPriceAfterDiscount END AS ProductPriceAfterDiscountNonPharos,
	       IFNULL(p1.ProductPrice,0) AS ProductPricePharos,
	       p1.ProductPrice AS ProductPriceNonPharos,
	       NULL AS GenericNamePharos,
	       g.GenericName AS GenericNameNonPharos,
	       NULL AS ProductTypePharos,
	       p1.ProductTypeClass AS ProductTypeNonPharos,
	       IFNULL(ps.ProductStock,0) AS ProductStockPharos,
	       IFNULL(ps.ProductStock,0) AS ProductStockNonPharos
	FROM apps_master.m_product1 p1          
	LEFT JOIN apps_master.recommendation_product_outlet_showed rpos ON rpos.ProductID = p1.ProductID
	LEFT JOIN apps_master.m_product3 p3             ON p1.ProductID = p3.ProductID
	LEFT JOIN apps_master.product_outlet_promo pop      ON pop.ProductID = p1.ProductID AND pop.OutletID = outletID1
	LEFT JOIN apps_master.m_generic g               ON p1.GenericID = g.GenericID
	LEFT JOIN apps_master.pharmanet_stock ps        ON ps.OutletID = @gudangid AND ps.ProductID = p1.ProductID
	WHERE 
	    p3.PharosYN = 'Y'
	    AND
	    p1.Divisi IN ('NS1')
	    AND 
	    p1.ActiveYN ='Y'
	    AND 
	    rpos.OutletID = outletID1
	    AND NOT EXISTS
		(
		    SELECT 
			ppp.ProductPharosID,
			ppp.ProductNonPharosID
		    FROM apps_master.recommendation_product_outlet_showed rpos
		    LEFT JOIN apps_master.m_pharos_product_paired ppp 
		    ON rpos.ProductID = ppp.ProductNonPharosID OR rpos.ProductID = ppp.ProductPharosID
		    WHERE 
			rpos.ActiveYN = 'Y'
			AND
			rpos.OutletID = outletID1
			AND
			ppp.ActiveYN = 'Y'
		)
	GROUP BY 
	    ProductNamePharos, 
	    ProductNameNonPharos
	UNION 
	SELECT
	       p1.ProductID AS ProductPharosID,
	       NULL AS ProductNonPharosID,
	       p1.ProductID AS ProductID,
	       NULL AS ProductID,
	       p1.ProductImage AS ProductImagePharos,
	       NULL AS ProductImageNonPharos,
	       p1.ProductName AS ProductNamePharos,
	       NULL AS ProductNameNonPharos,
	       p1.ProductID AS ProductIDPharos,
	       NULL AS ProductIDNonPharos,
	       CASE WHEN pop.ProductID IS NOT NULL THEN pop.ProductPriceAfterDiscount
	       ELSE p1.ProductPriceAfterDiscount END AS ProductPriceAfterDiscountPharos,
	       IFNULL(p1.ProductPrice,0) AS ProductPriceAfterDiscountNonPharos,
	       p1.ProductPrice AS ProductPricePharos,
	       IFNULL(p1.ProductPrice,0) AS ProductPriceNonPharos,
	       g.GenericName AS GenericNamePharos,
	       NULL AS GenericNameNonPharos,
	       p1.ProductTypeClass AS ProductTypePharos,
	       NULL AS ProductTypeNonPharos,
	       IFNULL(ps.ProductStock,0) AS ProductStockPharos,
	       IFNULL(ps.ProductStock,0) AS ProductStockNonPharos
	FROM apps_master.m_product1 p1          
	LEFT JOIN apps_master.recommendation_product_outlet_showed rpos ON rpos.ProductID = p1.ProductID
	LEFT JOIN apps_master.m_product3 p3             ON p1.ProductID = p3.ProductID
	LEFT JOIN apps_master.product_outlet_promo pop      ON pop.ProductID = p1.ProductID AND pop.OutletID = outletID1
	LEFT JOIN apps_master.m_generic g               ON p1.GenericID = g.GenericID
	LEFT JOIN apps_master.pharmanet_stock ps        ON ps.OutletID = @gudangid AND ps.ProductID = p1.ProductID
	WHERE 
	    p3.PharosYN = 'Y'
	    AND
	    p1.Divisi NOT IN ('NS1')
	    AND 
	    p1.ActiveYN ='Y'
	    AND
	    rpos.OutletID = outletID1
	     AND NOT EXISTS
		(
		    SELECT 
			ppp.ProductPharosID,
			ppp.ProductNonPharosID
		    FROM apps_master.recommendation_product_outlet_showed rpos
		    LEFT JOIN apps_master.m_pharos_product_paired ppp 
		    ON rpos.ProductID = ppp.ProductNonPharosID OR rpos.ProductID = ppp.ProductPharosID
		    WHERE 
			rpos.ActiveYN = 'Y'
			AND
			rpos.OutletID = outletID1
			AND
			ppp.ActiveYN = 'Y'
		)
	GROUP BY 
	    ProductNamePharos, 
	    ProductNameNonPharos;
    
END