checkmattbill.sql

出租车管理企业

CREATE PROCEDURE CheckMattBill(@sId varchar(50), @sPasser varchar(50), @dPass datetime)
AS
BEGIN
     declare @sStorer varchar(50), @sOthers varchar(50), @sCustId varchar(50)
     declare @sItemId varchar(50), @sProperty1 varchar(50), @sProperty2 varchar(50)
     declare @nCount numeric(20, 4), @nPrice numeric(20, 4), @nRate integer
     declare @lPass bit
     
     /*
     declare @sUseage varchar(50), @sBillTable varchar(50), @sListTable varchar(50)

     if @sUseage = ''
     begin
          -- sUseage错误
          return 1
     end
     if exists(select * from worktable where sId = @sUseage)
        select @sBillTable = sTable, @sListTable = sListTable1 where sId = @sUseage
     end
     if @sBillTable = '' or @sListTable = ''
     begin
          -- sUseage错误
          return 1
     end
     */
     if not exists(select * from mattbill where sId = @sId)
     begin
        --单据号错误或单据不存在
        return 2
     end
     else
     begin
         select @lPass = lPass, @sStorer = sStorer, @sOthers = sOthers, @nRate = nCountRate,
                @sCustId = sCustId from mattbill where sId = @sId
         if @lPass = 1
         begin
            -- 单据已审核
            return 3
         end
     end

     declare @transcnt int
     select @transcnt = @@trancount
     if @transcnt = 0
        begin tran p1
     else
         save tran p1

     update mattbill set lPass = 1, dPass = @dPass, sPasser = @sPasser

     DECLARE mattlist_cursor CURSOR
             FOR SELECT sItemId, sProperty1, sProperty2, nCount, nPrice
             FROM mattlist where sId = @sId
             
     open mattlist_cursor
     
     
     FETCH NEXT FROM mattlist_cursor
           INTO @sItemId, @sProperty1, @sProperty2, @nCount, @nPrice
     WHILE @@FETCH_STATUS = 0
     begin
          if exists(select * from fitstore where sStoreId = @sStorer and sItemID = @sItemId
                           and sProperty1 = @sProperty1 and sProperty2 = @sProperty2)
          begin
               update FitStore
                      set nCount = nCount + (@nCount * @nRate), nPrice = @nPrice, sOwner = @sCustId
                      where sStoreId = @sStorer and sItemID = @sItemId and
                      sProperty1 = @sProperty1 and sProperty2 = @sProperty2
          end
          else
          begin
               insert into FitStore (sStoreId, sItemID, sProperty1, sProperty2,
                      sOwner, nCount, nPrice) values (@sStorer, @sItemId,
                      @sProperty1, @sProperty2, @sCustId, (@nCount * @nRate), @nPrice)
          end

          if @@error > 0
          begin
             --单据审核错误
             rollback tran p1
             return 4
          end

          ---更新往来单位库存
          if exists(select * from storer where sId = @sOthers)
          begin
               if exists(select * from fitstore where sStoreId = @sOthers and sItemID = @sItemId
                                and sProperty1 = @sProperty1 and sProperty2 = @sProperty2)
               begin
                    update FitStore
                           set nCount = nCount - (@nCount * @nRate), nPrice = @nPrice, sOwner = @sCustId
                           where sStoreId = @sOthers and sItemID = @sItemId and
                           sProperty1 = @sProperty1 and sProperty2 = @sProperty2
               end
               else
               begin
                    insert into FitStore (sStoreId, sItemID, sProperty1, sProperty2,
                           sOwner, nCount, nPrice) values (@sOthers, @sItemId,
                           @sProperty1, @sProperty2, @sCustId, (@nCount * @nRate), @nPrice)
               end
          end
          if @@error > 0
          begin
             --单据审核错误
             rollback tran p1
             return 4
          end

          FETCH NEXT FROM mattlist_cursor
          INTO @sItemId, @sProperty1, @sProperty2, @nCount, @nPrice
     end
     
     close mattlist_cursor
     deallocate mattlist_cursor
     
     if @@error > 0
     begin
          --单据审核错误
          rollback tran p1
          return 4
     end
     else
     begin
          --单据审核成功
          commit tran p1
          return 0
     end
     ----存储过程结束
END