1#!/usr/bin/perl
2# vim:ts=4:sw=4:et
3# Virtual quota_usage 0.3
4# Contributed to Postfixadmin by Jose Nilton <jniltinho@gmail.com>
5#
6# See also : http://www.russelldare.net/media/perl/dirsizeSource.pdf
7# License: GPL v2.
8
9# Usage:
10# perl quota_usage.pl --list
11# perl quota_usage.pl --list --addmysql
12#                                      for add mysql database postfix
13#
14# Requirements - the following perl modules are required:
15# DBD::Pg or DBD::mysql; perl perl-DBD-mysql perl-DBD (may be named differently depending on your platform).
16#           and the 'du' binary in $ENV{'PATH'} (see below).
17#
18# You will need to modify the postfix DATABASE to add a quota_usage column.
19# Mysql:
20# 	ALTER TABLE mailbox ADD quota_usage INT(11) NOT NULL DEFAULT '0' AFTER modified,
21# 	ADD quota_usage_date DATE NOT NULL DEFAULT '0000-00-00' AFTER quota_usage;
22# PostgreSQL:
23# 	ALTER TABLE mailbox ADD COLUMN quota_usage INTEGER NOT NULL DEFAULT 0;
24# 	ALTER TABLE mailbox ADD COLUMN quota_usage_date DATE NOT NULL DEFAULT current_date;
25#
26
27use strict;
28use warnings;
29use File::Path;
30use DBI;
31use Getopt::Long;
32
33##EDIT##
34my $db_host 	= 'localhost';
35my $db_database = 'postfix';
36my $db_user 	= 'postfix';
37my $db_password = '123456';
38my $root_path 	= '/home/vmail';
39# Pg or mysql
40my $db_type     = 'mysql';
41##END EDIT##
42
43(help()) if (!$ARGV[0]);
44
45$ENV{'PATH'} = "/sbin:/bin:/usr/sbin:/usr/bin";
46my($domain_dir, $full_domain_dir, $user_dir, $usage, $email, $sql, $dbh);
47
48my $list = 0;
49my $insert_db = 0;
50my $total_mailbox = 0;
51my $total_domain = 0;
52GetOptions ('l|list' => \$list, 'i|addmysql' => \$insert_db, 'help|h|man' => \&help) or (help());
53
54
55(list_quota_usage()) if ($list == 1 || $insert_db == 1 );
56
57
58
59sub list_quota_usage {
60    opendir(DOMAINDIR, $root_path) or die ("Unable to access directory '$root_path' ($!)");
61
62    if($insert_db == 1){
63        $dbh = DBI->connect("DBI:$db_type:database=$db_database;host=$db_host", $db_user, $db_password) or die ("cannot connect the database");
64        execSql("UPDATE mailbox set quota_usage = 0");
65    }
66
67    foreach $domain_dir (sort readdir DOMAINDIR) {
68        next if $domain_dir =~ /^\./;                    # skip dotted dirs
69        $full_domain_dir = "$root_path/$domain_dir"; #print "$full_domain_dir\n";
70        $total_domain++;
71
72        opendir(USERDIR, $full_domain_dir) or die ("Unable to access directory '$full_domain_dir' ($!)");
73        foreach $user_dir (sort readdir USERDIR) {
74            next if $user_dir =~ /^\./; # skip dotted dirs
75            $email = "$user_dir\@$domain_dir";
76            $total_mailbox++;
77
78            my $i = `du -0 --summarize $full_domain_dir/$user_dir`;
79            ($usage) = split(" ", $i);
80
81            if ($usage < 100) {
82                $usage = 0;
83            } elsif ($usage < 1000) {
84                $usage = 1;
85            } else {
86                $usage = $usage + 500;
87                $usage = int $usage / 1000;
88            }
89
90            if($insert_db == 1)
91            {
92                execSql("INSERT INTO quota2 (username, bytes) values ('$email', $usage) ON DUPLICATE KEY UPDATE bytes = VALUES(bytes)");
93            }
94            print_list() if ($list == 1);
95
96        }
97    }
98    close(DOMAINDIR);
99    close(USERDIR);
100
101    (print_total()) if ($list == 1);
102
103}
104
105
106
107
108
109sub execSql {
110    my $sql = shift;
111    my $ex;
112    $ex = $dbh->do($sql) or die ("error when running $sql");
113}
114
115
116
117sub print_total{
118    print "---------------------------------------------------------\n";
119    print "TOTAL DOMAIN\t\t\t\tTOTAL MAILBOX\n";
120    print "---------------------------------------------------------\n";
121    print "$total_domain\t\t\t\t\t\t$total_mailbox\n";
122}
123
124
125
126sub print_list {
127format STDOUT_TOP =
128Report of Quota Used
129---------------------------------------------------------
130EMAIL                                         QUOTA USED
131---------------------------------------------------------
132.
133
134
135format =
136@<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<         @<<<<<<<<<<
137$email,                                           "$usage MB"
138.
139
140    write;
141}
142
143
144
145
146
147sub help {
148    print "$0 [options...]\n";
149    print "-l|--list                     List quota used\n";
150    print "-i|--addmysql                 For insert quota used in database mysql\n";
151}
152