using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace EnvigoHiring.Models.Designation
{
    public class Designation
    {
        public int DesignationID { get; set; }
        [Required(ErrorMessage = "Enter Designation Name!")]
        public string DesignationName { get; set; }

        public List<SelectListItem> Department  { get; set; }
        [Required(ErrorMessage = "Please select Department!")]
        public string DepartmentID { get; set; }

        public string MessageType { get; set; }
        public string lblMessage { get; set; }
        public DataTable ListData { get; set; }

        DataLayer obj = DataLayer.Instance;

        public DataTable EditData()
        {
            string query = string.Format(@"SELECT tblMasterDesignation.DesignationID, tblMasterDesignation.DesignationName, tblMasterDepartment.DepartmentID
                                        FROM tblMasterDesignation INNER JOIN tblMasterDepartment ON tblMasterDesignation.DepartmentID = tblMasterDepartment.DepartmentID
						                where tblMasterDesignation.DesignationID={0}",DesignationID);
           return  obj.SelectDatatable(query);
        }

        public List<SelectListItem> GetDepartment()
        {
            string query = string.Format(@"SELECT * from tblMasterDepartment order by DepartmentName asc");
            DataTable dt = obj.SelectDatatable(query);
            List<SelectListItem> dep = new List<SelectListItem>();
            foreach (DataRow temp in dt.Rows)
            {
                dep.Add(new SelectListItem() { Text = temp["DepartmentName"].ToString(), Value = temp["DepartmentID"].ToString() });
            }
            var depname = new SelectListItem()
            {
                Value = null,
                Text = "--- select Department ---"
            };
            dep.Insert(0, depname);
            return dep;
        }
        public DataTable getdata()
        {
            string query = string.Format(@"SELECT tblMasterDesignation.DesignationID, tblMasterDesignation.DesignationName, tblMasterDepartment.DepartmentName
                                        FROM tblMasterDesignation INNER JOIN tblMasterDepartment ON tblMasterDesignation.DepartmentID = tblMasterDepartment.DepartmentID
						                order by DesignationName asc");
            return obj.SelectDatatable(query);
        }


        public bool CheckDesignation(int DesID)
        {
            string Query = "";
            if (DesID == 0)
            {
                Query = string.Format(@"select DesignationName from tblMasterDesignation where DesignationName= '" + HttpUtility.HtmlEncode(DesignationName.Trim().ToString()) + "' and DepartmentID=" + DepartmentID + "");
            }
            else
            {
                Query = string.Format(@"select DesignationName from tblMasterDesignation where DesignationName= '" + HttpUtility.HtmlEncode(DesignationName.Trim().ToString()) + "' and DepartmentID=" + DepartmentID + " and DesignationID !=" + DesID + "");
            }
            DataTable dt = obj.SelectDatatable(Query);
            if (dt.Rows.Count > 0) { return true; } else { return false; }
        }
        public int add()
        {
            string query = string.Format(@"insert into tblMasterDesignation (DesignationName, DepartmentID) values('"+HttpUtility.HtmlEncode(DesignationName.Trim().ToString()) + "', " + DepartmentID + ")");
            int i = obj.ExecuteNonQuery(query);
            return i;

        }

        public int edit()
        {
            string query = string.Format(@"update tblMasterDesignation set DesignationName ='" + HttpUtility.HtmlEncode(DesignationName.Trim().ToString()) + "',DepartmentID =  " + DepartmentID + " where DesignationID="+DesignationID);
            int i = obj.ExecuteNonQuery(query);
            return i;

        }
    }
}