"""
同步正式环境企业表
"""

from datetime import datetime
import requests
import urllib3

urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
from base.BaseCore import BaseCore

baseCore = BaseCore()
log = baseCore.getLogger()
# 144数据库
cnx_ = baseCore.cnx
cursor_ = baseCore.cursor

# 11数据库
cnx = baseCore.cnx_
cursor = baseCore.cursor_

r = baseCore.r


# 查询正式环境表（两张表） 判断测试环境是否有这一条数据
# 有 判断字段是否都有 如英文名称。再去上市表中判断
# 没有 新增

def search_formal_table(condition, table_name, field_name, field_value, cursor_c):
    """
    查询正式环境表
    :param table_name: 表名
    :param field_name: 字段名
    :param field_value: 字段值
    :return:
    """
    query = "SELECT {} FROM {} WHERE {} = '{}'".format(condition, table_name, field_name, field_value)
    cursor_c.execute(query)
    result = cursor_c.fetchall()
    return result


def update_table(update_sql, cursor_c, cnx_c):
    cursor_c.execute(update_sql)
    cnx_c.commit()


if __name__ == "__main__":
    key = 'Synchronize_data:info'
    # result = search_formal_table('social_credit_code, name, english_name', 'sys_base_enterprise', 'yn_domestic', '1', cursor)
    # for row in result:
    #     social_credit_code = row[0]
    #     name = row[1]
    #     english_name = row[2]
    #     if not english_name:
    #         english_name = ''
    #     item = social_credit_code + '|' + name + '|' + english_name
    #     baseCore.rePutIntoR(key, item)
    while True:
        info = baseCore.redicPullData(key)
        if info == None:
            break
        else:
            pass
        com_code = info.split('|')[0]
        com_name = info.split('|')[1]
        com_english_name = info.split('|')[2]
        result = search_formal_table('CompanyName, SocialCode, EnglishName, SecuritiesCode, SecuritiesShortName, Place, isIPO, SecuritiesType, Category, Exchange, countryName', 'EnterpriseInfo',
                                     'SocialCode', com_code, cursor_)
        u_name, u_code, u_ename, u_short_name, u_type, u_category, u_exchange = '', '', '', '', '', '', ''
        # 更新语句
        update_sql = """update EnterpriseInfo set {} where SocialCode = {}"""
        fields = ''
        if result:
            # 判断这几个值是否为空
            if result[0][0] != com_name:
                u_name = com_name
                fields = f'CompanyName = "{com_name}", '
            if not result[0][2] and com_english_name:
                u_ename = com_english_name
                fields += f'EnglishName = "{com_english_name}", '
            if not result[0][5]:
                u_place = '1'
                fields += f'Place = "{u_place}", '
            if not result[0][10]:
                u_countryname = '中国内地'
                fields += f'countryName = "{u_countryname}", '
            if not result[0][3]:
                result_ipo = search_formal_table('social_credit_code, securities_code, securities_short_name, securities_type, category,exchange', 'sys_base_enterprise_ipo', 'social_credit_code',
                                                 com_code, cursor)
                if result_ipo:
                    # 是上市企业
                    if not result[0][6]:
                        u_ipo = '1'
                        fields += f'IsIPO = "{u_ipo}", '
                    if result_ipo[0][1]:
                        u_code = result_ipo[0][1]
                        fields += f'SecuritiesCode = "{u_code}", '
                    if not result[0][4] and result_ipo[0][2]:
                        u_short_name = result_ipo[0][2]
                        fields += f'SecuritiesShortName = "{u_short_name}", '
                    if not result[0][7] and result_ipo[0][3]:
                        u_type = result_ipo[0][3]
                        fields += f'SecuritiesType = "{u_type}", '
                    if not result[0][8] and result_ipo[0][4]:
                        u_category = result_ipo[0][4]
                        fields += f'Category = "{u_category}", '
                    if not result[0][9] and result_ipo[0][5]:
                        u_exchange = result_ipo[0][5]
                        fields += f'Exchange = "{u_exchange}", '
                else:  # 可能不是上市企业
                    # if fields:
                    #     update_sql = update_sql.format(fields.rstrip(', '), f'"{com_code}"')
                    #     print(update_sql)
                    #     continue
                    pass

            if fields:
                update_sql = update_sql.format(fields.rstrip(', '), f'"{com_code}"')
                log.info(f'更新的sql语句--{update_sql}')
                update_table(update_sql, cursor_, cnx_)
        else:
            result_ipo = search_formal_table('social_credit_code, securities_code, securities_short_name, securities_type, category,exchange', 'sys_base_enterprise_ipo', 'social_credit_code',
                                             com_code, cursor)
            if result_ipo:
                SecuritiesCode = result_ipo[1]
                SecuritiesShortName = result_ipo[2]
                securities_type = result_ipo[3]
                Category = result_ipo[4]
                exchange = result_ipo[5]
                sqlInsert = 'insert into EnterpriseInfo(CompanyName, SocialCode, EnglishName, SecuritiesCode, SecuritiesShortName, Place, isIPO, SecuritiesType, Category, Exchange, countryName) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
                baseCore.cursor.execute(sqlInsert, (com_name, com_code, com_english_name, SecuritiesCode, 1, 1, securities_type, Category, exchange, '中国内地'))
                baseCore.cnx.commit()
                log.info(f'{com_name}==={com_name}===上市企业===插入成功')
            else:
                sqlInsert = 'insert into EnterpriseInfo(CompanyName, SocialCode, EnglishName, Place, isIPO, countryName) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
                baseCore.cursor.execute(sqlInsert, (com_name, com_code, com_english_name, 1, 1, '中国内地'))
                baseCore.cnx.commit()
                log.info(f'{com_name}==={com_name}===非上市企业===插入成功')