เขียนโปรแกรมยืมอุปกรณ์ แต่วันที่ยืมคืนเวลารันไม่ขึ้นให้เลือก

Option Explicit On
Option Strict On
Imports System.Data
Imports System.Data.SqlClient
'Imports System.Drawing.Printing
Imports System.Drawing.Printing
Public Class frmBorrow
    Inherits System.Windows.Forms.Form

    Dim strConnection As String = "Data Source=BLOG-8R3Q8PQKGG\SQLEXPRESS;Initial Catalog=add;Integrated Security=True"
    Dim Conn As New SqlConnection(strConnection)

    Dim da As SqlDataAdapter
    Dim ds As DataSet = New DataSet()

    Dim UseFont As New Font("MS Sans Serif", 10)
    'Dim prDoc As PrintDocument = New PrintDocument
    'Dim printDoc As 
    Dim prDoc As New PrintDocument()

    Private prDlg As PrintDialog = New PrintDialog

    Dim tmpFileName As String = ""
    Dim IsFindEmployee As Boolean = False

    Dim IsFindProducts As Boolean = False
    Dim LastID_W As String = ""
    Private Sub frmBorrow_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized

        Cursor.Current = Cursors.WaitCursor
        dtpDateCheck_P.Enabled = False
        dtpDateCheck_R.Enabled = False

        With Conn
            If .State = ConnectionState.Open Then .Close()
            .ConnectionString = strConnection
            .Open()
        End With

        'ประกาศค่าในดาต้ากิจ ว่าต้องการอะไรบ้าง
        Dim sqlSectionType As String = ""
        sqlSectionType = "SELECT ID_S,Name_S FROM I_Section"
        sqlSectionType &= " ORDER BY ID_S"

        da = New SqlDataAdapter(sqlSectionType, Conn)
        da.Fill(ds, "I_Section")

        lsvProductsList.Columns.Add("รหัสอุปกรณ์", 100, HorizontalAlignment.Left)
        lsvProductsList.Columns.Add("ชื่ออุปกรณ์", 300, HorizontalAlignment.Left)
        lsvProductsList.Columns.Add("จำนวนที่ยืม", 100, HorizontalAlignment.Right)
        lsvProductsList.Columns.Add("จำนวนที่อนุมัติ", 100, HorizontalAlignment.Right)

        lsvProductsList.View = View.Details
        lsvProductsList.GridLines = True

        txtNumber_W.Text = "0"
        txtNumber_approval.Text = "0"
        txtRemain_P.Text = "0"


        AddHandler prDoc.PrintPage, New PrintPageEventHandler(AddressOf Me.StringToPrint_Print)

        prDoc.DocumentName = "ยืมอุปกรณ์"
        prDlg.Document = prDoc

        txtID_P.Focus()
        Cursor.Current = Cursors.Default
    End Sub

    Private Sub txtID_E_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtID_P.KeyDown
        If txtID_P.Text <> "" Then
            If (e.KeyCode = Keys.Enter) Then

                If txtID_P.Text = "" Then
                    MessageBox.Show("กรุณาป้อนข้อมูลอุปกรณ์ก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    txtID_P.Focus()
                    Exit Sub
                End If

                Dim sqlProducts As String = ""


                sqlProducts = "SELECT I_Equipment.ID_P,I_Equipment.Name_P,I_Equipment.Remain_P,I_Equipment.Num_Check,I_Equipment.Address_P,I_Equipment.DataCheck_P,I_Equipment.DataCheck_R,I_Equipment.Number_P,"
                sqlProducts &= " I_Equipment.ID_P,I_Section.Name_E"
                sqlProducts &= " FROM I_Equipment, I_Section"
                sqlProducts &= " WHERE(I_Equipment.ID_P = I_Section.Name_E)"
                sqlProducts &= " AND (I_Equipment.ID_P='" & txtID_P.Text & "')"


                If IsFindProducts = True Then
                    ds.Tables("I_Equipment").Clear()
                End If

                da.SelectCommand.CommandText = sqlProducts
                da.Fill(ds, "I_Equipment")

                Dim AllDateRent As Integer = 0
                If ds.Tables("I_Equipment").Rows.Count <> 0 Then
                    IsFindProducts = True

                    txtID_P.Text = CStr(ds.Tables("I_Equipment").Rows(0).Item("ID_P"))
                    txtName_P.Text = CStr(ds.Tables("I_Equipment").Rows(0).Item("Name_P"))
                    txtRemain_P.Text = CStr(ds.Tables("I_Equipment").Rows(0).Item("Remain_P"))

                    txtNumber_W.Focus()
                    txtNumber_W.SelectAll()
                Else
                    IsFindProducts = False

                    MessageBox.Show("ไม่พบรหัสอุปกรณ์ตามที่คุณระบุ  !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    ClearAllProductsData()
                    txtID_P.Focus()
                    txtID_P.SelectAll()
                End If
            End If
        End If
    End Sub
    Private Sub ClearAllProductsData()

        txtID_P.Text = ""
        txtName_P.Text = ""
        txtNumber_W.Text = "0"
        txtNumber_approval.Text = "0"
        txtRemain_P.Text = ""
        dtpDateCheck_P.Text = ""
        dtpDateCheck_R.Text = ""


    End Sub
    Private Sub btnShowListProducts_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShowListProducts.Click
        'If txtID_P.Text = "" Then
        'MessageBox.Show("กรุณาป้อนข้อมูลผู้ยืมก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
        'txtID_P.Focus()
        ' Exit Sub
        ' End If

        'ดึงฟอร์ม AllEquipment มาร่วมในการทำงาน
        Dim fAllPro As New AllEquipmentt
        fAllPro.ShowDialog(Me)

        txtID_P.Text = rID_P
        txtName_P.Text = rName_P
        txtNumber_W.Text = rNumber_W.ToString()
        txtNumber_approval.Text = rNumber_approval.ToString()
        txtRemain_P.Text = rRemain_P.ToString()
        'dtpDateCheck_P.Text = rdtpDateCheck_P.ToString()
        'dtpDateCheck_R.Text = rdtpDateCheck_R.ToString()


        rID_P = ""
        rName_P = ""
        rNumber_W = 0
        rNumber_approval = 0
        rRemain_P = 0
        rdtpDateCheck_P = 0
        rdtpDateCheck_R = 0

        txtNumber_W.Focus()
        txtNumber_W.SelectAll()
    End Sub

    Private Sub ClearAllData()
        txtID_P.Text = ""
        txtName_P.Text = ""
        txtNumber_W.Text = "1"
        txtNumber_approval.Text = "0"
        txtRemain_P.Text = ""
        'dtpDateCheck_P.Text = "0"
        'dtpDateCheck_R.Text = "0"

    End Sub

    Private Sub btnAddList_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddList.Click
        If (txtID_P.Text = "") Or (txtName_P.Text = "") Or (txtNumber_W.Text = "") Or (txtNumber_approval.Text = "") Or (txtRemain_P.Text = "") Then
            txtID_P.Focus()
            Exit Sub
        End If


        If (CInt(txtNumber_W.Text) = 0) Or (CInt(txtNumber_approval.Text) = 0) Then
            txtNumber_W.Focus()
            Exit Sub
        End If

        If CInt(txtNumber_W.Text) > CInt(txtNumber_approval.Text) Then


            MessageBox.Show("จำนวนที่ยืมมากกว่าจำนวนอนุมัติ ระบบจะปรับตามจำนวนอนุมัติ  คุณจะได้รับสินค้าตามจำนวนอนุมัติ", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
            txtNumber_W.Text = txtNumber_approval.Text
            txtNumber_W.Focus()
            txtNumber_W.SelectAll()
            Exit Sub
        End If

        If CInt(txtNumber_W.Text) < CInt(txtNumber_approval.Text) Then
            MessageBox.Show("จำนวนที่ยืมน้อยกว่าจำนวนอนุมัติ !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
            txtNumber_W.Focus()
            txtNumber_W.SelectAll()
            Exit Sub
        End If

        If CInt(txtNumber_W.Text) > CInt(txtRemain_P.Text) Then
            MessageBox.Show("อุปกรณ์ที่มีในสต๊อก มีไม่เพียงพอที่จะให้ท่านยืมได้ !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
            txtNumber_W.Focus()
            txtNumber_W.SelectAll()
            Exit Sub
        End If

        Dim i As Integer = 0
        Dim tmpID As String = ""
        For i = 0 To lsvProductsList.Items.Count - 1
            tmpID = lsvProductsList.Items(i).SubItems(0).Text
            If txtID_P.Text = tmpID Then
                MessageBox.Show("คุณเลือกรหัสอุปกรณ์ซ้ำกัน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Information)

                txtID_P.Text = ""
                txtName_P.Text = ""
                txtNumber_W.Text = "0"
                txtNumber_approval.Text = "0"
                txtRemain_P.Text = ""
                'dtpDateCheck_P.Text = ""
                'dtpDateCheck_R.Text = ""
                txtID_P.Focus()
                txtID_P.SelectAll()
                Exit Sub
            End If
        Next

        Dim sqlCheckProducts As String = ""
        sqlCheckProducts = "SELECT I_Withdraw.ID_E, I_WithdrawDetails.ID_P"
        sqlCheckProducts &= " FROM I_Withdraw, I_WithdrawDetails"
        sqlCheckProducts &= " WHERE (I_Withdraw.ID_W = I_WithdrawDetails.ID_W)"
        sqlCheckProducts &= " AND (I_WithdrawDetails.ID_P='" & txtID_P.Text & "')"
        sqlCheckProducts &= " AND (I_Withdraw.ID_E='" & txtID_E.Text & "')"


        da.SelectCommand.CommandText = sqlCheckProducts
        da.Fill(ds, "CheckEquipment")

        Dim WithdrawCheck As Integer = 0
        Dim lvi As ListViewItem
        WithdrawCheck = CInt(txtNumber_W.Text) * CInt(txtNumber_approval.Text) '* CInt(lblBookCost.Text)

        Dim anyData() As String
        anyData = New String() { _
            txtID_P.Text, _
            txtName_P.Text, _
            txtNumber_W.Text, _
            txtNumber_approval.Text, _
            txtRemain_P.Text, _
            WithdrawCheck.ToString("0") _
        }

        lvi = New ListViewItem(anyData)
        lsvProductsList.Items.Add(lvi)

        CalculateBring()
        ClearAllProductsData()

        txtID_P.Focus()
    End Sub
    Private Sub lsvProductsList_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lsvProductsList.SelectedIndexChanged

        Dim i As Integer = 0
        For i = 0 To lsvProductsList.SelectedItems.Count - 1
            Dim lvi As ListViewItem
            lvi = lsvProductsList.SelectedItems(i)
            lsvProductsList.Items.Remove(lvi)
        Next

        CalculateBring()
    End Sub

    Private Sub CalculateBring()
        Dim i As Integer = 0
        Dim tmpNumber_W As Integer = 0
        Dim tmpNumber_approval As Integer = 0

        'คำนวณจำนวนที่ยืมและจำนวนที่อนุมัติ
        For i = 0 To lsvProductsList.Items.Count - 1
            tmpNumber_W = CInt(lsvProductsList.Items(i).SubItems(2).Text)
            tmpNumber_approval = CInt(lsvProductsList.Items(i).SubItems(3).Text)

        Next
    End Sub

    Private Sub btnDeleteList_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteList.Click
        lsvProductsList.Items.Clear()
        ClearAllProductsData()
        txtID_P.Focus()
    End Sub

    Private Sub btnSaveList_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveList.Click
        If txtID_E.Text = "" Then
            MessageBox.Show("กรุณาระบุข้อมูลผู้ยืมก่อนก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
            txtID_P.Focus()
            Exit Sub
        End If

        If lsvProductsList.Items.Count = 0 Then
            MessageBox.Show("กรุณาป้อนรายการยืมอุปกรณ์ !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
            txtID_E.Focus()
            Exit Sub
        End If

        Dim sqlBring As String = ""
        Dim comBring As SqlCommand = New SqlCommand

        With Conn
            If .State = ConnectionState.Open Then .Close()
            .ConnectionString = strConnection
            .Open()
        End With

        Try
            If MessageBox.Show("คุณต้องการบันทึกรายการการยืมอุปกรณ์ ใช่หรือไม่?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes Then

                AutoGenerateID_W()

                sqlBring = "INSERT INTO I_Withdraw (ID_W,Date_W,ID_E) "
                sqlBring &= " VALUES('" & LastID_W & "',"
                sqlBring &= "'" & dtpDateWithdraw.Value & "',"
                sqlBring &= "'" & txtID_P.Text & "')"


                With comBring
                    .CommandType = CommandType.Text
                    .CommandText = sqlBring
                    .Connection = Conn
                    .ExecuteNonQuery()
                End With

                Dim i As Integer = 0
                Dim tmpID_P As String = ""

                Dim sqlProducts As String = ""
                Dim tmpRemain_P As Integer = 0
                Dim tmpNumber_W As Integer = 0
                Dim tmpNumber_approval As Integer = 0
                Dim tmpDateWithdraw As Date
                tmpDateWithdraw = Date.Now

                For i = 0 To lsvProductsList.Items.Count - 1
                    sqlBring = "INSERT INTO I_WithdrawDetails(ID_W,ID_P,Number_W,"
                    sqlBring &= " Number_approval)"
                    sqlBring &= " VALUES('" & LastID_W & "',"

                    tmpID_P = lsvProductsList.Items(i).SubItems(0).Text
                    tmpNumber_W = CInt(lsvProductsList.Items(i).SubItems(2).Text)
                    tmpNumber_approval = CInt(lsvProductsList.Items(i).SubItems(3).Text)

                    sqlBring &= "'" & tmpID_P & "',"
                    sqlBring &= tmpNumber_approval & ","
                    sqlBring &= tmpNumber_W & ")"

                    With comBring
                        .CommandText = sqlBring
                        .ExecuteNonQuery()
                    End With

                    sqlProducts = "SELECT ID_P,Remain_P FROM I_Equipment"
                    sqlProducts &= " WHERE (ID_P='" & tmpID_P & "')"

                    da.SelectCommand.CommandText = sqlProducts
                    da.Fill(ds, "I_Equipment")

                    tmpRemain_P = CInt(ds.Tables("I_Equipment").Rows(0).Item("Remain_P"))

                    ds.Tables("I_Equipment").Clear()

                    sqlBring = "UPDATE I_Equipment"
                    sqlBring &= " SET Remain_P =" & tmpRemain_P - tmpNumber_W
                    sqlBring &= " WHERE (ID_P='" & tmpID_P & "')"

                    With comBring
                        .CommandText = sqlBring
                        .ExecuteNonQuery()
                    End With

                Next

                'frmMain.tslStatus.Text = "กำลังบันทึกข้อมูล..."
                MessageBox.Show("บันทึกรายการการยืมอุปกรณ์เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
                'frmMain.tslStatus.Text = ""

                If IsInstallPrinter() = True Then
                    If prDlg.ShowDialog = DialogResult.OK Then
                        prDoc.Print()
                    End If
                Else
                    MessageBox.Show("กรุณาติดตั้งเครื่อง Printer ก่อนสั่งพิมพ์เอกสาร", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    'MessageBox.Show("คุณไม่ติดตั้ง Printer แล้วจะพิมพ์เอกสารได้ไงเล่า  555", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
                End If

                ClearAllEmployeesData()
                ClearAllProductsData()
                lsvProductsList.Items.Clear()

                txtID_P.Enabled = True
                txtID_P.Focus()
            End If
        Catch ErrProcess As Exception
            MessageBox.Show("ไม่สามารถบันทึกรายการการยืมได้ เนื่องจาก " & ErrProcess.Message, "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            Exit Sub
        End Try
    End Sub
    Private Sub ClearAllEmployeesData()

        txtID_E.Text = ""
        txtName_P.Text = ""
        txtAddress_E.Text = ""
        txtTel_E.Text = ""

    End Sub

    Private Function IsInstallPrinter() As Boolean
        IsInstallPrinter = False
        If prDoc.PrinterSettings.PrinterName = "<no default printer>" Then
            IsInstallPrinter = False
        Else
            IsInstallPrinter = True
        End If
    End Function
    Private Sub StringToPrint_Print(ByVal sender As Object, ByVal e As PrintPageEventArgs)
        AnyString(e.Graphics, "ใบยืมอุปกรณ์", 350, 140)
        AnyString(e.Graphics, "การจัดการศาสนสมบัติของวัด", 317, 160)

        AnyString(e.Graphics, "เลขที่ใบยืม     :  " & LastID_W, 550, 200)
        AnyString(e.Graphics, "วัน/เวลาที่ยืม  :  " & dtpDateWithdraw.Value, 550, 220)


        'AnyString(e.Graphics, "____________________________________________________________________________________________________________________", 10, 240)


        AnyString(e.Graphics, "รหัสผู้ยืม   :  " & txtID_P.Text, 80, 280)
        AnyString(e.Graphics, "ชื่อ - สกุล   :  " & txtName_P.Text, 400, 280)
        AnyString(e.Graphics, "ทีอยู่   :  " & txtAddress_E.Text, 450, 300)
        AnyString(e.Graphics, "เบอร์ติดต่อ   :  " & txtID_P.Text, 80, 280)
       
        AnyString(e.Graphics, "____________________________________________________________________________________________________________________", 10, 325)
        AnyString(e.Graphics, "รหัสอุปกรณ์", 100, 350)
        AnyString(e.Graphics, "รายการ", 300, 350)
        AnyString(e.Graphics, "จำนวนยืม", 530, 350)
        AnyString(e.Graphics, "จำนวนอนุมัติ", 620, 350)
        AnyString(e.Graphics, "____________________________________________________________________________________________________________________", 10, 360)
        AnyString(e.Graphics, "____________________________________________________________________________________________________________________", 10, 750)
        Dim i As Integer = 0
        Dim CurrentYPosition As Integer = 390
        Dim strColumn1 As String = ""
        Dim strColumn2 As String = ""
        Dim strColumn3 As Integer = 0
        Dim strColumn4 As Integer = 0


        For i = 0 To lsvProductsList.Items.Count - 1
            strColumn1 = lsvProductsList.Items(i).SubItems(0).Text
            strColumn2 = lsvProductsList.Items(i).SubItems(1).Text
            strColumn3 = CInt(lsvProductsList.Items(i).SubItems(2).Text)
            strColumn4 = CInt(lsvProductsList.Items(i).SubItems(3).Text)

            AnyString(e.Graphics, strColumn1, 100, CurrentYPosition)
            AnyString(e.Graphics, strColumn2, 200, CurrentYPosition)
            AnyString(e.Graphics, strColumn3.ToString(), 550, CurrentYPosition)
            AnyString(e.Graphics, strColumn4.ToString(), 650, CurrentYPosition)


            CurrentYPosition = CurrentYPosition + 20

        Next

    End Sub
    Private Sub AnyString(ByVal g As Graphics, ByVal printString As String, ByVal xPos As Integer, ByVal yPos As Integer)
        Dim anyPoint As New PointF(xPos, yPos)
        g.DrawString(printString, UseFont, Brushes.Black, anyPoint)
    End Sub
    Private Sub AutoGenerateID_W()
        Dim sqlTmp As String = ""
        Dim comTmp As SqlCommand = New SqlCommand
        Dim drTmp As SqlDataReader
        Dim tmpID_W As Integer = 0

        sqlTmp = "SELECT TOP 1 ID_W FROM I_Withdraw ORDER BY ID_W DESC"

        With Conn
            If .State = ConnectionState.Open Then .Close()
            .ConnectionString = strConnection
            .Open()
        End With

        Try
            With comTmp
                .CommandType = CommandType.Text
                .CommandText = sqlTmp
                .Connection = Conn
                drTmp = .ExecuteReader()

                drTmp.Read()

                tmpID_W = CInt(CStr((drTmp.Item("ID_W"))))
                tmpID_W = tmpID_W + 1
                LastID_W = tmpID_W.ToString("0000000")
            End With
        Catch
            LastID_W = "0000001"
        End Try
        drTmp.Close()
    End Sub
    Private Sub txtNumber_W_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtNumber_W.KeyPress
        If e.KeyChar < "0" Or e.KeyChar > "9" Then
            e.Handled = True
        End If
    End Sub
    Private Sub txtNumber_approval_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtNumber_approval.KeyPress
        If e.KeyChar < "0" Or e.KeyChar > "9" Then
            e.Handled = True
        End If
    End Sub

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