internal static string GetParentContentIDs(MGL.Security.ContentType contentType,
List <int> contentIDs, bool isSelectChildContentID)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT pc.ID ");
if (isSelectChildContentID)
{
sql.Append(", c.ID ");
}
sql.Append(" FROM ");
sql.Append(contentType.ContentSourceTable);
sql.Append(" t, ");
sql.Append(BaseSecurityOperations.tnContent);
sql.Append(" c, ");
sql.Append(BaseSecurityOperations.tnContent);
sql.Append(" pc ");
if (contentIDs != null && contentIDs.Count > 0)
{
string idList = "(";
bool first = true;
foreach (int id in contentIDs)
{
if (!first)
{
idList += ",";
}
idList += id.ToString();
first = false;
}
idList += ")";
sql.Append(" WHERE c.ID ");
sql.Append(" IN ");
sql.Append(idList);
}
sql.Append(" AND t.");
sql.Append(THEME_PARENT_ID_COL);
sql.Append(" <> -1 ");
sql.Append(" AND c.FeatureName = 'THEME' AND ");
sql.Append(" c.FeatureValue = t.dlt_ID AND ");
sql.Append(" t.Dlt_ID <> -1 AND ");
sql.Append(" pc.FeatureName = 'THEME' AND ");
sql.Append(" pc.FeatureValue = t." + THEME_PARENT_ID_COL);
sql.Append(" GROUP BY pc.ID");
if (isSelectChildContentID)
{
sql.Append(", c.ID");
}
sql.Append(";");
return(sql.ToString());
}
internal static string GetSelectGetThemeParentChildIDsSql(List <int> contentIDs,
MGL.Security.ContentType contentType)
{
bool isSelectChildContentID = true;
return(GetParentContentIDs(contentType, contentIDs, isSelectChildContentID));
}
internal static string GetSelectNewContentItemsFromSrcTblSql(MGL.Security.ContentType contentType)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT ");
sql.Append(contentType.ContentSrcIDCol + " as ContentItemID,");
if (contentType.TypeOfContent == SecureRequestContext.ContentType.THEME)
{
sql.Append("CONCAT(ifnull(" + contentType.ContentSrcParentNameCol + @", ''), CONVERT(IF(!ISNULL(" + contentType.ContentSrcParentNameCol + @"),' - ',''),CHAR), " + contentType.ContentSrcNameCol + @") as ContentItemName, '");
}
else
{
sql.Append(contentType.ContentSrcNameCol + " as ContentItemName, '");
}
sql.Append(contentType.TypeOfContent.ToString() + "' as ContentTypeName ");
sql.Append(" FROM ");
sql.Append(contentType.ContentSourceTable);
sql.Append(" WHERE " + contentType.ContentSrcIDCol + @" NOT IN (SELECT FeatureValue FROM " + BaseSecurityOperations.tnContent + " WHERE FeatureName='" + contentType.TypeOfContent.ToString() + "') ");
return(sql.ToString());
}
internal static string GetSelectContentItemsSQL(MGL.Security.ContentType contentType,
List <int> contentsIds)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT ");
string orderClause = " ORDER BY ";
string nameSelectCols = "";
if (contentType.ContentSrcParentNameCol != null && contentType.ContentSrcParentNameCol != "")
{
nameSelectCols = contentType.ContentSrcParentNameCol + "," + contentType.ContentSrcNameCol;
orderClause += contentType.ContentSrcParentNameCol + "," + contentType.ContentSrcNameCol;
}
else
{
orderClause += contentType.ContentSrcNameCol;
nameSelectCols = contentType.ContentSrcNameCol;
}
sql.Append(contentType.ContentSrcIDCol + ",");
if (contentType.TypeOfContent == SecureRequestContext.ContentType.THEME)
{
sql.Append(ContentQB.THEME_PARENT_ID_COL + ", ");
}
sql.Append(nameSelectCols);
sql.Append(" FROM ");
sql.Append(contentType.ContentSourceTable);
if (contentsIds != null && contentsIds.Count > 0)
{
string idList = "(";
bool first = true;
foreach (int id in contentsIds)
{
if (!first)
{
idList += ",";
}
idList += id.ToString();
first = false;
}
idList += ")";
sql.Append(" WHERE ");
sql.Append(contentType.ContentSrcIDCol + " IN ");
sql.Append(idList);
}
sql.Append(orderClause);
sql.Append(";");
return(sql.ToString());
}
internal static string GetInsertNewContentItemsFromSrcTblSql(string contentTable,
MGL.Security.ContentType contentType)
{
StringBuilder builder = new StringBuilder();
builder.Append("INSERT INTO ");
builder.Append(contentTable);
builder.Append(" (FeatureValue, Description, FeatureName) ");
string selectContentItemsSql = ContentQB.GetSelectNewContentItemsFromSrcTblSql(contentType);
builder.Append(selectContentItemsSql);
builder.Append(" GROUP BY " + contentType.ContentSrcIDCol);
builder.Append(";");
return(builder.ToString());
}
internal static string GetDeleteOldContentItemsFromSrcTblSql(string contentTable,
MGL.Security.ContentType contentType)
{
StringBuilder builder = new StringBuilder();
// e.g DELETE c.*
//FROM security_content c LEFT OUTER JOIN dl_themes s ON
//c.FeatureValue = s.dlt_ID
//WHERE c.FeatureName = 'THEME'
//AND s.dlt_ID IS NULL;
builder.Append("DELETE c.* FROM ");
builder.Append(contentTable);
builder.Append(" c LEFT OUTER JOIN ");
if (contentType.TypeOfContent != SecureRequestContext.ContentType.STAT_LAYER)
{
builder.Append(contentType.ContentSourceTable);
}
else
{ // The active layers will always be in dl_layers, even if the security_content_type
// specifices a different ContentSourceTable in order to display layers descriptions with their layer group name as a prefix
builder.Append("dl_layers");
}
builder.Append(" s ");
builder.Append(" ON c.FeatureValue = s.");
builder.Append(contentType.ContentSrcIDCol);
builder.Append(" WHERE c.FeatureName = '");
builder.Append(contentType.TypeOfContent.ToString());
builder.Append("' AND s.");
builder.Append(contentType.ContentSrcIDCol);
builder.Append(" IS NULL ");
builder.Append(";");
return(builder.ToString());
}
internal static string GetParentContentIDs(MGL.Security.ContentType contentType, List <int> contentIDs)
{
bool isSelectChildContentID = false;
return(GetParentContentIDs(contentType, contentIDs, isSelectChildContentID));
}
