cretrg.txt

北京铁路局住房分配系统,数据库为MSSql2000,依次执行crebas4.sql

As
    Set NoCount On
    Set @nRet=0
    Begin Transaction P_UserDel
    If Exists(Select * From userid Where userid=@userid) 
      Set @nRet= 5
    If @nRet=0 
       Delete From userid Where userid=@userid
    Set @nRet=@@Error
    If @nRet =0 
       Commit Transaction P_UserDel
    Else 
       Rollback Transaction P_UserDel
    Return
--结束
go



CREATE PROCEDURE P_UserLogin
    @UserId Char(8)='',
    @UserPassword Char(10)='',
    @UserName Char(20)='' Out,
    @UserPurview Char(20)='00000000000000000000'  Out,
    @UserType Tinyint=0 Out,
    @nRet Smallint=0 Out
    --@nRet ： 0 正确，1 用户名或密码错误，2 用户已登录，3 用户禁用，4 用户名正确但无法正常保存登录标志
--With Encryption
AS
    Set NoCount On
    Declare  @pwd char(10)
    set @nRet =0
    Select @UserName = UserName,
            @UserPurview = UserPurview,
            @UserType = UserType,
            @pwd =UserPassword
        From Userid
        Where Userid=@Userid --And UserPassword=@UserPassword

    If @UserName = Null  --用户名或密码错误
        Select @nRet=1, @username='',@userpurview='',@usertype=0
    Else
     if @pwd <> @userpassword 
        set @nret =2
    else
    if @pwd = @userpassword
       set @nret=0     
    else 
       set @nRet=8

    Return
-- P_UserLogin 结束
go



CREATE PROCEDURE P_UserSave
    @UserId Char(8)='',
    @UserName Char(20)='',
    @UserPassword Char(10)='',
    @UserPurview Char(50)='',
    @UserType tinyint ,
    @nRet Smallint =0 Out
--With Encryption
AS
    Set NoCount On
   If Exists(Select * From userid  Where userid=@userid)
     Begin
        Set @nRet=1
        Begin Transaction P_UserSave
          Update userid Set username=@username, userpassword=@userpassword,userpurview=@userpurview  Where userid=@userid
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_UserSave
          Else
            Commit Transaction P_UserSave
      End
    Else
      Begin
        Begin Transaction P_UserSave
          Insert Into userid(userid,username,userpassword,userpurview,usertype)  
           Values (@userid,@username,@userpassword,@userpurview,@usertype)
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_UserSave
          Else
            Commit Transaction P_UserSave
      End
    Return
--结束P_UserSave
go



CREATE PROCEDURE P_XQXX_Del
    @XQBH Char(3)='',  --分配途径编号
    @nRet Smallint =0 Out  --返回值
--With Encryption
As
    Set NoCount On
    Set @nRet=0
    Begin Transaction P_XQXX_Del
    If Exists(Select * From lyxxb  Where xqbh=@XQBH) 
      Set @nRet= 1
    If @nRet=0 
        Delete From xqxxb  Where XQBH =@XQBH
    Set @nRet=@@Error
    If @nRet =0 
          Commit Transaction P_XQXX_Del
    Else 
          Rollback Transaction P_XQXX_Del
    Return
--结束
go



CREATE PROCEDURE P_Xqxx_Save
    @XQBH Char(3)='',  --小区编号
    @XQMC Char(20)='', --小区名称
    @DZ Char(40)='',   --地址
    @nRet Smallint =0 Out  --返回值

--With Encryption
As
   Set NoCount On
   If Exists(Select * From xqxxb  Where XQBH=@XQBH)
     Begin
        Set @nRet=1
        Begin Transaction P_Xqxx_Save
          Update xqxxb Set XQMC=@XQMC, DZ=@DZ   Where XQBH=@XQBH
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_Xqxx_Save
          Else
            Commit Transaction P_Xqxx_Save
      End
    Else
      Begin
        Begin Transaction P_Xqxx_Save
          Insert Into xqxxb(XQBH,XQMC,DZ)  Values (@XQBH,@XQMC,@DZ)
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_Xqxx_Save
          Else
            Commit Transaction P_Xqxx_Save
      End
    Return
--结束
go



CREATE PROCEDURE P_YTFPB_Del
   @DWBH Char(3)='',      --住房编号
   @nRet Smallint =0 Out  --返回值
--With Encryption
As
    Set NoCount On
    Set @nRet=0
    Begin Transaction P_YTFPB_Del
        Delete From ytfpb  Where DWBH =@DWBH
    Set @nRet=@@Error
    If @nRet =0 
      Commit Transaction P_YTFPB_Del
    Else 
      Rollback Transaction P_YTFPB_Del
    Return
--结束
go



--select * from ytfpb
CREATE PROCEDURE P_YTFPB_Save
    @DWBH Char(3)='',  --单位编号
    @ZGFP smallint=0,  --职工分配
    @JLFP smallint=0,  --奖励分配
    @CQFP smallint=0,  --拆迁分配
    @LDGB smallint=0,  --领导干部
    @SHYF smallint=0,  --社会用房
    @GYFW smallint=0,  --公用房屋
    @JDFW smallint=0,  --机动房屋
    @QTYF smallint=0,  --其他用房
    @nRet Smallint =0 Out  --返回值

--With Encryption
As
   Set NoCount On
   If Exists(Select * From ytfpb Where DWBH=@DWBH)
     Begin
        Set @nRet=1
        Begin Transaction P_YTFPB_Save
          Update ytfpb Set ZGFP=@ZGFP,JLFP=@JLFP,CQFP=@CQFP,LDGB=@LDGB,SHYF=@SHYF,GYFW=@GYFW,JDFW=@JDFW,QTYF=@QTYF
            Where DWBH=@DWBH
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_YTFPB_Save
          Else
            Commit Transaction P_YTFPB_Save
      End
    Else
      Begin
        Begin Transaction P_YTFPB_Save
          Insert Into ytfpb(DWBH,ZGFP,JLFP,CQFP,LDGB,SHYF,GYFW,JDFW,QTYF) 
            Values (@DWBH,@ZGFP,@JLFP,@CQFP,@LDGB,@SHYF,@GYFW,@JDFW,@QTYF)
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_YTFPB_Save
          Else
            Commit Transaction P_YTFPB_Save
      End
    Return
--结束
go



CREATE PROCEDURE P_ZFXX_Del
   @ZFBH Char(10)='',      --住房编号
   @nRet Smallint =0 Out  --返回值
--With Encryption
As
    Set NoCount On
    Set @nRet=0
    Begin Transaction P_ZFXX_Del
        Delete From zfxxb  Where ZFBH =@ZFBH
    Set @nRet=@@Error
    If @nRet =0 
      Commit Transaction P_ZFXX_Del
    Else 
      Rollback Transaction P_ZFXX_Del
    Return
--结束
go



CREATE PROCEDURE P_ZFXX_Save
    @ZFBH Char(10)='',   --住房编号
    @LYBH Char(5)='',   --楼宇编号
    @DYH   Char(1)='',   --单元号
    @HXBH Char(2)='',   --户型编号
    @LC   Char(2)='',      --楼层
    @CXBH Char(2)='',   --朝向编号
    @DJBH Char(2)='',   --等级编号
    @YTBH Char(2)='',   --用途编号
    @SFFP Char(1)='',    --是否分配
    @KFFP Char(1)='',    --可否分配
    @SFZH Char(18)='',  --身份证号
    @DWBH Char(3)='',  --单位编号
    @AREA decimal(8,2)=0, --住房面积
    @COST decimal(16,2)=0, --房价
    @nRet Smallint =0 Out  --返回值

--With Encryption
As
   Set NoCount On
   If Exists(Select * From zfxxb  Where ZFBH= @ZFBH )
     Begin
        Set @nRet=1
        Begin Transaction P_ZFXX_Save
          if @dwbh='' 
            Update zfxxb 
               Set LYBH=@LYBH,DYH=@DYH,HXBH=@HXBH,LC=@LC,CXBH=@CXBH,DJBH=@DJBH,YTBH=@YTBH,SFFP=@SFFP,KFFP=@KFFP,SFZH=@SFZH,DWBH=null,AREA=@AREA,COST=@COST
                Where ZFBH=@ZFBH
         else
             Update zfxxb 
               Set LYBH=@LYBH,DYH=@DYH,HXBH=@HXBH,LC=@LC,CXBH=@CXBH,DJBH=@DJBH,YTBH=@YTBH,SFFP=@SFFP,KFFP=@KFFP,SFZH=@SFZH,DWBH=@DWBH,AREA=@AREA,COST=@COST
                Where ZFBH=@ZFBH
  
        Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_ZFXX_Save
          Else
            Commit Transaction P_ZFXX_Save
      End
    Else
      Begin
        Begin Transaction P_ZFXX_Save
         IF @DWBH=''
          Insert Into zfxxb(ZFBH,LYBH,DYH,HXBH,LC,CXBH,DJBH,YTBH,SFFP,KFFP,SFZH,AREA,COST)  
              Values (@ZFBH,@LYBH,@DYH,@HXBH,@LC,@CXBH,@DJBH,@YTBH,@SFFP,@KFFP,@SFZH,@AREA,@COST)
        ELSE
          Insert Into zfxxb(ZFBH,LYBH,DYH,HXBH,LC,CXBH,DJBH,YTBH,SFFP,KFFP,SFZH,DWBH,AREA,COST)  
              Values (@ZFBH,@LYBH,@DYH,@HXBH,@LC,@CXBH,@DJBH,@YTBH,@SFFP,@KFFP,@SFZH,@DWBH,@AREA,@COST)

         Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_ZFXX_Save
          Else
            Commit Transaction P_ZFXX_Save
      End
    Return
--结束
go



CREATE PROCEDURE P_ZFXX_VIEW
  @nRet smallint       out    

AS
   Set NoCount On
   set @nret= 0
    begin Tran   
    select ZFBH,LYMC=b.mc,HX=c.mc,LC,CX=d.mc,DJ=e.mc,YT=f.mc,
          SFFP=(case when sffp = '1' then '未分' else '已分' end),
          KFFP=(case when kffp = '1' then '可分' else '不可分' end),
          SFZH  
     from zfxxb a, lyxxb b, fwhxb c, fwcxb d, fwdjb e, fwytb f
     where a.lybh= b.lybh and 
           a.hxbh= c.bh and 
           a.cxbh= d.bh and
           a.djbh= e.bh and
           a.ytbh= f.bh 
   set @nRet =@@error 
   If @nRet <>0 
      Rollback Tran
   Else
      Commit Tran

  Return
--结束
go



CREATE PROCEDURE P_ZGFPB_Del
   @DWBH Char(3)='',      --住房编号
   @YTBH Char(2)='',      --用途编号
   @nRet Smallint =0 Out  --返回值
--With Encryption
As
    Set NoCount On
    Set @nRet=0
    Begin Transaction P_ZGFPB_Del
        Delete From zgfpb  Where DWBH =@DWBH and YTBH=@YTBH
    Set @nRet=@@Error
    If @nRet =0 
      Commit Transaction P_ZGFPB_Del
    Else 
      Rollback Transaction P_ZGFPB_Del
    Return
--结束
go



CREATE PROCEDURE P_ZGFPB_Save
    @DWBH Char(3)='',    --单位编号
    @YTBH Char(2)='',    --房屋用途编号
    @YJSL smallint=0,    --一居数量
    @EJSL smallint=0,    --二居数量
    @SJSL smallint=0,    --三居数量
    @FSEJ smallint=0,    --复式二居
    @FSSJ smallint=0,    --复式三居
    @QT   smallint=0,    --其他
    @HJ   smallint=0,    --合计
    @nRet Smallint =0 Out  --返回值

--With Encryption
As
   Set NoCount On
   If Exists(Select * From zgfpb  Where DWBH+YTBH = @DWBH+ @YTBH )
     Begin
        Set @nRet=1
        Begin Transaction P_ZGFPB_Save
          Update zgfpb Set YJSL=@YJSL,EJSL=@EJSL,SJSL=@SJSL,FSEJ=@FSEJ,FSSJ=@FSSJ,QT=@QT,HJ=@HJ
          Where DWBH+YTBH= @DWBH+ @YTBH 
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_ZGFPB_Save
          Else
            Commit Transaction P_ZGFPB_Save
      End
    Else
      Begin
        Begin Transaction P_ZGFPB_Save
          Insert Into zgfpb(DWBH,YTBH,YJSL,EJSL,SJSL,FSEJ,FSSJ,QT,HJ) 
           Values (@DWBH,@YTBH,@YJSL,@EJSL,@SJSL,@FSEJ,@FSSJ,@QT,@HJ)
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_ZGFPB_Save
          Else
            Commit Transaction P_ZGFPB_Save
      End
    Return
--结束
go

CREATE PROCEDURE P_TXFP_Save1
(
    @ZFBH Char(10)='',  --住房编号
    @DWBH Char(3)='',  --单位编号
    @YTBH Char(2)='',  --用途编号
    @MSG  Varchar(50)='' out,
    @nRet Smallint =0 Out  --返回值 1-单位置空 2-分配未超规定 3-相等 4-超出 5-住房号不存在 7-房屋不可分 8-户型不确定 9-单位编号为空
)
AS
  Set NoCount On
  declare @CompA smallint, @CompB smallint, @hx varchar(4)
  If Exists(Select * From zfxxb  Where ZFBH=@ZFBH)
     Begin
        Set @nRet=1
        IF @DWBH=''
          Set @nRet=9
        ELSE
          begin
            select @hx= hxbh from zfxxb where zfbh=@ZFBH
            select @CompA=count(*) from zfxxb where hxbh= @hx and dwbh=@dwbh and YTBH=@YTBH
            set @compB=-1
            if @hx= '01'
              begin
                select @CompB=yjsl from zgfpb where dwbh=@dwbh and YTBH=@YTBH
                if @compB is null 
                  set @compb =0
                set @Msg='一居室'
              end
            else
            if @hx= '02'
              begin
                select @CompB=ejsl from zgfpb where dwbh=@dwbh and YTBH=@YTBH
                if @compB is null 
                  set    @compb =0
                set @Msg='二居室'
              end
            else
            if @hx= '03'
              begin
                select @CompB=sjsl from zgfpb where dwbh=@dwbh and YTBH=@YTBH
                if @compB is null 
                  set    @compb =0
                set @Msg='三居室'
              end
            else
            if @hx= '04'
              begin
                select @CompB=fsej from zgfpb where dwbh=@dwbh and YTBH=@YTBH
                if @compB is null 
                  set    @compb =0
                set @Msg='复式二居'
              end
            else
            if @hx= '05'
              begin
                select @CompB=fssj from zgfpb where dwbh=@dwbh and YTBH=@YTBH
                if @compB is null 
                  set    @compb =0
                set @Msg='复式三居'
              end
            else
              set @nret= 8  
            
            if @compB <>-1 
            begin 
              if @CompA< @CompB 
              begin
                select @nRet= kffp from zfxxb where ZFBH=@ZFBH
                if @nRet= 1 
                begin
                  set @nRet= 2
                  Update zfxxb Set YTBH =@YTBH,sffp=1 Where ZFBH=@ZFBH
                  set @MSG=@MSG+'  可分<'+convert(varchar(4),@compB)+'>套，已分<'+convert(varchar(4),@compA+1)+'>套，剩余<'+convert(varchar(4),@compB-@compA-1)+'>套'
                  If @@Error <>0
                      Set @nRet=9
                end
                else
                  select @nRet=7
              end
              else
              if @CompA= @CompB
              begin
                set @nRet= 3
                set @MSG=@MSG+'  可分<'+convert(varchar(4),@compB)+'>套，已分<'+convert(varchar(4),@compA)+'>套'
              end
              else
              begin
                set @nret= 4
                set @MSG=@MSG+'  可分<'+convert(varchar(4),@compB)+'>套，已分<'+convert(varchar(4),@compA)+'>套,请重新设置分配数量'
              end
            end
        End
     End
    else
       set @nret=5

    Return

--结束

GO

CREATE procedure p_Zfxx_set
(
  @zfbh varchar(10)='',
  @msg varchar(50)='' out,
  @nRet smallint=0  out
)
 
as
  Set NoCount On
  If Exists(Select * From zfxxb  Where ZFBH=@ZFBH)
  Begin
    update zfxxb set kffp=0 where zfbh=@zfbh
    select @msg='  不可分住房数量为：'+convert(varchar(5), count(*)) from zfxxb where kffp=0
    Set @nRet=@@Error
  end
  else
  begin
    select @msg='  不可分住房数量为：'+convert(varchar(5), count(*)) from zfxxb where kffp=0
    set @nRet=5
  end

return
GO