/* This query identifies rank and endemism discrepancies between the global and subnational levels. This version is made to be run with html output, as it flags discrepancies in red and provides links to EGTs and ESTs. If you would like to download results, please use the non-html version. 

Query by Gwen Davis and modified by Dave Almquist 2022-08-30

Run in a local instance of Biotics.*/
 
Select
egt.element_global_id EGT_ID,
egt.elcode_bcd ELCODE,
d_nc.name_type_cd NMTYP,  
d_nc.name_category_desc NAME_CAT,
informal_grp((informal_tax(egt.element_global_id))) INF_GRP,
informal_tax(egt.element_global_id) INF_TAX,
d_cs.classification_status_cd CL_STAT,
gname.scientific_name GNAME,
case when tg.d_juris_endem_id is null and est.d_endemism_id = 1 then '<font color="red">CHECK<BR>G_ENDEM: </font><BR>'||'<center><a href="https://biotics'||subn.subnation_code||'.natureserve.org/biotics/services/page/ElementGlobal/'||egt.ELEMENT_GLOBAL_ID||'.html'||'"target="_blank">'||'null'||'</a></center> '
     when (tg.d_juris_endem_id != 1 and est.d_endemism_id = 1) 
       or (tg.d_juris_endem_id = 1 and (est.d_endemism_id != 1 or est.d_endemism_id is null))
     then '<font color="red">CHECK<BR>G_ENDEM: </font><BR>'||'<center><a href="https://biotics'||subn.subnation_code||'.natureserve.org/biotics/services/page/ElementGlobal/'||egt.ELEMENT_GLOBAL_ID||'.html'||'"target="_blank">'||d_jend_g.juris_endem_cd||'</a></center> ' ELSE 'G_ENDEM:<BR><center> '||d_jend_g.juris_endem_cd||'</center>'END AS G_J_END,

case when ((tg.d_juris_endem_id = 1 or est.d_endemism_id = 1)
       and (replace(est.s_rank,'S','') != REGEXP_REPLACE(SUBSTR(egt.g_rank,INSTR(egt.g_rank,'T') +0),'([G|T|Q])','')))
      then '<font color="red">CHECK:  </font>'||'<a href="https://biotics'||subn.subnation_code||'.natureserve.org/biotics/services/page/ElementGlobal/'||egt.ELEMENT_GLOBAL_ID||'.html'||'"target="_blank">'||egt.g_rank||'</a>' ELSE egt.g_rank END AS GRANK,

/*egt.rounded_g_rank R_GRANK,*/
'GRK_CH_DT<BR>'||to_char(egt.g_rank_change_date, 'yyyy-mm-dd') GRK_CH_DT,
'GRK_RV_DT<BR>'||to_char(egt.g_rank_review_date, 'yyyy-mm-dd') GRK_RV_DT,
'EGR_VDATE<BR>'||to_char(egr.VERSION_DATE,'YYYY-MM-DD') EGR_VDATE,
subn.subnation_code SUBN,
case when est.d_endemism_id is null and tg.d_juris_endem_id = 1 then '<font color="red"> CHECK<BR>S_ENDEM:<BR></font>'||'<center> <a href="https://biotics'||subn.subnation_code||'.natureserve.org/biotics/services/page/ElementSubnational/'||est.element_subnational_id||'.html'||'"target="_blank">'||'null'||'</a></center> '
     when (est.d_endemism_id = 1 and (tg.d_juris_endem_id != 1 or tg.d_juris_endem_id is null)) 
       or (est.d_endemism_id != 1 and tg.d_juris_endem_id = 1)
     then '<font color="red"> CHECK<BR>S_ENDEM:<BR></font>'||'<center> <a href="https://biotics'||subn.subnation_code||'.natureserve.org/biotics/services/page/ElementSubnational/'||est.element_subnational_id||'.html'||'"target="_blank">'||d_endem_s.endemism_cd||'</a></center> ' ELSE d_endem_s.endemism_cd END AS S_ENDEM,
     
case when ((tg.d_juris_endem_id = 1 or est.d_endemism_id = 1)
       and (replace(est.s_rank,'S','') != REGEXP_REPLACE(SUBSTR(egt.g_rank,INSTR(egt.g_rank,'T') + 0),'([G|T|Q])','')))
     then '<font color="red"> CHECK<BR></font>'||'<a href="https://biotics'||subn.subnation_code||'.natureserve.org/biotics/services/page/ElementSubnational/'||est.element_subnational_id||'.html'||'"target="_blank">'||substr(est.s_rank,1,12)||'</a>'  ELSE est.s_rank END AS SRANK,
'S_RANK_REASONS<BR><BR>'||ESR.S_RANK_REASONS S_RANK_REASONS,
'S_RANGE_COMMENTS<BR><BR>'||esr.S_RANGE_COM S_RANGE_COMMENTS,
'S_TRK<BR><center>'||d_eot.EO_TRACK_STATUS_CD||'</center>' S_TRK,
'SRK_CH_DT<BR>'||to_char(est.s_rank_change_date, 'yyyy-mm-dd') srk_ch_dt,
'SRK_REV_DT<BR>'||to_char(est.s_rank_review_date, 'yyyy-mm-dd') srk_rev_dt,
'ESR_VDATE<BR>'||to_char(esr.VERSION_DATE,'YYYY-MM-DD') ESR_VDATE,
sname.scientific_name SNAME,
est.element_subnational_id EST_ID,
egr.G_RANK_REASONS,

'G_CLASSIFICATION_COM<BR><BR>'||egt.G_CLASSIFICATION_COM G_CLASSIFICATION_COM,
'G_RANGE_COMMENTS<BR><BR>'||egr.G_RANGE_COM G_RANGE_COMMENTS,
DelimList('SELECT d_origin.origin_desc
FROM taxon_natl_dist n_dist, d_origin
WHERE n_dist.d_origin_id = d_origin.d_origin_id
and n_dist.d_dist_confidence_id = 1  /***confident***/
and n_dist.element_national_id = ' || ent.element_national_id || '
ORDER BY n_dist.d_origin_id', ' and ') AS NATION_ORIGIN,
(select d_usesa.usesa_cd from d_usesa
  WHERE tg.d_usesa_id = d_usesa.d_usesa_id) USESA,
tg.interpreted_usesa INTERP_USESA,
(select taxon_subnational.state_interpreted_usesa from taxon_subnational
  WHERE taxon_subnational.element_subnational_id = est.element_subnational_id) STATE_INTERP_USESA

From
element_global egt,
taxon_global tg,
element_national ent,
element_subnational est,
scientific_name gname,
scientific_name sname,
subnation subn,
d_juris_endem d_jend_g,
d_endemism d_endem_s,
d_classification_status d_cs,
element_global_rank egr,
d_name_category d_nc,
D_EO_TRACK_STATUS d_eot,
ELEMENT_SUBNATL_RANK esr

Where egt.element_global_id = egr.element_global_id (+)
and egt.element_global_id = ent.element_global_id
and ent.element_national_id = est.element_national_id
and est.element_subnational_id = esr.element_subnational_id (+)
and est.subnation_id = subn.subnation_id
and est.d_endemism_id = d_endem_s.d_endemism_id (+)
and d_eot.d_eo_track_status_id = est.d_eo_track_status_id
and egt.gname_id = gname.scientific_name_id
and est.sname_id = sname.scientific_name_id
and gname.d_name_category_id = d_nc.d_name_category_id
and egt.element_global_id = tg.element_global_id
and egt.d_classification_status_id = d_cs.d_classification_status_id
and tg.d_juris_endem_id = d_jend_g.d_juris_endem_id (+)

and egt.inactive_ind = 'N'
  -- and subn.subnation_code like 'FL'
and d_nc.name_type_desc in ('Zoological', 'Botanical')
  -- and gname.d_name_category_id = 4  /***vascular plants***/
and est.s_rank not in ('SNR','SNA')
and (
     --endemic to a single state or province
      ((tg.d_juris_endem_id = 1 or est.d_endemism_id = 1)
      and
      -- Srank not same as Grank
      (replace(est.s_rank,'S','') != REGEXP_REPLACE(SUBSTR(egt.g_rank,INSTR(egt.g_rank,'T') + 0),'([G|T|Q])','')))
        
     or
      --EST says endemic EGT does not
     (est.d_endemism_id = 1 and (tg.d_juris_endem_id != 1 or tg.d_juris_endem_id is null))
      --EGT says endemic EST does not
     or (tg.d_juris_endem_id = 1 and (est.d_endemism_id != 1 or est.d_endemism_id is null))
     )
order by NMTYP, INF_GRP, INF_tax, GNAME



/******* This version is made to be run with html output, as it flags discrepancies in red and provides hyperlinks to EGTs and ESTs to check and fix issues. If you would like to download results, please use the non-html version. *******/