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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
|
CREATE TABLE "purchase_request_attachments" (
"id" serial PRIMARY KEY NOT NULL,
"request_id" integer NOT NULL,
"file_name" varchar(255) NOT NULL,
"original_file_name" varchar(255) NOT NULL,
"file_path" varchar(512) NOT NULL,
"file_size" integer,
"file_type" varchar(100),
"category" varchar(50) DEFAULT '설계문서' NOT NULL,
"description" varchar(500),
"created_by" integer NOT NULL,
"created_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "purchase_requests" (
"id" serial PRIMARY KEY NOT NULL,
"request_code" varchar(50) NOT NULL,
"project_id" integer,
"project_code" varchar(100),
"project_name" varchar(255),
"project_company" varchar(255),
"project_site" varchar(255),
"class_no" varchar(50),
"package_no" varchar(50),
"package_name" varchar(255),
"major_item_material_category" varchar(100),
"major_item_material_description" varchar(255),
"sm_code" varchar(255),
"request_title" varchar(255) NOT NULL,
"request_description" text,
"estimated_budget" varchar(100),
"requested_delivery_date" timestamp with time zone,
"items" jsonb DEFAULT '[]'::jsonb,
"status" varchar(30) DEFAULT '작성중' NOT NULL,
"reject_reason" text,
"confirmed_at" timestamp with time zone,
"confirmed_by" integer,
"rfq_id" integer,
"rfq_code" varchar(50),
"rfq_created_at" timestamp with time zone,
"eng_pic_id" integer,
"eng_pic_name" varchar(50),
"purchase_pic_id" integer,
"purchase_pic_name" varchar(50),
"created_by" integer NOT NULL,
"updated_by" integer NOT NULL,
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL,
CONSTRAINT "purchase_requests_request_code_unique" UNIQUE("request_code")
);
--> statement-breakpoint
ALTER TABLE "purchase_request_attachments" ADD CONSTRAINT "purchase_request_attachments_request_id_purchase_requests_id_fk" FOREIGN KEY ("request_id") REFERENCES "public"."purchase_requests"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "purchase_request_attachments" ADD CONSTRAINT "purchase_request_attachments_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "purchase_requests" ADD CONSTRAINT "purchase_requests_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "purchase_requests" ADD CONSTRAINT "purchase_requests_confirmed_by_users_id_fk" FOREIGN KEY ("confirmed_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "purchase_requests" ADD CONSTRAINT "purchase_requests_eng_pic_id_users_id_fk" FOREIGN KEY ("eng_pic_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "purchase_requests" ADD CONSTRAINT "purchase_requests_purchase_pic_id_users_id_fk" FOREIGN KEY ("purchase_pic_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "purchase_requests" ADD CONSTRAINT "purchase_requests_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "purchase_requests" ADD CONSTRAINT "purchase_requests_updated_by_users_id_fk" FOREIGN KEY ("updated_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE UNIQUE INDEX "request_file_idx" ON "purchase_request_attachments" USING btree ("request_id","file_name");--> statement-breakpoint
CREATE VIEW "public"."purchase_requests_view" AS (
SELECT
pr.id,
pr.request_code,
pr.request_title,
pr.request_description,
-- 프로젝트 정보
pr.project_id,
pr.project_code,
pr.project_name,
pr.project_company,
pr.project_site,
pr.class_no,
-- 패키지 정보
pr.package_no,
pr.package_name,
-- 자재 정보
pr.major_item_material_category,
pr.major_item_material_description,
pr.sm_code,
-- 예산 및 납기
pr.estimated_budget,
pr.requested_delivery_date,
-- 아이템 정보
pr.items,
jsonb_array_length(pr.items) as item_count,
(
SELECT SUM((item->>'quantity')::numeric)
FROM jsonb_array_elements(pr.items) as item
) as total_quantity,
(
SELECT SUM(
(item->>'quantity')::numeric *
COALESCE((item->>'estimatedUnitPrice')::numeric, 0)
)
FROM jsonb_array_elements(pr.items) as item
) as total_estimated_amount,
-- 상태 정보
pr.status,
pr.reject_reason,
-- 확정 정보
pr.confirmed_at,
pr.confirmed_by,
cb.name as confirmed_by_name,
-- RFQ 정보
pr.rfq_id,
pr.rfq_code,
pr.rfq_created_at,
-- 담당자 정보
pr.eng_pic_id,
pr.eng_pic_name,
ep.email as eng_pic_email,
pr.purchase_pic_id,
pr.purchase_pic_name,
pp.email as purchase_pic_email,
-- 첨부파일 수
(
SELECT COUNT(*)
FROM purchase_request_attachments pra
WHERE pra.request_id = pr.id
) as attachment_count,
-- 생성/수정 정보
pr.created_by,
cr.name as created_by_name,
cr.email as created_by_email,
pr.updated_by,
up.name as updated_by_name,
up.email as updated_by_email,
pr.created_at,
pr.updated_at
FROM purchase_requests pr
LEFT JOIN users cb ON cb.id = pr.confirmed_by
LEFT JOIN users ep ON ep.id = pr.eng_pic_id
LEFT JOIN users pp ON pp.id = pr.purchase_pic_id
LEFT JOIN users cr ON cr.id = pr.created_by
LEFT JOIN users up ON up.id = pr.updated_by
);
|
