public bool SaveEmployeeDetails(EmployeeDTO employeeDetails, string connectionString)
{
int EmployeeSaveCount = 0;
long employeeId;
StringBuilder saveEmployee = new StringBuilder();
try
{
con = new NpgsqlConnection(connectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
// Master Data
string contactdetails = Convert.ToString(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "select coalesce(name,'')||'@'|| coalesce( surname,'') from tblmstcontact where contactid =" + employeeDetails.pContactId));
employeeDetails.pEmployeeName = contactdetails.Split('@')[0];
employeeDetails.pEmployeeSurName = contactdetails.Split('@')[1];
employeeId = Convert.ToInt64(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "insert into tblmstemployee(contactid,titlename,name,surname,statusid,createdby,createddate) values(" + employeeDetails.pContactId + ",'" + ManageQuote(employeeDetails.pEmployeeTitleName).Trim() + "','" + ManageQuote(employeeDetails.pEmployeeName).Trim() + "','" + ManageQuote(employeeDetails.pEmployeeSurName).Trim() + "'," + Convert.ToInt32(Status.Active) + "," + employeeDetails.pCreatedby + ",current_timestamp) returning employeeid;"));
// Employement Details
if (string.IsNullOrEmpty(employeeDetails.pEmploymentJoiningDate))
{
employeeDetails.pEmploymentJoiningDate = "null";
}
else
{
employeeDetails.pEmploymentJoiningDate = "'" + FormatDate(employeeDetails.pEmploymentJoiningDate) + "'";
}
employeeDetails.pEmploymentBasicSalary = Convert.ToString(employeeDetails.pEmploymentBasicSalary) == string.Empty ? 0 : employeeDetails.pEmploymentBasicSalary < 0 ? 0 : employeeDetails.pEmploymentBasicSalary;
employeeDetails.pEmploymentAllowanceORvda = Convert.ToString(employeeDetails.pEmploymentAllowanceORvda) == string.Empty ? 0 : employeeDetails.pEmploymentAllowanceORvda < 0 ? 0 : employeeDetails.pEmploymentAllowanceORvda;
employeeDetails.pEmploymentCTC = Convert.ToString(employeeDetails.pEmploymentCTC) == string.Empty ? 0 : employeeDetails.pEmploymentCTC < 0 ? 0 : employeeDetails.pEmploymentCTC;
// KYC Details
//if (employeeDetails.pListEmpKYC != null && employeeDetails.pListEmpKYC.Count > 0)
//{
// foreach (documentstoreDTO kycDetails in employeeDetails.pListEmpKYC)
// {
// if (!string.IsNullOrEmpty(kycDetails.pDocumentReferenceMonth) && !string.IsNullOrEmpty(kycDetails.pDocumentReferenceYear))
// {
// kycDetails.pDocReferenceno = kycDetails.pDocReferenceno + "~" + kycDetails.pDocumentReferenceMonth + "~" + kycDetails.pDocumentReferenceYear;
// }
// else if (!string.IsNullOrEmpty(kycDetails.pDocumentReferenceMonth))
// {
// kycDetails.pDocReferenceno = kycDetails.pDocReferenceno + "~" + kycDetails.pDocumentReferenceMonth;
// }
// else if (!string.IsNullOrEmpty(kycDetails.pDocumentReferenceYear))
// {
// kycDetails.pDocReferenceno = kycDetails.pDocReferenceno + "~" + kycDetails.pDocumentReferenceYear;
// }
// else
// {
// kycDetails.pDocReferenceno = kycDetails.pDocReferenceno.Trim();
// }
// saveEmployee.Append("update tabapplicationkyccreditdetailsapplicablesections set iskycdocumentsdetailsapplicable='" + (kycDetails.pisapplicable) + "', modifiedby=" + (kycDetails.pCreatedby) + ", modifieddate=current_timestamp where applicationid = " + 0 + " and contactid =" + kycDetails.pContactId + ";");
// if (!string.IsNullOrEmpty(kycDetails.pDocStorePath) && kycDetails.pDocStorePath.Contains('.'))
// {
// string strext = kycDetails.pDocStorePath.Substring(kycDetails.pDocStorePath.LastIndexOf('.') + 1);
// if (!string.IsNullOrEmpty(strext))
// {
// kycDetails.pDocFileType = strext.ToString();
// }
// }
// Int64 count = Convert.ToInt64(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "select count(docstoreid) from tblmstdocumentstore where contactid=" + kycDetails.pContactId + " and documentid=" + kycDetails.pDocumentId + " and documentgroupid=" + kycDetails.pDocumentGroupId + " and coalesce(loanid,0)=0"));
// if (count == 0)
// {
// saveEmployee.Append("insert into tblmstdocumentstore(contactid,documentid,documentname,documentgroupid,documentgroupname,docstorepath,docfiletype,docreferenceno,docisdownloadable,statusid,createdby,createddate) values(" + employeeDetails.pContactId + "," + kycDetails.pDocumentId + ",'" + ManageQuote(kycDetails.pDocumentName) + "'," + kycDetails.pDocumentGroupId + ",'" + ManageQuote(kycDetails.pDocumentGroup) + "','" + ManageQuote(kycDetails.pDocStorePath) + "','" + ManageQuote(kycDetails.pDocFileType) + "','" + ManageQuote(kycDetails.pDocReferenceno).Trim() + "','" + kycDetails.pDocIsDownloadable + "'," + Convert.ToInt32(Status.Active) + "," + employeeDetails.pCreatedby + ",current_timestamp);");
// }
// else
// {
// if (kycDetails.pisapplicable == true)
// saveEmployee.Append("UPDATE tblmstdocumentstore SET applicationno=" + 0 + ",contacttype='" + ManageQuote(kycDetails.pContactType) + "', documentid=" + kycDetails.pDocumentId + ", documentgroupid=" + kycDetails.pDocumentGroupId + ", documentgroupname='" + ManageQuote(kycDetails.pDocumentGroup) + "', documentname='" + ManageQuote(kycDetails.pDocumentName) + "', docstorepath='" + ManageQuote(kycDetails.pDocStorePath) + "', docfiletype='" + ManageQuote(kycDetails.pDocFileType) + "', docreferenceno='" + ManageQuote(kycDetails.pDocReferenceno) + "', docisdownloadable=" + kycDetails.pDocIsDownloadable + ", modifiedby=" + kycDetails.pCreatedby + ", modifieddate=current_timestamp,filename='" + ManageQuote(kycDetails.pFilename) + "' WHERE contactid=" + kycDetails.pContactId + " and docstoreid=" + kycDetails.pDocstoreId + ";");
// }
// }
//}
// End KYC
if (employeeDetails.pListEmpKYC != null && employeeDetails.pListEmpKYC.Count > 0)
{
ReferralAdvocateDAL objReferralAdvocateDAL = new ReferralAdvocateDAL();
saveEmployee.Append(objReferralAdvocateDAL.UpdateStoreDetails(employeeDetails.pListEmpKYC, connectionString, 0, employeeDetails.pContactId));
}
// Employement Details
string roleid = Convert.ToString(employeeDetails.pEmploymentRoleId);
if (employeeDetails.pEmploymentRoleId == 0 || employeeDetails.pEmploymentRoleId == null)
{
roleid = "null";
}
saveEmployee.Append("insert into tblmstemployeeemploymentdetails(employeeid,roleid,rolename,designation,dateofjoining,basicsalary,allowanceorvariablepay,totalcosttocompany,statusid,createdby,createddate) values(" + employeeId + "," + roleid + ",'" + ManageQuote(employeeDetails.pEmploymentRoleName) + "','" + ManageQuote(employeeDetails.pEmploymentDesignation) + "'," + (employeeDetails.pEmploymentJoiningDate) + "," + employeeDetails.pEmploymentBasicSalary + "," + employeeDetails.pEmploymentAllowanceORvda + "," + employeeDetails.pEmploymentCTC + "," + Convert.ToInt32(Status.Active) + "," + employeeDetails.pCreatedby + ",current_timestamp);");
// Personal Details
saveEmployee.Append("insert into tblmstemployeepersonalbirthdetails(employeeid,residentialstatus,maritalstatus,placeofbirth,countryofbirth,nationality,minoritycommunity,statusid,createdby,createddate) values(" + employeeId + ",'" + ManageQuote(employeeDetails.presidentialstatus) + "','" + ManageQuote(employeeDetails.pmaritalstatus) + "','" + ManageQuote(employeeDetails.pplaceofbirth).Trim() + "','" + ManageQuote(employeeDetails.pcountryofbirth).Trim() + "','" + ManageQuote(employeeDetails.pnationality) + "','" + ManageQuote(employeeDetails.pminoritycommunity) + "'," + Convert.ToInt32(Status.Active) + "," + employeeDetails.pCreatedby + ",current_timestamp);");
if (employeeDetails.pListFamilyDetails != null && employeeDetails.pListFamilyDetails.Count > 0)
{
foreach (familyDetailsDTO familyDetails in employeeDetails.pListFamilyDetails)
{
// Personal Details
familyDetails.pTotalnoofmembers = Convert.ToString(familyDetails.pTotalnoofmembers) == string.Empty ? 0 : familyDetails.pTotalnoofmembers < 0 ? 0 : familyDetails.pTotalnoofmembers;
saveEmployee.Append("insert into tblmstemployeepersonalfamilydetails(employeeid,totalnoofmembers,contactpersonname,relationwithemployee,contactnumber,statusid,createdby,createddate) values(" + employeeId + "," + familyDetails.pTotalnoofmembers + ",'" + familyDetails.pContactpersonname + "','" + familyDetails.pRelationwithemployee + "','" + familyDetails.pContactnumber + "'," + Convert.ToInt32(Status.Active) + "," + employeeDetails.pCreatedby + ",current_timestamp);");
}
}
// Bank Details
if (employeeDetails.pListEmpBankDetails != null && employeeDetails.pListEmpBankDetails.Count > 0)
{
foreach (EmployeeBankDetails bankDetails in employeeDetails.pListEmpBankDetails)
{
saveEmployee.Append("insert into tblmstemployeebankdetails(employeeid,bankaccountname,bankname,bankaccountno,bankifsccode,bankbranch,isprimaryaccount,statusid,createdby,createddate) values(" + employeeId + ",'" + ManageQuote(bankDetails.pBankAccountname).Trim() + "','" + ManageQuote(bankDetails.pBankName).Trim() + "','" + ManageQuote(bankDetails.pBankAccountNo).Trim() + "','" + ManageQuote(bankDetails.pBankifscCode).Trim() + "','" + ManageQuote(bankDetails.pBankBranch).Trim() + "','" + bankDetails.pIsprimaryAccount + "'," + Convert.ToInt32(Status.Active) + "," + employeeDetails.pCreatedby + ",current_timestamp);");
}
}
if (Convert.ToString(saveEmployee) != string.Empty)
{
EmployeeSaveCount = NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, saveEmployee.ToString());
trans.Commit();
}
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(EmployeeSaveCount > 0 ? true : false);
}
public bool UpdateEmployeeData(EmployeeDTO empUpdateDTO, string connectionString)
{
bool IsUpdated = false;
StringBuilder sbUpdateEmployee = new StringBuilder();
string Recordid = string.Empty;
// string query = string.Empty;
StringBuilder sbDelete = new StringBuilder();
try
{
con = new NpgsqlConnection(connectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
string contactdetails = Convert.ToString(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "select coalesce(name,'')||'@'|| coalesce( surname,'') from tblmstcontact where contactid in (select contactid from tblmstemployee where employeeid =" + empUpdateDTO.pEmployeeId + ")"));
empUpdateDTO.pEmployeeName = contactdetails.Split('@')[0];
empUpdateDTO.pEmployeeSurName = contactdetails.Split('@')[1];
if (string.IsNullOrEmpty(empUpdateDTO.pMainTransactionType) || empUpdateDTO.pMainTransactionType.Trim().ToUpper() != "DELETE")
{
// Employement Details
if (string.IsNullOrEmpty(empUpdateDTO.pEmploymentJoiningDate))
{
empUpdateDTO.pEmploymentJoiningDate = "null";
}
else
{
empUpdateDTO.pEmploymentJoiningDate = "'" + FormatDate(empUpdateDTO.pEmploymentJoiningDate) + "'";
}
empUpdateDTO.pEmploymentBasicSalary = Convert.ToString(empUpdateDTO.pEmploymentBasicSalary) == string.Empty ? 0 : empUpdateDTO.pEmploymentBasicSalary < 0 ? 0 : empUpdateDTO.pEmploymentBasicSalary;
empUpdateDTO.pEmploymentAllowanceORvda = Convert.ToString(empUpdateDTO.pEmploymentAllowanceORvda) == string.Empty ? 0 : empUpdateDTO.pEmploymentAllowanceORvda < 0 ? 0 : empUpdateDTO.pEmploymentAllowanceORvda;
empUpdateDTO.pEmploymentCTC = Convert.ToString(empUpdateDTO.pEmploymentCTC) == string.Empty ? 0 : empUpdateDTO.pEmploymentCTC < 0 ? 0 : empUpdateDTO.pEmploymentCTC;
string roleid = Convert.ToString(empUpdateDTO.pEmploymentRoleId);
if (empUpdateDTO.pEmploymentRoleId == 0)
{
roleid = "null";
}
sbUpdateEmployee.Append("update tblmstemployee set modifiedby=" + empUpdateDTO.pCreatedby + ",modifieddate=current_timestamp where employeeid=" + empUpdateDTO.pEmployeeId + ";");
sbUpdateEmployee.Append("update tblmstemployeeemploymentdetails set totalcosttocompany=" + empUpdateDTO.pEmploymentCTC + ", allowanceorvariablepay=" + empUpdateDTO.pEmploymentAllowanceORvda + ", basicsalary=" + empUpdateDTO.pEmploymentBasicSalary + ", dateofjoining=" + empUpdateDTO.pEmploymentJoiningDate + ",designation='" + ManageQuote(empUpdateDTO.pEmploymentDesignation) + "', rolename='" + ManageQuote(empUpdateDTO.pEmploymentRoleName) + "', roleid=" + roleid + ",statusid=" + getStatusid(empUpdateDTO.pStatusname, connectionString) + ",modifiedby=" + empUpdateDTO.pCreatedby + ",modifieddate=current_timestamp where employeeid=" + empUpdateDTO.pEmployeeId + ";");
sbUpdateEmployee.Append("update tblmstemployeepersonalbirthdetails set residentialstatus='" + ManageQuote(empUpdateDTO.presidentialstatus) + "', maritalstatus='" + ManageQuote(empUpdateDTO.pmaritalstatus) + "', placeofbirth='" + ManageQuote(empUpdateDTO.pplaceofbirth) + "', countryofbirth='" + ManageQuote(empUpdateDTO.pcountryofbirth) + "',nationality='" + ManageQuote(empUpdateDTO.pnationality) + "', minoritycommunity='" + ManageQuote(empUpdateDTO.pminoritycommunity) + "',modifiedby=" + empUpdateDTO.pCreatedby + ",modifieddate=current_timestamp where employeeid=" + empUpdateDTO.pEmployeeId + ";");
// KYC Data
//if (empUpdateDTO.pListEmpKYC != null && empUpdateDTO.pListEmpKYC.Count > 0)
//{
// foreach (documentstoreDTO docStore in empUpdateDTO.pListEmpKYC)
// {
// if (!string.IsNullOrEmpty(docStore.pDocumentReferenceMonth) && !string.IsNullOrEmpty(docStore.pDocumentReferenceYear))
// {
// docStore.pDocReferenceno = docStore.pDocReferenceno + "~" + docStore.pDocumentReferenceMonth + "~" + docStore.pDocumentReferenceYear;
// }
// else if (!string.IsNullOrEmpty(docStore.pDocumentReferenceMonth))
// {
// docStore.pDocReferenceno = docStore.pDocReferenceno + "~" + docStore.pDocumentReferenceMonth;
// }
// else if (!string.IsNullOrEmpty(docStore.pDocumentReferenceYear))
// {
// docStore.pDocReferenceno = docStore.pDocReferenceno + "~" + docStore.pDocumentReferenceYear;
// }
// else
// {
// docStore.pDocReferenceno = docStore.pDocReferenceno;
// }
// if (!string.IsNullOrEmpty(docStore.ptypeofoperation))
// {
// if (docStore.ptypeofoperation.Trim().ToUpper() != "CREATE")
// {
// if (string.IsNullOrEmpty(Recordid))
// {
// Recordid = docStore.pDocstoreId.ToString();
// }
// else
// {
// Recordid = Recordid + "," + docStore.pDocstoreId.ToString();
// }
// }
// if(docStore.ptypeofoperation.Trim().ToUpper() == "OLD")
// {
// docStore.ptypeofoperation = "UPDATE";
// }
// if (docStore.ptypeofoperation.Trim().ToUpper() == "UPDATE")
// {
// sbUpdateEmployee.Append("UPDATE tblmstdocumentstore SET documentid=" + docStore.pDocumentId + ", documentgroupid=" + docStore.pDocumentGroupId + ", documentgroupname='" + ManageQuote(docStore.pDocumentGroup).Trim() + "', documentname='" + ManageQuote(docStore.pDocumentName).Trim() + "', docstorepath='" + ManageQuote(docStore.pDocStorePath).Trim() + "', docfiletype='" + ManageQuote(docStore.pDocFileType).Trim() + "', docreferenceno='" + ManageQuote(docStore.pDocReferenceno).Trim() + "', docisdownloadable=" + docStore.pDocIsDownloadable + ", modifiedby=" + empUpdateDTO.pCreatedby + ", modifieddate=current_timestamp,statusid=" + Convert.ToInt32(Status.Active) + " WHERE contactid=" + docStore.pContactId + " and docstoreid=" + docStore.pDocstoreId + ";");
// }
// else if (docStore.ptypeofoperation.Trim().ToUpper() == "CREATE")
// {
// sbUpdateEmployee.Append("insert into tblmstdocumentstore (contactid ,documentid,documentgroupid,documentgroupname,documentname,docstorepath,docfiletype,docreferenceno,docisdownloadable,statusid,createdby,createddate) values (" + docStore.pContactId + "," + docStore.pDocumentId + "," + docStore.pDocumentGroupId + ",'" + ManageQuote(docStore.pDocumentGroup).Trim() + "','" + ManageQuote(docStore.pDocumentName).Trim() + "','" + ManageQuote(docStore.pDocStorePath).Trim() + "','" + ManageQuote(docStore.pDocFileType).Trim() + "','" + ManageQuote(docStore.pDocReferenceno).Trim() + "'," + docStore.pDocIsDownloadable + "," + Convert.ToInt32(Status.Active) + "," + empUpdateDTO.pCreatedby + ",current_timestamp);");
// }
// //else if (docStore.ptypeofoperation.Trim().ToUpper() == "DELETE")
// //{
// // sbUpdateEmployee.Append("UPDATE tblmstdocumentstore SET modifiedby=" + empUpdateDTO.pCreatedby + ", modifieddate=current_timestamp,statusid=" + getStatusid("In-Active", connectionString) + " WHERE contactid=" + docStore.pContactId + " and docstoreid=" + docStore.pDocstoreId + ";");
// //}
// }
// }
// if (!string.IsNullOrEmpty(Recordid))
// {
// sbDelete.Append("update tblmstdocumentstore set statusid=" + Convert.ToInt32(Status.Inactive) + ",modifiedby=" + empUpdateDTO.pCreatedby + ",modifieddate=current_timestamp where docstoreid not in(" + Recordid + ")" +
// " and contactid in (select contactid from tblmstemployee where employeeid =" + empUpdateDTO.pEmployeeId + ")" +
// "and coalesce(loanid,0)=0;");
// }
// else
// {
// // sbDelete.Append("update tblmstdocumentstore set statusid=" + Convert.ToInt32(Status.Inactive) + ",modifiedby=" + empUpdateDTO.pCreatedby + ",modifieddate=current_timestamp where contactid in (select contactid from tblmstemployee where employeeid =" + empUpdateDTO.pEmployeeId + ") and coalesce(loanid,0)=0;");
// sbDelete.Append("update tblmstdocumentstore set statusid=" + Convert.ToInt32(Status.Inactive) + ",modifiedby=" + empUpdateDTO.pCreatedby + ",modifieddate=current_timestamp where contactid in (select contactid from tblmstemployee where employeeid =" + empUpdateDTO.pEmployeeId + ") and coalesce(loanid,0)=0;");
// }
//}
//else
//{
// sbDelete.Append("update tblmstdocumentstore set statusid=" + Convert.ToInt32(Status.Inactive) + ",modifiedby=" + empUpdateDTO.pCreatedby + ",modifieddate=current_timestamp where contactid in (select contactid from tblmstemployee where employeeid =" + empUpdateDTO.pEmployeeId + ") and coalesce(loanid,0)=0;");
//}
if (empUpdateDTO.pListEmpKYC != null && empUpdateDTO.pListEmpKYC.Count > 0)
{
ReferralAdvocateDAL objReferralAdvocateDAL = new ReferralAdvocateDAL();
sbDelete.Append(objReferralAdvocateDAL.UpdateStoreDetails(empUpdateDTO.pListEmpKYC, connectionString, 0, empUpdateDTO.pContactId));
}
if (empUpdateDTO.pListEmpBankDetails != null && empUpdateDTO.pListEmpBankDetails.Count > 0)
{
Recordid = string.Empty;
foreach (EmployeeBankDetails empBank in empUpdateDTO.pListEmpBankDetails)
{
if (!string.IsNullOrEmpty(empBank.ptypeofoperation))
{
if (empBank.ptypeofoperation.Trim().ToUpper() != "CREATE")
{
if (string.IsNullOrEmpty(Recordid))
{
Recordid = empBank.pBankRecordid.ToString();
}
else
{
Recordid = Recordid + "," + empBank.pBankRecordid.ToString();
}
}
if (empBank.ptypeofoperation.Trim().ToUpper() == "UPDATE")
{
sbUpdateEmployee.Append("UPDATE tblmstemployeebankdetails SET bankaccountname ='" + ManageQuote(empBank.pBankAccountname).Trim() + "', bankname ='" + ManageQuote(empBank.pBankName).Trim() + "', bankaccountno ='" + ManageQuote(empBank.pBankAccountNo).Trim() + "',bankifsccode ='" + ManageQuote(empBank.pBankifscCode).Trim() + "', bankbranch ='" + ManageQuote(empBank.pBankBranch).Trim() + "', isprimaryaccount ='" + empBank.pIsprimaryAccount + "', modifiedby =" + empUpdateDTO.pCreatedby + ", modifieddate =current_timestamp,statusid=" + Convert.ToInt32(Status.Active) + " WHERE empbankid=" + empBank.pBankRecordid + " and employeeid=" + empUpdateDTO.pEmployeeId + "; ");
}
else if (empBank.ptypeofoperation.Trim().ToUpper() == "CREATE")
{
sbUpdateEmployee.Append("insert into tblmstemployeebankdetails(employeeid,bankaccountname,bankname,bankaccountno,bankifsccode,bankbranch,isprimaryaccount,statusid,createdby,createddate) values(" + empUpdateDTO.pEmployeeId + ",'" + ManageQuote(empBank.pBankAccountname).Trim() + "','" + ManageQuote(empBank.pBankName).Trim() + "','" + ManageQuote(empBank.pBankAccountNo).Trim() + "','" + ManageQuote(empBank.pBankifscCode).Trim() + "','" + ManageQuote(empBank.pBankBranch).Trim() + "','" + empBank.pIsprimaryAccount + "'," + Convert.ToInt32(Status.Active) + "," + empUpdateDTO.pCreatedby + ",current_timestamp);");
}
//else if (empBank.ptypeofoperation.Trim().ToUpper() == "DELETE")
//{
// sbUpdateEmployee.Append("UPDATE tblmstemployeebankdetails SET modifiedby=" + empUpdateDTO.pCreatedby + ", modifieddate=current_timestamp,statusid=" + getStatusid("In-Active", connectionString) + " WHERE empbankid=" + empBank.pBankRecordid + " and employeeid=" + empUpdateDTO.pEmployeeId + ";");
//}
}
}
if (!string.IsNullOrEmpty(Recordid))
{
sbDelete.Append("update tblmstemployeebankdetails set statusid=" + Convert.ToInt32(Status.Inactive) + ",modifiedby=" + empUpdateDTO.pCreatedby + ",modifieddate=current_timestamp where empbankid not in(" + Recordid + ");");
}
else
{
sbDelete.Append("update tblmstemployeebankdetails set statusid=" + Convert.ToInt32(Status.Inactive) + ",modifiedby=" + empUpdateDTO.pCreatedby + ",modifieddate=current_timestamp where employeeid=" + empUpdateDTO.pEmployeeId + ";");
}
}
else
{
sbDelete.Append("update tblmstemployeebankdetails set statusid=" + Convert.ToInt32(Status.Inactive) + ",modifiedby=" + empUpdateDTO.pCreatedby + ",modifieddate=current_timestamp where employeeid=" + empUpdateDTO.pEmployeeId + ";");
}
if (empUpdateDTO.pListFamilyDetails != null && empUpdateDTO.pListFamilyDetails.Count > 0)
{
Recordid = string.Empty;
foreach (familyDetailsDTO empFamily in empUpdateDTO.pListFamilyDetails)
{
// Personal Details
empFamily.pTotalnoofmembers = Convert.ToString(empFamily.pTotalnoofmembers) == string.Empty ? 0 : empFamily.pTotalnoofmembers < 0 ? 0 : empFamily.pTotalnoofmembers;
if (!string.IsNullOrEmpty(empFamily.ptypeofoperation))
{
if (empFamily.ptypeofoperation.Trim().ToUpper() != "CREATE")
{
if (string.IsNullOrEmpty(Recordid))
{
Recordid = empFamily.pfamilyrecordid.ToString();
}
else
{
Recordid = Recordid + "," + empFamily.pfamilyrecordid.ToString();
}
}
if (empFamily.ptypeofoperation.Trim().ToUpper() == "UPDATE")
{
sbUpdateEmployee.Append("UPDATE tblmstemployeepersonalfamilydetails SET totalnoofmembers =" + (empFamily.pTotalnoofmembers) + ", contactpersonname ='" + empFamily.pContactpersonname + "', relationwithemployee ='" + ManageQuote(empFamily.pRelationwithemployee).Trim() + "',contactnumber ='" + ManageQuote(empFamily.pContactnumber).Trim() + "',modifiedby =" + empUpdateDTO.pCreatedby + ", modifieddate =current_timestamp,statusid=" + Convert.ToInt32(Status.Active) + " WHERE recordid=" + empFamily.pfamilyrecordid + " and employeeid=" + empUpdateDTO.pEmployeeId + "; ");
}
else if (empFamily.ptypeofoperation.Trim().ToUpper() == "CREATE")
{
sbUpdateEmployee.Append("insert into tblmstemployeepersonalfamilydetails(employeeid,totalnoofmembers,contactpersonname,relationwithemployee,contactnumber,statusid,createdby,createddate) values(" + empUpdateDTO.pEmployeeId + "," + empFamily.pTotalnoofmembers + ",'" + ManageQuote(empFamily.pContactpersonname).Trim() + "','" + ManageQuote(empFamily.pRelationwithemployee).Trim() + "','" + ManageQuote(empFamily.pContactnumber).Trim() + "'," + Convert.ToInt32(Status.Active) + "," + empUpdateDTO.pCreatedby + ",current_timestamp);");
}
//else if (empFamily.ptypeofoperation.Trim().ToUpper() == "DELETE")
//{
// sbUpdateEmployee.Append("UPDATE tblmstemployeepersonalfamilydetails SET modifiedby =" + empUpdateDTO.pCreatedby + ", modifieddate=current_timestamp,statusid=" + getStatusid("In-Active", connectionString) + " WHERE recordid=" + empFamily.pfamilyrecordid + " and employeeid=" + empUpdateDTO.pEmployeeId + ";");
//}
}
}
if (!string.IsNullOrEmpty(Recordid))
{
sbDelete.Append("update tblmstemployeepersonalfamilydetails set statusid=" + Convert.ToInt32(Status.Inactive) + ",modifiedby=" + empUpdateDTO.pCreatedby + ",modifieddate=current_timestamp where recordid not in(" + Recordid + ");");
}
else
{
sbDelete.Append("update tblmstemployeepersonalfamilydetails set statusid=" + Convert.ToInt32(Status.Inactive) + ",modifiedby=" + empUpdateDTO.pCreatedby + ",modifieddate=current_timestamp where employeeid=" + empUpdateDTO.pEmployeeId + ";");
}
}
else
{
sbDelete.Append("update tblmstemployeepersonalfamilydetails set statusid=" + Convert.ToInt32(Status.Inactive) + ",modifiedby=" + empUpdateDTO.pCreatedby + ",modifieddate=current_timestamp where employeeid=" + empUpdateDTO.pEmployeeId + ";");
}
}
else if (empUpdateDTO.pMainTransactionType.Trim().ToUpper() == "DELETE")
{
sbUpdateEmployee.Append("update tblmstemployee set statusid=" + Convert.ToInt32(Status.Inactive) + ", modifiedby=" + empUpdateDTO.pCreatedby + ",modifieddate=current_timestamp where employeeid=" + empUpdateDTO.pEmployeeId + ";");
sbUpdateEmployee.Append("UPDATE tblmstdocumentstore SET modifiedby=" + empUpdateDTO.pCreatedby + ", modifieddate=current_timestamp,statusid=" + Convert.ToInt32(Status.Inactive) + " WHERE contactid=" + empUpdateDTO.pContactId + " and docstoreid in (select distinct docstoreid from tblmstdocumentstore td join tblmstemployee tm on tm.contactid=td.contactid where tm.contactid=" + empUpdateDTO.pContactId + ");");
sbUpdateEmployee.Append("update tblmstemployeeemploymentdetails set statusid=" + Convert.ToInt32(Status.Inactive) + ", modifiedby=" + empUpdateDTO.pCreatedby + ",modifieddate=current_timestamp where employeeid=" + empUpdateDTO.pEmployeeId + ";");
sbUpdateEmployee.Append("update tblmstemployeepersonalbirthdetails set statusid=" + Convert.ToInt32(Status.Inactive) + ", modifiedby=" + empUpdateDTO.pCreatedby + ",modifieddate=current_timestamp where employeeid=" + empUpdateDTO.pEmployeeId + ";");
sbUpdateEmployee.Append("update tblmstemployeepersonalfamilydetails set statusid=" + Convert.ToInt32(Status.Inactive) + ", modifiedby=" + empUpdateDTO.pCreatedby + ",modifieddate=current_timestamp where employeeid=" + empUpdateDTO.pEmployeeId + ";");
sbUpdateEmployee.Append("update tblmstemployeebankdetails set statusid=" + Convert.ToInt32(Status.Inactive) + ", modifiedby=" + empUpdateDTO.pCreatedby + ",modifieddate=current_timestamp where employeeid=" + empUpdateDTO.pEmployeeId + ";");
}
_ = NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, Convert.ToString(sbDelete) + "" + Convert.ToString(sbUpdateEmployee));
trans.Commit();
IsUpdated = true;
}
catch (Exception)
{
trans.Rollback();
throw;
}
return(IsUpdated);
}
