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

 

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

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

               ------ 郑紫至

               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