using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Ease.Core.DataAccess;
using HRM.BO.Basic;

namespace HRM.DA
{
    public class JobDefinitionDA
    {
        public JobDefinitionDA()
        {
        }

        //internal static void InsertJD(TransactionContext tc, JobDefinition item)
        //{
        //    string sql = SQLParser.MakeSQL(
        //        @"INSERT INTO dbo.JobDefinition (JdId, JdNo, JdName, RevisionDate, DepartmentId, DesignationId, GradeId, Purpose, FinancialInformation, TeamInformation,  InternalComm, ExternalComm, ExperienceMinYear, ExperienceMaxYear, KnowledgeUnderstanding, AdherenceToCompany, ApprovedBy, ApprovedDate, ApprovarRemarks,WfStatus)
        //      VALUES (%n, %n, %s, %d, %n, %n, %n, %s, %s, %s, %s, %s, %s, %s, %s, %s, %n, %d, %s,%n)", item.ID,
        //        item.JdNo,
        //        item.JdName, item.ApproveDate, item.DepartmentId, item.DesignationId, item.GradeId, item.Purpose,
        //        item.FinancialInformation, item.TeamInformation, item.InternalComm,
        //        item.ExternalComm, item.ExperienceMinYear, item.ExperienceMaxYear, item.KnowledgeUnderstanding,
        //        item.AdherenceToCompany, item.ApprovedBy, item.ApproveDate, item.ApprovarRemarks,(int)item.WfStatus);
        //    tc.ExecuteNonQuery(sql);
        //}
        internal static void InsertJD(TransactionContext tc, JobDefinition item)
        {

            string sql = SQLParser.MakeSQL(
                @"INSERT INTO dbo.JobDefinition (JdId, JdNo, JdName, RevisionDate, DepartmentId, DesignationId, GradeId, Purpose, FinancialInformation, TeamInformation,  InternalComm, ExternalComm, ExperienceMinYear, ExperienceMaxYear, KnowledgeUnderstanding, AdherenceToCompany, CreatedBy, CreatedDate, ApprovarRemarks,WfStatus,Requirement)
              VALUES (%n, %n, %s, %d, %n, %n, %n, %s, %s, %s, %s, %s, %s, %s, %s, %s, %n, %d, %s,%n,%s)", item.ID,
                item.JdNo,
                item.JdName, item.ApproveDate, item.DepartmentId, item.DesignationId, item.GradeId, item.Purpose,
                item.FinancialInformation, item.TeamInformation, item.InternalComm,
                item.ExternalComm, item.ExperienceMinYear, item.ExperienceMaxYear, item.KnowledgeUnderstanding,
                item.AdherenceToCompany, item.CreatedBy, item.CreatedDate, item.ApprovarRemarks, (int)item.WfStatus,item.Requirement);
            tc.ExecuteNonQuery(sql);
        }

        internal static void UpdateJD(TransactionContext tc, JobDefinition item)
        {
            string sql = SQLParser.MakeSQL(@"UPDATE dbo.JobDefinition
                                                SET JdNo=%n,
                                                JdName = %s,
	                                            RevisionDate = %d,
	                                            DepartmentId = %n,
	                                            DesignationId = %n,
	                                            GradeId = %n,
	                                            Purpose = %s,
	                                            FinancialInformation = %s,
	                                            TeamInformation = %s,
	                                            InternalComm = %s,
	                                            ExternalComm = %s,
	                                            ExperienceMinYear = %s,
	                                            ExperienceMaxYear = %s,
	                                            KnowledgeUnderstanding = %s,
	                                            AdherenceToCompany = %s,
	                                            ApprovedBy = %n,
	                                            ApprovedDate = %d,
	                                            ApprovarRemarks = %s,
                                                WfStatus=%n,
                                                Requirement=%s
	                                            WHERE JdId = %n", item.JdNo, item.JdName, item.RevisionDate,
                item.DepartmentId,
                item.DesignationId, item.GradeId, item.Purpose,
                item.FinancialInformation, item.TeamInformation, item.InternalComm,
                item.ExternalComm, item.ExperienceMinYear, item.ExperienceMaxYear, item.KnowledgeUnderstanding,
                item.AdherenceToCompany, item.ApprovedBy, item.ApproveDate, item.ApprovarRemarks, (int)item.WfStatus,item.Requirement,item.ID);
            tc.ExecuteNonQuery(sql);
        }

        internal static void InsertJDEducation(TransactionContext tc, JDEducation item)
        {
            string sql = SQLParser.MakeSQL(
                @"INSERT INTO dbo.JDEducation (JdEducationId, JdId, ReferenceId, JdEducationName, remarks)
                            VALUES (%n, %n, %n, %s, %s)", item.ID, item.JdId, item.ReferenceId, item.JdEducationName,
                item.Remarks);
            tc.ExecuteNonQuery(sql);
        }
        internal static void InsertJDCertification(TransactionContext tc, JDCertification item)
        {
            string sql = SQLParser.MakeSQL(
                @"INSERT INTO dbo.JDCertification (JdCertificationId, JdId, ReferenceId, JdCertificationName, remarks)
                            VALUES (%n, %n, %n, %s, %s)", item.ID, item.JdId, item.ReferenceId, item.JdCertificationName,
                item.Remarks);
            tc.ExecuteNonQuery(sql);
        }

        internal static void InsertJDResponsibility(TransactionContext tc, JDResponsibility item)
        {
            string sql = SQLParser.MakeSQL(
                @"INSERT INTO dbo.JdResponsibility (JdResponsibilityId, JdId, Name, Description, TimeSpentPercent, MeasurementCriteria)
                         VALUES (%n, %n, %s, %s, %n, %s)", item.ID, item.JdId, item.Name, item.Description,
                item.TimeSpentPercent, item.MeasurementCriteria);
            tc.ExecuteNonQuery(sql);
        }

        internal static void DeleteJDEducation(TransactionContext tc, int id)
        {
            string sql = SQLParser.MakeSQL(@"Delete from JDEducation where JdId=%n", id);
            tc.ExecuteNonQuery(sql);
        }
        internal static void DeleteJDCertification(TransactionContext tc, int id)
        {
            string sql = SQLParser.MakeSQL(@"Delete from JDCertification where JdId=%n", id);
            tc.ExecuteNonQuery(sql);
        }
        internal static void DeleteJDResponsibility(TransactionContext tc, int id)
        {
            string sql = SQLParser.MakeSQL(@"Delete from JdResponsibility where JdId=%n", id);
            tc.ExecuteNonQuery(sql);
        }

        internal static void DeleteJobDefinition(TransactionContext tc, int id)
        {
            string sql = SQLParser.MakeSQL(@"Delete from JobDefinition where JdId=%n", id);
            tc.ExecuteNonQuery(sql);
        }

        internal static IDataReader GetAllJobDefinition(TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"select * from JobDefinition");
            return tc.ExecuteReader(sql);
        }
        internal static IDataReader GetByID(TransactionContext tc,int jdid)
        {
            string sql = SQLParser.MakeSQL(@"select * from JobDefinition where JdId = %n", jdid);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetJDEducation(TransactionContext tc, int id)
        {
            string sql = SQLParser.MakeSQL(@"select * from JDEducation where JdId=%n", id);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetJDCertification(TransactionContext tc, int id)
        {
            string sql = SQLParser.MakeSQL(@"select * from JDCertification where JdId=%n", id);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetJDResponsibility(TransactionContext tc, int id)
        {
            string sql = SQLParser.MakeSQL(@"select * from JdResponsibility where JdId=%n", id);
            return tc.ExecuteReader(sql);
        }
    }
}