public List <ContactMasterDTO> GetContactdetails(string ConnectionString)
{
List <ContactMasterDTO> lstcontactdetails = new List <ContactMasterDTO>();
try
{
dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select x.*,y.statusname from (select tc.contacttype,tc.name,tc.surname,tc.contactid,tc.contactreferenceid,tc.statusid,tca.contactname,tca.contactnumber,tca.emailid,tca.priority from tblmstcontact tc join TBLMSTCONTACTPERSONDETAILS tca on tc.contactreferenceid=tca.contactreferenceid where upper(tca.priority)='PRIMARY') x left join tblmststatus y on x.statusid=y.statusid");
while (dr.Read())
{
ContactMasterDTO obj = new ContactMasterDTO();
obj.pContactId = Convert.ToInt64(dr["contactid"]);
obj.pReferenceId = dr["contactreferenceid"].ToString();
obj.pContactType = dr["contacttype"].ToString();
obj.pStatusid = dr["statusid"].ToString();
obj.pStatusname = dr["statusname"].ToString();
obj.pName = dr["name"].ToString();
obj.pSurName = dr["surname"].ToString();
obj.pEmailidsList = new List <EmailidsDTO>();
obj.pEmailidsList.Add(new EmailidsDTO
{
pContactNumber = dr["contactnumber"].ToString()
,
pEmailId = dr["emailid"].ToString()
});
lstcontactdetails.Add(obj);
}
}
catch (Exception ex)
{
throw ex;
}
return(lstcontactdetails);
}
public IActionResult viewphoto(ContactMasterDTO contact)
{
string base64String = string.Empty;
string filePath = string.Empty;
try
{
using (System.Drawing.Image image = System.Drawing.Image.FromFile(contact.pContactimagepath))
{
using (MemoryStream m = new MemoryStream())
{
image.Save(m, image.RawFormat);
byte[] imageBytes1 = m.ToArray();
base64String = Convert.ToBase64String(imageBytes1);
contact.pPhoto = base64String;
}
}
}
catch (Exception ex)
{
throw new FinstaAppException(ex.ToString());
}
return(Ok(base64String));
}
public bool UpdateContact(ContactMasterDTO contact)
{
bool issaved = false;
bool isfileupload = false;
try
{
if (!string.IsNullOrEmpty(contact.pPhoto))
{
isfileupload = Uploadphoto(contact);
}
else
{
isfileupload = true;
}
if (isfileupload == true)
{
issaved = obj.UpdateContact(contact, Con);
}
}
catch (Exception ex)
{
throw new FinstaAppException(ex.ToString());
}
return(issaved);
}
public void Savecontact(ContactMasterDTO contact)
{
try
{
obj.Savecontact(contact);
}
catch (Exception ex)
{
throw ex;
}
}
public int GetPersonCount(ContactMasterDTO ContactDto)
{
try
{
return(obj.GetPersoncount(ContactDto, Con));
}
catch (Exception ex)
{
throw new FinstaAppException(ex.ToString());
}
}
public bool DeleteContact(ContactMasterDTO contact)
{
try
{
return(obj.DeleteContact(contact, Con));
}
catch (Exception ex)
{
throw new FinstaAppException(ex.ToString());
}
}
public bool Savecontact([FromBody] ContactMasterDTO contact)
{
try
{
return(obj.Savecontact(contact, Con));
}
catch (Exception ex)
{
throw new FinstaAppException(ex.ToString());
}
}
public List <ContactMasterDTO> GetContactdetails(string ConnectionString, string Type)
{
string Query = string.Empty;
List <ContactMasterDTO> lstcontactdetails = new List <ContactMasterDTO>();
if (!string.IsNullOrEmpty(Type))
{
Type = Type.ToUpper();
}
try
{
if (Type == "ALL" || string.IsNullOrEmpty(Type))
{
Query = "select x.*,y.statusname from (select tc.contacttype,tc.name,tc.surname,tc.contactid,tc.contactreferenceid,tc.statusid,businessentityemailid , businessentitycontactno from tblmstcontact tc ) x left join tblmststatus y on x.statusid=y.statusid order by x.contactid desc;";
}
else
{
Query = "select x.*,y.statusname from (select tc.contacttype,tc.name,tc.surname,tc.contactid,tc.contactreferenceid,tc.statusid,businessentityemailid , businessentitycontactno from tblmstcontact tc ) x left join tblmststatus y on x.statusid = y.statusid where upper(x.name) like'%" + Type + "%' or upper(x.surname) like '%" + Type + "%' or upper(x.contactreferenceid) like '%" + Type + "%' or upper(x.businessentityemailid) like '%" + Type + "%' or upper(businessentitycontactno::text) like '%" + Type + "%'; ";
}
//using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select x.*,y.statusname from (select tc.contacttype,tc.name,tc.surname,tc.contactid,tc.contactreferenceid,tc.statusid,tca.contactname,tca.contactnumber,tca.emailid,tca.priority from tblmstcontact tc join TBLMSTCONTACTPERSONDETAILS tca on tc.contactreferenceid=tca.contactreferenceid where upper(tca.priority)='PRIMARY') x left join tblmststatus y on x.statusid=y.statusid"))
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, Query))
{
while (dr.Read())
{
ContactMasterDTO obj = new ContactMasterDTO();
obj.pContactId = Convert.ToInt64(dr["contactid"]);
obj.pReferenceId = dr["contactreferenceid"].ToString();
obj.pContactType = dr["contacttype"].ToString();
obj.pStatusid = dr["statusid"].ToString();
obj.pStatusname = dr["statusname"].ToString();
obj.pName = dr["name"].ToString() + " " + dr["surname"].ToString();
obj.pSurName = dr["surname"].ToString();
obj.pBusinessEntityEmailid = dr["businessentityemailid"].ToString();
obj.pBusinessEntityContactno = dr["businessentitycontactno"].ToString();
//obj.pEmailidsList = new List<EmailidsDTO>();
//obj.pEmailidsList.Add(new EmailidsDTO
//{
// pContactNumber = dr["contactnumber"].ToString()
//,
// pEmailId = dr["emailid"].ToString()
//});
lstcontactdetails.Add(obj);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lstcontactdetails);
}
public bool DeleteContact(ContactMasterDTO contact, string ConnectionString)
{
bool isSaved = false;
try
{
NPGSqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, "update tblmstcontact set statusid=" + getStatusid(contact.pStatusname, ConnectionString) + ",modifiedby=" + contact.pCreatedby + ",modifieddate=current_timestamp where upper(contactreferenceid)='" + contact.pReferenceId.ToUpper() + "';");
isSaved = true;
}
catch (Exception ex)
{
throw ex;
}
return(isSaved);
}
public ContactMasterDTO ViewContact(string refernceid)
{
lstcontactMasterDetails = new ContactMasterDTO();
try
{
lstcontactMasterDetails = obj.ViewContact(refernceid, Con);
if (!string.IsNullOrEmpty(lstcontactMasterDetails.pContactimagepath))
{
viewphoto(lstcontactMasterDetails);
}
}
catch (Exception ex)
{
throw new FinstaAppException(ex.ToString());
}
return(lstcontactMasterDetails);
}
public int GetPersoncount(ContactMasterDTO ContactDto, string ConnectionString)
{
int count = 0;
try
{
for (var i = 0; i < ContactDto.pEmailidsList.Count; i++)
{
count = count + Convert.ToInt32(NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "select count(*) from (select tc.contacttype,tc.name,tc.surname,tca.contactnumber,tca.priority from tblmstcontact tc join TBLMSTCONTACTPERSONDETAILS tca on tc.contactreferenceid=tca.contactreferenceid where upper(tca.priority)='PRIMARY') x where x.name='" + ManageQuote(ContactDto.pName) + "' and x.surname='" + ManageQuote(ContactDto.pSurName) + "' and x.contactnumber=" + ContactDto.pEmailidsList[i].pContactNumber + ";"));
}
}
catch (Exception)
{
throw;
}
return(count);
}
public bool Uploadphoto(ContactMasterDTO contact)
{
try
{
var bytes = Convert.FromBase64String(contact.pPhoto); // a.base64image
//or full path to file in temp location
//var filePath = Path.GetTempFileName();
// full path to file in current project location
string filedir = Path.Combine(Directory.GetCurrentDirectory(), "ContactImages");
Debug.WriteLine(filedir);
Debug.WriteLine(Directory.Exists(filedir));
if (!Directory.Exists(filedir))
{ //check if the folder exists;
Directory.CreateDirectory(filedir);
}
Guid obj = Guid.NewGuid();
string file1 = Path.Combine(filedir, obj.ToString() + contact.pBusinesscontactName + ".jpg");
contact.pContactimagepath = file1;
Debug.WriteLine(file1);
//Debug.WriteLine(File.Exists(file));
if (bytes.Length > 0)
{
using (var stream = new FileStream(file1, FileMode.Create))
{
stream.Write(bytes, 0, bytes.Length);
stream.Flush();
}
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
//return StatusCode(500, "Internal server error");
//throw ex;
throw new FinstaAppException(ex.ToString());
}
}
public List <ContactMasterDTO> GetContactdetails()
{
List <ContactMasterDTO> lstcontactdetails = new List <ContactMasterDTO>();
try
{
dr = NPGSqlHelper.ExecuteReader(NPGSqlHelper.SQLConnString, CommandType.Text, "select * from tabcontact tc join tabcontactaddressdetails tca on tc.contactreferenceid=tca.contactreferenceid");
while (dr.Read())
{
ContactMasterDTO obj = new ContactMasterDTO();
obj.pReferenceId = dr["contacttype"].ToString();
obj.pContactType = dr["contacttype"].ToString();
obj.pName = dr["name"].ToString();
obj.pSurName = dr["surname"].ToString();
obj.pDob = dr["dob"].ToString();
obj.pGender = dr["gender"].ToString();
obj.pFatherName = dr["fathername"].ToString();
obj.pSpouseName = dr["spousename"].ToString();
obj.pcontactdetailslist[0].pContactNo = dr["contactnumber"].ToString();
obj.pcontactdetailslist[0].pAlternativeNo = dr["alternatenumber"].ToString();
obj.pcontactdetailslist[0].pEmailId1 = dr["emailid1"].ToString();
obj.pcontactdetailslist[0].pEmailId1 = dr["emailid2"].ToString();
obj.pAddressList[0].pAddressType = dr["pAddressType"].ToString();
obj.pAddressList[0].pAddress1 = dr["pAddress1"].ToString();
obj.pAddressList[0].pAddress2 = dr["pAddress2"].ToString();
obj.pAddressList[0].pState = dr["pState"].ToString();
obj.pAddressList[0].pDistrict = dr["pDistrict"].ToString();
obj.pAddressList[0].pCity = dr["pCity"].ToString();
obj.pAddressList[0].pCountry = dr["pCountry"].ToString();
obj.pAddressList[0].pPinCode = Convert.ToInt64(dr["pPinCode"]);
}
}
catch (Exception ex)
{
throw ex;
}
return(lstcontactdetails);
}
public int GetPersoncount(ContactMasterDTO ContactDto, string ConnectionString)
{
int count = -1;
try
{
if (string.IsNullOrEmpty(ContactDto.pReferenceId))
{
ContactDto.pReferenceId = "";
}
else
{
ContactDto.pReferenceId = ContactDto.pReferenceId.ToUpper();
}
count = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "select count(*) from tblmstcontact x where upper(contactreferenceid)<>'" + ContactDto.pReferenceId + "' and upper(x.name)='" + ManageQuote(ContactDto.pName.ToUpper()) + "' and upper(coalesce(x.surname,''))='" + ManageQuote(ContactDto.pSurName.ToUpper()) + "' and x.businessentitycontactno=" + ContactDto.pBusinessEntityContactno + ";"));
}
catch (Exception)
{
throw;
}
return(count);
}
public bool Savecontact(ContactMasterDTO contact)
{
bool isSaved = false;
try
{
string savecontact = string.Empty;
if (contact.pContactType == "")
{
savecontact = "insert into tabcontact(contactreferenceid,contacttype,name,surname,dob,gender,gendercode,fathername,spousename,contactnumber,alternatenumber,emailid1,emailid2,statusid,createdby,dreateddate) values('" + contact.pReferenceId + "','" + contact.pContactType + "','" + contact.pName + "','" + contact.pSurName + "','" + contact.pDob + "','" + contact.pGender + "','" + contact.pGenderCode + "','" + contact.pFatherName + "','" + contact.pSpouseName + "','" + contact.pcontactdetailslist[0].pContactNo + "','" + contact.pcontactdetailslist[0].pAlternativeNo + "','" + contact.pcontactdetailslist[0].pEmailId1 + "','" + contact.pcontactdetailslist[0].pEmailId2 + "',1,1,current_timestamp)";
NPGSqlHelper.ExecuteNonQuery(NPGSqlHelper.SQLConnString, CommandType.Text, savecontact);
isSaved = true;
}
else
{
}
}
catch (Exception ex)
{
throw ex;
}
return(isSaved);
}
public bool Savecontact(ContactMasterDTO contact, string ConnectionString)
{
bool isSaved = false;
try
{
StringBuilder sb = new StringBuilder();
long ContactRefid;
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (string.IsNullOrEmpty(contact.pDob))
{
contact.pDob = "null";
}
else
{
contact.pDob = "'" + FormatDate(contact.pDob) + "'";
}
contact.pReferenceId = NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "SELECT FN_GENERATENEXTID('CONTACT','" + ManageQuote(contact.pContactType) + "',CURRENT_DATE)").ToString();
if (contact.pContactType == "Individual")
{
ContactRefid = Convert.ToInt64(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "insert into tblmstcontact(transdate,contactreferenceid,contacttype,titlename,name,surname,businessentityemailid , businessentitycontactno, dob,gender,gendercode,fathername,spousename,contactimagepath,statusid,createdby,createddate) values(CURRENT_DATE,'" + ManageQuote(contact.pReferenceId) + "','" + ManageQuote(contact.pContactType) + "','" + ManageQuote(contact.pTitleName) + "','" + ManageQuote(contact.pName) + "','" + ManageQuote(contact.pSurName) + "','" + ManageQuote(contact.pBusinessEntityEmailid) + "','" + ManageQuote(contact.pBusinessEntityContactno) + "'," + (contact.pDob) + ",'" + ManageQuote(contact.pGender) + "','" + contact.pGenderCode + "','" + ManageQuote(contact.pFatherName) + "','" + ManageQuote(contact.pSpouseName) + "','" + ManageQuote(contact.pContactimagepath) + "'," + getStatusid(contact.pStatusname, ConnectionString) + "," + contact.pCreatedby + ",current_timestamp) returning CONTACTID;"));
for (var i = 0; i < contact.pAddressList.Count; i++)
{
sb.Append("insert into tblmstcontactaddressdetails(CONTACTID,contactreferenceid,addresstype,address1,address2,state,stateid,district,districtid,city,country,countryid,pincode,priority,statusid,createdby,createddate) values(" + ContactRefid + ",'" + ManageQuote(contact.pReferenceId) + "','" + ManageQuote(contact.pAddressList[i].pAddressType) + "','" + ManageQuote(contact.pAddressList[i].pAddress1) + "','" + ManageQuote(contact.pAddressList[i].pAddress2) + "','" + ManageQuote(contact.pAddressList[i].pState) + "'," + contact.pAddressList[i].pStateId + ",'" + ManageQuote(contact.pAddressList[i].pDistrict) + "'," + contact.pAddressList[i].pDistrictId + ",'" + ManageQuote(contact.pAddressList[i].pCity) + "','" + ManageQuote(contact.pAddressList[i].pCountry) + "'," + contact.pAddressList[i].pCountryId + "," + contact.pAddressList[i].pPinCode + ",'" + ManageQuote(contact.pAddressList[i].pPriority) + "'," + getStatusid(contact.pStatusname, ConnectionString) + "," + contact.pCreatedby + ",current_timestamp);");
}
for (var i = 0; i < contact.pEmailidsList.Count; i++)
{
if (contact.pEmailidsList[i].pContactNumber == "" || contact.pEmailidsList[i].pContactNumber == string.Empty || contact.pEmailidsList[i].pContactNumber == null)
{
contact.pEmailidsList[i].pContactNumber = "0";
}
sb.Append("insert into TBLMSTCONTACTPERSONDETAILS(contactid,contactreferenceid,contactname,contactnumber,emailid,priority,statusid,createdby,createddate) values(" + ContactRefid + ",'" + ManageQuote(contact.pReferenceId) + "','" + ManageQuote(contact.pContactName) + "'," + contact.pEmailidsList[i].pContactNumber + ",'" + ManageQuote(contact.pEmailidsList[i].pEmailId) + "','" + ManageQuote(contact.pEmailidsList[i].pPriority) + "'," + getStatusid(contact.pStatusname, ConnectionString) + "," + contact.pCreatedby + ",current_timestamp);");
}
}
else
{
ContactRefid = Convert.ToInt64(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "insert into tblmstcontact(transdate,contactreferenceid,contacttype,name,typeofenterprise,natureofbusiness,businessentityemailid , businessentitycontactno,contactimagepath,statusid,createdby,createddate) values(CURRENT_DATE,'" + ManageQuote(contact.pReferenceId) + "','" + ManageQuote(contact.pContactType) + "','" + ManageQuote(contact.pName) + "','" + ManageQuote(contact.pEnterpriseType) + "','" + ManageQuote(contact.pBusinesstype) + "','" + ManageQuote(contact.pBusinessEntityEmailid) + "','" + ManageQuote(contact.pBusinessEntityContactno) + "','" + ManageQuote(contact.pContactimagepath) + "'," + getStatusid(contact.pStatusname, ConnectionString) + "," + contact.pCreatedby + ",current_timestamp) returning CONTACTID;"));
if (contact.pAddressList != null)
{
for (int i = 0; i < contact.pAddressList.Count; i++)
{
sb.Append("insert into tblmstcontactaddressdetails(CONTACTID,contactreferenceid,addresstype,address1,address2,state,stateid,district,districtid,city,country,countryid,pincode,priority,statusid,createdby,createddate) values(" + ContactRefid + ",'" + ManageQuote(contact.pReferenceId) + "','" + ManageQuote(contact.pAddressList[i].pAddressType) + "','" + ManageQuote(contact.pAddressList[i].pAddress1) + "','" + ManageQuote(contact.pAddressList[i].pAddress2) + "','" + ManageQuote(contact.pAddressList[i].pState) + "'," + contact.pAddressList[i].pStateId + ",'" + ManageQuote(contact.pAddressList[i].pDistrict) + "'," + contact.pAddressList[i].pDistrictId + ",'" + ManageQuote(contact.pAddressList[i].pCity) + "','" + ManageQuote(contact.pAddressList[i].pCountry) + "'," + contact.pAddressList[i].pCountryId + "," + contact.pAddressList[i].pPinCode + ",'" + ManageQuote(contact.pAddressList[i].pPriority) + "'," + getStatusid(contact.pStatusname, ConnectionString) + "," + contact.pCreatedby + ",current_timestamp);");
}
}
if (contact.pEmailidsList != null)
{
for (int i = 0; i < contact.pEmailidsList.Count; i++)
{
if (contact.pEmailidsList[i].pContactNumber == "" || contact.pEmailidsList[i].pContactNumber == string.Empty || contact.pEmailidsList[i].pContactNumber == null)
{
contact.pEmailidsList[i].pContactNumber = "0";
}
sb.Append("insert into TBLMSTCONTACTPERSONDETAILS(contactid,contactreferenceid,contactname,contactnumber,emailid,priority,statusid,createdby,createddate) values(" + ContactRefid + ",'" + ManageQuote(contact.pReferenceId) + "','" + ManageQuote(contact.pContactName) + "'," + contact.pEmailidsList[i].pContactNumber + ",'" + ManageQuote(contact.pEmailidsList[i].pEmailId) + "','" + ManageQuote(contact.pEmailidsList[i].pPriority) + "'," + getStatusid(contact.pStatusname, ConnectionString) + "," + contact.pCreatedby + ",current_timestamp);");
}
}
}
if (Convert.ToString(sb) != string.Empty)
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sb.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 UpdateContact(ContactMasterDTO contact, string ConnectionString)
{
bool isSaved = false;
long ContactRefid;
string Recordid = string.Empty;
StringBuilder sbupdate = new StringBuilder();
string query = "";
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (string.IsNullOrEmpty(contact.pDob))
{
contact.pDob = "null";
}
else
{
contact.pDob = "'" + FormatDate(contact.pDob) + "'";
}
if (contact.pContactType == "Individual")
{
sbupdate.Append("update tblmstcontact set name='" + ManageQuote(contact.pName) + "',surname='" + ManageQuote(contact.pSurName) + "',dob=" + (contact.pDob) + ",gender='" + ManageQuote(contact.pGender) + "',gendercode='" + contact.pGenderCode + "',fathername='" + ManageQuote(contact.pFatherName) + "',spousename='" + ManageQuote(contact.pSpouseName) + "',businessentityemailid ='" + ManageQuote(contact.pBusinessEntityEmailid) + "' , businessentitycontactno='" + ManageQuote(contact.pBusinessEntityContactno) + "',contactimagepath='" + ManageQuote(contact.pContactimagepath) + "',modifiedby=" + contact.pCreatedby + ",modifieddate=current_timestamp where upper(contactreferenceid)='" + contact.pReferenceId.ToUpper() + "';");
}
else
{
sbupdate.Append("update tblmstcontact set name='" + ManageQuote(contact.pName) + "',typeofenterprise='" + ManageQuote(contact.pEnterpriseType) + "',natureofbusiness='" + ManageQuote(contact.pBusinesstype) + "',businessentityemailid ='" + ManageQuote(contact.pBusinessEntityEmailid) + "' , businessentitycontactno='" + ManageQuote(contact.pBusinessEntityContactno) + "',contactimagepath='" + ManageQuote(contact.pContactimagepath) + "',modifiedby=" + contact.pCreatedby + ",modifieddate=current_timestamp where upper(contactreferenceid)='" + contact.pReferenceId.ToUpper() + "';");
}
ContactRefid = Convert.ToInt64(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "select CONTACTID from tblmstcontact where contactreferenceid='" + contact.pReferenceId + "';"));
if (contact.pEmailidsList != null)
{
for (var i = 0; i < contact.pEmailidsList.Count; i++)
{
sbupdate.Append("update TBLMSTCONTACTPERSONDETAILS set contactname='" + ManageQuote(contact.pContactName) + "',contactnumber=" + contact.pEmailidsList[i].pContactNumber + ",emailid='" + ManageQuote(contact.pEmailidsList[i].pEmailId) + "',priority='" + ManageQuote(contact.pEmailidsList[i].pPriority) + "',modifiedby=" + contact.pCreatedby + ",modifieddate=current_timestamp where upper(contactreferenceid)='" + contact.pReferenceId.ToUpper() + "' and recordid=" + contact.pEmailidsList[i].pRecordId + ";");
}
}
if (contact.pAddressList != null)
{
for (int i = 0; i < contact.pAddressList.Count; i++)
{
if (contact.pAddressList[i].ptypeofoperation != "CREATE")
{
if (string.IsNullOrEmpty(Recordid))
{
Recordid = contact.pAddressList[i].pRecordId.ToString();
}
else
{
Recordid = Recordid + "," + contact.pAddressList[i].pRecordId.ToString();
}
}
if (contact.pAddressList[i].ptypeofoperation == "UPDATE")
{
sbupdate.Append("update tblmstcontactaddressdetails set addresstype='" + ManageQuote(contact.pAddressList[i].pAddressType) + "', address1='" + ManageQuote(contact.pAddressList[i].pAddress1) + "',address2='" + ManageQuote(contact.pAddressList[i].pAddress2) + "',state='" + ManageQuote(contact.pAddressList[i].pState) + "',stateid=" + contact.pAddressList[i].pStateId + ",district='" + ManageQuote(contact.pAddressList[i].pDistrict) + "',districtid=" + contact.pAddressList[i].pDistrictId + ",city='" + ManageQuote(contact.pAddressList[i].pCity) + "',country='" + ManageQuote(contact.pAddressList[i].pCountry) + "',countryid=" + contact.pAddressList[i].pCountryId + ",pincode=" + contact.pAddressList[i].pPinCode + ",priority='" + ManageQuote(contact.pAddressList[i].pPriority) + "',modifiedby=" + contact.pCreatedby + ",modifieddate=current_timestamp where upper(contactreferenceid)='" + contact.pReferenceId.ToUpper() + "' and recordid=" + contact.pAddressList[i].pRecordId + ";");
}
else if (contact.pAddressList[i].ptypeofoperation == "CREATE")
{
sbupdate.Append("insert into tblmstcontactaddressdetails(CONTACTID,contactreferenceid,addresstype,address1,address2,state,stateid,district,districtid,city,country,countryid,pincode,priority,statusid,createdby,createddate) values(" + ContactRefid + ",'" + ManageQuote(contact.pReferenceId) + "','" + ManageQuote(contact.pAddressList[i].pAddressType) + "','" + ManageQuote(contact.pAddressList[i].pAddress1) + "','" + ManageQuote(contact.pAddressList[i].pAddress2) + "','" + ManageQuote(contact.pAddressList[i].pState) + "'," + contact.pAddressList[i].pStateId + ",'" + ManageQuote(contact.pAddressList[i].pDistrict) + "'," + contact.pAddressList[i].pDistrictId + ",'" + ManageQuote(contact.pAddressList[i].pCity) + "','" + ManageQuote(contact.pAddressList[i].pCountry) + "'," + contact.pAddressList[i].pCountryId + "," + contact.pAddressList[i].pPinCode + ",'" + ManageQuote(contact.pAddressList[i].pPriority) + "'," + getStatusid(contact.pStatusname, ConnectionString) + "," + contact.pCreatedby + ",current_timestamp);");
}
//else if (contact.pAddressList[i].ptypeofoperation == "DELETE")
//{
// sbupdate.Append("update tblmstcontactaddressdetails set statusid=" + getStatusid(contact.pStatusname, ConnectionString) + ",modifiedby=" + contact.pAddressList[i].pCreatedby + ",modifieddate=current_timestamp where upper(contactreferenceid)='" + contact.pReferenceId.ToUpper()+ "' and upper(addresstype)='" + contact.pAddressList[i].pAddressType.ToUpper()+ "' and recordid=" + contact.pAddressList[i].pRecordId + ";");
//}
}
}
if (!string.IsNullOrEmpty(Recordid))
{
query = "update tblmstcontactaddressdetails set statusid=" + getStatusid("In-Active", ConnectionString) + ",modifiedby=" + contact.pCreatedby + ",modifieddate=current_timestamp where upper(contactreferenceid)='" + contact.pReferenceId.ToUpper() + "' and recordid not in(" + Recordid + ");";
}
else
{
query = "update tblmstcontactaddressdetails set statusid=" + getStatusid("In-Active", ConnectionString) + ",modifiedby=" + contact.pCreatedby + ",modifieddate=current_timestamp where upper(contactreferenceid)='" + contact.pReferenceId.ToUpper() + "';";
}
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, query + "" + sbupdate.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 ContactMasterDTO ViewContact(string refernceid, string ConnectionString)
{
string addressdetails = string.Empty;
ContactMasterDTO obj = new ContactMasterDTO();
ds = new DataSet();
try
{
obj.pAddressList = new List <contactAddressDTO>();
obj.pEmailidsList = new List <EmailidsDTO>();
ds = NPGSqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, "select * from tblmstcontact where contactreferenceid='" + refernceid + "';select * from tblmstcontactaddressdetails where contactreferenceid='" + refernceid + "';select * from tblmstcontactpersondetails where contactreferenceid='" + refernceid + "'");
if (ds != null)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
obj.pContactType = dr["contacttype"].ToString();
obj.pName = dr["name"].ToString();
obj.pSurName = dr["surname"].ToString();
obj.pContactType = dr["contacttype"].ToString();
obj.pTitleName = dr["titlename"].ToString();
obj.pName = dr["name"].ToString();
obj.pSurName = dr["surname"].ToString();
obj.pDob = dr["dob"].ToString();
obj.pGender = dr["gender"].ToString();
obj.pFatherName = dr["fathername"].ToString();
obj.pSpouseName = dr["spousename"].ToString();
obj.pEnterpriseType = dr["typeofenterprise"].ToString();
obj.pBusinesstype = dr["NatureofBusiness"].ToString();
}
foreach (DataRow dr1 in ds.Tables[1].Rows)
{
addressdetails = string.Empty;
if (dr1["Address1"].ToString() != "" && dr1["Address1"].ToString() != null && dr1["Address1"].ToString() != string.Empty)
{
addressdetails = addressdetails + dr1["Address1"].ToString();
}
if (dr1["Address2"].ToString() != "" && dr1["Address2"].ToString() != null && dr1["Address2"].ToString() != string.Empty)
{
addressdetails = addressdetails + "," + addressdetails + dr1["Address2"].ToString();
}
if (dr1["City"].ToString() != "" && dr1["City"].ToString() != null && dr1["City"].ToString() != string.Empty)
{
addressdetails = addressdetails + "," + dr1["City"].ToString();
}
if (dr1["District"].ToString() != "" && dr1["District"].ToString() != null && dr1["District"].ToString() != string.Empty)
{
addressdetails = addressdetails + "," + dr1["District"].ToString();
}
if (dr1["State"].ToString() != "" && dr1["State"].ToString() != null && dr1["State"].ToString() != string.Empty)
{
addressdetails = addressdetails + "," + dr1["State"].ToString();
}
if (dr1["Country"].ToString() != "" && dr1["Country"].ToString() != null && dr1["Country"].ToString() != string.Empty)
{
addressdetails = addressdetails + "," + dr1["Country"].ToString();
}
if (dr1["PinCode"].ToString() != "" && dr1["PinCode"].ToString() != null && dr1["PinCode"].ToString() != string.Empty)
{
addressdetails = addressdetails + "-" + Convert.ToInt64(dr1["PinCode"]);
}
obj.pAddressList.Add(new contactAddressDTO
{
pRecordId = Convert.ToInt64(dr1["recordid"]),
pAddressType = dr1["AddressType"].ToString(),
pAddress1 = dr1["Address1"].ToString(),
pAddress2 = dr1["Address2"].ToString(),
pState = dr1["State"].ToString(),
pStateId = Convert.ToInt64(dr1["stateid"]),
pDistrict = dr1["District"].ToString(),
pDistrictId = Convert.ToInt64(dr1["stateid"]),
pCity = dr1["City"].ToString(),
pCountry = dr1["Country"].ToString(),
pCountryId = Convert.ToInt64(dr1["countryid"]),
pPinCode = Convert.ToInt64(dr1["PinCode"]),
pPriority = dr1["Priority"].ToString(),
pAddressDetails = addressdetails
});
}
foreach (DataRow dr2 in ds.Tables[2].Rows)
{
obj.pEmailidsList.Add(new EmailidsDTO
{
pRecordId = Convert.ToInt64(dr2["recordid"]),
pContactName = dr2["contactname"].ToString(),
pContactNumber = dr2["contactnumber"].ToString(),
pEmailId = dr2["emailid"].ToString(),
pPriority = dr2["Priority"].ToString()
});
}
}
}
catch (Exception ex)
{
throw ex;
}
return(obj);
}
public ContactMasterDTO ViewContact(string refernceid, string ConnectionString)
{
string addressdetails = string.Empty;
ContactMasterDTO obj = new ContactMasterDTO();
ds = new DataSet();
try
{
obj.pAddressList = new List <contactAddressDTO>();
obj.pEmailidsList = new List <EmailidsDTO>();
ds = NPGSqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, "select * from tblmstcontact where upper(contactreferenceid)='" + refernceid.ToUpper() + "';select * from tblmstcontactaddressdetails where statusid in (select statusid from tblmststatus where upper(statusname) ='ACTIVE') and upper(contactreferenceid)='" + refernceid.ToUpper() + "';select * from tblmstcontactpersondetails where statusid in (select statusid from tblmststatus where upper(statusname) ='ACTIVE') and upper(contactreferenceid)='" + refernceid.ToUpper() + "'");
if (ds != null)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
obj.pContactId = Convert.ToInt64(dr["contactid"]);
obj.pContactType = Convert.ToString(dr["contacttype"]);
obj.pName = Convert.ToString(dr["name"]);
obj.pSurName = Convert.ToString(dr["surname"]);
obj.pContactType = Convert.ToString(dr["contacttype"]);
obj.pTitleName = Convert.ToString(dr["titlename"]);
obj.pName = Convert.ToString(dr["name"]);
obj.pSurName = Convert.ToString(dr["surname"]);
if (!string.IsNullOrEmpty(Convert.ToString(dr["dob"])))
{
obj.pDob = Convert.ToDateTime(dr["dob"]).ToString("dd-MM-yyyy");
}
obj.pGender = Convert.ToString(dr["gender"]);
obj.pFatherName = Convert.ToString(dr["fathername"]);
obj.pSpouseName = Convert.ToString(dr["spousename"]);
obj.pEnterpriseType = Convert.ToString(dr["typeofenterprise"]);
obj.pBusinesstype = Convert.ToString(dr["NatureofBusiness"]);
obj.pBusinessEntityEmailid = Convert.ToString(dr["businessentityemailid"]);
obj.pBusinessEntityContactno = Convert.ToString(dr["businessentitycontactno"]);
obj.pContactimagepath = dr["contactimagepath"].ToString();
}
obj.pcontactexistingstatus = Convert.ToBoolean(
NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "SELECT getcontactexistingdata(" + obj.pContactId + ");"));
foreach (DataRow dr1 in ds.Tables[1].Rows)
{
addressdetails = string.Empty;
if (dr1["Address1"].ToString() != "" && dr1["Address1"].ToString() != null && dr1["Address1"].ToString() != string.Empty)
{
addressdetails = addressdetails + dr1["Address1"].ToString();
}
if (dr1["Address2"].ToString() != "" && dr1["Address2"].ToString() != null && dr1["Address2"].ToString() != string.Empty)
{
if (string.IsNullOrEmpty(addressdetails))
{
addressdetails = dr1["Address2"].ToString();
}
else
{
addressdetails = addressdetails + "," + dr1["Address2"].ToString();
}
}
if (dr1["City"].ToString() != "" && dr1["City"].ToString() != null && dr1["City"].ToString() != string.Empty)
{
if (string.IsNullOrEmpty(addressdetails))
{
addressdetails = dr1["City"].ToString();
}
else
{
addressdetails = addressdetails + "," + dr1["City"].ToString();
}
}
if (dr1["District"].ToString() != "" && dr1["District"].ToString() != null && dr1["District"].ToString() != string.Empty)
{
if (string.IsNullOrEmpty(addressdetails))
{
addressdetails = dr1["District"].ToString();
}
else
{
addressdetails = addressdetails + "," + dr1["District"].ToString();
}
}
if (dr1["State"].ToString() != "" && dr1["State"].ToString() != null && dr1["State"].ToString() != string.Empty)
{
if (string.IsNullOrEmpty(addressdetails))
{
addressdetails = dr1["State"].ToString();
}
else
{
addressdetails = addressdetails + "," + dr1["State"].ToString();
}
}
if (dr1["Country"].ToString() != "" && dr1["Country"].ToString() != null && dr1["Country"].ToString() != string.Empty)
{
if (string.IsNullOrEmpty(addressdetails))
{
addressdetails = dr1["Country"].ToString();
}
else
{
addressdetails = addressdetails + "," + dr1["Country"].ToString();
}
}
if (dr1["PinCode"].ToString() != "" && dr1["PinCode"].ToString() != null && dr1["PinCode"].ToString() != string.Empty)
{
if (string.IsNullOrEmpty(addressdetails))
{
addressdetails = dr1["PinCode"].ToString();
}
else
{
addressdetails = addressdetails + "-" + Convert.ToInt64(dr1["PinCode"]);
}
}
obj.pAddressList.Add(new contactAddressDTO
{
pRecordId = Convert.ToInt64(dr1["recordid"]),
pAddressType = dr1["AddressType"].ToString(),
pAddress1 = dr1["Address1"].ToString(),
pAddress2 = dr1["Address2"].ToString(),
pState = dr1["State"].ToString(),
pStateId = Convert.ToInt64(dr1["stateid"]),
pDistrict = dr1["District"].ToString(),
pDistrictId = Convert.ToInt64(dr1["districtid"]),
pCity = dr1["City"].ToString(),
pCountry = dr1["Country"].ToString(),
pCountryId = Convert.ToInt64(dr1["countryid"]),
pPinCode = Convert.ToInt64(dr1["PinCode"]),
pPriority = dr1["Priority"].ToString(),
pAddressDetails = addressdetails
});
}
foreach (DataRow dr2 in ds.Tables[2].Rows)
{
obj.pEmailidsList.Add(new EmailidsDTO
{
pRecordId = Convert.ToInt64(dr2["recordid"]),
pContactName = dr2["contactname"].ToString(),
pContactNumber = dr2["contactnumber"].ToString(),
pEmailId = dr2["emailid"].ToString(),
pPriority = dr2["Priority"].ToString()
});
}
}
}
catch (Exception ex)
{
throw ex;
}
return(obj);
}
