(D-3-4) auditReportGAAP

# -*- coding: utf-8 -*-
"""
Created on Thu Aug 13 15:19:08 2020

@author: user
"""

from bs4 import BeautifulSoup
import os
import glob
import pandas as pd
import numpy as np

def col_span_count(soup):
    try:
        result = int(soup["colspan"])
    except KeyError:
        result= 1
    return result

def row_span_count(soup):
    try:
        result = int(soup["rowspan"])
    except KeyError:
        result= 1
    return result

def FindTargetTable(soup):
    tables = soup.find_all("table")
    for i in tables:
        tds = i.find_all("td")
        if len(tds) > 20:  # 시간 파트가 약 80정도 된다. 비교 표시 없는 경우 td가 50도 안된다. 첫번째 나오는 20 초과 table 선택
            break
    return i

def MatrixGenerator(table):
    table_row = table.find_all("tr")

    columnCount = 0
    for i in table_row:
        columnNumber = 0
        for j in i.find_all(["th", "td"]):
            try:
                columnNumber += int(j["colspan"])
            except KeyError:
                columnNumber += 1
            if columnNumber > columnCount:
                columnCount = columnNumber        
    rowCount = len(table_row)
    matrix = [['#' for x in range(columnCount)] for y in range(rowCount)] 
    for i in range(len(table_row)):
        locator = [i for i, x in enumerate(matrix[i]) if x=='#']  # https://stackoverflow.com/questions/9542738/python-find-in-list
        column, colSpan = 0, 0
        for j in table_row[i].find_all(["th", "td"]):
            rowSpanCount = row_span_count(j)
            colSpanCount = col_span_count(j)

            for k in range(rowSpanCount):
                for l in range(colSpanCount):
                    row = i + k
                    column = locator[l+colSpan]
                    matrix[row][column] = j.text.strip()
            colSpan += col_span_count(j)
    return matrix

def ParsingTime(matrix, isComparative, tableLength, file, report):
    if isComparative:
        container = []
        tableLength = int(tableLength / 2)
        for i in range(tableLength):
            container.append(file + "_" + matrix[report][1] + "_" + matrix[1][2+2*i] + "_" + matrix[report][2+2*i].replace('-', '0').replace(',','') + "\n")
    else:
        container = []
        tableLength = int(tableLength)
        for i in range(tableLength):
            container(file + "_" + matrix[report][1] + "_" + matrix[1][2+i] + "_" + matrix[report][2+i].replace('-', '0').replace(',','') + "\n")
    return container

def Indexing(matrix):
    """
    당기, 전기 부분 삭제한 경우들이 있어서 찾아야함
    """
    container = []
    for i in matrix:
        try:
            i[0:2].index("투입 인원수")
            container.append(matrix.index(i))
        except ValueError:
            pass
        try:
            i[0:2].index("분ㆍ반기검토")
            container.append(matrix.index(i))
        except ValueError:
            pass
        try:
            i[0:2].index("감사")
            container.append(matrix.index(i))
        except ValueError:
            pass
        try:
            i[0:2].index("합계")
            container.append(matrix.index(i))
        except ValueError:
            pass
    return container

# 1. 작업 폴더로 변경
os.chdir("C:\data\\")  # 작업 폴더로 변경

# 2. 타겟 폴더에 있는 필요 문서 경로 리스트업
pathList = []
for path in [".\A001_2017\\", ".\A001_2018\\",
              ".\A001_2019\\", ".\A001_2020\\",
              ".\F001_2017\\", ".\F001_2018\\",
              ".\F001_2019\\", ".\F001_2020\\",
              ".\F002_2017\\", ".\F002_2018\\",
              ".\F002_2019\\", ".\F002_2020\\"              
              ]:
    path = path + "*감사인의감사보고서*.*"  # 필요한 Keyword 입력
    pathInProcess = glob.glob(path)
    pathList= pathList + pathInProcess

# 3. 입수 과정에서 중복입수되어 표시된 duplicated 표시 파일 제거
pathList = [x for x in pathList if "duplicated" not in x]

# 4. 분리
pathList = [x for x in pathList if "(2017." in x] + [x for x in pathList if "(2018." in x] + [x for x in pathList if "(2019." in x] 

# 5. Preprocess
PathListDf = pd.DataFrame(pathList)
df = pd.DataFrame([x.split("_") for x in pathList])
df["path"] = PathListDf[0]
df["con"] = df[6].str.contains("연결")
df['con'] = np.where(df['con']==True, "C", "S")
df['amend'] = df[6].str.contains("정정")
df['amend'] = np.where(df['amend']==True, "A", "B")
df["key"] = df[2] + df[6].str.slice(stop=10) + df["con"] + df["amend"] + df[5] + df[8] + df[10]
# key = 접수일 + 보고서 제출일(DRT 인증일) + 연결(C/S) + 정정(A/B) + 보고기간종료월 + 종목코드 + 법인등록번호

df["duplc"] = df.duplicated(subset=["key"], keep=False)
isTrue = df[df["duplc"] == True]
df = df.drop_duplicates(subset=["key"])

pathListOut = df["path"].tolist()

result = []
count = 0

KGAAP = ["일반기업회계기준에따라", "일반회계기준에따라", "一般企業會計基準에따라", "일반기업회계처리기준에따라",
         "'일반기업회계기준'에 따라"]
KIFRS = ["한국채택국제회계기준에따라", "한국채택회계기준"]
OTHERS = ["공기업ㆍ준정부기관회계사무규칙에따라", "지방공기업법및지방공기업결산지침에따라",
          "지방공기업법(령)및지방공기업결산지침에따라", "일반기업회계기준및",
          "지방공기업법과행정자치부의지방공기업결산지침,대구도시공사정관및회계규정에따라"]

GAAP = [KGAAP, KIFRS, OTHERS]
tag = ["일반기업회계기준", "한국채택국제회계기준", "기타기준"]


# disclaimer of opinion
keyWord1 = ['의견거절근거', '의견을표명하지않', '감사의견을표명하지아니합니다', '의견을표명하지아니', '의견을표명할수없']
# qualified 
keyWord2 = ['한정의견근거', '한정의견근거단락에기술된사항이미치는영향을제외']
# adverse
keyWord3 = ['부적정의견근거', '부적정의견근거단락에서기술된사항의유의성']

keyWord = [keyWord1, keyWord2, keyWord3]
opinion = ["의견거절", "한정", "부적정"]


for file in pathListOut:

    html = open(file, "r", encoding="utf-8")
    soup = BeautifulSoup(html, "lxml")
    html.close()
    
    content = ''.join(soup.text.split())
    
    num = 0

    accountingStandard = "예외" 
    for i in GAAP:
        for j in i:
            if j in content:            
                accountingStandard = tag[num]
                break
        num += 1
    
    num = 0

    auditOpinion = "적정" 
    for i in keyWord:
        for j in i:
            if j in content:        
                auditOpinion = opinion[num] 
                break
        num += 1
    returnText = accountingStandard + "\t" + auditOpinion 
        
    result.append(returnText)
        
    count += 1
    print(count, end='  ')

df["GAAP"] = result

df = df.drop([0, 1, 14, "path", "duplc"], axis=1)
df = df.sort_values(by=[10, 5, "con", 2, 6, "amend"],
                    ascending=[True, True, True, False, False, True])

df["toDrop"] = 1

for i in range(1, len(df)):
    if df.iloc[i,3] == df.iloc[i-1,3] and df.iloc[i,8] == df.iloc[i-1,8]:
        df.iloc[i, 16] = df.iloc[i-1, 16] + 1
    else:
        df.iloc[i, 16] = 1

df = df[df["toDrop"] == 1]
df = df.drop("toDrop", axis=1)

os.chdir(r"C:\\Users\\yoont\\Desktop\\output\\") 
df.to_csv("wp01.data13_output.csv", sep="\t")