502 lines
20 KiB
C#
502 lines
20 KiB
C#
using HRM.BO;
|
|
using Ease.Core.DataAccess;
|
|
using System;
|
|
using System.Data;
|
|
|
|
namespace HRM.DA
|
|
{
|
|
public class ErCVDA
|
|
{
|
|
internal static void InsertErCVBase(ErCV 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)
|
|
string sql = SQLParser.MakeSQL(
|
|
@"INSERT INTO ErCV
|
|
(
|
|
CVID, Name, FirstName,
|
|
LastName, Email, Mobile,
|
|
Designation, PresentAddress, PermanentAddress,
|
|
Gender, MaritalStatus, ReligionID,
|
|
CREATIONDATE, CREATEDBY,UserID
|
|
)
|
|
VALUES
|
|
(
|
|
%n, %s, %s,
|
|
%s, %s, %s,
|
|
%n, %s, %s,
|
|
%n, %n, %n,
|
|
%d, %n,%n
|
|
)", obCv.ID, obCv.Name, obCv.FirstName, obCv.LastName,
|
|
obCv.Email, obCv.Mobile,
|
|
obCv.Designation, obCv.PresentAddress, obCv.PermanentAddress,
|
|
obCv.Gender, obCv.MaritalStatus, obCv.ReligionID,
|
|
obCv.CreatedDate, obCv.CreatedBy,obCv.UserID);
|
|
|
|
tc.ExecuteNonQuery(sql);
|
|
}
|
|
|
|
|
|
internal static void UpdateCVBase(ErCV 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 Ercv
|
|
SET
|
|
Name = %s,
|
|
FirstName = %s,
|
|
LastName = %s,
|
|
Email = %s,
|
|
Mobile = %s,
|
|
Designation = %n,
|
|
PresentAddress = %s,
|
|
PermanentAddress = %s,
|
|
Gender = %n,
|
|
MaritalStatus = %n,
|
|
ReligionID = %n,
|
|
MODIFIEDBY = %n,
|
|
MODIFICATIONDATE = %d
|
|
WHERE CVID = %n",
|
|
obCv.Name, obCv.FirstName, obCv.LastName,
|
|
obCv.Email, obCv.Mobile, obCv.Designation, obCv.PresentAddress,
|
|
obCv.PermanentAddress,
|
|
obCv.Gender, obCv.MaritalStatus, obCv.ReligionID, obCv.ModifiedBy, modificationDate, 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,
|
|
SELECTSTATUS, 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 ErCVEducation Where CVID = %n", cVID);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader GetErCVTraining(int cVID, TransactionContext tc)
|
|
{
|
|
string sql = SQLParser.MakeSQL(@"Select * From ERCVTRAINING Where CVID = %n", cVID);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader GetExperiences(int cVID, TransactionContext tc)
|
|
{
|
|
string sql = SQLParser.MakeSQL(@"Select * From ErCVExperience 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 ERCV Where CvID = %n", cVID);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader GetByUserId(int userId, TransactionContext tc)
|
|
{
|
|
string sql = SQLParser.MakeSQL(@"Select * From ERCV Where userId = %n", userId);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader Get(string cVIDs, TransactionContext tc)
|
|
{
|
|
string sql = SQLParser.MakeSQL(@"Select * From CV Where CvID in (%q)", cVIDs);
|
|
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
|
|
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'
|
|
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 IDataReader GetPortalCvsByRequisition(TransactionContext tc, int requisitionID)
|
|
{
|
|
string sql = SQLParser.MakeSQL(@"SELECT DISTINCT ercv.* FROM ErAppliedApplicant era
|
|
INNER JOIN ER_Circular ec ON ec.ERCircularID = era.JobID
|
|
INNER JOIN INTERNALREQRUITMENT ir ON ir.POSITIONID= ec.RecruitementID
|
|
INNER JOIN ERJOBUSER eju ON eju.USERID=era.UserID
|
|
INNER JOIN ercv ercv ON ercv.USERID=eju.UserID
|
|
WHERE ec.RecruitementID=%n AND
|
|
NOT EXISTS (SELECT
|
|
cv.cvID
|
|
FROM cv
|
|
WHERE ercv.CVID=cv.ErCvID)", requisitionID);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader GetbyErcvIds(string cvIds, TransactionContext tc)
|
|
{
|
|
string sql = SQLParser.MakeSQL(@"Select * From ercv Where CvID in (%q)", cvIds);
|
|
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);
|
|
}
|
|
|
|
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 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 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);
|
|
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);
|
|
}
|
|
|
|
}
|
|
} |