summaryrefslogtreecommitdiff
path: root/lib/tasks/service.ts
diff options
context:
space:
mode:
Diffstat (limited to 'lib/tasks/service.ts')
-rw-r--r--lib/tasks/service.ts561
1 files changed, 561 insertions, 0 deletions
diff --git a/lib/tasks/service.ts b/lib/tasks/service.ts
new file mode 100644
index 00000000..c31ecd4b
--- /dev/null
+++ b/lib/tasks/service.ts
@@ -0,0 +1,561 @@
+// src/lib/tasks/service.ts
+"use server"; // Next.js 서버 액션에서 직접 import하려면 (선택)
+
+import { revalidateTag, unstable_noStore } from "next/cache";
+import db from "@/db/db";
+import { tasks, type Task } from "@/db/schema/tasks";
+import { customAlphabet } from "nanoid";
+
+import { filterColumns } from "@/lib/filter-columns";
+import { unstable_cache } from "@/lib/unstable-cache";
+import { getErrorMessage } from "@/lib/handle-error";
+
+import type { CreateTaskSchema, UpdateTaskSchema, GetTasksSchema } from "./validations";
+import { asc, desc, ilike, inArray, and, gte, lte, not, or } from "drizzle-orm";
+
+// 레포지토리 함수들
+import {
+ selectTasks,
+ countTasks,
+ insertTask,
+ insertTasks,
+ selectOldestTaskExcept,
+ deleteTaskById,
+ deleteTasksByIds,
+ deleteAllTasks,
+ updateTask,
+ updateTasks,
+ groupByStatus,
+ groupByPriority,
+ getAllTasks,
+} from "./repository";
+
+import ExcelJS from "exceljs"
+import { tasksColumnsConfig, type TaskColumnConfig } from "@/config/tasksColumnsConfig"
+
+interface ImportResult {
+ errorFile: File | null
+ errorMessage: string | null
+ successMessage?: string
+}
+/* -----------------------------------------------------
+ 1) 조회 관련
+----------------------------------------------------- */
+
+/**
+ * 복잡한 조건으로 Task 목록을 조회 (+ pagination) 하고,
+ * 총 개수에 따라 pageCount를 계산해서 리턴.
+ * Next.js의 unstable_cache를 사용해 일정 시간 캐시.
+ */
+export async function getTasks(input: GetTasksSchema) {
+
+ return unstable_cache(
+ async () => {
+ try {
+ const offset = (input.page - 1) * input.perPage;
+ const fromDate = input.from ? new Date(input.from) : undefined;
+ const toDate = input.to ? new Date(input.to) : undefined;
+ // const advancedTable = input.flags.includes("advancedTable");
+ const advancedTable = true;
+
+ // advancedTable 모드면 filterColumns()로 where 절 구성
+ const advancedWhere = filterColumns({
+ table: tasks,
+ filters: input.filters,
+ joinOperator: input.joinOperator,
+ });
+
+
+ let globalWhere
+ if (input.search) {
+ const s = `%${input.search}%`
+ globalWhere = or(ilike(tasks.title, s), ilike(tasks.code, s)
+ , ilike(tasks.status, s)
+ )
+ // 필요시 여러 칼럼 OR조건 (status, priority, etc)
+ }
+
+ const finalWhere = and(
+ // advancedWhere or your existing conditions
+ advancedWhere,
+ globalWhere // and()함수로 결합 or or() 등으로 결합
+ )
+
+
+ // 아니면 ilike, inArray, gte 등으로 where 절 구성
+ const where = advancedTable
+ ? finalWhere
+ : and(
+ input.title ? ilike(tasks.title, `%${input.title}%`) : undefined,
+ input.status.length > 0 ? inArray(tasks.status, input.status) : undefined,
+ input.priority.length > 0 ? inArray(tasks.priority, input.priority) : undefined,
+ fromDate ? gte(tasks.createdAt, fromDate) : undefined,
+ toDate ? lte(tasks.createdAt, toDate) : undefined
+ );
+
+ const orderBy =
+ input.sort.length > 0
+ ? input.sort.map((item) =>
+ item.desc ? desc(tasks[item.id]) : asc(tasks[item.id])
+ )
+ : [asc(tasks.createdAt)];
+
+ // 트랜잭션 내부에서 Repository 호출
+ const { data, total } = await db.transaction(async (tx) => {
+ const data = await selectTasks(tx, {
+ where,
+ orderBy,
+ offset,
+ limit: input.perPage,
+ });
+ const total = await countTasks(tx, where);
+ return { data, total };
+ });
+
+ const pageCount = Math.ceil(total / input.perPage);
+
+// console.log("===> advancedWhere:", advancedWhere);
+// console.log("===> globalWhere:", globalWhere);
+// console.log("===> finalWhere:", finalWhere);
+// console.log("===> offset:", offset, " limit:", input.perPage);
+
+ return { data, pageCount };
+ } catch (err) {
+ // 에러 발생 시 디폴트
+ return { data: [], pageCount: 0 };
+ }
+ },
+ [JSON.stringify(input)], // 캐싱 키
+ {
+ revalidate: 3600,
+ tags: ["tasks"], // revalidateTag("tasks") 호출 시 무효화
+ }
+ )();
+}
+
+
+/** Status별 개수 */
+export async function getTaskStatusCounts() {
+ return unstable_cache(
+ async () => {
+ try {
+
+ const initial: Record<Task["status"], number> = {
+ todo: 0,
+ "in-progress": 0,
+ done: 0,
+ canceled: 0,
+ };
+
+
+ const result = await db.transaction(async (tx) => {
+ const rows = await groupByStatus(tx);
+ return rows.reduce<Record<Task["status"], number>>((acc, { status, count }) => {
+ acc[status] = count;
+ return acc;
+ }, initial);
+ });
+
+ return result;
+ } catch (err) {
+ return {} as Record<Task["status"], number>;
+ }
+ },
+ ["task-status-counts"], // 캐싱 키
+ {
+ revalidate: 3600,
+ }
+ )();
+}
+
+/** Priority별 개수 */
+export async function getTaskPriorityCounts() {
+ return unstable_cache(
+ async () => {
+ try {
+
+ const initial: Record<Task["priority"], number> = {
+ low: 0,
+ medium: 0,
+ high: 0,
+ };
+
+ const result = await db.transaction(async (tx) => {
+ const rows = await groupByPriority(tx);
+ return rows.reduce<Record<Task["priority"], number>>((acc, { priority, count }) => {
+ acc[priority] = count;
+ return acc;
+ }, initial);
+ });
+
+ return result;
+ } catch (err) {
+ return {} as Record<Task["priority"], number>;
+ }
+ },
+ ["task-priority-counts"],
+ {
+ revalidate: 3600,
+ }
+ )();
+}
+
+/* -----------------------------------------------------
+ 2) 생성(Create)
+----------------------------------------------------- */
+
+
+/**
+ * Task 생성 후, (가장 오래된 Task 1개) 삭제로
+ * 전체 Task 개수를 고정
+ */
+export async function createTask(input: CreateTaskSchema) {
+ unstable_noStore(); // Next.js 서버 액션 캐싱 방지
+ try {
+ await db.transaction(async (tx) => {
+ // 새 Task 생성
+ const [newTask] = await insertTask(tx, {
+ title: input.title,
+ status: input.status,
+ label: input.label,
+ priority: input.priority,
+ });
+ return newTask;
+
+ });
+
+ console.log("tasks")
+
+ // 캐시 무효화
+ revalidateTag("tasks");
+ revalidateTag("task-status-counts");
+ revalidateTag("task-priority-counts");
+
+ return { data: null, error: null };
+ } catch (err) {
+ return { data: null, error: getErrorMessage(err) };
+ }
+}
+
+/* -----------------------------------------------------
+ 3) 업데이트
+----------------------------------------------------- */
+
+/** 단건 업데이트 */
+export async function modifiTask(input: UpdateTaskSchema & { id: string }) {
+ unstable_noStore();
+ try {
+ const data = await db.transaction(async (tx) => {
+ const [res] = await updateTask(tx, input.id, {
+ title: input.title,
+ label: input.label,
+ status: input.status,
+ priority: input.priority,
+ });
+ return res;
+ });
+
+ revalidateTag("tasks");
+ if (data.status === input.status) {
+ revalidateTag("task-status-counts");
+ }
+ if (data.priority === input.priority) {
+ revalidateTag("task-priority-counts");
+ }
+
+ return { data: null, error: null };
+ } catch (err) {
+ return { data: null, error: getErrorMessage(err) };
+ }
+}
+
+/** 복수 업데이트 */
+export async function modifiTasks(input: {
+ ids: string[];
+ label?: Task["label"];
+ status?: Task["status"];
+ priority?: Task["priority"];
+}) {
+ unstable_noStore();
+ try {
+ const data = await db.transaction(async (tx) => {
+ const [res] = await updateTasks(tx, input.ids, {
+ label: input.label,
+ status: input.status,
+ priority: input.priority,
+ });
+ return res;
+ });
+
+ revalidateTag("tasks");
+ if (data.status === input.status) {
+ revalidateTag("task-status-counts");
+ }
+ if (data.priority === input.priority) {
+ revalidateTag("task-priority-counts");
+ }
+
+ return { data: null, error: null };
+ } catch (err) {
+ return { data: null, error: getErrorMessage(err) };
+ }
+}
+
+/* -----------------------------------------------------
+ 4) 삭제
+----------------------------------------------------- */
+
+/** 단건 삭제 */
+export async function removeTask(input: { id: string }) {
+ unstable_noStore();
+ try {
+ await db.transaction(async (tx) => {
+ // 삭제
+ await deleteTaskById(tx, input.id);
+ // 바로 새 Task 생성
+ });
+
+ revalidateTag("tasks");
+ revalidateTag("task-status-counts");
+ revalidateTag("task-priority-counts");
+
+ return { data: null, error: null };
+ } catch (err) {
+ return { data: null, error: getErrorMessage(err) };
+ }
+}
+
+/** 복수 삭제 */
+export async function removeTasks(input: { ids: string[] }) {
+ unstable_noStore();
+ try {
+ await db.transaction(async (tx) => {
+ // 삭제
+ await deleteTasksByIds(tx, input.ids);
+ });
+
+ revalidateTag("tasks");
+ revalidateTag("task-status-counts");
+ revalidateTag("task-priority-counts");
+
+ return { data: null, error: null };
+ } catch (err) {
+ return { data: null, error: getErrorMessage(err) };
+ }
+}
+
+/**
+ * 1) 그룹 헤더(2줄 헤더)인지, 1줄 헤더인지 판별
+ * - Row1이 `group` 값들로 이루어져 있고,
+ * - Row2가 `excelHeader` 값들과 매칭되는지
+ */
+function detectHasGroupHeader(
+ worksheet: ExcelJS.Worksheet,
+ config: TaskColumnConfig[]
+): boolean {
+ // 전체 group 목록
+ const groupSet = new Set(
+ config.filter((c) => c.group).map((c) => c.group!.trim())
+ )
+ // 전체 excelHeader 목록
+ const headerSet = new Set(
+ config.filter((c) => c.excelHeader).map((c) => c.excelHeader!.trim())
+ )
+
+
+ // row1이 전부(또는 대부분) groupSet에 속하면 => 그룹 헤더일 가능성 높음
+ // row1Values = (index 0은 비어있을 수 있으므로) 안전하게 string 변환 후 trim
+ const row1Values = (worksheet.getRow(1)?.values ?? []) as (string | null | undefined)[]
+ const row2Values = (worksheet.getRow(2)?.values ?? []) as (string | null | undefined)[]
+
+ // row1Values가 전부 groupSet 내에 있거나 빈 문자열이면, "이건 그룹 헤더"
+ const row1IsMostlyGroup = row1Values.every((val) => {
+ if (!val) {
+ return true
+ }
+ return groupSet.has(val.trim())
+ })
+ // row2 중에 headerSet에 포함되는 값이 몇 개나 되는가?
+ // 즉, row2가 실제로 excelHeader로 구성되어 있으면 -> 2줄 헤더 가능성
+ const row2HeaderCount = row2Values.filter((val) => {
+ // val이 string인지 확인
+ if (typeof val === "string") {
+ return headerSet.has(val.trim())
+ }
+ // null/undefined(또는 숫자, 객체 등)이면 필터링 제외
+ return false
+ }).length
+
+ // (단순 로직) row1이 그룹 같고, row2가 적어도 1개 이상 excelHeader 매칭 => 2줄 헤더
+ // 프로젝트에 맞춰 좀 더 세밀하게 조건을 잡아도 됨.
+ if (row1IsMostlyGroup && row2HeaderCount > 0) {
+ return true
+ }
+ return false
+}
+
+export async function importTasksExcel(file: File): Promise<ImportResult> {
+ try {
+ // 1) 엑셀 로드
+ const buffer = await file.arrayBuffer()
+ const workbook = new ExcelJS.Workbook()
+ await workbook.xlsx.load(buffer)
+
+ // 첫 번째 시트만 사용
+ const worksheet = workbook.worksheets[0]
+ if (!worksheet) {
+ throw new Error("엑셀 파일에 시트가 없습니다.")
+ }
+
+ // 2) 그룹 헤더(2줄) or 일반 헤더(1줄) 판별
+ const hasGroupHeader = detectHasGroupHeader(worksheet, tasksColumnsConfig)
+ const headerRowIndex = hasGroupHeader ? 2 : 1
+ const dataStartRowIndex = hasGroupHeader ? 3 : 2
+
+ const headerRow = worksheet.getRow(headerRowIndex)
+ if (!headerRow) {
+ throw new Error("엑셀 헤더 행을 찾지 못했습니다.")
+ }
+
+ // 3) 엑셀 헤더(문자열) → 컬럼 인덱스(Map)
+ const columnIndexMap = new Map<string, number>()
+ headerRow.eachCell((cell, colIndex) => {
+ if (typeof cell.value === "string") {
+ columnIndexMap.set(cell.value.trim(), colIndex)
+ }
+ })
+
+ // 4) columnToFieldMap: "엑셀 열 인덱스" → "DB 필드(Task의 keyof)"
+ // 예) "Code" → "code", "Title" → "title", ...
+ const columnToFieldMap = new Map<number, keyof Task>()
+ tasksColumnsConfig.forEach((cfg) => {
+ if (!cfg.excelHeader) return
+ const colIndex = columnIndexMap.get(cfg.excelHeader.trim())
+ if (colIndex !== undefined) {
+ // 예: colIndex=1 -> cfg.id="code"
+ columnToFieldMap.set(colIndex, cfg.id)
+ }
+ })
+
+ // 5) 에러가 발생하면 표시할 용도
+ const errorRows: { rowIndex: number; message: string }[] = []
+
+ // 6) 엑셀에서 읽어온 행 데이터를 임시 보관
+ // "마지막 컬럼(D/d) → toDelete=true"
+ type ExcelRowData = {
+ rowIndex: number
+ fields: Partial<Task>
+ toDelete: boolean
+ }
+ const rowDataList: ExcelRowData[] = []
+
+ for (let r = dataStartRowIndex; r <= worksheet.rowCount; r++) {
+ const row = worksheet.getRow(r)
+ if (!row ) continue
+
+ // (6-1) 마지막 셀을 보고 DELETE 여부 판단
+ const lastCellValue = row.getCell(row.cellCount).value
+ const isDelete =
+ typeof lastCellValue === "string" &&
+ lastCellValue.toLowerCase() === "d"
+
+ // (6-2) 각 열 -> DB 필드 매핑
+ const fields = {} as Partial<Task>
+
+ columnToFieldMap.forEach((fieldId, colIdx) => {
+ const cellValue = row.getCell(colIdx).value
+ if (fieldId === "createdAt") {
+ fields.createdAt = undefined
+ } else {
+ fields[fieldId] = (cellValue ?? null) as any
+ }
+ })
+
+ rowDataList.push({
+ rowIndex: r,
+ fields,
+ toDelete: isDelete,
+ })
+ }
+
+ // (6-3) 혹시 이 시점에서 "필수 값이 누락됐다" 등의 검증을 하고 싶다면 errorRows.push(...)
+ // if (errorRows.length > 0) => 엑셀에 표시 후 리턴 (생략)
+
+ // 7) 현재 DB에 있는 "code" 목록을 가져온다
+ const existingCodes = await getAllTasks().then((rows) => rows.map((r) => r.code))
+ const existingCodeSet = new Set<string>(existingCodes.filter(Boolean))
+
+ // 8) CREATE/UPDATE/DELETE 목록 분리
+ const toCreate: Task[] = []
+ // (updateTasks 함수가 "ids: string[], data: Partial<Task>" 형태)
+ // - 여러 code를 한꺼번에 업데이트할 수도 있지만, 여기선 간단히 1code씩
+ const toUpdate: { codes: string[]; data: Partial<Task> }[] = []
+ const toDeleteCodes: string[] = []
+
+ for (const { rowIndex, fields, toDelete } of rowDataList) {
+ // code를 string으로 캐스팅
+ const code = fields.code ? String(fields.code).trim() : ""
+
+ if (toDelete) {
+ // DELETE
+ if (code && existingCodeSet.has(code)) {
+ toDeleteCodes.push(code)
+ }
+ // code가 없거나 DB에 없으면 무시
+ continue
+ }
+
+ // CREATE or UPDATE
+ if (!code) {
+
+ toCreate.push(fields as Task)
+ } else {
+ // code가 있고, DB에도 있으면 UPDATE
+ if (existingCodeSet.has(code)) {
+ toUpdate.push({ codes: [code], data: fields })
+ } else {
+ // code가 있지만 DB에 없으면 CREATE
+ toCreate.push(fields as Task)
+ }
+ }
+ }
+
+ // (선택) 에러가 있으면 여기서 다시 한 번 errorRows에 추가 후 반환 가능
+
+ // 9) 트랜잭션으로 처리
+ await db.transaction(async (tx) => {
+ // CREATE
+ if (toCreate.length > 0) {
+ await insertTasks(tx, toCreate)
+ }
+ // UPDATE
+ if (toUpdate.length > 0) {
+ for (const { codes, data } of toUpdate) {
+ await updateTasks(tx, codes, data)
+ }
+ }
+ // DELETE
+ if (toDeleteCodes.length > 0) {
+ await deleteTasksByIds(tx, toDeleteCodes)
+ }
+ })
+
+ // 10) 성공 메시지
+ const msg: string[] = []
+ if (toCreate.length > 0) msg.push(`${toCreate.length}건 생성`)
+ if (toUpdate.length > 0) msg.push(`${toUpdate.length}건 수정`)
+ if (toDeleteCodes.length > 0) msg.push(`${toDeleteCodes.length}건 삭제`)
+ const successMessage = msg.length > 0 ? msg.join(", ") : "No changes"
+
+ return {
+ errorFile: null,
+ errorMessage: null,
+ successMessage,
+ }
+ } catch (err: any) {
+ return {
+ errorFile: null,
+ errorMessage: err.message || "Import 중 오류가 발생했습니다.",
+ }
+ }
+} \ No newline at end of file
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 05:37:58 +0000