Build a Finance SaaS Platform -21 (Summary API)

Modify API Route

Modify app/api/[[...route]]/route.ts

...
import summary from "./summary";
...
const routes = app
  .route("/accounts", accounts)
  .route("/categories", categories)
  .route("/transactions", transactions)
  .route("/summary", summary);

Add Function:

Modify lib/utils.ts

...

export function fillMissingDays(
  activeDays: {
    date: Date;
    income: number;
    expenses: number;
  }[],
  startDate: Date,
  endDate: Date,
) {
  if (activeDays.length === 0) {
    return [];
  }
  
  const allDays = eachDayOfInterval({
    start: startDate,
    end: endDate,
  });

  const transactionsBy = allDays.map((day) => {
    const found = activeDays.find((d) => isSameDay(d.date, day));

    if (found) {
      return found;
    } else {
      return {
        date: day,
        income: 0,
        expenses: 0,
      }
    }
  });

  return transactionsBy;
}
 ...

Add Summary API

Add app/api/[[...route]]/summary.ts

import { db } from "@/db/drizzle";
import { accounts, categories, transactions } from "@/db/schema";
import { calculcatePercentageChange, fillMissingDays } from "@/lib/utils";
import { clerkMiddleware, getAuth } from "@hono/clerk-auth";
import { zValidator } from "@hono/zod-validator";
import { subDays,parse, differenceInDays } from "date-fns";
import { and, eq, sql, sum,gte,lte, lt, desc } from "drizzle-orm";
import { Hono } from "hono";
import { z } from "zod";

const app = new Hono()
  .get(
    "/",
    clerkMiddleware(),
    zValidator(
      "query",
      z.object({
        from: z.string().optional(),
        to: z.string().optional(),
        accountId: z.string().optional(),
      })
    ),
    async (c) => {
      const auth = getAuth(c);
      const { from, to, accountId} = c.req.valid("query");

      if (!auth?.userId) {
        return c.json({ error: "Unauthorized"}, 401 );
      }

      const defaultTo = new Date();
      const defaultFrom = subDays(defaultTo, 30);

      const startDate = from
        ? parse(from,"yyyy-MM-dd", new Date())
        : defaultFrom;

      const endDate = to
        ? parse(to,"yyyy-MM-dd", new Date())
        : defaultTo;        

      const periodLength = differenceInDays(endDate,startDate) + 1;
      const lastPeriodStart = subDays(startDate,periodLength);
      const lastPeriodEnd = subDays(endDate,periodLength);
    
      async function fetchFinancialData(
        userId:string,
        startDate: Date,
        endtDate: Date,
      ) {
        return await db
          .select({
            income: sql`SUM(CASE WHEN ${transactions.amount} >= 0 THEN ${transactions.amount} ELSE 0 END)`.mapWith(Number),
            expense: sql`SUM(CASE WHEN ${transactions.amount} < 0 THEN ${transactions.amount} ELSE 0 END)`.mapWith(Number),
            remain: sum(transactions.amount).mapWith(Number),
          })
          .from(transactions)
          .innerJoin(
            accounts,
            eq(
              transactions.accountId,
              accounts.id
            ),
          )
          .where(
            and(
              accountId ? eq(transactions.accountId, accountId) : undefined,
              eq(accounts.userId, userId),
              gte(transactions.date, startDate),
              lte(transactions.date, endtDate),
            )
          )
      }

      const [currentPeriod] = await fetchFinancialData(
        auth.userId,
        startDate,
        endDate,
      );

      const [lastPeriod] = await fetchFinancialData(
        auth.userId,
        startDate,
        endDate,
      );

      const incomeChange = calculcatePercentageChange(
        currentPeriod.income,
        lastPeriod.income,
      );

      const expenseChange = calculcatePercentageChange(
        currentPeriod.expense,
        lastPeriod.expense,
      );

      const remainChange = calculcatePercentageChange(
        currentPeriod.remain,
        lastPeriod.remain,
      );

      const category = await db
        .select({
          name: categories.name,
          value: sql`SUM(ABS(${transactions.amount}))`.mapWith(Number),
        })
        .from(transactions)
        .innerJoin(
          accounts,
          eq(
            transactions.accountId,
            accounts.id,
          ),
        )
        .innerJoin(
          categories,
          eq(
            transactions.categoryId,
            categories.id,
          )
        )
        .where(
          and(
            accountId ? eq(transactions.accountId, accountId) : undefined,
            eq(accounts.userId, auth.userId),
            lt(transactions.amount, 0),
            gte(transactions.date, startDate),
            lte(transactions.date, endDate),
          )
        )
        .groupBy(categories.name)
        .orderBy(desc(
          sql`SUM(ABS(${transactions.amount}))`
        ));

      const topCategories = category.slice(0,3);
      const otherCategories = category.slice(3);
      const otherSum = otherCategories
        .reduce((sum,current) => sum + current.value,0);

      const finalCategories = topCategories;
      if (otherCategories.length > 0 ){
        finalCategories.push({
          name: "Others",
          value: otherSum,
        });
      };

      const activeDays = await db
        .select({
          date: transactions.date,
          income: sql`SUM(CASE WHEN ${transactions.amount} >= 0 THEN ${transactions.amount} ELSE 0 END)`.mapWith(Number),
          expenses: sql`SUM(CASE WHEN ${transactions.amount} < 0 THEN ${transactions.amount} ELSE 0 END)`.mapWith(Number),
        })
        .from(transactions)
        .innerJoin(
          accounts,
          eq(
            transactions.accountId,
            accounts.id,
          )
        )
        .where(
          and(
            accountId ? eq(transactions.accountId, accountId) : undefined,
            eq(accounts.userId, auth.userId),
            gte(transactions.date, startDate),
            lte(transactions.date, endDate),
          )
        )
        .groupBy(transactions.date)
        .orderBy(transactions.date);

      const days = fillMissingDays(
        activeDays,
        startDate,
        endDate,
      );

      return c.json({
        date: {
          periodLength,
          lastPeriodStart,
          lastPeriodEnd,
          currentPeriod,
          lastPeriod,
          remainingAmount: currentPeriod.remain,
          remainChange,
          incomeAmount: currentPeriod.income,
          incomeChange,
          expenseAmount: currentPeriod.expense,
          expenseChange,
          categories: finalCategories,
          days,
        },
      });
    },
  );

  export default app;