text: ﻿/*
  Deployment script for DBIOT
  
  This code was generated by a tool.
  Changes to this file may cause incorrect behavior and will be lost if
  the code is regenerated.
  */
  
  GO
  SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
  
  SET NUMERIC_ROUNDABORT OFF;
  
  
  GO
  :setvar DatabaseName "DBIOT"
  :setvar DefaultFilePrefix "DBIOT"
  :setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\"
  :setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\"
  
  GO
  :on error exit
  GO
  /*
  Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
  To re-enable the script after enabling SQLCMD mode, execute the following:
  SET NOEXEC OFF; 
  */
  :setvar __IsSqlCmdEnabled "True"
  GO
  IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
      BEGIN
          PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
          SET NOEXEC ON;
      END
  
  
  GO
  USE [$(DatabaseName)];
  
  
  GO
  IF EXISTS (SELECT 1
             FROM   [master].[dbo].[sysdatabases]
             WHERE  [name] = N'$(DatabaseName)')
      BEGIN
          ALTER DATABASE [$(DatabaseName)]
              SET ANSI_NULLS OFF,
                  ANSI_PADDING OFF,
                  ANSI_WARNINGS OFF,
                  ARITHABORT OFF,
                  CONCAT_NULL_YIELDS_NULL OFF,
                  QUOTED_IDENTIFIER OFF,
                  ANSI_NULL_DEFAULT OFF,
                  CURSOR_DEFAULT GLOBAL 
              WITH ROLLBACK IMMEDIATE;
      END
  
  
  GO
  PRINT N'Creating [dbo].[DataMenu]...';
  
  
  GO
  CREATE TABLE [dbo].[DataMenu] (
      [idMenu]     BIGINT         IDENTITY (1, 1) NOT NULL,
      [NamaMenu]   NVARCHAR (MAX) NULL,
      [Action]     NVARCHAR (MAX) NULL,
      [Controller] NVARCHAR (MAX) NULL,
      [Platform]   NVARCHAR (MAX) NULL,
      [Img]        NVARCHAR (MAX) NULL,
      [Status]     BIGINT         NOT NULL
  );
  
  
  GO
  PRINT N'Creating [dbo].[DataModule]...';
  
  
  GO
  CREATE TABLE [dbo].[DataModule] (
      [IdModul]    BIGINT         IDENTITY (1, 1) NOT NULL,
      [NamaModule] NVARCHAR (MAX) NULL,
      [Action]     NVARCHAR (MAX) NULL,
      [Controller] NVARCHAR (MAX) NULL,
      [Img]        NVARCHAR (MAX) NULL,
      [Status]     INT            NULL
  );
  
  
  GO
  PRINT N'Creating [dbo].[GroupData]...';
  
  
  GO
  SET QUOTED_IDENTIFIER ON;
  
  SET ANSI_NULLS OFF;
  
  
  GO
  CREATE TABLE [dbo].[GroupData] (
      [IdRole]    BIGINT         IDENTITY (1, 1) NOT NULL,
      [NamaGroup] NVARCHAR (MAX) NULL,
      [Status]    BIGINT         NULL,
      [Desc]      NVARCHAR (MAX) NULL,
      PRIMARY KEY CLUSTERED ([IdRole] ASC)
  );
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[GroupData_MenuAkses]...';
  
  
  GO
  SET QUOTED_IDENTIFIER ON;
  
  SET ANSI_NULLS OFF;
  
  
  GO
  CREATE TABLE [dbo].[GroupData_MenuAkses] (
      [Id]     INT    IDENTITY (1, 1) NOT NULL,
      [IdRole] BIGINT NULL,
      [IdMenu] BIGINT NULL,
      PRIMARY KEY CLUSTERED ([Id] ASC)
  );
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[LogActivityUser]...';
  
  
  GO
  CREATE TABLE [dbo].[LogActivityUser] (
      [Id]        BIGINT         IDENTITY (1, 1) NOT NULL,
      [NamaTable] NVARCHAR (MAX) NULL,
      [Message]   NVARCHAR (MAX) NULL,
      [IdRow]     BIGINT         NULL,
      [Action]    NVARCHAR (MAX) NULL,
      [UserBy]    NVARCHAR (MAX) NULL,
      [Datetime]  NVARCHAR (MAX) NULL,
      CONSTRAINT [PK_LogActivityUser] PRIMARY KEY CLUSTERED ([Id] ASC)
  );
  
  
  GO
  PRINT N'Creating [dbo].[LogErrorMessage]...';
  
  
  GO
  CREATE TABLE [dbo].[LogErrorMessage] (
      [logId]        BIGINT         IDENTITY (1, 1) NOT NULL,
      [MessageError] NVARCHAR (MAX) NULL,
      [FunctionName] NVARCHAR (MAX) NULL,
      [Parameter]    NVARCHAR (MAX) NULL,
      [Datetime]     NVARCHAR (MAX) NULL,
      [Username]     NVARCHAR (MAX) NULL
  );
  
  
  GO
  PRINT N'Creating [dbo].[Master_Form]...';
  
  
  GO
  SET QUOTED_IDENTIFIER ON;
  
  SET ANSI_NULLS OFF;
  
  
  GO
  CREATE TABLE [dbo].[Master_Form] (
      [idLog]      BIGINT         IDENTITY (1, 1) NOT NULL,
      [NamaForm]   NVARCHAR (MAX) NOT NULL,
      [Type]       NVARCHAR (MAX) NULL,
      [Id]         NVARCHAR (MAX) NULL,
      [TextLabel]  NVARCHAR (MAX) NULL,
      [Action]     NVARCHAR (MAX) NULL,
      [Controller] NVARCHAR (MAX) NULL,
      [ValueInput] NVARCHAR (MAX) NULL,
      [ListModel]  NVARCHAR (MAX) NULL,
      [Urutan]     INT            NOT NULL,
      [ShowHide]   NVARCHAR (MAX) NULL,
      [ReadOnly]   NVARCHAR (MAX) NULL,
      [Enable]     NVARCHAR (MAX) NULL,
      [Mandatory]  NVARCHAR (MAX) NULL,
      [IsNumber]   INT            NULL,
      [FilterBy]   INT            NOT NULL,
      CONSTRAINT [PK_Master_Form] PRIMARY KEY CLUSTERED ([idLog] ASC)
  );
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[Master_ListItem]...';
  
  
  GO
  SET QUOTED_IDENTIFIER ON;
  
  SET ANSI_NULLS OFF;
  
  
  GO
  CREATE TABLE [dbo].[Master_ListItem] (
      [id]       BIGINT         IDENTITY (1, 1) NOT NULL,
      [ListName] NVARCHAR (MAX) NOT NULL,
      [Urutan]   NVARCHAR (MAX) NULL,
      [Text]     NVARCHAR (MAX) NULL,
      [Value]    NVARCHAR (MAX) NULL
  );
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[Role_MenuTree]...';
  
  
  GO
  CREATE TABLE [dbo].[Role_MenuTree] (
      [IdRole]   BIGINT IDENTITY (1, 1) NOT NULL,
      [IdModule] BIGINT NOT NULL,
      [Posisi]   BIGINT NOT NULL,
      [IdParent] BIGINT NOT NULL,
      [Urutan]   BIGINT NOT NULL,
      [IdMenu]   BIGINT NOT NULL
  );
  
  
  GO
  PRINT N'Creating [dbo].[UserData]...';
  
  
  GO
  CREATE TABLE [dbo].[UserData] (
      [id]          BIGINT          IDENTITY (1, 1) NOT NULL,
      [username]    NVARCHAR (MAX)  NULL,
      [password]    VARBINARY (200) NULL,
      [RoleId]      BIGINT          NULL,
      [NamaLengkap] NVARCHAR (MAX)  NULL,
      [Photo]       NVARCHAR (MAX)  NULL,
      [Status]      BIGINT          NULL
  );
  
  
  GO
  PRINT N'Creating [dbo].[DF_DataMenu_Status]...';
  
  
  GO
  ALTER TABLE [dbo].[DataMenu]
      ADD CONSTRAINT [DF_DataMenu_Status] DEFAULT ((1)) FOR [Status];
  
  
  GO
  PRINT N'Creating unnamed constraint on [dbo].[Master_Form]...';
  
  
  GO
  ALTER TABLE [dbo].[Master_Form]
      ADD DEFAULT ((0)) FOR [Urutan];
  
  
  GO
  PRINT N'Creating [dbo].[DF_Master_Form_FilterBy]...';
  
  
  GO
  ALTER TABLE [dbo].[Master_Form]
      ADD CONSTRAINT [DF_Master_Form_FilterBy] DEFAULT ((0)) FOR [FilterBy];
  
  
  GO
  PRINT N'Creating [dbo].[DF_Role_MenuTree_IdParent]...';
  
  
  GO
  ALTER TABLE [dbo].[Role_MenuTree]
      ADD CONSTRAINT [DF_Role_MenuTree_IdParent] DEFAULT ((0)) FOR [IdParent];
  
  
  GO
  PRINT N'Creating [dbo].[DF_Role_MenuTree_Urutan]...';
  
  
  GO
  ALTER TABLE [dbo].[Role_MenuTree]
      ADD CONSTRAINT [DF_Role_MenuTree_Urutan] DEFAULT ((0)) FOR [Urutan];
  
  
  GO
  PRINT N'Creating [dbo].[Roman]...';
  
  
  GO
  create function [dbo].[Roman] (
    @arabic int
  ) returns varchar(30) as begin
    if @arabic > 10000 return '*'
    declare @roman varchar(30)
    set @roman = replicate('M',@arabic/1000)
    set @arabic = @arabic%1000
    set @roman = @roman + replicate('C',@arabic/100)
    set @arabic = @arabic%100
    set @roman = @roman + replicate('X',@arabic/10)
    set @arabic = @arabic%10
    set @roman = @roman + replicate('I',@arabic)
  
    set @roman = replace(@roman,replicate('C',9),'CM')
    set @roman = replace(@roman,replicate('X',9),'XC')
    set @roman = replace(@roman,replicate('I',9),'IX')
  
    set @roman = replace(@roman,replicate('C',5),'D')
    set @roman = replace(@roman,replicate('X',5),'L')
    set @roman = replace(@roman,replicate('I',5),'V')
  
    set @roman = replace(@roman,replicate('C',4),'CD')
    set @roman = replace(@roman,replicate('X',4),'XL')
    set @roman = replace(@roman,replicate('I',4),'IV')
  
    return @roman
  end
  GO
  PRINT N'Creating [dbo].[TRIM]...';
  
  
  GO
  CREATE FUNCTION dbo.TRIM(@String VARCHAR(MAX))
  RETURNS VARCHAR(MAX)
  BEGIN
      RETURN LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@String,CHAR(10),'[]'),CHAR(13),'[]'),char(9),'[]'),CHAR(32),'[]'),'][',''),'[]',CHAR(32))));
  END
  GO
  PRINT N'Creating [dbo].[SP_FormData_Del]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_FormData_Del]
  	@Id bigint
  AS
  declare @Title nvarchar(max)
  declare @Message nvarchar(max)
  declare @MStatus nvarchar(max)
  
  if exists(select*from Master_Form where idLog = @Id)
  begin
  	declare @NamaForm nvarchar(max)
  	declare @IdObj nvarchar(max)
  
  	select @IdObj=Id, @NamaForm=NamaForm  from Master_Form where idLog=@Id
  	
  	delete from Master_Form where idLog=@Id
  
  	set @Title = 'Success'
  	set @Message = 'Data '+ @NamaForm + ' dengan ID Object : '+@IdObj+' berhasil dihapus'
  	set @MStatus = 'success'
  end
  else
  begin
  	set @Title = 'Sorry'
  	set @Message = 'data not exists'
  	set @MStatus = 'error'
  end
  
  select @Title Title, @Message Message, @MStatus Status, @Id Id
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_FormData_Get]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_FormData_Get]
  AS
  	select*from Master_Form
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_FormData_GetById]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_FormData_GetById]
  	@Id bigint
  AS
  	select*from Master_Form
  	where idLog = @Id
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_FormData_GetSearch]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_FormData_GetSearch]
  	@NamaForm nvarchar(max)
  AS
  
  
  select*from Master_Form
  where 
  	REPLACE(RTRIM(LTRIM(NamaForm)),' ','') like '%'+ REPLACE(RTRIM(LTRIM(@NamaForm)),' ','')+'%'
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_FormData_Save]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_FormData_Save]
  	@idLog bigint,
  	@NamaForm nvarchar(max),
  	@Type nvarchar(max),
  	@Id nvarchar(max),
  	@TextLabel nvarchar(max),
  	@Action nvarchar(max),
  	@Controller nvarchar(max),
  	@ValueInput nvarchar(max),
  	@ListModel nvarchar(max),
  	@Urutan bigint,
  	@ShowHide nvarchar(max),
  	@ReadOnly nvarchar(max),
  	@Enable nvarchar(max),
  	@Mandatory nvarchar(max),
  	@IsNumber bigint,
  	@FilterBy bigint
  AS
  
  declare @Title nvarchar(max)
  declare @Message nvarchar(max)
  declare @MStatus nvarchar(max)
  
  declare @IdS bigint
  set @IdS = 0
  if(@idLog = 0)
  begin
  	if not exists(select*from Master_Form where NamaForm = @NamaForm and Id = @Id)
  	begin
  		insert into Master_Form
  		(
  			NamaForm,
  			Type,
  			Id,
  			TextLabel,
  			Action,
  			Controller,
  			ValueInput,
  			ListModel,
  			Urutan,
  			ShowHide,
  			ReadOnly,
  			Enable,
  			Mandatory,
  			IsNumber,
  			FilterBy
  		)
  		values 
  		(
  			@NamaForm,
  			@Type,
  			@Id,
  			@TextLabel,
  			@Action,
  			@Controller,
  			@ValueInput,
  			@ListModel,
  			@Urutan,
  			@ShowHide,
  			@ReadOnly,
  			@Enable,
  			@Mandatory,
  			@IsNumber,
  			@FilterBy
  		)
  
  		set @Id = (select idLog from Master_Form where idLog = SCOPE_IDENTITY())
  		set @Title = 'Success'
  		set @Message = 'Penambahan Element Form '+ @NamaForm + ' dengan object :'+ @Id +' berhasil dibuat'
  		set @MStatus = 'success'
  	end
  	else
  	begin
  		set @Title = 'Sorry'
  		set @Message = 'Data Element Form '+ @NamaForm + ' dengan object :'+ @Id +' already exists'
  		set @MStatus = 'error'
  	end
  end
  else
  begin
  	declare @namaObject_sebelum nvarchar(max)
  	set @namaObject_sebelum = (select Id from Master_Form where idLog=@idLog)
  	if(@namaObject_sebelum = @Id)
  	begin
  		update Master_Form
  		set 
  			NamaForm=@NamaForm,
  			Type=@Type,
  			Id=@Id,
  			TextLabel=@TextLabel,
  			Action=@Action,
  			Controller=@Controller,
  			ValueInput=@ValueInput,
  			ListModel=@ListModel,
  			Urutan=@Urutan,
  			ShowHide=@ShowHide,
  			ReadOnly=@ReadOnly,
  			Enable=@Enable,
  			Mandatory=@Mandatory,
  			IsNumber=@IsNumber,
  			FilterBy=@FilterBy
  		where 
  		idLog=@idLog
  
  		set @Id = @idLog
  		set @Title = 'Success'
  		set @Message = 'Data Element Form '+ @NamaForm + ' dengan object :'+ @Id +' berhasil diupdate'
  		set @MStatus = 'success'
  
  	end
  	else
  	begin
  		declare @idmodule_sama bigint
  		if((select count(*) from Master_Form where NamaForm=@NamaForm and Id=@Id) > 0)
  		begin
  			set @Id = @idLog
  			set @Title = 'Sorry'
  			set @Message = 'Data Element Form '+ @NamaForm + ' dengan object :'+ @Id +' already exists, silahkan cari nama lain'
  			set @MStatus = 'error'
  		end
  		else
  		begin
  			update Master_Form
  			set 
  				NamaForm=@NamaForm,
  				Type=@Type,
  				Id=@Id,
  				TextLabel=@TextLabel,
  				Action=@Action,
  				Controller=@Controller,
  				ValueInput=@ValueInput,
  				ListModel=@ListModel,
  				Urutan=@Urutan,
  				ShowHide=@ShowHide,
  				ReadOnly=@ReadOnly,
  				Enable=@Enable,
  				Mandatory=@Mandatory,
  				IsNumber=@IsNumber,
  				FilterBy=@FilterBy
  			where 
  			idLog=@idLog
  
  			set @Id = @idLog
  			set @Title = 'Success'
  			set @Message = 'Data Element Form '+ @NamaForm + ' dengan object :'+ @Id +' berhasil diupdate'
  			set @MStatus = 'success'
  		end
  	end
  
  end
  
  
  select @Title Title, @Message Message, @MStatus Status, @Id Id
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_Get_Module]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_Get_Module]
  	@param1 int = 0,
  	@param2 int
  AS
  	SELECT @param1, @param2
  RETURN 0
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_GetDateTime]...';
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_GetDateTime]
  AS
  	SELECT FORMAT(GETDATE() , 'dd/MM/yyyy HH:mm:ss') as tanggal
  GO
  PRINT N'Creating [dbo].[SP_GetDiffDays]...';
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_GetDiffDays]
  	@Date1 nvarchar(20),
  	@Date2 nvarchar(20)
  AS
  	SELECT DATEDIFF(DAY, 
  	replace(CONVERT(VARCHAR(10), CONVERT(date, @Date1, 105), 23),'-',''), 
  	replace(CONVERT(VARCHAR(10), CONVERT(date, @Date2, 105), 23),'-','')) Total
  GO
  PRINT N'Creating [dbo].[SP_GetListDataMaster]...';
  
  
  GO
  CREATE PROCEDURE SP_GetListDataMaster
  	@Data nvarchar(max)
  AS
  BEGIN
  	SET NOCOUNT ON;
  	if exists(select*from dbo.Master_ListItem where ListName = @Data)
  	begin
  		select*from dbo.Master_ListItem where ListName = @Data
  		order by Urutan asc
  	end
  	else
  	begin
  		if(@Data ='ListMasterModule')
  		begin
              select*from 
  			(
  				select '' Text, 0 Value
  				union all
  				select NamaModule Text, IdModul Value from DataModule where Status = 1
  			) q
  			order by q.Text
  		end
  		
          else if(@Data = 'ListPlatform')
  		begin
  			
  			select*from 
  			(
  				select '' Text, '' Value
  				union all
                  select distinct Platform Text, Platform Value from DataMenu
  			) q
  			where Text is not null
  			order by q.Text
  		end
          else if(@Data ='ListMasterModuleFilter')
  		begin
              select*from 
  			(
  				select '' Text, '' Value
  				union all
  				select NamaModule Text, NamaModule Value from DataModule where Status = 1
  			) q
  			order by q.Text
  		end
          else if(@Data ='ListMainMenu')
  		begin
              select*from 
  			(
  				select '' Text, '' Value
  				union all
  				select NamaMenu Text, NamaMenu Value from DataMenu where idMenu IN
  				(
  					select IdMenu from Role_MenuTree where IdRole in
  					(select IdParent from Role_MenuTree where IdParent != 0)
  				)
  			) q
  			order by q.Text
  		end
  		
  		else if(@Data ='ListModule')
  		begin
              select*from 
  			(
  				select '' Text, 0 Value
  				union all
  
  				select
  				NamaModule Text,IdModul Value
  				from DataModule
  
  			) q
  			order by q.Text
  		end
  		else if(@Data ='ListMenu')
  		begin
              select*from 
  			(
  				select '' Text, 0 Value
  				union all
  
  				select
  				NamaMenu Text,idMenu Value
  				from DataMenu
  
  			) q
  			order by q.Text
  		end
  		else if(@Data ='RoleAkses')
  		begin
              select*from 
  			(
  				select '' Text, 0 Value
  				union all
  				select
  				NamaGroup Text,IdRole Value
  				from GroupData
  
  			) q
  			order by q.Text
  		end
  		
  		else if(@Data ='ListNamaPenyewa')
  		begin
  			select*from 
  			(
  				select '' Text, 0 Value
  				union all
  				select
  				NamaLengkap Text,id Value
  				from UserData
  			) q
  			order by q.Text
  		end
  	end
  END
  GO
  PRINT N'Creating [dbo].[SP_GetMenuByAkses]...';
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_GetMenuByAkses]
  	@IdUser bigint,	
  	@IdModule bigint,
  	@Posisi bigint
  AS
  if(@Posisi = 1)
  begin
  	select*from DataMenu x
  	left join Role_MenuTree b on b.IdMenu = x.idMenu
  	where x.idMenu in
  	(
  		select xx.IdMenu from Role_MenuTree xx
  		where IdModule = @IdModule and Posisi = @Posisi
  		and xx.IdMenu in (
  			select distinct case when IdParent = 0 then b.IdMenu else IdParent end as Id
  			from Role_MenuTree b
  			where b.IdMenu in (select a.IdMenu from GroupData_MenuAkses a where IdRole in (select RoleId from UserData where id = @IdUser))
  		)
  	)
  	and Status =1
  	order by b.Urutan asc
  end
  else if(@Posisi >=2)
  begin
  	select x.* from DataMenu x
  	left join Role_MenuTree b on b.IdMenu = x.idMenu
  	where x.idMenu in
  	(
  		select xx.IdMenu from Role_MenuTree xx
  		where Posisi = @Posisi and IdParent = @IdModule
  		and xx.IdMenu in (
  			select distinct case when IdParent > 0 then b.IdMenu else IdParent end as Id
  			from Role_MenuTree b
  			where b.IdMenu in (select a.IdMenu from GroupData_MenuAkses a where IdRole in (select RoleId from UserData where id = @IdUser))
  		)
  	)
  	and Status =1
  	order by b.Urutan asc
  end
  GO
  PRINT N'Creating [dbo].[SP_GetModulByAkses]...';
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_GetModulByAkses]
  	@IdUser bigint
  AS
  	select * from DataModule where IdModul in
  	(
  		select
  		distinct IdModule
  		from Role_MenuTree where IdMenu in (
  			select
  			IdMenu
  			from GroupData_MenuAkses where IdRole in (select RoleId from UserData where id = @IdUser)
  		)
  	) 
  	and Status = 1
  GO
  PRINT N'Creating [dbo].[SP_GetProfileUser]...';
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_GetProfileUser]
  	@IdUser bigint
  AS
  	select
  	*, b.NamaGroup RoleName
  	from UserData a
  	left join GroupData b on b.IdRole = a.RoleId
  	where id =@IdUser
  GO
  PRINT N'Creating [dbo].[SP_GetUserDataMaster]...';
  
  
  GO
  CREATE PROCEDURE SP_GetUserDataMaster
  	@Id bigint,
  	@search nvarchar(max)
  AS
  BEGIN
  	SET NOCOUNT ON;
  	if(@Id = 0)
  	begin
  		select*from UserData where NamaLengkap like '%'+@search+'%'
  	end
  	else if(@Id != 0 and @search = '')
  	begin
  		select*from UserData where id = @Id
  	end
  
  END
  GO
  PRINT N'Creating [dbo].[SP_GroupData_Get]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].SP_GroupData_Get
  AS
  	select*from GroupData
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_ListItemData_Del]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_ListItemData_Del]
  	@Id bigint
  AS
  declare @Title nvarchar(max)
  declare @Message nvarchar(max)
  declare @MStatus nvarchar(max)
  
  if exists(select*from Master_ListItem where id = @Id)
  begin
  	declare @NamaList nvarchar(max)
  	declare @Item nvarchar(max)
  
  	select @Item=Text, @NamaList=ListName  from Master_ListItem where id=@Id
  	
  	delete from Master_ListItem where id=@Id
  
  	set @Title = 'Success'
  	set @Message = 'Data '+ @NamaList + ' dengan Item : '+@Item+' berhasil dihapus'
  	set @MStatus = 'success'
  end
  else
  begin
  	set @Title = 'Sorry'
  	set @Message = 'data not exists'
  	set @MStatus = 'error'
  end
  
  select @Title Title, @Message Message, @MStatus Status, @Id Id
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_ListItemData_Get]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_ListItemData_Get]
  AS
  	select*from Master_ListItem
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_ListItemData_GetById]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_ListItemData_GetById]
  	@Id bigint
  AS
  	select*from Master_ListItem
  	where id = @Id
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_ListItemData_GetSearch]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_ListItemData_GetSearch]
  	
  	@ListName nvarchar(max)
  AS
  	select*from Master_ListItem
  	where 
  	REPLACE(RTRIM(LTRIM(ListName)),' ','') like '%'+ REPLACE(RTRIM(LTRIM(@ListName)),' ','')+'%'
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_ListItemData_Save]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_ListItemData_Save]
  	@id int,
  	@ListName nvarchar(max),
  	@Urutan nvarchar(max),
  	@Text nvarchar(max),
  	@Value nvarchar(max)
  AS
  
  declare @Title nvarchar(max)
  declare @Message nvarchar(max)
  declare @MStatus nvarchar(max)
  
  declare @IdS bigint
  set @IdS = 0
  if(@id = 0)
  begin
  	if not exists(select*from Master_ListItem where ListName = @ListName and Text = @Text)
  	begin
  		insert into Master_ListItem
  		(
  			ListName,
  			Urutan,
  			Text,
  			Value
  		)
  		values 
  		(
  			@ListName,
  			@Urutan,
  			@Text,
  			@Value
  		)
  
  		set @Id = (select id from Master_ListItem where id = SCOPE_IDENTITY())
  		set @Title = 'Success'
  		set @Message = 'Data ListItem '+ @ListName + ' dengan Item :'+ @Text +' berhasil dibuat'
  		set @MStatus = 'success'
  	end
  	else
  	begin
  		set @Title = 'Sorry'
  		set @Message = 'Data ListItem '+ @ListName + ' dengan Item :'+ @Text +' already exists'
  		set @MStatus = 'error'
  	end
  end
  else
  begin
  	declare @item_sebelum nvarchar(max)
  	set @item_sebelum = (select Text from Master_ListItem where id=@id)
  	if(@item_sebelum = @Text)
  	begin
  		update Master_ListItem
  		set 
  			ListName=@ListName,
  			Urutan=@Urutan,
  			Text=@Text,
  			Value=@Value
  		where 
  		id=@id
  
  		set @Title = 'Success'
  		set @Message = 'Data ListItem '+ @ListName + ' dengan Item :'+ @Text +' berhasil diupdate'
  		set @MStatus = 'success'
  
  	end
  	else
  	begin
  		declare @id_sama bigint
  		if((select count(*) from Master_ListItem where ListName = @ListName and Text = @Text) > 0)
  		begin
  			set @Title = 'Sorry'
  			set @Message = 'Data ListItem '+ @ListName + ' dengan Item :'+ @Text +' already exists, silahkan cari nama lain'
  			set @MStatus = 'error'
  		end
  		else
  		begin
  			update Master_ListItem
  			set 
  				ListName=@ListName,
  				Urutan=@Urutan,
  				Text=@Text,
  				Value=@Value
  			where 
  			id=@id
  
  			set @Title = 'Success'
  			set @Message = 'Data ListItem '+ @ListName + ' dengan Item :'+ @Text +' berhasil diupdate'
  			set @MStatus = 'success'
  		end
  	end
  
  end
  
  
  select @Title Title, @Message Message, @MStatus Status, @Id Id
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_LoginProc]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_LoginProc]
  	@Username nvarchar(max),
  	@Password nvarchar(max),
  	@Category nvarchar(max)
  AS
  	
  	if exists(select*from UserData where username = @Username and convert(nvarchar(max),DecryptByPassPhrase('key', password ))=@Password)
  	begin
  		select 
  		'Success' title,
  		'Login Success' message,
  		'success' status
  	end
  	else
  	begin
  		select 
  		'Sorry' title,
  		'Username dan password tidak teregistrasi, mohon dicheck kembali ' message,
  		'error' status
  	end
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_LoginProc_GetID]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_LoginProc_GetID]
  	@Username nvarchar(max),
  	@Password nvarchar(max),
  	@Category nvarchar(max)
  AS
  	
  	select id
  	from UserData where username = @Username and convert(nvarchar(max),DecryptByPassPhrase('key', password ))=@Password
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_MenuData_Del]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_MenuData_Del]
  	@Id bigint
  AS
  declare @Title nvarchar(max)
  declare @Message nvarchar(max)
  declare @MStatus nvarchar(max)
  
  if exists(select*from DataMenu where idMenu = @Id)
  begin
  	declare @NamaMenu nvarchar(max)
  	set @NamaMenu =(select NamaMenu from DataMenu where idMenu=@Id)
  	
  	delete from DataMenu where idMenu = @Id
  
  	set @Title = 'Success'
  	set @Message = 'Menu '+ @NamaMenu + ' berhasil dihapus'
  	set @MStatus = 'success'
  end
  else
  begin
  	set @Title = 'Sorry'
  	set @Message = 'Modul '+ @NamaMenu + ' not exists'
  	set @MStatus = 'error'
  end
  
  select @Title Title, @Message Message, @MStatus Status, @Id Id
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_MenuData_Get]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_MenuData_Get]
  AS
  
  select*from DataMenu
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_MenuData_Get_ById]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_MenuData_Get_ById]
  	@Id bigint
  AS
  	select*from DataMenu
  	where idMenu = @Id
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_MenuData_GetMenuByIdModule]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_MenuData_GetMenuByIdModule]
  	@Id bigint
  AS
  	
  	select*from DataMenu where idMenu in (select IdMenu from Role_MenuTree where IdModule = @Id)
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_MenuData_GetParent]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_MenuData_GetParent]
  	@IdModule bigint, 
  	@IdMenu bigint,
  	@IdPosisi bigint
  AS
  
  	select
  	a.IdMenu Value, b.NamaMenu Text
  	from Role_MenuTree a
  	left join DataMenu b on b.idMenu = a.IdMenu
  	where a.Posisi = (@IdPosisi-1) and a.IdModule = @IdModule
  	and a.IdMenu not in (@IdMenu)
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_MenuData_GetSearch]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_MenuData_GetSearch]
  	@NamaMenu nvarchar(max),
  	@Controller nvarchar(max),
  	@Platform nvarchar(max)
  AS
  if(@Platform !='')
  begin
  	select*from DataMenu
  	where REPLACE(RTRIM(LTRIM(NamaMenu)),' ','') like '%'+REPLACE(RTRIM(LTRIM(@NamaMenu)),' ','')+'%' and REPLACE(RTRIM(LTRIM(Platform)),' ','') = REPLACE(RTRIM(LTRIM(@Platform)),' ','')
  	and REPLACE(RTRIM(LTRIM(Controller)),' ','') like '%'+REPLACE(RTRIM(LTRIM(@Controller)),' ','')+'%'
  end
  else
  begin
  	select*from DataMenu
  	where REPLACE(RTRIM(LTRIM(NamaMenu)),' ','') like '%'+REPLACE(RTRIM(LTRIM(@NamaMenu)),' ','')+'%' 
  	and REPLACE(RTRIM(LTRIM(Controller)),' ','') like '%'+REPLACE(RTRIM(LTRIM(@Controller)),' ','')+'%'
  end
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_ModuleData_Del]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_ModuleData_Del]
  	@Id bigint
  AS
  declare @Title nvarchar(max)
  declare @Message nvarchar(max)
  declare @MStatus nvarchar(max)
  
  if exists(select*from DataModule where IdModul = @Id)
  begin
  	declare @NamaModule nvarchar(max)
  	set @NamaModule =(select NamaModule from DataModule where IdModul=@Id)
  	
  	delete from DataModule where IdModul = @Id
  
  	set @Title = 'Success'
  	set @Message = 'Modul '+ @NamaModule + ' berhasil dihapus'
  	set @MStatus = 'success'
  end
  else
  begin
  	set @Title = 'Sorry'
  	set @Message = 'Modul '+ @NamaModule + ' not exists'
  	set @MStatus = 'error'
  end
  
  select @Title Title, @Message Message, @MStatus Status, @Id Id
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_ModuleData_Get]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_ModuleData_Get]
  AS
  	select top 100 *from DataModule
  RETURN
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_ModuleData_Get_ById]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_ModuleData_Get_ById]
  	@Id bigint
  AS
  	select*from DataModule
  	where IdModul = @Id
  RETURN
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_ModuleData_GetSearch]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  -- exec SP_ModuleData_GetSearch @NamaModule='',@Status=0
  CREATE PROCEDURE [dbo].[SP_ModuleData_GetSearch]
  	@NamaModule nvarchar(max),
  	@Status  bigint
  AS
  	select*from DataModule
  	where NamaModule like '%'+@NamaModule+'%' and Status = @Status
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_ModuleData_Save]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  create PROCEDURE [dbo].[SP_ModuleData_Save]
  	@IdModul bigint,
  	@NamaModule nvarchar(max),
  	@Action nvarchar(max),
  	@Controller nvarchar(max),
  	@Img nvarchar(max),
  	@Status int
  AS
  
  declare @Title nvarchar(max)
  declare @Message nvarchar(max)
  declare @MStatus nvarchar(max)
  
  declare @Id bigint
  set @Id = 0
  if(@IdModul = 0)
  begin
  	if not exists(select*from [dbo].[DataModule] where NamaModule = @NamaModule)
  	begin
  		insert into DataModule
  		(NamaModule,Action,Controller,Img,Status)
  		values (@NamaModule,@Action,@Controller,@Img,@Status)
  		set @Id = (select IdModul from DataModule where IdModul = SCOPE_IDENTITY())
  		set @Title = 'Success'
  		set @Message = 'Penambahan Modul '+ @NamaModule + ' berhasil dibuat'
  		set @MStatus = 'success'
  	end
  	else
  	begin
  		set @Title = 'Sorry'
  		set @Message = 'Modul '+ @NamaModule + ' already exists'
  		set @MStatus = 'error'
  	end
  end
  else
  begin
  	declare @namamodule_sebelum nvarchar(max)
  	set @namamodule_sebelum = (select NamaModule from DataModule where IdModul=@IdModul)
  	if(@namamodule_sebelum = @NamaModule)
  	begin
  		update DataModule
  		set 
  			Action=@Action,
  			Controller = @Controller,
  			Img = @Img,
  			Status= @Status
  		where 
  		IdModul=@IdModul
  
  		set @Id = @IdModul
  		set @Title = 'Success'
  		set @Message = 'Modul '+ @NamaModule + ' berhasil diupdate'
  		set @MStatus = 'success'
  
  	end
  	else
  	begin
  		declare @idmodule_sama bigint
  		if((select count(*) from DataModule where NamaModule= @NamaModule) > 0)
  		begin
  			set @Id = @IdModul
  			set @Title = 'Sorry'
  			set @Message = 'Modul '+ @NamaModule + ' already exists, silahkan cari nama lain'
  			set @MStatus = 'error'
  		end
  		else
  		begin
  			update DataModule
  			set 
  				NamaModule=@NamaModule,
  				Action=@Action,
  				Controller = @Controller,
  				Img = @Img,
  				Status= @Status
  			where 
  			IdModul=@IdModul
  
  			set @Id = @IdModul
  			set @Title = 'Success'
  			set @Message = 'Modul '+ @NamaModule + ' berhasil diupdate'
  			set @MStatus = 'success'
  		end
  	end
  
  end
  
  
  select @Title Title, @Message Message, @MStatus Status, @Id Id
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_RoleGroupAkses_Del]...';
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_RoleGroupAkses_Del]
  		@Id bigint
  AS
  
  declare @Title nvarchar(max)
  declare @Message nvarchar(max)
  declare @MStatus nvarchar(max)
  
  if exists(select*from GroupData where IdRole = @Id)
  begin
  	declare @NamaGroup nvarchar(max)
  
  	select @NamaGroup=NamaGroup  from GroupData where IdRole=@Id
  	
  	delete from GroupData where IdRole=@Id
  
  	set @Title = 'Success'
  	set @Message = 'Data '+ @NamaGroup +' berhasil dihapus'
  	set @MStatus = 'success'
  end
  else
  begin
  	set @Title = 'Sorry'
  	set @Message = 'data not exists'
  	set @MStatus = 'error'
  end
  
  select @Title Title, @Message Message, @MStatus Status, @Id Id
  GO
  PRINT N'Creating [dbo].[SP_RoleGroupAkses_Get]...';
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_RoleGroupAkses_Get]
  AS
  	select*from Role_MenuTree
  GO
  PRINT N'Creating [dbo].[SP_RoleGroupAkses_GetById]...';
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_RoleGroupAkses_GetById]
  	@Id bigint
  AS
  	select*from GroupData
  	where IdRole = @Id
  GO
  PRINT N'Creating [dbo].[SP_RoleGroupAkses_GetSearch]...';
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_RoleGroupAkses_GetSearch]
  	@NamaGroup nvarchar(max)
  AS
  	select*from GroupData
  	where 
  	REPLACE(RTRIM(LTRIM(NamaGroup)),' ','') like '%'+ REPLACE(RTRIM(LTRIM(@NamaGroup)),' ','')+'%'
  GO
  PRINT N'Creating [dbo].[SP_RoleGroupAkses_ListMenu_Get]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_RoleGroupAkses_ListMenu_Get]
  	@id bigint
  AS
  select q.*from 
  (
  	select
  	b.*
  	from GroupData_MenuAkses a
  	left join DataMenu b on b.idMenu = a.IdMenu
  	where IdRole = @id
  ) q
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_RoleGroupAkses_ListMenu_Get_NotInList]...';
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_RoleGroupAkses_ListMenu_Get_NotInList]
  	@id bigint
  AS
  	
  	select*from DataMenu 
  	where idMenu not in
  	(
  		select
  		IdMenu
  		from GroupData_MenuAkses
  		where IdRole = @id
  	)
  	and Action != '0'
  GO
  PRINT N'Creating [dbo].[SP_RoleGroupAkses_ListMeny_Add]...';
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_RoleGroupAkses_ListMeny_Add]
  	@IdRole bigint,
  	@IdMenu bigint
  AS
  	declare @Title nvarchar(max)
  	declare @Message nvarchar(max)
  	declare @MStatus nvarchar(max)
  
  	declare @NamaMenu nvarchar(max)
  	select @NamaMenu=NamaMenu from DataMenu where idMenu = @IdMenu
  
  	if not exists(select*from GroupData_MenuAkses where IdRole = @IdRole and IdMenu = @IdMenu)
  	begin
  		insert into GroupData_MenuAkses
  		(IdRole,IdMenu)
  		values
  		(
  			@IdRole,@IdMenu
  		)
  		set @Title = 'Success'
  		set @Message = 'Data '+ @NamaMenu + ' berhasil dibuat'
  		set @MStatus = 'success'
  	end
  GO
  PRINT N'Creating [dbo].[SP_RoleGroupAkses_Save]...';
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_RoleGroupAkses_Save]
  	@IdRole bigint,
  	@NamaGroup nvarchar(max),
  	@Status bigint,
  	@Desc nvarchar(max)
  AS
  
  declare @Title nvarchar(max)
  declare @Message nvarchar(max)
  declare @MStatus nvarchar(max)
  
  if(@IdRole = 0)
  begin
  	if not exists(select*from GroupData where NamaGroup=@NamaGroup)
  	begin
  		insert into GroupData 
  		(
  			NamaGroup,[Status],[Desc]
  		)
  		values
  		(
  			@NamaGroup,@Status,@Desc
  		)
  		
  		set @IdRole = (select IdRole from GroupData where IdRole = SCOPE_IDENTITY())
  		set @Title = 'Success'
  		set @Message = 'Data '+ @NamaGroup + ' berhasil dibuat'
  		set @MStatus = 'success'
  
  	end
  	else
  	begin
  		set @Title = 'Sorry'
  		set @Message = 'Data '+ @NamaGroup+' already exists'
  		set @MStatus = 'error'
  	end
  end
  else
  begin
  	declare @NamaGroup_sebelum nvarchar(max)
  	select @NamaGroup_sebelum=NamaGroup from GroupData where IdRole = @IdRole
  
  	if(@NamaGroup = @NamaGroup_sebelum)
  	begin
  		update GroupData
  		set 
  			Status = @Status,
  			[Desc] = @Desc
  		where 
  		IdRole = @IdRole
  
  		set @Title = 'Success'
  		set @Message = 'Data '+ @NamaGroup + ' berhasil diupdate'
  		set @MStatus = 'success'
  	end
  	else
  	begin
  		if not exists(select*from GroupData where NamaGroup = @NamaGroup and IdRole not in (@IdRole))
  		begin
  			update GroupData
  			set 
  				Status = @Status,
  				[Desc] = @Desc
  			where 
  			IdRole = @IdRole	
  
  			set @Title = 'Success'
  			set @Message = 'Data '+ @NamaGroup + ' berhasil diupdate'
  			set @MStatus = 'success'
  		end
  		else
  		begin
  			set @Title = 'Sorry'
  			set @Message = 'Data '+ @NamaGroup+' already exists'
  			set @MStatus = 'error'
  		end
  	end
  end
  
  select @Title Title, @Message Message, @MStatus Status, @IdRole Id
  GO
  PRINT N'Creating [dbo].[SP_RoleMenuData_Del]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_RoleMenuData_Del]
  	@Id bigint
  AS
  
  declare @Title nvarchar(max)
  declare @Message nvarchar(max)
  declare @MStatus nvarchar(max)
  
  
  if exists(select*from Role_MenuTree where IdRole = @Id)
  begin
  	
  	declare @NamaModule nvarchar(max) 
  	set @NamaModule = (select NamaModule from DataModule where IdModul in (select IdModule from Role_MenuTree where IdRole=@Id))
  	
  	declare @NamaMenu nvarchar(max) 
  	set @NamaMenu = (select NamaMenu from DataMenu a where a.idMenu in (select x.IdMenu from Role_MenuTree x where IdRole=@Id))
  	
  	delete from Role_MenuTree where IdRole=@Id
  
  	set @Title = 'Success'
  	set @Message = 'Data '+ @NamaModule + ' - '+ @NamaMenu +' berhasil dihapus'
  	set @MStatus = 'success'
  end
  else
  begin
  	set @Title = 'Sorry'
  	set @Message = 'data not exists'
  	set @MStatus = 'error'
  end
  
  select @Title Title, @Message Message, @MStatus Status, @Id Id
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_RoleMenuData_Get]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_RoleMenuData_Get]
  AS
  	select
  	a.*,b.NamaModule,c.NamaMenu,d.NamaPosisi NamaPosisi,(
  			select NamaMenu from Role_MenuTree x
  			left join DataMenu xx on x.idMenu = xx.IdMenu
  			where IdRole=a.IdParent
  		) NamaParent,
  	c.Platform
  	from Role_MenuTree a
  	left join DataModule b on b.IdModul = a.IdModule
  	left join DataMenu c on c.idMenu = a.IdMenu
  	left join (select z.Text NamaPosisi,z.Value Id from Master_ListItem z where ListName='ListPosisiMenu') d on d.Id = a.Posisi
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_RoleMenuData_GetById]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_RoleMenuData_GetById]
  	@Id bigint
  AS
  	select
  	a.*,b.NamaModule,c.NamaMenu,'' NamaPosisi,'' NamaParent,
  	c.Platform
  	from Role_MenuTree a
  	left join DataModule b on b.IdModul = a.IdModule
  	left join DataMenu c on c.idMenu = a.IdMenu
  	where IdRole = @Id
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_RoleMenuData_GetSearch]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_RoleMenuData_GetSearch]
  	--@IdRole bigint,
  	@IdModule bigint,
  	--@Posisi bigint,
  	--@IdParent bigint,
  	--@Urutan bigint,
  	@IdMenu bigint
  AS
  	if(@IdModule=0 and @IdMenu=0)
  	begin
  		
  		select
  		a.*,b.NamaModule,c.NamaMenu,d.NamaPosisi NamaPosisi,
  		(
  			select NamaMenu from DataMenu where idMenu = a.IdParent
  		) NamaParent,
  		c.Platform
  		from Role_MenuTree a
  		left join DataModule b on b.IdModul = a.IdModule
  		left join DataMenu c on c.idMenu = a.IdMenu
  		left join (select Text NamaPosisi,z.Value Id from Master_ListItem z where ListName='ListPosisiMenu') d on d.Id = a.Posisi
  	end
  	else if(@IdModule>0 and @IdMenu=0)
  	begin
  		select
  		a.*,b.NamaModule,c.NamaMenu,d.NamaPosisi NamaPosisi,
  		(
  			select NamaMenu from DataMenu where idMenu = a.IdParent
  		) NamaParent,
  		c.Platform
  		from Role_MenuTree a
  		left join DataModule b on b.IdModul = a.IdModule
  		left join DataMenu c on c.idMenu = a.IdMenu
  		left join (select Text NamaPosisi,value Id from Master_ListItem where ListName='ListPosisiMenu') d on d.Id = a.Posisi
  		where IdModule = @IdModule
  	end
  	else
  	begin
  		select
  		a.*,b.NamaModule,c.NamaMenu,d.NamaPosisi NamaPosisi,
  		(
  			select NamaMenu from DataMenu where idMenu = a.IdParent
  		) NamaParent,
  		c.Platform
  		from Role_MenuTree a
  		left join DataModule b on b.IdModul = a.IdModule
  		left join DataMenu c on c.idMenu = a.IdMenu
  		left join (select Text NamaPosisi,value Id from Master_ListItem where ListName='ListPosisiMenu') d on d.Id = a.Posisi
  		where 
  		(a.IdModule = @IdModule and a.IdMenu = @IdMenu)
  	end
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_RoleMenuData_ListMenu_Delete]...';
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_RoleMenuData_ListMenu_Delete]
  	@IdRole bigint,
  	@IdMenu bigint
  AS
  
  declare @Title nvarchar(max)
  declare @Message nvarchar(max)
  declare @MStatus nvarchar(max)
  
  if exists(select*from GroupData_MenuAkses where IdMenu = @IdMenu and IdRole=@IdRole)
  begin
  	declare @NamaMenu nvarchar(max)
  
  	select @NamaMenu=NamaMenu  from DataMenu where idMenu=@IdMenu
  	
  	delete from GroupData_MenuAkses where IdMenu=@IdMenu and IdRole=@IdRole
  
  	set @Title = 'Success'
  	set @Message = 'Data '+ @NamaMenu +' berhasil dihapus'
  	set @MStatus = 'success'
  end
  else
  begin
  	set @Title = 'Sorry'
  	set @Message = 'data not exists'
  	set @MStatus = 'error'
  end
  
  select @Title Title, @Message Message, @MStatus Status, @IdMenu Id
  GO
  PRINT N'Creating [dbo].[SP_RoleMenuData_Save]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_RoleMenuData_Save]
  	@IdRole bigint,
  	@IdModule bigint,
  	@Posisi bigint,
  	@IdParent bigint,
  	@Urutan bigint,
  	@IdMenu bigint
  AS
  
  
  declare @Title nvarchar(max)
  declare @Message nvarchar(max)
  declare @MStatus nvarchar(max)
  
  declare @IdS bigint
  set @IdS = 0
  
  declare @NamaModule nvarchar(max) set @NamaModule = (select NamaModule from DataModule where IdModul = @IdModule)
  declare @NamaMenu nvarchar(max) set @NamaMenu = (select NamaMenu from DataMenu where idMenu = @IdMenu)
  
  if(@IdRole = 0)
  begin
  	if not exists(select*from Role_MenuTree where IdModule = @IdModule and IdMenu = @IdMenu)
  	begin
  		insert into Role_MenuTree
  		(
  			IdModule,
  			Posisi,
  			IdParent,
  			Urutan,
  			IdMenu
  		)
  		values 
  		(
  			@IdModule,
  			@Posisi,
  			@IdParent,
  			@Urutan,
  			@IdMenu
  		)
  
  		set @IdRole = (select IdRole from Role_MenuTree where IdRole = SCOPE_IDENTITY())
  		set @Title = 'Success'
  		set @Message = 'Data '+ @NamaModule + ' - '+ @NamaMenu +' berhasil dibuat'
  		set @MStatus = 'success'
  	end
  	else
  	begin
  		set @Title = 'Sorry'
  		set @Message = 'Data '+ @NamaModule + ' - '+ @NamaMenu +' already exists'
  		set @MStatus = 'error'
  	end
  end
  else
  begin
  	declare @idModule_sebelum bigint
  	declare @idMenu_sebelum bigint
  
  	set @idModule_sebelum = (select IdModule from Role_MenuTree where IdRole=@IdRole)
  	set @idMenu_sebelum = (select IdMenu from Role_MenuTree where IdRole=@IdRole)
  	
  	if(@IdModule = @idModule_sebelum and @IdMenu = @idMenu_sebelum)
  	begin
  		update Role_MenuTree
  		set 
  			IdModule=@IdModule,
  			Posisi=@Posisi,
  			IdParent=@IdParent,
  			Urutan=@Urutan,
  			IdMenu=@IdMenu
  		where 
  		IdRole=@IdRole
  
  		set @Title = 'Success'
  		set @Message = 'Data '+ @NamaModule + ' - '+ @NamaMenu +' berhasil diupdate'
  		set @MStatus = 'success'
  	end
  	else
  	begin
  		if((select count(*) from Role_MenuTree where IdModule = @IdModule and IdMenu = @IdMenu) > 0)
  		begin
  			set @Title = 'Sorry'
  			set @Message = 'Data '+ @NamaModule + ' - '+ @NamaMenu  +' already exists, silahkan cari nama lain'
  			set @MStatus = 'error'
  		end
  		else
  		begin
  			update Role_MenuTree
  			set 
  				IdModule=@IdModule,
  				Posisi=@Posisi,
  				IdParent=@IdParent,
  				Urutan=@Urutan,
  				IdMenu=@IdMenu
  			where 
  			IdRole=@IdRole
  
  			set @Title = 'Success'
  			set @Message = 'Data '+ @NamaModule + ' - '+ @NamaMenu +' berhasil diupdate'
  			set @MStatus = 'success'
  		end
  	end
  
  end
  
  
  select @Title Title, @Message Message, @MStatus Status, @IdRole Id
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_UserData_Del]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_UserData_Del]
  	@Id bigint
  AS
  
  declare @Title nvarchar(max)
  declare @Message nvarchar(max)
  declare @MStatus nvarchar(max)
  
  if exists(select*from UserData where id = @Id)
  begin
  	declare @Username nvarchar(max)
  	set @Username =(select username from UserData where id=@Id)
  	
  	delete from UserData where id = @Id
  
  	set @Title = 'Success'
  	set @Message = 'User '+ @Username + ' berhasil dihapus'
  	set @MStatus = 'success'
  end
  else
  begin
  	set @Title = 'Sorry'
  	set @Message = 'User '+ @Username + ' not exists'
  	set @MStatus = 'error'
  end
  
  select @Title Title, @Message Message, @MStatus Status, @Id Id
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_UserData_Get]...';
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_UserData_Get]
  AS
  	select
  	a.id,
  	a.username,
  	convert(nvarchar(max),DecryptByPassPhrase('key', a.password )) password,
  	a.RoleId,a.NamaLengkap,a.Photo,a.Status, b.NamaGroup RoleName
  	from UserData a
  	left join GroupData b on b.IdRole = a.RoleId
  GO
  PRINT N'Creating [dbo].[SP_UserData_GetById]...';
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_UserData_GetById]
  	@Id bigint
  AS
  	select
  	a.id,a.username,
  	convert(nvarchar(max),DecryptByPassPhrase('key', a.password )) password,
  	a.RoleId,a.NamaLengkap,a.Photo,a.Status,b.NamaGroup RoleName
  	from UserData a
  	left join GroupData b on b.IdRole = a.RoleId
  	where id = @Id
  GO
  PRINT N'Creating [dbo].[SP_UserData_GetSearch]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_UserData_GetSearch]
  	@username nvarchar(max)
  AS
  	select
  	a.id,a.username,
  	convert(nvarchar(max),DecryptByPassPhrase('key', a.password )) password,
  	a.RoleId,a.NamaLengkap,a.Photo,a.Status,b.NamaGroup RoleName
  	from UserData a
  	left join GroupData b on b.IdRole = a.RoleId
  	where REPLACE(RTRIM(LTRIM(username)),' ','') like '%'+REPLACE(RTRIM(LTRIM(@username)),' ','')+'%'
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[SP_UserData_Save]...';
  
  
  GO
  CREATE PROCEDURE [dbo].[SP_UserData_Save]
  	@id bigint,
  	@username nvarchar(max),
  	@password nvarchar(max),
  	@RoleId bigint,
  	@NamaLengkap nvarchar(max),
  	@Photo nvarchar(max),
  	@Status bigint
  AS
  	declare @Title nvarchar(max)
  	declare @Message nvarchar(max)
  	declare @MStatus nvarchar(max)
  	
  
  if(@id=0)
  begin
  	-- Create
  	if not exists(select*from UserData where (username = @username))
  	begin
  		insert into UserData
  		(
  			username,
  			password,
  			RoleId,
  			NamaLengkap,
  			Photo,
  			Status
  		)
  		values 
  		(
  			@username,
  			EncryptByPassPhrase('key',@password),
  			@RoleId,
  			@NamaLengkap,
  			@Photo,
  			@Status
  		)
  
  		set @Id = (select id from UserData where id = SCOPE_IDENTITY())
  		set @Title = 'Success'
  		set @Message = 'Penambahan user '+ @username + ' berhasil dibuat'
  		set @MStatus = 'success'
  	end
  	else
  	begin
  		set @Title = 'Sorry'
  		set @Message = 'user '+ @username + ' already exists'
  		set @MStatus = 'error'
  	end
  end
  else
  begin
  	-- Modif
  	declare @username_sebelum nvarchar(max)
  	select @username_sebelum =  username from UserData where id = @id
  
  	if(@username_sebelum = @username)
  	begin
  		update UserData
  		set 
  			username=@username,
  			password=EncryptByPassPhrase('key',@password),
  			NamaLengkap=@NamaLengkap,
  			Photo = @Photo,
  			RoleId = @RoleId,
  			Status=@Status
  		where id=@id
  
  		set @Title = 'Success'
  		set @Message = 'User '+ @username + ' berhasil diupdate'
  		set @MStatus = 'success'
  	end
  	else
  	begin
  		--kalo ada perubahan username
  		if not exists(select*from UserData where (username = @username) and id not in (@id))
  		begin
  			update UserData
  			set 
  				username=@username,
  				password=EncryptByPassPhrase('key',@password),
  				NamaLengkap=@NamaLengkap,
  				Photo = @Photo,
  				RoleId = @RoleId,
  				Status=@Status
  			where id=@id
  
  			set @Title = 'Success'
  			set @Message = 'User '+ @username + ' berhasil diupdate'
  			set @MStatus = 'success'
  		end
  		else
  		begin
  			set @Title = 'Sorry'
  			set @Message = 'User / Email already exists, silahkan cari nama lain'
  			set @MStatus = 'error'
  		end
  	end	
  end
  
  select @Title Title, @Message Message, @MStatus Status, @Id Id
  GO
  ALTER DATABASE [$(DatabaseName)]
      SET RESTRICTED_USER 
      WITH ROLLBACK IMMEDIATE;
  
  
  GO
  PRINT N'Update complete.';
  
  
  GO
  