Register Menu

USE a2isauthorizationdb
GO
BEGIN TRANSACTION
/*
READ THIS FIRST!
================
Before executing this script, please check on the current database is there an exists data or
please remember is this script has been executed before
*/

DECLARE @APPID int, @MENUID1 int, @MENUID2 int, @MENUID3 int, @MENUID4 int, @ROLEID1 int
/* change the number if numbers exists, please set into a non exists number, sequently*/
SET @APPID = 1
SET @MENUID1 = 96
SET @MENUID2 = 97
SET @MENUID3 = 98
SET @MENUID4 = 99
SET @ROLEID1 = 921

--REGISTRASI APPLICATION MENUS
BEGIN
IF NOT EXISTS (SELECT 1 FROM General.ApplicationMenus WHERE MenuName = 'VEHICLEMAIN' And ApplicationID = @APPID)
BEGIN
INSERT General.ApplicationMenus (ApplicationID, MenuID, MenuName, MenuCaption, ParentMenuID, OrderingNo, ApplicationInfo, IsActive, CreatedBy, CreatedDate, UpdateBy, UpdateDate, RowStatus)
VALUES (@APPID, @MENUID1, N'VEHICLEMAIN', N'Vehicle', 0, 0, N'', 1, N'NKW', GETDATE(), NULL, NULL, 0)
END
ELSE
BEGIN
SELECT @MENUID1 = MenuID FROM General.ApplicationMenus WHERE MenuName = 'VEHICLEMAIN' And ApplicationID = @APPID
END

IF NOT EXISTS (SELECT 1 FROM General.ApplicationMenus WHERE MenuName = 'VEHICLEMAINTENANCE' And ApplicationID = @APPID)
BEGIN
INSERT General.ApplicationMenus (ApplicationID, MenuID, MenuName, MenuCaption, ParentMenuID, OrderingNo, ApplicationInfo, IsActive, CreatedBy, CreatedDate, UpdateBy, UpdateDate, RowStatus)
VALUES (@APPID, @MENUID2, N'VEHICLEMAINTENANCE', N'Vehicle Maintenance', @MENUID1, 0, N'VehicleMaintenance/Index', 1, N'NKW', GETDATE(), NULL, NULL, 0)
END
ELSE
BEGIN
SELECT @MENUID2 = MenuID FROM General.ApplicationMenus WHERE MenuName = 'VEHICLEMAINTENANCE' And ApplicationID = @APPID
END

IF NOT EXISTS (SELECT 1 FROM General.ApplicationMenus WHERE MenuName = 'VEHICLEMARKETPRICE' And ApplicationID = @APPID)
BEGIN
INSERT General.ApplicationMenus (ApplicationID, MenuID, MenuName, MenuCaption, ParentMenuID, OrderingNo, ApplicationInfo, IsActive, CreatedBy, CreatedDate, UpdateBy, UpdateDate, RowStatus)
VALUES (@APPID, @MENUID3, N'VEHICLEMARKETPRICE', N'Vehicle Market Price', @MENUID1, 1, N'VehiclePricePerModel/Index', 1, N'NKW', GETDATE(), NULL, NULL, 0)
END
ELSE
BEGIN
SELECT @MENUID3 = MenuID FROM General.ApplicationMenus WHERE MenuName = 'VEHICLEMARKETPRICE' And ApplicationID = @APPID
END

IF NOT EXISTS (SELECT 1 FROM General.ApplicationMenus WHERE MenuName = 'VEHICLEUPLOADPRICE' And ApplicationID = @APPID)
BEGIN
INSERT General.ApplicationMenus (ApplicationID, MenuID, MenuName, MenuCaption, ParentMenuID, OrderingNo, ApplicationInfo, IsActive, CreatedBy, CreatedDate, UpdateBy, UpdateDate, RowStatus)
VALUES (@APPID, @MENUID4, N'VEHICLEUPLOADPRICE', N'Upload Price', @MENUID1, 2, N'VehicleUploadPrice/Index', 1, N'NKW', GETDATE(), NULL, NULL, 0)
END
ELSE
BEGIN
SELECT @MENUID4 = MenuID FROM General.ApplicationMenus WHERE MenuName = 'VEHICLEUPLOADPRICE' And ApplicationID = @APPID
END
END
----REGISTRASI ROLE
BEGIN
IF NOT EXISTS (SELECT 1 FROM General.Roles WHERE RoleCode='VEHICLEMP')
BEGIN
INSERT INTO General.Roles (RoleID, RoleCode, RoleName, IsActive, CreatedBy, CreatedDate, UpdateBy, UpdateDate, RowStatus)
VALUES (@ROLEID1, 'VEHICLEMP', 'Vehicle Market Price', 1, 'NKW', GETDATE(), NULL, NULL, 0)
END
ELSE
BEGIN
SELECT @ROLEID1 = RoleID FROM General.Roles WHERE RoleCode='VEHICLEMP'
END
END

----REGISTRASI APPLICATION ROLE
BEGIN
IF NOT EXISTS (SELECT 1 FROM General.ApplicationRoles WHERE ApplicationID = @APPID AND RoleID = @ROLEID1)
BEGIN
INSERT INTO General.ApplicationRoles (ApplicationID, RoleID, IsActive, CreatedBy, CreatedDate, UpdateBy, UpdateDate, RowStatus)
VALUES (@APPID, @ROLEID1, 1, 'NKW', GETDATE(), NULL, NULL, 0)
END
END

----REGISTRASI APPLICATION ROLE MENU
BEGIN
IF NOT EXISTS (SELECT 1 FROM General.ApplicationRoleMenus WHERE MENUID = @MENUID1 AND APPLICATIONID = @APPID)
BEGIN
INSERT INTO General.ApplicationRoleMenus (ApplicationID, RoleID, MenuID, IsAddEnabled, IsEditEnabled, IsRetrieveEnabled, IsRemoveEnabled, WorkflowID, IsActive, CreatedBy, CreatedDate, UpdateBy, UpdateDate, RowStatus)
VALUES (@APPID, @ROLEID1, @MENUID1, 1, 1, 1, 1, '', 1, 'NKW', GETDATE(), NULL, NULL, 0)
END

IF NOT EXISTS (SELECT 1 FROM General.ApplicationRoleMenus WHERE MENUID = @MENUID2 AND APPLICATIONID = @APPID)
BEGIN
INSERT INTO General.ApplicationRoleMenus (ApplicationID, RoleID, MenuID, IsAddEnabled, IsEditEnabled, IsRetrieveEnabled, IsRemoveEnabled, WorkflowID, IsActive, CreatedBy, CreatedDate, UpdateBy, UpdateDate, RowStatus)
VALUES (@APPID, @ROLEID1, @MENUID2, 1, 1, 1, 1, '', 1, 'NKW', GETDATE(), NULL, NULL, 0)
END

IF NOT EXISTS (SELECT 1 FROM General.ApplicationRoleMenus WHERE MENUID = @MENUID3 AND APPLICATIONID = @APPID)
BEGIN
INSERT INTO General.ApplicationRoleMenus (ApplicationID, RoleID, MenuID, IsAddEnabled, IsEditEnabled, IsRetrieveEnabled, IsRemoveEnabled, WorkflowID, IsActive, CreatedBy, CreatedDate, UpdateBy, UpdateDate, RowStatus)
VALUES (@APPID, @ROLEID1, @MENUID3, 1, 1, 1, 1, '', 1, 'NKW', GETDATE(), NULL, NULL, 0)
END

IF NOT EXISTS (SELECT 1 FROM General.ApplicationRoleMenus WHERE MENUID = @MENUID4 AND APPLICATIONID = @APPID)
BEGIN
INSERT INTO General.ApplicationRoleMenus (ApplicationID, RoleID, MenuID, IsAddEnabled, IsEditEnabled, IsRetrieveEnabled, IsRemoveEnabled, WorkflowID, IsActive, CreatedBy, CreatedDate, UpdateBy, UpdateDate, RowStatus)
VALUES (@APPID, @ROLEID1, @MENUID4, 1, 1, 1, 1, '', 1, 'NKW', GETDATE(), NULL, NULL, 0)
END
END

----REGISTRASI USER
BEGIN
IF NOT EXISTS (SELECT 1 FROM General.Users WHERE UserID = 'HKU')
BEGIN
INSERT INTO General.Users(UserID, Counter, UserName, CreatedBy, CreatedDate, UpdateBy, UpdateDate)
VALUES ('HKU', 0, 'Hendra Kusnandar', 'NKW', GETDATE(), NULL, NULL)
END
IF NOT EXISTS (SELECT 1 FROM General.Users WHERE UserID = 'ESR')
BEGIN
INSERT INTO General.Users(UserID, Counter, UserName, CreatedBy, CreatedDate, UpdateBy, UpdateDate)
VALUES ('ESR', 0, 'Edy Suryono', 'NKW', GETDATE(), NULL, NULL)
END
END

----REGISTRASI USER APPLICATION ROLES
BEGIN
IF NOT EXISTS (SELECT 1 FROM General.UserApplicationRoles WHERE ApplicationID = @APPID AND RoleID = @ROLEID1 AND UserID ='HKU')
BEGIN
INSERT INTO General.UserApplicationRoles (UserID, Counter, ApplicationID, RoleID, CreatedBy, CreatedDate, UpdateBy, UpdateDate, RowStatus)
VALUES ('HKU', 0, @APPID, @ROLEID1, 'NKW', GETDATE(), NULL, NULL, 0)
END
END

BEGIN
IF NOT EXISTS (SELECT 1 FROM General.UserApplicationRoles WHERE ApplicationID = @APPID AND RoleID = @ROLEID1 AND UserID ='ESR')
BEGIN
INSERT INTO General.UserApplicationRoles (UserID, Counter, ApplicationID, RoleID, CreatedBy, CreatedDate, UpdateBy, UpdateDate, RowStatus)
VALUES ('ESR', 0, @APPID, @ROLEID1, 'NKW', GETDATE(), NULL, NULL, 0)
END
END

DECLARE @IsTesting INT
SET @IsTesting = 1 -- Ubah menjadi 0 jika testing berhasil dan jalankan kembali
IF @IsTesting = 1
BEGIN
Select 'ROLLBACK'
ROLLBACK TRAN
END
ELSE
BEGIN
SELECT 'COMMIT'
COMMIT
END