public string[] GetRelationships(string module, string id, string sRelMod,
string sRelModQuery, int deleted)
{
module = ConvertToProper(module);
get_relationships_result result = _sugarsoap.get_relationships(_session, module, id, sRelMod, sRelModQuery,
deleted);
VerifySugarResult.Verify(result.error);
id_mod[] ids = result.ids;
var strArray2 = new string[ids.Length];
int index = 0;
foreach (id_mod mod in ids)
{
strArray2[index] = mod.id;
index++;
}
return(strArray2);
}
public get_relationships_result get_relationships(string session, string module_name, string module_id, string related_module, string related_module_query, int deleted)
{
Guid gUSER_ID = GetSessionUserID(session);
Guid gTIMEZONE = Sql.ToGuid(HttpContext.Current.Cache.Get("soap.user.timezone." + gUSER_ID.ToString()));
TimeZone T10n = TimeZone.CreateTimeZone(gTIMEZONE);
string sTABLE_NAME = VerifyModuleName(module_name );
int nACLACCESS = Security.GetUserAccess(module_name, "list");
if ( nACLACCESS < 0 )
{
L10N L10n = new L10N("en-US");
throw(new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
}
sTABLE_NAME = VerifyModuleName(related_module);
nACLACCESS = Security.GetUserAccess(related_module, "list");
if ( nACLACCESS < 0 )
{
L10N L10n = new L10N("en-US");
throw(new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
}
get_relationships_result results = new get_relationships_result();
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using ( IDbConnection con = dbf.CreateConnection() )
{
con.Open();
string sSQL;
// 02/16/2006 Paul. Providing a way to directly access tables is a hacker's dream.
// We will not do that here. We will require that all relationships be defined in a SQL view.
using ( IDbCommand cmd = con.CreateCommand() )
{
switch ( module_name )
{
case "Contacts":
{
switch ( related_module )
{
case "Calls":
{
sSQL = "select * " + ControlChars.CrLf
+ " from vwCONTACTS_CALLS_Soap " + ControlChars.CrLf
+ " where PRIMARY_ID = @PRIMARY_ID" + ControlChars.CrLf
+ " and DELETED = @DELETED " + ControlChars.CrLf;
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@PRIMARY_ID", module_id );
Sql.AddParameter(cmd, "@DELETED" , Math.Min(deleted, 1));
if ( !Sql.IsEmptyString(related_module_query) )
{
DateTime dtBeginDate = DateTime.MinValue;
DateTime dtEndDate = DateTime.MinValue;
ParseDateRange(related_module_query, "DATE_START", T10n, ref dtBeginDate, ref dtEndDate);
if ( dtBeginDate != DateTime.MinValue )
{
cmd.CommandText += " and DATE_START > @BEGIN_DATE" + ControlChars.CrLf;
Sql.AddParameter(cmd, "@BEGIN_DATE", dtBeginDate);
}
if ( dtEndDate != DateTime.MinValue )
{
cmd.CommandText += " and DATE_START < @END_DATE " + ControlChars.CrLf;
Sql.AddParameter(cmd, "@END_DATE" , dtEndDate );
}
}
break;
}
case "Meetings":
{
sSQL = "select * " + ControlChars.CrLf
+ " from vwCONTACTS_MEETINGS_Soap" + ControlChars.CrLf
+ " where PRIMARY_ID = @PRIMARY_ID" + ControlChars.CrLf
+ " and DELETED = @DELETED " + ControlChars.CrLf;
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@PRIMARY_ID", module_id );
Sql.AddParameter(cmd, "@DELETED" , Math.Min(deleted, 1));
if ( !Sql.IsEmptyString(related_module_query) )
{
DateTime dtBeginDate = DateTime.MinValue;
DateTime dtEndDate = DateTime.MinValue;
ParseDateRange(related_module_query, "DATE_START", T10n, ref dtBeginDate, ref dtEndDate);
if ( dtBeginDate != DateTime.MinValue )
{
cmd.CommandText += " and DATE_START > @BEGIN_DATE" + ControlChars.CrLf;
Sql.AddParameter(cmd, "@BEGIN_DATE", dtBeginDate);
}
if ( dtEndDate != DateTime.MinValue )
{
cmd.CommandText += " and DATE_START < @END_DATE " + ControlChars.CrLf;
Sql.AddParameter(cmd, "@END_DATE" , dtEndDate );
}
}
break;
}
case "Users":
{
sSQL = "select * " + ControlChars.CrLf
+ " from vwCONTACTS_USERS_Soap " + ControlChars.CrLf
+ " where PRIMARY_ID = @PRIMARY_ID" + ControlChars.CrLf
+ " and DELETED = @DELETED " + ControlChars.CrLf;
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@PRIMARY_ID", module_id );
Sql.AddParameter(cmd, "@DELETED" , Math.Min(deleted, 1));
if ( !Sql.IsEmptyString(related_module_query) )
{
throw(new Exception(String.Format("A related_module_query is not allowed at this time.", module_name, related_module)));
}
break;
}
default:
{
throw(new Exception(String.Format("Relationship between {0} and {1} is not defined", module_name, related_module)));
}
}
break;
}
case "Users":
{
switch ( related_module )
{
case "Calls":
{
sSQL = "select * " + ControlChars.CrLf
+ " from vwUSERS_CALLS_Soap " + ControlChars.CrLf
+ " where PRIMARY_ID = @PRIMARY_ID" + ControlChars.CrLf
+ " and DELETED = @DELETED " + ControlChars.CrLf;
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@PRIMARY_ID", module_id );
Sql.AddParameter(cmd, "@DELETED" , Math.Min(deleted, 1));
if ( !Sql.IsEmptyString(related_module_query) )
{
DateTime dtBeginDate = DateTime.MinValue;
DateTime dtEndDate = DateTime.MinValue;
ParseDateRange(related_module_query, "DATE_START", T10n, ref dtBeginDate, ref dtEndDate);
if ( dtBeginDate != DateTime.MinValue )
{
cmd.CommandText += " and DATE_START > @BEGIN_DATE" + ControlChars.CrLf;
Sql.AddParameter(cmd, "@BEGIN_DATE", dtBeginDate);
}
if ( dtEndDate != DateTime.MinValue )
{
cmd.CommandText += " and DATE_START < @END_DATE " + ControlChars.CrLf;
Sql.AddParameter(cmd, "@END_DATE" , dtEndDate );
}
}
break;
}
case "Meetings":
{
sSQL = "select * " + ControlChars.CrLf
+ " from vwUSERS_MEETINGS_Soap " + ControlChars.CrLf
+ " where PRIMARY_ID = @PRIMARY_ID" + ControlChars.CrLf
+ " and DELETED = @DELETED " + ControlChars.CrLf;
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@PRIMARY_ID", module_id );
Sql.AddParameter(cmd, "@DELETED" , Math.Min(deleted, 1));
if ( !Sql.IsEmptyString(related_module_query) )
{
DateTime dtBeginDate = DateTime.MinValue;
DateTime dtEndDate = DateTime.MinValue;
ParseDateRange(related_module_query, "DATE_START", T10n, ref dtBeginDate, ref dtEndDate);
if ( dtBeginDate != DateTime.MinValue )
{
cmd.CommandText += " and DATE_START > @BEGIN_DATE" + ControlChars.CrLf;
Sql.AddParameter(cmd, "@BEGIN_DATE", dtBeginDate);
}
if ( dtEndDate != DateTime.MinValue )
{
cmd.CommandText += " and DATE_START < @END_DATE " + ControlChars.CrLf;
Sql.AddParameter(cmd, "@END_DATE" , dtEndDate );
}
}
break;
}
case "Contacts":
{
sSQL = "select * " + ControlChars.CrLf
+ " from vwUSERS_CONTACTS_Soap " + ControlChars.CrLf
+ " where PRIMARY_ID = @PRIMARY_ID" + ControlChars.CrLf
+ " and DELETED = @DELETED " + ControlChars.CrLf;
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@PRIMARY_ID", module_id );
Sql.AddParameter(cmd, "@DELETED" , Math.Min(deleted, 1));
if ( !Sql.IsEmptyString(related_module_query) )
{
throw(new Exception(String.Format("A related_module_query is not allowed at this time.", module_name, related_module)));
}
break;
}
default:
{
throw(new Exception(String.Format("Relationship between {0} and {1} is not defined", module_name, related_module)));
}
}
break;
}
case "Meetings":
{
switch ( related_module )
{
case "Contacts":
{
sSQL = "select * " + ControlChars.CrLf
+ " from vwMEETINGS_CONTACTS_Soap" + ControlChars.CrLf
+ " where PRIMARY_ID = @PRIMARY_ID" + ControlChars.CrLf
+ " and DELETED = @DELETED " + ControlChars.CrLf;
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@PRIMARY_ID", module_id );
Sql.AddParameter(cmd, "@DELETED" , Math.Min(deleted, 1));
if ( !Sql.IsEmptyString(related_module_query) )
{
DateTime dtBeginDate = DateTime.MinValue;
DateTime dtEndDate = DateTime.MinValue;
ParseDateRange(related_module_query, "DATE_START", T10n, ref dtBeginDate, ref dtEndDate);
if ( dtBeginDate != DateTime.MinValue )
{
cmd.CommandText += " and DATE_START > @BEGIN_DATE" + ControlChars.CrLf;
Sql.AddParameter(cmd, "@BEGIN_DATE", dtBeginDate);
}
if ( dtEndDate != DateTime.MinValue )
{
cmd.CommandText += " and DATE_START < @END_DATE " + ControlChars.CrLf;
Sql.AddParameter(cmd, "@END_DATE" , dtEndDate );
}
}
break;
}
case "Users":
{
sSQL = "select * " + ControlChars.CrLf
+ " from vwMEETINGS_USERS_Soap " + ControlChars.CrLf
+ " where PRIMARY_ID = @PRIMARY_ID" + ControlChars.CrLf
+ " and DELETED = @DELETED " + ControlChars.CrLf;
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@PRIMARY_ID", module_id );
Sql.AddParameter(cmd, "@DELETED" , Math.Min(deleted, 1));
if ( !Sql.IsEmptyString(related_module_query) )
{
DateTime dtBeginDate = DateTime.MinValue;
DateTime dtEndDate = DateTime.MinValue;
ParseDateRange(related_module_query, "DATE_START", T10n, ref dtBeginDate, ref dtEndDate);
if ( dtBeginDate != DateTime.MinValue )
{
cmd.CommandText += " and DATE_START > @BEGIN_DATE" + ControlChars.CrLf;
Sql.AddParameter(cmd, "@BEGIN_DATE", dtBeginDate);
}
if ( dtEndDate != DateTime.MinValue )
{
cmd.CommandText += " and DATE_START < @END_DATE " + ControlChars.CrLf;
Sql.AddParameter(cmd, "@END_DATE" , dtEndDate );
}
}
break;
}
default:
{
throw(new Exception(String.Format("Relationship between {0} and {1} is not defined", module_name, related_module)));
}
}
break;
}
case "Calls":
{
switch ( related_module )
{
case "Contacts":
{
sSQL = "select * " + ControlChars.CrLf
+ " from vwCALLS_CONTACTS_Soap " + ControlChars.CrLf
+ " where PRIMARY_ID = @PRIMARY_ID" + ControlChars.CrLf
+ " and DELETED = @DELETED " + ControlChars.CrLf;
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@PRIMARY_ID", module_id );
Sql.AddParameter(cmd, "@DELETED" , Math.Min(deleted, 1));
if ( !Sql.IsEmptyString(related_module_query) )
{
DateTime dtBeginDate = DateTime.MinValue;
DateTime dtEndDate = DateTime.MinValue;
ParseDateRange(related_module_query, "DATE_START", T10n, ref dtBeginDate, ref dtEndDate);
if ( dtBeginDate != DateTime.MinValue )
{
cmd.CommandText += " and DATE_START > @BEGIN_DATE" + ControlChars.CrLf;
Sql.AddParameter(cmd, "@BEGIN_DATE", dtBeginDate);
}
if ( dtEndDate != DateTime.MinValue )
{
cmd.CommandText += " and DATE_START < @END_DATE " + ControlChars.CrLf;
Sql.AddParameter(cmd, "@END_DATE" , dtEndDate );
}
}
break;
}
case "Users":
{
sSQL = "select * " + ControlChars.CrLf
+ " from vwCALLS_USERS_Soap " + ControlChars.CrLf
+ " where PRIMARY_ID = @PRIMARY_ID" + ControlChars.CrLf
+ " and DELETED = @DELETED " + ControlChars.CrLf;
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@PRIMARY_ID", module_id );
Sql.AddParameter(cmd, "@DELETED" , Math.Min(deleted, 1));
if ( !Sql.IsEmptyString(related_module_query) )
{
DateTime dtBeginDate = DateTime.MinValue;
DateTime dtEndDate = DateTime.MinValue;
ParseDateRange(related_module_query, "DATE_START", T10n, ref dtBeginDate, ref dtEndDate);
if ( dtBeginDate != DateTime.MinValue )
{
cmd.CommandText += " and DATE_START > @BEGIN_DATE" + ControlChars.CrLf;
Sql.AddParameter(cmd, "@BEGIN_DATE", dtBeginDate);
}
if ( dtEndDate != DateTime.MinValue )
{
cmd.CommandText += " and DATE_START < @END_DATE " + ControlChars.CrLf;
Sql.AddParameter(cmd, "@END_DATE" , dtEndDate );
}
}
break;
}
default:
{
throw(new Exception(String.Format("Relationship between {0} and {1} is not defined", module_name, related_module)));
}
}
break;
}
case "Accounts":
{
switch ( related_module )
{
case "Contacts":
{
sSQL = "select * " + ControlChars.CrLf
+ " from vwACCOUNTS_CONTACTS_Soap" + ControlChars.CrLf
+ " where PRIMARY_ID = @PRIMARY_ID" + ControlChars.CrLf
+ " and DELETED = @DELETED " + ControlChars.CrLf;
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@PRIMARY_ID", module_id );
Sql.AddParameter(cmd, "@DELETED" , Math.Min(deleted, 1));
if ( !Sql.IsEmptyString(related_module_query) )
{
throw(new Exception(String.Format("A related_module_query is not allowed at this time.", module_name, related_module)));
}
break;
}
case "Users":
{
sSQL = "select * " + ControlChars.CrLf
+ " from vwACCOUNTS_USERS_Soap " + ControlChars.CrLf
+ " where PRIMARY_ID = @PRIMARY_ID" + ControlChars.CrLf
+ " and DELETED = @DELETED " + ControlChars.CrLf;
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@PRIMARY_ID", module_id );
Sql.AddParameter(cmd, "@DELETED" , Math.Min(deleted, 1));
if ( !Sql.IsEmptyString(related_module_query) )
{
throw(new Exception(String.Format("A related_module_query is not allowed at this time.", module_name, related_module)));
}
break;
}
default:
{
throw(new Exception(String.Format("Relationship between {0} and {1} is not defined", module_name, related_module)));
}
}
break;
}
default:
{
throw(new Exception(String.Format("Relationship between {0} and {1} is not defined", module_name, related_module)));
}
}
try
{
CultureInfo ciEnglish = CultureInfo.CreateSpecificCulture("en-US");
using ( DbDataAdapter da = dbf.CreateDataAdapter() )
{
((IDbDataAdapter)da).SelectCommand = cmd;
using ( DataTable dt = new DataTable() )
{
da.Fill(dt);
if ( dt.Rows.Count > 0 )
{
results.ids = new id_mod[dt.Rows.Count];
int i = 0;
foreach ( DataRow row in dt.Rows )
{
results.ids[i] = new id_mod();
results.ids[i].id = Sql.ToString (row["RELATED_ID" ]);
results.ids[i].deleted = Sql.ToInteger (row["DELETED" ]);
// 06/13/2006 Paul. Italian has a problem with the time separator. Use the value from the culture from CalendarControl.SqlDateTimeFormat.
// 06/14/2006 Paul. The Italian problem was that it was using the culture separator, but DataView only supports the en-US format.
// 08/17/2006 Paul. The time on the server and the time in the database are both considered ServerTime.
DateTime dtDATE_MODIFIED_ServerTime = Sql.ToDateTime(row["DATE_MODIFIED"]);
// 08/17/2006 Paul. We need a special function to convert to UniversalTime because it might already be in UniversalTime, based on m_bGMTStorage flag.
DateTime dtDATE_MODIFIED_UniversalTime = T10n.ToUniversalTimeFromServerTime(dtDATE_MODIFIED_ServerTime);
results.ids[i].date_modified = dtDATE_MODIFIED_UniversalTime.ToString(CalendarControl.SqlDateTimeFormat, ciEnglish.DateTimeFormat);
i++;
}
}
}
}
}
catch(Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
throw(new Exception("SOAP: Failed get_relationships", ex));
}
}
}
return results;
}