import time

import pymysql

from base import BaseCore
from apscheduler.schedulers.blocking import BlockingScheduler

basecore = BaseCore.BaseCore()
log = basecore.getLogger()

r = basecore.r

def cnn11():
    #11数据库
    cnx_ = pymysql.connect(host='114.116.44.11', user='caiji', password='f7s0&7qqtK', db='clb_project', charset='utf8mb4')
    cursor_ = cnx_.cursor()
    return cnx_,cursor_
def close11(cnx_,cursor_):
    cnx_.close()
    cursor_.close()

# # 连接到Redis
# r = redis.Redis(host="114.115.236.206", port=6379, password='clbzzsn', db=6)
#
# cnx = pymysql.connect(host='114.115.159.144', user='caiji', password='zzsn9988', db='caiji',
#                            charset='utf8mb4')
# cursor = cnx.cursor()

# def pullDateFromSql():
#     gn_query = "select SocialCode from EnterpriseInfo where Place = '1' "
#     cursor.execute(gn_query)
#     gn_result = cursor.fetchall()
#
#     gw_query = "select SocialCode from EnterpriseInfo where Place = '2' "
#     cursor.execute(gw_query)
#     gw_result = cursor.fetchall()
#
#     gw_social_list = [item[0] for item in gw_result]
#     gn_social_list = [item[0] for item in gn_result]
#     return gn_social_list,gw_social_list

#企业动态

#创建数据库连接
def connectSql():
    cnx = basecore.cnx
    cursor = basecore.cursor
    return cnx,cursor

#关闭数据库连接
def closeSql(cnx,cursor):
    cnx.close()
    cursor.close()

def NewsEnterprise():
    cnx,cursor = connectSql()
    # #获取国内企业
    # gn_query = "select SocialCode from EnterpriseInfo where Place = '1'"
    # cursor.execute(gn_query)
    # gn_result = cursor.fetchall()
    #获取国外企业
    gw_query = "select SocialCode from EnterpriseInfo where Place = '2'"
    cursor.execute(gw_query)
    gw_result = cursor.fetchall()
    cnx.commit()
    gw_social_list = [item[0] for item in gw_result]
    #todo:打印长度
    # print(len(gw_social_list))
    # gn_social_list = [item[0] for item in gn_result]
    print('=======')
    #将数据插入到redis中
    # for item in gn_social_list:
    #     r.rpush('NewsEnterprise:gnqy_socialCode', item)

    for item in gw_social_list:
        r.rpush('NewsEnterprise:gwqy_socialCode', item)
    closeSql(cnx,cursor)

#企业动态定时任务
def NewsEnterprise_task():
    # 实例化一个调度器
    scheduler = BlockingScheduler()
    # 每天执行一次
    scheduler.add_job(NewsEnterprise, 'cron', hour=0,minute=0,max_instances=2)
    try:
        # redisPushData  # 定时开始前执行一次
        NewsEnterprise()
        scheduler.start()
    except Exception as e:
        print('定时采集异常', e)
        pass

#企业公告
def NoticeEnterprise():
    cnx,cursor = connectSql()
    # 获取国内企业
    gn_query = "select SocialCode from EnterpriseInfo where Place = '1' and SecuritiesCode is not null "
    cursor.execute(gn_query)
    cnx.commit()
    gn_result = cursor.fetchall()
    gn_social_list = [item[0] for item in gn_result]
    print('=======')
    for item in gn_social_list:
        r.rpush('NoticeEnterprise:gnqy_socialCode', item)
    closeSql(cnx,cursor)

#企业公告定时任务
def NoticeEnterprise_task():
    # 实例化一个调度器
    scheduler = BlockingScheduler()
    # 每天执行一次
    scheduler.add_job(NoticeEnterprise, 'cron', hour=12,minute=0)
    try:
        # redisPushData  # 定时开始前执行一次
        scheduler.start()
    except Exception as e:
        print('定时采集异常', e)
        pass

#企业基本信息
def BaseInfoEnterprise():
    cnx,cursor = connectSql()
    # 获取国内企业
    gn_query = "select SocialCode from EnterpriseInfo where Place = '1'"
    cursor.execute(gn_query)
    gn_result = cursor.fetchall()
    cnx.commit()
    gn_social_list = [item[0] for item in gn_result]
    print('=======')
    for item in gn_social_list:
        r.rpush('BaseInfoEnterprise:gnqy_socialCode', item)
    closeSql(cnx,cursor)

#企业基本信息定时任务
def BaseInfoEnterprise_task():
    # 实例化一个调度器
    scheduler = BlockingScheduler()
    # 每年执行一次
    scheduler.add_job(BaseInfoEnterprise, 'cron', month=1, day=1, hour=0, minute=0)
    try:
        # 定时开始前执行一次
        BaseInfoEnterprise()
        scheduler.start()
    except Exception as e:
        print('定时采集异常', e)
        pass

#企业核心人员
def CorPerson():
    cnx, cursor = connectSql()
    gn_query = "select SocialCode from EnterpriseInfo where Place = '1'"
    cursor.execute(gn_query)
    gn_result = cursor.fetchall()
    cnx.commit()
    gn_social_list = [item[0] for item in gn_result]
    print('=======')
    for item in gn_social_list:
        r.rpush('CorPersonEnterprise:gnqy_socialCode', item)
    closeSql(cnx, cursor)

#企业核心人员定时任务：
def CorPerson_task():
    # 实例化一个调度器
    scheduler = BlockingScheduler()
    # 每个月执行一次
    scheduler.add_job(CorPerson, 'cron', day='1', hour=0, minute=0)
    try:
        scheduler.start()
    except Exception as e:
        print('定时采集异常', e)
        pass

#东方财富网财务数据
def FinanceFromEast():
    cnx_,cursor_ = cnn11()
    #从上市企业库中读取数据
    sql_sel = '''select social_credit_code from sys_base_enterprise_ipo where category = '1' '''
    cursor_.execute(sql_sel)
    finance = cursor_.fetchall()
    cnx_.commit()
    finance_list = [item[0] for item in finance]
    print('=======')
    for item in finance_list:
        r.rpush('FinanceFromEast:eastfinance_socialCode', item)
    close11(cnx_,cursor_)

#东方财富网财务数据定时任务
def FinanceFromEase_task():
    # 实例化一个调度器
    scheduler = BlockingScheduler()
    # 每个季度执行一次
    scheduler.add_job(FinanceFromEast, 'cron', month='1-12/3', day='1',hour=0, minute=0)
    try:
        scheduler.start()
    except Exception as e:
        print('定时采集异常', e)
        pass

#微信公众号
def WeiXingetFromSql():
    cnx_,cursor_=cnn11()
    selectSql = "SELECT info_source_code from info_source where site_uri like '%mp.weixin.qq.com%'"
    cursor_.execute(selectSql)
    results = cursor_.fetchall()
    cnx_.commit()
    result_list = [item[0] for item in results]
    #放入redis
    for item in result_list:
        r.rpush('WeiXinGZH:infoSourceCode', item)
    close11(cnx_,cursor_)


#微信公众号定时任务
def weixin_task():
    # 实例化一个调度器
    scheduler = BlockingScheduler()
    # 每天执行一次
    scheduler.add_job(WeiXingetFromSql, 'cron', hour=0,minute=0)
    try:
        # 定时开始前执行一次
        scheduler.start()
    except Exception as e:
        print('定时采集异常', e)
        pass

#企业年报证监会
def AnnualEnterprise():
    cnx,cursor = connectSql()
    # 获取国内企业
    gn_query = "select SocialCode from EnterpriseInfo where Place = '1' and SecuritiesCode is not null"
    cursor.execute(gn_query)
    gn_result = cursor.fetchall()
    cnx.commit()
    gn_social_list = [item[0] for item in gn_result]
    print('=======')
    for item in gn_social_list:
        r.rpush('AnnualEnterprise:gnqy_socialCode', item)
    closeSql(cnx,cursor)

#企业年报定时任务
def AnnualEnterprise_task():
    # 实例化一个调度器
    scheduler = BlockingScheduler()
    # 每年执行一次
    scheduler.add_job(AnnualEnterprise, 'cron', second='*/10')
    try:
        # 定时开始前执行一次
        AnnualEnterprise()
        scheduler.start()
    except Exception as e:
        print('定时采集异常', e)
        pass

# 企业年报——雪球网
def AnnualEnterpriseXueQ():
    cnx,cursor = connectSql()
    # 获取国内上市企业
    gn_query = "select SocialCode from EnterpriseInfo where Place = '1' and SecuritiesCode is not null and isIPO = 1 limit 10"
    cursor.execute(gn_query)
    gn_result = cursor.fetchall()
    cnx.commit()
    gn_social_list = [item[0] for item in gn_result]
    print('=======')
    for item in gn_social_list:
        r.rpush('AnnualEnterprise:gnshqy_socialCode', item)
    closeSql(cnx,cursor)
#雪球网年报定时任务
def AnnualEnterpriseXueQ_task():
    # 实例化一个调度器
    scheduler = BlockingScheduler()
    # 每年执行一次
    scheduler.add_job(AnnualEnterpriseXueQ, 'cron', month=1, day=1, hour=0, minute=0)
    try:
        # 定时开始前执行一次
        AnnualEnterpriseXueQ()
        scheduler.start()
    except Exception as e:
        print('定时采集异常', e)
        pass

#企业年报--美国证券交易委员会
def AnnualEnterpriseUS():
    cnx,cursor = connectSql()
    # 获取美股企业
    us_query = "select SocialCode from EnterpriseInfo where Place = '2' and SecuritiesType = '美股' and SecuritiesCode is not null and  CreateTime='2023-08-15 14:00:00'"
    # us_query = "select SocialCode from EnterpriseInfo where Place = '2' and SecuritiesType = '美股' and SecuritiesCode = 'BP' "
    #ZZSN22080900000025
    cursor.execute(us_query)
    us_result = cursor.fetchall()
    cnx.commit()
    us_social_list = [item[0] for item in us_result]
    print('=======')
    for item in us_social_list:
        r.rpush('AnnualEnterprise:usqy_socialCode', item)
    closeSql(cnx,cursor)

#国外企业基本信息 redis中放入id
def BaseInfoEnterpriseAbroad():
    cnx,cursor = connectSql()
    # 获取国外企业
    gn_query = "select id from EnterpriseInfo where Place = '2' "
    cursor.execute(gn_query)
    gn_result = cursor.fetchall()
    gn_social_list = [item[0] for item in gn_result]
    print('=======')
    for item in gn_social_list:
        r.rpush('BaseInfoEnterprise:gwqy_socialCode', item)
    closeSql(cnx,cursor)
#国外基本信息定时任务
def BaseInfoAbroad_task():
    # 实例化一个调度器
    scheduler = BlockingScheduler()
    # 每个月执行一次
    scheduler.add_job(BaseInfoEnterpriseAbroad, 'cron', day=1,hour=0, minute=0)
    try:
        # redisPushData  # 定时开始前执行一次
        BaseInfoEnterpriseAbroad()
        scheduler.start()
    except Exception as e:
        print('定时采集异常', e)
        pass


def SEC_CIK():
    cnx,cursor = connectSql()
    cik_query = "select cik from mgzqyjwyh_list where state=2"
    cursor.execute(cik_query)
    cik_result = cursor.fetchall()
    cnx.commit()
    cik_list = [item[0] for item in cik_result]
    print('=====')
    for item in cik_list:
        r.rpush('Sec_cik_US:uscik_baseinfo',item)
        r.rpush('Sec_cik_US:uscik_annualReport', item)
    closeSql(cnx,cursor)


#福布斯=====从数据库中读取信息放入redis
def FBS():
    cnx,cursor = connectSql()
    # todo:调整为获取福布斯的数据库
    gw_query = "select a.SocialCode from EnterpriseInfo a,EnterpriseType b where a.SocialCode=b.SocialCode and b.type=3 and a.Place=2"
    cursor.execute(gw_query)
    gw_result = cursor.fetchall()

    #获取国内企业
    # gn_query = "select a.SocialCode from EnterpriseInfo a,EnterpriseType b where a.SocialCode=b.SocialCode and b.type=3 and a.Place=1 "
    # cursor.execute(gn_query)
    # gn_result = cursor.fetchall()
    #
    # gn_social_list = [item[0] for item in gn_result]
    gw_social_list = [item[0] for item in gw_result]

    for item in gw_social_list:
        r.rpush('NewsEnterpriseFbs:gwqy_socialCode', item)
        # r.rpush('BaseInfoEnterpriseFbs:gwqy_social_code',item)
    # for item in gn_social_list:
    #     if not r.exists(item):
    #         # r.rpush('NewsEnterpriseFbs:gnqy_socialCode', item)
    #         # r.rpush('CorPersonEnterpriseFbs:gnqy_socialCode', item)
    #         r.rpush('AnnualEnterprise:gnshqy_socialCode',item)
    #         # r.rpush('BaseInfoEnterpriseFbs:gnqy_social_code',item)
    #         # r.rpush('FinanceFromEast:eastfinance_socialCode',item)
    closeSql(cnx,cursor)

#省属国有企业  盟市国有企业
def MengZhi():
    cnx, cursor = cnn11()
    gn_query = "select * from t_0906 a where not exists (select 1  from sys_base_enterprise_executive b where a.xydm =b.social_credit_code)"
    # gn_query = "select a.SocialCode from EnterpriseInfo a,EnterpriseType b where b.type=4 and a.SocialCode=b.SocialCode;"
    cursor.execute(gn_query)
    gn_result = cursor.fetchall()

    gn_social_list = [item[0] for item in gn_result]
    for item in gn_social_list:
        if not r.exists(item):
            # r.rpush('BaseInfoEnterpriseMz:gnqy_socialCode', item)
            r.rpush('CorPersonEnterprise:gnqy_socialCode', item)
    closeSql(cnx, cursor)


#将IPO的国外股票代码放到redis中
def yahooCodeFromSql():
    cnx_,cursor_=cnn11()
    try:
        gn_query = "select securities_code from sys_base_enterprise_ipo where category in ('4','5','6') "
        cursor_.execute(gn_query)
        gn_result = cursor_.fetchall()
        gn_social_list = [item[0] for item in gn_result]
        print('=======')
        for item in gn_social_list:
            r.rpush('FinanceFromEast:yahoo_securities_code', item)
    except Exception as e:
        log.info("数据查询异常")
    finally:
        close11(cnx_,cursor_)

#雅虎财务数据3天一次
def yahooCode_task():
    # 实例化一个调度器
    scheduler = BlockingScheduler()
    # 每天执行一次
    # scheduler.add_job(yahooCodeFromSql, 'cron', hour=0,minute=0)
    #3天执行一次
    scheduler.add_job(yahooCodeFromSql, 'interval', days=3)
    try:
        yahooCodeFromSql()  # 定时开始前执行一次
        scheduler.start()
    except Exception as e:
        print('定时采集异常', e)
        pass

#新三板
def NQEnterprise():
    cnx_, cursor_ = cnn11()

    nq_query = "SELECT social_credit_code FROM sys_base_enterprise_ipo WHERE securities_type='新三板' and listed='1'"
    cursor_.execute(nq_query)
    nq_result = cursor_.fetchall()
    cnx_.commit()
    nq_social_list = [item[0] for item in nq_result]

    for item in nq_social_list:
        # r.rpush('NQEnterprise:nq_Ipo', item)
        r.rpush('NQEnterprise:nq_finance',item)
        # r.rpush('NQEnterprise:nq_notice',item)
    closeSql(cnx_, cursor_)

def omeng():
    cnx, cursor = connectSql()

    # om_query = " SELECT A.SocialCode FROM EnterpriseInfo A , EnterpriseType B WHERE B.TYPE=6 AND A.SocialCode=B.SocialCode AND A.Place=1 order by A.id desc "
    om_query = "  SELECT A.SocialCode FROM EnterpriseInfo A , EnterpriseType B WHERE B.TYPE=6 AND A.SocialCode=B.SocialCode AND A.Place=2 "
    cursor.execute(om_query)
    om_result = cursor.fetchall()
    om_social_list = [item[0] for item in om_result]

    for item in om_social_list:
        #企业基本信息
        r.rpush('gwOMEnterprise_socialcode:BaseInfo', item)
        #企业高管信息
        # r.rpush('gnOMEnterprise_socialcode:TYCid', item)
        # r.rpush('gnOMEnterprise_socialcode:CenterPerson', item)
        #企业动态
        r.rpush('gwOMEnterprise_socialcode:News', item)
        #企业年报
        # r.rpush('gnOMEnterprise_socialcode:Report', item)
        #企业公告
        # r.rpush('gnOMEnterprise_socialcode:Notice', item)
    closeSql(cnx, cursor)


if __name__ == "__main__":
    start = time.time()
    # NoticeEnterprise()
    # AnnualEnterpriseIPO()
    # AnnualEnterprise()
    # BaseInfoEnterpriseAbroad()
    # NewsEnterprise_task()
    # NewsEnterprise()
    # BaseInfoEnterprise()
    # FBS()
    # MengZhi()
    # NQEnterprise()
    SEC_CIK()
    # omeng()
    # AnnualEnterpriseUS()
    # NoticeEnterprise_task()
    # AnnualEnterprise_task()
    # NoticeEnterprise()
    # FinanceFromEast()
    log.info(f'====={basecore.getNowTime(1)}=====添加数据成功======耗时：{basecore.getTimeCost(start,time.time())}===')

