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); }