USP_TRANSACTIONTOGROUP_GETSTUDENTENTRIES

CREATE procedure dbo.USP_TRANSACTIONTOGROUP_GETSTUDENTENTRIES
(
    @IDSETREGISTERID uniqueidentifier,
    @CHARGEDATE date,
    @BILLINGITEMID uniqueidentifier,    
    @SCHOOLS xml,    
    @AMOUNT money
)
as
begin
    set nocount on

    declare @INCLUDEDSTUDENT table
    (
        STUDENTID uniqueidentifier not null
    )

    -- limit to selection, if chosen

    if @IDSETREGISTERID is null or @IDSETREGISTERID='00000000-0000-0000-0000-000000000000'
        insert into @INCLUDEDSTUDENT (STUDENTID)
        select ID from dbo.STUDENT
    else
        insert into @INCLUDEDSTUDENT (STUDENTID)
        select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID)

    declare @STUDENT table 
    (
        STUDENTID uniqueidentifier not null, 
        STUDENTNAME nvarchar(210) null,
        SCHOOLID uniqueidentifier null,
        SCHOOL nvarchar(100) null,
        GRADELEVELID uniqueidentifier null,
        GRADELEVEL nvarchar(10) null,
        STUDENTPROGRESSIONID uniqueidentifier null,
        ENROLLMENTID uniqueidentifier null,
        AMOUNT money null,
        MESSAGE nvarchar(100) null,
        LASTNAME nvarchar(50) null,
        FIRSTNAME nvarchar(50) null,
        HASMESSAGE bit null
    )

    declare @PRICETYPECODE integer

    select @PRICETYPECODE =PRICETYPECODE FROM DBO.BILLINGITEM WHERE ID=@BILLINGITEMID

    if @PRICETYPECODE =1
    begin
        insert into @STUDENT
        (
            STUDENTID,
            STUDENTNAME,            
            AMOUNT,
            LASTNAME,
            FIRSTNAME
        )
        select S.STUDENTID, 
            C.NAME + case when LEN(C.NICKNAME) > 0 then ' (' + C.NICKNAME + ')'    else ''    end as STUDENTNAME,
            @AMOUNT, C.KEYNAME, C.FIRSTNAME
        from @INCLUDEDSTUDENT S inner join dbo.CONSTITUENT C on S.STUDENTID=C.ID

        update @STUDENT 
        set SCHOOLID = SCHOOLGRADELEVEL.SCHOOLID,
            SCHOOL = CONSTITUENT.KEYNAME,
            GRADELEVELID = GRADELEVEL.ID,
            GRADELEVEL = GRADELEVEL.ABBREVIATION 
        from @STUDENT ST inner join dbo.EDUCATIONALHISTORY on ST.STUDENTID = EDUCATIONALHISTORY.CONSTITUENTID
        inner join dbo.STUDENTPROGRESSION on dbo.STUDENTPROGRESSION.[ENROLLMENTID] = dbo.EDUCATIONALHISTORY.[ID]
        inner join dbo.SCHOOLGRADELEVEL on dbo.STUDENTPROGRESSION.[SCHOOLGRADELEVELID] = dbo.SCHOOLGRADELEVEL.[ID]        
        inner join dbo.CONSTITUENT on dbo.CONSTITUENT.ID=SCHOOLGRADELEVEL.SCHOOLID
        inner join dbo.GRADELEVEL on dbo.SCHOOLGRADELEVEL.GRADELEVELID=dbo.GRADELEVEL.ID
        where dbo.STUDENTPROGRESSION.ID=dbo.UFN_STUDENT_GETSTUDENTPROGRESSIONBYDATE(@CHARGEDATE, ST.STUDENTID, NULL)

        update @STUDENT 
        set SCHOOLID = CONSTITUENT.ID,
            SCHOOL = CONSTITUENT.KEYNAME            
        from @STUDENT ST inner join dbo.EDUCATIONALHISTORY on ST.STUDENTID = EDUCATIONALHISTORY.CONSTITUENTID
        inner join dbo.CONSTITUENT on dbo.EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID = CONSTITUENT.ID
        where dbo.EDUCATIONALHISTORY.ID=dbo.UFN_STUDENT_GETENROLLMENTBYDATE(ST.STUDENTID, NULL, @CHARGEDATE)
        AND ST.SCHOOLID is null
    end

    if @PRICETYPECODE <>1
    begin
        declare @SCHOOL table
        (
            SCHOOLID uniqueidentifier not null,
            SCHOOL nvarchar(100) null
        )

        insert into @SCHOOL (SCHOOLID, SCHOOL)    
        SELECT T.C.value('(SCHOOLID)[1]','uniqueidentifier') AS 'SCHOOLID',
            T.C.value('(NAME)[1]','nvarchar(100)') AS 'NAME'
        FROM @SCHOOLS.nodes('/SCHOOLS/ITEM') T(C)

        insert into @STUDENT
            (
                STUDENTID,
                STUDENTNAME,                        
                SCHOOLID,
                SCHOOL,
                GRADELEVELID,
                GRADELEVEL,
                STUDENTPROGRESSIONID,
                LASTNAME,
                FIRSTNAME
            )
            select S.STUDENTID, 
            C.NAME + case when LEN(C.NICKNAME) > 0 then ' (' + C.NICKNAME + ')'    else ''    end as STUDENTNAME,
            SCHOOLGRADELEVEL.SCHOOLID, CONSTITUENT.KEYNAME, GRADELEVEL.ID, GRADELEVEL.ABBREVIATION, STUDENTPROGRESSION.ID, C.KEYNAME, C.FIRSTNAME
            from @INCLUDEDSTUDENT S inner join dbo.CONSTITUENT C on S.STUDENTID=C.ID
            inner join dbo.EDUCATIONALHISTORY on S.STUDENTID = EDUCATIONALHISTORY.CONSTITUENTID
            inner join dbo.STUDENTPROGRESSION on dbo.STUDENTPROGRESSION.[ENROLLMENTID] = dbo.EDUCATIONALHISTORY.[ID]
            inner join dbo.SCHOOLGRADELEVEL on dbo.STUDENTPROGRESSION.[SCHOOLGRADELEVELID] = dbo.SCHOOLGRADELEVEL.[ID]        
            inner join dbo.CONSTITUENT on dbo.CONSTITUENT.ID=SCHOOLGRADELEVEL.SCHOOLID
            inner join dbo.GRADELEVEL on dbo.SCHOOLGRADELEVEL.GRADELEVELID=dbo.GRADELEVEL.ID
            inner join @SCHOOL sch on sch.SCHOOLID = dbo.SCHOOLGRADELEVEL.SCHOOLID
            where dbo.STUDENTPROGRESSION.ID=dbo.UFN_STUDENT_GETSTUDENTPROGRESSIONBYDATE(@CHARGEDATE, S.STUDENTID, sch.SCHOOLID)

        insert into @STUDENT
        (
            STUDENTID,
            STUDENTNAME,                        
            SCHOOLID,
            SCHOOL,
            ENROLLMENTID,
            LASTNAME,
            FIRSTNAME
        )
        select S.STUDENTID, 
        C.NAME + case when LEN(C.NICKNAME) > 0 then ' (' + C.NICKNAME + ')'    else ''    end as STUDENTNAME,
        CONSTITUENT.ID, CONSTITUENT.KEYNAME, EDUCATIONALHISTORY.ID, C.KEYNAME, C.FIRSTNAME
        from @INCLUDEDSTUDENT S inner join dbo.EDUCATIONALHISTORY on S.STUDENTID = EDUCATIONALHISTORY.CONSTITUENTID
        inner join dbo.CONSTITUENT C on S.STUDENTID=C.ID
        inner join dbo.CONSTITUENT on dbo.EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID = CONSTITUENT.ID
        inner join @SCHOOL sch on sch.SCHOOLID = CONSTITUENT.ID
        where S.STUDENTID not in (select STUDENTID from @STUDENT) AND dbo.EDUCATIONALHISTORY.ID=dbo.UFN_STUDENT_GETENROLLMENTBYDATE(S.STUDENTID, sch.SCHOOLID, @CHARGEDATE)

        update @STUDENT 
        set AMOUNT=dbo.UFN_BILLINGITEM_GETBILLINGITEMPRICE(@BILLINGITEMID, @CHARGEDATE, s.STUDENTID, s.SCHOOLID, s.GRADELEVELID)
        from @STUDENT s where MESSAGE is null    
    end

    if @PRICETYPECODE =2
        update @STUDENT set HASMESSAGE=1, MESSAGE='The student has no student progression record defined for the charge date.' from @STUDENT where GRADELEVELID is null

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

    update S
    set HASMESSAGE=1, MESSAGE= 'Duplicate with charge ' + FINANCIALTRANSACTION.USERDEFINEDID            
    from @STUDENT S
        inner join dbo.STUDENTCHARGE
            on S.STUDENTPROGRESSIONID = STUDENTCHARGE.STUDENTPROGRESSIONID
        inner join dbo.FINANCIALTRANSACTION
            on STUDENTCHARGE.ID = FINANCIALTRANSACTION.ID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM
            on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
        inner join dbo.CHARGELINEITEM
            on FINANCIALTRANSACTIONLINEITEM.ID = CHARGELINEITEM.ID
            and CHARGELINEITEM.BILLINGITEMID = @BILLINGITEMID

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

    if (@PRICETYPECODE = 3)
    begin
        update S
        set HASMESSAGE =1 ,MESSAGE= 'Duplicate with charge ' + FINANCIALTRANSACTION.USERDEFINEDID            
        from @STUDENT S
            inner join dbo.STUDENTCHARGE
                on S.ENROLLMENTID = STUDENTCHARGE.EDUCATIONALHISTORYID
            inner join dbo.ACADEMICYEAR
                on S.SCHOOLID = ACADEMICYEAR.SCHOOLID
                and @CHARGEDATE between ACADEMICYEAR.BILLINGSTARTDATE and ACADEMICYEAR.BILLINGENDDATE
            inner join dbo.FINANCIALTRANSACTION
                on STUDENTCHARGE.ID = FINANCIALTRANSACTION.ID
                and FINANCIALTRANSACTION.DATE between ACADEMICYEAR.BILLINGSTARTDATE and ACADEMICYEAR.BILLINGENDDATE
            inner join dbo.FINANCIALTRANSACTIONLINEITEM
                on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
            inner join dbo.CHARGELINEITEM
                on FINANCIALTRANSACTIONLINEITEM.ID = CHARGELINEITEM.ID
                and CHARGELINEITEM.BILLINGITEMID = @BILLINGITEMID
    end

    if @PRICETYPECODE =2    
        update S
        set HASMESSAGE=1, MESSAGE= 'No amount defined for calculated grade level'
        from @STUDENT S
        where AMOUNT=0 AND GRADELEVELID is not null

    if @PRICETYPECODE =3
        update S
        set HASMESSAGE=1, MESSAGE= 'No amount defined for school'
        from @STUDENT S
        where AMOUNT=0 AND SCHOOLID is not null

    select STUDENTID, 
        STUDENTNAME,
        SCHOOLID,
        SCHOOL,
        GRADELEVELID,
     GRADELEVEL,
        STUDENTPROGRESSIONID,
        ENROLLMENTID,
        AMOUNT,
        MESSAGE
    from @STUDENT
    order by HASMESSAGE, LASTNAME, FIRSTNAME
end