Home > Net >  How to implement the calendar layout logic in ASP.NET and C# with MS SQL Database
How to implement the calendar layout logic in ASP.NET and C# with MS SQL Database

Time:04-18

I want to implement this layout In C#

I have this project at school and I have created a project with homePage, member signUp, member Login, member profile, and Admin Login pages I want to implement this layout on my homepage when the user is logged in and be able to select dates when an employee is working from home should choose a date and insert into a database for the employer to see who is working from home on this specific date or day.The picture below is my Home page currently I want to change it to the layout with a calendar

CodePudding user response:

Well, if we break this down?

We need a listbox on the left side to display the Employee's

So, drop in a listbox, say like this:

        <div style="float:left">
            <asp:ListBox ID="lstEmployee" runat="server" 
                DataValueField="ID"
                DataTextField="EmpName" Height="270px" Width="125px" 
                AutoPostBack="True" 
                OnSelectedIndexChanged="lstEmployee_SelectedIndexChanged" >
            </asp:ListBox>
        </div>

And our code to load is thus this:

    DateTime dtStart, dtEnd;    // start and end of this month
    DateTime CalStart, CalEnd;  // start and end of the whole cal display

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            LoadEmployee();
            LoadCal();
        }
    }

    void LoadEmployee()
    {
        // load up employee listbox
        SqlCommand cmdSQL = new SqlCommand(
          "SELECT ID,(FirstName   ' '   LastName) as EmpName "  
          "FROM Employee ORDER BY FirstName");

        lstEmployee.DataSource = MyRstP(cmdSQL);
        lstEmployee.DataBind();
    }

And now we have this:

enter image description here

Ok, now we need a calendar. Hum, ok, looking at outlook, we see that we need 6 rows of a "thing".

I think a simple Listview, and for each row (6 of them), we need a data table, with 1-7 days of data.

So, a simple list view, one row - but we spit out 6 of them.

and we want the calendar to be clickable, so each day will be a button - a LinkButton should work fine.

So, a list view could be like this:

        <div style="float:left;margin-left:25px">
        <asp:ListView ID="ListView1" runat="server" OnItemDataBound="ListView1_ItemDataBound1"  >
            <ItemTemplate>
              <tr id="OneRow" runat="server"> 
                 <td>
                    <asp:Linkbutton ID="L1" runat="server" Text='<%# Eval("Sun", "{0:dd}") %>'
                    bDate = '<%# Eval("Sun") %>' OnClick="L1_Click"  />
                </td>
                <td>
                    <asp:Linkbutton ID="L2" runat="server" Text='<%# Eval("Mon", "{0:dd}") %>' 
                     bDate = '<%# Eval("Mon") %>' OnClick="L1_Click" />
                </td>
                <td>
                    <asp:Linkbutton ID="L3" runat="server" Text='<%# Eval("Tue", "{0:dd}") %>' 
                    bDate = '<%# Eval("Tue") %>' OnClick="L1_Click" />
                </td>
                <td>
                    <asp:Linkbutton ID="L4" runat="server" Text='<%# Eval("Wed", "{0:dd}") %>'
                    bDate = '<%# Eval("Wed") %>' OnClick="L1_Click" />
                </td>
                <td>
                    <asp:Linkbutton ID="L5" runat="server" Text='<%# Eval("Thu", "{0:dd}") %>'
                    bDate = '<%# Eval("Thu") %>' OnClick="L1_Click" />
                </td>
                <td>
                    <asp:Linkbutton ID="L6" runat="server" Text='<%# Eval("Fri", "{0:dd}") %>'
                    bDate = '<%# Eval("Fri") %>' OnClick="L1_Click" />
                </td>
                <td>
                    <asp:Linkbutton ID="L7" runat="server" Text='<%# Eval("Sat", "{0:dd}") %>'
                    bDate = '<%# Eval("Sat") %>' OnClick="L1_Click" />
                </td>
              </tr>
            </ItemTemplate>
            <LayoutTemplate>
                <table id="itemPlaceholderContainer" runat="server" border="0" style="">
                <tr runat="server" style="">
                    <th runat="server" style="text-align:center" >Sun</th>
                    <th runat="server" style="text-align:center">Mon</th>
                    <th runat="server" style="text-align:center">Tue</th>
                    <th runat="server" style="text-align:center">Wed</th>
                    <th runat="server" style="text-align:center">Thr</th>
                    <th runat="server" style="text-align:center">Fri</th>
                    <th runat="server" style="text-align:center">Sat</th>
                </tr>
                <tr id="itemPlaceholder" runat="server"></tr>
                </table>
            </LayoutTemplate>
        </asp:ListView>

Not a lot of markup.

Now, we need to load up the data, so we have this:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            LoadEmployee();
            LoadCal();
        }
    }

And the code to load up the calendar? A data table of 6 rows, and 7 columns, say like this code:

    void LoadCal()
    {
        SetupDates();

        DataTable OneMonth = new DataTable();
        // add heading colums to table (Sun->Sat)
        for (int i = 0; i <= 6; i  )
        {
            DateTime d = CalStart.AddDays(i);
            string strCol = d.ToString("ddd"); // gets day of week as text (sun->sat)
            OneMonth.Columns.Add(new DataColumn(strCol, typeof(DateTime)));
        }

        DateTime dtPtr = CalStart;   // start a simple date counter - upper left
        // add 6 rows for calendar
        for (int i = 1; i <= 6; i  )
        {
            DataRow OneRow = OneMonth.Rows.Add();
            for (int wDay = 0; wDay <= 6; wDay  ) // put date in each colum 1 - 7
            {
                OneRow[wDay] = dtPtr;   // shove date into cal square
                dtPtr = DateAndTime.DateAdd(DateInterval.Day, 1, dtPtr);
            }
            ListView1.DataSource = OneMonth;
            ListView1.DataBind();
        }
    }

And we now have this:

enter image description here

Now, I did want to "gray" out the dates off this month, so in the Listview databound event (a great event for formatting grids or listview), then I put in this code

If the date ptr is outside of this month, we grey out the linkbutton.

so, this:

    protected void ListView1_ItemDataBound1(object sender, ListViewItemEventArgs e)
    {
        if (e.Item.ItemType == ListViewItemType.DataItem)
        {
            ListViewDataItem lvRow = e.Item as ListViewDataItem;
            for (int MyDay = 1; MyDay <= 7;MyDay  )
            {
                LinkButton LDate = lvRow.FindControl("L"   MyDay) as LinkButton;
                DateTime BoxDate =  Convert.ToDateTime(LDate.Attributes["bDate"]);
                if ( (BoxDate < dtStart) | (BoxDate > dtEnd)) 
                    LDate.BackColor = System.Drawing.Color.LightGray;

                // this sets the size for ALL squares 
                LDate.Attributes.Add("style", "float:right;height:70px;width:60px;text-align:right");
            }
        }
    }

Ok, so now we have to add some code to highlight a square if this is a work at home date.

We have this data table:

enter image description here

So, after we load the Grid, then we click on a list view item.

That code is this:

    protected void lstEmployee_SelectedIndexChanged(object sender, EventArgs e)
    {
        int EmployeeID = 0;
        EmployeeID = Convert.ToInt32(lstEmployee.SelectedValue);
        ShowOffDays(EmployeeID);
    }

So, we need a routine to now display (highlight) any record in the table based on employee id and the given date.

So this:

    void ShowOffDays(int EmpID)
    {
        SetupDates();

        var cmdSQL = new SqlCommand(
            "SELECT * FROM AtHome WHERE EmployeeID = @EmpID "  
            "AND AtHomeDate BETWEEN @dtSTart AND @dtEnd");

        cmdSQL.Parameters.Add("@EmpID", SqlDbType.Int).Value = EmpID;
        cmdSQL.Parameters.Add("@dtStart", SqlDbType.Date).Value = dtStart;
        cmdSQL.Parameters.Add("@dtEnd", SqlDbType.Date).Value = dtEnd;

        DataTable rstAtHome = new DataTable();
        rstAtHome = MyRstP(cmdSQL);

        DateTime dtPtr = CalStart;
        for(int i = 0;i<=5;i  )
        {
            ListViewItem lvRow = ListView1.Items[i];
            for (int wDay = 1;wDay <= 7;wDay  )
            {
                if ((dtPtr >= dtStart) & (dtPtr <= dtEnd))   // only highlit this month
                {
                    LinkButton LDate = lvRow.FindControl("L"   wDay) as LinkButton;
                    DataRow[] tView = rstAtHome.Select("AtHomeDate = #"   dtPtr   "#");
                    if (tView.Length == 0)
                        // no at home date
                        LDate.BackColor = System.Drawing.Color.White;
                    else
                        // found a date - blue the square0
                        LDate.BackColor = System.Drawing.Color.LightBlue;
                }
                dtPtr = DateAndTime.DateAdd(DateInterval.Day, 1, dtPtr);
            }
        }
    }

Now, that was a bit of code - but not too bad.

So, now we get this:

enter image description here

Ok, so now we need a click event for the square. If you click a empty square, we add a single row to the table, and if already highliged, we delete it.

So, that click event can be this:

    protected void L1_Click(object sender, EventArgs e)
    {
        LinkButton btn = sender as LinkButton;
        DateTime dtDateV = Convert.ToDateTime(btn.Attributes["bDate"]);
        int EmpID = Convert.ToInt32(lstEmployee.SelectedItem.Value);

        DataTable rstDayOff = new DataTable();
        using (var conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            using (var cmdSQL = new SqlCommand(
                "SELECT * FROM AtHome WHERE EmployeeID = @EmpID "  
                "AND AtHomeDate = @Date", conn))
            {
                cmdSQL.Parameters.Add("@EmpID", SqlDbType.Int).Value = EmpID;
                cmdSQL.Parameters.Add("@Date", SqlDbType.Date).Value = dtDateV;
                conn.Open();
                rstDayOff.Load(cmdSQL.ExecuteReader());
                if (rstDayOff.Rows.Count == 0 )
                {
                    // add this day off to table
                    DataRow OneDay = rstDayOff.Rows.Add();
                    OneDay["EmployeeID"] = EmpID;
                    OneDay["AtHomeDate"] = dtDateV;
                    btn.BackColor = System.Drawing.Color.LightBlue;
                }
                else
                {
                    // delete the at home day
                    rstDayOff.Rows[0].Delete();
                    btn.BackColor = System.Drawing.Color.White;
                }
                SqlDataAdapter da = new SqlDataAdapter(cmdSQL);
                SqlCommandBuilder daU = new SqlCommandBuilder(da);
                da.Update(rstDayOff);
            }
        }
    }

Not bad! So, now you can click to highlight, or un-highlight a day. (it toggles the given square).

So, above should give you some ideas.

And the two helper routines I used was to get a data table. And the other to setup the dates.

These:

    void SetupDates()
    {
        DateTime dt = DateTime.Today;

        dtStart = DateAndTime.DateSerial(DateAndTime.Year(dt), DateAndTime.Month(dt), 1);
        int DaysInMonth = DateTime.DaysInMonth(DateAndTime.Year(dt),DateAndTime.Month(dt));

        // end of month = DaysInmonth less one
        dtEnd = DateAndTime.DateAdd(DateInterval.Day, DaysInMonth - 1, dtStart);

        // now with date end/start - get display start/end (subtract day of week)

        CalStart = DateAndTime.DateAdd(DateInterval.Day, 1 - (DateAndTime.Weekday(dtStart)), dtStart);
        CalEnd = DateAndTime.DateAdd(DateInterval.Day, 41, CalStart);

    }

and

    DataTable MyRstP(SqlCommand cmdSQL)
    {
        DataTable rstData = new DataTable();
        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            using (cmdSQL)
            {
            cmdSQL.Connection = conn;
            conn.Open();
            rstData.Load(cmdSQL.ExecuteReader());
            }
        }
        return rstData;
    }

So, it was a bit of code, but breaking down each part - into smaller bits and sizes? It was not really that hard.

  • Related