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
|
CREATE TABLE "nonsap"."nonsap_user" (
"USR_ID" varchar(255) PRIMARY KEY NOT NULL,
"USR_NM" varchar(255),
"USR_ENM" varchar(255),
"EMPNO" varchar(255),
"CO_CD" varchar(255),
"CO_NM" varchar(255),
"DEPTCD" varchar(255),
"DEPTNM" varchar(255),
"MAST_DEPTCD" varchar(255),
"MAST_DEPTNM" varchar(255),
"VNDRCD" varchar(255),
"VNDRNM" varchar(255),
"REGL_ORORD_GB" varchar(255),
"JG_CD" varchar(255),
"JG_NM" varchar(255),
"JK_CD" varchar(255),
"JK_NM" varchar(255),
"EMAIL_ADR" varchar(255),
"TELNO" varchar(255),
"HP_NO" varchar(255),
"ADR" varchar(255),
"MYSNG_ID" varchar(255),
"MYSNG_USR_ID" varchar(255),
"MYSNG_USE_YN" varchar(255),
"CHRG_BIZ_NM" varchar(255),
"FIN_PWD_CHG_DTM" varchar(255),
"FIN_LGN_DTM" varchar(255),
"FIN_LOGOUT_DTM" varchar(255),
"FIN_LGN_FAIL_TMS" varchar(255),
"FIN_USEIP" varchar(255),
"UNLOCK_DTM" varchar(255),
"LOCK_YN" varchar(255),
"AGR_YN" varchar(255),
"DEL_YN" varchar(255),
"BIZLOC_GB_CD" varchar(255),
"BIZLOC_GB_NM" varchar(255),
"GRD_NM" varchar(255),
"CH_DEPTCD" varchar(255),
"CH_DEPTNM" varchar(255),
"ORG_OTHER_NAME" varchar(255),
"GRADE_OTHER_NAME" varchar(255),
"FAX_NO" varchar(255),
"FS_INPR_ID" varchar(255),
"FS_INP_DTM" varchar(255),
"FIN_CHGR_ID" varchar(255),
"FIN_CHG_DTM" varchar(255),
"LOFF_GB" varchar(255),
"DEL_DTM" varchar(255)
);
--> statement-breakpoint
DROP VIEW "public"."user_view";--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "employee_number" varchar(50);--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "knox_id" varchar(50);--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "nonsap_user_id" varchar(50);--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "is_absent" boolean;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "is_deleted_on_non_sap" boolean;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "is_regular_employee" boolean;--> statement-breakpoint
ALTER TABLE "users" ADD CONSTRAINT "users_nonsap_user_id_unique" UNIQUE("nonsap_user_id");--> statement-breakpoint
CREATE VIEW "public"."user_view" AS (select "users"."id" as "user_id", "users"."name" as "user_name", "users"."phone" as "user_phone", "users"."email" as "user_email", "users"."domain" as "user_domain", "users"."image_url" as "user_image", "users"."employee_number" as "employee_number", "users"."deptName" as "dept_name", "users"."knox_id" as "knox_id", "users"."is_locked" as "is_locked", "users"."is_absent" as "is_absent", "users"."is_deleted_on_non_sap" as "is_deleted_on_non_sap", "users"."is_regular_employee" as "is_regular_employee", "vendors"."id" as "company_id", "vendors"."vendor_name" as "company_name",
array_agg("roles"."name")
as "roles", "users"."created_at" as "created_at", "users"."updated_at" as "updated_at", "users"."deactivated_at" as "deactivated_at" from "users" left join "vendors" on "users"."company_id" = "vendors"."id" left join "user_roles" on "users"."id" = "user_roles"."user_id" left join "roles" on "user_roles"."role_id" = "roles"."id" group by "users"."id", "vendors"."id");
|
