/// <summary>
/// Save list of hits and optional reference to a results file from a background query
/// </summary>
/// <param name="qm"></param>
/// <param name="listOwner"></param>
/// <returns></returns>
public static UserObject SaveBackgroundQueryResultsReferenceObject(
QueryManager qm,
string listOwner,
string resultsFileName)
{
Query q = qm.Query;
DataTableManager dtm = qm.DataTableManager;
UserObject cidListUo = new UserObject(UserObjectType.CnList);
cidListUo.Owner = listOwner;
cidListUo.Id = UserObjectDao.GetNextId();
cidListUo.Name = "List " + cidListUo.Id; // assign unique name
cidListUo.Description = q.UserObject.Id + "\t" + // store query id
DateTimeUS.ToString(DateTime.Now); // and a time stamp
if (!Lex.IsNullOrEmpty(resultsFileName))
{
cidListUo.Description += "\t" + resultsFileName;
}
SortOrder sortDirection = (q.KeySortOrder > 0) ? SortOrder.Ascending : SortOrder.Descending;
ResultsSorter.SortKeySet(dtm.ResultsKeys, sortDirection); // sort properly
StringBuilder sb = new StringBuilder();
foreach (string s in dtm.ResultsKeys)
{ // build comma separated list of numbers
if (sb.Length > 0)
{
sb.Append("\r\n");
}
sb.Append(s);
}
cidListUo.Content = sb.ToString();
cidListUo.Count = dtm.ResultsKeys.Count;
UserObjectDao.Write(cidListUo, cidListUo.Id); // write list with supplied id
return(cidListUo);
}
/// <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>
/// Perform a deep clone of an annotation table UserObject including the underlying data
/// </summary>
/// <param name="uo"></param>
/// <returns></returns>
public static UserObject DeepClone(UserObject uo)
{
UserObject uo2 = uo.Clone();
long oldCode, newCode;
// Create a copy of the user object including remapping of the codes
AnnotationDao dao = new AnnotationDao();
MetaTable mt = MetaTable.Deserialize(uo.Content); // deserialize metatable xml
int newMethodCode = UserObjectDao.GetNextId();
uo2.Id = newMethodCode; // store in UserObject id as well
mt.Code = newMethodCode.ToString();
mt.Name = "ANNOTATION_" + mt.Code;
mt.Label = uo.Name;
Dictionary <long, long> codeMap = new Dictionary <long, long>();
foreach (MetaColumn mc in mt.MetaColumns)
{
if (!Lex.IsNullOrEmpty(mc.ResultCode))
{
if (!long.TryParse(mc.ResultCode, out oldCode))
{
continue;
}
newCode = dao.GetNextIdLong();
codeMap[oldCode] = newCode;
mc.Name = "R_" + newCode;
mc.ResultCode = newCode.ToString();
}
}
// Write import state if checking for updates
if (mt.ImportParms != null && mt.ImportParms.CheckForFileUpdates)
{
UserObject udisUo = new UserObject(UserObjectType.ImportState, uo2.Owner, mt.Name);
UserDataImportState udis = new UserDataImportState();
udis.UserDatabase = false; // indicate annotation table
udis.UserDataObjectId = uo2.Id; // store id of annotation table user object
udis.ClientFile = mt.ImportParms.FileName;
udis.CheckForFileUpdates = mt.ImportParms.CheckForFileUpdates;
udis.ClientFileModified = mt.ImportParms.ClientFileModified;
udis.FileName = mt.ImportParms.FileName;
udisUo.Description = udis.Serialize(); // serialize to description
UserObjectDao.Write(udisUo);
}
// Copy the data
dao.BeginTransaction();
dao.BufferInserts(true);
dao.OpenReader(uo.Id);
Dictionary <long, long> groupMap = new Dictionary <long, long>();
int readCount = 0;
while (true)
{
AnnotationVo vo = dao.Read();
if (vo == null)
{
break;
}
if (!codeMap.ContainsKey(vo.rslt_typ_id))
{
continue;
}
vo.rslt_typ_id = codeMap[vo.rslt_typ_id]; // map the result code
vo.rslt_id = dao.GetNextIdLong(); // new result id
vo.mthd_vrsn_id = newMethodCode;
if (!groupMap.ContainsKey(vo.rslt_grp_id)) // map the group id
{
groupMap[vo.rslt_grp_id] = dao.GetNextIdLong();
}
vo.rslt_grp_id = groupMap[vo.rslt_grp_id];
dao.Insert(vo);
readCount++;
if (readCount % 1000 == 0)
{
dao.ExecuteBufferedInserts();
dao.Commit();
}
}
dao.ExecuteBufferedInserts();
dao.Commit();
dao.Dispose();
uo2.Count = groupMap.Count; // update the count
uo2.CreationDateTime = uo2.UpdateDateTime = DateTime.Now;
uo2.Content = mt.Serialize();
return(uo2);
}