الانتقال إلى المحتوى

Covert Script From Sqlserver To Sql

mo7asb

بواسطة mo7asb
في SQL and PL/SQL

 مشاركة

Recommended Posts

Ahmed_oracle مستخدم مقيم

Ahmed_oracle

بتاريخ:
بتاريخ: (معدل)

الجداول السابقة لا يوجد ربط بينها !! هل هذا سليم ؟
before

CREATE TABLE CHECKEXACT (
EXACTID INT IDENTITY(1,1) NOT NULL ,
USERID INT NULL DEFAULT 0,              
CHECKTIME DATETIME NULL DEFAULT 0,      
CHECKTYPE VARCHAR (2) NULL DEFAULT 0,   
ISADD SMALLINT NULL DEFAULT 0,          
YUYIN VARCHAR (25) NULL ,         
ISMODIFY SMALLINT NULL DEFAULT 0,
ISDELETE SMALLINT NULL DEFAULT 0,       
INCOUNT SMALLINT NULL DEFAULT 0,        
ISCOUNT SMALLINT NULL DEFAULT 0,        
MODIFYBY VARCHAR (20) NULL,      
[DATE] DATETIME NULL,             
       CONSTRAINT EXACTID PRIMARY KEY (EXACTID)             
)
;

CREATE TABLE CHECKINOUT (               
USERID INT NOT NULL ,               
CHECKTIME DATETIME NOT NULL DEFAULT GETDATE(),       
CHECKTYPE VARCHAR (1) NULL DEFAULT 'I',
VERIFYCODE INT NULL DEFAULT 0,           
SENSORID VARCHAR (5) NULL,          
       CONSTRAINT USERCHECKTIME PRIMARY KEY (USERID, CHECKTIME)
)
;

CREATE TABLE DEPARTMENTS (              
DEPTID INT IDENTITY(1,1) NOT NULL ,    
DEPTNAME VARCHAR (30) NULL ,             
SUPDEPTID INT NOT NULL DEFAULT 1,                      
       CONSTRAINT DEPTID PRIMARY KEY (DEPTID)
)
;

CREATE TABLE EXCNOTES (              
USERID INT NULL ,
ATTDATE DATETIME NULL ,
NOTES VARCHAR (200) NULL
)  
;

CREATE TABLE HOLIDAYS (                            
HOLIDAYID INT IDENTITY(1,1) NOT NULL ,   
HOLIDAYNAME VARCHAR (20) NULL ,            
HOLIDAYYEAR SMALLINT NULL ,                
HOLIDAYMONTH SMALLINT NULL ,               
HOLIDAYDAY SMALLINT NULL DEFAULT 1,                 
STARTTIME DATETIME NULL ,                  
DURATION SMALLINT NULL ,                   
HOLIDAYTYPE SMALLINT NULL ,                
XINBIE VARCHAR (4) NULL ,                     
MINZU VARCHAR (50) NULL,                       
       CONSTRAINT HOLID PRIMARY KEY (HOLIDAYID)
)
;

CREATE TABLE NUM_RUN (                              
NUM_RUNID INT IDENTITY(1,1) NOT NULL ,    
OLDID INT NULL DEFAULT -1,                            
NAME VARCHAR (30) NOT NULL ,                
STARTDATE DATETIME NULL DEFAULT '1900-1-1',                   
ENDDATE DATETIME NULL DEFAULT '2099-12-31',                     
CYLE SMALLINT NULL DEFAULT 1,                        
UNITS SMALLINT NULL DEFAULT 1,                        
       CONSTRAINT NUMID PRIMARY KEY (NUM_RUNID)
)
;

CREATE TABLE NUM_RUN_DEIL (                      
NUM_RUNID SMALLINT NOT NULL ,                
STARTTIME DATETIME NOT NULL ,                
ENDTIME DATETIME NULL ,                  
SDAYS SMALLINT NOT NULL ,                    
EDAYS SMALLINT NULL ,
       SCHCLASSID INT NULL DEFAULT -1,
       CONSTRAINT NUMID2 PRIMARY KEY (NUM_RUNID, SDAYS, STARTTIME)
)
;

CREATE TABLE SECURITYDETAILS (                 
SECURITYDETAILID INT IDENTITY(1,1) NOT NULL ,
USERID SMALLINT NULL ,
DEPTID SMALLINT NULL ,
SCHEDULE SMALLINT NULL ,
USERINFO SMALLINT NULL ,
ENROLLFINGERS SMALLINT NULL ,
REPORTVIEW SMALLINT NULL ,
REPORT VARCHAR (10) NULL,
       CONSTRAINT NAMEID2 PRIMARY KEY (SECURITYDETAILID)
)  
;

CREATE TABLE SHIFT (                           
SHIFTID INT IDENTITY(1,1) NOT NULL ,  
NAME VARCHAR (20) NULL ,                   
USHIFTID INT NULL DEFAULT -1,                     
STARTDATE DATETIME NOT NULL DEFAULT '1900-1-1',           
ENDDATE DATETIME NULL DEFAULT '1900-12-31',                 
RUNNUM SMALLINT NULL DEFAULT 0,                  
SCH1 INT NULL DEFAULT 0,                         
SCH2 INT NULL DEFAULT 0,
SCH3 INT NULL DEFAULT 0,
SCH4 INT NULL DEFAULT 0,
SCH5 INT NULL DEFAULT 0,
SCH6 INT NULL DEFAULT 0,
SCH7 INT NULL DEFAULT 0,
SCH8 INT NULL DEFAULT 0,
SCH9 INT NULL DEFAULT 0,
SCH10 INT NULL DEFAULT 0,
SCH11 INT NULL DEFAULT 0,
SCH12 INT NULL DEFAULT 0,
CYCLE SMALLINT NULL DEFAULT 0,             
UNITS SMALLINT NULL DEFAULT 0 ,             
       CONSTRAINT SHIFTS PRIMARY KEY (SHIFTID)
)  
;

CREATE TABLE TEMPLATE (                           
TEMPLATEID INT IDENTITY(1,1) NOT NULL ,  
USERID INT NOT NULL ,                      
FINGERID INT NOT NULL ,                    
TEMPLATE image NOT NULL ,                  
TEMPLATE2 image NULL ,                  
TEMPLATE3 image NULL ,
BITMAPPICTURE image NULL ,
BITMAPPICTURE2 image NULL ,
BITMAPPICTURE3 image NULL ,                  
BITMAPPICTURE4 image NULL ,                  
USETYPE SMALLINT NULL ,                      
       CONSTRAINT TEMPLATED PRIMARY KEY (TEMPLATEID)
)
;

CREATE TABLE USER_OF_RUN (              
USERID INT NOT NULL ,           
NUM_OF_RUN_ID INT not NULL ,        
STARTDATE DATETIME not NULL DEFAULT '1900-1-1',          
ENDDATE DATETIME not NULL DEFAULT '2099-12-31',            
ISNOTOF_RUN INT NULL DEFAULT 0,          
ORDER_RUN INT NULL ,              
       CONSTRAINT USER_ST_NUM PRIMARY KEY (USERID, NUM_OF_RUN_ID, STARTDATE, ENDDATE)
)
;

CREATE TABLE USER_SPEDAY (                    
USERID INT NOT NULL ,
STARTSPECDAY DATETIME NOT NULL DEFAULT '1900-1-1',          
ENDSPECDAY DATETIME NULL DEFAULT '2099-12-31',            
DATEID SMALLINT not NULL DEFAULT -1,                
YUANYING VARCHAR (200) NULL ,         
[DATE] DATETIME NULL ,                  
       CONSTRAINT USER_SEP PRIMARY KEY (USERID, STARTSPECDAY, DATEID)
)
;

CREATE TABLE USER_TEMP_SCH (                  
USERID INT not NULL ,                     
COMETIME DATETIME not NULL ,              
LEAVETIME DATETIME not NULL ,             
       OVERTIME INT not NULL DEFAULT 0,     
[TYPE] SMALLINT NULL DEFAULT 0,                  
FLAG SMALLINT NULL DEFAULT 1,
       SCHCLASSID INT NULL DEFAULT -1,
       CONSTRAINT USER_TEMP PRIMARY KEY (USERID, COMETIME, LEAVETIME)

)
;

CREATE TABLE USERINFO (                      
USERID INT IDENTITY(1,1) NOT NULL ,   
BADGENUMBER VARCHAR (12) NOT NULL ,         
SSN VARCHAR (20) NULL ,                 
NAME VARCHAR (20) NULL ,               
GENDER VARCHAR (2) NULL ,               
TITLE VARCHAR (20) NULL ,               
PAGER VARCHAR (20) NULL ,              
BIRTHDAY DATETIME NULL ,                
HIREDDAY DATETIME NULL ,                
STREET VARCHAR (40) NULL ,             
CITY VARCHAR (2) NULL ,                 
STATE VARCHAR (2) NULL ,                
ZIP VARCHAR (12) NULL ,                 
OPHONE VARCHAR (20) NULL ,
FPHONE VARCHAR (20) NULL ,              
VERIFICATIONMETHOD SMALLINT NULL ,      
DEFAULTDEPTID SMALLINT NULL  DEFAULT 1,           
SECURITYFLAGS SMALLINT NULL ,           
ATT SMALLINT NOT NULL DEFAULT 1,        
INLATE SMALLINT NOT NULL DEFAULT 1,                    
OUTEARLY SMALLINT NOT NULL DEFAULT 1,                   
OVERTIME SMALLINT NOT NULL DEFAULT 1,                    
SEP SMALLINT NOT NULL DEFAULT 1,                     
HOLIDAY SMALLINT NOT NULL DEFAULT 1,                 
MINZU VARCHAR (8) NULL ,                
[PASSWORD] VARCHAR (20) NULL ,          
LUNCHDURATION SMALLINT NOT NULL DEFAULT 1,
       MVerifyPass VARCHAR(10) NULL, 
PHOTO Image NULL,
       CONSTRAINT USERIDS PRIMARY KEY (USERID)
)
;


CREATE  UNIQUE  INDEX USERFINGER ON TEMPLATE(USERID, FINGERID)
;

CREATE  UNIQUE  INDEX HOLIDAYNAME ON HOLIDAYS(HOLIDAYNAME)
;

CREATE  INDEX DEPTNAME ON DEPARTMENTS(DEPTNAME)
;

CREATE  UNIQUE  INDEX EXCNOTE ON EXCNOTES(USERID, ATTDATE)
;

CREATE  UNIQUE  INDEX BADGENUMBER ON USERINFO(BADGENUMBER)
;

Create Table LeaveClass(
 LeaveId INT Identity(1,1) not null primary key,
 LeaveName VARCHAR(20) not null,                
 MinUnit float not null default 1,              
 Unit smallint not null default 1,              
 RemaindProc smallint not null default 1,     
 RemaindCount smallint not null default 1,    
 ReportSymbol varchar(4) not null default '-',
 Deduct float not null default 0,             
 Color int not null default 0,
 Classify SMALLINT NOT null default 0)
;

Create Table LeaveClass1(
 LeaveId INT Identity(999,1) not null primary key,
 LeaveName VARCHAR(20) not null,
 MinUnit float not null default 1,
 Unit smallint not null default 0,
 RemaindProc smallint not null default 2,
 RemaindCount smallint not null default 1,
 ReportSymbol varchar(4) not null default '-',
 Deduct float not null default 0,
 LeaveType SMALLINT not null default 0,
 Color int not null default 0,
 Classify SMALLINT not null default 0,
 Calc text null)
;

CREATE TABLE SchClass(
 schClassid INT identity(1,1) NOT NULL PRIMARY KEY,
 schName VARCHAR(20) NOT null,
 StartTime datetime NOT NULL,
 EndTime datetime NOT NULL,
 LateMinutes int null,
 EarlyMinutes int null,
 CheckIn int null default 1,
 CheckOut int null default 1,
 CheckInTime1 datetime NULL,
 CheckInTime2 datetime NULL,
 CheckOutTime1 datetime NULL,
 CheckOutTime2 datetime NULL,
 Color Int NULL default 16715535,
 AutoBind SMALLINT NULL DEFAULT 1)
;


Create Table AttParam(
 PARANAME VARCHAR (20) NOT NULL Primary key,
 PARATYPE VARCHAR (2) NULL ,
 PARAVALUE VARCHAR(100) NOT NULL)
;


------------------------------
after

create sequence S_CHECKEXACT;

create sequence S_DEPARTMENTS;

create sequence S_HOLIDAYS;

create sequence S_LeaveClass;

create sequence S_LeaveClass1;

create sequence S_NUM_RUN;

create sequence S_SECURITYDETAILS;

create sequence S_SHIFT;

create sequence S_SchClass;

create sequence S_TEMPLATE;

create sequence S_USERINFO;

create table AttParam  (
  PARANAME             VARCHAR2(20)                    not null,
  PARATYPE             VARCHAR2(2),
  PARAVALUE            VARCHAR2(100)                   not null,
  constraint PK_ATTPARAM primary key (PARANAME)
);

create table CHECKEXACT  (
  EXACTID              NUMBER(6)                       not null,
  USERID               INTEGER                        default 0,
  CHECKTIME            DATE                           default '0',
  CHECKTYPE            VARCHAR2(2)                    default '0',
  ISADD                SMALLINT                       default 0,
  YUYIN VARCHAR2(25),
  ISMODIFY SMALLINT                       default 0,
  ISDELETE             SMALLINT                       default 0,
  INCOUNT              SMALLINT                       default 0,
  ISCOUNT              SMALLINT                       default 0,
  MODIFYBY             VARCHAR2(20),
  DATE               DATE,
  constraint EXACTID primary key (EXACTID)
);

create table CHECKINOUT  (
  USERID               INTEGER                         not null,
  CHECKTIME            DATE                           default 'getdate()' not null,
  CHECKTYPE            VARCHAR2(1)                    default 'I',
  VERIFYCODE           INTEGER                        default 0,
  SENSORID             VARCHAR2(5),
  constraint USERCHECKTIME primary key (USERID, CHECKTIME)
);

create table DEPARTMENTS  (
  DEPTID               NUMBER(6)                       not null,
  DEPTNAME             VARCHAR2(30),
  SUPDEPTID            INTEGER                        default 1 not null,
  constraint DEPTID primary key (DEPTID)
);

create index DEPTNAME on DEPARTMENTS (
  DEPTNAME ASC );

create table EXCNOTES  (
  USERID               INTEGER                         not null,
  ATTDATE              DATE                            not null,
  NOTES                VARCHAR2(200)
);

create unique index EXCNOTE on EXCNOTES (
  USERID ASC,
  ATTDATE ASC );

create table HOLIDAYS  (
  HOLIDAYID            NUMBER(6)                       not null,
  HOLIDAYNAME          VARCHAR2(20)                    not null,
  HOLIDAYYEAR          SMALLINT,
  HOLIDAYMONTH         SMALLINT,
  HOLIDAYDAY           SMALLINT                       default 1,
  STARTTIME            DATE,
  DURATION             SMALLINT,
  HOLIDAYTYPE          SMALLINT,
  XINBIE               VARCHAR2(4),
  MINZU                VARCHAR2(50),
  constraint HOLID primary key (HOLIDAYID)
);

create unique index HOLIDAYNAME on HOLIDAYS (
  HOLIDAYNAME ASC );

create table LeaveClass  (
  LeaveId            NUMBER(6)                       not null,
  LeaveName          VARCHAR2(20)                    not null,
  MinUnit            FLOAT                          default 1 not null,
  Unit               SMALLINT                       default 1 not null,
  RemaindProc        SMALLINT                       default 1 not null,
  RemaindCount       SMALLINT                       default 1 not null,
  ReportSymbol       VARCHAR2(4)                    default '-' not null,
  Deduct             FLOAT                          default 0 not null,
  Color              INTEGER                        default 0 not null,
  Classify           SMALLINT                       default 0 not null,
  constraint PK_LEAVECLASS primary key (LeaveId)
);

create table LeaveClass1  (
  LeaveId            NUMBER(6)                       not null,
  LeaveName          VARCHAR2(20)                    not null,
  MinUnit            FLOAT                          default 1 not null,
  Unit               SMALLINT                       default 0 not null,
  RemaindProc        SMALLINT                       default 2 not null,
  RemaindCount       SMALLINT                       default 1 not null,
  ReportSymbol       VARCHAR2(4)                    default '-' not null,
  Deduct             FLOAT                          default 0 not null,
  LeaveType          SMALLINT                       default 0 not null,
  Color              INTEGER                        default 0 not null,
  Classify           SMALLINT                       default 0 not null,
  Calc               CLOB,
  constraint PK_LEAVECLASS1 primary key (LeaveId)
);

create table NUM_RUN  (
  NUM_RUNID            NUMBER(6)                       not null,
  OLDID                INTEGER                        default -1,
  NAME                 VARCHAR2(30)                    not null,
  STARTDATE            DATE                           default '1900-1-1',
  ENDDATE              DATE                           default '2099-12-31',
  CYLE                 SMALLINT                       default 1,
  UNITS                SMALLINT                       default 1,
  constraint NUMID primary key (NUM_RUNID)
);

create table NUM_RUN_DEIL  (
  NUM_RUNID            SMALLINT                        not null,
  STARTTIME            DATE                            not null,
  ENDTIME              DATE,
  SDAYS                SMALLINT                        not null,
  EDAYS                SMALLINT,
  SCHCLASSID           INTEGER                        default -1,
  constraint NUMID2 primary key (NUM_RUNID, SDAYS, STARTTIME)
);

create table SECURITYDETAILS  (
  SECURITYDETAILID     NUMBER(6)                       not null,
  USERID               SMALLINT,
  DEPTID               SMALLINT,
  SCHEDULE             SMALLINT,
  USERINFO             SMALLINT,
  ENROLLFINGERS        SMALLINT,
  REPORTVIEW           SMALLINT,
  REPORT               VARCHAR2(10),
  constraint NAMEID2 primary key (SECURITYDETAILID)
);

create table SHIFT  (
  SHIFTID              NUMBER(6)                       not null,
  NAME                 VARCHAR2(20),
  USHIFTID             INTEGER                        default -1,
  STARTDATE            DATE                           default '1900-1-1' not null,
  ENDDATE              DATE                           default '1900-12-31',
  RUNNUM               SMALLINT                       default 0,
  SCH1                 INTEGER                        default 0,
  SCH2                 INTEGER                        default 0,
  SCH3                 INTEGER                        default 0,
  SCH4                 INTEGER                        default 0,
  SCH5                 INTEGER                        default 0,
  SCH6                 INTEGER                        default 0,
  SCH7                 INTEGER                        default 0,
  SCH8                 INTEGER                        default 0,
  SCH9                 INTEGER                        default 0,
  SCH10                INTEGER                        default 0,
  SCH11                INTEGER                        default 0,
  SCH12                INTEGER                        default 0,
  CYCLE                SMALLINT                       default 0,
  UNITS                SMALLINT                       default 0,
  constraint SHIFTS primary key (SHIFTID)
);

create table SchClass  (
  schClassid         NUMBER(6)                       not null,
  schName            VARCHAR2(20)                    not null,
  StartTime          DATE                            not null,
  EndTime            DATE                            not null,
  LateMinutes        INTEGER,
  EarlyMinutes       INTEGER,
  CheckIn INTEGER                        default 1,
  CheckOut           INTEGER                        default 1,
  CheckInTime1       DATE,
  CheckInTime2       DATE,
  CheckOutTime1      DATE,
  CheckOutTime2      DATE,
  Color              INTEGER                        default 16715535,
  AutoBind           SMALLINT                       default 1,
  constraint PK_SCHCLASS primary key (schClassid)
);

create table TEMPLATE  (
  TEMPLATEID           NUMBER(6)                       not null,
  USERID               INTEGER                         not null,
  FINGERID             INTEGER                         not null,
  TEMPLATE             BLOB                            not null,
  TEMPLATE2            BLOB,
  TEMPLATE3            BLOB,
  BITMAPPICTURE        BLOB,
  BITMAPPICTURE2       BLOB,
  BITMAPPICTURE3       BLOB,
  BITMAPPICTURE4       BLOB,
  USETYPE              SMALLINT,
  constraint TEMPLATED primary key (TEMPLATEID)
);

create unique index USERFINGER on TEMPLATE (
  USERID ASC,
  FINGERID ASC );

create table USERINFO  (
  USERID               NUMBER(6)                       not null,
  BADGENUMBER          VARCHAR2(12)                    not null,
  SSN                  VARCHAR2(20),
  NAME                 VARCHAR2(20),
  GENDER               VARCHAR2(2),
  TITLE                VARCHAR2(20),
  PAGER                VARCHAR2(20),
  BIRTHDAY             DATE,
  HIREDDAY             DATE,
  STREET               VARCHAR2(40),
  CITY                 VARCHAR2(2),
  STATE                VARCHAR2(2),
  ZIP                  VARCHAR2(12),
  OPHONE               VARCHAR2(20),
  FPHONE               VARCHAR2(20),
  VERIFICATIONMETHOD   SMALLINT,
  DEFAULTDEPTID        SMALLINT                       default 1,
  SECURITYFLAGS        SMALLINT,
  ATT                  SMALLINT                       default 1 not null,
  INLATE               SMALLINT                       default 1 not null,
  OUTEARLY             SMALLINT                       default 1 not null,
  OVERTIME             SMALLINT                       default 1 not null,
  SEP                  SMALLINT                       default 1 not null,
  HOLIDAY              SMALLINT                       default 1 not null,
  MINZU                VARCHAR2(8),
  PASSWORD             VARCHAR2(20),
  LUNCHDURATION        SMALLINT                       default 1 not null,
  MVerifyPass        VARCHAR2(10),
  PHOTO                BLOB,
  constraint USERIDS primary key (USERID)
);

create unique index BADGENUMBER on USERINFO (
  BADGENUMBER ASC );

create table USER_OF_RUN  (
  USERID               INTEGER                         not null,
  NUM_OF_RUN_ID        INTEGER                         not null,
  STARTDATE            DATE                           default '1900-1-1' not null,
  ENDDATE              DATE                           default '2099-12-31' not null,
  ISNOTOF_RUN          INTEGER                        default 0,
  ORDER_RUN            INTEGER,
  constraint USER_ST_NUM primary key (USERID, NUM_OF_RUN_ID, STARTDATE, ENDDATE)
);

create table USER_SPEDAY  (
  USERID               INTEGER                         not null,
  STARTSPECDAY         DATE                           default '1900-1-1' not null,
  ENDSPECDAY           DATE                           default '2099-12-31',
  DATEID               SMALLINT                       default -1 not null,
  YUANYING             VARCHAR2(200),
  DATE               DATE,
  constraint USER_SEP primary key (USERID, STARTSPECDAY, DATEID)
);

create table USER_TEMP_SCH  (
  USERID               INTEGER                         not null,
  COMETIME             DATE                            not null,
  LEAVETIME            DATE                            not null,
  OVERTIME             INTEGER                        default 0 not null,
  TYPE                 SMALLINT                       default 0,
  FLAG                 SMALLINT                       default 1,
  SCHCLASSID           INTEGER                        default -1,
  constraint USER_TEMP primary key (USERID, COMETIME, LEAVETIME)
);

تم تعديل بواسطة Ahmed_oracle
رابط هذا التعليق
شارك
mo7asb مستخدم متعاون

mo7asb

بتاريخ:
  • كاتب الموضوع
بتاريخ:

شكرا جزيلا يا اخ احمد
هذه الجداول لماكينهfinger print وهى موجوده بهذا النمط فى اسطوانة التعريفات
وانا زيك مستغرب من انه ما فيش اى علاقه بس انا هاجربها الاول وبعدين هاشوف لو ما اشتغلتشى هاتصل بالشركه وهاقول لهم
ومتشكرييييييييييييييييييييييييييييين جدااااااا

رابط هذا التعليق
شارك

انضم إلى المناقشة

يمكنك المشاركة الآن والتسجيل لاحقاً. إذا كان لديك حساب, سجل دخولك الآن لتقوم بالمشاركة من خلال حسابك.

زائر
أضف رد على هذا الموضوع...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   تمت استعادة المحتوى السابق الخاص بك.   مسح المحرر

×   You cannot paste images directly. Upload or insert images from URL.

جاري التحميل
×
 مشاركة
عودة لقائمة الموضوعات
×
×
  • أضف...

برجاء الإنتباه

بإستخدامك للموقع فأنت تتعهد بالموافقة على هذه البنود: سياسة الخصوصية

  أوافق