CREATE OR REPLACE VIEW vBluesealOrdersByUsersList AS
SELECT
  concat(`o`.`id`) AS `id`,
  concat(`ud`.`name`, ' ', `ud`.`surname`) AS `user`,
  `ud`.`name` AS `name`,
  `ud`.`surname` AS `surname`,
  `u`.`email` AS `email`,
  `ua`.`city` AS `city`,
  `c`.`name` as `country`,
  `o`.`status` as `statusCode`,
  concat(`o`.`orderDate`) AS `orderDate`,
  group_concat(`ol`.`productId`, '-', `ol`.`productVariantId`) AS `product`,
  group_concat(`s`.`title`) AS `shop`,
  `os`.`title` AS `orderStatus`,
  group_concat(`pb`.`name`) AS `productBrand`,
  `opm`.`name` as `paymentMethod`
FROM ((((((((((((`Order` `o`
JOIN `User` `u`)
JOIN `UserAddress` `ua` on `ua`.`id` = `o`.`billingAddressId` OR `ua`.`id` = `o`.`shipmentAddressId`)
JOIN `Country` `c` on `ua`.`countryId` = `c`.`id`)
JOIN `UserDetails` `ud`)
JOIN `OrderPaymentMethod` `opm`)
JOIN `OrderStatus` `os`)
JOIN `OrderStatusTranslation` `oshl`)
JOIN `OrderLine` `ol`) JOIN `Shop` `s`)
JOIN `OrderLineStatus` `ols`)
JOIN `Product` `p`)
JOIN `ProductBrand` `pb`)
WHERE ((`o`.`userId` = `u`.`id`) AND (`ud`.`userId` = `u`.`id`) AND (`o`.`orderPaymentMethodId` = `opm`.`id`) AND
(`o`.`status` = `os`.`code`) AND (`o`.`status` LIKE 'ORD%') AND (`oshl`.`orderStatusId` = `os`.`id`) AND
(`ol`.`orderId` = `o`.`id`) AND (`s`.`id` = `ol`.`shopId`) AND (`ol`.`productId` = `p`.`id`) AND
(`ol`.`productVariantId` = `p`.`productVariantId`) AND (`p`.`productBrandId` = `pb`.`id`) AND
(`ol`.`status` = CONVERT (`ols`.`code` USING utf8))) AND `o`.`shipmentAddressId` is not null  GROUP BY `o`.`id`;
