# get lotus 
- versi ngelu
```sql
WITH orders AS (
  SELECT
    o.id,
    i.variant_id,
    o.display_id,
    o.store_code,
    o.customer_id,
    o.metadata,
    o.created_at,
    C.first_name,
    C.last_name,
    C.phone
  FROM
    "order" o
    JOIN customer C ON o.customer_id = C .id
    JOIN logistic_order lo ON o.id = lo.commercial_order_id
    JOIN line_item i ON o.id = i.order_id
    -- JOIN product_variant p ON i.variant_id = p.id
  WHERE
    lo.status IN ('SHIPPED', 'RECEIVED')
    AND o.created_at BETWEEN {{from}} AND {{to}}
)
SELECT
  o. *,
--   sku,
  (o.metadata ->> 'customer') :: json ->> 'makroMemberNo' AS "makroMemberNo",
  LTRIM(
    SUBSTRING(
      CONCAT(
        REPEAT(
          '0',
          14 - LENGTH(
            (o.metadata ->> 'customer') :: json ->> 'makroMemberNo'
          )
        ),
        (o.metadata ->> 'customer') :: json ->> 'makroMemberNo'
      ),
      1,
      9
    ),
    '0'
  ) AS "loyalty_mmid"
FROM
  orders o
 WHERE
   variant_id IN (
 '25932551699021940000',
'27971231702549210000',
'27971351702549210000',
'25932341699021943000',
'27971221702549210000',
'25932351699021943000',
'25932491699021943000',
'26111991699948190000',
'26106551699414344000',
'25932051699021940000',
'27971471702549210000',
'27971521702549210000',
'27971271702549205000',
'25932121699021943000',
'25935271699242750000',
'26111971699948188000',
'27971381702549210000',
'25928871698899857000',
'25932511699021940000',
'27971391702549210000',
'25932371699021943000',
'27971441702549210000',
'25928891698899993000',
'25932311699021943000',
'27971481702549210000',
'25932421699021943000',
'25932641699021943000',
'25932201699021940000',
'25932601699021943000',
'25932671699021943000',
'27971281702549210000',
'25932381699021940000',
'25932081699021940000',
'25932251699021940000',
'25928911698900206000',
'25932181699021943000',
'25932651699021943000',
'25932541699021943000',
'27971171702549210000',
'27971321702549210000',
'25932101699021940000',
'25932171699021940000',
'25928931698900464000',
'25932441699021943000',
'27971361702549210000',
'25932591699021943000',
'25932471699021943000',
'25932521699021943000',
'27971201702549210000',
'25932241699021943000',
'27971401702549210000',
'25932291699021943000',
'27971531702549210000',
'25932461699021943000',
'25932151699021940000',
'25932571699021943000',
'27971311702549210000',
'25932271699021943000',
'27971451702549210000',
'27971251702549210000',
'26106561699414397000',
'26112001699948250000',
'25932561699021943000',
'25928861698898743000',
'25932261699021943000',
'25932451699021943000',
'25928901698900070000',
'25932301699021943000',
'25932661699021943000',
'27971491702549210000',
'27971291702549210000',
'25932621699021943000',
'25932191699021940000',
'25928881698899930000',
'25932431699021940000',
'27971371702549210000',
'25932401699021940000',
'25932681699021943000',
'25932631699021940000',
'25932611699021943000',
'27971501702549210000',
'25932091699021940000',
'25932071699021940000',
'27971421702549205000',
'27971261702549210000',
'25932221699021940000',
'25932391699021943000',
'25932131699021943000',
'27971301702549210000',
'27971431702549210000',
'27971411702549210000',
'25932321699021943000',
'25932481699021943000',
'27971331702549210000',
'25932581699021940000',
'27971211702549210000',
'25932111699021943000',
'26111981699948190000',
'26106581699414680000',
'25932691699021943000',
'25932161699021940000',
'25932061699021943000',
'25932231699021943000',
'27971191702549210000',
'25932531699021943000',
'27971241702549210000',
'25932141699021943000',
'25928921698900420000',
'25932331699021943000',
'25932501699021940000',
'27971461702549210000',
'27971341702549205000',
'25932361699021943000',
'25932411699021943000',
'25932211699021943000',
'27971511702549210000',
'27971181702549210000',
'26106571699414510000',
'26111961699948190000',
'25932281699021943000',
'25932041699021943000'
   )
```