So there really isn't a tutorial/documentation out there for this. For example over at http://www.amember.com/docs/API/Concepts , it saids "Am_Report API provides easy way to define new reports and modify existing without worrying about results rendering" But once I clicked on it, there was nothing. By examining the files, I do see that there is an abstract class "Am_Report_Abstract" that I can probably use to generate reports. The only problem is that I don't know what the functions are used for, how to run the query, and display the results. Any help at all would be greatly appreciated!
Unfortunately there is no documentation for report API yet. In order to check how you can use Am_Report API, check existing reports in /amember/library/Am/Report/Standart.php
Hey Alexander, thanks for the reply. I saw the class definitions for the reports, but I haven't seen an example of actually instantiating and using them, which will probably help a ton. Also, I have no idea what some of the functions are for (i.e. getLines()) since there is a lack of comments.
You don't need to instantiate it manually. Just create child from Am_Report_Abstract and it already will be include in reports available in amember CP -> Reports getLines function should return array of Am_Report_Line objects. Each object represents actual "Line" on report graph. Take Am_Report_SignupsCount as an example. If you explain what do you need to create I can help with real example.
Would I save the class in Standard.php? I copy and paste the new signup report (and rename it) and added the class to Standard.php, but I didn't see a new report generated on the drop down box. I feel like I need to call it somewhere... So say I want to create a custom report for the amount of time a user subscribes to our service (a particular product) before canceling. I don't need the exact code, but roughly how would I go about it... y axis = number of users x axis = amount of time (say it's monthly with the last marker 1 year or more) So base on my research there are three key functions I would need to override: getPointField, getQuery, and getLines. What would I return from each function... getPointField - return "month"? getQuery - There will be 12 different queries, one for each month, (i.e. SELECT COUNT(*) FROM am_users WHERE TIMEDIFF(join_date,end_date) >= '30:00:00',) how do I fit them all into Am_Query object? getLines - what would I return? Some side questions 1) Is there a field somewhere when it states a user's status with a product (active/expired)? 2) Wouldn't my code have to take into account "Period" and "Quantity", selected by the user from the frontend, as well?
First you can place your report class to /amember/application/configs/site.php If it is a subclass from Am_Report_Abstract, it will be added to reports automatically. But make sure that you have changed Report title in constructor. Your query inside getQuery should return two columns. One for "point" - coordinate on x axis. and the other for coordinate on y axis. getPoint function should return name of the "point" column in query, for example if you have query: "select date, amount from ...." point in that query is "date" getLines function describe each line in resulting report, Here is am example of usage: function getLines() { return array( ////// Arguments: amount - is name of y axis column from getQuery; ///// third argument is line color. new Am_Report_Line("amount", "Line Title", "#ff00cc"), ); } Check am_access_cache table. It have info which you require. To handle quantity selected in report settings your query should group results by expression which is returned by $this->quantity->getSqlExpr(POINTFIELDNAME) call. Also you have access to period as well: $this->start - period start, $this->stop -> period stop.
Hey Alexander, Sorry for the late response. Just want to say you are hands down one of the few people who knows amember really well and you are absolutely awesome/amazing for helping me out! You have no idea how hard it is for me to figure this out! I literally put this in the file path you suggested on my server. I don't see a new item on the drop down under admin reports. class Am_Report_SignupsCount1 extends Am_Report_Abstract { public function __construct() { $this->title = ___("Count of user signups 1"); $this->description = "including pending records 1"; } protected function runQuery() { $expra = $this->quantity->getSqlExpr('u.added'); $this->stmt = $this->getDi()->db->queryResultOnly(" SELECT $expra as point, COUNT(user_id) as cnt FROM ?_user u WHERE added BETWEEN ? AND ? GROUP BY $expra ", $this->start, $this->stop); } function getLines() { $ret = array(); $ret[] = new Am_Report_Line('cnt', ___("Count of signups")); return $ret; } } Omg thank you so much for clarifying this!!! Alright, let me get this straight, for these three functions, getQuery() is the place where I query the info (ex. "SELECT x, y FROM ...") and return the actual data getPoint() and getLines() are basically describes the x/y axis getPoint() returns a string value describing x axis getLines() returns the Am_Report_Line object describing y axis. Is this right? Got it. So every report will have period/quantity on the front end which we can use (but don't have to?) in our code with those variables? Also, I'm not exactly sure what is POINTFIELDNAME? Again. Alexander you are so awesome!!!
My mistake. Add this line before your report class: PHP: include_once("library/Am/Report.php"); Yes right. Yes correct. POINTFIELDNAME is the name of your point field. PHP: protected function runQuery(){$expra = $this->quantity->getSqlExpr('u.added');$this->stmt = $this->getDi()->db->queryResultOnly("SELECT$expra as point,COUNT(user_id) as cntFROM ?_user uWHERE added BETWEEN ? AND ?GROUP BY $expra", $this->start, $this->stop);} POINTFIELDNAME is "point" in above example
I added this line to my site.php file and still no go. I still don't see the custom report in my admin-reports. Any ideas? I tried both an absolute and relative path. Is there anyway to check if the path file is correct? I In /amember/library/Am/Report/Standard.php, I even tried changing the names of titles/removing classes in the file, but the change is not reflected in the admin reports panel. And another quick question, we can refer to any amember tables as ?_tablename
It seems that my server has multiple amember folders and the one I'm modifying isn't the one admin reports is reading from, if that makes sense. Is there a way to know which is the "correct" file path to the library folder?
Please disregard my other two posts, I found the correct amember folder to change. 1) What is the difference between runQuery() and getQuery(), they both seem to return a query object. 2) Is it possible to add fields / dropdowns on the frontend other than period/quantity? 3) Is there a way to echo out the SQL statement so if I am writing the query correctly? 4) How would I graph different lines (more than one x/y data)? Thanks!
1. getQuery create query object, and runQuery execute it and return PDO statement. 2. Unfortunately no. 3. Add this to your file before query is executed: Am_Db::setLogger(); this will enable database log. All queries after that line will be printed. 4. You should define only more then one y data because x data will be the same for all lines. function getLines() { $ret = array(); $ret[] = new Am_Report_Line('cnt', ___("Count of signups")); $ret[] = new Am_Report_Line('cnt2', ___("Count of signups 2")); return $ret; } }
Can't I override a function in the Am_Report_Abstract class like createForm()? Awesome. Where will it be printed? Thanks again!