 <cfinclude template="/includes/_header.cfm">
 <cfset title="Bulkload Taxonomy">
 <!---- make the table
+	create table cf_temp_taxon_name (
+		username varchar2(255) not null,
+		scientific_name varchar2(255) not null,
+		status  varchar2(255)
+	);
 
- revision to deal with name+classification in new model - seems we're going to have to eventually
+	create or replace public synonym cf_temp_taxon_name for cf_temp_taxon_name;
 
- approach: add NULLable columns for every conceiveable rank
-
-alter table cf_temp_taxonomy add SUBPHYLUM varchar2(255);
-alter table cf_temp_taxonomy modify VALID_CATALOG_TERM_FG null;
-alter table cf_temp_taxonomy modify SOURCE_AUTHORITY null;
-alter table cf_temp_taxonomy modify SUBSPECIES varchar2(255);
-alter table cf_temp_taxonomy modify PHYLCLASS varchar2(255);
-alter table cf_temp_taxonomy modify SUBCLASS varchar2(255);
-alter table cf_temp_taxonomy modify PHYLORDER varchar2(255);
-alter table cf_temp_taxonomy modify SUBORDER varchar2(255);
-alter table cf_temp_taxonomy modify FAMILY varchar2(255);
-alter table cf_temp_taxonomy modify SUBFAMILY varchar2(255);
+	grant all on cf_temp_taxon_name to manage_taxonomy;
 
 
-alter table cf_temp_taxonomy modify GENUS varchar2(255);
-alter table cf_temp_taxonomy modify SUBGENUS varchar2(255);
-alter table cf_temp_taxonomy modify SPECIES varchar2(255);
-alter table cf_temp_taxonomy modify SOURCE_AUTHORITY varchar2(255);
-alter table cf_temp_taxonomy modify TRIBE varchar2(255);
-alter table cf_temp_taxonomy modify INFRASPECIFIC_RANK varchar2(255);
-alter table cf_temp_taxonomy modify PHYLUM varchar2(255);
-alter table cf_temp_taxonomy modify PHYLCLASS varchar2(255);
-
-alter table cf_temp_taxonomy modify NOMENCLATURAL_CODE null;
-
-alter table cf_temp_taxonomy add display_name varchar2(255);
-alter table cf_temp_taxonomy add source varchar2(255);
-
-
-alter table cf_temp_taxonomy drop column INFRASPECIFIC_RANK;
-
-
-------------- oldstuff follows ------------
-drop table cf_temp_taxonomy;
-
-create table cf_temp_taxonomy (
-	key number,
-	status varchar2(4000),
- 	SCIENTIFIC_NAME VARCHAR2(255)
-);
-
-	create or replace public synonym cf_temp_taxonomy for cf_temp_taxonomy;
-	grant select,insert,update,delete on cf_temp_taxonomy to coldfusion_user;
-	grant select on cf_temp_taxonomy to public;
-
-
-CREATE OR REPLACE TRIGGER cf_temp_taxonomy_key
- before insert  ON cf_temp_taxonomy
-FOR EACH ROW
-DECLARE
-BEGIN
-	if :NEW.key is null then
-		select somerandomsequence.nextval into :new.key from dual;
-    end if;
-
-END;
+create or replace trigger trg_cf_cf_temp_taxon_name before insert on cf_temp_taxon_name
+	FOR EACH ROW
+	begin
+		if :NEW.username is null then
+			select SYS_CONTEXT('USERENV', 'SESSION_USER') into :NEW.username from dual;
+		end if;
+	end;
 /
-sho err
-
--------- classifications
-
-drop table cf_temp_taxonomy;
-
-create table cf_temp_taxonomy (
-	key number,
-	status varchar2(4000),
- 	SCIENTIFIC_NAME VARCHAR2(255)
-);
-
-	create or replace public synonym cf_temp_taxonomy for cf_temp_taxonomy;
-	grant select,insert,update,delete on cf_temp_taxonomy to coldfusion_user;
-	grant select on cf_temp_taxonomy to public;
-
-
-CREATE OR REPLACE TRIGGER cf_temp_taxonomy_key
- before insert  ON cf_temp_taxonomy
-FOR EACH ROW
-DECLARE
-BEGIN
-	if :NEW.key is null then
-		select somerandomsequence.nextval into :new.key from dual;
-    end if;
-
-END;
-/
-sho err
-
-
-
-
-
-
------->
-
-
-
-deprecated<cfabort>
+sho err;
+	------->
 <!------------------------------------------------------->
 <cfif action is "down">
 	<cfquery name="mine" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
-		select * from cf_temp_taxonomy
+		select * from cf_temp_taxonomy where upper(username)='#ucase(session.username)#'
 	</cfquery>
 	<cfset  util = CreateObject("component","component.utilities")>
 	<cfset csv = util.QueryToCSV2(Query=mine,Fields=mine.columnlist)>

<cfif action is "show">
<script src="/includes/sorttable.js"></script>
<cfoutput>
	<cfquery name="data" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
		select * from cf_temp_taxonomy
	</cfquery>
	<cfquery name="isProb" dbtype="query">
		select count(*) c from data where status ='valid'
	</cfquery>
	<p>
		This form will happily create garbage. CHECK <strong>ALL</strong> DATA CAREFULLY BEFORE LOADING!!!
	</p>
	<p>
		This form is not the preferred method of creating classifications. Use GNITE if you can.
	</p>
	<P>
		Some terms may not mean what you think they do. Ask us (contact link below) if you're uncertain.
	</P>
	<ul>
		<cfif isProb.c eq data.recordcount>
			<li><strong>Carefully</strong> check the table below, then <a href="BulkloadTaxonomy.cfm?action=loadData">continue to load</a></li>
		</cfif>
		<li>
			<a href="BulkloadTaxonomy.cfm?action=autogenmn">Click here to generate multinomials</a>
			<br>Do this BEFORE generating display_name
			<br>Do this BEFORE validation
			<br>CHECK THE RESULTS
		</li>
		<li>
			<a href="BulkloadTaxonomy.cfm?action=autogendispname">Click here to generate display_name</a>
			Do this BEFORE validation and CHECK THE RESULTS. This may not do what you want if you don't specify nomenclatural_code.
		</li>
		<li><a href="BulkloadTaxonomy.cfm?action=validate">validate</a></li>
		<li><a href="BulkloadTaxonomy.cfm?action=deleteDups">discard duplicate names</a></li>
		<li><a href="BulkloadTaxonomy.cfm?action=down">download</a></li>
		<li><a href="BulkloadTaxonomy.cfm?action=nothing">start over</a></li>
	</ul>
	<cfset h=data.columnlist>
	<table border id="t" class="sortable">
		<tr>

</cfoutput>
</cfif>
<!------------------------------------------------------->
<cfif action is "validate">
<cfoutput>
	<cfquery name="d" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
		select * from  cf_temp_taxonomy  where status is null and  upper(username)='#ucase(session.username)#'
	</cfquery>
	<cfset tc = CreateObject("component","component.taxonomy")>

	<cfloop query="d">
		<cfset result=tc.validateName(scientific_name)>
		<cfquery name="u" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
			update cf_temp_taxonomy set status='#result.consensus#' where scientific_name='#scientific_name#'
		</cfquery>
	</cfloop>
	<cflocation url="BulkloadTaxonomy.cfm" addtoken="false">
</cfoutput>
</cfif>



<!------------------------------------------------------->
<cfif action is "loadData">
<cfoutput>
	<cfquery name="d" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
		select * from  cf_temp_taxonomy  where  upper(username)='#ucase(session.username)#'
	</cfquery>
	<cfloop query="d">
		<cftransaction>
			<cfquery name="isn" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
				insert into taxon_name (scientific_name) values ('#scientific_name#')
			</cfquery>
			<cfquery name="dup" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
				delete from cf_temp_taxonomy  where scientific_name='#scientific_name#' and upper(username)='#ucase(session.username)#'
			</cfquery>
			<br>loaded #scientific_name#
		</cftransaction>
	</cfloop>
	Spiffy, all done.
</cfoutput>
</cfif>
