Import new assets

*Stmnt No*	*Type*	*Prompt Text*	*Voice Freq*	*Voice Msg*	*Data ID*	*Data Type*	*Max Chars*	*Min Chars*	*Input Dev*	*Next Yes*	*End No*	*Upload prefix*	*Upload Suffix*
1	D	Order number	E			-	10	00	-	02	99	ORD
2	D	Vendor	E			N	30	00	-	03	99	VEN
3	D	Model	E			N	30	00	-	04	99	MOD
4	D	Serial Number	E			-	30	00	-	04	03	SNU

Perl script (New asset)

# Created by Daniel Paessens (Synstar CoE CS)

use DBI;

#
# Prepare used files
#
my $sourcefile    = "new.txt";
my $outputfile    = "newasset.txt";
my $outputhtm   = "newasset.htm";
my $featfile  = "feat.txt";
my $wrongfile    = "wrong.txt";
my $wronghtm   = "wrong.htm";

#
# Prepare Database access
#
$DSN = 'driver=Microsoft Access Driver (*.mdb);dbq=G:\\Public\\SYS\\Customer Service\\Unicenter Files\\asset.mdb';
$dbh = DBI->connect("dbi:ODBC:$DSN",",") ||die("Can't connect to DB: $DBI::errstr");
$dbo = DBI->connect("dbi:Oracle:paradigm","paradigm","Passw0rd") || die("Can't connect to Oracle database: $DBI::errstr");
$sth = $dbh->prepare('select Manuf, Class, Family from Man_mod where Model=?') || die("Can't prepare SQL statement: $DBI::errstr");
$sto = $dbo->prepare("select id from paradigm.net_res where nr_prim_search_key = ?") ||die("Can't prepare SQL statement: $DBI::errstr");

open(FILE,"$sourcefile") || die("Can't open NewAsset.txt: $!");
open(INFILE,">$outputfile") || die("Can't write to newasset.txt: $!");
open(INHTM,">$outputhtm") || die("Can't write to newasset.htm: $!");
open(FEATFILE,">$featfile") || die("Can't write to feat.txt: $!");
open(WRONGFILE,">$wrongfile") || die("Can't write to wrong.txt: $!");
open(WRONGHTM,">$wronghtm") || die("Can't write to wrong.htm: $!");

use Time::gmtime;

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =  localtime(time);

$year = $year + 1900;
$mon = $mon + 1;

#
# Write header into file
#
print INFILE "TABLE Network_Resource\n \tnr_prim_search_key nr_financial_id nr_prim_v_id nr_mfr_id nr_grc_id nr_mdl_id nr_family nr_serial_num nr_rss_id nr_wrty_st_dt nr_inst_dt\n";

print INHTM ('<HTML>'."\n".'<BODY bgcolor="#9cc2e4">'."\n".'<font face="verdana">'."\n");
#top table (title)
print INHTM "<table width=\"702\" cellpadding=\"1\" cellspacing=\"0\" bgcolor=\"#3F2DFF\">\n";
print INHTM "<tr><td>\n";
print INHTM "<table width=\"700\" cellspacing=\"1\" bgcolor=\"#D4D4FA\">\n";
print INHTM "<tr>\n";
print INHTM "<td align=\"left\" valign=\"top\"><font size=\"-2\"><a href=\"http://lofty\">home</a></td>\n";
print INHTM "<td align=\"center\"><strong><font color=\"#0000ED\" size=\"-1\">TNG Unicenter Service Desk</font></BR>\n";
print INHTM "<font size=\"-2\" color=\"#7575FF\">B.E.I. Implementation by Synstar</font></strong></td>\n";
print INHTM "<td align=\"right\" valign=\"top\"><font size=\"-2\"><a href = \"javascript:history.back()\">back</a></td>\n";
print INHTM "</tr>\n";
print INHTM "</table></td></tr></table>\n";
print WRONGHTM ('<HTML>'."\n".'<BODY bgcolor="#9cc2e4">'."\n".'<font face="verdana">'."\n");
#top table (title)
print WRONGHTM "<table width=\"702\" cellpadding=\"1\" cellspacing=\"0\" bgcolor=\"#3F2DFF\">\n";
print WRONGHTM "<tr><td>\n";
print WRONGHTM "<table width=\"700\" cellspacing=\"1\" bgcolor=\"#D4D4FA\">\n";
print WRONGHTM "<tr>\n";
print WRONGHTM "<td align=\"left\" valign=\"top\"><font size=\"-2\"><a href=\"http://lofty\">home</a></td>\n";
print WRONGHTM "<td align=\"center\"><strong><font color=\"#0000ED\" size=\"-1\">TNG Unicenter Service Desk</font></BR>\n";
print WRONGHTM "<font size=\"-2\" color=\"#7575FF\">B.E.I. Implementation by Synstar</font></strong></td>\n";
print WRONGHTM "<td align=\"right\" valign=\"top\"><font size=\"-2\"><a href = \"javascript:history.back()\">back</a></td>\n";
print WRONGHTM "</tr>\n";
print WRONGHTM "</table></td></tr></table>\n";
print WRONGHTM ('<table width="702" cellpadding="1" cellspacing="0" bgcolor="#3F2DFF">'."\n".'<tr>'."\n".'<td>'."\n");
print WRONGHTM ('<table width="700" bgcolor="#35A3D1">'."\n");
print WRONGHTM ('<tr><td bgcolor="#7EBEE4" colspan="4" align="center"><font size="- 1"><strong>Following assets are already existing in database:</strong></font></td></tr>'."\n");

#
# Write Data into New assets file
#
foreach(readline(FILE))
{
       chomp;
       my $prefix = substr($_,0,3);

       if ($prefix eq "ORD")
         {
         $order = substr($_,3);
         $serial= "";
         }
       if ($prefix eq "VEN")
         {
         $vendor= substr($_,3);
         $serial= "";
         }
       if ($prefix eq "MOD")
         {
         $model = substr($_,3);
         $serial= "";
         }
       if ($prefix eq "SNU")
         {
         $serial= substr($_,3);
         }
       if ($serial ne '')
         {
         $sto->execute($serial) || die("Can't execute SQL statement: $DBI::errstr");
         my $id = $sto->fetchrow_array();
         if($id)
           {
           print WRONGFILE "Asset $serial exists already\n";

           print WRONGHTM ('<tr>'."\n");
           print WRONGHTM ('<td align="right"><strong><font size="- 1">Serial:</font></strong></td>'."\n".'<td><font size=" 1">'."$serial".'</font></td>'."\n");
           print WRONGHTM ('<td align="right"><strong><font size="- 1">Model:</font></strong></td>'."\n".'<td><font size="- 1">'."$model".'</font></td>'."\n");
           print WRONGHTM ('</tr>'."\n");
           }
         else
           {
           $sth->execute($model) || die("Can't execute SQL statement: $DBI::errstr");
           my ($Manuf, $Class, $Family);
           # Bind Perl variables to columns:
           $rv = $sth->bind_columns(\$Manuf, \$Class, \$Family);
           while ($sth->fetch)
             {
             print INFILE "\t{ \"$serial\", \"$order\", \"$vendor\", \"$Manuf\", \"$Class\", \"$model\", \"$Family\", \"$serial\", \"414273\", \"$mday/$mon/$year $hour:$min:$sec\", \"$mday/$mon/$year $hour:$min:$sec\" }\n";
             print INHTM ('<table width="702" cellpadding="1" cellspacing="0" bgcolor="#3F2DFF">'."\n".'<tr>'."\n".'<td>'."\n");
             print INHTM ('<table width="700" cellspacing="1" bgcolor="#35A3D1">'."\n");
             print INHTM ('<tr><td align="center" bgcolor="#7EBEE4" colspan="6"><strong><font size="- 1">ASSET:'.$serial.'</font></strong></td></tr>'."\n");
             print INHTM ('<tr>'."\n");
             print INHTM ('<td align="right"><strong><font size="- 1">Order:</font></strong></td>'."\n".'<td><font size="- 1">'."$order".'</font></td>'."\n");
             print INHTM ('<td align="right"><strong><font size="- 1">Class:</font></strong></td>'."\n".'<td><font size="- 1">'."$Class".'</font></td>'."\n");
             print INHTM ('<td align="right"><strong><font size="- 1">Serial:</font></strong></td>'."\n".'<td><font size="- 1">'."$serial".'</font></td>'."\n");        print INHTM ('</tr>'."\n");
             print INHTM ('<tr>'."\n");
             print INHTM ('<td align="right"><strong><font size="- 1">Vendor:</font></strong></td>'."\n".'<td><font size="- 1">'."$vendor".'</font></td>'."\n");
             print INHTM ('<td align="right"><strong><font size="- 1">Model:</font></strong></td>'."\n".'<td><font size="- 1">'."$model".'</font></td>'."\n");
             print INHTM ('<td align="right"><strong><font size="- 1">Rss_Id:</font></strong></td>'."\n".'<td><font size="- 1">'."414273".'</font></td>'."\n");
             print INHTM ('</tr>'."\n");
             print INHTM ('<tr>'."\n");
             print INHTM ('<td align="right"><strong><font size="- 1">Manufacturer:</font></strong></td>'."\n".'<td><font size="- 1">'."$Manuf".'</font></td>'."\n");
             print INHTM ('<td align="right"><strong><font size="- 1">Family:</font></strong></td>'."\n".'<td><font size="- 1">'."$Family".'</font></td>'."\n");
             print INHTM ('<td align="right"><strong><font size="-1">Date in:</font></strong></td>'."\n".'<td><font size="- 1">'."$mday/$mon/$year $hour:$min:$sec".'</font></td>'."\n");
             print INHTM ('</tr>'."\n");
             print INHTM ('</table>'."\n");
             print INHTM ('</tr>'."\n".'</td>'."\n".'</table>');
             print INHTM ('</BR>'."\n");
             }
              if ($Family =~ "3.0")
             {
             print FEATFILE "$serial\n";
             }
           }
       }
}

#bottom table (BACK button)
print INHTM "<table width=\"702\" cellpadding=\"1\" cellspacing=\"0\" bgcolor=\"#3F2DFF\">\n";
print INHTM "<tr><td>\n";
print INHTM "<table width=\"700\" cellspacing=\"1\" bgcolor=\"#D4D4FA\">\n";
print INHTM "<tr>\n";
print INHTM "<td><strong><font size=\"-1\"><a href = \"javascript:history.back()\">Go back</a></font></strong></td>\n";
print INHTM "</tr>\n";
print INHTM "</table>\n</tr></td>\n</table>\n";
#close html
print INHTM ('</font>'."\n".'</body>'."\n".'</html>'."\n");
print WRONGHTM ('</table></td></td></table>'."\n");
print WRONGHTM "<table width=\"702\" cellpadding=\"1\" cellspacing=\"0\" bgcolor=\"#3F2DFF\">\n";
print WRONGHTM "<tr><td>\n";
print WRONGHTM "<table width=\"700\" cellspacing=\"1\" bgcolor=\"#D4D4FA\">\n";
print WRONGHTM "<tr>\n";
print WRONGHTM "<td><strong><font size=\"-1\"><a href = \"javascript:history.back()\">Go back</a></font></strong></td>\n";
print WRONGHTM "</tr>\n";
print WRONGHTM "</table>\n</tr></td>\n</table>\n";
#close html
print WRONGHTM ('</font></body></html>');
#
# Closing used files
#
close(FILE);
close(INFILE);
close(INHTM);
close(WRONGHTM);
close(FEATFILE);

#
# Closing DB connections
#
$sto->finish or die $sto->errstr;
$sth->finish or die $sth->errstr;
$dbh->disconnect() || die("Error disconnecting: $DBI::errstr");

Perl script (PC Features)

# Created by Daniel Paessens (Synstar CoE CS)

use DBI;
#
# Prepare used files
#
my $sourcefile    = "feat.txt";
my $outputfile    = "feat1.txt";

#
# Prepare Database access
#
my $dbh = DBI->connect("dbi:Oracle:paradigm","paradigm","Passw0rd") || die("Can't connect to Oracle database: $DBI::errstr");

open(FILE,"$sourcefile") || die("Can't open feat.txt: $!");
open(INFILE,">$outputfile") || die("Can't open feat1.txt: $!");

#
# Write header into file
#

#
# Write Data into New assets file
#

foreach(readline(FILE))
       {
       chomp;
       my $serial     = $_;
       my $sth = $dbh->prepare("select id,nr_mdl_id from paradigm.net_res where nr_prim_search_key = '$serial'") ||die("Can't prepare SQL statement: $DBI::errstr");
       $sth->execute || die("Can't execute SQL statement: $DBI::errstr");
       my ($id, $model);
       # Bind Perl variables to columns:
       $rv = $sth->bind_columns(\$id, \$model);
       while ($sth->fetch){
       if($id)
             {
             print INFILE "I$id\nM$model\n";
             }
       }
       }
close(FILE);
close(INFILE);

$dbh->disconnect() || die("Error disconnecting: $DBI::errstr");

# Created by Daniel Paessens (Synstar CoE CS)

use DBI;

#
# Prepare used files
#
my $sourcefile    = "feat1.txt";
my $outputfile    = "f2.txt";
my $accessfile     = "";

#
# Prepare Database access
#
my $DSN = 'driver=Microsoft Access Driver (*.mdb);dbq=G:\\Public\\SYS\\Customer Service\\Unicenter Files\\asset.mdb';
$dbh = DBI->connect("dbi:ODBC:$DSN",",") ||die("Can't connect to DB: $DBI::errstr");
open(FILE,"$sourcefile") || die("Can't open feat.txt: $!");
open(INFILE,">$outputfile") || die("Can't open f2.txt: $!");

#
# Write header into file
#
print INFILE "TABLE Computer_Extension\n \text_asset cpex_processor_speed cpex_physical_memory cpex_hard_disk_space cpex_cd_rom cpex_media_type    \n";

#
# Write Data into New assets file
#
foreach(readline(FILE))
       {
       chomp;
       my $prefix   = substr($_,0,1);
       if ($prefix =~ I)
             {
             $id  = substr($_,1);
             }
       if ($prefix =~ M)
             {
             $model  = substr($_,1);
             my $sth = $dbh->prepare("select CPU, RAM, BUS, HDD, CDrom, HDDi,HDDm from AssetF where $model=ID")
             || die("Can't prepare SQL statement: $DBI::errstr");
             $sth->execute || die("Can't execute SQL statement: $DBI::errstr");
             my ($CPU, $RAM, $BUS, $HDD, $CDrom, $HDDi,$HDDm);
             # Bind Perl variables to columns:
             $rv = $sth->bind_columns(\$CPU, \$RAM, \$BUS, \$HDD, \$CDrom, \$HDDi,\$HDDm);
             while ($sth->fetch) {
             print INFILE "\t{ \"$id\", \"$CPU\", \"$RAM\", \"$HDD\", \"$CDrom\", \"$HDDi\" }\n";
             }
             }
       }

#
# Closing used files
#
close(FILE);
close(INFILE);
close(FEATFILE);

#
# Closing DB connections
#
$dbh->disconnect() || die("Error disconnecting: $DBI::errstr");

Batch file

@echo off
Rem * Written by Daniel Paessens (Synstar CoE CS)
Rem * Version 0.1
Rem * File: c:\scripts\NewPC\new.bat

Rem * Import new assets into DB
perl c:\Scripts\NewPC\NewAsset.pl

Rem - Transfer file
c:\scripts\pscp -q -pw scissors c:\scripts\newpc\newasset.htm root@lofty:/var/www/html/assetlog
c:\scripts\pscp -q -pw scissors c:\scripts\newpc\newasset.txt root@lofty:/var/www/html/assetlog
c:\scripts\pscp -q -pw scissors c:\scripts\newpc\wrong.htm root@lofty:/var/www/html/assetlog

Rem - Launch remote script
c:\scripts\plink -ssh -pw scissors root@lofty /opt/script/newasset.sh

Rem * Import features of Computers into DB
c:\scripts\sleep.com 20
perl c:\Scripts\NewPC\AssetF.pl
perl c:\Scripts\NewPC\Feat.pl

Rem - Transfer file
c:\scripts\pscp -q -pw scissors c:\scripts\newpc\f2.txt root@lofty:/var/www/html/assetlog

Rem - Launch remote script
c:\scripts\plink -ssh -pw scissors root@lofty /opt/script/feat.sh

Rem - Delete temporary files
del c:\scripts\newpc\newasset.htm
del c:\scripts\newpc\newasset.txt
del c:\scripts\newpc\wrong.htm
del c:\scripts\newpc\f2.txt
del c:\scripts\newpc\feat.txt
del c:\scripts\newpc\feat1.txt

batch-file on Lofty

Newasset.sh:

# Written by Daniel Paessens (Synstar CoE)
# Revision:  0.1
# Creation date: 15/05/2003
# File name:       newasset.sh
# File location:  /opt/scripts

pdm_load -i -f /var/www/html/assetlog/newasset.txt

Feat.sh:

# Written by Daniel Paessens (Synstar CoE)
# Revision:  0.1
# Creation date: 15/05/2003
# File name:       feat.sh
# File location:  /opt/scripts

cp /var/www/html/assetlog/f2.txt /var/www/html/assetlog/feat.txt
pdm_load -i -f /var/www/html/assetlog/feat.txt
rm /var/www/html/assetlog/f2.txt -f

Top

USD 5.5.1 Implementation	Powered By Ega's Inc
USD 5.5.1 Implementation	Last updated: 2009-07-10

	Home \| Table of Contents \| Overview Map \| Icon Legend
	USD 5.5.1 implementation
	Daniel Paessens ega@paessens.org