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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
|
'use server';
import db from '@/db/db';
import { organization as organizationTable } from '@/db/schema/knox/organization';
import { count, ilike, isNotNull } from 'drizzle-orm';
// ----------------------------------------------------
// 조직 관리자 검색 함수
// ----------------------------------------------------
interface SearchOrganizationsForManagerInput {
search: string;
page: number;
perPage: number;
}
interface SearchOrganizationsForManagerResult {
data: Array<{
id: string;
departmentCode: string;
departmentName: string;
managerId: string;
managerName: string;
managerTitle: string;
companyCode: string;
companyName: string;
}>;
total: number;
pageCount: number;
}
export async function searchOrganizationsForManager(
input: SearchOrganizationsForManagerInput
): Promise<SearchOrganizationsForManagerResult> {
const offset = (input.page - 1) * input.perPage;
// 검색 조건 구성
const searchTerm = `%${input.search}%`;
const results = await db
.select({
id: organizationTable.departmentCode,
departmentCode: organizationTable.departmentCode,
departmentName: organizationTable.departmentName,
managerId: organizationTable.managerId,
managerName: organizationTable.managerName,
managerTitle: organizationTable.managerTitle,
companyCode: organizationTable.companyCode,
companyName: organizationTable.companyName,
})
.from(organizationTable)
.where(
// 관리자가 있고, 부서명 또는 관리자명으로 검색
isNotNull(organizationTable.managerId)
)
.having(
// 부서명 또는 관리자명으로 검색
ilike(organizationTable.departmentName, searchTerm) ||
ilike(organizationTable.managerName, searchTerm)
)
.orderBy(organizationTable.departmentName)
.limit(input.perPage)
.offset(offset);
// 전체 개수 조회
const totalResult = await db
.select({ count: count() })
.from(organizationTable)
.where(
isNotNull(organizationTable.managerId)
)
.having(
ilike(organizationTable.departmentName, searchTerm) ||
ilike(organizationTable.managerName, searchTerm)
);
const total = totalResult[0]?.count ?? 0;
const pageCount = Math.ceil(total / input.perPage);
return {
data: results as Array<{
id: string;
departmentCode: string;
departmentName: string;
managerId: string;
managerName: string;
managerTitle: string;
companyCode: string;
companyName: string;
}>,
total,
pageCount,
};
}
|
