from typing import NoReturn
import time
import dataset
import codecs
import sys
import os
import time
import re
import datetime
import dataset
import jinja2



def gen_report():
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/hhh?charset=utf8mb4')
    fname=os.path.abspath(__file__)
    elmts=fname.split(os.path.sep)
    path2=os.path.sep.join(elmts[0:-1])
    keysdir=path2+os.path.sep+'static'+os.path.sep
    print(keysdir)
    templateLoader = jinja2.FileSystemLoader(searchpath=keysdir)
    templateEnv = jinja2.Environment(loader=templateLoader)
    TEMPLATE_FILE = "designer.tmpl"
    template = templateEnv.get_template(TEMPLATE_FILE)

    repeat={}
    displaydata=[]
    cursor=db.query('SELECT designer,searchome,c_100,hhh,dt FROM hhh.rank_summary order by dt desc,hhh asc;')
    for c in cursor:
        designer=c['designer']
        if repeat.get(designer) is None:
            repeat[designer]=1
            hhh=c['hhh']
            c_100=c['c_100']
            searchome=c['searchome']
            hhh_label='<a  href="https://www.google.com/search?q='+designer+'"><button class="btn btn-primary">幸福空間 <span class="badge">'+str(c['hhh'])+'</span> </button></a>'

            if c_100==-1:
                c100_label='<span>&nbsp;</span>'
            else:
                c100_label='<a  href="https://www.google.com/search?q='+designer+'"><button class="btn btn-danger">100設計 <span class="badge">'+str(c['c_100'])+'</span> </button></a>'

            if searchome==-1:
                sch_label='<span>&nbsp;</span>'
            else:
                sch_label='<a  href="https://www.google.com/search?q='+designer+'"><button class="btn btn-danger">設計家 <span class="badge">'+str(c['searchome'])+'</span> </button></a>'


            local_lst=[]
            if hhh > 0:
                local_lst.append(('hhh',hhh))
            if searchome > 0:
                local_lst.append(('searchome',searchome))
            if c_100 > 0:
                local_lst.append(('c_100',c_100))

            sorted_by_second = sorted(local_lst, key=lambda tup: tup[1])
            print(sorted_by_second)
            first=None
            second=None
            slen=len(sorted_by_second)
            
            results=[]
            for l in sorted_by_second:
                if l[0]=='hhh':
                    results.append(hhh_label)
                if l[0]=='searchome':
                    results.append(sch_label)
                if l[0]=='c_100':
                    results.append(c100_label)

            if slen==1:
                results.append('<span>&nbsp;</span>')
            
            if len(results)==1:
                first=results[0]

            if len(results)>=1:
                first=results[0]
                second=results[1]

            if len(results)<=0:
                first=''
                secont=''

            displaydata.append({'designer':designer,'first':first,'second':second,'dt':c['dt']})

    outputText = template.render(displaydata=displaydata) 
    return outputText

#fw=codecs.open('c:/tmp/output.html','w','utf-8')
#fw.write(outputText)
#fw.close()
#os.startfile('c:/tmp/output.html')

#print(outputText)