import codecs
import dataset
import json

#db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/hhh?charset=utf8mb4')
db = dataset.connect('mysql://hhh7796hhh:lYmWsu^ujcA1@127.0.0.1:13317/xoops?charset=utf8mb4')

cursor=db.query("SELECT hdesigner_id,background,title,name,img_path,region,type,style,budget,area,special,idea,service_phone,phone,address,fax,mail,website,fbpageurl FROM xoops._hdesigner")
ddict={}
for c in cursor:
    ddict[str(c['hdesigner_id'])]={'workBannerImg':c['background'],'WorkName':c['title'],'DesignerName':c['name'],'Designerimg':c['img_path'],'WorkLoc':c['region'],'WorkType':c['type'],'WorkStyle':c['style'],'WorkBudget':c['budget'],'WorkSize':c['area'],'WorkSoho':c['special'],'DesignIdea1':c['idea'],'TollFreeCall':c['service_phone'],'Telephone':c['phone'],'Address':c['address'],'Fax':c['fax'],'Email':c['mail'],'Web':c['website'],'Facebook':c['fbpageurl']}
#    print(c['background'])

fr=codecs.open('c:/tmp/andy/id.txt','r','utf-8')
lines=fr.readlines()
fr.close()
lst=[]
for l in lines:
    elmts=l.split(":")
    if len(elmts)>1:
        txt=elmts[1]
        e2=txt.split('"')
        lst.append(e2[1])
        cursor=db.query("SELECT cover,caption,hcase_id FROM xoops._hcase where hdesigner_id='"+e2[1]+"'")
        ddict[e2[1]]['workimg']=[]
        for c in cursor:
            ddict[e2[1]]['workimg'].append({'img':c['cover'],'text':c['caption'],'workSrc':'https://hhh.com.tw/cases/detail/d/'+str(c['hcase_id'])+'/'})

        cursor=db.query("select  i.`name`,c.ctitle,i.hcolumn_id FROM xoops._hcolumn c, xoops._hcolumn_img i where i.hcolumn_id=c.hcolumn_id and  c.hdesigner_ids = '"+e2[1]+"' ;")
        ddict[e2[1]]['workMethod']=[]
        for c in cursor:
            ddict[e2[1]]['workMethod'].append({'Methodimg':c['name'],'Methodtext':c['ctitle'],'Methodsrc':'https://hhh.com.tw/columns/detail/'+str(c['hcolumn_id'])+'/'})

        cursor=db.query("select youtube_img,title,youtube_video_id FROM xoops.youtube_list where hdesigner_id = '"+e2[1]+"' ;")
        ddict[e2[1]]['workVideo']=[]
        for c in cursor:
            ddict[e2[1]]['workVideo'].append({'video':c['youtube_img'],'videotext':c['title'],'videoSrc':'https://youtu.be/'+str(c['youtube_video_id'])})



finaldata=[]
for l in lst:
    entry={"id":l,'workBannerImg':ddict[l]['workBannerImg'],'WorkName':ddict[l]['WorkName'],'DesignerName':ddict[l]['DesignerName'],'Designerimg':ddict[l]['Designerimg'],'WorkLoc':ddict[l]['WorkLoc'],'WorkType':ddict[l]['WorkType'],'WorkStyle':ddict[l]['WorkStyle'],'WorkBudget':ddict[l]['WorkBudget'],'WorkSize':ddict[l]['WorkSize'],'WorkSoho':ddict[l]['WorkSoho'],'DesignIdea1':ddict[l]['DesignIdea1'],'TollFreeCall':ddict[l]['TollFreeCall'],'Telephone':ddict[l]['Telephone'],'Address':ddict[l]['Address'],'Fax':ddict[l]['Fax'],'Email':ddict[l]['Email'],'Web':ddict[l]['Web'],'Facebook':ddict[l]['Facebook'],'workimg':ddict[l]['workimg'],'workVideo':ddict[l]['workVideo'],'workMethod':ddict[l]['workMethod']}
    finaldata.append(entry)

fw=codecs.open('c:/tmp/designer.json','w','utf-8')
fw.write(json.dumps(finaldata))
fw.close()
#print(entry)