pdf_view.py

# -*- coding: utf-8 -*-
import tabula
import sys
import pandas as pd
import re
import os
import pymysql
import time
import subprocess
import datetime
import shutil
import smtplib
from sqlalchemy import create_engine
from win32com import client
from email.header import Header
from email.mime.text import MIMEText
from email.utils import parseaddr, formataddr
from email.mime.multipart import MIMEMultipart
from xml.etree import ElementTree as ET
from os.path import getsize


def pdf_to_xml():
    os.chdir("C:\\Users\\qinxd\\Desktop\\pp")
    # """cd 到文件夹运行PDF转xml"""
    subprocess.Popen(r'exec.bat -d "1 1"')

class mv_file:
    """
    type_file : 'all' 是全部, 可以指定各种格式pdf,csv,xlsx
    """

    def __init__(self, old_path, new_path, type_file='all'):
        self.old_path = old_path
        self.new_path = new_path
        self.files = []
        self.type_file = type_file

    def read_files(self):
        if self.type_file == 'all':
            self.files = os.listdir(self.old_path)
        else:
            files = os.listdir(self.old_path)
            self.files = [i for i in files if i[-len(self.type_file):] == self.type_file]
        return self.files

    def move_files(self):
        if not os.path.exists(self.new_path):
            os.makedirs(self.new_path)  # 创建路径
        files_list = self.read_files()
        for i in files_list:
            srcfile = self.old_path + '\\' + i
            dstfile = self.new_path + '\\' + i
            shutil.move(srcfile, dstfile)
            print("move %s -> %s" % (srcfile, dstfile))

    def copy_files(self):
        if not os.path.exists(self.new_path):
            os.makedirs(self.new_path)  # 创建路径
        files_list = self.read_files()
        for i in files_list:
            srcfile = self.old_path + '\\' + i
            dstfile = self.new_path + '\\' + i
            shutil.copy(srcfile, dstfile)
            print("copy %s -> %s" % (srcfile, dstfile))



class to_email:

    def __init__(self, to_addre):
        self.to_addre = to_addre

    def format_addr(self, s):
        name, addr = parseaddr(s)
        return formataddr((Header(name, 'utf-8').encode(), addr))

    def course(self):
        # 输入Email地址和口令:
        from_addr = '632207812@qq.com'
        password = 'aphstjyszatqbecc'
        # 输入收件人地址:
        # to_addr = '632207812@qq.com'
        # 输入SMTP服务器地址:
        smtp_server = 'smtp.qq.com'
        msg = MIMEMultipart()
        msg.attach(MIMEText('pdf解析完成请查收', 'plain', 'utf-8'))
        # msg = MIMEText('hello, send by Python...', 'plain', 'utf-8')#正文
        msg['From'] = self.format_addr('黄义超 <%s>' % from_addr)  # 发件人 不该就成为邮箱地址
        msg['To'] = self.format_addr('收件人 <%s>' % self.to_addre)  # 收件人
        msg['Subject'] = Header('pdf解析的报告', 'utf-8').encode()  # 标题

        path = os.getcwd()
        a = mv_file(old_path=path, new_path=path, type_file='.csv')
        b = a.read_files()

        lu = path + '\\' + b[0]
        att1 = MIMEText(open(lu, 'rb').read(), 'base64', 'utf-8')
        att1["Content-Type"] = 'application/octet-stream'
        att1["Content-Disposition"] = 'attachment; filename="{}"'.format(b[0])
        msg.attach(att1)
        server = smtplib.SMTP(smtp_server, 25)
        server.set_debuglevel(1)
        server.login(from_addr, password)
        server.sendmail(from_addr, [self.to_addre], msg.as_string())
        server.quit()


def sql_cols(df, usage="sql"):
    cols = tuple(df.columns)
    if usage == "sql":
        cols_str = str(cols).replace("'", "`")
        if len(df.columns) == 1:
            cols_str = cols_str[:-2] + ")"  # to process dataframe with only one column
        return cols_str
    elif usage == "format":
        base = "'%%(%s)s'" % cols[0]
        for col in cols[1:]:
            base += ", '%%(%s)s'" % col
        return base
    elif usage == "values":
        base = "%s=VALUES(%s)" % (cols[0], cols[0])
        for col in cols[1:]:
            base += ", `%s`=VALUES(`%s`)" % (col, col)
        return base


engine = create_engine(
    "mysql+pymysql://{}:{}@{}:{}/{}".format('root', 'Chihiro123+', '10.3.2.25', 3306, 'base', ),
    connect_args={"charset": "utf8"}, echo=True, )

def to_sql(tb_name, conn, dataframe, type="update", chunksize=2000, debug=False):
    """
    Dummy of pandas.to_sql, support "REPLACE INTO ..." and "INSERT ... ON DUPLICATE KEY UPDATE (keys) VALUES (values)"
    SQL statement.

    Args:
        tb_name: str
            Table to insert get_data;
        conn:
            DBAPI Instance
        dataframe: pandas.DataFrame
            Dataframe instance
        type: str, optional {"update", "replace", "ignore"}, default "update"
            Specified the way to update get_data. If "update", then `conn` will execute "INSERT ... ON DUPLICATE UPDATE ..."
            SQL statement, else if "replace" chosen, then "REPLACE ..." SQL statement will be executed; else if "ignore" chosen,
            then "INSERT IGNORE ..." will be excuted;
        chunksize: int
            Size of records to be inserted each time;
        **kwargs:

    Returns:
        None
    """

    df = dataframe.copy(deep=False)
    df = df.fillna("None")
    df = df.applymap(lambda x: re.sub('([\'\"\\\])', '\\\\\g<1>', str(x)))
    cols_str = sql_cols(df)
    sqls = []
    for i in range(0, len(df), chunksize):
        # print("chunk-{no}, size-{size}".format(no=str(i/chunksize), size=chunksize))
        df_tmp = df[i: i + chunksize]

        if type == "replace":
            sql_base = "REPLACE INTO `{tb_name}` {cols}".format(
                tb_name=tb_name,
                cols=cols_str
            )

        elif type == "update":
            sql_base = "INSERT INTO `{tb_name}` {cols}".format(
                tb_name=tb_name,
                cols=cols_str
            )
            sql_update = "ON DUPLICATE KEY UPDATE {0}".format(
                sql_cols(df_tmp, "values")
            )

        elif type == "ignore":
            sql_base = "INSERT IGNORE INTO `{tb_name}` {cols}".format(
                tb_name=tb_name,
                cols=cols_str
            )

        sql_val = sql_cols(df_tmp, "format")
        vals = tuple([sql_val % x for x in df_tmp.to_dict("records")])
        sql_vals = "VALUES ({x})".format(x=vals[0])
        for i in range(1, len(vals)):
            sql_vals += ", ({x})".format(x=vals[i])
        sql_vals = sql_vals.replace("'None'", "NULL")

        sql_main = sql_base + sql_vals
        if type == "update":
            sql_main += sql_update

        if sys.version_info.major == 2:
            sql_main = sql_main.replace("u`", "`")
        if sys.version_info.major == 3:
            sql_main = sql_main.replace("%", "%%")

        if debug is False:
            try:
                conn.execute(sql_main)
            except pymysql.err.InternalError as e:
                print("ENCOUNTERING ERROR: {e}, RETRYING".format(e=e))
                time.sleep(10)
                conn.execute(sql_main)
        else:
            sqls.append(sql_main)
    if debug:
        return sqls


def now_time(a=0):
    now = datetime.datetime.now()
    delta = datetime.timedelta(days=a)
    n_days = now + delta
    print(n_days.strftime('%Y-%m-%d %H:%M:%S'))
    f = n_days.strftime('%Y-%m-%d')
    return f


def now_time2(a=0):
    now = datetime.datetime.now()
    delta = datetime.timedelta(minutes=a)
    n_days = now + delta
    print(n_days.strftime('%Y-%m-%d %H:%M:%S'))
    f = n_days.strftime('%Y%m%d%H%M')
    return f


def strQ2B(ustring):
    """全角转半角"""
    rstring = ""
    for uchar in ustring:
        inside_code = ord(uchar)
        if inside_code == 12288:  # 全角空格直接转换
            inside_code = 32
        elif (inside_code >= 65281 and inside_code <= 65374):  # 全角字符（除空格）根据关系转化
            inside_code -= 65248

        rstring = rstring + chr(inside_code)
    return rstring




def doc2pdf(doc_name, pdf_name):
    """
    :word文件转pdf
    :param doc_name word文件名称
    :param pdf_name 转换后pdf文件名称
    """
    try:
        word = client.DispatchEx("Word.Application")
        if os.path.exists(pdf_name):
            os.remove(pdf_name)
        worddoc = word.Documents.Open(doc_name, ReadOnly=1)
        worddoc.SaveAs(pdf_name, FileFormat=17)
        worddoc.Close()
        return pdf_name
    except:
        return 1


def analysis():
    """

    :path: 读取文件夹的路径
    """
    p = os.getcwd()
    pp = p + '\\pdfs\\'
    files = os.listdir(pp)
    """
    读取数据库里面已经解析的pdf
    """
    df = pd.read_sql("select pdf_name from pdf_match", engine)
    pl = df['pdf_name'].values.tolist()
    ma = [i for i in files if i not in pl]
    data = pd.DataFrame(ma, columns=['pdf_name'])
    to_sql('pdf_match', engine, data, type='update')
    pdfs = []

    for i in ma:
        if '.pdf' in i:
            pdfs.append(i)
        else:
            pass

    result = []
    for pdf in pdfs:
        readp = pp + pdf
        print(pdf)
        w = read_pdf_all(readp)
        f = [pdf, w[0], w[1]]
        result.append(f)

    dataframe = pd.DataFrame(result, columns=['PDF名字', '解析结果', '内容和状况'])
    dataframe['解析不出的'] = dataframe['内容和状况'].apply(lambda x: '解析失败,或者有图片' if '解析' in x else 'pass')
    now = '\\' + now_time2() + '.csv'
    dataframe.to_csv(p + now, encoding='utf_8_sig')


def all_doc2pdf(path):
    files = os.listdir(path)
    docs = []
    for i in files:
        if '.doc' in i:
            docs.append(i)
        else:
            pass
    for doc in docs:
        in_name = path + doc
        out_name = path + doc[:-3] + 'pdf'
        try:
            doc2pdf(in_name, out_name)
            print(doc + 'successful')
        except BaseException as e:
            print(e)
        else:
            pass

def len_pdf(all_files=r'\\vm-zdhjg64\resource\pdf\港股其他 (月報表等)'):
    lls = []
    dfp = pd.read_sql("select pdf_name from pdf_match", engine)
    opdf = dfp['pdf_name'].values.tolist()
    for dirpath, dirnames, filenames in os.walk(all_files):
        for filename in filenames:
            if filename in opdf:
                pass
            else:
                cc = dirpath + '\\'+filename
                if cc[-4:] in ['.pdf', '.doc']:
                    lls.append(cc)
    return lls


def main():
    print('start')
    # all_files = r'\\dmp1\resource\pdf\港股其他 (月報表等)'
    all_files = r'\\vm-zdhjg64\resource\pdf\港股其他 (月報表等)'
    lls = []
    dfp = pd.read_sql("select pdf_name from pdf_match", engine)
    opdf = dfp['pdf_name'].values.tolist()
    for dirpath, dirnames, filenames in os.walk(all_files):
        for filename in filenames:
            if filename in opdf:
                pass
            else:
                cc = dirpath + '\\'+filename
                if cc[-4:] in ['.pdf', '.doc']:
                    lls.append(cc)
    print('新的pdf有'+str(len(lls)))
    pdfaddress = os.getcwd() + '\\pdfs\\'
    if not os.path.isdir(pdfaddress):
        os.mkdir(pdfaddress)

    for ll in lls:
        shutil.copy(ll, pdfaddress)

    print('开始解析word')

    """word转pdf"""
    # all_doc2pdf(path=pdfaddress)
    # print('doc 转 pdf 成功')
    """
    迁移存放pdf文件，存放旧的csv文件
    """
    base_path = os.getcwd()
    PATH = os.getcwd() + '\\被解析过的pdfs\\'
    if not os.path.isdir(PATH):
        os.mkdir(PATH)
    csv_path = os.getcwd() + '\\存放旧csv\\'
    if not os.path.isdir(csv_path):
        os.mkdir(csv_path)
    csv = mv_file(base_path + '\\', csv_path, type_file='.csv')
    csv.move_files()
    analysis()
    print('解析PDF成功')


    """
    传入邮箱
    """
    if len(lls) > 0:
        e = to_email('hkstock@gildata.com')
        e.course()
        e1 = to_email('632207812@qq.com')
        e1.course()

    strtime = now_time()
    new_path = PATH + strtime + '\\'
    if not os.path.isdir(new_path):
        os.mkdir(new_path)
    try:
        a = mv_file(pdfaddress, new_path, type_file='all')
        a.move_files()
    except BaseException:
        pass







def read_xml(path):
    print(path)
    tree = ET.parse(path)
    root = tree.getroot()
    # root = ET.fromstring(country_data_as_string) #通过字符串导入,直接获取根
    childs = root.getchildren()

    books = []
    # all = ""
    for child0 in childs:
        # book = {}
        for child00 in child0.getchildren():
            # print child00.tag #标签名，即name、date、price、description
            # print child00.text
            bb = child00.text
            books.append("喵厸" + re.sub("s/|\|", "甲鸭", strQ2B(bb)) + "喵厸")
            # all += re.sub("", "", strQ2B(bb))

            # for p in child00:
            #     t = p.text
            #     books.append("\n" + t + "\n")
            #     all += "\n" + t + "\n"

    return books

def zero(strs):
    s = 0
    for i in v:
        if i in strs:
            s += 1
    return s

def havestr(lls, strs):
    have = []
    for i in lls:
        if i in strs:
            have.append(i)
    ll = list(set(have))
    if len(ll) > 0:
        return ll
    else:
        return None


def clean(txt, zhenze):
    try:
        num = re.sub("", "", re.search(zhenze, txt).group(1))
    except BaseException:
        num = ''
    else:
        pass
    return num

def relist(list, ss):
    ll = []
    for i in list:
        a = clean(ss, "{}".format(i))
        print(a)
        if type(a) is str and len(a) >= 1:
            ll.append(a)
    if len(ll) >= 1:
        print('采集合集:', ll)
        # ll.sort(key=lambda i: len(i), reverse=True)
        return ll[0]
    else:
        return None

def getdirsize(name):
    size = getsize(name)
    print(size)  # 输出文件的大小
    return size


def only_num(x):
    try:
        t = re.sub(",", "", x)
        print(t)
        txt = re.findall('[0-9]+', t)
        print(txt)
        num = [float(x) for x in txt if x != '']
    except BaseException:
        return None
    return num

path = r'C:\Users\qinxd\Desktop\pp\pdfs2018-11-01羅馬集團(08072)月報表股份發行人的證券變動月報表(截至2018年10月31日) (461kb, pdf).pdf.xml'

path = r'C:\Users\qinxd\Desktop\105\100005-匯豐控股-月報表截至2018年9月30日止月份之股份發行人的證券變動月報表 (694KB, PDF).pdf.xml'





vlist = []

lls = []
all_files = r'C:\Users\qinxd\Desktop\pp'
for dirpath, dirnames, filenames in os.walk(all_files):
    for filename in filenames:
        cc = dirpath + '\\' + filename
        if cc[-4:] in ['.xml']:
            lls.append(cc)

for path in lls:

    name = os.path.basename(path)
    try:
        size = getdirsize(path)


        if size < 2000:
            psize = '文件可能是图片需要查看'
            v = None
            vlist.append([name, v, psize])
        else:
            psize = 'pass'
            Z1 = read_xml(path)
            df = pd.DataFrame(Z1)
            v = ['上月底結存', '本月增加/(減少)', '增加/(減少)','本月底結存','本月增加','增加/(減少)']


            df = df.reset_index(level=0)
            df.columns = ["SUM", "A"]

            df["B"] = df["A"].apply(lambda x: zero(x) if type(x) is str else 0)
            df["C"] = (df["B"].shift(1) + df["B"].shift(-1) + df["B"])
            # df["映射"] = list(map(lambda x, y: havestr(v, x) if y >= 1 else None, df["A"], df["B"]))

            llmax = list(set(df["C"].tolist()))
            llmax = [int(i) for i in llmax if i >= 2]
            df['D'] = list(map(lambda x, y: x if y in llmax else None, df['SUM'], df['C']))
            p = list(set(df["D"].tolist()))
            p = [int(i) for i in p if type(i) is float and i > 2]

            """取坐标公司基本情况"""
            size = 2
            ps = []
            p1 = []
            for i in range(len(p)):
                if i == (len(p) - 1):
                    p1.append(p[i])
                    ps.append(p1)
                else:
                    if (p[i + 1] - p[i]) < size:
                        p1.append(p[i])
                    else:
                        p1.append(p[i])
                        new = p1.copy()
                        ps.append(new)
                        p1.clear()


            maxandmin = [[min(i), max(i)] for i in ps]

            wen = []
            for i in range(len(maxandmin)):
                df1 = df.iloc[maxandmin[i][0]:maxandmin[i][1], [1]]
                t1 = df1["A"].tolist()
                wen1 = ''.join(t1)
                wen.append(wen1)





            ff = ["本月增加/\(減少\)(.+?)本月底結存","本月增加/\(減少\)(.+?)喵厸",
                  "增加/\(減少\)(.+?)本月底","增加/\(減少\)(.+?)喵厸"]
            caowen = []
            for txt in wen:
                # print(txt)
                # s = clean(txt,"本月增加(.+?)本月底結存")
                w = relist(ff, txt)
                caowen.append(w)

            nums = []
            for i in caowen:
                nb = only_num(i)
                if type(nb) is list:
                    for yy in nb:
                        nums.append(yy)

            nums = list(set(nums))
            v = [str(i) for i in nums if i > 100]

            wnums = ",".join(v)

            if len(wnums) >= 1:
                psize = '有数据请查看'

            if wnums == []:
                wnums = None

            name = os.path.basename(path)
            vlist.append([name, wnums, psize])
    except BaseException:
        print('文件打开失败')
        vlist.append([name, None, '无法解析请手动查看'])


vdf = pd.DataFrame(vlist)
vdf.columns = ['文件名', '数值', '文件状态']


vdf.to_csv(r'C:\Users\qinxd\Desktop\xml2018127122111.csv', encoding='utf_8_sig')