⭐ 欢迎来到虫虫下载站！ | 📦 资源下载 📁 资源专辑 ℹ️ 关于我们
⭐ 虫虫下载站
📤 上传资源

📄 uu1.pas.~2~

📁 用串口发送手机短信 需要GSM Mode设备
💻 ~2~
📖 第 1 页 / 共 5 页
字号:
🔍
12345 
  rtn :boolean;
  i :integer;
begin
  rtn :=false;
  adoQury.Close;  adoQury.SQL.Clear;

  //要能查询历史人员
  sql :='if not exists (select [name] from sysobjects where [name] = ''人员表'')'
			+'CREATE TABLE [人员表] ('
			+'[人员ID]		[smallint] NOT NULL,'
      +'[iTime]	   	[int] NOT NULL ,'
			+'[卡ID]		   [smallint] DEFAULT -1 NOT NULL ,'
			+'[姓名]		  [nvarchar] (20)  NOT NULL ,'
			+'[身份证]		 [varchar] (20)  NULL ,'
			+'[性别]		  [nchar] (1) DEFAULT ''男'' NOT NULL Check (性别=''男'' or 性别=''女''),'
			+'[出生日期]	[smalldatetime] DEFAULT 0 NOT NULL ,'
			+'[部门ID]		[smallint]  NOT NULL ,'
			+'[部门名]	   [nvarchar] (60)  NULL ,'
			+'[工种]	   	[smallint] DEFAULT 0 NOT NULL ,'
			+'[职务]		  [smallint] DEFAULT 0 NOT NULL ,'
			+'[联系电话]	[varchar] (20) NULL ,'
      +'[主要工作地] [tinyint] NOT NULL ,'
      +'[日下井时间]   [smallint]  NULL ,'    //用分钟计算
			+'[月下井次]	[tinyint]  NULL ,'
      +'[班次]	    [tinyint]  NULL ,'
			+'[状态]			[smallint]  DEFAULT 0 NOT NULL ,'//个位表示状态(1 标记为删除) 十百千万表示时间(MMdd)
			+'[创建时间]	[smalldatetime] NULL ,'
			+'[修改时间]	[smalldatetime] NULL ,'
      +'[Limit1]    [tinyint]  NULL ,'
      +'[Limit2]    [tinyint]  NULL ,'
      +'[Limit3]    [tinyint]  NULL ,'
      +'[Limit4]    [tinyint]  NULL ,'
      +'[s1]			  [nvarchar] (20) NULL ,'
      +'[s2]			  [nvarchar] (20) NULL ,'
      +'[s3]			  [nvarchar] (20) NULL ,'
      +'[i1]			  [int] NULL ,'
      +'[i2]			  [int] NULL ,'
      +'[f1]		   	[float] NULL ,'
			+'PRIMARY KEY([人员ID],[iTime])	)';
  adoQury.SQL.Add(sql);
{
  //要能查询历史主要工作地点
  sql :='if not exists (select [name] from sysobjects where [name] = ''主要工作地点表'')'
			+'CREATE TABLE [主要工作地点表] ('
			+'[主要工作地ID]		[tinyint] NOT NULL Check (主要工作地ID>=1),'
      +'[iTime]	   	     [int] NOT NULL ,'
			+'[主要工作地点]	 [nvarchar] (20) NOT NULL Constraint MasterAdr Unique,'
      +'[标识]	   	     [tinyint] NOT NULL ,'  //$01为重点区, $02为限制区($04是否限放宽在此主要工作的人)
			+'PRIMARY KEY([主要工作地ID],[iTime])	)';
  adoQury.SQL.Add(sql);
}
  //要能查询历史工种
  sql :='if not exists (select [name] from sysobjects where [name] = ''工种表'')'
			+'CREATE TABLE [工种表] ('
			+'[工种ID]		[smallint] NOT NULL Check (工种ID>=1),'
      +'[iTime]	    [int] NOT NULL ,'
			+'[工种名]	   [nvarchar] (20) NOT NULL Constraint GongZhong Unique,'
			+'PRIMARY KEY([工种ID],[iTime])	)';
  adoQury.SQL.Add(sql);

  //上下井标识已固化到历史数据中,但考勤仍要依赖班次
  sql :='if not exists (select [name] from sysobjects where [name] = ''班次表'')'
			+'CREATE TABLE [班次表] ('
			+'[班次ID]		[smallint] NOT NULL ,'
      +'[iTime]	    [int] NOT NULL ,'
			+'[班次名]	   [nvarchar] (8) NOT NULL ,'
      +'[开始时间]	[smallint]  NULL ,'
      +'[结束时间]	[smallint]  NULL ,'
			+'PRIMARY KEY([班次ID],[iTime])	)';
  adoQury.SQL.Add(sql);

  //要能查询历史采区
  sql :='if not exists (select [name] from sysobjects where [name] = ''采区表'')'
			+'CREATE TABLE [采区表] ('
			+'[采区ID]		[tinyint] NOT NULL Check (采区ID>=1),'
      +'[iTime]	    [int] NOT NULL ,'
			+'[采区名]	   [nvarchar] (20) NOT NULL Constraint CaiQu Unique,'
			+'PRIMARY KEY([采区ID],[iTime])	)';
  adoQury.SQL.Add(sql);

  //职务,部门不作 iTime处理
  sql :='if not exists (select [name] from sysobjects where [name] = ''职务表'')'
			+'CREATE TABLE [职务表] ('
			+'[职务ID]		[smallint] NOT NULL Check (职务ID>=1),'
			+'[职务名]	   [nvarchar] (20) NOT NULL Constraint Job Unique,'
			+'PRIMARY KEY([职务ID])	)';
  adoQury.SQL.Add(sql);

  sql :='if not exists (select [name] from sysobjects where [name] = ''部门表1'')'
			+'CREATE TABLE [部门表1] ('
			+'[部门ID]		[smallint] NOT NULL Check (部门ID>=10 and 部门ID<=99),'
			+'[部门名]	   [nvarchar] (16) NOT NULL Constraint BuMen1 Unique,'
      +'[负责人]	   [nvarchar] (20) NULL ,'
      +'[负责人ID]  [int] NULL ,'
      +'[联系电话1] [varchar] (20) NULL ,'
      +'[联系电话2] [varchar] (20) NULL ,'
			+'PRIMARY KEY([部门ID])	)';
  adoQury.SQL.Add(sql);

  sql :='if not exists (select [name] from sysobjects where [name] = ''部门表2'')'
			+'CREATE TABLE [部门表2] ('
			+'[部门ID]		[smallint] NOT NULL Check (部门ID>=100 and 部门ID<=999),'
			+'[部门名]	   [nvarchar] (16) NOT NULL,'
      +'[部门ID1]		[smallint] NOT  NULL Constraint BuMen2 Unique(部门名,部门ID1) ,'
      +'[负责人]	   [nvarchar] (20) NULL ,'
      +'[负责人ID]  [int] NULL ,'
      +'[联系电话1] [varchar] (20) NULL ,'
      +'[联系电话2] [varchar] (20) NULL ,'
			+'PRIMARY KEY([部门ID])	)';
  adoQury.SQL.Add(sql);

  sql :='if not exists (select [name] from sysobjects where [name] = ''部门表3'')'
			+'CREATE TABLE [部门表3] ('
			+'[部门ID]		[smallint] NOT NULL Check (部门ID>=1000 and 部门ID<=9999),'
			+'[部门名]	   [nvarchar] (16) NOT NULL ,'
      +'[部门ID2]		[smallint] NOT NULL  Constraint BuMen3 Unique(部门名,部门ID2),'
      +'[负责人]	   [nvarchar] (20) NULL ,'
      +'[负责人ID]  [int] NULL ,'
      +'[联系电话1] [varchar] (20) NULL ,'
      +'[联系电话2] [varchar] (20) NULL ,'
			+'PRIMARY KEY([部门ID])	)';
  adoQury.SQL.Add(sql);

  //进入历史表后,上下井标识已固化,不在依赖基站类型
  sql :='if not exists (select [name] from sysobjects where [name] = ''基站类型表'')'
			+'CREATE TABLE [基站类型表] ('
			+'[基站类型ID]		[smallint] NOT NULL ,'
			+'[基站类型名]	   [nvarchar] (10) NOT NULL ,'
			+'PRIMARY KEY([基站类型ID])	)';
  adoQury.SQL.Add(sql);

  sql :='if not exists (select [name] from sysobjects where [name] = ''基站安装表'')'
			+'CREATE TABLE [基站安装表] ('
			+'[基站ID]	 	 [tinyint]  NOT NULL ,'
      +'[iTime]	     [int] NOT NULL ,'
			+'[基站类型ID] [tinyint]  NOT NULL ,'
      +'[jzCMP]      [int]  NOT NULL Constraint jzCMP Unique,'
			+'[安装地点]	 [nvarchar](20) NULL ,'
			+'[采区]	  	 [tinyint] NULL ,'
			+'[批次]		   [tinyint]  NOT NULL ,'
			+'[是否询检]   [tinyint] DEFAULT 1 NULL ,'
			+'[人数上限]   [smallint] DEFAULT 0 NULL ,'
			+'[层次]		   [tinyint] DEFAULT 0  NULL ,'
      +'[intX]	   	 [smallint] DEFAULT 0  NULL ,'
      +'[intY]		   [smallint] DEFAULT 0  NULL ,'
      +'[intW]	   	 [smallint] DEFAULT 16  NULL ,'
      +'[intH]		   [smallint] DEFAULT 16  NULL ,'
      +'[intX2]	   	 [smallint] DEFAULT 16  NULL ,'
      +'[intY2]		   [smallint] DEFAULT 16  NULL ,'
      +'[标识]	   	 [tinyint]  NULL Check (标识<=3 or 标识=6 or 标识=7),'  //$01为重点区, $02为限制区($04是否限放宽在此主要工作的人).....3时既是重点又是限制, 0什么也不是
      +'[安装时间]   [smalldatetime] NULL ,'
      +'[当前总人数] [smallint] NULL ,'
			+'[s1]		   	 [nvarchar] (20) NULL ,'
			+'[s2]			   [nvarchar] (20) NULL ,'
      +'[s3]			   [nvarchar] (20) NULL ,'
      +'[i1]			   [int]  NULL ,'
      +'[i2]			   [int]  NULL ,'
			+'PRIMARY KEY([基站ID],[iTime])	)';
  adoQury.SQL.Add(sql);

  sql :='if not exists (select [name] from sysobjects where [name] = ''报警表_卡'')'
			+'CREATE TABLE [报警表_卡] ('
			+'[id]			 [bigint] IDENTITY(-2000000000, 1) NOT NULL ,'
			+'[员工ID]   [smallint] NOT NULL ,'
			+'[卡ID]	    [smallint] NOT NULL ,'
      +'[下井Time] [int] NULL ,'		//直接收到的人数
			+'[基站ID]	 [tinyint]  NULL ,'    //重复采集后经处理的人数
			+'[信号强度] [tinyint]  NULL ,'
      +'[状态]		 [tinyint]  NULL ,'        //$80    $40    $20    $10        $08    $04      $02    $01
			+'[接收时间] [datetime] NOT NULL ,'    //盲区  卡警   软警   超时       重点区  限制区域 补采   井下(若为0表示状态不明)
			+'PRIMARY KEY([卡ID],[接收时间])	)';
  adoQury.SQL.Add(sql);

  sql :='if not exists (select [name] from sysobjects where [name] = ''历史卡数据表'')'
			+'CREATE TABLE [历史卡数据表] ('
			+'[id]			[bigint] IDENTITY(-2000000000, 1) NOT NULL ,'
			+'[员工ID]	[smallint]  NOT NULL ,'
			+'[卡ID]		 [smallint]  NOT NULL ,'
			+'[基站ID1]		[tinyint] DEFAULT 255  NULL ,'    //255对应为-1
			+'[信号强度1]	[tinyint] DEFAULT 255  NULL ,'
      +'[基站ID2]		[tinyint] DEFAULT 255  NULL ,'
			+'[信号强度2]	[tinyint] DEFAULT 255  NULL ,'
      +'[基站ID3]		[tinyint] DEFAULT 255  NULL ,'
			+'[信号强度3]	[tinyint] DEFAULT 255  NULL ,'
      +'[当前基站ID]		[tinyint] DEFAULT 255  NULL ,'
			+'[当前信号强度]	[tinyint] DEFAULT 255  NULL ,'
      +'[上一基站ID]		[tinyint] DEFAULT 255  NULL ,'
			+'[上一信号强度]	[tinyint] DEFAULT 255  NULL ,'
			+'[接收时间]		[datetime] NOT NULL ,'
      +'[基站状态]		[tinyint] DEFAULT 255  NULL ,'
			+'[卡状态]	     [tinyint] DEFAULT 0  NULL ,'
      +'[上下井标识]	 [tinyint] DEFAULT 1  NULL ,' 
			+'PRIMARY KEY([卡ID],[接收时间])	)';
  adoQury.SQL.Add(sql);

  sql :='if not exists (select [name] from sysobjects where [name] = ''历史基站数据表'')'
			+'CREATE TABLE [历史基站数据表] ('
			+'[id]			[bigint] IDENTITY(-2000000000, 1) NOT NULL ,'
			+'[基站ID]		  [tinyint] NOT NULL ,'
			+'[基站类型ID]	[tinyint]  NOT NULL ,'
      +'[当前人数]		[smallint] NULL ,'		//直接收到的人数
			+'[过滤人数]		[smallint] NULL ,'    //重复采集后经处理的人数
			+'[基站状态]		[tinyint]  NULL ,'
			+'[接收时间]		[datetime] NOT NULL ,'
			+'PRIMARY KEY([基站ID],[接收时间])	)';
  adoQury.SQL.Add(sql);

  sql:='if not exists (select [name] from sysobjects where [name] = ''历史卡数据统计表'')'
			+'CREATE TABLE [历史卡数据统计表] ('
			+'[id]		   [bigint] IDENTITY(-2000000000, 1) NOT NULL ,'		//无法预计有多少条报警
			+'[员工ID]	 [smallint] NOT NULL ,'		//以天时分秒的时间算出
			+'[卡ID]		  [smallint] NOT NULL ,'
			+'[下井Time] [int]  NOT NULL ,'       //与年月日时分秒关联的整数
      +'[上井Time] [int] NULL ,'
			+'[下上井标识]	  [tinyint] DEFAULT 1 NOT NULL ,'   //1为下井  3为正在井下  4为上井
			+'[基站ID]	     [tinyint] DEFAULT 0 NOT NULL ,'
			+'[信号强度]	   [tinyint]  NULL ,'
      +'[到达时间]	   [datetime]  NOT NULL ,'
			+'[上一基站ID]   [tinyint]  NULL ,' //255或0表示没有上一基站
			+'[上一信号强度] [tinyint]  NULL ,' //255或0表示没有上一基站
      +'[接收时间]	   [datetime] NOT NULL ,' //此存储的是卡最后一次在此的时间
      +'[停留时间]	   [int] DEFAULT 1 NOT NULL ,'   //以秒计算
      +'[状态]	       [tinyint] DEFAULT 0 NULL ,'   //最低位为0表示实际检测,为1表示估算(入口漏检)
			+'PRIMARY KEY([员工ID],[下井Time],[基站ID],[接收时间])	)';
  adoQury.SQL.Add(sql);

  sql :='if not exists (select [name] from sysobjects where [name] = ''考勤表'')'
			+'CREATE TABLE [考勤表] ('
			+'[id]			   [bigint] IDENTITY(-2000000000, 1) NOT NULL ,'
			+'[员工ID]     [smallint] NOT NULL ,'
			+'[卡ID]	      [smallint] NOT NULL ,'
      +'[下井Time]	 [int] NULL ,'		    //
      +'[上井Time]	 [int] NULL ,'		    //
			+'[下上井标识]	[tinyint] NULL ,'    //重复采集后经处理的人数
      +'[iJZ]	       [tinyint] NOT NULL ,'   //为双井口考勤准备
      +'[iZT]	       [tinyint] NOT NULL ,'   //为补采准备
			+'[接收时间]	 [datetime] NOT NULL )';
  adoQury.SQL.Add(sql);

  sql :='if not exists (select [name] from sysobjects where [name] = ''用户表'')'
			+'CREATE TABLE [用户表] ('
			+'[用户名]		[nvarchar](20)   NOT NULL ,'
			+'[密码]			[varbinary](40) NOT NULL ,'
			+'[权限字]		[varbinary](40) NOT NULL ,'
			+'[级别]			[nvarchar] (10) DEFAULT ''普通用户'' NOT NULL ,'
			+'[s1]			[nvarchar] (20) NULL ,'
			+'[s2]			[nvarchar] (20) NULL ,'
			+'PRIMARY KEY([用户名])	)';
  adoQury.SQL.Add(sql);

  sql :='if not exists (select [name] from sysobjects where [name] = ''煤矿信息表'')'
			+'CREATE TABLE [煤矿信息表] ('
			+'[煤矿ID]		 [nvarchar](16)   NOT NULL ,'
			+'[煤矿名]			[nvarchar](20) NOT NULL ,'
			+'[地址]		   [nvarchar](20) NOT NULL ,'
			+'[邮编]			 [char] (6)   NULL ,'
			+'[联系人]			[nvarchar] (10) NULL ,'
			+'[联系电话1]	 [varchar] (20) NULL ,'
      +'[联系电话2]	 [varchar] (20) NULL ,'
      +'[状态]			 [tinyint] NULL ,'
      +'[创建日期] 	 [smalldatetime] NULL ,'
			+'PRIMARY KEY([煤矿ID])	)';
  adoQury.SQL.Add(sql);

  //版本表
12345

⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -