1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
DROP VIEW "public"."vendor_investigations_view";--> statement-breakpoint
CREATE VIEW "public"."vendor_investigations_view" AS (select "vendor_investigations"."id", "vendor_investigations"."status", "vendor_investigations"."scheduled_start_at", "vendor_investigations"."scheduled_end_at", "vendor_investigations"."completed_at", "vendor_investigations"."investigation_notes", "vendor_investigations"."created_at", "vendor_investigations"."updated_at", "vendor_investigations"."vendor_id", "vendors"."vendor_name", "vendors"."vendor_code", "vendors"."tax_id", "vendors"."status", "vendors"."country", "vendors"."email", "vendors"."phone", "vendors"."website", "vendors"."created_at", "vendors"."updated_at", (
SELECT COALESCE(
json_agg(json_build_object(
'contactName', c.contact_name,
'contactEmail', c.contact_email,
'contactPhone', c.contact_phone,
'contactPosition', c.contact_position,
'isPrimary', c.is_primary,
'contactCreatedAt', c.created_at,
'contactUpdatedAt', c.updated_at
)),
'[]'::json
)
FROM vendor_contacts c
WHERE c.vendor_id = "vendors"."id"
) as "contacts", (
SELECT COALESCE(
json_agg(json_build_object(
'itemCode', pi.item_code,
'itemName', i.item_name,
'itemCreatedAt', pi.created_at,
'itemUpdatedAt', pi.updated_at
)),
'[]'::json
)
FROM vendor_possible_items pi
LEFT JOIN items i ON pi.item_code = i.item_code
WHERE pi.vendor_id = "vendors"."id"
) as "possibleItems" from "vendor_investigations" left join "vendors" on "vendor_investigations"."vendor_id" = "vendors"."id");
|
