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
96
97
98
99
100
101
102
103
104
105
|
"use server";
import { sql, SQL } from "drizzle-orm";
import db from "@/db/db";
import { materialSearchView } from "@/db/schema/items";
export interface MaterialSearchItem {
materialGroupCode: string;
materialName: string;
displayText: string;
}
export interface MaterialSearchResult {
success: boolean;
data: MaterialSearchItem[];
pagination: {
page: number;
perPage: number;
total: number;
pageCount: number;
hasNextPage: boolean;
hasPrevPage: boolean;
};
}
/**
* 자재 검색 함수 - material_search_view에서 검색
*/
export async function searchMaterialsForSelector(
query: string,
page: number = 1,
perPage: number = 10
): Promise<MaterialSearchResult> {
try {
const offset = (page - 1) * perPage;
// 검색 조건
let searchWhere: SQL<unknown> | undefined;
if (query.trim()) {
const searchPattern = `%${query.trim()}%`;
searchWhere = sql`(
${materialSearchView.materialGroupCode} ILIKE ${searchPattern} OR
${materialSearchView.materialName} ILIKE ${searchPattern} OR
${materialSearchView.displayText} ILIKE ${searchPattern}
)`;
}
const { data, total } = await db.transaction(async (tx) => {
// 데이터 조회
const data = await tx
.select()
.from(materialSearchView)
.where(searchWhere)
.orderBy(materialSearchView.materialGroupCode, materialSearchView.materialName)
.limit(perPage)
.offset(offset);
// 총 개수 조회
const countResult = await tx
.select({ count: sql<number>`count(*)` })
.from(materialSearchView)
.where(searchWhere);
const total = countResult[0]?.count || 0;
return {
data: data.map((row) => ({
materialGroupCode: row.materialGroupCode,
materialName: row.materialName,
displayText: row.displayText,
})),
total,
};
});
const pageCount = Math.ceil(total / perPage);
return {
success: true,
data,
pagination: {
page,
perPage,
total,
pageCount,
hasNextPage: page < pageCount,
hasPrevPage: page > 1,
},
};
} catch (error) {
console.error("자재 검색 오류:", error);
return {
success: false,
data: [],
pagination: {
page: 1,
perPage: 10,
total: 0,
pageCount: 0,
hasNextPage: false,
hasPrevPage: false,
},
};
}
}
|
