USP_DATALIST_RESERVATIONDUEDATES

CREATE procedure dbo.USP_DATALIST_RESERVATIONDUEDATES(@RESERVATIONID uniqueidentifier)
as
    set nocount on;

    declare @CLIENTCURRENTDATE date = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(getutcdate());

    declare @DUEDATES table (
        DUEDATETYPECODE tinyint,
        DUEDATE date,
        METREQUIREMENT bit,
        SENT bit,
        VIEWDATAFORMID uniqueidentifier,
        HASCONTRACT bit default 0
    );

    declare @BALANCE money;
    declare @SECURITYDEPOSITAMOUNTPAID money;

    declare @DEPOSITREQUIRED bit = 0;
    declare @DEPOSITDUEDATE date;
    declare @DEPOSITAMOUNT money;

    declare @SECURITYDEPOSITREQUIRED bit = 0;
    declare @SECURITYDEPOSITDUEDATE date;
    declare @SECURITYDEPOSITAMOUNT money;

    declare @CONTRACTREQUIRED bit = 0;
    declare @CONTRACTDUEDATE date;
    declare @CONTRACTSENT bit = 0;
    declare @CONTRACTRECEIVED bit = 0;
    declare @HASCONTRACT bit = 0;

    declare @FINALDUEDATE date;
    declare @ORDERBALANCESENT bit = 0;

    declare @FINALCOUNTREQUIRED bit = 0;
    declare @FINALCOUNTDUEDATE date;
    declare @FINALCOUNTRECEIVED bit = 0;

    select
        @DEPOSITREQUIRED = RESERVATION.DEPOSITREQUIRED,
        @DEPOSITDUEDATE = RESERVATION.DEPOSITDUEDATE,
        @DEPOSITAMOUNT = RESERVATION.DEPOSITAMOUNT,
        @SECURITYDEPOSITREQUIRED = RESERVATION.SECURITYDEPOSITREQUIRED,
        @SECURITYDEPOSITDUEDATE = RESERVATION.SECURITYDEPOSITDUEDATE,
        @SECURITYDEPOSITAMOUNT = RESERVATION.SECURITYDEPOSITAMOUNT,
        @CONTRACTREQUIRED = RESERVATION.CONTRACTREQUIRED,
        @CONTRACTSENT = RESERVATION.CONTRACTSENT,
        @CONTRACTDUEDATE = RESERVATION.CONTRACTDUEDATE,
        @CONTRACTRECEIVED = RESERVATION.CONTRACTRECEIVED,
        @FINALDUEDATE = RESERVATION.FINALDUEDATE,
        @ORDERBALANCESENT = RESERVATION.ORDERBALANCESENT,
        @FINALCOUNTREQUIRED = RESERVATION.FINALCOUNTREQUIRED,
        @FINALCOUNTDUEDATE = RESERVATION.FINALCOUNTDUEDATE,
        @FINALCOUNTRECEIVED = RESERVATION.FINALCOUNTRECEIVED,
        @BALANCE = TOTALS.BALANCE,
        @SECURITYDEPOSITAMOUNTPAID = TOTALS.SECURITYDEPOSITAMOUNTPAID
    from
        dbo.RESERVATION
    outer apply
        dbo.UFN_SALESORDER_TOTALS(RESERVATION.ID) as TOTALS
    where
        RESERVATION.ID = @RESERVATIONID;

    if @DEPOSITREQUIRED = 1 begin
        insert into @DUEDATES (DUEDATETYPECODE, DUEDATE, METREQUIREMENT, SENT, VIEWDATAFORMID)
        values (0, @DEPOSITDUEDATE, dbo.UFN_RESERVATION_ISDEPOSITPAID(@RESERVATIONID), 0, '066ec611-2f2a-4cf9-a410-cecd25f71362');
    end

    if @SECURITYDEPOSITREQUIRED = 1 begin
        insert into @DUEDATES (DUEDATETYPECODE, DUEDATE, METREQUIREMENT, SENT, VIEWDATAFORMID)
        values (1, @SECURITYDEPOSITDUEDATE, case when @SECURITYDEPOSITAMOUNTPAID < @SECURITYDEPOSITAMOUNT then 0 else 1 end, 0, '43a395c6-57e1-47c2-b153-1897db42bc3b');
    end

    if @CONTRACTREQUIRED = 1 begin
        if exists (select * from dbo.RESERVATIONATTACHMENT where RESERVATIONID = @RESERVATIONID and ISCONTRACT = 1) begin
            set @HASCONTRACT = 1;
        end

        insert into @DUEDATES (DUEDATETYPECODE, DUEDATE, METREQUIREMENT, SENT, HASCONTRACT, VIEWDATAFORMID)
        values (2, @CONTRACTDUEDATE, @CONTRACTRECEIVED, @CONTRACTSENT, @HASCONTRACT, '273FB09E-34CE-4FEF-A58D-17B8256EBAA1');
    end

    if @FINALCOUNTREQUIRED = 1 begin
        insert into @DUEDATES (DUEDATETYPECODE, DUEDATE, METREQUIREMENT, SENT, VIEWDATAFORMID)
        values (3, @FINALCOUNTDUEDATE, @FINALCOUNTRECEIVED, 0, '854AE2C2-0828-46D7-9FF9-42B979E6D0EE');
    end

    insert into @DUEDATES (DUEDATETYPECODE, DUEDATE, METREQUIREMENT, SENT, VIEWDATAFORMID)
    values (4, @FINALDUEDATE, case when @BALANCE > 0 then 0 else 1 end, @ORDERBALANCESENT, 'E4F77003-06D9-48FB-BE24-A9FCC3BC4777');

    select
        @RESERVATIONID as ID,
        DUEDATETYPECODE,
        DUEDATETYPECODE as DUEDATETYPE,
        DUEDATE,
        case
            when METREQUIREMENT = 1 then
                N'RES:check'
            when @CLIENTCURRENTDATE > DUEDATE then
                N'RES:warning'
            else
                null
        end as IMAGEKEY,
        SENT,
        METREQUIREMENT,
        HASCONTRACT,
        VIEWDATAFORMID
    from
        @DUEDATES
    order by
        DUEDATE, DUEDATETYPECODE;

    return 0;