/// <summary>
/// Create a new user entry
/// </summary>
/// <param name="userInfo"></param>
/// <returns></returns>
public static void CreateUser(
UserInfo userInfo)
{
string txt;
if (userInfo == null || Lex.IsNullOrEmpty(userInfo.UserName))
{
throw new Exception("User not defined");
}
// txt = UserObjectDao.GetUserParameter(userInfo.UserName,"NameAddress");
// if (txt!="") return false;
txt =
userInfo.FirstName + "|" +
userInfo.MiddleInitial + "|" +
userInfo.LastName + "|" +
userInfo.EmailAddress + "|" +
userInfo.Company + "|" +
userInfo.Site + "|" +
userInfo.Department + "|" +
userInfo.UserDomainName + "|" +
userInfo.UserName;
UserObjectDao.SetUserParameter(userInfo.UserName, "NameAddress", txt);
GrantPrivilege(userInfo.UserName, "Logon"); // authorize by default
return;
}
private void OK_Click(object sender, EventArgs e)
{
// Save preferred project in server preferences if changed
if (PreferredProjectChanged)
{
SS.I.PreferredProjectId = PreferredProjectId;
UserObjectDao.SetUserParameter(SS.I.UserName, "PreferredProject", PreferredProjectId);
SessionManager.Instance.MainContentsControl.ShowNormal(); // redisplay main tree with new selected project open
}
// Save default folder info in local preferences
string folder = DefaultFolder.Text;
if (folder.EndsWith(@"\") && !folder.EndsWith(@":\"))
{
folder = folder.Substring(0, folder.Length - 1);
}
if (!System.IO.Directory.Exists(folder))
{
XtraMessageBox.Show("Folder does not exist: " + folder, UmlautMobius.String,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
DefaultFolder.Focus();
return;
}
ClientDirs.DefaultMobiusUserDocumentsFolder = folder;
Preferences.Set("DefaultExportFolder", folder); // also persist
if (ZoomChanged)
{
Preferences.Set("TableColumnZoom", SS.I.TableColumnZoom);
Preferences.Set("GraphicsColumnZoom", SS.I.GraphicsColumnZoom);
}
SS.I.ScrollGridByPixel = ScrollGridByPixel.Checked; // set selected value
if (SS.I.ScrollGridByPixel != InitialScrollGridByPixel)
{
Preferences.Set("ScrollGridByPixel", SS.I.ScrollGridByPixel);
}
SaveLookAndFeel();
if (FindRelatedCpdsInQuickSearch.Checked != SS.I.FindRelatedCpdsInQuickSearch)
{
SS.I.FindRelatedCpdsInQuickSearch = !SS.I.FindRelatedCpdsInQuickSearch;
Preferences.Set("FindRelatedCpdsInQuickSearch", SS.I.FindRelatedCpdsInQuickSearch);
}
if (RestoreWindowsAtStartup.Checked != SS.I.RestoreWindowsAtStartup)
{
SS.I.RestoreWindowsAtStartup = !SS.I.RestoreWindowsAtStartup;
Preferences.Set("RestoreWindowsAtStartup", SS.I.RestoreWindowsAtStartup);
}
Hide(); // must explicitly hide since closing event is cancelled
}
/// <summary>
/// Set value for a string parameter
/// </summary>
/// <param name="parameter"></param>
/// <param name="value"></param>
public static void Set(
string parameter,
string value)
{
string key = parameter.ToUpper();
PrefDict[key] = value; // update local dict
UserObjectDao.SetUserParameter(SS.I.UserName, parameter, value);
}
/// <summary>
/// Grant a privilege to a user
/// </summary>
/// <param name="userName"></param>
/// <param name="privilege"></param>
/// <returns></returns>
public static void GrantPrivilege(
string userName,
string privilege)
{
if (!PrivilegesMx.IsValidPrivilegeName(privilege))
{
throw new Exception("Not a valid privilege");
}
UserObjectDao.SetUserParameter(userName, "Privilege" + Lex.CapitalizeFirstLetters(privilege), "True"); // authorize by default
return;
}
/// <summary>
/// Set user parameter enabling/disabling display of news
/// </summary>
/// <param name="enabled"></param>
public static void SetShowNewsEnabled(
bool enabled)
{
if (enabled)
{
UserObjectDao.SetUserParameter(SS.I.UserName, "ShowNews", SS.I.CurrentDate);
}
else
{
UserObjectDao.SetUserParameter(SS.I.UserName, "ShowNews", "false");
}
}
DialogResult ShowInstance(QueryColumn qc)
{
new JupyterGuiConverter().ConvertFormOrUserControl(Instance);
MetaColumn mc = qc.MetaColumn;
HorizontalAlignmentEx ha = qc.ActiveHorizontalAlignment;
SetHorizontalAlignment(qc.ActiveHorizontalAlignment);
SetVerticalAlignment(qc.ActiveVerticalAlignment);
ApplyToAllColumns.Checked = false;
SetAsDefault.Checked = false;
DialogResult dr = ShowDialog(SessionManager.ActiveForm);
if (dr == DialogResult.Cancel)
{
return(dr);
}
qc.HorizontalAlignment = GetHorizontalAlignment();
qc.VerticalAlignment = GetVerticalAlignment();
if (ApplyToAllColumns.Checked && qc.QueryTable != null &&
qc.QueryTable.Query != null)
{ // apply to all columns
foreach (QueryTable qt0 in qc.QueryTable.Query.Tables)
{
foreach (QueryColumn qc0 in qt0.QueryColumns)
{
qc0.HorizontalAlignment = qc.HorizontalAlignment;
qc0.VerticalAlignment = qc.VerticalAlignment;
}
}
}
if (SetAsDefault.Checked)
{ // set as user default
MetaColumn.SessionDefaultHAlignment = qc.ActiveHorizontalAlignment;
UserObjectDao.SetUserParameter(SS.I.UserName, "DefaultHorizontalAlignment", MetaColumn.SessionDefaultHAlignment.ToString());
MetaColumn.SessionDefaultVAlignment = qc.ActiveVerticalAlignment;
UserObjectDao.SetUserParameter(SS.I.UserName, "DefaultVerticalAlignment", MetaColumn.SessionDefaultVAlignment.ToString());
}
return(dr);
}
/// <summary>
/// Revoke a privilege from a user
/// </summary>
/// <param name="userName"></param>
/// <param name="privilege"></param>
/// <returns></returns>
public static void RevokePrivilege(
string userName,
string privilege)
{
if (!HasPrivilege(userName, privilege))
{
throw new Exception("User doesn't have privilege");
}
if (!PrivilegesMx.IsValidPrivilegeName(privilege))
{
throw new Exception("Not a valid privilege");
}
UserObjectDao.SetUserParameter(userName, "Privilege" + privilege, "False");
return;
}
/// <summary>
/// Display any new news if requested
/// </summary>
public static bool ShowNewNews()
{
try
{
string lastNews = Preferences.Get("ShowNews");
if (lastNews == "false")
{
return(false); // user doesn't want to see news
}
if (ClientState.IsDeveloper)
{
return(false); // debug
}
string fileName = ServicesIniFile.Read("NewsFile");
if (fileName == "")
{
throw new Exception("NewsFile not defined");
}
DateTime dt = File.GetLastWriteTime(fileName);
string fileDate = String.Format("{0,4:0000}{1,2:00}{2,2:00}", dt.Year, dt.Month, dt.Day);
if (String.Compare(fileDate, lastNews) > 0 || lastNews == "")
{
ShowNews(); // show the news
UserObjectDao.SetUserParameter(SS.I.UserName, "ShowNews", fileDate);
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
//if (ClientState.IsDeveloper)
// MessageBoxMx.ShowError(ex.Message);
return(false);
}
}
public DialogResult ShowDialog2(MetaTable cMt, UserObject uo, bool userDatabase)
{
MetaColumn mc;
bool headerLineChecked;
QualifiedNumber qn;
string tok;
UserDataImportParms importParms = cMt.ImportParms;
if (importParms == null)
{
importParms = new UserDataImportParms();
cMt.ImportParms = importParms;
}
else // setup for existing
{
FileName.Text = importParms.FileName;
HeaderLine.Checked = importParms.FirstLineHeaders;
DeleteExistingData.Checked = importParms.DeleteExisting;
ImportInBackground.Checked = importParms.ImportInBackground;
CheckForFileUpdates.Checked = importParms.CheckForFileUpdates;
TabDelim.Checked = (importParms.Delim == '\t');
CommaDelim.Checked = (importParms.Delim == ',');
SemiDelim.Checked = (importParms.Delim == ';');
SpaceDelim.Checked = (importParms.Delim == ' ');
}
if (userDatabase) // setup for db import
{
Text = "Import Database from File";
FileFilter =
"Smiles, CSV (Comma delimited) (*.smi; *.csv)|*.smi; *.csv|All files (*.*)|*.*";
DefaultExt = "smi";
}
else // setup for anotation import
{
Text = "Import annotation table data from a text file";
FileFilter =
"CSV (Comma delimited)(*.csv)|*.csv|Text(*.txt)|*.txt|All files (*.*)|*.*";
DefaultExt = "csv";
}
GetInput:
DialogResult dr = ShowDialog(SessionManager.ActiveForm);
if (dr == DialogResult.Cancel)
{
return(dr);
}
importParms.FileName = FileName.Text;
importParms.ClientFileModified = FileUtil.GetFileLastWriteTime(importParms.FileName);
if (CommaDelim.Checked)
{
importParms.Delim = ',';
}
else if (TabDelim.Checked)
{
importParms.Delim = '\t';
}
else if (SemiDelim.Checked)
{
importParms.Delim = ';';
}
else if (SpaceDelim.Checked)
{
importParms.Delim = ' ';
}
importParms.MultDelimsAsSingle = MultDelimsAsSingle.Checked;
tok = TextQualifier.Text;
if (tok == "\"")
{
importParms.TextQualifier = '\"';
}
else if (tok == "\'")
{
importParms.TextQualifier = '\'';
}
importParms.FirstLineHeaders = HeaderLine.Checked;
UserObjectDao.SetUserParameter( // save setting for next time
SS.I.UserName, "AnnotationHeaderLineChecked", importParms.FirstLineHeaders.ToString());
importParms.DeleteExisting = DeleteExistingData.Checked;
importParms.DeleteDataOnly = false;
if (importParms.DeleteExisting && uo.Id > 0)
{
string userDataTypeLabel = userDatabase ? "database" : "annotation table";
int dri = MessageBoxMx.ShowWithCustomButtons(
"Are you sure you want to delete any existing data for this " + userDataTypeLabel + "?", UmlautMobius.String,
"Yes", "Keep Cols.", "No", null, MessageBoxIcon.Question);
if (dri == 1)
{
} // yes
else if (dri == 2)
{
importParms.DeleteDataOnly = true;
}
else
{
goto GetInput;
}
}
importParms.ImportInBackground = ImportInBackground.Checked;
importParms.CheckForFileUpdates = CheckForFileUpdates.Checked;
DialogResult = DialogResult.OK;
return(DialogResult);
}
/// <summary>
/// Sync the Mobius CorpMoltable replicate used to retrieve Smiles
/// Syntax: UpdateCorpDbMoltableMx [ ByDateRange | ByCorpIdRange | LoadMissing | <singleCorpId>]
/// </summary>
/// <returns></returns>
public static string UpdateCorpDbMoltableMx(
string args)
{
DateTime moleculeDateTime = DateTime.MinValue;
double mw;
string msg = "", sql = "", maxCorpIdSql, mf = "", chime = "", smiles = "", checkPointDate = "", helm = "", sequence = "", svg = "";
object[][] pva = null;
int pvaCount = 0, CorpId, lowCorpId = 0, highCorpId = 0, srcMaxCorpId = 0;
int SelectChunkSize = 20; // small chunks
int InsertBufferSize = 10;
//int SelectChunkSize = 100000; // big chunks
//int InsertBufferSize = 1000;
// Select data from corp_moltable by CorpId range
const string SelectByCorpIdRange = @"
SELECT
m.corp_nbr,
chime(m.ctab),
m.molformula,
m.molweight,
null molsmiles,
s.helm_txt,
s.sequence_txt,
m.molecule_date
FROM
corp_owner.corp_moltable m,
corp_owner.corp_substance s
where
m.corp_nbr > 0
and s.corp_nbr = m.corp_nbr
and (s.status_code is null or s.status_code = 'A')
ORDER BY corp_nbr";
// Select data from corp_moltable by date range comparing to corp_moltable_mx
const string SelectByDateRange = @"
select
m.corp_nbr,
chime(m.ctab),
m.molformula,
m.molweight,
null molsmiles,
s.helm_txt,
s.sequence_txt,
m.molecule_date,
m2.molecule_date
from
corp_owner.corp_moltable m,
corp_owner.corp_substance s,
corp_moltable_mx m2
where
m.molecule_date > to_date('1-jan-1900 000000','DD-MON-YYYY HH24MISS')
and s.corp_nbr = M.CORP_NBR
and (s.status_code is null or s.status_code = 'A')
and m2.corp_nbr (+) = m.corp_nbr
and m2.molecule_date (+) != m.molecule_date
order by m.molecule_date" ;
// Select for missing smiles strings, ex: Update CorpDbMoltableMx LoadMissing mx.molecule_date > '1-jan-2014'
const string SelectMissingSmilesFix = @"
select /* check for CorpIds in corp_moltable not in corp_moltable_mx */
corp_nbr,
chime(ctab),
molformula,
molweight,
null molsmiles,
helm_txt,
sequence_txt,
molecule_date
from
(
select
m.*,
s.helm_txt,
s.sequence_txt,
mx.molsmiles molsmiles_mx
from
corp_owner.corp_moltable m,
corp_owner.corp_substance s,
corp_moltable_mx mx
where
s.corp_nbr = M.CORP_NBR
and (s.status_code is null or s.status_code = 'A')
and mx.corp_nbr (+) = m.corp_nbr
and 1=1 /* condition to substitute */
) m
where molsmiles_mx is null /* extra condition */
order by corp_nbr" ;
// Insert missing helm info
const string SelectMissingHelmFix = @"
select /* check for CorpIds in corp_moltable not in corp_moltable_mx */
corp_nbr,
chime(ctab),
molformula,
molweight,
null molsmiles,
helm_txt,
sequence_txt,
molecule_date
from
(
select
m.*,
s.helm_txt,
s.sequence_txt,
mx.molsmiles molsmiles_mx
from
corp_owner.corp_moltable m,
corp_owner.corp_substance s,
corp_moltable_mx mx
where
s.corp_nbr = M.CORP_NBR
and (s.status_code is null or s.status_code = 'A')
and mx.corp_nbr (+) = m.corp_nbr
and 1=1 /* condition to substitute */
) m
where length(helm_txt) > 0 /* extra condition */
order by corp_nbr" ;
// Secondary "large" structure table (~5k mols)
const string SelectLargeMols = @"
select
corp_nbr,
to_clob(molstructure),
to_clob(molformula),
molweight,
molsmiles,
null helm_txt,
null sequence_txt,
molecule_date
from
(select
corp_srl_nbr corp_nbr,
'CompoundId=' || corp_srl_nbr molstructure,
null ctab,
mlclr_frml_txt molformula,
mlclr_wgt molweight,
null molsmiles,
null molecule_date
from rdm_owner.rdm_sbstnc
where rdw_src_cd = 'LRG'" ;
// Insert statement
const string InsertSql = @"
insert into mbs_owner.corp_moltable_mx (
corp_nbr,
molstructure,
molformula,
molweight,
molsmiles,
molecule_date)
values (:0, :1, :2, :3, :4, :5)" ;
// Build select sql
bool byDateRange = false, byCorpIdRange = false, missingFix = true, deleteExisting = true;
string missingFixCriteria = "";
if (Lex.IsUndefined(args) || Lex.Eq(args, "ByDateRange"))
{
byDateRange = true;
}
else if (Lex.Eq(args, "ByCorpIdRange"))
{
byCorpIdRange = true;
Progress.Show("Getting range of CorpIds to insert...");
maxCorpIdSql = "select max(corp_nbr) from corp_owner.corp_moltable"; // get highest CorpId in source db
srcMaxCorpId = SelectSingleValueDao.SelectInt(maxCorpIdSql);
if (srcMaxCorpId < 0)
{
srcMaxCorpId = 0;
}
maxCorpIdSql = "select max(corp_nbr) from mbs_owner.corp_moltable_mx"; // get highest CorpId in dest db
highCorpId = SelectSingleValueDao.SelectInt(maxCorpIdSql);
if (highCorpId < 0)
{
highCorpId = 0;
}
}
else if (Lex.StartsWith(args, "LoadMissing"))
{
missingFix = true;
if (args.Contains(" "))
{
missingFixCriteria = args.Substring(10).Trim();
}
}
else if (int.TryParse(args, out srcMaxCorpId)) // single CorpId
{
byCorpIdRange = true;
highCorpId = srcMaxCorpId - 1; // say 1 less is the max we have
}
else
{
return("Syntax: UpdateCorpDbMoltableMx [ ByDateRange | ByCorpIdRange | LoadMissing | <singleCorpId>]");
}
Log("UpdateCorpDbMoltableMx started: " + args);
int readCount = 0, insCount = 0, insertCount = 0, updateCount = 0, undefinedStructures = 0, smilesSuccess = 0, smilesFails = 0, helmStructures = 0;
List <string> CorpIdList = new List <string>();
for (int chunk = 1; ; chunk++) // loop over chunks
{
if (byDateRange) // single chunk
{
if (chunk > 1)
{
break; // break 2nd time through
}
checkPointDate = UserObjectDao.GetUserParameter("MOBIUS", "UpdateCorpDbMoltableMxCheckpointDate", "01-sep-2013 000000");
//UserObjectDao.SetUserParameter("MOBIUS", "UpdateCorpDbMoltableMxCheckpointDate", checkPointDate);
sql = Lex.Replace(SelectByDateRange, "1-jan-1900 000000", checkPointDate);
msg = "Reading where date >= " + checkPointDate;
}
else if (byCorpIdRange) // by CorpId range
{
if (highCorpId >= srcMaxCorpId)
{
break; // done
}
lowCorpId = highCorpId + 1; // start of next chunk
highCorpId = lowCorpId + SelectChunkSize;
if (highCorpId >= srcMaxCorpId)
{
highCorpId = srcMaxCorpId;
}
sql = Lex.Replace(SelectByCorpIdRange, "corp_nbr > 0", "corp_nbr between " + lowCorpId + " and " + highCorpId);
msg = "Reading: " + lowCorpId + " to " + highCorpId + ", Reads: " + readCount + ", Inserts: " + insertCount;
}
else if (missingFix)
{
if (chunk > 1)
{
break; // break 2nd time through
}
sql = SelectMissingHelmFix;
if (Lex.IsDefined(missingFixCriteria)) // substitute any criteria
{
sql = Lex.Replace(sql, "1=1", missingFixCriteria);
}
msg = "Fixing missing data";
}
Progress.Show(msg);
DbCommandMx readCmd = new DbCommandMx();
readCmd.MxConn = DbConnectionMx.GetConnection("prd123");
readCmd.PrepareUsingDefinedConnection(sql, null);
DbDataReader rdr = readCmd.ExecuteReader();
DbCommandMx insertCmd = new DbCommandMx();
OracleDbType[] pta = new OracleDbType[6];
pta[0] = OracleDbType.Int32; // corp_nbr
pta[1] = OracleDbType.Clob; // molstructure
pta[2] = OracleDbType.Clob; // molformula
pta[3] = OracleDbType.Double; // molweight
pta[4] = OracleDbType.Clob; // smiles
pta[5] = OracleDbType.Date; // molecule_date
insertCmd.Prepare(InsertSql, pta);
insertCmd.BeginTransaction(); // be sure we have a transaction going
pva = DbCommandMx.NewObjectArrayArray(6, InsertBufferSize); // alloc insert row array
object[] vo = new object[6];
while (true)
{
bool readOk = rdr.Read();
if (readOk)
{
rdr.GetValues(vo);
CorpId = readCmd.GetInt(0); // corp_nbr
vo[0] = CorpId;
CorpIdList.Add(CorpId.ToString());
if (!readCmd.IsNull(1)) // molstructure
{
chime = readCmd.GetClob(1);
chime = OracleMx.ClearStringIfExceedsMaxStringSize(chime);
vo[1] = chime;
}
else
{
chime = "";
}
if (!readCmd.IsNull(2)) // molformula
{
mf = readCmd.GetClob(2);
mf = OracleMx.ClearStringIfExceedsMaxStringSize(mf);
vo[2] = mf;
}
if (!readCmd.IsNull(3)) // molweight
{
mw = readCmd.GetDouble(3);
vo[3] = mw;
}
if (Lex.IsDefined(chime)) // molsmiles - calculate from chime string
{
MoleculeMx cs = new MoleculeMx(MoleculeFormat.Chime, chime);
if (cs.AtomCount > 1) // need more than one atom
{
MoleculeMx cs2 = cs.ConvertTo(MoleculeFormat.Smiles);
smiles = cs2.GetSmilesString();
if (Lex.IsDefined(smiles))
{
smilesSuccess++;
}
else
{
Log("Smiles conversion failure for CorpId: " + CorpId);
smilesFails++;
}
smiles = OracleMx.ClearStringIfExceedsMaxStringSize(smiles);
vo[4] = smiles;
}
else
{
undefinedStructures++;
}
}
else
{
undefinedStructures++;
}
if (!readCmd.IsNull(5))
{
helm = readCmd.GetClob(5);
if (Lex.IsDefined(helm))
{
svg = HelmControl.GetSvg(helm);
vo[1] = SvgUtil.CompressSvgString(svg); // store compressed svg in molstructure column for now
helmStructures++;
}
}
if (!readCmd.IsNull(6))
{
sequence = readCmd.GetClob(6);
if (Lex.IsDefined(sequence))
{
// nothing yet
}
}
moleculeDateTime = DateTime.MinValue;
if (!readCmd.IsNull(7)) // molecule_date
{
moleculeDateTime = readCmd.GetDateTime(7);
vo[5] = moleculeDateTime;
}
for (int pi = 0; pi < 6; pi++) // invert for insert
{
pva[pi][pvaCount] = vo[pi];
}
if (Debug)
{
msg = String.Format("CorpId: {0}, mf: {1}, chime: {2}, smiles: {3}", CorpId.ToString(), mf.Length, chime.Length, smiles.Length);
Log(msg);
}
pvaCount++;
}
if (pvaCount >= InsertBufferSize || (!readOk && pvaCount > 0)) // write if buffer full or at end
{
try
{
if (deleteExisting)
{
int delCount = DoDeletes(CorpIdList);
updateCount += delCount; // count deletes as updates
insertCount -= delCount; // subtract from inserts
}
CorpIdList.Clear();
insCount = insertCmd.ExecuteArrayNonReader(pva, ref pvaCount);
insertCmd.Commit();
insertCmd.BeginTransaction();
insertCount += insCount;
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
if (byDateRange)
{
string checkPointDate2 = String.Format("{0:dd-MMM-yyyy HHmmss}", moleculeDateTime); // format date time that will work with oracle
UserObjectDao.SetUserParameter("MOBIUS", "UpdateCorpDbMoltableMxCheckpointDate", checkPointDate2);
msg = "Processing where date >= " + checkPointDate + ", Reads: " + readCount + ", Inserts: " + insertCount + ", Updates: " + updateCount;
}
else if (byCorpIdRange) // CorpId range
{
msg = "Processing: " + lowCorpId + " to " + highCorpId + ", Reads: " + readCount + ", Inserts: " + insertCount;
}
else if (missingFix)
{
msg = "Fixing missing smiles, Updates: " + updateCount;
}
msg += String.Format(", Undefined structures: {0} , Smiles failures: {1}, Helms: {2}", undefinedStructures, smilesFails, helmStructures);
Progress.Show(msg);
}
if (!readOk)
{
break;
}
readCount++;
}
readCmd.Dispose();
insertCmd.Dispose();
} // end for select chunk
msg = "UpdateCorpDbMoltableMx - Inserts: " + insertCount + ", Updates: " + updateCount;
msg += String.Format(", Undefined structures: {0} , Smiles failures: {1}, Helms: {2}", undefinedStructures, smilesFails, helmStructures);
Log(msg);
return(msg);
}