import socket
import time
import random
import threading
import configparser
import os
import re
import shutil
import json
import numpy as np
import xlrd
import xlwt
from xlutils.copy import copy
from xlutils.filter import process,XLRDReader,XLWTWriter
import openpyxl
import logging

logging.basicConfig(
    filename = os.path.join(os.path.join(os.getcwd(), 'logs'), 'lczg.log'),
    level = logging.DEBUG,
    format = "[%(asctime)s] - %(levelname)s - %(lineno)s] %(message)s",
    datefmt = "%Y-%m-%d %H:%M:%S"
)

def copy2(wb):
    w = XLWTWriter()
    process(
        XLRDReader(wb,'unknown.xls'),
        w
        )
    return w.output[0][1], w.style_list

'''
    获取产品列表
'''
def get_prd_list(cur, value_date):
    sql = '''SELECT 
        to_char(t2.val_date, 'yyyy-mm-dd') AS VALDATE,
        T0.BOOKSET_ID AS FINPRODID,
        T0.BOOKSET_NAME AS FINPRODNAME,
        decode(T0.ACCT_MODE, '01', '净值', '其他') AS FINPRODTYPE
        from BOK_BOOKSET T0
        inner JOIN BOK_VALBAL_TABLE_LOG T2
            ON T0.ACCT_SUBJECT_ID = T2.FINPROD_ID
        AND T2.VAL_BAL_FLAG = '01'
        where 1 = 1
        AND T0.ACCT_MODE in ('01')
        and t2.val_date = to_date('{}', 'yyyy-mm-dd')
        order by T0.BOOKSET_NAME desc'''.format(value_date)
    cur.execute(sql)
    res = cur.fetchall()
    return res

'''
获取某个产品某日的估值表信息
'''
def get_value_table(cur, prd_no, value_date, trade_list):
    jinrong = get_trade_by_type(trade_list, 'JINRONG')
    qiye = get_trade_by_type(trade_list, 'QIYE')
    zichan = get_trade_by_type(trade_list, 'ZICHAN')
    sql = '''
    select * 
    from bok_val_table_data a
    where a.bookset_id = '{}'
    and a.val_date = to_date('{}', 'yyyy-mm-dd')
    order by detail_dist asc, subject_no asc
    '''.format(prd_no, value_date)
    logging.debug(sql)
    cur.execute(sql)
    detail_list = cur.fetchall()
    prd_name = detail_list[0][2]
    logging.debug('prd_name:{}'.format(prd_name))
    keys = ['seq_no', 'bookset_id', 'bookset_name', 'profit_type', 'val_date',
    'detail_dist', 'layering_id', 'subject_no', 'subject_name', 'fsubject_id', 'num_amt',
    'unit_cost', 'cost', 'cost_percent', 'close_price', 'market_value', 'value_percent',
    'value_increment', 'bal_flag', 'shadow_price_value', 'market_val_date', 'create_user', 'create_dept',
    'create_time', 'update_user', 'update_time', 'o_ccy', 'exchange_rate', 'o_cost', 'o_market_value']
    value_table = [dict(zip(keys, x)) for x in detail_list]
    
    # M列资产类合计
    A0001 = [x['market_value'] for x in value_table if x['subject_no'] == '资产类合计：']
    A0001 = np.sum(A0001)
    logging.debug('A0001:{}'.format(A0001))
    
    # M列1002科目+M列1031科目；如果无M列1031科目，只取M列1002科目。
    A2000 = [x['market_value'] for x in value_table if x['subject_no'] in ['1002', '1031']]
    A2000 = np.sum(A2000)
    logging.debug('A2000:{}'.format(A2000))
    
    # 同存款
    A2100 = A2000
    
    # 债券类型为金融债 并且 H列1103科目下的三级科目+H列1204科目下的三级科目
    A4400 = [x['o_cost'] for x in value_table if x['subject_no'].startswith('1103') and x['subject_no'].endswith(jinrong)]
    A4400 = np.sum(A4400)
    logging.debug('A4400:{}'.format((A4400))

    # 债券类型为企业债券 并且 H列1103科目下的三级科目+H列1204科目下的三级科目
    A4500 = [x['o_cost'] for x in value_table if x['subject_no'].startswith('1103') and x['subject_no'].endswith(qiye)]
    A4500 = np.sum(A4500)
    logging.debug('A4500:{}'.format(A4500))

    # 债券类型为资产支持证券 并且 H列1103科目下的三级科目+H列1204科目下的三级科目
    A4700 = [x['o_cost'] for x in value_table if x['subject_no'].startswith('1103') and x['subject_no'].endswith(zichan)]
    A4700 = np.sum(A4700)
    logging.debug('A4700:{}'.format(A4700))

    # I列110343科目+11034399科目+12040543科目(等于A4400+A4500+A4700)
    A4000 = A4400 + A4500 + A4700
    logging.debug('A4000:{}'.format(A4000))

    # M列1106科目+M列1204科目；必须1106+1204同时出现才统计
    has_1106_1204 = [x['subject_no'] for x in value_table if x['subject_no'] in ['1106', '1204']]
    logging.debug('has_1106:{}'.format(has_1106_1204))
    A5000 = 0.0
    if '1106' in has_1106_1204 and '1204' in has_1106_1204:
        A5000 = np.sum([x['market_value'] for x in value_table if x['subject_no'] in ['1106', '1204']])
    logging.debug('A5000:{}'.format(A5000))

    # 同贷款_A5000
    A5100 = A5000

    # M列1110科目+1203科目；必须1110+1203科目同时出现才统计
    has_1110_1203 = [x['subject_no'] for x in value_table if x['subject_no'] in ['1110', '1203']]
    logging.debug('has_1110_1203:{}'.format(has_1110_1203))
    A7000 = 0.0
    if '1110' in has_1110_1203 and '1203' in has_1110_1203:
        A7000 = np.sum([x['market_value'] for x in value_table if x['subject_no'] in ['1110', '1203']])
    logging.debug('A7000:{}'.format(A7000))

    # 同股权及特定目的载体份额_A7000 
    A7200 = A7000

    # A7240
    A7240 = A7000

    # M列1204科目
    A9000 = [x['market_value'] for x in value_table if x['subject_no'] == '1204']
    logging.debug('A9000:{}'.format(A9000))
    A9000 = np.sum(A9000)
    if prd_name.find('2020年') >= 0:
        A9000 = 0.0
    logging.debug('A9000:{}'.format(A9000))

    D0000 = A0001
    logging.debug('D0000:{}'.format(D0000))

    B0000 = [x['market_value'] for x in value_table if x['subject_no'] == '负债类合计：']
    logging.debug('B0000:{}'.format(B0000))
    B0000 = np.sum(B0000)
    logging.debug('B0000:{}'.format(B0000))

    B4000 = B0000
    logging.debug('B4000:{}'.format(B4000))

    # M列产品资产净值-费前
    C0000 = [x['market_value'] for x in value_table if x['subject_no'] == '产品资产净值-费前：']
    logging.debug('C0000:{}'.format(C0000))
    C0000 = np.sum(C0000)
    logging.debug('C0000:{}'.format(C0000))

    # F列实收资本
    C1000 = [x['num_amt'] for x in value_table if x['subject_no'] == '实收资本：']
    logging.debug('C1000:{}'.format(C1000))
    C1000 = np.sum(C1000) or 0
    logging.debug('C1000:{}'.format(C1000))

    C1200 = C1000
    logging.debug('C1200:{}'.format(C1200))

    C1210  = C1000
    logging.debug('C1210:{}'.format(C1210))

    # M列产品资产净值-费前减去F列实收资本
    C3000 = round(C0000 - C1000, 2)
    logging.debug('C3000:{}'.format(C3000))
    return {
        'A0001': A0001,
        'A2000': A2000,
        'A2100': A2100,
        'A4000': A4000,
        'A4400': A4400,
        'A4500': A4500,
        'A4700': A4700,
        'A5000': A5000,
        'A5100': A5100,
        'A7000': A7000,
        'A7200': A7200,
        'A7240': A7240,
        'A9000': A9000,
        'D0000': D0000,
        'B0000': B0000,
        'B4000': B4000,
        'C0000': C0000,
        'C1000': C1000,
        'C1200': C1200,
        'C1210': C1210,
        'C3000': C3000
    }

def generate_excel(data_list, val_date, output):
    logging.debug(output)
    template = os.path.join(os.path.join(os.getcwd(), 'res'), 'template.xls')
    logging.debug(template)
    rdbook = xlrd.open_workbook(template, formatting_info=True)
    rdsheet = rdbook.sheet_by_index(0)
    wtbook, style_list = copy2(rdbook)
    wtsheet = wtbook.get_sheet(0)
    #xf_index = rdsheet.cell_xf_index(0, 0)
    wtsheet.write(0, 0, val_date + '人行数据提取模板')
    for i in range(len(data_list)):
        data = data_list[i]
        wtsheet.write(3+i, 0, data['prd_name'])
        wtsheet.write(3+i, 1, data['prod_values']['A0001'])
        wtsheet.write(3+i, 2, data['prod_values']['A2000'])
        wtsheet.write(3+i, 3, data['prod_values']['A2100'])
        wtsheet.write(3+i, 4, data['prod_values']['A4000'])
        wtsheet.write(3+i, 5, data['prod_values']['A4400'])
        wtsheet.write(3+i, 6, data['prod_values']['A4500'])
        wtsheet.write(3+i, 7, data['prod_values']['A4700'])
        wtsheet.write(3+i, 8, data['prod_values']['A5000'])
        wtsheet.write(3+i, 9, data['prod_values']['A5100'])
        wtsheet.write(3+i, 10, data['prod_values']['A7000'])
        wtsheet.write(3+i, 11, data['prod_values']['A7200'])
        wtsheet.write(3+i, 12, data['prod_values']['A7240'])
        wtsheet.write(3+i, 13, data['prod_values']['A9000'])
        wtsheet.write(3+i, 14, data['prod_values']['D0000'])
        wtsheet.write(3+i, 15, data['prod_values']['B0000'])
        wtsheet.write(3+i, 16, data['prod_values']['B4000'])
        wtsheet.write(3+i, 17, data['prod_values']['C0000'])
        wtsheet.write(3+i, 18, data['prod_values']['C1000'])
        wtsheet.write(3+i, 19, data['prod_values']['C1200'])
        wtsheet.write(3+i, 20, data['prod_values']['C1210'])
        wtsheet.write(3+i, 21, data['prod_values']['C3000'])
    wtbook.save(output)


def get_trade_by_type(trade_list, typecode):
    if typecode == 'JINRONG':
        return tuple([x['trade_code'] for x in filter(lambda x: x['trade_type'] == '金融债', trade_list)])
    elif typecode == 'QIYE':
        return tuple([x['trade_code'] for x in filter(lambda x: x['trade_type'] == '企业债券', trade_list)])
    elif typecode == 'ZICHAN':
        return tuple([x['trade_code'] for x in filter(lambda x: x['trade_type'] == '资产支持证券', trade_list)])
    else:
        return ()

def read_excel(path):
    trade_list = []
    if path.endswith('.xls'):
        book = xlrd.open_workbook(path)
        sheet = book.sheets()[0]
        for rownum in range(sheet.nrows):
            if rownum == 0:continue
            trade_code = sheet.cell_value(rownum, 3)
            trade_type = sheet.cell_value(rownum, 2)
            trade_name = sheet.cell_value(rownum, 1)
            logging.debug('trade_code:{}, trade_type:{}'.format(trade_code, trade_type))
            trade_list.append({
                'trade_code': trade_code,
                'trade_name': trade_name,
                'trade_type': trade_type
            })
    elif path.endswith('.xlsx'):
        book = openpyxl.load_workbook(path)
        sheet = book.worksheets[0]
        rows = [x for x in sheet.rows]
        for row in range(len(rows)):
            if row == 0:continue
            trade_code = rows[row][3].value
            trade_type = rows[row][2].value
            trade_name = rows[row][1].value
            trade_list.append({
                'trade_code': trade_code,
                'trade_name': trade_name,
                'trade_type': trade_type
            })
    return trade_list