Custom Admin Report

Discussion in 'Troubleshooting' started by robbyschanilec, Feb 26, 2016.

  1. robbyschanilec

    robbyschanilec New Member

    Joined:
    Feb 9, 2016
    Messages:
    22
    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
  2. caesar

    caesar aMember Pro Developer Staff Member

    Joined:
    Oct 16, 2009
    Messages:
    1,642
    Do you mind to share code of report that you implemented?
  3. robbyschanilec

    robbyschanilec New Member

    Joined:
    Feb 9, 2016
    Messages:
    22
    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;
    }


    }
  4. robbyschanilec

    robbyschanilec New Member

    Joined:
    Feb 9, 2016
    Messages:
    22
    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
  5. caesar

    caesar aMember Pro Developer Staff Member

    Joined:
    Oct 16, 2009
    Messages:
    1,642
    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)
            );
        }
    }
  6. robbyschanilec

    robbyschanilec New Member

    Joined:
    Feb 9, 2016
    Messages:
    22
    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!
  7. caesar

    caesar aMember Pro Developer Staff Member

    Joined:
    Oct 16, 2009
    Messages:
    1,642
    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)
            );
        }
    }
  8. robbyschanilec

    robbyschanilec New Member

    Joined:
    Feb 9, 2016
    Messages:
    22
    You must be a wizard! this is great thanks! Do you know why the first two bars on the graph dont have lables?
  9. caesar

    caesar aMember Pro Developer Staff Member

    Joined:
    Oct 16, 2009
    Messages:
    1,642
    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.
  10. robbyschanilec

    robbyschanilec New Member

    Joined:
    Feb 9, 2016
    Messages:
    22
    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

Share This Page