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); }