Tax Years custom WordPress Query

At work we’ve been using a custom accounting system based on WordPress for quite a while now. And since I liked the idea of having a go at it myself I decided to do so. The result is a nifty little setup I now use to write invoices, project proposals and log expenses.

This way I managed to make end with tedious numbers of InDesign templates and can keep all affairs in one central system. I also taught it to take in my full-time earnings via pay-slips and give me an estimated income tax and national insurance contributions overview. So now I can always check what to expect from my tax return and make sure I put enough aside.

However whilst programming my little office I encountered a problem (well, not just one but this I found particular interesting). Giving the nature of the site I had to organise and query entries not just by the calendar year – but the tax year. Getting entries using the standard WordPress tools such as get_posts() or altering the main query were of course very straight forward thanks to the date_query parameters. If I wanted to know the current tax year or see if I had entries for a given tax year however things quickly became quite inefficient having to run too many extra queries.

Direct database queries haven’t really been my strong point in the past but I am getting to love them more and more and so I decided to give it a go. I decided to use the $wpdb Class to make a database request and ask for all tax years with entries of a given post_type and the according count. This is what I came up with:

function get_tax_years() {
  global $wpdb;
  $query = "SELECT ".
    "count( ID ) as `count`, ".
    "IF( ".
      "CONCAT(DATE( post_date )) >= CONCAT_WS( '-',YEAR(post_date),'04','06'), ".
      "CONCAT_WS('-',YEAR(post_date),YEAR(post_date)+1), ".
      "CONCAT_WS('-',YEAR(post_date)-1,YEAR(post_date)) ".
    ") AS `tax_year` ".
    "FROM $wpdb->posts ".
    "WHERE ".
      "post_type = 'post_type' ".
      "AND post_status = 'publish' ".
    "GROUP BY ".
      "`tax_year`".
    "ORDER BY post_date DESC";
  $results = $wpdb->get_results( $query );
  return $results;
}

And it returns an array with an object for each tax year with entries and the corresponding count. Just like so:

array(3) {
  [0]=>
  object(stdClass)#438 (2) {
    ["count"]=>
    string(1) "8"
    ["tax_year"]=>
    string(9) "2014-2015"
  }
  [1]=>
  object(stdClass)#439 (2) {
    ["count"]=>
    string(1) "5"
    ["tax_year"]=>
    string(9) "2013-2014"
  }
  [2]=>
  object(stdClass)#442 (2) {
    ["count"]=>
    string(2) "10"
    ["tax_year"]=>
    string(9) "2012-2013"
  }
}

For me this one extra query seemed the most efficient way to get to that information. You could from there of course further save or cache it to avoid needless repetition.

I also wanted a simple function to quickly return the current tax year and came up with this little helper:

function get_current_tax_year( $the_date = false , $return = 'array' ) {
  if( !isset( $the_date ) || empty( $the_date ) )
    $the_date = current_time( 'timestamp' );
  $tax_year = array();
  if( mktime( 0, 0, 0, 04, 06, date('Y',$the_date) ) <= $the_date ) {
    $diff = 1;
  }else{
    $diff = -1;
  } 
  $tax_year[] = (int) date('Y',$the_date);
  $tax_year[] = (int) date('Y',$the_date ) + $diff;
  sort( $tax_year );
  if( $return == 'string' )
    return implode( '-', $tax_year );
  $tax_year = array(
    'start' => $tax_year[0],
    'end' => $tax_year[1]
  );
  return $tax_year;
}

If you replaced WordPress’ own current_time() function for a standard PHP time() you could also use this outside of WordPress.

This is of course no definitive guide as I am sure there is other, possibly better, ways to achieve this. However as get_tax_years() in particular gave me some headaches at first I though I’d shared it in case someone comes across it struggling with a similar scenario. Feel free to use this as you wish or leave me a comment with some feedback. I always appreciate pointers on how to improve these things.