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