protected void Page_Load(Object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            cal2.VisibleDate = DateTime.Today;
            FillLeaveplannerDataset();
        }
    }
    protected void FillLeaveplannerDataset()
    {
        cal2.VisibleDate = cal2.TodaysDate;
        DateTime firstDate = new DateTime(cal2.VisibleDate.Year, cal2.VisibleDate.Month, 1).AddDays(-6);
        DateTime lastDate = new DateTime(cal2.VisibleDate.Date.AddMonths(1).Year, cal2.VisibleDate.Date.AddMonths(1).Month, 1).AddDays(7);
        dsleaveplanner = GetCurrentMonthData(firstDate, lastDate);
    }
    protected DateTime GetFirstDayOfNextMonth()
    {
        int monthNumber, yearNumber;
        if (cal2.VisibleDate.Month == 12)
        {
            monthNumber = 1;
            yearNumber = cal2.VisibleDate.Year + 1;
        }
        else
        {
            monthNumber = cal2.VisibleDate.Month + 1;
            yearNumber = cal2.VisibleDate.Year;
        }
        DateTime lastDate = new DateTime(yearNumber, monthNumber, 1);
        return lastDate;
    }
    protected DataSet GetCurrentMonthData(DateTime firstDate, DateTime lastDate)
    {
        DataSet dsMonth = new DataSet();
        MySqlConnection con = new MySqlConnection("Server=localhost;Database=mydb;Uid=myid;Pwd=abc123;");
        string caldate = "Select date From approved Where date >= @firstDate And date <= @lastDate Group By date";
        MySqlCommand cmd = new MySqlCommand(caldate, con);
        cmd.Parameters.AddWithValue("@firstDate", firstDate);
        cmd.Parameters.AddWithValue("@lastDate", lastDate);
        MySqlDataAdapter mysqlDataAdapter = new MySqlDataAdapter(cmd);
        try
        {
            mysqlDataAdapter.Fill(dsMonth);
        }
        catch { }
        return dsMonth;
    }

    protected void Calendar1_DayRender(object sender, DayRenderEventArgs e)
    {
        DateTime nextDate;
        if (dsleaveplanner != null)
        {
            foreach (DataRow dr in dsleaveplanner.Tables[0].Rows)
            {
                nextDate = (DateTime)dr["date"];
                MySqlConnection conn = new MySqlConnection("Server=localhost;Database=mydb;Uid=myid;Pwd=abc123;");
                string cntdate = "SELECT COUNT(date) FROM approved WHERE date = @date";
                string cntdate2 = "SELECT COUNT(date) FROM pending WHERE date = @date";
                MySqlCommand cmd2 = new MySqlCommand(cntdate, conn);
                MySqlCommand cmd3 = new MySqlCommand(cntdate2, conn);
                cmd2.Parameters.AddWithValue("@date", nextDate);
                cmd3.Parameters.AddWithValue("@date", nextDate);
                conn.Open();
                string count = cmd2.ExecuteScalar().ToString();
                string count2 = cmd3.ExecuteScalar().ToString();
                var slot2 = Convert.ToInt32(count);
                Int32 slot3 = 10 - slot2;
                string slot4 = slot3.ToString();
                conn.Close();
                 if (nextDate == e.Day.Date)
                {
                    e.Cell.BackColor = System.Drawing.Color.Orange;
                    Environment.NewLine.ToString();
                    e.Cell.ForeColor = System.Drawing.Color.Red;
                    e.Cell.Font.Size = 9;
                    e.Cell.Controls.Add(new LiteralControl("<p></p>Slot available:"));
                    e.Cell.Controls.Add(new LiteralControl(slot4));
                    e.Cell.Controls.Add(new LiteralControl("<p></p>Pending:"));
                    e.Cell.Controls.Add(new LiteralControl(count2));
                }
                else
                {
                    e.Cell.Font.Size = 9;
                    e.Cell.Controls.Add(new LiteralControl("<p>Slot available: 10</p>"));
                    e.Cell.Controls.Add(new LiteralControl("<p></p>Pending: 0"));
                }
            }
        }

    }
    protected void Calendar1_VisibleMonthChanged(object sender,
    MonthChangedEventArgs e)
    {
        DateTime firstDate = e.NewDate.AddDays(-7);
        DateTime lastDate = e.NewDate.AddMonths(1).AddDays(7);
        dsleaveplanner = GetCurrentMonthData(firstDate, lastDate);

    }