/** OLD SELECT
SELECT DISTINCT
  CONCAT(`p`.`id`, '-', `p`.`productVariantId`) AS `CodProd`,
  `pc`.`id` AS `id`,
  (SELECT
      MAX((CASE WHEN (`c`.`depth` = 1) THEN `pct`.`name` END)) AS `slug1`
    FROM ((`ProductCategory` `c`
      JOIN `ProductCategoryTranslation` `pct`
        ON ((`pct`.`productCategoryId` = `c`.`id`)))
      JOIN `ProductCategory` `c2`)
    WHERE ((`pct`.`langId` = 1)
    AND (`c`.`lft` <= `c2`.`lft`)
    AND (`c`.`rght` >= `c2`.`rght`)
    AND (`c2`.`id` = `pc`.`id`))) AS `livello1`,
  (SELECT
      MAX((CASE WHEN (`c`.`depth` = 2) THEN `pct`.`name` END)) AS `slug1`
    FROM ((`ProductCategory` `c`
      JOIN `ProductCategoryTranslation` `pct`
        ON ((`pct`.`productCategoryId` = `c`.`id`)))
      JOIN `ProductCategory` `c2`)
    WHERE ((`pct`.`langId` = 1)
    AND (`c`.`lft` <= `c2`.`lft`)
    AND (`c`.`rght` >= `c2`.`rght`)
    AND (`c2`.`id` = `pc`.`id`))) AS `livello2`,
  (SELECT
      MAX((CASE WHEN (`c`.`depth` = 3) THEN `pct`.`name` END)) AS `slug1`
    FROM ((`ProductCategory` `c`
      JOIN `ProductCategoryTranslation` `pct`
        ON ((`pct`.`productCategoryId` = `c`.`id`)))
      JOIN `ProductCategory` `c2`)
    WHERE ((`pct`.`langId` = 1)
    AND (`c`.`lft` <= `c2`.`lft`)
    AND (`c`.`rght` >= `c2`.`rght`)
    AND (`c2`.`id` = `pc`.`id`))) AS `livello3`,
  (SELECT
      MAX((CASE WHEN (`c`.`depth` = 4) THEN `pct`.`name` END)) AS `slug1`
    FROM ((`ProductCategory` `c`
      JOIN `ProductCategoryTranslation` `pct`
        ON ((`pct`.`productCategoryId` = `c`.`id`)))
      JOIN `ProductCategory` `c2`)
    WHERE ((`pct`.`langId` = 1)
    AND (`c`.`lft` <= `c2`.`lft`)
    AND (`c`.`rght` >= `c2`.`rght`)
    AND (`c2`.`id` = `pc`.`id`))) AS `livello4`,
  (SELECT
      MAX((CASE WHEN (`c`.`depth` = 5) THEN `pct`.`name` END)) AS `slug1`
    FROM ((`ProductCategory` `c`
      JOIN `ProductCategoryTranslation` `pct`
        ON ((`pct`.`productCategoryId` = `c`.`id`)))
      JOIN `ProductCategory` `c2`)
    WHERE ((`pct`.`langId` = 1)
    AND (`c`.`lft` <= `c2`.`lft`)
    AND (`c`.`rght` >= `c2`.`rght`)
    AND (`c2`.`id` = `pc`.`id`))) AS `livello5`,
  (SELECT
      MAX((CASE WHEN (`c`.`depth` = 6) THEN `pct`.`name` END)) AS `slug1`
    FROM ((`ProductCategory` `c`
      JOIN `ProductCategoryTranslation` `pct`
        ON ((`pct`.`productCategoryId` = `c`.`id`)))
      JOIN `ProductCategory` `c2`)
    WHERE ((`pct`.`langId` = 1)
    AND (`c`.`lft` <= `c2`.`lft`)
    AND (`c`.`rght` >= `c2`.`rght`)
    AND (`c2`.`id` = `pc`.`id`))) AS `livello6`
FROM ((`ProductCategory` `pc`
  JOIN `ProductHasProductCategory` `phpc`
    ON ((`phpc`.`productCategoryId` = `pc`.`id`)))
  JOIN `Product` `p`
    ON (((`phpc`.`productId` = `p`.`id`)
    AND (`phpc`.`productVariantId` = `p`.`productVariantId`))))
WHERE (`pc`.`id` > 1)
GROUP BY CONCAT(`p`.`id`, '-', `p`.`productVariantId`)
*/

CREATE OR REPLACE VIEW vQlikCategorie AS
SELECT DISTINCT
  CONCAT(p.id, '-', p.productVariantId) AS CodProd,
  p.id as productId,
  p.productVariantId as productVariantId,
  pc.id                                 AS id,
  (SELECT MAX((CASE WHEN (c.depth = 1)
    THEN pct.name END)) AS slug1
   FROM ((ProductCategory c
     JOIN ProductCategoryTranslation pct
       ON ((pct.productCategoryId = c.id)))
     JOIN ProductCategory c2)
   WHERE ((pct.langId = 1)
          AND (c.lft <= c2.lft)
          AND (c.rght >= c2.rght)
          AND (c2.id = pc.id)))         AS livello1,
  (SELECT MAX((CASE WHEN (c.depth = 2)
    THEN pct.name END)) AS slug1
   FROM ((ProductCategory c
     JOIN ProductCategoryTranslation pct
       ON ((pct.productCategoryId = c.id)))
     JOIN ProductCategory c2)
   WHERE ((pct.langId = 1)
          AND (c.lft <= c2.lft)
          AND (c.rght >= c2.rght)
          AND (c2.id = pc.id)))         AS livello2,
  (SELECT MAX((CASE WHEN (c.depth = 3)
    THEN pct.name END)) AS slug1
   FROM ((ProductCategory c
     JOIN ProductCategoryTranslation pct
       ON ((pct.productCategoryId = c.id)))
     JOIN ProductCategory c2)
   WHERE ((pct.langId = 1)
          AND (c.lft <= c2.lft)
          AND (c.rght >= c2.rght)
          AND (c2.id = pc.id)))         AS livello3,
  (SELECT MAX((CASE WHEN (c.depth = 4)
    THEN pct.name END)) AS slug1
   FROM ((ProductCategory c
     JOIN ProductCategoryTranslation pct
       ON ((pct.productCategoryId = c.id)))
     JOIN ProductCategory c2)
   WHERE ((pct.langId = 1)
          AND (c.lft <= c2.lft)
          AND (c.rght >= c2.rght)
          AND (c2.id = pc.id)))         AS livello4,
  (SELECT MAX((CASE WHEN (c.depth = 5)
    THEN pct.name END)) AS slug1
   FROM ((ProductCategory c
     JOIN ProductCategoryTranslation pct
       ON ((pct.productCategoryId = c.id)))
     JOIN ProductCategory c2)
   WHERE ((pct.langId = 1)
          AND (c.lft <= c2.lft)
          AND (c.rght >= c2.rght)
          AND (c2.id = pc.id)))         AS livello5,
  (SELECT MAX((CASE WHEN (c.depth = 6)
    THEN pct.name END)) AS slug1
   FROM ((ProductCategory c
     JOIN ProductCategoryTranslation pct
       ON ((pct.productCategoryId = c.id)))
     JOIN ProductCategory c2)
   WHERE ((pct.langId = 1)
          AND (c.lft <= c2.lft)
          AND (c.rght >= c2.rght)
          AND (c2.id = pc.id)))         AS livello6
FROM ProductCategory pc
  JOIN ProductHasProductCategory phpc
    ON phpc.productCategoryId = pc.id
  JOIN Product p
    ON phpc.productId = p.id
       AND phpc.productVariantId = p.productVariantId
WHERE pc.id > 1
GROUP BY CONCAT(p.id, '-', p.productVariantId);

/*
vecchia select view ordini
SELECT
  `o`.`id` AS `numOrd`,
  `ol`.`id` AS `rigaOrd`,
  DATE_FORMAT(`o`.`orderDate`, '%d/%m/%Y') AS `OrderCreation`,
  DATE_FORMAT(`o`.`creationDate`, '%H') AS `OraSco`,
  DATE_FORMAT(`l`.`time`, '%d/%m/%Y') AS `DataOrd`,
  DATE_FORMAT(`l`.`time`, '%H:%i:%s') AS `OraOrd`,
  IF(((`o`.`paymentDate` <> 0) OR (`o`.`orderPaymentMethodId` = 5)), 1, 0) AS `Pagato`,
  `ol`.`shopId` AS `friendId`,
  `sh`.`name` AS `friendName`,
  `o`.`userId` AS `CodUtente`,
  (SELECT
      `Log`.`eventValue`
    FROM `Log`
    WHERE ((`Log`.`entityName` LIKE 'OrderLine')
    AND ((`Log`.`eventName` LIKE 'changeOrderLineStatus')
    OR (`Log`.`eventName` LIKE 'orderStatusLogByHands'))
    AND (`Log`.`stringId` = CONCAT(`ol`.`id`, '-', `ol`.`orderId`)))
    ORDER BY `Log`.`id` DESC LIMIT 1) AS `StatoRiga`,
  `l`.`eventValue` AS `StatoOrdine`,
  CONCAT(`ol`.`productId`, '-', `ol`.`productVariantId`) AS `CodProd`,
  `pb`.`name` AS `Brand`,
  `ps`.`name` AS `Taglia`,
  `shp`.`shopId` AS `CodShop`,
  REPLACE(CAST(IFNULL(`ol`.`cost`, 0) AS char charset utf8), '.', ',') AS `CostoForn`,
  REPLACE(CAST(IFNULL(`ol`.`friendRevenue`, 0) AS char charset utf8), '.', ',') AS `CostoFriend`,
  REPLACE(CAST(IFNULL(`o`.`shippingPrice`, 0) AS char charset utf8), '.', ',') AS `RealTrasp`,
  REPLACE(CAST(IFNULL(`ol`.`activePrice`, 0) AS char charset utf8), '.', ',') AS `PrezzoAtt`,
  REPLACE(ROUND(`ol`.`netPrice`, 2), '.', ',') AS `netPrice`,
  REPLACE(CAST(ROUND(IFNULL(IF(((`ol`.`couponCharge` < 0) AND ((`ol`.`netPrice` + `ol`.`couponCharge`) < 0)), 0, `ol`.`netPrice`), 0), 2) AS char charset utf8), '.', ',') AS `realizzo`,
  REPLACE(CAST(ROUND(IFNULL(IF(((`ol`.`couponCharge` < 0) AND ((`ol`.`netPrice` + `ol`.`couponCharge`) < 0)), 0, (`ol`.`netPrice` / ((100 + `c`.`vat`) / 100))), 0), 2) AS char charset utf8), '.', ',') AS `realizzoNetto`,
  `c`.`vat` AS `iva`,
  REPLACE(CAST(ROUND(IFNULL(IF(((`ol`.`couponCharge` < 0) AND ((`ol`.`netPrice` + `ol`.`couponCharge`) < 0)), 0, ((((`ol`.`netPrice` / (100 + `c`.`vat`)) * 100) - `ol`.`couponCharge`) - `ol`.`friendRevenue`)), 0), 2) AS char charset utf8), '.', ',') AS `margine`,
  REPLACE(CAST(ROUND(IFNULL((`ol`.`activePrice` - REPLACE(CAST(IFNULL(IF(((`ol`.`couponCharge` < 0) AND ((`ol`.`netPrice` + `ol`.`couponCharge`) < 0)), 0, ((`ol`.`activePrice` - `ol`.`netPrice`) - `ol`.`shippingCharge`)), 0) AS char charset utf8), '.', ',')), 0), 2) AS char charset utf8), '.', ',') AS `sconti`,
  1 AS `Qta`,
  `ua`.`city` AS `Citta`,
  `c`.`name` AS `Stato`,
  `pse`.`name` AS `Stagione`,
  `pc`.`livello1` AS `Categoria1`,
  `pc`.`livello2` AS `Categoria2`,
  `pc`.`livello3` AS `Categoria3`,
  `pc`.`livello4` AS `Categoria4`
FROM (((((((((((`Order` `o`
  JOIN `OrderLine` `ol`
    ON ((`o`.`id` = `ol`.`orderId`)))
  JOIN `Product` `p`
    ON (((`p`.`id` = `ol`.`productId`)
    AND (`p`.`productVariantId` = `ol`.`productVariantId`))))
  JOIN `ProductBrand` `pb`
    ON ((`pb`.`id` = `p`.`productBrandId`)))
  JOIN `ProductSeason` `pse`
    ON ((`pse`.`id` = `p`.`productSeasonId`)))
  JOIN `ProductSize` `ps`
    ON ((`ol`.`productSizeId` = `ps`.`id`)))
  JOIN `Shop` `sh`
    ON ((`ol`.`shopId` = `sh`.`id`)))
  LEFT JOIN `ShopHasProduct` `shp`
    ON (((`ol`.`shopId` = `shp`.`shopId`)
    AND (`ol`.`productVariantId` = `shp`.`productVariantId`)
    AND (`ol`.`productId` = `shp`.`productId`))))
  JOIN `Log` `l`
    ON (((`l`.`stringId` = `ol`.`orderId`)
    OR (`l`.`stringId` = `ol`.`id`))))
  JOIN `UserAddress` `ua`
    ON ((`o`.`billingAddressId` = `ua`.`id`)))
  JOIN `Country` `c`
    ON ((`c`.`id` = `ua`.`countryId`)))
  JOIN `vQlikCategorie` `pc`
    ON ((CONCAT(`ol`.`productId`, '-', `ol`.`productVariantId`) = `pc`.`CodProd`)))
WHERE (`l`.`entityName` = 'Order')
ORDER BY `o`.`orderDate` DESC
*/


CREATE OR REPLACE VIEW vQlikOrdini AS
  SELECT
    o.id                                                                                      AS numOrd,
    ol.id                                                                                     AS rigaOrd,
    date_format(o.orderDate, '%d/%m/%Y')                                                      AS OrderCreation,
    date_format(o.creationDate, '%H')                                                         AS OraSco,
    date_format(l.time, '%d/%m/%Y')                                                           AS DataOrd,
    date_format(l.time, '%H:%i:%s')                                                           AS OraOrd,
    if(((o.paymentDate <> 0) OR (o.orderPaymentMethodId = 5)), 1, 0)                          AS Pagato,
    ol.shopId                                                                                 AS friendId,
    sh.name                                                                                   AS friendName,
    o.userId                                                                                  AS CodUtente,
    (SELECT barbagalloshop.Log.eventValue
     FROM barbagalloshop.Log
     WHERE ((barbagalloshop.Log.entityName = 'OrderLine') AND
            ((barbagalloshop.Log.eventName LIKE 'changeOrderLineStatus') OR
             (barbagalloshop.Log.eventName LIKE 'orderStatusLogByHands')) AND
            (barbagalloshop.Log.stringId = concat(ol.id, '-', ol.orderId)))
     ORDER BY barbagalloshop.Log.id DESC
     LIMIT 1)                                                                                 AS StatoRiga,
    l.eventValue                                                                              AS StatoOrdine,
    concat(ol.productId, '-', ol.productVariantId)                                            AS CodProd,
    pb.name                                                                                   AS Brand,
    ps.name                                                                                   AS Taglia,
    shp.shopId                                                                                AS CodShop,
    replace(cast(ifnull(ol.cost, 0) AS CHAR CHARSET utf8), '.', ',')                          AS CostoForn,
    replace(cast(ifnull(ol.friendRevenue, 0) AS CHAR CHARSET utf8), '.', ',')                 AS CostoFriend,
    replace(cast(ifnull(o.shippingPrice, 0) AS CHAR CHARSET utf8), '.', ',')                  AS RealTrasp,
    replace(cast(ifnull(ol.activePrice, 0) AS CHAR CHARSET utf8), '.', ',')                   AS PrezzoAtt,
    replace(round(ol.netPrice, 2), '.', ',')                                                  AS netPrice,
    replace(cast(round(ifnull(if(((ol.couponCharge < 0) AND ((ol.netPrice + ol.couponCharge) < 0)), 0,
                                 ol.netPrice), 0), 2) AS CHAR CHARSET utf8), '.', ',')        AS realizzo,
    replace(cast(round(ifnull(if(((ol.couponCharge < 0) AND ((ol.netPrice + ol.couponCharge) < 0)), 0,
                                 (ol.netPrice / ((100 + c.vat) / 100))), 0), 2) AS CHAR CHARSET utf8), '.',
            ',')                                                                              AS realizzoNetto,
    c.vat                                                                                     AS iva,
    replace(cast(round(ifnull(if(((ol.couponCharge < 0) AND ((ol.netPrice + ol.couponCharge) < 0)), 0,
                                 ((((ol.netPrice / (100 + c.vat)) * 100) - ol.couponCharge) -
                                  ol.friendRevenue)), 0), 2) AS CHAR CHARSET utf8), '.', ',') AS margine,
    replace(cast(round(ifnull((ol.activePrice - replace(cast(ifnull(if(((ol.couponCharge < 0) AND
                                                                        ((ol.netPrice + ol.couponCharge) <
                                                                         0)), 0,
                                                                       ((ol.activePrice - ol.netPrice) -
                                                                        ol.shippingCharge)), 0) AS
                                                             CHAR CHARSET utf8), '.', ',')), 0), 2) AS
                 CHAR CHARSET utf8), '.', ',')                                                AS sconti,
    1                                                                                         AS Qta,
    ua.city                                                                                   AS Citta,
    c.name                                                                                    AS Stato,
    pse.name                                                                                  AS Stagione,
    pc.livello1                                                                               AS Categoria1,
    pc.livello2                                                                               AS Categoria2,
    pc.livello3                                                                               AS Categoria3,
    pc.livello4                                                                               AS Categoria4
  FROM `Order` o
    JOIN OrderLine ol ON o.id = ol.orderId
    JOIN Product p
      ON p.id = ol.productId AND p.productVariantId = ol.productVariantId
    JOIN ProductBrand pb ON pb.id = p.productBrandId
    JOIN ProductSeason pse
      ON pse.id = p.productSeasonId
    JOIN ProductSize ps
      ON ol.productSizeId = ps.id
    JOIN Shop sh ON ol.shopId = sh.id
    JOIN Log l
      ON (l.entityName, l.stringId) = ('Order', ol.orderId) OR
         (l.entityName, l.stringId) = ('OrderLine', concat(ol.id, '-', ol.orderId))
    JOIN UserAddress ua
      ON o.billingAddressId = ua.id
    JOIN Country c ON c.id = ua.countryId
    JOIN vQlikCategorie pc ON concat(ol.productId, '-', ol.productVariantId) = pc.CodProd
    LEFT JOIN
    ShopHasProduct shp
      ON ol.shopId = shp.shopId AND ol.productVariantId = shp.productVariantId AND
         ol.productId = shp.productId
  WHERE (l.entityName = 'Order')
  ORDER BY o.orderDate DESC;