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 List <FDMemberDetailsDTO> GetMemberDetails(string BranchName, string Connectionstring)
{
List <FDMemberDetailsDTO> lstMemberDetails = new List <FDMemberDetailsDTO>();
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(Connectionstring, CommandType.Text, "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 tf.chitbranchname ='" + BranchName + "' and tm.statusid=" + Convert.ToInt32(Status.Active
) + " and tf.statusid=" + Convert.ToInt32(Status.Active) + ";"))
{
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["businessentitycontactno"];
lstMemberDetails.Add(objMemberdetails);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lstMemberDetails);
}
