207 lines
7.3 KiB
PL/PgSQL
207 lines
7.3 KiB
PL/PgSQL
-- Assets: replace plant_id + warehouse_id with a single location_id (plant OR
|
|
-- warehouse). For GRN auto-created assets, location_id comes from the PO
|
|
-- shipping_id. Recreates the asset views to use location_id.
|
|
-- Idempotent: safe to re-run.
|
|
|
|
BEGIN;
|
|
|
|
-- 1. New unified location column
|
|
ALTER TABLE assets ADD COLUMN IF NOT EXISTS location_id BIGINT;
|
|
|
|
-- 2. Backfill from old columns (prefer warehouse, else plant) when present
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'assets' AND column_name = 'plant_id'
|
|
) THEN
|
|
UPDATE assets
|
|
SET location_id = COALESCE(warehouse_id, plant_id)
|
|
WHERE location_id IS NULL;
|
|
END IF;
|
|
END $$;
|
|
|
|
ALTER TABLE assets ALTER COLUMN location_id SET NOT NULL;
|
|
|
|
-- 3. Drop dependent views before removing old columns
|
|
DROP VIEW IF EXISTS v_assets CASCADE;
|
|
DROP VIEW IF EXISTS v_asset_expiry_alerts CASCADE;
|
|
DROP VIEW IF EXISTS v_asset_next_service CASCADE;
|
|
|
|
-- 4. Drop old columns (FK constraints/indexes drop with them)
|
|
DROP INDEX IF EXISTS idx_assets_plant_id;
|
|
ALTER TABLE assets DROP COLUMN IF EXISTS plant_id;
|
|
ALTER TABLE assets DROP COLUMN IF EXISTS warehouse_id;
|
|
|
|
-- 5. FK + index for the new column
|
|
ALTER TABLE assets DROP CONSTRAINT IF EXISTS fk_assets_location;
|
|
ALTER TABLE assets
|
|
ADD CONSTRAINT fk_assets_location FOREIGN KEY (location_id) REFERENCES locations(id);
|
|
CREATE INDEX IF NOT EXISTS idx_assets_location_id ON assets(location_id);
|
|
|
|
-- 6. Recreate views using location_id (join locations regardless of type)
|
|
CREATE VIEW v_asset_expiry_alerts 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,
|
|
'AMC'::text AS alert_type,
|
|
amc.id AS reference_id,
|
|
amc.contract_no AS reference_no,
|
|
v.vendor_name AS party_name,
|
|
amc.end_date AS expiry_date,
|
|
(amc.end_date - CURRENT_DATE) AS days_remaining,
|
|
CASE
|
|
WHEN (amc.end_date - CURRENT_DATE) <= 0 THEN 'EXPIRED'
|
|
WHEN (amc.end_date - CURRENT_DATE) <= 30 THEN 'CRITICAL'
|
|
WHEN (amc.end_date - CURRENT_DATE) <= 60 THEN 'WARNING'
|
|
WHEN (amc.end_date - CURRENT_DATE) <= 90 THEN 'INFO'
|
|
END AS alert_level
|
|
FROM asset_amc_contracts amc
|
|
JOIN assets a ON a.id = amc.asset_id
|
|
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
|
|
LEFT JOIN vendors v ON v.id = amc.vendor_id
|
|
WHERE amc.is_active = TRUE AND amc.deleted_at IS NULL AND a.deleted_at IS NULL
|
|
AND (amc.end_date - CURRENT_DATE) <= 90
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
a.id, a.asset_code, a.asset_name, ac_cat.name, loc.name, d.name,
|
|
'INSURANCE'::text, ins.id, ins.policy_no, ins.insurer_name,
|
|
ins.policy_end_date, (ins.policy_end_date - CURRENT_DATE),
|
|
CASE
|
|
WHEN (ins.policy_end_date - CURRENT_DATE) <= 0 THEN 'EXPIRED'
|
|
WHEN (ins.policy_end_date - CURRENT_DATE) <= 30 THEN 'CRITICAL'
|
|
WHEN (ins.policy_end_date - CURRENT_DATE) <= 60 THEN 'WARNING'
|
|
WHEN (ins.policy_end_date - CURRENT_DATE) <= 90 THEN 'INFO'
|
|
END
|
|
FROM asset_insurance_policies ins
|
|
JOIN assets a ON a.id = ins.asset_id
|
|
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
|
|
WHERE ins.is_active = TRUE AND ins.deleted_at IS NULL AND a.deleted_at IS NULL
|
|
AND (ins.policy_end_date - CURRENT_DATE) <= 90
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
a.id, a.asset_code, a.asset_name, ac_cat.name, loc.name, d.name,
|
|
'WARRANTY'::text, a.id, a.asset_code, 'Manufacturer Warranty'::varchar,
|
|
a.warranty_expiry_date, (a.warranty_expiry_date - CURRENT_DATE),
|
|
CASE
|
|
WHEN (a.warranty_expiry_date - CURRENT_DATE) <= 0 THEN 'EXPIRED'
|
|
WHEN (a.warranty_expiry_date - CURRENT_DATE) <= 30 THEN 'CRITICAL'
|
|
WHEN (a.warranty_expiry_date - CURRENT_DATE) <= 60 THEN 'WARNING'
|
|
WHEN (a.warranty_expiry_date - CURRENT_DATE) <= 90 THEN 'INFO'
|
|
END
|
|
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
|
|
WHERE a.warranty_expiry_date IS NOT NULL AND a.deleted_at IS NULL
|
|
AND (a.warranty_expiry_date - CURRENT_DATE) <= 90;
|
|
|
|
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 * 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');
|
|
|
|
CREATE VIEW v_assets AS
|
|
SELECT
|
|
a.id,
|
|
a.asset_code,
|
|
a.asset_name,
|
|
ac.name AS category_name,
|
|
a.brand_model,
|
|
a.serial_number,
|
|
a.status,
|
|
a.condition,
|
|
a.purchase_date,
|
|
a.purchase_cost,
|
|
a.warranty_expiry_date,
|
|
loc.name AS location_name,
|
|
loc.type AS location_type,
|
|
d.name AS department_name,
|
|
a.location_detail,
|
|
u.full_name AS assigned_to,
|
|
v.vendor_name AS supplier,
|
|
a.qr_code_value,
|
|
amc.contract_no AS amc_contract_no,
|
|
amc.end_date AS amc_expiry_date,
|
|
amc.contract_type AS amc_type,
|
|
amcv.vendor_name AS amc_vendor,
|
|
(amc.end_date - CURRENT_DATE) AS amc_days_remaining,
|
|
ins.policy_no AS insurance_policy_no,
|
|
ins.policy_end_date AS insurance_expiry_date,
|
|
ins.insurer_name,
|
|
ins.sum_insured,
|
|
(ins.policy_end_date - CURRENT_DATE) AS insurance_days_remaining,
|
|
lsv.visit_date AS last_service_date,
|
|
lsv.next_service_date,
|
|
(lsv.next_service_date - CURRENT_DATE) AS service_due_in_days,
|
|
a.created_at
|
|
FROM assets a
|
|
JOIN item_categories ac ON ac.id = a.item_category_id
|
|
JOIN locations loc ON loc.id = a.location_id
|
|
LEFT JOIN departments d ON d.id = a.department_id
|
|
LEFT JOIN users u ON u.id = a.assigned_to_user_id
|
|
LEFT JOIN vendors v ON v.id = a.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
|
|
LEFT JOIN vendors amcv ON amcv.id = amc.vendor_id
|
|
LEFT JOIN asset_insurance_policies ins
|
|
ON ins.asset_id = a.id AND ins.is_active = TRUE AND ins.deleted_at IS NULL
|
|
LEFT JOIN LATERAL (
|
|
SELECT visit_date, next_service_date
|
|
FROM asset_service_visits
|
|
WHERE asset_id = a.id AND deleted_at IS NULL
|
|
ORDER BY visit_date DESC LIMIT 1
|
|
) lsv ON TRUE
|
|
WHERE a.deleted_at IS NULL;
|
|
|
|
COMMIT;
|
|
|
|
-- Verify:
|
|
-- SELECT column_name FROM information_schema.columns
|
|
-- WHERE table_name = 'assets'
|
|
-- AND column_name IN ('location_id','plant_id','warehouse_id');
|