CREATE VIEW `vQlikOnlineProduct` AS
  SELECT
    count(DISTINCT ProductVariantId) AS `prodottiOnline`,
    shopId,
    s.name                           AS shopName,
    date
  FROM ProductStatistics AS pst
    JOIN Shop AS s ON s.id = pst.shopId
  WHERE isVisible = 1
  GROUP BY date, shopId;

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` OR `o`.`orderPaymentMethodId` = 5, 1, 0)                                   AS `Pagato`,
    `ol`.shopId                                                                                     AS `friendId`,
    `sh`.`name`                                                                                     AS `friendName`,
    `o`.`userId`                                                                                    AS `CodUtente`,
    (SELECT eventValue
     FROM Log
     WHERE entityName LIKE 'OrderLine' AND
           (eventName LIKE 'changeOrderLineStatus' OR eventName LIKE 'orderStatusLogByHands') AND
           stringId = CONCAT(ol.id, '-', ol.orderId)
     ORDER BY Log.Id DESC
     LIMIT 1)                                                                                       AS `StatoRiga`,
    #`l`.`eventValue` as `StatoOrdine`,
    #IF((SELECT eventValue FROM Log WHERE entityName LIKE 'OrderLine' AND (eventName LIKE 'changeOrderLineStatus' OR eventName LIKE 'orderStatusLogByHands') AND stringId = CONCAT(ol.id, '-', ol.orderId) ORDER BY Log.Id DESC LIMIT 1) LIKE '%CANC%', 'ORD_CANCEL', `l`.`eventValue`) as `StatoOrdine`,
    `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), '.', ',')                                         AS `CostoForn`,
    REPLACE(CAST(IFNULL(`ol`.`friendRevenue`, 0) AS CHAR), '.', ',')                                AS `CostoFriend`,
    REPLACE(CAST(IFNULL(`o`.`shippingPrice`, 0) AS CHAR), '.', ',')                                 AS `RealTrasp`,
    REPLACE(CAST(IFNULL(`ol`.`activePrice`, 0) AS CHAR), '.', ',')                                  AS `PrezzoAtt`,
    REPLACE(ROUND(`ol`.`netPrice`, 2), '.', ',')                                                    AS `netPrice`,
    REPLACE(CAST(ROUND(IFNULL(
                           IF(`ol`.`couponCharge` < 0 && `ol`.`netPrice` + `ol`.`couponCharge` < 0, 0, `ol`.`netPrice`)
                           , 0), 2) AS CHAR), '.', ',')                                             AS `realizzo`,
    REPLACE(CAST(ROUND(IFNULL(
                           IF(`ol`.`couponCharge` < 0 && `ol`.`netPrice` + `ol`.`couponCharge` < 0, 0,
                              (`ol`.`netPrice` / ((100 + `c`.`vat`) / 100)))
                           , 0), 2) AS CHAR), '.', ',')                                             AS `realizzoNetto`,
    `c`.`vat`                                                                                       AS iva,
    REPLACE(CAST(ROUND(IFNULL(
                           IF(`ol`.`couponCharge` < 0 && `ol`.`netPrice` + `ol`.`couponCharge` < 0, # condition
                              0, # if true
                              (`ol`.`netPrice` / (100 + `c`.`vat`) * 100) - `ol`.`couponCharge` - `ol`.`friendRevenue`)
                           #if false
                           , 0), 2) AS CHAR), '.', ',')                                             AS `margine`,
    REPLACE(CAST(ROUND(IFNULL(`ol`.`activePrice` -
                              REPLACE(CAST(IFNULL(
                                               IF(`ol`.`couponCharge` < 0 && `ol`.`netPrice` + `ol`.`couponCharge` < 0,
                                                  0, `ol`.`activePrice` - `ol`.`netPrice` - `ol`.`shippingCharge`)
                                               , 0) AS CHAR), '.', ','), 0), 2) AS CHAR), '.', ',') 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` AS `o`
    JOIN `OrderLine` AS `ol` ON `o`.`id` = `ol`.`orderId`
    JOIN `Product` AS `p` ON `p`.`id` = `ol`.`productId` AND `p`.`productVariantId` = `ol`.`productVariantId`
    JOIN `ProductBrand` as `pb` ON `pb`.id = `p`.`productBrandId`
    JOIN `ProductSeason` AS `pse` ON `pse`.`id` = `p`.`productSeasonId`
    JOIN `ProductSize` AS `ps` ON ol.`productSizeId` = `ps`.`id`
    JOIN Shop AS `sh` ON `ol`.`shopId` = `sh`.`id`
    /*LEFT JOIN (
      `ProductHasProductColorGroup` as `phpcg` JOIN `ProductColorGroup` as `pcg` ON `phpcg`.`productColorGroupId` = `pcg`.`id`
    ) ON `phpcg`.`productId` = `ol`.`productId` AND `phpcg`.`productVariantId` = `ol`.`productVariantId`*/
    LEFT JOIN `ShopHasProduct` AS `shp`
      ON `ol`.`shopId` = `shp`.`shopId` AND `ol`.`productVariantId` = `shp`.`productVariantId` AND
         `ol`.`productId` = `shp`.`productId`
    JOIN `Log` AS `l` ON `l`.stringId = `ol`.`orderId` OR `l`.`stringId` = `ol`.`id`
    JOIN `UserAddress` AS `ua` ON `o`.`billingAddressId` = `ua`.`id`
    JOIN `Country` AS `c` ON `c`.`id` = `ua`.`countryId`
    JOIN vQlikCategorie AS `pc` ON concat(`ol`.`productId`, '-', `ol`.`productVariantId`) = `pc`.`CodProd`
  WHERE `l`.`entityName` = 'Order'
  ORDER BY `o`.`orderDate` DESC;

CREATE OR REPLACE VIEW `vQlickFriendTimes` AS
  SELECT
    concat(`ol`.`id`, '-', `ol`.`orderId`) as `CodProd`,
    `s`.`id`                               AS `friendId`,
    `s`.`title`                               AS `FriendName`,
<<<<<<< Updated upstream
    `ols`.`friendResponseDays`            AS `FriendResponseDays`,
    `ols`.`friendTimeDays`                AS `FriendDays`,
    `ols`.`carrierFromFriendDays`         AS `CarrierFromFriendDays`
=======
    `ols`.FriendConfirmationDays            AS `FriendResponseDays`,
    `ols`.`FriendTimeDays`                AS `FriendDays`,
    `ols`.`CarrierFromFriendDays`         AS `CarrierFromFriendDays`
>>>>>>> Stashed changes
  FROM
    `OrderLineStatistics` as `ols`
    JOIN `OrderLine` as `ol` ON `ols`.`orderLineId` = `ol`.`id` AND `ols`.`orderId` = `ol`.`orderId`
    JOIN `Shop` as `s` ON `s`.`id` = `ol`.`shopId`;
    #JOIN `ProductSku` as `ps` ON `ps`.`productId` = `ol`.`productId` AND `ps`.`productVariantId` = `ol`.`productVariantId` AND `ps`;

CREATE OR REPLACE VIEW `vQlikCategorie` AS
  SELECT DISTINCT
    concat(p.id, '-', p.productVariantId)                                                 CodProd,
    pc.id                                                                              AS id,
    (SELECT max(CASE WHEN c.depth = 1
      THEN pct.name END) AS slug1
     FROM ProductCategory AS c
       JOIN ProductCategoryTranslation AS pct ON pct.productCategoryId = c.id
       , ProductCategory AS 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 AS c
       JOIN ProductCategoryTranslation AS pct ON pct.productCategoryId = c.id
       , ProductCategory AS 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 AS c
       JOIN ProductCategoryTranslation AS pct ON pct.productCategoryId = c.id
       , ProductCategory AS 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 AS c
       JOIN ProductCategoryTranslation AS pct ON pct.productCategoryId = c.id
       , ProductCategory AS 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 AS c
       JOIN ProductCategoryTranslation AS pct ON pct.productCategoryId = c.id
       , ProductCategory AS 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 AS c
       JOIN ProductCategoryTranslation AS pct ON pct.productCategoryId = c.id
       , ProductCategory AS c2
     WHERE pct.langId = 1 AND c.lft <= c2.lft AND c.rght >= c2.rght AND c2.id = pc.id) AS livello6
  FROM ProductCategory AS pc
    JOIN ProductHasProductCategory AS phpc ON phpc.productCategoryId = pc.id
    JOIN Product AS p ON phpc.productId = p.id AND phpc.productVariantId = p.productVariantId
  WHERE pc.id > 1 GROUP BY CodProd;

CREATE OR REPLACE VIEW `vQlikProdotti` AS
  SELECT
    concat(`p`.`id`, '-', `p`.`productVariantId`)                                                        AS `CodProd`,
    `shp`.`shopId`                                                                                       AS `CodShop`,
    REPLACE(CAST(IFNULL(`shp`.`value`, 0) AS CHAR), '.', ',')                                            AS `CostoForn`,
    REPLACE(CAST(IFNULL(IF(`p`.`isOnSale` = 0,
                           IF(`pse`.`isActive` = 1, `shp`.`value` / 100 * `s`.`currentSeasonMultiplier` + `shp`.`value`,
                              `shp`.`value` / 100 * `s`.`pastSeasonMultiplier` + `shp`.`value`),
                           `shp`.`value` / 100 * `s`.`saleMultiplier` + `shp`.`value`
                        ), 0) AS CHAR), '.',
            ',')                                                                                         AS `CostoFriend`,
    REPLACE(CAST(IFNULL(IF(`p`.`isOnSale` = 0, `psk`.`price`, `psk`.`salePrice`), 0) AS CHAR), '.', ',') AS `PrezzoAtt`,
    `pcg`.`name`                                                                                         AS `GruColore`,
    `psg`.`name`                                                                                         AS `codTaglia`,
    `pse`.`name`                                                                                         AS `Stagione`
  FROM `Product` AS `p`
    JOIN `ShopHasProduct` AS `shp`
      ON `p`.`productVariantId` = `shp`.`productVariantId` AND `p`.`productVariantId` AND `shp`.`productId` = `p`.`id`
    JOIN `ProductSeason` AS `pse` ON `p`.`productSeasonId` = `pse`.`id`
    JOIN `Shop` AS `s` ON `s`.`id` = `shp`.`shopId`
    JOIN `ProductSizeGroup` AS `psg` ON `p`.`productSizeGroupId` = `psg`.`id`
    LEFT JOIN `ProductColorGroup` AS `pcg` ON `p`.`productColorGroupId` = `pcg`.`id`
    LEFT JOIN (`ProductHasProductCategory` AS `phpc`
      JOIN `ProductCategory` AS `pc` ON `phpc`.`productCategoryId` = `pc`.`id`
      JOIN `ProductCategoryTranslation` AS `pct` ON `pc`.`id` = `pct`.`productCategoryId`)
      ON `phpc`.productVariantId = `p`.`productVariantId` AND `phpc`.`productId` = `p`.`id`
    JOIN `ProductSku` AS `psk` ON `psk`.`productId` = `p`.`id` AND `psk`.`productVariantId` = `p`.`productVariantId`
  WHERE (`pct`.`langId` = 1 OR `pct`.`langId` IS NULL) AND productStatusId IN (5, 6, 11) AND
        `p`.`dummyPicture` NOT LIKE '%bs-dummy%' AND `p`.`qty` > 0;


CREATE OR REPLACE VIEW `vQlikCampagneProdotti` AS
  SELECT
    c.name                                             AS CodAggr,
    date(cv.timestamp)                                 AS Data,
    concat(mahp.productId, '-', mahp.productVariantId) AS CodProd,
    count(DISTINCT cv.id)                              AS NumVisite,
    count(DISTINCT ol.orderId)                         AS NumConv,
    ifnull(sum(ol.netPrice), 0)                        AS ValVisite
  FROM
    MarketplaceAccount ma
    JOIN MarketplaceAccountHasProduct mahp
      ON ma.id = mahp.marketplaceAccountId AND ma.marketplaceId = mahp.marketplaceId
    JOIN Campaign c ON c.code = concat('MarketplaceAccount', ma.id, '-', ma.marketplaceId)
    JOIN CampaignVisit cv ON c.id = cv.campaignId
    JOIN CampaignVisitHasProduct cvhp
      ON cvhp.campaignId = cv.campaignId AND cvhp.campaignVisitId = cv.id AND cvhp.productId = mahp.productId AND
         cvhp.productVariantId = mahp.productVariantId
    LEFT JOIN (
      CampaignVisitHasOrder cvho
      JOIN OrderLine ol ON cvho.orderId = ol.orderId)
      ON cvho.campaignId = cv.campaignId AND cvho.campaignVisitId = cv.id AND ol.productId = mahp.productId AND
         ol.productVariantId = mahp.productVariantId
  GROUP BY mahp.productId, mahp.productVariantId, c.id, date(cv.timestamp);

CREATE OR REPLACE VIEW `vQlikUtenti` AS
  SELECT
    `u`.`id`                                    AS `CodUtente`,
    `ud`.`name`                                 AS `Nome`,
    `ud`.`surname`                              AS `Cognome`,
    `u`.`email`                                 AS `email`,
    DATE_FORMAT(`u`.`creationDate`, '%d/%m/%Y') AS `DataIscrizione`,
    `ua`.`address`                              AS `address`,
    `ua`.`postcode`                             AS `Cap`,
    `ua`.`city`                                 AS `Citta`,
    `ua`.`province`                             AS `Provincia`,
    `c`.`name`                                  AS `Stato`,
    `ua`.`phone`                                AS `Telefono`
  FROM
    `User` AS `u`
    JOIN `UserDetails` AS `ud` ON `u`.`id` = `ud`.`userId`
    JOIN `UserAddress` AS `ua` ON `u`.`id` = `ua`.`userId`
    JOIN `Country` AS `c` ON `c`.`id` = `ua`.`countryId`;

CREATE OR REPLACE VIEW `vQlikMovimenti` AS
  SELECT
    CONCAT(`sl`.`productId`, '-', `sl`.`productVariantId`)                 AS `CodProd`,
    DATE_FORMAT(`s`.`operationDate`, '%d/%m/%Y')                           AS `DataMov`,
    `sc`.`name`                                                            AS `Causale`,
    `uhs`.`shopId`                                                         AS `ShopId`,
    sum(`sl`.qty)                                                          AS `Qty`,
    `psz`.`name`                                                           AS `Taglia`,
    sum(`sl`.qty)                                                          AS `Qta`,
    REPLACE(CAST(IFNULL(IF(`p`.`isOnSale` = 0,
                           IF(`pse`.`isActive` = 1,
                              `shp`.`value` / 100 * `sh`.`currentSeasonMultiplier` + `shp`.`value`,
                              `shp`.`value` / 100 * `sh`.`pastSeasonMultiplier` + `shp`.`value`),
                           `shp`.`value` / 100 * `sh`.`saleMultiplier` + `shp`.`value`
                        ) * sum(`sl`.qty), 0) AS CHAR), '.', ',')          AS `ValCosFri`,
    IF(`p`.`isOnSale` = 0, `ps`.`price`, `ps`.`salePrice`) * sum(`sl`.qty) AS `ValPreAtt`
  FROM
    `StorehouseOperation` AS `s`
    JOIN `StorehouseOperationLine` AS `sl` ON `s`.`id` = `sl`.`storehouseOperationId`
    JOIN `StorehouseOperationCause` AS `sc` ON `sc`.`id` = `s`.`storehouseOperationCauseId`
    JOIN `UserHasShop` AS `uhs` ON `s`.`userId` = `uhs`.`userId`
    JOIN `ShopHasProduct` AS `shp`
      ON `shp`.`productVariantId` = `sl`.`productVariantId` AND `shp`.`shopId` = `sl`.`shopId`
    JOIN `Shop` AS `sh` ON `sl`.`shopId` = `sh`.`id`
    JOIN `ProductSku` AS `ps` ON `sl`.`productId` = `ps`.`productId` AND
                                 `sl`.`productVariantId` = `ps`.`productVariantId` AND
                                 `sl`.`productSizeId` = `ps`.`productSizeId` AND `sl`.`shopId` = `ps`.`shopId`
    JOIN `Product` AS `p` ON `sl`.`productVariantId` = `p`.`productVariantId` AND `sl`.`productId` = `p`.`id`
    JOIN `ProductSeason` AS `pse` ON `pse`.`id` = `p`.`productSeasonId`
    JOIN `ProductSize` AS `psz` ON `psz`.`id` = `sl`.`productSizeId`
  WHERE 1
  GROUP BY `s`.`id`, `sl`.`productVariantId`;

CREATE OR REPLACE VIEW vQlikMarketplaceCategoryStatistics_partial1 AS
  SELECT
    c.id                                            AS campaignId,
    c.marketplaceId,
    c.marketplaceAccountId,
    year(timestamp)                                 AS anno,
    month(timestamp)                                AS mese,
    cvhp.productId,
    cvhp.productVariantId,
    ifnull(sum(ol.netPrice), 0)                     AS conversionsValue,
    sum(CASE WHEN
      ol.productId = cvhp.productId AND
      ol.productVariantId = cvhp.productVariantId
      THEN ol.netPrice
        ELSE 0 END)                                 AS pConversionsValue,
    group_concat(DISTINCT ol.orderId SEPARATOR ',') AS ordersIds,
    count(DISTINCT cv.id)                           AS visits,
    count(DISTINCT o.id)                            AS conversions,
    #conversioni totali di questa visita
    round(sum(cv.cost), 2)                          AS visitsCost,
    count(CASE WHEN
      ol.productId = cvhp.productId AND
      ol.productVariantId = cvhp.productVariantId
      THEN o.id
          ELSE NULL END)                            AS pConversions #conversioni totali di questa visita per questo prodotto
  FROM
    Campaign c
    JOIN CampaignVisit cv ON cv.campaignId = c.id
    LEFT JOIN CampaignVisitHasProduct cvhp
      ON cvhp.campaignId = cv.campaignId AND cvhp.campaignVisitId = cv.id
    LEFT JOIN (CampaignVisitHasOrder cvho
    JOIN `Order` o ON cvho.orderId = o.id
    JOIN OrderLine ol ON o.id = ol.orderId
    ) ON cvho.campaignVisitId = cv.id AND
         cvho.campaignId = cv.campaignId
  GROUP BY cvhp.productId, cvhp.productVariantId, cvhp.campaignId, anno, mese;

CREATE OR REPLACE VIEW vQlikMarketplaceCategoryStatistics_partial2 AS
  SELECT
    concat(`p`.`id`, '-', `p`.`productVariantId`)          AS `codice`,
    `p`.`id`                                               AS `productId`,
    `p`.`productVariantId`                                 AS `productVariantId`,
    `p`.`itemno`                                           AS `itemno`,
    concat(`pss`.`name`, `pss`.`year`)                     AS `season`,
    `pb`.`name`                                            AS `brand`,
    `p`.`creationDate`                                     AS `creationDate`,
    concat(`m`.`name`, ' - ', `ma`.`name`)                 AS `marketplaceAccountName`,
    `s`.`name`                                             AS `shop`,
    `s`.`id`                                               AS `shopId`,
    `mahp`.`marketplaceProductId`                          AS `marketplaceProductId`,
    `mahp`.`marketplaceId`                                 AS `marketplaceId`,
    `mahp`.`marketplaceAccountId`                          AS `marketplaceAccountId`,
    `mahp`.`fee`                                           AS `fee`,
    p.qty                                                  AS stock,
    if(mahp.isToWork = 1, 'sìsi', 'no')                    AS isToWork,
    if(mahp.hasError = 1, 'sìsi', 'no')                    AS hasError,
    if(mahp.isDeleted = 1, 'sìsi', 'no')                   AS isDeleted,
    round(ifnull(visits, 0))                               AS visits,
    round(ifnull(visitsCost, 0))                           AS visitsCost,
    round(ifnull(conversions, 0))                          AS conversions,
    round(ifnull(conversionsValue, 0))                     AS conversionsValue,
    round(ifnull(pConversions, 0))                         AS pConversions,
    round(ifnull(pConversionsValue, 0))                    AS pConversionsValue,
    phpc.productCategoryId                                 AS categories,
    if(p.isOnSale = 0, min(shp.price), min(shp.salePrice)) AS activePrice,
    anno,
    mese
  FROM `Product` `p`
    JOIN `ProductStatus` `ps` ON ((`p`.`productStatusId` = `ps`.`id`))
    JOIN `ShopHasProduct` `shp`
      ON (((`p`.`id` = `shp`.`productId`) AND (`p`.`productVariantId` = `shp`.`productVariantId`)))
    JOIN `Shop` `s` ON ((`s`.`id` = `shp`.`shopId`))
    JOIN `ProductSeason` `pss` ON ((`pss`.`id` = `p`.`productSeasonId`))
    JOIN `ProductBrand` `pb` ON ((`p`.`productBrandId` = `pb`.`id`))
    JOIN ProductHasProductCategory phpc
      ON (p.id = phpc.productId AND p.productVariantId = phpc.productVariantId)
    JOIN `MarketplaceAccountHasProduct` `mahp`
      ON (((`mahp`.`productId` = `p`.`id`) AND (`mahp`.`productVariantId` = `p`.`productVariantId`)))
    JOIN `MarketplaceAccount` `ma`
      ON (((`ma`.`marketplaceId` = `mahp`.`marketplaceId`) AND
           (`ma`.`id` = `mahp`.`marketplaceAccountId`)))
    JOIN `Marketplace` `m` ON ((`m`.`id` = `ma`.`marketplaceId`))
    LEFT JOIN vQlikMarketplaceCategoryStatistics_partial1 AS sql2
      ON sql2.productId = mahp.productId AND sql2.productVariantId = mahp.productVariantId AND
         sql2.marketplaceId = mahp.marketplaceId AND
         sql2.marketplaceAccountId = mahp.marketplaceAccountId
  GROUP BY productId, productVariantId, marketplaceId, marketplaceAccountId, productCategoryId, anno, mese;

CREATE OR REPLACE VIEW vQlikMarketplaceCategoryStatistics AS
  SELECT
    Parent.id                        AS category,
    Parent.depth                     AS livello,
    anno,
    mese,
    marketplaceId,
    marketplaceAccountId,
    marketplaceAccountName,
    count(DISTINCT codice)           AS products,
    sum(visits)                      AS visits,
    sum(visitsCost)                  AS visitsCost,
    sum(conversions)                 AS conversions,
    ifnull(sum(conversionsValue), 0) AS conversionsValue,
    sum(pConversions)                AS pConversions,
    sum(pConversionsValue)           AS pConversionsValue
  FROM
    ProductCategory Child
    JOIN
    ProductCategory Parent ON Child.lft BETWEEN Parent.lft AND Parent.rght
    JOIN vQlikMarketplaceCategoryStatistics_partial2 AS sel3 ON Child.id = sel3.categories
  GROUP BY Parent.id, anno, mese, marketplaceId, marketplaceAccountId
  ORDER BY Child.lft;

CREATE OR REPLACE VIEW vQlikFriendAction as
  SELECT
    concat(`ol`.`orderId`, '-', `ol`.`id`) as `OrderLine`,
    `s`.`title` as `Friend`,
    `l`.`time` as InvioRichiesta,
    (SELECT max(`time`) FROM Log WHERE stringId = concat(`ol`.`id`, '-', `ol`.`orderId`) AND eventValue = 'ORD_FRND_OK') as Accettazione,
    (SELECT max(`time`) FROM Log WHERE stringId = concat(`ol`.`id`, '-', `ol`.`orderId`) AND eventValue = 'ORD_FRND_CANC') as Rifiuto,
    (SELECT max(`time`) FROM Log WHERE stringId = concat(`ol`.`id`, '-', `ol`.`orderId`) AND eventValue = 'ORD_FRND_ORDSNT') as SpeditoDalFriend,
    (SELECT max(`time`) FROM Log WHERE stringId = concat(`ol`.`id`, '-', `ol`.`orderId`) AND eventValue = 'ORD_FRND_CHK_IN') as CheckIn
  FROM `OrderLine` as `ol`
    JOIN `Shop` as `s` ON `s`.`id` = `ol`.`shopId`
    JOIN `Log` as l ON  concat(`ol`.`id`, '-',`ol`.`orderId`) = l.stringId
  WHERE `l`.`eventValue` = 'ORD_FRND_SENT' AND `l`.`id` > 32276;

CREATE OR REPLACE VIEW vQlikFriendTime as
  SELECT
    OrderLine,
    Friend,
    InvioRichiesta,
    DATEDIFF(Accettazione, InvioRichiesta) as AccettazioneGiorni,
    if(hour(Accettazione) - hour(InvioRichiesta) < 0, hour(Accettazione) - hour(InvioRichiesta) + 24, hour(Accettazione) - hour(InvioRichiesta))  as AccettazioneOre,
    if(minute(Accettazione) - minute(InvioRichiesta) < 0, minute(Accettazione) - minute(InvioRichiesta) + 60, minute(Accettazione) - minute(InvioRichiesta))  as AccettazioneMinuti,
    DATEDIFF(SpeditoDalFriend, Accettazione) as SpeditoDalFriendGiorni,
    if(hour(SpeditoDalFriend) - hour(Accettazione) < 0, hour(SpeditoDalFriend) - hour(Accettazione) + 24, hour(SpeditoDalFriend) - hour(Accettazione)) as SpeditoDalFriendOre,
    if(minute(SpeditoDalFriend) - minute(Accettazione) < 0, minute(SpeditoDalFriend) - minute(Accettazione) + 24, minute(SpeditoDalFriend) - minute(Accettazione)) as SpeditoDalFriendMinuti,
    DATEDIFF(CheckIn, SpeditoDalFriend) as CheckInGiorni,
    if(hour(CheckIn) - hour(SpeditoDalFriend) < 0, hour(CheckIn) - hour(SpeditoDalFriend) + 24, hour(CheckIn) - hour(SpeditoDalFriend)) as CheckInOre,
    if(minute(CheckIn) - minute(SpeditoDalFriend) < 0, minute(CheckIn) - minute(SpeditoDalFriend) + 24, minute(CheckIn) - minute(SpeditoDalFriend)) as CheckInMinute,
    DATEDIFF(Rifiuto, InvioRichiesta) as RifiutoGiorni,
    if(hour(Rifiuto) - hour(InvioRichiesta) < 0, hour(Rifiuto) - hour(InvioRichiesta) + 24, hour(Rifiuto) - hour(InvioRichiesta)) as RifiutoOre,
    if(minute(Rifiuto) - minute(InvioRichiesta) < 0, minute(Rifiuto) - minute(InvioRichiesta) + 24, minute(Rifiuto) - minute(InvioRichiesta)) as RifiutoMinuti
    FROM vQlickFriendAction;