Squishdot Investigation

__init__.py
#register the Squishdot Site class
initialize(context)
--registers class SquishSite
--manages add Squishdot
#Load addSquishdotForm from disk

#method to construct a Squishdot site
manage_addSquishdot(self, id, ,,)
"""Create a Squishdot Site"""
#load images from disk and make them accessible
#make the mailhost list method accessible.

Lessons Learned Doing ACPG Unit Tests

Here are just a few thoughts on what I learned writing ACPG unit tests.
My perception is that I spent a lot of time spinning wheels at first.
However, following these rules as I learned them increased the speed of
writing tests by an order of magnitude.

If you think it worthwile I could go over this in less than two minutes
at a morning meeting.

Lessons Learned In ACPG Regression Testing

Note: "Target" refers to the code being tested. Test classes test the
target code.

(1) When testing Oracle packages create one test class per target Oracle
package; when testing .NET code create one test class per target .NET
class.

(2) Use the Nunit or MbUnit feature which runs all regressions
sequentially with a single click.

(3) Avoid elaborate test setups in which the test classes duplicate the
insertion and creation target objects in the module being tested.
Elaborate test setups can take as long to write as the target code being
tested due to the fact that the setup has to do the same thing as the
targetcode.

Whenever possible use the target objects' own procedures to setup test
data for test objects. This means finding out the order in which data is
entered and deleted. First create test objects for target procedures
that create or insert data. Then create test objects for target
procedures that display, delete or archive data.

(4) If test classes create data make sure that they use any Oracle
sequences which exist for that purpose.

(5) Although it would be desirable to use VB.NET transactions and
rollback to remove effects of testing this may not be possible. Many
Oracle objects include commits inside their procedures. Thus they cannot
be rolled back.

(6) Do not create special scripts that call the test classes. These are
hard to maintain, as it is easy to forget which script calls which test
classes. Rather, each test class should call whatever other test classes
it needs.

(7) Place connection info at top of each test class rather than
embedding connect strings inside class member functions. Similary, place
reused parameters (e.g., registry_id) as public parameters within each
test class.

(8) When creating VB.Net variables to hold database columns make sure
that the attribute is the same datatype as the database column. If the
attribute needs to be converted to a string for use in a SQL statement
then use the toString() function. Do not convert Integers to strings
when retrieving them.

(9) Give the names of test class attributes a prefix so that the names
don't accidentally get overridden by local variables inside member
functions. To create the names of test classes and test class member
functions prefix the word "test" to the corresponding names of Oracle
packages and procedures.

(10) IMPORTANT: The first thing to do is to create stubs for all test
classes and test class member functions using the naming convention in
(9) and the connection data in (7). Have each member function of each
test class open up and close a connection.

(11) Each test class is its own Nunit or MbUnit . Each
member function is its own .

(12) MbUnit and Nunit are almost identical so it doesn't matter which is
used. However, GreaterThan in MbUnit is called Greater in Nunit.

(13) Make certain that if an Assertion fails the connection is already
closed. Otherwise as soon as one test class fails all subsequent test
classes fail due to their attempting to open an existing connection.

(14) It doesn't matter much whether test classes are written in VB.NET
or C#. ReSharper and other tools can convert from one to the other. The
functionality is in the .NET objects. Those don't change when changing
.NET languages.

(15) IMPORTANT: In a perfect world each test class could run
independently, so that if one test class throws an assertion that fails
other test classes can still run. In the real world writing test classes
in this manner means that the test classes take as long to write as the
target objects being tested. Rule (3) means that test classes are not
independent.

Example Of Doing Oracle NUnit Tests Using VB.NET

Imports System
Imports Microsoft.VisualBasic.Strings
Imports Microsoft.VisualBasic.FileSystem
Imports System.IO
Imports System.Data
Imports System.Security
Imports System.Data.Odbc
Imports System.Security.Principal
Imports System.Security.Permissions
Imports System.Threading
Imports NUnit.Framework
Imports System.Data.Common
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports Oracle.DataAccess.Client ' ODP.NET Oracle managed provider
Imports Oracle.DataAccess.Types ' VB.NET, Oracle data types for parameters
Imports System.Text

'''''''''''''''''''''''''''''''''''''
'Test classes written by Jonathan Mark
'NG
'May-June 2006

'Work suspended on 6/12/06. Comments written 6/13/06
'On 6/13/06 changes to the Oracle database invalidated the following three tests:

' testProcess_Encounter--The parameter enc_num was removed from ACPG_REMINDER_PKG.PROCESS_ENCOUNTER.
' This change causes the test to fail when the Process_Encounter Procedure is called. To
' correct this error remove the enc_num parameter from the test

' testProcess_Medcin_ids--ACPG_REMINDER_PKG.PROCESS_MEDCIN_IDS was removed from tohe ACPG_REMINDER_PKG.
' This change causes the test to fail. To correct this error remove this test from this test module.

' testAutoRegisterOrders--The parameter enc_num was removed from ACPG_REGISTRY_PKG.PROCESS_MEDCIN_IDS.
' This change causes the test to fail when the Process_Medcin_ids procedure is called. To
' correct this error remove the enc_num parameter from the test.

' testDeactivate_Patient_From_Reg()-- This test failed as of 6/12/06.

' testDeleteReminderFromPatient -- ACPG_PROTOCOL_ITEM_PKG.DELETE_REMINDER_FROM_PATIENT does not delete
' from the acpg_protocol_item_medcin_id table.












''''''''''''''''''''''''''''''''''''''
Public Class testACPG_PATIENT_PKG
Dim oradb As String = "Data Source=(DESCRIPTION=" _
+ "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dev1cdr)(PORT=1521)))" _
+ "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PROD.World)));" _
+ "User Id=acpg;Password=acpg;"
Dim myOraConn As New OracleConnection(oradb)
Public Rid As Integer
Public uniqueID As Integer

Public Sub testAdd_patient_to_registry()
myOraConn.Open()

Dim mySetup = New testACPG_PROTOCOL_ITEM_PKG


mySetup.testAdd_protocol_item()


Dim result = "Connection Opened"
Dim myCommand As OracleCommand = myOraConn.CreateCommand()



Dim longTime As Long = Now.ToFileTimeUtc()
uniqueID = longTime Mod 1000000000
Dim uniqueIDStatus2 = uniqueID + 1

Rid = mySetup.RegistryID


myCommand.CommandText = "Insert into acpg_registry_patient(Registry_id, unit_number, " _
& "status, date_added, added_by_ncid" _
& ") VALUES (" _
& Rid.ToString() & "," & uniqueID & ",1, sysdate,1)"
result = "About to execute for unit_number = " & uniqueID


myCommand.ExecuteNonQuery()

myCommand.CommandText = "Insert into acpg_registry_patient(Registry_id, unit_number, " _
& "status, date_added, added_by_ncid" _
& ") VALUES (" _
& Rid & "," & uniqueIDStatus2 & ",2, sysdate,1)"
result = "About to execute for unit_number 2 "

myCommand.ExecuteNonQuery()

result = "Executed"


Dim sql As String = "ACPG_PATIENT_PKG.ADD_PATIENT_TO_REGISTRY"
Dim cmdAddRegistry As New OracleCommand(sql, myOraConn)

Dim cmdARHigherUnitNumber As New OracleCommand(sql, myOraConn)


'Find out the next value of ACPG_Registry.

cmdAddRegistry.CommandType() = CommandType.StoredProcedure
cmdAddRegistry.Parameters.Add(New OracleParameter("p_unit_number", OracleDbType.Int64)).Value = uniqueID
cmdAddRegistry.Parameters.Add(New OracleParameter("p_registry_id", OracleDbType.Int64)).Value = Rid
cmdAddRegistry.Parameters.Add(New OracleParameter("p_user_ncid", OracleDbType.Int64)).Value = 1
cmdAddRegistry.ExecuteNonQuery()

cmdARHigherUnitNumber.CommandType() = CommandType.StoredProcedure
cmdARHigherUnitNumber.Parameters.Add(New OracleParameter("p_unit_number", OracleDbType.Int64)).Value = uniqueIDStatus2
cmdARHigherUnitNumber.Parameters.Add(New OracleParameter("p_registry_id", OracleDbType.Int64)).Value = Rid
cmdARHigherUnitNumber.Parameters.Add(New OracleParameter("p_user_ncid", OracleDbType.Int64)).Value = 1
cmdARHigherUnitNumber.ExecuteNonQuery()

result = "Records are written to database."

Dim StatusSQL As String = "SELECT status as myStatus from acpg_registry_patient" _
& " where unit_number In (" & uniqueID & ", " & uniqueIDStatus2 _
& ") Order By unit_number"

Dim cmdStatusID = New OracleCommand(StatusSQL, myOraConn)
Dim statusds As New DataSet
Dim statusda As OracleDataAdapter
Dim statusdt As DataTable
statusda = New OracleDataAdapter(cmdStatusID)
statusda.Fill(statusds, "R")
statusdt = statusds.Tables("R")

Dim myStatus As Integer
'Earlier in this class the lower of the two unit numbers had a status which
'was set to 1. The higher of the two unit numbers had a status which was set to 2.

myStatus = statusdt.Rows(0).Item(0)
Assert.AreEqual(1, myStatus, "Status remained 1")
myStatus = statusdt.Rows(1).Item(0)
Assert.AreEqual(1, myStatus, "Status not changed to 1")


myOraConn.Close()

End Sub
Public Sub testDeactivate_Patient_From_Reg()

'add patient to registry
testAdd_patient_to_registry()
myOraConn.Open()
Dim myOracleTransaction As OracleTransaction = myOraConn.BeginTransaction()
'remove patient from registry
Dim sql As String = "ACPG_PATIENT_PKG.DEACTIVATE_PATIENT_From_REG"
Dim cmdDeactivateRegistry As New OracleCommand(sql, myOraConn)


cmdDeactivateRegistry.CommandType() = CommandType.StoredProcedure
cmdDeactivateRegistry.Parameters.Add(New OracleParameter("p_unit_number", OracleDbType.Int64)).Value = uniqueID
cmdDeactivateRegistry.Parameters.Add(New OracleParameter("p_registry_id", OracleDbType.Int64)).Value = Rid
cmdDeactivateRegistry.Parameters.Add(New OracleParameter("p_user_ncid", OracleDbType.Int64)).Value = 1
cmdDeactivateRegistry.ExecuteNonQuery()

'result = "Records are written to database."

'test that patient is gone
Dim checkSQL = "select count(*) from ACPG_REGISTRY_PATIENT WHERE registry_id = " _
& Rid.ToString() & " AND unit_number = " & uniqueID.ToString()

Dim cmdCheckRegistry As New OracleCommand(checkSQL, myOraConn)

Dim CRds As New DataSet
Dim CRda As OracleDataAdapter
Dim CRdt As DataTable
CRda = New OracleDataAdapter(cmdCheckRegistry)
CRda.Fill(CRds, "R")
CRdt = CRds.Tables("R")

Dim result = CRdt.Rows(0).Item(0)
Assert.AreEqual(0, result, "Count remaining is " & CRdt.Rows(0).Item(0) _
& " for registry id = " & Rid.ToString & " unit number = " & uniqueID.ToString())

myOracleTransaction.Rollback()
myOraConn.Close()
End Sub

Public Sub testGet_Patient_QResponses()


myOraConn.Open()

'Get an existing RegistryID
Dim getRegSQL = "select registry_id, unit_number, count(*) from " _
& " acpg_patient_qresponse_vw group by registry_id, unit_number"

Dim cmdReg = New OracleCommand(getRegSQL, myOraConn)

Dim Ridds As New DataSet
Dim Ridda As OracleDataAdapter
Dim Riddt As DataTable
Ridda = New OracleDataAdapter(cmdReg)
Ridda.Fill(Ridds, "R")
Riddt = Ridds.Tables("R")
Dim qCount As Integer = 0
Dim qRid As Integer = 0
Dim qUNum As Integer = 0
If (Riddt.Rows.Count() > 0) Then
qRid = Riddt.Rows(0).Item(0)
qUNum = Riddt.Rows(0).Item(1)
qCount = Riddt.Rows(0).Item(2)
End If



'Check that row returns'
Dim checkSql = "ACPG_PATIENT_PKG.GET_PATIENT_QRESPONSES"
Dim cmdCheck As New OracleCommand(checkSql, myOraConn)


'Find out the next value of ACPG_Registry.

cmdCheck.CommandType() = CommandType.StoredProcedure
cmdCheck.Parameters.Add(New OracleParameter("p_unit_number", OracleDbType.Int64)).Value = qUNum
cmdCheck.Parameters.Add(New OracleParameter("p_registry_id", OracleDbType.Int64)).Value = Rid
Dim prm = New OracleParameter("curVar", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output)
cmdCheck.Parameters.Add(prm)
cmdCheck.ExecuteNonQuery()

Dim oraref As OracleRefCursor = cmdCheck.Parameters("curVar").Value
Dim dr As OracleDataReader = oraref.GetDataReader()


Dim numberOfRowsInReader = 0
While (dr.Read())
numberOfRowsInReader = numberOfRowsInReader + 1
End While
myOraConn.Close()
Assert.AreEqual(numberOfRowsInReader, qCount, "Get_Patient_QResponses Failed")
End Sub
Public Sub testGet_Patient_Reminders()
myOraConn.Open()


'Get an existing RegistryID
Dim getRegSQL = "select unit_number, count(*) from " _
& " acpg_patient_reminder_vw group by unit_number"

Dim cmdReg = New OracleCommand(getRegSQL, myOraConn)

Dim Ridds As New DataSet
Dim Ridda As OracleDataAdapter
Dim Riddt As DataTable
Ridda = New OracleDataAdapter(cmdReg)
Ridda.Fill(Ridds, "R")
Riddt = Ridds.Tables("R")
Dim qCount As Integer = 0
Dim qRid As Integer = 0
Dim qUNum As Integer = 0
If (Riddt.Rows.Count() > 0) Then

qUNum = Riddt.Rows(0).Item(0)
qCount = Riddt.Rows(0).Item(1)
End If



'Check that row returns'
Dim checkSql = "ACPG_PATIENT_PKG.GET_PATIENT_REMINDERS"
Dim cmdCheck As New OracleCommand(checkSql, myOraConn)


'Find out the next value of ACPG_Registry.

cmdCheck.CommandType() = CommandType.StoredProcedure
cmdCheck.Parameters.Add(New OracleParameter("p_unit_number", OracleDbType.Int64)).Value = qUNum
Dim prm = New OracleParameter("curVar", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output)
cmdCheck.Parameters.Add(prm)
cmdCheck.ExecuteNonQuery()

Dim oraref As OracleRefCursor = cmdCheck.Parameters("curVar").Value
Dim dr As OracleDataReader = oraref.GetDataReader()


Dim numberOfRowsInReader = 0
While (dr.Read())
numberOfRowsInReader = numberOfRowsInReader + 1
End While
myOraConn.Close()
Assert.AreEqual(numberOfRowsInReader, qCount, "Get_Patient_Reminders Failed")


myOraConn.Close()
End Sub
End Class



Public Class testACPG_PRECONFIG_ITEM_PKG

Public registryID As String = ""
Dim protocolItemId As String = ""
Dim refItemID As String = ""
Dim facilityId As String = ""
Dim preConfiguredItemID As Integer
Public orderID As Integer = 0
Dim oradb As String = "Data Source=(DESCRIPTION=" _
+ "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dev1cdr)(PORT=1521)))" _
+ "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PROD.World)));" _
+ "User Id=acpg;Password=acpg;"
Dim myOraConn As New OracleConnection(oradb)

Public Sub setup1RegistryCreation()
myOraConn.Open()

Dim mystringRegistryId = "SELECT REGISTRY_ID_SEQUENCE.NEXTVAL from Dual R"
Dim cmdInsertRegistry As New OracleCommand(mystringRegistryId, myOraConn)

Dim Ridds As New DataSet
Dim Ridda As OracleDataAdapter
Dim Riddt As DataTable
Ridda = New OracleDataAdapter(cmdInsertRegistry)
Ridda.Fill(Ridds, "R")
Riddt = Ridds.Tables("R")

registryID = Riddt.Rows(0).Item(0)





Dim newRowRegistry As String = " INSERT INTO ACPG_REGISTRY " _
& " (REGISTRY_ID, " _
& " REGISTRY_NAME, " _
& " OWNER_NCID, " _
& " OWNER_TYPE, STATUS, RESTRICTED_FLAG, CREATED_DATE, CREATED_BY_NCID ) VALUES (" _
& registryID & ",'test registry name', 1, 1, 1,'N', sysdate, 1)"


Dim newRowAAR As String = " INSERT INTO ACPG_AUTO_REGISTRATION (REGISTRY_ID, " _
& " ITEM_TYPE, " _
& " ITEM_ID, " _
& " FACILITY_NCID ) VALUES (" _
& registryID & ", 1, 1, " & registryID & ")"



Dim myCommandAAR As New OracleCommand(newRowAAR, myOraConn)
Dim myCommandRegistry As New OracleCommand(newRowRegistry, myOraConn)







myCommandRegistry.ExecuteNonQuery()
myCommandAAR.ExecuteNonQuery()








Dim cmdpostInsertRegistry As New OracleCommand(mystringRegistryId, myOraConn)

Ridda = New OracleDataAdapter(cmdpostInsertRegistry)
Ridda.Fill(Ridds, "R")
Riddt = Ridds.Tables("R")

Assert.AreEqual(Riddt.Rows(0).Item(0).ToString(), registryID)



mystringRegistryId = "SELECT REGISTRY_ID_SEQUENCE.NEXTVAL FROM DUAL R"
Dim cmdCheckRegistry As New OracleCommand(mystringRegistryId, myOraConn)


Ridda = New OracleDataAdapter(cmdCheckRegistry)
Ridda.Fill(Ridds, "R")
Riddt = Ridds.Tables("R")

Dim iRid As String = Riddt.Rows(0).Item(0).ToString()

Assert.AreEqual(iRid, registryID, "Error in test class.")

myOraConn.Close()

Assert.Greater(registryID, "", "Error in test class. ACPG_REGISTRY_PKG.ADD_REGISTRY did not create a non-null registry. The test uses this package and so could not proceed.")
End Sub

Public Sub setup2FacilityCreation()
setup1RegistryCreation()
myOraConn.Open()
Dim sql As String
sql = "insert into acpg_facility_registry (registry_id, facility_ncid) VALUES (" _
& registryID & ", 1)"
Dim cmdFacilityID = New OracleCommand(sql, myOraConn)
cmdFacilityID.ExecuteNonQuery()

'Verify that the new row in the ACPG Registry exists

Dim mystringFacilityId As String = "SELECT Registry_ID as regID from " _
& "acpg_facility_registry where facility_ncid = 1 and registry_id = " _
& "'" & registryID & "'"

Dim cmdCheckFacility As New OracleCommand(mystringFacilityId, myOraConn)

Dim Ridds As New DataSet
Dim Ridda As OracleDataAdapter
Dim Riddt As DataTable
Ridda = New OracleDataAdapter(cmdCheckFacility)
Ridda.Fill(Ridds, "R")
Riddt = Ridds.Tables("R")

Dim iRid As String = Riddt.Rows(0).Item(0)
registryID = iRid
Assert.AreEqual(iRid, registryID, "Error in test class. " _
& " Creation of row of acpg_facility registry failed. Created value was supposed to be:" & registryID & " but actually was: " & iRid)

myOraConn.Close()





End Sub

Public Sub setup3OrderCreation()
setup2FacilityCreation()
myOraConn.Open()


Dim myCommand As OracleCommand = myOraConn.CreateCommand()

'Select new values for entering into test rows.
Dim mystringOrderId As String = "SELECT (MAX(Order_ID) + 1) from acpg_order R"
Dim cmdOrderID = New OracleCommand(mystringOrderId, myOraConn)

Dim Ridds As New DataSet
Dim Ridda As OracleDataAdapter
Dim Riddt As DataTable
Ridda = New OracleDataAdapter(cmdOrderID)
Ridda.Fill(Ridds, "R")
Riddt = Ridds.Tables("R")

orderID = Riddt.Rows(0).Item(0)
myCommand.CommandText = "Insert into acpg_order(order_id, order_name) " _
& "VALUES (" & orderID.ToString() & ", 'inserted for testing purposes')"

myCommand.ExecuteNonQuery()

myOraConn.Close()


myOraConn.Open()


myCommand = myOraConn.CreateCommand()

Dim sql = "Insert into acpg_facility_order (order_id, facility_ncid, order_data) " _
& "VALUES (" & orderID.ToString() & ", 1, 'inserted for testing purposes')"


myCommand.CommandText = sql



myCommand.ExecuteNonQuery()



Dim mystringNewOrderId As String = "SELECT MAX(Order_ID) from acpg_facility_order R"
Dim cmdNewOrderID = New OracleCommand(mystringNewOrderId, myOraConn)

Dim newRidds As New DataSet
Dim newRidda As OracleDataAdapter
Dim newRiddt As DataTable
newRidda = New OracleDataAdapter(cmdNewOrderID)
newRidda.Fill(newRidds, "R")
newRiddt = newRidds.Tables("R")

Assert.AreEqual(orderID, Riddt.Rows(0).Item(0), "Error in test class. New order not created.")





myOraConn.Close()

End Sub

Public Sub setup4ProtocolItemCreation()
setup3OrderCreation()
Dim result As String = "No Result"
myOraConn.Open()
Dim myCommand As OracleCommand = myOraConn.CreateCommand()



'Select new values for entering into test rows.
Dim mystringRegistryId As String = "SELECT MAX(REGISTRY_ID) FROM ACPG_REGISTRY R"
Dim mystringProtocolId As String = "SELECT PROTOCOL_ITEM_ID_SEQUENCE.NEXTVAL FROM DUAL P"
Dim cmdRegID = New OracleCommand(mystringRegistryId, myOraConn)
Dim cmdProtID = New OracleCommand(mystringProtocolId, myOraConn)

'For determining reminder Ids.
Dim Ridds As New DataSet
Dim Ridda As OracleDataAdapter
Dim Riddt As DataTable
Ridda = New OracleDataAdapter(cmdRegID)
Ridda.Fill(Ridds, "R")
Riddt = Ridds.Tables("R")

'For determining protocol item Ids.
Dim Protidds As New DataSet
Dim Protidda As OracleDataAdapter
Dim Protiddt As DataTable
Protidda = New OracleDataAdapter(cmdProtID)
Protidda.Fill(Protidds, "R")
Protiddt = Protidds.Tables("R")
'Select new values for entering into test rows.
Dim mystringPreconfId As String = "SELECT PRECONFIGURED_ITEM_ID_SEQUENCE.NEXTVAL FROM DUAL P"
Dim cmdPreconfID = New OracleCommand(mystringPreconfId, myOraConn)


Dim Preconfidds As New DataSet
Dim Preconfidda As OracleDataAdapter
Dim Preconfiddt As DataTable
Preconfidda = New OracleDataAdapter(cmdPreconfID)
Preconfidda.Fill(Preconfidds, "R")
Preconfiddt = Preconfidds.Tables("R")


'Retrieve class variables for registry id and protocol id
registryID = Riddt.Rows(0).Item(0)
protocolItemId = Protiddt.Rows(0).Item(0)
preConfiguredItemID = Preconfiddt.Rows(0).Item(0)



myCommand.CommandText = "Insert into acpg_protocol_item(Registry_id, item_type, " _
& "status, autocite_flag, SCHEDULE_TYPE, INTERVAL_LENGTH, INTERVAL_UNIT," _
& "NUMBER_OF_TIMES, protocol_Item_id, REF_ITEM_ID) VALUES (" _
& registryID & ",1,1,1,1,1,1,1," & protocolItemId & "," & preConfiguredItemID & ")"
result = "About to execute for Preconf " & preConfiguredItemID & " Protid " & protocolItemId & " Rid = " & registryID

myCommand.ExecuteNonQuery()
result = "Executed"

myOraConn.Close()

Assert.Greater(protocolItemId, "", "Error in test class. New protocol item not created")
End Sub


Public Sub test1AddPreconfiguredItem()
setup4ProtocolItemCreation()
Dim v_autocite_flag As Integer = 1
Dim v_autocite_qty As Integer = 1
Dim v_autocite_units As Integer = 1
Dim v_comments As String = "inserted for test purposes"
Dim v_goal_operator As Integer = 1
Dim v_goal_value As String = 1
Dim v_interval_length As Integer = 1
Dim v_interval_unit As Integer = 1
Dim v_item_name As String = "test data"
Dim v_item_type As Integer = 1
Dim v_number_of_times As Integer = 1
Dim v_ref_item_id As String = orderID.ToString
Dim v_schedule_type As Integer = 1
Dim v_status As Integer = 1
Dim v_url As String = "testACPGURL.org"


myOraConn.Open()
Dim sql As String = "ACPG_PRECONFIG_ITEM_PKG.add_preconfigured_item"

Dim cmdAddPI As New OracleCommand(sql, myOraConn)

cmdAddPI.CommandType() = CommandType.StoredProcedure
cmdAddPI.Parameters.Add(New OracleParameter("p_autocite_flag", OracleDbType.Int64)).Value = v_autocite_flag
cmdAddPI.Parameters.Add(New OracleParameter("p_autocite_qty", OracleDbType.Int64)).Value = v_autocite_qty
cmdAddPI.Parameters.Add(New OracleParameter("p_autocite_units", OracleDbType.Int64)).Value = v_autocite_units
cmdAddPI.Parameters.Add(New OracleParameter("p_comments", OracleDbType.Varchar2)).Value = v_comments
cmdAddPI.Parameters.Add(New OracleParameter("p_goal_operator", OracleDbType.Int64)).Value = v_goal_operator
cmdAddPI.Parameters.Add(New OracleParameter("p_goal_value", OracleDbType.Varchar2)).Value = v_goal_value
cmdAddPI.Parameters.Add(New OracleParameter("p_interval_length", OracleDbType.Int64)).Value = v_interval_length
cmdAddPI.Parameters.Add(New OracleParameter("p_interval_unit", OracleDbType.Int64)).Value = v_interval_unit
cmdAddPI.Parameters.Add(New OracleParameter("p_item_name", OracleDbType.Varchar2)).Value = v_item_name
cmdAddPI.Parameters.Add(New OracleParameter("p_item_type", OracleDbType.Int64)).Value = v_item_type
cmdAddPI.Parameters.Add(New OracleParameter("p_number_of_times", OracleDbType.Int64)).Value = v_number_of_times
cmdAddPI.Parameters.Add(New OracleParameter("p_ref_item_id", OracleDbType.Int64)).Value = orderID
cmdAddPI.Parameters.Add(New OracleParameter("p_schedule_type", OracleDbType.Int64)).Value = v_schedule_type
cmdAddPI.Parameters.Add(New OracleParameter("p_status", OracleDbType.Int64)).Value = v_status
cmdAddPI.Parameters.Add(New OracleParameter("p_url", OracleDbType.Varchar2)).Value = v_url
Dim prm = New OracleParameter("curVar", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output)
cmdAddPI.Parameters.Add(prm)
cmdAddPI.ExecuteNonQuery()

Dim oraref As OracleRefCursor = cmdAddPI.Parameters("curVar").Value

Dim dr As OracleDataReader = oraref.GetDataReader()


dr.Read()

preConfiguredItemID = dr(0).ToString()



'Verify that the new row in the ACPG ACPG_PRECONFIGURED_ITEM table exists.

Dim mystringPreconf As String = "SELECT ref_item_id from acpg_preconfigured_item R " _
& " where preconfigured_item_id = " & preConfiguredItemID.ToString()


Dim cmdCheckPreconf As New OracleCommand(mystringPreconf, myOraConn)

Dim Ridds As New DataSet
Dim Ridda As OracleDataAdapter
Dim Riddt As DataTable
Ridda = New OracleDataAdapter(cmdCheckPreconf)
Ridda.Fill(Ridds, "R")
Riddt = Ridds.Tables("R")

Dim retOrderID As Integer = Riddt.Rows(0).Item(0)
Assert.AreEqual(orderID, retOrderID, "Error in test class. Inserted Preconfigured Item ID not returned.")
myOraConn.Close()

End Sub

Public Sub test2GetAllPreconfiguredItems()
test1AddPreconfiguredItem()
Dim numberOfRowsInExplicitCursor As Integer = 0

'run ACPG_PRECONF_ITEM_PKG.get_all_preconf_items

'Create Command For Stored Procedure
Dim v_status As Integer = 1

Dim prm = New OracleParameter("curVar", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output)
myOraConn.Open()

Dim sql As String = "ACPG_PRECONFIG_ITEM_PKG.get_all_preconfig_items"
Dim cmdAddPI As New OracleCommand(sql, myOraConn)
cmdAddPI.CommandType() = CommandType.StoredProcedure
cmdAddPI.Parameters.Add(New OracleParameter("p_status", OracleDbType.Int64)).Value = v_status
cmdAddPI.Parameters.Add(prm)
cmdAddPI.ExecuteNonQuery()

'Find the value of Order ID and Registry which test1AddPreconfiguredItem() created and added.


Dim oraref As OracleRefCursor = cmdAddPI.Parameters("curVar").Value
Dim dr As OracleDataReader = oraref.GetDataReader()
While (dr.Read())
numberOfRowsInExplicitCursor = numberOfRowsInExplicitCursor + 1
End While
'dr.Close()
'For starters, make certain that at least one row gets returned. We know that at least one
'row is in there because we added it at the start of this procedure
Assert.Greater(numberOfRowsInExplicitCursor, 0, "get_all_preconfig_items did not retrieve any items." & preConfiguredItemID)


'Now make certain that the preconfigured item ID that we created is retrieved
Assert.AreEqual(1, ConvertDataReaderToCount(dr, preConfiguredItemID, 0), preConfiguredItemID)



myOraConn.Close()


'Check that the number of rows returned is identical.
'Assert.AreEqual(numberOfRowsInExplicitCursor, numberOfRowsReturnedBySQL)
End Sub

Public Sub test3GetFacilityPreconfItem()
test2GetAllPreconfiguredItems()


'Create Command For Stored Procedure
Dim v_facility_ncid As Integer = 1 'tests above use this facility_ncid
Dim prm = New OracleParameter("curVar", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output)
myOraConn.Open()

Dim sql As String = "ACPG_PRECONFIG_ITEM_PKG.get_all_preconfig_items"
Dim cmdAddPI As New OracleCommand(sql, myOraConn)
cmdAddPI.CommandType() = CommandType.StoredProcedure
cmdAddPI.Parameters.Add(New OracleParameter("p_facility_ncid", OracleDbType.Int64)).Value = v_facility_ncid
cmdAddPI.Parameters.Add(prm)
cmdAddPI.ExecuteNonQuery()

'find the number rows in the DataReader
Dim oraref As OracleRefCursor = cmdAddPI.Parameters("curVar").Value
Dim dr As OracleDataReader = oraref.GetDataReader()
Assert.AreEqual(ConvertDataReaderToCount(dr, preConfiguredItemID, orderID), 1, "Get_all_preconfig_items failed")



dr.Close()


myOraConn.Close()


End Sub

Public Function ConvertDataReaderToCount(ByVal reader As OracleDataReader, ByVal pPreconfiguredItemId As Integer, ByVal pOrderID As Integer) As Integer


Dim pPreconfiguredIDCount As Integer = 0

While reader.Read()
If (pPreconfiguredItemId = reader.GetValue(0)) Then
pPreconfiguredIDCount = pPreconfiguredIDCount + 1
End If
End While
If (pOrderID = 0) Then
Return pPreconfiguredIDCount
End If
If (pPreconfiguredIDCount <> 1) Then
Return pPreconfiguredIDCount
End If

'See if there is a row of acpg_facility_order for which the unique order id appears
'along with facility id = 1 which is used in all of our test rows.

Dim foSQL As String = "Select count(*) from acpg_facility_order " _
& "where order_ID = " & orderID.ToString & " and facility_ncid = 1"
Dim cmdFoID = New OracleCommand(foSQL, myOraConn)


Dim Fods As New DataSet
Dim Foda As OracleDataAdapter
Dim Fodt As DataTable
Foda = New OracleDataAdapter(cmdFoID)
Foda.Fill(Fods, "R")
Fodt = Fods.Tables("R")


Dim FoCount = Fodt.Rows(0).Item(0)
Return FoCount


End Function

End Class

Public Class testACPG_PROTOCOL_ITEM_PKG

Public registryID As Integer = 0
Dim protocolItemId As Integer = 0
Dim refItemID As String = ""
Dim facilityId As String = ""
Dim preConfiguredItemID As Integer
Dim orderID As Integer = 0
Dim oradb As String = "Data Source=(DESCRIPTION=" _
+ "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dev1cdr)(PORT=1521)))" _
+ "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PROD.World)));" _
+ "User Id=acpg;Password=acpg;"
Dim myOraConn As New OracleConnection(oradb)

Public Sub testAdd_protocol_item()
Dim inserted_protocol As Integer

Dim mySetup = New testACPG_PRECONFIG_ITEM_PKG
myOraConn.Open()

mySetup.setup3OrderCreation()

'Call ACPG_PROTOCOL_ITEM_PKG
Dim v_autocite_flag As Integer = 1
Dim v_autocite_qty As Integer = 1
Dim v_autocite_units As Integer = 1
Dim v_comments As String = "inserted for test purposes"
Dim v_goal_operator As Integer = 1
Dim v_goal_value As String = 1
Dim v_interval_length As Integer = 1
Dim v_interval_unit As Integer = 1
Dim v_item_detail As String = "inserted for test purposes"
Dim v_item_name As String = "test data"
Dim v_item_type As Integer = 1
Dim v_number_of_times As Integer = 1
Dim v_ref_item_id As Integer = CType(mySetup.orderID, Integer)
Assert.Greater(v_ref_item_id, 0)
Dim v_registry_id As Integer

Dim v_schedule_type As Integer = 1
Dim v_status As Integer = 1
Dim v_url As String = "testACPGURL.org"

'Select a value of for registry_ID which already exists in the database.

Dim mystringRegistryId = "SELECT MAX(Registry_ID) from acpg_registry R"
Dim cmdInsertRegistry As New OracleCommand(mystringRegistryId, myOraConn)

Dim Ridds As New DataSet
Dim Ridda As OracleDataAdapter
Dim Riddt As DataTable
Ridda = New OracleDataAdapter(cmdInsertRegistry)
Ridda.Fill(Ridds, "R")
Riddt = Ridds.Tables("R")

registryID = Riddt.Rows(0).Item(0)





Dim sql As String = "ACPG_PROTOCOL_ITEM_PKG.add_protocol_item"

Dim cmdAddPI As New OracleCommand(sql, myOraConn)

cmdAddPI.CommandType() = CommandType.StoredProcedure
cmdAddPI.Parameters.Add(New OracleParameter("p_autocite_flag", OracleDbType.Int64)).Value = v_autocite_flag
cmdAddPI.Parameters.Add(New OracleParameter("p_autocite_qty", OracleDbType.Int64)).Value = v_autocite_qty
cmdAddPI.Parameters.Add(New OracleParameter("p_autocite_units", OracleDbType.Int64)).Value = v_autocite_units
cmdAddPI.Parameters.Add(New OracleParameter("p_comments", OracleDbType.Varchar2)).Value = v_comments
cmdAddPI.Parameters.Add(New OracleParameter("p_goal_operator", OracleDbType.Int64)).Value = v_goal_operator
cmdAddPI.Parameters.Add(New OracleParameter("p_goal_value", OracleDbType.Varchar2)).Value = v_goal_value
cmdAddPI.Parameters.Add(New OracleParameter("p_interval_length", OracleDbType.Int64)).Value = v_interval_length
cmdAddPI.Parameters.Add(New OracleParameter("p_interval_unit", OracleDbType.Int64)).Value = v_interval_unit
cmdAddPI.Parameters.Add(New OracleParameter("p_item_detail", OracleDbType.Varchar2)).Value = v_item_detail
cmdAddPI.Parameters.Add(New OracleParameter("p_item_name", OracleDbType.Varchar2)).Value = v_item_name
cmdAddPI.Parameters.Add(New OracleParameter("p_item_type", OracleDbType.Int64)).Value = v_item_type
cmdAddPI.Parameters.Add(New OracleParameter("p_number_of_times", OracleDbType.Int64)).Value = v_number_of_times
cmdAddPI.Parameters.Add(New OracleParameter("p_ref_item_id", OracleDbType.Int64)).Value = orderID
cmdAddPI.Parameters.Add(New OracleParameter("p_registry_id", OracleDbType.Int64)).Value = registryID
cmdAddPI.Parameters.Add(New OracleParameter("p_schedule_type", OracleDbType.Int64)).Value = v_schedule_type
cmdAddPI.Parameters.Add(New OracleParameter("p_status", OracleDbType.Int64)).Value = v_status
cmdAddPI.Parameters.Add(New OracleParameter("p_url", OracleDbType.Varchar2)).Value = v_url
Dim prm = New OracleParameter("curVar", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output)
cmdAddPI.Parameters.Add(prm)



'Find out what the next value of protocol_item_id will be. It will be
'one more than the value of protocol_item_id_sequence that we retrieve.

Dim nextProtocolIdSQL = "Select protocol_item_id_sequence.NEXTVAL + 1 from dual R"
Dim cmdNextProtocolIdSQL As New OracleCommand(nextProtocolIdSQL, myOraConn)

Dim nextValDs As New DataSet
Dim nextValDa = New OracleDataAdapter(cmdNextProtocolIdSQL)
nextValDa.Fill(nextValDs, "R")
Dim nextValDt = nextValDs.Tables("R")

Dim nextValPlusOne As Integer = nextValDt.Rows(0).Item(0)






cmdAddPI.ExecuteNonQuery()

Dim oraref As OracleRefCursor = cmdAddPI.Parameters("curVar").Value

Dim dr As OracleDataReader = oraref.GetDataReader()


dr.Read()

inserted_protocol = dr(0)
Assert.AreEqual(inserted_protocol, nextValPlusOne)


myOraConn.Close()






End Sub

Public Sub testDeleteReminderFromPatients()
myOraConn.Open()
Dim myOracleTransaction As OracleTransaction = myOraConn.BeginTransaction()


'Make certain that there is something in the reminder_history.
'I can put whatever I want to into event ID.

Dim mySetup = New testACPG_REMINDER_PKG


mySetup.testAdd_Reminder_History()




'Find a protocol_item_id for which there is a reminder history to delete.

Dim protocolItemIDSQL As String = "Select a.protocol_item_id from ACPG_REMINDER_HISTORY a " _
& "WHERE NOT EXISTS (SELECT 'X' FROM ACPG_REMINDER_HISTORY_ARCH b " _
& " WHERE b.reminder_history_id = a.reminder_history_id )"


Dim cmdGetProtocolItemID As New OracleCommand(protocolItemIDSQL, myOraConn)

Dim PIds As New DataSet
Dim PIda As OracleDataAdapter
Dim PIdt As DataTable
PIda = New OracleDataAdapter(cmdGetProtocolItemID)
PIda.Fill(PIds, "R")
PIdt = PIds.Tables("R")
Assert.Greater(PIdt.Rows.Count, 0)
Assert.Greater(PIdt.Rows(0).Item(0), 0)

protocolItemId = PIdt.Rows(0).Item(0)

'select protocol item information from acpg_protocol_item


Dim protItemInfoSQL As String = "Select * from acpg_protocol_item where protocol_item_id =" _
& protocolItemId.ToString()

Dim cmdGetProtocolItemInfo As New OracleCommand(protItemInfoSQL, myOraConn)

Dim PInfods As New DataSet
Dim PInfoda As OracleDataAdapter
Dim PInfodt As DataTable
PInfoda = New OracleDataAdapter(cmdGetProtocolItemInfo)
PInfoda.Fill(PInfods, "R")
PInfodt = PInfods.Tables("R")






Dim v_Registry_ID As Integer = PInfodt.Rows(0).Item(1)
Dim v_Item_Type As Integer = PInfodt.Rows(0).Item(2)
Dim v_Item_Name As String = PInfodt.Rows(0).Item(3)
Dim v_Status As Integer = PInfodt.Rows(0).Item(4)
Dim v_Schedule_Type As Integer = PInfodt.Rows(0).Item(5)
Dim v_Interval_Length As Integer = PInfodt.Rows(0).Item(6)
Dim v_Interval_Unit As Integer = PInfodt.Rows(0).Item(7)
Dim v_Number_Of_Times As Integer = PInfodt.Rows(0).Item(8)
Dim v_Goal_Value As String = PInfodt.Rows(0).Item(9)
Dim v_Goal_Operator As Integer = PInfodt.Rows(0).Item(10)
Dim v_Comments As String = PInfodt.Rows(0).Item(11)
Dim v_URL As String = PInfodt.Rows(0).Item(12)
Dim v_Ref_Item_Id As Integer = PInfodt.Rows(0).Item(13)
Dim v_Item_Detail As String = PInfodt.Rows(0).Item(14)
Dim v_Autocite_Flag As Integer = PInfodt.Rows(0).Item(15)
Dim v_Autocite_Qty As Integer = PInfodt.Rows(0).Item(16)
Dim v_Autocite_Units As Integer = PInfodt.Rows(0).Item(17)

Dim sql As String = "ACPG_PROTOCOL_ITEM_PKG.delete_reminder_from_patients"


Dim cmdDeleteRem As New OracleCommand(sql, myOraConn)

cmdDeleteRem.CommandType() = CommandType.StoredProcedure
cmdDeleteRem.Parameters.Add(New OracleParameter("p_autocite_flag", OracleDbType.Int64)).Value = v_Autocite_Flag
cmdDeleteRem.Parameters.Add(New OracleParameter("p_autocite_qty", OracleDbType.Int64)).Value = v_Autocite_Qty
cmdDeleteRem.Parameters.Add(New OracleParameter("p_autocite_units", OracleDbType.Int64)).Value = v_Autocite_Units
cmdDeleteRem.Parameters.Add(New OracleParameter("p_comments", OracleDbType.Varchar2)).Value = v_Comments
cmdDeleteRem.Parameters.Add(New OracleParameter("p_goal_operator", OracleDbType.Int64)).Value = v_Goal_Operator
cmdDeleteRem.Parameters.Add(New OracleParameter("p_goal_value", OracleDbType.Varchar2)).Value = v_Goal_Value
cmdDeleteRem.Parameters.Add(New OracleParameter("p_interval_length", OracleDbType.Int64)).Value = v_Interval_Length
cmdDeleteRem.Parameters.Add(New OracleParameter("p_interval_unit", OracleDbType.Int64)).Value = v_Interval_Unit
cmdDeleteRem.Parameters.Add(New OracleParameter("p_item_detail", OracleDbType.Varchar2)).Value = v_Item_Detail
cmdDeleteRem.Parameters.Add(New OracleParameter("p_item_name", OracleDbType.Varchar2)).Value = v_Item_Name
cmdDeleteRem.Parameters.Add(New OracleParameter("p_item_type", OracleDbType.Int64)).Value = v_Item_Type
cmdDeleteRem.Parameters.Add(New OracleParameter("p_number_of_times", OracleDbType.Int64)).Value = v_Number_Of_Times
cmdDeleteRem.Parameters.Add(New OracleParameter("p_ref_item_id", OracleDbType.Int64)).Value = v_Ref_Item_Id
cmdDeleteRem.Parameters.Add(New OracleParameter("p_registry_id", OracleDbType.Int64)).Value = v_Registry_ID
cmdDeleteRem.Parameters.Add(New OracleParameter("p_schedule_type", OracleDbType.Int64)).Value = v_Schedule_Type
cmdDeleteRem.Parameters.Add(New OracleParameter("p_status", OracleDbType.Int64)).Value = v_Status
cmdDeleteRem.Parameters.Add(New OracleParameter("p_url", OracleDbType.Varchar2)).Value = v_URL
cmdDeleteRem.Parameters.Add(New OracleParameter("p_protocol_item", OracleDbType.Int64)).Value = protocolItemId



cmdDeleteRem.ExecuteNonQuery()



'Check That Rows Were Inserted Into ACPG_Protocol_Item_Arch

Dim checkSQL As String = "Select count(*) from ACPG_Protocol_Item_Arch " _
& " where protocol_item_id =" & protocolItemId.ToString() _
& " AND registry_id = " & v_Registry_ID.ToString() _
& " AND item_type = " & v_Item_Type.ToString() _
& " AND schedule_type =" & v_Schedule_Type.ToString() _
& " AND interval_length = " & v_Interval_Length.ToString() _
& " AND interval_unit = " & v_Autocite_Flag.ToString()



Dim cmdCheckSQL As New OracleCommand(checkSQL, myOraConn)

Dim checkds As New DataSet
Dim checkda As OracleDataAdapter
Dim checkdt As DataTable
checkda = New OracleDataAdapter(cmdCheckSQL)
checkda.Fill(checkds, "R")
checkdt = checkds.Tables("R")

Dim checkCount = checkdt.Rows(0).Item(0)

Assert.AreEqual(1, checkCount, "No Row was inserted into ACPG_Protocol_Item_Arch")

'Check that rows were deleted from protocol_item_medcin_id
Dim medcinSQL As String = "Select count(*) from acpg_protocol_item_medcin_id " _
& " where protocol_item_id = " & protocolItemId.ToString()


Dim cmdMedcinSQL As New OracleCommand(medcinSQL, myOraConn)

Dim medcinds As New DataSet
Dim medcinda As OracleDataAdapter
Dim medcindt As DataTable
medcinda = New OracleDataAdapter(cmdMedcinSQL)
medcinda.Fill(medcinds, "R")
medcindt = medcinds.Tables("R")

Dim medcinCount = checkdt.Rows(0).Item(0)


Assert.AreEqual(0, medcinCount, "acpg_protocol_item_medcin_id not cleaned for protocol item id = " & protocolItemId.ToString)






'Check that rows were deleted from acpg_reminder_history
Dim cleanRemHistSQL As String = "Select count(*) from acpg_reminder_history " _
& " where protocol_item_id = " & protocolItemId.ToString()


Dim cmdCleanRemHistSQL As New OracleCommand(cleanRemHistSQL, myOraConn)

Dim cleanRHds As New DataSet
Dim cleanRHda As OracleDataAdapter
Dim cleanRHdt As DataTable
cleanRHda = New OracleDataAdapter(cmdCleanRemHistSQL)
cleanRHda.Fill(cleanRHds, "R")
cleanRHdt = cleanRHds.Tables("R")

Dim cleanRHCount = cleanRHdt.Rows(0).Item(0)


Assert.AreEqual(0, cleanRHCount, "acpg_reminder_history not cleaned for protocol item id = " & protocolItemId.ToString)








'Check that rows were deleted from ACPG_Reminder





Dim cleanRemSQL As String = "Select count(*) from acpg_reminder " _
& " where protocol_item_id = " & protocolItemId.ToString()


Dim cmdCleanRemSQL As New OracleCommand(cleanRemHistSQL, myOraConn)

Dim cleanRds As New DataSet
Dim cleanRda As OracleDataAdapter
Dim cleanRdt As DataTable
cleanRda = New OracleDataAdapter(cmdCleanRemSQL)
cleanRda.Fill(cleanRds, "R")
cleanRdt = cleanRds.Tables("R")

Dim cleanRCount = cleanRdt.Rows(0).Item(0)


Assert.AreEqual(0, cleanRCount, "acpg_reminder not cleaned for protocol item id = " & protocolItemId.ToString)




myOracleTransaction.Rollback()
myOraConn.Close()
End Sub
End Class

Public Class testACPG_REGISTRY_PKG
Dim oradb As String = "Data Source=(DESCRIPTION=" _
+ "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dev1cdr)(PORT=1521)))" _
+ "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PROD.World)));" _
+ "User Id=acpg;Password=acpg;"
Dim myOraConn As New OracleConnection(oradb)
Dim v_registry_id As Integer

Public Sub testAdd_Registry()
myOraConn.Open()

Dim v_registry_id As Integer
Dim v_unit_number As Integer
Dim v_protocol_item_id As Integer





Dim VCRegSQL As String = _
"SELECT a.OWNER_NCID, a.OWNER_TYPE, a.RESTRICTED_FLAG, a.REGISTRY_NAME," _
& " a.CREATED_BY_NCID, a.REGISTRY_ID " _
& " from acpg_registry a where rownum = 1"

Dim v_cs_protocol_itemSQL As String = _
"SELECT * from acpg.acpg_protocol_item where rownum = 1"


Dim v_cs_patientSQL As String = _
"SELECT * from acpg.acpg_registry_patient where rownum = 1"


Dim v_cs_medcinSQL As String = _
"SELECT * from acpg.acpg_protocol_item_medcin_id where rownum = 1"



Dim mystringRegistryId As String = "SELECT REGISTRY_ID_SEQUENCE.NEXTVAL FROM DUAL R"

Dim add_RegistrySQL As String = "ACPG_REGISTRY_PKG.add_registry"
Dim cmdAddRegistry As New OracleCommand(add_RegistrySQL, myOraConn)

cmdAddRegistry.CommandType() = CommandType.StoredProcedure
cmdAddRegistry.Parameters.Add(New OracleParameter("p_registry_name", OracleDbType.Varchar2)).Value = "1"
cmdAddRegistry.Parameters.Add(New OracleParameter("p_owner_ncid", OracleDbType.Int64)).Value = 1
cmdAddRegistry.Parameters.Add(New OracleParameter("p_owner_type", OracleDbType.Int64)).Value = 1
cmdAddRegistry.Parameters.Add(New OracleParameter("p_status", OracleDbType.Int64)).Value = 1
cmdAddRegistry.Parameters.Add(New OracleParameter("p_restricted_flag", OracleDbType.Char)).Value = "Y"
cmdAddRegistry.Parameters.Add(New OracleParameter("p_default_dx_medcin_id", OracleDbType.Int64)).Value = 1
cmdAddRegistry.Parameters.Add(New OracleParameter("p_URL", OracleDbType.Varchar2)).Value = "1"
cmdAddRegistry.Parameters.Add(New OracleParameter("p_created_date", OracleDbType.Date)).Value = Now
cmdAddRegistry.Parameters.Add(New OracleParameter("p_created_by_ncid", OracleDbType.Int64)).Value = 1
Dim prm = New OracleParameter("curVar", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output)
cmdAddRegistry.Parameters.Add(prm)


cmdAddRegistry.ExecuteNonQuery()

Dim oraref As OracleRefCursor = cmdAddRegistry.Parameters("curVar").Value

Dim dr As OracleDataReader = oraref.GetDataReader()

Dim RetVal As Integer

dr.Read()

RetVal = dr(0)


Assert.Greater(RetVal, 0)

'Test it a second time with better data'

'Instantiate the v_cs_registry view

Dim cmdVCReg As New OracleCommand(VCRegSQL, myOraConn)

Dim VCRegds As New DataSet
Dim VCRegda As OracleDataAdapter
Dim VCRegdt As DataTable
VCRegda = New OracleDataAdapter(cmdVCReg)
VCRegda.Fill(VCRegds, "R")
VCRegdt = VCRegds.Tables("R")

v_registry_id = VCRegdt.Rows(0).Item(5)

Dim v_registry_name As String = VCRegdt.Rows(0).Item(5).ToString() _
& "-" & VCRegdt.Rows(0).Item(5)

v_registry_name = Left(v_registry_name, 50)

Dim v_owner_ncid = VCRegdt.Rows(0).Item(0)

Dim v_owner_type = VCRegdt.Rows(0).Item(1)

Dim v_status = 1

Dim v_restricted_flag = VCRegdt.Rows(0).Item(2)

Dim v_created_date = Now

Dim v_created_by_ncid = VCRegdt.Rows(0).Item(4)





Dim cmdAddR As New OracleCommand(add_RegistrySQL, myOraConn)

cmdAddR.CommandType() = CommandType.StoredProcedure
cmdAddR.Parameters.Add(New OracleParameter("p_registry_name", OracleDbType.Varchar2)).Value = "hello" 'v_registry_name
cmdAddR.Parameters.Add(New OracleParameter("p_owner_ncid", OracleDbType.Int64)).Value = 1 'v_owner_ncid
cmdAddR.Parameters.Add(New OracleParameter("p_owner_type", OracleDbType.Int64)).Value = 1 'owner_type
cmdAddR.Parameters.Add(New OracleParameter("p_status", OracleDbType.Int64)).Value = 1 'v_status
cmdAddR.Parameters.Add(New OracleParameter("p_restricted_flag", OracleDbType.Char)).Value = "N" 'v_restricted_flag
cmdAddR.Parameters.Add(New OracleParameter("p_default_dx_medcin_id", OracleDbType.Int64)).Value = 0 'v_created_by_ncid
cmdAddR.Parameters.Add(New OracleParameter("p_url", OracleDbType.Varchar2)).Value = ""
cmdAddR.Parameters.Add(New OracleParameter("p_created_date", OracleDbType.Date)).Value = Now 'v_created_date
cmdAddR.Parameters.Add(New OracleParameter("p_created_by_ncid", OracleDbType.Int64)).Value = 1 'v_created_by_ncid




prm = New OracleParameter("curVar", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output)
cmdAddR.Parameters.Add(prm)
cmdAddR.ExecuteNonQuery()

oraref = cmdAddR.Parameters("curVar").Value

dr = oraref.GetDataReader()


dr.Read()

RetVal = dr(0)


Assert.Greater(RetVal, 0)

myOraConn.Close()



End Sub
Public Sub testAuto_Register_Medcins()


Dim mySetup = New testACPG_PATIENT_PKG

mySetup.testAdd_patient_to_registry()
myOraConn.Open()
'get the unit number and registry added
Dim registryID As Integer = mySetup.Rid
Dim unitNumber As Integer = mySetup.uniqueID

'Check that this combination has medcins to register

Dim testSql As String = "Select a.item_id from acpg_auto_registration a, " _
& " acpg_registry r " _
& " WHERE a.item_type In (1,2) AND " _
& " a.facility_ncid = 0 AND " _
& " a.registry_id = r.registry_id AND " _
& " r.status = 1 "




Dim cmdTestSQL As New OracleCommand(testSql, myOraConn)

Dim testRds As New DataSet
Dim testRda As OracleDataAdapter
Dim testRdt As DataTable
testRda = New OracleDataAdapter(cmdTestSQL)
testRda.Fill(testRds, "R")
testRdt = testRds.Tables("R")


Dim testCount = testRdt.Rows.Count()

Assert.Greater(testCount, 0, "no Medcin to register")
Dim testMedcin As String = testRdt.Rows(0).Item(0)

'Register MEDCIN

Dim add_RegistrySQL As String = "ACPG_REGISTRY_PKG.auto_register_medcins"
Dim cmdAddRegistry As New OracleCommand(add_RegistrySQL, myOraConn)

cmdAddRegistry.CommandType() = CommandType.StoredProcedure
cmdAddRegistry.Parameters.Add(New OracleParameter("p_unit_number", OracleDbType.Varchar2)).Value = unitNumber
cmdAddRegistry.Parameters.Add(New OracleParameter("p_medcin_id_list", OracleDbType.Varchar2)).Value = testMedcin


cmdAddRegistry.ExecuteNonQuery()


'Check that unit number was added for new patient

Dim checkSQL As String = "Select count(*) from acpg_reminder where " _
& " Unit_Number = " & unitNumber.ToString()

Dim cmdCheckSQL As New OracleCommand(checkSQL, myOraConn)

Dim checkRds As New DataSet
Dim checkRda As OracleDataAdapter
Dim checkRdt As DataTable
checkRda = New OracleDataAdapter(cmdCheckSQL)
checkRda.Fill(checkRds, "R")
checkRdt = checkRds.Tables("R")


Dim checkCount = testRdt.Rows.Count()

Assert.Greater(checkCount, 0, "patient not inserted into acpg_reminder")


myOraConn.Close()
End Sub


Public Sub testAuto_Register_Orders()






Dim mySetup = New testACPG_PATIENT_PKG

mySetup.testAdd_patient_to_registry()
myOraConn.Open()
'get the unit number and registry added
Dim registryID As Integer = mySetup.Rid
Dim unitNumber As Integer = mySetup.uniqueID

'Check that this combination has medcins to register

Dim testSql As String = "Select a.facility_ncid, a.item_id " _
& " from acpg_auto_registration a, " _
& " acpg_registry r " _
& " WHERE a.item_type In (3,4,5,6) AND " _
& " a.registry_id = r.registry_id AND " _
& " r.status = 1 "




Dim cmdTestSQL As New OracleCommand(testSql, myOraConn)

Dim testRds As New DataSet
Dim testRda As OracleDataAdapter
Dim testRdt As DataTable
testRda = New OracleDataAdapter(cmdTestSQL)
testRda.Fill(testRds, "R")
testRdt = testRds.Tables("R")


Dim testCount = testRdt.Rows.Count()

Assert.Greater(testCount, 0, "no facilities and orders to register")
Dim testFacility As Integer = testRdt.Rows(0).Item(0)
Dim testOrder As Integer = testRdt.Rows(0).Item(1)
'Register facilites and orders

Dim add_RegistrySQL As String = "ACPG_REGISTRY_PKG.auto_register_orders"
Dim cmdAddRegistry As New OracleCommand(add_RegistrySQL, myOraConn)

cmdAddRegistry.CommandType() = CommandType.StoredProcedure
cmdAddRegistry.Parameters.Add(New OracleParameter("p_unit_number", OracleDbType.Int64)).Value = unitNumber
cmdAddRegistry.Parameters.Add(New OracleParameter("p_enc_num", OracleDbType.Varchar2)).Value = "test data"
cmdAddRegistry.Parameters.Add(New OracleParameter("p_order_ien_list", OracleDbType.Varchar2)).Value = testOrder
cmdAddRegistry.Parameters.Add(New OracleParameter("p_order_fac_ncid", OracleDbType.Varchar2)).Value = testFacility


cmdAddRegistry.ExecuteNonQuery()


'Check that unit number was added for new patient

Dim checkSQL As String = "Select count(*) from acpg_reminder where " _
& " Unit_Number = " & unitNumber.ToString()

Dim cmdCheckSQL As New OracleCommand(checkSQL, myOraConn)

Dim checkRds As New DataSet
Dim checkRda As OracleDataAdapter
Dim checkRdt As DataTable
checkRda = New OracleDataAdapter(cmdCheckSQL)
checkRda.Fill(checkRds, "R")
checkRdt = checkRds.Tables("R")


Dim checkCount = testRdt.Rows.Count()

Assert.Greater(checkCount, 0, "order facility patient not inserted into acpg_reminder")


myOraConn.Close()





















End Sub
Public Sub testDelete_Registry()

'Add A Registry
'testAdd_Registry() adds a registry and tests that it is there.
'It creates a registry whose registry id value is the class variable
'v_registry_id
testAdd_Registry()
myOraConn.Open()

'Run Delete Registry
Dim delete_RegistrySQL As String = "ACPG_REGISTRY_PKG.delete_registry"
Dim cmddeleteRegistry As New OracleCommand(delete_RegistrySQL, myOraConn)

cmddeleteRegistry.CommandType() = CommandType.StoredProcedure
cmddeleteRegistry.Parameters.Add(New OracleParameter("p_registry_id", OracleDbType.Int64)).Value = v_registry_id
cmddeleteRegistry.ExecuteNonQuery()

'Check that it is gone
Dim cleanSQL As String = "Select count(*) from acpg_registry " _
& " where registry_id = " & v_registry_id.ToString()


Dim cmdCleanSQL As New OracleCommand(cleanSQL, myOraConn)

Dim cleanRds As New DataSet
Dim cleanRda As OracleDataAdapter
Dim cleanRdt As DataTable
cleanRda = New OracleDataAdapter(cmdCleanSQL)
cleanRda.Fill(cleanRds, "R")
cleanRdt = cleanRds.Tables("R")

Dim cleanRCount = cleanRdt.Rows(0).Item(0)


Assert.AreEqual(0, cleanRCount, "registry not deleted for = " & v_registry_id.ToString)




myOraConn.Close()
End Sub

Public Sub testGet_Clinic_Filters()
myOraConn.Open()


' Pick a registry_id to test
Dim getRegFacSQL As String = " Select a.registry_id, c.facility_ncid " _
& " FROM ACPG_CLINIC_SPECIFIC_FILTER a, clinic c, mtf_dmis_id m " _
& " WHERE c.ncid = a.clinic_ncid " _
& " AND m.dmis_id = c.dmis_id_code " _
& " AND m.facility_service_code IS NOT NULL"


Dim cmdGetRegFac As New OracleCommand(getRegFacSQL, myOraConn)

Dim getRegFacRds As New DataSet
Dim getRegFacRda As OracleDataAdapter
Dim getRegFacRdt As DataTable
getRegFacRda = New OracleDataAdapter(cmdGetRegFac)
getRegFacRda.Fill(getRegFacRds, "R")
getRegFacRdt = getRegFacRds.Tables("R")

Dim registryID = getRegFacRdt.Rows(0).Item(0)
Dim FacilityID = getRegFacRdt.Rows(0).Item(1)

'Get counts

Dim sqlCount As String = _
" Select count(*) FROM ( " _
& " SELECT registry_id FROM ACPG_CLINIC_TYPE_FILTER " _
& " WHERE registry_id = " & registryID.ToString() _
& " UNION ALL" _
& " SELECT registry_id FROM ACPG_CLINIC_SPECIFIC_FILTER a, clinic c, mtf_dmis_id m " _
& " WHERE a.registry_id = " & registryID.ToString() _
& " And c.facility_ncid = " & FacilityID.ToString() _
& " AND c.ncid = a.clinic_ncid" _
& " AND m.dmis_id = c.dmis_id_code" _
& " AND m.facility_service_code IS NOT NULL" _
& " )"




Dim cmdCount As New OracleCommand(sqlCount, myOraConn)

Dim countds As New DataSet
Dim countda As OracleDataAdapter
Dim countdt As DataTable
countda = New OracleDataAdapter(cmdCount)
countda.Fill(countds, "R")
countdt = countds.Tables("R")

Dim v_count = countdt.Rows(0).Item(0)

'Run get_clinic_filters

Dim gCFSQL As String = "ACPG_REGISTRY_PKG.GET_CLINIC_FILTERS"
Dim cmdGCF As New OracleCommand(gCFSQL, myOraConn)

cmdGCF.CommandType() = CommandType.StoredProcedure
cmdGCF.Parameters.Add(New OracleParameter("p_registry_id", OracleDbType.Int64)).Value = registryID
cmdGCF.Parameters.Add(New OracleParameter("p_facility_ncid", OracleDbType.Int64)).Value = FacilityID



Dim prm = New OracleParameter("curVar", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output)
cmdGCF.Parameters.Add(prm)
cmdGCF.ExecuteNonQuery()

Dim oraref As OracleRefCursor = cmdGCF.Parameters("curVar").Value
Dim dr As OracleDataReader = oraref.GetDataReader()


Dim numberOfRowsInReader = 0
While (dr.Read())
numberOfRowsInReader = numberOfRowsInReader + 1
End While


Assert.AreEqual(v_count, numberOfRowsInReader, "getClinic_Filters returned inconsistent results")


myOraConn.Close()
End Sub


Public Sub testGet_Initial_Clinic_Filters()
myOraConn.Open()


Dim iCFsql As String = "SELECT facility_ncid " _
& " FROM clinic, mtf_dmis_id " _
& " WHERE inactive = 0 " _
& " AND (meprs_code LIKE 'B___' OR meprs_code LIKE 'D___')" _
& " AND meprs_code NOT LIKE '__X_'" _
& " AND dmis_id_code = dmis_id " _
& " AND facility_service_code IS NOT NULL"

Dim cmdICF As New OracleCommand(iCFsql, myOraConn)

Dim iCFds As New DataSet
Dim iCFda As OracleDataAdapter
Dim iCFdt As DataTable
iCFda = New OracleDataAdapter(cmdICF)
iCFda.Fill(iCFds, "R")
iCFdt = iCFds.Tables("R")

Dim facilityNCID = iCFdt.Rows(0).Item(0)

'Get counts

Dim sqlCount As String = " SELECT NAME, meprs_code, ncid AS clinic_ncid, " _
& " facility_service_code " _
& " FROM clinic, mtf_dmis_id " _
& " WHERE facility_ncid = " & facilityNCID.ToString() & " And inactive = 0 " _
& " AND (meprs_code LIKE 'B___' OR meprs_code LIKE 'D___') " _
& " AND meprs_code NOT LIKE '__X_'" _
& " AND dmis_id_code = dmis_id " _
& " AND facility_service_code IS NOT NULL " _
& " UNION " _
& " SELECT MIN(description) AS NAME, code AS meprs_code, " _
& " NULL AS clinic_ncid, NULL AS facility_service_code " _
& " FROM meprs_code" _
& " WHERE active = 1 " _
& " AND (code LIKE 'B__' OR code LIKE 'D__')" _
& " AND code NOT LIKE '__X'" _
& " GROUP BY description, code "



Dim cmdCount As New OracleCommand(sqlCount, myOraConn)

Dim countds As New DataSet
Dim countda As OracleDataAdapter
Dim countdt As DataTable
countda = New OracleDataAdapter(cmdCount)
countda.Fill(countds, "R")
countdt = countds.Tables("R")

Dim v_count = countdt.Rows.Count

'Run get_clinic_filters

Dim gCFSQL As String = "ACPG_REGISTRY_PKG.GET_INITIAL_CLINIC_FILTERS"
Dim cmdGCF As New OracleCommand(gCFSQL, myOraConn)

cmdGCF.CommandType() = CommandType.StoredProcedure
cmdGCF.Parameters.Add(New OracleParameter("p_facility_ncid", OracleDbType.Int64)).Value = facilityNCID



Dim prm = New OracleParameter("curVar", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output)
cmdGCF.Parameters.Add(prm)
cmdGCF.ExecuteNonQuery()

Dim oraref As OracleRefCursor = cmdGCF.Parameters("curVar").Value
Dim dr As OracleDataReader = oraref.GetDataReader()


Dim numberOfRowsInReader = 0
While (dr.Read())
numberOfRowsInReader = numberOfRowsInReader + 1
End While


Assert.AreEqual(v_count, numberOfRowsInReader, "getInitialClinic_Filters returned inconsistent results")








myOraConn.Close()
End Sub
Public Sub testGet_Registry_Facility_Patients()
myOraConn.Open()
myOraConn.Close()
'Assert.AreEqual(1, 0, "This subroutine is not completed. It is not ready to test.")
End Sub
Public Sub testGet_Registry_Patient()
myOraConn.Open()
myOraConn.Close()
'Assert.AreEqual(1, 0, "This subroutine is not completed. It is not ready to test.")
End Sub


Public Sub testGet_Registry_Patients()
myOraConn.Open()
myOraConn.Close()
'Assert.AreEqual(1, 0, "This subroutine is not completed. It is not ready to test.")
End Sub
Public Sub testGet_Registry_Protocol_Items()
myOraConn.Open()
myOraConn.Close()
'Assert.AreEqual(1, 0, "This subroutine is not completed. It is not ready to test.")
End Sub
Public Sub testInsert_Clinic_Specific_Filters()
myOraConn.Open()
myOraConn.Close()
'Assert.AreEqual(1, 0, "This subroutine is not completed. It is not ready to test.")
End Sub


Public Sub testInsert_Clinic_Type_Filters()
myOraConn.Open()
myOraConn.Close()
'Assert.AreEqual(1, 0, "This subroutine is not completed. It is not ready to test.")
End Sub

End Class



Public Class testACPG_REMINDER_PKG
Dim oradb As String = "Data Source=(DESCRIPTION=" _
+ "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dev1cdr)(PORT=1521)))" _
+ "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PROD.World)));" _
+ "User Id=acpg;Password=acpg;"
Dim myOraConn As New OracleConnection(oradb)
Public Sub testNext_Due_Date()
myOraConn.Open()

Dim dayBeforeLeap As String = "to_date('02/28/2004', 'mm/dd/yyyy')"



Dim oneDaySQLDBL = "Select NVL(to_char(ACPG_REMINDER_PKG.next_due_date(1,1," & dayBeforeLeap & "), 'mm/dd/yyyy'), to_date('01/02/1903','mm/dd/yyyy')) from dual"

Dim oneDayResult As String



Dim cmdODR = New OracleCommand(oneDaySQLDBL, myOraConn)


Dim ODRds As New DataSet
Dim ODRda As OracleDataAdapter
Dim ODRdt As DataTable
ODRda = New OracleDataAdapter(cmdODR)
ODRda.Fill(ODRds, "R")
ODRdt = ODRds.Tables("R")


'Retrieve class variables for registry id and protocol id
oneDayResult = ODRdt.Rows(0).Item(0)


Assert.AreNotEqual("02-JAN-03", oneDayResult, "Next_Due_Date function returned an empty value")
Assert.AreEqual(oneDayResult, "29-FEB-04")











myOraConn.Close()
End Sub

' Public Sub testAddress_Reminder()
' myOraConn.Open()

' 'Not testable'

' myOraConn.Close()
'End Sub
Public Sub testAdd_Reminder_History()
'Variables
'A due date from acpg_reminder
'an event ID try 1
'a result try tlast_result from acpg_reminder
'a result date last result_date from acpg_reminder
'a result id try 1
'a unit number from acpg_reminder

myOraConn.Open()

Dim remSQL = "Select R.unit_number, R.protocol_item_id, R.date_due, " _
& " R.last_result, R.last_result_date " _
& " from acpg_reminder R where to_char(R.unit_number)||to_char(R.protocol_item_id) NOT IN " _
& " (Select to_char(RH.unit_number)||to_char(RH.protocol_item_id) from acpg_reminder_history RH) "


Dim cmdGetRem As New OracleCommand(remSQL, myOraConn)

Dim Remds As New DataSet
Dim Remda As OracleDataAdapter
Dim Remdt As DataTable
Remda = New OracleDataAdapter(cmdGetRem)
Remda.Fill(Remds, "R")
Remdt = Remds.Tables("R")

Dim v_date_due As Date

Dim v_unit_number As Integer = Remdt.Rows(0).Item(0)
Dim v_protocol_item_id As Integer = Remdt.Rows(0).Item(1)
If Not IsDBNull(Remdt.Rows(0).Item(2)) Then
v_date_due = Remdt.Rows(0).Item(2)
Else
v_date_due = Now
End If


Dim v_last_result As String

If Not (IsDBNull(Remdt.Rows(0).Item(3))) Then

v_last_result = Remdt.Rows(0).Item(3)

Else
v_last_result = ""
End If



Dim v_last_result_date As Date

If Not (IsDBNull(Remdt.Rows(0).Item(4))) Then

v_last_result_date = Remdt.Rows(0).Item(4)

Else
v_last_result_date = Now
End If


Dim v_result_id As Integer = 1
Dim v_event_id As Integer = 1

Dim remHistSQL As String = "ACPG_REMINDER_PKG.ADD_REMINDER_HISTORY"
Dim cmdAddRemHist As New OracleCommand(remHistSQL, myOraConn)
cmdAddRemHist.CommandType() = CommandType.StoredProcedure

cmdAddRemHist.Parameters.Add(New OracleParameter("p_date_due", OracleDbType.Date)).Value = v_date_due
cmdAddRemHist.Parameters.Add(New OracleParameter("p_event_id", OracleDbType.Int64)).Value = 1
cmdAddRemHist.Parameters.Add(New OracleParameter("p_protocol_item_id", OracleDbType.Int64)).Value = v_protocol_item_id
cmdAddRemHist.Parameters.Add(New OracleParameter("p_result", OracleDbType.Varchar2)).Value = v_last_result
cmdAddRemHist.Parameters.Add(New OracleParameter("p_result_date", OracleDbType.Date)).Value = v_last_result_date
cmdAddRemHist.Parameters.Add(New OracleParameter("p_result_id", OracleDbType.Int64)).Value = 1
cmdAddRemHist.Parameters.Add(New OracleParameter("p_unit_number", OracleDbType.Int64)).Value = v_unit_number

Dim prm = New OracleParameter("curVar", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output)
cmdAddRemHist.Parameters.Add(prm)




cmdAddRemHist.ExecuteNonQuery()

'See what it returned
Dim oraref As OracleRefCursor = cmdAddRemHist.Parameters("curVar").Value
Dim dr As OracleDataReader = oraref.GetDataReader()


Dim numberOfRowsInReader = 0
While (dr.Read())
numberOfRowsInReader = numberOfRowsInReader + 1
End While
myOraConn.Close()
Assert.Greater(numberOfRowsInReader, 0, "Add Reminder Failed")



myOraConn.Close()







End Sub
Public Sub testAddressResults()
myOraConn.Open()
myOraConn.Close()
'Assert.AreEqual(1, 0, "This subroutine is not completed. It is not ready to test.")
End Sub
Public Sub testProcess_Medcin_IDs()
myOraConn.Open()
'Check to see if there is anything to be processed
Dim Remsql = "select r.unit_number, m.medcin_id, r.protocol_item_id from " _
& " acpg_reminder r, acpg_protocol_item p, acpg_protocol_item_medcin_id m where" _
& " r.status In (1,3) AND " _
& " r.protocol_item_id = p.protocol_item_id AND " _
& " r.protocol_item_id = m.protocol_item_id "

Dim cmdGetRem As New OracleCommand(Remsql, myOraConn)

Dim Remds As New DataSet
Dim Remda As OracleDataAdapter
Dim Remdt As DataTable
Remda = New OracleDataAdapter(cmdGetRem)
Remda.Fill(Remds, "R")
Remdt = Remds.Tables("R")

Assert.Greater(Remdt.Rows.Count(), 0, "Nothing to test for Process_MedcinIDs")

Dim procMedSQL As String = "ACPG_REMINDER_PKG.PROCESS_MEDCIN_IDS"
Dim cmdProcMed As New OracleCommand(procMedSQL, myOraConn)
cmdProcMed.CommandType() = CommandType.StoredProcedure




Dim uniqueID As Integer

Dim longTime As Long = Now.ToFileTimeUtc()
uniqueID = longTime Mod 1000000000

cmdProcMed.Parameters.Add(New OracleParameter("p_unit_number", OracleDbType.Int64)).Value = Remdt.Rows(0).Item(0)
cmdProcMed.Parameters.Add(New OracleParameter("p_event_id", OracleDbType.Int64)).Value = uniqueID
cmdProcMed.Parameters.Add(New OracleParameter("p_result_date", OracleDbType.Date)).Value = Now
cmdProcMed.Parameters.Add(New OracleParameter("p_medcin_id_list", OracleDbType.Varchar2)).Value = Remdt.Rows(0).Item(1)
cmdProcMed.ExecuteNonQuery()

'Now lets see if it is now in the acpg_reminder_history table

Dim pMcheckSQL = "Select count(*) from acpg_reminder_history where event_id = " _
& uniqueID.ToString()


Dim cmdCheckPM As New OracleCommand(pMcheckSQL, myOraConn)

Dim PMds As New DataSet
Dim PMda As OracleDataAdapter
Dim PMdt As DataTable
PMda = New OracleDataAdapter(cmdCheckPM)
PMda.Fill(PMds, "R")
PMdt = PMds.Tables("R")

Assert.Greater(PMdt.Rows.Count(), 0, "Did not add to reminder_history_table")



myOraConn.Close()
End Sub
Public Sub testProcess_Encounter()


myOraConn.Open()

'Check to see if there is anything to be processed
Dim Remsql = "select a.unit_number, b.ref_item_id, a.date_added from " _
& " acpg_reminder a, acpg_protocol_item b where" _
& " a.status In (1,3) AND " _
& " a.protocol_item_id = b.protocol_item_id AND " _
& " b.ITEM_TYPE = 31"

Dim cmdGetRem As New OracleCommand(Remsql, myOraConn)

Dim Remds As New DataSet
Dim Remda As OracleDataAdapter
Dim Remdt As DataTable
Remda = New OracleDataAdapter(cmdGetRem)
Remda.Fill(Remds, "R")
Remdt = Remds.Tables("R")

Assert.Greater(Remdt.Rows.Count(), 0, "Nothing to test for Process_MedcinIDs")

Dim procMedSQL As String = "ACPG_REMINDER_PKG.PROCESS_ENCOUNTER"
Dim cmdProcMed As New OracleCommand(procMedSQL, myOraConn)
cmdProcMed.CommandType() = CommandType.StoredProcedure



Dim uniqueID As Integer

Dim longTime As Long = Now.ToFileTimeUtc()
uniqueID = longTime Mod 1000000000

cmdProcMed.Parameters.Add(New OracleParameter("p_unit_number", OracleDbType.Int64)).Value = Remdt.Rows(0).Item(0)
cmdProcMed.Parameters.Add(New OracleParameter("p_enc_num", OracleDbType.Varchar2)).Value = uniqueID
cmdProcMed.Parameters.Add(New OracleParameter("p_event_id", OracleDbType.Varchar2)).Value = uniqueID
cmdProcMed.Parameters.Add(New OracleParameter("p_result_date", OracleDbType.Date)).Value = Now
cmdProcMed.Parameters.Add(New OracleParameter("p_vitalsign_ncid_list", OracleDbType.Varchar2)).Value = Remdt.Rows(0).Item(1)
cmdProcMed.Parameters.Add(New OracleParameter("p_vitalsign_ncid_list", OracleDbType.Varchar2)).Value = ""
cmdProcMed.Parameters.Add(New OracleParameter("p_vitalsign_ncid_list", OracleDbType.Varchar2)).Value = ""
cmdProcMed.Parameters.Add(New OracleParameter("p_vitalsign_ncid_list", OracleDbType.Varchar2)).Value = ""
cmdProcMed.Parameters.Add(New OracleParameter("p_vitalsign_ncid_list", OracleDbType.Varchar2)).Value = ""
cmdProcMed.Parameters.Add(New OracleParameter("p_vitalsign_ncid_list", OracleDbType.Varchar2)).Value = ""
cmdProcMed.ExecuteNonQuery()

'Now lets see if it is now in the acpg_reminder_history table

Dim pMcheckSQL = "Select count(*) from acpg_reminder_history where event_id = " _
& uniqueID.ToString()


Dim cmdCheckPM As New OracleCommand(pMcheckSQL, myOraConn)

Dim PMds As New DataSet
Dim PMda As OracleDataAdapter
Dim PMdt As DataTable
PMda = New OracleDataAdapter(cmdCheckPM)
PMda.Fill(PMds, "R")
PMdt = PMds.Tables("R")

Assert.Greater(PMdt.Rows.Count(), 0, "Did not add to reminder_history_table")








End Sub




End Class

too many values Oracle error

ORA-00913: too many values
Cause: The SQL statement requires two sets of values equal in number. This error occurs when the second set contains more items than the first set. For example, the subquery in a WHERE or HAVING clause may return too many columns, or a VALUES or SELECT clause may return more columns than are listed in the INSERT.
Action: Check the number of items in each set and change the SQL statement to make them equal.

U Washington Course On Cryptography

http://www.cs.washington.edu/education/courses/csep590/06wi/lectures/

Examples Of Ints And Floats in C#

int count = 5;
float temperature = 99.5F;
//Float constants are suffixed with "F".
//Otherwise, they are considered as doubles.
//Doubles hold floating point values but have
//greater range and precision than floats.

Casting from string to an integer in VB.NET

Not all types can be casted to other types. If you attempt to illegally cast a type, you will get an error message informing you that the cast is illegal. In VB.NET, explicit casting can be used via the CType function. The syntax is as follows:

VariableOfTypeConvertToType = CType(ObjectToCast, ConvertToType)

How to get a computer to boot from a USB thumb drive

http://www.beezmo.com/FloobyDustDir/FDKnoppixUsbDir/FDKnoppixUsb.htm

Inserting into a CLOB

Just insert a string surrounded by single quotes.