Forum for discussing general topics related to Couch.
8 posts Page 1 of 1
Hi guys!

I have the following situation:
1. For one specific service there can be more invoices issued;
2. Each of this invoices can be payed with partial payments (multiple payments/invoice)

I needed to make some calculations in a report for invoice value, payed amount/invoice, total amount due/service (filter done in masterpage services.php ) , and came up with this solution, which works ok:
Code: Select all
<cms:pages masterpage='members/services.php' custom_field=my_search_str order='asc'>

<cms:set totalinvoiced='0' scope='global' />
<cms:pages masterpage='members/invoices.php' custom_field="idfolder=id(<cms:show k_page_id />)" show_future_entries='1'>
   <cms:set totalinvoiced="<cms:add totalinvoiced invoicevalue />" scope='global' />
</cms:pages>
               
<cms:set totalpayed='0' scope='global' />
<cms:pages masterpage='members/invoices.php' custom_field="idfolder=id(<cms:show k_page_id />)" show_future_entries='1'>
   <cms:reverse_related_pages 'idinvoice' masterpage='members/payments.php' >
       <cms:set totalpayed="<cms:add totalpayed payedamount />" scope='global' />
      </cms:reverse_related_pages>
</cms:pages>
                                             
  <cms:pages masterpage='members/invoices.php' custom_field="idfolder=id(<cms:show k_page_id />)" show_future_entries='1' order='asc'>
    <tr>
       <td><cms:show customername/></td>
        <td><cms:show service /></td>
      <td><cms:show invoicenr/></td>
      <td><cms:date k_page_date format='d.m.Y' /></td>
      <td><cms:show invoicevalue/></td>
      <td>
           <cms:set totalpayedfrominvoice='0' scope='global' />
           <cms:reverse_related_pages 'idinvoice' masterpage='members/payments.php' >
               <cms:set totalpayedfrominvoice="<cms:add totalpayedfrominvoice payedamount />" scope='global' />
            </cms:reverse_related_pages>
           <cms:show totalpayedfrominvoice/>
        </td>
      <td><cms:sub invoicevalue totalpayedfrominvoice /></td>
        <td><cms:sub totalinvoiced totalpayed /></td>
   </tr>
  </cms:pages>
 
</cms:pages>


Because one of the requirement is to filter data by customer name, which is present in invoices.php only, I should filter the data using invoices.php as masterpage and using as criteria both by my_search_str and page_id as custom_field. But I just think this is not possibe.
Is there any solution with this approach or should I look in another direction?
Any help is appreciated.
If you need to filter by person, you may consider storing “people” as records in a people.php clonable template and relating those people to invoices via relation editable. Same with services to invoices.

With people and services related to the invoices, we can filter via reverse_related_pages, using a custom_field. Maybe this would help?
Code: Select all
<cms:pages masterpage=“people.php” id=my_person>
  <cms:reverse_related_pages masterpage=“invoices.php” field=“person” custom_field=“service=my_service”>
    # Invoices related to person and service custom_field
  </cms:reverse_related_pages>
</cms:pages>


Also, I think you can use cms:incr (instead of cms:add) and combine the setting of totalinvoiced and totalpayed within the same services.php loop to increase legibility (probably lighten the SQL load a little too) :)
Code: Select all
<cms:pages masterpage='members/services.php' custom_field=my_search_str order='asc'>

   <cms:set totalinvoiced='0' scope='global' />
   <cms:set totalpayed='0' scope='global' />

   <cms:pages masterpage='members/invoices.php' custom_field="idfolder=id(<cms:show k_page_id />)" show_future_entries='1'>
      <cms:incr totalinvoiced invoicevalue />
      <cms:reverse_related_pages 'idinvoice' masterpage='members/payments.php' >
         <cms:incr totalpayed payedamount />
      </cms:reverse_related_pages>
   </cms:pages>
            
   <cms:pages masterpage='members/invoices.php' custom_field="idfolder=id(<cms:show k_page_id />)" show_future_entries='1' order='asc'>
      <tr>
         <td><cms:show customername/></td>
         <td><cms:show service /></td>
         <td><cms:show invoicenr/></td>
         <td><cms:date k_page_date format='d.m.Y' /></td>
         <td><cms:show invoicevalue/></td>
         <td>
            <cms:set totalpayedfrominvoice='0' scope='global' />
            <cms:reverse_related_pages 'idinvoice' masterpage='members/payments.php' >
               <cms:incr totalpayedfrominvoice payedamount />
            </cms:reverse_related_pages>
            <cms:show totalpayedfrominvoice/>
         </td>
         <td><cms:sub invoicevalue totalpayedfrominvoice /></td>
         <td><cms:sub totalinvoiced totalpayed /></td>
      </tr>
   </cms:pages>

</cms:pages>
Also, can you explain what’s going on with “idfolder=id(<cms:show k_page_id />)” inside custom_field? I’ve never seen custom_field used like that before. :ugeek:
@mwlarkin1
can you explain what’s going on with “idfolder=id(<cms:show k_page_id />)” inside custom_field? I’ve never seen custom_field used like that before.

Please see the discussion under the head "2. Enhanced cms:pages tag" on viewtopic.php?f=5&t=8581
That is basically another (more powerful) way of dealing with 'related pages'.

Hope this answers your query.
mwlarkin1 wrote: Also, can you explain what’s going on with “idfolder=id(<cms:show k_page_id />)” inside custom_field? I’ve never seen custom_field used like that before. :ugeek:

Right after I saw what you asked, I realized the solution was so simple! I didn't knew how to use inside the pages tag the needed filtering options, but because
Code: Select all
<cms:pages masterpage='members/invoices.php' custom_field="idfolder=id(<cms:show k_page_id />)" show_future_entries='1' order='asc'>

equals
Code: Select all
<cms:reverse_related_pages 'idfolder' masterpage='members/invoices.php' order='asc'  >
I just realised I could use the filtering params here, in the last exemple.
I also realized that some calculations and informations are not needed in my report, so my code has become:
Code: Select all
<cms:pages masterpage='members/services.php' order='asc'>
<cms:set totalinvoiced='0' scope='global' />
<cms:pages masterpage='members/invoices.php' custom_field="idfolder=id(<cms:show k_page_id />)" show_future_entries='1'>
   <cms:set totalinvoiced="<cms:add totalinvoiced invoicevalue />" scope='global' />
</cms:pages>   
<cms:set totalpayed='0' scope='global' />
<cms:pages masterpage='members/invoices.php' custom_field="idfolder=id(<cms:show k_page_id />)" show_future_entries='1'>
   <cms:reverse_related_pages 'idinvoice' masterpage='members/payments.php' >
       <cms:set totalpayed="<cms:add totalpayed payedamount />" scope='global' />
      </cms:reverse_related_pages>
</cms:pages>
<cms:reverse_related_pages 'idfolder' masterpage='members/invoices.php' custom_field=my_search_str order='asc' paginate='1' limit='1000' start_on=start_date stop_before=end_date >
    <tr>
        <td><cms:show service /></td>
        <td><cms:show totalinvoiced/></td>
      <td><cms:show totalpayed/></td>
        <td><cms:sub totalinvoiced totalpayed /></td>
   </tr>
</cms:reverse_related_pages>
</cms:pages>
But now I'm faceing another problem. In my first attempt I displayed detailed informations about each invoice and its payment, so it was normal for service name to repeat for each invoice/same service.
Now, because I decided to show in my report only total amount invoiced, total amount payed and total amount to be payed, being in the reverse_related_pages loop, the informations are repeated as many times as the number of invoices issued for that service.
My question is what should I use to display the informations/service only once, whithout reapeating them (because there are many invoices/same service in some cases)?

Thank you.
If I’m understanding correctly, you have three templates: services.php —> invoices.php —> payments.php. You want to loop through those services, present basic info about them, and within each one also display its relative total invoice amounts.

If that’s true, you need to stay within the scope of the service and run any invoice/payment math related to that service before outputting. I’d do something like the following (note: keeping template/variable names minimal for brevity):

Code: Select all

<cms:pages masterpage=‘services.php’>

    <cms:set service_invoices_total = ‘0’ />
    <cms:set service_invoices_total_paid = ‘0’ />
    <cms:set service_invoices_total_outstanding = ‘0’ />

    <cms:related_pages ‘invoices’>

        <cms:set this_invoice_total_paid = ‘0’ />
        <cms:reverse_related_pages masterpage=‘payments.php’ field=‘invoice’>
            <cms:incr this_invoice_total_paid payment_amount />
        </cms:reverse_related_pages>

        <cms:incr service_invoices_total invoice_amount />
        <cms:incr service_invoices_total_paid this_invoice_total_paid />
        <cms:set service_invoices_total_outstanding = “<cms:sub service_invoices_total service_invoices_total_paid />” />

    </cms:related_pages>

    <cms:show service />

    <cms:show service_invoices_total />
    <cms:show service_invoices_total_paid />
    <cms:show service_invoices_total_outstanding />

</cms:pages>



Hope that helps.
@mwlarkin1, it is a clean solution.
Just one little change, I think, is required - the following statement will require 'scope' else the variable will be available only within the enclosing <cms:related_pages> block (more on this issue - viewtopic.php?f=4&t=9173&p=36779#p36779)
Code: Select all
<cms:set service_invoices_total_outstanding = “<cms:sub service_invoices_total service_invoices_total_paid />” scope='parent' />

I had discussion with @atisz over PM and he is trying to do the calculations per 'service provider' where further the invoices are limited to a particular time-period ('my_search_str' in his code contains the name of the provider being searched for and 'start_date' / 'end_date' delimit the time period).

So the relation goes something like this -
Service provider -> Services -> Invoices -> Payments

He does have a separate clonable template for the providers but has chosen not to 'relate' that template with services; instead he stores the names of the providers in a custom field of services. While, as @mwlarkin1 suggested earlier, the best way would be to create a relation between providers and services, going with the current setup we can do the filtering as follows -
Code: Select all
<cms:pages masterpage=‘services.php’ custom_field=my_search_str>
    <cms:related_pages ‘invoices’ start_on=start_date stop_before=end_date >
        ..
    </cms:related_pages>
</cms:pages>

@atisz, as you can see, we first filter the services based on the selected provider and then filter the invoices fetched for these services by the time period.

Hope this helps.
Thank you @KK, thank you @mwlarkin1!
It works great! :D
8 posts Page 1 of 1