# Name:        dbgrid.pl
# Version:     .80
# Purpose:     Database grid application (Perl Win32::ODBC,GUI)
# Authors:     Eric Hansen,   Copyright 1999, Information  <erichansen@itsi-corp.com>
#              Technology Services, Inc. Dallas, TX
#
#              Mike Kangas <kangas@anlon.com>
#              Anlon Systems, Inc.


use Win32::GUI;
use Win32::GUI::CardLayout;
use Win32::ODBC;

$menu = Win32::GUI::MakeMenu(
  "&File" => "&File",
  "> DSN..."  => "Add",
  "> Close" => "Close",
);

#
#
# Main Window
#
#

$mainWindow = new GUI::Window(
   -name => "Window",
   -text => "PerlSQL Database Interface",
   -width => 800,
   -height => 550,
   -left => 100,
   -top => 100,
   -menu => $menu,
);

$tabletableTreeLabel = $mainWindow->AddLabel (
   -text => 'DSN Tables',
   -height => 15,
   -left => 5,
   -top => 5,
);

$tableTree = $mainWindow->AddTreeView (
   -name   => "TableTree",
   -width  => 250,
   -height => 473,
   -left   => 5,
   -top    => 22,
   -lines  => 1,
   -rootlines => 1,
   -buttons => 1,
   -visible => 1,
);

$tableListViewLabel = $mainWindow->AddLabel (
   -text => 'Table View',
   -height => 15,
   -left => 260,
   -top => 5,
);

$tableListView = $mainWindow->AddListView(
   -name => "tableListView",
   -left => 260,
   -top  => 25,
   -width => 525,
   -height => 360,
   -fullrowselect => 1,
   -gridlines => 1,
);
$tableListView->View(1);
$tableListView->TextColor(hex("0000FF"));

$TextAreaLabel = $mainWindow->AddLabel (
   -text => 'SQL Statement',
   -height => 15,
   -left => 260,
   -top => 390,
);

$sqlTextfield = $mainWindow->AddTextfield(
   -name => "sqlTextfield",
   -multiline => 1,
   -left => 260,
   -top  => 410,
   -width => 525,
   -height => 60,
);

$submitSQLbutton = $mainWindow->AddButton(
   -name => "sqlButton",
   -text => "   Submit SQL   ",
   -height => 25,
   -left => 695,
   -top  => 475,
);


#################################
#
# DSN Select Box Window
#
#################################

$dsnWindow = new GUI::Window(
   -name => "dsnWindow",
   -text => "Select DSN",
   -width => 300,
   -height => 200,
   -left => 150,
   -top => 150,
);

$dsnDropLabel = $dsnWindow->AddLabel(
   -text => "Select DSN ",
   -top => 12,
   -left => 10,
);

$dsnDropdownBox = $dsnWindow->AddCombobox(
   -name => "dsnDropdownBox",
   -left => 80,
   -top => 10,
   -height => 1000,
   -width => 200,
   -style => WS_VISIBLE | 2 | WS_NOTIFY,
);

$dsnUserLabel = $dsnWindow->AddLabel(
   -text => "User Name ",
   -top => 52,
   -left => 10,
);

$userTextfield = $dsnWindow->AddTextfield(
   -name => "userTextfield",
   -left => 80,
   -top  => 47,
   -width => 200,
   -height=> 24,
);

$dsnPassLabel = $dsnWindow->AddLabel(
   -text => "Password ",
   -top => 92,
   -left => 10,
);

$passTextfield = $dsnWindow->AddTextfield(
   -name => "passTextfield",
   -left => 80,
   -top  => 87,
   -width => 200,
   -height=> 24,
);

$dsnButton = $dsnWindow->AddButton(
   -name => "dsnButton",
   -text => "   Submit   ",
   -height => 22,
   -left => 120,
   -top  => 120,
);

$dsnStatus = new GUI::StatusBar($dsnWindow,
        -text => " ",
        -width => $dsnWindow->ScaleWidth,
);



$mainWindow->Show();
Win32::GUI::Dialog;



##########  EVENTS  ###########

sub Add_Click {
  $dsnWindow->Show();
  Load_DSNs();
}

sub Close_Click {
  $db->Close();
  exit;
}

sub TableTree_NodeClick {
  $node = $_[0];
  my %node = $tableTree->GetItem($node);

  if($node{-parent} eq "0") {
    $Table = $node{-text};
    Load_ListView();
  } else {
    $parent = $mainWindow->TableTree->GetParent($node);
    %parent = $mainWindow->TableTree->GetItem($parent);
    $Table = $parent{-text};
    $ColName = $node{-text};
    # Load_DataTypes();
  }
  return 1;
}

sub TableTree_Expand {
    my %node = $tableTree->GetItem($_[0]);
    $ExpandNode = $node{-text};
    return 1;
}

sub TableTree_Collapse {
    my %node = $tableTree->GetItem($_[0]);
    $CollapseNode = $node{-text};
    return 1;
}

sub dsnButton_Click {
  my $item = $dsnDropdownBox->SelectedItem;
  $DSN = $dsnDropdownBox->GetString($item);
  $username = 'undef' if (!($username = $userTextfield->Text()));
  $password = 'undef' if (!($password = $passTextfield->Text()));

  $dsnWindow->Hide();
  Load_TBLs();
}

sub sqlButton_Click {
  $SQL = $sqlTextfield->Text();
  $results = $db->Sql($SQL);
  if($SQL =~ /^SELECT/i) {
    while($db->FetchRow()) {
      @data = $db->Data();
    }
  } else {
    Load_ListView();
  }
}

##########  FUNCTIONS  ###########

sub Load_DSNs {
    $DSNmode=1;
    if (%DSNList=ODBC::DataSources()) {
       #foreach $Name (keys(%DSNList)) {
       #  $height+= 15;
       #}
       #$dsnWindow->dsnDropdownBox->Change(-height => $height);
       foreach $Name (keys(%DSNList)) {
         $dsnDropdownBox->InsertItem($Name);
       }
    } else {
       GUI::MessageBox($mainWindow,"Can't Determine Available DSN's","Error",16,);
       GUI::MessageBox($mainWindow,"Shutting Down the Application","Status",64,);
       exit;
    }
}

sub Load_TBLs {
   $db = new ODBC($DSN, $username, $password);
   if (! $db) {
      GUI::MessageBox($W,"Can't Establish Database Connection to DSN '$DSN'", "Error",16,);
      GUI::MessageBox($W,"Shutting Down the Application","Status",64,);
      exit;
   }
   @Tables=$db->TableList();
   foreach $currTable(@Tables) {
     $table[$tableCount] = $tableTree->InsertItem(
       -text  => "$currTable",
       -name  => "table_$currTable",
     );
     $db->Sql("SELECT * FROM $currTable");
     $db->FetchRow();

     @Cols = $db->FieldNames();
     foreach $currCol(@Cols) {
       %Attrib = $db->ColAttributes($db->SQL_COLUMN_TYPE_NAME, $currCol);
       $tablecol[$tableCount] = $tableTree->InsertItem(
         -parent => $table[$tableCount],
         -text  => "$currCol ($Attrib{$currCol})",
       );
     }
     $tableCount++;
   }
}

sub Load_ListView {

   $tableListView->Clear;
   for($i=$lastcol;$i>=0;$i--) {
      $tableListView->DeleteColumn($i);
   }


   #-------------------------------------------#
   #  Get a row count for progress bar display
   #-------------------------------------------#
   $ret = $db->Sql("SELECT COUNT(*) AS cnt_of_rows FROM $Table");
   if ($ret) {
      $error=$db->Error();  # database error msg
      GUI::MessageBox($mainWindow,$error,"Error",16,);
      GUI::MessageBox($mainWindow,"Shutting Down the Application","Status",64,);
      exit;
   }
   $db->FetchRow();
   $dbcnt = $db->Data("cnt_of_rows");

   #-----------------------------------#
   # Load Listview from the Database
   #-----------------------------------#
   $ret = $db->Sql("SELECT * FROM $Table");
   if ($ret) {
      $error=$db->Error();  # database error msg
      GUI::MessageBox($mainWindow,$error,"Error",16,);
      GUI::MessageBox($mainWindow,"Shutting Down the Application","Status",64,);
      exit;
   }

   @Cols = $db->FieldNames();

   $lastcol=$#Cols;
   for($i=0;$i<=$lastcol;$i++) {
      $columname = $Cols[$i];
      $tableListView->InsertColumn(-index => $i,-width => 100, -text => $columname);
   }

   $itemcnt=0;
   $rowcnt=1;
   while ($db->FetchRow()) {
     @Data=();  # clear the data array
     @Data = $db->Data();
     $tableListView->InsertItem(-item => $itemcnt, -text => "$Data[0]");
     $lastfld=$#Data;
     $subitemcnt=1;
     for($i=1;$i<=$lastfld;$i++) {
        $fieldvalue=$Data[$i];
        $tableListView->SetItem(-item => $itemcnt,-subitem => $subitemcnt,-text => $fieldvalue);
        $subitemcnt++;
     }
     $rowcnt++;
     $itemcnt++;
   }
}

sub Load_DataTypes {

#  $tableListView->Clear;
#  for($i=$lastcol;$i>=0;$i--) {
#    $tableListView->DeleteColumn($i);
#  }
#
#  %Attrib = $db->ColAttributes($db->SQL_COLUMN_TYPE_NAME, $ColName);
#  $tableListView->InsertColumn(-index => 1,-width => 100, -text => "Attributes");
#
#  $tableListView->InsertItem(-item => $itemcnt, -text => "$key");
#  foreach $key(sort keys %Attrib) {
#    print "$Attrib{$key}\n";
#  }

}