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
|
import type pg from "pg";
/** A row from the `cemetary_plot` table. */
export interface CemetaryPlot {
id: number;
address: string;
ownerId: number;
//assignmentInterval: Date;
}
/** A row from the `assignments` table. */
export interface Assignment {
id: number;
gardenerId: number;
cemetaryPlotId: number;
date: Date;
}
/**
* Retrieves all assignments for the given user.
*
* @param dbConn Connection to database.
* @param userId ID used to identify user.
*/
export async function getAssignments(dbConn: pg.ClientBase, userId: number): Promise<Assignment[]> {
const result = await dbConn.query(
"SELECT * FROM assignments WHERE gardener_id = $1 ORDER BY date",
[userId],
);
return result.rows.map(
(r) =>
({
id: r.id,
gardenerId: r.gardener_id,
cemetaryPlotId: r.cemetary_plot_id,
date: r.date,
}) satisfies Assignment,
);
}
type GetAssignmentResult =
| { assignment: Assignment; cemetaryPlot: CemetaryPlot }
| { assignment: null; cemetaryPlot: null };
/**
* Retrieves a specfic assignment, along with relevant cemetary plot.
*/
export async function getAssignmentAndCemetaryById(
dbConn: pg.ClientBase,
assignmentId: number,
): Promise<GetAssignmentResult> {
const queryText = `SELECT
a.id,
a.gardener_id,
a.cemetary_plot_id,
a.date,
c.id,
c.address,
c.owner_id,
c.assignment_interval
FROM
assignments AS a
INNER JOIN
cemetary_plots AS C ON c.id = a.cemetary_plot_id
WHERE c.id = $1`;
const result = await dbConn.query({ rowMode: "array", text: queryText }, [assignmentId]);
if (result.rowCount == 0) {
return { assignment: null, cemetaryPlot: null };
}
const assignment: Assignment = {
id: result.rows[0][0],
gardenerId: result.rows[0][1],
cemetaryPlotId: result.rows[0][2],
date: result.rows[0][3],
};
const cemetaryPlot: CemetaryPlot = {
id: result.rows[0][4],
address: result.rows[0][5],
ownerId: result.rows[0][6],
//assignmentInterval: result.rows[0][7],
};
return { assignment, cemetaryPlot };
}
|
