diff options
Diffstat (limited to 'db/migrations/0377_same_sentry.sql')
| -rw-r--r-- | db/migrations/0377_same_sentry.sql | 150 |
1 files changed, 150 insertions, 0 deletions
diff --git a/db/migrations/0377_same_sentry.sql b/db/migrations/0377_same_sentry.sql new file mode 100644 index 00000000..4ec10811 --- /dev/null +++ b/db/migrations/0377_same_sentry.sql @@ -0,0 +1,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 + );
\ No newline at end of file |