using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Web;

namespace Phonebook
{
    public class DataQuery
    {
        public string insert(int getID, String getName, String getLName, String getPhone, String getEmail, int getGentype, String getCountry, string getDate)
        {
            var datasource = @"server";
            string connString = "Data Source=" + datasource + ";Initial Catalog=dbname;Persist Security Info=True;User Id=username;Password=password;";

            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand();

            cmd.Connection = conn;
            conn.Open();

            SqlParameter parmid = new SqlParameter("IDp", SqlDbType.Int);
            parmid.Value = getID;

            SqlParameter parmname = new SqlParameter("Namep", SqlDbType.VarChar);
            parmname.Value = getName;

            SqlParameter parmlname = new SqlParameter("LNamep", SqlDbType.VarChar);
            parmlname.Value = getLName;

            SqlParameter parmphone = new SqlParameter("Phonep", SqlDbType.VarChar);
            parmphone.Value = getPhone;

            SqlParameter parmemail = new SqlParameter("Emailp", SqlDbType.VarChar);
            parmemail.Value = getEmail;

            SqlParameter parmgentype = new SqlParameter("Gentypep", SqlDbType.Int);
            parmgentype.Value = getGentype;

            SqlParameter parmcountry = new SqlParameter("Countryp", SqlDbType.VarChar);
            parmcountry.Value = getCountry;

            SqlParameter parmbdate = new SqlParameter("Bdatep", SqlDbType.Date);
            parmbdate.Value = getDate;

            cmd.Parameters.Add(parmid);
            cmd.Parameters.Add(parmname);
            cmd.Parameters.Add(parmlname);
            cmd.Parameters.Add(parmphone);
            cmd.Parameters.Add(parmemail);
            cmd.Parameters.Add(parmgentype);
            cmd.Parameters.Add(parmcountry);
            cmd.Parameters.Add(parmbdate);
            cmd.Parameters.Add("out_id", SqlDbType.Int).Direction = ParameterDirection.Output;

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.CommandText = @"insUpd";

            cmd.ExecuteNonQuery();

            conn.Dispose();

            return "";
        }

        public string delete(string getID)
        {
            var datasource = @"server";
            string connString = "Data Source=" + datasource + ";Initial Catalog=dbname;Persist Security Info=True;User Id=username;Password=password;";

            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand();
            DataTable dt = new DataTable();

            conn.Open();

            SqlParameter parm = new SqlParameter("originalid", SqlDbType.Int);
            parm.Value = getID;

            cmd.Connection = conn;
            cmd.Parameters.Add(parm);

            cmd.CommandText = "DELETE from Contacts WHERE ID = '" + getID + "'";
            cmd.CommandType = CommandType.Text;

            cmd.ExecuteNonQuery();

            conn.Dispose();

            return "";
        }

        public DataTable getDataTableView(int CurrentPage, int _pageSize, string searchBox, string favClause, string genClause, string dropDown)
        {
            var datasource = @"server";
            string connString = "Data Source=" + datasource + ";Initial Catalog=dbname;Persist Security Info=True;User Id=username;Password=password;";

            SqlConnection conn = new SqlConnection(connString);
            DataTable dt = new DataTable();

            conn.Open();

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;

            SqlParameter parm1 = new SqlParameter("startrow", SqlDbType.Int);
            parm1.Value = 1 + ((CurrentPage - 1) * Convert.ToInt32(dropDown));

            SqlParameter parm2 = new SqlParameter("endrow", SqlDbType.Int);
            parm2.Value = CurrentPage * Convert.ToInt32(dropDown);

            cmd.Parameters.Add(parm1);
            cmd.Parameters.Add(parm2);

            cmd.CommandType = CommandType.Text;

            cmd.CommandText = @"select * from (select *, ROW_NUMBER() OVER(ORDER BY Name asc, ID asc, favourites desc) as Ro from Contacts_view where " + favClause + " " + genClause + " + Name like '%" + searchBox + "%')s where s.ro >= @startrow and s.ro <= @endrow;";

            dt.Load(cmd.ExecuteReader());

            conn.Dispose();

            return dt;
        }

        public DataTable getDataById(string id)
        {
            DataTable dt = new DataTable();

            var datasource = @"server";
            string connString = "Data Source=" + datasource + ";Initial Catalog=dbname;Persist Security Info=True;User Id=username;Password=password;";

            SqlConnection conn = new SqlConnection(connString);

            conn.Open();

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;

            cmd.CommandText = "select * from Contacts c, Contacts_2 p where c.ID = " + id + " and c.ID = p.con_id";

            dt.Load(cmd.ExecuteReader());

            conn.Dispose();

            return dt;
        }

        public string MakeFavourite(int id, int favourite)
        {
            var datasource = @"server";
            string connString = "Data Source=" + datasource + ";Initial Catalog=dbname;Persist Security Info=True;User Id=username;Password=password;";

            SqlConnection conn = new SqlConnection(connString);

            conn.Open();

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;

            SqlParameter parmone = new SqlParameter("id", SqlDbType.Int);
            parmone.Value = id;

            SqlParameter parmtwo = new SqlParameter("favourite", SqlDbType.Int);
            parmtwo.Value = favourite;

            cmd.Parameters.Add(parmone);
            cmd.Parameters.Add(parmtwo);

            cmd.CommandText = @"Update Contacts set favourites = @favourite where ID = @ID";

            cmd.ExecuteNonQuery();
            conn.Dispose();

            return "";
        }

        public Int32 pageCount()
        {
            var datasource = @"server";
            string connString = "Data Source=" + datasource + ";Initial Catalog=dbname;Persist Security Info=True;User Id=username;Password=password;";

            SqlConnection conn = new SqlConnection(connString);
            conn.Open();

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;

            cmd.CommandText = "SELECT COUNT(*) FROM Contacts_view";

            var pageCount = (Int32)cmd.ExecuteScalar();
            conn.Dispose();

            return pageCount;
        }
    }
}