This script will check if new users are present
into the new created VMTEL-file. Those new users are imported into
a new file which will be imported into the DB by the appropriated
command line.
Will import the
VMTEL-file, check users and import new users.
#
Created by Daniel Paessens (Synstar CoE CS)
# Version 0.2
# File: /opt/script/VMTEL2/newusers.pl
use DBI;
my $insertfile = "newusers.txt";
my $updatefile = "update.txt";
$outfilehtm="newusers.htm";
my $dbh =
DBI->connect("dbi:Oracle:paradigm","paradigm","Passw0rd") ||
die("Can't connect to Oracle database: $DBI::errstr");
open(FILE,"/opt/script/VMTEL.TXT") || die("Can't open VMTEL.TXT:
$!");
open(INFILE,">$insertfile") || die("Can't open $insertfile:
$!");
open(UPFILE,">$updatefile") || die("Can't open $updatefile:
$!");
open(OUTHTM,">$outfilehtm");
print OUTHTM "<HTML>\n<BODY
bgcolor=\"#9cc2e4\"><font face=\"verdana\">\n";
#top table (title)
print OUTHTM "<table width=\"702\" cellpadding=\"1\"
cellspacing=\"0\" bgcolor=\"#3F2DFF\">\n";
print OUTHTM "<tr><td>\n";
print OUTHTM "<table width=\"700\" cellspacing=\"1\"
bgcolor=\"#D4D4FA\">\n";
print OUTHTM "<tr>\n";
print OUTHTM "<td align=\"left\" valign=\"top\"><font
size=\"-2\"><a
href=\"http://lofty\">home</a></td>\n";
print OUTHTM "<td align=\"center\"><strong><font
color=\"#0000ED\" size=\"-1\">TNG Unicenter Service
Desk</font></BR>\n";
print OUTHTM "<font size=\"-2\" color=\"#7575FF\">B.E.I.
Implementation by
Synstar</font></strong></td>\n";
print OUTHTM "<td align=\"right\" valign=\"top\"><font
size=\"-2\"><a href =
\"javascript:history.back()\">back</a></td>\n";
print OUTHTM "</tr>\n";
print OUTHTM
"</table></td></tr></table>\n";
#contents
print OUTHTM "<table width=\"702\" cellpadding=\"1\"
cellspacing=\"0\" bgcolor=\"#3F2DFF\">\n";
print OUTHTM " <tr><td>\n";
print OUTHTM " <table width=\"700\" cellspacing=\"1\"
bgcolor=\"#35A3D1\">\n";
print OUTHTM " <tr><td align=\"center\"
bgcolor=\"#7EBEE4\" colspan=\"4\"><strong><font
size=\"-1\">ADDED
USERS</font></strong></td></tr>\n";
print OUTHTM " <tr
bgcolor=\"#7EBEE4\">\n";
print OUTHTM " <td><font
size=\"-1\"><strong>Public
Phone</strong></font></td>\n";
print OUTHTM " <td><font
size=\"-1\"><strong>First
Name</strong></font></td>\n";
print OUTHTM " <td><font
size=\"-1\"><strong>Last
name</strong></font></td>\n";
print OUTHTM " </tr>\n";
print UPFILE "TABLE Contact\n\tid c_l_id\n";
print INFILE "TABLE Contact\n\tc_public_phone c_userid
c_contact_num c_dept c_expense c_last_name c_first_name
c_ctp_id c_l_id\n";
foreach(readline(FILE))
{
chomp;
my $first =
substr($_,0,4);$first =~ s/\s+$//;
my $second =
substr($_,5,8);$second =~ s/\s+$//;
my $third =
substr($_,14,4);$third =~ s/\s+$//;
my $fourth =
substr($_,19,3);$fourth =~ s/\s+$//;
my $fifth =
substr($_,23,4);$fifth =~ s/\s+$//;
my $sixth =
substr($_,28,20);$sixth =~ s/\s+$//;
my $seventh =
substr($_,48);
if($seventh =~
/^\)/)
{
$sixth
.= ')';
$seventh
= substr($seventh,1);
}
my $sth =
$dbh->prepare("select id from paradigm.ctct where c_userid =
'$second'") ||die("Can't prepare SQL statement:
$DBI::errstr");
$sth->execute ||
die("Can't execute SQL statement: $DBI::errstr");
my $id =
$sth->fetchrow_array();
%locaties=("1616"=>"413971",
"1615"=>"413961",
"1614"=>"413955",
"1606"=>"413969",
"1609"=>"413967",
"1605"=>"413963",
"1602"=>"413965",
"1601"=>"413959",
"1600"=>"413957",
"0"=>"413971",
"90"=>"413974",
"91"=>"413974",
"92"=>"413974",
"93"=>"413974",
"94"=>"413974",
"97"=>"413974");
$key=substr($first,0,1);
if
(exists($locaties{$key}))
{$loc=$locaties{$key}
}
else
{$key=substr($first,0,2);
if (exists($locaties{$key}))
{$loc=$locaties{$key}
}
else
{$key=substr($first,0,4);
if (exists($locaties{$key}))
{$loc=$locaties{$key}
}
else
{$loc="413973"
}
}
}
if($id)
{
#
print ("$first $loc - ") ;
print
UPFILE "\t{ \"$id\", \"$loc\"}\n";
}
else
{
print INFILE "\t{ \"$first\", \"$second\", \"$third\", \"$fourth\",
\"$fifth\", \"$sixth\", \"$seventh\", \"2305\", \"$loc\"
}\n";
print OUTHTM
" <tr>\n";
print OUTHTM
" <td><font
size=\"-1\">$first</font></td>\n";
print OUTHTM
" <td><font
size=\"-1\">$sixth</font></td>\n";
print OUTHTM
" <td><font
size=\"-1\">$seventh</font></td>\n";
print OUTHTM
" </tr>\n";
}
}
print OUTHTM
"<tr><td> </td></tr>";
print OUTHTM " </table>\n";
print OUTHTM " </td></tr>\n";
print OUTHTM "</table>\n";
#bottom table (BACK button)
print OUTHTM "<table width=\"702\" cellpadding=\"1\"
cellspacing=\"0\" bgcolor=\"#3F2DFF\">\n";
print OUTHTM "<tr><td>\n";
print OUTHTM "<table width=\"700\" cellspacing=\"1\"
bgcolor=\"#D4D4FA\">\n";
print OUTHTM "<tr>\n";
print OUTHTM "<td><strong><font size=\"-1\"><a
href = \"javascript:history.back()\">Go
back</a></font></strong></td>\n";
print OUTHTM "</tr>\n";
print OUTHTM
"</table>\n</tr></td>\n</table>\n";
#close html
print OUTHTM "</font></BODY>\n</HTML>\n";
close(OUTHTM);
close(FILE);
close(INFILE);
close(UPFILE);
$dbh->disconnect() || die("Error disconnecting:
$DBI::errstr
-
Result file (Required layout for
import)
TABLE
Contact
c_public_phone c_userid c_contact_num
c_dept c_expense c_last_name c_first_name c_ctp_id c_l_id
{ "0405", "MAGNANIP", "0089", "089", "3029",
"MAGNANI (TEMP) (FEI)", "Paolo
", "2305", "413971" }
{ "8692", "BUECHER", "7145", "F45", "0341",
"BUECHER (OSIRIS)", "Vincent
", "2305", "413973" }
{ "8742", "BESSE", "7144", "F44", "0341",
"BESSE", "Pierre-Henri
", "2305", "413973" }
#
Written by Daniel Paessens (Synstar CoE)
# Version: 0.1
# File:
newusers.sh
# File location: /opt/script/
export ORACLE_HOME=/opt/ora/OraHome1
export ORACLE_BASE=/opt/ora/OraHome1
export ORACLE_SID=paradigm
export NX_ROOT=/opt/CAisd
# Move to directory
cd /opt/script/VMTEL2
# Load files and run script
perl /opt/script/VMTEL2/newusers.pl
cp -f /opt/script/VMTEL2/newusers.htm
/var/www/html/users/newusers.htm
# Load new users into the database
pdm_load -i -f /opt/script/VMTEL2/newusers.txt
1. Hardware Requirements
