Online Surveys / Administration for University of Colorado Cosmetic Specialists with ASP.NET and SQL Server

In this post I will detail a project that was created for the University of Colorado Cosmetic Specialists at UCH Hospitals in Aurora, CO.  The UCH CS needed a series of forms to capture data from patients submitting information online.  They also needed a way to view this information and perform reporting functions.  This project uses a SQL Server 2005 backend database with Stored Procedures to secure data and comply with HIPAA patient security requirements:  http://www.hhs.gov/ocr/privacy/

Here is a view of the homepage:

http://cucosmetics.com

This is the Appointment page.  On this page a person can submit an online request for an appointment with one of the Cosmetic Specialist doctors.  When this request goes through, administrators will be notified by email and a record will be written in the database for future reference through the Administration page.

This is the Pharma Survey page.  Companies can fill out this survey to receive information on products / research.  There is also a similar page for people to fill out and receive information.  Past records from both surveys are available for viewing in the Administration page.

This is the Administration page where all data and administrative features are centralized.  Questions for all surveys can be edited and changed in this section.   Data from past responses can also be viewed.  Detailed reporting and search capabilities are included for all candidate / company responses.  All data is stored in a SQL Server database.

Administration for survey questions / web order.

Candidate Search / Reporting

As with all software and websites we create, this project can be modified to suit your needs. Call or request a quote here: http://www.spagnolidesign.com/Contact.aspx

SkyBlue Software Detail Part 1 - Inventory Search / Product Editor / Publish Que

Introduction:

I wanted to start by detailing a custom software project I have been working on for the past year or so for Vickerey.com in Boulder, CO.  The package, which has come to be known as SkyBlue, works in conjunction with a software package called Stone Edge Technologies Order Manager.  Stone Edge is used to import and manage orders from an online store.  In the case of Vickerey, Yahoo!Store, which is the backbone of their website.

Aside from importing orders, Stone Edge does a multitude of things such as managing customers, payment processing, inventory, purchasing, receiving, picking / packing, shipping, returns and exchanges, and much more.  According to their website it is used by over 2,500 retailers and is compatible with over 40 shopping cart systems.

http://www.stoneedge.com/

SkyBlue is written and created in Microsoft Access with VBA, the same language that Stone Edge Order Manager is written in.

Purpose:

Stone Edge Order Manager has many functions and can do a lot for a business but like every software package, it is by no means perfect.  This is why Vickerey decided to create SkyBlue.  Vickerey had a desire to capture a large amount of data about their products to be used on their website.  The key to Vickerey's success is constantly changing content and SkyBlue provides the tools to allow for these changes.

SkyBlue provides a robust product editor that works with Stone Edge by using Stone Edge data as well as providing its own tables to store additional information about inventory.  The product editor features the ability to edit hundreds of attributes about a product from Headlines, captions, pricing, images and even quantity on hand.

SkyBlue also has the ability to update practically all information about a product in a Yahoo!Store, including images, completely changing the content of a website through the upload of .csv files that it outputs.  This feature saves huge amounts of time for users of any Yahoo!Store.

Features Rundown:

Along with capturing additional inventory data and outputting .csv files, SkyBlue also provides:

Inventory Search / Product Editor / Publish Que - These functions will all be detailed in this section.  The following will be detailed in Section 2.

Sales Information -  Sales Information provided in a form that allows searches by Date, SKU, Supplier, Category and Season.  The Sales Detail form provids a SKU Sales breakdown and totals for QOH Cost, Sold Price, Sold Cost, Gross Margin, Units Order and Units Shipped

Sales Tax Form - Displaying total sales and a complete breakdown of all State sales tax categories available for a searchable period of time.  All tax rates are adjustable.  The form also displays amounts paid in each category through orders and the calculated  amount owed based the state tax table.

QOH Snapshot - Displays a detailed view of all current Quantity on Hand.

Stock Perfection - Alerts users as to which SKUs have come in-stock and have went out-of-stock

Yahoo Perfection - Downloads a current .csv of the available inventory listed on the website from the Yahoo!Store and compares Stone Edge inventory data to this import to determine discrepancies in pricing and QOH.

Functionality / Features:

Main menu / Startup Screen:

The tabs are available because Vickerey uses two company files in Stone Edge, one for Vickerey.com and another for moleskineus.com by which they sell paper products.  SkyBlue allows seamless editing of the data in both companies.  There is also an Admin tab with restricted access to Sales Tax Information, Sales Reports and the Publish Ques which output the .csv files.

From the main menu a user can click the Product Editor button which brings you to the initial product search screen:

Inventory Search:

As you can see, in the top section, there are many different ways to search for products, these fields correspond to both Stone Edge and SkyBlue data.  Once a product is found, the user double clicks the product in the grid to bring them to the Product Editor screen.

 

Product Editor - General Tab:

This is the first tab for the product editor, fields are saved in both Stone Edge tables as well as SkyBlue tables seamlessly without the user knowing.  The General Tab includes general product information that can be found on the website such as Headline, Caption, Style Pts, etc.

The Product Editor in SkyBlue works similar to a website where data is first loaded to the screen from the database, edited, and saved back to the database when the 'Save' button is hit. SkyBlue rarely uses "Bound" data throughout the program, which is a very efficient technique in preventing users from accidentally changing data that they do not intend to change.

Product Editor - Pricing Tab:

The Pricing Tab is used to store pricing information about a Parent part and all of its sub-components.  Data is stored in the grid as a temporary table and saved back to Stone Edge when formatted the way the user intends.

Product Editor - Sizing Tab:

The sizing tab is used to edit a product's Sizing information.  Users can create their own custom chart for a SKU or use a pre-defined Brand Size chart that is shared by multiple SKUs.  I will skip a description of the Paper and Bath & Spa tabs.

Product Editor - Images Tab:

The images tab provides a visual display of all the images associated a SKU.  Each thumbnail can be clicked and opened in Internet Explorer.  Images are stored in a series of folders based on if they are approved to be uploaded to the website or not.  Tasks can be assigned per image.

Product Editor - Yahoo Tab:

The Yahoo Tab is where a user can enter information about all the sub SKUs that are associated with a Parent SKU.

Product Editor - Tasks:

You will notice that every tab has a section for Tasks.  Users can create a task with a Task Status which corresponds to each Tab in the SkyBlue Inventory Editor.  Tasks are used to communicate which SKUs need to be updated on the website through the Publish Que via a .csv file.  When a Task has a status of Approved, it will appear in the Publish Que and can be exported as a .csv.

Here is a detailed view of the Task Form:

Users can change status, delete and make notes about a particular task.  Each Task is related to a Parent SKU.  Tasks with a status of Approved will appear in the Publish Que awaiting a .csv upload.

Task Search:

You can also search for Tasks and open the SKU associated with the Task in the search Screen.

Publish Que:

The Publish Que is the final piece in the life cycle of an inventory part.  Here an administrator can export a .csv file with all the updated data used to change the content on the website.  The Publish Que only displays Tasks with a status of Approved allowing users to change content as much as they want until the content is ready by assigning an Approved Task to a part.

Here is an example of an exported .csv file from the Publish Que, opened in MS Excel:

This file can then be uploaded to Yahoo to directly change the content on the website.

Here is a product page after update:

Category Editor


SkyBlue also has the ability to edit entire Categories of items within this form:

Attributes from the Category Editor can be edited and used to update the website just like a product.

After editing a Category and uploading the .csv file to the Yahoo Store the new Category information will be updated on the website.

SkyBlue Software Detail Part 2 - Additonal Features / Modules

This section will provide information on additional features in SkyBlue, aside from the Inventory Editor.

Sales Tax Module:

The Sales Tax Module allows users to see the product sold within a specific period of time and the taxes both paid and calculated within this period of time. The tax table is adjustable and the results can be saved for historical archiving.

This module can save enormous amounts of time, before Vickery had to keep spreadsheets of all their Colorado orders in order to pay their state sales tax. Now it is calculated in less than a minute.

Stock Perfection Module:

Stock Perfection automattically displays all the SKUs that have recently come in-stock or have gone out-of-stock by category. There is also a feature for users to add SKUs to a suspect Que that they believe may be in or out-of-stock.

Yahoo! Perfection Module:

Yahoo Perfection works by downloading a .csv of the current inventory from the website and determines if compares this data with Stone Edge data.  Yahoo Perfection notifies users of a set of conditions:

    1. Whether a SKU is marked as Orderable on the website but is Out-of-Stock

    2. Whether there is current QOH but the SKU is not orderable on the website.

    3. Whether a SKU is on the website but not in Stone Edge

    4. Price mismatches between the website and Stone Edge data

QOH Snapshot Module:

The QOH Snapshop displays current QOH quantities rolled up to the Parent SKU level as well as totals for QOH. There is also tabs for grouping SKUs up to the supplier level. QOH Snapshot also features the ability to Write-down SKUs and includes a sum of Write-down costs.

Sales Report Module:

The Sales Report allows users to view current sales based on a date range, SKU, Supplier or Season.  A breakdown per order appears in the grid below and the totals appear in the text boxes above.