Ladvien's Lab

Latest Posts

/> Lumi4 -- LumiCommunication

Lumi Communication

This namespace contains the serial device handling functions.  The goal is to have two abstract classes which define the interaction between the main device and the peripheral  These two classes will be responsible for searching, connecting, exchanging data, closing connections, and device failure handling.  To get going I'm going to take some advice from Mythical Man Month: "Show me your flowcharts and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won’t usually need your flowcharts; they’ll be obvious."  Well, I don't have tables, so I guess my charts will have to do.

/> lumi_communication_central.png

LumiCommunication is largely modeled after Apple's CoreBluetooth API .  It has abstractions representing both remote and local devices.  These abstractions are inherited in concrete classes for different device types.  Currently, the API is focusing on two device types, BluetoothLE and WiFi (ESP8266).  Though, if the abstraction is effective, it shouldn't be difficult to provide support for Bluetooth Classic and Wired Serial connections.

The CentralManager's main responsibilities are to monitor the PC's device status, initiate searches, connect to devices.

I'm adding a bit to the CoreBluetooth model; I'm writing this code to be hacker friendly as one of my biggest peeves with frameworks meant for interacting with embedded devices is they often don't allow for easy modification of the device's behavior.  For example, most SoC-centered device modules (HM-10, ESP8266, etc.) which control radio hardware have firmware allowing for the modification of the module's behavior.  Often, these are modified through AT Commands.  It struck me one day, why am I not writing code in such a manner?  For example, instead writing concrete code inside the classes which handle searching for BluetoothLE to to automatically connect to known devices, why not create an static object which defines these behaviors, then, when I want to change the behavior of my hardware, I simply pass in a new behavioral definition object. I've outlined this in my BehavioralBluetooth project (abaonded for the time being).

LumiCommunication classes will have an object which defines the behavior of the hardware.

/> lumi_communication.png
The PeripheralManager is responsible for representing the states and delivered data of the peripheral devices.  There are events associated with data received from the device, confirmation of sent data, device state changes.  Like the CentralManager the PeripheralManager will have a PeripheralBehavior object which will define its actions.  There are received and sent buffers to monitor succesful flow of data between the local and remote device.

/> Lumi4 - init()

Lumi4

This is the next iteration in a three year project.  Here are the current iterations:

  1. Vorpal Hoff -- an attempt at wireless uploading with a HM-11 and LPC1114 combination.  Written in C / C++ (Initialized May 22nd 2014).
  2. HM-1X Aid -- this project was meant to be a GUI on top of the HM-1X modules, allowing "easy" editing of the module's behavior.  It was my first venture into C#.  (It's sooo bad;although, the serial communication was asynchronous.) (Initialized Dec. 19th 2015)
  3. Lumi1 -- this the first successful TinySafeBoot uploader.  It was written in C# using the .NET WinForms.  Unfortunately, it was synchronous.  And I was finished with the USB-to-UART uploader before I realized there was no easy BLE support in WinForm's .NET. (Initialized  March 2nd 2016)
  4. Lumi2 --  this is where things start getting better.  It is the current version of the TSB wireless bootloader.  It works, is asynchronous, and has BLE support.  Unfortunately, the code turned into spaghetti.  This is largely due to my poor understanding of object-oriented design.  It has god-modules, a horrifically implemented SerialEvent response protocol, poor encapsulation, no polymorphism.  It's just a mess. (Initialized March 21st 2016)
  5. Lumi3 -- this project was stopped early.  It was an attempt to build a multiplatform uploader using Xamarin Forms.  It would have allowed iOS, Android, and Windows versions of the application. Unfortunately, it is a fairly complex project.  Theoretically, the uploader would work by allowing the user to select a HEX file from Dropbox, handshake with TinySafeBoot using BLE or WifI, then upload the HEX file.  And though this is theoretically possible, it would take learning two new APIs: Xamarin Forms and Dropbox.  And my focus is dilberate practice of language conventiosn and OOP, rather hacking through two new APIs.  Most likely, I'll come back to this project after Lumi4 (Jan 13th 2017)

Why? Seriously, dude

It is important to state the objective of the this three-year project has not been to produce a product which works, although, that's a close second.  The purpose of these repeated attempts is to improve as a developer.

Of course, I don't believe if I try enough and eat my Wheatie I'll grow into a great developer.  But with each iteration I'm focusing on developing a handful of new skills.  This learning strategy is from the book Peak by Anders Ericsson.  The continued and intentional practice is labeled by Ericsson as " deliberate practice ."  It's with this mindset I've approached this iterative coding project, hoping with each iteration the code improves, but more importantly, my skill as a developer improves.

Focus

Deliberate practice involves selecting particular deficits to focus on.  This is more effective, as the improvement is in a few intentional areas, rather than trying to practice every important nuance of a skill at once.  This seems easy to get behind; especially, when it comes to developer skills.  There are just too many to try and refine all at once.

Sadly, focused practice isn't something I was intentional about for the first few code bases in this series.  It wasn't until Lumi3 and Lumi4 did it bubble up as crucial in the process of developing my skills.

Targeted Areas in Lumi4

The areas I'm looking to practice in Lumi4

  • Project Management
  • Project journaling
  • Abstraction
  • Encapsulation
  • Granulization of objects (avoid God-objects)
  • Meaningful names
  • C# Conventions (naming, formatting, placement, etc.)
  • Factory design pattern
  • Observer design pattern
  • Error handling
  • Unit Testing

Some areas which I may take on if all goes well:

  • Integration testing
  • Documentation API

What's the Plan for Lumi4?

Lumi4 will have three basic components:

  1. Communication handling for BluetoothLE and WiFi (extendedable to Bluetooth Classic and USB-to-UART)
  2. Smart serial display (e.g., recognizes data outside of ASCII range and prints as a hex string)
  3. TinySafeBoot uploader

Learning Assests

Unit and Integration Testing

Testing fascinates me.  When I studied psychometrics there were a battery of tests as to whether and instrument worked as intended. In the psychology world these were the fundemental building blocks of effective research and practice.  Why did we use CPT ? Because it passes a variety of tests to demonstrate efficacy.  In the developer world tests still hold my fascination.  They consistently demonstrate a product is capable of completing the task for which it was designed.

Of course, I've struggled with adopting testing in projects.  A lot of this has to do with poor understanding of how to design a test to meet a purpose.  It wasn't until I was listening to a Coding Blocks (#54 -- Writing Amazing Unit Tests ) episode on writing unit tests did I get more comfortable with testing.  Specifically, when they discussed the differences between unit and integration testing.

When I first attempted to write tests for a project it was unit tests. Unfortunately, this project was the second iteration of my Lumi uploader and the tests resulted in a hot-mess.  This is because I was trying to test functions which relied on inputs from other systems.

For example,

    [TestMethod]
    public async Task<bool> shouldStartBLEWatcher()
    {
        // Arrange
        blueTestObject.init();
        await blueTestObject.startBLEWatcher(8);

        return true;
    }

This is as far as I made it writing a unit test on a method which was meant to test whether the StartBLEWatcher() method was working.  In unit testing there should be an assert on the output of the method, but StartBLEWatcher() returned discovered BLE devices (yes, I realize the method could be re-written better, thus the reason for this article).  This is where I got frustrated.  "How the hell am I suppossed to write unit tests on code which interacts with other hardware!?" I mean, I get it, unit testing is the bread-and-butter of professional programmers.  It helps building big projects which would otherwise collapse under size.  But how the hell do I write unit tests for code which relies on outside systems!?  Saldy, I found the answer too late: You don't.

In the Coding Blocks episodes there is a discussion on the difference between unit tests and integration tests.  A unit test is meant to test a small piece of code and it should rely on no other code.  An integration tests checks whether a piece of code works as intended. However, unlike unit tests, integration tests do rely on outside systems by their very definition.

When I heard this discussion I went to the first StackOverflow answer on the subject:

Question: "What is the difference between integration and unit tests?"

Answer (by Nathan Huges )

A unit test is a test written by the programmer to verify that a relatively small piece of code is doing what it is intended to do. They are narrow in scope, they should be easy to write and execute, and their effectiveness depends on what the programmer considers to be useful. The tests are intended for the use of the programmer, they are not directly useful to anybody else, though, if they do their job, testers and users downstream should benefit from seeing fewer bugs.

Part of being a unit test is the implication that things outside the code under test are mocked or stubbed out. Unit tests shouldn't have dependencies on outside systems. They test internal consistency as opposed to proving that they play nicely with some outside system.

An integration test is done to demonstrate that different pieces of the system work together. Integration tests cover whole applications, and they require much more effort to put together. They usually require resources like database instances and hardware to be allocated for them. The integration tests do a more convincing job of demonstrating the system works (especially to non-programmers) than a set of unit tests can, at least to the extent the integration test environment resembles production.

Actually "integration test" gets used for a wide variety of things, from full-on system tests against an environment made to resemble production to any test that uses a resource (like a database or queue) that isn't mocked out.

Well, that's it for a bit.

/> Lumi3 Debriefing Notes

C# Learning Journal: Refactoring Lumi

Lumi Uploader

I’ve been working on writing my own flash UART uploader since May 2014. Originally, I was trying to write an uploader in C using the GCC compiler. The idea was to upload a Intel HEX file compiled for the LPC1114 to the uC remotely, using a Bluetooth LE connection. Here’s a description of the custom circuit board designed for the project:

* Valdez Mutant Board

Unfortunately, the project was out of my league. After spending months writing C code there it was not usable. Of course, learned a lot about C in the process.

Well, after a couple of years I started on code to upload compiled Atmel ATMega and ATtiny programs using the same method outlined in the Valdez Mutant article. But this time, the uploader was written in C# on Windows. And it interfaced with the TinySafeBootloader on the Atmel uCs.

Strangely, I actually finished the project. The first code-base was written as a C# Forms application . This worked out great! I was actually able to use the System.Devices.Ports to access a CH340G or FTDI chip. The USB-to-UART then shook hands with the bootloader on either an ATMega328P, ATtiny84, or ATtiny85 (others should be supported, but these were the only tested due to the simplicity of the Arduino HAL).

/>

Here’s the code base:

* Lumi Uploader – Windows Forms Version

Of course, there is are a lot of problems with the code. Most center around inexperience writing object-oriented code.

Here are some of the problems I identified:

Mistakes
1\. [God objects](http://sourcemaking.com/antipatterns/the-blob)
2\. [C# Conventions not followed](https://msdn.microsoft.com/en-us/library/ff926074.aspx)
3\. Deprecation (Forms->Universal)
4\. [Synchronous IO](https://msdn.microsoft.com/en-us/library/windows/desktop/aa365683(v=vs.85).aspx)
5\. Poor support for BLE
6\. Poor naming schemes
7\. Improper use of delegate / events
8\. Poor use of object abstraction
9.

It was really the lack of BluetoothLE support which forced a change in directions. However, the elusive wireless upload to an AVR was just too close to abandon. Reluctantly, I created yet another code base. This time, it was derived from the Windows Universal App platform.

After a few months I had a working version. It was able to upload to ATtiny chips and ATMega chips over Bluetooth LE.

/> However, when I started trying to add ESP8266 support–well, things went to the Pooh. It seemed of all the problems listed above the only one resolved was the adding of Bluetooth LE support.  My skill was not increasing.

Also, there were two additional issues which arose:

  1. Handling advertisement and connection for Bluetooth.
  2. There was a rather nasty bug around writing to a connected device.

The first issue was a nightmare. I was able to work around it–but, it was horrifically hackish. In short, there are two namespaces which must be used to achieve in app BluetoothLE search and connection, Windows.Devices.Bluetooth and Windows.Devices.Bluetooth.BluetoothAdvertisement . First, to find the BluetoothLE devices you’d need to instialize BluetoothLEAdvertisementWatcher object:

  // Bluetooth LE Discovery
  BluetoothLEAdvertisementWatcher bleAdvertWatcher = new BluetoothLEAdvertisementWatcher();
  public sealed partial class MainPage : Page
  {
        // Create and initialize a new watcher instance.
        bleAdvertWatcher = new BluetoothLEAdvertisementWatcher();
        bleAdvertWatcher.Received += OnAdvertisementReceived;
        bleAdvertWatcher.Stopped += OnAdvertisementWatcherStopped;
        bleAdvertWatcher.ScanningMode = BluetoothLEScanningMode.Active;
        bleAdvertWatcher.Start();
  }

I’ll not dig into the details, but with this sample in mind here is the outline of how I achieved BluetoothLE in-app scan and connect.

  1. When OnAdvertisementReceived fires you get the discovered devices ID from the EventArgs
  2. After the user discovers the device sought, then a user input would start a the asynchronous creation of a BluetoothLEDevice using the ID found from the AdvertisementWatcher.
  3. Here’s where it gets hackish: If the device is successful in connecting, then there is no event–rather, a callback timer should be started with enough time for the BluetoothLEDevice to connect and enumerate.
  4. When the timer callback fires then, using the new var device = await BluetoothLEDevice.FromBluetoothAddressAsync(ID).
  5. After the wait, the services variable should have all of the services found on the BluetoothLEDevice. At this point, all the services on the remote device should be enumerated–and var services = device.GattServices, which includes enumerating services and characteristics.

What the API actually expects is the user will connect to the device using Windows built-in Bluetooth support. This API seems poorly thought out and unfortunate. Even Apple, with all of their “developer guidance”, doesn’t tie the developers’ hands when searching and connecting to BluetoothLE devices. Of course, CoreBluetooth was developed early in BluetoothLE’s lifecycle, so maybe that’s before API developers knew better than turn too much power over to code-consumers? Who knows! But I’ve strong feelings on the matter, given it took me so much time to figure out Microsoft’s intentions.

And with that--I'm closing down the Lumi3 project and starting on Lumi4.

/> JPS DSRIP Report V2.0

JPS DSRIP Report V2.0

options(java.parameters = "-Xmx14336m")  ## memory set to 14 GB
library("XLConnect")
library("sqldf")
library("tcltk")

startDate <- "2015-10-01"
endDate <- "2016-09-30"

df <- readWorksheetFromFile("JPS_Raw_Data.xlsx", sheet = 1, startRow = 2)

#sampleVector <- sample(1:nrow(df), 30000)
#df2 <- df[sampleVector,]

#write.csv(df2, file="Sample of JPS_Raw_Data (30000).csv", na="")

#df3 <- read.csv("Sample of JPS_Raw_Data (30000).csv")

### Formatting ###################################
df3[is.na(df3)] <- ""
df3$Participant.Enterprise.Identifier <- gsub("-", "", df3$Participant.Enterprise.Identifier)
colnames(df3)[2] <- "peid"
colnames(df3)[5] <- "CaseNumber"
colnames(df3)[7] <- "Gender"
colnames(df3)[8] <- "Race"
colnames(df3)[9] <- "Ethnicity"
colnames(df3)[10] <- "ProgramName"
colnames(df3)[11] <- "SiteName"
colnames(df3)[12] <- "AgreesToShareOne"
colnames(df3)[13] <- "AgreesToShareTwo"
colnames(df3)[17] <- "ScanCardIssuedDate"
colnames(df3)[21] <- "ProgramStartDate"
df3$ProgramStartDate <- as.character(df3$ProgramStartDate)
colnames(df3)[22] <- "ProgramEndDate"
df3$ProgramEndDate <- as.character(df3$ProgramEndDate)
df3$ScanCardIssuedDate <- as.character(df3$ScanCardIssuedDate)
colnames(df3)[19] <- "OutreachContactDate"
df3$OutreachContactDate <- as.character(df3$OutreachContactDate)

##################################################

# Filter to only participants who agree to share information.
df4 <- sqldf("SELECT * FROM df3 
              WHERE (
                 AgreesToShareOne == 'Yes' 
                OR AgreesToShareTwo == 'Yes')
             ")

##################################################
####### Start ####################################
####### Get Most Recent Scancard PEIDs ###########
##################################################

df5a <- sqldf("SELECT * FROM df4 WHERE ScanCardIssuedDate != ''")

# Filter to Scan Card Creations (First time Homeless) or Return in Six Months
str2 <- paste("SELECT * FROM df5a WHERE ScanCardIssuedDate > '", startDate, "' AND ScanCardIssuedDate < '", endDate, "'", sep="")
df5a <- sqldf(str2)

str <- paste("SELECT peid, MAX(ScanCardIssuedDate) AS 'MostRecentScanCardDate', 'Scan-card' As 'DateType', Value_1712 As 'Issuance Type', 'Scan-card' As 'ContactType' FROM df5a WHERE Value_1712 = 'Scan Card Creation (First time homeless clients)' OR  Value_1712 = 'Scan Card Renewal (clients who return to the shelter after six months of being away)' GROUP BY peid ORDER BY MostRecentScanCardDate DESC", sep = "")

df5a <- sqldf(str)
##################################################
####### End = df5a ###############################
####### Get Most Recent Scancard PEIDs ###########
##################################################

##################################################
####### Start ####################################
####### Get most recent Outreach Contact #########
##################################################

df5b <- sqldf("SELECT *
              FROM df4 
              WHERE Outreach_Contact_2478 != ''
              ")

str <- paste("SELECT peid, MAX(Outreach_Contact_2478) As 'MostRecentOutreachContact', 'OutreachContact' As 'DateType' FROM df5b WHERE Outreach_Contact_2478 > '", startDate, "' AND Outreach_Contact_2478  < '", endDate, "' GROUP BY peid ORDER BY MostRecentOutreachContact DESC", sep = "")

df5b <- sqldf(str)
##################################################
####### End = df5a ###############################
####### Get most recent Outreach Contact #########
##################################################

##################################################
####### Start ####################################
####### Get most recent Program Enrollment #######
##################################################

#### 'CD PIT ES LTB' Program Group #######
  # Emergency Youth Shelter
  # ALS Emergency Shelter
  # Employment Program
  # Emergency Shelter
  # PNS-Lowden Schutts Program for Women and Children
  # PNS-Moving Home
  # Veteran's Voice Shelter Based
  # S.T.A.R.T

#### 'TH JPS Project' Program Group
  # Families Together TH
  # ARL.HA -Transitional Housing
  # YWCA-TBLA 114 RRH
  # CEC TH
  # CEC- TBLA 114 Transitional Housing
  # 3CP
  # GRACE-Transitional Housing - TBLA 114
  # NASH TH
  # Liberty House TH
  # MHMR-HS- TBLA 114 TH
  # PNS-Veteran Transitional Living
  # TC-TBLA 114 Transitional Housing TCCD
  # SIMON
  # The Salvation Army Mabee Center -- TBLA 114

#### Individual Program Groups
  # ALS Emergency Shelter
  # Emergency Shelter

df5c <- sqldf("SELECT * 
              FROM df4
              WHERE ProgramName = 'Emergency Youth Shelter'
                 OR ProgramName = 'ALS Emergency Shelter'
                 OR ProgramName = 'Employment Program'
                 OR ProgramName = 'Emergency Shelter'
                 OR ProgramName = 'PNS-Lowden Schutts Program for Women and Children'
                 OR ProgramName = 'PNS-Moving Home'
                 OR ProgramName = 'Veteran''s Voice Shelter Based'
                 OR ProgramName = 'S.T.A.R.T'

                OR ProgramName = 'YWCA-TBLA 114 RRH'
                OR ProgramName = 'CEC TH'
                OR ProgramName = 'CEC- TBLA 114 Transitional Housing'
                OR ProgramName = '3CP'
                OR ProgramName = 'GRACE-Transitional Housing - TBLA 114'
                OR ProgramName = 'NASH TH'
                OR ProgramName = 'Liberty House TH'
                OR ProgramName = 'MHMR-HS- TBLA 114 TH'
                OR ProgramName = 'PNS-Veteran Transitional Living'
                OR ProgramName = 'TC-TBLA 114 Transitional Housing TCCD'
                OR ProgramName = 'SIMON'
                OR ProgramName = 'The Salvation Army Mabee Center -- TBLA 114'

                OR ProgramName = 'ALS Emergency Shelter'
              ")

# TODO: Fix ProgramEndDate to remove HH:MM:SS instead of hacking it.
df5c <- sqldf("SELECT *, date(ProgramEndDate) As ProgramEndDate2 FROM df5c")

#df5c <- sqldf("SELECT * FROM df5c WHERE ProgramStartDate >= '2015-10-01'
#                                  AND ( ProgramEndDate2 = ''
#                                        OR ProgramEndDate2 >= '2015-01'
#                                      ) 
#              ")

df5c <- activeFilter(df5c, 'ProgramStartDate', 'ProgramEndDate2', startDate, endDate)

df5c <- sqldf("SELECT peid, ProgramName, SiteName, MAX(ProgramStartDate) As 'MostRecentProgramStart', ProgramEndDate As 'MostRecentProgramEnd' 
              FROM df5c
              GROUP BY peid
              ORDER BY MostRecentProgramStart
              ")

##################################################
####### Start ####################################
####### Aggregate Outreach, Scancard, Program ####
##################################################

df6 <- sqldf("SELECT * FROM df5a a LEFT JOIN df5b b ON a.peid=b.peid")
df6 <- subset(df6)
df6 <- sqldf("SELECT * FROM df6 a LEFT JOIN df5c b ON a.peid=b.peid")
df6 <- subset(df6)

df6$MostRecentProgramStart[is.na(df6$MostRecentProgramStart)] <- "1900-01-01"
df6$MostRecentOutreachContact[is.na(df6$MostRecentOutreachContact)] <- "1900-01-01"
df6$MostRecentScanCardDate[is.na(df6$MostRecentScanCardDate)] <- "1900-01-01"

#df6 <- sqldf(c("UPDATE df6 SET MostRecentScanCardDate = replace(MostRecentScanCardDate, '', '1900-01-01')", "SELECT * FROM df6"))
#df6 <- sqldf(c("UPDATE df6 SET MostRecentProgramStart = replace(MostRecentProgramStart, '', '1900-01-01')", "SELECT MostRecentProgramStart FROM df6"))

df6 <- sqldf("SELECT DISTINCT(peid), 
             CASE 
                WHEN MostRecentScanCardDate > MostRecentOutreachContact
                AND MostRecentScanCardDate > MostRecentProgramStart
              THEN MostRecentScanCardDate 
                WHEN MostRecentProgramStart > MostRecentOutreachContact
              THEN MostRecentProgramStart
                WHEN MostRecentOutreachContact = MAX(MostRecentScanCardDate, MostRecentProgramStart, MostRecentOutreachContact)
              THEN MostRecentOutreachContact
              ELSE 'Unknown'
              END AS LastContactDate, 
             CASE 
                WHEN MostRecentScanCardDate > MostRecentOutreachContact
                AND MostRecentScanCardDate > MostRecentProgramStart
             THEN 'Scan Card Issuance' 
                WHEN MostRecentProgramStart > MostRecentOutreachContact
             THEN 'Program Start Date'
                WHEN MostRecentOutreachContact = MAX(MostRecentScanCardDate, MostRecentProgramStart, MostRecentOutreachContact)
             THEN 'Outreach Contact'
             ELSE 'Unknown'
             END AS ContactDateType 
             FROM df6
             ")

##################################################
####### End = df6 ################################
####### Aggregate Outreach, Scancard, Program ####
##################################################

##################################################
####### Start ####################################
####### Add Demographics #########################
##################################################

df7 <- sqldf("SELECT DISTINCT(peid), SSN, Name, CaseNumber, DOB, Gender, Race, Ethnicity
             FROM df3
             ")

df7 <- sqldf("SELECT a.*, b.SSN, b.Name, b.CaseNumber, b.DOB, b.Gender, b.Race, b.Ethnicity
             FROM df6 a
             INNER JOIN df7 b
             ON a.peid=b.peid
             ")

# activeRecords <- activeFilter(df, "occStartDate", "occEndDate", "2017-01-23", '2017-01-26')
activeFilter <- function(df, beginDate, endDate, beginRange, endRange){
  df[is.na(df)] <- ""
  str <- paste("SELECT * FROM df WHERE ", beginDate, " >= '", beginRange, "' AND ( ", endDate, " = '' OR ", endDate, " >= '", beginRange, "')", sep = "")
  #print(str)
  df <- sqldf(str)
  df
}
/> Identifying Chronically Homeless and Veteran Participants throughout a COC

This is my attempt to write SQL against the HMIS 5.1 CSVs.  It includes:

  1. Identifying Chronically Homeless (CHP) Participants enterprise Wide
  2. Identifying Veterans (Vets) enterprise wide
  3. Sorting CHPs and Vets to identify those who've exited the literal homelessness and where they went.
  4. Sorting CHPs and Vets to identify those are still in the literal homelessness
  5. Filtering to Active Participants in Projects using Entry / Exit
  6. Filtering to Active Participants in Projects using NBN
  7. Getting total NBN stays by participant

To actualy get anything done through writing SQL against these CSVs, one will need the HMIS Vendor CSV Specifications

Current HMIS CSV Specifications

library("sqldf")
library("tcltk")

startDate <- "2015-10-01"
endDate <- "2016-09-30"

affiliation <- read.csv("Affiliation.csv")
client <- read.csv("Client.csv")
disabilities <- read.csv("Disabilities.csv")
employementEducation <- read.csv("EmploymentEducation.csv")
enrollment <- read.csv("Enrollment.csv")
exit <- read.csv("Exit.csv")
export <- read.csv("Export.csv")
funder <- read.csv("Funder.csv")
healthAndDv <- read.csv("HealthAndDV.csv")
incomeBenefits <- read.csv("IncomeBenefits.csv")
inventory <- read.csv("Inventory.csv")
organization <- read.csv("Organization.csv")
project <- read.csv("Project.csv")
projectCoc <- read.csv("ProjectCoC.csv")
services <- read.csv("Services.csv")
site <- read.csv("Site.csv")

#############################################
##### Get those Impairing Disability ########
#############################################
disabledAndImpairedDf <- sqldf("SELECT PersonalID 
                              FROM disabilities 
                              WHERE DisabilityResponse = 1 
                              AND IndefiniteAndImpairs = 1")

#############################################
##### Get those with Disabling Condition ###
#############################################
disablingCondition <- sqldf("SELECT PersonalID 
                            FROM activeEnrollment 
                            WHERE DisablingCondition = 1")

#############################################
##### Get Active Participants #1 ############
#############################################
# Compares enrollment.ProjectEntryID and exit.ProjectEntryID.  
# Should take all records where there is no matching exit.

activeEnrollment <- sqldf("SELECT * 
                          FROM enrollment a 
                          LEFT JOIN exit b 
                          ON a.ProjectEntryID=b.ProjectEntryID 
                          WHERE b.ProjectEntryID IS NULL")
activeEnrollment <- subset(activeEnrollment)

## ^^^^^^ Doesn't work. ^^^^^^
# Rhis will not work for us, since many shelters are
# not entering HUD Exit Assessments.

#############################################
##### Get Active Participants #2 ############
#############################################
# For activeEnrollment, take the MAX(EntryDate) from enrollment and
# MAX(ExitDate) FROM exit.  Then, compare the dates, if the entry date is later
# then the exit date, then the participant is still active in the project.
# if the ExitDate is after the entry, then the participant is no longer in the project.
mostRecentEnrollment <- sqldf("SELECT *, MAX(EntryDate) As 'MostRecentEntryDate'
                              FROM enrollment
                              GROUP BY PersonalID")

mostRecentExit <- sqldf("SELECT *, MAX(ExitDate) As 'MostRecentExitDate'
                              FROM exit
                              GROUP BY PersonalID")

mostRecentEntryAndExit <- sqldf("SELECT a.PersonalID, a.MostRecentEntryDate, b.MostRecentExitDate, a.ProjectEntryID, b.ExitID
                                  FROM mostRecentEnrollment a 
                                  LEFT JOIN mostRecentExit b
                                  ON a.PersonalID=b.PersonalID
                                ")

activeParticipants <- sqldf("SELECT PersonalID, MostRecentEntryDate, MostRecentExitDate, ProjectEntryID, ExitID
                              FROM mostRecentEntryAndExit
                                WHERE (MostRecentEntryDate > MostRecentExitDate)
                                OR MostRecentExitDate IS NULL
                            ")

dayLongParticipants <- sqldf("SELECT PersonalID, MostRecentEntryDate, MostRecentExitDate, ProjectEntryID, ExitID
                              FROM mostRecentEntryAndExit
                              WHERE (MostRecentEntryDate = MostRecentExitDate)
                            ")

inactiveParticipants <- sqldf("SELECT PersonalID, MostRecentEntryDate, MostRecentExitDate, ProjectEntryID
                              FROM mostRecentEntryAndExit
                              WHERE (MostRecentEntryDate < MostRecentExitDate)
                            ")

## ^^^^^^ Grr...Doesn't work. ^^^^^^
# Unfortunately, this wont work because of participants exited from a project
# then enrolled in a different project on the same day.
# Looks like I'm getting active participants through method one, filtering out
# the TCES, and then adding them back in.  
# Wait, the TCES:PNS and TCES:TSA should both be pulling only participants
# who've stayed in a bed.  Maybe it's the DRC which is responsible for the high total?
# I'll re-pull the data excluding the DRC and see if that drastically lowers the number.

#############################################
##### Get Active Participants #3 ############
############## Incomplete ###################
#############################################

# My next thought is to break out NBN data, where Exits are collected.  Once removed, then the activeEnrollment formula
# should work, since everything else is Entry / Exit.  I'll then use the Services NBN dates to determine if someone is
# still active in the shelters.

#############################################
##### Get Active NBN Participants  ##########
###### And their total NBN stays ############
#############################################
# 200 = NBN Service
# http://www.hudhdx.info/VendorResources.aspx
clientNbn <- sqldf("SELECT * 
                   FROM services
                   WHERE RecordType = 200 
                   ") 

str <- paste("SELECT * FROM clientNbn WHERE DateProvided > '", startDate, "' AND DateProvided < '", endDate,"'", sep = "")

activeClientNbn <- sqldf(str)
distinctActiveClientNbn <- sqldf("SELECT DISTINCT(PersonalID) FROM activeClientNbn")

clientNbnDuration <- sqldf("SELECT PersonalID, COUNT(DateProvided) As 'Total NBN Stays'
                            FROM clientNbn
                            GROUP BY PersonalID
                           ")

activeNbnClientWithTotalNbnDuration <- sqldf("SELECT a.PersonalID, b.'Total NBN Stays'
                                             FROM distinctActiveClientNbn a
                                             INNER JOIN clientNbnDuration b
                                             ON a.PersonalID=b.PersonalID
                                             ORDER BY b.'Total NBN Stays' DESC
                                             ")

#################

#############################################
##### Length-of-Stay ########################
#############################################
# Participants who meet the length-of-stay in homelessness requirement
# Either through four or more occurences with cumulative duration exceeding a year
# Or a consequtive year.
#                 113 = "12 Months"
#                 114 = "More than 12 Months"
chronicityDf <- sqldf("SELECT PersonalID, 'Yes' As 'Meets LOS'
                               FROM activeEnrollment
                               WHERE (TimesHomelessPastThreeYears = 4
                                    AND (
                                          MonthsHomelessPastThreeYears = 113
                                          OR MonthsHomelessPastThreeYears = 114)
                                        )
                               OR (CAST(JULIANDAY('now') - JULIANDAY(DateToStreetESSH) AS Integer) > 364
                                   AND (DateToStreetESSH != '') 
                                  )
                               ")

#############################################
##### Chronically Homeless ##################
#############################################
# Take the distinct PersonalIDs of individuals who meet both chronicity
# and disabling condition.
chronicallyHomeless <- sqldf("SELECT DISTINCT(a.PersonalID)
                              FROM chronicityDf a
                              INNER JOIN disablingCondition b
                              ON a.PersonalID=b.PersonalID
                             ")

# Get client info for chronically homeless.
chClient <- sqldf("SELECT *, 'Yes' As 'Chronically Homeless' 
                  FROM client a 
                  INNER JOIN chronicallyHomeless b 
                  ON a.PersonalID=b.PersonalID
                  ")
chClient <- subset(chClient)

#############################################
##### Chronically Homeless Veterans #########
#############################################
# Finds the total Chronically Homeless Veterans in the data set.
chronicallyHomelessVeterans <- sqldf("SELECT * 
                                     FROM chClient 
                                     WHERE VeteranStatus = 1
                                     ")

#############################################
##### Exit Destination Information ##########
############## Incomplete ###################
#############################################
# Take only the most recent exit assessment
clientLastExit <- sqldf("SELECT PersonalID, MAX(ExitDate), Destination
                        FROM exit
                        GROUP BY PersonalID
                        ")

clientLastExit <- destinationToReadable(clientLastExit)

clientsBrief <- sqldf("SELECT PersonalID, FirstName, LastName, SSN FROM client")
clientsBriefExit <- sqldf("SELECT * 
                          FROM clientsBrief a 
                          INNER JOIN clientLastExit b
                          ON a.PersonalID=b.PersonalID
                        ")

# Target day is 1-26-2017

# activeRecords <- activeFilter(df, "occStartDate", "occEndDate", "2017-01-23", '2017-01-26')
activeFilter <- function(df, dateVector1, dateVector2, beginRange, endRange){
  df[is.na(df)] <- ""
  df[dateVector1,] <- as.character(df[dateVector1,])
  df[dateVector2,] <- as.character(df[dateVector2,])
  str <- paste("SELECT * FROM df WHERE (", dateVector1, "< '", endRange, "' AND ", dateVector2, " = '') OR (", dateVector1, "< '", endRange, "' AND ", dateVector2, " > '", beginRange, "')", sep = "")
  sqldf(str)
}

makeDestinationReadable <- function (df) {
  df <- exit
  df <- sqldf("SELECT *, Destination as 'ReadableDestination' FROM df")
  df$ReadableDestination[df$ReadableDestination == "1"] <- "Emergency shelter, including hotel or motel paid for with emergency shelter voucher"
  df$ReadableDestination[df$ReadableDestination == "2"] <- "Transitional housing for homeless persons (including homeless youth)"
  df$ReadableDestination[df$ReadableDestination == "3"] <- "Permanent housing for formerly homeless persons (such as: CoC project; or HUD legacy programs; or HOPWA PH)"
  df$ReadableDestination[df$ReadableDestination == "4"] <- "Psychiatric hospital or other psychiatric facility"
  df$ReadableDestination[df$ReadableDestination == "5"] <- "Substance abuse treatment facility or detox center"
  df$ReadableDestination[df$ReadableDestination == "6"] <- "Hospital or other residential non-psychiatric medical facility"
  df$ReadableDestination[df$ReadableDestination == "7"] <- "Jail, prison or juvenile detention facility"
  df$ReadableDestination[df$ReadableDestination == "8"] <- "Client doesn’t know"
  df$ReadableDestination[df$ReadableDestination == "9"] <- "Client refused"
  df$ReadableDestination[df$ReadableDestination == "10"] <- "Rental by client, no ongoing housing subsidy"
  df$ReadableDestination[df$ReadableDestination == "11"] <- "Owned by client, no ongoing housing subsidy"
  df$ReadableDestination[df$ReadableDestination == "12"] <- "Staying or living with family, temporary tenure (e.g., room, apartment or house)"
  df$ReadableDestination[df$ReadableDestination == "13"] <- "Staying or living with friends, temporary tenure (e.g., room apartment or house)"
  df$ReadableDestination[df$ReadableDestination == "14"] <- "Hotel or motel paid for without emergency shelter voucher"
  df$ReadableDestination[df$ReadableDestination == "15"] <- "Foster care home or foster care group home"
  df$ReadableDestination[df$ReadableDestination == "16"] <- "Place not meant for habitation (e.g., a vehicle, an abandoned building, bus/train/subway station/airport or anywhere outside)"
  df$ReadableDestination[df$ReadableDestination == "17"] <- "Other"
  df$ReadableDestination[df$ReadableDestination == "18"] <- "Safe Haven"
  df$ReadableDestination[df$ReadableDestination == "19"] <- "Rental by client, with VASH housing subsidy"
  df$ReadableDestination[df$ReadableDestination == "20"] <- "Rental by client, with other ongoing housing subsidy"
  df$ReadableDestination[df$ReadableDestination == "21"] <- "Owned by client, with ongoing housing subsidy"
  df$ReadableDestination[df$ReadableDestination == "22"] <- "Staying or living with family, permanent tenure"
  df$ReadableDestination[df$ReadableDestination == "23"] <- "Staying or living with friends, permanent tenure"
  df$ReadableDestination[df$ReadableDestination == "24"] <- "Deceased"
  df$ReadableDestination[df$ReadableDestination == "25"] <- "Long-term care facility or nursing home"
  df$ReadableDestination[df$ReadableDestination == "26"] <- "Moved from one HOPWA funded project to HOPWA PH"
  df$ReadableDestination[df$ReadableDestination == "27"] <- "Moved from one HOPWA funded project to HOPWA TH"
  df$ReadableDestination[df$ReadableDestination == "28"] <- "Rental by client, with GPD TIP housing subsidy"
  df$ReadableDestination[df$ReadableDestination == "29"] <- "Residential project or halfway house with no homeless criteria"
  df$ReadableDestination[df$ReadableDestination == "30"] <- "No exit interview completed"
  df$ReadableDestination[df$ReadableDestination == "99"] <- "Data not collected"

  df
}
/> Sampling Large Data

This R function allows sampling of a dataframe.  This is helpful when writing a script which will be used against a large dataframe, however, writing the script is iterative.  Sampling allows the overall reduction in time of testing iterations, without losing the validity of realistic results.

    options(java.parameters = "-Xmx14336m")  ## memory set to 14 GB
    library("sqldf")
    library("XLConnect")
    library("tcltk")

    df <- readWorksheetFromFile("Data_X.xlsx", sheet = 1, startRow = 1)

    sampleVector <- sample(1:nrow(df), 30000)
    df2 <- df[sampleVector,]

    write.csv(df2, file="Sample of Data_X (30000).csv", na="")

Lumi3

This project is meant as stepping stone to implement Lumi2.  The Lumi projects I've been working on are over-the-air uploaders of Arduino / AVR programs to Atmega and Atiny chips which are programmed with the TinySafeBoot bootloader.  The goal is to allow the user to select either WiFi or Bluetooth, create a connection to either an ESP8266 or HM-1X device, and upload whatever program to an AVR connected to the wireless receiving device.

The last iteration of Lumi was written in Windows Universal Apps SDK.  Unfortunately, the code-base turned into spaghetti.  I've diagnosed the issues to be due to God-modules, poor understand of object-oriented design, and rushed coding.  Passion got ahead of my ability.  Here's my history on the project so far:

  1. Vorpal Hoff -- an attempt at wireless uploading with a HM-11 and LPC1114 combination.  Written in C / C++.
  2. HM-1X Aid -- this project was meant to be a GUI on top of the HM-1X modules, allowing "easy" editing of the module's behavior.  It was my first venture into C#.  (It's sooo bad;although, the serial communication was asynchoronous.)
  3. Lumi1 -- this the first succesful TinySafeBoot uploader.  It was written in C# using the .NET WinForms.  Unfortunately, it was synchoronou.  And I was finished with the USB-to-UART uploader before I realized there was no easy BLE support in WinForm's .NET.
  4. Lumi2 --  this is where things start getting better.  It is the current version of the TSB wireless bootloader.  It works, is asynchronous, and has BLE support.  Unfortunately, the code turned into spaghetti.  This is largely due to my poor understanding of object-oriented design.  It has god-modules, a horrifically implemented SerialEvent response protocol, poor encapsulation, no polymorphism.  It's just a mess.

Now, I'm going for the third attempt.  I'll attempt to correct for the above errors and implement the WiFi uploading, with the receiving device being the ESP8266.

Lumi3

Here is sketch of the design:

/> Lumi2_rough_Sketch_v2.png

ESPER

ESPER is a mini project to troubleshoot how the Lumi3 program will interact with a remote device.

There are two sets of code below, the first is the C# side of the interaction.  It sets up an HttpClient with POST and GET calls.  The one real variant which makes C# ESPER code a little bit different is the asynchronous polling POST request for data.  This is meant to imitate a serial communication RX line across a WiFi signal.

The other code is Arduino C and sets up the ESP8266 device as an HTTP WebServer.  It can then take data received from the UART and print it to the server.  This allows the C# polling POST call to pick up the data.  Of course, in the same manner, there is a the Arduino code is setup to receive data from the HTTP Client and transmit it across the UART. Voila! Serial communication across WiFi.  Now all we need is the annoying autobauding sounds and we will be firmly back in the 1990s.

Update 2/7/2016

/> ESPER_ui_1.png I've added a search method to the ESPER class.  Basically, this iterates over a range POSTing a name request for the ESPER.  When the C# code discovers an ESPER, then it adds it to an array.  I'm pretty happy with it.

I did run into an issue trying to use Windows.HttpClient, as there doesn't seem to be a way to adjust the timeout.  The default was like 3 seconds, which is way too long.  Therefore, the System.Net.HttpClient was used, since it has a Timeout property which takes a Timespan.

C#

 public async Task<List<Uri>> SearchForESPER(int startingSub, int endingSub)
        {
            var httpClient = new System.Net.Http.HttpClient();
            httpClient.Timeout = new TimeSpan(0, 0, 0, 0, 300);
            var webService = WebServerUrl + "name";
            List<Uri> discoveredIPs = new List<Uri>();
            EsperProgressBar.Maximum = endingSub - startingSub;

            for (int i = startingSub; i < endingSub; i++)
            {
                try
                {
                    string ip = "http://192.168.1." + i.ToString() + "/";
                    var resourceUri = new Uri(ip);
                    var response = await httpClient.PostAsync(resourceUri, null);
                    if(response.IsSuccessStatusCode == true)
                    {
                        discoveredIPs.Add(resourceUri);
                    }
                    response.Dispose();
                }
                catch (Exception ex)
                {

                }
                EsperProgressBar.Value += 1;
            }
            EsperProgressBar.Value = 0;
            EsperProgressBar.IsEnabled = false;
            return discoveredIPs;
        }

.......

        private async void Search_Click(object sender, RoutedEventArgs e)
        {
            Esper esper = new Esper(ProgressBar);
            var discoveredIPs = await esper.SearchForESPER(98, 130);

            foreach(Uri ip in discoveredIPs) {
                IPComboBox.Items.Add(ip.Host);
            }
            IPComboBox.SelectedIndex = 0;
        }

C# ESPER

    using System;
    using System.Collections.Generic;
    using System.Diagnostics;
    using System.Linq;
    using System.Runtime.InteropServices.WindowsRuntime;
    using System.Text;
    using System.Threading;
    using System.Threading.Tasks;
    using Windows.Storage.Streams;
    using Windows.Web.Http;

    namespace ESPER
    {
        class Esper
        {
            const int defaultPollingDelay = 50; 

            HttpClient httpClient = new HttpClient();
            CancellationTokenSource PollingForDataCancelToken = new CancellationTokenSource();

            private string WebServerUrl { get; set; }
            private int PollingDelay { get; set; } = defaultPollingDelay;
            private bool PollingActive { get; set; } = false;

            public Esper(string consumerUrl)
            {
                WebServerUrl = consumerUrl;
            }

            public async void PostByteArray(byte[] data)
            {

                var httpClient = new HttpClient();
                var webService = WebServerUrl + "data";
                var resourceUri = new Uri(WebServerUrl);
                try
                {
                    IBuffer buffer = data.AsBuffer();
                    using (HttpBufferContent content = new HttpBufferContent(buffer))
                    {
                        content.Headers.Add("Content-Type", "text/html; charset=utf-8");
                        content.Headers.ContentLength = buffer.Length;
                        var response = await httpClient.PostAsync(resourceUri, content);
                        Debug.WriteLine(response);
                    }
                }
                catch (TaskCanceledException ex)
                {
                    // Handle request being canceled due to timeout.
                }
            }

            public async void PostString(string str)
            {
                var httpClient = new HttpClient();
                var webService = WebServerUrl + "string";
                var resourceUri = new Uri(webService);
                try
                {
                    using (HttpStringContent content = new HttpStringContent(str, Windows.Storage.Streams.UnicodeEncoding.Utf8))
                    {
                        content.Headers.ContentLength = (ulong)str.Length;
                        using (var response = await httpClient.PostAsync(resourceUri, content)) { };
                    }
                }
                catch (TaskCanceledException ex)
                {
                    // Handle request being canceled due to timeout.
                }
            }

            public void Start()
            {
                if(false == PollingActive)
                {
                    PollingActive = true;
                    PollingForDataCancelToken = new CancellationTokenSource();
                    PollWebServerDataAvailability();
                }
            }

            public void End()
            {
                PollingActive = false;
                PollingForDataCancelToken.Cancel();
            }

            public void SetPollingDelay(int delayInMilliseconds) { PollingDelay = delayInMilliseconds; }

            private void PollWebServerDataAvailability()
            {   
                try
                {
                    Task.Run(async () =>
                    {
                        while (true)
                        {
                            if (PollingForDataCancelToken.IsCancellationRequested)
                            {
                                PollingForDataCancelToken.Token.ThrowIfCancellationRequested();
                            }
                            await GetData();
                            await Task.Delay(PollingDelay);
                        }
                    }, PollingForDataCancelToken.Token);
                } catch (TaskCanceledException)
                {
                    // TODO: Add cancelation callback here.
                }
            }

            public async Task<string> GetData()
            {
                var cts = new CancellationTokenSource();
                cts.CancelAfter(TimeSpan.FromSeconds(30));

                var webService = WebServerUrl + "buffer";
                var resourceUri = new Uri(webService);
                try
                {
                    HttpResponseMessage response = await httpClient.PostAsync(resourceUri, null);
                    var message = await response.Content.ReadAsStringAsync();
                    if (message != "") { Debug.WriteLine(message); }
                    response.Dispose();
                    cts.Dispose();
                    return message;
                }
                catch (TaskCanceledException ex)
                {
                    // Handle request being canceled due to timeout.
                    return "";
                }
                return "";
            }
        }
    }
Arduino ESPER WebServer

    /*
     * This code has been adapted from:
     *    "SDWebServer - Example WebServer with SD Card backend for esp8266
     *    Copyright (c) 2015 Hristo Gochkov. All rights reserved.
     *    This file is part of the ESP8266WebServer library for Arduino environment."
     * 
    */

    const char* ssid = "SSID";
    const char* password = "password";

    // Gross.  Global variables.  These are used for collecting Serial Data.
    String inputBuffer = "";         

    #include <ESP8266WiFi.h>
    #include <WiFiClient.h>
    #include <ESP8266WebServer.h>
    #include <ESP8266mDNS.h>

    const int ledPin = 2;
    const char* host = "esp8266sd";

    ESP8266WebServer server(80);

    void returnOK() {
      server.send(200, "text/plain", "");
    }

    void returnFail(String msg) {
      server.send(500, "text/plain", msg + "\r\n");
    }

    void debugWebRequest(){
      String message = "";
      message += "URI: ";
      message += server.uri();
      message += "\nMethod: ";
      message += (server.method() == HTTP_GET)?"GET":"POST";
      message += "\nArguments: ";
      message += server.args();
      message += "\n";
      for (uint8_t i=0; i<server.args(); i++){
        message += " NAME:"+server.argName(i) + "\n VALUE:" + server.arg(i) + "\n";
      }
      server.send(404, "text/plain", message);
      Serial.print(message);
    }

    void getSerialBuffer(){
      Serial.print("Sent data: ");
      Serial.println(inputBuffer);
      server.send(200, "text/plain", inputBuffer);
      inputBuffer = "";
    }

    void handleUnknownPost(){
      returnOK();
      //debugWebRequest();  
      Serial.print("Unknown POST.");
    }

    void handleStringPost(){
      returnOK();
      //debugWebRequest();
      Serial.print("Got data: ");
      Serial.print(server.arg(0));
    }

    void handleDataPost(){
      returnOK();
      //debugWebRequest();
      Serial.print("Data POST.");
    }

    void handleNotFound(){
      returnOK();
      //debugWebRequest();
      Serial.print("Resource not found POST.");
    }

    void setup(void){
      pinMode(ledPin, OUTPUT);

      Serial.begin(115200);
      Serial.setDebugOutput(true);
      Serial.print("\n");
      WiFi.begin(ssid, password);
      Serial.print("Connecting to ");
      Serial.println(ssid);

      digitalWrite(ledPin, HIGH);
      bool isLedPinOn = true;

      // Wait for connection
      uint8_t i = 0;
      while (WiFi.status() != WL_CONNECTED && i++ < 20) {//wait 10 seconds
        delay(500);
        Serial.print(".");
        isLedPinOn = !isLedPinOn;  

      }
      Serial.println("");
      if(i == 21){
        digitalWrite(ledPin, HIGH);
        Serial.print("Could not connect to");
        Serial.println(ssid);
        while(1) { 
          digitalWrite(ledPin, isLedPinOn ? HIGH : LOW);
          delay(200); 
        }
      }

      digitalWrite(ledPin, LOW);  

      Serial.print("Connected! IP address: ");
      Serial.println(WiFi.localIP());

      if (MDNS.begin(host)) {
        MDNS.addService("http", "tcp", 80);
        Serial.println("MDNS responder started");
        Serial.print("You can now connect to http://");
        Serial.print(host);
        Serial.println(".local");
      }

      server.on("/", HTTP_POST, handleUnknownPost);
      server.on("/string", HTTP_POST, handleStringPost);
      server.on("/data", HTTP_POST, handleDataPost);
      server.on("/buffer", HTTP_POST, getSerialBuffer);
      server.onNotFound(handleNotFound);

      server.begin();
      Serial.println("HTTP server started");
    }

    void loop(void){
      server.handleClient();
      while (Serial.available()) {
        char inChar = (char)Serial.read();
        inputBuffer += inChar;
      }
    }
/> Splitting Program Data

options(java.parameters = "-Xmx14336m")  ## memory set to 14 GB
library("sqldf")
library("XLConnect")
library("tcltk")

# 1. Load all PoS 
# 2. Load all NBN stays
# 3. Find all PEID's who have had a PoS in the last year
# 4. Find all PEID's of those who have stayed in a NBN bed in the last year
# 5. Append the lists and get a distinct set of PEIDs
# 6. Load family and individual demographic information
# 7. Inner join the demographic data to the distinct PEID set.
# 8. Write all information to file.
# 9. Pray.


allNBN <- readWorksheetFromFile("All TCES NBN Bed Data up to 1-19-2017 -- v04.xlsx", sheet = 1, startRow = 1)
allPoS <- readWorksheetFromFile ("All PoS up to 12-14-16.xlsx", sheet = 1, startRow = 1)

# Make SQL friendly headers
colnames(allNBN)[1] <- "peid"
colnames(allNBN)[3] <- "caseNumber"
colnames(allNBN)[5] <- "firstName"
colnames(allNBN)[6] <- "lastName"
colnames(allNBN)[10] <- "OccupancyStart"
colnames(allNBN)[11] <- "OccupancyEnd"


colnames(allPoS)[1] <- "ServiceName"
colnames(allPoS)[2] <- "peid"
colnames(allPoS)[3] <- "DateOfContact"

# Change dates to by SQL friendly.
allNBN$OccupancyStart <- as.character(allNBN$OccupancyStart)
allNBN$OccupancyEnd <- as.character(allNBN$OccupancyEnd)

allPoS$DateOfContact <- as.character(allPoS$DateOfContact)

# Find the days since the stay in the NBN bed started
allNBN <- sqldf("SELECT *, CAST((julianday('NOW') - julianday(OccupancyStart))AS INTEGER) 'DaysSince' 
                FROM allNBN 
                ORDER BY 'DaysSince' DESC")

write.csv(allNBN, "Test.csv")

# Find the days since the PoS was occurred.
allPoS <- sqldf("SELECT *, CAST((julianday('NOW') - julianday(DateOfContact))AS INTEGER) 'DaysSince' 
                FROM allPoS 
                ORDER BY 'DaysSince' DESC")


allNBN <- subset(allNBN)
allPoS <- subset(allPoS)

# Get all PEIDs who've stayed in a NBN bed in the last year
activeInNBN <- sqldf("SELECT * FROM allNBN WHERE DaysSince < 366")

# Get all PEIDS who received a Service in the last year
activePoS <- sqldf("SELECT * FROM allPoS WHERE DaysSince < 366")

targetElementsFromActivePoS <- sqldf("SELECT peid, ServiceName, DaysSince, 'PoS' FROM activePoS ORDER BY DaysSince DESC")
targetElementsFromActiveNBN <- sqldf("SELECT peid, caseNumber, firstName, lastName, DaysSince, 'NBN' FROM activeInNBN ORDER BY DaysSince DESC") 

# Get all distinct PEIDs.
posPEIDs <- sqldf("SELECT DISTINCT(peid) FROM targetElementsFromActivePoS")
nbnPEIDs <- sqldf("SELECT DISTINCT(peid) FROM targetElementsFromActiveNBN")

# Combine the PEIDs
posAndNbnPEIDs <- rbind(posPEIDs, nbnPEIDs)

# Get de-duplicate.
allPEIDsDistinct <- sqldf("SELECT DISTINCT(peid) FROM posAndNbnPEIDs")

# Load all FAMILY demographic info
allTCESDemographicsFamilies <- readWorksheetFromFile("Demographics in TCES up to 1-24-2017 -- Batch Upload - Participants.xlsx", sheet = 1, startRow = 1)
colnames(allTCESDemographicsFamilies)[1] <- "peid"

# Load all INDIVIDUAL demographic info
allTCESDemographicsIndividuals <- readWorksheetFromFile("Demographics in TCES up to 1-24-2017 -- Batch Upload - Participants.xlsx", sheet = 2, startRow = 1)
colnames(allTCESDemographicsIndividuals)[1] <- "peid"


# Add back demographics
activeThisYearWithDemoFamilies <- sqldf("SELECT * FROM allPEIDsDistinct INNER JOIN allTCESDemographicsFamilies ON allPEIDsDistinct.peid=allTCESDemographicsFamilies.peid")
activeThisYearWithDemoIndividuals <- sqldf("SELECT * FROM allPEIDsDistinct INNER JOIN allTCESDemographicsIndividuals ON allPEIDsDistinct.peid=allTCESDemographicsIndividuals.peid")

write.csv(activeThisYearWithDemoFamilies, file = "Active Families Demographics from TCES.csv")
write.csv(activeThisYearWithDemoIndividuals, file = "Active Individuals Demographics from TCES.csv")
/> Robber Board

The Robber Board

This board originated with a request from an LPC who was practicing EMDR and wanted to upgrade his feedback machine.  He had requested it be wireless and provide both haptic and visual feedback.  The whole thing fell apart when he sent me an NDA which seemed typical of real professional level projects.  However, it would have prohibited me from sharing anything I discovered, and given the amount I could have made from it, well, it just wasn't worth it.  I thought I'd finish the project and share with everyone, since sharing is the greatest form of payment.

Here are some of the features of the board:

  1. OTA Uploading to an ATtiny84
  2. An on board charing circuit with load sharing capability ( thanks Zak Kemble )
  3. Two RGB LEDs to provide visual feedback.
  4. One unidirectional, motor-driver meant to control a vibration motor.
  5. Three free pins

My purpose in completing the board is to continue to test my TinySafeBoot BLE uploader:

Lumi(v2)

I'm also in the process of re-writing the uploader to be cross platform, targetting Android, iOS, and Windows 10.  It'll be a feat, but I'm thinking I'll center the project around Dropbox.  The consumer would:

  1. Compile an AVR binary using Arduino, Atmel Studio, or AVRDude
  2. Save the binary in Dropbox
  3. Lumi3 will then connect to the target device, this could be done from Android, iOS, or Windows.
  4. Lumi3 would then pull the binary from Dropbox and upload it using the TinySafeBootoader.

Not sure if I can pull it off; wish me luck.  And feel free to follow the code base here:

Lumi3

/>

Design Info

Robber_Schematic.pdf

/> Robber v01 Schematic.png

/> Robber v01 T Solder Guide.png

/> Robber v01 B Solder Guide.png

HM-11 Setup

A few commands which are required to make the OTA process work correctly

  1. AT+AFTC3FF  --  This command sets all IO pins to go HIGH after connection.  This isn't needed for OTA but, since the AVR would be pulled low as soon as it connects, any sketch you have running would immediately be shutdown as the AVR's RESET is pulled low.
  2. AT+BEFC3FF -- This is like the AFTC command, however, it set IO pins HIGH after the HM-11 is powered.  For the Robber, if this is not setup the AVR will stay reset until connected.  Initially, I didn't notice this and spent a lot of time trying to figure out what the AVR wouldn't respond.  Sigh.
  3. AT+MODE2 -- this put the HM-11 into "Remote" mode.  This will allow AT commands to be sent to the HM-11 after it has been connected, through the BLE connection.  This is what allows the commands to be sent to remotely toggle the PIO connected to the AVR's RESET.

A few commands which I think make the connection more reliable:

  1. AT+BAUD2 -- this sets the communication rate between the HM-11 and AVR to 38400.  After testing, this is about the highest speed ATtiny's can for the TSB auto-baud.
  2. AT+POWE3 -- this raises the radio gain of the HM-11.  Power-convseration is not the friend of prototyping.
  3. AT+GAIN1 -- I think this raises the gain on the HM-11's RX?  I'm not sure.  The documentation is a little crappy.

Debugging First Iteration

The boards came in from OSHPark.  They look sharp--but, it's time to test the board and see what mistakes were made.

Circuits tested:

|Circuit Abstraction|Tested|Pass|Description of Issues| | Mini USB | Yes | 100% | | | MCP73831 | Yes | 100% | Battery Charges.  Used a 2k resistor for the charge rate. | | MCP73831 - LED Charge Indicator | Yes | 100% | | Load Sharing | Yes | 100% | Shesh, I love this circuit.  Thanks Zak. | | LM1117 -- 3.3v | Yes | 50% | The OUTPUT voltage was calculated based upon 5V INPUT, however, when the USB connection is removed the INPUT voltage is equal to VBAT voltage.  In short, the output is ~3.29v when connected to USB and 2.9v when running on battery.  This will drop even more when the battery discharges.  I've selected a fixed VREG to drop in later ( NCP186-AMX330TAG ). | | Tacit Reset Switch | Yes | 75% | The reset line was being pulled HIGH with a 10k resistor.  This might have been causing issues.  It was removed for the time and will be re-added if needed. | | ATtiny84 -- Wireless UART Connection | Yes | 85% | I cannot get the ATtiny84's TSB bootloader to respond.  I've tried both wireless TSB HELLO and jumpering directly to the UART.  No response.  I'm currently waiting on a SOIC-14 ATtiny AVR programme r to arrive to more easily troubleshoot the ATtiny84 chip. One thing I'd like to test is the BROWNOUT fuse setting.  It seems like this fuse was set different than usual.  Also, testing the UART on the naked chip would be helpful. Update:  Ok, not sure what happened to the chip, but apparently I killed it?  Regardless, when a new one was programmed and swapped out using a heat gun--the new one worked like a charm. I've provided more details below.| | Battery Voltage Monitor | No | | | | RGB LEDs | No | | | | HM-11 Reset of ATtiny84 | Yes | 0% | BAD IDEA! I've wasted a lot of time because the program I uploaded was sending the HM-11 RESET pin HIGH and LOW.  This made it difficult to re-program, since the HM-11 would lose connection before the upload finished.  Dear lord, what was I thinking? | | Vibration Motor | Yes | 100% | It doesn't work too well when powered by USB alone.  Probably need to get a lower powered vibration motor.  Also, I don't like the wire connections.  They get all tangled and pull off.  It'd be better with a PCB mounted vibration motor. |

/> IMG_1616_0.jpg

/> IMG_1617_0.jpg

Debugging Wireless UART Connection

When the ATtiny84 and HM-11 combination were tested using Lumi the Robber board failed to handshake.  It was disheartening.  The connections were double checked, routing was checked, jumper wires soldered on, ATtiny84 fuses checked, even checked the TSB build used. Nothing.  Frankly, the reason it was such a problem to troubleshoot was lack of foresight in prototyping.  Here are a couple things which should be in every prototype:

  1. Jumpers.  This is especially true if there is a UART connection.  They just beg to be miswired.  By providing solder-jumpers the UART connection can be tested a device at a time; ATtiny84, then, HM-11
  2. Test points.  Gah, this is a no-brainer, but in inevitably there end up being leads soldered on at the most precarious place.

Back to the Robber board issue.  I got wore out trying to do stuff like this:

/> 2017-01-22 14.16.09.jpg

The wires kept popping off, they'd need to be resoldered, then pop off again.  Finally, I broke down and bought a SOIC-14 AVR Programmer . Dear lord, where has this thing been all of my life?  Instead of taking two hours of setting up a chip, wiring leads, and programming it, I was done in about 10 minutes.  If you like SOIC ATtiny85/84 chips, get one!

Here's an action shot!
/> 2017-02-01 08.56.38.jpg

SMD RGB LED

There was trouble testing the SMD RGB LEDs.  During the solder-desoldering process the voltage traces for the green LED came off the board.  However, this shouldn't affect the second LED--but for some reason the green channel on the other LED is the only one working properly.  The red and blue do not seem to be lighting up.

I thought I'd take a moment and diagram the SMD RGBs I'm using, as I can't ever seem to get directionality correct.
/> SMD_RGB_Closeup.jpg

2017-12-24 -- v3

I've almost finished testing the Robber board v3. A few changes:

ISP Key

I've added a special ISP header to the board. It works with Tiny AVR-ISP pogo-pin programming adapter

It's a bit of a pain to solder, but it's pretty darn sweet once it's in place. Of course, the header is backwards. More on that later.

/>

/> R Function to Split CSVs

This is an R function written to split a dataset into particular sized sets, then write them as a CSV.  Often, our office is need a quick way to split files for uploading purposes, since our HMIS software doesn't handle large uploads well.

For example:

splitDataAndWriteFiles(df, 500, "My_Data")  

Will produce X number of files named "My_data_X.csv"

options(java.parameters = "-Xmx14336m")  ## memory set to 14 GB
library("XLConnect")

# Function to split files.
splitDataAndWriteFiles <- function(df, chunkSize, nameOfFiles) {
  success <- FALSE
  count <- 0
  while (!success) {
    # If you want 20 samples, put any range of 20 values within the range of number of rows
    s <- paste(((count*chunkSize)+1), "_", ((count+1)*chunkSize))
    print(s)
    chunk <- subset(df[((count*chunkSize)+1):((count+1)*chunkSize),])
    #chunk <- sample(df[5:20,])
    ## this would contain first 20 rows
    fileName <- paste(nameOfFiles, "_", as.character(count), ".csv")
    # Write out all the Active HUD Assessments.
    write.csv(chunk, file = fileName, na = "", row.names = FALSE, fileEncoding = "utf8")
    count <- count + 1
    success <- (count * chunkSize) > nrow(df)
  }
  return(success)
}

fileToSplit <- read.csv("UPLOAD -- Sal Men-- TCES Move -- TSA Bed Data Template.csv")

splitDataAndWriteFiles(fileToSplit, 5000, "Sal_Men_NBN")