hhh_new/hhh_system_for_page/genjson_request.py

import os
from typing import Optional
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
import mysql.connector
from mysql.connector import Error
from io import BytesIO
from fastapi.responses import StreamingResponse
import xlsxwriter
import pandas as pd
import dataset

import json
from pytube import extract
import random

import boto3
from botocore.exceptions import ClientError
import base64

from pydantic import BaseModel
from datetime import datetime
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.image import MIMEImage

import jinja2


app = FastAPI()

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

#https://m3.hhh.com.tw:18687/docs

hhhMBPath = '../hhh-home-mb'
hhhPCPath = '../hhh-home-pc'
# db = dataset.connect(
#     'mysql://hhh7796hhh:lYmWsu^ujcA1@hhh-v57.cmab1ctkglka.ap-northeast-2.rds.amazonaws.com:3306/xoops?charset=utf8mb4')
#2022/12/13 change
db = dataset.connect( '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',
    connection = 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'
    )
    # connection.set_charset_collation('utf8','utf8_general_ci')
    cursor = connection.cursor(dictionary=True)
    cursor.execute(isql)
    if cursor.rowcount == -1:
        return cursor.fetchall()
    else:
        connection.commit()
        return cursor.rowcount


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

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

    output = BytesIO()
    records = ExecuteQuery(""" 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"):

    ExecuteQuery(
        "replace INTO stage._hdesigner SELECT * FROM xoops._hdesigner WHERE hdesigner_id IN ('" + designerid.replace(',', "','")+"');")
    ExecuteQuery(
        "replace INTO stage._hcase SELECT * FROM xoops._hcase WHERE hcase_id IN ('"+caseid.replace(',', "','")+"');")
    ExecuteQuery(
        "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"):

    ExecuteQuery(
        "replace INTO stage._hbrand SELECT * FROM xoops._hbrand WHERE hbrand_id IN ('" + brandid.replace(',', "','")+"');")
    ExecuteQuery(
        "replace INTO stage._hbrand_page SELECT * FROM xoops._hbrand_page WHERE hbrand_id IN ('" + brandid.replace(',', "','")+"');")
    ExecuteQuery(
        "replace INTO stage._hproduct SELECT * FROM xoops._hproduct WHERE id IN ('"+productid.replace(',', "','")+"');")
    ExecuteQuery(
        "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"):

    ExecuteQuery(
        "replace INTO stage._hcolumn SELECT * FROM xoops._hcolumn WHERE hcolumn_id IN ('" + columnid.replace(',', "','")+"');")
    ExecuteQuery(
        "replace INTO stage._hcolumn_img SELECT * FROM xoops._hcolumn_img WHERE hcolumn_id IN ('" + columnid.replace(',', "','")+"');")
    ExecuteQuery(
        "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
                            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(_hcase.caption,_hcolumn.ctitle),_hproduct.name),_hvideo.title) caption , IFNULL(ifnull(_hcase.cover,_hcolumn.clogo),_hproduct.cover) J, iframe , IFNULL(ifnull(ifnull(_hcase.short_desc,_hcolumn.cdesc),_hproduct.descr),_hvideo.`desc`) 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
                            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(_hcase.caption,_hcolumn.ctitle),_hproduct.name),_hvideo.title) caption , IFNULL(ifnull(_hcase.cover,_hcolumn.clogo),_hproduct.cover) J, iframe , IFNULL(ifnull(ifnull(_hcase.short_desc,_hcolumn.cdesc),_hproduct.descr),_hvideo.`desc`) 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
                            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 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 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("/gendesigner_list_six")
def gendesigner_list_six(sort: str = "new", page: str="1",city: str = "0", county: str="0",region:str="0",dtype:str="0",dstyle:str="0",budget:str="0",arch:str="0",award:str="0",agent:str="0"):
    
    #jData = json.load(open(hhhMBPath+'/json/designer_list_all.json', encoding='utf8'))
    condition = ""
    if city != "0":
        condition+=""" and (t2.address like '%"""+city+"""%' or t3.address like '%"""+city+"""%') """
    if county != "0":
        condition+=""" and (t2.address like '%"""+county+"""%' or t3.address like '%"""+county+"""%') """
    if region != "0":
        condition+=""" and (t2.region like '%"""+region+"""%' or t2.region like '%不限%') """
    if dtype != "0":
        condition+=""" and (t2.type like '%"""+dtype+"""%' or t2.type like '%不限%') """
    if dstyle != "0":
        condition+=""" and (t2.style like '%"""+dstyle+"""%' or t2.style like '%不限%') """
    if arch != "0":
        condition+=""" and license!='' """
    if award !="0":
        condition+=""" and (t2.awards like '%亞洲設計獎%') """
    if agent !="0":
        condition+=""" and (t2.guarantee > 0) """
    if budget!="0":
        if budget==1:
            condition+=""" and (t2.min_budget>=0 and t2.min_budget<=1000000) """
        elif budget==2:
            condition+=""" and (t2.min_budget>=1000001 and t2.min_budget<=2000000) """
        elif budget==3:
            condition+=""" and (t2.min_budget>=2000001 and t2.min_budget<=3000000) """
        elif budget==4:
            condition+=""" and (t2.min_budget>=3000001 and t2.min_budget<=4000000) """
        elif budget==5:
            condition+=""" and (t2.min_budget>=4000001 and t2.min_budget<=5000000) """
        elif budget==6:
            condition+=""" and (t2.min_budget>=5000001)"""
    #jData = json.load(open(hhhMBPath+'/json/designer_list_six.json', encoding='utf8'))
    if condition=="":
        if page=="1":
        # records = db.query("""SELECT  d.hdesigner_id,d.img_path,d.title,d.name,(select c.cover from _hcase c where c.hdesigner_id = d.hdesigner_id and c.onoff=1 ORDER BY c.corder ASC ,c.viewed DESC limit 1 ) as cover  FROM _hdesigner d left join   xoops.designer_branch b on b.designer_id = d.hdesigner_id left join  xoops._hawards ha on ha.hdesigner_id = d.hdesigner_id where ha.onoff=1 and d.top="O" GROUP BY d.hdesigner_id  Order By rand(); """)
            records = db.query("""SELECT  t2.hdesigner_id,t2.img_path,t2.name,t2.title,(SELECT t1.cover from _hcase t1 where t1.hdesigner_id = t2.hdesigner_id and t1.onoff=1 ORDER BY t1.corder ASC ,t1.viewed DESC limit 1 ) as cover  FROM _hdesigner t2 left JOIN _hcase t1 on t1.hdesigner_id=t2.hdesigner_id left JOIN designer_branch t3 on t1.hdesigner_id=t3.designer_id where t2.onoff=1 and t2.top_six="O" GROUP BY t2.hdesigner_id Order By rand(); """)
        else:
            records = db.query("""SELECT  t2.hdesigner_id,t2.img_path,t2.name,t2.title,(SELECT t1.cover from _hcase t1 where t1.hdesigner_id = t2.hdesigner_id and t1.onoff=1 ORDER BY t1.corder ASC ,t1.viewed DESC limit 1 ) as cover  FROM _hdesigner t2 left JOIN _hcase t1 on t1.hdesigner_id=t2.hdesigner_id left JOIN designer_branch t3 on t1.hdesigner_id=t3.designer_id where t2.onoff=1 and t2.top_six!="O" GROUP BY t2.hdesigner_id Order By"""+" "+("t1.sdate" if sort == 'new' else 't1.viewed')+""" DESC LIMIT"""+" "+str((int(page) - 2)*18+12)+""" ,6;""")
    else:
         records = db.query("""SELECT  t2.hdesigner_id,t2.img_path,t2.name,t2.title,(SELECT t1.cover from _hcase t1 where t1.hdesigner_id = t2.hdesigner_id and t1.onoff=1 ORDER BY t1.corder ASC ,t1.viewed DESC limit 1 ) as cover  FROM _hdesigner t2 left JOIN _hcase t1 on t1.hdesigner_id=t2.hdesigner_id left JOIN designer_branch t3 on t1.hdesigner_id=t3.designer_id where t2.onoff=1 """+condition+""" GROUP BY t2.hdesigner_id Order By """+" "+("t1.sdate" if sort == 'new' else 't1.viewed')+""" DESC LIMIT"""+" "+str((int(page) - 1)*18)+""" ,6;""")


    #print(records)
    #print(jData)
    jData = []   
    
    for c in records:
        if "_hcase_img" in c["cover"]:
            cover=c["cover"].replace("_hcase_img","_hcase_img_orig")
        else:
            cover=c["cover"].replace("_hcase","_hcase_orig")

        jData.append({"hdesigner_id":str(c["hdesigner_id"]),"img_path":str(c["img_path"]),"name":c["name"],"title":c["title"],"cover":cover} )    
    #for x,c in zip(jData,records):
    #    x["hdesigner_id"] = str(c["hdesigner_id"])
    #    x["img_path"] = str(c["img_path"])
    #    x["name"] = c["name"]
    #    x["title"] = c["title"]
    #    x["cover"] = c["cover"]
            
            
              
    return jData
@app.get("/gendesigner_list_twelve")
def gendesigner_list_twelve(sort: str = "new", page: str="1",city: str = "0", county: str="0",region:str="0",dtype:str="0",dstyle:str="0",budget:str="0",arch:str="0",award:str="0",agent:str="0"):

    #jData = json.load(open(hhhMBPath+'/json/designer_list_twelve.json', encoding='utf8'))
    condition = ""
    if city != "0":
        condition+=""" and (t2.address like '%"""+city+"""%' or t3.address like '%"""+city+"""%') """
    if county != "0":
        condition+=""" and (t2.address like '%"""+county+"""%' or t3.address like '%"""+county+"""%') """
    if region != "0":
        condition+=""" and (t2.region like '%"""+region+"""%' or t2.region like '%不限%') """
    if dtype != "0":
        condition+=""" and (t2.type like '%"""+dtype+"""%' or t2.type like '%不限%') """
    if dstyle != "0":
        condition+=""" and (t2.style like '%"""+dstyle+"""%' or t2.style like '%不限%') """
    if arch != "0":
        condition+=""" and license!='' """
    if award !="0":
        condition+=""" and (t2.awards like '%亞洲設計獎%') """
    if agent !="0":
        condition+=""" and (t2.guarantee > 0) """
    if budget!="0":
        if budget==1:
            condition+=""" and (t2.min_budget>=0 and t2.min_budget<=1000000) """
        elif budget==2:
            condition+=""" and (t2.min_budget>=1000001 and t2.min_budget<=2000000) """
        elif budget==3:
            condition+=""" and (t2.min_budget>=2000001 and t2.min_budget<=3000000) """
        elif budget==4:
            condition+=""" and (t2.min_budget>=3000001 and t2.min_budget<=4000000) """
        elif budget==5:
            condition+=""" and (t2.min_budget>=4000001 and t2.min_budget<=5000000) """
        elif budget==6:
            condition+=""" and (t2.min_budget>=5000001)"""
    if condition=="":
        records = db.query("""SELECT  t2.hdesigner_id,t2.img_path,t2.name,t2.title,(SELECT t1.cover from _hcase t1 where t1.hdesigner_id = t2.hdesigner_id and t1.onoff=1 ORDER BY t1.corder ASC ,t1.viewed DESC limit 1 ) as cover  FROM _hdesigner t2 left JOIN _hcase t1 on t1.hdesigner_id=t2.hdesigner_id left JOIN designer_branch t3 on t1.hdesigner_id=t3.designer_id where t2.onoff=1 and t2.top_six!="O" GROUP BY t2.hdesigner_id Order By"""+" "+("t1.sdate" if sort == 'new' else 't1.viewed')+""" DESC LIMIT"""+" "+str((int(page)-1)*18)+""" ,12;""")
    else:
        records = db.query("""SELECT  t2.hdesigner_id,t2.img_path,t2.name,t2.title,(SELECT t1.cover from _hcase t1 where t1.hdesigner_id = t2.hdesigner_id and t1.onoff=1 ORDER BY t1.corder ASC ,t1.viewed DESC limit 1 ) as cover  FROM _hdesigner t2 left JOIN _hcase t1 on t1.hdesigner_id=t2.hdesigner_id left JOIN designer_branch t3 on t1.hdesigner_id=t3.designer_id where t2.onoff=1 """+condition+""" GROUP BY t2.hdesigner_id Order By"""+" "+("t1.sdate" if sort == 'new' else 't1.viewed')+""" DESC LIMIT"""+" "+str((int(page)-1)*18+6)+""" ,12;""")
    #print(records)
    #print(jData)
        
    #for x,c in zip(jData,records):
    #    x["hdesigner_id"] = str(c["hdesigner_id"])
    #    x["img_path"] = str(c["img_path"])
    #    x["name"] = c["name"]
    #    x["title"] = c["title"]
    #    x["cover"] = c["cover"]
    jData = []   
    
    for c in records:
        if "_hcase_img" in c["cover"]:
            cover=c["cover"].replace("_hcase_img","_hcase_img_orig")
        else:
            cover=c["cover"].replace("_hcase","_hcase_orig")
        jData.append({"hdesigner_id":str(c["hdesigner_id"]),"img_path":str(c["img_path"]),"name":c["name"],"title":c["title"],"cover":cover} )
        
            
              
    return jData

@app.get("/gendesigner_list_all")
def gendesigner_list_all(city: str = "0", county: str="0",region:str="0",dtype:str="0",dstyle:str="0",budget:str="0",arch:str="0",award:str="0",agent:str="0"):

    #jData = json.load(open(hhhMBPath+'/json/designer_list_all.json', encoding='utf8'))
    condition = ""
    if city != "0":
        condition+=""" and (t2.address like '%"""+city+"""%' or t3.address like '%"""+city+"""%') """
    if county != "0":
        condition+=""" and (t2.address like '%"""+county+"""%' or t3.address like '%"""+county+"""%') """
    if region != "0":
        condition+=""" and (t2.region like '%"""+region+"""%' or t2.region like '%不限%') """
    if dtype != "0":
        condition+=""" and (t2.type like '%"""+dtype+"""%' or t2.type like '%不限%') """
    if dstyle != "0":
        condition+=""" and (t2.style like '%"""+dstyle+"""%' or t2.style like '%不限%') """
    if arch != "0":
        condition+=""" and license!='' """
    if award !="0":
        condition+=""" and (t2.awards like '%亞洲設計獎%') """
    if agent !="0":
        condition+=""" and (t2.guarantee > 0) """
    if budget!="0":
        if budget==1:
            condition+=""" and (t2.min_budget>=0 and t2.min_budget<=1000000) """
        elif budget==2:
            condition+=""" and (t2.min_budget>=1000001 and t2.min_budget<=2000000) """
        elif budget==3:
            condition+=""" and (t2.min_budget>=2000001 and t2.min_budget<=3000000) """
        elif budget==4:
            condition+=""" and (t2.min_budget>=3000001 and t2.min_budget<=4000000) """
        elif budget==5:
            condition+=""" and (t2.min_budget>=4000001 and t2.min_budget<=5000000) """
        elif budget==6:
            condition+=""" and (t2.min_budget>=5000001)"""

    records = db.query("""SELECT  t2.hdesigner_id,t2.img_path,t2.name,t2.title,(SELECT t1.cover from _hcase t1 where t1.hdesigner_id = t2.hdesigner_id and t1.onoff=1 ORDER BY t1.corder ASC ,t1.viewed DESC limit 1 ) as cover FROM _hdesigner t2 left JOIN _hcase t1 on t1.hdesigner_id=t2.hdesigner_id left JOIN designer_branch t3 on t1.hdesigner_id=t3.designer_id where t2.onoff=1 """+condition+""" GROUP BY t2.hdesigner_id;""")
    #print(records)
    #print(jData)
    jData = []   
    
    for c in records:
        if "_hcase_img" in c["cover"]:
            cover=c["cover"].replace("_hcase_img","_hcase_img_orig")
        else:
            cover=c["cover"].replace("_hcase","_hcase_orig")
        jData.append({"hdesigner_id":str(c["hdesigner_id"]),"img_path":str(c["img_path"]),"name":c["name"],"title":c["title"],"cover":c["cover"]} )
    json.dumps(jData)
    #print(jData)    
    #print(len(jData))           
            
              
    return jData





@app.get("/find_designer_show")
def find_designer_show(cid:str ,type_use: str="computer"):
    condition = ""
    #jData = json.load(open(hhhMBPath+'/json/designer_list_all.json', encoding='utf8'))
    

    if type_use == "computer":
        records = db.query("""SELECT  order_computer  FROM _hdesigner where hdesigner_id="""+cid+""";""")
        jData = []   
    
        for c in records:
            jData.append({"order_show_type":str(c["order_computer"])} )
        json.dumps(jData)
    else:
        records = db.query("""SELECT  order_mb  FROM _hdesigner where hdesigner_id="""+cid+""";""")
        jData = []   
    
        for c in records:
            jData.append({"order_show_type":str(c["order_mb"])} )
        json.dumps(jData)
    #print(records)
    #print(jData)
    
    #print(jData)    
    #print(len(jData))           
            
              
    return jData


@app.get("/find_total")
def find_total(cid:str ,type_use: str):
    condition = ""
    if type_use=='case':
    #jData = json.load(open(hhhMBPath+'/json/designer_list_all.json', encoding='utf8'))
        records = db.query("""SELECT count(*) FROM _hcase c
        WHERE c.hdesigner_id = '""" + cid + """' AND c.onoff='1'; """ )
    elif type_use=='video':
        records = db.query("""SELECT count(*) FROM _hvideo v
        WHERE v.hdesigner_id = '""" + cid + """' AND display_datetime < NOW() ;""")
    elif type_use == 'column':
        records = db.query("""SELECT count(*) FROM _hcolumn c
        WHERE onoff=1 AND (c.hdesigner_ids LIKE '""" + cid + """,%' OR c.hdesigner_ids LIKE '%,""" + cid + """,%' OR c.hdesigner_ids LIKE '%,""" + cid + """' OR c.hdesigner_ids = '""" + cid + """') ;""")
    elif type_use == 'vr360':
        records = db.query("""SELECT count(*) FROM _hcase c
        WHERE c.hdesigner_id = '""" + cid + """' and istaging != '' AND c.onoff='1' ;""")


    jData = []   
    
    for c in records:
        jData.append({"total":str(c["count(*)"])} )
    json.dumps(jData)
    #print(records)
    #print(jData)
    
    #print(jData)    
    #print(len(jData))           
            
              
    return jData



















@app.get("/find_user_favorite")
def find_user_favorite(user_id: str ,cid:str, type_use: str="designer"):
    condition = ""
    #jData = json.load(open(hhhMBPath+'/json/designer_list_all.json', encoding='utf8'))
    

    
    records = db.query("""SELECT  *  FROM user_favorite where status=0 and user_id="""+user_id+""" and table_id="""+cid+""" and type= '"""+type_use+"""' ;""")

    #print(records)
    #print(jData)
    jData = []   
    
    for c in records:
        jData.append({"id":str(c["id"]),"user_id":str(c["user_id"]),"type":c["type"],"cid":c["table_id"],"create_time":str(c["create_time"]),"status":str(c["status"])} )
    json.dumps(jData)
    #print(jData)    
    #print(len(jData))           
            
              
    return jData


# @app.post("/add_user_favorite")
# def add_user_favorite(user_id: str ,cid:str, type_use: str="designer"):

#     records = db.query("""SELECT  *  FROM user_favorite where status=1 and user_id="""+user_id+""" and table_id="""+cid+""" and type= '"""+type_use+"""' ;""")
#     jData = []   
    
#     for c in records:
#         jData.append({"id":str(c["id"]),"user_id":str(c["user_id"]),"type":c["type"],"cid":c["table_id"],"create_time":str(c["create_time"]),"status":str(c["status"])} )
#     json.dumps(jData)
#     if len(jData)>0:
#        db.query("""UPDATE user_favorite SET status=0 WHERE user_id="""+user_id+""" and table_id="""+cid+""" and type= '"""+type_use+"""';""") 
#     else:
#        records = db.query("""SELECT  *  FROM user_favorite where status=0 and user_id="""+user_id+""" and table_id="""+cid+""" and type= '"""+type_use+"""' ;""")
#        jData = [] 
#        json.dumps(jData)
#        if len(jData)==0:
#             db.query("""INSERT INTO user_favorite (user_id, type, table_id) values ( """+user_id+""",'"""+type_use+"""',"""+cid+""");""") 

#     db.commit()

#     return True


# @app.post("/delete_user_favorite")
# def delete_user_favorite(user_id: str ,cid:str, type_use: str="designer"):
#     records = db.query("""SELECT  *  FROM user_favorite where status=0 and user_id="""+user_id+""" and table_id="""+cid+""" and type= '"""+type_use+"""' ;""")
#     jData = []   
    
#     for c in records:
#         jData.append({"id":str(c["id"]),"user_id":str(c["user_id"]),"type":c["type"],"cid":c["table_id"],"create_time":str(c["create_time"]),"status":str(c["status"])} )
#     json.dumps(jData)
#     if len(jData)>0:
#         db.query("""UPDATE user_favorite SET status=1 WHERE user_id="""+user_id+""" and table_id="""+cid+""" and type= '"""+type_use+"""';""") 
#     return True


#===================================================================================================================================

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

    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")
def gendesigner(id: str = "14151"):

    jData = json.load(open(hhhMBPath+'/json/designers.json', encoding='utf8'))

    records = db.query("""SELECT *, ci.name cimg FROM _hcase c
LEFT JOIN _hcase_img ci ON c.hcase_id = ci.hcase_id
LEFT JOIN _hdesigner d ON c.hdesigner_id = d.hdesigner_id
WHERE d.hdesigner_id = '""" + id + """'  AND c.onoff='1' AND d.onoff='1' """)

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

            x["id"] = c["hdesigner_id"]
            x["BannerImg"] = c["background"]
            x["CompanyName"] = c["title"]
            x["DesignerName"] = c["name"]
            x["Designerimg"] = c["img_path"]
            x["Description"] = c["seo"]
            x["Approve"] = c["position"]
            x["Basics"] = [
            {"title": "免費專線：",
                "link": c["service_phone"], "data": c["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"] = [
            {"title": "分公司地址：", "link": "台北市中山區林森北路50號4樓之一",
                "data": "台北市中山區林森北路50號4樓之一"},
            {"title": "分公司電話：", "link": "tel:02-2562-7755", "data": "02-2562-7755"},
            {"title": "分公司傳真：", "link": "tel:02-2562-7002", "data": "02-2562-7002"},
            {"title": "分公司地址：", "link": "上海市闵行区东川路2688号", "data": "上海市闵行区东川路2688号"}
            ]
            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"] = [
            {"title": "接案預算：", "data": c["budget"]},
            {"title": "接案坪數：", "data": c["area"]},
            {"title": "特殊接案：", "data": c["special"]},
            {"title": "收費方式：", "data": c["charge"]},
            {"title": "付費方式：", "data": c["payment"]},
            {"title": "接案區域：", "data": c["region"]},
            {"title": "接案類型：", "data": c["type"]},
            {"title": "接案風格：", "data": c["style"]}
            ]
            x["scMedia"] = [
            {"name": "Facebook", "img": "https://hhh.com.tw/assets/images/rv_web/fb.svg",
                "link": c["fbpageurl"]},
            {"name": "Line", "img": "https://hhh.com.tw/assets/images/rv_web/line.svg",
                "link": c["line_link"]},
            {"name": "Wechat", "img": "https://hhh.com.tw/assets/images/rv_web/wechat.svg",
                "link": c["fbpageurl"]},
            {"name": "email", "img": "https://hhh.com.tw/assets/images/rv_web/share.svg",
                "link": c["mail"]},
            {"name": "Like", "img": "https://hhh.com.tw/assets/images/rv_web/like-o.svg", "link": ""}
            ]
            x["Content"] = [
            {
                "Title": "設計師作品",
                "mb_title": "作品",
                "Tabtag": "intro",
                "Display_mb": "true",
                "isActive": "true",
                "Carddata": [
                ]
            },
            {
                "Title": "設計師影音",
                "mb_title": "影音",
                "Tabtag": "video",
                "Display_mb": "true",
                "isActive": "true",
                "Carddata": [
                ]
            },
            {
                "Title": "設計師專欄",
                "mb_title": "專欄",
                "Tabtag": "columns",
                "Display_mb": "true",
                "isActive": "true",
                "Carddata": [
                ]
            },
            {
                "Title": "VR360",
                "mb_title": "",
                "Tabtag": "vr360",
                "Display_mb": "false",
                "isActive": "true",
                "Carddata": [
                ]
            },
            {
                "Title": "設計師公司簡介",
                "mb_title": "公司簡介",
                "Tabtag": "company",
                "Display_mb": "true",
                "isActive": "true",
                "Carddata": [
                ],
                "info": [
                    {"title": "設計理念", "data": "空間設計的美學藝術，蔡岳儒總監更能將日式精闢的手工藝術融入台灣的設計與工程，更將留日所學發揚光大明確的把結構、 動線、收納、光線與品味融入建築、景觀與室內三大空間之中，藉由點線面展現出空間的獨特性，並賦予空間延續的生命活力。"},
                    {"title": "公司統編", "data": "經濟部／財政部登記有案：54290717"},
                    {"title": "相關經歷", "data": "蔡岳儒<br>◆現任／恆岳空間設計 主持設計師<br>◆學歷／東京デザイナー学院卒業<br>◆經歷／十餘年設計、工程經歷。"},
                    {"title": "專業證照",
                     "data": "建築師證書 高恒悌 (101) 專高建字第000013號<br>建築物室內裝修專業技術人員登記證 高恒悌 第40EC116787號"},
                    {"title": "獲獎紀錄", "data": "2020年雪梨設計獎SYDNEY Design Awards GIOD-Quality Arena<br>2020年雪梨設計獎SYDNEY Design Awards SELECTION- Life Savoring"}
                ]
            }
            ]

    # 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"):

    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' """)

    #print(id)
    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["ColumnsDate"] = str(c["sdate"])
                x["ColumnsViews"] = str(c["viewed"])
                if page == "1":
                    x["ColumnsContent"] = c["page_content"]
                tmpTags = []
                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
        """
                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 = []
                    for tag in other["ctag"].split(','):
                        tmpTags.append({"Tag": tag})
                    tmpOtherCol["ColumnsTag"] = tmpTags
                    tmpOtherCols.append(tmpOtherCol)
                x["OtherColumns"] = tmpOtherCols
        
    #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():
    
    ads = []
    
    records = db.query("""SELECT adlogo_mobile imgUrl, adlogo DimgUrl, adhref link
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

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










@app.get("/genemering")
async def genemering():
    id_use = []
    ids = db.query("""SELECT hdesigner_id FROM _hdesigner d where d.emering=1 and d.onoff='1' order by rand();""")
    for c in ids:
        id_use.append(str(c["hdesigner_id"]))
    # with dataset.connect(connstr) as db:
    #     jData = json.load(open(hhhMBPath+'/json/designers.json', encoding='utf8'))
    jData_all = []
    for id in id_use:
        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'  ORDER BY sdate DESC LIMIT 6 """ )
        cases_list = []
        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"])
            }
            cases_list.append(tmpobj)
        
        records = db.query("""SELECT * FROM _hdesigner d WHERE d.hdesigner_id = '""" + id + """' AND d.onoff='1' """)
        for c in records:
            if c["service_phone"]!='':
                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=''
            jData={"hdesignerId":str(c["hdesigner_id"]),
                        "designerName":str(c["name"]),
                        "companyName": str(c["title"]),
                        'designerBigImg':"./img/"+id,
                        "designerIdea":str(c["idea"]),
                        "website":str(c["website"]),
                        "servicePhone":service_phone,
                        "fbUrl":str(c["fbpageurl"]),
                        "cardData": cases_list
                        }
        jData_all.append(jData)
    json.dumps(jData_all)
    return jData_all
from typing import List
@app.get("/genemering_case")
async def genemering_case():
    id_use = []
    ids = db.query("""SELECT hdesigner_id FROM _hdesigner d where d.emering=1 and d.onoff='1' order by rand();""")
    for c in ids:
        id_use.append(str(c["hdesigner_id"]))
    # with dataset.connect(connstr) as db:
    #     jData = json.load(open(hhhMBPath+'/json/designers.json', encoding='utf8'))
    jData_all = []
    for id in id_use:
        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'  ORDER BY sdate DESC LIMIT 6 """ )
        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"])
            }
            jData_all.append(tmpobj)

    random.shuffle(jData_all)
    json.dumps(jData_all)
    return jData_all[0:6]
@app.get("/genemering_calculator_count")
async def genemering_calculator_count():
    counts_use = []
    counts = db.query("""SELECT count(1) FROM calculator;""")
    for c in counts:
        counts_use.append(str(c["count(1)"]))
    return counts_use






class deco_request_detail(BaseModel):
    id: str
    name: str
    email: str
    phone: str
    #detail
    loc: str
    h_class: str
    # type: str
    # budget: str
    size: str
    # bed_num: int
    # liv_num: int
    # bath_num: int
    # style: str
    # prefer_date: str
    time_stamp: str
    # gender: str
    version: str
    utmSource:str
    utmMedium:str
    utmCampaign:str


@app.post("/insert_calculator_request_A")
async def insert_calculator_request_A(req:deco_request_detail):
    db_new = dataset.connect('mysql://hhh7796hhh:lYmWsu^ujcA1@ec2-3-35-26-49.ap-northeast-2.compute.amazonaws.com:3306/xoops?charset=utf8mb4')
    db_new.query("""INSERT INTO calculator_request (name, phone, email,city,area,ca_type,source_web,marketing_consent,h_class,utm_source,utm_medium,utm_campaign) VALUES ('""" + req.name + """','""" + req.phone + """','""" + req.email + """','""" + req.loc + """', '""" + req.size + """', '""" + '無' + """', '""" + '全室裝修A' + """', '""" + '2' + """', '""" + req.h_class + """', '""" +str(req.utmSource)+ """', '""" + str(req.utmMedium)+ """', '""" + str(req.utmCampaign) + """');""")
    db_new.commit()

######寄給業務通知信###########################

    # Replace sender@example.com with your "From" address.
    # This address must be verified with Amazon SES.
    iName = req.name
    iPhone = req.phone
    iEmail = req.email

    iCity = req.loc
    iArea = req.size
    iHouse= req.h_class

    iCtype = "無"
    iSource = "全室裝修A"

    #獲取當前時間
    now = datetime.now()
    #格式化時間
    iDate = now.strftime("%Y.%m.%d   %H:%M") 
    
    SENDER = "Gorgeous Space - Mailer <noreply@hhh.com.tw>"

    # Replace recipient@example.com with a "To" address. If your account 
    # is still in the sandbox, this address must be verified.
    #RECIPIENT = iRecipient

    # RECIPIENTS = iRecipient.split(',')
    # CCS = iSale_mail.split(',')

    # RECIPIENTS = iRecipient.split(',')

    #寄給業務
    RECIPIENTS = ["miko@hhh.com.tw","lily_huang@hhh.com.tw","dori@hhh.com.tw","wesson@hhh.com.tw"]
    #寄給開發
    CCS=['oak@choozmo.com']
         
    # CCS.append("hhh_edit@hhh.com.tw")
    print(RECIPIENTS)
    print(CCS)
    ###之後要改成上面設計師email
    # RECIPIENTS = ["may@hhh.com.tw","jennifer_kao@hhh.com.tw",]
    #RECIPIENTS = ["hhh_edit@hhh.com.tw"]
    #CCS = ["hhh_edit@hhh.com.tw"]
    BCCS = []

    # Specify a configuration set. If you do not want to use a configuration
    # set, comment the following variable, and the 
    # ConfigurationSetName=CONFIGURATION_SET argument below.
    #CONFIGURATION_SET = "ConfigSet"

    # If necessary, replace us-west-2 with the AWS Region you're using for Amazon SES.
    AWS_REGION = "us-east-1"

    # The subject line for the email.
    SUBJECT = "幸福空間 裝修需求預算通知 (來自"+iName+'-'+iPhone+")"

    # The email body for recipients with non-HTML email clients.
    # BODY_TEXT = ("\r\n" + iName + " 設計師 , 您好:\r\n我是幸福空間編輯 , 您的個案已經完成並上線！\r\n幸福空間已於"+ iSection +"將您的個案露出於幸福空間網站上"+"\r\n個案名稱 :"+ iCap+"\r\n\r\n"+ iImg+"\r\n\r\n網址: https://hhh.com.tw/cases/detail/d/"+iId+"/\r\n煩請您上網查收點閱，若有需要增刪修改之處，還請不吝提出指教，謝謝！\r\n------------------------------------------------------------\r\n 幸福空間股份有限公司\r\n E-mail : service@choozmo.com \r\n  幸福空間 : https://hhh.com.tw \r\n ")
    # BODY_TEXT = ("\r\n" + " 以下為裝修需求")
    BODY_TEXT = ""           
    BODY_HTML = """<html>
    <head></head>
    <body>
    <p>以下為裝修需求 <br />
    <p> 建立時間 : """ + iDate + "<br />姓名 : "+ iName +"<br />手機 : "+ iPhone+"<br />E-mail: "+ iEmail+"<br />縣市區域 : " + iCity +"<br />坪數 : "+ iArea+ "<br/>裝修類型:"+ iCtype+ "<br/>房屋類型:"+ iHouse+ "<br/>需求來源平台:"+ iSource +"<br/>"+"""
    ------------------------------------------------------------<br/> 
    幸福空間股份有限公司<br/> 
    E-mail : hhh_edit@hhh.com.tw <br/> 
    幸福空間 : https://hhh.com.tw <br/> 

    </p>
    </body>
    </html>"""  
    # The character encoding for the email.
    CHARSET = "UTF-8"

    # Create a new SES resource and specify a region.
    client = boto3.client('ses'
            ,aws_access_key_id='AKIAQG3PJZCHXPCSTAJG'
            ,aws_secret_access_key='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'
            ,region_name=AWS_REGION)

    # Try to send the email.
    try:
        #Provide the contents of the email.
        response = client.send_email(
            Destination={
                'ToAddresses': RECIPIENTS,
                'CcAddresses': CCS,
                'BccAddresses': BCCS
            },
            Message={
                'Body': {
                    'Html': {
                        'Charset': CHARSET,
                        'Data': BODY_HTML,
                    },
                    'Text': {
                        'Charset': CHARSET,
                        'Data': BODY_TEXT,
                    },
                },
                'Subject': {
                    'Charset': CHARSET,
                    'Data': SUBJECT,
                },
            },
            Source=SENDER,
            # If you are not using a configuration set, comment or delete the
            # following line
            #ConfigurationSetName=CONFIGURATION_SET,
        )
    # Display an error if something goes wrong. 
    except ClientError as e:
        print(e.response['Error']['Message'])
    else:
        print("Email sent! Message ID:"),
        print(response['MessageId']) 

    return True


@app.post("/insert_calculator_request_B")
async def insert_calculator_request_A(req:deco_request_detail):
    db_new = dataset.connect('mysql://hhh7796hhh:lYmWsu^ujcA1@ec2-3-35-26-49.ap-northeast-2.compute.amazonaws.com:3306/xoops?charset=utf8mb4')
    db_new.query("""INSERT INTO calculator_request (name, phone, email,city,area,ca_type,source_web,marketing_consent,h_class,utm_source,utm_medium,utm_campaign) VALUES ('""" + req.name + """','""" + req.phone + """','""" + req.email + """','""" + req.loc + """', '""" + req.size + """', '""" + '無' + """', '""" + '全室裝修B' + """', '""" + '2' + """', '""" + req.h_class+ """', '""" +str(req.utmSource)+ """', '""" + str(req.utmMedium)+ """', '""" + str(req.utmCampaign) + """');""")
    db_new.commit()

######寄給業務通知信###########################

    # Replace sender@example.com with your "From" address.
    # This address must be verified with Amazon SES.
    iName = req.name
    iPhone = req.phone
    iEmail = req.email

    iCity = req.loc
    iArea = req.size
    iHouse= req.h_class

    iCtype = "無"
    iSource = "全室裝修B"

    #獲取當前時間
    now = datetime.now()
    #格式化時間
    iDate = now.strftime("%Y.%m.%d   %H:%M") 
    
    SENDER = "Gorgeous Space - Mailer <noreply@hhh.com.tw>"

    # Replace recipient@example.com with a "To" address. If your account 
    # is still in the sandbox, this address must be verified.
    #RECIPIENT = iRecipient

    # RECIPIENTS = iRecipient.split(',')
    # CCS = iSale_mail.split(',')

    # RECIPIENTS = iRecipient.split(',')

    #寄給業務
    RECIPIENTS = ["miko@hhh.com.tw","lily_huang@hhh.com.tw","dori@hhh.com.tw","wesson@hhh.com.tw"]
    #寄給開發
    CCS=['oak@choozmo.com']
         
    # CCS.append("hhh_edit@hhh.com.tw")
    print(RECIPIENTS)
    print(CCS)
    ###之後要改成上面設計師email
    # RECIPIENTS = ["may@hhh.com.tw","jennifer_kao@hhh.com.tw",]
    #RECIPIENTS = ["hhh_edit@hhh.com.tw"]
    #CCS = ["hhh_edit@hhh.com.tw"]
    BCCS = []

    # Specify a configuration set. If you do not want to use a configuration
    # set, comment the following variable, and the 
    # ConfigurationSetName=CONFIGURATION_SET argument below.
    #CONFIGURATION_SET = "ConfigSet"

    # If necessary, replace us-west-2 with the AWS Region you're using for Amazon SES.
    AWS_REGION = "us-east-1"

    # The subject line for the email.
    SUBJECT = "幸福空間 裝修需求預算通知 (來自"+iName+'-'+iPhone+")"

    # The email body for recipients with non-HTML email clients.
    # BODY_TEXT = ("\r\n" + iName + " 設計師 , 您好:\r\n我是幸福空間編輯 , 您的個案已經完成並上線！\r\n幸福空間已於"+ iSection +"將您的個案露出於幸福空間網站上"+"\r\n個案名稱 :"+ iCap+"\r\n\r\n"+ iImg+"\r\n\r\n網址: https://hhh.com.tw/cases/detail/d/"+iId+"/\r\n煩請您上網查收點閱，若有需要增刪修改之處，還請不吝提出指教，謝謝！\r\n------------------------------------------------------------\r\n 幸福空間股份有限公司\r\n E-mail : service@choozmo.com \r\n  幸福空間 : https://hhh.com.tw \r\n ")
    # BODY_TEXT = ("\r\n" + " 以下為裝修需求")
    BODY_TEXT = ""           
    BODY_HTML = """<html>
    <head></head>
    <body>
    <p>以下為裝修需求 <br />
    <p> 建立時間 : """ + iDate + "<br />姓名 : "+ iName +"<br />手機 : "+ iPhone+"<br />E-mail: "+ iEmail+"<br />縣市區域 : " + iCity +"<br />坪數 : "+ iArea+ "<br/>裝修類型:"+ iCtype+ "<br/>房屋類型:"+ iHouse+ "<br/>需求來源平台:"+ iSource +"<br/>"+"""
    ------------------------------------------------------------<br/> 
    幸福空間股份有限公司<br/> 
    E-mail : hhh_edit@hhh.com.tw <br/> 
    幸福空間 : https://hhh.com.tw <br/> 

    </p>
    </body>
    </html>"""  
    # The character encoding for the email.
    CHARSET = "UTF-8"

    # Create a new SES resource and specify a region.
    client = boto3.client('ses'
            ,aws_access_key_id='AKIAQG3PJZCHXPCSTAJG'
            ,aws_secret_access_key='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'
            ,region_name=AWS_REGION)

    # Try to send the email.
    try:
        #Provide the contents of the email.
        response = client.send_email(
            Destination={
                'ToAddresses': RECIPIENTS,
                'CcAddresses': CCS,
                'BccAddresses': BCCS
            },
            Message={
                'Body': {
                    'Html': {
                        'Charset': CHARSET,
                        'Data': BODY_HTML,
                    },
                    'Text': {
                        'Charset': CHARSET,
                        'Data': BODY_TEXT,
                    },
                },
                'Subject': {
                    'Charset': CHARSET,
                    'Data': SUBJECT,
                },
            },
            Source=SENDER,
            # If you are not using a configuration set, comment or delete the
            # following line
            #ConfigurationSetName=CONFIGURATION_SET,
        )
    # Display an error if something goes wrong. 
    except ClientError as e:
        print(e.response['Error']['Message'])
    else:
        print("Email sent! Message ID:"),
        print(response['MessageId'])  

    return True





class CalculatorRequest(BaseModel):
    name: str
    phone: str
    email: str
    city: str
    area: str
    ca_type: str
    source: str
    marketing_consent: str
    h_class: str
    utmSource:str
    utmMedium:str
    utmCampaign:str



# @app.post("/insert_calculator_request")
# async def insert_calculator_request(name: str = "test", phone: str = "0987654321", email: str="oak@choozmo.com",city:str="台北",area:str="20",ca_type:str="輕裝修",source:str="官網",marketing_consent:str="1",h_class:str="無"):
@app.post("/insert_calculator_request")
async def insert_calculator_request(request: CalculatorRequest):
    name = request.name
    phone = request.phone
    email = request.email
    city = request.city
    area = request.area
    ca_type = request.ca_type
    source = request.source
    marketing_consent = request.marketing_consent
    h_class = request.h_class 

    utm_source = request.utmSource
   
    utm_medium = request.utmMedium

    utm_campaign= request.utmCampaign

    db.query("""INSERT INTO calculator_request (name, phone, email,city,area,ca_type,source_web,marketing_consent,h_class,utm_source,utm_medium,utm_campaign) VALUES ('""" + name + """','""" + phone + """','""" + email + """','""" + city + """', '""" + area + """', '""" + ca_type + """', '""" + source + """', '""" + marketing_consent + """', '""" + h_class+ """', '""" +str(utm_source)+ """', '""" + str(utm_medium)+ """', '""" + str(utm_campaign) + """');""")
  
    db.commit()
    if ca_type=="輕裝修":
        price = 43000*float(area)
        k1 = price*0.3
        k2 = price*0.36
        k3 = price*0.22
        k4 = price*0.12

        jData = {"city":str(city),
              "area":str(area),
              "ca_type": str(ca_type),
              "basic_engineering":k1,
              "hardware_engineering":k2,
              "decorated_home":k3,
              "home_appliances":k4
        }
        json.dumps(jData)
    elif ca_type=="全室裝修":
        price1 = 63000*float(area)
        k1 = price1*0.3
        k2 = price1*0.36
        k3 = price1*0.22
        k4 = price1*0.12

        price2 = 98000*float(area)
        k5 = price2*0.3
        k6 = price2*0.36
        k7 = price2*0.22
        k8 = price2*0.12

        price3 = 163000*float(area)
        k9 = price3*0.3
        k10 = price3*0.36
        k11 = price3*0.22
        k12 = price3*0.12


        jData = {"city":str(city),
              "area":str(area),
              "ca_type": str(ca_type),
              "type_basic":{
              "basic_engineering":k1,
              "hardware_engineering":k2,
              "decorated_home":k3,
              "home_appliances":k4
              },
              "type_advanced":{
              "basic_engineering":k5,
              "hardware_engineering":k6,
              "decorated_home":k7,
              "home_appliances":k8
              },
              "type_all":{
              "basic_engineering":k9,
              "hardware_engineering":k10,
              "decorated_home":k11,
              "home_appliances":k12
              }
        }
        json.dumps(jData)





    elif ca_type=="局部裝修":
        jData = {"city":str(city),
              "area":str(area),
              "ca_type": str(ca_type),
              "kitchen_renovation":"18-28",
              "bathroom_refurbishment":"15-21",
              "ceiling":"19-25",
              "windows_and_doors":"13-24",
              "floor_renewal":"18-29"
        }
        json.dumps(jData)
    else:
        jData = {}
        json.dumps(jData)
    
    return jData



@app.get("/genrequest")
async def genrequest():
    id_use = []
    ids = db.query("""SELECT hdesigner_id FROM _hdesigner d where d.emering=1 and d.onoff='1' order by rand();""")
    for c in ids:
        id_use.append(str(c["hdesigner_id"]))
    # with dataset.connect(connstr) as db:
    #     jData = json.load(open(hhhMBPath+'/json/designers.json', encoding='utf8'))
    jData_all = []
    for id in id_use:
        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'  ORDER BY sdate DESC LIMIT 6 """ )
        cases_list = []
        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"])
            }
            cases_list.append(tmpobj)
        
        records = db.query("""SELECT * FROM _hdesigner d WHERE d.hdesigner_id = '""" + id + """' AND d.onoff='1' """)
        for c in records:
            if c["service_phone"]!='':
                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=''
            jData={"hdesignerId":str(c["hdesigner_id"]),
                        "designerName":str(c["name"]),
                        "companyName": str(c["title"]),
                        'designerBigImg':"./img/"+id,
                        "designerIdea":str(c["idea"]),
                        "website":str(c["website"]),
                        "servicePhone":service_phone,
                        "fbUrl":str(c["fbpageurl"]),
                        "cardData": cases_list
                        }
        jData_all.append(jData)
    json.dumps(jData_all)
    return jData_all

#################################################################################################
# def GetList():
# #SELECT hcase_id,caption,name,sdate,mail,cover FROM _hcase c left join _hdesigner d on c.hdesigner_id = d.hdesigner_id where sdate= CURDATE();;    
#     #本週/粉絲/編輯推薦
# #     records = db.query("""SELECT hs.start_time,hs.end_time, ifnull(ca.hdesigner_id,co.hdesigner_ids) hid, ca.hdesigner_id,co.hdesigner_ids, oss.title sec
# # ,dd.title, dd.name, dd.mail, ifnull(ca.cover,co.clogo) img
# # FROM homepage_set hs
# # LEFT JOIN outer_site_set oss ON oss.oss_id = hs.outer_set
# # left join _hcase ca ON ca.hcase_id = hs.mapping_id AND hs.theme_type='case'
# # LEFT JOIN _hcolumn co ON hs.mapping_id = co.hcolumn_id AND hs.theme_type='column'
# # LEFT JOIN _hdesigner dd ON dd.hdesigner_id = ifnull(ca.hdesigner_id,co.hdesigner_ids)
# # WHERE hs.theme_type IN ('case','column')
# # and start_time BETWEEN DATE_ADD(CURDATE(), INTERVAL-15 DAY) AND DATE_ADD(NOW(), INTERVAL-1 DAY)
# # ORDER BY hs.ps_id
# # """)


#     records = db.query("""
#         SELECT hcase_id,caption,name,sdate,mail,cover,sales_mail FROM _hcase c left join _hdesigner d on c.hdesigner_id = d.hdesigner_id where sdate= CURDATE() and c.onoff=1;
#     """)
#     mList = []
#     for row in records:
        
#         sMail = str(row['mail'])
#         sName = str(row['name'])
#         sCap = str(row['caption'])
#         sSec = str(row['sdate'])
#         sImg = str(row['cover'])
#         sID = str(row['hcase_id'])
#         sSales_mail = str(row['sales_mail'])
#         # sDuration = row['start_time'].strftime("%Y/%m/%d") + '-' + row['end_time'].strftime("%Y/%m/%d")
#         mList.append([sMail,sCap,sName,sSec,sImg,sID,sSales_mail])



            
#     #首八大
# #     records = db.query("""SELECT hh.start_time,hh.end_time, hh.hdesigner_id hid, '首頁大' sec
# # ,dd.title, dd.name, dd.mail, adlogo img 
# # FROM _had hh
# # LEFT JOIN _hdesigner dd ON dd.hdesigner_id = hh.hdesigner_id
# # WHERE adtype LIKE '首八大%'
# # AND hh.onoff='1' AND dd.onoff = '1'
# # and start_time BETWEEN DATE_ADD(CURDATE(), INTERVAL-1 DAY) AND DATE_ADD(NOW(), INTERVAL-1 DAY)
# # -- 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)
# # """)

# #     for row in records:
# #         if str(row['hid']) != "":
# #             sMail = row['mail']
# #             sName = row['name']
# #             sCom = row['title']
# #             sSec = row['sec']
# #             sImg = row['img']
# #             sDuration = row['start_time'].strftime("%Y/%m/%d") + '-' + row['end_time'].strftime("%Y/%m/%d")
# #             mList.append([sMail,sCom,sName,sDuration,sSec,sImg])

#     return mList
#####################################################################################################################
    #print(sMail,sCom,sName,sDuration,sSec)



@app.post("/SendMail_to_sales")
async def SendMail_to_sales(request: CalculatorRequest):
    # Replace sender@example.com with your "From" address.
    # This address must be verified with Amazon SES.
    iName = request.name
    iPhone = request.phone
    iEmail = request.email
    iCity = request.city
    iArea = request.area
    iCtype = request.ca_type
    iSource = request.source

    #獲取當前時間
    now = datetime.now()
    #格式化時間
    iDate = now.strftime("%Y.%m.%d   %H:%M") 
    iHouse="無"


    SENDER = "Gorgeous Space - Mailer <noreply@hhh.com.tw>"

    # Replace recipient@example.com with a "To" address. If your account 
    # is still in the sandbox, this address must be verified.
    #RECIPIENT = iRecipient

    # RECIPIENTS = iRecipient.split(',')
    # CCS = iSale_mail.split(',')

    # RECIPIENTS = iRecipient.split(',')

    #寄給業務
    #RECIPIENTS = ["miko@hhh.com.tw","lily_huang@hhh.com.tw","dori@hhh.com.tw","wesson@hhh.com.tw"]
    RECIPIENTS = ["agent@hhh.com.tw"]
    #寄給開發
    #CCS=["oak@choozmo.com"]
    CCS=[]         
         
         
    
    # CCS.append("hhh_edit@hhh.com.tw")
    print(RECIPIENTS)
    print(CCS)
    ###之後要改成上面設計師email
    # RECIPIENTS = ["may@hhh.com.tw","jennifer_kao@hhh.com.tw",]
    #RECIPIENTS = ["hhh_edit@hhh.com.tw"]
    #CCS = ["hhh_edit@hhh.com.tw"]
    BCCS = []

    # Specify a configuration set. If you do not want to use a configuration
    # set, comment the following variable, and the 
    # ConfigurationSetName=CONFIGURATION_SET argument below.
    #CONFIGURATION_SET = "ConfigSet"

    # If necessary, replace us-west-2 with the AWS Region you're using for Amazon SES.
    AWS_REGION = "us-east-1"

    # The subject line for the email.
    SUBJECT = "幸福空間 裝修需求預算通知 (來自"+iName+'-'+iPhone+")"

    # The email body for recipients with non-HTML email clients.
    # BODY_TEXT = ("\r\n" + iName + " 設計師 , 您好:\r\n我是幸福空間編輯 , 您的個案已經完成並上線！\r\n幸福空間已於"+ iSection +"將您的個案露出於幸福空間網站上"+"\r\n個案名稱 :"+ iCap+"\r\n\r\n"+ iImg+"\r\n\r\n網址: https://hhh.com.tw/cases/detail/d/"+iId+"/\r\n煩請您上網查收點閱，若有需要增刪修改之處，還請不吝提出指教，謝謝！\r\n------------------------------------------------------------\r\n 幸福空間股份有限公司\r\n E-mail : service@choozmo.com \r\n  幸福空間 : https://hhh.com.tw \r\n ")
    # BODY_TEXT = ("\r\n" + " 以下為裝修需求")
    BODY_TEXT = ""
    #    :\r\n我是幸福空間編輯 , 您的個案已經完成並上線！\r\n幸福空間已於"+ iSection +"將您的個案露出於幸福空間網站上"+"\r\n個案名稱 :"+ iCap+"\r\n網址: https://hhh.com.tw/cases/detail/d/"+iId+"/\r\n煩請您上網查收點閱，若有需要增刪修改之處，還請不吝提出指教，謝謝！\r\n------------------------------------------------------------\r\n 幸福空間股份有限公司\r\n E-mail : hhh_edit@hhh.com.tw \r\n  幸福空間 : https://hhh.com.tw \r\n ")            
    # The HTML body of the email.
    # BODY_HTML = """<html>
    # <head></head>
    # <body>
    # <p>""" + iName + " 設計師 , 您好:<br />我是幸福空間編輯 , 您的個案已經完成並上線！<br />幸福空間已於"+ iSection + "將您的個案露出於幸福空間網站上<br />個案名稱 :"+ iCap+"<br /><br /><img style='width:40%' src='" + iImg + """' /><br /><br />網址: <a href='https://hhh.com.tw/cases/detail/d/"""+iId+"""/' target='_blank'>https://hhh.com.tw/cases/detail/d/"""+iId+"""/</a><br/>
    # 煩請您上網查收點閱，若有需要增刪修改之處，還請不吝提出指教，謝謝！<br/> 
    # ------------------------------------------------------------<br/> 
    # 幸福空間股份有限公司<br/> 
    # E-mail : service@choozmo.com <br/> 
    # 幸福空間 : https://hhh.com.tw <br/> 

    # </p>
    # </body>
    # </html>"""   
    # 读取图片并编码为Base64字符串
    with open('/var/www/html/hhh/genjson/request_template/mailhead.jpg', 'rb') as img_file:
        img_data = img_file.read()
        img_base64 = base64.b64encode(img_data).decode('utf-8')
        #print(img_data)
    BODY_HTML = """<html>
    <head></head>
    <body>
    <img src="https://hhh.com.tw/HHH/calculator_request/img/mailhead.jpg" width="800" height="53" alt="Image" usemap="#image-map">
     <map name="image-map">
                    <area target="_blank" alt="幸福空間" title="幸福空間" href="https://hhh.com.tw/" coords="13,8,154,56"
                        shape="rect">
                    <area target="_blank" alt="Facebook" title="Facebook" href="https://www.facebook.com/hhhfb"
                        coords="731,32,24" shape="circle">
                    <area target="_blank" alt="YouTube" title="YouTube" href="https://www.youtube.com/user/gorgeousspace"
                        coords="751,33,25" shape="circle">
                </map>
    <p>以下為裝修需求 <br />
    <p> 建立時間 : """ + iDate + "<br />姓名 : "+ iName +"<br />手機 : "+ iPhone+"<br />E-mail: "+ iEmail+"<br />縣市區域 : " + iCity +"<br />坪數 : "+ iArea+ "<br/>裝修類型:"+ iCtype+ "<br/>房屋類型:"+ iHouse+ "<br/>需求來源平台:"+ iSource +"<br/>"+"""
    ------------------------------------------------------------<br/> 
    幸福空間股份有限公司<br/> 
    E-mail : agent@hhh.com.tw <br/> 
    幸福空間 : https://hhh.com.tw <br/> 

    </p>
    </body>
    </html>"""  
    # The character encoding for the email.
    CHARSET = "UTF-8"


   # 创建邮件对象
    msg = MIMEMultipart('alternative')
    msg['Subject'] = SUBJECT
    msg['From'] = SENDER
    msg['To'] = ', '.join(RECIPIENTS)
    msg['Cc'] = ', '.join(CCS)


    # 添加图片附件
    #img_attachment = MIMEImage(img_data)
    #img_attachment.add_header('Content-Disposition', 'inline', filename='mailhead.jpg')
    #img_attachment.add_header('Content-ID', '<image_cid>')
    #msg.attach(img_attachment)

    # 创建邮件的HTML部分
    html_part = MIMEText(BODY_HTML, 'html','utf-8')
    print(html_part)
    msg.attach(html_part)
    



    # Create a new SES resource and specify a region.
    client = boto3.client('ses'
            ,aws_access_key_id='AKIAQG3PJZCHXPCSTAJG'
            ,aws_secret_access_key='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'
            ,region_name=AWS_REGION)

    # Try to send the email.
    try:
        response = client.send_raw_email(
            Source=SENDER,
            Destinations=RECIPIENTS + CCS + BCCS,
            RawMessage={'Data': msg.as_string()}
        )


    #     #Provide the contents of the email.
    #     response = client.send_email(
    #         Destination={
    #             'ToAddresses': RECIPIENTS,
    #             'CcAddresses': CCS,
    #             'BccAddresses': BCCS
    #         },
    #         Message={
    #             'Body': {
    #                 'Html': {
    #                     'Charset': CHARSET,
    #                     'Data': BODY_HTML,
    #                 },
    #                 'Text': {
    #                     'Charset': CHARSET,
    #                     'Data': BODY_TEXT,
    #                 },
    #             },
    #             'Subject': {
    #                 'Charset': CHARSET,
    #                 'Data': SUBJECT,
    #             },
    #         },
    #         Source=SENDER,
    #         # If you are not using a configuration set, comment or delete the
    #         # following line
    #         #ConfigurationSetName=CONFIGURATION_SET,
    #     )
    # # Display an error if something goes wrong. 

    except ClientError as e:
        print(e.response['Error']['Message'])
    else:
        print("Email sent! Message ID:"),
        print(response['MessageId'])
    return True





@app.post("/SendMail_to_customer")
# async def SendMail_to_customer(iRecipient:str,iSale_mail:str, iDate:str,iPhone:str, iName:str, iCity:str, iArea:str,iCtype:str,iEmail:str):
async def SendMail_to_customer(request: CalculatorRequest):
    # Replace sender@example.com with your "From" address.
    # This address must be verified with Amazon SES.
    SENDER = "Gorgeous Space - Mailer <noreply@hhh.com.tw>"

    # Replace recipient@example.com with a "To" address. If your account 
    # is still in the sandbox, this address must be verified.
    #RECIPIENT = iRecipient
    iName = request.name
    iPhone = request.phone
    iEmail = request.email
    iCity = request.city
    iArea = request.area
    iCtype = request.ca_type
    

    #獲取當前時間
    now = datetime.now()
    #格式化時間
    iDate = now.strftime("%Y.%m.%d   %H:%M") 
    iHouse="無"


    # RECIPIENTS = iRecipient.split(',')
    # CCS = iSale_mail.split(',')

    #寄給填寫email
    RECIPIENTS = []
    RECIPIENTS.append(iEmail)

    #寄給開發
    #CCS=['oak@choozmo.com']
    CCS=[]

    CCS.append("agent@hhh.com.tw")
    print(RECIPIENTS)
    print(CCS)
    ###之後要改成上面設計師email
    # RECIPIENTS = ["may@hhh.com.tw","jennifer_kao@hhh.com.tw",]
    #RECIPIENTS = ["hhh_edit@hhh.com.tw"]
    #CCS = ["hhh_edit@hhh.com.tw"]
    BCCS = []

    # Specify a configuration set. If you do not want to use a configuration
    # set, comment the following variable, and the 
    # ConfigurationSetName=CONFIGURATION_SET argument below.
    #CONFIGURATION_SET = "ConfigSet"

    # If necessary, replace us-west-2 with the AWS Region you're using for Amazon SES.
    AWS_REGION = "us-east-1"

    # The subject line for the email.
    SUBJECT = "幸福空間 裝修需求預算通知 (來自"+iName+'-'+iPhone+")"

    # The email body for recipients with non-HTML email clients.
    # BODY_TEXT = ("\r\n" + iName + " 設計師 , 您好:\r\n我是幸福空間編輯 , 您的個案已經完成並上線！\r\n幸福空間已於"+ iSection +"將您的個案露出於幸福空間網站上"+"\r\n個案名稱 :"+ iCap+"\r\n\r\n"+ iImg+"\r\n\r\n網址: https://hhh.com.tw/cases/detail/d/"+iId+"/\r\n煩請您上網查收點閱，若有需要增刪修改之處，還請不吝提出指教，謝謝！\r\n------------------------------------------------------------\r\n 幸福空間股份有限公司\r\n E-mail : service@choozmo.com \r\n  幸福空間 : https://hhh.com.tw \r\n ")
    # BODY_TEXT = ("\r\n" + " 以下為裝修需求")
    BODY_TEXT = ""
    #    :\r\n我是幸福空間編輯 , 您的個案已經完成並上線！\r\n幸福空間已於"+ iSection +"將您的個案露出於幸福空間網站上"+"\r\n個案名稱 :"+ iCap+"\r\n網址: https://hhh.com.tw/cases/detail/d/"+iId+"/\r\n煩請您上網查收點閱，若有需要增刪修改之處，還請不吝提出指教，謝謝！\r\n------------------------------------------------------------\r\n 幸福空間股份有限公司\r\n E-mail : hhh_edit@hhh.com.tw \r\n  幸福空間 : https://hhh.com.tw \r\n ")            
    # The HTML body of the email.
    # BODY_HTML = """<html>
    # <head></head>
    # <body>
    # <p>""" + iName + " 設計師 , 您好:<br />我是幸福空間編輯 , 您的個案已經完成並上線！<br />幸福空間已於"+ iSection + "將您的個案露出於幸福空間網站上<br />個案名稱 :"+ iCap+"<br /><br /><img style='width:40%' src='" + iImg + """' /><br /><br />網址: <a href='https://hhh.com.tw/cases/detail/d/"""+iId+"""/' target='_blank'>https://hhh.com.tw/cases/detail/d/"""+iId+"""/</a><br/>
    # 煩請您上網查收點閱，若有需要增刪修改之處，還請不吝提出指教，謝謝！<br/> 
    # ------------------------------------------------------------<br/> 
    # 幸福空間股份有限公司<br/> 
    # E-mail : service@choozmo.com <br/> 
    # 幸福空間 : https://hhh.com.tw <br/> 

    # </p>
    # </body>
    # </html>"""            
    BODY_HTML = """<html>
    <head></head>
    <body>
    <img src="https://hhh.com.tw/HHH/calculator_request/img/mailhead.jpg" width="800" height="53" alt="Image" usemap="#image-map">
     <map name="image-map">
                    <area target="_blank" alt="幸福空間" title="幸福空間" href="https://hhh.com.tw/" coords="13,8,154,56"
                        shape="rect">
                    <area target="_blank" alt="Facebook" title="Facebook" href="https://www.facebook.com/hhhfb"
                        coords="731,32,24" shape="circle">
                    <area target="_blank" alt="YouTube" title="YouTube" href="https://www.youtube.com/user/gorgeousspace"
                        coords="751,33,25" shape="circle">
                </map>
    <p>親愛的"""+iName+"""，<br />
    <p> 非常感謝您在幸福空間官網留下您的裝修需求。<br/> 以下是您填寫的資訊：<br/>"""+ "<br />建立時間: "+ iDate  + "<br />姓名 : "+ iName +"<br />手機 : "+ iPhone+"<br />E-mail: "+ iEmail+"<br />縣市區域 : " + iCity +"<br />坪數 : "+ iArea+ "<br/>裝修類型:"+ iCtype+ "<br/>"+"""
    <br/>
    幸福經紀人將後續與您聯繫，以便確認您的需求，<br/>
    並向您推薦最適合的裝修方案。<br/>
    <br/>
    歡迎您至幸福空間官網參考更多設計風格案例作品<br/>
    <br/>
    </p>
    <img src="https://hhh.com.tw/HHH/calculator_request/img/mailfooter.jpg" width="800" height="600" alt="Image" usemap="#image-map1">
    <map name="image-map1">
     <area shape="rect" coords="64,88,388,242" href="https://hhh.com.tw/photos/lists/modern-style/" target="_blank" />
        <!-- 右上 -->
        <area shape="rect" coords="412,91,733,239" href="https://hhh.com.tw/photos/lists/nordic-style/" target="_blank" />
        <!-- 左下 -->
        <area shape="rect" coords="64,266,387,413" href="https://hhh.com.tw/photos/lists/american-style/" target="_blank" />
        <!-- 右下 -->
        <area shape="rect" coords="419,265,730,410" href="https://hhh.com.tw/photos/lists/mashup-style/" target="_blank" />   

        <area shape="rect" coords="0,422,795,596" href="https://event.hhh.com.tw/agent/" target="_blank" />        
    </map>
    </body>
    </html>"""  
    # The character encoding for the email.
    CHARSET = "UTF-8"

    # Create a new SES resource and specify a region.
    client = boto3.client('ses'
            ,aws_access_key_id='AKIAQG3PJZCHXPCSTAJG'
            ,aws_secret_access_key='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'
            ,region_name=AWS_REGION)

    # Try to send the email.
    try:
        #Provide the contents of the email.
        response = client.send_email(
            Destination={
                'ToAddresses': RECIPIENTS,
                'CcAddresses': CCS,
                'BccAddresses': BCCS
            },
            Message={
                'Body': {
                    'Html': {
                        'Charset': CHARSET,
                        'Data': BODY_HTML,
                    },
                    'Text': {
                        'Charset': CHARSET,
                        'Data': BODY_TEXT,
                    },
                },
                'Subject': {
                    'Charset': CHARSET,
                    'Data': SUBJECT,
                },
            },
            Source=SENDER,
            # If you are not using a configuration set, comment or delete the
            # following line
            #ConfigurationSetName=CONFIGURATION_SET,
        )
    # Display an error if something goes wrong. 
    except ClientError as e:
        print(e.response['Error']['Message'])
    else:
        print("Email sent! Message ID:"),
        print(response['MessageId'])
    return True





# for item in GetList():
#     #print(item)
#     SendMail(item[0],item[1],item[2],item[3],item[4],item[5],item[6])
















    # with dataset.connect(connstr) as db:
    #     jData = json.load(open(hhhMBPath+'/json/designers.json', encoding='utf8'))
    # 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'  ORDER BY sdate DESC LIMIT 6 """ )
    # cases_list = []
    # 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"])
    #     }
    #     cases_list.append(tmpobj)
    
    # records = db.query("""SELECT * FROM _hdesigner d WHERE d.hdesigner_id = '""" + id + """' AND d.onoff='1' """)
    # jData = []
    # for c in records:
    #     if c["service_phone"]!='':
    #         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=''
    #     jData.append({"hdesigner_id":str(c["hdesigner_id"]),
    #                 "DesignerName":str(c["name"]),
    #                 "CompanyName": str(c["title"]),
    #                 'Designerbigimg':"./img/"+id,
    #                 "DesignerIdea":str(c["idea"]),
    #                 "Website":str(c["website"]),
    #                 "Service_phone":service_phone,
    #                 "FB_url":str(c["fbpageurl"]),
    #                 "Carddata": cases_list
    #                 })
    # json.dumps(jData)
    # return jData
    


    # 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["BannerImg"] = c["background"]
    #             x["BannerImg_mobile"] = c['background_mobile']
    #             x["CompanyName"] = c["title"]
    #             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"]!='':
    #                 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})
    #             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",
    #                     "link": c["fbpageurl"]},
    #                 {"name": "Line", "img": "https://hhh.com.tw/assets/images/rv_web/line.svg",
    #                     "link": c["line_link"]},
    #                 {"name": "Wechat", "img": "https://hhh.com.tw/assets/images/rv_web/wechat.svg",
    #                     "link": c["fbpageurl"]},
    #                 {"name": "email", "img": "https://hhh.com.tw/assets/images/rv_web/share.svg",
    #                     "link": c["mail"]},
    #                 {"name": "Like", "img": "https://hhh.com.tw/assets/images/rv_web/like-o.svg", "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'; """ )
    #         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' ;""")
    #         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' 
    # 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
    # """)

    #         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
    # """)

    #         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
    # """)

    #         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) """