/// <summary>
/// Read the initial version of the base version of the specified Spotfire SQL
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public static string ReadSpotfireSql(
string name)
{
string spotfireSql = SpotfireDao.ReadSqlStatement(name, 0);
return(spotfireSql);
}
/// <summary>
/// Read the initial version of the specified version of the named Spotfire SQL stmt
/// </summary>
/// <param name="name"></param>
/// <param name="version"></param>
/// <returns></returns>
public static string ReadSpotfireSql(
string name,
int version)
{
string spotfireSql = SpotfireDao.ReadSqlStatement(name, version);
return(spotfireSql);
}
/// <summary>
/// Save a list of keys for later inclusion in SQL statements
/// </summary>
/// <param name="listType">CIDLIST, ASSAYLIST</param>
/// <param name="keyList"></param>
/// <returns></returns>
public static string SaveSpotfireKeyList(
string keyColName,
string listType,
string keyList)
{
string subList;
string listName = listType + "_"; // start building name
int v1 = -2; // modify name with next version
while (true) // break into 4000 char chunks for later use by Oracle function
{
if (keyList.Length < 4000)
{
subList = keyList;
keyList = "";
}
else
{
subList = keyList.Substring(0, 4000);
int i1 = subList.LastIndexOf(",");
subList = subList.Substring(0, i1);
keyList = keyList.Substring(i1 + 1);
}
int v2 = SpotfireDao.InsertSpotfireSql(listName, v1, null, keyColName, subList, Security.UserName);
if (v1 == -2) // first time?
{
listName += v2; // all sublists will have this list name
v1 = -1;
}
if (keyList.Length == 0)
{
break;
}
}
return(listName);
}
/// <summary>
/// Build and save SQL for use in Spotfire information link
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public static int SaveSpotfireSql(
string sqlStmtName,
Query query)
{
DbConnectionMx conn = null;
string qtSql = "", sql, expr;
string keys = null;
string keyColName = "";
string t1KeyColExpr = "", keyColExpr;
string rootDataSource = "DEV857";
string rootSchema = "MBS_OWNER";
//if (query.Tables.Count != 1) throw new Exception("Can only save Spotfire Sql for single-table queries");
Query q = query.Clone();
// Clean up query before generating SQL
q.KeyCriteria = q.KeyCriteriaDisplay = "";
List <QueryTable> qtToRemove = new List <QueryTable>();
foreach (QueryTable qt0 in q.Tables)
{
if (qt0.SelectedCount <= 1 ||
!qt0.MetaTable.RetrievesDataFromQueryEngine)
{
qtToRemove.Add(qt0);
continue;
}
foreach (QueryColumn qc0 in qt0.QueryColumns) // clear any criteria
{
if (Lex.IsDefined(qc0.Criteria))
{
qc0.Criteria = qc0.CriteriaDisplay = "";
}
}
}
foreach (QueryTable qt0 in qtToRemove)
{
q.RemoveQueryTable(qt0);
}
string selectList = ""; // top-level list of selected columns
string fromList = ""; // sql for each QueryTable
string joinCriteria = ""; // join criteria between QueryTables
int remapCount = 0;
q.AssignUndefinedAliases();
q.MarkDuplicateNamesAndLabels();
for (int ti = 0; ti < q.Tables.Count; ti++)
{
QueryTable qt = q.Tables[ti];
if (ti == 0)
{
keyColName = qt.MetaTable.KeyMetaColumn.Name;
}
QueryEngine qe = new QueryEngine();
ExecuteQueryParms eqp = new ExecuteQueryParms(qe, qt);
eqp.ReturnQNsInFullDetail = false;
qtSql = qe.BuildSqlForSingleTable(eqp);
qtSql = Lex.Replace(qtSql, "/*+ hint */ ", "");
conn = DbConnectionMx.MapSqlToConnection(ref qtSql, rootDataSource, rootSchema); // convert SQL to use dblinks from root source/schema
if (conn == null)
{
throw new Exception("Connection not found for: " + rootDataSource);
}
// Recast numeric cols that are integers as integers for Spotfire
List <DbColumnMetadata> cmdList = OracleMx.GetColumnMetadataFromSql(qtSql, conn);
string qtSelectList = "";
remapCount = 0; // number of cols remapped
int sci = -1;
foreach (QueryColumn qc in qt.QueryColumns)
{
if (!qc.Selected)
{
continue;
}
sci++; // synch with cmdList
MetaColumn mc = qc.MetaColumn;
string mcName = mc.Name;
if (q.Tables.Count > 1) // if more than one table qualify by table name
{
mcName = qt.Alias + "." + mcName;
}
string colName = qc.UniqueName;
//if (mc.Name == "CORP_SBMSN_ID") mc = mc; // debug
//if (mc.DataType == MetaColumnType.CompoundId) mc = mc; // debug
if (mc.IsNumeric && (mc.DataType == MetaColumnType.Integer || mc.DataType == MetaColumnType.CompoundId))
{
DbColumnMetadata md = cmdList[sci];
expr = "cast (" + mcName + " as integer) " + colName; // integer same as number(22,0)--- " as number(38, 0)) " + expr;
}
else if (mcName != colName)
{
expr = mcName + " " + colName;
remapCount++;
}
else
{
expr = mc.Name;
}
if (qtSelectList != "")
{
qtSelectList += ", ";
}
qtSelectList += expr;
}
if (selectList != "")
{
selectList += ", ";
}
selectList += qtSelectList;
if (fromList != "")
{
fromList += ", ";
}
fromList += "(" + qtSql + ") " + qt.Alias;
keyColExpr = qt.Alias + "." + qt.KeyQueryColumn.MetaColumn.Name;
if (ti == 0)
{
t1KeyColExpr = keyColExpr;
}
else
{
if (joinCriteria != "")
{
joinCriteria += " and ";
}
joinCriteria += keyColExpr + " (+) = " + t1KeyColExpr;
}
}
selectList += " "; // be sure last col name in list is delimited with a space
if (q.Tables.Count == 1 && remapCount == 0)
{
sql = qtSql; // simple single table with no remapping of cols
}
else // combine list of elements
{
sql =
"select " + selectList +
" from " + fromList;
if (joinCriteria != "")
{
sql += " where " + joinCriteria;
}
sql = "select * from (" + sql + ")"; // encapsulate the SQL
}
int v2 = SpotfireDao.InsertSpotfireSql(sqlStmtName, 0, sql, keyColName, null, Security.UserName);
return(v2);
}
