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:

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

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