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


public class logic
{
    SqlConnection cn;
    SqlCommand cmd;
    //SqlDataReader dr;
    public logic()
    {
        string cs;
        cs = ConfigurationManager.ConnectionStrings["eofficedbcs"].ConnectionString;
        cn = new SqlConnection(cs);
        cmd = new SqlCommand();
        cmd.Connection = cn;
        cmd.CommandType = CommandType.Text;
    }
    public bool addUser(string name, string mail, int id, string password)
    {
        try
        {
            cmd.CommandText = string.Format("insert into users (name,mail,id,password) values('{0}','{1}',{2},'{3}')", name, mail, id, password);
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
            return true;
        }
        catch
        {

            return false;
        }
    }

    public bool addManager(string name, string mail, int id, string password)
    {
        try
        {
            cmd.CommandText = string.Format("insert into manager (name,mail,id,password) values('{0}','{1}',{2},'{3}')", name, mail, id, password);
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
            return true;
        }
        catch
        {

            return false;
        }
    }

    public bool addProject(string pname,int work_id, int assign_to, string start_date, string desc, string priority, string end_date, int assign_by,int no_lines,string docs)
    {
        try
        {

            
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            cmd.CommandText = string.Format("insert into projects (proj_name,assigned_to,start_date,description,priority,end_date,assigned_by,no_of_lines,doc,proj_id) values('{0}',{1},'{2}','{3}','{4}','{5}',{6},{7},'{8}',{9})", pname, assign_to, start_date, desc, priority, end_date, assign_by, no_lines, docs,work_id);
            cmd.ExecuteNonQuery();
            cn.Close();
            return true;
       }
       catch
        {
              return false;
        }
    }


    public bool login_valid(int id, string pwd)
    {
        try
        {
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            cmd.CommandText = string.Format("select count(*) from manager where id={0} and password='{1}'", id, pwd);
            int val = (Int32)cmd.ExecuteScalar();
            if (val == 1)
            {
                return true;
            }
            else { return false; }
        }
        catch { return false; }
    }

    public bool user_login_valid(int id, string pwd)
    {
        try
        {
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            cmd.CommandText = string.Format("select count(*) from users where id={0} and password='{1}'", id, pwd);
            int val = (Int32)cmd.ExecuteScalar();
            if (val == 1)
            {
                return true;
            }
            else { return false; }
        }
        catch { return false; }
    }

    public bool manager_exists(string to)
    {
        try
        {
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            cmd.CommandText = string.Format("select count(*) from manager where mail='{0}'", to);
            int val = (Int32)cmd.ExecuteScalar();
            if (val == 1)
            {
                return true;
            }
            else { return false; }
        }
        catch { return false; }
    }


    public bool user_exists(string to)
    {
        try
        {
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            cmd.CommandText = string.Format("select count(*) from users where mail='{0}'", to);
            int val = (Int32)cmd.ExecuteScalar();
            if (val == 1)
            {
                return true;
            }
            else { return false; }
        }
        catch { return false; }
    }

    public bool manager_messg_send(string to, string subj, string messg, string by)
    {
        try
        {
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            cmd.CommandText = string.Format("insert into messages (messg_to,messg_by,message,subj,date_sent) values ('{0}','{1}','{2}','{3}','{4}')", to, by, messg, subj, DateTime.Now.ToString());
            cmd.ExecuteNonQuery();
            cn.Close();
            return true;
        }
        catch
        {

            return false;
        }

    }

    public bool user_messg_send(string to, string subj, string messg, string by)
    {
        try
        {
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            cmd.CommandText = string.Format("insert into messages (messg_to,messg_by,message,subj,date_sent) values ('{0}','{1}','{2}','{3}','{4}')", to, by, messg, subj, DateTime.Now.ToString());
            cmd.ExecuteNonQuery();
            cn.Close();
            return true;
        }
        catch
        {

            return false;
        }

    }

    public bool edit_manager_name(string new_name, int id)
    {
        try
        {
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            cmd.CommandText = string.Format("update manager set name='{0}' where id={1}", new_name, id);
            cmd.ExecuteNonQuery();
            cn.Close();
            return true;
        }
        catch
        {

            return false;
        }
    }

    public bool edit_password(string new_pwd, int id)
    {
        try
        {
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            cmd.CommandText = string.Format("update manager set password='{0}' where id={1}", new_pwd, id);
            cmd.ExecuteNonQuery();
            cn.Close();
            return true;
        }
        catch
        {

            return false;
        }
    }


    public bool edit_mail(string new_mail, int id)
    {
        try
        {
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            cmd.CommandText = string.Format("update manager set mail='{0}' where id={1}", new_mail, id);
            cmd.ExecuteNonQuery();
            cn.Close();
            return true;
        }
        catch
        {

            return false;
        }
    }







    public bool edit_user_name(string new_name, int id)
    {
        try
        {
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            cmd.CommandText = string.Format("update users set name='{0}' where id={1}", new_name, id);
            cmd.ExecuteNonQuery();
            cn.Close();
            return true;
        }
        catch
        {

            return false;
        }
    }

    public bool edit_user_password(string new_pwd, int id)
    {
        try
        {
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            cmd.CommandText = string.Format("update users set password='{0}' where id={1}", new_pwd, id);
            cmd.ExecuteNonQuery();
            cn.Close();
            return true;
        }
        catch
        {

            return false;
        }
    }


    public bool edit_user_mail(string new_mail, int id)
    {
        try
        {
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            cmd.CommandText = string.Format("update users set mail='{0}' where id={1}", new_mail, id);
            cmd.ExecuteNonQuery();
            cn.Close();
            return true;
        }
        catch
        {

            return false;
        }


    }

    

    public bool user_approval_messg(string to,string subj,string messg,string by)
    {
        //try
        //{
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            cmd.CommandText = string.Format("insert into messages (messg_to,messg_by,message,subj,date_sent) values ('{0}','{1}','{2}','{3}','{4}')", to, by, messg, subj, DateTime.Now.ToString());
            cmd.ExecuteNonQuery();
            cn.Close();
            return true;
        //}
       // catch
        //{

         //   return false;
        //}
    }

    public string get_mail_id(int to)
    {
        try
        {
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            cmd.CommandText = string.Format("select mail from users where id={0}", to);
            string val = cmd.ExecuteScalar().ToString();
            cn.Close();
            return val;
            
        }
        catch { 
            string a="";
            return a;

        }
        
    }
    public string get_manager_mail_id(int by)
    {
        try
        {
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            cmd.CommandText = string.Format("select mail from manager where id={0}", by);
            string val = cmd.ExecuteScalar().ToString();
            cn.Close();
            return val;
            
        }
        catch
        {
            string a = "";
            return a;

        }

    }

    public string ret_pwd(int id)
    {
        try
        {
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            cmd.CommandText = string.Format("select password from manager where id={0}", id);
            string val = cmd.ExecuteScalar().ToString();
            cn.Close();
            return val;

        }
        catch
        {
            string a = "";
            return a;

        }

    }

    

}