1

In my system for pistol competition we have an invoice system. The problem is that one can choose one or more signups but not all for creating invoices but all signups are created. For example we have two signups, John Doe and Mary Doe and I choose only John Doe for invoice both are created in one invoice.

It looks like this: https://i.stack.imgur.com/QtaAy.jpg

There are some code in the beginning that takes care of the chosen signups but further down it's not taking care of.

                if($signupIds){
                        $query->whereIn('competitions_signups.id', $signupIds);
                    }   dump($signupIds);      //Only the chosen is dumped here.   ***COMMENT***

as here the dump:

 ------------ ---------------------------------------- 
  date         Thu, 26 May 2022 13:32:25 +0200         
  controller   "ClubInvoicesController"                
  source       InvoiceRepository.php on line 90        
  file         app/Repositories/InvoiceRepository.php  
 ------------ ---------------------------------------- 

array:1 [
  0 => "34"
]

The file is InvoiceRepository.php The php code:

    /** 
     * Collect all the clubs which to generate a invoice to.
     * The club must have one or more competitions that has signups where invoices_id is null.
     * Or the club must have one or more competitions that has teams where invoices_id is null.
     */
    public function createInvoicesForClub($club, $signupIds = null, $teamIds = null)
    {
        /**
         * Instanciate $invoices as new collection.
         */

        $invoices = new \Illuminate\Database\Eloquent\Collection;
        $query = Competition::where(function($query) use ($club, $signupIds, $teamIds){
            
            $query->where(function($query) use ($club, $signupIds, $teamIds){
                $query->whereHas('Signups', function($query) use ($club, $signupIds){
                    $query->where(function($query) use ($club) {
                        $query->whereNull('invoices_id');
                        $query->where('clubs_id', $club->id);
                    });
                    if($signupIds){
                        $query->whereIn('competitions_signups.id', $signupIds);
                    }   dump($signupIds);      //Only the chosen is dumped here.                              ***COMMENT***
                });
                $query->orWhereHas('Teams', function($query) use ($club){
                    $query->where(function($query) use ($club) {
                        $query->whereNull('invoices_id');
                        $query->where('clubs_id', $club->id);
                    });
                });
            });
        });
        $competitions = $query->get();

        $invoicesRecipientTypes = $competitions->groupBy('invoices_recipient_type');
        $invoicesRecipientTypes->each(function($invoiceRecipientType, $type) use($club, $invoices, $teamIds){
            $signupsGroupedBySender = $invoiceRecipientType->groupBy('invoices_recipient_id');
            $signupsGroupedBySender->each(function($signups, $index) use($club, $invoices, $type, $teamIds) {
                $signupsGroupedByCompetition = $signups->groupBy('competitions_id');
                $signupsGroupedByCompetition->each(function($item) use ($index, $club, $invoices, $type, $teamIds){
                    $sender = ($type == 'App\Models\District') ? District::find($index) : Club::find($index);
                    dump($sender);
                    /**
                     * Collect the signups to attach to the invoice.
                     */
                    $query = \App\Models\Signup::with('User','Competition', 'Weaponclass');
                    
                    $query->orderBy('competitions_id') ->  orderBy('users_id');
                    $query->where(function($query) use ($club, $sender, $type){
                        $query->whereNull('invoices_id');
                        $query->where('clubs_id', $club->id);
                        $query->whereHas('Competition', function($query) use ($sender, $type){
                            $query->where('invoices_recipient_type', $type);
                            $query->where('invoices_recipient_id', $sender->id);
                        });
                    });
                    $signups = $query->get();
                    dd($signups);  //All signups are here, including non-chosen                                           ***COMMENT***
                    if(!$signups->isEmpty()):
                        $invoice = new \App\Models\Invoice;
                        $invoice->created_by = \Auth::id();
                        $invoice->recipient_id = $club->id;
                        $invoice->recipient_address_city = $club->address_city;
                        $invoice->sender_id = $sender->id;
                        $invoice->sender_type = $type;
                        $invoice->sender_name = $sender->name;
                        $invoice_nr = \App\Models\Invoice::GenerateInvoiceNumber($type, $sender->id);
                        $invoice->invoice_nr = $invoice_nr;
                        $invoice->invoice_reference = $sender->id.date('Y').$invoice_nr;
                        $invoice->sender_swish = $sender->swish;
                        $invoice->save();

                        $sortorder = 0;

                            foreach($signups as $signup):
                            $sortorder++;
                            $invoice->expiration_date = (!$invoice->expiration_date || 
                            $invoice->expiration_date > $signup->Competition->signups_closing_date) ?  
                            $signup->Competition->signups_closing_date : $invoice->expiration_date;
                            $invoice->InvoiceRows()->create([
                                'description' => $signup->User->full_name.' '.$signup->Competition->name.' '
                                .$signup->Competition->date.' ('.(($signup->Competition->championships_id) ? 
                                $signup->Weaponclass->classname_general : $signup->Weaponclass->classname).')',
                                'quantity' => 1,
                                'unit' => _('st'),
                                'net_unit_amount' => $signup->registration_fee,
                                'vat_percent' => 0,
                                'vat_amount' => 0,
                                'row_net_amount' => 1 * $signup->registration_fee,
                                'row_vat_amount' => 0,
                                'row_sum_amount' => 1 * $signup->registration_fee,
                                'sortorder' => $sortorder
                            ]);
                            $invoice->Signups()->save($signup);
                        endforeach;
                        $amount = $invoice->InvoiceRows()->sum('row_sum_amount');
                        $invoice->amount = $amount;

                        $invoice->save();
                        $invoices->push($invoice);
 

                });
            });
        });

        return $invoices;
    }

How to modify to get it to work?

raffodog
  • 59
  • 7
  • `$query = \App\Models\Signup...->get()` will get all sign-ups. You need to restrict it using a `whereIn` as you did above. Side note, look at the `->when...` function to avoid if statements in queries. https://laravel.com/docs/9.x/queries#conditional-clauses – waterloomatt May 26 '22 at 12:30
  • Thank you. Well, I'm not skilled enough to clear this out. What I have seen from the dumps all included not only chosen are in the ```$signups = $query->get();``` statement. Is it possible to invoke something from ```$query->whereIn('competitions_signups.id', $signupIds);```there where ```$signupIds``` contains only chosen ones? – raffodog May 26 '22 at 13:51
  • Yes. You've done it already above. Just do the same in the last instance. – waterloomatt May 26 '22 at 15:47
  • @ waterloomatt Thanks, but I don't understand. What have I done already? I have now stripped down the code and know now that this statement ```$query = \App\Models\Signup::with('User','Competition', 'Weaponclass');```is collecting ALL competitors including not chosen into the array. So when this ```$signups = $query->get(); dump($signups); ``` happens I can see all competitors in the dump. I don't know how to use the earlier ```$signupIds``` to limit. – raffodog May 27 '22 at 08:53
  • @ waterloomatt If I try to use $signupIds further down I'm getting Undefined variable $signupIds. So I don't know have to get this to work. – raffodog May 30 '22 at 13:08
  • You have nested _each_ statements. You need to pass `$signups` to each one using `use (...)`. – waterloomatt May 30 '22 at 15:16
  • Solved. See below. Thanks. – raffodog May 31 '22 at 20:40

1 Answers1

1

I got the solution. This is the adjusted code part:

        /**
         * Instanciate $invoices as new collection.
         */
    $invoices = new \Illuminate\Database\Eloquent\Collection;
        $query = Competition::where(function($query) use ($club, $signupIds, $teamIds){
            $query->where(function($query) use ($club, $signupIds, $teamIds){
                $query->whereHas('Signups', function($query) use ($club, $signupIds){
                    $query->where(function($query) use ($club) {
                        $query->whereNull('invoices_id');
                        $query->where('clubs_id', $club->id);
                    });
                    if($signupIds){
                        $query->whereIn('competitions_signups.id', $signupIds);
                    }
                });
                $query->orWhereHas('Teams', function($query) use ($club){
                    $query->where(function($query) use ($club) {
                        $query->whereNull('invoices_id');
                        $query->where('clubs_id', $club->id);
                    });
                });
            });
        });
        $competitions = $query->get();
        $invoicesRecipientTypes = $competitions->groupBy('invoices_recipient_type');
        $invoicesRecipientTypes->each(function($invoiceRecipientType, $type) use($club, $invoices, $signupIds, $teamIds){
            $signupsGroupedBySender = $invoiceRecipientType->groupBy('invoices_recipient_id');
            $signupsGroupedBySender->each(function($signups, $index) use($club, $invoices, $type, $signupIds, $teamIds) {
                $signupsGroupedByCompetition = $signups->groupBy('competitions_id');
                $signupsGroupedByCompetition->each(function($item) use ($index, $club, $invoices, $type, $signupIds,  $teamIds){
                    $sender = ($type == 'App\Models\District') ? District::find($index) : Club::find($index);

                    /**
                     * Collect the signups to attach to the invoice.
                     */
                    if($signupIds){
                    $query = \App\Models\Signup::with('User','Competition', 'Weaponclass');
                    $query->whereIn('competitions_signups.id', $signupIds);
                    $query->orderBy('competitions_id') ->  orderBy('users_id');
                    $query->where(function($query) use ($club, $sender, $type){
                        $query->whereNull('invoices_id');
                        $query->where('clubs_id', $club->id);
                        $query->whereHas('Competition', function($query) use ($sender, $type){
                            $query->where('invoices_recipient_type', $type);
                            $query->where('invoices_recipient_id', $sender->id);
                        });
                    });
                    $signups = $query->get();
                } else {
                    $signups = null;
                }

                    if(!$signups->isEmpty()):
                        $invoice = new \App\Models\Invoice;
                        $invoice->created_by = \Auth::id();
.
.

The $signupIds, were missing in three rows in the beginning and I had to do an if($signupIds){ around the $querys that collect the signups. So now only the chosen ones are collected for the invoice. And it works like a charm. Thank you all for tips!

raffodog
  • 59
  • 7