

import pymysql
import requests
import json
import time
import datetime
import urllib3
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
from BaseCore import BaseCore

baseCore=BaseCore()
logger=baseCore.getLogger()


def reqmsg(offset,operand):
    header={
        'Connection':'keep-alive',
        'Content-Length':'268',
        'sec-ch-ua':'"Google Chrome";v="117", "Not;A=Brand";v="8", "Chromium";v="117"',
        'sec-ch-ua-platform':'"Windows"',
        'sec-ch-ua-mobile':'?0',
        'User-Agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36',
        'Content-Type':'application/json',
        'Accept':'*/*',
        'Origin':'https://finance.yahoo.com',
        'Sec-Fetch-Site':'same-site',
        'Sec-Fetch-Mode':'cors',
        'Sec-Fetch-Dest':'empty',
        'Referer':'https://finance.yahoo.com/screener/unsaved/ff7f1d36-5088-4986-b7ed-4c7ee4f7af57?count=100&offset=0',
        'Accept-Encoding':'gzip, deflate, br',
        'Accept-Language':'zh-CN,zh;q=0.9',
        'Cookie':'tbla_id=24af1dd7-fb87-4d22-a0cf-7c313202694c-tuct86184e4; gpp=DBAA; gpp_sid=-1; OTH=v=2&s=2&d=eyJraWQiOiIwMTY0MGY5MDNhMjRlMWMxZjA5N2ViZGEyZDA5YjE5NmM5ZGUzZWQ5IiwiYWxnIjoiUlMyNTYifQ.eyJjdSI6eyJndWlkIjoiMkZJTDdIQjVZVFcyN1NLWVFIQjNOTFU0RDQiLCJwZXJzaXN0ZW50Ijp0cnVlLCJzaWQiOiJZbXE5VW82MkRsd0QifX0.sVtMHG-HjplldMiy1GXA1thZlnwJusoLr5vAjuHOGIppAWCgZgTz4HuUAB4weZAfICtLge3MZbDnfnIDuHSm620aQ-8lc9RbpQ0_YtWbn50lbi13EgxHuDs7IDvozIqZ7Wji4DldEHGMezxWOqwzG6HeiWdu51gngtC0wYXtKGM; T=af=JnRzPTE2OTY3Mjc4NDAmcHM9THVnWDRYeE11MUNhSXkxcHFOb0pjUS0t&d=bnMBeWFob28BZwEyRklMN0hCNVlUVzI3U0tZUUhCM05MVTRENAFhYwFBRWVLWnVsbQFhbAF3ZWlnYW5nbGl1MTFAZ21haWwuY29tAXNjAW1icl9yZWdpc3RyYXRpb24BZnMBeGI5ODZlUmxJZ01nAXp6AWdNZ0lsQkE3RQFhAVFBRQFsYXQBZ01nSWxCAW51ATA-&kt=EAApyOTr6JKep3_MVia32x9bA--~I&ku=FAAfpFFRxC0lNQCwURloTuGtPI.ZMaMkip9vcBGgEYFfK9jscSEGovt9tf6JPudIpJ1LGwPF8XPDrQGyLdzpK0WeyodXshfKU_VWmF7zaHgEKwVTP6eyxJagSsjv_f.k4KH4UemJUDrEv6AlrYlxgrVtqn8oRdc0E6dse7_A.dyKxk-~E; F=d=GTRbFBs9vIsFBPqpFYbEBjWkFoKZ3VYPcOZjc86puK_qeukJy9prU1z2; PH=l=en-US; Y=v=1&n=4e4ri71j7l6on&l=lj3pdsegobxk43l22ihvwfahc77wvm4u4aao9056/o&p=n2svvhk00000000&r=1ce&intl=us; axids=gam=y-dy_7BtBG2uKuxS_spt7cbrFy7QoSkfWRrfb.CnwY_FJGvFqjAA---A&dv360=eS1Sci5ZRTQ5RTJ1RXBvZkRrM1JMM1pzM1Z0LlBqSkhYdEZnRUY1cmVqRVZWdGo4Z3hjM0NiMnVnTE5YVGFibjYzcFROS35B; gam_id=y-dy_7BtBG2uKuxS_spt7cbrFy7QoSkfWRrfb.CnwY_FJGvFqjAA---A; GUC=AQEACAJlI0llUkIdBwRd&s=AQAAAIsCEds8&g=ZSIDMg; A1=d=AQABBFnbm2QCEJD73Orb0UPzH5ts62DMAwYFEgEACAJJI2VSZdyia3sB_eMBAAcIKP9nYQIMhisIDy8f80k8wbm5XkoLgG-PKwkBBwoBCg&S=AQAAAo9aGFBw2wIEVJoc_Tspjbw; A3=d=AQABBFnbm2QCEJD73Orb0UPzH5ts62DMAwYFEgEACAJJI2VSZdyia3sB_eMBAAcIKP9nYQIMhisIDy8f80k8wbm5XkoLgG-PKwkBBwoBCg&S=AQAAAo9aGFBw2wIEVJoc_Tspjbw; cmp=t=1696898974&j=0&u=1YNN; PRF=t%3DLMT%252BAAPL%252BAAPL.BA%252BTM.BA%252B%255EIXIC%252BISP%252B032830.KS%252BABG.JO%252BWCC%252BAHT.L%252BCPB%252BVMUK.L%252BRAJESHEXPO.NS%252B8128.HK%252B5019.T%26newChartbetateaser%3D1; __gpi=UID=00000c5bc07ce289:T=1696900616:RT=1696900616:S=ALNI_MZVy68LYVM9slK8cg6vB3OeE3-uvw; A1S=d=AQABBFnbm2QCEJD73Orb0UPzH5ts62DMAwYFEgEACAJJI2VSZdyia3sB_eMBAAcIKP9nYQIMhisIDy8f80k8wbm5XkoLgG-PKwkBBwoBCg&S=AQAAAo9aGFBw2wIEVJoc_Tspjbw',

    }
    data={
        "sortType": "DESC",
        "sortField": "intradaymarketcap",
        "quoteType": "EQUITY",
        "offset": offset,
        "query": {
            "operator": "and",
            "operands": [
                {
                    "operator": "or",
                    "operands": [operand]
                }
            ]
        },
        "size": 100,
        "userId": "2FIL7HB5YTW27SKYQHB3NLU4D4",
        "userIdType": "guid"
    }
    proxy = {'https': 'http://127.0.0.1:1080', 'http': 'http://127.0.0.1:1080'}
    url='https://query2.finance.yahoo.com/v1/finance/screener?crumb=sZDWS3KsXAl&lang=en-US&region=US&formatted=true&corsDomain=finance.yahoo.com'
    for i in range(0,3):
        try:
            response=requests.post(url=url,data=json.dumps(data),headers=header,verify=False,timeout=20,proxies=proxy)
            stockmsg=response.json()
        except Exception as e:
            stockmsg=''
            logger.info(f"第offset={offset}页请求失败{e}")
        if stockmsg:
            logger.info(f"第offset={offset}页请求成功")
            break
    return stockmsg

def listPage():
    operands=[
        {
            "operator": "EQ",
            "operands": [
                "region",
                "ar"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "au"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "ch"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "cn"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "de"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "ee"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "at"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "ca"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "be"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "cl"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "cz"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "dk"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "eg"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "fi"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "br"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "es"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "fr"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "gb"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "hk"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "id"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "gr"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "hu"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "il"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "ie"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "it"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "in"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "kr"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "is"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "jp"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "kw"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "lt"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "lk"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "lv"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "mx"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "nl"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "nz"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "my"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "no"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "ph"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "pe"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "vn"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "us"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "tr"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "sr"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "za"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "ve"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "th"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "sg"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "tw"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "sa"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "qa"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "pl"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "se"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "ru"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "pt"
            ]
        },
        {
            "operator": "EQ",
            "operands": [
                "region",
                "pk"
            ]
        }
    ]
    for operand in operands:
        logger.info(f'采集地域股票信息{operand}')
        #第一次请求获取地域总共有的股票代码数量
        try:
            stockmsg=reqmsg(0,operand)
            total=stockmsg['finance']['result'][0]['total']
        except Exception as e:
            logger.info(f'region该地域没有股票信息{operand}')
            continue
        for i in range(0,total,100):
            logger.info(f"offset的值{i}")
            stockmsg=reqmsg(i,operand)
            if stockmsg:
                try:
                    getStock(stockmsg)
                except Exception as e:
                    logger.info(f"解析失败{e}")
                    time.sleep(3)


def getStock(stockmsg):
    quotes=stockmsg['finance']['result'][0]['quotes']
    for quote in quotes:
        symbol=quote['symbol']
        try:
            longName=quote['longName']
        except:
            longName=''
        try:
            exchange=quote['exchange']
        except:
            exchange=''
        try:
            fullExchangeName=quote['fullExchangeName']
        except:
            fullExchangeName=''
        try:
            financialCurrency=quote['financialCurrency']
        except:
            financialCurrency=''
        try:
            market=quote['market']
        except:
            market=''
        try:
            shortName=quote['shortName']
        except:
            shortName=''
        quotmsg={
            'symbol':symbol,
            'longName':longName,
            'exchange':exchange,
            'fullExchangeName':fullExchangeName,
            'financialCurrency':financialCurrency,
            'market':market,
            'shortName':shortName
        }
        dataToSql(quotmsg)

def conn144():
    conn = pymysql.Connect(host='114.115.159.144', port=3306, user='caiji', passwd='zzsn9988', db='caiji',
                           charset='utf8')
    cursor = conn.cursor()
    return conn,cursor


def dataToSql(quotmsg):
    conn,cursor=conn144()
    try:
        symbol=quotmsg['symbol']
        longName=quotmsg['longName']
        exchange=quotmsg['exchange']
        fullExchangeName=quotmsg['fullExchangeName']
        financialCurrency=quotmsg['financialCurrency']
        market=quotmsg['market']
        shortName=quotmsg['shortName']
        # 检查记录是否存在
        select_sql=f"SELECT * FROM yahoostock WHERE symbol='{symbol}'"
        cursor.execute(select_sql)
        existing_record = cursor.fetchone()
        # 获取当前时间
        current_time = datetime.datetime.now()
        # 将时间转换为字符串
        currentdate = current_time.strftime("%Y-%m-%d %H:%M:%S")
        if existing_record:
            # 记录已存在，执行更新操作
            # update_param=(symbol,longName,exchange,fullExchangeName,financialCurrency,market,shortName,currentdate)
            # update_sql=f"UPDATE yahoostock SET social_credit_code=%s , content=%s , level_relation=%s,origin_type=%s ,create_time=%s WHERE stock_code=%s "
            # cursor.execute(update_sql,update_param)
            logger.info(f"股票代码已采集入库过{symbol}")
        else:
            insert_param=(symbol,longName,exchange,fullExchangeName,financialCurrency,market,shortName,currentdate)
            insert_sql=f"INSERT INTO yahoostock (symbol, longName,exchange,fullExchangeName,financialCurrency,market,shortName,currentdate) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s)"
            # 记录不存在，执行插入操作
            cursor.execute(insert_sql,insert_param)
            logger.info(f"{symbol}股票入库添加成功")

        # 提交事务
        conn.commit()
    except Exception as e:
        return False
    return True



if __name__ == '__main__':
    logger.info(f"采集开始")
    try:
        listPage()
    except Exception as e:
        logger.info(f"程序异常退出{e}")
    logger.info(f"采集结束")




