ansi__database.sql

扬州升瑞售楼系统2000扬州升瑞售楼系统2000扬州升瑞售楼系统2000扬州升瑞售楼系统2000扬州升瑞售楼系统2000

     if @NumOne=7         update userRoles set 销售管理=@MemberPower where 用户名=@MemberName

                              
                                select @Succey='Add'

                          end
end
if @AddOrSub='Sub'
begin
----减权
     if @NumOne=1         update userRoles set 财务管理='' where 用户名=@MemberName 
     if @NumOne=2         update userRoles set 房源信息管理='' where 用户名=@MemberName 
     if @NumOne=3         update userRoles set 分析查询管理='' where 用户名=@MemberName 
     if @NumOne=4         update userRoles set 价格管理='' where 用户名=@MemberName  
     if @NumOne=5         update userRoles set 物业管理='' where 用户名=@MemberName 
     if @NumOne=6         update userRoles set 项目展示='' where 用户名=@MemberName  
     if @NumOne=7         update userRoles set 销售管理='' where 用户名=@MemberName
      select @Succey='Sub'
end

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.procDeleteMember_GivePower    Script Date: 2002-01-30 16:24:09 ******/
CREATE proc procDeleteMember_GivePower
            @MemberName varchar(20),
            @ElectDeleted varchar(12) output  --删除成功时返回‘成功’
As
               select @ElectDeleted='不能成功删除'          
               Declare @a int,@b int
               select @a=count(*) from comember where 姓名=@MemberName  
               if @a=0 select @ElectDeleted='没有这个用户'  
               if @a=1 
                       begin
                             begin  tran DeleteData
                                        delete  from comember where 姓名=@MemberName
                                    --     select @b=count(*) from UserRoles where 用户名=@MemberName
                                    --      if @b=0    --角色表中没有这个用户
                                    --      if @b=1         
                                    --                   begin 
                                                       delete from UserRoles where 用户名=@MemberName
                                    --                   end 
                                         select @ElectDeleted='删除成功！'
                             commit transaction
                       end  

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.procInsertMember_GivePower    Script Date: 2002-01-30 16:24:09 ******/
CREATE Proc procInsertMember_GivePower
         @MemberName varchar(20),
         @MemberSex varchar(2),
         @FamilyTel varchar(20),
         @MoveTel varchar(12),
         @Adder varchar(50),
         @BeginWorkTime datetime,
         @MemberPhoto  image,
         --加入到数据角色表中的参数
         @YesOrNoRoles varchar(2),    --表明是否要加入到角色表中"是"就加入，“否”就不加入
         @PassWord varchar(10),
         @One varchar(10),
         @Two varchar(10),
         @Thr varchar(10),
         @Four varchar(10),
         @Five varchar(10),
         @Six varchar(10),
         @Seven varchar(10),

         @Inserted Varchar(20) output   --如成功加入到数据库中返回 1，不成功返回 0.
As
   
   Declare @a int
   select @inserted=
       case when len(rtrim(@MemberName))>20 or len(rtrim(@memberName))<=0  then '姓名不正确'  
            when len(rtrim(@MemberSex))>2                                  then '性别不正确'               
            when len(rtrim(@FamilyTel))>20                                 then '电话号码大于了20个字符'
            when len(rtrim(@MoveTel))>12                                   then '手机号码大于了12个字符'
            when len(rtrim(@Adder))>20                                     then '地址大于了50个字符'
            ELSE '成功'
      -- case when len(rtrim(@BeginWorkTime))>20                             then '地址大于了50个字符'
       end
   
   if @inserted='成功' 
   begin
        select @a=count(*) from coMember where 姓名=@MemberName
        if @a>0               select @inserted='这个姓名已有'
        if @a=0               
                     begin     
                     begin tran InsertData
                     --加入新数据
                     insert into coMember(姓名,性别,家庭电话,手机号码,联系地址,工作时间,照片) values(@MemberName,@MemberSex,@FamilyTel,@MoveTel,@Adder,@BeginWorkTime,@MemberPhoto)
                     --加入到角色表中
                     if @YesOrNoRoles='是'      insert into UserRoles(用户名,密码,财务管理,房源信息管理,分析查询管理,价格管理,物业管理,项目展示,销售管理) values(@MemberName,@Password,@One,@two,@thr,@four,@five,@six,@seven)

                     commit tran  
                     end   
   end 

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.procUpdateMember_GivePower    Script Date: 2002-01-30 16:24:09 ******/
--姓名不可修改
CREATE proc procUpdateMember_GivePower
         @MemberName varchar(20),
         @MemberSex varchar(2),
         @FamilyTel varchar(20),
         @MoveTel varchar(12),
         @Adder varchar(50),
         @BeginWorkTime datetime,
         @MemberPhoto  image,
         --加入到数据角色表中的参数
         @YesOrNoRoles varchar(2),    --表明是否要加入到角色表中"是"就加入，“否”就不加入
         @PassWord varchar(10),
         @One varchar(10),
         @Two varchar(10),
         @Thr varchar(10),
         @Four varchar(10),
         @Five varchar(10),
         @Six varchar(10),
         @Seven varchar(10),

         @Inserted Varchar(20) output 
As
Declare @a int,@b int
   select @inserted=
       case when len(rtrim(@MemberSex))>2                                  then '性别不正确'               
            when len(rtrim(@FamilyTel))>20                                 then '电话号码大于了20个字符'
            when len(rtrim(@MoveTel))>12                                   then '手机号码大于了12个字符'
            when len(rtrim(@Adder))>20                                     then '地址大于了50个字符'
            ELSE '成功'
      -- case when len(rtrim(@BeginWorkTime))>20                             then '地址大于了50个字符'
       end
   
   if @inserted='成功' 
   begin
        select @a=count(*) from coMember where 姓名=@MemberName
            if @a>0               update coMember set 性别=@MemberSex,家庭电话=@FamilyTel,手机号码=@MoveTel,联系地址=@Adder,工作时间=@BeginWorkTime,照片=@MemberPhoto where 姓名=@MemberName
            if @a=0               select @inserted='没有这个姓名'   
        if @YesOrNoRoles='是'      
                                begin 
                                   select @b=count(*) from UserRoles where 用户名=@MemberName
                                   if @b=0  insert into UserRoles(用户名,密码,财务管理,房源信息管理,分析查询管理,价格管理,物业管理,项目展示,销售管理) values(@MemberName,@Password,@One,@two,@thr,@four,@five,@six,@seven) --角色成员中没有这个用户，加入新角色成员
                                   if @b=1  update UserRoles set 密码=@Password,财务管理=@One,房源信息管理=@Two,分析查询管理=@Thr,价格管理=@Four,物业管理=@Five,项目展示=@Six,销售管理=@Seven where 用户名=@MemberName --角色成员中有这个用户，更新用户数据
                                end
   end 

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO