Imports MySql.Data.MySqlClient
Module selects
    Public con As MySqlConnection = mysqldb()
    'procedure of your autoappend and autosugest
    Public Sub autocompletetxt(ByVal sql As String, ByVal txt As TextBox)
        Try
            dt = New DataTable
            'OPENING THE CONNECTION
            con.Open()
            'HOLDS THE DATA TO BE EXECUTED
            With cmd
                .Connection = con
                .CommandText = sql
            End With
            'FILLING THE DATA IN THE DATATABLE
            da.SelectCommand = cmd
            da.Fill(dt)
            'SET A VARIABLE AS A ROW OF DATA IN THE DATATABLE
            Dim r As DataRow
            'CLEARING THE AUTOCOMPLETE SOURCE OF THE TEXTBOX

            With txt
                .AutoCompleteMode = AutoCompleteMode.SuggestAppend
                .AutoCompleteSource = AutoCompleteSource.CustomSource
                .AutoCompleteCustomSource.Clear()
            End With
            'LOOPING THE ROW OF DATA IN THE DATATABLE
            For Each r In dt.Rows
                'ADDING THE DATA IN THE AUTO COMPLETE SOURCE OF THE TEXTBOX
                txt.AutoCompleteCustomSource.Add(r.Item(0).ToString)
            Next
            ''''''''''''''''''''''''
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        'CLOSING THE CONNECTION
        con.Close()
        da.Dispose()
    End Sub
    Public Sub auto_sugestAll(ByVal sql As String, ByVal txt As Object)
        With cmd
            .Connection = con
            .CommandText = sql
        End With
        dt = New DataTable
        da = New MySqlDataAdapter(sql, con)
        da.Fill(dt)
        Dim r As DataRow
        txt.AutoCompleteCustomSource.Clear()
        For Each r In dt.Rows
            txt.AutoCompleteCustomSource.Add(r.Item(0).ToString)
            txt.AutoCompleteCustomSource.Add(r.Item(1).ToString)
            txt.AutoCompleteCustomSource.Add(r.Item(2).ToString)
        Next
    End Sub
    Public Function fillcboD(ByVal sql As String, ByVal cbo As ComboBox, ByVal display As String, ByVal ID As String) As Boolean
        Try

            If con.State = ConnectionState.Open Then
                con.Close()
            Else
                con.Open()
                'OPENING THE CONNECTION
                cmd = New MySqlCommand
                'HOLDS THE DATA TO BE EXECUTED
                With cmd
                    .Connection = con
                    .CommandText = sql
                End With
                'FILLING THE DATA IN THE DATATABLE
                da = New MySqlDataAdapter
                da.SelectCommand = cmd
                dt = New DataTable
                da.Fill(dt)
                If dt.Rows.Count > 0 Then
                    With cbo
                        .Refresh()
                        .DataSource = dt
                        .DisplayMember = display
                        .ValueMember = ID
                        .SelectedIndex = 0
                        .Text = ""
                    End With

                    Return True
                Else

                    Return False
                End If

            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            'CLOSING THE CONNECTION
            con.Close()
            da.Dispose()
        End Try

    End Function
    Public Function fillcbo(ByVal sql As String, ByVal cbo As ComboBox, ByVal display As String, ByVal ID As String) As Boolean
        Try

            If con.State = ConnectionState.Open Then
                con.Close()
            Else 
                con.Open()
                'OPENING THE CONNECTION
                cmd = New MySqlCommand
                'HOLDS THE DATA TO BE EXECUTED
                With cmd
                    .Connection = con
                    .CommandText = sql
                End With
                'FILLING THE DATA IN THE DATATABLE
                da = New MySqlDataAdapter
                da.SelectCommand = cmd
                dt = New DataTable
                da.Fill(dt)
                If dt.Rows.Count > 0 Then
                    With cbo
                        .Refresh()
                        .DataSource = dt
                        .DisplayMember = display
                        .ValueMember = ID
                        '.SelectedValue = "0"
                        .SelectedIndex = 0
                        .Text = "Select"

                    End With

                    Return True
                Else

                    Return False
                End If

            End If 
        Catch ex As Exception
            'MsgBox(ex.Message)
        Finally
            'CLOSING THE CONNECTION
            con.Close()
            da.Dispose()
        End Try

    End Function
    Public Sub autoreceipt(ByVal desc As String, ByVal txt As Object)
        Try
            sql = "SELECT concat(`STRT`, `END`) FROM `tblautonumber` WHERE `DESCRIPTION`= '" & desc & "'"
            reloadtxt(sql)
            txt.text = dt.Rows(0).Item(0)
        Catch ex As Exception
            ' MsgBox(ex.Message)
        End Try

    End Sub
    Public Sub autoreceiptupdate(ByVal desc As String)
        Try
            sql = "UPDATE `tblautonumber` SET `END`=`END`+`INCREMENT` WHERE `DESCRIPTION`='" & desc & "'"
            createNoMsg(sql)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub
    Public Sub loadautonumbers(ByVal txt As TextBox)
        Try
            sql = "SELECT CONCAT(`STRT`, `END`) FROM `tblautonumber` WHERE  `ID`=2"
            reloadtxt(sql)
            txt.Text = dt.Rows(0).Item(0)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub
    Public Sub loadautonumber(ByVal id As Integer, ByVal txt As TextBox)
        Try
            sql = "SELECT CONCAT(`STRT`, `END`) FROM `tblautonumber` WHERE  `ID`= " & id
            reloadtxt(sql)
            txt.Text = dt.Rows(0).Item(0).ToString
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub
    Public Sub loadautonumbercateg(ByVal desc As String, ByVal txt As Object)
        Try
            sql = "SELECT concat(`STRT`, `END`) FROM `tblautonumber` WHERE `DESCRIPTION`= '" & desc & "'"
            reloadtxt(sql)
            txt.Text = dt.Rows(0).Item(0)
        Catch ex As Exception
            'MsgBox(ex.Message)
        End Try

    End Sub
    Public Sub updateautonumbercateg(ByVal desc As String)
        Try
            sql = "UPDATE `tblautonumber` SET `END`=`END`+`INCREMENT` WHERE `DESCRIPTION`='" & desc & "'"
            createNoMsg(sql)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Public Sub updateautonumber(ByVal id As Integer)
        Try
            sql = "UPDATE `tblautonumber` SET `END`=`END`+`INCREMENT` WHERE `ID`=" & id
            createNoMsg(sql)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Public Sub variableautonum(ByVal id As String, ByVal var As String)
        Try
            sql = "SELECT concat(`STRT`, `END`) FROM `tblautonumber` WHERE `ID`= '" & id & "'"
            reloadtxt(sql)
            var = dt.Rows(0).Item(0)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Public coldate As New CalendarColumn()
    Public coltime As New CalendarColumnTime()

    Public Function dtgAddRec(ByVal dtg As DataGridView, ByVal sql As String) As Boolean
        Try
            con.Open()
            cmd = New MySqlCommand
            With cmd
                .Connection = con
                .CommandText = sql
            End With
            da = New MySqlDataAdapter
            da.SelectCommand = cmd
            dt = New DataTable
            da.Fill(dt)
  
            With dtg
              
                If .Rows.Count >= 0 Then
                  
                    .DataSource = dt
                    '.EditMode = DataGridViewEditMode.EditOnEnter
                    .RowHeadersVisible = False
                    .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
                    .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
                    Return True
                Else
                    Return False
                End If
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
            da.Dispose()
        End Try
    End Function


    Public Sub showStationList(ByVal frm As frmStation1)
        Try


            With frm
                sql = "SELECT  `MEDECATION` as 'Medication',`TREATMENT` as 'Nursing-Procedure-and-Treatment'  " & _
                " FROM `tblmedication` M, `tbladmitting` A " & _
                " WHERE `ADMITTINGSTATUS`=1 AND M.`ADMITTINGID`=A.`ADMITTINGID` AND A.CASENUM= " & .dtglistPatients.CurrentRow.Cells(0).Value
                RETRIEVEDTG(.dtgListmedication, sql)
                'dtgListmedication.Columns(0).Visible = False
                .dtgListmedication.Columns(1).Width = 100
                .dtgListmedication.SelectionMode = DataGridViewSelectionMode.FullRowSelect
                .dtgListmedication.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
                .dtgListmedication.EditMode = DataGridViewEditMode.EditProgrammatically
                .dtgListmedication.AllowUserToAddRows = False
                .dtgListmedication.SelectionMode = False

                sql = "SELECT `PRN_MED_ID`,PRNDATE as 'Date'  ,PRNTIME as 'Time' ,`DRUGSNAME` AS 'NameofDrugs', `DOSAGE` AS 'Dosage', `ROUTE` AS 'Route', `REMARKS` AS 'Remarks'  " & _
                        " FROM `tblprnmedication` M, `tbladmitting` A " & _
                        " WHERE M.`ADMITTINGID`=A.`ADMITTINGID` AND `ADMITTINGSTATUS`=1 AND A.CASENUM=" & .dtglistPatients.CurrentRow.Cells(0).Value
                result = dtgAddRec(.dtgListPRNRec, sql)


                .dtgListPRNRec.Columns(0).Visible = False

                sql = "SELECT `BPID`, `BPDATE` AS 'DATE',`BPTIME` as 'Time', `BP`, `PR`, `RR` FROM `tblbprecord` b,`tbladmitting` A " & _
                " WHERE b.`ADMITTINGID`=A.`ADMITTINGID` AND `ADMITTINGSTATUS`=1 AND A.`CASENUM`=" & .dtglistPatients.CurrentRow.Cells(0).Value
                result = dtgAddRec(.dtgListBPRec, sql)
                .dtgListPRNRec.SelectionMode = False
                .dtgListBPRec.Columns(0).Visible = False



                sql = "SELECT `IVFID`, `IVFDATE`  as 'Date/hrn', `BOTTLENUM` as 'Bottle #'," & _
                      " `KINDOFSOLUTION` as 'Kind of Solution', `IVFAMOUNT` AS 'Amount'," & _
                      " `MEDICATIONADDERT` as 'Meication/Addert', `IVFRATE` as 'Rate', `STARTEDTIME` as 'Time Started'," & _
                      " `STARTNURSE` as 'Nurse Id'," & _
                      " ( " & _
                    " CASE WHEN ( " & _
                    " `IVFREMARKS` =  '' " & _
                    " ) " & _
                    " THEN  `IVFREMARKS` " & _
                    " ELSE  `ENDEDTIME` " & _
                    " End " & _
                    " ) AS  'End Time', `ENDNURSE` as 'Nurse Id', ( " & _
                    " CASE WHEN ( " & _
                    " `IVFREMARKS` =  '' " & _
                    " ) " & _
                    " THEN  `IVFREMARKS` " & _
                    " ELSE  `ENDDATE` " & _
                    " End " & _
                    " ) AS  'End Date', `IVFREMARKS` as 'Remarks' FROM tblivfrecord I, `tbladmitting` A " & _
                " WHERE I.`ADDMITINGID`=A.`ADMITTINGID` AND `ADMITTINGSTATUS`=1 AND A.CASENUM=" & .dtglistPatients.CurrentRow.Cells(0).Value
                dtgAddRec(.dtgIVFRecord, sql)

                sql = "SELECT  NOTESID, `NOTESDATE` as 'Date', `NOTESTIME` as 'Time', `FOCUS` as 'Focus', `NOTESDATA` as 'Data' , `NOTESACTION` as 'Action', `NOTESRESPONSE` as 'Response' FROM `tblnursenotes` N,  `tbladmitting` A   WHERE N.`ADDMITINGID`=A.`ADMITTINGID` AND `ADMITTINGSTATUS`=1  AND A.CASENUM=" & .dtglistPatients.CurrentRow.Cells(0).Value

                dtgAddRec(.dtgNurseNotes, sql)
                .dtgNurseNotes.Columns(0).Visible = False

                .btnBPrec.Visible = True
                .btnIVFRecord.Visible = True
                .btnPrmNew.Visible = True
                .btnResultRec.Visible = True
                .btnMedicalChecklist.Visible = True
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

#Region "Report"
    Public Sub reports(ByVal sql As String, ByVal rptname As String, ByVal crystalRpt As Object)
        Try
            con.Open()

            Dim reportname As String
            With cmd
                .Connection = con
                .CommandText = sql
            End With
            ds = New DataSet
            da = New MySqlDataAdapter(sql, con)
            da.Fill(ds)
            reportname = rptname
            Dim reportdoc As New CrystalDecisions.CrystalReports.Engine.ReportDocument
            Dim strReportPath As String
            strReportPath = Application.StartupPath & "\report\" & reportname & ".rpt"
            With reportdoc
                .Load(strReportPath)
                .SetDataSource(ds.Tables(0))
            End With
            With crystalRpt
                .ShowRefreshButton = False
                .ShowCloseButton = False
                .ShowGroupTreeButton = False
                .ReportSource = reportdoc
            End With
        Catch ex As Exception
            MsgBox(ex.Message & "No Crystal Reports have been Installed")
        End Try
        con.Close()
        da.Dispose()
    End Sub
#End Region

End Module
