#########################################################################
# package SomaTools.pm
# This Perl module contains miscellaneous functions relevant to the Soma 
# project

# V      Who       When        What
# ---------------------------------------------------------------------------
# 1.0.3  skendric  06-16-2008  Stylistic mods
# 1.0.2  skendric  03-12-2007  Stylistic mods
# 1.0.1  skendric  09-07-2006  More robust parsing of fields within 
#                              the xxx_to_wall_jack routines
# 1.0.0  skendric  10-13-2005  First version
#
#
#
# Authors:  Stuart Kendrick <sbk {insert at sign here } skendric
#                                {insert dot here} com>
#
# Source:  http://www.skendric.com/device/soma
#
# This software is available under the GNU GENERAL PUBLIC LICENSE, see
# http://www.fsf.org/licenses/gpl.html
#


package FHCRC::VDOPS::SomaTools;

#### Load modules ####
use strict;
use warnings;
use threads;
use threads::shared;
use Carp qw(carp cluck croak confess);
use Data::Dumper;
use Spreadsheet::ParseExcel;
use DBI;
use Exporter;
use List::MoreUtils qw(all any notall none uniq);
use Net::Syslog;
use Perl6::Say;
use Time::Local;
use Thread::Running qw(running);

use lib '/home/soma/lib';
use FHCRC::VDOPS::SomaData;
use FHCRC::VDOPS::Utilities;

#### Set-up export stuff ####
our @ISA = qw(Exporter);
our @EXPORT = qw(
                    csv_to_wall_jack
                    xls_to_wall_jack
                 );

##### Only subroutines below here ####


########################################################################
# Given a CSV file name formatted as follows:
# 
# Switch     Slot     Port     Jack
# a1-esx     2        1        A1-141-01-A
# a1-esx     2        2        A1-141-02-A
# ...
#
# read it and return a reference to a hash of jacks keyed by the 
# construct "Switch~Slot~Port"
########################################################################
sub csv_to_wall_jack {
  my $file = shift;
  my $jack;
  my $name;
  my $port;
  my $slot;
  my $switch;
  my %wallJack;

  # Debug trace
  trace_location('begin') if $debug > 2;

  # Sanity check
  confess 'No parameters' unless defined $file;

  # Do the work
  open my $file , <, $file or confess "Cannot open $file: $!";
  LINE:
  while (my $line = <$file>) {
    ($name, $slot, $port, $jack) = split(',', $line);

    next LINE unless
        defined ($name and defined $slot and defined $port and defined $jack);

    # Process jack
    ($jack = $jack) =~ s/\s//g;
    next LINE if $jack eq $DASH;
    $jack =~ s/\"//g;
    $jack =~ s/--/-/g;

    # Process slot
    if ($slot =~ /\//) {              # if slot contains a '/' character
      ($slot) = ($slot =~ /\/(\d+)/); # interpret the next digits as slot
    } 

    # Process port
    if ($port =~ /\//) {              # if port contains a '/' character
      ($port) = ($port =~ /\/(\d+)/); # interpret the next digits as port
    } 

    # Build switch-side identifier
    $switch = $name . $TILDE . $slot . $TILDE . $port;
    say "$switch connects to $jack" if $debug == 4;
    $wallJack{$switch} = $jack;
  }
  close FILE;

  # Debug trace
  trace_location('end') if $debug > 2;

  return \%wallJack;
}




########################################################################
# Given an Excel file name formatted as follows:
# 
# Switch     Slot     Port     Jack
# a1-esx     2        1        A1-141-01-A
# a1-esx     2        2        A1-141-02-A
# ...
#
# read it and return a reference to a hash of jacks keyed by the 
# construct "Switch~Slot~Port"
#
# Notice that this routine does not support 'Stack'
#
# This routine uses Spreadsheet::ParseExcel, which I don't understand.  
# For instance, what is the purpose of the 'FOO' string?  And what do 
# the ttlRow, startCol, colCnt, datRow, and datLmt options do?
########################################################################
sub xls_to_wall_jack {
  my $dbh;
  my $file = shift;       # Name of Excel file to open
  my $jack;
  my $name;
  my $port;
  my $sheet = shift;      # Name of sheet within Excel file to read
  my $slot;
  my $sth;
  my $switch;
  my %wallJack;

  # Debug trace
  trace_location('begin') if $debug > 2;

  # Debug info
  dbi_trace($dbiDebug) if $dbiDebug > 0;

  # Sanity check
  confess 'Not enough parameters' unless (defined $file and defined $sheet);

  # Open the file
  $dbh = DBI->connect(
      "DBI:Excel:file=$file", undef, undef,
         {xl_vtbl =>
            {FOO =>
               {
                 sheetName => $sheet,
                 ttlRow    =>  5,
                 startCol  =>  1,
                 colCnt    =>  4,
                 datRow    =>  6,
                 datLmt    =>  4,
               }
            }
          })
  or confess "Cannot connect to $file:$sheet:  " . $DBI::errstr;

  # Prepare and execute
  $sth = $dbh->prepare(q/SELECT * FROM PortsAndJacks/);
  $sth->execute();

  # Build the data structure
  JACK:
  while ( ($name, $slot, $port, $jack)  = $sth->fetchrow_array ) {
    next JACK unless 
        defined ($name and defined $slot and defined $port and defined $jack);

    # Process jack
    ($jack = $jack) =~ s/\s//g;
    next JACK if $jack eq $DASH;
    $jack =~ s/\"//g;
    $jack =~ s/--/-/g;

    # Process slot
    if ($slot =~ /\//) {              # if slot contains a '/' character
      ($slot) = ($slot =~ /\/(\d+)/); # interpret the next digits as slot
    }

    # Process port
    if ($port =~ /\//) {              # if port contains a '/' character
      ($port) = ($port =~ /\/(\d+)/); # interpret the next digits as port
    }

    # Build switch-side identifier
    $switch = $name . $TILDE . $slot . $TILDE . $port;
    say "$switch connects to $jack" if $debug == 4;
    $wallJack{$switch} = $jack;
  }

  # Clean-up
  $sth->finish();
  $dbh->disconnect() or print_it("DBI::$DBI::errstr\n");

  # Debug trace
  trace_location('end') if $debug > 2;

  return \%wallJack;
}




1;