/// <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());
}
public void Setup(ColumnInfo colInfo)
{
bool check;
string s;
InSetup = true;
ColInfo = colInfo; // save ref to colInfo
QueryColumn qc = colInfo.Qc;
ParsedSingleCriteria psc = MqlUtil.ParseSingleCriteria(ColInfo.Qc.SecondaryCriteria); // parse criteria
Dictionary <string, object> listDict = new Dictionary <string, object>();
if (psc != null && psc.OpEnum != CompareOp.In)
{
psc = null; // switching from other criteria type
}
if (psc != null && Lex.Contains(qc.SecondaryCriteria, "(All)"))
{
psc = null; // rebuild if "all" items
}
if (psc != null) // list of previously selected items
{
foreach (string vs in psc.ValueList)
{
if (qc.MetaColumn.DataType == MetaColumnType.CompoundId)
{
s = CompoundId.Format(vs);
}
else if (qc.MetaColumn.DataType == MetaColumnType.Date)
{
s = DateTimeMx.Format(vs);
}
else
{
s = vs;
}
listDict[s.ToUpper()] = null;
}
}
else // setup default criteria
{
qc.SecondaryCriteria = qc.MetaColumn.Name + " in ('(All)')";
qc.SecondaryCriteriaDisplay = qc.ActiveLabel + " in list (All)";
}
ColumnStatistics stats = colInfo.Rfld.GetStats();
CheckedListBoxItemCollection items = ItemList.Items;
items.Clear();
check = (psc == null || listDict.ContainsKey("(All)".ToUpper()) || listDict.ContainsKey("All".ToUpper()));
AddItem("(All)", check);
if (stats.NullsExist || Math.Abs(1) == 1)
{ // always include blank/nonblank since there may be null rows because other tables have more rows for key
check = (psc == null || listDict.ContainsKey("(Blanks)".ToUpper()) || listDict.ContainsKey("Blanks".ToUpper()));
BlanksPos = items.Count;
AddItem("(Blanks)", check);
check = (psc == null || listDict.ContainsKey("(Non blanks)".ToUpper()) || listDict.ContainsKey("Nonblanks".ToUpper()));
NonBlanksPos = items.Count;
AddItem("(Non blanks)", check);
}
else
{
BlanksPos = NonBlanksPos = -1;
}
foreach (MobiusDataType mdt in stats.DistinctValueList)
{
s = mdt.FormattedText;
check = (psc == null || listDict.ContainsKey(s.ToUpper()));
AddItem(s, check);
if (items.Count >= 100)
{
AddItem("...", check);
break;
}
}
int itemHeight = 18; // height of single item (really 17 but add a bit extra)
int fullHeight = ItemList.Top + 6 + ItemList.Items.Count * itemHeight; // full height of control
if (fullHeight < this.Height)
{
this.Height = fullHeight;
}
InSetup = false;
return;
}
/// <summary>
/// Setup the form with existing criteria
/// </summary>
/// <param name="qc"></param>
void Setup(
QueryColumn qc)
{
string op, tok, val;
CheckEdit option = null;
CheckEdit subOption = null;
int i1;
MetaColumn mc = qc.MetaColumn;
string prompt = "Select the search criteria that you want to apply to " + qc.ActiveLabel +
" from the list below and enter the limiting value(s).";
bool removeDuplicates = mc.IgnoreCase; // remove dups if ignoring case
UIMisc.SetListControlItemsFromDictionary(Value.Properties.Items, mc.Dictionary, removeDuplicates); // setup dropdown
switch (mc.DataType)
{
case MetaColumnType.Integer:
case MetaColumnType.Number:
case MetaColumnType.QualifiedNo:
Setup(true, true, false, false);
break;
case MetaColumnType.String:
Setup(true, true, true, false);
break;
case MetaColumnType.Date:
prompt += " Dates can be entered in the common standard ways: e.g. 12/31/2004 or 31-Dec-04.";
Setup(true, true, false, true);
break;
case MetaColumnType.DictionaryId:
Setup(false, false, false, false);
break;
}
// Fill in the form with the current criteria
Instance.Text = "Search Criteria for " + qc.ActiveLabel;
Instance.Prompt.Text = prompt;
ParsedSingleCriteria psc = MqlUtil.ParseSingleCriteria(qc.Criteria);
if (psc == null)
{ // create default values
psc = new ParsedSingleCriteria();
psc.Op = "=";
}
if (mc.DataType == MetaColumnType.Date && psc.OpEnum != CompareOp.Within) // format dates for user if not within clause
{
if (psc.Value != "")
{
psc.Value = DateTimeMx.Format(psc.Value);
}
if (psc.Value2 != "")
{
psc.Value2 = DateTimeMx.Format(psc.Value2);
}
if (psc.ValueList != null)
{
for (i1 = 0; i1 < psc.ValueList.Count; i1++)
{
tok = (string)psc.ValueList[i1];
if (tok != null && tok != "")
{
psc.ValueList[i1] = DateTimeMx.Format(tok);
}
}
}
}
else if (mc.DataType == MetaColumnType.DictionaryId && psc.Value != "")
{ // transform database value to dictionary value
val = DictionaryMx.LookupWordByDefinition(mc.Dictionary, psc.Value);
if (val != null && val != "")
{
psc.Value = val;
}
}
op = psc.Op;
// Fill fields based on criteria types
if (op == "" || op.IndexOf("=") >= 0 || op.IndexOf("<") >= 0 || op.IndexOf(">") >= 0)
{
option = BasicOp;
if (op == "=" || op == "")
{
BasicOpBut.Text = "Equals";
}
else if (op == "<")
{
BasicOpBut.Text = "<";
}
else if (op == "<=")
{
BasicOpBut.Text = UnicodeString.LessOrEqual;
}
else if (op == "<>")
{
BasicOpBut.Text = UnicodeString.NotEqual;
}
else if (op == ">")
{
BasicOpBut.Text = ">";
}
else if (op == ">=")
{
BasicOpBut.Text = UnicodeString.GreaterOrEqual;
}
Value.Text = psc.Value; // put in current value
}
else if (Lex.Eq(op, "Between"))
{
option = Between;
Limit1.Text = psc.Value;
Limit2.Text = psc.Value2;
}
else if (Lex.Eq(op, "In"))
{
option = InList;
StringBuilder sb = new StringBuilder();
for (i1 = 0; i1 < psc.ValueList.Count; i1++)
{
if (i1 > 0)
{
sb.Append(", ");
}
sb.Append((string)psc.ValueList[i1]);
}
ValueList.Text = sb.ToString(); // set value
}
else if (Lex.Eq(op, "Like"))
{
option = Like;
tok = psc.Value.Replace("%", ""); // remove sql wildcard characters
Substring.Text = tok;
}
else if (Lex.Eq(op, "Within"))
{
option = Within;
WithinValue.Text = psc.Value;
string value2 = psc.Value2;
if (Lex.Contains(value2, "Day")) // translate alternative values
{
value2 = "Day(s)";
}
else if (Lex.Contains(value2, "Week"))
{
value2 = "Week(s)";
}
else if (Lex.Contains(value2, "Month"))
{
value2 = "Month(s)";
}
else if (Lex.Contains(value2, "Year"))
{
value2 = "Year(s)";
}
WithinUnits.Text = value2;
}
else if (Lex.Eq(op, "is not null"))
{
option = IsNotNull;
}
else if (Lex.Eq(op, "is null"))
{
option = IsNull;
}
else if (Lex.Eq(op, "is not null or is null"))
{
option = All;
}
else // oops
{
MessageBoxMx.ShowError("Unrecognized criteria type");
qc.Criteria = qc.CriteriaDisplay = "";
return;
}
option.Checked = true;
return;
}
/// <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");
}
private static Alert DeserializeHeaderOld(
UserObject uo,
bool fast)
{
string[] sa;
Alert a = new Alert();
a.Id = uo.Id;
//if (uo.Description.Trim().Length > 0 && uo.Description.Split('\t').Length == 2)
//{ // sometimes the desc contains just the queryId \t lastNewDate
// sa = uo.Description.Split('\t');
// string qidString = sa[0].Substring(sa[0].IndexOf(" ") + 1);
// a.QueryObjId = int.Parse(qidString);
// a.LastNewData = DateTimeEx.DateTimeParseUS(sa[1]);
// return a;
//}
if (uo.Description.Trim().Length > 0)
{ // have old new-style info?
try
{
sa = uo.Description.Split('\t');
a.Owner = sa[0];
a.QueryObjId = Int32.Parse(sa[1]);
a.Interval = Int32.Parse(sa[2]);
a.MailTo = sa[3];
if (a.MailTo == null || a.MailTo.Trim().Length == 0) // need to get email address
{
a.MailTo = Security.GetUserEmailAddress(a.Owner);
}
a.LastCheck = DateTimeMx.NormalizedToDateTime(sa[4]);
a.NewCompounds = Int32.Parse(sa[5]);
a.ChangedCompounds = Int32.Parse(sa[6]);
a.TotalCompounds = Int32.Parse(sa[7]);
a.NewRows = Int32.Parse(sa[8]);
a.TotalRows = Int32.Parse(sa[9]);
if (sa.Length > 10)
{
a.CheckTablesWithCriteriaOnly = bool.Parse(sa[10]);
}
if (sa.Length > 11)
{
a.LastNewData = DateTimeMx.NormalizedToDateTime(sa[11]);
}
}
catch (Exception ex) { throw new Exception(ex.Message, ex); } // debug
}
else // old style alert, get partial header info
{
a.Owner = uo.Owner;
a.QueryObjId = Int32.Parse(uo.Name.Substring(uo.Name.IndexOf("_") + 1));
a.Interval = 1; // assume just 1 day
if (!fast)
{
try // get old last check date
{
Query q = QbUtil.ReadQuery(a.QueryObjId);
if (q.AlertInterval > 0)
{
a.Interval = q.AlertInterval;
}
sa = uo.Content.Split('\t');
a.LastCheck = DateTimeMx.NormalizedToDateTime(sa[4]);
}
catch (Exception ex) { string msg = ex.Message; }
}
a.MailTo = Security.GetUserEmailAddress(a.Owner); // need to get email address
}
return(a);
}
/// <summary>
/// Build & show the Print Preview dialog
/// </summary>
/// <param name="printableDxControl"></param>
/// <returns></returns>
static DialogResult ShowDialog(
IPrintable printableDxControl)
{
string leftColumn, middleColumn, rightColumn;
int m;
Query query = Instance.Qm.Query;
int printScale = query.PrintScale; // scale the document
if (printScale < 0 && !(Instance.Qm.MoleculeGrid.MainView is DevExpress.XtraGrid.Views.BandedGrid.BandedGridView))
{ // if not banded view be sure not fit to width
printScale = query.PrintScale = 100;
}
Instance.Qm.ResultsFormat.PageScale = printScale; // do view scaling based on printScale
ResultsFormat rf = Instance.Qm.ResultsFormat;
PrintingSystem ps = new PrintingSystem();
Instance.Ps = ps;
Instance.PrintControl.PrintingSystem = ps;
PrintableComponentLink pcl = new PrintableComponentLink(ps);
Instance.Pcl = pcl;
pcl.Component = printableDxControl;
ps.Links.Add(pcl);
pcl.CreateDetailArea += new CreateAreaEventHandler(Instance.PrintableComponentLink_CreateDetailArea);
pcl.CreateDetailHeaderArea += new CreateAreaEventHandler(Instance.PrintableComponentLink_CreateDetailHeaderArea);
pcl.CreateInnerPageHeaderArea += new CreateAreaEventHandler(Instance.PrintableComponentLink_InnerPageHeaderArea);
pcl.CreateMarginalHeaderArea += new CreateAreaEventHandler(Instance.PrintableComponentLink_CreateMarginalHeaderArea);
pcl.CreateReportHeaderArea += new CreateAreaEventHandler(Instance.PrintableComponentLink_CreateReportHeaderArea);
if (query.PrintMargins > 0)
{
m = query.PrintMargins / 10;
pcl.Margins = new Margins(m, m, m, m);
}
if (query.PrintOrientation == Orientation.Vertical)
{
pcl.Landscape = false;
}
else
{
pcl.Landscape = true;
}
PageHeaderFooter phf = pcl.PageHeaderFooter as PageHeaderFooter;
phf.Header.Content.Clear();
middleColumn = rf.Query.UserObject.Name; // use query name for heading
phf.Header.Content.AddRange(new string[] { "", middleColumn, "" });
phf.Header.LineAlignment = BrickAlignment.Center;
phf.Header.Font = new Font("Arial", 10, FontStyle.Bold);
leftColumn = "Mobius: " + DateTimeMx.Format(DateTimeMx.GetCurrentDate());
middleColumn = "Confidential";
rightColumn = "Page [Page #]";
phf.Footer.Content.Clear();
phf.Footer.Content.AddRange(new string[] { leftColumn, middleColumn, rightColumn });
phf.Footer.LineAlignment = BrickAlignment.Center;
// Todo: If DataTable is big just use a subset for preview? Takes about 5 secs to format 100 structs.
Instance.PreviewRowCount = Instance.Qm.DataTable.Rows.Count;
Progress.Show("Formatting preview...", "Mobius", true);
pcl.CreateDocument();
Progress.Hide();
if (printScale > 0) // scale to percentage
{
ps.Document.ScaleFactor = 1.0f; // keep document scale at 1.0
ps.Document.AutoFitToPagesWidth = 0;
}
else
{
Instance.Qm.MoleculeGrid.ScaleBandedGridView(100.0); // scale grid to full size before setting doc scale
ps.Document.AutoFitToPagesWidth = (int)-printScale; // fit to 1 or more pages
int pctScale = (int)(ps.Document.ScaleFactor * 100);
Instance.Qm.ResultsFormat.PageScale = pctScale;
Instance.Qm.MoleculeGrid.ScaleBandedGridView(pctScale / 100.0); // scale grid down to get structures correct size
pcl.CreateDocument(); // recreate the doc
}
ps.StartPrint += new PrintDocumentEventHandler(Instance.PrintingSystem_StartPrint);
ps.PrintProgress += new PrintProgressEventHandler(Instance.PrintingSystem_PrintProgress);
ps.EndPrint += new EventHandler(Instance.PrintingSystem_EndPrint);
ps.ShowPrintStatusDialog = true;
Form shell = SessionManager.Instance.ShellForm;
Instance.Location = shell.Location;
Instance.Size = shell.Size;
Instance.WindowState = shell.WindowState;
RibbonControl src = SessionManager.Instance.RibbonCtl; // model this ribbon on shell ribbon
if (src != null)
{
Instance.RibbonControl.RibbonStyle = src.RibbonStyle;
Instance.RibbonControl.ApplicationIcon = src.ApplicationIcon;
}
DialogResult dr = Instance.ShowDialog(shell); // show the preview
query.PrintMargins = pcl.Margins.Left * 10; // keep margins in milliinches
if (pcl.Landscape)
{
query.PrintOrientation = Orientation.Horizontal;
}
else
{
query.PrintOrientation = Orientation.Vertical;
}
Progress.Hide(); // just in case
return(dr);
}
