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

namespace EnvigoHiring.Models.Applicants
{
    public class Display
    {
        public int ApplicantID { get; set; }
        public DataTable ApplicantData { get; set; }
        public DataTable ApplicantRounds { get; set; }
        public DataTable RoundsRating { get; set; }

        public string MessageType { get; set; }
        public string lblMessage { get; set; }
        public List<SelectListItem> UserList { get; set; }
        [Required(ErrorMessage = "Please select Interviewer!")]
        public int UserID { get; set; }

        [Required(ErrorMessage = "Please select Interview Date!")]
        public string sheduledOn { get; set; }
        public string RoundType { get; set; }
        public string RoundID { get; set; }

        DataLayer obj = DataLayer.Instance;
        public DataTable GetApplicantData()
        {
            string ApplicantDataQuery = string.Format(@"SELECT tblMasterCandidatePool.*, tblApplicants.*, tblMasterUnit.UnitName, tblMasterDesignation.DesignationName
FROM tblApplicants INNER JOIN
                         tblMasterCandidatePool ON tblApplicants.CandidateID = tblMasterCandidatePool.CandidateID INNER JOIN
                         tblPositions ON tblApplicants.PositionID = tblPositions.PositionID INNER JOIN
                         tblMasterUnit ON tblPositions.Unit = tblMasterUnit.unitID INNER JOIN
                         tblMasterDesignation ON tblPositions.DesignationID = tblMasterDesignation.DesignationID

 where ApplicantID = {0}", ApplicantID);
            return obj.SelectDatatable(ApplicantDataQuery);
        }

        public DataTable GetApplicantRounds()
        {
            string ApplicantDataQuery = string.Format(@"SELECT        tblInterviewRounds.RoundID,tblInterviewRounds.rating, tblInterviewRounds.ApplicantID, tblInterviewRounds.CandidateID, tblInterviewRounds.RoundType, tblInterviewRounds.SheduledOn, tblInterviewRounds.createdate, tblInterviewRounds.updatedate, 
                         tblInterviewRounds.addedby, tblInterviewRounds.updatedby, tblUserLogin.UserName, tblInterviewRounds.Interviewer, tblMasterInterviewResult.Result, tblInterviewRounds.InterviewResult as ResultID, 
                         tblInterviewRounds.InterviewStatus as InterviewStatusID, 
						 tblMasterInterviewStatus.StatusName AS InterviewStatus
FROM            tblInterviewRounds Left outer JOIN
                         tblUserLogin ON tblInterviewRounds.Interviewer = tblUserLogin.UserID LEFT OUTER JOIN
                         tblMasterInterviewStatus ON tblInterviewRounds.InterviewStatus = tblMasterInterviewStatus.InterviewStatusID LEFT OUTER JOIN
                         tblMasterInterviewResult ON tblInterviewRounds.InterviewResult = tblMasterInterviewResult.StatusID
						 where tblInterviewRounds.ApplicantID = {0} order by tblInterviewRounds.roundID asc", ApplicantID);
            return obj.SelectDatatable(ApplicantDataQuery);
        }

        public DataTable GetRoundsRating()
        {
            string ApplicantDataQuery = string.Format(@"SELECT tblMasterRating.RatingName, tblMasterRating.RatingID, tblApplicantRating.Rating, tblApplicantRating.ApplicantID, tblApplicantRating.RoundID, tblApplicantRating.ReviewID
FROM            tblApplicantRating INNER JOIN tblMasterRating ON tblApplicantRating.RatingID = tblMasterRating.RatingID
						 where tblApplicantRating.ApplicantID = {0} order by tblApplicantRating.RoundID Desc, tblMasterRating.RatingName asc", ApplicantID);
            return obj.SelectDatatable(ApplicantDataQuery);
        }

        public List<SelectListItem> Getusers()
        {
            string ApplicantDataQuery = string.Format(@"SELECT UserID, UserName from tblUserLogin where status = 1 and Role in (1,3) order by username asc");
            DataTable ApplicantDataQuery1 = obj.SelectDatatable(ApplicantDataQuery);
            List<SelectListItem> dep = new List<SelectListItem>();
            foreach (DataRow temp in ApplicantDataQuery1.Rows)
            {
                dep.Add(new SelectListItem() { Text = temp["UserName"].ToString(), Value = temp["UserID"].ToString() });
            }
            var depname = new SelectListItem()
            {
                Value = null,
                Text = "--- select Interviewer ---"
            };
            dep.Insert(0, depname);
            return dep;
        }

        public bool checklastRound()
        {
            string q = string.Format("select top 1 interviewstatus,RoundType  from tblInterviewRounds where ApplicantID = {0} order by RoundID desc", ApplicantID);
            DataTable dt = obj.SelectDatatable(q);
            if (dt != null && dt.Rows.Count > 0)
            {
                string status = dt.Rows[0]["interviewstatus"].ToString().Trim();
                string RoundType= dt.Rows[0]["RoundType"].ToString().Trim();
                if (status == "6" || status == "2" || status == "3" || status == "" || status == null || RoundType==null || RoundType=="")
                {
                    return false;
                }
                else { return true; }
            }
            else
            {
                return false;
            }

        }
        public int addRound()
        {
            string query = string.Format(@"insert into tblInterviewRounds (ApplicantID,CandidateID,RoundType,Interviewer,SheduledOn,InterviewStatus,
createdate,addedby) values (" + ApplicantID + ", (select candidateID from tblApplicants where ApplicantID=" + ApplicantID + "), '" + RoundType + "'," + UserID + ",'" + sheduledOn + "', 1, getdate()," + Int32.Parse(Users.LoginDetail().UserID) + ")");
            int i = obj.ExecuteNonQuery(query);
            return i;
        }
        public bool deleteround()
        {
            string str = string.Format(@"select interviewstatus from tblInterviewRounds where ApplicantID = {0} and RoundID={1}", ApplicantID, RoundID);
            DataTable dt = obj.SelectDatatable(str);
            if (dt != null && dt.Rows.Count > 0)
            {
                string status = dt.Rows[0]["interviewstatus"].ToString().Trim();
                if (status != "6")
                {
                    str = string.Format(@"delete from tblInterviewRounds where ApplicantID = {0} and RoundID={1}", ApplicantID, RoundID);
                    obj.ExecuteNonQuery(str);
                    return true;
                }
                else { return false; }
            }
            else
            {
                return false;
            }

        }

    }

}