Untitled

-- =============================================
-- Description: Returns an XML package of all the details of a access/gst_pass record
-- Example Call: execute [dbo].[siriussp_PassStatusInquiry] @tcTableName = 'GST_PASS', @tnPass_no = 527004001
-- =============================================
ALTER PROCEDURE [dbo].[siriussp_PassStatusInquiry]
   @tcTableName  VARCHAR(10) ,   -- must be either 'ACCESS' or 'GST_PASS'
   @tnPass_no    NUMERIC(17, 0)
AS
   SET NOCOUNT  ON;
   DECLARE @xmlResult  VARCHAR(MAX) = '',
           @xmlResult2 VARCHAR(MAX),
           @cCardNo    VARCHAR(22),
           @lDW_Active BIT ;

   SET @tcTableName = RTRIM(UPPER(@tcTableName)) ;

    -- return empty string if pass does not exist
    if not exists(SELECT pass_no FROM dbo.access   WHERE @tcTableName = 'ACCESS'   AND pass_no = @tnPass_no
                  UNION
                  SELECT pass_no FROM dbo.gst_pass WHERE @tcTableName = 'GST_PASS' AND pass_no = @tnPass_no)
    BEGIN
        SELECT   @xmlResult AS result;
        RETURN (0);
    END

    -- Guest passes only - include passholder info
   IF @tcTableName = 'GST_PASS'
   BEGIN
        SET @xmlResult = @xmlResult + '<csrPassholderInfo>' ;
        SET @xmlResult = @xmlResult +
                         ISNULL((select p.guest_no, rtrim(g.first_name) as first_name, rtrim(g.last_name) as last_name, g.birth_date
                                 from gst_pass p
                                 left outer join guests g on g.guest_no = p.guest_no
                                 where p.pass_no = @tnPass_no for xml raw, elements absent), '') ;
        SET @xmlResult = @xmlResult + '</csrPassholderInfo>'
   END

    -- Get basic access/pass info
    SET @xmlResult = @xmlResult + '<csrPassInfo>' ;
   IF @tcTableName = 'GST_PASS'
        SET @xmlResult = @xmlResult +
                         isnull((select p.last_use, p.total_uses, p.points1, p.points2, p.money1, p.money2, p.warnings,
                                        p.blackout_s, p.blackout_e, rtrim(p.bl_reason) as bl_reason,
                                        cast(case when datalength(rtrim(p.voided_by)) = 0 AND datalength(rtrim(p.voided_by)) = 0 then 0 else 1 end as bit) as voided,
                                        p.voided_by, p.voided_for, p.printcount, p.validcount, p.dis_count, p.val_parent,
                                        p.department, p.category, p.item, rtrim(i.descrip) AS DCIdescrip, p.amt_paid, p.account, a.full_name as AccntName,
                                        p.invoice_no, p.salespoint, p.operator, p.date_time
                                 from gst_pass p
                                 left outer join dbo.items i on i.department = p.department AND i.category = p.category AND i.item = p.item
                                 left outer join dbo.accounts a on p.account = a.acct_name
                                 where p.pass_no = @tnPass_no for xml raw, elements absent), '') ;
    ELSE
        SET @xmlResult = @xmlResult +
                         isnull((select p.last_use, p.total_uses, p.points1, p.points2, p.money1, p.money2, p.warnings,
                                        p.blackout_s, p.blackout_e, p.bl_reason,
                                        CAST(case when datalength(rtrim(p.voided_by)) = 0 AND datalength(rtrim(p.voided_by)) = 0 then 0 else 1 end as bit) as voided,
                                        p.voided_by, p.voided_for, p.validcount, p.dis_count, p.val_parent,
                                        p.department, p.category, p.item, rtrim(i.descrip) AS DCIdescrip, p.amt_paid, p.account, a.full_name as AccntName,
                                        p.salespoint, p.operator, p.date_time
                                 from access p
                                 left outer join dbo.items i on i.department = p.department AND i.category = p.category AND i.item = p.item
                                 left outer join dbo.accounts a on p.account = a.acct_name
                                 where p.pass_no = @tnPass_no for xml raw, elements absent), '') ;
    SET @xmlResult = @xmlResult + '</csrPassInfo>' ;

    -- Get Debitware info
   IF @tcTableName = 'GST_PASS'
    BEGIN
        SELECT @cCardNo = p.swipe_no, @lDW_Active = p.dw_active
        FROM gst_pass p
        WHERE datalength(rtrim(p.swipe_no)) > 0 AND p.invoice_no <> 0 AND p.dw_active = 1 ;
        IF datalength(rtrim(@cCardNo)) > 0 and @lDW_Active = 1
        BEGIN
            IF OBJECT_ID('tempdb..#tmpResults', N'U') IS NOT NULL   DROP TABLE #tmpResults ;
            CREATE TABLE #tmpResults (
                approval CHAR(6)
                ,invoice_no NUMERIC(17, 0)
                ,inv_bal MONEY
                ,sp_bal MONEY
                ,sp_bal_dy MONEY
                ,crLimit MONEY
                ,spLimit MONEY
                ,spLimit_Dy MONEY
                ,pass_no NUMERIC(17, 0)
                ,guest_no NUMERIC(17, 0)
                ,cErr VARCHAR(100)
                ,availbonus MONEY
                ,cashoutbal MONEY
                ,voided_for VARCHAR(15)
                ,Voided_By VARCHAR(6)
                ,ignoreExp BIT
                );
            INSERT INTO #tmpResults (
                approval
                ,invoice_no
                ,inv_bal
                ,sp_bal
                ,sp_bal_dy
                ,crLimit
                ,spLimit
                ,spLimit_Dy
                ,pass_no
                ,guest_no
                ,cErr
                ,availbonus
                ,cashoutbal
                ,voided_for
                ,Voided_By
                ,ignoreExp
                )
            EXECUTE dbo.siriussp_GetDebitwareApproval @tcOperator = 'ADMIN'
                ,@tcSalespoint = 'SYSTEM'
                ,@tcCardNo = @cCardNo
                ,@tnSaleNo = 0
                ,@cApproval = ''
                ,@tnAmount = 0 ;

            SET @xmlResult2 = isnull((select top 1 replicate('*', 12) + right(rtrim(@cCardNo),4) as swipe_no,
                                             cast(inv_bal as varchar(20)) as inv_bal,
                                             cast(sp_bal as varchar(20)) as sp_bal,
                                             cast(sp_bal_dy as varchar(20)) as sp_bal_dy,
                                             cast(crlimit as varchar(20)) as crlimit,
                                             cast(splimit as varchar(20)) as splimit,
                                             cast(splimit_dy as varchar(20)) as splimit_dy,
                                             rtrim(cErr) as err,
                                             cast(Availbonus as varchar(20)) as AvailBonus,
                                             cast(CashoutBal as varchar(20)) as CashoutBal,
                                             IgnoreExp
                                              from #tmpResults for xml raw, elements absent), '') ;
            DROP TABLE #tmpResults ;
            IF DATALENGTH(@xmlResult2) > 0
                SET @xmlResult = @xmlResult + '<csrDebitware>' + @xmlResult2 + '</csrDebitware>' ;
        END
    END

    -- Get linked passes
   IF @tcTableName = 'GST_PASS'
        SET @xmlResult2 = isnull((select p.pass_no, g.first_name, g.last_name,
                                         case when p.pass_no = p.masterpass then cast(1 as bit) else cast(0 as bit) end as ismaster, total_uses
                                  from gst_pass p
                                  left outer join guests g on g.guest_no = p.guest_no
                                  where p.masterpass = @tnPass_no and p.pass_no <> @tnPass_no for xml raw, elements absent), '') ;
    ELSE
        SET @xmlResult2 = isnull((select p.pass_no,
                                         case when p.pass_no = p.masterpass then cast(1 as bit) else cast(0 as bit) end as ismaster, total_uses
                                  from access p
                                  where p.masterpass = @tnPass_no and p.pass_no <> @tnPass_no for xml raw, elements absent), '') ;
    IF DATALENGTH(@xmlResult2) > 0
        SET @xmlResult = @xmlResult + '<csrLinkedPasses>' + @xmlResult2 + '</csrLinkedPasses>' ;

    -- Get Autosale info
   IF @tcTableName = 'GST_PASS'
    BEGIN
        DECLARE @lFirstUse BIT = 1,
                @cAutoSaleInfo VARCHAR(MAX) = '',
                @nA_shiftype TINYINT,
                @lA_multqty BIT,
                @lA_custmqty BIT,
                @nTemplateID INT ;

        SELECT TOP 1 @nA_shiftype = t.a_shiftype, @lA_multqty = t.a_multqty, @lA_custmqty = t.a_custmqty, @nTemplateID = t.TemplateID
        FROM dbo.gst_pass p
        LEFT OUTER JOIN dbo.template t ON t.department = p.department AND t.category = p.category AND t.item = p.item
        WHERE p.pass_no = @tnPass_no ;

        IF @lA_multqty = 1
        BEGIN
            IF OBJECT_ID('tempdb..#tmpResults2', N'U') IS NOT NULL  DROP TABLE #tmpResults ;
            CREATE TABLE #tmpResults2 (department char(10)
                                      ,category CHAR(10)
                                      ,item CHAR(10)
                                      ,descrip VARCHAR(25)
                                      ,maxqty INT
                                      ,remqty INT
                                      );

            -- Calculate if this is a first use
            IF @nA_shiftype < 2    -- shift = 1 day
                IF exists(select count(activ_id)
                             from dbo.gst_actv with (index = datetimerefnoactivtype)
                             where ref_no = cast(@tnPass_no as char(16)) and
                                     activ_type = 110 and
                                     date_time between DATEADD(dd,CAST(DATEDIFF(DAY,'17530101',current_timestamp) AS INT),'17530101') AND
                                                             DATEADD(ms, 86399997, CAST(CAST(current_timestamp AS DATE) AS DATETIME)) )
                    SET @lFirstUse = 0 ;

            -- Get all the defined autosale DCIs, whether standard in template or custom in gst_pass
            INSERT INTO #tmpResults2 (department, category, item, maxqty, descrip)
            SELECT case when @lA_custmqty = 0 then t.a_autodep1 else p.a_autodep1 end as department,
                   case when @lA_custmqty = 0 then t.a_autocat1 else p.a_autocat1 end as category,
                   case when @lA_custmqty = 0 then t.a_autoitm1 else p.a_autoitm1 end as item,
                   case when @lA_custmqty = 0 then t.a_maxqty1 else p.a_maxqty1 end as item, '' as descrip
            FROM dbo.template t
            LEFT OUTER JOIN dbo.gst_pass p ON p.pass_no = @tnPass_no
            WHERE t.TemplateID = @nTemplateID AND ( (@lA_custmqty = 0 AND DATALENGTH(RTRIM(t.a_autoitm1)) > 0) OR
                                                    (@lA_custmqty = 1 AND DATALENGTH(RTRIM(p.a_autoitm1)) > 0) )
            UNION
            SELECT case when @lA_custmqty = 0 then t.a_autodep2 else p.a_autodep2 end as department,
                   case when @lA_custmqty = 0 then t.a_autocat2 else p.a_autocat2 end as category,
                   case when @lA_custmqty = 0 then t.a_autoitm2 else p.a_autoitm2 end as item,
                   case when @lA_custmqty = 0 then t.a_maxqty2 else p.a_maxqty2 end as item, '' as descrip
            FROM dbo.template t
            LEFT OUTER JOIN dbo.gst_pass p ON p.pass_no = @tnPass_no
            WHERE t.TemplateID = @nTemplateID AND ( (@lA_custmqty = 0 AND DATALENGTH(RTRIM(t.a_autoitm2)) > 0) OR
                                                    (@lA_custmqty = 1 AND DATALENGTH(RTRIM(p.a_autoitm2)) > 0) )
            UNION
            SELECT case when @lA_custmqty = 0 then t.a_autodep3 else p.a_autodep3 end as department,
                   case when @lA_custmqty = 0 then t.a_autocat3 else p.a_autocat3 end as category,
                   case when @lA_custmqty = 0 then t.a_autoitm3 else p.a_autoitm3 end as item,
                   case when @lA_custmqty = 0 then t.a_maxqty3 else p.a_maxqty3 end as item, '' as descrip
            FROM dbo.template t
            LEFT OUTER JOIN dbo.gst_pass p ON p.pass_no = @tnPass_no
            WHERE t.TemplateID = @nTemplateID AND ( (@lA_custmqty = 0 AND DATALENGTH(RTRIM(t.a_autoitm3)) > 0) OR
                                                    (@lA_custmqty = 1 AND DATALENGTH(RTRIM(p.a_autoitm3)) > 0) )
            UNION
            SELECT case when @lA_custmqty = 0 then t.a_autodep4 else p.a_autodep4 end as department,
                   case when @lA_custmqty = 0 then t.a_autocat4 else p.a_autocat4 end as category,
                   case when @lA_custmqty = 0 then t.a_autoitm4 else p.a_autoitm4 end as item,
                   case when @lA_custmqty = 0 then t.a_maxqty4 else p.a_maxqty4 end as item, '' as descrip
            FROM dbo.template t
            LEFT OUTER JOIN dbo.gst_pass p ON p.pass_no = @tnPass_no
            WHERE t.TemplateID = @nTemplateID AND ( (@lA_custmqty = 0 AND DATALENGTH(RTRIM(t.a_autoitm4)) > 0) OR
                                                    (@lA_custmqty = 1 AND DATALENGTH(RTRIM(p.a_autoitm4)) > 0) )
            UNION
            SELECT case when @lA_custmqty = 0 then t.a_autodep5 else p.a_autodep5 end as department,
                   case when @lA_custmqty = 0 then t.a_autocat5 else p.a_autocat5 end as category,
                   case when @lA_custmqty = 0 then t.a_autoitm5 else p.a_autoitm5 end as item,
                   case when @lA_custmqty = 0 then t.a_maxqty5 else p.a_maxqty5 end as item, '' as descrip
            FROM dbo.template t
            LEFT OUTER JOIN dbo.gst_pass p ON p.pass_no = @tnPass_no
            WHERE t.TemplateID = @nTemplateID AND ( (@lA_custmqty = 0 AND DATALENGTH(RTRIM(t.a_autoitm5)) > 0) OR
                                                    (@lA_custmqty = 1 AND DATALENGTH(RTRIM(p.a_autoitm5)) > 0) )
            UNION
            SELECT case when @lA_custmqty = 0 then t.a_autodep6 else p.a_autodep6 end as department,
                   case when @lA_custmqty = 0 then t.a_autocat6 else p.a_autocat6 end as category,
                   case when @lA_custmqty = 0 then t.a_autoitm6 else p.a_autoitm6 end as item,
                   case when @lA_custmqty = 0 then t.a_maxqty6 else p.a_maxqty6 end as item, '' as descrip
            FROM dbo.template t
            LEFT OUTER JOIN dbo.gst_pass p ON p.pass_no = @tnPass_no
            WHERE t.TemplateID = @nTemplateID AND ( (@lA_custmqty = 0 AND DATALENGTH(RTRIM(t.a_autoitm6)) > 0) OR
                                                    (@lA_custmqty = 1 AND DATALENGTH(RTRIM(p.a_autoitm6)) > 0) ) ;

            UPDATE #tmpResults2 set descrip = i.descrip
            FROM #tmpResults2 xx JOIN items i ON xx.department = i.department AND xx.category = i.category AND xx.item = i.item ;

            IF @lFirstUse = 1 OR @nA_shiftype >= 2    -- not shift = 1 day
                UPDATE #tmpResults2 set remqty = xx.maxqty
                FROM #tmpResults2 xx ;
            ELSE
            BEGIN
                UPDATE #tmpResults2 set remqty = xx.maxqty - (coalesce(s.daycount, 0))
                FROM #tmpResults2 xx
                LEFT OUTER JOIN (SELECT u.department, u.category, u.item, SUM(u.uses) AS daycount
                             FROM dbo.uses_aut u with (index = uses_aut_date_time)
                             WHERE u.pass_no = @tnPass_no AND u.is_ticket = 0 AND
                                   u.date_time BETWEEN DATEADD(dd,CAST(DATEDIFF(DAY,'17530101',current_timestamp) AS INT),'17530101') AND
                                                                      DATEADD(ms, 86399997, CAST(CAST(current_timestamp AS DATE) AS DATETIME))
                             GROUP BY u.department, u.category, u.item) s
                             ON s.department = xx.department AND s.category = xx.category AND s.item = xx.item
            END

            SET @xmlResult2 = isnull((select * from #tmpResults2 for xml raw, elements absent), '') ;
            DROP TABLE #tmpResults2 ;
            IF DATALENGTH(@xmlResult2) > 0
                SET @xmlResult = @xmlResult + '<csrAutoSales>' + @xmlResult2 + '</csrAutoSales>' ;
        END
    END

    -- Get Reserved Seats Info
    DECLARE @nTrans_no NUMERIC(17,0) ;
   IF @tcTableName = 'GST_PASS'
        SELECT @nTrans_no = trans_no FROM dbo.gst_pass WHERE pass_no = @tnPass_no ;
    ELSE
        SELECT @nTrans_no = trans_no FROM dbo.access WHERE pass_no = @tnPass_no ;
    SET @xmlResult2 = isnull((select * from dbo.siriusfn_rsGetSeatsInfo(@nTrans_no) for xml raw, elements absent), '') ;
    IF DATALENGTH(@xmlResult2) > 0
        SET @xmlResult = @xmlResult + '<csrReservedSeats>' + @xmlResult2 + '</csrReservedSeats>' ;


    -- Get Member Benefits Info.  Use global temp table "##csrPassStatusInquiry" because INSERT EXEC statements cannot be nested
    DECLARE @tCurrentDT DATETIME = current_timestamp ;
    SET @xmlResult2 = '' ;
    BEGIN TRY
        EXEC dbo.siriussp_mbValidateBenefits @dtScanDateTime = @tCurrentDT,
                                                    @nQuantity=1, @nPassNo=@tnPass_no, @cTable= @tcTableName,
                                                    @cDepartment = '', @cCategory = '', @cItem = '', @topRows = 999, @cMbRedemptions = '',
                                                    @tlPassStatusInquiryTableOutput = 1;
        IF OBJECT_ID('tempdb..##csrPassStatusInquiry', N'U') IS NOT NULL
        BEGIN
            SET @xmlResult2 = isnull((SELECT * FROM ##csrPassStatusInquiry for xml raw, elements absent), '') ;
            DROP TABLE ##csrPassStatusInquiry ;
        END
    END TRY
    BEGIN CATCH
        IF OBJECT_ID('tempdb..##csrPassStatusInquiry', N'U') IS NOT NULL    DROP TABLE ##csrPassStatusInquiry ;
    END CATCH
    IF DATALENGTH(@xmlResult2) > 0
        SET @xmlResult = @xmlResult + '<csrMemberBenefits>' + @xmlResult2 + '</csrMemberBenefits>' ;


    -- Finally, return the it all
    SET @xmlResult = REPLACE(@xmlResult, '&#x20;', ' ') ;
    SELECT   @xmlResult AS result;
    RETURN (0);