/// <summary>
/// Debug check for disallowed duplicate insert for a given method, cid, result group and result type
/// </summary>
/// <param name="voList"></param>
/// <returns></returns>
bool CheckForDuplicateInsert(List <AnnotationVo> voList)
{
AnnotationVo vo = voList[0];
string sql0 = // see if this result value already exists
"SELECT * " +
" FROM mbs_owner.MBS_ADW_RSLT " +
" WHERE mthd_vrsn_id = " + vo.mthd_vrsn_id +
" and ext_cmpnd_id_nbr = " + vo.ext_cmpnd_id_nbr +
" and rslt_grp_id = " + vo.rslt_grp_id +
" and rslt_typ_id = " + vo.rslt_typ_id +
" and sts_id = 1";
DbCommandMx d0 = new DbCommandMx();
d0.Prepare(sql0);
d0.ExecuteReader();
bool exists = d0.Read();
d0.Dispose();
if (exists)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Select row for specified result id
/// </summary>
/// <param name="rsltId"></param>
/// <returns></returns>
public AnnotationVo Select(
long rsltId)
{
if (ServiceFacade.UseRemoteServices)
{
Mobius.Services.Native.INativeSession nativeClient = ServiceFacade.CreateNativeSessionProxy();
Services.Native.NativeMethodTransportObject resultObject =
ServiceFacade.InvokeNativeMethod(nativeClient,
(int)Services.Native.ServiceCodes.MobiusAnnotationService,
(int)Services.Native.ServiceOpCodes.MobiusAnnotationService.SelectByResultId,
new Services.Native.NativeMethodTransportObject(new object[] { instanceId, rsltId }));
((System.ServiceModel.IClientChannel)nativeClient).Close();
if (resultObject == null)
{
return(null);
}
ServiceTypes.AnnotationVo serviceAnnotationVo =
(ServiceTypes.AnnotationVo)resultObject.Value;
AnnotationVo annotationVo =
ServiceFacade.TypeConversionHelper.Convert <ServiceTypes.AnnotationVo, AnnotationVo>(serviceAnnotationVo);
return(annotationVo);
}
else
{
return(Instance.Select(rsltId));
}
}
/// <summary>
/// Select all rows for a method and compoundId
/// </summary>
/// <param name="mthdVrsnId"></param>
/// <param name="cmpndId"></param>
/// <returns></returns>
public List <AnnotationVo> Select(
int mthdVrsnId,
string cmpndId)
{
OpenReader(mthdVrsnId, cmpndId);
List <AnnotationVo> rows = new List <AnnotationVo>();
int readCount = 0;
while (true)
{
AnnotationVo vo = Read();
if (vo == null)
{
break;
}
rows.Add(vo);
readCount++;
}
Close();
if (readCount == 0)
{
return(null);
}
else
{
return(rows);
}
}
/// <summary>
/// Read next record
/// </summary>
/// <returns></returns>
public AnnotationVo Read()
{
if (!DbCmd.Read())
{
return(null);
}
AnnotationVo vo = new AnnotationVo();
vo.rslt_id = DbCmd.GetLong(0);
vo.rslt_grp_id = DbCmd.GetLong(1);
vo.ext_cmpnd_id_txt = DbCmd.GetString(2);
vo.ext_cmpnd_id_nbr = DbCmd.GetInt(3);
vo.src_db_id = DbCmd.GetInt(4);
vo.mthd_vrsn_id = DbCmd.GetInt(5);
vo.rslt_typ_id = DbCmd.GetLong(6);
vo.rslt_val_prfx_txt = DbCmd.GetString(7);
vo.rslt_val_nbr = DbCmd.GetDouble(8);
vo.uom_id = DbCmd.GetInt(9);
vo.rslt_val_txt = DbCmd.GetString(10);
vo.rslt_val_dt = DbCmd.GetDateTime(11);
vo.cmnt_txt = DbCmd.GetString(12);
vo.dc_lnk = DbCmd.GetString(13);
vo.chng_op_cd = DbCmd.GetString(14);
vo.chng_usr_id = DbCmd.GetString(15);
return(vo);
}
/// <summary>
/// Insert/update a single result row
/// </summary>
/// <param name="vo"></param>
/// <returns></returns>
public long InsertUpdateRow(AnnotationVo vo)
{
bool existingVal = vo.rslt_id > 0 && vo.rslt_grp_id == -1;
if (existingVal)
{
SetArchiveStatus(vo.rslt_id); // archive existing val
}
if (vo.rslt_id > 0) // get existing group
{
AnnotationVo vo2 = Select(vo.rslt_id);
if (vo2 != null)
{
vo.rslt_grp_id = vo2.rslt_grp_id;
}
}
if (vo.rslt_grp_id <= 0)
{
vo.rslt_grp_id = GetNextIdLong();
}
vo.rslt_id = GetNextIdLong();
List <AnnotationVo> voList = new List <AnnotationVo>();
voList.Add(vo);
if (Insert(voList))
{
return(vo.rslt_id);
}
else
{
return(-1);
}
}
/// <summary>
/// Insert Mobius warehouse row filling common parameters from var
/// </summary>
/// <param name="vo"></param>
/// <param name="cid"></param>
/// <param name="mt"></param>
/// <param name="mc"></param>
/// <returns></returns>
public bool Insert(
AnnotationVo vo,
string cid,
int methodVersionId,
long resultTypeId)
{
vo.ext_cmpnd_id_txt = cid; // compound id
vo.mthd_vrsn_id = methodVersionId;
vo.rslt_typ_id = resultTypeId;
vo.chng_op_cd = "I"; // this is an insert
vo.chng_usr_id = Security.UserInfo.UserName;
return(Insert(vo));
}
/// <summary>
/// Add an annotation vo to a vo list filling common parameters from args
/// </summary>
/// <param name="vo"></param>
/// <param name="cid"></param>
/// <param name="mt"></param>
/// <param name="mc"></param>
/// <param name="voList"></param>
/// <returns></returns>
public static void AddAnnotationVoToList(
AnnotationVo vo,
string cid,
int methodVersionId,
int resultTypeId,
List <AnnotationVo> voList)
{
vo.ext_cmpnd_id_txt = cid; // compound id
vo.mthd_vrsn_id = methodVersionId;
vo.rslt_typ_id = resultTypeId;
vo.chng_op_cd = "I"; // this is an insert
vo.chng_usr_id = SS.I.UserName;
voList.Add(vo);
}
/// <summary>
/// Select row for specified result id
/// </summary>
/// <param name="rsltId"></param>
/// <returns></returns>
public AnnotationVo Select(
long rsltId)
{
string sql =
"select " +
"rslt_id, rslt_grp_id, ext_cmpnd_id_txt, ext_cmpnd_id_nbr," +
"src_db_id, mthd_vrsn_id, rslt_typ_id, rslt_val_prfx_txt," +
"rslt_val_nbr, uom_id, rslt_val_txt, rslt_val_dt," +
"cmnt_txt, dc_lnk, chng_op_cd, chng_usr_id " +
"from " + TableName + " " +
"where rslt_id = :0";
DbCmd.Prepare(sql, OracleDbType.Long);
DbCmd.ExecuteReader(rsltId);
if (!DbCmd.Read())
{
return(null);
}
AnnotationVo vo = new AnnotationVo();
vo.rslt_id = DbCmd.GetLong(0);
vo.rslt_grp_id = DbCmd.GetLong(1);
vo.ext_cmpnd_id_txt = DbCmd.GetString(2);
vo.ext_cmpnd_id_nbr = DbCmd.GetInt(3);
vo.src_db_id = DbCmd.GetInt(4);
vo.mthd_vrsn_id = DbCmd.GetInt(5);
vo.rslt_typ_id = DbCmd.GetLong(6);
vo.rslt_val_prfx_txt = DbCmd.GetString(7);
vo.rslt_val_nbr = DbCmd.GetDouble(8);
vo.uom_id = DbCmd.GetInt(9);
vo.rslt_val_txt = DbCmd.GetString(10);
vo.rslt_val_dt = DbCmd.GetDateTime(11);
vo.cmnt_txt = DbCmd.GetString(12);
vo.dc_lnk = DbCmd.GetString(13);
vo.chng_op_cd = DbCmd.GetString(14);
vo.chng_usr_id = DbCmd.GetString(15);
return(vo);
}
/// <summary>
/// Insert row
/// </summary>
/// <param name="vo"></param>
/// <returns></returns>
public bool Insert(
AnnotationVo vo)
{
if (vo.rslt_val_nbr == NullValue.NullNumber &&
(vo.rslt_val_txt == null || vo.rslt_val_txt == "") &&
vo.rslt_val_dt == DateTime.MinValue)
{
return(false); // don't allow insert of null values (can't update since not retrieved)
}
if (InsertBuffer == null) // single insert
{
List <AnnotationVo> voList = new List <AnnotationVo>();
voList.Add(vo);
return(Insert(voList));
}
else // buffered batch
{
InsertBuffer.Add(vo.Clone());
return(true);
}
}
/// <summary>
/// Fast insert/update of a single row including creation of the AnnotationDao object, transaction and Header update
/// </summary>
/// <param name="vo"></param>
/// <returns></returns>
public static long InsertUpdateRowAndUserObjectHeader(AnnotationVo vo)
{
if (ServiceFacade.UseRemoteServices)
{
List <AnnotationVo> voList = new List <AnnotationVo>();
voList.Add(vo);
List <ServiceTypes.AnnotationVo> serviceVoList =
ServiceFacade.TypeConversionHelper.Convert <List <AnnotationVo>, List <ServiceTypes.AnnotationVo> >(voList);
Mobius.Services.Native.INativeSession nativeClient = ServiceFacade.CreateNativeSessionProxy();
Services.Native.NativeMethodTransportObject resultObject =
ServiceFacade.InvokeNativeMethod(nativeClient,
(int)Services.Native.ServiceCodes.MobiusAnnotationService,
(int)Services.Native.ServiceOpCodes.MobiusAnnotationService.InsertUpdateRowAndUserObjectHeader,
new Services.Native.NativeMethodTransportObject(new object[] { serviceVoList }));
((System.ServiceModel.IClientChannel)nativeClient).Close();
long newRsltId = (long)resultObject.Value;
return(newRsltId);
}
else
{
return(UAL.AnnotationDao.InsertUpdateRowAndUserObjectHeader(vo));
}
}
/// <summary>
/// Fast insert/update of a single row including creation of the AnnotationDao object, transaction and header update
/// </summary>
/// <param name="vo"></param>
/// <returns></returns>
public static long InsertUpdateRowAndUserObjectHeader(AnnotationVo vo)
{
bool newRow = vo.rslt_id <= 0;
AnnotationDao dao = new AnnotationDao();
dao.BeginTransaction();
long rsltId = dao.InsertUpdateRow(vo);
dao.Commit();
dao.Dispose();
UserObject uo = UserObjectDao.ReadHeader(vo.mthd_vrsn_id);
if (uo != null)
{
uo.UpdateDateTime = DateTime.Now;
if (newRow)
{
uo.Count++;
}
UserObjectDao.UpdateHeader(uo);
}
return(rsltId);
}
/// <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
}
object IInvokeServiceOps.InvokeServiceOperation(int opCode, object[] args)
{
MobiusAnnotationService op = (MobiusAnnotationService)opCode;
switch (op)
{
case MobiusAnnotationService.CreateInstance:
{
int instanceId = -1;
Mobius.UAL.AnnotationDao instance = new Mobius.UAL.AnnotationDao();
lock (_lockObject)
{
instanceId = _nextInstanceId++;
_instanceIdToInstance.Add(instanceId, instance);
}
return(instanceId);
}
case MobiusAnnotationService.DisposeInstance:
{
bool disposed = false;
int instanceId = (int)args[0];
Mobius.UAL.AnnotationDao instance = null;
lock (_lockObject)
{
if (_instanceIdToInstance.ContainsKey(instanceId))
{
instance = _instanceIdToInstance[instanceId];
_instanceIdToInstance.Remove(instanceId);
}
}
if (instance != null)
{
instance.Dispose();
disposed = true;
}
return(disposed);
}
case MobiusAnnotationService.Select:
{
int instanceId = (int)args[0];
int mthdVrsnId = (int)args[1];
string cmpndId = (string)args[2];
Mobius.UAL.AnnotationDao instance = null;
lock (_lockObject)
{
if (_instanceIdToInstance.ContainsKey(instanceId))
{
instance = _instanceIdToInstance[instanceId];
}
}
if (instance != null)
{
List <Mobius.Data.AnnotationVo> mobiusAnnotationVos =
instance.Select(mthdVrsnId, cmpndId);
List <AnnotationVo> annotationVos =
_transHelper.Convert <List <Mobius.Data.AnnotationVo>, List <AnnotationVo> >(mobiusAnnotationVos);
return(annotationVos);
}
return(null);
}
case MobiusAnnotationService.SelectByResultId:
{
int instanceId = (int)args[0];
long rsltId = (long)args[1];
Mobius.UAL.AnnotationDao instance = null;
lock (_lockObject)
{
if (_instanceIdToInstance.ContainsKey(instanceId))
{
instance = _instanceIdToInstance[instanceId];
}
}
if (instance != null)
{
Mobius.Data.AnnotationVo mobiusAnnotationVo =
instance.Select(rsltId);
AnnotationVo annotationVo =
_transHelper.Convert <Mobius.Data.AnnotationVo, AnnotationVo>(mobiusAnnotationVo);
return(annotationVo);
}
return(null);
}
case MobiusAnnotationService.SelectCompoundCount:
{
int instanceId = (int)args[0];
int mthdVrsnId = (int)args[1];
Mobius.UAL.AnnotationDao instance = null;
lock (_lockObject)
{
if (_instanceIdToInstance.ContainsKey(instanceId))
{
instance = _instanceIdToInstance[instanceId];
}
}
if (instance != null)
{
int count = instance.SelectCompoundCount(mthdVrsnId);
return(count);
}
return(null);
}
case MobiusAnnotationService.SelectRowCount:
{
int instanceId = (int)args[0];
int mthdVrsnId = (int)args[1];
Mobius.UAL.AnnotationDao instance = null;
lock (_lockObject)
{
if (_instanceIdToInstance.ContainsKey(instanceId))
{
instance = _instanceIdToInstance[instanceId];
}
}
if (instance != null)
{
int rowCount = instance.SelectRowCount(mthdVrsnId);
return(rowCount);
}
return(null);
}
case MobiusAnnotationService.GetNonNullRsltCnt:
{
int instanceId = (int)args[0];
int mthdVrsnId = (int)args[1];
Mobius.UAL.AnnotationDao instance = null;
lock (_lockObject)
{
if (_instanceIdToInstance.ContainsKey(instanceId))
{
instance = _instanceIdToInstance[instanceId];
}
}
if (instance != null)
{
int rowCount = instance.GetNonNullRsltCnt(mthdVrsnId);
return(rowCount);
}
return(null);
}
case MobiusAnnotationService.GetNextIdsLong:
{
int instanceId = (int)args[0];
int seqQueueRequestSize = (int)args[1];
Mobius.UAL.AnnotationDao instance = null;
lock (_lockObject)
{
if (_instanceIdToInstance.ContainsKey(instanceId))
{
instance = _instanceIdToInstance[instanceId];
}
}
if (instance != null)
{
//work-around for .Net bug for writes of multiples of 128
// (See: http://social.msdn.microsoft.com/Forums/en/wcf/thread/0c71eedb-6c71-4b94-97c7-332195a7eb5c )
if (seqQueueRequestSize % 2 == 0)
{
seqQueueRequestSize++;
}
long[] nextIds =
instance.GetNextIdsLong(seqQueueRequestSize);
return(nextIds);
}
return(null);
}
case MobiusAnnotationService.SetArchiveStatus:
{
int instanceId = (int)args[0];
long rsltId = (long)args[1];
Mobius.UAL.AnnotationDao instance = null;
lock (_lockObject)
{
if (_instanceIdToInstance.ContainsKey(instanceId))
{
instance = _instanceIdToInstance[instanceId];
}
}
if (instance != null)
{
int result =
instance.SetArchiveStatus(rsltId);
return(result);
}
return(null);
}
case MobiusAnnotationService.SetResultGroupArchiveStatus:
{
int instanceId = (int)args[0];
long rsltGrpId = (long)args[1];
Mobius.UAL.AnnotationDao instance = null;
lock (_lockObject)
{
if (_instanceIdToInstance.ContainsKey(instanceId))
{
instance = _instanceIdToInstance[instanceId];
}
}
if (instance != null)
{
int result =
instance.SetResultGroupArchiveStatus(rsltGrpId);
return(result);
}
return(null);
}
case MobiusAnnotationService.DeleteCompound:
{
int instanceId = (int)args[0];
int mthdVrsnId = (int)args[1];
string cmpndId = (string)args[2];
Mobius.UAL.AnnotationDao instance = null;
lock (_lockObject)
{
if (_instanceIdToInstance.ContainsKey(instanceId))
{
instance = _instanceIdToInstance[instanceId];
}
}
if (instance != null)
{
int result =
instance.DeleteCompound(mthdVrsnId, cmpndId);
return(result);
}
return(null);
}
case MobiusAnnotationService.DeleteTable:
{
int instanceId = (int)args[0];
int mthdVrsnId = (int)args[1];
bool useTransactions = (bool)args[2];
Mobius.UAL.AnnotationDao instance = null;
lock (_lockObject)
{
if (_instanceIdToInstance.ContainsKey(instanceId))
{
instance = _instanceIdToInstance[instanceId];
}
}
if (instance != null)
{
int result =
instance.DeleteTable(mthdVrsnId, useTransactions);
return(result);
}
return(null);
}
case MobiusAnnotationService.DeleteResultGroup:
{
int instanceId = (int)args[0];
long rsltGrpId = (long)args[1];
Mobius.UAL.AnnotationDao instance = null;
lock (_lockObject)
{
if (_instanceIdToInstance.ContainsKey(instanceId))
{
instance = _instanceIdToInstance[instanceId];
}
}
if (instance != null)
{
int result =
instance.DeleteResultGroup(rsltGrpId);
return(result);
}
return(null);
}
case MobiusAnnotationService.Insert:
{
int instanceId = (int)args[0];
List <AnnotationVo> voList = (List <AnnotationVo>)args[1];
Mobius.UAL.AnnotationDao instance = null;
lock (_lockObject)
{
if (_instanceIdToInstance.ContainsKey(instanceId))
{
instance = _instanceIdToInstance[instanceId];
}
}
if (instance != null)
{
List <Mobius.Data.AnnotationVo> mobiusAnnotationVos =
_transHelper.Convert <List <AnnotationVo>, List <Mobius.Data.AnnotationVo> >(voList);
bool result =
instance.Insert(mobiusAnnotationVos);
return(result);
}
return(null);
}
case MobiusAnnotationService.InsertUpdateRow:
{
int instanceId = (int)args[0];
List <AnnotationVo> voList = (List <AnnotationVo>)args[1];
Mobius.UAL.AnnotationDao instance = null;
lock (_lockObject)
{
if (_instanceIdToInstance.ContainsKey(instanceId))
{
instance = _instanceIdToInstance[instanceId];
}
}
if (instance != null)
{
List <Mobius.Data.AnnotationVo> mobiusAnnotationVos =
_transHelper.Convert <List <AnnotationVo>, List <Mobius.Data.AnnotationVo> >(voList);
long newRsltId = instance.InsertUpdateRow(mobiusAnnotationVos[0]);
return(newRsltId);
}
else
{
return(null);
}
}
case MobiusAnnotationService.InsertUpdateRowAndUserObjectHeader:
{
List <AnnotationVo> voList = (List <AnnotationVo>)args[0];
List <Mobius.Data.AnnotationVo> mobiusAnnotationVos =
_transHelper.Convert <List <AnnotationVo>, List <Mobius.Data.AnnotationVo> >(voList);
long newRsltId = Mobius.UAL.AnnotationDao.InsertUpdateRowAndUserObjectHeader(mobiusAnnotationVos[0]);
return(newRsltId);
}
case MobiusAnnotationService.DeepClone:
{
UserObjectNode uoNode = (UserObjectNode)args[0];
Mobius.Data.UserObject mobiusUO = _transHelper.Convert <UserObjectNode, Mobius.Data.UserObject>(uoNode);
Mobius.Data.UserObject fullMobiusUO = Mobius.UAL.AnnotationDao.DeepClone(mobiusUO);
UserObjectNode fullUONode = _transHelper.Convert <Mobius.Data.UserObject, UserObjectNode>(fullMobiusUO);
return(fullUONode);
}
case MobiusAnnotationService.UpdateCid:
{
int instanceId = (int)args[0];
int mthdVrsnId = (int)args[1];
string oldCid = (string)args[2];
string newCid = (string)args[3];
Mobius.UAL.AnnotationDao instance = null;
lock (_lockObject)
{
if (_instanceIdToInstance.ContainsKey(instanceId))
{
instance = _instanceIdToInstance[instanceId];
}
}
if (instance != null)
{
int result =
instance.UpdateCid(mthdVrsnId, oldCid, newCid);
return(result);
}
return(null);
}
case MobiusAnnotationService.UpdateResultGroupCid:
{
int instanceId = (int)args[0];
long rsltGrpId = (long)args[1];
string oldCid = (string)args[2];
string newCid = (string)args[3];
Mobius.UAL.AnnotationDao instance = null;
lock (_lockObject)
{
if (_instanceIdToInstance.ContainsKey(instanceId))
{
instance = _instanceIdToInstance[instanceId];
}
}
if (instance != null)
{
int result =
instance.UpdateResultGroupCid(rsltGrpId, oldCid, newCid);
return(result);
}
return(null);
}
case MobiusAnnotationService.BeginTransaction:
{
int instanceId = (int)args[0];
Mobius.UAL.AnnotationDao instance = null;
lock (_lockObject)
{
if (_instanceIdToInstance.ContainsKey(instanceId))
{
instance = _instanceIdToInstance[instanceId];
}
}
if (instance != null)
{
instance.BeginTransaction();
return(true);
}
return(false);
}
case MobiusAnnotationService.Commit:
{
int instanceId = (int)args[0];
Mobius.UAL.AnnotationDao instance = null;
lock (_lockObject)
{
if (_instanceIdToInstance.ContainsKey(instanceId))
{
instance = _instanceIdToInstance[instanceId];
}
}
if (instance != null)
{
instance.Commit();
return(true);
}
return(false);
}
}
return(null);
}
/// <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);
}
/// <summary>
/// Load data into PubChem database
/// PubChem assay data files are downloaded from the PubChem site:
/// http://pubchem.ncbi.nlm.nih.gov/ using a program like SmartFTP.
/// The files are in GNU Zip (.gz) format and can be unzipped with
/// the following gzip commands:
/// c:\gzip\gzip -d c:\pubchem\bioassay\csv\description\*.gz
/// c:\gzip\gzip -d c:\pubchem\bioassay\csv\data\*.gz
/// After downloading and decompression this method can be called on the files.
/// </summary>
/// <param name="args"></param>
/// <returns></returns>
public static string LoadData(
string aid)
{
int recCount = 0;
string mtName = "PubChem_aid_" + aid;
MetaTable mt = MetaTableCollection.Get(mtName);
if (mt == null)
{
return("Failed to get metatable");
}
// if (Math.Sqrt(4) == 2) goto UpdateCids;
string fileName = PubChemAssayDirectory + @"\CSV\Data\" + aid + ".csv";
StreamReader sr;
try { sr = new StreamReader(fileName); }
catch (Exception ex) { return("File not found: " + fileName); }
string header = sr.ReadLine(); // read headers line
List <string> headers = Csv.SplitCsvString(header);
int cidIdx = -1;
for (cidIdx = 0; cidIdx < headers.Count; cidIdx++)
{
if (headers[cidIdx].ToUpper() == "PUBCHEM_CID")
{
break;
}
}
if (cidIdx >= headers.Count)
{
sr.Close();
return("PUBCHEM_CID column not found in data headers");
}
Dictionary <string, MetaColumn> mcd = new Dictionary <string, MetaColumn>();
foreach (MetaColumn mc2 in mt.MetaColumns)
{
mcd[mc2.Name.ToUpper()] = mc2; // build dict for quick metacolumn lookup
}
DbConnectionMx conn = DbConnectionMx.MapSqlToConnection(ref PubChemWarehouseTable);
conn.BeginTransaction(); // do multiple updates per transaction
GenericDwDao dao = new GenericDwDao(
PubChemWarehouseTable, // table for results
PubChemWarehouseSeq); // sequence to use
dao.BufferInserts(true); // buffer inserts for better speed
SequenceDao.SetCacheSize(PubChemWarehouseSeq, 100); // number of ids to cache locally from sequence
//string progressMsg = "Deleting existing data...";
int i1 = dao.DeleteTable(Int32.Parse(mt.TableFilterValues[0]), true);
//if (Progress.CancelRequested())
//{
// dao.Dispose();
// return "Cancelled during data delete";
//}
//Progress.Show("Loading file...");
recCount = 0;
int t1 = 0;
while (true)
{
int t2 = TimeOfDay.Milliseconds();
if (t2 - t1 > 1000)
{
if (Progress.CancelRequested)
{
dao.ExecuteBufferedInserts();
conn.Commit();
conn.Close();
sr.Close();
Progress.Hide();
return(recCount.ToString() + " rows loaded");
}
Progress.Show("Loading file (" + recCount.ToString() + ") ...");
t1 = t2;
}
string rec = sr.ReadLine();
if (rec == null)
{
break;
}
List <string> vals = Csv.SplitCsvString(rec);
int cid;
try { cid = Int32.Parse(vals[cidIdx]); } // get compound id
catch (Exception ex)
{
string txtCid = vals[cidIdx];
if (txtCid == null)
{
txtCid = "";
}
DebugLog.Message("Load PubChem bad CID " + txtCid + ", AID = " + aid);
continue;
}
long rslt_grp_id = dao.GetNextIdLong(); // id to hold row together
for (int vi = 0; vi < vals.Count; vi++)
{
string s = vals[vi];
if (s == "")
{
continue;
}
string[] sa = rec.Split(',');
if (vi >= headers.Count)
{
continue;
}
string mcName = headers[vi].ToUpper();
if (mcName.Length > 26)
{
mcName = mcName.Substring(0, 26); // limit length to 26
}
if (mcName == "PUBCHEM_CID")
{
continue;
}
if (Lex.IsInteger(mcName))
{
mcName = "R_" + mcName; // result number
}
MetaColumn mc = mcd[mcName];
if (mc == null)
{
continue;
}
AnnotationVo vo = new AnnotationVo();
vo.rslt_grp_id = rslt_grp_id;
if (mc.DataType == MetaColumnType.String)
{
vo.rslt_val_txt = s;
}
else if (mc.DataType == MetaColumnType.Number || mc.DataType == MetaColumnType.Integer)
{
try
{
vo.rslt_val_nbr = Convert.ToDouble(s);
}
catch (Exception e) { continue; } // just continue if bad
}
else if (mc.DataType == MetaColumnType.Date)
{
s = DateTimeMx.Normalize(s);
if (s == null)
{
continue;
}
vo.rslt_val_dt = DateTimeMx.NormalizedToDateTime(s);
}
else if (mc.Name == "PUBCHEM_ACTIVITY_OUTCOME") // activity outcome is a dict value stored as an integer
{
try
{
vo.rslt_val_nbr = Convert.ToInt32(s);
}
catch (Exception e) { continue; } // just continue if bad
}
else if (mc.DataType == MetaColumnType.Hyperlink ||
mc.DataType == MetaColumnType.DictionaryId)
{
vo.rslt_val_txt = s;
}
else
{
continue;
}
vo.ext_cmpnd_id_nbr = cid;
vo.ext_cmpnd_id_txt = cid.ToString();
vo.mthd_vrsn_id = Int32.Parse(mt.TableFilterValues[0]);
vo.rslt_typ_id = Int32.Parse(mc.PivotValues[0]);
vo.chng_op_cd = "I";
vo.chng_usr_id = Security.UserInfo.UserName;
dao.Insert(vo);
} // end of field loop
recCount++;
if (recCount % 100 == 0)
{ // commit after group of updates
dao.ExecuteBufferedInserts();
conn.Commit();
conn.BeginTransaction(); // do multiple updates per transaction
}
} // end of record loop
dao.ExecuteBufferedInserts();
conn.Commit();
conn.Close();
dao.Dispose();
sr.Close();
//UpdateCids: // Add any missing CIDs under method 1000000
Progress.Show("Updating CID table...");
string sql =
"INSERT INTO " + PubChemWarehouseTable + "(ext_cmpnd_id_nbr,rslt_id,mthd_vrsn_id,rslt_typ_id,rslt_grp_id) " +
"SELECT ext_cmpnd_id_nbr, " + PubChemWarehouseSeq + ".NEXTVAL,1000000,0,0 " +
"FROM ( " +
"SELECT UNIQUE ext_cmpnd_id_nbr " +
"FROM " + PubChemWarehouseTable + " r1 " +
"WHERE mthd_vrsn_id = " + aid + " " +
"AND NOT EXISTS ( " +
" SELECT * " +
"FROM " + PubChemWarehouseTable + " r2 " +
"WHERE mthd_vrsn_id = 1000000 " +
"AND r2.ext_cmpnd_id_nbr = r1.ext_cmpnd_id_nbr) " +
"and rownum <= 10000)";
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.BeginTransaction();
int newCids = 0;
while (true)
{
int addedCids = drd.ExecuteNonReader();
if (addedCids == 0)
{
break;
}
newCids += addedCids;
drd.Commit();
drd.BeginTransaction(); // do multiple updates per transaction
Progress.Show("Updating CID table (" + newCids.ToString() + ")...");
}
drd.Dispose();
Progress.Hide();
return(recCount.ToString() + " rows loaded for AID " + aid + " plus " + newCids.ToString() + " new CIDs");
}
