Imports System.Data.SqlClient

Public Class Form1

    Dim con As New SqlConnection("server=.\SQLEXPRESS;database=domatia;Integrated Security=True")
    Dim imaf, iman As Date

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim room As Integer
        Dim timi As Double
        room = ListBox1.SelectedItem
        imaf = MonthCalendar1.SelectionStart.ToString("dd/MM/yyyy")
        iman = MonthCalendar2.SelectionStart.ToString("dd/MM/yyyy")

        If Month(imaf) = Month(iman) Then
            MessageBox.Show(Month(imaf) & " " & Month(iman))
            timi += ((Format(iman, "dd") - Format(imaf, "dd")) * 30)
            MessageBox.Show(timi)
        ElseIf Month(iman) > Month(imaf) Then
            timi += (((31 - Format(imaf, "dd")) + Format(iman, "dd")) * 30) + (Month(iman) - Month(imaf) - 1) * 30
        End If

        If room > 0 Then
            Dim query As String = "INSERT INTO kratisi VALUES (@onoma,@imaf,@iman,@room,@timi,@eponimo,@id)"
            MessageBox.Show(vbEmpty & " " & imaf & " " & iman)

            Using cmd As New SqlCommand()
                With cmd
                    .Connection = con
                    .CommandType = CommandType.Text
                    .CommandText = query
                    .Parameters.AddWithValue("@onoma", TextBox1.Text)
                    .Parameters.AddWithValue("@imaf", imaf)
                    .Parameters.AddWithValue("@iman", iman)
                    .Parameters.AddWithValue("@room", room)
                    .Parameters.AddWithValue("@timi", timi)
                    .Parameters.AddWithValue("@eponimo", TextBox2.Text)
                    .Parameters.AddWithValue("@id", "")
                End With
                MessageBox.Show(cmd.ToString())
                Try
                    con.Open()
                    cmd.ExecuteNonQuery()
                    MessageBox.Show("o πελάτης " & TextBox1.Text & " " & TextBox2.Text & " καταχωρήθηκε στον πίνακα kratisi.")
                Catch
                    MessageBox.Show("Error Message")
                End Try
            End Using

            con.Close()

            query = "update rooms set isfree=@id,imaf=@imaf,iman=@iman  where roomnumber=@room"

            Using cmd As New SqlCommand()
                With cmd
                    .Connection = con
                    .CommandType = CommandType.Text
                    .CommandText = query
                    .Parameters.AddWithValue("@id", 0)
                    .Parameters.AddWithValue("@imaf", imaf)
                    .Parameters.AddWithValue("@iman", iman)
                    .Parameters.AddWithValue("@room", room)
                End With
                MessageBox.Show(cmd.ToString())
                Try
                    con.Open()
                    cmd.ExecuteNonQuery()
                Catch
                    MessageBox.Show("Error Message")
                End Try
            End Using

            con.Close()


        End If



    End Sub
    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim d As Date
        d = Date.Now
        MessageBox.Show(d)
        Dim query As String = "delete from kratisi where iman<=@date"
        con.Open()
        Using comm As New SqlCommand()
            With comm
                .Connection = con
                .CommandType = CommandType.Text
                .CommandText = query
                .Parameters.AddWithValue("@date", d)
            End With
            Try
                comm.ExecuteNonQuery()
                MessageBox.Show("όλες οι παλιές κρατήσεις διαγράφηκσν.πατήστε show available rooms")
            Catch
                MessageBox.Show("Error Message")
            End Try
            con.Close()
        End Using
        query = "update rooms set imaf=@date1,iman=@date2,isfree=@value where iman<=@date"
        con.Open()
        Using comm As New SqlCommand()
            With comm
                .Connection = con
                .CommandType = CommandType.Text
                .CommandText = query
                .Parameters.AddWithValue("@date1", "1000-01-01")
                .Parameters.AddWithValue("@date2", "1000-01-01")
                .Parameters.AddWithValue("@value", 1)
                .Parameters.AddWithValue("@date", d)
            End With
            Try
                comm.ExecuteNonQuery()
                MessageBox.Show("όλες οι τιμές στον πίνακα rooms ενημερώθηκαν!!!")
            Catch
                MessageBox.Show("Error Message")
            End Try
            con.Close()
        End Using
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        'imaf = MonthCalendar1.SelectionStart.ToString("dd/MM/yyyy")
        'iman = MonthCalendar2.SelectionStart.ToString("dd/MM/yyyy")
        Dim d, m As String
        Dim f, l As Date
        'd = imaf.Day.ToString()
        ' m = imaf.ToString("MMMM")
        Dim dd, mm As Date
        Dim start, en As Date
        Dim dt As DateTime
        Dim format As String = "yyyy-MM-dd"
        Dim str1, str2 As String

        start = MonthCalendar1.SelectionStart
        'MessageBox.Show(start)
        dt = Convert.ToDateTime(start)
        'MessageBox.Show(dt)
        str1 = dt.ToString(format)
        f = CDate(str1)
        MessageBox.Show(f & " " & f.Day)
        en = MonthCalendar2.SelectionStart
        'MessageBox.Show(en)
        dt = Convert.ToDateTime(en)
        'MessageBox.Show(dt)
        str2 = dt.ToString(format)
        l = CDate(str2)
        MessageBox.Show(l)
        ListBox1.Items.Clear()

        Dim cmd As New SqlCommand("SELECT * from rooms", con)
        Dim room, free As Integer
        Try
            con.Open()
            Dim reader As SqlDataReader
            reader = cmd.ExecuteReader()

            If (reader.HasRows) Then
                While (reader.Read())
                    room = reader("roomnumber")
                    free = reader("isfree")
                    'If reader("imaf") <> "" And reader("iman") <> "" Then
                    dd = reader("imaf")
                    mm = reader("iman")
                    ' MessageBox.Show(mm.Day)
                    'End If
                    If free = 0 Then
                        'If (f.Day >= mm.Day And f.Month = mm.Month And f.Year >= mm.Year And free = 0) Or (f.Day < mm.Day And f.Month <> mm.Month And f.Year >= mm.Year And free = 0) Or free = 1 Then
                        If f >= mm Then
                            ListBox1.Items.Add(room)
                        ElseIf f < mm Or free = 0 Then
                            MessageBox.Show("room " & room & " is not available")
                        End If
                    Else
                        ListBox1.Items.Add(room)
                    End If
                End While
            End If

            con.Close()

        Catch ex As SqlException
            MessageBox.Show("something went wrong " + ex.ToString)
        End Try



    End Sub
End Class