#!/usr/local/bin/perl

#
# MTNcataloging.pl
# SMETE Cataloging Functions in PERL
#
# Andy Dong <adong@smete.org> 05/10/2001
#

use strict;
use DBI;
use DBD::ODBC;

# Configuration

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

# Generate a key given a field name
# e.g., my $key = &SP_key ($dbh,$field_name);
sub SP_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 lo_key in table keys: $dbh->errstr\n";
	$dbh->commit;
	return $key;
}

# Generate a needs number "nn"
# e.g., my $nn = &SP_nn ( '2000', '01', 1000 );
sub SP_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;
}

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;
}

# Update lo into tables
sub SP_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, $platform, $date, $url, $ped_lcontext) = @_;
	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, $description, $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(), reg_key=4275 WHERE lo_key = ? AND reg_key=3328}, 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, $description, $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, '', '', '27-Collection', '', '3-Instructor-guided classroom instruction', '', '', '', '', '', $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 = ?, modification_date = GetDate(), created_by = ? WHERE platform_key = (SELECT platform_key FROM lo_platform WHERE lo_key = ?)}, undef, '1-Generate Automatically from MIME Type', '5-WWW', $date, $url, '1-Freeware', $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;
}

# Find general_key given a title
# return undef if nothing found
sub SP_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;
	}
}

# Insert lo into tables
sub SP_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, $platform, $date, $url, $ped_lcontext) = @_;
	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, $description, $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 = &SP_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, 3329, 1, 1, 1, $submitter_key, 'active');
	# Add publisher contribution (ct_key=2)
	$cr_key = &SP_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, 3681, 2, 1, 1, $submitter_key, 'active');
	# Add submitter contribution (ct_key=3)
	$cr_key = &SP_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 = &SP_key($dbh,'cr_key');
	# Marcia Mardis is [user].reg_key=4275 and person.person_key=3420
	$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, 4275, 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, $description, $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 = &SP_key($dbh,'loped_key');
	my $pedagogy_key = &SP_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;
	$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, $author, $ped_lcontext, 'Professional Development', '', '27-Collection', '', '3-Instructor-guided classroom instruction', '', '', '', '', '', '', '', '', '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 = &SP_key($dbh,'loplat_key');
	my $platform_key = &SP_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, version, location_url, cost, creation_date, modification_date, created_by) VALUES (?,?,?,?,?,?,GetDate(),GetDate(),?)}, undef, $platform_key, '1-Generate Automatically from MIME Type', '5-WWW', $date, $url, '1-Freeware', $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 = &SP_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, 10);
	if (!$rc) {         
		$dbh->rollback;   
		$dbh->disconnect;  
		die "Unable to insert new record into cw_affiliate: $dbh->errstr\n";
	}
	$cw_affiliate_key = &SP_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, 12);
	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;
}