(대표님) 20250627 오전 10시 작업사항

1 files changed, 172 insertions, 0 deletions

diff --git a/lib/vendor-users/repository.ts b/lib/vendor-users/repository.ts
new file mode 100644
index 00000000..3719a3bf
--- /dev/null
+++ b/lib/vendor-users/repository.ts
@@ -0,0 +1,172 @@
+import db from "@/db/db"; 
+import { users, userRoles,userView,roles, type User, type UserRole, type UserView, Role  } from "@/db/schema/users";
+import { companies, type Company } from "@/db/schema/companies";
+import {
+  eq,
+  inArray,
+  asc,
+  desc,
+  and,
+  count,
+  gt,
+  sql,
+  SQL,
+} from "drizzle-orm";
+import { PgTransaction } from "drizzle-orm/pg-core";
+import { Vendor, vendors } from "@/db/schema/vendors";
+
+// ============================================================
+// 타입
+// ============================================================
+
+export type NewUser = typeof users.$inferInsert;       // User insert 시 필요한 타입
+export type NewUserRole = typeof userRoles.$inferInsert; // UserRole insert 시 필요한 타입
+export type NewCompany = typeof companies.$inferInsert;   // Company insert 시 필요한 타입
+
+
+
+export async function selectUsersWithCompanyAndRoles(
+  tx: PgTransaction<any, any, any>,
+  params: {
+    where?: any
+    orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc>)[]
+    offset?: number
+    limit?: number
+  }
+) {
+  const { where, orderBy, offset = 0, limit = 10 } = params
+
+  // 1) 쿼리 빌더 생성
+  const queryBuilder = tx
+    .select()
+    .from(userView)
+    .where(where)
+    .orderBy(...(orderBy ?? []))
+    .offset(offset)
+    .limit(limit)
+
+  const rows = await queryBuilder
+  return rows
+}
+
+
+/** 총 개수 count */
+export async function countUsers(
+  tx: PgTransaction<any, any, any>,
+  where?: any
+) {
+  const res = await tx.select({ count: count() }).from(userView).where(where);
+  return res[0]?.count ?? 0;
+}
+
+export async function groupByCompany(
+  tx: PgTransaction<any, any, any>,
+) {
+  return tx
+    .select({
+      companyId: users.companyId,
+      count: count(),
+    })
+    .from(users)
+    .groupBy(users.companyId)
+    .having(gt(count(), 0));
+}
+
+export async function groupByRole(tx: PgTransaction<any, any, any>, companyId: number) {
+  return tx
+    .select({
+      roleId: userRoles.roleId, 
+      count: sql<number>`COUNT(*)`.as("count"),
+    })
+    .from(users)
+    .where(eq(users.companyId, companyId))
+    .leftJoin(userRoles, eq(userRoles.userId, users.id))
+    .leftJoin(roles, eq(roles.id, userRoles.roleId))
+    .groupBy(userRoles.roleId, roles.id, roles.name)
+    .having(gt(sql<number>`COUNT(*)` /* 또는 count()와 동일 */, 0));
+}
+
+export async function insertUser(
+  tx: PgTransaction<any, any, any>,
+  data: NewUser
+) {
+  return tx.insert(users).values(data).returning();
+}
+
+export async function insertUserRole(
+  tx: PgTransaction<any, any, any>,
+  data: NewUserRole
+) {
+  return tx.insert(userRoles).values(data).returning();
+}
+
+export async function updateUser(
+  tx: PgTransaction<any, any, any>,
+  userId: number,
+  data: Partial<User>
+) {
+  return tx
+    .update(users)
+    .set(data)
+    .where(eq(users.id, userId))
+    .returning();
+}
+
+/** 복수 업데이트 */
+export async function updateUsers(
+  tx: PgTransaction<any, any, any>,
+ids: number[],
+data: Partial<User>
+) {
+return tx
+  .update(users)
+  .set(data)
+  .where(inArray(users.id, ids))
+  .returning({ companyId: users.companyId });
+}
+
+export async function deleteRolesByUserId(
+  tx: PgTransaction<any, any, any>,
+  userId: number
+) {
+  return tx.delete(userRoles).where(eq(userRoles.userId, userId));
+}
+
+
+export async function deleteRolesByUserIds(
+  tx: PgTransaction<any, any, any>,
+  ids: number[]
+) {
+  return tx.delete(userRoles).where(inArray(userRoles.userId, ids));
+}
+
+export async function deleteUserById(
+  tx: PgTransaction<any, any, any>,
+  userId: number
+) {
+  return tx.delete(users).where(eq(users.id, userId));
+}
+
+
+export async function deleteUsersByIds(
+  tx: PgTransaction<any, any, any>,
+  ids: number[]
+) {
+  return tx.delete(users).where(inArray(users.id, ids));
+}
+
+export async function findAllCompanies(): Promise<Vendor[]> {
+  return db.select().from(vendors).orderBy(asc(vendors.vendorName));
+}
+
+export async function findAllRoles(companyId:number): Promise<Role[]> {
+  return db.select().from(roles).where(and(eq(roles.domain ,'partners'),eq(roles.companyId, companyId))).orderBy(asc(roles.name));
+}
+
+export const getUserById = async (id: number): Promise<UserView | null> => {
+  const userFouned = await db.select().from(userView).where(eq(userView.user_id, id)).execute();
+  if (userFouned.length === 0) return null;
+
+  const user = userFouned[0];
+  return user
+};