//creating an instance of findDoctor table (Model) as a parameter
public bool commitInsert(contact_detail contact)
{
using (objContact)
{
objContact.contact_details.InsertOnSubmit(contact);
//commit insert with db
objContact.SubmitChanges();
return(true);
}
}
public ActionResult ContactDetailDelete(int Id, contact_detail contact)
{
//Selected value will be deleted from the database
try
{
objContact.commitDelete(Id);
return(RedirectToAction("ContactDetailIndex"));
}
catch
{
return(View());
}
}
public ActionResult ContactDetailUpdate(int Id, contact_detail contact)
{
//If all the input were valid , then database will be updated
if (ModelState.IsValid)
{
try
{
objContact.commitUpdate(Id, contact.name, contact.phone, (int)contact.department_id);
return(RedirectToAction("ContactDetailIndex"));
}
catch
{
return(View());
}
}
return(View());
}
public ActionResult ContactDetailInsert(contact_detail contact)
{
if (ModelState.IsValid)
{
try
{
objContact.commitInsert(contact);
return(RedirectToAction("ContactDetailIndex")); //On sucessful insert, redirect to the index view
}
catch
{
//Error handling, return to view if something goes wrong
return(View());
}
}
return(View());
}
public contact_detail[] contact_by_email(string user_name, string password, string email_address)
{
// 03/12/2007 Paul. If using NTLM, then user_name will be updated with value from Identity object.
Guid gUSER_ID = LoginUser(ref user_name, password);
int nACLACCESS = Security.GetUserAccess("Contacts", "list");
if ( nACLACCESS < 0 )
{
L10N L10n = new L10N("en-US");
throw(new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
}
contact_detail[] results = new contact_detail[0];
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using ( IDbConnection con = dbf.CreateConnection() )
{
con.Open();
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwSOAP_Contact_By_Email" + ControlChars.CrLf
+ " where 1 = 0 " + ControlChars.CrLf;
using ( IDbCommand cmd = con.CreateCommand() )
{
cmd.CommandText = sSQL;
// 12/29/2005 Paul. Allow multiple email addresses, separated by a semicolon.
email_address = email_address.Replace(" ", "");
string[] aAddresses = email_address.Split(';');
// 02/20/2006 Paul. Need to use the IN clause.
Sql.AppendParameter(cmd, aAddresses, "EMAIL1", true);
Sql.AppendParameter(cmd, aAddresses, "EMAIL2", true);
if ( nACLACCESS == ACL_ACCESS.OWNER )
{
Sql.AppendParameter(cmd, gUSER_ID, "ASSIGNED_USER_ID");
}
try
{
using ( DbDataAdapter da = dbf.CreateDataAdapter() )
{
((IDbDataAdapter)da).SelectCommand = cmd;
using ( DataTable dt = new DataTable() )
{
da.Fill(dt);
if ( dt.Rows.Count > 0 )
{
// 02/20/2006 Paul. First initialize the array.
results = new contact_detail[dt.Rows.Count];
for ( int i=0; i < dt.Rows.Count ; i++ )
{
// 02/20/2006 Paul. Then initialize each element in the array.
results[i] = new contact_detail();
results[i].email_address = Sql.ToString(dt.Rows[i]["EMAIL_ADDRESS"]);
results[i].name1 = Sql.ToString(dt.Rows[i]["NAME1" ]);
results[i].name2 = Sql.ToString(dt.Rows[i]["NAME2" ]);
results[i].association = Sql.ToString(dt.Rows[i]["ASSOCIATION" ]);
results[i].id = Sql.ToString(dt.Rows[i]["ID" ]);
results[i].type = Sql.ToString(dt.Rows[i]["TYPE" ]);
results[i].msi_id = (i+1).ToString();
}
}
}
}
}
catch(Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
throw(new Exception("SOAP: Failed contact_by_email", ex));
}
}
}
return results;
}
public contact_detail[] search(string user_name, string password, string name)
{
// 03/12/2007 Paul. If using NTLM, then user_name will be updated with value from Identity object.
Guid gUSER_ID = LoginUser(ref user_name, password);
contact_detail[] results = new contact_detail[0];
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using ( IDbConnection con = dbf.CreateConnection() )
{
con.Open();
string sSQL;
int nACLACCESS = 0;
using ( IDbCommand cmd = con.CreateCommand() )
{
StringBuilder sb = new StringBuilder();
// 12/29/2005 Paul. Names are normally separated by a semicolon.
// Since we are using our StringBuilder, convert the semicolon to an OR clause.
name = name.Replace(";", " or ");
sSQL = "select ID as ID " + ControlChars.CrLf
+ " , FIRST_NAME as NAME1 " + ControlChars.CrLf
+ " , LAST_NAME as NAME2 " + ControlChars.CrLf
+ " , ACCOUNT_NAME as ASSOCIATION " + ControlChars.CrLf
+ " , N'Contact' as TYPE " + ControlChars.CrLf
+ " , EMAIL1 as EMAIL_ADDRESS" + ControlChars.CrLf
+ " from vwCONTACTS_List " + ControlChars.CrLf
+ " where 1 = 1 " + ControlChars.CrLf
+ Contacts.SearchContacts.UnifiedSearch(name, cmd);
nACLACCESS = Security.GetUserAccess("Contacts", "list");
if ( nACLACCESS < 0 )
sSQL += sSQL + " and 1 = 0" + ControlChars.CrLf;
else if ( nACLACCESS == ACL_ACCESS.OWNER )
sSQL += sSQL + " and ASSIGNED_USER_ID = '" + gUSER_ID.ToString() + "'" + ControlChars.CrLf;
sb.Append(sSQL);
// 05/23/2006 Paul. Add space after the query to prevent UNION ALL from touching a previous field or keyword.
sSQL = " union all " + ControlChars.CrLf
+ "select ID as ID " + ControlChars.CrLf
+ " , FIRST_NAME as NAME1 " + ControlChars.CrLf
+ " , LAST_NAME as NAME2 " + ControlChars.CrLf
+ " , ACCOUNT_NAME as ASSOCIATION " + ControlChars.CrLf
+ " , N'Lead' as TYPE " + ControlChars.CrLf
+ " , EMAIL1 as EMAIL_ADDRESS" + ControlChars.CrLf
+ " from vwLEADS_List " + ControlChars.CrLf
+ " where 1 = 1 " + ControlChars.CrLf
+ Leads.SearchLeads.UnifiedSearch(name, cmd);
nACLACCESS = Security.GetUserAccess("Leads", "list");
if ( nACLACCESS < 0 )
sSQL += sSQL + " and 1 = 0" + ControlChars.CrLf;
else if ( nACLACCESS == ACL_ACCESS.OWNER )
sSQL += sSQL + " and ASSIGNED_USER_ID = '" + gUSER_ID.ToString() + "'" + ControlChars.CrLf;
sb.Append(sSQL);
// 05/23/2006 Paul. Add space after the query to prevent UNION ALL from touching a previous field or keyword.
sSQL = " union all " + ControlChars.CrLf
+ "select ID as ID " + ControlChars.CrLf
+ " , N'' as NAME1 " + ControlChars.CrLf
+ " , NAME as NAME2 " + ControlChars.CrLf
+ " , BILLING_ADDRESS_CITY as ASSOCIATION " + ControlChars.CrLf
+ " , N'Account' as TYPE " + ControlChars.CrLf
+ " , EMAIL1 as EMAIL_ADDRESS" + ControlChars.CrLf
+ " from vwACCOUNTS_List " + ControlChars.CrLf
+ " where 1 = 1 " + ControlChars.CrLf
+ Accounts.SearchAccounts.UnifiedSearch(name, cmd);
nACLACCESS = Security.GetUserAccess("Accounts", "list");
if ( nACLACCESS < 0 )
sSQL += sSQL + " and 1 = 0" + ControlChars.CrLf;
else if ( nACLACCESS == ACL_ACCESS.OWNER )
sSQL += sSQL + " and ASSIGNED_USER_ID = '" + gUSER_ID.ToString() + "'" + ControlChars.CrLf;
sb.Append(sSQL);
// 05/23/2006 Paul. Add space after the query to prevent UNION ALL from touching a previous field or keyword.
sSQL = " union all " + ControlChars.CrLf
+ "select ID as ID " + ControlChars.CrLf
+ " , N'' as NAME1 " + ControlChars.CrLf
+ " , NAME as NAME2 " + ControlChars.CrLf
+ " , ACCOUNT_NAME as ASSOCIATION " + ControlChars.CrLf
+ " , N'Case' as TYPE " + ControlChars.CrLf
+ " , N'' as EMAIL_ADDRESS" + ControlChars.CrLf
+ " from vwCASES_List " + ControlChars.CrLf
+ " where 1 = 1 " + ControlChars.CrLf
+ Cases.SearchCases.UnifiedSearch(name, cmd);
nACLACCESS = Security.GetUserAccess("Cases", "list");
if ( nACLACCESS < 0 )
sSQL += sSQL + " and 1 = 0" + ControlChars.CrLf;
else if ( nACLACCESS == ACL_ACCESS.OWNER )
sSQL += sSQL + " and ASSIGNED_USER_ID = '" + gUSER_ID.ToString() + "'" + ControlChars.CrLf;
sb.Append(sSQL);
// 05/23/2006 Paul. Add space after the query to prevent UNION ALL from touching a previous field or keyword.
sSQL = " union all " + ControlChars.CrLf
+ "select ID as ID " + ControlChars.CrLf
+ " , N'' as NAME1 " + ControlChars.CrLf
+ " , NAME as NAME2 " + ControlChars.CrLf
+ " , ACCOUNT_NAME as ASSOCIATION " + ControlChars.CrLf
+ " , N'Opportunity' as TYPE " + ControlChars.CrLf
+ " , N'' as EMAIL_ADDRESS" + ControlChars.CrLf
+ " from vwOPPORTUNITIES_List " + ControlChars.CrLf
+ " where 1 = 1 " + ControlChars.CrLf
+ Opportunities.SearchOpportunities.UnifiedSearch(name, cmd);
nACLACCESS = Security.GetUserAccess("Opportunities", "list");
if ( nACLACCESS < 0 )
sSQL += sSQL + " and 1 = 0" + ControlChars.CrLf;
else if ( nACLACCESS == ACL_ACCESS.OWNER )
sSQL += sSQL + " and ASSIGNED_USER_ID = '" + gUSER_ID.ToString() + "'" + ControlChars.CrLf;
sb.Append(sSQL);
// 06/01/2006 Paul. The string builder contains the full query.
cmd.CommandText = sb.ToString();
try
{
using ( DbDataAdapter da = dbf.CreateDataAdapter() )
{
((IDbDataAdapter)da).SelectCommand = cmd;
using ( DataTable dt = new DataTable() )
{
da.Fill(dt);
if ( dt.Rows.Count > 0 )
{
// 02/20/2006 Paul. First initialize the array.
results = new contact_detail[dt.Rows.Count];
for ( int i=0; i < dt.Rows.Count ; i++ )
{
// 02/20/2006 Paul. Then initialize each element in the array.
results[i] = new contact_detail();
results[i].email_address = Sql.ToString(dt.Rows[i]["EMAIL_ADDRESS"]);
results[i].name1 = Sql.ToString(dt.Rows[i]["NAME1" ]);
results[i].name2 = Sql.ToString(dt.Rows[i]["NAME2" ]);
results[i].association = Sql.ToString(dt.Rows[i]["ASSOCIATION" ]);
results[i].id = Sql.ToString(dt.Rows[i]["ID" ]);
results[i].type = Sql.ToString(dt.Rows[i]["TYPE" ]);
results[i].msi_id = (i+1).ToString();
}
}
}
}
}
catch(Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
throw(new Exception("SOAP: Failed search()", ex));
}
}
}
return results;
}
