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

@author: zhoushuai
"""
'''
列名	中文名	别名	类型	单位	默认值	是否可空	说明
SecuCode	证券代码		varchar(12)			FALSE	
SecuAbbr	证券名称		nvarchar(40)			TRUE	
EndDate	报告日期		datetime			FALSE	
TotalAssets	总资产		numeric(18,4)	元		TRUE	
AdvanceReceipts	预收款项		numeric(18,4)			TRUE	
EquityBelongedToPC	净资产		numeric(18,4)			TRUE	
PublDate	公告日期		datetime			TRUE	
TotalLiability	总负债		numeric(18,4)	元		TRUE	
TotalCurrentLiability	流动负债		numeric(18,4)	元		TRUE	
TotalCurrentAssets	流动资产		numeric(18,4)	元		TRUE	
CashOrDepositInCentralBank	现金及存放中央银行款项		numeric(18,4)	元		TRUE	
NetProfit	净利润		numeric(18,4)	元		TRUE	
NetOperateCashFlow	经营活动产生的现金流		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.financialIndicator import FinancialIndicator
    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 financialIndicator import FinancialIndicator
    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):
        financialIndicatorList = []
        for index,row in panda.iterrows():
            oneFinancialIndicator = FinancialIndicator()
            oneFinancialIndicator.SecuCode = row['SecuCode']         	
            oneFinancialIndicator.SecuAbbr = row['SecuAbbr']
            oneFinancialIndicator.EndDate = row['EndDate']
            oneFinancialIndicator.AdvanceReceipts = float(row['AdvanceReceipts'])
            oneFinancialIndicator.TotalAssets = float(row['TotalAssets'])
            oneFinancialIndicator.EquityBelongedToPC = float(row['EquityBelongedToPC'])
            oneFinancialIndicator.TotalLiability = float(row['TotalLiability'])
            oneFinancialIndicator.TotalCurrentAssets = float(row['TotalCurrentAssets'])
            oneFinancialIndicator.TotalCurrentLiability = float(row['TotalCurrentLiability'])
            oneFinancialIndicator.CashOrDepositInCentralBank = float(row['CashOrDepositInCentralBank'])
            oneFinancialIndicator.NetProfit = float(row['NetProfit'])
            oneFinancialIndicator.NetOperateCashFlow = float(row['NetOperateCashFlow'])

            financialIndicatorList.append(oneFinancialIndicator)
        return financialIndicatorList

    def getFinancialIndicator(self,startDate,endDate,stockList):
        '''
        CY_ValueDaily_Quote is daily data
        stock: list, ## [600000,000002]
        startDate: str, '2018-01-10'
        endDate: str,'2018-03-03' 
        '''
        #    
        itemString = '''
                SecuCode,
                SecuAbbr,
                EndDate,
                AdvanceReceipts,
                TotalAssets,
                EquityBelongedToPC,
                TotalLiability,
                TotalCurrentAssets,
                TotalCurrentLiability,
                CashOrDepositInCentralBank,
                NetProfit,
                NetOperateCashFlow
                '''
        #
        if len(stockList) == 1:
            stockList = '(%s)'%stockList[0]
        else:
            stockList = tuple(stockList)
        #
        query = '''
            SELECT
                %s
            FROM
                [dbo].[CY_FinacialIndicators_RP]
            where 
            EndDate <= '%s 00:00:00:000'
            AND 
            EndDate >= '%s 00:00:00:000'
            AND SecuCode IN %s
            ORDER BY
                SecuAbbr DESC,
                EndDate Asc
        '''%(itemString,endDate,startDate,stockList)
        #
        #print(query)
        self.cursor.execute(query)

        raw = self.cursor.fetchall()
        logger.info('raw :',raw)

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


if __name__ == '__main__':
    logger.warn(__file__)
    myAPI = API('BTEMmidd2')
    myAPI.init()
    y = myAPI.getFinancialIndicator('2017-01-05','2017-06-06',['600000','000002'])
    print(y)