/**
* Handle plan import request.
*
* @return The url to display on completion.
*/
function command_member_plan_import()
{
if (!user_access('member_plan_edit')) {
error_register('User does not have permission: member_plan_edit');
return crm_url('members');
}
if (!array_key_exists('plan-file', $_FILES)) {
error_register('No plan file uploaded');
return crm_url('plans&tab=import');
}
$csv = file_get_contents($_FILES['plan-file']['tmp_name']);
$data = csv_parse($csv);
foreach ($data as $row) {
// Convert row keys to lowercase and remove spaces
foreach ($row as $key => $value) {
$new_key = str_replace(' ', '', strtolower($key));
unset($row[$key]);
$row[$new_key] = $value;
}
// Add plan
$name = mysql_real_escape_string($row['planname']);
$price = mysql_real_escape_string($row['price']);
$active = mysql_real_escape_string($row['active']);
$voting = mysql_real_escape_string($row['voting']);
$sql = "\n INSERT INTO `plan`\n (`name`,`price`,`active`,`voting`)\n VALUES\n ('{$name}','{$price}','{$active}','{$voting}')";
$res = mysql_query($sql);
if (!$res) {
crm_error(mysql_error());
}
$pid = mysql_insert_id();
}
return crm_url('plans');
}
/**
* Save a membership. A membership represents a specific member's plan at
* for a specific time period.
* @param $membership
* @return $membership
*/
function member_membership_save($membership)
{
$esc_sid = mysql_real_escape_string($membership['sid']);
$esc_cid = mysql_real_escape_string($membership['cid']);
$esc_pid = mysql_real_escape_string($membership['pid']);
$esc_start = mysql_real_escape_string($membership['start']);
$esc_end = mysql_real_escape_string($membership['end']);
if (isset($membership['sid'])) {
// Update
$sql = "\n UPDATE `membership`\n SET `cid`='{$esc_cid}'\n , `pid`='{$esc_pid}', ";
if ($esc_start) {
$sql .= "`start`='{$esc_start}', ";
} else {
$sql .= "`start`=NULL, ";
}
if ($esc_end) {
$sql .= "`end`='{$esc_end}' ";
} else {
$sql .= "`end`=NULL ";
}
$sql .= "WHERE `sid`='{$esc_sid}'";
$res = mysql_query($sql);
if (!$res) {
crm_error(mysql_error());
}
} else {
// Insert
$sql = "\n INSERT INTO `membership`\n (`cid`, `pid`, `start`)\n VALUES\n ('{$esc_cid}', '{$esc_pid}', '{$esc_start}')\n ";
$res = mysql_query($sql);
if (!$res) {
crm_error(mysql_error());
}
$membership['sid'] = mysql_insert_id();
}
return $membership;
}
/**
* Update amazon_payment data when a payment is updated.
* @param $contact The contact data array.
* @param $op The operation being performed.
*/
function amazon_payment_payment_api($payment, $op)
{
if ($payment['method'] !== 'amazon') {
return $payment;
}
$name = $payment['amazon_name'];
$pmtid = $payment['pmtid'];
$credit_cid = $payment['credit_cid'];
$esc_name = mysql_real_escape_string($name);
$esc_pmtid = mysql_real_escape_string($pmtid);
// Create link between the amazon payment name and contact id
$amazon_contact = array();
if (isset($payment['amazon_name'])) {
$amazon_contact['amazon_name'] = $name;
}
if (isset($payment['credit_cid'])) {
$amazon_contact['cid'] = $credit_cid;
}
switch ($op) {
case 'insert':
$sql = "\n INSERT INTO `payment_amazon`\n (`pmtid`, `amazon_name`)\n VALUES\n ('{$esc_pmtid}', '{$esc_name}')\n ";
$res = mysql_query($sql);
if (!$res) {
crm_error(mysql_error());
}
amazon_payment_contact_save($amazon_contact);
break;
case 'update':
$sql = "\n UPDATE `payment_amazon`\n SET `amazon_name` = '{$esc_name}'\n WHERE `pmtid` = '{$esc_pmtid}'\n ";
$res = mysql_query($sql);
if (!$res) {
crm_error(mysql_error());
}
amazon_payment_contact_save($amazon_contact);
break;
case 'delete':
$sql = "\n DELETE FROM `payment_amazon`\n WHERE `pmtid`='{$esc_pmtid}'";
$res = mysql_query($sql);
if (!$res) {
crm_error(mysql_error());
}
break;
}
return $payment;
}
/**
* Delete user.
* @param $cid The user's cid.
*/
function user_delete($cid)
{
$esc_cid = mysql_real_escape_string($cid);
$sql = "DELETE FROM `user` WHERE `cid`='{$esc_cid}'";
$res = mysql_query($sql);
if (!$res) {
crm_error(msyql_error());
}
$sql = "DELETE FROM `user_role` WHERE `cid`='{$esc_cid}'";
$res = mysql_query($sql);
if (!$res) {
crm_error(msyql_error());
}
message_register("Deleted user data for: " . theme('contact_name', $cid));
}
/**
* Invoke an entity api in all modules.
*/
function module_invoke_api($type, $entity, $op)
{
$args = func_get_args();
$type = array_shift($args);
$modules = module_list();
foreach ($modules as $module) {
$hook = "{$module}_{$type}_api";
if (function_exists($hook)) {
$entity = call_user_func_array($hook, $args);
if (empty($entity)) {
crm_error("{$hook} returned empty entity");
}
$args[0] = $entity;
}
}
return $entity;
}
/**
* Return an array mapping cids to balance owed.
* @param $opts Options, possible keys are:
* 'cid' - A single cid or array of cids to limit results.
* @return The associative array mapping cids to payment objects.
*/
function payment_accounts($opts = array())
{
$cid_to_balance = array();
// Get credits
$sql = "\n SELECT `credit`, `code`, SUM(`value`) AS `value` FROM `payment` WHERE `credit` <> 0\n ";
foreach ($opts as $key => $value) {
switch ($key) {
case 'cid':
if (is_array($value)) {
$terms = array();
if (!empty($value)) {
foreach ($value as $cid) {
$terms[] = mysql_real_escape_string($cid);
}
$sql .= " AND `credit` IN (" . join(',', $terms) . ") ";
}
} else {
$sql .= " AND `credit`=" . mysql_real_escape_string($value) . " ";
}
break;
}
}
$sql .= " GROUP BY `credit`, `code` ";
$res = mysql_query($sql);
if (!$res) {
crm_error(mysql_error());
}
$db_row = mysql_fetch_assoc($res);
while ($db_row) {
$cid = $db_row['credit'];
// Subtract all credits from balance owed
if (isset($cid_to_balance[$cid])) {
$amount = payment_invert_currency($db_row);
$cid_to_balance[$cid] = payment_add_currency($amount, $cid_to_balance[$cid]);
} else {
$cid_to_balance[$cid] = payment_invert_currency($db_row);
}
$db_row = mysql_fetch_assoc($res);
}
// Get debits
$sql = "\n SELECT `debit`, `code`, SUM(`value`) AS `value` FROM `payment` WHERE `debit` <> 0\n ";
foreach ($opts as $key => $value) {
switch ($key) {
case 'cid':
if (is_array($value)) {
$terms = array();
if (!empty($value)) {
foreach ($value as $cid) {
$terms[] = mysql_real_escape_string($cid);
}
$sql .= " AND `debit` IN (" . join(',', $terms) . ") ";
}
} else {
$sql .= " AND `debit`=" . mysql_real_escape_string($value) . " ";
}
break;
}
}
$sql .= " GROUP BY `debit`, `code` ";
$res = mysql_query($sql);
if (!$res) {
crm_error(mysql_error());
}
$db_row = mysql_fetch_assoc($res);
while ($db_row) {
// Add all debits to balance owed
$cid = $db_row['debit'];
if (isset($cid_to_balance[$cid])) {
$cid_to_balance[$cid] = payment_add_currency($cid_to_balance[$cid], $db_row);
} else {
$cid_to_balance[$cid] = $db_row;
}
$db_row = mysql_fetch_assoc($res);
}
return $cid_to_balance;
}
/**
* Update paypal_payment data when a payment is updated.
* @param $contact The contact data array.
* @param $op The operation being performed.
*/
function paypal_payment_payment_api($payment, $op)
{
if ($payment['method'] !== 'paypal') {
return $payment;
}
$email = $payment['paypal_email'];
$pmtid = $payment['pmtid'];
$credit_cid = $payment['credit_cid'];
$esc_email = mysql_real_escape_string($email);
$esc_pmtid = mysql_real_escape_string($pmtid);
// Create link between the paypal payment name and contact id
$paypal_contact = array();
if (isset($payment['paypal_email'])) {
$paypal_contact['paypal_email'] = $email;
}
if (isset($payment['credit_cid'])) {
$paypal_contact['cid'] = $credit_cid;
}
switch ($op) {
case 'insert':
$sql = "\n INSERT INTO `payment_paypal`\n (`pmtid`, `paypal_email`)\n VALUES\n ('{$esc_pmtid}', '{$esc_email}')\n ";
$res = mysql_query($sql);
if (!$res) {
crm_error(mysql_error());
}
paypal_payment_contact_save($paypal_contact);
break;
case 'update':
$sql = "\n UPDATE `payment_paypal`\n SET `paypal_email` = '{$esc_email}'\n WHERE `pmtid` = '{$esc_pmtid}'\n ";
$res = mysql_query($sql);
if (!$res) {
die(mysql_error());
}
paypal_payment_contact_save($paypal_contact);
break;
case 'delete':
$sql = "\n DELETE FROM `payment_paypal`\n WHERE `pmtid`='{$esc_pmtid}'";
$res = mysql_query($sql);
if (!$res) {
crm_error(mysql_error());
}
break;
}
return $payment;
}
/**
* @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 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));
}