Creative and Strange

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

PS 12+ Contact Export

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

Thursday, March 21, 2019

Lenovo Flex 15 Wifi Interface Disappearance in Linux Mint

This happens around a kernel update 4.15.0-43-generic.

inxi -Fxz

System:

Host: jnguyen-Lenovo-Ideapad-Flex-15 Kernel: 4.15.0-43-generic x86_64

bits: 64 compiler: gcc v: 7.3.0 Desktop: Cinnamon 4.0.9

Distro: Linux Mint 19.1 Tessa base: Ubuntu 18.04 bionic

Machine:

Type: Laptop System: LENOVO product: 20309 v: Lenovo Ideapad Flex 15

serial: <filter>

Mobo: LENOVO model: Strawberry 5D v: 31900003Std serial: <filter>

UEFI [Legacy]: LENOVO v: 8ACN08WW date: 08/22/2013

Battery:

ID-1: BAT1 charge: 35.4 Wh condition: 35.4/43.4 Wh (82%)

model: SANYO L12S4F01 status: Full

CPU:

Topology: Dual Core model: Intel Core i7-4500U bits: 64 type: MT MCP

arch: Haswell rev: 1 L2 cache: 4096 KiB

flags: lm nx pae sse sse2 sse3 sse4_1 sse4_2 ssse3 vmx bogomips: 19156

Speed: 1129 MHz min/max: 800/3000 MHz Core speeds (MHz): 1: 1764 2: 1711

3: 1786 4: 1738

Graphics:

Device-1: Intel Haswell-ULT Integrated Graphics vendor: Lenovo

driver: i915 v: kernel bus ID: 00:02.0

Display: x11 server: X.Org 1.19.6 driver: modesetting unloaded: fbdev,vesa

resolution: 1366x768~60Hz

OpenGL: renderer: Mesa DRI Intel Haswell Mobile v: 4.5 Mesa 18.2.2

direct render: Yes

Audio:

Device-1: Intel Haswell-ULT HD Audio vendor: Lenovo driver: snd_hda_intel

v: kernel bus ID: 00:03.0

Device-2: Intel 8 Series HD Audio vendor: Lenovo driver: snd_hda_intel

v: kernel bus ID: 00:1b.0

Sound Server: ALSA v: k4.15.0-43-generic

Network:

Device-1: Realtek RTL810xE PCI Express Fast Ethernet vendor: Lenovo

driver: r8169 v: 2.3LK-NAPI port: 3000 bus ID: 01:00.0

IF: enp1s0 state: down mac: <filter>

Device-2: Intel Wireless 7260 driver: iwlwifi v: kernel port: 3000

bus ID: 02:00.0

IF: wlp2s0 state: up mac: <filter>

Drives:

Local Storage: total: 465.76 GiB used: 420.56 GiB (90.3%)

ID-1: /dev/sda vendor: Seagate model: ST500LM000-1EJ162 size: 465.76 GiB

Partition:

ID-1: / size: 450.23 GiB used: 420.56 GiB (93.4%) fs: ext4 dev: /dev/sda2

ID-2: swap-1 size: 7.73 GiB used: 0 KiB (0.0%) fs: swap dev: /dev/sda3

Sensors:

System Temperatures: cpu: 52.0 C mobo: N/A

Fan Speeds (RPM): N/A

Info:

Processes: 242 Uptime: 6m Memory: 7.52 GiB used: 1.17 GiB (15.5%)

Init: systemd runlevel: 5 Compilers: gcc: 7.3.0 Shell: bash v: 4.4.19

inxi: 3.0.27

rfkill list

0: ideapad_wlan: Wireless LAN

Soft blocked: no

Hard blocked: no

1: ideapad_bluetooth: Bluetooth

Soft blocked: yes

Hard blocked: no

2: hci0: Bluetooth

Soft blocked: yes

Hard blocked: no

3: phy0: Wireless LAN

Soft blocked: no

Hard blocked: no

Solution

echo "options iwlwifi swenc=Y ips=N" | sudo tee /etc/modprobe.d/iwlwifi.conf

sudo apt-get remove bcmwl-kernel-source && sudo apt-get install firmware-b43-installer

Reboot