Roy L. - "VB6/VBA/MS Access/MY SQL/MS SQL/HTML/ASP.Net/VB.Net - Freelance Programmer / Developer, Philippines"

Database has been partially designed to show required fields, provide a starting point for the designer and information that will be stored.  This is to be a client, payment, and appointment tracking database for small medical practice with a single physician.

 Due to the nature of the information being stored, security must be implemented.  Access must be permitted by username, with 2 users being administrators for user management.  Users will have access to 3 areas, patient info, appointment info, accounting.  If there are issues with this, please let me know.  We can isolate or simplify if necessary.

 Also, the client will be using Sharepoint 3.0 to access and view appointments in calendar format.  Currently they are using 2.0 and all appointments are only stored in Sharepoint.  We will be importing the current appointments from Sharepoint into this Access and will be using 3.0 to display these appointments from the db directly.

 If possible, we would like to use Active Directory authentication for the database also.  This is flexible based on difficulty.

 Information to be stored:
 Patient Information (Name, SSN, Address, phone, Zip)
 Appointments (Date, time, duration, reason, employee who made appt, last updated)
 Payments (Date made, amount, method, rcvd by)

 Reports:
 Appointments by date, date range, or patient
 Payments by date, week, month, year (sort by method option)
 Patient Statement (Select Patient to show charges & payments by date with balance)

• Business      logic information on GFCCOM program
  • The bakery has developed this program for tracking       mostly recurring orders from customers. (called standing orders) The       bakery makes and sells cinnamon buns with three different options: iced,       uniced and frozen. Recently they started selling something called mix       (not sure exact what it is).
  • There are two shifts of workers in the bakery. They       make buns in the morning and afternoon shifts. The program keeps track of       recurring orders and can tell the worker how many buns to make for each       shift.
  • The bakery also has a storefront to sell cinnamon buns       and other snacks. Each day, the numbers from the retail sales are entered       through the daily close process. The daily close must be done each night       (when the bakery is shutting down). Actual buns made for the day are also       entered to track waste.
  • Customers who have recurring orders can call in and       modify an order for a particular day. This is supported by the program       through a order that is generated when a day in the selected in the       calendar. When a day is selected, the program goes through all the       standing orders of all customers and turn the standing order into a real       order. This order can be changed as many times as possible but the       standing order (the recurring order) is not affects.
  • Sometimes, customers want to temporarily increase       their orders (during Christmas for example), this can be achieved by       adding a "temporary modification" to the standing order with a       start and end date. During this period, the program will use the number       specified in the temporary modification order.
  • The program prints out chits from the orders for each       day (a piece of paper the delivery driver carries that shows the customer       name, address and quantities being delivered) . The chit is printed on a       very small receipt printer.
  • You can change the standing order date by going to       Config -> System config, and change current standing order date.       Otherwise, the system will keep telling to do a daily close.

• Database      structure
  • There is no direct access to the database data       allowed. All access is done through stored procedures even simple       selects.
  • The database server used is Microsoft SQL server 2005.      
  • Most tables have clear names but here are the       important ones:
    • Orders: The order for customers for the day. They are        generated when a particular day is selected.
    • OrderDetails?: The detail of the order that includes the exact        quantities.
    • SO: The standing orders
    • SODetails: The detail of the standing orders
    • Mods: The "temporary modification" order
    • ModDetails?: The "temporary modification" order        details
    • Customers: Customer information
    • DailyClose?: The header information of the record for each day
    • DailyClosedDetails?: Details of the daily close
    • Parameters: System settings
    • Couriers: A list of delivery person who deliver the        order to the customers.
    • MenuItems?: A list of items the store is selling. More items        are planned
    • Routes: A list that can be changed through the UI to        give the delivery driver a delivery schedule

• Code      Architecture
  • Program is written in Visual Basic 6 and it uses       Crystal Report 8.5 for reports
  • Uses ADO to access MS SQL
  • Class module folder contains the methods to access the       database tables.
  • Main project file is GFCOM.vbp
  • Crystal Reports are stored in the root folder of the       project
  • GFC.ini contains the database server name and MS Sql       server instance name
  • Database name, user name and password is hardcoded in       DB_CONNPARAMETERS method in cldconnector

Error Catcher

Develop a memory resident application that can be started/stopped from the system tray that works as follows:

Specifications:

a. User presses the “Print Scrn” button on their keyboard.

b. An input box pop-up as below and ask the user for description of the problem.

c. User presses the send button and then following happens

1. Program will make a screen capture of current windows desktop [our desktop are currently 1920 x 1200].

2. Save the file as image file [JPG or TIFF] file.

3. Sends an e-mail to a  defined in an XML or INI file and attach the image file and user’s description of the problem.

Platform:
Assigned to developed standalone or a client/server SALES/INVENTORY/AR application and provide information on programs, modules, timetables, activities, staffing, project achievements and milestone, users training and project cost assessment. Evaluates and reevaluates programming languages to better suits the projects in relation to clients budget cost and with its existing hardware setup. The system provides a hierarchical level of security (up to row level) depending on the grants/privileges given to users for specific projects. It resides in an MS Access 2000 Database on trusted mode on Windows XP. Client application is being developed using Visual Basic 6.

Platform:
Assigned to developed standalone or a client/server SALES/INVENTORY/AR/AP/BILLING application and provide information on programs, modules, timetables, activities, staffing, project achievements and milestone, users training and project cost assessment. Evaluates and reevaluates programming languages to better suits the projects in relation to clients budget cost and with its existing hardware setup. The system provides a hierarchical level of security (up to row level) depending on the grants/privileges given to users for specific projects. It resides in an MS Access Database on trusted mode on Windows XP. Client application is being developed using Visual Basic 6.