/**
* @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);
}
/**
* Delete membership data for a contact.
* @param $cid - The contact id.
*/
function member_delete($cid)
{
// Store name
$contact_data = crm_get_data('contact', array('cid' => $cid));
$contact = $contact_data[0];
$name = theme('contact_name', $contact);
// Delete member
$esc_cid = mysql_real_escape_string($cid);
$sql = "DELETE FROM `member` WHERE `cid`='{$esc_cid}'";
$res = mysql_query($sql);
if (!$res) {
crm_error(mysql_error());
}
$sql = "DELETE FROM `membership` WHERE `cid`='{$esc_cid}'";
$res = mysql_query($sql);
if (!$res) {
crm_error(mysql_error());
}
message_register("Deleted membership info for: {$name}");
}
/**
* Delete an existing secret
* @param $secret The secret name
*/
function secrets_delete($secret)
{
if (isset($secret['name'])) {
$esc_name = mysql_real_escape_string($secret['name']);
$sql = "DELETE FROM variable WHERE name = '" . $esc_name . "'";
$res = mysql_query($sql);
if (!$res) {
die(mysql_error());
}
if (mysql_affected_rows() > 0) {
message_register('Secret deleted.');
}
} else {
message_register('No such secret');
var_dump_pre($secret);
}
}
/**
* Run billings
*/
function command_billing()
{
// Get current date and last bill date
$today = date('Y-m-d');
$last_billed = variable_get('billing_last_date', '');
// Find memberships that start before today and end after the last bill date
$filter = array();
if (!empty($last_billed)) {
$filter['ends_after'] = $last_billed;
}
$membership_data = crm_get_data('member_membership', array('filter' => $filter));
// Bill each membership
foreach ($membership_data as $membership) {
if (!empty($membership['end']) && strtotime($membership['end']) < strtotime($today)) {
// Bill until end of membership
_billing_bill_membership($membership, $membership['end'], $last_billed);
} else {
// Bill until today
_billing_bill_membership($membership, $today, $last_billed);
}
}
// Set last billed date to today
variable_set('billing_last_date', $today);
$begin = empty($last_billed) ? 'the beginning of time' : $last_billed;
message_register("Billings processed from {$begin} through {$today}.");
return crm_url('payments');
}
/**
* Delete a key.
* @param $key The key data structure to delete, must have a 'kid' element.
*/
function key_delete($key)
{
$esc_kid = mysql_real_escape_string($key['kid']);
$sql = "DELETE FROM `key` WHERE `kid`='{$esc_kid}'";
$res = mysql_query($sql);
if (!$res) {
die(mysql_error());
}
if (mysql_affected_rows() > 0) {
message_register('Key deleted.');
}
}
/**
* Respond to password reset confirmation.
* @return The url to display after the command is processed.
*/
function command_reset_password_confirm()
{
global $esc_post;
// Check code
if (!user_check_reset_code($_POST['code'])) {
error_register('Invalid reset code');
return crm_url();
}
// Check that passwords match
if ($_POST['password'] != $_POST['confirm']) {
error_register('Passwords do not match');
return crm_url();
}
// Get user id
$sql = "SELECT * FROM `resetPassword` WHERE `code`='{$esc_post['code']}'";
$res = mysql_query($sql);
if (!$res) {
die(mysql_error());
}
$row = mysql_fetch_assoc($res);
$esc_cid = mysql_real_escape_string($row['cid']);
// Calculate hash
$salt = user_salt();
$esc_hash = mysql_real_escape_string(user_hash($_POST['password'], $salt));
$esc_salt = mysql_real_escape_string($salt);
// Update password
$sql = "\n UPDATE `user`\n SET `hash`='{$esc_hash}'\n , `salt`='{$esc_salt}'\n WHERE `cid`='{$esc_cid}'\n ";
$res = mysql_query($sql);
if (!$res) {
die(mysql_error());
}
// Notify user to check their email
message_register('Your password has been reset, you may now log in');
return crm_url('login');
}
/**
* 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')));
}
/**
* Handle upgrade request.
*
* @return The url to redirect to on completion.
*/
function command_module_upgrade()
{
global $esc_post;
// Create tables
$res = module_upgrade();
if (!$res) {
return crm_url();
}
message_register('Seltzer CRM has been upgraded.');
return crm_url();
}
/**
* Handle payment edit request.
*
* @return The url to display on completion.
*/
function command_payment_edit()
{
// Verify permissions
if (!user_access('payment_edit')) {
error_register('Permission denied: payment_edit');
return crm_url('payments');
}
// Parse and save payment
$payment = $_POST;
$value = payment_parse_currency($_POST['value'], $_POST['code']);
$payment['code'] = $value['code'];
$payment['value'] = $value['value'];
payment_save($payment);
message_register('1 payment updated.');
return crm_url('payments');
}
/**
* Handle paypal payment import request.
*
* @return The url to display on completion.
*/
function command_paypal_payment_import()
{
if (!user_access('payment_edit')) {
error_register('User does not have permission: payment_edit');
return crm_url('payments');
}
if (!array_key_exists('payment-file', $_FILES)) {
error_register('No payment file uploaded');
return crm_url('payments&tab=import');
}
$csv = file_get_contents($_FILES['payment-file']['tmp_name']);
$data = csv_parse($csv);
$count = 0;
foreach ($data as $row) {
// Skip transactions that have already been imported
$payment_opts = array('filter' => array('confirmation' => $row['Transaction ID']));
$data = payment_data($payment_opts);
if (count($data) > 0) {
continue;
}
// Parse value
$value = payment_parse_currency($row['Gross']);
// Create payment object
$payment = array('date' => date('Y-m-d', strtotime($row['Date'])), 'code' => $value['code'], 'value' => $value['value'], 'description' => $row['Name'] . ' Paypal Payment', 'method' => 'paypal', 'confirmation' => $row['Transaction ID'], 'notes' => $row['Item Title'], 'paypal_email' => $row['From Email Address']);
// Check if the paypal email is linked to a contact
$opts = array('filter' => array('paypal_email' => $row['From Email Address']));
$contact_data = paypal_payment_contact_data($opts);
if (count($contact_data) > 0) {
$payment['credit_cid'] = $contact_data[0]['cid'];
}
// Save the payment
$payment = payment_save($payment);
$count++;
}
message_register("Successfully imported {$count} payment(s)");
return crm_url('payments');
}
/**
* Delete a contact.
* @param $cid The contact id.
*/
function contact_delete($cid)
{
$contact = crm_get_one('contact', array('cid' => $cid));
if (empty($contact)) {
error_register("No contact with cid {$cid}");
return;
}
// Notify other modules the contact is being deleted
$contact = module_invoke_api('contact', $contact, 'delete');
// Remove the contact from the database
$esc_cid = mysql_real_escape_string($cid);
$sql = "DELETE FROM `contact` WHERE `cid`='{$esc_cid}'";
$res = mysql_query($sql);
if (!$res) {
crm_error(mysql_error());
}
message_register('Deleted contact: ' . theme('contact_name', $contact));
}
