# -*- coding: utf-8 -*-
"""
Created on 20180309

@author: zhoushuai
"""

import pandas as pd
import pyodbc
import ky
import datetime

sdk = ky.Api("http://data-api.kuaiyutech.com/api.rpc")

import logging
logger = logging.getLogger(__name__)
try:
    from ..BT.stock import Stock
    from ..BT.index import Index 
    logger.setLevel(logging.CRITICAL)
    from ..util.kySDK import *
    from .apiParent import ApiParent
except:
    import sys   
    #D:\Project\益盟\回测框架\KYBT\BT
    sys.path.insert(0, 'D:/Project/YM/BackTesting Frame/KYBT/BT') 
    sys.path.insert(0, 'D:/Project/YM/BackTesting Frame/KYBT/util')
    print(sys.path)
    from stock import Stock 
    from index import Index
    from kySDK import *
    from apiParent import ApiParent
    logger.setLevel(logging.WARNING)



class API(ApiParent):

    '''
    get data from SQL and organize it 
    '''
    def __init__(self): 
        super().__init__()

    @staticmethod
    def pdToStockObjectList(panda):
        stockList = []
        for index,row in panda.iterrows():
            oneStock = Stock()
            oneStock.SecuCode = row['SecuCode']
            oneStock.SecuAbbr = row['SecuAbbr']
            oneStock.SecuMarket = row['SecuMarket']
            oneStock.TradeDate = row['TradeDate']
            oneStock.TradeStatus = row['TradeStatus']
            oneStock.PreClosePrice = float(row['PreClosePrice'])
            oneStock.OpenPrice = float(row['OpenPrice'])
            oneStock.ClosePrice = float(row['ClosePrice'])
            oneStock.HighPrice = float(row['HighPrice'])
            oneStock.LowPrice = float(row['LowPrice'])
            oneStock.TurnoverVolume = float(row['TurnoverVolume'])
            oneStock.TurnoverValue = float(row['TurnoverValue'])
            oneStock.TurnoverDeals = float(row['TurnoverDeals'])
            oneStock.Amplitude = float(row['Amplitude'])
            oneStock.ChangeRatio = float(row['ChangeRatio'])
            oneStock.FlowShares = float(row['FlowShares'])
            oneStock.FlowMarketValue = float(row['FlowMarketValue'])
            oneStock.TotalEquity = float(row['TotalEquity'])
            oneStock.IssueEquity = float(row['IssueEquity'])
            oneStock.TotalValue = float(row['TotalValue'])
            oneStock.RecordID = row['RecordID']
            oneStock.LLimitUpTime = str(row['LLimitUpTime'])
            oneStock.RiseOrDown = float(row['RiseOrDown'])
            stockList.append(oneStock)
        return stockList

    @staticmethod
    def pdToIndexObjectList(panda):
        indexList = []
        for index,row in panda.iterrows():
            indexOneDay = Index()
            indexOneDay.SecuCode = row['SecuCode']
            indexOneDay.SecuAbbr = row['SecuAbbr']
            indexOneDay.TradeDate = row['TradeDate']
            indexOneDay.PreClosePrice = float(row['PreClosePrice'])
            indexOneDay.OpenPrice = float(row['OpenPrice'])
            indexOneDay.ClosePrice = float(row['ClosePrice'])
            indexOneDay.HighPrice = float(row['HighPrice'])
            indexOneDay.LowPrice = float(row['LowPrice'])
            indexOneDay.TurnoverVolume = float(row['TurnoverVolume'])
            indexOneDay.TurnoverValue = float(row['TurnoverValue'])
            indexOneDay.ChangeRatio = float(row['ChangeRatio'])
            indexList.append(indexOneDay)
        return indexList
    
    def getMarketValueOneStock(self,startDate,endDate,stockID):
        '''
        get market value of one stock
        '''
        itemString = '''SecuCode,
        SecuAbbr,SecuMarket,TradeDate,TradeStatus,PreClosePrice,
        OpenPrice,ClosePrice,HighPrice,LowPrice,TurnoverVolume,TurnoverValue,TurnoverDeals,Amplitude,ChangeRatio,
        FlowShares,FlowMarketValue,TotalEquity,IssueEquity,TotalValue,RecordID,LLimitUpTime,RiseOrDown'''

        query = '''
                SELECT
                    %s
                FROM
                    [dbo].[EM_QUOTE_STOCK_DAILY]
                WHERE
                    SecuCode = '%s'
                AND TradeDate >= '%s 00:00:00.000'
                AND TradeDate <= '%s 00:00:00.000'
                '''%(itemString,stockID,startDate,endDate)
        
        self.cursor.execute(query)

        raw = self.cursor.fetchall()
        logger.info('raw :',raw)
        if not raw:
            logger.critical( 'Daily Quote has no data, return None')
            return None
        
        ReportsPD = self.pandaData(raw,itemString)
        
        ReportsPD.fillna(value=0, inplace=True) # to replace None with value 0

        return self.pdToStockObjectList(ReportsPD)

    def getAllStockCode(self,oneDay):
        '''
        return ['600000','000002',...]
        '''
        itemString = 'SecuCode'
        query = '''
        SELECT DISTINCT %s
        
        FROM
            [dbo].[EM_QUOTE_STOCK_DAILY]
        WHERE
        TradeDate = '%s 00:00:00.000'
        AND 
        left(SecuCode,1) != 9 
        AND
        left(SecuCode,1) != 2
        '''%(itemString,oneDay)

        self.cursor.execute(query)

        raw = self.cursor.fetchall()
        logger.info('raw :',raw)
        if not raw:
            logger.critical( 'Daily Quote all has no data, return None')
            return None
        
        allStockPD = self.pandaData(raw,'SecuCode')
        allStockPD.fillna(value=0, inplace=True) # to replace None with value 0
        #print('hahahahaha',allStockPD[itemString].tolist())
        return allStockPD[itemString].tolist()

    def getIndexMarketValue(self,startDate,endDate,index):
        '''
        get market value of one specific index
        '''

        if index == 'HS300':
            index = '000300'
        elif index == 'ZZ500':
            index = '000905'
        elif index == 'SZ50':
            index = '000016'
        else:
            raise ValueError('Index is incorrect')
        
        itemString = 'SecuCode,SecuAbbr,TradeDate,TradeStatus,PreClosePrice,OpenPrice,ClosePrice,HighPrice,LowPrice,TurnoverVolume,TurnoverValue,ChangeRatio'
        
        query = '''
        SELECT
            %s
        FROM
            [dbo].[EM_QUOTE_INDEX_DAILY]
        WHERE
                SecuCode = '%s'
        AND
                TradeDate >= '%s 00:00:00.000'
        AND     TradeDate <= '%s 00:00:00.000'
        ORDER BY TradeDate DESC
        '''%(itemString,index,startDate,endDate)

        #print(query)
        self.cursor.execute(query)

        raw = self.cursor.fetchall()
        logger.info('raw :',raw)
        if not raw:
            logger.critical( 'index market value has no data, return None')
            return None
        
        indexPD = self.pandaData(raw,itemString)
        indexPD.fillna(value=0, inplace=True) # to replace None with value 0
        return self.pdToIndexObjectList(indexPD)

    def getMarketValueOneDay(self,oneDay,index):
        '''
        get stocks' marketValue of 'all'|'HS300'|'SZ500'|'SZ50'|['600000','000002',...]
        index can be: 'all'|'HS300'|'SZ500'|'SZ50'|['600000','000002',...]
        '''
        itemString = '''SecuCode,
        SecuAbbr,SecuMarket,TradeDate,TradeStatus,PreClosePrice,
        OpenPrice,ClosePrice,HighPrice,LowPrice,TurnoverVolume,TurnoverValue,TurnoverDeals,Amplitude,ChangeRatio,
        FlowShares,FlowMarketValue,TotalEquity,IssueEquity,TotalValue,RecordID,LLimitUpTime,RiseOrDown'''
        if  (isinstance(index,str) and index == 'all') or None:
            #print('@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@')
            query = '''
            SELECT %s
            FROM
                [dbo].[EM_QUOTE_STOCK_DAILY]
            WHERE
            TradeDate = '%s 00:00:00.000'
            '''%(itemString,oneDay)

            self.cursor.execute(query)

            raw = self.cursor.fetchall()
            logger.info('raw :',raw)
            if not raw:
                logger.critical( 'Daily Quote all has no data, return None, %s'%oneDay)
                return None
            
            allStockPD = self.pandaData(raw,itemString)
            allStockPD.fillna(value=0, inplace=True) # to replace None with value 0
            return self.pdToStockObjectList(allStockPD)

        elif index in ['HS300','ZZ500','SZ50']:
            #print('@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@')
            # specify index
            if index == 'HS300':
                index = 'SHSE.000300'
            elif index == 'ZZ500':
                index = 'SHSE.000905'
            elif index == 'SZ50':
                index = 'SHSE.000016'
            #getStock
            try:
                stockPool = tuple(stockCode[-6:] for stockCode in getIndexPoolFromKy(index)) #['SHSE.600000','SHSE.000002'] tuple has no sequences
            except:
                raise

            query = '''
            SELECT %s
            FROM
                [dbo].[EM_QUOTE_STOCK_DAILY]
            WHERE
            TradeDate = '%s 00:00:00.000'
            AND
            SecuCode IN %s
            '''%(itemString,oneDay,stockPool)
            #print('query: %s',query)
            self.cursor.execute(query)

            raw = self.cursor.fetchall()
            logger.info('raw :',raw)
            if not raw:
                logger.critical( 'Daily Quote of specified index has no data, return None, %s'%oneDay)
                return None
            
            indexStockPD = self.pandaData(raw,itemString)
            indexStockPD.fillna(value=0, inplace=True) # to replace None with value 0
            return self.pdToStockObjectList(indexStockPD)

        elif isinstance(index,list):
            #getStock
            #print('@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@',index)
            try:
                stockPool = tuple(stockCode[-6:] for stockCode in index)  # no sequence
            except:
                raise
            if len(index) > 1:
                stockPool = tuple(stockCode[-6:] for stockCode in index)  # no sequence
            elif len(index) == 1:
                stockPool = '(%s)'%index[0]

            query = '''
            SELECT %s
            FROM
                [dbo].[EM_QUOTE_STOCK_DAILY]
            WHERE
            TradeDate = '%s 00:00:00.000'
            AND
            SecuCode IN %s
            '''%(itemString,oneDay,stockPool)
            #print(query)
            self.cursor.execute(query)
            raw = self.cursor.fetchall()
            logger.info('raw :',raw)
            if not raw:
                logger.critical( 'Daily Quote of specified stocks has no data, return None, %s'%oneDay)
                return None
            
            indexStockPD = self.pandaData(raw,itemString)
            indexStockPD.fillna(value=0, inplace=True) # to replace None with value 0
            return self.pdToStockObjectList(indexStockPD)
    

if __name__ == '__main__':
    logger.warn(__file__)
    myAPI = API('BTMarketTrade')
    myAPI.init()

    stockList = ['600000','000002']
    '''
    for stock in stockList:
        logger.critical(stock)
        #logger.warn(myAPI.getMarketValueOneStock('2015-05-05','2016-05-05',stock))
        
    logger.warn(len(myAPI.getMarketValueOneDay('2015-05-05','SZ50')))
    myStock = myAPI.getMarketValueOneDay('2015-05-05','SZ50')

    for x in myStock:
        x.printMe() 
    
    myStock = myAPI.getMarketValueOneDay('2015-05-05',stockList)
    for x in myStock:
        x.printMe() 
    '''

    #x = myAPI.getAllStockCode()
    #print(x)
    start = '2017-05-01'
    end = '2017-06-15'
    x = myAPI.getTradingDays(start,end)
    print(x)


