export interface AgingCustomer {
customer: string;
name: string;
amount0_30: number;
amount30_60: number;
amount60_90: number;
amount90_120: number;
amount120: number;
}
async getAgingList(): Promise<AgingCustomer[]> {
const res = await this.db
.select({
customer: sql<string>`customer`,
name: sql<string>`name`,
amount0_30:
sql<number>`SUM(CASE WHEN age >= 0 AND age < 30 THEN amount ELSE 0 END)`.as(
'amount0_30',
),
amount30_60:
sql<number>`SUM(CASE WHEN age >= 30 AND age < 60 THEN amount ELSE 0 END)`.as(
'amount30_60',
),
amount60_90:
sql<number>`SUM(CASE WHEN age >= 60 AND age < 90 THEN amount ELSE 0 END)`.as(
'amount60_90',
),
amount90_120:
sql<number>`SUM(CASE WHEN age >= 90 AND age < 120 THEN amount ELSE 0 END)`.as(
'amount90_120',
),
amount120:
sql<number>`SUM(CASE WHEN age >= 120 THEN amount ELSE 0 END)`.as(
'amount120',
),
})
.from(
sql`
(
SELECT
"customer",
"name",
"amount",
(current_timestamp::date - "dueDate"::date) AS age
FROM "Invoices"
) sub
`,
)
.groupBy(sql`customer, name`)
.orderBy(sql`customer ASC`);
console.log(res);
typia.assert<AgingCustomer[]>(res);
return res;
}
export interface AgingCustomer {
customer: string;
name: string;
amount0_30: number;
amount30_60: number;
amount60_90: number;
amount90_120: number;
amount120: number;
}
async getAgingList(): Promise<AgingCustomer[]> {
const res = await this.db
.select({
customer: sql<string>`customer`,
name: sql<string>`name`,
amount0_30:
sql<number>`SUM(CASE WHEN age >= 0 AND age < 30 THEN amount ELSE 0 END)`.as(
'amount0_30',
),
amount30_60:
sql<number>`SUM(CASE WHEN age >= 30 AND age < 60 THEN amount ELSE 0 END)`.as(
'amount30_60',
),
amount60_90:
sql<number>`SUM(CASE WHEN age >= 60 AND age < 90 THEN amount ELSE 0 END)`.as(
'amount60_90',
),
amount90_120:
sql<number>`SUM(CASE WHEN age >= 90 AND age < 120 THEN amount ELSE 0 END)`.as(
'amount90_120',
),
amount120:
sql<number>`SUM(CASE WHEN age >= 120 THEN amount ELSE 0 END)`.as(
'amount120',
),
})
.from(
sql`
(
SELECT
"customer",
"name",
"amount",
(current_timestamp::date - "dueDate"::date) AS age
FROM "Invoices"
) sub
`,
)
.groupBy(sql`customer, name`)
.orderBy(sql`customer ASC`);
console.log(res);
typia.assert<AgingCustomer[]>(res);
return res;
}