首页 > > 网络编程 > 其它 >

python学习笔记--工资条发放软件

2018-06-18 03:08:39来源：未知阅读 ()

　　近期学习python，便写点东西练手，主要使用了pyqt5，eric6，smtplib，pymysql，xlrd。

　　主要功能为：通过固定模板的excel上传员工信息以及当月工资条，然后向员工邮箱发送当月工资条。效果如下：

　　程序界面：

　　工资条模板：

　　员工模板：

　　1.使用eric6画出界面（我始终坚信，程序员的审美，没有最丑，只有更丑）：

　　2.编译窗口得到如下代码

  1 # -*- coding: utf-8 -*-
  2 
  3 # Form implementation generated from reading ui file 'D:\work\python_workspace\SalarySheet\main.ui'
  4 #
  5 # Created by: PyQt5 UI code generator 5.10.1
  6 #
  7 # WARNING! All changes made in this file will be lost!
  8 
  9 from PyQt5 import QtCore, QtGui, QtWidgets
 10 import time
 11 
 12 class Ui_MainWindow(object):
 13     def setupUi(self, MainWindow):
 14         MainWindow.setObjectName("SalarySheet")
 15         MainWindow.resize(506, 514)
 16         self.centralWidget = QtWidgets.QWidget(MainWindow)
 17         self.centralWidget.setObjectName("centralWidget")
 18         self.import_employee = QtWidgets.QPushButton(self.centralWidget)
 19         self.import_employee.setGeometry(QtCore.QRect(10, 450, 75, 23))
 20         self.import_employee.setObjectName("import_employee")
 21         self.delete_employee = QtWidgets.QPushButton(self.centralWidget)
 22         self.delete_employee.setGeometry(QtCore.QRect(130, 450, 75, 23))
 23         self.delete_employee.setObjectName("delete_employee")
 24         self.label = QtWidgets.QLabel(self.centralWidget)
 25         self.label.setGeometry(QtCore.QRect(130, 20, 31, 16))
 26         self.label.setObjectName("label")
 27         self.employee_count = QtWidgets.QLabel(self.centralWidget)
 28         self.employee_count.setGeometry(QtCore.QRect(175, 22, 54, 12))
 29         self.employee_count.setText("")
 30         self.employee_count.setObjectName("employee_count")
 31         self.label_2 = QtWidgets.QLabel(self.centralWidget)
 32         self.label_2.setGeometry(QtCore.QRect(10, 20, 54, 12))
 33         self.label_2.setObjectName("label_2")
 34         self.label_3 = QtWidgets.QLabel(self.centralWidget)
 35         self.label_3.setGeometry(QtCore.QRect(300, 20, 54, 12))
 36         self.label_3.setObjectName("label_3")
 37         self.label_4 = QtWidgets.QLabel(self.centralWidget)
 38         self.label_4.setGeometry(QtCore.QRect(400, 20, 31, 16))
 39         self.label_4.setObjectName("label_4")
 40         self.salary_count = QtWidgets.QLabel(self.centralWidget)
 41         self.salary_count.setGeometry(QtCore.QRect(440, 22, 54, 12))
 42         self.salary_count.setText("")
 43         self.salary_count.setObjectName("salary_count")
 44         self.import_salary = QtWidgets.QPushButton(self.centralWidget)
 45         self.import_salary.setGeometry(QtCore.QRect(300, 450, 75, 23))
 46         self.import_salary.setObjectName("import_salary")
 47         self.delete_salary = QtWidgets.QPushButton(self.centralWidget)
 48         self.delete_salary.setGeometry(QtCore.QRect(420, 450, 75, 23))
 49         self.delete_salary.setObjectName("delete_salary")
 50         self.send = QtWidgets.QPushButton(self.centralWidget)
 51         self.send.setGeometry(QtCore.QRect(210, 340, 75, 31))
 52         self.send.setObjectName("send")
 53         self.paln = QtWidgets.QLabel(self.centralWidget)
 54         self.paln.setGeometry(QtCore.QRect(210, 390, 71, 20))
 55         self.paln.setText("")
 56         self.paln.setObjectName("paln")
 57         self.employee = QtWidgets.QTableWidget(self.centralWidget)
 58         self.employee.setSelectionBehavior(1)
 59         self.employee.setEditTriggers(QtWidgets.QTableWidget.NoEditTriggers)
 60         self.employee.setAlternatingRowColors(True)
 61         self.employee.setGeometry(QtCore.QRect(10, 50, 191, 381))
 62         self.employee.setRowCount(0)
 63         self.employee.setColumnCount(2)
 64         self.employee.setObjectName("employee")
 65         item = QtWidgets.QTableWidgetItem()
 66         self.employee.setHorizontalHeaderItem(0, item)
 67         item = QtWidgets.QTableWidgetItem()
 68         self.employee.setHorizontalHeaderItem(1, item)
 69         self.employee.horizontalHeader().setDefaultSectionSize(87)
 70         self.employee.verticalHeader().setDefaultSectionSize(30)
 71         self.salary = QtWidgets.QTableWidget(self.centralWidget)
 72         self.salary.setSelectionBehavior(1)
 73         self.salary.setEditTriggers(QtWidgets.QTableWidget.NoEditTriggers)
 74         self.salary.setAlternatingRowColors(True)
 75         self.salary.setGeometry(QtCore.QRect(300, 50, 191, 391))
 76         self.salary.setRowCount(0)
 77         self.salary.setColumnCount(2)
 78         self.salary.setObjectName("salary")
 79         item = QtWidgets.QTableWidgetItem()
 80         self.salary.setHorizontalHeaderItem(0, item)
 81         item = QtWidgets.QTableWidgetItem()
 82         self.salary.setHorizontalHeaderItem(1, item)
 83         self.salary.horizontalHeader().setDefaultSectionSize(87)
 84         self.salary.verticalHeader().setDefaultSectionSize(30)
 85         self.salary_date = QtWidgets.QDateEdit(self.centralWidget)
 86         self.salary_date.setGeometry(QtCore.QRect(210, 60, 70, 22))
 87         self.salary_date.setObjectName("salary_date")
 88         now_day = time.strftime("%Y-%m", time.localtime())
 89         self.salary_date.setDate(QtCore.QDate.fromString(now_day, 'yyyy-MM'))
 90         self.query = QtWidgets.QPushButton(self.centralWidget)
 91         self.query.setGeometry(QtCore.QRect(210, 100, 71, 23))
 92         self.query.setObjectName("query")
 93         MainWindow.setCentralWidget(self.centralWidget)
 94 
 95         self.retranslateUi(MainWindow)
 96         QtCore.QMetaObject.connectSlotsByName(MainWindow)
 97 
 98     def retranslateUi(self, MainWindow):
 99         _translate = QtCore.QCoreApplication.translate
100         MainWindow.setWindowTitle(_translate("SalarySheet", "SalarySheet"))
101         self.import_employee.setText(_translate("SalarySheet", "导入"))
102         self.delete_employee.setText(_translate("SalarySheet", "删除"))
103         self.label.setText(_translate("SalarySheet", "总数："))
104         self.label_2.setText(_translate("SalarySheet", "员工清单"))
105         self.label_3.setText(_translate("SalarySheet", "工资条"))
106         self.label_4.setText(_translate("SalarySheet", "总数："))
107         self.import_salary.setText(_translate("SalarySheet", "导入"))
108         self.delete_salary.setText(_translate("SalarySheet", "删除"))
109         self.send.setText(_translate("SalarySheet", "发送"))
110         item = self.employee.horizontalHeaderItem(0)
111         item.setText(_translate("SalarySheet", "员工"))
112         item = self.employee.horizontalHeaderItem(1)
113         item.setText(_translate("SalarySheet", "邮箱"))
114         item = self.salary.horizontalHeaderItem(0)
115         item.setText(_translate("SalarySheet", "部门"))
116         item = self.salary.horizontalHeaderItem(1)
117         item.setText(_translate("SalarySheet", "员工"))
118         self.salary_date.setDisplayFormat(_translate("SalarySheet", "yyyy/M"))
119         self.query.setText(_translate("SalarySheet", "发放查询"))
120 
121 
122 if __name__ == "__main__":
123     import sys
124     app = QtWidgets.QApplication(sys.argv)
125     MainWindow = QtWidgets.QMainWindow()
126     ui = Ui_MainWindow()
127     ui.setupUi(MainWindow)
128     MainWindow.show()
129     sys.exit(app.exec_())

View Code

　　3.完成excel读取模块　　　

 1 import xlrd
 2 
 3 def read_excel(file_name,file_type):
 4     workbook =  xlrd.open_workbook(file_name)
 5     sheet_names = workbook.sheet_names()
 6     #只读取sheet1
 7     sheet_name = sheet_names[0]
 8     sheet = workbook.sheet_by_name(sheet_name)
 9     result = []
10     if file_type == 'employee':
11         for i in range(sheet.nrows - 1):
12             result.append(sheet.row_values(i + 1))
13     elif file_type == 'salary':
14         result.append(str(int(sheet.row_values(0)[0])))
15         for i in range(sheet.nrows - 3):
16             result.append(sheet.row_values(i + 3))
17     return result
18 
19 if __name__ == '__main__':
20     print(read_excel('C:/Users/hasee/Desktop/工资条.xlsx','salary'))

View Code

　　4.完成数据库操作模块，数据库采用MYSQL　　　

　　　　①创建员工表和工资条表

 1 CREATE TABLE `employee` (
 2   `id` int(11) NOT NULL AUTO_INCREMENT,
 3   `name` varchar(255) DEFAULT NULL,
 4   `email` varchar(255) DEFAULT NULL,
 5   `state` varchar(255) DEFAULT NULL,
 6   `department` varchar(255) DEFAULT NULL,
 7   PRIMARY KEY (`id`)
 8 ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
 9 
10 CREATE TABLE `salarysheet` (
11   `id` int(11) NOT NULL AUTO_INCREMENT,
12   `salary_date` date DEFAULT NULL,
13   `department` varchar(255) DEFAULT NULL,
14   `name` varchar(255) DEFAULT NULL,
15   `jbgz` varchar(255) DEFAULT NULL,
16   `jxgz` varchar(255) DEFAULT NULL,
17   `zkxm` varchar(255) DEFAULT NULL,
18   `kg` varchar(255) DEFAULT NULL,
19   `cd` varchar(255) DEFAULT NULL,
20   `bj` varchar(255) DEFAULT NULL,
21   `sj` varchar(255) DEFAULT NULL,
22   `kqgz` varchar(255) DEFAULT NULL,
23   `jbgz2` varchar(255) DEFAULT NULL,
24   `xj` varchar(255) DEFAULT NULL,
25   `zfgjj` varchar(255) DEFAULT NULL,
26   `sbxj` varchar(255) DEFAULT NULL,
27   `grsds` varchar(255) DEFAULT NULL,
28   `dkdjhj` varchar(255) DEFAULT NULL,
29   `sfgz` varchar(255) DEFAULT NULL,
30   `yfjx` varchar(255) DEFAULT NULL,
31   `zkgz` varchar(255) DEFAULT NULL,
32   `bz` varchar(255) DEFAULT NULL,
33   `state` varchar(255) DEFAULT NULL,
34   PRIMARY KEY (`id`)
35 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

View Code

　　　　②完成数据库相关操作

  1 import pymysql.cursors
  2 
  3 def insert_employees(employees):
  4     # 连接MySQL数据库
  5     connection = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123', db='salarysheet',
  6                                  charset='utf8', cursorclass=pymysql.cursors.DictCursor)
  7     try:
  8         # 通过cursor创建游标
  9         cursor = connection.cursor()
 10         for employee in employees:
 11             if len(employee) != 4:
 12                 return str(employee)
 13             cursor.execute("select id from `employee` where state = 0 and `name` = '"+str(employee[2])+"' and email = '"+str(employee[3])+"' and department = '"+str(employee[1])+"'")
 14             if cursor.rowcount > 0:
 15                 continue
 16             # 创建sql 语句，并执行
 17             cursor.execute("INSERT INTO `employee` (`name`, `email`,`state`,`department`) VALUES ('"+str(employee[2])+"', '"+str(employee[3])+"','0','"+str(employee[1])+"')")
 18         # 提交SQL
 19         connection.commit()
 20         return ''
 21     except Exception as e:
 22         connection.rollback()
 23         return e
 24     finally:
 25         cursor.close()
 26         connection.close()
 27 
 28 def insert_salarys(salarys):
 29     # 连接MySQL数据库
 30     connection = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123', db='salarysheet',
 31                                  charset='utf8', cursorclass=pymysql.cursors.DictCursor)
 32     try:
 33         # 通过cursor创建游标
 34         cursor = connection.cursor()
 35         salary_date = salarys[0]
 36         for i in range(len(salarys)):
 37             if i == 0:
 38                 continue
 39             salary = salarys[i]
 40             if len(salary) != 21:
 41                 return str(salary)
 42             cursor.execute("select id from `salarysheet` where state = 0 and `name` = '"+salary[2]+"' and DATE_FORMAT(`salary_date`,'%Y%m') = '"+salary_date+"' and department = '"+salary[1]+"'")
 43             if cursor.rowcount > 0:
 44                 continue
 45             # 创建sql 语句，并执行
 46             cursor.execute("INSERT INTO `salarysheet` (`salary_date`,`department`,`name`,`jbgz`,`jxgz`,`zkxm`,`kg`,`cd`,`bj`,`sj`,`kqgz`,`jbgz2`,`xj`,`zfgjj`,`sbxj`,`grsds`,`dkdjhj`,`sfgz`,`yfjx`,`zkgz`,`bz`,`state`) "
 47                            +"VALUES (date_format('"+salary_date+"01', '%Y%m%d'), '"+str(salary[1])+"', '"+str(salary[2])+"', '"+str(salary[3])+"', '"+str(salary[4])+"', '"+str(salary[5])+"', '"+str(salary[6])+"', '"+str(salary[7])+"', '"+str(salary[8])+"', '"+str(salary[9])+"', '"
 48                            +str(salary[10])+"', '"+str(salary[11])+"', '"+str(salary[12])+"', '"+str(salary[13])+"', '"+str(salary[14])+"', '"+str(salary[15])+"', '"+str(salary[16])+"', '"+str(salary[17])+"', '"+str(salary[18])+"', '"+str(salary[19])+"', '"+str(salary[20])+"', '"+str(0)+"')")
 49         # 提交SQL
 50         connection.commit()
 51         return ''
 52     except Exception as e:
 53         connection.rollback()
 54         return e
 55     finally:
 56         cursor.close()
 57         connection.close()
 58 
 59 def delete_employee(name,email):
 60     # 连接MySQL数据库
 61     connection = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123', db='salarysheet',
 62                                  charset='utf8', cursorclass=pymysql.cursors.DictCursor)
 63     # 通过cursor创建游标
 64     cursor = connection.cursor()
 65     # 创建sql 语句，并执行
 66     sql = "update `employee` set `state` = 1 where `name` = '"+name+"' and `email` = '"+email+"'"
 67     cursor.execute(sql)
 68     # 提交SQL
 69     connection.commit()
 70     cursor.close()
 71     connection.close()
 72 
 73 def delete_salary(department,name):
 74     # 连接MySQL数据库
 75     connection = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123', db='salarysheet',
 76                                  charset='utf8', cursorclass=pymysql.cursors.DictCursor)
 77     # 通过cursor创建游标
 78     cursor = connection.cursor()
 79     # 创建sql 语句，并执行
 80     sql = "update `salarysheet` set `state` = 1 where `name` = '"+name+"' and `department` = '"+department+"'"
 81     cursor.execute(sql)
 82     # 提交SQL
 83     connection.commit()
 84     cursor.close()
 85     connection.close()
 86 
 87 def table_count(table):
 88     # 连接MySQL数据库
 89     connection = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123', db='salarysheet',
 90                                  charset='utf8', cursorclass=pymysql.cursors.DictCursor)
 91     # 通过cursor创建游标
 92     cursor = connection.cursor()
 93     # 创建sql 语句，并执行
 94     sql = "select id from `"+table+"` where state = 0"
 95     cursor.execute(sql)
 96     list = cursor.rowcount
 97     cursor.close()
 98     connection.close()
 99     return list
100 
101 
102 def select_table(table,date=None):
103     # 连接MySQL数据库
104     connection = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123', db='salarysheet',
105                                  charset='utf8', cursorclass=pymysql.cursors.DictCursor)
106     # 通过cursor创建游标
107     cursor = connection.cursor()
108     # 创建sql 语句，并执行
109     if table == 'employee':
110         sql = "select * from `"+table+"` where state = 0"
111     elif table == 'salarysheet':
112         sql = "select * from `" + table + "` where state = 0 and DATE_FORMAT(salary_date,'%Y%m') = '"+date+"'"
113     cursor.execute(sql)
114     list = cursor.fetchall()
115     cursor.close()
116     connection.close()
117     return list
118 
119 def formart_date(date):
120     ss = date.split('/')
121     return ss[0] + '0' + ss[1] if len(ss[1]) == 1 else ss[0] + ss[1]

View Code

　　5.完成邮件发送模块

 1 #! /usr/bin/env python
 2 # -*- coding: UTF-8 -*-
 3 import smtplib
 4 from email.mime.text import MIMEText
 5 mailto_list=['xxx@qq.com']           #收件人(列表)
 6 mail_host="smtp.163.com"            #使用的邮箱的smtp服务器地址，这里是163的smtp地址
 7 mail_user="xxx@163.com"                           #用户名
 8 mail_pass="xxx"                             #密码
 9 def send_mail(to_list,sub,content):
10     me = u'综合部'
11     msg = MIMEText(content,_subtype='html')
12     msg['Subject'] = sub
13     msg['From'] = me
14     msg['To'] = ";".join(to_list)                #将收件人列表以‘；’分隔
15     try:
16         server = smtplib.SMTP()
17         server.connect(mail_host)                            #连接服务器
18         server.login(mail_user,mail_pass)               #登录操作
19         server.sendmail(me, to_list, msg.as_string())
20         server.close()
21         print('发送成功')
22     except Exception as e:
23         print(e)
24 
25 def formart_mail_msg(m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,m13,m14,m15,m16,m17,m18,m19,m20):
26     return '''
27             <div></div><div><includetail><div style="font:Verdana normal 14px;color:#000;"><div style="position:relative;"><div><table border="0" cellpadding="0" cellspacing="0" width="1605" style="border-collapse:
28          collapse;width:1205pt"><colgroup><col width="72" style="width:54pt"><col width="82" style="mso-width-source:userset;mso-width-alt:2624;width:62pt"><col width="72" style="width:54pt"><col width="90" style="mso-width-source:userset;mso-width-alt:2880;width:68pt"><col width="82" style="mso-width-source:userset;mso-width-alt:2624;width:62pt"><col width="95" style="mso-width-source:userset;mso-width-alt:3040;width:71pt"><col width="59" style="mso-width-source:userset;mso-width-alt:1888;width:44pt"><col width="59" span="3" style="mso-width-source:userset;mso-width-alt:1888;
29          width:44pt"><col width="91" style="mso-width-source:userset;mso-width-alt:2912;width:68pt"><col width="83" style="mso-width-source:userset;mso-width-alt:2656;width:62pt"><col width="82" style="mso-width-source:userset;mso-width-alt:2624;width:62pt"><col width="88" style="mso-width-source:userset;mso-width-alt:2816;width:66pt"><col width="88" style="mso-width-source:userset;mso-width-alt:2816;width:66pt"><col width="88" span="2" style="mso-width-source:userset;mso-width-alt:2816;
30          width:66pt"><col width="82" style="mso-width-source:userset;mso-width-alt:2624;width:62pt"><col width="104" style="mso-width-source:userset;mso-width-alt:3328;width:78pt"><col width="82" style="mso-width-source:userset;mso-width-alt:2624;width:62pt"></colgroup><tbody><tr height="18" style="height:13.5pt"><td colspan="17" height="18" class="xl144" width="1337" style="height:13.5pt;
31           width:1003pt">xxx公司%s工资表</td>
32           <td class="xl132" width="82" style="width:62pt"></td>
33           <td class="xl133" width="104" style="width:78pt">　</td>
34           <td class="xl132" width="82" style="width:62pt"></td>
35          </tr>
36          <tr height="18" style="mso-height-source:userset;height:13.5pt">
37           <td rowspan="2" class="xl98">部门</td>
38           <td rowspan="2" class="xl98">姓名</td>
39           <td colspan="10" class="xl146" style="border-right:.5pt solid black">当月工资</td>
40           <td colspan="4" class="xl98" style="border-left:none">代扣代缴项</td>
41           <td rowspan="2" class="xl99">实发工资</td>
42           <td rowspan="2" class="xl143" width="104" style="width:78pt">已发绩效<br>
43             （扣除）</td>
44           <td rowspan="2" class="xl100" width="82" style="width:62pt">转卡工资</td>
45          </tr>
46          <tr height="18" style="height:13.5pt">
47           <td height="18" class="xl118" style="height:13.5pt;border-top:none">基础工资</td>
48           <td class="xl74" style="border-top:none;border-left:none">绩效工资</td>
49           <td class="xl74" style="border-top:none;border-left:none">增扣项目</td>
50           <td class="xl74" style="border-top:none;border-left:none">旷工</td>
51           <td class="xl98" style="border-top:none;border-left:none">迟到</td>
52           <td class="xl98" style="border-top:none;border-left:none">病假</td>
53           <td class="xl99" style="border-top:none;border-left:none">事假</td>
54           <td class="xl95" style="border-top:none;border-left:none">考勤工资（扣发）</td>
55           <td class="xl100" width="83" style="border-top:none;border-left:none;width:62pt">加班工资</td>
56           <td class="xl93" style="border-top:none;border-left:none">小计</td>
57           <td class="xl135" style="border-top:none;border-left:none">社保小计</td>
58           <td class="xl136" style="border-top:none;border-left:none">公积金小计</td>
59           <td class="xl98" style="border-top:none;border-left:none">个人所得税</td>
60           <td class="xl98" style="border-top:none;border-left:none">代扣代缴合计</td>
61          </tr>
62          <tr height="18" style="height:13.5pt">
63           <td class="xl127" width="82" style="border-top:none;width:62pt">%s</td>
64           <td class="xl127" width="72" style="border-top:none;border-left:none;width:54pt">%s</td>
65           <td class="xl118" style="border-top:none">%s</td>
66           <td class="xl128" style="border-top:none;border-left:none">%s</td>
67           <td class="xl118" style="border-top:none">　</td>
68           <td class="xl74" style="border-top:none;border-left:none">%s</td>
69           <td class="xl98" style="border-top:none;border-left:none">%s</td>
70           <td class="xl98" style="border-top:none;border-left:none">%s</td>
71           <td class="xl98" style="border-top:none;border-left:none">%s</td>
72           <td class="xl95" style="border-top:none;border-left:none">%s</td>
73           <td class="xl99" style="border-top:none;border-left:none">%s</td>
74           <td class="xl93" style="border-top:none;border-left:none">%s</td>
75           <td class="xl135" style="border-top:none;border-left:none">%s</td>
76           <td class="xl137" style="border-left:none">%s</td>
77           <td class="xl101" style="border-left:none">%s</td>
78           <td class="xl98" style="border-top:none;border-left:none">%s</td>
79           <td class="xl73" style="border-top:none;border-left:none">%s</td>
80           <td class="xl134" style="border-top:none">%s</td>
81           <td class="xl99" style="border-top:none;border-left:none">%s</td>
82          </tr></tbody></table></div><div><br></div><div><tincludetail></tincludetail></div></div></div><!--<![endif]--></includetail></div>
83         ''' % (m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,m13,m14,m15,m16,m17,m18,m19,m20)

View Code

　　6.完成按钮事件绑定及相关处理（邮件发送功能暂未完成，原因嘛，就是突然不想写了，任性）

  1 # -*- coding: utf-8 -*-
  2 
  3 """
  4 Module implementing buttonWindows.
  5 """
  6 
  7 from PyQt5.QtCore import pyqtSlot
  8 from PyQt5.QtWidgets import QMainWindow
  9 from PyQt5 import QtWidgets
 10 from Ui_main import Ui_MainWindow
 11 import sys
 12 from salarysql import *
 13 from excel_utils import *
 14 from mail_utils import *
 15 
 16 
 17 class buttonWindows(QMainWindow, Ui_MainWindow):
 18 
 19     def __init__(self, parent=None):
 20         super(buttonWindows, self).__init__(parent)
 21         self.setupUi(self)
 22         self.init_employee()
 23         self.init_salary()
 24 
 25     # 初始化employee
 26     def init_employee(self):
 27         employees = select_table('employee')
 28         employee_count = len(employees)
 29         self.employee_count.setText(str(employee_count))
 30         self.employee.setRowCount(employee_count)
 31         for i in range(len(employees)):
 32             self.employee.setItem(i, 0, QtWidgets.QTableWidgetItem(str(employees[i].get('name'))))
 33             self.employee.setItem(i, 1, QtWidgets.QTableWidgetItem(employees[i].get('email')))
 34 
 35     # 初始化employee
 36     def init_salary(self):
 37         # 初始化salary
 38         salary_date = formart_date(self.salary_date.text())
 39         salarysheets = select_table('salarysheet',salary_date)
 40         salarysheet_count = len(salarysheets)
 41         self.salary_count.setText(str(salarysheet_count))
 42         self.salary.setRowCount(salarysheet_count)
 43         for i in range(len(salarysheets)):
 44             self.salary.setItem(i, 1, QtWidgets.QTableWidgetItem(salarysheets[i].get('name')))
 45             self.salary.setItem(i, 0, QtWidgets.QTableWidgetItem(salarysheets[i].get('department')))
 46 
 47     @pyqtSlot()
 48     def on_import_employee_clicked(self):
 49         fileName, filetype = QtWidgets.QFileDialog.getOpenFileName(self,"选取文件","C:/","All Files (*)")
 50         try:
 51             employees = read_excel(fileName,'employee')
 52             msg = insert_employees(employees)
 53             if msg == '':
 54                 QtWidgets.QMessageBox.information(self, '提示', '导入成功！！！')
 55             else:
 56                 QtWidgets.QMessageBox.information(self, '警告', '导入失败，请确认excel文件排版！！！\n'+str(msg))
 57         except Exception as e:
 58             QtWidgets.QMessageBox.information(self,'警告','导入失败，请确认文件格式！！！\n'+str(e))
 59         self.init_employee()
 60 
 61     @pyqtSlot()
 62     def on_delete_employee_clicked(self):
 63         cid = self.employee.currentRow()
 64         name = self.employee.item(cid, 0).text()
 65         email = self.employee.item(cid, 1).text()
 66         delete_employee(name, email)
 67         self.init_employee()
 68 
 69     @pyqtSlot()
 70     def on_import_salary_clicked(self):
 71         fileName, filetype = QtWidgets.QFileDialog.getOpenFileName(self,"选取文件","C:/","All Files (*)")
 72         try:
 73             salarys = read_excel(fileName,'salary')
 74             msg = insert_salarys(salarys)
 75             if msg == '':
 76                 QtWidgets.QMessageBox.information(self, '提示', '导入成功！！！')
 77             else:
 78                 QtWidgets.QMessageBox.information(self, '警告', '导入失败，请确认excel文件排版！！！\n'+str(msg))
 79         except Exception as e:
 80             QtWidgets.QMessageBox.information(self,'警告','导入失败，请确认文件格式！！！\n'+str(e))
 81         self.init_salary()
 82 
 83     @pyqtSlot()
 84     def on_delete_salary_clicked(self):
 85         cid = self.salary.currentRow()
 86         department = self.salary.item(cid, 0).text()
 87         name = self.salary.item(cid, 1).text()
 88         delete_salary(department, name)
 89         self.init_salary()
 90 
 91 
 92     @pyqtSlot()
 93     def on_send_clicked(self):
 94         reply = QtWidgets.QMessageBox.question(self, '确认', '你确定要发送吗?', QtWidgets.QMessageBox.Yes | QtWidgets.QMessageBox.No)
 95         if reply == QtWidgets.QMessageBox.Yes:
 96             if int(self.employee_count.text()) == 0:
 97                 QtWidgets.QMessageBox.information(self, '提示', '员工数不能为0！！！')
 98                 return
 99             elif int(self.salary_count.text()) == 0:
100                 QtWidgets.QMessageBox.information(self, '提示', '工资条数不能为0！！！')
101                 return
102 
103 
104     @pyqtSlot()
105     def on_query_clicked(self):
106         self.init_salary()
107 
108 
109 if __name__ == "__main__":
110     app = QtWidgets.QApplication(sys.argv)
111     bw = buttonWindows()
112     bw.show()
113     sys.exit(app.exec_())