#!/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 lo.id FROM learning_object lo WHERE lo.title = ?}, 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 MATTI (special to handle version.installation_note);
sub OAIc_update_matti {
	my ($dbh, $id, $installation_note) = @_;
	my $rc;
	# UPDATE [needs_3_1]..version
	printf("Update installation note for id = %s\n", $id);
	$rc = $dbh->do(q{UPDATE version SET installation_note = ? WHERE resource_id = ?}, undef, substr($installation_note,0,1024), $id);
	if (!$rc) {
	$dbh->rollback;
	$dbh->disconnect;
	die "Unable to update record into lo_platform: $dbh->errstr\n";
	}
}

# Update lo into tables
sub OAIc_update_lo {
	my ($dbh, $general_key, $title, $language, $description, $image, $pub_month, $pub_year, $keywords, $submitter_key, $author, $publisher, $collection, $format, $platform, $os, $url, $ped_lcontext, $ped_enduserrole, $author_reg_key, $publisher_reg_key) = @_;
	my $rc;
	# UPDATE [needs_3_1]..version
	$rc = $dbh->do(q{UPDATE version SET media_format_id = ?, platform_type_id = ?, location_url = ?, operating_system = ?, modification_date = GetDate(), reporter_id = ? WHERE resource_id = ?}, undef, $format, $platform, $url, $os, $submitter_key, $general_key);
	if (!$rc) {
	$dbh->rollback;
	$dbh->disconnect;
	die "Unable to update record into lo_platform: $dbh->errstr\n";
	}
	# UPDATE [needs_3_1]..lo
	$rc = $dbh->do(q{UPDATE lo SET title = ?, primary_language = ?, description = ?, keywords = ?, modification_date = GetDate(), publish_month = ?, publish_year = ? WHERE id = ?}, undef, $title, $language, substr($description,0,4096), $keywords, $pub_month, $pub_year, $general_key);
	if (!$rc) {
		die "Unable to update record into lo: $dbh->errstr\n";
		$dbh->rollback;
		$dbh->disconnect;
	}
	# UPDATE [needs_3_1]..learning_object_pedagogy
	$rc = $dbh->do(q{UPDATE learning_object_pedagogy SET modification_date = GetDate() WHERE learning_object_id = ?}, undef, $general_key);
	if (!$rc) {
		die "Unable to update record into learning_object_pedagogy: $dbh->errstr\n";
		$dbh->rollback;
		$dbh->disconnect;
	}
	my @row_ary = $dbh->selectrow_array(q{SELECT lo.pedagogy_id FROM learning_object_pedagogy lo WHERE lo.learning_object_id = ?}, undef, $general_key);
	my $pedagogy_id = $row_ary[0];
	$rc = $dbh->do(q{UPDATE pedagogy SET learning_context = ?, end_user_type_id = ?, modification_date = GetDate() WHERE id = ?}, undef, $ped_lcontext, $ped_enduserrole, $general_key);
	if (!$rc) {
	$dbh->rollback;
	$dbh->disconnect;
	die "Unable to update record into pedagogy: $dbh->errstr\n";
	}
	# Upon success commit
	$dbh->commit;
	return $rc;
}

# Insert lo into tables
sub OAIc_insert_lo {
	my ($dbh, $title, $language, $description, $image, $pub_month, $pub_year, $keywords, $submitter_key, $author, $publisher, $collection, $format, $platform, $os, $url, $ped_lcontext, $ped_enduserrole, $author_reg_key, $publisher_reg_key, $collection_reg_key, $difficulty_id, $interactivity_level_id, $pedagogy_description, $resource_type_id,$rights_description,$cost) = @_;
	my $rc;
	# INSERT INTO [needs_3_1]..lo
	$rc = $dbh->do(q{INSERT INTO learning_object (title, primary_language, description, keywords, creation_date, modification_date, publish_month, publish_year, submitter) VALUES (?,?,?,?,GetDate(),GetDate(),?,?,?)}, undef, $title, $language, substr($description,0,4096), $keywords, $pub_month, $pub_year, $submitter_key);
	if (!$rc) {
		$dbh->rollback;
		$dbh->disconnect;
		die "Unable to insert new record into lo: $dbh->errstr\n";
	}
	my $id = OAIc_loexists($dbh,$title);
	printf("Learning Object ID:%s\n", $id);
	printf("Author ID:%s\n", $author_reg_key);
	# INSERT INTO [needs_3_1]..learning_object_contributor
	# Add author contribution (ct_key=8)
	$rc = $dbh->do(q{INSERT INTO learning_object_contributor (learning_object_id, entity_id, role_id, order_by) VALUES (?,?,8,1)}, undef, $id, $author_reg_key);
	# Add publisher contribution (ct_key=9)
	$rc = $dbh->do(q{INSERT INTO learning_object_contributor (learning_object_id, entity_id, role_id, order_by) VALUES (?,?,9,2)}, undef, $id, $publisher_reg_key);
	# Add collection contribution (ct_key=12)
	$rc = $dbh->do(q{INSERT INTO learning_object_contributor (learning_object_id, entity_id, role_id, order_by) VALUES (?,?,12,2)}, undef, $id, $collection_reg_key);
	if (!$rc) {
	$dbh->rollback;
	$dbh->disconnect;
	die "Unable to insert new record into learning_object_contributor: $dbh->errstr\n";
	}
	# INSERT INTO [needs_3_1]..pedagogy
        my $pedagogy_id = $dbh->selectrow_array(q{SELECT NEWID()});
	printf("Pedagogy ID:%s\n", $pedagogy_id);
	if (!$pedagogy_id) {
		$dbh->rollback;
		$dbh->disconnect;
		die "Unable to insert new record into pedagogy: $dbh->errstr\n";
	}
	$rc = $dbh->do(q{INSERT INTO pedagogy (id,description,difficulty_id,interactivity_level_id,learning_context,end_user_type_id,resource_type_id,locale_id) VALUES (?,?,?,?,?,?,?,1)}, undef, $pedagogy_id, $pedagogy_description, $difficulty_id, $interactivity_level_id, $ped_lcontext, $ped_enduserrole, $resource_type_id);
	if (!$rc) {
	$dbh->rollback;
	$dbh->disconnect;
	die "Unable to insert new record into pedagogy: $dbh->errstr\n";
	}
	# INSERT INTO [needs_v2_1]..learning_object_pedagogy
	$rc = $dbh->do(q{INSERT INTO learning_object_pedagogy (learning_object_id,pedagogy_id,order_by,creation_date,modification_date,status) VALUES (?,?,?,GetDate(),GetDate(),?)}, undef, $id, $pedagogy_id, 1, 'complete');
	if (!$rc) {
	$dbh->rollback;
	$dbh->disconnect;
	die "Unable to insert new record into learning_object_pedagogy: $dbh->errstr\n";
	}
	# INSERT INTO [needs_v2_1]..version
	$rc = $dbh->do(q{INSERT INTO version (resource_id, media_format_id, platform_type_id, location_url, license, purchase_license_type_id, modification_date, creation_date, reporter_id) VALUES (?,?,?,?,?,?,GetDate(),GetDate(),?)}, undef, $id, $format, $platform, $url, $rights_description, $cost, $submitter_key);
	if (!$rc) {
	$dbh->rollback;
	$dbh->disconnect;
	die "Unable to insert new record into version: $dbh->errstr\n";
	}
	# Upon success commit
	$dbh->commit;
	return $rc;
}

# Insert lo into tables (DLESE only)
sub OAIc_insert_lo_dlese {
	my ($dbh, $title, $language, $description, $image, $pub_month, $pub_year, $keywords, $submitter_key, $publisher, $collection, $format, $platform, $location, $learning_context, $intendedenduserrole_id, $collection_reg_key, $rights_description, $cost) = @_;
	my $rc;
	# INSERT INTO [needs_3_1]..lo
	$rc = $dbh->do(q{INSERT INTO lo (title, primary_language, description, keywords, creation_date, modification_date, publish_month, publish_year, submitter) VALUES (?,?,?,?,GetDate(),GetDate(),?,?,?)}, undef, $title, $language, substr($description,0,4096), $keywords, $pub_month, $pub_year, $submitter_key);
	if (!$rc) {
		$dbh->rollback;
		$dbh->disconnect;
		die "Unable to insert new record into lo: $dbh->errstr\n";
	}
	my $id = OAIc_loexists($dbh,$title);
	printf("Learning Object ID:%s\n", $id);

	# INSERT INTO [needs_3_1]..pedagogy
        #my $pedagogy_id = $dbh->selectrow_array(q{SELECT NEWID()});
	#printf("Pedagogy ID:%s\n", $pedagogy_id);
	#if (!$pedagogy_id) {
	#	$dbh->rollback;
	#	$dbh->disconnect;
	#	die "Unable to insert new record into pedagogy: $dbh->errstr\n";
	#}
	#$rc = $dbh->do(q{INSERT INTO pedagogy (id,description,difficulty_id,interactivity_level_id,learning_context,end_user_type_id,resource_type_id,locale_id) VALUES (?,?,?,?,?,?,?,1)}, undef, $pedagogy_id, $pedagogy_description, $difficulty_id, $interactivity_level_id, $ped_lcontext, $ped_enduserrole, $resource_type_id);
	#if (!$rc) {
	#$dbh->rollback;
	#$dbh->disconnect;
	#die "Unable to insert new record into pedagogy: $dbh->errstr\n";
	#}
	# INSERT INTO [needs_v2_1]..learning_object_pedagogy
	#$rc = $dbh->do(q{INSERT INTO learning_object_pedagogy (learning_object_id,pedagogy_id,order_by,creation_date,modification_date,status) VALUES (?,?,?,GetDate(),GetDate(),?)}, undef, $id, $pedagogy_id, 1, 'complete');
	#if (!$rc) {
	#$dbh->rollback;
	#$dbh->disconnect;
	#die "Unable to insert new record into learning_object_pedagogy: $dbh->errstr\n";
	#}
	# INSERT INTO [needs_v2_1]..version
	$rc = $dbh->do(q{INSERT INTO version (resource_id, media_format_id, platform_type_id, location_url, license, purchase_license_type_id, modification_date, creation_date, reporter_id) VALUES (?,?,?,?,?,?,GetDate(),GetDate(),?)}, undef, $id, $format, $platform, $location, $rights_description, $cost, $submitter_key);
	if (!$rc) {
	$dbh->rollback;
	$dbh->disconnect;
	die "Unable to insert new record into version: $dbh->errstr\n";
	}
	# Upon success commit
	$dbh->commit;
	return $rc;
}

# Update DLESE
sub OAIc_update_lo_dlese {
	my ($dbh, $id, $learning_context, $intendedenduserrole_id, $rights_description, $cost) = @_;
	my $rc;
	# UPDATE [needs_3_1]..version
	$rc = $dbh->do(q{UPDATE version SET license = ?, purchase_license_type_id = ? WHERE resource_id = ?}, undef, substr($rights_description,0,4096), $cost, $id);
	if (!$rc) {
	$dbh->rollback;
	$dbh->disconnect;
	die "Unable to update record into lo_platform: $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 entity.id FROM entity entity WHERE entity.email_address = ?}, undef, $email);
	if ($person_row_ary[0]) {
		return $person_row_ary[0];
		} else {
		return undef;
	}
}

sub OAIc_personexists_name {
	my ($dbh,$name) = @_;
	my @person_row_ary = $dbh->selectrow_array(q{SELECT entity.id FROM entity entity WHERE entity.name = ?}, undef, $name);
	if ($person_row_ary[0]) {
		return $person_row_ary[0];
		} else {
		return undef;
	}
}
sub OAIc_orgexists {
	my ($dbh,$name) = @_;
	my @org_row_ary = $dbh->selectrow_array(q{SELECT entity.id FROM entity entity WHERE entity.name = ?}, undef, $name);
	if ($org_row_ary[0]) {
		return $org_row_ary[0];
		} else {
		return undef;
	}
}

sub OAIc_insert_person {
	my ($dbh,$affiliate_key,$submitter_key,$personLastname,$personFirstname,$personEmail,$personCompany) = @_;
	my $rc = $dbh->do(q{INSERT INTO entity (entity_type,name,email_address,privacy_flags,object_type) VALUES (2,?,?,0,'person')}, undef, join(' ',$personFirstname,$personLastname), $personEmail);
	if (!$rc) {          
		$dbh->rollback;
		$dbh->disconnect;
		die "Unable to insert new person into entity: $dbh->errstr \n";
	}
	my $id = OAIc_personexists_name($dbh,join(' ',$personFirstname,$personLastname));
	$rc = $dbh->do(q{INSERT INTO person (id,type,first_name,last_name,company) VALUES (?,'person',?,?,?)}, undef, $id, $personFirstname, $personLastname, $personCompany);
	if (!$rc) {          
		$dbh->rollback;
		$dbh->disconnect;
		die "Unable to insert new person into person: $dbh->errstr \n";
	}
	return $rc;
}

sub OAIc_insert_person_full {
	my ($dbh,$publisher_reg_key,$submitter_key,$person_last_name,$person_first_name,$entity_email_address,$person_company,$person_middle_name,$person_title,$entity_address,$entity_city,$entity_state,$entity_postal_code,$entity_home_page_url,$entity_phone,$entity_fax,$entity_country) = @_;
	my $rc = $dbh->do(q{INSERT INTO entity (entity_type,name,email_address,privacy_flags,object_type,address,city,state,postal_code,home_page_url,phone,fax,country) VALUES (2,?,?,0,'person',?,?,?,?,?,?,?,?)}, undef, join(' ',$person_first_name,$person_middle_name,$person_last_name),$entity_email_address,$entity_address,$entity_city,$entity_state,$entity_postal_code,$entity_home_page_url,$entity_phone,$entity_fax,$entity_country);
	if (!$rc) {          
		$dbh->rollback;
		$dbh->disconnect;
		die "Unable to insert new person into entity: $dbh->errstr \n";
	}
	my $id = OAIc_personexists($dbh,$entity_email_address);
	$rc = $dbh->do(q{INSERT INTO person (id,type,first_name,last_name,middle_name,title,company) VALUES (?,'person',?,?,?,?,?)}, undef, $id, $person_first_name, $person_last_name, $person_middle_name,$person_title,$person_company);
	if (!$rc) {          
		$dbh->rollback;
		$dbh->disconnect;
		die "Unable to insert new person into person: $dbh->errstr \n";
	}
	return $rc;
}

sub OAIc_insert_org {
	my ($dbh,$publisher_reg_key,$submitter_key,$entity_email_address,$person_company,$entity_address,$entity_city,$entity_state,$entity_postal_code,$entity_home_page_url,$entity_phone,$entity_fax,$entity_country) = @_;
	my $rc = $dbh->do(q{INSERT INTO entity (entity_type,name,email_address,privacy_flags,object_type,address,city,state,postal_code,home_page_url,phone,fax,country) VALUES (1,?,?,0,'organization',?,?,?,?,?,?,?,?)}, undef, $person_company,$entity_email_address,$entity_address,$entity_city,$entity_state,$entity_postal_code,$entity_home_page_url,$entity_phone,$entity_fax,$entity_country);
	if (!$rc) {          
		$dbh->rollback;
		$dbh->disconnect;
		die "Unable to insert new organization into entity: $dbh->errstr \n";
	}
	return $rc;
}
return 1;