getCmpResults

gistfile1.php

/******* BEGIN: getCmpResults() *******/
    public function getCmpResults()
    {
        $sql = 'SELECT *, "category" as type
                FROM (
                    SELECT
                        queryCmp.sFilename as new_name, queryCmp.sFilePath as path, queryCmpFinal.id_category, "" as id_product, "" as id_feature_value, queryCmpFinal.id_lang, queryCmpFinal.category_name as old_name, "" as field, "" as value,
                        CASE WHEN queryCmp.sFilename is null THEN "delete"
                             WHEN queryCmpFinal.category_name is null THEN "create"
                             WHEN queryCmp.sFilename <> queryCmpFinal.category_name THEN "update"
                        END AS action
                    FROM (
                        SELECT b.*
                        FROM (
                            SELECT DISTINCT sFilename, sFilePath, FilessTag, FilessValue, IFNULL(tags.field, FilessTag) as field, sType
                            FROM (
                                SELECT Files.sFilename as sFilename, Files.sPathName as sFilePath, Files.sTag as FilessTag, Files.sValue as FilessValue, Files.sType as sType
                                FROM '._DB_PREFIX_.'structuredfiles_content_files Files
                                WHERE sType = "category"
                            ) as a
                            LEFT JOIN '._DB_PREFIX_.'structuredfiles_field tags
                            ON a.FilessTag = tags.tag
                        ) as b
                    ) as queryCmp
                    LEFT JOIN (
                        SELECT c.id_category, '._DB_PREFIX_.'category_lang.id_lang,'._DB_PREFIX_.'category_lang.`name` as category_name
                        FROM '._DB_PREFIX_.'category c
                        INNER JOIN '._DB_PREFIX_.'category_lang ON c.id_category = '._DB_PREFIX_.'category_lang.id_category
                        WHERE c.id_parent != 0 AND c.is_root_category != 1
                    ) AS queryCmpFinal
                    ON queryCmp.sFileName = queryCmpFinal.category_name
                ) listing
                WHERE (listing.action = "create" OR listing.action = "update" OR listing.action = "delete")

                UNION

                SELECT *, "category" as type
                FROM (
                    SELECT
                        queryCmp.sFilename as new_name, queryCmp.sFilePath as path, queryCmpFinal.id_category, "" as id_product, "" as id_feature_value, queryCmpFinal.id_lang, queryCmpFinal.category_name as old_name, "" as field, "" as value,
                        CASE WHEN queryCmp.sFilename is null AND queryCmpFinal.active != 0 THEN "delete"
                            WHEN queryCmpFinal.category_name is null THEN "create"
                            WHEN queryCmp.sFilename <> queryCmpFinal.category_name THEN "update"
                        END AS action
                        FROM (
                            SELECT b.*
                            FROM (
                                SELECT DISTINCT sFilename, sFilePath, FilessTag, FilessValue, IFNULL(tags.field, FilessTag) as field, sType
                                FROM (
                                    SELECT Files.sFilename as sFilename, Files.sPathName as sFilePath, Files.sTag as FilessTag, Files.sValue as FilessValue, Files.sType as sType
                                    FROM '._DB_PREFIX_.'structuredfiles_content_files Files
                                    WHERE sType = "category"
                                ) as a
                                LEFT JOIN '._DB_PREFIX_.'structuredfiles_field tags
                                ON a.FilessTag = tags.tag
                            ) as b
                        ) as queryCmp
                        RIGHT JOIN (
                            SELECT c.id_category, c.active, '._DB_PREFIX_.'category_lang.id_lang,'._DB_PREFIX_.'category_lang.`name` as category_name
                            FROM '._DB_PREFIX_.'category c
                            INNER JOIN '._DB_PREFIX_.'category_lang ON c.id_category = '._DB_PREFIX_.'category_lang.id_category
                            WHERE c.id_parent != 0 AND c.is_root_category != 1
                        ) AS queryCmpFinal
                        ON queryCmp.sFileName = queryCmpFinal.category_name
                ) listing
                WHERE (listing.action = "create" OR listing.action = "update" OR listing.action = "delete")

                UNION

                SELECT *, "product" as type
                FROM (
                    SELECT
                        queryCmp.sFilename as new_name, queryCmp.sFilePath as path, "" as id_category, queryCmpFinal.id_product, "" as id_feature_value, queryCmpFinal.id_lang, queryCmpFinal.product_name as old_name, "" as field, "" as value,
                        CASE WHEN queryCmp.sFilename is null THEN "delete"
                             WHEN queryCmpFinal.product_name is null THEN "create"
                             WHEN queryCmp.sFilename <> queryCmpFinal.product_name THEN "update"
                        END AS action
                    FROM (
                        SELECT b.*
                        FROM (
                            SELECT DISTINCT sFilename, sFilePath, FilessTag, FilessValue, IFNULL(tags.field, FilessTag) as field, sType
                            FROM (
                                SELECT Files.sFilename as sFilename, Files.sPathName as sFilePath, Files.sTag as FilessTag, Files.sValue as FilessValue, Files.sType as sType
                                FROM '._DB_PREFIX_.'structuredfiles_content_files Files
                                WHERE sType = "product"
                            ) as a
                            LEFT JOIN '._DB_PREFIX_.'structuredfiles_field tags
                            ON a.FilessTag = tags.tag
                        ) as b
                    ) as queryCmp
                    LEFT JOIN (
                        SELECT c.id_product, '._DB_PREFIX_.'product_lang.id_lang, '._DB_PREFIX_.'product_lang.`name` as product_name
                        FROM '._DB_PREFIX_.'product c
                        INNER JOIN '._DB_PREFIX_.'product_lang ON c.id_product = '._DB_PREFIX_.'product_lang.id_product
                    ) AS queryCmpFinal
                    ON queryCmp.sFileName = queryCmpFinal.product_name
                ) listing
                WHERE (listing.action = "create" OR listing.action = "update" OR listing.action = "delete")

                UNION

                SELECT *, "product" as type
                FROM (
                    SELECT
                        queryCmp.sFilename as new_name, queryCmp.sFilePath as path, "" as id_category, queryCmpFinal.id_product, "" as id_feature_value, queryCmpFinal.id_lang, queryCmpFinal.product_name as old_name, "" as field, "" as value,
                        CASE WHEN queryCmp.sFilename is null AND queryCmpFinal.active != 0 THEN "delete"
                             WHEN queryCmpFinal.product_name is null THEN "create"
                             WHEN queryCmp.sFilename <> queryCmpFinal.product_name THEN "update"
                        END AS action
                        FROM (
                            SELECT b.*
                            FROM (
                                SELECT DISTINCT sFilename, sFilePath, FilessTag, FilessValue, IFNULL(tags.field, FilessTag) as field, sType
                                FROM (
                                    SELECT Files.sFilename as sFilename, Files.sPathName as sFilePath, Files.sTag as FilessTag, Files.sValue as FilessValue, Files.sType as sType
                                    FROM '._DB_PREFIX_.'structuredfiles_content_files Files
                                    WHERE sType = "product"
                                ) as a
                                LEFT JOIN '._DB_PREFIX_.'structuredfiles_field tags
                                ON a.FilessTag = tags.tag
                            ) as b
                        ) as queryCmp
                        RIGHT JOIN (
                            SELECT c.id_product, c.active, '._DB_PREFIX_.'product_lang.id_lang, '._DB_PREFIX_.'product_lang.`name` as product_name
                            FROM '._DB_PREFIX_.'product c
                            INNER JOIN '._DB_PREFIX_.'product_lang ON c.id_product = '._DB_PREFIX_.'product_lang.id_product
                        ) AS queryCmpFinal
                        ON queryCmp.sFileName = queryCmpFinal.product_name
                ) listing
                WHERE (listing.action = "create" OR listing.action = "update" OR listing.action = "delete")

                UNION

                SELECT *, "feature" as type
                FROM (
                    SELECT
                        queryCmp.sFilename as new_name,
                        queryCmp.sFilePath as path,
                        "" as id_category,
                        IFNULL (queryCmpFinal.id_product, cmpProducts.id_product) as id_product,
                        queryCmpFinal.id_feature_value,
                        queryCmpFinal.id_lang,
                        queryCmpFinal.product_name as old_name,
                        queryCmp.field,
                        queryCmp.FilessValue as value,
                        CASE
                            WHEN queryCmp.sFilename is null THEN "delete"
                            WHEN queryCmpFinal.product_name is null THEN "create"
                            WHEN queryCmp.sFilename <> queryCmpFinal.product_name THEN "update"
                        END AS action
                    FROM (
                        SELECT b.*
                        FROM (
                            SELECT DISTINCT sFilename, sFilePath, FilessTag, FilessValue, IFNULL(tags.field, FilessTag) as field, sType
                            FROM (
                                SELECT Files.sFilename as sFilename, Files.sPathName as sFilePath, Files.sTag as FilessTag, Files.sValue as FilessValue, Files.sType as sType
                                FROM '._DB_PREFIX_.'structuredfiles_content_files Files
                                WHERE sType = "feature"
                            ) as a
                                    LEFT JOIN '._DB_PREFIX_.'structuredfiles_field tags
                                    ON a.FilessTag = tags.tag
                                ) as b
                            ) as queryCmp
                            LEFT JOIN (
                                SELECT
                                    '._DB_PREFIX_.'product.id_product,
                                    '._DB_PREFIX_.'feature_value_lang.id_feature_value as id_feature_value,
                                    '._DB_PREFIX_.'product_lang.`name` as product_name,
                                    '._DB_PREFIX_.'feature_product.id_feature,
                                    '._DB_PREFIX_.'feature_lang.`name` as feature_name,
                                    '._DB_PREFIX_.'feature_value_lang.`value` as feature_value,
                                    '._DB_PREFIX_.'feature_value_lang.id_lang
                                FROM '._DB_PREFIX_.'product
                                INNER JOIN '._DB_PREFIX_.'product_lang ON '._DB_PREFIX_.'product.id_product = '._DB_PREFIX_.'product_lang.id_product
                                INNER JOIN '._DB_PREFIX_.'category_product ON '._DB_PREFIX_.'product.id_product = '._DB_PREFIX_.'category_product.id_product
                                INNER JOIN '._DB_PREFIX_.'category_lang ON '._DB_PREFIX_.'category_product.id_category = '._DB_PREFIX_.'category_lang.id_category and '._DB_PREFIX_.'category_lang.id_lang  = '._DB_PREFIX_.'product_lang.id_lang
                                INNER JOIN '._DB_PREFIX_.'category ON '._DB_PREFIX_.'category_lang.id_category = '._DB_PREFIX_.'category.id_category and '._DB_PREFIX_.'product_lang.id_lang = '._DB_PREFIX_.'category_lang.id_lang
                                RIGHT JOIN '._DB_PREFIX_.'feature_product ON '._DB_PREFIX_.'product.id_product = '._DB_PREFIX_.'feature_product.id_product
                                LEFT JOIN '._DB_PREFIX_.'feature_value_lang ON '._DB_PREFIX_.'feature_value_lang.id_feature_value = '._DB_PREFIX_.'feature_product.id_feature_value and '._DB_PREFIX_.'product_lang.id_lang = '._DB_PREFIX_.'feature_value_lang.id_lang
                                LEFT JOIN '._DB_PREFIX_.'feature_lang ON '._DB_PREFIX_.'feature_lang.id_feature = '._DB_PREFIX_.'feature_product.id_feature and '._DB_PREFIX_.'product_lang.id_lang = '._DB_PREFIX_.'feature_value_lang.id_lang
                            ) AS queryCmpFinal
                            ON queryCmp.sFileName = queryCmpFinal.product_name
                            LEFT JOIN (
                                SELECT DISTINCT
                                             '._DB_PREFIX_.'product.id_product,
                                             '._DB_PREFIX_.'product_lang.`name` as product_name
                                FROM '._DB_PREFIX_.'product
                                INNER JOIN '._DB_PREFIX_.'product_lang ON '._DB_PREFIX_.'product.id_product = '._DB_PREFIX_.'product_lang.id_product
                            ) AS cmpProducts
                            ON queryCmp.sFileName = cmpProducts.product_name
                ) listing
                WHERE (listing.action = "create" OR listing.action = "update" OR listing.action = "delete")

                UNION

                SELECT *, "feature" as type
                FROM (
                    SELECT
                        queryCmp.sFilename as new_name,
                        queryCmp.sFilePath as path,
                        "" as id_category,
                        IFNULL (queryCmpFinal.id_product, cmpProducts.id_product) as id_product,
                        queryCmpFinal.id_feature_value,
                        queryCmpFinal.id_lang,
                        queryCmpFinal.product_name as old_name,
                        queryCmp.field,
                        queryCmp.FilessValue as value,
                        CASE
                            WHEN queryCmp.sFilename is null THEN "delete"
                            WHEN queryCmpFinal.product_name is null THEN "create"
                            WHEN queryCmp.sFilename <> queryCmpFinal.product_name THEN "update"
                        END AS action
                    FROM (
                        SELECT b.*
                        FROM (
                            SELECT DISTINCT sFilename, sFilePath, FilessTag, FilessValue, IFNULL(tags.field, FilessTag) as field, sType
                            FROM (
                                SELECT Files.sFilename as sFilename, Files.sPathName as sFilePath, Files.sTag as FilessTag, Files.sValue as FilessValue, Files.sType as sType
                                FROM '._DB_PREFIX_.'structuredfiles_content_files Files
                                WHERE sType = "feature"
                            ) as a
                            LEFT JOIN '._DB_PREFIX_.'structuredfiles_field tags
                            ON a.FilessTag = tags.tag
                        ) as b
                    ) as queryCmp
                    RIGHT JOIN (
                        SELECT
                            '._DB_PREFIX_.'product.id_product,
                            '._DB_PREFIX_.'feature_value_lang.id_feature_value as id_feature_value,
                            '._DB_PREFIX_.'product_lang.`name` as product_name,
                            '._DB_PREFIX_.'feature_product.id_feature,
                            '._DB_PREFIX_.'feature_lang.`name` as feature_name,
                            '._DB_PREFIX_.'feature_value_lang.`value` as feature_value,
                            '._DB_PREFIX_.'feature_value_lang.id_lang
                        FROM '._DB_PREFIX_.'product
                        INNER JOIN '._DB_PREFIX_.'product_lang ON '._DB_PREFIX_.'product.id_product = '._DB_PREFIX_.'product_lang.id_product
                        INNER JOIN '._DB_PREFIX_.'category_product ON '._DB_PREFIX_.'product.id_product = '._DB_PREFIX_.'category_product.id_product
                        INNER JOIN '._DB_PREFIX_.'category_lang ON '._DB_PREFIX_.'category_product.id_category = '._DB_PREFIX_.'category_lang.id_category and '._DB_PREFIX_.'category_lang.id_lang  = '._DB_PREFIX_.'product_lang.id_lang
                        INNER JOIN '._DB_PREFIX_.'category ON '._DB_PREFIX_.'category_lang.id_category = '._DB_PREFIX_.'category.id_category and '._DB_PREFIX_.'product_lang.id_lang = '._DB_PREFIX_.'category_lang.id_lang
                        RIGHT JOIN '._DB_PREFIX_.'feature_product ON '._DB_PREFIX_.'product.id_product = '._DB_PREFIX_.'feature_product.id_product
                        LEFT JOIN '._DB_PREFIX_.'feature_value_lang ON '._DB_PREFIX_.'feature_value_lang.id_feature_value = '._DB_PREFIX_.'feature_product.id_feature_value and '._DB_PREFIX_.'product_lang.id_lang = '._DB_PREFIX_.'feature_value_lang.id_lang
                        LEFT JOIN '._DB_PREFIX_.'feature_lang ON '._DB_PREFIX_.'feature_lang.id_feature = '._DB_PREFIX_.'feature_product.id_feature and '._DB_PREFIX_.'product_lang.id_lang = '._DB_PREFIX_.'feature_value_lang.id_lang
                    ) AS queryCmpFinal
                    ON queryCmp.sFileName = queryCmpFinal.product_name AND queryCmp.FilessTag = queryCmpFinal.feature_name AND queryCmp.FilessValue = queryCmpFinal.feature_value
                ) listing';

        return Db::getInstance()->executeS($sql);
    }
    /******* END: getCmpResults() *******/