using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Net.Mail;
using System.Net.Mime;
using System.Data.SqlClient;
using System.Web.UI.HtmlControls;
using System.Collections;
using System.Configuration;
using System.Reflection;
using System.Net;
using System.Xml;
/// <summary>
/// Summary description for PublicMethods

public class PublicMethods
{
    public string salti { get; set; }
    public string slong { get; set; }
    public string cityCode { get; set; }


    EMUPDataContext dbEmup = new EMUPDataContext();
    public PublicMethods()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    /* retrieving categories from database  */


    public IQueryable GetEventCategories()
    {
        var objCategoryList = from objCat in dbEmup.Event_Category_Dets

                              orderby objCat.CategoryName ascending
                              select new
                              {
                                  Value = objCat.EventCategoryId,
                                  Text = objCat.CategoryName
                              };
        return objCategoryList;
    }


    public void ChekBoxReadOnly(Control cntControl)
    {
        foreach (Control c in cntControl.Controls)
        {
            if (c is CheckBox)
                ((CheckBox)c).Enabled = false;
        }
    }
    public void TexBoxBlank(Control cntControl)
    {
        foreach (Control c in cntControl.Controls)
        {
            if (c is TextBox)
                ((TextBox)c).Text = "";
        }
    }
    public void DropDowListBlank(Control cntControl)
    {
        foreach (Control c in cntControl.Controls)
        {
            if (c is DropDownList)
                ((DropDownList)c).SelectedIndex = 0;
        }
    }

    // retrieving city,state from location
    public void GetCityStateName(string strLocation)
    {
        string strSplit = Convert.ToString(strLocation);
        string[] values = strSplit.Split(',');
        for (int i = 0; i < values.Length; i++)
        {
            if (i == 0)
            {

                HttpContext.Current.Session["City"] = values[i].Trim();
            }
            if (i == 1)
            {
                HttpContext.Current.Session["State"] = values[i].Trim();
            }

        }
    }
    // retreiving city, state for autofill in textbox
    public string BindName()
    {
        SqlConnection conSql = null;
        DataTable dt = null;
        using (conSql = new SqlConnection(ConfigurationManager.ConnectionStrings["EMUP_DatabaseConnectionString"].ConnectionString))
        {
            using (SqlCommand cmd = conSql.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select CityDet.CityName,CityDet.StateId,CountryDet.CountryName  from CityDet,CountryDet where CityDet.CountryId=CountryDet.CountryId ";
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    dt = new DataTable();
                    da.Fill(dt);
                }
            }
        }

        StringBuilder output = new StringBuilder();
        output.Append("[");
        for (int i = 0; i < dt.Rows.Count; ++i)
        {
            output.Append("\"" + dt.Rows[i]["CityName"].ToString() + "," + dt.Rows[i]["StateId"].ToString() + "," + dt.Rows[i]["CountryName"].ToString() + "\"");

            if (i != (dt.Rows.Count - 1))
            {
                output.Append(",");
            }
        }
        output.Append("];");

        return output.ToString();
    }

    // filling  dropdown list as per Iqueryable with two parameters
    public void FillDropDownList(IQueryable IqueryName, DropDownList ddlDropDownList)
    {

        ddlDropDownList.Items.Clear();
        ddlDropDownList.Items.Add("Select");
        ddlDropDownList.DataSource = IqueryName;
        ddlDropDownList.DataValueField = "Value";
        ddlDropDownList.DataTextField = "Text";

        ddlDropDownList.DataBind();
    }
    public void FillListBox(IQueryable IqueryName, ListBox lstListBox)
    {
        lstListBox.Items.Clear();

        lstListBox.DataSource = IqueryName;
        lstListBox.DataValueField = "Value";
        lstListBox.DataTextField = "Text";
        // lstListBox.Items.Add("Select");
        lstListBox.DataBind();
    }
    public void FillCheckBoxList(IQueryable IqueryName, CheckBoxList chkBoxlist)
    {

        chkBoxlist.Items.Clear();

        chkBoxlist.DataSource = IqueryName;
        chkBoxlist.DataValueField = "Value";
        chkBoxlist.DataTextField = "Text";



        chkBoxlist.DataBind();

    }
    public void FillRadioBoxList(IQueryable IqueryName, RadioButtonList rdBoxlist)
    {

        rdBoxlist.Items.Clear();

        rdBoxlist.DataSource = IqueryName;
        rdBoxlist.DataValueField = "Value";
        rdBoxlist.DataTextField = "Text";



        rdBoxlist.DataBind();

    }

    public IQueryable GetAdImages(int intAdId)
    {

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

                       Text = objI.Image,




                   };

        return objL;

    }
    public DataTable GetRecords(string strCityName)
    {
        SqlConnection conSql = null;

        using (conSql = new SqlConnection(ConfigurationManager.ConnectionStrings["EMUP_DatabaseConnectionString"].ConnectionString))
        {
            using (SqlCommand cmd = conSql.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select CityDet.CityName,CityDet.StateId,CountryDet.CountryName  from CityDet,CountryDet where CityDet.CountryId=CountryDet.CountryId ";
                DataSet objDs = new DataSet();
                SqlDataAdapter dAdapter = new SqlDataAdapter();
                dAdapter.SelectCommand = cmd;
                conSql.Open();
                dAdapter.Fill(objDs);
                conSql.Close();
                return objDs.Tables[0];

            }

        }
    }
    // send emails to advertier

    public void SendeMail(string strFromMailId, string strToMailId, string strSubject, string strMailBody)
    {
        try
        {

            SmtpClient mySmtpClient = new SmtpClient("smtpout.secureserver.net");


            mySmtpClient.UseDefaultCredentials = false;
            System.Net.NetworkCredential basicAuthenticationInfo = new
               System.Net.NetworkCredential("shailesh.gajula@sudimna.com", "hindu@1234");
            mySmtpClient.Credentials = basicAuthenticationInfo;


            MailAddress from = new MailAddress("shailesh.gajula@sudimna.com", "Enterprise Multi Utility Portal");
            MailAddress to = new MailAddress(strToMailId);
            MailMessage myMail = new System.Net.Mail.MailMessage(from, to);


            //MailAddress replyto = new MailAddress("reply@example.com");
            //myMail.ReplyTo = replyto;

            // set subject and encoding
            myMail.Subject = strSubject;
            myMail.SubjectEncoding = System.Text.Encoding.UTF8;

            // set body-message and encoding
            //     myMail.Body = "<b>" + strMailBody + " </b><br />using <b>HTML</b>.";
            myMail.Body = strMailBody;
            myMail.BodyEncoding = System.Text.Encoding.UTF8;
            // text or html
            myMail.IsBodyHtml = true;

            mySmtpClient.Send(myMail);
        }

        catch (SmtpException ex)
        {
            throw new ApplicationException
              ("SmtpException has occured: " + ex.Message);
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }
    public void SendeMail(string strUserMailId, string strSubject, string strMsg)
    {


        try
        {
            SmtpClient mySmtpClient = new SmtpClient("smtpout.secureserver.net");


            mySmtpClient.UseDefaultCredentials = false;
            System.Net.NetworkCredential basicAuthenticationInfo = new
               System.Net.NetworkCredential("shailesh.gajula@sudimna.com", "hindu@1234");
            mySmtpClient.Credentials = basicAuthenticationInfo;


            MailAddress from = new MailAddress("shailesh.gajula@sudimna.com", "Jhalak");
            MailAddress to = new MailAddress(strUserMailId);
            MailMessage myMail = new System.Net.Mail.MailMessage(from, to);


            //MailAddress replyto = new MailAddress("reply@example.com");
            //myMail.ReplyTo = replyto;

            // set subject and encoding
            myMail.Subject = strSubject;
            myMail.SubjectEncoding = System.Text.Encoding.UTF8;

            // set body-message and encoding
            myMail.Body = "<b>" + strMsg + " </b><br />using <b>HTML</b>.";
            myMail.BodyEncoding = System.Text.Encoding.UTF8;
            // text or html
            myMail.IsBodyHtml = true;

            mySmtpClient.Send(myMail);


        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {


        }
    }
    public void SendMaidPostAdConfirmation(string strUserName, string strAdTitle, string strUserMailId, Int16 intAdId)
    {
        try
        {
            SmtpClient mySmtpClient = new SmtpClient("smtpout.secureserver.net");
            string strMsg;

            mySmtpClient.UseDefaultCredentials = false;
            System.Net.NetworkCredential basicAuthenticationInfo = new
               System.Net.NetworkCredential("shailesh.gajula@sudimna.com", "hindu@1234");
            mySmtpClient.Credentials = basicAuthenticationInfo;


            MailAddress from = new MailAddress("shailesh.gajula@sudimna.com", "Jhalak");
            MailAddress to = new MailAddress(strUserMailId);
            MailMessage myMail = new System.Net.Mail.MailMessage(from, to);


            //MailAddress replyto = new MailAddress("reply@example.com");
            //myMail.ReplyTo = replyto;

            // set subject and encoding
            myMail.Subject = strAdTitle;
            myMail.SubjectEncoding = System.Text.Encoding.UTF8;

            // set body-message and encoding
            strMsg = "<b><p>" + "Hi " + strUserName + "," + "<b></p>";
            strMsg += "<b><p>Thanks for creating your Ad ." + strAdTitle + "Ad Id:" + intAdId + "In www.sudimna.com . <b></p>";
            strMsg += "<b><p>You can view, edit, delete and check replies to all your ads by using My Account .<b></p>";
            strMsg += "<b><p>You can view, edit, delete and check replies to all your ads by using 'My Account'. <b></p>";
            strMsg += "<b><p>Thank you for using Sudimna and good luck with your ad! Remember, Sudimna is the best place for you <b></p>";
            strMsg += "<b><p>to buy, sell, rent or find anything - from used goods to cars to flats to jobs... and everything in between!<b> </p>";
            strMsg += "<b></p>";
            strMsg += "<b>";
            strMsg += "<b>Thanks,</p>";
            strMsg += "<b>";
            strMsg += "<b>";
            strMsg += "<b>Team Sudimna, <b></p>";
            strMsg += "<b>www.Sudimna.com</p>";
            myMail.Body += strMsg;
            myMail.BodyEncoding = System.Text.Encoding.UTF8;
            // text or html
            myMail.IsBodyHtml = true;

            mySmtpClient.Send(myMail);


        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {


        }
    }
    public void SendeMailResetPassword(string strToMailId, string strSubject, string UserName, string strResetLink)
    {
        try
        {
            var strBody = new StringBuilder();
            strBody.AppendLine(UserName);
            strBody.Append("," + "<br />");
            strBody.AppendLine(" We have received your password reset request. This email contains the information that you need to change your password. <br />");
            strBody.AppendLine("Your MailId  :" + strToMailId + " <br />");
            strBody.AppendLine("You can login here " + strResetLink + " to change or reset password" + " <br />");
            strBody.AppendLine("If you did not request that your password be reset, then you should send an email to support@sudimna.com as soon as possible to ensure the safety of your account. " + "<br />");
            strBody.AppendLine("Best Regards, " + "<br />");
            strBody.AppendLine("Sudimna Team" + "<br />");
            strBody.AppendLine("Replies to this message are undeliverable. Please do not reply.");

            SmtpClient mySmtpClient = new SmtpClient("smtpout.secureserver.net");


            mySmtpClient.UseDefaultCredentials = false;
            System.Net.NetworkCredential basicAuthenticationInfo = new
               System.Net.NetworkCredential("shailesh.gajula@sudimna.com", "hindu@1234");
            mySmtpClient.Credentials = basicAuthenticationInfo;


            MailAddress from = new MailAddress("shailesh.gajula@sudimna.com", "Sudimna");
            MailAddress to = new MailAddress(strToMailId);
            MailMessage myMail = new System.Net.Mail.MailMessage(from, to);

            myMail.Subject = strSubject;
            myMail.SubjectEncoding = System.Text.Encoding.UTF8;
            myMail.Body = Convert.ToString(strBody);
            myMail.BodyEncoding = System.Text.Encoding.UTF8;
            myMail.IsBodyHtml = true;

            mySmtpClient.Send(myMail);
        }
        catch (SmtpException ex)
        {
            throw new ApplicationException
              ("SmtpException has occured: " + ex.Message);
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }
    public void SendeMailAddTeam(string strTeamManager, string strTeamManagerMaildId, string strTournamentName, string strResetLink)
    {
        try
        {
            var strBody = new StringBuilder();


            strBody.AppendLine("Hi," + strTeamManager + " you are welcome to join in tournament i.e: <br /><br /> ");
            strBody.AppendLine(strTournamentName + ".");
            strBody.AppendLine(" please enroll as team manager.for that you can access below link to enroll yourself  <br />");
            strBody.AppendLine(strResetLink + "<br /><br />");

            strBody.AppendLine("Best Regards, " + "<br /><br />");
            strBody.AppendLine("Jhalak Team " + "<br /><br />");
            strBody.AppendLine("Replies to this message are undeliverable. Please do not reply.");

            SmtpClient mySmtpClient = new SmtpClient("smtpout.secureserver.net");


            mySmtpClient.UseDefaultCredentials = false;
            System.Net.NetworkCredential basicAuthenticationInfo = new
               System.Net.NetworkCredential("shailesh.gajula@sudimna.com", "hindu@1234");
            mySmtpClient.Credentials = basicAuthenticationInfo;


            MailAddress from = new MailAddress("shailesh.gajula@sudimna.com", "Jhalak.com");
            MailAddress to = new MailAddress(strTeamManagerMaildId);
            MailMessage myMail = new System.Net.Mail.MailMessage(from, to);

            myMail.Subject = "Welcome to " + strTournamentName;
            myMail.SubjectEncoding = System.Text.Encoding.UTF8;
            myMail.Body = Convert.ToString(strBody);
            myMail.BodyEncoding = System.Text.Encoding.UTF8;
            myMail.IsBodyHtml = true;

            mySmtpClient.Send(myMail);
        }
        catch (SmtpException ex)
        {
            throw new ApplicationException
              ("SmtpException has occured: " + ex.Message);
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }
    public void SendeMailAddTeamPlayer(string strPlayerName, string strPlayerMaildId, string strTeamName, string strResetLink)
    {
        try
        {
            var strBody = new StringBuilder();


            strBody.AppendLine("Hi," + strPlayerName + " you are welcome to join in Team i.e: <br /><br /> ");
            //   strBody.AppendLine(strTournamentName + ".");
            strBody.AppendLine(" please enroll as team player.for that you can access below link to enroll yourself  <br />");
            strBody.AppendLine(strResetLink + "<br /><br />");

            strBody.AppendLine("Best Regards, " + "<br /><br />");
            strBody.AppendLine("Jhalak Team " + "<br /><br />");
            strBody.AppendLine("Replies to this message are undeliverable. Please do not reply.");

            SmtpClient mySmtpClient = new SmtpClient("smtpout.secureserver.net");


            mySmtpClient.UseDefaultCredentials = false;
            System.Net.NetworkCredential basicAuthenticationInfo = new
               System.Net.NetworkCredential("shailesh.gajula@sudimna.com", "hindu@1234");
            mySmtpClient.Credentials = basicAuthenticationInfo;


            MailAddress from = new MailAddress("shailesh.gajula@sudimna.com", "Jhalak.com");
            MailAddress to = new MailAddress(strPlayerMaildId);
            MailMessage myMail = new System.Net.Mail.MailMessage(from, to);

            myMail.Subject = "Welcome to " + strTeamName;
            myMail.SubjectEncoding = System.Text.Encoding.UTF8;
            myMail.Body = Convert.ToString(strBody);
            myMail.BodyEncoding = System.Text.Encoding.UTF8;
            myMail.IsBodyHtml = true;

            mySmtpClient.Send(myMail);
        }
        catch (SmtpException ex)
        {
            throw new ApplicationException
              ("SmtpException has occured: " + ex.Message);
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }




    public IQueryable GetUserFullName(int UserId)
    {
        var objUserFullName = from objName in dbEmup.UserDets
                              where objName.EmailId == Convert.ToString(UserId)
                              select new
                              {
                                  FirstName = objName.Name,
                                  LastName = objName.BusinessName
                              };
        return objUserFullName;
    }
    //public IQueryable GetEvents()
    //{
    //    var objEventsList = from objEvent in dbEmup.Event_Creates

    //                      orderby objEvent.Event_Id descending
    //                      select new
    //                      {

    //                          EventTitle = Convert.ToString(objEvent.Event_Name),
    //                          EventDate = Convert.ToString(objEvent.EventDate),
    //                          EventVenue = Convert.ToString(objEvent.Venue),
    //                      };

    //    //   int resultcount = objTeamList.Count();
    //    return objEventsList;
    //}
    public IQueryable GetEvents()
    {
        var objEventsList = from objEvents in dbEmup.Event_Creates
                            orderby objEvents.Event_Id descending
                            select new
                            {
                                EventId = objEvents.Event_Id,
                                EventName = objEvents.Event_Name,
                                EventDesc = objEvents.Event_Desc,
                                Organization = objEvents.Event_Name,
                                EventDate = objEvents.EventDate,
                                EventVenue = objEvents.Venue,

                                AdEventDetail = objEvents.UserAdsDet.City.Replace(",", "-") + "-" + objEvents.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() + "-" + objEvents.AdId,
                              


                            };
        return objEventsList;
    }
    public IQueryable GetEvents(Int16 intEventId)
    {
        var objEventsList = from objEvents in dbEmup.Event_Creates
                            where objEvents.Event_Id == intEventId
                            orderby objEvents.Event_Id descending
                            select new
                            {
                                EventId = objEvents.Event_Id,
                                EventName = objEvents.Event_Name,
                                EventDesc = objEvents.Event_Desc,
                                Organization = objEvents.Event_Name,
                                EventtDate = objEvents.EventDate,

                                AdEventDetail = objEvents.UserAdsDet.City.Replace(",", "-") + "-" + objEvents.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() + "-" + objEvents.AdId,
                            


                            };
        return objEventsList;
    }
    public IQueryable GetEventsPublic()
    {
        var objEventsList = from objEvents in dbEmup.Event_Creates
                            where objEvents.EVentType != "Private"
                            orderby objEvents.Event_Id descending
                            select new
                            {
                                EventId = objEvents.Event_Id,
                                EventName = "<b> <font size='2'>" + objEvents.Event_Name + "</font></b>" + "<br/> " + "<span class='glyphicon glyphicon-calendar'> </span> " + objEvents.EventDate + "  <span class='glyphicon glyphicon-time'> </span>  " + objEvents.StartTime + " To " + objEvents.EndTime + "<br/> " + "<span class='glyphicon glyphicon-map-marker'> </span>" + objEvents.Venue,
                                EventDesc = objEvents.Event_Desc,
                                Organization = objEvents.Event_Name,
                                EventtDate = objEvents.EventDate,


                                AdEventDetail = objEvents.UserAdsDet.City.Replace(",", "-") + "-" + objEvents.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() + "-" + objEvents.AdId,
                             


                            };
        return objEventsList;
    }
    public IQueryable GetEventsPublic(string strContent)
    {
        var objEventsList = from objEvents in dbEmup.Event_Creates
                            where objEvents.EVentType != "Private" &&
                            (objEvents.Event_Category_Det.CategoryName.Contains(strContent))
                            orderby objEvents.Event_Id descending
                            select new
                            {
                                EventId = objEvents.Event_Id,
                                EventName = "<b> <font size='2'>" + objEvents.Event_Name + "</font></b>" + "<br/> " + "<span class='glyphicon glyphicon-calendar'> </span> " + objEvents.EventDate + "  <span class='glyphicon glyphicon-time'> </span>  " + objEvents.StartTime + " To " + objEvents.EndTime + "<br/> " + "<span class='glyphicon glyphicon-map-marker'> </span>" + objEvents.Venue,
                                EventDesc = objEvents.Event_Desc,
                                //  Organization =objEvents.Event_Name,
                                EventtDate = objEvents.EventDate,


                                AdEventDetail = objEvents.UserAdsDet.City.Replace(",", "-") + "-" + objEvents.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() + "-" + objEvents.AdId,
                              


                            };
        return objEventsList;
    }
    public IQueryable GetEventsPublic(string strContent, string strLocation)
    {
        var objEventsList = from objEvents in dbEmup.Event_Creates
                            where objEvents.EVentType != "Private" &&
                            (objEvents.Event_Category_Det.CategoryName.Contains(strContent) || objEvents.Venue.Contains(strLocation))
                            orderby objEvents.Event_Id descending
                            select new
                            {
                                EventId = objEvents.Event_Id,
                                EventName = "<b> <font size='2'>" + objEvents.Event_Name + "</font></b>" + "<br/> " + "<span class='glyphicon glyphicon-calendar'> </span> " + objEvents.EventDate + "  <span class='glyphicon glyphicon-time'> </span>  " + objEvents.StartTime + " To " + objEvents.EndTime + "<br/> " + "<span class='glyphicon glyphicon-map-marker'> </span>" + objEvents.Venue,
                                EventDesc = objEvents.Event_Desc,
                                Organization = objEvents.Event_Name,
                                EventtDate = objEvents.EventDate,


                                AdEventDetail = objEvents.UserAdsDet.City.Replace(",", "-") + "-" + objEvents.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() + "-" + objEvents.AdId,
                              


                            };
        return objEventsList;
    }


    public IQueryable GetEvents(string strUserName)
    {
        var objEventsList = from objEvent in dbEmup.Event_Creates
                            where objEvent.UserAdsDet.UserEmailId == strUserName
                            orderby objEvent.Event_Id descending
                            select new
                            {

                                EventId = objEvent.Event_Id,
                                EventName = objEvent.Event_Name,
                                EventDesc = objEvent.Event_Desc,

                                EventDate = objEvent.EventDate,
                                EventVenue = objEvent.Venue,
                                StartTime = Convert.ToString(objEvent.StartTime),
                                EndTime = Convert.ToString(objEvent.EndTime),


                                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.AdId,
                               


                            };

        //   int resultcount = objTeamList.Count();
        return objEventsList;
    }
    public IQueryable GetEventsList(string strUserName)
    {
        var objEventsList = from objEvent in dbEmup.Event_Creates
                            where objEvent.UserAdsDet.UserEmailId == strUserName
                            orderby objEvent.Event_Id descending
                            select new
                            {

                                Value = objEvent.Event_Id,
                                Text = objEvent.Event_Name,



                            };

        //   int resultcount = objTeamList.Count();
        return objEventsList;
    }
    public IQueryable GetEventGuest(string strUserName)
    {
        var objGuestList = from objGuest in dbEmup.Event_Guests
                           where objGuest.UserAdsDet.UserEmailId == strUserName
                           orderby objGuest.FirstName ascending
                           select new
                           {

                               GuestId = objGuest.Event_Guest_Id,
                               GuestName = Convert.ToString(objGuest.FirstName) + " " + Convert.ToString(objGuest.LastName),
                               EmailId = objGuest.EmailId,

                               ContactNumber = objGuest.ContactNumber


                           };

        //   int resultcount = objTeamList.Count();
        return objGuestList;
    }
    public IQueryable GetEventGuest(string strUserName, Int16 intEventId)
    {
        var objGuestList = from objGuest in dbEmup.Event_Guests
                           where objGuest.UserAdsDet.UserEmailId == strUserName &&
                           objGuest.AdId == Convert.ToInt16(intEventId)
                           orderby objGuest.FirstName ascending
                           select new
                           {

                               GuestId = objGuest.Event_Guest_Id,
                               GuestName = Convert.ToString(objGuest.FirstName) + " " + Convert.ToString(objGuest.LastName),
                               EmailId = objGuest.EmailId,

                               ContactNumber = objGuest.ContactNumber


                           };

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

    public IQueryable GetInvitees(string strUserName)
    {
        var objList = from objInvitee in dbEmup.Event_Invitees
                      where objInvitee.UserAdsDet.UserEmailId == strUserName
                      orderby objInvitee.FirstName ascending
                      select new
                      {

                          InviteeId = objInvitee.Evnent_InvitedId,
                          Name = Convert.ToString(objInvitee.FirstName) + " " + Convert.ToString(objInvitee.LastName),
                          Email = Convert.ToString(objInvitee.EmailId),
                          ContactNo = Convert.ToString(objInvitee.ContactNumber)



                      };


        return objList;
    }
    public IQueryable GetInvitees(Int16 intEventId)
    {
        var objList = from objInvitee in dbEmup.Event_Invitees
                      where objInvitee.AdId == intEventId
                      orderby objInvitee.FirstName ascending
                      select new
                      {

                          InviteeId = objInvitee.Evnent_InvitedId,
                          Name = Convert.ToString(objInvitee.FirstName) + " " + Convert.ToString(objInvitee.LastName),
                          Email = Convert.ToString(objInvitee.EmailId),
                          ContactNo = Convert.ToString(objInvitee.ContactNumber)



                      };


        return objList;
    }
    public IQueryable GetEventOrganizers()
    {
        var objOrgList = from objEvents in dbEmup.UserDets
                         orderby objEvents.EmailId ascending
                         select new
                         {
                             Id = objEvents.UserSerNo,
                             OrganizerName = objEvents.BusinessName,
                             Website = objEvents.Website,
                             Email = objEvents.EmailId,
                             Location = objEvents.Location


                         };
        return objOrgList;
    }

    public IQueryable GetEventOrganizersList()
    {
        var objOrgList = from objEvents in dbEmup.Event_Oraganization_Creates
                         orderby objEvents.AdId descending
                         select new
                         {
                             AdId = objEvents.AdId,
                             Id = objEvents.OrganizerId,
                             OrganizerName = objEvents.OrganizationName,
                             Website = objEvents.Website,
                             Email = objEvents.UserDet.EmailId,
                             Location = objEvents.Address,

                            
                             AdOrganizerDetail = objEvents.UserAdsDet.City.Replace(",", "-") + "-" + objEvents.OrganizationName.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() + "-" + objEvents.AdId,
                          

                         };
        return objOrgList;
    }

    //public IQueryable GetEventOrganizersHome()
    //{
    //    var objOrgList = (from objEvents in dbEmup.Event_Oraganization_Creates
    //                      orderby objEvents.OrganizerId descending
    //                      select new
    //                     {
    //                         Id = objEvents.OrganizerId,
    //                         OrganizerName = objEvents.OrganizationName,
    //                         Website = objEvents.Website,
    //                         Email = objEvents.Email,
    //                         Location = objEvents.Address


    //                     }).Take(4);
    //    return objOrgList;
    //}



    public IQueryable GetSupplier()
    {
        var objSupplierList = from objSuppliers in dbEmup.Event_Supplier_Networks

                              select new
                              {
                                  SupplierId = objSuppliers.SupplierId,
                                  CompanyName = Convert.ToString(objSuppliers.UserDet.BusinessName),
                                  Website = Convert.ToString(objSuppliers.UserDet.Website),
                                  Email = Convert.ToString(objSuppliers.UserDet.EmailId),
                                  ContactNo = Convert.ToString(objSuppliers.UserDet.ContactNo),
                                  Address = Convert.ToString(objSuppliers.UserDet.Location),

                               
                                  AdSupplierDetail = objSuppliers.UserAdsDet.City.Replace(",", "-") + "-" + objSuppliers.UserDet.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() + "-" + objSuppliers.AdId,
                                



                              };
        return objSupplierList;
    }

    public IQueryable GetSupplier(string strContent)
    {
        var objSupplierList = from objSuppliers in dbEmup.Event_Supplier_Networks
                              where objSuppliers.ServiceDescription.Contains(strContent)

                              select new
                              {
                                  SupplierId = objSuppliers.SupplierId,
                                  CompanyName = Convert.ToString(objSuppliers.UserDet.BusinessName),
                                  Website = Convert.ToString(objSuppliers.UserDet.Website),
                                  Email = Convert.ToString(objSuppliers.UserDet.EmailId),
                                  ContactNo = Convert.ToString(objSuppliers.UserDet.ContactNo),
                                  Address = Convert.ToString(objSuppliers.UserDet.Location),

                                 
                                  AdSupplierDetail = objSuppliers.UserAdsDet.City.Replace(",", "-") + "-" + objSuppliers.UserDet.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() + "-" + objSuppliers.AdId,
                                



                              };
        return objSupplierList;
    }
    public IQueryable GetEventSchedules(int eventId)
    {
        var objE = from objEvent in dbEmup.Event_Schedules
                   where objEvent.AdId == eventId
                   orderby objEvent.StartDate descending
                   select new
                   {

                       ScheduleId = objEvent.AdId,
                       Title = objEvent.Title,
                       StartDate = Convert.ToString(objEvent.StartDate),
                       EndDate = Convert.ToString(objEvent.StartDate),
                       StartTime = Convert.ToString(objEvent.Starttime),
                       EndTime = Convert.ToString(objEvent.Endtime),
                       Description = Convert.ToString(objEvent.Description),

                    
                   };

        //   int resultcount = objTeamList.Count();
        return objE;
    }
    // get all ads list



    #region

    public IQueryable GetSponsors()
    {

        var objSponsorsList = from objSponsor in dbEmup.Event_Sponsor_Creates

                              orderby objSponsor.SponsorName ascending
                              select new
                              {
                                  SponsorId = objSponsor.Sponsor_Id,
                                  SponsorName = objSponsor.SponsorName,
                                  SponsorManager = objSponsor.SponsorManager,

                                  Website = objSponsor.Website,
                                  Email = objSponsor.Email,
                                  Address = objSponsor.Address

                              };

        return objSponsorsList;

    }
    //for grid

    //public IQueryable GetSponsorsByEventId(int intEvent)
    //{

    //    var objSponsorsList = from objSponsor in dbEmup.Event_Sponsor_Dets
    //                          where objSponsor.Eventd == Convert.ToInt16(intEvent)
    //                          orderby objSponsor.Event_Create.CreatedDate descending
    //                          select new
    //                          {
    //                              EventId = objSponsor.SponsorEventId,
    //                              SponsorId = objSponsor.SponsorId,
    //                              EventName = objSponsor.Event_Create.Event_Name,
    //                              SponsorName = objSponsor.Event_Sponsor_Create.SponsorName,
    //                              Website = objSponsor.Event_Sponsor_Create.Website,
    //                              Email = objSponsor.Event_Create.EmailId,
    //                              ContactNo = objSponsor.Event_Create.ContactNumber



    //                          };

    //    return objSponsorsList;

    //}


    #endregion



    //cricket  tournament end




    public bool AuthenticateUser(string userName, string password, out string commaSeperatedRoles)
    {
        bool success = false;
        commaSeperatedRoles = string.Empty;

        //The user credential check is hard coded here. This should be done
        //against a user database in real projects
        if (string.Compare(userName, "Administrator", true) == 0 && password == "123")
        {
            commaSeperatedRoles = "Admin";
            success = true;
        }

        if (string.Compare(userName, "Shailesh", true) == 0 && password == "123")
        {
            commaSeperatedRoles = "User";
            success = true;
        }

        return success;
    }


    public string GetUserName(string id)
    {
        var objT = from objOrg in dbEmup.UserDets
                   where objOrg.EmailId == id
                   select objOrg;

        UserDet objO = objT.SingleOrDefault();
        if (objO != null)
        {
            return Convert.ToString(objO.Name);

        }
        else
        {
            return "";
        }
    }
    // clear all text controls
    public void ClearControls(ControlCollection ctrls)
    {
        foreach (Control ctrl in ctrls)
        {
            if (ctrl is TextBox)
                ((TextBox)ctrl).Text = string.Empty;


            ClearControls(ctrl.Controls);
        }
    }


    // insert user login date and time 

    public IQueryable GetTicketedEvents()
    {
        var objEventsList = from objEvents in dbEmup.Event_Ticket_Mgmts
                            orderby objEvents.Tiket_Mgmt_Id descending

                            select new
                            {

                                ClosinDate = Convert.ToString(objEvents.TicketSaleClosingDate),
                                TicketType = Convert.ToString(objEvents.TicketType),
                                UnitePrice = Convert.ToString(objEvents.UnitPrice),
                                MaxOrder = Convert.ToString(objEvents.MaxOrder),
                                MinOrder = Convert.ToString(objEvents.MinOrder),
                                TicketQty = Convert.ToString(objEvents.TotalQty)


                            };
        return objEventsList;
    }




    public IQueryable GetTicketedEvents(int intEventId)
    {
        var objEventsList = from objEvents in dbEmup.Event_Ticket_Mgmts
                            where objEvents.AdId == Convert.ToInt16(intEventId)

                            select new
                            {
                                AdId = objEvents.AdId,
                                TicketId = objEvents.Tiket_Mgmt_Id,
                                ClosinDate = Convert.ToString(objEvents.TicketSaleClosingDate),
                                TicketType = Convert.ToString(objEvents.TicketType),
                                UnitePrice = Convert.ToString(objEvents.UnitPrice) + "" + ".00",
                                MaxOrder = Convert.ToString(objEvents.MaxOrder),
                                MinOrder = Convert.ToString(objEvents.MinOrder),
                                TicketQty = Convert.ToString(objEvents.TotalQty),
                                Available = Convert.ToString(objEvents.AvailableQty)

                            };
        return objEventsList;
    }



    public IQueryable GetGuestsEvents(int intEventId)
    {
        var objEventsList = from objEvents in dbEmup.Event_Guests
                            where objEvents.AdId == Convert.ToInt16(intEventId)

                            select new
                            {
                                AdId = objEvents.AdId,
                                Id = objEvents.Event_Guest_Id,
                                FirstName = Convert.ToString(objEvents.FirstName),
                                lastName = Convert.ToString(objEvents.LastName),
                                Email = Convert.ToString(objEvents.EmailId),
                                OptEmail = Convert.ToString(objEvents.EmailIdOptional),
                                Contact = Convert.ToString(objEvents.ContactNumber),
                                ContactOpt = Convert.ToString(objEvents.ContactNumber1),
                                AboutGuest = Convert.ToString(objEvents.AboutGuest),
                                GuestStartTime = Convert.ToString(objEvents.StartTime),
                                GuestEndTime = Convert.ToString(objEvents.EndTime),
                                GuestTitle = Convert.ToString(objEvents.AboutGuest)

                            };
        return objEventsList;
    }



    public IQueryable GetSponserEvents(int intEventId)
    {
        var objEventsList = from objEvents in dbEmup.Event_Sponsor_Creates
                            where objEvents.AdId == Convert.ToInt16(intEventId)

                            select new
                            {
                                AdId = objEvents.AdId,
                                Id = objEvents.Sponsor_Id,
                                FirstName = Convert.ToString(objEvents.SponsorName),
                                SponserType = Convert.ToString(objEvents.SponsorType),
                                ManagerName = Convert.ToString(objEvents.SponsorManager),
                                Contact = Convert.ToString(objEvents.PhoneNo),
                                Website = Convert.ToString(objEvents.Website),
                                Email = Convert.ToString(objEvents.Email),
                                Address = Convert.ToString(objEvents.Address),
                                AboutSponser = Convert.ToString(objEvents.Description),


                            };
        return objEventsList;
    }



    public IQueryable GetPartnerEvents(int intEventId)
    {
        var objEventsList = from objEvents in dbEmup.Event_Partners
                            where objEvents.AdId == Convert.ToInt16(intEventId)

                            select new
                            {
                                Adid = objEvents.AdId,
                                Id = objEvents.Event_PartnerId,
                                FirstName = Convert.ToString(objEvents.PartnerName),
                                PartnerType = Convert.ToString(objEvents.PartnerType),
                                PartnerEmail = Convert.ToString(objEvents.PartnerEmail),



                            };
        return objEventsList;
    }


    //public IQueryable GetTicketedEvents(String StrMailId)
    //{
    //    var objEventsList = from objEvents in dbEmup.Event_Ticket_Mgmts
    //                        where objEvents.UserAdsDet.UserEmailId == StrMailId

    //                        select new
    //                        {
    //                            ClosinDate = Convert.ToString(objEvents.TicketSaleClosingDate),
    //                            TicketType = Convert.ToString(objEvents.TicketType),
    //                            UnitePrice = Convert.ToString(objEvents.UnitPrice),
    //                            MaxOrder = Convert.ToString(objEvents.MaxOrder),
    //                            MinOrder = Convert.ToString(objEvents.MinOrder)


    //                        };
    //    return objEventsList;
    //}



    public IQueryable GetEventPartners(string strUserName)
    {
        var objEventsList = from objEvent in dbEmup.Event_Partners
                            where objEvent.UserAdsDet.UserEmailId == strUserName
                            orderby objEvent.Event_PartnerId descending
                            select new
                            {

                                AdId = objEvent.AdId,
                                PartnerId = objEvent.Event_PartnerId,
                                PartnerName = objEvent.PartnerName,
                                PartnerType = objEvent.PartnerType,
                                MailId = objEvent.UserAdsDet.UserEmailId



                            };

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



    public IQueryable GetEventSponsers(String StrMailId)
    {

        var objsponersList = from objSponser in dbEmup.Event_Sponsor_Creates
                             where objSponser.UserAdsDet.UserEmailId == StrMailId
                             orderby objSponser.Sponsor_Id descending
                             select new
                             {
                                 Title = objSponser.SponsorName,


                                 Id = objSponser.Sponsor_Id
                                 //  LanguageDet.LanguageName
                             };

        return objsponersList;

    }


    public IQueryable GetTicketManagementUser(String StrMailId)
    {

        var ObjTicket = from ObjT in dbEmup.Event_Ticket_Mgmts
                        where ObjT.UserAdsDet.UserEmailId == StrMailId
                        orderby ObjT.Tiket_Mgmt_Id descending
                        select new
                        {

                            Id = ObjT.Tiket_Mgmt_Id,
                            Title = ObjT.TicketType
                        };
        return ObjTicket;

    }








    public IQueryable GetTodaysEvents()
    {
        var dateCriteria = DateTime.Now.Date.AddDays(7);

        var objOrgList = (from objEvents in dbEmup.Event_Creates
                          where (Convert.ToDateTime(objEvents.CreatedDate) <= dateCriteria)
                                     //   Where(objEvents => startOfWeek <= objEvents.DateField && objEvents.DateField < endOfWeek)


                                     //&& (Convert.ToDateTime(objEvents.EventEndDate)<=DateTime.Now.Date.AddDays(-7)))
                                     && objEvents.UserAdsDet.Status == null
                          orderby objEvents.Event_Id descending
                          //where ((Convert.ToDateTime(objEvents.EventDate) == DateTime.Now ) &&
                          //(Convert.ToDateTime(objEvents.EventEndDate)=>DateTime.Now))

                          select new
                          {
                              Id = objEvents.AdId,
                              Title = objEvents.Event_Name,
                              Location = objEvents.Venue,

                              AdEventDetail = objEvents.UserAdsDet.City.Replace(",", "-") + "-" + objEvents.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() + "-" + objEvents.AdId,
                             


                          }).Take(10);
        return objOrgList;
    }

    public IQueryable GetUpComingEvents(string strLoc)
    {
        var dateCriteria = DateTime.Now.Date.AddDays(7);
        var objOrgList = (from objEvents in dbEmup.Event_Creates
                          where (Convert.ToDateTime(objEvents.EventDate) > dateCriteria) && objEvents.UserAdsDet.Status == null && objEvents.UserAdsDet.AdType == "Free"
                          && objEvents.UserAdsDet.Status == null && objEvents.UserAdsDet.City == strLoc
                          orderby objEvents.Event_Id descending
                          //where ((Convert.ToDateTime(objEvents.EventDate) == DateTime.Now) &&
                          //(Convert.ToDateTime(objEvents.EventEndDate)=>DateTime.Now))

                          select new
                          {
                              Id = objEvents.AdId,
                              Title = objEvents.Event_Name,
                              Location = objEvents.Venue,
                              Address = objEvents.UserAdsDet.City,
                              Date = objEvents.EventDate,
                              StartTime = objEvents.StartTime,
                              EndTime = objEvents.EndTime,

                              AdEventDetail = objEvents.UserAdsDet.City.Replace(",", "-") + "-" + objEvents.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() + "-" + objEvents.AdId,
                            


                          }).Take(10);
        return objOrgList;
    }

    public IQueryable GetUpComingEvents(int startZip, int endZip)
    {
        var dateCriteria = DateTime.Now.Date.AddDays(7);
        var objOrgList = (from objEvents in dbEmup.Event_Creates
                          where (Convert.ToDateTime(objEvents.EventDate) > dateCriteria) && objEvents.UserAdsDet.Status == null
                          && (Convert.ToInt32(objEvents.UserAdsDet.ZipCode) >= startZip && Convert.ToInt32(objEvents.UserAdsDet.ZipCode) <= endZip)
                          orderby objEvents.Event_Id descending
                          //where ((Convert.ToDateTime(objEvents.EventDate) == DateTime.Now) &&
                          //(Convert.ToDateTime(objEvents.EventEndDate)=>DateTime.Now))

                          select new
                          {
                              Id = objEvents.AdId,
                              Title = objEvents.Event_Name,
                              Location = objEvents.Venue,
                              Address = objEvents.UserAdsDet.City,
                              Date = objEvents.EventDate,
                              StartTime = objEvents.StartTime,
                              EndTime = objEvents.EndTime,

                              AdEventDetail = objEvents.UserAdsDet.City.Replace(",", "-") + "-" + objEvents.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() + "-" + objEvents.AdId,
                             

                          }).Take(10);
        return objOrgList;
    }


    public IQueryable GetUpComingEvents()
    {
        var dateCriteria = DateTime.Now.Date.AddDays(7);
        var objOrgList = (from objEvents in dbEmup.Event_Creates
                          where (Convert.ToDateTime(objEvents.EventDate) > dateCriteria) && objEvents.UserAdsDet.Status == null && objEvents.UserAdsDet.AdType == "Free"
                          && objEvents.UserAdsDet.Status == null
                          orderby objEvents.Event_Id descending
                          //where ((Convert.ToDateTime(objEvents.EventDate) == DateTime.Now) &&
                          //(Convert.ToDateTime(objEvents.EventEndDate)=>DateTime.Now))

                          select new
                          {
                              Id = objEvents.AdId,
                              AdId = objEvents.AdId,
                              Title = objEvents.Event_Name,
                              AdTitle = objEvents.Event_Name,
                              Location = objEvents.Venue,
                              Address = objEvents.UserAdsDet.City,
                              City = objEvents.UserAdsDet.City,
                              Date = objEvents.EventDate,
                              EventDate = objEvents.EventDate,
                              StartTime = objEvents.StartTime,
                              EndTime = objEvents.EndTime,

                              AdEventDetail = objEvents.UserAdsDet.City.Replace(",", "-") + "-" + objEvents.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() + "-" + objEvents.AdId,
                            

                          }).Take(10);
        return objOrgList;
    }
    public IQueryable GetTodaysEventsList()
    {
        var objOrgList = from objEvents in dbEmup.Event_Creates
                         where ((Convert.ToDateTime(objEvents.EventDate) <= DateTime.Now && (Convert.ToDateTime(objEvents.EventEndDate) >= DateTime.Now)))
                         && objEvents.UserAdsDet.Status == null
                         orderby objEvents.AdId descending
                         //where ((Convert.ToDateTime(objEvents.EventDate) == DateTime.Now ) &&
                         //(Convert.ToDateTime(objEvents.EventEndDate)=>DateTime.Now))

                         select new
                         {
                             Id = objEvents.AdId,
                             Title = objEvents.Event_Name,
                             Location = objEvents.Venue,

                             AdEventDetail = objEvents.UserAdsDet.City.Replace(",", "-") + "-" + objEvents.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() + "-" + objEvents.AdId,
                          

                         };
        return objOrgList;
    }







    public IQueryable GetUpComingEventsList()
    {
        var objOrgList = from objEvents in dbEmup.Event_Creates
                         where Convert.ToDateTime(objEvents.EventDate) >= DateTime.Now.AddDays(+7)
                         && objEvents.UserAdsDet.Status == null
                         orderby objEvents.AdId descending
                         //where ((Convert.ToDateTime(objEvents.EventDate) == DateTime.Now ) &&
                         //(Convert.ToDateTime(objEvents.EventEndDate)=>DateTime.Now))

                         select new
                         {
                             Id = objEvents.AdId,
                             Title = objEvents.Event_Name,
                             Location = objEvents.Venue,

                             AdEventDetail = objEvents.UserAdsDet.City.Replace(",", "-") + "-" + objEvents.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() + "-" + objEvents.AdId,
                           

                         };
        return objOrgList;
    }


    public IQueryable GetTicketedEventsList()
    {
        var objOrgList = from objEvents in dbEmup.Event_Creates
                         where objEvents.AdmissionType == "Paid Entry"
                         orderby objEvents.Event_Id descending
                         //where ((Convert.ToDateTime(objEvents.EventDate) == DateTime.Now ) &&
                         //(Convert.ToDateTime(objEvents.EventEndDate)=>DateTime.Now))

                         select new
                         {
                             Id = objEvents.Event_Id,
                             Title = objEvents.Event_Name,
                             Location = objEvents.Venue,

                             AdEventDetail = objEvents.UserAdsDet.City.Replace(",", "-") + "-" + objEvents.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() + "-" + objEvents.AdId,
                           

                         };
        return objOrgList;
    }


    public IQueryable GetTicketedEventsHome()
    {
        var objOrgList = (from objEvents in dbEmup.Event_Creates
                          where objEvents.AdmissionType == "Paid Entry"
                          && objEvents.UserAdsDet.Status == null
                          orderby objEvents.Event_Id descending
                          //where ((Convert.ToDateTime(objEvents.EventDate) == DateTime.Now ) &&
                          //(Convert.ToDateTime(objEvents.EventEndDate)=>DateTime.Now))

                          select new
                          {
                              Id = objEvents.AdId,
                              Title = objEvents.Event_Name,
                              Location = objEvents.Venue,

                              AdEventDetail = objEvents.UserAdsDet.City.Replace(",", "-") + "-" + objEvents.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() + "-" + objEvents.AdId,
                            

                          }).Take(10);
        return objOrgList;
    }




    public IQueryable GetAllEvents()
    {
        var objOrgList = from objEvents in dbEmup.Event_Creates

                         orderby objEvents.Event_Id descending
                         //where ((Convert.ToDateTime(objEvents.EventDate) == DateTime.Now ) &&
                         //(Convert.ToDateTime(objEvents.EventEndDate)=>DateTime.Now))

                         select new
                         {
                             Id = objEvents.Event_Id,
                             Title = objEvents.Event_Name,
                             Location = objEvents.Venue,

                             AdEventDetail = objEvents.UserAdsDet.City.Replace(",", "-") + "-" + objEvents.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() + "-" + objEvents.AdId,
                           
                         };
        return objOrgList;
    }



    public IQueryable GetLatestAnnouncedEventsHome(string strLoc)
    {
        var dateCriteria = DateTime.Now.Date.AddDays(-7);
        var objOrgList = (from objEvents in dbEmup.Event_Creates
                          where objEvents.UserAdsDet.Status == null &&
                          ((Convert.ToDateTime(objEvents.CreatedDate) <= dateCriteria && Convert.ToDateTime(objEvents.CreatedDate) <= DateTime.Now)) && objEvents.UserAdsDet.AdType == "Free"
                          && objEvents.UserAdsDet.City == strLoc
                          //    (Convert.ToDateTime(objEvents.EventEndDate) <= DateTime.Now))
                          orderby objEvents.Event_Id descending

                          //where ((Convert.ToDateTime(objEvents.EventDate) == DateTime.Now ) &&
                          //(Convert.ToDateTime(objEvents.EventEndDate)=>DateTime.Now))

                          select new
                          {
                              Id = objEvents.AdId,
                              Title = objEvents.Event_Name,
                              Location = objEvents.Venue,
                              Address = objEvents.UserAdsDet.City,
                              Date = objEvents.EventDate,
                              StartTime = objEvents.StartTime,
                              EndTime = objEvents.EndTime,

                              AdEventDetail = objEvents.UserAdsDet.City.Replace(",", "-") + "-" + objEvents.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() + "-" + objEvents.AdId,
                            


                          }).Take(10);
        return objOrgList;
    }

    public IQueryable GetLatestAnnouncedEventsHome(int startZip, int endZip)
    {
        var dateCriteria = DateTime.Now.Date.AddDays(-7);
        var objOrgList = (from objEvents in dbEmup.Event_Creates
                          where objEvents.UserAdsDet.Status == null &&
                          ((Convert.ToDateTime(objEvents.CreatedDate) <= dateCriteria && Convert.ToDateTime(objEvents.CreatedDate) <= DateTime.Now)) && objEvents.UserAdsDet.AdType == "Free"

                          //    (Convert.ToDateTime(objEvents.EventEndDate) <= DateTime.Now))
                          && (Convert.ToInt32(objEvents.UserAdsDet.ZipCode) >= startZip && Convert.ToInt32(objEvents.UserAdsDet.ZipCode) <= endZip)

                          orderby objEvents.Event_Id descending

                          //where ((Convert.ToDateTime(objEvents.EventDate) == DateTime.Now ) &&
                          //(Convert.ToDateTime(objEvents.EventEndDate)=>DateTime.Now))

                          select new
                          {
                              Id = objEvents.AdId,
                              Title = objEvents.Event_Name,
                              Location = objEvents.Venue,
                              Address = objEvents.UserAdsDet.City,
                              Date = objEvents.EventDate,
                              StartTime = objEvents.StartTime,
                              EndTime = objEvents.EndTime,

                              AdEventDetail = objEvents.UserAdsDet.City.Replace(",", "-") + "-" + objEvents.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() + "-" + objEvents.AdId,
                            


                          }).Take(10);
        return objOrgList;
    }

    public IQueryable GetLatestAnnouncedEventsHome()
    {
        var dateCriteria = DateTime.Now.Date.AddDays(-7);
        var datetime = DateTime.Now;
        var objOrgList = (from objEvents in dbEmup.Event_Creates
                          where objEvents.UserAdsDet.Status == null &&
                          ((Convert.ToDateTime(objEvents.CreatedDate) <= datetime && Convert.ToDateTime(objEvents.EventDate) >= datetime)) && objEvents.UserAdsDet.AdType == "Free"

                          //    (Convert.ToDateTime(objEvents.EventEndDate) <= DateTime.Now))
                          orderby objEvents.Event_Id descending

                          //where ((Convert.ToDateTime(objEvents.EventDate) == DateTime.Now ) &&
                          //(Convert.ToDateTime(objEvents.EventEndDate)=>DateTime.Now))

                          select new
                          {
                              Id = objEvents.AdId,
                              AdId = objEvents.AdId,
                              Title = objEvents.Event_Name,
                              AdTitle = objEvents.Event_Name,
                              Location = objEvents.Venue,
                              Address = objEvents.UserAdsDet.City,
                              City = objEvents.UserAdsDet.City,
                              Date = objEvents.EventDate,
                              EventDate = objEvents.EventDate,
                              StartTime = objEvents.StartTime,
                              EndTime = objEvents.EndTime,

                              AdEventDetail = objEvents.UserAdsDet.City.Replace(",", "-") + "-" + objEvents.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() + "-" + objEvents.AdId,
                            


                          }).Take(10);
        return objOrgList;
    }



    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,
                       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),
                       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,


                   };

        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,
                       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),
                       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,


                   };

        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,

                   };

        return objA;
    }

    public IQueryable GetProvidersListings(int intCatId)

    {
        var objA = (from objG in dbEmup.UserDets
                   where objG.Role == "Business" && (objG.CategoryId == intCatId || objG.CategoryId == 21)
                   orderby objG.UserType descending, objG.UserSerNo descending
                   select new
                   {
                       UserId = objG.UserSerNo,
                       AdTitle=objG.Name,
                       UserSerNo = objG.UserSerNo,
                       usertype = Convert.ToString(Convert.ToString(objG.UserType)),
                       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 Recordcount)

    {
        var objA = (from objG in dbEmup.UserDets
                    where objG.Role == "Business" && objG.CategoryId == intCatId
                    orderby objG.UserType descending, objG.UserSerNo 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(Recordcount);

        return objA;
    }
    public IQueryable GetSuppliers(int intCatId, int intSubCateId, string adType, string strLoc)

    {
        var objA = (from objG in dbEmup.Event_Supplier_Networks
                    where objG.UserAdsDet.CategoryId == intCatId && objG.UserAdsDet.SubCategoryId == intSubCateId && objG.UserAdsDet.Status == null
                    && objG.UserAdsDet.AdType == adType && objG.UserAdsDet.City == strLoc
                    orderby objG.UserAdsDet.UserAdId descending
                    select new
                    {
                        Id = objG.UserAdsDet.UserAdId,
                        SubcategoryId = objG.UserAdsDet.SubCategoryId,
                        AdTitle = objG.UserAdsDet.AdTitle,
                        Category = objG.UserAdsDet.CategoryDet.Categoryname,
                        SubCategory = objG.UserAdsDet.SubCategoryDet.SubCategory,
                        CategoryId = objG.UserAdsDet.CategoryId,
                        Address = objG.UserAdsDet.City,
                        Name = objG.UserAdsDet.AdTitle,

                      
                        AdSupplierDetail = objG.UserAdsDet.City.Replace(",", "-") + "-" + objG.UserAdsDet.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.AdId,
                     


                    }).Take(100);

        return objA;
    }

    public IQueryable GetSuppliers(int intCatId, int intSubCateId, string adType, int startZip, int endZip)
    {
        var objA = (from objG in dbEmup.Event_Supplier_Networks
                    where objG.UserAdsDet.CategoryId == intCatId && objG.UserAdsDet.SubCategoryId == intSubCateId && objG.UserAdsDet.Status == null
                    && objG.UserAdsDet.AdType == adType && (Convert.ToInt32(objG.UserAdsDet.ZipCode) >= startZip && Convert.ToInt32(objG.UserAdsDet.ZipCode) <= endZip)
                    orderby objG.UserAdsDet.UserAdId descending
                    select new
                    {
                        Id = objG.UserAdsDet.UserAdId,
                        SubcategoryId = objG.UserAdsDet.SubCategoryId,
                        AdTitle = objG.UserAdsDet.AdTitle,
                        Category = objG.UserAdsDet.CategoryDet.Categoryname,
                        SubCategory = objG.UserAdsDet.SubCategoryDet.SubCategory,
                        CategoryId = objG.UserAdsDet.CategoryId,
                        Address = objG.UserAdsDet.City,
                        Name = objG.UserAdsDet.AdTitle,

                        AdSupplierDetail = objG.UserAdsDet.City.Replace(",", "-") + "-" + objG.UserAdsDet.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.AdId,
                      


                    }).Take(100);

        return objA;
    }

    public IQueryable GetSuppliers(int intCatId, int intSubCateId, string adType)
    {
        var objA = (from objG in dbEmup.Event_Supplier_Networks
                    where objG.UserAdsDet.CategoryId == intCatId && objG.UserAdsDet.SubCategoryId == intSubCateId && objG.UserAdsDet.Status == null
                    && objG.UserAdsDet.AdType == adType
                    orderby objG.UserAdsDet.AdType descending, objG.UserAdsDet.UserAdId descending
                    select new
                    {
                        Id = objG.UserAdsDet.UserAdId,
                        AdId = objG.UserAdsDet.UserAdId,
                        SubcategoryId = objG.UserAdsDet.SubCategoryId,
                        AdTitle = objG.UserAdsDet.AdTitle,
                        Category = objG.UserAdsDet.CategoryDet.Categoryname,
                        SubCategory = objG.UserAdsDet.SubCategoryDet.SubCategory,
                        CategoryId = objG.UserAdsDet.CategoryId,
                        Address = objG.UserAdsDet.City,
                        City = objG.UserAdsDet.City,
                        Name = objG.UserAdsDet.AdTitle,
                        AdType = Convert.ToString(Convert.ToString(objG.UserAdsDet.AdType)),

                        AdSupplierDetail = objG.UserAdsDet.City.Replace(",", "-") + "-" + objG.UserAdsDet.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.AdId,
                      



                    }).Take(100);

        return objA;
    }
    public IQueryable GetVenues(int intCatId, int intSubCateId, string adType, string strLoc)

    {
        var objA = (from objG in dbEmup.Event_Venue_Managers
                    where objG.UserAdsDet.CategoryId == intCatId && objG.UserAdsDet.SubCategoryId == intSubCateId && objG.UserAdsDet.Status == null
                    && objG.UserAdsDet.AdType == adType && objG.UserAdsDet.City == strLoc
                    orderby objG.UserAdsDet.UserAdId descending
                    select new
                    {
                        Id = objG.UserAdsDet.UserAdId,
                        SubcategoryId = objG.UserAdsDet.SubCategoryId,
                        AdTitle = objG.UserAdsDet.AdTitle,
                        Category = objG.UserAdsDet.CategoryDet.Categoryname,
                        SubCategory = objG.UserAdsDet.SubCategoryDet.SubCategory,
                        CategoryId = objG.UserAdsDet.CategoryId,
                        Address = objG.UserAdsDet.City,
                        Name = objG.UserAdsDet.AdTitle,
                        AdVenueDetail = objG.UserAdsDet.City.Replace(",", "-") + "-" + objG.UserAdsDet.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.AdId,



                    }).Take(100);

        return objA;
    }
    public IQueryable GetVenues(int intCatId, int intSubCateId, string adType)
    {
        var objA = (from objG in dbEmup.Event_Venue_Managers
                    where objG.UserAdsDet.CategoryId == intCatId && objG.UserAdsDet.SubCategoryId == intSubCateId && objG.UserAdsDet.Status == null
                    && objG.UserAdsDet.AdType == adType
                    orderby objG.UserAdsDet.UserAdId descending
                    select new
                    {
                        Id = objG.UserAdsDet.UserAdId,
                        AdId = objG.UserAdsDet.UserAdId,
                        SubcategoryId = objG.UserAdsDet.SubCategoryId,
                        AdTitle = objG.UserAdsDet.AdTitle,
                        Category = objG.UserAdsDet.CategoryDet.Categoryname,
                        SubCategory = objG.UserAdsDet.SubCategoryDet.SubCategory,
                        CategoryId = objG.UserAdsDet.CategoryId,
                        Address = objG.UserAdsDet.City,
                        City = objG.UserAdsDet.City,
                        Name = objG.UserAdsDet.AdTitle,

                      
                        AdVenueDetail = objG.UserAdsDet.City.Replace(",", "-") + "-" + objG.UserAdsDet.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.AdId,



                    }).Take(100);

        return objA;
    }

    public IQueryable GetVenues(int intCatId, int intSubCateId, string adType, int startZip, int endZip)
    {
        var objA = (from objG in dbEmup.Event_Venue_Managers
                    where objG.UserAdsDet.CategoryId == intCatId && objG.UserAdsDet.SubCategoryId == intSubCateId && objG.UserAdsDet.Status == null
                    && objG.UserAdsDet.AdType == adType && (Convert.ToInt32(objG.UserAdsDet.ZipCode) >= startZip && Convert.ToInt32(objG.UserAdsDet.ZipCode) <= endZip)

                    orderby objG.UserAdsDet.UserAdId descending
                    select new
                    {
                        Id = objG.UserAdsDet.UserAdId,
                        SubcategoryId = objG.UserAdsDet.SubCategoryId,
                        AdTitle = objG.UserAdsDet.AdTitle,
                        Category = objG.UserAdsDet.CategoryDet.Categoryname,
                        SubCategory = objG.UserAdsDet.SubCategoryDet.SubCategory,
                        CategoryId = objG.UserAdsDet.CategoryId,
                        Address = objG.UserAdsDet.City,
                        Name = objG.UserAdsDet.AdTitle,

                      
                        AdVenueDetail = objG.UserAdsDet.City.Replace(",", "-") + "-" + objG.UserAdsDet.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.AdId,



                    }).Take(100);

        return objA;
    }


    public IQueryable GetLatestAnnouncedEventsList()
    {
        var objOrgList = from objEvents in dbEmup.Event_Creates
                         where ((Convert.ToDateTime(objEvents.CreatedDate) == DateTime.Now))
                         && objEvents.UserAdsDet.Status == null
                         orderby objEvents.AdId descending

                         //where ((Convert.ToDateTime(objEvents.EventDate) == DateTime.Now ) &&
                         //(Convert.ToDateTime(objEvents.EventEndDate)=>DateTime.Now))

                         select new
                         {
                             Id = objEvents.AdId,
                             Title = objEvents.Event_Name,
                             Location = objEvents.Venue,

                             AdEventDetail = objEvents.UserAdsDet.City.Replace(",", "-") + "-" + objEvents.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() + "-" + objEvents.AdId,
                          

                         };
        return objOrgList;
    }



    #region Event Manager Login Name







    //public bool ValidateVenueManager(string strUseName, string strPwd)
    //{
    //    var objUser = from objU in dbEmup.Event_Venue_Manager_Creates
    //                  where objU.EmailId == Convert.ToString(strUseName) &&
    //             objU.Password == Convert.ToString(strPwd)
    //                  select objU;

    //    if (objUser.Count() > 0)

    //        return true;
    //    else
    //        return false;

    //}



    #endregion
    //public DataTable GetLocation(string varIPAddress)
    //{
    //    WebRequest varWebRequest = WebRequest.Create("http://freegeoip.net/xml/" + varIPAddress);
    //    WebProxy px = new WebProxy("http://freegeoip.net/xml/" + varIPAddress, true);
    //    varWebRequest.Proxy = px;
    //    varWebRequest.Timeout = 2000;
    //    try
    //    {
    //        WebResponse rep = varWebRequest.GetResponse();
    //        XmlTextReader xtr = new XmlTextReader(rep.GetResponseStream());
    //        DataSet ds = new DataSet();
    //        ds.ReadXml(xtr);
    //        return ds.Tables[0];
    //    }
    //    catch
    //    {
    //        return null;
    //    }
    //}

    public DataTable GetLocation(string varIPAddress)
    {
        string APIKey = "3ba21b6217ab971cc06a76cbd292fc822086c138efdf8ebc87290356cf535881";
        string url = string.Format("http://api.ipinfodb.com/v3/ip-city/?key={0}&ip={1}&format=xml", APIKey, varIPAddress);
        WebRequest varWebRequest = WebRequest.Create(url);
        WebProxy px = new WebProxy(url, true);
        varWebRequest.Proxy = px;
        varWebRequest.Timeout = 2000;
        try
        {
            WebResponse rep = varWebRequest.GetResponse();
            XmlTextReader xtr = new XmlTextReader(rep.GetResponseStream());
            DataSet ds = new DataSet();
            ds.ReadXml(xtr);



            return ds.Tables[0];




        }
        catch (Exception ex)
        {
            return null;
        }

    }

    public IQueryable GetCountryList()
    {

        var objC = from objL in dbEmup.CountryLists

                   select new
                   {
                       Value = objL.CountryId,
                       Text = objL.Name

                   };

        return objC;



    }
    public IQueryable GetStates(int intCountry)
    {

        var objC = from objL in dbEmup.StatesLists
                   where objL.CountryId == intCountry
                   orderby objL.StateName ascending

                   select new
                   {
                       Value = objL.StateId,
                       Text = objL.StateName

                   };

        return objC;

    }
    public IQueryable GetCityList(int stateId)
    {

        var objC = from objL in dbEmup.CityLists
                   where objL.StateId == stateId
                   orderby objL.CityName ascending

                   select new
                   {
                       Value = objL.Cityid,
                       Text = objL.CityName

                   };

        return objC;



    }



    public DataSet getZipCode(string city, string state)
    {
        DataSet dsLoadGallery = new DataSet();
        SqlConnection con;
        con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        try
        {
            string sql = "select * from zipcode where city=" + "'" + '"' + city + '"' + "'" + " and [state] = " + "'" + '"' + state.Trim() + '"' + "'";
            //  string sql = "select * from zipcode where city=" + "'" + '"' + city + '"' + "'";
            SqlDataAdapter daLoadGallery = new SqlDataAdapter(sql, con);
            daLoadGallery.Fill(dsLoadGallery, "LoadZip");
        }
        catch (Exception ex)
        {
            //Exception Handled
            Console.WriteLine(ex.Message);
        }
        finally
        {
        }
        return dsLoadGallery;
    }

    public IQueryable GetAds(int intCatId, int intSubCateId, int startZip, int endZip, string adtype)
    {
        DateTime today = DateTime.Today;
        DateTime timeGap;
        if (adtype == "Free")
        {
            timeGap = today.AddDays(-13);
        }
        else
        {
            timeGap = today.AddDays(-89);
        }

        var objA = (from objG in dbEmup.UserAdsDets
                    where objG.CategoryId == intCatId && objG.SubCategoryId == intSubCateId && objG.AdType == adtype
                    && objG.CreatedDate > timeGap && objG.Status == null
                    && (Convert.ToInt32(objG.ZipCode) >= startZip && Convert.ToInt32(objG.ZipCode) <= endZip)
                    orderby objG.UserAdId descending
                    select new
                    {
                        AdId = objG.UserAdId,
                        SubcategoryId = objG.SubCategoryId,
                        AdTitle = objG.AdTitle,
                        Category = objG.CategoryDet.Categoryname,
                        SubCategory = objG.SubCategoryDet.SubCategory,
                        CategoryId = objG.CategoryId,
                        Location = objG.City,
                        City = objG.City,

                        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,
                        AdOrganizerDetail = 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,
                        AdSupplierDetail = 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,
                        AdVenueDetail = 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(100);

        return objA;
    }



    #region

    public DataSet GetEventsByRadius(double latitude, double longitude, string Type)
    {
        var datetime = DateTime.Now.ToString(format: "MM/dd/yy");
        datetime = datetime.ToString().Replace("-", "/");

        DataSet ds = new DataSet();
        string sqlQuery = "SELECT AdId,AdTitle,Venue,EventDate,StartTime,EndTime, City,altitude,ImageUrl,longitude,AdEventDetail FROM( SELECT top 5 z.UserAdId as AdId, z.Adtitle as AdTitle,x.Venue,x.EventDate,x.StartTime,x.EndTime,z.location,z.imageurl, z.ZipCode,z.City,z.altitude, z.longitude, z.SubcategoryId,z.Categoryid ,";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdEventDetail, p.distance_unit ";
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Altitude)) * COS(RADIANS(p.longpoint - z.longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Altitude)))) AS distance FROM UserAdsdet AS z ";
        sqlQuery += " JOIN [Event_Create] AS x ON x.AdId=z.UserAdId ";
        if (Type == "Premium")
        {
            sqlQuery += " JOIN( SELECT " + latitude + " AS latpoint," + longitude + " AS longpoint,2000 AS radius, 111.045 AS distance_unit ) AS p ON 1 = 1 ";
        }
        else
        {
            sqlQuery += " JOIN( SELECT " + latitude + " AS latpoint," + longitude + " AS longpoint,300 AS radius, 111.045 AS distance_unit ) AS p ON 1 = 1 ";
        }
        sqlQuery += " WHERE  z.Status is null and z.AdType='" + Type + "' and CONVERT(varchar, x.EventEndDate, 110) >= CONVERT(date, '" + datetime + "')  and z.Altitude 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 + ";
        if (Type == "Premium")
        {
            sqlQuery += " (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))) AS d WHERE distance <=2000 order by AdId desc ";
        }
        else
        {
            sqlQuery += " (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))) AS d WHERE distance <=300 order by AdId 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;
    }

    public DataSet GetUserEventsByRadius(double latitude, double longitude, string Type, string UserId, int EventId)
    {
        var datetime = DateTime.Now.ToString(format: "MM/dd/yy");
        datetime = datetime.ToString().Replace("-", "/");

        DataSet ds = new DataSet();
        string sqlQuery = "SELECT AdId,AdTitle,Venue,EventDate,StartTime,EndTime, City,altitude,ImageUrl,longitude,AdEventDetail FROM( SELECT top 5 z.UserAdId as AdId, z.Adtitle as AdTitle,x.Venue,x.EventDate,x.StartTime,x.EndTime,z.location,z.imageurl, z.ZipCode,z.City,z.altitude, z.longitude, z.SubcategoryId,z.Categoryid ,";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdEventDetail, p.distance_unit ";
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Altitude)) * COS(RADIANS(p.longpoint - z.longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Altitude)))) AS distance FROM UserAdsdet AS z ";
        sqlQuery += " JOIN [Event_Create] AS x ON x.AdId=z.UserAdId ";
        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.Status is null and z.AdType='" + Type + "' and CONVERT(varchar, x.EventDate, 110) >= CONVERT(date,'" + datetime + "') and z.UserEmailId='" + UserId + "' and z.UserAdId!=" + EventId + "  and z.Altitude 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 AdId 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;
    }

    public DataSet GetRelatedEventsByRadius(double latitude, double longitude, string Type, int CategoryId, int EventId)
    {
        var datetime = DateTime.Now.ToString(format: "MM/dd/yy");
        datetime = datetime.ToString().Replace("-", "/");

        DataSet ds = new DataSet();
        string sqlQuery = "SELECT AdId,AdTitle,Venue,EventDate,StartTime,EndTime, City,altitude,ImageUrl,longitude,AdEventDetail FROM( SELECT top 5 z.UserAdId as AdId, z.Adtitle as AdTitle,x.Venue,x.EventDate,x.StartTime,x.EndTime,z.location,z.imageurl, z.ZipCode,z.City,z.altitude, z.longitude, z.SubcategoryId,z.Categoryid ,";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdEventDetail, p.distance_unit ";
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Altitude)) * COS(RADIANS(p.longpoint - z.longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Altitude)))) AS distance FROM UserAdsdet AS z ";
        sqlQuery += " JOIN [Event_Create] AS x ON x.AdId=z.UserAdId ";
        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.Status is null and CONVERT(varchar, x.EventDate, 110) >= CONVERT(date, '" + datetime + "') and z.AdType='" + Type + "' and x.CategoryId=" + CategoryId + " and z.UserAdId!=" + EventId + "  and z.Altitude 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 AdId 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;
    }

    public DataSet GetEventsByRadius(double latitude, double longitude)
    {
        var datetime = DateTime.Now.ToString(format: "MM/dd/yy");
        datetime = datetime.ToString().Replace("-", "/");
        var dateCriteria = DateTime.Now.Date.AddDays(7);
        DataSet ds = new DataSet();
        string sqlQuery = "SELECT AdId,AdTitle,Venue,EventDate,StartTime,EndTime, City,altitude,ImageUrl,longitude,AdEventDetail FROM( SELECT top 5 z.UserAdId as AdId, z.Adtitle as AdTitle,x.Venue,x.EventDate,x.StartTime,x.EndTime,z.location,z.imageurl, z.ZipCode,z.City,z.altitude, z.longitude, z.SubcategoryId,z.Categoryid ,";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdEventDetail, p.distance_unit ";
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Altitude)) * COS(RADIANS(p.longpoint - z.longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Altitude)))) AS distance FROM UserAdsdet AS z ";
        sqlQuery += " JOIN [Event_Create] AS x ON x.AdId=z.UserAdId ";
        sqlQuery += " JOIN( SELECT " + latitude + " AS latpoint," + longitude + " AS longpoint,200 AS radius, 111.045 AS distance_unit ) AS p ON 1 = 1 ";
        sqlQuery += " WHERE CONVERT(varchar, x.CreatedDate, 110) >= '" + datetime + "' and CONVERT(varchar, x.EventDate, 110) >= '" + dateCriteria + "' and z.Status is null  and z.Altitude 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 AdId 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;
    }

    public DataSet GetThisWeekEventsByRadius(double latitude, double longitude, string Type)
    {

        //var date = DateTime.Now.ToString(format: "yy/MM/dd");
        //date = date.ToString().Replace("-", "/");

        var datetime = DateTime.Now.ToString(format: "MM/dd/yy");
        datetime = datetime.ToString().Replace("-", "/");
        var dateCriteria = DateTime.Now.Date.AddDays(7).ToString(format: "MM/dd/yy");
        dateCriteria = dateCriteria.ToString().Replace("-", "/");

        DataSet ds = new DataSet();
        string sqlQuery = "SELECT AdId,AdTitle,Venue,EventDate,StartTime,EndTime, City,altitude,ImageUrl,longitude,AdEventDetail FROM( SELECT top 5 z.UserAdId as AdId, z.Adtitle as AdTitle,z.location,x.Venue,x.EventDate,x.StartTime,x.EndTime,z.imageurl, z.ZipCode,z.City,z.altitude, z.longitude, z.SubcategoryId,z.Categoryid ,";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdEventDetail, p.distance_unit ";
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Altitude)) * COS(RADIANS(p.longpoint - z.longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Altitude)))) AS distance FROM UserAdsdet AS z ";
        sqlQuery += " JOIN [Event_Create] AS x ON x.AdId=z.UserAdId ";
        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.AdType='" + Type + "' and CONVERT(varchar, x.EventEndDate, 110) <= CONVERT(date,'" + dateCriteria + "') and CONVERT(varchar, x.EventDate, 110) >= '" + datetime + "' and z.Status is null  and z.Altitude 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 AdId 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;
    }

    public DataSet GetUpcomingEventsByRadius(double latitude, double longitude, string Type)
    {
        var datetime = DateTime.Now.ToString(format: "MM/dd/yy");
        datetime = datetime.ToString().Replace("-", "/");

        var dateCriteria = DateTime.Now.Date.AddDays(7).ToString(format: "MM/dd/yy");
        dateCriteria = dateCriteria.ToString().Replace("-", "/");

        DataSet ds = new DataSet();
        string sqlQuery = "SELECT AdId,AdTitle,Venue,EventDate,StartTime,EndTime, City,altitude,ImageUrl,longitude,AdEventDetail FROM( SELECT top 5 z.UserAdId as AdId, z.Adtitle as AdTitle,x.Venue,x.EventDate,x.StartTime,x.EndTime,z.location,z.imageurl, z.ZipCode,z.City,z.altitude, z.longitude, z.SubcategoryId,z.Categoryid ,";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdEventDetail, p.distance_unit ";
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Altitude)) * COS(RADIANS(p.longpoint - z.longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Altitude)))) AS distance FROM UserAdsdet AS z ";
        sqlQuery += " JOIN [Event_Create] AS x ON x.AdId=z.UserAdId ";
        sqlQuery += " JOIN( SELECT " + latitude + " AS latpoint," + longitude + " AS longpoint,200 AS radius, 111.045 AS distance_unit ) AS p ON 1 = 1 ";
        sqlQuery += " WHERE  CONVERT(varchar, x.EventEndDate, 110) > CONVERT(date,'" + dateCriteria + "') and z.AdType='" + Type + "' and z.Status is null  and z.Altitude 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 AdId 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;
    }

    public DataSet GetTicketedEventsByRadius(double latitude, double longitude, int CatId, int SubCatId, string Type)
    {
        var datetime = DateTime.Now.ToString(format: "MM/dd/yy");
        datetime = datetime.ToString().Replace("-", "/");

        DataSet ds = new DataSet();
        string sqlQuery = "SELECT AdId,AdTitle,Venue,EventDate,StartTime,EndTime, City,altitude,ImageUrl,longitude,AdEventDetail FROM( SELECT top 5 z.UserAdId as AdId, z.Adtitle as AdTitle,x.Venue,x.EventDate,x.StartTime,x.EndTime,z.location,z.imageurl, z.ZipCode,z.City,z.altitude, z.longitude, z.SubcategoryId,z.Categoryid ,";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdEventDetail , p.distance_unit";
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Altitude)) * COS(RADIANS(p.longpoint - z.longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Altitude)))) AS distance FROM UserAdsdet AS z ";
        sqlQuery += " JOIN [Event_Create] AS x ON x.AdId=z.UserAdId ";
        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 + " and z.SubCategoryId=" + SubCatId + " and z.AdType='" + Type + "' and x.AdmissionType='Paid Entry' and CONVERT(varchar, x.EventEndDate, 110) >=CONVERT(date, '" + datetime + "')  and z.Status is null  and z.Altitude 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 AdId 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;
    }

    public DataSet GetEventsorgByRadius(double latitude, double longitude, int CatId, int SubCatId, string Type)
    {
        DataSet ds = new DataSet();
        string sqlQuery = "SELECT AdId,OrganizationName,Website,Email,City,altitude,ImageUrl,longitude ,SubCategoryId,CategoryId,AdEventDetail,AdOrganizerDetail,AdSupplierDetail,AdVenueDetail,AdpartyDetail FROM( SELECT top 5 z.UserAdId as AdId, z.Adtitle as AdTitle,z.location,z.imageurl,x.OrganizationName,x.Website,x.Email, z.ZipCode,z.City,z.altitude, z.longitude, z.SubcategoryId,z.Categoryid ,";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdEventDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdOrganizerDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdSupplierDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdVenueDetail , ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdpartyDetail, p.distance_unit ";
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Altitude)) * COS(RADIANS(p.longpoint - z.longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Altitude)))) AS distance FROM UserAdsdet AS z ";
        sqlQuery += " JOIN [Event_Oraganization_Create] AS x ON x.AdId=z.UserAdId ";
        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 + " and z.SubCategoryId=" + SubCatId + " and z.AdType='" + Type + "' and z.Status is null  and z.Altitude 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 AdId 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;
    }

    public DataSet GetUserOrgByRadius(double latitude, double longitude, int CatId, int SubCatId, string Type, string UserId)
    {
        DataSet ds = new DataSet();
        string sqlQuery = "SELECT AdId,OrganizationName,Website,Email,City,altitude,ImageUrl,longitude ,SubCategoryId,CategoryId,AdEventDetail,AdOrganizerDetail,AdSupplierDetail,AdVenueDetail,AdpartyDetail FROM( SELECT top 5 z.UserAdId as AdId, z.Adtitle as AdTitle,z.location,z.imageurl,x.OrganizationName,x.Website,x.Email, z.ZipCode,z.City,z.altitude, z.longitude, z.SubcategoryId,z.Categoryid ,";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdEventDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdOrganizerDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdSupplierDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdVenueDetail , ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdpartyDetail, p.distance_unit ";
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Altitude)) * COS(RADIANS(p.longpoint - z.longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Altitude)))) AS distance FROM UserAdsdet AS z ";
        sqlQuery += " JOIN [Event_Oraganization_Create] AS x ON x.AdId=z.UserAdId ";
        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 + " and z.SubCategoryId=" + SubCatId + " and z.AdType='" + Type + "' and z.UserEmailId='" + UserId + "' and z.Status is null  and z.Altitude 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 AdId 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;
    }

    public DataSet GetProvidersListings(double latitude, double longitude, int catId)
    {
        DataSet ds = new DataSet();
        string sqlQuery = "SELECT  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(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, 250 AS radius, 111.045 AS distance_unit ) AS p ON 1 = 1 ";
        sqlQuery += " WHERE z.CategoryId=" + catId + " 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 <= 250 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;
    }
    public DataSet GetProvidersListings(double latitude, double longitude, int catId, int recordCount)
    {
        DataSet ds = new DataSet();
        string sqlQuery = "SELECT Top " + recordCount + "  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(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, 250 AS radius, 111.045 AS distance_unit ) AS p ON 1 = 1 ";
        sqlQuery += " WHERE z.CategoryId=" + catId + " 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 <= 250 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;
    }



    public DataSet GetLatestEventsByRadius(double latitude, double longitude, string Type)
    {
        var datetime = DateTime.Now.ToString(format: "MM/dd/yy");
        datetime = datetime.ToString().Replace("-", "/");


        var dateCriteria = DateTime.Now.Date.AddDays(-7);


        DataSet ds = new DataSet();
        string sqlQuery = "SELECT AdId,AdTitle,Venue,EventDate,StartTime,EndTime, City,altitude,ImageUrl,longitude,AdEventDetail FROM( SELECT top 20 z.UserAdId as AdId, z.Adtitle as AdTitle,x.Venue,x.EventDate,x.StartTime,x.EndTime,z.location,z.imageurl, z.ZipCode,z.City,z.altitude, z.longitude, z.SubcategoryId,z.Categoryid ,";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdEventDetail, p.distance_unit ";
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Altitude)) * COS(RADIANS(p.longpoint - z.longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Altitude)))) AS distance FROM UserAdsdet AS z ";
        sqlQuery += " JOIN [Event_Create] AS x ON x.AdId=z.UserAdId ";
        sqlQuery += " JOIN( SELECT " + latitude + " AS latpoint," + longitude + " AS longpoint,200 AS radius, 111.045 AS distance_unit ) AS p ON 1 = 1 ";
        sqlQuery += " WHERE CONVERT(varchar, x.CreatedDate, 110) <=CONVERT(date, '" + dateCriteria + "') and  CONVERT(varchar, x.EventEndDate, 110) > '" + datetime + "' and z.AdType='Free' and z.Status is null and z.AdType='" + Type + "'  and z.Altitude 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 AdId 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;
    }

    public DataSet GetSuppliersByRadius(double latitude, double longitude, int CatId, int SubCatId, string Type)
    {
        DataSet ds = new DataSet();
        string sqlQuery = "SELECT AdId, AdTitle,Location, City,altitude,ImageUrl,longitude ,SubCategoryId,CategoryId,AdEventDetail,AdOrganizerDetail,AdSupplierDetail,AdVenueDetail,AdpartyDetail FROM( SELECT top 5 z.UserAdId as AdId, z.Adtitle as AdTitle,z.location,z.imageurl, z.ZipCode,z.City,z.altitude, z.longitude, z.SubcategoryId,z.Categoryid ,";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdEventDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdOrganizerDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdSupplierDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdVenueDetail , ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdpartyDetail, p.distance_unit ";
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Altitude)) * COS(RADIANS(p.longpoint - z.longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Altitude)))) AS distance FROM UserAdsdet AS z ";
        sqlQuery += " JOIN [Event_Supplier_Network] AS x ON x.AdId=z.UserAdId ";
        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 + " and z.SubCategoryId=" + SubCatId + " and z.AdType='" + Type + "' and z.Status is null  and z.Altitude 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 AdId 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;
    }

    public DataSet GetSuppliersByRadius(double latitude, double longitude, int CatId, int SubCatId, string Type, string UserId)
    {
        DataSet ds = new DataSet();
        string sqlQuery = "SELECT AdId, AdTitle,Location, City,altitude,ImageUrl,longitude ,SubCategoryId,CategoryId,AdEventDetail,AdOrganizerDetail,AdSupplierDetail,AdVenueDetail,AdpartyDetail FROM( SELECT top 5 z.UserAdId as AdId, z.Adtitle as AdTitle,z.location,z.imageurl, z.ZipCode,z.City,z.altitude, z.longitude, z.SubcategoryId,z.Categoryid ,";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdEventDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdOrganizerDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdSupplierDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdVenueDetail , ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdpartyDetail, p.distance_unit ";
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Altitude)) * COS(RADIANS(p.longpoint - z.longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Altitude)))) AS distance FROM UserAdsdet AS z ";
        sqlQuery += " JOIN [Event_Supplier_Network] AS x ON x.AdId=z.UserAdId ";
        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 + " and z.SubCategoryId=" + SubCatId + " and z.AdType='" + Type + "' and z.UserEmailId='" + UserId + "' and z.Status is null  and z.Altitude 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 AdId 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;
    }

    public DataSet GetVenueByRadius(double latitude, double longitude, int CatId, int SubCatId, string Type)
    {

        DataSet ds = new DataSet();
        string sqlQuery = "SELECT AdId, AdTitle,Location, City,altitude,ImageUrl,longitude ,SubCategoryId,CategoryId,AdEventDetail,AdOrganizerDetail,AdSupplierDetail,AdVenueDetail,AdpartyDetail FROM( SELECT top 5 z.UserAdId as AdId, z.Adtitle as AdTitle,z.location,z.imageurl, z.ZipCode,z.City,z.altitude, z.longitude, z.SubcategoryId,z.Categoryid ,";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdEventDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdOrganizerDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdSupplierDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdVenueDetail , ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdpartyDetail, p.distance_unit ";
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Altitude)) * COS(RADIANS(p.longpoint - z.longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Altitude)))) AS distance FROM UserAdsdet AS z ";
        sqlQuery += " JOIN [Event_Venue_Managers] AS x ON x.AdId=z.UserAdId ";
        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 + " and z.SubCategoryId=" + SubCatId + " and z.AdType='" + Type + "' and z.Status is null  and z.Altitude 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 AdId 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;
    }

    public DataSet GetVenueByRadius(double latitude, double longitude, int CatId, int SubCatId, string Type, string UserId)
    {

        DataSet ds = new DataSet();
        string sqlQuery = "SELECT AdId, AdTitle,Location, City,altitude,ImageUrl,longitude ,SubCategoryId,CategoryId,AdEventDetail,AdOrganizerDetail,AdSupplierDetail,AdVenueDetail,AdpartyDetail FROM( SELECT top 5 z.UserAdId as AdId, z.Adtitle as AdTitle,z.location,z.imageurl, z.ZipCode,z.City,z.altitude, z.longitude, z.SubcategoryId,z.Categoryid ,";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdEventDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdOrganizerDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdSupplierDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdVenueDetail , ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdpartyDetail, p.distance_unit ";
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Altitude)) * COS(RADIANS(p.longpoint - z.longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Altitude)))) AS distance FROM UserAdsdet AS z ";
        sqlQuery += " JOIN [Event_Venue_Managers] AS x ON x.AdId=z.UserAdId ";
        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 + " and z.SubCategoryId=" + SubCatId + " and z.AdType='" + Type + "' and z.UserEmailId='" + UserId + "' and z.Status is null  and z.Altitude 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 AdId 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;
    }

    public DataSet GetBUsersByRadius(double latitude, double longitude, int catId, int partycatid)
    {
        DataSet ds = new DataSet();
        string sqlQuery = "SELECT 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;
    }






    public DataSet GetAdsByRadius(double latitude, double longitude, int catId, string AdType, int AdId)
    {
        DataSet ds = new DataSet();
        string sqlQuery = "SELECT AdId, AdTitle,Location, City,altitude,ImageUrl,longitude ,SubCategoryId,CategoryId,AdEventDetail,AdOrganizerDetail,AdSupplierDetail,AdVenueDetail,AdpartyDetail FROM( SELECT top 5 z.UserAdId as AdId, z.Adtitle as AdTitle,z.location,z.imageurl, z.ZipCode,z.City,z.altitude, z.longitude, z.SubcategoryId,z.Categoryid ,";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdEventDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdOrganizerDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdSupplierDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdVenueDetail , ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdpartyDetail, p.distance_unit ";
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Altitude)) * COS(RADIANS(p.longpoint - z.longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Altitude)))) AS distance FROM UserAdsdet 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.Status is null and z.categoryid=" + catId + " and  z.UserAdId!=" + AdId + " and z.AdType='" + AdType + "' and z.Altitude 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 AdId 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;
    }

    public DataSet GetAdsByRadius(double latitude, double longitude, int catId, string AdType, int AdId, string UserId)
    {
        DataSet ds = new DataSet();
        string sqlQuery = "SELECT AdId, AdTitle,Location, City,altitude,ImageUrl,longitude ,SubCategoryId,CategoryId,AdEventDetail,AdOrganizerDetail,AdSupplierDetail,AdVenueDetail,AdpartyDetail FROM( SELECT top 5 z.UserAdId as AdId, z.Adtitle as AdTitle,z.location,z.imageurl, z.ZipCode,z.City,z.altitude, z.longitude, z.SubcategoryId,z.Categoryid ,";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdEventDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdOrganizerDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdSupplierDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdVenueDetail , ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdpartyDetail, p.distance_unit ";
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Altitude)) * COS(RADIANS(p.longpoint - z.longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Altitude)))) AS distance FROM UserAdsdet 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.Status is null and z.categoryid=" + catId + " and  z.UserAdId!=" + AdId + " and z.AdType='" + AdType + "' and z.UserEmailId='" + UserId + "' and z.Altitude 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 AdId 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;
    }

    public DataSet GetAdsByRadius(double latitude, double longitude, int catId, int subcatid, string AdType)
    {
        DataSet ds = new DataSet();
        string sqlQuery = "SELECT AdId, AdTitle,Location, City,altitude,ImageUrl,longitude ,SubCategoryId,CategoryId,AdEventDetail,AdOrganizerDetail,AdSupplierDetail,AdVenueDetail,AdpartyDetail FROM( SELECT top 5 z.UserAdId as AdId, z.Adtitle as AdTitle,z.location,z.imageurl, z.ZipCode,z.City,z.altitude, z.longitude, z.SubcategoryId,z.Categoryid ,";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdEventDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdOrganizerDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdSupplierDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdVenueDetail , ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdpartyDetail, p.distance_unit ";
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Altitude)) * COS(RADIANS(p.longpoint - z.longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Altitude)))) AS distance FROM UserAdsdet 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.Status is null and z.categoryid=" + catId + " and  z.subcategoryid=" + subcatid + " and z.AdType='" + AdType + "' and z.Altitude 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 AdId 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;
    }

    public DataSet GetAdsByRadius(double latitude, double longitude, int catId, int subcatid)
    {
        DataSet ds = new DataSet();
        string sqlQuery = "SELECT AdId, AdTitle,Location, City,altitude,ImageUrl,longitude ,SubCategoryId,CategoryId,AdEventDetail,AdOrganizerDetail,AdSupplierDetail,AdVenueDetail,AdpartyDetail FROM( SELECT top 5 z.UserAdId as AdId, z.Adtitle as AdTitle,z.location,z.imageurl, z.ZipCode,z.City,z.altitude, z.longitude, z.SubcategoryId,z.Categoryid ,";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdEventDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdOrganizerDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdSupplierDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdVenueDetail , ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdpartyDetail, p.distance_unit ";
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Altitude)) * COS(RADIANS(p.longpoint - z.longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Altitude)))) AS distance FROM UserAdsdet 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.Status is null and z.categoryid=" + catId + " and  z.subcategoryid=" + subcatid + " and z.Altitude 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 AdId 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;
    }

    public DataSet GetAdsByRadius(double latitude, double longitude, int catId)
    {
        DataSet ds = new DataSet();
        string sqlQuery = "SELECT AdId, AdTitle,Location, City,altitude,ImageUrl,longitude ,SubCategoryId,CategoryId,AdEventDetail,AdOrganizerDetail,AdSupplierDetail,AdVenueDetail,AdpartyDetail FROM( SELECT top 5 z.UserAdId as AdId, z.Adtitle as AdTitle,z.location,z.imageurl, z.ZipCode,z.City,z.altitude, z.longitude, z.SubcategoryId,z.Categoryid ,";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdEventDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdOrganizerDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdSupplierDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdVenueDetail , ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdpartyDetail, p.distance_unit ";
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Altitude)) * COS(RADIANS(p.longpoint - z.longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Altitude)))) AS distance FROM UserAdsdet 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.Status is null and z.categoryid=" + catId + " and z.Altitude 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 AdId 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;
    }
    public DataSet GetAdsByRadius(double latitude, double longitude, int catId, string AdType)
    {
        DataSet ds = new DataSet();
        string sqlQuery = "SELECT AdId, AdTitle,Location, City,altitude,ImageUrl,longitude ,SubCategoryId,CategoryId,AdEventDetail,AdOrganizerDetail,AdSupplierDetail,AdVenueDetail,AdpartyDetail FROM( SELECT top 5 z.UserAdId as AdId, z.Adtitle as AdTitle,z.location,z.imageurl, z.ZipCode,z.City,z.altitude, z.longitude, z.SubcategoryId,z.Categoryid ,";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdEventDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdOrganizerDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdSupplierDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdVenueDetail , ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdpartyDetail, p.distance_unit ";
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Altitude)) * COS(RADIANS(p.longpoint - z.longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Altitude)))) AS distance FROM UserAdsdet 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.Status is null and z.categoryid=" + catId + " and z.AdType='" + AdType + "' and z.Altitude 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 AdId 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;
    }


   

   


    public DataSet GetAdsByRadiusType(double latitude, double longitude, int catId, string AdType)
    {
        DataSet ds = new DataSet();
        string sqlQuery = "SELECT AdId, AdTitle,Location, City,altitude,ImageUrl,longitude ,SubCategoryId,CategoryId,AdEventDetail,AdOrganizerDetail,AdSupplierDetail,AdVenueDetail,AdpartyDetail FROM( SELECT top 5 z.UserAdId as AdId, z.Adtitle as AdTitle,z.location,z.imageurl, z.ZipCode,z.City,z.altitude, z.longitude, z.SubcategoryId,z.Categoryid ,";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdEventDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdOrganizerDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdSupplierDetail, ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdVenueDetail , ";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdpartyDetail, p.distance_unit ";
       
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Altitude)) * COS(RADIANS(p.longpoint - z.longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Altitude)))) AS distance FROM UserAdsdet 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.Status is null and z.categoryid=" + catId + " and z.AdType='" + AdType + "' and z.Altitude 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 AdId 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;
    }


    #endregion



    public void GetLatLong(string strCity, string strState)
    {
        var objC = from objL in dbEmup.cities
                   where objL.name == Convert.ToString(strCity) && objL.state_code == strState
                   select objL;
        city obj = objC.SingleOrDefault();
        salti = Convert.ToString(obj.latitude);
        slong = Convert.ToString(obj.longitude);
        cityCode = Convert.ToString(obj.id);


    }


    #region Party Ads
    public IQueryable GetAds(int intCatId, string adtype)

    {
        var objA = (from objG in dbEmup.UserAdsDets
                    where objG.CategoryId == intCatId && objG.Status == null && objG.AdType == adtype
                    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,
                        ImageURL = objG.ImageUrl,

                        AdpartyDetail = 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;
    }
    public IQueryable GetAds(int intCatId, int intSubCateId, string adType)

    {
        var objA = (from objG in dbEmup.UserAdsDets
                    where objG.CategoryId == intCatId && objG.SubCategoryId == intSubCateId && objG.Status == null && objG.AdType == adType
                    orderby objG.UserAdId descending
                    select new
                    {
                        AdId = objG.UserAdId,
                        SubcategoryId = objG.SubCategoryId,
                        AdTitle = objG.AdTitle,
                        Name = objG.UserDet.Name,
                        Category = objG.CategoryDet.Categoryname,
                        CategoryId = objG.CategoryId,
                        SubCategory = objG.SubCategoryDet.SubCategory,
                        Location = objG.City,
                        City = objG.City,
                        ImageURL = objG.ImageUrl,

                        AdpartyDetail = 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(100);

        return objA;
    }
    public DataSet GetPartyAdsByRadius(double latitude, double longitude, int catId, int subcatid, string AdType)
    {
        DataSet ds = new DataSet();
        string sqlQuery = "SELECT AdId, AdTitle,Location, City,altitude,ImageUrl,longitude ,SubCategoryId,CategoryId,AdpartyDetail FROM( SELECT top 5 z.UserAdId as AdId, z.Adtitle as AdTitle,z.location,z.imageurl, z.ZipCode,z.City,z.altitude, z.longitude, z.SubcategoryId,z.Categoryid ,";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE((REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdpartyDetail , p.distance_unit";
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Altitude)) * COS(RADIANS(p.longpoint - z.longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Altitude)))) AS distance FROM UserAdsdet 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.Status is null and z.categoryid=" + catId + " and  z.subcategoryid=" + subcatid + " and z.AdType='" + AdType + "' and z.Altitude 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 AdId 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;
    }
    public DataSet GetPartyAdsByRadiusType(double latitude, double longitude, int catId, string AdType)
    {
        DataSet ds = new DataSet();
        string sqlQuery = "SELECT AdId, AdTitle,Location, City,altitude,ImageUrl,longitude ,SubCategoryId,CategoryId,AdpartyDetail FROM( SELECT top 5 z.UserAdId as AdId, z.Adtitle as AdTitle,z.location,z.imageurl, z.ZipCode,z.City,z.altitude, z.longitude, z.SubcategoryId,z.Categoryid ,";
        sqlQuery += "(REPLACE(z.City,',','-'))+'-'+ concat (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(z.adtitle,'!', ''), '''', ''), '@', ''), '#', ''), '$', ''),'%',''),'^',''),'&',''),'*',''),'(',''),'-',''), '_',''),':',''),';',''),'',''),',',''),'.',''),'<',''),'>',''),'?',''),'/','')+ '-', ";
        sqlQuery += " z.useradid) AS AdpartyDetail , p.distance_unit";
        sqlQuery += " * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.Altitude)) * COS(RADIANS(p.longpoint - z.longitude)) ";
        sqlQuery += " + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.Altitude)))) AS distance FROM UserAdsdet 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.Status is null and z.categoryid=" + catId + " and z.AdType='" + AdType + "' and z.Altitude 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 AdId 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;
    }
    #endregion


}