(김준회) 결재 테스트 모듈 수정, 환경병수 eVCP 운영 대응, SGIPS JWT TOKEN 수정, SHI-API 기반 유저 관리 추가, 유저목록 테이블 변경

3 files changed, 124 insertions, 1 deletions

diff --git a/db/schema/NONSAP/nonsap-user.ts b/db/schema/NONSAP/nonsap-user.ts
new file mode 100644
index 00000000..c18244ba
--- /dev/null
+++ b/db/schema/NONSAP/nonsap-user.ts
@@ -0,0 +1,101 @@
+import { nonsapSchema } from './nonsap';
+import { varchar } from 'drizzle-orm/pg-core';
+
+export const nonsapUser = nonsapSchema.table('nonsap_user', {
+  // "USR_ID": "string",
+  USR_ID: varchar({ length: 255 }).primaryKey(),
+  // "USR_NM": "string",
+  USR_NM: varchar({ length: 255 }),
+  // "USR_ENM": "string",
+  USR_ENM: varchar({ length: 255 }),
+  // "EMPNO": "string",
+  EMPNO: varchar({ length: 255 }),
+  // "CO_CD": "string",
+  CO_CD: varchar({ length: 255 }),
+  // "CO_NM": "string",
+  CO_NM: varchar({ length: 255 }),
+  // "DEPTCD": "string",
+  DEPTCD: varchar({ length: 255 }),
+  // "DEPTNM": "string",
+  DEPTNM: varchar({ length: 255 }),
+  // "MAST_DEPTCD": "string",
+  MAST_DEPTCD: varchar({ length: 255 }),
+  // "MAST_DEPTNM": "string",
+  MAST_DEPTNM: varchar({ length: 255 }),
+  // "VNDRCD": "string",
+  VNDRCD: varchar({ length: 255 }),
+  // "VNDRNM": "string",
+  VNDRNM: varchar({ length: 255 }),
+  // "REGL_ORORD_GB": "string",
+  REGL_ORORD_GB: varchar({ length: 255 }),
+  // "JG_CD": "string",
+  JG_CD: varchar({ length: 255 }),
+  // "JG_NM": "string",
+  JG_NM: varchar({ length: 255 }),
+  // "JK_CD": "string",
+  JK_CD: varchar({ length: 255 }),
+  // "JK_NM": "string",
+  JK_NM: varchar({ length: 255 }),
+  // "EMAIL_ADR": "string",
+  EMAIL_ADR: varchar({ length: 255 }),
+  // "TELNO": "string",
+  TELNO: varchar({ length: 255 }),
+  // "HP_NO": "string",
+  HP_NO: varchar({ length: 255 }),
+  // "ADR": "string",
+  ADR: varchar({ length: 255 }),
+  // "MYSNG_ID": "string",
+  MYSNG_ID: varchar({ length: 255 }),
+  // "MYSNG_USR_ID": "string", // 별도 고유값
+  MYSNG_USR_ID: varchar({ length: 255 }),
+  // "MYSNG_USE_YN": "string",
+  MYSNG_USE_YN: varchar({ length: 255 }),
+  // "CHRG_BIZ_NM": "string",
+  CHRG_BIZ_NM: varchar({ length: 255 }),
+  // "FIN_PWD_CHG_DTM": "string",
+  FIN_PWD_CHG_DTM: varchar({ length: 255 }),
+  // "FIN_LGN_DTM": "string",
+  FIN_LGN_DTM: varchar({ length: 255 }),
+  // "FIN_LOGOUT_DTM": "string",
+  FIN_LOGOUT_DTM: varchar({ length: 255 }),
+  // "FIN_LGN_FAIL_TMS": "string",
+  FIN_LGN_FAIL_TMS: varchar({ length: 255 }),
+  // "FIN_USEIP": "string",
+  FIN_USEIP: varchar({ length: 255 }),
+  // "UNLOCK_DTM": "string",
+  UNLOCK_DTM: varchar({ length: 255 }),
+  // "LOCK_YN": "string",
+  LOCK_YN: varchar({ length: 255 }),
+  // "AGR_YN": "string",
+  AGR_YN: varchar({ length: 255 }),
+  // "DEL_YN": "string",
+  DEL_YN: varchar({ length: 255 }),
+  // "BIZLOC_GB_CD": "string",
+  BIZLOC_GB_CD: varchar({ length: 255 }),
+  // "BIZLOC_GB_NM": "string",
+  BIZLOC_GB_NM: varchar({ length: 255 }),
+  // "GRD_NM": "string",
+  GRD_NM: varchar({ length: 255 }),
+  // "CH_DEPTCD": "string",
+  CH_DEPTCD: varchar({ length: 255 }),
+  // "CH_DEPTNM": "string",
+  CH_DEPTNM: varchar({ length: 255 }),
+  // "ORG_OTHER_NAME": "string",
+  ORG_OTHER_NAME: varchar({ length: 255 }),
+  // "GRADE_OTHER_NAME": "string",
+  GRADE_OTHER_NAME: varchar({ length: 255 }),
+  // "FAX_NO": "string",
+  FAX_NO: varchar({ length: 255 }),
+  // "FS_INPR_ID": "string",
+  FS_INPR_ID: varchar({ length: 255 }),
+  // "FS_INP_DTM": "string",
+  FS_INP_DTM: varchar({ length: 255 }),
+  // "FIN_CHGR_ID": "string",
+  FIN_CHGR_ID: varchar({ length: 255 }),
+  // "FIN_CHG_DTM": "string",
+  FIN_CHG_DTM: varchar({ length: 255 }),
+  // "LOFF_GB": "string",
+  LOFF_GB: varchar({ length: 255 }),
+  // "DEL_DTM": "string",
+  DEL_DTM: varchar({ length: 255 }),
+});
diff --git a/db/schema/index.ts b/db/schema/index.ts
index 5b712b40..9cd71197 100644
--- a/db/schema/index.ts
+++ b/db/schema/index.ts
@@ -47,6 +47,7 @@ export * from './SOAP/soap';
 
 // NONSAP Oracle DB 스키마
 export * from './NONSAP/nonsap';
+export * from './NONSAP/nonsap-user'; // 김희은 프로 요청사항(모든 유저 데이터는 shi-api를 통한 nonspa 기준으로 처리)
 
 // ECC SOAP 수신용 (RFQ, PO, PR 데이터)
 export * from './ECC/ecc';
diff --git a/db/schema/users.ts b/db/schema/users.ts
index 0d727bb4..9977a442 100644
--- a/db/schema/users.ts
+++ b/db/schema/users.ts
@@ -59,6 +59,14 @@ export const users = pgTable("users", {
   //  emailVerifiedAt: timestamp("email_verified_at", { withTimezone: true }),
   //  registrationCompleted: boolean("registration_completed").default(false).notNull(),
 
+  // 김희은 프로 요구사항으로 추가
+  employeeNumber: varchar("employee_number", { length: 50 }),
+  knoxId: varchar("knox_id", { length: 50 }),
+  nonsapUserId: varchar("nonsap_user_id", { length: 50 }).unique(),
+  isAbsent: boolean("is_absent"), // 휴직여부 (SHI-API LOFF_GB (Y/N))
+  isDeletedOnNonSap: boolean("is_deleted_on_non_sap"), // 퇴직여부 (SHI-API DEL_YN (Y/N))
+  isRegularEmployee: boolean("is_regular_employee"), // 정직원여부 (SHI-API REGL_ORORD_GB (S/N))
+
 }, (table) => {
   return {
     emailIdx: uniqueIndex("users_email_idx").on(table.email),
@@ -286,6 +294,17 @@ export const userView = pgView("user_view").as((qb) => {
 
       user_image: sql<string>`${users.imageUrl}`.as("user_image"),
 
+      // 추가: 사번, 부서, 녹스ID
+      employee_number: sql<string | null>`${users.employeeNumber}`.as("employee_number"),
+      dept_name: sql<string | null>`${users.deptName}`.as("dept_name"),
+      knox_id: sql<string | null>`${users.knoxId}`.as("knox_id"),
+
+      // 추가: 계정 상태 플래그
+      is_locked: sql<boolean>`${users.isLocked}`.as("is_locked"),
+      is_absent: sql<boolean | null>`${users.isAbsent}`.as("is_absent"),
+      is_deleted_on_non_sap: sql<boolean | null>`${users.isDeletedOnNonSap}`.as("is_deleted_on_non_sap"),
+      is_regular_employee: sql<boolean | null>`${users.isRegularEmployee}`.as("is_regular_employee"),
+
       // 4) companyId: number | null
       company_id: sql<number | null>`${vendors.id}`.as("company_id"),
 
@@ -297,8 +316,10 @@ export const userView = pgView("user_view").as((qb) => {
       roles: sql<string[]>`
       array_agg(${roles.name})
     `.as("roles"),
-      // 7) createdAt: Date
+      // 7) created/updated/deactivated dates
       created_at: sql<Date>`${users.createdAt}`.as("created_at"),
+      updated_at: sql<Date>`${users.updatedAt}`.as("updated_at"),
+      deactivated_at: sql<Date | null>`${users.deactivatedAt}`.as("deactivated_at"),
     })
     .from(users)
     .leftJoin(vendors, eq(users.companyId, vendors.id))