Untitled

# TODO vorjahres, vormonat zahlen fehlen, muss irgendwie aus dem datum kontext berechnet werden

SET @from_date := '01.03.2020';
SET @to_date := '31.03.2020';

SELECT delivery_slips_items.article_sub_number                      AS 'Cat. No.',
       articles.ean                                                 AS 'EAN-Code',
       articles_sub_types.name                                      AS 'Format',
       articles.status                                              AS 'Status',
       articles.article_disponents_id                               AS 'Disponenten-Nr.',
       articles.product_code_id                                     AS 'Produkte-Code',
       articles.ue_code                                             AS 'U / E Code',
       articles.genre                                               AS 'Musik- / Filmart',
       articles.brand_block_id                                      AS 'Block',
       LPAD(articles.brand_main_id, 2, '0')                         AS 'Marke',
       brand_blocks.brand_name                                      AS 'Markenbezeichnung',
       LPAD(articles.brand_label_code_id, 2, '0')                   AS 'Label',
       brand_blocks.label_name                                      AS 'Labelbezeichnung',
       articles.accounting_nb                                       AS 'Erloeskonto',
       articles.alphapart                                           As 'Alpha',
       articles.supplier_article_nb                                 As 'Artikel-Nr. Lieferant',
       artists.names                                                AS 'Kuenstler',
       dirigent.names                                               AS 'Direigent',
       orchestra.names                                              AS 'Orchester',
       composer.names                                               AS 'Komponist',
       articles.diverses                                            AS 'Diverses',
       articles.title                                               AS 'Title',
       articles.set_units                                           AS 'Anhzahl Einheiten Lieferant',
       delivery_slips_items.price_code                              AS 'Pricecode',
       delivery_slips_items.price_tier                              AS 'Price Tier',
       ROUND(delivery_slips_items.ppd, 2)                           AS 'PPD',
       delivery_slips_items.client_nb                               AS 'Cust.No.',
       SUBSTRING(
               clients.short_address,
               1,
               LENGTH(clients.short_address) - LENGTH(REPLACE(clients.short_address, ' ', '')) + 5
           )                                                        AS 'CN short',
       clients.short_address                                        AS 'Customer Name',
       sum(delivery_slips_items.revenue_rabat_value)                AS 'Umsatzrab.',
       sum(delivery_slips_items.performance_rabat_value)            AS 'Leistungsrab.',
       sum(delivery_slips_items.special_rabat_value)                AS 'Spezialrab.',
       ROUND(delivery_slips_items.netto_price, 2)                   AS 'Warenwert / Unit',
       articles.units_1_month_ago                                   AS 'Bestand begin',
       sum(IF(
               delivery_slips_items.order_type NOT REGEXP '^([R-TEIJ])$',
               delivery_slips_items.units_normalized,
               0
           ))                                                       AS 'Sales Quantity',

       sum(IF(
               delivery_slips_items.order_type REGEXP '^[R-S]$',
               delivery_slips_items.units_normalized,
               0
           ))                                                       AS 'Returns Quantity',
       sum(IF(
               delivery_slips_items.order_type REGEXP '^[EIT]$',
               delivery_slips_items.units_normalized,
               0
           ))                                                       AS 'Promo Quantity',
       sum(IF(
               delivery_slips_items.client_nb = 94234,
               delivery_slips_items.units_normalized,
               0
           ))                                                       AS 'Vernichtungen Quantity',
       sum(IF(
               delivery_slips_items.client_nb = 94226,
               delivery_slips_items.units_normalized,
               0
           ))                                                       AS 'Returen an lieferant Quantity',

       articles.units_2_month_ago                                      AS 'Bestand end',
       sum(IF(
               delivery_slips_items.order_type NOT REGEXP '^([R-TEI])$',
               delivery_slips_items.netto_price,
               0
           ))                                                       AS 'Sales CHF',
       sum(IF(
               delivery_slips_items.order_type REGEXP '^[R-S]$',
               delivery_slips_items.netto_price,
               0
           ))                                                       AS 'Returns CHF',
       sum(IF(
               delivery_slips_items.order_type REGEXP '^[EIT]$',
               delivery_slips_items.netto_price,
               0
           ))                                                       AS 'Promo CHF',
       sum(ROUND(delivery_slips_items.total_value_netto * 0.97, 2)) AS 'Formel',
       articles.additional3                                         AS 'Lizenzsatz',
       articles.accounting_nb                                       AS 'KST',
       articles.suisa_nb                                            AS 'Suisa NB'
FROM (
         SELECT *,
                IF(
                        delivery_slips_items.spcial_price != 0,
                        delivery_slips_items.spcial_price,
                        prices.price
                    )       AS ppd,
                prices.name AS price_tier,
                IF(
                        delivery_slips_items.order_type REGEXP '^[R-T]$',
                        delivery_slips_items.units,
                        delivery_slips_items.delivered
                    )       AS units_normalized
         FROM delivery_slips_items
                  LEFT JOIN
              (
                  SELECT code,
                         value AS price,
                         name
                  FROM prices
              ) AS prices
              ON delivery_slips_items.price_code = prices.code
     ) AS delivery_slips_items


         LEFT JOIN articles
                   ON delivery_slips_items.article_sub_number = articles.article_sub_number
         LEFT JOIN
     (
         SELECT article_type_id,
                article_subtype_id,
                name
         FROM articles_sub_types
     ) AS articles_sub_types
     ON articles.article_type_id = articles_sub_types.article_type_id
         AND articles.article_subtype_id = articles_sub_types.article_subtype_id
         LEFT JOIN
     (
         SELECT brand_blocks.brand_block_id AS brand_block_id,
                brand_mains.brand_main_id   AS brand_main_id,
                brand_mains.brand_name      AS brand_name,
                brand_mains.label_code_id   AS label_code_id,
                brand_mains.label_name      AS label_name
         FROM brand_blocks
                  LEFT JOIN
              (
                  SELECT brand_mains.brand_block_id AS block_id,
                         brand_mains.brand_main_id  AS brand_main_id,
                         brand_mains.text           AS brand_name,
                         brand_labels.code_id       AS label_code_id,
                         brand_labels.name          AS label_name
                  FROM brand_mains
                           LEFT JOIN
                       (
                           SELECT code_id,
                                  brand_main_text_id,
                                  name
                           FROM brand_labels
                       ) AS brand_labels
                       ON brand_mains.id = brand_labels.brand_main_text_id
              ) AS brand_mains
              ON brand_blocks.id = brand_mains.block_id
     ) AS brand_blocks
     ON articles.brand_block_id = brand_blocks.brand_block_id
         AND articles.brand_main_id = brand_blocks.brand_main_id
         AND articles.brand_label_code_id = brand_blocks.label_code_id
         LEFT JOIN
     (
         SELECT article_id,
                GROUP_CONCAT(contributors.name SEPARATOR ' / ') AS names
         FROM contributors_roles
                  LEFT JOIN
              (
                  SELECT id,
                         name
                  FROM contributors
              ) AS contributors
              ON contributors.id = contributors_roles.contributors_id
         WHERE contributors_roles.role_id = 1
         GROUP BY contributors_roles.article_id
     ) AS artists
     ON articles.id = artists.article_id
         LEFT JOIN
     (
         SELECT article_id,
                GROUP_CONCAT(contributors.name SEPARATOR ' / ') AS names
         FROM contributors_roles
                  LEFT JOIN
              (
                  SELECT id,
                         name
                  FROM contributors
              ) AS contributors
              ON contributors.id = contributors_roles.contributors_id
         WHERE contributors_roles.role_id = 2
         GROUP BY contributors_roles.article_id
     ) AS dirigent
     ON articles.id = dirigent.article_id
         LEFT JOIN
     (
         SELECT article_id,
                GROUP_CONCAT(contributors.name SEPARATOR ' / ') AS names
         FROM contributors_roles
                  LEFT JOIN
              (
                  SELECT id,
                         name
                  FROM contributors
              ) AS contributors
              ON contributors.id = contributors_roles.contributors_id
         WHERE contributors_roles.role_id = 4
         GROUP BY contributors_roles.article_id
     ) AS orchestra
     ON articles.id = orchestra.article_id
         LEFT JOIN
     (
         SELECT article_id,
                GROUP_CONCAT(contributors.name SEPARATOR ' / ') AS names
         FROM contributors_roles
                  LEFT JOIN
              (
                  SELECT id,
                         name
                  FROM contributors
              ) AS contributors
              ON contributors.id = contributors_roles.contributors_id
         WHERE contributors_roles.role_id = 3
         GROUP BY contributors_roles.article_id
     ) AS composer
     ON articles.id = composer.article_id
         LEFT JOIN
     (
         SELECT nb,
                short_address
         FROM clients
     ) AS clients
     ON delivery_slips_items.client_nb = clients.nb
WHERE delivery_slips_items.report_day >= STR_TO_DATE(@from_date, '%d.%m.%Y')
  AND delivery_slips_items.report_day <= STR_TO_DATE(@to_date, '%d.%m.%Y')
  AND delivery_slips_items.units_normalized != 0
  and articles.brand_block_id = 'B'
  and articles.article_sub_number = 'BELLA947V'
group by delivery_slips_items.article_sub_number