Skip to content

Instantly share code, notes, and snippets.

@PrestaEdit

PrestaEdit/gist:272cc6af0fcc75444f80

Last active October 26, 2016 13:17
Show Gist options
  • Save PrestaEdit/272cc6af0fcc75444f80 to your computer and use it in GitHub Desktop.
Save PrestaEdit/272cc6af0fcc75444f80 to your computer and use it in GitHub Desktop.
Download ZIP
getCmpResults
Raw
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() *******/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment