#!/usr/bin/perl
# p4audit.pl
#   - a perl script to help in the auditing of users, client specs
#        and changelists.
#
# akalaveshi@mahinetworks.com



# Check for some required modules by loading their code
# at compile time
BEGIN {
   for (qw/ Getopt::Std Date::Manip Spreadsheet::WriteExcel /) {
      eval qq{ use $_ (); };
      if ($@) {
	 print "Module '$_' failed to load:\n\n$@\n";
         print "It may not be installed -- visit <http://www.cpan.org>.\n";
	 exit;
      }
   }
}


use Date::Manip;
$ENV{TZ}="PST";
use Getopt::Std;

use vars qw'$opt_d $opt_h $opt_o';
getopts('hd:o:') 
    || die "\n", 
           "usage: $0 [-h] [-d <date to compare>] [-o <filename>]\n";

if ($opt_h) {
   print "NAME\n\tp4audit.pl - perforce user and client information\n";
   print "SYNOPSIS\n\tp4audit.pl [-h] [-d <date to compare>] [-o <filename>]\n";
   print "DESCRIPTION\n\tA perl script to help in the auditing of P4 users and client specs.\n";
   print "OUTPUT\n\txls binary\n";
   print "AUTHOR\n\takalaveshi\@mahinetworks.com\n";
   print "OPTIONS\n";
   print "\t-d\n\t\tDate to display all users who's access time is earlier than.\n";
   print "\t\tThrough the magic of the Date::Manip module, you can also use\n";
   print "\t\tverbiage like \"6 months ago\".  This option defaults to\n";
   print "\t\t\"tomorrow\" which will display all users/ clients.\n";
   print "\t-h\n\t\tDisplay this page.\n";
   print "\t-o\n\t\tOutput filename (default: p4audit).\n";
   exit;
}

$opt_o ||= "p4audit";
my ($output_file) = $opt_o.".xls";

$opt_d ||= "tomorrow"; # if no date specified, use tomorrow (show all users)
my ($olddate) = &ParseDate($opt_d) || die "Cannot parse \"$opt_d\".\n";

my (@users);
my ($oldusers) = "FALSE";
my ($number) = 0;
my (@p4clients) = `p4 -Ztag clients`;
my (@clients);
my ($j) = 0;
for my $i (0..$#p4clients) {
   chomp ($p4clients[$i]);
   if ($p4clients[$i] !~ /^\.\.\./ && $next) {
      $next = 0;
      $j ++;
      next;
   }
   if ($p4clients[$i] =~ /\.\.\. client (.*)/) {
      $clients[$j]{client} = $1;
      $next = 1;
   } elsif ($p4clients[$i] =~ /\.\.\. Update (.*)/) {
      $clients[$j]{Update} = $1;
   } elsif ($p4clients[$i] =~ /\.\.\. Access (.*)/) {
      $clients[$j]{Access} = $1;
   } elsif ($p4clients[$i] =~ /\.\.\. Owner (.*)/) {
      $clients[$j]{Owner} = $1;
   } elsif ($p4clients[$i] =~ /\.\.\. Host (.*)/) {
      $clients[$j]{Host} = $1;
   } elsif ($p4clients[$i] =~/\.\.\. Root (.*)/) {
      $clients[$j]{Root} = $1;
   } elsif ($p4clients[$i] =~/\.\.\. Options (.*)/) {
      $clients[$j]{Options} = $1;
   } elsif ($p4clients[$i] =~/\.\.\. MapState (.*)/) {
      $clients[$j]{MapState} = $1;
   } elsif ($p4clients[$i] =~/\.\.\. Description (.*)/) {
      $clients[$j]{Description} = $1;
   } 
}

my (@p4opened) = `p4 opened -a`;

my (@pendingchanges) = `p4 changes -spending`;
my (@emptychangelists);
foreach my $change (@pendingchanges) {
   if ($change =~ /Change (\d+) on (\d+\/\d+\/\d+) by (.*)\@(.*) \*pending\* \'(.*)\'/) {
      my (%ecl);
      $ecl{changenumber} = $1;
      $ecl{date} = $2;
      $ecl{user} = $3;
      $ecl{client} = $4;
      $ecl{comment} = $5;
      my(@opened) = grep(/change $ecl{changenumber} /, @p4opened);
      if ($#opened < 0) {
         push (@emptychangelists, \%ecl);
      }
   }
}

my (@p4users);
open(P4,"p4 users |") || die "Can't perform 'p4 users', $!\n";
while ($line = <P4>) {
   if ($line =~ /(.*) <(.*)> \((.*)\) accessed (.*)/) {
      $user = $1;
      $email = $2;
      $fullname = $3;
      $access = $4;
   }
   push (@p4users, $user);
   my ($newdate) = &ParseDate($access);
   my($flag) = &Date_Cmp($olddate, $newdate);
   my (%user);
   undef (%user);
   if ($flag > 0) {   # an old user
      $user{old} = 1;
      $oldusers = "TRUE";
   }
   $user{name} = $user;
   $user{access} = $access;
   $user{email} = $email;
   $user{fullname} = $fullname;
   @user = `p4 user -o $user`;
   foreach (@user) {
      if (/\t\/\//) {  # quick and dirty test for reviews
         $user{reviews} = 1;   
      }
      if (/^Password:/) { # does this user have a password?
         $user{passwd} = 1;
      }
   }
   my(@opened) = grep(/by $user \@/, @p4opened);  # how about opened files?
   $user{opened} = scalar(@opened);
   push (@users, \%user);
   $number++;
}
close (P4);
my (@unused);  # MAHI specific
my (@invalidgroupmembers);
my (@p4groups) = `p4 groups`;
foreach my $group (@p4groups) {
   my ($index);
   chomp ($group);
   my (@p4group) = `p4 group -o $group`;
   for my $i (0..$#p4group) {
      if ($p4group[$i] =~ /^Users:$/) {
         $index = $i + 1;
         last;
      }   
   } 
   for my $i ($index..$#p4group) {
      if ($p4group[$i] =~ /\t(.*)/) {
         my ($groupuser) = $1;
         my ($found) = 0;
         foreach my $user (@p4users) {
            if ($user eq $groupuser) {
               $found = 1;
            }
         }
         my (%igm);
         if ($group eq "unused") { # people in the unused group
            push (@unused, $groupuser);
         }
         if (! $found) {
            $igm{User} = $groupuser;
            $igm{Group} = $group;
            push (@invalidgroupmembers, \%igm);
         }
      }
   }
}

my (@lostclients);
foreach my $client (@clients) {
   my ($found) = 0;
   foreach my $user (@p4users) {
      if ($user eq $$client{Owner}) {
         $found = 1;
         last;
      }
   }
   foreach my $user (@unused) { 
      # check to see if any clients are owned by folks in the unused group
      if ($user eq $$client{Owner}) {
         $found = 2;
         last;
      }
   }
   # lost clients are those who don't have a valid perforce owner
   # invalid clients are those whose owner is in the 'unused' group
   if ($found == 0) {
      push (@lostclients, $client);
   } elsif ($found == 2) {
      push (@invalidclients, $client); 
   } else {
      my ($oldclient);
      my ($clientdate) = &returnNormaldate($$client{Access});
      my ($newdate) = &ParseDate($clientdate) || die "Cannot parse \"$clientdate\".\n";
      my($flag) = &Date_Cmp($olddate, $newdate);
      if ($flag > 0) {
         push (@oldclient, $client); 
      }
   }
}

my $workbook = Spreadsheet::WriteExcel->new($output_file) || die "Cannot create file: $!\n";
my $worksheet1 = $workbook->addworksheet('TOC');
my $worksheet2 = $workbook->addworksheet('lost clients');
my $worksheet3 = $workbook->addworksheet('old clients');
my $worksheet4 = $workbook->addworksheet('old users');
my $worksheet5 = $workbook->addworksheet('invalid group members');
my $worksheet6 = $workbook->addworksheet('empty changelists');
my $worksheet7 = $workbook->addworksheet('invalid clients');
my $worksheet8 = $workbook->addworksheet('no password');

# Create a format for the column headings
my $header = $workbook->addformat();
$header->set_bold();

#toc
my (@widths) = ("25");
for my $i (0..$#widths) { $worksheet1->set_column($i,$i,$widths[$i]); }
my (@fieldnames) = ("lost clients", "old clients", "old users", "invalid group members", "empty changelists", "invalid clients", "no password");
for my $i (0..$#fieldnames) { $worksheet1->write($i,'0',$fieldnames[$i],$header); }
$worksheet1->write('0','1',"clients who don't have a valid perforce owner");
$worksheet1->write('1','1',"clients whose access time is older than \'$opt_d\'");
$worksheet1->write('2','1',"users whose access time is older than \'$opt_d\'");
$worksheet1->write('3','1',"group members who are not valid perforce users");
$worksheet1->write('4','1',"pending changelists that have no open files associated with them");
$worksheet1->write('5','1',"clients whose owner is in the 'unused' group");
$worksheet1->write('6','1',"users who don't have a password");


if ($#lostclients >= 0) {
   # set column widths
   my (@widths) = ("20", "15", "18", "12", "18", "20", "20", "20");
	for my $i (0..$#widths) { $worksheet2->set_column($i,$i,$widths[$i]); }
	my (@fieldnames) = ("Client Name", "Owner", "Access", "Opened Files", "Update", "Host", "Root", "Options");
	for my $i (0..$#fieldnames) { $worksheet2->write('0',$i,$fieldnames[$i],$header); }

   my ($x) = 1;
	foreach (@lostclients) {
      my ($clientname) = $$_{client};	  
      my (@clopened) = grep(/by .*\@$clientname/, @p4opened);
      $worksheet2->write($x,'0',$$_{client});
      $worksheet2->write($x,'1',$$_{Owner});
      $worksheet2->write($x,'2',returnP4date($$_{Access}));
      $worksheet2->write($x,'3',scalar(@clopened));
      $worksheet2->write($x,'4',returnP4date($$_{Update}));
      $worksheet2->write($x,'5',$$_{Host});
      $worksheet2->write($x,'6',$$_{Root});
      $worksheet2->write($x,'7',$$_{Options});
		$x++;
	}
} else {
   $worksheet2->write('0','0',"No Lost Clients");
}   

if ($#oldclient >= 0) {
   # set column widths
   my (@widths) = ("20", "15", "18", "12", "18", "20", "20", "20");
	for my $i (0..$#widths) { $worksheet3->set_column($i,$i,$widths[$i]); }
	my (@fieldnames) = ("Client Name", "Owner", "Access", "Opened Files", "Update", "Host", "Root", "Options");
	for my $i (0..$#fieldnames) { $worksheet3->write('0',$i,$fieldnames[$i],$header); }
	my ($x) = 1;
	foreach(@oldclient) {
		my ($clientname) = $$_{client};	  
      my (@clopened) = grep(/by .*\@$clientname/, @p4opened);
      $worksheet3->write($x,'0',$$_{client});
      $worksheet3->write($x,'1',$$_{Owner});
      $worksheet3->write($x,'2',returnP4date($$_{Access}));
      $worksheet3->write($x,'3',scalar(@clopened));
      $worksheet3->write($x,'4',returnP4date($$_{Update}));
      $worksheet3->write($x,'5',$$_{Host});
      $worksheet3->write($x,'6',$$_{Root});
      $worksheet3->write($x,'7',$$_{Options});
		$x++;
	}
} else {
   $worksheet3->write('0','0',"No Old Clients");
} 

if ($oldusers eq "TRUE") {
   # set column widths
   my (@widths) = ("15", "20", "25", "12", "18", "10", "10");
	for my $i (0..$#widths) { $worksheet4->set_column($i,$i,$widths[$i]); }
	my (@fieldnames) = ("Username", "Full Name", "E-mail", "Opened Files", "Access", "Reviews", "Clients");
	for my $i (0..$#fieldnames) { $worksheet4->write('0',$i,$fieldnames[$i],$header); }
	my ($x) = 1;
	foreach(@users) {
      if ($$_{old}) {
   		my ($username) = $$_{name};	  
         my (@usopened) = grep(/by $username \@/, @p4opened);
   		my ($ownedclients) = 0;
   		foreach my $client (@clients) {
            if ($$client{Owner} eq $$_{name}) {
               $ownedclients ++;
   			}
   		}
         $worksheet4->write($x,'0',$$_{name});
         $worksheet4->write($x,'1',$$_{fullname});
         $worksheet4->write($x,'2',$$_{email});
         $worksheet4->write($x,'3',scalar(@usopened));
         $worksheet4->write($x,'4',$$_{access});
         $worksheet4->write($x,'5',$$_{reviews});
         $worksheet4->write($x,'6',$ownedclients);
   		$x++;
      }
	}
} else {
   $worksheet4->write('0','0',"No Old Users");
} 

if ($#invalidgroupmembers >= 0) {
   # set column widths
   my (@widths) = ("20", "20");
	for my $i (0..$#widths) { $worksheet5->set_column($i,$i,$widths[$i]); }
	my (@fieldnames) = ("Group", "Username");
	for my $i (0..$#fieldnames) { $worksheet5->write('0',$i,$fieldnames[$i],$header); }
   my ($x) = 1;
   foreach (@invalidgroupmembers) {
      $worksheet5->write($x,'0',$$_{Group});
      $worksheet5->write($x,'1',$$_{User});
      $x ++;
   }
} else {
   $worksheet5->write('0','0',"No Invalid Group Members");
} 

if ($#emptychangelists >= 0) {
   # set column widths
   my (@widths) = ("8", "18", "20", "10", "30");
	for my $i (0..$#widths) { $worksheet6->set_column($i,$i,$widths[$i]); }
	my (@fieldnames) = ("Number", "Owner", "Client", "Date", "Description");
	for my $i (0..$#fieldnames) { $worksheet6->write('0',$i,$fieldnames[$i],$header); }
   my ($x) = 1;

   foreach (@emptychangelists) {
      $worksheet6->write($x,'0',$$_{changenumber});
      $worksheet6->write($x,'1',$$_{user});
      $worksheet6->write($x,'2',$$_{client});
      $worksheet6->write($x,'3',$$_{date});
      $worksheet6->write($x,'4',$$_{comment});
      $x ++;
   }
} else {
   $worksheet6->write('0','0',"No Empty Changelists");
} 

if ($#invalidclients >= 0) {
   # set column widths
   my (@widths) = ("20", "15", "18", "12", "18", "20", "20", "20");
	for my $i (0..$#widths) { $worksheet7->set_column($i,$i,$widths[$i]); }
	my (@fieldnames) = ("Client Name", "Owner", "Access", "Opened Files", "Update", "Host", "Root", "Options");
	for my $i (0..$#fieldnames) { $worksheet7->write('0',$i,$fieldnames[$i],$header); }

   my ($x) = 1;
	foreach (@invalidclients) {
      my ($clientname) = $$_{client};	  
      my (@clopened) = grep(/by .*\@$clientname/, @p4opened);
      $worksheet7->write($x,'0',$$_{client});
      $worksheet7->write($x,'1',$$_{Owner});
      $worksheet7->write($x,'2',returnP4date($$_{Access}));
      $worksheet7->write($x,'3',scalar(@clopened));
      $worksheet7->write($x,'4',returnP4date($$_{Update}));
      $worksheet7->write($x,'5',$$_{Host});
      $worksheet7->write($x,'6',$$_{Root});
      $worksheet7->write($x,'7',$$_{Options});
		$x++;
	}
} else {
   $worksheet7->write('0','0',"No Invalid Clients");
}

# users without a password
my ($passwd) = "TRUE";
foreach (@users) {
   if (! $$_{passwd}) {
      $passwd = "FALSE";
   }
}
if ($passwd eq "FALSE") {
   # set column widths
   my (@widths) = ("15", "20", "25");
	for my $i (0..$#widths) { $worksheet8->set_column($i,$i,$widths[$i]); }
	my (@fieldnames) = ("Username", "Full Name", "E-mail");
	for my $i (0..$#fieldnames) { $worksheet8->write('0',$i,$fieldnames[$i],$header); }
	my ($x) = 1;
	foreach(@users) {
      unless ($$_{passwd}) {
         $worksheet8->write($x,'0',$$_{name});
         $worksheet8->write($x,'1',$$_{fullname});
         $worksheet8->write($x,'2',$$_{email});
   		$x++;
      }
	}
} else {
   $worksheet8->write('0','0',"Every user has a password.");
}

sub returnP4date() {
   my ($ztime) = shift;
   my (@today) = localtime($ztime);
   $today[5]+=1900;
   $today[4]++;
   foreach (@today) {
      if ($_ < 10) {
         $_ = "0".$_;
      }
   }
   my ($today)="$today[5]/$today[4]/$today[3]:$today[2]:$today[1]:$today[0]";
   return ("$today");
}

sub returnNormaldate() {
   my ($ztime) = shift;
   my (@today) = localtime($ztime);
   $today[5]+=1900;
   $today[4]++;
   foreach (@today) {
      if ($_ < 10) {
         $_ = "0".$_;
      }
   }
   my ($today)="$today[4]/$today[3]/$today[5] $today[2]:$today[1]:$today[0]";
   return ("$today");
}