using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.IO;
using System.Web.UI.HtmlControls;
using AjaxControlToolkit;

public partial class Event_Description : System.Web.UI.Page
{
    PublicMethods clsPublicMethods = new PublicMethods();
    EMUPDataContext dbEmup = new EMUPDataContext();
    MailServicesCls clsMail = new MailServicesCls();
    Logic obj = new Logic();
    protected int GalleryId;
    protected string GalleryName;
    public string strEventName;
  
    protected string latti { get; set; }
    protected string longi { get; set; }
    private string strTitle;
    Slideshow clSlideShow = new Slideshow();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {



            try
            {
                if (Request.QueryString["Id"] != null)
                {
                    litAID.Text = Convert.ToString((Request.QueryString["Id"]));
                }
                else
                {
                    strTitle = Page.RouteData.Values["AdEventDetail"].ToString();
                    litAID.Text = strTitle.Substring(strTitle.LastIndexOf('-') + 1);
                }
            }
            catch
            {
                strTitle = Page.RouteData.Values["AdEventDetail"].ToString();
                litAID.Text = strTitle.Substring(strTitle.LastIndexOf('-') + 1);
            }




            //strTitle = Page.RouteData.Values["AdEventDetail"].ToString();
            //litAID.Text = strTitle.Substring(strTitle.LastIndexOf('-') + 1);

            string strIP = string.Empty;
            strIP = Request.ServerVariables["HTTP_X_FORWARDED_FOR"];
            if (string.IsNullOrEmpty(strIP))
                strIP = Request.ServerVariables["REMOTE_ADDR"];
            litiP.Text = strIP;
            if (string.IsNullOrEmpty(strIP))
                litiP.Text = Request.ServerVariables["REMOTE_ADDR"];

            litEventId.Text = Convert.ToString(litAID.Text);

          

            //clsPublicMethods.FillDropDownList(clsPublicMethods.GetAdImages(Convert.ToInt16(litEventId.Text)), ddlAdImages);
            Int16 id0 = Convert.ToInt16(litEventId.Text);
            //imgslides.ImageUrl = "LoadPhoto.aspx?ImagesId=" + Convert.ToString(ddlAdImages.SelectedValue);
            //slImages.SlideShowServiceMethod = "LoadPhoto.aspx?ImagesId=" + Convert.ToString(ddlAdImages.SelectedValue);
            if (id0 != 0)
            {

                //    BindGridView(id0);
                var objEvent_Creates = from objev in dbEmup.Event_Creates
                                       where objev.AdId == id0
                                       select objev;

                Event_Create objEvent_Create = objEvent_Creates.SingleOrDefault();

                // required fields
                if (objEvent_Create == null)
                    return;
                litEventCity.Text = Convert.ToString(objEvent_Create.UserAdsDet.City);
                if (objEvent_Create.EventBrochure == null)
                {
                    divBrochure.Visible = false;
                }
                else
                {
                    divBrochure.Visible = true;
                }
                if (objEvent_Create.UserAdsDet.AdType == "Premium")
                {
                    divAdType.Visible = true;
                }
                else
                {
                    divAdType.Visible = false;
                }

                lblEventName.Text = objEvent_Create.Event_Name.ToString();
                if (Convert.ToString(objEvent_Create.SeatingArrangement) != "")
                {
                    litSeat.Text = objEvent_Create.SeatingArrangement.ToString();
                }
                //  lblEventNameHeader.Text = objEvent_Create.Event_Name.ToString();
                lblEventDate.Text = objEvent_Create.EventDate.ToString();
                if (objEvent_Create.VenueName != null)
                {
                    //lblVenueName.Text = objEvent_Create.VenueName.ToString();
                }
                else
                {
                    //lblVenueName.Text = "NA";
                }
                lblVenue.Text = objEvent_Create.UserAdsDet.Location.ToString();
                if (Convert.ToString(objEvent_Create.UserAdsDet.Description) != "")
                {
                    txtDescription.Text = objEvent_Create.UserAdsDet.Description;
                }
                else
                {
                    divDesc.Visible = false;
                }
                litUserId.Text = objEvent_Create.UserAdsDet.UserEmailId.ToString();
                litUserName.Text = objEvent_Create.UserAdsDet.UserDet.Name.ToString();
                //lblenddate.Text = objEvent_Create.EventEndDate.ToString();
                if (objEvent_Create.StartTime.ToString() != "")
                {
                    lblstarttime.Text = objEvent_Create.StartTime.ToString();
                }
                else
                {
                    lblstarttime.Text = "NA";
                }
                //lblendtime.Text = objEvent_Create.EndTime.ToString();
                //  ddlTimeZone.Text = objEvent_Create.TimeZone.ToString();
                if (Convert.ToString(objEvent_Create.TimeZone) != "")
                {
                    ddlTimeZone.Text = Convert.ToString(objEvent_Create.TimeZone);
                }
                else
                {
                    ddlTimeZone.Text = "NA";
                }




                //if ((Convert.ToString(objEvent_Create.TicketBuyURL) != null) || (Convert.ToString(objEvent_Create.TicketBuyURL) != ""))

               




                //if ((Convert.ToString(objEvent_Create.TicketBuyURL) != "") || (objEvent_Create.TicketBuyURL != null))
                //{



                //    hlURL.Text = "Buy Tickets Here";
                //    hlURL.NavigateUrl = Convert.ToString(objEvent_Create.TicketBuyURL);

                //    //lbltktBuyURL.Text = Convert.ToString(objEvent_Create.TicketBuyURL);
                //}
                //else
                //{
                //    divtktUrl.Visible = false;
                //}
                lblAdmType.Text = Convert.ToString(objEvent_Create.AdmissionType);
                if (Convert.ToString(objEvent_Create.AdmissionType) == "Free Entry with Pre-Reservation")
                {
                    lblAdmFreewithPre.Text = "(Please RSVP to this Event)";
                }
                latti = Convert.ToString(objEvent_Create.UserAdsDet.Altitude);
                longi = Convert.ToString(objEvent_Create.UserAdsDet.Longitude);


                if (objEvent_Create.UserAdsDet.VideoUrl != "")
                {
                    litUrl.Text = Convert.ToString(objEvent_Create.UserAdsDet.VideoUrl);
                    litUrl.Text = litUrl.Text.Replace("https://www.youtube.com/watch?v=", "");

                    litUrl.Text = "https://www.youtube.com/embed/" + Convert.ToString(litUrl.Text);


                }
                else
                {

                    divVideo.Visible = false;

                }
                litImageUrl.Text = Convert.ToString(objEvent_Create.UserAdsDet.ImageUrl);

                lblCategory.Text = Convert.ToString(objEvent_Create.Event_Category_Det.CategoryName);
                litCatId.Text = Convert.ToString(objEvent_Create.CategoryId);
                // litSubCat.Text = Convert.ToString(objEvent_Create.UserAdsDet.SubCategoryDet.SubCategory);


                if (Convert.ToString(objEvent_Create.FBEventURL) != "")
                {
                    hlFacebook.Text = Convert.ToString(objEvent_Create.FBEventURL);
                    hlFacebook.NavigateUrl = Convert.ToString(objEvent_Create.FBEventURL);

                }
                else if (Convert.ToString(objEvent_Create.FBEventURL) != "NA")
                {
                    divfacebook.Visible = false;
                }
                imglogo.ImageUrl = "LoadPhoto.aspx?UserId=" + Convert.ToString(objEvent_Create.UserAdsDet.UserDet.UserSerNo);
                //Session["EventImageURL"]= 


                var objOrg = from objO in dbEmup.Event_Organizers
                             where objO.AdId == id0
                             select objO;

                Event_Organizer objOrganizer = objOrg.SingleOrDefault();

                // required fields
                //if (objOrganizer == null)
                //	return;
                if (objOrganizer != null)
                {
                    lblOrgabnizer.Text = Convert.ToString(objOrganizer.OrganizerName);
                    lblOrganization.Text = Convert.ToString(objOrganizer.OrganizationName);
                    lblOrgEmail.Text = Convert.ToString(objOrganizer.EmailId);
                    lblOrgWebsite.Text = Convert.ToString(objOrganizer.Website);
                    lblOrgWebsite.NavigateUrl = Convert.ToString(objOrganizer.Website);
                    lblOrgContactNo.Text = Convert.ToString(objOrganizer.Contact);
                }
                else
                {
                    divOrg.Visible = false;
                }
                // DownloadFile.ImageUrl = "Load-Image.aspx?Brochure=" + (Convert.ToInt16(Request.QueryString["Id"]));


                this.Title = "indian events, Desi events in " + litEventCity.Text + " " +  objEvent_Create.Event_Name  ;
                ifrmVideoUrl.Attributes.Add("src", litUrl.Text.Trim());
              
              
                //htmla.HRef = "LoadPhoto.aspx?Id=" + id0;
                imgEventLogo.ImageUrl = "LoadPhoto.aspx?Id=" + id0;
                GetAdImages();

                //GetGallery(Convert.ToInt16(Request.QueryString["Id"]));


                BindGridTicketEvents(id0);

                GetSchedules(id0);
                BindGuestEvents(id0);
                BindSponserEvents(id0);
                BindPartnerEvents(id0);
                BindBrochure();
                BindSeat();
                FBMetaTags();
                //Int32 startzip = 0;
                //Int32 endzip = 0;
                //string zipcode;
                string strLoc = "";
                try
                {

                    HttpCookie cookieLoc = Request.Cookies.Get("locInfo");
                    //if ((Request.QueryString["Sloc"]) != null)
                    //{
                    //    string strcity = Convert.ToString(Convert.ToString(Request.QueryString["Sloc"])).Split(',')[0];
                    //    Session["loc"] = strcity;
                    //    //Session["Sloc"] = (Convert.ToString(Request.QueryString["Sloc"]).Split(',')[0], Convert.ToString(Request.QueryString["Sloc"].Substring(Request.QueryString["Sloc"].LastIndexOf(',') + 1)));
                    //    string strstate = Convert.ToString(Convert.ToString(Request.QueryString["Sloc"])).Split(',')[1];
                    //    strLoc = strcity + "," + strstate;
                    //    Session["Sloc"] = strLoc;
                    //    clsPublicMethods.GetLatLong(Convert.ToString(Request.QueryString["Sloc"]).Split(',')[0], Convert.ToString(Request.QueryString["Sloc"].Substring(Request.QueryString["Sloc"].LastIndexOf(',') + 1)));
                    //    BindUserAds(Convert.ToDouble(clsPublicMethods.salti), Convert.ToDouble(clsPublicMethods.slong), Convert.ToInt16(litAID.Text));
                    //    BindRelatedAds(Convert.ToDouble(clsPublicMethods.salti), Convert.ToDouble(clsPublicMethods.slong), Convert.ToInt16(litAID.Text));
                    //    BindGridViewListings(Convert.ToDouble(clsPublicMethods.salti), Convert.ToDouble(clsPublicMethods.slong));
                    //}


                    //try
                    //{
                    //    BindGridViewListings(Convert.ToDouble(32.7766642), Convert.ToDouble(-96.7969879));
                    //}
                    //catch (Exception ex)
                    //{
                    //    BindGridViewListings();
                    //}

                    if (Request.Cookies["locInfo"] != null)
                    {
                        //Session["loc"] = Convert.ToString(Server.HtmlEncode(Request.Cookies["locInfo"]["loc"]));
                        //Session["Sloc"] = Convert.ToString(Server.HtmlEncode(Request.Cookies["locInfo"]["Sloc"]));
                        //BindUserAds(Convert.ToDouble(Server.HtmlEncode(Request.Cookies["locInfo"]["lat"])), Convert.ToDouble(Server.HtmlEncode(Request.Cookies["locInfo"]["long"])), Convert.ToInt16(litAID.Text));
                        //BindRelatedAds(Convert.ToDouble(Server.HtmlEncode(Request.Cookies["locInfo"]["lat"])), Convert.ToDouble(Server.HtmlEncode(Request.Cookies["locInfo"]["long"])), Convert.ToInt16(litAID.Text));
                        // BindGridViewListings(Convert.ToDouble(Server.HtmlEncode(Request.Cookies["locInfo"]["lat"])), Convert.ToDouble(Server.HtmlEncode(Request.Cookies["locInfo"]["long"])));
                        //  BindGrids(Convert.ToDouble(Server.HtmlEncode(Request.Cookies["locInfo"]["lat"])), Convert.ToDouble(Server.HtmlEncode(Request.Cookies["locInfo"]["long"])));


                        try
                        {
                            BindRelatedAds(Convert.ToDouble(Server.HtmlEncode(Request.Cookies["locInfo"]["lat"])), Convert.ToDouble(Server.HtmlEncode(Request.Cookies["locInfo"]["long"])), Convert.ToInt16(litAID.Text));
                            BindGridViewListings(Convert.ToDouble(Server.HtmlEncode(Request.Cookies["locInfo"]["lat"])), Convert.ToDouble(Server.HtmlEncode(Request.Cookies["locInfo"]["long"])));
                            BindUserAds(Convert.ToDouble(Server.HtmlEncode(Request.Cookies["locInfo"]["lat"])), Convert.ToDouble(Server.HtmlEncode(Request.Cookies["locInfo"]["long"])), Convert.ToInt16(litAID.Text));
                        }
                        catch (Exception ex)
                        {
                            BindGridViewListings();
                            BindRelatedAds();
                            BindUserAds();
                        }


                    }


                    else if (Session["Sloc"] != null)
                    {
                        //string strcity = Convert.ToString(Convert.ToString(Session["Sloc"])).Split(',')[0];
                        //Session["loc"] = strcity;
                        //string strstate = Convert.ToString(Convert.ToString(Session["Sloc"])).Split(',')[1];
                        ////clsPublic.GetLatLong(Convert.ToString(Session["Sloc"]));
                        //clsPublicMethods.GetLatLong(Convert.ToString(Session["Sloc"]).Split(',')[0], Convert.ToString(Session["Sloc"]).Split(',')[1]);
                        //BindUserAds(Convert.ToDouble(clsPublicMethods.salti), Convert.ToDouble(clsPublicMethods.slong), Convert.ToInt16(litAID.Text));
                        //BindRelatedAds(Convert.ToDouble(clsPublicMethods.salti), Convert.ToDouble(clsPublicMethods.slong), Convert.ToInt16(litAID.Text));
                        //BindGridViewListings(Convert.ToDouble(clsPublicMethods.salti), Convert.ToDouble(clsPublicMethods.slong));


                        try
                        {
                            BindGridViewListings(Convert.ToDouble(Session["lat"]), Convert.ToDouble(Session["long"]));
                            BindUserAds(Convert.ToDouble(Session["lat"]), Convert.ToDouble(Session["long"]), Convert.ToInt16(litAID.Text));
                            BindRelatedAds(Convert.ToDouble(Session["lat"]), Convert.ToDouble(Session["long"]), Convert.ToInt16(litAID.Text));
                        }
                        catch (Exception ex)
                        {
                            BindGridViewListings();
                            BindUserAds();
                            BindRelatedAds();

                        }

                    }
                    else if (cookieLoc != null)
                    {

                        try
                        {

                            BindGridViewListings(Convert.ToDouble(Server.HtmlEncode(Request.Cookies["locInfo"]["lat"])), Convert.ToDouble(Server.HtmlEncode(Request.Cookies["locInfo"]["long"])));
                            BindUserAds(Convert.ToDouble(Server.HtmlEncode(Request.Cookies["locInfo"]["lat"])), Convert.ToDouble(Server.HtmlEncode(Request.Cookies["locInfo"]["long"])), Convert.ToInt16(litAID.Text));
                            BindRelatedAds(Convert.ToDouble(Server.HtmlEncode(Request.Cookies["locInfo"]["lat"])), Convert.ToDouble(Server.HtmlEncode(Request.Cookies["locInfo"]["long"])), Convert.ToInt16(litAID.Text));
                            
                        }
                        catch (Exception ex)
                        {
                            BindGridViewListings();
                            BindUserAds();
                            BindRelatedAds();

                        }
                    }
                    else
                    {
                        //DataTable dt = clsPublicMethods.GetLocation(strIP);

                        //lblCity.Text = dt.Rows[0]["City"].ToString() + "," + dt.Rows[0]["RegionCode"].ToString() + "," + dt.Rows[0]["CountryName"].ToString();
                        //Session["Sloc"] = dt.Rows[0]["City"].ToString() + "," + dt.Rows[0]["RegionCode"].ToString();
                        //Session["loc"] = dt.Rows[0]["City"].ToString();

                        //clsPublicMethods.GetLatLong(Convert.ToString(dt.Rows[0]["City"].ToString()), Convert.ToString(dt.Rows[0]["RegionCode"].ToString()));
                        //BindUserAds(Convert.ToDouble(clsPublicMethods.salti), Convert.ToDouble(clsPublicMethods.slong), Convert.ToInt16(litAID.Text));
                        //BindRelatedAds(Convert.ToDouble(clsPublicMethods.salti), Convert.ToDouble(clsPublicMethods.slong), Convert.ToInt16(litAID.Text));
                        //BindGridViewListings(Convert.ToDouble(clsPublicMethods.salti), Convert.ToDouble(clsPublicMethods.slong));

                        //////


                        
                            BindUserAds();
                            BindRelatedAds();
                            BindGridViewListings();
                        

                    }
                }
                catch (Exception ex)
                {
                    BindUserAds();
                    BindRelatedAds();
                    BindGridViewListings();
                }


                //   Page.MetaDescription = "Jhalak.com events in " + Convert.ToString(Session["loc"]) + lblEventName.Text;

                GetRating(Convert.ToInt16(litEventId.Text));
                GetComments(Convert.ToInt16(litEventId.Text));


            //    try
            //    {
                   
            //    }
            //catch
            //    {

            //    }


                if (lblAdmType.Text == "Paid Entry")
                {
                    divRsvp.Visible = false;
                }
                else
                {
                    divRsvp.Visible = true;
                }



                //  DownloadFile()
                // getOrganizerDet();

                if (gvGuest.Items.Count == 0)
                {
                    divGuests.Visible = false;
                }

                if (gvPatners.Items.Count == 0)
                {
                    divPartners.Visible = false;
                }

                if (gvSponser.Items.Count == 0)
                {
                    divSpon.Visible = false;
                }

              
                if (grdEvent.Rows.Count == 0)
                {
                    divTickets.Visible = false;
                    divTerms.Visible = false;

                    
                }
                if (objEvent_Create.TicketBuyURL != null)
                {
                    litTicketUrl.Text= Convert.ToString(objEvent_Create.TicketBuyURL);

                    hlURL.Text = "<span style='background:#e21c04; border-radius:3px; padding:3px 8px;color:#fff;'>" + "Buy Tickets Here" + "</span>";
                    hlURL.NavigateUrl = Convert.ToString(objEvent_Create.TicketBuyURL);
                }
              
                //if (grdEvent.Rows.Count == 0)
                //{
                //    divTickets.Visible = false;
                //}

                if (grdSchedules.Rows.Count == 0)
                {
                    divSchedules.Visible = false;
                }

                if (gvBrochure.Rows.Count == 0)
                {
                    divBrochure.Visible = false;
                }
                if (Convert.ToString(litSeat.Text) == "")
                {
                    divSeat.Visible = false;
                }

            }

        }

    }

    private IQueryable GetGuestPhotos(Int16 intEventId)
    {
        var objList = from objGuest in dbEmup.Event_Guests
                      where objGuest.AdId == intEventId

                      select new
                      {
                          GuestId = objGuest.Event_Guest_Id,

                      };
        return objList;
    }



    private void GetSchedules(int intEventId)
    {
        grdSchedules.DataSource = clsPublicMethods.GetEventSchedules(intEventId);
        grdSchedules.DataBind();
    }


    #region Brochure
    private void BindBrochure()
    {
        gvBrochure.DataSource = GetBrochure(Convert.ToInt16(litAID.Text));
        gvBrochure.DataBind();
    }
    private IQueryable GetBrochure(Int16 IntadId)
    {
        var objA = (from objG in dbEmup.Event_Creates
                    where objG.AdId == IntadId
                    orderby objG.AdId descending
                    select new
                    {
                        Adid = objG.UserAdsDet.UserAdId,



                    }).Take(1);

        return objA;
    }
    #endregion

    #region Seating Arrangment
    private void BindSeat()
    {
        gvSeat.DataSource = GetSeat(Convert.ToInt16(litAID.Text));
        gvSeat.DataBind();
    }
    private IQueryable GetSeat(Int16 IntadId)
    {
        var objA = (from objG in dbEmup.Event_Creates
                    where objG.AdId == IntadId
                    orderby objG.AdId descending
                    select new
                    {
                        Adid = objG.AdId,



                    }).Take(1);

        return objA;
    }
    #endregion




    private void BindGridTicketEvents(Int16 intEventId)
    {
        grdEvent.DataSource = clsPublicMethods.GetTicketedEvents(intEventId);
        grdEvent.DataBind();
    }


    private void BindGuestEvents(Int16 intEventId)
    {
        gvGuest.DataSource = clsPublicMethods.GetGuestsEvents(intEventId);
        gvGuest.DataBind();
    }


    private void BindSponserEvents(Int16 intEventId)
    {
        gvSponser.DataSource = clsPublicMethods.GetSponserEvents(intEventId);
        gvSponser.DataBind();
    }


    private void BindPartnerEvents(Int16 intEventId)
    {
        gvPatners.DataSource = clsPublicMethods.GetPartnerEvents(intEventId);
        gvPatners.DataBind();
    }
    //protected void DownloadFile(object sender, EventArgs e)
    //{
    //    int AdId = int.Parse((sender as LinkButton).CommandArgument);
    //    byte[] bytes;
    //    string fileName;
    //    string constr = ConfigurationManager.ConnectionStrings["ClassifiedsConnectionString"].ConnectionString;
    //    using (SqlConnection con = new SqlConnection(constr))
    //    {
    //        using (SqlCommand cmd = new SqlCommand())
    //        {
    //            cmd.CommandText = "select AdId, EventBrochure from Event_Create where AdId=@AdId";
    //            cmd.Parameters.AddWithValue("@AdId", AdId);
    //            cmd.Connection = con;
    //            con.Open();
    //            using (SqlDataReader sdr = cmd.ExecuteReader())
    //            {
    //                sdr.Read();
    //                bytes = (byte[])sdr["EventBrochure"];
    //                fileName = sdr["AdId"].ToString();
    //            }
    //            con.Close();
    //        }
    //    }
    //    Response.ClearContent();
    //    Response.Clear();
    //    Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".pdf");
    //    Response.AddHeader("Content-Length", fileName.Length.ToString());
    //    Response.ContentType = "application/octet-stream";

    //}

    protected void DownloadFile(object sender, EventArgs e)
    {
        int AdId = int.Parse((sender as LinkButton).CommandArgument);
        byte[] bytes;
        string fileName;
        string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "select AdId, EventBrochure from Event_Create where AdId=@AdId";
                cmd.Parameters.AddWithValue("@AdId", AdId);
                cmd.Connection = con;
                con.Open();
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    sdr.Read();
                    bytes = (byte[])sdr["EventBrochure"];
                    fileName = sdr["AdId"].ToString();
                }
                con.Close();
            }
        }
        //Response.ClearContent();
        //Response.Clear();
        //Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName +".pdf");
        //Response.AddHeader("Content-Length", fileName.Length.ToString());
        //Response.ContentType = "application/octet-stream";
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "";
        Response.Cache.SetCacheability(HttpCacheability.NoCache);

        Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName + ".pdf");
        Response.BinaryWrite(bytes);
        Response.Flush();
        Response.End();
    }

    protected void DownloadSeat(object sender, EventArgs e)
    {
        int AdId = int.Parse((sender as LinkButton).CommandArgument);
        byte[] bytes;
        string fileName;
        string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "select AdId, SeatingArrangement from Event_Create where AdId=@AdId";
                cmd.Parameters.AddWithValue("@AdId", AdId);
                cmd.Connection = con;
                con.Open();
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    sdr.Read();
                    bytes = (byte[])sdr["SeatingArrangement"];
                    fileName = sdr["AdId"].ToString();
                }
                con.Close();
            }
        }
        //Response.ClearContent();
        //Response.Clear();
        //Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName +".pdf");
        //Response.AddHeader("Content-Length", fileName.Length.ToString());
        //Response.ContentType = "application/octet-stream";
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "";
        Response.Cache.SetCacheability(HttpCacheability.NoCache);

        Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName + ".jpg");
        Response.BinaryWrite(bytes);
        Response.Flush();
        Response.End();
    }

    protected void DownloadTandC(object sender, EventArgs e)
    {
        int AdId = int.Parse((sender as LinkButton).CommandArgument);
        byte[] bytes;
        string fileName;
        string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "select AdId, TermsConditions from Event_Ticket_Mgmt where AdId=@AdId";
                cmd.Parameters.AddWithValue("@AdId", AdId);
                cmd.Connection = con;
                con.Open();
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    sdr.Read();
                    bytes = (byte[])sdr["TermsConditions"];
                    fileName = sdr["TermsConditions"].ToString();
                }
                con.Close();
            }
        }
        Response.ClearContent();
        Response.Clear();
        Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".pdf");
        Response.AddHeader("Content-Length", fileName.Length.ToString());
        Response.ContentType = "application/octet-stream";

    }

    //private void BindBrochure()
    //{
    //	gvBrochure.DataSource = GetBrochure(Convert.ToInt16(Request.QueryString["Id"]));
    //	gvBrochure.DataBind();
    //}
    //private IQueryable GetBrochure(Int16 IntadId)
    //{
    //	var objA = (from objG in dbEmup.Event_Creates
    //				where objG.AdId == IntadId
    //				orderby objG.AdId descending
    //				select new
    //				{
    //					Adid = objG.UserAdsDet.UserAdId,



    //				}).Take(1);

    //	return objA;
    //}
    private void BindRelatedAds()
    {
        gvRelatedPremiumAds.DataSource = GetAds("Premium", Convert.ToInt16(litAID.Text), Convert.ToString(lblCategory.Text));
        gvRelatedPremiumAds.DataBind();


        gvRelatedAds.DataSource = GetAds("Free", Convert.ToInt16(litAID.Text), Convert.ToString(lblCategory.Text));
        gvRelatedAds.DataBind();

        if (gvRelatedPremiumAds.Items.Count == 0 && gvRelatedAds.Items.Count == 0)
        {
            divRelated.Visible = false;
        }
    }
    private void BindRelatedAds(string strLoc)
    {
        gvRelatedPremiumAds.DataSource = GetAds("Premium", Convert.ToInt16(litAID.Text), Convert.ToString(lblCategory.Text), strLoc);
        gvRelatedPremiumAds.DataBind();


        gvRelatedAds.DataSource = GetAds("Free", Convert.ToInt16(litAID.Text), Convert.ToString(lblCategory.Text), strLoc);
        gvRelatedAds.DataBind();
    }
    private void BindRelatedAds(int startzip, int endzip)
    {
        gvRelatedPremiumAds.DataSource = GetAds("Premium", Convert.ToInt16(litAID.Text), Convert.ToString(lblCategory.Text), startzip, endzip);
        gvRelatedPremiumAds.DataBind();


        gvRelatedAds.DataSource = GetAds("Free", Convert.ToInt16(litAID.Text), Convert.ToString(lblCategory.Text), startzip, endzip);
        gvRelatedAds.DataBind();
    }
    private void BindRelatedAds(double latitude, double longitude, int EventId)
    {
        gvRelatedPremiumAds.DataSource = clsPublicMethods.GetRelatedEventsByRadius(latitude, longitude, "Premium", Convert.ToInt16(litCatId.Text), EventId);
        gvRelatedPremiumAds.DataBind();


        gvRelatedAds.DataSource = clsPublicMethods.GetRelatedEventsByRadius(latitude, longitude, "Free", Convert.ToInt16(litCatId.Text), EventId);
        gvRelatedAds.DataBind();


        if (gvRelatedPremiumAds.Items.Count == 0 && gvRelatedAds.Items.Count == 0)
        {
            divRelated.Visible = false;
        }
    }
    //private void BindRelatedAds(string strloc)
    //{
    //	gvRelatedPremiumAds.DataSource = RelatedAds(27, "Premium", Convert.ToInt16(Request.QueryString["Id"]), strloc);
    //	gvRelatedPremiumAds.DataBind();

    //	gvRelatedAds.DataSource = RelatedAds(27, "Free", Convert.ToInt16(Request.QueryString["Id"]), strloc);
    //	gvRelatedAds.DataBind();
    //}
    private void BindUserAds()
    {
        rpPUserads.DataSource = GetEvents(Convert.ToString(litUserId.Text), Convert.ToInt16(litAID.Text), "Premium");
        rpPUserads.DataBind();

        gvMyPosts.DataSource = GetEvents(Convert.ToString(litUserId.Text), Convert.ToInt16(litAID.Text), "Free");
        gvMyPosts.DataBind();

        if (rpPUserads.Items.Count == 0 && gvMyPosts.Items.Count == 0)
        {
            divUser.Visible = false;
        }

    }
    private void BindUserAds(string strLoc)
    {
        rpPUserads.DataSource = GetEvents(Convert.ToString(litUserId.Text), Convert.ToInt16(litAID.Text), "Premium", strLoc);
        rpPUserads.DataBind();

        gvMyPosts.DataSource = GetEvents(Convert.ToString(litUserId.Text), Convert.ToInt16(litAID.Text), "Free", strLoc);
        gvMyPosts.DataBind();

    }
    private void BindUserAds(int startzip, int endzip)
    {
        rpPUserads.DataSource = GetEvents(Convert.ToString(litUserId.Text), Convert.ToInt16(litAID.Text), "Premium", startzip, endzip);
        rpPUserads.DataBind();

        gvMyPosts.DataSource = GetEvents(Convert.ToString(litUserId.Text), Convert.ToInt16(litAID.Text), "Free", startzip, endzip);
        gvMyPosts.DataBind();

    }
    private void BindUserAds(double latitude, double longitude, int EventId)
    {
        rpPUserads.DataSource = clsPublicMethods.GetUserEventsByRadius(latitude, longitude, "Premium", Convert.ToString(litUserId.Text), EventId);
        rpPUserads.DataBind();

        gvMyPosts.DataSource = clsPublicMethods.GetUserEventsByRadius(latitude, longitude, "Free", Convert.ToString(litUserId.Text), EventId);
        gvMyPosts.DataBind();

        if (rpPUserads.Items.Count == 0 && gvMyPosts.Items.Count == 0)
        {
            divUser.Visible = false;
        }

    }
    public IQueryable GetEvents(string strUserName, int AdId, string AdType)
    {
        var objEventsList = (from objEvent in dbEmup.Event_Creates
                             where objEvent.UserAdsDet.UserEmailId == strUserName
                             && objEvent.UserAdsDet.AdType == AdType && objEvent.AdId != AdId
                             && Convert.ToDateTime(objEvent.EventDate) >= DateTime.Now
                             orderby objEvent.Event_Id descending
                             select new
                             {

                                 EventId = objEvent.Event_Id,
                                 EventName = objEvent.Event_Name,
                                 AdTitle = objEvent.Event_Name,
                                 EventDesc = objEvent.Event_Desc,
                                 AdId = objEvent.AdId,
                                 EventDate = objEvent.EventDate,
                                 EventVenue = objEvent.Venue,
                                 StartTime = Convert.ToString(objEvent.StartTime),
                                 EndTime = Convert.ToString(objEvent.EndTime),
                                 Location = objEvent.UserAdsDet.City,
                                 City = objEvent.UserAdsDet.City,
                                 AdEventDetail = objEvent.UserAdsDet.City.Replace(",", "-") + "-" + objEvent.Event_Name.Replace(" ", "-").Replace(",", "").Replace(":", "").Replace("!", "").Replace("@", "").Replace("#", "").Replace("$", "").Replace("%", "").Replace("^", "").Replace("&", "").Replace("*", "").Replace("(", "").Replace(")", "").Replace("+", "").Replace("=", "").Replace("{", "").Replace("}", "").Replace(";", "").Replace("'", "").Replace("<", "").Replace(">", "").Replace("/", "").Replace("?", "").Replace("|", "").Trim() + "-" + objEvent.Event_Id,

                             }).Take(10);

        //   int resultcount = objTeamList.Count();
        return objEventsList;
    }
    public IQueryable GetEvents(string strUserName, int AdId, string AdType, string strLoc)
    {
        var objEventsList = (from objEvent in dbEmup.Event_Creates
                             where objEvent.UserAdsDet.UserEmailId == strUserName && objEvent.UserAdsDet.City == strLoc
                             && objEvent.UserAdsDet.AdType == AdType && objEvent.AdId != AdId
                             orderby objEvent.Event_Id descending
                             select new
                             {

                                 EventId = objEvent.Event_Id,
                                 EventName = objEvent.Event_Name,
                                 EventDesc = objEvent.Event_Desc,
                                 AdId = objEvent.AdId,
                                 EventDate = objEvent.EventDate,
                                 EventVenue = objEvent.Venue,
                                 StartTime = Convert.ToString(objEvent.StartTime),
                                 EndTime = Convert.ToString(objEvent.EndTime),
                                 Location = objEvent.UserAdsDet.City,
                                 AdEventDetail = objEvent.UserAdsDet.City.Replace(",", "-") + "-" + objEvent.Event_Name.Replace(" ", "-").Replace(",", "").Replace(":", "").Replace("!", "").Replace("@", "").Replace("#", "").Replace("$", "").Replace("%", "").Replace("^", "").Replace("&", "").Replace("*", "").Replace("(", "").Replace(")", "").Replace("+", "").Replace("=", "").Replace("{", "").Replace("}", "").Replace(";", "").Replace("'", "").Replace("<", "").Replace(">", "").Replace("/", "").Replace("?", "").Replace("|", "").Trim() + "-" + objEvent.Event_Id,

                             }).Take(10);

        //   int resultcount = objTeamList.Count();
        return objEventsList;
    }

    public IQueryable GetEvents(string strUserName, int AdId, string AdType, int startzip, int endzip)
    {
        var objEventsList = (from objEvent in dbEmup.Event_Creates
                             where objEvent.UserAdsDet.UserEmailId == strUserName && (Convert.ToInt32(objEvent.UserAdsDet.ZipCode) >= startzip && Convert.ToInt32(objEvent.UserAdsDet.ZipCode) <= endzip)
                             && objEvent.UserAdsDet.AdType == AdType && objEvent.AdId != AdId
                             orderby objEvent.Event_Id descending
                             select new
                             {

                                 EventId = objEvent.Event_Id,
                                 EventName = objEvent.Event_Name,
                                 EventDesc = objEvent.Event_Desc,
                                 AdId = objEvent.AdId,
                                 EventDate = objEvent.EventDate,
                                 EventVenue = objEvent.Venue,
                                 StartTime = Convert.ToString(objEvent.StartTime),
                                 EndTime = Convert.ToString(objEvent.EndTime),
                                 Location = objEvent.UserAdsDet.City,
                                 AdEventDetail = objEvent.UserAdsDet.City.Replace(",", "-") + "-" + objEvent.Event_Name.Replace(" ", "-").Replace(",", "").Replace(":", "").Replace("!", "").Replace("@", "").Replace("#", "").Replace("$", "").Replace("%", "").Replace("^", "").Replace("&", "").Replace("*", "").Replace("(", "").Replace(")", "").Replace("+", "").Replace("=", "").Replace("{", "").Replace("}", "").Replace(";", "").Replace("'", "").Replace("<", "").Replace(">", "").Replace("/", "").Replace("?", "").Replace("|", "").Trim() + "-" + objEvent.Event_Id,

                             }).Take(10);

        //   int resultcount = objTeamList.Count();
        return objEventsList;
    }

    public IQueryable GetAds(string adtype, int intAdid, string category, string strLoc)

    {
        var objA = (from objG in dbEmup.Event_Creates
                    where objG.UserAdsDet.Status == null && objG.UserAdsDet.AdType == adtype && objG.AdId != intAdid
                    && objG.Event_Category_Det.CategoryName == category && objG.UserAdsDet.City == strLoc
                    orderby objG.AdId descending
                    select new
                    {
                        AdId = objG.AdId,
                        AdTitle = objG.Event_Name,
                        Category = objG.UserAdsDet.CategoryDet.Categoryname,
                        SubCategory = objG.UserAdsDet.SubCategoryDet.SubCategory,
                        Location = objG.UserAdsDet.City,
                        Name = objG.UserAdsDet.UserDet.Name,
                        AdEventDetail = objG.UserAdsDet.City.Replace(",", "-") + "-" + objG.Event_Name.Replace(" ", "-").Replace(",", "").Replace(":", "").Replace("!", "").Replace("@", "").Replace("#", "").Replace("$", "").Replace("%", "").Replace("^", "").Replace("&", "").Replace("*", "").Replace("(", "").Replace(")", "").Replace("+", "").Replace("=", "").Replace("{", "").Replace("}", "").Replace(";", "").Replace("'", "").Replace("<", "").Replace(">", "").Replace("/", "").Replace("?", "").Replace("|", "").Trim() + "-" + objG.AdId,
                    }).Take(10);

        return objA;
    }

    public IQueryable GetAds(string adtype, int intAdid, string category)
    {
        var objA = (from objG in dbEmup.Event_Creates
                    where objG.UserAdsDet.Status == null && objG.UserAdsDet.AdType == adtype && objG.AdId != intAdid
                    && objG.Event_Category_Det.CategoryName == category && Convert.ToDateTime(objG.EventDate) >= DateTime.Now
                    orderby objG.AdId descending
                    select new
                    {
                        AdId = objG.AdId,
                        AdTitle = objG.Event_Name,
                        Category = objG.UserAdsDet.CategoryDet.Categoryname,
                        SubCategory = objG.UserAdsDet.SubCategoryDet.SubCategory,
                        Location = objG.UserAdsDet.City,
                        City = objG.UserAdsDet.City,
                        Name = objG.UserAdsDet.UserDet.Name,
                        AdEventDetail = objG.UserAdsDet.City.Replace(",", "-") + "-" + objG.Event_Name.Replace(" ", "-").Replace(",", "").Replace(":", "").Replace("!", "").Replace("@", "").Replace("#", "").Replace("$", "").Replace("%", "").Replace("^", "").Replace("&", "").Replace("*", "").Replace("(", "").Replace(")", "").Replace("+", "").Replace("=", "").Replace("{", "").Replace("}", "").Replace(";", "").Replace("'", "").Replace("<", "").Replace(">", "").Replace("/", "").Replace("?", "").Replace("|", "").Trim() + "-" + objG.AdId,
                    }).Take(10);

        return objA;
    }

    public IQueryable GetAds(string adtype, int intAdid, string category, int startzip, int endzip)
    {
        var objA = (from objG in dbEmup.Event_Creates
                    where objG.UserAdsDet.Status == null && objG.UserAdsDet.AdType == adtype && objG.AdId != intAdid
                    && objG.Event_Category_Det.CategoryName == category && (Convert.ToInt32(objG.UserAdsDet.ZipCode) >= startzip && Convert.ToInt32(objG.UserAdsDet.ZipCode) <= endzip)
                    orderby objG.AdId descending
                    select new
                    {
                        AdId = objG.AdId,
                        AdTitle = objG.Event_Name,
                        Category = objG.UserAdsDet.CategoryDet.Categoryname,
                        SubCategory = objG.UserAdsDet.SubCategoryDet.SubCategory,
                        Location = objG.UserAdsDet.City,
                        Name = objG.UserAdsDet.UserDet.Name,
                        AdEventDetail = objG.UserAdsDet.City.Replace(",", "-") + "-" + objG.Event_Name.Replace(" ", "-").Replace(",", "").Replace(":", "").Replace("!", "").Replace("@", "").Replace("#", "").Replace("$", "").Replace("%", "").Replace("^", "").Replace("&", "").Replace("*", "").Replace("(", "").Replace(")", "").Replace("+", "").Replace("=", "").Replace("{", "").Replace("}", "").Replace(";", "").Replace("'", "").Replace("<", "").Replace(">", "").Replace("/", "").Replace("?", "").Replace("|", "").Trim() + "-" + objG.Event_Id,
                    }).Take(10);

        return objA;
    }

    public IQueryable RelatedAds(int intCatId, string adtype, int intAdid, string strloc)

    {
        var objA = (from objG in dbEmup.UserAdsDets
                    where objG.CategoryId == intCatId && objG.Status == null && objG.AdType == adtype && objG.UserAdId != intAdid && objG.City == strloc
                    orderby objG.UserAdId descending
                    select new
                    {
                        AdId = objG.UserAdId,
                        AdTitle = objG.AdTitle,
                        Category = objG.CategoryDet.Categoryname,
                        SubCategory = objG.SubCategoryDet.SubCategory,
                        Location = objG.City,
                        Name = objG.UserDet.Name,
                        AdEventDetail = objG.City.Replace(",", "-") + "-" + objG.AdTitle.Replace(" ", "-").Replace(",", "").Replace(":", "").Replace("!", "").Replace("@", "").Replace("#", "").Replace("$", "").Replace("%", "").Replace("^", "").Replace("&", "").Replace("*", "").Replace("(", "").Replace(")", "").Replace("+", "").Replace("=", "").Replace("{", "").Replace("}", "").Replace(";", "").Replace("'", "").Replace("<", "").Replace(">", "").Replace("/", "").Replace("?", "").Replace("|", "").Trim() + "-" + objG.UserAdId,
                    }).Take(10);

        return objA;
    }

    private void BindGridViewListings()
    {
        gvServceListing.DataSource =GetProvidersListings(27);
        gvServceListing.DataBind();

        //gvPremiumProviders.DataSource = clsPublicMethods.GetProvidersListings(27, "Premium");
        //gvPremiumProviders.DataBind();


        //if (gvPremiumProviders.Items.Count > 0)
        //{
        //    gvServceListing.Visible = false;
        //}
        //else
        //{
        //    gvServceListing.Visible = true;
        //}
    }
    private void BindGridViewListings(string strLoc)
    {
        gvServceListing.DataSource = GetProvidersListings(27, strLoc, "Free");
        gvServceListing.DataBind();

        //gvPremiumProviders.DataSource = GetProvidersListings(27, strLoc, "Premium");
        //gvPremiumProviders.DataBind();


        //if (gvPremiumProviders.Items.Count > 0)
        //{
        //    gvServceListing.Visible = false;
        //}
        //else
        //{
        //    gvServceListing.Visible = true;
        //}
    }

    private void BindGridViewListings(int startzip, int endzip)
    {
        gvServceListing.DataSource = GetProvidersListings(27, startzip, endzip, "Free");
        gvServceListing.DataBind();

        //gvPremiumProviders.DataSource = GetProvidersListings(27, startzip, endzip, "Premium");
        //gvPremiumProviders.DataBind();


        //if (gvPremiumProviders.Items.Count > 0)
        //{
        //    gvServceListing.Visible = false;
        //}
        //else
        //{
        //    gvServceListing.Visible = true;
        //}
    }

    private void BindGridViewListings(double latitude, double longitude)
    {

        //gvServiceProviderPremium.DataSource = clsPublic.GetProvidersListings(Convert.ToInt16(litCatId.Text), strLoc, "Premium");
        //gvServiceProviderPremium.DataBind();


        gvServceListing.DataSource = GetBUsersByRadius(latitude, longitude, 27, 21);
        gvServceListing.DataBind();

        //gvPremiumProviders.DataSource = clsPublicMethods.GetProvidersListings(27, startzip, endzip, "Premium");
        //gvPremiumProviders.DataBind();

    }

    //private IQueryable GetProvidersListings(int intCatId, string Type)

    //{
    //	var objA = (from objG in dbEmup.UserDets
    //				where objG.Role == "Business" && objG.CategoryId == intCatId
    //				&& objG.UserType == Type
    //				orderby objG.CreatedDate descending
    //				select new
    //				{
    //					UserId = objG.UserSerNo,
    //					Name = objG.Name,
    //					Category = objG.CategoryDet.Categoryname,
    //					ContactNo = objG.BusinessContact,
    //					Location = objG.OperationLocatoin,
    //					BusinessName = objG.BusinessName,
    //					Website = Convert.ToString(objG.Website),
    //					AboutUs = Convert.ToString(objG.AboutUs),

    //				}).Take(5);

    //	return objA;
    //}
    protected void btnContact_Click(object sender, EventArgs e)
    {
        if (Page.IsValid)
        {


         if(   ValidateRSVP(Convert.ToString(txtEmail.Text), Convert.ToInt16(litAID.Text))==true)
            {
                lblMsg.Text = "You already responded to this event";
                txtName.Text = "";
                txtMsg.Text = "";
                txtContactNo.Text = "";
                txtEmail.Text = "";
                lblMsg.ForeColor = System.Drawing.Color.Red;
                return;
            }
            AdResponseDet objAdResponseDet = new AdResponseDet();
            //AdResponseDet objAdResponseDet;

            //objAdResponseDet = new AdResponseDet();


            objAdResponseDet.RespondentName = Convert.ToString(txtName.Text);

            objAdResponseDet.UserEmailId = Convert.ToString(litUserId.Text);

            objAdResponseDet.AdId = Convert.ToInt16(litAID.Text);

            objAdResponseDet.RespondentMailId = Convert.ToString(txtEmail.Text);
            objAdResponseDet.AdResponseMsg = Convert.ToString(txtMsg.Text);
            //           objAdResponseDet.CategoryId = Convert.ToInt16(litCatId.Text);
            objAdResponseDet.AdResponseDate = DateTime.Now;
            objAdResponseDet.Location = "-";
            //objAdResponseDet.AdResponseMsg = Convert.ToString(txtMsg.Text);
            objAdResponseDet.PhoneNo = Convert.ToString(txtContactNo.Text);

            try
            {
                dbEmup.AdResponseDets.InsertOnSubmit(objAdResponseDet);
                dbEmup.SubmitChanges();

                clsMail.AdResponseMail(Convert.ToString(litUserId.Text), Convert.ToString(litUserName.Text), Convert.ToString(txtName.Text));
                clsPublicMethods.ClearControls(Page.Controls);
                lblMsg.Text = "Message has been sent ";

            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                //Response.Redirect("Default.aspx");
            }


        }




    }
    private bool ValidateRSVP(string emailId,Int16 adid)

    {
        var objPwd = from objP in dbEmup.AdResponseDets
                     where objP.RespondentMailId == emailId && objP.AdId == adid 
                     select objP;
        if (objPwd.Count() > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    #region  photo gallery
    private void GetAdImages()
    {

        //old code starts here
        //clsPublicMethods.FillDropDownList(clsPublicMethods.GetAdImages(Convert.ToInt16(Convert.ToInt16(Request.QueryString["Id"]))), ddlAdImages);


        //for (int i = 0; i < ddlAdImages.Items.Count; i++)
        //{
        //    ddlAdImages.SelectedIndex = i;

        //    Image img = new Image(); // instantiating the control
        //  // img.Height = 100px;
        //  img.Width = 350;
        //    img.Attributes.Add("style", "z-index:9999999999 !important");

        //    img.ImageUrl = "LoadPhoto.aspx?AdImageId=" + Convert.ToInt16(ddlAdImages.SelectedValue);
        //    divAdImages.Controls.Add(img);

        //}
        // old code ends here

        SqlConnection CN = new SqlConnection(@"Data Source=jhalakservices.cpm5bnayea5q.us-west-2.rds.amazonaws.com;Initial Catalog=Classifieds;Persist Security Info=True;User ID=Shailesh;Password=jhalak0987;");

        CN.Open();

        SqlCommand command = new SqlCommand("ListImages", CN);
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.Parameters.Add("Id", System.Data.SqlDbType.Int).Value = Convert.ToInt16(litAID.Text);

        //Initialize SQL adapter.
        SqlDataAdapter ADAP = new SqlDataAdapter(command);

        //Initialize Dataset.
        DataSet DS = new DataSet();

        //Fill dataset with ImagesStore table.
        ADAP.Fill(DS, "ImagesStore");
        CN.Close();
        //Fill Grid with dataset.
        rptImage.DataSource = DS.Tables["ImagesStore"];
        rptImage.DataBind();

    }
    private IQueryable GetAdImages(int intAdId)
    {

        var objL = from objI in dbEmup.UserAdsImagesDets
                   where objI.UserAdId == intAdId
                   orderby objI.UserAdImageId ascending
                   select new
                   {
                       ImageId = objI.UserAdImageId,

                       Text = objI.Image,




                   };

        return objL;

    }


    protected void rptImage_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {
        if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
        {
            Image img = (Image)e.Item.FindControl("img");
            Label lbl = (Label)e.Item.FindControl("lbl");
            HtmlAnchor htmla = (HtmlAnchor)e.Item.FindControl("htmla");
            img.ImageUrl = "EventsImagesHandler.ashx?imageid=" + lbl.Text.ToString();
            htmla.HRef = "EventsImagesHandler.ashx?imageid=" + lbl.Text.ToString();
            //img.ImageUrl = "LoadPhoto.aspx?AdImageId=" + lbl.Text.ToString();
            //htmla.HRef = "LoadPhoto.aspx?AdImageId=" + lbl.Text.ToString();
        }
    }


    #endregion

    protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
    {
        if (litTicketUrl.Text != "")
        {
          
            Response.Redirect(litTicketUrl.Text);
        }
        else
        {

            if (Context.User.Identity.IsAuthenticated == false)
            {
                Response.Redirect("https://jhalak.com/Sign-In.aspx?EventId=" + Convert.ToString(litEventId.Text));

            }
            Response.Redirect("Book-Tickets.aspx?Id=" + Convert.ToString(litEventId.Text));
        }
    }

    protected void gvAdImages_SelectedIndexChanged(object sender, EventArgs e)
    {

    }

    protected void gvAdImages_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        //gvAdImages.PageIndex = e.NewPageIndex;
        //BindImages();
    }

    protected void gvAdImages_PageIndexChanging1(object sender, GridViewPageEventArgs e)
    {
        // gvThumbnails.PageIndex = e.NewPageIndex;
        // BindThumbnails();

        //gvAdImages.PageIndex = e.NewPageIndex;
        //BindImages();
    }

    //private static DataTable tblData = new DataTable();

    [System.Web.Services.WebMethodAttribute()]
    [System.Web.Script.Services.ScriptMethodAttribute()]

    //public AjaxControlToolkit.Slide[] GetSlides()
    //{

    //    AjaxControlToolkit.Slide[] slides = new AjaxControlToolkit.Slide[Convert.ToInt16(ddlAdImages.Items.Count)];

    //    for (int i = 0; i < ddlAdImages.Items.Count; i++)
    //    {
    //        ddlAdImages.SelectedIndex = i;
    //        int imgID = Convert.ToInt16(ddlAdImages.SelectedValue);
    //        slides[i] = new AjaxControlToolkit.Slide("LoadPhoto.aspx?ImagesId=" + imgID, "test", "test"); //sending the imgID to the handler to show which image will be viewed in slide
    //    }

    //    return slides;
    //}



    private void GetGallery(int id)
    {

        try
        {
            GalleryId = id;
            DataSet dsLoadGallery = obj.fnLoadGallery(GalleryId);
            if (dsLoadGallery.Tables.Count > 0)
            {
                if (dsLoadGallery.Tables[0].Rows.Count > 0)
                {
                    GalleryName = dsLoadGallery.Tables["LoadGallery"].Rows[0]["UserAdId"].ToString();

                    LoaddlGallery();
                }

            }
        }
        catch
        {

        }

    }

    protected void LoaddlGallery()
    {
        string GalleryPath = System.Configuration.ConfigurationManager.AppSettings["GalleryPath"] + GalleryId;
        string GalleryImagePath;

        string ThumbnailPath = System.Configuration.ConfigurationManager.AppSettings["ThumbnailPath"] + GalleryId;
        string ThumbnailImagePath;

        DirectoryInfo dir = new DirectoryInfo(MapPath(GalleryPath));
        FileInfo[] file = dir.GetFiles();
        DataTable dtLoadGallery = new DataTable();
        dtLoadGallery.Columns.Add("GalleryImagePath");
        dtLoadGallery.Columns.Add("ThumbnailImagePath");

        foreach (FileInfo image in file)
        {
            if (image.Extension.ToLower() == ".jpg" || image.Extension.ToLower() == ".JPG" || image.Extension.ToLower() == ".jpeg" || image.Extension.ToLower() == ".JPEG" || image.Extension.ToLower() == ".gif" || image.Extension.ToLower() == ".png" || image.Extension.ToLower() == ".bmp")
            {
                GalleryImagePath = GalleryPath + "\\" + image.Name;
                ThumbnailImagePath = ThumbnailPath + "\\" + image.Name;
                dtLoadGallery.Rows.Add(GalleryImagePath, ThumbnailImagePath);
            }
        }
        //dlGallery.DataSource = dtLoadGallery;
        //dlGallery.DataBind();
    }


    private void FBMetaTags()
    {
        HtmlMeta sitenameTag = new HtmlMeta();
        sitenameTag.Attributes.Add("property", "og:sitename");
        sitenameTag.Content = "https://events.jhalak.com";
        Page.Header.Controls.Add(sitenameTag);

        HtmlMeta Titletag = new HtmlMeta();
        Titletag.Attributes.Add("property", "og:title");
        Titletag.Content = Convert.ToString(lblEventName.Text);
        Page.Header.Controls.Add(Titletag);




        HtmlMeta urlTag = new HtmlMeta();
        urlTag.Attributes.Add("property", "og:url");
        urlTag.Content = "https://events.jhalak.com/Event-Description.aspx?Id=" + Convert.ToString(litAID.Text);
        Page.Header.Controls.Add(urlTag);




        HtmlMeta descriptionTag = new HtmlMeta();
        urlTag.Attributes.Add("property", "og:description");
        urlTag.Content = Convert.ToString(lblVenue.Text);
        Page.Header.Controls.Add(urlTag);


        HtmlMeta imageTag = new HtmlMeta();
        imageTag.Attributes.Add("property", "og:image:url");

       imageTag.Content = "https://jhalak.com/Compressed/" + Convert.ToString(litAID.Text) + ".jpeg" ;
        //imageTag.Content = "https://events.jhalak.com/images/" + Convert.ToString(Request.QueryString["id"]) + "/" + Convert.ToString(litImageUrl.Text);
        Page.Header.Controls.Add(imageTag);


        HtmlMeta imagetypeTag = new HtmlMeta();
        imagetypeTag.Attributes.Add("property", "og:image:type");
        imagetypeTag.Content = " image / jpeg";
        Page.Header.Controls.Add(imagetypeTag);


        HtmlMeta imagewidthTag = new HtmlMeta();
        imagewidthTag.Attributes.Add("property", "og:image:width");
        imagewidthTag.Content = "300";
        Page.Header.Controls.Add(imagewidthTag);


        HtmlMeta imageheightTag = new HtmlMeta();
        imageheightTag.Attributes.Add("property", "og:image:width");
        imageheightTag.Content = "300";
        Page.Header.Controls.Add(imageheightTag);


        HtmlMeta websiteTag = new HtmlMeta();
        websiteTag.Attributes.Add("property", "og:type");
        websiteTag.Content = "website";
        Page.Header.Controls.Add(websiteTag);




    }


    public DataSet GetBUsersByRadius(double latitude, double longitude, int catId, int partycatid)
    {
        DataSet ds = new DataSet();
        string sqlQuery = "SELECT Top 16 UserSerNo,usertype, BusinessName, ContactNo,Location, Website, latitude, longitude, Verified,JhalakBusinessD FROM( SELECT z.UserSerNo ,z.usertype, z.BusinessName,z.ContactNo,z.Location,z.Latitude, z.Longitude, z.Website,z.Verified,";
        sqlQuery += "concat(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.BusinessName,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.UserSerNo) AS JhalakBusinessD , p.distance_unit";
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Latitude)) * COS(RADIANS(p.longpoint - z.Longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Latitude)))) AS distance FROM Userdet AS z ";
        sqlQuery += " JOIN( SELECT " + latitude + " AS latpoint," + longitude + " AS longpoint, 200 AS radius, 111.045 AS distance_unit ) AS p ON 1 = 1 ";
        sqlQuery += " WHERE  (z.CategoryId=" + catId + " OR z.CategoryId=" + partycatid + ") and z.Role='Business'  and z.Latitude BETWEEN p.latpoint - (p.radius / p.distance_unit) AND p.latpoint + (p.radius / p.distance_unit) ";
        sqlQuery += " AND z.Longitude BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint)))) AND p.longpoint + ";
        sqlQuery += " (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))) AS d WHERE distance <= 200 order by usertype desc, UserSerNo desc ";

        string con = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlConnection connection = new SqlConnection(con);
        //SqlDataAdapter dataadapter = new SqlDataAdapter(sqlQuery, connection);
        connection.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, connection);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(ds);
        connection.Close();
        return ds;
    }
    private IQueryable GetProvidersListings(int intCatId)

    {
        var objA = (from objG in dbEmup.UserDets
                    where objG.Role == "Business" && objG.CategoryId == intCatId
                    orderby objG.UserType descending, objG.UserSerNo descending,objG.CreatedDate descending
                    select new
                    {
                        UserId = objG.UserSerNo,
                        UserSerNo = Convert.ToString(objG.UserSerNo),
                        Name = objG.Name,
                        Category = objG.CategoryDet.Categoryname,
                        ContactNo = objG.BusinessContact,
                        Location = objG.OperationLocatoin,
                        usertype = Convert.ToString(Convert.ToString(objG.UserType)),
                        AdType = Convert.ToString(Convert.ToString(objG.UserType)),
                        BusinessName = Convert.ToString(objG.BusinessName),
                        Website = Convert.ToString(objG.Website),
                        AboutUs = Convert.ToString(objG.AboutUs),
                        Verified = Convert.ToString(objG.Verified),
                        JhalakBusinessD = objG.BusinessName.Replace(" ", "-").Replace(",", "").Replace(":", "").Replace("!", "").Replace("@", "").Replace("#", "").Replace("$", "").Replace("%", "").Replace("^", "").Replace("&", "").Replace("*", "").Replace("(", "").Replace(")", "").Replace("+", "").Replace("=", "").Replace("{", "").Replace("}", "").Replace(";", "").Replace("'", "").Replace("<", "").Replace(">", "").Replace("/", "").Replace("?", "").Replace("|", "").Trim() + "-" + objG.UserSerNo,


                    }).Take(16);

        return objA;
    }



    public IQueryable GetProvidersListings(int intCatId, string strLoc, string strtype)

    {
        var objA = (from objG in dbEmup.UserDets
                    where objG.Role == "Business" && objG.CategoryId == intCatId && objG.OperationLocatoin == strLoc && objG.UserType == strtype
                    orderby objG.UserSerNo descending
                    select new
                    {
                        UserId = objG.UserSerNo,
                        UserSerNo = objG.UserSerNo,
                        Name = objG.Name,
                        Category = objG.CategoryDet.Categoryname,
                        ContactNo = objG.BusinessContact,
                        Location = objG.OperationLocatoin,
                        BusinessName = objG.BusinessName,
                        Website = Convert.ToString(objG.Website),
                        AboutUs = Convert.ToString(objG.AboutUs),
                        Verified = Convert.ToString(objG.Verified),
                        JhalakBusinessD = objG.BusinessName.Replace(" ", "-").Replace(",", "").Replace(":", "").Replace("!", "").Replace("@", "").Replace("#", "").Replace("$", "").Replace("%", "").Replace("^", "").Replace("&", "").Replace("*", "").Replace("(", "").Replace(")", "").Replace("+", "").Replace("=", "").Replace("{", "").Replace("}", "").Replace(";", "").Replace("'", "").Replace("<", "").Replace(">", "").Replace("/", "").Replace("?", "").Replace("|", "").Trim() + "-" + objG.UserSerNo,


                    }).Take(16);

        return objA;
    }

    public IQueryable GetProvidersListings(int intCatId, int startZip, int endZip, string strtype)
    {
        var objA = (from objG in dbEmup.UserDets
                    where objG.Role == "Business" && objG.CategoryId == intCatId && objG.UserType == strtype
                    && (Convert.ToInt32(objG.ZipCode) >= startZip && Convert.ToInt32(objG.ZipCode) <= endZip)
                    orderby objG.UserSerNo descending
                    select new
                    {
                        UserId = objG.UserSerNo,
                        UserSerNo = objG.UserSerNo,
                        Name = objG.Name,
                        Category = objG.CategoryDet.Categoryname,
                        ContactNo = objG.BusinessContact,
                        Location = objG.OperationLocatoin,
                        BusinessName = objG.BusinessName,
                        Website = Convert.ToString(objG.Website),
                        AboutUs = Convert.ToString(objG.AboutUs),
                        Verified = Convert.ToString(objG.Verified),
                        JhalakBusinessD = objG.BusinessName.Replace(" ", "-").Replace(",", "").Replace(":", "").Replace("!", "").Replace("@", "").Replace("#", "").Replace("$", "").Replace("%", "").Replace("^", "").Replace("&", "").Replace("*", "").Replace("(", "").Replace(")", "").Replace("+", "").Replace("=", "").Replace("{", "").Replace("}", "").Replace(";", "").Replace("'", "").Replace("<", "").Replace(">", "").Replace("/", "").Replace("?", "").Replace("|", "").Trim() + "-" + objG.UserSerNo,


                    }).Take(16);

        return objA;
    }

    public IQueryable GetProvidersListings(int intCatId, string strtype)

    {
        var objA = (from objG in dbEmup.UserDets
                    where objG.Role == "Business" && objG.CategoryId == intCatId && objG.UserType == strtype
                    orderby objG.UserSerNo descending
                    select new
                    {
                        UserId = objG.UserSerNo,
                        UserSerNo = objG.UserSerNo,
                        Name = objG.Name,
                        Category = objG.CategoryDet.Categoryname,
                        ContactNo = objG.BusinessContact,
                        Location = objG.OperationLocatoin,
                        BusinessName = objG.BusinessName,
                        Website = Convert.ToString(objG.Website),
                        AboutUs = Convert.ToString(objG.AboutUs),
                        Verified = Convert.ToString(objG.Verified),
                        JhalakBusinessD = objG.BusinessName.Replace(" ", "-").Replace(",", "").Replace(":", "").Replace("!", "").Replace("@", "").Replace("#", "").Replace("$", "").Replace("%", "").Replace("^", "").Replace("&", "").Replace("*", "").Replace("(", "").Replace(")", "").Replace("+", "").Replace("=", "").Replace("{", "").Replace("}", "").Replace(";", "").Replace("'", "").Replace("<", "").Replace(">", "").Replace("/", "").Replace("?", "").Replace("|", "").Trim() + "-" + objG.UserSerNo,

                    }).Take(16);

        return objA;
    }


    protected void gvServceListing_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {
        try
        {
            HiddenField adtype = e.Item.FindControl("hdtype") as HiddenField;
            Image imgtype = e.Item.FindControl("imgtype") as Image;

            if (adtype != null)
            {
                if (Convert.ToString(adtype.Value) == "Premium")
                {
                    imgtype.Visible = true;
                }
                else
                {
                    imgtype.Visible = false;
                }
            }
        }
        catch (Exception ex)
        {

        }
    }












    protected void OnRatingChanged(object sender, RatingEventArgs e)
    {
        //if (Context.User.Identity.IsAuthenticated == false)

        //{
        //    HttpContext.Current.User = new GenericPrincipal(new GenericIdentity(string.Empty), null);
        //    FormsAuthentication.SignOut();
        //    Session.Abandon();
        //    string spath = HttpContext.Current.Request.Url.AbsolutePath;
        //    //  Response.Redirect("Organizer-Login.aspx");
        //    //Response.Redirect("Sign-In.aspx?s=" + spath);
        //}
        bool blnRating = false;

        blnRating = ValidateUserRating(Convert.ToString(litiP.Text), Convert.ToInt16(litEventId.Text));
        if (blnRating == false)
        {



            User_Ad_Review_Det obj = new User_Ad_Review_Det();
            obj.UserEmailId = Convert.ToString(litUserId.Text);
            obj.Rating = Convert.ToInt16(e.Value);
            obj.Adid = Convert.ToInt16(litEventId.Text);
            // obj.CreatedBy = Convert.ToString(Context.User.Identity.Name.ToString());

            obj.Ip = litiP.Text;

            dbEmup.User_Ad_Review_Dets.InsertOnSubmit(obj);
            dbEmup.SubmitChanges();
        }
        GetRating(Convert.ToInt16(litEventId.Text));
    }

    private void GetRating(int intAdid)
    {
        //DataTable dt = this.GetData("SELECT ISNULL(AVG(Rating), 0) AverageRating, COUNT(Rating) RatingCount FROM Movie_Rating where MovieId=" + movieId);

        DataTable dt = this.Getdata("SELECT ROUND(ISNULL(CAST(SUM(Rating) AS NUMERIC(5, 2)) / COUNT(Rating), 0), 1) AverageRating, COUNT(Rating) RatingCount FROM User_Ad_Review_Det where Adid= " + intAdid);


        rMovie.CurrentRating = Convert.ToInt32(dt.Rows[0]["AverageRating"]);
        lblRatingStatus.Text = Convert.ToString(dt.Rows[0]["RatingCount"]);
        // lblAverageRating.Text = Convert.ToString(dt.Rows[0]["AverageRating"]);

        //lblAverageRating.Text = Convert.ToDouble(dt.Rows[0]["AverageRating"]).ToString("#0.0").Replace(".0", "");
        //AvgRati = Convert.ToInt32(dt.Rows[0]["AverageRating"]);
        //rMovie.CurrentRating = Convert.ToInt32(dt.Rows[0]["AverageRating"]);
        //lblRatingStatus.Text = string.Format("{0} Users rated. Average Rating {1}", dt.Rows[0]["RatingCount"], dt.Rows[0]["AverageRating"]);
    }


    private void GetComments(int intAdid)
    {
        gvcomments.DataSource = GetUserComments(Convert.ToInt16(intAdid));
        gvcomments.DataBind();
    }

    private DataTable Getdata(string query)
    {
        DataTable dt = new DataTable();
        string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand(query))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    sda.Fill(dt);
                }
            }
            return dt;
        }
    }


    public IQueryable GetUserComments(int intAdid)
    {

        var objUserReviews = (from ObjReview in dbEmup.User_Ad_Review_Dets
                              where ObjReview.Adid == intAdid
                              orderby ObjReview.ReviewId descending
                              select new
                              {

                                  Comment = ObjReview.ReviewDesc,
                                  Name = ObjReview.Name,
                                  Date = ObjReview.Date
                                  //  Rating=ObjReview.Rating

                                  //FunnyChamberTitle1 = "<div class='title-head'><b>" + objFunnyCham.Title.Substring(0, 150) + "..." + "</b><br/></div>" + "</div>",
                                  //JhalakFunnyChamber = (objFunnyCham.Title.Replace(" ", "-").Replace(",", "").Replace(":", "").Replace("!", "").Replace("@", "").Replace("#", "").Replace("$", "").Replace("%", "").Replace("^", "").Replace("&", "").Replace("*", "").Replace("(", "").Replace(")", "").Replace("+", "").Replace("=", "").Replace("{", "").Replace("}", "").Replace(";", "").Replace("'", "").Replace("<", "").Replace(">", "").Replace("/", "").Replace("?", "").Replace("|", "")).Trim() + "-" + objFunnyCham.FunnychamberId,
                                  //FunnychamberId = objFunnyCham.FunnychamberId,
                                  //ImageURl = objFunnyCham.ImageURL != null ? objFunnyCham.ImageURL : "/images/Jhalak-Logo.png",
                              }).Take(10);

        return objUserReviews;

    }




    protected void btncomment_Click(object sender, EventArgs e)
    {
        User_Ad_Review_Det objAdReview;

        objAdReview = new User_Ad_Review_Det();


        objAdReview.Name = Convert.ToString(txtCommentName.Text);

        objAdReview.ReviewerEmailId = Convert.ToString(txtCommentEmail.Text);
        objAdReview.UserEmailId = Convert.ToString(litUserId.Text);



        objAdReview.ReviewDesc = Convert.ToString(txtComment.Text);
        objAdReview.Date = DateTime.Now;
        objAdReview.Adid = Convert.ToInt16(litEventId.Text);



        try
        {
            dbEmup.User_Ad_Review_Dets.InsertOnSubmit(objAdReview);
            dbEmup.SubmitChanges();

            clsPublicMethods.ClearControls(Page.Controls);
            gvcomments.DataSource = GetUserComments(Convert.ToInt16(litEventId.Text));
            gvcomments.DataBind();
            lblCommentMSG.Text = "<div class='txtcenter'><b>" + "Comment Submitted Sucessfully " + "</b></div>";

        }
        catch (Exception ex)
        {
            throw ex;
        }

        finally
        {
            //Response.Redirect("Default.aspx");
        }

    }




    private bool ValidateUserRating(string Ip, Int16 adid)
    {
        var obj = from objM in dbEmup.User_Ad_Review_Dets
                  where objM.Ip == Ip && objM.Adid == adid
                  select objM;
        User_Ad_Review_Det objR = obj.SingleOrDefault();

        if (objR == null)

            return false;
        else
            return true;

        //bool exist = dbEmup.Movie_Ratings.Any(email => email.CreatedBy.Equals(Context.User.Identity.Name.ToString()) && );
        //return exist;
    }



    protected void gvcomments_RowDataBound(object sender, GridViewRowEventArgs e)
    {


        foreach (GridViewRow rw in gvcomments.Rows)
        {
            Label tx = (Label)rw.FindControl("lblCComment");

            if (tx.Text == "")
                rw.Visible = false;

        }


        //if (e.Row.Cells[2].Text == "")
        // e.Row.Visible = false;
    }







}