让知识更加联贯
让技术走进生活
我的博客
我的程序
我的网络
------
郑紫至
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]
AS
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',
1,
@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 --
打卡验证状态信息
AS
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,
1,
'
无效卡号
'
,
@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
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
END