aibeike/sql/CreateTable.sql

CREATE TABLE CT_DOR_Dormitory (
	FName_l1 NVARCHAR ( 255 ),
	FName_l2 NVARCHAR ( 255 ),
	FName_l3 NVARCHAR ( 255 ),
	FNumber NVARCHAR ( 80 ),
	FDescription_l1 NVARCHAR ( 255 ),
	FDescription_l2 NVARCHAR ( 255 ),
	FDescription_l3 NVARCHAR ( 255 ),
	FSimpleName NVARCHAR ( 80 ),
	FIsLeaf INT,
	FLevel INT,
	FLongNumber NVARCHAR ( 80 ),
	FID VARCHAR ( 44 ) DEFAULT '' NOT NULL,
	FCreatorID VARCHAR ( 44 ),
	FCreateTime DateTime,
	FLastUpdateUserID VARCHAR ( 44 ),
	FLastUpdateTime DateTime,
	FParentID VARCHAR ( 44 ),
	FControlUnitID VARCHAR ( 44 ) DEFAULT '11111111-1111-1111-1111-111111111111CCE7AED4',
	FDisplayName_l1 NVARCHAR ( 80 ),
	FDisplayName_l2 NVARCHAR ( 80 ),
	FDisplayName_l3 NVARCHAR ( 80 ),
	CFRoomNumber NVARCHAR ( 100 ),
	CFRoomClassification VARCHAR ( 100 ),
	CFRoomTypeID VARCHAR ( 44 ),
	CFDormitoryTypeID VARCHAR ( 44 ),
	CFOccupantsNumber INT,
	CFOccupancyCapacity INT,
	CFIsFull INT,
	CFRemarks NVARCHAR ( 255 ),
	CFState INT DEFAULT 1,
	CFIsSealUp INT DEFAULT 0,
CONSTRAINT CPK_DOR_Dormitos62 PRIMARY KEY ( FID ));
CREATE TABLE CT_OCC_Occupants (
	FName_l1 NVARCHAR ( 255 ),
	FName_l2 NVARCHAR ( 255 ),
	FName_l3 NVARCHAR ( 255 ),
	FNumber NVARCHAR ( 80 ),
	FDescription_l1 NVARCHAR ( 255 ),
	FDescription_l2 NVARCHAR ( 255 ),
	FDescription_l3 NVARCHAR ( 255 ),
	FSimpleName NVARCHAR ( 80 ),
	FID VARCHAR ( 44 ) DEFAULT '' NOT NULL,
	FCreatorID VARCHAR ( 44 ),
	FCreateTime DateTime,
	FLastUpdateUserID VARCHAR ( 44 ),
	FLastUpdateTime DateTime,
	FTreeID VARCHAR ( 44 ),
	FControlUnitID VARCHAR ( 44 ) DEFAULT '11111111-1111-1111-1111-111111111111CCE7AED4',
	CFPersonID VARCHAR ( 44 ),
	CFPersonPositionID VARCHAR ( 44 ),
	CFPersonDormitoryT VARCHAR ( 44 ),
	CFCheckInDate DateTime,
	CFCheckOutDate DateTime,
	CFDormitoryID VARCHAR ( 44 ),
	CFRemarks NVARCHAR ( 255 ),
	CFState  INT DEFAULT 1,
CONSTRAINT CPK_OCC_Occupan9mg PRIMARY KEY ( FID ));
CREATE TABLE T_OCC_OccupantsTREE (
	FName_l1 NVARCHAR ( 255 ),
	FName_l2 NVARCHAR ( 255 ),
	FName_l3 NVARCHAR ( 255 ),
	FNumber NVARCHAR ( 80 ),
	FDescription_l1 NVARCHAR ( 255 ),
	FDescription_l2 NVARCHAR ( 255 ),
	FDescription_l3 NVARCHAR ( 255 ),
	FSimpleName NVARCHAR ( 80 ),
	FIsLeaf INT,
	FLevel INT,
	FLongNumber NVARCHAR ( 80 ),
	FID VARCHAR ( 44 ) DEFAULT '' NOT NULL,
	FCreatorID VARCHAR ( 44 ),
	FCreateTime DateTime,
	FLastUpdateUserID VARCHAR ( 44 ),
	FLastUpdateTime DateTime,
	FParentID VARCHAR ( 44 ),
	FControlUnitID VARCHAR ( 44 ) DEFAULT '11111111-1111-1111-1111-111111111111CCE7AED4',
	FDisplayName_l1 NVARCHAR ( 80 ),
	FDisplayName_l2 NVARCHAR ( 80 ),
	FDisplayName_l3 NVARCHAR ( 80 ),
CONSTRAINT CPK_OCC_OccpnTRb0h PRIMARY KEY ( FID ));
CREATE TABLE CT_OTH_DormitoryType (
	FName_l1 NVARCHAR ( 255 ),
	FName_l2 NVARCHAR ( 255 ),
	FName_l3 NVARCHAR ( 255 ),
	FNumber NVARCHAR ( 80 ),
	FDescription_l1 NVARCHAR ( 255 ),
	FDescription_l2 NVARCHAR ( 255 ),
	FDescription_l3 NVARCHAR ( 255 ),
	FSimpleName NVARCHAR ( 80 ),
	FID VARCHAR ( 44 ) DEFAULT '' NOT NULL,
	FCreatorID VARCHAR ( 44 ),
	FCreateTime DateTime,
	FLastUpdateUserID VARCHAR ( 44 ),
	FLastUpdateTime DateTime,
	FTreeID VARCHAR ( 44 ),
	FControlUnitID VARCHAR ( 44 ) DEFAULT '11111111-1111-1111-1111-111111111111CCE7AED4',
	CFState  INT DEFAULT 1,
CONSTRAINT CPK_OTH_DrmtrTI9n3 PRIMARY KEY ( FID ));
CREATE TABLE CT_OTH_PersonDormitoryType (
	FName_l1 NVARCHAR ( 255 ),
	FName_l2 NVARCHAR ( 255 ),
	FName_l3 NVARCHAR ( 255 ),
	FNumber NVARCHAR ( 80 ),
	FDescription_l1 NVARCHAR ( 255 ),
	FDescription_l2 NVARCHAR ( 255 ),
	FDescription_l3 NVARCHAR ( 255 ),
	FSimpleName NVARCHAR ( 80 ),
	FID VARCHAR ( 44 ) DEFAULT '' NOT NULL,
	FCreatorID VARCHAR ( 44 ),
	FCreateTime DateTime,
	FLastUpdateUserID VARCHAR ( 44 ),
	FLastUpdateTime DateTime,
	FTreeID VARCHAR ( 44 ),
	FControlUnitID VARCHAR ( 44 ) DEFAULT '11111111-1111-1111-1111-111111111111CCE7AED4',
	CFState INT DEFAULT 1,
CONSTRAINT CPK_OTH_PersonDk1d PRIMARY KEY ( FID ));
CREATE TABLE CT_OTH_RoomType (
	FName_l1 NVARCHAR ( 255 ),
	FName_l2 NVARCHAR ( 255 ),
	FName_l3 NVARCHAR ( 255 ),
	FNumber NVARCHAR ( 80 ),
	FDescription_l1 NVARCHAR ( 255 ),
	FDescription_l2 NVARCHAR ( 255 ),
	FDescription_l3 NVARCHAR ( 255 ),
	FSimpleName NVARCHAR ( 80 ),
	FID VARCHAR ( 44 ) DEFAULT '' NOT NULL,
	FCreatorID VARCHAR ( 44 ),
	FCreateTime DateTime,
	FLastUpdateUserID VARCHAR ( 44 ),
	FLastUpdateTime DateTime,
	FTreeID VARCHAR ( 44 ),
	FControlUnitID VARCHAR ( 44 ) DEFAULT '11111111-1111-1111-1111-111111111111CCE7AED4',
	CFState INT DEFAULT 1,
CONSTRAINT CPK_OTH_RoomTypv7q PRIMARY KEY ( FID ));
CREATE TABLE T_OTH_DormitoryTypeTREE (
	FName_l1 NVARCHAR ( 255 ),
	FName_l2 NVARCHAR ( 255 ),
	FName_l3 NVARCHAR ( 255 ),
	FNumber NVARCHAR ( 80 ),
	FDescription_l1 NVARCHAR ( 255 ),
	FDescription_l2 NVARCHAR ( 255 ),
	FDescription_l3 NVARCHAR ( 255 ),
	FSimpleName NVARCHAR ( 80 ),
	FIsLeaf INT,
	FLevel INT,
	FLongNumber NVARCHAR ( 80 ),
	FID VARCHAR ( 44 ) DEFAULT '' NOT NULL,
	FCreatorID VARCHAR ( 44 ),
	FCreateTime DateTime,
	FLastUpdateUserID VARCHAR ( 44 ),
	FLastUpdateTime DateTime,
	FParentID VARCHAR ( 44 ),
	FControlUnitID VARCHAR ( 44 ) DEFAULT '11111111-1111-1111-1111-111111111111CCE7AED4',
	FDisplayName_l1 NVARCHAR ( 80 ),
	FDisplayName_l2 NVARCHAR ( 80 ),
	FDisplayName_l3 NVARCHAR ( 80 ),
CONSTRAINT CPK_OTH_DrmtTTRt14 PRIMARY KEY ( FID ));
CREATE TABLE T_OTH_PERSONDORMITORYTTREE (
	FName_l1 NVARCHAR ( 255 ),
	FName_l2 NVARCHAR ( 255 ),
	FName_l3 NVARCHAR ( 255 ),
	FNumber NVARCHAR ( 80 ),
	FDescription_l1 NVARCHAR ( 255 ),
	FDescription_l2 NVARCHAR ( 255 ),
	FDescription_l3 NVARCHAR ( 255 ),
	FSimpleName NVARCHAR ( 80 ),
	FIsLeaf INT,
	FLevel INT,
	FLongNumber NVARCHAR ( 80 ),
	FID VARCHAR ( 44 ) DEFAULT '' NOT NULL,
	FCreatorID VARCHAR ( 44 ),
	FCreateTime DateTime,
	FLastUpdateUserID VARCHAR ( 44 ),
	FLastUpdateTime DateTime,
	FParentID VARCHAR ( 44 ),
	FControlUnitID VARCHAR ( 44 ) DEFAULT '11111111-1111-1111-1111-111111111111CCE7AED4',
	FDisplayName_l1 NVARCHAR ( 80 ),
	FDisplayName_l2 NVARCHAR ( 80 ),
	FDisplayName_l3 NVARCHAR ( 80 ),
CONSTRAINT CPK_OTH_PERSONDrbd PRIMARY KEY ( FID ));
CREATE TABLE T_OTH_RoomTypeTREE (
	FName_l1 NVARCHAR ( 255 ),
	FName_l2 NVARCHAR ( 255 ),
	FName_l3 NVARCHAR ( 255 ),
	FNumber NVARCHAR ( 80 ),
	FDescription_l1 NVARCHAR ( 255 ),
	FDescription_l2 NVARCHAR ( 255 ),
	FDescription_l3 NVARCHAR ( 255 ),
	FSimpleName NVARCHAR ( 80 ),
	FIsLeaf INT,
	FLevel INT,
	FLongNumber NVARCHAR ( 80 ),
	FID VARCHAR ( 44 ) DEFAULT '' NOT NULL,
	FCreatorID VARCHAR ( 44 ),
	FCreateTime DateTime,
	FLastUpdateUserID VARCHAR ( 44 ),
	FLastUpdateTime DateTime,
	FParentID VARCHAR ( 44 ),
	FControlUnitID VARCHAR ( 44 ) DEFAULT '11111111-1111-1111-1111-111111111111CCE7AED4',
	FDisplayName_l1 NVARCHAR ( 80 ),
	FDisplayName_l2 NVARCHAR ( 80 ),
FDisplayName_l3 NVARCHAR ( 80 ),
CONSTRAINT CPK_OTH_RmTTREEgiq PRIMARY KEY ( FID ));

-- 入住申请单
If not exists (select 1 from KSQL_USERTABLES where KSQL_TABNAME='CT_APP_CheckInApplication')
Create Table CT_APP_CheckInApplication ( FFivouchered INT DEFAULT 0,CFManagementNotes VARCHAR(2000),FAdminOrgUnitId VARCHAR(44),FHROrgUnitID VARCHAR(44),FBillState INT,FApproveType INT,FIsMultiEntry INT,FNumber NVARCHAR(80),FBizDate DateTime,FHandlerID VARCHAR(44),FDescription VARCHAR(255),FHasEffected INT,FAuditorID VARCHAR(44),FSourceBillID VARCHAR(80),FSourceFunction VARCHAR(255),FCreatorID VARCHAR(44),FCreateTime DateTime,FLastUpdateUserID VARCHAR(44),FLastUpdateTime DateTime,FControlUnitID VARCHAR(44),FID VARCHAR(44) DEFAULT '' NOT NULL ,CONSTRAINT PK_CheckInAppliadf PRIMARY KEY (FID));

--入住申请单分录
If not exists (select 1 from KSQL_USERTABLES where KSQL_TABNAME='CT_APP_CheckInAE')
Create Table CT_APP_CheckInAE ( FParentID VARCHAR(44),CFPersonID VARCHAR(44),CFAdminOrgID VARCHAR(44),CFPositionID VARCHAR(44),CFCheckInRoomID VARCHAR(44),CFNotes VARCHAR(2000),CFCheckInDate DateTime,CFCheckoutDate DateTime,CFOccupantsID VARCHAR(44),FSeq INT,FID VARCHAR(44) DEFAULT '' NOT NULL ,CONSTRAINT PK_CheckInApplicat PRIMARY KEY (FID));

-- 退宿申请单
If not exists (select 1 from KSQL_USERTABLES where KSQL_TABNAME='CT_APP_CheckoutApplication')
Create Table CT_APP_CheckoutApplication ( FFivouchered INT DEFAULT 0,FAdminOrgUnitId VARCHAR(44),FHROrgUnitID VARCHAR(44),FBillState INT,FApproveType INT,FIsMultiEntry INT,FNumber NVARCHAR(80),FBizDate DateTime,FHandlerID VARCHAR(44),FDescription VARCHAR(255),FHasEffected INT,FAuditorID VARCHAR(44),FSourceBillID VARCHAR(80),FSourceFunction VARCHAR(255),FCreatorID VARCHAR(44),FCreateTime DateTime,FLastUpdateUserID VARCHAR(44),FLastUpdateTime DateTime,FControlUnitID VARCHAR(44),FID VARCHAR(44) DEFAULT '' NOT NULL ,CFManagementNotes NVARCHAR(2000),CONSTRAINT PK_CheckoutApplkje PRIMARY KEY (FID));

-- 退宿申请单分录
If not exists (select 1 from KSQL_USERTABLES where KSQL_TABNAME='CT_APP_CheckoutAE')
Create Table CT_APP_CheckoutAE ( FParentID VARCHAR(44),CFPersonID VARCHAR(44),CFAdminOrgID VARCHAR(44),CFPositionID VARCHAR(44),CFCurrentRoomID VARCHAR(44),CFCheckInDate DateTime,CFCheckoutDate DateTime,CFNotes VARCHAR(2000),CFOccupantsID VARCHAR(44),FSeq INT,FID VARCHAR(44) DEFAULT '' NOT NULL ,CFOldCheckoutDate DateTime,CONSTRAINT PK_CheckoutApplica PRIMARY KEY (FID));