I am trying to add a custom report that will show how users found us. We have been collecting the data and want to be able to display it. I have been looking over things in Standard.php and have tried to duplicate one of the functions I found in there but the closest I have gotten was just showing the over all amount of people that have filled out this field. but when i run the query in the database i get this result: valuecnt Bing1 Blank31 CECBEMS3 Facebook3 Friend12 Google66 NREMT5 Other18 Yahoo3 this is the query: SELECT value as value, COUNT(value) as cnt FROM am_data WHERE `key`= 'howdidyoufind' GROUP BY value it is pretty simple but of course amember doesn't like to make things simple. Does anyone know how to display this properly in the reports? Thanks
class Am_Report_FoundCount extends Am_Report_Date { public function __construct() { $this->title = ___('How they found us'); } public function _initConfigForm(Am_Form $form) { parent::_initConfigForm($form); } protected function runQuery() { //$expra = $this->quantity->getSqlExpr('value'); /* SQL query***** SELECT value as value, COUNT(value) as cnt FROM am_data WHERE `key`= 'howdidyoufind' GROUP BY value */ $this->stmt = $this->getDi()->db->queryResultOnly(" SELECT value, COUNT(value) as cnt FROM am_data WHERE `key`= 'howdidyoufind' GROUP BY value ", $this->start, $this->stop ); } function getLines() { $ret = array(); $ret[] = new Am_Report_Line('cnt', ___('Found Count')); return $ret; } }
I just want it to display two columns onve as value and one as cnt that is associated with it and each value on a new line
Here is exact code for your use case (require latest version of aMember): PHP: class Am_Report_FoundCount extends Am_Report_Abstract{ public function __construct() { $this->title = ___('How they found us'); } protected function runQuery() { $point_fld = self::POINT_FLD; $this->stmt = $this->getDi()->db->queryResultOnly(" SELECT value AS $point_fld, COUNT(value) as cnt FROM am_data WHERE `key`= 'howdidyoufind' GROUP BY $point_fld"); } function getLines() { $ret = array(); $ret[] = new Am_Report_Line('cnt', ___('Found Count')); return $ret; } protected function getOptions() { $f = $this->getDi()->userTable->customFields()->get('howdidyoufind'); return $f->options; } protected function processConfigForm(array $values) { $this->setQuantity(new Am_Report_Quant_Enum($this->getOptions())); } public function getOutput(Am_Report_Result $result) { return array( new Am_Report_Graph_Bar($result), new Am_Report_Table($result) ); }}
Yes I have added this and it is working. Although the first two bar graphs dont have labels. And the field that should say "Blank" is actually blank which is strange. In the DB it has the word "Blank". Do you know if there is a way to be able to select a date range for this output? It would be based on when the user signed up. So the date for each value would be where 'id' field for that value in am_data matches the 'user_id' in am_user and the date to go by is the 'added' field for that user. does this make sense? I can try and make an SQL query from our tables so you can see what i mean form that way if you need. Thank you so very much for your help!
Here you are PHP: class Am_Report_FoundCount extends Am_Report_Abstract{ const FIELD_NAME = 'howdidyoufind'; public function __construct() { $this->title = ___('How they found us'); } public function _initConfigForm(Am_Form $form) { $g = $form->addGroup(); $g->setSeparator(' '); $g->addDate('start'); $g->addStatic()->setContent(' – '); $g->addDate('stop'); $g->setLabel("Limit Users by Signup Date\n" . "leave empty to iinclude all users to report"); $form->addRule('callback', 'Start Date cannot be later than the End Date', array($this, 'checkStopDate')); } public function checkStopDate($val){ @$res = isset($val['stop']) && $val['stop']<$val['start']; if ($res) { $elements = $this->getForm()->getElementsByName('start'); $elements[0]->setError('Start Date cannot be later than the End Date'); } return !$res; } protected function runQuery() { $point_fld = self::POINT_FLD; $this->stmt = $this->getDi()->db->queryResultOnly(" SELECT value AS $point_fld, COUNT(value) as cnt FROM ?_data d LEFT JOIN ?_user u ON d.`table` = 'user' AND d.`id` = u.user_id WHERE `key` = ? AND `value`<>'' AND u.added BETWEEN ? AND ? GROUP BY $point_fld", self::FIELD_NAME , $this->start, $this->stop); } protected function getOptions() { $f = $this->getDi()->userTable->customFields()->get(self::FIELD_NAME); return $f->options; } protected function processConfigForm(array $values) { $this->setInterval(@$values['start'], @$values['stop']); $this->setQuantity(new Am_Report_Quant_Enum($this->getOptions())); } public function setInterval($start, $stop) { $this->start = $start ? date('Y-m-d 00:00:00', strtotime($start)) : '1970-01-01 00:00:00'; $this->stop = $stop ? date('Y-m-d 23:59:59', strtotime($stop)) : '2037-12-31 23:59:59'; return $this; } function getLines() { $ret = array(); $ret[] = new Am_Report_Line('cnt', ___('Found Count')); return $ret; } public function getOutput(Am_Report_Result $result) { return array( new Am_Report_Graph_Bar($result), new Am_Report_Table($result) ); }}
You must be a wizard! this is great thanks! Do you know why the first two bars on the graph dont have lables?
Did you change options for this field meanwhile? I guess you can have some values in database that you have not in options of this field configuration.
Well the first one is "bing" and shows up when i hover over the bar, just not at the bottom where all the others are labeled