----13--11--2018---
GO
CREATE TABLE [dbo].[HR_EMPLOYEE_SIGN](
	[EMPLOYEE_ID] [nvarchar](20) NOT NULL,
	[EMPLOYEE_SIGN] [image] NOT NULL,
	[SIGN_NOTE] [nvarchar](250) NOT NULL,
	[EMPLOYEE_SIGN_PATH] [nvarchar](250) NOT NULL,
 CONSTRAINT [PK_HR_EMPLOYEE_SIGN] PRIMARY KEY CLUSTERED 
(
	[EMPLOYEE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[HR_REQUEST_CLAIM_OVERTIME](
	[PR_KEY] [uniqueidentifier] NOT NULL,
	[TRAN_ID] [nvarchar](20) NOT NULL,
	[TRAN_TYPE] [nvarchar](20) NOT NULL,
	[TRAN_DATE] [smalldatetime] NOT NULL,
	[EMPLOYEE_ID] [nvarchar](20) NOT NULL,
	[PR_ORGANIZATION_ID] [nvarchar](20) NOT NULL,
	[POSITION_ID] [nvarchar](20) NOT NULL,
	[EMPLOYEE_LEVEL_ID] [nvarchar](20) NOT NULL,
	[LINE_MANAGER_ID] [nvarchar](20) NOT NULL,
	[TRAN_NOTE] [nvarchar](150) NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[TRAN_MODIFIED] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_HR_REQUEST_CLAIM_OVERTIME] PRIMARY KEY CLUSTERED 
(
	[PR_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HR_REQUEST_CLAIM_OVERTIME_DETAIL](
	[PR_KEY] [uniqueidentifier] NOT NULL,
	[FR_KEY] [uniqueidentifier] NOT NULL,
	[TRAN_DATE_DETAIL] [smalldatetime] NOT NULL,
	[PROJECT_ID] [nvarchar](20) NOT NULL,
	[FROM_TIME] [smalldatetime] NOT NULL,
	[TO_TIME] [smalldatetime] NOT NULL,
	[DURATION] [money] NOT NULL,
	[HOUR_RATE] [money] NOT NULL,
	[IS_WORKIG] [smallint] NOT NULL,
	[WORKING_HOUR] [money] NOT NULL,
	[TRAVELLING_HOUR] [money] NOT NULL,
	[REASON] [nvarchar](250) NOT NULL,
 CONSTRAINT [PK_HR_REQUEST_CLAIM_OVERTIME_DETAIL] PRIMARY KEY CLUSTERED 
(
	[PR_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--15-11-2018---
GO
CREATE TABLE [dbo].[HR_EMPLOYEE_MAPPING_PR_DETAIL](
	[EMPLOYEE_ID] [nvarchar](20) NOT NULL,
	[PR_DETAIL_ID] [nvarchar](20) NOT NULL,
	[TIMEKEEPING_TYPE_ID] [nvarchar](20) NOT NULL,
	[ACTIVE] [smallint] NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_HR_EMPLOYEE_MAPPING_PR_DETAIL_1] PRIMARY KEY CLUSTERED 
(
	[EMPLOYEE_ID] ASC,
	[TIMEKEEPING_TYPE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
---22-11-2018-----
GO
Alter table HR_REQUEST_CLAIM_OVERTIME_DETAIL add CLIENT_ID nvarchar(20) not null default ''
GO

Alter table HR_REQUEST_CLAIM_OVERTIME add TRAN_STATUS nvarchar(20) not null default ''
GO

GO


-- 22/11 --
GO
alter table HR_EMPLOYEE_TEMP_IMPORT add IS_SALARY_NET smallint not null DEFAULT 0
GO
alter table HR_EMPLOYEE_TEMP_IMPORT add INFO_EXTRA_STRING_4 nvarchar(200) not null DEFAULT ''
GO
alter table HR_EMPLOYEE_TEMP_IMPORT add INFO_EXTRA_STRING_5 nvarchar(200) not null DEFAULT ''
GO
alter table HR_EMPLOYEE_TEMP_IMPORT add INFO_EXTRA_STRING_6 nvarchar(100) not null DEFAULT ''
GO
alter table HR_EMPLOYEE_TEMP_IMPORT add INFO_EXTRA_STRING_7 nvarchar(100) not null DEFAULT ''
GO
alter table HR_EMPLOYEE_TEMP_IMPORT add INFO_EXTRA_STRING_8 nvarchar(100) not null DEFAULT ''
GO
alter table HR_EMPLOYEE_TEMP_IMPORT add INFO_EXTRA_STRING_9 nvarchar(100) not null DEFAULT ''
GO


-- 04/12 --
GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('NUM_UP_SABBATICAL_MAX',
           '10',
           N'Số ngày phép thâm niêm tối đa được tăng thêm',
           'INT',
           'HRM')
GO
-- 06/12 --
GO
alter table HR_EMPLOYEE_TEMP_IMPORT add REASON_ID nvarchar(100) not null DEFAULT ''
GO




-- 11/12 --
GO
ALTER TABLE PR_EMPLOYEE_SALARY add NUMBER_SENT int not null default 0
GO


INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'HR_REQUEST_CLAIM_OT'--[MENU_ID]
           ,N'Yêu cầu tăng ca - Claim '--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'TimeReport'--[CONTROLLER]
           ,'Hr_Request_Claim_OT'--[ACTION]
           ,''--[MENU_ICON]
           ,'HRM_TR_TIMESHEET'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_TR'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,3 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'HR_REQUEST_CLAIM_OT', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO

-----------12/12/2018------------
GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'REQUEST_OT'--[MENU_ID]
           ,N'Yêu cầu tăng ca - Request'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'TimeReport'--[CONTROLLER]
           ,'OT_Request'--[ACTION]
           ,''--[MENU_ICON]
           ,'HRM_PR_TIMESHEET_PERSONAL'--[PARENT_ID]
           ,2 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_TR'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'PERSONAL'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,2 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'REQUEST_OT', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'REQUEST_OT', 
		 'PERSONAL', 
		 1,
		 1, 
		 '' ) 
GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'CLAIM_OT'--[MENU_ID]
           ,N'Yêu cầu tăng ca - Claim'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'TimeReport'--[CONTROLLER]
           ,'OT_Claim'--[ACTION]
           ,''--[MENU_ICON]
           ,'HRM_PR_TIMESHEET_PERSONAL'--[PARENT_ID]
           ,2 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_TR'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'PERSONAL'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,3 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'CLAIM_OT', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'CLAIM_OT', 
		 'PERSONAL', 
		 1,
		 1, 
		 '' ) 
GO

-- 20/12 --

GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('HR_IS_MATERNITY_LEAVE',
           'NTS',
           N'Chế độ nghỉ thai sản',
           'STRING',
           'HRM')
GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('EMPLOYEE_PROBATIONARY_CONTRACT',
           'HDTV',
           N'Loại HĐLĐ là thử việc',
           'STRING',
           'HRM')
GO

-- 27/12 --
GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('HR_PHEP_THAM_NIEN_CHIA_DEU',
           '0',
           N'Tính phép thâm niên chia đều 12 tháng tính từ khi đủ điều kiện tròn năm',
           'BOOLEAN',
           'HRM')
GO
-- 30/12--
GO
alter table HR_SHIFT_PLAN add PR_PARENT_ORGANIZATION_ID nvarchar(max) not null DEFAULT ''
GO

INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('HR_SHIFT_ID_DEFAULT',
           '',
           N'Ca làm việc mặc định khi chưa có dữ liệu phân ca',
           'STRING',
           'HRM')
GO
--09-01-2019------
GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('HRM_RATE_SHIFT_BHL',
           '10',
           N'Tỷ lệ % đơn giá ca',
           'INT',
           'HRM')
GO
alter table HR_SHIFT_V2 add IS_GET_EARLY_LATE_BY_FOUR_TIMECHECK smallint not null default 0
GO
--23-01-2019

GO
alter table DM_TR_PROJECT add IRC_DATE smalldatetime  NULL 
GO

GO
alter table DM_TR_PROJECT add CONTRACT_DATE smalldatetime  NULL 
GO

GO
alter table DM_TR_PROJECT add BILLALBE_FEE MONEY DEFAULT 0 NOT NULL 
GO

GO
alter table DM_TR_PROJECT add FEE_CARRIED_FW MONEY DEFAULT 0 NOT NULL 
GO
-- 11/01/2019 --

GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('HR_SEND_TIMECHEK_WHEN_NOCHECK',
           '0',
           N'Gửi thông báo cho danh sách nhân sự không chấm công khi tới giờ làm việc của ca',
           'BOOLEAN',
           'HRM')
GO
--- 1/3/2019-----
GO
ALTER TABLE DM_TR_TASK ADD GROUP_TASK_ID nvarchar(20) not null default ''
GO


CREATE TABLE [dbo].[DM_GROUP_TASK](
	[GROUP_TASK_ID] [nvarchar](20) NOT NULL,
	[GROUP_TASK_NAME] [nvarchar](100) NOT NULL,
	[ORGANIZATION_ID] [nvarchar](20) NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[ACTIVE] [smallint] NOT NULL,
 CONSTRAINT [PK_DM_GROUP_TASK] PRIMARY KEY CLUSTERED 
(
	[GROUP_TASK_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

GO
ALTER TABLE TIMESHEET ADD EMPLOYEE_LEVEL_ID nvarchar(20) not null default ''
GO
ALTER TABLE TIMESHEET ADD PR_ORGANIZATION_ID nvarchar(20) not null default ''
GO

--- 26--03--2019
GO
ALTER TABLE JOB_STATEMENT
ADD AMOUNT_POH_VND money not null default((0))
GO
INSERT INTO [dbo].[SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('PMS_EMAIL_PORT'
           ,N''
           ,N'PMS EMAIL PORT'
           ,N'STRING'
           ,N'HRM')

GO
INSERT INTO [dbo].[SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('PMS_EMAIL_HOST'
           ,N''
           ,N'PMS EMAIL HOST'
           ,N'STRING'
           ,N'HRM')

GO

--1-4-2019------
GO
ALTER TABLE DM_HR_LEAVE_REASON ADD LEAVE_REASON_NAME_EN nvarchar(70) not null default ''
GO
ALTER TABLE DM_HR_LEAVE_REASON ADD LEAVE_REASON_NAME_OTHER nvarchar(70) not null default ''
GO


--03-04-2019
go
alter table HR_EMPLOYEE_TEMP_IMPORT add LINE_MANAGER_ID NVARCHAR(20) DEFAULT '' NOT NULL 
GO


GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('HRM_ZALO_OA_ID',
           '0',
           N'Tên tài khoản đăng nhập zalo oa',
           'STRING',
           'COMMON')
GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('HRM_ZALO_OA_PASS',
           '',
           N'Mật khẩu đăng nhập zalo oa',
           'STRING',
           'COMMON')
GO

-- 10-04-2019----

GO
Update SYS_MENU_WEB
SET MENU_ICON = 'icon-lock fa-expeditedssl color-green'
where MENU_ID = 'HRM_ADMI_MANAGE'
GO

Update SYS_MENU_WEB
SET MENU_ICON = 'icon-compass fa-tachometer color-goldenrod'
where MENU_ID = 'HRM_DASHBOARD_MANAGE'
GO

Update SYS_MENU_WEB
SET MENU_ICON = 'icon-users fa-list color-orange'
where MENU_ID = 'HRM_HR'
GO

Update SYS_MENU_WEB
SET MENU_ICON = 'icon-notebook fa-book color-peru'
where MENU_ID = 'HRM_PMS'
GO
Update SYS_MENU_WEB
SET MENU_ICON = 'icon-layers fa-building-o color-black'
where MENU_ID = 'HRM_REPORT'
GO
Update SYS_MENU_WEB
SET MENU_ICON = 'icon-list fa-list-alt color-pink'
where MENU_ID = 'HRM_TIME_REPORT'
GO
Update SYS_MENU_WEB
SET MENU_ICON = 'icon-list fa-list-alt color-pink'
where MENU_ID = 'SYS_SYSTEM'
GO


---- 23-04-2019---------------------
GO
ALTER TABLE JOB_STATEMENT
ADD PRINT_USD_TEMPLATE smallint not null Default((0))
GO
ALTER TABLE JOB_STATEMENT_DETAIL
ADD LIST_ORDER int not null Default((0))
GO



GO
CREATE TABLE OFM_MEETING_ROOM_REGISTER -- ĐĂNG KÝ PHÒNG HỌP
(
	PR_KEY UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,
	DATE SMALLDATETIME NOT NULL DEFAULT GETDATE(), --THỜI GIAN HỌP
	TIME_IN SMALLDATETIME NOT NULL,
    TIME_OUT SMALLDATETIME NOT NULL,
	CONTENT NVARCHAR(500) NOT NULL DEFAULT '',
	PR_ORGANIZATION_ID NVARCHAR(100) NOT NULL DEFAULT '', -- ĐƠN VỊ ĐỀ XUẤT
	PARTICIPANT NVARCHAR(500) NOT NULL DEFAULT '', -- THÀNH PHẦN
	PERSONAL_NUMBER INT NOT NULL DEFAULT 0,
	ROOM_ID NVARCHAR(50) NOT NULL DEFAULT '', -- SỐ LƯỢNG NGƯỜI THAM DỰ
	COMMENTS NVARCHAR(500) NOT NULL DEFAULT '', -- YÊU CẦU KHÁC
	LEADER_ID NVARCHAR(20) NOT NULL DEFAULT '', -- LÃNH ĐẠO VĂN PHÒNG
	DIRECTOR_ID NVARCHAR(20) NOT NULL DEFAULT '', -- THỬ TRƯỞNG ĐƠN VỊ ĐĂNG KÝ
	STATUS NVARCHAR(50) NOT NULL DEFAULT '',
	USER_ID NVARCHAR(50) NOT NULL DEFAULT ''
)
GO
--Management--

GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'OFM_MEETING_ROOM_REGISTER'--[MENU_ID]
           ,N'Đăng ký phòng họp'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'OfficeManagement'--[CONTROLLER]
           ,'Ofm_Meeting_Room_Register'--[ACTION]
           ,''--[MENU_ICON]
           ,'MANAGE_OFM'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_OFM'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,1 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'OFM_MEETING_ROOM_REGISTER', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO

-------4/5/2019----
GO
CREATE TABLE [dbo].[DM_OFM_EQUIPMENT] -- DANH MỤC THIẾT BỊ
(
	[EQUIPMENT_ID] [nvarchar](20) primary key NOT NULL default '',
	[EQUIPMENT_NAME] [nvarchar](400) NOT NULL default '',
	[EQUIPMENT_TYPE_ID] [nvarchar](20) NOT NULL,
	[EQUIPMENT_QUALITY_ID] [nvarchar](20) NOT NULL default '',
	[ORGANIZATION_ID] [nvarchar](20) NOT NULL default '',
	[PR_ORGANIZATION_ID] [nvarchar](20) NOT NULL default '',
	[YEAR_PRODUCT] [int] NOT NULL default 0,
	[MANUFACTURER] [nvarchar](200) NOT NULL default '',
	[PRODUCT_NATIONALITY] [nvarchar](50) NOT NULL,
	[VALID_DATE] [smalldatetime] NOT NULL default null,
	[EQUIPMENT_SERIAL] [nvarchar](50) NOT NULL default '',
	[ACTIVE] [smallint] NOT NULL default 0,
	[USER_ID] [nvarchar](50) NOT NULL default ''
)
GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'DM_OFM_EQUIPMENT'--[MENU_ID]
           ,N'Danh mục trang, thiết bị'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'OfficeManagement'--[CONTROLLER]
           ,'Dm_Ofm_Equipment'--[ACTION]
           ,''--[MENU_ICON]
           ,'DM_OFM'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_OFM'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,3 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'DM_OFM_EQUIPMENT', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO
CREATE TABLE DM_OFM_EQUIPMENT_QUALITY --DM Chất lượng thiết bị
	(
	EQUIPMENT_QUALITY_ID NVARCHAR(20) PRIMARY KEY NOT NULL DEFAULT '',
	EQUIPMENT_QUALITY_NAME NVARCHAR(20) NOT NULL DEFAULT '',
	NOTE NVARCHAR(50) NOT NULL DEFAULT '',
	USER_ID NVARCHAR(20) NOT NULL DEFAULT '',
	ACTIVE SMALLINT NOT NULL DEFAULT 0 	
	)

GO

---09-05-2019-----
GO
INSERT INTO [dbo].[SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('BILLING_CHECK_DAY'
           ,N'20'
           ,N'Billing Check Day'
           ,N'INT'
           ,N'HRM')

GO

ALTER TABLE HR_EMPLOYEE_TEMP_IMPORT
ADD LINE_MANAGER_ID nvarchar(100) null Default ''
GO

GO
CREATE TABLE [dbo].[DM_OFM_EQUIPMENT] -- DANH MỤC THIẾT BỊ
(
	[EQUIPMENT_ID] [nvarchar](20) primary key NOT NULL default '',
	[EQUIPMENT_NAME] [nvarchar](400) NOT NULL default '',
	[EQUIPMENT_TYPE_ID] [nvarchar](20) NOT NULL,
	[EQUIPMENT_QUALITY_ID] [nvarchar](20) NOT NULL default '',
	[ORGANIZATION_ID] [nvarchar](20) NOT NULL default '',
	[PR_ORGANIZATION_ID] [nvarchar](20) NOT NULL default '',
	[YEAR_PRODUCT] [int] NOT NULL default 0,
	[MANUFACTURER] [nvarchar](200) NOT NULL default '',
	[PRODUCT_NATIONALITY] [nvarchar](50) NOT NULL,
	[VALID_DATE] [smalldatetime] NOT NULL default null,
	[EQUIPMENT_SERIAL] [nvarchar](50) NOT NULL default '',
	[ACTIVE] [smallint] NOT NULL default 0,
	[USER_ID] [nvarchar](50) NOT NULL default ''
)
GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'DM_OFM_EQUIPMENT'--[MENU_ID]
           ,N'Danh mục trang, thiết bị'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'OfficeManagement'--[CONTROLLER]
           ,'Dm_Ofm_Equipment'--[ACTION]
           ,''--[MENU_ICON]
           ,'DM_OFM'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_OFM'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,3 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'DM_OFM_EQUIPMENT', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO
CREATE TABLE DM_OFM_EQUIPMENT_QUALITY --DM Chất lượng thiết bị
	(
	EQUIPMENT_QUALITY_ID NVARCHAR(20) PRIMARY KEY NOT NULL DEFAULT '',
	EQUIPMENT_QUALITY_NAME NVARCHAR(20) NOT NULL DEFAULT '',
	NOTE NVARCHAR(50) NOT NULL DEFAULT '',
	USER_ID NVARCHAR(20) NOT NULL DEFAULT '',
	ACTIVE SMALLINT NOT NULL DEFAULT 0 	
	)
GO
CREATE TABLE OFM_EQUIPMENT_REPAIR_SUGGEST -- ĐỀ NGHỊ SỬA CHỮA THIẾT BỊ
(
	PR_KEY UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,
	DATE SMALLDATETIME NOT NULL DEFAULT GETDATE(),
	EQUIPMENT_ID NVARCHAR ( 20 ) NOT NULL DEFAULT '',
	EQUIPMENT_QUALITY_ID NVARCHAR ( 20 ) NOT NULL DEFAULT '',
	PR_ORGANIZATION_ID NVARCHAR ( 20 ) NOT NULL DEFAULT '',-- BỘ PHẬN ĐỀ XUẤT
	ORGANIZATION_ID NVARCHAR ( 20 ) NOT NULL DEFAULT '',-- ĐƠN VỊ ĐỀ XUẤT
	EQUIPMENT_TROUBLE NVARCHAR ( 1000 ) NOT NULL DEFAULT '',-- SỰ CỐ THIẾT BỊ
	EMPLOYEE_ID NVARCHAR ( 20 ) NOT NULL DEFAULT '',-- NGƯỜI SỬ DỤNG
	LEADER_ORGANIZATION_ID NVARCHAR ( 20 ) NOT NULL DEFAULT '',-- LÃNH ĐẠO ĐƠN VỊ
	DIRECTOR_ID NVARCHAR ( 20 ) NOT NULL DEFAULT '',-- THỦ TRƯỞNG ĐƠN VỊ ĐĂNG KÝ
	LEADER_OFFICE_ID NVARCHAR ( 20 ) NOT NULL DEFAULT '',-- LÃNH ĐẠO VĂN PHÒNG
	COMMENTS NVARCHAR ( 1000 ) NOT NULL DEFAULT '',
  USER_ID NVARCHAR ( 50 ) NOT NULL DEFAULT '' 
)

GO
ALTER TABLE OFM_EQUIPMENT_REPAIR_SUGGEST ADD ORGANIZATION_REPAIR NVARCHAR(500) NOT NULL DEFAULT ''	
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'OFM_EQUIPMENT_REPAIR_SUGGEST'--[MENU_ID]
           ,N'Đề nghị sửa chữa thiết bị'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'OfficeManagement'--[CONTROLLER]
           ,'Ofm_Equipment_Repair_Suggest'--[ACTION]
           ,''--[MENU_ICON]
           ,'MANAGE_OFM'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_OFM'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,3 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'OFM_EQUIPMENT_REPAIR_SUGGEST', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO
-----7/5/2019-----------
GO
CREATE TABLE OFM_EQUIPMENT_PROVIDE  -- ĐỀ NGHỊ TRANG CẤP THIẾT BỊ VẬT TƯ
(
	PR_KEY UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,
	TRAN_ID NVARCHAR(20) NOT NULL DEFAULT '',
	TRAN_NO NVARCHAR(20) NOT NULL DEFAULT '',
	TRAN_DATE SMALLDATETIME NOT NULL DEFAULT GETDATE(),
	TRAN_TYPE NVARCHAR(20) NOT NULL DEFAULT '',
	TRAN_NOTE NVARCHAR(100) NOT NULL DEFAULT '',
	EMPLOYEE_ID NVARCHAR ( 20 ) NOT NULL DEFAULT '',-- NGUƯỜI ĐỀ NGHỊ
	ORGANIZATION_ID NVARCHAR(20) NOT NULL DEFAULT'', -- ĐƠN VỊ ĐỀ NGHỊ
	PR_ORGANIZATION_ID NVARCHAR(20) NOT NULL DEFAULT '', -- BỘ PHẬN ĐỀ NGHỊ
	EQUIPMENT_REASON NVARCHAR (500) NOT NULL DEFAULT'', -- CĂN CỨ , LÝ DO ĐỀ NGHỊ
	COMMENTS NVARCHAR ( 500 ) NOT NULL DEFAULT '',
    USER_ID NVARCHAR ( 20 ) NOT NULL DEFAULT '' 
)
GO 
CREATE TABLE OFM_EQUIPMENT_PROVIDE_DETAIL
(
	PR_KEY UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,
	FR_KEY UNIQUEIDENTIFIER NOT NULL,
	EQUIPMENT_ID NVARCHAR(20) NOT NULL DEFAULT '',
	EQUIPMENT_REQUEST_ID NVARCHAR(20) NOT NULL DEFAULT'', --CẤP MỚI HOẶC SỬA CHỮA
	EQUIPMENT_NUMBER INT NOT NULL DEFAULT 0,
	COMMENTS NVARCHAR ( 500 ) NOT NULL DEFAULT ''
)
GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'OFM_EQUIPMENT_PROVIDE'--[MENU_ID]
           ,N'Đề nghị trang cấp thiết bị, vật tư'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'OfficeManagement'--[CONTROLLER]
           ,'Ofm_Equipment_Provide'--[ACTION]
           ,''--[MENU_ICON]
           ,'MANAGE_OFM'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_OFM'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,3 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'OFM_EQUIPMENT_PROVIDE', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO
--------9/5/2019---------
alter table OFM_MEETING_ROOM_REGISTER 
add 
EMPLOYEE_ID NVARCHAR(20) NOT NULL DEFAULT '',
DATE_MEETING SMALLDATETIME,
STATUS_COMMENTS nvarchar(400) not null default ''
GO
ALTER TABLE OFM_EQUIPMENT_REPAIR_SUGGEST 
ADD 
STATUS_ID NVARCHAR(20) NOT NULL DEFAULT '', -- TRẠNG THÁI 
STATUS_COMMENTS NVARCHAR(400) NOT NULL DEFAULT '',-- COMMENT FOR REJECT
DATE_EXECUTE SMALLDATETIME  -- NGÀY DỰ KIẾN THỰC HIỆN
GO
-----10/5/2019---------
------ SỬA BẢNG OFM_EQUIPMENT_REPAIR_SUGGEST VÀ OFM_EQUIPMENT_PROVIDE
GO
ALTER TABLE OFM_EQUIPMENT_REPAIR_SUGGEST 
ADD 
DATE_COMPLETE SMALLDATETIME --NGÀY DỰ KIẾN HOÀN THÀNH
GO
ALTER TABLE OFM_EQUIPMENT_PROVIDE 
ADD
STATUS_ID NVARCHAR(20) NOT NULL DEFAULT '',
STAUS_COMMENTS NVARCHAR(400) NOT NULL DEFAULT '',
DATE_EXECUTE SMALLDATETIME,
DATE_COMPLETE SMALLDATETIME
GO

-- BẢNG DANH MỤC LOẠI THIẾT BỊ

CREATE TABLE DM_OFM_EQUIPMENT_TYPE  -- BẢNG DANH MỤC LOẠI THIẾT BỊ
(
EQUIPMENT_TYPE_ID NVARCHAR(20) PRIMARY KEY NOT NULL DEFAULT '',
EQUIPMENT_TYPE_NAME NVARCHAR(100) NOT NULL DEFAULT '',
ACTIVE SMALLINT NOT NULL DEFAULT 1,
NOTE NVARCHAR(200) NOT NULL DEFAULT '',
USER_ID NVARCHAR(20) NOT NULL DEFAULT ''
)
GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'DM_OFM_EQUIPMENT_TYPE'--[MENU_ID]
           ,N'Danh mục loại thiết bị'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'OfficeManagement'--[CONTROLLER]
           ,'Dm_Ofm_Equipment_Type'--[ACTION]
           ,''--[MENU_ICON]
           ,'DM_OFM'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_OFM'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,4 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'DM_OFM_EQUIPMENT_TYPE', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'DM_OFM_EQUIPMENT_QUALITY'--[MENU_ID]
           ,N'Danh mục chất lượng thiết bị'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'OfficeManagement'--[CONTROLLER]
           ,'Dm_Ofm_Equipment_Quality'--[ACTION]
           ,''--[MENU_ICON]
           ,'DM_OFM'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_OFM'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,5 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'DM_OFM_EQUIPMENT_QUALITY', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO


GO
CREATE TABLE [dbo].[PR_EMPLOYEE_SALARY_BASE_NS](
	[PR_KEY] [uniqueidentifier] NOT NULL,
	[EMPLOYEE_ID] [nvarchar](20) NOT NULL,
	[EFFECTIVE_DATE] [smalldatetime] NOT NULL,
	[SHIFT_ID] [nvarchar](20) NOT NULL,
	[PR_ORGANIZATION_ID] [nvarchar](20) NOT NULL,
	[EXPIRATION_DATE] [smalldatetime] NOT NULL,
	[MODIFIED_DATE] [smalldatetime] NOT NULL,
	[S_A1] [money] NOT NULL,
	[S_A2] [money] NOT NULL,
	[S_A3] [money] NOT NULL,
	[S_A4] [money] NOT NULL,
	[S_A5] [money] NOT NULL,
	[S_A6] [money] NOT NULL,
	[S_A7] [money] NOT NULL,
	[S_A8] [money] NOT NULL,
	[S_A9] [money] NOT NULL,
	[S_B1] [money] NOT NULL,
	[S_B2] [money] NOT NULL,
	[S_B3] [money] NOT NULL,
	[S_B4] [money] NOT NULL,
	[S_B5] [money] NOT NULL,
	[S_B6] [money] NOT NULL,
	[S_B7] [money] NOT NULL,
	[S_B8] [money] NOT NULL,
	[S_B9] [money] NOT NULL,
	[COMMENTS] [nvarchar](300) NOT NULL,
	[EXTRA_STR_1] [nvarchar](300) NOT NULL,
	[EXTRA_STR_2] [nvarchar](300) NOT NULL,
	[EXTRA_STR_3] [nvarchar](300) NOT NULL,
	[EXTRA_STR_4] [nvarchar](300) NOT NULL,
	[EXTRA_STR_5] [nvarchar](300) NOT NULL,
	[EXTRA_STR_6] [nvarchar](300) NOT NULL,
	[EXTRA_STR_7] [nvarchar](300) NOT NULL,
	[EXTRA_STR_8] [nvarchar](300) NOT NULL,
	[EXTRA_STR_9] [nvarchar](300) NOT NULL,
	[EXTRA_DATE_1] [smalldatetime] NULL,
	[EXTRA_DATE_2] [smalldatetime] NULL,
	[GROUP_SALARY_ID] [nvarchar](20) NOT NULL,
	[LEVEL_SALARY] [nvarchar](20) NOT NULL,
	[REASON_ID] [nvarchar](20) NOT NULL,
	[IS_PAID_INS] [smallint] NOT NULL,
	[IS_SALARY_NET] [smallint] NOT NULL,
	[TIMEKEEPING_TYPE] [nvarchar](50) NOT NULL,
	[LOCAL_NETWORK] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_PR_EMPLOYEE_SALARY_BASE_NS] PRIMARY KEY CLUSTERED 
(
	[EMPLOYEE_ID] ASC,
	[EFFECTIVE_DATE] ASC,
	[SHIFT_ID] ASC,
	[PR_ORGANIZATION_ID] ASC,
	[TIMEKEEPING_TYPE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

----20-05----
GO
ALTER TABLE [dbo].[PR_EMPLOYEE_SALARY_BASE_NS] ADD  DEFAULT ('') FOR [EXTRA_STR_6]
GO

ALTER TABLE [dbo].[PR_EMPLOYEE_SALARY_BASE_NS] ADD  DEFAULT ('') FOR [EXTRA_STR_7]
GO

ALTER TABLE [dbo].[PR_EMPLOYEE_SALARY_BASE_NS] ADD  DEFAULT ('') FOR [EXTRA_STR_8]
GO

ALTER TABLE [dbo].[PR_EMPLOYEE_SALARY_BASE_NS] ADD  DEFAULT ('') FOR [EXTRA_STR_9]
GO

ALTER TABLE [dbo].[PR_EMPLOYEE_SALARY_BASE_NS] ADD  DEFAULT ('') FOR [GROUP_SALARY_ID]
GO

ALTER TABLE [dbo].[PR_EMPLOYEE_SALARY_BASE_NS] ADD  DEFAULT ('') FOR [LEVEL_SALARY]
GO

ALTER TABLE [dbo].[PR_EMPLOYEE_SALARY_BASE_NS] ADD  DEFAULT ('') FOR [REASON_ID]
GO

ALTER TABLE [dbo].[PR_EMPLOYEE_SALARY_BASE_NS] ADD  DEFAULT ((0)) FOR [IS_PAID_INS]
GO

ALTER TABLE [dbo].[PR_EMPLOYEE_SALARY_BASE_NS] ADD  DEFAULT ((0)) FOR [IS_SALARY_NET]
GO

ALTER TABLE [dbo].[PR_EMPLOYEE_SALARY_BASE_NS] ADD  DEFAULT ('') FOR [TIMEKEEPING_TYPE]
GO

ALTER TABLE [dbo].[PR_EMPLOYEE_SALARY_BASE_NS] ADD  DEFAULT ('') FOR [LOCAL_NETWORK]
GO

INSERT INTO SYS_MENU
           ([MENU_ID]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_ICON]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
           ('PR_EMPLOYEE_SALARY_BASE_NS','HRM','HRM_SA','FUNC','HRM_SA_1','',85,'PR_EMPLOYEE_SALARY_BASE_NS',12,1)
		   
GO
CREATE TABLE [dbo].[PR_EMPLOYEE_SALARY_EVA_NS](
	[PR_KEY] [uniqueidentifier] NOT NULL,
	[EMPLOYEE_ID] [nvarchar](20) NOT NULL,
	[SHIFT_ID] [nvarchar](20) NOT NULL,
	[PR_ORGANIZATION_ID] [nvarchar](20) NOT NULL DEFAULT (''),
	[DAY_START] [smalldatetime] NOT NULL,
	[DAY_END] [smalldatetime] NOT NULL,
	[E_A1] [money] NOT NULL,
	[E_A2] [money] NOT NULL,
	[E_A3] [money] NOT NULL,
	[E_A4] [money] NOT NULL,
	[E_A5] [money] NOT NULL,
	[E_A6] [money] NOT NULL,
	[E_A7] [money] NOT NULL,
	[E_A8] [money] NOT NULL,
	[E_A9] [money] NOT NULL,
	[E_B1] [money] NOT NULL,
	[E_B2] [money] NOT NULL,
	[E_B3] [money] NOT NULL,
	[E_B4] [money] NOT NULL,
	[E_B5] [money] NOT NULL,
	[E_B6] [money] NOT NULL,
	[E_B7] [money] NOT NULL,
	[E_B8] [money] NOT NULL,
	[E_B9] [money] NOT NULL,
	[E_C1] [money] NOT NULL DEFAULT ((0)),
	[E_C2] [money] NOT NULL DEFAULT ((0)),
	[E_C3] [money] NOT NULL DEFAULT ((0)),
	[E_C4] [money] NOT NULL DEFAULT ((0)),
	[E_C5] [money] NOT NULL DEFAULT ((0)),
	[E_C6] [money] NOT NULL DEFAULT ((0)),
	[E_C7] [money] NOT NULL DEFAULT ((0)),
	[E_C8] [money] NOT NULL DEFAULT ((0)),
	[E_C9] [money] NOT NULL DEFAULT ((0)),
	[EX_A1] [money] NOT NULL DEFAULT ((0)),
	[EX_A2] [money] NOT NULL DEFAULT ((0)),
	[EX_A3] [money] NOT NULL DEFAULT ((0)),
	[EX_A4] [money] NOT NULL DEFAULT ((0)),
	[EX_A5] [money] NOT NULL DEFAULT ((0)),
	[EX_A6] [money] NOT NULL DEFAULT ((0)),
	[EX_A7] [money] NOT NULL DEFAULT ((0)),
	[EX_A8] [money] NOT NULL DEFAULT ((0)),
	[EX_A9] [money] NOT NULL DEFAULT ((0)),
	[EX_B1] [money] NOT NULL DEFAULT ((0)),
	[EX_B2] [money] NOT NULL DEFAULT ((0)),
	[EX_B3] [money] NOT NULL DEFAULT ((0)),
	[EX_B4] [money] NOT NULL DEFAULT ((0)),
	[EX_B5] [money] NOT NULL DEFAULT ((0)),
	[EX_B6] [money] NOT NULL DEFAULT ((0)),
	[EX_B7] [money] NOT NULL DEFAULT ((0)),
	[EX_B8] [money] NOT NULL DEFAULT ((0)),
	[EX_B9] [money] NOT NULL DEFAULT ((0)),
	[EX_C1] [money] NOT NULL DEFAULT ((0)),
	[EX_C2] [money] NOT NULL DEFAULT ((0)),
	[EX_C3] [money] NOT NULL DEFAULT ((0)),
	[EX_C4] [money] NOT NULL DEFAULT ((0)),
	[EX_C5] [money] NOT NULL DEFAULT ((0)),
	[EX_C6] [money] NOT NULL DEFAULT ((0)),
	[EX_C7] [money] NOT NULL DEFAULT ((0)),
	[EX_C8] [money] NOT NULL DEFAULT ((0)),
	[EX_C9] [money] NOT NULL DEFAULT ((0)),
	[EX_D1] [money] NOT NULL DEFAULT ((0)),
	[EX_D2] [money] NOT NULL DEFAULT ((0)),
	[EX_D3] [money] NOT NULL DEFAULT ((0)),
	[EX_D4] [money] NOT NULL DEFAULT ((0)),
	[EX_D5] [money] NOT NULL DEFAULT ((0)),
	[EX_D6] [money] NOT NULL DEFAULT ((0)),
	[EX_D7] [money] NOT NULL DEFAULT ((0)),
	[EX_D8] [money] NOT NULL DEFAULT ((0)),
	[EX_D9] [money] NOT NULL DEFAULT ((0)),
	[EX_E1] [money] NOT NULL DEFAULT ((0)),
	[EX_E2] [money] NOT NULL DEFAULT ((0)),
	[EX_E3] [money] NOT NULL DEFAULT ((0)),
	[EX_E4] [money] NOT NULL DEFAULT ((0)),
	[EX_E5] [money] NOT NULL DEFAULT ((0)),
	[EX_E6] [money] NOT NULL DEFAULT ((0)),
	[EX_E7] [money] NOT NULL DEFAULT ((0)),
	[EX_E8] [money] NOT NULL DEFAULT ((0)),
	[EX_E9] [money] NOT NULL DEFAULT ((0)),
	[BONUS_TYPE_ID] [nvarchar](20) NOT NULL,
	[BONUS_TYPE_ID_REG] [nvarchar](20) NOT NULL,
	[COMMENTS] [nvarchar](200) NOT NULL,
	[COMMENTS_1] [nvarchar](100) NOT NULL,
	[COMMENTS_2] [nvarchar](100) NOT NULL,
	[COMMENTS_3] [nvarchar](100) NOT NULL,
	[COMMENTS_4] [nvarchar](100) NOT NULL,
	[COMMENTS_5] [nvarchar](100) NOT NULL,
	[MODIFIED_DATE] [smalldatetime] NOT NULL,
	[DAYS_NOT_GO_WORK] [money] NOT NULL DEFAULT ((0)),
	[DAYS_OFF_WORK] [money] NOT NULL DEFAULT ((0)),
	[POSITION_ID] [nvarchar](20) NOT NULL DEFAULT (''),
	[PR_PARENT_ORGANIZATION_ID] [nvarchar](1000) NOT NULL DEFAULT (''),
	[EMPLOYEE_LEVEL_ID] [nvarchar](20) NOT NULL DEFAULT (''),
	[AUTO_SA_GROSS] [money] NOT NULL DEFAULT ((0)),
	[AUTO_SA_ACTUALLY_RECEIVE] [money] NOT NULL DEFAULT ((0)),
	[AUTO_SA_TAXABLE_INCOME] [money] NOT NULL DEFAULT ((0)),
	[AUTO_SA_PAY_TAX] [money] NOT NULL DEFAULT ((0)),
	[LOCAL_NETWORK] [nvarchar](100) NOT NULL DEFAULT (''),
 CONSTRAINT [PK_PR_EMPLOYEE_SALARY_EVA_NS] PRIMARY KEY CLUSTERED 
(
	[DAY_START] DESC,
	[DAY_END] DESC,
	[EMPLOYEE_ID] ASC,
	[SHIFT_ID] ASC,
	[PR_ORGANIZATION_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO
INSERT INTO SYS_MENU
           ([MENU_ID]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_ICON]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
           ('PR_EMPLOYEE_SALARY_EVA_NS','HRM','HRM_SA','FUNC','HRM_SA_1','',85,'PR_EMPLOYEE_SALARY_EVA_NS',12,1)
		   
GO
CREATE TABLE [dbo].[PR_EMPLOYEE_SALARY_NS](
	[PR_KEY] [uniqueidentifier] NOT NULL,
	[EMPLOYEE_ID] [nvarchar](20) NOT NULL,
	[EMPLOYEE_NAME] [nvarchar](150) NOT NULL,
	[SHIFT_ID] [nvarchar](20) NOT NULL,
	[PR_ORGANIZATION_ID] [nvarchar](20) NOT NULL,
	[PR_PARENT_ORGANIZATION_ID] [nvarchar](max) NOT NULL,
	[POSITION_ID] [nvarchar](20) NOT NULL,
	[EMPLOYEE_LEVEL_ID] [nvarchar](20) NOT NULL,
	[DAY_START] [smalldatetime] NOT NULL,
	[DAY_END] [smalldatetime] NOT NULL,
	[TIMEKEEPING_TYPE] [nvarchar](20) NOT NULL,
	[DATE_MODIFIED] [smalldatetime] NOT NULL,
	[AUTO_SA_GROSS] [money] NOT NULL,
	[AUTO_SA_NET] [money] NOT NULL,
	[AUTO_SA_TOTAL_REDUCE] [money] NOT NULL,
	[AUTO_SA_ACTUALLY_RECEIVE] [money] NOT NULL,
	[AUTO_SA_TAXABLE_INCOME] [money] NOT NULL,
	[AUTO_SA_PAY_TAX] [money] NOT NULL,
	[AUTO_SA_INSURANCE] [money] NOT NULL,
	[AUTO_REDUCE_SI_PE] [money] NOT NULL,
	[AUTO_REDUCE_HI_PE] [money] NOT NULL,
	[AUTO_REDUCE_UI_PE] [money] NOT NULL,
	[AUTO_REDUCE_BH_PE] [money] NOT NULL,
	[AUTO_REDUCE_SI_CO] [money] NOT NULL,
	[AUTO_REDUCE_HI_CO] [money] NOT NULL,
	[AUTO_REDUCE_UI_CO] [money] NOT NULL,
	[AUTO_REDUCE_BH_CO] [money] NOT NULL,
	[AUTO_GTNPT] [money] NOT NULL ,
	[AUTO_REDUCE_FEE_PE] [money] NOT NULL,
	[AUTO_REDUCE_FEE_CO] [money] NOT NULL,
	[S_A1] [money] NOT NULL,
	[S_A2] [money] NOT NULL,
	[S_A3] [money] NOT NULL,
	[S_A4] [money] NOT NULL,
	[S_A5] [money] NOT NULL,
	[S_A6] [money] NOT NULL,
	[S_A7] [money] NOT NULL,
	[S_A8] [money] NOT NULL,
	[S_A9] [money] NOT NULL,
	[S_B1] [money] NOT NULL,
	[S_B2] [money] NOT NULL,
	[S_B3] [money] NOT NULL,
	[S_B4] [money] NOT NULL,
	[S_B5] [money] NOT NULL,
	[S_B6] [money] NOT NULL,
	[S_B7] [money] NOT NULL,
	[S_B8] [money] NOT NULL,
	[S_B9] [money] NOT NULL,
	[COPY_A1] [money] NOT NULL,
	[COPY_A2] [money] NOT NULL,
	[COPY_A3] [money] NOT NULL,
	[COPY_A4] [money] NOT NULL,
	[COPY_A5] [money] NOT NULL,
	[COPY_A6] [money] NOT NULL,
	[COPY_A7] [money] NOT NULL,
	[COPY_A8] [money] NOT NULL,
	[COPY_A9] [money] NOT NULL,
	[COPY_B1] [money] NOT NULL,
	[COPY_B2] [money] NOT NULL,
	[COPY_B3] [money] NOT NULL,
	[COPY_B4] [money] NOT NULL,
	[COPY_B5] [money] NOT NULL,
	[COPY_B6] [money] NOT NULL,
	[COPY_B7] [money] NOT NULL,
	[COPY_B8] [money] NOT NULL,
	[COPY_B9] [money] NOT NULL,
	[COPY_C1] [money] NOT NULL,
	[COPY_C2] [money] NOT NULL,
	[COPY_C3] [money] NOT NULL,
	[COPY_C4] [money] NOT NULL,
	[COPY_C5] [money] NOT NULL,
	[COPY_C6] [money] NOT NULL,
	[COPY_C7] [money] NOT NULL,
	[COPY_C8] [money] NOT NULL,
	[COPY_C9] [money] NOT NULL,
	[EX_A1] [money] NOT NULL,
	[EX_A2] [money] NOT NULL,
	[EX_A3] [money] NOT NULL,
	[EX_A4] [money] NOT NULL,
	[EX_A5] [money] NOT NULL,
	[EX_A6] [money] NOT NULL,
	[EX_A7] [money] NOT NULL,
	[EX_A8] [money] NOT NULL,
	[EX_A9] [money] NOT NULL,
	[EX_B1] [money] NOT NULL,
	[EX_B2] [money] NOT NULL,
	[EX_B3] [money] NOT NULL,
	[EX_B4] [money] NOT NULL,
	[EX_B5] [money] NOT NULL,
	[EX_B6] [money] NOT NULL,
	[EX_B7] [money] NOT NULL,
	[EX_B8] [money] NOT NULL,
	[EX_B9] [money] NOT NULL,
	[EX_C1] [money] NOT NULL,
	[EX_C2] [money] NOT NULL,
	[EX_C3] [money] NOT NULL,
	[EX_C4] [money] NOT NULL,
	[EX_C5] [money] NOT NULL,
	[EX_C6] [money] NOT NULL,
	[EX_C7] [money] NOT NULL,
	[EX_C8] [money] NOT NULL,
	[EX_C9] [money] NOT NULL,
	[EX_D1] [money] NOT NULL DEFAULT ((0)),
	[EX_D2] [money] NOT NULL DEFAULT ((0)),
	[EX_D3] [money] NOT NULL DEFAULT ((0)),
	[EX_D4] [money] NOT NULL DEFAULT ((0)),
	[EX_D5] [money] NOT NULL DEFAULT ((0)),
	[EX_D6] [money] NOT NULL DEFAULT ((0)),
	[EX_D7] [money] NOT NULL DEFAULT ((0)),
	[EX_D8] [money] NOT NULL DEFAULT ((0)),
	[EX_D9] [money] NOT NULL DEFAULT ((0)),
	[EX_E1] [money] NOT NULL DEFAULT ((0)),
	[EX_E2] [money] NOT NULL DEFAULT ((0)),
	[EX_E3] [money] NOT NULL DEFAULT ((0)),
	[EX_E4] [money] NOT NULL DEFAULT ((0)),
	[EX_E5] [money] NOT NULL DEFAULT ((0)),
	[EX_E6] [money] NOT NULL DEFAULT ((0)),
	[EX_E7] [money] NOT NULL DEFAULT ((0)),
	[EX_E8] [money] NOT NULL DEFAULT ((0)),
	[EX_E9] [money] NOT NULL DEFAULT ((0)),
	[DAY_NT] [money] NOT NULL,
	[DAY_NT_D] [money] NOT NULL,
	[DAY_NN] [money] NOT NULL,
	[DAY_NN_D] [money] NOT NULL,
	[DAY_NL] [money] NOT NULL,
	[DAY_NL_D] [money] NOT NULL,
	[DAY_NL_KL] [money] NOT NULL,
	[WT_NT] [money] NOT NULL,
	[WT_NT_D] [money] NOT NULL,
	[OT_NT] [money] NOT NULL,
	[LEAVE_NP] [money] NOT NULL,
	[LEAVE_A1] [money] NOT NULL,
	[LEAVE_A2] [money] NOT NULL,
	[LEAVE_A3] [money] NOT NULL,
	[LEAVE_A4] [money] NOT NULL,
	[LEAVE_A5] [money] NOT NULL,
	[LEAVE_A6] [money] NOT NULL,
	[LEAVE_A7] [money] NOT NULL,
	[LEAVE_A8] [money] NOT NULL,
	[LEAVE_A9] [money] NOT NULL,
	[FAULT_LATE_TIME_IN_COUNT] [money] NOT NULL,
	[FAULT_EARLY_TIME_OUT_COUNT] [money] NOT NULL,
	[FAULT_FORGET_CHECK_COUNT] [money] NOT NULL,
	[FAULT_NOT_CHECK_COUNT] [money] NOT NULL,
	[FAULT_LATE_TIME_IN_SUM] [money] NOT NULL,
	[FAULT_EARLY_TIME_OUT_SUM] [money] NOT NULL,
	[FAULT_COUNT_NOT_EARLY_IN] [money] NOT NULL,
	[IS_PAID_INS] [smallint] NOT NULL,
	[OVER_TIME_1] [money] NOT NULL,
	[OVER_TIME_2] [money] NOT NULL,
	[OVER_TIME_3] [money] NOT NULL,
	[OVER_TIME_4] [money] NOT NULL,
	[OVER_TIME_5] [money] NOT NULL,
	[OVER_TIME_6] [money] NOT NULL,
	[OVER_TIME_7] [money] NOT NULL,
	[OVER_TIME_8] [money] NOT NULL,
	[OVER_TIME_9] [money] NOT NULL,
	[OVER_TIME_10] [money] NOT NULL,
	[OVER_TIME_11] [money] NOT NULL,
	[OVER_TIME_12] [money] NOT NULL,
	[OVER_TIME_13] [money] NOT NULL,
	[OVER_TIME_14] [money] NOT NULL,
	[NCC] [money] NOT NULL ,
	[MONTHS_SENIORITY] [money] NOT NULL ,
	[IS_HDCT] [smallint] NOT NULL,
	[NPT] [int] NOT NULL ,
	[GTBT] [money] NOT NULL,
	[COMMENTS] [nvarchar](200) NOT NULL,
	[COMMENTS_1] [nvarchar](200) NOT NULL,
	[COMMENTS_2] [nvarchar](200) NOT NULL,
	[COMMENTS_3] [nvarchar](200) NOT NULL,
	[IS_SALARY_NET] [smallint] NOT NULL DEFAULT ((0)),
	[LOCAL_NETWORK] [nvarchar](100) NOT NULL DEFAULT (''),
	[FAULT_DAY_NOT_CHECK_COUNT] [money] NOT NULL DEFAULT ((0)),
	[NUMBER_SENT] [int] NOT NULL DEFAULT ((0)),
 CONSTRAINT [PK_PR_EMPLOYEE_SALARY_NS] PRIMARY KEY CLUSTERED 
(
	[EMPLOYEE_ID] ASC,
	[SHIFT_ID] ASC,
	[PR_ORGANIZATION_ID] ASC,
	[DAY_START] ASC,
	[DAY_END] ASC,
	[TIMEKEEPING_TYPE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
INSERT INTO SYS_MENU
           ([MENU_ID]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_ICON]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
           ('PR_EMPLOYEE_SALARY_NS','HRM','HRM_SA','FUNC','HRM_SA_1','',85,'PR_EMPLOYEE_SALARY_NS',12,1)
		   
GO





GO
CREATE TABLE [dbo].[DM_OFM_EQUIPMENT] -- DANH MỤC THIẾT BỊ
(
	[EQUIPMENT_ID] [nvarchar](20) primary key NOT NULL default '',
	[EQUIPMENT_NAME] [nvarchar](400) NOT NULL default '',
	[EQUIPMENT_TYPE_ID] [nvarchar](20) NOT NULL,
	[EQUIPMENT_QUALITY_ID] [nvarchar](20) NOT NULL default '',
	[ORGANIZATION_ID] [nvarchar](20) NOT NULL default '',
	[PR_ORGANIZATION_ID] [nvarchar](20) NOT NULL default '',
	[YEAR_PRODUCT] [int] NOT NULL default 0,
	[MANUFACTURER] [nvarchar](200) NOT NULL default '',
	[PRODUCT_NATIONALITY] [nvarchar](50) NOT NULL,
	[VALID_DATE] [smalldatetime] NOT NULL default null,
	[EQUIPMENT_SERIAL] [nvarchar](50) NOT NULL default '',
	[ACTIVE] [smallint] NOT NULL default 0,
	[USER_ID] [nvarchar](50) NOT NULL default ''
)
GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'DM_OFM_EQUIPMENT'--[MENU_ID]
           ,N'Danh mục trang, thiết bị'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'OfficeManagement'--[CONTROLLER]
           ,'Dm_Ofm_Equipment'--[ACTION]
           ,''--[MENU_ICON]
           ,'DM_OFM'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_OFM'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,3 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'DM_OFM_EQUIPMENT', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO
CREATE TABLE DM_OFM_EQUIPMENT_QUALITY --DM Chất lượng thiết bị
	(
	EQUIPMENT_QUALITY_ID NVARCHAR(20) PRIMARY KEY NOT NULL DEFAULT '',
	EQUIPMENT_QUALITY_NAME NVARCHAR(20) NOT NULL DEFAULT '',
	NOTE NVARCHAR(50) NOT NULL DEFAULT '',
	USER_ID NVARCHAR(20) NOT NULL DEFAULT '',
	ACTIVE SMALLINT NOT NULL DEFAULT 0 	
	)
GO
CREATE TABLE OFM_EQUIPMENT_REPAIR_SUGGEST -- ĐỀ NGHỊ SỬA CHỮA THIẾT BỊ
(
	PR_KEY UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,
	DATE SMALLDATETIME NOT NULL DEFAULT GETDATE(),
	EQUIPMENT_ID NVARCHAR ( 20 ) NOT NULL DEFAULT '',
	EQUIPMENT_QUALITY_ID NVARCHAR ( 20 ) NOT NULL DEFAULT '',
	PR_ORGANIZATION_ID NVARCHAR ( 20 ) NOT NULL DEFAULT '',-- BỘ PHẬN ĐỀ XUẤT
	ORGANIZATION_ID NVARCHAR ( 20 ) NOT NULL DEFAULT '',-- ĐƠN VỊ ĐỀ XUẤT
	EQUIPMENT_TROUBLE NVARCHAR ( 1000 ) NOT NULL DEFAULT '',-- SỰ CỐ THIẾT BỊ
	EMPLOYEE_ID NVARCHAR ( 20 ) NOT NULL DEFAULT '',-- NGƯỜI SỬ DỤNG
	LEADER_ORGANIZATION_ID NVARCHAR ( 20 ) NOT NULL DEFAULT '',-- LÃNH ĐẠO ĐƠN VỊ
	DIRECTOR_ID NVARCHAR ( 20 ) NOT NULL DEFAULT '',-- THỦ TRƯỞNG ĐƠN VỊ ĐĂNG KÝ
	LEADER_OFFICE_ID NVARCHAR ( 20 ) NOT NULL DEFAULT '',-- LÃNH ĐẠO VĂN PHÒNG
	COMMENTS NVARCHAR ( 1000 ) NOT NULL DEFAULT '',
  USER_ID NVARCHAR ( 50 ) NOT NULL DEFAULT '' 
)
ALTER TABLE OFM_EQUIPMENT_REPAIR_SUGGEST ADD ORGANIZATION_REPAIR NVARCHAR(500) NOT NULL DEFAULT ''	
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'OFM_EQUIPMENT_REPAIR_SUGGEST'--[MENU_ID]
           ,N'Đề nghị sửa chữa thiết bị'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'OfficeManagement'--[CONTROLLER]
           ,'Ofm_Equipment_Repair_Suggest'--[ACTION]
           ,''--[MENU_ICON]
           ,'MANAGE_OFM'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_OFM'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,3 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'OFM_EQUIPMENT_REPAIR_SUGGEST', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO
-----7/5/2019-----------

GO
CREATE TABLE OFM_EQUIPMENT_PROVIDE  -- ĐỀ NGHỊ TRANG CẤP THIẾT BỊ VẬT TƯ
(
	PR_KEY UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,
	TRAN_ID NVARCHAR(20) NOT NULL DEFAULT '',
	TRAN_NO NVARCHAR(20) NOT NULL DEFAULT '',
	TRAN_DATE SMALLDATETIME NOT NULL DEFAULT GETDATE(),
	TRAN_TYPE NVARCHAR(20) NOT NULL DEFAULT '',
	TRAN_NOTE NVARCHAR(100) NOT NULL DEFAULT '',
	EMPLOYEE_ID NVARCHAR ( 20 ) NOT NULL DEFAULT '',-- NGUƯỜI ĐỀ NGHỊ
	ORGANIZATION_ID NVARCHAR(20) NOT NULL DEFAULT'', -- ĐƠN VỊ ĐỀ NGHỊ
	PR_ORGANIZATION_ID NVARCHAR(20) NOT NULL DEFAULT '', -- BỘ PHẬN ĐỀ NGHỊ
	EQUIPMENT_REASON NVARCHAR (500) NOT NULL DEFAULT'', -- CĂN CỨ , LÝ DO ĐỀ NGHỊ
	COMMENTS NVARCHAR ( 500 ) NOT NULL DEFAULT '',
    USER_ID NVARCHAR ( 20 ) NOT NULL DEFAULT '' 
)
GO 
CREATE TABLE OFM_EQUIPMENT_PROVIDE_DETAIL
(
	PR_KEY UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,
	FR_KEY UNIQUEIDENTIFIER NOT NULL,
	EQUIPMENT_ID NVARCHAR(20) NOT NULL DEFAULT '',
	EQUIPMENT_REQUEST_ID NVARCHAR(20) NOT NULL DEFAULT'', --CẤP MỚI HOẶC SỬA CHỮA
	EQUIPMENT_NUMBER INT NOT NULL DEFAULT 0,
	COMMENTS NVARCHAR ( 500 ) NOT NULL DEFAULT ''
)
GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'OFM_EQUIPMENT_PROVIDE'--[MENU_ID]
           ,N'Đề nghị trang cấp thiết bị, vật tư'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'OfficeManagement'--[CONTROLLER]
           ,'Ofm_Equipment_Provide'--[ACTION]
           ,''--[MENU_ICON]
           ,'MANAGE_OFM'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_OFM'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,3 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'OFM_EQUIPMENT_PROVIDE', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO
--------9/5/2019---------
GO
alter table OFM_MEETING_ROOM_REGISTER 
add 
EMPLOYEE_ID NVARCHAR(20) NOT NULL DEFAULT '',
DATE_MEETING SMALLDATETIME NOT NULL DEFAULT GETDATE(),
STATUS_COMMENTS nvarchar(400) not null default ''
GO
ALTER TABLE OFM_EQUIPMENT_REPAIR_SUGGEST 
ADD 
STATUS_ID NVARCHAR(20) NOT NULL DEFAULT '', -- TRẠNG THÁI 
STATUS_COMMENTS NVARCHAR(400) NOT NULL DEFAULT '',-- COMMENT FOR REJECT
DATE_EXECUTE SMALLDATETIME NOT NULL DEFAULT GETDATE()  -- NGÀY DỰ KIẾN THỰC HIỆN
-----10/5/2019---------
------ SỬA BẢNG OFM_EQUIPMENT_REPAIR_SUGGEST VÀ OFM_EQUIPMENT_PROVIDE
GO
ALTER TABLE OFM_EQUIPMENT_REPAIR_SUGGEST 
ADD 
DATE_COMPLETE SMALLDATETIME NOT NULL DEFAULT GETDATE() --NGÀY DỰ KIẾN HOÀN THÀNH
GO
ALTER TABLE OFM_EQUIPMENT_PROVIDE 
ADD
STATUS_ID NVARCHAR(20) NOT NULL DEFAULT '',
STAUS_COMMENTS NVARCHAR(400) NOT NULL DEFAULT '',
DATE_EXECUTE SMALLDATETIME NOT NULL DEFAULT GETDATE(),
DATE_COMPLETE SMALLDATETIME NOT NULL DEFAULT GETDATE()
GO

-- BẢNG DANH MỤC LOẠI THIẾT BỊ
GO
CREATE TABLE DM_OFM_EQUIPMENT_TYPE  -- BẢNG DANH MỤC LOẠI THIẾT BỊ
(
EQUIPMENT_TYPE_ID NVARCHAR(20) PRIMARY KEY NOT NULL DEFAULT '',
EQUIPMENT_TYPE_NAME NVARCHAR(100) NOT NULL DEFAULT '',
ACTIVE SMALLINT NOT NULL DEFAULT 1,
NOTE NVARCHAR(200) NOT NULL DEFAULT '',
USER_ID NVARCHAR(20) NOT NULL DEFAULT ''
)
GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'DM_OFM_EQUIPMENT_TYPE'--[MENU_ID]
           ,N'Danh mục loại thiết bị'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'OfficeManagement'--[CONTROLLER]
           ,'Dm_Ofm_Equipment_Type'--[ACTION]
           ,''--[MENU_ICON]
           ,'DM_OFM'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_OFM'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,4 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'DM_OFM_EQUIPMENT_TYPE', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'DM_OFM_EQUIPMENT_QUALITY'--[MENU_ID]
           ,N'Danh mục chất lượng thiết bị'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'OfficeManagement'--[CONTROLLER]
           ,'Dm_Ofm_Equipment_Quality'--[ACTION]
           ,''--[MENU_ICON]
           ,'DM_OFM'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_OFM'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,5 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'DM_OFM_EQUIPMENT_QUALITY', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO

---26/06/2019

GO
alter table PR_SA_LOCK add DAY_START_TIMESHEET smalldatetime DEFAULT '2019-06-01' NOT NULL 
GO
alter table PR_SA_LOCK add DAY_END_TIMESHEET smalldatetime DEFAULT '2019-06-30' NOT NULL 
GO
--28/06/2019
GO
CREATE TABLE [dbo].[DM_TR_PROJECT_STATUS](
	[PROJECT_STATUS_ID] [nvarchar](20) NOT NULL,
	[PROJECT_STATUS_NAME] [nvarchar](60) NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[ACTIVE] [smallint] NOT NULL,
 CONSTRAINT [PK_DM_TR_PROJECT_STATUS] PRIMARY KEY CLUSTERED 
(
	[PROJECT_STATUS_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--02/07/2019
GO
CREATE TABLE [dbo].[DM_TR_PROJECT_FEE](
	[PR_KEY] [uniqueidentifier] NOT NULL,
	[PROJECT_ID] [nvarchar](20) NOT NULL,
	[DAY_START] [smalldatetime] NOT NULL,
	[DAY_END] [smalldatetime] NOT NULL,
	[YEAR] [smallint] NOT NULL,
	[CONTRACT_VALUE] [money] NOT NULL,
	[BUBGET_FEE] [money] NOT NULL,
	[BUBGET_FEE_FW] [money] NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[DATE_MODIFIED] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_DM_TR_PROJECT_FEE] PRIMARY KEY CLUSTERED 
(
	[PR_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


--07-07--2019----
GO

INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('APIURL_STDERP_DM_PR_DETAIL',
           '',
           N'API chuyển dữ liệu sang danh mục pr_detail',
           'STRING',
           'HRM')
GO

INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('APIUSERID_STDERP_DM_PR_DETAIL',
           '',
           N'User ID cho API chuyển dữ liệu sang danh mục pr_detail',
           'STRING',
           'HRM')
GO

INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('APIPASS_STDERP_DM_PR_DETAIL',
           '',
           N'Pass cho API chuyển dữ liệu sang danh mục pr_detail',
           'STRING',
           'HRM')
GO



-- 12/07 --
GO
alter table PR_PIT_REDUCTION add PIT_NATIONALITY_ID nvarchar(20) default '' NOT NULL 
GO

-- 18/07 --
GO
alter table HR_TIME_DEVICE add SYNC_TYPE nvarchar(20) not null default ''
GO
alter table HR_EMPLOYEE_OTHER add IS_CHECK_TIMEWORK smallint not null default 1
GO


-- 20/07 --
GO
alter table HR_TIME_CHECKINOUT_MAP add CHECK_STATUS nvarchar(20) default '' NOT NULL 
GO
GO
alter table HR_TIME_CHECKINOUT_MAP add APPROVED_EMPLOYEE_ID nvarchar(20) default '' NOT NULL 
GO

GO
alter table HR_TIME_CHECKINOUT_MAP add CHECK_COMMENT nvarchar(700) default '' NOT NULL 
GO

--22/07---2019
GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('HRM_DEFAULT_STATUS_WORK_OFF',
           '',
           N'Mặc định giá trị trạng thái nghỉ việc',
           'STRING',
           'HRM')
GO

GO
alter table PR_TIMEKEEPING_TYPE add LIST_PR_ORGANIZATION_ID_NOR nvarchar(2000) default '' NOT NULL 
GO
alter table PR_TIMEKEEPING_TYPE add SQL_WHERE nvarchar(max) default '' NOT NULL 
GO
alter table PR_TIMEKEEPING_TYPE add CHECK_TIMEWORK_TYPE nvarchar(30) default '' NOT NULL 
GO
--22-07-2019----
GO
CREATE TABLE [dbo].[DM_CONTRACT_PROGRESS](
	[CONTRACT_PROGRESS_ID] [nvarchar](20) NOT NULL,
	[CONTRACT_PROGRESS_NAME] [nvarchar](150) NOT NULL,
	[CONTRACT_PROGRESS_NAME_EN] [nvarchar](150) NOT NULL,
	[CONTRACT_PROGRESS_NAME_OTHER] [nvarchar](150) NOT NULL,
	[ACTIVE] [smallint] NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[COMMENT_DETAIL] [nvarchar](150) NOT NULL,
 CONSTRAINT [PK_DM_CONTRACT_PROGRESS] PRIMARY KEY CLUSTERED 
(
	[CONTRACT_PROGRESS_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO SYS_MENU
           ([MENU_ID]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_ICON]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
           ('DM_CONTRACT_PROGRESS','HRM','HRM_INFO','LIST','HRMTSGROUP1','',80,'DM_CONTRACT_PROGRESS',20,1)
GO

GO
alter table HR_EMPLOYEE_CONTRACT add CONTRACT_PROGRESS_ID nvarchar(20) not null default ''
GO

---24--07-----
GO
alter table HR_EMPLOYEE_LEAVE_ABOUT add LEAVE_HOUR money DEFAULT 0 NOT NULL 
GO
alter table HR_EMPLOYEE_LEAVE_ABOUT add SUB_EMPLOYEE_ID nvarchar(20) DEFAULT 0 NOT NULL 
GO

alter table HR_EMPLOYEE_LEAVE add LEAVE_HOUR money DEFAULT 0 NOT NULL 
GO
alter table HR_EMPLOYEE_LEAVE add SUB_EMPLOYEE_ID nvarchar(20) DEFAULT 0 NOT NULL 
GO


alter table HR_EMPLOYEE_POSITION add COMMUNES_ID nvarchar(20) DEFAULT 0 NOT NULL 
GO
alter table HR_EMPLOYEE_POSITION add DISTRICT_ID nvarchar(20) DEFAULT 0 NOT NULL 
GO
alter table HR_EMPLOYEE_POSITION add PROVINCE_ID nvarchar(20) DEFAULT 0 NOT NULL 
GO


---25-/07-----2019
GO
CREATE TABLE [dbo].[OFM_DOCUMENT_PUBLIC](
	[DOCUMENT_PB_ID] [nvarchar](20) NOT NULL,
	[DOCUMENT_PB_NAME] [nvarchar](220) NOT NULL,
	[DOCUMENT_PB_LINK] [nvarchar](220) NOT NULL,
	[DOCUMENT_PB_DATE] [smalldatetime] NOT NULL,
	[DOCUMENT_PB_STATUS] [nvarchar](20) NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[ACTIVE] [smallint] NOT NULL,
 CONSTRAINT [PK_OFM_DOCUMENT_PUBLIC] PRIMARY KEY CLUSTERED 
(
	[DOCUMENT_PB_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


---29-07---2019-------
GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('HR_SEND_TIMECHEK_CC_LINE_MANAGER',
           '0',
           N'Tự động CC email cho quản lý trực tiếp',
           'BOOLEAN',
           'HRM')
GO

----30-07-2019-----

GO
CREATE TABLE [dbo].[DM_BENEFIT_ITEM](
	[BENEFIT_ITEM_ID] [nvarchar](20) NOT NULL,
	[BENEFIT_ITEM_NAME] [nvarchar](150) NOT NULL,
	[BENEFIT_ITEM_NAME_EN] [nvarchar](150) NOT NULL,
	[BENEFIT_ITEM_NAME_OTHER] [nvarchar](150) NOT NULL,
	[COMMENTS] [nvarchar](350) NOT NULL,
	[ACTIVE] [smallint] NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_DM_BENEFIT_ITEM] PRIMARY KEY CLUSTERED 
(
	[BENEFIT_ITEM_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO SYS_MENU
           ([MENU_ID]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_ICON]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
           ('DM_BENEFIT_ITEM','HRM','HRM_REC','LIST','HRMRCGROUP2','',80,'DM_BENEFIT_ITEM',20,1)
GO

GO
CREATE TABLE [dbo].[HR_CANDIDATES_BENEFIT](
	[PR_KEY] [uniqueidentifier] NOT NULL,
	[CANDIDATES_ID] [nvarchar](20) NOT NULL,
	[BENEFIT_ITEM_ID] [nvarchar](20) NOT NULL,
	[COMMENTS] [nvarchar](220) NOT NULL,
	[POINT_RANK] [money] NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_HR_CANDIDATES_BENEFIT] PRIMARY KEY CLUSTERED 
(
	[PR_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

alter table DM_POSITION add PR_ORGANIZATION_ID nvarchar(20) not null DEFAULT ''

GO
alter table DM_PR_HR_ORGANIZATION add PR_ORGANIZATION_NOTE1 nvarchar(220) not null DEFAULT ''
GO
alter table DM_PR_HR_ORGANIZATION add PR_ORGANIZATION_NOTE2 nvarchar(220) not null DEFAULT ''
GO

---31/07/2019----
GO
INSERT INTO [dbo].[SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           (N'LINK_WEBSITE_GSTC'
           ,N''
           ,'Link Website Giám sát thi công online'
           ,'STRING'
           ,'HRM')

GO

-- 04/08 --
GO
alter table HR_SHIFT_V2  add  IS_INSERT_OUT_CHECK  smallint not null default 0
GO

CREATE TABLE [dbo].[DM_SERVICE_TYPE](
	[SERVICE_TYPE_ID] [nvarchar](20) NOT NULL,
	[SERVICE_TYPE_NAME] [nvarchar](500) NOT NULL,
	[SUB_SERVICE_LINE_ID] [nvarchar](20) NULL,
	[ORGANIZATION_ID] [nvarchar](20) NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[ACTIVE] [smallint] NOT NULL,
 CONSTRAINT [PK_DM_SERVICE_TYPE] PRIMARY KEY CLUSTERED 
(
	[SERVICE_TYPE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO


GO



CREATE TABLE [dbo].[JOB_TRAN_GROUP](
	[JOB_TRAN_GROUP_ID] [nvarchar](20) NOT NULL,
	[JOB_TRAN_GROUP_NAME] [nvarchar](50) NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[ACTIVE] [smallint] NOT NULL,
	[ORGANIZATION_ID] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_JOB_TRAN_GROUP] PRIMARY KEY CLUSTERED 
(
	[JOB_TRAN_GROUP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[JOB_TRAN](
	[JOB_TRAN_ID] [nvarchar](20) NOT NULL,
	[JOB_TRAN_NAME] [nvarchar](220) NOT NULL,
	[JOB_TRAN_GROUP_ID] [nvarchar](20) NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[ACTIVE] [smallint] NOT NULL,
	[ORGANIZATION_ID] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_JOB_TRAN] PRIMARY KEY CLUSTERED 
(
	[JOB_TRAN_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[JOB_TRAN_CONFIG](
	[PR_KEY] [uniqueidentifier] NOT NULL,
	[JOB_TRAN_ID] [nvarchar](20) NOT NULL,
	[JOB_TRAN_GROUP_ID] [nvarchar](20) NOT NULL,
	[JOB_TRAN_DATE] [smalldatetime] NOT NULL,
	[STEP_ID] [smallint] NOT NULL,
	[STEP_NAME] [nvarchar](150) NOT NULL,
	[STEP_STATUS_ID] [nvarchar](20) NOT NULL,
	[IS_LINE_MANAGER] [smallint] NOT NULL,
	[PR_ORGANIZATION_ID] [nvarchar](20) NOT NULL,
	[EMPLOYEE_ID] [nvarchar](20) NOT NULL,
	[WAIT_DAY_NUM] [money] NOT NULL,
	[IS_EMAIL] [smallint] NOT NULL,
	[IS_FINAL] [smallint] NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[ACTIVE] [smallint] NOT NULL,
	[ORGANIZATION_ID] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_JOB_TRAN_CONFIG] PRIMARY KEY CLUSTERED 
(
	[PR_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[JOB_TRAN_CONFIG](
	[PR_KEY] [uniqueidentifier] NOT NULL,
	[JOB_TRAN_ID] [nvarchar](20) NOT NULL,
	[JOB_TRAN_GROUP_ID] [nvarchar](20) NOT NULL,
	[JOB_TRAN_DATE] [smalldatetime] NOT NULL,
	[STEP_ID] [smallint] NOT NULL,
	[STEP_NAME] [nvarchar](150) NOT NULL,
	[STEP_STATUS_ID] [nvarchar](20) NOT NULL,
	[IS_LINE_MANAGER] [smallint] NOT NULL,
	[PR_ORGANIZATION_ID] [nvarchar](20) NOT NULL,
	[EMPLOYEE_ID] [nvarchar](20) NOT NULL,
	[WAIT_DAY_NUM] [money] NOT NULL,
	[IS_EMAIL] [smallint] NOT NULL,
	[IS_FINAL] [smallint] NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[ACTIVE] [smallint] NOT NULL,
	[ORGANIZATION_ID] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_JOB_TRAN_CONFIG] PRIMARY KEY CLUSTERED 
(
	[PR_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[JOB_TRAN_CURRENT](
	[PR_KEY] [uniqueidentifier] NOT NULL,
	[JOB_TRAN_DATE] [smalldatetime] NOT NULL,
	[JOB_TRAN_ID] [nvarchar](20) NOT NULL,
	[JOB_TRAN_GROUP_ID] [nvarchar](20) NOT NULL,
	[JOB_TRAN_NO] [nvarchar](50) NOT NULL,
	[STEP_ID] [smallint] NOT NULL,
	[STEP_NAME] [nvarchar](150) NOT NULL,
	[STEP_STATUS_ID] [nvarchar](20) NOT NULL,
	[STEP_ID_NEXT] [smallint] NOT NULL,
	[STEP_STATUS_ID_NEXT] [nvarchar](50) NOT NULL,
	[STEP_NAME_NEXT] [nvarchar](150) NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[ACTIVE] [smallint] NOT NULL,
	[ORGANIZATION_ID] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_JOB_TRAN_CURRENT] PRIMARY KEY CLUSTERED 
(
	[PR_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO



--06---08---2019-----


GO
CREATE TABLE [dbo].[JOB_REQUEST](
	[PR_KEY] [uniqueidentifier] NOT NULL,
	[JOB_TRAN_ID] [nvarchar](20) NOT NULL,
	[JOB_TRAN_STATUS_ID] [nvarchar](20) NOT NULL,
	[JOB_RQ_NO] [nvarchar](30) NOT NULL,
	[JOB_RQ_NAME] [nvarchar](250) NOT NULL,
	[JOB_RQ_DATE] [smalldatetime] NOT NULL,
	[EMPLOYEE_ID] [nvarchar](20) NOT NULL,
	[JOB_PRIORITY_ID] [nvarchar](20) NOT NULL,
	[EMPLOYEE_ID_TO_ASSIGN] [nvarchar](20) NOT NULL,
	[JOB_CONTENT] [nvarchar](550) NOT NULL,
	[JOB_COMPLAINT] [nvarchar](550) NOT NULL,
	[JOB_PLAN_OFFER] [nvarchar](550) NOT NULL,
	[JOB_PLAN_RESULT] [nvarchar](550) NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[ACTIVE] [smallint] NOT NULL,
	[ORGANIZATION_ID] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_JOB_REQUEST] PRIMARY KEY CLUSTERED 
(
	[PR_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[JOB_RQ_FILE_ATTACH](
	[PR_KEY] [uniqueidentifier] NOT NULL,
	[JOB_RQ_NO] [nvarchar](50) NOT NULL,
	[JOB_RQ_ATTACH_DATE] [smalldatetime] NOT NULL,
	[JOB_RQ_ATTACH_COMMENT] [nvarchar](550) NOT NULL,
	[EMPLOYEE_ID] [nvarchar](30) NOT NULL,
	[FILE_NAME] [nvarchar](150) NOT NULL,
	[FILE_NAME_PATH] [nvarchar](550) NOT NULL,
	[FILE_NAME_ATTACH] [varbinary](max) NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[ACTIVE] [smallint] NOT NULL,
	[ORGANIZATION_ID] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_JOB_RQ_FILE_ATTACH] PRIMARY KEY CLUSTERED 
(
	[PR_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[JOB_RQ_POST](
	[PR_KEY] [uniqueidentifier] NOT NULL,
	[JOB_RQ_NO] [nvarchar](50) NOT NULL,
	[JOB_RQ_POST_DATE] [nchar](10) NOT NULL,
	[JOB_RQ_POST_COMMENT] [nvarchar](950) NOT NULL,
	[EMPLOYEE_ID_TO_ASSIGN] [nvarchar](20) NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[ACTIVE] [smallint] NOT NULL,
	[ORGANIZATION_ID] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_JOB_RQ_POST] PRIMARY KEY CLUSTERED 
(
	[PR_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[JOB_TRAN](
	[JOB_TRAN_ID] [nvarchar](20) NOT NULL,
	[JOB_TRAN_NAME] [nvarchar](220) NOT NULL,
	[JOB_TRAN_GROUP_ID] [nvarchar](20) NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[ACTIVE] [smallint] NOT NULL,
	[ORGANIZATION_ID] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_JOB_TRAN] PRIMARY KEY CLUSTERED 
(
	[JOB_TRAN_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[JOB_TRAN_CONFIG](
	[PR_KEY] [uniqueidentifier] NOT NULL,
	[JOB_TRAN_ID] [nvarchar](20) NOT NULL,
	[JOB_TRAN_GROUP_ID] [nvarchar](20) NOT NULL,
	[DATE_CONFIG] [smalldatetime] NOT NULL,
	[STEP_ID] [smallint] NOT NULL,
	[STEP_NAME] [nvarchar](150) NOT NULL,
	[JOB_TRAN_STATUS_ID] [nvarchar](20) NOT NULL,
	[JOB_TRAN_STATUS_ID_NEXT] [nvarchar](20) NOT NULL,
	[IS_LINE_MANAGER] [smallint] NOT NULL,
	[PR_ORGANIZATION_ID] [nvarchar](20) NOT NULL,
	[EMPLOYEE_ID] [nvarchar](20) NOT NULL,
	[WAIT_DAY_NUM] [money] NOT NULL,
	[NOTIFICATION_DAY_NUM
NOTIFICATION_DAY_NUM] [smallint] NOT NULL,
	[IS_EMAIL] [smallint] NOT NULL,
	[IS_FINAL] [smallint] NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[ACTIVE] [smallint] NOT NULL,
	[ORGANIZATION_ID] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_JOB_TRAN_CONFIG] PRIMARY KEY CLUSTERED 
(
	[PR_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[JOB_TRAN_GROUP](
	[JOB_TRAN_GROUP_ID] [nvarchar](20) NOT NULL,
	[JOB_TRAN_GROUP_NAME] [nvarchar](50) NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[ACTIVE] [smallint] NOT NULL,
	[ORGANIZATION_ID] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_JOB_TRAN_GROUP] PRIMARY KEY CLUSTERED 
(
	[JOB_TRAN_GROUP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[JOB_TRAN_PRIORITY](
	[JOB_PRIORITY_ID] [nvarchar](20) NOT NULL,
	[JOB_PRIORITY_NAME] [nvarchar](150) NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[ACTIVE] [smallint] NOT NULL,
	[ORGANIZATION_ID] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_JOB_TRAN_PRIORITY] PRIMARY KEY CLUSTERED 
(
	[JOB_PRIORITY_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[JOB_TRAN_STATUS](
	[JOB_TRAN_STATUS_ID] [nvarchar](20) NOT NULL,
	[JOB_TRAN_STATUS_NAME] [nchar](70) NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[ACTIVE] [smallint] NOT NULL,
	[ORGANIZATION_ID] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_JOB_TRAN_STATUS] PRIMARY KEY CLUSTERED 
(
	[JOB_TRAN_STATUS_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

---------07-08-2019------
GO
CREATE TABLE [dbo].[DM_TR_PROJECT](
	[PROJECT_ID] [nvarchar](20) NOT NULL,
	[PROJECT_NAME] [nvarchar](250) NOT NULL,
	[PROJECT_TYPE_ID] [nvarchar](20) NOT NULL,
	[PROJECT_LEADER_ID] [nvarchar](20) NOT NULL,
	[CLIENT_ID] [nvarchar](20) NOT NULL,
	[PARTNER_ID] [nvarchar](20) NOT NULL,
	[ORGANIZATION_ID] [nvarchar](20) NOT NULL,
	[PROJECT_STATUS_ID] [nvarchar](20) NOT NULL,
	[START_DATE] [smalldatetime] NOT NULL,
	[END_DATE] [smalldatetime] NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[ACTIVE] [smallint] NOT NULL,
	[NOTES] [nvarchar](2000) NOT NULL,
	[MANAGER_ID] [nvarchar](20) NOT NULL,
	[ENGAGEMENT_PARTNER_ID] [nvarchar](20) NOT NULL,
	[CONTRACT_VALUE] [money] NOT NULL,
	[CONTRACT_ID] [nvarchar](50) NOT NULL,
	[IS_APPROVED] [smallint] NOT NULL,
	[BUDGET_FEE] [money] NOT NULL,
	[BUDGET_HOURS] [money] NOT NULL,
	[BUDGET_COST] [money] NOT NULL,
	[RECOVERY_RATE] [money] NOT NULL,
	[PROJECT_LEADER_ID_1] [nvarchar](20) NOT NULL,
	[OFFICE_IN_CHARGE] [nvarchar](20) NOT NULL,
	[SERVICE_LINE_ID] [nvarchar](20) NOT NULL,
	[GTI_REFERRAL_FROM] [nvarchar](2000) NOT NULL,
	[OPES_BILLABLE] [nvarchar](10) NOT NULL,
	[SUB_SERVICE_LINE_ID] [nvarchar](50) NOT NULL,
	[BUSINESS_PARTNER] [nvarchar](500) NOT NULL,
	[SERVICE_TYPE_ID] [nvarchar](20) NOT NULL,
	[DISCOUNT_AMOUNT] [money] NOT NULL,
	[REFERRED_FEE] [money] NOT NULL,
	[BU_PAR_FEE] [money] NOT NULL,
	[IRC_DATE] [smalldatetime] NULL,
	[CONTRACT_DATE] [smalldatetime] NULL,
	[BILLALBE_FEE] [money] NOT NULL,
	[FEE_CARRIED_FW] [money] NOT NULL,
 CONSTRAINT [PK_DM_TR_PROJECT] PRIMARY KEY CLUSTERED 
(
	[PROJECT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO


GO
CREATE TABLE [dbo].[DM_TR_OFFICE_IN_CHARGE](
	[OFFICE_IN_CHARGE] [nvarchar](20) NOT NULL,
	[OFFICE_IN_CHARGE_NAME] [nvarchar](50) NOT NULL,
	[PR_ORGANIZATION_ID] [nvarchar](20) NOT NULL,
	[ACTIVE] [smallint] NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_DM_TR_OFFICE_IN_CHARGE] PRIMARY KEY CLUSTERED 
(
	[OFFICE_IN_CHARGE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

GO
alter table JOB_STATEMENT add IS_CHECK smallint DEFAULT 0 NOT NULL 
GO
GO
alter table JOB_STATEMENT_DETAIL add EX_RATE money DEFAULT 0 NOT NULL 
GO

GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('SELECT_POS_BY_ORG',
           '0',
           N'Chon chuc danh theo phong ban',
           'BOOLEAN',
           'HRM')
GO

---13---08---2019

GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
           (N'DM_CURRENCY'
           ,N'Currency'
           ,N''
           ,N'Hrm_DM'
           ,N'Dm_Currency'
           ,N''
           ,N'HRM_LIST'
           ,3
           ,N'HRM'
           ,N'HRM_TR'
           ,N'SUB'
           ,'MANAGE'
           ,0
           ,N''
           ,13
           ,1)
GO

INSERT INTO [dbo].[SEC_PERMISSION_WEB]
           ([PR_KEY]
           ,[FUNCTION_ID]
           ,[USER_GROUP_ID]
           ,[IS_VIEW]
           ,[IS_EXECUTE]
           ,[TITLE])
     VALUES
           (NEWID()
           ,'DM_CURRENCY'
           ,'ADMIN'
           ,1
           ,1
           ,N'')
GO

INSERT INTO [dbo].[SEC_PERMISSION_WEB]
           ([PR_KEY]
           ,[FUNCTION_ID]
           ,[USER_GROUP_ID]
           ,[IS_VIEW]
           ,[IS_EXECUTE]
           ,[TITLE])
     VALUES
           (NEWID()
           ,'DM_CURRENCY'
           ,'PMS'
           ,1
           ,1
           ,N'')

GO
------ 15/08/2019------------------------------------
GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('SELECT_DM_PR_DETAIL_FINGER',
           '[FTS_ERP_OS].[dbo]',
           N'Database Name của kế toán nằm trong cùng một SQL Server với HRM',
           'STRING',
           'HRM')
GO

CREATE TABLE [dbo].[HR_DEVIVE_ALL_LOG](
	[PR_KEY] [uniqueidentifier] NOT NULL,
	[DEVICE_ID] [int] NOT NULL,
	[ENROLL_NUMBER] [int] NOT NULL,
	[USER_NAME] [nvarchar](100) NOT NULL,
	[TIME_CHECK] [smalldatetime] NOT NULL,
	[DATE_CHECK] [smalldatetime] NOT NULL,
	[CHECK_TYPE] [nvarchar](10) NOT NULL,
	[NOTES] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_HR_DEVIVE_ALL_LOG] PRIMARY KEY CLUSTERED 
(
	[PR_KEY]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO


GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
           (N'DM_CURRENCY'
           ,N'Currency'
           ,N''
           ,N'Hrm_DM'
           ,N'Dm_Currency'
           ,N''
           ,N'HRM_LIST'
           ,3
           ,N'HRM'
           ,N'HRM_TR'
           ,N'SUB'
           ,'MANAGE'
           ,0
           ,N''
           ,13
           ,1)
GO

INSERT INTO [dbo].[SEC_PERMISSION_WEB]
           ([PR_KEY]
           ,[FUNCTION_ID]
           ,[USER_GROUP_ID]
           ,[IS_VIEW]
           ,[IS_EXECUTE]
           ,[TITLE])
     VALUES
           (NEWID()
           ,'DM_CURRENCY'
           ,'ADMIN'
           ,1
           ,1
           ,N'')
GO

INSERT INTO [dbo].[SEC_PERMISSION_WEB]
           ([PR_KEY]
           ,[FUNCTION_ID]
           ,[USER_GROUP_ID]
           ,[IS_VIEW]
           ,[IS_EXECUTE]
           ,[TITLE])
     VALUES
           (NEWID()
           ,'DM_CURRENCY'
           ,'PMS'
           ,1
           ,1
           ,N'')

GO
------ 15/08/2019------------------------------------
GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('SELECT_DM_PR_DETAIL_FINGER',
           '[FTS_ERP_OS].[dbo]',
           N'Database Name của kế toán nằm trong cùng một SQL Server với HRM',
           'STRING',
           'HRM')
GO

CREATE TABLE [dbo].[HR_DEVIVE_ALL_LOG](
	[PR_KEY] [uniqueidentifier] NOT NULL,
	[DEVICE_ID] [int] NOT NULL,
	[ENROLL_NUMBER] [int] NOT NULL,
	[USER_NAME] [nvarchar](100) NOT NULL,
	[TIME_CHECK] [smalldatetime] NOT NULL,
	[DATE_CHECK] [smalldatetime] NOT NULL,
	[CHECK_TYPE] [nvarchar](10) NOT NULL,
	[NOTES] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_HR_DEVIVE_ALL_LOG] PRIMARY KEY CLUSTERED 
(
	[PR_KEY]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'HRM_JO'--[MENU_ID]
           ,N'Job Offer'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,''--[CONTROLLER]
           ,''--[ACTION]
           ,'fa fa-calendar-plus-o color-peru'--[MENU_ICON]
           ,''--[PARENT_ID]
           ,1 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_JO'--[MODULE_ID]
           ,'MODULE'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,4 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'HRM_JO', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'DM_JO'--[MENU_ID]
           ,N'Danh mục'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,''--[CONTROLLER]
           ,''--[ACTION]
           ,''--[MENU_ICON]
           ,'HRM_JO'--[PARENT_ID]
           ,2 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_JO'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,1 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'DM_JO', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'MANAGE_JO'--[MENU_ID]
           ,N'Quản lý'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,''--[CONTROLLER]
           ,''--[ACTION]
           ,''--[MENU_ICON]
           ,'HRM_JO'--[PARENT_ID]
           ,2 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_JO'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,2 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'MANAGE_JO', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO


-----16/07/2019----
GO
CREATE TABLE DM_JO_OFFER_TYPE
(
OFFER_TYPE_ID NVARCHAR(20) PRIMARY KEY NOT NULL DEFAULT '',
OFFER_TYPE_NAME NVARCHAR(100) NOT NULL DEFAULT '',
COMMENTS NVARCHAR(200) NOT NULL DEFAULT '',
ACTIVE SMALLINT NOT NULL DEFAULT 1,
USER_ID NVARCHAR(20) NOT NULL DEFAULT ''
)
GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'DM_JO_OFFER_TYPE'--[MENU_ID]
           ,N'Danh mục loại đề xuất công việc'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'JobOfferManagement'--[CONTROLLER]
           ,'Dm_Jo_Offer_Type'--[ACTION]
           ,''--[MENU_ICON]
           ,'DM_JO'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_JO'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,1 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'DM_JO_OFFER_TYPE', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO
CREATE TABLE JO_OFFER_MANAGE --BẢNG ĐỀ XUẤT CÔNG VIỆC
(
PR_KEY UNIQUEIDENTIFIER primary key NOT NULL DEFAULT NEWID(),
OFFER_NO NVARCHAR(20) NOT NULL DEFAULT '',
OFFER_TYPE_ID NVARCHAR(20)  NOT NULL DEFAULT '',
OFFER_DATE SMALLDATETIME,
RECEIVE_EMPLOYEE_ID NVARCHAR(20) NOT NULL DEFAULT '', -- NGƯỜI NHẬN
PR_ORGANIZATION_ID NVARCHAR(20) NOT NULL DEFAULT '',
EMPLOYEE_ID NVARCHAR(20) NOT NULL DEFAULT '',
OFFER_CONTENT NVARCHAR(300) NOT NULL DEFAULT '',
OFFER_PROBLEM NVARCHAR(300) NOT NULL DEFAULT '', --VƯỚNG MẮC
OFFER_PLAN NVARCHAR(300) NOT NULL DEFAULT '',-- ĐỀ XUẤT PHƯƠNG ÁN
STATUS1_ID NVARCHAR(20) NOT NULL DEFAULT '',
STATUS2_ID NVARCHAR(20) NOT NULL DEFAULT '',
STATUS3_ID NVARCHAR(20) NOT NULL DEFAULT '',
STATUS4_ID NVARCHAR(20) NOT NULL DEFAULT '',
STATUS5_ID NVARCHAR(20) NOT NULL DEFAULT '',
IS_CHECK_STATUS1 SMALLINT NOT NULL DEFAULT 0,
IS_CHECK_STATUS2 SMALLINT NOT NULL DEFAULT 0,
IS_CHECK_STATUS3 SMALLINT NOT NULL DEFAULT 0,
IS_CHECK_STATUS4 SMALLINT NOT NULL DEFAULT 0,
IS_CHECK_STATUS5 SMALLINT NOT NULL DEFAULT 0,
COMMENTS NVARCHAR(500) NOT NULL DEFAULT '',
USER_ID NVARCHAR(20) NOT NULL DEFAULT ''
)

GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'JO_OFFER_MANAGE'--[MENU_ID]
           ,N'Đề xuất công việc'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'JobOfferManagement'--[CONTROLLER]
           ,'Jo_Offer_Manage'--[ACTION]
           ,''--[MENU_ICON]
           ,'MANAGE_JO'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_JO'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,1 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'JO_OFFER_MANAGE', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO
CREATE TABLE JO_OFFER_CONFIG -- CẤU HÌNH JOB OFFER
(
PR_KEY UNIQUEIDENTIFIER PRIMARY KEY NOT NULL DEFAULT NEWID(),
TRAN_ID NVARCHAR(20) NOT NULL DEFAULT '',
TRAN_NO NVARCHAR(20) NOT NULL DEFAULT '',
TRAN_DATE SMALLDATETIME,
TRAN_TYPE NVARCHAR(20) NOT NULL DEFAULT '',
MODULE_ID NVARCHAR(300) NOT NULL DEFAULT '',
OFFER_TYPE_ID NVARCHAR(20) NOT NULL DEFAULT '',
IS_MYSELF SMALLINT NOT NULL DEFAULT 1,
IS_ALL_CHECKED SMALLINT NOT NULL DEFAULT 0,
COMMENTS NVARCHAR(300) NOT NULL DEFAULT '',
USER_ID NVARCHAR(20) NOT NULL DEFAULT ''
)
GO
CREATE TABLE JO_OFFER_CONFIG_DETAIL -- CẤU HÌNH JOB OFFER DETAIL
( 
PR_KEY UNIQUEIDENTIFIER PRIMARY KEY NOT NULL DEFAULT NEWID(),
FR_KEY UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
STEP_ID INT NOT NULL DEFAULT 0,
PR_ORGANIZATION_ID NVARCHAR(20) NOT NULL DEFAULT '',
EMPLOYEE_ID NVARCHAR(20) NOT NULL DEFAULT '',
STATUS_ID NVARCHAR(20) NOT NULL DEFAULT '',
IS_FINAL_APPROVE SMALLINT NOT NULL DEFAULT 0
)

GO
INSERT INTO [dbo].[SYS_TRAN]([TRAN_ID],[TRAN_NAME],[TRAN_CLASS] ,[TRAN_SUB_CLASS],[LIST_ORDER],[OUTPUT_FORM],[POSTED],[MODULE_ID],[PROJECT_ID],[USER_ID],[ACTIVE],[SHOW_IN_MENU],[IS_SALE],[IS_LISTING],[IS_FIXED_LENGTH_TRAN_NO],[TRAN_NO_LENGTH],[TRAN_NO_PREFIX],[TRAN_NO_POSTFIX],[TRAN_NO_BY_MONTH],[TRAN_NO_BY_DAY],[IS_POS])
VALUES('JO_OFFER_CONFIG',N'Cấu hình Job Offer','SALE' ,'JO_OFFER_CONFIG',7,'FRMSALE',1,'FIN_PO','FIN','ADMIN',1,1,1,0,1,6,'','',0,0,0)
GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'JO_OFFER_CONFIG'--[MENU_ID]
           ,N'Cấu hình Job Offer'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'JobOfferManagement'--[CONTROLLER]
           ,'Jo_Offer_Config'--[ACTION]
           ,''--[MENU_ICON]
           ,'DM_JO'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_JO'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,1 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'JO_OFFER_CONFIG', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO
----------13/08/2019----------
GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'HRM_JO_PERSONAL'--[MENU_ID]
           ,N'Job Offer'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,''--[CONTROLLER]
           ,''--[ACTION]
           ,'fa fa-calendar-plus-o color-peru'--[MENU_ICON]
           ,''--[PARENT_ID]
           ,1 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_JO'--[MODULE_ID]
           ,'MODULE'--[MENU_TYPE]
           ,'PERSONAL'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,4 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'HRM_JO_PERSONAL', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO
------13/08/2019-----
GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'JOB_REQUEST'--[MENU_ID]
           ,N'Job Request'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'JobOfferManagement'--[CONTROLLER]
           ,'Job_Request'--[ACTION]
           ,''--[MENU_ICON]
           ,'MANAGE_JO'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_JO'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,2 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'JOB_REQUEST', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO
----16/08/2019----
GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'JOB_TRAN_GROUP'--[MENU_ID]
           ,N'Khai báo Job Tran Group'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'JobOfferManagement'--[CONTROLLER]
           ,'Job_Tran_Group'--[ACTION]
           ,''--[MENU_ICON]
           ,'DM_JO'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_JO'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,2 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'JOB_TRAN_GROUP', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'JOB_TRAN'--[MENU_ID]
           ,N'Khai báo Job Tran'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'JobOfferManagement'--[CONTROLLER]
           ,'Job_Tran'--[ACTION]
           ,''--[MENU_ICON]
           ,'DM_JO'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_JO'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,3 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'JOB_TRAN', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'JOB_TRAN_STATUS'--[MENU_ID]
           ,N'Khai báo Job Tran Status'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'JobOfferManagement'--[CONTROLLER]
           ,'Job_Tran_Status'--[ACTION]
           ,''--[MENU_ICON]
           ,'DM_JO'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_JO'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,4 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'JOB_TRAN_STATUS', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'JOB_TRAN_PRIORITY'--[MENU_ID]
           ,N'Khai báo Job Tran Priority'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'JobOfferManagement'--[CONTROLLER]
           ,'Job_Tran_Priority'--[ACTION]
           ,''--[MENU_ICON]
           ,'DM_JO'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_JO'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,5 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'JOB_TRAN_PRIORITY', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'JOB_TRAN_CONFIG'--[MENU_ID]
           ,N'Cấu hình Job Tran Config'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'JobOfferManagement'--[CONTROLLER]
           ,'Job_Tran_Config'--[ACTION]
           ,''--[MENU_ICON]
           ,'DM_JO'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_JO'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,6 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'JOB_TRAN_CONFIG', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO

--- 19---08---2019 ---

GO
Alter table PR_EMPLOYEE_SALARY  add  FAULT_FORGET_CHECK_COUNT_NN  money not null default 0
GO
Alter table PR_EMPLOYEE_SALARY  add  DAYS_NOT_GO_WORK  money not null default 0
GO
Alter table PR_EMPLOYEE_SALARY  add  DAYS_OFF_WORK  money not null default 0
GO




-- 21/08 --
GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('HR_SEND_TIMECHEK_ERROR_YESTERDAY',
           '1',
           N'Gửi thông báo vi phạm chấm công ngày hôm trước',
           'BOOLEAN',
           'HRM')
GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('HR_SEND_TIMECHEK_ERROR_TODAY',
           '1',
           N'Gửi thông báo vi phạm chấm công ngày hiện tại',
           'BOOLEAN',
           'HRM')
GO

-- 28/08 --
GO
Alter table HR_TIME_WORK_V3  add  TH_NO_CHECK  int not null default 0
GO
Alter table HR_TIME_WORK_V3  add  TH_CHECK_LATE  int not null default 0
GO
Alter table HR_TIME_WORK_V3  add  TH_CHECK_EARLY  int not null default 0
GO
---29---08---2019-------------
GO
INSERT INTO [dbo].[SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           (N'IS_API_GOOGLE_MAP'
           ,N'0'
           ,N'Sử dụng google map'
           ,'INT'
           ,'HRM')
GO
INSERT INTO [dbo].[SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           (N'API_KEY_GOOGLE_MAP'
           ,N''
           ,N'API KEY google map'
           ,N'STRING'
           ,N'HRM')

GO
-- 30/08--
GO
Alter table HR_SHIFT_V2  add  IS_NO_DAYS_CALC_LATE_EARLY  smallint not null default 0
GO




---------------------- 3/9/2019 ----------------

GO

ALTER TABLE HR_TIME_CHECKINOUT_MAP
ADD JOB_TRAN_ID nvarchar(20) not null default('')

GO 

ALTER TABLE HR_TIME_CHECKINOUT_MAP
ADD JOB_TRAN_STATUS_ID nvarchar(20) not null default('')

GO
---------------------- 5/9/2019 ----------------

GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('HRM_START_DATE_SABBATICAL_TN_DATE_COMPANY',
           '1',
           N'Ngày bắt đầu tính thâm niên là ngày vào Cty',
           'BOOLEAN',
           'HRM')
GO


-----11-09-----2019------
GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('IS_USE_FTP',
           '0',
           N'Sử dụng FTP',
           'BOOLEAN',
           'HRM')
GO
alter table HR_DOCUMENT_FILESTREAM add NAME_FILE_FTP NVARCHAR(220) DEFAULT '' NOT NULL 
GO

GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('FTP_URI',
           '',
           N'Sử dụng FTP URL',
           'STRING',
           'HRM')
GO

GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('FTP_PASSWORD',
           '',
           N'Sử dụng FTP URL',
           'STRING',
           'HRM')
GO

GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('FTP_USER_ID',
           '',
           N'Sử dụng FTP URL',
           'STRING',
           'HRM')
GO

----16--09---2019-----
GO
Alter table DM_COMMUNES add COMMUNES_NAME_EG nvarchar(60) not null default ''
GO

----18-09--2019-------
GO

CREATE TABLE [dbo].[HR_TIME_CHECKINOUT_MAP](
	[PR_KEY] [uniqueidentifier] NOT NULL,
	[EMPLOYEE_ID] [nvarchar](20) NOT NULL,
	[TIME_CHECK] [smalldatetime] NOT NULL,
	[DATE_CHECK] [smalldatetime] NOT NULL,
	[CHECK_TYPE] [nvarchar](50) NOT NULL,
	[IMAGES] [varbinary](max) NOT NULL,
	[MAP_X] [nvarchar](20) NOT NULL,
	[MAP_Y] [nvarchar](20) NOT NULL,
	[MAP_Z] [nvarchar](20) NOT NULL,
	[ADRRESS_MAP] [nvarchar](max) NOT NULL,
	[CHECK_STATUS] [nvarchar](20) NOT NULL,
	[APPROVED_EMPLOYEE_ID] [nvarchar](20) NOT NULL,
	[CHECK_COMMENT] [nvarchar](max) NOT NULL,
	[JOB_TRAN_ID] [nvarchar](20) NOT NULL,
	[JOB_TRAN_STATUS_ID] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_HR_TIME_CHECKINOUT_MAP] PRIMARY KEY CLUSTERED 
(
	[PR_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

-- 20/09 --
GO
ALTER TABLE HR_EMPLOYEE_OTHER ADD [LIST_WORKING_DAY] nvarchar(200) not null default ''
GO

-- 23/09--
GO
ALTER TABLE  PR_DUTY_RATE  ADD  EMPLOYEE_ID nvarchar(20) not null default ''
GO


-- 25/09--
GO
alter table HR_EMPLOYEE_INFO add EMPLOYEE_INTRODUCED_AMOUNT money DEFAULT 0 NOT NULL 
GO

GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('HR_NUM_MONTH_INTRODUCE_AMOUNT',
           '0',
           N'Số tháng tính hưởng tiền giới thiệu nhân viên',
           'INT',
           'HRM')
GO
----27--09--2019
GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('IS_USE_STDWORKDAY_ALLPAYROLL',
           '1',
           N'Su dung ngay cong chuan cho toan bo bang luong',
           'BOOLEAN',
           'HRM')
GO

GO
alter table PR_ACTUAL_WORKINGDAYS add TIMEKEEPING_TYPE nvarchar(30) not null default ''
GO

GO
alter table DM_EMPLOYEE_STATUS add STATUS_COLOR nvarchar(50) DEFAULT '' NOT NULL 
GO
---01/10/2019----
GO
ALTER TABLE HR_TIME_CHECKINOUT_MAP ADD PR_ORGANIZATION_ID NVARCHAR(20) DEFAULT '' NOT NULL
GO
GO
ALTER TABLE JOB_TRAN_CONFIG ADD STEP_ORDER money DEFAULT 0 NOT NULL
GO


---02--10-2019-----
GO
CREATE TABLE [dbo].[JOB_TRANSACTION](
	[PR_KEY] [uniqueidentifier] NOT NULL,
	[FR_KEY] [uniqueidentifier] NOT NULL,
	[JOB_TRAN_ID] [nvarchar](20) NOT NULL,
	[JOB_TRAN_DATE] [smalldatetime] NOT NULL,
	[JOB_TRAN_STATUS_ID] [nvarchar](20) NOT NULL,
	[EMPLOYEE_ID] [nvarchar](20) NOT NULL,
	[EMPLOYEE_ID_ASSIGN] [nvarchar](20) NOT NULL,
	[EMPLOYEE_ID_ASSIGN_LIST] [nvarchar](250) NOT NULL,
	[PR_ORGANIZATION_ID] [nvarchar](20) NOT NULL,
	[JOB_COMMENT] [nvarchar](250) NOT NULL,
	[JOB_ATTACH_FILE_URL] [nvarchar](250) NOT NULL,
	[ORGANIZATION_ID] [nvarchar](20) NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[EMPLOYEE_ID_TRANSF] [nvarchar](20) NOT NULL,
	[EMPLOYEE_ID_APPROVED] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_JOB_TRANSACTION] PRIMARY KEY CLUSTERED 
(
	[PR_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
GO
ALTER TABLE JOB_TRANSACTION ADD EMPLOYEE_ID_TRANSF NVARCHAR(20) DEFAULT '' NOT NULL
GO

GO
ALTER TABLE JOB_TRANSACTION ADD EMPLOYEE_ID_APPROVED NVARCHAR(20) DEFAULT '' NOT NULL
GO

GO
ALTER TABLE HR_TIME_CHECKINOUT_MAP ADD EMPLOYEE_ID_CHECK NVARCHAR(20) DEFAULT '' NOT NULL
GO


GO
alter table PR_EMPLOYEE_SALARY_NS add IS_EX_1 SMALLINT DEFAULT 0 NOT NULL 
GO
alter table PR_EMPLOYEE_SALARY_NS add IS_EX_2 SMALLINT DEFAULT 0 NOT NULL 
GO
alter table PR_EMPLOYEE_SALARY_NS add IS_EX_3 SMALLINT DEFAULT 0 NOT NULL 
GO
alter table PR_EMPLOYEE_SALARY_NS add IS_EX_4 SMALLINT DEFAULT 0 NOT NULL 
GO
alter table PR_EMPLOYEE_SALARY_NS add IS_EX_5 SMALLINT DEFAULT 0 NOT NULL 
GO


---04-10--2019---

GO
INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
	  'HRM_OFM_PERSONAL'--[MENU_ID]
           ,N'Bảng tin'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,''--[CONTROLLER]
           ,''--[ACTION]
           ,'fa-newspaper-o'--[MENU_ICON]
           ,''--[PARENT_ID]
           ,1 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_OFM'--[MODULE_ID]
           ,'MODULE'--[MENU_TYPE]
           ,'PERSONAL'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,1 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO

INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'HRM_OFM_PERSONAL', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO



INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'DOCUMENT_PUBLIC'--[MENU_ID]
           ,N'Văn bản- tài liệu chung'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'Hrm'--[CONTROLLER]
           ,'Ofm_Documnet_Public'--[ACTION]
           ,''--[MENU_ICON]
           ,'HRM_OFM_PERSONAL'--[PARENT_ID]
           ,2 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_OFM'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'PERSONAL'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,3 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO

INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'DOCUMENT_PUBLIC', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO

alter table OFM_DOCUMENT_PUBLIC add DOCUMENT_TYPE_ID nvarchar(20) DEFAULT 0 NOT NULL 
GO

INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'DM_DOCUMENT_TYPE'--[MENU_ID]
           ,N'Danh mục văn bản-tài liệu'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'Dm_Document_Type'--[CONTROLLER]
           ,'Hrm_Dm'--[ACTION]
           ,''--[MENU_ICON]
           ,'DM_OFM'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_OFM'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,1 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'DM_DOCUMENT_TYPE', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO


INSERT INTO [dbo].[SYS_MENU_WEB]
           ([MENU_ID]
           ,[MENU_TITLE]
           ,[SEF_URL]
           ,[CONTROLLER]
           ,[ACTION]
           ,[MENU_ICON]
           ,[PARENT_ID]
           ,[MENU_LEVEL]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
        (
			'OFM_DOCUMENT_PUBLIC'--[MENU_ID]
           ,N'Quản lý văn bản-tài liệu'--[MENU_TITLE]
           ,''--[SEF_URL]
           ,'OfficeManagement'--[CONTROLLER]
           ,'Ofm_Document_Public'--[ACTION]
           ,''--[MENU_ICON]
           ,'MANAGE_OFM'--[PARENT_ID]
           ,3 --[MENU_LEVEL]
           ,'HRM'--[PROJECT_ID]
           ,'HRM_OFM'--[MODULE_ID]
           ,'SUB'--[MENU_TYPE]
           ,'MANAGE'--[MENU_GROUP]
           ,0 --[MENU_WIDTH]
           ,''--[MENU_TAG]
           ,6 --[MENU_ORDER]
           ,1 --[ACTIVE]
		)
GO
INSERT INTO [dbo].[SEC_PERMISSION_WEB] 
		([PR_KEY], 
		 [FUNCTION_ID],
		 [USER_GROUP_ID], 
		 [IS_VIEW], 
		 [IS_EXECUTE], 
		 [TITLE])
VALUES
		( NEWID(), 
		 'OFM_DOCUMENT_PUBLIC', 
		 'ADMIN', 
		 1,
		 1, 
		 '' ) 
GO
---08-10-2019----
GO
alter table HR_EMPLOYEE_TEMP_IMPORT add DESC_OF_WORK NVARCHAR(200) DEFAULT '' NOT NULL 
GO
GO
alter table HR_EMPLOYEE_TEMP_IMPORT add COMMENTS NVARCHAR(200) DEFAULT '' NOT NULL 
GO
GO
CREATE TABLE [dbo].[PR_SALARY_WARNING](
	[PR_KEY] [uniqueidentifier] NOT NULL,
	[TIMEKEEPING_TYPE_ID] [nvarchar](30) NOT NULL,
	[EFFECTIVE_DATE] [smalldatetime] NOT NULL,
	[COLUMN_NAME] [nvarchar](30) NOT NULL,
	[MIN_VALUE] [money] NOT NULL,
	[MAX_VALUE] [money] NOT NULL,
	[USER_ID] [nvarchar](30) NOT NULL,
	[ORGANIZATION_ID] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_PR_SALARY_WARNING] PRIMARY KEY CLUSTERED 
(
	[PR_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('IS_WARNING_SALARY_OVER',
           '0',
           N'Cảnh báo vượt ngưỡng lương theo từng cột',
           'BOOLEAN',
           'COMMON')
GO
GO
INSERT INTO [dbo].[SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           (N'LIST_POSITION_APPROVED'
           ,N'GD,PGD'
           ,N'Danh sách chức danh'
           ,N'STRING'
           ,N'HRM')

GO
INSERT [dbo].[SYS_MENU_WEB] ([MENU_ID], [MENU_TITLE], [SEF_URL], [CONTROLLER], [ACTION], [MENU_ICON], [PARENT_ID], [MENU_LEVEL], [PROJECT_ID], [MODULE_ID], [MENU_TYPE], [MENU_GROUP], [MENU_WIDTH], [MENU_TAG], [MENU_ORDER], [ACTIVE]) VALUES (N'CHECK_INOUT_MANAGE_PERSONAL', N'Check in out manage', N'', N'Hrm', N'CheckInOutManage', N'', N'HRM_PR_TIMEKEEPING_PERSONAL', 2, N'HRM', N'HRM_TIME', N'SUB', N'PERSONAL', 0, N'', 0, 1)
GO
INSERT [dbo].[SYS_MENU_WEB] ([MENU_ID], [MENU_TITLE], [SEF_URL], [CONTROLLER], [ACTION], [MENU_ICON], [PARENT_ID], [MENU_LEVEL], [PROJECT_ID], [MODULE_ID], [MENU_TYPE], [MENU_GROUP], [MENU_WIDTH], [MENU_TAG], [MENU_ORDER], [ACTIVE]) VALUES (N'HR_TIME_WORK_V3_MONTH_PERSONAL', N'Chấm công tháng', N'', N'Hrm', N'HrTimeWorkV3Month', N'', N'HRM_PR_TIMEKEEPING_PERSONAL', 2, N'HRM', N'HRM_TIME', N'SUB', N'PERSONAL', 0, N'', 8, 1)
GO
INSERT [dbo].[SYS_MENU_WEB] ([MENU_ID], [MENU_TITLE], [SEF_URL], [CONTROLLER], [ACTION], [MENU_ICON], [PARENT_ID], [MENU_LEVEL], [PROJECT_ID], [MODULE_ID], [MENU_TYPE], [MENU_GROUP], [MENU_WIDTH], [MENU_TAG], [MENU_ORDER], [ACTIVE]) VALUES (N'JOB_TRANSACTION_PERSONAL', N'Thông báo phê duyệt', N'', N'Hrm', N'Job_Transaction', N'fa-bell', N'', 1, N'HRM', N'', N'', N'PERSONAL', 0, N'', 9, 1)
GO
----09---10---2019----
GO
Alter table HR_TIME_CHECKINOUT_MAP add TIM_CHECK_FULL smalldatetime null
GO


----10---10---2019----
GO
ALTER TABLE HR_EMPLOYEE_OTHER ADD FIRST_LOGIN smallint not null default(1)
GO
GO
CREATE TABLE [dbo].[HR_CANDIDATES_OTHER](
	[CANDIDATES_ID] [nvarchar](20) NOT NULL,
	[ORGANIZATION_ID] [nvarchar](20) NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[WORK_PLACE] [nvarchar](120) NOT NULL,
	[WORK_PLACE2] [nvarchar](120) NOT NULL,
	[DATE_WORK_PLAN] [smalldatetime] NOT NULL,
	[DATE_WORK_PLAN1] [smalldatetime] NOT NULL,
	[WORK_PLACE_COMMENT] [nvarchar](220) NOT NULL,
	[WORK_PLACE_COMMENT1] [nvarchar](220) NOT NULL,
	[WORK_PLACE_COMMENT2] [nvarchar](220) NOT NULL,
	[SALARY_AMOUNT1] [money] NOT NULL,
	[SALARY_AMOUNT2] [money] NOT NULL,
	[SALARY_AMOUNT3] [money] NOT NULL,
	[SALARY_AMOUNT4] [money] NOT NULL,
	[SALARY_AMOUNT5] [money] NOT NULL,
	[SALARY_AMOUNT6] [money] NOT NULL,
	[SALARY_AMOUNT7] [money] NOT NULL,
	[SALARY_AMOUNT8] [money] NOT NULL,
	[SALARY_AMOUNT9] [money] NOT NULL,
	[SALARY_AMOUNT10] [money] NOT NULL,
	[SALARY_AMOUNT11] [money] NOT NULL,
	[SALARY_AMOUNT12] [money] NOT NULL,
	[SALARY_AMOUNT13] [money] NOT NULL,
	[SALARY_AMOUNT14] [money] NOT NULL,
	[SALARY_AMOUNT15] [money] NOT NULL,
	[SALARY_AMOUNT16] [money] NOT NULL,
 CONSTRAINT [PK_HR_CANDIDATES_OTHER] PRIMARY KEY CLUSTERED 
(
	[CANDIDATES_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--12/10/2019
GO
alter table PR_EMPLOYEE_SALARY_NS add IS_SUPPORT_SIZE SMALLINT DEFAULT 0 NOT NULL 
GO
GO
alter table HR_CANDIDATES_OTHER  add WORK_PLACE1 NVARCHAR(200) DEFAULT '' NOT NULL 
GO


--14-10-2019----
GO
alter table PR_TIMEKEEPING_TYPE add IS_PIT smallint not null DEFAULT 0
GO

GO
INSERT INTO SYS_MENU
           ([MENU_ID]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_ICON]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
           ('PR_EMPLOYEE_SALARY_BASE_SIZE_NS','HRM','HRM_SA','FUNC','HRM_SA_1','',85,'PR_EMPLOYEE_SALARY_BASE_SIZE_NS',12,1)
		   
GO


-- 12/10 --
GO
Alter table HR_TIME_WORK_V3  add  OVER_TIME_15 money not null default 0
GO
Alter table HR_TIME_WORK_V3  add  OVER_TIME_16 money not null default 0
GO
Alter table HR_TIME_WORK_V3  add  OVER_TIME_17 money not null default 0
GO
Alter table HR_TIME_WORK_V3  add  OVER_TIME_18 money not null default 0
GO
Alter table HR_TIME_WORK_V3  add  OVER_TIME_19 money not null default 0
GO
Alter table HR_TIME_WORK_V3  add  OVER_TIME_20 money not null default 0
GO

Alter table HR_TIME_WORK_V3  add  OVER_TIME_15_ADD money not null default 0
GO
Alter table HR_TIME_WORK_V3  add  OVER_TIME_16_ADD money not null default 0
GO
Alter table HR_TIME_WORK_V3  add  OVER_TIME_17_ADD money not null default 0
GO
Alter table HR_TIME_WORK_V3  add  OVER_TIME_18_ADD money not null default 0
GO
Alter table HR_TIME_WORK_V3  add  OVER_TIME_19_ADD money not null default 0
GO
Alter table HR_TIME_WORK_V3  add  OVER_TIME_20_ADD money not null default 0
GO

Alter table HR_TIME_WORK_V3  add  OVER_TIME_15_REG money not null default 0
GO
Alter table HR_TIME_WORK_V3  add  OVER_TIME_16_REG money not null default 0
GO
Alter table HR_TIME_WORK_V3  add  OVER_TIME_17_REG money not null default 0
GO
Alter table HR_TIME_WORK_V3  add  OVER_TIME_18_REG money not null default 0
GO
Alter table HR_TIME_WORK_V3  add  OVER_TIME_19_REG money not null default 0
GO
Alter table HR_TIME_WORK_V3  add  OVER_TIME_20_REG money not null default 0
GO

Alter table  HR_TIME_WORK_TMP  add  OVER_TIME_15 money not null default 0
GO
Alter table  HR_TIME_WORK_TMP  add  OVER_TIME_16 money not null default 0
GO
Alter table  HR_TIME_WORK_TMP  add  OVER_TIME_17 money not null default 0
GO
Alter table  HR_TIME_WORK_TMP  add  OVER_TIME_18 money not null default 0
GO
Alter table  HR_TIME_WORK_TMP  add  OVER_TIME_19 money not null default 0
GO
Alter table  HR_TIME_WORK_TMP  add  OVER_TIME_20 money not null default 0
GO

Alter table HR_TIME_WORK_V3  add  POSITION_ID nvarchar(20) not null default ''
GO


--15-10-2019----

GO
 INSERT INTO SYS_MENU_WEB (MENU_ID,MENU_TITLE,SEF_URL,CONTROLLER,ACTION,MENU_ICON,PARENT_ID,MENU_LEVEL,PROJECT_ID,MODULE_ID,MENU_TYPE,MENU_GROUP,MENU_WIDTH,MENU_TAG,MENU_ORDER,ACTIVE) VALUES (N'EMPLOYEE_LEAVE',N'Duyệt chế độ',N'',N'Hrm_Manage',N'EmployeeLeave',N'',N'HRM_HR_1',3,N'HRM',N'HRM_INFO',N'SUB',N'MANAGE',0,N'',7,1) 
GO
 INSERT INTO SYS_MENU_WEB (MENU_ID,MENU_TITLE,SEF_URL,CONTROLLER,ACTION,MENU_ICON,PARENT_ID,MENU_LEVEL,PROJECT_ID,MODULE_ID,MENU_TYPE,MENU_GROUP,MENU_WIDTH,MENU_TAG,MENU_ORDER,ACTIVE) VALUES (N'EMPLOYEE_LEAVE_PERSONAL',N'Duyệt chế độ',N'',N'Hrm',N'EmployeeLeave',N'',N'HRM_CHE_DO_PERSONAL',2,N'HRM',N'HRM_INFO',N'SUB',N'PERSONAL',0,N'',6,1) 
GO


GO
alter table HR_EMPLOYEE_LEAVE_ABOUT add EMPLOYEE_ID_CHECK nvarchar(20) DEFAULT '' NOT NULL 
GO

alter table HR_EMPLOYEE_LEAVE_ABOUT add JOB_TRAN_ID nvarchar(20) DEFAULT '' NOT NULL 
GO

alter table HR_EMPLOYEE_LEAVE_ABOUT add JOB_TRAN_STATUS_ID nvarchar(20) DEFAULT '' NOT NULL 
GO

alter table HR_EMPLOYEE_LEAVE_ABOUT add EMPLOYEE_ID_APPROVED nvarchar(20) DEFAULT '' NOT NULL 
GO


GO
Alter table  PR_EMPLOYEE_SALARY add  OVER_TIME_15 money not null default 0
GO
Alter table  PR_EMPLOYEE_SALARY add  OVER_TIME_16 money not null default 0
GO
Alter table  PR_EMPLOYEE_SALARY add  OVER_TIME_17 money not null default 0
GO
Alter table  PR_EMPLOYEE_SALARY add  OVER_TIME_18 money not null default 0
GO
Alter table  PR_EMPLOYEE_SALARY add  OVER_TIME_19 money not null default 0
GO
Alter table  PR_EMPLOYEE_SALARY add  OVER_TIME_20 money not null default 0
GO

Alter table  PR_EMPLOYEE_SALARY_YEAR  add  OVER_TIME_15 money not null default 0
GO
Alter table  PR_EMPLOYEE_SALARY_YEAR  add  OVER_TIME_16 money not null default 0
GO
Alter table  PR_EMPLOYEE_SALARY_YEAR  add  OVER_TIME_17 money not null default 0
GO
Alter table  PR_EMPLOYEE_SALARY_YEAR  add  OVER_TIME_18 money not null default 0
GO
Alter table  PR_EMPLOYEE_SALARY_YEAR  add  OVER_TIME_19 money not null default 0
GO
Alter table  PR_EMPLOYEE_SALARY_YEAR  add  OVER_TIME_20 money not null default 0
GO

--16--10---2019--
GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('IS_PR_ORGANIZATION_POSTION_BY_CBO',
           '0',
           N'Thay đổi bộ phận thay đổi lương',
           'BOOLEAN',
           'HRM')
GO

GO
alter table PR_SALARY_WARNING add PR_ORGANIZATION_ID nvarchar(20) not null default ''
GO



GO
 INSERT INTO SYS_MENU_WEB (MENU_ID,MENU_TITLE,SEF_URL,CONTROLLER,ACTION,MENU_ICON,PARENT_ID,MENU_LEVEL,PROJECT_ID,MODULE_ID,MENU_TYPE,MENU_GROUP,MENU_WIDTH,MENU_TAG,MENU_ORDER,ACTIVE) VALUES (N'EMPLOYEE_LEAVE',N'Duyệt chế độ',N'',N'Hrm_Manage',N'EmployeeLeave',N'',N'HRM_HR_1',3,N'HRM',N'HRM_INFO',N'SUB',N'MANAGE',0,N'',7,1) 
GO
 INSERT INTO SYS_MENU_WEB (MENU_ID,MENU_TITLE,SEF_URL,CONTROLLER,ACTION,MENU_ICON,PARENT_ID,MENU_LEVEL,PROJECT_ID,MODULE_ID,MENU_TYPE,MENU_GROUP,MENU_WIDTH,MENU_TAG,MENU_ORDER,ACTIVE) VALUES (N'EMPLOYEE_LEAVE_PERSONAL',N'Duyệt chế độ',N'',N'Hrm',N'EmployeeLeave',N'',N'HRM_CHE_DO_PERSONAL',2,N'HRM',N'HRM_INFO',N'SUB',N'PERSONAL',0,N'',6,1) 
GO
----------17/10/2019
GO
 INSERT INTO SYS_MENU_WEB (MENU_ID,MENU_TITLE,SEF_URL,CONTROLLER,ACTION,MENU_ICON,PARENT_ID,MENU_LEVEL,PROJECT_ID,MODULE_ID,MENU_TYPE,MENU_GROUP,MENU_WIDTH,MENU_TAG,MENU_ORDER,ACTIVE) VALUES (N'HISTORY_CHECKINOUT_PERSONAL',N'Lịch sử Check in out online',N'',N'Hrm',N'CheckInOutview',N'',N'HRM_PR_TIMEKEEPING_PERSONAL',2,N'HRM',N'HRM_TIME',N'SUB',N'PERSONAL',0,N'',10,1) 
GO


----------22/10/2019
GO

CREATE TABLE [dbo].[SYS_TEMPLATES_CONFIG_WEB](
	[PR_KEY] [money] NOT NULL,
	[REPORT_FILE_NAME] [nvarchar](100) NOT NULL,
	[TABLE_NAME] [nvarchar](50) NOT NULL,
	[COLUMN_NAME] [nvarchar](50) NOT NULL,
	[DATA_TYPE] [nvarchar](20) NOT NULL,
	[TITLE] [nvarchar](100) NOT NULL,
	[VISIBLE] [smallint] NOT NULL,
	[FORMAT] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_SYS_TEMPLATES_CONFIG_WEB] PRIMARY KEY CLUSTERED 
(
	[PR_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

-----------23/10/2019

GO
ALTER TABLE HR_VM_REGISTER
ADD JOB_TRAN_ID nvarchar(20) not null default('')
GO
ALTER TABLE HR_VM_REGISTER
ADD JOB_TRAN_STATUS_ID nvarchar(20) not null default('')
GO
ALTER TABLE HR_VM_REGISTER
ADD EMPLOYEE_ID_CHECK nvarchar(20) not null default('')
GO
ALTER TABLE HR_VM_REGISTER
ADD EMPLOYEE_ID_APPROVED nvarchar(20) not null default('')
GO


-----------25/10/2019

GO
 INSERT INTO SYS_MENU_WEB (MENU_ID,MENU_TITLE,SEF_URL,CONTROLLER,ACTION,MENU_ICON,PARENT_ID,MENU_LEVEL,PROJECT_ID,MODULE_ID,MENU_TYPE,MENU_GROUP,MENU_WIDTH,MENU_TAG,MENU_ORDER,ACTIVE) VALUES (N'HR_VEHICLE_REGISTER_PERSONAL',N'Đăng ký xe',N'',N'Hrm',N'Vehicle',N'',N'HRM_CHE_DO_PERSONAL',2,N'HRM',N'HRM_INFO',N'SUB',N'PERSONAL',0,N'',7,1) 
GO
 INSERT INTO SYS_MENU_WEB (MENU_ID,MENU_TITLE,SEF_URL,CONTROLLER,ACTION,MENU_ICON,PARENT_ID,MENU_LEVEL,PROJECT_ID,MODULE_ID,MENU_TYPE,MENU_GROUP,MENU_WIDTH,MENU_TAG,MENU_ORDER,ACTIVE) VALUES (N'VEHICLE_APPROVAL_PERSONAL',N'Duyệt đăng ký xe',N'',N'Hrm',N'Vehicle_Approval',N'',N'HRM_CHE_DO_PERSONAL',2,N'HRM',N'HRM_INFO',N'SUB',N'PERSONAL',0,N'',6,1) 
GO

---26--10--2019----
GO
ALTER TABLE JOB_TRAN_CONFIG
ADD LIST_EMPLOYEE_ID nvarchar(320) not null default('')
GO
GO
ALTER TABLE JOB_TRAN_CONFIG
ADD LIST_EMPLOYEE_ID_CHECK nvarchar(320) not null default('')
GO
GO
ALTER TABLE JOB_TRAN_CONFIG
ADD LIST_EMPLOYEE_ID_APPROVED nvarchar(320) not null default('')
GO
GO
CREATE TABLE [dbo].[HR_PLAN_OVERTIME_NEW](
	[PR_KEY] [uniqueidentifier] NOT NULL,
	[EMPLOYEE_ID] [nvarchar](20) NOT NULL,
	[PR_ORGANIZATION_ID] [nvarchar](20) NOT NULL,
	[SHIFT_ID] [nvarchar](20) NOT NULL,
	[START_DATE] [smalldatetime] NOT NULL,
	[END_DATE] [smalldatetime] NOT NULL,
	[TIME_IN] [smalldatetime] NOT NULL,
	[OVER_TIME_REG] [money] NOT NULL,
	[TIME_OUT] [smalldatetime] NOT NULL,
	[RATE_OT] [money] NOT NULL,
	[COMMENT] [nvarchar](100) NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[MODIFIED_DATE] [smalldatetime] NOT NULL,
	[JOB_TRAN_ID] [nvarchar](30) NOT NULL,
	[JOB_TRAN_STATUS_ID] [nvarchar](30) NOT NULL,
	[EMPLOYEE_ID_CHECK] [nvarchar](30) NOT NULL,
	[EMPLOYEE_ID_APPROVED] [nvarchar](30) NOT NULL,
 CONSTRAINT [PK_HR_PLAN_OVERTIME_NEW] PRIMARY KEY CLUSTERED 
(
	[PR_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO




GO
 INSERT INTO SYS_MENU_WEB (MENU_ID,MENU_TITLE,SEF_URL,CONTROLLER,ACTION,MENU_ICON,PARENT_ID,MENU_LEVEL,PROJECT_ID,MODULE_ID,MENU_TYPE,MENU_GROUP,MENU_WIDTH,MENU_TAG,MENU_ORDER,ACTIVE) VALUES (N'OVERTIME_PERSONAL',N'Đăng ký tăng ca',N'',N'Hrm',N'OverTime',N'',N'HRM_PR_TIMEKEEPING_PERSONAL',2,N'HRM',N'HRM_TIME',N'SUB',N'PERSONAL',0,N'',11,1) 
GO
 INSERT INTO SYS_MENU_WEB (MENU_ID,MENU_TITLE,SEF_URL,CONTROLLER,ACTION,MENU_ICON,PARENT_ID,MENU_LEVEL,PROJECT_ID,MODULE_ID,MENU_TYPE,MENU_GROUP,MENU_WIDTH,MENU_TAG,MENU_ORDER,ACTIVE) VALUES (N'OVERTIME_APPROVAL_PERSONAL',N'Phê duyệt tăng ca',N'',N'Hrm',N'OverTimeApproval',N'',N'HRM_PR_TIMEKEEPING_PERSONAL',2,N'HRM',N'HRM_TIME',N'SUB',N'PERSONAL',0,N'',10,1) 
GO
 INSERT INTO SYS_MENU_WEB (MENU_ID,MENU_TITLE,SEF_URL,CONTROLLER,ACTION,MENU_ICON,PARENT_ID,MENU_LEVEL,PROJECT_ID,MODULE_ID,MENU_TYPE,MENU_GROUP,MENU_WIDTH,MENU_TAG,MENU_ORDER,ACTIVE) VALUES (N'OVERTIME_APPROVAL_MANAGE',N'Phê duyệt tăng ca',N'',N'Hrm_Manage',N'OverTime',N'',N'HRM_PR_TIMEKEEPING',2,N'HRM',N'HRM_TIME',N'SUB',N'MANAGE',0,N'',8,1) 
GO


GO
ALTER TABLE [dbo].[PR_DUTY_RATE] DROP CONSTRAINT [PK_PR_DUTY_RATE]
GO

ALTER TABLE [dbo].[PR_DUTY_RATE] ADD  CONSTRAINT [PK_PR_DUTY_RATE_1] PRIMARY KEY CLUSTERED 
(
	[EFFECTIVE_DATE] DESC,
	[EMPLOYEE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

-- 27/10 --
GO
INSERT INTO SYS_MENU
           ([MENU_ID]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_ICON]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
           ('HR_EMPLOYEE_CONTACT_FULL','HRM','HRM_INFO','LIST','HRMTSGROUP1','',80,'HR_EMPLOYEE_CONTACT_FULL',20,1)
GO

-- 31/10  --
GO
alter table PR_EMPLOYEE_SALARY add INDEX_ROW_EM smallint DEFAULT 0 NOT NULL 
GO
GO
CREATE TABLE [dbo].[SEC_USER_API](
	[ORGANIZATION_ID] [nvarchar](20) NOT NULL,
	[USER_ID] [nvarchar](20) NOT NULL,
	[USER_NAME] [nvarchar](200) NOT NULL,
	[PASSWORD] [nvarchar](50) NOT NULL,
	[EMAIL] [nvarchar](300) NOT NULL,
	[TEL] [nvarchar](100) NOT NULL,
	[REFERENCE_ID] [nvarchar](20) NOT NULL,
	[REFERENCE_DETAIL_ID] [nvarchar](20) NOT NULL,
	[ACTIVE] [smallint] NOT NULL,
	[CREATE_USER_ID] [nvarchar](20) NOT NULL,
	[CHANGE_PASSWORD] [int] NOT NULL,
	[LOGIN_DATE] [smalldatetime] NOT NULL,
	[QUANTITY_INVALID] [int] NOT NULL,
	[OTP1] [nvarchar](10) NOT NULL,
	[OTP2] [nvarchar](10) NOT NULL,
	[OTP_DATE] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_SEC_USER_API] PRIMARY KEY CLUSTERED 
(
	[USER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--21/10/2019
GO
INSERT INTO SYS_MENU
           ([MENU_ID]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_ICON]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
           ('PR_PIT_DATA','HRM','HRM_SA','FUNC','HRM_SA_1','',85,'PR_PIT_DATA',12,0)
		   
GO
--31/10/2019--
GO
CREATE TABLE [dbo].[PR_EMPLOYEE_SALARY_BASE_ORG](
	[PR_KEY] [uniqueidentifier] NOT NULL,
	[PR_ORGANIZATION_ID] [nvarchar](20) NOT NULL,
	[SHIFT_ID] [nvarchar](20) NOT NULL,
	[EFFECTIVE_DATE] [smalldatetime] NOT NULL,
	[EXPIRATION_DATE] [smalldatetime] NOT NULL,
	[MODIFIED_DATE] [smalldatetime] NOT NULL,
	[S_A1] [money] NOT NULL,
	[S_A2] [money] NOT NULL,
	[S_A3] [money] NOT NULL,
	[S_A4] [money] NOT NULL,
	[S_A5] [money] NOT NULL,
	[S_A6] [money] NOT NULL,
	[S_A7] [money] NOT NULL,
	[S_A8] [money] NOT NULL,
	[S_A9] [money] NOT NULL,
	[S_B1] [money] NOT NULL,
	[S_B2] [money] NOT NULL,
	[S_B3] [money] NOT NULL,
	[S_B4] [money] NOT NULL,
	[S_B5] [money] NOT NULL,
	[S_B6] [money] NOT NULL,
	[S_B7] [money] NOT NULL,
	[S_B8] [money] NOT NULL,
	[S_B9] [money] NOT NULL,
	[COMMENTS] [nvarchar](300) NOT NULL,
	[TIMEKEEPING_TYPE] [nvarchar](50) NOT NULL,
	[LOCAL_NETWORK] [nvarchar](100) NOT NULL,
	[ACTIVE] [SMALLINT] NOT NULL,
	[USER_ID] [NVARCHAR](20) NOT NULL
 CONSTRAINT [PK_PR_EMPLOYEE_SALARY_BASE_ORG] PRIMARY KEY CLUSTERED 
(
	[PR_ORGANIZATION_ID] ASC,
	[SHIFT_ID] ASC,
	[EFFECTIVE_DATE] ASC,
	[TIMEKEEPING_TYPE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO SYS_MENU
           ([MENU_ID]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_ICON]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
           ('PR_EMPLOYEE_SALARY_BASE_ORG','HRM','HRM_SA','FUNC','HRM_SA_1','',85,'PR_EMPLOYEE_SALARY_BASE_ORG',12,1)
		   
GO


--04/11/2019
GO
alter table PR_EMPLOYEE_SALARY_BASE_ORG add IS_PAID_INS SMALLINT DEFAULT 0 NOT NULL 
GO
alter table PR_EMPLOYEE_SALARY_BASE_ORG add IS_SALARY_NET SMALLINT DEFAULT 0 NOT NULL 
GO

---------4/11/2019
GO
 INSERT INTO SYS_MENU_WEB (MENU_ID,MENU_TITLE,SEF_URL,CONTROLLER,ACTION,MENU_ICON,PARENT_ID,MENU_LEVEL,PROJECT_ID,MODULE_ID,MENU_TYPE,MENU_GROUP,MENU_WIDTH,MENU_TAG,MENU_ORDER,ACTIVE) VALUES (N'ASSET_APPROVAL_MANAGE',N'Phê duyệt cấp phát tài sản',N'',N'Hrm_Manage',N'AssetApproval',N'',N'MANAGE_OFM',3,N'HRM',N'HRM_OFM',N'SUB',N'MANAGE',0,N'',1,1) 
GO
 INSERT INTO SYS_MENU_WEB (MENU_ID,MENU_TITLE,SEF_URL,CONTROLLER,ACTION,MENU_ICON,PARENT_ID,MENU_LEVEL,PROJECT_ID,MODULE_ID,MENU_TYPE,MENU_GROUP,MENU_WIDTH,MENU_TAG,MENU_ORDER,ACTIVE) VALUES (N'HRM_ASM_PERSONAL',N'Cấp phát tài sản',N'',N'',N'',N'fa-server',N'',1,N'HRM',N'HRM_ASM',N'MODULE',N'PERSONAL',0,N'',11,1) 
GO
 INSERT INTO SYS_MENU_WEB (MENU_ID,MENU_TITLE,SEF_URL,CONTROLLER,ACTION,MENU_ICON,PARENT_ID,MENU_LEVEL,PROJECT_ID,MODULE_ID,MENU_TYPE,MENU_GROUP,MENU_WIDTH,MENU_TAG,MENU_ORDER,ACTIVE) VALUES (N'ASSET_PERSONAL',N'Đăng ký cấp phát tài sản',N'',N'Hrm',N'Asset',N'',N'HRM_ASM_PERSONAL',2,N'HRM',N'HRM_ASM',N'SUB',N'PERSONAL',0,N'',1,1) 
GO
 INSERT INTO SYS_MENU_WEB (MENU_ID,MENU_TITLE,SEF_URL,CONTROLLER,ACTION,MENU_ICON,PARENT_ID,MENU_LEVEL,PROJECT_ID,MODULE_ID,MENU_TYPE,MENU_GROUP,MENU_WIDTH,MENU_TAG,MENU_ORDER,ACTIVE) VALUES (N'ASSET_APPROVAL_PERSONAL',N'Phê duyệt cấp phát tài sản',N'',N'Hrm',N'AssetApproval',N'',N'HRM_ASM_PERSONAL',2,N'HRM',N'HRM_ASM',N'SUB',N'PERSONAL',0,N'',2,1) 
GO
---07--11--2019-------
GO
alter table HR_VM_REGISTER add PR_ORGANIZATION_ID nvarchar(300) DEFAULT '' NOT NULL 
-- chon phong ban
GO
alter table HR_VM_REGISTER add LIST_EMPLOYEE_ID nvarchar(300) DEFAULT '' NOT NULL 
--danh sach nguon di cung
GO
alter table HR_VM_REGISTER add LEADER_EMPLOYEE_ID nvarchar(20) DEFAULT '' NOT NULL 
--nguoi truong doan
GO
alter table HR_VM_REGISTER add TRIP_AMOUNT money DEFAULT 0 NOT NULL 
--chi chi chuyen di neu di grabm taxi, xe ngoai co the nhap
GO
alter table HR_VM_REGISTER add LIST_JOINT_EMPLOYEE_ID nvarchar(300) DEFAULT '' NOT NULL 
--danh sach nguoi di cung tu don dang ky khac
GO
alter table HR_VM_REGISTER add PR_KEY_JOIN uniqueidentifier DEFAULT null  NULL 
-- key tu don dang ky khac
GO


GO
INSERT INTO SYS_MENU
           ([MENU_ID]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_ICON]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
           ('HR_EMPLOYEE_EDU_FULL','HRM','HRM_INFO','LIST','HRMTSGROUP1','',80,'HR_EMPLOYEE_EDU_FULL',21,1)

GO

INSERT INTO SYS_MENU
           ([MENU_ID]
           ,[PROJECT_ID]
           ,[MODULE_ID]
           ,[MENU_TYPE]
           ,[MENU_GROUP]
           ,[MENU_ICON]
           ,[MENU_WIDTH]
           ,[MENU_TAG]
           ,[MENU_ORDER]
           ,[ACTIVE])
     VALUES
           ('HR_EMPLOYEE_CERTIFICATION_FULL','HRM','HRM_INFO','LIST','HRMTSGROUP1','',80,'HR_EMPLOYEE_CERTIFICATION_FULL',22,1)
GO

----08/11/2019-------
GO
ALTER TABLE DM_ORGANIZATION ADD LOGO_FILE nvarchar(50) not null default ''
GO


----11/11/2019-------
GO
ALTER TABLE SYS_GRIDINFO_CONFIG_WEB
ADD EDITOR_TYPE nvarchar(50) not null default('')
GO
ALTER TABLE SYS_GRIDINFO_CONFIG_WEB
ADD EDITOR nvarchar(100) not null default('')
GO
ALTER TABLE SYS_GRIDINFO_CONFIG_WEB
ADD IS_GROUP bit not null default((0)) 
GO 
GO 
ALTER TABLE SYS_GRIDINFO_CONFIG_WEB
ADD IS_READONLY bit not null default((0)) 
GO
ALTER TABLE SYS_GRIDINFO_CONFIG_WEB
ADD IS_SHOW_NAME bit not null default((0)) 
GO  
-----14---11--2009---
GO
INSERT INTO [SYS_SYSTEMVAR]
           ([VAR_NAME]
           ,[VAR_VALUE]
           ,[DESCRIPTION]
           ,[VAR_TYPE]
           ,[VAR_GROUP])
     VALUES
           ('APIFUNCTION_HR_INFO',
           'SyncEmployeeToEmployeeList',
           N'API lấy số liệu Hồ sơ nhân sự',
           'STRING',
           'HRM')
GO

---------11/11/2019
GO
ALTER TABLE SYS_GRIDINFO_CONFIG_WEB
ADD EDITOR_TYPE nvarchar(50) not null default('')
GO
ALTER TABLE SYS_GRIDINFO_CONFIG_WEB
ADD EDITOR nvarchar(100) not null default('')
GO
ALTER TABLE SYS_GRIDINFO_CONFIG_WEB
ADD IS_GROUP smallint not null default((0)) 
GO 
ALTER TABLE SYS_GRIDINFO_CONFIG_WEB
ADD IS_READONLY smallint not null default((0)) 
GO
ALTER TABLE SYS_GRIDINFO_CONFIG_WEB
ADD IS_SHOW_NAME smallint not null default((0)) 
GO
 INSERT INTO SYS_MENU_WEB (MENU_ID,MENU_TITLE,SEF_URL,CONTROLLER,ACTION,MENU_ICON,PARENT_ID,MENU_LEVEL,PROJECT_ID,MODULE_ID,MENU_TYPE,MENU_GROUP,MENU_WIDTH,MENU_TAG,MENU_ORDER,ACTIVE) VALUES (N'HRM_ASM_MANAGE',N'Tài sản',N'',N'',N'',N'fa fa-server',N'',1,N'HRM',N'HRM_ASM',N'MODULE',N'MANAGE',0,N'',6,1) 
GO
 INSERT INTO SYS_MENU_WEB (MENU_ID,MENU_TITLE,SEF_URL,CONTROLLER,ACTION,MENU_ICON,PARENT_ID,MENU_LEVEL,PROJECT_ID,MODULE_ID,MENU_TYPE,MENU_GROUP,MENU_WIDTH,MENU_TAG,MENU_ORDER,ACTIVE) VALUES (N'ASSET_APPROVAL_MANAGE',N'Phê duyệt cấp phát tài sản',N'',N'Hrm_Manage',N'AssetApproval',N'',N'HRM_ASM_MANAGE',3,N'HRM',N'HRM_OFM',N'SUB',N'MANAGE',0,N'',3,1) 
GO
 INSERT INTO SYS_MENU_WEB (MENU_ID,MENU_TITLE,SEF_URL,CONTROLLER,ACTION,MENU_ICON,PARENT_ID,MENU_LEVEL,PROJECT_ID,MODULE_ID,MENU_TYPE,MENU_GROUP,MENU_WIDTH,MENU_TAG,MENU_ORDER,ACTIVE) VALUES (N'DM_HR_ASSET_MANAGE',N'Danh mục vật tư',N'',N'Hrm_DM',N'Dm_Hr_Asset',N'',N'HRM_ASM_MANAGE',2,N'HRM',N'HRM_ASM',N'SUB',N'MANAGE',0,N'',1,1) 
GO
 INSERT INTO SYS_MENU_WEB (MENU_ID,MENU_TITLE,SEF_URL,CONTROLLER,ACTION,MENU_ICON,PARENT_ID,MENU_LEVEL,PROJECT_ID,MODULE_ID,MENU_TYPE,MENU_GROUP,MENU_WIDTH,MENU_TAG,MENU_ORDER,ACTIVE) VALUES (N'DM_UNIT_MANAGE',N'Danh mục đơn vị tính',N'',N'Hrm_DM',N'Dm_Unit',N'',N'HRM_ASM_MANAGE',2,N'HRM',N'HRM_ASM',N'SUB',N'MANAGE',0,N'',2,1) 
GO
 INSERT INTO SYS_MENU_WEB (MENU_ID,MENU_TITLE,SEF_URL,CONTROLLER,ACTION,MENU_ICON,PARENT_ID,MENU_LEVEL,PROJECT_ID,MODULE_ID,MENU_TYPE,MENU_GROUP,MENU_WIDTH,MENU_TAG,MENU_ORDER,ACTIVE) VALUES (N'LEAVE_HISTORY_PERSONAL',N'Lịch sử đăng ký chế độ',N'',N'Hrm',N'LeaveHistory',N'',N'HRM_CHE_DO_PERSONAL',2,N'HRM',N'HRM_INFO',N'SUB',N'PERSONAL',0,N'',1,1) 
GO













---ENDSCRIPT-----


























----VIEW-----------------------------------------------------------------------------------------------
GO

DROP VIEW [dbo].[HR_EMPLOYEE_INFO_VIEW]
GO
CREATE VIEW [dbo].[HR_EMPLOYEE_INFO_VIEW]
AS
SELECT A.EMPLOYEE_ID, A.FIRST_NAME, A.LAST_NAME, A.EMPLOYEE_NAME, A.SEX, A.DOB, A.BIRTH_PLACE, A.ORIGIN_PLACE, A.ORIGIN_CITY, A.IDENTITY_NO, A.IDENTITY_ISSUE_DATE, A.IDENTITY_ISSUE_PLACE, A.PASSPORT_NO, 
                  A.PASSPORT_ISSUE_DATE, A.PASSPORT_ISSUE_PLACE, A.PASSPORT_EXPIRE_DATE, A.SOCIAL_BOOK_NO, A.SOCIAL_BOOK_ISSUE_DATE, A.SOCIAL_BOOK_ISSUE_PLACE, A.BANK_ACCOUNT_NO, A.BANK_NAME, 
                  A.BANK_BRANCH, A.MARITAL_STATUS_ID, A.FAMILY_ORIGIN_ID, A.EMPLOYEE_ORIGIN_ID, A.ETHNICS_ID, A.RELIGION_ID, A.NATIONALITY_ID, A.HOME_PHONE, A.WORK_PHONE, A.MOBILE_PHONE, A.PERSONAL_EMAIL, 
                  A.WORK_EMAIL, A.YAHOO_ID, A.SKYPE_ID, A.PERSONAL_TAXCODE, A.PERSONAL_TAXCODE_DATE, A.PERSONAL_TAXCODE_PLACE, A.WORK_DATE_START, A.DATE_COMPANY, A.DATE_GROUP_COMPANY, A.ACCEPT_STATUS, 
                  A.COMMENTS_ACCEPT, A.EMPLOYEE_NAME_OTHER, A.CANDIDATES_ID, A.ORGANIZATION_ID, A.START_DATE_POSITION, A.START_DATE_STATUS, A.START_DATE_CONTRACT, A.EMPLOYEE_STATUS_ID, A.CONTRACT_TYPE_ID, 
                  A.INFO_EXTRA_STRING_1, A.INFO_EXTRA_STRING_2, A.INFO_EXTRA_STRING_3, A.REC_CHANNEL_ID, A.REC_CHANNEL_DETAIL, A.EMPLOYEE_INTRODUCED_ID, A.ACTIVE, A.IDENTITY_ISSUE_PLACE_EN, 
                  A.IDENTITY_ISSUE_PLACE_OTHER, A.PASSPORT_TYPE, A.HI_BOOK_NO, A.PROVINCE_ID, A.HOSPITAL_ID, A.HI_START_DATE, A.HI_EXPIRE_DATE, A.PASS_WORK_EMAIL, A.WORD_PERMIT_NO, A.WORD_PERMIT_ISSUE_DATE, 
                  A.WORD_PERMIT_ISSUE_PLACE, A.WORD_PERMIT_EXPIRE_DATE, A.YEAR_BIRTH, A.INFO_EXTRA_STRING_4, A.INFO_EXTRA_STRING_5, A.P_ADDRESS, A.P_ADDRESS_EN, A.P_COUNTRY_ID, A.P_PROVINCE_ID, A.P_DISTRICT_ID, 
                  A.P_COMMUNES_ID, A.P_HAMLET, A.T_ADDRESS, A.T_ADDRESS_EN, A.T_COUNTRY_ID, A.T_PROVINCE_ID, A.T_DISTRICT_ID, A.T_COMMUNES_ID, A.T_HAMLET, A.PROFILE_NO, A.CABINET_ID, A.PROFILE_STATUS, 
                  A.PROFILE_COMMENTS, A.PR_ORGANIZATION_ID_NOW, A.VISA_ISSUE_DATE, A.VISA_EXPIRE_DATE, A.WORK_PERMIT_DATE, A.TEMPORARY_RESIDENCE_CAR_EXPIRE_DATE, A.INFO_EXTRA_STRING_6, A.INFO_EXTRA_STRING_7, 
                  A.INFO_EXTRA_STRING_8, A.INFO_EXTRA_STRING_9, A.EMPLOYEE_INTRODUCED_AMOUNT, A.START_DATE_STATUS AS EMPLOYEE_STATUS_DATE, dbo.HR_EMPLOYEE_OTHER.ENROLL_NUMBER, 
                  C.END_DATE AS END_DATE_CONTRACT, C.COMMENTS AS CONTRACT_COMMENTS, C.CONTRACT_DATE, C.CONTRACT_NO, C.CONTRACT_PERIOD, C.DUTY_RATE, DM_C.CONTRACT_TYPE_NAME, DM_C.CONTRACT_TYPE_NAME_EN, 
                  DM_C.CONTRACT_TYPE_NAME_OTHER, P.PR_ORGANIZATION_ID, P.POSITION_ID, P.CAREER_ID, P.COMMENTS AS POSITION_COMMENTS, P.DESC_OF_WORK, P.END_DATE, P.LINE_MANAGER_ID, P.POS_DECISION_DATE, 
                  P.POS_DECISION_NO, P.EMPLOYEE_LEVEL_ID, dbo.DM_POSITION.GROUP_POSITION_ID, dbo.DM_POSITION.POSITION_NAME, dbo.DM_POSITION.POSITION_NAME_EN, dbo.DM_POSITION.POSITION_NAME_OTHER, 
                  dbo.DM_POSITION.POSITION_INDEX_VIEW, DM_S.EMPLOYEE_STATUS_NAME, DM_S.EMPLOYEE_STATUS_NAME_EN, DM_S.EMPLOYEE_STATUS_NAME_OTHER, dbo.DM_HR_GROUP_POSITION.GROUP_POSITION_NAME, 
                  dbo.DM_HR_GROUP_POSITION.GROUP_POSITION_NAME_EN, dbo.DM_HR_GROUP_POSITION.GROUP_POSITION_NAME_OTHER, dbo.DM_EMPLOYEE_LEVEL.EMPLOYEE_LEVEL_NAME, 
                  dbo.DM_EMPLOYEE_LEVEL.EMPLOYEE_LEVEL_NAME_EN, dbo.DM_EMPLOYEE_LEVEL.EMPLOYEE_LEVEL_NAME_OTHER, dbo.DM_HR_CAREER.CAREER_NAME, dbo.DM_HR_CAREER.CAREER_NAME_EN, 
                  dbo.DM_HR_CAREER.CAREER_NAME_OTHER, DM_ORG.PR_ORGANIZATION_NAME, DM_ORG.PR_ORGANIZATION_NAME_EN, DM_ORG.PR_ORGANIZATION_NAME_OTHER, 
                  DM_ORG.HR_LIST_PARENT_ORGANIZATION_ID AS HR_PARENT_ORGANIZATION_ID, DM_ORG.HR_LIST_CHILD_ORGANIZATION_ID, ORG.ORGANIZATION_NAME, DATEDIFF(YEAR, A.DOB, GETDATE()) AS YEARS, DATEADD(Year, 
                  DATEPART(Year, GETDATE()) - DATEPART(Year, A.DOB), A.DOB) AS DOB_CURRENT_YEAR, DATEDIFF(mm, A.DATE_COMPANY, GETDATE()) AS NUM_MONTH, CAST(DATEDIFF(mm, A.DATE_COMPANY, GETDATE()) 
                  / CONVERT(DECIMAL(9, 2), 12) AS NUMERIC(18, 1)) AS NUM_YEAR, CONVERT(NVARCHAR(50), DATEDIFF(mm, A.DATE_COMPANY, GETDATE()) / 12) + ' ' + N' năm ' + ' ' + CONVERT(NVARCHAR(50), DATEDIFF(mm, A.DATE_COMPANY, 
                  GETDATE()) - DATEDIFF(mm, A.DATE_COMPANY, GETDATE()) / 12 * 12) + ' ' + N' tháng ' AS STRING_SENIORITY, CONVERT(NVARCHAR(50), (DATEDIFF(mm, A.DATE_COMPANY, GETDATE()) + 1) / 12) 
                  + ' ' + N' năm ' + ' ' + CONVERT(NVARCHAR(50), (DATEDIFF(mm, A.DATE_COMPANY, GETDATE()) + 1) - (DATEDIFF(mm, A.DATE_COMPANY, GETDATE()) + 1) / 12 * 12) + ' ' + N' tháng ' AS STRING_SENIORITY_NN, 
                  '' AS LEAVE_REASON_ID
FROM     dbo.HR_EMPLOYEE_INFO AS A LEFT OUTER JOIN
                  dbo.HR_EMPLOYEE_POSITION AS P ON P.IS_CURRENT = 1 AND A.EMPLOYEE_ID = P.EMPLOYEE_ID AND A.START_DATE_POSITION = P.START_DATE LEFT OUTER JOIN
                  dbo.HR_EMPLOYEE_CONTRACT AS C ON A.EMPLOYEE_ID = C.EMPLOYEE_ID AND A.START_DATE_CONTRACT = C.START_DATE LEFT OUTER JOIN
                  dbo.HR_EMPLOYEE_OTHER ON A.EMPLOYEE_ID = dbo.HR_EMPLOYEE_OTHER.EMPLOYEE_ID LEFT OUTER JOIN
                  dbo.DM_EMPLOYEE_STATUS AS DM_S ON A.EMPLOYEE_STATUS_ID = DM_S.EMPLOYEE_STATUS_ID LEFT OUTER JOIN
                  dbo.DM_CONTRACT_TYPE AS DM_C ON C.CONTRACT_TYPE_ID = DM_C.CONTRACT_TYPE_ID LEFT OUTER JOIN
                  dbo.DM_POSITION ON P.POSITION_ID = dbo.DM_POSITION.POSITION_ID LEFT OUTER JOIN
                  dbo.DM_HR_GROUP_POSITION ON dbo.DM_POSITION.GROUP_POSITION_ID = dbo.DM_HR_GROUP_POSITION.GROUP_POSITION_ID LEFT OUTER JOIN
                  dbo.DM_HR_CAREER ON P.CAREER_ID = dbo.DM_HR_CAREER.CAREER_ID LEFT OUTER JOIN
                  dbo.DM_EMPLOYEE_LEVEL ON P.EMPLOYEE_LEVEL_ID = dbo.DM_EMPLOYEE_LEVEL.EMPLOYEE_LEVEL_ID LEFT OUTER JOIN
                  dbo.DM_PR_HR_ORGANIZATION AS DM_ORG ON P.PR_ORGANIZATION_ID = DM_ORG.PR_ORGANIZATION_ID LEFT OUTER JOIN
                  dbo.DM_ORGANIZATION AS ORG ON DM_ORG.ORGANIZATION_ID = ORG.ORGANIZATION_ID
WHERE  (A.START_DATE_STATUS <= GETDATE()) AND (A.START_DATE_POSITION <= GETDATE()) AND (A.START_DATE_CONTRACT <= GETDATE())
GO


-- 06/12 --
GO
DROP PROCEDURE [dbo].[Pro_Load_Full_Employee_Info_V2]
GO

 CREATE PROCEDURE  [dbo].[Pro_Load_Full_Employee_Info_V2] 	@ACCEP_STATUS nvarchar(50) AS BEGIN 	SET NOCOUNT ON; 	
select A.*, DATEADD( Year, DATEPART( Year, GETDATE()) - DATEPART( Year, A.DOB), A.DOB) as DOB_CURRENT_YEAR,
A.START_DATE_STATUS as EMPLOYEE_STATUS_DATE,HR_EMPLOYEE_OTHER.ENROLL_NUMBER, 
 C.END_DATE as END_DATE_CONTRACT, C.COMMENTS as CONTRACT_COMMENTS, 
 C.CONTRACT_DATE,C.CONTRACT_NO,C.CONTRACT_PERIOD,C.DUTY_RATE,
 DM_C.CONTRACT_TYPE_NAME, DM_C.CONTRACT_TYPE_NAME_EN, DM_C.CONTRACT_TYPE_NAME_OTHER,  
 P.PR_ORGANIZATION_ID,P.POSITION_ID,P.CAREER_ID, P.COMMENTS as POSITION_COMMENTS,  
 P.DESC_OF_WORK,P.END_DATE,P.LINE_MANAGER_ID,P.POS_DECISION_DATE,P.POS_DECISION_NO,   
 P.EMPLOYEE_LEVEL_ID,  Info2.EMPLOYEE_NAME as LINE_MANAGER_NAME,
 DM_POSITION.GROUP_POSITION_ID, DM_POSITION.POSITION_NAME,DM_POSITION.POSITION_NAME_EN, DM_POSITION.POSITION_NAME_OTHER, 
 DM_POSITION.POSITION_INDEX_VIEW,  
 DM_S.EMPLOYEE_STATUS_NAME, DM_S.EMPLOYEE_STATUS_NAME_EN, DM_S.EMPLOYEE_STATUS_NAME_OTHER,  
 DM_HR_GROUP_POSITION.GROUP_POSITION_NAME, DM_HR_GROUP_POSITION.GROUP_POSITION_NAME_EN, DM_HR_GROUP_POSITION.GROUP_POSITION_NAME_OTHER,  
 DM_EMPLOYEE_LEVEL.EMPLOYEE_LEVEL_NAME, DM_EMPLOYEE_LEVEL.EMPLOYEE_LEVEL_NAME_EN, DM_EMPLOYEE_LEVEL.EMPLOYEE_LEVEL_NAME_OTHER,  
 DM_HR_CAREER.CAREER_NAME, DM_HR_CAREER.CAREER_NAME_EN, DM_HR_CAREER.CAREER_NAME_OTHER,  
 DM_ORG.PR_ORGANIZATION_NAME, DM_ORG.PR_ORGANIZATION_NAME_EN, DM_ORG.PR_ORGANIZATION_NAME_OTHER, 
 DM_ORG.HR_LIST_PARENT_ORGANIZATION_ID AS HR_PARENT_ORGANIZATION_ID, DM_ORG.HR_LIST_CHILD_ORGANIZATION_ID, ORG.ORGANIZATION_NAME, 
 DM_MARITAL_STATUS.MARITAL_STATUS_NAME, DM_MARITAL_STATUS.MARITAL_STATUS_NAME_EN, 

 DATEDIFF(YEAR, A.DOB, GETDATE()) AS YEARS, 
 DATEADD( Year, DATEPART( Year, GETDATE()) - DATEPART( Year, A.DOB), A.DOB) as DOB_CURRENT_YEAR,  

 DATEDIFF(mm,A.DATE_COMPANY,GETDATE()) as NUM_MONTH, CAST((DATEDIFF(mm,A.DATE_COMPANY,GETDATE())/(CONVERT(DECIMAL(9,2),12))) AS NUMERIC(18,1)) as NUM_YEAR,  

 CONVERT(NVARCHAR(50),DATEDIFF(mm,A.DATE_COMPANY,GETDATE())/12)+' '+N' năm '+' '+CONVERT(NVARCHAR(50),
 DATEDIFF(mm,A.DATE_COMPANY,GETDATE())-(DATEDIFF(mm,A.DATE_COMPANY,GETDATE())/12)*12)+' '+N' tháng ' as STRING_SENIORITY, 

 CONVERT(NVARCHAR(50),(DATEDIFF(mm,A.DATE_COMPANY,GETDATE())+1)/12)+' '+N' năm '+' '+CONVERT(NVARCHAR(50),
 ((DATEDIFF(mm,A.DATE_COMPANY,GETDATE())+1) -((DATEDIFF(mm,A.DATE_COMPANY,GETDATE())+1)/12)*12 ))+' '+N' tháng ' as STRING_SENIORITY_NN ,

 '' as LEAVE_REASON_ID ,CAST( null as smalldatetime)ACP_DAY_START,CAST( null as smalldatetime)ACP_DAY_END, EDU.*
 from HR_EMPLOYEE_INFO A  

 LEFT JOIN HR_EMPLOYEE_POSITION P      ON P.IS_CURRENT =1 AND A.EMPLOYEE_ID = P.EMPLOYEE_ID AND A.START_DATE_POSITION = P.START_DATE  
 LEFT JOIN HR_EMPLOYEE_CONTRACT C      ON A.EMPLOYEE_ID = C.EMPLOYEE_ID AND A.START_DATE_CONTRACT = C.START_DATE  
 LEFT JOIN HR_EMPLOYEE_OTHER           ON A.EMPLOYEE_ID = HR_EMPLOYEE_OTHER.EMPLOYEE_ID 
 LEFT JOIN HR_EMPLOYEE_INFO Info2	   ON P.LINE_MANAGER_ID = Info2.EMPLOYEE_ID

 LEFT JOIN DM_EMPLOYEE_STATUS DM_S     ON A.EMPLOYEE_STATUS_ID = DM_S.EMPLOYEE_STATUS_ID 
 LEFT JOIN DM_ORGANIZATION ORG 	       ON A.ORGANIZATION_ID = ORG.ORGANIZATION_ID   
 LEFT JOIN DM_CONTRACT_TYPE DM_C       ON C.CONTRACT_TYPE_ID = DM_C.CONTRACT_TYPE_ID   
 LEFT JOIN DM_POSITION                 ON P.POSITION_ID = DM_POSITION.POSITION_ID   
 LEFT JOIN DM_HR_GROUP_POSITION        ON DM_POSITION.GROUP_POSITION_ID = DM_HR_GROUP_POSITION.GROUP_POSITION_ID 
 LEFT JOIN DM_HR_CAREER                ON P.CAREER_ID = DM_HR_CAREER.CAREER_ID   
 LEFT JOIN DM_EMPLOYEE_LEVEL           ON P.EMPLOYEE_LEVEL_ID = DM_EMPLOYEE_LEVEL.EMPLOYEE_LEVEL_ID  
 LEFT JOIN DM_PR_HR_ORGANIZATION DM_ORG ON P.PR_ORGANIZATION_ID = DM_ORG.PR_ORGANIZATION_ID   
 LEFT JOIN DM_MARITAL_STATUS		   ON A.MARITAL_STATUS_ID = DM_MARITAL_STATUS.MARITAL_STATUS_ID 

 
 LEFT JOIN (select HR_EMPLOYEE_EDUCATION.EMPLOYEE_ID,EDUCATION_ID, RANK_MAX,SCHOOL_NAME,SPECIALIZATION, SPECIALIZATION_ID, HR_UNIVERSITY_ID from HR_EMPLOYEE_EDUCATION 
 Inner join (select EMPLOYEE_ID, MAX(RANK) RANK_MAX from HR_EMPLOYEE_EDUCATION where EDUCATION_ID != '' group by EMPLOYEE_ID ) EDU_MAX 
 on HR_EMPLOYEE_EDUCATION.EMPLOYEE_ID = EDU_MAX.EMPLOYEE_ID and HR_EMPLOYEE_EDUCATION.RANK = EDU_MAX.RANK_MAX ) EDU
 ON A.EMPLOYEE_ID = EDU.EMPLOYEE_ID 

 where A.ACCEPT_STATUS = @ACCEP_STATUS
 and A.START_DATE_STATUS <= GETDATE() and A.START_DATE_POSITION <= GETDATE() AND A.START_DATE_CONTRACT <= GETDATE()  
 ORDER BY DM_POSITION.POSITION_INDEX_VIEW,A.EMPLOYEE_ID 
END
GO

GO
ALTER PROCEDURE  [dbo].[Pro_Load_Quick_Employee_Info_With_Status]
	@ACCEP_STATUS nvarchar(50) ,
	@EMPLOYEE_STATUS_ID nvarchar(20)
AS
BEGIN

	SET NOCOUNT ON;
	select A.EMPLOYEE_ID,A.EMPLOYEE_NAME,A.DATE_COMPANY,A.EMPLOYEE_STATUS_ID,A.CONTRACT_TYPE_ID,
	 A.DOB,A.SEX,A.IDENTITY_NO,A.PASSPORT_NO,A.PERSONAL_TAXCODE,A.BANK_ACCOUNT_NO,A.WORK_EMAIL,A.WORK_PHONE,A.MOBILE_PHONE,
	 DATEADD( Year, DATEPART( Year, GETDATE()) - DATEPART( Year, A.DOB), A.DOB) as DOB_CURRENT_YEAR,
	 HR_EMPLOYEE_OTHER.ENROLL_NUMBER,HR_EMPLOYEE_OTHER.IS_CHECK_TIMEWORK,
	 P.PR_ORGANIZATION_ID,P.POSITION_ID,P.EMPLOYEE_LEVEL_ID,P.CAREER_ID,
	 DM_ORG.ORGANIZATION_ID, DM_ORG.PR_ORGANIZATION_NAME, DM_ORG.HR_LIST_PARENT_ORGANIZATION_ID as HR_PARENT_ORGANIZATION_ID ,
	 DM_POSITION.POSITION_NAME, DM_POSITION.POSITION_INDEX_VIEW, ORG.ORGANIZATION_NAME,
	 A.START_DATE_POSITION, P.END_DATE as END_DATE_POSITION, 
	 A.START_DATE_CONTRACT, C.END_DATE as END_DATE_CONTRACT, 
	 A.START_DATE_STATUS
	 from HR_EMPLOYEE_INFO A  
	 LEFT JOIN HR_EMPLOYEE_POSITION P      ON P.IS_CURRENT =1 AND A.EMPLOYEE_ID = P.EMPLOYEE_ID AND A.START_DATE_POSITION = P.START_DATE  
	 LEFT JOIN HR_EMPLOYEE_CONTRACT C      ON A.EMPLOYEE_ID = C.EMPLOYEE_ID AND A.START_DATE_CONTRACT = C.START_DATE  
	 LEFT JOIN HR_EMPLOYEE_OTHER           ON A.EMPLOYEE_ID = HR_EMPLOYEE_OTHER.EMPLOYEE_ID 
     LEFT JOIN DM_ORGANIZATION ORG 	       ON A.ORGANIZATION_ID = ORG.ORGANIZATION_ID  
	 LEFT JOIN DM_POSITION                 ON P.POSITION_ID = DM_POSITION.POSITION_ID   
	 LEFT JOIN DM_PR_HR_ORGANIZATION DM_ORG ON P.PR_ORGANIZATION_ID = DM_ORG.PR_ORGANIZATION_ID   
	  
	 where A.EMPLOYEE_STATUS_ID = @EMPLOYEE_STATUS_ID AND A.ACCEPT_STATUS = @ACCEP_STATUS and A.START_DATE_STATUS <= GETDATE()
	ORDER BY P.PR_ORGANIZATION_ID,A.EMPLOYEE_ID 
END

GO

ALTER PROCEDURE  [dbo].[Pro_Load_Quick_Employee_Info_With_Status_And_StartDate]
	@ACCEP_STATUS nvarchar(50) ,
	@EMPLOYEE_STATUS_ID nvarchar(20) ,
	@START_DATE SMALLDATETIME 
AS
BEGIN
	SET NOCOUNT ON;
	select A.EMPLOYEE_ID,A.EMPLOYEE_NAME,A.DATE_COMPANY,S.EMPLOYEE_STATUS_ID,C.CONTRACT_TYPE_ID,
	 A.DOB,A.SEX,A.IDENTITY_NO,A.PASSPORT_NO,A.PERSONAL_TAXCODE,A.BANK_ACCOUNT_NO,A.WORK_EMAIL,A.WORK_PHONE,A.MOBILE_PHONE,
	 DATEADD( Year, DATEPART( Year, GETDATE()) - DATEPART( Year, A.DOB), A.DOB) as DOB_CURRENT_YEAR,
	 HR_EMPLOYEE_OTHER.ENROLL_NUMBER,HR_EMPLOYEE_OTHER.IS_CHECK_TIMEWORK,
	 P.PR_ORGANIZATION_ID,P.POSITION_ID,P.EMPLOYEE_LEVEL_ID,P.CAREER_ID,
	 DM_ORG.ORGANIZATION_ID,DM_ORG.PR_ORGANIZATION_NAME, DM_ORG.HR_LIST_PARENT_ORGANIZATION_ID as HR_PARENT_ORGANIZATION_ID ,
	 DM_POSITION.POSITION_NAME,DM_POSITION.POSITION_INDEX_VIEW , ORG.ORGANIZATION_NAME,
	 P.START_DATE as START_DATE_POSITION,P.END_DATE as END_DATE_POSITION,
	 C.START_DATE as START_DATE_CONTRACT,C.END_DATE as END_DATE_CONTRACT,
	 S.EMPLOYEE_STATUS_DATE as START_DATE_STATUS
         from HR_EMPLOYEE_INFO A  
         LEFT JOIN HR_EMPLOYEE_OTHER		ON A.EMPLOYEE_ID=HR_EMPLOYEE_OTHER.EMPLOYEE_ID  
         LEFT JOIN HR_EMPLOYEE_STATUS S		ON A.EMPLOYEE_ID=S.EMPLOYEE_ID         
         LEFT JOIN HR_EMPLOYEE_POSITION P	ON A.EMPLOYEE_ID=P.EMPLOYEE_ID and P.IS_CURRENT = 1 
         LEFT JOIN HR_EMPLOYEE_CONTRACT C	ON A.EMPLOYEE_ID=C.EMPLOYEE_ID   
         LEFT JOIN DM_PR_HR_ORGANIZATION DM_ORG ON P.PR_ORGANIZATION_ID=DM_ORG.PR_ORGANIZATION_ID 
	 LEFT JOIN DM_ORGANIZATION ORG 		ON DM_ORG.ORGANIZATION_ID=ORG.ORGANIZATION_ID 
         LEFT JOIN DM_POSITION			ON P.POSITION_ID=DM_POSITION.POSITION_ID  
         INNER JOIN    
         (  
         SELECT EMPLOYEE_ID, MAX(EMPLOYEE_STATUS_DATE) AS EMPLOYEE_STATUS_DATE    
         FROM dbo.HR_EMPLOYEE_STATUS where HR_EMPLOYEE_STATUS.EMPLOYEE_STATUS_DATE <=  @START_DATE
         GROUP BY EMPLOYEE_ID  
         ) TBLSTATUS  
         ON S.EMPLOYEE_ID = TBLSTATUS.EMPLOYEE_ID and S.EMPLOYEE_STATUS_DATE=TBLSTATUS.EMPLOYEE_STATUS_DATE      
         INNER JOIN  
         ( 
         SELECT EMPLOYEE_ID, MAX([START_DATE]) AS [START_DATE] FROM dbo.HR_EMPLOYEE_POSITION     
         where  HR_EMPLOYEE_POSITION.[START_DATE] <= @START_DATE AND HR_EMPLOYEE_POSITION.IS_CURRENT =1 
         GROUP BY EMPLOYEE_ID  
         ) TBLPOSITION  
         ON P.EMPLOYEE_ID = TBLPOSITION.EMPLOYEE_ID and P.[START_DATE]=TBLPOSITION.[START_DATE]    
         INNER JOIN    
         (  
         SELECT EMPLOYEE_ID, MAX([START_DATE]) AS EMPLOYEE_CONTRACT_DATE FROM dbo.HR_EMPLOYEE_CONTRACT    
         where HR_EMPLOYEE_CONTRACT.[START_DATE]<= @START_DATE
         GROUP BY EMPLOYEE_ID  
         ) TBLCONTRACT    
         ON C.EMPLOYEE_ID = TBLCONTRACT.EMPLOYEE_ID and C.[START_DATE] = TBLCONTRACT.EMPLOYEE_CONTRACT_DATE    
         where  S.EMPLOYEE_STATUS_ID = @EMPLOYEE_STATUS_ID AND A.ACCEPT_STATUS = @ACCEP_STATUS
		ORDER BY P.PR_ORGANIZATION_ID,A.EMPLOYEE_ID 

END