from collections import OrderedDict
from sqlalchemy import Integer, Text
import numpy
import pathlib
import os
import time
from PyQt5.QtWidgets import *
from pandas import DataFrame

from library.open_api import *
from library.daily_buy_list import *

# open_api를 이용하여 데이터베이스에 정보를 저장하는 클래스
class collector_api():
    def __init__(self):
        self.open_api = open_api()
        self.engine_bot = self.open_api.engine_bot
        self.variable_setting()

    # 변수설정
    def variable_setting(self):
        self.open_api.py_gubun = "collector"        # 용도를 저장하는 변수. collector : 데이터 저장 / trader : 종목 거래
        self.dc = daily_crawler(self.open_api.cf.real_bot_name, self.open_api.cf.real_daily_craw_db_name,
                                self.open_api.cf.real_daily_buy_list_db_name)
        self.dbl = daily_buy_list()

    # 콜렉팅을 실행하는 함수
    def code_update_check(self):
        logger.debug("code_update_check 함수에 들어왔습니다.")

        sql = "select code_update,jango_data_db_check, possessed_item, today_profit, final_chegyul_check, " \
              "db_to_buy_list,today_buy_list, daily_crawler , min_crawler, daily_buy_list from setting_data limit 1"
        rows = self.engine_bot.execute(sql).fetchall()

        # 종목 업데이트
        if rows[0][0] != self.open_api.today:
            self.open_api.check_balance()

        # 잔고/보유종목 업데이트 확인
        if rows[0][1] != self.open_api.today or rows[0][2] != self.open_api.today:
            self.py_check_balance()
            self.open_api.set_invest_unit()

        # possessed_item(현재 보유종목) 테이블 업데이트
        if rows[0][2] != self.open_api.today:
            self.open_api.db_to_possessed_item()
            self.open_api.setting_data_possessed_item()

        # 당일 종목별 실현 손익 내역 테이블 업데이트
        if rows[0][3] != self.open_api.today:
            self.db_to_today_profit_list()

        # daily_craw 데이터베이스 업데이트
        if rows[0][7] != self.open_api.today:
            self.daily_crawler_check()

        # daily_buy_list 데이터베이스 업데이트
        if rows[0][9] != self.open_api.today:
            self.daily_buy_list_check()

        # 매수/매도 후 daily_buy_list 데이터베이스 업데이트
        if rows[0][4] != self.open_api.today:
            self.open_api.check_chegyul()                   # 매수 후 all_item_db에 저장되지 않은 종목 처리
            self.open_api.final_chegyul_check()             # 매도 후 all_item_db에 sell_date가 업데이트 되지 않은 항목 처리

        # 다음날 매수 종목 테이블(realtime_daily_buy_list) 업데이트
        if rows[0][6] != self.open_api.today:
            self.realtime_daily_buy_list_check()

        # min_craw db (분별 데이터) 업데이트
        if rows[0][8] != self.open_api.today:
            self.min_crawler_check()

        logger.debug("collecting 작업을 모두 정상적으로 마쳤습니다.")

        # cmd 콘솔창 종료
        sys.exit()

    # 매수 종목 설정 함수
    def realtime_daily_buy_list_check(self):
        # daily_buy_list 데이터베이스에 오늘 날짜의 테이블이 존재하는 경우, realtime_daily_buy_list 테이블 생성
        if self.open_api.sf.is_date_exist(self.open_api.today):
            self.open_api.sf.get_date_for_simul()
            self.open_api.sf.db_to_realtime_daily_buy_list(self.open_api.today, self.open_api.today, len(self.open_api.sf.date_rows))

            # all_stocks 테이블을 오늘 일자 데이터로 업데이트 한다.
            self.open_api.sf.update_all_db_by_date(self.open_api.today)
            self.open_api.rate_check()
            # realtime_daily_buy_list(매수 리스트) 테이블 세팅을 완료 후, setting_data의 today_buy_list에 오늘 날짜를 저장
            query = "UPDATE setting_data SET today_buy_list='%s' limit 1"
            self.engine_bot.execute(query % (self.open_api.today))

        else:
            logger.debug(
                """daily_buy_list DB에 {} 테이블이 없습니다. realtime_daily_buy_list 테이블을 생성 할 수 없습니다.
                아래 내역을 확인하세요.
                1. 장이 열리지 않은 날 혹은 15시 30분 ~ 23시 59분 사이에 콜렉터를 돌리지 않은 경우 
                2. 콜렉터를 오늘 날짜 까지 돌리지 않아 daily_buy_list의 오늘 날짜 테이블이 없는 경우
                """.format(self.open_api.today))

    # daily_buy_list 데이터베이스에 테이블이 존재하는지 확인하는 함수
    def is_table_exist_daily_buy_list(self, date):
        query = "select 1 from information_schema.tables where table_schema ='daily_buy_list' and table_name = '%s'"
        rows = self.open_api.engine_daily_buy_list.execute(query % (date)).fetchall()

        if len(rows) == 1:
            return True
        elif len(rows) == 0:
            return False

    # min_craw 데이터베이스에 테이블이 존재하는지 확인하는 함수
    def is_table_exist(self, db_name, table_name):
        query = "select 1 from information_schema.tables where table_schema ='{}' and table_name = '{}'"
        rows = self.open_api.engine_craw.execute(query.format(db_name, table_name)).fetchall()
        if len(rows) == 1:
            return True
        elif len(rows) == 0:
            return False

    # daily_buy_list 테이블 생성 및 데이터 저장하는 함수
    def daily_buy_list_check(self):
        self.dbl.daily_buy_list()

        query = "UPDATE setting_data SET daily_buy_list='%s' limit 1"
        self.engine_bot.execute(query % (self.open_api.today))

    # min_craw 데이터베이스 생성 함수
    def db_to_min_craw(self):
        query = "select code,code_name,check_min_crawler from stock_item_all"
        target_code = self.open_api.engine_daily_buy_list.execute(query).fetchall()
        num = len(target_code)

        query = "UPDATE stock_item_all SET check_min_crawler='%s' WHERE code='%s'"

        for i in range(num):
            # check_item이 0이 아니면 다음 항목 처리
            if int(target_code[i][2]) != 0:
                continue

            code = target_code[i][0]
            code_name = target_code[i][1]

            logger.debug("++++++++++++++" + str(code_name) + "++++++++++++++++++++" + str(i + 1) + '/' + str(num))

            check_item_gubun = self.set_min_crawler_table(code, code_name)

            self.open_api.engine_daily_buy_list.execute(query % (check_item_gubun, code))

    # 당일 daily_craw 데이터베이스의 업데이트 내역을 확인하고, 업데이트를 실행하는 함수
    def db_to_daily_craw(self):
        query = "select code,code_name,check_daily_crawler from stock_item_all"

        target_code = self.open_api.engine_daily_buy_list.execute(query).fetchall()
        num = len(target_code)
        query = "UPDATE stock_item_all SET check_daily_crawler='%s' WHERE code='%s'"

        # check_daily_crawler : daily_craw 데이터베이스를 업데이트 했는지 확인하는 변수
        # 1: 당일 업데이트 완료 / 3 : 과거에 업데이트 완료 / 0 : 업데이트 전 / 4 : daily_buy_list에 내용 변동을 업데이트 필요
        # check_daily_crawler이 1,3이 아닌 경우 업데이트 실행
        for i in range(num):
            if int(target_code[i][2]) in (1, 3):
                continue

            code = target_code[i][0]
            code_name = target_code[i][1]

            logger.debug("++++++++++++++" + str(code_name) + "++++++++++++++++++++" + str(i + 1) + '/' + str(num))

            check_item_gubun = self.set_daily_crawler_table(code, code_name)

            self.open_api.engine_daily_buy_list.execute(query % (check_item_gubun, code))

    # min_crawler 데이터베이스에 콜렉팅을 완료했는지 확인하는 함수
    def min_crawler_check(self):
        self.db_to_min_craw()

        query = "UPDATE setting_data SET min_crawler='%s' limit 1"
        self.engine_bot.execute(query % (self.open_api.today))

    # daily_crawler 데이터베이스에 콜렉팅을 완료했는지 확인하는 함수
    def daily_crawler_check(self):
        self.db_to_daily_craw()
        logger.debug("daily_crawler success !!!")

        sql = "UPDATE setting_data SET daily_crawler='%s' limit 1"
        self.engine_bot.execute(sql % (self.open_api.today))

    # 틱(1분 별) 데이터를 가져오는 함수
    def set_min_crawler_table(self, code, code_name):
        df = self.open_api.get_total_data_min(code, code_name, self.open_api.today)

        df_temp = DataFrame(df,
                            columns=['date', 'check_item', 'code', 'code_name', 'd1_diff_rate',
                                     'close', 'open', 'high','low','volume', 'sum_volume',
                                     'clo5', 'clo10', 'clo20', 'clo60','clo120',
                                     "clo5_diff_rate", "clo10_diff_rate","clo20_diff_rate", "clo60_diff_rate",
                                     "clo120_diff_rate",
                                     'yes_clo5', 'yes_clo10', 'yes_clo20', 'yes_clo60', 'yes_clo120',
                                     'vol5', 'vol10', 'vol20', 'vol60', 'vol120'
                                     ])

        df_temp = df_temp.sort_values(by=['date'], ascending=True)

        df_temp['code'] = code
        df_temp['code_name'] = code_name
        d1_diff_rate = round((df_temp['close'] - df_temp['close'].shift(1)) / df_temp['close'].shift(1) * 100, 2)
        df_temp['d1_diff_rate'] = d1_diff_rate.replace(numpy.inf, numpy.nan)

        clo5 = df_temp['close'].rolling(window=5).mean()
        clo10 = df_temp['close'].rolling(window=10).mean()
        clo20 = df_temp['close'].rolling(window=20).mean()
        clo60 = df_temp['close'].rolling(window=60).mean()
        clo120 = df_temp['close'].rolling(window=120).mean()
        df_temp['clo5'] = round(clo5, 2)
        df_temp['clo10'] = round(clo10, 2)
        df_temp['clo20'] = round(clo20, 2)
        df_temp['clo60'] = round(clo60, 2)
        df_temp['clo120'] = round(clo120, 2)

        df_temp['clo5_diff_rate'] = round((df_temp['close'] - clo5) / clo5 * 100, 2)
        df_temp['clo10_diff_rate'] = round((df_temp['close'] - clo10) / clo10 * 100, 2)
        df_temp['clo20_diff_rate'] = round((df_temp['close'] - clo20) / clo20 * 100, 2)
        df_temp['clo60_diff_rate'] = round((df_temp['close'] - clo60) / clo60 * 100, 2)
        df_temp['clo120_diff_rate'] = round((df_temp['close'] - clo120) / clo120 * 100, 2)

        df_temp['yes_clo5'] = df_temp['clo5'].shift(1)
        df_temp['yes_clo10'] = df_temp['clo10'].shift(1)
        df_temp['yes_clo20'] = df_temp['clo20'].shift(1)
        df_temp['yes_clo60'] = df_temp['clo60'].shift(1)
        df_temp['yes_clo120'] = df_temp['clo120'].shift(1)

        df_temp['vol5'] = df_temp['volume'].rolling(window=5).mean()
        df_temp['vol10'] = df_temp['volume'].rolling(window=10).mean()
        df_temp['vol20'] = df_temp['volume'].rolling(window=20).mean()
        df_temp['vol60'] = df_temp['volume'].rolling(window=60).mean()
        df_temp['vol120'] = df_temp['volume'].rolling(window=120).mean()

        # 분별 테이블이 존재한다면, 가장 최근의 분 데이터 이후의 값을 저장
        if self.open_api.craw_table_exist:
            df_temp = df_temp[df_temp.date > self.open_api.craw_db_last_min]

        # 추가할 내역이 없다면, check_item_gubun=3
        if len(df_temp) == 0:
            time.sleep(0.03)
            check_item_gubun = 3
            return check_item_gubun

        df_temp[['close', 'open', 'high', 'low', 'volume', 'sum_volume', 'clo5', 'clo10', 'clo20', 'clo60','clo120',
                 'yes_clo5', 'yes_clo10', 'yes_clo20', 'yes_clo60','yes_clo120',
                 'vol5', 'vol10', 'vol20', 'vol60', 'vol120']] = \
            df_temp[
                ['close', 'open', 'high', 'low', 'volume', 'sum_volume', 'clo5', 'clo10', 'clo20', 'clo60', 'clo120',
                 'yes_clo5', 'yes_clo10', 'yes_clo20','yes_clo60', 'yes_clo120',
                 'vol5', 'vol10', 'vol20', 'vol60', 'vol120']].fillna(0).astype(int)

        temp_date = self.open_api.craw_db_last_min
        sum_volume = self.open_api.craw_db_last_min_sum_volume

        for i in range(len(df_temp),-1,-1):
            try:
                temp_index = i

                # 데이터가 하루 이상 차이날 경우, sum_volume 초기화
                if ((int(df_temp.loc[temp_index, 'date']) - int(temp_date)) > 9000):
                    sum_volume = 0

                temp_date = df_temp.loc[temp_index, 'date']
                # 분별로 sum_volume값 누적 저장
                sum_volume += df_temp.loc[temp_index, 'volume']
                df_temp.loc[temp_index, 'sum_volume'] = sum_volume

            except Exception as e:
                logger.critical(e)

        df_temp.to_sql(name=code_name, con=self.open_api.engine_craw, if_exists='append')
        # 콜렉팅하다가 max_api_call 횟수까지 가게 된 경우
        # 이후 콜렉팅 하지 못한 정보를 가져오기 위해 check_item_gubun=0
        if self.open_api.rq_count == self.open_api.cf.max_api_call - 1:
            check_item_gubun = 0
        # 정상완료한 경우 check_item_gubun=1
        else:
            check_item_gubun = 1
        return check_item_gubun

    # daily_crawler(종목별 일일 데이터) 테이블 생성 및 내역을 추가하는 함수
    # daily_crawler 테이블 업데이트 후, daily_buy_list(일별 종목 데이터) 테이블 생성 및 내역 추가
    def set_daily_crawler_table(self, code, code_name):
        df = self.open_api.get_total_data(code, code_name, self.open_api.today)
        oldest_row = df.iloc[-1]        # 가장 최신 데이터
        check_row = None

        check_dc_query = "UPDATE daily_buy_list.stock_item_all SET check_daily_crawler = '4' WHERE code = '{}'"

        # 특정 종목명(code_name)의 테이블이 존재하는 경우
        if self.engine_bot.dialect.has_table(self.open_api.engine_daily_craw, code_name):
            query=f"select * from `{code_name}` where date='{oldest_row['date']}' limit 1"
            check_row = self.open_api.engine_daily_craw.execute(query).fetchall()
        # 종목명 테이블이 존재하지 않는 경우, 종목 리스트(stock_item_all) 테이블에 check_daily_crawler=4 업데이트
        else:
            self.engine_bot.execute(check_dc_query.format(code))

        # 종목 테이블에 저장된 가장 최신의 데이터가 실제 가장 최신 데이터와 같지 않다면 다시 테이블 삭제 후 다시 생성
        if check_row and check_row[0]['close'] != oldest_row['close']:
            # 테이블 삭제
            logger.info('daily_craw와 min_craw 삭제 중..')
            commands = [
                f"DROP TABLE IF EXISTS daily_craw.'{code_name}'",
                f"DROP TABLE IF EXISTS min_craw.'{code_name}"
            ]

            for com in commands:
                self.open_api.engine_daily_buy_list.execute(com)
            logger.info('삭제 완료')

            # 테이블 생성
            df = self.open_api.get_total_data(code, code_name, self.open_api.today)
            self.engine_bot.execute(check_dc_query.format(code))

        query=f"select check_daily_crawler from daily_buy_list.stock_item_all where code='{code}'"
        check_daily_crawler = self.engine_bot.execute(query).fetchall()[0].check_daily_crawler

        df_temp = DataFrame(df,
                            columns=['date', 'check_item', 'code', 'code_name', 'd1_diff_rate',
                                     'close', 'open', 'high','low','volume',
                                     'clo5', 'clo10', 'clo20', 'clo60', 'clo120',
                                     "clo5_diff_rate", "clo10_diff_rate","clo20_diff_rate", "clo60_diff_rate",
                                     "clo120_diff_rate",
                                     'yes_clo5', 'yes_clo10', 'yes_clo20', 'yes_clo60', 'yes_clo120',
                                     'vol5', 'vol10', 'vol20', 'vol60', 'vol120',
                                     'd1_diff','dn_diff'
                                     ])

        df_temp = df_temp.sort_values(by=['date'], ascending=True)

        df_temp['code'] = code
        df_temp['code_name'] = code_name
        df_temp['d1_diff_rate'] = round(
            (df_temp['close'] - df_temp['close'].shift(1)) / df_temp['close'].shift(1) * 100, 2)

        clo5 = df_temp['close'].rolling(window=5).mean()
        clo10 = df_temp['close'].rolling(window=10).mean()
        clo20 = df_temp['close'].rolling(window=20).mean()
        clo60 = df_temp['close'].rolling(window=60).mean()
        clo120 = df_temp['close'].rolling(window=120).mean()
        df_temp['clo5'] = clo5
        df_temp['clo10'] = clo10
        df_temp['clo20'] = clo20
        df_temp['clo60'] = clo60
        df_temp['clo120'] = clo120

        df_temp['clo5_diff_rate'] = round((df_temp['close'] - clo5) / clo5 * 100, 2)
        df_temp['clo10_diff_rate'] = round((df_temp['close'] - clo10) / clo10 * 100, 2)
        df_temp['clo20_diff_rate'] = round((df_temp['close'] - clo20) / clo20 * 100, 2)
        df_temp['clo60_diff_rate'] = round((df_temp['close'] - clo60) / clo60 * 100, 2)
        df_temp['clo120_diff_rate'] = round((df_temp['close'] - clo120) / clo120 * 100, 2)

        df_temp['yes_clo5'] = df_temp['clo5'].shift(1)
        df_temp['yes_clo10'] = df_temp['clo10'].shift(1)
        df_temp['yes_clo20'] = df_temp['clo20'].shift(1)
        df_temp['yes_clo60'] = df_temp['clo60'].shift(1)
        df_temp['yes_clo120'] = df_temp['clo120'].shift(1)

        df_temp['vol5'] = df_temp['volume'].rolling(window=5).mean()
        df_temp['vol10'] = df_temp['volume'].rolling(window=10).mean()
        df_temp['vol20'] = df_temp['volume'].rolling(window=20).mean()
        df_temp['vol60'] = df_temp['volume'].rolling(window=60).mean()
        df_temp['vol120'] = df_temp['volume'].rolling(window=120).mean()

        df_temp['d1_diff']=df_temp['close']-df_temp['close'].shift(1)
        df_temp['dn_diff']=df_temp['close']-df_temp['close'].shift(14)

        # daily_craw테이블이 존재할 경우, 저장되어있는 날짜 이후의 값을 저장
        if self.engine_bot.dialect.has_table(self.open_api.engine_daily_craw, code_name):
            df_temp = df_temp[df_temp.date > self.open_api.get_daily_craw_db_last_date(code_name)]

        # 데이터가 없거나 이미 데이터 콜렉팅을 완료한 경우, check_item_gubun=3으로 설정
        if len(df_temp) == 0 and check_daily_crawler != '4':
            time.sleep(0.03)
            check_item_gubun = 3
            return check_item_gubun

        df_temp[['close', 'open', 'high', 'low', 'volume', 'clo5', 'clo10', 'clo20', 'clo60','clo120',
                 'yes_clo5', 'yes_clo10', 'yes_clo20','yes_clo60', 'yes_clo120',
                 'vol5', 'vol10', 'vol20','vol60','vol120']] = \
            df_temp[
                ['close', 'open', 'high', 'low', 'volume', 'clo5', 'clo10', 'clo20','clo60','clo120',
                 'yes_clo5', 'yes_clo10', 'yes_clo20', 'yes_clo60','yes_clo120',
                 'vol5', 'vol10', 'vol20', 'vol60', 'vol120']].fillna(0).astype(int)

        df_temp.to_sql(name=code_name, con=self.open_api.engine_daily_craw, if_exists='append')

        # check_daily_crawler 가 4 인 경우는 액면분할, 증자 등으로 인해 daily_buy_list 업데이트를 해야하는 경우
        # 업데이트 완료 후 check_item_gubun=1
        if check_daily_crawler == '4':
            logger.info(f'daily_craw.{code_name} 업데이트 완료 {code}')
            logger.info('daily_buy_list 업데이트 중..')

            query="SELECT table_name as tname FROM information_schema.tables " \
                  "WHERE table_schema ='daily_buy_list' AND table_name REGEXP '[0-9]{8}'"
            dbl_dates = self.open_api.engine_daily_buy_list.execute(query).fetchall()

            for row in dbl_dates:
                logger.info(f'{code} {code_name} - daily_buy_list.`{row.tname}` 업데이트')
                try:
                    new_data = df_temp[df_temp['date'] == row.tname]
                except IndexError:
                    continue
                query=f"delete from '{row.tname}' where code={code}"
                self.open_api.engine_daily_buy_list.execute(query)
                new_data.to_sql(name=row.tname, con=self.open_api.engine_daily_buy_list, if_exists='append')

            logger.info('daily_buy_list 업데이트 완료')

        check_item_gubun = 1
        return check_item_gubun

    # today_profit_list 테이블 생성 및 내역 추가 함수
    def db_to_today_profit_list(self):
        self.open_api.reset_opt10073_output()

        self.open_api.set_input_value("계좌번호", self.open_api.account_no)
        self.open_api.set_input_value("시작일자", self.open_api.today)
        self.open_api.set_input_value("종료일자", self.open_api.today)

        self.open_api.comm_rq_data("opt10073_req", "opt10073", 0, "0328")

        while self.open_api.remained_data:
            self.open_api.set_input_value("계좌번호", self.open_api.account_no)
            self.open_api.comm_rq_data("opt10073_req", "opt10073", 2, "0328")

        today_profit_item_temp = {'date': [], 'code': [], 'code_name': [], 'amount': [], 'today_profit': [],
                                  'earning_rate': []}

        today_profit_item = DataFrame(today_profit_item_temp,
                                      columns=['date', 'code', 'code_name', 'amount', 'today_profit',
                                               'earning_rate'])

        item_count = len(self.open_api.opt10073_output['multi'])
        for i in range(item_count):
            row = self.open_api.opt10073_output['multi'][i]
            today_profit_item.loc[i, 'date'] = row[0]                       # 날짜
            today_profit_item.loc[i, 'code'] = row[1]                       # 종목코드
            today_profit_item.loc[i, 'code_name'] = row[2]                  # 종목명
            today_profit_item.loc[i, 'amount'] = int(row[3])                # 보유수량
            today_profit_item.loc[i, 'today_profit'] = float(row[4])        # 당일실현손익
            today_profit_item.loc[i, 'earning_rate'] = float(row[5])        # 수익률

        if len(today_profit_item) > 0:
            today_profit_item.to_sql('today_profit_list', self.engine_bot, if_exists='append')
        query = "UPDATE setting_data SET today_profit='%s' limit 1"
        self.engine_bot.execute(query % (self.open_api.today))

    # jango 테이블 생성 및 내역 추가 함수
    def db_to_jango(self):
        self.total_invest = self.open_api.change_format(
            str(int(self.open_api.d2_deposit_before_format) + int(self.open_api.total_purchase_price)))
        jango_temp = {'id': [], 'date': [], 'total_asset': [], 'today_profit': [], 'total_profit': [],
                      'total_invest': [], 'd2_deposit': [],
                      'today_purchase': [], 'today_evaluation': [],
                      'today_invest': [], 'today_rate': [],
                      'estimate_asset': []}

        jango_col_list = ['date',
                          'today_earning_rate','total_evaluation',
                          'total_profit', 'total_invest',
                          'total_valuation','total_purchase','total_rate','today_profit','estimate_asset','d2_deposit',
                          'volume_limit','sell_point','invest_limit_rate', 'invest_unit','limit_money',
                          'total_profitcut','total_losscut','total_profitcut_count', 'total_losscut_count',
                          'today_buy_count','total_sell_count','total_possess_count']

        jango = DataFrame(jango_temp,
                          columns=jango_col_list,
                          index=jango_temp['id'])

        jango.loc[0, 'date'] = self.open_api.today                                              # 날짜
        jango.loc[0, 'total_evaluation'] = self.open_api.change_total_eval_price                # 총평가금액
        jango.loc[0, 'total_profit'] = self.open_api.total_profit                               # 실현손익
        jango.loc[0, 'total_invest'] = self.total_invest                                        # 총투자금액
        jango.loc[0, 'total_valuation'] = self.open_api.change_total_eval_profit_loss_price     # 총평가손익금액
        jango.loc[0, 'total_purchase'] = self.open_api.change_total_purchase_price              # 총매입금액

        jango.loc[0, 'total_rate'] = float(self.open_api.change_total_earning_rate) / self.open_api.mod_gubun   # 총수익률
        jango.loc[0, 'today_profit'] = self.open_api.today_profit                               # 당일매도손익
        jango.loc[0, 'estimate_asset'] = self.open_api.change_estimated_deposit                 # 추정예탁자산
        jango.loc[0, 'd2_deposit'] = self.open_api.d2_deposit                                   # 예수금
        jango.loc[0, 'volume_limit'] = self.open_api.sf.volume_limit

        jango.loc[0, 'sell_point'] = self.open_api.sf.sell_point                                # 매도기준수익률
        jango.loc[0, 'invest_limit_rate'] = self.open_api.sf.invest_limit_rate                  # 매수기준수익률
        jango.loc[0, 'invest_unit'] = self.open_api.invest_unit                                 # 투자기준금액
        jango.loc[0, 'limit_money'] = self.open_api.sf.limit_money                              # 잔고에 남겨둘 최소금액

        # 당일 익절 금액(today_profitcut), 당일 손절 금액(today_losscut)
        if self.is_table_exist(self.open_api.db_name, "today_profit_list"):
            query = "select sum(today_profit) from today_profit_list where today_profit >='%s' and date = '%s'"
            rows = self.engine_bot.execute(query % (0, self.open_api.today)).fetchall()
            if rows[0][0] is not None:
                jango.loc[0, 'total_profitcut'] = int(rows[0][0])
            else:
                jango.loc[0,'total_profitcut']=0

            query = "select sum(today_profit) from today_profit_list where today_profit < '%s' and date = '%s'"
            rows = self.engine_bot.execute(query % (0, self.open_api.today)).fetchall()
            if rows[0][0] is not None:
                jango.loc[0, 'total_losscut'] = int(rows[0][0])
            else:
                jango.loc[0, 'total_losscut'] = 0

        # 총 익절 종목 수(total_profitcut_count)
        query = "select count(*) " \
                "from (select code from all_stocks where sell_rate >='%s' and sell_date like '%s' group by code) temp"
        rows = self.engine_bot.execute(query % (0, self.open_api.today + "%%")).fetchall()
        jango.loc[0, 'total_profitcut_count'] = int(rows[0][0])

        # 총 손절 종목 수(total_losscut_count)
        query = "select count(*) " \
                "from (select code from all_stocks where sell_rate < '%s' and sell_date like '%s' group by code) temp"
        rows = self.engine_bot.execute(query % (0, self.open_api.today + "%%")).fetchall()
        jango.loc[0, 'total_losscut_count'] = int(rows[0][0])

        # jango_data 테이블 생성 및 데이터 추가
        jango.to_sql('jango_data', self.engine_bot, if_exists='append')

        query = "select date from jango_data"
        rows = self.engine_bot.execute(query).fetchall()

        for i in range(len(rows)):
            # 당일 수익률 (today_earning_rate)
            query = "update jango_data set " \
                    "today_earning_rate =round(today_profit / total_invest  * '%s',2) WHERE date='%s'"
            self.engine_bot.execute(query % (100, rows[i][0]))

            # 당일 구매 종목 수 (today_buy_count)
            query = "UPDATE jango_data SET " \
                    "today_buy_count=" \
                    "(select count(*) from (select code from all_stocks where buy_date like '%s' group by code) temp) " \
                    "WHERE date='%s'"
            self.engine_bot.execute(query % (rows[i][0] + "%%", rows[i][0]))

            # 총 매수 종목 수 (total_sell_count)
            query = "UPDATE jango_data SET " \
                    "total_sell_count=" \
                    "(select count(*) from " \
                    "(select code from all_stocks a where sell_date!='0' group by code) temp)"\
                    "WHERE date='%s'"
            self.engine_bot.execute(query % (rows[i][0]))

            # 총 보유 종목 수 (total_possess_count)
            query = "UPDATE jango_data SET " \
                    "total_possess_count=" \
                    "(select count(*) from (select code from all_stocks where sell_date = '0' group by code ) temp) " \
                    "WHERE date='%s'"
            self.engine_bot.execute(query % (rows[i][0]))

        query = "UPDATE setting_data SET jango_data_db_check='%s' limit 1"
        self.engine_bot.execute(query % (self.open_api.today))

    # 계좌정보 확인 함수
    def py_check_balance(self):
        logger.debug("py_check_balance!!!")

        # 예수금상세현황
        self.open_api.reset_opw00018_output()

        self.open_api.set_input_value("계좌번호", self.open_api.account_no)
        self.open_api.set_input_value("비밀번호입력매체구분", 00)
        self.open_api.set_input_value("조회구분", 1)
        self.open_api.comm_rq_data("opw00001_req", "opw00001", 0, "2000")

        # 계좌평가잔고내역
        self.open_api.set_input_value("계좌번호", self.open_api.account_no)
        self.open_api.comm_rq_data("opw00018_req", "opw00018", 0, "2000")

        while self.open_api.remained_data:
            self.open_api.set_input_value("계좌번호", self.open_api.account_no)
            self.open_api.comm_rq_data("opw00018_req", "opw00018", 2, "2000")

        # 일자별실현손익
        self.open_api.set_input_value("계좌번호", self.open_api.account_no)
        self.open_api.set_input_value("시작일자", "20170101")
        self.open_api.set_input_value("종료일자", self.open_api.today)

        self.open_api.comm_rq_data("opt10074_req", "opt10074", 0, "0329")
        while self.open_api.remained_data:
            self.open_api.set_input_value("계좌번호", self.open_api.account_no)
            self.open_api.set_input_value("시작일자", "20170101")
            self.open_api.set_input_value("종료일자", "20180930")

            # 	구분 = 0:전체, 1:입출금, 2:입출고, 3:매매, 4:매수, 5:매도, 6:입금, 7:출금, A:예탁담보대출입금, F:환전
            self.open_api.set_input_value("구분", "0")
            self.open_api.comm_rq_data("opt10074_req", "opt10074", 2, "0329")

        self.db_to_jango()

    def get_code_list(self):
        self.dc.get_item_kospi()
        self.stock_to_db(self.dc.code_df_kospi,"item_all")

    def stock_to_db(self,origin_df,type):
        checking_stocks=['kospi']
        stock_df=DataFrame()
        stock_df['code']=origin_df['code']
        name_list=[]
        for info in origin_df.itertuples():
            kiwoom_name = self.open_api.dynamicCall("GetMasterCodeName(QString)", info.code).strip()
            name_list.append(kiwoom_name)
            if not kiwoom_name:
                if type in checking_stocks:
                    logger.error(
                        f"종목명이 비어있습니다. - "
                        f"종목: {info.code_name}, "
                        f"코드: {info.code}"
                    )

        stock_df['code_name']=name_list
        stock_df['check_item']=0

        if type in checking_stocks:
            stock_df=stock_df[stock_df['code_name'].map(len)>0]

        if type=="item_all":
            stock_df['check_daily_crawler']='0'
            stock_df['check_min_crawler']='0'

        dtypes=dict(zip(list(stock_df.columns),[Text]*len(stock_df.columns)))
        dtypes['check_item']=Integer

        stock_df.to_sql(f'stock_{type}',self.open_api.engine_daily_buy_list,if_exists='replace',dtype=dtypes)
        return stock_df