#!C:\Program Files\Python27\python.exe
# -*- coding: UTF-8 -*-

import os, sys, ssl

# set path to lib and import
sys.path.insert(0, os.path.abspath("../rw_common"))
from lib import getRecordSet, ssi_to_tempfile
from templating import PageMacro

# Get list from DB
rs = getRecordSet("""
    SELECT p.provider_id, 
	case when length(p.uddi_business_key) < 10 then '-' else '<a href="http://www.gbif.org/publisher/' || p.uddi_business_key || '" target="blank">GBIF</a>' end as gbif_registry, 
	p.name, p.town, p.country, 
	case when p.home_url is null then '-' else '<a href="' || p.home_url || '" target="_blank">home</a>' end as home, 
	case when count(d.datasource_id) = 0 then 'in' else count(d.datasource_id) || ' datasource(s) in' end as num_datasources
    FROM registry.provider p
    LEFT JOIN registry.datasource d ON provider_id=provider_fk
    WHERE (active_flag or openup or avh or binhum or geocase or ggbn)
     and (protocol_fk = 'biocase' or protocol_fk is null)
    GROUP BY p.provider_id, p.uddi_business_key, p.name, p.town, p.country, p.home_url --, protocol_fk, active_flag, openup, avh, binhum, geocase, ggbn
    ORDER BY p.name""")

# load html template and fill in data
tmpl = PageMacro('Content', PageMacro.DELMODE)
ssl._create_default_https_context = ssl._create_unverified_context
temp = ssi_to_tempfile("https://www.biocase.org/whats_biocase/_providers_list.shtml")
tmpl.load('Content', temp)

tmpl.expand('Content', 'providerlist',
    [{'id': str(row[0]), 'gbif_registry': row[1],
      'name': row[2], 'town': row[3], 'country': row[4],
      'home': row[5], 'num_datasources': row[6]}  for row in rs])

# print HTML
print "Content-Type: text/html\n"
print tmpl
os.remove(temp)