Статистика сервера печати CUPS

[maxitso]

server1=cups
server2=mysql, ahache, php

server2
Создать базу, пользователя с правами на эту базу

CREATE DATABASE `cups_stat`;
    USE `cups_stat`;

    CREATE TABLE IF NOT EXISTS `tasks_log` (
    `id` bigint(10) NOT NULL auto_increment,
    `prn_name` varchar(50) NOT NULL,
    `user_name` varchar(50) NOT NULL,
    `task_id` int(10) NOT NULL,
    `p_date` datetime NOT NULL,
    `page_num` int(3) NOT NULL,
    `rep_num` int(3) NOT NULL,
    `host` varchar(50) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

    CREATE TABLE `prn` (
    `id` int(11) NOT NULL auto_increment,
    `name` varchar(30) NOT NULL,
    `model` varchar(30) NOT NULL,
    `comment` varchar(128) NOT NULL,
    `status` enum('0','1') default '1',
    `balance` enum('0','1') NOT NULL default '1',
    `b_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
    `p_rur` decimal(10,2) NOT NULL,
    `p_usd` decimal(5,2) NOT NULL,
    `c_type` varchar(30) character set utf8 collate utf8_unicode_ci NOT NULL,
    `c_type1` varchar(30) character set utf8 collate utf8_unicode_ci NOT NULL,
    `c_type2` varchar(30) character set utf8 collate utf8_unicode_ci NOT NULL,
    `c_type3` varchar(30) character set utf8 collate utf8_unicode_ci NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=34 DEFAULT CHARSET=utf8;

    CREATE TABLE `users` (
    `id` tinyint(3) NOT NULL auto_increment,
    `user` varchar(50) NOT NULL,
    `name` varchar(128) default NULL,
    `div` varchar(128) character set utf8 collate utf8_unicode_ci NOT NULL,
    `status` enum('0','1') default '1',
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=34 DEFAULT CHARSET=utf8;

    GRANT ALL PRIVILEGES ON cups_stat.* TO cups@'%' IDENTIFIED BY 'cups' WITH GRANT OPTION;

Web интерфейс скопировать в /var/www/cups/
отредактировать config.php
Необходимо указать:
- хост, на котором крутится MySQL,
- имя пользователя
- пароль на доступ к просмотру статистики,
- имя пользователя MySQL
- пароль для доступа к MySQL.

Для отображения круговых диаграмм нужен пакет gd

aptitude install php5-gd



server1
скрипт cups-export.py для экспорта данных из page_log в mysql
добавить в cron

30 * * * * /root/cups-export.py

    #!/usr/bin/env python

    import MySQLdb, string

    m = { 'Jan' : '01',
    'Feb': '02',
    'Mar': '03',
    'Apr': '04',
    'May': '05',
    'Jun': '06',
    'Jul': '07',
    'Aug': '08',
    'Sep': '09',
    'Oct': '10',
    'Nov': '11',
    'Dec': '12'
    }
    # unpack line of log-file to some vars
    def unpack_line(line):
    line = string.replace(line, "'", "")
    els = string.split(line, " ")
    fprn = els[0]
    fuser = els[1]
    fid = int(els[2])
    fdate = els[3]
    fpage = int(els[5])
    frep = int(els[6])
    fhost = els[8]
    return fprn, fuser, fid, fdate, fpage, frep, fhost
    # prepare date for mysql
    def unpack_date(fdate):
    day = fdate[1:3]
    month = fdate[4:7]
    year = fdate[8:12]
    hms = fdate[13:21]
    udt = year+'-'+m[month]+'-'+day+' '+hms
    return udt

    # Connect to DB
    db = MySQLdb.connect(host="10.0.0.125",
    user="cups",
    passwd="cups",
    db="cups_stat2")
    cur = db.cursor()

    # get max_date, id
    cur.execute("SELECT UNIX_TIMESTAMP(MAX(p_date)), MAX(task_id) FROM tasks_log")
    max = cur.fetchone()
    max_date = max[0]
    max_id = max[1]

    # open file
    f = open('/var/log/cups/page_log', 'r')
    lines = f.readlines()
    # if id in log file > id in db, then add to db new entry
    for line in lines:
    fprn, fuser, fid, fdate, fpage, frep, fhost = unpack_line(line)
    if fid > max_id :
    query = 'insert into tasks_log (prn_name, user_name, task_id, p_date, page_num, rep_num, host) values(\''+fprn+'\',\''+fuser+'\',\''+str(fid)+'\',\''+str(unpack_date(fdate))+'\',\''+str(fpage)+'\',\''+str(frep)+'\',\''+str(fhost)+'\')'
    # cur = db.cursor()
    cur.execute(query)
    db.commit()

    f.close()
    db.close()