gistfile1.php

<?php
class PaymentException extends AppModel {
	var $actsAs = array('SoftDeletable');
	var $belongsTo = array('Procedure');
	var $hasMany = array(
		'ExceptionWorkflowLog' => array(
			'dependent' => true,
			'order' => array('ExceptionWorkflowLog.created')
		)
	);

gistfile2.php

<?php
class Procedure extends AppModel {
	var $actsAs = array('SoftDeletable');
	var $belongsTo = array('Claim', 'Payer');
	var $hasOne = array('PaymentException');
	var $hasMany = array('Payment');
}

gistfile3.php

<?php
class Claim extends AppModel {
	var $actsAs = array('SoftDeletable');
	var $belongsTo = array('Group');
	var $hasMany = array('Procedure');
}

gistfile4.php

<?php
class Group extends AppModel {
	var $actsAs = array('SoftDeletable');
	var $displayField = 'abbr';
	var $hasMany = array(
		'Batch',
		'Claim',
		'Facility' => array(
			'dependent' => true,
			'order' => 'Facility.name'
		)
	);	

gistfile5.php

<?php
class ExceptionWorkflowLog extends AppModel {
	var $actsAs = array('SoftDeletable');
	var $belongsTo = array(
		'PaymentException' => array(
			'foreignKey' => 'exception_id'
		),
		'User'
	);

gistfile6.php

<?php
class User extends AppModel {
	var $actsAs = array('SoftDeletable');
	var $displayField = 'full_name';
	var $hasMany = array(
		'UserAbsence' => array(
			'dependent' => true,
			'order' => 'start_date ASC'
		), 
		'BatchWorkflowLog'
	);
	var $hasAndBelongsToMany = array('Notification');

gistfile7.sql

SELECT "User"."id" AS "Report__record_id", "User"."full_name" AS "Report__name", COUNT("PaymentException"."id") AS "Report__exception_count", 
	AVG("PaymentException"."appeals") AS "Report__appeals_average", SUM("PaymentException"."writeoff_amount") AS "Report__writeoff_total", 
	SUM("PaymentException"."appeal_amount") AS "Report__amount_total", AVG("PaymentException"."appeal_amount") AS "Report__appealed_average", 
	AVG(EXTRACT(epoch FROM CASE WHEN "PaymentException"."finalized" IS NULL THEN now() ELSE "PaymentException"."finalized" END) - EXTRACT(epoch FROM "PaymentException"."created")) AS "Report__age_average", 
	AVG(EXTRACT(epoch FROM "ExceptionWorkflowLog"."created") - EXTRACT(epoch FROM "PaymentException"."created")) AS "Report__response_average" 
FROM exceptions "PaymentException" 
	INNER JOIN procedures "Procedure" ON "Procedure"."id" = "PaymentException"."procedure_id" 
	INNER JOIN claims "Claim" ON "Claim"."id" = "Procedure"."claim_id" 
	INNER JOIN groups "Group" ON "Group"."id" = "Claim"."group_id" 
	INNER JOIN exception_workflow_logs "ExceptionWorkflowLog" on "ExceptionWorkflowLog"."exception_id" = "PaymentException"."id" AND "ExceptionWorkflowLog"."action" = 2 
	INNER JOIN users "User" ON "User"."id" = "PaymentException"."appeal_user_id" 
WHERE "PaymentException"."appeal_amount" > 0 
	AND "PaymentException"."finalized" IS NULL 
	AND "Group"."id" = 22 
	AND date_trunc('day', "PaymentException"."created") >= '11/01/2010' AND date_trunc('day', "PaymentException"."created") <= '11/05/2010' 
GROUP BY "Report__record_id", "Report__name" 
ORDER BY "Report__name";