pmpro_report_memberships_page

The Paid Memberships Pro pmpro report memberships page function.

Description

pmpro_report_memberships_page(); 

Usage

  1. if ( !function_exists( 'pmpro_report_memberships_page' ) ) { 
  2. require_once ABSPATH . PLUGINDIR . 'paid-memberships-pro/adminpages/reports/memberships.php'; 
  3.  
  4.  
  5. // NOTICE! Understand what this does before running. 
  6. $result = pmpro_report_memberships_page(); 
  7.  

Defined (1)

The function is defined in the following location(s).

/adminpages/reports/memberships.php  
  1. function pmpro_report_memberships_page() 
  2. global $wpdb, $pmpro_currency_symbol; 
  3.  
  4. //get values from form 
  5. if(isset($_REQUEST['type'])) 
  6. $type = sanitize_text_field($_REQUEST['type']); 
  7. else 
  8. $type = "signup_v_all"; 
  9.  
  10. if(isset($_REQUEST['period'])) 
  11. $period = sanitize_text_field($_REQUEST['period']); 
  12. else 
  13. $period = "monthly"; 
  14.  
  15. if(isset($_REQUEST['month'])) 
  16. $month = intval($_REQUEST['month']); 
  17. else 
  18. $month = date_i18n("n"); 
  19.  
  20. $thisyear = date_i18n("Y"); 
  21. if(isset($_REQUEST['year'])) 
  22. $year = intval($_REQUEST['year']); 
  23. else 
  24. $year = date_i18n("Y"); 
  25.  
  26. if(isset($_REQUEST['level'])) 
  27. $l = intval($_REQUEST['level']); 
  28. else 
  29. $l = ""; 
  30.  
  31. //calculate start date and how to group dates returned from DB 
  32. if($period == "daily") 
  33. $startdate = $year . '-' . substr("0" . $month, strlen($month) - 1, 2) . '-01';  
  34. $enddate = $year . '-' . substr("0" . $month, strlen($month) - 1, 2) . '-32';  
  35. $date_function = 'DAY'; 
  36. elseif($period == "monthly") 
  37. $startdate = $year . '-01-01'; 
  38. $enddate = strval(intval($year)+1) . '-01-01'; 
  39. $date_function = 'MONTH'; 
  40. elseif($period == "annual") 
  41. $startdate = '1960-01-01'; //all time 
  42. $enddate = strval(intval($year)+1) . '-01-01'; 
  43. $date_function = 'YEAR'; 
  44.  
  45. //testing or live data 
  46. $gateway_environment = pmpro_getOption("gateway_environment"); 
  47.  
  48. //get data 
  49. if ( $type === "signup_v_cancel" || $type === "signup_v_expiration" || $type === "signup_v_all") { 
  50. $sqlQuery = "SELECT $date_function(startdate) as date, COUNT(DISTINCT user_id) as signups 
  51. FROM $wpdb->pmpro_memberships_users WHERE startdate >= '" . $startdate . "' "; 
  52.  
  53. if(!empty($enddate)) 
  54. $sqlQuery .= "AND startdate < '" . $enddate . "' "; 
  55. if ( $type === "mrr_ltv" ) { 
  56. // Get total revenue, number of months in system, and date 
  57. if ( $period == 'annual' ) 
  58. $sqlQuery = "SELECT SUM(total) as total, COUNT(DISTINCT MONTH(timestamp)) as months, $date_function(timestamp) as date 
  59. FROM $wpdb->pmpro_membership_orders WHERE status NOT IN('refunded', 'review', token) 
  60. AND timestamp >= '" . $startdate . "' AND gateway_environment = '" . esc_sql($gateway_environment) . "' "; 
  61.  
  62. if ( $period == 'monthly' ) 
  63. $sqlQuery = "SELECT SUM(total) as total, $date_function(timestamp) as date 
  64. FROM $wpdb->pmpro_membership_orders WHERE status NOT IN('refunded', 'review', token) 
  65. AND timestamp >= '" . $startdate . "' AND gateway_environment = '" . esc_sql($gateway_environment) . "' "; 
  66.  
  67. if(!empty($enddate)) 
  68. $sqlQuery .= "AND timestamp < '" . $enddate . "' "; 
  69.  
  70. if(!empty($l)) 
  71. $sqlQuery .= "AND membership_id IN(" . $l . ") "; 
  72.  
  73. $sqlQuery .= " GROUP BY dateORDERBY date "; 
  74.  
  75. $dates = $wpdb->get_results($sqlQuery); 
  76.  
  77. //fill in blanks in dates 
  78. $cols = array();  
  79. if($period == "daily") 
  80. $lastday = date_i18n("t", strtotime($startdate, current_time("timestamp"))); 
  81.  
  82. for($i = 1; $i <= $lastday; $i++) 
  83. // Signups vs. Cancellations, Expirations, or All 
  84. if ( $type === "signup_v_cancel" || $type === "signup_v_expiration" || $type === "signup_v_all" ) { 
  85. $cols[$i] = new stdClass(); 
  86. $cols[$i]->signups = 0; 
  87. foreach($dates as $day => $date) 
  88. if( $date->date == $i ) { 
  89. $cols[$i]->signups = $date->signups; 
  90. elseif($period == "monthly") 
  91. for($i = 1; $i < 13; $i++) 
  92. // Signups vs. Cancellations, Expirations, or All 
  93. if ( $type === "signup_v_cancel" || $type === "signup_v_expiration" || $type === "signup_v_all" ) { 
  94. $cols[$i] = new stdClass(); 
  95. $cols[$i]->date = $i; 
  96. $cols[$i]->signups = 0; 
  97. foreach($dates as $date) 
  98. if( $date->date == $i ) { 
  99. $cols[$i]->date = $date->date; 
  100. $cols[$i]->signups = $date->signups; 
  101.  
  102. // MRR & LTV 
  103. if ( $type === "mrr_ltv" ) { 
  104. $cols[$i] = new stdClass(); 
  105. $cols[$i]->date = $i; 
  106. $cols[$i]->months = 1; 
  107. foreach($dates as $date) 
  108. if( $date->date == $i ) { 
  109. $cols[$i]->total = $date->total; 
  110. elseif($period == "annual") //annual 
  111.  
  112. $dates = ( ! empty( $cols ) ) ? $cols : $dates; 
  113.  
  114. // Signups vs. all 
  115. if ( $type === "signup_v_cancel" || $type === "signup_v_expiration" || $type === "signup_v_all" ) 
  116. $sqlQuery = "SELECT $date_function(mu1.modified) as date, COUNT(DISTINCT mu1.user_id) as cancellations 
  117. FROM $wpdb->pmpro_memberships_users mu1 "; 
  118. if ( $type === "signup_v_cancel") 
  119. $sqlQuery .= "WHERE mu1.status IN('inactive', 'cancelled', 'cancelled_admin') "; 
  120. elseif($type === "signup_v_expiration") 
  121. $sqlQuery .= "WHERE mu1.status IN('expired') "; 
  122. else 
  123. $sqlQuery .= "WHERE mu1.status IN('inactive', 'expired', 'cancelled', 'cancelled_admin') "; 
  124.  
  125. $sqlQuery .= "AND mu1.startdate >= '" . $startdate . "'  
  126. AND mu1.startdate < '" . $enddate . "' "; 
  127.  
  128. //restrict by level 
  129. if(!empty($l)) 
  130. $sqlQuery .= "AND mu1.membership_id IN(" . $l . ") "; 
  131.  
  132. $sqlQuery .= " GROUP BY dateORDERBY date "; 
  133.  
  134. /** 
  135. * Filter query to get cancellation numbers in signups vs cancellations detailed report. 
  136. * @since 1.8.8 
  137. * @param string $sqlQuery The current SQL 
  138. * @param string $type report type 
  139. * @param string $startdate Start Date in YYYY-MM-DD format 
  140. * @param string $enddate End Date in YYYY-MM-DD format 
  141. * @param int $l Level ID 
  142. */ 
  143. $sqlQuery = apply_filters('pmpro_reports_signups_sql', $sqlQuery, $type, $startdate, $enddate, $l); 
  144.  
  145. $cdates = $wpdb->get_results($sqlQuery, OBJECT_K);  
  146.  
  147. foreach( $dates as $day => &$date ) 
  148. if(!empty($cdates) && !empty($cdates[$day])) 
  149. $date->cancellations = $cdates[$day]->cancellations; 
  150. else 
  151. $date->cancellations = 0; 
  152.  
  153. // MRR & LTV 
  154. if ( $type === "mrr_ltv" && count( $dates ) === 1 ) { 
  155. $dummy_date = new stdClass(); 
  156. $dummy_date->total = 0; 
  157. $dummy_date->months = 0; 
  158. $dummy_date->date = $dates[0]->date - 1;  
  159. array_unshift( $dates, $dummy_date ); // Add to beginning 
  160. ?> 
  161. <form id="posts-filter" method="get" action="">  
  162. <h1> 
  163. <?php _e('Membership Stats', 'paid-memberships-pro' );?> 
  164. </h1> 
  165. <ul class="subsubsub"> 
  166. <li> 
  167. <?php _e('Show', 'paid-memberships-pro' )?> 
  168. <select id="period" name="period"> 
  169. <option value="daily" <?php selected($period, "daily");?>><?php _e('Daily', 'paid-memberships-pro' );?></option> 
  170. <option value="monthly" <?php selected($period, "monthly");?>><?php _e('Monthly', 'paid-memberships-pro' );?></option> 
  171. <option value="annual" <?php selected($period, "annual");?>><?php _e('Annual', 'paid-memberships-pro' );?></option> 
  172. </select> 
  173. <select id="type" name="type"> 
  174. <option value="signup_v_all" <?php selected($type, "signup_v_all");?>><?php _e('Signups vs. All Cancellations', 'paid-memberships-pro' );?></option> 
  175. <option value="signup_v_cancel" <?php selected($type, "signup_v_cancel");?>><?php _e('Signups vs. Cancellations', 'paid-memberships-pro' );?></option> 
  176. <option value="signup_v_expiration" <?php selected($type, "signup_v_expiration");?>><?php _e('Signups vs. Expirations', 'paid-memberships-pro' );?></option> 
  177. <?php /** 
  178. <option value="mrr_ltv" <?php selected($type, "mrr_ltv");?>><?php _e('MRR & LTV', 'paid-memberships-pro' );?></option> 
  179. */ ?> 
  180. </select> 
  181. <span id="for"><?php _e('for', 'paid-memberships-pro' )?></span> 
  182. <select id="month" name="month"> 
  183. <?php for($i = 1; $i < 13; $i++) { ?> 
  184. <option value="<?php echo $i;?>" <?php selected($month, $i);?>><?php echo date_i18n("F", mktime(0, 0, 0, $i, 2));?></option> 
  185. <?php } ?> 
  186. </select> 
  187. <select id="year" name="year"> 
  188. <?php for($i = $thisyear; $i > 2007; $i--) { ?> 
  189. <option value="<?php echo $i;?>" <?php selected($year, $i);?>><?php echo $i;?></option> 
  190. <?php } ?> 
  191. </select> 
  192. <span id="for"><?php _e('for', 'paid-memberships-pro' )?></span> 
  193. <select name="level"> 
  194. <option value="" <?php if(!$l) { ?>selected="selected"<?php } ?>><?php _e('All Levels', 'paid-memberships-pro' );?></option> 
  195. <?php 
  196. $levels = $wpdb->get_results("SELECT id, name FROM $wpdb->pmpro_membership_levelsORDERBY name"); 
  197. foreach($levels as $level) 
  198. ?> 
  199. <option value="<?php echo $level->id?>" <?php if($l == $level->id) { ?>selected="selected"<?php } ?>><?php echo $level->name?></option> 
  200. <?php 
  201. ?> 
  202. </select> 
  203.  
  204. <input type="hidden" name="page" value="pmpro-reports" />  
  205. <input type="hidden" name="report" value="memberships" />  
  206. <input type="submit" class="button" value="<?php _e('Generate Report', 'paid-memberships-pro' );?>" /> 
  207. </li> 
  208. </ul> 
  209.  
  210. <div id="chart_div" style="clear: both; width: 100%; height: 500px;"></div>  
  211.  
  212. <script> 
  213. //update month/year when period dropdown is changed 
  214. jQuery(document).ready(function() { 
  215. jQuery('#period').change(function() { 
  216. pmpro_ShowMonthOrYear(); 
  217. }); 
  218. }); 
  219.  
  220. function pmpro_ShowMonthOrYear() 
  221. var period = jQuery('#period').val(); 
  222. if(period == 'daily') 
  223. jQuery('#for').show(); 
  224. jQuery('#month').show(); 
  225. jQuery('#year').show(); 
  226. else if(period == 'monthly') 
  227. jQuery('#for').show(); 
  228. jQuery('#month').hide(); 
  229. jQuery('#year').show(); 
  230. else 
  231. jQuery('#for').hide(); 
  232. jQuery('#month').hide(); 
  233. jQuery('#year').hide(); 
  234.  
  235. pmpro_ShowMonthOrYear(); 
  236.  
  237. //draw the chart 
  238. google.load("visualization", "1", {packages:["corechart"]}); 
  239. google.setOnLoadCallback(drawChart); 
  240. function drawChart() {  
  241.  
  242. var data = google.visualization.arrayToDataTable([ 
  243. <?php if ( $type === "signup_v_all" ) : // Signups vs. all cancellations ?> 
  244. ['<?php echo $date_function;?>', 'Signups', 'All Cancellations'],  
  245. <?php foreach($dates as $key => $value) { ?> 
  246. ['<?php if($period == "monthly") echo date_i18n("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; ?>],  
  247. <?php } ?> 
  248. <?php endif; ?> 
  249.  
  250. <?php if ( $type === "signup_v_cancel" ) : // Signups vs. cancellations ?> 
  251. ['<?php echo $date_function;?>', 'Signups', 'Cancellations'],  
  252. <?php foreach($dates as $key => $value) { ?> 
  253. ['<?php if($period == "monthly") echo date_i18n("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; ?>],  
  254. <?php } ?> 
  255. <?php endif; ?> 
  256.  
  257. <?php if ( $type === "signup_v_expiration" ) : // Signups vs. expirations ?> 
  258. ['<?php echo $date_function;?>', 'Signups', 'Expirations'],  
  259. <?php foreach($dates as $key => $value) { ?> 
  260. ['<?php if($period == "monthly") echo date_i18n("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; ?>],  
  261. <?php } ?> 
  262. <?php endif; ?> 
  263.  
  264. <?php if ( $type === "mrr_ltv" ) : ?> 
  265. ['<?php echo $date_function;?>', 'MRR', 'LTV'],  
  266. <?php foreach($dates as $key => $value) { ?> 
  267. ['<?php if($period == "monthly") echo date_i18n("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 ); ?>],  
  268. <?php } ?> 
  269. <?php endif; ?> 
  270. ]); 
  271.  
  272. var options = {  
  273. colors: ['#0099c6', '#dc3912'],  
  274. hAxis: {title: '<?php echo $date_function;?>', titleTextStyle: {color: 'black'}, maxAlternation: 1},  
  275. vAxis: {color: 'green', titleTextStyle: {color: '#51a351'}},  
  276. }; 
  277.  
  278. <?php if ( $type === "signup_v_cancel" || $type === "signup_v_expiration" || $type === "signup_v_all" ) : // Signups vs. cancellations ?> 
  279. var chart = new google.visualization.ColumnChart(document.getElementById('chart_div')); 
  280.  
  281. <?php elseif ( $type === "mrr_ltv" ) : // MRR & LTV ?> 
  282.  
  283. <?php 
  284. //prefix or suffix? 
  285. if(pmpro_getCurrencyPosition() == "right") 
  286. $position = "suffix"; 
  287. else 
  288. $position = "prefix"; 
  289. ?> 
  290.  
  291. var formatter = new google.visualization.NumberFormat({<?php echo $position;?>: '<?php echo html_entity_decode($pmpro_currency_symbol);?>'}); 
  292. formatter.format(data, 2); 
  293. var formatter = new google.visualization.NumberFormat({<?php echo $position;?>: '<?php echo html_entity_decode($pmpro_currency_symbol);?>'}); 
  294. formatter.format(data, 1); 
  295.  
  296. var chart = new google.visualization.LineChart(document.getElementById('chart_div')); 
  297. <?php endif; ?> 
  298. chart.draw(data, options); 
  299. </script> 
  300.  
  301. </form> 
  302. <?php