public static void DumpListForShowObjid(int showObjid)
{
#region Sql statement to get the list of a show by the show's objid
const string listByShowObjidSql = @"
SELECT objid FROM table_hgbst_lst WHERE hgbst_lst2hgbst_show =
(
SELECT show5.objid FROM table_hgbst_show show5, table_hgbst_show show5A
WHERE show5A.objid IN
(
SELECT show4.objid FROM table_hgbst_show show4, table_hgbst_show show4A
WHERE show4A.objid IN
(
SELECT show3.objid FROM table_hgbst_show show3, table_hgbst_show show3A
WHERE show3A.objid IN
(
SELECT show2.objid FROM table_hgbst_show show2, table_hgbst_show show2A
WHERE show2A.objid IN
(
SELECT show1.objid FROM table_hgbst_show show1
WHERE show1.objid = {0}
)
AND
(
(show2A.chld_prnt2hgbst_show IS NULL AND show2.objid = show2A.objid)
OR
(show2.objid = show2A.chld_prnt2hgbst_show)
)
)
AND
(
(show3A.chld_prnt2hgbst_show IS NULL AND show3.objid = show3A.objid)
OR
(show3.objid = show3A.chld_prnt2hgbst_show)
)
)
AND
(
(show4A.chld_prnt2hgbst_show IS NULL AND show4.objid = show4A.objid)
OR
(show4.objid = show4A.chld_prnt2hgbst_show)
)
)
AND
(
(show5A.chld_prnt2hgbst_show IS NULL AND show5.objid = show5A.objid)
OR
(show5.objid = show5A.chld_prnt2hgbst_show)
)
)";
var sqlHelper = new SqlHelper(listByShowObjidSql);
sqlHelper.Parameters.Add("showObjid", showObjid);
var result = sqlHelper.ExecuteScalar();
if (result == DBNull.Value || result == null)
{
// No results returned. This is usually caused by:
// 1.) The show does not exist
// 2.) The show is in a show hierarchy that is disconnected
// 3.) There is some corrupt data up the hierarchy
// Check to see if the show doesn't exists first (the most common case)
sqlHelper = new SqlHelper("SELECT objid FROM table_hgbst_show WHERE objid = {0}");
sqlHelper.Parameters.Add("showObjid", showObjid);
result = sqlHelper.ExecuteScalar();
if (result == DBNull.Value || result == null)
{
Console.WriteLine("No HGBST show found with objid '{0}'.", showObjid);
return;
}
Console.WriteLine("Unable to retrieve LIST tree for show with objid '{0}'.", showObjid);
return;
}
var listObjid = Convert.ToInt32(sqlHelper.ExecuteScalar());
DumpListForListObjid(listObjid);
#endregion
}
private static void CopyAndReparentElement(UpdateQueryBatch batch, int elmObjid, int childShowObjid,
int newParentShowObjid)
{
log.LogDebug("CopyAndReparentElement called. Elmenet: {0}, Child Show: {1}, New Parent Show: {2}",
elmObjid, childShowObjid, newParentShowObjid);
// Get table ID's for hgbst_elm and hgbst_show
var sqlHelper = new SqlHelper(batch.Provider)
{
Transaction = batch.Transaction,
CommandText = @"SELECT type_id, type_name FROM adp_tbl_name_map WHERE type_name = 'hgbst_elm' OR type_name = 'hgbst_show'"
};
var hgbstElmTableID = 0;
var hgbstShowTableID = 0;
using (var dataReader = sqlHelper.ExecuteReader())
{
while (dataReader.Read())
{
if (String.Compare(Convert.ToString(dataReader["type_name"]), "hgbst_elm", true) == 0)
{
hgbstElmTableID = Convert.ToInt32(dataReader["type_id"]);
}
else if (String.Compare(Convert.ToString(dataReader["type_name"]), "hgbst_show", true) == 0)
{
hgbstShowTableID = Convert.ToInt32(dataReader["type_id"]);
}
}
}
sqlHelper = new SqlHelper(batch.Provider)
{
Transaction = batch.Transaction,
CommandText = "SELECT site_id FROM adp_db_header"
};
var siteId = Convert.ToInt32(sqlHelper.ExecuteScalar());
var objidBase = siteId*Convert.ToInt32(Math.Pow(2, 28));
//this.objidBase = 0;
// Duplicate the element
var dupeElmObjid = DuplicateElement(batch, elmObjid, newParentShowObjid, objidBase, hgbstElmTableID);
log.LogDebug("Duplicated element. New Objid: {0}", dupeElmObjid);
// Duplicate the child show and link it to the new element
var dupeChildShowObjid = DuplicateShow(batch, childShowObjid, dupeElmObjid, newParentShowObjid, objidBase, hgbstShowTableID);
log.LogDebug("Duplicated child show. New Objid: {0}", dupeChildShowObjid);
// Get the whole child hierarchy for this element starting from its childShowObjid and duplicate it
log.LogDebug("Duplicating child hierarchy...");
DuplicateChildHierarchy(batch, elmObjid, childShowObjid, dupeChildShowObjid, objidBase, hgbstElmTableID,
hgbstShowTableID);
// Unlink the old elm from the new parent show (if it was linked - a la the Best Buy double-linked problem)
log.LogDebug("Unlinking original element (if necessary) from new parent to complete the separation");
var commandParameters = new DataParameterCollection {{"elmObjid", elmObjid}, {"newParent", newParentShowObjid}};
batch.AddStatement(@"DELETE FROM mtm_hgbst_elm0_hgbst_show1 WHERE hgbst_elm2hgbst_show = {0} and hgbst_show2hgbst_elm = {1}", commandParameters);
}
public static void DumpListForElementObjid(int elmObjid)
{
#region Sql statement to get list for element (NOTE: Only supports 5-levels)
// NOTE WARN Hard-coded to 5-levels-or-less support
const string listByElementObjidSql = @"
SELECT objid FROM table_hgbst_lst WHERE hgbst_lst2hgbst_show =
(
SELECT show5.objid FROM table_hgbst_show show5, table_hgbst_show show5A
WHERE show5A.objid IN
(
SELECT show4.objid FROM table_hgbst_show show4, table_hgbst_show show4A
WHERE show4A.objid IN
(
SELECT show3.objid FROM table_hgbst_show show3, table_hgbst_show show3A
WHERE show3A.objid IN
(
SELECT show2.objid FROM table_hgbst_show show2, table_hgbst_show show2A
WHERE show2A.objid IN
(
SELECT show1.objid FROM table_hgbst_show show1, table_hgbst_show show1A
WHERE show1A.objid IN
(
SELECT parentShow.objid
FROM table_hgbst_show parentShow, mtm_hgbst_elm0_hgbst_show1 mtm1
WHERE
mtm1.hgbst_elm2hgbst_show = {0}
AND parentShow.objid = mtm1.hgbst_show2hgbst_elm
AND
(
parentShow.objid =
(
SELECT childShow.chld_prnt2hgbst_show
FROM table_hgbst_show childShow, mtm_hgbst_elm0_hgbst_show1 mtm2
WHERE childShow.objid = mtm2.hgbst_show2hgbst_elm
AND mtm2.hgbst_elm2hgbst_show = mtm1.hgbst_elm2hgbst_show
AND childShow.objid != parentShow.objid
)
OR
1 =
(
SELECT COUNT(*)
FROM mtm_hgbst_elm0_hgbst_show1 mtm3
WHERE mtm3.hgbst_elm2hgbst_show = mtm1.hgbst_elm2hgbst_show
)
OR
parentShow.chld_prnt2hgbst_show IS NULL
)
)
AND
(
(show1A.chld_prnt2hgbst_show IS NULL AND show1.objid = show1A.objid)
OR
(show1.objid = show1A.chld_prnt2hgbst_show)
)
)
AND
(
(show2A.chld_prnt2hgbst_show IS NULL AND show2.objid = show2A.objid)
OR
(show2.objid = show2A.chld_prnt2hgbst_show)
)
)
AND
(
(show3A.chld_prnt2hgbst_show IS NULL AND show3.objid = show3A.objid)
OR
(show3.objid = show3A.chld_prnt2hgbst_show)
)
)
AND
(
(show4A.chld_prnt2hgbst_show IS NULL AND show4.objid = show4A.objid)
OR
(show4.objid = show4A.chld_prnt2hgbst_show)
)
)
AND
(
(show5A.chld_prnt2hgbst_show IS NULL AND show5.objid = show5A.objid)
OR
(show5.objid = show5A.chld_prnt2hgbst_show)
)
)";
#endregion
var sqlHelper = new SqlHelper(listByElementObjidSql);
sqlHelper.Parameters.Add("elmobjid", elmObjid);
var result = sqlHelper.ExecuteScalar();
if (result == DBNull.Value || result == null)
{
// No results returned. This is usually caused by:
// 1.) The element does not exist
// 2.) The element is in a show hierarchy that is disconnected
// 3.) There is some corrupt data up the hierarchy
// Check to see if the element doesn't exists first (the most common case)
sqlHelper = new SqlHelper("SELECT objid FROM table_hgbst_elm WHERE objid = {0}");
sqlHelper.Parameters.Add("elmObjid", elmObjid);
result = sqlHelper.ExecuteScalar();
if (result == DBNull.Value || result == null)
{
Console.WriteLine("No HGBST element found with objid '{0}'.", elmObjid);
return;
}
Console.WriteLine("Unable to retrieve LIST tree for element with objid '{0}'.", elmObjid);
return;
}
var listObjid = Convert.ToInt32(sqlHelper.ExecuteScalar());
DumpListForListObjid(listObjid);
}