/**
* @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);
}
/**
* Returns the form structure for editing a membership plan.
*
* @param $pid The pid of the membership plan to edit.
* @return The form structure.
*/
function member_plan_edit_form($pid)
{
// Ensure user is allowed to edit plans
if (!user_access('member_plan_edit')) {
return NULL;
}
// Get plan data
$plans = member_plan_data(array('pid' => $pid));
$plan = $plans[0];
if (!$plan) {
return NULL;
}
// Create form structure
$form = array('type' => 'form', 'method' => 'post', 'command' => 'member_plan_update', 'hidden' => array('pid' => $pid), 'fields' => array(array('type' => 'fieldset', 'label' => 'Edit Membership Plan', 'fields' => array(array('type' => 'text', 'label' => 'Name', 'name' => 'name', 'value' => $plan['name']), array('type' => 'text', 'label' => 'Price', 'name' => 'price', 'value' => $plan['price']), array('type' => 'checkbox', 'label' => 'Voting', 'name' => 'voting', 'checked' => $plan['voting']), array('type' => 'checkbox', 'label' => 'Active', 'name' => 'active', 'checked' => $plan['active']), array('type' => 'submit', 'value' => 'Update')))));
return $form;
}
/**
* Generates an associative array mapping membership plan pids to
* strings describing those membership plan.
*
* @param $opts Options to be passed to member_plan_data().
* @return The associative array of membership plan descriptions.
*/
function member_plan_options($opts = NULL)
{
// Get plan data
$plans = member_plan_data($opts);
// Add option for each member plan
$options = array();
foreach ($plans as $plan) {
$options[$plan['pid']] = "{$plan['name']} - {$plan['price']}";
}
return $options;
}
/**
* Return a table structure representing membership plans.
*
* @param $opts Options to pass to member_plan_data().
* @return The table structure.
*/
function member_plan_table($opts = NULL)
{
// Ensure user is allowed to view membership plans
if (!user_access('member_plan_edit')) {
return NULL;
}
// Get membership plan data
$plans = member_plan_data($opts);
// Create table structure
$table = array('id' => '', 'class' => '', 'rows' => array());
// Add columns
$table['columns'] = array();
if (user_access('member_plan_edit')) {
$table['columns'][] = array('title' => 'Name', 'class' => '');
$table['columns'][] = array('title' => 'Price', 'class' => '');
$table['columns'][] = array('title' => 'Active', 'class' => '');
$table['columns'][] = array('title' => 'Voting', 'class' => '');
$table['columns'][] = array('title' => 'Ops', 'class' => '');
}
// Loop through plan data
foreach ($plans as $plan) {
// Add plan data to table
$row = array();
if (user_access('member_plan_edit')) {
// Add cells
$row[] = $plan['name'];
$row[] = $plan['price'];
$row[] = $plan['active'] ? 'Yes' : 'No';
$row[] = $plan['voting'] ? 'Yes' : 'No';
}
// Construct ops array
$ops = array();
// Add edit op
if (user_access('member_plan_edit')) {
$ops[] = '<a href=' . crm_url('plan&pid=' . $plan['pid'] . '&tab=edit') . '>edit</a>';
}
// Add delete op
if (user_access('member_plan_edit')) {
$ops[] = '<a href=' . crm_url('delete&type=member_plan&id=' . $plan['pid']) . '>delete</a>';
}
// Add ops row
if (user_access('member_plan_edit')) {
$row[] = join(' ', $ops);
}
// Add row to table
$table['rows'][] = $row;
}
// Return table
return $table;
}
/**
* Return the themed html description for a plan.
*
* @param $pid The pid of the plan.
* @return The themed html string.
*/
function theme_member_plan_description($pid)
{
// Get plan data
$data = member_plan_data(array('pid' => $pid));
if (count($data) < 1) {
return '';
}
$output = $data[0]['name'] . ' : ' . $data[0]['price'];
return $output;
}