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	Y		E			-	10	00	-	02	99
2	D	Order Number	E			-	10	00	-	03	99	ORD
3	D	Vendor	E			N	30	00	-	04	99	VEN
4	D	Model	E			N	30	00	-	05	99	MOD
5	D	Count	E			N	30	00	-	04	99	NUM

# addinvent2.pl
# author: Stefan Joris, Synstar
# created May 6, 2003
# last reviewed May 8, 2003
# version  0.1b
# i/o:
# 1) input scanfile.txt = output of handscanner.
#    file format is plain text with following layout (ex):
#       DAT08052003
#    VEN123
#    ORD5e
#    MOD407487
#    NUM4
#    MOD408025
#    NUM3
#    VEN200
#    ORD6
#    MOD408472
#    NUM1
#
# 2) access database ASSET.MDB (loc c:\scripts) containing query man_mod and table sequence
#
# 3) output addedmodels.txt, plain text file containing model names and sequence numbers
#    example:
#    TABLE Network_Resource
#           nr_prim_search_key nr_financial_id nr_prim_v_id nr_mdl_id nr_mfr_id nr_grc_id nr_family nr_rss_id nr_wrty_st_dt nr_inst_dt
#           {"FILTRE 3M POLARISER_1457","5e","123","407487","400378","2837","0","414273","08/052003 00:00:00","08/05/2003 00:00:00"}
#           {"FILTRE 3M POLARISER_1458","5e","123","407487","400378","2837","0","414273","08/052003 00:00:00","08/05/2003 00:00:00"}
#           {"FILTRE 3M POLARISER_1459","5e","123","407487","400378","2837","0","414273","08/052003 00:00:00","08/05/2003 00:00:00"}
#           {"FILTRE 3M POLARISER_1460","5e","123","407487","400378","2837","0","414273","08/052003 00:00:00","08/05/2003 00:00:00"}
#           {"DESKPRO C6733_1461","5e","123","408025","400411","2807","3","414273","08/052003 00:00:00","08/05/2003 00:00:00"}
#           {"DESKPRO C6733_1462","5e","123","408025","400411","2807","3","414273","08/052003 00:00:00","08/05/2003 00:00:00"}
#           {"DESKPRO C6733_1463","5e","123","408025","400411","2807","3","414273","08/052003 00:00:00","08/05/2003 00:00:00"}
#           {"DESKJET DJ895CXI_1464","6","200","408472","400449","464300","4","414273","08/05/2003 00:00:00","08/05/2003 00:00:00"}

use DBI;

# fill memory tables with records from scanfile.txt
open(SCANFILE,"scanfile.txt");
foreach(readline(SCANFILE))
{chomp;
  $prefix=substr($_,0,3);
  s/^...//;
  $cdat=$_ if $prefix eq "DAT";
  $cven=$_ if $prefix eq "VEN";
  $cord=$_ if $prefix eq "ORD";
  if ($prefix eq "MOD")
    {$cmodel=$_;
     $ven{$cmodel}=$cven unless (exists($ven{$cmodel}));
     $ord{$cmodel}=$cord unless (exists($ord{$cmodel}));
     $dat{$cmodel}=$cdat unless (exists($dat{$cmodel}));
    }
  $num{$cmodel}=$_ if $prefix eq "NUM";
}
close(SCANFILE);

# open access database and prepare select commands
$DSN='driver=Microsoft Access Driver (*.mdb);dbq=G:\\Public\\SYS\\Customer Service\\Unicenter Files\\asset.mdb';
$dbh=DBI->connect("dbi:ODBC:$DSN") or die $DBI::errstr;
$find_model=$dbh->prepare('select mdl_sym, model, manuf, class, family from man_mod where model = ?') or die $find_model->errstr;
$find_sequence=$dbh->prepare('select sequence from sequence where id = 1') or die $find_sequence->errstr;
$update_sequence=$dbh->prepare('update sequence set sequence = sequence+1 where id = 1') or die $update_sequence->errstr;

# retrieve next sequence nr from table sequence
$find_sequence->execute or die $find_sequence->errstr;
$next_sequence=$find_sequence->fetchrow_array();

# write changes in textfile and update next sequence in table
open(ADDEDMODELS,">addedmodels.txt");
print ADDEDMODELS ("TABLE Network_Resource\n \tnr_prim_search_key nr_financial_id nr_prim_v_id nr_mdl_id ".
                   "nr_mfr_id nr_grc_id nr_family nr_rss_id nr_wrty_st_dt nr_inst_dt\n");
foreach (keys(%num))
  {$find_model->execute($_) or die $find_model->errstr;
   @data=$find_model->fetchrow_array();
   my $numbersfound=@data;
   next unless $numbersfound;
   for($y=0;$y<$num{$_};$y++)
       {printf ADDEDMODELS ("\t{\"%s_%d\",\"%s\",\"%s\",\"%d\",\"%d\",\"%d\",\"%d\",\"414273\"",
                            $data[0],$next_sequence++,$ord{$_},$ven{$_},$data[1],$data[2],$data[3],$data[4]);
        print ADDEDMODELS (",\"".substr($dat{$_},0,2)."/".substr($dat{$_},2,2)."/".substr($dat{$_},4,4)." 00:00:00\"");
        print ADDEDMODELS (",\"".substr($dat{$_},0,2)."/".substr($dat{$_},2,2)."/".substr($dat{$_},4,4)." 00:00:00\"}\n");
        $update_sequence->execute() or die $update_sequence->errstr;
       }
  }

#close files and network connections
close(ADDEDMODELS);
$find_model->finish or die $find_model->errstr;
$find_sequence->finish or die $find_sequence->errstr;
$update_sequence->finish or die $update_sequence->errstr;
$dbh->disconnect or die $DBI::errstr;

#debug info (its okay to delete this part)
#print ("Done!\ncontents of input file scanfile.txt:\n");
#open(SCANFILE,"scanfile.txt"); print @sd = <SCANFILE> ; close(SCANFILE);
#print("\ncontents of output file addedmodels.txt:\n");
#open(ADDEDMODELS,"addedmodels.txt"); print @sd = <ADDEDMODELS>; close(ADDEDMODELS);
#print "next sequence number set to $next_sequence";

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

Rem * Import new assets into DB
perl c:\Scripts\bulk\addinvent2.pl

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

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

Rem - Remove temporary files
del c:\scripts\bulk\addedmodels.txt

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

pdm_load -i -f /var/www/html/users/addedmodels.txt