====== Task Scheduler ======

====== Built-In Tasks ======
  * Batch Queue Maintenance
  * Messenger Maintenace
  * POSExtract
  * PsNotificaitonTrigger_8_0
  * Remote Dial-In Extract

====== No Field Trip ======

<code sql>
--Create New Flags Where Opted Out in OLR and Flag start date does not match
INSERT INTO ProgramParticipation (programID,personID,startDate,districtID)
SELECT P.programID, CS.personID, CAST(CS.date AS DATE) AS startDate,D.districtID
FROM CustomStudent CS
JOIN CampusAttribute CA ON CA.attributeID = CS.attributeID AND CA.object = 'Release Agreements OLR' AND CA.element = 'fieldTripRelease' AND CS.value = 'N'
LEFT JOIN ProgramParticipation PP ON PP.personID = CS.personID AND PP.startDate = CAST(CS.date AS DATE)
CROSS JOIN (SELECT programID FROM Program WHERE code = 'FIELDTRIP') P
CROSS JOIN (SELECT districtID FROM District WHERE NUMBER = 066105) D
WHERE PP.participationID IS NULL
 
--End Date Flags Where Opted In via OLR.
UPDATE PP
SET endDate = GETDATE()
FROM CustomStudent CS
JOIN CampusAttribute CA ON CA.attributeID = CS.attributeID AND CA.object = 'Release Agreements OLR' AND CA.element = 'fieldTripRelease' AND CS.value = 'Y'
JOIN ProgramParticipation PP ON PP.personID = CS.personID
JOIN Program P ON P.code = 'FIELDTRIP' AND P.programID = PP.programID
WHERE endDate IS NULL
 
--Copy Flag Description to User Warning
UPDATE PP
SET userWarning = P.[description]
FROM ProgramParticipation PP
JOIN Program P ON P.programID = PP.programID AND P.code = 'FIELDTRIP'
WHERE PP.userWarning <> P.[description] OR PP.userWarning IS NULL
</code>
====== No Media ======
<code sql>
--Create New Flags Where Opted Out in OLR and Flag start date does not match
INSERT INTO ProgramParticipation (programID,personID,startDate,districtID)
SELECT P.programID, CS.personID, CAST(CS.date AS date) AS startDate,D.districtID
FROM CustomStudent CS
JOIN CampusAttribute CA ON CA.attributeID = CS.attributeID AND CA.object = 'Release Agreements OLR' AND CA.element = 'mediaRelease' AND CS.value = 'N'
LEFT JOIN ProgramParticipation PP ON PP.personID = CS.personID AND PP.startDate = CAST(CS.date AS date)
CROSS JOIN (SELECT programID FROM Program WHERE code = 'MEDIA') P
CROSS JOIN (SELECT districtID FROM District WHERE number = 066105) D
WHERE PP.participationID IS NULL

--End Date Flags Where Opted In via OLR.
UPDATE PP
SET endDate = GETDATE()
FROM CustomStudent CS
JOIN CampusAttribute CA ON CA.attributeID = CS.attributeID AND CA.object = 'Release Agreements OLR' AND CA.element = 'mediaRelease' AND CS.value = 'Y'
JOIN ProgramParticipation PP ON PP.personID = CS.personID
JOIN Program P ON P.code = 'MEDIA' AND P.programID = PP.programID
WHERE endDate IS NULL

--Copy Flag Description to User Warning
UPDATE PP
SET userWarning = P.[description]
FROM ProgramParticipation PP
JOIN Program P ON P.programID = PP.programID AND P.code = 'MEDIA'
WHERE PP.userWarning <> P.[description] OR PP.userWarning IS NULL
</code>


====== No Survey ======

<code sql>
--Create New Flags Where Opted Out in OLR and Flag start date does not match
INSERT INTO ProgramParticipation (programID,personID,startDate,districtID)
SELECT P.programID, CS.personID, CAST(CS.date AS DATE) AS startDate,D.districtID
FROM CustomStudent CS
JOIN CampusAttribute CA ON CA.attributeID = CS.attributeID AND CA.object = 'Release Agreements OLR' AND CA.element = 'surveyRelease' AND CS.value = 'N'
LEFT JOIN ProgramParticipation PP ON PP.personID = CS.personID AND PP.startDate = CAST(CS.date AS DATE)
CROSS JOIN (SELECT programID FROM Program WHERE code = 'SURVEY') P
CROSS JOIN (SELECT districtID FROM District WHERE NUMBER = 066105) D
WHERE PP.participationID IS NULL
 
--End Date Flags Where Opted In via OLR.
UPDATE PP
SET endDate = GETDATE()
FROM CustomStudent CS
JOIN CampusAttribute CA ON CA.attributeID = CS.attributeID AND CA.object = 'Release Agreements OLR' AND CA.element = 'surveyRelease' AND CS.value = 'Y'
JOIN ProgramParticipation PP ON PP.personID = CS.personID
JOIN Program P ON P.code = 'SURVEY' AND P.programID = PP.programID
WHERE endDate IS NULL
 
--Copy Flag Description to User Warning
UPDATE PP
SET userWarning = P.[description]
FROM ProgramParticipation PP
JOIN Program P ON P.programID = PP.programID AND P.code = 'SURVEY'
WHERE PP.userWarning <> P.[description] OR PP.userWarning IS NULL
</code>



====== Parent Pickup PIN ======
<code sql>
DECLARE @Flag INT
SET @Flag = 1

WHILE @Flag = 1
BEGIN
    -- Perform your actions here
    
DECLARE @GeneratedPIN INT;
SET @GeneratedPIN = FLOOR(RAND() * 900000) + 100000;
WHILE EXISTS (SELECT 1 FROM CustomTransportation WHERE value = @GeneratedPIN AND attributeID = 1094)
BEGIN
    SET @GeneratedPIN = FLOOR(RAND() * 900000) + 100000;
END
INSERT INTO CustomTransportation (attributeID,personID,[value],[date],transportationID)
SELECT TOP 1
    1094 AS attributeID,
    P.personID,
    @GeneratedPIN AS [value],
    GETDATE() AS [date],
    T.transportationID
FROM Person P
JOIN Enrollment E ON E.PersonID = P.PersonID AND E.endDate IS NULL
JOIN Calendar C ON C.calendarID = E.calendarID AND C.exclude = 0 AND C.summerSchool = 0
JOIN SchoolYear SY ON SY.endYear = C.endYear AND SY.active = 1
JOIN School S ON S.schoolID = C.schoolID AND S.number IN (4020, 3000)
LEFT JOIN Transportation T ON T.personID = P.personID AND T.calendarID = C.calendarID AND T.endDate IS NULL
LEFT JOIN CustomTransportation CT ON T.transportationID = CT.transportationID AND attributeID = 1094
WHERE CT.[value] IS NULL AND T.transportationID IS NOT NULL

    -- Check if any matching records exist
    IF EXISTS (SELECT 1 
        FROM Person P
        JOIN Enrollment E ON E.PersonID = P.PersonID AND E.endDate IS NULL
        JOIN Calendar C ON C.calendarID = E.calendarID AND C.exclude = 0 AND C.summerSchool = 0
        JOIN SchoolYear SY ON SY.endYear = C.endYear AND SY.active = 1
        JOIN School S ON S.schoolID = C.schoolID AND S.number IN (4020, 3000)
        LEFT JOIN Transportation T ON T.personID = P.personID AND T.calendarID = C.calendarID AND T.endDate IS NULL
        LEFT JOIN CustomTransportation CT ON T.transportationID = CT.transportationID AND attributeID = 1094
        WHERE CT.[value] IS NULL AND T.transportationID IS NOT NULL
    )
        SET @Flag = 1
    ELSE
        SET @Flag = 0
END
</code>

====== Student Number ======
<code sql>
--Update StudentNumber to PersonID where person has an active enrollment record and has a NULL student number.
UPDATE Person
SET studentNumber = P.personID
FROM Person P
JOIN Enrollment E ON P.personID = e.personID
WHERE studentNumber IS NULL AND E.endDate IS NULL

--Delete the student number history where PersonID is studentNumber
DELETE FROM StudentNumberHistory
WHERE CAST(personID AS varchar) = studentNumber

--Update StudentNumber to StateID Where StudentNumber is PersonID and a stateID is available.
UPDATE Person
SET studentNumber = stateID
WHERE stateID IS NOT NULL AND stateID <> studentNumber
</code>

====== SAML SP Config ======
<code sql>
UPDATE SAMLSPConfig
SET idpMetadataUrl = 'https://login.microsoftonline.com/d20eaf2d-daf4-4af0-b96e-bd270dd7ac59/federationmetadata/2007-06/federationmetadata.xml?appid=97415069-bc87-4b32-bc3a-f04b1c9f7a74'
WHERE DB_NAME() = 'osage_school_sandbox'

UPDATE SAMLSPConfig
SET idpMetadataUrl = 'https://login.microsoftonline.com/d20eaf2d-daf4-4af0-b96e-bd270dd7ac59/federationmetadata/2007-06/federationmetadata.xml?appid=fe845c24-66c4-42e3-a4cc-96ef0977cc15'
WHERE DB_NAME() = 'osage_school_staging'
</code>