公交安全管理系统

/*1.创建基本表*/
/*1.2创建线路_站点表*/
DROP TABLE 线路_站点 CASCADE;

CREATE TABLE 线路_站点 (
       zhandianming         VARCHAR(10) NOT NULL,
       zhandianbianhao      SMALLINT NULL,
       xianlubianhao        VARCHAR(5) NOT NULL
);


ALTER TABLE 线路_站点
       ADD  PRIMARY KEY (zhandianming, xianlubianhao) ;

/*1.3创建车队成员_违章表*/
DROP TABLE 车队成员_违章 CASCADE;

CREATE TABLE 车队成员_违章 (
       weizhangshijian      DATE NOT NULL,
       weizhangsijigonghao  CHAR(11) NOT NULL,
       chepaihao            VARCHAR(20) NULL,
       xianlubianhao        VARCHAR(5) NULL,
       zhandianming         VARCHAR(10) NULL,
       weizhangbianhao      SMALLINT NULL,
       jilurengonghao       CHAR(11) NULL
);

ALTER TABLE 车队成员_违章
       ADD  PRIMARY KEY (weizhangshijian, weizhangsijigonghao)  ;

/*1.4创建车队成员表*/
DROP TABLE 车队成员 CASCADE;

CREATE TABLE 车队成员 (
       gonghao              CHAR(11) NOT NULL,
       shenfenzhenghao      VARCHAR(20) NOT NULL UNIQUE,
       xingming             VARCHAR(10) NULL,
       xingbie              CHAR(2) NULL,
       chushengnianfen      SMALLINT NULL,
       zhicheng             VARCHAR(10) NULL,
       lianxifangshi        VARCHAR(20) NULL,
       jiatinzhuzhi         VARCHAR(30) NULL,
       ruduishijian         DATE NULL,
       cheling              SMALLINT NULL,
       xianlubianhao        VARCHAR(5) NULL
       cheduibianhao      SMALLINT NULL
);

ALTER TABLE 车队成员
       ADD   PRIMARY KEY (gonghao)  ;

/*1.5创建汽车表*/
DROP TABLE 汽车 CASCADE;

CREATE TABLE 汽车 (
       chepaihao            VARCHAR(20) NOT NULL,
       chepinpai            VARCHAR(10) NOT NULL,
       zuoshu               SMALLINT NULL,
       cheling              SMALLINT NULL,
       gongsimingcheng      VARCHAR(20) NULL,
       xianlubianhao        VARCHAR(5) NULL
);


ALTER TABLE 汽车
       ADD  PRIMARY KEY (chepaihao)  ;

/*1.6创建线路表*/
DROP TABLE 线路 CASCADE;

CREATE TABLE 线路 (
       xianlubianhao        VARCHAR(5) NOT NULL,
       xianluchuangjianshijian DATE NULL,
       cheduibianhao        SMALLINT NULL
);


ALTER TABLE 线路
       ADD   PRIMARY KEY (xianlubianhao)  ;

/*1.7创建车队表*/
DROP TABLE 车队 CASCADE;

CREATE TABLE 车队 (
       cheduibianhao        SMALLINT NOT NULL,
       cheduichuangjianshijian DATE NOT NULL,
       gongsimingcheng      VARCHAR(20) NULL
);


ALTER TABLE 车队
       ADD   PRIMARY KEY (cheduibianhao)  ;

/*1.8创建公交公司表*/
DROP TABLE 公交公司 CASCADE;

CREATE TABLE 公交公司 (
       gongsimingcheng      VARCHAR(20) NOT NULL,
       gongsichuangjianshijian DATE NULL,
       bangongdidian        VARCHAR(20) NULL
);


ALTER TABLE 公交公司
       ADD   PRIMARY KEY (gongsimingcheng)  ;

/*1.9创建站点表*/
DROP TABLE 站点 CASCADE;

CREATE TABLE 站点 (
       zhandianming         VARCHAR(10) NOT NULL,
       zhandianweizhi       VARCHAR(20) NULL
);


ALTER TABLE 站点
       ADD   PRIMARY KEY (zhandianming)  ;

/*1.10创建违章表*/
DROP TABLE 违章 CASCADE;

CREATE TABLE 违章 (
       weizhangbianhao      SMALLINT NOT NULL,
       weizhangleixing      VARCHAR(20) NULL,
       chufafangshi         VARCHAR(20) NOT NULL
);


ALTER TABLE 违章
       ADD   PRIMARY KEY (weizhangbianhao)  ;

/*2创建视图部分*/

/*2.1创建路队长视图*/

CREATE OR REPLACE VIEW 路队长 (工号, 负责路线)  AS
       SELECT 车队成员.gonghao, 车队成员.xianlubianhao
       FROM 车队成员
       WHERE 车队成员.zhicheng='路队长';


/*2.2创建车队队长视图*/
CREATE OR REPLACE VIEW 车队队长 (工号, 负责车队编号)  AS
       SELECT 车队成员.gonghao, 车队成员.cheduibianhao
       FROM 车队成员, 车队成员
       WHERE 车队成员.zhicheng='车队长' AND 车队成员.gonghao=车队成员.gonghao;

/*2.3创建车队_车队成员视图*/
CREATE OR REPLACE VIEW 车队_车队成员 (工号, 车队编号)  AS
select `cheduichengyuan`.`gonghao` AS `gonghao`,`cheduichengyuan`.`xianlubianhao` AS `xianlubianhao` from `cheduichengyuan`

/*2.4创建司机违章信息视图*/
CREATE OR REPLACE VIEW 司机违章信息 (违章司机工号, 违章司机车队, 车牌号, 线路编号, 违章站点, 违章时间, 违章类型说明, 记录人工号)  AS
       SELECT 车队成员_违章.weizhangsijigonghao, 车队成员.cheduibianhao, 车队成员_违章.chepaihao, 车队成员_违章.xianlubianhao, 车队成员_违章.zhandianming, 车队成员_违章.weizhangshijian, 违章.weizhangleixing, 车队成员_违章.jilurengonghao
       FROM 车队成员_违章, 车队成员, 违章
       WHERE 车队成员.gonghao=车队成员_违章.weizhangsijigonghao
AND 车队成员_违章.weizhangbianhao=违章.weizhangbianhao;

/*2.5创建车队违章信息视图*/
CREATE OR REPLACE VIEW 车队违章信息 (车队编号, 违章时间, 违章类型说明)  AS
       SELECT 车队成员.cheduibianhao, 车队成员_违章.weizhangshijian, 违章.weizhangleixing
       FROM 车队成员, 车队成员_违章, 违章
       WHERE 车队成员.gonghao=车队成员_违章.weizhangsijigonghao
AND 违章.weizhangbianhao=车队成员_违章.weizhangbianhao;

/*3设置外码*/

ALTER TABLE 线路_站点
       ADD   FOREIGN KEY (xianlubianhao)
                             REFERENCES 线路(xianlubianhao);


ALTER TABLE 线路_站点
       ADD  FOREIGN KEY (zhandianming)
                             REFERENCES 站点(zhandianming);


ALTER TABLE 车队成员_违章
       ADD  FOREIGN KEY (weizhangbianhao)
                             REFERENCES 违章(weizhangbianhao)
                             ON DELETE SET NULL  ;


ALTER TABLE 车队成员_违章
       ADD   FOREIGN KEY (zhandianming)
                             REFERENCES 站点(zhandianming)
                             ON DELETE SET NULL  ;


ALTER TABLE 车队成员_违章
       ADD   FOREIGN KEY (xianlubianhao)
                             REFERENCES 线路(xianlubianhao)
                             ON DELETE SET NULL  ;


ALTER TABLE 车队成员_违章
       ADD   FOREIGN KEY (chepaihao)
                             REFERENCES 汽车(chepaihao)
                             ON DELETE SET NULL  ;


ALTER TABLE 车队成员_违章
       ADD   FOREIGN KEY (weizhangsijigonghao)
                             REFERENCES 车队成员(gonghao)  ;


ALTER TABLE 车队成员
       ADD   FOREIGN KEY (xianlubianhao)
                             REFERENCES 线路(xianlubianhao)
                             ON DELETE SET NULL  ;


ALTER TABLE 汽车
       ADD   FOREIGN KEY (xianlubianhao)
                             REFERENCES 线路(xianlubianhao)
                             ON DELETE SET NULL ;


ALTER TABLE 汽车
       ADD   FOREIGN KEY (gongsimingcheng)
                             REFERENCES 公交公司(gongsimingcheng)
                             ON DELETE SET NULL  ;


ALTER TABLE 线路
       ADD   FOREIGN KEY (cheduibianhao)
                             REFERENCES 车队(cheduibianhao)
                             ON DELETE SET NULL  ;


ALTER TABLE 车队
       ADD   FOREIGN KEY (gongsimingcheng)
                             REFERENCES 公交公司(gongsimingcheng)
                             ON DELETE SET NULL  ;

INSERT INTO 公交公司 VALUE ('zhou',null,'online')
SELECT * FROM 公交公司

# 在这里面的函数与DBase里面的url对应
import sys

from django.shortcuts import render,HttpResponse,redirect
from app01 import models
import time

# Create your views here.
# 函数默认需要有一个参数

# 录入司机基本信息，如工号、姓名、性别等
def siji(request):
    if request.method == 'GET':
        return render(request,'lurusiji.html')
    print(request.POST)
    try:
    # 从前端获取数据
        gonghao = request.POST.get("gonghao")
        shenfenzhenghao = request.POST.get("shenfenzhenghao")
        print(len(shenfenzhenghao))
        if len(shenfenzhenghao)!=18 and len(shenfenzhenghao)!=0:
            return render(request, 'lurusiji.html',{"errsfzh":"身份证号输入错误"})
        xingming = request.POST.get("xingming")
        xingbie = request.POST.get("xingbie")
        chushengnianfen = request.POST.get("chushengnianfen")
        zhicheng = request.POST.get("zhicheng")
        lianxifangshi = request.POST.get("lianxifangshi")
        if len(lianxifangshi) != 11 and len(lianxifangshi) != 0:
            return render(request, 'lurusiji.html', {"errlxfs": "联系方式填写错误"})
        jiatinzhuzhi = request.POST.get("jiatinzhuzhi")
        ruduishijian = request.POST.get("ruduishijian")
        cheling = request.POST.get("cheling")
        xianlubianhao = request.POST.get("xianlubianhao")
        cheduibianhao = request.POST.get("cheduibianhao")
        print(xingbie,zhicheng)
        # 特殊处理外码
        xianlubianhaof=models.Xianlu.objects.get(xianlubianhao=xianlubianhao)

        # 将信息放入数据库
        models.Cheduichengyuan.objects.create(gonghao=gonghao,shenfenzhenghao=shenfenzhenghao,xingming=xingming,xingbie=xingbie,chushengnianfen=chushengnianfen,zhicheng= zhicheng,lianxifangshi=lianxifangshi,jiatinzhuzhi=jiatinzhuzhi,ruduishijian=ruduishijian,cheling=cheling,xianlubianhao=xianlubianhaof,cheduibianhao=cheduibianhao)
    except Exception as e:
        print(e)
        print("error")
        return render(request,'lurusiji.html',{"errormsg":"录入失败！请检查：工号和身份证号是否录入，以及是否唯一，车队编号是否存在，若以上均正确仍无法录入请联系管理员",})
    else:
       return render(request, 'lurusiji.html')

# 录入汽车基本信息，如车牌号、座数等
def qiche(request):
    if request.method == 'GET':
        return render(request,'luruqiche.html')
    print(request.POST)
    try:
        # 从前端获取数据
        chepaihao = request.POST.get("chepaihao")
        chepinpai = request.POST.get("chepinpai")
        zuoshu = request.POST.get("zuoshu")
        cheling = request.POST.get("cheling")
        gongsimingcheng = request.POST.get("gongsimingcheng")
        xianlubianhao = request.POST.get("xianlubianhao")

        # 外码
        gongsimingchengf = models.Gongjiaogongsi.objects.get(gongsimingcheng=gongsimingcheng)
        xianlubianhaof=models.Xianlu.objects.get(xianlubianhao=xianlubianhao)

        # 加入到数据库中
        models.Qiche.objects.create(chepaihao=chepaihao,chepinpai=chepinpai,zuoshu=zuoshu,cheling=cheling,gongsimingcheng=gongsimingchengf,xianlubianhao=xianlubianhaof)
    except Exception as e:
        print(e)
        return render(request, 'luruqiche.html',{"err","录入错误！请检查检查公司和线路是否存在以及填写是否正确"})
    else:
        return render(request, 'luruqiche.html')
    return HttpResponse('录入汽车基本信息，如车牌号、座数等')

# 录入司机的违章信息；
def lsijiweizhang(request):
    if request.method == 'GET':
        return render(request,'luruweizhang.html')
    print(request.POST)

    try:
        # 从前端获取数据
        weizhangshijian = request.POST.get("weizhangshijian")
        weizhangsijigonghao = request.POST.get("weizhangsijigonghao")
        chepaihao = request.POST.get("chepaihao")
        zhandianming = request.POST.get("zhandianming")
        weizhangbianhao = request.POST.get("weizhangbianhao")
        jilurengonghao = request.POST.get("jilurengonghao")

        # 外码
        weizhangsijigonghaof=models.Cheduichengyuan.objects.get(gonghao=weizhangsijigonghao)
        chepaihaof = models.Qiche.objects.get(chepaihao=chepaihao)
        zhandianmingf = models.Zhandian.objects.get(zhandianming=zhandianming)
        weizhangbianhaof = models.Weizhang.objects.get(weizhangbianhao=weizhangbianhao)

        # 将信息放到数据库中
        models.CheduichengyuanWeizhang.objects.create(weizhangshijian=weizhangshijian,weizhangsijigonghao=weizhangsijigonghaof,chepaihao=chepaihaof,zhandianming=zhandianmingf,weizhangbianhao=weizhangbianhaof,jilurengonghao= jilurengonghao)
    except Exception as e:
        print(e)
        return  render(request, 'luruweizhang.html',{"err":"请检查信息填写是否正确"})
    else:
        return render(request, 'luruweizhang.html')
    return HttpResponse('录入司机的违章信息')

# 查询某个车队下的司机基本信息；
def chedui(request):
    if request.method == 'GET':
        return render(request,'chaxunchedui.html')
    print(request.POST)
    try:
        # 从前端获取数据并筛选数据库中的数据
        cheduibianhao = request.POST.get("cheduibianhao")
        sijiset=models.Cheduichengyuan.objects.filter(cheduibianhao=cheduibianhao)

        # 将出生年份转换成年龄
        for siji in sijiset:
            if siji.chushengnianfen != None:
                siji.chushengnianfen=time.localtime(time.time())[0]-siji.chushengnianfen
    except Exception as e:
        print(e)
        return render(request,'chaxunchedui.html',{"err":"请检查信息填写是否正确"})
    else:
        return render(request,'chaxunchedui.html',{"sijisets":sijiset})

    return HttpResponse('查询某个车队下的司机基本信息')

# 查询某名司机在某个时间段的违章详细信息；
def ssijiweizhang(request):
    if request.method == 'GET':
        return render(request,'chaxunsijiweizhang.html')
    print(request.POST)
    try:
        # 获取前端输入数据并将获得的string转换成date
        gonghao = request.POST.get("gonghao")
        kaishishijian = request.POST.get("kaishishijian")
        jieshushijian = request.POST.get("jieshushijian")

        import datetime
        kaishishijian=(datetime.date(*map(int, kaishishijian.split('-'))))
        jieshushijian=(datetime.date(*map(int, jieshushijian.split('-'))))

        # 从数据库中筛选数据
        weizhangsets = models.Sijiweizhangxinxi.objects.filter(weizhangshijian__range=[kaishishijian, jieshushijian],weizhangsijigonghao=gonghao)
    except Exception as e:
        print(e)
        return render(request,'chaxunsijiweizhang.html',{"err":"请检查输入是否正确"})
    else:
        return render(request,'chaxunsijiweizhang.html',{"weizhangsets":weizhangsets})
    return HttpResponse('查询某名司机在某个时间段的违章详细信息')

# 查询某个车队在某个时间段的违章统计信息，
# 如：2次闯红灯、4次未礼让斑马线等。(次数使用count得出)
def cheduiweizhang(request):
    if request.method == 'GET':
        return render(request,'chaxunweizhang.html')
    print(request.POST)
    try:
        # 从前端获取数据,并将string转换成date
        chedui = request.POST.get("chedui")
        kaishishijian = request.POST.get("kaishishijian")
        jieshushijian = request.POST.get("jieshushijian")

        import datetime
        kaishishijian=(datetime.date(*map(int, kaishishijian.split('-'))))
        jieshushijian=(datetime.date(*map(int, jieshushijian.split('-'))))

        # 从数据库中筛选数据,并将数据转换成dic(统计数据),key为违章类型,value为对应违章类型的次数
        weizhangsets = models.Cheduiweizhangxinxi.objects.filter(cheduibianhao=chedui,weizhangshijian__range=[kaishishijian, jieshushijian])
        weizhangdic={}
        for weizhang in weizhangsets:
            if weizhang.weizhangleixing in weizhangdic:
                weizhangdic[weizhang.weizhangleixing]=weizhangdic[weizhang.weizhangleixing]+1
            else:
                weizhangdic[weizhang.weizhangleixing] =1
    except Exception as e:
        print(e)
        return render(request, 'chaxunweizhang.html',{"err":"请检查输入是否正确"})
    else:
        return render(request,'chaxunweizhang.html',{"chedui": chedui,"kaishishijian":kaishishijian,"jieshushijian":jieshushijian,"weizhangdic":weizhangdic})
    return HttpResponse('查询某个车队在某个时间段的违章统计信息')

username='username'
# 登录页面
def login(request):
    if request.method == 'GET':
        return render(request,'login.html')
        # 如果是post请求则获取用户提交的数据
    print(request.POST)

    # 获取前端数据
    username=request.POST.get("user")
    password=request.POST.get("pwd")

    # 验证是不是车队长或者路队长   cheduizhangset=models.Cheduiduizhang.objects.filter(gonghao=username)
    luduizhangset=models.Luduizhang.objects.filter(gonghao=username)
    if (len(cheduizhangset)!=0 or len(luduizhangset)!=0) and password == '1234':
        return render(request,'duizhang.html')

    # 验证是不是车队成员,并将出生年份转换成年龄    chengyuanset=models.Cheduichengyuan.objects.filter(gonghao=username)
    if len(chengyuanset)!=0:
        chengyuan={}
        for item in chengyuanset:
            chengyuan=item
            if chengyuan.chushengnianfen != None:
                chengyuan.chushengnianfen=time.localtime(time.time())[0]-chengyuan.chushengnianfen
            print(chengyuan.xianlubianhao)
        weizhang=models.Sijiweizhangxinxi.objects.filter(weizhangsijigonghao=username)
        weizhanglen=len(weizhang)
        return render(request,'chengyuan.html',{"chengyuan":chengyuan,"weizhangsets":weizhang,"weizhanglen":weizhanglen})
    return render(request,'login.html',{"error_msg":"工号输入错误"})