/**
* @return json structure containing membership statistics.
*/
function member_statistics()
{
// Get plans and earliest date
$plans = crm_map(member_plan_data(), 'pid');
$results = array();
foreach ($plans as $pid => $plan) {
$results[$pid] = array();
}
$earliest = member_membership_earliest_date();
if (empty($earliest)) {
message_register('No membership data available.');
return '[]';
}
// Generate list of months
$start = 12 * (int) date('Y', strtotime($earliest)) + (int) date('m', strtotime($earliest)) - 1;
$now = 12 * (int) date('Y') + (int) date('m') - 1;
$dates = array();
for ($months = $start; $months <= $now; $months++) {
$year = floor($months / 12);
$month = $months % 12 + 1;
$dates[] = "('{$year}-{$month}-01')";
}
// Create temporary table with dates
$sql = "DROP TEMPORARY TABLE IF EXISTS `temp_months`";
$res = mysql_query($sql);
if (!$res) {
crm_error(mysql_error($res));
}
$sql = "CREATE TEMPORARY TABLE `temp_months` (`month` date NOT NULL);";
$res = mysql_query($sql);
if (!$res) {
crm_error(mysql_error($res));
}
$sql = "INSERT INTO `temp_months` (`month`) VALUES " . implode(',', $dates) . ";";
$res = mysql_query($sql);
if (!$res) {
crm_error(mysql_error($res));
}
// Query number of active memberships for each month
$sql = "\n SELECT\n `plan`.`pid`\n , `plan`.`name`\n , `temp_months`.`month`\n , UNIX_TIMESTAMP(`temp_months`.`month`) AS `month_timestamp`\n , count(`membership`.`sid`) AS `member_count`\n FROM `temp_months`\n JOIN `plan`\n LEFT JOIN `membership`\n ON `membership`.`pid`=`plan`.`pid`\n AND `membership`.`start` <= `month`\n AND (`membership`.`end` IS NULL OR `membership`.`end` > `month`)\n GROUP BY `plan`.`pid`, `month`;\n ";
$res = mysql_query($sql);
if (!$res) {
crm_error(mysql_error($res));
}
// Build results
while ($row = mysql_fetch_assoc($res)) {
$results[$row['pid']][] = array('x' => (int) $row['month_timestamp'], 'label' => $row['month'], 'y' => (int) $row['member_count']);
}
// Convert from associative to indexed
$indexed = array();
foreach ($results as $pid => $v) {
$indexed[] = array('name' => $plans[$pid]['name'] . " ({$pid})", 'values' => $v);
}
return json_encode($indexed);
}
/**
* Implementation of hook_data_alter().
* @param $type The type of the data being altered.
* @param $data An array of structures of the given $type.
* @param $opts An associative array of options.
* @return An array of modified structures.
*/
function paypal_payment_data_alter($type, $data = array(), $opts = array())
{
switch ($type) {
case 'payment':
// Get paypal payments
$pmtids = array();
foreach ($data as $payment) {
$pmtids[] = $payment['pmtid'];
}
$opts = array('pmtid' => $pmtids);
$paypal_payment_map = crm_map(crm_get_data('paypal_payment', $opts), 'pmtid');
// Add paypal data to each payment data
foreach ($data as $i => $payment) {
if (array_key_exists($payment['pmtid'], $paypal_payment_map)) {
$data[$i]['paypal'] = $paypal_payment_map[$payment['pmtid']];
}
}
}
return $data;
}
/**
* Return a table structure for a table of key assignments.
*
* @param $opts The options to pass to key_data().
* @return The table structure.
*/
function key_table($opts)
{
// Determine settings
$export = false;
foreach ($opts as $option => $value) {
switch ($option) {
case 'export':
$export = $value;
break;
}
}
// Get key data
$data = crm_get_data('key', $opts);
if (count($data) < 1) {
return array();
}
// Get contact info
$contact_opts = array();
foreach ($data as $row) {
$contact_opts['cid'][] = $row['cid'];
}
$contact_data = crm_get_data('contact', $contact_opts);
$cid_to_contact = crm_map($contact_data, 'cid');
// Initialize table
$table = array("id" => '', "class" => '', "rows" => array(), "columns" => array());
// Add columns
if (user_access('key_view') || $opts['cid'] == user_id()) {
if ($export) {
$table['columns'][] = array("title" => 'cid', 'class' => '', 'id' => '');
}
$table['columns'][] = array("title" => 'Name', 'class' => '', 'id' => '');
$table['columns'][] = array("title" => 'Serial', 'class' => '', 'id' => '');
$table['columns'][] = array("title" => 'Slot', 'class' => '', 'id' => '');
$table['columns'][] = array("title" => 'Start', 'class' => '', 'id' => '');
$table['columns'][] = array("title" => 'End', 'class' => '', 'id' => '');
}
// Add ops column
if (!$export && (user_access('key_edit') || user_access('key_delete'))) {
$table['columns'][] = array('title' => 'Ops', 'class' => '');
}
// Add rows
foreach ($data as $key) {
// Add key data
$row = array();
if (user_access('key_view') || $opts['cid'] == user_id()) {
// Add cells
if ($export) {
$row[] = $key['cid'];
}
$row[] = theme('contact_name', $cid_to_contact[$key['cid']], !$export);
$row[] = $key['serial'];
$row[] = $key['slot'];
$row[] = $key['start'];
$row[] = $key['end'];
}
if (!$export && (user_access('key_edit') || user_access('key_delete'))) {
// Construct ops array
$ops = array();
// Add edit op
if (user_access('key_edit')) {
$ops[] = '<a href=' . crm_url('key&kid=' . $key['kid'] . '#tab-edit') . '>edit</a> ';
}
// Add delete op
if (user_access('key_delete')) {
$ops[] = '<a href=' . crm_url('delete&type=key&id=' . $key['kid']) . '>delete</a>';
}
// Add ops row
$row[] = join(' ', $ops);
}
$table['rows'][] = $row;
}
return $table;
}
/**
* Send emails to any members with a positive balance.
*/
function command_amazon_payment_email()
{
global $config_email_from;
global $config_site_title;
// Get balances and contacts
$cids = payment_contact_filter(array('balance_due' => true));
$balances = payment_accounts(array('cid' => $cids));
$contacts = crm_get_data('contact', array('cid' => $cids));
$cidToContact = crm_map($contacts, 'cid');
// Email each contact with a balance
foreach ($balances as $cid => $balance) {
// Construct button
$params = array('referenceId' => $cid, 'amount' => $balance['code'] . ' ' . payment_format_currency($balance, false), 'description' => 'CRM Dues Payment');
$amount = payment_format_currency($balance);
$button = theme('amazon_payment_button', $cid, $params);
// Send email
$to = $cidToContact[$cid]['email'];
$subject = "[{$config_site_title}] Payment Due";
$from = $config_email_from;
$headers = "Content-type: text/html\r\nFrom: {$from}\r\n";
$message = "<p>Hello,<br/>Your current account balance is {$amount}. To pay this balance using Amazon Payments, please click the button below.</p>{$button}";
$res = mail($to, $subject, $message, $headers);
}
message_register('E-mails have been sent');
variable_set('amazon_payment_last_email', date('Y-m-d'));
return crm_url('payments', array('query' => array('tab' => 'billing')));
}
/**
* Return a table showing account balances.
* @param $opts An associative array of options.
* @return A table object.
*/
function payment_accounts_table($opts)
{
$export = array_key_exists('export', $opts) && $opts['export'] ? true : false;
$cids = payment_contact_filter(array('balance_due' => true));
$balances = payment_accounts(array('cid' => $cids));
$table = array('columns' => array(array('title' => 'Name'), array('title' => 'Email'), array('title' => 'Balance Owed')), 'rows' => array());
$contacts = crm_get_data('contact', array('cid' => $cids));
$cidToContact = crm_map($contacts, 'cid');
foreach ($balances as $cid => $balance) {
$row = array();
$row[] = theme('contact_name', $cid, !$export);
$row[] = $cidToContact[$cid]['email'];
$row[] = payment_format_currency($balance);
$table['rows'][] = $row;
}
return $table;
}
