if exists (select * from sysobjects where id = object_id('[Tr_sys_Functions_Insert]') and OBJECTPROPERTY(id, 'IsTrigger') = 1)
drop trigger [Tr_sys_Functions_Insert]
GO

if exists (select * from sysobjects where id = object_id('[Tr_sys_Functions_Update]') and OBJECTPROPERTY(id, 'IsTrigger') = 1)
drop trigger [Tr_sys_Functions_Update]
GO

if exists (select * from sysobjects where id = object_id('[Proc_AysnUserPrivilegeByGroup]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [Proc_AysnUserPrivilegeByGroup]
GO

if exists (select * from sysobjects where id = object_id('[Proc_CheckDepartMentExist]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [Proc_CheckDepartMentExist]
GO

if exists (select * from sysobjects where id = object_id('[Proc_CheckDropDownFiledExist]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [Proc_CheckDropDownFiledExist]
GO

if exists (select * from sysobjects where id = object_id('[Proc_CheckFunctionCodeExist]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [Proc_CheckFunctionCodeExist]
GO

if exists (select * from sysobjects where id = object_id('[Proc_CheckRoleNameExist]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [Proc_CheckRoleNameExist]
GO

if exists (select * from sysobjects where id = object_id('[Proc_CheckUserNameExist]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [Proc_CheckUserNameExist]
GO

if exists (select * from sysobjects where id = object_id('[Proc_GetPermissionByUserID]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [Proc_GetPermissionByUserID]
GO

if exists (select * from sysobjects where id = object_id('[Proc_GetPermissionCodeByUrl]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [Proc_GetPermissionCodeByUrl]
GO

if exists (select * from sysobjects where id = object_id('[Proc_MemberLogin]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [Proc_MemberLogin]
GO

if exists (select * from sysobjects where id = object_id('[Proc_PageQuery]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [Proc_PageQuery]
GO

if exists (select * from sysobjects where id = object_id('[Proc_UpdatePwd_sys_Users]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [Proc_UpdatePwd_sys_Users]
GO

if exists (select * from sysobjects where id = object_id('[proc_Delete_sys_Department]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_Delete_sys_Department]
GO

if exists (select * from sysobjects where id = object_id('[proc_Delete_sys_Function]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_Delete_sys_Function]
GO

if exists (select * from sysobjects where id = object_id('[proc_Delete_sys_Log]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_Delete_sys_Log]
GO

if exists (select * from sysobjects where id = object_id('[proc_Delete_sys_Role]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_Delete_sys_Role]
GO

if exists (select * from sysobjects where id = object_id('[proc_Delete_sys_RolePrivilege]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_Delete_sys_RolePrivilege]
GO

if exists (select * from sysobjects where id = object_id('[proc_Delete_sys_User]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_Delete_sys_User]
GO

if exists (select * from sysobjects where id = object_id('[proc_Delete_sys_UserPrivilege]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_Delete_sys_UserPrivilege]
GO

if exists (select * from sysobjects where id = object_id('[proc_GetObject_DropDownFiled]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_GetObject_DropDownFiled]
GO

if exists (select * from sysobjects where id = object_id('[proc_GetObject_sys_Department]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_GetObject_sys_Department]
GO

if exists (select * from sysobjects where id = object_id('[proc_GetObject_sys_Function]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_GetObject_sys_Function]
GO

if exists (select * from sysobjects where id = object_id('[proc_GetObject_sys_Log]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_GetObject_sys_Log]
GO

if exists (select * from sysobjects where id = object_id('[proc_GetObject_sys_Role]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_GetObject_sys_Role]
GO

if exists (select * from sysobjects where id = object_id('[proc_GetObject_sys_RolePrivilege]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_GetObject_sys_RolePrivilege]
GO

if exists (select * from sysobjects where id = object_id('[proc_GetObject_sys_User]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_GetObject_sys_User]
GO

if exists (select * from sysobjects where id = object_id('[proc_GetObject_sys_UserPrivilege]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_GetObject_sys_UserPrivilege]
GO

if exists (select * from sysobjects where id = object_id('[proc_Insert_DropDownFiled]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_Insert_DropDownFiled]
GO

if exists (select * from sysobjects where id = object_id('[proc_Insert_sys_Department]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_Insert_sys_Department]
GO

if exists (select * from sysobjects where id = object_id('[proc_Insert_sys_Function]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_Insert_sys_Function]
GO

if exists (select * from sysobjects where id = object_id('[proc_Insert_sys_Log]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_Insert_sys_Log]
GO

if exists (select * from sysobjects where id = object_id('[proc_Insert_sys_Role]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_Insert_sys_Role]
GO

if exists (select * from sysobjects where id = object_id('[proc_Insert_sys_RolePrivilege]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_Insert_sys_RolePrivilege]
GO

if exists (select * from sysobjects where id = object_id('[proc_Insert_sys_User]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_Insert_sys_User]
GO

if exists (select * from sysobjects where id = object_id('[proc_Insert_sys_UserPrivilege]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_Insert_sys_UserPrivilege]
GO

if exists (select * from sysobjects where id = object_id('[proc_Update_DropDownFiled]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_Update_DropDownFiled]
GO

if exists (select * from sysobjects where id = object_id('[proc_Update_sys_Department]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_Update_sys_Department]
GO

if exists (select * from sysobjects where id = object_id('[proc_Update_sys_Function]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_Update_sys_Function]
GO

if exists (select * from sysobjects where id = object_id('[proc_Update_sys_Role]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_Update_sys_Role]
GO

if exists (select * from sysobjects where id = object_id('[proc_Update_sys_RolePrivilege]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_Update_sys_RolePrivilege]
GO

if exists (select * from sysobjects where id = object_id('[proc_Update_sys_User]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_Update_sys_User]
GO

if exists (select * from sysobjects where id = object_id('[proc_Update_sys_UserPrivilege]') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure [proc_Update_sys_UserPrivilege]
GO

if exists (select * from sysobjects where id = object_id('[vw_GoodsIn]') and OBJECTPROPERTY(id, 'IsView') = 1)
drop view [vw_GoodsIn]
GO

if exists (select * from sysobjects where id = object_id('[vw_GoodsOut]') and OBJECTPROPERTY(id, 'IsView') = 1)
drop view [vw_GoodsOut]
GO

if exists (select * from sysobjects where id = object_id('[DropDownFiled]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
drop table [DropDownFiled]
GO

if exists (select * from sysobjects where id = object_id('[Goods]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
drop table [Goods]
GO

if exists (select * from sysobjects where id = object_id('[GoodsIn]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
drop table [GoodsIn]
GO

if exists (select * from sysobjects where id = object_id('[GoodsOut]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
drop table [GoodsOut]
GO

if exists (select * from sysobjects where id = object_id('[Provider]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
drop table [Provider]
GO

if exists (select * from sysobjects where id = object_id('[sys_Department]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
drop table [sys_Department]
GO

if exists (select * from sysobjects where id = object_id('[sys_Functions]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
drop table [sys_Functions]
GO

if exists (select * from sysobjects where id = object_id('[sys_Log]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
drop table [sys_Log]
GO

if exists (select * from sysobjects where id = object_id('[sys_RolePrivilege]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
drop table [sys_RolePrivilege]
GO

if exists (select * from sysobjects where id = object_id('[sys_Roles]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
drop table [sys_Roles]
GO

if exists (select * from sysobjects where id = object_id('[sys_UserPrivilege]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
drop table [sys_UserPrivilege]
GO

if exists (select * from sysobjects where id = object_id('[sys_Users]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
drop table [sys_Users]
GO

CREATE TABLE [DropDownFiled] (
	[DropFiled_ID] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL ,
	[DropFiled_Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[DropFiled_Value] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

INSERT [DropDownFiled] ( [DropFiled_Name] , [DropFiled_Value] ) VALUES ('货物单位' , '斤' )
INSERT [DropDownFiled] ( [DropFiled_Name] , [DropFiled_Value] ) VALUES ('货物单位' , '壶' )
INSERT [DropDownFiled] ( [DropFiled_Name] , [DropFiled_Value] ) VALUES ('货物单位' , '条' )
INSERT [DropDownFiled] ( [DropFiled_Name] , [DropFiled_Value] ) VALUES ('货物单位' , '只' )
INSERT [DropDownFiled] ( [DropFiled_Name] , [DropFiled_Value] ) VALUES ('货物单位' , '盘' )
INSERT [DropDownFiled] ( [DropFiled_Name] , [DropFiled_Value] ) VALUES ('货物单位' , '块' )
INSERT [DropDownFiled] ( [DropFiled_Name] , [DropFiled_Value] ) VALUES ('货物单位' , '包' )
INSERT [DropDownFiled] ( [DropFiled_Name] , [DropFiled_Value] ) VALUES ('货物单位' , '桶' )
INSERT [DropDownFiled] ( [DropFiled_Name] , [DropFiled_Value] ) VALUES ('货物单位' , '箱' )
INSERT [DropDownFiled] ( [DropFiled_Name] , [DropFiled_Value] ) VALUES ('货物单位' , '瓶' )
INSERT [DropDownFiled] ( [DropFiled_Name] , [DropFiled_Value] ) VALUES ('货物单位' , '打' )
GO

CREATE TABLE [Goods] (
	[GoodsID] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL ,
	[GoodsName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[GoodsSpell] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

INSERT [Goods] ( [GoodsName] , [GoodsSpell] ) VALUES ('青菜' , 'qc' )
INSERT [Goods] ( [GoodsName] , [GoodsSpell] ) VALUES ('精肉' , 'jr' )
INSERT [Goods] ( [GoodsName] , [GoodsSpell] ) VALUES ('条肉' , 'tr' )
INSERT [Goods] ( [GoodsName] , [GoodsSpell] ) VALUES ('大白菜' , 'dbc' )
INSERT [Goods] ( [GoodsName] , [GoodsSpell] ) VALUES ('青椒' , 'qj' )
INSERT [Goods] ( [GoodsName] , [GoodsSpell] ) VALUES ('鲫鱼' , 'jy' )
INSERT [Goods] ( [GoodsName] , [GoodsSpell] ) VALUES ('土豆' , 'td' )
INSERT [Goods] ( [GoodsName] , [GoodsSpell] ) VALUES ('毛豆' , 'md' )
INSERT [Goods] ( [GoodsName] , [GoodsSpell] ) VALUES ('蚕豆' , 'cd' )
INSERT [Goods] ( [GoodsName] , [GoodsSpell] ) VALUES ('鸡肉' , 'jr' )
INSERT [Goods] ( [GoodsName] , [GoodsSpell] ) VALUES ('活鸡' , 'hj' )
INSERT [Goods] ( [GoodsName] , [GoodsSpell] ) VALUES ('活鸭' , 'hy' )
INSERT [Goods] ( [GoodsName] , [GoodsSpell] ) VALUES ('白条鱼' , 'bty' )
INSERT [Goods] ( [GoodsName] , [GoodsSpell] ) VALUES ('蕃茄' , 'fq' )
INSERT [Goods] ( [GoodsName] , [GoodsSpell] ) VALUES ('茄子' , 'qz' )
INSERT [Goods] ( [GoodsName] , [GoodsSpell] ) VALUES ('鸡蛋' , 'jd' )
GO

CREATE TABLE [GoodsIn] (
	[InID] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL ,
	[GoodsID] [int] NULL ,
	[GoodsUnit] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[AddTime] [datetime] NULL ,
	[Number] [decimal](18, 2) NULL ,
	[InPrice] [decimal](18, 2) NULL ,
	[CaiGouID] [int] NULL ,
	[PersonID] [int] NULL ,
	[SupplyName] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[ContactMan] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Remarks] [text] COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [GoodsOut] (
	[OutID] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL ,
	[GoodsID] [int] NULL ,
	[GoodsUnit] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[AddTime] [datetime] NULL ,
	[LingYongID] [int] NULL ,
	[DepartmentID] [int] NULL ,
	[Number] [decimal](18, 2) NULL ,
	[PersonID] [int] NULL ,
	[Remarks] [text] COLLATE Chinese_PRC_CI_AS NULL ,
	[OutPrice] [decimal](18, 2) NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [Provider] (
	[ProvideID] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL ,
	[ProvideUnit] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[ProvideName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

INSERT [Provider] ( [ProvideUnit] , [ProvideName] ) VALUES ('粮店' , '张丽' )
INSERT [Provider] ( [ProvideUnit] , [ProvideName] ) VALUES ('肉类' , '刘海洋' )
INSERT [Provider] ( [ProvideUnit] , [ProvideName] ) VALUES ('蔬菜' , '朱加明' )
INSERT [Provider] ( [ProvideUnit] , [ProvideName] ) VALUES ('禽类' , '林雪珍' )
INSERT [Provider] ([ProvideUnit] , [ProvideName] ) VALUES ('水果' , '刘兴' )
GO

CREATE TABLE [sys_Department] (
	[DepID] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL ,
	[DepName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[ParentID] [int] NULL 
) ON [PRIMARY]
GO

INSERT [sys_Department] ( [DepName] , [ParentID] ) VALUES ('食堂' , 0 )
GO

CREATE TABLE [sys_Functions] (
	[FunctionID] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL ,
	[ParentID] [int] NULL ,
	[FunctionCode] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[FunctionName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Description] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[Url] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
	[IsShow] [bit] NULL ,
	[OrderID] [int] NULL ,
	[IsSystem] [bit] NULL ,
	[depth] [smallint] NULL ,
	[lineage] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 0 , 'S001' , '系统管理' , '' , '' , 1 , 1 , 0 , 0 , '/1/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 1 , 'S001M001' , '用户资料管理' , ' ' , 'Module/SystemApp/UserManage/Default.aspx' , 1 , 2 , 0 , 1 , '/1/2/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 1 , 'S001M002' , '系统菜单管理' , ' ' , 'Module/SystemApp/ModuleManage/Default.aspx' , 0 , 4 , 0 , 1 , '/1/3/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 1 , 'S001M003' , '职位资料管理' , ' ' , 'Module/SystemApp/RoleManage/Default.aspx' , 1 , 1 , 0 , 1 , '/1/4/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 1 , 'S001M004' , '部门资料管理' , ' ' , 'Module/SystemApp/DepartmentManage/Default.aspx' , 1 , 3 , 0 , 1 , '/1/5/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 0 , 'S002' , '系统维护' , ' ' , '' , 1 , 2 , 0 , 0 , '/6/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 6 , 'S002M001' , '系统运行状态' , ' ' , 'Module/SysMaintenance/SystemState.aspx' , 1 , 1 , 0 , 1 , '/6/7/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 0 , 'S003' , '个人面板' , ' ' , '' , 1 , 3 , 0 , 0 , '/8/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 6 , 'S002M002' , '系统环境配置' , ' ' , 'Module/SysMaintenance/SystemConfig.aspx' , 0 , 1 , 0 , 1 , '/6/9/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 8 , 'S003M001' , '修改个人资料' , ' ' , 'Module/PersonPanel/UserInfo.aspx' , 1 , 1 , 0 , 1 , '/8/10/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 8 , 'S003M002' , '修改密码' , ' ' , 'Module/PersonPanel/UpdatePwd.aspx' , 1 , 1 , 0 , 1 , '/8/11/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 8 , 'S003M003' , '退出系统' , ' ' , 'LoginOut.aspx' , 0 , 1 , 0 , 1 , '/8/12/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 6 , 'S002M003' , '事件日志管理' , ' ' , 'Module/SysMaintenance/SystemLogs.aspx' , 1 , 1 , 0 , 1 , '/6/13/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 3 , 'S001M002F001' , '新建' , '' , '' , 1 , 1 , 1 , 2 , '/1/3/14/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 3 , 'S001M002F002' , '修改' , ' ' , ' ' , 1 , 1 , 1 , 2 , '/1/3/15/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 3 , 'S001M002F003' , '删除' , ' ' , ' ' , 1 , 1 , 1 , 2 , '/1/3/16/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 2 , 'S001M001F001' , '新建' , '' , '' , 1 , 1 , 1 , 2 , '/1/2/17/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 2 , 'S001M001F002' , '修改' , '' , '' , 1 , 1 , 1 , 2 , '/1/2/18/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 2 , 'S001M001F003' , '删除' , '' , '' , 1 , 1 , 1 , 2 , '/1/2/19/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 4 , 'S001M003F001' , '新建' , '' , '' , 1 , 1 , 1 , 2 , '/1/4/20/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 4 , 'S001M003F002' , '修改' , '' , '' , 1 , 1 , 1 , 2 , '/1/4/21/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 4 , 'S001M003F003' , '删除' , '' , '' , 1 , 1 , 1 , 2 , '/1/4/22/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 5 , 'S001M004F001' , '新建' , '' , '' , 1 , 1 , 1 , 2 , '/1/5/23/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 5 , 'S001M004F002' , '修改' , '' , '' , 1 , 1 , 1 , 2 , '/1/5/24/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 5 , 'S001M004F003' , '删除' , '' , '' , 1 , 1 , 1 , 2 , '/1/5/25/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 2 , 'S001M001F004' , '查看' , '' , '' , 1 , 1 , 1 , 2 , '/1/2/26/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 4 , 'S001M003F004' , '角色权限同步' , '' , '' , 1 , 1 , 1 , 2 , '/1/4/27/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 4 , 'S001M003F005' , '设置角色权限' , '' , '' , 1 , 1 , 1 , 2 , '/1/4/28/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 2 , 'S001M001F005' , '更改状态' , '' , '' , 1 , 1 , 1 , 2 , '/1/2/29/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 2 , 'S001M001F006' , '设置用户权限' , '' , '' , 1 , 1 , 1 , 2 , '/1/2/30/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 1 , 'S001M005' , '数据字典管理' , '' , 'Module/SystemApp/Dictionary/Default.aspx' , 1 , 5 , 0 , 1 , '/1/31/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 31 , 'S001M005F001' , '添加' , '' , '' , 1 , 1 , 1 , 2 , '/1/31/32/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 31 , 'S001M005F002' , '修改' , '' , '' , 1 , 1 , 1 , 2 , '/1/31/33/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 31 , 'S001M005F003' , '删除' , '' , '' , 1 , 1 , 1 , 2 , '/1/31/34/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 0 , 'S004' , '食堂进销存' , '' , '' , 1 , 5 , 0 , 0 , '/35/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 35 , 'S004M001' , '货品入库' , '' , 'Module/Invoicing/GoodsIn.aspx' , 1 , 1 , 0 , 1 , '/35/36/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 35 , 'S004M002' , '货品出库' , '' , 'Module/Invoicing/GoodOut.aspx' , 1 , 1 , 0 , 1 , '/35/37/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 0 , 'S005' , '基础信息' , '' , '' , 1 , 4 , 0 , 0 , '/38/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 38 , 'S005M001' , '供货商管理' , '' , 'Module/BasicInfo/ProvideManage.aspx' , 1 , 1 , 0 , 1 , '/38/39/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 38 , 'S005M002' , '货品管理' , '' , 'Module/BasicInfo/GoodsManage.aspx' , 1 , 1 , 0 , 1 , '/38/40/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 0 , 'S006' , '报表中心' , '' , '' , 1 , 6 , 0 , 0 , '/41/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 39 , 'S005M001F001' , '新建' , '' , '' , 1 , 1 , 1 , 2 , '/38/39/42/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 39 , 'S005M001F002' , '修改' , '' , '' , 1 , 1 , 1 , 2 , '/38/39/43/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 39 , 'S005M001F003' , '删除' , '' , '' , 1 , 1 , 1 , 2 , '/38/39/44/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 40 , 'S005M002F001' , '新建' , '' , '' , 1 , 1 , 1 , 2 , '/38/40/45/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 40 , 'S005M002F002' , '修改' , '' , '' , 1 , 1 , 1 , 2 , '/38/40/46/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 40 , 'S005M002F003' , '删除' , '' , '' , 1 , 1 , 1 , 2 , '/38/40/47/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 36 , 'S004M001F001' , '新建' , '' , '' , 1 , 1 , 1 , 2 , '/35/36/48/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 36 , 'S004M001F002' , '修改' , '' , '' , 1 , 1 , 1 , 2 , '/35/36/49/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 36 , 'S004M001F003' , '删除' , '' , '' , 1 , 1 , 1 , 2 , '/35/36/50/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 37 , 'S004M002F001' , '新建' , '' , '' , 1 , 1 , 1 , 2 , '/35/37/51/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 37 , 'S004M002F002' , '修改' , '' , '' , 1 , 1 , 1 , 2 , '/35/37/52/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 37 , 'S004M002F003' , '删除' , '' , '' , 1 , 1 , 1 , 2 , '/35/37/53/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 41 , 'S006M001' , '入库查询' , '' , 'Module/Statistics/In.aspx' , 1 , 1 , 0 , 1 , '/41/54/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 41 , 'S006M002' , '出库查询' , '' , 'Module/Statistics/Out.aspx' , 1 , 1 , 0 , 1 , '/41/55/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 41 , 'S006M003' , '库存查询' , '' , 'Module/Statistics/Stock.aspx' , 1 , 1 , 0 , 1 , '/41/56/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 41 , 'S006M004' , '出入单查询' , '' , 'Module/Statistics/InAndOut.aspx' , 1 , 1 , 0 , 1 , '/41/57/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 41 , 'S006M005' , '按月份入库查询' , '' , 'Module/Statistics/MonthIn.aspx' , 1 , 1 , 0 , 1 , '/41/58/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 41 , 'S006M006' , '按月份出库查询' , '' , 'Module/Statistics/MonthOut.aspx' , 1 , 1 , 0 , 1 , '/41/59/' )
 INSERT [sys_Functions] (  [ParentID] , [FunctionCode] , [FunctionName] , [Description] , [Url] , [IsShow] , [OrderID] , [IsSystem] , [depth] , [lineage] ) VALUES ( 6, 'S002M004' , '数据库备份与还原' , '' , 'Module/SysMaintenance/SystemBackup.aspx' , 1 , 1 , 0 , 1 , '/6/60/' )
 GO

CREATE TABLE [sys_Log] (
	[LogID] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL ,
	[UserID] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[LonginTime] [datetime] NULL ,
	[IP] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Description] [varchar] (300) COLLATE Chinese_PRC_CI_AS NULL ,
	[Page] [varchar] (300) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [sys_RolePrivilege] (
	[RPID] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL ,
	[RoleID] [smallint] NULL ,
	[FunctionCode] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[Range] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [sys_Roles] (
	[RoleID] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL ,
	[RoleName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Description] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

INSERT [sys_Roles] ( [RoleName] , [Description] ) VALUES ('管理员' , '' )
INSERT [sys_Roles] ( [RoleName] , [Description] ) VALUES ('采购员' , '' )
GO

CREATE TABLE [sys_UserPrivilege] (
	[PermissionID] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL ,
	[UserID] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[FunctionCode] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[Range] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [sys_Users] (
	[UID] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL ,
	[UserID] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[RealName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Password] [char] (32) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[IsLocked] [bit] NULL ,
	[DepID] [int] NULL ,
	[LastLoginTime] [datetime] NULL ,
	[LoginTimes] [int] NULL ,
	[IsSuper] [int] NULL ,
	[RoleID] [int] NULL 
) ON [PRIMARY]
GO

INSERT [sys_Users] ( [UserID] , [RealName] , [Password] , [IsLocked] , [DepID] , [LastLoginTime] , [LoginTimes] , [IsSuper] , [RoleID] ) VALUES ( 'admin' , '管理员' , '123456' , 0 , 1 , getdate() , 0 , 1 , 1)
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW vw_GoodsIn
AS
SELECT Goods.GoodsName, Goods.GoodsID, GoodsIn.AddTime, 
      GoodsIn.Number, GoodsIn.InPrice, GoodsIn.CaiGouID, 
      GoodsIn.PersonID, GoodsIn.SupplyName, GoodsIn.ContactMan, 
      GoodsIn.InID
FROM GoodsIn INNER JOIN
      Goods ON GoodsIn.GoodsID = Goods.GoodsID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW vw_GoodsOut
AS
SELECT Goods.GoodsName, Goods.GoodsID, GoodsOut.OutID, 
      GoodsOut.AddTime, GoodsOut.LingYongID, GoodsOut.DepartmentID, 
      GoodsOut.Number, GoodsOut.PersonID, GoodsOut.OutPrice
FROM Goods INNER JOIN
      GoodsOut ON Goods.GoodsID = GoodsOut.GoodsID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Proc Proc_AysnUserPrivilegeByGroup
	@RoleID int
as

begin

	SET NOCOUNT ON

	declare @Cursor cursor
	set @Cursor = cursor for select UserID,IsSuper from sys_Users where RoleID=@RoleID
	open @Cursor
	declare @UserID varchar(100)
	declare @IsSuper int
	Fetch Next from @Cursor into @UserID,@IsSuper
	while @@Fetch_Status = 0
		begin
			if @IsSuper = 0
			Begin
				
				DELETE from sys_UserPrivilege where UserID=@UserID
				declare @PCursor cursor
				set @PCursor = cursor for select FunctionCode,Range from sys_RolePrivilege where RoleID=@RoleID
				open @PCursor
				declare @FunctionCode varchar(100)
				declare @Range int
				Fetch Next from @PCursor into @FunctionCode,@Range
				while @@Fetch_Status = 0
					begin
						insert into sys_UserPrivilege ([UserID],[FunctionCode],[Range]) values(@UserID,@FunctionCode,@Range)
						Fetch Next from @PCursor into @FunctionCode,@Range
					end
				close @PCursor
			end
				
			
			Fetch Next from @Cursor into @UserID,@IsSuper

		End
	close @Cursor

	SET NOCOUNT OFF
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [Proc_CheckDepartMentExist] 
	@DepName nvarchar(50)
AS
BEGIN

	SET NOCOUNT ON;
	declare @IsExists bit

	if Exists(Select [DepID] from sys_Department where DepName=@DepName)
		set @IsExists = 1
	else
		set @IsExists = 0
	select @IsExists
	SET NOCOUNT OFF;
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [Proc_CheckDropDownFiledExist] 
	@DropFiled_Name varchar(50),
	@DropFiled_Value varchar(50)
AS
BEGIN

	SET NOCOUNT ON;
	declare @IsExists bit

	if Exists(Select [DropFiled_ID] from DropDownFiled where DropFiled_Name=@DropFiled_Name and DropFiled_Value=@DropFiled_Value)
		set @IsExists = 1
	else
		set @IsExists = 0
	select @IsExists
	SET NOCOUNT OFF;
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

create PROCEDURE [Proc_CheckFunctionCodeExist] 
	@FunctionCode varchar(100)
AS
BEGIN

	SET NOCOUNT ON;
	declare @IsExists bit

	if Exists(Select [FunctionID] from sys_Functions where FunctionCode=@FunctionCode)
		set @IsExists = 1
	else
		set @IsExists = 0
	select @IsExists
	SET NOCOUNT OFF;
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create PROCEDURE [Proc_CheckRoleNameExist] 
	@RoleName nvarchar(50)
AS
BEGIN

	SET NOCOUNT ON;
	declare @IsExists bit

	if Exists(Select [RoleID] from sys_Roles where RoleName=@RoleName)
		set @IsExists = 1
	else
		set @IsExists = 0
	select @IsExists
	SET NOCOUNT OFF;
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create PROCEDURE [Proc_CheckUserNameExist] 
	@UserName nvarchar(50)
AS
BEGIN

	SET NOCOUNT ON;
	declare @IsExists bit

	if Exists(Select [UserID] from sys_Users where UserID=@UserName)
		set @IsExists = 1
	else
		set @IsExists = 0
	select @IsExists
	SET NOCOUNT OFF;
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [Proc_GetPermissionByUserID]
	@UserID varchar(100)
AS


SELECT

	[FunctionCode]
FROM
	[sys_UserPrivilege]
WHERE
	[UserID] = @UserID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [Proc_GetPermissionCodeByUrl]
	@Url varchar(300)
AS


SELECT

	[FunctionCode]
FROM
	[sys_Functions]
WHERE
	[Url] = @Url

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE  Proc Proc_MemberLogin
	@UserID varchar(100),
	@Password varchar(50),
	@Result int output
as
	if (@UserID = '' or @Password='')
		set @Result = -1
	else
			
			begin
				set @Result = -1
				declare @T_Pass char(32)
				declare @IsLocked bit
				
				select @T_Pass = [Password],@IsLocked=IsLocked from sys_Users where  [UserID]=@UserID
						if @@Rowcount = 0
							begin
								set @Result = -1  ---用户不存在
							end
						else
							if @T_Pass <> @Password
								begin
									set @Result = 0   ----密码错误
								end
							else
								begin
									if @IsLocked = 1 
										begin
										set @Result = 2    ----用户名已锁定
										end
									else
										begin															
											update sys_Users set LastLoginTime=getdate(),LoginTimes=LoginTimes+1 where [UserID]=@UserID and [Password]=@Password
											set @Result = 1
										 end
								end
			end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


/* 创建过程/函数 通用分页存储过程。                                                                          */
--通用分页存储过程
---------------------------------------------------------------------
CREATE  PROCEDURE Proc_PageQuery
	@tblName varchar(255), 			---- 表名 
	@strGetFields varchar(1000) = '*', 	---- 需要返回的列 
	@fldName varchar(255)='', 		---- 排序的字段名 
	@PageSize int = 10, 			---- 页尺寸 
	@PageIndex int = 1, 			---- 页码 
	@doCount bit = 0, 			---- 返回记录总数, 1 值则返回 
	@OrderType bit = 0, 			---- 设置排序类型, 1 值则降序 
	@strWhere varchar(8000) = '' 		---- 查询条件 (注意: 不要加 where) 
AS 
	declare @strSQL varchar(5000) 		----- 主语句 
	declare @strTmp varchar(110) 		----- 临时变量 
	declare @strOrder varchar(400) 		----- 排序类型 
	if Len(@strWhere) != 0 
		set @strWhere = '(' + @strWhere + ')'
	if @doCount != 0 			----- 统计总数
		begin 
			if @strWhere !='' 
				set @strSQL = 'select count(*) as Total from [' + @tblName + '] where '+@strWhere 
			else 
				set @strSQL = 'select count(*) as Total from [' + @tblName + ']' 
		end
	else 					------------------- 返回记录
		begin 
			if @OrderType != 0 	------------------ 降序
				begin 
					set @strTmp = '<(select mi' 
					set @strOrder = ' order by [' + @fldName +'] desc' 
				end 
			else 			------------------ 升序
				begin 
					set @strTmp = '>(select max' 
					set @strOrder = ' order by [' + @fldName +'] asc' 
				end 
			if @PageIndex = 1 	-----------------如果是第一页就执行以下代码，这样会加快执行速度 
				begin 
					if @strWhere != '' 	------------- 条件查询
						set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder 
					else 			-------------- 无条件查询
						set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['+ @tblName + '] '+ @strOrder 
					
				end 
			else 			------------ 非第一页查询
				begin 
					---------以下代码赋予了@strSQL以真正执行的SQL代码 
					set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['
							+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
							+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
							+ @fldName + '] from [' + @tblName + ']' 
							+ @strOrder + ') as tblTmp)'+ @strOrder 
					if @strWhere != '' 
						set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' 
							+ @tblName + '] where [' + @fldName + ']' + @strTmp + '([' 
							+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' 
							+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' ' 
							+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder 
				end 
		end 
exec (@strSQL)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE [Proc_UpdatePwd_sys_Users]
	@UserID varchar(100),
	@UserPwd varchar(50)
	
AS
	declare @ReturnValue int
	set @ReturnValue = -1
	update sys_Users set[Password]=@UserPwd Where [UserID]=@UserID
	
	SET @ReturnValue = @@Rowcount
	select @ReturnValue
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [proc_Delete_sys_Department]
	@DepID int
AS

SET NOCOUNT ON

DELETE FROM [sys_Department]
WHERE
	[DepID] = @DepID

SET NOCOUNT OFF
--endregion



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [proc_Delete_sys_Function]
	@FunctionID int
AS

SET NOCOUNT ON

		 --删除该模块及下属子模块相关的用户权限
		delete from sys_UserPrivilege where [FunctionCode] in (select [FunctionCode] from sys_Functions where [FunctionID]=@FunctionID)
		--删除该模块及下属子模块相关的用户角色权限 
		delete from sys_RolePrivilege where [FunctionCode] in (select [FunctionCode] from  sys_Functions where [FunctionID]=@FunctionID)
		 --同时删除该模块及下属子模块
		delete from sys_Functions where [ParentID] in(select FunctionID from sys_Functions where [ParentID]=@FunctionID)
		delete from sys_Functions where  [ParentID]=@FunctionID
		delete from sys_Functions where  [FunctionID]=@FunctionID
		
		
SET NOCOUNT OFF
--endregion
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [proc_Delete_sys_Log]
	@LogID int
AS

SET NOCOUNT ON

DELETE FROM [sys_Log]
WHERE
	[LogID] = @LogID

SET NOCOUNT OFF
--endregion



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [proc_Delete_sys_Role]
	@RoleID int
AS

SET NOCOUNT ON

DELETE FROM [sys_Roles]
WHERE
	[RoleID] = @RoleID

DELETE FROM [sys_RolePrivilege]
WHERE
	[RoleID] = @RoleID

SET NOCOUNT OFF
--endregion

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [proc_Delete_sys_RolePrivilege]
	@RoleID int
AS

SET NOCOUNT ON

DELETE FROM [sys_RolePrivilege]
WHERE
	[RoleID] = @RoleID

SET NOCOUNT OFF
--endregion

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [proc_Delete_sys_User]
	@UserID varchar(100)
AS

SET NOCOUNT ON

--删除该用户相关的权限
delete from sys_UserPrivilege where [UserID]=@UserID
		
--删除该用户数据
delete from sys_Users where [UserID]=@UserID

SET NOCOUNT OFF
--endregion

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [proc_Delete_sys_UserPrivilege]
	@UserID varchar(100)
AS

SET NOCOUNT ON

DELETE FROM [sys_UserPrivilege]
WHERE
	[UserID] = @UserID

SET NOCOUNT OFF
--endregion

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [proc_GetObject_DropDownFiled]
	@DropFiled_ID int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	*
FROM
	[DropDownFiled]
WHERE
	[DropFiled_ID] = @DropFiled_ID

SET NOCOUNT OFF
--endregion
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [proc_GetObject_sys_Department]
	@DepID int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[DepID],
	[DepName],
	[ParentID]
FROM
	[sys_Department]
WHERE
	[DepID] = @DepID

SET NOCOUNT OFF
--endregion



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE [proc_GetObject_sys_Function]
	@FunctionID int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[FunctionID],
	[ParentID],
	[FunctionCode],
	[FunctionName],
	[Description],
	[Url],
	[IsShow],
	[OrderID],
	[IsSystem],
	[depth],
	[lineage]
FROM
	[sys_Functions]
WHERE
	[FunctionID] = @FunctionID

SET NOCOUNT OFF
--endregion
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [proc_GetObject_sys_Log]
	@LogID int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[LogID],
	[UserID],
	[LonginTime],
	[IP],
	[Description],
	[Page]
FROM
	[sys_Log]
WHERE
	[LogID] = @LogID

SET NOCOUNT OFF
--endregion



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [proc_GetObject_sys_Role]
	@RoleID int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[RoleID],
	[RoleName],
	[Description]
FROM
	[sys_Roles]
WHERE
	[RoleID] = @RoleID

SET NOCOUNT OFF
--endregion



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [proc_GetObject_sys_RolePrivilege]
	@RoleID smallint
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[RPID],
	[RoleID],
	[FunctionCode],
	[Range]
FROM
	[sys_RolePrivilege]
WHERE
	[RoleID] =@RoleID

SET NOCOUNT OFF
--endregion

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [proc_GetObject_sys_User]
	@UserID varchar(100)
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[UID],
	[UserID],
	[RealName],
	[Password],
	[IsLocked],
	[DepID],
	[LastLoginTime],
	[LoginTimes],
	[IsSuper],
	[RoleID]
FROM
	[sys_Users]
WHERE
	[UserID] = @UserID

SET NOCOUNT OFF
--endregion

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [proc_GetObject_sys_UserPrivilege]
	@UserID varchar(100),
	@FunctionCode varchar(100)
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[Range]
FROM
	[sys_UserPrivilege]
WHERE
	[UserID] = @UserID and  [FunctionCode]=@FunctionCode
	

SET NOCOUNT OFF
--endregion

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [proc_Insert_DropDownFiled]
	@DropFiled_Name varchar(50),
	@DropFiled_Value varchar(50)
AS

SET NOCOUNT ON

declare @ReturnValue int
set @ReturnValue = -1
INSERT INTO [DropDownFiled] (
	[DropFiled_Name],
	[DropFiled_Value]
) VALUES (
	@DropFiled_Name,
	@DropFiled_Value
)

SET @ReturnValue = SCOPE_IDENTITY()
select @ReturnValue

--endregion
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [proc_Insert_sys_Department]
	@DepName nvarchar(50),
	@ParentID int
AS

SET NOCOUNT ON

declare @ReturnValue int
set @ReturnValue = -1
INSERT INTO [sys_Department] (
	[DepName],
	[ParentID]
) VALUES (
	@DepName,
	@ParentID
)

SET @ReturnValue = SCOPE_IDENTITY()
select @ReturnValue

--endregion

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [proc_Insert_sys_Function]
	@ParentID int,
	@FunctionCode varchar(100),
	@FunctionName nvarchar(50),
	@Description nvarchar(200),
	@Url varchar(255),	
	@IsShow bit,
	@OrderID int,
	@IsSystem bit
AS

SET NOCOUNT ON

declare @ReturnValue int
set @ReturnValue = -1
INSERT INTO [sys_Functions] (
	[ParentID],
	[FunctionCode],
	[FunctionName],
	[Description],
	[Url],
	[IsShow],
	[OrderID],
	[IsSystem]
) VALUES (
	@ParentID,
	@FunctionCode,
	@FunctionName,
	@Description,
	@Url,
	@IsShow,
	@OrderID,
	@IsSystem
)

SET @ReturnValue = SCOPE_IDENTITY()
select @ReturnValue

--endregion
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [proc_Insert_sys_Log]
	@UserID varchar(100),
	@LonginTime datetime,
	@IP varchar(50),
	@Description varchar(300),
	@Page varchar(300)
AS

SET NOCOUNT ON
declare @ReturnValue int
set @ReturnValue = -1
INSERT INTO [sys_Log] (
	[UserID],
	[LonginTime],
	[IP],
	[Description],
	[Page]
) VALUES (
	@UserID,
	@LonginTime,
	@IP,
	@Description,
	@Page
)
SET @ReturnValue = SCOPE_IDENTITY()
select @ReturnValue

--endregion

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [proc_Insert_sys_Role]
	@RoleName nvarchar(50),
	@Description nvarchar(255)
AS

SET NOCOUNT ON
declare @ReturnValue int
set @ReturnValue = -1
INSERT INTO [sys_Roles] (
	[RoleName],
	[Description]
) VALUES (
	@RoleName,
	@Description
)


SET @ReturnValue = SCOPE_IDENTITY()
select @ReturnValue
--endregion

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [proc_Insert_sys_RolePrivilege]
	@RoleID smallint,
	@FunctionCode varchar(100),
	@Range int
AS

SET NOCOUNT ON
declare @ReturnValue int
set @ReturnValue = -1
INSERT INTO [sys_RolePrivilege] (
	[RoleID],
	[FunctionCode],
	[Range]
) VALUES (
	@RoleID,
	@FunctionCode,
	@Range
)

SET @ReturnValue = SCOPE_IDENTITY()
select @ReturnValue

--endregion

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [proc_Insert_sys_User]
	@UserID varchar(100),
	@RealName nvarchar(50),
	@Password char(32),
	@IsLocked bit,
	@DepID int,
	@LastLoginTime datetime,
	@LoginTimes int,
	@IsSuper int,
	@RoleID int
AS

SET NOCOUNT ON

declare @ReturnValue int
set @ReturnValue = -1
INSERT INTO [sys_Users] (
	[UserID],
	[RealName],
	[Password],
	[IsLocked],
	[DepID],
	[LastLoginTime],
	[LoginTimes],
	[IsSuper],
	[RoleID]
) VALUES (
	@UserID,
	@RealName,
	@Password,
	@IsLocked,
	@DepID,
	@LastLoginTime,
	@LoginTimes,
	@IsSuper,
	@RoleID
)

SET @ReturnValue = @@RowCount
select @ReturnValue
--endregion
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [proc_Insert_sys_UserPrivilege]
	@UserID varchar(100),
	@FunctionCode varchar(100),
	@Range int
AS

SET NOCOUNT ON
declare @ReturnValue int
set @ReturnValue = -1
INSERT INTO [sys_UserPrivilege] (
	[UserID],
	[FunctionCode],
	[Range]
) VALUES (
	@UserID,
	@FunctionCode,
	@Range
)

SET @ReturnValue = SCOPE_IDENTITY()
select @ReturnValue
--endregion

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [proc_Update_DropDownFiled]
	@DropFiled_ID int,
	@DropFiled_Name varchar(50),
	@DropFiled_Value varchar(50)
AS

SET NOCOUNT ON
UPDATE [DropDownFiled] SET
		[DropFiled_Name] = @DropFiled_Name,
		[DropFiled_Value] = @DropFiled_Value
WHERE
		[DropFiled_ID] = @DropFiled_ID
SET NOCOUNT OFF
--endregion
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [proc_Update_sys_Department]
	@DepID int,
	@DepName nvarchar(50),
	@ParentID int
AS

SET NOCOUNT ON
UPDATE [sys_Department] SET
		[DepName] = @DepName,
		[ParentID] = @ParentID
WHERE
		[DepID] = @DepID
SET NOCOUNT OFF
--endregion



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [proc_Update_sys_Function]
	@FunctionID int,
	@ParentID int,
	@FunctionCode varchar(100),
	@FunctionName nvarchar(50),
	@Description nvarchar(200),
	@Url varchar(255),
	@IsShow bit,
	@OrderID int,
	@IsSystem bit
AS

SET NOCOUNT ON
UPDATE [sys_Functions] SET
		[ParentID] = @ParentID,
		[FunctionName] = @FunctionName,
		[Description] = @Description,
		[Url] = @Url,
		[IsShow] = @IsShow,
		[OrderID] = @OrderID,
		[IsSystem]=@IsSystem
WHERE
		[FunctionID] = @FunctionID
SET NOCOUNT OFF
--endregion

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [proc_Update_sys_Role]
	@RoleID int,
	@RoleName nvarchar(50),
	@Description nvarchar(255)
AS

SET NOCOUNT ON
UPDATE [sys_Roles] SET
		[RoleName] = @RoleName,
		[Description] = @Description
WHERE
		[RoleID] = @RoleID
SET NOCOUNT OFF
--endregion



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [proc_Update_sys_RolePrivilege]
	@RPID int,
	@RoleID smallint,
	@FunctionCode varchar(100),
	@Range int
AS

SET NOCOUNT ON
UPDATE [sys_RolePrivilege] SET
		[RoleID] = @RoleID,
		[FunctionCode] = @FunctionCode,
		[Range] = @Range
WHERE
		[RPID] = @RPID
SET NOCOUNT OFF
--endregion



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [proc_Update_sys_User]
	@UID int,
	@UserID  varchar(100),
	@RealName nvarchar(50),
	@Password char(32),
	@IsLocked bit,
	@DepID int,
	@LastLoginTime datetime,
	@LoginTimes int,
	@IsSuper int,
	@RoleID int
AS

SET NOCOUNT ON
UPDATE [sys_Users] SET
		[RealName] = @RealName,
		[Password] = @Password,
		[IsLocked] = @IsLocked,
		[DepID] = @DepID,
		[LastLoginTime] = @LastLoginTime,
		[LoginTimes] = @LoginTimes,
		[IsSuper]=@IsSuper,
		[RoleID]=@RoleID
WHERE
		[UID] = @UID
SET NOCOUNT OFF
--endregion
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [proc_Update_sys_UserPrivilege]
	@PermissionID int,
	@UserID int,
	@FunctionCode varchar(100),
	@Range int
AS

SET NOCOUNT ON
UPDATE [sys_UserPrivilege] SET
		[UserID] = @UserID,
		[FunctionCode] = @FunctionCode,
		[Range] = @Range
WHERE
		[PermissionID] = @PermissionID
SET NOCOUNT OFF
--endregion



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create TRIGGER [Tr_sys_Functions_Insert] ON sys_Functions
FOR INSERT AS
UPDATE child
-- 设置子级深度=父级深度+1
SET depth = ISNULL(parent.depth + 1,0),
-- 目录路径=父级目录路径+子级ID+'/'
lineage = ISNULL(parent.lineage,'/') + LTrim(Str(child.FunctionID)) + '/'
-- we ca't update the "inserted" table directly,
-- so we find the corresponding child in the
-- "real" table 

FROM sys_Functions child INNER JOIN inserted i ON i.FunctionID=child.FunctionID
-- now, we attempt to find the parent of this
-- "child" - but it might not exist, so these
-- values may well be NULL
LEFT OUTER JOIN sys_Functions parent ON child.parentId=parent.FunctionID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create TRIGGER [Tr_sys_Functions_Update]
ON sys_Functions
FOR UPDATE AS
-- if we've modified the parentId, then we
-- need to do some calculations
IF UPDATE (parentId)
UPDATE child
-- to calculate the correct depth of a node, remember that
-- - old.depth is the depth of its old parent
-- - child.depth is the original depth of the node
-- we're looking at before a parent node moved.
-- note that this is not necessarily old.depth + 1,
-- as we are looking at all depths below the modified
-- node
-- the depth of the node relative to the old parent is
-- (child.depth - old.depth), then we simply add this to the
-- depth of the new parent, plus one.
SET depth = child.depth - old.depth + ISNULL(parent.depth + 1,0),
lineage = ISNULL(parent.lineage,'/') + LTrim(Str(old.FunctionID)) + '/' +
right(child.lineage, len(child.lineage) - len(old.lineage))
-- if the parentId has been changed for some row
-- in the "inserted" table, we need to update the
-- fields in all children of that node, and the
-- node itself
FROM sys_Functions child
INNER JOIN inserted old ON child.lineage LIKE old.lineage + '%'
-- as with the insert trigger, attempt to find the parent
-- of the updated row
LEFT OUTER JOIN sys_Functions parent ON old.parentId=parent.FunctionID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

