import os
from typing import Optional
import mysql.connector
from dataset.util import ResultIter
from fastapi import FastAPI
from fastapi.middleware.cors import CORSMiddleware

from pytrends.request import TrendReq
from datetime import tzinfo
import datetime
from io import BytesIO
from fastapi.responses import StreamingResponse
import xlsxwriter
import pandas as pd
import dataset
import html
import json
from pytube import extract
import re
from fastapi import BackgroundTasks, FastAPI
app = FastAPI()

origins = [
    "*"
]
app.add_middleware(
    CORSMiddleware,
    allow_origins=origins,
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

hhhMBPath = '../hhh-home-mb'
hhhPCPath = '../hhh-home-pc'
# connstr = 'mysql://hhh7796hhh:lYmWsu^ujcA1@hhh-v57.cmab1ctkglka.ap-northeast-2.rds.amazonaws.com:3306/xoops?charset=utf8mb4'
#2022/12/13 change
connstr = 'mysql://hhh7796hhh:lYmWsu^ujcA1@ec2-3-35-26-49.ap-northeast-2.compute.amazonaws.com:3306/xoops?charset=utf8mb4'



def ExecuteQuery(isql):
    #2022/12/13 change
    #host='hhh-v57.cmab1ctkglka.ap-northeast-2.rds.amazonaws.com',
    with mysql.connector.connect(
        host='ec2-3-35-26-49.ap-northeast-2.compute.amazonaws.com',
        database='xoops',
        user='hhh7796hhh',
        password='lYmWsu^ujcA1',
        use_unicode=True,
        charset='utf8',
        collation='utf8_unicode_ci'
    ) as connection :
        with connection.cursor(dictionary=True) as cursor:
    # connection.set_charset_collation('utf8','utf8_general_ci')
            #cursor = connection.cursor(dictionary=True)
            cursor.execute(isql)
            return cursor.fetchall()

def ExecuteCmd(isql):
    #2022/12/13 change
    #host='hhh-v57.cmab1ctkglka.ap-northeast-2.rds.amazonaws.com',
    with mysql.connector.connect(
        host='ec2-3-35-26-49.ap-northeast-2.compute.amazonaws.com',
        database='xoops',
        user='hhh7796hhh',
        password='lYmWsu^ujcA1',
        use_unicode=True,
        charset='utf8',
        collation='utf8_unicode_ci'
    ) as connection:
        cursor = connection.cursor(dictionary=True)
        cursor.execute(isql)
        print(cursor.rowcount)
        connection.commit()
        return None

@app.get("/ExportExecuteDetail")
async def ExportExecuteDetail():

    with dataset.connect(connstr) as db:
        output = BytesIO()
        records = db.query(""" select f.exf_id, num 合約, company 合約公司,lv1 大項目,lv2 執行項, contract_time 合約到期日,price 金額,sales_man 業務,quota 額度,creator 建立者,is_close 狀態,sdate 上架日期,edate 下架日期,f.note 備註,designer 設計師,mobile 手機,telete 電話,contract_person 聯絡人,detail_status 合約名稱,d.create_time 建立時間,d.update_time 更新時間,last_update 最後更新 from execute_form f
    left join execute_detail d on f.exf_id=d.exf_id
    where f.is_delete='N' order BY f.exf_id DESC, exd_id
    """)

        df = pd.DataFrame(list(records))
        writer = pd.ExcelWriter(output)
        df.to_excel(writer, sheet_name='bar')
        writer.save()


        """ workbook = xlsxwriter.Workbook(output)
        worksheet = workbook.add_worksheet()

        for cols in records:
            worksheet.write(0, 0, 'ISBN')
            worksheet.write(0, 1, 'Name')
            worksheet.write(0, 2, 'Takedown date')
            worksheet.write(0, 3, 'Last updated')
            
        workbook.close() """
        output.seek(0)

        headers = {
            'Content-Disposition': 'attachment; filename="execute_detail_all.xlsx"'
        }
        return StreamingResponse(output, headers=headers, media_type='application/octet-stream')


@app.get("/")
def read_root():
    return {"Hello": "World"}


@app.get("/movexoopstostage")
def movexoopstostage(designerid: str = "0", caseid: str = "0"):
    with dataset.connect(connstr) as db:
        db.query(
            "replace INTO stage._hdesigner SELECT * FROM xoops._hdesigner WHERE hdesigner_id IN ('" + designerid.replace(',', "','")+"');")
        db.query(
            "replace INTO stage._hcase SELECT * FROM xoops._hcase WHERE hcase_id IN ('"+caseid.replace(',', "','")+"');")
        db.query(
            "replace INTO stage._hcase_img SELECT * FROM xoops._hcase_img WHERE hcase_id IN ('"+caseid.replace(',', "','")+"');")

        return {"success"}


@app.get("/movepxoopstostage")
def movepxoopstostage(brandid: str = "0", productid: str = "0"):
    with dataset.connect(connstr) as db:
        db.query(
            "replace INTO stage._hbrand SELECT * FROM xoops._hbrand WHERE hbrand_id IN ('" + brandid.replace(',', "','")+"');")
        db.query(
            "replace INTO stage._hbrand_page SELECT * FROM xoops._hbrand_page WHERE hbrand_id IN ('" + brandid.replace(',', "','")+"');")
        db.query(
            "replace INTO stage._hproduct SELECT * FROM xoops._hproduct WHERE id IN ('"+productid.replace(',', "','")+"');")
        db.query(
            "replace INTO stage._hproduct_img SELECT * FROM xoops._hproduct_img WHERE hproduct_id IN ('"+productid.replace(',', "','")+"');")

        return {"success"}


@app.get("/movecxoopstostage")
def movecxoopstostage(columnid: str = "0"):
    with dataset.connect(connstr) as db:
        db.query(
            "replace INTO stage._hcolumn SELECT * FROM xoops._hcolumn WHERE hcolumn_id IN ('" + columnid.replace(',', "','")+"');")
        db.query(
            "replace INTO stage._hcolumn_img SELECT * FROM xoops._hcolumn_img WHERE hcolumn_id IN ('" + columnid.replace(',', "','")+"');")
        db.query(
            "replace INTO stage._hcolumn_page SELECT * FROM xoops._hcolumn_page WHERE hcolumn_id IN ('" + columnid.replace(',', "','")+"');")

        return {"success"}


@app.get("/genjson")
def genjson(filename: str = "realtime.json"):
    
    jData = json.load(open(hhhMBPath+'/json/data.json', encoding='utf8'))

    records = ExecuteQuery("SELECT * FROM _had where (now() between start_time and end_time or ( start_time is null and end_time is null) or ( start_time = '0000-00-00 00:00:00' and end_time = '0000-00-00 00:00:00')) and onoff='1' and adtype like '首八大%' ")

    for x in jData:

        # 頂部輪播區-新刊頭
        if x['id'] == 0:

            records = ExecuteQuery("""SELECT adlogo lo,adlogo_mobile mlo, adhref lk, adlogo_mobile_webp lomwebp, adlogo_webp dwebp FROM _had
                        WHERE adtype LIKE '新刊頭%'
                        AND onoff='1'
                        AND(NOW() BETWEEN start_time AND end_time OR(start_time='0000-00-00 00:00:00' and end_time='0000-00-00 00:00:00') or (start_time is null and end_time is NULL))
                        ORDER BY cast(SUBSTR(adtype,4) AS DECIMAL)""")

            x["data"] = []
            for c in records:
                a = {'imgUrl': c['mlo'], 'link': str(
                    c['lk']), 'DimgUrl': c['lo'], 'webp': str(c['lomwebp']), 'Dwebp': str(c['dwebp'])}
                x["data"].append(a)
            # print(x["data"])

        # 主要輪播區-首八大
        if x['id'] == 1:
            records = ExecuteQuery("""SELECT adlogo lo,adlogo_mobile mlo, adhref lk, adlogo_mobile_webp lomwebp, adlogo_webp dwebp FROM _had
                        WHERE adtype LIKE '首八大%'
                        AND onoff='1'
                        AND(NOW() BETWEEN start_time AND end_time OR(start_time='0000-00-00 00:00:00' and end_time='0000-00-00 00:00:00') or (start_time is null and end_time is NULL))
                        ORDER BY cast(SUBSTR(adtype,4) AS DECIMAL)""")

            x["data"] = []
            for c in records:
                a = {'imgUrl': c['mlo'], 'link': str(
                    c['lk']), 'DimgUrl': c['lo'], 'webp': str(c['lomwebp']), 'Dwebp': str(c['dwebp'])}
                x["data"].append(a)
            # print(x["data"])

        #tab區塊-最夯設計, 影音實錄, 專欄文章
        if x['id'] == 2:
            x["data"] = []
            records = ExecuteQuery("""SELECT caption TT ,cover IMG, CONCAT('https://hhh.com.tw/cases/detail/',hcase_id,'/') LK, short_desc txt
                            from _hcase
                            left join _hdesigner ON _hcase.hdesigner_id=_hdesigner.hdesigner_id
                            WHERE
                            _hcase.onoff='1' AND _hdesigner.onoff='1'
                            AND(NOW() > sdate)
                            ORDER BY hcase_id DESC
                            LIMIT 3""")
            a = {'tab': '最夯設計', 'data': []}
            for c in records:
                ad = {'imgUrl': c['IMG'], 'link': c['LK'],
                    'title': c['TT'], 'description': c['txt']}
                a['data'].append(ad)
            x["data"].append(a)

            records = ExecuteQuery("""SELECT title TT,iframe IMG , CONCAT('https://hhh.com.tw/video-post.php?id=',hvideo_id) LK , name
                            from _hvideo where display_datetime < NOW()
                            ORDER BY hvideo_id DESC
                            LIMIT 4""")
            a = {'tab': '影音實錄', 'data': []}
            cnt = 0
            for c in records:
                if cnt == 0:
                    cnt += 1
                    continue
                tid = extract.video_id(c['IMG'])
                timg = "https://img.youtube.com/vi/" + tid+"/hqdefault.jpg"
                ad = {'imgUrl': timg, 'link': c['LK'],
                    'title': c['name'], 'description': c['TT']}
                a['data'].append(ad)
            x["data"].append(a)

            records = ExecuteQuery("""SELECT ctitle TT,clogo IMG, CONCAT('https://hhh.com.tw/columns/detail/',hcolumn_id,'/') LK, cdesc
                            from _hcolumn
                            WHERE onoff='1'
                            AND NOW() > sdate
                            ORDER BY hcolumn_id DESC
                            LIMIT 3""")
            a = {'tab': '專欄文章', 'data': []}
            for c in records:
                ad = {'imgUrl': c['IMG'], 'link': c['LK'],
                    'title': c['TT'], 'description': c['cdesc']}
                a['data'].append(ad)
            x["data"].append(a)
            # print(x["data"])

        # 主題企劃區
        if x['id'] == 3:
            records = ExecuteQuery("""SELECT logo lo, CONCAT('https://hhh.com.tw/topic/detail/',htopic_id,'/') lk, `desc`, title FROM _htopic
WHERE onoff = '1'
ORDER BY htopic_id DESC limit 3""")

            x["data"] = []
            for c in records:
                a = {'imgUrl': c['lo'], 'link': str(
                    c['lk']), 'video': 'false', 'description': c['desc'], 'title': c['title']}
                x["data"].append(a)
            # print(x["data"])

        # 編輯精選
        if x['id'] == 4:
            records = ExecuteQuery("""SELECT hcolumn_id, ctitle, clogo,cdesc
                                FROM homepage_set
                                LEFT JOIN _hcolumn ON mapping_id = hcolumn_id
                                WHERE outer_set=8
                                AND homepage_set.onoff='Y'
                                AND(NOW() BETWEEN homepage_set.start_time AND homepage_set.end_time OR(homepage_set.start_time='0000-00-00 00:00:00' and homepage_set.end_time='0000-00-00 00:00:00') or (homepage_set.start_time is null and homepage_set.end_time is NULL))
                                ORDER BY inner_sort""")

            x["data"] = []
            for c in records:
                a = {'imgUrl': c['clogo'], 'link': "https://hhh.com.tw/columns/detail/" + str(
                    c['hcolumn_id']) + "/", 'title': c['ctitle'], 'video': 'false', 'description': c['cdesc']}
                x["data"].append(a)
            # print(x["data"])

        # 首列表廣告
        if x['id'] == 5:
            records = ExecuteQuery("""SELECT adlogo lo,adlogo_mobile mlo, adhref lk, adlogo_mobile_webp lomwebp, adlogo_webp dwebp FROM _had
                        WHERE adtype LIKE '首列表廣告%'
                        AND onoff='1'
                        AND(NOW() BETWEEN start_time AND end_time OR(start_time='0000-00-00 00:00:00' and end_time='0000-00-00 00:00:00') or (start_time is null and end_time is NULL))
                        ORDER BY adtype""")

            x["data"] = []
            for c in records:
                a = {'imgUrl': c['mlo'], 'link': str(
                    c['lk']), 'DimgUrl': c['lo'], 'webp': str(c['lomwebp']), 'Dwebp': str(c['dwebp'])}
                x["data"].append(a)
            # print(x["data"])

        # 來選好物區
        if x['id'] == 6:

            records = ExecuteQuery(
                "SELECT max_row from outer_site_set WHERE title='來選好貨'")

            maxrow = 1
            for c in records:
                maxrow = c['max_row']

            records = ExecuteQuery("""(SELECT theme_type, mapping_id, IFNULL(ifnull(ifnull(_hcase.caption,_hcolumn.ctitle),_hproduct.name),_hvideo.title) COLLATE utf8_general_ci caption  , IFNULL(ifnull(_hcase.cover,_hcolumn.clogo),_hproduct.cover) COLLATE utf8_general_ci J, iframe , IFNULL(ifnull(ifnull(_hcase.short_desc,_hcolumn.cdesc),_hproduct.descr),_hvideo.`desc`) COLLATE utf8_general_ci short_desc
, (case when theme_type='case' then CONCAT('https://hhh.com.tw/cases/detail/d/',mapping_id) when theme_type='column' then CONCAT('https://hhh.com.tw/columns/detail/',mapping_id) when theme_type='product' then CONCAT('https://hhh.com.tw/product-post.php?id=',mapping_id) when theme_type='video' then CONCAT('https://hhh.com.tw/video-post.php?id=',mapping_id) ELSE '' END) url
-- SELECT *
FROM homepage_set
left join _hcase ON _hcase.hcase_id=homepage_set.mapping_id AND theme_type='case'-- AND _hcase.onoff = '1'
LEFT JOIN _hproduct ON mapping_id = _hproduct.id AND theme_type='product'-- AND _hproduct.onoff = '1'
LEFT JOIN _hcolumn ON mapping_id = _hcolumn.hcolumn_id AND theme_type='column'-- AND _hcolumn.onoff = '1'
LEFT JOIN _hvideo ON mapping_id = _hvideo.hvideo_id AND theme_type='video'
WHERE homepage_set.onoff='Y'
AND outer_set = (SELECT oss_id from outer_site_set WHERE title='來選好貨')
AND(NOW() BETWEEN homepage_set.start_time AND homepage_set.end_time OR(homepage_set.start_time='0000-00-00 00:00:00' and homepage_set.end_time='0000-00-00 00:00:00') or (homepage_set.start_time is null and homepage_set.end_time is NULL))
ORDER BY outer_set, inner_sort)
UNION
(SELECT 'product', id, `name`, cover, NULL ,descr ,CONCAT('https://hhh.com.tw/product-post.php?id=',id) FROM _hproduct WHERE onoff='1' ORDER BY id DESC LIMIT """ + str(maxrow) + """)
                                LIMIT """ + str(maxrow))

            x["data"] = []
            for c in records:
                #print(c)
                if c['iframe'] is None:
                    if isinstance(c['J'], bytearray) or isinstance(c['J'], bytes):
                        c['J'] = c['J'].decode('utf8')
                    if isinstance(c['caption'], bytearray) or isinstance(c['caption'], bytes):
                        c['caption'] = c['caption'].decode('utf8')
                    if isinstance(c['short_desc'], bytearray) or isinstance(c['short_desc'], bytes):
                        c['short_desc'] = c['short_desc'].decode('utf8')
                    a = {'imgUrl': c['J'], 'link': c['url'], 'title': c['caption'],
                        'description': c['short_desc'], 'video': 'false'}
                else:
                    tid = extract.video_id(str(c['iframe']))
                    timg = "https://img.youtube.com/vi/" + tid+"/hqdefault.jpg"
                    ccaption = ""
                    cdescription = ""
                    if isinstance(c['caption'], bytearray):
                        ccaption = str(c['caption'].decode('utf8'))
                    else:
                        ccaption = str(c['caption'])
                    if c['short_desc'] is not None:
                        if isinstance(c['short_desc'], bytes):
                            cdescription = str(c['short_desc'].decode('utf8'))
                        else:
                            cdescription = str(c['short_desc'])
                    a = {'imgUrl': timg, 'link': c['url'], 'title': ccaption,
                        'description': cdescription, 'video': tid}
                x["data"].append(a)
            # print(x["data"])

        # 本週推薦
        if x['id'] == 7:
            records = ExecuteQuery(
                "SELECT max_row from outer_site_set WHERE title='本週推薦'")

            maxrow = 1
            for c in records:
                maxrow = c['max_row']

            records = ExecuteQuery("""SELECT theme_type, mapping_id, IFNULL(ifnull(ifnull(ifnull(_hcase.caption,_hcolumn.ctitle),_hproduct.name),_hvideo.title),_hbrand.title) caption , 
                IFNULL(ifnull(ifnull(_hcase.cover,_hcolumn.clogo),_hproduct.cover),_hbrand.logo) J, iframe , IFNULL(ifnull(ifnull(ifnull(_hcase.short_desc,_hcolumn.cdesc),_hproduct.descr),_hvideo.`desc`),_hbrand.intro) short_desc
, (case when theme_type='case' then CONCAT('https://hhh.com.tw/cases/detail/d/',mapping_id) when theme_type='column' then CONCAT('https://hhh.com.tw/columns/detail/',mapping_id) when theme_type='product' then CONCAT('https://hhh.com.tw/product-post.php?id=',mapping_id) when theme_type='video' then CONCAT('https://hhh.com.tw/video-post.php?id=',mapping_id) when theme_type='brand' then CONCAT('https://hhh.com.tw/brand-index.php?brand_id=',mapping_id) ELSE '' END) url
-- SELECT *
FROM homepage_set
left join _hcase ON _hcase.hcase_id=homepage_set.mapping_id AND theme_type='case'-- AND _hcase.onoff = '1'
LEFT JOIN _hproduct ON mapping_id = _hproduct.id AND theme_type='product'-- AND _hproduct.onoff = '1'
LEFT JOIN _hcolumn ON mapping_id = _hcolumn.hcolumn_id AND theme_type='column'-- AND _hcolumn.onoff = '1'
LEFT JOIN _hvideo ON mapping_id = _hvideo.hvideo_id AND theme_type='video'
LEFT JOIN _hbrand ON mapping_id = _hbrand.hbrand_id AND theme_type='brand'
WHERE homepage_set.onoff='Y'
AND outer_set = (SELECT oss_id from outer_site_set WHERE title='本週推薦')
AND(NOW() BETWEEN homepage_set.start_time AND homepage_set.end_time OR(homepage_set.start_time='0000-00-00 00:00:00' and homepage_set.end_time='0000-00-00 00:00:00') or (homepage_set.start_time is null and homepage_set.end_time is NULL))
ORDER BY outer_set, inner_sort
                            LIMIT """ + str(maxrow))

            x["data"] = []
            for c in records:
                if c['iframe'] is None:
                    if isinstance(c['J'], bytearray) or isinstance(c['J'], bytes):
                        c['J'] = c['J'].decode('utf8')
                    if isinstance(c['caption'], bytearray) or isinstance(c['caption'], bytes):
                        c['caption'] = c['caption'].decode('utf8')
                    if isinstance(c['short_desc'], bytearray) or isinstance(c['short_desc'], bytes):
                        c['short_desc'] = c['short_desc'].decode('utf8')
                    a = {'imgUrl': c['J'], 'link': c['url'], 'title': c['caption'],
                        'description': c['short_desc'], 'video': 'false'}
                else:
                    tid = extract.video_id(str(c['iframe']))
                    timg = "https://img.youtube.com/vi/" + tid+"/hqdefault.jpg"
                    ccaption = ""
                    cdescription = ""
                    if isinstance(c['caption'], bytearray):
                        ccaption = str(c['caption'].decode('utf8'))
                    else:
                        ccaption = str(c['caption'])
                    if c['short_desc'] is not None:
                        if isinstance(c['short_desc'], bytes):
                            cdescription = str(c['short_desc'].decode('utf8'))
                        else:
                            cdescription = str(c['short_desc'])
                    a = {'imgUrl': timg, 'link': c['url'], 'title': ccaption,
                        'description': cdescription, 'video': tid}
                x["data"].append(a)
            # print(x["data"])

        # 粉絲推薦
        if x['id'] == 8:

            records = ExecuteQuery(
                "SELECT max_row from outer_site_set WHERE title='粉絲推薦'")

            maxrow = 1
            for c in records:
                maxrow = c['max_row']

            records = ExecuteQuery("""SELECT theme_type, mapping_id, IFNULL(ifnull(ifnull(ifnull(_hcase.caption,_hcolumn.ctitle),_hproduct.name),_hvideo.title),_hbrand.title) caption , 
                IFNULL(ifnull(ifnull(_hcase.cover,_hcolumn.clogo),_hproduct.cover),_hbrand.logo) J, iframe , IFNULL(ifnull(ifnull(ifnull(_hcase.short_desc,_hcolumn.cdesc),_hproduct.descr),_hvideo.`desc`),_hbrand.intro) short_desc
, (case when theme_type='case' then CONCAT('https://hhh.com.tw/cases/detail/d/',mapping_id) when theme_type='column' then CONCAT('https://hhh.com.tw/columns/detail/',mapping_id) when theme_type='product' then CONCAT('https://hhh.com.tw/product-post.php?id=',mapping_id) when theme_type='video' then CONCAT('https://hhh.com.tw/video-post.php?id=',mapping_id) when theme_type='brand' then CONCAT('https://hhh.com.tw/brand-index.php?brand_id=',mapping_id) ELSE '' END) url
-- SELECT *
FROM homepage_set
left join _hcase ON _hcase.hcase_id=homepage_set.mapping_id AND theme_type='case'-- AND _hcase.onoff = '1'
LEFT JOIN _hproduct ON mapping_id = _hproduct.id AND theme_type='product'-- AND _hproduct.onoff = '1'
LEFT JOIN _hcolumn ON mapping_id = _hcolumn.hcolumn_id AND theme_type='column'-- AND _hcolumn.onoff = '1'
LEFT JOIN _hvideo ON mapping_id = _hvideo.hvideo_id AND theme_type='video'
LEFT JOIN _hbrand ON mapping_id = _hbrand.hbrand_id AND theme_type='brand'
WHERE homepage_set.onoff='Y'
AND outer_set = (SELECT oss_id from outer_site_set WHERE title='粉絲推薦')
AND(NOW() BETWEEN homepage_set.start_time AND homepage_set.end_time OR(homepage_set.start_time='0000-00-00 00:00:00' and homepage_set.end_time='0000-00-00 00:00:00') or (homepage_set.start_time is null and homepage_set.end_time is NULL))
ORDER BY outer_set, inner_sort
                            LIMIT """ + str(maxrow))

            x["data"] = []
            for c in records:
                if c['iframe'] is None:
                    if isinstance(c['J'], bytearray) or isinstance(c['J'], bytes):
                        c['J'] = c['J'].decode('utf8')
                    if isinstance(c['caption'], bytearray) or isinstance(c['caption'], bytes):
                        c['caption'] = c['caption'].decode('utf8')
                    if isinstance(c['short_desc'], bytearray) or isinstance(c['short_desc'], bytes):
                        c['short_desc'] = c['short_desc'].decode('utf8')
                    a = {'imgUrl': c['J'], 'link': c['url'], 'title': c['caption'],
                        'description': c['short_desc'], 'video': 'false'}
                else:
                    tid = extract.video_id(str(c['iframe']))
                    timg = "https://img.youtube.com/vi/" + tid+"/hqdefault.jpg"
                    ccaption = ""
                    cdescription = ""
                    if isinstance(c['caption'], bytearray):
                        ccaption = str(c['caption'].decode('utf8'))
                    else:
                        ccaption = str(c['caption'])
                    if c['short_desc'] is not None:
                        if isinstance(c['short_desc'], bytes):
                            cdescription = str(c['short_desc'].decode('utf8'))
                        else:
                            cdescription = str(c['short_desc'])
                    a = {'imgUrl': timg, 'link': c['url'], 'title': ccaption,
                        'description': cdescription, 'video': tid}
                x["data"].append(a)
            # print(x["data"])

        if x['id'] == 9:
            records = ExecuteQuery(
                "SELECT id, (case when youtube_title = '' OR youtube_title IS NULL then (SELECT title FROM _hvideo where display_datetime < NOW() ORDER BY hvideo_id DESC LIMIT 1 ) ELSE youtube_title END) T, (case when youtube_id = '' OR youtube_id IS NULL then (SELECT iframe FROM _hvideo where display_datetime < NOW() ORDER BY hvideo_id DESC LIMIT 1) ELSE youtube_id end) Y FROM site_setup")

            for c in records:
                x['title'] = ""
                if isinstance(c['T'], bytearray):
                    x['title'] = str(c['T'].decode('utf8'))
                else:
                    x['title'] = str(c['T'])
                x['yt'] = extract.video_id(str(c['Y']))
                # print(id)

        if x['id'] == 10:
            records = ExecuteQuery(
                "SELECT all_search_tag ast FROM site_setup")

            x["data"] = []
            for c in records:
                x["data"] = c['ast'].split(',')
                # print(id)

    # print(jData)
    """ if not os.path.exists(hhhMBPath):
        os.mkdir(hhhMBPath)
    with open(hhhMBPath+'/json/' + filename, 'w', encoding='utf-8') as f:
        json.dump(jData, f, ensure_ascii=False, indent=4)

    if not os.path.exists(hhhPCPath):
        os.mkdir(hhhPCPath)
    with open(hhhPCPath+'/json/' + filename, 'w', encoding='utf-8') as f:
        json.dump(jData, f, ensure_ascii=False, indent=4) """
    
    return jData


@app.get("/gencase")
def gencase(id: str = "14151", sort: str = "new", page: str="1"):

    with dataset.connect(connstr) as db:
        jData = json.load(open(hhhMBPath+'/json/cases.json', encoding='utf8'))

        records = db.query("""SELECT *, c.style cstyle, c.style2 cstyle2 FROM _hcase c
    LEFT JOIN _hdesigner d ON c.hdesigner_id = d.hdesigner_id
    WHERE c.hcase_id = '""" + id + """'  AND c.onoff='1' AND d.onoff='1' and c.sdate < now() """)

        # print(jData)
        for x in jData:
            tmpCaseDetail = []
            icount = 0
            for c in records:
                icount += 1
                #tmpCaseDetail.append({"CaseDetailImg": c["cimg"]})
            #x["CaseDetail"] = tmpCaseDetail
                if c != None:
                    x["designerid"] = str(c["hdesigner_id"])
                    x["CaseId"] = str(c["hcase_id"])
                    x["Casetitle"] = c["caption"]
                    x["CaseTeamName"] = c["name"]
                    x["CaseCompany"] = c["title"]
                    x["CaseCompanyAddress"] = c["address"]
                    x["CaseCompanyTel"] = c["phone"]
                    x["CaseCompanyEmail"] = c["mail"]
                    x["CaseCompanyWeb"] = c["website"]
                    x["CaseDate"] = str(c["sdate"])
                    x["CaseViews"] = c["viewed"]
                    x["CaseCoverImg"] = c["cover"]
                    x["CaseImgAmount"] = icount
                    x["CaseStyle"] = c["cstyle"]
                    x["CaseHouse"] = c["layout"]
                    x["CaseSize"] = c["area"]
                    x["CaseProject"] = ""
                    x["CaseDataMember"] = c["member"]
                    x["CaseDataSize"] = c["area"]
                    x["CaseDataStyle"] = c["cstyle"] + c["cstyle2"]
                    x["CaseDataType"] = c["type"]
                    x["CaseDataSituation"] = c["condition"]
                    x["CaseDataImgProvide"] = c["provider"]
                    x["CaseDataSpace"] = c["layout"]
                    x["CaseDataMaterial"] = c["materials"]
                    x["ContactFreeTel"] = c["service_phone"]
                    x["ContactDesignerImg"] = c["img_path"]
                    x["CasePageLink"] = ""
                    x["CasePageprev"] = ""
                    #x["CaseTag"]= []
                    
                    #相同設計師的個案
                    sql = """SELECT * FROM _hcase c
            WHERE hdesigner_id = '""" + x["designerid"] + """' and hcase_id <> '""" + x["CaseId"] + """' and sdate < now()  AND c.onoff='1'
            ORDER BY """ + ("sdate" if sort == 'new' else 'viewed') + """ DESC
            LIMIT """ + str((int(page) - 1)*12) + """,12
            """
                    cases = db.query(sql)
                
                    tmpOtherCases = []
                    for other in cases:
                        tmpOtherCase = {}
                        tmpOtherCase["designerid"] = str(other["hdesigner_id"])
                        tmpOtherCase["casesid"] = str(other["hcase_id"])
                        tmpOtherCase["Views"] = other["viewed"]
                        tmpOtherCase["ProfileImg"] = other["cover"]
                        tmpTags = []
                        for tag in other["tag"].split(','):
                            tmpTags.append({"Tag": tag , "TagLink": "" })
                        tmpOtherCase["ProfileTag"] = tmpTags
                        tmpOtherCases.append(tmpOtherCase)
                        
                    x["DesignerProfile"] = tmpOtherCases

                    #相同風格的個案
                    sql = """SELECT * FROM _hcase c
            WHERE style = '""" + x["CaseStyle"] + """' and hcase_id <> '""" + x["CaseId"] + """' and sdate < now()  AND c.onoff='1'
            ORDER BY """ + ("sdate" if sort == 'new' else 'viewed') + """ DESC
            LIMIT """ + str((int(page) - 1)*12) + """,12
            """
                    cases = db.query(sql)
                    
                    tmpOtherCases = []
                    for other in cases:
                        tmpOtherCase = {}
                        tmpOtherCase["designerid"] = str(other["hdesigner_id"])
                        tmpOtherCase["casesid"] = str(other["hcase_id"])
                        tmpOtherCase["Views"] = other["viewed"]
                        tmpOtherCase["ProfileImg"] = other["cover"]
                        tmpTags = []
                        for tag in other["tag"].split(','):
                            tmpTags.append({"Tag": tag , "TagLink": "" })
                        tmpOtherCase["ProfileTag"] = tmpTags
                        tmpOtherCases.append(tmpOtherCase)
                        
                    x["StyleProfile"] = tmpOtherCases

                    #相同風格的RANDOM 10筆
                    #cases = db.query("""SELECT * FROM _hcase c
            #WHERE style = '""" + x["CaseStyle"] + """' and hcase_id <> '""" + x["CaseId"] + """' and sdate < now()  AND c.onoff='1'
            #ORDER BY RAND()
            #LIMIT 10
            #""")
                    """ tmpOtherCases = []
                    for other in cases:
                        tmpOtherCase = {}
                        tmpOtherCase["designerid"] = str(other["hdesigner_id"])
                        tmpOtherCase["casesid"] = str(other["hcase_id"])
                        tmpOtherCase["PortfoliolImg"] = other["cover"]
                        tmpOtherCase["PortfoliolLink"] = ""
                        tmpOtherCase["PortfoliolImgAlt"] = other["caption"]
                        tmpOtherCases.append(tmpOtherCase)
                    x["OtherStylePortfolio"]= tmpOtherCases """

        # print(x)

        # print(jData)
        """ if not os.path.exists(hhhMBPath):
            os.mkdir(hhhMBPath)
        with open(hhhMBPath+'/json/cases-' + id + '.json', 'w', encoding='utf-8') as f:
            json.dump(jData, f, ensure_ascii=False, indent=4) """

        return jData

@app.get("/gendesigner")
async def gendesigner(id: str = "447", sort: str = "new", page: str = "1"):

    with dataset.connect(connstr) as db:
        jData = json.load(open(hhhMBPath+'/json/designers.json', encoding='utf8'))

        records = db.query("""SELECT * FROM _hdesigner d
    WHERE d.hdesigner_id = '""" + id + """' AND d.onoff='1' """)
        
        check_len=[]
        for c in records:
            check_len.append(c)
        if len(check_len)==0:
            return []

        # print(jData)
        for x in jData:
            tmpCaseDetail = []
            icount = 0
            for c in records:
                icount += 1
                # tmpCaseDetail.append({"CaseDetailImg":c["cimg"]})

            if page == "1":
                x["id"] = c["hdesigner_id"]
                x["premium"] = c["premium"]
                x["BannerImg"] = c["background"]
                x["BannerImg_mobile"] = c['background_mobile']
                x["CompanyName"] = c["title"].replace("\n","<br>")
                x["DesignerName"] = c["name"]
                x["Designerimg"] = c["img_path"]
                x["Description"] = c["seo"]
                x["Approve"] = c["position"]
                x["FB_link"] = c["fbpageurl"]
                # x["order_computer"] = c["order_computer"]
                # x["order_mb"] = c["order_mb"]
                if c["service_phone"]!='':
                    if ',' not in c["service_phone"]:
                        service_phone = c["service_phone"]
                    else:
                        part1=c["service_phone"][0:4]
                        part2=c["service_phone"][4:7]
                        part3=c["service_phone"][7:10]
                        part4=c["service_phone"].split(',')[1]
                        service_phone = part1+'-'+part2+'-'+part3+'#'+part4
                else:
                    service_phone=''
                phone_list=[]
                for k1 in c["phone"].split(','):
                    phone_list.append({"title": "諮詢專線：", "link":'tel:'+k1, "data":k1})

                x["Basics"] = []
                if service_phone!='':
                    x["Basics"].append({"title": "免費專線：","link": 'tel:'+c["service_phone"], "data": service_phone})
                if c["phone"]!='':
                    for k2 in phone_list:
                        x["Basics"].append(k2)
                if len(c["fax"])!=0:
                    x["Basics"].append({"title": "公司傳真：", "link": "fax:"+c["fax"], "data": c["fax"]})
                if len(c["address"])!=0:
                    x["Basics"].append({"title": "公司地址：", "link": "https://www.google.com/search?q="+c["address"], "data": c["address"]})
                if len(c["mail"])!=0:
                    x["Basics"].append({"title": "電子信箱：", "link": "mailto:"+c["mail"], "data": c["mail"]})
                if len(c["website"])!=0:
                    x["Basics"].append({"title": "公司網址：", "link": c["website"], "data": c["website"]})
                blog_list=re.findall(r'href="(.*?)"',c["blog"])
                if len(blog_list)==0:
                    blog_list=re.findall(r'http.*',c["blog"]) 
                if len(blog_list)!=0:
                    for k3 in blog_list:
                        x["Basics"].append({"title": "其他連結：", "link": k3, "data": k3})
                
                # x["Basics"] = [
                #     {"title": "免費專線：","link": 'tel:'+c["service_phone"], "data": service_phone},
                #    # {"title": "諮詢專線：", "link": c["phone"], "data": c["phone"]},
                #     {"title": "諮詢專線：", "link": c["phone"], "data": c["phone"]},
                #     {"title": "公司傳真：", "link": c["fax"], "data": c["fax"]},
                #     {"title": "公司地址：", "link": c["address"], "data": c["address"]},
                #     {"title": "電子信箱：", "link": c["mail"], "data": c["mail"]},
                #     {"title": "公司網址：", "link": c["website"], "data": c["website"]}
                # ]
                x["FreeCall"] = c["service_phone"]
                x["ConsoleCall_1"] = c["phone"]
                x["ConsoleCall_2"] = c["phone"]
                x["Fax"] = c["fax"]
                x["Address"] = c["address"]
                x["Email"] = c["mail"]
                x["Web"] = c["website"]
                
                x["Branches"] = []
                branches = db.query("""SELECT * FROM designer_branch br 
    WHERE br.designer_id = '""" + id + """' """)
                
                for branch in branches:
                    tmpobj = {
                        "title": branch["title"],
                        "address": branch["address"],
                        "address_link" : "https://www.google.com/search?q="+branch["address"],
                        "tel": branch["tel"],
                        "tel_link": "tel:"+branch["tel"],
                        "fax": branch["fax"],
                        "fax_link": "fax:"+branch["fax"]
                    }
                    x["Branches"].append(tmpobj)
                
                x["Budget"] = c["budget"]
                x["Square"] = c["area"]
                x["SpecialCase"] = c["special"]
                x["Charge"] = c["charge"]
                x["Pay"] = c["payment"]
                x["WorkLoc"] = c["region"]
                x["WorkType"] = c["type"]
                x["WorkStyle"] = c["style"]
                x["WorkBudget"] = c["budget"]
                x["Terms"] = []
                if c["budget"]!='':
                   x["Terms"].append({"title": "接案預算：", "data": c["budget"]}) 
                if c["area"]!='':
                   x["Terms"].append({"title": "接案坪數：", "data": c["area"]})  
                if c["special"]!='':
                   x["Terms"].append({"title": "特殊接案：", "data": c["special"]})  
                if c["charge"]!='':
                    x["Terms"].append({"title": "收費方式：", "data": c["charge"]})
                if c["payment"]!='':    
                    x["Terms"].append({"title": "付費方式：", "data": c["payment"]})
                if c["region"]!='':    
                    x["Terms"].append({"title": "接案區域：", "data": c["region"]})
                if c["type"]!='':    
                    x["Terms"].append({"title": "接案類型：", "data": c["type"]})
                if c["style"]!='':    
                    x["Terms"].append({"title": "接案風格：", "data": c["style"]})
                

                x["scMedia"] = [
                    {"name": "Facebook", "img": "https://hhh.com.tw/assets/images/rv_web/fb.svg","premium_img":"https://hhh.com.tw/assets/images/rv_web/fb-premium.png",
                        "link": c["fbpageurl"]},
                    {"name": "Line", "img": "https://hhh.com.tw/assets/images/rv_web/line.svg","premium_img":"https://hhh.com.tw/assets/images/rv_web/line-premium.png",
                        "link": c["line_link"]},
                    {"name": "Wechat", "img": "https://hhh.com.tw/assets/images/rv_web/wechat.svg","premium_img":"https://hhh.com.tw/assets/images/rv_web/wechat-premium.png",
                        "link": c["fbpageurl"]},
                    {"name": "email", "img": "https://hhh.com.tw/assets/images/rv_web/share.svg","premium_img":"https://hhh.com.tw/assets/images/rv_web/share-premium.png",
                        "link": c["mail"]},
                    {"name": "Like", "img": "https://hhh.com.tw/assets/images/rv_web/like-o.svg","premium_img":"https://hhh.com.tw/assets/images/rv_web/like-o-premium.png","img_d":"https://hhh.com.tw/assets/images/rv_web/like.svg","premium_img_d":"https://hhh.com.tw/assets/images/rv_web/like-premium.png" ,"link": ""}
                ]
                x_info = []
                if len(c["idea"])!=0:
                    x_info.append({"title": "設計理念", "data": c["idea"]})
                if len(c["taxid"])!=0:
                    x_info.append( {"title": "公司統編", "data": c["taxid"]})
                if len(c["career"])!=0:
                    x_info.append({"title": "相關經歷", "data": c["career"]})
                if len(c["license"])!=0:
                    x_info.append( {"title": "專業證照", "data": c["license"].replace('\r\n','<br />')})
                if len(c["awards"])!=0:
                    x_info.append( {"title": "獲獎紀錄", "data": c["awards"].replace('\r\n','<br />')})           
                          
                           

                x["Content"] = [
                    {
                        "Title": "設計師作品",
                        "mb_title": "作品",
                        "Tabtag": "intro",
                        "Display_mb": "true",
                        "isActive": "true",
                        "Carddata": [],
                        "total_all":0
                    },
                    {
                        "Title": "設計師影音",
                        "mb_title": "影音",
                        "Tabtag": "video",
                        "Display_mb": "true",
                        "isActive": "true",
                        "Carddata": [],
                        "total_all":0

                    },
                    {
                        "Title": "設計師專欄",
                        "mb_title": "專欄",
                        "Tabtag": "columns",
                        "Display_mb": "true",
                        "isActive": "true",
                        "Carddata": [],
                        "total_all":0

                    },
                    {
                        "Title": "VR360",
                        "mb_title": "VR",
                        "Tabtag": "vr360",
                        "Display_mb": "false",
                        "isActive": "true",
                        "Carddata": [],
                        "total_all":0

                    },
                    {
                        "Title": "設計師公司簡介",
                        "mb_title": "公司簡介",
                        "Tabtag": "company",
                        "Display_mb": "true",
                        "isActive": "true",
                        "Carddata": [],
                        "info":x_info
                        # "info": [
                        #     {"title": "設計理念", "data": c["idea"]},
                        #     {"title": "公司統編", "data": c["taxid"]},
                        #     {"title": "相關經歷", "data": c["career"]},
                        #     {"title": "專業證照", "data": c["license"]},
                        #     {"title": "獲獎紀錄", "data": c["awards"]}
                        # ]
                    }
                ]
            records = db.query("""SELECT count(1) FROM _hcase c WHERE c.hdesigner_id = '""" + id + """' AND c.onoff='1' and c.sdate < now(); """ )
            for c in records:
                x["Content"][0]["total_all"] = str(c["count(1)"])
            records = db.query("""SELECT count(1) FROM _hvideo v WHERE v.hdesigner_id = '""" + id + """' AND display_datetime < NOW() and iframe<>'' ;""")
            for c in records:
                x["Content"][1]["total_all"] = str(c["count(1)"])
            records = db.query("""SELECT count(1) FROM _hcolumn c WHERE onoff=1 AND (c.hdesigner_ids LIKE '""" + id + """,%' OR c.hdesigner_ids LIKE '%,""" + id + """,%' OR c.hdesigner_ids LIKE '%,""" + id + """' OR c.hdesigner_ids = '""" + id + """') ;""")
            for c in records:
                x["Content"][2]["total_all"] = str(c["count(1)"])
            records = db.query("""SELECT count(1) FROM _hcase c WHERE c.hdesigner_id = '""" + id + """' and istaging != '' AND c.onoff='1' and c.sdate < now();""")
            for c in records:
                x["Content"][3]["total_all"] = str(c["count(1)"])
            # 設計師作品
            x["Content"][0]["Carddata"] = []
            cases = db.query("""SELECT tag,cover,caption,hcase_id,viewed,sdate,style,style2,type,`condition` FROM _hcase c
    WHERE c.hdesigner_id = '""" + id + """' AND c.onoff='1' and c.sdate < now()
    ORDER BY """ + ("sdate" if sort == 'new' else 'corder ASC,viewed') + """ DESC
    LIMIT """ + str((int(page) - 1)*12) + """,12
    """)

            for case in cases:
                tag_use = ''
                if case['style']!='':
                    tag_use += case['style'].strip()
                    tag_use += ','
                if case['style2']!='':
                    tag_use += case['style2'].strip()
                    tag_use += ','
                if case['type']!='':
                    tag_use += case['type'].strip()
                    tag_use += ','
                if case['condition']!='':
                    tag_use += case['condition'].strip()
                tmpobj = {
                    "url": "https://hhh.com.tw/cases/detail/d/"+str(case["hcase_id"]),
                    "imgURL": case["cover"].replace('/_hcase/', '/_hcase_orig/').replace('/_hcase_img/', '/_hcase_img_orig/'),
                    "title": case["caption"],
                    "tag": [{"name": tag, "link": "https://hhh.com.tw/search/lists/case/{}-keyword/".format(tag)} for tag in tag_use.split(',')],
                    # "tag": [{"name": tag, "link": "https://hhh.com.tw/search/lists/case/{}-keyword/".format(tag)} for tag in case["tag"].split(',')],
                    "views": case["viewed"],
                    "dateSort": str(case["sdate"])
                }
                x["Content"][0]["Carddata"].append(tmpobj)

            # 設計師影音
            # https://i.ytimg.com/vi/y6VmaLC7O9Y/hqdefault.jpg
            x["Content"][1]["Carddata"] = []
    #         videos = db.query("""SELECT tag_vpattern,iframe,title,hvideo_id,viewed,display_datetime FROM _hvideo v
    # WHERE v.hdesigner_id = '""" + id + """' AND display_datetime < NOW() and iframe<>'' 
    # ORDER BY """ + ("hvideo_id" if sort == 'new' else 'viewed') + """ DESC  
    # LIMIT """ + str((int(page) - 1)*12) + """,12
    # """)
            videos = db.query("""SELECT tag_vpattern,iframe,title,hvideo_id,viewed,display_datetime FROM _hvideo v
    WHERE v.hdesigner_id = '""" + id + """' AND display_datetime < NOW() and iframe<>'' 
    ORDER BY """+ "hvideo_id" + """ DESC  
    LIMIT """ + str((int(page) - 1)*12) + """,12
    """)

            for video in videos:
                print(str(video['iframe']))
                tmpobj = {
                    "url": "https://hhh.com.tw/video-post.php?id="+str(video['hvideo_id']),
                    "imgURL": "https://i.ytimg.com/vi/" + extract.video_id(html.unescape(str(video['iframe'])))+"/hqdefault.jpg",
                    "title": video["title"],
                    "tag": [{"name": tag, "link": "https://hhh.com.tw/search/lists/video/{}-keyword/".format(tag)} for tag in video["tag_vpattern"].split(',')],
                    "views": video["viewed"],
                    "dateSort": str(video["display_datetime"])
                }

                x["Content"][1]["Carddata"].append(tmpobj)

            # 設計師專欄
    #          x["Content"][2]["Carddata"] = []
    #         columns = db.query("""SELECT Ctag,clogo,ctitle,hcolumn_id,viewed,sdate FROM _hcolumn c
    # WHERE onoff=1 AND (hdesigner_ids LIKE '""" + id + """,%' OR hdesigner_ids LIKE '%,""" + id + """,%' OR hdesigner_ids LIKE '%,""" + id + """' OR hdesigner_ids = '""" + id + """') 
    # ORDER BY """ + ("sdate" if sort == 'new' else 'viewed') + """ DESC
    # LIMIT """ + str((int(page) - 1)*12) + """,12
    # """)
            x["Content"][2]["Carddata"] = []
            columns = db.query("""SELECT Ctag,clogo,ctitle,hcolumn_id,viewed,sdate FROM _hcolumn c
    WHERE onoff=1 AND (hdesigner_ids LIKE '""" + id + """,%' OR hdesigner_ids LIKE '%,""" + id + """,%' OR hdesigner_ids LIKE '%,""" + id + """' OR hdesigner_ids = '""" + id + """') 
    ORDER BY """ + "sdate"  + """ DESC
    LIMIT """ + str((int(page) - 1)*12) + """,12
    """)

            for column in columns:
                tmpobj = {
                    "url": "https://hhh.com.tw/columns/detail/"+str(column['hcolumn_id']),
                    "imgURL": column['clogo'],
                    "title": column["ctitle"],
                    "tag": [] if not column["Ctag"] else [{"name": tag, "link": "https://hhh.com.tw/search/lists/column/{}-keyword/".format(tag)} for tag in column["Ctag"].split(',')],
                    "views": column["viewed"],
                    "dateSort": str(column["sdate"])
                }
                x["Content"][2]["Carddata"].append(tmpobj)

            # VR360
    #         x["Content"][3]["Carddata"] = []
    #         cases = db.query("""SELECT tag,cover,caption,hcase_id,viewed,sdate FROM _hcase c
    # WHERE c.hdesigner_id = '""" + id + """' and istaging != '' AND c.onoff='1' 
    # ORDER BY """ + ("sdate" if sort == 'new' else 'viewed') + """ DESC
    # LIMIT """ + str((int(page) - 1)*12) + """,12
    # """)
            x["Content"][3]["Carddata"] = []
            cases = db.query("""SELECT tag,cover,caption,hcase_id,viewed,sdate FROM _hcase c
    WHERE c.hdesigner_id = '""" + id + """' and istaging != '' AND c.onoff='1' and c.sdate < now()
    ORDER BY """ + "sdate" + """ DESC
    LIMIT """ + str((int(page) - 1)*12) + """,12
    """)

            for case in cases:
                tmpobj = {
                    "url": "https://hhh.com.tw/cases/detail/d/"+str(case["hcase_id"]),
                    "imgURL": case["cover"],
                    "title": case["caption"],
                    "tag": [{"name": tag, "link": "https://hhh.com.tw/search/lists/column/{}-keyword/".format(tag)} for tag in case["tag"].split(',')],
                    "views": case["viewed"],
                    "dateSort": str(case["sdate"])
                }
                x["Content"][3]["Carddata"].append(tmpobj)

            # 設計公司簡介
            #x["Content"][4]["Carddata"] = []
            #cases = db.query("")

            """ for case in cases:
                tmpobj = {
                    "imgURL":case["cover"],
                    "title":case["caption"],
                    "tag":[{"name": tag, "link": ""} for tag in case["tag"].split(',')],
                    "views":case["viewed"],
                    "dateSort":str(case["sdate"])
                    }
                x["Content"][4]["Carddata"].append(tmpobj) """

        # print(x)

        # print(jData)
        """ if not os.path.exists(hhhMBPath):
            os.mkdir(hhhMBPath)
        with open(hhhMBPath+'/json/designers-' + id + '.json', 'w', encoding='utf-8') as f:
            json.dump(jData, f, ensure_ascii=False, indent=4) """

        return jData

@app.get("/gencolumn")
def gencolumn(id: str = "6392", sort: str = "new", page: str="1"):

    with dataset.connect(connstr) as db:
        jData = json.load(open(hhhMBPath+'/json/Columns.json', encoding='utf8'))
    #     records = db.query("""SELECT * FROM _hcolumn c
    # WHERE c.hcolumn_id = '""" + id + """'  AND c.onoff='1' """)
        records = db.query("""SELECT * FROM _hcolumn c
    WHERE c.hcolumn_id = '""" + id + """' AND c.onoff='1'   """)
        
        #print(id)
        base_c_url="https://hhh.com.tw/columns/lists/"
        ctype_sub_dist={
                'hometrends': '居家趨勢',
                'hotrank'        : '人氣排行',
                'archdesign'     : '建築設計',
                'styleselection' : '風格選店',
                'charitable'     : '公益活動',
                'exhibition'     : '展演資訊',
           
                'smallhouse' : '小宅規劃',
                'oldhouse'   : '老屋翻新',
                'stylebuild' : '風格營造',
                'color'      : '配色佈置',
                'goodhouse'  : '好宅特輯',
                'proposal'   : '設計提案',
           
                'news'       : '新聞最前線',
                'allocation' : '預算分配',
                'process'    : '施工流程',
                'knowledge'  : '建材知識',
                'decorating' : '裝潢撇步',
          
                'fengshui'      : '居家風水',
                'housekeeping'  : '家事清潔',
                'storageskills' : '收納技巧',
                'renovation'    : '改造修繕',
                'retire'        : '退休好幸福',
           
                'furnishings' : '家具家飾',
                'appliances'  : '美型家電',
                'recommend'   : '推薦廚衛',
                'homegoods'   : '居家好物',
                'quality'     : '優質建材',
                'brandnews'   : '品牌新訊',
           
                'housingnews' : '房市新聞',
                'specsearch'  : '建案特搜',
                'buyandsell'  : '買屋賣屋',
                'mortgage'    : '房貸稅務',
                'renting'     : '租房須知',

        }
        ctype_sub_dist_converse = {v: k for k, v in ctype_sub_dist.items()}
        for x in jData:
            icount = 0
            c = None
            for c in records:
                icount += 1
                if c != None:
                    x["Columnsid"] = str(c["hcolumn_id"])
                    x["Columnstitle"] = c["ctitle"]
                    x["ColumnsCoverImg"] = c["clogo"]
                    x["ctype"] = c["ctype"]
                    x["ctype_sub"] = c["ctype_sub"]
                    if c["ctype"] == "不限":
                        x["ctype_link"] = base_c_url+"all-columntypea"
                        x["ctype_sub_link"] = ""
                        x["ctype_sub"] = ""
                    else:
                        try:
                            x["ctype_sub_link"] = ctype_sub_dist_converse[c["ctype_sub"]]+"-columntypeb"
                        except:
                            x["ctype_sub_link"] = ""
                        if c["ctype"] == "編輯精選":
                            x["ctype_link"] = base_c_url+"editorchoose-columntypea" 
                        elif c["ctype"] == "居家設計":
                            x["ctype_link"] = base_c_url+"housedesign-columntypea"
                        elif c["ctype"] == "裝修前線":
                            x["ctype_link"] = base_c_url+"decoration-columntypea"
                        elif c["ctype"] == "生活PLUS":
                            x["ctype_link"] = base_c_url+"plus-columntypea"
                        elif c["ctype"] == "品牌好物":
                            x["ctype_link"] = base_c_url+"gooditem-columntypea"
                        elif c["ctype"] == "房市焦點":
                            x["ctype_link"] = base_c_url+"focus-columntypea"
            
                    
                    x["ColumnsDate"] = str(c["sdate"])
                    x["ColumnsViews"] = str(c["viewed"])
                    if page == "1":
                        x["ColumnsContent"] = html.unescape(c["page_content"]).replace('../hhh/',"//www.hhh.com.tw/modules/gs/hhh/").replace('<img',"<img alt='幸福空間專欄' ").replace('../../../uploads/',"//www.hhh.com.tw/uploads/")
                    tmpTags = []
                    c["ctag"] = c["ctag"].replace(' ',',')
                    for tag in c["ctag"].split(','):
                        tmpTags.append({"Tag": tag})
                    x["ColumnsTag"] = tmpTags
                    x["author_inf"] = c["extend_str"]
                    
                    #相同類別的最新12筆

                    sql = """SELECT * FROM _hcolumn c
            WHERE (c.ctype like '%""" + str(c["ctype"]) + """%' and c.ctype_sub like '%""" + str(c["ctype_sub"]) + """%') and hcolumn_id <> '""" + str(c["hcolumn_id"]) + """' and sdate < now()  AND c.onoff='1'
            ORDER BY """ + ("sdate" if sort == 'new' else 'viewed') + """ DESC
            LIMIT """ + str((int(page) - 1)*12) + """,12
            """
                    if c["ctype"]=='不限':
                        sql = """SELECT * FROM _hcolumn c
            where hcolumn_id <> '""" + str(c["hcolumn_id"]) + """' and sdate < now()  AND c.onoff='1'
            ORDER BY """ + ("sdate" if sort == 'new' else 'viewed') + """ DESC
            LIMIT """ + str((int(page) - 1)*12) + """,12"""

            
                    ctypes = db.query(sql)
                    tmpOtherCols = []
                    for other in ctypes:
                        tmpOtherCol = {}
                        tmpOtherCol["Columnsid"] = str(other["hcolumn_id"])
                        tmpOtherCol["ColumnsCoverImg"] = other["clogo"]
                        tmpOtherCol["Views"] = other["viewed"]
                        tmpOtherCol["Columnstitle"] = other["ctitle"]
                        tmpTags = []
                        other["ctag"] = other["ctag"].replace(' ',',')
                        for tag in other["ctag"].split(','):
                            tmpTags.append({"Tag": tag})
                        tmpOtherCol["ColumnsTag"] = tmpTags
                        tmpOtherCols.append(tmpOtherCol)
                    x["OtherColumns"] = tmpOtherCols
            if icount == 0:
                return []
        #print(x)

        # print(jData)
        """ if not os.path.exists(hhhMBPath):
            os.mkdir(hhhMBPath)
        with open(hhhMBPath+'/json/Columns-' + id + '.json', 'w', encoding='utf-8') as f:
            json.dump(jData, f, ensure_ascii=False, indent=4) """

        return jData
@app.get("/gencolumn_use")
def gencolumn_use(id: str = "6392", sort: str = "new", page: str="1"):

    with dataset.connect(connstr) as db:
        jData = json.load(open(hhhMBPath+'/json/Columns.json', encoding='utf8'))
    #     records = db.query("""SELECT * FROM _hcolumn c
    # WHERE c.hcolumn_id = '""" + id + """'  AND c.onoff='1' """)
        records = db.query("""SELECT * FROM _hcolumn c
    WHERE c.hcolumn_id = '""" + id + """'  """)
        
        #print(id)
        base_c_url="https://hhh.com.tw/columns/lists/"
        ctype_sub_dist={
                'hometrends': '居家趨勢',
                'hotrank'        : '人氣排行',
                'archdesign'     : '建築設計',
                'styleselection' : '風格選店',
                'charitable'     : '公益活動',
                'exhibition'     : '展演資訊',
           
                'smallhouse' : '小宅規劃',
                'oldhouse'   : '老屋翻新',
                'stylebuild' : '風格營造',
                'color'      : '配色佈置',
                'goodhouse'  : '好宅特輯',
                'proposal'   : '設計提案',
           
                'news'       : '新聞最前線',
                'allocation' : '預算分配',
                'process'    : '施工流程',
                'knowledge'  : '建材知識',
                'decorating' : '裝潢撇步',
          
                'fengshui'      : '居家風水',
                'housekeeping'  : '家事清潔',
                'storageskills' : '收納技巧',
                'renovation'    : '改造修繕',
                'retire'        : '退休好幸福',
           
                'furnishings' : '家具家飾',
                'appliances'  : '美型家電',
                'recommend'   : '推薦廚衛',
                'homegoods'   : '居家好物',
                'quality'     : '優質建材',
                'brandnews'   : '品牌新訊',
           
                'housingnews' : '房市新聞',
                'specsearch'  : '建案特搜',
                'buyandsell'  : '買屋賣屋',
                'mortgage'    : '房貸稅務',
                'renting'     : '租房須知',

        }
        ctype_sub_dist_converse = {v: k for k, v in ctype_sub_dist.items()}
        for x in jData:
            icount = 0
            c = None
            for c in records:
                icount += 1
                if c != None:
                    x["Columnsid"] = str(c["hcolumn_id"])
                    x["Columnstitle"] = c["ctitle"]
                    x["ColumnsCoverImg"] = c["clogo"]
                    x["ctype"] = c["ctype"]
                    x["ctype_sub"] = c["ctype_sub"]
                    if c["ctype"] == "不限":
                        x["ctype_link"] = base_c_url+"all-columntypea"
                        x["ctype_sub_link"] = ""
                        x["ctype_sub"] = ""
                    else:
                        try:
                            x["ctype_sub_link"] = ctype_sub_dist_converse[c["ctype_sub"]]+"-columntypeb"
                        except:
                            x["ctype_sub_link"] = ""
                        if c["ctype"] == "編輯精選":
                            x["ctype_link"] = base_c_url+"editorchoose-columntypea" 
                        elif c["ctype"] == "居家設計":
                            x["ctype_link"] = base_c_url+"housedesign-columntypea"
                        elif c["ctype"] == "裝修前線":
                            x["ctype_link"] = base_c_url+"decoration-columntypea"
                        elif c["ctype"] == "生活PLUS":
                            x["ctype_link"] = base_c_url+"plus-columntypea"
                        elif c["ctype"] == "品牌好物":
                            x["ctype_link"] = base_c_url+"gooditem-columntypea"
                        elif c["ctype"] == "房市焦點":
                            x["ctype_link"] = base_c_url+"focus-columntypea"
            
                    
                    x["ColumnsDate"] = str(c["sdate"])
                    x["ColumnsViews"] = str(c["viewed"])
                    if page == "1":
                        x["ColumnsContent"] = html.unescape(c["page_content"]).replace('../hhh/',"//www.hhh.com.tw/modules/gs/hhh/").replace('<img',"<img alt='幸福空間專欄' ").replace('../../../uploads/',"//www.hhh.com.tw/uploads/")
                    tmpTags = []
                    c["ctag"] = c["ctag"].replace(' ',',')
                    for tag in c["ctag"].split(','):
                        tmpTags.append({"Tag": tag})
                    x["ColumnsTag"] = tmpTags
                    x["author_inf"] = c["extend_str"]
                    
                    #相同類別的最新12筆

                    sql = """SELECT * FROM _hcolumn c
            WHERE (c.ctype like '%""" + str(c["ctype"]) + """%' and c.ctype_sub like '%""" + str(c["ctype_sub"]) + """%') and hcolumn_id <> '""" + str(c["hcolumn_id"]) + """' and sdate < now()  AND c.onoff='1'
            ORDER BY """ + ("sdate" if sort == 'new' else 'viewed') + """ DESC
            LIMIT """ + str((int(page) - 1)*12) + """,12
            """
                    if c["ctype"]=='不限':
                        sql = """SELECT * FROM _hcolumn c
            where hcolumn_id <> '""" + str(c["hcolumn_id"]) + """' and sdate < now()  AND c.onoff='1'
            ORDER BY """ + ("sdate" if sort == 'new' else 'viewed') + """ DESC
            LIMIT """ + str((int(page) - 1)*12) + """,12"""

            
                    ctypes = db.query(sql)
                    tmpOtherCols = []
                    for other in ctypes:
                        tmpOtherCol = {}
                        tmpOtherCol["Columnsid"] = str(other["hcolumn_id"])
                        tmpOtherCol["ColumnsCoverImg"] = other["clogo"]
                        tmpOtherCol["Views"] = other["viewed"]
                        tmpOtherCol["Columnstitle"] = other["ctitle"]
                        tmpTags = []
                        other["ctag"] = other["ctag"].replace(' ',',')
                        for tag in other["ctag"].split(','):
                            tmpTags.append({"Tag": tag})
                        tmpOtherCol["ColumnsTag"] = tmpTags
                        tmpOtherCols.append(tmpOtherCol)
                    x["OtherColumns"] = tmpOtherCols
            if icount == 0:
                return []
        #print(x)

        # print(jData)
        """ if not os.path.exists(hhhMBPath):
            os.mkdir(hhhMBPath)
        with open(hhhMBPath+'/json/Columns-' + id + '.json', 'w', encoding='utf-8') as f:
            json.dump(jData, f, ensure_ascii=False, indent=4) """

        return jData

@app.get("/getColumnAds")
def getColumnAds():
    with dataset.connect(connstr) as db:
        ads = []
        
        records = db.query("""SELECT adlogo_mobile imgUrl, adlogo DimgUrl, adhref link,alt_use alt,adid
    FROM _had hh
    WHERE adtype LIKE '專欄首大%'
    AND hh.onoff='1' 
    and NOW() BETWEEN start_time AND end_time
    ORDER BY cast(SUBSTR(adtype, 4) AS DECIMAL) """)
        
        """ for x in records:
            ads.append(x) """

        ads.extend(records)
        rData = json.loads(json.dumps(ads))      
        #print(json.dumps(ads))
        return rData
from threading import Lock
import threading
mutex = Lock()

@app.post("/add_designer_clicks")
def add_designer_clicks(cid:str):
    with mutex:
        with dataset.connect(connstr) as db:
            records = db.query("""UPDATE _hdesigner SET clicks=clicks+1 WHERE hdesigner_id="""+cid)

            db.commit()

        return True
cid_locks = {}

@app.post("/add_columns_viewed")
def add_columns_clicks(cid: str):
    
    if cid not in cid_locks:
        cid_locks[cid] = threading.Lock()

    with cid_locks[cid]:
        with dataset.connect(connstr) as db:
            records = db.query("UPDATE _hcolumn SET viewed = viewed + 1 WHERE hcolumn_id = :cid", cid=cid)
            db.commit()

    return True

'''
@app.post("/add_columns_viewed")
def add_columns_clicks(cid:str):
    with mutex:
        with dataset.connect(connstr) as db:
            records = db.query("""UPDATE _hcolumn SET viewed=viewed+1 WHERE hcolumn_id="""+cid)

            db.commit()

        return True    
'''

#print(getColumnAds())

""" if __name__ == "__main__":
    uvicorn.run(app, host="0.0.0.0", port=8000) """