智能通道系统之(4) 创建数据库[表、视图、存储过程]

本文链接：https://blog.youkuaiyun.com/zhengzizhi/article/details/2184782

本文介绍了一个基于Socket与多线程技术的智能通道系统设计方案，详细展示了如何创建数据库、表、视图及存储过程来实现员工就餐信息的验证等功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

让知识更加联贯让技术走进生活

我的博客我的程序我的网络

------郑紫至

E-mail:zhengzizhi@yahoo.com.cn

智能通道系统

本系统全面地展示了Socket与多线程，数据库，工业上采用的CRC查表校验信息码

等综合技术的完整代码，从代码的角度展示了工控通讯的完整过程，本系统可以改装成

地铁、火车站、海关、商场、旅游景点，智能小区、大型集团公司，大型停车场，

等等需要刷卡通行智能验证平台，本系统使用了软终端从理论的角度模拟出单片机终

端设备的通讯过程。

开发工具:Visual Studio 2008.NET(C#) + Microsoft SQL Server 2005

智能通道系统之(4) 创建数据库[表、视图、存储过程]

1．首先创建一个名称为IRS数据库。

2．创建表

CREATE TABLE [dbo].[AbnormityCard](

[IndexNo] [int] IDENTITY(1,1) PRIMARY KEY,

[CardNo] [varchar](10) NULL,

[EmpNo] [varchar](8) NULL,

[ReadCardTime] [datetime] NULL,

[AbnormityNo] [int] NULL,

[AbnormityDescription] [nvarchar](100) NULL,

[MachineNo] [varchar](5) NULL,

[ReadHeadNo] [int] NULL,

[CreateTime] [datetime] NULL DEFAULT (getdate()))

CREATE TABLE [dbo].[Department](

[DeptNo] [varchar](2) PRIMARY KEY,

[DeptName] [nvarchar](16) NULL )

CREATE TABLE [dbo].[DiningClass](

[EmpNo] [nchar](10) PRIMARY KEY,

[DiningDate] [datetime] PRIMARY KEY,

[DiningClassA] [varchar](10) NULL,

[DiningClassB] [varchar](10) NULL,

[DiningClassC] [varchar](10) NULL,

[DiningClassD] [varchar](10) NULL,

[DiningApplicationNo] [nvarchar](10) NULL,

[IndexNo] [int] NULL )

CREATE TABLE [dbo].[DiningClassTime](

[ClassNo] [varchar](50) PRIMARY KEY,

[ClassName] [nvarchar](20) NULL,

[Time_B] [varchar](5) NULL,

[IsNight_B] [bit] NULL,

[Time_E] [varchar](5) NULL,

[IsNight_E] [bit] NULL,

[Price] [numeric](18, 2) NULL )

CREATE TABLE [dbo].[Employee](

[EmpNo] [varchar](8) PRIMARY KEY,

[EmpName] [nvarchar](50) NULL,

[DeptNo] [varchar](2) NULL,

[CardNo] [varchar](10) NULL,

[DimissionNo] [nvarchar](50) NULL,

[DimissionDate] [datetime] NULL )

CREATE TABLE [dbo].[ReadCard](

[EmpNo] [varchar](8) PRIMARY KEY,

[ReadCardTime] [datetime] PRIMARY KEY,

[CardNo] [varchar](10) NULL,

[MachineNo] [varchar](5) NULL,

[ReadHeadNo] [int] NULL )

3．创建视图

CREATE VIEW [dbo].[V_DiningClass]

SELECT A.EmpNo, A.DiningDate,

A.DiningClassA, B.ClassName AS ClassName1 ,

B.Time_B AS Time_B1, B.Time_E AS Time_E1,

B.IsNight_B AS IsNight_B1, B.IsNight_E AS IsNight_E1,

A.DiningClassB, C.ClassName AS ClassName2 ,

C.Time_B AS Time_B2 ,C.Time_E AS Time_E2 ,

C.IsNight_B AS IsNight_B2 , C.IsNight_E AS IsNight_E2,

A.DiningClassC, D.ClassName AS ClassName3,

D.Time_B AS Time_B3 , D.Time_E AS Time_E3,

D.IsNight_B AS IsNight_B3 , D.IsNight_E AS IsNight_E3 ,

A.DiningClassD,E.ClassName AS ClassName4 ,

E.Time_B AS Time_B4 , E.Time_E AS Time_E4,

E.IsNight_B AS IsNight_B4 ,E.IsNight_E AS IsNight_E4,

F.EmpName, F.DimissionNo,F.CardNo, F.DeptNo,

CASE WHEN B.IsNight_B = 1 OR

C.IsNight_B = 1 OR

D.IsNight_B = 1 OR

E.IsNight_B = 1 THEN 1 ELSE 0 END AS IsAddDay_B,

CASE WHEN B.IsNight_E = 1 OR

C.IsNight_E = 1 OR

D.IsNight_E = 1 OR

E.IsNight_E = 1 THEN 1 ELSE 0 END AS IsAddDay_E,

ISNULL(B.Price, 0) AS Price_A, ISNULL(C.Price, 0) AS Price_B,

ISNULL(D.Price, 0) AS Price_C, ISNULL(E.Price, 0) AS Price_D, G.DeptName

FROM dbo.DiningClass AS A LEFT OUTER JOIN

dbo.DiningClassTime AS B ON A.DiningClassA = B.ClassNo LEFT OUTER JOIN

dbo.DiningClassTime AS C ON A.DiningClassB = C.ClassNo LEFT OUTER JOIN

dbo.DiningClassTime AS D ON A.DiningClassC = D.ClassNo LEFT OUTER JOIN

dbo.DiningClassTime AS E ON A.DiningClassD = E.ClassNo LEFT OUTER JOIN

dbo.Employee AS F ON A.EmpNo=F.EmpNo LEFT OUTER JOIN

dbo.Department AS G ON F.DeptNo = G.DeptNo

4. 创建存储过程

--功能：通过打卡验证员工的就餐信息

--作者：郑紫至

--创建日期：2008-03-12 18:00:00

--测试方法：

declare @CardNo varchar(10)

declare @EmpNo varchar(8)

declare @EmpName nvarchar(50)

declare @CardPassingState int

EXEC [dbo].[CheckDiningEmpInfoByCardNo]

'0013776138',

'02',

@EmpNo output,

@EmpName output,

@CardPassingState output

SELECT @EmpNo,@EmpName,@CardPassingState

CREATE PROCEDURE [dbo].[CheckDiningEmpInfoByCardNo]

@CardNo varchar(10) , --卡号

@MachineNo varchar(5), --卡机编号

@ReadHeadNo int, --卡机读头编号

@EmpNo varchar(8) output, --员工编号

@EmpName nvarchar(20) output,--员工姓名

@CardPassingState int output --打卡验证状态信息

SET @EmpNo=''

--@State取以下整数值的意义

--0.正常通行

--1.无效卡号（包括离职人员）

--2.未到或已过就餐时间

--3.无设置班次

--无效卡号

SELECT

@EmpNo=ISNULL(EmpNo,''),

@EmpName=EmpName

FROM Employee WHERE CardNo=@CardNo AND DimissionDate IS NULL

IF @EmpNo=''

BEGIN

INSERT INTO AbnormityCard

( CardNo,

AbnormityNo,

AbnormityDescription,

MachineNo,

ReadHeadNo)

VALUES

( @CardNo,

'无效卡号',

@MachineNo,

@ReadHeadNo)

SET @CardPassingState=1

RETURN

END

declare @NowTime datetime,@NowDay datetime

declare @Time_B1 datetime,@Time_E1 datetime

declare @Time_B2 datetime,@Time_E2 datetime

declare @Time_B3 datetime,@Time_E3 datetime

declare @Time_B4 datetime,@Time_E4 datetime

declare @RecordCount int,@AbnormityDescription nvarchar(100)

SET @NowTime=GETDATE()

SET @NowDay=CONVERT(varchar(10),@NowTime,120)

SELECT TOP 1

@Time_B1=CONVERT(varchar(11),@NowDay,120)+Time_B1,

@Time_E1=CONVERT(varchar(11),@NowDay,120)+Time_E1,

@Time_B2=CONVERT(varchar(11),@NowDay,120)+Time_B2,

@Time_E2=CONVERT(varchar(11),@NowDay,120)+Time_E2,

@Time_B3=CONVERT(varchar(11),@NowDay,120)+Time_B3,

@Time_E3=CONVERT(varchar(11),@NowDay,120)+Time_E3,

@Time_B4=CONVERT(varchar(11),@NowDay,120)+Time_B4,

@Time_E4=CONVERT(varchar(11),@NowDay,120)+Time_E4

FROM V_DiningClass

WHERE EmpNo=@EmpNo AND @NowDay= DiningDate

SET @RecordCount=@@ROWCOUNT

IF @Time_E4<@Time_B4 SET @Time_E4=DATEADD(day,1,@Time_E4)

IF @RecordCount>0

BEGIN

IF (@NowTime>= @Time_B1 AND @NowTime<= @Time_E1 ) OR

(@NowTime>= @Time_B2 AND @NowTime<= @Time_E2 ) OR

(@NowTime>= @Time_B3 AND @NowTime<= @Time_E3 ) OR

(@NowTime>= @Time_B4 AND @NowTime<= @Time_E4 )

SET @CardPassingState=0

ELSE

SET @CardPassingState=2

END

ELSE

BEGIN

SET @CardPassingState=3

END

IF @CardPassingState=0

BEGIN

--打卡时间的前后半个小时如果没有打过卡就添加打卡记录

IF NOT EXISTS (SELECT ReadCardTime

FROM ReadCard WHERE EmpNo=@EmpNo AND

ReadCardTime >=DATEADD(MINUTE,-30,@NowTime)AND

ReadCardTime <=DATEADD(MINUTE,30,@NowTime))

BEGIN

INSERT INTO ReadCardT(

EmpNo,

CardNo,

MaincheNo,

ReadHeadNo,

ReadCardTime)

VALUES(

@EmpNo,

@CardNo,

@MachineNo,

@ReadHeadNo,

@NowTime)

END

IF @CardPassingState<>0

BEGIN

--打卡时间的前后半个小时如果没有打过卡就添加打卡异常记录

IF NOT EXISTS (SELECT EmpNo

FROM AbnormityCard WHERE EmpNo=@EmpNo AND

ReadCardTime >=DATEADD(MINUTE,-30,@NowTime) AND

ReadCardTime <=DATEADD(MINUTE,30,@NowTime))

BEGIN

SET @AbnormityDescription=CASE @CardPassingState

WHEN 2 THEN '未到或已过就餐时间'

WHEN 3 THEN '未设置班次'

END

INSERT INTO AbnormityCard(

CardNo,

EmpNo,

AbnormityNo,

AbnormityDescription,

MachineNo,

ReadHeadNo,

ReadCardTime)

VALUES (

@CardNo,

@EmpNo,

@CardPassingState,

@AbnormityDescription,

@MachineNo,

@ReadHeadNo,

@NowTime)

END