<?php
    require 'scr/header.php';
    include 'scr/lister.php';
    echoHead("User statistics",0,0);

    $ul['sql'] = "SELECT u.user_name, (
            SELECT COUNT( fu.name )
            FROM `faq_use` AS fu, `irc_nicks` AS ircn
            WHERE LOWER(fu.name) LIKE LOWER(CONCAT('%', ircn.nick_name, '%'))
            AND ircn.user_name = u.user_name
        ), (
            SELECT COUNT( f.action )
            FROM  `faq_edit` AS f
            WHERE f.name = u.user_name
            AND f.action =  'INSERT'
        ), (
            SELECT COUNT( f.action )
            FROM  `faq_edit` AS f
            WHERE f.name = u.user_name
            AND f.action =  'U_FAQ'
        ), (
            SELECT COUNT( f.action )
            FROM  `faq_edit` AS f
            WHERE f.name = u.user_name
            AND f.action =  'DELETE'
        )
        FROM  `users` AS u";
    $ul['translate'] = array(
        'user_name'=>'User',
        '(
            SELECT COUNT( fu.name )
            FROM `faq_use` AS fu, `irc_nicks` AS ircn
            WHERE LOWER(fu.name) LIKE LOWER(CONCAT(\'%\', ircn.nick_name, \'%\'))
            AND ircn.user_name = u.user_name
        )'=>'Used',
        '(
            SELECT COUNT( f.action )
            FROM  `faq_edit` AS f
            WHERE f.name = u.user_name
            AND f.action =  \'INSERT\'
        )'=>'Added',
        '(
            SELECT COUNT( f.action )
            FROM  `faq_edit` AS f
            WHERE f.name = u.user_name
            AND f.action =  \'U_FAQ\'
        )'=>'Edited',
        '(
            SELECT COUNT( f.action )
            FROM  `faq_edit` AS f
            WHERE f.name = u.user_name
            AND f.action =  \'DELETE\'
        )'=>'Deleted',
    );
    $ul['styles'] = array(
        'Used'=>'c tiny',
        'Added'=>'c tiny',
        'Edited'=>'c tiny',
        'Deleted'=>'c tiny',
    );

    $fl['sql'] = "SELECT cmd, (
            SELECT COUNT(fe.id)
            FROM `faq_edit` AS fe
            WHERE f.id = fe.faq_id OR f.cmd = fe.command
        ), (
            SELECT COUNT(fu.id)
            FROM `faq_use` AS fu
            WHERE f.cmd = fu.command
        )
        FROM `faq` AS f";
    $fl['translate'] = array(
        'cmd'=>'Command',
        '(
            SELECT COUNT(fe.id)
            FROM `faq_edit` AS fe
            WHERE f.id = fe.faq_id OR f.cmd = fe.command
        )'=>'Edited',
        '(
            SELECT COUNT(fu.id)
            FROM `faq_use` AS fu
            WHERE f.cmd = fu.command
        )'=>'Used',
    );
    $fl['styles'] = array(
        'Edited'=>'c tiny',
        'Used'=>'c tiny',
    );

    echo("<div class='tablecon'>");
    Echo("<h1>User Statistics</h1>");
    makeList($ul['sql'],0,$ul['translate'],$ul['styles']);
    Echo("<br /><h1>FAQ Statistics</h1>");
    makeList($fl['sql'],1,$fl['translate'],$fl['styles']);
    echo("</div>");
    include 'scr/footer.php';
?>