using HRM.BO;
using Ease.Core.DataAccess;
using System;
using System.Data;
using System.Security.Cryptography.Xml;

namespace HRM.DA
{
    public class CVDA
    {
        internal static void InsertCVBase(CV obCv, TransactionContext tc)
        {
            int? createdBy = null;
            if (obCv.CreatedBy == 0)
            {
                createdBy = null;
            }
            else
            {
                createdBy = obCv.CreatedBy;
            }

            DateTime? creationDate;
            if (obCv.CreatedDate == null)
            {
                creationDate = null;
            }
            else
            {
                creationDate = obCv.CreatedDate;
            }

            //DataReader.GetNullValue(item.ApprovarID,0)
            //POSITIONID = NULL
            string sql = SQLParser.MakeSQL(
                @"INSERT INTO CV
                    (
                    CVID, Name, FirstName,
                    LastName, Email, Mobile,
                    DesignationID, PresentAddress, PermanentAddress,
                    Gender, MaritalStatus, ReligionID,
                    CREATIONDATE, CREATEDBY,ErCvID,Reference,RecruitmentID,Education,Experience,Skill
                    )
                    VALUES 
                    (
                    %n, %s, %s,
                    %s, %s, %s,
                    %n, %s, %s,
                    %n, %n, %n,
                    %d, %n, %n,%s,%n,%s,%s,%s
                    )", obCv.ID, obCv.Name, obCv.FirstName, obCv.LastName,
                    obCv.Email, obCv.Mobile,
                    obCv.DesignationID, obCv.PresentAddress, obCv.PermanentAddress,
                    obCv.Gender, obCv.MaritalStatus, obCv.ReligionID,
                    obCv.CreatedDate, obCv.CreatedBy, obCv.ErCvID, obCv.Reference,obCv.PositionID, obCv.Education, obCv.Experience, obCv.Skill);
            tc.ExecuteNonQuery(sql);

            //With RecruitmentID (ReferencedColumnName=POSITIONID OF ReferencedTableName=INTERNALREQRUITMENT)
            /*string sql = SQLParser.MakeSQL(
                @"INSERT INTO CV
                    (
                    CVID, Name, FirstName,
                    LastName, Email, Mobile,
                    DesignationID, PresentAddress, PermanentAddress,
                    Gender, MaritalStatus, ReligionID,
                    CREATIONDATE, CREATEDBY,ErCvID,RecruitmentID
                    )
                    VALUES 
                    (
                    %n, %s, %s,
                    %s, %s, %s,
                    %n, %s, %s,
                    %n, %n, %n,
                    %d, %n,%n,%n
                    )", obCv.ID, obCv.Name, obCv.FirstName, obCv.LastName,
                    obCv.Email, obCv.Mobile,
                    obCv.DesignationID, obCv.PresentAddress, obCv.PermanentAddress,
                    obCv.Gender, obCv.MaritalStatus, obCv.ReligionID,
                    obCv.CreatedDate, obCv.CreatedBy, obCv.ErCvID, obCv.PositionID);
            tc.ExecuteNonQuery(sql);       */
        }


        internal static void UpdateCVBase(CV obCv, TransactionContext tc)
        {
            int? modifiedBy = null;
            if (obCv.ModifiedBy == 0)
            {
                modifiedBy = null;
            }
            else
            {
                modifiedBy = obCv.ModifiedBy;
            }

            DateTime? modificationDate;
            if (obCv.ModifiedDate == null)
            {
                modificationDate = null;
            }
            else
            {
                modificationDate = DateTime.Today;
            }

            string sql = SQLParser.MakeSQL(
                @"UPDATE CV
                SET 
                Name = %s,
                FirstName = %s,
                LastName = %s,
                Email = %s,
                Mobile = %s,
                DesignationID = %n,
                PresentAddress = %s,
                PermanentAddress = %s,
                Gender = %n,
                MaritalStatus = %n,
                ReligionID = %n,
                MODIFIEDBY = %n,
                MODIFICATIONDATE = %d,
                Reference = %s,
                RecruitmentID = %n,
                Education = %s,
                Experience = %s,
                Skill = %s
                WHERE CVID = %n",
                obCv.Name, obCv.FirstName, obCv.LastName,
                obCv.Email, obCv.Mobile, obCv.DesignationID, obCv.PresentAddress,
                obCv.PermanentAddress,
                obCv.Gender, obCv.MaritalStatus, obCv.ReligionID, obCv.ModifiedBy, modificationDate, obCv.Reference, obCv.PositionID, obCv.Education, obCv.Experience, obCv.Skill ,obCv.ID);
            tc.ExecuteNonQuery(sql);
        }

        internal static void UpdateCVRecruitment(CV obCv, TransactionContext tc)
        {
            int? modifiedBy = null;
            if (obCv.ModifiedBy == 0)
            {
                modifiedBy = null;
            }
            else
            {
                modifiedBy = obCv.ModifiedBy;
            }

            DateTime? modificationDate;
            if (obCv.ModifiedDate == null)
            {
                modificationDate = null;
            }
            else
            {
                modificationDate = DateTime.Today;
            }

            string sql = SQLParser.MakeSQL(
                @"UPDATE CV
                SET 
                Name = %s,
                FirstName = %s,
                LastName = %s,
                Email = %s,
                Mobile = %s,
                MODIFIEDBY = %n,
                MODIFICATIONDATE = %d
                WHERE CVID = %n",
                obCv.Name, obCv.FirstName, obCv.LastName,
                obCv.Email, obCv.Mobile,obCv.ModifiedBy, modificationDate, obCv.ID);
            tc.ExecuteNonQuery(sql);
        }

        internal static void InsertCVSort(CVSort obCvSort, TransactionContext tc)
        {
            int? createdBy = null;
            if (obCvSort.CreatedBy == 0)
            {
                createdBy = null;
            }
            else
            {
                createdBy = obCvSort.CreatedBy;
            }

            DateTime? creationDate;
            if (obCvSort.CreatedDate == null)
            {
                creationDate = null;
            }
            else
            {
                creationDate = obCvSort.CreatedDate;
            }

            //DataReader.GetNullValue(item.ApprovarID,0)
            string sql = SQLParser.MakeSQL(
                @"INSERT INTO CVsort
                    (
                    CVSORTID, EMPLOYEEID, REQUISITIONID,
                    ASSIGNEDDATETIME, WFSTATUS,
                    CTREATIONDATE, CREATEDBY
                    )
                    VALUES 
                    (
                    %n, %n, %n,
                    %d, %n,
                    %d, %n
                    )", obCvSort.ID, obCvSort.EmployeeID, obCvSort.RequisitionID, obCvSort.AssignDateTime,
                    (int)obCvSort.WfStatus, 
                    obCvSort.CreatedDate, obCvSort.CreatedBy);
            tc.ExecuteNonQuery(sql);
        }
        internal static void UpdateCVSort(CVSort obCv, TransactionContext tc)
        {
            int? modifiedBy = null;
            if (obCv.ModifiedBy == 0)
            {
                modifiedBy = null;
            }
            else
            {
                modifiedBy = obCv.ModifiedBy;
            }

            DateTime? modificationDate;
            if (obCv.ModifiedDate == null)
            {
                modificationDate = null;
            }
            else
            {
                modificationDate = DateTime.Today;
            }

            string sql = SQLParser.MakeSQL(
                @"UPDATE CVSort
                SET 
                EMPLOYEEID = %n,
                REQUISITIONID = %n,
                ASSIGNEDDATETIME = %d,
                WFSTATUS = %n,
                MODIFIEDBY = %n,
                MODIFICATIONDATE = %d
                WHERE CVSORTID = %n",
                obCv.EmployeeID, obCv.RequisitionID, obCv.AssignDateTime,
                obCv.WfStatus, obCv.ModifiedBy, modificationDate, obCv.ID);
            tc.ExecuteNonQuery(sql);
        }

        internal static void InsertUserCvSort(UserCvSort obCvSort, TransactionContext tc)
        {
            int? createdBy = null;
            if (obCvSort.CreatedBy == 0)
            {
                createdBy = null;
            }
            else
            {
                createdBy = obCvSort.CreatedBy;
            }

            DateTime? creationDate;
            if (obCvSort.CreatedDate == null)
            {
                creationDate = null;
            }
            else
            {
                creationDate = obCvSort.CreatedDate;
            }

            //DataReader.GetNullValue(item.ApprovarID,0)
            string sql = SQLParser.MakeSQL(
                @"INSERT INTO USERCVSORT
                    (
                    USERCVSORTID, REQUISITIONID, CANDIDATEID,
                    DiscardStatus, ENTRYDATE,
                    ENTRYBY,REMARKS
                    )
                    VALUES 
                    (
                    %n, %n, %n,
                    %b, %D,
                    %n,%s
                    )", obCvSort.ID, obCvSort.PositionID, obCvSort.CandidateID, obCvSort.SelectStatus,
                    obCvSort.CreatedDate, obCvSort.CreatedBy,obCvSort.Remarks);
            tc.ExecuteNonQuery(sql);
        }

        internal static IDataReader GetReferredBy(int cVID, TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"Select * From CVReferredBy Where CvId = %n", cVID);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetCVOrgByID(int cVID, TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"Select * From CVOrg Where CvId = %n", cVID);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetEducationQualifications(int cVID, TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"Select * From CVEducation Where CVID = %n", cVID);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetExperiences(int cVID, TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"Select * From CVExperience Where CVID = %n", cVID);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader Get(TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"Select * From CV");
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader Get(int cVID, TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"Select * From CV Where CvID = %n", cVID);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetByErcvId(int ercvId, TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"Select * From CV Where ercvId = %n", ercvId);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader Get(string cVIDs, TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"Select c.*,rc.CANDIDATEID,rc.PrimarySelected From CV c  INNER JOIN RECRUITEMENTCANDIDATE rc ON c.CVID=rc.cvid Where c.CvID in (%q)", cVIDs);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetByCandiadteId(string candidateIDs, TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"Select c.*,rc.CANDIDATEID,rc.PrimarySelected From CV c  INNER JOIN RECRUITEMENTCANDIDATE rc ON c.CVID=rc.cvid Where rc.candidateID in (%q)", candidateIDs);
            return tc.ExecuteReader(sql);
        }

        internal static void Delete(int iD, TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"Delete From CV Where CvID = %n", iD);
            tc.ExecuteNonQuery(sql);
        }
        internal static void DeleteUserCvSort(int iD, TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"Delete From UserCVSort Where USERCVSORTID= %n", iD);
            tc.ExecuteNonQuery(sql);
        }

        internal static IDataReader GetUserCVSortByRequisitionID(int requisitionID, TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"Select * From UserCVSort Where RequisitionID = %n", requisitionID);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetCVSortByRequisitionID(int requisitionID, TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"Select * From CVSort Where RequisitionID = %n", requisitionID);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetByQuery(string query, TransactionContext tc)
        {
            return tc.ExecuteReader(query);
        }

        internal static DataSet dtGetCVByID(TransactionContext tc, int cvID)
        {
            string sql = SQLParser.MakeSQL(@"SELECT c.CVID, e.EDUCTIONALQUALIFICATIONID, el.DESCRIPTION EDUCATIONLEVEL, el.EDUCATIONLEVELID,
                                            d.DESCRIPTION DISCIPLINE, d.DISCIPLINEID, e.PASSINGYEAR,
                                            CASE e.ISHIGHESTEDUCATION WHEN 1 THEN 'Yes' ELSE 'No' END AS ISHIGHESTEDUCATION
                                            FROM CV c
                                            LEFT JOIN  CVEDUCTIONALQUALIFICATION e ON e.CVID = c.CVID
                                            LEFT JOIN EDUCATIONLEVEL el ON el.EDUCATIONLEVELID = e.DEGREETITLEID
                                            LEFT JOIN DISCIPLINE d ON d.DISCIPLINEID = e.DISCIPLINEID
                                            WHERE c.CVID = %n", cvID);
            return tc.ExecuteDataSet(sql);
        }

        internal static DataSet dtGetRecruitmentbyCVSort(TransactionContext tc, int employeeID)
        {
            ////
            string sql = SQLParser.MakeSQL(@";WITH cte AS (
                                                SELECT CvSortID,REQUISITIONID,AssignedDatetime,WFSTATUS FROM CVSORT AS c WHERE c.employeeID=%n)
                                             SELECT ir.PositionID,ir.POSITIONNAME,ir.ONBOARDSTATUS,COUNT(*) TotalCV,rc1.assignedDatetime,rc1.WFSTATUS FROM InternalReqruitment ir 
                                               INNER JOIN RECRUITEMENTCANDIDATE rc ON ir.POSITIONID=rc.PROCESSID
                                               INNER JOIN cte rc1 ON ir.POSITIONID=rc1.REQUISITIONID
                                             WHERE ir.ONBOARDSTATUS=%n
                                             GROUP BY  ir.positionID,ir.POSITIONNAME,ir.ONBOARDSTATUS,rc1.assignedDatetime,rc1.WFSTATUS", employeeID,(int)EnumOnBoradStatus.CVCollection);/// EnumonboardStatus=4, userCvSorting
            return tc.ExecuteDataSet(sql);
        }

        internal static DataSet dtCVSortDetails(TransactionContext tc, int positionID)
        {
            //string sql = SQLParser.MakeSQL(@"SELECT ir.PositionID,ir.POSITIONNAME,ir.ONBOARDSTATUS,cv.Name,cv.Mobile,cv.Mobile,cv.Email,rc.CANDIDATEID,cv.CVID
            //                              FROM InternalReqruitment ir 
            //                              INNER JOIN RECRUITEMENTCANDIDATE rc ON ir.POSITIONID=rc.PROCESSID
            //                              INNER JOIN CV cv ON ir.POSITIONID=rc.PROCESSID AND cv.CVID=rc.CVID
            //                             WHERE ir.ONBOARDSTATUS=4 AND ir.positionID=%n", positionID);/// EnumonboardStatus=4
            //string sql = SQLParser.MakeSQL(@"SELECT ir.PositionID,ir.POSITIONNAME,ir.ONBOARDSTATUS,cv.Name,cv.Mobile,cv.Mobile,cv.Email,rc.CANDIDATEID,cv.CVID
            //                                 ,usr.Remarks,usr.SELECTSTATUS,usr.USERCVSORTID
            //                              FROM InternalReqruitment ir 
            //                              INNER JOIN RECRUITEMENTCANDIDATE rc ON ir.POSITIONID=rc.PROCESSID
            //                              INNER JOIN CV cv ON ir.POSITIONID=rc.PROCESSID AND cv.CVID=rc.CVID
            //                                    LEFT JOIN USERCVSORT usr ON ir.POSITIONID=rc.PROCESSID AND rc.CANDIDATEID=usr.CANDIDATEID
            //                             WHERE ir.ONBOARDSTATUS=%n AND ir.positionID=%n", (int)EnumOnBoradStatus.CVCollection,positionID);/// EnumonboardStatus=4
            string sql = SQLParser.MakeSQL(@"SELECT ir.PositionID,ir.POSITIONNAME,ir.ONBOARDSTATUS,cv.Name,cv.Mobile,cv.Mobile,cv.Email,rc.CANDIDATEID,cv.CVID,
                                            rc.PrimarySelected
		                                        FROM InternalReqruitment ir 
		                                        INNER JOIN RECRUITEMENTCANDIDATE rc ON ir.POSITIONID=rc.PROCESSID
		                                        INNER JOIN CV cv ON ir.POSITIONID=rc.PROCESSID AND cv.CVID=rc.CVID
	                                        WHERE ir.ONBOARDSTATUS=%n AND ir.positionID=%n", (int)EnumOnBoradStatus.CVCollection, positionID);/// EnumonboardStatus=4
            return tc.ExecuteDataSet(sql);
        }

        internal static IDataReader GetPreviousCvOfRequisition(TransactionContext tc, int requisitionID)
        {
            //string sql = SQLParser.MakeSQL(@"SELECT cv.*,rc.CANDIDATEID,ir.PositionID
		          //                              FROM InternalReqruitment ir 
		          //                              INNER JOIN RECRUITEMENTCANDIDATE rc ON ir.POSITIONID=rc.PROCESSID
		          //                              INNER JOIN CV cv ON ir.POSITIONID=rc.PROCESSID AND cv.CVID=rc.CVID
	           //                             WHERE ir.ONBOARDSTATUS=%n AND ir.positionID=%n",(int)EnumOnBoradStatus.CVCollection,requisitionID);
            string sql = SQLParser.MakeSQL(@"SELECT cv.*,rc.CANDIDATEID,ir.PositionID,SortStatus='N/A',rc.PrimarySelected
		                                        FROM InternalReqruitment ir 
                                                INNER JOIN RECRUITEMENTCANDIDATE rc ON ir.POSITIONID=rc.PROCESSID
		                                        INNER JOIN CV cv ON ir.POSITIONID=rc.PROCESSID AND cv.CVID=rc.CVID
	                                        WHERE ir.positionID=%n", requisitionID);
            return tc.ExecuteReader(sql);
        }

        internal static void DeleteAttachment(TransactionContext tc, int refID, EnumFileType type)
        {
            string sql = SQLParser.MakeSQL(@"DELETE FROM FILEATTACHMENT WHERE REFERENCEID =%n AND FILETYPE = %n", refID, type);
            tc.ExecuteNonQuery(sql);
        }

        internal static IDataReader GetCVs(TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"Select * From CV");
            return tc.ExecuteReader(sql);
        }

        //changeable
        internal static IDataReader GetOnlinePortalCvs(TransactionContext tc, int requisitionID)
        {
            string sql = SQLParser.MakeSQL(@"Select * From CV WHERE RecruitmentID=%n  AND ErCvID IS NOT null", requisitionID);
            return tc.ExecuteReader(sql);
        }

        internal static void UpdateRequisitionStatus(int RequisitionID,EnumOnBoradStatus status,TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(
                @"UPDATE InternalReqruitment
                SET 
                ONBOARDSTATUS=%n
                WHERE POSITIONID= %n",
                (int)status, RequisitionID);
            tc.ExecuteNonQuery(sql);
        }

        internal static void UpdateCVFromErecruitmentPortal(int CVID, int ercvID, TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(
                @"UPDATE CV
                SET 
                ERCVID=%n
                WHERE CVID= %n",
                ercvID, CVID);
            tc.ExecuteNonQuery(sql);
        }

        internal static IDataReader GetByCvSearch(TransactionContext tc, string name, string email, string mobile)
        {
            string subQuery = string.Empty;

            if (!String.IsNullOrEmpty(name))
            {
                subQuery = SQLParser.TagSQL(subQuery) + SQLParser.MakeSQL("cv.name LIKE %s", ("%" + name + "%"));
            }

            if (!String.IsNullOrEmpty(mobile))
            {
                subQuery = SQLParser.TagSQL(subQuery) + SQLParser.MakeSQL("cv.mobile LIKE %s", ("%" + mobile + "%"));
            }

            if (!String.IsNullOrEmpty(email))
            {
                subQuery = SQLParser.TagSQL(subQuery) + SQLParser.MakeSQL("cv.email LIKE %s", ("%" + email + "%"));
            }

            return tc.ExecuteReader(
                "SELECT Distinct cv.* FROM cv cv %q Order by cv.cvid asc",
                subQuery);
        }

        internal static IDataReader GetAllSearchedCV(TransactionContext tc, string name, string email, string mobile, DateTime? fromDate, DateTime? toDate)
        {
            string subQuery = string.Empty;

            if (!String.IsNullOrEmpty(name))
            {
                subQuery = SQLParser.TagSQL(subQuery) + SQLParser.MakeSQL("cv.name LIKE %s",("%" + name + "%"));
            }

            if (!String.IsNullOrEmpty(mobile))
            {
                subQuery = SQLParser.TagSQL(subQuery) + SQLParser.MakeSQL("cv.mobile LIKE %s",("%" + mobile + "%"));
            }

            if (!String.IsNullOrEmpty(email))
            {
                subQuery = SQLParser.TagSQL(subQuery) + SQLParser.MakeSQL("cv.email LIKE %s",("%" + email + "%"));
            }
            if (fromDate != null)
            {
                subQuery = SQLParser.TagSQL(subQuery) + SQLParser.MakeSQL("CAST(cv.CREATIONDATE as DATE) >= CAST(%d as Date)", fromDate);
            }

            if (toDate != null)
            {
                subQuery = SQLParser.TagSQL(subQuery) + SQLParser.MakeSQL("CAST(cv.CREATIONDATE as DATE) <= CAST(%d as Date)", toDate);
            }

            return tc.ExecuteReader(
                "SELECT Distinct cv.* FROM cv cv %q Order by cv.cvid asc",
                subQuery);
        }

        internal static void UpdateCVCandidate(UserCvSort userCvSort, TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(
                @"UPDATE CV
                SET 
                Name=%s,Mobile=%s,Email=%s
                WHERE CVID= %n",
                userCvSort.CandidateName,userCvSort.Mobile,userCvSort.Email,userCvSort.CvID);
            tc.ExecuteNonQuery(sql);
        }

        //new???
        internal static void UpdateCandidate(UserCvSort userCvSort, TransactionContext tc)
        {
            //string sql = SQLParser.MakeSQL(
            //    @"UPDATE RECRUITEMENTCANDIDATE
            //    SET ISSELECTED=1
            //    WHERE CANDIDATEID= %n",
            //     userCvSort.CandidateID);
            string sql = SQLParser.MakeSQL(
               @"UPDATE RECRUITEMENTCANDIDATE
                SET PrimarySelected=%b
                WHERE CANDIDATEID= %n",
                false, userCvSort.CandidateID);
            tc.ExecuteNonQuery(sql);
        }

        internal static void UpdateCandidateLastStatus(string status,int candidateID,TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(
               @"UPDATE RECRUITEMENTCANDIDATE
                SET CandidateLastStatus=%s
                WHERE CANDIDATEID= %n",
                status, candidateID);
            tc.ExecuteNonQuery(sql);
        }

        internal static void UpdateEmployeeWFStatus(int positionID,int employeeID, TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(
                @"UPDATE CVSort
                SET 
                 WFSTATUS=%n
                WHERE REQUISITIONID= %n and EMPLOYEEID=%n",
                 (int)EnumWFCvSortStatus.Completed,positionID,employeeID);
            tc.ExecuteNonQuery(sql);
        }

        internal static void UpdateCvNameMobileEmail(int cvId, string name, string mobile, string email,string refenrece,string education, string experience, string skill, TransactionContext tc)
        {
            if (!String.IsNullOrEmpty(name))
            {
                string sql = SQLParser.MakeSQL(@"UPDATE CV set name=%s WHERE cvId= %n", name, cvId);
                tc.ExecuteNonQuery(sql);
            }

            if (!String.IsNullOrEmpty(mobile))
            {
                string sql = SQLParser.MakeSQL(@"UPDATE CV set mobile=%s WHERE cvId= %n", mobile, cvId);
                tc.ExecuteNonQuery(sql);
            }

            if (!String.IsNullOrEmpty(email))
            {
                string sql = SQLParser.MakeSQL(@"UPDATE CV set email=%s WHERE cvId= %n", email, cvId);
                tc.ExecuteNonQuery(sql);
            }
            if (!String.IsNullOrEmpty(refenrece))
            {
                string sql = SQLParser.MakeSQL(@"UPDATE CV set Reference=%s WHERE cvId= %n", refenrece, cvId);
                tc.ExecuteNonQuery(sql);
            }
            if (!String.IsNullOrEmpty(education))
            {
                string sql = SQLParser.MakeSQL(@"UPDATE CV set education=%s WHERE cvId= %n", education, cvId);
                tc.ExecuteNonQuery(sql);
            }
            if (!String.IsNullOrEmpty(experience))
            {
                string sql = SQLParser.MakeSQL(@"UPDATE CV set experience=%s WHERE cvId= %n", experience, cvId);
                tc.ExecuteNonQuery(sql);
            }
            if (!String.IsNullOrEmpty(skill))
            {
                string sql = SQLParser.MakeSQL(@"UPDATE CV set skill=%s WHERE cvId= %n", skill, cvId);
                tc.ExecuteNonQuery(sql);
            }

        }

        internal static IDataReader EmailAndPhoneDuplicateCheck(TransactionContext tc,string email, string mobile)
        {
            string subQuery = string.Empty;

            if (!String.IsNullOrEmpty(mobile))
            {
                subQuery = SQLParser.TagSQL(subQuery) + SQLParser.MakeSQL("cv.mobile = %s", ( mobile));
            }

            if (!String.IsNullOrEmpty(email))
            {
                subQuery = SQLParser.TagSQL(subQuery) + SQLParser.MakeSQL("cv.email = %s", (email));
            }

            return tc.ExecuteReader(
                "SELECT Distinct cv.* FROM cv cv %q Order by cv.cvid asc",
                subQuery);
        }

        internal static void DeleteCvSort(int requisitionId,int employeeId,TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"Delete From CVSort Where employeeID= %n and REQUISITIONID=%n", employeeId, requisitionId);
            tc.ExecuteNonQuery(sql);
        }

        internal static IDataReader Getcvs(TransactionContext tc, string name, string email, string mobile,string institute,int degreeTitleId)
        {
            string subQuery = string.Empty;

            if (!String.IsNullOrEmpty(mobile))
            {
                subQuery = SQLParser.TagSQL(subQuery) + SQLParser.MakeSQL("c.mobile = %s", (mobile));
            }

            if (!String.IsNullOrEmpty(email))
            {
                subQuery = SQLParser.TagSQL(subQuery) + SQLParser.MakeSQL("c.email = %s", (email));
            }
            if (!String.IsNullOrEmpty(name))
            {
                subQuery = SQLParser.TagSQL(subQuery) + SQLParser.MakeSQL("c.name like  %s", ("%" + name + "%"));
            }
            if (!String.IsNullOrEmpty(institute))
            {
                subQuery = SQLParser.TagSQL(subQuery) + SQLParser.MakeSQL("c2.institute like  %s", ("%" + institute + "%"));
            }
            if (degreeTitleId > 0)
            {
                subQuery = SQLParser.TagSQL(subQuery) + SQLParser.MakeSQL("c2.DegreeTitleID=%n", degreeTitleId);
            }

            return tc.ExecuteReader(
                "SELECT Distinct c.*,c2.InstituteName FROM cv c Left JOIN CVEducation AS c2 ON c.CVID = c2.CVID %q Order by c.cvid asc",
                subQuery);
        }

    }
}