 		<cfquery name="d_htax_dataset" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
 			delete from htax_dataset where dataset_id =#d.dataset_id#
 		</cfquery>
-
 	</cftransaction>
 
+	delete successful.
+		<p>
+			<a href="taxonomyTree.cfm?action=nothing">home</a>
+		</p>
 	</cfoutput>
-
-
-
-
-
-
-
-
 </cfif>
 <!--------------------------------------------------------------------------------------->
 <cfif action is "mismatch_import">

</cfif>



<!------------------------------------------------------------------------------------------------->
<cfif action is "noSuccessimport">

	</cfif>
</select>
</cfoutput>

<!------------------------------------------------------------------------------------------------->
<cfif action is "findProblemData">
	<p>
		Maybe this will be a form someday.... it's just SQL for now
	</p>

<h3>What's that doing there</h3>

<pre>
	select scientific_name from taxon_name,taxon_term a, taxon_term b where
	taxon_name.taxon_name_id=a.taxon_name_id and
	taxon_name.taxon_name_id=b.taxon_name_id and
	a.source='Arctos' and
	b.source='Arctos' and
	a.term='Aves' and
	b.term='Curculionoidea'
	;


	select distinct term_type from
	taxon_term where
	source='Arctos' and
	taxon_name_id in (
	select distinct
		taxon_name.taxon_name_id
	from
		taxon_name,
		taxon_term a
		--, taxon_term b
	where
		taxon_name.taxon_name_id=a.taxon_name_id and
	--taxon_name.taxon_name_id=b.taxon_name_id and
	a.source='Arctos' and
	--b.source='Arctos' and
	--a.term='Aves' and
	a.term='Curculionoidea'
	)
	;
	-- nope...

	select * from temp_missedh where scientific_name='Curculionidae';

		select * from htax_seed where scientific_name='Curculionidae';
		select * from hierarchical_taxonomy where term='Curculionidae';
</pre>


<h3>
	find terms which are used by bird collections but do not have a class=Aves term, possibly because they have no classification anything
</h3>

<pre>

	-- First a temp table, because

	drop table temp_missedh;

	create table temp_missedh as select distinct
		taxon_name.scientific_name,
		taxon_name.taxon_name_id
	from
		taxon_name,
		(select * from taxon_term where term_type='class' and term='Aves') taxon_term ,
		identification_taxonomy,
		identification,
		cataloged_item,
		collection
	where
		taxon_name.taxon_name_id=identification_taxonomy.taxon_name_id and
		identification_taxonomy.identification_id=identification.identification_id and
		identification.collection_object_id=cataloged_item.collection_object_id and
		cataloged_item.collection_id=collection.collection_id and
		collection.collection_cde='Bird' and
		taxon_name.taxon_name_id =taxon_term.taxon_name_id (+) and
		taxon_term.taxon_name_id is null;

	insert into htax_seed (
		SCIENTIFIC_NAME,
		TAXON_NAME_ID,
		DATASET_ID
	) (
		select
			scientific_name,
			TAXON_NAME_ID,
			114013137
		from
			temp_missedh
		where
			rank in (select taxon_term from cttaxon_term where IS_CLASSIFICATION=1)
	);

	-- now find terms related to those terms, and which we don't already have
	-- these might also lead to related terms, so run this until it doesn't do anything

	declare
		v_wrk varchar2(4000);
		v_tt varchar2(4000);
		v_c number;
		----- CAUTION HARDCODED!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
		v_dateset_id number := 114013137;
		----- CAUTION HARDCODED!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
	begin
		for r in (select taxon_name_id,scientific_name from temp_missedh) loop
			dbms_output.put_line(r.scientific_name);
			-- if the term looks like a multinomial we might be able to do something here
			v_wrk:='';

			if r.scientific_name like '% % % %' then
				v_tt:='too_many_spaces';
				dbms_output.put_line(v_tt);
			elsif r.scientific_name like '% % %' then
				v_tt:='subspecies';
				dbms_output.put_line(v_tt);
				v_wrk:=substr(r.scientific_name,0,instr(r.scientific_name,' ',1,2));
			elsif r.scientific_name like '% %' then
				v_tt:='species';
				dbms_output.put_line(v_tt);
				v_wrk:=substr(r.scientific_name,0,instr(r.scientific_name,' '));
			else
				v_tt:='genus or something maybe IDK';
				dbms_output.put_line(v_tt);
			end if;
			-- see if we can find related
			dbms_output.put_line('v_wrk=' || v_wrk);
			for rt in (
				select distinct scientific_name,taxon_name_id from taxon_name
					--, taxon_term
					where
					--taxon_name.taxon_name_id=taxon_term.taxon_name_id
					--and term
					scientific_name=trim(v_wrk) and
					taxon_name.taxon_name_id != r.taxon_name_id
			) loop
				dbms_output.put_line('parent=' || rt.scientific_name);
				select count(*) into v_c from htax_seed where
					dataset_id=v_dateset_id and
					scientific_name=r.scientific_name;
				if v_c=0 then
					-- insert
					insert into htax_seed (
						SCIENTIFIC_NAME,
						TAXON_NAME_ID,
						DATASET_ID
					) values (
						r.scientific_name,
						r.TAXON_NAME_ID,
						v_dateset_id
					);
					dbms_output.put_line('inserted ' || r.scientific_name);
				end if;
			end loop;
		end loop;
	end;
	/



</pre>























<hr>Old-n-busted</h3>

<pre>
	-- this isn't really necessary; the import procedure now tries to guess at rank. Just leaving it here
	in case it becomes useful somehow.

	<p>
	everything that follows is for historical purposes only
	</p>
	alter table temp_missedh add rank varchar2(255);

	declare
		hs number;
		rk varchar2(255);
	begin
		for r in (select * from temp_missedh) loop
			rk:=null;
			dbms_output.put_line(r.scientific_name);
			select count(*) into hs from taxon_term where source='Arctos' and taxon_name_id=r.taxon_name_id;
			dbms_output.put_line('class terms: ' || hs);
			if hs > 0 then
			dbms_output.put_line('gotsomething');
				-- has classification, might have rank
				select count(*) into hs from taxon_term where source='Arctos' and
					term_type != 'scientific_name' and
					term=r.scientific_name and
					taxon_name_id=r.taxon_name_id;
				if hs>0 then
					-- yay, got rank
					select term_type into rk from taxon_term where source='Arctos' and
					term_type != 'scientific_name' and
					term=r.scientific_name and
					taxon_name_id=r.taxon_name_id;
					dbms_output.put_line('using rank ' || rk);
				end if;
			end if;
			if rk is null then
				dbms_output.put_line('didnt find rank try to guess by structure');
				if r.scientific_name like '% % %' then
					rk:='subspecies';
					dbms_output.put_line('subspecies');
				elsif r.scientific_name like '% %' then
					rk:='species';
					dbms_output.put_line('species');
				elsif r.scientific_name like '% % % %' then
					rk:='too_many_spaces';
					dbms_output.put_line('too_many_spaces');
				else
					rk:='genus or something maybe IDK';
					dbms_output.put_line('genus or something maybe IDK');
				end if;
			end if;
			update temp_missedh set rank=rk where taxon_name_id=r.taxon_name_id;
		end loop;
	end;
	/


	select scientific_name,rank from temp_missedh where rank not in (select taxon_term from cttaxon_term where IS_CLASSIFICATION=1);

	--- deal with those manually



	<br>Insert them into the hierarchy directly under kingdom
	<br>No other insert point is safe
	<br>
	select dataset_id from htax_dataset where dataset_name='bird';
	<br> >	114013137

	<br>
	select tid from hierarchical_taxonomy where term='Animalia' and dataset_id=114013137;
	<br> > 114013138

	insert into hierarchical_taxonomy (
		TID,
		PARENT_TID,
		TERM,
		RANK,
		DATASET_ID
	) (
		select
			someRandomSequence.nextval,
			114013138,
			scientific_name,
			rank,
			114013137
		from
			temp_missedh
		where
			rank in (select taxon_term from cttaxon_term where IS_CLASSIFICATION=1)
	);

	-- well that sucks - try different path...
	delete from hierarchical_taxonomy where DATASET_ID=114013137 and TERM in (select scientific_name from temp_missedh);


UAM@ARCTOS> desc hierarchical_taxonomy
 Name								   Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 TID								   NOT NULL NUMBER
 PARENT_TID								    NUMBER
 TERM									    VARCHAR2(255)
 RANK									    VARCHAR2(255)
 DATASET_ID							   NOT NULL NUMBER


	select distinct
		taxon_name.scientific_name
	from
		taxon_name,
		taxon_term,
		identification_taxonomy,
		identification,
		cataloged_item,
		collection
	where
		taxon_name.taxon_name_id=identification_taxonomy.taxon_name_id and
		identification_taxonomy.identification_id=identification.identification_id and
		identification.collection_object_id=cataloged_item.collection_object_id and
		cataloged_item.collection_id=collection.collection_id and
		collection.collection_cde='Bird' and
		taxon_name.taxon_name_id =taxon_term.taxon_name_id (+) and
		taxon_term.taxon_name_id is null;


		select distinct
		taxon_name.scientific_name
	from
		taxon_name,
		(select * from taxon_term where term_type='class' and term='Aves')taxon_term ,
		identification_taxonomy,
		identification,
		cataloged_item,
		collection
	where
		taxon_name.taxon_name_id=identification_taxonomy.taxon_name_id and
		identification_taxonomy.identification_id=identification.identification_id and
		identification.collection_object_id=cataloged_item.collection_object_id and
		cataloged_item.collection_id=collection.collection_id and
		collection.collection_cde='Bird' and
		taxon_name.taxon_name_id =taxon_term.taxon_name_id (+) and
		taxon_term.taxon_name_id is null;




	select
		taxon_name.scientific_name
	from
		taxon_name,
		taxon_term,
		identification_taxonomy,
		identification,
		cataloged_item,
		collection
	where
		taxon_name.taxon_name_id=identification_taxonomy.taxon_name_id and
		identification_taxonomy.identification_id=identification.identification_id and
		identification.collection_object_id=cataloged_item.collection_object_id and
		cataloged_item.collection_id=collection.collection_id and
		collection.collection_cde='Bird' and
		taxon_name.taxon_name_id not in (
			select taxon_name_id from taxon_term,CTTAXONOMY_SOURCE
				where taxon_term.source=CTTAXONOMY_SOURCE.source and
				taxon_term.term_type='class' and
				taxon_term.term='Aves'
		)


	select distinct
		taxon_name.scientific_name
	from
		taxon_name,
		taxon_term,
		identification_taxonomy,
		identification,
		cataloged_item,
		collection
	where
		taxon_name.taxon_name_id=identification_taxonomy.taxon_name_id and
		identification_taxonomy.identification_id=identification.identification_id and
		identification.collection_object_id=cataloged_item.collection_object_id and
		cataloged_item.collection_id=collection.collection_id and
		collection.collection_cde='Bird' and
		taxon_name.taxon_name_id =taxon_term.taxon_name_id (+) and
		taxon_term.taxon_name_id is null;


			select taxon_name_id from taxon_term,CTTAXONOMY_SOURCE
				where taxon_term.source=CTTAXONOMY_SOURCE.source and
				taxon_term.term_type='class' and
				taxon_term.term='Aves'
		)

		</pre>
</cfif>
<!------------------------------------------------------------------------------------------------->
<cfif action is "nothing">
	<p>
