128 lines
4.2 KiB
PL/PgSQL
128 lines
4.2 KiB
PL/PgSQL
-- 1) GRN: warehouse_id → location_id (from PO shipping_id)
|
|
-- 2) Vendor bank details: only one active primary per vendor
|
|
-- 3) asset_service_visits: drop visit_number
|
|
-- Idempotent: safe to re-run.
|
|
|
|
BEGIN;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 1. GRN warehouse_id → location_id
|
|
-- ---------------------------------------------------------------------------
|
|
DROP VIEW IF EXISTS v_grn CASCADE;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'public' AND table_name = 'grn' AND column_name = 'warehouse_id'
|
|
) AND NOT EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'public' AND table_name = 'grn' AND column_name = 'location_id'
|
|
) THEN
|
|
ALTER TABLE grn RENAME COLUMN warehouse_id TO location_id;
|
|
END IF;
|
|
END $$;
|
|
|
|
ALTER TABLE grn ADD COLUMN IF NOT EXISTS location_id BIGINT;
|
|
|
|
-- Prefer PO shipping_id; fall back to existing location_id for any orphan rows
|
|
UPDATE grn g
|
|
SET location_id = po.shipping_id
|
|
FROM purchase_orders po
|
|
WHERE po.id = g.po_id
|
|
AND po.shipping_id IS NOT NULL;
|
|
|
|
ALTER TABLE grn ALTER COLUMN location_id SET NOT NULL;
|
|
|
|
ALTER TABLE grn DROP CONSTRAINT IF EXISTS grn_warehouse_id_fkey;
|
|
ALTER TABLE grn DROP CONSTRAINT IF EXISTS grn_location_id_fkey;
|
|
ALTER TABLE grn
|
|
ADD CONSTRAINT grn_location_id_fkey FOREIGN KEY (location_id) REFERENCES locations(id);
|
|
|
|
DROP INDEX IF EXISTS idx_grn_warehouse_id;
|
|
CREATE INDEX IF NOT EXISTS idx_grn_location_id ON grn(location_id);
|
|
|
|
CREATE VIEW v_grn AS
|
|
SELECT
|
|
g.*,
|
|
po.po_number,
|
|
v.vendor_code,
|
|
v.vendor_name,
|
|
loc.code AS location_code,
|
|
loc.name AS location_name,
|
|
loc.type AS location_type
|
|
FROM grn g
|
|
JOIN purchase_orders po ON po.id = g.po_id
|
|
JOIN vendors v ON v.id = g.vendor_id
|
|
JOIN locations loc ON loc.id = g.location_id
|
|
WHERE g.deleted_at IS NULL;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 2. Vendor bank: one active primary per vendor
|
|
-- ---------------------------------------------------------------------------
|
|
-- Demote duplicates (keep lowest id as primary)
|
|
WITH ranked AS (
|
|
SELECT
|
|
id,
|
|
ROW_NUMBER() OVER (PARTITION BY vendor_id ORDER BY id ASC) AS rn
|
|
FROM vendor_bank_details
|
|
WHERE is_primary = TRUE AND is_active = TRUE
|
|
)
|
|
UPDATE vendor_bank_details vbd
|
|
SET is_primary = FALSE
|
|
FROM ranked r
|
|
WHERE vbd.id = r.id
|
|
AND r.rn > 1;
|
|
|
|
DROP INDEX IF EXISTS uq_vendor_bank_one_primary;
|
|
CREATE UNIQUE INDEX uq_vendor_bank_one_primary
|
|
ON vendor_bank_details (vendor_id)
|
|
WHERE is_primary = TRUE AND is_active = TRUE;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 3. Drop visit_number from asset_service_visits
|
|
-- ---------------------------------------------------------------------------
|
|
DROP VIEW IF EXISTS v_asset_next_service CASCADE;
|
|
|
|
ALTER TABLE asset_service_visits DROP COLUMN IF EXISTS visit_number;
|
|
|
|
CREATE VIEW v_asset_next_service AS
|
|
SELECT
|
|
a.id AS asset_id,
|
|
a.asset_code,
|
|
a.asset_name,
|
|
ac_cat.name AS category_name,
|
|
loc.name AS location_name,
|
|
d.name AS department_name,
|
|
sv.id AS last_visit_id,
|
|
sv.visit_date AS last_service_date,
|
|
sv.visit_type AS last_visit_type,
|
|
sv.next_service_date,
|
|
(sv.next_service_date - CURRENT_DATE) AS days_to_next_service,
|
|
CASE
|
|
WHEN sv.next_service_date < CURRENT_DATE THEN 'OVERDUE'
|
|
WHEN (sv.next_service_date - CURRENT_DATE) <= 7 THEN 'DUE_THIS_WEEK'
|
|
WHEN (sv.next_service_date - CURRENT_DATE) <= 30 THEN 'DUE_THIS_MONTH'
|
|
ELSE 'UPCOMING'
|
|
END AS service_status,
|
|
v.vendor_name AS service_vendor,
|
|
amc.contract_no AS amc_contract_no,
|
|
amc.end_date AS amc_end_date
|
|
FROM assets a
|
|
JOIN item_categories ac_cat ON ac_cat.id = a.item_category_id
|
|
JOIN locations loc ON loc.id = a.location_id
|
|
LEFT JOIN departments d ON d.id = a.department_id
|
|
JOIN LATERAL (
|
|
SELECT id, visit_date, visit_type, next_service_date, vendor_id
|
|
FROM asset_service_visits
|
|
WHERE asset_id = a.id AND deleted_at IS NULL AND next_service_date IS NOT NULL
|
|
ORDER BY visit_date DESC
|
|
LIMIT 1
|
|
) sv ON TRUE
|
|
LEFT JOIN vendors v ON v.id = sv.vendor_id
|
|
LEFT JOIN asset_amc_contracts amc
|
|
ON amc.asset_id = a.id AND amc.is_active = TRUE AND amc.deleted_at IS NULL
|
|
WHERE a.deleted_at IS NULL AND a.status NOT IN ('DISPOSED','SCRAPPED');
|
|
|
|
COMMIT;
|