SQL Queries
Get product series and lines by assortment
select group_concat(`t1`.`uid`, ",", `t2`.`uid`)
from `tx_fhproductcatalog_domain_model_productline` `t1`
join `tx_fhproductcatalog_domain_model_productline` `t2` on `t1`.`uid` = `t2`.`parent`
where `t1`.`assortment` = @uid;
Get corresponding products and variants
select group_concat(`t1`.`uid`, ",", `t2`.`uid`)
from `tx_fhproductcatalog_domain_model_product` `t1`
join `tx_fhproductcatalog_domain_model_product` `t2` on `t1`.`uid` = `t2`.`parent`
where `t1`.`productline` in (@priorResult);
Get corresponding image with video wall flag NOT set (detailFlag: 1, listFlag: 2, videoWallFlag: 4)
select `t2`.`identifier`
from `sys_file_reference` `t1`
join `sys_file` `t2` on `t1`.`uid_local` = `t2`.`uid`
where `t1`.`uid_foreign` in (@priorResult) and not `t1`.`fhpc_display` & 4
order by `t2`.`identifier` order by field(`product`.`eb_article_number`, 2758803, 2758804);
Get Series + Suppliername and UID for CSV
SELECT concat(t1.title, " - ", t2.title), t1.uid
FROM `tx_fhproductcatalog_domain_model_productline` AS t1
JOIN `tx_fhproductcatalog_domain_model_supplier` AS t2 ON t1.supplier = t2.uid
WHERE t1.record_type = 0
Get Series + Suppliername + Assortment and UID for CSV
SELECT concat(t1.title, " - ", t2.title, " <", t4.title, ">") AS 'Title', '' AS 'Placeholder', t1.uid AS 'Series'
FROM `tx_fhproductcatalog_domain_model_productline` AS t1
JOIN `tx_fhproductcatalog_domain_model_supplier` AS t2 ON t1.supplier = t2.uid
JOIN `sys_category` AS t4 ON t4.uid = t1.assortment
WHERE t1.record_type = 0 AND t1.deleted = 0 AND t1.title IN ('Trend Line')
Get Series + Suppliername [optional brandname] + Assortment and UID for CSV
SELECT concat(t1.title, " - ", concat(t2.title, IF(t1.brand > 0, (SELECT concat(" [", t5.title, "]") FROM `tx_fhproductcatalog_domain_model_supplier` AS t5 WHERE t5.uid = t1.brand), "")), " <", t4.title, ">") AS 'Title',
'' AS 'Placeholder',
t1.uid AS 'Series'
FROM `tx_fhproductcatalog_domain_model_productline` AS t1
JOIN `tx_fhproductcatalog_domain_model_supplier` AS t2 ON t1.supplier = t2.uid
JOIN `sys_category` AS t4 ON t4.uid = t1.assortment
WHERE t1.record_type = 0 AND t1.deleted = 0 AND t1.brand IN (60)
ORDER BY t1.title
Get first slide images of product series
SELECT concat(t4.title, " - ", t5.title), t3.identifier
FROM `tx_fhproductcatalog_domain_model_slide` AS t1
JOIN `tx_fhproductcatalog_domain_model_productline` AS t4 ON t1.productline = t4.uid
JOIN `tx_fhproductcatalog_domain_model_supplier` AS t5 ON t4.supplier = t5.uid
LEFT JOIN `sys_file_reference` AS t2 ON t1.uid = t2.uid_foreign AND t2.tablenames = 'tx_fhproductcatalog_domain_model_slide' AND t2.fieldname = 'images' AND t2.hidden = 0 AND t2.deleted = 0
RIGHT JOIN `sys_file` AS t3 ON t2.uid_local = t3.uid
WHERE t1.sorting = 1 AND t1.images > 0
Get products where number of configuration groups mismatch those of configuration attributes set!
SELECT *
FROM `tx_fhproductcatalog_domain_model_product`
WHERE record_type = 0 AND configuration_groups <> configuration_attributes
Get variants where number of configuration groups mismatch those of configuration attributes set!
SELECT t2.uid, t2.parent, t2.title, t1.eb_article_number, t2.eb_article_number AS 'child EB#', t2.deleted, t1.configuration_groups, t2.configuration_attributes
FROM `tx_fhproductcatalog_domain_model_product` AS t1
JOIN `tx_fhproductcatalog_domain_model_product` AS t2 ON t2.parent = t1.uid
WHERE t1.record_type = 0 AND t1.configuration_groups <> t2.configuration_attributes
Get products with manufacturer/series from containing page titles
SELECT pages2.`title` AS manufacturer, pages.`title` AS series, t1.`title`, t1.`eb_article_number`, t1.`lan`, t1.`ean`,
IF(t1.`parent` > 0, (SELECT t2.`eb_article_number` FROM `tx_fhproductcatalog_domain_model_product` AS t2 WHERE t2.`uid` = t1.`parent`), '') AS product
FROM `tx_fhproductcatalog_domain_model_product` AS t1
LEFT JOIN pages AS pages ON pages.uid = t1.pid
LEFT JOIN pages AS pages2 ON pages2.uid = pages.pid
WHERE t1.`deleted` = 0 AND t1.`hidden` = 0 AND IF(t1.`record_type` > 0 AND t1.`parent` = 0, 1 = 2, 1 = 1)
ORDER BY t1.pid
+ Attribute values
SELECT pages2.`title` AS manufacturer, pages.`title` AS series, t1.`title`, t1.`eb_article_number`, t1.`lan`, t1.`ean`,
IF(t1.`parent` > 0, (SELECT t2.`eb_article_number` FROM `tx_fhproductcatalog_domain_model_product` AS t2 WHERE t2.`uid` = t1.`parent`), '') AS product,
IF(t1.`attribute_values` > 0, (SELECT group_concat((SELECT tam.`title` FROM `tx_fhproductcatalog_domain_model_attribute` AS tam WHERE tam.`uid` = tav.`attribute`), ': ', tav.`value`, '\n')
FROM `tx_fhproductcatalog_domain_model_attributevalue` AS tav
WHERE tav.`product` = t1.`uid`), '') AS attributes
FROM `tx_fhproductcatalog_domain_model_product` AS t1
LEFT JOIN pages AS pages ON pages.uid = t1.pid
LEFT JOIN pages AS pages2 ON pages2.uid = pages.pid
WHERE t1.`deleted` = 0 AND t1.`hidden` = 0 AND IF(t1.`record_type` > 0 AND t1.`parent` = 0, 1 = 2, 1 = 1)
ORDER BY t1.pid
+ Configuration attributes
SELECT pages2.`title` AS manufacturer, pages.`title` AS series, t1.`title`, t1.`eb_article_number`, t1.`lan`, t1.`ean`,
IF(t1.`parent` > 0, (SELECT t2.`eb_article_number` FROM `tx_fhproductcatalog_domain_model_product` AS t2 WHERE t2.`uid` = t1.`parent`), '') AS product,
IF(t1.`attribute_values` > 0, (SELECT group_concat((SELECT tam.`title` FROM `tx_fhproductcatalog_domain_model_attribute` AS tam WHERE tam.`uid` = tav.`attribute`), ': ', tav.`value` SEPARATOR '\n')
FROM `tx_fhproductcatalog_domain_model_attributevalue` AS tav
WHERE tav.`product` = t1.`uid`), '') AS attributes,
IF(t1.`configuration_attributes` > 0, (SELECT group_concat(tattr.`title`, ': ', tcattr.`title` SEPARATOR '\n')
FROM `tx_fhproductcatalog_product_configurationattributes_mm` AS pcamm
JOIN `tx_fhproductcatalog_domain_model_configurationattribute` AS tcattr ON tcattr.`uid` = pcamm.`uid_foreign`
JOIN `sys_category` AS tattr ON tattr.`uid` = tcattr.`cluster`
WHERE pcamm.`uid_local` = t1.`uid`), '') AS configuration
FROM `tx_fhproductcatalog_domain_model_product` AS t1
LEFT JOIN pages AS pages ON pages.uid = t1.pid
LEFT JOIN pages AS pages2 ON pages2.uid = pages.pid
WHERE t1.`deleted` = 0 AND t1.`hidden` = 0 AND IF(t1.`record_type` > 0 AND t1.`parent` = 0, 1 = 2, 1 = 1)
ORDER BY t1.pid
Sample -> Chip preparation
SELECT t1.uid, concat(t1.title, ' - ', t3.title, ' <', t2.title, '>')
FROM `tx_fhproductcatalog_domain_model_productline` AS t1
JOIN `sys_category` AS t2 ON t2.uid = t1.`assortment`
JOIN `tx_fhproductcatalog_domain_model_supplier` AS t3 ON t3.uid = t1.`supplier`
WHERE t1.title IN ('Alberta', 'Andromeda', 'Arbor', 'Cement', 'Cloud', 'Concept', 'Easy', 'Leeds', 'Louisiana', 'Metro', 'Piccadilly', 'Prag', 'Quartz', 'Quarzit', 'Rome', 'Saarlouis', 'Seattle', 'Terra', 'Terraquartz', 'Tira', 'Uni',
'Moderna Horizon', 'Moderna Comfort', 'Moderna Exquisit', 'Moderna Optima')
ORDER BY field(t1.title, 'Alberta', 'Andromeda', 'Arbor', 'Cement', 'Cloud', 'Concept', 'Easy', 'Leeds', 'Louisiana', 'Metro', 'Piccadilly', 'Prag', 'Quartz', 'Quarzit', 'Rome', 'Saarlouis', 'Seattle', 'Terra', 'Terraquartz', 'Tira', 'Uni',
'Moderna Horizon', 'Moderna Comfort', 'Moderna Exquisit', 'Moderna Optima');
Select products without any image assigned
SELECT t1.`eb_article_number` AS 'EB-Artikelnummer', t1.`title` AS 'Produkt-/Variantenname', t2.`title` AS 'Serie', t3.`title` AS 'Hersteller'
FROM `tx_fhproductcatalog_domain_model_product` AS t1
JOIN `pages` AS t2 ON t1.pid = t2.uid
JOIN `pages` AS t3 ON t2.pid = t3.uid
WHERE t1.images = 0 AND t1.deleted = 0
ORDER BY t3.title, t2.title
Correct file reference page uids
UPDATE `sys_file_reference` AS t1 JOIN `tx_fhproductcatalog_domain_model_product` AS t2 ON t2.uid = t1.`uid_foreign`
SET t1.pid=t2.pid
WHERE t1.deleted = 0 AND t1.hidden = 0 AND t1.`tablenames` = 'tx_fhproductcatalog_domain_model_product' AND NOT t2.pid = t1.pid;
UPDATE `sys_file_reference` AS t1 JOIN `tx_fhproductcatalog_domain_model_productline` AS t2 ON t2.uid = t1.`uid_foreign`
SET t1.pid=t2.pid
WHERE t1.deleted = 0 AND t1.hidden = 0 AND t1.`tablenames` = 'tx_fhproductcatalog_domain_model_productline' AND NOT t2.pid = t1.pid;
UPDATE `tx_fhproductcatalog_domain_model_attributevalue` AS t1 JOIN `tx_fhproductcatalog_domain_model_product` AS t2 ON t2.uid = t1.`product`
SET t1.pid=t2.pid
WHERE t1.deleted = 0 AND t1.hidden = 0 AND NOT t2.pid = t1.pid;
DB trash
select hidden non-archived series
select concat("LIEFERANT: ", `t3`.`title`, " | SERIE: ", `t1`.`title`) as 'series'
from `tx_fhproductcatalog_domain_model_productline` as `t1`
join `tx_fhproductcatalog_domain_model_supplier` as `t3` on `t1`.`supplier` = `t3`.`uid`
where `t1`.`hidden`
and `t1`.`record_type` = 0
and not `t1`.`archived`;
select hidden non-archived lineups
select concat("LIEFERANT: ", `t3`.`title`, " | SERIE: ", `t2`.`title`, " | AUSFÜHRUNG: ", `t1`.`title`) as 'info'
from `tx_fhproductcatalog_domain_model_productline` as `t1`
join `tx_fhproductcatalog_domain_model_productline` as `t2` on `t1`.`parent` = `t2`.`uid`
join `tx_fhproductcatalog_domain_model_supplier` as `t3` on `t2`.`supplier` = `t3`.`uid`
where `t1`.`hidden`
and `t1`.`record_type` = 1
and not `t1`.`archived`;
Mass Upload Products/Variants
SELECT product.`title` AS 'Produkt/Variante',
concat('#', product.`eb_article_number`, ' <', series.`title`, ' - ', supplier.`title`, '>') AS 'Zusatzinfo',
IF(productline.`record_type` = 0, productline.`uid`, productline.`parent`) AS 'Serien-ID',
0 AS '', # placeholder
IF(productline.`record_type` = 0, 0, productline.`uid`) AS 'Ausführungs-ID',
IF(product.`record_type` = 0, product.`uid`, product.`parent`) AS 'Produkt-ID',
IF(product.`record_type` = 0, 0, product.`uid`) AS 'Varianten-ID'
FROM `tx_fhproductcatalog_domain_model_product` AS product
JOIN `tx_fhproductcatalog_domain_model_productline` AS productline
ON productline.`uid` = if(product.`record_type` = 0, product.`productline`, (SELECT t3.`productline` FROM `tx_fhproductcatalog_domain_model_product` AS t3 WHERE t3.`uid` = product.`parent` LIMIT 1))
JOIN `tx_fhproductcatalog_domain_model_productline` AS series
ON series.`uid` = if(productline.`record_type` = 0, productline.`uid`, (SELECT t4.`uid` FROM `tx_fhproductcatalog_domain_model_productline` AS t4 WHERE t4.`uid` = productline.`parent` LIMIT 1))
JOIN `tx_fhproductcatalog_domain_model_supplier` AS supplier ON supplier.`uid` = series.`supplier`
WHERE series.`approved` = 1 AND product.`deleted` = 0 AND product.`hidden` = 0 AND product.`eb_article_number` IN (2758803, 2758804)
Figure out which series have NOT been assigned to any chip on 10400 instance
SELECT cc_series
FROM `tx_fhdigitalexposition_domain_model_chip` AS chip
WHERE `fe_group` = 2 AND `record_type` = 1; # Get series uid's (merge list via PhpStorm)
Get those NOT assigned
SELECT concat(series.title, " - ", t2.title, " <", t4.title, ">") AS 'Title', '' AS 'Placeholder', series.uid AS 'Series'
FROM `tx_fhproductcatalog_domain_model_productline` AS series
JOIN `tx_fhproductcatalog_domain_model_supplier` AS t2 ON series.supplier = t2.uid
JOIN `sys_category` AS t4 ON t4.uid = series.assortment
WHERE series.record_type = 0 AND NOT series.uid IN ({result}) AND NOT series.hidden AND NOT series.deleted
ORDER BY series.title;
Figure out records that have exceeded endtime but no archive flag:
SELECT uid, title AS 'Name', eb_article_number AS 'EB#', if(record_type = 0, 'Produkt', 'Variante') AS 'Typ', hidden, deleted, FROM_UNIXTIME(endtime, '%d.%m.%Y') AS Auslaufdatum, archived AS Archiviert
FROM `tx_fhproductcatalog_domain_model_product`
WHERE NOT `endtime` = 0 AND `endtime` < current_timestamp AND NOT archived;
SELECT t1.uid, t1.pid, if(t1.record_type = 1, (SELECT uid from `tx_fhproductcatalog_domain_model_productline` where uid=t1.parent), '') AS 'Serien-ID', if(t1.record_type = 1, (SELECT title from `tx_fhproductcatalog_domain_model_productline` where uid=t1.parent), '') AS 'Serie', t1.title AS 'Name', if(t1.record_type = 0, 'Serie', 'Ausführung') AS 'Typ', t1.hidden, t1.deleted, FROM_UNIXTIME(t1.endtime, '%d.%m.%Y') AS Auslaufdatum, t1.archived AS Archiviert
FROM `tx_fhproductcatalog_domain_model_productline` as t1
WHERE NOT t1.endtime = 0 AND t1.endtime < current_timestamp AND NOT t1.archived;
Get variants with either outdated parent and differing endtime or archived parent, but not archived on it's own
SELECT t1.`eb_article_number` as 'EB#', t1.`archived` AS 'archiviert', t1.`hidden` AS 'verborgen', t1.`deleted` AS 'gelöscht', FROM_UNIXTIME(t1.`endtime`) as 'Auslaufdatum', t2.`eb_article_number` as 'Variante EB#',
t2.`archived` as 'Variante archiviert', FROM_UNIXTIME(t2.`endtime`) as 'Variante Auslaufdatum', t2.`hidden` AS 'Variante verborgen', t2.`deleted` AS 'Variante gelöscht'
FROM `tx_fhproductcatalog_domain_model_product` AS t1
JOIN `tx_fhproductcatalog_domain_model_product` AS t2 ON t2.parent = t1.uid
WHERE t1.record_type = 0 AND (t1.archived AND NOT t2.archived) OR (t1.endtime > 0 AND t1.endtime < current_timestamp AND t1.endtime <> t2.endtime);
Get outdated
SELECT *
FROM `tx_fhproductcatalog_domain_model_productline`
WHERE uid IN (...) AND (archived OR (endtime > 0 AND endtime < current_timestamp));
Fetch booked contents
booked contents per division
select `t2`.`title` as 'GEFI',
`t2`.`description` as 'GEFI-Name',
`t1`.`rfid` as 'Slot',
if(`t4`.`short_title`, `t4`.`short_title`, `t4`.`title`) as 'Lieferant',
replace(`t3`.`title`, '||', ' - ') as 'Serie',
concat(`t5`.`title`, ' » ', `t5`.`title`) as 'Sparte » Sortiment',
date_format(from_unixtime(`t1`.`crdate`), '%d.%m.%y') as 'erstmalig hinzugefügt am'
from `tx_fhdigitalexposition_domain_model_chip` as `t1`
join `fe_groups` as `t2` on `t1`.`fe_group` = `t2`.`uid`
join `tx_fhproductcatalog_domain_model_productline` as `t3` on `t1`.`cc_series` = `t3`.`uid`
join `tx_fhproductcatalog_domain_model_supplier` as `t4` on `t3`.`supplier` = `t4`.`uid`
join `sys_category` as `t5` on `t3`.`assortment` = `t5`.`uid`
join `sys_category` as `t6` on `t5`.`parent` = `t6`.`uid`
where `t1`.`rfid` like 'a%'
and not `t1`.`hidden`
and `t5`.`parent` in (@uidList)
and not `t2`.`fhde_preview_instance`
and not `t2`.`title` in ('10400', '10403', '38150', '99001', '99002', '99003', '99004')
and not `t2`.`hidden`
order by `t2`.`title`, `t3`.`title`;
booked contents per assortment
select `t2`.`title` as 'GEFI',
`t2`.`description` as 'GEFI-Name',
`t1`.`rfid` as 'Slot',
if(`t4`.`short_title`, `t4`.`short_title`, `t4`.`title`) as 'Lieferant',
replace(`t3`.`title`, '||', ' - ') as 'Serie',
concat(`t6`.`title`, ' » ', `t5`.`title`) as 'Sparte » Sortiment',
date_format(from_unixtime(`t1`.`crdate`), '%d.%m.%y') as 'erstmalig hinzugefügt am'
from `tx_fhdigitalexposition_domain_model_chip` as `t1`
join `fe_groups` as `t2` on `t1`.`fe_group` = `t2`.`uid`
join `tx_fhproductcatalog_domain_model_productline` as `t3` on `t1`.`cc_series` = `t3`.`uid`
join `tx_fhproductcatalog_domain_model_supplier` as `t4` on `t3`.`supplier` = `t4`.`uid`
join `sys_category` as `t5` on `t3`.`assortment` = `t5`.`uid`
join `sys_category` as `t6` on `t5`.`parent` = `t6`.`uid`
where `t1`.`rfid` like 'a%'
and not `t1`.`hidden`
and `t3`.`assortment` in (@uidList)
and not `t2`.`fhde_preview_instance`
and not `t2`.`title` in ('10400', '10403', '38150', '99001', '99002', '99003', '99004')
and not `t2`.`hidden`
order by `t2`.`title`, `t3`.`title`;
booked contents per series id list
select `t2`.`title` as GEFI,
`t2`.`description` as 'GEFI-Name',
`t1`.`rfid` as 'Slot',
if(`t4`.`short_title`, `t4`.`short_title`, `t4`.`title`) as 'Lieferant',
replace(`t3`.`title`, '||', ' - ') as 'Serie',
date_format(from_unixtime(`t1`.`crdate`), '%d.%m.%y') as 'erstmalig hinzugefügt am'
from `tx_fhdigitalexposition_domain_model_chip` as `t1`
join `fe_groups` as `t2` on `t1`.`fe_group` = `t2`.`uid`
join `tx_fhproductcatalog_domain_model_productline` as `t3` on `t1`.`cc_series` = `t3`.`uid`
join `tx_fhproductcatalog_domain_model_supplier` as `t4` on `t3`.`supplier` = `t4`.`uid`
where `t1`.`rfid` like 'a%'
and not `t1`.`hidden`
and `t1`.`cc_series` in (@uidList)
and not `t2`.`fhde_preview_instance`
and not `t2`.`title` in ('10400', '10403', '38150', '99001', '99002', '99003', '99004')
and not `t2`.`hidden`
order by `t2`.`title`, `t3`.`title`;
booked contents per supplier
select `t2`.`title` as 'GEFI',
`t2`.`description` as 'GEFI-Name',
`t1`.`rfid` as 'Slot',
if(`t4`.`short_title`, `t4`.`short_title`, `t4`.`title`) as 'Lieferant',
replace(`t3`.`title`, '||', ' - ') as 'Serie',
date_format(from_unixtime(`t1`.`crdate`), '%d.%m.%y') as 'erstmalig hinzugefügt am'
from `tx_fhdigitalexposition_domain_model_chip` as `t1`
join `fe_groups` as `t2` on `t1`.`fe_group` = `t2`.`uid`
join `tx_fhproductcatalog_domain_model_productline` as `t3` on `t1`.`cc_series` = `t3`.`uid`
join `tx_fhproductcatalog_domain_model_supplier` as `t4` on `t3`.`supplier` = `t4`.`uid`
where `t1`.`rfid` like 'a%'
and not `t1`.`hidden`
and `t3`.`supplier` in (@uidList)
and (`t3`.`endtime` = 0 or `t3`.`endtime` > current_timestamp)
and not `t2`.`fhde_preview_instance`
and not `t2`.`title` in ('10400', '10403', '38150', '99001', '99002', '99003', '99004')
and not `t2`.`hidden`
order by `t2`.`title`, `t3`.`title`;
booked contents per brand
select `t2`.`title` as 'GEFI',
`t2`.`description` as 'GEFI-Name',
`t1`.`rfid` as 'Slot',
if(`t4`.`short_title`, `t4`.`short_title`, `t4`.`title`) as 'Lieferant',
replace(`t3`.`title`, '||', ' - ') as 'Serie',
date_format(from_unixtime(`t1`.`crdate`), '%d.%m.%y') as 'erstmalig hinzugefügt am'
from `tx_fhdigitalexposition_domain_model_chip` as `t1`
join `fe_groups` as `t2` on `t1`.`fe_group` = `t2`.`uid`
join `tx_fhproductcatalog_domain_model_productline` as `t3` on `t1`.`cc_series` = `t3`.`uid`
join `tx_fhproductcatalog_domain_model_supplier` as `t4` on `t3`.`supplier` = `t4`.`uid`
where `t1`.`rfid` like 'a%'
and not `t1`.`hidden`
and `t3`.`brand` in (@uidList)
and (`t3`.`endtime` = 0 or `t3`.`endtime` > current_timestamp)
and not `t2`.`fhde_preview_instance`
and not `t2`.`title` in ('10400', '10403', '38150', '99001', '99002', '99003', '99004')
and not `t2`.`hidden`
order by `t2`.`title`, `t3`.`title`;
Get anchor point information
select `t1`.`uid`,
`t2`.`sorting` as 'Slide#',
(if(`t5`.`short_title`, `t5`.`short_title`, `t5`.`title`)) as 'Hersteller',
(if(`t4`.`brand`, ( select if(`short_title`, `short_title`, `title`) from `tx_fhproductcatalog_domain_model_supplier` where `uid` = `t4`.`brand` ),
'')) as 'Marke',
`t4`.`title` as 'Serie',
`t3`.`eb_article_number` as 'EB#',
`t3`.`title` as 'Produkt/Variante',
if(`t4`.`endtime` > 0, from_unixtime(`t4`.`endtime`), 'n/a') as 'Serie Auslaufdatum',
if(`t3`.`endtime` > 0, from_unixtime(`t3`.`endtime`), 'n/a') as 'Prod/Var. Auslaufdatum',
from_unixtime(`t1`.`crdate`, '%d.%m.%y %h:%i') as 'erstellt am',
from_unixtime(`t1`.`tstamp`, '%d.%m.%y %h:%i') as 'zuletzt geändert am'
from `tx_fhproductcatalog_domain_model_slideanchor` as `t1`
join `tx_fhproductcatalog_domain_model_slide` as `t2` on `t1`.`slide` = `t2`.`uid`
join `tx_fhproductcatalog_domain_model_product` as `t3` on `t1`.`product` = `t3`.`uid`
join `tx_fhproductcatalog_domain_model_productline` as `t4` on `t2`.`productline` = `t4`.`uid`
join `tx_fhproductcatalog_domain_model_supplier` as `t5` on `t4`.`supplier` = `t5`.`uid`
where not `t1`.`hidden`
and not `t1`.`deleted`
and not `t2`.`hidden`
and not `t2`.`deleted`
order by `t5`.`short_title`, `t4`.`title`;
Get video wall images information (all flagged)
select `t1`.`uid`,
from_unixtime(`t1`.`crdate`) as 'Erstellt am',
concat('VideoWall', if(`t1`.`fhpc_display` & 1, ', Detailansicht', ''), if(`t1`.`fhpc_display` & 2, ', Listenansicht', ''), if(`t1`.`fhpc_display` & 8, ', Suche', '')) as 'Verwendung',
`t5`.`title` as 'Sparte',
`t4`.`title` as 'Sortiment',
`t6`.`title` as 'Lieferant',
`t3`.`title` as 'Serie',
`t2`.`eb_article_number` as 'EB#',
`t2`.`title` as 'Prod/Var',
if(`t3`.`endtime` > 0, from_unixtime(`t3`.`endtime`), 'n/a') as 'Serie Auslaufdatum',
if(`t2`.`endtime` > 0, from_unixtime(`t2`.`endtime`), 'n/a') as 'Prod/Var. Auslaufdatum'
from `sys_file_reference` `t1`
join `tx_fhproductcatalog_domain_model_product` `t2` on `t1`.`uid_foreign` = `t2`.`uid`
join `tx_fhproductcatalog_domain_model_productline` `t3` on `t3`.`uid` = if(
`t2`.`record_type` = 0,
if(
( select `record_type` from `tx_fhproductcatalog_domain_model_productline` where `uid` = `t2`.`productline` ) > 0,
( select `parent` from `tx_fhproductcatalog_domain_model_productline` where `uid` = `t2`.`productline` ),
`t2`.`productline`
),
if(
( select `record_type` from `tx_fhproductcatalog_domain_model_productline` where `uid` = ( select `productline` from `tx_fhproductcatalog_domain_model_product` where `uid` = `t2`.`parent` ) ) > 0,
( select `parent` from `tx_fhproductcatalog_domain_model_productline` where `uid` = ( select `productline` from `tx_fhproductcatalog_domain_model_product` where `uid` = `t2`.`parent` ) ),
( select `productline` from `tx_fhproductcatalog_domain_model_product` where `uid` = `t2`.`parent` )
)
)
join `sys_category` `t4` on `t3`.`assortment` = `t4`.`uid`
join `sys_category` `t5` on `t4`.`parent` = `t5`.`uid`
join `tx_fhproductcatalog_domain_model_supplier` `t6` on `t3`.`supplier` = `t6`.`uid`
where `t1`.`tablenames` = 'tx_fhproductcatalog_domain_model_product'
and `t1`.`fhpc_display` & 4
and not `t1`.`hidden`
and not `t1`.`deleted`
and not `t2`.`hidden`
and not `t2`.`deleted`;
# sort out expired products/variants > makes last 2 columns redundant!
# and not (from_unixtime(`t2`.`endtime`) < current_timestamp and `t2`.`endtime` > 0);
Find booked contents with NO A* or 0*** attached
select `t5`.`title` as 'GEFI',
`t5`.`description` as 'GEFI-Name',
`pages`.`uid` as 'data storage',
`tc`.`rfid` as 'RFID',
concat(
`t1`.`title`,
" - ",
concat(`t2`.`title`, if(`t1`.`brand` > 0, ( select concat(" [", `t5`.`title`, "]") from `tx_fhproductcatalog_domain_model_supplier` `t5` where `t5`.`uid` = `t1`.`brand` ), "")),
" <",
`t4`.`title`,
">"
) as 'Title',
'' as 'Placeholder',
`t1`.`uid` as 'Series'
from `tx_fhdigitalexposition_domain_model_chip` `tc`
join `pages` on `pages`.`uid` = `tc`.`pid`
join `tx_fhproductcatalog_domain_model_productline` `t1` on `t1`.`uid` = `tc`.`cc_series`
join `tx_fhproductcatalog_domain_model_supplier` `t2` on `t1`.`supplier` = `t2`.`uid`
join `sys_category` `t4` on `t4`.`uid` = `t1`.`assortment`
join `fe_groups` `t5` on `pages`.`fh_fe_group` = `t5`.`uid`
where not `tc`.`rfid` regexp '^[aA]?[0-9]*$'
and `tc`.`record_type` = 1
and `tc`.`cc_series` > 0
and not exists(select `t2`.`uid` from `tx_fhdigitalexposition_domain_model_chip` `tcp` where `tcp`.`cc_series` = `tc`.`cc_series` and `tcp`.`pid` = `tc`.`pid` and `tcp`.`record_type` = 1 and `tcp`.`rfid` regexp '^[aA]?[0-9]*$')
order by `pages`.`title`;
Find booked per division
select group_concat(`uid`)
from `sys_category`
where `parent` in (@divisionUidList);
select `t2`.`title` as 'GEFI', `t2`.`description` as 'GEFI Name', `t3`.`title` as 'Serie', `t5`.`title` as 'Lieferant', `t4`.`title` as 'Sortiment'
from `tx_fhdigitalexposition_domain_model_chip` `t1`
join `fe_groups` `t2` on `t1`.`fe_group` = `t2`.`uid`
join `tx_fhproductcatalog_domain_model_productline` `t3` on `t1`.`cc_series` = `t3`.`uid`
join `sys_category` `t4` on `t3`.`assortment` = `t4`.`uid`
join `tx_fhproductcatalog_domain_model_supplier` `t5` on `t3`.`supplier` = `t5`.`uid`
where not `t2`.`fhde_preview_instance`
and not `t2`.`hidden`
and not `t2`.`title` in ('10400', '10403', '99001', '99002', '99003', '99004', '38150', '99989', '99996')
and `t1`.`record_type` = 1
and `t3`.`assortment` in (@uidList)
group by `t2`.`title`
order by `t2`.`title`, `t3`.`title`;
Find archived
# series and lineups
select `t1`.`uid`,
`t1`.`pid`,
if(`t1`.`record_type` = 1, ( select `uid` from `tx_fhproductcatalog_domain_model_productline` where `uid` = `t1`.`parent` ), '') as 'Serien-ID',
if(`t1`.`record_type` = 1, ( select `title` from `tx_fhproductcatalog_domain_model_productline` where `uid` = `t1`.`parent` ), '') as 'Serie',
`t1`.`title` as 'Name',
if(`t1`.`record_type` = 0, 'Serie', 'Ausführung') as 'Typ',
from_unixtime(`t1`.`endtime`, '%d.%m.%Y') as Auslaufdatum,
`t1`.`archived` as Archiviert
from `tx_fhproductcatalog_domain_model_productline` as `t1`
where not `t1`.`endtime` = 0
and `t1`.`endtime` < current_timestamp
and `t1`.`archived`
and `t1`.`uid` in (@uidList);
# products and variants
select `t1`.`uid`,
`t1`.`pid`,
`t1`.`title` as 'Name',
if(`t1`.`record_type` = 0, 'Produkt', 'Variante') as 'Typ',
from_unixtime(`t1`.`endtime`, '%d.%m.%Y') as Auslaufdatum,
`t1`.`archived` as Archiviert
from `tx_fhproductcatalog_domain_model_product` as `t1`
where not `t1`.`endtime` = 0
and `t1`.`endtime` < current_timestamp
and `t1`.`archived`
and `t1`.`uid` in (@uidList);
All simple contents with associated assortment and division
select `prod`.`title` as 'Inhalt', `c2`.`title` as 'Sparte', `c1`.`title` as 'Sortiment'
from `tx_fhdigitalexposition_domain_model_product` `prod`
join `sys_category` `c1` on `prod`.`assortment` = `c1`.`uid`
join `sys_category` `c2` on `c1`.`parent` = `c2`.`uid`
where not `prod`.`deleted`
order by `c2`.`title`, `c1`.`title`, `prod`.`title`;
Fix central content slots (where series=lineup)
update `tx_fhdigitalexposition_domain_model_chip`
set `cc_lineup`=0
where `record_type`=1
and `cc_series` = `cc_lineup`
and `cc_series` <> 0;
Find slots that require update after series re-structure
select `t2`.`title` as GEFI,
`t2`.`description` as 'GEFI-Name',
`t1`.`uid` as 'Chip-Id',
`t1`.`simple_number` as 'Einfache Händlernummer',
`t1`.`rfid` as RFID,
`t3`.`title` as Serie,
`t3`.`uid` as 'Serien-Id',
if(`t1`.`cc_lineup` > 0, ( select `uid` from `tx_fhproductcatalog_domain_model_productline` where `uid` = `t1`.`cc_lineup` ), '') as Ausführung,
if(`t1`.`cc_product` > 0, ( select `uid` from `tx_fhproductcatalog_domain_model_product` where `uid` = `t1`.`cc_product` ), '') as Produkt,
if(`t1`.`cc_variant` > 0, ( select `uid` from `tx_fhproductcatalog_domain_model_product` where `uid` = `t1`.`cc_variant` ), '') as Variante
from `tx_fhdigitalexposition_domain_model_chip` `t1`
join `fe_groups` `t2` on `t1`.`fe_group` = `t2`.`uid`
join `tx_fhproductcatalog_domain_model_productline` `t3` on `t1`.`cc_series` = `t3`.`uid`
where `t1`.`cc_series` in (@uidList)
order by `t2`.`title`, field(`t1`.`cc_series`, @uidList);
Query instances with constant printEnabled set
select `p`.`title` as 'GEFI#', `grp`.`description` as 'GEFI', `tmpl`.`constants` as 'setup'
from `sys_template` `tmpl`
join `pages` `p` on `p`.`uid` = `tmpl`.`pid`
join `fe_groups` as `grp` on `grp`.`uid` = `p`.`fh_fe_group`
where `p`.`doktype` = 97
and `tmpl`.`constants` like '%printEnabled%';