Plugin development date selection


#1

I am trying to create a new plugin that gathers data from an external database. I managed to get the basic thing working and I see data in my graph, but I have three problems:

Problem #1:
For some reason I cannot get the selected data (selected with the standard ‘date selector’ that appears on each page) into the SQL query. For me it is completely unclear how to obtain and process this field into a startdate/enddate data string that I can pass on to the SQL query.

Problem #2:
The graph that is produced has a very cluttered x-axis (many labels that overwrite each other), how can that be cleaned up?

Problem #3:
How to add this plugin to the list of items that can be emailed in a report?

Daan


(Matthieu Aubry) #2
  1. in the Controller.php can you access $this->date ? Then you can simply do $this->date->toString(‘Y-m-d H:i:s’); and $this->date->subDay(1)->toStrong(…) for example

  2. Please post screenshot? Can you replicate this issue in other “core” piwik graphs?

  3. PDF/Email reports feed of the “metadata” reports. http://piwik.org/docs/analytics-api/metadata/
    Your plugin can hook to event ‘API.getReportMetadata’ and then return the metadata information which will be used to build the report in PDF/email.
    See example in: http://dev.piwik.org/trac/browser/trunk/plugins/UserCountry/UserCountry.php#L77


#3

Regarding #1: I am still confused:

You say I can access $this->date in the controller, but I need this info in the API function. Next to this I need access to the starttime and endtime as a string and after two days of fiddling around with these functions I can still not get a method that works for all use cases (day, week, month, year, date range).

The code below also leads to the ‘cluttered x-axis’ because the x-labels are to big and overwrite each other. See attached screenshot. This only is a problem with MY plugin, so I am definitely doing something horribly wrong.

For completeness my buggy controller and API code:


class Piwik_FeedStats_Controller extends Piwik_Controller
{

	function dataInserts()
	{
		echo "<h2>Message inserts per feed</h2>";
        	$this->echoEvolutionGraph();
	}

	function echoEvolutionGraph()
	{
		$view = Piwik_ViewDataTable::factory('graphEvolution');
        	$this->setPeriodVariablesView($view);
        	$view->init( $this->pluginName,  __FUNCTION__, 'FeedStats.getDataInserts' );
		$view->setColumnTranslation('feed1', "Feed #1" );
		$view->setColumnTranslation('feed2', "Feed #2" );
		$this->renderView($view);
	}
}


class Piwik_FeedStats_API
{
	static private $instance = null;
	static public function getInstance()
	{
		if (self::$instance == null)
		{
			self::$instance = new self;
		}
		return self::$instance;
	}
	
	public function getDataInserts( $idSite, $period, $date, $segment = false)
	{
        	$dataTable = new Piwik_DataTable();
	
		// Copied from ExampleUI
	        $period = new Piwik_Period_Range($period, 'last30');
	        $dateStart = $period->getDateStart()->toString('Y-m-d'); // eg. "2009-04-01"
		$dateEnd = $period->getDateEnd()->toString('Y-m-d'); // eg. "2009-04-30"

	        $link = mysql_connect('www.example.com', 'db_user', 'db_password')
        	    or die('Could not connect: ' . mysql_error());
        	mysql_select_db('db_database') or die('Could not select database');

        	// Performing SQL query
        	$query = "SELECT * FROM tbl_feed_stats WHERE `time` >= '" . $dateStart . "' AND `time` < '" . $dateEnd . "'";
        	$result = mysql_query($query) or die('Query failed: ' . mysql_error());

	        // Getting results in DataTable format
	        while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
	        {
	            $values[$row['time']] = array("feed1" => $row['feed1'], "feed2" => $row['feed2']);
	        }
        	$dataTable->addRowsFromArrayWithIndexLabel($values);

	        // Free resultset
	        mysql_free_result($result);

	        // Closing connection
	        mysql_close($link);
	        return $dataTable;
	}

}



#4

Nobody an idea of what I am doing wrong here? I am still completely stuck.