Return to OAIcataloging.pm CVS log

Up to [LON-CAPA] / nsdl / harvestsmete

SMETE side harvest code for LON-CAPA

#!/usr/local/bin/perl -w

use strict;

use Getopt::Std;

use DBI;
use DBD::ODBC;

my $DBI_DSN='dbi:ODBC:needs2.odbc';
my $DBI_USER='smete_user';
my $DBI_PWD='needsmete';
my $dbh;

sub SP_ct_key {
	my ($dbh,$name) = @_;
	# Fetch the ct_key number from the contrib_type table
	my @row_ary = $dbh->selectrow_array(q{SELECT c.ct_key FROM contrib_type c WHERE c.name = ?}, undef, $name);
	my $ct_key = $row_ary[0];
	#       print $ct_key . "\n";
	return $ct_key;
}

# Find general_key given a title   
# return undef if nothing found
# Usage: OAIc_loexists($dbh,$title)
sub OAIc_loexists {
	my ($dbh, $title) = @_;
	my @row_ary = $dbh->selectrow_array(q{SELECT logeneral.general_key FROM logeneral logeneral WHERE logeneral.title1 = ?}, undef, $title);
	if ($row_ary[0]) {
		return $row_ary[0];
	} else {
	return undef;
	}
}

# Generate a key given a field name
# e.g., my $key = &OAIc_key ($dbh,$field_name);
sub OAIc_key {
	my ($dbh,$field_name) = @_;
	# Fetch the current key number from the KEYS table
	my @row_ary = $dbh->selectrow_array(q{SELECT k.key_value FROM keys k WHERE k.field_name = ?}, undef, $field_name);
	# Increment the value by 1
	my $key = $row_ary[0] + 1;
	#       print $key . "\n";
	# Now update the table with the new value
	my $rc = $dbh->do(q{UPDATE keys SET key_value = ?, mod_date=GetDate() FROM keys k WHERE k.field_name = ?}, undef, $key, $field_name) || warn "Unable to update key value in table keys: $dbh->errstr\n";
	$dbh->commit;
	return $key;
}

# Generate a needs number "nn"
# e.g., my $nn = &OAIc_nn ( '2000', '01', 1000 );
sub OAIc_nn {
	my ( $pubYear, $pubMonth, $lo_key ) = @_;
	# Format lo_key to be 10 characters long
	$lo_key = sprintf("%10d",$lo_key);
	# Replace leading spaces with a 0
	$lo_key =~ tr/ /0/;
	my $nn = sprintf("%s_%s_%s", $pubYear, $pubMonth, $lo_key );
	#       print $nn . "\n";
        return $nn;
}

# Update lo into tables
sub OAIc_update_lo {
	my ($dbh,$lo_key, $needs_number, $general_key, $title, $language, $description, $image, $pub_month, $pub_year, $keywords, $submitter_key, $author, $publisher, $affiliate, $contributor, $format, $platform, $os, $version, $date, $url, $ped_lcontext, $ped_enduserrole, $author_reg_key, $publisher_reg_key) = @_;
	my $rc;
	# UPDATE [needs_v2_1]..logeneral
	$rc = $dbh->do(q{UPDATE logeneral SET title1 = ?, language1 = ?, description = ?, image = ?, keywords = ?, modification_date = GetDate(), pub_month = ?, pub_year = ? WHERE general_key = ?}, undef, $title, $language, substr($description,0,1024), $image, $keywords, $pub_month, $pub_year, $general_key);
	if (!$rc) {
		$dbh->rollback;
		$dbh->disconnect;
		die "Unable to update record into logeneral: $dbh->errstr\n";
	}
	# UPDATE [needs_v2_1]..lo
	$rc = $dbh->do(q{UPDATE lo SET needs_number = ?, modification_date = GetDate() WHERE lo_key = ?}, undef, $needs_number, $lo_key);
	if (!$rc) {
	$dbh->rollback;
	$dbh->disconnect;
	die "Unable to update record into lo: $dbh->errstr\n";
	}
	# UPDATE [needs_v2_1]..contrib_role2
	$rc = $dbh->do(q{UPDATE contrib_role2 SET modification_date = GetDate() WHERE lo_key = ?}, undef, $lo_key);
	if (!$rc) {
	$dbh->rollback;
	$dbh->disconnect;
	die "Unable to update contrib_role2: $dbh->errstr\n";
	}
	# UPDATE [needs_v2_1]..cw_search
	$rc = $dbh->do(q{UPDATE cw_search SET title = ?, summary = ?, author_text = ?, subject_heading_text = ?, publisher_text = ?, affiliate_text = ?, platform_text = ?, contributor_text = ?, modification_date = GetDate() WHERE lo_key = ?}, undef, $title, substr($description,0,1024), $author, $keywords, $publisher, $affiliate, $platform, $contributor, $lo_key);
	if (!$rc) {
	$dbh->rollback;
	$dbh->disconnect;
	die "Unable to update record into cw_search: $dbh->errstr\n";
	}
	# UPDATE [needs_v2_1]..lo_pedagogy
	$rc = $dbh->do(q{UPDATE lo_pedagogy SET modification_date = GetDate() WHERE lo_key = ?}, undef, $lo_key);
	if (!$rc) {
	$dbh->rollback;
	$dbh->disconnect;
	die "Unable to update record into lo_pedagogy: $dbh->errstr\n";
	}
	# UPDATE [needs_v2_1]..pedagogy
	$rc = $dbh->do(q{UPDATE pedagogy SET ped_title = ?, ped_desc = ?, ped_lcontext = ?, ped_lcontext_other = ?, ped_difficulty = ?, ped_restype = ?, ped_restype_other = ?, ped_use = ?, ped_use_other = ?, ped_intlevel = ?, ped_inttype = ?, ped_semdensity = ?, ped_enduserrole = ?, ped_agerange = ?, ped_ltime = ?, ped_coverage = ?, ped_intlanguage = ?, created_by = ?, modification_date = GetDate() WHERE pedagogy_key = (SELECT pedagogy_key FROM lo_pedagogy WHERE lo_key = ?)}, undef, $title, $author, $ped_lcontext, '', '', '', '', '', $ped_enduserrole, '', '', '', '', $ped_lcontext, '', '', '1-en-US', $submitter_key, $lo_key);
	if (!$rc) {
	$dbh->rollback;
	$dbh->disconnect;
	die "Unable to update record into pedagogy: $dbh->errstr\n";
	}
	# UPDATE [needs_v2_1]..lo_platform
	$rc = $dbh->do(q{UPDATE lo_platform SET modification_date = GetDate() WHERE lo_key = ?}, undef, $lo_key);
	if (!$rc) {
	$dbh->rollback;
	$dbh->disconnect;
	die "Unable to update record into lo_platform: $dbh->errstr\n";
	}
	# UPDATE [needs_v2_1]..platform
	$rc = $dbh->do(q{UPDATE platform SET format = ?, type = ?, version = ?, location_url = ?, cost = ?, os = ?, status = ?, modification_date = GetDate(), created_by = ? WHERE platform_key = (SELECT platform_key FROM lo_platform WHERE lo_key = ?)}, undef, $format, $platform, $version, $url, '1-Freeware', $os, 'complete', $submitter_key, $lo_key);
	if (!$rc) {
	$dbh->rollback;
	$dbh->disconnect;
	die "Unable to update record into lo_platform: $dbh->errstr\n";
	}
	# Upon success commit
	$dbh->commit;
	return $rc;
}

# Insert lo into tables
sub OAIc_insert_lo {
	my ($dbh,$lo_key, $needs_number, $general_key, $title, $language, $description, $image, $pub_month, $pub_year, $keywords, $submitter_key, $author, $publisher, $affiliate, $contributor, $format, $platform, $os, $version, $date, $url, $ped_lcontext, $ped_enduserrole, $author_reg_key, $publisher_reg_key, $affiliate_key) = @_;
	my $rc;
	# INSERT INTO [needs_v2_1]..logeneral
	$rc = $dbh->do(q{INSERT INTO logeneral (general_key, title1, language1, description, image, keywords, creation_date, modification_date, pub_month, pub_year, submitter_key) VALUES (?,?,?,?,?,?,GetDate(),GetDate(),?,?,?)}, undef, $general_key, $title, $language, substr($description,0,1024), $image, $keywords, $pub_month, $pub_year, $submitter_key);
	if (!$rc) {
		$dbh->rollback;
		$dbh->disconnect;
		die "Unable to insert new record into logeneral: $dbh->errstr\n";
	}
	# INSERT INTO [needs_v2_1]..lo
	$rc = $dbh->do(q{INSERT INTO lo (lo_key, needs_number, status, general_key, lo_commit, community, creation_date, modification_date) VALUES (?,?,?,?,?,?,GetDate(),GetDate())}, undef, $lo_key, $needs_number, 'active', $general_key, 1, 'eng');
	if (!$rc) {
	$dbh->rollback;
	$dbh->disconnect;
	die "Unable to insert new record into lo: $dbh->errstr\n";
	}
	# Get a cr_key
	my $cr_key = OAIc_key($dbh,'cr_key');
	# INSERT INTO [needs_v2_1]..contrib_role2
	# Add author contribution (ct_key=1)
	$rc = $dbh->do(q{INSERT INTO contrib_role2 (cr_key, lo_key, reg_key, ct_key, primary_member, order_by, created_by, creation_date, modification_date, status) VALUES (?,?,?,?,?,?,?,GetDate(),GetDate(),?)}, undef, $cr_key, $lo_key, $author_reg_key, 1, 1, 1, $submitter_key, 'active');
	# Add publisher contribution (ct_key=2)
	$cr_key = OAIc_key($dbh,'cr_key');
	$rc = $dbh->do(q{INSERT INTO contrib_role2 (cr_key, lo_key, reg_key, ct_key, primary_member, order_by, created_by, creation_date, modification_date, status) VALUES (?,?,?,?,?,?,?,GetDate(),GetDate(),?)}, undef, $cr_key, $lo_key, $publisher_reg_key, 2, 1, 1, $submitter_key, 'active');
	# Add submitter contribution (ct_key=3)
	$cr_key = OAIc_key($dbh,'cr_key');
	$rc = $dbh->do(q{INSERT INTO contrib_role2 (cr_key, lo_key, reg_key, ct_key, primary_member, order_by, created_by, creation_date, modification_date, status) VALUES (?,?,?,?,?,?,?,GetDate(),GetDate(),?)}, undef, $cr_key, $lo_key, $submitter_key, 3, 1, 1, $submitter_key, 'active');
	# Add contact contribution (ct_key=4)
	$cr_key = OAIc_key($dbh,'cr_key');
	$rc = $dbh->do(q{INSERT INTO contrib_role2 (cr_key, lo_key, reg_key, ct_key, primary_member, order_by, created_by, creation_date, modification_date, status) VALUES (?,?,?,?,?,?,?,GetDate(),GetDate(),?)}, undef, $cr_key, $lo_key, $author_reg_key, 4, 1, 1, $submitter_key, 'active');
	if (!$rc) {
	$dbh->rollback;
	$dbh->disconnect;
	die "Unable to insert new record into contrib_role2: $dbh->errstr\n";
	}
	# INSERT INTO [needs_v2_1]..cw_search
	$rc = $dbh->do(q{INSERT INTO cw_search (lo_key, title, summary, author_text, subject_heading_text, publisher_text, affiliate_text, platform_text, contributor_text, creation_date, modification_date) VALUES (?,?,?,?,?,?,?,?,?,GetDate(),GetDate())}, undef, $lo_key, $title, substr($description,0,1024), $author, $keywords, $publisher, $affiliate, $platform, $contributor);
	if (!$rc) {
	$dbh->rollback;
	$dbh->disconnect;
	die "Unable to insert new record into cw_search: $dbh->errstr\n";
	}
	# INSERT INTO [needs_v2_1]..lo_pedagogy
	my $loped_key = OAIc_key($dbh,'loped_key');
	my $pedagogy_key = OAIc_key($dbh,'pedagogy_key');
	$rc = $dbh->do(q{INSERT INTO lo_pedagogy (loped_key, lo_key, pedagogy_key, order_by, creation_date, modification_date, status) VALUES (?,?,?,?,GetDate(),GetDate(),?)}, undef, $loped_key, $lo_key, $pedagogy_key, 1, 'complete');
	if (!$rc) {
	$dbh->rollback;
	$dbh->disconnect;
	die "Unable to insert new record into lo_pedagogy: $dbh->errstr\n";
	}
	# INSERT INTO [needs_v2_1]..pedagogy
	$rc = $dbh->do(q{INSERT INTO pedagogy (pedagogy_key, ped_title, ped_desc, ped_lcontext, ped_lcontext_other, ped_difficulty, ped_restype, ped_restype_other, ped_use, ped_use_other, ped_intlevel, ped_inttype, ped_semdensity, ped_enduserrole, ped_agerange, ped_ltime, ped_coverage, ped_intlanguage, created_by, creation_date, modification_date) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,GetDate(),GetDate())}, undef, $pedagogy_key, $title, '', $ped_lcontext, '', '', '', '', '', '', '', '', '', $ped_enduserrole, '', '', '', '1-en-US', $submitter_key);
	if (!$rc) {
	$dbh->rollback;
	$dbh->disconnect;
	die "Unable to insert new record into pedagogy: $dbh->errstr\n";
	}
	# INSERT INTO [needs_v2_1]..lo_platform
	my $loplat_key = OAIc_key($dbh,'loplat_key');
	my $platform_key = OAIc_key($dbh,'platform_key');
	$rc = $dbh->do(q{INSERT INTO lo_platform (loplat_key, lo_key, platform_key, order_by, creation_date, modification_date, status) VALUES (?,?,?,?,GetDate(),GetDate(),'complete')}, undef, $loplat_key, $lo_key, $platform_key, 1);
	if (!$rc) {
	$dbh->rollback;
	$dbh->disconnect;
	die "Unable to insert new record into lo_platform: $dbh->errstr\n";
	}
	# INSERT INTO [needs_v2_1]..platform
	$rc = $dbh->do(q{INSERT INTO platform (platform_key, format, type, os, version, location_url, cost, status, creation_date, modification_date, created_by) VALUES (?,?,?,?,?,?,?,?,GetDate(),GetDate(),?)}, undef, $platform_key, $format, $platform, $os, $version, $url, '1-Freeware', 'complete', $submitter_key);
	if (!$rc) {
	$dbh->rollback;
	$dbh->disconnect;
	die "Unable to insert new record into lo_platform: $dbh->errstr\n";
	}
	# INSERT INTO [needs_v2_1]..cw_affiliate
	my $cw_affiliate_key = OAIc_key($dbh,'cw_affiliate_key');
	$rc = $dbh->do(q{INSERT INTO cw_affiliate (cw_affiliate_key, cw_key, affiliate_key, creation_date, modification_date, order_by) VALUES (?,?,?,GetDate(),GetDate(),1)}, undef, $cw_affiliate_key, $lo_key, $affiliate_key);
	if (!$rc) {          
		$dbh->rollback;
		$dbh->disconnect;
		die "Unable to insert new record into cw_affiliate: $dbh->errstr \n";          
	}
	# Upon success commit
	$dbh->commit;
	return $rc;
}

sub OAIc_personexists {
	my ($dbh,$email) = @_;
	my @person_row_ary = $dbh->selectrow_array(q{SELECT person.person_key FROM person person WHERE person.email = ?}, undef, $email);
	if ($person_row_ary[0]) {
		my @reg_row_ary = $dbh->selectrow_array(q{SELECT u.reg_key FROM [user] u WHERE u.person_key = ?}, undef, $person_row_ary[0]);
		if ($reg_row_ary[0]) {
			return $reg_row_ary[0];
		} else {
			return undef;
		}
	} else {
	return undef;
	}
}

sub OAIc_insert_person {
	my ($dbh,$reg_key,$person_key,$org_key,$submitter_key,$lastname,$firstname,$email,$company) = @_;
	my $rc = $dbh->do(q{INSERT INTO [user] (reg_key,person_key,org_key,type,created_by,creation_date,modification_date,status) VALUES (?,?,?,?,?,GetDate(),GetDate(),'active')}, undef, $reg_key, $person_key, $org_key, 'person', $submitter_key);
	if (!$rc) {          
		$dbh->rollback;
		$dbh->disconnect;
		die "Unable to insert new person into user: $dbh->errstr \n";
	}

	$rc = $dbh->do(q{INSERT INTO person (person_key,email,lastname,firstname,company,creation_date,created_by,olderThan13) VALUES (?,?,?,?,?,GetDate(),?,1)}, undef, $person_key, $email, $lastname, $firstname, $company, $submitter_key);
	if (!$rc) {          
		$dbh->rollback;
		$dbh->disconnect;
		die "Unable to insert new person into person: $dbh->errstr \n";
	}

	return $rc;
}
return 1;