Creative and Strange: April 2019

Tuesday, April 23, 2019

Sort by Color

Reference: https://www.dynatable.com/?perPage=50&queries%5Bsearch%5D=2012

// Our custom sort function
function rgbSort(a, b, attr, direction) {

  // Assuming we've created a separate function
  // to get the average RGB value from an image.
  // (see source for example above for getAverageRGB function)
  var aRgb = getAverageRGB(a.img),
      bRgb = getAverageRGB(b.img),
      aDec = ( aRgb.r << 16 ) + ( aRgb.g << 8 ) + aRgb.b,
      bDec = ( bRgb.r << 16 ) + ( bRgb.g << 8 ) + bRgb.b,
      comparison = aDec - bDec;

  return direction > 0 ? comparison : -comparison;
};

// Wait until images are loaded
$(window).load(function() {
  $('#sorting-function-example')

    // Add our custom sort function to dynatable
    .bind('dynatable:init', function(e, dynatable) {
      dynatable.sorts.functions["rgb"] = rgbSort;
    })

    // Initialize dynatable
    .dynatable({
      features: {
        paginate: false,
        search: false,
        recordCount: false
      },
      dataset: {
        // When we sort on the color column,
        // use our custom sort added above.
        sortTypes: {
          color: 'rgbSort'
        }
      },
      readers: {
        color: function(cell, record) {
          var $cell = $(cell);

          // Store the average RGB image color value
          // as a decimal in "dec" attribute.
          record['img'] = $cell.find('img').get(0);

          // Return the HTML of the cell to be stored
          // as the "color" attribute.
          return $cell.html();
        }
      }
    });
})

Monday, April 22, 2019

MS SQL Find Last Created Object

select
so.name, su.name, so.crdate
from
sysobjects so
join
sysusers su on so.uid = su.uid
order by
so.crdate;

Thursday, April 4, 2019

<!DOCTYPE html>
<html>

<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]


<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


~[wc:admin_navigation_frame_css]



<table width="100%">
<tr>
<td background="/images/transparent.gif">


<h1>Contact Export</h1>


~[if.isstudent]



<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>


[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>


[/if.isstudent]

</td>
</tr>
</table>


<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>




<script type="text/javascript" src="/admin/javascript/saveAsExcel.js"></script>



</body>
</html>

PS 12+ Contact Export with Employer

<!DOCTYPE html>
<html>

<head>
<title>Contacts Employer 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]


<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


~[wc:admin_navigation_frame_css]



<table width="100%">
<tr>
<td background="/images/transparent.gif">


<h1>Contacts Employer Export</h1>


<input type="button" value="Export as Excel" onclick="saveAsExcel('contactExport', 'contactExport.xls')"/>

~[if.isstudent]



<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>


[else]

<table id="contactExport" class="grid" style="width:100%">
<tr>
<th>Student Full Name</th>
<th colspan=10>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,
co.Employer
FROM
PSSIS_STU_Contact_Act co
LEFT JOIN PSSIS_Person_Phone pp on pp.PersonID = co.ContactPersonDCID AND pp.isPreferred = 1
LEFT JOIN PSSIS_Person_Email e on e.PersonID = co.ContactPersonDCID AND e.isPrimaryEmailAddress = 1
WHERE co.Employer is not null
)
SELECT DISTINCT
s.lastfirst,
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>' ||
'<td>' || m.Employer || '</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>' ||
'<td>' || f.Employer || '</td>') AS Father
FROM
Students s
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'
WHERE
s.enroll_status in (0,-1)
ORDER BY
LOWER(s.lastfirst) ; ]
<tr valign="top">
<td>~(s.lastfirst)</td>
~(MotherRow)
~(FatherRow)
</tr>

[/tlist_sql]

</table>


[/if.isstudent]

</td>
</tr>
</table>


<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>




<script type="text/javascript" src="/admin/javascript/saveAsExcel.js"></script>

</body>
</html>

PS 12+ Contact Export of Custom Field

Here is one use report to identify the school specific ID and color code based on the enroll status. This has the ability to also export out to an Excel. Notice the javascript saveAsExcel.js in order for the export feature to work.

<!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> &gt;

<a href="home.html?selectstudent=nosearch" target="_top">~[text:psx.html.admin_students.unlimitedcontacts.student_selection]</a> &gt;

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>