/// <summary>
/// Edit a new or existing calculated field
/// </summary>
/// <param name="uo">Existing UserObject with content or null to create a new CF</param>
/// <returns></returns>
public static UserObject Edit(UserObject uo)
{
if (uo == null) // prompt if not supplied
{
uo = UserObjectOpenDialog.ShowDialog(UserObjectType.CalcField, "Open Calculated Field");
}
if (uo == null)
{
return(null);
}
uo = UserObjectDao.Read(uo);
if (uo == null)
{
return(null);
}
CalcField cf = CalcField.Deserialize(uo.Content);
if (cf == null)
{
return(null);
}
uo = Edit(cf, uo);
return(uo);
}
/// <summary>
/// Edit and existing Spotfire link
/// </summary>
/// <param name="internalName"></param>
/// <returns></returns>
public static UserObject Edit(string internalName)
{
UserObject uo = null;
if (Lex.IsDefined(internalName)) // get the user object
{
uo = UserObject.ParseInternalUserObjectName(internalName, "");
if (uo != null)
{
uo = UserObjectDao.Read(uo.Id);
}
if (uo == null)
{
throw new Exception("User object not found " + internalName);
}
}
else
{
uo = null; // no arg, show open dialog
}
UserObject uo2 = Edit(uo);
if (uo2 != null && uo2.Id > 0)
{
QbUtil.UpdateMetaTableCollection(uo2.InternalName);
}
return(uo2);
}
/// <summary>
/// Set flag to try to browse results upon open in both the saved query and any currently open query
/// </summary>
/// <param name="qid"></param>
void SetBrowseSavedResultsUponOpenForQuery(
int qid,
bool value)
{
if (qid <= 0)
{
return;
}
UserObject uo = UserObjectDao.Read(qid); // update any saved version of query
if (uo == null || uo.Type != UserObjectType.Query)
{
return;
}
Query q = Query.Deserialize(uo.Content);
q.BrowseSavedResultsUponOpen = value;
uo.Content = q.Serialize();
UserObjectDao.Write(uo, uo.Id);
int di = QueriesControl.Instance.GetQueryIndexByUserObjectId(qid); // update any in-memory version of query
if (di < 0)
{
return;
}
q = QueriesControl.Instance.GetQuery(di);
q.BrowseSavedResultsUponOpen = value;
return; // todo
}
/// <summary>
/// Edit a new or existing link
/// </summary>
/// <param name="uo">Existing UserObject with content or null to create a new link</param>
/// <returns></returns>
public static UserObject Edit(UserObject uo)
{
if (uo == null) // prompt if not supplied
{
uo = UserObjectOpenDialog.ShowDialog(UserObjectType.SpotfireLink, "Edit Spotfire Link");
}
if (uo == null)
{
return(null);
}
uo = UserObjectDao.Read(uo);
if (uo == null)
{
throw new Exception("User object not found: " + uo.Id);
}
SpotfireViewProps sl = SpotfireViewProps.Deserialize(uo.Content);
if (sl == null)
{
return(null);
}
uo = Edit(sl, uo);
return(uo);
}
/// <summary>
/// Build query to get summarization detail for RgroupMatrix data element
/// </summary>
/// <param name="mt"></param>
/// <param name="mc"></param>
/// <param name="resultId">act_code.compound_id</param>
/// <returns></returns>
public override Query GetDrilldownDetailQuery(
MetaTable mt,
MetaColumn mc,
int level,
string resultId)
{
QueryColumn qc;
// ResultId is of the form: queryId, mtName, mcName, sn1, sn2,...snn
string[] sa = resultId.Split(',');
int objectId = Int32.Parse(sa[0]);
UserObject uo = UserObjectDao.Read(objectId);
if (uo == null) return null; // no longer there
Query q = Query.Deserialize(uo.Content);
q.ResultKeys = null; // clear any set of result keys
if (q.LogicType == QueryLogicType.Complex)
{ // if complex logic then go simple (todo: fix to handle complex logic)
q.ClearAllQueryColumnCriteria();
q.LogicType = QueryLogicType.And;
}
q.KeyCriteria = "in (";
for (int i1 = 3; i1 < sa.Length; i1++)
{
q.KeyCriteria += Lex.AddSingleQuotes(sa[i1]);
if (i1 < sa.Length - 1) q.KeyCriteria += ",";
}
q.KeyCriteria += ")";
return q;
}
/// <summary>
/// Read in focused UserObject
/// </summary>
/// <param name="ri"></param>
/// <param name="dr"></param>
/// <param name="ucdb"></param>
/// <param name="uo"></param>
/// <returns></returns>
bool GetFocusedUo(out int ri, out DataRow dr, out UcdbDatabase ucdb, out UserObject uo)
{
dr = null;
ucdb = null;
uo = null;
ri = GridView.FocusedRowHandle;
if (ri < 0)
{
MessageBoxMx.ShowError("You must select the user compound database first");
return(false);
}
dr = DataTable.Rows[ri];
ucdb = (UcdbDatabase)dr["Ucdb"];
uo = UserObjectDao.Read(UserObjectType.UserDatabase, ucdb.OwnerUserName, ucdb.NameSpace, ucdb.Name);
if (uo == null)
{
MessageBoxMx.ShowError("Unable to find associated user database \"UserObject\" information");
return(false);
}
return(true);
}
/// <summary>
/// Pass drilldown along to first metacolumn in calc field for processing
/// </summary>
/// <param name="mt"></param>
/// <param name="mc"></param>
/// <param name="level"></param>
/// <param name="resultId"></param>
/// <returns></returns>
public override Query GetDrilldownDetailQuery(
MetaTable mt,
MetaColumn mc,
int level,
string linkInfoString)
{
bool summary;
int tableId;
MetaTable.ParseMetaTableName(mt.Name, out tableId, out summary);
UserObject uo = UserObjectDao.Read(tableId);
if (uo == null)
{
return(null);
}
CalcField cf = CalcField.Deserialize(uo.Content);
if (cf == null)
{
return(null);
}
if (cf.MetaColumn1 == null || cf.MetaColumn1.MetaTable == null)
{
return(null);
}
Query q = QueryEngine.GetDrilldownDetailQuery(cf.MetaColumn1.MetaTable, cf.MetaColumn1, level, linkInfoString);
return(q);
}
/// <summary>
/// Read a query & return with metatables embedded in the query for UserObject
/// </summary>
/// <param name="uo"></param>
/// <returns>The requested user object or null if no matching user object is found.</returns>
public static UserObject ReadQueryWithMetaTables(UserObject uo)
{
Query q, q2;
uo = UserObjectDao.Read(uo);
if (uo == null)
{
return(null);
}
// Sharing a query implicitly shares any underlying annotations, calc fields & lists.
// Mark these objects as readable within the context of this query even if they are not shared.
Permissions.AllowTemporaryPublicReadAccessToAllUserObjects = true;
q = Query.Deserialize(uo.Content);
foreach (QueryTable qt in q.Tables)
{ // mark any saved lists in criteria as temporarily readable since the query is readable
foreach (QueryColumn qc in qt.QueryColumns)
{
if (qc.MetaColumn.DataType == MetaColumnType.CompoundId &&
Lex.Contains(qc.Criteria, "IN LIST"))
{
int id;
string criteria = qc.MetaColumn.Name + " " + qc.Criteria;
ParsedSingleCriteria psc = MqlUtil.ParseSingleCriteria(criteria);
if (psc == null)
{
continue;
}
psc.Value = Lex.RemoveAllQuotes(psc.Value);
if (Lex.IsUndefined(psc.Value))
{
continue;
}
if (UserObject.TryParseObjectIdFromInternalName(psc.Value, out id) && id > 0)
{
Permissions.TemporaryPublicReadAccessUserObjects[id] = null;
}
}
}
}
Permissions.AllowTemporaryPublicReadAccessToAllUserObjects = false;
uo.Content = q.Serialize(true); // serialize with metatables
return(uo);
}
/// <summary>
/// Read a query & return with metatables embedded in the query given the object id
/// </summary>
/// <param name="objectId">id of item to read</param>
/// <returns>The requested user object or null if no matching user object is found.</returns>
public static UserObject ReadQueryWithMetaTables(int objectId)
{
UserObject uo = UserObjectDao.Read(objectId);
if (uo == null)
{
return(null);
}
Query q = Query.Deserialize(uo.Content);
uo.Content = q.Serialize(true); // serialize with metatables
return(uo);
}
/// <summary>
/// Get the MQL for a saved query
/// </summary>
/// <param name="queryId"></param>
/// <returns></returns>
///
public static string GetSavedQueryMQL(
int queryId, bool mobileQuery = false)
{
UserObject uo = UserObjectDao.Read(queryId);
if (uo == null)
{
return(null);
}
Query q = Query.Deserialize(uo.Content);
string mql = MqlUtil.ConvertQueryToMql(q, mobileQuery); // to mql
return(mql);
}
public static UserObject GetNextAlert(string processorId, string alertQueueFileName)
{
Mutex mutex = new Mutex(false, _mobiusAlertQueue);
mutex.WaitOne(); // get exclusive access
int alertId;
try
{
StreamReader sr = new StreamReader(alertQueueFileName);
string content = sr.ReadToEnd();
sr.Close();
if (Lex.IsNullOrEmpty(content)) // all done if nothing left in queue
{
//AlertCount = 0;
return(null);
}
int i1 = content.IndexOf(",");
if (i1 >= 0)
{
alertId = Int32.Parse(content.Substring(0, i1));
content = content.Substring(i1 + 1);
}
else
{
alertId = Int32.Parse(content);
content = "";
}
StreamWriter sw = new StreamWriter(alertQueueFileName);
sw.Write(content);
sw.Close();
//_remainingingAlertCount--;
}
catch (Exception ex)
{
AlertUtil.LogAlertMessage("Error accessing alert queue: " + ex.Message + processorId);
return(null);
}
finally { mutex.ReleaseMutex(); }
UserObject uo = UserObjectDao.Read(alertId); // read the alert
if (uo == null)
{
AlertUtil.LogAlertMessage("Error reading alert " + alertId + processorId);
}
return(uo);
}
/// <summary>
/// Prompt user for annotation and return associated metatable
/// </summary>
/// <param name="caption"></param>
/// <returns></returns>
public static MetaTable SelectAnnotationTable(
string caption)
{
UserObject uo = UserObjectOpenDialog.ShowDialog(UserObjectType.Annotation, caption);
if (uo == null)
{
return(null);
}
uo = UserObjectDao.Read(uo);
MetaTable mt = MetaTable.Deserialize(uo.Content); // deserialize metatable xml
return(mt);
}
/// <summary>
/// Edit a new or existing calculated field
/// </summary>
/// <param name="uo">Existing UserObject with content or null to create a new CF</param>
/// <returns></returns>
public static CondFormat EditUserObject(
UserObject uo = null)
{
if (uo == null) // prompt if not supplied
{
UserObject defaultUo = new UserObject(UserObjectType.CondFormat);
defaultUo.ParentFolder = CondFormat.PredefinedCondFormatFolderName;
uo = UserObjectOpenDialog.ShowDialog(UserObjectType.CondFormat, "Edit Conditional Formatting", defaultUo);
}
if (uo == null)
{
return(null);
}
uo = UserObjectDao.Read(uo);
if (uo == null)
{
return(null);
}
CondFormat cf = CondFormat.Deserialize(uo.Content);
if (cf == null)
{
return(null);
}
string title = "Conditional Formatting - " + uo.Name + " (" + uo.InternalName + ")";
CondFormat cf2 = Edit(cf, true, title);
if (cf2 == null)
{
return(null);
}
uo.Content = cf2.Serialize();
UserObjectDao.Write(uo, uo.Id);
if (MainMenuControl != null)
{
MainMenuControl.UpdateMruList(uo.InternalName);
}
CondFormat.UpdatePredefined(uo.InternalName, cf2); // update the dictionary of predefined CFs
UpdateExistingCfReferences(uo, cf2); // update any references to this cf in the list of open queries
return(cf);
}
/// <summary>
/// Read a compoundId list given the listId
/// </summary>
/// <param name="listId"></param>
/// <param name="mt"></param>
/// <returns></returns>
public static CidList Read(
int listId,
MetaTable mt)
{
UserObject uo = UserObjectDao.Read(listId);
if (uo == null)
{
return(new CidList());
}
else
{
return(CidList.Deserialize(uo, mt));
}
}
/// <summary>
/// Edit and existing calculated field
/// </summary>
/// <param name="internalName"></param>
/// <returns></returns>
public static UserObject Edit(string internalName)
{
UserObject uo = UserObject.ParseInternalUserObjectName(internalName, "");
if (uo != null)
{
uo = UserObjectDao.Read(uo.Id);
}
if (uo == null)
{
throw new Exception("User object not found " + internalName);
}
UserObject uo2 = Edit(uo);
return(uo2);
}
void FillDataTable()
{
UcdbDatabase ucdb, ucdb2;
string headerString;
long databaseId;
UserObject uo;
UserDataEditor editor;
UcdbDatabase[] ucdbArray;
List <UcdbDatabase> ucdbList = new List <UcdbDatabase>();
List <UserObject> uoList = null;
Dictionary <long, UserObject> uoDict = null;
int li, i1;
//if (!FailedOnly) // Get list of databases
{
if (OwnerUserName != "") // show databases for specified user
{
ucdbArray = Udbs.SelectDatabaseHeaders(OwnerUserName);
}
else
{
ucdbArray = Udbs.SelectDatabaseHeaders(); // get for all users
}
}
//else ucdbArray = Udbs.SelectFailedUpdates(); // show databases with failed updates
foreach (UcdbDatabase ucdb0 in ucdbArray)
{
uo = UserObjectDao.Read(UserObjectType.UserDatabase, ucdb0.OwnerUserName, ucdb0.NameSpace, ucdb0.Name);
if (uo != null)
{
ucdbList.Add(ucdb0);
}
}
DataTable.Rows.Clear();
foreach (UcdbDatabase ucdb0 in ucdbList)
{
DataRow dr = DataTable.NewRow();
SetupDataRow(dr, ucdb0);
DataTable.Rows.Add(dr);
}
return;
}
/// <summary>
/// Command entry to execute a query in the background and save the results for later retrieval
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public static string RunBackgroundQuery(
string args)
{
UserObject uo = null;
bool sendEmail = false;
string emailSubject = null;
string msg;
int queryId = -1;
string[] sa = args.Split(' ');
if (sa.Length > 0)
{
if (int.TryParse(sa[0].Trim(), out queryId))
{
uo = UserObjectDao.Read(queryId);
}
}
if (uo == null)
{
emailSubject = UmlautMobius.String + " background query results error";
msg = "RunQueryInBackground failed to read query " + queryId;
ServicesLog.Message(msg);
Email.Send(
null, SS.I.UserInfo.EmailAddress, emailSubject, msg);
return(msg);
}
if (sa.Length > 1)
{
bool.TryParse(sa[1].Trim(), out sendEmail);
}
Query q = Query.Deserialize(uo.Content);
q.UserObject = uo; // copy user object to get current name, etc.
q.IncludeRootTableAsNeeded();
if (sendEmail)
{
emailSubject = UmlautMobius.String + " background query results for " + Lex.Dq(q.UserObject.Name);
}
return(RunBackgroundQuery(q, emailSubject, "MobiusBackgroundQueryEmailTemplate.htm"));
}
/// <summary>
/// Read a compound id list given an internal list name (e.g. FOLDER_123.name or LIST_1234)
/// </summary>
/// <param name="name"></param>
/// <param name="mt"></param>
/// <returns></returns>
public static CidList Read(
string internalName,
MetaTable mt,
bool allowLocalRead)
{
UserObject uo, uo2;
string fileName, cn;
int i1;
uo = UserObjectUtil.ParseInternalUserObjectName(internalName, UserObjectType.CnList);
if (uo == null)
{
return(null);
}
if (allowLocalRead && UserObject.IsCurrentObjectInternalName(internalName)) // get from the current query
{
return(ReadCurrentListLocal());
}
uo2 = UserObjectDao.Read(uo); // get from the server side
if (uo2 == null)
{
MessageBoxMx.ShowError(
"Unable to find list: " + internalName + "\r\n\r\n" +
CommandExec.GetUserObjectReadAccessErrorMessage(uo.Id, "list"));
return(null);
}
CidList cnList = CidList.Deserialize(uo2, mt);
if (UserObject.IsCurrentObjectInternalName(internalName)) // if current list store keys with query
{
SessionManager.CurrentResultKeys = cnList.ToStringList();
}
return(cnList);
}
/// <summary>
/// Read a compound id list given a list name
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public static CidList Read(
string name,
MetaTable mt)
{
UserObject uo;
CidList cnList = new CidList();
string fileName, cn;
int i1;
uo = UserObject.ParseInternalUserObjectName(name, UserObjectType.CnList, Security.UserName);
if (uo == null)
{
return(null);
}
uo = UserObjectDao.Read(uo);
if (uo == null)
{
return(new CidList());
}
return(CidList.Deserialize(uo, mt));
}
/// <summary>
/// Create an annotation table from a DataTable
/// </summary>
/// <param name="fullyQualifiedName">Fully qualified name to assign to table</param>
/// <param name="dataTable">DataTable containing table definition & data</param>
/// <param name="showProgress">Display dialog box showing progress of creation</param>
/// <returns>Internal name assigned to annotation table (ANNOTATION_12345)</returns>
public static MetaTable CreateAnnotationTable(
string fullyQualifiedName,
DataTable dataTable,
bool showProgress)
{
List <AnnotationVo> voList = new List <AnnotationVo>();
AnnotationVo avo;
if (dataTable == null)
{
DebugMx.ArgException("DataTable is null");
}
if (dataTable.Columns.Count == 0)
{
DebugMx.ArgException("No DataColumns are defined");
}
if (dataTable.Columns[0].DataType != typeof(CompoundId))
{
DebugMx.ArgException("The first column must be of type CompoundId");
}
if (showProgress)
{
Progress.Show("Creating annotation table...");
}
AnnotationDao aDao = new AnnotationDao();
UserObject auo = UserObjectUtil.ParseInternalUserObjectName(fullyQualifiedName);
auo.Type = UserObjectType.Annotation;
UserObjectTree.GetValidUserObjectTypeFolder(auo);
UserObject auo2 = UserObjectDao.Read(auo); // see if there already
MetaTable oldMt = null;
if (auo2 == null) // get new identifier
{
auo.Id = UserObjectDao.GetNextId(); // id to store table under
}
else // reuse identifier
{
auo.Id = auo2.Id; // copy it over
aDao.DeleteTable(auo.Id); // delete any existing data
string oldMtName = "ANNOTATION_" + auo2.Id;
oldMt = MetaTableCollection.Get(oldMtName);
}
MetaTable mt = new MetaTable();
mt.MetaBrokerType = MetaBrokerType.Annotation;
mt.Name = "ANNOTATION_" + auo.Id; // name table by uo
mt.Label = auo.Name;
mt.Code = auo.Id.ToString(); // code for the metatable
int mtCode = auo.Id;
if (dataTable.ExtendedProperties.ContainsKey("ParentTableName"))
{
mt.Parent = MetaTableCollection.Get((string)dataTable.ExtendedProperties["ParentTableName"]);
}
foreach (DataColumn dc in dataTable.Columns)
{
MetaColumn mc = new MetaColumn();
mc.MetaTable = mt;
mc.Name = dc.ColumnName;
MetaColumn oldMc = null;
if (oldMt != null)
{
oldMc = oldMt.GetMetaColumnByName(mc.Name); // see if column name exists
}
if (oldMc != null && oldMc.ResultCode != "") // use any existing code
{
mc.ResultCode = oldMc.ResultCode;
}
else
{
mc.ResultCode = aDao.GetNextIdLong().ToString();
}
if (dc.Caption != null)
{
mc.Label = dc.Caption;
}
else
{
mc.Label = mc.Name;
}
if (dc.DataType == typeof(CompoundId))
{
mc.DataType = MetaColumnType.CompoundId;
if (dc.ExtendedProperties.ContainsKey("StorageType") && dc.ExtendedProperties["StorageType"] is MetaColumnType &&
((MetaColumnType)dc.ExtendedProperties["StorageType"]) == MetaColumnType.String)
{
mc.ColumnMap = "EXT_CMPND_ID_TXT"; // text column
}
else
{
mc.ColumnMap = "EXT_CMPND_ID_NBR"; // numeric column otherwise
}
}
else if (dc.DataType == typeof(int) || dc.DataType == typeof(Int16) || dc.DataType == typeof(Int32))
{
mc.DataType = MetaColumnType.Integer;
}
else if (dc.DataType == typeof(float) || dc.DataType == typeof(double))
{
mc.DataType = MetaColumnType.Number;
}
else if (dc.DataType == typeof(QualifiedNumber))
{
mc.DataType = MetaColumnType.QualifiedNo;
}
else if (dc.DataType == typeof(string))
{
mc.DataType = MetaColumnType.String;
}
else if (dc.DataType == typeof(DateTime))
{
mc.DataType = MetaColumnType.Date;
}
else if (dc.DataType == typeof(MoleculeMx))
{
mc.DataType = MetaColumnType.Structure;
}
else
{
throw new Exception("Invalid data type " + dc.DataType.ToString());
}
if (dc.ExtendedProperties.ContainsKey("DisplayLevel"))
{
mc.InitialSelection = (ColumnSelectionEnum)dc.ExtendedProperties["DisplayLevel"];
}
if (dc.ExtendedProperties.ContainsKey("DisplayWidth"))
{
mc.Width = (float)dc.ExtendedProperties["DisplayWidth"];
}
if (dc.ExtendedProperties.ContainsKey("DisplayFormat"))
{
mc.Format = (ColumnFormatEnum)dc.ExtendedProperties["DisplayFormat"];
}
if (dc.ExtendedProperties.ContainsKey("Decimals"))
{
mc.Decimals = (int)dc.ExtendedProperties["Decimals"];
}
mt.MetaColumns.Add(mc);
}
ToolHelper.CreateAnnotationTable(mt, auo);
aDao.BeginTransaction(); // insert all data in single transaction
if (showProgress)
{
Progress.Show("Writing data to annotation table...");
}
int t1 = TimeOfDay.Milliseconds();
int writeCount = 0;
foreach (DataRow dr in dataTable.Rows)
{
if (dr.IsNull(0))
{
continue; // shouldn't happen
}
string key = dr[0].ToString();
key = CompoundId.NormalizeForDatabase(key, mt.Root);
long rslt_grp_id = aDao.GetNextIdLong();
for (int ci = 1; ci < dataTable.Columns.Count; ci++) // do columns after key
{
if (dr.IsNull(ci))
{
continue;
}
DataColumn dc = dataTable.Columns[ci];
MetaColumn mc = mt.MetaColumns[ci];
int mcCode = Int32.Parse(mc.ResultCode);
avo = new AnnotationVo();
avo.rslt_grp_id = rslt_grp_id; // keep row together
if (dc.DataType == typeof(CompoundId)) // shouldn't happen since key processed already
{
avo.rslt_val_txt = dr[ci].ToString();
}
else if (dc.DataType == typeof(int) || dc.DataType == typeof(Int16) || dc.DataType == typeof(Int32) ||
dc.DataType == typeof(float) || dc.DataType == typeof(double))
{
avo.rslt_val_nbr = (double)dr[ci];
}
else if (dc.DataType == typeof(QualifiedNumber))
{
QualifiedNumber qn = (QualifiedNumber)dr[ci];
avo.rslt_val_nbr = qn.NumberValue;
avo.rslt_val_prfx_txt = qn.Qualifier;
avo.rslt_val_txt = qn.TextValue;
avo.dc_lnk = qn.Hyperlink;
}
else if (dc.DataType == typeof(string))
{
avo.rslt_val_txt = dr[ci].ToString();
}
else if (dc.DataType == typeof(DateTime))
{
avo.rslt_val_dt = (DateTime)dr[ci];
}
else if (dc.DataType == typeof(MoleculeMx))
{
avo.rslt_val_txt = dr[ci].ToString();
}
AddAnnotationVoToList(avo, key, mtCode, mcCode, voList);
}
writeCount++;
if (Progress.CancelRequested) // check for cancel
{
aDao.Commit();
aDao.Insert(voList);
voList.Clear();
aDao.Commit();
aDao.Dispose();
if (showProgress)
{
Progress.Hide();
}
MessageBoxMx.ShowError("Writing of annotation table cancelled.");
return(null);
}
int t2 = TimeOfDay.Milliseconds();
if (showProgress && t2 - t1 >= 1000)
{
t1 = t2;
Progress.Show("Writing data to annotation table " + writeCount.ToString() +
" of " + dataTable.Rows.Count.ToString() + " ...");
aDao.Insert(voList);
voList.Clear();
aDao.Commit();
}
}
aDao.Insert(voList);
voList.Clear();
aDao.Commit();
aDao.Dispose();
if (showProgress)
{
Progress.Hide();
}
return(mt); // return metatable name
}
/// <summary>
/// Get metatable if name belongs to broker
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public MetaTable GetMetaTable(
string name)
{
UserObject uo = null;
int objectId, i1;
string tok;
Stopwatch sw = Stopwatch.StartNew();
if (!UserObject.IsAnnotationTableName(name) &&
!UserObject.IsUserDatabaseStructureTableName(name))
{
return(null);
}
try { objectId = UserObject.ParseObjectIdFromInternalName(name); }
catch (Exception ex) { return(null); }
try { uo = UserObjectDao.Read(objectId); }
catch (MobiusConnectionOpenException ex)
{ DbConnectionMx.ThrowSpecificConnectionOpenException(ex, "mbs_owner"); }
if (uo == null)
{
return(null);
}
if (!Permissions.UserHasReadAccess(Security.UserName, uo))
{
return(null);
}
MetaTable mt = MetaTable.Deserialize(uo.Content);
if (mt == null)
{
return(null); // something wrong with the annotation table content
}
MetaColumn keyMc = mt.KeyMetaColumn;
MetaColumn rootMc = mt.Root.KeyMetaColumn;
if (rootMc.IsNumeric) // adjust key col mapping
{
keyMc.ColumnMap = "EXT_CMPND_ID_NBR";
}
else
{
keyMc.ColumnMap = "EXT_CMPND_ID_TXT";
}
while (mt.MetaColumns.Count > MetaTable.MaxColumns) // fixup for table with too many columns
{
mt.MetaColumns.RemoveAt(mt.MetaColumns.Count - 1);
}
foreach (MetaColumn mc in mt.MetaColumns)
{ // set DetailsAvailable for existing annotation tables */
if (mc.ResultCode != "")
{
mc.DetailsAvailable = true;
}
}
mt.Label = uo.Name; // get latest name (may have changed if renamed after saved)
if (MetaTableFactory.ShowDataSource) // add source to label if requested
{
mt.Label = MetaTableFactory.AddSourceToLabel(mt.Name, mt.Label);
}
long ms = sw.ElapsedMilliseconds;
return(mt);
}
/// <summary>
/// Right-click commands on objects in a tree
/// </summary>
/// <param name="command">The command to process</param>
/// <param name="mtn">MetaTreeNode</param>
/// <param name="uo">Any associated user object</param>
/// <param name="ctc">ContentsTreeControl</param>
/// <returns></returns>
public static bool ProcessCommonRightClickObjectMenuCommands(
string command,
MetaTreeNode mtn,
UserObject[] uoArray,
ContentsTreeControl ctc)
{
UserObject uo2;
string txt;
UserObject uo = null;
if (uoArray != null && uoArray.Length == 1)
{
uo = uoArray[0];
}
//if (mtn == null)
//{
// MessageBoxMx.ShowError("Operation is not allowed for this Database Contents node.");
// return true;
//}
if (Lex.Eq(command, "Cut"))
{
CopyCutDelete(command, uoArray, ctc, true, true);
}
else if (Lex.Eq(command, "Copy"))
{
CopyCutDelete(command, uoArray, ctc, true, false);
}
else if (Lex.Eq(command, "Paste"))
{
try
{
txt = Clipboard.GetText();
//uo2 = UserObject.Deserialize(txt);
uoArray = Deserialize(txt);
if (uoArray == null)
{
throw new Exception("Not a UserObject");
}
//if (uo2 == null) throw new Exception("Not a UserObject");
}
catch (Exception ex)
{
MessageBoxMx.ShowError("The clipboard does not contain a recognized user objects");
return(true);
}
foreach (var userObject in uoArray)
{
Progress.Show("Pasting " + userObject.Name + "...", UmlautMobius.String, false);
Permissions.UpdateAclForNewOwner(userObject, userObject.Owner, SS.I.UserName); // fixup the ACL for the new owner
userObject.Owner = SS.I.UserName;
userObject.ParentFolder = mtn.Name;
mtn = UserObjectTree.GetValidUserObjectTypeFolder(userObject);
for (int ci = 0; ; ci++) // find a name that's not used
{
UserObject uo3 = UserObjectDao.ReadHeader(userObject);
if (uo3 == null)
{
break;
}
if (ci == 0)
{
userObject.Name = "Copy of " + userObject.Name;
}
else if (ci == 1)
{
userObject.Name = Lex.Replace(userObject.Name, "Copy of ", "Copy (2) of ");
}
else
{
userObject.Name = Lex.Replace(userObject.Name, "Copy (" + ci + ") of ", "Copy (" + (ci + 1) + ") of ");
}
}
UserObject userObjectFinal = null;
if (UserObjectDao.ReadHeader(userObject.Id) != null) // create a deep clone if orignal object exists
{
userObjectFinal = DeepClone(userObject);
}
if (userObjectFinal == null)
{
userObjectFinal = userObject;
}
UserObjectDao.Write(userObjectFinal, userObjectFinal.Id); // write using the current id
Progress.Hide();
}
//if (ctc != null) // need to update form directly?
// UserObjectTree.RefreshSubtreeInTreeControl(uo2, ctc);
}
else if (Lex.Eq(command, "Delete"))
{
CopyCutDelete(command, uoArray, ctc, false, true);
}
else if (Lex.Eq(command, "Rename"))
{
if (!UserHasWriteAccess(uo))
{
MessageBoxMx.ShowError("You are not authorized to rename " + uo.Name);
return(true);
}
string newName = InputBoxMx.Show("Enter the new name for " + uo.Name,
"Rename", uo.Name);
if (newName == null || newName == "" || newName == uo.Name)
{
return(true);
}
if (!IsValidUserObjectName(newName))
{
MessageBoxMx.ShowError("The name " + newName + " is not valid.");
return(true);
}
uo2 = uo.Clone();
uo2.Name = newName;
uo2.Id = 0; // clear Id so not looked up by id
if (!Lex.Eq(newName, uo.Name) && UserObjectDao.ReadHeader(uo2) != null)
{
MessageBoxMx.ShowError(newName + " already exists.");
return(true);
}
uo2.Id = uo.Id;
UserObjectDao.UpdateHeader(uo2);
if (ctc != null)
{
UserObjectTree.UpdateObjectInTreeControl(uo, uo2, ctc);
}
}
else if (Lex.Eq(command, "MakePublic") ||
Lex.Eq(command, "MakePrivate"))
{
UserObjectAccess newAccess;
MetaTreeNode objFolder;
if (!UserHasWriteAccess(uo))
{
MessageBoxMx.ShowError("You are not authorized to make " + uo.Name +
((Lex.Eq(command, "MakePublic")) ? " public" : " private"));
return(true);
}
if (Lex.Eq(command, "MakePublic"))
{
if (uo.ParentFolder == "DEFAULT_FOLDER")
{
MessageBoxMx.ShowError("Items in the Default Folder cannot be made public");
return(true);
}
else
{
//check the folder id parentage to ensure that the current folder isn't a subfolder of the default folder
if (UserObjectTree.FolderNodes.ContainsKey(uo.ParentFolder))
{
objFolder = UserObjectTree.FolderNodes[uo.ParentFolder];
while (objFolder != null)
{
if (objFolder.Target == "DEFAULT_FOLDER")
{
MessageBoxMx.ShowError("Items in the Default Folder cannot be made public");
return(true);
}
objFolder = objFolder.Parent;
}
}
else
{
throw new Exception("Failed to recognize the folder that this object is in!");
}
}
newAccess = UserObjectAccess.Public;
}
else
{
//user folders cannot be made private if they contain public children
if (uo.Type == UserObjectType.Folder)
{
objFolder = UserObjectTree.BuildNode(uo);
if (UserObjectTree.FolderNodes.ContainsKey(objFolder.Target))
{
objFolder = UserObjectTree.FolderNodes[objFolder.Target];
for (int i = 0; i < objFolder.Nodes.Count; i++)
{
MetaTreeNode currentChild = (MetaTreeNode)objFolder.Nodes[i];
}
}
}
newAccess = UserObjectAccess.Private;
}
uo2 = UserObjectDao.Read(uo);
if (uo2 == null)
{
return(true);
}
if (uo2.AccessLevel == newAccess)
{
return(true); // no change
}
uo2.AccessLevel = newAccess;
UserObjectDao.UpdateHeader(uo2);
if (ctc != null) // need to update form directly?
{
UserObjectTree.RefreshSubtreeInTreeControl(uo2, ctc);
}
return(true);
}
else if (Lex.Eq(command, "ChangeOwner"))
{
string newOwner = InputBoxMx.Show("Enter the user name of the person to transfer ownership of " + Lex.AddDoubleQuotes(uo.Name) + " to:",
"Change Owner", "");
if (Lex.IsNullOrEmpty(newOwner))
{
return(true);
}
string result = UserObjectUtil.ChangeOwner(uo.Id, newOwner);
if (!Lex.IsNullOrEmpty(result))
{
MessageBoxMx.Show(result);
}
return(true);
}
else if (Lex.Eq(command, "Permissions")) // set object permissions
{
PermissionsDialog.Show(uo);
return(true);
}
else if (Lex.Eq(command, "ViewSource"))
{
uo = UserObjectDao.Read(uo);
if (uo == null)
{
return(true);
}
string ext = ".txt"; // default extension
if (uo.Type == UserObjectType.Query ||
uo.Type == UserObjectType.Annotation)
{
ext = ".xml";
}
string cFile = ClientDirs.TempDir + @"\Source" + ext;
StreamWriter sw = new StreamWriter(cFile);
sw.Write(uo.Content);
sw.Close();
SystemUtil.StartProcess(cFile); // show it
}
else
{
return(false); // command not recognized
}
return(true); // command recognized and processed
}
/// <summary>
/// Do some combination of copy/cut/delete on a single userObject
/// </summary>
/// <param name="command"></param>
/// <param name="uoArray"></param>
/// <param name="ctc"></param>
/// <param name="copy"></param>
/// <param name="delete"></param>
//private static void CopyCutDelete(
// string command,
// UserObject uo,
// ContentsTreeControl ctc,
// bool copy,
// bool delete)
//{
// UserObject[] uoArray = new UserObject[1] { uo };
// CopyCutDelete(command, uoArray, ctc, copy, delete);
//}
/// <summary>
/// Do some combination of copy/cut/delete on an array of Userobjects
/// </summary>
/// <param name="command"></param>
/// <param name="uoArray"></param>
/// <param name="ctc"></param>
/// <param name="copy"></param>
/// <param name="delete"></param>
static void CopyCutDelete(
string command,
UserObject[] uoArray,
ContentsTreeControl ctc,
bool copy,
bool delete)
{
// Copy to clipboard
if (copy)
{
if (!UserHasReadAccess(SS.I.UserName, uoArray))
{
MessageBoxMx.ShowError("You are not authorized to copy these nodes. Copy canceled.");
return;
}
if (!UserObject.CanCopyPaste(uoArray))
{
MessageBoxMx.ShowError("Can't copy/cut user objects of these types. Copy/cut canceled.");
return;
}
List <UserObject> userObjectArray = new List <UserObject>();
foreach (UserObject userObject in uoArray)
{
UserObject uo = UserObjectDao.Read(userObject.Id);
userObjectArray.Add(uo);
}
string serializedUos = Serialize(userObjectArray.ToArray());
if (serializedUos != null)
{
Clipboard.SetText(serializedUos);
}
}
// Delete object
if (delete)
{
if (!UserHasWriteAccess(SS.I.UserName, uoArray))
{
MessageBoxMx.ShowError("You are not authorized to cut/delete these nodes.");
return;
}
//for now, don't allow folders to be deleted if they aren't empty
foreach (var uo in uoArray)
{
if (uo.Type == UserObjectType.Folder)
{
MetaTreeNode folderNode = UserObjectTree.BuildNode(uo);
if (UserObjectTree.FolderNodes.ContainsKey(folderNode.Target.ToUpper()))
{
folderNode = UserObjectTree.FolderNodes[folderNode.Target.ToUpper()];
if (folderNode.Nodes.Count > 0)
{
MessageBoxMx.ShowError("Folder \"" + folderNode.Label + "\" is not empty and cannot be deleted.");
return;
}
}
}
}
if (!copy)
{
string caption = "Confirm " + UserObject.GetTypeLabel(uoArray[0].Type) + " Delete";
string txt = "Are you sure you want to delete " + Lex.Dq(uoArray[0].Name);
if (uoArray.Length > 1)
{
txt = "Are you sure you want to delete these " + uoArray.Length + " items?";
}
DialogResult dr = MessageBoxMx.Show(txt, caption, MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dr != DialogResult.Yes)
{
return;
}
foreach (var uo in uoArray)
{
UserObject userObject = null;
userObject = UserData.IsUserDataObject(uo) ? UserObjectDao.Read(uo.Id) : uo;
bool succeeded = userObject != null && UserObjectDao.Delete(userObject.Id);
// remove userobject from database
if (UserData.IsUserDataObject(userObject)) // start removing any associated user data
{
UserData.StartBackgroundDataDelete(userObject);
}
}
//if (UserData.IsUserDataObject(uo)) uo = UserObjectDao.Read(uo.Id); // will need content to del assoc user data
//bool succeeded = UserObjectDao.Delete(uo.Id); // remove userobject from database
//if (UserData.IsUserDataObject(uo)) // start removing any associated user data
// UserData.StartBackgroundDataDelete(uo);
}
else
{
foreach (var uo in uoArray)
{
UserObjectDao.Delete(uo.Id); // just remove userobject from database, will leave orphan data for annotation tables but need for paste
}
}
if (ctc != null) // redraw the subtree without the deleted user object
{
UserObjectTree.RefreshSubtreeInTreeControl(uoArray[0], ctc);
}
}
}
/// <summary>
/// Synthesize metatable associated with a multitable
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public MetaTable GetMetaTable(
string name)
{
UserObject uo;
QueryTable qt;
QueryColumn qc;
MetaTable mt = null;
MetaColumn mc;
bool [] hasCol;
int objectId, qti, qci;
string prefix = "multitable_"; // multitable metatable names begin with "multitable_"
if (name.ToLower().IndexOf(prefix) != 0)
{
return(null);
}
string tok = name.Substring(prefix.Length); // get the id of the associated query
try { objectId = Int32.Parse(tok); }
catch (Exception ex) { return(null); }
uo = UserObjectDao.Read(objectId); // read query
if (uo == null)
{
return(null);
}
if (RecursionDepth > 3)
{
return(null); // avoid recursion loop
}
RecursionDepth++;
Query q = Query.Deserialize(uo.Content);
q.UserObject = uo; // set associated user object
qt = null; // set of common fields built here
hasCol = null;
bool summarizedExists = true; // say summarized exists unless not seen on at least one table
foreach (QueryTable qt2 in q.Tables)
{
if (qt2.MetaTable.Parent == null && q.Tables.Count > 1)
{
continue; // don't include root unless only table
}
if (!qt2.MetaTable.SummarizedExists)
{
summarizedExists = false;
}
if (qt == null) // duplicate first non-root table as model
{
qt = qt2.Clone();
hasCol = new bool[qt.QueryColumns.Count];
continue;
}
hasCol.Initialize();
foreach (QueryColumn qc2 in qt2.QueryColumns)
{
qci = qt.GetQueryColumnIndexByName(qc2.MetaColumn.Name);
if (qci < 0)
{
continue;
}
hasCol[qci] = true;
}
qci = 0; // remove columns not in table other than key
while (qci < qt.QueryColumns.Count)
{
qc = qt.QueryColumns[qci];
if (hasCol[qci] || qc.IsKey)
{
qci++;
}
else
{
qt.QueryColumns.RemoveAt(qci);
}
}
}
RecursionDepth--;
if (qt == null)
{
return(null); // no tables or only root table
}
// Build metatable with common columns
mt = new MetaTable();
mt.Name = name.ToUpper();
mt.Label = q.UserObject.Name; // label same as query name
mt.MetaBrokerType = MetaBrokerType.MultiTable;
mt.Parent = qt.MetaTable.Parent;
mt.SummarizedExists = summarizedExists;
mt.MultiPivot = true; // cause pivot at runtime
for (qci = 0; qci < qt.QueryColumns.Count; qci++)
{
qc = qt.QueryColumns[qci];
if (qc.MetaColumn == null)
{
continue;
}
mc = qc.MetaColumn.Clone();
mc.MetaTable = mt;
mt.MetaColumns.Add(mc);
if (qci == 0 && Math.Sqrt(4) == 2) // add column listing included tables after key (disabled)
{
string tables = "";
int tableCount = 0;
foreach (QueryTable qt2 in q.Tables)
{
if (qt2.MetaTable.Parent == null && q.Tables.Count > 1)
{
continue; // don't include root unless only table
}
if (tables.Length > 0)
{
tables += ", ";
}
tables += qt2.ActiveLabel;
tableCount++;
}
mc = mt.AddMetaColumn("DataTables", "Data Tables(" + tableCount + "): " + tables, MetaColumnType.String, ColumnSelectionEnum.Selected, 10);
mc.IsSearchable = false;
break; // include just key & table list cols
}
}
return(mt);
}
/// <summary>
/// Build metatable for a Spotfire analysis link
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public MetaTable GetMetaTable(
string name)
{
UserObject uo;
SpotfireViewProps sl;
MetaTable mt, mt2;
MetaColumn mc;
int objectId;
string prefix = "spotfirelink_";
if (name.ToLower().IndexOf(prefix) != 0)
{
return(null);
}
string tok = name.Substring(prefix.Length); // get the object id
if (!int.TryParse(tok, out objectId))
{
return(null);
}
uo = UserObjectDao.Read(objectId);
if (uo == null)
{
return(null);
}
if (!Permissions.UserHasReadAccess(Security.UserName, uo))
{
return(null);
}
try
{
sl = SpotfireViewProps.Deserialize(uo.Content);
if (sl == null)
{
throw new Exception("Null Deserialize result for object " + uo.Id);
}
}
catch (Exception ex)
{
DebugLog.Message(ex);
return(null);
}
MetaTable root = MetaTableCollection.GetWithException(MetaTable.PrimaryRootTable);
mt = MetaTableCollection.GetWithException("SPOTFIRELINK_MODEL");
mt = mt.Clone(); // build metatable here
mt.Name = "SPOTFIRELINK_" + uo.Id;
mt.Code = uo.Content; // store serialized SpotfireLink content in Code field
mt.Parent = root;
mt.MetaBrokerType = MetaBrokerType.SpotfireLink;
mt.TableMap = root.TableMap; // allow searches on CorpId
mt.Label = uo.Name;
foreach (MetaColumn mc0 in mt.MetaColumns)
{
mc0.InitialSelection = ColumnSelectionEnum.Hidden;
}
foreach (SpotfireLinkParameter sp in sl.SpotfireLinkParameters.Values)
{
mc = mt.GetMetaColumnByName(sp.Name);
if (mc == null)
{
continue;
}
bool b = false;
bool.TryParse(sp.Value, out b);
mc.InitialSelection = b ? ColumnSelectionEnum.Selected : ColumnSelectionEnum.Hidden;
}
if (mt.KeyMetaColumn.InitialSelection != ColumnSelectionEnum.Selected)
{
mt.KeyMetaColumn.InitialSelection = ColumnSelectionEnum.Unselected;
}
mc = mt.GetMetaColumnByName("VISUALIZATION");
if (mc != null)
{
mc.InitialSelection = ColumnSelectionEnum.Selected;
}
SetAllInQueryInitialCriteria(mt, "invitro_assays", "invitro_assays_default_to_all", "in (ALL_ASSAY_INVITRO_ASSAYS_IN_THIS_QUERY)");
SetAllInQueryInitialCriteria(mt, "insilico_models", "insilico_models_default_to_all", "in (ALL_SPM_INSILICO_MODELS_IN_THIS_QUERY)");
return(mt);
}
/// <summary>
/// Retrieve the results of a background export
/// Example: Retrieve Background Export 231243
/// </summary>
/// <param name="objectIdString">Id of BackgroundExport UserObject containing serialized ResultsFormat</param>
/// <returns></returns>
public static string RetrieveBackgroundExport(
string objectIdString)
{
ResultsFormat rf;
Query q;
int objId;
QbUtil.SetMode(QueryMode.Build); // be sure in build mode
if (!int.TryParse(objectIdString, out objId))
{
return("Invalid UserObjectId: " + objectIdString);
}
UserObject uo = UserObjectDao.Read(objId);
if (uo == null)
{
return("RunInBackground UserObject not found: " + objectIdString);
}
if (uo.Type != UserObjectType.BackgroundExport)
{
return("Object is not the expected RunInBackground UserObject type");
}
rf = ResultsFormat.Deserialize(uo.Content);
if (rf == null)
{
throw new Exception("Failed to deserialize ResultsFormat: " + objectIdString);
}
string clientFile = rf.OutputFileName;
string serverFile = GetServerFileName(rf, objId);
string ext = Path.GetExtension(clientFile);
string filter = "*" + ext + "|*" + ext;
if (SharePointUtil.IsRegularFileSystemName(clientFile))
{
clientFile = UIMisc.GetSaveAsFilename("Retrieve Background Export File", clientFile, filter, ext);
}
else
{
clientFile =
SharePointFileDialog.GetSaveAsFilename("Retrieve Background Export File", clientFile, filter, ext);
}
if (String.IsNullOrEmpty(clientFile))
{
return("");
}
Progress.Show("Retrieving file...");
try { ServerFile.CopyToClient(serverFile, clientFile); }
catch (Exception ex)
{
string msg =
"Unable to retrieve cached export file: " + serverFile + "\n" +
"to client file: " + clientFile + "\n" +
DebugLog.FormatExceptionMessage(ex);
ServicesLog.Message(msg);
Progress.Hide();
return(msg);
}
Progress.Hide();
if (Lex.Eq(ext, ".xls") || Lex.Eq(ext, ".xlsx") || Lex.Eq(ext, ".doc") || Lex.Eq(ext, ".docx"))
{
DialogResult dr = MessageBoxMx.Show("Do you want to open " + clientFile + "?",
UmlautMobius.String, MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dr == DialogResult.Yes)
{
SystemUtil.StartProcess(clientFile);
}
}
return("Background export file retrieved: " + clientFile);
}
/// <summary>
/// Open a spotfire link or webplayer url
/// Example: https://[server]/SpotfireWeb/ViewAnalysis.aspx?file=/Mobius/Visualizations/MdbAssay_MOBIUS&configurationBlock=CorpId_LIST={1,3,5};
/// Example: https://[server]/SpotfireWeb/ViewAnalysis.aspx?file=/Mobius/Visualizations/SELECT_MOBIUS_DATA_Test&configurationBlock=SQLPARMS="1,3,5,7,9";
/// </summary>
/// <param name="link">Open a spotfire link or Webplayer URL </param>
/// <param name="browserControl"></param>
public static void OpenLink(
string link,
string title,
WebBrowser browserControl)
{
SpotfireViewProps sl = null;
UserObject uo = null;
UserObjectType uoType;
string url;
PopupHtml form;
int uoId;
if (Lex.IsUndefined(title))
{
title = link;
}
if (Lex.IsUndefined(link)) // prompt for the link to open
{
string prompt = "Open Spotfire Link ";
uo = UserObjectOpenDialog.ShowDialog(UserObjectType.SpotfireLink, prompt);
if (uo == null)
{
return;
}
link = uo.InternalName;
}
if (Lex.StartsWith(link, "SPOTFIRELINK_")) // get the link user object
{
string internalName = link;
bool parseOk = UserObject.ParseObjectTypeAndIdFromInternalName(link, out uoType, out uoId);
uo = UserObjectDao.Read(uoId);
if (uo == null)
{
throw new Exception("User object not found " + internalName);
}
sl = SpotfireViewProps.Deserialize(uo.Content);
title = uo.Name;
url = BuildUrl(sl);
if (Lex.IsUndefined(url))
{
return; // cancelled
}
}
else
{
url = link; // assume link is a complete url
}
if (browserControl == null) // Open the link within a WebBrowser control
{
form = new PopupHtml();
UIMisc.PositionPopupForm(form);
form.Text = title;
form.Show();
browserControl = form.WebBrowser;
}
browserControl.Navigate(url);
return;
}
/// <summary>
/// This method runs in a background process and exports data to the specified destination
/// </summary>
/// <param name="objectIdString">Id of UserObject containing run parameters in a serialized ResultsFormat</param>
/// <param name="templateFileName">Name of template file to use</param>
/// <param name="emailResultsHtml">If true then send email otherwise just return html</param>
/// <returns></returns>
public static string RunBackgroundExport(
string objectIdString,
string templateFileName,
bool emailResultsHtml,
out bool copiedToDestinationFile,
int alertId = 0)
{
ResultsFormat rf;
Query q;
string msg = "";
int objId;
//if (ClientState.IsDeveloper)
//{
// ServicesLog.Message(SS.I.UserName + ": BackgrounExport Debug");
// //DataTableManager.AllowCaching = false;
// DataTableManager.DebugBasics = true;
// DataTableManager.DebugCaching = true;
//}
if (String.IsNullOrEmpty(templateFileName))
{
templateFileName = "MobiusBackgroundExportEmailTemplate.htm";
}
ServicesLog.Message("RunBackgroundExport started: UserObject id = " + objectIdString);
string emailSubject = UmlautMobius.String + " background export results";
copiedToDestinationFile = false;
try
{
if (!int.TryParse(objectIdString, out objId))
{
throw new Exception("Invalid UserObjectId");
}
UserObject uo = UserObjectDao.Read(objId);
if (uo == null)
{
throw new Exception("UserObject not found");
}
QueryManager qm = new QueryManager();
rf = ResultsFormat.Deserialize(uo.Content);
if (rf == null)
{
throw new Exception("Failed to deserialize ResultsFormat");
}
string clientFile = rf.OutputFileName; // ultimate file we want to go to
rf.OutputFileName = GetServerFileName(rf, objId); // get file name to export to on server & use here temporarily
qm.ResultsFormat = rf;
rf.QueryManager = qm;
q = QbUtil.ReadQuery(rf.QueryId);
if (q == null)
{
throw new Exception("Failed to read query: " + rf.QueryId);
}
q.IncludeRootTableAsNeeded();
qm.Query = q;
q.QueryManager = qm;
emailSubject += " for query " + Lex.Dq(q.UserObject.Name); // include query name in subject
ResultsFormatFactory rff = new ResultsFormatFactory(rf);
rff.Build();
ResultsFormatter fmtr = new ResultsFormatter(qm);
DataTableManager dtm = new DataTableManager(qm);
dtm.BeginCaching(); // allow caching of DataTable
dtm.PurgeDataTableWithoutWritingToCacheFile = true; // skip actual writing of cache since it won't be read back in
qm.DataTableManager = dtm;
qm.DataTable = DataTableManager.BuildDataTable(rf.Query); // build data table to receive data
QueryExec qex = new QueryExec(rf);
msg = qex.RunQuery3(rf, false, false); // do the export
int compoundCount = 0;
int rowCount = 0;
QueryEngine qe = qex.QueryEngine;
if (qe != null)
{
compoundCount = qm.DataTableManager.KeyCount;
rowCount = qm.DataTableManager.TotalRowsTransferredToDataTable; // use this for accurate row count
}
dtm.EndCaching(); // close cache file (note: resets key/row counts)
if (compoundCount <= 0 || rowCount <= 0) // any results
{
msg = "Query " + Lex.Dq(q.UserObject.Name) + " returned no results.";
Email.Send(
null, SS.I.UserInfo.EmailAddress, emailSubject, msg);
return(msg);
}
if (ServerFile.CanWriteFileFromServiceAccount(clientFile))
{ // copy to dest file if possible
try
{
FileUtil.CopyFile(rf.OutputFileName, clientFile);
copiedToDestinationFile = true;
rf.OutputFileName = clientFile;
}
catch (Exception ex)
{
ServicesLog.Message("Error copying file from service account: " + clientFile + "\n" + DebugLog.FormatExceptionMessage(ex));
}
}
string viewCmd = "Retrieve Background Export " + uo.Id;
msg = "RunBackgroundExport ended: UserObjectId = " + objectIdString;
if (emailResultsHtml)
{
MailBackgroundExportResults(
q,
clientFile,
rowCount,
compoundCount,
copiedToDestinationFile,
viewCmd,
SS.I.UserInfo.EmailAddress,
emailSubject,
templateFileName);
ServicesLog.Message(msg);
return(msg);
}
else // just fill in values & return
{
string html = ReadTemplateFile(templateFileName);
html = SubstituteBackgroundExportParameters(
html, "", rf.OutputFileName, rowCount, compoundCount, copiedToDestinationFile, viewCmd);
ServicesLog.Message(msg);
return(html);
}
}
catch (Exception ex)
{
if (alertId > 0)
{
msg += "Alert: " + alertId + " ";
}
msg +=
"RunBackgroundExport exception: BackgroundExportId = " + objectIdString + ",\r\n" +
DebugLog.FormatExceptionMessage(ex);
Email.Send(
null, SS.I.UserInfo.EmailAddress, emailSubject, msg);
ServicesLog.Message(msg);
return(msg);
}
}
private MetaTable GetMetaTableLevel2(
string name)
{
UserObject uo;
Query incQry = null; // included query
QueryTable qt, qt1, qt2;
QueryColumn qc, qc1, qc2;
CalcFieldMetaTable mt;
MetaTable mt1, mt2;
MetaColumn mc, mc1, mc2, cfMc, cfMcModel;
ExecuteQueryParms eqp;
string cfLabel = "", mcName, mcLabel;
string sql = null, tables = null, exprs = null, criteria = null;
int objectId, startingSuffix, ci, i1;
string prefix = "calcfield_"; // calculated field metatable names begin with "calcfield_"
if (name.ToLower().IndexOf(prefix) != 0)
{
return(null);
}
if (Lex.Eq(name, "CALCFIELD_826476"))
{
name = name; // debug
}
string tok = name.Substring(prefix.Length); // get the object id
try { objectId = Int32.Parse(tok); }
catch (Exception ex) { return(null); }
uo = UserObjectDao.Read(objectId);
if (uo == null)
{
return(null);
}
if (!Permissions.UserHasReadAccess(Security.UserName, uo))
{
return(null);
}
CalcField cf = CalcField.Deserialize(uo.Content);
if (cf == null)
{
return(null);
}
if (cf.FinalResultType == MetaColumnType.Unknown)
{
return(null); // need a final result type
}
mt = new CalcFieldMetaTable();
mt.Name = name;
mt.CalcField = cf; // include associated CalcField object
mt.Label = uo.Name;
if (MetaTableFactory.ShowDataSource) // add source to label if requested
{
mt.Label = MetaTableFactory.AddSourceToLabel(mt.Name, mt.Label);
}
mt.Description = cf.Description;
mt.MetaBrokerType = MetaBrokerType.CalcField;
bool keyAdded = false;
cfLabel = cf.ColumnLabel; // get any user defined label for result
// Add metacolumns for basic calculated field
if (cf.CalcType == CalcTypeEnum.Basic && cf.Column1.MetaColumn != null)
{
cfMcModel = cf.Column1.MetaColumn;
tok = cf.Operation;
if (Lex.IsNullOrEmpty(cfLabel))
{
if (tok.IndexOf("/") == 0)
{
cfLabel = "Ratio";
}
else if (tok.IndexOf("*") == 0)
{
cfLabel = "Product";
}
else if (tok.IndexOf("+") == 0)
{
cfLabel = "Sum";
}
else if (tok.IndexOf("-") == 0)
{
cfLabel = "Difference";
}
else
{
if (cf.Column1.FunctionEnum == CalcFuncEnum.None) // just use existing col name
{
cfLabel = cf.Column1.MetaColumn.Label;
}
else // use function & col name
{
tok = cf.Column1.Function;
i1 = tok.IndexOf("(");
if (i1 >= 0)
{
tok = tok.Substring(0, i1);
}
if (!tok.EndsWith(" "))
{
tok += " ";
}
tok += cf.MetaColumn1.Label;
cfLabel = tok;
}
}
if (cf.IsClassificationDefined)
{
cfLabel += " Class"; // add class suffix if classifier
}
}
}
else if (cf.CalcType == CalcTypeEnum.Advanced && cf.AdvancedExpr != "")
{
List <MetaColumn> mcList = cf.GetInputMetaColumnList();
if (mcList.Count == 0)
{
return(null);
}
cfMcModel = mcList[0];
if (cfLabel == "")
{
cfLabel = "Value";
}
}
else
{
return(null);
}
mt.Parent = cfMcModel.MetaTable.Parent; // set parent
if (mt.Root == mt)
{
mt.Parent = cfMcModel.MetaTable; // if F1 table is a root set it as parent
}
// Add key value
mc = cfMcModel.MetaTable.KeyMetaColumn; // make copy of first table key field for our key
if (mc == null)
{
return(null);
}
mc = mc.Clone();
mc.ColumnMap = mc.Name; // map to self
mc.MetaTable = mt;
mt.AddMetaColumn(mc);
keyAdded = true;
mc.IsSearchable = cf.IsSearchable;
// Add calculated value column
mc = new MetaColumn();
mt.AddMetaColumn(mc);
mc.MetaTable = mt;
mc.Name = "CALC_FIELD";
mc.Label = cfLabel;
mc.DataType = cf.FinalResultType; // final result type
mc.InitialSelection = ColumnSelectionEnum.Selected;
mc.Format = cfMcModel.Format;
mc.Width = cfMcModel.Width;
mc.Decimals = cfMcModel.Decimals;
if (cf.FinalResultType == MetaColumnType.Integer)
{
mc.Format = ColumnFormatEnum.Decimal;
mc.Decimals = 0;
}
if (cf.FinalResultType == MetaColumnType.Image)
{
mc.Width = (int)(mc.Width * 2.0); // make CF images wider
}
mc.IsSearchable = cfMcModel.IsSearchable;
if (mc.IsSearchable) // refine searchability
{
mc.IsSearchable = cf.IsSearchable;
}
cfMc = mc;
// Add metacolumns for the underlying columns that go into the calculation
foreach (CalcFieldColumn cfc in cf.CfCols)
{
if (cfc.MetaColumn == null)
{
continue;
}
if (cfc.MetaColumn.IsKey)
{
continue; // don't add additional key
}
mc = cfc.MetaColumn.Clone();
mcName = mc.MetaTable.Name + "_" + mc.Name;
mc.Name = mt.GetValidMetaColumnName(mcName);
mc.DetailsAvailable = false; // no drill-down for now (can cause issues)
mc.IsSearchable = false; // no searching for now (can cause issues)
mc.ColumnMap = mc.Name; // map to self
if (Lex.Eq(mc.Label, cfLabel)) // if same as result start suffix checking at 2
{
startingSuffix = 1;
}
else
{
startingSuffix = 2;
}
mc.Label = mt.GetUniqueMetaColumnLabel(mc.Label, startingSuffix);
mc.InitialSelection = ColumnSelectionEnum.Hidden; // hide for now
mc.TableMap = cfc.MetaColumn.MetaTable.Name + "." + cfc.MetaColumn.Name; // save source table and column
//mc.TableMap = cfc.MetaColumn.MetaTable.Name; // save source table and column
//mc.ColumnMap = cfc.MetaColumn.Name;
mt.AddMetaColumn(mc);
}
//mt.MetaColumns.Remove(cfMc); // move cf result column to end after the inputs
//mt.MetaColumns.Add(cfMc);
return(mt);
}
/// <summary>
/// Read the original query associated with this multitable metatable
/// and substitute in the query along with any criteria, selections
/// for the common fields.
/// </summary>
/// <param name="qt"></param>
/// <param name="q"></param>
/// <param name="resultKeys"></param>
public override void DoPreSearchTransformation(
Query originalQuery,
QueryTable qt,
Query newQuery)
{
MetaTable mt2;
UserObject uo;
int objectId;
string name = qt.MetaTable.Name;
if (Lex.Eq(name, MetaTable.AllDataQueryTable)) // special case transform for QuickSearch all data query
{
QueryEngine.TransformSelectAllDataQuery(originalQuery, qt, newQuery);
return;
}
string prefix = "multitable_"; // multitable metatable names begin with "multitable_"
if (name.ToLower().IndexOf(prefix) != 0)
{
return;
}
string tok = name.Substring(prefix.Length); // get the id of the associated query
try { objectId = Int32.Parse(tok); }
catch (Exception ex) { return; }
uo = UserObjectDao.Read(objectId); // read query
if (uo == null)
{
return;
}
Query q2 = Query.Deserialize(uo.Content);
foreach (QueryTable qt2 in q2.Tables)
{
mt2 = qt2.MetaTable;
if (mt2.Parent == null)
{
continue; // ignore root table
}
QueryTable qt3 = qt2.Clone(); // make copy to modify & add to query
qt3.Alias = ""; // clear alias to avoid possible conflicts with existing aliases
if (qt.HeaderBackgroundColor != Color.Empty)
{
qt2.HeaderBackgroundColor = qt.HeaderBackgroundColor;
}
MetaColumn keyMc = qt3.MetaTable.KeyMetaColumn; // clear any key criteria
if (keyMc != null)
{
QueryColumn qc3 = qt3.GetQueryColumnByName(keyMc.Name);
qc3.Criteria = qc3.CriteriaDisplay = "";
}
foreach (QueryColumn qc in qt.QueryColumns)
{ // pass any criteria, selections from multitable to underlying tables
int qci3 = qt3.GetQueryColumnIndexByName(qc.MetaColumn.Name);
if (qci3 < 0)
{
continue; // ignore if doesn't match by name
}
QueryColumn qc3 = qt3.QueryColumns[qci3]; // cloned column
if (qc.Criteria != "")
{ // if criteria then clone model query col for simpler copying of criteria
QueryColumn qc4 = qc.Clone();
qc4.MetaColumn = qc3.MetaColumn;
qc4.QueryTable = qt3;
qt3.QueryColumns[qci3] = qc4;
qc3 = qc4;
}
qc3.Selected = qc.Selected;
continue;
}
newQuery.AddQueryTable(qt3);
}
return;
}