G01_0350/output_file.py

import app
import xlwt
import log
import threading
import tkinter.messagebox
import sql
import time
import datetime
import os
from reportlab.pdfbase import pdfmetrics
from reportlab.pdfbase.ttfonts import TTFont
from reportlab.platypus import Table, SimpleDocTemplate, Paragraph
from reportlab.lib.pagesizes import A3, landscape
from reportlab.lib.styles import getSampleStyleSheet
from reportlab.lib import colors
from reportlab.graphics.charts.barcharts import VerticalBarChart
from reportlab.graphics.charts.legends import Legend
from reportlab.graphics.shapes import Drawing

pdfmetrics.registerFont(TTFont('SimSun', 'SIMYOU.TTF'))

class output_file(threading.Thread):
    def __init__(self, threadID, name, counter, type, datastr='', filepath='', content='', master=None):
        threading.Thread.__init__(self)
        self.threadID = threadID
        self.name = name
        self.counter = counter
        self.filepath = filepath
        self.content = content
        self.type = type
        self.user_var = app.user_var
        self.datastr = datastr
        self.user_var = app.user_var
        self.sqlclient = sql.sqlclient
        self.log_file = log.log_file
        self.log_print = log.log_file

    def get_product_param(self):
        self.user_var.product_data.clear()
        idx = 0
        for product_name in self.user_var.product_list:
            if product_name == '':
                continue
            sqlstr = "SELECT product_idx,product_name,param_type,startuptime,airtime,presstime, testtime, exhaust, \
                        maxpress, minpress, setpress, capacity, posthreshold, pnegthreshold \
                        FROM product_param_tbl WHERE product_name='{0}' ORDER by product_idx ASC".format(product_name)
            results = self.sqlclient.sql_inquire_all(sqlstr)
            if results != None and len(results) > 0:
                for row in results:
                    if row[2] == 17700:
                        value = (
                        row[0]+1, self.user_var.product_17700_list[idx], row[3], row[4], row[5], row[6], row[7], row[8],
                        row[9], row[10], row[11], row[12], row[13])
                    else:
                        value = (
                        row[0]+1, self.user_var.product_17720_list[idx], row[3], row[4], row[5], row[6], row[7], row[8],
                        row[9], row[10], row[11], row[12], row[13])
                    self.user_var.product_data.append(value)
            else:
                value = ('0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0')
                self.user_var.product_data.append(value)

        self.user_var.listview_stat = True

    def get_product_data(self):
        self.user_var.product_data.clear()
        sqlstr = 'SELECT batch,serial, product_name, testok, data_17700, data_17720, team, begin_time, end_time\
                 FROM product_data_tbl {0}'.format(
            self.datastr)
        results = self.sqlclient.sql_inquire_all(sqlstr)
        if results != None and len(results) > 0:
            for row in results:
                tt = time.localtime(row[8])
                if row[3] == 1:
                    testok = 'OK'
                else:
                    testok = 'NG'
                sqlstr = "SELECT 17700_name,17720_name FROM product_tbl WHERE stat=True and product_name='{0}'".format(
                    row[2]
                )
                paramresult = self.sqlclient.sql_inquire_all(sqlstr)
                if paramresult != None and len(paramresult) > 0:
                    for paramrow in paramresult:
                        name_17700 = paramrow[0]
                        name_17720 = paramrow[1]
                else:
                    name_17700 = row[2]
                    name_17720 = row[2]

                datalist17700 = [time.strftime("%Y-%m-%d", tt), time.strftime("%H:%M:%S", tt), row[1], row[0], testok,
                                 row[4], row[6], name_17700]
                sqlstr = "SELECT startuptime,airtime,presstime, testtime, exhaust, maxpress, minpress, setpress, capacity, \
                         posthreshold, pnegthreshold FROM product_param_tbl WHERE product_name='{0}' and \
                         param_type =  17700".format(row[2])
                paramresult = self.sqlclient.sql_inquire_all(sqlstr)
                if paramresult != None and len(paramresult) > 0:
                    for paramrow in paramresult:
                        datalist17700.append(paramrow[0])
                        datalist17700.append(paramrow[1])
                        datalist17700.append(paramrow[2])
                        datalist17700.append(paramrow[3])
                        datalist17700.append(paramrow[4])
                        datalist17700.append(paramrow[5])
                        datalist17700.append(paramrow[6])
                        datalist17700.append(paramrow[7])
                        datalist17700.append(paramrow[8])
                        datalist17700.append(paramrow[9])
                        datalist17700.append(paramrow[10])
                else:
                    datalist17700.append(0)
                    datalist17700.append(0)
                    datalist17700.append(0)
                    datalist17700.append(0)
                    datalist17700.append(0)
                    datalist17700.append(0)
                    datalist17700.append(0)
                    datalist17700.append(0)
                    datalist17700.append(0)
                    datalist17700.append(0)
                    datalist17700.append(0)
                self.user_var.product_data.append(tuple(datalist17700))

                datalist17720 = [time.strftime("%Y-%m-%d", tt), time.strftime("%H:%M:%S", tt), row[1], row[0], testok,
                                 row[5], row[6], name_17720]
                sqlstr = "SELECT startuptime, airtime,presstime, testtime, exhaust, maxpress, minpress, setpress, capacity, \
                                         posthreshold, pnegthreshold FROM product_param_tbl WHERE product_name='{0}' and \
                                         param_type =  17720".format(row[2])
                paramresult = self.sqlclient.sql_inquire_all(sqlstr)
                if paramresult != None and len(paramresult) > 0:
                    for paramrow in paramresult:
                        datalist17720.append(paramrow[0])
                        datalist17720.append(paramrow[1])
                        datalist17720.append(paramrow[2])
                        datalist17720.append(paramrow[3])
                        datalist17720.append(paramrow[4])
                        datalist17720.append(paramrow[5])
                        datalist17720.append(paramrow[6])
                        datalist17720.append(paramrow[7])
                        datalist17720.append(paramrow[8])
                        datalist17720.append(paramrow[9])
                        datalist17720.append(paramrow[10])
                else:
                    datalist17720.append(0)
                    datalist17720.append(0)
                    datalist17720.append(0)
                    datalist17720.append(0)
                    datalist17720.append(0)
                    datalist17720.append(0)
                    datalist17720.append(0)
                    datalist17720.append(0)
                    datalist17720.append(0)
                    datalist17720.append(0)
                    datalist17720.append(0)
                self.user_var.product_data.append(tuple(datalist17720))
        self.user_var.listview_stat = True

    def save_excel_file(self):
        try:
            xls = xlwt.Workbook()
            sht1 = xls.add_sheet("Sheet1")
            for row, row_content in enumerate(self.content):
                for column, column_content in enumerate(row_content):
                    sht1.write(row, column, column_content)
            xls.save(self.filepath)
            return True
        except Exception as e:
            self.log_file.logger.error('写入excel文件({0})异常: {1}'.format(self.filepath, e),
                                       exc_info=True, stack_info=True)
            return False

    def draw_title(self, title):
        style = getSampleStyleSheet()
        ct = style['Normal']
        ct.fontName = 'SimSun'
        ct.fontSize = 18
        # 设置行距
        ct.leading = 50
        # 颜色
        ct.textColor = colors.green
        # 居中
        ct.alignment = 1
        # 添加标题并居中
        title = Paragraph(title, ct)
        return title

    def draw_table(self, *args):
        style = [
            ('FONTNAME', (0, 0), (-1, -1), 'SimSun'),  # 字体
            ('BACKGROUND', (0, 0), (-1, 0), '#d5dae6'),  # 设置第一行背景颜色
            ('ALIGN', (0, 0), (-1, -1), 'CENTER'),  # 对齐
            ('VALIGN', (-1, 0), (-2, 0), 'MIDDLE'),  # 对齐
            ('GRID', (0, 0), (-1, -1), 0.5, colors.grey),  # 设置表格框线为grey色，线宽为0.5
        ]
        table = Table(args, style=style)
        return table

    def save_pdf_file(self):
        try:
            content = list()
            content.append(self.draw_title('气密数据表'))
            content.append(self.draw_table(*self.content))
            pdf = SimpleDocTemplate(self.filepath, pagesize=landscape(A3))
            pdf.build(content)
            return True
        except Exception as e:
            self.log_file.logger.error('写入pdf文件({0})异常: {1}'.format(self.filepath, e),
                                       exc_info=True, stack_info=True)
            return False

    def delete_database_data(self):
        if self.user_var.del_data_stat == True:
            folder_path = 'D:/sqlBAK'
            backfileName = '{0}/G01_0350_{1}.sql'.format(folder_path, datetime.datetime.now().strftime('%Y%m%d%H%M%S'))
            print(backfileName)
            if os.path.isdir(folder_path):
                print('Folder exists')
            else:
                os.mkdir(folder_path)
                print('Folder does not exist')
            if os.path.exists(backfileName):
                os.remove(backfileName)
            os.system("mysqldump -h{0} -u{1} -p{2} {3} > {4}".format('localhost', 'root',
                                                                       '1234qwer!@#$QWER', 'G01_0350', backfileName))
            sqlstr = 'DELETE FROM product_data_tbl {0}'.format(self.user_var.del_data_str)
            print(sqlstr)
            self.sqlclient.sql_action_all(sqlstr)

    def run(self):
        if self.type == 'save_excel':
            stat = self.save_excel_file()
            if stat == True:
                if self.user_var.del_data_stat == True:
                    self.delete_database_data()
                    tkinter.messagebox.showinfo(title='提示', message='文件保存成功，并已在数据库中移除对应数据！')
                else:
                    tkinter.messagebox.showinfo(title='提示', message='文件保存成功！')
            else:
                tkinter.messagebox.showwarning(title='提示', message='文件保存失败！')
            self.user_var.file_save_stat = True
            print("{0}保存完成".format(self.filepath))
        elif self.type == 'save_pdf':
            stat = self.save_pdf_file()
            if stat == True:
                tkinter.messagebox.showinfo(title='提示', message='文件保存成功！')
            else:
                tkinter.messagebox.showwarning(title='提示', message='文件保存失败！')
            self.user_var.file_save_stat = True
            print("{0}保存完成".format(self.filepath))
        elif self.type == 'inquire_param':
            self.get_product_param()
        elif self.type == 'inquire_data':
            self.user_var.del_data_str = self.datastr
            self.get_product_data()