Here's the complete text of the orcid script. `#!/usr/local/bin/perl use strict; use MARC::Record; use lib qw(/export/home/voyager/perl/lib); use TAMU_MARC; use TAMU_General; use TAMU_DBConnect3 qw(oracle_connect oracle_disconnect get_error); use MIME::Lite; ######################################################################

Set up output file for faculty/staff 'fast'

###################################################################### my $tdate = &today_date(); my $outfile = "/export/home/voyager/scripts/am/orcid_fast.txt"; open(OUT, ">$outfile") or die "Cannot open $outfile\n"; my $from = "voyager\@surprise.tamu.edu"; my $to = "dhahn\@library.tamu.edu,ethel\@library.tamu.edu";

my $to = "hismith\@library.tamu.edu";

my $dbh = &oracle_connect('ora-shared.it', 'cis', 'read') or die "Cannot connect to oracle\n";

my $sql = " SELECT pi.uid32 AS uid32, emp.uin AS id, emp.tamu_netid AS tamu_netid, nvl2(pi.last_name,pi.last_name,emp.last_name) AS last_name, nvl2(pi.first_name,pi.first_name,emp.first_name) AS first_name, emp.middle_name AS middle_name, emp.tamu_preferred_alias AS tamumail, emp.employee_type_name AS employee_type_name, emp.adloc_dept_name AS adloc_dept_name, emp.title AS title FROM patron.employees_retirees emp, patron.person_identifiers pi, patron.library_eresources eres WHERE emp.uin = eres.uin AND emp.employee_type_name != 'Student' AND emp.employee_type_name is not null AND emp.employment_status_name not in ('Deceased','Terminated') AND emp.uin = pi.uin(+) AND emp.title_code != '8491' order by emp.uin ";

print "$sql\n" and die;

my $sth = $dbh->prepare($sql); $sth->execute or die "Cannot execute\n";

while (my $hr = $sth->fetchrow_hashref('NAME_lc')){ print OUT "$hr->{uid32}\t"; print OUT "$hr->{id}\t"; print OUT "$hr->{tamu_netid}\t"; print OUT "$hr->{last_name}\t"; print OUT "$hr->{first_name}\t"; print OUT "$hr->{middle_name}\t"; print OUT "$hr->{tamumail}\t"; print OUT "$hr->{employee_type_name}\t"; print OUT "$hr->{adloc_dept_name}\t"; print OUT "$hr->{title}\n"; } close(OUT); &send_email($outfile,'faculty staff'); ######################################################################

set up new output file for student data

###################################################################### $outfile = "/export/home/voyager/scripts/am/orcid_student.txt"; open(STU, ">$outfile") or die "Cannot open $outfile\n";

$sql = " SELECT DISTINCT pi.uid32 AS uid32, stu.uin AS id, nvl2(pi.last_name,pi.last_name,stu.last_name) AS last_name, nvl2(pi.first_name,pi.first_name,stu.first_name) AS first_name, stu.middle_name AS middle_name, stu.tamu_preferred_alias AS tamumail, class.amdbpatrongroupcode AS patron_group, stu.acad_dept AS acad_dept FROM patron.students stu, patron.classification class, patron.employees_retirees emp, person_identifiers pi, patron.library_eresources eres WHERE pi.uin(+) = stu.uin AND stu.uin = eres.uin AND trim(stu.classification) = trim(class.classification) AND stu.currently_enrolled = 'Y' AND stu.uin = emp.uin(+) AND ((emp.employee_type = 1) OR (emp.uin is null) OR (emp.employee_type is null) OR (emp.employee_type in (2,3) and emp.title_code = '8491') ) AND class.amdbpatrongroupcode = 'grad' order by stu.uin ";

print "$sql\n" and die;

$sth = $dbh->prepare($sql); $sth->execute or die "Cannot execute\n";

while (my $hr = $sth->fetchrow_hashref('NAME_lc')){ print STU "$hr->{uid32}\t"; print STU "$hr->{id}\t"; print STU "$hr->{last_name}\t"; print STU "$hr->{first_name}\t"; print STU "$hr->{middle_name}\t"; print STU "$hr->{tamumail}\t"; print STU "$hr->{patron_group}\t"; print STU "$hr->{acad_dept}\n"; } close(STU); &send_email($outfile,'students'); ###################################################################### sub send_email { my $outfile = shift; my $group = shift; my $msg = MIME::Lite->new(Type => 'multipart/mixed', From => $from, To => $to, Subject => "Orcid list for $group");

$msg->attach(Path => $outfile,
             Type => 'text/plain',
             Disposition => 'attachment') or die "Problem attaching $outfile: $!";

MIME::Lite->send( 'smtp', 'localhost', Timeout => 20 );
$msg->send or die "cannot send message: $!";

} `

TAMULib / fw-registry

Systems Workflow: Extract ids for ORCID ingest (Oracle to MSSQL) #100

Set up output file for faculty/staff 'fast'

my $to = "hismith\@library.tamu.edu";

print "$sql\n" and die;

set up new output file for student data

print "$sql\n" and die;