# NewStore Check Query for Promo

## Select Order from Store within 6 Month Ago

SELECT op.id
      FROM
        orders AS o
      JOIN
        order_parcels AS op ON op.order_id = o.id
      JOIN
        order_brands AS ob ON ob.order_parcel_id = op.id
      JOIN
        order_brand_catalogues AS obc ON obc.order_brand_id = ob.id
      WHERE
        o.store_id = [StoreID yg digunakan]
      AND op.created_at > 'now'::timestamp - '6 month'::interval;
      

## Update Order from Store within 6 Month Ago

update order_parcels set created_at = 'now'::timestamp - '7 month'::interval 
WHERE order_parcels.id in ([OrderParcelId])

# promo Query

## Check Collectible Voucher code

    select V.voucher_allocation_type,V.voucher_slot, V.max_collection_per_store, VC.voucher_id, V.code, count(VC.voucher_id) as JumlahCollected
    from vouchers V 
    inner join voucher_collected VC on VC.voucher_id = V.id
    where V.supplier_id = 44 and v.voucher_type <> 'direct'
    GROUP by V.voucher_allocation_type,V.voucher_slot, V.max_collection_per_store, V.code,VC.voucher_id

## Store target

SELECT
      p.id AS promo_id, p.base, p.type, p.first_buy, p.voucher_combine, p.max_redemption_per_store,
      p.promo_budget, p.start_date, p.end_date, pc.id as promo_condition_id, pc.benefit_type, pc.benefit_discount, pc.benefit_max_rebate,
      pc.benefit_rebate, pc.benefit_catalogue_id, pc.benefit_bonus_qty, pc.multiplication, pc.condition_base, pc.condition_qty, pc.condition_value,
      pca.catalogue_id, pb.brand_id, pig.invoice_group_id
    FROM
      promos p
    JOIN
      promo_stores ps ON ps.promo_id = p.id
    LEFT JOIN
      promo_catalogues pca ON pca.promo_id = p.id
    LEFT JOIN
      promo_brands pb ON pb.promo_id = p.id
    LEFT JOIN
      promo_invoice_groups pig ON pig.promo_id = p.id
    JOIN
      promo_conditions pc ON pc.promo_id = p.id
    WHERE
      ps.store_id = ${data.storeId}
      AND p.target = 'store'
      AND p.status = 'active'
      AND p.type in ('flexi', 'target')
      AND p.start_date < now()
      AND p.end_date > now()
      AND (p.platform = 'all' OR p.platform = '${data.platform}')
      AND p.deleted_at is null
      AND
        CASE
          WHEN p.base = 'sku' THEN pca.catalogue_id IN (${data.catalogueIds})
          WHEN p.base = 'brand' THEN pb.brand_id IN (${brandIds})
          WHEN p.base = 'invoice_group' THEN pig.invoice_group_id IN (${invoiceGroupIds})
        END

## Segmentation Target

SELECT
      res.promo_id, res.base, res.type, res.first_buy, res.voucher_combine, res.max_redemption_per_store,
      res.promo_budget, res.start_date, res.end_date, pc.id as promo_condition_id, pc.benefit_type, pc.benefit_discount, pc.benefit_max_rebate,
      pc.benefit_rebate, pc.benefit_catalogue_id, pc.benefit_bonus_qty, pc.multiplication, pc.condition_base,
      pc.condition_qty, pc.condition_value, pca.catalogue_id, pb.brand_id, pig.invoice_group_id
    FROM
      (SELECT
        pr.*
      FROM
        (SELECT
          p.id as promo_id, p.base, p.type, p.first_buy, p.voucher_combine, p.max_redemption_per_store,
          p.promo_budget, p.start_date, p.end_date, promo_channels.channel_id, promo_clusters.cluster_id, promo_groups.group_id,
          promo_types.type_id, promo_warehouses.warehouse_id
        FROM
          promos p
        LEFT JOIN
          promo_channels ON promo_channels.promo_id = p.id
        LEFT JOIN
          promo_clusters ON promo_clusters.promo_id = p.id
        LEFT JOIN
          promo_groups ON promo_groups.promo_id = p.id
        LEFT JOIN
          promo_types ON promo_types.promo_id = p.id
        LEFT JOIN
          promo_warehouses ON promo_warehouses.promo_id = p.id
        WHERE p.deleted_at IS NULL
          AND p.target = 'segmentation'
          AND (p.platform = 'all' OR p.platform = '${data.platform}')
          AND p.start_date < now()
          AND p.end_date > now()
          AND p.status = 'active'
          AND p.type in ('flexi', 'target')
        ) AS pr
      CROSS JOIN
        (SELECT
          s.id AS store_id, s.urban_id, s.store_code, s.name, store_channels.channel_id,
          store_clusters.cluster_id, store_groups.group_id, store_types.type_id
        FROM
          stores AS s
        LEFT OUTER JOIN
          store_channels ON s.id = store_channels.store_id
        LEFT OUTER JOIN
          store_clusters ON s.id = store_clusters.store_id
        LEFT OUTER JOIN
          store_groups ON s.id = store_groups.store_id
        LEFT OUTER JOIN
          store_types ON s.id = store_types.store_id
        WHERE
          s.id = ${data.storeId}
        ) AS sr
      WHERE 
        (pr.cluster_id = sr.cluster_id
          OR pr.cluster_id IS NULL)
      AND
        (pr.channel_id = sr.channel_id
          OR pr.channel_id IS NULL)
      AND
        (pr.group_id = sr.group_id
          OR pr.group_id IS NULL)
      AND
        (pr.type_id = sr.type_id
          OR pr.type_id IS NULL)
      AND
        (pr.warehouse_id in (${warehouseIds})
          OR pr.warehouse_id IS NULL)
      ) AS res
      JOIN
        promo_conditions pc ON pc.promo_id = res.promo_id
      LEFT JOIN
        promo_catalogues pca ON pca.promo_id = res.promo_id
      LEFT JOIN
        promo_brands pb ON pb.promo_id = res.promo_id
      LEFT JOIN
        promo_invoice_groups pig ON pig.promo_id = res.promo_id
      WHERE
        CASE
          WHEN res.base = 'sku' THEN pca.catalogue_id IN (${data.catalogueIds})
          WHEN res.base = 'brand' THEN pb.brand_id IN (${brandIds})
          WHEN res.base = 'invoice_group' THEN pig.invoice_group_id IN (${invoiceGroupIds})
        END

### notes

1. ${data.storeId} itu diganti store id dari store yang lagi checkout
2. ${data.platform} itu diganti dengan dia loginnya dari mana, 'all' atau 'agent_app' atau 'sinbad_app'
3. ${data.catalogueIds} itu list dari catalogue id yang dicheckout, contohnya 1, 2, 3
4. ${brandIds} itu list dari brand id dari catalogue yang dicheckout, contohnya 1, 2, 3
5. ${invoiceGroupIds} itu list dari invoice group id dari catalogue yang dicheckout, contohnya 1, 2, 3
6. ${warehouseIds} itu list dari warehouse id dari catalogue yang dicheckout, contohnya 1, 2, 3 . ketentuan ngambil warehouse_id-nya:
a. ambil dari supplier_stores.warehouse_id langsung
b. ambil dari warehouse_urbans.warehouse_id dengan urban_id dari supplier_stores.urban_id
c. ambil dari warehouse_urbans.warehouse_id dengan urban_id dari stores.urban_id

tiga kondisi buat ngambil warehouse itu dijalannin dari a-c, prioritas utama a dulu, kalo ngga ada baru cek ke kondisi b, dst