package RecordsetSelector;

use strict;
use warnings;
use vars qw(@ISA);

@ISA=qw(Wx::Dialog);

use Wx qw(:everything);
use File::Spec;
if ($^O=~'Win32') {
 require Win32::ODBC;
  }

use PDL;

use Wx::Event qw(EVT_CLOSE EVT_BUTTON);

use attenuationrecordset;
use attenuation;
use material;

# This is the name of the DSN
my $DSN =  "DTDB";

my @standard_frequencies = qw(3 3.69 4.53 5.57 6.84 8.4 10.32 12.69 15.59 19.15 23.53 28.91 35.53 43.65 53.64 65.9 80.9 99);

my @descriptions;

# Event IDs
my( $OK_Btn_ID,
    $Cancel_Btn_ID) = ( 1, 2);

sub new {
    my( $class ) = shift;
    # now we are calling the base class constructor
#    my( $this ) = $class->SUPER::new( undef, -1, $_[0], $_[1], $_[2],$_[3],$_[4] );
    my( $this ) = $class->SUPER::new( $_[0], $_[1], $_[2],$_[3],$_[4] );
    size_distribution->new($this); # call constructor of parent
    # Filename of the database
    #$this->{database} = "C:\\Andreas\\inversion\\frontend\\data_aco\\DATA_ACO.mdb";
    $this->{database} = "C:\\data_aco\\DATA_ACO.mdb";
    $this->{default_dir} = "C:\\data_aco\\";
    #$this->{default_dir}="c:\\Andreas\\inversion\\frontend\\data_aco\\";


    # Define sample data storage variables
    $this->{measurementrecord}= "";
    $this->{blanksamplerecord}= "";
    
    # Create data for attenuation
    $this->{attn_data} = attenuation->new($this);
    $this->{blank_data} = attenuation->new($this);
    
    # Create data for material properties
    $this->{particle} = material->new($this);
    $this->{fluid} = material->new($this);
    
    # Add a panel for better optics
    $this->{toppanel} = Wx::Panel->new($this,-1);
    # Create the layout: top sizer
    $this->{toppanel}->{topsizer} = Wx::BoxSizer->new(wxVERTICAL );
    # tell we want automatic layout
    #$this->{toppanel}->SetAutoLayout(1);
    
    # Sizer for measurement values
    my $box=Wx::StaticBox->new(
                   $this->{toppanel},
                   -1,
                   "Select the measurement dataset",
                   Wx::Point->new(-1,-1),Wx::Size->new(100,100));
    $this->{toppanel}->{measurement_sizer} =
                   Wx::StaticBoxSizer->new($box,wxVERTICAL);
    # Add sizer to main sizer
    $this->{toppanel}->{topsizer}->Add($this->{toppanel}->{measurement_sizer},
                  0,
                  wxEXPAND|wxALL,
                  10);
    # Load recordset descriptions, displayed in following combo box
    $this->load_recordsetdescriptions;
    # Sizer holds a Combo Box which is used for the record selection
    $this->{meas_combo} = Wx::ComboBox->new($this->{toppanel}, # parent
                   -1,                # id
                   $descriptions[1],  # inital selection
                   wxDefaultPosition, # position
                   wxDefaultSize,     # size
                   [@descriptions],   # selections (array ref)
                   wxCB_READONLY);    # style
                   
    # Sizer for blank sample values
    $this->{toppanel}->{measurement_sizer}->Add($this->{meas_combo},
                  0,
                  wxEXPAND|wxALL,
                  10);
    # Sizer for blank sample values
    my $box2=Wx::StaticBox->new(
                   $this->{toppanel},
                   -1,
                   "Select the dataset of the blank sample",
                   Wx::Point->new(-1,-1),Wx::Size->new(100,100));
    $this->{toppanel}->{blank_sizer} =
                   Wx::StaticBoxSizer->new($box2,wxVERTICAL);
    # Add sizer to main sizer
    $this->{toppanel}->{topsizer}->Add($this->{toppanel}->{blank_sizer},
                  0,
                  wxEXPAND|wxALL,
                  10);
    # Load recordset descriptions, displayed in following combo box
    if ($this->load_recordsetdescriptions) {
      # Sizer holds a Combo Box which is used for the record selection
      $this->{blank_combo} = Wx::ComboBox->new($this->{toppanel}, # parent
                   -1,                # id
                   $descriptions[1],  # inital selection
                   wxDefaultPosition, # position
                   wxDefaultSize,     # size
                   [@descriptions],   # selections (array ref)
                   wxCB_READONLY);    # style
      $this->{toppanel}->{blank_sizer}->Add($this->{blank_combo},
                  0,
                  wxEXPAND|wxALL,
                  10);

      # Create Ok and Cancel Buttons.
      # add a sizer
      $this->{toppanel}->{button_sizer} = Wx::BoxSizer->new( wxHORIZONTAL );
      # Create Ok-Button and add it to $button_sizer
      my ($OK_Btn) =Wx::Button->new( $this->{toppanel},
                   $OK_Btn_ID,
                   "OK" );
      $this->{toppanel}->{button_sizer}->Add($OK_Btn,
                    0, # make horizontally unstretchable
                    wxEXPAND|wxALL, # make border all around (implicit top alignment)
                    5 ); # set border width to 5
                    # Create Ok-Button and add it to $button_sizer
      my ($Cancel_Btn) =Wx::Button->new( $this->{toppanel},
                   $Cancel_Btn_ID,
                   "Cancel" );
      $this->{toppanel}->{button_sizer}->Add($Cancel_Btn,
                    0, # make horizontally unstretchable
                    wxEXPAND|wxALL, # make border all around (implicit top alignment)
                    5 ); # set border width to 5
      $this->{toppanel}->{topsizer}->Add($this->{toppanel}->{button_sizer},
                    0,
                    wxEXPAND|wxALL,
                    10);
      # Assign Events
      EVT_BUTTON($this,          # Object to bind to
            $OK_Btn,         # ButtonID
            \&OK_Btn_Clicked # Subroutine to execute
      );
      EVT_BUTTON($this,          # Object to bind to
            $Cancel_Btn,         # ButtonID
            \&Cancel_Btn_Clicked # Subroutine to execute
      );
      EVT_CLOSE( $this, \&Cancel_Btn_Clicked );
                  
      # Assign top sizer
      $this->SetSizer( $this->{toppanel}->{topsizer} );
      $this->SetAutoLayout(1);
      $this->{toppanel}->{topsizer}->Fit($this->{toppanel});
      # Assign default control
      $this->{toppanel}->SetDefaultItem($this->{meas_combo});
      # Set default return value
      $this->SetReturnCode(0);
      return $this;
  } else {
    $this->SetReturnCode(1);
    return $this;
  }
}

sub Destroy {
  # remove DSN
  use Win32::ODBC;
  if (Win32::ODBC::ConfigDSN(ODBC_REMOVE_DSN, "Microsoft Access Driver (*.mdb)", "DSN=$DSN")) {
    print ("ConfigDSN(): Could not remove temporary DSN because " . Win32::ODBC::Error(). "\n");
    return(0);
  }
}

sub database {
  my $this = shift;
  if (@_) { $this->{database} = shift }
  return $this->{database};
}

sub default_dir {
  my $this = shift;
  if (@_) { $this->{default_dir} = shift }
  return $this->{default_dir};
}



sub load_recordsetdescriptions {
  my ($this) =@_;
  use Win32::ODBC;
  my $db;
#  $Result = Win32::ODBC::ConfigDSN( ODBC_ADD_SYS_DSN, "Microsoft Access Driver (*.mdb)", ("DSN=My DSN", "Description=The Win32 ODBC Test DSN for Perl", "DBQ=c:\\temp\\MyData.mdb", "DEFAULTDIR=c:\\temp", "UID=MyUserId", "PWD=MyPassword" );
#  my $db = new Win32::ODBC($DSN);
  #printf $this->{database} . "\n";
  #if (Win32::ODBC::ConfigDSN(ODBC_REMOVE_DSN, "Microsoft Access Driver (*.mdb)", "DSN=".$DSN)) {
  #  print("ConfigDSN(): Could not remove temporary DSN because " . Win32::ODBC::Error() . "\n");
  #  return(0);
  #}
  if (Win32::ODBC::ConfigDSN(ODBC_ADD_DSN, "Microsoft Access Driver (*.mdb)", ("DSN=".$DSN,
  "Description=Temporary ODBC Source",
  "DBQ=" . $this->{database},
  "DEFAULTDIR=" . $this->{default_dir},
  "UID=", "PWD="))) {
    $db = new Win32::ODBC($DSN);
    if (!($db )){ #$db is handle,ID and PW can be input here
      Wx::MessageBox( "Connection to database failed.\n"  , 'Error', wxOK, $this );
      print "Connection to database failed.\n";
      print "Error:" . Win32::ODBC::Error()."\n";
      return(0);
    }
    else{
      # Select the descriptions from the table
      my $SQL_STRING="SELECT measurements.[Measurement Date], Samples.[Chemical name], [Samples].[Chemical name] & ', ' & [measurements].[Measurement Date] AS Description
FROM Samples INNER JOIN (Experiments INNER JOIN measurements ON (Experiments.[Experiment Date] = measurements.[Experiment Date]) AND (Experiments.[System ID] = measurements.[System ID])) ON (Samples.[Sample Date] = Experiments.[Sample Date]) AND (Samples.[System ID] = Experiments.[System ID])
WHERE (((measurements.[Measurement Date])>#7/1/2002#))
ORDER BY measurements.[Measurement Date];
";
      if (($db->Sql($SQL_STRING)) ){ #this is SQL language
        # SQL statement did not execute correctly
        my($err) = $db->Error;
        Wx::MessageBox( "Data extraction from database failed.\n (SQL error)"  , 'Error', wxOK, $this );
  		  warn "Sql() ERROR\n";
    		warn "\tSQL_STRING: $SQL_STRING\n";
  		  warn "\t\$err:  $err\n";
    		return(0);
      }
      else {
        # SQL statement executed correctly
        my $i = 0; # array index
        # fetch one row from database
        while ($db->FetchRow()) {
          # Extract data
          my(%data) = $db->DataHash();
          # We only want the "Description" field
          $descriptions[$i]=$data{'Description'};
          # increment array index
          $i++;
    	  }  # loop until all recordsets have been read
      }
      # Close database
      $db->Close();
    }
  }
  else {
    # creation of the DSN failed
    Wx::MessageBox( "Could not establish connection to the database - file not found?" , 'Error', wxOK, $this );
    return (0);
  }
  return(1);
}

sub get_measurement_data_from_DB {
  my ($this) =@_;
  my $rec =$this->{measurementrecord};

  my $db = new Win32::ODBC($DSN);

  if (!($db )){ #$db is handle,ID and PW can be input here
    Wx::MessageBox( "Connection to database failed.\n"  , 'Error', wxOK, $this );
    print "connect to database failed.\n";
    print "Error:" . Win32::ODBC::Error()."\n";
    return(0);
  }
  else{
    # Select the record
    my $SQL_STRING="SELECT [Samples].[Chemical name] & ', ' & [measurements].[Measurement Date] AS Description, measurements.A1, measurements.A2, measurements.A3, measurements.A4, measurements.A5, measurements.A6, measurements.A7, measurements.A8, measurements.A9, measurements.A10, measurements.A11, measurements.A12, measurements.A13, measurements.A14, measurements.A15, measurements.A16, measurements.A17, measurements.A18, measurements.A19, measurements.A20, measurements.A21, Materials.[Density  Co] AS rho_L, Materials_1.[Density  Co] AS rho_P, Materials.[S_Speed  Co] AS c_L, Materials_1.[S_Speed  Co] AS c_P
FROM (Materials AS Materials_1 RIGHT JOIN (Materials RIGHT JOIN Samples ON Materials.Name = Samples.Medium) ON Materials_1.Name = Samples.[Dispersed Phase]) INNER JOIN (Experiments INNER JOIN measurements ON (Experiments.[Experiment Date] = measurements.[Experiment Date]) AND (Experiments.[System ID] = measurements.[System ID])) ON (Samples.[Sample Date] = Experiments.[Sample Date]) AND (Samples.[System ID] = Experiments.[System ID])
WHERE ((([Samples].[Chemical name] & ', ' & [measurements].[Measurement Date])='$rec'));";


    if (($db->Sql($SQL_STRING)) ){ #this is SQL language
      # SQL statement did not execute correctly
      my($err) = $db->Error;
      Wx::MessageBox( "Data extraction from database failed.\n (SQL error)"  , 'Error', wxOK, $this );
		  warn "Sql() ERROR\n";
  		warn "\tSQL_STRING: $SQL_STRING\n";
		  warn "\t\$err:  $err\n";
  		return(0);
    }
    else {
      # SQL statement executed correctly
      # fetch one row from database
      while ($db->FetchRow()) {
        # Extract data
        my(%data) = $db->DataHash();
        # We only want the "A.." values
        my @values;
        my @frequencies;
        for(my $i=0;$i<=17;$i++) {
          unless ($data{"A".($i+1)}==0) {
            $values[$i] = $data{"A".($i+1)};
            $frequencies[$i] = $standard_frequencies[$i];
          }
        }
        # Store extracted data
        $this->{attn_data}->alpha(pdl (@values));
        $this->{attn_data}->f(pdl @frequencies);
        # Get material properties
        $this->{particle}->c_L($data{"c_P"});
        $this->{particle}->rho($data{"rho_P"});
        $this->{fluid}->c_L($data{"c_L"});
        $this->{fluid}->rho($data{"rho_L"});

  	  }  # loop until all recordsets have been read
    }
    # Close database
    $db->Close();
  }
  return ($this->{attn_data}->alpha);
}

sub get_blank_data_from_DB {
  my ($this) =@_;
  my $rec =$this->{blanksamplerecord};

  my $db = new Win32::ODBC($DSN);

  if (!($db )){ #$db is handle,ID and PW can be input here
    Wx::MessageBox( "Connection to database failed.\n"  , 'Error', wxOK, $this );
    print "connect to database failed.\n";
    print "Error:" . Win32::ODBC::Error()."\n";
    return(0);
  }
  else{
    # Select the record
    my $SQL_STRING="SELECT [Samples].[Chemical name] & ', ' & [measurements].[Measurement Date] AS Description, measurements.A1, measurements.A2, measurements.A3, measurements.A4, measurements.A5, measurements.A6, measurements.A7, measurements.A8, measurements.A9, measurements.A10, measurements.A11, measurements.A12, measurements.A13, measurements.A14, measurements.A15, measurements.A16, measurements.A17, measurements.A18, measurements.A19, measurements.A20, measurements.A21, Materials.[Density  Co] AS rho_L, Materials_1.[Density  Co] AS rho_P
FROM (Materials AS Materials_1 RIGHT JOIN (Materials RIGHT JOIN Samples ON Materials.Name = Samples.Medium) ON Materials_1.Name = Samples.[Dispersed Phase]) INNER JOIN (Experiments INNER JOIN measurements ON (Experiments.[Experiment Date] = measurements.[Experiment Date]) AND (Experiments.[System ID] = measurements.[System ID])) ON (Samples.[Sample Date] = Experiments.[Sample Date]) AND (Samples.[System ID] = Experiments.[System ID])
WHERE ((([Samples].[Chemical name] & ', ' & [measurements].[Measurement Date])='$rec'));";

    if (($db->Sql($SQL_STRING)) ){ #this is SQL language

      # SQL statement did not execute correctly
      my($err) = $db->Error;
      Wx::MessageBox( "Data extraction from database failed.\n (SQL error)"  , 'Error', wxOK, $this );
		  warn "Sql() ERROR\n";
  		warn "\tSQL_STRING: $SQL_STRING\n";
		  warn "\t\$err:  $err\n";
  		return(0);
    }
    else {
      # SQL statement executed correctly
      # fetch one row from database
      while ($db->FetchRow()) {
        # Extract data
        my(%data) = $db->DataHash();
        # We only want the "A.." values
        my @values;
        for(my $i=0;$i<=17;$i++) {
          unless ($data{"A".($i+1)}==0) {
            $values[$i] = $data{"A".($i+1)};
          }
        }
        $this->{blank_data}->alpha(pdl (@values));
      }  # loop until all recordsets have been read
    }
    # Close database
    $db->Close();
  }
  return ($this->{blank_data}->alpha);
}


sub OK_Btn_Clicked {
  my ($this,$event)=@_;
  $this->{measurementrecord}= $this->{meas_combo}->GetValue;
  $this->{blanksamplerecord}= $this->{blank_combo}->GetValue;
  # Return with "Successful" message
  $this->EndModal(1);
}

sub Cancel_Btn_Clicked {
  my ($this,$event)=@_;
  $this->EndModal(0);
}


1;
