<th scope="row" valign="top"><label for="trial_amount"><?php
_e('Trial Billing Amount', 'pmpro');
?>
:</label></th>
<td>
<?php
if (pmpro_getCurrencyPosition() == "left") {
echo $pmpro_currency_symbol;
}
?>
<input name="trial_amount[]" type="text" size="20" value="<?php
echo str_replace("\"", """, stripslashes($level->trial_amount));
?>
" />
<?php
if (pmpro_getCurrencyPosition() == "right") {
echo $pmpro_currency_symbol;
}
?>
<small><?php
_e('for the first', 'pmpro');
?>
</small>
<input name="trial_limit[]" type="text" size="10" value="<?php
echo str_replace("\"", """, stripslashes($level->trial_limit));
?>
" />
<small><?php
_e('subscription payments', 'pmpro');
?>
.</small>
function pmpro_report_memberships_page()
{
global $wpdb, $pmpro_currency_symbol;
//get values from form
if (isset($_REQUEST['type'])) {
$type = sanitize_text_field($_REQUEST['type']);
} else {
$type = "signup_v_all";
}
if (isset($_REQUEST['period'])) {
$period = sanitize_text_field($_REQUEST['period']);
} else {
$period = "monthly";
}
if (isset($_REQUEST['month'])) {
$month = intval($_REQUEST['month']);
} else {
$month = date("n");
}
$thisyear = date("Y");
if (isset($_REQUEST['year'])) {
$year = intval($_REQUEST['year']);
} else {
$year = date("Y");
}
if (isset($_REQUEST['level'])) {
$l = intval($_REQUEST['level']);
} else {
$l = "";
}
//calculate start date and how to group dates returned from DB
if ($period == "daily") {
$startdate = $year . '-' . substr("0" . $month, strlen($month) - 1, 2) . '-01';
$enddate = $year . '-' . substr("0" . $month, strlen($month) - 1, 2) . '-32';
$date_function = 'DAY';
} elseif ($period == "monthly") {
$startdate = $year . '-01-01';
$enddate = strval(intval($year) + 1) . '-01-01';
$date_function = 'MONTH';
} elseif ($period == "annual") {
$startdate = '1960-01-01';
//all time
$enddate = strval(intval($year) + 1) . '-01-01';
$date_function = 'YEAR';
}
//testing or live data
$gateway_environment = pmpro_getOption("gateway_environment");
//get data
if ($type === "signup_v_cancel" || $type === "signup_v_expiration" || $type === "signup_v_all") {
$sqlQuery = "SELECT {$date_function}(startdate) as date, COUNT(DISTINCT user_id) as signups\r\n\t\tFROM {$wpdb->pmpro_memberships_users} WHERE startdate >= '" . $startdate . "' ";
if (!empty($enddate)) {
$sqlQuery .= "AND startdate < '" . $enddate . "' ";
}
}
if ($type === "mrr_ltv") {
// Get total revenue, number of months in system, and date
if ($period == 'annual') {
$sqlQuery = "SELECT SUM(total) as total, COUNT(DISTINCT MONTH(timestamp)) as months, {$date_function}(timestamp) as date\r\n\t\t\tFROM {$wpdb->pmpro_membership_orders} WHERE status NOT IN('refunded', 'review', 'token')\r\n\t\t\tAND timestamp >= '" . $startdate . "' AND gateway_environment = '" . esc_sql($gateway_environment) . "' ";
}
if ($period == 'monthly') {
$sqlQuery = "SELECT SUM(total) as total, {$date_function}(timestamp) as date\r\n\t\t\tFROM {$wpdb->pmpro_membership_orders} WHERE status NOT IN('refunded', 'review', 'token')\r\n\t\t\tAND timestamp >= '" . $startdate . "' AND gateway_environment = '" . esc_sql($gateway_environment) . "' ";
}
if (!empty($enddate)) {
$sqlQuery .= "AND timestamp < '" . $enddate . "' ";
}
}
if (!empty($l)) {
$sqlQuery .= "AND membership_id IN(" . $l . ") ";
}
$sqlQuery .= " GROUP BY date ORDER BY date ";
$dates = $wpdb->get_results($sqlQuery);
//fill in blanks in dates
$cols = array();
if ($period == "daily") {
$lastday = date("t", strtotime($startdate, current_time("timestamp")));
for ($i = 1; $i <= $lastday; $i++) {
// Signups vs. Cancellations, Expirations, or All
if ($type === "signup_v_cancel" || $type === "signup_v_expiration" || $type === "signup_v_all") {
$cols[$i] = new stdClass();
$cols[$i]->signups = 0;
foreach ($dates as $day => $date) {
if ($date->date == $i) {
$cols[$i]->signups = $date->signups;
}
}
}
}
} elseif ($period == "monthly") {
for ($i = 1; $i < 13; $i++) {
// Signups vs. Cancellations, Expirations, or All
if ($type === "signup_v_cancel" || $type === "signup_v_expiration" || $type === "signup_v_all") {
$cols[$i] = new stdClass();
$cols[$i]->date = $i;
$cols[$i]->signups = 0;
foreach ($dates as $date) {
if ($date->date == $i) {
$cols[$i]->date = $date->date;
$cols[$i]->signups = $date->signups;
}
}
}
// MRR & LTV
if ($type === "mrr_ltv") {
$cols[$i] = new stdClass();
$cols[$i]->date = $i;
$cols[$i]->months = 1;
foreach ($dates as $date) {
if ($date->date == $i) {
$cols[$i]->total = $date->total;
}
}
}
}
} elseif ($period == "annual") {
}
$dates = !empty($cols) ? $cols : $dates;
// Signups vs. all
if ($type === "signup_v_cancel" || $type === "signup_v_expiration" || $type === "signup_v_all") {
$sqlQuery = "SELECT {$date_function}(mu1.modified) as date, COUNT(DISTINCT mu1.user_id) as cancellations\r\n\t\tFROM {$wpdb->pmpro_memberships_users} mu1\r\n\t\tLEFT JOIN {$wpdb->pmpro_memberships_users} mu2 ON mu1.user_id = mu2.user_id AND\r\n\t\tmu2.modified > mu1.enddate AND\r\n\t\tDATE_ADD(mu1.modified, INTERVAL 1 DAY) > mu2.startdate ";
if ($type === "signup_v_cancel") {
$sqlQuery .= "WHERE mu1.status IN('inactive','cancelled','cancelled_admin') ";
} elseif ($type === "signup_v_expiration") {
$sqlQuery .= "WHERE mu1.status IN('expired') ";
} else {
$sqlQuery .= "WHERE mu1.status IN('inactive','expired','cancelled','cancelled_admin') ";
}
$sqlQuery .= "AND mu2.id IS NULL \r\n\t\tAND mu1.startdate >= '" . $startdate . "' \r\n\t\tAND mu1.startdate < '" . $enddate . "' ";
//restrict by level
if (!empty($l)) {
$sqlQuery .= "AND mu1.membership_id IN(" . $l . ") ";
}
$sqlQuery .= " GROUP BY date ORDER BY date ";
/**
* Filter query to get cancellation numbers in signups vs cancellations detailed report.
*
* @since 1.8.8
*
* @param string $sqlQuery The current SQL
* @param string $type report type
* @param string $startdate Start Date in YYYY-MM-DD format
* @param string $enddate End Date in YYYY-MM-DD format
* @param int $l Level ID
*/
$sqlQuery = apply_filters('pmpro_reports_signups_sql', $sqlQuery, $type, $startdate, $enddate, $l);
$cdates = $wpdb->get_results($sqlQuery, OBJECT_K);
foreach ($dates as $day => &$date) {
if (!empty($cdates) && !empty($cdates[$day])) {
$date->cancellations = $cdates[$day]->cancellations;
} else {
$date->cancellations = 0;
}
}
}
// MRR & LTV
if ($type === "mrr_ltv" && count($dates) === 1) {
$dummy_date = new stdClass();
$dummy_date->total = 0;
$dummy_date->months = 0;
$dummy_date->date = $dates[0]->date - 1;
array_unshift($dates, $dummy_date);
// Add to beginning
}
?>
<form id="posts-filter" method="get" action="">
<h1>
<?php
_e('Membership Stats', 'pmpro');
?>
</h1>
<ul class="subsubsub">
<li>
<?php
_e('Show', 'pmpro');
?>
<select id="period" name="period">
<option value="daily" <?php
selected($period, "daily");
?>
><?php
_e('Daily', 'pmpro');
?>
</option>
<option value="monthly" <?php
selected($period, "monthly");
?>
><?php
_e('Monthly', 'pmpro');
?>
</option>
<option value="annual" <?php
selected($period, "annual");
?>
><?php
_e('Annual', 'pmpro');
?>
</option>
</select>
<select id="type" name="type">
<option value="signup_v_all" <?php
selected($type, "signup_v_all");
?>
><?php
_e('Signups vs. All Cancellations', 'pmpro');
?>
</option>
<option value="signup_v_cancel" <?php
selected($type, "signup_v_cancel");
?>
><?php
_e('Signups vs. Cancellations', 'pmpro');
?>
</option>
<option value="signup_v_expiration" <?php
selected($type, "signup_v_expiration");
?>
><?php
_e('Signups vs. Expirations', 'pmpro');
?>
</option>
<?php
/*
<option value="mrr_ltv" <?php selected($type, "mrr_ltv");?>><?php _e('MRR & LTV', 'pmpro');?></option>
*/
?>
</select>
<span id="for"><?php
_e('for', 'pmpro');
?>
</span>
<select id="month" name="month">
<?php
for ($i = 1; $i < 13; $i++) {
?>
<option value="<?php
echo $i;
?>
" <?php
selected($month, $i);
?>
><?php
echo date("F", mktime(0, 0, 0, $i, 2));
?>
</option>
<?php
}
?>
</select>
<select id="year" name="year">
<?php
for ($i = $thisyear; $i > 2007; $i--) {
?>
<option value="<?php
echo $i;
?>
" <?php
selected($year, $i);
?>
><?php
echo $i;
?>
</option>
<?php
}
?>
</select>
<span id="for"><?php
_e('for', 'pmpro');
?>
</span>
<select name="level">
<option value="" <?php
if (!$l) {
?>
selected="selected"<?php
}
?>
><?php
_e('All Levels', 'pmpro');
?>
</option>
<?php
$levels = $wpdb->get_results("SELECT id, name FROM {$wpdb->pmpro_membership_levels} ORDER BY name");
foreach ($levels as $level) {
?>
<option value="<?php
echo $level->id;
?>
" <?php
if ($l == $level->id) {
?>
selected="selected"<?php
}
?>
><?php
echo $level->name;
?>
</option>
<?php
}
?>
</select>
<input type="hidden" name="page" value="pmpro-reports" />
<input type="hidden" name="report" value="memberships" />
<input type="submit" class="button" value="<?php
_e('Generate Report', 'pmpro');
?>
" />
</li>
</ul>
<div id="chart_div" style="clear: both; width: 100%; height: 500px;"></div>
<script>
//update month/year when period dropdown is changed
jQuery(document).ready(function() {
jQuery('#period').change(function() {
pmpro_ShowMonthOrYear();
});
});
function pmpro_ShowMonthOrYear()
{
var period = jQuery('#period').val();
if(period == 'daily')
{
jQuery('#for').show();
jQuery('#month').show();
jQuery('#year').show();
}
else if(period == 'monthly')
{
jQuery('#for').show();
jQuery('#month').hide();
jQuery('#year').show();
}
else
{
jQuery('#for').hide();
jQuery('#month').hide();
jQuery('#year').hide();
}
}
pmpro_ShowMonthOrYear();
//draw the chart
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart);
function drawChart() {
var data = google.visualization.arrayToDataTable([
<?php
if ($type === "signup_v_all") {
// Signups vs. all cancellations
?>
['<?php
echo $date_function;
?>
', 'Signups', 'All Cancellations'],
<?php
foreach ($dates as $key => $value) {
?>
['<?php
if ($period == "monthly") {
echo date("M", mktime(0, 0, 0, $value->date, 2));
} else {
if ($period == "daily") {
echo $key;
} else {
echo $value->date;
}
}
?>
', <?php
echo $value->signups;
?>
, <?php
echo $value->cancellations;
?>
],
<?php
}
?>
<?php
}
?>
<?php
if ($type === "signup_v_cancel") {
// Signups vs. cancellations
?>
['<?php
echo $date_function;
?>
', 'Signups', 'Cancellations'],
<?php
foreach ($dates as $key => $value) {
?>
['<?php
if ($period == "monthly") {
echo date("M", mktime(0, 0, 0, $value->date, 2));
} else {
if ($period == "daily") {
echo $key;
} else {
echo $value->date;
}
}
?>
', <?php
echo $value->signups;
?>
, <?php
echo $value->cancellations;
?>
],
<?php
}
?>
<?php
}
?>
<?php
if ($type === "signup_v_expiration") {
// Signups vs. expirations
?>
['<?php
echo $date_function;
?>
', 'Signups', 'Expirations'],
<?php
foreach ($dates as $key => $value) {
?>
['<?php
if ($period == "monthly") {
echo date("M", mktime(0, 0, 0, $value->date, 2));
} else {
if ($period == "daily") {
echo $key;
} else {
echo $value->date;
}
}
?>
', <?php
echo $value->signups;
?>
, <?php
echo $value->cancellations;
?>
],
<?php
}
?>
<?php
}
?>
<?php
if ($type === "mrr_ltv") {
?>
['<?php
echo $date_function;
?>
', 'MRR', 'LTV'],
<?php
foreach ($dates as $key => $value) {
?>
['<?php
if ($period == "monthly") {
echo date("M", mktime(0, 0, 0, $value->date, 2));
} else {
if ($period == "daily") {
echo $key;
} else {
echo $value->date;
}
}
?>
', <?php
echo ($mrr = $value->total / $value->months) && $mrr != 0 ? $mrr : 0;
?>
, <?php
echo pmpro_getLTV($period, NULL, $mrr);
?>
],
<?php
}
?>
<?php
}
?>
]);
var options = {
colors: ['#0099c6', '#dc3912'],
hAxis: {title: '<?php
echo $date_function;
?>
', titleTextStyle: {color: 'black'}, maxAlternation: 1},
vAxis: {color: 'green', titleTextStyle: {color: '#51a351'}},
};
<?php
if ($type === "signup_v_cancel" || $type === "signup_v_expiration" || $type === "signup_v_all") {
// Signups vs. cancellations
?>
var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
<?php
} elseif ($type === "mrr_ltv") {
// MRR & LTV
?>
<?php
//prefix or suffix?
if (pmpro_getCurrencyPosition() == "right") {
$position = "suffix";
} else {
$position = "prefix";
}
?>
var formatter = new google.visualization.NumberFormat({<?php
echo $position;
?>
: '<?php
echo html_entity_decode($pmpro_currency_symbol);
?>
'});
formatter.format(data, 2);
var formatter = new google.visualization.NumberFormat({<?php
echo $position;
?>
: '<?php
echo html_entity_decode($pmpro_currency_symbol);
?>
'});
formatter.format(data, 1);
var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
<?php
}
?>
chart.draw(data, options);
}
</script>
</form>
<?php
}
function pmpro_update_existing_subscriptions()
{
//only admins can get this
if (!function_exists("current_user_can") || !current_user_can("manage_options")) {
die(__("You do not have permissions to perform this action.", "pmpro"));
}
//vars
global $pmpro_currency_symbol, $gateway;
if (isset($_REQUEST['pmproues_gateway'])) {
$pmproues_gateway = $_REQUEST['pmproues_gateway'];
} else {
$pmproues_gateway = "stripe";
}
if (isset($_REQUEST['pmproues_level'])) {
$pmproues_level = $_REQUEST['pmproues_level'];
} else {
$pmproues_level = "";
}
if (isset($_REQUEST['pmproues_billing_amount'])) {
$pmproues_billing_amount = $_REQUEST['pmproues_billing_amount'];
} else {
$pmproues_billing_amount = "";
}
if (isset($_REQUEST['pmproues_cycle_number'])) {
$pmproues_cycle_number = $_REQUEST['pmproues_cycle_number'];
} else {
$pmproues_cycle_number = "1";
}
if (isset($_REQUEST['pmproues_cycle_period'])) {
$pmproues_cycle_period = $_REQUEST['pmproues_cycle_period'];
} else {
$pmproues_cycle_period = "Month";
}
require_once PMPRO_DIR . "/adminpages/admin_header.php";
//clear out msg fields
$msg = "";
$msgt = "";
//running?
if (!empty($_REQUEST['updatesubscriptions'])) {
//check fields
if (empty($pmproues_gateway) || empty($pmproues_level)) {
$msg = __('You must select a gateway and level to update.');
$msgt = 'error';
} elseif (!empty($pmproues_billing_amount) && (empty($pmproues_cycle_number) || empty($pmproues_cycle_period))) {
$msg = __('Select a cycle number and billing period or use billing amount 0 to cancel the subscriptions.');
$msgt = 'error';
} else {
$updatesubscriptions = true;
}
}
?>
<h2><?php
_e('Update Existing Subscriptions at the Gateway', 'pmpro');
?>
</h2>
<?php
if (!empty($msg)) {
?>
<div class="message <?php
echo $msgt;
?>
"><p><?php
echo $msg;
?>
</p></div>
<?php
}
?>
<p><?php
_e('This plugin currently supports updating Stripe subscriptions only. You can choose one level to update at a time and set a new billing amount and/or period for all active subscriptions for users of that level.');
?>
</p>
<?php
if (!empty($updatesubscriptions)) {
?>
<p id="pmproues_updates_intro"><?php
_e('Updates are processing. This may take a few minutes to complete.', 'pmproues');
?>
</p>
<textarea id="pmproues_updates_status" rows="20" cols="120">Loading...</textarea>
<?php
} else {
?>
<form action="" method="post">
<table class="form-table">
<tbody>
<tr>
<th scope="row">
<label for="pmproues_gateway"><?php
_e('Gateway', 'pmproues');
?>
</label>
</th>
<td>
<select name="pmproues_gateway" id="pmproues_gateway">
<option value="stripe"><?php
_e('Stripe', 'pmproues');
?>
</option>
</select>
</td>
</tr>
<tr>
<th scope="row">
<label for="pmproues_level"><?php
_e('Level', 'pmproues');
?>
</label>
</th>
<td>
<select name="pmproues_level" id="pmproues_level">
<option value="">- <?php
_e('Choose One', 'pmproues');
?>
-</option>
<?php
$levels = pmpro_getAllLevels(true, true);
foreach ($levels as $level) {
?>
<option value="<?php
echo $level->id;
?>
"><?php
echo $level->name;
?>
</option>
<?php
}
?>
</select>
</td>
</tr>
<tr>
<th scope="row" valign="top"><label for="pmproues_billing_amount"><?php
_e('New Billing Amount', 'pmproues');
?>
:</label></th>
<td>
<?php
if (pmpro_getCurrencyPosition() == "left") {
echo $pmpro_currency_symbol;
}
?>
<input id="pmproues_billing_amount" name="pmproues_billing_amount" type="text" size="20" value="<?php
echo esc_attr($pmproues_billing_amount);
?>
" />
<?php
if (pmpro_getCurrencyPosition() == "right") {
echo $pmpro_currency_symbol;
}
?>
<small><?php
_e('per', 'pmpro');
?>
</small>
<input id="pmproues_cycle_number" name="pmproues_cycle_number" type="text" size="10" value="<?php
echo esc_attr($pmproues_cycle_number);
?>
" />
<select id="pmproues_cycle_period" name="pmproues_cycle_period">
<?php
$cycles = array(__('Day(s)', 'pmpro') => 'Day', __('Week(s)', 'pmpro') => 'Week', __('Month(s)', 'pmpro') => 'Month', __('Year(s)', 'pmpro') => 'Year');
foreach ($cycles as $name => $value) {
echo "<option value='{$value}'";
if ($pmproues_cycle_period == $value) {
echo " selected='selected'";
}
echo ">{$name}</option>";
}
?>
</select>
<br /><small>
<?php
_e('Use billing amount of 0 to cancel subscriptions.', 'pmproues');
?>
<?php
if ($gateway == "stripe") {
?>
<br /><strong <?php
if (!empty($pmpro_stripe_error)) {
?>
class="pmpro_red"<?php
}
?>
><?php
_e('Stripe integration currently only supports billing periods of "Week", "Month" or "Year".', 'pmpro');
?>
<?php
} elseif ($gateway == "braintree") {
?>
<br /><strong <?php
if (!empty($pmpro_braintree_error)) {
?>
class="pmpro_red"<?php
}
?>
><?php
_e('Braintree integration currently only supports billing periods of "Month" or "Year".', 'pmpro');
?>
<?php
} elseif ($gateway == "payflowpro") {
?>
<br /><strong <?php
if (!empty($pmpro_payflow_error)) {
?>
class="pmpro_red"<?php
}
?>
><?php
_e('Payflow integration currently only supports billing frequencies of 1 and billing periods of "Week", "Month" or "Year".', 'pmpro');
?>
<?php
}
?>
</small>
<?php
if ($gateway == "braintree" && $edit < 0) {
?>
<p class="pmpro_message"><strong><?php
_e('Note', 'pmpro');
?>
:</strong> <?php
_e('After saving this level, make note of the ID and create a "Plan" in your Braintree dashboard with the same settings and the "Plan ID" set to <em>pmpro_#</em>, where # is the level ID.', 'pmpro');
?>
</p>
<?php
} elseif ($gateway == "braintree") {
?>
<p class="pmpro_message"><strong><?php
_e('Note', 'pmpro');
?>
:</strong> <?php
_e('You will need to create a "Plan" in your Braintree dashboard with the same settings and the "Plan ID" set to', 'pmpro');
?>
<em>pmpro_<?php
echo $level->id;
?>
</em>.</p>
<?php
}
?>
</td>
</tr>
<tr>
<th scope="row">
<label for="pmproues_live"><?php
_e('Test or Live', 'pmproues');
?>
</label>
</th>
<td>
<select name="pmproues_live" id="pmproues_live">
<option value="0"><?php
_e('Test Run', 'pmproues');
?>
</option>
<option value="1"><?php
_e('Live Run', 'pmproues');
?>
</option>
</select>
<small><?php
_e('When testing, planned updates are shown but not made at the gateway.', 'pmproues');
?>
</small>
</td>
</tr>
</tbody>
</table>
<p class="submit topborder">
<input type="hidden" name="updatesubscriptions" value="1" />
<input name="run" type="submit" class="button-primary" value="<?php
_e('Run Update', 'pmproues');
?>
" />
</p>
</form>
<?php
}
?>
<?php
require_once PMPRO_DIR . "/adminpages/admin_footer.php";
}
function pmpro_report_sales_page()
{
global $wpdb, $pmpro_currency_symbol, $pmpro_currency, $pmpro_currencies;
//get values from form
if (isset($_REQUEST['type'])) {
$type = sanitize_text_field($_REQUEST['type']);
} else {
$type = "revenue";
}
if ($type == "sales") {
$type_function = "COUNT";
} else {
$type_function = "SUM";
}
if (isset($_REQUEST['period'])) {
$period = sanitize_text_field($_REQUEST['period']);
} else {
$period = "daily";
}
if (isset($_REQUEST['month'])) {
$month = intval($_REQUEST['month']);
} else {
$month = date("n");
}
$thisyear = date("Y");
if (isset($_REQUEST['year'])) {
$year = intval($_REQUEST['year']);
} else {
$year = $thisyear;
}
if (isset($_REQUEST['level'])) {
$l = intval($_REQUEST['level']);
} else {
$l = "";
}
//calculate start date and how to group dates returned from DB
if ($period == "daily") {
$startdate = $year . '-' . substr("0" . $month, strlen($month) - 1, 2) . '-01';
$enddate = $year . '-' . substr("0" . $month, strlen($month) - 1, 2) . '-32';
$date_function = 'DAY';
} elseif ($period == "monthly") {
$startdate = $year . '-01-01';
$enddate = strval(intval($year) + 1) . '-01-01';
$date_function = 'MONTH';
} else {
$startdate = '1960-01-01';
//all time
$date_function = 'YEAR';
}
//testing or live data
$gateway_environment = pmpro_getOption("gateway_environment");
//get data
$sqlQuery = "SELECT {$date_function}(timestamp) as date, {$type_function}(total) as value FROM {$wpdb->pmpro_membership_orders} WHERE timestamp >= '" . $startdate . "' AND status NOT IN('refunded', 'review', 'token', 'error') AND gateway_environment = '" . esc_sql($gateway_environment) . "' ";
if (!empty($enddate)) {
$sqlQuery .= "AND timestamp < '" . $enddate . "' ";
}
if (!empty($l)) {
$sqlQuery .= "AND membership_id IN(" . $l . ") ";
}
$sqlQuery .= " GROUP BY date ORDER BY date ";
$dates = $wpdb->get_results($sqlQuery);
//fill in blanks in dates
$cols = array();
if ($period == "daily") {
$lastday = date("t", strtotime($startdate, current_time("timestamp")));
for ($i = 1; $i <= $lastday; $i++) {
$cols[$i] = 0;
foreach ($dates as $date) {
if ($date->date == $i) {
$cols[$i] = $date->value;
}
}
}
} elseif ($period == "monthly") {
for ($i = 1; $i < 13; $i++) {
$cols[$i] = 0;
foreach ($dates as $date) {
if ($date->date == $i) {
$cols[$i] = $date->value;
}
}
}
} else {
//get min and max years
$min = 9999;
$max = 0;
foreach ($dates as $date) {
$min = min($min, $date->date);
$max = max($max, $date->date);
}
for ($i = $min; $i <= $max; $i++) {
foreach ($dates as $date) {
if ($date->date == $i) {
$cols[$i] = $date->value;
}
}
}
}
?>
<form id="posts-filter" method="get" action="">
<h2>
<?php
_e('Sales and Revenue', 'pmpro');
?>
</h2>
<div class="tablenav top">
<?php
_ex('Show', 'Dropdown label, e.g. Show Daily Revenue for January', 'pmpro');
?>
<select id="period" name="period">
<option value="daily" <?php
selected($period, "daily");
?>
><?php
_e('Daily', 'pmpro');
?>
</option>
<option value="monthly" <?php
selected($period, "monthly");
?>
><?php
_e('Monthly', 'pmpro');
?>
</option>
<option value="annual" <?php
selected($period, "annual");
?>
><?php
_e('Annual', 'pmpro');
?>
</option>
</select>
<select name="type">
<option value="revenue" <?php
selected($type, "revenue");
?>
><?php
_e('Revenue', 'pmpro');
?>
</option>
<option value="sales" <?php
selected($type, "sales");
?>
><?php
_e('Sales', 'pmpro');
?>
</option>
</select>
<span id="for"><?php
_ex('for', 'Dropdown label, e.g. Show Daily Revenue for January', 'pmpro');
?>
</span>
<select id="month" name="month">
<?php
for ($i = 1; $i < 13; $i++) {
?>
<option value="<?php
echo $i;
?>
" <?php
selected($month, $i);
?>
><?php
echo date("F", mktime(0, 0, 0, $i, 2));
?>
</option>
<?php
}
?>
</select>
<select id="year" name="year">
<?php
for ($i = $thisyear; $i > 2007; $i--) {
?>
<option value="<?php
echo $i;
?>
" <?php
selected($year, $i);
?>
><?php
echo $i;
?>
</option>
<?php
}
?>
</select>
<span id="for"><?php
_ex('for', 'Dropdown label, e.g. Show Daily Revenue for January', 'pmpro');
?>
</span>
<select name="level">
<option value="" <?php
if (!$l) {
?>
selected="selected"<?php
}
?>
><?php
_e('All Levels', 'pmpro');
?>
</option>
<?php
$levels = $wpdb->get_results("SELECT id, name FROM {$wpdb->pmpro_membership_levels} ORDER BY name");
foreach ($levels as $level) {
?>
<option value="<?php
echo $level->id;
?>
" <?php
if ($l == $level->id) {
?>
selected="selected"<?php
}
?>
><?php
echo $level->name;
?>
</option>
<?php
}
?>
</select>
<input type="hidden" name="page" value="pmpro-reports" />
<input type="hidden" name="report" value="sales" />
<input type="submit" class="button action" value="<?php
_ex('Generate Report', 'Submit button value.', 'pmpro');
?>
" />
</div>
<div id="chart_div" style="clear: both; width: 100%; height: 500px;"></div>
<script>
//update month/year when period dropdown is changed
jQuery(document).ready(function() {
jQuery('#period').change(function() {
pmpro_ShowMonthOrYear();
});
});
function pmpro_ShowMonthOrYear()
{
var period = jQuery('#period').val();
if(period == 'daily')
{
jQuery('#for').show();
jQuery('#month').show();
jQuery('#year').show();
}
else if(period == 'monthly')
{
jQuery('#for').show();
jQuery('#month').hide();
jQuery('#year').show();
}
else
{
jQuery('#for').hide();
jQuery('#month').hide();
jQuery('#year').hide();
}
}
pmpro_ShowMonthOrYear();
//draw the chart
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart);
function drawChart() {
var data = google.visualization.arrayToDataTable([
['<?php
echo $date_function;
?>
', '<?php
echo ucwords($type);
?>
'],
<?php
foreach ($cols as $date => $value) {
?>
['<?php
if ($period == "monthly") {
echo date("M", mktime(0, 0, 0, $date, 2));
} else {
echo $date;
}
?>
', <?php
echo $value;
?>
],
<?php
}
?>
]);
var options = {
colors: ['#51a351', '#387038'],
hAxis: {title: '<?php
echo $date_function;
?>
', titleTextStyle: {color: 'black'}, maxAlternation: 1},
vAxis: {color: 'green', titleTextStyle: {color: '#51a351'}},
};
<?php
if ($type != "sales") {
if (pmpro_getCurrencyPosition() == "right") {
$position = "suffix";
} else {
$position = "prefix";
}
?>
var formatter = new google.visualization.NumberFormat({<?php
echo $position;
?>
: '<?php
echo html_entity_decode($pmpro_currency_symbol);
?>
'});
formatter.format(data, 1);
<?php
}
?>
var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
chart.draw(data, options);
}
</script>
</form>
<?php
}