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

  • تسجيل الدخول عبر الفيس بوك تسجيل الدخول عبر تويتر Log In with LinkedIn Log In with Google      تسجيل دخول    
  • إنشاء حساب

صورة
- - - - -

Covert Script From Sqlserver To Sql


2 رد (ردود) على هذا الموضوع

#1 mo7asb

mo7asb

    مشترك

  • الأعضــاء
  • 149 مشاركة
  • البـلـد: Country Flag

تاريخ المشاركة 14 November 2009 - 01:08 AM

i want to covert this script from sql server to 'sql' please

ملفات مرفقة

  • ملف مرفق  sqlserver.txt   13.74كيلو   29 عدد مرات التحميل


#2 Ahmed_oracle

Ahmed_oracle

    عضو نشط

  • الأعضــاء
  • 240 مشاركة
  • البـلـد: Country Flag

تاريخ المشاركة 14 November 2009 - 02:55 AM

الجداول السابقة لا يوجد ربط بينها !! هل هذا سليم ؟
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, 14 November 2009 - 03:02 AM.

سبحان الله ... والحمد لله 


#3 mo7asb

mo7asb

    مشترك

  • الأعضــاء
  • 149 مشاركة
  • البـلـد: Country Flag

تاريخ المشاركة 14 November 2009 - 10:00 PM

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