Java教程

数据库结课作业——校园失物招领系统

本文主要是介绍数据库结课作业——校园失物招领系统,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

目录

  • 杂七杂八
    • 起因
    • 项目环境
    • 前端界面的选择
  • 数据库理论阶段
    • 数据库模型
  • 代码实现
    • 相关功能
    • UI设计
    • 界面展示
    • 项目总文件
    • 项目代码(ui总文件在中)

杂七杂八

起因

因为数据库实验结课作业要结合理论课和具体实现来完成,而老师给的题目中,只有这个很简单且没多少人选,再加上有现成的案例来“供我们借鉴”——支付宝完美校园中的失物招领系统,所以有了这么个粗制滥造。

项目环境

pycharm +sql server
python简单,加上sql server的可视化界面十分友好,所以选择这两位搭配。

前端界面的选择

由于pyqt5的简单易上手,且同时有qt designer的可视化,pyqt5当之无愧。

数据库理论阶段

数据库模型

易得三个实体——用户、管理员、物品,再结合现实失物招领的特点,我们赋予相关实体联系以及各自的属性,其中有特点的是信誉等级这个属性。具体的sql语句如下:

CREATE TABLE 用户
(
账号 nvarchar(10) PRIMARY KEY, 
姓名 nvarchar(10) NOT NULL,
密码 nvarchar(20) NOT NULL DEFAULT '123456',
网络匿称 nvarchar(20) NOT NULL DEFAULT '神秘用户',
联系方式 nvarchar(20) NOT NULL UNIQUE,
二次密码 nvarchar(10),
信誉等级 nvarchar(10)  DEFAULT '0',
认领成功数 nvarchar(10) DEFAULT '0'
)
CREATE TABLE 物品
(
物品id nvarchar(20) PRIMARY KEY ,
档案号 int identity(1,1) ,
案件性质 nvarchar(4) CHECK (案件性质 IN('失物','招领')),
种类 nvarchar(10) CHECK (种类 IN('书籍/文具','证件','雨伞/钥匙','电子产品','其他')),
时间 date DEFAULT (GETDATE()) ,
区域 nvarchar(10) CHECK (区域 IN('A区','B区','C区','D区','其他')), 
相关描述 nvarchar(40) DEFAULT '用户暂未添加相关描述',
物品状态  nvarchar(10) CHECK (物品状态 IN('处理中','已找回','无人认领'))
)

CREATE TABLE 管理员
(
账号 nvarchar(10) PRIMARY KEY,
密码 nvarchar(20) NOT NULL DEFAULT '123456789',
区域 nvarchar(10) CHECK (区域 IN('A区','B区','C区','D区','其他')), 
二次密码 nvarchar(10)
)
CREATE TABLE 认领关系
(
物品id nvarchar(20) FOREIGN KEY REFERENCES 物品(物品id),
发布人账号  nvarchar(10) FOREIGN KEY REFERENCES 用户(账号),
认领人账号  nvarchar(10) FOREIGN KEY REFERENCES 用户(账号)
)

代码实现

相关功能

我们仨讨论我们的系统应该有哪些功能时,确实粗现了一些分歧,主要是我想要的功能太多——插入图片、信誉等级(荣誉系统)、物品状态的实时更新,舍去这些功能后,我们最开始的版本的功能如下:

  • 用户/管理员登录
  • 用户注册
  • 用户找回密码
  • 用户发布失物/招领
  • 用户修改自己的网络昵称/密码
  • 用户删除自己的发布
  • 用户筛选物品
  • 管理员删除用户发布
  • 管理员筛选物品
  • 管理员筛选用户
  • 管理员修改物品的相关信息
  • 管理员修改用户的密码
  • 管理员获得年度的统计信息(折线图、饼状图、柱状图)
  • 管理员导出物品/用户信息(excel)
    具体的实现代码会在最后。最终的代码我自己还是修改了一下,加入了我自己想要的几个功能(图片我懒得弄了哈哈)。

UI设计

pyqt5的逻辑很简单,按钮与槽函数(功能函数)的关联,我们在ui文件上摆摆样子,能实现最简单的逻辑交互就好。

界面展示

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

项目总文件

提取码:aaaa

项目代码(ui总文件在中)

import pymssql    
import random  
import time   
import pyqtgraph    
from PyQt5.QtWidgets import  QMessageBox,QTableWidgetItem,QTableWidget,QInputDialog,QMainWindow,QApplication, QWidget, QVBoxLayout
from PyQt5.uic import loadUi as QUiLoader
from PyQt5.QtCore import QRegExp,QDate,Qt
from PyQt5.QtChart import QChart, QChartView, QBarSet, QPercentBarSeries, QBarCategoryAxis, QPieSeries, QPieSlice
from PyQt5.QtGui import QPainter,QIcon,QRegExpValidator, QIntValidator
import  xlwt   
 
from pymssql import _mssql
from pymssql import _pymssql
import uuid
import decimal


class MainUi(QWidget):
    def __init__(self):
        super().__init__()
        self.visitor = pymssql.connect('127.0.0.1', 'sa', 'agzsyg','test', autocommit=True)
        self.cursor = self.visitor.cursor()
        self.zhanghao = '1'
        self.op = -1
        self.yonghujiemian=None
        self.guanliyuanjiemian0=None
        self.Login_ui()
        self.Forget_password_ui()
        self.Register_ui()

    def Login_ui(self):   
        self.denglujiemian = QUiLoader('游客_登录.ui')
        self.denglujiemian.zhanghaoshuru.setValidator(QRegExpValidator(QRegExp('[0-9]{10}$')))
        self.denglujiemian.denglu.clicked.connect(self.Login)
        self.denglujiemian.wangjimima.clicked.connect(self.Forget_password)
        self.denglujiemian.zhuce.clicked.connect(self.Register)
        self.denglujiemian.mimashuru.returnPressed.connect(self.Login)
    def Forget_password_ui(self):   
        self.zhaohuimima = QUiLoader('游客_忘记密码.ui')
        self.zhaohuimima.a.setValidator(QRegExpValidator(QRegExp('[0-9]{10}$')))
        self.zhaohuimima.b.setValidator(QRegExpValidator(QRegExp('[0-9]{10}$')))
        self.zhaohuimima.c.setValidator(QRegExpValidator(QRegExp("^[A-Za-z0-9]{20}$")))
        self.zhaohuimima.g.setValidator(QIntValidator())
        self.zhaohuimima.e.clicked.connect(lambda: self.Random_operation(self.zhaohuimima.f))
        self.zhaohuimima.h.clicked.connect(self.Password_retrieval)
        self.zhaohuimima.fanhui.clicked.connect(self.Return_login_ui)
    def Register_ui(self):   
        self.zhucejiemian = QUiLoader('游客_注册.ui')
        self.zhucejiemian.a.setValidator(QRegExpValidator(QRegExp('[0-9]{10}$')))
        self.zhucejiemian.b.setValidator(QRegExpValidator(QRegExp('[0-9]{11}$')))
        self.zhucejiemian.c.setValidator(QRegExpValidator(QRegExp("^[A-Za-z0-9]{20}$")))
        self.zhucejiemian.d.setValidator(QRegExpValidator(QRegExp("^[A-Za-z0-9]{20}$")))
        self.zhucejiemian.j.setValidator(QRegExpValidator(QRegExp("^[A-Za-z0-9]{20}$")))
        self.zhucejiemian.f.setValidator(QIntValidator())
        self.zhucejiemian.g.clicked.connect(self.Registration_test)
        self.zhucejiemian.h.clicked.connect(lambda: self.Random_operation(self.zhucejiemian.e))
        self.zhucejiemian.fanhui.clicked.connect(self.Return_login_ui)
    def Return_login_ui(self):
        self.Login_ui()
        self.Register_ui()
        self.Forget_password_ui()
        self.User_ui()
        self.denglujiemian.show()

    def Login(self):   
        zhanghao = self.denglujiemian.zhanghaoshuru.text()
        mima = self.denglujiemian.mimashuru.text()
        denglushenfen = self.denglujiemian.denglushenfen.currentText().strip()
        self.denglujiemian.close()
        if zhanghao != '' and mima != '':
            if denglushenfen =='用户':
                self.cursor.execute("select 密码,二次密码,信誉等级,认领成功数 from %s where 账号='%s'" % (denglushenfen, zhanghao))
            else:
                self.cursor.execute("select 密码,二次密码 from %s where 账号='%s'" % (denglushenfen, zhanghao))

            result = self.cursor.fetchone()
            if result and mima == str(result[0]).strip():
                self.zhanghao = str(zhanghao).strip()
                self.mibao = str(result[1])
                if denglushenfen =='用户':
                    self.xinyudengji = int(result[2])
                    self.renlingchenggongshu = int(result[3])

                self.denglujiemian.close()
                if denglushenfen == '用户':
                    self.User_ui()
                    self.yonghujiemian.show()
                else:

                    self.Admin_ui1()
                    self.Admin_ui2()
                    self.Admin_ui0()
                    self.guanliyuanjiemian0.show()
            else:
                self.denglujiemian.zhanghaoshuru.clear()
                self.denglujiemian.mimashuru.clear()
                QMessageBox.warning(None, '输入错误!', '账号或密码错误!', QMessageBox.Close)
                self.denglujiemian.show()
        elif zhanghao == '' or mima == '':
            self.denglujiemian.zhanghaoshuru.clear()
            self.denglujiemian.mimashuru.clear()
            QMessageBox.warning(None, '输入错误!', '请输入有效的账号或密码!', QMessageBox.Close)
            self.denglujiemian.show()


    def Forget_password(self):   
        self.denglujiemian.close()
        self.zhaohuimima.show()
    def Register(self):   
        self.denglujiemian.close()
        self.zhucejiemian.show()
    def Random_operation(self, x):   
        random.seed(time.time())
        a = random.randint(0, 50)
        b = random.randint(0, 50)
        c = random.choice(["+"])
        x.setPlaceholderText(" " * 6 + str(a) + c + str(b))
        if c == '+':
            self.op = a + b
    def reRandom_operation(self, x, y):   
        x.clear()
        self.Random_operation(y)
    def Password_retrieval(self):   
        zhanghao = self.zhaohuimima.a.text()
        dianhua = self.zhaohuimima.b.text()
        mibao = self.zhaohuimima.c.text()
        answer = self.zhaohuimima.g.text()
        if answer == str(self.op) and zhanghao and dianhua and answer and mibao:
            self.cursor.execute("select 联系方式,二次密码 from 用户 where 账号='%s'" % (zhanghao))
            result = self.cursor.fetchone()
            if result and mibao == str(result[1]):
                self.cursor.execute(" UPDATE 用户 SET 用户.密码 = '%s' WHERE 用户.账号 ='%s'" % (zhanghao[-6:], zhanghao))
                QMessageBox.information(None, '密码找回成功', '密码已修改为账号后六位,为了安全,请你尽早修改!', QMessageBox.Close)
            else:
                QMessageBox.warning(None, '错误', '请你检查账号与电话以及二次密码是否正确!', QMessageBox.Close)
                self.Return_login_ui()
        elif not (zhanghao and dianhua and mibao):
            QMessageBox.warning(None, '输入错误!', '相关信息未填写!', QMessageBox.Close)
            self.reRandom_operation(self.zhaohuimima.g, self.zhaohuimima.f)
        elif answer == '':
            QMessageBox.warning(None, '未经验证', '请输入正确的答案', QMessageBox.Close)
            self.reRandom_operation(self.zhaohuimima.g, self.zhaohuimima.f)
        elif answer != str(self.op):
            QMessageBox.warning(None, '答案错误!', '请重新输入正确的答案!', QMessageBox.Close)
            self.reRandom_operation(self.zhaohuimima.g, self.zhaohuimima.f)
        else:
            QMessageBox.warning(None, '输入错误!', '填写错误!', QMessageBox.Close)
            self.reRandom_operation(self.zhaohuimima.g, self.zhaohuimima.f)
    def Registration_test(self):   
        zhanghao = self.zhucejiemian.a.text()
        name = self.zhucejiemian.i.text()
        dianhua = self.zhucejiemian.b.text()
        answer = self.zhucejiemian.f.text()
        mibao = self.zhucejiemian.j.text()
        mima = self.zhucejiemian.c.text()
        mima0 = self.zhucejiemian.d.text()
        self.cursor.execute(" select  账号,联系方式 from 用户 where 用户.账号='%s' or 用户.联系方式='%s'" % (zhanghao, dianhua))
        result = self.cursor.fetchone()
        if result:
            QMessageBox.warning(None, '注册失败!', '账户或手机号码已注册!', QMessageBox.Close)
            self.reRandom_operation(self.zhucejiemian.f, self.zhucejiemian.e)
        else:

            if answer == str(self.op) and mima == mima0 and zhanghao and mima and mima0 and dianhua and answer:
                if len(mima) < 6 or len(mima0) < 6 or len(zhanghao) < 6:
                    QMessageBox.warning(None, '注册失败!', '安全强度低!账号和密码需设置为六位或以上!', QMessageBox.Close)
                else:
                    self.cursor.execute("insert into 用户(账号,姓名,密码,联系方式,二次密码) VALUES ('%s','%s','%s','%s','%s')" % (
                        zhanghao, name, mima, dianhua, mibao))
                    QMessageBox.information(None, '注册成功!', '现在您可以登录您的账户', QMessageBox.Close)
                    self.reRandom_operation(self.zhucejiemian.f, self.zhucejiemian.e)
                    self.Return_login_ui()

            elif not (zhanghao and mima and mima0 and dianhua):
                QMessageBox.warning(None, '输入错误!', '相关信息未填写!', QMessageBox.Close)
                self.reRandom_operation(self.zhucejiemian.f, self.zhucejiemian.e)
            elif answer == '':
                QMessageBox.warning(None, '未经验证', '请输入正确的答案', QMessageBox.Close)
                self.reRandom_operation(self.zhucejiemian.f, self.zhucejiemian.e)
            elif answer != str(self.op):
                QMessageBox.warning(None, '答案错误!', '请重新输入正确的答案!', QMessageBox.Close)
                self.reRandom_operation(self.zhucejiemian.f, self.zhucejiemian.e)
            elif mima != mima0:
                QMessageBox.warning(None, '注意!', '两次密码不一致!', QMessageBox.Close)
                self.reRandom_operation(self.zhucejiemian.f, self.zhucejiemian.e)
            else:
                QMessageBox.warning(None, '输入错误!', '填写错误!', QMessageBox.Close)
                self.reRandom_operation(self.zhucejiemian.f, self.zhucejiemian.e)

    def User_ui(self):
        self.yonghujiemian = QUiLoader('用户_失物招领大厅.ui')
        self.yonghujiemian.shijian.setDate(QDate.currentDate())
        self.yonghujiemian.chaxun.clicked.connect(lambda :self. Information_screening(self.yonghujiemian))
        self.yonghujiemian.anjianxingzhi.currentIndexChanged.connect(lambda :self. Information_screening(self.yonghujiemian))
        self.yonghujiemian.quyu.currentIndexChanged.connect(lambda :self. Information_screening(self.yonghujiemian))
        self.yonghujiemian.wupingzhonglei.currentIndexChanged.connect(lambda :self. Information_screening(self.yonghujiemian))
        self.yonghujiemian.renling.clicked.connect(self.Renling)
        self.yonghujiemian.fabu.clicked.connect(self.Publishing_ui)
        self.yonghujiemian.geren.clicked.connect(self.Personal_information_ui)
        self.yonghujiemian.tuichu.clicked.connect( self.Return_login_ui)
        self.yonghujiemian.shuaxin.clicked.connect(lambda :self.Refresh(self.yonghujiemian))
        sql="select 档案号,案件性质,种类,时间,区域,相关描述 from 物品 where 物品状态='无人认领' "
        self.Sql_serach(sql,self.yonghujiemian.shiwuzhaoling)

    def Personal_information_ui(self):   
        self.yonghugerenxinxi = QUiLoader('用户_个人信息.ui')
        self.yonghujiemian.close()

        self.Release()
        self.yonghugerenxinxi.wupingzhuangtai.currentIndexChanged.connect(self.Release)
        self.yonghugerenxinxi.shanchu.clicked.connect(self.Delete_user)
        self.yonghugerenxinxi.name.clicked.connect(lambda: self.Modify_user('name'))   
        self.yonghugerenxinxi.mima.clicked.connect(lambda: self.Modify_user('mima'))   
        self.yonghugerenxinxi.chenggong.clicked.connect(self.Success_user)
        self.yonghugerenxinxi.shibai.clicked.connect(self.Fail_user)
        self.yonghugerenxinxi.fanhui.clicked.connect(self.Fanhui_user)
        self.cursor.execute("select 姓名,联系方式,网络匿称,信誉等级,认领成功数 from 用户 where 账号='%s'" % (self.zhanghao))
        result = self.cursor.fetchone()
        self.yonghugerenxinxi.a.setText(self.zhanghao)
        self.yonghugerenxinxi.b.setText(result[0])
        self.yonghugerenxinxi.c.setText(result[1])
        self.yonghugerenxinxi.d.setText(result[2])
        self.yonghugerenxinxi.e.setText(result[3])
        self.yonghugerenxinxi.f.setText(result[4])
        self.yonghugerenxinxi.show()


    def Publishing_ui(self):
        self.fabujiemian = QUiLoader('用户_发布.ui')
        self.fabujiemian.dateEdit_shijian.setDate(QDate.currentDate())
        self.fabujiemian.show()
        self.fabujiemian.Button_tijiao.clicked.connect(self.Issue_lost_and_found)
    



        
    def Release(self):   

        wupingzhuangtai = self.yonghugerenxinxi.wupingzhuangtai.currentText().strip()
        if wupingzhuangtai=="无人认领":
            sql = "select 档案号,案件性质,种类,时间,区域,相关描述,发布人账号 from  物品,认领关系 where 物品.物品状态='%s' and 物品.物品id=认领关系.物品id and (认领关系.发布人账号='%s' or 认领关系.认领人账号='%s')" % (
                wupingzhuangtai, self.zhanghao, self.zhanghao)
        else:
            sql = "select 档案号,案件性质,种类,时间,区域,相关描述,发布人账号,认领人账号,联系方式 from  物品,认领关系,用户 where 认领人账号=账号 and 物品.物品状态='%s' and 物品.物品id=认领关系.物品id and (认领关系.发布人账号='%s' or 认领关系.认领人账号='%s')" % (
                wupingzhuangtai, self.zhanghao, self.zhanghao)
        self.Sql_serach(sql,self.yonghugerenxinxi.shiwuzhaoling)
       
    def Renling(self):
        danganhao = self.yonghujiemian.danganhao.text() if self.yonghujiemian.danganhao.text() else ""

        if danganhao:
            if self.xinyudengji < 0:
                QMessageBox.information(None, '失败', '您的信誉等级太低!请联系管理员!', QMessageBox.Ok)
            else:
                self.cursor.execute(
                    "select 物品.物品id,发布人账号 from 物品,认领关系 where 认领关系.物品id=物品.物品id and 物品.档案号=%s" % (danganhao))
                self.result = self.cursor.fetchall()
                if self.result:
                    sql = "update 认领关系 set 认领人账号='%s' where 物品id='%s' and 发布人账号='%s'" % (
                        self.zhanghao, self.result[0][0], self.result[0][1])
                    self.cursor.execute(sql)
                    sql = "update 物品 set 物品状态='%s' where 档案号='%s' " % ('处理中', danganhao)
                    self.cursor.execute(sql)
                    QMessageBox.information(None, '成功', '认领成功,为您重新载入', QMessageBox.Ok)
                    self.Refresh(self.yonghujiemian)

                else:
                    QMessageBox.information(None, '失败', '档案号错误!', QMessageBox.Ok)

        else:
            QMessageBox.information(None, '失败', '请输入档案号!', QMessageBox.Ok)
    def Issue_lost_and_found(self):
        if self.xinyudengji < 0:
            QMessageBox.information(None, '失败', '您的信誉等级太低!请联系管理员!', QMessageBox.Ok)
        else:
            t = time.time()
            wupingid = str(t)
            miaoshu1 = self.fabujiemian.lineEdit_miaoshu.text()
            quyu2 = self.fabujiemian.comboBox_quyu.currentText().strip()
            fenlei = self.fabujiemian.comboBox_fenlei.currentText().strip()
            xingzhi = self.fabujiemian.comboBox_xingzhi.currentText().strip()
            shijian = self.fabujiemian.dateEdit_shijian.date().toString("yyyy-MM-dd")

            sql1 = "INSERT INTO 物品 (物品id,案件性质,种类,时间,区域,相关描述,物品状态) VALUES ('" + wupingid + "','" + xingzhi + "','" + fenlei + "','" + shijian + "','" + quyu2 + "','" + miaoshu1 + "','" + '无人认领' + "')"

            sql2 = "INSERT INTO 认领关系 (物品id,发布人账号) VALUES ('" + wupingid + "','" + self.zhanghao + "')"
            self.cursor.execute(sql1)
            self.cursor.execute(sql2)

            QMessageBox.information(None, '成功', '发布成功!', QMessageBox.Close)

            self.fabujiemian.close()
            self.User_ui()
            self.yonghujiemian.show()
    def Delete_user(self):
        danganhao = self.yonghugerenxinxi.danganhao.text()
        if not len(danganhao) == 0:
            yanzhen, ok = QInputDialog.getText(None, '确认删除',
                                               '要删除的物品档案号为:%s,请输入您的二次密码进行验证' % danganhao)   
            if yanzhen:
                if yanzhen == self.mibao:   
                    self.cursor.execute("select 物品状态,发布人账号,物品.物品id from 物品,认领关系 where 档案号='%s' and 物品.物品id=认领关系.物品id"%(danganhao))
                    self.result = self.cursor.fetchall()
                    if self.result:
                        if self.result[0][0] == '无人认领' and self.result[0][1] == self.zhanghao:
                            self.cursor.execute("delete from 认领关系 where 物品id='%s'" % (self.result[0][2]))
                            self.cursor.execute("delete from 物品 where 物品id='%s'" % (self.result[0][2]))
                            QMessageBox.information(None, '成功', '删除成功,为您重新载入', QMessageBox.Ok)
                            self.yonghugerenxinxi.danganhao.clear()
                            self.Release()   

                        else:
                            QMessageBox.information(None, '失败', '删除失败!', QMessageBox.Ok)
                    else:
                        QMessageBox.information(None, '失败', '档案号输入错误!', QMessageBox.Ok)
                else:
                    QMessageBox.warning(None, '失败', '您的二次密码输入错误!', QMessageBox.Ok)
    def Success_user(self):
        danganhao = self.yonghugerenxinxi.danganhao.text()
        if not len(danganhao) == 0:
            yanzhen, ok = QInputDialog.getText(None, '确认物归原主了吗?',
                                               '要修改的物品档案号为:%s,请输入您的二次密码进行验证' % danganhao)   
            if yanzhen:
                if yanzhen == self.mibao:
                    self.cursor.execute(
                        "select 物品状态,发布人账号,物品.物品id from 物品,认领关系 where 档案号='%s' and 物品.物品id=认领关系.物品id" % (danganhao))
                    self.result = self.cursor.fetchall()
                    if self.result:
                        if self.result[0][0] == '处理中' and self.result[0][1] == self.zhanghao:
                            self.cursor.execute("update 物品 set 物品状态='已找回'where 物品id='%s'" % (self.result[0][2]))
                            QMessageBox.information(None, '成功', '恭喜物归原主!为您重新载入', QMessageBox.Ok)
                            self.renlingchenggongshu+=1
                            self.xinyudengji+=1
                            self.cursor.execute("update 用户 set 信誉等级= '%s',认领成功数='%s' where 账号='%s'" % (str(self.xinyudengji),str(self.renlingchenggongshu),self.zhanghao))
                            self.Personal_information_ui()
                            self.yonghugerenxinxi.show()
                        else:
                            QMessageBox.information(None, '失败', '修改失败', QMessageBox.Ok)
                    else:
                        QMessageBox.information(None, '失败', '档案号输入错误!', QMessageBox.Ok)
                else:
                    QMessageBox.warning(None, '失败', '您的二次密码输入错误!', QMessageBox.Ok)
    def Fail_user(self):
        danganhao = self.yonghugerenxinxi.danganhao.text()
        if not len(danganhao) == 0:
            yanzhen, ok = QInputDialog.getText(None, '您确认取得联系并核实了吗?','要修改的物品档案号为:%s,请输入您的二次密码进行验证' % danganhao)
            if yanzhen:
                if yanzhen == self.mibao:
                    self.cursor.execute(
                        "select 物品状态,发布人账号,物品.物品id,认领人账号 from 物品,认领关系 where 档案号='%s' and 物品.物品id=认领关系.物品id" % (danganhao))
                    self.result = self.cursor.fetchall()
                    renlingrenzhanghao=self.result[0][3]
                    if self.result:
                        if self.result[0][0] == '处理中' and self.result[0][1] == self.zhanghao:
                            self.cursor.execute("update 物品 set 物品状态='无人认领'where 物品id='%s'" % (self.result[0][2]))
                            sql = "update 认领关系 set 认领人账号=NULL where 物品id='%s' and 发布人账号='%s'" % (self.result[0][2], self.result[0][1])
                            self.cursor.execute(sql)
                            self.cursor.execute("select 信誉等级 from 用户 where 账号 = '%s'"%(renlingrenzhanghao))
                            self.result=self.cursor.fetchone()
                            self.cursor.execute("update 用户 set 信誉等级= '%s' where 账号='%s'" % (str(int(self.result[0])-1),renlingrenzhanghao))
                            QMessageBox.information(None, '成功', '感谢您的付出!为您重新载入', QMessageBox.Ok)
                            self.Personal_information_ui()
                            self.yonghugerenxinxi.show()
                        else:
                            QMessageBox.information(None, '失败', '修改失败', QMessageBox.Ok)
                    else:
                        QMessageBox.information(None, '失败', '档案号输入错误!', QMessageBox.Ok)
                else:
                    QMessageBox.warning(None, '失败', '您的二次密码输入错误!', QMessageBox.Ok)
    def Fanhui_user(self):
        self.yonghugerenxinxi.close()
        self.User_ui()
        self.yonghujiemian.show()
    def Modify_user(self, x):
        if x == 'name':
            nicheng, ok = QInputDialog.getText(None, '修改', '请输入修改后的网络匿称')   
            if nicheng:
                yanzhen, ok = QInputDialog.getText(None, '确认修改', '请输入您的二次密码进行验证')   
                if yanzhen:
                    if yanzhen == self.mibao:   
                        self.cursor.execute(
                            " UPDATE 用户 SET 用户.网络匿称 = '%s' WHERE  用户.账号 = '%s' " % (nicheng, self.zhanghao))
                        QMessageBox.information(None, '成功', '修改成功,为您重新载入', QMessageBox.Ok)   
                        self.yonghugerenxinxi.shiwuzhaoling.clear()   
                        self.Personal_information_ui()   
                        self.yonghugerenxinxi.show()
                    else:
                        QMessageBox.warning(None, '失败', '您的二次密码输入错误!', QMessageBox.Ok)


        else:
            mima, ok = QInputDialog.getText(None, '修改', '请输入修改后的密码')   
            if mima:
                yanzhen, ok = QInputDialog.getText(None, '确认修改', '请输入您的二次密码进行验证')   
                if yanzhen:
                    if yanzhen == self.mibao:   
                        self.cursor.execute(
                            " UPDATE 用户 SET 用户.密码 = '%s' WHERE  用户.账号 = '%s' " % (mima, self.zhanghao))
                        QMessageBox.information(None, '成功', '修改成功,请重新登录!', QMessageBox.Ok)
                        self.yonghugerenxinxi.close()
                        self.denglujiemian.show()
                    else:
                        QMessageBox.warning(None, '失败', '您的二次密码输入错误!', QMessageBox.Ok)

    def Admin_ui0(self):

        self.guanliyuanjiemian0 = QUiLoader('管理员_用户发布管理.ui')
        self.guanliyuanjiemian0.a.setValidator(QRegExpValidator(QRegExp('[0-9]{10}$')))
        self.guanliyuanjiemian0.shijian.setDate(QDate.currentDate())
        self.guanliyuanjiemian0.shiwuzhaoling.setEditTriggers(QTableWidget.NoEditTriggers)


        self.guanliyuanjiemian0.tuichu.clicked.connect(self.Return_login_ui)
        self.guanliyuanjiemian0.shaixuan.clicked.connect(lambda :self.Information_screening(self.guanliyuanjiemian0))
        self.guanliyuanjiemian0.daochu.clicked.connect(lambda: self.Data_export(self.guanliyuanjiemian0))
        self.guanliyuanjiemian0.shuaxin.clicked.connect(lambda: self.Refresh(self.guanliyuanjiemian0))
        self.guanliyuanjiemian0.xiugai.clicked.connect(lambda: self.Modify_administrators(self.guanliyuanjiemian0))
        self.guanliyuanjiemian0.shanchu.clicked.connect(lambda: self.Delete_administrators(self.guanliyuanjiemian0))

        self.guanliyuanjiemian0.wupingzhuangtai.currentIndexChanged.connect(lambda :self.Information_screening(self.guanliyuanjiemian0))
        self.guanliyuanjiemian0.quyu.currentIndexChanged.connect(lambda :self.Information_screening(self.guanliyuanjiemian0))
        self.guanliyuanjiemian0.wupingzhonglei.currentIndexChanged.connect(lambda :self.Information_screening(self.guanliyuanjiemian0))
        self.guanliyuanjiemian0.anjianxingzhi.currentIndexChanged.connect(lambda :self.Information_screening(self.guanliyuanjiemian0))
        self.guanliyuanjiemian0.gongneng.currentIndexChanged.connect(
            lambda: self.Administrator_ui_jump(self.guanliyuanjiemian0))
        wupingzhuangtai = self.guanliyuanjiemian0.wupingzhuangtai.currentText().strip()
        sql = "select 物品.档案号,案件性质,种类,时间,区域,相关描述,发布人账号,联系方式 from 物品,认领关系,用户 where 物品状态='%s' and 认领关系.物品id=物品.物品id and 发布人账号=账号 " % (wupingzhuangtai)
        self.Sql_serach(sql,self.guanliyuanjiemian0.shiwuzhaoling)
    def Admin_ui1(self):

        self.guanliyuanjiemian1 = QUiLoader('管理员_用户管理.ui')
        self.guanliyuanjiemian1.a.setValidator(QRegExpValidator(QRegExp('[0-9]{10}$')))
        self.guanliyuanjiemian1.shuaxin.clicked.connect(lambda: self.Refresh(self.guanliyuanjiemian1))
        self.guanliyuanjiemian1.daochu.clicked.connect(lambda: self.Data_export(self.guanliyuanjiemian1))
        self.guanliyuanjiemian1.gongneng.currentIndexChanged.connect(
            lambda: self.Administrator_ui_jump(self.guanliyuanjiemian1))
        self.guanliyuanjiemian1.xiugai.clicked.connect(lambda: self.Modify_administrators(self.guanliyuanjiemian1))
        self.guanliyuanjiemian1.shanchu.clicked.connect(lambda: self.Delete_administrators(self.guanliyuanjiemian1))
        self.guanliyuanjiemian1.yonghuxinxi.setEditTriggers(QTableWidget.NoEditTriggers)
        self.guanliyuanjiemian1.xiugai0.clicked.connect(self.Reputation_modify)
        sql=" select 账号,姓名,网络匿称,联系方式,信誉等级,认领成功数 from 用户 "
        self.Sql_serach(sql,self.guanliyuanjiemian1.yonghuxinxi)
    def Admin_ui2(self):
        self.guanliyuanjiemian2 = QUiLoader('管理员_统计信息.ui')
        self.guanliyuanjiemian2.gongneng.currentIndexChanged.connect(
            lambda: self.Administrator_ui_jump(self.guanliyuanjiemian2))
        self.guanliyuanjiemian2.a.clicked.connect(self.Pie_chart)
        self.guanliyuanjiemian2.b.clicked.connect(self.Histogram)
        self.guanliyuanjiemian2.c.clicked.connect(self.Line_chart)
        self.guanliyuanjiemian2.d.clicked.connect(lambda: self.Refresh(self.guanliyuanjiemian2))

    def Administrator_ui_jump(self, x):
        if x.gongneng.currentText().strip() == "用户发布管理":
            self.Refresh(self.guanliyuanjiemian0)
        elif x.gongneng.currentText().strip() == "用户管理":
            self.Refresh(self.guanliyuanjiemian1)
        else:
            self.Refresh(self.guanliyuanjiemian2)


    





      
    def Modify_administrators(self, x):   
        hao = x.a.text()

        if x == self.guanliyuanjiemian0:
            self.cursor.execute(" select 物品id,物品状态 from 物品 where 物品.档案号= '%s' " % (hao))
            result = self.cursor.fetchall()
            if result[0][1] == '已找回':
                QMessageBox.warning(None, '失败', '该物品已物归原主!', QMessageBox.Ok)

            else:
                if result:
                    xiugaihou, ok = QInputDialog.getText(None, '修改', '要修改的物品id为:%s\n请输入修改后的相关信息' % hao)
                    if xiugaihou:
                        yanzhen, ok = QInputDialog.getText(None, '确认修改', '请输入您的密保进行验证')
                        if yanzhen:
                            if yanzhen == self.mibao:
                                self.cursor.execute(
                                    " UPDATE 物品 SET 物品.相关描述 = '%s' WHERE  物品.档案号 = '%s' " % (xiugaihou, hao))
                                QMessageBox.information(None, '成功', '修改成功,为您重新载入', QMessageBox.Ok)
                                self.Refresh(self.guanliyuanjiemian0)
                            else:
                                QMessageBox.warning(None, '失败', '您的密保输入错误!', QMessageBox.Ok)
                else:
                    QMessageBox.warning(None, '失败', '查无此物品!', QMessageBox.Ok)

        else:
            self.cursor.execute("select 账号 from 用户 where 用户.账号= '%s' " % (hao))
            result = self.cursor.fetchone()
            if result:
                xiugaihou, ok = QInputDialog.getText(None, '修改', '要修改的用户账号为:%s\n请输入修改后的密码' % hao)
                if xiugaihou:
                    yanzhen, ok = QInputDialog.getText(None, '确认修改', '请输入您的密保进行验证')
                    if yanzhen == self.mibao:
                        self.cursor.execute(" UPDATE 用户 SET 用户.密码 = '%s' WHERE  用户.账号 = '%s' " % (xiugaihou, hao))
                        QMessageBox.information(None, '成功', '修改成功,为您重新载入', QMessageBox.Ok)
                        self.Refresh(self.guanliyuanjiemian1)
                    else:
                        QMessageBox.warning(None, '失败', '您的密保输入错误!', QMessageBox.Ok)

            else:
                QMessageBox.warning(None, '失败', '查无此用户!', QMessageBox.Ok)
    def Delete_administrators(self, x):   
        hao = x.a.text()

        if x == self.guanliyuanjiemian0:
            self.cursor.execute(" select 物品id,物品状态 from 物品 where 物品.档案号= '%s' " % (hao))
            result = self.cursor.fetchall()

            if result[0][1] =='已找回':
                QMessageBox.warning(None, '失败', '该物品已物归原主!', QMessageBox.Ok)

            else:
                if result:
                    yanzhen, ok = QInputDialog.getText(None, '确认删除', '要删除的物品物品档案号为:%s,请输入您的密保进行验证' % hao)
                    if yanzhen:
                        if yanzhen == self.mibao:

                            self.cursor.execute("delete from 认领关系 where 物品id='%s'" % (result[0][0]))
                            self.cursor.execute("delete from 物品 where 物品id='%s'" % (result[0][0]))
                            QMessageBox.information(None, '成功', '删除成功,为您重新载入', QMessageBox.Ok)
                            self.Refresh(self.guanliyuanjiemian0)
                        else:
                            QMessageBox.warning(None, '失败', '您的密保输入错误!', QMessageBox.Ok)
                else:
                    QMessageBox.warning(None, '失败', '查无此物品!', QMessageBox.Ok)



        else:
            self.cursor.execute("select 账号 from 用户 where 用户.账号= '%s' " % (hao))
            result = self.cursor.fetchone()
            if result:
                yanzhen, ok = QInputDialog.getText(None, '确认删除', '要删除的用户账号为:%s,请输入您的密保进行验证' % hao)
                if yanzhen:
                    if yanzhen == self.mibao:

                        self.cursor.execute("DELETE FROM 用户 WHERE 用户.账号 = '%s' " % (hao))
                        QMessageBox.information(None, '成功', '修改成功,为您重新载入', QMessageBox.Ok)
                        self.Refresh(self.guanliyuanjiemian1)
                    else:
                        QMessageBox.warning(None, '失败', '您的密保输入错误!', QMessageBox.Ok)
            else:
                QMessageBox.warning(None, '失败', '查无此用户!', QMessageBox.Ok)
    def Reputation_modify(self):
        hao=self.guanliyuanjiemian1.a.text()
        self.cursor.execute("select 账号 from 用户 where 用户.账号= '%s' " % (hao))
        result = self.cursor.fetchone()
        if result:
            yanzhen, ok = QInputDialog.getText(None, '确认修改', '请输入您的密保进行验证')
            if yanzhen == self.mibao:
                self.cursor.execute(" UPDATE 用户 SET 用户.信誉等级 = '0' WHERE  用户.账号 = '%s' " % ( hao))
                QMessageBox.information(None, '成功', '修改成功,为您重新载入', QMessageBox.Ok)
                self.Refresh(self.guanliyuanjiemian1)
            else:
                QMessageBox.warning(None, '失败', '您的密保输入错误!', QMessageBox.Ok)
        else:
            QMessageBox.warning(None, '失败', '查无此用户!', QMessageBox.Ok)
    def Data_export(self, x):   
        wb = xlwt.Workbook()   
        ws = wb.add_sheet('数据库导出')   
        if x == self.guanliyuanjiemian0:
            wupingzhuangtai = self.guanliyuanjiemian0.wupingzhuangtai.currentText().strip()
            if wupingzhuangtai == '处理中':
                column = ["档案号", "案件性质", "物品种类", "时间", "区域", "相关描述", "发布人学号", "发布人联系方式"]
            else:
                column = ["档案号", "案件性质", "物品种类", "时间", "区域", "相关描述", "发布人学号", "发布人联系方式", "认领人学号"]
        else:
            column = ["账号", "姓名", "网络匿称", "联系方式", "信誉等级", "认领成功数"]
        for i in range(len(self.result[0])):
            ws.write(0, i, column[i])
        for i in range(len(self.result)):
            for j in range(len(self.result[0])):
                    ws.write(i + 1, j, str(self.result[i][j]))

        wenjianming, ok = QInputDialog.getText(None, '命名', '导出的EXCEL数据表名为:')
        if wenjianming:
            wb.save('./%s.xls' % wenjianming)   
            QMessageBox.information(None, '成功', '导出成功!', QMessageBox.Close)
        else:
            wb.save('./数据.xls')   
            QMessageBox.information(None, '成功', '导出成功!', QMessageBox.Close)




    def Pie_chart(self):   
        self.bing = QMainWindow()
        self.bing.setWindowTitle("各区域总丢失物品饼状图")
        self.bing.setGeometry(100, 100, 1000, 600)
        self.bing.show()
        series = QPieSeries()

        self.cursor.execute(" SELECT COUNT(区域) FROM 物品 where 区域='A区'")
        A = self.cursor.fetchone()
        self.cursor.execute("SELECT COUNT(区域) FROM 物品 where 区域='B区'")
        B = self.cursor.fetchone()
        self.cursor.execute(" SELECT COUNT(区域) FROM 物品 where 区域='C区'")
        C = self.cursor.fetchone()
        self.cursor.execute(" SELECT COUNT(区域) FROM 物品 where 区域='D区'")
        D = self.cursor.fetchone()
        self.cursor.execute("SELECT COUNT(区域) FROM 物品 where 区域='其他'")
        E = self.cursor.fetchone()
        series.append("A区", A[0])
        series.append("B区", B[0])
        series.append("C区", C[0])
        series.append("D区", D[0])
        series.append("其他区域", E[0])

        slice0 = QPieSlice()
        slice0 = series.slices()[0]
        slice0.setLabelVisible(True)
        slice0.setBrush(Qt.green)

        slice1 = QPieSlice()
        slice1 = series.slices()[1]
        slice1.setLabelVisible(True)
        slice0.setBrush(Qt.red)

        slice2 = QPieSlice()
        slice2 = series.slices()[2]
        slice2.setLabelVisible(True)

        slice3 = QPieSlice()
        slice3 = series.slices()[3]
        slice3.setLabelVisible(True)

        slice4 = QPieSlice()
        slice4 = series.slices()[4]
        slice4.setLabelVisible(True)

        chart = QChart()
        chart.legend().hide()
        chart.addSeries(series)
        chart.createDefaultAxes()
        chart.setAnimationOptions(QChart.SeriesAnimations)
        chart.setTitle("各区域丢失物品占比")

        chart.legend().setVisible(True)
        chart.legend().setAlignment(Qt.AlignBottom)

        chartview = QChartView(chart)
        chartview.setRenderHint(QPainter.Antialiasing)
        self.bing.setCentralWidget(chartview)

    def Histogram(self):   
        self.z = QWidget()
        self.z.setGeometry(200, 200, 1000, 400)
        self.z.setWindowTitle("本年度种类统计")
        self.z.setWindowIcon(QIcon("python.png"))

         
        self.z.setStyleSheet('background-color:white')

         
        set0 = QBarSet("书籍/文具")
        set1 = QBarSet("证件")
        set2 = QBarSet("雨伞/钥匙")
        set3 = QBarSet("电子产品")
        set4 = QBarSet("其他")
        a = []
        zhonglei = ['书籍/文具', '证件', '雨伞/钥匙', '电子产品', '其他']
        for i in range(5):
            a.append([])
            self.cursor.execute(
                " SELECT COUNT(种类) FROM 物品 WHERE  种类='%s' AND 时间 IN(SELECT 时间 FROM 物品 WHERE 时间<'2021-02-01 ' and 时间 >'2020-12-31 ')" % (
                zhonglei[i]))
            result = self.cursor.fetchone()
            a[i].append(result[0])
            self.cursor.execute(
                " SELECT COUNT(种类) FROM 物品 WHERE  种类='%s' AND 时间 IN(SELECT 时间 FROM 物品 WHERE 时间<'2021-03-01 ' and 时间 >'2021-01-31 ')" % (
                zhonglei[i]))
            result = self.cursor.fetchone()
            a[i].append(result[0])
            self.cursor.execute(
                " SELECT COUNT(种类) FROM 物品 WHERE  种类='%s' AND 时间 IN(SELECT 时间 FROM 物品 WHERE 时间<'2021-04-01 ' and 时间 >'2021-02-28 ')" % (
                zhonglei[i]))
            result = self.cursor.fetchone()
            a[i].append(result[0])
            self.cursor.execute(
                "SELECT COUNT(种类) FROM 物品 WHERE  种类='%s' AND 时间 IN(SELECT 时间 FROM 物品 WHERE 时间<'2021-05-01 ' and 时间 >'2021-03-31 ')" % (
                zhonglei[i]))
            result = self.cursor.fetchone()
            a[i].append(result[0])
            self.cursor.execute(
                "SELECT COUNT(种类) FROM 物品 WHERE  种类='%s' AND 时间 IN(SELECT 时间 FROM 物品 WHERE 时间<'2021-06-01 ' and 时间 >'2021-04-30 ')" % (
                zhonglei[i]))
            result = self.cursor.fetchone()
            a[i].append(result[0])
            self.cursor.execute(
                " SELECT COUNT(种类) FROM 物品 WHERE  种类='%s' AND 时间 IN(SELECT 时间 FROM 物品 WHERE 时间<'2021-07-01 ' and 时间 >'2021-05-31 ')" % (
                zhonglei[i]))
            result = self.cursor.fetchone()
            a[i].append(result[0])
            self.cursor.execute(
                " SELECT COUNT(种类) FROM 物品 WHERE  种类='%s' AND 时间 IN(SELECT 时间 FROM 物品 WHERE 时间<'2021-08-01 ' and 时间 >'2021-06-30 ')" % (
                    zhonglei[i]))
            result = self.cursor.fetchone()
            a[i].append(result[0])
            self.cursor.execute(
                " SELECT COUNT(种类) FROM 物品 WHERE  种类='%s' AND 时间 IN(SELECT 时间 FROM 物品 WHERE 时间<'2021-09-01 ' and 时间 >'2021-07-31 ')" % (
                    zhonglei[i]))
            result = self.cursor.fetchone()
            a[i].append(result[0])
            self.cursor.execute(
                " SELECT COUNT(种类) FROM 物品 WHERE  种类='%s' AND 时间 IN(SELECT 时间 FROM 物品 WHERE 时间<'2021-10-01 ' and 时间 >'2021-08-31 ')" % (
                    zhonglei[i]))
            result = self.cursor.fetchone()
            a[i].append(result[0])
            self.cursor.execute(
                " SELECT COUNT(种类) FROM 物品 WHERE  种类='%s' AND 时间 IN(SELECT 时间 FROM 物品 WHERE 时间<'2021-11-01 ' and 时间 >'2021-09-30 ')" % (
                    zhonglei[i]))
            result = self.cursor.fetchone()
            a[i].append(result[0])
            self.cursor.execute(
                " SELECT COUNT(种类) FROM 物品 WHERE  种类='%s' AND 时间 IN(SELECT 时间 FROM 物品 WHERE 时间<'2021-12-01 ' and 时间 >'2021-10-31 ')" % (
                    zhonglei[i]))
            result = self.cursor.fetchone()
            a[i].append(result[0])
            self.cursor.execute(
                "SELECT COUNT(种类) FROM 物品 WHERE  种类='%s' AND 时间 IN(SELECT 时间 FROM 物品 WHERE 时间<'2022-01-01 ' and 时间 >'2021-11-30 ')" % (
                    zhonglei[i]))
            result = self.cursor.fetchone()
            a[i].append(result[0])
        set0 << a[0][0] << a[0][1] << a[0][2] << a[0][3] << a[0][4] << a[0][5] << a[0][6] << a[0][7] << a[0][8] << a[0][
            9] << a[0][10] << a[0][11]
        set1 << a[1][0] << a[1][1] << a[1][2] << a[1][3] << a[1][4] << a[1][5] << a[1][6] << a[1][7] << a[1][8] << a[1][
            9] << a[1][10] << a[1][11]
        set2 << a[2][0] << a[2][1] << a[2][2] << a[2][3] << a[2][4] << a[2][5] << a[2][6] << a[2][7] << a[2][8] << a[2][
            9] << a[2][10] << a[2][11]
        set3 << a[3][0] << a[3][1] << a[3][2] << a[3][3] << a[3][4] << a[3][5] << a[3][6] << a[3][7] << a[3][8] << a[3][
            9] << a[3][10] << a[3][11]
        set4 << a[4][0] << a[4][1] << a[4][2] << a[4][3] << a[4][4] << a[4][5] << a[4][6] << a[4][7] << a[4][8] << a[4][
            9] << a[4][10] << a[4][11]

         
        series = QPercentBarSeries()
        series.append(set0)
        series.append(set1)
        series.append(set2)
        series.append(set3)
        series.append(set4)
         
        chart = QChart()
        chart.addSeries(series)
        chart.setTitle("上半年各月份各种类物品统计图")
        chart.setAnimationOptions(QChart.SeriesAnimations)
        chart.setTheme(QChart.ChartThemeDark)
         
        categories = ["一月", "二月", "三月", "四月", "五月", "六月", "七月", "八月", "九月", "十月", "十一月", "十二月"]
        axis = QBarCategoryAxis()
        axis.append(categories)
        chart.createDefaultAxes()
        chart.setAxisX(axis, series)
         
        chartview = QChartView(chart)
        vbox = QVBoxLayout()
        vbox.addWidget(chartview)
        self.z.setLayout(vbox)
        self.z.show()

    def Line_chart(self):   
        self.l = QWidget()
        self.l.setGeometry(200, 200, 1000, 400)
        self.l.setWindowTitle("本年度种类统计")

        self.l.pw = pyqtgraph.PlotWidget()
        self.l.pw.setTitle("物品丢失趋势", color='008080', size='12pt')
        self.l.pw.setLabel("left", "丢失物品件数")
        self.l.pw.setLabel("bottom", "季度")
        self.l.pw.setBackground('w')

        month = [1, 2, 3, 4]
        num = []

        self.cursor.execute(
            "SELECT COUNT(*) FROM 物品 WHERE 时间<'2021-04-01 00:00:00' and 时间 >'2021-01-01 00:00:00'")
        result = self.cursor.fetchone()
        num.append(result[0])
        self.cursor.execute(
            " SELECT COUNT(*) FROM 物品 WHERE 时间<'2021-07-01 00:00:00' and 时间 >'2021-04-01 00:00:00'")
        result = self.cursor.fetchone()
        num.append(result[0])
        self.cursor.execute(
            " SELECT COUNT(*) FROM 物品 WHERE 时间<'2021-10-01 00:00:00' and 时间 >'2021-07-01 00:00:00'")
        result = self.cursor.fetchone()
        num.append(result[0])
        self.cursor.execute(
            " SELECT COUNT(*) FROM 物品 WHERE 时间<'2022-01-01 00:00:00' and 时间 >'2021-10-01 00:00:00'")
        result = self.cursor.fetchone()
        num.append(result[0])

        self.l.pw.plot(month, num, pen=pyqtgraph.mkPen('r'))
        self.l.pw.show()

    def Refresh(self, x):


        if x == self.yonghujiemian:
                self.User_ui()
                self.yonghujiemian.show()
        else:
            if x == self.guanliyuanjiemian0:
                self.guanliyuanjiemian1.close()
                self.guanliyuanjiemian2.close()
                self.Admin_ui0()
                self.guanliyuanjiemian0.show()
            elif x == self.guanliyuanjiemian1:
                self.guanliyuanjiemian0.close()
                self.guanliyuanjiemian2.close()
                self.Admin_ui1()
                self.guanliyuanjiemian1.show()
            else:
                self.guanliyuanjiemian0.close()
                self.guanliyuanjiemian1.close()
                self.Admin_ui2()
                self.guanliyuanjiemian2.show()


    def Sql_serach(self, sql, insert):
        insert.clearContents()
        insert.setRowCount(0)
        self.cursor.execute(sql)
        self.result = self.cursor.fetchall()
        for i in range(len(self.result)):
            insert.insertRow(i)
            for j in range(len(self.result[0])):
                insert.setItem(i, j, QTableWidgetItem(str(self.result[i][j])))
    def Information_screening(self, x):
        anjianxingzhi = x.anjianxingzhi.currentText().strip()
        wupingzhonglei = x.wupingzhonglei.currentText().strip()
        shijian = x.shijian.date().toString("yyyy-MM-dd")
        quyu = x.quyu.currentText().strip()
        miaoshu0 = x.miaoshu.text() if x.miaoshu.text() else " "
        miaoshu = "%" + miaoshu0 + "%"

        if ('self.guanliyuanjiemian0' in dir()):
            if x == self.guanliyuanjiemian0:
                wupingzhuangtai = x.wupingzhuangtai.currentText().strip()
                if wupingzhuangtai == "无人认领":
                    sql = "select 物品.档案号,案件性质,种类,时间,区域,相关描述,发布人账号,联系方式 from 物品,认领关系,用户 where 物品状态='%s' and ((案件性质='%s' and 种类='%s' and 区域= '%s') or (时间='%s') or (相关描述 like'%s')) and (用户.账号 = 认领关系.发布人账号 and 认领关系.物品id=物品.物品id)" % (
                        wupingzhuangtai, anjianxingzhi, wupingzhonglei, quyu, shijian, miaoshu)

                else:
                    sql = "select 物品.档案号,案件性质,种类,时间,区域,相关描述,发布人账号,联系方式,认领人账号 from 物品,认领关系,用户 where 物品状态='%s' and ((案件性质='%s' and 种类='%s' and 区域= '%s') or (时间='%s') or (相关描述 like'%s')) and (用户.账号 = 认领关系.发布人账号 and 认领关系.物品id=物品.物品id)" % (
                        wupingzhuangtai, anjianxingzhi, wupingzhonglei, quyu, shijian, miaoshu)

        else:
            sql = "select 档案号,案件性质,种类,时间,区域,相关描述 from 物品 where 物品状态= '无人认领' and ( (案件性质='%s' and 种类='%s' and 区域= '%s') or (时间='%s') or (相关描述 like'%s')) " % (
                anjianxingzhi, wupingzhonglei, quyu, shijian, miaoshu)

        self.Sql_serach(sql, x.shiwuzhaoling)


app = QApplication([])
stats = MainUi()
stats.denglujiemian.show()
app.exec_()




这篇关于数据库结课作业——校园失物招领系统的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!