Build a Referral System with Laravel
This episode is for members only

Sign up to access "Build a Referral System with Laravel" right now.

Get started
Already a member? Sign in to continue
Playing
23. Generating a CSV attachment

Episodes

0%
Your progress
  • Total: 2h 39m
  • Played: 0m
  • Remaining: 2h 39m
Join or sign in to track your progress

Transcript

00:00
OK, so let's get this CSV attachment created and attached to the email that we just sent. So for this, we're going to use Laravel Excel.
00:09
It's a really nice way we can just pass in some data here, whether it's an array, a collection, a query. And we can map these up to different headings and basically include the data we want.
00:18
It's really nice to work with. So let's go ahead and install this, and then we'll look at how we can get this working. OK, so we'll head over to the installation section,
00:26
and we'll go ahead and grab the composer install command for this. And of course, let's paste this in. OK, so just let's close this off,
00:34
and let's think about what we need to pass through here to this referral payout. Because remember, this is the email where we need to attach the data down here.
00:44
Well, it's going to be pretty much all of this, so all of these records. But we don't want to get this as an array. We could use get, because we're eventually
00:52
going to pass a collection to build up a CSV from. But let's just go ahead and pass the entire query down into here. So let's get rid of this die dump, and let's just pass all of these records
01:03
directly into referral payout. So we can accept this into here now. This is going to be a builder. So let's go ahead and pull this in.
01:11
And what do we call them? Let's just call these payouts, so we don't get confused. And then down here, what we can do is use these payouts directly in an attachment to create this from that raw data.
01:23
So instead of here, to attach something with an email with Laravel, we're going to go ahead and use illuminate mail attachment. And we're going to say from data.
01:31
There are lots of different ways that you can include this. Now, this will take in a closure. And we basically just want to return the data from here that we actually want to attach.
01:41
So the second argument for this is going to be the name of the file that we're attaching. So let's go ahead and just say now. We'll grab the current month with F,
01:51
and then we'll just concatenate on export.csv. And then lastly, what we can do at the end of this is say with MIME, and we can choose the MIME type. So let's say text CSV.
02:03
OK, great. So all that's left to do is just return the Excel data from here. Now, with this package, what we can do is we can say Excel and use the facade, and we can say raw.
02:16
And it's really important that from here, we return raw data, because that then will need to go into the attachment with that MIME type with that name to be built up.
02:25
We don't want to store this CSV anywhere. There's not really much point. OK, so into here, we need a specific export for this data. So we'll go ahead and create that out in our app just here.
02:36
Let's create a referrals directory. And let's create out a referral payout export class. And this is specific to that package that we've just pulled in.
02:49
So let's go ahead and fill in what we need to in here. And referrals, and this is referral payout export. OK, great. So the way that we use this package,
03:03
the first thing that we need to do is include the exportable trace in here. And the next thing we need to do is implement certain methods of how we want this to work.
03:14
So the first thing is this is going to be from a collection of data. So we've got our query builder. We're going to turn this into a collection
03:21
and then pass it down to here. And the second thing is we want to include mapping. So we want to be able to map the columns up to very specific things that we want to do.
03:31
So we pull both of them interfaces in and apply these to this class. We just need to create the methods out now that these interfaces require.
03:39
So from collection requires that we have a collection method. So let's go ahead and create out a collection method in here. That needs to return a Laravel collection, of course. And with mapping requires that we have a map here
03:52
with a row for each record. So let's go ahead and create this out in here. And we'll return an array from there, just an empty array for now.
04:02
OK, so now that we've done that, let's just make sure we pull that in properly. The collection data, we just need to new this up and pass that data in.
04:09
So we're going to go ahead and new up our referral payout export. And we're going to pass through the payouts that we passed into here.
04:22
So we just say this payouts. And that's pretty much it. So now into the export itself, we will get the payouts passed into here.
04:31
So again, let's create out a payouts property. And make sure we pull the builder in. We don't need to namespace that. And then from here, we just want to return this payouts.
04:46
And get, which will return to us a Laravel collection, which is required to be returned from this method. OK, so now that we've done that, we've got map implemented here. Let's just check that this sends.
04:57
It won't have any data in, because the mapping is completely empty. But let's just see if this sends over. So we'll come over to the browser, give this a refresh.
05:05
And yeah, so Excel raw. OK, yeah, so we've got one more thing that we need to include in here. That is the format.
05:11
And that's CSV. So we want to make sure that's a capital C in there as well. OK, let's give that a refresh. Head over to mail pit.
05:18
And there we go. We now have a December export attachment. Now, if I click on this, you can see at the moment it's completely empty.
05:25
So obviously not great. That's just because we didn't map everything up. So over in our referral export here, this is where we want to choose what columns we want to include.
05:35
So the first thing from this row, which I'm actually going to call payout, that kind of makes sense, we are going to grab the PayPal email that we extracted earlier when we did this.
05:48
So that's going to include that PayPal email. We then want the amount. So for now, I'm just going to go ahead and hard code this to, say, $10 or something.
05:57
And then we need the currency. So generally, for mass payouts within PayPal or most other systems, you need the email or some sort of identifier you're sending to.
06:06
The amount, not in cents. That's really, really important. So for example, if you were paying $10 out, you wouldn't want this to be 1,000 or 100 in this case,
06:15
because that's not going to make sense. You're going to pay out way too much money. And you usually need the currency. But you can add more here if you want to.
06:22
You can add messages to this, whatever you need to do. OK, let's just test again that this works. So let's head over to payout. And let's go over to our emails and check this out.
06:32
And sure enough, we now have a CSV with two rows with each of them email addresses in that we're paying out to based on the data we have in our database, $10 and USD.
06:43
Great. We now need to work out how to grab this amount. Now, let's think about the data that we've got. So if we go over to our generate referral payout,
06:52
this gives us, if we just die dump on it, we just die dump on records and get, this gives to us over here a collection of referral payment classes.
07:05
Now, we know that when we're building this up over in our referral payment model, we have that money property or amount property on here. So over in the database, we've got this amount.
07:17
Now, what we can do is a similar thing to what we did over in our other models, like our plan. And we can cast that amount to a money decimal cast, which means that we can easily format it,
07:28
even though we're working in cent. So we're going to go ahead and pull this in. And we're going to cast the amount using that money package.
07:36
Now, what that means is that when we get this data back here, it will still be an amount. So if we just check this out, it will still be an amount like 6,000.
07:47
But what we can do is access the records now and have that amount formatted. So over in our referral payout, down to our export just here, what we can now do is take the payout amount.
08:03
And we can format that by decimal. So that's just another method on that Laravel money package that we pulled in. So now this will take the cents amount.
08:12
It will format it for us by a decimal. It won't include the dollar sign, because we don't need that. We should be good.
08:19
OK, let's go over and give this a refresh again. And we get format by decimal and string. So where did we add that to? Let's go over to our referral payment,
08:27
which is what we actually wanted to add that one to. And I think we added that to our referral code, which doesn't make sense. OK, so it was just in the wrong place.
08:37
Let's pull this over to our actual referral payment. And now that will actually get set properly. Give that a refresh. And there we go.
08:44
Great. So moment of truth, we're going to go over to that referral payment, open this up, and there we go. We've got $60 US for Alex and $30 US for Mabel.
08:55
So there we go. We are now constructing this CSV. Of course, this is just opening up in the browser, but you could download it and open this up
09:02
in Excel or whatever. And there we go. We've got the data that we need. Now, of course, we can't forget, once that email
09:10
has been sent once a month, to make sure we pull back this update here. So what that's now going to mean is if we just close this off and we go over and refresh, that's
09:21
going to send that email, of course, but it's also going to mark them as paid. So if we now go back and delete all of these emails and I do this again, we're not going
09:31
to get another email because they've already been paid. OK, there we go. So we are now constructing out the CSV. We're passing the data down to this email, which, of course,
09:40
the email is responsible for constructing this attachment. Excel is responsible for building up our sheet with the referral export, making sure that we get the right data and we format it in the right way.
09:52
And of course, you can adjust this depending on what you need, but there we go. There is our email once per month with everything we need to pay out.

Episode summary

In this episode, we tackle how to generate a CSV file and attach it to an outgoing email using Laravel Excel. The goal is to send a monthly payout report via email with all the relevant referral payout data neatly formatted in a CSV for easy processing (e.g., in Excel or for direct import into PayPal).

We start by installing the Laravel Excel package and walking through the basics of how it works. You'll see how we can pass arrays, collections, or even Eloquent queries to build up the raw data for our CSV. We set up the necessary export class to control exactly what data ends up in the CSV, including formatting the amount field to properly display dollar values instead of raw cents.

Once the export is in place and mapped to output the right columns (like PayPal email, amount, and currency), we show how to wire this export up so it's generated on the fly and attached to the outgoing email—no file saved on disk required. We do a few rounds of testing, first with dummy data and then pulling in actual values from the database, making sure the fields are correct and appropriately formatted.

To wrap up, we talk through marking payouts as "paid" after the email sends, ensuring that users don't get duplicate payout notifications. In the end, you'll have a working system that emails out a CSV each month, formatted just how you need it, and smart enough not to double-send.

It's a really practical episode packed with steps you can follow directly. By the end, you'll have a re-usable pattern for generating and emailing CSV reports straight from your Laravel app.

Episode discussion

No comments, yet. Be the first!