/*
작성자 : ByVINS
작성일 : 2004-01-31
설 명 : 관리자 아이디 추가(관리자 툴..)
*/
Create Procedure dbo.NM_ManagerMod_sp
(
@UserID NVarchar(12) = '',
@Pwd NVarchar(30) = '' ,
@UserName NVarchar(20) = '',
@Group_No INT = 0,
@el_CD INT = 0
)
AS
Declare @Mem_NO INT, @Cnt INT
IF @UserID = '' Or @Pwd='' Or @UserName = '' Or @Group_No = 0 Or @el_CD = 0 Begin
Goto Err_EmptyInfo
End
--아이디가 이미 있는지 체크
Select @Cnt = Count(*) From dbo.Employee Where UserID = @UserID
IF @Cnt <> 0 Begin
Update Employee SET Pwd = @Pwd, UserName = @UserName, Group_No = @Group_No, el_CD = @el_CD
Where UserID = @UserID
IF @@Error <> 0 Goto Err_UpdateFail
Return 1 --성공
End
Else Begin
Return -1 --없는 아이디를 수정 시도 하였음
End
Err_EmptyInfo:
Return -2 --중요 정보가 누락 됨
Err_UpdateFail:
Return -3 --Insert 실폐
----------------------------------------------------------------------
NM_GetMainCategory_sp 0, 0
/*
작성자 : ByVINS
작성일 : 2003-12-29
설 명 : 관리자 관리 메뉴 해당 카테고리 서브 리턴
*/
ALTER Procedure dbo.NM_GetMainCategory_sp
@np_supCD INT = 0
, @np_CD INT = 0
AS
Select np_Cd, np_progCategoryID from dbo.NM_progCategory
Where np_IsActive = 1 And np_supCD = @np_supCD
Order by np_Point
Execute NM_GetSubCategory_sp @np_CD
Go
/*
작성자 : ByVINS
작성일 : 2003-12-29
설 명 : 관리자 관리 메뉴 해당 카테고리 서브 리턴
*/
ALTER Procedure dbo.NM_GetSubCategory_sp
@np_CD INT = 0
AS
IF @np_CD = 0 Goto Err_Skip
Select nd_CD, nd_progPath, nd_progTarget, nd_progDesc, nd.np_CD
from NM_progCategory AS np Inner join NM_progDetails AS nd ON np.np_Cd = nd.np_Cd
Where nd.np_CD = @np_CD
Order By nd.nd_Point ASC
Return
Err_Skip:
Select nd_CD, nd_progPath, nd_progTarget, nd_progDesc, nd.np_CD
from NM_progCategory AS np Inner join NM_progDetails AS nd ON np.np_Cd = nd.np_Cd
Order By nd.nd_Point ASC
Return
/*
작성자 : ByVINS
작성일 : 2003-12-29
설 명 : 관리자 관리 메뉴 해당 카테고리 서브 리턴
*/
Create Procedure dbo.NM_GetSubCategoryDetail_sp
@nd_CD INT = 0
AS
IF @nd_CD = 0 Goto Err
Select nd_CD, nd_progPath, nd_progTarget, nd_progDesc, nd.np_CD
from NM_progCategory AS np Inner join NM_progDetails AS nd ON np.np_Cd = nd.np_Cd
Where nd_CD = @nd_CD
Return
Err:
Return
----------------------------------------------------------------------
Select * from NM_progDetails
/*
작성자 : ByVINS
작성일 : 2003-12-30
설 명 : 관리자 메뉴 추가
*/
Alter Procedure dbo.NM_Add_progDetails_sp
(
@nd_progDesc NVarchar(50)
, @nd_progPath NVarchar(100)
, @nd_progTarget NVarchar(20) = 'Manage_ex'
, @np_CD INT = 0
, @doID NVarchar(12) = 'system'
)
AS
Declare @ReturnValue INT, @ReturnSet INT
IF @np_CD = 0 Goto Err_NoParent
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, np_CD)
Values(@nd_progDesc, @nd_progPath, @nd_progTarget, @np_CD)
IF @@Error > 0 Goto Err_NoParent
SET @ReturnSet = @@Identity
Execute @ReturnValue = NM_ADD_CategoryHistory_sp 'INSERT', @nd_progDesc, @nd_progPath, @nd_progTarget, doID
IF @ReturnValue = 0 Goto Err_RetryHistory
Return @ReturnSet
Err_NoParent:
Return 0
Err_RetryHistory:
Execute @ReturnValue = NM_ADD_CategoryHistory_sp 'INSERT', @nd_progDesc, @nd_progPath, @nd_progTarget, doID
Return @ReturnSet
GO
/*
작성자 : ByVINS
작성일 : 2003-12-30
설 명 : 관리자 메뉴 삭제, 수정
*/
Alter Procedure dbo.NM_ModDel_progDetails_sp
(
@sel_mode INT = 0
, @nd_CD INT = 0
, @nd_progDesc NVarchar(50) = ''
, @nd_progPath NVarchar(100) = ''
, @nd_progTarget NVarchar(20) = 'Manage_ex'
, @doID NVarchar(12) = 'system'
)
AS
IF @nd_CD = 0 Goto Err_NoParent
IF @sel_mode = 0 Begin
Update NM_progDetails
SET nd_progDesc = @nd_progDesc, nd_progPath = @nd_progPath, nd_progTarget = @nd_progTarget
Where nd_CD = @nd_CD
Execute NM_ADD_CategoryHistory_sp 'UPDATE', @nd_progDesc, @nd_progPath, @nd_progTarget, doID
Return 1
End
IF @sel_mode = 1 Begin
Delete NM_progDetails Where nd_CD = @nd_CD
Execute NM_ADD_CategoryHistory_sp 'DELETE', @nd_progDesc, @nd_progPath, @nd_progTarget, doID
Return 2
End
Err_NoParent:
Return 0
/*
작성자 : ByVINS
작성일 : 2003-12-30
설 명 : 카테고리 히스토리
*/
CREATE Table dbo.CategoryHistory
(
ch_CD INT Identity(1,1) Primary key
, selMode NVarchar(10) default('Insert')
, nd_progDesc NVarchar(50)
, nd_progPath NVarchar(100)
, nd_progTarget NVarchar(20)
, doID NVarchar(12) default('system')
, regDate DateTime default(Getdate())
)
/*
작성자 : ByVINS
작성일 : 2003-12-30
설 명 : 카테고리 히스토리 InSERT
*/
Create Procedure dbo.NM_ADD_CategoryHistory_sp
(
@selMode NVarchar(10) = ''
, @nd_progDesc NVarchar(50) = ''
, @nd_progPath NVarchar(100) = ''
, @nd_progTarget NVarchar(20) = 'Manage_ex'
, @doID NVarchar(12) = 'system'
)
AS
INSERT CategoryHistory(selMode, nd_progDesc, nd_progPath, nd_progTarget, doID)
Values(@selMode, @nd_progDesc, @nd_progPath, @nd_progTarget, @doID)
IF @@Error > 0 Goto Err
Return 1
Err:
Return 0
----------------------------------------------------------------------
/*
작성자 : ByVINS
작성일 : 2004-01-02
설 명 : 직함 코드를 받아서 직함을 리턴함(상무, 부장, 주임등...)
*/
CREATE Function dbo.fn_Office_Name
(
@office_code INT
)
RETURNS NVARCHAR(20)
BEGIN
DECLARE @RX NVarchar(20)
Select @RX = office_name from Office_Level Where office_code = @office_code
RETURN (@RX)
END
Go
NM_Get_User 1, 0, 0
NM_Get_User 0 --전체
NM_Get_User 1, 34 --부서별
NM_Get_User 2, 3 --그룹별
NM_Get_User 3, 'icdtl' --아이디별
NM_Get_User 4, 8 --직급별
NM_Get_User 1,0,4
/*
작성자 ; ByVINS
작성일 : 2004-01-02
설 명 : User 리턴(by SomeType)
*/
ALTER Procedure dbo.NM_Get_User
@SelMode INT = 0
, @SearchChar NVarchar(10) = ''
, @GroupCD INT = 0
AS
Declare @strSQL NVarchar(1000), @vstrSQL NVarchar(1000)
--모든 그룹 리턴
Select GroupCD, GroupName from NM_UserGroups
SET @strSQL = ' Select UC_CD, NU.GroupCD, GroupName, NU.Mem_NO, NU.UserID, NU.UserName, NUG.GroupTypeCD, GroupTypeName, NU.Office_Code, dbo.fn_Office_Name(NU.Office_Code) As OfficeName, Group_no '
SET @strSQL = @strSQL + ' from NM_UserContainer AS NU Inner Join NM_UserGroups AS NUG ON NU.GroupCD=NUG.GroupCD '
SET @strSQL = @strSQL + ' Inner Join NM_GroupType AS NG ON NUG.GroupTypeCD = NG.GroupTypeCD '
SET @strSQL = @strSQL + ' Inner Join Employee AS E ON NU.Mem_no = E.Mem_no Where NU.GroupCD = 3 '
SET @vstrSQL = @strSQL + ' And E.Mem_no in (Select Mem_no from NM_UserContainer Where GroupCD = ' + Convert(NVarchar(10), @GroupCD) + ')'
SET @strSQL = @strSQL + ' And E.Mem_no not in (Select Mem_no from NM_UserContainer Where GroupCD = ' + Convert(NVarchar(10), @GroupCD) + ')'
IF @SelMode=1 And @SearchChar<>0
SET @strSQL = @strSQL + ' And Group_No = ' + @SearchChar
IF @SelMode=2 And @SearchChar<>0
SET @strSQL = @strSQL + ' And NU.GroupCD = ' + @SearchChar
IF @SelMode=3 And @SearchChar<>0
SET @strSQL = @strSQL + ' And UserID = ''' + @SearchChar + ''''
IF @SelMode=4 And @SearchChar<>0
SET @strSQL = @strSQL + ' And NU.Office_Code = ' + @SearchChar
Execute sp_ExecuteSQL @strSQL
--선택된 UserGroup 리턴
Execute sp_ExecuteSQL @vstrSQL
Go
Select UC_CD, NU.GroupCD, GroupName, NU.Mem_NO, NU.UserID, NU.UserName, NUG.GroupTypeCD, GroupTypeName, NU.Office_Code, dbo.fn_Office_Name(NU.Office_Code) As OfficeName, Group_no from NM_UserContainer AS NU Inner Join NM_UserGroups AS NUG ON NU.GroupCD=NUG.GroupCD Inner Join NM_GroupType AS NG ON NUG.GroupTypeCD = NG.GroupTypeCD Inner Join Employee AS E ON NU.Mem_no = E.Mem_no Where NU.GroupCD = 3 And E.Mem_no not in (Select Mem_no from NM_UserContainer Where GroupCD = 4)
Select UC_CD, NU.GroupCD, GroupName, NU.Mem_NO, NU.UserID, NU.UserName, NUG.GroupTypeCD, GroupTypeName, NU.Office_Code, dbo.fn_Office_Name(NU.Office_Code) As OfficeName, Group_no from NM_UserContainer AS NU Inner Join NM_UserGroups AS NUG ON NU.GroupCD=NUG.GroupCD Inner Join NM_GroupType AS NG ON NUG.GroupTypeCD = NG.GroupTypeCD Inner Join Employee AS E ON NU.Mem_no = E.Mem_no Where NU.GroupCD = 3 And E.Mem_no not in (Select Mem_no from NM_UserContainer Where GroupCD = 4) And Group_No = 34
Select UC_CD, NU.GroupCD, GroupName, NU.Mem_NO, NU.UserID, NU.UserName, NUG.GroupTypeCD, GroupTypeName, NU.Office_Code, dbo.fn_Office_Name(NU.Office_Code) As OfficeName, Group_no from NM_UserContainer AS NU Inner Join NM_UserGroups AS NUG ON NU.GroupCD=NUG.GroupCD Inner Join NM_GroupType AS NG ON NUG.GroupTypeCD = NG.GroupTypeCD Inner Join Employee AS E ON NU.Mem_no = E.Mem_no Where NU.GroupCD = 3 And E.Mem_no in (Select Mem_no from NM_UserContainer Where GroupCD = 5)
----------------------------------------------------------------------
/*
작성자 : ByVINS
작성일 : 2004-01-02
설 명 : 전체 User OR 그룹 User 반환
*/
CREATE Procedure dbo.NM_GetMemberByGroup
(
@sel_mode INT = 0
, @group_no INT = 0
)
AS
IF @sel_mode = 0 Begin
Select mem_no, [user_ID], [name], [Email], OL.office_code, OL.office_Name from dbo.new_member As NM
INNER JOIN office_level AS OL ON NM.office_code = OL.office_code
Order by mem_no asc
End
IF @sel_mode = 2 Begin
Select mem_no, [user_ID], [name], [Email], OL.office_code, OL.office_Name from dbo.new_member As NM
INNER JOIN office_level AS OL ON NM.office_code = OL.office_code
Where group_no = @group_no
Order by mem_no asc
End
Go
/*
작성자 ; ByVINS
작성일 : 2004-01-02
설 명 : 그룹 스타일(차후 OU, GU 등으로 확장 가능하게 설계)
*/
CREATE Table dbo.NM_GroupType
(
GroupTypeCD INT identity(0,1) primary key,
GroupTypeName NVarchar(50) not null,
GroupTypeDesc NVarchar(300),
)
/*
Insert NM_GroupType(GroupTypeName, GroupTypeDesc) Values('System Groups','모회사 관리자 사이트 builtin Group')
Insert NM_GroupType(GroupTypeName, GroupTypeDesc) Values('User Groups','모회사 관리자 사이트 사용자 생성 그룹')
*/
Go
/*
작성자 ; ByVINS
작성일 : 2004-01-02
설 명 : 그룹 설명
*/
CREATE Table dbo.NM_UserGroups
(
GroupCD INT identity(1,1) primary key,
GroupName NVarchar(50) not null,
GroupDesc NVarchar(300),
GroupTypeCD INT default(1)
)
/*
Insert NM_UserGroups(GroupName, GroupDesc, GroupTypeCD) Values('Administrators','모회사 관리자 사이트의 전체를 관리 할수 있는 builtin 계정', 0)
Insert NM_UserGroups(GroupName, GroupDesc, GroupTypeCD) Values('Power Users','모회사 관리자 사이트의 일부 권한을 제외한 builtin 계정', 0)
Insert NM_UserGroups(GroupName, GroupDesc, GroupTypeCD) Values('Users','모회사 관리자 사이트의 일부 권한만 가진 builtin 계정', 0)
Insert NM_UserGroups(GroupName, GroupDesc, GroupTypeCD) Values('Web Developer1','모회사 웹개발1팀', 1)
Insert NM_UserGroups(GroupName, GroupDesc, GroupTypeCD) Values('Web Developer2','모회사 웹개발2팀', 1)
Insert NM_UserGroups(GroupName, GroupDesc, GroupTypeCD) Values('CS','모회사 CS팀', 1)
*/
Go
/*
작성자 ; ByVINS
작성일 : 2004-01-02
설 명 : 사용자 지정 그룹에 포함된 USER
*/
CREATE Table dbo.NM_UserContainer
(
Uc_CD INT identity(1,1) primary key,
GroupCD INT not null,
mem_no INT not null,
UserID NVarchar(12) not null,
UserName NVarchar(20) not null,
GroupTypeCD INT default(0),
office_code INT default(0),
RegDate DateTime default(Getdate())
)
--------------------------------------------------------------------------------
--------------------- 사원 전체를 일반 USER 권한을 준다. -----------------
--------------------------------------------------------------------------------
--커서 선언
DECLARE cur_OverUsersGroup CURSOR
FOR
Select mem_no, [user_ID] AS UserID, [name] AS UserName, OL.office_code AS office_code from dbo.new_member As NM
INNER JOIN office_level AS OL ON NM.office_code = OL.office_code
--커서 오픈
OPEN cur_OverUsersGroup
--변수 선언
DECLARE @Mem_no INT, @UserID NVarchar(12), @UserName NVarchar(20), @office_code INT
--첫 로우 FETCH
FETCH NEXT FROM cur_OverUsersGroup INTO @Mem_no, @UserID, @UserName, @office_code
WHILE @@FETCH_STATUS = 0
BEGIN
--FETCH
Insert NM_UserContainer(GroupCD, Mem_no, UserID, UserName, GroupTypeCD, office_code)
Values(3, @Mem_no, @UserID, @UserName, 0, @office_code)
--다음 로우 FEETCH - 루프
FETCH NEXT FROM cur_OverUsersGroup INTO @Mem_no, @UserID, @UserName, @office_code
END
--커서 CLOSE
CLOSE cur_OverUsersGroup
--커서 DEALLOCATE
DEALLOCATE cur_OverUsersGroup
GO
----------------------------------------------------------------------
Select * from employee where UserID = 'webplan'
/*
작성자 : ByVINS
작성일 : 2004-02-04
설 명 : 카테고리 설정 페이지 리스팅
*/
ALTER Procedure dbo.NM_Category_Select_ForNonLimitDepth
AS
Select
np_CD, np_progCategoryID, np_progCategoryDesc, --기본 카테고리 설정인자들
np_Group, np_Step, np_Depth, --카테고리의 무한 종속성을 위한 인자들 및 사용여부
np_subCD, np_Point, np_supCD --카테고리의 상하를 찾아 갈수 있는 인덱스 및 순위조정
from dbo.NM_progCategory
Where np_IsActive = 1
Order by np_Group Asc, np_Step Asc, np_Point Asc
--np_Group 값은 ASC 또는 Desc로 하게 되면 먼저 입력한 카테고리나 나중에 입력한 카테고리나 둘중에 하나가 먼저 보이게 된다.
--np_Step 값은 ASC로 리스팅 해야 하며 이는 상위-중위-하위-> 이렇게 되면 카테고리의 형식을 보여주게된다.
--np_Depth 값은 카테고리의 깊이를 뜻하며 Display 시 Visual하게 처리 할수 있게 하기 위함이다.
Go
/*
작성자 : ByVINS
작성일 : 2004-02-04
설 명 : 관리자 메뉴 조정 카테고리 설정 세팅
-- 카테고리 생성 무한 Depth ~
*/
Alter Procedure dbo.NM_Category_Add_ForNonLimitDepth
(
@np_CD INT = 0
, @np_progCategoryID NVarchar(20)
, @np_progCategoryDesc NVarchar(200)
, @np_subCD INT = 0
, @np_Point INT = 0
, @np_supCD INT = 0
, @np_Group INT = 0
, @np_Step INT = 0
, @np_Depth INT = 0
, @doID NVarchar(12) = 'system'
)
AS
Declare @nMaxGroup INT
IF @np_CD = 0 Begin
Select @nMaxGroup = Max(np_Group) From NM_progCategory
SET @np_Group = @nMaxGroup + 1
SET @np_Step=0
SET @np_Depth=0
End
Else Begin
--카테고리에서 같은 그룹 내의 원카테고리에서 현 답변보다 더 이전에 작성된 카테고리는 순위를 올려 버림
Update NM_ProgCategory SET np_Step=np_Step + 1 WHERE np_Group= @np_Group And np_Step > @np_Step
--그리고 그 올려버린 카테고리와 원본 글 사이이 들어갈 값으로 세팅
SET @np_Step = @np_Step + 1
SET @np_Depth = @np_Depth + 1
End
Insert NM_progCategory(np_progCategoryID, np_progCategoryDesc, np_subCD, np_Point, np_supCD, np_Group, np_Step, np_Depth, doID)
Values (@np_progCategoryID, @np_progCategoryDesc, @np_subCD, @np_Point, @np_supCD, @np_Group, @np_Step, @np_Depth, @doID)
Return 1
IF @@Error > 0 Goto Err_Insert
Err_Insert:
Return 0
Go
/*
작성자 : ByVINS
작성일 : 2004-02-04
설 명 : 관리자 메뉴 조정 카테고리 수정 세팅
*/
ALTER Procedure dbo.NM_Category_Mod_ForNonLimitDepth
(
@np_CD INT = 0
, @np_progCategoryID NVarchar(20)
, @np_progCategoryDesc NVarchar(200)
, @doID NVarchar(12) = 'system'
)
AS
Update NM_progCategory SET np_progCategoryID=@np_progCategoryID, np_progCategoryDesc = @np_progCategoryDesc, doID = @doID
Where np_CD = @np_CD
Return 1
IF @@Error > 0 Goto Err_Update
Err_Update:
Return 0
Go
/*
작성자 : ByVINS
작성일 : 2004-02-04
설 명 : 관리자 메뉴 조정 카테고리 샥제
*/
ALTER Procedure dbo.NM_Category_Del_ForNonLimitDepth
(
@np_CD INT = 0
, @doID NVarchar(12) = 'system'
)
AS
Select * from NM_progCategory_Del_History
Insert NM_progCategory_Del_History (np_CD, np_progCategoryID, np_progCategoryDesc, np_subCD, np_Point, np_supCD, np_IsActive, np_Group, np_Step, np_Depth, doID)
Select np_CD, np_progCategoryID, np_progCategoryDesc, np_subCD, np_Point, np_supCD, np_IsActive, np_Group, np_Step, np_Depth, @doID From NM_progCategory
Where np_CD = 1
Delete From NM_progCategory Where np_CD = @np_CD
IF @@Error > 0 Goto Err_Delete
Return 1
Err_Delete:
Return 0
Go
----------------------------------------------------------------------
Select np_CD, np_progCategoryID, np_progCategoryDesc, np_supCD
Into dbo.NM_progCategory_show from dbo.NM_progCategory
Order by np_supCD ASC, np_Point Asc, np_CD ASC
Select * from dbo.NM_progCategory_show
sp_help NM_progCategory_show
Select NM2.np_supCD, NM2.np_CD, NM2.np_progCategoryID, NM2.np_progCategoryDesc, NM2.np_subCD, NM2.np_Point, NM2.np_supCD
from dbo.NM_progCategory AS NM1 INNER JOIN dbo.NM_progCategory AS NM2
ON NM1.np_CD = NM2.np_supCD
Where np_CD
Select np_CD, np_progCategoryID, np_progCategoryDesc, np_supCD from dbo.NM_progCategory_show
DECLARE @strSQL NVARCHAR(1000)
DECLARE CUR_NM_CategoryGeneration CURSOR
FOR
Select np_CD, np_subCD, np_Point, np_supCD
from dbo.NM_progCategory
Order by np_supCD ASC, np_Point Asc, np_CD ASC
OPEN CUR_NM_CategoryGeneration
DECLARE @np_CD INT, @np_subCD INT, @np_Point INT, @np_supCD INT
--첫 로우 FETCH
FETCH NEXT FROM CUR_NM_CategoryGeneration INTO @np_CD, @np_subCD, @np_Point, @np_supCD
WHILE @@FETCH_STATUS = 0
BEGIN
--현제 값이 상위 메뉴가 있다면 상위값을 가져와서 리스팅한다.
IF @np_supCD > 0 Begin
Select np_CD, np_progCategoryID, np_progCategoryDesc, np_supCD From dbo.NM_progCategory Where np_CD =@np_supCD
End
--해당 번호가 있다면 지우고 난 후 insert 함...
IF Exists(Select np_CD, np_progCategoryID, np_progCategoryDesc, np_supCD from dbo.NM_progCategory_show Where np_CD = @np_CD)
Begin
delete from dbo.NM_progCategory_show Where np_CD = @np_CD and np_supCD = @np_supCD
End
Insert dbo.NM_progCategory_show (np_CD, np_progCategoryID, np_progCategoryDesc, np_supCD )
FETCH NEXT FROM CUR_NM_CategoryGeneration INTO @Aid, @file_codes
END
CLOSE CUR_NM_CategoryGeneration
DEALLOCATE CUR_NM_CategoryGeneration
----------------------------------------------------------------------
/*
작성자 : ByVINS
작성일 : 2004-01-28
설 명 : User가 접근 할려고 시도 하는 페이지에 적정 권한이 있는지 확인(프로그램 접근 권한 체크)
각 페이지에서 공통으로 불러올 페이지에서 해당 로그온 자가 프로그램에 접근할 권한이 있는지 검사..후 값 리턴...
*/
ALTER Procedure dbo.NM_Check_Authority_ToProg
(
@nd_CD INT = 0
, @UserID NVarchar(12) = ''
)
AS
IF @nd_CD = 0 Or @UserID = '' Goto Err_Exit
Declare @np_CD INT, @Cnt INT
--하위 카테고리 값으로 상위 값을 쿼리
Select @np_CD = np.np_CD
from dbo.NM_progDetails AS nd INNER JOIN dbo.NM_progCategory AS np
ON nd.np_CD = np.np_CD
Where np_isActive = 1 And nd_CD = @nd_CD
--User Permit Info
Select @Cnt = Count(UC.GroupCD) from dbo.NM_UserContainer AS UC
INNER JOIN Employee AS E ON UC.UserID = E.UserID
INNER JOIN NM_progPermission AS NPP ON UC.GroupCD = NPP.GroupCD
Where E.UserID = @UserID And np_CD = @np_CD
Return @Cnt
Err_Exit:
Return 0
Go
--사용 샘플....
--0이면 권한 없고 0보다 크면 권한 있음....
Declare @Return INT
Execute @Return = NM_Check_Authority_ToProg 153, 'icdtl'
Select @Return
----------------------------------------------------------------------
/*
작성자 : ByVINS
작성일 : 2004-01-19
설 명 : 유저그룹 리스트 변환
*/
CREATE Procedure dbo.NM_Get_UserGroups_sp
(
@SelMode INT = 0
, @GroupTypeCD INT = 0
)
AS
IF @SelMode = 0 Begin
Select GroupCD, GroupName, GroupDesc, GroupTypeCD
from dbo.NM_UserGroups
Order by GroupCD ASC
End
Else Begin
Select GroupCD, GroupName, GroupDesc, GroupTypeCD
from dbo.NM_UserGroups
Where GroupTypeCD = @GroupTypeCD
Order by GroupCD ASC
End
Go
Select * from dbo.NM_progCategory
sp_help NM_progPermission
CREATE Table dbo.NM_progPermission
(
npp_CD INT identity(1,1) primary key
, GroupCD INT default(0) --그룹 코드
, np_CD INT default(0) --프로그램 코드(Main)
)
/*
작성자 : ByVINS
작성일 : 2004-01-19
설 명 : 소속 그룹 반환 반환(Administrators, Power User, Web developer1....)
*/
CREATE function dbo.fn_Get_NM_UserGroups_Name
(
@GroupCD INT
)
Returns NVarchar(20)
Begin
Declare @GroupName NVarchar(20)
Select @GroupName = GroupName from NM_UserGroups
Where GroupCD = @GroupCD
Return(@GroupName)
End
Go
select * from dbo.NM_GroupType
/*
작성자 ; ByVINS
작성일 : 2004-01-19
설 명 : 로그인 관련 Session 처리 하기 위한 값 select
*/
CREATE View dbo.NM_Employee_v AS
select E.UserID, Pwd, E.UserName, el_CD, GroupCD, dbo.fn_Get_NM_UserGroups_Name(GroupCD) AS GroupName
from Employee AS E
INNER JOIN NM_UserContainer AS U ON E.UserID = U.UserID
Select * from dbo.NM_UserContainer
Select * from Manage
select E.UserID, Pwd, E.UserName, el_CD, GroupCD, dbo.fn_Get_NM_UserGroups_Name(GroupCD) AS GroupName
from Employee AS E
INNER JOIN NM_UserContainer AS U ON E.UserID = U.UserID
-- 기본 데이터 마이그레이션 --
Select * from dbo.manage
Select Mem_No, UserID, Pwd, UserName, Group_No, el_CD from Employee Where UserID = 'icdtl'
Insert Employee( mem_no, userID, Pwd, UserName, Group_no, el_CD )
Select [no], [id], [password], '', 0, [Level] From dbo.manage
-- 기본 데이터 마이그레이션 --
/*
작성자 : ByVINS
작성일 : 2004-01-19
설 명 : 프로그램 권한 불러 오는 부분.
*/
CREATE Procedure dbo.NM_Get_Permission_by_GroupCD
@GroupCD INT = 0
AS
Select np_CD, np_ProgCategoryID from NM_ProgCategory
Where np_CD Not in ( Select np_CD from NM_progPermission Where GroupCD = @GroupCD )
And np_IsActive = 1 And np_supCD = 0
Select npp.np_CD, np_ProgCategoryID from NM_ProgCategory AS np
INNER JOIN NM_progPermission As npp ON np.np_cd=npp.np_cd
Where GroupCD = @GroupCD
Go
Select Mem_No, UserID, Pwd, UserName, Group_No, el_CD from Employee where UserID= 'icdtl'
Select * from dbo.NM_Employee_v
Select * from manage Where id = '###########'
Update Manage SET Password = '###########' Where [no] = 191
----------------------------------------------------------------------
Create Table dbo.NM_progCategory
(
np_CD Int identity(1,1) primary key
, np_progCategoryID NVarchar(20) not null
, np_progCategoryDesc NVarchar(200) default('')
, np_subCD Int default(0)
)
insert nm_progCategory(np_progCategoryID) values('관리자 아이디 관리(뽀응) ')
insert nm_progCategory(np_progCategoryID) values('회원관리 ')
insert nm_progCategory(np_progCategoryID) values('리더스클럽 ')
insert nm_progCategory(np_progCategoryID) values('게시판 관리 ')
insert nm_progCategory(np_progCategoryID) values('팅이벤트 관리 ')
insert nm_progCategory(np_progCategoryID) values('전체 쪽지 보내기 ')
insert nm_progCategory(np_progCategoryID) values('Bad Manager ')
insert nm_progCategory(np_progCategoryID) values('동호회관리 ')
insert nm_progCategory(np_progCategoryID) values('게임아이템(뽀응) ')
insert nm_progCategory(np_progCategoryID) values('아바타 ')
insert nm_progCategory(np_progCategoryID) values('홈피 ')
insert nm_progCategory(np_progCategoryID) values('아바타샵(NEW) ')
insert nm_progCategory(np_progCategoryID) values('IP 추적 ')
insert nm_progCategory(np_progCategoryID) values('명문학교 ')
insert nm_progCategory(np_progCategoryID) values('게임관련 ')
insert nm_progCategory(np_progCategoryID) values('이벤트관리 ')
insert nm_progCategory(np_progCategoryID) values('이벤트관리(NEW) ')
insert nm_progCategory(np_progCategoryID) values('god ')
insert nm_progCategory(np_progCategoryID) values('MBC 드라마 ')
insert nm_progCategory(np_progCategoryID) values('음악방송 ')
insert nm_progCategory(np_progCategoryID) values('강진축구 ')
insert nm_progCategory(np_progCategoryID) values('CP게임 아이템관련 ')
insert nm_progCategory(np_progCategoryID) values('순위집계하기 ')
insert nm_progCategory(np_progCategoryID) values('캐릭쳇관련 ')
insert nm_progCategory(np_progCategoryID) values('작업중 ')
insert nm_progCategory(np_progCategoryID) values('FamChat ')
insert nm_progCategory(np_progCategoryID) values('공지 및 홍보, 음방 ')
insert nm_progCategory(np_progCategoryID) values('서버 모니터링 ')
insert nm_progCategory(np_progCategoryID) values('로그아웃')
insert nm_progCategory(np_progCategoryID) values('러브관리')
Select * from nm_progCategory
----------------------------------------------------------------------
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('관리자 등록하기','/Manage/add_manager.asp','Manage_ex',0,1)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('비밀번호 변경(본인)','/Manage/manager_pass.asp','Manage_ex',0,1)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('관리자 비밀번호 변경','/Manage/modify_manager_pass.asp','Manage_ex',0,1)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('불량아이디처리','/Manage/userinfo/info_index.asp','Manage_ex',0,2)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('애드빌싱크','/Manage/userinfo/index.asp','Manage_ex',0,2)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('부모 동의 여부 검색','/Manage/userinfo/parent.asp','Manage_ex',0,2)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('Suspend_회원정보','/Manage/userinfo/info_Suspend.asp','Manage_ex',0,2)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('탈퇴회원정보','/Manage/userinfo/info_break.asp','Manage_ex',0,2)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('라그하임 비번 변경(뽀응)','http://www.laghaim.com/netmarble_tools/change_pass.asp','Manage_ex',0,2)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('성형하기 변경(뽀응)','/Manage/userinfo/modify_face.asp','Manage_ex',0,2)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('사용자 IRC에서 튕기기','/Manage/KillUser/','Manage_ex',0,2)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('사용자정보 메모 삭제','/Manage/userinfo/memodel.asp','Manage_ex',0,2)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('리더스클럽 회원정보','/Manage/Premium/index_new.asp','Manage_ex',0,3)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('리더스클럽 아이템정보','/Manage/Premium/Item.asp','Manage_ex',0,3)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('공지사항','/Manage/NoticeBoard/frame.asp','Manage_ex',0,4)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('게시판','/Manage/Board/frame.asp','Manage_ex',0,4)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('부모님 전용 게시판','/Manage/parent/index.asp','Manage_ex',0,4)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('홍보자료','/_footer/notice_board/list.asp','Manage_ex',0,4)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('SMS 이모티콘(뽀응)','/Manage/Emoticon/','Manage_ex',0,4)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('SMS 전송확인(뽀응)','http://netmarble.kynax.com/netmarble/record.jsp','Manage_ex',0,4)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('회사홈페이지 공지사항','/manage/netmarble/write.asp','Manage_ex',0,4)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('웹서버 확인(뽀응)','/manage/netmarble/webserver.asp','Manage_ex',0,4)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('인증/구매정보 조회','/Manage/TingEvent/info_Ting.asp','Manage_ex',0,5)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('일별 정보조회','/Manage/TingEvent/info_TingByDay.asp','Manage_ex',0,5)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('미인증자 처리','/Manage/TingEvent/auth_Ting.asp','Manage_ex',0,5)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('전체 쪽지 보내기','http://218.153.10.41/notice/all_notice.html','Manage_ex',0,6)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('카툰레이서 불량사용자 관리','/Manage/baduser/Cracer.asp','Manage_ex',0,7)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('바둑 불량사용자 관리','/Manage/baduser/Baduk.asp','Manage_ex',0,7)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('불량사용자 관리','/Manage/baduser/','Manage_ex',0,7)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('불량아이디 처리','/manage/baduser/find_badid.asp?directID=on','Manage_ex',0,7)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('불량아이디 처리 II(뽀응)','/manage/baduser_climax2/','Manage_ex',0,7)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('동호회 검색 및 폐쇄(뽀응)','/Manage/club/club_del.asp','Manage_ex',0,8)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('동호회파일검색','/Manage/club/file.asp','Manage_ex',0,8)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('폐쇄동호회 파일삭제','/Manage/club/showOutClub.asp','Manage_ex',0,8)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('정리동호회 백업하기','/Manage/club/showCutClub.asp','Manage_ex',0,8)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('소유하고 있는 아이템 목록','/Manage/item_own/game_item.asp','Manage_ex',0,9)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('되팔기한 아이템 목록','/Manage/item_sell/game_item.asp','Manage_ex',0,9)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('삭제된 아이템 목록','/Manage/item_del/game_item.asp','Manage_ex',0,9)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('선물한 아이템 목록','/Manage/present/game_item.asp','Manage_ex',0,9)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('리더스전용 패키지아이템','/Manage/item_own/package_item.asp','Manage_ex',0,9)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('베스트아바타 순위','/Manage/avata/rank.asp','Manage_ex',0,10)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('명예의 전당 등록','/Manage/avata/bestavata_rank.asp','Manage_ex',0,10)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('아바타초기화','/Manage/avata/','Manage_ex',0,10)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('소유하고 있는 아이템 목록(뽀응)','/Manage/item_own/','Manage_ex',0,10)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('되팔기한 아이템 목록(뽀응)','/Manage/item_sell/','Manage_ex',0,10)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('삭제한 아이템 목록(뽀응)','/Manage/item_del/','Manage_ex',0,10)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('선물한 아이템 목록(뽀응)','/Manage/present/','Manage_ex',0,10)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('소유 아이템(cmind)(뽀응)','/Manage/item_own/cmind_item.asp','Manage_ex',0,10)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('되팔기한 아이템(cmind)(뽀응)','/Manage/item_sell/cmind_item.asp','Manage_ex',0,10)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('삭제한 아이템(cmind)(뽀응)','/Manage/item_del/cmind_item.asp','Manage_ex',0,10)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('선물한 아이템(cmind)(뽀응)','/Manage/present/cmind_item.asp','Manage_ex',0,10)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('캐릭챗 마나구입(뽀응)','/Manage/item_own/cchat.asp','Manage_ex',0,10)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('캐릭챗 마나선물(뽀응)','/Manage/present/cchat.asp','Manage_ex',0,10)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('아이템 추가하기(뽀응)','/Manage/add_item/','Manage_ex',0,10)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('아이템 목록(뽀응만)','/Manage/add_item/list.asp','Manage_ex',0,10)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('복사실패 아바타이미지','http://test2.netmarble.net/manage/avata/imgcopyfaillist.asp','Manage_ex',0,10)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('베스트 신청 리스트','/Manage/agit/best/viewBestAccept.asp','Manage_ex',0,11)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('베스트 선정 리스트','/Manage/agit/best/viewBestSelect.asp','Manage_ex',0,11)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('베스트홈피','/Manage/agit/best','Manage_ex',0,11)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('점수관리','/Manage/agit/Point','Manage_ex',0,11)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('게시판 관리','/Manage/agit/BBS','Manage_ex',0,11)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('앨범 관리','/Manage/agit/album/index_pic.asp','Manage_ex',0,11)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('홈피샷 관리','/Manage/agit/shot','Manage_ex',0,11)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('아이템 목록','/Manage/new_shop/item_list.asp','Manage_ex',0,12)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('아이템 추가','/Manage/new_shop/add_item.asp','Manage_ex',0,12)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('세트 아이템 등록','/Manage/new_shop/set_item_reg.asp','Manage_ex',0,12)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('아바타샵 메인 추천','/Manage/new_shop/new_item_reg.asp','Manage_ex',0,12)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('기본헤어 번호 입력','/Manage/new_shop/hair_item_list.asp','Manage_ex',0,12)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('메인 롤링 배너','/Manage/new_shop/banner_list.asp','Manage_ex',0,12)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('코디룸 등록','/Manage/new_shop/coordiroom_reg.asp','Manage_ex',0,12)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('아이피추적 등록','/Manage/Trace/index.asp','Manage_ex',0,13)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('추적아이디 추가','/Manage/Trace/Trace_ID.asp','Manage_ex',0,13)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('아이피추적 목록','/Manage/Trace/Trace_List.asp','Manage_ex',0,13)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('경찰제공 페이지','http://test2.netmarble.net/trace/','Manage_ex',0,13)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('게임머니충전','/Manage/Game/moneyplus.asp','Manage_ex',0,15)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('게임머니확인(뽀응)','/Manage/Game/money_result.asp','Manage_ex',0,15)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('세븐포커확인','/Manage/Game/smoney_result.asp','Manage_ex',0,15)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('세븐포커2확인','/Manage/Game/s2money_result.asp','Manage_ex',0,15)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('하이로우확인','/Manage/Game/hmoney_result.asp','Manage_ex',0,15)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('바둑이+ 확인','/Manage/Game/lowmoney_result.asp','Manage_ex',0,15)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('PC방 무료충전 카운터','/Manage/Game/pcRoomChk.asp','Manage_ex',0,15)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('게임서버추가','/Manage/Game/','Manage_ex',0,15)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('이벤트등록','/Manage/Event/insert.asp','Manage_ex',0,16)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('이벤트수정','/Manage/Event/modify.asp','Manage_ex',0,16)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('지난이벤트','/Manage/Event/modify_prev.asp','Manage_ex',0,16)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('공지사항등록','/manage/notice_more/index.asp','Manage_ex',0,16)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('이벤트등록','/Manage/Event/NEW/insert.asp','Manage_ex',0,17)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('진행중인이벤트','/Manage/Event/NEW/modify.asp','Manage_ex',0,17)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('지난이벤트','/Manage/Event/NEW/modify_prev.asp','Manage_ex',0,17)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('FAQ관리','/Manage/Event/NEW/faq_list.asp','Manage_ex',0,17)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('Fan god 관리','/Manage/god/','Manage_ex',0,18)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('god vod - 컨텐츠 초기화','/Manage/vod/','Manage_ex',0,18)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('god vod - 티켓 초기화','/Manage/vod/index2.asp','Manage_ex',0,18)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('god vod - 티켓 삭제','/Manage/vod/index3.asp','Manage_ex',0,18)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('drama vod - 컨텐츠 초기화','/Manage/drama_vod/','Manage_ex',0,19)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('drama vod - 티켓 초기화','/Manage/drama_vod/index2.asp','Manage_ex',0,19)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('drama vod - 티켓 삭제','/Manage/drama_vod/index3.asp','Manage_ex',0,19)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('24시간 방송국 등록','/Manage/NowCast/Cast24/index.asp','Manage_ex',0,20)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('전광판 관리','/Manage/EBoard/index.asp?CodeNO=0','Manage_ex',0,20)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('프로등록현황','/Manage/cp_game/ksoccer/pro_count.asp','Manage_ex',0,21)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('회원정보검색','/Manage/cp_game/ksoccer/index.asp','Manage_ex',0,21)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('길드검색','/Manage/cp_game/ksoccer/guild_index.asp','Manage_ex',0,21)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('길드삭제','/Manage/cp_game/ksoccer/delete_process.asp','Manage_ex',0,21)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('아이템, 프로등록 건수(뽀응)','/Manage/cp_game/ksoccer/item.asp','Manage_ex',0,21)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('선물한 아이템 목록(뽀응)','/Manage/cp_game/ksoccer/present_item.asp','Manage_ex',0,21)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('카툰아이템','/manage/cp_game/cracer/index.asp','Manage_ex',0,22)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('카툰되팔기 정보','/manage/cp_game/cracer/sellitem/sellLog.asp','Manage_ex',0,22)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('꾸루꾸루아이템','/manage/cp_game/kurukuru/','Manage_ex',0,22)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('야부리 보유정보','/manage/cp_game/yaburi/owe_item.asp','Manage_ex',0,22)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('야부리 선물정보','/manage/cp_game/yaburi/present_item.asp','Manage_ex',0,22)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('야부리 되팔기정보','/manage/cp_game/yaburi/sell_item.asp','Manage_ex',0,22)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('순위집계하기','/Manage/ranking/ranking.asp','Manage_ex',0,23)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('알림판등록','/Manage/CChat/alim/?BBSMODE=Alim','Manage_ex',0,24)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('친구해요등록','/Manage/CChat/meet/?BBSMODE=meet','Manage_ex',0,24)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('버그게시판관리','/Manage/Board/AddBug.asp','Manage_ex',0,25)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('아이템샵 카테고리 관리','/Manage/new_shop/item_group.asp','Manage_ex',0,25)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('초등학생 팸리스트','/Manage/FamManage/Category/Category.asp?szCategory=a0001','Manage_ex',0,26)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('1318 팸리스트','/Manage/FamManage/Category/Category.asp?szCategory=a0002','Manage_ex',0,26)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('20/30 팸리스트','/Manage/FamManage/Category/Category.asp?szCategory=a0003','Manage_ex',0,26)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('30/40 팸리스트','/Manage/FamManage/Category/Category.asp?szCategory=a0004','Manage_ex',0,26)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('신규팸 등록','/Manage/FamManage/NewFam/NewFam.asp','Manage_ex',0,26)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('Room 추가','/Manage/FamManage/Room/Add.asp','Manage_ex',0,26)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('인기팸 등록','/Manage/FamManage/BestFam/BestFam.asp','Manage_ex',0,26)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('인기팸 삭제','/Manage/FamManage/DelBestFam/DelBestFam.asp','Manage_ex',0,26)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('공지사항','/Manage/NoticeBoard/frame.asp','Manage_ex',0,27)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('홍보자료','/_footer/notice_board/list.asp','Manage_ex',0,27)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('설문관리','/Manage/Vote/index.asp','Manage_ex',0,27)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('모회사 회사소개 공지','/manage/netmarble/write.asp','Manage_ex',0,27)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('New설문등록','/Manage/NewVote/write.asp?Action=Write','Manage_ex',0,27)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('New설문리스트','/Manage/NewVote/','Manage_ex',0,27)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('제안하기','/Manage/NewVote/PropoList.asp','Manage_ex',0,27)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('설문한마디관리','/Manage/VoteAdmin/','Manage_ex',0,27)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('자유게시판','/Manage/Meeting/Board/index.asp?BBSMode=BoardFree','Manage_ex',0,31)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('유머게시판','/Manage/Meeting/Board/index.asp?BBSMode=BoardHumour','Manage_ex',0,31)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('미팅만남 게시판','/Manage/Meeting/Board/index.asp?BBSMode=BoardMeeting','Manage_ex',0,31)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('나만의 작업 노하우','/Manage/Meeting/Board/index.asp?BBSMode=BoardJob','Manage_ex',0,31)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('솔로들을 위한 방','/Manage/Meeting/Board/index.asp?BBSMode=BoardSolo','Manage_ex',0,31)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('아담게시판','/Manage/Meeting/Board/index.asp?BBSMode=BoardAdam','Manage_ex',0,31)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('이브게시판','/Manage/Meeting/Board/index.asp?BBSMode=BoardEve','Manage_ex',0,31)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('Picture','/Manage/Meeting/CheckPicture.asp?menu=pic','Manage_ex',0,31)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('MainUser','/Manage/Meeting/ManageMainUser.asp','Manage_ex',0,31)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('BestUser','/Manage/Meeting/ManageBestUser.asp','Manage_ex',0,31)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('회원 정보','/Manage/Meeting/UserInfo.asp','Manage_ex',0,31)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('게시물선정','/Manage/Meeting/BestBoard.asp','Manage_ex',0,31)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('베스트앨범','/Manage/Meeting/BestabRegList.asp','Manage_ex',0,31)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('뮤직샵메인','/Manage/Meeting/music_Main.asp','Manage_ex',0,31)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('뮤직일일결제','/Manage/Meeting/music/businessLogic/music_Payment_byDay.asp','Manage_ex',0,31)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('게임머니','/Manage/Meeting/LogList.asp','Manage_ex',0,31)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('게임머니2','/Manage/Meeting/LogList.asp?Mode=2','Manage_ex',0,31)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('ARS 정보','/Manage/Meeting/ArsList.asp','Manage_ex',0,31)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('ARS머니 Error','/Manage/Meeting/ARSError.asp','Manage_ex',0,31)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('ARS머니 Error 2','/Manage/Meeting/ARSError.asp?Mode=2','Manage_ex',0,31)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('러브콜매출','http://www.infortel.seoul.kr/webservice/web060/admin/login.asp','Manage_ex',0,31)
Insert NM_progDetails(nd_progDesc, nd_progPath, nd_progTarget, nd_ExecutePermit, np_CD) values('뮤직샵매출','http://billmgmt.netmarble.net/login.asp?rul=/index.asp','Manage_ex',0,31)
----------------------------------------------------------------------
Select * From NM_UserContainer Where GroupCD<>3
Delete From NM_UserContainer Where Uc_CD in ( 358,359 )
Declare @return Int
Execute @return = NM_Set_UserGroups_sp '26,341', 4
Select @return
/*
작성자 : ByVINS
작성일 : 2004-01-05
설 명 : User --> Group 에 멈버로 세팅시킨다..
*/
ALTER Procedure dbo.NM_Set_UserGroups_sp
@Mem_nos NVarchar(500) = ''
, @GroupCD INT = 0
AS
Declare @strSQL_Log NVarchar(1000), @strSQL_Real NVarchar(1000)
IF @GroupCD = 0 Or @Mem_nos = '' Begin
--Group으로 세팅 될수 없는 상황
Goto Err_Groups
End
--기존 멤버를 전부 삭제 한다.
Delete From NM_UserContainer Where GroupCD = @GroupCD
IF @@Error > 0 Goto Err_Insert
SET @strSQL_Log = 'Insert NM_UserContainerHistory (GroupCD, Mem_no, UserID, UserName, GroupTypeCD, office_code) '
SET @strSQL_Log = @strSQL_Log + 'Select ' + Convert(NVarchar(5), @GroupCD) + ', Mem_no, UserID, UserName, GroupTypeCD, office_code From NM_UserContainer '
SET @strSQL_Log = @strSQL_Log + 'Where GroupCD = 3 And Mem_no in (' + @Mem_nos + ') '
SET @strSQL_Real = 'Insert NM_UserContainer (GroupCD, Mem_no, UserID, UserName, GroupTypeCD, office_code) '
SET @strSQL_Real = @strSQL_Real + 'Select ' + Convert(NVarchar(5), @GroupCD) + ', Mem_no, UserID, UserName, GroupTypeCD, office_code From NM_UserContainer '
SET @strSQL_Real = @strSQL_Real + 'Where GroupCD = 3 And Mem_no in (' + @Mem_nos + ') '
--선택되어 온 멤버들의 로그를 남긴다..
Execute Sp_ExecuteSQL @strSQL_Log
IF @@Error > 0 Goto Err_Insert
--선택되어 온 멤버들을 그룹에 넣는다.
Execute Sp_ExecuteSQL @strSQL_Real
IF @@Error > 0 Goto Err_Insert
Return 1
Err_Groups:
Return 0
Err_Insert:
Return -1
GO
NM_GetMainCategory_sp 0, 0
sp_helptext NM_GetMainCategory_sp
/*
작성자 : ByVINS
작성일 : 2003-12-29
설 명 : 관리자 관리 메뉴 해당 카테고리 서브 리턴
*/
CREATE Procedure dbo.NM_GetMainCategory_sp
@np_supCD INT = 0
, @np_CD INT = 0
AS
Select np_Cd, np_progCategoryID from dbo.NM_progCategory
Where np_IsActive = 1 And np_supCD = @np_supCD
Order by np_Point
Execute NM_GetSubCategory_sp @np_CD
/*
작성자 : ByVINS
작성일 : 2004-01-16
설 명 : 관리자 Group 생성
*/
CREATE Procedure dbo.NM_Add_Groups_sp
(
@GroupName NVarchar(100) = ''
, @GroupDesc NVarchar(600) = '사용자 정의 그룹'
, @GroupTypeCD INT = 1 --기본 사용자로 지정
)
AS
IF @GroupName = '' Goto Err_Empty
Insert NM_UserGroups (GroupName, GroupDesc, GroupTypeCD)
Values (@GroupName, @GroupDesc, @GroupTypeCD)
IF @@Error > 0 Goto Err_Empty
Return 1
Err_Empty:
Return 0
----------------------------------------------------------------------
위 쿼리들은 한국 모 인터넷 게임 업체에서 실제로 쓰이고 있는 관리툴 접근 권한을
통제하는 부분의 일부 쿼리 및 프로시져 또는 기초 코드들이다.
SP가 어떤식으로 엮일수 있으며 어느 정도 규모로 사용되며 웹페이지의 삽입 방식인
임베데드 쿼리를 더 빠르고 효율적으로 관리하기를 원하는 개발자들에게 도움이 되었으면
하는 마음에서 공개하였다.
----------------------------------------------------------------------
