#------------------------------------------------------------------#
# Name:        dbgrid.pl
# Purpose:     Database grid application (Perl Win32::ODBC,GUI)
# Author:      Eric Hansen, Information
#              Technology Services, Inc. Dallas, TX
# Contact:     erichansen@itsi-corp.com
# Description: User is displayed a list of available ODBC
#              DataSourceNames. The user double clicks a DSN
#              and then is displayed a list of available tables.
#              The user then double clicks a tablename, afterwhich
#              a listview is loaded with all records from that
#              table. The user can then browse the rows and columns
#              from that database table. No rows are updated. This
#              is a display only database interface.  Future
#              releases may provide selective data export features
#              (i.e. selected rows, selected columns), and sorting.
#------------------------------------------------------------------#
use Win32::GUI;
use Win32::ODBC;

# Hide the Dos Window
($DOShwnd, $DOShinstance) = GUI::GetPerlWindow();
GUI::Hide($DOShwnd);

#-------------------------#
#    Window Controls
#-------------------------#

$W = new GUI::Window(
    -name => "Window",
    -text => "dbgrid.pl - Database Grid Application (Win32::ODBC,Win32::GUI)",
    -width => 510,
    -height => 310,
    -left => 100,
    -top => 100,
);

$LV = new GUI::ListView($W,
         -name => "ListView",
         -left => 10,
         -top => 10,
         -width => 490,
         -height => 290,
);
$LV->TextColor(hex("0000FF"));  # red
$LV->View(1);  # detailed listing
$LV->Hide;

$LB = new GUI::Listbox($W,
         -name => "ListBox",
         -style => WS_CHILD | WS_VISIBLE | WS_VSCROLL | 1,
         -left => 10,
         -top  => 10,
         -foreground => 0x0000FF, # red
         -height => 490,
         -width =>  290,
);

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

$Progress = new GUI::ProgressBar($Status,
    -smooth => 0,     # 0=broken bar, 1=smooth bar
    -width  => $Status->Width/2,
    -height => $Status->Height-3,
    -left   => $Status->Width/2,
    -top    => 2,
);
$Progress->Hide;

#----------------------------#
#      Window Dialog
#----------------------------#
$W->Show;
Load_DSNs();
GUI::Dialog;

#----------------------------#
#   Non-Event Subroutines
#----------------------------#

END {
    if ($db) {$db->Close();}
    GUI::Show($DOShwnd);
}

sub Load_DSNs {
    $DSNmode=1;
    if (%DSNList=ODBC::DataSources()) {
       foreach $Name (keys(%DSNList)) {
          $LB->AddString($Name);
       }
       $Status->Text("Double Click a DataSourceName from the List above");
       $Status->Update;
    }
    else {
       GUI::MessageBox($W,"Can't Determine Available DSN's","Error",16,);
       GUI::MessageBox($W,"Shutting Down the Application","Status",64,);
       exit;
    }
}

sub Load_TBLs {
   $LB->Reset();
   $db = new ODBC($DSN);
   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();
   $lasttbl=$#Tables;
   for($i=0;$i<=$lasttbl;$i++) {
      $LB->AddString($Tables[$i]);
   }
   $Status->Text("Double Click a TableName from the List above");
   $Status->Update;
}

sub Load_ListView {
   $Status->Text("Loading Database Grid...");
   $Status->Update;
   #-------------------------------------------#
   #  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($W,$error,"Error",16,);
      GUI::MessageBox($W,"Shutting Down the Application","Status",64,);
      exit;
   }
   $db->FetchRow();
   $dbcnt = $db->Data("cnt_of_rows");
   #-----------------------------------#
   # Load Listview from the Database
   #-----------------------------------#
   $sqltxt = "SELECT ' ', * FROM $Table";
   $ret = $db->Sql($sqltxt);
   if ($ret) {
      $error=$db->Error();  # database error msg
      GUI::MessageBox($W,$error,"Error",16,);
      GUI::MessageBox($W,"Shutting Down the Application","Status",64,);
      exit;
   }
   $LV->InsertColumn(-index => 0,-width => 50, -text => "Row#");
   @Cols = $db->FieldNames();
   $lastcol=$#Cols;
   for($i=1;$i<=$lastcol;$i++) {
      $columname=$Cols[$i];
      $LV->InsertColumn(-index => $i,-width => 100, -text => $columname);
   }
   $itemcnt=0;
   $rowcnt=1;
   $Progress->Show;
   while ($db->FetchRow()) {
      $pos=($rowcnt*100)/$dbcnt;
      $Progress->SetPos($pos);
      $Progress->Update;
      $LV->InsertItem(-item => $itemcnt, -text => $rowcnt);
      @Data=();  # clear the data array
      @Data = $db->Data();
      $lastfld=$#Data;
      $subitemcnt=1;
      for($i=1;$i<=$lastfld;$i++) {
         $fieldvalue=$Data[$i];
         $LV->SetItem(-item => $itemcnt,-subitem => $subitemcnt,-text => 
$fieldvalue);
         $subitemcnt++;
      }
      $rowcnt++;
      $itemcnt++;
   }
   $db->Close();
   $Progress->Hide;
   $LV->Show;
   $Status->Text("DataSourceName=$DSN, TableName=$Table");
   $Status->Update;
}

#----------------------------#
#      Window Events
#----------------------------#

sub Window_Terminate {
    if ($db) {$db->Close();}
    GUI::Show($DOShwnd);
    exit;
}

sub Window_Resize {
  $LV->Resize($W->ScaleWidth-20,($W->ScaleHeight-20)-$Status->Height);
  $LB->Resize($W->ScaleWidth-20,($W->ScaleHeight-15)-$Status->Height);
  $Status->Resize($W->ScaleWidth,$W->ScaleHeight);
  $Progress->Resize($Status->Width/2,$Status->Height-3);
  $Progress->Move($Status->Width/2,2);
}

sub ListBox_DblClick {
  $sel = $LB->SelectedItem(); # sel is zero based index
  if($sel >= 0) {
     $item = $LB->GetString($sel);
     if ($DSNmode) {
        undef $DSNmode;
        $DSN=$item;
        Load_TBLs();
     }
     else {
        $Table=$item;
        $LB->Hide;
        Load_ListView();
     }
  }
}