Imports System
Imports System.Data
Imports System.Data.SqlClient
Partial Public Class ClosedDates
    Inherits System.Web.UI.Page
    Dim Conn As SqlConnection = New SqlConnection
    Dim dayno As Integer
    Dim StartDate As Date
    Dim EndDate As Date
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Session("user_id") = "" Then
            Response.Redirect("Login.aspx")
            Exit Sub
        End If
        Conn.ConnectionString = ConfigurationManager.ConnectionStrings("CalendarConnStr").ConnectionString
        If Not IsPostBack Then
            GetWeekDaysData(0)
        End If
    End Sub
    Protected Sub Bind_Exception_dateRange_to_Grid(ByVal weekDayId)
        Dim exceptionDateDataSet As New DataSet()
        Dim exceptionDateSql As String = "SELECT rangeid," & _
            "DATENAME(month,startdate)as Start_Month,DATENAME(day,startdate)as Start_Day,DATENAME(year,startdate)as Start_Year," & _
            "DATENAME(month,enddate)as End_Month,DATENAME(day,enddate)as End_Day,DATENAME(year,enddate)as End_Year FROM " & _
            "holiday_exception WHERE weekday_id=" & weekDayId & ""

        Dim exceptionDateAdapter As New SqlDataAdapter(exceptionDateSql, Conn)
        Try
            exceptionDateAdapter.Fill(exceptionDateDataSet)
        Catch ex As Exception
            lblMessage.Text = "There is some problem in connecting the database. Please try again later. "
            Exit Sub
        End Try
        If (exceptionDateDataSet.Tables(0).Rows.Count > 0) Then
            'Code need to validate for "if not January 1 1900"
            grdClosingDays.DataSource = exceptionDateDataSet
            Me.grdClosingDays.DataBind()
            'Putting the serial date range
            For Each gv As GridViewRow In grdClosingDays.Rows
                Dim lblDateRnjNo As Label = CType(gv.FindControl("lblDateRnjNo"), Label)
                lblDateRnjNo.Text = gv.RowIndex + 1
            Next
            lblMaxDateRange.Text = grdClosingDays.Rows.Count + 1
        Else
            grdClosingDays.DataBind()
            lblMaxDateRange.Text = ""
        End If
        cboStartMonth.SelectedValue = "Month"
        cboStartDay.SelectedValue = "Day"
        cboStartYear.SelectedValue = "Year"
        cboEndMonth.SelectedValue = "Month"
        cboEndDay.SelectedValue = "Day"
        cboEndYear.SelectedValue = "Year"
    End Sub
    Protected Sub btnAddAnotherDate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddAnotherDate.Click
        lblMessage.Text = ""
        btnSave_Click(Me.btnAddAnotherDate, e)
        cboStartMonth.SelectedValue = "Month"
        cboStartDay.SelectedValue = "Day"
        cboStartYear.SelectedValue = "Year"
        cboEndMonth.SelectedValue = "Month"
        cboEndDay.SelectedValue = "Day"
        cboEndYear.SelectedValue = "Year"
    End Sub
  
    Protected Sub GetWeekDaysData(ByVal holdiayNo)
        lblMessage.Text = ""
        Dim closedDaysDataSet As New DataSet()
        Dim closedDaysSql As String
        If holdiayNo = 0 Then
            closedDaysSql = "SELECT Holiday_ID,Message,Holiday_Day_No FROM WeekDayHolidays " & _
                                          "WHERE Holiday_Day_No=(SELECT MIN(Holiday_Day_No) FROM WeekDayHolidays)"
        Else
            closedDaysSql = "SELECT Holiday_ID,Message,Holiday_Day_No FROM WeekDayHolidays " & _
                             "WHERE Holiday_Day_No=" & holdiayNo & " "
        End If

        Dim closedDaysAdapter As New SqlDataAdapter(closedDaysSql, Conn)
        Try
            closedDaysAdapter.Fill(closedDaysDataSet)
        Catch ex As Exception
            lblMessage.Text = "There is some problem in connecting the database. Please try again later. "
            Exit Sub
        End Try

        If (closedDaysDataSet.Tables(0).Rows.Count > 0) Then
            If closedDaysDataSet.Tables(0).Rows(0)("Holiday_Day_No") = 1 Then
                rbtSunday.Checked = True : Else : rbtSunday.Checked = False
            End If
            If closedDaysDataSet.Tables(0).Rows(0)("Holiday_Day_No") = 2 Then
                rbtMonday.Checked = True : Else : rbtMonday.Checked = False
            End If
            If closedDaysDataSet.Tables(0).Rows(0)("Holiday_Day_No") = 3 Then
                rbtTuesday.Checked = True : Else : rbtTuesday.Checked = False
            End If
            If closedDaysDataSet.Tables(0).Rows(0)("Holiday_Day_No") = 4 Then
                rbtWednesday.Checked = True : Else : rbtWednesday.Checked = False
            End If
            If closedDaysDataSet.Tables(0).Rows(0)("Holiday_Day_No") = 5 Then
                rbtThursdays.Checked = True : Else : rbtThursdays.Checked = False
            End If
            If closedDaysDataSet.Tables(0).Rows(0)("Holiday_Day_No") = 6 Then
                rbtFridays.Checked = True : Else : rbtFridays.Checked = False
            End If
            If closedDaysDataSet.Tables(0).Rows(0)("Holiday_Day_No") = 7 Then
                rbtSatardays.Checked = True : Else : rbtSatardays.Checked = False
            End If
            txtClosedMsg.Text = closedDaysDataSet.Tables(0).Rows(0)("Message").ToString
            Bind_Exception_dateRange_to_Grid(closedDaysDataSet.Tables(0).Rows(0)("Holiday_ID"))
        Else
            Me.grdClosingDays.DataBind()
            lblMaxDateRange.Text = ""
            txtClosedMsg.Text = ""
        End If

    End Sub
    Protected Sub btnEvent_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEvent.Click
        Response.Redirect("Event.aspx")
    End Sub

    Protected Sub btnCategories_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCategories.Click
        Response.Redirect("Category.aspx")
    End Sub

    Protected Sub btnClosedDates_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClosedDates.Click
        Response.Redirect("ClosedDates.aspx")
    End Sub

    Protected Sub btnUsers_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUsers.Click
        Response.Redirect("Users.aspx")
    End Sub
    Function Get_DayNo() As Integer
        If Me.rbtSunday.Checked = True Then
            dayno = 1
        ElseIf Me.rbtMonday.Checked = True Then
            dayno = 2
        ElseIf Me.rbtTuesday.Checked = True Then
            dayno = 3
        ElseIf Me.rbtWednesday.Checked = True Then
            dayno = 4
        ElseIf Me.rbtThursdays.Checked = True Then
            dayno = 5
        ElseIf Me.rbtFridays.Checked = True Then
            dayno = 6
        Else : Me.rbtSatardays.Checked = True
            dayno = 7
        End If
        Return dayno
    End Function
    Protected Function Validate_Date()

        Dim retStatus As Boolean = True
        If cboStartMonth.SelectedValue <> "Month" Or cboStartDay.SelectedValue <> "Day" Or cboStartYear.SelectedValue <> "Year" Or cboEndMonth.SelectedValue <> "Month" Or cboEndDay.SelectedValue <> "Day" Or cboEndYear.SelectedValue <> "Year" Then
            Try
                StartDate = "" & cboStartMonth.SelectedValue & "-" & cboStartDay.SelectedValue & "-" & cboStartYear.SelectedValue & ""
                EndDate = "" & cboEndMonth.SelectedValue & "-" & cboEndDay.SelectedValue & "-" & cboEndYear.SelectedValue & ""
                If StartDate > EndDate Then
                    If lblMessage.Text <> "" Then
                        lblMessage.Text = lblMessage.Text + "<BR>" + "* Start Date Should not Greater than End Date!"
                    Else
                        lblMessage.Text = "* Start Date Should not Greater than End Date!"
                    End If
                    cboStartMonth.Focus()
                    retStatus = False
                End If
            Catch ex As Exception
                lblMessage.Text = "* Not a Valid Date!"
                cboStartMonth.Focus()
                retStatus = False
                'Exit Function
            End Try
        Else
            retStatus = False
        End If

        If retStatus = False Then
            Return False
        Else
            Return True
        End If
    End Function


    Protected Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        lblMessage.Text = ""
        ' ''get the day no
        dayno = Get_DayNo()

        ' ''Finding the Holiday no to check for availabilty
        Dim holidayNoSql As String = "SELECT Holiday_Day_No FROM WeekDayHolidays"
        Dim holidayNoAdapter As New SqlDataAdapter(holidayNoSql, Conn)
        Dim holidayNoDataSet As New DataSet
        Try
            holidayNoAdapter.Fill(holidayNoDataSet)
        Catch ex As Exception
            lblMessage.Text = "There is some problem in connecting the database. Please try again later. "
            Exit Sub
        End Try

        ' ''Checking for Holiday_day_no in database
        Dim i As Integer = 0
        Dim insert As Boolean = True
        If holidayNoDataSet.Tables(0).Rows.Count > 0 Then
            For i = 0 To holidayNoDataSet.Tables(0).Rows.Count - 1
                If dayno = holidayNoDataSet.Tables(0).Rows(i)(0) Then
                    insert = False
                    Exit For
                End If
            Next
        End If

        Dim selecteedHolidayID As Integer
        Dim saveCommand As SqlCommand = New SqlCommand()
        Dim saveTrans As SqlTransaction
        Conn.Open()
        saveCommand.Connection = Conn
        saveTrans = Conn.BeginTransaction()
        saveCommand.Transaction = saveTrans
        Try

            'IF radio is not Checked
            If insert = True Then

                saveCommand.CommandText = "INSERT INTO WeekDayHolidays VALUES('" & dayno & "','" & txtClosedMsg.Text.ToString() & "')"
                saveCommand.ExecuteNonQuery()

                ' ''Get The Holiday Id that is recentely entered
                saveCommand.CommandText = "SELECT Holiday_ID FROM WeekDayHolidays WHERE Holiday_Day_No='" & dayno & "'"
                selecteedHolidayID = saveCommand.ExecuteScalar()

                'saveCommand.CommandText = "SELECT ISNULL((SELECT MAX(Holiday_ID) FROM WeekDayHolidays),0)AS Holiday_ID"
                'Dim maxHolidayId As Integer = saveCommand.ExecuteScalar()
                'hdnAlreadySaved.Value = maxHolidayId
                If Validate_Date() Then
                    If cboStartMonth.SelectedValue <> "Month" Or cboStartDay.SelectedValue <> "Day" Or cboStartYear.SelectedValue <> "Year" Or cboEndMonth.SelectedValue <> "Month" Or cboEndDay.SelectedValue <> "Day" Or cboEndYear.SelectedValue <> "Year" Then
                        saveCommand.CommandText = "INSERT INTO holiday_exception(weekday_id,startdate,enddate,status,ceatedby,createdon) VALUES( " & _
                              "" & selecteedHolidayID & ",'" & StartDate & "','" & EndDate & "','True'," & Session("user_id") & ",getdate())"
                        saveCommand.ExecuteNonQuery()
                    End If
                End If
            Else
                'IF radio Checked

                ' ''Get The Holiday Id selected for the day
                saveCommand.CommandText = "SELECT Holiday_ID FROM WeekDayHolidays WHERE Holiday_Day_No='" & dayno & "'"
                selecteedHolidayID = saveCommand.ExecuteScalar()

                saveCommand.CommandText = "UPDATE WeekDayHolidays SET Message='" & txtClosedMsg.Text.ToString() & "' WHERE Holiday_Day_No='" & dayno & "'"
                saveCommand.ExecuteNonQuery()
                If Validate_Date() Then
                    If cboStartMonth.SelectedValue <> "Month" Or cboStartDay.SelectedValue <> "Day" Or cboStartYear.SelectedValue <> "Year" Or cboEndMonth.SelectedValue <> "Month" Or cboEndDay.SelectedValue <> "Day" Or cboEndYear.SelectedValue <> "Year" Then
                        saveCommand.CommandText = "INSERT INTO holiday_exception(weekday_id,startdate,enddate,status,ceatedby,createdon) VALUES( " & _
                              "" & selecteedHolidayID & ",'" & StartDate & "','" & EndDate & "','True'," & Session("user_id") & ",getdate() )"
                        saveCommand.ExecuteNonQuery()
                    End If
                End If
                ' ''Update Multiple date range
                For Each gv As GridViewRow In grdClosingDays.Rows
                    Dim stDate As Date
                    Dim endDate As Date
                    Dim rangeId As Integer = CType(gv.FindControl("lblDateRangeId"), Label).Text.ToString()
                    Dim cboStartMonth As DropDownList = CType(gv.FindControl("cboStartMonth"), DropDownList)
                    Dim cboStartDay As DropDownList = CType(gv.FindControl("cboStartDay"), DropDownList)
                    Dim cboStartYear As DropDownList = CType(gv.FindControl("cboStartYear"), DropDownList)
                    Dim cboEndMonth As DropDownList = CType(gv.FindControl("cboEndMonth"), DropDownList)
                    Dim cboEndDay As DropDownList = CType(gv.FindControl("cboEndDay"), DropDownList)
                    Dim cboEndYear As DropDownList = CType(gv.FindControl("cboEndYear"), DropDownList)
                    Try
                        stDate = "" & cboStartMonth.SelectedValue & "-" & cboStartDay.SelectedValue & "-" & cboStartYear.SelectedValue & ""
                        endDate = "" & cboEndMonth.SelectedValue & "-" & cboEndDay.SelectedValue & "-" & cboEndYear.SelectedValue & ""
                        If stDate > endDate Then
                            If lblMessage.Text <> "" Then
                                lblMessage.Text = lblMessage.Text + "<BR>" + "* Start Date Should not Greater than End Date!"
                            Else
                                lblMessage.Text = "* Start Date Should not Greater than End Date!"
                            End If
                            cboStartMonth.Focus()
                            Exit Sub
                        Else
                            saveCommand.CommandText = "UPDATE holiday_exception " & _
                                                        "SET startdate ='" & stDate & " ',enddate='" & endDate & "' " & _
                                                        "WHERE rangeid=" & rangeId & " "
                            saveCommand.ExecuteNonQuery()
                        End If

                    Catch ex As Exception
                        lblMessage.Text = "* Not a Valid Date!"
                        Exit Sub
                    End Try
                Next
                End If

                saveTrans.Commit()
        Catch ex As Exception
            saveTrans.Rollback()
            lblMessage.Text = ex.Message
            Exit Sub
        Finally
            Conn.Close()
        End Try
        Bind_Exception_dateRange_to_Grid(selecteedHolidayID)
    End Sub
    Protected Sub Btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btndelete.Click
        dayno = Get_DayNo()
        Dim selecteedHolidayID As Integer
        Dim DeleteCommand As SqlCommand = New SqlCommand()
        Dim DeleteTrans As SqlTransaction
        Conn.Open()
        DeleteCommand.Connection = Conn
        DeleteTrans = Conn.BeginTransaction()
        DeleteCommand.Transaction = DeleteTrans
        Try
            ' ''Get The Holiday Id selected for the day
            DeleteCommand.CommandText = "SELECT Holiday_ID FROM WeekDayHolidays WHERE Holiday_Day_No=" & dayno & ""
            selecteedHolidayID = DeleteCommand.ExecuteScalar()

            DeleteCommand.CommandText = "DELETE FROM holiday_exception WHERE rangeid IN(SELECT rangeid FROM holiday_exception WHERE weekday_id=" & selecteedHolidayID & ") "
            DeleteCommand.ExecuteNonQuery()

            DeleteCommand.CommandText = "DELETE FROM WeekDayHolidays WHERE Holiday_ID=" & selecteedHolidayID & ""
            DeleteCommand.ExecuteNonQuery()

            DeleteTrans.Commit()
        Catch ex As Exception
            DeleteTrans.Rollback()
            lblMessage.Text = ex.Message
            Exit Sub
        Finally
            Conn.Close()
        End Try

        Me.grdClosingDays.DataBind()
        lblMaxDateRange.Text = ""
        txtClosedMsg.Text = ""
    End Sub

    Private Sub rbtSunday_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles rbtSunday.CheckedChanged
        If IsPostBack Then
            GetWeekDaysData(1)
        End If
    End Sub
    Private Sub rbtMonday_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles rbtMonday.CheckedChanged
        If IsPostBack Then
            GetWeekDaysData(2)
        End If
    End Sub
    Private Sub rbtTuesday_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles rbtTuesday.CheckedChanged
        If IsPostBack Then
            GetWeekDaysData(3)
        End If
    End Sub
    Private Sub rbtWednesday_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles rbtWednesday.CheckedChanged
        If IsPostBack Then
            GetWeekDaysData(4)
        End If
    End Sub

    Private Sub rbtThursdays_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles rbtThursdays.CheckedChanged
        If IsPostBack Then
            GetWeekDaysData(5)
        End If
    End Sub
    Private Sub rbtFridays_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles rbtFridays.CheckedChanged
        If IsPostBack Then
            GetWeekDaysData(6)
        End If
    End Sub
    Private Sub rbtSatardays_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles rbtSatardays.CheckedChanged
        If IsPostBack Then
            GetWeekDaysData(7)
        End If
    End Sub

    Protected Sub btnGrdDeleteDtRange_Command(ByVal sender As System.Object, ByVal e As System.Web.UI.WebControls.CommandEventArgs)
        ' ''Delete a Date Range From Multiple date range
        Dim DeleteCommand As SqlCommand = New SqlCommand()

        For Each gv As GridViewRow In grdClosingDays.Rows
            Dim stDate As Date
            Dim endate As Date
            Dim rangeId As Integer = CType(gv.FindControl("lblDateRangeId"), Label).Text.ToString()
            If rangeId = e.CommandArgument Then
                Dim cboStartMonth As DropDownList = CType(gv.FindControl("cboStartMonth"), DropDownList)
                Dim cboStartDay As DropDownList = CType(gv.FindControl("cboStartDay"), DropDownList)
                Dim cboStartYear As DropDownList = CType(gv.FindControl("cboStartYear"), DropDownList)
                Dim cboEndMonth As DropDownList = CType(gv.FindControl("cboEndMonth"), DropDownList)
                Dim cboEndDay As DropDownList = CType(gv.FindControl("cboEndDay"), DropDownList)
                Dim cboEndYear As DropDownList = CType(gv.FindControl("cboEndYear"), DropDownList)

                Try
                    stDate = "" & cboStartMonth.SelectedValue & "-" & cboStartDay.SelectedValue & "-" & cboStartYear.SelectedValue & ""
                    endate = "" & cboEndMonth.SelectedValue & "-" & cboEndDay.SelectedValue & "-" & cboEndYear.SelectedValue & ""
                Catch ex As Exception
                    lblMessage.Text = "* Not a Valid Date!"
                    Exit Sub
                End Try
                DeleteCommand.Connection = Conn
                Conn.Open()
                Try
                    DeleteCommand.CommandText = "DELETE FROM holiday_exception WHERE rangeid= " & rangeId & " "
                    DeleteCommand.ExecuteNonQuery()
                Catch ex As Exception
                    lblMessage.Text = ex.Message
                    Exit Sub
                Finally
                    Conn.Close()
                End Try
                'Display the Existing date ranges
                GetWeekDaysData(Get_DayNo())
                Exit Sub
            End If
        Next
    End Sub

    Protected Sub lnkLogout_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lnkLogout.Click
        Session.Abandon()
        Response.Redirect("Login.aspx")
    End Sub
End Class