USP_BILLINGGENERATETRANSACTIONSCHARGE_BULKPROCESS

CREATE procedure dbo.USP_BILLINGGENERATETRANSACTIONSCHARGE_BULKPROCESS
(
    @IDSETREGISTERID uniqueidentifier,
    @TRANSACTIONDATE date,
    @BILLINGITEMS xml,
    @SUCCESSTABLE nvarchar(128),
    @EXCEPTIONTABLE nvarchar(128),
    @SUCCESSCOUNT int = 0 output,
    @EXCEPTIONCOUNT int = 0 output,
    @CHANGEAGENTID uniqueidentifier,
    @CHANGEDATE datetime = null,
    @SCHOOLS xml
)
as
begin
    set nocount on

    if @CHANGEAGENTID is null
        exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    if @CHANGEDATE is null 
        set @CHANGEDATE = getdate()

    -- Table of schools to generate charges for

    declare @SCHOOL table
    (
        SCHOOLID uniqueidentifier not null,
        SCHOOL nvarchar(100) null
    )

    insert into @SCHOOL (SCHOOLID)
    select SCHOOLID from dbo.UFN_BILLINGGENERATETRANSACTIONSPROCESS_GETSCHOOLS_FROMITEMLISTXML(@SCHOOLS)

    update @SCHOOL
    set SCHOOL = CONSTITUENT.KEYNAME
    from @SCHOOL S
        inner join dbo.CONSTITUENT
            on S.SCHOOLID = CONSTITUENT.ID


    -- Table of billing items and cost histories to add charges for.

    declare @BILLINGITEM table
    (
        BILLINGITEMID uniqueidentifier not null,
        BILLINGITEMNAME nvarchar(100) null,
        PRICETYPECODE smallint null,
        BILLINGITEMDATESID uniqueidentifier null
    )

    -- Add the selected billing items specified in the parameters.

    insert into @BILLINGITEM (BILLINGITEMID)
    select BILLINGITEMID from dbo.UFN_BILLINGGENERATETRANSACTIONSPROCESS_GETBILLINGITEMS_FROMITEMLISTXML(@BILLINGITEMS)

    -- If no billing items were selected, all vary by grade level and vary by school billing items are used.

    if (select count(*) from @BILLINGITEM) = 0
        insert into @BILLINGITEM (BILLINGITEMID)
        select ID from dbo.BILLINGITEM
        where BILLINGITEM.PRICETYPECODE in (2,3) --Vary by grade level and vary by school billing items only


    -- Set the billing item information, including the cost history record whose date range includes the TRANSACTIONDATE.

    update BI set
        BILLINGITEMNAME = BILLINGITEM.NAME,
        PRICETYPECODE = BILLINGITEM.PRICETYPECODE,
        BILLINGITEMDATESID = BILLINGITEMDATES.ID
    from @BILLINGITEM BI
        inner join dbo.BILLINGITEM
            on BILLINGITEM.ID = BI.BILLINGITEMID
        inner join dbo.BILLINGITEMDATES
            on BILLINGITEM.ID = BILLINGITEMDATES.BILLINGITEMID
    where
        BILLINGITEMDATES.BILLINGITEMID = BI.BILLINGITEMID and
        BILLINGITEMDATES.STARTDATE is null or
        (BILLINGITEMDATES.ENDDATE is null and @TRANSACTIONDATE >= BILLINGITEMDATES.STARTDATE) or
        @TRANSACTIONDATE between BILLINGITEMDATES.STARTDATE and BILLINGITEMDATES.ENDDATE


    -- Table of students who should receive charges, using selection specified in the parameters, if one exists, otherwise all students.

    declare @INCLUDEDSTUDENT table
    (
        STUDENTID uniqueidentifier not null
    )

    if @IDSETREGISTERID is null
        insert into @INCLUDEDSTUDENT (STUDENTID)
        select ID from dbo.STUDENT
    else
        insert into @INCLUDEDSTUDENT (STUDENTID)
        select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID)


    -- Table of student SPEs to use when generating charges for vary by grade level billing items

    -- Charges for vary by grade level billing items are created for every SPE that contains the transaction date.

    declare @VARYBYGRADELEVELSTUDENT table 
    (
        STUDENTID uniqueidentifier not null, 
        STUDENTNAME nvarchar(154) null,
        STUDENTPROGRESSIONID uniqueidentifier null,
        GRADELEVELID uniqueidentifier null,
        GRADELEVEL nvarchar(10) null,
        SCHOOLID uniqueidentifier null,
        SCHOOL nvarchar(100) null
    )

    -- Table of student SPE/Enrollments to use when generating charges for vary by school billing items

    -- Charges for vary by school billing items are created for every enrollment that contains the transaction date,

    -- or that has an SPE that contains the transaction date, but only one charge per enrollment, using the SPE if one exists,

    -- otherwise, using just the enrollment and academic year.

    declare @VARYBYSCHOOLSTUDENT table 
    (
        STUDENTID uniqueidentifier not null, 
        STUDENTNAME nvarchar(154) null,
        STUDENTPROGRESSIONID uniqueidentifier null,
        ENROLLMENTID uniqueidentifier null,
        GRADELEVELID uniqueidentifier null,
        GRADELEVEL nvarchar(10) null,
        SCHOOLID uniqueidentifier null,
        SCHOOL nvarchar(100) null
    )

    -- Table of all charges to be created

    create table #STUDENTTRANSACTION
    (
        ID uniqueidentifier not null,
        STUDENTID uniqueidentifier not null,
        STUDENTNAME nvarchar(154) collate DATABASE_DEFAULT,
        STUDENTPROGRESSIONID uniqueidentifier,
        ENROLLMENTID uniqueidentifier,
        GRADELEVEL nvarchar(10) collate DATABASE_DEFAULT,
        SCHOOLID uniqueidentifier,
        SCHOOL nvarchar(100) collate DATABASE_DEFAULT,
        BILLINGITEMID uniqueidentifier,
        BILLINGITEMNAME nvarchar(100) collate DATABASE_DEFAULT,
        PRICETYPECODE smallint,
        TRANSACTIONID uniqueidentifier,
        TRANSACTIONAMOUNT money,
        CHARGEID nvarchar(60) collate DATABASE_DEFAULT,
        EXCEPTION nvarchar(20) collate DATABASE_DEFAULT,
        DUPLICATECHARGEID nvarchar(60) collate DATABASE_DEFAULT,
        DUPLICATETRANSACTIONID uniqueidentifier
    )

    -- We want all SPEs for students in @INCLUDEDSTUDENT table that are valid for the transaction date, using the academic year's billing dates,

    -- limiting the student SPEs to those for schools in the included parameter list.

    insert into @VARYBYGRADELEVELSTUDENT
    (
        STUDENTID,
        STUDENTNAME,
        STUDENTPROGRESSIONID,
        GRADELEVELID,
        GRADELEVEL,
        SCHOOLID,
        SCHOOL
    )
    select
        CONSTITUENT.ID,
        CONSTITUENT.NAME,
        STUDENTPROGRESSION.ID,
        GRADELEVEL.ID,
        GRADELEVEL.ABBREVIATION,
        S.SCHOOLID,
        S.SCHOOL
    from
        dbo.CONSTITUENT
            inner join @INCLUDEDSTUDENT as INCLUDEDSTUDENT
                on CONSTITUENT.ID = INCLUDEDSTUDENT.STUDENTID
            inner join dbo.EDUCATIONALHISTORY 
                on CONSTITUENT.ID = EDUCATIONALHISTORY.CONSTITUENTID
            inner join dbo.STUDENTPROGRESSION
                on EDUCATIONALHISTORY.ID = STUDENTPROGRESSION.ENROLLMENTID
            inner join dbo.SCHOOLGRADELEVEL 
                on STUDENTPROGRESSION.SCHOOLGRADELEVELID = SCHOOLGRADELEVEL.ID
            inner join dbo.GRADELEVEL
                on SCHOOLGRADELEVEL.GRADELEVELID = GRADELEVEL.ID
            inner join dbo.ACADEMICYEAR 
                on SCHOOLGRADELEVEL.SCHOOLID = ACADEMICYEAR.SCHOOLID
                and STUDENTPROGRESSION.STARTDATE = ACADEMICYEAR.STARTDATE
                and STUDENTPROGRESSION.ENDDATE = ACADEMICYEAR.ENDDATE
            inner join @SCHOOL S
                on SCHOOLGRADELEVEL.SCHOOLID = S.SCHOOLID
    where
        @TRANSACTIONDATE between ACADEMICYEAR.BILLINGSTARTDATE and ACADEMICYEAR.BILLINGENDDATE

    -- Cross join @VARYBYGRADELEVELSTUDENT with @BILLINGITEM to get all vary by grade level charges that should be created.

    -- Calculate the amount using the student's grade level on the SPE and the billing item's cost history record.

    insert into #STUDENTTRANSACTION
    (
        ID,
        STUDENTID,
        STUDENTNAME,
        STUDENTPROGRESSIONID,
        GRADELEVEL,
        SCHOOL,
        BILLINGITEMID,
        BILLINGITEMNAME,
        PRICETYPECODE,
        TRANSACTIONAMOUNT
    )
    select
        newid(),
        GRADELEVELSTUDENT.STUDENTID,
        GRADELEVELSTUDENT.STUDENTNAME,
        GRADELEVELSTUDENT.STUDENTPROGRESSIONID,
        GRADELEVELSTUDENT.GRADELEVEL,
        GRADELEVELSTUDENT.SCHOOL,
        BI.BILLINGITEMID,
        BI.BILLINGITEMNAME,
        BI.PRICETYPECODE,
        BILLINGITEMPRICEBYGRADELEVEL.PRICE
    from
        @VARYBYGRADELEVELSTUDENT GRADELEVELSTUDENT
            cross join @BILLINGITEM BI
            inner join BILLINGITEMPRICEBYGRADELEVEL
                on GRADELEVELSTUDENT.GRADELEVELID = BILLINGITEMPRICEBYGRADELEVEL.GRADELEVELID
                and BILLINGITEMPRICEBYGRADELEVEL.BILLINGITEMDATESID = BI.BILLINGITEMDATESID
                and (BILLINGITEMPRICEBYGRADELEVEL.PRICE > 0)
    where BI.PRICETYPECODE = 2

    -- We want all SPEs for students in @INCLUDEDSTUDENT table that are valid for the transaction date, using the academic year's billing dates.

    -- We also want students whose enrollments include the transaction date but do not have a valid SPE for that date.

    -- Limit SPE/Enrollments to those for schools in the parameter list.


    -- Find valid SPEs for TRANSACTIONDATE

    insert into @VARYBYSCHOOLSTUDENT
    (
        STUDENTID,
        STUDENTNAME,
        STUDENTPROGRESSIONID,
        ENROLLMENTID,
        GRADELEVELID,
        GRADELEVEL,
        SCHOOLID
    )
    select
        CONSTITUENT.ID,
        CONSTITUENT.NAME,
        STUDENTPROGRESSION.ID,
        EDUCATIONALHISTORY.ID,
        GRADELEVEL.ID,
        GRADELEVEL.ABBREVIATION,
        SCHOOLGRADELEVEL.SCHOOLID
    from
        dbo.CONSTITUENT
            inner join @INCLUDEDSTUDENT as INCLUDEDSTUDENT
                on CONSTITUENT.ID = INCLUDEDSTUDENT.STUDENTID
            inner join dbo.EDUCATIONALHISTORY 
                on CONSTITUENT.ID = EDUCATIONALHISTORY.CONSTITUENTID
            inner join dbo.STUDENTPROGRESSION
                on EDUCATIONALHISTORY.ID = STUDENTPROGRESSION.ENROLLMENTID
            inner join dbo.SCHOOLGRADELEVEL 
                on STUDENTPROGRESSION.SCHOOLGRADELEVELID = SCHOOLGRADELEVEL.ID
            inner join dbo.GRADELEVEL
                on SCHOOLGRADELEVEL.GRADELEVELID = GRADELEVEL.ID
            inner join dbo.ACADEMICYEAR 
                on SCHOOLGRADELEVEL.SCHOOLID = ACADEMICYEAR.SCHOOLID
                and STUDENTPROGRESSION.STARTDATE = ACADEMICYEAR.STARTDATE
                and STUDENTPROGRESSION.ENDDATE = ACADEMICYEAR.ENDDATE
    where
        @TRANSACTIONDATE between ACADEMICYEAR.BILLINGSTARTDATE and ACADEMICYEAR.BILLINGENDDATE

    declare @ENROLLMENT table
    (
        ENROLLMENTID uniqueidentifier not null
    )

    insert into @ENROLLMENT (ENROLLMENTID)
    select ENROLLMENTID from @VARYBYSCHOOLSTUDENT

    -- Find valid enrollments that do not contain valid SPEs for the TRANSACTIONDATE.

    insert into @VARYBYSCHOOLSTUDENT
    (
        STUDENTID,
        STUDENTNAME,
        ENROLLMENTID,
        SCHOOLID
    )
    select
        CONSTITUENT.ID,
        CONSTITUENT.NAME,
        EDUCATIONALHISTORY.ID,
        EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID
    from
        dbo.CONSTITUENT
            inner join @INCLUDEDSTUDENT as INCLUDEDSTUDENT
                on CONSTITUENT.ID = INCLUDEDSTUDENT.STUDENTID
            inner join dbo.EDUCATIONALHISTORY 
                on CONSTITUENT.ID = EDUCATIONALHISTORY.CONSTITUENTID
    where
        @TRANSACTIONDATE >= dbo.UFN_DATE_FROMFUZZYDATE(EDUCATIONALHISTORY.STARTDATE)
        and ((EDUCATIONALHISTORY.DATELEFT = '00000000') or (@TRANSACTIONDATE <= dbo.UFN_DATE_FROMFUZZYDATE(EDUCATIONALHISTORY.DATELEFT)))
        and (not EDUCATIONALHISTORY.ID in (select ENROLLMENTID from @ENROLLMENT))

    insert into #STUDENTTRANSACTION
    (
        ID,
        STUDENTID,
        STUDENTNAME,
        STUDENTPROGRESSIONID,
        ENROLLMENTID,
        GRADELEVEL,
        SCHOOLID,
        SCHOOL,
        BILLINGITEMID,
        BILLINGITEMNAME,
        PRICETYPECODE,
        TRANSACTIONAMOUNT
    )
    select
        newid(),
        SCHOOLSTUDENT.STUDENTID,
        SCHOOLSTUDENT.STUDENTNAME,
        SCHOOLSTUDENT.STUDENTPROGRESSIONID,
        SCHOOLSTUDENT.ENROLLMENTID,
        SCHOOLSTUDENT.GRADELEVEL,
        S.SCHOOLID,
        S.SCHOOL,
        BI.BILLINGITEMID,
        BI.BILLINGITEMNAME,
        BI.PRICETYPECODE,
        BILLINGITEMPRICEBYSCHOOL.PRICE
    from
        @VARYBYSCHOOLSTUDENT SCHOOLSTUDENT
            cross join @BILLINGITEM BI
            inner join BILLINGITEMPRICEBYSCHOOL
                on SCHOOLSTUDENT.SCHOOLID = BILLINGITEMPRICEBYSCHOOL.SCHOOLID
                and BILLINGITEMPRICEBYSCHOOL.BILLINGITEMDATESID = BI.BILLINGITEMDATESID
                and (BILLINGITEMPRICEBYSCHOOL.PRICE > 0)
            inner join @SCHOOL S
                on SCHOOLSTUDENT.SCHOOLID = S.SCHOOLID
            inner join dbo.ACADEMICYEAR
                on S.SCHOOLID = ACADEMICYEAR.SCHOOLID
                and @TRANSACTIONDATE between ACADEMICYEAR.BILLINGSTARTDATE and ACADEMICYEAR.BILLINGENDDATE
    where BI.PRICETYPECODE = 3

    -- Need to exclude any transactions that would create duplicates.

    -- Set EXCEPTION column to 'DUPLICATE' if the charge should not be added and set DUPLICATECHARGEID column to the existing charge ID.


    -- For vary by grade level charges and vary by school charges linked to SPEs, use the student progression and billing item IDs to match.

    update ST
    set EXCEPTION = 'DUPLICATE',
        DUPLICATECHARGEID = FINANCIALTRANSACTION.USERDEFINEDID,
        DUPLICATETRANSACTIONID = FINANCIALTRANSACTION.ID
    from #STUDENTTRANSACTION ST
        inner join dbo.STUDENTCHARGE
            on ST.STUDENTPROGRESSIONID = STUDENTCHARGE.STUDENTPROGRESSIONID
        inner join dbo.CHARGE
            on STUDENTCHARGE.ID = CHARGE.ID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM
            on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = CHARGE.ID
        inner join dbo.CHARGELINEITEM
            on ST.BILLINGITEMID = CHARGELINEITEM.BILLINGITEMID
        inner join dbo.FINANCIALTRANSACTION
            on CHARGE.ID = FINANCIALTRANSACTION.ID
    where CHARGE.ID is not null

    -- For vary by school charges linked to enrollments, use the student enrollment, academic year (by billing dates) and billing item IDs to match.

    update ST
    set EXCEPTION = 'DUPLICATE',
        DUPLICATECHARGEID = FINANCIALTRANSACTION.USERDEFINEDID,
        DUPLICATETRANSACTIONID = FINANCIALTRANSACTION.ID
    from #STUDENTTRANSACTION ST
        inner join dbo.STUDENTCHARGE
            on ST.ENROLLMENTID = STUDENTCHARGE.EDUCATIONALHISTORYID
        inner join dbo.ACADEMICYEAR
            on ST.SCHOOLID = ACADEMICYEAR.SCHOOLID
            and @TRANSACTIONDATE between ACADEMICYEAR.BILLINGSTARTDATE and ACADEMICYEAR.BILLINGENDDATE
        inner join dbo.CHARGE
            on STUDENTCHARGE.ID = CHARGE.ID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM
            on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = CHARGE.ID
        inner join dbo.CHARGELINEITEM
            on ST.BILLINGITEMID = CHARGELINEITEM.BILLINGITEMID
        inner join dbo.FINANCIALTRANSACTION
            on CHARGE.ID = FINANCIALTRANSACTION.ID
            and FINANCIALTRANSACTION.DATE between ACADEMICYEAR.BILLINGSTARTDATE and ACADEMICYEAR.BILLINGENDDATE
    where CHARGE.ID is not null
        and ST.PRICETYPECODE = 3


    -- Use configuration defaults to calculate due date.

    declare @DUEDATE date
    set @DUEDATE = dbo.UFN_CHARGE_GETDEFAULTDUEDATE(@TRANSACTIONDATE)

    -- Create all the charges that are not duplicates from the STUDENTTRANSACTION table.

    declare @ROWID uniqueidentifier;
    declare @STUDENTID uniqueidentifier
    declare @STUDENTPROGRESSIONID uniqueidentifier
    declare @ENROLLMENTID uniqueidentifier
    declare @BILLINGITEMID uniqueidentifier
    declare @TRANSACTIONID uniqueidentifier
    declare @TRANSACTIONAMOUNT money
    declare @CHARGEID nvarchar(60);
    declare @BILLINGITEMNAME nvarchar(100);

    declare @AUTOAPPLY bit
    select @AUTOAPPLY=CHARGE from dbo.APPLICATIONRULES 

    declare STUDENTTRANSACTIONCURSOR cursor local for
        select
            ID,
            STUDENTID,
            STUDENTPROGRESSIONID,
            ENROLLMENTID,
            BILLINGITEMID,
            TRANSACTIONID,
            TRANSACTIONAMOUNT,
            BILLINGITEMNAME
        from #STUDENTTRANSACTION
        where EXCEPTION is null
        for update of TRANSACTIONID

    open STUDENTTRANSACTIONCURSOR
    fetch next from STUDENTTRANSACTIONCURSOR
    into @ROWID, @STUDENTID, @STUDENTPROGRESSIONID, @ENROLLMENTID, @BILLINGITEMID, @TRANSACTIONID, @TRANSACTIONAMOUNT, @BILLINGITEMNAME;

    while @@fetch_status = 0
    begin
        set @CHARGEID = null;

        exec dbo.USP_CHARGE_ADD @ID=@TRANSACTIONID output, @CHANGEAGENTID=@CHANGEAGENTID, @STUDENTID=@STUDENTID, @DUEDATE=@DUEDATE, @AMOUNT=@TRANSACTIONAMOUNT, @POSTDATE=@TRANSACTIONDATE, @CHARGEDATE=@TRANSACTIONDATE, @BILLINGITEMID=@BILLINGITEMID, @STUDENTPROGRESSIONID=@STUDENTPROGRESSIONID, @CHARGEID=@CHARGEID output, @DESCRIPTION=@BILLINGITEMNAME, @STUDENTENROLLMENTID=@ENROLLMENTID, @AUTOAPPLY=@AUTOAPPLY;

        update ST
            set CHARGEID = @CHARGEID,
                TRANSACTIONID = @TRANSACTIONID
            from #STUDENTTRANSACTION as ST
            where (ST.ID = @ROWID);

        fetch next from STUDENTTRANSACTIONCURSOR
        into @ROWID, @STUDENTID, @STUDENTPROGRESSIONID, @ENROLLMENTID, @BILLINGITEMID, @TRANSACTIONID, @TRANSACTIONAMOUNT, @BILLINGITEMNAME;
    end

    close STUDENTTRANSACTIONCURSOR
    deallocate STUDENTTRANSACTIONCURSOR


    -- Create success and exception output tables and counts of each for the process status page.

    declare @LOGSUCCESSSQL nvarchar(1000)
    declare @LOGEXCEPTIONSQL nvarchar(1000)

    set @LOGSUCCESSSQL = N'insert into dbo.' + @SUCCESSTABLE + N' (STUDENTID, STUDENTNAME, GRADELEVEL, SCHOOL, BILLINGITEMID, BILLINGITEMNAME, TRANSACTIONID, AMOUNT, CHARGEID) 
        select 
            STUDENTID, 
            STUDENTNAME, 
            GRADELEVEL, 
            SCHOOL,
            BILLINGITEMID, 
            BILLINGITEMNAME, 
            TRANSACTIONID, 
            TRANSACTIONAMOUNT,
            CHARGEID
        from #STUDENTTRANSACTION where EXCEPTION is null'
    exec sp_executesql @LOGSUCCESSSQL

    select @SUCCESSCOUNT = COUNT(distinct STUDENTID) from #STUDENTTRANSACTION where EXCEPTION is null

    set @LOGEXCEPTIONSQL = N'insert into dbo.' + @EXCEPTIONTABLE + N' (STUDENTID, STUDENTNAME, GRADELEVEL, SCHOOL, BILLINGITEMID, BILLINGITEMNAME, AMOUNT, ERRORMESSAGE, DUPLICATECHARGEID, DUPLICATETRANSACTIONID) 
        select 
            STUDENTID, 
            STUDENTNAME, 
            GRADELEVEL,
            SCHOOL,
            BILLINGITEMID, 
            BILLINGITEMNAME,
            TRANSACTIONAMOUNT, 
            EXCEPTION,
            DUPLICATECHARGEID,
            DUPLICATETRANSACTIONID
        from #STUDENTTRANSACTION where EXCEPTION is not null'

    exec sp_executesql @LOGEXCEPTIONSQL

    select @EXCEPTIONCOUNT = count(distinct STUDENTID) from #STUDENTTRANSACTION where EXCEPTION is not null
end