public List <FDDetailsDTO> GetFdToDetails(string Branchname, string Membercode, string Connectionstring)
{
List <FDDetailsDTO> lstFDDetails = new List <FDDetailsDTO>();
try
{
string Query = string.Empty;
Query = "select fdaccountid,fdaccountno,membername,chitbranchname,depositamount,accountid,memberid from tbltransfdcreation where statusid=" + Convert.ToInt32(Status.Active) + " and upper(membercode) ='" + ManageQuote(Membercode).ToUpper() + "' and upper(chitbranchname)='" + ManageQuote(Branchname).ToUpper() + "' and fdaccountid not in (select fd_account_id from fd_receipt) ;";
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.pMemberId = Convert.ToInt64(dr["memberid"]);
lstFDDetails.Add(objFDdetails);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lstFDDetails);
}
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);
}
public List <FDDetailsDTO> GetFdFromDetails(string Connectionstring)
{
List <FDDetailsDTO> lstFDDetails = new List <FDDetailsDTO>();
try
{
// string Query = "select fdaccountid,fdaccountno,membername,chitbranchname,depositamount,accountid,memberid from tbltransfdcreation where statusid=" + Convert.ToInt32(Status.Active) + " and fdaccountid in (select fd_account_id from fd_receipt);";
// string Query = "select a.*,receivedamount from (select fdaccountid,fdaccountno,membername,chitbranchname,depositamount,accountid,memberid from tbltransfdcreation where statusid=" + Convert.ToInt32(Status.Active) + " and fdaccountid in (select fd_account_id from fd_receipt)) a join (select fd_account_id, sum(received_amount) as receivedamount from fd_receipt group by fd_account_id) b on a.fdaccountid=b.fd_account_id where a.depositamount=b.receivedamount order by membername;";
string Query = "select a.*,receivedamount,'' as receipt_no from (select fdaccountid,fdaccountno,membername,chitbranchname,depositamount,accountid,memberid from tbltransfdcreation where statusid=" + Convert.ToInt32(Status.Active) + " and fdaccountid in (select fd_account_id from fd_receipt)) a join (select fd_account_id, sum(received_amount) as receivedamount from fd_receipt where mode_of_receipt='CASH' group by fd_account_id) b on a.fdaccountid=b.fd_account_id where a.depositamount=b.receivedamount union select a.*,receivedamount,receipt_no from (select fdaccountid,fdaccountno,membername,chitbranchname,depositamount,accountid,memberid from tbltransfdcreation where statusid=" + Convert.ToInt32(Status.Active) + " and fdaccountid in (select fd_account_id from fd_receipt)) a join (select fd_account_id, sum(received_amount) as receivedamount,receipt_no from fd_receipt where mode_of_receipt='BANK' group by fd_account_id,receipt_no) b on a.fdaccountid=b.fd_account_id join tbltransreceiptreference tr on tr.receiptid=b.receipt_no where transtype = 'CHEQUE' and clearstatus = 'Y'; ";
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.pMemberId = Convert.ToInt64(dr["memberid"]);
lstFDDetails.Add(objFDdetails);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lstFDDetails);
}
