Sorting Issue

Question

DelilahDemented 0 Newbie Poster

11 Years Ago

I have a custom class that consists of patient data. I am pulling data from three different sql tables and combining it into a list of patients. If the patient doesn't exist in the last table then I am inserting the patient into that table. From this last table, I am pulling a list of patients that are in the department for which I am looking. Once I get all the patients in this list, I display the list in a datagridview. All of this works, but I need to sort the final list by Room number so all the rooms are in order i.e. 401 A, 401 B, 402 A, 403 A, ect. I have an order by in my sql statements which works to an extent. When I run the program the first time, the patients that were already in the table are in order and the new ones are tagged on to the bottom. The next time, if no new patients are added, then it is in the correct sort order. I don't have a clue as to where to start on this. I have read about an IComparer, but do not know how such a thing would be implemented. I have posted my code to pull patients and my custom patient class. Any help, suggestions, tips, or advice is greatly appreciated!

  'Declare variables
        Dim DepartmentsNeeded As String() = DepartmentNumber.Split("|")
        Dim PatientListADT As New List(Of Patient)
        Dim PatientListCMSC As New List(Of Patient)
        Dim PatientListFinal As New List(Of Patient)

        'Open and read
        Using con As New SqlConnection(My.Settings.CPSISQLConnectionString)
            con.Open()
            Dim cmd As New SqlCommand(ADTLOSString, con)
            Dim reader1 As SqlDataReader
            reader1 = cmd.ExecuteReader

            'Populate tempPatient1 with Data from ADTLOS tables
            While reader1.Read
                Dim tempPatient1 As New Patient
                With tempPatient1
                    .PatientNumber = NewCStr(reader1("PatNum"))
                    .Room = NewCStr(reader1("Room"))
                    .Department = NewCStr(reader1("Department"))
                    .FC = NewCStr(reader1("FinClass"))
                    .Type = NewCInt(reader1("Type"))
                    .PatientName = NewCStr(reader1("PatientName"))
                    .AttendingPhy = NewCStr(reader1("AttendingPhy"))
                    If NewCStr(reader1("AdmitDate")) <> "" Then
                        Dim value As String = reader1("AdmitDate")
                        .AdmitDate = NewCDate(value.Substring(0, 2) & "/" & value.Substring(2, 2) & "/" & value.Substring(4, 4))
                    End If
                    .AdmitTime = NewCStr(reader1("AdmitTime"))
                    .AdmitSource = NewCStr(reader1("AdmitSource"))
                    If NewCStr(reader1("DischargeDate")) <> "" Then
                        Dim value As String = reader1("DischargeDate")
                        If value.Count = 8 Then
                            .DischargeDate = NewCDate(value.Substring(0, 2) & "/" & value.Substring(2, 2) & "/" & value.Substring(4, 4))
                        End If
                    End If
                    .DischargeTime = NewCStr(reader1("DischargeTime"))
                    .CurrentDRG = NewCStr(reader1("DRG"))
                    .PLOS = NewCStr(reader1("PLOS"))
                    .ALOS = NewCStr(reader1("ALOS"))
                End With

                'Add Patient to the ADTLOS Patient List
                PatientListADT.Add(tempPatient1)
            End While

            'Close Reader1
            reader1.Close()

            'Declare variables for Reader1
            cmd = New SqlCommand(CMSCString, con)
            reader1 = cmd.ExecuteReader

            'Populate tempPatient2 with Data from CMSCORECARD table
            While reader1.Read
                Dim tempPatient2 As New Patient
                With tempPatient2
                    .PatientNumber = NewCStr(reader1("PatNum"))
                    .Room = NewCStr(reader1("Room"))
                    .Department = NewCStr(reader1("Department"))
                    .FC = NewCStr(reader1("FinClass"))
                    .Type = NewCInt(reader1("Type"))
                    .PatientName = NewCStr(reader1("PatientName"))
                    .AttendingPhy = NewCStr(reader1("AttendingPhy"))
                    .AdmitDate = NewCDate(reader1("AdmitDate"))
                    .AdmitTime = NewCStr(reader1("AdmitTime"))
                    .AdmitSource = NewCStr(reader1("AdmitSource"))
                    .DischargeDate = NewCDate(reader1("DischargeDate"))
                    .DischargeTime = NewCStr(reader1("DischargeTime"))
                    .LastDRG = NewCStr(reader1("LastDRG"))
                    .CurrentDRG = NewCStr(reader1("CurrentDRG"))
                    .Level = NewCInt(reader1("Level"))
                    .PLOS = NewCStr(reader1("PLOS"))
                    .ALOS = NewCStr(reader1("ALOS"))
                    .SeverityOfIllness = NewCStr(reader1("SI"))
                    .IntensityOfService = NewCStr(reader1("IntensityOfService"))
                    .DCP = NewCStr(reader1("DCP"))
                    .DCDate = NewCDate(reader1("DCDate"))
                    .MD = NewCStr(reader1("MD"))
                    .PA = NewCStr(reader1("PA"))
                    .DCM = NewCStr(reader1("DCM"))
                    .PAD = NewCStr(reader1("PAD"))
                    .Comments = NewCStr(reader1("Comments"))
                    .DeniedDays = NewCInt(reader1("DD"))
                    .SaveDays = NewCInt(reader1("SD"))
                    .Completed = NewCStr(reader1("Completed"))
                End With

                'Add Patient to the CMSCORECARD Patient List
                PatientListCMSC.Add(tempPatient2)
            End While

            'Close reader
            reader1.Close()

            'Combine two Lists/If patient isn't in list, then added to sql table
            Dim PatientInList As Boolean
            For Each person As Patient In PatientListADT
                'Set PatientInList Default Value
                PatientInList = False

                'Find matching Patients
                For i As Integer = 0 To PatientListCMSC.Count - 1
                    If Trim(person.PatientNumber) = Trim(PatientListCMSC(i).PatientNumber) Then

                        'Update Room in CMSCORECARD table
                        If PatientListCMSC(i).Room <> person.Room Then
                            PatientListCMSC(i).Room = person.Room
                            UpdateScorecardTable("[ROOM]", person.Room, person.PatientNumber)
                        End If

                        'Update Department in CMSCORECARD table
                        If PatientListCMSC(i).Department <> person.Department Then
                            PatientListCMSC(i).Department = person.Department
                            UpdateScorecardTable("[DEPARTMENT]", person.Department, person.PatientNumber)
                        End If

                        'Update FC in CMSCORECARD table
                        If PatientListCMSC(i).FC <> person.FC Then
                            PatientListCMSC(i).FC = person.FC
                            UpdateScorecardTable("[FC]", person.FC, person.PatientNumber)
                        End If

                        'Update Patient Type in CMSCORECARD table
                        If PatientListCMSC(i).Type <> person.Type Then
                            PatientListCMSC(i).Type = person.Type
                            UpdateScorecardTable("[Patient_Type]", person.Type, person.PatientNumber)
                        End If

                        'Update Patient Name in CMSCORECARD table
                        If PatientListCMSC(i).PatientName <> person.PatientName Then
                            PatientListCMSC(i).PatientName = person.PatientName
                            UpdateScorecardTable("[PATIENT_NAME]", person.PatientName, person.PatientNumber)
                        End If

                        'Update Attending Physician in CMSCORECARD table
                        If PatientListCMSC(i).AttendingPhy <> person.AttendingPhy Then
                            PatientListCMSC(i).AttendingPhy = person.AttendingPhy
                            UpdateScorecardTable("[PHYSICIAN]", person.AttendingPhy, person.PatientNumber)
                        End If

                        'Update Admit Date in CMSCORECARD table
                        If PatientListCMSC(i).AdmitDate <> person.AdmitDate Then
                            PatientListCMSC(i).AdmitDate = person.AdmitDate
                            UpdateScorecardTable("[ADMIT_DATE]", person.AdmitDate, person.PatientNumber)
                        End If

                        'Update Admit Time in CMSCORECARD table
                        If PatientListCMSC(i).AdmitTime <> person.AdmitTime Then
                            PatientListCMSC(i).AdmitTime = person.AdmitTime
                            UpdateScorecardTable("[ADMIT_TIME]", person.AdmitTime, person.PatientNumber)
                        End If

                        'Update Admit Source in CMSCORECARD table
                        If PatientListCMSC(i).AdmitSource <> person.AdmitSource Then
                            PatientListCMSC(i).AdmitSource = person.AdmitSource
                            UpdateScorecardTable("[ADMIT_SOURCE]", person.AdmitSource, person.PatientNumber)
                        End If

                        'Update Discharge Date in CMSCORECARD table
                        If PatientListCMSC(i).DischargeDate <> person.DischargeDate Then
                            PatientListCMSC(i).DischargeDate = person.DischargeDate
                            UpdateScorecardTable("[DISCHARGE_DATE]", person.DischargeDate, person.PatientNumber)
                        End If

                        'Update Discharge Time in CMSCORECARD table
                        If PatientListCMSC(i).DischargeTime <> person.DischargeTime Then
                            PatientListCMSC(i).DischargeTime = person.DischargeTime
                            UpdateScorecardTable("[DISCHARGE_TIME]", person.DischargeTime, person.PatientNumber)
                        End If

                        'Check for Different DRG in CMSCORECARD table
                        If PatientListCMSC(i).LastDRG <> PatientListCMSC(i).CurrentDRG Then
                            PatientListCMSC(i).LastDRG = PatientListCMSC(i).CurrentDRG
                            UpdateScorecardTable("[LAST_DRG]", PatientListCMSC(i).CurrentDRG, person.PatientNumber)
                        End If

                        'Update Current DRG in CMSCORECARD table
                        If PatientListCMSC(i).CurrentDRG <> person.CurrentDRG Then
                            PatientListCMSC(i).CurrentDRG = person.CurrentDRG
                            UpdateScorecardTable("[CURRENT_DRG]", person.CurrentDRG, person.PatientNumber)
                        End If

                        'Update ALOS in CMSCORECARD table
                        If PatientListCMSC(i).ALOS <> person.ALOS Then
                            PatientListCMSC(i).ALOS = person.ALOS
                            UpdateScorecardTable("[ALOS]", person.ALOS, person.PatientNumber)
                        End If

                        'Update PLOS in CMSCORECARD table
                        If PatientListCMSC(i).PLOS <> person.PLOS Then
                            PatientListCMSC(i).PLOS = person.PLOS
                            UpdateScorecardTable("[PLOS]", person.PLOS, person.PatientNumber)
                        End If

                        'Patient Exists in the Table
                        PatientInList = True
                        Exit For
                    End If
                Next

                'Patient Not found in the CMSCORECARD table/Add Patient to Table
                If PatientInList = False Then
                    'Add the patient to the list
                    PatientListCMSC.Add(person)

                    'Build insert string for New Patient in List
                    cmd = New SqlCommand("INSERT INTO CMSCORECARD " &
                        "(CMSCORECARD.[PATNUM], " &
                        "CMSCORECARD.[ROOM], " &
                        "CMSCORECARD.[FC], " &
                        "CMSCORECARD.[Patient_Type], " &
                        "CMSCORECARD.[PATIENT_NAME], " &
                        "CMSCORECARD.[PHYSICIAN], " &
                        "CMSCORECARD.[ADMIT_DATE], " &
                        "CMSCORECARD.[ADMIT_TIME], " &
                        "CMSCORECARD.[ADMIT_SOURCE], " &
                        "CMSCORECARD.[LAST_DRG], " &
                        "CMSCORECARD.[CURRENT_DRG], " &
                        "CMSCORECARD.[PLOS], " &
                        "CMSCORECARD.[ALOS], " &
                        "CMSCORECARD.[DEPARTMENT], " &
                        "CMSCORECARD.[SI], " &
                        "CMSCORECARD.[IS], " &
                        "CMSCORECARD.[DCP], " &
                        "CMSCORECARD.[DC_Date], " &
                        "CMSCORECARD.[MD], " &
                        "CMSCORECARD.[PA], " &
                        "CMSCORECARD.[DCM], " &
                        "CMSCORECARD.[PAD], " &
                        "CMSCORECARD.[LEVEL], " &
                        "CMSCORECARD.[COMMENTS], " &
                        "CMSCORECARD.[DD], " &
                        "CMSCORECARD.[SD], " &
                        "CMSCORECARD.[DISCHARGE_DATE], " &
                        "CMSCORECARD.[DISCHARGE_TIME], " &
                        "CMSCORECARD.[COMPLETED]) " &
                        "VALUES " &
                        "(" &
                        "'" & person.PatientNumber & "'," &
                        "'" & person.Room & "'," &
                        "'" & person.FC & "'," &
                        "'" & person.Type & "'," &
                        "'" & person.PatientName & "'," &
                        "'" & person.AttendingPhy & "'," &
                        "'" & person.AdmitDate & "', " &
                        "'" & person.AdmitTime & "'," &
                        "'" & person.AdmitSource & "'," &
                        "'" & person.CurrentDRG & "'," &
                        "'" & person.CurrentDRG & "'," &
                        "'" & person.PLOS & "'," &
                        "'" & person.ALOS & "'," &
                        "'" & person.Department & "'," &
                        "NULL, " &
                        "NULL, " &
                        "NULL, " &
                        "NULL, " &
                        "NULL, " &
                        "NULL, " &
                        "NULL, " &
                        "NULL, " &
                        "NULL, " &
                        "NULL, " &
                        "NULL, " &
                        "NULL, " &
                        "" & StrToSql(person.DischargeDate) & ", " &
                        "" & StrToSql(person.DischargeTime) & ", " &
                        "NULL" &
                        ")", con)

                    'Execute SQL String
                    cmd.ExecuteNonQuery()
                End If
            Next

            'Filter for Needed Department and place person in the Final list for Display
            For Each dept As String In DepartmentsNeeded
                For Each person In PatientListCMSC
                    If person.Department = dept Then
                        PatientListFinal.Add(person)
                    End If
                Next
            Next

            'Populate DataGridView with the Patient List
            With DataGridView1
                .DataSource = PatientListFinal
            End With

            'Close connection/Dispose
            con.Close()
            cmd.Dispose()
        End Using
    End Sub


    Public Class Patient
    'Patient Number
    Private _PatientNumber As String
    Public Property PatientNumber() As String
        Get
            Return _PatientNumber
        End Get
        Set(ByVal value As String)
            _PatientNumber = value
        End Set
    End Property

    'Patient Room Number
    Private _Room As String
    Public Property Room() As String
        Get
            Return _Room
        End Get
        Set(ByVal value As String)
            _Room = value
        End Set
    End Property

    'Financial Class
    Private _FC As String
    Public Property FC() As String
        Get
            Return _FC
        End Get
        Set(ByVal value As String)
            _FC = value
        End Set
    End Property

    'Patient Type
    Private _Type As Integer
    Public Property Type() As String
        Get
            Return _Type
        End Get
        Set(ByVal value As String)
            _Type = value
        End Set
    End Property

    'Patient Name
    Private _PatientName As String
    Public Property PatientName() As String
        Get
            Return _PatientName
        End Get
        Set(ByVal value As String)
            _PatientName = value
        End Set
    End Property

    'Attending Physician
    Private _AttendingPhy As String
    Public Property AttendingPhy() As String
        Get
            Return _AttendingPhy
        End Get
        Set(ByVal value As String)
            _AttendingPhy = value
        End Set
    End Property

    'Admit Date
    Private _AdmitDate As Date
    Public Property AdmitDate() As Date
        Get
            Return _AdmitDate
        End Get
        Set(ByVal value As Date)
            _AdmitDate = value
        End Set
    End Property

    'Admit Time
    Private _AdmitTime As String
    Public Property AdmitTime() As String
        Get
            Return _AdmitTime
        End Get
        Set(ByVal value As String)
            _AdmitTime = value
        End Set
    End Property

    'Admit Source
    Private _AdmitSource As String
    Public Property AdmitSource() As String
        Get
            Return _AdmitSource
        End Get
        Set(ByVal value As String)
            _AdmitSource = value
        End Set
    End Property

    'Last DRG
    Private _LastDRG As String
    Public Property LastDRG() As String
        Get
            Return _LastDRG
        End Get
        Set(ByVal value As String)
            _LastDRG = value
        End Set
    End Property

    'Current DRG
    Private _CurrentDRG As String
    Public Property CurrentDRG() As String
        Get
            Return _CurrentDRG
        End Get
        Set(ByVal value As String)
            _CurrentDRG = value
        End Set
    End Property

    'Predicted Length of Stay
    Private _PLOS As String
    Public Property PLOS() As String
        Get
            Return _PLOS
        End Get
        Set(ByVal value As String)
            _PLOS = value
        End Set
    End Property

    'Actual Length of Stay
    Private _ALOS As String
    Public Property ALOS() As String
        Get
            Return _ALOS
        End Get
        Set(ByVal value As String)
            _ALOS = value
        End Set
    End Property

    'SI - Severity of Illness
    Private _SeverityOfIllness As String
    Public Property SeverityOfIllness() As String
        Get
            Return _SeverityOfIllness
        End Get
        Set(ByVal value As String)
            _SeverityOfIllness = value
        End Set
    End Property

    'IS Intensity of Service
    Private _IntensityOfService As String
    Public Property IntensityOfService() As String
        Get
            Return _IntensityOfService
        End Get
        Set(ByVal value As String)
            _IntensityOfService = value
        End Set
    End Property

    'Discharge Plan
    Private _DCP As String
    Public Property DCP() As String
        Get
            Return _DCP
        End Get
        Set(ByVal value As String)
            _DCP = value
        End Set
    End Property

    'Expected Discharge Date
    Private _DCDate As Date
    Public Property DCDate() As Date
        Get
            Return _DCDate
        End Get
        Set(ByVal value As Date)
            _DCDate = value
        End Set
    End Property

    'MD
    Private _MD As String
    Public Property MD() As String
        Get
            Return _MD
        End Get
        Set(ByVal value As String)
            _MD = value
        End Set
    End Property

    'Physician Advisor
    Private _PA As String
    Public Property PA() As String
        Get
            Return _PA
        End Get
        Set(ByVal value As String)
            _PA = value
        End Set
    End Property

    'Director of Case Management
    Private _DCM As String
    Public Property DCM() As String
        Get
            Return _DCM
        End Get
        Set(ByVal value As String)
            _DCM = value
        End Set
    End Property

    'Possible Avoidable Days
    Private _PAD As String
    Public Property PAD() As String
        Get
            Return _PAD
        End Get
        Set(ByVal value As String)
            _PAD = value
        End Set
    End Property

    'Level of Service
    Private _Level As Integer
    Public Property Level() As Integer
        Get
            Return _Level
        End Get
        Set(ByVal value As Integer)
            _Level = value
        End Set
    End Property

    'Comments
    Private _Comments As String
    Public Property Comments() As String
        Get
            Return _Comments
        End Get
        Set(ByVal value As String)
            _Comments = value
        End Set
    End Property

    'Denied Days
    Private _DeniedDays As Integer
    Public Property DeniedDays() As Integer
        Get
            Return _DeniedDays
        End Get
        Set(ByVal value As Integer)
            _DeniedDays = value
        End Set
    End Property

    'Save Days
    Private _SaveDays As Integer
    Public Property SaveDays() As Integer
        Get
            Return _SaveDays
        End Get
        Set(ByVal value As Integer)
            _SaveDays = value
        End Set
    End Property

    'Discharge Date
    Private _DischargeDate As Date
    Public Property DischargeDate() As Date
        Get
            Return _DischargeDate
        End Get
        Set(ByVal value As Date)
            _DischargeDate = value
        End Set
    End Property

    'Discharge Time
    Private _DischargeTime As String
    Public Property DischargeTime() As String
        Get
            Return _DischargeTime
        End Get
        Set(ByVal value As String)
            _DischargeTime = value
        End Set
    End Property

    'Completed
    Private _Completed As String
    Public Property Completed() As String
        Get
            Return _Completed
        End Get
        Set(ByVal value As String)
            _Completed = value
        End Set
    End Property

    'Patient Department
    Private _Department As String
    Public Property Department() As String
        Get
            Return _Department
        End Get
        Set(ByVal value As String)
            _Department = value
        End Set
    End Property
End Class

sort vb.net

2 Contributors
3 Replies
192 Views
1 Day Discussion Span
Latest Post 11 Years Ago Latest Post by DelilahDemented

All 3 Replies

TnTinMN 418 Practically a Master Poster

11 Years Ago

Assuming all room numbers follow the pattern, [Number][Space][Letter], adding this class to your project this should do the trick.

  Friend Class PatientRoomComparer
     Implements IComparer(Of Patient)

     Public Function Compare(ByVal x As Patient, ByVal y As Patient) As Integer Implements System.Collections.Generic.IComparer(Of Patient).Compare
        ' For Ascending Order
        ' x < y : return -1;  x = y : return 0; x > y : return 1

        Dim xparts(), yparts() As String
        Dim splitter() As Char = {" "c}

        xparts = x.Room.Split(splitter, System.StringSplitOptions.RemoveEmptyEntries)
        yparts = y.Room.Split(splitter, System.StringSplitOptions.RemoveEmptyEntries)

        If xparts.Length = 0 OrElse yparts.Length = 0 Then
           Throw New ArgumentException("Invalid Room Number")
        End If

        Dim xnum, ynum As Int32
        Int32.TryParse(xparts(0), xnum)
        Int32.TryParse(yparts(0), ynum)

        ' 1st compare the number part
        Dim returnvalue As Int32 = xnum.CompareTo(ynum)

        If returnvalue = 0 Then ' numbers are equal, compare letters
           If xparts.Length = 1 Then
             ' no letter component for x : x <= y
             ' for sorting purposes can treat as <
             Return -1

           End If
           If yparts.Length = 1 Then
             ' no letter component for y : x >=y
             ' for sorting purposes can treat as >
             Return 1
           End If

           ' If this point is reached, compare the letters
           Return xparts(1).CompareTo(yparts(1))

        Else

           Return returnvalue

        End If
     End Function
  End Class

Then to do perform the actual sorting: PatientListFinal.Sort(New PatientRoomComparer)

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

TnTinMN 418 Practically a Master Poster · Answer 1 · 2012-10-12T02:36:02+00:00

It just occured to me that if the room number does not have a letter component that the comparer will fail for two equal room numbers. Here is a corrected version.

Friend Class PatientRoomComparer
     Implements IComparer(Of Patient)

     Public Function Compare(ByVal x As Patient, ByVal y As Patient) As Integer Implements System.Collections.Generic.IComparer(Of Patient).Compare
        ' For Ascending Order
        ' x < y : return -1;  x = y : return 0; x > y : return 1

        Dim xparts(), yparts() As String
        Dim splitter() As Char = {" "c}

        xparts = x.Room.Split(splitter, System.StringSplitOptions.RemoveEmptyEntries)
        yparts = y.Room.Split(splitter, System.StringSplitOptions.RemoveEmptyEntries)

        If xparts.Length = 0 OrElse yparts.Length = 0 Then
           Throw New ArgumentException("Invalid Room Number")
        End If

        Dim xnum, ynum As Int32
        Int32.TryParse(xparts(0), xnum)
        Int32.TryParse(yparts(0), ynum)

        ' 1st compare the number part
        Dim returnvalue As Int32 = xnum.CompareTo(ynum)

        If returnvalue = 0 Then ' numbers are equal, compare letters
           If xparts.Length = 1 AndAlso yparts.Length = 1 Then
             ' no letter component for x : x < y
             Return 0
           End If

           If xparts.Length = 1 Then
             ' no letter component for x : x <y
             Return -1

           End If
           If yparts.Length = 1 Then
             ' no letter component for y : x >y
             Return 1
           End If

           ' If this point is reached, compare the letters
           Return xparts(1).CompareTo(yparts(1))

        Else

           Return returnvalue

        End If
        Debug.WriteLine(Compare.ToString)
     End Function
  End Class

DelilahDemented 0 Newbie Poster · Answer 2 · 2012-10-13T00:38:43+00:00

Thanks for the response. I will try it when I get to work on Monday!

Sorting Issue

Recommended Answers Collapse Answers

All 3 Replies

Recommended Answers