private void GetEmbeddedTasks(ref string sOriginalTaskIDs, ref string sErr) { //this is recursive, but the SQL excludes any tasks already in the list //so it should never spin out of control. string sSQL = ""; sSQL = "select ExtractValue(function_xml, '(//original_task_id)')" + " from task_step ts" + " join task t on ts.task_id = t.task_id" + " where t.original_task_id in (" + sOriginalTaskIDs + ")" + " and t.default_version = 1" + " and ts.function_name in ('subtask','run_task')" + " and ifnull(ExtractValue(function_xml, '(//original_task_id)'), '') <> ''" + " and ExtractValue(function_xml, '(//original_task_id)') not in (" + sOriginalTaskIDs + ")"; string sMoreIDs = ""; if (!dc.csvGetList(ref sMoreIDs, sSQL, ref sErr, true)) { throw new Exception(sErr); } if (sMoreIDs.Length > 0) { //this is recursive... if there are more id's, we need to check them as well sOriginalTaskIDs += "," + sMoreIDs; GetEmbeddedTasks(ref sOriginalTaskIDs, ref sErr); } }
public string wmDeleteTasks(string sDeleteArray) { dataAccess dc = new dataAccess(); acUI.acUI ui = new acUI.acUI(); string sSql = null; string sErr = ""; string sTaskNames = ""; if (sDeleteArray.Length < 36) return ""; sDeleteArray = ui.QuoteUp(sDeleteArray); //NOTE: right now this plows ALL versions. There is an enhancement to possibly 'retire' a task, or //only delete certain versions. try { // what about the instance tables????? // bugzilla 1290 Tasks that have history (task_instance table) can not be deleted // exclude them from the list and return a message noting the task(s) that could not be deleted // first we need a list of tasks that will not be deleted sSql = "select task_name from task t " + "where t.original_task_id in (" + sDeleteArray.ToString() + ") " + "and t.task_id in (select ti.task_id from tv_task_instance ti where ti.task_id = t.task_id)"; if (!dc.csvGetList(ref sTaskNames, sSql, ref sErr, true)) throw new Exception(sErr); // list of tasks that will be deleted //we have an array of 'original_task_id'. //we need an array or task_id //build one. sSql = "select t.task_id from task t " + "where t.original_task_id in (" + sDeleteArray.ToString() + ") " + "and t.task_id not in (select ti.task_id from tv_task_instance ti where ti.task_id = t.task_id)"; string sTaskIDs = ""; if (!dc.csvGetList(ref sTaskIDs, sSql, ref sErr, true)) throw new Exception(sErr); // if any tasks can be deleted if (sTaskIDs.Length > 1) { dataAccess.acTransaction oTrans = new dataAccess.acTransaction(ref sErr); //oTrans.Command.CommandText = "delete from task_asset_attribute where task_id in (" + sTaskIDs + ")"; //if (!oTrans.ExecUpdate(ref sErr)) // throw new Exception(sErr); oTrans.Command.CommandText = "delete from task_step_user_settings" + " where step_id in" + " (select step_id from task_step where task_id in (" + sTaskIDs + "))"; if (!oTrans.ExecUpdate(ref sErr)) throw new Exception(sErr); oTrans.Command.CommandText = "delete from task_step where task_id in (" + sTaskIDs + ")"; if (!oTrans.ExecUpdate(ref sErr)) throw new Exception(sErr); oTrans.Command.CommandText = "delete from task_codeblock where task_id in (" + sTaskIDs + ")"; if (!oTrans.ExecUpdate(ref sErr)) throw new Exception(sErr); oTrans.Command.CommandText = "delete from task where task_id in (" + sTaskIDs + ")"; if (!oTrans.ExecUpdate(ref sErr)) throw new Exception(sErr); oTrans.Commit(); ui.WriteObjectDeleteLog(Globals.acObjectTypes.Task, "Multiple", "Original Task IDs", sDeleteArray.ToString()); } } catch (Exception ex) { throw new Exception(ex.Message); } // if the sTaskNames contains any names, then send back a message that these were not deleted because of history records. if (sTaskNames.Length > 0) { return "Task(s) (" + sTaskNames + ") have history rows and could not be deleted."; } else { return sErr; } }
public static string DeleteAssets(string sDeleteArray) { dataAccess dc = new dataAccess(); acUI.acUI ui = new acUI.acUI(); string sSql = null; string sErr = ""; ArrayList arrList = new ArrayList(); arrList.AddRange(sDeleteArray.Split(',')); if (sDeleteArray.Length < 36) return ""; StringBuilder sbAssetIDString = new StringBuilder(); StringBuilder sbAssetsCantDelete = new StringBuilder(); foreach (string sAssetID in arrList) { if (sAssetID.Length == 36) { // what about the instance tables????? // bugzilla 1290 Assets that have history (task_instance table) can not be deleted // exclude them from the list and return a message noting the asset(s) that could not be deleted // check if this asset has any history rows. sSql = "select count(*) from tv_task_instance where asset_id = '" + sAssetID + "'"; int iHistory = 0; if (!dc.sqlGetSingleInteger(ref iHistory, sSql, ref sErr)) throw new Exception(sErr); // if there is no history add this to the delete list, // otherwise add the task id to the non delete list if (iHistory == 0) { sbAssetIDString.Append("'" + sAssetID + "',"); } else { sbAssetsCantDelete.Append("'" + sAssetID + "',"); }; } } // trim the trailing , if (sbAssetsCantDelete.ToString().Length > 2) { sbAssetsCantDelete.Remove(sbAssetsCantDelete.Length - 1, 1); }; if (sbAssetIDString.ToString().Length > 2) { // delete from these tables: // asset, asset_credential (if the credential is local). // trim the trailing , sbAssetIDString.Remove(sbAssetIDString.Length - 1, 1); try { dataAccess.acTransaction oTrans = new dataAccess.acTransaction(ref sErr); // delete asset_credential sSql = "delete from asset_credential" + " where shared_or_local = 1" + " and credential_id in (select credential_id from asset where asset_id in (" + sbAssetIDString.ToString() + "))"; oTrans.Command.CommandText = sSql; if (!oTrans.ExecUpdate(ref sErr)) throw new Exception(sErr); // delete asset sSql = "delete from asset where asset_id in (" + sbAssetIDString.ToString() + ")"; oTrans.Command.CommandText = sSql; if (!oTrans.ExecUpdate(ref sErr)) throw new Exception(sErr); oTrans.Commit(); // add security log ui.WriteObjectDeleteLog(Globals.acObjectTypes.Asset, sbAssetIDString.ToString(), "Batch Asset Delete", "Deleted Assets in batch mode"); } catch (Exception ex) { throw new Exception(ex.Message); } }; // if some did not get deleted return a message. if (sbAssetsCantDelete.Length > 2) { string sTaskNames = ""; sSql = "select asset_name from asset where asset_id in (" + sbAssetsCantDelete.ToString() + ")"; if (!dc.csvGetList(ref sTaskNames, sSql, ref sErr, true)) throw new Exception(sErr); return "Asset deletion completed. Asset(s) (" + sTaskNames + ") could not be deleted because history rows exist."; } else { return sErr; } }
public static string DeleteAssets(string sDeleteArray) { dataAccess dc = new dataAccess(); acUI.acUI ui = new acUI.acUI(); string sSql = null; string sErr = ""; ArrayList arrList = new ArrayList(); arrList.AddRange(sDeleteArray.Split(',')); if (sDeleteArray.Length < 36) { return(""); } StringBuilder sbAssetIDString = new StringBuilder(); StringBuilder sbAssetsCantDelete = new StringBuilder(); foreach (string sAssetID in arrList) { if (sAssetID.Length == 36) { // what about the instance tables????? // bugzilla 1290 Assets that have history (task_instance table) can not be deleted // exclude them from the list and return a message noting the asset(s) that could not be deleted // check if this asset has any history rows. sSql = "select count(*) from tv_task_instance where asset_id = '" + sAssetID + "'"; int iHistory = 0; if (!dc.sqlGetSingleInteger(ref iHistory, sSql, ref sErr)) { throw new Exception(sErr); } // if there is no history add this to the delete list, // otherwise add the task id to the non delete list if (iHistory == 0) { sbAssetIDString.Append("'" + sAssetID + "',"); } else { sbAssetsCantDelete.Append("'" + sAssetID + "',"); }; } } // trim the trailing , if (sbAssetsCantDelete.ToString().Length > 2) { sbAssetsCantDelete.Remove(sbAssetsCantDelete.Length - 1, 1); } ; if (sbAssetIDString.ToString().Length > 2) { // delete from these tables: // asset, asset_credential (if the credential is local). // trim the trailing , sbAssetIDString.Remove(sbAssetIDString.Length - 1, 1); try { dataAccess.acTransaction oTrans = new dataAccess.acTransaction(ref sErr); // delete asset_credential sSql = "delete from asset_credential" + " where shared_or_local = 1" + " and credential_id in (select credential_id from asset where asset_id in (" + sbAssetIDString.ToString() + "))"; oTrans.Command.CommandText = sSql; if (!oTrans.ExecUpdate(ref sErr)) { throw new Exception(sErr); } // delete asset sSql = "delete from asset where asset_id in (" + sbAssetIDString.ToString() + ")"; oTrans.Command.CommandText = sSql; if (!oTrans.ExecUpdate(ref sErr)) { throw new Exception(sErr); } oTrans.Commit(); // add security log ui.WriteObjectDeleteLog(Globals.acObjectTypes.Asset, sbAssetIDString.ToString(), "Batch Asset Delete", "Deleted Assets in batch mode"); } catch (Exception ex) { throw new Exception(ex.Message); } } ; // if some did not get deleted return a message. if (sbAssetsCantDelete.Length > 2) { string sTaskNames = ""; sSql = "select asset_name from asset where asset_id in (" + sbAssetsCantDelete.ToString() + ")"; if (!dc.csvGetList(ref sTaskNames, sSql, ref sErr, true)) { throw new Exception(sErr); } return("Asset deletion completed. Asset(s) (" + sTaskNames + ") could not be deleted because history rows exist."); } else { return(sErr); } }