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

namespace EnvigoHiring.Models.CandidateSelection
{
    public class ApprovalModel:BaseClass
    {
        public int PositionID { get; set; }
        public string Department { get; set; }
        public string Designation { get; set; }
        public DataTable dtCandidate { get; set; }
        public DataTable dtSelectedCandidate { get; set; }
        public string CandidateList { get; set; }
        public int ApplicantID { get; set; }
        public string sheduledDate { get; set; }
        DataLayer obj = DataLayer.Instance;
        public void FillData()
        {
            string query = "Select t1.positionID,DepartmentName,DesignationName,candidate from tblSuggestedApplicants t right outer join vwPosition t1 on (t.PositionID=t1.PositionID) where t1.JDStatus>0 and  t1.PositionID=" + PositionID;
            dtSelectedCandidate = obj.SelectDatatable(query);
            if (dtSelectedCandidate != null && dtSelectedCandidate.Rows.Count > 0)
            {
                DataRow row = dtSelectedCandidate.Rows[0];
                PositionID = row.SetParamValue("PositionID").ToInt32();
                Department = row.SetParamValue("DepartmentName");
                Designation = row.SetParamValue("DesignationName");
                FilePath = ConfigurationManager.AppSettings["ResumePath"].ToString().Trim();
                dtCandidate = viewcandidate();
            }
            else
            {
                HttpContext.Current.Response.Redirect("/position/viewposition");
            }
        }
        public bool CandidateSelected(string candidateID)
        {
            bool result = false;
            if (dtSelectedCandidate != null && dtSelectedCandidate.Rows.Count > 0)
            {
                foreach (DataRow row in dtSelectedCandidate.Rows)
                {
                    if (row.SetParamValue("candidate") == candidateID)
                    {
                        result = true;
                    }
                }
            }
            return result;
        }
        public DataTable viewcandidate()
        {
            //  string query = string.Format(@"select  CandidateID,CurrentStatus,Name,Experience,EmailID,CandidateStatus,Source,Gender,ResumeName,ResumeGoogleLink,LastContactedDate,Education,DepartmentName from tblMasterCandidatePool t inner join 
            //tblSuggestedApplicants t1 on (t.CandidateID=t1.Candidate) inner join
            //                              tblMasterDepartment t2 on(t.InterestedDepartmentID = t2.DepartmentID) 
            //where positionid="+PositionID+ " ");
            string query = string.Format(@"SELECT t.CandidateID, t1.CurrentStatus, t.Name, t.Experience, t.EmailID, t.CandidateStatus, t.Source, t.Gender, t.ResumeName, t.ResumeGoogleLink, t.LastContactedDate, t.Education, t2.DepartmentName,
                        apl.ApplicantID,r.InterviewStatus,r.SheduledOn,r.rank,mis.StatusName as InterViewStatusName FROM tblMasterCandidatePool AS t INNER JOIN tblSuggestedApplicants AS t1 ON t.CandidateID = t1.Candidate INNER JOIN
                         tblMasterDepartment AS t2 ON t.InterestedDepartmentID = t2.DepartmentID left outer join tblApplicants apl on (t1.PositionID=apl.PositionID and t.CandidateID=apl.CandidateID)
						 left outer join (select rounds.ApplicantID,SheduledOn,InterviewStatus,PositionID,Rank() over (Partition BY PositionID,rounds.CandidateID ORDER BY rounds.Createdate desc ) AS Rank from tblInterviewRounds rounds 
                        inner join tblapplicants apl on (rounds.ApplicantID=apl.ApplicantID) ) as r on (apl.ApplicantID=r.ApplicantID) left outer join tblMasterInterviewStatus mis on (r.InterviewStatus=mis.InterviewStatusID)
						 WHERE (t1.PositionID = {0}) and t1.CurrentStatus<>'Reject' and (rank=1 or rank is null)", PositionID);
            return obj.SelectDatatable(query);
        }
        public void ApproveCand(int CandID)
        {
            SaveData sv = new SaveData();
            SqlParameter[] perm1 = new SqlParameter[3];
            perm1[0] = new SqlParameter("@PositionID", PositionID);
            perm1[1] = new SqlParameter("@CandidateID", CandID);
            perm1[2] = new SqlParameter("@CurrentStatus", "Approve");
            DataTable success1 = obj.ExecuteStoreProcedure("Proc_tblSuggestedApplicants_Approve", perm1);
            if (success1 != null)
            {
                sv.isSuccess = true;
                sv.Message = "<b>Success :</b> Candidate approved successfully.";
                string email = CommonFunctions.getadminemailid(4);
                if (email.Length > 0)
                {
                    string q = @"SELECT tblUserLogin.UserName,tblUserLogin.Useremail, tblMasterDesignation.DesignationName
                    FROM tblPositions INNER JOIN tblUserLogin ON tblPositions.RequesterID = tblUserLogin.UserID INNER JOIN
                     tblMasterDesignation ON tblPositions.DesignationID = tblMasterDesignation.DesignationID
                    WHERE(tblPositions.PositionID = " + PositionID + ")";
                DataTable dt = obj.SelectDatatable(q);


                string FileName = AppDomain.CurrentDomain.BaseDirectory + "content\\mailer.html";
                System.IO.StreamReader ObjStrmRdr;
                ObjStrmRdr = System.IO.File.OpenText(FileName);
                string StrMail = ObjStrmRdr.ReadToEnd();
                StrMail = StrMail.Replace("$$Heading$$", "Resume selected by user!");
                StrMail = StrMail.Replace("$$Content$$", "Dear Member,<br/><br/> Resumes has been selected by "+ Users.LoginDetail().UserName + " on position " + dt.Rows[0]["DesignationName"] + " in <b>Envigo Hiring</b> application. <a href='" + ConfigurationManager.AppSettings["SiteUrl"] + "' target=_blank>Click here</a> for next step.");
                ObjStrmRdr.Close();
                CommonFunctions.Email_Send("Resumes for Position - " + dt.Rows[0]["DesignationName"] + "", email, StrMail);
                }
            }
            else
            {
                sv.isSuccess = false;
                sv.Message = "<b>Error :</b> Please try again later.";
            }
            alert(sv.Message, sv.isSuccess);
            
        }
        public void RejectCand(int CandID)
        {
            SaveData sv = new SaveData();
            SqlParameter[] perm1 = new SqlParameter[3];
            perm1[0] = new SqlParameter("@PositionID", PositionID);
            perm1[1] = new SqlParameter("@CandidateID", CandID);
            perm1[2] = new SqlParameter("@CurrentStatus", "Reject");
            DataTable success1 = obj.ExecuteStoreProcedure("Proc_tblSuggestedApplicants_reject", perm1);
            if (success1 != null)
            {
                sv.isSuccess = true;
                sv.Message = "<b>Success :</b> Candidate rejected successfully.";
                string email = CommonFunctions.getadminemailid(4);
                if (email.Length > 0)
                {
                    string q = @"SELECT tblUserLogin.UserName,tblUserLogin.Useremail, tblMasterDesignation.DesignationName
                    FROM tblPositions INNER JOIN tblUserLogin ON tblPositions.RequesterID = tblUserLogin.UserID INNER JOIN
                     tblMasterDesignation ON tblPositions.DesignationID = tblMasterDesignation.DesignationID
                    WHERE(tblPositions.PositionID = " + PositionID + ")";
                    DataTable dt = obj.SelectDatatable(q);


                    string FileName = AppDomain.CurrentDomain.BaseDirectory + "content\\mailer.html";
                    System.IO.StreamReader ObjStrmRdr;
                    ObjStrmRdr = System.IO.File.OpenText(FileName);
                    string StrMail = ObjStrmRdr.ReadToEnd();
                    StrMail = StrMail.Replace("$$Heading$$", "Resume rejected by User!");
                    StrMail = StrMail.Replace("$$Content$$", "Dear Member,<br/><br/> Resumes has been rejected by "+ Users.LoginDetail().UserName + " on position " + dt.Rows[0]["DesignationName"] + " in <b>Envigo Hiring</b> application. <a href='" + ConfigurationManager.AppSettings["SiteUrl"] + "' target=_blank>Click here</a> for next step.");
                    ObjStrmRdr.Close();
                    CommonFunctions.Email_Send("Resumes for Position - " + dt.Rows[0]["DesignationName"] + "", email, StrMail);
                }

            }
            else
            {
                sv.isSuccess = false;
                sv.Message = "<b>Error :</b> Please try again later.";
            }
            alert(sv.Message, sv.isSuccess);
            
        }
        
    }
}