using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data.SqlClient;
using System.Data;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class Service : System.Web.Services.WebService
{

    SqlConnection con = new SqlConnection("Data Source=COIGN-PC\\SQLEXPRESS;database=HolidayTrip;Integrated Security=true");
    SqlCommand cmd;
    SqlDataAdapter adp;
    DataSet ds;
    public Service () {

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

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

    [WebMethod]
    public string InsertUser(string fname, string lname, string sex, int age, string padd, string cadd, string email, string phn, string mob, string uname, string pwd)
    {
        try
        {
            con.Open();
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_InserUser";
            cmd.Parameters.AddWithValue("@fname", fname);
            cmd.Parameters.AddWithValue("@lname", lname);
            cmd.Parameters.AddWithValue("@sex", sex);
            cmd.Parameters.AddWithValue("@age", age);
            cmd.Parameters.AddWithValue("@padd", padd);
            cmd.Parameters.AddWithValue("@cadd", cadd);
            cmd.Parameters.AddWithValue("@email", email);
            cmd.Parameters.AddWithValue("@phn", phn);
            cmd.Parameters.AddWithValue("@mob", mob);
            cmd.Parameters.AddWithValue("@uname", uname);
            cmd.Parameters.AddWithValue("@pwd", pwd);
            int i = cmd.ExecuteNonQuery();
            con.Close();
            if (i > 0)
            {
                return "True";
            }
            else
                return "False";
        }
        catch 
        {
            return "False";
        }
        
    }

    [WebMethod]
    public string LoginUser(string uname, string pwd)
    {
        con.Open();
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "SP_LoginUser";

        cmd.Parameters.AddWithValue("@uname", uname);
        cmd.Parameters.AddWithValue("@pwd", pwd);
        string UserType = cmd.ExecuteScalar().ToString();
        con.Close();
        return UserType;
    }

    [WebMethod]
    public string InsertCountry(string Cname, string description)
    {
        con.Open();
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "SP_InsertCountryDetails";
        cmd.Parameters.AddWithValue("@Cname", Cname);
        cmd.Parameters.AddWithValue("@description", description);
        int i = cmd.ExecuteNonQuery();
        con.Close();
        if (i > 0)
            return "True";
        else
            return "False";
    }

    [WebMethod]
    public string InsertCity(int CountryId, string CityName, string description)
    {
        try
        {
          
            con.Open();
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_CityDetails";
            cmd.Parameters.AddWithValue("@CountryId", CountryId);
            cmd.Parameters.AddWithValue("@CityName", CityName);
            cmd.Parameters.AddWithValue("@description", description);
            int i = cmd.ExecuteNonQuery();
            con.Close();
            if (i > 0)
                return "True";
            else
                return "False";
        }
        catch(Exception ex)
        {
            return "False";
        }
    }
    
    
    [WebMethod]
    public string getcountry()
    {
        con.Open();
        adp = new SqlDataAdapter("select * from Tbl_CountryDetails", con);
        ds = new DataSet();
        adp.Fill(ds, "Country");
        return ds.GetXml();
    }
    
    
    [WebMethod]
    public string InsertHotel(int CityId, string HotelName, string HotelType, int NoOfBed, float Costforadult, float Costforchild, int CountryId)
    {
        try
        {
            con.Open();
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_HotelDetails";
            cmd.Parameters.AddWithValue("@CityId", CityId);
            cmd.Parameters.AddWithValue("@HotelName", HotelName);
            cmd.Parameters.AddWithValue("@HotelType", HotelType);
            cmd.Parameters.AddWithValue("@NoOfBed", NoOfBed);
            cmd.Parameters.AddWithValue("@Costforadult", Costforadult);
            cmd.Parameters.AddWithValue("@Costforchild", Costforchild);
            cmd.Parameters.AddWithValue("@CountryId", CountryId);

            int i = cmd.ExecuteNonQuery();
            con.Close();

            if (i > 0)
                return "True";
            else
                return "False";
        }
        catch
        {
            return "False";
        }
    }

    [WebMethod]
    public string PhotoDetails(int CityId, string PhotoName, string description)
    {
        con.Open();
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "SP_PhotoDetails";
        cmd.Parameters.AddWithValue("@CityId", CityId);
        cmd.Parameters.AddWithValue("@PhotoName", PhotoName);
        cmd.Parameters.AddWithValue("@description", description);

        int i = cmd.ExecuteNonQuery();
        con.Close();

        if (i > 0)
            return "Photo Details Inserted...";
        else
            return "Insertion Failed...";
    }


    [WebMethod]
    public string VideoDetails(int CityId, string VideoName, string Description)
    {
        con.Open();
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "SP_videoStore";
        cmd.Parameters.AddWithValue("@CityId", CityId);
        cmd.Parameters.AddWithValue("@VideoName", VideoName);
        cmd.Parameters.AddWithValue("@Description", Description);

        int i = cmd.ExecuteNonQuery();
        con.Close();

        if (i > 0)
            return "Video Details Inserted...";
        else
            return "Insertion Failed....";
    }


    [WebMethod]
    public string GetCityNames(int CountryID)
        {
        con.Open();
        adp = new SqlDataAdapter("select * from Tbl_CityDetails where CountryId='" + CountryID + "'", con);
        ds = new DataSet();
        adp.Fill(ds, "CityNames");
        con.Close();
        return ds.GetXml();
    }

    [WebMethod]
    public string GetHotels(int CityId)
    {
        con.Open();
        adp = new SqlDataAdapter("select * from Tbl_HotelDetails where  CityId='" + CityId + "'", con);
        ds = new DataSet();
        adp.Fill(ds,"Hotels");
        con.Close();
        return ds.GetXml();
    }

    [WebMethod]
    public string GetUserDetails(int Uid)
    {
        con.Open();
        adp = new SqlDataAdapter("select * from Tbl_UserDetails where UserId='" + Uid + "'", con);
        ds = new DataSet();
        adp.Fill(ds, "User");
        con.Close();
        return ds.GetXml();
    }

    [WebMethod]
    public string Payment(string uname, string cityid, string hid, string cardtype, string cno, string holdername, string expdate, string cvvno, string payment,string total)
    {
        con.Open();
        cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = con;
        cmd.CommandText = "SP_Payment";
        SqlCommand cmd1 = new SqlCommand("select UserId from Tbl_UserDetails where UserName='" + uname + "'",con);
        int uid = int.Parse(cmd1.ExecuteScalar().ToString());
        cmd.Parameters.AddWithValue("@UserId", uid);
        cmd.Parameters.AddWithValue("@CityId",int.Parse(cityid));
        cmd.Parameters.AddWithValue("@HotelId",int.Parse(hid));
        cmd.Parameters.AddWithValue("@CardType", cardtype);
        cmd.Parameters.AddWithValue("@CardNo", cno);
        cmd.Parameters.AddWithValue("@HolderName", holdername);
        cmd.Parameters.AddWithValue("@expdate", expdate);
        cmd.Parameters.AddWithValue("@cvvno", cvvno);
        cmd.Parameters.AddWithValue("@paieddate",payment);
        cmd.Parameters.AddWithValue("@Total", total);


        int i = int.Parse(cmd.ExecuteNonQuery().ToString());
        if (i > 0)
            return "True";
        else
            return "False";
    }


    [WebMethod]
    public string getcityandcountry()
    {
        con.Open();
        return "";
    }
    
    [WebMethod]
    public string insertImgNames(string imgName)
    {
        if (con.State == ConnectionState.Open)
            con.Close();
        con.Open();

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "insert into Tbl_Files values('" + imgName + "','Image')";

        int i = cmd.ExecuteNonQuery();
        if (i > 0)
            return "Inserted successfully";
        else
            return "Not inserted";

    }
    [WebMethod]
    public string insertVideoNames(string imgName)
    {
        if (con.State == ConnectionState.Open)
            con.Close();
        con.Open();

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "insert into Tbl_Files values('" + imgName + "','Video')";

        int i = cmd.ExecuteNonQuery();
        if (i > 0)
            return "Inserted successfully";
        else
            return "Not inserted";

    }

    [WebMethod]
    public string GetVideoPath(int CityID)
    {
        if (con.State == ConnectionState.Closed)
            con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "select VideoName from Tbl_videodetails where CityId='" + CityID + "'";
        string name = cmd.ExecuteScalar().ToString();
        string finalname = Server.MapPath("~\\Videos\\") + name;

        return finalname;
    }

    public DataSet GetPhotoDs(int CityID)
    {
        if (con.State == ConnectionState.Closed)
            con.Open();
        SqlCommand cmd = new SqlCommand();
        DataSet ds = new DataSet();
        SqlDataAdapter adp = new SqlDataAdapter(cmd);

        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "select * from Tbl_PhotoDetails where CityId='" + CityID + "'";
        adp.Fill(ds);
        return ds;
    }

    [WebMethod]
    public string GetPhotoName(int CityID, int pos)
    {
        string fname = "";
        DataSet ds = GetPhotoDs(CityID);
        if (ds.Tables[0].Rows.Count > 0)
        {
            string n = ds.Tables[0].Rows[pos]["PhotoName"].ToString();
            fname = Server.MapPath("~\\Images\\") + n;
        }
        return fname;
    }

    [WebMethod]
    public string GetTotal(int hid, int noofA, int noofC)
    {
        if (con.State == ConnectionState.Closed)
            con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "select * from Tbl_HotelDetails where HotelId='" + hid + "'";
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        sda.Fill(ds);
        float total=0;
        
            float a;
            float b;

            a = Convert.ToSingle(ds.Tables[0].Rows[0]["CostForBed"].ToString());
            b = Convert.ToSingle(ds.Tables[0].Rows[0]["CostForChildren"].ToString());
            total = (a * noofA) + (b * noofC);  

           
          return total.ToString ();
        
 
      

    }
}
