存储过程 insert

代码

ALTER   proc   [ dbo ] . [ P_InsertCnmCashList ]
/* ************************************************

************************************************* */
(
@ret   int =- 1  output,
@nHotelId   int ,
@nId   int =- 1  output,
@nCnmListId   int ,
@nCashId   int ,
@nPayValue   decimal ( 18 , 2 ),
@tag   int ,
@nCshGroupId   int =- 1  output
)
as
begin
     if  dbo.F_GetOptionLock( @nHotelId ) = 1
     begin
         return
     end

     declare   @cContent   nvarchar ( 1000 )
     declare   @nLogTypeId   int

     if   @nCshGroupId < 1
     begin
         exec  P_GetGlobalId  @nCshGroupId  output
     end
    
     if   @tag = 1   -- bill
     begin
         insert   into  C_BillCnmCsh(nHotelId,nBillCnmListId,nBillCashId,nPayValue,nCshGroupId)
         values ( @nHotelId , @nCnmListId , @nCashId , @nPayValue , @nCshGroupId )
         set   @nId = @@identity

--         update C_BillCash set nCshGroupId=@nCshGroupId where nId=@nCashId
--         update C_BillCnmList set nCshGroupId=@nCshGroupId where nId=@nCnmListId

         -- ---------------------------------------------------
         -- -----Begin Log-------------------------------------
--         set @cContent='新增主单付款明细:nId'+convert(varchar(20),@nId)+',金额：'+convert(varchar(20),@nPayValue)
--         set @nLogTypeId=[dbo].[F_GetLogTypeId_Bill]()
--         exec P_DoLog @ret output,@nHotelId,null,@cContent,'C_BillCnmCsh',null,null,null,null,null,@nLogTypeId
         -- 2009-04-17
         -- -----End Log---------------------------------------
         -- ---------------------------------------------------
         set   @ret = 1
     end
     else   if   @tag = 2   -- guest
     begin
         insert   into  C_GuestCnmCsh(nHotelId,nGuestCnmListId,nGuestCashId,nPayValue,nCshGroupId)
         values ( @nHotelId , @nCnmListId , @nCashId , @nPayValue , @nCshGroupId )
         set   @nId = @@identity

--         update C_GuestCash set nCshGroupId=@nCshGroupId where nId=@nCashId
--         update C_GuestCnmList set nCshGroupId=@nCshGroupId where nId=@nCnmListId
         -- ---------------------------------------------------
         -- -----Begin Log-------------------------------------
--         set @cContent='新增客单付款明细:nId'+convert(varchar(20),@nId)+',金额：'+convert(varchar(20),@nPayValue)
--         set @nLogTypeId=[dbo].[F_GetLogTypeId_Guest]()
--         exec P_DoLog @ret output,@nHotelId,null,@cContent,'C_GuestCnmCsh',null,null,null,null,null,@nLogTypeId
         -- 2009-04-17
         -- -----End Log---------------------------------------
         -- ---------------------------------------------------
         set   @ret = 1
     end
end

 

 

代码

set  ANSI_NULLS  ON
set  QUOTED_IDENTIFIER  ON
go


ALTER   proc   [ dbo ] . [ P_InsertGuestCnmList ]
(
     @ret   int =- 1  output,
     @nHotelId   int ,
     @nGuestId   int ,
     @nProductId   int ,
     @nProductTypeId   int ,
     @nCount   decimal ( 18 , 2 ),
     @nPrice   decimal ( 18 , 2 ),
     @nUserId   int ,
     @cComputer   nvarchar ( 50 ),
     @cRemark   nvarchar ( 4000 ),
     @nJobTimeListId   int ,
     @cOutBillCode   nvarchar ( 50 ),
     @tag   int
)
/* ************************************************

************************************************* */
as
begin
-- --    if dbo.F_GetOptionLock(@nHotelId)=1
-- --    begin
-- --        return
-- --    end

     declare   @cContent   nvarchar ( 1000 )
     declare   @nLogTypeId   int , @nPkgListId   int
     declare   @GuestCnmListnId   int , @nBillId   int , @ntargId   int , @nCnmListId   int
     declare   @cCnmSourceName   nvarchar ( 10 ), @nConsumeKindId   int , @cProductName   nvarchar ( 50 ),
     @nUnitId   int , @cUnitName   nvarchar ( 10 ),
-- --------------插入佣金值------------------------------------------------------------------------------------------------------------
     @nCompanyId   int , @nCompanyPrice   decimal ( 18 , 2 ), @bIsCompanyPrice   bit

     select   @nCompanyId = nPactId  from  C_Guest  where  nId = @nGuestId
     select   @nCompanyPrice = dbo.F_GetCompanyPrice( @nHotelId , @nCompanyId , @nProductId )
     if   @nCompanyPrice =- 1
     begin
         set   @bIsCompanyPrice = 0
     end
     else
     begin
         set   @bIsCompanyPrice = 1
     end
-- ----------------------------------------------------------------
     select   @cCnmSourceName = cName  from  A_CnmSource  where  nId = 1  
     if   not   exists ( select   1   from  C_Guest  where  nBillStateId  in ( 1 , 3 )  and  nHotelId = @nHotelId   and  nId = @nGuestId )
     begin
         set   @ret =- 1
         return
     end
     if   @tag = 1
     begin
         select   @nConsumeKindId = nConsumeKindId, @cProductName = cName,
             @nUnitId = nUnitId, @cUnitName = cUnitName  from  B_Consume
             where  nId = @nProductId   and  nHotelId = @nHotelId
            
         insert   into  C_GuestCnmList(nHotelId,cCnmSourceName,nGuestId,nConsumeListStateId,nCnmListTypeId,nProductTypeId,
            nConsumeKindId,nProductId,cProductName,nCount,nUnitId,cUnitName,nPrice,nValue,nJobTimeListId,
            dOptionTime,nUserId,cComputer,cRemark,cOutBillCode,bIsPackage,bIsOwn,IsCompanyPrice,nCompanyPrice)
             values ( @nHotelId , @cCnmSourceName , @nGuestId , 1 , 1 , @nProductTypeId ,
             @nConsumeKindId , @nProductId , @cProductName , @nCount , @nUnitId , @cUnitName , @nPrice , @nCount * @nPrice , @nJobTimeListId ,
             getdate (), @nUserId , @cComputer , @cRemark , @cOutBillCode , 0 , 1 , @bIsCompanyPrice , @nCompanyPrice )

         set   @GuestCnmListnId = @@identity

         select   @nBillId = nBillId  from  C_Guest  where  nId = @nGuestId
             and  nHotelId = @nHotelId

         -- 处理自动转账(含转团帐）
         if   exists ( select   1   from  C_GuestAccount  where  nGuestId = @nGuestId
         and  nHotelId = @nHotelId   and  nConsumeKindId = @nConsumeKindId )
         begin      -- 转团帐
             exec  P_DoTransfer  @ret  output, 2 , 1 , @nGuestId , @nBillId , @GuestCnmListnId ,
             @nHotelId , @nUserId , @nJobTimeListId , @cComputer , @cRemark
         end
         else   if   exists ( select   1   from  dbo.C_G2BAccountTurn 
             where  nHotelId = @nHotelId
             and  nSourceGuestId = @nGuestId   and  nConsumeKindId = @nConsumeKindId )
         begin
             select   @ntargId = nTargetBillId  from  dbo.C_G2BAccountTurn 
             where  nHotelId = @nHotelId
             and  nSourceGuestId = @nGuestId   and  nConsumeKindId = @nConsumeKindId

             exec  P_DoTransfer  @ret  output, 2 , 1 , @nGuestId , @ntargId , @GuestCnmListnId ,
             @nHotelId , @nUserId , @nJobTimeListId , @cComputer , @cRemark
            
         end
         else   if   exists ( select   1   from  dbo.C_G2GAccountTurn
             where  nHotelId = @nHotelId
             and  nSourceGuestId = @nGuestId   and  nConsumeKindId = @nConsumeKindId )
         begin
             select   @ntargId = nTargetGuestId  from  dbo.C_G2GAccountTurn
             where  nHotelId = @nHotelId
             and  nSourceGuestId = @nGuestId   and  nConsumeKindId = @nConsumeKindId

             exec  P_DoTransfer  @ret  output, 2 , 2 , @nGuestId , @ntargId , @GuestCnmListnId ,
             @nHotelId , @nUserId , @nJobTimeListId , @cComputer , @cRemark
         end

         -- ---------------------------------------------------
         -- -----Begin Log-------------------------------------
         set   @cContent = ' [收银入账] 消费  ' + @cProductName + '   ' + convert ( nvarchar ( 20 ), @nCount ) + @cUnitName + ' ,共计 ' + convert ( nvarchar ( 50 ), @nCount * @nPrice ) + ' 元 '
--         set @cContent='新增客单消费明细:['+[dbo].[F_GetConsumeKindName](@nConsumeKindId)+'],金额'+convert(nvarchar(50),@nCount*@nPrice)
         set   @nLogTypeId = [ dbo ] . [ F_GetLogTypeId_Guest ] ()
         exec  P_DoLog  @ret  output, @nHotelId , @cComputer , @cContent , ' C_GuestCnmList ' , @nUserId , null , @nGuestId , null , null , @nLogTypeId
         -- 2009-04-17
         -- -----End Log---------------------------------------
         -- ---------------------------------------------------
         set   @ret = 1
     end
     else   if   @tag = 2
     begin
         begin   tran
             insert   into  C_GuestPkgList(cCnmSourceName,nGuestId,nConsumeListStateId,nCnmListTypeId,
            nProductTypeId,nPackageId,cPackageName,nCount,nUnitId,cUnitName,nJobTimeListId,
            dOptionTime,nUserId,cComputer,cRemark,nHotelId)
             select   @cCnmSourceName , @nGuestId , 1 , 1 ,
            nProductTypeId, @nProductId ,cName, @nCount ,nUnitId,cUnitName, @nJobTimeListId ,
             getdate (), @nUserId , @cComputer , @cRemark , @nHotelId
             from  B_Package  where  nId = @nProductId   and  nHotelId = @nHotelId
             set   @nPkgListId = @@identity

             insert   into  C_GuestCnmList(nHotelId,cCnmSourceName,nGuestId,nConsumeListStateId,nCnmListTypeId,nProductTypeId,
            nConsumeKindId,nProductId,cProductName,nCount,nUnitId,cUnitName,nPrice,nValue,nJobTimeListId,
            dOptionTime,nUserId,cComputer,cRemark,cOutBillCode,bIsPackage,nPkgListId,bIsOwn)
             select   @nHotelId , @cCnmSourceName , @nGuestId , 1 , 1 ,nProductTypeId,
            nConsumeKindId,nId,cName,nCount * @nCount ,nUnitId,cUnitName,nPrice,nCount * @nCount * nprice, @nJobTimeListId ,
             getdate (), @nUserId , @cComputer , @cRemark , @cOutBillCode , 1 , @nPkgListId , 1
             from  dbo.V_PkgItm  where  nPackageId = @nProductId   and  bIsUse = 1

             -- 处理自动转账(含转团帐）
             if   exists ( select   1   from  C_GuestAccount  where  nGuestId = @nGuestId
             and  nHotelId = @nHotelId )
             begin      -- 转团帐
                 declare  mycursor2  cursor   for
                 select  A.nId  from  C_GuestCnmList A
                 right   join  C_GuestAccount B  On  A.nGuestId = B.nGuestId 
                         and  A.nConsumeKindId = B.nConsumeKindId
                 where  A.nConsumeListStateId = 1   and  A.nCnmListTypeId = 1  
                     and  bIsPackage = 1   and  nPkgListId = @nPkgListId
                     and  A.nGuestId = @nGuestId   and  A.nHotelId = @nHotelId
                     and  B.nGuestId = @nGuestId   and  B.nHotelId = @nHotelId

                 open  mycursor2
                 fetch  mycursor2  into   @nCnmListId
                 while   @@fetch_status = 0  
                 begin
                     exec  P_DoTransfer  @ret  output, 2 , 1 , @nGuestId , @nBillId , @nCnmListId ,
                     @nHotelId , @nUserId , @nJobTimeListId , @cComputer , @cRemark
                     fetch  mycursor2  into   @nCnmListId
                 end
                 close  mycursor2 
                 deallocate  mycursor2
             end
             else   if   exists ( select   1   from  dbo.C_G2BAccountTurn 
             where  nHotelId = @nHotelId
             and  nSourceGuestId = @nGuestId )
             begin
                 declare  mycursor  cursor   for
                 select  A.nId,nTargetBillId  from  C_GuestCnmList A
                 right   join  C_G2BAccountTurn B  On  A.nGuestId = B.nSourceGuestId  and  A.nConsumeKindId = B.nConsumeKindId
                 where  A.nConsumeListStateId = 1   and  A.nCnmListTypeId = 1
                 and  bIsPackage = 1   and  nPkgListId = @nPkgListId
                 and  A.nGuestId = @nGuestId   and  A.nHotelId = @nHotelId

                 open  mycursor
                 fetch  mycursor  into   @nCnmListId , @ntargId
                 while   @@fetch_status = 0  
                 begin
                     exec  P_DoTransfer  @ret  output, 2 , 1 , @nGuestId , @ntargId , @nCnmListId ,
                     @nHotelId , @nUserId , @nJobTimeListId , @cComputer , @cRemark
                     fetch  mycursor  into   @nCnmListId , @ntargId
                 end
                 close  mycursor 
                 deallocate  mycursor
             end
             else   if   exists ( select   1   from  dbo.C_G2GAccountTurn 
             where  nHotelId = @nHotelId
             and  nSourceGuestId = @nGuestId )
             begin
                 declare  mycursor1  cursor   for
                 select  A.nId,nTargetGuestId  from  C_GuestCnmList A
                 right   join  C_G2GAccountTurn B  On  A.nGuestId = B.nSourceGuestId  and  A.nConsumeKindId = B.nConsumeKindId
                 where  A.nConsumeListStateId = 1   and  A.nCnmListTypeId = 1  
                 and  bIsPackage = 1   and  nPkgListId = @nPkgListId
                 and  A.nGuestId = @nGuestId   and  A.nHotelId = @nHotelId

                 open  mycursor1
                 fetch  mycursor1  into   @nCnmListId , @ntargId
                 while   @@fetch_status = 0  
                 begin
                     exec  P_DoTransfer  @ret  output, 2 , 2 , @nGuestId , @ntargId , @nCnmListId ,
                     @nHotelId , @nUserId , @nJobTimeListId , @cComputer , @cRemark
                     fetch  mycursor1  into   @nCnmListId , @ntargId
                 end
                 close  mycursor1 
                 deallocate  mycursor1
             end
             -- ---------------------------------------------------
             -- -----Begin Log-------------------------------------
             set   @cContent = ' 日志待补充 '
--             set @cContent='新增住客套餐消费项:nPackageId'+convert(varchar(20),@nProductId)
             set   @nLogTypeId = [ dbo ] . [ F_GetLogTypeId_Guest ] ()
             exec  P_DoLog  @ret  output, @nHotelId , @cComputer , @cContent , ' C_GuestCnmList ' , @nUserId , null , @nGuestId , null , null , @nLogTypeId
             -- 2009-04-17
             -- -----End Log---------------------------------------
             -- ---------------------------------------------------
             set   @ret = 1
         commit   tran
     end
     else
     begin
         set   @ret =- 1
     end
end

 

 

 

转载于:https://www.cnblogs.com/callbin/archive/2009/12/29/1634936.html