#!/s/std/bin/perl
package printer_management;

use strict;
use warnings;

use DBI;
use CSL::DBs;
use CSL::Pg;
use Mail::Internet;
use Text::FormatTable;
use FileHandle;
our $VERSION = '0.01';

#TODO: change once done
my $lab_mail = 'lab@cs.wisc.edu'; 
my $database;    #Connection to postgres csdb
my $printer_path = "SET search_path TO printer_accounting";
my $csdb_path    = "SET search_path TO csdb";
my @changed_parts;

my @types = ('all', 'K', 'C', 'M', 'Y', 'Maintenance Kit', 'Maintenance Kit A', 'Maintenance Kit B'); 

=head1 NAME

printer_management - Perl module to connect with CSDB and perform tasks
with the database. 


=head1 DESCRIPTION

This module will allow you to perform basic tasks with the CSDB
printer managment database. Used in tandom with the printerman script.

=head1 METHODS

=head2 Constructor

=over

=item new()

Connect to printer management database

=back

=cut

#my $printers = new printer_management

sub new {
	my ($class) = @_;

	$database = DBI->connect( $CSL::DBs::csdb, '', '', )
	  or die("Could not connect to CSDB\n");
  	#This currently causes "die" to occur if an error occurs, 
	#considering changing this so that we return failure via error code instead
	#also possible to define a DBI error handler
  	$database->{RaiseError} = 1;
	$database->do($printer_path);
	my $ret = $class;
	return $ret;
}

=head2 Other Methods

=over

=item disconnect()

Disconnects from the database.

=cut

sub disconnect {
	$database->disconnect();
}

=item add_supply($printer_modelnum, $supply_modelnum, $type, $number_available, $low_supply_threshold)

Adds a supply for a given printer model, used for adding a new supply, not updating. 

=cut

sub add_supply {
	my ( $class, $printer_modelnum, $supply_modelnum, $type, $number_available,
		$low_supply_threshold )
	= @_;

	#check printer, and then make sure that the supply does not exist
	if ( !$class->check_model($printer_modelnum) ) {
		die(
			"You cannot add supplies until the printer model number is in CSDB!"
		);
	}
	if ( $class->check_supply( $printer_modelnum, $type ) ) {
		die("This part is already in the database!");
	}

	$database->do($printer_path);
	my $sth = $database->prepare('INSERT INTO supplies VALUES (?,?,?,?,?)');
	$sth->execute( $printer_modelnum, $supply_modelnum, $type,
		$number_available, $low_supply_threshold );

	#adds changed part to array for checking ordering information later
	my $array_ref = [ $printer_modelnum, $type ];
	push( @changed_parts, $array_ref );
}

=item update_supply_amount($printer_modelnum, $type, $number_available)

Used to update the number of a given supply

=cut

sub update_supply_amount {
	my ( $class, $printer_modelnum, $type, $number_available ) = @_;
	if ( !$class->check_supply( $printer_modelnum, $type ) ) {
		return 0; 
	}

	$database->do($printer_path);
	my $sth = $database->prepare(
"UPDATE supplies SET number_available = ? WHERE printer_modelnum = ? AND type = ?"
	  );
	$sth->execute( $number_available, $printer_modelnum, $type );

	#adds changed part to array for checking ordering information later
	my $array_ref = [ $printer_modelnum, $type ];
	push( @changed_parts, $array_ref );
	return 1; 
}

=item remove_supply($printer_modelnum, $type)

Removes a supply for a printer in the database. If type is 'all' then it will remove
all parts for that model. 

=cut

sub remove_supply {
	my ( $class, $printer_modelnum, $type ) = @_;
	my $sth;
	if ( $class->check_supply( $printer_modelnum, $type ) ) {
		$sth = $database->prepare("DELETE FROM supplies WHERE printer_modelnum = ? AND type = ?");
		$sth->execute( $printer_modelnum, $type );
	}
	elsif ( $class->check_model($printer_modelnum) && $type eq 'all' ) {
		$sth = $database->prepare("DELETE FROM supplies WHERE printer_modelnum = ?");
		$sth->execute($printer_modelnum);
	}
	else {
		return 0;
	}
	return 1;
}

=item get_supply_amount($printer_modelnum, $type)

Returns the number of the supplies.

=cut

sub get_supply_amount {
	my ( $class, $printer_modelnum, $type ) = @_;
	if ( !$class->check_supply( $printer_modelnum, $type ) ) {
		die("This supply is not in the database!");
	}
	my $sth = $database->prepare(
		"SELECT number_available from supplies 
  WHERE printer_modelnum = ? AND type = ?"
	);
	$sth->execute( $printer_modelnum, $type );
	my @array = $sth->fetchrow_array;
	return $array[0];
}

=item get_supplies($printer_modelmum)

Returns an array of supplies for a given printer model in the database, or all supplies
if the model is not given.

=cut

sub get_supplies {
	my ( $class, $printer_modelnum ) = @_;
	my $sth;

	#Two options, if a model isn't given the query string selects all suppplies.
	if ($printer_modelnum) {
		if ( !$class->check_model($printer_modelnum) ) {
			die("The printer model number is not in CSDB!");
		}
		$sth =
		  $database->prepare(
			"SELECT * from supplies where printer_modelnum = ? order by type");
		$sth->execute($printer_modelnum);
	}
	else {
		$sth =
		  $database->prepare(
			"SELECT * from supplies order by printer_modelnum");
		$sth->execute();
	}

	return $sth->fetchall_arrayref(
		{
			printer_modelnum     => 1,
			supply_modelnum      => 1,
			type                 => 1,
			number_available     => 1,
			low_supply_threshold => 1
		}
	);
}

=item maintenance_event($printer_name, $page_count, $event){

Adds a maintenance event to the log for a given printer.

=cut

sub maintenance_event {
	my ( $class, $printer_name, $page_count, $event ) = @_;

	#Checking the printer name and adding .cs.wisc.edu if needed
	if ( index( $printer_name, ".cs.wisc.edu" ) == -1 ) {
		$printer_name = $printer_name . '.cs.wisc.edu';
	}

	#Obtaining useful information.
	my $staff = getlogin();

	#Get the date from SQL because the format is correct.
	my @row  = $database->selectrow_array("SELECT LOCALTIMESTAMP(0)");
	my $date = pop(@row);

	#Go to CSDB to get inventory number.
	$database->do($csdb_path);
	#TODO: need to check printer type? 
	my $sth = $database->prepare("SELECT invnum FROM parts WHERE cpuname = ?");
	$sth->execute($printer_name);
	my @array = $sth->fetchrow_array;

	my $invnum;
	if ( $array[0] ) {
		$invnum = $array[0];
	}
	else {
		#no printers returned
		return 0;
	}

	#Now adding the event to database
	$database->do($printer_path);
	$sth = $database->prepare(
		"INSERT INTO maintenance_log VALUES(?, ?, ?, ?, ?, ?)");
	$sth->execute( $printer_name, $page_count, $event, $staff, $date, $invnum );
	return 1;
}

=item remove_maintenance_events($printer_name)

Removes all maintenance events for a given printer.

=cut

sub remove_maintenance_events {
	my ( $class, $printer_name ) = @_;

	#Checking the printer name
	if ( index( $printer_name, ".cs.wisc.edu" ) == -1 ) {
		$printer_name = $printer_name . '.cs.wisc.edu';
	}
	if ( $class->check_printer($printer_name) ) {
		my $sth =
		  $database->prepare("DELETE FROM maintenance_log WHERE printer = ?");
		$sth->execute($printer_name);
		return 1;
	}
	return 0;
}

=item send_mail()

Checks all supplies and sends mail when the threshold is met.

=cut

sub send_mail {
	my ($class) = @_;

	#First, find all changed parts that have met the threshold
	#and store them in an array
	my @parts_to_order;
	foreach my $row (@changed_parts) {
		my $sth =
		  $database->prepare(
			"SELECT * FROM supplies WHERE printer_modelnum = ? AND type = ?");

		$sth->execute( @$row[0], @$row[1] );
		my $row = $sth->fetchrow_hashref;
		if ( $row->{low_supply_threshold} >= 0
			&& ( $row->{number_available} <= $row->{low_supply_threshold} ) )
		{
			push( @parts_to_order, $row );
		}
	}

	if (@parts_to_order) {

		#generate the email message to send now that we have all
		#applicable rows

		my $body =
		    "The shows the following supplies have dropped below their order threshold. \n" 
		  . "The table below should provide "
		  . "all relevant information for ordering supplies.\n\n";

		my $table = Text::FormatTable->new('l | l | l | l | l');

		$table->head(
			"Printer Model",
			"Supply Model Number",
			"Type",
			"Number Available",
			"Low Threshold"
		);

		$table->rule('=');

		foreach my $row (@parts_to_order) {
			$table->row(
				$row->{printer_modelnum}, $row->{supply_modelnum},
				$row->{type},             $row->{number_available},
				$row->{low_supply_threshold}
			);
		}

		$table->rule('=');
		$body .= $table->render(120);

		$body .= "\nThis ticket generated by the printerman program after servicing a printer.\n";
		my $msg = new Mail::Internet;
		$msg->head->add( "From",    $lab_mail );
		$msg->head->add( "To",      $lab_mail );
		$msg->head->add( "Subject", "Printer Supplies Need to be Ordered" );
		$msg->body($body);
		$msg->smtpsend;
	}
}

=item get_printer_model($printer_name);

Obtains the model number of the printer for a given printer name.

=cut

sub get_printer_model {
	my ( $class, $printer_name ) = @_;
	if(!$printer_name){
		return;
	}
	if ( index( $printer_name, ".cs.wisc.edu" ) == -1 ) {
		$printer_name = $printer_name . '.cs.wisc.edu';
	}
	$database->do($csdb_path);
	my $sth = $database->prepare("SELECT modelnum FROM parts WHERE cpuname=?");
	$sth->execute($printer_name);
	my @array = $sth->fetchrow_array;
	$database->do($printer_path);
	if ( $#array > 1 ) {
		die("Duplicate parts with cpuname of $printer_name!\n");
	}
	return $array[0];
}

=item get_logs($printer_name)

Returns the maintenance logs for a given printer, returns all logs if a printer is not given.

=cut

sub get_logs {
	my ( $class, $printer_name ) = @_;
	my $sth;
	if ($printer_name) {
		print $printer_name; 
		if ( index( $printer_name, ".cs.wisc.edu" ) == -1 ) {
			$printer_name = $printer_name . '.cs.wisc.edu';
		}
		$sth =
		  $database->prepare(
			"SELECT * from maintenance_log where printer = ? order by date");
		$sth->execute($printer_name);
	}
	else {
		$sth =
		  $database->prepare("SELECT * from maintenance_log order by date");
		$sth->execute();
	}
	return $sth->fetchall_arrayref(
		{
			printer   => 1,
			pagecount => 1,
			event     => 1,
			staff     => 1,
			date      => 1,
			invnum    => 1
		}
	);
}

=item check_printer($printer_name)

Checks to see if a printer is in CSDB.

=cut

sub check_printer {
	my ( $class, $printer_name ) = @_;
	my $sth;
	if ( !$printer_name ) {
		return 0;
	}
	$database->do($csdb_path);

	if ( index( $printer_name, ".cs.wisc.edu" ) == -1 ) {
		$printer_name = $printer_name . '.cs.wisc.edu';
	}
	$sth =
	  $database->prepare(
		    "select p.cpuname, p.modelnum from parts p, machines m, models x "
		  . "where p.cpuname = m.cpuname and p.modelnum = x.modelnum and x.type = 'PRI' "
		  . "and m.status = 'Production' and m.cpuname like ?" );
	$sth->execute($printer_name);

	$database->do($printer_path);

	if ( !$sth->fetchrow_arrayref() ) { return 0; }
	return 1;
}

=item get_printers()

Retrieves all of the printers in the database.

=cut

sub get_printers {
	my $class = shift @_;
	$database->do($csdb_path);
	my $sth =
	  $database->prepare( "select p.cpuname, p.modelnum, p.invnum, p.room "
		  . "from parts p, machines m, models x "
		  . "where p.cpuname = m.cpuname and p.modelnum = x.modelnum and x.type = 'PRI' "
		  . "and m.status = 'Production' and m.cpuname like '%.cs.wisc.edu'" );
	$sth->execute();

	$database->do($printer_path);
	return $sth->fetchall_arrayref(
		{ cpuname => 0, modelnum => 1, invnum => 2, room => 3 } );
}

=item check_model($printer_modelnum)

Checks to see if a printer model is in the database.

=cut

sub check_model {
	my ( $class, $printer_modelnum ) = @_;
	$database->do($csdb_path);
	my $sth = $database->prepare("SELECT * FROM models where modelnum = ?");
	$sth->execute($printer_modelnum);
	$database->do($printer_path);
	if ( !$sth->fetchrow_arrayref() ) { return 0; }
	return 1;
}

=item check_supply($printer_modelnum, $type)

Checks for a supply in the database.

=cut

sub check_supply {
	my ( $class, $printer_modelnum, $type ) = @_;
	my $sth = $database->prepare(
		"SELECT * FROM supplies 
  		WHERE printer_modelnum = ? AND type = ?"
	);
	$sth->execute( $printer_modelnum, $type );
	if ( !$sth->fetchrow_arrayref() ) { return 0; }
	return 1;
}

=item check_type($type)

Checks if a given type is valid.  

=cut

sub check_type {
	my ( $class, $type ) = @_;
	if ( grep(/^$type$/, @types )) { return 1; }
	return 0;
}
=item get_supply_modelnum($printer_modelnum, $type)

Returns the supply modelnum for the given model and type.

=cut

sub get_supply_modelnum {
	my ( $class, $printer_modelnum, $type ) = @_;
	my $sth = $database->prepare(
		"SELECT supply_modelnum FROM supplies 
      	WHERE printer_modelnum = ? AND type = ?"
	);
	$sth->execute( $printer_modelnum, $type );
	my @array = $sth->fetchrow_array;
	return $array[0];
}

=item update_supply_modelnum($printer_modelnum, $supply_modelnum, $type)

Updates a supply modelnum for a given model and type. 

=cut

sub update_supply_modelnum {
	my ( $class, $printer_modelnum, $supply_modelnum, $type ) = @_; 
	my $sth = $database->prepare(
		"UPDATE supplies SET supply_modelnum = ? 
  		WHERE printer_modelnum = ? AND type = ?"
	);
	$sth->execute( $supply_modelnum, $printer_modelnum, $type );
}

=item update_low_supply_threshold($printer_modelnum, $low_supply_threshold, $type)

Updates the low supply threshold for a given model and type.

=cut

sub update_low_supply_threshold {
	my ( $class, $printer_modelnum, $low_supply_threshold, $type ) = @_;
	my $sth = $database->prepare(
		"UPDATE supplies SET low_supply_threshold = ? 
  		WHERE printer_modelnum = ? AND type = ?"
	);
	$sth->execute( $low_supply_threshold, $printer_modelnum, $type );
}

=item get_page_count($printer_name)

Attempts to get page count from SNMP, returns -1 if unable to do so.
*Will only query for Ricoh printers, 
if a different manufacturer is added this needs to change*

=cut

sub get_page_count {
	my ( $class, $printer_name ) = @_;
	
	my $lanierprinter = 0; 
	my $count = -1;
	
	#Checking the printer name
	if ( index( $printer_name, ".cs.wisc.edu" ) == -1 ) {
		$printer_name = $printer_name . '.cs.wisc.edu';
	}
	
	if ( !$class->check_printer($printer_name) ) {
		die("Printer $printer_name does not exist.");
	}

	my $printer_model = get_printer_model($printer_name);
	
	#get vendor of the printer	
	$database->do($csdb_path);
	my $sth = $database->prepare("SELECT p.vendor FROM parts p, models m 
					WHERE p.cpuname like ? and 
					p.modelnum=m.modelnum and 
					m.type='PRI'");
	$sth->execute($printer_name);
	my @csdbarray = $sth->fetchrow_array;
	$database->do($printer_path);

	if($csdbarray[0] =~ /lanier/i || $csdbarray[0] =~ /ricoh/i){
		$lanierprinter = 1; 
	}

	#stolen from easy-pg, we're not going to handle printers that are not ricoh
	if ($lanierprinter) {
	    # Pagecount for Lanier devices
	    # do NOT test for open failure...it will detect failure 
	    # even though pipe will open successfully  :(
	    # this stopped working (although it works in easy?) due to errors piping /dev/null to stdin
	    #open (COUNT, "/usr/bin/ssh print.cs.wisc.edu /usr/bin/snmpget -c CSL -v 1 $printer_name .1.3.6.1.2.1.43.10.2.1.4.1.1 |");
	    open (COUNT, "/usr/bin/snmpget -c CSL -v 1 $printer_name .1.3.6.1.2.1.43.10.2.1.4.1.1 |");
	    while (<COUNT>) {
	    	chomp;
		if (/Counter32:/) {
		    $count = $_;
		    $count =~ s/.*Counter32: //;
		}
	    }
	    close(COUNT);
	} 
	
	if($count < 1) {
		return -1; 
	}
	return $count; 
}

=back

=head1 SEE ALSO

Documentation on Labstaffdocs wiki 

=head1 AUTHORS

=over

=item Robert Wespetal, E<lt>wespetal@cs.wisc.eduE<gt>

=item Eric Stellpflug, E<lt>stellpfl@cs.wisc.eduE<gt>

=back 

=head1 COPYRIGHT AND LICENSE

Contact lab@cs.wisc.edu for more information or for help.

This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself, either Perl version 5.12.3 or,
at your option, any later version of Perl 5 you may have available.

=cut

1;
