CREATE OR REPLACE VIEW vBluesealFriendsList AS
  SELECT
    `s`.`name`                                                                              AS `shop`,
    concat(`pse`.`name`, ' ', `pse`.`year`)                                                 AS `season`,
    count(DISTINCT `p`.`id`, `p`.`productVariantId`)                                        AS `prodotti`,
    count(distinct if(p.productStatusId = 6,`p`.`productVariantId`,null)) as pubblicati,
    sum(`ps`.`stockQty`)                                                                    AS `quantita`,
    round(sum(`ps`.`value`))                                                                       AS `valore_al_costo`,
    round(sum(`ps`.`price`))                                                                       AS `valore_al_prezzo`,
    round(
        ifnull(sum(if(((`ol`.`id` IS NOT NULL) AND (`ols`.`id` IN (7, 8, 19, 10, 11, 12, 13))), `ol`.`friendRevenue`, 0)),
               0), 0)                                                                       AS `incasso_friend`,
    round(ifnull(sum(if(((`ol`.`id` IS NOT NULL) AND (`ols`.`id` IN (7, 8, 19, 10, 11, 12, 13))),
                        (`ol`.`netPrice` - `ol`.`friendRevenue`), 0)), 0), 0)               AS `incasso_picky`,
    sum(if(((`ol`.`id` IS NOT NULL) AND (`ols`.`id` IN (7, 8, 19, 10, 11, 12, 13))), 1, 0)) AS `venduto`,
    sum(if(((`ol`.`id` IS NOT NULL) AND (`ols`.`id` IN (6, 14, 16, 15))), 1, 0))            AS `cancellato`
  FROM ((((((`Product` `p` JOIN `ProductSeason` `pse`) JOIN `ProductSku` `ps`) JOIN `ShopHasProduct` `shp`) JOIN
    `Shop` `s`) LEFT JOIN `OrderLine` `ol`
      ON (`ol`.`productId` = `ps`.`productId`) AND (`ol`.`productVariantId` = `ps`.`productVariantId`) AND
         (`ol`.`productSizeId` = `ps`.`productSizeId`) AND (`ol`.`shopId` = `ps`.`shopId`)) left JOIN
    `OrderLineStatus` `ols` on `ol`.`status` = `ols`.`code`  left JOIN `Order` `o` on `o`.`id` = `ol`.`orderId`)
  WHERE (
    (`p`.`productSeasonId` = `pse`.`id`) AND (`p`.`id` = `shp`.`productId`) AND
    (`p`.`productVariantId` = `shp`.`productVariantId`) AND (`shp`.`shopId` = `s`.`id`) AND
    (`shp`.`productId` = `ps`.`productId`) AND (`shp`.`productVariantId` = `ps`.`productVariantId`) AND
    (`shp`.`shopId` = `ps`.`shopId`))
  GROUP BY `s`.`id`, `pse`.`id`;