using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;

using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Text;
/// <summary>
/// Summary description for WsAutoFill
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
[System.Web.Script.Services.ScriptService]
public class WsAutoFill : System.Web.Services.WebService
{

    public WsAutoFill()
    {

        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }

    [WebMethod]
    public string HelloWorld()
    {
        return "Hello World";
    }


    [WebMethod]
    public string[] GetSubCategoryList(string prefixText)
    {




        DataSet dsCity = new DataSet();
        SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        string strSql = "select SubCategory from SubcategoryDet where subcategory like" + "'" + prefixText + "%'";
        // string strSql = "select categorydesc from categorydet ";
        // string strSql = "Select firstname from userdet";
        SqlCommand sqlComd = new SqlCommand(strSql, sqlCon);
        sqlCon.Open();
        SqlDataAdapter sqlAdpt = new SqlDataAdapter();
        sqlAdpt.SelectCommand = sqlComd;
        sqlAdpt.Fill(dsCity);
        List<string> list = new List<string>(dsCity.Tables[0].Rows.Count);
        foreach (DataRow dr in dsCity.Tables[0].Rows)
        {
            list.Add(AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(dr["SubCategory"].ToString(), dr[1].ToString()));
        }
        sqlCon.Close();
        return list.ToArray();
    }


    // retreiving city, state for autofill in textbox
    //public string[] GetCityList(string strPrefix, int intCount)
    //{
    //    intCount = 30;
    //    DataTable dt = GetRecords(strPrefix);
    //    List<string> items = new List<string>(intCount);

    //    for (int i = 0; i < dt.Rows.Count; i++)
    //    {
    //        string strName = dt.Rows[i][0].ToString();
    //        items.Add(strName);
    //    }
    //    return items.ToArray();

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

        using (conSql = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].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 ";
                cmd.CommandText = "select subcategory from subcategorydet";
                DataSet objDs = new DataSet();
                SqlDataAdapter dAdapter = new SqlDataAdapter();
                dAdapter.SelectCommand = cmd;
                conSql.Open();
                dAdapter.Fill(objDs);
                conSql.Close();
                return objDs.Tables[0];

            }

        }
    }



    [WebMethod]
    public string[] GetCountryCityList(string prefixText, string contextKey)
    {

        DataSet dsCity = new DataSet();
        SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

        string strSql = "select distinct name,state_code,id   from cities where county=" + "'" + contextKey + "'" + " and name like" + "'" + prefixText + "%'";
        SqlCommand sqlComd = new SqlCommand(strSql, sqlCon);
        sqlCon.Open();
        SqlDataAdapter sqlAdpt = new SqlDataAdapter();
        sqlAdpt.SelectCommand = sqlComd;
        sqlAdpt.Fill(dsCity);
        List<string> list = new List<string>(dsCity.Tables[0].Rows.Count);
        foreach (DataRow dr in dsCity.Tables[0].Rows)
        {

            list.Add(AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(dr["name"].ToString() + "," + dr["state_code"].ToString(), dr["id"].ToString()));
        }
        if (list.Count == 0)
        {
            list.Add("No city found");
        }
        sqlCon.Close();
        return list.ToArray();

        #region WORKING CODE
        //DataSet dsCity = new DataSet();
        //SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

        //string strSql = "select  name,state_code,id   from cities where name like" + "'" + prefixText + "%'";
        // SqlCommand sqlComd = new SqlCommand(strSql, sqlCon);
        //sqlCon.Open();
        //SqlDataAdapter sqlAdpt = new SqlDataAdapter();
        //sqlAdpt.SelectCommand = sqlComd;
        //sqlAdpt.Fill(dsCity);
        //List<string> list = new List<string>(dsCity.Tables[0].Rows.Count);
        //foreach (DataRow dr in dsCity.Tables[0].Rows)
        //{

        //    list.Add(AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(dr["name"].ToString() , dr["id"].ToString()));
        //}
        //sqlCon.Close();
        //return list.ToArray();
        #endregion
    }





    [WebMethod]
    public string[] GetCityList(string prefixText)
    {

        DataSet dsCity = new DataSet();
        SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

        string strSql = "select distinct name,state_code,id   from cities where name like" + "'" + prefixText + "%'";
        SqlCommand sqlComd = new SqlCommand(strSql, sqlCon);
        sqlCon.Open();
        SqlDataAdapter sqlAdpt = new SqlDataAdapter();
        sqlAdpt.SelectCommand = sqlComd;
        sqlAdpt.Fill(dsCity);
        List<string> list = new List<string>(dsCity.Tables[0].Rows.Count);
        foreach (DataRow dr in dsCity.Tables[0].Rows)
        {

            list.Add(AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(dr["name"].ToString() + "," + dr["state_code"].ToString(), dr["id"].ToString()));
        }
        if (list.Count == 0)
        {
            list.Add("No city found");
        }
        sqlCon.Close();
        return list.ToArray();

        #region WORKING CODE
        //DataSet dsCity = new DataSet();
        //SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

        //string strSql = "select  name,state_code,id   from cities where name like" + "'" + prefixText + "%'";
        // SqlCommand sqlComd = new SqlCommand(strSql, sqlCon);
        //sqlCon.Open();
        //SqlDataAdapter sqlAdpt = new SqlDataAdapter();
        //sqlAdpt.SelectCommand = sqlComd;
        //sqlAdpt.Fill(dsCity);
        //List<string> list = new List<string>(dsCity.Tables[0].Rows.Count);
        //foreach (DataRow dr in dsCity.Tables[0].Rows)
        //{

        //    list.Add(AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(dr["name"].ToString() , dr["id"].ToString()));
        //}
        //sqlCon.Close();
        //return list.ToArray();
        #endregion
    }
}