#
#  CSDB.pm
#  --make queries to the inventory database

package Mchange::CSDB;

use strict;

use lib '/afs/.cs.wisc.edu/s/mchange-2.2/common/perl5';

use CSL::DBs;
use CSL::Pg;
use DBI;
use Class::Phrasebook::SQL;
use Carp;

use Mchange;

sub new {
  my ( $class, $error_handle ) = @_;
  
  print "Connecting to CSDB...\n";
  my $dbh= DBI->connect( $CSL::DBs::csdb )
    or die $DBI::errstr;

  $dbh->do("set search_path to csdb");
  $dbh->do("set datestyle to sybase");
  $dbh->{ChopBlanks} = 1;
  $dbh->begin_work;
  
  #set up the phrasebook
  my $pb = new Class::Phrasebook::SQL( undef, "./Mchange/inv.pbk" );
  $pb->place_holders_conatain_dollars( 1 );
  $pb->load( "EN" );
  return unless $pb;

  my $ref = { dbh => $dbh,
              pb => $pb,
              eh => $error_handle };

  my $ret =  bless $ref, $class;
  return $ret;
 
}

sub DESTROY {

  my $self = shift;

  #print "Mchange::CSDB DESTROY\n";g
  
  $self->{dbh}->disconnect if $self->{dbh};
  unlink( $self->{status_file} ) if -e $self->{status_file};
}

#
#  query_machine_parts
#  --return info on parts
#

sub query_machine_parts {

  my ( $self, $mname ) = @_;

  my $query = $self->_run_query( "GET_PARTS", {cpuname => $mname} );

  #make a hash ref out of it
  my $hash_ref;
  while ( my $ref = $query->fetchrow_hashref ) {
    $hash_ref->{$ref->{invnum}} = $ref;
  }

  return $hash_ref;

}

#
#  query_machine_attrib
#  --return info on all the attrib of a machine
#

sub query_machine_attrib {

  my ( $self, $machine ) = @_;

  my $ref1 = $self->_run_query( "GET_ATTRIBS", {cpuname => $machine} )->fetchrow_hashref;
  my $ref2 = $self->_run_query( "GET_MONITOR", {cpuname => $machine} )->fetchrow_hashref;  
  my $ref3 = $self->_run_query( "GET_VALIDOS", {cpuname => $machine} );

  $ref1->{monitor} = $ref2->{type};
  
  my @list;
  while ( my $ref = $ref3->fetchrow_hashref ) {
    chomp( $ref->{os} );
    push @list, $ref->{os};
  }
  
  $ref1->{validos} = \@list;
  return $ref1;
}

#
#  query_part
#  --return info on a part
#

sub query_part {

  my ( $self, $part ) = @_;

  return $self->_run_query( "GET_PART_INFO", {invnum => $part} )->fetchrow_hashref;

}

#
#  query_machine
#  --see if a machine exists in the database
#

sub query_machine {

  my ( $self, $machine ) = @_;

  my $query = $self->_run_query( "GET_MACHINE", {cpuname => $machine} );

  return 1 if $query->fetchrow_hashref;
  
}

#
#  check_account
#  --see if a person has an account on a machine
#

sub check_account {

  my ( $self, $machine, $login ) = @_;

  my $query = $self->_run_query( "CHECK_ACCOUNT", {login => $login, machine => $machine} );
  return 1 if $query->fetchrow_hashref;
}

#
#  transfer_prime
#  -- transfer prime user
#

sub trans_prime {

  my ( $self, $curname, $newname ) = @_;

  my $query = $self->_run_query( "TRANSFER_PRIME", {curname => $curname, newname => $newname} );

  return $self->{eh}->( "More than one row updated duing \'trans_prime( $curname, $newname )\'" )
    if $query->rows > 1;

}

#
#  get_cpu_part
#  --get the cpu part on the box
#

sub get_cpu_part {

  my ( $self, $machine ) = @_;

  my $query = $self->_run_query( "GET_CPU_PART", {cpuname => $machine} );
  my $ref = $query->fetchrow_hashref;

  return $self->{eh}->( "$machine does not have a CPU part" )
    unless $ref;

  return $ref->{invnum};

}

#
#  find_machine
#  --find a machine based on a string
#

sub find_machine {

  my ( $self, $string ) = @_;

  chomp( $string );
  $string = uc( $string ) . '%';
  my $query = $self->_run_query( "FIND_MACHINE", {string => $string} );

  my @list;

  while ( my $ref = $query->fetchrow_hashref ) {
    push( @list, $ref->{cpuname} );
  }

  return @list;
  
}

#
#  update_part
#  --update the cpuname in the db
#

sub update_part {

  my ( $self, $machine, $invnum ) = @_;

  my $query = $self->_run_query( "UPDATE_PART", {cpuname => $machine, invnum => $invnum} );

  return $self->{eh}->( "More than one row updated during 'update_part( $machine, $invnum )'" )
    if $query->rows != 1;
  
}

#
#  update_attrib
#  --update an attribute in the db
#

sub update_attrib {
  
  my ( $self, $attrib, $machine, $value ) = @_;

  my $query;

  if ( $attrib eq 'cpuname' ) {
      return $self->{eh}->( "New CPU name \`$value\' is invalid." )
          if !Mchange->is_valid_mname( $value );
      $query = $self->_run_query( "UPDATE_NAME", {curname => $machine, newname => $value} );

      return $self->{eh}->( "More than one row updated during \'update_attrib( $attrib, $machine, $value )\'" )
          if $query->rows > 1;

      #move all the parts over from the old machine
      $query = $self->_run_query( "MOVE_PARTS", {oldmachine => $machine, newmachine => $value} );

      #monitor is a separate table, so make sure to reassociate it on name change
      $query = $self->_run_query( "GET_MONITOR", {cpuname => $machine} );
      my $ref = $query->fetchrow_hashref;

      $self->update_attrib( 'monitor', $value, $ref->{type} ) if $ref;
      
  }
  elsif ( $attrib eq 'monitor' ) {

    #can't update monitor unless there is an fqdn
    return unless $machine =~ m/\.wisc\.edu$/;
    $query = $self->_run_query( "UPDATE_MONITOR", {type => $value, cpuname => $machine} )
      
  }
  elsif ( $attrib eq 'condor' ) {
    $query = $self->_run_query( "UPDATE_CONDOR", {cpuname => $machine, value => $value} );
  }
  elsif ( $attrib eq 'validos' ) {
    $query = $self->_run_query( "GET_CPU_PART", {cpuname => $machine} );
    my $ref = $query->fetchrow_hashref;
    
    return $self->{eh}->( "$machine doesn't have a CPU part--can't set validos" )
      unless $ref;
    
    $query = $self->_run_query( "UPDATE_VALIDOS", {modelnum => $ref->{modelnum}, validos => $value} );
  }
  else {
    $query = $self->_run_query( "UPDATE_ATTRIB", {attrib => $attrib, cpuname => $machine, value => $value} );
  }

  return $self->{eh}->( "More than one row updated during \'update_attrib( $attrib, $machine, $value )\'" )
    if $query->rows > 1;

}

#
#  update_office
#  --update a users office
#

sub update_office {

  my ( $self, $user, $office ) = @_;

  my $query = $self->_run_query( "UPDATE_OFFICE", {office => $office, username => $user} );

  return $self->{eh}->( "More than one row updated duing \'update_office( $user, $office )\'" )
    if $query->rows > 1;
}

#
#  get_status_data
#  --return the values for the status fields
#

sub get_status_data {

  my ( $self ) = @_;

  my ($fh, $file, $query);

  my $me = `whoami`;
  chomp $me;
  
  $file = "/tmp/mc_status_$me";  
  return $file if -e $file;
  
  $self->{status_file} = $file;
  
  $query = $self->_run_query( 'MACHINE_STATUS_VALUES', {} );
  $fh = new IO::File "> $file";
  return unless defined $fh;

  while ( my $ref = $query->fetchrow_hashref ) {
    print $fh "$ref->{status}=$ref->{status}\n";
  }

  $fh->close;
  return $file;
}

sub commit_changes {

  my ( $self ) = @_;

  $self->{dbh}->commit;
}

sub _run_query {

  my ( $self, @pb_args ) = @_;

  my $query;
  eval {
    $query = $self->{dbh}->prepare( $self->{pb}->get( @pb_args ) );
    $query->execute;
  };
  confess $@ if $@;

  return $query;
  
}


1;
