UTS_Peminjaman

Imports System.Data.SqlClient
Imports System.Math

Public Class Form1
    Sub kosongkan()
        lbl_angsur.Text = ""
        lbl_namanasabah.Text = ""
        lbl_penghasilan.Text = ""
        lbl_status.Text = ""
        lbl_limit.Text = ""
        txt_bunga.Clear()
        txt_jmlpinjam.Clear()
        txt_lamacicil.Clear()
        cb_nasabah.Text = ""
        cb_nasabah.Focus()
    End Sub
    Sub ketemu()
        On Error Resume Next
        cb_nasabah.Text = dr(2)
        txt_bunga.Text = dr(3)
        txt_lamacicil.Text = dr(4)
        txt_jmlpinjam.Text = dr(5)
        lbl_angsur.Text = dr(6)
    End Sub
    Sub tampilgrid()
        Call koneksi()
        da = New SqlDataAdapter("SELECT * FROM tbl_pinjaman", conn)
        ds = New DataSet
        da.Fill(ds)
        dgv.DataSource = ds.Tables(0)
        dgv.ReadOnly = True
    End Sub
    Sub notis()
        '    Call koneksi()
        '    cmd = New SqlCommand("SELECT id_pinjaman FROM tbl_pinjaman ORDER BY id_pinjaman desc", conn)
        '    dr = cmd.ExecuteReader
        '    dr.Read()
        '    If Not dr.HasRows Then
        '        lbl_idpinjam.Text = "00001"
        '    Else
        '        lbl_idpinjam.Text = Format(Microsoft.VisualBasic.Right(dr(0), 5) + 1, "00000")
        '    End If
        Call koneksi()
        cmd = New SqlCommand("select Id_pinjaman from tbl_pinjaman order by Id_pinjaman desc", conn)
        dr = cmd.ExecuteReader
        dr.Read()
        If Not dr.HasRows Then
            lbl_idpinjam.Text = "P" + "0001"
        Else
            lbl_idpinjam.Text = "P" + Format(Microsoft.VisualBasic.Right(dr(0), 4) + 1, "0000")
        End If

    End Sub

    Sub tampilnasabah()
        Call koneksi()
        cmd = New SqlCommand("SELECT * FROM tbl_nasabah", conn)
        dr = cmd.ExecuteReader
        cb_nasabah.Items.Clear()
        Do While dr.Read
            cb_nasabah.Items.Add(dr(0))
        Loop
    End Sub

    Sub mulai()
        Call koneksi()
        Call kosongkan()
        Call notis()
        Call tampilgrid()
        Call tampilnasabah()
    End Sub



    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        txt_tglpinjam.Text = Format(Now, "dd-MMM-yyyy")
        tampilgrid()
        tampilnasabah()
        notis()
    End Sub

    Private Sub cb_nasabah_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cb_nasabah.SelectedIndexChanged
        Call koneksi()
        cmd = New SqlCommand("select * from tbl_nasabah where id_nasabah = '" & cb_nasabah.Text & "'", conn)
        dr = cmd.ExecuteReader
        dr.Read()
        If dr.HasRows Then
            lbl_namanasabah.Text = dr(1)
            lbl_penghasilan.Text = dr(3)
            lbl_status.Text = dr(2)
        Else
            MsgBox("ID NASABAH TIDAK DITEMUKAN")
        End If

        'cari jumlah pinjaman yang bisa dipinjam

        If (lbl_status.Text = "Sudah Nikah" And lbl_penghasilan.Text = True) Then
            MsgBox("anda boleh pinjam")
            lbl_limit.Text = lbl_penghasilan.Text * 0.5
        Else
            MsgBox("Anda tidak bisa pinjam")
            lbl_namanasabah.Text = ""
            lbl_penghasilan.Text = ""
            lbl_status.Text = ""
            lbl_limit.Text = ""
        End If



        'cari nasabah yang belum lunas
        Call koneksi()
        cmd = New SqlCommand("select * from tbl_pinjaman where id_nasabah = '" & cb_nasabah.Text & "' and sisa_pinjaman < 1000000", conn)
        dr = cmd.ExecuteReader
        dr.Read()
        If dr.HasRows Then
            MsgBox("Anda Belum Bisa Pinjam karena BELUM LUNAS")
            Call mulai()
        End If

    End Sub

    Private Sub txt_jmlpinjam_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txt_jmlpinjam.KeyDown
        'fungsi kendali Enter
        If e.KeyCode = Keys.Enter Then
            lbl_angsur.Text = Round(Pmt(txt_bunga.Text / 12 / 100, txt_lamacicil.Text, txt_jmlpinjam.Text), 0) * -1
        End If

    End Sub

    Private Sub btnsimpan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsimpan.Click
        'cek data yang belum di isi
        If cb_nasabah.Text = "" Or txt_bunga.Text = "" Or txt_jmlpinjam.Text = "" Or txt_lamacicil.Text = "" Then
            MsgBox("Data Belum Lengkap")
            Exit Sub
        End If
        Try
            'simpan disini
            Call koneksi()
            Dim simpan As String = "INSERT INTO tbl_pinjaman values('" & lbl_idpinjam.Text & "', '" & txt_tglpinjam.Text & "','" & cb_nasabah.Text & "','" & txt_bunga.Text & "','" & txt_lamacicil.Text & "', '" & txt_jmlpinjam.Text & "', '" & lbl_angsur.Text & "', '" & txt_jmlpinjam.Text & "', 'BELUM LUNAS')"
            cmd = New SqlCommand(simpan, conn)
            cmd.ExecuteNonQuery()
            kosongkan()
            notis()
            tampilgrid()
            tampilnasabah()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

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

    Private Sub btnbatal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnbatal.Click
        kosongkan()
    End Sub

    Private Sub lbl_angsur_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lbl_angsur.Click

    End Sub
End Class