# OS.PL
# ----------
# This is a management system script designed to discover 
# information about both the computer and OS. Once the
# information has been obtained it is then set to a database
# where it is stored.
#
# This script assumes that the database has 2 tables:
#   1) Computer:
#       ID          int(), primary key, autoincrimenting
#       Name        char
#       Processor   char
#       Speed       int
#       OS          char
#       ServicePack char
#       Version     float
#       Build       int
#       MMX         int (used as a boolean or bit)
#       Ram         int
#       PageFile    int
#
#   2) Drives:
#       ID          int, primary key, autoincrimenting
#       Size        int
#       Free        int
#       Drive       char 
#       Type        char
#       Computer    int
# 
#   3) software:
#       ID          int, primary key, autoincrimenting
#       computer    int
#       software    char
#
#   Dave Roth
#   Roth Consulting
#   http://www.roth.net
#
use Win32::AdminMisc;
use Win32::ODBC;
use Win32::TieRegistry ( Delimiter=>"/", ArrayValues=>1 );

#$DSN = "Materiel CPH";
$DSN = "Materiel CPH";
$Machine = Win32::NodeName();

%DriveType = (
    &DRIVE_REMOTE    => "remote",
    &DRIVE_REMOVABLE => "removable",
    &DRIVE_FIXED     => "fixed",
    &DRIVE_CDROM     => "cdrom",
    &DRIVE_RAMDISK   => "ramdisk",
);

# Création du lien ODBC
$database = "DBQ=\\\\INF002\\INFO\$\\matériel.mdb";
$ODBCDriver = "Microsoft Access Driver (*.mdb)";
if( ! Win32::ODBC::ConfigDSN( ODBC_ADD_SYS_DSN, $ODBCDriver, ("DSN=$DSN", "Description=The Configuration Database", $database ) ) ) 
{
        print "Unable to create DSN: " . Win32::ODBC::Error() . "\n";
        exit;
}


# Get memory info...
%Mem = Win32::AdminMisc::GetMemoryInfo();

# Get processor info...
%Processor = Win32::AdminMisc::GetProcessorInfo();

# Get OS info then fix the servicepack and platform values...
%OS = Win32::AdminMisc::GetWinVersion();
( $OS{ServicePack} ) = ( $OS{CSD} =~ /(\d*?)$/ );
$OS{Platform} =~ s/win32_//i;

# Get drive info...
foreach $Drive ( Win32::AdminMisc::GetDrives() )
{
    my $Type = Win32::AdminMisc::GetDriveType( $Drive );
 
    if( DRIVE_REMOTE != $Type )
    {
        $Drives{$Drive} = { size => 0, free => 0 };
 
        if( DRIVE_FIXED == $Type )
        {
            ($Drives{$Drive}->{size}, $Drives{$Drive}->{free} ) = Win32::AdminMisc::GetDriveSpace( $Drive );
        }

        $Drives{$Drive}->{type} = $Type;
        if( DRIVE_REMOVABLE != $Type && DRIVE_CDROM != $Type )
        {
            my %Volume = Win32::AdminMisc::GetVolumeInfo( $Drive );
            $Drives{$Drive}->{filesystem} = $Volume{FileSystemName};
        }
    }
}

if( $db = new Win32::ODBC( $DSN ) )
{
    $Id = GetDbId( $db, $Machine );
    if( ! $Id )
    {
        $db->Sql( "INSERT INTO Computer (Name) VALUES ('$Machine')" );
        $Id = GetDbId( $db, $Machine );
    }
    
    if( $Id )
    {
        $Update = "UPDATE Computer SET " .
               "  ServicePack = '$OS{ServicePack}', " . 
               "  Version     = $OS{Major}.$OS{Minor}, " .
               "  OS          = '$OS{Platform}', " .
               "  Build       = $OS{Build}, " . 
               "  Processor   = '$Processor{ProcessorType}', " .
               "  Speed       = $Processor{Win32ProcessorSpeed}, " .
               "  Ram         = $Mem{RAMTotal}, " . 
               "  PageFile    = $Mem{PageTotal} " .
               " WHERE ID = $Id";
       if( $db->Sql( $Update ) )
       {
         print "Could not update computer info: " . $db->Error() . "\n";
       }
             
       if( $db->Sql( "DELETE FROM Drives WHERE Computer=$Id" ) )
       {
          print "Could not delete drive info: " . $db->Error() . "\n";
       }
       
       foreach $Drive ( sort( keys( %Drives ) ) )
       {
         my $Insert = "INSERT INTO Drives " . 
                      "(Drive,Size,Free,Type,Computer) " . 
                      "VALUES ( '$Drive', $Drives{$Drive}->{size}, " . 
                      "$Drives{$Drive}->{free}, '$DriveType{$Drives{$Drive}->{type}}', " .
                      "$Id )";
         if( $db->Sql( $Insert ) )
         {
            print "Could not insert drive $Drive\n $Drives{$Drive}->{size}\n $Drives{$Drive}->{free}\n $DriveType{$Drives{$Drive}->{type}}\n info: " . $db->Error() . "\n";
         }
       }

       $Registry->Delimiter("/"); # Set delimiter to "/".
       $swKey= $Registry->{"LMachine/Software/Microsoft/Windows/CurrentVersion/Uninstall/"};
       @members=keys( %{$swKey} );
       if( $db->Sql( "DELETE FROM Software WHERE Computer=$Id" ) )
       {
          print "Could not delete software info: " . $db->Error() . "\n";
       }
       
       foreach $soft ( @members )
       {
	 # remplacement des apostrophes par des espaces
	 # suppression des / finaux
	 $soft=~s/'/ /;
	 $soft=~s/\///;
         my $Insert = "INSERT INTO Software " . 
                      "(Computer,Software) " . 
                      "VALUES ( $Id, '$soft' )";
         if( $db->Sql( $Insert ) )
         {
            print "Could not insert software $soft\n  info: " . $db->Error() . "\n";
         }
       }
       
    }
    $db->Close();
}
else
{
    print "Unable to connect to the database: " . Win32::ODBC::Error() . "\n";
}

sub GetDbId
{
    my( $db, $Computer ) = @_;
    my( %Data );    
    if( ! $db->Sql( "SELECT DISTINCT * FROM Computer WHERE Name like '$Computer' " ) )
    {
        if( $db->FetchRow() )
        {
            %Data = $db->DataHash( 'ID' );
        }
    }
    return( $Data{ID} );
}