create view vBluesealProductImporter as
SELECT
  `p`.`id`                                 AS `id`,
  `p`.`productVariantId`                   AS `productVariantId`,
  concat(`p`.`itemno`, ' # ', `pv`.`name`) AS `code`,
  `s`.`name`                               AS `shop`,
  `s`.`id`                                 AS `shopId`,
  `pb`.`name`                              AS `brand`,
  `p`.`externalId`                         AS `externalId`,
  `ps`.`name`                              AS `status`,
  `p`.`creationDate`                       AS `creationDate`
FROM (((((((`Product` `p`
  JOIN `ProductVariant` `pv` ON ((`pv`.`id` = `p`.`productVariantId`))) JOIN `ProductBrand` `pb`
    ON ((`p`.`productBrandId` = `pb`.`id`))) JOIN `ProductStatus` `ps` ON ((`p`.`productStatusId` = `ps`.`id`))) JOIN
  `DirtyProduct` `dp` ON (((`p`.`id` = `dp`.`productId`) AND (`p`.`productVariantId` = `dp`.`productVariantId`)))) JOIN
  `DirtySku` `ds` ON ((`dp`.`id` = `ds`.`dirtyProductId`))) LEFT JOIN `ShopHasProduct` `sp`
    ON (((`dp`.`productId` = `sp`.`productId`) AND (`dp`.`productVariantId` = `sp`.`productVariantId`) AND
         (`dp`.`shopId` = `sp`.`shopId`)))) JOIN `Shop` `s` ON ((`sp`.`shopId` = `s`.`id`)))
WHERE ((`ps`.`id` NOT IN (7, 8, 12, 13)) AND (`s`.`importer` IS NOT NULL) AND (`ds`.`status` <> 'ok'))
GROUP BY `dp`.`productId`, `dp`.`productVariantId`, `dp`.`shopId`
HAVING (sum(`ds`.`qty`) > 0);