USP_DATAFORMTEMPLATE_DISBURSEMENTPROCESS_PRINT_EDIT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_DISBURSEMENTPROCESS_PRINT_EDIT (
    @ID uniqueidentifier
    ,@CHANGEAGENTID uniqueidentifier = null
  ,@ASSIGNDISBURSEMENTSCODE tinyint
  ,@CHECKS_NEXTUNUSED int  
  ,@RANGESGRID xml 
  ,@CHECKS_PRINTERID uniqueidentifier
  ,@CHECKS_FORMAT uniqueidentifier
)
as
    set nocount on;

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output            
    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try
  -- Get parameters from the disbursement process

      declare @BANKACCOUNTID uniqueidentifier;
      declare @POSTSTATUSCODE int;
      declare @POSTDATE date;

      select
          @BANKACCOUNTID = DP.BANKACCOUNTID
          ,@POSTSTATUSCODE = CASE DP.POSTSTATUSCODE WHEN 1 THEN 1 WHEN 3 THEN 2 END -- yippeeee!

          ,@POSTDATE = DP.POSTDATE
      from
          dbo.DISBURSEMENTPROCESS as DP
      where
          DP.ID = @ID;

    -- Do not allow zero for any check numbers as zero is not a valid check number.

    if @CHECKS_NEXTUNUSED = 0 
      raiserror('ERR_DISBURSEMENTPROCESSFORMNUMBERRANGE_INVALIDCHECKNUMBERRANGE', 16, 1)

    -- Do not allow the check number to exceed 9 characters.

    if @CHECKS_NEXTUNUSED > 999999999
      raiserror('ERR_DISBURSEMENTPROCESSFORMNUMBERRANGE_9DIGITSONLYRANGE', 16, 1)

    -- Validate print ranges

    -- @@@@@

    declare @TOTALDISBURSEMENTS int
    select    
        @TOTALDISBURSEMENTS=COUNT(*)
      from
        dbo.DISBURSEMENTPROCESSDISBURSEMENT as DPD
        inner join dbo.FINANCIALTRANSACTION as FTD on DPD.ID = FTD.ID
    where
      DPD.DISBURSEMENTPROCESSID = @ID
            and FTD.ID in (select DPD.ID 
                from dbo.DISBURSEMENTPROCESSDISBURSEMENT as DPD
                inner join dbo.FINANCIALTRANSACTIONAPPLICATION FTA on FTA.FINANCIALTRANSACTIONID = DPD.ID
                where FTA.AMOUNT != 0 and DPD.DISBURSEMENTPROCESSID = @ID)
      group by
        DPD.DISBURSEMENTPROCESSID


        if exists(
            select *
            from dbo.BANKACCOUNTTRANSACTION BAT
            where BAT.TRANSACTIONNUMBER >= @CHECKS_NEXTUNUSED and BAT.TRANSACTIONNUMBER <= (@TOTALDISBURSEMENTS + @CHECKS_NEXTUNUSED - 1)
                and  BAT.BANKACCOUNTID = @BANKACCOUNTID and BAT.DELETED = 0)
            raiserror('ERR_DISBURSEMENTPROCESSFORMNUMBERRANGE_CHECKNUMBEREXISTSRANGE', 16,1)

    declare @ValidateTempTbl table
    (
      [ROWID] int identity(1,1)
      ,[ID] uniqueidentifier
      ,[RANGESTART] int
      ,[RANGEEND] int
      ,[RANGECOUNT] int
      ,[BANKACCOUNTID] uniqueidentifier
    )

    if @ASSIGNDISBURSEMENTSCODE=0 --USING RANGE

      begin
        insert into @ValidateTempTbl values
        (
          newid()
          ,@CHECKS_NEXTUNUSED
          ,@CHECKS_NEXTUNUSED + @TOTALDISBURSEMENTS - 1
          ,@TOTALDISBURSEMENTS
          ,@BANKACCOUNTID
        )
      end
    else -- USING GRID

      begin

        if @RANGESGRID is null
          raiserror('ERR_DISBURSEMENTPROCESSFORMNUMBERRANGE_NOTENOUGHDISBURSEMENTS', 16, 1)

        insert into @ValidateTempTbl 
          select
            isnull([ID], newid()) ID
            ,[RANGESTART]
            ,[RANGEEND]
            ,CASE WHEN [RANGEEND]<>0
             THEN
               [RANGEEND]-[RANGESTART]+1 
             ELSE
               0
             END as [RANGECOUNT]
            ,@BANKACCOUNTID
        from dbo.UFN_DISBURSEMENTPROCESSFORMNUMBERRANGE_FROMITEMLISTXML(@RANGESGRID)

        update @ValidateTempTbl
        set RANGECOUNT = @TOTALDISBURSEMENTS - (select SUM(RANGECOUNT) from @ValidateTempTbl)
        where RANGECOUNT = 0
      end

    -- Do not allow zero for any check numbers as zero is not a valid check number.

    if dbo.UFN_DISBURSEMENTPROCESS_RANGESGRID_DONOTALLOWZERO(@RANGESGRID)=1
      raiserror('ERR_DISBURSEMENTPROCESSFORMNUMBERRANGE_INVALIDCHECKNUMBERGRID', 16, 1)


    -- Do not allow the check number to exceed 9 characters.

    if dbo.UFN_DISBURSEMENTPROCESS_RANGESGRID_DONOTALLOWCHECKNUMBERGREATERTHANNINE(@RANGESGRID)=1
      raiserror('ERR_DISBURSEMENTPROCESSFORMNUMBERRANGE_9DIGITSONLYGRID',16,1)    

    -- Validate that the start numbers are less than the end numbers.

    if dbo.UFN_DISBURSEMENTPROCESS_RANGESGRID_DONOTALLOWSTARTGREATERTHANEND(@ID, @RANGESGRID)=1
      raiserror('ERR_DISBURSEMENTPROCESSFORMNUMBERRANGE_RANGESTARTGREATERTHANRANGEEND', 16, 1)      

    -- Validate that the ranges do not overlap.

    if dbo.UFN_DISBURSEMENTPROCESS_RANGESGRID_DONOTALLOWOVERLAPPINGRANGES(@ID, @RANGESGRID)=1
      raiserror('ERR_DISBURSEMENTPROCESSFORMNUMBERRANGE_RANGESCANNOTOVERLAP', 16, 1)

    -- Validate that none of the checks in the range have been used in the check register.

    if dbo.UFN_DISBURSEMENTPROCESS_RANGESGRID_DONOTALLOWDUPLICATECHECKNUMBERS(@ID, @BANKACCOUNTID, @RANGESGRID, 0, 0)=1
      raiserror('ERR_DISBURSEMENTPROCESSFORMNUMBERRANGE_CHECKNUMBEREXISTSGRID', 16,1)

    -- Validate that the total number of disbursements matches the number ranges.

    -- VALIDATE4 START

    declare @TOTALRANGECOUNT int

    select @TOTALRANGECOUNT=SUM(RANGECOUNT) from @ValidateTempTbl 

    if @TOTALRANGECOUNT < @TOTALDISBURSEMENTS 
      raiserror('ERR_DISBURSEMENTPROCESSFORMNUMBERRANGE_NOTENOUGHDISBURSEMENTS', 16, 1)
    -- VALIDATE4 END

    -- @@@@@


    -- Change the user entered values if they used a range that included more changes than exist in this check run

    -- %%%%%

    declare @update_rowid int
    declare @update_rangeStart int
    declare @update_rangeEnd int
    declare @update_rangeCount int
    declare @totalCumulativeRangeCount int = 0

    declare update_cursor scroll cursor for
    select ROWID, RANGESTART, RANGEEND, RANGECOUNT from @ValidateTempTbl order by rowid

    open update_cursor

    fetch next from update_cursor
    into @update_rowid, @update_rangeStart, @update_rangeEnd, @update_rangeCount

    while @@FETCH_STATUS=0
    begin
      if @TOTALDISBURSEMENTS = 0
        delete @ValidateTempTbl where ROWID = @update_rowid
      else
        begin
          if (@TOTALDISBURSEMENTS - @totalCumulativeRangeCount - @update_rangeCount) >= 0 
              set @totalCumulativeRangeCount  = @totalCumulativeRangeCount + @update_rangeCount
          else
            begin
              if @totalCumulativeRangeCount >= 0
                set @totalCumulativeRangeCount = @TOTALDISBURSEMENTS - @totalCumulativeRangeCount - 1
                else
                      set @totalCumulativeRangeCount = @TOTALDISBURSEMENTS - 1                
              update @ValidateTempTbl set RANGEEND = @update_rangeStart + @totalCumulativeRangeCount where ROWID = @update_rowid
              update @ValidateTempTbl set RANGECOUNT = RANGEEND - RANGESTART + 1 where ROWID = @update_rowid
              set @TOTALDISBURSEMENTS = 0
            end
        end
      fetch next from update_cursor into @update_rowid, @update_rangeStart, @update_rangeEnd, @update_rangeCount
    end

    close update_cursor
        deallocate update_cursor
    -- %%%%%



    -- Now that the values are valid insert them into DISBURSEMENTPROCESSPRINTFINALIZERANGE .

      insert into dbo.DISBURSEMENTPROCESSPRINTFINALIZERANGE 
      (
        ID
        ,DISBURSEMENTPROCESSID
        ,RANGESTART
        ,RANGEEND
        ,RANGECOUNT
        ,ADDEDBYID
        ,CHANGEDBYID
        ,DATEADDED
        ,DATECHANGED
      )
      select 
        isnull([ID], newid()) ID
        ,@ID
        ,[RANGESTART]
        ,[RANGEEND]
        ,[RANGECOUNT]
        ,@CHANGEAGENTID ADDEDBYID
        ,@CHANGEAGENTID CHANGEDYID
        ,@CURRENTDATE DATEADDED
        ,@CURRENTDATE DATECHANGED  
      from @ValidateTempTbl

      -- Update the last range to have the RANGEEND blank.

      declare @TEMPID uniqueidentifier
      select 
        top(1) @TEMPID =  ID 
      from dbo.DISBURSEMENTPROCESSPRINTFINALIZERANGE
      where DISBURSEMENTPROCESSID = @ID
      order by dateadded desc
      update dbo.DISBURSEMENTPROCESSPRINTFINALIZERANGE
      set RANGEEND = 0
                ,CHANGEDBYID = @CHANGEAGENTID
                ,DATECHANGED = @CURRENTDATE
      where ID = @TEMPID


        --Cache current context information 

        declare @CONTEXTCACHE varbinary(128); 
        set @CONTEXTCACHE = CONTEXT_INFO(); 
        set CONTEXT_INFO @CHANGEAGENTID; 

      -- Remove all zero amount FTAs 

    -- This needs to be moved to the start of the approvals when approval processes exist.

      delete 
          dbo.FINANCIALTRANSACTIONAPPLICATION 
      from dbo.FINANCIALTRANSACTIONAPPLICATION FTA
          inner join dbo.DISBURSEMENTPROCESSDISBURSEMENT DPD on DPD.ID = FTA.FINANCIALTRANSACTIONID
          inner join dbo.DISBURSEMENTPROCESS DP on DPD.DISBURSEMENTPROCESSID = DP.ID
      where AMOUNT = 0
          and DP.ID = @ID;

    -- Remove all zero amount DPDs and FTs.

    -- This needs to be moved to the start of the approvals when approval processes exist.

    -- #####

      declare @AssignTempTbl as TABLE (
          ID uniqueidentifier
      );

      insert into @AssignTempTbl 
      select
          DPD.ID from dbo.DISBURSEMENTPROCESSDISBURSEMENT DPD
          inner join dbo.FINANCIALTRANSACTION FT on FT.ID = DPD.ID
          left outer join dbo.FINANCIALTRANSACTIONAPPLICATION FTA on FTA.FINANCIALTRANSACTIONID = FT.ID
      where FT.TRANSACTIONAMOUNT = 0 and DPD.DISBURSEMENTPROCESSID = @ID and FTA.ID is null;

    -- Remove all of the 0 amount DPDs.

      delete dbo.DISBURSEMENTPROCESSDISBURSEMENT
      from dbo.DISBURSEMENTPROCESSDISBURSEMENT DPD
          inner join @AssignTempTbl T on DPD.ID = T.ID;

    -- Remove all of the 0 amount FTs.

      delete dbo.FINANCIALTRANSACTION
      from dbo.FINANCIALTRANSACTION FT
          inner join @AssignTempTbl T on FT.ID = T.ID;
    -- #####


    if not @CONTEXTCACHE is null 
          set CONTEXT_INFO @CONTEXTCACHE;

    -- Update the Invoices and CreditMemos that were excluded (since you can no longer work

    -- with the excluded grid after you have moved beyond this step).

    -- $$$$$

    update dbo.INVOICE
        set DISBURSEMENTPROCESSID = null
            ,CHANGEDBYID = @CHANGEAGENTID
            ,DATECHANGED = @CURRENTDATE
    from dbo.INVOICE as I
        where I.ID not in (
            select FTS.FINANCIALTRANSACTIONID
            from dbo.FINANCIALTRANSACTIONSCHEDULE as FTS 
            inner join dbo.FINANCIALTRANSACTIONAPPLICATION as FTA on FTS.ID = FTA.FINANCIALTRANSACTIONSCHEDULEID
            inner join dbo.DISBURSEMENTPROCESSDISBURSEMENT DPD on DPD.ID = FTA.FINANCIALTRANSACTIONID
            where FTS.DELETED = 0 and DPD.DISBURSEMENTPROCESSID = @ID)
        and I.DISBURSEMENTPROCESSID = @ID

    update dbo.CREDITMEMO
        set DISBURSEMENTPROCESSID = null
            ,CHANGEDBYID = @CHANGEAGENTID
            ,DATECHANGED = @CURRENTDATE
    from dbo.CREDITMEMO as CM
        where CM.ID not in(
            select FTS.FINANCIALTRANSACTIONID
            from dbo.FINANCIALTRANSACTIONSCHEDULE as FTS 
            inner join dbo.FINANCIALTRANSACTIONAPPLICATION as FTA on FTS.ID = FTA.FINANCIALTRANSACTIONSCHEDULEID
            inner join dbo.DISBURSEMENTPROCESSDISBURSEMENT DPD on DPD.ID = FTA.FINANCIALTRANSACTIONID
            where FTS.DELETED = 0 and DPD.DISBURSEMENTPROCESSID = @ID)
        and CM.DISBURSEMENTPROCESSID = @ID
    -- $$$$$


    -- Assign check numbers based on user input.

    declare @IDS table 
    (
      FTID uniqueidentifier
      ,NUMBER integer
    )

    if @ASSIGNDISBURSEMENTSCODE=0 --USING RANGE

      begin
          -- Create ids for the BANKACCOUNTTRANSACTION insert so we can create fk

          insert into @IDS
          (
              FTID
              ,NUMBER
          )
          select
              ID
              ,NUMBER = (row_number() over (order by SORTCOLUMN)) + @CHECKS_NEXTUNUSED - 1
          from dbo.UFN_DISBURSEMENTPROCESS_FORMATPRINTORDER(@ID)
      end
    else --USING GRID

      begin
        declare @rangeStart int
        declare @rangeEnd int

        declare @FinancialTransactionTempTbl table (FTID uniqueidentifier, SORTCOLUMN int)
        insert into @FinancialTransactionTempTbl(FTID, SORTCOLUMN)
          select ID, SORTCOLUMN from dbo.UFN_DISBURSEMENTPROCESS_FORMATPRINTORDER(@ID)

        declare grid_cursor scroll cursor for
        select rangeStart, rangeEnd from dbo.DISBURSEMENTPROCESSPRINTFINALIZERANGE where DISBURSEMENTPROCESSID = @ID
        order by rangeStart

        open grid_cursor

        fetch next from grid_cursor
        into @rangeStart, @rangeEnd

        while @@FETCH_STATUS = 0
        begin
          declare @difference int
          set @difference = CASE WHEN @rangeEnd= 0 THEN 2147483647 ELSE @rangeEnd END - @rangeStart + 1

          insert into @IDS
          (
            FTID
            ,NUMBER
          )
          select top (@difference) 
            FTID 
            ,NUMBER = (row_number() over (order by SORTCOLUMN)) + @rangeStart - 1
          from @FinancialTransactionTempTbl order by SORTCOLUMN

          delete @FinancialTransactionTempTbl    where FTID in 
            (select top (@difference) FTID from @FinancialTransactionTempTbl order by SORTCOLUMN)

          fetch next from grid_cursor into @rangeStart, @rangeEnd
        end

        close grid_cursor
        deallocate grid_cursor

      end

      -- Transform the DISBURSEMENTPROCESSDISBURSEMENTS and their associated FINANCIALTRANSACTION to proper checks

      -- 

      -- 


      insert into dbo.FINANCIALTRANSACTIONLINEITEM
      (
        ID
        ,FINANCIALTRANSACTIONID
        ,TRANSACTIONAMOUNT
        ,BASEAMOUNT
        ,ORGAMOUNT
        ,VISIBLE
        ,[DESCRIPTION]
        ,SEQUENCE
        ,TYPECODE
        ,POSTDATE
        ,POSTSTATUSCODE
        -- Standard stuff

        , DATEADDED
        , DATECHANGED
        , CHANGEDBYID
        , ADDEDBYID
      )
      select
        NEWID()
        ,IDS.FTID
        ,FT.TRANSACTIONAMOUNT
        ,FT.TRANSACTIONAMOUNT
        ,FT.TRANSACTIONAMOUNT
        ,1 -- Visible

        ,'' -- Description

        ,1 -- Sequence

        ,0 -- Typecode, standard

        ,FT.POSTDATE        
        ,FT.POSTSTATUSCODE
        -- Standard stuff

        , @CURRENTDATE DATECHANGED
        , @CURRENTDATE DATEADDED
        , @CHANGEAGENTID CHANGEDYID
        , @CHANGEAGENTID ADDEDBYID
      from @IDS as IDS
      inner join dbo.FINANCIALTRANSACTION as FT on FT.ID = IDS.FTID
      ;

      -- Update the Financialtransaction to be a bankaccounttransaction

      update dbo.FINANCIALTRANSACTION
        set TYPECODE = 9 -- Computer check

      from @IDS as IDS
      inner join dbo.FINANCIALTRANSACTION as FT on FT.ID = IDS.FTID

      insert into dbo.BANKACCOUNTTRANSACTION_EXT 
      (  ID
        ,BANKACCOUNTID
        ,TRANSACTIONNUMBER
        ,TRANSACTIONTYPECODE
        ,TRANSACTIONFLAGCODE
        ,PROCESSING
        ,DISBURSEMENTPROCESSID
        ,COUNTRYID
        ,STATEID
        ,ADDRESSBLOCK
        ,CITY
        ,POSTCODE
        -- Standard stuff

        ,DATEADDED
        ,DATECHANGED
        ,CHANGEDBYID
        ,ADDEDBYID
      )  
      select
        IDS.FTID
        ,@BANKACCOUNTID
        ,IDS.NUMBER
        ,1 TRANSACTIONTYPECODE -- computer check

        ,2 TRANSACTIONFLAGCODE -- check

        ,1 PROCESSING              
        ,@ID
        ,A.COUNTRYID
        ,A.STATEID
        ,isnull(A.ADDRESSBLOCK, '')
        ,isnull(A.CITY,'')
        ,isnull(A.POSTCODE, '')
        -- Standard stuff

        ,@CURRENTDATE DATECHANGED
        ,@CURRENTDATE DATEADDED
        ,@CHANGEAGENTID CHANGEDYID
        ,@CHANGEAGENTID ADDEDBYID
      from 
        @IDS as IDS
        inner join dbo.FINANCIALTRANSACTION as FT on IDS.FTID = FT.ID
        inner join dbo.CONSTITUENT as C on FT.CONSTITUENTID = C.ID
        inner join dbo.DISBURSEMENTPROCESSDISBURSEMENT as DPD on DPD.ID = IDS.FTID
        left outer join dbo.ADDRESS as A on DPD.REMITADDRESSID = A.ID;    


    -- Change the status of the disbursement process    

      update 
          dbo.DISBURSEMENTPROCESS
      set
          STATUSCODE = 1 -- 1 = Created

      ,HASUNSUCCESSFUL = 0
            -- Standard update

          ,CHANGEDBYID = @CHANGEAGENTID
          ,DATECHANGED = @CURRENTDATE
    where
      ID = @ID;      

    -- update the disbursement process printer and format options

    update dbo.DISBURSEMENTPROCESSFORMAT
        set PRINTERID = @CHECKS_PRINTERID
            ,DISBURSEMENTFORMATID = @CHECKS_FORMAT
            ,DATECHANGED = @CURRENTDATE
            ,CHANGEDBYID = @CHANGEAGENTID
        where DISBURSEMENTPROCESSID  = @ID;

    -- insert "reprinted" records into the history tab

        insert into dbo.DISBURSEMENTHISTORY
      (
          ID
            ,FINANCIALTRANSACTIONID
          ,ACTIONCODE
          ,ORIGINALNUMBER
        -- Standard stuff

          , DATEADDED
          , DATECHANGED
          , CHANGEDBYID
          , ADDEDBYID
      )
        select
            NEWID()
            ,IDS.FTID
            ,3 --reprinted

            ,IDS.NUMBER
            -- Standard stuff

          ,@CURRENTDATE
          ,@CURRENTDATE
          ,@CHANGEAGENTID
          ,@CHANGEAGENTID
      from @IDS IDS
        where IDS.FTID in (select FINANCIALTRANSACTIONID from dbo.DISBURSEMENTHISTORY)
            or IDS.FTID in (select FUTUREFINANCIALTRANSACTIONID from dbo.DISBURSEMENTHISTORY where FUTUREFINANCIALTRANSACTIONID is not null)

    -- insert "created" records into the history tab

        insert into dbo.DISBURSEMENTHISTORY
      (
          ID
            ,FINANCIALTRANSACTIONID
          ,ACTIONCODE
          ,ORIGINALNUMBER
        -- Standard stuff

          , DATEADDED
          , DATECHANGED
          , CHANGEDBYID
          , ADDEDBYID
      )
        select
            NEWID()
            ,IDS.FTID
            ,0 --created

            ,IDS.NUMBER
            -- Standard stuff

          ,@CURRENTDATE
          ,@CURRENTDATE
          ,@CHANGEAGENTID
          ,@CHANGEAGENTID
      from @IDS IDS
        left outer join dbo.DISBURSEMENTHISTORY H on H.FINANCIALTRANSACTIONID = IDS.FTID or H.FUTUREFINANCIALTRANSACTIONID = IDS.FTID
        where H.ID is null

  end try
  begin catch

    if left(error_message(), 52) = 'Conversion failed when converting the nvarchar value'
      raiserror ('Starting number or ending number can not contain decimals.', 16, 1)
    else
          exec dbo.USP_RAISE_ERROR
    return 1
    end catch
return 0;