# AppSupport

## Query to check stock / unlimited stock

SELECT 
      ss.warehouse_id as "warehouseId"
    FROM
      catalogues c
    JOIN
      brands b on b.id = c.brand_id
    JOIN
      supplier_stores ss on ss.supplier_id = b.supplier_id
    JOIN
      stores s on ss.store_id = s.id
    WHERE
      s.id = {storeId}
      AND c.id in (catalogue_id)

SELECT
        wc.warehouse_id as "warehouseId",
        wc.id as "warehouseCatalogueId",
        wc.stock,
        wc.unlimited_stock as "unlimitedStock"
      FROM
        warehouse_urbans AS wu
        JOIN warehouse_catalogues AS wc ON wc.warehouse_id = wu.warehouse_id
      WHERE
        wu.urban_id in (
          SELECT
            ss.urban_id
          FROM
            supplier_stores ss
          JOIN
            brands b ON b.supplier_id = ss.supplier_id
          JOIN
            catalogues c on c.brand_id = b.id 
          WHERE
            c.id in  (catalogue_id)
            AND ss.store_id = {storeId}
        )
        AND wc.catalogue_id in  (catalogue_id)
        AND wc.deleted_at is null
      ORDER BY wc.warehouse_id ASC
      LIMIT 1;

## Query to check Search Store List

SELECT
a.id,
a.store_id AS "storeId",
a.external_id AS "externalId",
a.name,
a.address,
f.name AS "province",
e.city,
e.district,
e.urban,
e.zip_code AS "zipCode",
a.note_address AS "noteAddress",
a.tax_no AS "taxNo",
a.longitude,
a.latitude,
a.large_area AS "largeArea",
a.phone_no AS "phoneNo",
a.image_url AS "storePhoto",
a.number_of_employee AS "numberOfEmployee",
a.tax_image_url AS "taxImageUrl",
a.number_of_employee AS "numberOfEmployee",
a.urban_id AS "urbanId",
a.top_selling_brand AS "topSellingBrand",
a.most_wanted_brand AS "mostWantedBrand",
a.status,
a.approval_status AS "approvalStatus",
a.created_at AS "createdAt",
(SELECT full_name FROM users u LEFT JOIN user_stores us ON u.id = us.user_id WHERE us.store_id = a.store_id ORDER BY us.created_at DESC LIMIT 1) AS "ownerName",
(SELECT mobile_phone_no FROM users u LEFT JOIN user_stores us ON u.id = us.user_id WHERE us.store_id = a.store_id ORDER BY us.created_at DESC LIMIT 1) AS "mobilePhoneNo",
(SELECT join_date FROM users u LEFT JOIN user_stores us ON u.id = us.user_id WHERE us.store_id = a.store_id ORDER BY us.created_at DESC LIMIT 1) AS "joinDate",
g.name AS "vehicleAccessibility",
(SELECT name FROM store_types st LEFT JOIN types t ON st.type_id = t.id WHERE st.store_id = b.id LIMIT 1) AS "storeType",
(SELECT name FROM store_clusters sc LEFT JOIN clusters c ON sc.cluster_id = c.id WHERE sc.store_id = c.id LIMIT 1) AS "storeCluster",
(SELECT name FROM store_groups sg LEFT JOIN groups g ON sg.group_id = g.id WHERE sg.store_id = g.id LIMIT 1) AS "storeGroup",
(SELECT name FROM store_channels scn LEFT JOIN channels cn ON scn.channel_id = cn.id WHERE scn.store_id = cn.id LIMIT 1) AS "storeChannel"
FROM supplier_stores a
LEFT JOIN stores b ON a.store_id = b.id
LEFT JOIN urbans e ON a.urban_id = e.id
LEFT JOIN provinces f ON e.province_id = f.id
LEFT JOIN vehicle_accessibilities g ON g.id = a.vehicle_accessibility_id
WHERE a.deleted_at IS NULL
AND supplier_id = 44
AND
(
a.name like '%keyword%' OR
a.external_id like '%keyword%' OR
a.address like '%keyword%' OR
a.phone_no like '%keyword%' OR
(SELECT full_name FROM users u LEFT JOIN user_stores us ON u.id = us.user_id WHERE us.store_id = a.store_id AND u.deleted_at IS NULL ORDER BY us.created_at DESC LIMIT 1) like '%keyword%' OR
(SELECT mobile_phone_no FROM users u LEFT JOIN user_stores us ON u.id = us.user_id WHERE us.store_id = a.store_id AND u.deleted_at IS NULL ORDER BY us.created_at DESC LIMIT 1) like '%keyword%'
)

## Query to check the PENDING order mapping problem

select DISTINCT
	CASE 
		WHEN s.urban_id = wu.urban_id then 'PASS'
		ELSE 'FAILED'
	END as CheckUrbanId,
	CASE
		WHEN invg.external_id is NULL then CONCAT('FAILED => ', 'external_id InvoiceGroup is NULL')
		ELSE 'PASS'
	
	END as CheckExternalIdInvoiceGroup,
	CASE 
		WHEN obc.warehouse_id = wu.warehouse_id then 'PASS'
		ELSE CONCAT('FAILED => ', 'update warehouseId_obc from ',obc.warehouse_id, ' to ', wu.warehouse_id)
	END as CheckWarehouseId,
	CASE 
		WHEN CAST ( substring(w.external_id,1, 2) AS INT ) = tsp.initial_sellpo_code then 'PASS'
		ELSE CONCAT('FAILED => ', 'externalID Warehouse difference ',substring(cast(w.external_id as varchar(100)),1, 2), ' with TRS SellPoCode ', tsp.initial_sellpo_code)
	END as CheckWarehouseSellPoCode,
    CASE 
		WHEN CAST ( substring(s.external_id,1, 2) AS VARCHAR(2) ) = CAST (tsp.initial_sellpo_code AS VARCHAR(2)) then 'PASS'
		ELSE CONCAT('FAILED => ', 'externalID Store difference ',substring(cast(s.external_id as varchar(100)),1, 2), ' with TRS SellPoCode ', tsp.initial_sellpo_code)
	END as CheckStoreSellPoCode,
	op.status,
	op.order_code,
	op.order_ref,
	op.id as orderParcelId,
	s.urban_id as urbanId_supplierStore,
	wu.urban_id as urbanId_warehouseUrban,
	w.id as warehouseId,
	obc.warehouse_id as warehouseId_obc,
	wu.warehouse_id as warehouseId_warehouseUrban,
	w.external_id as externalId_warehouse,
	w."name" as sinbad_warehousename,
	s.external_id as externalId_supplierStore,
	s."name" as name_supplierstore,
	us.user_code as userCode_users,
	st.creator_id as store_CreatorId,
	us.id as id_users,
	op.created_at
	from supplier_stores as s 
	left join stores as st on st.id=s.store_id
	left join urbans as u on u.id=s.urban_id 
	left join warehouse_urbans as wu on wu.urban_id=u.id
	left join warehouses as w on w.id=wu.warehouse_id
	left join trs_sellpocode tsp on tsp.description = w."name"
	left join orders as o on o.store_id=s.store_id
	left join order_parcels as op on op.order_id=o.id
	inner join invoice_groups as invg on invg.id = op.invoice_group_id
	left join order_brands ob on ob.order_parcel_id = op.id
	left join order_brand_catalogues obc on obc.order_brand_id = ob.id
	left join portfolios as p on p.id = op.portfolio_id
	left join users as us on us.id = p.user_id
	where w.supplier_id ='1' 
	and op.status = 'pending_supplier' 
	-- 	and w."name" = 'DC PULOGEBANG1'
	and op.order_code in ('S0100182248951250747')

## Query to Count all Pending Supplier in Each DC

select A.sinbad_warehousename, count(1) from 
(
	select DISTINCT
	CASE 
		WHEN s.urban_id = wu.urban_id then 'PASS'
		ELSE 'FAILED'
	END as CheckUrbanId,
	CASE 
		WHEN obc.warehouse_id = wu.warehouse_id then 'PASS'
		ELSE CONCAT('FAILED => ', 'update warehouseId_obc from ',obc.warehouse_id, ' to ', wu.warehouse_id)
	END as CheckWarehouseId,
	op.status,
	op.order_code,
	op.order_ref,
	op.id as orderParcelId,
	s.urban_id as urbanId_supplierStore,
	wu.urban_id as urbanId_warehouseUrban,
	w.id as warehouseId,
	obc.warehouse_id as warehouseId_obc,
	wu.warehouse_id as warehouseId_warehouseUrban,
	substring(cast(wu.warehouse_id as varchar(100)),1, 2) as prefix_externalID,
	w.external_id as externalId_warehouse,
	w."name" as sinbad_warehousename,
	s.external_id as externalId_supplierStore,
	s."name" as name_supplierstore,
	us.user_code as userCode_users,
	st.creator_id as store_CreatorId,
	us.id as id_users,
	op.created_at
	from supplier_stores as s 
	left join stores as st on st.id=s.store_id
	left join urbans as u on u.id=s.urban_id 
	left join warehouse_urbans as wu on wu.urban_id=u.id
	left join warehouses as w on w.id=wu.warehouse_id
	left join orders as o on o.store_id=s.store_id
	left join order_parcels as op on op.order_id=o.id
	left join order_brands ob on ob.order_parcel_id = op.id
	left join order_brand_catalogues obc on obc.order_brand_id = ob.id
	left join portfolios as p on p.id = op.portfolio_id
	left join users as us on us.id = p.user_id
	where w.supplier_id ='1' 
	and op.status = 'pending_supplier' 
-- 		and w."name" = 'DC PULOGEBANG1'
	-- and op.order_code in ('S0100182137351232163')
) A 
GROUP by A.sinbad_warehousename
order by A.sinbad_warehousename ASC

### Query for Update OrderBrandCatalogue WarehouseId

select * from order_brand_catalogues obc 
inner join order_brands ob on ob.id = obc.order_brand_id
inner join order_parcels op on op.id = ob.order_parcel_id
where op.order_code = 'S0100182137351232163'

### Query for Insert Table TRS SellPoCode

CREATE TEMP TABLE trs_sellpocode (
   description varchar(50),
   region varchar(10),
   initial_sellpo_code INT
);
INSERT INTO trs_sellpocode(description, region, initial_sellpo_code) values ('DC TANGERANG', 'REGION 1',16);
INSERT INTO trs_sellpocode(description, region, initial_sellpo_code) values ('DC CIBINONG', 'REGION 1',20);
INSERT INTO trs_sellpocode(description, region, initial_sellpo_code) values ('DC SERANG', 'REGION 1',18);
INSERT INTO trs_sellpocode(description, region, initial_sellpo_code) values ('DC CIKAMPEK', 'REGION 1',14);
INSERT INTO trs_sellpocode(description, region, initial_sellpo_code) values ('DC PULOGEBANG1', 'REGION 1',10);
INSERT INTO trs_sellpocode(description, region, initial_sellpo_code) values ('DC PULOGEBANG2', 'REGION 1',12);
INSERT INTO trs_sellpocode(description, region, initial_sellpo_code) values ('DC CIANJUR', 'REGION 1',26);
INSERT INTO trs_sellpocode(description, region, initial_sellpo_code) values ('DC SUKABUMI', 'REGION 1',24);
INSERT INTO trs_sellpocode(description, region, initial_sellpo_code) values ('DC BANDUNG', 'REGION 1',22);
INSERT INTO trs_sellpocode(description, region, initial_sellpo_code) values ('DC GARUT', 'REGION 1',30);
INSERT INTO trs_sellpocode(description, region, initial_sellpo_code) values ('DC CIREBON', 'REGION 1',28);
INSERT INTO trs_sellpocode(description, region, initial_sellpo_code) values ('DC KUDUS', 'REGION 2',34);
INSERT INTO trs_sellpocode(description, region, initial_sellpo_code) values ('DC SEMARANG', 'REGION 2',32);
INSERT INTO trs_sellpocode(description, region, initial_sellpo_code) values ('DC YOGYAKARTA', 'REGION 2',40);
INSERT INTO trs_sellpocode(description, region, initial_sellpo_code) values ('DC PURWOKERTO', 'REGION 2',36);
INSERT INTO trs_sellpocode(description, region, initial_sellpo_code) values ('DC SOLO', 'REGION 2',42);
INSERT INTO trs_sellpocode(description, region, initial_sellpo_code) values ('DC SURABAYA', 'REGION 3',44);
INSERT INTO trs_sellpocode(description, region, initial_sellpo_code) values ('DC JEMBER', 'REGION 3',50);
INSERT INTO trs_sellpocode(description, region, initial_sellpo_code) values ('DC MALANG', 'REGION 3',52);
INSERT INTO trs_sellpocode(description, region, initial_sellpo_code) values ('DC TEGAL', 'REGION 3',38);
INSERT INTO trs_sellpocode(description, region, initial_sellpo_code) values ('DC MADIUN', 'REGION 3',48);
INSERT INTO trs_sellpocode(description, region, initial_sellpo_code) values ('DC JOMBANG', 'REGION 3',46);
INSERT INTO trs_sellpocode(description, region, initial_sellpo_code) values ('DC KEDIRI', 'REGION 3',54);

drop table trs_sellpocode;

###### tags: `Documents`