USE DST20000Suffix

/* ------------------------------------------------------------------------
Author: Aeries Software, Chris Brown
Last Modified: 2021/03/07
Version 1.0
------------------------------------------------------------------------ */

/*------------------------------------------------------------------------
	-Before running, be sure to set the output to text (Ctrl+T)
	-Verify that GRD been rolled to GRH and HIS for each school
------------------------------------------------------------------------*/

SET NOCOUNT ON
PRINT ''
PRINT '-------------------------------------------------------------------------------------------------'
PRINT '***************************************PRE-ROLLOVER REPORT***************************************'
PRINT '-------------------------------------------------------------------------------------------------'
PRINT ''

PRINT '--------------------------------------------------------------------------------------------------'
PRINT 'Review the last day of school in each calendar. Use this to plan when the rollover can take place.'
PRINT '--------------------------------------------------------------------------------------------------'
PRINT ''
SELECT SC, CONVERT(VARCHAR(10), MAX(DT), 101) 'Last Day' FROM DAY WHERE DEL = 0 AND HO NOT IN ('$', '@', '#')
	AND SC IN (SELECT CD FROM LOC WHERE TG = '' AND DEL = 0 AND CD <> 0 AND U = 0)
GROUP BY SC

PRINT '-----------------------------------------------'
PRINT 'Count of non del-tagged District Asset records.'
PRINT '-----------------------------------------------'
PRINT ''
IF EXISTS (SELECT TOP(1) * FROM DRA WHERE DEL = 0)
SELECT COUNT(*) 'District Resources Count' FROM DRA
ELSE
PRINT 'No District Resources'
PRINT ''

PRINT '------------------------------------------------------------------------------------------------------------------------------------------------------------'
PRINT 'Identifies whether or not there are differences in graduation requirements year over year. If no differences, no need to increment gradudation requirements.'
PRINT '------------------------------------------------------------------------------------------------------------------------------------------------------------'
PRINT ''
IF EXISTS (SELECT * FROM REQ WHERE DEL = 0 AND C1 <> C2 AND C2 <> C3  AND C3 <> C4)
PRINT 'Increment Grad Requirements'
ELSE
PRINT 'No REQ/or Differences'
PRINT ''

PRINT '-----------------------------------------------------------------------------------------------------------------------------------------------------------------------'
PRINT 'Identifies whether or not there are differences in the street table year over year. If no differences, no need to copy next year''s schools to current year in Streets.'
PRINT '-----------------------------------------------------------------------------------------------------------------------------------------------------------------------'
PRINT ''
IF EXISTS (SELECT * FROM STR WHERE DEL = 0 AND (G0 <> N0 OR G1 <> N1 OR G2 <> N2 OR G3 <> N3 OR G4 <> N4 OR G5 <> N5
			OR G6 <> N6 OR G7 <> N7 OR G8 <> N8 OR G9 <> N9 OR G10 <> N10 OR G11 <> N11 OR G12 <> N12 OR G13 <> N13
			OR G14 <> N14 OR G15 <> N15 OR G16 <> N16 OR G17 <> N17 OR G18 <> N18 OR GN2 <> NN2 OR GN1 <> NN1))
BEGIN
PRINT 'Verify Next Street Usage - Compare/Check Next Year Fields'
PRINT ''
END
ELSE
BEGIN
PRINT 'No STR Differences'
PRINT ''
END


PRINT '------------------------------------------------------------------------------------------------------'
PRINT 'Count of gradebook backups per gradebook.  Where possible, only copy the most recent gradebook backup.'
PRINT '------------------------------------------------------------------------------------------------------'
PRINT ''
IF EXISTS (SELECT 1 FROM GBB WHERE DEL = 0 AND ((SELECT COUNT(*) FROM GBB WHERE YR = YEAR(GETDATE()) - 1) > (SELECT COUNT(*) FROM (SELECT DISTINCT SC, YR, GN, TN, NM, TM, PD FROM GBB WHERE YR = YEAR(GETDATE()) - 1) C)))
SELECT 'More than one GBB record per gradebook exists' 'GBB'
ELSE 
SELECT 'No more than one GBB record per gradebook exists' 'GBB'


PRINT '-----------------------------------------------------------------------------------------------------------------'
PRINT 'Table record counts for tables that can be skipped (not copied to the new year). If unsure, copy to the new year!'
PRINT '-----------------------------------------------------------------------------------------------------------------'
PRINT ''
SELECT [COUNT], [TABLE] FROM (
	SELECT COUNT(*) 'COUNT', 'TFL' 'TABLE', 1 'RNK' FROM TFL WHERE DEL = 0 UNION
	SELECT COUNT(*), 'SFL', 2 FROM SFL WHERE DEL = 0 UNION
	SELECT COUNT(*), 'MED', 3 FROM MED WHERE DEL = 0 UNION
	SELECT COUNT(*), 'ATN', 4 FROM ATN WHERE DEL = 0 UNION
	SELECT COUNT(*), 'FRE (', 5 FROM FRE WHERE DEL = 0) R
ORDER BY R.RNK

PRINT '-----------------------------------------------------------------------------------------------------'
PRINT 'Count of paid and unpaid fees to help determine whether or not to copy all fees, or just outstanding.'
PRINT '-----------------------------------------------------------------------------------------------------'
PRINT ''
SELECT (SELECT COUNT(*) FROM FEE WHERE AM = PD OR AM = 0) 'Paid Fees', (SELECT COUNT(*) FROM FEE WHERE PD < AM) 'Outstanding Fees'


PRINT '-----------------------------------------------------------------------------------------------------------'
PRINT 'AUT Record counts by code and dates. Can have an impact on any Data Confirmation currently open to parents.'
PRINT '-----------------------------------------------------------------------------------------------------------'
PRINT ''
IF EXISTS (SELECT * FROM AUT WHERE DEL = 0)
SELECT CD, DT, SDT, EED, COUNT(*) 'Authorizations' --SUBSTRING(CO, 1, 100) AS CO
FROM (SELECT CD, CO, ISNULL(YEAR(DT), '') DT, ISNULL(YEAR(SDT), '') SDT, CASE WHEN EED IS NOT NULL THEN 'End' ELSE 'No End' END EED FROM AUT WHERE DEL = 0 ) A
GROUP BY CD, DT, SDT, EED
ELSE PRINT 'NO AUT Records'
PRINT ''

PRINT '---------------------------'
PRINT 'Identify "Inactive" school.'
PRINT '---------------------------'
PRINT ''
IF EXISTS (SELECT * FROM LOC WHERE NM LIKE '%INACTIVE%' OR NM LIKE '%ARCHIVE%')
SELECT CD, NM FROM LOC WHERE NM LIKE '%INACTIVE%' OR NM LIKE '%ARCHIVE%'
ELSE SELECT CD, NM, LO, HI FROM LOC

PRINT '--------------------------------------------------------------------------------------------------------------'
PRINT 'Identify Rollover Status Tag.  If a status tag does not exist, create one using "Update Code Table" in Aeries.'
PRINT '--------------------------------------------------------------------------------------------------------------'
PRINT ''
IF EXISTS (SELECT CD AS 'Rollover Code' FROM COD WHERE TC = 'STU' AND FC = 'TG' AND (DE LIKE '%ROLLOVER%' OR DE LIKE '%DO NOT USE%'))
SELECT CD AS 'Rollover Code' FROM COD WHERE TC = 'STU' AND FC = 'TG' AND (DE LIKE '%ROLLOVER%' OR DE LIKE '%DO NOT USE%')
ELSE SELECT TC, FC, CD, CONVERT(VARCHAR(40), DE) AS 'DE' FROM COD WHERE TC = 'STU' AND FC = 'TG'


PRINT '---------------------------------------------------------------------------------------------------------'
PRINT 'Count of Students by school and status type.  Determine "regular" and "special" rollover options schools.'
PRINT '---------------------------------------------------------------------------------------------------------'
PRINT ''
SELECT LOC.CD, LOC.NM, LOC.LO, LOC.HI, LOC.TG,
	(SELECT COUNT(*) FROM STU WHERE DEL = 0 AND TG = '' AND SC = LOC.CD) 'Active STU',
	(SELECT COUNT(*) FROM STU WHERE DEL = 0 AND TG = '*' AND SC = LOC.CD) 'Pre-Enrolled STU',
	(SELECT COUNT(*) FROM STU WHERE DEL = 0 AND TG NOT IN ('', '*') AND SC = LOC.CD) 'Inactive STU'
FROM LOC WHERE DEL = 0 AND CD <> 0
PRINT ''

PRINT '-------------------------------------------------------------------------------------------------------------'
PRINT 'Count of MST and SMS records by school. Determine whether or not to rollover Master Schedule to the new year.'
PRINT '-------------------------------------------------------------------------------------------------------------'
PRINT ''
IF EXISTS (SELECT * FROM MST WHERE DEL = 0)
SELECT SC, COUNT(*) 'MST COUNT' FROM MST GROUP BY SC
ELSE PRINT 'No MST'
IF EXISTS (SELECT * FROM SMS WHERE DEL =0)
SELECT SC, COUNT(*) 'SMS COUNT' FROM SMS GROUP BY SC
ELSE PRINT 'No SMS'
PRINT ''


PRINT '-------------------------------------------------------------------------------------------'
PRINT 'Count of legacy textbook records.  If still in use, should be converted to District Assets.'
PRINT '-------------------------------------------------------------------------------------------'
PRINT ''
IF EXISTS (SELECT TOP(1) SN FROM TBC WHERE DEL = 0 AND SN <> 0)
SELECT 'Total TBC', COUNT(*) 'Count' FROM TBC
UNION
SELECT 'Total Assigned', COUNT(*) FROM TBC WHERE DEL = 0 AND SN <> 0
ELSE PRINT 'No Textbooks or None Assigned'
PRINT ''

PRINT '--------------------------------------------------------------------------------------------------------------------------------------'
PRINT 'Schools that have a "Next Year School Start Date" populated in LOC. Determine whether or not to use the corresponding rollover option.'
PRINT '--------------------------------------------------------------------------------------------------------------------------------------'
PRINT ''
IF EXISTS (SELECT CD, NM, NSD FROM LOC WHERE DEL = 0 AND NSD IS NOT NULL)
SELECT CD, NM, NSD FROM LOC WHERE DEL = 0 AND NSD IS NOT NULL
ELSE PRINT 'Next School Year Start Date Not Populated in LOC'
PRINT ''

PRINT '------------------------------------------------------------------------------------------------------------------------------------------------------------'
PRINT 'Grade levels above 13 used in Aeries.  If using the "Do Not Bump" rollover option, the "Next Grade" field will be ignored for the corresponding grade level.'
PRINT '------------------------------------------------------------------------------------------------------------------------------------------------------------'
PRINT ''
IF EXISTS (SELECT TOP(1) STU.GR FROM STU WHERE GR > 13) 
SELECT GR, COUNT(*) FROM STU WHERE DEL = 0 AND TG IN ('', '*') AND GR > 13 GROUP BY GR
ELSE PRINT 'No Grades > 13'
PRINT ''

PRINT '------------------------------------------------------------------------------------------------------'
PRINT 'Student Age Distribution. Determine the max age to stop rolling over student records (Default Age 25).'
PRINT '------------------------------------------------------------------------------------------------------'
PRINT ''
SELECT DATEDIFF(YY, STU.BD, GETDATE()) 'Age', COUNT(*) 'Count' FROM STU
WHERE DEL = 0 AND BD IS NOT NULL
GROUP BY DATEDIFF(YY, STU.BD, GETDATE())
ORDER BY DATEDIFF(YY, STU.BD, GETDATE())
PRINT ''

PRINT '----------------------------------------------------------------------------------------------------------------'
PRINT 'Count of Locker records (LKR) assigned to students. Determine whether they should be unassigned during rollover.'
PRINT '----------------------------------------------------------------------------------------------------------------'
PRINT ''
IF EXISTS (SELECT TOP(1) LK FROM LKR WHERE DEL = 0 AND SN <> 0)
SELECT 'Total Records', COUNT(*) FROM LKR WHERE DEL = 0
UNION
SELECT 'LKR Assigned', COUNT(*) FROM LKR WHERE DEL = 0 AND SN <> 0
ELSE PRINT 'No Lockers or None Assigned'
PRINT ''

PRINT '-------------------------------------------------------------------------------------------------------------'
PRINT 'Secondary Schools that have one or more student records with the counselor field populated (STU.CU).'
PRINT '-------------------------------------------------------------------------------------------------------------'
PRINT ''
IF EXISTS (SELECT LOC.CD FROM LOC INNER JOIN STU ON LOC.CD = STU.SC WHERE LOC.E = 2 AND STU.CU <> 0)
SELECT DISTINCT LOC.CD 'Secondary Counelors Populated' FROM LOC INNER JOIN STU ON LOC.CD = STU.SC WHERE LOC.E = 2 AND STU.CU <> 0
ELSE PRINT 'No Secondary Schools or No Counselors Populated'
PRINT ''

PRINT '----------------------------------------------------------------------------------------------------------------------------------------------------------'
PRINT 'Elementary type schools that have the "Next Teacher" field populated with record count. Determine if the option will be used for the corresponding school.'
PRINT '----------------------------------------------------------------------------------------------------------------------------------------------------------'
PRINT ''
IF EXISTS (SELECT LOC.CD FROM LOC INNER JOIN STU ON LOC.CD =STU.SC WHERE LOC.E <> 2 AND STU.NT <> 0 AND STU.NT <> STU.CU)
SELECT LOC.CD 'Using Next Teacher', Count(STU.SN) 'Count' FROM LOC INNER JOIN STU ON LOC.CD = STU.SC WHERE LOC.E <> 2 AND STU.NT <> 0 AND STU.NT <> STU.CU
GROUP BY LOC.CD
ORDER BY LOC.CD
ELSE PRINT 'No Next Teacher'
PRINT ''

PRINT '-----------------------------------------------------------'
PRINT 'Record count where STU.IT <> STU.NIT or STU.ITD <> STU.NTD.'
PRINT '-----------------------------------------------------------'
PRINT ''
IF EXISTS (SELECT TOP(1) IT, ITD, NIT, NTD FROM STU WHERE (IT <> '' OR ITD <> '' OR NIT <> '' OR NTD <> '' AND TG IN ('', '*') AND DEL = 0))
BEGIN
SELECT COUNT(*) 'Non-Matching IT/ITD - NIT/NTD' FROM STU WHERE (IT <> '' OR ITD <> '' OR NIT <> '' OR NTD <> '' AND TG IN ('', '*') AND DEL = 0) AND (IT <> NIT OR ITD <> NTD)
PRINT 'Run this for details:   
	SELECT ID ''Perm ID'', SC ''School Code'', CASE WHEN IT = '''' THEN ''(Blank)'' ELSE IT END ''Current Code'', 
	CASE WHEN ITD = '''' THEN ''(Blank)'' ELSE ITD END ''Current District'', CASE WHEN NIT = '''' THEN ''(Blank)'' ELSE NIT END ''Next Code'', 
	CASE WHEN NTD = '''' THEN ''(Blank)'' ELSE NTD END ''Next District'' FROM STU 
	WHERE (IT <> '''' OR ITD <> '''' OR NIT <> '''' OR NTD <> '''' AND TG IN ('''', ''*'') AND DEL = 0) AND (IT <> NIT OR ITD <> NTD)'
END
ELSE PRINT 'No Inter/Intra or No Differences'
PRINT ''

PRINT '-------------------------------------'
PRINT 'Record count where STU.TR <> STU.NTR.'
PRINT '-------------------------------------'
PRINT ''
IF EXISTS (SELECT TOP(1) SC FROM STU WHERE TG IN ('', '*') AND DEL = 0 AND NTR <> '')
SELECT STU.SC, COUNT(STU.SN) FROM STU WHERE TG IN ('', '*') AND DEL = 0 AND NTR <> '' AND TR <> NTR GROUP BY STU.SC
ELSE PRINT 'No Tracks or No Differences'
PRINT ''

PRINT '-------------------------------------'
PRINT 'Record count where STU.RS <> STU.NRS.'
PRINT '-------------------------------------'
PRINT ''
IF EXISTS (SELECT TOP(1) SC FROM STU WHERE TG IN ('', '*') AND DEL = 0 AND RS <> NRS AND NRS <> 0)
SELECT STU.SC, COUNT(STU.SN) 'Next Residence School Populated' FROM STU WHERE TG IN ('', '*') AND DEL = 0 AND NRS <> 0 AND RS <> NRS GROUP BY STU.SC
ELSE PRINT 'No Next Residence School or No Differences'
PRINT ''

PRINT '-------------------------------------'
PRINT 'Record count where STU.SP <> STU.NSP.'
PRINT '-------------------------------------'
PRINT ''
IF EXISTS (SELECT TOP(1) SC FROM STU WHERE TG IN ('', '*') AND DEL = 0 AND SP <> NSP)
SELECT STU.SC, COUNT(STU.SN) 'Next Program Populated' 
	FROM STU WHERE TG IN ('', '*') AND DEL = 0 AND SP <> NSP GROUP BY STU.SC
ELSE PRINT 'No Next Program Differences'
PRINT ''

PRINT '--------------------------------------------------------------'
PRINT 'Record counts where STU.AP1 <> STU.NP1 and STU.AP2 <> STU.NP2.'
PRINT '--------------------------------------------------------------'
PRINT ''
IF EXISTS (SELECT TOP(1) SC FROM STU WHERE TG IN ('', '*') AND DEL = 0 AND AP1 <> NP1)
SELECT DISTINCT STU.SC 'NP1 Populated' FROM STU WHERE TG IN ('', '*') AND DEL = 0 AND AP1 <> NP1 GROUP BY STU.SC
ELSE PRINT 'No NP1 Differences'
PRINT ''
IF EXISTS (SELECT TOP(1) SC FROM STU WHERE TG IN ('', '*') AND DEL = 0 AND AP2 <> NP2)
SELECT STU.SC, COUNT(STU.SN) 'NP2 Populated' FROM STU WHERE TG IN ('', '*') AND DEL = 0 AND AP2 <> NP2 GROUP BY STU.SC
ELSE PRINT 'No NP2 Differences'
PRINT ''

PRINT '-------------------------------------'
PRINT 'Record count where STU.GC <> STU.NGC.'
PRINT '-------------------------------------'
PRINT ''
IF EXISTS (SELECT TOP(1) SC FROM STU WHERE TG IN ('', '*') AND DEL = 0 AND GC <> NGC AND NGC NOT IN ('', '0'))
SELECT STU.SC, COUNT(STU.SN) 'Next Grid Code Populated' FROM STU WHERE TG IN ('', '*') AND DEL = 0 AND GC <> NGC AND NGC NOT IN ('', '0') GROUP BY STU.SC
ELSE PRINT 'No Next Grid Code or No Differences'
PRINT ''

PRINT '-------------------------------------------------------'
PRINT 'Record count by school where "Next School" is populated'
PRINT '-------------------------------------------------------'
PRINT ''
SELECT L.CD 'School', 
	ISNULL((SELECT COUNT(*) FROM STU WHERE SC = L.CD AND TG IN ('', '*') AND DEL = 0), 0) 'Active/PreEnrolled STU',
	ISNULL((SELECT COUNT(*) FROM STU WHERE SC = L.CD AND TG IN ('', '*') AND DEL = 0 AND NS <> 0 AND NS IN (SELECT CD FROM LOC WHERE TG = '' AND DEL = 0 AND CD <> 0) GROUP BY SC), 0) 'Next School Populated'
FROM LOC L
WHERE CD IN (SELECT DISTINCT SC FROM STU WHERE DEL = 0 AND TG IN ('', '*'))
PRINT ''

PRINT '---------------------------------------------------------'
PRINT 'Record count of students pre-enrolled more than one time.'
PRINT '---------------------------------------------------------'
PRINT ''
IF EXISTS (SELECT TOP(1) SC, SN, ID, LN, FN FROM STU WHERE TG = '*' GROUP BY SC, SN, ID, LN, FN HAVING COUNT(*) > 1)
SELECT SC, SN, ID, LN, FN FROM STU WHERE TG = '*' GROUP BY SC, SN, ID, LN, FN HAVING COUNT(*) > 1
ELSE PRINT 'No Pre-enrolled or Only one pre-enrolled record per student'
PRINT ''

PRINT '---------------------------------------------------------'
PRINT 'Are the Sync/Lock District Rules enabled?'
PRINT '---------------------------------------------------------'
PRINT ''
IF EXISTS (SELECT R23 'Sync', R25 'Lock' FROM LOC WHERE CD = 0 AND (R23 = 1 OR R25 = 1))
PRINT 'Sync/Lock Enabled - Review'
ELSE PRINT 'Sync/Lock Not Enabled'
PRINT ''

PRINT '----------------------------------------------------------------------------'
PRINT 'SQL Logins and the last time they accessed the database using Aeries Client.'
PRINT '----------------------------------------------------------------------------'
PRINT ''
select CONVERT(VARCHAR(30), s.[Login Name]) AS 'Login Name', CASE WHEN s.hasaccess = 1 THEN 'Yes' ELSE 'No' END AS 'SQL ACCESS?', 
	CASE WHEN S.denylogin = 1 THEN 'No' ELSE 'Yes' END AS 'CAN LOGIN?', l.dt 'Last Login'
from 
	(select usr, max(dt) as dt from LOG group by usr) l 
	right join 
	(select CONVERT(VARCHAR(30), SL.name) AS 'Login Name', sl.hasaccess 'hasaccess', sl.denylogin 'denylogin'
		from sys.syslogins AS SL inner join sys.database_principals
			on SL.name = sys.database_principals.name
		where SL.name not like '%\%' and SL.name not like '%#%' and SL.name not like 'ABI%' and SL.name not like 'Aeries%') s
	on l.usr = s.[Login Name]
ORDER BY S.[Login Name]

PRINT '------------------------------------------'
PRINT 'Is Online Enrollment configured in Aeries?'
PRINT '------------------------------------------'
PRINT ''
IF EXISTS (SELECT * FROM DPT WHERE DEL = 0 AND CD <> '' AND NM IN ('AIR_SecurityCode', 'AIR_URL'))
PRINT 'Using Online Enrollment'
ELSE PRINT 'Not Using Online Enrollment'
PRINT ''

PRINT '------------------------------------------------------'
PRINT 'Is Aeries Communications configured for use in Aeries?'
PRINT '------------------------------------------------------'
PRINT ''
IF EXISTS (SELECT * FROM DPT WHERE DEL = 0 AND (NM = 'LOOP' OR NM = 'ParentSquare') AND CD = 'True')
PRINT 'Using Aeries Communications'
ELSE PRINT 'Not Using Aeries Communications'
PRINT ''

PRINT '----------------------------'
PRINT 'Is EM2 configured in Aeries?'
PRINT '----------------------------'
PRINT ''
IF EXISTS (SELECT * FROM DPT WHERE NM = 'MobileApp_EM2_Enabled' AND CD = 'True')
PRINT 'EM2 Configured'
ELSE PRINT 'EM2 Not Configured'
PRINT ''

PRINT '-----------------------------------'
PRINT 'Custom Tables/Views defined in CTD.'
PRINT '-----------------------------------'
PRINT ''
IF EXISTS (SELECT TOP(1) * FROM CTD WHERE DEL = 0)
SELECT TC, CONVERT(VARCHAR(50), NM), VW FROM CTD
ELSE PRINT 'No Custom Tables/Views Defined in CTD'
PRINT ''

PRINT '---------------------------------------'
PRINT 'Custom Tables/Views NOT defined in CTD.'
PRINT '---------------------------------------'
PRINT ''
SELECT CONVERT(VARCHAR(50), TABLE_NAME) 'Tables not defined in CTD' FROM INFORMATION_SCHEMA.TABLES
WHERE LEN(TABLE_NAME) > 4 AND TABLE_TYPE = 'BASE TABLE'
	AND TABLE_NAME NOT LIKE 'RT_%'
	AND TABLE_NAME NOT IN ('USYSGCOD', 'DistrictCache')
	AND TABLE_NAME NOT IN (SELECT TC FROM CTD)