processmattbill.sql

来自「出租车管理企业」· SQL 代码 · 共 117 行

CREATE PROCEDURE ProcessMattBill(
       @sMachineId varchar(5),
       @sId varchar(50),
       @OutId varchar(50),
       @sUseage varchar(50),
       @sLinkId varchar(50),
       @sLinkUseage varchar(50),
       @dDate datetime,
       @sHandler varchar(50),
       @sStorer varchar(50),
       @sOthers varchar(50),
       @sUser varchar(50),
       @sCustId varchar(5),
       @nMoney numeric(20, 4),
       @nMoney2 numeric(20, 4),
       @sPayMode varchar(50),
       @nAgioMoney numeric(20, 4),
       @nAddinMoney numeric(20, 4),
       @nTax numeric(18, 4),
       @sEventID varchar(50),
       @sCounter varchar(50),
       @sPlanID varchar(50),
       @sInvoiceID varchar(50),
       @sCreditID varchar(50),
       @nLeftMode numeric(8, 2),
       @nCountRate numeric(18, 4),
       @nOrderRate numeric(18, 4),
       @nRepairRate numeric(18, 4),
       @sComment varchar(100),
       @nStatus numeric(8, 2)
       /*
       @dCreate datetime
       @lPass bit,
       @sPasser varchar(50),
       @dPass datetime,
       */
       )
AS
BEGIN
     declare @sIdHead varchar(8)
     declare @processMode integer
     declare @lPass bit
     
     /*
      * @sId = ''新单据插入记录
      * @sId <> ''更新原有单据
      */
     if @sId = ''
     begin
          --根据sUseage提取Worktable中的sIDHead
          --用于自动生成BillID
          --如果失败，返回4，表示sUseage 错误
          select @sIdHead = dbo.GetIdHead(@sUseage)
          if @sIdHead = ''
          begin
               --sUseage 错误
               return 4
          end
          else
          begin
               if @sMachineId = ''
               begin
                    --机器码错误
                    return 6
               end
               set @sIdHead = @sIdHead + '_' + @sMachineId + '_'
               set @sId = IsNull(dbo.BuildNewMattBillID(@sIdHead, 10), '')
               if @sId = ''
               begin
                    --生成单据号错误
                    return 5
               end
          end
          insert MattBill (sId, OutId, sUseage, sLinkId, sLinkUseage, dDate, sHandler,
                 sStorer, sOthers, sUser, sCustId, nMoney, nMoney2, sPayMode, nAgioMoney,
                 nAddinMoney, nTax, sEventID, sCounter, sPlanID, sInvoiceID, sCreditID,
                 nLeftMode, nCountRate, nOrderRate, nRepairRate, sComment, nStatus)
          values  (@sId, @OutId, @sUseage, @sLinkId,
                 @sLinkUseage, @dDate, @sHandler, @sStorer, @sOthers, @sUser, @sCustId,
                 @nMoney, @nMoney2, @sPayMode, @nAgioMoney, @nAddinMoney, @nTax,
                 @sEventID, @sCounter, @sPlanID, @sInvoiceID, @sCreditID, @nLeftMode,
                 @nCountRate, @nOrderRate, @nRepairRate, @sComment, @nStatus)
     end
     else
     begin
          if exists(select * from MattBill where sId = @sId)
          begin
               select @lPass = lPass from MattBill where sId = @sId
               if @lPass = 1
               begin
                    --单据已经审核
                    return 2
               end
               update MattBill set OutId = @OutId, sUseage = @sUseage, sLinkId = @sLinkId,
                      sLinkUseage = @sLinkUseage, dDate = @dDate, sHandler = @sHandler,
                      sStorer = @sStorer, sOthers = @sOthers, sUser = @sUser,
                      sCustId = @sCustId, nMoney = @nMoney, nMoney2 = @nMoney2,
                      sPayMode = @sPayMode, nAgioMoney = @nAgioMoney, nAddinMoney = @nAddinMoney,
                      nTax = @nTax, sEventID = @sEventID, sCounter = @sCounter,
                      sPlanID = @sPlanId, sInvoiceID = @sInvoiceID, sCreditID = @sCreditID,
                      nLeftMode = @nLeftMode, nCountRate = @nCountRate, nOrderRate = @nOrderRate,
                      nRepairRate = @nRepairRate, sComment = @sComment, nStatus = @nStatus
               where sId = @sId
          end
          else
          begin
               --单据号错误
               return 3
          end
     end
     if @@error <> 0
        --单据保存错误
        return 1
     else
         return 0
END