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

@author: zhoushuai
"""
'''
created by: matt  2018-03-30

EMMIDD2.dbo.CY_FinacialRatio_RP

SecuCode	证券代码		varchar(12)			FALSE	
SecuAbbr	证券名称		nvarchar(40)			TRUE	
EndDate	报告日期		datetime			FALSE	
PublDate	公告日期		datetime			TRUE	
InventoryTurnover	存货周转率		numeric(18,4)	次		TRUE	
AccountsReceivableTurnover	应收账款周转率		numeric(18,4)	次		TRUE	
FixedAssetsTurnover	固定资产周转率		numeric(18,4)	次		TRUE	
OperatingRevenueYoY	主营收入同比		numeric(18,4)			TRUE	
DeductNetProfitYoY	归属于母公司股东的扣除非经常性损益后的净利润同比		numeric(18,4)			TRUE	

'''
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.financialRatioRP import FinancialRatioRP
    logger.setLevel(logging.CRITICAL)
    from ..util.kySDK import *
    from .apiParent import ApiParent
except:
    import sys   
    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 financialRatioRP import FinancialRatioRP
    from kySDK import *
    from apiParent import ApiParent
    logger.setLevel(logging.WARNING)


class API(ApiParent):

    def __init__(self,DSN): 
        super().__init__(DSN)
    
    @staticmethod
    def pdToValueObjectList(panda):
        FinancialRatioRPList = []
        for index,row in panda.iterrows():
            oneFinancialRatioRP = FinancialRatioRP()
            oneFinancialRatioRP.SecuCode = row['SecuCode']         	
            oneFinancialRatioRP.SecuAbbr = row['SecuAbbr']
            oneFinancialRatioRP.EndDate = row['EndDate']
            oneFinancialRatioRP.PublDate = row['PublDate']
            oneFinancialRatioRP.InventoryTurnover = row['InventoryTurnover']
            oneFinancialRatioRP.AccountsReceivableTurnover = row['AccountsReceivableTurnover']
            oneFinancialRatioRP.FixedAssetsTurnover = row['FixedAssetsTurnover']
            oneFinancialRatioRP.OperatingRevenueYoY = row['OperatingRevenueYoY']
            oneFinancialRatioRP.DeductNetProfitYoY = row['DeductNetProfitYoY']
            FinancialRatioRPList.append(oneFinancialRatioRP)
        return FinancialRatioRPList

    def removeDuplicated(self,myPD):
        # find two or more identical EndDate, delete one random row
        # allStockPD.drop_duplicates(subset = date,keep ='last',inplace = True) # just that easy 
        seen = set()
        uniqObj = []

        for x,y in zip(self.pdToValueObjectList(myPD),[j.EndDate for j in self.pdToValueObjectList(myPD)]):
            if y not in seen:
                #print(seen)
                uniqObj.append(x)
                seen.add(y)
        return uniqObj

    def getRaw(self,endDate,stockList):
        '''
        获取从endDate开始往前推近四年的所有data，每年4个

        如果有两个2013-06-30，去Null少的那个

        CY_ValueDaily_Quote is daily data
        stock: list, ## [600000,000002]
        startDate: str, '2018-01-10'
        endDate: str,'2018-03-03' 
        '''
        #    
        itemString = '''
                SecuCode,
                SecuAbbr,
                EndDate,
                PublDate,
                InventoryTurnover,
                AccountsReceivableTurnover,
                FixedAssetsTurnover,
                OperatingRevenueYoY,
                DeductNetProfitYoY
                '''
        #
        if len(stockList) == 1:
            stockList = '(%s)'%stockList[0]
        else:
            stockList = tuple(stockList)
        #
        startDate = self.getNdayBefore(endDate,365*4)
        #
        query = '''
            SELECT
                %s
            FROM
                [dbo].[CY_FinacialRatio_RP]
            where 
            EndDate <= '%s 00:00:00:000'
            AND 
            EndDate >= '%s 00:00:00:000'
            AND
            isValid = '1'
            AND SecuCode IN %s
            ORDER BY
                SecuAbbr DESC,
                EndDate DESC
        '''%(itemString,endDate,startDate,stockList)
        #
        #print(query)
        self.cursor.execute(query)

        raw = self.cursor.fetchall()
        #print('raw :',raw[0],type(raw[0]))

        if not raw:
            logger.critical( 'CY_FinacialRatio_RP value has no data, return None')
            return None
        
        financialRatioPD = self.pandaData(raw,itemString)
        financialRatioPD.fillna(value=0, inplace=True) # to replace None with value 0


        # take care of multiple stocks in one single big pandas sheet
        # get rid of duplicated rows in pandas, replace with removed myPD
        result = self.pdToDict(financialRatioPD) 
        for SecuCode,myPD in result.items():
            result[SecuCode] = self.removeDuplicated(myPD)   
        # {'600000':[obj,obj],'000002':[obj,obj]}   每个SecoCode里，最新的obj在最前面      
        return result
    
    def validateStockList(self,stockList,spotDate,criteria,period,upperbound,lowerbound,):
        mydict = dict()
        dictPD = self.getRaw(spotDate,stockList)
        for SecuCode,objList in dictPD.items():
            mydict[SecuCode] = self.validateOneStock(SecuCode,spotDate,objList,criteria,period,upperbound,lowerbound)
        #print('mydict',mydict)
        return mydict

    def validateOneStock(self,oneStock,spotDate,objList,criteria,period,upperbound,lowerbound,):
        '''
        CY_ValueDaily_Quote is daily data
        stockList: list, ## [600000,000002]
        spotDate: str, '2015-03-01'|'now'
        period: str, '最近一期|最近一年|最近三年|'
        

        EndDate                 PublDate
        2017-09-30 00:00:00.000	2017-10-28 00:00:00.000
        2017-06-30 00:00:00.000	2017-08-30 00:00:00.000
        2017-03-31 00:00:00.000	2017-04-27 00:00:00.000
        2016-12-31 00:00:00.000	2017-04-01 00:00:00.000
        2016-09-30 00:00:00.000	2017-10-28 00:00:00.000
        2016-06-30 00:00:00.000	2017-08-30 00:00:00.000
        2016-03-31 00:00:00.000	2017-04-27 00:00:00.000
        2015-12-31 00:00:00.000	2017-04-01 00:00:00.000
        2015-09-30 00:00:00.000	2016-10-29 00:00:00.000
        2015-06-30 00:00:00.000	2016-08-11 00:00:00.000
        2015-03-31 00:00:00.000	2016-04-30 00:00:00.000
        2014-12-31 00:00:00.000	2016-04-07 00:00:00.000
        2014-09-30 00:00:00.000	2015-10-30 00:00:00.000
        2014-06-30 00:00:00.000	2015-08-20 00:00:00.000

        '''        
        dateList = [str(x.EndDate) for x in objList]
        spotYear = spotDate[:4]
        #print(spotYear)

        # 确定选股的基准时间点，现在为时间基点 or 以前某时间为基点 
        if spotDate == 'now':
            spotDate = getYesterday() # 选昨日不会错，选今日可能错
        #
        if period == '最近一期': #找到最近的一个PublDate, 先用标准日期代替PublDate
            objWanted = [objList[0]]

        elif period == '最近一年':
            if spotDate + '-12-31' in dateList[0]:
                # 已发布
                objWanted = [dateList[0]]
            else:
                # 未发布
                objWanted = self.findObj('EndDate',[str(eval(spotYear)-1) + '-12-31', str(eval(spotYear)-2) + '-12-31'],objList)[:1] # 只要前一个
            
        elif period == '最近三年':
            if '12-31' in dateList[0]:
                # 已发布
                recentThreeYears = [spotYear,str(eval(spotYear)-1),str(eval(spotYear)-2)]
                recentThreeDates = [x + '-12-31' for x in recentThreeYears]
                objWanted = self.findObj('EndDate',recentThreeDates,objList)[:3] # 只要前三个
            else:
                # 未发布
                recentThreeYears = [str(eval(spotYear)-1),str(eval(spotYear)-2),str(eval(spotYear)-3),str(eval(spotYear)-4)]
                recentThreeDates = [x + '-12-31' for x in recentThreeYears]
                objWanted = self.findObj('EndDate',recentThreeDates,objList)[:3] # 只要前三个
        
        #print('objWanted',objWanted)
        #for x in objWanted:
        #    print(x.DeductNetProfitYoY)
        #    print(x.printMe())
        #print('objWanted: ',objWanted)
        tell = self.isItTrue(objWanted,upperbound,lowerbound,criteria)
        #print('tell',tell)
        return tell

    def isItTrue(self,objWanted,upperbound,lowerbound,criteria):
        '''
        criteria: list,['营业收入增长率','归母净利润增长率','应收账款周转率','存货周转率','固定资产周转率'] 
        '''
        if upperbound == None:
            upperbound = 1000000
        
        if lowerbound == None:
            lowerbound = -1000000

        # 全真则为真，否则为假
        result = False if False in [True if (eval('x.%s'%criteria) > lowerbound and eval('x.%s'%criteria) < upperbound) else False for x in objWanted] else True
        #print('True or False result: ',result)
        return result

        
    def test(self):
        query = 'SELECT TOP 10  * FROM embase2.[dbo].[CY_FinacialIndicators_RP]'
        self.cursor.execute(query)
        #print(self.cursor.fetchall())

if __name__ == '__main__':
    logger.warn(__file__)
    import time
    myAPI = API('BTEMmidd2')
    myAPI.init()
    #myAPI.test()
    #y = myAPI.getRaw('2017-06-06',['000002','600000'])
    stockList = ['600000','000002','600485','300357','600000','000002','600485','300357','600000','000002','600485','300357']
    myAPI.validateStockList(stockList,'2015-11-29','DeductNetProfitYoY','最近三年',None,5,)
    #print(y[0].printMe())
    '''
    #找出最近一期所有符合条件的股票
    start = time.time()
    for a in range(len(stockList)):
        myAPI.getRaw('2017-06-06',['000002'])
    end = time.time()
    print('it takes %f to run one for %d times'%(end-start,len(stockList)))

    start = time.time()
    myAPI.getRaw('2017-06-06',stockList)
    end = time.time()
    print('it takes %f to run stockList for one time'%(end-start))
    '''