Take the 2-minute tour ×
Drupal Answers is a question and answer site for Drupal developers and administrators. It's 100% free, no registration required.

I have an application that records properties as nodes and relates property nodes to charge nodes and payment nodes.

I would like to add the ability to view a report by selecting a property and specifying two dates, a start date and an end date for the report, that shows a total of payments, a total of charges and then a balance total for a particular property.

However, the charges content type is only used to record additional charges to a property. There is a monthly service charge which is stored as an amount in a field of the property node. So when generating the report, I simply add one service charge payment for each month the report spans.

A summary of the report:

Select a property and give a date range. Produce a list of charges by :

  1. Adding one service charge for each month between the two dates. (Note: The service charge amount is given in a field of the property node)
  2. Adding amount from all charge nodes related to this property.
  3. Give a total field for all the charges.
  4. Show all payments from payment nodes related to this property.
  5. Total the payments.
  6. Give a balance total(charge total - payment total)

What I can't figure out is if views can build the report I need.

Can this be done by adding calculated fields to views, in particular the service charge payments, or do I have to do this in a template file (views-view--foobar--page.tpl.php for example)?

I know how to get all payments and charges related to a property into a view, but I am not sure where to go from there. Maybe this comes down to personal preference, but this is my first venture into views and I would appreciate some advice on where I should be concentrating my efforts!

share|improve this question

3 Answers 3

Would this be a suitable case for using the Views Calc module?

Example image from module page:

enter image description here

share|improve this answer
    
Thanks for the link. Playing with this for a little it seems to not like summing a column of values brought in through an entity reference. In particular, the view is built on a property node and brings in values from an amounts field in a second content type, charges. When I set this column to calculate the sum, the calculated field is empty. Also, I have yet to see how I can generate a column that repeats the service payment value filed from the property node several times and then gives a total. I will keep playing with it and let you know. –  NectarSoft Feb 26 at 17:01

Another Module you can consider is the views summarize

Enables an extra display style which displays summaries of a column on the last row.

enter image description here You can also use the views calc mentioned by @Felix together with the views aggregator module

share|improve this answer
    
Thanks, using views summarise I could get the entity reference values to sum, however I finally went another route. –  NectarSoft Feb 27 at 20:14

It seems that views is not a good way to go when trying to build reports that require more than one query of the database. Problems I found were:

-- 1) I could not bring in all payment nodes to the view using a property node id and then call in all charge nodes for the same property. The query returned the same information into the view multiple times, once for each payment and once for each charge. I wanted separate tables and then I wanted to make total fields from the data in each table. It seems a solution to this is to use hook_views_pre_execute() to edit the query to create a union query as described here but this was honestly mostly over my head!

-- 2) I could not calculate fields base on a value from a field in the property node and add them to the view (the service charge I talk about in the question).

The help offered in the previous two answers solved the problem of creating a sum of a column in a views table, but didn't allow me to add a computed field of my own based on data from the view. For example, take two summed fields, subtract one from the other and put the result into a custom field. Views' custom field would be the way to do this as it allows you to use php snippets to build the field value, although again I am not sure it would allow you access to other custom fields in the view (just the view results).

Furthermore, I would then have to format the results by a call to some template hook function (sorry, I haven't figure out which one yet!).

So instead my solution was to use hook_menu() to add a path to create my own report

  $items['property_account_summary'] = array(
    'title' => 'Property account summary',
    'page callback' => 'MYmodule_property_account_summary_view',
    'page arguments' => array(1),
    'access callback' => 'user_access',
    'access arguments' => array('List Charge by Prop'),

and in MYmodule_property_account_summary_view it was pretty straight forward to construct the two queries I needed to collect the payment and charge nodes I needed. For example, for payments (where $prop_nid contains the node id of the property node I am working from:

  $query = new EntityFieldQuery();
  $query->entityCondition('entity_type', 'node')
     ->entityCondition('bundle', 'payment');
  $query->fieldCondition('field_payment_property', 'target_id', $prop_nid, '=');
  $result = $query->execute();
  $node_list = $result['node'];

and for charges:

  $query = new EntityFieldQuery();
  $query->entityCondition('entity_type', 'node')
     ->entityCondition('bundle', 'charge');
  $query->fieldCondition('field_charge_property_id', 'target_id', $prop_nid, '=');
  $result = $query->execute();
  $node_list = $result['node'];

and from there build the html and add the totals.

Payment and charge summary Now I just need to format the values and add links to the term values in the Type column.

It just seems like a lot less work than trying to force views into doing what I needed. I must reiterate that this is my first attempt at using views (other than the straight forward grab and show data) and maybe I missed something amongst the views hooks that would have made my life a lot easier. But for now, I will use a solution within my module.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.