public List <FDMemberDetailsDTO> GetMemberDetails(string MemberType, string BranchName, string Connectionstring)
{
List <FDMemberDetailsDTO> lstMemberDetails = new List <FDMemberDetailsDTO>();
try
{
//select distinct tm.memberid,tm.Membername, tm.membercode, tc.contactid, tc.contactreferenceid, tm.membertype, tc.name, tc.businessentitycontactno from tblmstmembers tm join tblmstcontact tc on tm.contactid = tc.contactid join tbltransfdcreation tf on tm.memberid = tf.memberid where upper(tm.membertype) = '" + ManageQuote(MemberType.ToUpper()) + "' and tf.chitbranchname = '"+ BranchName + "' and tm.statusid = 1 and tf.statusid = 1
//string query = "select distinct memberid,membercode,membername,contactid,contactreferenceid,membertype,mobileno from vwfdtransaction_details where upper(membertype)='" + ManageQuote(MemberType.ToUpper()) + "' and chitbranchname ='" + BranchName + "' and balanceamount>0 order by membername";
string query = "select * from (select distinct memberid,membercode,membername,fd.contactid,fd.contactreferenceid,membertype,mobileno,count(case when contacttype='Business Entity' then '1' else tn.vchapplicationid end)count from vwfdtransaction_details fd left join tabapplicationpersonalnomineedetails tn on tn.vchapplicationid=fd.fdaccountno where upper(membertype)='" + ManageQuote(MemberType.ToUpper()) + "' and balanceamount>0 and accountstatus='N' group by memberid,membercode,membername,fd.contactid,fd.contactreferenceid,membertype,mobileno order by membername)tbl where count>0";
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(Connectionstring, CommandType.Text, query))
{
while (dr.Read())
{
FDMemberDetailsDTO objMemberdetails = new FDMemberDetailsDTO();
objMemberdetails.pMemberid = dr["memberid"];
objMemberdetails.pMembercode = dr["membercode"];
objMemberdetails.pName = dr["Membername"];
objMemberdetails.pConid = dr["contactid"];
objMemberdetails.pContactreferenceid = dr["contactreferenceid"];
objMemberdetails.pMembertype = dr["membertype"];
objMemberdetails.pBusinessentitycontactno = dr["mobileno"];
lstMemberDetails.Add(objMemberdetails);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lstMemberDetails);
}
public async Task <List <ShareviewDTO> > GetSharNames(string Membertype, string Applicanttype, string ConnectionString)
{
List <ShareviewDTO> lstShareviewDTO = new List <ShareviewDTO>();
await Task.Run(() =>
{
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select distinct shareconfigid,sharename from tblmstshareconfigdetails where membertype='" + ManageQuote(Membertype) + "' and applicanttype='" + ManageQuote(Applicanttype) + "' and statusid=" + Convert.ToInt32(Status.Active) + ";"))
{
while (dr.Read())
{
ShareviewDTO ShareviewDTO = new ShareviewDTO();
ShareviewDTO.pshareconfigid = Convert.ToInt64(dr["shareconfigid"]);
ShareviewDTO.psharename = Convert.ToString(dr["sharename"]);
lstShareviewDTO.Add(ShareviewDTO);
}
}
}
catch (Exception)
{
throw;
}
});
return(lstShareviewDTO);
}
public List <SavingAccNameDetails> GetSavingAccountNameDetails(string ConnectionString)
{
List <SavingAccNameDetails> _SavingAccNameDetails = new List <SavingAccNameDetails>();
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select savingconfigid, savingaccname, savingaccnamecode from tblmstsavingaccountconfig where savingconfigid in (select distinct savingconfigid from tbltranssavingaccountcreation where statusid = " + Convert.ToInt32(Status.Active) + ") order by savingaccname;"))
{
while (dr.Read())
{
SavingAccNameDetails _objSavingAccNameDetails = new SavingAccNameDetails();
_objSavingAccNameDetails.pSavingConfigid = Convert.ToInt64(dr["savingconfigid"]);
_objSavingAccNameDetails.pSavingAccname = Convert.ToString(dr["savingaccname"]);
_objSavingAccNameDetails.pSavingAccNameCode = Convert.ToString(dr["savingaccnamecode"]);
_SavingAccNameDetails.Add(_objSavingAccNameDetails);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(_SavingAccNameDetails);
}
public async Task <List <ChitBranchDetails> > GetFDBranchDetails(string Connectionstring)
{
List <ChitBranchDetails> _ChitBranchDetailsList = new List <ChitBranchDetails>();
await Task.Run(() =>
{
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(Connectionstring, CommandType.Text, "select distinct chitbranchid,chitbranchname from vwfdtransaction_details where balanceamount<=0 and interestpayout<>'On Maturity' and fdaccountid not in(select trans_type_id from maturity_bonds) and fdaccountid not in(select fd_account_id from self_or_adjustment) order by chitbranchname;"))
{
while (dr.Read())
{
var _ChitBranchDetails = new ChitBranchDetails
{
pBranchId = Convert.ToInt64(dr["chitbranchid"]),
pBranchname = dr["chitbranchname"],
};
_ChitBranchDetailsList.Add(_ChitBranchDetails);
}
}
}
catch (Exception)
{
throw;
}
});
return(_ChitBranchDetailsList);
}
public async Task <List <shareconfigDetails> > GetSharconfigdetails(long shareconfigid, string Applicanttype, string ConnectionString)
{
List <shareconfigDetails> lstShareconfigdetails = new List <shareconfigDetails>();
await Task.Run(() =>
{
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select shareconfigid,sharename,applicanttype,facevalue,minshares,maxshares from tblmstshareconfigdetails where shareconfigid =" + shareconfigid + " and applicanttype='" + ManageQuote(Applicanttype) + "' and statusid=" + Convert.ToInt32(Status.Active) + ";"))
{
while (dr.Read())
{
shareconfigDetails objshareconfigDetails = new shareconfigDetails();
objshareconfigDetails.pShareconfigid = Convert.ToInt64(dr["shareconfigid"]);
objshareconfigDetails.pSharename = Convert.ToString(dr["sharename"]);
objshareconfigDetails.pApplicanttype = Convert.ToString(dr["applicanttype"]);
objshareconfigDetails.pFacevalue = Convert.ToInt64(dr["facevalue"]);
objshareconfigDetails.pMinshare = Convert.ToInt64(dr["minshares"]);
objshareconfigDetails.pMaxshare = Convert.ToInt64(dr["maxshares"]);
lstShareconfigdetails.Add(objshareconfigDetails);
}
}
}
catch (Exception)
{
throw;
}
});
return(lstShareconfigdetails);
}
public async Task <List <FdAccountDTO> > GetFdAcnumbers(string branchname, int memberid, Int64 fdconfigid, string ConnectionString)
{
List <FdAccountDTO> lstFDAccountno = new List <FdAccountDTO>();
await Task.Run(() =>
{
try
{
//select fdaccountid, fdaccountno, membername from vwfddata where chitbranchname = '" + branchname + "' and memberid = " + memberid + " and fdconfigid = "+fdconfigid+" and fdaccountid not in(select fd_account_id from self_or_adjustment)
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select fdaccountid,fdaccountno,membername from vwfdtransaction_details where chitbranchname='" + branchname + "' and memberid=" + memberid + " and fdconfigid=" + fdconfigid + " and balanceamount<=0 and interestpayout<>'On Maturity' and fdaccountid not in(select trans_type_id from maturity_bonds) and fdaccountid not in(select fd_account_id from self_or_adjustment) ;"))
{
while (dr.Read())
{
FdAccountDTO objFDAccountno = new FdAccountDTO();
objFDAccountno.pFdaccountid = dr["fdaccountid"];
objFDAccountno.pFdaccountnumber = dr["fdaccountno"];
objFDAccountno.pMembername = dr["membername"];
lstFDAccountno.Add(objFDAccountno);
}
}
}
catch (Exception ex)
{
throw ex;
}
});
return(lstFDAccountno);
}
public async Task <List <SelfBankDetailsDTO> > GetBankDetails(int Contactid, string ConnectionString)
{
List <SelfBankDetailsDTO> lstBankdetails = new List <SelfBankDetailsDTO>();
await Task.Run(() =>
{
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select bankname,accountno,ifsccode,branch from tabapplicationpersonalbankdetails where contactid=" + Contactid + " and isprimarybank=true ;"))
{
while (dr.Read())
{
SelfBankDetailsDTO objBankdetails = new SelfBankDetailsDTO();
objBankdetails.pBankname = dr["bankname"];
objBankdetails.pAccountno = dr["accountno"];
objBankdetails.pIfsccode = dr["ifsccode"];
objBankdetails.pBranhname = dr["branch"];
lstBankdetails.Add(objBankdetails);
}
}
}
catch (Exception ex)
{
throw ex;
}
});
return(lstBankdetails);
}
public List <ViewtodayreceiptsDTO> Viewtodayreceipts(string fromdate, string todate, string ConnectionString, string formname)
{
lstViewtodayreceipts = new List <ViewtodayreceiptsDTO>();
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select receiptdate,receiptno,te.vchapplicationid,ta.applicantname,modeofpayment,bank,referencenumber,totalreceived,narration from tbltransemireceipt te join tabapplication ta on te.vchapplicationid=ta.vchapplicationid where receiptdate between '" + FormatDate(fromdate.ToString()) + "' and '" + FormatDate(todate.ToString()) + "' and upper(formname)='" + formname + "' order by emiid desc"))
while (dr.Read())
{
ViewtodayreceiptsDTO objtodayreceipts = new ViewtodayreceiptsDTO();
if (dr["receiptdate"] != DBNull.Value)
{
objtodayreceipts.pReceiptdate = Convert.ToDateTime(dr["receiptdate"]).ToString("dd/MM/yyyy");
}
else
{
objtodayreceipts.pReceiptdate = "";
}
objtodayreceipts.pReceiptno = dr["receiptno"].ToString();
objtodayreceipts.pLoanno = dr["vchapplicationid"].ToString();
objtodayreceipts.pCustomername = dr["applicantname"].ToString();
objtodayreceipts.pModeofreceipt = dr["modeofpayment"].ToString();
objtodayreceipts.pBankname = dr["bank"].ToString();
objtodayreceipts.pChequeno = dr["referencenumber"].ToString();
objtodayreceipts.pTotalreceived = Convert.ToDecimal(dr["totalreceived"]);
objtodayreceipts.pNarration = dr["narration"].ToString();
lstViewtodayreceipts.Add(objtodayreceipts);
}
}
catch (Exception ex)
{
throw ex;
}
return(lstViewtodayreceipts);
}
public List <UserInfo> ShowUserName()
{
List <UserInfo> lstemployeename = new List <UserInfo>();
try
{
string str = "SELECT userid, upper(username) as username FROM tabuserinfo where statusid=1 ORDER BY username";
npgdr = NPGSqlHelper.ExecuteReader(NPGSqlHelper.SQLConnString, CommandType.Text, str);
while (npgdr.Read())
{
UserInfo objUserInfoDTO = new UserInfo();
objUserInfoDTO.UserID = npgdr["userid"].ToString();
objUserInfoDTO.UserName = npgdr["username"].ToString();
lstemployeename.Add(objUserInfoDTO);
}
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "UserRights");
}
finally
{
npgdr.Dispose();
}
return(lstemployeename);
}
public List <FDDetailsDTO> GetFdDetails(string MemberCode, string ChitBranch, string Connectionstring)
{
List <FDDetailsDTO> lstFDDetails = new List <FDDetailsDTO>();
try
{
//select fdaccountid, fdaccountno, membername, chitbranchname, depositamount, accountid from tbltransfdcreation where upper(membercode) = '" + ManageQuote(MemberCode.ToUpper()) + "' and upper(chitbranchname)= '"+ManageQuote(ChitBranch.ToUpper())+"'
//string query = "select fdaccountid,fdaccountno,membername,chitbranchname,depositamount,accountid from vwfdtransaction_details where upper(membercode) ='" + ManageQuote(MemberCode.ToUpper()) + "' and upper(chitbranchname)='" + ManageQuote(ChitBranch.ToUpper()) + "' and balanceamount>0 ";
string query = "select * from (select fdaccountid,fdaccountno,membername,chitbranchname,depositamount,accountid,count(case when contacttype='Business Entity' then '1' else tn.vchapplicationid end)count from vwfdtransaction_details fd left join tabapplicationpersonalnomineedetails tn on tn.vchapplicationid=fd.fdaccountno where upper(membercode) ='" + ManageQuote(MemberCode.ToUpper()) + "' and balanceamount>0 group by fdaccountid,fdaccountno,membername,chitbranchname,depositamount,accountid)tbl where count>0 ";
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(Connectionstring, CommandType.Text, query))
{
while (dr.Read())
{
FDDetailsDTO objFDdetails = new FDDetailsDTO();
objFDdetails.pFdaccountid = Convert.ToInt64(dr["fdaccountid"]);
objFDdetails.pFdaccountno = dr["fdaccountno"];
objFDdetails.pMembername = dr["membername"];
objFDdetails.pChitbranchname = dr["chitbranchname"];
//objFDdetails.pDeposiamount = dr["depositamount"];
//objFDdetails.pAccountno = dr["accountid"];
lstFDDetails.Add(objFDdetails);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lstFDDetails);
}
private List <TransactionsDTO> GetTransactionslist(string loanid, string ConnectionString)
{
lstTransactions = new List <TransactionsDTO>();
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, " select totalreceived,receiptno,receiptdate,narration,case when chrclearstatus in('N','P') then 'Not Cleared' when chrclearstatus='R' then 'Return' else 'Cleared' end as chequestatus from tbltransemireceipt where vchapplicationid='" + ManageQuote(loanid) + "' and chrclearstatus in('N','P','Y','') order by emiid desc;;"))
while (dr.Read())
{
TransactionsDTO objTransactions = new TransactionsDTO();
objTransactions.pReceiptamount = Convert.ToDecimal(dr["totalreceived"]);
objTransactions.pReceiptno = dr["receiptno"].ToString();
objTransactions.pNarration = dr["narration"].ToString();
if (dr["receiptdate"] != DBNull.Value)
{
objTransactions.pReceiptdate = Convert.ToDateTime(dr["receiptdate"]).ToString("dd/MM/yyyy");
}
else
{
objTransactions.pReceiptdate = "";
}
objTransactions.pChequestatus = dr["chequestatus"].ToString();
lstTransactions.Add(objTransactions);
}
}
catch (Exception)
{
throw;
}
return(lstTransactions);
}
public async Task <List <ChitBranchDetails> > GetFDReceiptBranchDetails(string Connectionstring)
{
List <ChitBranchDetails> _ChitBranchDetailsList = new List <ChitBranchDetails>();
await Task.Run(() =>
{
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(Connectionstring, CommandType.Text, "select * from (select distinct chitbranchid,chitbranchname,count(case when contacttype='Business Entity' then '1' else tn.vchapplicationid end)count from vwfdtransaction_details fd left join tabapplicationpersonalnomineedetails tn on tn.vchapplicationid=fd.fdaccountno where balanceamount >0 group by chitbranchid,chitbranchname order by chitbranchname)tbl where count>0"))
{
while (dr.Read())
{
var _ChitBranchDetails = new ChitBranchDetails
{
pBranchId = Convert.ToInt64(dr["chitbranchid"]),
pBranchname = dr["chitbranchname"],
};
_ChitBranchDetailsList.Add(_ChitBranchDetails);
}
}
}
catch (Exception)
{
throw;
}
});
return(_ChitBranchDetailsList);
}
public async Task <List <ChitBranchDetails> > GetFDBranchDetails(string Connectionstring)
{
List <ChitBranchDetails> _ChitBranchDetailsList = new List <ChitBranchDetails>();
await Task.Run(() =>
{
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(Connectionstring, CommandType.Text, "select distinct tc.code, tc.branchname, tc.vchregion, tc.vchzone from tabbranchcodes tc join tbltransfdcreation tf on tc.code=tf.chitbranchid::numeric(9,0) where tf.statusid=" + Convert.ToInt32(Status.Active) + " order by tc.branchname;"))
{
while (dr.Read())
{
var _ChitBranchDetails = new ChitBranchDetails
{
pBranchId = Convert.ToInt64(dr["code"]),
pBranchname = dr["branchname"],
pVchRegion = dr["vchregion"],
pVchZone = dr["vchzone"]
};
_ChitBranchDetailsList.Add(_ChitBranchDetails);
}
}
}
catch (Exception)
{
throw;
}
});
return(_ChitBranchDetailsList);
}
public List <FDReceiptDetailsDTO> GetFDReceiptDetails(string FromDate, string Todate, string Connectionstring)
{
List <FDReceiptDetailsDTO> lstFDReceiptDetailsbyid = new List <FDReceiptDetailsDTO>();
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(Connectionstring, CommandType.Text, "select tm.membername,tm.membercode,ft.fdaccountno,to_char(fr.fd_receiptt_date, 'dd/Mon/yyyy')fd_receiptt_date,ft.depositamount as dueamount,fr.received_amount,fr.mode_of_receipt,fr.receipt_no,(case when tt.clearstatus = 'R' OR tt.clearstatus = 'C' OR tt.depositstatus = 'C' then 'Cancelled' when tt.clearstatus = 'Y' then 'Cleared' when tt.clearstatus IS NULL then 'Cleared' else 'Un-Cleared' end)as ChequeStatus from fd_receipt fr join tblmstmembers tm on fr.member_id = tm.memberid join tbltransfdcreation ft on fr.fd_account_id = ft.fdaccountid and fr.status = true left join tbltransreceiptreference tt ON tt.receiptid = fr.receipt_no where fd_receiptt_date between '" + FormatDate(FromDate) + "' and '" + FormatDate(Todate) + "' order by fd_receipt_id desc; "))
{
while (dr.Read())
{
FDReceiptDetailsDTO objFDReceiptdetailsbyid = new FDReceiptDetailsDTO();
objFDReceiptdetailsbyid.pMembername = dr["membername"];
objFDReceiptdetailsbyid.pMembercode = dr["membercode"];
objFDReceiptdetailsbyid.pFdaccountno = dr["fdaccountno"];
objFDReceiptdetailsbyid.pReceiptdate = Convert.ToString(dr["fd_receiptt_date"]);
objFDReceiptdetailsbyid.pDueamount = dr["dueamount"];
objFDReceiptdetailsbyid.pReceivedAmount = dr["received_amount"];
objFDReceiptdetailsbyid.pModeOfReceipt = dr["mode_of_receipt"];
objFDReceiptdetailsbyid.pReceiptno = dr["receipt_no"];
objFDReceiptdetailsbyid.pChequestatus = dr["chequestatus"];
lstFDReceiptDetailsbyid.Add(objFDReceiptdetailsbyid);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lstFDReceiptDetailsbyid);
}
public async Task <List <SchemeTypeDTO> > GetSchemeType(string BranchName, string ConnectionString)
{
List <SchemeTypeDTO> lstSchemetype = new List <SchemeTypeDTO>();
string query = string.Empty;
await Task.Run(() =>
{
try
{
//query = "select distinct tc.fdname,tc.fdconfigid from tblmstfixeddepositConfig tc join tbltransfdcreation tf on tc.fdconfigid=tf.fdconfigid and tf.statusid=" + Convert.ToInt32(Status.Active) + " where chitbranchname='"+ BranchName + "' AND tc.statusid=" + Convert.ToInt32(Status.Active) + " order by fdname";
query = "select distinct fdconfigid,fdname from vwfdtransaction_details where chitbranchname='" + BranchName + "' and balanceamount<=0 and interestpayout<>'On Maturity' and fdaccountid not in(select trans_type_id from maturity_bonds) and fdaccountid not in(select fd_account_id from self_or_adjustment) order by fdname";
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, query))
{
while (dr.Read())
{
SchemeTypeDTO objSchemetypes = new SchemeTypeDTO();
objSchemetypes.pSchemeid = dr["fdconfigid"];
objSchemetypes.pSchemeName = dr["fdname"];
lstSchemetype.Add(objSchemetypes);
}
}
}
catch (Exception ex)
{
throw ex;
}
});
return(lstSchemetype);
}
public List <ModuleDTO> ShowModuleName()
{
List <ModuleDTO> lstModule = new List <ModuleDTO>();
try
{
string str = "select modulename,moduleid from tabmodules where statusid=1 and moduletype in ('RMS','HRMS');";
npgdr = NPGSqlHelper.ExecuteReader(NPGSqlHelper.SQLConnString, CommandType.Text, str);
while (npgdr.Read())
{
ModuleDTO objModuleDTO = new ModuleDTO();
objModuleDTO.Moduleid = Convert.ToInt32(npgdr["moduleid"]);
objModuleDTO.Modulename = npgdr["modulename"].ToString();
lstModule.Add(objModuleDTO);
}
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "UserRights");
}
finally
{
npgdr.Dispose();
}
return(lstModule);
}
public async Task <List <MembersDTO> > GetMembers(string branchname, Int64 fdconfigid, string ConnectionString)
{
List <MembersDTO> lstMembers = new List <MembersDTO>();
await Task.Run(() =>
{
try
{
//select distinct contactid,memberid,membercode,membername,mobileno from vwfddata where chitbranchname = '" + branchname + "' and fdconfigid = "+ fdconfigid + " order by membername
string query = "select distinct memberid,membercode,membername,mobileno,contactid from vwfdtransaction_details where chitbranchname='" + branchname + "' and fdconfigid=" + fdconfigid + " and balanceamount<=0 and interestpayout<>'On Maturity' and fdaccountid not in(select trans_type_id from maturity_bonds) and fdaccountid not in(select fd_account_id from self_or_adjustment) order by membername";
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, query))
{
while (dr.Read())
{
MembersDTO objMembers = new MembersDTO();
objMembers.pMemberid = dr["memberid"];
objMembers.pMembercode = dr["membercode"];
objMembers.pMembername = dr["membername"];
objMembers.pMobileno = dr["mobileno"];
objMembers.pContactid = dr["contactid"];
lstMembers.Add(objMembers);
}
}
}
catch (Exception ex)
{
throw ex;
}
});
return(lstMembers);
}
public List <FunctionsDTO> getFunctions(string Moduleid)
{
List <FunctionsDTO> lstFunction = new List <FunctionsDTO>();
try
{
string str = "select functionname,functionid,statusid from tabfunctions where moduleid=1 and statusid=1;";
str = "select functionname,functionid,statusid,moduleid from tabfunctions where moduleid=" + Moduleid + " and statusid=1;";
npgdr = NPGSqlHelper.ExecuteReader(NPGSqlHelper.SQLConnString, CommandType.Text, str);
while (npgdr.Read())
{
FunctionsDTO objFunctionsDTO = new FunctionsDTO();
objFunctionsDTO.FunctionId = Convert.ToInt32(npgdr["functionid"]);
objFunctionsDTO.FunctionName = npgdr["functionname"].ToString();
objFunctionsDTO.Moduleid = Convert.ToInt16(npgdr["moduleid"]);
objFunctionsDTO.FunctionStatus = false;
lstFunction.Add(objFunctionsDTO);
}
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "UserRights");
}
finally
{
npgdr.Dispose();
}
return(lstFunction);
}
public async Task <List <CompanyNamesDTO> > GetCompanyname(string ConnectionString)
{
List <CompanyNamesDTO> lstCompanyname = new List <CompanyNamesDTO>();
await Task.Run(() =>
{
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select distinct companyname from tabbranchcodes;"))
{
while (dr.Read())
{
CompanyNamesDTO objCompanyname = new CompanyNamesDTO();
objCompanyname.pcompanyname = dr["companyname"];
lstCompanyname.Add(objCompanyname);
}
}
}
catch (Exception ex)
{
throw ex;
}
});
return(lstCompanyname);
}
public List <ModuleDTO> ShowRoleFunctions(string ID)
{
NpgsqlDataReader npgdr = null;
List <ModuleDTO> lstJquery = new List <ModuleDTO>();
try
{
string strlocation = "select f.functionid,f.functionname,f.functionurl,f.moduleid,m.modulename from tabfunctions f left join tabmodules m on m.moduleid=f.moduleid order by modulename;";
strlocation = "select x.functionid,functionname,functionurl,x.moduleid,modulename,coalesce(status,false) as status from (select f.functionid,f.functionname,f.functionurl,f.moduleid,m.modulename from tabfunctions f left join tabmodules m on m.moduleid=f.moduleid where moduletype in ('RMS') order by modulename)x left join (SELECT functionid,moduleid,true as status FROM tabrolefunctions where userid=" + ID + ")y on x.moduleid=y.moduleid and x.functionid=y.functionid;";
strlocation = "select x.functionid,functionname,functionurl,x.moduleid, modulename,coalesce(status,false) as status from (select f.functionid,f.functionname,f.functionurl,f.moduleid,m.parentmodulename||'_'||m.modulename as modulename from tabfunctions f left join tabmodules m on m.moduleid=f.moduleid where moduletype in ('RMS') order by modulename)x left join (SELECT functionid,moduleid,true as status FROM tabrolefunctions where userid=" + ID + ")y on x.moduleid=y.moduleid and x.functionid=y.functionid;";
npgdr = NPGSqlHelper.ExecuteReader(NPGSqlHelper.SQLConnString, CommandType.Text, strlocation);
while (npgdr.Read())
{
ModuleDTO obj = new ModuleDTO();
obj.functionid = Convert.ToInt32(npgdr["functionid"].ToString());
obj.functionname = npgdr["functionname"].ToString();
obj.functionurl = npgdr["functionurl"].ToString();
obj.Moduleid = Convert.ToInt32(npgdr["moduleid"].ToString());
obj.modulename = npgdr["modulename"].ToString();
obj.chkStatus = Convert.ToBoolean(npgdr["status"]);
lstJquery.Add(obj);
}
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "UserRights");
}
finally
{
npgdr.Dispose();
}
return(lstJquery);
}
public async Task <List <BranchNamesDTO> > GetBranchName(string Companyname, string ConnectionString)
{
List <BranchNamesDTO> lstBranchname = new List <BranchNamesDTO>();
await Task.Run(() =>
{
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select code,branchname from tabbranchcodes where companyname='" + Companyname + "' order by branchname;"))
{
while (dr.Read())
{
BranchNamesDTO objBranchname = new BranchNamesDTO();
objBranchname.pBranchid = dr["code"];
objBranchname.pBranchname = dr["branchname"];
lstBranchname.Add(objBranchname);
}
}
}
catch (Exception ex)
{
throw ex;
}
});
return(lstBranchname);
}
public List <ChequemanagementDTO> ViewChequeManagementDetails(string con)
{
List <ChequemanagementDTO> lstChequemanagement = new List <ChequemanagementDTO>();
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(con, CommandType.Text, "select t1.chqbookid,t1.noofcheques,t1.chequefrom,t1.chequeto,t1.chqegeneratestatus,bankname,accountnumber from tblmstchequemanagement t1 join tblmstbank t2 on t1.bankid=t2.recordid join tblmststatus ts on t1.statusid=ts.statusid and upper(statusname)='ACTIVE';"))
{
while (dr.Read())
{
ChequemanagementDTO obj = new ChequemanagementDTO();
obj.pChqbookid = Convert.ToInt64(dr["chqbookid"]);
obj.pNoofcheques = Convert.ToInt64(dr["noofcheques"]);
obj.pChequefrom = Convert.ToInt64(dr["chequefrom"]);
obj.pChequeto = Convert.ToInt64(dr["chequeto"]);
obj.pChqegeneratestatus = Convert.ToString(dr["chqegeneratestatus"]);
obj.pBankname = Convert.ToString(dr["bankname"]);
obj.pAccountnumber = Convert.ToString(dr["accountnumber"]);
lstChequemanagement.Add(obj);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lstChequemanagement);
}
public List <instalmentdatedetails> getinstalmentsdateslist(string ConnectionString, Int64 Loanid)
{
loaninstalmentdatedetails = new List <instalmentdatedetails>();
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select * from TBLMSTLOANINSTALLMENTDATECONFIG where loanid=" + Loanid + " "))
{
while (dr.Read())
{
instalmentdatedetails objinstalmentsdateslist = new instalmentdatedetails();
// objinstalmentsdateslis.pLoantypeid = Convert.ToInt32(dr["loantypeid"]);
objinstalmentsdateslist.pTypeofInstalmentDay = dr["typeofinstallmentday"].ToString();
objinstalmentsdateslist.pDisbursefromday = Convert.ToInt32(dr["disbursefromday"]);
objinstalmentsdateslist.pDisbursetoday = Convert.ToInt32(dr["Disbursetoday"]);
objinstalmentsdateslist.pInstalmentdueday = Convert.ToInt32(dr["installmentdueday"]);
objinstalmentsdateslist.pInstalmentdueday = Convert.ToInt32(dr["installmentdueday"]);
loaninstalmentdatedetails.Add(objinstalmentsdateslist);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(loaninstalmentdatedetails);
}
public List <BankInformationDTO> ViewBankInformationDetails(string con)
{
List <BankInformationDTO> lstBankInformation = new List <BankInformationDTO>();
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(con, CommandType.Text, "select recordid,bankname,accountnumber,accountname,statusname,isdebitcardapplicable,isupiapplicable from tblmstbank t join tblmststatus ts on t.statusid=ts.statusid ;"))
{
while (dr.Read())
{
BankInformationDTO obj = new BankInformationDTO();
obj.pRecordid = Convert.ToInt64(dr["recordid"]);
obj.pBankname = Convert.ToString(dr["bankname"]);
obj.pAccountnumber = Convert.ToString(dr["accountnumber"]);
obj.pAccountname = Convert.ToString(dr["accountname"]);
obj.pStatusname = Convert.ToString(dr["statusname"]);
obj.pIsdebitcardapplicable = Convert.ToBoolean(dr["isdebitcardapplicable"]);
obj.pIsupiapplicable = Convert.ToBoolean(dr["isupiapplicable"]);
lstBankInformation.Add(obj);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lstBankInformation);
}
public List <jointdetails> GetJointMembersListInEdit(string AccountNo, string Accounttype, string ConnectionString)
{
var _MemberJointDetailsList = new List <jointdetails>();
try
{
//Changed By Sai Mahesh 15 JAN 2021
//using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "SELECT recordid, memberid,membercode,membername,contactid,contacttype,contactreferenceid from tbltransrdjointdetails where rdaccountno = '" + AccountNo + "' and accounttype='" + Accounttype + "' and statusid = " + Convert.ToInt32(Status.Active) + ";"))
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select jointdetailsid, tj.memberid, membercode, membername, contactid, contacttype, contactreferenceid from tbltransjointdetails tj join tblmstmembers tm on tm.memberid = tj.memberid where accountno = '" + AccountNo + "' and accounttype='" + Accounttype + "' and tj.statusid = " + Convert.ToInt32(Status.Active) + ";"))
{
while (dr.Read())
{
jointdetails _MemberJointDetails = new jointdetails
{
precordid = Convert.ToInt64(dr["jointdetailsid"]),
pMemberId = Convert.ToInt64(dr["memberid"]),
pMemberCode = Convert.ToString(dr["membercode"]),
pMemberName = Convert.ToString(dr["membername"]),
pContactid = Convert.ToInt64(dr["contactid"]),
pContacttype = Convert.ToString(dr["contacttype"]),
pContactrefid = Convert.ToString(dr["contactreferenceid"]),
pTypeofOperation = "OLD"
};
_MemberJointDetailsList.Add(_MemberJointDetails);
}
}
}
catch (Exception)
{
throw;
}
return(_MemberJointDetailsList);
}
private List <BankUPI> ViewBankUpiDetails(long pRecordid, string con)
{
lstBankUpi = new List <BankUPI>();
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(con, CommandType.Text, "select recordid,bankid,upiid,upiname,statusname,'OLD' as typeofoperation from tblmstbankupidtls t join tblmststatus ts on t.statusid=ts.statusid and upper(statusname)=upper('active') where bankid=" + pRecordid + ""))
{
while (dr.Read())
{
BankUPI obj = new BankUPI();
obj.pRecordid = Convert.ToInt64(dr["recordid"]);
obj.pBankId = Convert.ToInt64(dr["bankid"]);
obj.pUpiid = Convert.ToString(dr["upiid"]);
obj.pUpiname = Convert.ToString(dr["upiname"]);
obj.pStatusname = Convert.ToString(dr["statusname"]);
obj.ptypeofoperation = Convert.ToString(dr["typeofoperation"]);
lstBankUpi.Add(obj);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lstBankUpi);
}
public List <MemberreceiptViewDTO> GetMemberReceiptView(string FromDate, string Todate, string Connectionstring)
{
List <MemberreceiptViewDTO> lstmemberReceiptView = new List <MemberreceiptViewDTO>();
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(Connectionstring, CommandType.Text, "select memberid, membername, membercode, membertypeid, membertype, contactid, contacttype, contactreferenceid,to_char(receipt_date, 'dd/Mon/yyyy') receipt_date, received_amount, mode_of_receipt, receipt_no, narration, (case when tt.clearstatus = 'R' OR tt.clearstatus = 'C' OR tt.depositstatus = 'C' then 'Cancelled' when tt.clearstatus = 'Y' then 'Cleared' when tt.clearstatus IS NULL then 'Cleared' else 'Un-Cleared' end)as ChequeStatus from Member_receipt MR join tblmstmembers TM on TM.memberid = MR.member_id left join tbltransreceiptreference TT on TT.receiptid = MR.receipt_no where MR.status = true and receipt_date between '" + FormatDate(FromDate) + "' and '" + FormatDate(Todate) + "' order by memberid desc;"))
{
while (dr.Read())
{
MemberreceiptViewDTO objMemberReceipt = new MemberreceiptViewDTO();
objMemberReceipt.pmemberid = Convert.ToInt64(dr["memberid"]);
objMemberReceipt.pmembername = Convert.ToString(dr["membername"]);
objMemberReceipt.pmembercode = Convert.ToString(dr["membercode"]);
objMemberReceipt.pmembertypeid = Convert.ToInt64(dr["membertypeid"]);
objMemberReceipt.pmembertype = Convert.ToString(dr["membertype"]);
objMemberReceipt.pcontactid = Convert.ToInt64(dr["contactid"]);
objMemberReceipt.pcontacttype = Convert.ToString(dr["contacttype"]);
objMemberReceipt.pcontactreferenceid = Convert.ToString(dr["contactreferenceid"]);
objMemberReceipt.preceiptdate = dr["receipt_date"] == DBNull.Value ? null : Convert.ToDateTime(dr["receipt_date"]).ToString("dd/MM/yyyy");
objMemberReceipt.preceivedamount = Convert.ToDecimal(dr["received_amount"]);
objMemberReceipt.pmodeofreceipt = Convert.ToString(dr["mode_of_receipt"]);
objMemberReceipt.preceiptno = Convert.ToString(dr["receipt_no"]);
objMemberReceipt.pnarration = Convert.ToString(dr["narration"]);
objMemberReceipt.pChequeStatus = Convert.ToString(dr["ChequeStatus"]);
lstmemberReceiptView.Add(objMemberReceipt);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lstmemberReceiptView);
}
public List <BankInformationAddressDTO> ViewBankAddressInformation(Int64 pRecordid, string con)
{
lstBankInformationAddress = new List <BankInformationAddressDTO>();
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(con, CommandType.Text, "select recordid,bankid,address1,address2,city,t.district,coalesce(t3.districtid,0) districtid,t.state,coalesce(t2.stateid,0) stateid,t.country,coalesce(t1.countryid,0) countryid,pincode,statusname,'OLD' as typeofoperation from tblmstbankaddressdtls t join tblmststatus ts on t.statusid=ts.statusid and upper(statusname)=upper('active') left join tblmstcountry t1 on t.country=t1.country left join tblmststate t2 on t.state=t2.state left join tblmstdistrict t3 on t.district=t3.district where bankid =" + pRecordid + ""))
{
while (dr.Read())
{
BankInformationAddressDTO obj = new BankInformationAddressDTO();
obj.pRecordid = Convert.ToInt64(dr["recordid"]);
obj.pBankId = Convert.ToInt64(dr["bankid"]);
obj.pAddress1 = Convert.ToString(dr["address1"]);
obj.pAddress2 = Convert.ToString(dr["address2"]);
obj.pcity = Convert.ToString(dr["city"]);
obj.pDistrict = Convert.ToString(dr["district"]);
obj.pState = Convert.ToString(dr["state"]);
obj.pCountry = Convert.ToString(dr["country"]);
obj.pPincode = Convert.ToString(dr["pincode"]);
obj.pStatusname = Convert.ToString(dr["statusname"]);
obj.ptypeofoperation = Convert.ToString(dr["typeofoperation"]);
obj.pstateid = Convert.ToInt64(dr["stateid"]);
obj.pcountryid = Convert.ToInt64(dr["countryid"]);
obj.pdistrictid = Convert.ToInt64(dr["districtid"]);
lstBankInformationAddress.Add(obj);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lstBankInformationAddress);
}
public List <SavingTransactionDTO> GetSavingTransaction(Int64 SavingAccountId, string Connectionstring)
{
List <SavingTransactionDTO> lstSavingTransaction = new List <SavingTransactionDTO>();
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(Connectionstring, CommandType.Text, "select trans_date, receipt_no, received_amount, mode_of_receipt from saving_account_receipt where saving_account_id = " + SavingAccountId + " and status = true;"))
{
while (dr.Read())
{
SavingTransactionDTO objSavingTransaction = new SavingTransactionDTO();
objSavingTransaction.ptransdate = dr["trans_date"] == DBNull.Value ? null : Convert.ToDateTime(dr["trans_date"]).ToString("dd/MM/yyyy");
objSavingTransaction.preceiptno = Convert.ToString(dr["receipt_no"]);
objSavingTransaction.preceivedamount = Convert.ToDecimal(dr["received_amount"]);
objSavingTransaction.pmodeofreceipt = Convert.ToString(dr["mode_of_receipt"]);
lstSavingTransaction.Add(objSavingTransaction);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lstSavingTransaction);
}
public List <TransactionsDTO> GetTransactionslist(int FdAccountNo, string ConnectionString)
{
List <TransactionsDTO> lstTransactions = new List <TransactionsDTO>();
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, " select * from(select received_amount,receipt_no,fd_receiptt_date,narration,Mode_of_receipt,(case when tt.clearstatus='Y' then 'cleared' when (tt.clearstatus='R' or tt.clearstatus='C' or depositstatus='C') then 'Canceled' when tt.clearstatus is null then 'cleared' else 'Not Cleared' end ) as Chequestatus from fd_receipt fr left join tbltransreceiptreference tt on fr.receipt_no=tt.receiptid where fd_account_id=" + FdAccountNo + " order by fd_receipt_id desc)tbl where Chequestatus<>'Canceled';"))
while (dr.Read())
{
TransactionsDTO objTransactions = new TransactionsDTO();
objTransactions.pReceiptamount = dr["received_amount"];
objTransactions.pReceiptno = dr["receipt_no"];
objTransactions.pNarration = dr["narration"];
objTransactions.pModeofReceipt = dr["Mode_of_receipt"];
objTransactions.pChequestatus = dr["Chequestatus"];
if (dr["fd_receiptt_date"] != DBNull.Value)
{
objTransactions.pReceiptdate = Convert.ToDateTime(dr["fd_receiptt_date"]).ToString("dd/MM/yyyy");
}
else
{
objTransactions.pReceiptdate = "";
}
//objTransactions.pChequestatus = dr["chequestatus"].ToString();
lstTransactions.Add(objTransactions);
}
}
catch (Exception ex)
{
throw;
}
return(lstTransactions);
}