from PyQt5.QtCore import *
from PyQt5.QAxContainer import *
from PyQt5.QtWidgets import *
import pymysql
import datetime
from sqlalchemy import *
from collections import defaultdict
from pandas import DataFrame
import re

from Logger import *
import cf
from Simulator_Api import *

pymysql.install_as_MySQLdb()

class Open_Api(QAxWidget):
    def __init__(self):
        super().__init__()

        # event_loop list
        self.login_event_loop=QEventLoop()
        self.tr_event_loop=QEventLoop()

        # open_api 호출 횟수를 저장
        self.rq_count=0
        self.set_date()
        self.tr_loop_count=0
        self.call_time=datetime.datetime.now()

        # open_api 연동
        self._create_instance()
        self._set_signal_slots()
        self.comm_connect()

        # 계좌정보 출력
        self.get_account_info()

        # 변수 설정
        self.set_variable()

        self.sf=Simulator_Func(self.simul_num,"real",self.db_name)
        logger.debug("알고리즘 번호 : %s",self.simul_api.simul_num)
        logger.debug("매수 알고리즘 번호 : %s",self.simul_api.buy_algorithm)
        logger.debug("매도 알고리즘 번호 : %s",self.simul_api.sell_algorithm)

        # 시뮬레이션 데이터베이스에 setting_data 테이블이 존재하지 않는다면 생성
        if not self.simul_api.is_table_exist(self.db_name,"setting_data"):
            self.init_setting_data()

        self.set_simul_variable()
        self.ohlcv=defaultdict(list)

    # 날짜 설정
    def set_date(self):
        self.today=datetime.datetime.today().strftime("%Y%m%d")
        self.today_time=datetime.datetime.today().strftime("%Y%m%d%H%M")

    # 키움 open_api 를 사용하기 위한 ocx controller 생성
    def _create_instance(self):
        try:
            self.setControl("KHOPENAPI.KHOpenAPICtrl.1")
        except Exception as e:
            logger.critical(e)

    # 이벤트 처리를 위한 slots
    def _set_signal_slots(self):
        try:
            # 로그인 처리 이벤트
            self.OnEventConnect.connect(self._login_slot)
            # 조회요청 처리 이벤트
            self.OnReceiveTrData.connect(self._receive_tr_data)
            # 서버 통신 후 수신메세지 처리 이벤트
            self.OnReceiveMsg.connect(self._receive_msg)
            # 주문요청 처리 이벤트
            self.OnReceiveChejanData.connect(self._receive_chejan_data)
        except Exception as e:
            logger.critical(e)

    # 로그인 이벤트 처리 slot
    # param : code - 로그인 성공 시 0
    #                      실패 시 에러코드 출력
    def _login_slot(self,code):
        try:
            if code==0:
                logger.debug("connected")
            else:
                logger.debug("connection failed...")
            self.login_event_loop.exit()
        except Exception as e:
            logger.critical(e)

    # 사용자의 계좌정보 저장 및 출력
    def get_account_info(self):
        account_no=self.get_login_info("ACCNO")
        self.account_no=account_no.split(";")[0]
        logger.debug(self.account_no)

    # 원하는 사용자 정보 반환
    # param     :   tag - ACCNO - 보유계좌리스트
    #                     ACCOUNT_CNT - 보유계좌 수
    #                     USER_ID - 사용자 ID
    #                     USER_NAME - 사용자 이름
    #                     KEY_BSECGB - 키보드 보안 해제 여부 (0 : 정상, 1: 해지)
    #                     FIREW_SECGB - 방화벽 설정여부 (0 : 미설정, 1: 설정, 2 : 해지)
    #                     GetServerGubun - 접속서버 구분 (1 : 모의투자, 나머지 : 실서버)
    # return    :   tag를 통해 요청한 정보(ret) 반환
    def get_login_info(self,tag):
        try:
            ret=self.dynamicCall("GetLoginInfo(QString)",tag)
            return ret
        except Exception as e:
            logger.critical(e)

    # 수동 로그인설정인 경우 로그인창을 출력해서 로그인을 시도
    # 자동로그인 설정인 경우 로그인창 출력없이 로그인을 시도
    def comm_connect(self):
        try:
            self.dynamicCall("CommConnect()")
            self.login_event_loop.exec_()
        except Exception as e:
            logger.critical(e)

    # TR 요청을 처리하는 slot
    # param sScrNo      : 스크린번호
    #       sRQName     : 요청했을 때 지은 이름
    #       sTrCode     : 요청 id, tr코드
    #       sRecordName : 레코드 이름
    #       sPrevNext   : 다음 페이지가 있는지 여부. "2" : 다음페이지 존재, "0" or "" : 다음페이지 없음
    def _receive_tr_data(self,sScrNo,sRQName,sTrCode,sRecordName,sPrevNext):
        if sPrevNext=='2':
            self.remained_data=True     # 다음 페이지가 존재하는지 확인하는 변수
        else:
            self.remained_data=False

        # Request 요청에 따른 함수 처리
        if sRQName == "opt10081_req" and self.purpose == "trader":
            logger.debug("주식일봉차트조회요청")
            self._opt10081(sRQName,sTrCode)
        elif sRQName == "opt10081_req" and self.purpose == "collector":
            logger.debug("주식일봉차트조회요청")
            self.collector_opt10081(sRQName,sTrCode)
        elif sRQName == "opw00001_req":
            logger.debug("예수금상세현황요청")
            self._opw00001(sRQName,sTrCode)
        elif sRQName == "opw00018_req":
            logger.debug("계좌평가잔고내역요청")
            self._opw00018(sRQName,sTrCode)
        elif sRQName == "opt10074_req":
            logger.debug("일자별실현손익요청")
            self._opt10074(sRQName,sTrCode)
        elif sRQName == "opw00015_req":
            logger.debug("위탁종합거래내역요청")
            self._opw00015(sRQName,sTrCode)
        elif sRQName == "opt10076_req":
            logger.debug("실시간체결요청")
            self._opt10076(sRQName,sTrCode)
        elif sRQName == "opt10073_req":
            logger.debug("일자별종목별실현손익요청")
            self._opt10073(sRQName,sTrCode)
        elif sRQName == "opt10080_req":
            logger.debug("주식분봉차트조회요청")
            self._opt10080(sRQName,sTrCode)
        elif sRQName == "send_order_req":
            pass
        else:
            logger.debug("Invalid Request Code...")

        # 다음 페이지가 존재할 경우 처리
        # 다음 페이지가 존재하지 않을 경우 event loop를 종료
        if sRQName!="send_order_req":
            self.tr_loop_count-=1
        try:
            if self.tr_loop_count<=0:
                self.tr_event_loop.exit()
                self.tr_loop_count=0
        except AttributeError:
            pass

    # 서버통신 후 수신한 메시지를 알려주는 slot
    def _receive_msg(self,sScrNo,sRQName,sTrCode,sMsg):
        logger.debug(sMsg)

    # 주문요청후 주문접수, 체결통보, 잔고통보를 수신할 때 마다 호출
    # GetChejanData()함수를 이용해서 상세한 정보를 얻을 수 있다
    # param     :   sGubun  -   체결구분. 접수와 체결시 '0'값, 국내주식 잔고전달은 '1'값, 파생잔고 전달은 '4'
    def _receive_chejan_data(self,sGubun,nItemCnt,sFIdList):
        # 체결구분. 접수와 체결
        if sGubun=="0":
            code=self.get_chejan_data(9001)         # 현재 체결 진행 중인 코드
            code=re.compile(r'\d{6}')               # 종목코드는 연속된 숫자 6자리
            order_num=self.get_chejan_data(9203)    # 주문번호

            # 주문번호가 존재하지 않는다면 주문실패
            if not order_num:
                logger.debug(code,"주문 실패")
                return

            chegyul_fail_amount=self.get_chejan_data(902)   # 미체결 수량
            order_gubun=self.get_chejan_data(905)           # 주문구분
            purchase_price=self.get_chejan_data(10)         # 체결가

            # 종목코드가 존재한다면
            if code:
                if chegyul_fail_amount!="":
                    # 해당 종목을 보유하고 있지 않은 경우
                    if not self.is_exist_possess_item_in_transaction(code):
                        # 해당 종목의 체결 실패 내역이 없다면
                        # transaction 테이블에 업데이트. 정상 체결 시 chegyul_check=0
                        if chegyul_fail_amount=="0":
                            logger.debug(code, "체결 완료")
                            self.db_to_transaction(order_num,code,0,purchase_price,0)
                        # 체결 실패 내역이 존재한다면
                        # transaction 테이블에 업데이트. 미체결 시 chegyul_check=1
                        else:
                            logger.debug(code,"미체결")
                            self.db_to_transaction(order_num,code,1,purchase_price,0)

                    # 매수하는 경우
                    elif order_gubun=="+매수":
                        if chegyul_fail_amount!="0" and self.check_stock_chegyul(code):
                            logger.debug("미체결. 매수 진행중!")
                            pass
                        elif chegyul_fail_amount=="0" and self.check_stock_chegyul(code):
                            logger.debug("매수 완료")
                            self.check_end_invest(code)
                        else:
                            pass

                    # 매도하는 경우
                    elif order_gubun=="-매도":
                        if chegyul_fail_amount=="0":
                            logger.debug("전량 매도")
                            self.check_end_sell(code)
                        else:
                            logger.debug("부분 매도")
                            self.check_sell_chegyul_fail(code)

                    else:
                        logger.debug("Invalid order_gubun")
                else:
                    logger.debug("Invalid chegyul_fail_amount value")
            else:
                logger.debug("can't receive code value")

        # 국내주식 잔고전달
        elif sGubun=="1":
            chegyul_fail_amount=self.get_chejan_data(902)
            logger.debug("미체결 수량",chegyul_fail_amount)

        else:
            logger.debug("Invlid _receive_chejan_data")

    # 특정 종목을 보유하고 있는지 확인하는 함수
    def is_exist_possess_item_in_transaction(self,code):
        query = "select code from transaction " \
                "where code='%s' and (sell_date ='%s' or sell_date='%s') ORDER BY buy_date desc LIMIT 1"
        result = self.engine_bot.execute(query % (code, 0, "")).fetchall()
        if len(result) != 0:
            return True
        else:
            return False

    # 해당 종목이 체결되었는지 확인하는 함수
    def check_stock_chegyul(self,code):
        query = "SELECT chegyul_check FROM transaction " \
                "where code='%s' and sell_date = '%s' ORDER BY buy_date desc LIMIT 1"
        result = self.engine_bot.execute(query % (code, 0)).fetchall()
        if result[0][0] == 1:
            return True
        else:
            return False

    # 체결 완료 시 transaction 테이블의 chegyul_check항목을 업데이트
    def check_end_invest(self,code):
        query = "UPDATE transaction " \
                "SET " \
                "chegyul_check='%s' WHERE code='%s' and sell_date = '%s' ORDER BY buy_date desc LIMIT 1"
        self.engine_bot.execute(query % (0, code, 0))

    # 매도 완료 후 DB 업데이트트
    def check_sell_end(self,code):
        query="select valuation_profit,rate,item_total_urchase,present_price" \
              "from possessed_item" \
              "where code={} limit 1"
        get_list=self.engine_bot.execute(query.format(code)).fetchall()

        # 매도 완료 종목에 대해 DB 업데이트
        if get_list:
            item = get_list[0]
            query = f"""UPDATE transaction
                    SET 
                    item_total_purchase = {item.item_total_purchase}, chegyul_check = 0,
                    sell_date = '{self.today_detail}', valuation_profit = {item.valuation_profit},
                    sell_rate = {item.rate}, sell_price = {item.present_price}
                    WHERE code = '{code}' and sell_date = '0' ORDER BY buy_date desc LIMIT 1"""
            self.engine_bot.execute(query)

            # 매도 완료 후 possessd_item 테이블에서 해당 종목 삭제
            self.engine_bot.execute(f"DELETE FROM possessed_item WHERE code = '{code}'")
        else:
            logger.debug("보유 종목이 없습니다.")

    # 매도 체결 실패시 DB 업데이트
    def check_sell_chegyul_fail(self,code):
        query = "UPDATE transaction SET chegyul_check='%s' " \
                "WHERE code='%s' and sell_date = '%s' ORDER BY buy_date desc LIMIT 1"
        self.engine_bot.execute(query % (1, code, 0))

    # OnReceiveChejan()이벤트가 호출될때 체결정보나 잔고정보를 얻어오는 함수
    # param     :   nFid    -   실시간 타입에 포함된 FID
    # FID List
    # (FID, 설명) : (9023, 주문번호), (302, 종목명), (900, 주문수량), (901,주문가격), (902,미체결수량), (904,원주문번호),
    #              (905, 주문구분), (908, 주문/체결시간), (909, 체결번호), (910, 체결가), (911, 체결량), (10, 현재가/체결가/실시간종가)
    def get_chejan_data(self,nFid):
        try:
            ret=self.dynamicCall("GetChejanData(int)",nFid)
            return ret
        except Exception as e:
            logger.critical(e)

    # 거래이력(transaction)테이블에 현재 보유중인 종목이 있는지 확인하는 함수
    def is_transaction_check(self,code):
        query = "select code from transaction " \
                "where code='%s' and (sell_date ='%s' or sell_date='%s') ORDER BY buy_date desc LIMIT 1"
        rows = self.engine_bot.execute(query % (code, 0, "")).fetchall()
        if len(rows) != 0:
            return True
        else:
            return False

    # 조회요청시 TR의 Input값을 지정하는 함수
    # param : sId       -   TR에 명시된 Input이름
    #         svalue    -   Input이름으로 지정한 값
    def set_input_value(self,sId,sValue):
        try:
            self.dynamicCall("SetInputValue(QString, QString)", sId, sValue)
        except Exception as e:
            print(e)
            sys.exit()

    # 조회요청함수
    # param : sRQName       -   사용자 구분명
    #         sTrCode       -   조회하려는 TR이름
    #         nPrevNext     -   연속조회여부
    #         sScreenNo     -   화면번호
    def comm_rq_data(self,sRQName,sTrData,nPrevNext,sScrNo):
        self.dynamicCall("CommRqData(QString, QString, int, QString", sRQName, sTrData, nPrevNext, sScrNo)
        self.tr_event_loop.exec_()

    # OnReceiveTRData()이벤트가 호출될때 조회데이터를 얻어오는 함수
    # param : sTrCode       -   TR 이름
    #         sRecordName   -   레코드이름
    #         nIndex        -   TR반복부
    #         sItemName     -   TR에서 얻어오려는 출력항목이름
    def _get_comm_data(self,sTrCode,sRecordName,nIndex,sItemName):
        ret = self.dynamicCall("GetCommData(QString, QString, int, QString", sTrCode, sRecordName, nIndex, sItemName)
        return ret.strip()

    # 조회수신한 멀티데이터의 갯수(반복)을 얻는다
    # param : sTrCode       -   tr이름
    #         sRecordName   -   레코드 이름
    def _get_repeat_cnt(self,sTrCode,sRecordName):
        try:
            ret=self.dynamicCall("GetRepeatCnt(QString, QString)",sTrCode,sRecordName)
            return ret
        except Exception as e:
            print(e)
            sys.exit()

    # 변수 설정
    # 실전투자인지 모의투자인지 여부를 확인하고 그에 해당하는 데이터베이스를 생성하는 함수
    def set_variable(self):
        self.cf=cf
        self.get_today_buy_list_code=0
        self.reset_opw00018_output()

        if self.account_no==self.cf.real_account_no:
            logger.debug("실전투자")
            self.simul_num=self.cf.real_num
            self.set_database(cf.real_bot_name)
            self.mod_gubun=100      # 실전투자와 모의투자를 구분하는 변수

        elif self.account_no==self.cf.test_account_no:
            logger.debug("모의투자")
            self.simul_num=self.cf.test_num
            self.set_database(cf.test_bot_name)
            self.mod_gubun=1

        else:
            logger.debug("Invalid Account Number. Check the Config.py")
            exit(1)

        self.is_balance_null=True
        self.py_gubun=False

    # 데이터베이스 생성 및 엔진 설정
    def set_database(self,db_name):
        self.db_name=db_name
        conn=pymysql.connect(
            host=cf.db_ip,
            port=int(cf.db_port),
            user=cf.db_id,
            password=cf.db_pw,
            charset='utf8mb4'
        )
        cursor=conn.cursor()
        if not self.is_database_exist(cursor):
            self.create_database(cursor)
        self.engine_bot=create_engine("mysql+pymysql://"+self.cf.db_id+":"+self.cf.db_pw+"@"+
                                      self.cf.db_ip + ":" + self.cf.db_port+"/"+db_name,encoding='utf-8')
        self.create_basic_database(cursor)

        conn.commit()
        cursor.close()
        conn.close()

        self.engine_craw = create_engine("mysql+pymysql://" + cf.db_id + ":" + cf.db_pw + "@" + cf.db_ip + ":" +
                                         cf.db_port + "/min_craw",encoding='utf-8')
        self.engine_daily_craw = create_engine("mysql+pymysql://" + cf.db_id + ":" + cf.db_pw + "@" + cf.db_ip + ":" +
                                               cf.db_port + "/daily_craw",encoding='utf-8')
        self.engine_daily_buy_list = create_engine("mysql+pymysql://" + cf.db_id + ":" + cf.db_pw + "@" + cf.db_ip + ":"
                                                   + cf.db_port + "/daily_buy_list",encoding='utf-8')

    # Bot Database가 존재하는지 확인하는 함수
    def is_database_exist(self,cursor):
        query="select 1 from information_schema.schemata where schema_name='{}'"
        result=cursor.execute(query.format(self.db_name))
        if result:
            return True
        else:
            return False

    # Bot Database를 생성하는 함수
    def create_database(self,cursor):
        query="create database {}"
        cursor.execute(query.format(self.db_name))

    # daily_craw(종목의 날짜별 데이터), daily_buy_list(날짜별 종목 데이터), min_craw(종목의 분별 데이터) 가 존재하는지 확인하는 함수
    # 존재하지 않는다면 새로 생성
    def create_basic_database(self,cursor):
        check_list = ['daily_craw', 'daily_buy_list', 'min_craw']
        query = "SELECT SCHEMA_NAME FROM information_schema.SCHEMATA"
        cursor.execute(query)
        result = cursor.fetchall()
        db_list = [n['SCHEMA_NAME'].lower() for n in result]
        create_db_query = "CREATE DATABASE {}"
        has_created = False
        for check_name in check_list:
            if check_name not in db_list:
                has_created = True
                logger.debug(check_name,"데이터베이스 생성...")
                create_db_sql = create_db_query.format(check_name)
                cursor.execute(create_db_sql)

        if has_created and self.engine_bot.has_table('setting_data'):
            self.engine_bot.execute("UPDATE setting_data SET code_update = '0';")

    # setting_data 테이블 생성 및 초기화하는 함수
    def init_setting_data(self):
        df_setting_data_temp = {'limit_money': [], 'invest_unit': [], 'max_invest_unit': [],
                                'min_invest_unit': [],'set_invest_unit': [], 'code_update': [],
                                'today_buy_stop': [],'account_balance_db_check': [], 'possessed_item': [],
                                'today_profit': [],'final_chegyul_check': [],'db_to_buy_list': [], 'today_buy_list': [],
                                'daily_crawler': [],'daily_buy_list': []}

        df_setting_data = DataFrame(df_setting_data_temp,
                                    columns=['limit_money', 'invest_unit', 'max_invest_unit','min_invest_unit',
                                             'set_invest_unit', 'code_update', 'today_buy_stop',
                                             'account_balance_db_check', 'possessed_item', 'today_profit',
                                             'final_chegyul_check','db_to_buy_list', 'today_buy_list', 'daily_crawler',
                                             'daily_buy_list'])

        # 칼럼 자료형 설정
        df_setting_data.loc[0, 'limit_money'] = int(0)                      # 잔고에 최소한으로 남겨놓을 금액
        df_setting_data.loc[0, 'invest_unit'] = int(0)                      # 기준 투자금액
        df_setting_data.loc[0, 'max_invest_unit'] = int(0)                  # 최대 투자금액
        df_setting_data.loc[0, 'min_invest_unit'] = int(0)                  # 최소 투자금액
        df_setting_data.loc[0, 'set_invest_unit'] = str(0)                  # 기준 투자금액 설정 날짜

        df_setting_data.loc[0, 'code_update'] = str(0)                      # setting_data의 데이터를 업데이트 한 날짜
        df_setting_data.loc[0, 'today_buy_stop'] = str(0)                   # 당일 구매 종료 설정 시간
        df_setting_data.loc[0, 'account_balance_db_check'] = str(0)         # 잔고데이터 DB 업데이트 날짜
        df_setting_data.loc[0, 'possessed_item'] = str(0)                   # 보유종목 DB 업데이트 날짜
        df_setting_data.loc[0, 'today_profit'] = str(0)                     # 당일 수익

        df_setting_data.loc[0, 'final_chegyul_check'] = str(0)              # 마지막 체결 확인
        df_setting_data.loc[0, 'db_to_buy_list'] = str(0)                   # 구매 리스트 DB 업데이트 날짜
        df_setting_data.loc[0, 'today_buy_list'] = str(0)                   # 당일 구매 리스트 DB 업데이트 날짜
        df_setting_data.loc[0, 'daily_crawler'] = str(0)                    # daily_crawler(종목의 일별 데이터) 업데이트 날짜
        df_setting_data.loc[0, 'min_crawler'] = str(0)                      # min_crawler(종목의 분별 데이터) 업데이트 날자
        df_setting_data.loc[0, 'daily_buy_list'] = str(0)                   # daily_buy_list(일별 종목 데이터) 업데이트 날짜

        df_setting_data.to_sql('setting_data', self.engine_JB, if_exists='replace')

    # simulator_fun 에서 설정한 변수를 가져오는 함수
    def set_simul_variable(self):
        # daily_buy_list에 저장된 가장 최신 날짜
        self.date_rows_yesterday=self.sf.get_recent_daily_buy_list_date()

        # AutoBot 데이터베이스에 transaction 테이블이 존재하지 않을 경우
        # 테이블 생성 및 초기화
        if not self.sf.is_simul_table_exist(self.db_name,"transaction"):
            logger.debug("transaction 테이블을 생성합니다")
            self.invest_unit=0
            self.db_to_transaction(0,0,0,0,0)
            # 테이블 생성 후 초기화
            self.delete_transaction_item("0")

        # setting_data에 invest_unit값이 없다면 설정
        if not self.check_set_invest_unit():
            self.set_invest_unit()
        # 존재한다면 해당 값을 simulator_func에 저장
        else:
            self.invest_unit=self.get_invest_unit()
            self.sf.invest_unit=self.invest_unit

    # transaction(거래 내역) 테이블 생성
    def db_to_transaction(self,order_num,code,chegyul_check,purchase_price,rate):
        self.date_setting()
        # 거래내역 DataFrame 생성
        self.sf.init_df_transaction()

        # dataframe에 값 할당
        self.sf.df_transaction.loc[0, 'order_num'] = order_num                  # 주문번호
        self.sf.df_transaction.loc[0, 'code'] = str(code)                       # 종목코드
        self.sf.df_transaction.loc[0, 'rate'] = float(rate)                     # 수익률
        self.sf.df_transaction.loc[0, 'buy_date'] = self.today_detail           # 구매날짜
        self.sf.df_all_item.loc[0, 'chegyul_check'] = chegyul_check             # 체결확인

        self.sf.df_all_item.loc[0, 'invest_unit'] = self.invest_unit            # 투자기준금액
        self.sf.df_all_item.loc[0, 'purchase_price'] = purchase_price           # 구매금액

        if order_num != 0:
            recent_daily_buy_list_date=self.sf.get_recent_daily_buy_list_date()
            if recent_daily_buy_list_date:
                # 특정 날짜, 특정 종목의 주가 데이터
                df=self.sf.get_daily_buy_list_by_code(code,recent_daily_buy_list_date)
                if not df.empty:
                    self.sf.df_transaction.loc[0, 'code_name'] = df.loc[0, 'code_name']     # 종목명

                    self.sf.df_transaction.loc[0, 'close'] = df.loc[0, 'close']             # 종가
                    self.sf.df_transaction.loc[0, 'open'] = df.loc[0, 'open']               # 시가
                    self.sf.df_transaction.loc[0, 'high'] = df.loc[0, 'high']               # 고가
                    self.sf.df_transaction.loc[0, 'low'] = df.loc[0, 'low']                 # 저가
                    self.sf.df_transaction.loc[0, 'volume'] = df.loc[0, 'volume']           # 거래량

                    self.sf.df_transaction.loc[0,'d1_diff']=float(df.loc[0,'d1_diff'])      # 전날대비 가격변동
                    self.sf.df_transaction.loc[0, 'd1_diff_rate'] = float(df.loc[0, 'd1_diff_rate'])    # 전날대비 가격변동률

                    self.sf.df_transaction.loc[0, 'clo5'] = df.loc[0, 'clo5']               # 5일 이동평균선
                    self.sf.df_transaction.loc[0, 'clo10'] = df.loc[0, 'clo10']             # 10일 이동평균선
                    self.sf.df_transaction.loc[0, 'clo20'] = df.loc[0, 'clo20']             # 20일 이동평균선
                    self.sf.df_transaction.loc[0, 'clo60'] = df.loc[0, 'clo60']             # 60일 이동평균선
                    self.sf.df_transaction.loc[0, 'clo120'] = df.loc[0, 'clo120']           # 120일 이동평균선

                    if df.loc[0, 'clo5_diff_rate'] is not None:
                        self.sf.df_transaction.loc[0, 'clo5_diff_rate'] = float(df.loc[0, 'clo5_diff_rate'])        # 5일 이동평균선 변동률
                    if df.loc[0, 'clo10_diff_rate'] is not None:
                        self.sf.df_transaction.loc[0, 'clo10_diff_rate'] = float(df.loc[0, 'clo10_diff_rate'])
                    if df.loc[0, 'clo20_diff_rate'] is not None:
                        self.sf.df_transaction.loc[0, 'clo20_diff_rate'] = float(df.loc[0, 'clo20_diff_rate'])
                    if df.loc[0, 'clo60_diff_rate'] is not None:
                        self.sf.df_transaction.loc[0, 'clo60_diff_rate'] = float(df.loc[0, 'clo60_diff_rate'])
                    if df.loc[0, 'clo120_diff_rate'] is not None:
                        self.sf.df_transaction.loc[0, 'clo120_diff_rate'] = float(df.loc[0, 'clo120_diff_rate'])

        # null값을 0으로 변환
        self.sf.df_all_item = self.sf.df_all_item.fillna(0)
        self.sf.df_all_item.to_sql('transaction', self.engine_bot, if_exists='append', dtype={
            'code_name': Text,
            'rate': Float,
            'sell_rate': Float,
            'purchase_rate': Float,
            'sell_date': Text,
            'd1_diff': Integer,
            'd1_diff_rate': Float,
            'clo5_diff_rate': Float,
            'clo10_diff_rate': Float,
            'clo20_diff_rate': Float,
            'clo60_diff_rate': Float,
            'clo120_diff_rate': Float,
        })

    # 거래내역(transaction) 테이블에서 특정 종목을 삭제하는 함수
    def delete_transaction_item(self,code):
        query=f"delete from transaction where code={code}"
        self.engine_bot.execute(query)

    # setting_data 테이블에 invest_unit이 오늘 업데이트 되었는지 확인
    def check_set_invest_unit(self):
        query="select invest_unit, set_invest_unit from setting_data"
        result=self.engine_bot.execute(query).fetchall()
        if result[0][1]==self.today:
            self.invest_unit=result[0][0]
            return True
        else:
            return False

    # 데이터베이스에서 invest_unit값을 가져오는 함수
    def get_invest_unit(self):
        query="select invest_unit from setting_data"
        result=self.engine_bot.execute(query).fetchall()
        return result[0][0]

    # invest_unit 항목 업데이트
    # 업데이트 완료 후 set_invest_unit에 업데이트 날짜 저장
    def set_invest_unit(self):
        self.get_deposit()
        self.get_balance()
        self.total_invest=self.deposit+self.total_purchase_price

        self.invest_unit=self.sf.invest_unit
        query=f"update setting_data set invest_unit='{self.invest_unit}', set_invest_unit='{self.today}'"
        self.engine_bot.execute(query)

    # 예수금 조회 및 저장
    def get_deposit(self):
        self.set_input_value("계좌번호",self.account_no)
        self.set_input_value("비밀번호입력매체구분",00)
        self.set_input_value("조회구분",1)
        self.comm_rq_data("opw00001_req","opw00001",0,"2000")

    # 잔고 조회 및 저장
    def get_balance(self):
        self.reset_opw00018_output()
        self.set_input_value("계좌번호",self.account_no)
        self.comm_rq_data("opw00018_req","opw00018",0,"2000")
        # 다음페이지가 존재할 경우 계속해서 조회
        while self.remained_data:
            self.set_input_value("계좌번호",self.account_no)
            self.comm_rq_data("opw00018_req","opw00018",2,"2000")

    # open_api를 통해 보유한 종목을 가져오는 함수
    # 가져온 정보를 posses_item이라는 테이블에 저장
    def get_posses_item(self):
        item_count=len(self.opw00018_output['multi'])
        posses_item_data={'date':[],'code':[],'code_name':[],'holding_amount':[],'purchase_price':[],
                          'present_price':[],'valuation_profit':[],'rate':[],'item_total_purchase':[]}

        posses_item=DataFrame(posses_item_data,
                              columns=['date','code','code_name','holding_amount','purchase_price',
                                       'present_price','valuation_profit','rate','item_total_purchase'])

        for i in range(item_count):
            item=self.opw00018_output['multi'][i]

            posses_item.loc[i,'date']=self.today
            posses_item.loc[i,'code']=item[7]
            posses_item.loc[i,'code_name']=item[0]
            posses_item.loc[i,'holding_amount']=int(item[1])
            posses_item.loc[i,'purchase_price']=int(item[2])
            posses_item.loc[i,'present_price']=int(item[3])
            posses_item.loc[i,'valuation_profit']=int(item[4])
            posses_item.loc[i,'rate']=float(item[5])
            posses_item.loc[i,'item_total_purchase']=int(item[6])

        posses_item.to_sql("posses_item",self.engine_bot,if_exists='replace')
        self.contract_sync()

    # 현재 소유하고 있는 종목에 대해 transaction_history 테이블을 업데이트
    def contract_sync(self):
        query="select code,code_name,rate from posses_item p" \
              "where p.code not in (select a.code from transaction_history a" \
              "where a.sell_date='0' group by a.code)" \
              "group by p.code"
        result=self.engine_bot.execute(query).fetchall()

        for item in result:
            self.set_input_value("종목코드",item.code)
            self.set_input_value("조회구분",1)
            self.set_input_value("계좌번호",self.account_no)
            self.comm_rq_data("opt10076_req","opt10076",0,"0350")

            if self.not_contract['주문구분']=="+매수":
                if self.not_contract['미체결수량']==0:
                    contract_check=0
                else:
                    contract_check=1
            elif self.not_contract['주문구분']=='':
                self.create_transaction_history(self.today,item.code,0,0,item.rate)
                continue
            else:
                continue

            self.create_transaction_history(self.not_contract['주문번호'],item.code,contract_check,self.not_contract['체결가'],item.rate)

    # posses_item 테이블을 업데이트 했을 경우 setting data 테이블에 업데이트 한 날짜를 표시
    def setting_data_posses_stock(self):
        query="update setting_data set posses_stocks='%s'"
        self.engine_bot.execute(query%self.today)

    # 특정 종목의 일자별 거래 데이터 조회 함수
    def get_date_data(self,code,code_name,date):
        self.ohlcv=defaultdict(list)
        self.set_input_value("종목코드",code)
        self.set_input_value("기준일자",date)
        self.set_input_value("수정주가구분",1)

        # 한번에 600일치의 데이터를 가져온다
        self.comm_rq_data("opt10081_req","opt10081",0,"0101")

        # stock_info 테이블이 존재하지 않는다면 600일치 이상의 전체 데이터를 가져와야 하므로 다음 페이지가 존재하지 않을 때까지 조회
        if not self.is_stock_table_exist(code_name):
            while self.remained_data==True:
                self.set_input_value("종목코드",code)
                self.set_input_value("기준일자",date)
                self.set_input_value("수정주가구분",1)
                self.comm_rq_data("opt10081_req","opt10081",2,"0101")

        if len(self.ohlcv)==0:
            return []
        if self.ohlcv['date']=='':
            return []

        df=DataFrame(self.ohlcv,columns=['date','open','high','low','close','volume'])

        return df

    # stock_info 데이터베이스가 존재하는지 확인하는 함수
    def is_stock_table_exist(self,code_name):
        query = "select 1 from information_schema.tables where table_schema ='stock_info' and table_name = '{}'"
        result=self.engine_stock.execute(query.format(code_name)).fetchall()
        if result:
            return True
        else:
            return False

    # stock_info의 특정 종목 테이블에서 마지막으로 저장된 날짜를 가져오는 함수
    # 저장된 데이터가 없다면 '0'문자를 반환
    def get_latest_date_from_stock_info(self,code_name):
        query="select date from {} order by date desc limit 1"
        result=self.engine_stock.execute(query.format(code_name)).fetchall()
        if len(result):
            return result[0][0]
        else:
            return str(0)

    def check_contract(self):
        query="select code from transaction_history where contract_check='1' and (sell date='0' or sell_date='')"
        rows=self.engine_bot.execute(query).fetchall()

        for r in rows:
            self.set_input_value("종목코드",r.code)
            self.set_input_value("조회구분",1)
            self.set_input_value("계좌번호",self.account_no)
            self.comm_rq_data("opt10076_req","opt10076",0,"0350")

            query="update transaction_history set contract_check='0' where code='{}' and sell_date='0'"
            # 거래가 완료된 항목은 주문번호가 존재하지 않음
            # 거래가 완료된 항목에 대해서 contract_check항목을 '0'으로 업데이트
            if not self.not_contract['주문번호']:
                self.engine_bot.execute(query.format(r.code))
            # 미체결 수량이 없을 경우 contract_check항목을 '0'으로 업데이트
            elif self.not_contract['미체결수량']==0:
                logger.debug("미체결 항목이 없습니다")
                self.engine_bot.execute(query.format(r.code))
            else:
                logger.debug("미체결 종목이 존재합니다")

    # posses_item테이블에는 존재하지만 transaction_history테이블에 존재하지 않는 항목에 대해 업데이트
    def final_check_contract(self):
        query="select code from transaction_history" \
              "where" \
              "sell_date='%s' or sell_date='%s' and code not in (select code from posses_item) and contract_check!='%s'"
        result=self.engine_bot.execute(query%(0,"",1)).fetchall()
        num=len(result)
        for i in range(num):
            self.sell_check(result[i][0])
        query="update setting_data set final_contract_check='%s'"
        self.engine_bot.execute(query%(self.today))

    # 가지고 있던 종목을 판매하여 posses_item테이블에 내역이 존재하지 않지만 transaction_history에 매도처리가 되지 않은 항목 업데이트
    def sell_check(self,code):
        query="update transaction_history" \
              "set" \
              "contract_check='%s', sell_date='%s'" \
              "where code='%s' and sell_date='%s'"
        self.engine_bot.execute(query%(0,self.today_time,code,0))

    # 주식일봉차트조회 요청 함수 - 하나의 데이터만 저장
    def _opt10081(self, sRQName, sTrCode):
        code = self._get_comm_data(sTrCode, sRQName, 0, "종목코드")
        # 조회하는 종목이 매수하려는 종목이 아니라면 에러메세지 출력
        if code != self.get_today_buy_list_code:
            logger.critical(f"_opt10081:({code},{self.get_today_buy_list_code})")

        date = self._get_comm_data(sTrCode, sRQName, 0, "일자")
        open = self._get_comm_data(sTrCode, sRQName, 0, "시가")
        high = self._get_comm_data(sTrCode, sRQName, 0, "고가")
        low = self._get_comm_data(sTrCode, sRQName, 0, "저가")
        close = self._get_comm_data(sTrCode, sRQName, 0, "현재가")
        volume = self._get_comm_data(sTrCode, sRQName, 0, "거래량")

        self.ohlcv['date'].append(date)
        self.ohlcv['open'].append(int(open))
        self.ohlcv['high'].append(int(high))
        self.ohlcv['low'].append(int(low))
        self.ohlcv['close'].append(int(close))
        self.ohlcv['volume'].append(int(volume))

    # 주식일봉차트조회 요청 함수 - 모든 행의 데이터를 저장
    def collector_opt10081(self, sRQName, sTrCode):
        # 몇개의 행을 읽어야 하는지 담는 변수
        ohlcv_cnt = self._get_repeat_cnt(sTrCode, sRQName)

        for i in range(ohlcv_cnt):
            date = self._get_comm_data(sTrCode, sRQName, i, "일자")
            open = self._get_comm_data(sTrCode, sRQName, i, "시가")
            high = self._get_comm_data(sTrCode, sRQName, i, "고가")
            low = self._get_comm_data(sTrCode, sRQName, i, "저가")
            close = self._get_comm_data(sTrCode, sRQName, i, "현재가")
            volume = self._get_comm_data(sTrCode, sRQName, i, "거래량")

            self.ohlcv['date'].append(date)
            self.ohlcv['open'].append(int(open))
            self.ohlcv['high'].append(int(high))
            self.ohlcv['low'].append(int(low))
            self.ohlcv['close'].append(int(close))
            self.ohlcv['volume'].append(int(volume))

    # 예수금 상세현황요청 함수
    def _opw00001(self,sRQName,sTrCode):
        self.deposit=self._get_comm_data(sTrCode,sRQName,0,"d+2출금가능금액")
        self.deposit=int(self.deposit)

    # 계좌평가 잔고내역을 저장하는 변수 초기화
    def reset_opw00018_output(self):
        # single data : 계좌에 대한 평가 잔고 데이터 제공
        # multi data  : 보유 종목별 평가 잔고 데이터 제공
        self.opw00018_output={'single':[],'multi':[]}

    # 계좌평가 잔고내역 요청 함수
    def _opw00018(self,sRQName,sTrCode):
        # 계좌평가 잔고 데이터 - 싱글데이터 저장
        self.total_purchase_price=self._get_comm_data(sTrCode,sRQName,0,"총매입금액")
        self.total_eval_price=self._get_comm_data(sTrCode,sRQName,0,"총평가금액")
        self.total_eval_profit_loss_price=self._get_comm_data(sTrCode,sRQName,0,"총평가손익금액")
        self.total_earning_rate=self._get_comm_data(sTrCode,sRQName,0,"총수익률(%)")
        self.estimated_deposit=self._get_comm_data(sTrCode,sRQName,0,"추정예탁자산")

        self.total_purchase_price=int(self.total_purchase_price)
        self.total_eval_price=int(self.total_eval_price)
        self.total_eval_profit_loss_price=int(self.total_eval_profit_loss_price)
        self.total_earning_rate=float(self.total_earning_rate)
        self.estimated_deposit=int(self.estimated_deposit)

        self.opw00018_output['single'].append(self.total_purchase_price)
        self.opw00018_output['single'].append(self.total_eval_price)
        self.opw00018_output['single'].append(self.total_eval_profit_loss_price)
        self.opw00018_output['single'].append(self.total_earning_rate)
        self.opw00018_output['single'].append(self.estimated_deposit)

        # 종목별 평가 잔고 데이터 - 멀티데이터 저장
        rows=self._get_repeat_cnt(sTrCode,sRQName)

        for i in range(rows):
            code=self._get_comm_data(sTrCode,sRQName,i,"종목번호")
            name=self._get_comm_data(sTrCode,sRQName,i,"종목명")
            quantity=self._get_comm_data(sTrCode,sRQName,i,"보유수량")
            purchase_price=self._get_comm_data(sTrCode,sRQName,i,"매입가")
            current_price=self._get_comm_data(sTrCode,sRQName,i,"현재가")
            eval_profit_loss_price=self._get_comm_data(sTrCode,sRQName,i,"평가손익")
            earning_rate=self._get_comm_data(sTrCode,sRQName,i,"수익률(%)")
            item_total_purchase=self._get_comm_data(sTrCode,sRQName,i,"매입금액")

            code=code[1:]
            quantity=int(quantity)
            purchase_price=int(purchase_price)
            current_price=int(current_price)
            eval_profit_loss_price=int(eval_profit_loss_price)
            earning_rate=float(earning_rate)
            item_total_purchase=int(item_total_purchase)

            self.opw00018_output['multi'].append(
                [name,quantity,purchase_price,current_price,eval_profit_loss_price,earning_rate,item_total_purchase,code]
            )

    # 일자별 실현 손익 요청
    def _opt10074(self,sRQName,sTrCode):
        self.total_profit=self._get_comm_data(sTrCode,sRQName,0,"실현손익")
        self.today_profit=self._get_comm_data(sTrCode,sRQName,0,"당일매도손익")

    # 위탁종합거래내역 요청 함수
    def _opw00015(self,sRQName,sTrCode):
        rows = self._get_repeat_cnt(sTrCode, sRQName)
        acc_no = self._get_comm_data(sTrCode, sRQName, 1, "계좌번호")
        for i in range(rows):
            date = self._get_comm_data(sTrCode, sRQName, i, "거래일자")

    # 실시간체결요청 함수
    def _opt10076(self,sRQName,sTrCode):
        outputs=['주문번호','종목명','주문구분','주문가격','주문수량','체결가','체결량','미체결수량',
                 '당일매매수수료','당일매매세금','주문상태','매매구분','원주문번호','주문시간','종목코드']
        self._data={}

        for key in outputs:
            if key not in ['주문번호','원주문번호','주문시간','종목코드']:
                self._data[key]=int(self._get_comm_data(sTrCode,sRQName,0,key))
                continue
            self._data[key]=self._get_comm_data(sTrCode,sRQName,0,key)

    # _opt10073 결과를 담는 변수를 초기화하는 함수
    def reset_opt10073_output(self):
        self.opt10073_output={'single':[],'multi':[]}

    # 일자별종목별실현손익요청 함수
    def _opt10073(self,sRQName,sTrCode):
        rows=self._get_repeat_cnt(sTrCode,sRQName)

        for i in range(rows):
            date=self._get_comm_data(sTrCode,sRQName,i,"일자")
            code=self._get_comm_data(sTrCode,sRQName,i,"종목코드")
            code_name=self._get_comm_data(sTrCode,sRQName,i,"종목명")
            amount=self._get_comm_data(sTrCode,sRQName,i,"체결량")
            today_profit=self._get_comm_data(sTrCode,sRQName,i,"당일매도손익")
            earning_rate=self._get_comm_data(sTrCode,sRQName,i,"손익율")

            code=code.lstrip('A')

            self.opt10073_output['multi'].append([date,code,code_name,amount,today_profit,earning_rate])

    # 주식분봉차트조회요청 함수
    def _opt10080(self,sRQName,sTrCode):
        data_cnt = self._get_repeat_cnt(sTrCode, sRQName)
        for i in range(data_cnt):
            date = self._get_comm_data(sTrCode, sRQName, i, "체결시간")
            open = self._get_comm_data(sTrCode, sRQName, i, "시가")
            high = self._get_comm_data(sTrCode, sRQName, i, "고가")
            low = self._get_comm_data(sTrCode, sRQName, i, "저가")
            close = self._get_comm_data(sTrCode, sRQName, i, "현재가")
            volume = self._get_comm_data(sTrCode, sRQName, i, "거래량")

            self.ohlcv['date'].append(date[:-2])
            self.ohlcv['open'].append(abs(int(open)))
            self.ohlcv['high'].append(abs(int(high)))
            self.ohlcv['low'].append(abs(int(low)))
            self.ohlcv['close'].append(abs(int(close)))
            self.ohlcv['volume'].append(int(volume))
            self.ohlcv['sum_volume'].append(int(0))


if __name__=="__main__":
    app=QApplication(sys.argv)
    a=Open_Api()