.NET · Coding · Task

Aplikasi ENI TODO terkoneksi dengan MS. Access dan MS. Excel Berbasis ASP.NET

Hi! Balik lagi dengan tema yang kayak kemarin “Coding”. Aku masih betah dengan tema coding, nanti aku balik lagi dengan lanjutan KNOK.

Deskripsi

Ok! Kali ini aku mau share aplikasi TODO berbasis ASP.NET. Walaupun aplikasi todo yang terdengar sangaaat mainstream, aplikasi ENI TODO ini memiliki keunggulan:

  1. Todo dikelompokkan dengan folder-folder.
  2. Todo dikelompokkan berdasarkan prioritas: important, normal, dan easy.
  3. Todo bisa di export ke Excel.
  4. User bisa daftar sendiri dan manage todo secara pribadi!

Berikut salah satu tampilan design di dalam Visual Studio:

p14

Gambar. Tampilan design halaman detail.aspx

Walau di design tersebut kelihatan berantakan, ketika sudah dijalankan di browser tampilannya seperti ini:

p1

Gambar. Tampilan di Browser halaman detail Folder

Selain pakai Visual Studio, untuk membuat designnya aku juga menggunakan html dulu ketika design udah oke, tag demi tag html aku salin ke Visual Studio. Jujur, belum terbiasa design pake GUI hehe

Design & Demo

Berikut screenshot design + demo dari aplikasi ENI TODO:

p2

Gambar. Halaman login

p3

Gambar. Halaman pendaftaran

p4

Gambar. Alert di form pendaftaran jika username sudah ada ketika Submit Form

p5

Gambar. Halaman landing / dashboard

p6

Gambar. Halaman tambah folder baru

p7

Gambar. Result ketika folder berhasil ditambahkan

p8

Gambar. Contoh  halaman detail folder yang baru di buat

p9Gambar. Halaman tambah todo baru dengan keterangan
by pass Folder id & Tipe Todo

p10

Gambar. Contoh todo baru yang terbuat di prioritas important dan ‘folder baru’

p11

Gambar. Tampilan action export todo & filter action

p12

Gambar. Tampilan detail folder dengan macam-macam todo yang complete dan tidak

p13

Gambar. Tampilan Excel file ketika Export ‘folder 1’ dengan filer semua tipe prioritas

 

Source Code

login

Imports System.Data
Imports System.Data.OleDb
Public Class login
    Inherits System.Web.UI.Page

    Dim conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Eni\Documents\tugas_uas\todo.mdb"
    Dim objConn As New OleDb.OleDbConnection(conn)

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub btnLogin_Click(sender As Object, e As EventArgs) Handles btnLogin.Click
        If (txtUname.Text.Equals("") Or txtPass.Text.Equals("")) Then
            Response.Write("alert('Username atau password tidak boleh kosong!')")
        Else
            Dim uname As String = txtUname.Text
            Dim pass As String = txtPass.Text
            Dim hs As System.Security.Cryptography.MD5 = System.Security.Cryptography.MD5.Create
            Dim db As Byte() = hs.ComputeHash(System.Text.Encoding.UTF8.GetBytes(pass))
            Dim passRest As String = Convert.ToBase64String(db)

            objConn.Open()
            Dim q As String = "SELECT * FROM [user] WHERE ([username]='" + uname + "' AND [password]='" + passRest + "')"
            Dim oCmd = New OleDbCommand(q, objConn)
            Dim o = oCmd.ExecuteReader()
            If (o.Read()) Then
                Session("auth") = o("uid").ToString
                Response.Redirect("landing.aspx")
            Else
                Response.Write("alert('user tidak ditemukan!')")
            End If
            objConn.Close()
        End If
    End Sub
End Class

registration

Imports System.Data
Imports System.Data.OleDb
Public Class registration
    Inherits System.Web.UI.Page

    Dim conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Eni\Documents\tugas_uas\todo.mdb"
    Dim objConn As New OleDb.OleDbConnection(conn)

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub btnLogin_Click(sender As Object, e As EventArgs) Handles btnLogin.Click
        If (txtUname.Text.Equals("") Or txtPass.Text.Equals("")) Then
            Response.Write("alert('Username atau password tidak boleh kosong!')")
        Else
            Dim uname As String = txtUname.Text
            Dim pass As String = txtPass.Text
            Dim hs As System.Security.Cryptography.MD5 = System.Security.Cryptography.MD5.Create
            Dim db As Byte() = hs.ComputeHash(System.Text.Encoding.UTF8.GetBytes(pass))
            Dim passRest As String = Convert.ToBase64String(db)

            objConn.Open()
            Dim q As String = "SELECT * FROM [user] WHERE ([username]='" + uname + "')"
            Dim oCmd = New OleDbCommand(q, objConn)
            Dim o = oCmd.ExecuteReader()
            If (o.Read()) Then
                Response.Write("alert('user sudah ada! gunakan username yang lain.')")
                objConn.Close()
            Else
                Dim q2 As String = "INSERT INTO [user] ([username],[password]) values('" + uname + "','" + passRest + "')"
                Dim oCmd2 = New OleDbCommand(q2, objConn)
                oCmd2.ExecuteNonQuery()
                objConn.Close()
                Response.Redirect("login.aspx")
            End If
        End If
    End Sub
End Class

landing

Public Class landing1
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim uid = Session("auth")
If (IsNothing(uid)) Then
Response.Write("alert('login dulu!')")
Response.Redirect("login.aspx")
End If

uid = Session("auth").ToString
SqlDataSource1.SelectCommand = "SELECT * FROM [folder] WHERE ([user_id]=" + uid + ")"
End Sub

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Response.Redirect("add_folder.aspx")
End Sub

Protected Sub BulletedList1_Click(sender As Object, e As BulletedListEventArgs) Handles BulletedList1.Click
Response.Redirect("detail.aspx?id=" + BulletedList1.Items.Item(e.Index).Value)
End Sub
End Class

add todo

Imports System.Data
Imports System.Data.OleDb
Public Class add_todo
Inherits System.Web.UI.Page

Dim conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Eni\Documents\tugas_uas\todo.mdb"
Dim objConn As New OleDb.OleDbConnection(conn)

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim uid = Session("auth")
If (IsNothing(uid)) Then
Response.Write("alert('login dulu!')")
Response.Redirect("login.aspx")
End If

Dim fid As String = Request.QueryString("folder")
Dim type As String = Request.QueryString("type")
DropDownList1.SelectedValue = type
DropDownList2.SelectedValue = fid
End Sub

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
If (TextBox1.Text.Equals("")) Then
Response.Write("alert('Wajib isi nama todo!')")
Else
Dim uid = Session("auth").ToString
objConn.Open()
Dim tambah As String = "INSERT INTO todo (todo_name,todo_status,todo_type,folder_id,user_id) values('" + TextBox1.Text + "', 'd', '" + DropDownList1.SelectedItem.Value + "', '" + DropDownList2.SelectedItem.Value + "','" + uid + "')"
Dim oCmd = New OleDbCommand(tambah, objConn)
oCmd.ExecuteNonQuery()
objConn.Close()
Response.Redirect("detail.aspx?id=" + DropDownList2.SelectedValue)
End If
End Sub
End Class

add folder

Imports System.Data
Imports System.Data.OleDb
Public Class add_folder
Inherits System.Web.UI.Page

Dim conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Eni\Documents\tugas_uas\todo.mdb"
Dim objConn As New OleDb.OleDbConnection(conn)

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim uid = Session("auth")
If (IsNothing(uid)) Then
Response.Write("alert('login dulu!')")
Response.Redirect("login.aspx")
End If
End Sub

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
If (TextBox1.Text.Equals("")) Then
Response.Write("alert('Wajib isi nama folder!')")
Else
Dim uid = Session("auth").ToString
objConn.Open()
Dim tambah As String = "INSERT INTO folder (folder_name,user_id) values('" + TextBox1.Text + "','" + uid + "')"
Dim oCmd = New OleDbCommand(tambah, objConn)
oCmd.ExecuteNonQuery()
objConn.Close()
Response.Redirect("landing.aspx")
End If
End Sub
End Class

detail

Imports System.Data
Imports System.Data.OleDb
Imports Excel = Microsoft.Office.Interop.Excel
Public Class landing
Inherits System.Web.UI.Page

Dim conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Eni\Documents\tugas_uas\todo.mdb"
Dim objConn As New OleDb.OleDbConnection(conn)

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim uid = Session("auth")
If (IsNothing(uid)) Then
Response.Write("alert('login dulu!')")
Response.Redirect("login.aspx")
End If

uid = Session("auth").ToString
Dim qs As String = Request.QueryString("id")

objConn.Open()
Dim q As String = "SELECT * FROM [folder] WHERE ([fid]=" + qs + ")"
Dim oCmd = New OleDbCommand(q, objConn)
Dim o = oCmd.ExecuteReader()
If (o.Read()) Then
Label1.Text = o("folder_name").ToString
Else
Response.Write("alert('tidak ditemukan!')")
End If
objConn.Close()
SqlDataSource1.SelectCommand = "SELECT * FROM [folder] WHERE ([user_id]=" + uid + ")"
Dim impo As String = "SELECT * FROM [todo] WHERE ([todo_type]='important' AND [folder_id]=" + qs + " AND [user_id]=" + uid + ")"
SqlDataSource2.SelectCommand = impo
Dim i As Integer = 0
For Each li As ListItem In CheckBoxList1.Items
Dim val As String = CheckBoxList1.Items(i).Value
objConn.Open()
Dim upd As String = "SELECT * FROM [todo] WHERE ([todo_id]=" + val + " AND [todo_status]='d')"
oCmd = New OleDbCommand(upd, objConn)
o = oCmd.ExecuteReader()
If (o.Read()) Then
CheckBoxList1.Items(i).Selected = False
Else
CheckBoxList1.Items(i).Selected = True
End If
objConn.Close()
Next
Dim nor As String = "SELECT * FROM [todo] WHERE ([todo_type]='normal' AND [folder_id]=" + qs + " AND [user_id]=" + uid + ")"
SqlDataSource3.SelectCommand = nor
i = 0
For Each li As ListItem In CheckBoxList2.Items
Dim val As String = CheckBoxList2.Items(i).Value
objConn.Open()
Dim upd As String = "SELECT * FROM [todo] WHERE ([todo_id]=" + val + " AND [todo_status]='d')"
oCmd = New OleDbCommand(upd, objConn)
o = oCmd.ExecuteReader()
If (o.Read()) Then
CheckBoxList2.Items(i).Selected = False
Else
CheckBoxList2.Items(i).Selected = True
End If
objConn.Close()
Next
Dim eas As String = "SELECT * FROM [todo] WHERE ([todo_type]='easy' AND [folder_id]=" + qs + " AND [user_id]=" + uid + ")"
SqlDataSource4.SelectCommand = eas
i = 0
For Each li As ListItem In CheckBoxList3.Items
Dim val As String = CheckBoxList3.Items(i).Value
objConn.Open()
Dim upd As String = "SELECT * FROM [todo] WHERE ([todo_id]=" + val + " AND [todo_status]='d')"
oCmd = New OleDbCommand(upd, objConn)
o = oCmd.ExecuteReader()
If (o.Read()) Then
CheckBoxList3.Items(i).Selected = False
Else
CheckBoxList3.Items(i).Selected = True
End If
objConn.Close()
Next
End Sub

Protected Sub BulletedList1_Click(sender As Object, e As BulletedListEventArgs) Handles BulletedList1.Click
Response.Redirect("detail.aspx?id=" + BulletedList1.Items.Item(e.Index).Value)
End Sub

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Response.Redirect("add_folder.aspx")
End Sub

Protected Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Dim qs As String = Request.QueryString("id")
Response.Redirect("add_todo.aspx?folder=" + qs + "&type=important")
End Sub

Protected Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
Dim qs As String = Request.QueryString("id")
Response.Redirect("add_todo.aspx?folder=" + qs + "&type=normal")
End Sub

Protected Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
Dim qs As String = Request.QueryString("id")
Response.Redirect("add_todo.aspx?folder=" + qs + "&type=easy")
End Sub

Protected Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
Dim app As New Excel.Application
Dim file As Excel.Workbook

Dim type = DropDownList1.SelectedItem.Value
Dim uid = Session("auth")
Dim fid As String = Request.QueryString("id")
objConn.Open()
Dim q As String = "SELECT * FROM [user] WHERE ([uid]=" + uid + ")"
Dim oCmd = New OleDbCommand(q, objConn)
Dim o = oCmd.ExecuteReader()
Dim uname As String = ""
If (o.Read()) Then
uname = o("username").ToString
Else
Response.Write("alert('usernmae tidak ditemukan!')")
End If
objConn.Close()
uid = uid.ToString
objConn.Open()
q = "SELECT * FROM [folder] WHERE ([fid]=" + fid + ")"
oCmd = New OleDbCommand(q, objConn)
o = oCmd.ExecuteReader()
Dim folder_name As String = ""
If (o.Read()) Then
folder_name = o("folder_name").ToString
Else
Response.Write("alert('folder tidak ditemukan!')")
End If
objConn.Close()

file = app.Workbooks.Open("C:\Users\Eni\Documents\tugas_uas\report.xls")

app.Range("C1").Value = uname
app.Range("C2").Value = folder_name
objConn.Open()
If (type.Equals("all")) Then
q = "SELECT [todo_name],[todo_type],[todo_status] FROM [todo] WHERE ([folder_id]=" + fid + ")"
Else
q = "SELECT [todo_name],[todo_type],[todo_status] FROM [todo] WHERE ([folder_id]=" + fid + " AND [todo_type]='" + type + "')"
End If
oCmd = New OleDbCommand(q, objConn)
o = oCmd.ExecuteReader()
Dim complete As Integer = 0
Dim uncomplete As Integer = 0
Dim i As Integer = 0
Dim col As Integer = 7
While (o.Read())
app.Range("A" + col.ToString).Value = o.GetString(0)
app.Range("B" + col.ToString).Value = o.GetString(1)
If (o.GetString(2).Equals("d")) Then
app.Range("C" + col.ToString).Value = "un-complete"
uncomplete = uncomplete + 1
Else
app.Range("C" + col.ToString).Value = "complete"
complete = complete + 1
End If
i = i + 1
col = col + 1
End While
app.Range("C3").Value = complete
app.Range("C4").Value = uncomplete
objConn.Close()

Dim fileNew As String = "C:\Users\Eni\Documents\tugas_uas\report\report_" + uname + "_" + folder_name + ".xlsx"

If System.IO.File.Exists(fileNew) Then
Dim rnd As New Random()
Dim randomNumber As String = rnd.Next(10, 100)
fileNew = "C:\Users\Eni\Documents\tugas_uas\report\report_" + uname + "_" + folder_name + "" + randomNumber + ".xlsx"
End If

file.SaveAs(fileNew)
app.Visible = True
End Sub

Protected Sub CheckBoxList2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles CheckBoxList2.SelectedIndexChanged
Dim fid As String = Request.QueryString("id")
Dim i As Integer = 0
For Each li As ListItem In CheckBoxList2.Items
Dim val As String = CheckBoxList2.Items(i).Value
If (CheckBoxList2.Items(i).Selected) Then
Dim ck As String = "y"
objConn.Open()
Dim upd As String = "UPDATE [todo] Set todo_status='" + ck + "' WHERE ([todo_id]=" + val + ") "
Dim oCmd = New OleDbCommand(upd, objConn)
oCmd.ExecuteNonQuery()
objConn.Close()
Else
Dim ck As String = "d"
objConn.Open()
Dim upd As String = "UPDATE [todo] Set todo_status='" + ck + "' WHERE ([todo_id]=" + val + ") "
Dim oCmd = New OleDbCommand(upd, objConn)
oCmd.ExecuteNonQuery()
objConn.Close()
End If
i = i + 1
Next


End Sub

Protected Sub CheckBoxList3_SelectedIndexChanged(sender As Object, e As EventArgs) Handles CheckBoxList3.SelectedIndexChanged
Dim fid As String = Request.QueryString("id")
Dim i As Integer = 0
For Each li As ListItem In CheckBoxList3.Items
Dim val As String = CheckBoxList3.Items(i).Value
If (CheckBoxList3.Items(i).Selected) Then
Dim ck As String = "y"
objConn.Open()
Dim upd As String = "UPDATE [todo] Set todo_status='" + ck + "' WHERE ([todo_id]=" + val + ") "
Dim oCmd = New OleDbCommand(upd, objConn)
oCmd.ExecuteNonQuery()
objConn.Close()
Else
Dim ck As String = "d"
objConn.Open()
Dim upd As String = "UPDATE [todo] Set todo_status='" + ck + "' WHERE ([todo_id]=" + val + ") "
Dim oCmd = New OleDbCommand(upd, objConn)
oCmd.ExecuteNonQuery()
objConn.Close()
End If
i = i + 1
Next


End Sub

Protected Sub CheckBoxList1_SelectedIndexChanged1(sender As Object, e As EventArgs) Handles CheckBoxList1.SelectedIndexChanged
Dim fid As String = Request.QueryString("id")
Dim i As Integer = 0
For Each li As ListItem In CheckBoxList1.Items
Dim val As String = CheckBoxList1.Items(i).Value
If (CheckBoxList1.Items(i).Selected) Then
Dim ck As String = "y"
objConn.Open()
Dim upd As String = "UPDATE [todo] Set todo_status='" + ck + "' WHERE ([todo_id]=" + val + ") "
Dim oCmd = New OleDbCommand(upd, objConn)
oCmd.ExecuteNonQuery()
objConn.Close()
Else
Dim ck As String = "d"
objConn.Open()
Dim upd As String = "UPDATE [todo] Set todo_status='" + ck + "' WHERE ([todo_id]=" + val + ") "
Dim oCmd = New OleDbCommand(upd, objConn)
oCmd.ExecuteNonQuery()
objConn.Close()
End If
i = i + 1
Next


End Sub
End Class

 

Good Luck!

Happy Coding Guys! 🙂

Iklan

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout /  Ubah )

Foto Google

You are commenting using your Google account. Logout /  Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout /  Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout /  Ubah )

Connecting to %s