Search (Filter) Bootstrap Year Calendar with DropDownList using Web Service in ASP.Net

Kibock
 
on Jul 23, 2020 11:40 PM
Sample_144179.zip
1707 Views

Hi,

I would like to filter the user record on FullCalendar using 2 DropDownList: Selected UserEmail and Selected Year.

Based on the following article: Integrate Bootstrap Year Calendar with data from database using jQuery Ajax in ASP.Net

The search should display only record in Calendar with corresponding email and year.

Code: I am using Ajax Web service.

***** ForumYearCalendar.aspx *****

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css" />
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/2.4.4/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>

<link href="ForumCss/bootstrap-year-calendar.min.css" rel="stylesheet" />
<script src="ForumCss/bootstrap-year-calendar.min.js"></script> 

   <section class="content">
        <div class="container-fluid">
            <div class="row" style="justify-content: center;">
                <div class="col-md-12">
                    <div class="card">
                        <div class="card-header" style="background-color: #cc6028; color: #ffffff;">
                            <span class="card-title" style="font-size: 13px;">KALENDER - Search User Data in Calendar</span>
                        </div>
                        <div class="card-body col-md-12" style="background-color: #ffffff;">
                            <div class="row">
                                <div class="col-xs-3">
                                    <div class="input-group">
                                        <div class="input-group-prepend">
                                            <span class="input-group-text">
                                                <label class="text small" style="color: #063b66; font-size: 10px; font-weight: bold;">EMAIL</label>
                                            </span>
                                            <asp:DropDownList ID="ddlUserEmail" runat="Server" CssClass="form-control"></asp:DropDownList>
                                        </div>
                                    </div>
                                </div>
                                <div class="col-xs-3">
                                    <div class="input-group">
                                        <div class="input-group-prepend">
                                            <span class="input-group-text">
                                                <label class="text small" style="color: #063b66; font-size: 10px; font-weight: bold;">YEAR</label>
                                            </span>
                                            <asp:DropDownList ID="ddlCalendarYear" runat="Server" CssClass="form-control"></asp:DropDownList>
                                        </div>
                                    </div>
                                </div>
                                <div class="col-xs-3">
                                    <div class="">
                                        <div class="input-group-prepend">
                                            <asp:LinkButton ID="searchUser" runat="server" OnClick="searchUser_Calendar" ForeColor="#ffffff" CssClass="btn btn-secondary">
                               <i class="fas fa-search"></i> Search
                                            </asp:LinkButton>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </section>

    <div id="calendar"></div>

    <script>

        $(document).ready(function () {

            // Return today's date and time
            var currentTime = new Date()
            // returns the month (from 0 to 11)
            var currentMonth = currentTime.getMonth() + 1
            // returns the day of the month (from 1 to 31)
            var currentDay = currentTime.getDate()
            // returns the year (four digits)
            var currentYear = currentTime.getFullYear()

            var circleDateTime = new Date(currentYear, (currentMonth - 1), currentDay).getTime();

            function ConvertJsonDateToDateTime(date) {
                var parsedDate = new Date(parseInt(date.substr(6)));
                var newDate = new Date(parsedDate);
                return newDate;
            }
            $.ajax({

                url: 'ForumFullCalendarAjaxService.asmx/GetDetails',
                data: {},
                type: 'post',
                contentType: 'application/json; charset=utf-8',
                dataType: 'json',
                success: function (r) {

                    for (var i = 0; i < r.d.length; i++) {
                        r.d[i].startDate = ConvertJsonDateToDateTime(r.d[i].startdato);
                        r.d[i].endDate = ConvertJsonDateToDateTime(r.d[i].sluttdato);
                    }

                    //This is what make the calendar show and work
                    $('#calendar').calendar({
                        enableContextMenu: true,
                        enableRangeSelection: true,
                        displayWeekNumber: true,
                        // style: 'background',
                        //weekStart: 1,
                        // firstDay: 1,

                        contextMenuItems: [
                            {
                                text: 'Update',
                                click: editEvent
                            },
                            {
                                text: 'Delete',
                                click: deleteEvent
                            }
                        ],
                        selectRange: function (e) {

                        },
                        //This is to show it if it has events
                        mouseOnDay: function (e) {
                            if (e.events.length > 0) {
                                var content = '';

                                for (var i in e.events) {
                                    content += '<div class="event-tooltip-content" style="background-color:#f8c17e;">'
                                       + '<div class="userEmail">' + "Deltaker: " + e.events[i].useremail + '</div>'
                                       + '<div class="event-name" style="color:' + e.events[i].color + '">' + "Forum Name: " + e.events[i].forumname + '</div>'
                                       + '<div class="deets">' + e.events[i].forumformaal + '</div>'
                                       + '<div class="event-location">' + e.events[i].forumvenue + '</div>'
                                       + '<div>' + e.events[i].forumstatus + '</div>'
                                       + '</div>';

                                }
                                $(e.element).popover({
                                    trigger: 'manual',
                                    container: 'body',
                                    html: true,
                                    content: content
                                });

                                $(e.element).popover('show');
                            }
                        },
                        //This is to hide the popover
                        mouseOutDay: function (e) {
                            if (e.events.length > 0) {

                                $(e.element).popover('hide');
                            }
                        },

                        dayContextMenu: function (e) {
                            $(e.element).popover('hide');
                        },

                        customDayRenderer: function (element, date) {

                            if (date.getTime() == circleDateTime) {
                                $(element).css('background-color', 'red');
                                $(element).css('color', 'white');
                                $(element).css('border-radius', '15px');
                            }
                        },

                        customDataSourceRenderer: function (element, event) {
                            // This will override the background-color to the event's color
                            $(element).css('background-color', event[0].color);
                            $(element).css('border-radius', '15px');
                        },
                        dataSource: r.d
                    });
                }
            });

            function editEvent(event) {
                $('#event-modal input[name="event-index"]').val(event ? event.forumdeltakerid : '');
                $('#event-modal input[name="event-name"]').val(event ? event.forumname : '');
                $('#event-modal input[name="event-location"]').val(event ? event.forumvenue : '');
                $('#event-modal input[name="event-start-date"]').datepicker('update', event ? event.startdato : '');
                $('#event-modal input[name="event-end-date"]').datepicker('update', event ? event.sluttdato : '');
                $('#event-modal').modal();
            }

            function deleteEvent(event) {
                var dataSource = $('#calendar').data('calendar').getDataSource();

                for (var i in dataSource) {
                    if (dataSource[i].id == event.id) {
                        dataSource.splice(i, 1);
                        break;
                    }
                }

                $('#calendar').data('calendar').setDataSource(dataSource);
            }
        });

    </script>

 

***** ForumFullCalendarAjaxService.asmx.cs *****

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Web.Services;

    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    
     [System.Web.Script.Services.ScriptService]
    public class ForumFullCalendarAjaxService : System.Web.Services.WebService
    {
        [WebMethod]
        public List<ForumCalendarYear> GetDetails()
        {
            List<ForumCalendarYear> events = new List<ForumCalendarYear>();

            string conString = ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;

            string query = "Select * FROM Forum";

            SqlCommand cmd = new SqlCommand(query);

            using (SqlConnection con = new SqlConnection(conString))
            {
                cmd.Connection = con;
                con.Open();

                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        events.Add(new ForumCalendarYear
                        {
                            forumdeltakerid     = Convert.ToInt32(sdr["ForumDeltakerID"]),
                            useremail           = sdr["UserEmail"].ToString(),
                            forumname           = sdr["ForumName"].ToString(),
                            forumvenue          = sdr["ForumVenue"].ToString(),
                            forumformaal        = sdr["ForumFormaal"].ToString(),
                            startdato           = Convert.ToDateTime(sdr["ForumStartDato"]),
                            sluttdato           = Convert.ToDateTime(sdr["ForumSluttDato"]),
                            forumstatus         = sdr["AdminForumStatus"].ToString(),
                        });
                    }
                }
                con.Close();
            }
            return events;
        }
    }

***** ForumCalendarYear.cs *****

    public class ForumCalendarYear
    {
        public int forumdeltakerid { get; set; }
        public string useremail { get; set; }
        public string forumname { get; set; }
        public string forumvenue { get; set; }
        public string forumformaal { get; set; }
        public DateTime startdato { get; set; }
        public DateTime sluttdato { get; set; }
        public string forumstatus { get; set; }
    }

 

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

 public partial class ForumYearCalendar : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Populate_UserEmails();
                Populate_CalendarYears();
            }
        }
        protected void Populate_UserEmails()
        {
            ddlUserEmail.Items.Add(new System.Web.UI.WebControls.ListItem("Select User Email", "Select User Email"));
            ddlUserEmail.AppendDataBoundItems = true;

            DataTable DSource = new DataTable();

            string constr = ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                string query = "Select * DISTINCT UserEmail, UserId FROM Forum ORDER BY UserEmail ASC";

                SqlDataAdapter adapter = new SqlDataAdapter(query, con);
                adapter.Fill(DSource);

                ddlUserEmail.DataSource = DSource;
                ddlUserEmail.DataTextField = "UserEmail";
                ddlUserEmail.DataValueField = "UserId";
                ddlUserEmail.DataBind();
            }
        }
        protected void Populate_CalendarYears()
        {
            for (int intYear = DateTime.Now.Year - 2; intYear <= DateTime.Now.Year + 2; intYear++)
            {
                ddlCalendarYear.Items.Add(intYear.ToString());
            }
            //Make the current year selected item in the list
            ddlCalendarYear.Items.FindByValue((DateTime.Now.Year).ToString()).Selected = true;
        }
        protected void searchUser_Calendar(object sender, EventArgs e)
        {

        }
    }

How to implement the search_Calendar method ?

Many thanks in advance

Kibock

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 24, 2020 04:31 AM

Refer below code. Set the selected statement as per your table structure and datatype with DropDownlist selected value.

HTML

<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css" />
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css" />
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/2.4.4/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<link href="bootstrap-year-calendar.min.css" rel="stylesheet" />
<script src="bootstrap-year-calendar.js"></script>
<section class="content">
    <div class="container-fluid">
        <div class="row" style="justify-content: center;">
            <div class="col-md-12">
                <div class="card">
                    <div class="card-header" style="background-color: #cc6028; color: #ffffff;">
                        <span class="card-title" style="font-size: 13px;">KALENDER - Search User Data in Calendar</span>
                    </div>
                    <div class="card-body col-md-12" style="background-color: #ffffff;">
                        <div class="row">
                            <div class="col-xs-3">
                                <div class="input-group">
                                    <div class="input-group-prepend">
                                        <span class="input-group-text">
                                            <label class="text small" style="color: #063b66; font-size: 10px; font-weight: bold;">EMAIL</label>
                                        </span>
                                        <asp:DropDownList ID="ddlUserEmail" runat="Server" CssClass="form-control"></asp:DropDownList>
                                    </div>
                                </div>
                            </div>
                            <div class="col-xs-3">
                                <div class="input-group">
                                    <div class="input-group-prepend">
                                        <span class="input-group-text">
                                            <label class="text small" style="color: #063b66; font-size: 10px; font-weight: bold;">YEAR</label>
                                        </span>
                                        <asp:DropDownList ID="ddlCalendarYear" runat="Server" CssClass="form-control"></asp:DropDownList>
                                    </div>
                                </div>
                            </div>
                            <div class="col-xs-3">
                                <div class="">
                                    <div class="input-group-prepend">
                                        <asp:LinkButton ID="searchUser" runat="server" OnClick="searchUser_Calendar" ForeColor="#ffffff" CssClass="btn btn-secondary">
                        <i class="fas fa-search"></i> Search
                                        </asp:LinkButton>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</section>

<div id="calendar"></div>

<script>

    $(document).ready(function () {
        BindCalendar();
        $('[id*=searchUser]').on('click', function () {
            var email = $('[id*=ddlUserEmail]').find('option:selected').text();
            var year = $('[id*=ddlCalendarYear]').find('option:selected').text();
            BindCalendar(email, year);
            return false;
        });

        function BindCalendar(em, yr) {
            // Return today's date and time
            var currentTime = new Date()
            // returns the month (from 0 to 11)
            var currentMonth = currentTime.getMonth() + 1
            // returns the day of the month (from 1 to 31)
            var currentDay = currentTime.getDate()
            // returns the year (four digits)
            var currentYear = currentTime.getFullYear()

            var circleDateTime = new Date(currentYear, (currentMonth - 1), currentDay).getTime();

            function ConvertJsonDateToDateTime(date) {
                var parsedDate = new Date(parseInt(date.substr(6)));
                var newDate = new Date(parsedDate);
                return newDate;
            }
            var obj = {};
            obj.email = em == undefined ? '' : em;
            obj.year = yr == undefined ? '' : yr;
            $.ajax({
                url: 'WebServiceCS.asmx/GetDetails',
                data: JSON.stringify(obj),
                type: 'post',
                contentType: 'application/json; charset=utf-8',
                dataType: 'json',
                success: function (r) {
                    $('#calendar').empty();
                    for (var i = 0; i < r.d.length; i++) {
                        r.d[i].startDate = ConvertJsonDateToDateTime(r.d[i].startdato);
                        r.d[i].endDate = ConvertJsonDateToDateTime(r.d[i].sluttdato);
                    }

                    //This is what make the calendar show and work
                    $('#calendar').calendar({
                        enableContextMenu: true,
                        enableRangeSelection: true,
                        displayWeekNumber: true,
                        // style: 'background',
                        //weekStart: 1,
                        // firstDay: 1,

                        contextMenuItems: [
                            {
                                text: 'Update',
                                click: editEvent
                            },
                            {
                                text: 'Delete',
                                click: deleteEvent
                            }
                        ],
                        selectRange: function (e) {

                        },
                        //This is to show it if it has events
                        mouseOnDay: function (e) {
                            if (e.events.length > 0) {
                                var content = '';

                                for (var i in e.events) {
                                    content += '<div class="event-tooltip-content" style="background-color:#f8c17e;">'
                                        + '<div class="userEmail">' + "Deltaker: " + e.events[i].useremail + '</div>'
                                        + '<div class="event-name" style="color:' + e.events[i].color + '">' + "Forum Name: " + e.events[i].forumname + '</div>'
                                        + '<div class="deets">' + e.events[i].forumformaal + '</div>'
                                        + '<div class="event-location">' + e.events[i].forumvenue + '</div>'
                                        + '<div>' + e.events[i].forumstatus + '</div>'
                                        + '</div>';

                                }
                                $(e.element).popover({
                                    trigger: 'manual',
                                    container: 'body',
                                    html: true,
                                    content: content
                                });

                                $(e.element).popover('show');
                            }
                        },
                        //This is to hide the popover
                        mouseOutDay: function (e) {
                            if (e.events.length > 0) {

                                $(e.element).popover('hide');
                            }
                        },

                        dayContextMenu: function (e) {
                            $(e.element).popover('hide');
                        },

                        customDayRenderer: function (element, date) {

                            if (date.getTime() == circleDateTime) {
                                $(element).css('background-color', 'red');
                                $(element).css('color', 'white');
                                $(element).css('border-radius', '15px');
                            }
                        },

                        customDataSourceRenderer: function (element, event) {
                            // This will override the background-color to the event's color
                            $(element).css('background-color', event[0].color);
                            $(element).css('border-radius', '15px');
                        },
                        dataSource: r.d
                    });
                }
            });
        }

        function editEvent(event) {
            $('#event-modal input[name="event-index"]').val(event ? event.forumdeltakerid : '');
            $('#event-modal input[name="event-name"]').val(event ? event.forumname : '');
            $('#event-modal input[name="event-location"]').val(event ? event.forumvenue : '');
            $('#event-modal input[name="event-start-date"]').datepicker('update', event ? event.startdato : '');
            $('#event-modal input[name="event-end-date"]').datepicker('update', event ? event.sluttdato : '');
            $('#event-modal').modal();
        }

        function deleteEvent(event) {
            var dataSource = $('#calendar').data('calendar').getDataSource();

            for (var i in dataSource) {
                if (dataSource[i].id == event.id) {
                    dataSource.splice(i, 1);
                    break;
                }
            }

            $('#calendar').data('calendar').setDataSource(dataSource);
        }
    });
</script>

WebMethod

[WebMethod]
public List<ForumCalendarYear> GetDetails(string email, string year)
{
    List<ForumCalendarYear> events = new List<ForumCalendarYear>();

    string conString = ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;

    string query = "Select * FROM Forum WHERE (UserEmail = @Email AND DATEPART(YEAR,ForumStartDato) = @Year) OR @Email IS NULL OR @Year IS NULL";

    SqlCommand cmd = new SqlCommand(query);

    using (SqlConnection con = new SqlConnection(conString))
    {
        cmd.Connection = con;
        con.Open();
        cmd.Parameters.AddWithValue("@Email", !string.IsNullOrEmpty(email) ? email : (object)DBNull.Value);
        cmd.Parameters.AddWithValue("@Year", !string.IsNullOrEmpty(year) ? year : (object)DBNull.Value);
        using (SqlDataReader sdr = cmd.ExecuteReader())
        {
            while (sdr.Read())
            {
                events.Add(new ForumCalendarYear
                {
                    forumdeltakerid = Convert.ToInt32(sdr["ForumDeltakerID"]),
                    useremail = sdr["UserEmail"].ToString(),
                    forumname = sdr["ForumName"].ToString(),
                    forumvenue = sdr["ForumVenue"].ToString(),
                    forumformaal = sdr["ForumFormaal"].ToString(),
                    startdato = Convert.ToDateTime(sdr["ForumStartDato"]),
                    sluttdato = Convert.ToDateTime(sdr["ForumSluttDato"]),
                    forumstatus = sdr["AdminForumStatus"].ToString()
                });
            }
        }
        con.Close();
    }
    return events;
}

public class ForumCalendarYear
{
    public int forumdeltakerid { get; set; }
    public string useremail { get; set; }
    public string forumname { get; set; }
    public string forumvenue { get; set; }
    public string forumformaal { get; set; }
    public DateTime startdato { get; set; }
    public DateTime sluttdato { get; set; }
    public string forumstatus { get; set; }
}