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()