Final projects

1) Write a program that builds an api to use the cm config table. The API should be able to do the following:


a. ask for the value of any parameter for a particular configuration - input ins id & practice id & name of variable to return; output int error indicator; changeable i/o - variable value, level found

i. Users will ask for configurations by insurance ID/ Practice ID combinations.
ii. Check for system values: Find the S/Null/Null record (and there should only be one). If the requested value is not null, stop and return this value.
iii. Check for insurance values: Find the I/Ins/Null record (and there should only be one). If the requested value is not null, stop and return this value.
iv. Check for the config values: Find the C/Ins/Pract record (and there should be only one). If this record is not found, return an error (-1). If this record is found, return the value.

b. Ask for the value of a parameter for a particular exact config - input level, ins id, practice id and name of variable to return
c. Set the value of any parameter - input level, ins id, practice id, name of variable to update, new value.

i. Find an exact match on the full key or give error
ii. Update field

d. Add a new row that is empty except for keys: input level, ins id, practice id. . (Return error if you cannot update)
e. Delete an existing row (same parms as adding). (Return an error if you cannot delete)
f. Print all values for a config using the config hierarchy.

NOTE: Use the column name passed to access the column so you do not have to repeat work for different fields. You can look up the attributes of a column dynamically in the catalog so your program can handle the types properly.

2) Create a program that uses that API to create and change a Config level row. Just pick 10 fields to work with. Getting this far is not as critical.


3) This is more of an exercise, but an important one: Create a model in SQL 2008 over Lytec reports. Also create a simple report using that model. I need to give more details of what tables and fields are needed. It will basically be a model of all 0 level items, all non-0 level items, summaries of all payments against charges, all payments against bills. It will need all lookup tables also.

You can also just create the following queries:

1) Create a query to list all charages with their payments giving one line per sub 0 item and select by a list of insurance categories:

Name : first and last name
Billing : bill #
Service : Service Date 1
Billed : extended
CPT : cpt code
Note : [Billing Detail].[Detail Note]
Date Billed : get the maximum [date printed] from the history header for that bill. It is for an entire bill, not just a line
Prim Code : primary ins code
Primary ID : [billing header].[primary id],
Prim Paid : total all sub 1+ items for that bill/item. (transaction code's type is2 or 10
Sec Code : secondary code
Secondary ID : [billing header].[secondary id],
Sec Paid : total all sub 1+ items for that bill/item. (transaction code's type is 6 or 11
other : total all sub 1+ items for that bill/item. (transaction code's type is not 2 or 10 or 6 or 11)
balance : billed - prim paid - sec paid - other

2) Create a query that will provide a lookup of insurance codes with the insurance code names. This means list all the insurance codes and their names plus a row for all.

3) A summary of #1 for a range of service dates

HERE ARE FILES YOU WILL NEED:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection(VS.71).aspx

or

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx