kpi.module

<?php
/* hook_menu */
function sasa_kpi_menu()
{
    $items['admin/config/kpi']=array(
      'title'=>'Key Perfomance Parameters',
      'description'=>'Key perfomance parameters',
      'page callback'=>'drupal_get_form',
      'page arguments'=>array('kpi_filter_form'),
      'access callback' => 'user_access',
      'access arguments' => array('access kpi'),
      'type' => MENU_NORMAL_ITEM,
      'menu_name'=>'management', 
    );
  return $items;
}    
/* create a form so that the user will be able to filter out resulsts
//
$status article status eigther published or unplished
$start  and $end this are date range in time stamp
*/
function kpi_filter_form()
{
  $form = array();
  $form['kpi'] = array(
    '#title' => t('Key Perfomance Idicators'),
    '#description' => t('Flter out the perfomance range that you want'),
    '#weight' => '0',
    '#type' => 'fieldset',
    '#collapsible' => '1',
    '#collapsed' => '0',
    );
  // get VID of counties from vocabularies
  $vid=db_query('SELECT vid FROM {taxonomy_vocabulary} WHERE machine_name = :name', array(':name' =>'kenya_counties'))->fetchField();
// list all counties and store in arrays
  $kenya_counties=array();
  if ($terms = taxonomy_get_tree($vid)) 
      {
          foreach ($terms as $term) 
            {
                $kenya_counties[$term->tid]=$term->name;
            }
      }
  $form['kpi']['county'] = array(
    '#required' => '1',
    '#key_type_toggled' => '1',
    '#description' => t('Please select county to filter'),
    '#default_value' => 'active',
    '#weight' => '-10',
    '#type' => 'select',
    '#options'=>$kenya_counties,
    '#multiple_toggle' => '1',
    '#title' => t('County Name'),
  );
  $form['kpi']['status'] = array(
    '#required' => '1',
    '#key_type_toggled' => '1',
    '#description' => t('Please select article status'),
    '#weight' => '-10',
    '#type' => 'select',
    '#options'=>array('1'=>'Published','0'=>'Unpublished'),
    '#multiple_toggle' => '1',
    '#title' => t('Article Status'),
  );  
  $form['kpi']['start']= array(
    '#type' => 'date_popup',
    '#date_format' => 'm-d-Y',
    '#default_value' =>date("m-d-Y",date('U')),
    '#date_year_range' =>'-1:+0',
    '#date_label_position' => 'within',
    '#title' => t('Select starting date'),
    '#weight' => '-9',    
);
  $form['kpi']['end']= array(
    '#type' => 'date_popup',
    '#date_format' => 'm-d-Y',
    '#default_value' =>date("m-d-Y",date('U')),
    '#date_year_range' =>'-1:+0',
    '#date_label_position' => 'within',
    '#title' => t('Select ending date'),
    '#weight' => '-8',    
  );
  $form['search']['submit'] = array(
    '#type' => 'submit',
    '#weight' => '-7',
    '#value' => t('Search')
  );
return $form;
}
function kpi_filter_form_submit($form, &$form_state)
  {

      drupal_set_message(t("Number of submitted articles %c",array('%c'=>get_submitted_articles($form_state['values']['start'],$form_state['values']['end'],$form_state['values']['county'],$form_state['values']['status']))));    
      drupal_set_message(t("Total new contributors %c",array('%c'=>get_unique_contributors($form_state['values']['start'],$form_state['values']['end'],$form_state['values']['county'],$form_state['values']['status']))));    
      drupal_set_message(t("Total contributors %c",array('%c'=>get_contributors($form_state['values']['start'],$form_state['values']['end'],$form_state['values']['county'],$form_state['values']['status']))));    

  }
/*
this functions returns the total number of submitted articles filterd by county and date range
*/     
function get_submitted_articles($from,$to,$county,$status)
  {

/* 
//this too works but it will throw an error if $from and $to dates are the same because of using 'BETWEEN' operator  


      $range=array(strtotime($from),strtotime($to));
        $query = new EntityFieldQuery();
        $query->entityCondition('entity_type', 'node')
          ->entityCondition('bundle', 'article')
          ->propertyCondition('status',$status)
          ->propertyCondition('created',$range,'BETWEEN')
          ->fieldCondition('field_article_county','tid',$county,'=');
        $v= $query->count()->execute();      
        drupal_set_message(t('Total submitted articles %v',array('%v'=>$v)));  
*/
    // build query
      $query= db_select('node','n')
      // filter only articles
      ->condition('n.type', 'article')
      // filter by the given date range, using BETWEEN throws an error, better using >$from and ,$to  
      ->condition('n.created',strtotime($to),'<')
      ->condition('n.created',strtotime($from),'>')
      // filter by article status
      ->condition('n.status',$status);
      // JOIN node table with county data attached to the node
      $query->join('field_data_field_article_county','c', 'c.entity_id=n.nid');
      // filter by article county 
      $query->condition('c.field_article_county_tid',$county);
      // count query
      $result=$query->countQuery()
      // run the query and return it
      ->execute()->fetchField();
      return $result;

  }
/*
this functions returns the total number of contributors filterd by county and date range
*/  
function get_contributors($from,$to,$county,$status)
  {

  $a=db_query('SELECT count (DISTINCT n.uid) as cn 
   /* select dinstinct UID from node*/ 
   FROM node AS n 
   /* ADD join to get the county which the node belongs */
   JOIN field_data_field_article_county AS c ON n.nid=c.entity_id 
   WHERE 
   /* filter by the given dates */
   n.created BETWEEN :start AND :end 
   AND
   /* select only node for thr given county */
    c.field_article_county_tid=:county
  AND
  /* SELECT ONLY NODES HAVING THE STATUS SAME AS THE GIVEN STATUS */  
   n.status=:status ',
  /* ARRAY OF ARGUMENTS */ 
   array(':start'=>strtotime($from),':end'=>strtotime($to),':county'=>$county,':status'=>$status));

  $count=0; // holds total contributors
      foreach ($a as $a) 
        {
           $count=$a->cn;   
        }
      return $count;

  }
  /*
this functions returns the total number of new users i.e (users who have posted only one article) filterd by county and date range
*/
function get_unique_contributors($from,$to,$county,$status) 
  {

      // build query
      $query= db_select('node','n')
      // a field must be selected due to having and group by condition
      ->fields('n',array('nid','uid'))
      // select only articles      
      ->condition('n.type', 'article')
      // filter by article status
      ->condition('n.status',$status)
      // without group by having condition wont apply, also group by the field (i.e uid) which shall be used in having condition
      ->groupBy('n.uid');
      // Count how many article each user has
      $query->addExpression('COUNT (n.uid)','ucount');
      // get how many users have written only one article
      $query->havingCondition('ucount',1);   
      // JOIN node table with county data attached to the node
      $query->join('field_data_field_article_county','c', 'c.entity_id=n.nid');
      // filter by article county 
      $query->condition('c.field_article_county_tid',$county);
      // filter by date created 
      $query->condition('n.created',strtotime($to),'<');
      $query->condition('n.created',strtotime($from),'>');
      // count query
      $result=$query->countQuery()
      // run the query and fetch result as single value and return it

      ->execute()->fetchField();
      return $result;
  }