hhh_index_dev/hhh_index/genjson/gen_realtime.py

import os
from typing import Optional
from dataset.util import ResultIter
from datetime import tzinfo
import datetime
import mysql.connector
from mysql.connector import Error
import dataset
import json
from pytube import extract
import requests
access_token = "57e1d6642904ecc2348b2768b028b5dd74720f791c7a3fd7ec551db3fd12a303"

hhhMBPath = '../hhh-home-mb'
hhhPCPath = '../hhh-home-pc'

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

""" db = dataset.connect(
    'mysql://hhh7796hhh:lYmWsu^ujcA1@hhh-v57.cmab1ctkglka.ap-northeast-2.rds.amazonaws.com:3306/xoops?charset=utf8mb4') """
#        charset='utf8',
#        collation='utf8_unicode_ci'

#2022/12/13 change
connobj = {
    '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'
  }

#connobj = {
  #  'host': 'hhh-v57.cmab1ctkglka.ap-northeast-2.rds.amazonaws.com',
   # 'database': 'xoops',
   # 'user': 'hhh7796hhh',
   # 'password': 'lYmWsu^ujcA1',
   # 'use_unicode': True
   # #'charset': 'utf8',
   # #'collation': 'utf8_unicode_ci'
#}

def ExecuteQuery(isql):
    with mysql.connector.connect(**connobj) as connection :
        with connection.cursor(dictionary=True) as cursor:
    # connection.set_charset_collation('utf8','utf8_general_ci')
            #cursor = connection.cursor(dictionary=True)
            cursor.execute(isql)
            return cursor.fetchall()
def genjson_new(filename: str = "realtime.json"):
    
    jData = json.load(open('../json/'+filename, 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,logo_icon,index_char_1,index_char_2_1,index_char_2_2,index_char_2_3 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:
                if c['index_char_1']==None:
                    c['index_char_1']=''
                if c['index_char_2_1']==None:
                    c['index_char_2_1']=''
                if c['index_char_2_2']==None:
                    c['index_char_2_2']=''
                if c['index_char_2_3']==None:
                    c['index_char_2_3']=''
                a = {'imgUrl': c['mlo'], 'link': str(
                    c['lk']), 'DimgUrl': c['lo'], 'webp': str(c['lomwebp']), 'Dwebp': str(c['dwebp']),'logo_icon':str(c['logo_icon']),'index_char_1':str(c['index_char_1']),'index_char_2_1':str(c['index_char_2_1']),'index_char_2_2':str(c['index_char_2_2']),'index_char_2_3':str(c['index_char_2_3'])}
                x["data"].append(a)
            # print(x["data"])

        #tab區塊-最夯設計, 影音實錄, 專欄文章
        if x['id'] == 6:
            x["data"] = []
            #過年後修改
            a = {'tab': '編輯精選', 'data': []}
            records_for_custom = ExecuteQuery("""SELECT hcolumn_id, ctitle,clogo,cdesc,_hcolumn.ctag
                            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 limit 4""")
            
            
            #加入不重複id
            id_use = []
            for c in records_for_custom:
                id_use.append(c['hcolumn_id'])
            id_len = 8 - len(id_use)
            # print(id_use)
            str_id_use=''
            for k in id_use:
                str_id_use+=' and hcolumn_id!=%s '%k
            # print(str_id_use)



            #加入不重複id sql
            # records = ExecuteQuery("""SELECT ctag,ctitle TT,clogo IMG, CONCAT('/columns/detail/',hcolumn_id,'/') LK, cdesc
            #                 from _hcolumn
            #                 WHERE onoff='1' """+str_id_use+"""
            #                 AND NOW() > sdate
            #                 ORDER BY hcolumn_id DESC
            #                 LIMIT 8""")
            records = ExecuteQuery("""SELECT ctag,ctitle TT,clogo IMG, CONCAT('/columns/detail/',hcolumn_id,'/') LK, cdesc
                            from _hcolumn
                            WHERE onoff='1' """ +str_id_use+"""
                            AND NOW() > sdate
                            ORDER BY  sdate desc,hcolumn_id DESC
                            LIMIT """+ str(id_len) +""" """)


            count_k=1
            for c in records:
                ctag_list=c['ctag'].split(',')
                if len(ctag_list)==1 and ctag_list[0]=='':
                    ctag_list=[]
                ad = {'imgUrl': c['IMG'], 'link': c['LK'],
                    'title': c['TT'],'ctag':ctag_list}
                a['data'].append(ad)
                if count_k==4:
                    for d in records_for_custom:
                        ad = {'imgUrl': d['clogo'], 'link': "/columns/detail/" + str(
                            d['hcolumn_id']) + "/", 'title': d['ctitle'], 'ctag':d['ctag'].split(',')}
                        a["data"].append(ad)
                count_k+=1


            
            x["data"].append(a)






            


           



            a = {'tab': '最夯設計', 'data': []}
            records_for_custom = ExecuteQuery(""" SELECT  hcase_id,caption TT,tag ,cover IMG, CONCAT('/cases/detail/',hcase_id,'/') LK,_hdesigner.hdesigner_id hid,img_path,title
                            FROM homepage_set
                            LEFT JOIN _hcase ON mapping_id = hcase_id
                            left join _hdesigner ON _hcase.hdesigner_id=_hdesigner.hdesigner_id
                            WHERE outer_set=2 and  _hcase.onoff='1' AND _hdesigner.onoff='1' 
                            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 limit 4""")
            #加入不重複id
            id_use = []
            for c in records_for_custom:
                id_use.append(c['hcase_id'])
            id_len = 8 - len(id_use)
            # print(id_use)
            str_id_use=''
            for k in id_use:
                str_id_use+=' and hcase_id!=%s '%k

            records = ExecuteQuery("""SELECT hcase_id,caption TT,tag ,cover IMG, CONCAT('/cases/detail/',hcase_id,'/') LK,_hdesigner.hdesigner_id hid,img_path,title
                            from _hcase
                            left join _hdesigner ON _hcase.hdesigner_id=_hdesigner.hdesigner_id
                            WHERE
                            _hcase.onoff='1' AND _hdesigner.onoff='1' 
                            AND (NOW() > sdate)  """+str_id_use+"""
                            ORDER BY  sdate desc,hcase_id DESC
                            LIMIT """+ str(id_len) +""" """)

            count_k=1
            for c in records:
                # print(c['hcase_id'])
                ctag_list="、".join(c['tag'].split(',')).split("、")
                if len(ctag_list)==1 and ctag_list[0]=='':
                    ctag_list=[]
                ad = {'imgUrl': c['IMG'], 'link': c['LK'],'img_designer':c['img_path'],
                    'title': c['TT'],'ctag':ctag_list,'title_designer':c['title'],'link_designer': "/HHH_NEW/designers/index_designerList.php?cid=" + str(c['hid'])}
                a['data'].append(ad)
                if count_k==4:
                    for d in records_for_custom:
                        ctag_list="、".join(d['tag'].split(',')).split("、")
                        if len(ctag_list)==1 and ctag_list[0]=='':
                            ctag_list=[]
                        ad = {'imgUrl': d['IMG'], 'link': d['LK'],'img_designer':d['img_path'],
                    'title': d['TT'],'ctag':ctag_list,'title_designer':d['title'],'link_designer': "/HHH_NEW/designers/index_designerList.php?cid=" + str(c['hid'])}
                        a["data"].append(ad)
                count_k+=1
            x["data"].append(a)









            a = {'tab': '最新影音實錄', 'data': []}



            records_for_custom = ExecuteQuery(""" SELECT  hvideo_id,title TT,iframe IMG , CONCAT('https://hhh.com.tw/video-post.php?id=',hvideo_id) LK , name
                            FROM homepage_set
                            LEFT JOIN _hvideo ON mapping_id = hvideo_id
                            WHERE outer_set=3 
                            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 limit 4""")
            #加入不重複id
            id_use = []
            for c in records_for_custom:
                id_use.append(c['hvideo_id'])
            id_len = 8 - len(id_use)
            # print(id_use)
            str_id_use=''
            for k in id_use:
                str_id_use+=' and hvideo_id!=%s '%k

            records = ExecuteQuery("""SELECT title TT,iframe IMG , CONCAT('https://hhh.com.tw/video-post.php?id=',hvideo_id) LK , name
                            from _hvideo where display_datetime < NOW() """+str_id_use+"""
                            ORDER BY hvideo_id DESC
                            LIMIT """+ str(id_len) +""" """)
            count_k=1
            for c in records:
                tid = extract.video_id(c['IMG'])
                timg = "https://img.youtube.com/vi/" + tid+"/hqdefault.jpg"
                ad = {'imgUrl': timg, 'link': "https://www.youtube.com/watch?v="+str(tid),
                     'description': c['TT']}
                a['data'].append(ad)
                if count_k==4:
                    for d in records_for_custom:
                        tid = extract.video_id(d['IMG'])
                        timg = "https://img.youtube.com/vi/" + tid+"/hqdefault.jpg"
                        ad = {'imgUrl': timg, 'link': "https://www.youtube.com/watch?v="+str(tid),
                            'description': d['TT']}
                        a["data"].append(ad)
                count_k+=1

            x["data"].append(a)

            
            # print(x["data"])

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

            x["data"] = []
            #手機板
            a = {'tab': 'phone', 'data': []}
            for c in records:
                phone = {'imgUrl': c['lo'], 'link': str(
                    c['lk']),  'title': c['title']} #'video': 'false', 'description': c['desc'],
                a["data"].append(phone)
            x["data"].append(a)
            #電腦版
            records = ExecuteQuery("""SELECT theme_type, mapping_id, IFNULL(ifnull(ifnull(ifnull(_hcase.caption,_hcolumn.ctitle),_hproduct.name),_hvideo.title),_hbrand.title) caption , 
                IFNULL(ifnull(ifnull(_hcase.cover,_hcolumn.clogo),_hproduct.cover),_hbrand.logo) J,
(case when theme_type='case' then CONCAT('/cases/detail/d/',mapping_id) when theme_type='column' then CONCAT('/columns/detail/',mapping_id) when theme_type='product' then CONCAT('/product-post.php?id=',mapping_id) when theme_type='video' then CONCAT('https://hhh.com.tw/video-post.php?id=',mapping_id) when theme_type='brand' then CONCAT('/brand-index.php?brand_id=',mapping_id) ELSE '' END) url
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'
LEFT JOIN _hcolumn ON mapping_id = _hcolumn.hcolumn_id AND theme_type='column'
LEFT JOIN _hvideo ON mapping_id = _hvideo.hvideo_id AND theme_type='video'
LEFT JOIN _hbrand ON mapping_id = _hbrand.hbrand_id AND theme_type='brand'
WHERE homepage_set.onoff='Y' and outer_set=1 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 asc limit 4""")
            a = {'tab': 'web', 'data': []}
            for c in records:
                phone = {'imgUrl': c['J'], 'link': str(
                    c['url']),  'title': c['caption']} #'video': 'false', 'description': c['desc'],
                a["data"].append(phone)
            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'] == 3:
            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 = 8
#             # for c in records:
#             #     maxrow = c['max_row']

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

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

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

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

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

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

#             x["data"] = []
#             for c in records:
#                 if c['iframe'] is None:
#                     if isinstance(c['J'], bytearray) or isinstance(c['J'], bytes):
#                         c['J'] = c['J'].decode('utf8')
#                     if isinstance(c['caption'], bytearray) or isinstance(c['caption'], bytes):
#                         c['caption'] = c['caption'].decode('utf8')
#                     if isinstance(c['short_desc'], bytearray) or isinstance(c['short_desc'], bytes):
#                         c['short_desc'] = c['short_desc'].decode('utf8')
#                     a = {'imgUrl': c['J'], 'link': c['url'], 'title': c['caption'],
#                         'description': c['short_desc'], 'video': 'false'}
#                 else:
#                     tid = extract.video_id(str(c['iframe']))
#                     timg = "https://img.youtube.com/vi/" + tid+"/hqdefault.jpg"
#                     ccaption = ""
#                     cdescription = ""
#                     if isinstance(c['caption'], bytearray):
#                         ccaption = str(c['caption'].decode('utf8'))
#                     else:
#                         ccaption = str(c['caption'])
#                     if c['short_desc'] is not None:
#                         if isinstance(c['short_desc'], bytes):
#                             cdescription = str(c['short_desc'].decode('utf8'))
#                         else:
#                             cdescription = str(c['short_desc'])
#                     a = {'imgUrl': timg, 'link': c['url'], 'title': ccaption,
#                         'description': cdescription, 'video': tid}
#                 x["data"].append(a)
        # # 推薦設計師
        # if x['id'] == 5:
        #     records = ExecuteQuery("""SELECT hdesigner_id,title,img_path,name FROM _hdesigner where onoff=1 order by dorder limit 12;
        #                         """)

        #     x["data"] = []
        #     for c in records:
        #         a = {'imgUrl': c['img_path'],'name': c['title'] ,'designers_name':c['name'],'link': "/HHH_NEW/designers/index_designerList.php?cid=" + str(c['hdesigner_id'])}
        #         x["data"].append(a)
        # 推薦設計師
        if x['id'] == 5:
            records_for_custom = ExecuteQuery(""" SELECT  hd.hdesigner_id,hd.title,hd.img_path,hd.name
                            FROM homepage_set
                            LEFT JOIN _hdesigner hd ON mapping_id = hd.hdesigner_id
                            
                            WHERE outer_set=9  AND hd.onoff='1' 
                            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 limit 6""")
            #加入不重複id
            id_use = []
            for g in records_for_custom:
                id_use.append(g['hdesigner_id'])
            id_len = 12 - len(id_use)
            print(id_use)
            str_id_use=''
            for k in id_use:
                str_id_use+=' and hdesigner_id!=%s '%k



            records = ExecuteQuery("""SELECT hdesigner_id,title,img_path,name FROM _hdesigner where onoff=1 """+str_id_use +""" order by dorder limit """+ str(id_len) +""" """)

            x["data"] = []
            count_k=1
            for c in records:
                a = {'imgUrl': c['img_path'],'name': c['title'] ,'designers_name':c['name'],'link': "/HHH_NEW/designers/index_designerList.php?cid=" + str(c['hdesigner_id'])}
                if count_k==7:
                    for d in records_for_custom:
                        q = {'imgUrl': d['img_path'],'name': d['title'] ,'designers_name':d['name'],'link': "/HHH_NEW/designers/index_designerList.php?cid=" + str(d['hdesigner_id'])}
                        x["data"].append(q)
                count_k+=1


                x["data"].append(a)
#=======================================================================================================================
        # 加好物API


        if x['id'] == 7:
            url = 'https://open.shopline.io/v1/products/search'
            params = {'per_page': 200, 'page': 1,"category_id":"6108deabe15ff9003e5cef08"}
            headers = {
                'accept': 'application/json',
                'authorization': 'Bearer '+access_token,
                'User-Agent': 'Shopline test'
                    }

            response = requests.get(url, params=params, headers=headers)



            use=response.json()
            

            x["data"] = []
            # for i in range(len(use['items'])):
            for i in range(len(use['items'])):
                if use['items'][i]['status']=='active':
                    title = use['items'][i]['title_translations']['zh-hant']
                    link = use['items'][i]['link']
                    img = use['items'][i]['medias'][0]['images']['original']['url']
                    a = {'imgUrl': img,'title': title ,'link':'https://shop.hhh.com.tw/products'+link}
                    x["data"].append(a)

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

            # print(x["data"])

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

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

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

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

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

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

    return jData

genjson_new("data_index.json")
cmd="""curl -X POST "https://api.cloudflare.com/client/v4/zones/8cb58022ad5743cfd9f088d5e3a261eb/purge_cache" -H "X-Auth-Email: web.dept.hhh@gmail.com"  -H "X-Auth-Key:63e51d9a1638d66afcbfc3320aabec52304de" -H "Content-Type: application/json" --data '{"files":["https://hhh.com.tw/hhh_index/json/data_index.json"]}'"""
os.system(cmd)