function where()
{
parent::where();
$params = array('name' => 'HRJobContract_Summary');
CRM_Core_DAO::commonRetrieve('CRM_Core_DAO_CustomGroup', $params, $cGrp);
$params = array('name' => 'Final_Termination_Date', 'custom_group_id' => $cGrp['id']);
CRM_Core_DAO::commonRetrieve('CRM_Core_DAO_CustomField', $params, $cField);
$dbAlias = $this->_columns[$cGrp['table_name']]['fields']["custom_{$cField['id']}"]['dbAlias'];
if (!$this->isFieldSelected($this->_columns[$cGrp['table_name']])) {
$whereClauses[] = "({$dbAlias} >= CURDATE() OR {$dbAlias} IS NULL)";
}
$params = array('name' => 'Initial_Join_Date', 'custom_group_id' => $cGrp['id']);
CRM_Core_DAO::commonRetrieve('CRM_Core_DAO_CustomField', $params, $cField);
$dbAlias = $this->_columns[$cGrp['table_name']]['fields']["custom_{$cField['id']}"]['dbAlias'];
$addWhereClauses = "({$this->_aliases['civicrm_hrjobcontract']}.is_primary IS NULL AND {$dbAlias} IS NOT NULL AND {$dbAlias} <= CURDATE())";
if (!empty($this->_params['current_employee_value'])) {
$whereClauses[] = "(({$this->_aliases['civicrm_hrjobcontract']}.is_primary = 1 OR {$this->_aliases['civicrm_hrjobcontract']}.is_primary IS NULL) AND ({$this->_aliases['civicrm_hrjobcontract_details']}.period_start_date IS NOT NULL AND {$this->_aliases['civicrm_hrjobcontract_details']}.period_start_date <= CURDATE()))";
$this->_where = str_replace("AND ( hrjobcontract_civireport.current_employee = 1 )", '', $this->_where);
}
$whereClauses[] = "{$this->_aliases['civicrm_contact']}.contact_type = 'Individual'";
$where = implode(' AND ', $whereClauses);
if ($this->_where == "WHERE ( 1 )") {
$this->_where = $where;
} else {
$this->_where .= " AND {$where}";
}
}
/**
* @param string $fieldName
* @param $field
* @param $defaults
*
* @return bool
*/
public static function dateParam($fieldName, &$field, &$defaults)
{
// type = 12 (datetime) is not recognized by Utils_Type::escape() method,
// and therefore the below hack
$type = 4;
$from = self::getTypedValue("{$fieldName}_from", $type);
$to = self::getTypedValue("{$fieldName}_to", $type);
$relative = CRM_Utils_Array::value("{$fieldName}_relative", $_GET);
if ($relative) {
list($from, $to) = CRM_Report_Form::getFromTo($relative, NULL, NULL);
$from = substr($from, 0, 8);
$to = substr($to, 0, 8);
}
if (!($from || $to)) {
return FALSE;
}
if ($from !== NULL) {
$dateFrom = CRM_Utils_Date::setDateDefaults($from);
if ($dateFrom !== NULL && !empty($dateFrom[0])) {
$defaults["{$fieldName}_from"] = $dateFrom[0];
}
}
if ($to !== NULL) {
$dateTo = CRM_Utils_Date::setDateDefaults($to);
if ($dateTo !== NULL && !empty($dateTo[0])) {
$defaults["{$fieldName}_to"] = $dateTo[0];
}
}
}
function where()
{
parent::where();
list($offset, $rowCount) = $this->limit();
$this->_limit = NULL;
$tempClause = $offset && $rowCount ? "AND temp.id BETWEEN {$offset} AND {$rowCount}" : null;
$this->_where .= " AND (log_action != 'Initialization') {$tempClause}";
}
/**
* Constructor method
*/
function __construct()
{
$this->setUserSelectList();
$this->setCountrySelectList();
$this->setCaseStatusSelectList();
$this->_columns = array('projectintake' => array('alias' => 'pi', 'fields' => array('case_id' => array('no_display' => TRUE, 'required' => TRUE), 'customer_id' => array('no_display' => TRUE, 'required' => TRUE), 'customer_name' => array('title' => ts('Client'), 'required' => TRUE), 'customer_country_name' => array('title' => ts('Client Country'), 'default' => TRUE), 'case_subject' => array('title' => ts('Case Subject'), 'default' => TRUE), 'case_status' => array('title' => ts('Case Status'), 'default' => TRUE), 'date_submission' => array('title' => ts('Date Submission'), 'required' => TRUE), 'representative_id' => array('no_display' => TRUE, 'required' => TRUE), 'representative_name' => array('title' => ts('Representative'), 'default' => TRUE), 'assess_rep_date' => array('title' => ts('Date Assessment Rep'), 'default' => TRUE), 'assess_rep_customer' => array('title' => ts('Customer Approved by Rep'), 'default' => TRUE), 'assess_cc_date' => array('title' => ts('Date Intake CC'), 'default' => TRUE), 'assess_cc_customer' => array('title' => ts('Customer Approved by CC'), 'default' => TRUE), 'assess_sc_date' => array('title' => ts('Date Intake SC'), 'default' => TRUE), 'assess_sc_customer' => array('title' => ts('Customer Approved by SC'), 'default' => TRUE), 'assess_anamon_date' => array('title' => ts('Date Intake Anamon'), 'default' => TRUE), 'assess_anamon_customer' => array('title' => ts('Customer Approved by Anamon'), 'default' => TRUE)), 'filters' => array('user_id' => array('title' => ts('Project Intake for user'), 'default' => 1, 'pseudofield' => 1, 'type' => CRM_Utils_Type::T_INT, 'operatorType' => CRM_Report_Form::OP_SELECT, 'options' => $this->_userSelectList), 'country_id' => array('title' => ts('Country'), 'operatorType' => CRM_Report_Form::OP_MULTISELECT, 'options' => $this->_countrySelectList), 'case_status_id' => array('title' => ts('Case Status'), 'operatorType' => CRM_Report_Form::OP_MULTISELECT, 'options' => $this->_caseStatusSelectList), 'date_submission' => array('title' => ts('Date Submission'), 'operatorType' => CRM_Report_Form::OP_DATE, 'type' => CRM_Utils_Type::T_DATE)), 'order_bys' => array('customer_name' => array('title' => 'Customer'), 'customer_country_name' => array('title' => 'Country'), 'date_submission' => array('title' => 'Date Submission'))));
parent::__construct();
}
function select($recordType = NULL)
{
if (!array_key_exists("contact_{$recordType}", $this->_params['fields']) && $recordType != 'final') {
$this->_nonDisplayFields[] = "civicrm_contact_contact_{$recordType}";
$this->_params['fields']["contact_{$recordType}"] = 1;
}
parent::select();
if ($recordType == 'final' && !empty($this->_nonDisplayFields)) {
foreach ($this->_nonDisplayFields as $fieldName) {
unset($this->_columnHeaders[$fieldName]);
}
}
if (empty($this->_selectAliasesTotal)) {
$this->_selectAliasesTotal = $this->_selectAliases;
}
$removeKeys = array();
if ($recordType == 'target') {
foreach ($this->_selectClauses as $key => $clause) {
if (strstr($clause, 'civicrm_contact_assignee.') || strstr($clause, 'civicrm_contact_source.') || strstr($clause, 'civicrm_email_assignee.') || strstr($clause, 'civicrm_email_source.')) {
$removeKeys[] = $key;
unset($this->_selectClauses[$key]);
}
}
} else {
if ($recordType == 'assignee') {
foreach ($this->_selectClauses as $key => $clause) {
if (strstr($clause, 'civicrm_contact_target.') || strstr($clause, 'civicrm_contact_source.') || strstr($clause, 'civicrm_email_target.') || strstr($clause, 'civicrm_email_source.')) {
$removeKeys[] = $key;
unset($this->_selectClauses[$key]);
}
}
} else {
if ($recordType == 'source') {
foreach ($this->_selectClauses as $key => $clause) {
if (strstr($clause, 'civicrm_contact_target.') || strstr($clause, 'civicrm_contact_assignee.') || strstr($clause, 'civicrm_email_target.') || strstr($clause, 'civicrm_email_assignee.')) {
$removeKeys[] = $key;
unset($this->_selectClauses[$key]);
}
}
} else {
if ($recordType == 'final') {
$this->_selectClauses = $this->_selectAliasesTotal;
foreach ($this->_selectClauses as $key => $clause) {
if (strstr($clause, 'civicrm_contact_contact_target') || strstr($clause, 'civicrm_contact_contact_assignee') || strstr($clause, 'civicrm_contact_contact_source')) {
$this->_selectClauses[$key] = "GROUP_CONCAT({$clause} SEPARATOR ';') as {$clause}";
}
}
}
}
}
}
if ($recordType) {
foreach ($removeKeys as $key) {
unset($this->_selectAliases[$key]);
}
$this->_select = "SELECT " . implode(', ', $this->_selectClauses) . " ";
}
}
/**
* Constructor method
*/
function __construct()
{
$this->setReportUserContext();
$this->setUserSelectList();
$this->setCountrySelectList();
$this->setCustomerSelectList();
$this->_columns = array('project' => array('alias' => 'project', 'fields' => array('project_id' => array('no_display' => TRUE, 'required' => TRUE), 'project_name' => array('title' => ts('Project Name'), 'required' => TRUE), 'start_date' => array('title' => ts('Start Date'), 'default' => TRUE), 'end_date' => array('title' => ts('End Date'), 'default' => TRUE), 'projectmanager_id' => array('no_display' => TRUE, 'required' => TRUE), 'projectmanager_name' => array('title' => ts('Project Manager'), 'default' => TRUE), 'programme_id' => array('no_display' => TRUE, 'required' => TRUE), 'programme_name' => array('title' => ts('Programme'), 'default' => TRUE), 'country_id' => array('no_display' => TRUE, 'required' => TRUE), 'customer_id' => array('no_display' => TRUE, 'required' => TRUE), 'customer_name' => array('title' => ts('Customer or Country'), 'default' => TRUE), 'country_name' => array('no_display' => TRUE, 'required' => TRUE), 'anamon_id' => array('no_display' => TRUE, 'required' => TRUE), 'country_coordinator_id' => array('no_display' => TRUE, 'required' => TRUE), 'project_officer_id' => array('no_display' => TRUE, 'required' => TRUE), 'sector_coordinator_id' => array('no_display' => TRUE, 'required' => TRUE), 'programme_manager_id' => array('no_display' => TRUE, 'required' => TRUE)), 'filters' => array('user_id' => array('title' => ts('Projects for user'), 'default' => 1, 'pseudofield' => 1, 'type' => CRM_Utils_Type::T_INT, 'operatorType' => CRM_Report_Form::OP_SELECT, 'options' => $this->_userSelectList), 'project_name' => array('title' => ts('Project'), 'type' => CRM_Utils_Type::T_STRING, 'operator' => 'like'), 'country_id' => array('title' => ts('Country'), 'operatorType' => CRM_Report_Form::OP_MULTISELECT, 'options' => $this->_countrySelectList), 'start_date' => array('title' => ts('Start Date'), 'operatorType' => CRM_Report_Form::OP_DATE, 'type' => CRM_Utils_Type::T_DATE), 'end_date' => array('title' => ts('End Date'), 'operatorType' => CRM_Report_Form::OP_DATE, 'type' => CRM_Utils_Type::T_DATE), 'customer_id' => array('title' => ts('Customer'), 'operatorType' => CRM_Report_Form::OP_MULTISELECT, 'options' => $this->_customerSelectList))));
parent::__construct();
}
/**
* Constructor method
*/
function __construct()
{
$this->_caseTypes = CRM_Case_PseudoConstant::caseType();
$this->_caseStatus = CRM_Case_PseudoConstant::caseStatus();
$this->setUserSelectList();
$this->_deletedLabels = array('' => ts('- select -'), 0 => ts('No'), 1 => ts('Yes'));
$this->_columns = array('pum_main' => array('fields' => array('case_id' => array('no_display' => TRUE, 'required' => TRUE), 'customer_name' => array('name' => 'customer_name', 'title' => ts('Client'), 'required' => TRUE), 'customer_id' => array('name' => 'customer_id', 'no_display' => TRUE, 'required' => TRUE), 'country_name' => array('name' => 'country_name', 'title' => ts('Country'), 'default' => TRUE), 'representative' => array('name' => 'representative', 'title' => ts('Representative'), 'default' => TRUE), 'representative_id' => array('name' => 'representative_id', 'no_display' => TRUE, 'required' => TRUE), 'case_type' => array('name' => 'case_type_id', 'title' => ts('Case Type'), 'default' => TRUE), 'case_status' => array('name' => 'case_status_id', 'title' => ts('Case Status'), 'default' => TRUE), 'expert' => array('name' => 'expert', 'title' => ts('Expert'), 'default' => TRUE), 'expert_id' => array('name' => 'expert_id', 'no_display' => TRUE, 'required' => TRUE), 'start_date' => array('name' => 'start_date', 'title' => ts('Activity Start Date'), 'default' => TRUE), 'end_date' => array('name' => 'end_date', 'title' => ts('Activity End Date'), 'default' => TRUE), 'ma_expert_approval' => array('name' => 'ma_expert_approval', 'title' => ts('Expert approves Main. Act'), 'default' => TRUE), 'pq_approved_sc' => array('name' => 'pq_approved_sc', 'title' => ts('PQ approved by SC'), 'default' => TRUE), 'pq_approved_cc' => array('name' => 'pq_approved_cc', 'title' => ts('PQ approved by CC'), 'default' => TRUE), 'cust_approves_expert' => array('name' => 'cust_approves_expert', 'title' => ts('Customer approves Expert'), 'default' => TRUE), 'briefing_date' => array('name' => 'briefing_date', 'title' => ts('Briefing Date'), 'default' => TRUE), 'briefing_status' => array('name' => 'briefing_status', 'title' => ts('Briefing Status'), 'default' => TRUE)), 'filters' => array('user_id' => array('title' => ts('Main Activities for User'), 'default' => 0, 'pseudofield' => 1, 'type' => CRM_Utils_Type::T_INT, 'operatorType' => CRM_Report_Form::OP_SELECT, 'options' => $this->_userSelectList), 'case_type_id' => array('title' => ts('Case Type'), 'operatorType' => CRM_Report_Form::OP_MULTISELECT, 'options' => $this->_caseTypes), 'case_status_id' => array('title' => ts('Status'), 'operatorType' => CRM_Report_Form::OP_MULTISELECT, 'options' => $this->_caseStatus)), 'order_bys' => array('start_date' => array('title' => ts('Activity Start Date'), 'name' => 'start_date', 'default' => 1))), 'case_status_weight' => array('dao' => 'CRM_Core_DAO_OptionValue', 'fields' => array('case_status_label' => array('name' => 'label', 'no_display' => TRUE, 'required' => TRUE), 'weight' => array('no_display' => TRUE, 'required' => TRUE)), 'order_bys' => array('case_status_label' => array('title' => ts('Case Status'), 'name' => 'label', 'default' => 1))));
parent::__construct();
}
/**
* Constructor method
*/
function __construct()
{
$this->_caseStatusList = CRM_Case_PseudoConstant::caseStatus();
$this->setUserSelectList();
$this->setSectorList();
$this->_deletedLabels = array('' => ts('- select -'), 0 => ts('No'), 1 => ts('Yes'));
$this->_columns = array('pum_expert' => array('fields' => array('case_id' => array('no_display' => TRUE, 'required' => TRUE), 'expert_name' => array('name' => 'expert_name', 'title' => ts('Expert'), 'required' => TRUE), 'sector_coordinator_name' => array('name' => 'sector_coordinator_name', 'title' => ts('Sector Coordinator'), 'required' => TRUE), 'status' => array('name' => 'status', 'title' => ts('Status'), 'default' => TRUE), 'sector_coordinator_id' => array('no_display' => TRUE, 'required' => TRUE), 'expert_id' => array('no_display' => TRUE, 'required' => TRUE)), 'filters' => array('user_id' => array('title' => ts('Expert Applications for User'), 'default' => 0, 'pseudofield' => 1, 'type' => CRM_Utils_Type::T_INT, 'operatorType' => CRM_Report_Form::OP_SELECT, 'options' => $this->_userSelectList), 'status_id' => array('title' => ts('Status'), 'operatorType' => CRM_Report_Form::OP_MULTISELECT, 'options' => $this->_caseStatusList)), 'order_bys' => array('status' => array('title' => ts('Case Status'), 'name' => 'status', 'default' => 1))));
parent::__construct();
}
function where($includeTemp = TRUE)
{
parent::where();
$this->_where .= "\n AND cdntax_c.id IS NULL AND {$this->_aliases['civicrm_contact']}.is_deleted = 0\n ";
if ($includeTemp && $this->_useEligibilityHooks) {
$this->_where .= "\n AND cdntax_t.contribution_id IS NOT NULL\n ";
} else {
$this->_where .= "\n AND {$this->_aliases['civicrm_contribution']}.contribution_status_id = 1\n AND {$this->_aliases['civicrm_financial_type']}.is_deductible = 1\n AND ({$this->_aliases['civicrm_contribution']}.total_amount - COALESCE({$this->_aliases['civicrm_contribution']}.non_deductible_amount,0)) > 0\n ";
}
}
public function where()
{
parent::where();
// Handle calculated end date. This can come from one of two sources:
// Either there is a specified end date for the end_date field
// Or, we calculate the end date based on the start date +
// installments * intervals using the mysql date_add function, along
// with the interval unit (e.g. DATE_ADD(start_date, INTERVAL 12 * 1 MONTH)
$date_suffixes = array('relative', 'from', 'to');
while (list(, $suffix) = each($date_suffixes)) {
$isBreak = FALSE;
// Check to see if the user wants to search by calculated date.
if (!empty($this->_params['calculated_end_date_' . $suffix])) {
// The calculated date field is in use - spring into action
// Gather values
$relative = CRM_Utils_Array::value("calculated_end_date_relative", $this->_params);
$from = CRM_Utils_Array::value("calculated_end_date_from", $this->_params);
$to = CRM_Utils_Array::value("calculated_end_date_to", $this->_params);
$end_date_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['end_date']['dbAlias'];
$end_date_type = $this->_columns['civicrm_contribution_recur']['filters']['end_date']['type'];
$start_date_type = $this->_columns['civicrm_contribution_recur']['filters']['start_date']['type'];
$frequency_unit_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['frequency_unit']['dbAlias'];
$frequency_interval_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['frequency_interval']['dbAlias'];
$installments_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['installments']['dbAlias'];
$start_date_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['start_date']['dbAlias'];
// The end date clause is simple to construct
$end_date_clause = $this->dateClause($end_date_db_alias, $relative, $from, $to, $end_date_type, NULL, NULL);
// NOTE: For the calculation based on installment, there doesn't
// seem to be a way to include the interval unit (e.g. month,
// date, etc) as a field name - so we have to build a complex
// OR statement instead.
$this->_where .= 'AND (' . $this->dateClause("DATE_ADD({$start_date_db_alias}, INTERVAL {$installments_db_alias} * COALESCE({$frequency_interval_db_alias},1) month)", $relative, $from, $to, $start_date_type, NULL, NULL);
$this->_where .= " AND {$frequency_unit_db_alias} = 'month' ) OR \n";
$this->_where .= '(' . $this->dateClause("DATE_ADD({$start_date_db_alias}, INTERVAL {$installments_db_alias} * COALESCE({$frequency_interval_db_alias},1) day)", $relative, $from, $to, $start_date_type, NULL, NULL);
$this->_where .= " AND {$frequency_unit_db_alias} = 'day' ) OR \n";
$this->_where .= '(' . $this->dateClause("DATE_ADD({$start_date_db_alias}, INTERVAL {$installments_db_alias} * COALESCE({$frequency_interval_db_alias}, 1) week)", $relative, $from, $to, $start_date_type, NULL, NULL);
$this->_where .= " AND {$frequency_unit_db_alias} = 'week' ) OR \n";
$this->_where .= '(' . $this->dateClause("DATE_ADD({$start_date_db_alias}, INTERVAL {$installments_db_alias} * COALESCE({$frequency_interval_db_alias}, 1) year)", $relative, $from, $to, $start_date_type, NULL, NULL);
$this->_where .= " AND {$frequency_unit_db_alias} = 'year' )\n AND (({$end_date_db_alias} IS NOT NULL AND {$end_date_clause})\n OR ({$installments_db_alias} IS NOT NULL))\n";
$isBreak = TRUE;
}
if (!empty($this->_params['contribution_processed_date_' . $suffix])) {
$relative = CRM_Utils_Array::value("contribution_processed_date_relative", $this->_params);
$from = CRM_Utils_Array::value("contribution_processed_date_from", $this->_params);
$to = CRM_Utils_Array::value("contribution_processed_date_to", $this->_params);
$this->_where .= " AND contribution_civireport.id IN (SELECT MAX(cc.id) FROM civicrm_contribution cc WHERE cc.contribution_status_id = 1 AND (" . $this->dateClause("DATE(cc.receive_date)", $relative, $from, $to, CRM_Utils_Type::T_DATE, NULL, NULL) . " ) GROUP BY cc.contribution_recur_id)";
$isBreak = TRUE;
}
if (!empty($isBreak)) {
break;
}
}
}
function preProcess()
{
parent::preProcess();
if (!$this->_id) {
if (CRM_Utils_Array::value('title', $_POST)) {
$this->assign('reportTitle', $_POST['title']);
} else {
if (CRM_Utils_Array::value('weekday_value', $_POST)) {
$this->assign('reportTitle', "EXTENDED CARE FOR " . strtoupper($_POST['weekday_value']));
}
}
}
}
function statistics(&$rows)
{
$statistics = parent::statistics($rows);
if (!$this->_having) {
$select = "\n SELECT COUNT({$this->_aliases['civicrm_pledge']}.amount ) as count,\n SUM({$this->_aliases['civicrm_pledge']}.amount ) as amount,\n ROUND(AVG({$this->_aliases['civicrm_pledge']}.amount), 2) as avg\n ";
$sql = "{$select} {$this->_from} {$this->_where}";
$dao = CRM_Core_DAO::executeQuery($sql);
if ($dao->fetch()) {
$statistics['counts']['amount'] = array('value' => $dao->amount, 'title' => 'Total Pledged', 'type' => CRM_Utils_Type::T_MONEY);
$statistics['counts']['count '] = array('value' => $dao->count, 'title' => 'Total No Pledges');
$statistics['counts']['avg '] = array('value' => $dao->avg, 'title' => 'Average', 'type' => CRM_Utils_Type::T_MONEY);
}
}
return $statistics;
}
/**
* This function is to build the date range - relative or absolute
*
* @param Object $form the form object that we are operating on
*
* @static
* @access public
*/
static function buildDateRange(&$form, $fieldName, $count = 1, $from = '_from', $to = '_to', $fromLabel = 'From:', $required = FALSE, $addReportFilters = TRUE, $dateFormat = 'searchDate')
{
$selector = array(ts('Choose Date Range'), 'this.year' => ts('This Year'), 'this.fiscal_year' => ts('This Fiscal Year'), 'this.quarter' => ts('This Quarter'), 'this.month' => ts('This Month'), 'this.week' => ts('This Week'), 'this.day' => ts('This Day'), 'previous.year' => ts('Previous Year'), 'previous.fiscal_year' => ts('Previous Fiscal Year'), 'previous.quarter' => ts('Previous Quarter'), 'previous.month' => ts('Previous Month'), 'previous.week' => ts('Previous Week'), 'previous.day' => ts('Previous Day'), 'previous_before.year' => ts('Prior to Previous Year'), 'previous_before.quarter' => ts('Prior to Previous Quarter'), 'previous_before.month' => ts('Prior to Previous Month'), 'previous_before.week' => ts('Prior to Previous Week'), 'previous_before.day' => ts('Prior to Previous Day'), 'previous_2.year' => ts('Previous 2 Years'), 'previous_2.quarter' => ts('Previous 2 Quarters'), 'previous_2.month' => ts('Previous 2 Months'), 'previous_2.week' => ts('Previous 2 Weeks'), 'previous_2.day' => ts('Previous 2 Days'), 'earlier.year' => ts('To End of Prior Year'), 'earlier.quarter' => ts('To End of Prior Quarter'), 'earlier.month' => ts('To End of Prior Month'), 'earlier.week' => ts('To End of Prior Week'), 'earlier.day' => ts('To End of Prior Day'), 'greater.year' => ts('Current Year to-date'), 'greater.quarter' => ts('Current Quarter to-date'), 'greater.month' => ts('Current Month to-date'), 'greater.week' => ts('Current Week to-date'), 'greater.day' => ts('Current Day'), 'ending.year' => ts('From 12 Months Ago'), 'ending.quarter' => ts('From 3 Months Ago'), 'ending.month' => ts('From 1 Month Ago'), 'ending.week' => ts('From 1 Week Ago'));
if ($addReportFilters) {
$selector += CRM_Report_Form::getOperationPair(CRM_Report_FORM::OP_DATE);
}
$config = CRM_Core_Config::singleton();
//if fiscal year start on 1 jan then remove fiscal year task
//form list
if ($config->fiscalYearStart['d'] == 1 & $config->fiscalYearStart['M'] == 1) {
unset($selector['this.fiscal_year']);
unset($selector['previous.fiscal_year']);
}
$form->add('select', "{$fieldName}_relative", ts('Relative Date Range'), $selector, $required, array('onclick' => "showAbsoluteRange(this.value, '{$fieldName}_relative');"));
$form->addDateRange($fieldName, $from, $to, $fromLabel, $dateFormat);
}
function statistics(&$rows)
{
$statistics = parent::statistics($rows);
$avg = null;
$select = " SELECT SUM( {$this->_aliases['civicrm_line_item']}.participant_count ) as count,\n\t\t\t\t\t\t\t\t\tSUM( {$this->_aliases['civicrm_line_item']}.line_total )\t as amount\n\t\t\t\t\t\t";
$sql = "{$select} {$this->_from} {$this->_where}";
$dao = CRM_Core_DAO::executeQuery($sql);
if ($dao->fetch()) {
if ($dao->count && $dao->amount) {
$avg = $dao->amount / $dao->count;
}
$statistics['counts']['count'] = array('value' => $dao->count, 'title' => 'Total Participants', 'type' => CRM_Utils_Type::T_INT);
$statistics['counts']['amount'] = array('value' => $dao->amount, 'title' => 'Total Income', 'type' => CRM_Utils_Type::T_MONEY);
$statistics['counts']['avg '] = array('value' => $avg, 'title' => 'Average', 'type' => CRM_Utils_Type::T_MONEY);
}
return $statistics;
}
/**
* This function is to build the date range - relative or absolute
*
* @param Object $form the form object that we are operating on
*
* @static
* @access public
*/
static function buildDateRange(&$form, $fieldName, $count = 1, $required = false, $addReportFilters = true)
{
$selector = array(ts('Choose Date Range'), 'this.year' => ts('This Year'), 'this.fiscal_year' => ts('This Fiscal Year'), 'this.quarter' => ts('This Quarter'), 'this.month' => ts('This Month'), 'this.week' => ts('This Week'), 'this.day' => ts('This Day'), 'previous.year' => ts('Previous Year'), 'previous.fiscal_year' => ts('Previous Fiscal Year'), 'previous.quarter' => ts('Previous Quarter'), 'previous.month' => ts('Previous Month'), 'previous.week' => ts('Previous Week'), 'previous.day' => ts('Previous Day'), 'previous_before.year' => ts('Previous Before Year'), 'previous_before.quarter' => ts('Previous Before Quarter'), 'previous_before.month' => ts('Previous Before Month'), 'previous_before.week' => ts('Previous Before Week'), 'previous_before.day' => ts('Previous Before Day'), 'previous_2.year' => ts('Previous 2 Years'), 'previous_2.quarter' => ts('Previous 2 Quarters'), 'previous_2.month' => ts('Previous 2 Months'), 'previous_2.week' => ts('Previous 2 Weeks'), 'previous_2.day' => ts('Previous 2 Days'), 'earlier.year' => ts('Earlier Year'), 'earlier.quarter' => ts('Earlier Quarter'), 'earlier.month' => ts('Earlier Month'), 'earlier.week' => ts('Earlier Week'), 'earlier.day' => ts('Earlier Day'), 'greater.year' => ts('Greater Year'), 'greater.quarter' => ts('Greater Quarter'), 'greater.month' => ts('Greater Month'), 'greater.week' => ts('Greater Week'), 'greater.day' => ts('Greater Day'));
if ($addReportFilters) {
require_once 'CRM/Report/Form.php';
$selector += CRM_Report_Form::getOperationPair(CRM_Report_FORM::OP_DATE);
}
$config =& CRM_Core_Config::singleton();
//if fiscal year start on 1 jan then remove fiscal year task
//form list
if ($config->fiscalYearStart['d'] == 1 & $config->fiscalYearStart['M'] == 1) {
unset($selector['this.fiscal_year']);
unset($selector['previous.fiscal_year']);
}
$form->add('select', "{$fieldName}_relative", ts('Relative Date Range'), $selector, $required, array('onclick' => "showAbsoluteRange(this.value, '{$fieldName}_relative');"));
$form->addDateRange($fieldName);
}
function __construct()
{
$this->_add2groupSupported = false;
// don’t display the ‘Add these Contacts to Group’ button
$dsn = defined('CIVICRM_LOGGING_DSN') ? DB::parseDSN(CIVICRM_LOGGING_DSN) : DB::parseDSN(CIVICRM_DSN);
$this->loggingDB = $dsn['database'];
$this->log_conn_id = CRM_Utils_Request::retrieve('log_conn_id', 'Integer', CRM_Core_DAO::$_nullObject);
$this->log_date = CRM_Utils_Request::retrieve('log_date', 'String', CRM_Core_DAO::$_nullObject);
// make sure the report works even without the params
if (!$this->log_conn_id or !$this->log_date) {
$dao = new CRM_Core_DAO();
$dao->query("SELECT log_conn_id, log_date FROM `{$this->loggingDB}`.log_civicrm_contact WHERE log_action = 'Update' ORDER BY log_date DESC LIMIT 1");
$dao->fetch();
$this->log_conn_id = $dao->log_conn_id;
$this->log_date = $dao->log_date;
}
$this->_columnHeaders = array('field' => array('title' => ts('Field')), 'from' => array('title' => ts('Changed From')), 'to' => array('title' => ts('Changed To')));
parent::__construct();
}
static function dateParam($fieldName, &$field, &$defaults)
{
// type = 12 (datetime) is not recognized by Utils_Type::escape() method,
// and therefore the below hack
$type = 4;
$from = self::getTypedValue("{$fieldName}_from", $type);
$to = self::getTypedValue("{$fieldName}_to", $type);
$relative = CRM_Utils_Array::value("{$fieldName}_relative", $_GET);
if ($relative) {
list($from, $to) = CRM_Report_Form::getFromTo($relative, null, null);
}
if (!($from || $to)) {
return false;
} else {
if ($from || $to || $relative) {
// unset other criteria
self::unsetFilters($defaults);
}
}
$defaults["{$fieldName}_from"] = CRM_Utils_Date::unformat($from, '');
$defaults["{$fieldName}_to"] = CRM_Utils_Date::unformat($to, '');
}
function __construct()
{
self::$nscd_fid = _iats_civicrm_nscd_fid();
self::$version = _iats_civicrm_domain_info('version');
self::$financial_types = self::$version[0] <= 4 && self::$version[1] <= 2 ? array() : CRM_Contribute_PseudoConstant::financialType();
if (self::$version[0] <= 4 && self::$version[1] < 4) {
self::$prefixes = CRM_Core_PseudoConstant::individualPrefix();
self::$contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
} else {
self::$prefixes = CRM_Contact_BAO_Contact::buildOptions('individual_prefix_id');
self::$contributionStatus = CRM_Contribute_BAO_Contribution::buildOptions('contribution_status_id');
}
$params = array('version' => 3, 'sequential' => 1, 'is_test' => 0, 'return.name' => 1);
$result = civicrm_api('PaymentProcessor', 'get', $params);
foreach ($result['values'] as $pp) {
self::$processors[$pp['id']] = $pp['name'];
}
$this->_columns = array('civicrm_contact' => array('dao' => 'CRM_Contact_DAO_Contact', 'order_bys' => array('sort_name' => array('title' => ts("Last name, First name"))), 'fields' => array('first_name' => array('title' => ts('First Name')), 'last_name' => array('title' => ts('Last Name')), 'prefix_id' => array('title' => ts('Prefix')), 'sort_name' => array('title' => ts('Contact Name'), 'no_repeat' => TRUE, 'default' => TRUE), 'id' => array('no_display' => TRUE, 'required' => TRUE))), 'civicrm_email' => array('dao' => 'CRM_Core_DAO_Email', 'order_bys' => array('email' => array('title' => ts('Email'))), 'fields' => array('email' => array('title' => ts('Email'), 'no_repeat' => TRUE)), 'grouping' => 'contact-fields'), 'civicrm_phone' => array('dao' => 'CRM_Core_DAO_Phone', 'fields' => array('phone' => array('title' => ts('Phone'), 'no_repeat' => TRUE)), 'grouping' => 'contact-fields'), 'civicrm_contribution' => array('dao' => 'CRM_Contribute_DAO_Contribution', 'fields' => array('id' => array('title' => ts('Contribution ID(s)'), 'required' => TRUE, 'dbAlias' => "GROUP_CONCAT(contribution_civireport.id SEPARATOR ', ')"), 'total_amount' => array('title' => ts('Amount Contributed to date'), 'required' => TRUE, 'statistics' => array('sum' => ts("Total Amount contributed")))), 'filters' => array('total_amount' => array('title' => ts('Total Amount'), 'operatorType' => CRM_Report_Form::OP_FLOAT, 'type' => CRM_Utils_Type::T_FLOAT))), 'civicrm_iats_customer_codes' => array('dao' => 'CRM_Contribute_DAO_Contribution', 'order_bys' => array('expiry' => array('title' => ts("Expiry Date"))), 'fields' => array('customer_code' => array('title' => 'customer code', 'default' => TRUE), 'expiry' => array('title' => 'Expiry Date', 'default' => TRUE))), 'civicrm_contribution_recur' => array('dao' => 'CRM_Contribute_DAO_ContributionRecur', 'order_bys' => array('id' => array('title' => ts("Series ID")), 'amount' => array('title' => ts("Current Amount")), 'start_date' => array('title' => ts('Start Date')), 'modified_date' => array('title' => ts('Modified Date')), self::$nscd_fid => array('title' => ts('Next Scheduled Contribution Date')), 'cycle_day' => array('title' => ts('Cycle Day')), 'payment_processor_id' => array('title' => ts('Payment Processor'))), 'fields' => array('id' => array('required' => TRUE, 'title' => ts("Series ID")), 'recur_id' => array('name' => 'id', 'title' => ts('Series ID')), 'invoice_id' => array('title' => ts('Invoice ID'), 'default' => FALSE), 'currency' => array('title' => ts("Currency")), 'amount' => array('title' => ts('Amount'), 'default' => TRUE), 'contribution_status_id' => array('title' => ts('Donation Status')), 'frequency_interval' => array('title' => ts('Frequency interval'), 'default' => TRUE), 'frequency_unit' => array('title' => ts('Frequency unit'), 'default' => TRUE), 'installments' => array('title' => ts('Installments'), 'default' => TRUE), 'start_date' => array('title' => ts('Start Date'), 'default' => TRUE), 'create_date' => array('title' => ts('Create Date')), 'modified_date' => array('title' => ts('Modified Date')), 'cancel_date' => array('title' => ts('Cancel Date')), self::$nscd_fid => array('title' => ts('Next Scheduled Contribution Date'), 'default' => TRUE), 'next_scheduled_day' => array('name' => self::$nscd_fid, 'dbAlias' => 'DAYOFMONTH(contribution_recur_civireport.next_sched_contribution)', 'title' => ts('Next Scheduled Day of the Month')), 'cycle_day' => array('title' => ts('Cycle Day')), 'failure_count' => array('title' => ts('Failure Count')), 'failure_retry_date' => array('title' => ts('Failure Retry Date')), 'payment_processor_id' => array('title' => ts('Payment Processor'), 'default' => TRUE)), 'filters' => array('contribution_status_id' => array('title' => ts('Donation Status'), 'operatorType' => CRM_Report_Form::OP_MULTISELECT, 'options' => self::$contributionStatus, 'default' => array(5), 'type' => CRM_Utils_Type::T_INT), 'payment_processor_id' => array('title' => ts('Payment Processor'), 'operatorType' => CRM_Report_Form::OP_MULTISELECT, 'options' => self::$processors, 'type' => CRM_Utils_Type::T_INT), 'currency' => array('title' => 'Currency', 'operatorType' => CRM_Report_Form::OP_MULTISELECT, 'options' => CRM_Core_OptionGroup::values('currencies_enabled'), 'default' => NULL, 'type' => CRM_Utils_Type::T_STRING), 'financial_type_id' => array('title' => ts('Financial Type'), 'operatorType' => CRM_Report_Form::OP_MULTISELECT, 'options' => self::$financial_types, 'type' => CRM_Utils_Type::T_INT), 'frequency_unit' => array('title' => ts('Frequency Unit'), 'operatorType' => CRM_Report_Form::OP_MULTISELECT, 'options' => CRM_Core_OptionGroup::values('recur_frequency_units')), self::$nscd_fid => array('title' => ts('Next Scheduled Contribution Date'), 'operatorType' => CRM_Report_Form::OP_DATE, 'type' => CRM_Utils_Type::T_DATE), 'next_scheduled_day' => array('title' => ts('Next Scheduled Day'), 'operatorType' => CRM_Report_Form::OP_INT, 'type' => CRM_Utils_Type::T_INT), 'cycle_day' => array('title' => ts('Cycle Day'), 'operatorType' => CRM_Report_Form::OP_INT, 'type' => CRM_Utils_Type::T_INT), 'start_date' => array('title' => ts('Start Date'), 'operatorType' => CRM_Report_Form::OP_DATE, 'type' => CRM_Utils_Type::T_DATE), 'modified_date' => array('title' => ts('Modified Date'), 'operatorType' => CRM_Report_Form::OP_DATE, 'type' => CRM_Utils_Type::T_DATE), 'cancel_date' => array('title' => ts('Cancel Date'), 'operatorType' => CRM_Report_Form::OP_DATE, 'type' => CRM_Utils_Type::T_DATE))), 'civicrm_address' => array('dao' => 'CRM_Core_DAO_Address', 'fields' => array('street_address' => array('title' => ts('Address'), 'default' => FALSE), 'supplemental_address_1' => array('title' => ts('Supplementary Address Field 1'), 'default' => FALSE), 'supplemental_address_2' => array('title' => ts('Supplementary Address Field 2'), 'default' => FALSE), 'city' => array('title' => 'City', 'default' => FALSE), 'state_province_id' => array('title' => 'Province', 'default' => FALSE, 'alter_display' => 'alterStateProvinceID'), 'postal_code' => array('title' => 'Postal Code', 'default' => FALSE), 'country_id' => array('title' => 'Country', 'default' => FALSE, 'alter_display' => 'alterCountryID')), 'grouping' => 'contact-fields'));
if (empty(self::$financial_types)) {
unset($this->_columns['civicrm_contribution_recur']['filters']['financial_type_id']);
}
parent::__construct();
}
/**
* @param $rows
*
* @return array
*/
public function statistics(&$rows)
{
$statistics = parent::statistics($rows);
//fetch contributions for both date ranges from pre-existing temp tables
$sql = "\nCREATE TEMPORARY TABLE civicrm_temp_civireport_repeat3\nSELECT contact_id FROM civicrm_temp_civireport_repeat1 UNION SELECT contact_id FROM civicrm_temp_civireport_repeat2;";
$dao = CRM_Core_DAO::executeQuery($sql);
$sql = "\nSELECT civicrm_temp_civireport_repeat3.contact_id,\n civicrm_temp_civireport_repeat1.total_amount_sum as contribution1_total_amount_sum,\n civicrm_temp_civireport_repeat2.total_amount_sum as contribution2_total_amount_sum\nFROM civicrm_temp_civireport_repeat3\nLEFT JOIN civicrm_temp_civireport_repeat1\n ON civicrm_temp_civireport_repeat3.contact_id = civicrm_temp_civireport_repeat1.contact_id\nLEFT JOIN civicrm_temp_civireport_repeat2\n ON civicrm_temp_civireport_repeat3.contact_id = civicrm_temp_civireport_repeat2.contact_id";
$dao = CRM_Core_DAO::executeQuery($sql);
//store contributions in array 'contact_sums' for comparison
$contact_sums = array();
while ($dao->fetch()) {
$contact_sums[$dao->contact_id] = array('contribution1_total_amount_sum' => $dao->contribution1_total_amount_sum, 'contribution2_total_amount_sum' => $dao->contribution2_total_amount_sum);
}
$total_distinct_contacts = count($contact_sums);
$maintained = 0;
$upgraded = 0;
$downgraded = 0;
$new = 0;
$lapsed = 0;
foreach ($contact_sums as $uid => $row) {
if ($row['contribution1_total_amount_sum'] && $row['contribution2_total_amount_sum']) {
$change = $row['contribution1_total_amount_sum'] - $row['contribution2_total_amount_sum'];
if ($change == 0) {
$maintained += 1;
} elseif ($change > 0) {
$upgraded += 1;
} elseif ($change < 0) {
$downgraded += 1;
}
} elseif ($row['contribution1_total_amount_sum']) {
$new += 1;
} elseif ($row['contribution2_total_amount_sum']) {
$lapsed += 1;
}
}
//calculate percentages from numbers
if (!empty($total_distinct_contacts)) {
$maintained = $maintained / $total_distinct_contacts * 100;
$upgraded = $upgraded / $total_distinct_contacts * 100;
$downgraded = $downgraded / $total_distinct_contacts * 100;
$new = $new / $total_distinct_contacts * 100;
$lapsed = $lapsed / $total_distinct_contacts * 100;
}
//display percentages for new, lapsed, upgraded, downgraded, and maintained contributors
$statistics['counts']['count_new'] = array('value' => $new, 'title' => '% New Donors');
$statistics['counts']['count_lapsed'] = array('value' => $lapsed, 'title' => '% Lapsed Donors');
$statistics['counts']['count_upgraded'] = array('value' => $upgraded, 'title' => '% Upgraded Donors');
$statistics['counts']['count_downgraded'] = array('value' => $downgraded, 'title' => '% Downgraded Donors');
$statistics['counts']['count_maintained'] = array('value' => $maintained, 'title' => '% Maintained Donors');
$select = "\nSELECT COUNT({$this->_aliases['civicrm_contribution']}1.total_amount_count ) as count,\n SUM({$this->_aliases['civicrm_contribution']}1.total_amount_sum ) as amount,\n ROUND(AVG({$this->_aliases['civicrm_contribution']}1.total_amount_sum), 2) as avg,\n COUNT({$this->_aliases['civicrm_contribution']}2.total_amount_count ) as count2,\n SUM({$this->_aliases['civicrm_contribution']}2.total_amount_sum ) as amount2,\n ROUND(AVG({$this->_aliases['civicrm_contribution']}2.total_amount_sum), 2) as avg2,\n currency";
$sql = "{$select} {$this->_from} {$this->_where}\nGROUP BY currency\n";
$dao = CRM_Core_DAO::executeQuery($sql);
$amount = $average = $amount2 = $average2 = array();
$count = $count2 = 0;
while ($dao->fetch()) {
if ($dao->amount) {
$amount[] = CRM_Utils_Money::format($dao->amount, $dao->currency) . "(" . $dao->count . ")";
$average[] = CRM_Utils_Money::format($dao->avg, $dao->currency);
}
$count += $dao->count;
if ($dao->amount2) {
$amount2[] = CRM_Utils_Money::format($dao->amount2, $dao->currency) . "(" . $dao->count . ")";
$average2[] = CRM_Utils_Money::format($dao->avg2, $dao->currency);
}
$count2 += $dao->count2;
}
$statistics['counts']['range_one_title'] = array('title' => 'Initial Date Range:');
$statistics['counts']['amount'] = array('value' => implode(', ', $amount), 'title' => 'Total Amount', 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['count'] = array('value' => $count, 'title' => 'Total Donations');
$statistics['counts']['avg'] = array('value' => implode(', ', $average), 'title' => 'Average', 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['range_two_title'] = array('title' => 'Second Date Range:');
$statistics['counts']['amount2'] = array('value' => implode(', ', $amount2), 'title' => 'Total Amount', 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['count2'] = array('value' => $count2, 'title' => 'Total Donations');
$statistics['counts']['avg2'] = array('value' => implode(', ', $average2), 'title' => 'Average', 'type' => CRM_Utils_Type::T_STRING);
return $statistics;
}
/**
* Override "This Year" $op options
* @param string $type
* @param null $fieldName
*
* @return array
*/
public function getOperationPair($type = "string", $fieldName = NULL)
{
if ($fieldName == 'yid') {
return array('calendar' => ts('Is Calendar Year'), 'fiscal' => ts('Fiscal Year Starting'));
}
return parent::getOperationPair($type, $fieldName);
}
/**
* @param $rows
*
* @return array
*/
public function statistics(&$rows)
{
$statistics = parent::statistics($rows);
$tempTableName = CRM_Core_DAO::createTempTableName('civicrm_contribution');
$select = "SELECT {$this->_aliases['civicrm_contribution']}.id, {$this->_aliases['civicrm_entity_financial_trxn']}.id as trxnID, {$this->_aliases['civicrm_contribution']}.currency,\n CASE\n WHEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id IS NOT NULL\n THEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.amount\n ELSE {$this->_aliases['civicrm_entity_financial_trxn']}.amount\n END as amount\n";
$tempQuery = "CREATE TEMPORARY TABLE {$tempTableName} CHARACTER SET utf8 COLLATE utf8_unicode_ci AS\n {$select} {$this->_from} {$this->_where} {$this->_groupBy} ";
CRM_Core_DAO::executeQuery($tempQuery);
$sql = "SELECT COUNT(trxnID) as count, SUM(amount) as amount, currency\n FROM {$tempTableName}\n GROUP BY currency";
$dao = CRM_Core_DAO::executeQuery($sql);
$amount = $avg = array();
while ($dao->fetch()) {
$amount[] = CRM_Utils_Money::format($dao->amount, $dao->currency);
$avg[] = CRM_Utils_Money::format(round($dao->amount / $dao->count, 2), $dao->currency);
}
$statistics['counts']['amount'] = array('value' => implode(', ', $amount), 'title' => 'Total Amount', 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['avg'] = array('value' => implode(', ', $avg), 'title' => 'Average', 'type' => CRM_Utils_Type::T_STRING);
return $statistics;
}
function preProcess()
{
$this->assign('reportTitle', ts('Management Summary Report'));
parent::preProcess();
}
function preProcess()
{
parent::preProcess();
}
function postProcess()
{
parent::postProcess();
}
function alterDisplay(&$rows)
{
parent::alterDisplay($rows);
//THis is all generic functionality which can hopefully go into the parent class
// it introduces the option of defining an alter display function as part of the column definition
// @tod tidy up the iteration so it happens in this function
list($firstRow) = $rows;
// no result to alter
if (empty($firstRow)) {
return;
}
$selectedFields = array_keys($firstRow);
$alterfunctions = $altermap = array();
foreach ($this->_columns as $tablename => $table) {
if (array_key_exists('fields', $table)) {
foreach ($table['fields'] as $field => $specs) {
if (in_array($tablename . '_' . $field, $selectedFields) && array_key_exists('alter_display', $specs)) {
$alterfunctions[$tablename . '_' . $field] = $specs['alter_display'];
$altermap[$tablename . '_' . $field] = $field;
}
}
}
}
if (empty($alterfunctions)) {
// - no manipulation to be done
return;
}
foreach ($rows as $index => &$row) {
foreach ($row as $selectedfield => $value) {
if (array_key_exists($selectedfield, $alterfunctions)) {
$rows[$index][$selectedfield] = $this->{$alterfunctions}[$selectedfield]($value, $row, $selectedfield, $altermap[$selectedfield]);
}
}
}
}
function alterDisplay(&$rows)
{
$totalStatistics = $grantStatistics = array();
$totalStatistics = parent::statistics($rows);
$awardedGrantsAmount = $grantsReceived = $totalAmount = $awardedGrants = $grantReportsReceived = 0;
$grantStatistics = array();
$grantTypes = CRM_Core_PseudoConstant::get('CRM_Grant_DAO_Grant', 'grant_type_id');
$countries = CRM_Core_PseudoConstant::country();
$gender = CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'gender_id');
$grantAmountTotal = "\nSELECT COUNT({$this->_aliases['civicrm_grant']}.id) as count ,\n SUM({$this->_aliases['civicrm_grant']}.amount_total) as totalAmount\n {$this->_from} ";
if (!empty($this->_whereClause)) {
$grantAmountTotal .= " {$this->_whereClause}";
}
$result = CRM_Core_DAO::executeQuery($grantAmountTotal);
while ($result->fetch()) {
$grantsReceived = $result->count;
$totalAmount = $result->totalAmount;
}
if (!$grantsReceived) {
return;
}
$grantAmountAwarded = "\nSELECT COUNT({$this->_aliases['civicrm_grant']}.id) as count ,\n SUM({$this->_aliases['civicrm_grant']}.amount_granted) as grantedAmount,\n SUM({$this->_aliases['civicrm_grant']}.amount_total) as totalAmount\n {$this->_from} ";
if (!empty($this->_where)) {
$grantAmountAwarded .= " {$this->_where}";
}
$values = CRM_Core_DAO::executeQuery($grantAmountAwarded);
while ($values->fetch()) {
$awardedGrants = $values->count;
$awardedGrantsAmount = $values->totalAmount;
$amountGranted = $values->grantedAmount;
}
foreach ($rows as $key => $values) {
if (CRM_Utils_Array::value('civicrm_grant_grant_report_received', $values)) {
$grantReportsReceived++;
}
if (CRM_Utils_Array::value('civicrm_grant_grant_type_id', $values)) {
$grantType = CRM_Utils_Array::value($values['civicrm_grant_grant_type_id'], $grantTypes);
$grantStatistics['civicrm_grant_grant_type_id']['title'] = ts('By Grant Type');
self::getStatistics($grantStatistics['civicrm_grant_grant_type_id'], $grantType, $values, $awardedGrants, $awardedGrantsAmount);
}
if (array_key_exists('civicrm_world_region_name', $values)) {
$region = CRM_Utils_Array::value('civicrm_world_region_name', $values);
$region = $region ? $region : 'Unassigned';
$grantStatistics['civicrm_world_region_name']['title'] = ts('By Region');
self::getStatistics($grantStatistics['civicrm_world_region_name'], $region, $values, $awardedGrants, $awardedGrantsAmount);
}
if (array_key_exists('civicrm_address_country_id', $values)) {
$country = CRM_Utils_Array::value($values['civicrm_address_country_id'], $countries);
$country = $country ? $country : 'Unassigned';
$grantStatistics['civicrm_address_country_id']['title'] = ts('By Country');
self::getStatistics($grantStatistics['civicrm_address_country_id'], $country, $values, $awardedGrants, $awardedGrantsAmount);
}
if ($type = CRM_Utils_Array::value('civicrm_contact_contact_type', $values)) {
$grantStatistics['civicrm_contact_contact_type']['title'] = ts('By Contact Type');
$title = "Total Number of {$type}(s)";
self::getStatistics($grantStatistics['civicrm_contact_contact_type'], $title, $values, $awardedGrants, $awardedGrantsAmount);
}
if (array_key_exists('civicrm_contact_gender_id', $values)) {
$genderLabel = CRM_Utils_Array::value($values['civicrm_contact_gender_id'], $gender);
$genderLabel = $genderLabel ? $genderLabel : 'Unassigned';
$grantStatistics['civicrm_contact_gender_id']['title'] = ts('By Gender');
self::getStatistics($grantStatistics['civicrm_contact_gender_id'], $genderLabel, $values, $awardedGrants, $awardedGrantsAmount);
}
foreach ($values as $customField => $customValue) {
if (strstr($customField, 'civicrm_value_')) {
$customFieldTitle = CRM_Utils_Array::value('title', $this->_columnHeaders[$customField]);
$customGroupTitle = explode('_custom', strstr($customField, 'civicrm_value_'));
$customGroupTitle = $this->_columns[$customGroupTitle[0]]['group_title'];
$grantStatistics[$customGroupTitle]['title'] = ts('By %1', array(1 => $customGroupTitle));
$customData = $customValue ? FALSE : TRUE;
self::getStatistics($grantStatistics[$customGroupTitle], $customFieldTitle, $values, $awardedGrants, $awardedGrantsAmount, $customData);
}
}
}
$totalStatistics['total_statistics'] = array('grants_received' => array('title' => ts('Grant Requests Received'), 'count' => $grantsReceived, 'amount' => $totalAmount), 'grants_awarded' => array('title' => ts('Grants Awarded'), 'count' => $awardedGrants, 'amount' => $amountGranted), 'grants_report_received' => array('title' => ts('Grant Reports Received'), 'count' => $grantReportsReceived));
$this->assign('totalStatistics', $totalStatistics);
$this->assign('grantStatistics', $grantStatistics);
if ($this->_outputMode == 'csv' || $this->_outputMode == 'pdf') {
$row = array();
$this->_columnHeaders = array('civicrm_grant_total_grants' => array('title' => ts('Summary')), 'civicrm_grant_count' => array('title' => ts('Count')), 'civicrm_grant_amount' => array('title' => ts('Amount')));
foreach ($totalStatistics['total_statistics'] as $title => $value) {
$row[] = array('civicrm_grant_total_grants' => $value['title'], 'civicrm_grant_count' => $value['count'], 'civicrm_grant_amount' => $value['amount']);
}
if (!empty($grantStatistics)) {
foreach ($grantStatistics as $key => $value) {
$row[] = array('civicrm_grant_total_grants' => $value['title'], 'civicrm_grant_count' => ts('Number of Grants') . ' (%)', 'civicrm_grant_amount' => ts('Total Amount') . ' (%)');
foreach ($value['value'] as $field => $values) {
foreach ($values['currency'] as $currency => $amount) {
$totalAmount[$currency] = $currency . $amount['value'] . "({$values['percentage']}%)";
}
$totalAmt = implode(', ', $totalAmount);
$count = (bool) CRM_Utils_Array::value('count', $values, 0) ? $values['count'] . " ({$values['percentage']}%)" : '';
$row[] = array('civicrm_grant_total_grants' => $field, 'civicrm_grant_count' => $count, 'civicrm_grant_amount' => $totalAmt);
}
}
}
$rows = $row;
}
}
/**
* Post process function.
*/
public function postProcess()
{
$this->buildACLClause($this->_aliases['civicrm_contact']);
parent::postProcess();
}
/**
* Overridden parent method before any form processing
*/
function preProcess()
{
$this->assign('reportTitle', ts('Opportunities Report'));
parent::preProcess();
}
public function where()
{
// reset where clause as its called multiple times, every time insert sql is built.
$this->_whereClauses = array();
parent::where();
$this->_where .= " AND (entity_log_civireport.log_action != 'Initialization')";
}
function postProcess()
{
// get the acl clauses built before we assemble the query
$this->buildACLClause($this->_aliases['civicrm_contact']);
parent::postProcess();
}
