 	</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>

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

	</cfoutput>








</cfif>
<!--------------------------------------------------------------------------------------->
<cfif action is "mismatch_import">

</cfif>


<!------------------------------------------------------------------------------------------------->
<cfif action is "findTermSource">
	<!--- pass in a term, figure out where it came from ---->

	<cfquery name="d" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
		select
			taxon_term.term_type,
			count(*) timesUsed
		from
			taxon_name,
			taxon_term,
			CTTAXONOMY_SOURCE
		where
			taxon_name.taxon_name_id=taxon_term.taxon_name_id and
			taxon_term.source=CTTAXONOMY_SOURCE.source and
			taxon_term.position_in_classification is not null and
			taxon_term.term='#term#'
		group by taxon_term.term_type
		order by count(*)
	</cfquery>
	<cfoutput>
		<p>
			Usage of #term# (in local classifications):
		</p>
		<table border>
			<tr>
				<td>Rank</td>
				<td>TimesUsed</td>
				<td>Arctos</td>
			</tr>
			<cfloop query="d">
				<tr>
					<td>#term_type#</td>
					<td>#timesUsed#</td>
					<td>
						<div>
							<cfif len(term_type) is 0>
								<cfset stt='=NULL'>
							<cfelse>
								<cfset stt='==#term_type#'>
							</cfif>
							<a href="/taxonomy.cfm?source=LOCAL&taxon_term==#term#&term_type#stt#">search term@rank</a>
						</div>
					</td>
				</tr>
			</cfloop>
		</table>
	</cfoutput>



	<!----
	<cfquery name="repop" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
		select
			htax_temp_hierarcicized.TAXON_NAME_ID,
			taxon_name.scientific_name,
			taxon_term.term,
			taxon_term.term_type
		from
			htax_temp_hierarcicized,
			htax_dataset,
			taxon_name,
			taxon_term
		where
			htax_temp_hierarcicized.dataset_id=htax_dataset.dataset_id and
			htax_dataset.dataset_name='#dataset_name#' and
			htax_temp_hierarcicized.status='fail: ORA-00001: unique constraint (UAM.IU_TERM_DS) violated' and
			htax_temp_hierarcicized.TAXON_NAME_ID=taxon_name.TAXON_NAME_ID and
			taxon_name.TAXON_NAME_ID=taxon_term.TAXON_NAME_ID and
			taxon_term.position_in_classification is not null and
			taxon_term.source=htax_dataset.source
	</cfquery>
	<cfdump var=#d#>
	---->
</cfif>




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