/** * Create work and associated columns in the database * * @param int data_id The id of the data group * @param int process_id The process id to apply * @param int column_id The column id where the data originates * @param array operators An array of operators to assign the work to (if any) * @param int|bool $mcgi Multi code_group_id - to create multiple columns if exists * @param array|bool $compare Whether we are creating a comparison work item or not (if true, should be an array of parent work_ids) * @param int|bool $reference Reference column id - to be handled by process * @return bool True on success, false on fail */ function create_work($data_id, $process_id, $column_id, $operators = array('NULL'), $mcgi = false, $compare = false, $reference = false) { global $db; $db->StartTrans(); //Determine if any code applies $sql = "SELECT code_group_id,template\r\n\t\tFROM process\r\n\t\tWHERE process_id = '{$process_id}'"; $c = $db->GetRow($sql); $column_group_id = "NULL"; $cmgi = "NULL"; $work_id = array(); if (!empty($c['code_group_id'])) { $mc = array(array('cmgi' => "NULL", 'label' => "")); //Columns need to be created as we are creating a new code //need to create a new column for each assigned operator (if there is one) foreach ($operators as $operator_id) { //Create a new cmg for each operator otherwise they are writing in to the same group if ($mcgi) { $sql = "INSERT INTO column_multi_group(description,code_group_id)\r\n\t\t\t\t\tVALUES ('','{$mcgi}')"; $db->Execute($sql); $cmgi = $db->Insert_ID(); $sql = "SELECT {$cmgi} as cmgi,c.label as label,cg.blank_code_id,c.code_id\r\n\t\t\t\t\tFROM code as c,code_level as cl,code_group as cg\r\n\t\t\t\t\tWHERE cg.code_group_id = '{$mcgi}'\r\n\t\t\t\t\tAND cl.code_group_id = cg.code_group_id\r\n\t\t\t\t\tAND c.code_level_id = cl.code_level_id\r\n\t\t\t\t\tAND cl.level = 0"; //select the top level only $mc = $db->GetAll($sql); } foreach ($mc as $m) { $template = $c['template']; $cmgi = $m['cmgi']; if ($template == 1) { if ($reference == false) { $code_group_id = copy_code_group($c['code_group_id'], !$compare); } else { //if a reference, get code group id from reference column $sql = "SELECT cl.code_group_id \r\n\t\t\t\t\t\t\tFROM `column` as c, code_level as cl\r\n\t\t\t\t\t\t\tWHERE c.column_id = '{$reference}'\r\n\t\t\t\t\t\t\tAND cl.code_level_id = c.code_level_id"; $tmp = $db->GetRow($sql); $code_group_id = copy_code_group($tmp['code_group_id'], !$compare); } } else { $code_group_id = $c['code_group_id']; } if ($compare !== false) { //if comparing, create codes in this code group for each column in this work $sql = "SELECT column_group_id,column_multi_group_id\r\n\t\t\t\t\t\tFROM work\r\n\t\t\t\t\t\tWHERE ( "; foreach ($compare as $c) { $sql .= " work_id = {$c} OR"; } $sql = substr($sql, 0, -2) . ")"; $ncodes = $db->GetAll($sql); $width = 0; $cval = 2; //start cmgi code value at 2 (first one is Identical - 1) foreach ($ncodes as $n) { if (!empty($n['column_group_id'])) { $c = "cgi" . $n['column_group_id']; } if (!empty($n['column_multi_group_id'])) { $c = "cmgi" . $n['column_multi_group_id']; } if (strlen($cval) > $width) { $width = strlen($cval); } $sql = "INSERT INTO `code` (code_id,value,label,code_level_id)\r\n\t\t\t\t\t\t\tSELECT NULL,'{$cval}','{$c}',code_level_id\r\n\t\t\t\t\t\t\tFROM code_level\r\n\t\t\t\t\t\t\tWHERE code_group_id = '{$code_group_id}'"; $db->Execute($sql); $cval++; } $sql = "UPDATE code_level\r\n\t\t\t\t\t\tSET width = '{$width}'\r\n\t\t\t\t\t\tWHERE code_group_id = '{$code_group_id}'"; $db->Execute($sql); } //First create a new column_group $sql = "INSERT INTO column_group (column_group_id,description,code_group_id)\r\n\t\t\t\t\tSELECT NULL, CONCAT(p.description, ' " . T_("code for variable:") . " ', c.name, CASE WHEN o.operator_id IS NULL THEN '' ELSE CONCAT(' " . T_("by operator:") . " ', o.description) END ), '{$code_group_id}'\r\n\t\t\t\t\tFROM process as p\r\n\t\t\t\t\tJOIN `column` as c on (c.column_id = '{$column_id}')\r\n\t\t\t\t\tLEFT JOIN operator as o on (o.operator_id = {$operator_id})\r\n\t\t\t\t\tWHERE p.process_id = '{$process_id}'"; $db->Execute($sql); $column_group_id = $db->Insert_ID(); //Name a coded column by the code group description $sql = "SELECT `description`\r\n\t\t\t\t\tFROM `column`\r\n\t\t\t\t\tWHERE column_id = {$column_id}"; $description = $db->GetOne($sql); //remove spaces $description = str_replace(" ", "", $description); //Now create all necessary columns $sql = "INSERT INTO `column` (column_id,data_id,column_group_id,name,description,startpos,width,type,in_input,sortorder,code_level_id,column_multi_group_id)\r\n\t\t\t\t\tSELECT NULL,'{$data_id}','{$column_group_id}',CONCAT('{$description}',ocg.description,(ocl.level + 1)),"; if ($cmgi == "NULL") { $sql .= "CONCAT('" . T_("Code for group:") . " ',ocg.description, ' " . T_("Level:") . " ',ocl.level)"; } else { $sql .= "'{$m['label']}'"; } $sql .= ",0,width,(SELECT c.value NOT REGEXP '[0-9]+' as ttype\r\n\t\t\t\t\t\tFROM code as c\r\n\t\t\t\t\t\tWHERE c.code_level_id = ocl.code_level_id\r\n\t\t\t\t\t\tGROUP BY c.value NOT REGEXP '[0-9]+'\r\n\t\t\t\t\t\tORDER BY ttype ASC\r\n\t\t\t\t\t\tLIMIT 1),'0','0',ocl.code_level_id,{$cmgi}\r\n\t\t\t\t\tFROM code_level as ocl, code_group as ocg\r\n\t\t\t\t\tWHERE ocl.code_group_id = '{$code_group_id}'\r\n\t\t\t\t\tAND ocg.code_group_id = '{$code_group_id}'"; $db->Execute($sql); if ($m['code_id'] == $m['blank_code_id']) { $temp_column_id = $db->Insert_ID(); $sql = "UPDATE column_multi_group\r\n\t\t\t\t\t\tSET blank_column_id = '{$temp_column_id}'\r\n\t\t\t\t\t\tWHERE column_multi_group_id = '{$cmgi}'"; $db->Execute($sql); } if ($mcgi) { $sql = "INSERT INTO `column` (column_id,data_id,column_group_id,name,description,startpos,width,type,in_input,sortorder,reference_column_group_id) VALUES (NULL,'{$data_id}','{$column_group_id}','TG{$column_group_id}','" . T_("Text for") . " {$column_group_id}','0','2048','1','0','0','{$column_group_id}')"; $db->Execute($sql); } } if ($cmgi != "NULL") { $column_group_id = "NULL"; } if ($reference == false) { $reference = "NULL"; } //Create the initial work item $sql = "INSERT INTO work (work_id,column_id,column_group_id,process_id,operator_id,column_multi_group_id,reference_column_id)\r\n\t\t\t\tVALUES (NULL,'{$column_id}',{$column_group_id},'{$process_id}',{$operator_id},{$cmgi},{$reference})"; $db->Execute($sql); $twid = $db->Insert_ID(); $work_id[] = $twid; } } else { //No code group to worry about foreach ($operators as $operator_id) { //Create the initial work item $sql = "INSERT INTO work (work_id,column_id,column_group_id,process_id,operator_id)\r\n\t\t\t\tVALUES (NULL,'{$column_id}',NULL,'{$process_id}',{$operator_id})"; $db->Execute($sql); $work_id[] = $db->Insert_ID(); } } $nwork_id = array(); foreach ($work_id as $w) { //code for each $nwork_id[] = array($w, $process_id); } //Create all dependent work items //For multiple operators, each work item will have the same parent process do { $sql = "SELECT parent_process_id\r\n\t\t\tFROM process_parent\r\n\t\t\tWHERE process_id = '{$process_id}'"; $pprocess = $db->GetRow($sql); if (!empty($pprocess)) { $parent_process_id = $pprocess['parent_process_id']; $sql = "INSERT INTO work (work_id,column_id,column_group_id,process_id)\r\n\t\t\t\tVALUES (NULL, '{$column_id}',NULL,'{$parent_process_id}')"; $db->Execute($sql); $parent_work_id = $db->Insert_ID(); $nwork_id[] = array($parent_work_id, $parent_process_id); foreach ($work_id as $wid) { $sql = "INSERT INTO work_parent (work_id,parent_work_id)\r\n\t\t\t\t\tVALUES ('{$wid}','{$parent_work_id}')"; $db->Execute($sql); } $process_id = $parent_process_id; $work_id = array($parent_work_id); } } while (!empty($pprocess)); //Insert if comparing if ($compare !== false) { foreach ($compare as $c) { $sql = "INSERT INTO work_parent (work_id,parent_work_id)\r\n\t\t\t\tVALUES ('{$twid}','{$c}')"; $db->Execute($sql); } } run_auto_processes(array_reverse($nwork_id)); return $db->CompleteTrans(); }
*/ include "../functions/functions.import.php"; xhtml_head(T_("Import data file"), true, false, array('../js/display.js')); if (isset($_POST['import_file']) && isset($_GET['data_id']) && isset($_GET['column_id'])) { //file has been submitted $datafname = tempnam("/tmp", "FOO"); move_uploaded_file($_FILES['datafile']['tmp_name'], $datafname); $data_id = intval($_GET['data_id']); $column_id = intval($_GET['column_id']); $is = import_fixed_width($datafname, $data_id, $column_id); if ($is != false) { $rows = count($is); print "<p>" . T_("Data imported successfully") . " " . T_("Rows:") . " {$rows}</p>"; if ($rows > 0) { include "../functions/functions.work.php"; if (run_auto_processes(get_auto_processes($data_id), $is)) { p(T_("Successfully ran automatic processes"), "p"); } else { p(T_("Failed to run automatic processes"), "p"); } } } else { p(T_("Failed to import data"), "p"); } } else { //First choose data file $data_id = 0; if (isset($_GET['data_id'])) { $data_id = intval($_GET['data_id']); } //Select a data file to mark