So now that we've wrapped up the transactions page, let's go ahead and do the following. Let's create an API endpoint to create a summary of our transactions and categories. So here's what I want you to do first. I want you to have at least a couple of transactions in your recent dates. Right, so make sure it's in the last 30 days.
So for me it's 10th of May, which means everything in the past 30 days I can select here. So I'm just randomly going to add some things with some incomes and some expenses here. So I basically want you to have some items here. Because remember, only the last 30 days will be calculated and shown here. And Now switch to overview and let's go ahead and let's develop.
So you can shut down the Drizzle Studio, no need for that now, just keep your app running right here. Let's go ahead inside of our app folder API route And let's go ahead and let's create summary.ts. And inside of this summary, let's go ahead and let's import Hono. From Hono. And let's write const app new Hono.
And let's write .get. Slash. And for now we can just add a very simple controller summary true like this and let's export default app Now let's go inside of a route.ps here and let's chain our summary. So we are going to have to import summary, rom.slash summary like this. So I'm going to make this first.
So they all have the same increasing length. There we go. It doesn't matter, right? I mean, the order of your chaining matters if you have same named stuff. But all of our are named differently so it does not matter.
So now you should be able to go to localhost 3000 slash API slash summary and you should just get summary true inside. That's it. And this is going to be our only endpoint in the summary routes. So go inside of summary and let's go ahead and let's develop this. First of all we're gonna add the clerk middleware here from HonoClerkAuth right here.
Then we're gonna add a z-validator from HonoZodValidator. We're going to validate query field and we're going to use Zod. So import z from Zod. Object from z.string.optional. And then we're going to have to z.string.optional and we're going to have account ID z.string.optional as well.
There we go. And now inside of this asynchronous method, let's go ahead and let's get the out using getOutC and we need to import getOut from hono.clerk.out. Let me just change these imports right here. There we go. So we can also immediately destructure everything we need from our C request valid query.
So that's going to be from to and Account ID. Now let's check if we don't have out ?userid. In that case, we can return a c.json with an error inside saying unauthorized. And let's mark this as 401. That's very important to mark this as an error.
Great and now inside of here let's go ahead and let's write const default to to be new date then const default from to be sub days from date FNS so make sure you've imported sub days from date FNS and let's also add parse and difference in days from date FNS So sub days, parse and difference in days. So inside of here we're going to sub 30 days from the default to. And then in here we can go ahead and we can create our start date. So if we have from in that case we're going to parse from using our format so year, month, day, new date as the third argument or otherwise use the default from And then inside of here let's define our end date to check if we have two. If we have two we're going to do the exact same thing here but we're going to pass two.
Like that. Otherwise default to. There we go. Now let's define period length, the difference in days, because remember, it doesn't have to be 30 days right if user selects their own period it can be 40 days or 15 days so end date start date and plus one for the offset then let's do last period start so every time we calculate the summary we're also going to calculate the period 30 days, well not 30 days but the equivalent amount but from before that, from the period before that so that we can show the user all right if you selected last 15 days you made this much money but that is 5% less than the previous period of that same length right so that's why we want to calculate the previous period as well So we're going to sub days inside of here, start date and period length and then let's define last period end to be the end date and sub days as well. There we go.
Great. So now I want to do, I want to create two things. So I want to get the current period from something, right? And I want to get the previous period or last period from something as well and this something will be our await fetch financial data method which we don't have yet and this fetch financial data will take three arguments. The first one will be auth.userid, the second one will be start date and the last one will be end date.
So these ones right here. So I'm passing in auth.userid. You don't have to obviously, but if you want to extract this fetch financial data in another folder or file, then you're going to have to pass auth.userid. But for simplicity, I'm going to write it inside of here. So obviously if you write it here, you don't need to pass it as an argument, but just in case someone wants to copy it somewhere else, I'm gonna leave it like this.
Now let's create this asynchronous function, fetch financial data. So the first one is going to be user ID which is a string like this. The next one is start date which is a date and end date which is a date as well. Like this. Let's open this method here and inside of here we're going to return await database from database drizzle so just make sure you've added this and we're going to select the following we're going to select income we're going to select expenses and we're going to select remaining.
So I want to know how much money we've made in this period. So for that, I'm going to use SQL from Drizzle ORM. So make sure you've added the import. I'm just gonna move it here. I'm gonna open Bactex and I'm going to sum case when transactions, so we have to import transactions from database schema, make sure you add this.
So transactions.amount, like this, is higher or equal than zero. So that's my income, right? So then we're gonna continue writing. I'm just gonna zoom out so you can see how this looks like in one line. I'm going to zoom back in later.
So if it's higher than zero, then this is my income. So then I'm going to do the transactions.amount. Like that. Else, I'm going to write zero and end. And then I'm going to add dot map width and pass in the number like this so let me go ahead and try and zoom out one more time, hopefully so you can see it in one line.
Alright I'm not gonna zoom in any further. So let me show you how this looks like in one line when I have to scroll like this. Maybe this is easier for you. Alright I'm gonna turn word wrap back on so it collapses now. Great, so we have the income and now we're gonna go ahead and copy and paste for expenses a similar thing.
So this is gonna be the opposite. So it's gonna be some case when transactions.amount is this time less than zero, right? So very similar, but a different clause here. And else zero can be stay and stays the same and map with is still the number. And then we're gonna have a last one, which is gonna use the native, well, not native, but the drizzle ORM sum.
So let me just show you, we have SQL and sum imported here from Drizzle ORM so in here we can just use this sum because it's a simpler method transactions dot amount that's it map with number no high logic here right we just have to count everything And now we have to specify from where. So from transactions, we already added that import, but remember transactions don't belong to users, they belong to accounts. So we have to create an inner join of accounts. So we're going to write accounts from database schema. So make sure you add accounts here.
And then we're going to write equals. So import equals from drizzle ORM. And inside of equals for the first argument, I'm gonna pass the transactions.accountID and then for the second one is gonna be accounts.ID like that. So that's gonna be the inner join and then we can chain where here and in the where I can add and from drizzle orm so make sure you've added and equals equal and sum and in this end we are going to add for the first argument a check if we have an account ID if we do we're gonna add equals transactions.accountID with account ID otherwise undefined like this let's go ahead did I add and whoops how did this happen I don't know so and that's what I need and equals SQL and sum there we go And this is account ID with a single C. So that's the first argument.
Then we're gonna have equals accounts.userID to be auth user ID. Actually not auth user ID, but instead it's going to be this userId here from the function in case you want to extract that right so it's easier. Great so we have that and then we're gonna add greater than or equal from DrizzleORM for transactions.date and startDate and we're gonna copy and paste it and make this less than or equal from Drizzle ORM for end date. So just ensure that you have end equals greater than less than equal SQL and sum from drizzle ORM. There we go.
So that is our query here for the periods. So if you want to you can already try and return this. So return c.json. Let's return the current period and the last period. So let's see if I refresh this we should now see some changes here.
There we go. So you can see my total income, my expenses and how much I have remaining, right? Obviously these are million units, so they have to be divided by a thousand to be presentable on the front end. But we can already calculate something in this period. That's why I told you to have some transactions in the period, right?
Otherwise, it's not going to work. There we go. Great. So now I want to create a method that will help me calculate the difference from the current period and the last period in a percentage. So let's go inside of the utils folder and let's export function calculate percentage change.
So that's going to accept current as a number and previous as a number as well. And inside of here, what we're going to do is we're going to check if previous is equal to zero. In that case, let's check if previous is equal to current. In that case, the result is zero. Otherwise it's a hundred percent increase.
Otherwise let's return open parentheses, open double parentheses inside current minus previous, like this, divided by previous, and we are going to multiply it by a hundred. There we go. Perfect. So now we have that change. And now inside of here, we can define the following.
We can write const income change, and we can pass in the calculate percentage change from our libutils. So just make sure you've added the calculate percentage change here. So we're gonna open this and we're gonna pass in the current period income and last period income. You could of course, you know, try, if you know SQL better than me, feel free. I don't know SQL that well, right?
So if you know a better way to calculate this inside of SQL, go ahead and do that. It's probably much faster, much more optimized, right? But I'm satisfied with at least, you know, not using Prisma like queries. So this is a big step for me. Okay, but if you know SQL better, feel free to try and do that here, what I'm doing here with this percentage changes.
So the next one is gonna be expenses change. So I'm gonna be looking at the expenses here. Like this. And the last one is going to be the remaining change. So remaining, there we go.
So if you want to, you can now add in here the income change, the expenses change and the remaining change to see exactly how much in percentage this has changed over the last period. Perhaps your for example mine are zero here because I just don't have that many transactions right I have a very small amount of transactions so there's nothing to compare with the last month. But I think that if I go into my localhost 3000, if I go into transactions, for example, and if I add one from previous month, from maybe March, for example, if I try that, and if I, for example, make an expense of $500, it's not gonna be visible here, but I think that maybe if I did this correct maybe it's gonna be visible here it's not okay we're gonna see that later when we create a seed script which is going to be able to fill from months before and up till now. So we're gonna see this change working. Okay.
Let me just confirm that we didn't have a bug here. All right, I think there's no bug, we just don't have enough data. Great. And now what I wanna do is I want to create queries to show the user how much they spend by category. So we're gonna be using the group by element.
So let's go ahead and write const category to be await database dot select. We are going to select the name which will be categories dot name and we have to import the categories from database schema so just make sure you've added this. Besides the name we are going to add the value which will be a SQL operator sum absolute and then we're going to add the transactions.amount.MACWIDTHNumber and then we're going to write from transactions inner join so we want to create two inner joins now, right? We're gonna create an inner join for accounts with an equals here of transactions.accountID and accounts.id, but we also want an inner join for categories. Usually we use left join for categories, but in this case, the query won't make sense if we load something that doesn't have a category, right?
So let's go ahead and join the categories using transactions.categoryId with categories.id. Let me just see, oh, I'm missing the categories in the inner join, there we go. So this is accounts, account ID, accounts.id, categories, category ID, category.id. There we go. And then let's do a where here.
And in the where, we can actually copy it from the one above so we save some time. This where. So let's chain that. So the user ID method here doesn't exist, so it's going to be alf.userid. Like this.
The account ID logic stays the same. The equals stays the same. But we are going to add one more thing which is less than transactions.amount is zero so we only want to count the positive category spend so we have to import larger than in Drizzle ORM larger than right here, there we go. All right, we have larger than here. So we are only loading those categories, right?
And now we have to finish the query by grouping by category name, right? So I only wanted to display, you know, travel and then the amount. I don't want an array of items where travel repeats multiple times. I'm going to group it by name. So it aggregates by only a single name.
So it's only going to repeat once, right? And then let's order by, and let's go ahead and write descending from Drizzle ORM. So make sure you've added this import. And we're going to write a SQL here. Some absolute transactions dot amount.
Is this good enough? Let me just see. Cannot find the SQL. Oh, again, something happened. My apologies.
I keep somehow doing this. There we go. So now I think this should be just fine. So let's go ahead and do this. So I'm not never going to send back all the categories because if there's a ton of them, right, I don't need them.
So I'm going to write const top categories to be category.slice 0 and 3 and const other categories are gonna be category.slice3, so I am doing the constant category here. So make sure you don't accidentally do categories because that is our schema, right? So make sure you are doing this on this constant where we did the query for categories. Great. So now I wanna keep everything else as well but I'm gonna keep everything else in the other sum.
So that's gonna be under other. I only wanna display the top three most spent categories and everything else will be under other. So I'm gonna write other categories which we just have here. .Reduce I will get sum and current and I'm just gonna reduce sum plus current value beginning from zero. There we go.
Perfect. And now let's go ahead and let's finalize this by writing const final categories. So that's going to be top categories like this. And then let's do if other categories dot length is less than, is more than zero, finalCategories.push name is going to be other and value will be the other sum. So no point in showing the other categories if there are only three categories, right?
So we do this little trick here by assigning this as an array and then we add an additional one if other categories actually exist. So we can now add the final categories here to the change as well. So let's refresh this to see what we spent our money on. There we go. So this is the most spent category that I have chosen.
Right? So I only chose this one. So I think that now if I try, and for example, go inside of transactions, and if I go inside of add new, If I pick a date, for example, a few days ago, let me go ahead and create food here. Test and I'm going to spend a thousand dollars in food. And I think that now this might be visible in the summary.
Let's see. All right, it still doesn't seem to be visible. I'm not exactly sure why. Oh, I realized why. I also think I found a bug here.
So I just modified this food category to be in a half a million dollars. And also we have a bug here. We forgot to change our edit transaction. So it has this, we have to convert from million it's somewhere, but let's do this. Let's go ahead and create, for example, $5, 000, but make sure it's an expense, right?
That's the important part. So now if I go to slash API summary, It should be here. There we go. So we spent a lot of money on food. So it's working.
Great. Okay. Fine. And now the last thing that we have to do is we have to create data for our graph to show how much we spent by day in this period that the user selected. So const active days is going to be await database dot select, date will be transactions dot date because we are going to group by date later and then income is going to be SQL SUM case when transactions.amount is higher than, greater or equal than zero.
In that case, we're going to sound transactions.amount else, whoops, else zero and math with number. So let me zoom out. So perhaps, whoops, where was I? My apologies. So it's a very similar, if not exactly the same query as the income we had in the beginning when we were calculating the income, the expenses and remaining right.
So some case when transactions amount is greater or equal than zero then we use transactions.amount else zero and math with number And then you can go ahead and copy and paste this one for, oops, for expenses. So for each day, we're going to calculate the income and the expenses. So this one will only calculate if the income is, if the amount of that transaction in those days was less than zero. Everything else can stay the same. There we go.
So let's now go ahead and do, well, the same thing we already did a couple of times. So from transactions, and then let's inner join the accounts. So let's copy and paste that. So we save some time. From transactions, inner join with transaction account.
There we go. We can also copy the where here. So we are checking by account ID here. So let's just confirm that. We have account ID.
If we do, we do the equals, otherwise we do undefined. Like that. And we confirm that these are only the users' transactions. So this is kind of the most important equals sign here. We don't need this less than that's to be removed but we do need greater than or equal and less than or equal and finally let's do group by so we aggregate by transactions dot date so we don't have repeated dates And the last one is to order this by increasing dates.
So transactions.date like this. And I somehow did this again. Okay, There we go. The problem now is that this will only generate the data for the days that we selected, but only those that actually have some transactions. So I'm gonna add active days here.
So when I refresh inside of this API summary, There we go. So we have this dates but that's not enough for us to fill a useful graph. I also want to fill the dates where we have nothing. So we're going to create a method for that called fillMissingDays. So let's go inside of our utils and let's write export function fill missing days.
So the first argument will be active days, which is going to be an object and an array of those objects. Inside of the object, we accept the date, income, which is number, and expenses, which is a number as well. Then we have a start date, which is a date, and an end date, which is a date as well. Now let's go ahead and let's import each day of interval from date FNS. So using that we'll be able to generate the rest of the days here.
So First of all, if activeDays.length is equal to zero, we just return back an empty array, right? Then let's write const allDays to be each day of interval. And let's pass in start to be our start date and end to be our end date. And then we can say const transactions by day to be all days.mat, get the individual day, open a function. Let's attempt to find this in our existing active days which we passed here so active days dot find the short for day is same day using date FNS so we don't add duplicates here so is same day D dot date and day itself like this and now we're gonna write if found return found else we are going to return an empty day.
So date will be this date, income will be zero, and expenses will be zero. So you're going to see why we are doing this in a second. Let's return our transactions by day. So remember this is how it looks right now. So we only returned three days even though we selected 30 days.
So our graph needs to show 30 days. So let's go inside of our summary method here and let's go ahead and let's finally write const days to be fill missing days and pass in active days start date and end date like this and make sure you've imported the fill missing days from libutils. And then inside of here we can go ahead and we can send days instead of active days here. Let's go ahead and refresh this and let's see this now. And there we go.
You can see how now we have everything we need for our graph and only some days have values, but most of these, well, don't have anything. Great, so this is what we wanted and that's it for the summary end point here. So let's just go ahead and style this better. So we're gonna have data first. It's gonna start from data so our API structure stays consistent.
Then we're going to have remaining amount. So that's gonna be currentPeriod.remaining And then we're gonna have the remaining change below that. Then we're gonna have an income amount. That's going to be current period income. Then we're gonna have income change.
Then we'll have expenses amount. That's gonna be current period dot expenses. And then we're gonna have expenses change. So I'm just bringing things back that we are going to need for the dashboard. And then I will need the categories, which will be my final categories.
And I will need my days, which will be my days. They are the same named constant. There we go. We've wrapped up our summary API endpoints. So if I refresh, there we go.
This is enough for us now to display on the dashboard. Before we wrap this up, I just want to go ahead and fix this million units issue that we have. So when I select something that has an amount of $500 and I click edit in here it appears as half a million dollars. So I think I know exactly where the culprit is. Let's go inside of our features, transactions, API, use get individual transaction.
And instead of returning data here, we're going to return an object where we spread the data and get the amount and write convert amount from milli units, data.amount. And make sure you've added an import to convert amount from milli units. Let's try this again. I'm going to close the drawer and refresh. There we go.
My input, my transaction for $500. When I click edit, it has $500 here. Perfect. Amazing, amazing job. What we're gonna do next is we're gonna create the, well, all the elements needed for the dashboard which will work with the summary API endpoint.