public void Commit()
{
try
{
_transacao.Commit();
}
catch
{
_transacao.Rollback();
throw;
}
finally
{
_transacao.Dispose();
_transacao = _conexao.BeginTransaction();
resetarRepositorios();
}
}
public bool SaveFdReceipt(FDReceiptDTO ObjFdReceiptDTO, string ConnectionString, out string OUTReceiptid)
{
bool Issaved = false;
StringBuilder sbInsert = new StringBuilder();
bool IsAccountSaved = false;
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (Savegenaralreceipt(ObjFdReceiptDTO, trans, out OUTReceiptid))
{
IsAccountSaved = true;
}
else
{
trans.Rollback();
return(IsAccountSaved);
}
string Receiptno = OUTReceiptid;
if (string.IsNullOrEmpty(ObjFdReceiptDTO.pRecordid.ToString()) || ObjFdReceiptDTO.pRecordid == 0)
{
sbInsert.Append("INSERT INTO public.fd_receipt(fd_receiptt_date,member_id, fd_account_id,deposit_type, instalment_amount, received_amount, mode_of_receipt,receipt_no,narration,status)VALUES('" + FormatDate(ObjFdReceiptDTO.pReceiptdate) + "'," + ObjFdReceiptDTO.pMemberid + "," + ObjFdReceiptDTO.pFdaccountid + ",'" + ObjFdReceiptDTO.pDeposittype + "'," + ObjFdReceiptDTO.pInstalmentamount + "," + ObjFdReceiptDTO.pReceivedamount + ",'" + ObjFdReceiptDTO.pModeofreceipt + "','" + Receiptno + "','" + ObjFdReceiptDTO.pNarration + "'," + ObjFdReceiptDTO.pStatus + ");");
}
if (!string.IsNullOrEmpty(sbInsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbInsert.ToString());
}
if (ObjFdReceiptDTO.pModeofreceipt == "CASH")
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, "SELECT FN_PROMOTOR_SALARY_JV(" + ObjFdReceiptDTO.pFdaccountid + ",'" + FormatDate(ObjFdReceiptDTO.pReceiptdate) + "')");
}
trans.Commit();
Issaved = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
public string SaveshareApplication(ShareApplicationDTO ShareApplicationDTO, string ConnectionString, out long pShareaccountid)
{
bool IsSaved = false;
StringBuilder sbInsert = new StringBuilder();
Int64 Maxsharetovalue = 0;
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (string.IsNullOrEmpty(ShareApplicationDTO.pshareapplicationid.ToString()) || ShareApplicationDTO.pshareapplicationid == 0)
{
Maxsharetovalue = Convert.ToInt64(NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "select coalesce(max(distinctive_to),0) from Share_Account_creation;"));
ShareApplicationDTO.pdistinctivefrom = Maxsharetovalue + 1;
// ShareApplicationDTO.pdistinctiveto = (Maxsharetovalue + ShareApplicationDTO.pnoofsharesissued) - 1;
ShareApplicationDTO.pdistinctiveto = (ShareApplicationDTO.pdistinctivefrom + ShareApplicationDTO.pnoofsharesissued);
ShareApplicationDTO.pShareAccountNo = NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "SELECT FN_GENERATENEXTID('SHARE CAPITAL','" + ShareApplicationDTO.psharename + "','" + ShareApplicationDTO.pTransdate + "')").ToString();
//ShareApplicationDTO.pShareAccountNo = "sd0001";
ShareApplicationDTO.pshareapplicationid = Convert.ToInt64(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "insert into Share_Account_creation(share_Account_number,share_config_id,share_name,member_type_id,member_id,member_name,applicant_type,Trans_date,reference_no,face_value,no_of_shares_issued,distinctive_from,distinctive_to,total_amount,shares_issue_date,is_member_fee_applicable,Bond_print_status,statusid,createdby,createddate)values('" + ShareApplicationDTO.pShareAccountNo + "'," + ShareApplicationDTO.pshareconfigid + ",'" + ManageQuote(ShareApplicationDTO.psharename) + "'," + Convert.ToInt64(ShareApplicationDTO.pmembertypeid) + "," + Convert.ToInt64(ShareApplicationDTO.pmemberid) + ", '" + ManageQuote(ShareApplicationDTO.pmembername) + "','" + ManageQuote(ShareApplicationDTO.pApplicanttype) + "','" + FormatDate(ShareApplicationDTO.pTransdate) + "','" + ManageQuote(ShareApplicationDTO.preferenceno) + "'," + Convert.ToDecimal(ShareApplicationDTO.pfacevalue) + "," + Convert.ToInt64(ShareApplicationDTO.pnoofsharesissued) + "," + Convert.ToInt64(ShareApplicationDTO.pdistinctivefrom) + "," + Convert.ToInt64(ShareApplicationDTO.pdistinctiveto) + "," + Convert.ToDecimal(ShareApplicationDTO.ptotalamount) + ",'" + FormatDate(ShareApplicationDTO.pshareissuedate) + "','" + ShareApplicationDTO.pismemberfeeapplicable + "','N'," + Convert.ToInt32(Status.Active) + "," + ShareApplicationDTO.pCreatedby + ",current_timestamp) returning share_account_id;"));
}
else
{
sbInsert.Append("update Share_Account_creation set is_member_fee_applicable='" + ShareApplicationDTO.pismemberfeeapplicable + "', member_type_id=" + Convert.ToInt64(ShareApplicationDTO.pmembertypeid) + ", member_id=" + Convert.ToInt64(ShareApplicationDTO.pmemberid) + ", share_config_id=" + Convert.ToInt64(ShareApplicationDTO.pshareconfigid) + ",reference_no='" + ManageQuote(ShareApplicationDTO.preferenceno) + "', face_value=" + Convert.ToDecimal(ShareApplicationDTO.pfacevalue) + ",no_of_shares_issued=" + Convert.ToInt64(ShareApplicationDTO.pnoofsharesissued) + ", distinctive_from=" + Convert.ToInt64(ShareApplicationDTO.pdistinctivefrom) + ", distinctive_to=" + Convert.ToInt64(ShareApplicationDTO.pdistinctiveto) + ", total_amount=" + Convert.ToDecimal(ShareApplicationDTO.ptotalamount) + ",Share_Joining_date='" + FormatDate(ShareApplicationDTO.pTransdate) + "', shares_issue_date='" + FormatDate(ShareApplicationDTO.pshareissuedate) + "', Bond_print_status='N',modifiedby=" + ShareApplicationDTO.pCreatedby + ",modifieddate=current_timestamp where share_account_id=" + ShareApplicationDTO.pShareaccountid + " and share_account_number=" + ShareApplicationDTO.pShareAccountNo + ";");
if (!string.IsNullOrEmpty(sbInsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbInsert.ToString());
}
}
pShareaccountid = ShareApplicationDTO.pshareapplicationid;
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Convert.ToString(ShareApplicationDTO.pShareAccountNo));
}
public static void RollbackAndDispose(this NpgsqlTransaction transaction)
{
if (transaction == null || transaction.IsCompleted)
{
return;
}
transaction.Rollback();
transaction.Dispose();
}
protected virtual void Dispose(bool disposing)
{
if (!disposing)
{
return;
}
Transaction?.Dispose();
DbConnection?.Close();
DbConnection?.Dispose();
}
public static void CommitAndDispose(this NpgsqlTransaction transaction)
{
if (transaction == null || transaction.IsCompleted)
{
return;
}
transaction.Commit();
transaction.Dispose();
}
public bool SaveReferralCommission(ReferralCommissionDTO objReferralCommission, string ConnectionString)
{
bool isSaved = false;
StringBuilder sbinsert = new StringBuilder();
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (string.IsNullOrEmpty(objReferralCommission.pCommissionValue.ToString()))
{
objReferralCommission.pCommissionValue = 0;
}
if (string.IsNullOrEmpty(objReferralCommission.pTdspercentage.ToString()))
{
objReferralCommission.pTdspercentage = 0;
}
int count = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "select count(*) from tblmstSavingAccountConfigreferraldetails where savingconfigid=" + objReferralCommission.pSavingConfigid + " and statusid=" + Convert.ToInt32(Status.Active) + ""));
if (count != 0)
{
sbinsert.Append("Update tblmstSavingAccountConfigreferraldetails set isreferralcommissionapplicable=" + objReferralCommission.pIsreferralcommissionapplicable + ", referralcommissioncalfield='" + ManageQuote(objReferralCommission.pReferralcommissioncalfield) + "',referralcommissiontype='" + objReferralCommission.pReferralcommissiontype + "' ,commissionValue=" + objReferralCommission.pCommissionValue + ",istdsapplicable=" + objReferralCommission.pIstdsapplicable + ",tdsaccountid='" + ManageQuote(objReferralCommission.pTdsaccountid) + "',tdssection='" + ManageQuote(objReferralCommission.ptdssection) + "',tdspercentage=" + objReferralCommission.pTdspercentage + ",modifiedby =" + objReferralCommission.pCreatedby + ",modifieddate=current_timestamp where savingconfigid =" + objReferralCommission.pSavingConfigid + ";");
}
if (count == 0)
{
sbinsert.Append("insert into tblmstSavingAccountConfigreferraldetails(savingconfigid ,savingaccname,isreferralcommissionapplicable,referralcommissioncalfield,referralcommissiontype ,commissionValue,istdsapplicable,tdsaccountid,tdssection,tdspercentage,statusid,createdby,createddate) values(" + objReferralCommission.pSavingConfigid + ",'" + objReferralCommission.pSavingAccname + "'," + objReferralCommission.pIsreferralcommissionapplicable + ",'" + ManageQuote(objReferralCommission.pReferralcommissioncalfield) + "','" + objReferralCommission.pReferralcommissiontype + "'," + objReferralCommission.pCommissionValue + "," + objReferralCommission.pIstdsapplicable + ",'" + ManageQuote(objReferralCommission.pTdsaccountid) + "','" + ManageQuote(objReferralCommission.ptdssection) + "'," + objReferralCommission.pTdspercentage + "," + Convert.ToInt32(Status.Active) + "," + objReferralCommission.pCreatedby + ",current_timestamp);");
}
if (!string.IsNullOrEmpty(sbinsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbinsert.ToString());
}
trans.Commit();
isSaved = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(isSaved);
}
public bool SaveIdentificationdocuments(IdentificationDocumentsDTO obIdentificationDocuments, string connectionstring)
{
bool isSaved = false;
StringBuilder sbinsert = new StringBuilder();
try
{
con = new NpgsqlConnection(connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (obIdentificationDocuments.identificationdocumentsList != null)
{
//if (obIdentificationDocuments.ptypeofoperation == "UPDATE")
//{
//if (obIdentificationDocuments.identificationdocumentsList.Count > 0)
//{
sbinsert.Append("delete from tblmstloanwisedocumentproofs where loanid=" + obIdentificationDocuments.pSavingConfigid + ";");
//}
//}
for (int i = 0; i < obIdentificationDocuments.identificationdocumentsList.Count; i++)
{
if (obIdentificationDocuments.identificationdocumentsList[i].pDocumentRequired == true || obIdentificationDocuments.identificationdocumentsList[i].pDocumentMandatory == true)
{
sbinsert.Append("insert into tblmstloanwisedocumentproofs(loantypeid,loanid,contacttype,documentid,documentgroupid,isdocumentrequired,isdocumentmandatory,statusid,createdby,createddate) values(" + obIdentificationDocuments.pSavingConfigid + "," + obIdentificationDocuments.pSavingConfigid + ",'" + ManageQuote(obIdentificationDocuments.identificationdocumentsList[i].pContactType) + "'," + obIdentificationDocuments.identificationdocumentsList[i].pDocumentId + "," + obIdentificationDocuments.identificationdocumentsList[i].pDocumentgroupId + ",'" + obIdentificationDocuments.identificationdocumentsList[i].pDocumentRequired + "','" + obIdentificationDocuments.identificationdocumentsList[i].pDocumentMandatory + "'," + Convert.ToInt32(Status.Active) + "," + obIdentificationDocuments.identificationdocumentsList[i].pCreatedby + ",current_timestamp);");
}
}
}
if (!string.IsNullOrEmpty(sbinsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbinsert.ToString());
}
trans.Commit();
isSaved = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(isSaved);
}
public bool SaveLoanFacility(LoanFacilityDTO objLoanFacility, string connectionstring)
{
bool isSaved = false;
StringBuilder sbinsert = new StringBuilder();
try
{
con = new NpgsqlConnection(connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (string.IsNullOrEmpty(objLoanFacility.pEligiblepercentage.ToString()))
{
objLoanFacility.pEligiblepercentage = 0;
}
if (string.IsNullOrEmpty(objLoanFacility.pAgeperiod.ToString()))
{
objLoanFacility.pAgeperiod = 0;
}
int count = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "select count(*) from tblmstSavingAccountLoansConfig where savingconfigid=" + objLoanFacility.pSavingConfigid + " and statusid=" + Convert.ToInt32(Status.Active) + ""));
if (count == 0)
{
sbinsert.Append("insert into tblmstSavingAccountLoansConfig(savingconfigid, savingaccname, isloanfacilityapplicable, eligiblepercentage, ageperiod, ageperiodtype, statusid, createdby, createddate)values(" + objLoanFacility.pSavingConfigid + ",'" + objLoanFacility.pSavingAccname + "'," + objLoanFacility.pIsloanfacilityapplicable + "," + objLoanFacility.pEligiblepercentage + "," + objLoanFacility.pAgeperiod + ",'" + objLoanFacility.pAgeperiodtype + "'," + Convert.ToInt32(Status.Active) + "," + objLoanFacility.pCreatedby + ",current_timestamp);");
}
if (count != 0)
{
sbinsert.Append("update tblmstSavingAccountLoansConfig set isloanfacilityapplicable=" + objLoanFacility.pIsloanfacilityapplicable + ", eligiblepercentage=" + objLoanFacility.pEligiblepercentage + ", ageperiod=" + objLoanFacility.pAgeperiod + ", ageperiodtype='" + objLoanFacility.pAgeperiodtype + "',modifiedby =" + objLoanFacility.pCreatedby + ",modifieddate=current_timestamp where savingconfigid =" + objLoanFacility.pSavingConfigid + ";");
}
if (!string.IsNullOrEmpty(sbinsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbinsert.ToString());
}
trans.Commit();
isSaved = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(isSaved);
}
public bool saveGroupConfiguration(GroupCreationDTO groupDetails, string connectionString)
{
int groupSavedCount = 0;
long groupId;
StringBuilder saveGroupConfiguration = new StringBuilder();
try
{
con = new NpgsqlConnection(connectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
groupDetails.pMembersCount = Convert.ToString(groupDetails.pMembersCount) == string.Empty ? 0 : groupDetails.pMembersCount < 0 ? 0 : groupDetails.pMembersCount;
// Master Data
groupId = Convert.ToInt64(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "insert into tblmstgroupconfig(grouptype,groupname,groupcode,membercount,statusid,createdby,createddate) values('" + ManageQuote(groupDetails.pGroupType).Trim() + "','" + ManageQuote(groupDetails.pGroupName).Trim() + "','" + ManageQuote(groupDetails.pGroupCode).Trim() + "'," + groupDetails.pMembersCount + "," + Convert.ToInt32(Status.Active) + "," + groupDetails.pCreatedby + ",current_timestamp) returning groupid;"));
// groupId = 23;
// Child Details
if (groupDetails.pListGroupDetails != null && groupDetails.pListGroupDetails.Count > 0)
{
foreach (GroupCreation childDetails in groupDetails.pListGroupDetails)
{
int count = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "select count(*) from tblgroupnamewisemembers where contactid=" + childDetails.pContactID + " and groupname='" + ManageQuote(groupDetails.pGroupName).Trim() + "';"));
if (count == 0)
{
saveGroupConfiguration.Append("insert into tblgroupnamewisemembers(groupid,groupname,groupno,contactid,contactreferenceid,contactname,contactnumber,grouproleid,roleingroup,statusid,createdby,createddate) values(" + groupId + ",'" + ManageQuote(groupDetails.pGroupName).Trim() + "','" + ManageQuote(groupDetails.pGroupNo).Trim() + "'," + childDetails.pContactID + ",'" + ManageQuote(childDetails.pContactRefId) + "','" + ManageQuote(childDetails.pContactName).Trim() + "','" + ManageQuote(childDetails.pContactNo).Trim() + "'," + (childDetails.pGrouproleID) + ",'" + ManageQuote(childDetails.pRoleInGroup).Trim() + "'," + Convert.ToInt32(Status.Active) + "," + groupDetails.pCreatedby + ",current_timestamp);");
}
}
}
if (Convert.ToString(saveGroupConfiguration) != string.Empty)
{
groupSavedCount = NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, saveGroupConfiguration.ToString());
trans.Commit();
}
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(groupSavedCount > 0 ? true : false);
}
public bool SaveRoleFunction(MenuandNavigationDTO _MenuandNavigationDTO, string Connectionstring)
{
bool Issaved = false;
try
{
con = new NpgsqlConnection(Connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (!string.IsNullOrEmpty(_MenuandNavigationDTO.ptypeofoperation))
{
string ParentFunctionid = Convert.ToString(_MenuandNavigationDTO.PFunctionParentID);
if (_MenuandNavigationDTO.PFunctionParentID == 0)
{
ParentFunctionid = "null";
}
if (_MenuandNavigationDTO.ptypeofoperation.Trim().ToUpper() == "CREATE")
{
_ = NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText: "INSERT INTO tblmstfunctions (functionparentid,parentmoduleid,submoduleid,functionname,functiondescription,functionurl,isfunctionshowinnavigation,isfunctionallowinroles,statusid,createdby,createddate) VALUES (" + ParentFunctionid + "," + _MenuandNavigationDTO.pModuleId + "," + _MenuandNavigationDTO.pSubmoduleId + ", '" + ManageQuote(_MenuandNavigationDTO.pFunctionname).Trim() + "','','" + ManageQuote(_MenuandNavigationDTO.pFunctionurl).Trim() + "','" + _MenuandNavigationDTO.pIsfunctionshowinNavigation + "','" + _MenuandNavigationDTO.pIsFunctionshowinRoles + "'," + Convert.ToInt32(Status.Active) + ", " + _MenuandNavigationDTO.pCreatedby + ", current_timestamp); ");
}
else if (_MenuandNavigationDTO.ptypeofoperation.Trim().ToUpper() == "UPDATE")
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, "Update tblmstfunctions set parentmoduleid=" + _MenuandNavigationDTO.pModuleId + ",submoduleid= '" + ManageQuote(_MenuandNavigationDTO.pModulename).Trim() + "', functionname= '" + ManageQuote(_MenuandNavigationDTO.pFunctionname).Trim() + "',functionurl='" + ManageQuote(_MenuandNavigationDTO.pFunctionurl).Trim() + "',isfunctionshowinnavigation='" + _MenuandNavigationDTO.pIsfunctionshowinNavigation + "',isfunctionallowinroles='" + _MenuandNavigationDTO.pIsFunctionshowinRoles + "',modifiedby=" + _MenuandNavigationDTO.pCreatedby + ",modifieddate=current_timestamp,functionparentid=" + ParentFunctionid + " where functionid=" + _MenuandNavigationDTO.pFunctionId + ";");
}
else if (_MenuandNavigationDTO.ptypeofoperation.Trim().ToUpper() == "DELETE")
{
_ = NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText: "update tblmstfunctions set statusid=" + Convert.ToInt32(Status.Inactive) + ",modifiedby=" + _MenuandNavigationDTO.pCreatedby + ",modifieddate=current_timestamp where functionid=" + _MenuandNavigationDTO.pFunctionId + ";");
}
trans.Commit();
Issaved = true;
}
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
/// <summary>
///
/// </summary>
/// <typeparam name="TResult"></typeparam>
/// <param name="connection"></param>
/// <param name="execute"></param>
/// <param name="transaction"></param>
/// <returns></returns>
private static TResult TransactionalExecute <TResult>(this NpgsqlConnection connection,
Func <TResult> execute,
NpgsqlTransaction transaction)
{
// Variables
var result = default(TResult);
var hasTransaction = (transaction == null || Transaction.Current != null);
// Open
connection.EnsureOpen();
// Ensure transaction
if (hasTransaction == false)
{
transaction = connection.BeginTransaction();
}
try
{
// Execute
if (execute != null)
{
result = execute();
}
// Commit
if (hasTransaction == false)
{
transaction.Commit();
}
}
catch
{
// Rollback
if (hasTransaction == false)
{
transaction.Rollback();
}
// Throw
throw;
}
finally
{
// Dispose
if (hasTransaction == false)
{
transaction.Dispose();
}
}
// Return
return(result);
}
public override void Dispose()
{
_transaction?.Dispose();
_connection.Dispose();
if (_deleteDatabase)
{
DeleteDatabaseAsync(_name).Wait();
}
}
public void TearDown()
{
OnTeardownInTransaction();
transaction.Rollback();
transaction.Dispose();
connection.Close();
connection.Dispose();
OnTeardownAfterTransaction();
}
public bool SaveMemberReceipt(MemberReceiptDTO ObjMemberReceiptDTO, string ConnectionString, out string OUTReceiptid)
{
bool Issaved = false;
StringBuilder sbInsert = new StringBuilder();
bool IsAccountSaved = false;
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (Savegenaralreceipt(ObjMemberReceiptDTO, trans, out OUTReceiptid))
{
IsAccountSaved = true;
}
else
{
trans.Rollback();
return(IsAccountSaved);
}
string Receiptno = OUTReceiptid;
ObjMemberReceiptDTO.pReceiptno = Receiptno;
if (string.IsNullOrEmpty(ObjMemberReceiptDTO.pRecordid.ToString()) || ObjMemberReceiptDTO.pRecordid == 0)
{
sbInsert.Append("insert into Member_receipt (member_id, receipt_date, received_amount, mode_of_receipt, receipt_no, narration, status) values (" + ObjMemberReceiptDTO.pMemberid + ", '" + FormatDate(ObjMemberReceiptDTO.pReceiptdate) + "', " + ObjMemberReceiptDTO.pReceivedamount + ", '" + ObjMemberReceiptDTO.pModeofreceipt + "', '" + ObjMemberReceiptDTO.pReceiptno + "', '" + ObjMemberReceiptDTO.pNarration + "', true);");
}
if (!string.IsNullOrEmpty(sbInsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbInsert.ToString());
}
trans.Commit();
Issaved = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
/// <summary>
/// Gets the ignored <see cref="PersonPermission"/>
/// </summary>
/// <param name="requestDataModelVersion">The requested version</param>
/// <param name="inPersonPermissions">The source <see cref="PersonPermission"/></param>
/// <param name="inPersonRoles">The source <see cref="PersonRole"/></param>
/// <returns>The ignored identifier</returns>
private IReadOnlyList <Guid> GetIgnoredPersonPermissionIds(Version requestDataModelVersion, IReadOnlyList <PersonPermission> inPersonPermissions, IReadOnlyList <PersonRole> inPersonRoles)
{
var excludedPersonPermission = new List <Guid>();
NpgsqlConnection connection = null;
NpgsqlTransaction transaction = null;
try
{
// get prepared data source transaction
var queryPersonPermissions = inPersonRoles.SelectMany(x => x.PersonPermission).Except(inPersonPermissions.Select(x => x.Iid)).ToArray();
transaction = this.TransactionManager.SetupTransaction(ref connection, null);
// if all permissions are in then dont do extra db query
var personPermissions = queryPersonPermissions.Length > 0
? this.PersonPermissionDao.Read(transaction, SiteDirectoryData, queryPersonPermissions, true).Union(inPersonPermissions)
: inPersonPermissions;
foreach (var personPermission in personPermissions)
{
var metainfo = this.MetadataProvider.GetMetaInfo(personPermission.ObjectClass.ToString());
if (string.IsNullOrEmpty(metainfo.ClassVersion) || requestDataModelVersion >= new Version(metainfo.ClassVersion))
{
continue;
}
excludedPersonPermission.Add(personPermission.Iid);
}
return(excludedPersonPermission);
}
catch (Exception e)
{
Logger.Error("Getting participant permission ids failed: {0}", e.Message);
throw;
}
finally
{
if (transaction != null)
{
transaction.Dispose();
}
if (connection != null && connection.State != ConnectionState.Closed)
{
connection.Close();
connection.Dispose();
}
}
}
public static void ReleaseTransaction(ref NpgsqlTransaction pPgsTrans)
{
try
{
pPgsTrans.Commit();
pPgsTrans.Dispose();
pPgsTrans = null;
GC.Collect();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "ReleaseTransaction");
}
}
/// <summary>
/// Releases unmanaged and - optionally - managed resources.
/// </summary>
/// <param name="disposing"><c>true</c> to release both managed and unmanaged resources; <c>false</c> to release only unmanaged resources.</param>
protected virtual void Dispose(bool disposing)
{
if (m_Disposed)
{
return;
}
if (disposing)
{
m_Transaction.Dispose();
m_Connection.Dispose();
m_Disposed = true;
}
}
private void Dispose(bool disposing)
{
if (m_Disposed)
{
return;
}
if (disposing)
{
m_Transaction.Dispose();
m_Connection.Dispose();
m_Disposed = true;
}
}
/// <summary>
/// Validates the username and password
/// </summary>
/// <param name="username">
/// The supplied authenticating username
/// </param>
/// <param name="password">
/// The supplied password
/// </param>
/// <returns>
/// A value representing the authenticated user, null if the user was not authenticated.
/// </returns>
public IUserIdentity Validate(string username, string password)
{
NpgsqlConnection connection = null;
NpgsqlTransaction transaction = null;
try
{
connection = new NpgsqlConnection(Utils.GetConnectionString(AppConfig.Current.Backtier.Database));
connection.Open();
transaction = connection.BeginTransaction();
// resolve person
var credentials = this.PersonResolver.ResolvePerson(transaction, username) as Credentials;
transaction.Commit();
// if the person could not be resolved, drop straight away
if (credentials == null)
{
return(null);
}
// authenticate against authentication providers
return(this.Authenticate(credentials, password) ? credentials : null);
}
catch (Exception ex)
{
if (transaction != null)
{
transaction.Rollback();
}
Logger.Error(ex, "There was an error while authenticating the user credentials");
return(null);
}
finally
{
if (transaction != null)
{
transaction.Dispose();
}
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
}
public bool SaveCinEntry(CinEntryDTO _CinEntryDTO, string ConnectionString)
{
bool isSaved = false;
StringBuilder sbinsert = new StringBuilder();
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
string query = "";
Int64 VoucherId = Convert.ToInt64(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "select recordid from tbltranspaymentvoucher where paymentid = '" + _CinEntryDTO.pVoucherId + "'"));
if (string.IsNullOrEmpty(_CinEntryDTO.ptypeofoperation.ToString()))
{
_CinEntryDTO.ptypeofoperation = "CREATE";
}
if (_CinEntryDTO.ptypeofoperation.ToString().ToUpper() == "CREATE")
{
query = "insert into cin_entry( challana_id, voucher_id, reference_no, paid_date, paid_bank,bsr_code,challana_sl_no,challana_bank,challana_date)values(" + _CinEntryDTO.pChallanaId + "," + VoucherId + ",'" + _CinEntryDTO.pReferenceNo + "','" + FormatDate(_CinEntryDTO.pPaidDate.ToString()) + "','" + _CinEntryDTO.pPaidBank + "','" + _CinEntryDTO.pBsrCode + "','" + _CinEntryDTO.pChallanaSNO + "','" + _CinEntryDTO.pChallanaBank + "','" + FormatDate(_CinEntryDTO.pChallanaDate.ToString()) + "') ";
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, query);
}
trans.Commit();
isSaved = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(isSaved);
}
public void NpgsqlErrorRepro2()
{
NpgsqlConnection connection = new NpgsqlConnection(TheConnectionString);
connection.Open();
NpgsqlTransaction transaction = connection.BeginTransaction();
LargeObjectManager largeObjectMgr = new LargeObjectManager(connection);
try
{
LargeObject largeObject = largeObjectMgr.Open(-1, LargeObjectManager.READWRITE);
transaction.Commit();
}
catch
{
// ignore the LO failure
try
{
transaction.Dispose();
}
catch
{
// ignore dispose failure
}
try
{
connection.Dispose();
}
catch
{
// ignore dispose failure
}
}
using (connection = new NpgsqlConnection(TheConnectionString))
{
connection.Open();
using (NpgsqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM pg_database";
using (NpgsqlDataReader reader = command.ExecuteReader())
{
Assert.IsTrue(reader.Read());
// *1* this fails if the connection for the pool happens to be the bad one from above
Assert.IsTrue(!String.IsNullOrEmpty((string)reader["datname"]));
}
}
}
}
public void Commit()
{
if (transaction is null)
{
throw new Exception("Transaction is not started.");
}
transaction.Commit();
while (!transaction.IsCompleted)
{
continue;
}
transaction.Dispose();
transaction = null;
}
/// <summary>
/// Удалить ресурс и очистить пул подключений, связанный с заданным подключением
/// </summary>
private void DisposeAndClearPool()
{
// Удаление транзакции
if (tran != null)
{
tran.Dispose();
tran = null;
}
// Удаление соединения
conn.ClearPool();
conn.Close();
conn.Dispose();
conn = null;
}
public bool SaveLienentry(LienEntryDTO _lienentryDTO, string connectionstring)
{
bool IsSaved = false;
string qry = string.Empty;
try
{
con = new NpgsqlConnection(connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (_lienentryDTO.ptypeofoperation == "CREATE")
{
qry = "insert into tbltranslienentry(liendate,membercode,fdaccountno,lienamount,liencount,companyname,companybranch,lienadjuestto,receipttype,lienstatus,statusid,createdby,createddate)values('" + FormatDate(_lienentryDTO.pLiendate.ToString()) + "','" + _lienentryDTO.pMembercode + "','" + _lienentryDTO.pFdaccountno + "'," + _lienentryDTO.pLienamount + "," + _lienentryDTO.pLiencount + ",'" + _lienentryDTO.pCompanyname + "','" + _lienentryDTO.pCompanybranch + "','" + _lienentryDTO.pLienadjuestto + "','" + _lienentryDTO.pReceipttype + "','N'," + Convert.ToInt32(Status.Active) + ", " + _lienentryDTO.pCreatedby + ", current_timestamp);";
}
else
{
qry = "Update tbltranslienentry set liendate = '" + FormatDate(_lienentryDTO.pLiendate.ToString()) + "',membercode = '" + _lienentryDTO.pMembercode + "',fdaccountno ='" + _lienentryDTO.pFdaccountno + "',lienamount =" + _lienentryDTO.pLienamount + ",liencount = " + _lienentryDTO.pLiencount + ",companyname = '" + _lienentryDTO.pCompanyname + "',companybranch = '" + _lienentryDTO.pCompanybranch + "',lienadjuestto = '" + _lienentryDTO.pLienadjuestto + "',receipttype = '" + _lienentryDTO.pReceipttype + "',modifiedby = '" + _lienentryDTO.pCreatedby + "',modifieddate= current_timestamp where lienid = " + _lienentryDTO.pLienid + ";";
}
if (!string.IsNullOrEmpty(qry))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, qry);
}
trans.Commit();
IsSaved = true;
}
catch (Exception Ex)
{
trans.Rollback();
throw Ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(IsSaved);
}
public void Insert(string pInsertString)
{
try
{
_tran = connection.BeginTransaction();
cmd.CommandText = pInsertString;
cmd.Connection = connection;
cmd.ExecuteNonQuery();
_tran.Commit();
}
catch (Exception)
{
Message = "Invalid insert: " + pInsertString;
_tran.Dispose();
}
}
public bool SaveFdTransfer(Fdtransfersave _Fdtransfersave, string Connectionstring)
{
bool IsSaved = false;
try
{
con = new NpgsqlConnection(Connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (string.IsNullOrEmpty(_Fdtransfersave.pTransferdate))
{
_Fdtransfersave.pTransferdate = "null";
}
else
{
_Fdtransfersave.pTransferdate = "'" + FormatDate(_Fdtransfersave.pTransferdate) + "'";
}
string SbsaveReferences = string.Empty;
SbsaveReferences = "INSERT INTO Transfer(from_member_id, to_member_id, from_fd_account_id, to_fd_account_id,transfer_date, status) VALUES ( " + _Fdtransfersave.pFromMemberId + ", '" + _Fdtransfersave.pToMemberId + "', '" + _Fdtransfersave.pFromAccountId + "', '" + _Fdtransfersave.pToAccountId + "', " + _Fdtransfersave.pTransferdate + ",'true');";
if (!string.IsNullOrEmpty(SbsaveReferences))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, SbsaveReferences);
trans.Commit();
IsSaved = true;
}
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(IsSaved);
}
public bool DeleteInsuranceMember(string MemberReferenceID, long Userid, string ConnectionString)
{
StringBuilder SbDeleteInsuranceMember = new StringBuilder();
bool IsDeleted = false;
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
int Statusid = Convert.ToInt32(Status.Inactive);
long Memberid = Convert.ToInt64(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "Select memberid from tblmstmembers where upper(membercode)='" + ManageQuote(MemberReferenceID).ToUpper() + "' ;"));
// Nominee Details
SbDeleteInsuranceMember.AppendLine("UPDATE tabapplicationpersonalnomineedetails SET statusid=" + Statusid + ",modifiedby=" + Userid + ",modifieddate=current_timestamp where upper(vchapplicationid)='" + ManageQuote(MemberReferenceID).ToUpper() + "' and applicationid=" + Memberid + " and upper(applicantype)='INSURANCE'; ");
// Insurance Member Details
SbDeleteInsuranceMember.AppendLine("UPDATE tbltransinsurancemember SET statusid=" + Statusid + ",modifiedby=" + Userid + ",modifieddate=current_timestamp where upper(membercode)='" + ManageQuote(MemberReferenceID).ToUpper() + "'; ");
if (Convert.ToString(SbDeleteInsuranceMember) != string.Empty)
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, SbDeleteInsuranceMember.ToString());
trans.Commit();
IsDeleted = true;
}
}
catch (Exception Ex)
{
trans.Rollback();
throw Ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(IsDeleted);
}
public bool SaveSelforAdjustment(SelfAdjustmentConfigDTO Selfadjustment, string Connectionstring)
{
bool IsSaved = false;
StringBuilder SbInsert = new StringBuilder();
try
{
con = new NpgsqlConnection(Connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (string.IsNullOrEmpty(Selfadjustment.pRecordid.ToString()) || Selfadjustment.pRecordid == 0)
{
SbInsert.Append("insert into self_or_adjustment(self_or_adjustment_date,member_id,fd_account_id,company_name,branch_name,group_code_ticket_no,chit_person_name,Payment_type,bank_name,bank_branch,bank_account_no,ifsc_code,status)values('" + Selfadjustment.pTransdate + "'," + Selfadjustment.pMemberid + "," + Selfadjustment.pFdaccountid + ",'" + Selfadjustment.pCompnayname + "','" + Selfadjustment.pBranchname + "','" + Selfadjustment.pGroupcodeticketno + "','" + Selfadjustment.pChitpersonname + "','" + Selfadjustment.pPaymenttype + "','" + Selfadjustment.pBankname + "','" + Selfadjustment.pBranchname + "','" + Selfadjustment.pBankaccountno + "','" + Selfadjustment.pIfsccode + "',true);");
}
else
{
SbInsert.Append("update table self_or_adjustment set self_or_adjustment_date='" + Selfadjustment.pTransdate + "',company_name='" + ManageQuote(Selfadjustment.pCompnayname) + "',branch_name='" + ManageQuote(Selfadjustment.pBranchname) + ",member_id=" + Selfadjustment.pMemberid + ",fd_account_id=" + Selfadjustment.pFdaccountid + ",group_code_ticket_no='" + ManageQuote(Selfadjustment.pGroupcodeticketno) + "',chit_person_name='" + ManageQuote(Selfadjustment.pChitpersonname) + "',Payment_type='" + ManageQuote(Selfadjustment.pPaymenttype) + "',bank_name='" + ManageQuote(Selfadjustment.pBankname) + "',bank_branch='" + ManageQuote(Selfadjustment.pBankbranch) + "',bank_account_no='" + ManageQuote(Selfadjustment.pBankaccountno) + "',ifsc_code='" + ManageQuote(Selfadjustment.pIfsccode) + "' where recordid=" + Selfadjustment.pRecordid + ")");
}
if (!string.IsNullOrEmpty(SbInsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, SbInsert.ToString());
}
trans.Commit();
IsSaved = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(IsSaved);
}
public bool SaveBondsPrint(Bonds_PrintDTO ObjBondsprint, string Connectionstring)
{
bool Issaved = false;
StringBuilder sbInsert = new StringBuilder();
try
{
con = new NpgsqlConnection(Connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (ObjBondsprint.lstBonds_Print != null)
{
for (int i = 0; i < ObjBondsprint.lstBonds_Print.Count; i++)
{
sbInsert.Append("insert into Bonds_Print(fd_account_id, print_date, print_status)values(" + ObjBondsprint.lstBonds_Print[i].pDeposit_id + ",'" + FormatDate(ObjBondsprint.lstBonds_Print[i].pPrint_Date) + "','N');");
}
}
if (!string.IsNullOrEmpty(sbInsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbInsert.ToString());
}
trans.Commit();
Issaved = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
