/**
* 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
