private string CopyTask(int iMode, string sSourceTaskID, string sNewTaskName, string sNewTaskCode)
{
//iMode 0=new task, 1=new major version, 2=new minor version
dataAccess dc = new dataAccess();
acUI.acUI ui = new acUI.acUI();
string sErr = "";
string sSQL = "";
string sNewTaskID = ui.NewGUID();
int iIsDefault = 0;
string sTaskName = "";
double dVersion = 1.000;
double dMaxVer = 0.000;
string sOTID = "";
//do it all in a transaction
dataAccess.acTransaction oTrans = new dataAccess.acTransaction(ref sErr);
//figure out the new name and selected version
oTrans.Command.CommandText = "select task_name, version, original_task_id from task where task_id = '" + sSourceTaskID + "'";
DataRow dr = null;
if (!oTrans.ExecGetDataRow(ref dr, ref sErr))
throw new Exception("Unable to find task for ID [" + sSourceTaskID + "]." + sErr);
sTaskName = dr["task_name"].ToString();
dVersion = Convert.ToDouble(dr["version"]);
sOTID = dr["original_task_id"].ToString();
//figure out the new version
switch (iMode)
{
case 0:
sTaskName = sNewTaskName;
iIsDefault = 1;
dVersion = 1.000;
sOTID = sNewTaskID;
break;
case 1:
//gotta get the highest version
sSQL = "select max(version) from task where task_id = '" + sOTID + "'";
dc.sqlGetSingleDouble(ref dMaxVer, sSQL, ref sErr);
if (sErr != "")
{
oTrans.RollBack();
throw new Exception(sErr);
}
dVersion = dMaxVer + 1;
break;
case 2:
sSQL = "select max(version) from task where task_id = '" + sOTID + "'" +
" and cast(version as unsigned) = " + Convert.ToInt32(dVersion);
dc.sqlGetSingleDouble(ref dMaxVer, sSQL, ref sErr);
if (sErr != "")
{
oTrans.RollBack();
throw new Exception(sErr);
}
dVersion = dMaxVer + 0.001;
break;
default: //a iMode is required
throw new Exception("A mode required for this copy operation." + sErr);
}
//if we are versioning, AND there are not yet any 'Approved' versions,
//we set this new version to be the default
//(that way it's the one that you get taken to when you pick it from a list)
if (iMode > 0)
{
sSQL = "select case when count(*) = 0 then 1 else 0 end" +
" from task where original_task_id = '" + sOTID + "'" +
" and task_status = 'Approved'";
dc.sqlGetSingleInteger(ref iIsDefault, sSQL, ref sErr);
if (sErr != "")
{
oTrans.RollBack();
throw new Exception(sErr);
}
}
//start copying
oTrans.Command.CommandText = "create temporary table _copy_task" +
" select * from task where task_id = '" + sSourceTaskID + "'";
if (!oTrans.ExecUpdate(ref sErr))
throw new Exception(sErr);
//update the task_id
oTrans.Command.CommandText = "update _copy_task set" +
" task_id = '" + sNewTaskID + "'," +
" original_task_id = '" + sOTID + "'," +
" version = '" + dVersion + "'," +
" task_name = '" + sTaskName + "'," +
" default_version = " + iIsDefault.ToString() + "," +
" task_status = 'Development'," +
" created_dt = now()";
if (!oTrans.ExecUpdate(ref sErr))
throw new Exception(sErr);
//update the task_code if necessary
if (iMode == 0)
{
oTrans.Command.CommandText = "update _copy_task set task_code = '" + sNewTaskCode + "'";
if (!oTrans.ExecUpdate(ref sErr))
throw new Exception(sErr);
}
//codeblocks
oTrans.Command.CommandText = "create temporary table _copy_task_codeblock" +
" select '" + sNewTaskID + "' as task_id, codeblock_name" +
" from task_codeblock where task_id = '" + sSourceTaskID + "'";
if (!oTrans.ExecUpdate(ref sErr))
throw new Exception(sErr);
//USING TEMPORARY TABLES... need a place to hold step ids while we manipulate them
oTrans.Command.CommandText = "create temporary table _step_ids" +
" select distinct step_id, uuid() as newstep_id" +
" from task_step where task_id = '" + sSourceTaskID + "'";
if (!oTrans.ExecUpdate(ref sErr))
throw new Exception(sErr);
//steps temp table
oTrans.Command.CommandText = "create temporary table _copy_task_step" +
" select step_id, '" + sNewTaskID + "' as task_id, codeblock_name, step_order, commented," +
" locked, function_name, function_xml, step_desc, output_parse_type, output_row_delimiter," +
" output_column_delimiter, variable_xml" +
" from task_step where task_id = '" + sSourceTaskID + "'";
if (!oTrans.ExecUpdate(ref sErr))
throw new Exception(sErr);
//update the step id
oTrans.Command.CommandText = "update _copy_task_step a, _step_ids b" +
" set a.step_id = b.newstep_id" +
" where a.step_id = b.step_id";
if (!oTrans.ExecUpdate(ref sErr))
throw new Exception(sErr);
//update steps with codeblocks that reference a step (embedded steps)
oTrans.Command.CommandText = "update _copy_task_step a, _step_ids b" +
" set a.codeblock_name = b.newstep_id" +
" where b.step_id = a.codeblock_name";
if (!oTrans.ExecUpdate(ref sErr))
throw new Exception(sErr);
//spin the steps and update any embedded step id's in the commands
oTrans.Command.CommandText = "select step_id, newstep_id from _step_ids";
DataTable dtStepIDs = new DataTable();
if (!oTrans.ExecGetDataTable(ref dtStepIDs, ref sErr))
throw new Exception("Unable to get step ids." + sErr);
foreach (DataRow drStepIDs in dtStepIDs.Rows)
{
oTrans.Command.CommandText = "update _copy_task_step" +
" set function_xml = replace(lower(function_xml), '" + drStepIDs["step_id"].ToString().ToLower() + "', '" + drStepIDs["newstep_id"].ToString() + "')" +
" where function_name in ('if','loop','exists')";
if (!oTrans.ExecUpdate(ref sErr))
throw new Exception(sErr);
}
//finally, put the temp steps table in the real steps table
oTrans.Command.CommandText = "insert into task select * from _copy_task";
if (!oTrans.ExecUpdate(ref sErr))
throw new Exception(sErr);
oTrans.Command.CommandText = "insert into task_codeblock select * from _copy_task_codeblock";
if (!oTrans.ExecUpdate(ref sErr))
throw new Exception(sErr);
oTrans.Command.CommandText = "insert into task_step select * from _copy_task_step";
if (!oTrans.ExecUpdate(ref sErr))
throw new Exception(sErr);
//finally, if we versioned up and we set this one as the new default_version,
//we need to unset the other row
if (iMode > 0 && iIsDefault == 1)
{
oTrans.Command.CommandText = "update task" +
" set default_version = 0" +
" where original_task_id = '" + sOTID + "'" +
" and task_id <> '" + sNewTaskID + "'";
if (!oTrans.ExecUpdate(ref sErr))
throw new Exception(sErr);
}
oTrans.Commit();
return sNewTaskID;
}
