/// <summary>
/// Check for valid low or high value
/// </summary>
/// <param name="val"></param>
/// <param name="colType"></param>
/// <param name="r"></param>
/// <param name="c"></param>
/// <returns></returns>
private bool IsValidValue(
string val,
MetaColumnType colType,
int r,
int c)
{
double d1;
if (MetaColumn.IsNumericMetaColumnType(colType))
{
if (!double.TryParse(val, out d1))
{
XtraMessageBox.Show("Invalid numeric value", UmlautMobius.String);
//RulesGrid.EditCell(r, c);
return(false);
}
}
else if (colType == MetaColumnType.Date)
{
if (DateTimeMx.Normalize(val) == null)
{
XtraMessageBox.Show("Invalid date", UmlautMobius.String);
//RulesGrid.EditCell(r, c);
return(false);
}
}
return(true);
}
static void AddInsertColumn(
string colName,
ref string names,
ref string values,
object obj)
{
if (names != "")
{
names += ",";
}
names += colName;
if (values != "")
{
values += ", ";
}
if (obj == null)
{
values += "null";
}
else if (obj is int && ((int)obj) == NullValue.NullNumber || // store null number values as nulls
obj is double && ((double)obj) == NullValue.NullNumber)
{
values += "null";
}
else if (obj is DateTime)
{
DateTime dt = (DateTime)obj;
if (dt.Equals(DateTime.MinValue))
{
values += "null";
}
else
{
string yyyymmdd = DateTimeMx.Normalize(dt);
values += "to_date('" + yyyymmdd + "','YYYYMMDD')";
}
}
else
{
values += Lex.AddSingleQuotes(obj.ToString());
}
return;
}
/// <summary>
/// Commandline command to analyse usage data
/// </summary>
/// <param name="commandLine"></param>
/// <returns></returns>
public static string AnalyzeUsageData(
string commandArgs)
{
string currentUserid = "";
DateTime startTime = DateTime.MinValue, finishTime;
int count, pass, n, rowCount = 0;
int i1, i2;
string txt, rec, buf;
string sql, sqlmsg, stmt, table;
int objid, curobj, emp_id;
int noMatchingBegin = 0;
DbCommandMx drd;
Lex lex = new Lex();
lex.OpenString(commandArgs);
string startDate = lex.Get();
string endDate = lex.Get();
string startDate2 = DateTimeMx.Normalize(startDate);
string endDate2 = DateTimeMx.Normalize(endDate);
if (startDate2 == null || endDate2 == null)
{
throw new Exception("Syntax: Analyze Usage Data <start_date> <end_date>");
}
startDate2 = "to_date('" + startDate2 + "','YYYYMMDD')";
endDate2 = "to_date('" + endDate2 + " 235959','YYYYMMDD HH24MISS')";
string where = "where crt_dt between " + startDate2 + " and " + endDate2;
string arg = lex.Get();
if (Lex.Eq(arg, "DumpUsageData"))
{
return(DumpUsageData(where));
}
// Init data
TableData = new Hashtable(); // number of times each table accessed
CommandData = new Hashtable(); // number of times each command accessed
TransactionData = new Hashtable(); // count for each transaction type
UserDataHash = new Hashtable(); // info on each user, site, department
// Process usage log records. Find the users for the time period,
// how many times each user accessed app and the average
// session time in minutes for each user
string orderBy = "order by ownr_id, crt_dt";
drd = UsageDao.Select(where, orderBy);
UserObject beginUo = new UserObject();
while (true)
{
UserObject uo = UsageDao.Read(drd);
if (uo == null)
{
drd.Dispose();
if (rowCount > 0)
{
break;
}
throw new Exception("No data found for specified for time period");
}
if (uo.Owner == null || uo.Owner.Trim().Length == 0)
{
continue; // skip if owner not specified
}
if (rowCount % 1000 == 0)
{
UAL.Progress.Show("Analyzing Usage Data, rows: " + rowCount + " ...", UmlautMobius.String, false);
}
rowCount++;
string eventName = uo.Description;
string eventData = uo.Content;
// Increment count on event type
object o = TransactionData[eventName];
if (o == null)
{
TransactionData.Add(eventName, 0);
count = 0;
}
else
{
count = (int)o;
}
TransactionData[eventName] = count + 1;
// Beginning of session?
if (Lex.Eq(eventName, "Begin"))
{
beginUo = uo;
currentUserid = uo.Owner;
startTime = uo.UpdateDateTime;
UserData(currentUserid).Count++;
}
else if (Lex.Eq(eventName, "SSS") ||
eventName.ToLower().StartsWith("strsrch sss"))
{
UserData(uo.Owner).SsCount++;
}
else if (Lex.Eq(eventName, "MSimilar") ||
eventName.ToLower().StartsWith("strsrch msimilar"))
{
UserData(uo.Owner).SimCount++;
}
else if (eventName.StartsWith("QueryGrid")) // get QueryGridAnd, Complex & Or
{
UserData(uo.Owner).QueryCount++;
}
else if (Lex.Eq(eventName, "TableStats") && uo.Content != null)
{
string[] sa = uo.Content.Split('\n');
for (i1 = 0; i1 < sa.Length; i1++)
{
if (sa[i1] == null || sa[i1] == "" || sa[i1] == "")
{
continue;
}
string[] sa2 = sa[i1].Split('\t'); // split into table name & count
if (sa2.Length != 2)
{
continue;
}
o = TableData[sa2[0]]; // lookup table
if (o == null)
{
TableData.Add(sa2[0], null);
count = 0;
}
else
{
count = (int)o;
}
TableData[sa2[0]] = count + Int32.Parse(sa2[1]);
}
}
else if (Lex.Eq(eventName, "CommandStats") && uo.Content != null)
{
string[] sa = uo.Content.Split('\n');
for (i1 = 0; i1 < sa.Length; i1++)
{
if (sa[i1] == null || sa[i1] == "" || sa[i1] == "")
{
continue;
}
string[] sa2 = sa[i1].Split('\t'); // split into table name & count
if (sa2.Length != 2)
{
continue;
}
o = CommandData[sa2[0]]; // lookup table
if (o == null)
{
CommandData.Add(sa2[0], null);
count = 0;
}
else
{
count = (int)o;
}
CommandData[sa2[0]] = count + Int32.Parse(sa2[1]);
}
}
else if (Lex.Eq(eventName, "End"))
{
if (uo.Owner == currentUserid) // same user?
{
UserData(currentUserid).Ended++;
TimeSpan elapsed = uo.UpdateDateTime.Subtract(startTime);
UserData(currentUserid).TotalTime += elapsed.Minutes;
}
else
{
noMatchingBegin++;
}
currentUserid = "";
}
} // end of main loop
// Calculate totals
UserDataVo totalVo = UserData("=Total=");
foreach (UserDataVo vo in UserDataHash.Values)
{
if (vo.Userid == totalVo.Userid)
{
continue;
}
totalVo.Users++;
totalVo.Count += vo.Count;
totalVo.Ended += vo.Ended;
totalVo.SsCount += vo.SsCount;
totalVo.SimCount += vo.SimCount;
totalVo.QueryCount += vo.QueryCount;
totalVo.TotalTime += vo.TotalTime;
}
// Calculate site totals
foreach (UserDataVo vo in UserDataHash.Values)
{
if (vo.Users > 0)
{
continue; // just individuals
}
if (vo.Site == "")
{
continue; // ignore if no site info
}
totalVo.Users++;
totalVo.Count += vo.Count;
totalVo.Ended += vo.Ended;
totalVo.SsCount += vo.SsCount;
totalVo.SimCount += vo.SimCount;
totalVo.QueryCount += vo.QueryCount;
totalVo.TotalTime += vo.TotalTime;
}
// Order user data by descending usage count
ArrayList UserDataList = new ArrayList(UserDataHash.Values);
UserDataVo ud1, ud2;
for (i1 = 1; i1 < UserDataList.Count; i1++)
{
ud1 = (UserDataVo)UserDataList[i1];
for (i2 = i1 - 1; i2 >= 0; i2--)
{
ud2 = (UserDataVo)UserDataList[i2];
if (ud1.Count < ud2.Count)
{
break;
}
UserDataList[i2 + 1] = UserDataList[i2];
}
UserDataList[i2 + 1] = ud1;
}
// Output user info
StringBuilder sb = new StringBuilder();
sb.AppendLine("Mobius Usage Statistics for " + startDate + " through " + endDate);
sb.AppendLine("");
string format, s;
object[] args;
format = "{0,-38} {1,5} {2,4} {3,7} {4,4} {5,4}";
args = new object[] { "User", "Uses", "Time", "Queries", "SSS", "Sim" };
s = String.Format(format, args);
sb.AppendLine(s);
args = new object[] { "--------------------------------------", "-----", "----", "-------", "----", "----" };
s = String.Format(format, args);
sb.AppendLine(s);
for (pass = 1; pass <= 2; pass++) // do summary values first, then users
{
for (int ui = 0; ui < UserDataList.Count; ui++)
{
UserDataVo vo = (UserDataVo)UserDataList[ui];
if (pass == 1 && vo.Users == 0)
{
continue; // skip users on first past
}
else if (pass == 2 && vo.Users > 0)
{
continue; // skip totals on 2nd pass
}
if (vo.Ended == 0)
{
vo.AverageTime = 0;
}
else
{
vo.AverageTime = vo.TotalTime / vo.Ended; // avg. Time (min) for properly ended sessions
}
if (vo.Users > 0) // summary
{
txt = vo.Userid + " (" + vo.Users.ToString() + " users)";
}
else
{ // single user
try
{
UserInfo sui = Security.GetUserInfo(vo.Userid);
txt = sui.LastName + ", " + sui.FirstName;
txt += " (" + vo.Userid + ")";
}
catch (Exception ex) { txt = vo.Userid; }
if (vo.Site != "")
{
txt += " (" + vo.Site + ")";
}
}
args = new object[] { txt, vo.Count, vo.AverageTime, vo.QueryCount, vo.SsCount, vo.SimCount };
s = String.Format(format, args);
sb.AppendLine(s);
}
}
// Output table usage stats
sb.AppendLine("");
format = "{0,-18} {1,8}";
s = String.Format(format, "MetaTable", "Accesses");
sb.AppendLine(s);
s = String.Format(format, "------------------", "--------");
sb.AppendLine(s);
ArrayList al = new ArrayList();
foreach (string key in TableData.Keys)
{
s = String.Format(format, key, TableData[key]);
al.Add(s);
}
al.Sort();
foreach (string s2 in al)
{
sb.AppendLine(s2);
}
// Output command usage stats
sb.AppendLine("");
format = "{0,-18} {1,8}";
s = String.Format(format, "Command", "Accesses");
sb.AppendLine(s);
s = String.Format(format, "------------------", "--------");
sb.AppendLine(s);
al = new ArrayList();
foreach (string key in CommandData.Keys)
{
s = String.Format(format, key, CommandData[key]);
al.Add(s);
}
al.Sort();
foreach (string s2 in al)
{
sb.AppendLine(s2);
}
// Output transaction counts
sb.AppendLine("");
format = "{0,-18} {1,8}";
s = String.Format(format, "Transaction", "Count");
sb.AppendLine(s);
s = String.Format(format, "------------------", "--------");
sb.AppendLine(s);
al = new ArrayList();
foreach (string key in TransactionData.Keys)
{
s = String.Format(format, key, TransactionData[key]);
al.Add(s);
}
al.Sort();
foreach (string s2 in al)
{
sb.AppendLine(s2);
}
return(sb.ToString());
}
/// <summary>
/// Parse an operator and value
/// </summary>
/// <param name="qc"></param>
/// <param name="op"></param>
/// <param name="val"></param>
/// <returns>Expert form of criteria if form is acceptable</returns>
static string CheckCriteriaValue(
QueryColumn qc,
string val)
{
string eval, txt, txt2, txt3;
int i1;
double d1;
MetaColumn mc = qc.MetaColumn;
if (val == null)
{
return(null);
}
val = eval = val.Trim();
switch (mc.DataType)
{
case MetaColumnType.Date:
eval = DateTimeMx.Normalize(val);
if (eval == null)
{
MessageBoxMx.Show(val + " is not a valid date", UmlautMobius.String,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(null);
}
break;
case MetaColumnType.Integer:
case MetaColumnType.Number:
case MetaColumnType.QualifiedNo:
try
{ d1 = Convert.ToDouble(val); }
catch (Exception e)
{
MessageBoxMx.Show(val + " is not a valid number", UmlautMobius.String,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(null);
}
break;
case MetaColumnType.String: // quoted form
case MetaColumnType.MolFormula:
eval = Lex.AddSingleQuotes(Lex.RemoveSingleQuotes(val));
break;
case MetaColumnType.DictionaryId: // translate dictionary value back to database value
eval = DictionaryMx.LookupDefinition(mc.Dictionary, val); // get database value
if (eval == null || eval == "")
{
MessageBoxMx.Show(Lex.Dq(val) + " is not a valid value.\nYou must select an item from the dropdown box.", UmlautMobius.String,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(null);
}
eval = Lex.AddSingleQuotes(Lex.RemoveSingleQuotes(eval)); // quote in case string value
break;
} // end of datatype case
return(eval);
} // end of CheckCriteriaValue
/// <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");
}