CiviCRM Community Forums (archive)

I've taken coleman's drupal module that creates a contribution list of unthanked contributions and assigns it to a token that can be used in an email or pdf letter. I'm trying to add a total to the loop but can't figure out where I need to put the calculation. You can see i'm using $fin_total as the variable to store the total. Any help on where to put the line $fin_total += $dao->total_amount;

function mysite_hooks_civicrm_tokenValues(&$values, $cids, $job = null, $tokens = array(), $context = null) {
  // Donation info for contact and spouse
  $contacts = implode(',', $cids);
  if (!empty($tokens['donor'])) {
    $spouses = array();
    $contacts_and_spouses = $cids;
    $fin_total = 0.00;
   
    $dao = &CRM_Core_DAO::executeQuery("
      SELECT contact_id_a, contact_id_b
      FROM civicrm_relationship
      WHERE relationship_type_id = 2
      AND is_active = 1
      AND (end_date IS NULL OR end_date > CURDATE())
      AND (contact_id_a IN ($contacts) OR contact_id_b IN ($contacts))
    ");
           
    while ($dao->fetch()) {

      if (!in_array($dao->contact_id_a, $contacts_and_spouses)) {
        $contacts_and_spouses[] = $dao->contact_id_a;
      }
      if (!in_array($dao->contact_id_b, $contacts_and_spouses)) {
        $contacts_and_spouses[] = $dao->contact_id_b;
      }
      if (in_array($dao->contact_id_a, $cids)) {
        $spouses[$dao->contact_id_b] = $dao->contact_id_a;
      }
      if (in_array($dao->contact_id_b, $cids)) {
        $spouses[$dao->contact_id_a] = $dao->contact_id_b;
      }
    }
    $contacts_and_spouses = implode(',', $contacts_and_spouses);
    // Clear today's thank-yous (a kind of crude UNDO)
    if (in_array('clear_thank_you', $tokens['donor'])) {
      CRM_Core_DAO::executeQuery("
        UPDATE civicrm_contribution SET thankyou_date = NULL
        WHERE is_test = 0 AND financial_type_id = 1 OR cc.financial_type_id = 2 OR cc.financial_type_id = 8 AND contribution_status_id = 1
        AND DATE(thankyou_date) = CURDATE()"
      );
    }
    if (in_array('unthanked', $tokens['donor'])) {
      $dao = &CRM_Core_DAO::executeQuery("
        SELECT cc.contact_id, cc.financial_type_id, cc.total_amount, cc.receive_date, cc.check_number, con.display_name, hon.display_name as honoree, pi.label AS payment_instrument, ht.label AS honor_type
        FROM civicrm_contribution cc
        INNER JOIN civicrm_contact con ON con.id = cc.contact_id
        LEFT JOIN civicrm_contact hon ON hon.id = cc.honor_contact_id
        LEFT JOIN civicrm_option_value pi ON cc.payment_instrument_id = pi.value AND pi.option_group_id = (SELECT id FROM civicrm_option_group WHERE name = 'payment_instrument')
        LEFT JOIN civicrm_option_value ht ON cc.honor_type_id = ht.value AND ht.option_group_id = (SELECT id FROM civicrm_option_group WHERE name = 'honor_type')
        WHERE cc.is_test = 0 AND cc.financial_type_id = 1 OR cc.financial_type_id = 2 OR cc.financial_type_id = 8 AND cc.contribution_status_id = 1
        AND cc.contact_id IN ($contacts_and_spouses) AND cc.thankyou_date IS NULL
        ORDER BY cc.receive_date"
      );
      $header = '
        <table  width="100%" border="1" cellpadding="4" cellspacing="0">
          <thead><tr>
            <th align="left">Date</th>
            <th align="left">Donor</th>
            <th align="left">Fund</th>
            <th align="left">Amount</th>
            <th align="left">Paid By</th>
            <th align="left">Notes</th>
          </tr></thead>
          <tbody>';
      while ($dao->fetch()) {
        $cid = $dao->contact_id;
      switch ($dao->financial_type_id) {
        case 1:
            $fin_type = "Donation";
            break;
        case 2:
            $fin_type = "Offering";
            break;
        case 8:
            $fin_type = "Bishops' Relief Fund";
            break;
        }
       
        $row = '
          <tr>
            <td>' . date('m/d/Y', strtotime($dao->receive_date)) . '</td>
            <td>' . $dao->display_name . '</td>
            <td>' . $fin_type .  '</td>
            <td>$' . $dao->total_amount . '</td>
            <td>' . ($dao->payment_instrument ? $dao->payment_instrument : 'In Kind')
            . ($dao->check_number ? ' #' . $dao->check_number : '') . '</td>
            <td>' . ($dao->honoree ? "<br />{$dao->honor_type} {$dao->honoree}" : '') . '</td>
          </tr>';
        if (in_array($cid, $cids)) {
          $values[$cid]['donor.unthanked'] = (!empty($values[$cid]['donor.unthanked']) ? $values[$cid]['donor.unthanked'] : $header) . $row;
        }
        if (isset($spouses[$cid])) {
          $values[$spouses[$cid]]['donor.unthanked'] = (!empty($values[$spouses[$cid]]['donor.unthanked']) ? $values[$spouses[$cid]]['donor.unthanked'] : $header) . $row;
        }
      }
      foreach ($cids as $cid) {
        if (!empty($values[$cid]['donor.unthanked'])) {
          $values[$cid]['donor.unthanked'] .= '</tbody></table><br /><strong>TOTAL: </strong>$' . $fin_total;
        }
      }
    }
    if (in_array('set_thank_you', $tokens['donor'])) {
      CRM_Core_DAO::executeQuery("
        UPDATE civicrm_contribution SET thankyou_date = NOW()
        WHERE is_test = 0 AND financial_type_id = 1 OR cc.financial_type_id = 2 OR cc.financial_type_id = 8 AND contribution_status_id = 1
        AND contact_id IN ($contacts_and_spouses) AND thankyou_date IS NULL"
      );
    }
  }


Thanks Eileen.

For some reason it's getting a higher amount than what is printing. The total should be 969 for my test contact and it's getting 1209.

I'm not sure. I get lost in the all the IF's and foreach's.   I've played around with it at various places but can't figure it out. It either resets it to zero completely or stays at the 1209.

This is interesting. I don't know why it's doing what it's doing.

This is the code with the dpn inserted at various places. I named each one by the line number to make it easier to find.  Notice how when it gets to the subtotal of 919 it somehow skips line 110 whereas all the calculations prior included line 110. All the donations are straight donations. there's no spouse involved.

<?php

function mysite_hooks_civicrm_tokens(&$tokens)
{
$tokens['date'] = array(
'date.date_short' => 'Today\'s Date: mm/dd/yyyy',
'date.date_med' => 'Today\'s Date: Mon d yyyy',
'date.date_long' => 'Today\'s Date: Month d, yyyy',
        'date.date_year' => 'Today\'s Date: yyyy',
);

$tokens['donor'] = array(
'donor.unthanked' => 'Donations: To Thank',
'donor.set_thank_you' => 'Donations: MARK AS THANKED',
'donor.clear_thank_you' => 'Donations: CLEAR TODAYS THANKED',
);
}

function mysite_hooks_civicrm_tokenValues(&$values, $cids, $job = null, $tokens = array(), $context = null) {
  // Donation info for contact and spouse
  $contacts = implode(',', $cids);
  if (!empty($tokens['donor'])) {
    $spouses = array();
    $contacts_and_spouses = $cids;
    $fin_total = 0;
     dpm($fin_total, $name = "NEW");
     
    $dao = &CRM_Core_DAO::executeQuery("
      SELECT contact_id_a, contact_id_b
      FROM civicrm_relationship
      WHERE relationship_type_id = 2
      AND is_active = 1
      AND (end_date IS NULL OR end_date > CURDATE())
      AND (contact_id_a IN ($contacts) OR contact_id_b IN ($contacts))
    ");       
    while ($dao->fetch()) {
      if (!in_array($dao->contact_id_a, $contacts_and_spouses)) {
        $contacts_and_spouses[] = $dao->contact_id_a;
      }
      if (!in_array($dao->contact_id_b, $contacts_and_spouses)) {
        $contacts_and_spouses[] = $dao->contact_id_b;
      }
      if (in_array($dao->contact_id_a, $cids)) {
        $spouses[$dao->contact_id_b] = $dao->contact_id_a;
      }
      if (in_array($dao->contact_id_b, $cids)) {
        $spouses[$dao->contact_id_a] = $dao->contact_id_b;
      }
    }
    $contacts_and_spouses = implode(',', $contacts_and_spouses);
    // Clear today's thank-yous (a kind of crude UNDO)
    if (in_array('clear_thank_you', $tokens['donor'])) {
      CRM_Core_DAO::executeQuery("
        UPDATE civicrm_contribution SET thankyou_date = NULL
        WHERE is_test = 0 AND financial_type_id = 1 OR cc.financial_type_id = 2 OR cc.financial_type_id = 8 AND contribution_status_id = 1
        AND DATE(thankyou_date) = CURDATE()"
      );
    }
    if (in_array('unthanked', $tokens['donor'])) {
      $dao = &CRM_Core_DAO::executeQuery("
        SELECT cc.contact_id, cc.financial_type_id, cc.total_amount, cc.receive_date, cc.check_number, con.display_name, hon.display_name as honoree, pi.label AS payment_instrument, ht.label AS honor_type
        FROM civicrm_contribution cc
        INNER JOIN civicrm_contact con ON con.id = cc.contact_id
        LEFT JOIN civicrm_contact hon ON hon.id = cc.honor_contact_id
        LEFT JOIN civicrm_option_value pi ON cc.payment_instrument_id = pi.value AND pi.option_group_id = (SELECT id FROM civicrm_option_group WHERE name = 'payment_instrument')
        LEFT JOIN civicrm_option_value ht ON cc.honor_type_id = ht.value AND ht.option_group_id = (SELECT id FROM civicrm_option_group WHERE name = 'honor_type')
        WHERE cc.is_test = 0 AND cc.financial_type_id = 1 OR cc.financial_type_id = 2 OR cc.financial_type_id = 8 AND cc.contribution_status_id = 1
        AND cc.contact_id IN ($contacts_and_spouses) AND cc.thankyou_date IS NULL
        ORDER BY cc.receive_date"
      );
      $header = '
        <table  width="100%" border="1" cellpadding="4" cellspacing="0">
          <thead><tr>
            <th align="left">Date</th>
            <th align="left">Donor</th>
            <th align="left">Fund</th>
            <th align="left">Amount</th>
            <th align="left">Paid By</th>
            <th align="left">Notes</th>
          </tr></thead>
          <tbody>';
    
      while ($dao->fetch()) {
    dpm($fin_total, $name = 84);
        $cid = $dao->contact_id;
      switch ($dao->financial_type_id) {
        case 1:
            $fin_type = "Donation";
            break;
        case 2:
            $fin_type = "Offering";
            break;
        case 8:
            $fin_type = "Bishops' Relief Fund";
            break;
        }
        dpm($fin_total, $name = 99);
        $row = '
          <tr>
            <td>' . date('m/d/Y', strtotime($dao->receive_date)) . '</td>
            <td>' . $dao->display_name . '</td>
            <td>' . $fin_type .  '</td>
            <td>$' . $dao->total_amount . '</td>
            <td>' . ($dao->payment_instrument ? $dao->payment_instrument : 'In Kind') 
            . ($dao->check_number ? ' #' . $dao->check_number : '') . '</td>
            <td>' . ($dao->honoree ? "<br />{$dao->honor_type} {$dao->honoree}" : '') . '</td>
          </tr>';
        if (in_array($cid, $cids)) {
          $values[$cid]['donor.unthanked'] = (!empty($values[$cid]['donor.unthanked']) ? $values[$cid]['donor.unthanked'] : $header) . $row;
          dpm($fin_total, $name = 110);
        }
        if (isset($spouses[$cid])) {
          $values[$spouses[$cid]]['donor.unthanked'] = (!empty($values[$spouses[$cid]]['donor.unthanked']) ? $values[$spouses[$cid]]['donor.unthanked'] : $header) . $row;
          dpm($fin_total, $name = 113);
          
        }
         dpm($fin_total, $name = 116);
        $fin_total += $dao->total_amount;
        dpm($fin_total, $name = 118);
      }
       dpm($fin_total, $name = 121);
      foreach ($cids as $cid) {
        if (!empty($values[$cid]['donor.unthanked'])) {
          $values[$cid]['donor.unthanked'] .= '</tbody></table><br /><strong>TOTAL: </strong>$' . $fin_total;
          dpm($fin_total, $name = 124);
        }
      }
     dpm($fin_total, $name = 127);
    }
    
    if (in_array('set_thank_you', $tokens['donor'])) {
      CRM_Core_DAO::executeQuery("
        UPDATE civicrm_contribution SET thankyou_date = NOW()
        WHERE is_test = 0 AND financial_type_id = 1 OR cc.financial_type_id = 2 OR cc.financial_type_id = 8 AND contribution_status_id = 1
        AND contact_id IN ($contacts_and_spouses) AND thankyou_date IS NULL"
      );
    }
  }


// Date tokens
if (!empty($tokens['date']))
{
$date = array(
'date.date_short' => date('m/d/Y'),
'date.date_med' => date('M j Y'),
'date.date_long' => date('F j, Y'),
            'date.date_year' => date('Y'),
);
foreach ($cids as $cid)
{
$values[$cid] = empty($values[$cid]) ? $date : $values[$cid] + $date;
}
}
 }
this is the Logging that the dpm gives me. Notice that at 118 is where it increments each time and it does it correctly until it gets to the 18th iteration through and somehow adds more to the count. The amounts that it's adding is below.

   

    NEW => 0

    84 => 0

    99 => 0

    110 => 0

    116 => 0

    118 => 204

    84 => 204

    99 => 204

    110 => 204

    116 => 204

    118 => 244

    84 => 244

    99 => 244

    110 => 244

    116 => 244

    118 => 304

    84 => 304

    99 => 304

    110 => 304

    116 => 304

    118 => 356

    84 => 356

    99 => 356

    110 => 356

    116 => 356

    118 => 396

    84 => 396

    99 => 396

    110 => 396

    116 => 396

    118 => 436

    84 => 436

    99 => 436

    110 => 436

    116 => 436

    118 => 456

    84 => 456

    99 => 456

    110 => 456

    116 => 456

    118 => 536

    84 => 536

    99 => 536

    110 => 536

    116 => 536

    118 => 596

    84 => 596

    99 => 596

    110 => 596

    116 => 596

    118 => 617

    84 => 617

    99 => 617

    110 => 617

    116 => 617

    118 => 658

    84 => 658

    99 => 658

    110 => 658

    116 => 658

    118 => 688

    84 => 688

    99 => 688

    110 => 688

    116 => 688

    118 => 708

    84 => 708

    99 => 708

    110 => 708

    116 => 708

    118 => 778

    84 => 778

    99 => 778

    110 => 778

    116 => 778

    118 => 843

    84 => 843

    99 => 843

    110 => 843

    116 => 843

    118 => 874

    84 => 874

    99 => 874

    110 => 874

    116 => 874

    118 => 914

    84 => 914

    99 => 914

    110 => 914

    116 => 914

    118 => 919

    84 => 919

    99 => 919

    116 => 919

    118 => 1069

    84 => 1069

    99 => 1069

    110 => 1069

    116 => 1069

    118 => 1099

    84 => 1099

    99 => 1099

    110 => 1099

    116 => 1099

    118 => 1119

    84 => 1119

    99 => 1119

    116 => 1119

    118 => 1159

    84 => 1159

    99 => 1159

    116 => 1159

    118 => 1209

    121 => 1209

    124 => 1209

    127 => 1209




Amount    Subtotal
$204.00
$40.00 $244.00
$60.00 $304.00
$52.00 $356.00
$40.00 $396.00
$40.00 $436.00
$20.00 $456.00
$80.00 $536.00
$60.00 $596.00
$21.00 $617.00
$41.00 $658.00
$30.00 $688.00
$20.00 $708.00
$70.00 $778.00
$65.00 $843.00
$31.00 $874.00
$40.00 $914.00
$5.00 $919.00
$30.00 $949.00
$20.00 $969.00


Any ideas?

It's all the same contact.

It's beyond my skill level to figure it out. My contributors will just have to add up their donations on their own.

I still don't understand why a product that says it's geared toward non-profits does not have an out of the box way of sending an end of year or quarterly statement of contributions.

and in great Civi fashion I'm getting some sort of error now. I can't edit a contribution because it gives me this error:

Notice: Undefined index: price_field_id in postProcess() (line 1075 of /home/saintfran/www/www/sites/all/modules/civicrm/CRM/Contribute/Form/Contribution.php).