Public Class frmListAdmitted

    Private Sub selectS(ByVal condition As String)
        'condition = "AND STATION=" & cboStation.SelectedValue & " AND ROOMID =" & cboRoom.SelectedValue & " AND BEDID=" & cboBed.SelectedValue
        sql = "SELECT DISTINCT ( ADMITTINGID ),p.`CASENUM` as 'Case #', CONCAT(  `FNAME` ,  ' ',  `MNAME` ,  ' ',  `LNAME` ) AS  'Patient',  `DOCNAME` AS  'Physician',  `STATION` AS  'Station',  `RNAME` AS  'Room',  `DIAGNOSIS` AS  'Diagnosis',BEDNUM as 'Bed',DATEADMIT AS 'Date Admitted' " & _
               " FROM  `tblpatient` p, tbldoctor d, `tbladmitting` a,  `tblroom` r,  `tblbed` b " & _
               " WHERE p.`CASENUM` = a.`CASENUM` " & _
               " AND a.`DOCID` = D.`DOCID` " & _
               " AND a.`ROOMID` = r.`ROOMID` " & _
               " AND a.`BEDID` = b.`BEDID` " & _
               " AND ADMITTED=1 AND B.STATUS='NotAvailable' " & condition
        RETRIEVEDTG(dtgList, sql)
        dtgList.Columns(0).Visible = False
        dtgList.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
    End Sub


    Private Sub frmListAdmitted_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            sql = "SELECT DISTINCT ( `ADMITTINGID` ),p.`CASENUM` as 'Case #', CONCAT(  `FNAME` ,  ' ',  `MNAME` ,  ' ',  `LNAME` ) AS  'Patient',  `DOCNAME` AS  'Physician',  `STATION` AS  'Station',  `RNAME` AS  'Room',  `DIAGNOSIS` AS  'Diagnosis',BEDNUM as 'Bed',DATEADMIT AS 'Date Admitted' " & _
                          " FROM  `tblpatient` p,tbldoctor d,  `tbladmitting` a,  `tblroom` r,  `tblbed` b " & _
                          " WHERE p.`CASENUM` = a.`CASENUM` " & _
                           " AND a.`DOCID` = D.`DOCID` " & _
                          " AND a.`ROOMID` = r.`ROOMID` " & _
                          " AND a.`BEDID` = b.`BEDID` " & _
                          " AND B.STATUS='NotAvailable' AND ADMITTED=1 "
            RETRIEVEDTG(dtgList, sql)
            dtgList.Columns(0).Visible = False
            dtgList.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)

            cboStation.Refresh()
            sql = "SELECT DISTINCT ( `STATION` )  FROM tblroom"
            fillcbo(sql, cboStation, "STATION", "STATION")

            'sql = "SELECT * FROM tblroom"
            'fillcbo(sql, cboRoom, "RNAME", "ROOMID")
        Catch ex As Exception

        End Try

    End Sub

    Private Sub cboStation_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboStation.SelectedIndexChanged
        Try

            If cboStation.Text = "Select" Then
                cboRoom.Refresh()
            Else
                cboRoom.Refresh()
                sql = "SELECT * FROM tblroom WHERE STATION=" & cboStation.SelectedValue
                fillcbo(sql, cboRoom, "RNAME", "ROOMID")

                condition = "AND STATION=" & cboStation.SelectedValue

                selectS(condition)

            End If


        Catch ex As Exception
            'MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub cboRoom_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboRoom.SelectedIndexChanged
        Try 

            If cboRoom.Text = "Select" Then
                cboBed.Refresh()
            Else
                cboBed.Refresh()
                sql = "SELECT BEDID, BEDNUM  FROM tblbed WHERE ROOMID=" & cboRoom.SelectedValue
                fillcbo(sql, cboBed, "BEDNUM", "BEDID")

                condition = "AND STATION=" & cboStation.SelectedValue & " AND r.ROOMID =" & cboRoom.SelectedValue

                selectS(condition)
            End If

        Catch ex As Exception
            'MsgBox(ex.Message)
        End Try
    End Sub
    Dim condition As String
    Private Sub cboBed_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboBed.SelectedIndexChanged
        Try
            condition = "AND STATION=" & cboStation.SelectedValue & " AND r.ROOMID =" & cboRoom.SelectedValue & " AND b.BEDID=" & cboBed.SelectedValue

            selectS(condition)
        Catch ex As Exception

        End Try
    End Sub

   

    Private Sub cboStation_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles cboStation.KeyPress, cboRoom.KeyPress, cboBed.KeyPress
        e.Handled = True
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

  
    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Try

            sql = "UPDATE  `tbladmitting` A, `tblbed` B  SET `STATUS`='Available' WHERE A.`BEDID`=B.`BEDID` AND `CASENUM` =" & dtgList.CurrentRow.Cells(1).Value
            result = CUD(sql)
            If result = False Then
                MsgBox("Query erorr tblbed")
            End If

            sql = "DELETE  FROM `tbladmitting` WHERE `CASENUM`=" & dtgList.CurrentRow.Cells(1).Value & "  AND date(`DATEADMIT`)='" & Format(dtgList.CurrentRow.Cells(8).Value, "yyyy-MM-dd") & "'"
            result = CUD(sql)
            If result = False Then
                MsgBox("Query erorr tbladmitting")
            End If

            sql = "DELETE  FROM `tblmedication` WHERE `CASENUM`=" & dtgList.CurrentRow.Cells(1).Value & "  AND date(`MEDICATIONDATE`)='" & Format(dtgList.CurrentRow.Cells(8).Value, "yyyy-MM-dd") & "'"
            result = CUD(sql)
            If result = False Then
                MsgBox("Query erorr tblmedication")
            End If


            sql = "UPDATE `tblpatient` SET `ADMITTED`=0 WHERE `CASENUM` =" & dtgList.CurrentRow.Cells(1).Value
            result = CUD(sql)
            If result = False Then
                MsgBox("Query erorr tblpatient")
            End If
 
            Call frmListAdmitted_Load(sender, e)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Class