<html>
<!-- start right frame -->
<head>
<title>Contacts Export</title>
<link href="/images/css/screen.css" rel="stylesheet" media="screen">
<link href="/images/css/print.css" rel="stylesheet" media="print">
</head>
<body>
~[wc:admin_header_frame_css]
<!-- breadcrumb start -->
<a href="/admin/home.html" target="_top">~[text:psx.html.admin_students.unlimitedcontacts.start_page]</a> >
<a href="home.html?selectstudent=nosearch" target="_top">~[text:psx.html.admin_students.unlimitedcontacts.student_selection]</a> >
Contacts Export
<!-- breadcrumb end -->
~[wc:admin_navigation_frame_css]
<!-- start of title and student content -->
<table width="100%">
<tr>
<td background="/images/transparent.gif">
<!-- title -->
<h1>Contact Export</h1>
<!-- title -->
~[if.isstudent]
<!-- don't allow students to view the directory. -->
<!-- start of content and bounding box -->
<table>
<tr>
<td width="23" background="/images/bond_box_left_edge.gif"><img src="/images/spacer.gif" width="23" height="1"></td>
<td>
<table>
<tr>
<td>Your account does not allow access to this page.</td>
</tr>
</table>
</td>
<td width="25" background="/images/bond_box_right_edge.gif"><img src="/images/spacer.gif" width="25" height="1"></td>
</tr>
</table>
<br>
<!-- end of content of bounding box -->
[else]
<form name="searchform" action="ssis_contacts_export.html" method="GET" style="float:left;width:500px">
<table>
<tr>
<td style="background-color:white;">Filter by Grade Level: </td>
<td style="background-color:white;">
<a href="ssis_contact_export.html?grade_level=-2">EC3</a>
<a href="ssis_contact_export.html?grade_level=-1">EC4</a>
<a href="ssis_contact_export.html?grade_level=0">K</a>
<a href="ssis_contact_export.html?grade_level=1">1</a>
<a href="ssis_contact_export.html?grade_level=2">2</a>
<a href="ssis_contact_export.html?grade_level=3">3</a>
<a href="ssis_contact_export.html?grade_level=4">4</a>
<a href="ssis_contact_export.html?grade_level=5">5</a>
<a href="ssis_contact_export.html?grade_level=6">6</a>
<a href="ssis_contact_export.html?grade_level=7">7</a>
<a href="ssis_contact_export.html?grade_level=8">8</a>
<a href="ssis_contact_export.html?grade_level=9">9</a>
<a href="ssis_contact_export.html?grade_level=10">10</a>
<a href="ssis_contact_export.html?grade_level=11">11</a>
<a href="ssis_contact_export.html?grade_level=12">12</a>
<a href="ssis_contact_export.html?grade_level=ALL">ALL</a>
</td>
</tr>
</table>
</form>
<input type="button" value="Export as Excel" onclick="saveAsExcel('contactExport', 'contactExport.xls')"/>
<table id="contactExport" class="grid" style="width:100%">
<tr>
<th>ID</th>
<th>Student Full Name</th>
<th>Grade</th>
<th>Email</th>
<th>Gender</th>
<th>DOB</th>
<th>Address</th>
<th>Home Phone</th>
<th colspan=12>Emergency Contact Information</th>
</tr>
~[tlist_sql;
WITH StudentsParents AS (
SELECT DISTINCT
DECODE(co.ContactRelationship,'Mother','M','Father','F','Step-Mother','M','Step-Father','F','X') ContactRelationship,
co.StudentDCID,
co.ContactLastFirst,
pp.PhoneNumberasentered,
e.EmailAddress
FROM
PSSIS_STU_Contact_Act_Emerg co
LEFT JOIN PSSIS_Person_Phone pp on pp.PersonID = co.ContactPersonID AND pp.isPreferred = 1
LEFT JOIN PSSIS_Person_Email e on e.PersonID = co.ContactPersonID AND e.isPrimaryEmailAddress = 1
WHERE co.FilteredPriorityOrder in (1,2,3)
)
SELECT DISTINCT
s.Student_Number, s.lastfirst,
DECODE(s.grade_level,0,'K',-2,'EC3',-1,'EC4',TRIM(TO_CHAR(s.grade_level))),
u.Email,
'<td>' || s.Gender || '</td>' AS Gender,
'<td>' || TO_CHAR(s.DOB, 'MM/DD/YYYY') || '</td>' AS DOB,
'<td>' || s.Street || ',' ||
u.SSIS_Student_Ward || ', ' || u.SSIS_Student_District || ',' ||
s.City || '</td>' AS Street,
'<td>' || s.home_phone || '</td>' as Homephone,
DECODE(m.ContactLastFirst ,'','',
'<td class="right bold">Mother</td><td>' || m.ContactLastFirst || '</td>' ||
/* CHR(58) is a colon. Actual colons break TLIST_SQL. */
'<td>' || m.PhoneNumberasentered || '</td>' ||
'<td>' || m.EmailAddress || '</td>') AS Mother,
DECODE(f.ContactLastFirst ,'','',
'<td class="right bold">Father</td><td>' || f.ContactLastFirst || '</td>' ||
/* CHR(58) is a colon. Actual colons break TLIST_SQL. */
'<td>' || f.PhoneNumberasentered || '</td>' ||
'<td>' || f.EmailAddress || '</td>') AS Father,
DECODE(x.ContactLastFirst ,'','',
'<td class="right bold">Emergency</td><td>' || x.ContactLastFirst || '</td>' ||
'<td>' || x.PhoneNumberasentered || '</td>' ||
'<td>' || x.EmailAddress || '</td>') AS Emergency
FROM
Students s
INNER JOIN CC ON s.ID = cc.StudentID
LEFT JOIN StudentsParents m ON s.DCID = m.StudentDCID AND m.ContactRelationship = 'M'
LEFT JOIN StudentsParents f ON s.DCID = f.StudentDCID AND f.ContactRelationship = 'F'
LEFT JOIN StudentsParents x ON s.DCID = x.StudentDCID AND x.ContactRelationship = 'X'
INNER JOIN U_StudentsUserFields u ON s.DCID = u.StudentsDCID
WHERE
s.Enroll_Status = 0
AND cc.TERMID >= (~(curyearid) * 100) and cc.TERMID < (~(curyearid) * 100 + 100)
~[if#1.~(gpv.grade_level)=]
/* NO Grade level provided. */
AND S.grade_level LIKE 'zzz'
[else#1]
~[if#2.~(gpv.grade_level)=ALL]
AND S.grade_level LIKE '%'
[else#2]
/* Grade Level provided. . */
AND s.grade_level = ('~[gpv:grade_level]')
[/if#2]
[/if#1]
ORDER BY
LOWER(s.LastFirst); ]
<tr valign="top">
<td>~(s.Student_Number)</td>
<td>~(s.lastfirst)</td>
<td>~(s.grade_level)</td>
<td>~(s.email)</td>
~(GenderRow)
~(DOBRow)
~(StreetRow)
~(HomePhoneRow)
~(MotherRow)
~(FatherRow)
~(EmergencyRow)
</tr>
[/tlist_sql]
</table>
<!-- end student content -->
[/if.isstudent]
</td>
</tr>
</table>
<!-- end of title and student content -->
<p>NOTE: In order for the contact's information to show up, either the preferred phone number and primary email must be checked off.</p>
<!-- JN Export using TableExport.js -->
<!-- <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.js"></script>
<script type="text/javascript" src="/admin/javascript/xlsx.core.js"></script>
<script type="text/javascript" src="/admin/javascript/Blob.min.js"></script>
<script type="text/javascript" src="/admin/javascript/FileSaver.min.js"></script>
<script type="text/javascript" src="/admin/javascript/tableexport.min.js"></script>-->
<script type="text/javascript" src="/admin/javascript/saveAsExcel.js"></script>
<!-- <script type="text/javascript">
$(document).ready(function() {
$('#contactExport').tableExport({
formats: ["csv"],
bootstrap: true,
});
});
</script>
-->
</body>
</html>
