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); }