<?php

/**
 * @version    CVS: 1.0.0
 * @package    Com_Mmap
 * @author     David <davidgeorgespam@gmail.com>
 * @copyright  2017 David
 * @license    GNU General Public License version 2 or later; see LICENSE.txt
 */
// No direct access.
defined('_JEXEC') or die;

jimport('joomla.application.component.modelitem');
jimport('joomla.event.dispatcher');

use Joomla\Utilities\ArrayHelper;

/**
 * Mmap model.
 *
 * @since  1.6
 */
class MmapModelCompare extends JModelItem
{
	public $paired_patient_visits = true;
	public $v1_total = 0;
	public $v2_total = 0;
	
	public $v1_total_general = 0;
	public $v2_total_general = 0;
	
	public $total_55_responses = 1;
	public $total_57_responses = 1;
	private $where_filter; 
	public $debug = false; 
	
	public $total_obj; 
	
	/**
	 * Method to auto-populate the model state.
	 *
	 * Note. Calling getState in this method will result in recursion.
	 *
	 * @return void
	 *
	 * @since    1.6
	 *
	 */
	protected function populateState()
	{
		$app  = JFactory::getApplication('com_mmap');
		$user = JFactory::getUser();

		// Check published state
		if ((!$user->authorise('core.edit.state', 'com_mmap')) && (!$user->authorise('core.edit', 'com_mmap')))
		{
			$this->setState('filter.published', 1);
			$this->setState('filter.archived', 2);
		}

		// Load state from the request userState on edit or from the passed variable on default
		if (JFactory::getApplication()->input->get('layout') == 'edit')
		{
			$id = JFactory::getApplication()->getUserState('com_mmap.edit.mmap.id');
		}
		else
		{
			$id = JFactory::getApplication()->input->get('id');
			JFactory::getApplication()->setUserState('com_mmap.edit.mmap.id', $id);
		}

		$this->setState('mmap.id', $id);

		// Load the parameters.
		$params       = $app->getParams();
		$params_array = $params->toArray();

		if (isset($params_array['item_id']))
		{
			$this->setState('mmap.id', $params_array['item_id']);
		}

		$this->setState('params', $params);
	}

	/**
	 * Method to get an object.
	 *
	 * @param   integer $id The id of the object to get.
	 *
	 * @return  mixed    Object on success, false on failure.
	 * 
	 * 
	 * 
	 * 
	 * 
	 */
	public function &getData($id = null)
	{
		$this->_item = new stdClass; 
		$this->_item->metabolics = $this->metabolicsReport();
		//$this->_item->meta = $this->metabolicsReport(18); // Metabolic Syndrome by 
		//$this->_item->male = $this->metabolicsReport(15); // Male 
		//$this->_item->female = $this->metabolicsReport(16); // Female
		
		//$this->_item->param = $this->parameterReport($query_id = null, $query_filter = null);
		
		/** 
		$cats = array(39, 32, 31, 30); // BMI Cats 
		$this->_item->bmi['Total'] = $this->metabolicsReport(null, null, $cats);
		$this->_item->bmi['Med Switch'] = $this->metabolicsReport(23, null, $cats);
		$this->_item->bmi['Lifestyle Only'] = $this->metabolicsReport(33, null, $cats);     
		*/
		
		//$this->_item->meta = $this->parameterReport(18, $query_filter = 43);
		
		
		/*
		
		$this->_item->hasmetabolic = $this->metabolicsReport(18); // Metabolics 
		$this->_item->medswitch = $this->metabolicsReport(23);  // Had medication switch
		//->_item->lifestyle = $this->metabolicsReport(33);  // Had lifestyle Only  
		*/
		/**
		$meds = array(0, 18, 1, 4, 5, 6, 7, 8, 9, 10, 11, 30, 31, 24, 23); 
		$visits = array(1,2); 
		
		foreach ($visits as $visit) {
		    foreach ($meds as $med) {
		        $this->_item->medications[] = $this->medicationsReportCur($med, $visit); 
    		}
		}	
		*/
		
		$this->_item->medications_table = $this->medicationsReport(); 
		
		$this->_item->users = $this->userReport(); 
		$this->_item->survey = $this->surveyReport(); 
		
		
		
		//$this->_item->v1_total = $this->v1_total; 
		//$this->_item->v2_total = $this->v2_total; 
		
		// INVERSE THE QUERY 
		
		$this->_item->compare = $this->comparisonQuery(); 
		
        return $this->_item;
	}
	
	
	public function comparisonQuery($id = null) {
	    $db    = JFactory::getDbo();
	    
	    //$field = 'fasting_plasma_glucose';
	    //$field = 'waist_circumference';
	    //$field = 'bmi'; 
	    //$field = 'triglycerides'; 
	    //$field = 'hdl_cholesterol'; 
	    //$field = 'systolic'; 
	    //$field = 'diastolic'; 
	    
	    $field = 'weight'; 
	    
	    
	    
	    $this->debug = true; 
	    
	   	$query = $db->getQuery(true);
    	$query->select('DISTINCT(p.id), p.gender');
    	$query->select('visit1.' . $db->quoteName($field) . ' as ' . $db->escape($field) . '_1, visit2.' . $db->quoteName($field) . ' as ' . $db->escape($field) . '_2'); 
    	$query->select('p.new_medication,
            p.switch_antipsychotic_medication as intervention_switch,
            p.adjust_dose_of_current_antipsychotic_medication as intervention_dose,
            p.lifestyle_intervention as intervention_lifestyle,
            p.commence_other_specific_therapy as intervention_medical,
            p.other as intervention_other,
            p.`time_until_next_review`');
    	
    

        $query->from($db->quoteName('patients', 'p'));
        $query->join('LEFT', $db->quoteName('users', 'u') . ' ON u.id = p.user_id');
        $query->join('LEFT', '(SELECT pe1.*
            FROM `pt_entry` AS `pe1` 
            LEFT JOIN `patients` AS `p1` ON p1.id = pe1.patient_id 
            LEFT JOIN `users` AS `u1` ON u1.id = p1.user_id 
            LEFT JOIN `surveys_data_55` AS `s551` ON s551.user_id = u1.id 
            LEFT JOIN `surveys_data_57` AS `s571` ON s571.user_id = u1.id 
            LEFT JOIN medications as m1 ON m1.pt_entry_id = pe1.id 
            LEFT JOIN medications_map as mm1 ON mm1.id = m1.med_id 
            LEFT JOIN medications_class as mc1 ON mm1.class_id = mc1.id 
            WHERE visit = 1
            GROUP BY p1.id) as visit1 ON visit1.patient_id = p.id');


        $query->join('LEFT', '(SELECT pe2.*
            FROM `pt_entry` AS `pe2` 
            LEFT JOIN `patients` AS `p2` ON p2.id = pe2.patient_id 
            LEFT JOIN `users` AS `u2` ON u2.id = p2.user_id 
            LEFT JOIN `surveys_data_55` AS `s552` ON s552.user_id = u2.id 
            LEFT JOIN `surveys_data_57` AS `s572` ON s572.user_id = u2.id 
            LEFT JOIN medications as m2 ON m2.pt_entry_id = pe2.id 
            LEFT JOIN medications_map as mm2 ON mm2.id = m2.med_id 
            LEFT JOIN medications_class as mc2 ON mm2.class_id = mc2.id 
            WHERE visit = 2
            GROUP BY p2.id) as visit2 ON visit2.patient_id = p.id'); 

        $query->where('visit1.' . $db->quoteName($field) . ' IS NOT NULL');  
        $query->where($this->whereRemove());  

        $query->group('p.id'); 
        $query->order('p.id');
        $db->setQuery($query);
    	
    	if($this->debug) {
    	    echo __FUNCTION__; 
    	    echo $query->dump();     
    	    return false; 
    	} else {
    	    return $items = $db->loadObjectList();     
    	}
    	
	}
	
	
	public function userReport() {
	    $return = new stdClass; 
	    
	    $db    = JFactory::getDbo();
	    
	    $visits = array(1,2); 
	    
	    foreach ($visits as $visit) {
        	
        	$query = $db->getQuery(true);
        	$query->select('count(*) as n');
        	$query->from($db->quoteName('users', 'u'));
        	$query->where($this->whereRemove());  
        	$db->setQuery($query);
        	
        	if($this->debug) {
        	    echo __FUNCTION__; 
        	    echo $query->dump();     
        	}
            
            $return->total_users[$visit] = $db->loadObject()->n;
            
            $query = $db->getQuery(true);
        	$query->select('count(*) as n, address_state as value, (count(*) / ' . $return->total_users[$visit] . ') as percent');
        	$query->from($db->quoteName('users', 'u'));
        	$query->where($this->whereRemove());  
        	$query->group('u.address_state');  
        	$query->order('count(*) DESC'); 
        	$db->setQuery($query);
        	
        	if($this->debug) {
        	    echo __FUNCTION__; 
        	    echo $query->dump();     
        	}
            
            $return->total_users_state[$visit] = $db->loadObjectList();
            
        	$query = $db->getQuery(true);
        	$query->select("DISTINCT(u.id), u.name, count(*) as n FROM `users` as u 
                LEFT JOIN patients as p ON p.user_id = u.id 
                LEFT JOIN pt_entry as pe ON pe.patient_id = p.id");
            $query->where("pe.visit = " . $db->quote($visit));  
            $query->where($this->whereRemove());  
        	$db->setQuery($query);
            
            $return->patients_visit[$visit] = $db->loadObject()->n;
            
            $query = "SELECT name, count(*) as total, avg(n) as avg, std(n) as std, min(n) as min, max(n) as max
    FROM users 
    RIGHT JOIN (
    SELECT u.id, count(*) as n FROM `users` as u 
    LEFT JOIN patients as p ON p.user_id = u.id 
    LEFT JOIN pt_entry as pe ON pe.patient_id = p.id 
    WHERE pe.visit = " . $db->quote($visit) . "
    GROUP BY u.id ) as ug on ug.id = users.id
    WHERE  users.email NOT LIKE '%@servier%' AND users.email NOT LIKE '%@allori%' AND users.aphra_number LIKE 'MED%'"; 
    
            $db->setQuery($query);
            
            $item = $db->loadObject(); 
            
            $return->patientst_n[$visit] = $item->total;
            $return->patients_avg[$visit] = $item->avg;
            $return->patients_std[$visit] = $item->std;
            $return->patients_min[$visit] = $item->min;
            $return->patients_max[$visit] = $item->max;
    
    $query = "SELECT users.id, name, count(*) as total, avg(n) as avg, std(n) as std, min(n) as min, max(n) as max
    FROM users 
    RIGHT JOIN (
    SELECT count(*) as n, u.id FROM `users` as u 
    LEFT JOIN patients as p on p.user_id = u.id 
    LEFT JOIN pt_entry as pe on pe.patient_id = p.id
    WHERE pe.visit = " . $db->quote($visit) . "
    GROUP BY u.id 
    HAVING 
    count(*) >=5 ) as ug on ug.id = users.id
    WHERE  users.email NOT LIKE '%@servier%' AND users.email NOT LIKE '%@allori%' AND users.aphra_number LIKE 'MED%'"; 
    $db->setQuery($query);
    
            $item = $db->loadObject(); 
            $return->five_plus_pt[$visit] = $item->total;
    
	    }
	    
	 return $return; 
	 
	}

	
    public function surveyReport() {
        $return = new stdClass; 
        
        $db    = JFactory::getDbo();
        $settings = array('total', 'public', 'private', 'approx. 50% in public and 50% private'); 
        $patiens = array('Community', 'Inpatients', 'Other'); 
        
        foreach($settings as $setting) {
            
            
            if($setting != 'total') {
                $query->where('setting LIKE ' . $db->quote($setting)); 
            }
        	
        	$query = $db->getQuery(true);
        	$query->select('count(*) as n, avg(`relevant`) as relevant, avg(`learning_effective`) as learning_effective, avg(`audit_useful`) as audit_useful, avg(`likely_to_implement`) as likely_to_implement');
        	$query->from($db->quoteName('surveys_data_57', 's'));
        	$db->setQuery($query);
        	$item = $db->loadObject(); 
        	
        	$return->total_57_responses = $item->n;
        	$return->avg_relevant = $item->relevant;
        	$return->avg_learning_effective = $item->learning_effective;
        	$return->avg_audit_useful = $item->audit_useful;
        	$return->avg_likely_to_implement = $item->likely_to_implement;
        	
        	
        	$query = $db->getQuery(true);
        	$query->select('count(*) as n, (count(*) / ' . $return->total_57_responses . ') as percent');
        	$query->from($db->quoteName('surveys_data_57', 's'));
        	$query->where('relevant = 5');
        	$db->setQuery($query);
        	$return->relevant5 = $db->loadObject()->n;
        	
        	$query = $db->getQuery(true);
        	$query->select('count(*) as n');
        	$query->from($db->quoteName('surveys_data_57', 's'));
        	$query->where('learning_effective = 5');
        	$db->setQuery($query);
        	$return->learning_effective5 = $db->loadObject()->n;
        	
        	$query = $db->getQuery(true);
        	$query->select('count(*) as n');
        	$query->from($db->quoteName('surveys_data_57', 's'));
        	$query->where('audit_useful = 5');
        	$db->setQuery($query);
        	$return->audit_useful5 = $db->loadObject()->n;
        	
        	$query = $db->getQuery(true);
        	$query->select('count(*) as n');
        	$query->from($db->quoteName('surveys_data_57', 's'));
        	$query->where('likely_to_implement = 5');
        	$db->setQuery($query);
        	$return->likely_to_implement5 = $db->loadObject()->n;
        	
        	$query = $db->getQuery(true);
        	$query->select('count(*) as n');
        	$query->from($db->quoteName('surveys_data_55', 's'));
        	$db->setQuery($query);
        	$item = $db->loadObject(); 
        	$return->total_55_responses = $item->n;
        	
        	$this->total_55_responses = $return->total_55_responses; 
        	
        	$survey_55_items = array('metabolic_proportion','review_frequency', 'gender', 'years_of_practice', 'patients', 'setting'); 
        	
        	foreach($survey_55_items as $item) {
        	    $return->{$item} = $this->getSurvey55Items($item); 
        	    
        	    }
        	
        	$db    = JFactory::getDbo();
    	    $query = $db->getQuery(true);
        	$query->select('count(*) as n, (count(*) / ' . $this->total_55_responses . ') as percent'); 
        	$query->select('CONCAT(setting, "-", patients) as value');
        	$query->from($db->quoteName('surveys_data_55'));
        	$query->group('setting, patients');
        	$db->setQuery($query);
        	
        	if($this->debug) {
        	    echo __FUNCTION__; 
        	    echo $query->dump();     
        	}
        	
        	$return->setting_patients = $db->loadObjectList();
        	
        }
        
    	return $return; 
    }

	public function getSurvey55Items($item) {
	    $db    = JFactory::getDbo();
	    $query = $db->getQuery(true);
    	$query->select('count(*) as n, (count(*) / ' . $this->total_55_responses . ') as percent'); 
    	$query->select($db->quoteName($item) . ' as value');
    	$query->from($db->quoteName('surveys_data_55'));
    	$query->group($db->quoteName($item));
    	$db->setQuery($query);
    	
    	if($this->debug) {
    	    echo __FUNCTION__; 
    	    echo $query->dump();     
    	}
    	
    	return $db->loadObjectList();
	}



    
    /** 
     * $query_id mixed can be null, single id or array of ids 
     * 
     */ 

    public function parameterReport($query_id = null, $query_filter = null) {
        
        // get the querys 
        $db    = JFactory::getDbo();
    	$query = $db->getQuery(true);
    	$query->select('q.*, q.qtable, qt.qtable as type_table, qt.title as type');
    	$query->from($db->quoteName('queries', 'q'));
    	$query->join('LEFT', $db->quoteName('queries_types', 'qt') . ' ON q.type = qt.id ');   
    	
    	if(is_array($query_id)) {
            $query_id = implode(',', $query_id);
            $query->where('q.id = ' . $db->quote($query_id));
        } elseif(is_numeric($query_id)) {
            $query_id = $query_id; 
            $query->where('q.id IN( ' . $db->quote($query_id) . ')');
        }
    	$db->setQuery($query);
    	
    	if($this->debug) {
    	    echo __FUNCTION__; 
	        echo $query->dump();     
	    }
    	
    	return $queries = $db->loadObjectList();
    
        
        
        
        $db    = JFactory::getDbo();
    	$query = $db->getQuery(true);
    	$query->select('q.*, q.qtable, qt.qtable as type_table, qt.title as type');
    	$query->from($db->quoteName('queries', 'q'));
    	$query->join('LEFT', $db->quoteName('queries_types', 'qt') . ' ON q.type = qt.id ');   
    	$query->where('state = 1'); 
    	if(is_array($ids) && !empty($ids)) {
    	    $ids = array_merge(array(17), $ids);  
    	    $ids = $db->escape(implode(',', $ids));
    	    $query->where('q.id IN(' . $ids . ')');     
    	} elseif(is_numeric($ids) && !empty($ids)) {
    	    $ids = (array)$ids; 
    	    $ids = array_merge(array(17), $ids);  
    	    $ids = $db->escape(implode(',', $ids));
    	    $query->where('q.id IN(' . $ids . ')');
    	} 
    	$query->order('ordering ASC'); 
    	$db->setQuery($query);
        
        if($this->debug) {
            echo __FUNCTION__; 
    	    echo $query->dump();  
    	}
        
        $measurement = $db->loadObject();
        
        $this->processQuery($measurement); 
        
        return $this->queries; 
    }
	
	public function metabolicsReport($filter = NULL, $ids = array()) {
	    
	    //$where_filter = NULL; 
	    
	    if(!empty($filter) && is_numeric($filter)) {
		    $db    = JFactory::getDbo();
        	$f_query = $db->getQuery(true);
        	$f_query->select('q.*, q.qtable, qt.qtable as type_table, qt.title as type');
        	$f_query->from($db->quoteName('queries', 'q'));
        	$f_query->join('LEFT', $db->quoteName('queries_types', 'qt') . ' ON q.type = qt.id ');   
        	$f_query->where('q.id = ' . $db->quote($filter));
        	$db->setQuery($f_query);
        	
        	if($this->debug) {
    	        echo __FUNCTION__ . ' filter'; 
    	        echo $f_query->dump();     
    	    }
        	
        	$filter_result = $db->loadObject();
        	//print_r($filter_result);
        	
        	$where_filter = $this->processCompositeCase ($filter_result->qwhere); 
        	$where_filter = $this->replaceGender($where_filter); 
        	
            $this->where_filter = $where_filter; 
        }
	    
	    $return = array(); 
		$queries = array(); 
		
		/** Conditions */ 
		//$conditions = new stdClass; 
		//$conditions->paired_patient_visits = false;
		
		
		//$this->paired_patient_visits = false;
		
		/** Variabkes */ 
		//$visits = array(1,2); 
		//$normals = array('Total', 'Abnormal');
		
		$db    = JFactory::getDbo();
    	$query = $db->getQuery(true);
    	$query->select('q.*, q.qtable, qt.qtable as type_table, qt.title as type');
    	$query->from($db->quoteName('queries', 'q'));
    	$query->join('LEFT', $db->quoteName('queries_types', 'qt') . ' ON q.type = qt.id ');   
    	$query->where('state = 1'); 
    	if(is_array($ids) && !empty($ids)) {
    	    $ids = array_merge(array(17), $ids);  
    	    $ids = $db->escape(implode(',', $ids));
    	    $query->where('q.id IN(' . $ids . ')');     
    	} elseif(is_numeric($ids) && !empty($ids)) {
    	    $ids = (array)$ids; 
    	    $ids = array_merge(array(17), $ids);  
    	    $ids = $db->escape(implode(',', $ids));
    	    $query->where('q.id IN(' . $ids . ')');
    	} 
    	$query->order('ordering ASC'); 
    	$db->setQuery($query);
        
        if($this->debug) {
            echo __FUNCTION__ . ' measurements';  
    	    echo $query->dump();  
    	}
        
        $measurements = $db->loadObjectList();
        
        // Reset the queries 
        $this->queries = array(); 
        
		foreach ($measurements as $key => $measurement) {
		    $this->processQuery($measurement); 
		}
		
		if($this->debug) {
		    //echo $this->v1_total;
		    //echo $this->v2_total;
		    //echo $this->v1_total_general; 
		    //echo $this->v2_total_general; 
		}
		return $this->queries; 
	}

    public function processQuery($measurement, $visits = array(1,2), $normals = array('Total', 'Abnormal')) {
        if(!is_object($measurement)) {
            throw new Exception('Measurement should be an object'); 
        }
	    if(empty($measurement->qtable)) {
            $measurement->qtable = $measurement->type_table; 
        }
        $measurement->qwhere = $this->replaceGender($measurement->qwhere); 
    	$measurement->abnormal_condition = $this->replaceGender($measurement->abnormal_condition); 
    	
	    $qwhere_original = $measurement->qwhere; 
	    $abnormal_condition_original = $measurement->abnormal_condition;
	    $query_original = $measurement->query; 
	    
	    foreach ($normals as $normal) {
    	    foreach ($visits as $visit) {  
    	        // If no abnormal condition is defined skip the query 
    	        if($normal == 'Abnormal' && empty($measurement->abnormal_condition)) {
    	            continue;
    	        }
    	        
    		    $db    = JFactory::getDbo();
    	        //$query = $db->getQuery(true);
    	        
    	        // Sub different where based on vist 
    	        $qwhere = $qwhere_original; 
    	        
    	        // Replace Gender by visit 
    	        $abnormal_condition = $abnormal_condition_original; 
    	        
    	        // Initalise the query
        	   $query = $this->getQueryMeasurementType ($measurement, $query_original = ''); 
        	    
    	        // Determine which table set to query
                switch($measurement->qtable) {
        		    case 'users':
                        $query->from($db->quoteName('users', 'u'));
        		    break;  
        		    case 'patients': 
        		        $query->from($db->quoteName('patients', 'p'));
        		        $query->join('LEFT', $db->quoteName('users', 'u') . ' ON u.id = p.user_id '); 
        		        $query->join('LEFT', $db->quoteName('pt_entry', 'pe') . ' ON p.id = pe.patient_id');
        		    break; 
        		    case 'medications': 
        		        /**
        		        $subquery = $db->getQuery(true);
        		        $subquery->select('count(*) as n, pe.id');
        		        $subquery->from($db->quoteName('medications', 'm'));
        		        $subquery->join('LEFT', $db->quoteName('medications_map', 'mm') . ' ON mm.id = m.med_id');
        		        $subquery->join('LEFT', $db->quoteName('medications_class', 'mc') . ' ON mm.class_id = mc.id');
        		        $subquery->join('LEFT', $db->quoteName('pt_entry', 'pe') . ' ON pe.id = m.pt_entry_id');
            		    $subquery->join('LEFT', $db->quoteName('patients', 'p') . ' ON p.id = pe.patient_id ');
            		    $subquery->join('LEFT', $db->quoteName('users', 'u') . ' ON u.id = p.user_id '); 
                        $db->setQuery($subquery);
        
        		        $query->from($db->quoteName('pt_entry', 'pe') . ' ON pe.id = m.pt_entry_id');
        		        
        		        $query->join('LEFT', '(' . $subquery . ') as med_table');
        		        
        		        */
        		        
        		        //$this->debug = true; 
        		        
        		        $query->from($db->quoteName('medications', 'm'));
        		        $query->join('LEFT', $db->quoteName('medications_map', 'mm') . ' ON mm.id = m.med_id');
        		        $query->join('LEFT', $db->quoteName('medications_class', 'mc') . ' ON mm.class_id = mc.id');
        		        $query->join('LEFT', $db->quoteName('pt_entry', 'pe') . ' ON pe.id = m.pt_entry_id');
            		    $query->join('LEFT', $db->quoteName('patients', 'p') . ' ON p.id = pe.patient_id ');
            		    $query->join('LEFT', $db->quoteName('users', 'u') . ' ON u.id = p.user_id '); 
        		    break; 
        		    case 'pt_entry':
        		    default:
            		    $query->from($db->quoteName('pt_entry', 'pe'));
            		    $query->join('LEFT', $db->quoteName('patients', 'p') . ' ON p.id = pe.patient_id ');
            		    $query->join('LEFT', $db->quoteName('users', 'u') . ' ON u.id = p.user_id '); 
            		    $query->join('LEFT', $db->quoteName('surveys_data_55', 's55') . ' ON s55.user_id = u.id'); 
            		    $query->join('LEFT', $db->quoteName('surveys_data_57', 's57') . ' ON s57.user_id = u.id'); 
        		    break; 
        		}
        		
        		
        		if(!empty($measurement->qjoin)) {
                    // Add custom join 
                    //$join = explode
                    
                    $query->join('LEFT', $db->escape($measurement->qjoin));   
                }
                
        		/** Add conditions */
                if($measurement->qtable != 'users' && $this->paired_patient_visits == true) {
                    // Select only record that have a follow up visit 
                    $query->where('(SELECT count(patient_id) FROM pt_entry  WHERE patient_id = pe.patient_id) = 2');     
                }
                    
                // Toggle the Visit 
                $query->where('visit = ' . $db->escape($visit)); 
                
                // Add Where filter if has value 
                if(!empty($this->where_filter)) {
                    $query->where($this->where_filter);  
                } 
                
                // Add custom conditions 
                if($normal == 'Total' && !empty($measurement->qwhere)) {
                    $query->where($measurement->qwhere); 
                }
                
                if($normal == 'Abnormal' && !empty($measurement->abnormal_condition)) {
                    $query->where($measurement->abnormal_condition); 
                }
                
                if($normal == 'Healthy' && !empty($measurement->abnormal_condition)) {
                    $query->where($measurement->healthy_condition); 
                }
                
        		 // Remove servier and Allori pts 
                $query->where($this->whereRemove());  
                
                // Add Custom Group 
                if(!empty($measurement->qgroup)) {
                    $query->group($measurement->qgroup); 
                }
                
                // Add Custom Order 
                if(!empty($measurement->qorder)) {
                    $query->order($measurement->qorder); 
                }
                
        		$db->setQuery($query);
        		
        		if($this->debug) {
            	    echo __FUNCTION__; 
            	    echo $measurement->title . ' - ' . $normal . ' - v' . $visit;
            	    echo $query->dump();     
            	}
        		
        		if($this->where_filter) {
        		    if($this->debug) {    
        		        echo __FUNCTION__ . ' where filters';  
        		        echo $this->where_filter; 
        		    }
        		}
        		
        		if(!$this->debug) {
        		    $items = $db->loadObjectList();
        		    $prop = 'v' . $visit . '_total';
        		    
        		    foreach ($items as $key => $item) : 
        		        $item->title = $measurement->title; 
        		        $item->units = $measurement->units; 
        		        $item->query = $query->dump(); 
        		        $item->abnormal = $measurement->abnormal_condition;   
        		        
        		        if($measurement->type == 'filter') {
        		            $item->filter = $item->value;     
        		        } else {
        		            $item->filter = $key;  
        		        }
        		        
                	    if($measurement->title == 'Total' && $normal == 'Total' && !empty($item->n)) {
                	        $prop = 'v' . $visit . '_total'; 
                	        $this->{$prop} = $item->n; 
                	        if(empty($this->where_filter)) {
                	            $prop_general = 'v' . $visit . '_total_general'; 
                	            $this->{$prop_general} = $item->n; 
                	        }
                	    }
                	    
                	    if(!empty($this->{$prop})) {
                	        $item->percent = round((float)($item->n/$this->{$prop}) * 100, 1 ) . '%';
                	    } else {
                	        $item->percent = null; 
                	    }
                	    
                	    if(!empty($this->{'v' . $visit . '_total_general'})) {
                	        $item->percent_general = round((float)($item->n/$this->{'v' . $visit . '_total_general'}) * 100, 1 ) . '%';
                	    } else {
                	        $item->percent_general = null; 
                	    }
                	    $title = $measurement->title . ' (' . $measurement->units . ')'; 
                	        
        		        $this->queries[$title][$item->filter][$normal]['v' . $visit] = $item; 
        		        
        		        if(isset($this->_item->metabolics)) {
                	        $item->n_row = $this->_item->metabolics[$title][$item->filter][$normal]['v' . $visit]->n; 
                	        $item->percent_row = round((float)($item->n/$item->n_row) * 100, 1 ) . '%';
                	    } else {
                	        $item->n_row = null; 
                	        $item->percent_row = null; 
                	    }
        		        //print_r($item); 
        		    endforeach; 
        		}
	        }
	    }
	    return true; 
    }

    //// Function needs to be rewritten to group by MED_ID and get each param one at a time, will need avg, std, and n at a min 
    
   public function advancedMetabolicsReport() {
       // Look for metabolic syndrome 
       return $this->metabolicsReport(18); 
       
  /**     
SELECT 
count(*) as n, "" as value, "" as sd,"" as min, "" as max
FROM `pt_entry` AS `pe`
LEFT JOIN `patients` AS `p` ON p.id = pe.patient_id 
LEFT JOIN `users` AS `u` ON u.id = p.user_id 
LEFT JOIN (SELECT patient_id, new_medication as new_medication1 from pt_entry WHERE visit = 1) AS pe1 ON pe.patient_id = pe1.patient_id  
WHERE visit = 2 AND new_medication1 <> '' 
AND u.email NOT LIKE '%@servier%' AND u.email NOT LIKE '%@allori%' AND u.aphra_number LIKE 'MED%'

*/
   }
   
   
    public function medicationsReport() {
        /**
        $return = array(); 
        
        $db    = JFactory::getDbo();
        
        $queries = array(0, 18, 1, 4, 5, 6, 7, 8, 9, 10, 11, 30, 31, 24, 23); 
        $visits = array(1,2); 
        
        
	    foreach ($queries as $id) {
	        foreach ($visits as $visit) {
	        if(!empty($id)) {
            	$query = $db->getQuery(true);
            	$query->select('q.*, q.qtable, qt.qtable as type_table, qt.title as type');
            	$query->from($db->quoteName('queries', 'q'));
            	$query->join('LEFT', $db->quoteName('queries_types', 'qt') . ' ON q.type = qt.id ');   
            	$query->where('q.id = ' . $db->quote($id)); 
            	$db->setQuery($query);
            	if($this->debug) {
        	        echo $query->dump();     
        	    }
                
                $measurement = $db->loadObject();
                
                $query = $this->getQueryMeasurementType ($measurement, $query_original = ''); 
                $alias = preg_replace("/[^A-Za-z0-9]/", '_', strtolower($measurement->title));
                $query->select($db->quote($alias) . ' as alias');
            } else {
                $query = $db->getQuery(true);
                $query->select('"" as n, "" as avg, "" as sd, "" as min, "" as max,' . $db->quote('total') . ' as alias');
            }
	        
	        
		        $query->select('count(*) as n, mm.id, mm.generic, mm.brand, mc.title as class'); 
		        $query->from($db->quoteName('medications', 'm'));
                $query->join('LEFT', $db->quoteName('medications_map', 'mm') . ' ON mm.id = m.med_id');
                $query->join('LEFT', $db->quoteName('medications_class', 'mc') . ' ON mc.id = mm.class_id');
                $query->join('LEFT', $db->quoteName('pt_entry', 'pe') . ' ON pe.id = m.pt_entry_id');
        	    $query->join('LEFT', $db->quoteName('patients', 'p') . ' ON p.id = pe.patient_id ');
        	    $query->join('LEFT', $db->quoteName('users', 'u') . ' ON u.id = p.user_id '); 
        	    
        	    if(!empty($measurement->qwhere)) {
                    $query->where($this->replaceGender($measurement->qwhere)); 
                }
        	    
        	    $query->where('visit = ' . $db->quote($visit)); 
        	    
                // Anti-Psychotics 
                //$query->where('(mc.id = 13 OR mc.id = 11 OR mc.id = 5)'); // All Antipschoitics 
                $query->where('(mc.id = 13)'); // Atypial Only 
                $query->where($this->whereRemove());   
                $query->order('mc.ordering ASC, mm.generic ASC'); 
            	$query->group('mm.id');  
            	
            	$db->setQuery($query);
                if($this->debug) {
            	    
            	}
            	echo $query->dump();  
            	
            	$rows = $db->loadObjectList(); 
            	
            	foreach($rows as $row) {
            	    $return[$row->alias][$row->generic][$visit]['n'] = $row->n;  
            	    $return[$row->alias][$row->generic][$visit]['avg'] = $row->avg;  
            	}
    		}
		}	
        return $return;  
        */
    }
    
    public function medicationsReportCur($id = 0, $visit = 1) {
        
        $db    = JFactory::getDbo();
        
        if(!empty($id)) {
        	$query = $db->getQuery(true);
        	$query->select('q.*, q.qtable, qt.qtable as type_table, qt.title as type');
        	$query->from($db->quoteName('queries', 'q'));
        	$query->join('LEFT', $db->quoteName('queries_types', 'qt') . ' ON q.type = qt.id ');   
        	$query->where('q.id = ' . $db->quote($id)); 
        	$db->setQuery($query);
        	if($this->debug) {
    	        echo $query->dump();     
    	    }
            
            $measurement = $db->loadObject();
            
            $query = $this->getQueryMeasurementType ($measurement, $query_original = ''); 
            $alias = preg_replace("/[^A-Za-z0-9]/", '_', strtolower($measurement->title));
            $query->select($db->quote($alias) . ' as alias');
        } else {
            $query = $db->getQuery(true);
            $query->select('"" as n, "" as avg, "" as sd, "" as min, "" as max,' . $db->quote('total') . ' as alias');
        }
            
        $query->select('count(*) as n, mm.generic, mm.brand, mc.title as class'); 
        
        $query->select($db->quote($visit) . ' as visit'); 
        
        
            
        /**
        
    	    if(empty($measurement->qtable)) {
                $measurement->qtable = $measurement->type_table; 
            }
            
    	    $alias = preg_replace("/[^A-Za-z0-9]/", '_', strtolower($measurement->title));
    	    
        	switch($measurement->type) {
        	    case 'measurement': 
                    $query->select('CONCAT(ROUND(AVG(CAST(' . $db->quoteName($measurement->query) . ' AS DECIMAL(12,2))),3), ' . $db->quote(' (') . ', ROUND(STD(CAST(' . $db->quoteName($measurement->query) . ' AS DECIMAL(12,2))),3), ' . $db->quote(')') . ') as ' . $db->escape($alias) . "\n"); 
                    
                    if(!empty($measurement->query)) {
                        $min_value = (!empty($measurement->min_value)) ? $measurement->min_value : 0; 
                        
                        $query->where($measurement->query . ' > ' . $db->escape($min_value)); 
                
                        if(!empty($measurement->max_value)) {
                            $query->where($measurement->query . ' < ' . $db->escape($measurement->max_value)); 
                        }
                    }
                    
                break; 
                case 'measurement_advanced': 
                   $query->select('CONCAT(ROUND(AVG(CAST(' . $db->escape($measurement->query) . ' AS DECIMAL(12,2))),3), ' . $db->quote(' (') . ', ROUND(STD(CAST(' . $db->escape($measurement->query) . ' AS DECIMAL(12,2))),3), ' . $db->quote(')') . ') as ' . $db->escape($alias) . "\n"); 
                   
                   if(!empty($measurement->query)) {
                    $min_value = (!empty($measurement->min_value)) ? $measurement->min_value : 0; 
                    
                    $query->where($measurement->query . ' > ' . $db->escape($min_value)); 
            
                    if(!empty($measurement->max_value)) {
                        $query->where($measurement->query . ' < ' . $db->escape($measurement->max_value)); 
                    }
                }
                
                
                break; 
                case 'count': 
                    
                break; 
        	}      
        */
    	
        $query->from($db->quoteName('medications', 'm'));
        $query->join('LEFT', $db->quoteName('medications_map', 'mm') . ' ON mm.id = m.med_id');
        $query->join('LEFT', $db->quoteName('medications_class', 'mc') . ' ON mc.id = mm.class_id');
        $query->join('LEFT', $db->quoteName('pt_entry', 'pe') . ' ON pe.id = m.pt_entry_id');
	    $query->join('LEFT', $db->quoteName('patients', 'p') . ' ON p.id = pe.patient_id ');
	    $query->join('LEFT', $db->quoteName('users', 'u') . ' ON u.id = p.user_id '); 
	    
	    if(!empty($measurement->qwhere)) {
            $query->where($this->replaceGender($measurement->qwhere)); 
        }
	    
	    $query->where('visit = ' . $db->quote($visit)); 
	    
        // Anti-Psychotics 
        //$query->where('(mc.id = 13 OR mc.id = 11 OR mc.id = 5)'); // All Antipschoitics 
        $query->where('(mc.id = 13)'); // Atypial Only 
        $query->where($this->whereRemove());   
        $query->order('mc.ordering ASC, mm.generic ASC'); 
    	$query->group('mm.id');  
    	
    	$db->setQuery($query);
        if($this->debug) {
    	    echo $query->dump();     
    	}
        
        return $medications = $db->loadObjectList();
    }
    
    public function medicationsReport2($id = NULL) {
        $return = array(); 
		
		$db    = JFactory::getDbo();
    	$query = $db->getQuery(true);
    	$query->select('q.*, q.qtable, qt.qtable as type_table, qt.title as type');
    	$query->from($db->quoteName('queries', 'q'));
    	$query->join('LEFT', $db->quoteName('queries_types', 'qt') . ' ON q.type = qt.id ');   
    	$query->where('state = 1'); 
    	$db->setQuery($query);
        if($this->debug) {
    	    echo $query->dump();     
    	}
        	
        $measurements = $db->loadObjectList();
        
		// Number of Meds table 
		// SELECT DISTINCT(pt_entry_id), count(*) FROM `medications` GROUP BY `pt_entry_id` 
		
		// n, min, max, avg - dose 
		// frequency - group 
		// route - group 
		// duration 
		
		
		/** Variables */ 
		
		//$visits = array(1,2); 
		//$normals = array('Total', 'Abnormal');
		
		$db    = JFactory::getDbo();
    	$query = $db->getQuery(true);
    	
    	$query->select('count(*) as n, mm.generic, mm.brand, mc.title as class'); 
    	
    	foreach ($measurements as $measurement) {
    	   $qwhere_original = $measurement->qwhere; 
    	    if(empty($measurement->qtable)) {
                $measurement->qtable = $measurement->type_table; 
            }
            
            //$query->select('CONCAT(ROUND(AVG(CAST(' . $db->quoteName($measurement->query) . ' AS DECIMAL(12,2))),3), " (", ROUND(STD(CAST(' . $db->quoteName($measurement->query) . ' AS DECIMAL(12,2))),3), ")") as ' . $db->escape($alias));
            
            
            
            
    	    
    	    
    	    $alias = preg_replace("/[^A-Za-z0-9]/", '_', strtolower($measurement->title));
    	    //echo $alias . "<BR>"; 
    	    
    	    // Working 
    	    //$query->select('CONCAT(ROUND(AVG(CAST(' . $db->quoteName($measurement->query) . ' AS DECIMAL(12,2))),3), ' . $db->quote(' (') . ', ROUND(STD(CAST(' . $db->quoteName($measurement->query) . ' AS DECIMAL(12,2))),3), ' . $db->quote(')') . ') as ' . $db->escape($alias)); 
    	    
    	    
    	    //$alias = 'value';
    	    
    	
        	switch($measurement->type) {
        	    case 'measurement': 
                    $query->select('CONCAT(ROUND(AVG(CAST(' . $db->quoteName($measurement->query) . ' AS DECIMAL(12,2))),3), ' . $db->quote(' (') . ', ROUND(STD(CAST(' . $db->quoteName($measurement->query) . ' AS DECIMAL(12,2))),3), ' . $db->quote(')') . ') as ' . $db->escape($alias) . "\n"); 
                    
                    //$query->where($db->quoteName($measurement->query) . ' > 0'); 
                        
                        
                    /**
    	            if(!empty($measurement->query)) {
                        $min_value = (!empty($measurement->min_value)) ? $measurement->min_value : 0; 
                        
                        $query->where($measurement->query . ' > ' . $db->escape($min_value)); 
                
                        if(!empty($measurement->max_value)) {
                            $query->where($measurement->query . ' < ' . $db->escape($measurement->max_value)); 
                        }
                    }
                    */
                break; 
                case 'measurement_advanced': 
                    //$query->select('CONCAT(ROUND(AVG(CAST(' . $db->escape($measurement->query) . ' AS DECIMAL(12,2))),3), " (", ROUND(STD(CAST(' . $db->escape($measurement->query) . ' AS DECIMAL(12,2))),3), ")") as ' . $db->escape($alias)); 
                    
                    //$query->select('count(*) as n, AVG(' . $db->escape($measurement->query) .') as value, STD(' . $db->escape($measurement->query) .') as sd, min(' . $db->escape($measurement->query) .') as min, max(' . $db->escape($measurement->query) .') as max');     
                    /**
                    if(!empty($measurement->query)) {
                        $min_value = (!empty($measurement->min_value)) ? $measurement->min_value : 0; 
                        
                        $query->where($measurement->query . ' > ' . $db->escape($min_value)); 
                
                        if(!empty($measurement->max_value)) {
                            $query->where($measurement->query . ' < ' . $db->escape($measurement->max_value)); 
                        }
                    }
                    */
                break; 
                case 'count': 
                    //$query->select('count(*) as ' . $db->escape($alias)); 
                    //$query->join('LEFT', '(SELECT pe1.patient_id, count(*) as ' . $db->escape($alias) . ' FROM pt_entry as pe1 LEFT JOIN patients ON patients.id = pe1.patient_id WHERE ' . $this->replaceGender($qwhere_original) . ') as ' . $db->escape($alias . '_table') . ' ON ' . $db->escape($alias . '_table') . '.patient_id = pe.patient_id' );
                    
                    
                    
                		    
                    
                break; 
        	}      
    	    
    	}
    	
    	
    	$query->from($db->quoteName('medications', 'm'));
        $query->join('LEFT', $db->quoteName('medications_map', 'mm') . ' ON mm.id = m.med_id');
        $query->join('LEFT', $db->quoteName('medications_class', 'mc') . ' ON mc.id = mm.class_id');
        $query->join('LEFT', $db->quoteName('pt_entry', 'pe') . ' ON pe.id = m.pt_entry_id');
	    $query->join('LEFT', $db->quoteName('patients', 'p') . ' ON p.id = pe.patient_id ');
	    $query->join('LEFT', $db->quoteName('users', 'u') . ' ON u.id = p.user_id '); 
	    
	    //$query->where("WHERE hdl_cholesterol > 0 AND hdl_cholesterol < 5 AND (SELECT count(patient_id) FROM pt_entry  WHERE patient_id = pe.patient_id) = 2 AND visit = 1 AND u.email NOT LIKE '%@servier%' AND u.email NOT LIKE '%@allori%' AND u.aphra_number LIKE 'MED%'");
        // Anti-Psychotics 
        $query->where('(mc.id = 13 OR mc.id = 11 OR mc.id = 5)');
        $query->where($this->whereRemove());   
        $query->order('mc.ordering ASC, mm.generic ASC'); 
    	$query->group('mm.id');  
    	
    	$db->setQuery($query);
        if($this->debug) {
    	    echo $query->dump();     
    	}
        
        return $medications = $db->loadObjectList();
        
        /**
         *  Select basic medication stats 
         * SELECT mm.id, count(*), mm.generic, mm.brand, m.route, mc.title as class, ROUND(avg(dose_mg),1) as avg_dose, min(dose_mg) as min_dose, max(dose_mg) as max_dose,
SUM((`duration_days` / 365) + (`duration_months` /12) + `duration_years`) as total_years, AVG((`duration_days` / 365) + (`duration_months` /12) + `duration_years`) as avg_years, 
STD((`duration_days` / 365) + (`duration_months` /12) + `duration_years`) as std_years
FROM `medications` AS m
LEFT JOIN medications_map AS mm ON mm.id = m.`med_id`
LEFT JOIN medications_class AS mc ON mc.id = mm.class_id
WHERE dose_mg > 0 
GROUP BY m.med_id, m.`route`
*/
        
        
        /**
        
        */
        
        
        
        
        // JK - michaela - 03 9076 6924 
        // Trevor - social research 
        
        
        
        
        // Select Counts for all meds at baseline and visit 2 
        
        
        // count of med
        // patients continued at visit 2
        // patients switched in at visit 2 
        // patients switched out at visit 2 
        
        // Psychs that suggested med change 
        // -- array of meds change with counts 
        // Psychs that did lifestlye 
        // psychs that did all interventions, etc 
        /**
        call aaron 
        tuesday - 
        ANZJP - jan or feb, new consultants 
        */
        
        
        // Select Switch from each med to other meds -- neeed to exclude any meds that stayed the same 
        
        
    }
	/**
	 * Get an instance of JTable class
	 *
	 * @param   string $type   Name of the JTable class to get an instance of.
	 * @param   string $prefix Prefix for the table class name. Optional.
	 * @param   array  $config Array of configuration values for the JTable object. Optional.
	 *
	 * @return  JTable|bool JTable if success, false on failure.
	 */
	public function getTable($type = 'Mmap', $prefix = 'MmapTable', $config = array())
	{
		$this->addTablePath(JPATH_ADMINISTRATOR . '/components/com_mmap/tables');

		return JTable::getInstance($type, $prefix, $config);
	}
	
	private function saveResults($result) {
	    $db    = JFactory::getDbo();
    	$query = $db->getQuery(true);
    	        
    	$query->select('r.id');
    	$query->from($db->quoteName('results', 'r'));
    	$query->where('query_id = ' . $db->quote($item->query_id) . ' AND normal = ' . $db->quote($item->normal) . ' AND visit = ' . $db->quote($item->visit)); 
    	
    	$measurements = $db->loadObject();
	    
	    $db    = JFactory::getDbo();
    	$query = $db->getQuery(true);
    	
    	$object             = new stdClass; 
    	$object->query_id   = (int) $item->query_id; 		
    	$object->normal	    = $item->normal; 
    	$object->visit      = (int) $item->visit; 	
    	$object->mean       = $item->value; 	
    	$object->sd	        = $item->sd; 	
    	$object->rmin       = $item->min; 	
    	$object->rmax       = $item->max; 	
    	$object->n          = $item->n; 
    	$object->query      = $item->query; 
    	$object->created    = JFactory::getDate()->toSql(); 
    	$object->updated    = JFactory::getDate()->toSql();  

        $result = JFactory::getDbo()->insertObject('#__results', $object);
        $result = JFactory::getDbo()->updateObject('#__results', $object, 'id');
	}
	
	public function replaceGender($subject) {
        $db    = JFactory::getDbo();
	    return str_replace(array('__IS_MALE__', '__IS_FEMALE__'), array("p.gender = " . $db->quote('male'), "p.gender = " . $db->quote('female')), $subject); 
	}
	
	public function whereRemove() {
	    $db    = JFactory::getDbo();
    	return  'u.email NOT LIKE ' . $db->quote('%@servier%') .
    	        ' AND u.email NOT LIKE ' . $db->quote('%@allori%') .   
    	        ' AND u.aphra_number LIKE ' . $db->quote('MED%');
	}
	
	public function getQueryMeasurementType ($measurement, $query_original = '') {
	    
	    $db    = JFactory::getDbo();
    	$query = $db->getQuery(true);
	    
	    switch ($measurement->type) {
            case 'composite':
                $stats = array('value' => 'AVG','sd'=>'STD', 'min'=>'MIN', 'max'=>'MAX'); 
                $measurement->query = explode('|', $query_original); 
                $query->select('count(*) as n'); 
                
                foreach ($stats as $key => $stat) {
                    $measurement_querys = array(); 
                    $measurement_query = '';
                    foreach($measurement->query as $measurement_query) {
                        $measurement_querys[] = $stat . '('.$measurement_query.')';
                        /*
                        if(!empty($measurement_query)) {
                            if(!empty($measurement->min_value) || $measurement->min_value == 0) {
                                $query->where($measurement->query . ' > ' . $db->escape($measurement->min_value)); 
                            }
                            if(!empty($measurement->max_value)) {
                                $query->where($measurement->query . ' < ' . $db->escape($measurement->max_value)); 
                            }
                        }
                        */
                    }
                    $measurement_query = implode(',' . $db->quote($measurement->divider) . ',', $measurement_querys);
                    $query->select('CONCAT(' . $measurement_query . ') as ' . $key);
                }
            break; 
            case 'measurement': 
                $query->select('count(*) as n, AVG(CAST(' . $db->quoteName($measurement->query) .' AS DECIMAL(12,2))) as value, STD(CAST(' . $db->quoteName($measurement->query) .' AS DECIMAL(12,2))) as sd, min(CAST(' . $db->quoteName($measurement->query) .' AS DECIMAL(12,2))) as min, max(CAST(' . $db->quoteName($measurement->query) .' AS DECIMAL(12,2))) as max');
	            if(!empty($measurement->query)) {
                    $min_value = (!empty($measurement->min_value)) ? $measurement->min_value : 0; 
                    
                    $query->where($measurement->query . ' > ' . $db->escape($min_value)); 
            
                    if(!empty($measurement->max_value)) {
                        $query->where($measurement->query . ' < ' . $db->escape($measurement->max_value)); 
                    }
                }
            break; 
            case 'measurement_advanced': 
                $query->select('count(*) as n, AVG(' . $db->escape($measurement->query) .') as value, STD(' . $db->escape($measurement->query) .') as sd, min(' . $db->escape($measurement->query) .') as min, max(' . $db->escape($measurement->query) .') as max');     
                
                
                if(!empty($measurement->query)) {
                    $min_value = (!empty($measurement->min_value)) ? $measurement->min_value : 0; 
                    
                    $query->where($measurement->query . ' > ' . $db->escape($min_value)); 
            
                    if(!empty($measurement->max_value)) {
                        $query->where($measurement->query . ' < ' . $db->escape($measurement->max_value)); 
                    }
                }
                
                
            break; 
            case 'composite_case';
                $query->select('count(*) as n, "" as value, "" as sd,"" as min, "" as max'); 
                
                $measurement->qwhere = $this->processCompositeCase ($measurement->qwhere); 
                
                /**
                // Replace {id} tags and get the abnormal condition for each 
                $regex = '#{(.*?)}#s';
                if(isset($measurement->qwhere)){
        			preg_match_all($regex, $measurement->qwhere, $matches);
        			if(!empty($matches[1][0])){
        				$chrono_data = $matches[1];
        				foreach($chrono_data as $i => $match){
        					
        					$composite = $db->getQuery(true);
                        	$composite->select('id, abnormal_condition');
                        	$composite->from($db->quoteName('queries', 'q'));
                        	$composite->where('id = ' . $db->quote($match)); 
                        	$db->setQuery($composite);
                        	if($this->debug) {
                        	    echo $query->dump();     
                        	}
                        	
                        	$item = $db->loadObject();
        					
        					$measurement->qwhere = str_replace($matches[0][$i], $this->replaceGender($item->abnormal_condition), $measurement->qwhere);
        				}
        			}
                }
                */////
                //echo $measurement->qwhere; 
            break;
            case 'filter': 
                $query->select('count(*) as n, ' . $db->escape($measurement->query) . ' as value, "" as sd,"" as min, "" as max'); 
                
                $measurement->qwhere = $this->processCompositeCase ($measurement->qwhere); 
                    
            break; 
            case 'count': 
                $query->select('count(*) as n, "" as value, "" as sd,"" as min, "" as max'); 
            break; 
        }
        
        return $query; 
	    
	}
	
	private function processMeasurementAdvanced ($measurement) {
	    if(!empty($measurement->query)) {
            $min_value = (!empty($measurement->min_value)) ? $measurement->min_value : 0; 
            
            $query->where($measurement->query . ' > ' . $db->escape($min_value)); 
    
            if(!empty($measurement->max_value)) {
                $query->where($measurement->query . ' < ' . $db->escape($measurement->max_value)); 
            }
        }
	}
	
	private function processCompositeCase ($qwhere) {
        
        $db    = JFactory::getDbo();
    	
        // Replace {id} tags and get the abnormal condition for each 
        $regex = '#{(.*?)}#s';
        if(isset($qwhere)){
			preg_match_all($regex, $qwhere, $matches);
			if(!empty($matches[1][0])){
				$chrono_data = $matches[1];
				foreach($chrono_data as $i => $match){
					
					$composite = $db->getQuery(true);
                	$composite->select('id, abnormal_condition');
                	$composite->from($db->quoteName('queries', 'q'));
                	$composite->where('id = ' . $db->quote($match)); 
                	$db->setQuery($composite);
                	if($this->debug) {
                	    echo $composite->dump();     
                	}
                	
                	$item = $db->loadObject();
				
					$qwhere = str_replace($matches[0][$i], $this->replaceGender($item->abnormal_condition), $qwhere);    
					
				}
			}
        }
	    return $qwhere; 
	}
	
	public function selectComments() {
	    $db    = JFactory::getDbo();
    	
	    $query = $db->getQuery(true);
    	$query->select('comments');
    	$query->from($db->quoteName('pt_entry', 'pe'));
    	
    	$db->setQuery($query);
    	if($this->debug) {
    	    echo $query->dump();     
    	}
    	$comments = $db->loadObjectList();
    	
    	foreach($comments as $comment) {
    	    echo $comment->comments . ' '; 
    	}
	}
	
	
	
	
}

/** 
 * GET TIME BETWEEN VISITS COMPARED TO PROPOSED VISIT GAP 
 * 
 * SELECT v1.patient_id, v1.visit, v2.visit, v1.`occurred_on`, v2.`occurred_on`, avg(TIMESTAMPDIFF(WEEK, v1.`occurred_on`, v2.`occurred_on`)) AS diff FROM (SELECT * FROM `pt_entry` WHERE visit = 1) as v1 
LEFT JOIN (SELECT * FROM `pt_entry` WHERE visit = 2) as v2 ON v1.patient_id = v2.patient_id 









// COMPARE 1 value 

SELECT 
DISTINCT(p.id), p.gender, visit1.fasting_plasma_glucose, visit2.fasting_plasma_glucose,

p.new_medication,
p.switch_antipsychotic_medication as intervention_switch,
p.adjust_dose_of_current_antipsychotic_medication as intervention_dose,
p.lifestyle_intervention as intervention_lifestyle,
p.commence_other_specific_therapy as intervention_medical,
p.other as intervention_other,
p.`time_until_next_review`



FROM patients as p
LEFT JOIN `users` AS `u` ON u.id = p.user_id 

LEFT JOIN (SELECT pe1.*
FROM `pt_entry` AS `pe1` 
LEFT JOIN `patients` AS `p1` ON p1.id = pe1.patient_id 
LEFT JOIN `users` AS `u1` ON u1.id = p1.user_id 
LEFT JOIN `surveys_data_55` AS `s551` ON s551.user_id = u1.id 
LEFT JOIN `surveys_data_57` AS `s571` ON s571.user_id = u1.id 
LEFT JOIN medications as m1 ON m1.pt_entry_id = pe1.id 
LEFT JOIN medications_map as mm1 ON mm1.id = m1.med_id 
LEFT JOIN medications_class as mc1 ON mm1.class_id = mc1.id 
WHERE visit = 1
GROUP BY p1.id) as visit1 ON visit1.patient_id = p.id 

LEFT JOIN (SELECT pe2.*
FROM `pt_entry` AS `pe2` 
LEFT JOIN `patients` AS `p2` ON p2.id = pe2.patient_id 
LEFT JOIN `users` AS `u2` ON u2.id = p2.user_id 
LEFT JOIN `surveys_data_55` AS `s552` ON s552.user_id = u2.id 
LEFT JOIN `surveys_data_57` AS `s572` ON s572.user_id = u2.id 
LEFT JOIN medications as m2 ON m2.pt_entry_id = pe2.id 
LEFT JOIN medications_map as mm2 ON mm2.id = m2.med_id 
LEFT JOIN medications_class as mc2 ON mm2.class_id = mc2.id 
WHERE visit = 2
GROUP BY p2.id) as visit2 ON visit2.patient_id = p.id 

WHERE visit1.fasting_plasma_glucose IS NOT NULL 

AND (u.email NOT LIKE '%@servier%' AND u.email NOT LIKE '%@allori%' AND u.aphra_number LIKE 'MED%')
GROUP BY p.id 
ORDER BY p.id 





// Big flat tables

SELECT 
pe.id,
max(total_duration_years) as max_med_years, 
u.id as user_id,
u.name as user_name,
s55.gender as user_gender,
u.institution,
u.address_state,
u.phone,
u.admin,
u.email,

metabolic_adverse_effects,
metabolic_proportion,
review_frequency,
years_of_practice,
patients,
setting,
relevant,
learning_effective,
audit_useful,
likely_to_implement,
changed_practice,

pe.patient_id,
visit,
occurred_on,
p.gender as patient_gender,
num_meds,
height,
weight,
bmi,
waist_circumference,
fasting_plasma_glucose,
total_cholesterol,
ldl_cholesterol,
hdl_cholesterol,
triglycerides,
systolic,
diastolic,

ongoing_management_plan,
reasons,
overweight,
waist_above_normal,
elevated_fasting_plasma_glucose,
dyslipidaemia,
hypertension,
switch_antipsychotic_medication,
new_medication,
adjust_dose_of_current_antipsychotic_medication,
lifestyle_intervention,
commence_other_specific_therapy,

pe.comments,
time_until_next_review,
patient_consent


FROM `pt_entry` AS `pe` 
LEFT JOIN `patients` AS `p` ON p.id = pe.patient_id 
LEFT JOIN `users` AS `u` ON u.id = p.user_id 
LEFT JOIN `surveys_data_55` AS `s55` ON s55.user_id = u.id 
LEFT JOIN `surveys_data_57` AS `s57` ON s57.user_id = u.id 
LEFT JOIN medications as m ON m.pt_entry_id = pe.id 
LEFT JOIN medications_map as mm ON mm.id = m.med_id 
LEFT JOIN medications_class as mc ON mm.class_id = mc.id 
GROUP BY pe.id 
ORDER BY p.id, pe.visit


*/
