Indice (TOC)
Mindtouch deki has all you need to create and plot complex statistical analysis.
You have to install mysql extension , connect it to deki mysql database and use dekiscript to catch data from mysql and plot using google graph chart api.
SECURITY ISSUE: Create a specific user for mysql and allow it to READ-ONLY SOME TABLES (exclude users, extensions, ...) otherwise you allow any registered user to query some sensible information!
Compare number of active users with number of edited pages
var tot = [0];
var row = 20;
var q1 = "";
foreach (var i in tot) {
let q1 = "SELECT count(title) AS revisions, count(DISTINCT title) as editedpages, count(DISTINCT user) as activeusers, CAST( DATE_FORMAT(tstamp,'%d/%m') AS char(8)) as pdata FROM ((SELECT old_title as title, old_user as user, old_timestamp as tstamp FROM old) UNION (SELECT page_title AS title, page_user_id AS user,page_timestamp AS tstamp FROM pages) ) as tab GROUP BY DATE_FORMAT(tstamp,'%y-%m-%d') order by DATE_FORMAT(tstamp,'%y-%m-%d') DESC limit ".. (i*row)..", "..row..";";
var activeusers = mysql.List(q1,"activeusers");
var editedpages = mysql.List(q1,"editedpages");
var revisions = mysql.List(q1,"revisions");
var pd = mysql.List(q1,"pdata");
var t1 = list.reverse(list.sort(revisions));
var t2 = list.reverse(list.sort(activeusers));
var t = num.max(t1[0],t2[0]);
var max = [0, (t/4),(t/2),((t/4)*3), t];
google.linechart(800,250,[editedpages, activeusers, revisions],["edited pages","active users", "revisions"],["0000ff","ff0000","00ffff"],pd,max);
}
/* mysql.table(q1); */
Number of pages
This script is a draft, I know that it is not optimized, probably we can create only one mysql query instead the foreach loop.
Last 15 days
Last 15 weeks
Last 15 months
var factor = [1,7,30]; /* days interval: 1 for days, 7 for week and 30 for month */ var title = ["Last 15 days", "Last 15 weeks", "Last 15 months"];
var interval = [0, -1, -2, -3, -4, -5, -6, -7, -8, -9, -10, -11, -12, -13, -14, -15];
foreach (var group in factor) {
var arrdate = [ ];
var arrpage = [ ];
var dd = nil;
var rr = [ ];
var k = nil;
var t = nil;
web.html("<h3>" .. title[__count] .. "</h3>");
foreach (var x in interval) {
let t = (x * group);
let dd = Date.Format(Date.AddDays(date.now, t), "yyyyMMdd");
let rr = Mysql.List("SELECT count(DISTINCT title) FROM ((SELECT old_title AS title, old_timestamp AS tstamp FROM old) UNION (SELECT page_title AS title, page_timestamp AS tstamp FROM pages )) AS tab WHERE tstamp < '" .. dd .. "';");
let k = rr[0];
if ( k == 0 ) {
let rr = [ 1 ];
}
let dd = Date.Format(Date.AddDays(date.now, t), "dd/MM");
let arrdate = arrdate .. [dd];
let arrpage = arrpage .. rr;
}
var t = list.reverse(list.sort(arrpage));
var max = t[0];
let t = [0, ( max / 2 ) , max ];
google.linechart(700,150,arrpage,["numero pagine"],["0000ff"], arrdate,t);
}
-
Commenti