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 *,(Select count(1) from tblInterviewRounds where ApplicantID!=tbl.ApplicantID and CandidateID=tbl.CandidateID and SheduledOn>tbl.SheduledOn and rank=1 ) as NewInterview from (SELECT  tblInterviewRounds.RoundID,
						pos.PositionID,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,pos.Status, Rank() over (Partition BY pos.PositionID,tblInterviewRounds.ApplicantID ORDER BY 
						 tblInterviewRounds.SheduledOn desc ) Rank 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
						 inner join tblApplicants apl on (tblInterviewRounds.ApplicantID=apl.ApplicantID) inner join tblPositions pos on (apl.PositionID=pos.PositionID) ) tbl
						 where tbl.ApplicantID = {0}  order by tbl.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 == "4" || 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;
            }

        }

    }

}