ABOUT
WANDA BASE

Features

Status: rebuilt - active

Core of the WANDA system, user access, groups, login, change password, common screens (label/envelope formatting) includes, office locations, configuration, etc.

Tables

  • configuration - general info like agency name
  • department - different departments in agency (currently only used in Fixed Assets)
  • location - office locations (fixed assets, referral, & subsidy contact info)
  • user - name, position, contact info, location id (used in agency forms)
  • user-group - group id, access level
  • group - group name, access level name/descriptions
  • log - log of info sent by various apps, timestamp, info
  • zipcode list - list of local towns, zipcodes, GIS info, FIPS code, county, state, used for zipclode lookup, spelling correction, and determining areas for reports
  • zip neighbor - nearby towns (ziplist ids)
  • funding sources - state/fed funding code, description, agency's ID, notes (used in state reports)

Accomplishments

  • 99.9% code is below web root, user access to programs strictly controllable.
  • Variables, table fields/names and data are meant to be user understandable.
  • A 5-level group-based access system
  • Wiki based documentation support
  • Drop in menu (install app folder and the menu items/groups are available)
  • Uses a unique text record ID to reduce collision if ever data was merged from a similar program from elsewhere. The IDs include encoded date, time user id, and location, the extra data has proved valuable for sorting by entry date and identifying problems in data entry/management (i.e. who did what and when).

Challenges

  • determining a user access control method
  • creating a workable system to have code below web root
  • working out the file system structure - it evolved over the years of development
  • Creating a framework that was more data-centric.
  • There are a few parts of WANDA that need to be Object Oriented.

Lessons

  • You can work too hard to find the best solution, learn to step away from that and work on other parts, then revisit it... sometimes you just think it's broke. Before I got the code below web root, there was a lot more effort to restrict arbitrary activation. Getting the code in a more secure configuration relives that aspect.
  • You don't really need all that much code to have a really effective framework.
  • Flexibility in entry methods makes all the difference.

CHILD CARE PROVIDER

Features

Status: rebuilt - inactive

The child care provider database is a necessary component of both child care referral and child care subsidy databases. This program manages the entry, listing and reporting on providers within given areas. There are two main parts to a provider record, the vendor, which is the legal entity for contact, tax and payment purposes, and the facility, which is the care facilities that the business runs. Providers can have multiple facilities which vary in location, ages of children served, and hours.

Historical data is required for various reports to get an accurate "point in time" picture or to see difference between one time to another. So instead of record being merely changed, changes generate a new record with the previous current record becoming an historic item.

Tables

  • vendor - (the business side)
  • facility - facility name, location address, contact/license info, director, services, capacities, stats, reg fees, directions, notes
  • facility holidays - holiday month, description, # of days
  • facility rates - from-to age, unit of time & rate for pt/ft and drop-in, notes
  • provider log - provider/facility id, date, due date, completed date, item list module, units/amount, note

Accomplishments

  • Creation of historical data record scheme using a general item ID, a unique record ID, the start & end dates of when that record was active
  • Splitting the providers into vendors and facilities, reduced duplication of data and helped get a more accurate portrayal of the 'providers' in the area instead of just the facilities.
  • Provider log enabled the creation of additional data capture for such things as rate change events (thers is a limit of rate changes allowed annually for providers of subsidized children) holidays used (also a limit allowed if paid holidays for subsidized care) and was on it's way to proving CCIP data integration.

Challenges

  • The historical record was a bit of a challenge getting my brain wrapped around not only how to structure it but also how to do effective queries in MySQL.

CHILD CARE REFERRAL

Features

Status: rebuilt - inactive

Staff would interview families about their child care needs to match them up with providers that offer services that match the care setting to the children's ages, the parent's schedule, and other needs. Upon referring providers staff would print out letters of referrals given. Semi-annually, they may mail out satisfaction surveys to the parents. If parents called back within a quarter for more referrals it was considered a 'call back' which was recorded as such on one state report.

Tables

  • referral adult - call id, current id, date, name, contact info, funding , stats from & to time
  • referral child - birthdate/age of need, stats
  • referral - adult id, provider id

Accomplishments

  • Single page entry of the adult/family data and up to ten children greatly sped up data entry.
  • PDF form creation, Selectable language (i.e. English, Spanish) letter capability

Challenges

  • The first version relied on session variables too much for transporting data to other modules. The rewite removed the mess.
  • Comparison of care and other services needed vs providers care offered.
  • Multi-Lingual capability for parent letters.

Lessons

  • Most people will never notice what a mess your code may be on the inside, only that it works, (that it works well is most important to them) but you will be concenred bout the code in the long-run - especially when you need to fix something.

CHILD CARE SUBSIDY

Status: ~60% rebuilt - inactive

Features

Tracks families enrolled in Subsidized Child Care programs this includes, eligibility, children enrolled, services awarded, agreements for providers providing care, and the care both projected through the program year as well as the care used. Also share-of-cost family fees that are a portion of some of the services.

Staff entry and access is currently geared for a small agency serving rural areas.

Creates the necessary forms CD7617 NOA, CD9600, (partial na832/833/834/835), and 801A among other usage reports.

Generates child care reimbursement invoices for fiscal (& export file for direct importing into accounting system) family fee billing notices and DE542 and other financial reports for funding programs/providers.

Tables

  • family - family residence, mailing, start dates, id,s, etc. gen. family income
  • family log - changes in family fees, sick/bi days used, etc.
  • family-adult - adults role in this family (Head of Household, spouse, SO, other/exempt)
  • adult - adult's name, contact info, income, SSN
  • adult-activity - what sort of eligible activity the adult is involved in
  • adult-activity schedule - schedule of days/hours of eligible activity
  • (adult)business-school - name/address of school/employer for activity.
  • family-child - relationship in family (born/adopted/foster), foster family gorup, ccd initial date for child
  • child - name, birthdate, gender, langs, SSN, add adjustment factor
  • subsidy service - budget code, enrol date, reason for care, notes
  • subsidy service schedule - schedule of days and hours of overall needed care
  • subsidy agreement - agreement #, provider id, rates
  • subsidy agreement schedule - schedule of days and hours of agreed care
  • subsidy care - projected->actual, reimbursed care & family fee hours/days
  • subsidy family fee - family fee info id, budget code, hours/months/days, child
  • subsidy family fee info - family id family or foster fam id, notes
  • subsidy family noa - family id, effective date , issued date, action, distribution, actions
  • subsidy noa messages - pre-written NOA message templates
  • school calendar - identifies school
  • school year - identifies school year
  • school day - school day info (full/part for what grades, etc.)
  • subsidy fee - start date of fees
  • subsidy fee list - subsidy fee id, income level, rates
  • subsidy budget - funding code, agency code, description, director id
  • subsidy budget list - start/end of fy, fy budget amount, local accounting AP/AR codes.
  • subsidy rate ceiling - start/end date, FIPS code
  • subsidy rate ceiling list - ceiling id, care type, from-to age, ceiling rates

Accomplishments

  • This was a major undertaking it was a total re-design] on the system. A lot of system analysis o the old way and the paper portions not computerized as well as how to better integrate the different tasks. Part was changes in features as development progressed and another was seeing how to vastly improve the processes.
  • developed a more logical structure and language that described the 'big picture' of the subsidy data and tasks that worked with the report methods.
  • Combining the care projections with the care used along with the projected days of care (for family fees) with the actual days of care used. Family fee projections and resolution happened at the some time as care reimbursement projections and actuals for payment.
  • Successfully creating complete CDD9600, NOA, and 801A forms and reports

Challenges

  • In subsidized care programs, families can be very dynamic, adults can be related to multiple families (divorced but still listed for contact purposes), or families could become more alternative groupings (parents of same sex), and children could be in multiple families (parental sharing of children) so I created a middle linkage tables family-adult and family-child which ties the adults and children to the families as well as provides unique elements to their situation to that family (i.e. adult relationship, child relationship and sibling identifier if a foster family of siblings)
  • Learning that children could be families within families and possibly be responsible for family fees this moved family fee status to a separate tables outside of the family record.

Lessons

  • Something this complex requires good regular communication with key staff. Being able to let them see examples of new interfaces and how they would work, as well as to discover missing requirements beyond the original scope.
  • Made the language codes in database fields follow ISO standards and then had secondary tables to adjust to reports. Other reportable categories were encoded in a more standard method and then translated to specific report requirements.
  • Don't trust internal accounting's codes to stay the same - Separation of internal accounting codes and state/fed contract codes. The letter codes from the state rarely change and are progressive, where internal accounting codes changed vastly three or four times over the 24 years I worked there (as they switched accounting systems). so in WANDA I set up again a more legible letter code based on state federal/state codes and lookup tables (with historic records) to look up the appropriate local accounting code for the period in question.
  • Don't get too clever with historic records. To be clever when a child or adult left a family, I just removed the record instead of having some indication the current child record is inactive. It was great for 'now' queries but added big issues in resolving period of time queries (which was resolved with additional complex SQL). In future, just add a record status field (to indicate that element is closed) and save the hassle. Forget that, actually it seems the best solution for the application. Should stop second guessing things that I took a long time to work out.

DO PLACES

Features

Status: new - active

A community directory featuring local events, public places, businesses, services and groups.
Users are able to easily submit addition/change suggestions to the directory. Advertising revenue is based on enhacing entries to be more noticeable as well as improving search results.

Tables

  • place information - type, name, address, description, updated
  • place schedule - month, week, days, start/end time
  • products - producer id, seller id, description
  • event information - type, name, location, date, start/end time, updated
  • event participants - event id, participant id, role
  • contacts - event/place id, contact type, info, description
  • categories - event/place id, category list id
  • category list - category name, sub-category name, tags
  • messages - message id, title, message, updated
  • updates - update id, place/event id, update info, reason, contact, note, status
  • location list - city, county, location

Accomplishments

  • Good layout, simple, informative, uncluttered.
  • No cookies, the public facing part uses GET method to store data.
  • Simple form for user entry of additions/updates, I went through a few form revisions until I decided the easiest thing is a textarea with prompting of what info is needed. With that hands are (almost) never off the screen keyboard. There is more work on my side, getting the data in its necessary format, now it's probably less than would have been to correct bad forms submitted from users.

Challenges

  • Responsive Web Design - it seemed a-lot more complex than it it really is. Part of the complexity is when you are using some one-size-fits all. Layout control. KISS (Keep it Simple, Stupid) relives a lot of that.
  • Screen real estate is premium down on a 320 x 480 display, working on integrating advertising so it was worthwhile while not spoiling the mobile experience was a challenge.
  • Adding images, I wanted a way to not loose track of them. My solution is to name the image files with the place/event record id, along with an extension for thier purpose. If I need to title them (not in this instance) that would be done in the database. With having the ID in the image file name I can determine whether the source data still exists or not.
  • Location and area search - still need to have a better experince for such things. The reliability of the technology available to provide a location varies on phone platforms.

Lessons

  • Mobile interface design - screen real estate is minuscule and actions are just more direct on touch - pop-up lists dont work well, large forms with controls are a big pain on a touch device. So a new level of simplicity is needed.
  • Mobile web design - Start with mobile size first and chose - very complex and slick or very simple and nice. Most of the mobile web apps have fancy animations and rendering effects. To implement those you have to take into account multiple web platforms and lots of testing and what works on desktop may not work on mobile and visa-vera. Or you go old-school, strip out anything too fancy and use just basic HTML and CSS, may not have AJAX slickness to it but the end result (user gets their information) is just the same.
  • Spent too much time "shopping" for solutions for the new platform instead of just writing code. With all the shiny new web technologies developers can get stuck in that mode often.

FIXED ASSETS

Features

Status: ? - inactive

Tracked fixed assets for funded multiple departments in multiple locations in the organization. Provided reports such as current inventory status of items by location/program as well as depreciation schedules.

Tables

  • asset - ser#, barcode, description, delivery date, purchase price, value, location, disposition, primary owner, last updated
  • asset funding code - asset id, funding code, amount

Accomplishments

  • Providing tools to submit text lists of scanned codes to bulk validate/move/remove items on the list from portable units with barcode scanners.
  • Depreciation schedules that supported 1/2 fiscal years.

Challenges

  • Getting departments to use the system - fixed assets were always a low priority thing among the departments.

Lessons

  • As in subsidy; don't rely on internal accounting codes for funding sources, but use state/federal codes as a base and create a lookup tables to relate to whatever codes accounting was used at the time of purchase.
  • Never remove disposed assets, as some report will need a total or other historic information. Asset records marked as lost, stolen or disposed were retained but hidden on some searches and reports unless specified to be shown.

LIBRARY

Status: new - in development

Features

A lending library system for items beyond just books and media. Be able to acquire information about new books and media by ISBN lookup. Use barcode scanner to quickly check-out and check-in items.

  • patron - name, address, phone, email, init date, agreement date, statlist, id
  • item - name, author, manufacturer, barcode, filing code, isbn, condition, description, updated, age from age to, media type, location id, notes
  • checkout - patron id, item barcode, checkout date, duedate, returned date
  • reserve - patron id, title, filecode, media type, status, barcode, reserve date

Accomplishments

  • ISBN lookup, gathers data via google books.
  • DVD lookup via data from home theatre info's DVD list.
  • Item barcode, uses base 26 (A-Z): AAAAAA-ZZZZZZ easy to remember in 2 groups of three AAA-ADS.

Challenges

  • Would like to see how it could work with a tablet camera barcode reader...
  • Need to work up a document on creating a usable filing code. (not everyone "gets it" on how to do it right.)

Lessons

  • Dewey Decimal is a) old and antiquated, b) still proprietary c) not very useful in this context.

MAIL LIST

Features

Status: rebuilt - inactive

Postal Mailing List for agency publications to interested community members as well as for parents and providers in the subsidy and referral programs. Besides holding on-client data was able to pull in data from providers, and parents of the other programs to be included.

Tables

  • mail list - name, type of client, address, email, phone, init date, statlist, notes
  • also clients from subsidy and providers.

Accomplishments

  • Was able to do zip/location/adc sorting and reporting to gain reduced bulk mailing rates.
  • Labels were generated AS PDF files; by laying out the labels as they would be on a page unscaled and then prompting users to turn scaling off on PDF viewers when printing, this assured perfect label alignment, regardless of what model printer/or computer they were using.
  • Postnet barcodes on the lables was an option.

Challenges

  • Never got to the point to integrate USPS OneCode and sorting, though recent TCPDF libraries now have One Code capability.

Lessons

  • Duplication and errors happen, having sortable list tools help greatly in being able to quickly review entries with similar names, addresses, etc. to easily clean up list problems.

RESOURCE DIRECTORY

Features

Status: rebuilt - inactive

Fast and simple on-line resource directory. Inspired by the Referral Database (2003) by Joe Glass.

Tables

  • resource - name, short name, brief description, description, primary category, updated, notes
  • contact - resource id, info, type, description
  • resource category - resource id, category id
  • category - category, sub category

Accomplishments

  • Turned out rather well.
  • Used a single file for public side presentation.

Challenges

  • Creating an export text function to be used for a paper edition.

Lessons

  • Was a good lead-in toward mobile layout.