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