Demarc
  

Resource Logic Client/Server Development Services

Corporate Resume



2013      Medical Records Database Migration
 
  An umbrella organization for Federally Qualified Health Clinics (FQHCs) is tasked with gathering certain information from each clinic in order to provided a consolidated Clinical Data Reporting system (CDR).  The clinic databases are varied, ones we've worked with include NextGen, Centricity, Sevocity, HEHR HealthPort, and others.  The initial tasking was to set up replication between the clinic databases and the CDR tranformation server cluster.  This involved setting up replication on SQL Server and MySQL host databases.

In some instances either clinics or their software vendors prefer to avoid operational modifications to their production systems.  Another approach is a 'no-touch' infrastructure for querying the tables during non-business hours, identifying those records that have changed, transmitting those to a target database, and then running the transforms for adding data to the reporting datasets.  This application is written in Visual Studio 2012 C#/Winforms using .NET connector objects and TableAdaptors.  Records are serialized, the serialized text is converted to character arrays, two checksums are run on the character arrays, and these are matched to the checksums associated with the same primary key from the last extraction cycle.  If one or both of the checksums are different, or no prior record with that primary key is found, the record is added to the transmission queue.

Once the records are resident in the host server cluster, various SQL Server stored procedures are run to migrate the data into the reporting rowsets.  One issue that emerged in this process involved the assignment of LOINC codes to lab results.  At various times for various reasons, such codes are not assigned by clinic staff, and it is desirable to find an algorithm for making proposed assignments after the fact.  Some transformation rules have been attempted, and this process remains a topic of operational research.
 
2011      Real Estate Lease Management
 
  A client has a portfolio of real properties located in many parts of the US, many of which are leased for terms of 1 to 5 years.  The application keeps track of the basic property record, the current lessee (if any), and the title documents associated with the property.  This application interfaces to an accounting system which keeps track of month to month payments and associated cash flow. This 'add-on' application deals with the document tracking and lease committments.  More specific features of this system are left vague to maintain client confidentiality.  The application is written in C# (Visual Studio 2010) and deployed using SQL Server Express 2008 R2.  The C# application includes reports using the (new) reporting tool included in VS 2010.
 
2010      Binary Flat File Extraction, Transformation, and Load
 
  A client is running programs that were written circa-1988 in QBasic.  This is a pure DOS character oriented application that saves data in binary flat files.  Our assignment is to characterize the files: record lengths, columns, column formats, and record relationships, incuding keys and pointers.

The first-pass solution is written in Microsoft Access VBA.  The 'TYPE' declaration, in this case, serves in the same role as the C++ 'STRUCT': it contains a record description composed of integers, fixed length strings, byte arrays, and other constructs.  Using this and 'OPEN #1 "filename" As Binary', it is possible to read these in and assign the individual fields to corresponding columns in an Access table.

Populating a carbon copy in relational database tables makes it possible to sort rapidly, which in turn makes it possible to 'audit' the records for various kinds of content, such as no values at all (in the case of integer fields, zeros in every record), or 'strange' (non-ascii) characters in text fields.  One thing became clear right away: the files had been initially saved in one format, and then migrated into a 'newer' format, and many of the columns defined in the older records were full of random garbage.

The client asked for a 'one click' import, where the business location is selected, and then a context menu option is selected to import the data.  This functionality is implemented entirely in a 'Winforms' C# program.  On selecting the location, the program creates a PostGreSql database; two schemas; around a dozen tables; and their associated functions for querying, inserting, and updating records; and then imports the data directly from the binary files.  The import folder usually contains around 100Mb of data, the resulting PostGreSql database is around 200Mb.

The application involves the recording of notes from phone conversations.  In some cases, these notes are in records that are interleaved with other records made by other agents in phone calls at the same moment.  The migration process consolidates these individual notes records into a memo field, using the rule that notes recorded within five mintes of each other made by the same agent with the same customer belong in the same record.
 
2010      MySQL PHP Web-Based Record Loader
 
  In many situations Internet hosts time out when users attempt to import large rowsets.  A good example is a zip code table, which has over 40,000 records.  On behalf of a client, I set up a system based on a C# program running under Windows and two PHP pages running on the ISP hosted site to bulk load SQL Insert statements.

The user is able to run a desktop program that automatically executes an arbitrary number of SQL statements on the hosted website.  In this particular case they were Insert statements, however they could be more complicated, in particular testing for the existence of a record and choosing to update the existing record if it already exists.

Variations on this system could query data out of databases such as Microsoft Access or Microsoft SQL-Server, or Excel spreadsheets or the Quickbooks application. Quickbooks and other accounting systems often store pricing information that businesses need to synchronize with their hosted web pages.  With the ability to run arbitrary SQL statements, the system allows the user to synchronize local databases with hosted websites at their convenience.
 
2009 - 2010      Oasis CRM Contacts and Quotes customization - 'Classic' ASP
 
  Hand coded Javascript drop down menu functionality for the eCommerce website.  Added customer enrollment form (out of box functionality allows user to create their own account).  Enrollment process is an application, which is reviewed by sales manager and assigned to a rep when the account is created.  Added specialized functionality for reporting 'meter readings': essentially a self-reporting mechanism for certain users.  Duplicated the purchase history functionality that exists within the internal system on the eCommerce site, so that a customer can place a new order by simply entering quantities next to items they've bought previously.

Improved performance of the Oasis CRM product in a number of respects.  Created indexes on a number of tables that do not have indexes, including the POLineItem.  PO display how occurs subtantially faster.  Limited the number of records returned in the Quotes list in order to reduce system-wide overhead.  In an environment with 20 users, this cuts a significant amount of delay.

Merged spreadsheets containing business leads with Oasis CRM database, particularly the Leads table.  Also merged tables originally captured from the ACT contact management system.  Identified and removed duplicate records, and mopped up improperly formatted telephone numbers and various other data quality problems.

Deployed SQL Server Express 2008, including Reporting Services and Report Builder 2.0.  Created and deployed demonstration reports, then gave sales manager walk-through on how to create or maintain reports.

Modified KnowTia Oasis CRM system to improve user convenience.  Built on previous user enhancement that listed product purchase history per customer.  Second enhancement provides list of historical products as a 'product search' result set.  The sum of these two features is that a customer can see what they've ordered historically, and reorder simply by filling in quantity fields next to those items.

System also differentiates between Inventory Items, Service Items, and line items that contain instructions or messages.  Products are all that are displayed during product searches, and service items are all that are displayed during service searches.

Added Customer Specific Discount, which allows applies fixed percentage discounts on up to six product categories.

Coded Credit Card Instructions drop-down for allowing selection of 'Process On Ship', 'Authorization Required', 'COD', or 'On Account' options.  The 'Process on Ship' report produces a list of credit card transactions that are to be run once all the day's orders are entered.
 
2008 - 2009      Winforms/Oracle 10.2 C#, ASPX Applications
 
  Develop a collection of applications using Visual Studio 2008, C#, and Windows Forms to maintain various tables in Oracle 10.2. These include a process for merging information captured from OCR forms into a client master database; a budgeting system; an Electronic Data Interchange (EDI) system for ordering books; and a maintenance facility for maintaining test questions in an on-line exam.

Create table in Oracle to store graphics images, and develop C# application for importing images to database, then retreiving them by keywords.  Application is used to print postcards merging a photographic face with the addressee and appropriate narrative.

Duplicate a registration website oriented toward North American users and recompose the page contents in Spanish (with language assistance as appropriate) for Latin American users.
 
2008 Access-Based Demand Reports from SQL-Server
 
  This system serves the needs of a company that builds large-scale projects of the nature of ships or bridges.  Much of the accounting for project cost is either labor or purchased components, such as steel, piping, specialty equipment, electrical, or HVAC.  The reports track 'Open Purchase Orders', 'Past Due Purchase Orders', 'Received but not Invoiced', and so on.  These reports can be presented in a number of orders, with extensive filtering if desired.

Some of the maintenance on this system is on the OSAS Traverse application.  Much of the form modification is on the Purchase Order module in particular.  The reports, while accessing the Traverse SQL database, are not incorporated into the Traverse application, and therefore do not require a free license.  That being the case, they do not run under the security access rules either.

The accounting and job costing systems are vertical market applications for this particular industry.  The custom reports address the more specific requirements of this particular business.

This system extracts data from SQL-Server 2005 tables, and produces reports through Access (2003) .ADPs.
 
2006 - 2008      Mainframe Language (Natural) Legacy Migration Tool
 
  A federal government agency has a large number of (IBM Mainframe) 3270 terminals (or PCs emulating these terminals) located in district offices throughout the country and a number of foreign countries. This system is written in Natural, using the ADABAS (Software A.G.) back-end database. The objective of the agency is to migrate this application to the Internet using Windows-based web servers, SQL-Server 2005, and Visual Basic .NET (2005).  A further complication is that the user interface must remain identical to the 3270 display and keyboard arrangement, which means the implementation of a 25 x 80 character 'green screen' using Internet Explorer.

The 3270 functionality is implemented using Javascript and AJAX.

The mainframe source code files are transformed using a system developed primarily in Microsoft Access and T-SQL. There are three basic exports from the ADABAS/Natural system that are imported to the migration tool: schemas (table definitions), screen forms (3270 layouts), and program code.  These files are imported into SQL-Server via Microsoft Access, then transformed into a number of outputs:
  • SQL Table Creation Scripts
  • SQL DTS Import Packages (for migrating data from CSV-like text files)
  • Stored Procedure Generator (Select, Insert, Update, Delete operations on each table)
  • Visual Basic class definitions (memory equivalent of the table layout)
  • Visual Basic collection definitions (for returning groups of records)
  • Visual Basic data services (methods for querying and parameterizing data from and to the database)
  • Visual Basic screen classes for each displayed form
The individual programs are converted by programmers. Other elements of the migration system help expedite the hand coding, by making it easy to locate dependent structures stored in 'include' files.

The Migration tool uses Access as an import and export mechanism and T-SQL in SQL Server 2005 to do the transformations. Many of these stored procedures are thousands of lines long.

The target application is a mix of ASP .NET, Visual Basic .NET, AJAX, JScript, VBScript, and console Visual Basic. Some elements of the original application are run in batch overnight, and have no console interface. In the new system, these are dispatched through a scheduler.
 
2005 - 2006 Management Console for Embedded Controller Monitors
 
  The product is a system for monitoring computer projectors in schools and businesses.  A box containing an embedded controller is physically wired to a projector and acts as a node on a campus LAN.  If the projector is physically separated from controller the controller immediately issues an email to security or other monitoring party.  The controller also monitors lamp life, input selections (whether made through the controller or via a handheld remote), and other settings that are model dependent.

The management console presents information on dozens or hundreds of these monitors on a single workstation, allowing a tree-view drill-down of campus, building, area, room, and device.  This console can issue commands to turn off projectors, select inputs, or reset alarms.  It also collects projector use statistics for incorporation in a database.  This database is used to produce reports on collective projector use.

This system is written in C# (.NET) 2003 using SQL Server 2000 (or MSDE) as the backend database.
 
1999 - 2006 Frozen Vegetable Packing Plant - Ordering and Production Control
 
  Frozen vegetables are packed by mixing and bagging anywhere from one to twelve separate vegetables.  Vegetables are stored in totes (shipping cartons containing about 1000 pounds of product, typically) and then metered onto a conveyer with other vegetables, mixed, and poured into bags (for retail sale) or cases (for use in restaurants).  The ordering and production control application schedules production runs, orders product from the cold storage warehouse, follows up production with actual pounds packed, and analyzes production figures to identify waste or delay.  A quality assurance component is used to record QA sample records taken during the production process.  Barcoded labels are printed and applied to incoming raw material as it enters cold storage, and pallets of finished goods as they are produced.

This system is written in Access/2000 using SQL Server as the backend database.  Microsoft Excel is used for import and export of pricing, production statistics, product component revisions, and auditing worksheets.  The system also exchanges data with Quickbooks to drill-into various reporting options.
 
2000 - 2001 Insurance Broker Customer Services Website
 
  The insurance broker's clients have locations throughout the United States and Canada. The first service brought online is a request for rating form, in which the driver's name, address, license number, etc. are entered into the database via a web form for rating by the agency.

Once the driver is rated, divisional offices review the scores and other relevant information to approve the hire.

This becomes a database for tracking vehicles and drivers, since the review process requires access to existing employee, and therefore vehicle, information. This becomes an 'added benefit' service from the broker to the client.

This system is written in HTML and Active Server Pages (ASP), Javascript (Jscript), and IIS. The backend database is SQL-Server 7.0. Frontpage is used as the development environment.
 
1998 - 1999 Air Force Tech Order Warehouse
 
  Migrate Technical Order (TO) warehouse application from Foxpro/W 2.6 to Visual Basic 6, preserving existing SQL Server table definitions and stored procedures. Write ActiveX embedded control for Intermec 4100 Label printer using classes and collections (OOP). Rewrite Foxpro reports using the Microsoft ActiveX report designer in conjunction with existing stored procedures. All database connections are made via RDO (Remote Data Objects) or ADO (ActiveX Data Objects).
 
1997 - 1998 Research Work-in-Process Tracking
 
  Migrate HWIS Application (Laboratory Research Work in Progress Tracking) from Oracle Forms 3.0 (character based) to Developer/2000 (Forms 4.5) (GUI). Set up mail notification service in Oracle using Microsoft Exchange via OLE. Specific assignments were related to contracts, budgets, and funding transaction tables and forms.
 
1994 - 1997 Research Requirements and Performance Analysis Tracking
 
  Design and implement Air Force Research Planning and Progress Tracking System in Visual Basic using Oracle 7 database server. Work with TPIPTs (officers responsible for tracking research efforts) to translate user requirements into data structures and interactive forms. VB application includes context sensitive help, Oracle to Access table migration, and memo field cut and paste between program and word processors.

Product Experience





Operating Systems and Local Area Networks
Windows Server 2008 R2, Windows Server 2003, Windows Server 2000
      Windows 7 Professional; Windows XP NT Workstation 4.0, NT Server 4.0
Windows 95, Windows 98 MS-DOS Version 3.3, 5.0, 6.x
   
Databases
Microsoft SQL Server 7.0, 2000, 2005, 2008 R2, 2012 Microsoft Access '95, '97, 2000, 2003, 2007, 2010
Oracle 7.3, 10g.2 dBase IV 1.5, Foxpro 2.0, 2.6/W
PostGre SQL 9.1, MySQL SyBase SQLAnywhere 12.1
   
Programming Languages
Visual Basic for Applications (VBA), Visual Basic 6
Visual Studio 2003, 2005, 2008, 2010, 2012
Active Server Pages (.ASP), ASP.NET (.ASPX) National Instruments LabView 5.0
   
Spreadsheets
Microsoft Excel '97, 2000, XP, 2003, 2007, 2010 Lotus 2.x, Lotus for Windows
   
Graphics Software
Corel Draw 3 through x3 ABC Flowcharter 2.0
Microsoft Visio
   
Communications Software
Attachmate 3.30 (Windows) /w IPX Gateway
   
Report Writing Tools
SQL Server Reporting Services Visual Basic 6 ActiveX Reports
Crystal Reports 6.0, 8.0 R&R Report Writer
   
Business Applications Software Programming Experience
   
Manufacturing Production Control Insurance Customer Service
General Ledger/Fixed Assets Accounts Payable
Order Entry/Sales Analysis Professional Time Accounting
Medical Billing/AR Project Tracking/Work in Process
Oil and Gas Joint Interest Billing and Revenue Distribution

Please eMail us at info@resourcelogic.net