Level 80
@ollie_123 Schedule two tasks? One that runs every month to process monthly invoices, and a second that runs every week to process weekly invoices.
Jul 14, 2021
7
Level 6
Scheduled Invoices With Kernal Command
Hi All
I hope you're all well.
I'm trying to create a command that creates and sends an invoice invoice based on the orders placed by a company on a weekly or monthly basis based on the payment type the company has been set at.
i.e payment_type_id => '3' is weekly i.e payment_type_id => '4' is monthly
For reference, there is the invoices & invoice_line_items tables
Which an invoice & its line items are created from an Order and order_line_items tables
So far i've got
//Get companies on monthly payments
$companies = DB::table('companies')
->join('orders', 'companies.id', '=', 'orders.company_id')
->where('companies.payment_type_id', '3')
->where('orders.invoice_id', NULL)
->get();
foreach($companies as $company)
{
foreach($company->orders as $order)
{
// Create Invoice Here
}
}
The bit i'm struggling to get my head round is how i can create all of the orders & order line items into one invoice & invoice line items as ideally i would like each order & line item to be under the same invoice reference.
i.e
invoices
id |company_incr_inv | description |.....
1 | 200 | PC Build. |.....
2 | 200 | PC Repair |.....
3 | 200 | PC Service |.....
invoice_line_items
id |invoice_id | item_name |.....
1 | 1 | Nvidia X. |.....
2 | 1 | Graphics Card |.....
3 | 1 | HDMI Input |.....
4 | 2 | Screen Repl |.....
4 | 3 | Something |.....
Thank you in advance.
Level 6
Hi @martinbean, thanks for your message. I’ve created the commands that will run weekly & monthly but thats not the part im stuck on.
The bit i'm struggling to get my head round is how i can create all of the orders & order line items into one invoice & invoice line items as ideally i would like each order & line item to be under the same invoice reference for each company.
Ultimately I’m trying to create an invoice & invoice line items for all orders placed for each company that month / week.
Level 80
@ollie_123 I’m not sure what the issue is? For each order, create a corresponding invoice. And for each order line item, create a corresponding invoice line item.
Level 6
For proforma customers i'm currently doing exactly as you described. An invoice for each order.
For monthly customers i'm trying to create a single invoice for all orders placed that month.
Level 9
What if you created a column in the Orders table that associates it with an Invoice defaulting to NULL. When an order is invoiced the Invoice_id is set in that column. For customers that pay one-order-one-invoice, the invoice can be created automatically when the order is placed, and for frequency-based billing (weekly, monthly, etc) you can have a scheduled task for each frequency that queries the database for Orders with a NULL invoice_id column, and creates an Invoice for each customer with that payment frequency that gets associated with all those NULL invoice_id orders, and then send the invoices as a separate task. That's the way I'd do it. Let your frequency-based-billing customers accumulate NULL invoice_id Orders, and at the end of that period, find them in the database and assign them a new single invoice right before you send it...
If you also wanted to make the object relationships more well-defined in the code as well, you could have two separate Invoice classes, one for the one-to-one Order Invoices, and one for the many-to-one Order Invoices, which might potentially help with templating the invoice out...
Level 6
Hi @cjholowatyj
Thanks for your message, i have got that where clause in my query i hadnt added it at time of op ->where('orders.invoice_id', NULL).
Currently for the proforma customers, an invoice is created at time of order completion and the orders table is updated with the invoice_id. For the monthly customers the order is just updated on the orders table to completed but no invoice is created.
The bit i'm trying to work out in the code is for the frequency based customers, how to actually put the orders all into one invoice because i cant do
$companies = DB::table('companies')
->join('orders', 'companies.id', '=', 'orders.company_id')
->where('companies.payment_type_id', '3')
->where('orders.invoice_id', NULL)
->first();
foreach($companies as $company)
{
foreach($company->orders as $order)
{
// As this will create an invoice for every order.
}
}
You make some good points though so i'll keep thinking to see if i can figure it out. Thank you.
Level 9
Actually you can use the above code... All you need to do is capture subsequent orders after the first one creates an invoice... Something like...
foreach($companies as $company){
$invoice=false;
foreach($company->orders as $order){
if(!$invoice){ $invoice = new Invoice(); }
$order->invoice_id = $invoice->id;
}
}
... unless I'm missing or forgetting something that is! haha
$invoice=false; resets the invoice variable in between companies in the loop, but not in between orders, which I think is what you need to make this work.
Please or to participate in this conversation.