public static T_CMS_Master_Contract UpdateItem(T_CMS_Master_Contract model)
{
int result = 0;
using (var reader = SqlHelper.ExecuteReaderService(ModuleConfig.MyConnection, CommandType.StoredProcedure, "T_CMS_Master_Contract_Update_Mini", CreateSqlParameter(model)))
{
while (reader.Read())
{
result = (int)reader[0];
}
}
return(GetItemByID(model.ID));
}
public static T_CMS_Master_ContractCollection Search(SearchFilter SearchKey)
{
T_CMS_Master_ContractCollection collection = new T_CMS_Master_ContractCollection();
using (var reader = SqlHelper.ExecuteReaderService(ModuleConfig.MyConnection, "T_CMS_Master_Contract_Search", SearchFilterManager.SqlSearchConditionNoCompany(SearchKey)))
{
while (reader.Read())
{
T_CMS_Master_Contract obj = new T_CMS_Master_Contract();
obj = GetItemFromReader(reader);
collection.Add(obj);
}
}
return(collection);
}
public static T_CMS_Master_Contract AddItem(T_CMS_Master_Contract model)
{
int result = 0;
//using (var reader = SqlHelper.ExecuteReaderService(ModuleConfig.MyConnection, CommandType.StoredProcedure, "T_CMS_Master_Contract_Add", CreateSqlParameter(model)))
using (var reader = SqlHelper.ExecuteReaderService(ModuleConfig.MyConnection, CommandType.StoredProcedure, "DNH_CMS_ContractGeneration_Insert", AddNewSqlParameter(model)))
{
while (reader.Read())
{
result = (int)reader[0];
}
}
return(GetItemByEmployeeCode(result));
}
public static T_CMS_Master_Contract GetItemByEmployeeCode(int EmployeeCode)
{
T_CMS_Master_Contract item = new T_CMS_Master_Contract();
var sqlParams = new SqlParameter[1];
sqlParams[0] = new SqlParameter("@EmployeeCode", EmployeeCode);
using (var reader = SqlHelper.ExecuteReaderService(ModuleConfig.MyConnection, "T_CMS_Master_Contract_GetByEmployeeCode", sqlParams))
{
while (reader.Read())
{
item = GetItemFromReader(reader);
}
}
return(item);
}
public static T_CMS_Master_ContractCollection GetAllItem(bool isActive)
{
T_CMS_Master_ContractCollection collection = new T_CMS_Master_ContractCollection();
var sqlParams = new SqlParameter[1];
sqlParams[0] = new SqlParameter("@isActive", isActive);
using (var reader = SqlHelper.ExecuteReaderService(ModuleConfig.MyConnection, "T_CMS_Master_Contract_GetAll", sqlParams))
{
while (reader.Read())
{
T_CMS_Master_Contract obj = new T_CMS_Master_Contract();
obj = GetItemFromReader(reader);
collection.Add(obj);
}
}
return(collection);
}
private static SqlParameter[] AddNewSqlParameter(T_CMS_Master_Contract model)
{
return(new SqlParameter[]
{
new SqlParameter("@Mode", model.Mode),
new SqlParameter("@Type", "NEWCONTRACT"), //DNH - Add only for the new Contract
new SqlParameter("@RowID", model.ID),
new SqlParameter("@EmployeeCode", model.EmployeeCode),
new SqlParameter("@SalutationID", model.SalutationID),
new SqlParameter("@FirstName_EN", model.FirstName_EN),
new SqlParameter("@MiddleName_EN", model.MiddleName_EN),
new SqlParameter("@LastName_EN", model.LastName_EN),
new SqlParameter("@FirstName_VN", model.FirstName_VN),
new SqlParameter("@MiddleName_VN", model.MiddleName_VN),
new SqlParameter("@LastName_VN", model.LastName_VN),
new SqlParameter("@DOB", model.DOB),
new SqlParameter("@POB", model.POB),
new SqlParameter("@HighestDegree", model.HighestDegree),
new SqlParameter("@EmpAddress", model.PerAddress),
new SqlParameter("@IDCardNo", model.IDCardNo),
new SqlParameter("@IDDOI", model.IDDOI),
new SqlParameter("@IDPOI", model.IDPOI),
new SqlParameter("@PassportNo", model.PassportNo),
new SqlParameter("@PassportDOI", model.PassportDOI),
new SqlParameter("@PassportPOI", model.PassportPOI),
new SqlParameter("@LabourBookNo", model.LabourBookNo),
new SqlParameter("@LabourDOI", model.LabourDOI),
new SqlParameter("@LabourPOI", model.LabourPOI),
new SqlParameter("@ContractTerm", model.ContractTerm),
new SqlParameter("@Joiningdate", model.Joiningdate),
new SqlParameter("@Enddate", model.Enddate),
new SqlParameter("@LocationID", model.LocationID),
new SqlParameter("@PositionID", model.PositionID),
new SqlParameter("@GradeID", model.GradeID),
new SqlParameter("@DeptID", model.DeptID),
new SqlParameter("@WorkHoursID", model.WorkHoursID),
new SqlParameter("@Grossoffer", model.Grossoffer),
new SqlParameter("@AnnualLeave", model.AnnualLeave),
new SqlParameter("@EmpTypeID", model.EmpTypeID),
new SqlParameter("@EmpSubTypeID", model.EmpSubTypeID),
new SqlParameter("@WorkPermitNo", model.WorkPermitNo),
new SqlParameter("@WorkPermitFrom", model.WorkPermitFrom),
new SqlParameter("@WorkPermitTo", model.WorkPermitTo),
new SqlParameter("@RoleId", 0), //DNH - Default Role = 0
new SqlParameter("@CreatedBy", model.CreatedBy),
new SqlParameter("@HostCountryCurrency", model.HostCountryCurrency),
new SqlParameter("@HostGrossOfferEffectiveFrom", model.HostGrossOfferEffectiveFrom),
new SqlParameter("@HostGrossOfferEffectiveTo", model.HostGrossOfferEffectiveTo),
new SqlParameter("@HomeGrossOffer", model.HomeGrossOffer),
new SqlParameter("@HomeCountryCurrency", model.HomeCountryCurrency),
new SqlParameter("@HomeGrossOfferEffectiveFrom", model.HomeGrossOfferEffectiveFrom),
new SqlParameter("@HomeGrossOfferEffectiveTo", model.HomeGrossOfferEffectiveTo),
new SqlParameter("@RelocationallowanceCurrency", model.RelocationallowanceCurrency),
new SqlParameter("@Relocationallowance", model.Relocationallowance),
new SqlParameter("@GoabroadallowanceCurrency", model.GoabroadallowanceCurrency),
new SqlParameter("@Goabroadallowance", model.Goabroadallowance),
new SqlParameter("@WaivingallowanceCurrency", model.WaivingallowanceCurrency),
new SqlParameter("@Waivingallowance", model.Waivingallowance),
new SqlParameter("@Remarks", model.Remarks),
new SqlParameter("@ProbationsPeriod", model.ProbationsPeriod),
new SqlParameter("@HandPhone", model.HandPhone),
new SqlParameter("@TempAddress", model.TempAddress),
new SqlParameter("@JobDesc", model.JobDesc),
});
}
private static SqlParameter[] CreateSqlParameter(T_CMS_Master_Contract model)
{
return(new SqlParameter[]
{
new SqlParameter("@ID", model.ID),
new SqlParameter("@EmployeeCode", model.EmployeeCode),
new SqlParameter("@ContractNo", model.ContractNo),
new SqlParameter("@StatusID", model.StatusID),
new SqlParameter("@ApproverLevel", model.ApproverLevel),
new SqlParameter("@ContractLevel", model.ContractLevel),
new SqlParameter("@SalutationID", model.SalutationID),
new SqlParameter("@FirstName_EN", model.FirstName_EN),
new SqlParameter("@MiddleName_EN", model.MiddleName_EN),
new SqlParameter("@LastName_EN", model.LastName_EN),
new SqlParameter("@FirstName_VN", model.FirstName_VN),
new SqlParameter("@MiddleName_VN", model.MiddleName_VN),
new SqlParameter("@LastName_VN", model.LastName_VN),
new SqlParameter("@DOB", model.DOB),
new SqlParameter("@POB", model.POB),
new SqlParameter("@HighestDegree", model.HighestDegree),
new SqlParameter("@EmpAddress", model.PerAddress),
new SqlParameter("@IDCardNo", model.IDCardNo),
new SqlParameter("@IDDOI", model.IDDOI),
new SqlParameter("@IDPOI", model.IDPOI),
new SqlParameter("@PassportNo", model.PassportNo),
new SqlParameter("@PassportDOI", model.PassportDOI),
new SqlParameter("@PassportPOI", model.PassportPOI),
new SqlParameter("@LabourBookNo", model.LabourBookNo),
new SqlParameter("@LabourDOI", model.LabourDOI),
new SqlParameter("@LabourPOI", model.LabourPOI),
new SqlParameter("@ContractTerm", model.ContractTerm),
new SqlParameter("@Joiningdate", model.Joiningdate),
new SqlParameter("@Enddate", model.Enddate),
new SqlParameter("@PositionID", model.PositionID),
new SqlParameter("@LocationID", model.LocationID),
new SqlParameter("@GradeID", model.GradeID),
new SqlParameter("@DeptID", model.DeptID),
new SqlParameter("@WorkHoursID", model.WorkHoursID),
new SqlParameter("@Grossoffer", model.Grossoffer),
new SqlParameter("@AnnualLeave", model.AnnualLeave),
new SqlParameter("@EmpTypeID", model.EmpTypeID),
new SqlParameter("@EmpSubTypeID", model.EmpSubTypeID),
new SqlParameter("@WorkPermitNo", model.WorkPermitNo),
new SqlParameter("@WorkPermitFrom", model.WorkPermitFrom),
new SqlParameter("@WorkPermitTo", model.WorkPermitTo),
new SqlParameter("@HomeGrossOffer", model.HomeGrossOffer),
new SqlParameter("@HomeCountryCurrency", model.HomeCountryCurrency),
new SqlParameter("@HomeGrossOfferEffectiveFrom", model.HomeGrossOfferEffectiveFrom),
new SqlParameter("@HomeGrossOfferEffectiveTo", model.HomeGrossOfferEffectiveTo),
new SqlParameter("@RelocationallowanceCurrency", model.RelocationallowanceCurrency),
new SqlParameter("@Relocationallowance", model.Relocationallowance),
new SqlParameter("@GoabroadallowanceCurrency", model.GoabroadallowanceCurrency),
new SqlParameter("@Goabroadallowance", model.Goabroadallowance),
new SqlParameter("@WaivingallowanceCurrency", model.WaivingallowanceCurrency),
new SqlParameter("@Waivingallowance", model.Waivingallowance),
new SqlParameter("@HostCountryCurrency", model.HostCountryCurrency),
new SqlParameter("@HostGrossOfferEffectiveFrom", model.HostGrossOfferEffectiveFrom),
new SqlParameter("@HostGrossOfferEffectiveTo", model.HostGrossOfferEffectiveTo),
new SqlParameter("@Remarks", model.Remarks),
new SqlParameter("@CreatedBy", model.CreatedBy),
new SqlParameter("@CreatedDate", model.CreatedDate),
new SqlParameter("@ModifiedBy", model.ModifiedBy),
new SqlParameter("@ModifiedDate", model.ModifiedDate),
new SqlParameter("@IsActive", model.IsActive),
new SqlParameter("@ProbationsPeriod", model.ProbationsPeriod),
new SqlParameter("@OriginalDate", model.OriginalDate),
new SqlParameter("@WorkFlowStatus", model.WorkFlowStatus),
new SqlParameter("@HandPhone", model.HandPhone),
new SqlParameter("@TempAddress", model.TempAddress),
});
}
private static T_CMS_Master_Contract GetItemFromReader(IDataReader dataReader)
{
T_CMS_Master_Contract objItem = new T_CMS_Master_Contract();
objItem.ID = SqlHelper.GetInt(dataReader, "ID");
objItem.EmployeeCode = SqlHelper.GetInt(dataReader, "EmployeeCode");
objItem.ContractNo = SqlHelper.GetString(dataReader, "ContractNo");
objItem.StatusID = SqlHelper.GetInt(dataReader, "StatusID");
objItem.ApproverLevel = SqlHelper.GetInt(dataReader, "ApproverLevel");
objItem.ContractLevel = SqlHelper.GetInt(dataReader, "ContractLevel");
objItem.SalutationID = SqlHelper.GetInt(dataReader, "SalutationID");
objItem.FirstName_EN = SqlHelper.GetString(dataReader, "FirstName_EN");
objItem.MiddleName_EN = SqlHelper.GetString(dataReader, "MiddleName_EN");
objItem.LastName_EN = SqlHelper.GetString(dataReader, "LastName_EN");
objItem.FirstName_VN = SqlHelper.GetString(dataReader, "FirstName_VN");
objItem.MiddleName_VN = SqlHelper.GetString(dataReader, "MiddleName_VN");
objItem.LastName_VN = SqlHelper.GetString(dataReader, "LastName_VN");
objItem.DOB = SqlHelper.GetDateTime(dataReader, "DOB");
objItem.POB = SqlHelper.GetString(dataReader, "POB");
objItem.HighestDegree = SqlHelper.GetString(dataReader, "HighestDegree");
objItem.PerAddress = SqlHelper.GetString(dataReader, "EmpAddress");
objItem.IDCardNo = SqlHelper.GetString(dataReader, "IDCardNo");
objItem.IDDOI = SqlHelper.GetDateTime(dataReader, "IDDOI");
objItem.IDPOI = SqlHelper.GetInt(dataReader, "IDPOI");
objItem.PassportNo = SqlHelper.GetString(dataReader, "PassportNo");
objItem.PassportDOI = SqlHelper.GetNDateTime(dataReader, "PassportDOI");
objItem.PassportPOI = SqlHelper.GetString(dataReader, "PassportPOI");
objItem.LabourBookNo = SqlHelper.GetString(dataReader, "LabourBookNo");
objItem.LabourDOI = SqlHelper.GetNDateTime(dataReader, "LabourDOI");
objItem.LabourPOI = SqlHelper.GetString(dataReader, "LabourPOI");
objItem.ContractTerm = SqlHelper.GetInt(dataReader, "ContractTerm");
objItem.Joiningdate = SqlHelper.GetDateTime(dataReader, "Joiningdate");
objItem.Enddate = SqlHelper.GetDateTime(dataReader, "Enddate");
objItem.PositionID = SqlHelper.GetInt(dataReader, "PositionID");
objItem.LocationID = SqlHelper.GetInt(dataReader, "LocationID");
objItem.GradeID = SqlHelper.GetInt(dataReader, "GradeID");
objItem.DeptID = SqlHelper.GetInt(dataReader, "DeptID");
objItem.WorkHoursID = SqlHelper.GetInt(dataReader, "WorkHoursID");
objItem.Grossoffer = SqlHelper.GetDecimal(dataReader, "Grossoffer");
objItem.AnnualLeave = SqlHelper.GetInt(dataReader, "AnnualLeave");
objItem.EmpTypeID = SqlHelper.GetInt(dataReader, "EmpTypeID");
objItem.EmpSubTypeID = SqlHelper.GetInt(dataReader, "EmpSubTypeID");
objItem.WorkPermitNo = SqlHelper.GetString(dataReader, "WorkPermitNo");
objItem.WorkPermitFrom = SqlHelper.GetDateTime(dataReader, "WorkPermitFrom");
objItem.WorkPermitTo = SqlHelper.GetDateTime(dataReader, "WorkPermitTo");
objItem.HomeGrossOffer = SqlHelper.GetDecimal(dataReader, "HomeGrossOffer");
objItem.HomeCountryCurrency = SqlHelper.GetString(dataReader, "HomeCountryCurrency");
objItem.HomeGrossOfferEffectiveFrom = SqlHelper.GetDateTime(dataReader, "HomeGrossOfferEffectiveFrom");
objItem.HomeGrossOfferEffectiveTo = SqlHelper.GetDateTime(dataReader, "HomeGrossOfferEffectiveTo");
objItem.RelocationallowanceCurrency = SqlHelper.GetString(dataReader, "RelocationallowanceCurrency");
objItem.Relocationallowance = SqlHelper.GetDecimal(dataReader, "Relocationallowance");
objItem.GoabroadallowanceCurrency = SqlHelper.GetString(dataReader, "GoabroadallowanceCurrency");
objItem.Goabroadallowance = SqlHelper.GetDecimal(dataReader, "Goabroadallowance");
objItem.WaivingallowanceCurrency = SqlHelper.GetString(dataReader, "WaivingallowanceCurrency");
objItem.Waivingallowance = SqlHelper.GetDecimal(dataReader, "Waivingallowance");
objItem.HostCountryCurrency = SqlHelper.GetString(dataReader, "HostCountryCurrency");
objItem.HostGrossOfferEffectiveFrom = SqlHelper.GetDateTime(dataReader, "HostGrossOfferEffectiveFrom");
objItem.HostGrossOfferEffectiveTo = SqlHelper.GetDateTime(dataReader, "HostGrossOfferEffectiveTo");
objItem.Remarks = SqlHelper.GetString(dataReader, "Remarks");
objItem.CreatedBy = SqlHelper.GetInt(dataReader, "CreatedBy");
objItem.CreatedDate = SqlHelper.GetDateTime(dataReader, "CreatedDate");
objItem.ModifiedBy = SqlHelper.GetInt(dataReader, "ModifiedBy");
objItem.ModifiedDate = SqlHelper.GetDateTime(dataReader, "ModifiedDate");
objItem.IsActive = SqlHelper.GetBoolean(dataReader, "IsActive");
objItem.ProbationsPeriod = SqlHelper.GetInt(dataReader, "ProbationsPeriod");
objItem.OriginalDate = SqlHelper.GetDateTime(dataReader, "OriginalDate");
objItem.WorkFlowStatus = SqlHelper.GetInt(dataReader, "WorkFlowStatus");
objItem.HandPhone = SqlHelper.GetString(dataReader, "HandPhone");
objItem.TempAddress = SqlHelper.GetString(dataReader, "TempAddress");
if (SqlHelper.ColumnExists(dataReader, "JobDesc"))
{
objItem.JobDesc = SqlHelper.GetString(dataReader, "JobDesc");
}
if (SqlHelper.ColumnExists(dataReader, "TotalRecord"))
{
objItem.TotalRecord = SqlHelper.GetInt(dataReader, "TotalRecord");
}
return(objItem);
}