SQL requette - myteknow

SQL requette - myteknow RG.sql

-- ----------------------------------------------------------------
DROP FUNCTION IF EXISTS getIdEntrepriseFromPrestation; DELIMITER //
CREATE FUNCTION getIdEntrepriseFromPrestation(id_post_prestation INT) RETURNS INT BEGIN 
   DECLARE idEntreprise INT;
    SELECT pm.meta_value INTO idEntreprise FROM wp_612371_postmeta pm INNER JOIN wp_612371_posts p ON p.ID=pm.post_id 
        WHERE p.post_type = "prestation" AND pm.meta_key = "Entreprise" AND p.ID=id_post_prestation;
	RETURN idEntreprise;
END // DELIMITER ;

SELECT getIdEntrepriseFromPrestation(74511);
-- ----------------------------------------------------------------
DROP FUNCTION IF EXISTS getVisibiliteOfPrestation; DELIMITER //
CREATE FUNCTION getVisibiliteOfPrestation(id_post_prestation INT) RETURNS VARCHAR(30) BEGIN 
   DECLARE visibilite VARCHAR(30);
    SELECT pm.meta_value INTO visibilite FROM wp_612371_postmeta pm INNER JOIN wp_612371_posts p ON p.ID=pm.post_id 
        WHERE p.post_type = "prestation" AND pm.meta_key = "Visibilite" AND p.ID=id_post_prestation;
	RETURN visibilite;
END // DELIMITER ;

SELECT getVisibiliteOfPrestation(74511);
-- ----------------------------------------------------------------
-- Miasa ve?
DROP FUNCTION IF EXISTS getVisibiliteOfPrestationPost; DELIMITER //
CREATE FUNCTION getVisibiliteOfPrestationPost( id_post_software INT ) RETURNS INT BEGIN
   DECLARE visibiliteOfPrestation INT;
   SELECT meta_value
        INTO visibiliteOfPrestation
        FROM   wp_612371_posts p
               LEFT JOIN wp_612371_postmeta pm
                      ON p.id = pm.post_id
        WHERE  post_type = "prestation"
               AND id IN (
               -- showListPrestationOfSoftware
				SELECT p.ID FROM wp_612371_postmeta pm INNER JOIN wp_612371_posts p ON p.ID=pm.post_id 
							WHERE p.post_type = "prestation" AND meta_key = "Progiciel" AND  meta_value = id_post_software
				)
               AND meta_key = "visibilite";
   RETURN visibiliteOfPrestation;
END; // DELIMITER ;
SELECT getVisibiliteOfPrestationPost(65681);
-- ----------------------------------------------------------------
DROP FUNCTION IF EXISTS getVisibiliteQuotaEcheanceOfEntreprisePost; DELIMITER //
CREATE FUNCTION getVisibiliteQuotaEcheanceOfEntreprisePost( id_post_entreprise INT ) RETURNS VARCHAR(40) BEGIN
   DECLARE visibiliteQuotaEcheanceOfEntreprisePost VARCHAR(40);
   SELECT meta_value
        INTO visibiliteQuotaEcheanceOfEntreprisePost
        FROM   wp_612371_postmeta
        WHERE  meta_key = "Visibilite_quota_echeance"
               AND post_id = id_post_entreprise;
   
   RETURN visibiliteQuotaEcheanceOfEntreprisePost;
END; // DELIMITER ;
SELECT getVisibiliteQuotaEcheanceOfEntreprisePost(65681);
-- ----------------------------------------------------------------
DROP FUNCTION IF EXISTS getVisibiliteQuotaOfEntreprisePost; DELIMITER //
CREATE FUNCTION getVisibiliteQuotaOfEntreprisePost( id_post_entreprise INT ) RETURNS VARCHAR(40) BEGIN
   DECLARE visibiliteQuotaOfEntreprisePost VARCHAR(40);
   SELECT meta_value
		INTO visibiliteQuotaOfEntreprisePost
        FROM   wp_612371_postmeta
        WHERE  meta_key = "Visibilite_quota"
               AND post_id = id_post_entreprise;
   RETURN visibiliteQuotaOfEntreprisePost;
END; // DELIMITER ;

SELECT getVisibiliteQuotaOfEntreprisePost(65681);
-- ----------------------------------------------------------------
DROP FUNCTION IF EXISTS isThisPrestationVisible; DELIMITER //
CREATE FUNCTION isThisPrestationVisible( id_post_Prestation INT ) RETURNS INT BEGIN
    DECLARE isThisPrestationVisibleRes INT;
    SELECT (
		CASE 
			WHEN (
				getVisibiliteOfPrestation(id_post_Prestation) = "Visible"
				AND CONVERT(IFNULL(getVisibiliteQuotaOfEntreprisePost(getIdEntrepriseFromPrestation(id_post_Prestation)), 0), SIGNED INTEGER) > 0
				AND CONVERT(getVisibiliteQuotaEcheanceOfEntreprisePost(getIdEntrepriseFromPrestation(id_post_Prestation)), DATE) >= CURDATE()
			) THEN 1
			ELSE 0
		END
    ) INTO isThisPrestationVisibleRes;
   RETURN isThisPrestationVisibleRes;
END; // DELIMITER ;

SELECT isThisPrestationVisible(77426);
-- ----------------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS showListPrestationOfSoftware; DELIMITER //
CREATE PROCEDURE showListPrestationOfSoftware(IN id_post_software INT) BEGIN 
    SELECT 
		-- *, 
		-- getIdEntrepriseFromPrestation(p.ID) AS IdEntrepriseFromPrestation, 
        -- getVisibiliteOfPrestation(p.ID) AS VisibiliteOfPrestation, 
        -- getVisibiliteOfPrestation(p.ID) = "Visible" AS VisibiliteOfPrestation_CheckedRG, 
		-- CONVERT(IFNULL(getVisibiliteQuotaOfEntreprisePost(getIdEntrepriseFromPrestation(p.ID)), 0), SIGNED INTEGER) > 0 AS VisibiliteQuotaOfEntreprise_CheckedRG,
		-- CONVERT(getVisibiliteQuotaEcheanceOfEntreprisePost(getIdEntrepriseFromPrestation(p.ID)), DATE) AS VisibiliteQuotaEcheanceOfEntreprise,
		-- CONVERT(getVisibiliteQuotaEcheanceOfEntreprisePost(getIdEntrepriseFromPrestation(p.ID)), DATE) >= CURDATE() AS VisibiliteQuotaEcheanceOfEntreprise_CheckedRG,
        MAX(isThisPrestationVisible(p.ID)) AS ifExistAtLeastOnePrestationVisible
	FROM wp_612371_postmeta pm INNER JOIN wp_612371_posts p ON p.ID=pm.post_id 
        WHERE p.post_type = "prestation" AND meta_key = "Progiciel" AND  meta_value = id_post_software;
END // DELIMITER ;

CALL showListPrestationOfSoftware(72481);
-- ----------------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS ifExistAtLeastOnePrestationVisible; DELIMITER //
CREATE FUNCTION ifExistAtLeastOnePrestationVisible(id_post_software INT) RETURNS INT BEGIN 
	DECLARE ifExistAtLeastOnePrestationVisibleRes INT;
    SELECT 
		-- *, 
		-- getIdEntrepriseFromPrestation(p.ID) AS IdEntrepriseFromPrestation, 
        -- getVisibiliteOfPrestation(p.ID) AS VisibiliteOfPrestation, 
        -- getVisibiliteOfPrestation(p.ID) = "Visible" AS VisibiliteOfPrestation_CheckedRG, 
		-- CONVERT(IFNULL(getVisibiliteQuotaOfEntreprisePost(getIdEntrepriseFromPrestation(p.ID)), 0), SIGNED INTEGER) > 0 AS VisibiliteQuotaOfEntreprise_CheckedRG,
		-- CONVERT(getVisibiliteQuotaEcheanceOfEntreprisePost(getIdEntrepriseFromPrestation(p.ID)), DATE) AS VisibiliteQuotaEcheanceOfEntreprise,
		-- CONVERT(getVisibiliteQuotaEcheanceOfEntreprisePost(getIdEntrepriseFromPrestation(p.ID)), DATE) >= CURDATE() AS VisibiliteQuotaEcheanceOfEntreprise_CheckedRG,
        MAX(isThisPrestationVisible(p.ID)) INTO ifExistAtLeastOnePrestationVisibleRes
	FROM wp_612371_postmeta pm INNER JOIN wp_612371_posts p ON p.ID=pm.post_id 
        WHERE p.post_type = "prestation" AND meta_key = "Progiciel" AND  meta_value = id_post_software;
	RETURN ifExistAtLeastOnePrestationVisibleRes;
END // DELIMITER ;

SELECT ifExistAtLeastOnePrestationVisible(72481);
-- ----------------------------------------------------------------------------------------------------

-- ---------------------------------------------------------------------------------------------------------------------

-- SET @ID_POST_ORIGIN = 74527;  -- Prestation
-- SET @ID_POST_ORIGIN = 69368;  -- Software
-- SET @ID_POST_ORIGIN = 65086;  -- Company avec quota+echeance+visibilité
SET @ID_POST_ORIGIN = 72481;  -- Software avec 2 prestations

SELECT p.id,
       p.post_title,
       p.post_content,
       p.post_type,
       (SELECT meta_value FROM wp_612371_postmeta WHERE meta_key="Visibilite" AND post_id=@ID_POST_ORIGIN) as Visibilite_company,
	   (SELECT meta_value FROM wp_612371_postmeta WHERE meta_key="Visibilite_quota" AND post_id=@ID_POST_ORIGIN) as Visibilite_quota_company,
	   (SELECT meta_value FROM wp_612371_postmeta WHERE meta_key="Visibilite_quota_echeance" AND post_id=@ID_POST_ORIGIN) as Visibilite_quota_echeance_company,
       getVisibiliteOfPrestationPost(@ID_POST_ORIGIN) AS Visibilite_software,
       ifExistAtLeastOnePrestationVisible(@ID_POST_ORIGIN) AS Visibilite_software,
       getVisibiliteQuotaEcheanceOfEntreprisePost(@ID_POST_ORIGIN) AS Visibilite_quota_echeance_software,
       getVisibiliteQuotaOfEntreprisePost(@ID_POST_ORIGIN) AS Visibilite_quota_software
FROM   `wp_612371_posts` p
WHERE  p.id = @ID_POST_ORIGIN 
    
/*
74527 => Prestation

*/