public List <MemberDetailsDTO> GetMemberDetails(string MemberType, string Connectionstring)
{
List <MemberDetailsDTO> lstMemberDetails = new List <MemberDetailsDTO>();
try
{
string query = "select distinct memberid,membercode,membername,tc.contactid,tc.contactreferenceid,membertype,businessentitycontactno as mobileno from tblmstmembers tm join tblmstcontact tc on tc.contactid=tm.contactid where upper(membertype)='" + ManageQuote(MemberType.ToUpper()) + "' and tm.statusid =" + Convert.ToInt32(Status.Active) + " order by membername";
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(Connectionstring, CommandType.Text, query))
{
while (dr.Read())
{
MemberDetailsDTO objMemberdetails = new MemberDetailsDTO();
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);
}
//branches insert
public List <dynamic> AddMember(MemberDetailsUI MemberUI)
{
List <dynamic> ObjDynamic = new List <dynamic>();
MemberDetailsDTO MemberDTO = new MemberDetailsDTO();
MemberDTO.BranchId = MemberUI.BranchId;
MemberDTO.BranchName = MemberUI.BranchName;
MemberDTO.FirstName = MemberUI.FirstName;
MemberDTO.LastName = MemberUI.LastName;
MemberDTO.Phone = MemberUI.Phone;
MemberDTO.Email = MemberUI.Email;
MemberDTO.Address1 = MemberUI.Address1;
MemberDTO.Address2 = MemberUI.Address2;
MemberDTO.Address3 = MemberUI.Address3;
MemberDTO.FullAddress = MemberUI.FullAddress;
MemberDTO.City = MemberUI.City;
MemberDTO.State = MemberUI.State;
MemberDTO.Zip = MemberUI.Zip;
MemberDTO.Country = MemberUI.Country;
//MemberDTO.UserID = MemberUI.UserID;
MemberDTO.Type = 1;
DateTime now = DateTime.Now;
MemberDTO.start_date = now;// DateTime.Now.ToString("yyyy-MM-ddTHH:mm:sszzz");
MemberDTO.expiry_date = now;
AddMemberData(MemberDTO);
ObjDynamic.Add(MemberDTO);
return(ObjDynamic);
}
public List <dynamic> AddMember(MemberDetailsDTO Member)
{
string insertProcedure = "[MemberMasterInsert]";
DateTime now = DateTime.Now;
Dictionary <string, string> input_parameters = new Dictionary <string, string>();
input_parameters.Add("@pkey_member_id", 1 + "#int#" + Member.MemberId);
//input_parameters.Add("@Branch_name", 1 + "#varchar#" + Member.BranchName);
input_parameters.Add("@first_name", 1 + "#varchar#" + Member.FirstName);
input_parameters.Add("@last_name", 1 + "#varchar#" + Member.LastName);
input_parameters.Add("@mobile_number", 1 + "#varchar#" + Member.Phone);
input_parameters.Add("@email_id", 1 + "#varchar#" + Member.Email);
input_parameters.Add("@address1", 1 + "#varchar#" + Member.Address1);
input_parameters.Add("@address2", 1 + "#varchar#" + Member.Address2);
input_parameters.Add("@address3", 1 + "#varchar#" + Member.Address3);
input_parameters.Add("@pincode", 1 + "#int#" + Member.Zip);
input_parameters.Add("@city", 1 + "#varchar#" + Member.City);
input_parameters.Add("@state", 1 + "#varchar#" + Member.State);
input_parameters.Add("@country", 1 + "#varchar#" + Member.Country);
input_parameters.Add("@start_date", 1 + "#datetime#" + now);
input_parameters.Add("@expiry_date", 1 + "#datetime#" + now);
//input_parameters.Add("@is_delete", 1 + "#int#" + Member.is_delete);
//input_parameters.Add("@UserID", 1 + "#int#" + Member.UserID);
input_parameters.Add("@Type", 1 + "#int#" + Member.Type);
input_parameters.Add("@pkey_member_idOut", 2 + "#int#" + null);
input_parameters.Add("@ReturnValue", 2 + "#int#" + null);
return(obj.SqlCRUD(insertProcedure, input_parameters));
}
public List <MemberDetailsDTO> GetMemberDetailsByid(long Memberid, string Connectionstring)
{
List <MemberDetailsDTO> lstMemberDetails = new List <MemberDetailsDTO>();
string Query = string.Empty;
try
{
Query = "select memberid,membercode,membername,tc.fathername,businessentitycontactno as phoneno,(case when ta.contactid is not null then address1||','||city||','||district||','||state||','||country||'-'||pincode else null end )::text as Address from tblmstmembers tm join tblmstcontact tc on tm.contactid=tc.contactid left join tblmstcontactaddressdetails ta on tm.contactid=ta.contactid and ta.priority='PRIMARY' where memberid=" + Memberid + ";";
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(Connectionstring, CommandType.Text, Query))
{
while (dr.Read())
{
MemberDetailsDTO objMemberDetailsDTO = new MemberDetailsDTO();
objMemberDetailsDTO.pMemberid = dr["memberid"];
objMemberDetailsDTO.pMembername = dr["membername"];
objMemberDetailsDTO.pMembercode = dr["membercode"];
objMemberDetailsDTO.pMobileno = dr["phoneno"];
objMemberDetailsDTO.pAddress = dr["Address"];
objMemberDetailsDTO.pFathername = dr["fathername"];
lstMemberDetails.Add(objMemberDetailsDTO);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lstMemberDetails);
}
//Log log = new Log();
private int AddMemberData(MemberDetailsDTO Member)
{
string insertProcedure = "[MemberMasterInsert]";
SqlCommand insertCommand = new SqlCommand(insertProcedure, connection);
insertCommand.CommandType = CommandType.StoredProcedure;
int MemberId = 0;
if (Member.MemberId != 0)
{
insertCommand.Parameters.AddWithValue("@pkey_member_id", Member.MemberId);
}
else
{
insertCommand.Parameters.AddWithValue("@pkey_member_id", 0);
}
//if (Member.BranchId != 0)
//{
// insertCommand.Parameters.AddWithValue("@Pkey_Branch_id", Member.BranchId);
//}
//else
//{
// insertCommand.Parameters.AddWithValue("@Pkey_Branch_id", 0);
//}
//if (!string.IsNullOrEmpty(Member.BranchName))
//{
// insertCommand.Parameters.AddWithValue("@Branch_name", Member.BranchName);
//}
//else
//{
// insertCommand.Parameters.AddWithValue("@Branch_name", DBNull.Value);
//}
if (!string.IsNullOrEmpty(Member.FirstName))
{
insertCommand.Parameters.AddWithValue("@first_name", Member.FirstName);
}
else
{
insertCommand.Parameters.AddWithValue("@first_name", DBNull.Value);
}
if (!string.IsNullOrEmpty(Member.LastName))
{
insertCommand.Parameters.AddWithValue("@last_name", Member.LastName);
}
else
{
insertCommand.Parameters.AddWithValue("@last_name", DBNull.Value);
}
if (Member.Phone != 0)
{
insertCommand.Parameters.AddWithValue("@mobile_number", Member.Phone);
}
else
{
insertCommand.Parameters.AddWithValue("@mobile_number", 0);
}
if (!string.IsNullOrEmpty(Member.Email))
{
insertCommand.Parameters.AddWithValue("@email_id", Member.Email);
}
else
{
insertCommand.Parameters.AddWithValue("@email_id", DBNull.Value);
}
if (!string.IsNullOrEmpty(Member.Address1))
{
insertCommand.Parameters.AddWithValue("@address1", Member.Address1);
}
else
{
insertCommand.Parameters.AddWithValue("@address1", DBNull.Value);
}
if (!string.IsNullOrEmpty(Member.Address2))
{
insertCommand.Parameters.AddWithValue("@address2", Member.Address2);
}
else
{
insertCommand.Parameters.AddWithValue("@address2", DBNull.Value);
}
if (!string.IsNullOrEmpty(Member.Address3))
{
insertCommand.Parameters.AddWithValue("@address3", Member.Address3);
}
else
{
insertCommand.Parameters.AddWithValue("@address3", DBNull.Value);
}
//if (!string.IsNullOrEmpty(Member.FullAddress))
//{
// insertCommand.Parameters.AddWithValue("@", Member.FullAddress);
//}
//else
//{
// insertCommand.Parameters.AddWithValue("@", DBNull.Value);
//}
if (Member.Zip != 0)
{
insertCommand.Parameters.AddWithValue("@pincode", Member.Zip);
}
else
{
insertCommand.Parameters.AddWithValue("@pincode", 0);
}
if (!string.IsNullOrEmpty(Member.City))
{
insertCommand.Parameters.AddWithValue("@city", Member.City);
}
else
{
insertCommand.Parameters.AddWithValue("@city", DBNull.Value);
}
if (!string.IsNullOrEmpty(Member.State))
{
insertCommand.Parameters.AddWithValue("@state", Member.State);
}
else
{
insertCommand.Parameters.AddWithValue("@state", DBNull.Value);
}
if (!string.IsNullOrEmpty(Member.Country))
{
insertCommand.Parameters.AddWithValue("@country", Member.Country);
}
else
{
insertCommand.Parameters.AddWithValue("@country", DBNull.Value);
}
//if (Member.UserID != 0)
//{
// insertCommand.Parameters.AddWithValue("@UserID", Member.UserID);
//}
//else
//{
// insertCommand.Parameters.AddWithValue("@UserID", 0);
//}
insertCommand.Parameters.AddWithValue("@start_date", Member.start_date);
insertCommand.Parameters.AddWithValue("@expiry_date", Member.expiry_date);
insertCommand.Parameters.AddWithValue("@UserID", 0);
insertCommand.Parameters.AddWithValue("@is_delete", 0);
insertCommand.Parameters.AddWithValue("@Type", Member.Type);
insertCommand.Parameters.Add("@pkey_member_idOut", System.Data.SqlDbType.Int);
insertCommand.Parameters["@pkey_member_idOut"].Direction = ParameterDirection.Output;
insertCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int);
insertCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.Output;
try
{
int count = 0;
connection.Open();
insertCommand.ExecuteNonQuery();
if (insertCommand.Parameters["@ReturnValue"].Value != DBNull.Value)
{
count = System.Convert.ToInt32(insertCommand.Parameters["@ReturnValue"].Value);
}
if (count != 0 && Member.BranchId == 0)
{
Member.BranchId = Convert.ToInt32(insertCommand.Parameters["@pkey_member_idOut"].Value);
}
return(MemberId);
}
catch (Exception ex)
{
//log.logErrorMessage("");
//log.logErrorMessage(ex.StackTrace);
return(MemberId);
}
finally
{
connection.Close();
}
}
