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