Imports System.Data.SqlClient
Public Class frmremedy_Medi
Dim dtRemedy As DataTable = New DataTable("Dt_Order")
Dim flagClear As Integer = 0
Private Sub frmremedy_Medi_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
LoadData()
End Sub
Sub LoadData()
Dim connect As SqlConnection = New SqlConnection("Data Source=(local)\SQLEXPRESS;Initial Catalog=Clinic;Integrated Security=True")
Dim sql As String = "SELECT max(Reme_Number) as MaxReme from Remedies"
Dim sqlCmd As SqlCommand = New SqlCommand(sql, connect)
connect.Open()
Dim reader As SqlDataReader = sqlCmd.ExecuteReader()
reader.Read()
Dim RemNum As String
RemNum = reader("MaxReme").ToString()
reader.Close()
Dim RemeMax As Integer
If RemNum = "" Then
RemeMax = 1
Else
RemeMax = CInt(RemNum) + 1
End If
If RemeMax < 10 Then
txtRemSearch.Text = "0000" + RemeMax.ToString()
ElseIf RemeMax >= 10 And RemeMax <= 99 Then
txtRemSearch.Text = "000" + RemeMax.ToString()
ElseIf RemeMax = 100 And RemeMax <= 999 Then
txtRemSearch.Text = "00" + RemeMax.ToString()
ElseIf RemeMax >= 1000 And RemeMax <= 9999 Then
txtRemSearch.Text = "o" + RemeMax.ToString()
Else
txtRemSearch.Text = RemeMax.ToString()
End If
txtRemeDate.Text = Date.Now().Date
'ดึงข้อมูลจากตารางยามาแสดง()
Dim Sql2 As String = "SELECT Medi_ID,Medi_Name from Medicines"
Dim Sqlcmd2 As SqlCommand = New SqlCommand(Sql2, connect)
Dim Sqladp As SqlDataAdapter = New SqlDataAdapter(Sqlcmd2)
Dim ds As DataSet = New DataSet()
Sqladp.Fill(ds, "medi")
cboMedi.DataSource = ds.Tables("medi")
cboMedi.ValueMember = "Medi_ID"
cboMedi.DisplayMember = "Medi_Name"
'ดึงข้อมูลจากตารางโรคมาแสดง
Dim Sql3 As String = "SELECT Dise_ID,Dise_Name from Diseases"
Dim Sqlcmd3 As SqlCommand = New SqlCommand(Sql3, connect)
Dim Sqladp2 As SqlDataAdapter = New SqlDataAdapter(Sqlcmd3)
Dim ds2 As DataSet = New DataSet()
Sqladp2.Fill(ds2, "Dise")
cboDise.DataSource = ds2.Tables("Dise")
cboDise.ValueMember = "Dise_ID"
cboDise.DisplayMember = "Dise_Name"
connect.Close()
If flagClear = 0 Then
dtRemedy.Columns.Add("Medi_ID")'บรรทัดนี้เเลย
dtRemedy.Columns.Add("Medi_Name")
dtRemedy.Columns.Add("qty")
dtRemedy.Columns.Add("Total")
End If
' Dim DiseTotal As Double = CDbl(txtDisePrice.Text)
' txtTotal.Text = DiseTotal.ToString()
End Sub
'ต้องการให้กดค้นหาแล้วขึ้นdialog
Private Sub btnPat_Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPat_Search.Click
PatIdSearch = txtPat_IDSearch.Text
lovPat.ShowDialog()
Dim connect As SqlConnection = New SqlConnection("Data Source=(local)\SQLEXPRESS;Initial Catalog=Clinic;Integrated Security=True")
Dim sql As String = "select* from Patients where Pat_ID = '" + PatIdSelect + "'"
Dim sqlCmd As SqlCommand = New SqlCommand(sql, connect)
connect.Open()
Dim reader As SqlDataReader = sqlCmd.ExecuteReader()
reader.Read()
If (reader.HasRows) Then
txtPat_IDSearch.Text = PatIdSelect
txtFName.Text = reader("Pat_FName").ToString()
txtLName.Text = reader("Pat_LName").ToString()
maskTel.Text = reader("Pat_Phone").ToString()
txtPat_Address.Text = reader("Pat_Address").ToString()
End If
reader.Close()
connect.Close()
End Sub
'แสดงราคาต่อหน่วยและยาในคลังอัตโนมัติ ที่เลือกจากcombobox
Private Sub cboMedi_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboMedi.SelectedIndexChanged
Dim Medi As String = cboMedi.SelectedValue.ToString()
Dim connect As SqlConnection = New SqlConnection("Data Source=(local)\SQLEXPRESS;Initial Catalog=Clinic;Integrated Security=True")
Dim SqlString As String = "Select Medi_QtyOnHand,Medi_SalePrice from Medicines where Medi_ID = '" + Medi + "'"
Dim sqlCmd As SqlCommand = New SqlCommand(SqlString, connect)
connect.Open()
Dim reader As SqlDataReader = sqlCmd.ExecuteReader()
reader.Read()
If (reader.HasRows) Then
txtOnHand.Text = reader("Medi_QtyOnHand").ToString
txtunitPrice.Text = reader("Medi_SalePrice").ToString
End If
reader.Close()
connect.Close()
End Sub
' 'ค่ารักษาโรค
Private Sub cboDise_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboDise.SelectedIndexChanged
Dim Dise As String = cboDise.SelectedValue.ToString()
Dim connect As SqlConnection = New SqlConnection("Data Source=(local)\SQLEXPRESS;Initial Catalog=Clinic;Integrated Security=True")
Dim SqlString As String = "Select Dise_Price from Diseases where Dise_ID = '" + Dise + "'"
Dim sqlCmd As SqlCommand = New SqlCommand(SqlString, connect)
connect.Open()
Dim reader As SqlDataReader = sqlCmd.ExecuteReader()
reader.Read()
If (reader.HasRows) Then
txtDisePrice.Text = reader("Dise_Price").ToString
End If
reader.Close()
connect.Close()
End Sub
'ปุ่มสั่งซื้อ
Private Sub btnOrder_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOrder.Click
Dim ID As String = cboMedi.SelectedValue.ToString()
Dim name As String = cboMedi.Text.ToString()
Dim qty As Integer = CInt(txtUnit.Text)
Dim Price As Double = CDbl(txtunitPrice.Text)
Dim PriceItem As Double = qty * Price
Dim drNew As DataRow
drNew = dtRemedy.NewRow()
drNew("Medi_ID") = ID
drNew("Medi_Name") = name
drNew("qty") = qty
drNew("Total") = PriceItem
dtRemedy.Rows.Add(drNew)
dgvRemedy.DataSource = dtRemedy
ShowTotalAmount()
End Sub
'คำนวนราคาสินค้ารวมโดยใช้ForLoop
Sub ShowToTalAmount()
Dim sumTotal As Double
Dim dr As DataRow
For Each dr In dtRemedy.Rows
sumTotal = sumTotal + CDbl(dr("Total"))
Next
Dim DiseTotal As Double = CDbl(txtDisePrice.Text)
txtTotal.Text = (DiseTotal + sumTotal).ToString()
End Sub
'ปุ๋มบันทึกข้อมูล
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim RemeDy_Num As String = txtRemSearch.Text
Dim Pat_ID As String = txtPat_IDSearch.Text
Dim Dise_ID As String = cboDise.SelectedValue
Dim day As String = Date.Now().Date.Day
Dim month As String = Date.Now.Date.Month
Dim Year As String = Date.Now.Date.Year
Dim RemeDate As String = month + "/" + day + "/" + Year
Dim connect As SqlConnection = New SqlConnection("Data Source=(local)\SQLEXPRESS;Initial Catalog=Clinic;Integrated Security=True")
Dim SqlIN As String = "InSert InTo Remedies(Reme_Number,Pat_ID,Remedy_Date) Values ('" + RemeDy_Num + "','" + Pat_ID + "','" + RemeDate + "')"
Dim sqlCmd As SqlCommand = New SqlCommand(SqlIN, connect)
connect.Open()
Dim successInSert As Integer = sqlCmd.ExecuteNonQuery()
Dim successInSert2 As Integer
Dim sqlCmd2 As SqlCommand
For i = 0 To dtRemedy.Rows.Count - 1
sqlCmd2 = New SqlCommand("InSert InTo RemedyDetails(Dise_ID,Reme_Number,Medi_ID,OrderMediQty)Values('" + Dise_ID + "','" + RemeDy_Num + "','" + dtRemedy.Rows(i)("Medi_ID") + "','" + dtRemedy.Rows(i)("Qty") + "')", connect)
successInSert2 = sqlCmd2.ExecuteNonQuery()
Next
If successInSert > 0 And successInSert2 > 0 Then
MsgBox("บันทึกข้อมูลเรียบร้อย")
'clearForm()
LoadData()
Else
MsgBox("การบันทึกข้อมูลไม่สำเร็จ")
End If
connect.Close()
End Sub
'ลบค่าที่เลือกในดาต้าGrid
Private Sub btndelet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelet.Click
Dim row As DataGridViewRow
Dim NumRow As Integer = dgvRemedy.Rows.Count
For A As Integer = 0 To NumRow
For Each row In dgvRemedy.Rows
If row.Selected Then
Dim rIndex As Integer = CInt(row.Index)
dgvRemedy.Rows.RemoveAt(rIndex)
ShowToTalAmount()
End If
Next
Next
End Sub
Sub ClearForm()
txtFName.Text = ""
txtLName.Text = ""
txtPat_Address.Text = ""
maskTel.Text = ""
txtPat_IDSearch.Text = ""
txtUnit.Text = ""
txtDisePrice.Text = ""
txtOnHand.Text = ""
txtTotal.Text = ""
txtunitPrice.Text = ""
End Sub
End Class