public ShareschemeandcodeCount GetShareNameCodeCount(Int64 shareid, string ShareName, string ShareCode, string ConnectionString)
{
ShareschemeandcodeCount _ShareschemeandcodeCount = new ShareschemeandcodeCount();
try
{
if (shareid == 0)
{
_ShareschemeandcodeCount.pSchemeCount = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "select count(*) from tblmstshareconfig where upper(sharename)='" + ManageQuote(ShareName).ToUpper() + "' and statusid=" + Convert.ToInt32(Status.Active) + ";"));
_ShareschemeandcodeCount.pSchemeCodeCount = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "select count(*) from tblmstshareconfig where upper(sharecode)='" + ManageQuote(ShareCode).ToUpper() + "' and statusid=" + Convert.ToInt32(Status.Active) + ";"));
}
else
{
_ShareschemeandcodeCount.pSchemeCount = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "select count(*) from tblmstshareconfig where upper(sharename)='" + ManageQuote(ShareName).ToUpper() + "' and shareconfigid <> " + shareid + " and statusid=" + Convert.ToInt32(Status.Active) + ";"));
_ShareschemeandcodeCount.pSchemeCodeCount = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "select count(*) from tblmstshareconfig where upper(sharecode)='" + ManageQuote(ShareCode).ToUpper() + "' and shareconfigid <> " + shareid + " and statusid=" + Convert.ToInt32(Status.Active) + ";"));
}
}
catch (Exception)
{
throw;
}
return(_ShareschemeandcodeCount);
}
public RefferalDetailsDTO GetRefferalDetails(string contactrefID, string ConnectionString, long contactid)
{
string Query = string.Empty;
RefferalDetailsDTO _RefferalDetailsDTO = new RefferalDetailsDTO();
string applicationid = string.Empty;
try
{
_RefferalDetailsDTO.pStatusName = Convert.ToString(NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "SELECT statusname from tblmstreferral t1 join tblmststatus t2 on t1.statusid=t2.statusid where contactid=" + contactid));
_RefferalDetailsDTO.ploansamount = Convert.ToDecimal(NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "select coalesce( sum(coalesce(approvedloanamount,0)),0) from tbltransapprovedapplications where vchapplicationid in (select vchapplicationid from tabapplication where referralcontactrefid ='" + (contactrefID.Trim().ToUpper()) + "')"));
_RefferalDetailsDTO.pcommissionpaidamount = 0;
_RefferalDetailsDTO.pcommissindueamount = 0;
_RefferalDetailsDTO.pfdamount = 0;
_RefferalDetailsDTO.prdamount = 0;
_RefferalDetailsDTO.psdamount = 0;
_RefferalDetailsDTO.pbusinessamount = _RefferalDetailsDTO.ploansamount + _RefferalDetailsDTO.pfdamount + _RefferalDetailsDTO.prdamount + _RefferalDetailsDTO.psdamount;
}
catch (Exception)
{
throw;
}
return(_RefferalDetailsDTO);
}
public bool SaveChallanaEntry(ChallanaEntryDTO _ChallanaEntryDTO, 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 = "";
long ChallanaId = 0;
if (string.IsNullOrEmpty(Convert.ToString(_ChallanaEntryDTO.pChallanaNo)))
{
_ChallanaEntryDTO.pChallanaNo = NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "SELECT FN_GENERATENEXTID('CHALLANA ENTRY', 'CHALLANA', current_date)").ToString();
//_ChallanaEntryDTO.pChallanaNo =Convert.ToString(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "SELECT FN_GENERATENEXTID('CHALLANA ENTRY','CHALLANA',current_date);"));
}
if (string.IsNullOrEmpty(_ChallanaEntryDTO.ptypeofoperation.ToString()))
{
_ChallanaEntryDTO.ptypeofoperation = "CREATE";
}
if (_ChallanaEntryDTO.ptypeofoperation.ToString().ToUpper() == "CREATE")
{
query = "insert into challana_entry( challana_no, from_date, to_date, company_type, tds_percent,calc_tds,actual_tds,paid_Amount,companyid)values('" + _ChallanaEntryDTO.pChallanaNo + "', '" + FormatDate(_ChallanaEntryDTO.pFromDate.ToString()) + "', '" + FormatDate(_ChallanaEntryDTO.pToDate.ToString()) + "', '" + _ChallanaEntryDTO.pCompanyType + "','" + _ChallanaEntryDTO.pTdsType + "'," + _ChallanaEntryDTO.pTotalTdsAmount + "," + _ChallanaEntryDTO.pActualTotalTdsAmount + "," + _ChallanaEntryDTO.pTotalPaidAmount + "," + _ChallanaEntryDTO.pCompanyId + ") returning challana_id";
ChallanaId = Convert.ToInt64(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, query));
}
if (_ChallanaEntryDTO._ChallanaEntryDetails != null)
{
for (int i = 0; i < _ChallanaEntryDTO._ChallanaEntryDetails.Count; i++)
{
sbinsert.Append("insert into challana_entry_details( challana_id, tds_voucher_id,parent_id,account_id,panno,amount,calc_tds,actual_tds,balance,paidamt,status)values (" + ChallanaId + "," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pTdsVoucherId + "," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pParentId + "," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pAccountId + ",'" + _ChallanaEntryDTO._ChallanaEntryDetails[i].pPanNo + "'," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pAmount + "," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pTdsAmount + "," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pActualTdsAmount + "," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pBalance + "," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pPaidAmount + ",'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);
}
public MemberschemeandcodeCount GetMemberNameCount(Int64 memberid, string MemberType, string MemberTypeCode, string ConnectionString)
{
MemberschemeandcodeCount _memberschemeandcodeCount = new MemberschemeandcodeCount();
try
{
if (memberid == 0)
{
_memberschemeandcodeCount.pSchemeCount = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "select count(*) from tblmstmembertypes where upper(membertype)='" + ManageQuote(MemberType).ToUpper() + "' and statusid=" + Convert.ToInt32(Status.Active) + ";"));
_memberschemeandcodeCount.pSchemeCodeCount = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "select count(*) from tblmstmembertypes where upper(membertypecode)='" + ManageQuote(MemberTypeCode).ToUpper() + "' and statusid=" + Convert.ToInt32(Status.Active) + ";"));
}
else
{
_memberschemeandcodeCount.pSchemeCount = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "select count(*) from tblmstmembertypes where upper(membertype)='" + ManageQuote(MemberType).ToUpper() + "' and membertypeid <> " + memberid + " and statusid=" + Convert.ToInt32(Status.Active) + ";"));
_memberschemeandcodeCount.pSchemeCodeCount = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "select count(*) from tblmstmembertypes where upper(membertypecode)='" + ManageQuote(MemberTypeCode).ToUpper() + "' and membertypeid <> " + memberid + " and statusid=" + Convert.ToInt32(Status.Active) + ";"));
}
}
catch (Exception)
{
throw;
}
return(_memberschemeandcodeCount);
}
public List <MemberEnquiryDTO> GetMemberEnquiryDetailsReport(string FdAccountNo, string ConnectionString)
{
long Memberid = 0;
List <MemberEnquiryDTO> LstMemberEnquiryDetails = new List <MemberEnquiryDTO>();
Memberid = Convert.ToInt64(NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "select memberid from tbltransfdcreation where fdaccountno ='" + FdAccountNo + "'"));
try
{
MemberEnquiryDTO _objMemberEnquiryDetailsDTO = new MemberEnquiryDTO
{
pContactImagePath = Convert.ToString(NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "select contactimagepath from tblmstcontact tc join tblmstmembers tm on tc.contactid=tm.contactid where tm.memberid=" + Memberid + "; ")),
LstMemberDetails = GetMemberDetailsByid(Memberid, ConnectionString),
LstMemberBankDetails = GetMemberBankDetails(Memberid, ConnectionString),
LstMemberTransactionDetails = GetMemberTransactionDetailsByid(FdAccountNo, ConnectionString),
LstMemberReceiptDetails = GetMemberReceiptDetails(FdAccountNo, ConnectionString),
LstMemberNomieeDetails = GetMemberNomineeDetails(FdAccountNo, ConnectionString),
LstMemberInterestPaymentDetails = GetMemberInterestPaymentDetails(FdAccountNo, ConnectionString),
LstMemberPromoterSalarytDetails = GetMemberPromoterSalaryDetails(FdAccountNo, ConnectionString),
LstMemberLientDetails = GetMemberLiensDetails(FdAccountNo, ConnectionString),
LstMemberMaturityBondDetails = GetMemberMaturityBondsDetails(FdAccountNo, ConnectionString),
LstMemberMaturityPaymentsDetails = GetMemberMaturityPaymentsDetails(FdAccountNo, ConnectionString),
};
LstMemberEnquiryDetails.Add(_objMemberEnquiryDetailsDTO);
}
catch (Exception ex)
{
throw ex;
}
return(LstMemberEnquiryDetails);
}
public int checkInsertAccNameandCodeDuplicates(string checkparamtype, string Accname, string Acccode, Int64 SavingAccid, string connectionstring)
{
int count = 0;
try
{
if (checkparamtype.ToUpper() == "ACCNAME")
{
if (string.IsNullOrEmpty(SavingAccid.ToString()) || SavingAccid == 0)
{
count = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(connectionstring, CommandType.Text, "select count(*) from tblmstSavingAccountConfig where upper(savingaccname)='" + ManageQuote(Accname.Trim().ToUpper()) + "' and statusid=" + Convert.ToInt32(Status.Active) + ""));
}
else
{
count = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(connectionstring, CommandType.Text, "select count(*) from tblmstSavingAccountConfig where upper(savingaccname)='" + ManageQuote(Accname.Trim().ToUpper()) + "' and savingconfigid!=" + SavingAccid + " and statusid=" + Convert.ToInt32(Status.Active) + ";"));
}
}
else
{
if (string.IsNullOrEmpty(SavingAccid.ToString()) || SavingAccid == 0)
{
count = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(connectionstring, CommandType.Text, "select count(*) from tblmstSavingAccountConfig where upper(savingacccode)='" + ManageQuote(Acccode.Trim().ToUpper()) + "' and statusid=" + Convert.ToInt32(Status.Active) + ""));
}
else
{
count = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(connectionstring, CommandType.Text, "select count(*) from tblmstSavingAccountConfig where upper(savingacccode)='" + ManageQuote(Acccode.Trim().ToUpper()) + "' and savingconfigid!=" + SavingAccid + " and statusid=" + Convert.ToInt32(Status.Active) + ";"));
}
}
}
catch (Exception ex)
{
throw ex;
}
return(count);
}
public async Task <string> GetRoleName(long EmployeeID, string ConnectionString)
{
string RoleName = string.Empty;
await Task.Run(() =>
{
return(RoleName = Convert.ToString(NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "select rolename from tblmstemployeeemploymentdetails where employeeid=" + EmployeeID + " and statusid=1;")));
});
return(RoleName);
}
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 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 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 int checkMemberCountinMaster(string ContactReferenceId, long InsuranceID, string Connectionstring, string InsuranceType, long Recordid)
{
try
{
return(Convert.ToInt32(NPGSqlHelper.ExecuteScalar(Connectionstring, CommandType.Text, "select count(*) from tbltransinsurancemember where upper(insurancetype)='" + ManageQuote(InsuranceType).ToUpper() + "' and insuranceschemeconfigid = " + InsuranceID + " and upper(membercode)='" + ManageQuote(ContactReferenceId).ToUpper() + "' and recordid!=" + Recordid + " and statusid=" + Convert.ToInt32(Status.Active) + ";")));
}
catch (Exception)
{
throw;
}
}