/**
* Retrieve financial items assigned for a batch.
*
* @param int $entityID
* @param array $returnValues
* @param bool $notPresent
* @param array $params
* @param bool $getCount
*
* @return CRM_Core_DAO
*/
public static function getBatchFinancialItems($entityID, $returnValues, $notPresent = NULL, $params = NULL, $getCount = FALSE)
{
if (!$getCount) {
if (!empty($params['rowCount']) && $params['rowCount'] > 0) {
$limit = " LIMIT {$params['offset']}, {$params['rowCount']} ";
}
}
// action is taken depending upon the mode
$select = 'civicrm_financial_trxn.id ';
if (!empty($returnValues)) {
$select .= " , " . implode(' , ', $returnValues);
}
$orderBy = " ORDER BY civicrm_financial_trxn.id";
if (!empty($params['sort'])) {
$orderBy = ' ORDER BY ' . CRM_Utils_Type::escape($params['sort'], 'String');
}
$from = "civicrm_financial_trxn\nLEFT JOIN civicrm_entity_financial_trxn ON civicrm_entity_financial_trxn.financial_trxn_id = civicrm_financial_trxn.id\nLEFT JOIN civicrm_entity_batch ON civicrm_entity_batch.entity_table = 'civicrm_financial_trxn'\nAND civicrm_entity_batch.entity_id = civicrm_financial_trxn.id\nLEFT JOIN civicrm_contribution ON civicrm_contribution.id = civicrm_entity_financial_trxn.entity_id\nLEFT JOIN civicrm_financial_type ON civicrm_financial_type.id = civicrm_contribution.financial_type_id\nLEFT JOIN civicrm_contact contact_a ON contact_a.id = civicrm_contribution.contact_id\nLEFT JOIN civicrm_contribution_soft ON civicrm_contribution_soft.contribution_id = civicrm_contribution.id\n";
$searchFields = array('sort_name', 'financial_type_id', 'contribution_page_id', 'payment_instrument_id', 'contribution_trxn_id', 'contribution_source', 'contribution_currency_type', 'contribution_pay_later', 'contribution_recurring', 'contribution_test', 'contribution_thankyou_date_is_not_null', 'contribution_receipt_date_is_not_null', 'contribution_pcp_made_through_id', 'contribution_pcp_display_in_roll', 'contribution_date_relative', 'contribution_amount_low', 'contribution_amount_high', 'contribution_in_honor_of', 'contact_tags', 'group', 'contribution_date_relative', 'contribution_date_high', 'contribution_date_low', 'contribution_check_number', 'contribution_status_id');
$values = array();
foreach ($searchFields as $field) {
if (isset($params[$field])) {
$values[$field] = $params[$field];
if ($field == 'sort_name') {
$from .= " LEFT JOIN civicrm_contact contact_b ON contact_b.id = civicrm_contribution.contact_id\n LEFT JOIN civicrm_email ON contact_b.id = civicrm_email.contact_id";
}
if ($field == 'contribution_in_honor_of') {
$from .= " LEFT JOIN civicrm_contact contact_b ON contact_b.id = civicrm_contribution.contact_id";
}
if ($field == 'contact_tags') {
$from .= " LEFT JOIN civicrm_entity_tag `civicrm_entity_tag-{$params[$field]}` ON `civicrm_entity_tag-{$params[$field]}`.entity_id = contact_a.id";
}
if ($field == 'group') {
$from .= " LEFT JOIN civicrm_group_contact `civicrm_group_contact-{$params[$field]}` ON contact_a.id = `civicrm_group_contact-{$params[$field]}`.contact_id ";
}
if ($field == 'contribution_date_relative') {
$relativeDate = explode('.', $params[$field]);
$date = CRM_Utils_Date::relativeToAbsolute($relativeDate[0], $relativeDate[1]);
$values['contribution_date_low'] = $date['from'];
$values['contribution_date_high'] = $date['to'];
}
$searchParams = CRM_Contact_BAO_Query::convertFormValues($values);
$query = new CRM_Contact_BAO_Query($searchParams, CRM_Contribute_BAO_Query::defaultReturnProperties(CRM_Contact_BAO_Query::MODE_CONTRIBUTE, FALSE), NULL, FALSE, FALSE, CRM_Contact_BAO_Query::MODE_CONTRIBUTE);
if ($field == 'contribution_date_high' || $field == 'contribution_date_low') {
$query->dateQueryBuilder($params[$field], 'civicrm_contribution', 'contribution_date', 'receive_date', 'Contribution Date');
}
}
}
if (!empty($query->_where[0])) {
$where = implode(' AND ', $query->_where[0]) . " AND civicrm_entity_batch.batch_id IS NULL\n AND civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution'";
$where = str_replace('civicrm_contribution.payment_instrument_id', 'civicrm_financial_trxn.payment_instrument_id', $where);
$searchValue = TRUE;
} else {
$searchValue = FALSE;
}
if (!$searchValue) {
if (!$notPresent) {
$where = " ( civicrm_entity_batch.batch_id = {$entityID}\n AND civicrm_entity_batch.entity_table = 'civicrm_financial_trxn'\n AND civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution') ";
} else {
$where = " ( civicrm_entity_batch.batch_id IS NULL\n AND civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution')";
}
}
$sql = "\nSELECT {$select}\nFROM {$from}\nWHERE {$where}\n {$orderBy}\n";
if (isset($limit)) {
$sql .= "{$limit}";
}
$result = CRM_Core_DAO::executeQuery($sql);
return $result;
}
/**
* Get values for from and to for date ranges.
*
* @param bool $relative
* @param string $from
* @param string $to
* @param string $fromTime
* @param string $toTime
*
* @return array
*/
public function getFromTo($relative, $from, $to, $fromTime = NULL, $toTime = NULL)
{
if (empty($toTime)) {
$toTime = '235959';
}
//FIX ME not working for relative
if ($relative) {
list($term, $unit) = CRM_Utils_System::explode('.', $relative, 2);
$dateRange = CRM_Utils_Date::relativeToAbsolute($term, $unit);
$from = substr($dateRange['from'], 0, 8);
//Take only Date Part, Sometime Time part is also present in 'to'
$to = substr($dateRange['to'], 0, 8);
}
$from = CRM_Utils_Date::processDate($from, $fromTime);
$to = CRM_Utils_Date::processDate($to, $toTime);
return array($from, $to);
}
function getFromTo($relative, $from, $to, $fromtime = NULL, $totime = NULL)
{
if (empty($totime)) {
$totime = '235959';
}
//FIX ME not working for relative
if ($relative) {
list($term, $unit) = CRM_Utils_System::explode('.', $relative, 2);
$dateRange = CRM_Utils_Date::relativeToAbsolute($term, $unit);
$from = substr($dateRange['from'], 0, 8);
//Take only Date Part, Sometime Time part is also present in 'to'
$to = substr($dateRange['to'], 0, 8);
}
$from = CRM_Utils_Date::processDate($from, $fromtime);
$to = CRM_Utils_Date::processDate($to, $totime);
$report_type = CRM_Utils_Array::value("teamsinger_retention_report_type_value", $this->_params);
if ($report_type == 'have_renewed' && !$this->_runQuery) {
$this->_runQuery = TRUE;
$original_from = new DateTime($from);
$original_from->add(new DateInterval('P1Y'));
$from = $original_from->format('YmdHis');
$original_to = new DateTime($to);
$original_to->add(new DateInterval('P1Y'));
$to = $original_to->format('YmdHis');
}
return array($from, $to);
}
/**
* (non-PHPdoc)
* @see CRM_Extendedreport_Form_Report_Advancedfundraising::beginPostProcess()
*/
function beginPostProcess()
{
parent::beginPostProcess();
if (!empty($this->_params['report_options_value'])) {
//if none are set we will display all
$this->_kpiDescriptors = array_intersect_key($this->_kpiDescriptors, array_flip($this->_params['report_options_value']));
}
if (!empty($this->_params['receive_date_relative'])) {
//render out relative dates here
$rels = explode('.', $this->_params['receive_date_relative']);
$fromTo = CRM_Utils_Date::relativeToAbsolute($rels[0], $rels[1]);
$this->_params['receive_date_from'] = $this->_params['receive_date_from_stash'] = $fromTo['from'];
$this->_params['receive_date_to'] = $fromTo['to'];
// unset($this->_params['receive_date_relative']);
}
$this->_currentYear = date('Y', strtotime($this->_params['receive_date_from']));
$this->_lastYear = $this->_currentYear - 1;
$this->_yearBeforeLast = $this->_currentYear - 2;
$this->_years = array(0 => $this->_currentYear, 1 => $this->_lastYear);
// receive date from & to get unset in parent class. I'm a bit scared to change that right
// now so will hack around it by stashing them for a bit
$this->_params['receive_date_from_stash'] = $this->_params['receive_date_from'];
$this->_params['receive_date_to_stash'] = $this->_params['receive_date_to'];
$this->constructRanges(array('primary_from_date' => 'receive_date_from', 'primary_to_date' => 'receive_date_to', 'offset_unit' => 'year', 'offset' => 1, 'comparison_offset' => '1', 'comparison_offset_unit' => 'year', 'no_periods' => 2, 'statuses' => array('increased')));
}
static function getFromTo($relative, $from, $to)
{
require_once 'CRM/Utils/Date.php';
//FIX ME not working for relative
if ($relative) {
list($term, $unit) = explode('.', $relative);
$dateRange = CRM_Utils_Date::relativeToAbsolute($term, $unit);
$from = $dateRange['from'];
//Take only Date Part, Sometime Time part is also present in 'to'
$to = substr($dateRange['to'], 0, 8);
}
$from = CRM_Utils_Date::processDate($from);
$to = CRM_Utils_Date::processDate($to, '235959');
return array($from, $to);
}
static function getFromTo($relative, $from, $to)
{
require_once 'CRM/Utils/Date.php';
if ($relative) {
list($term, $unit) = explode('.', $relative);
$dateRange = CRM_Utils_Date::relativeToAbsolute($term, $unit);
$from = $dateRange['from'];
$to = $dateRange['to'];
}
if (CRM_Utils_Date::isDate($from)) {
$revDate = array_reverse($from);
$from = CRM_Utils_Date::format($revDate);
} else {
$from = null;
}
if (CRM_Utils_Date::isDate($to)) {
$revDate = array_reverse($to);
$to = CRM_Utils_Date::format($revDate);
} else {
$to = null;
}
return array($from, $to);
}
