<!DOCTYPE html>
<html>
<!-- start right frame -->
<head>
<title>Contacts Export SSIS ID</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 with SSIS ID</h1>
<!-- title -->
<span style="float:right">CTRL+F to find </span><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>Enroll Status</th>
<th>Grade</th>
<th colspan=9>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,
co.ContactPersonID,
xp.SSIS_PERSON_ID
FROM
PSSIS_STU_Contact_Act_Emerg co
LEFT JOIN U_DEF_EXT_PERSON xp ON xp.PERSONID = co.CONTACTPERSONID
)
SELECT DISTINCT
s.Student_Number, s.lastfirst, s.Enroll_Status,
DECODE(s.grade_level,0,'K',-2,'EC3',-1,'EC4',TRIM(TO_CHAR(s.grade_level))),
DECODE(m.ContactLastFirst ,'','',
/* CHR(58) is a colon. Actual colons break TLIST_SQL. */
'<td class="right bold">Mother</td><td><a href="../contacts/edit.html#?contactid=' || m.ContactPersonID || '">' || m.ContactLastFirst || '</a></td>' ||
'<td>' || m.ssis_person_id || '</td>') AS Mother,
DECODE(f.ContactLastFirst ,'','',
'<td class="right bold">Father</td><td><a href="../contacts/edit.html#?contactid=' || f.ContactPersonID || '">' || f.ContactLastFirst || '</a></td>' ||
'<td>' || f.ssis_person_id || '</td>') AS Father,
DECODE(x.ContactLastFirst ,'','',
'<td class="right bold">Other</td><td><a href="../contacts/edit.html#?contactid=' || x.ContactPersonID || '">' || x.ContactLastFirst || '</a></td>' ||
'<td>' || x.ssis_person_id || '</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) */
ORDER BY
LOWER(s.LastFirst); ]
<tr valign="top" >
<td>~(s.Student_Number)</td>
<td>~(s.lastfirst)</td>
<td style="color: ~(s.Enroll_Status;t;if.test=0;then=gray;else=orange;) ">
~(s.Enroll_Status) </td>
<td>~(s.grade_level)</td>
~(MotherRow)
~(FatherRow)
~(EmergencyRow)
</tr>
[/tlist_sql]
</table>
<!-- end student content -->
<script type="text/javascript" src="/admin/javascript/saveAsExcel.js"></script>
</body>
</html>
