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 interviewStatus { 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, 
                        (select count(1) from tblSuggestedApplicants where positionid=pos.PositionID and candidate=tblInterviewRounds.CandidateID) as SuggestedApplicantCount,
						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(@"update tblApplicants set Stage=2 where ApplicantID={0}; 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) + ");", ApplicantID);
            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;
            }

        }

        public bool UpdateInterviewStatus()
        {
            string Query = string.Format("update tblInterviewRounds set InterviewStatus={0} where ApplicantID = {1} and RoundID={2}", interviewStatus,ApplicantID, RoundID);
            int i = obj.ExecuteNonQuery(Query);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public List<SelectListItem> InterViewStatusList()
        {
            string Query = "Select InterviewStatusID,StatusName from tblMasterInterviewStatus where InterviewStatusID<4";
            return Common.FillSelectList(Query, "StatusName", "InterviewStatusID");
        }

    }

}