/**
* Get a static reference to the storage object associated with this model object
*
* @return the storage object
*/
public static function get_so()
{
if (self::$so == null) {
self::$so = CreateObject('controller.socontrol_item');
}
return self::$so;
}
/**
* Get check lists from database with related check items and control items
*
* @param $check_list_id check list id
* @param $status status OPEN/CLOSED
* @param $type control items registration type (Radiobuttons, Checklist, textfield, just commentfield)
* @return returns a check list object
*/
public function get_single_with_check_items($check_list_id, $status, $type)
{
$check_list_id = (int) $check_list_id;
$sql = "SELECT cl.id as cl_id, cl.status as cl_status, cl.control_id, cl.comment as cl_comment, deadline, planned_date, completed_date,assigned_to, num_open_cases, location_code, num_pending_cases, ";
$sql .= "ci.id as ci_id, control_item_id, check_list_id, cl.serie_id";
$sql .= "coi.title as coi_title, coi.required as coi_required, ";
$sql .= "coi.what_to_do as coi_what_to_do, coi.how_to_do as coi_how_to_do, coi.control_group_id as coi_control_group_id, coi.type ";
$sql .= "FROM controller_check_list cl ";
$sql .= "LEFT JOIN controller_check_item as ci ON cl.id = ci.check_list_id ";
$sql .= "LEFT JOIN controller_control_item as coi ON ci.control_item_id = coi.id ";
$sql .= "WHERE cl.id = {$check_list_id} ";
if ($status == 'open') {
$sql .= "AND ci.status = 0 ";
} else {
if ($status == 'handled') {
$sql .= "AND ci.status = 1 ";
}
}
if ($type != null) {
$sql .= "AND coi.type = '{$type}'";
}
$this->db->query($sql);
$counter = 0;
$check_list = null;
while ($this->db->next_record()) {
if ($counter == 0) {
$check_list = new controller_check_list($this->unmarshal($this->db->f('cl_id'), 'int'));
$check_list->set_status($this->unmarshal($this->db->f('cl_status'), 'bool'));
$check_list->set_control_id($this->unmarshal($this->db->f('control_id'), 'int'));
$check_list->set_comment($this->unmarshal($this->db->f('cl_comment', true), 'string'));
$check_list->set_deadline($this->unmarshal($this->db->f('deadline'), 'int'));
$check_list->set_planned_date($this->unmarshal($this->db->f('planned_date'), 'int'));
$check_list->set_completed_date($this->unmarshal($this->db->f('completed_date'), 'int'));
$check_list->set_location_code($this->unmarshal($this->db->f('location_code', true), 'string'));
$check_list->set_num_open_cases($this->unmarshal($this->db->f('num_open_cases'), 'int'));
$check_list->set_num_pending_cases($this->unmarshal($this->db->f('num_pending_cases'), 'int'));
$check_list->set_assigned_to($this->unmarshal($this->db->f('assigned_to'), 'int'));
$check_list->set_serie_id($this->db->f('serie_id'));
}
if ($this->db->f('ci_id')) {
$check_item = new controller_check_item($this->unmarshal($this->db->f('ci_id'), 'int'));
$check_item->set_control_item_id($this->unmarshal($this->db->f('control_item_id'), 'int'));
$check_item->set_check_list_id($this->unmarshal($this->db->f('check_list_id'), 'int'));
$control_item = new controller_control_item($this->unmarshal($this->db->f('coi_id'), 'int'));
$control_item->set_title($this->db->f('coi_title', true), 'string');
$control_item->set_required($this->db->f('coi_required', true), 'string');
$control_item->set_what_to_do($this->db->f('coi_what_to_do', true), 'string');
$control_item->set_how_to_do($this->db->f('coi_how_to_do', true), 'string');
$control_item->set_control_group_id($this->db->f('coi_control_group_id', true), 'string');
$control_item->set_type($this->db->f('type', true), 'string');
$check_item->set_control_item($control_item->toArray());
$check_items_array[] = $check_item->toArray();
}
$counter++;
}
if ($check_list != null) {
$check_list->set_check_item_array($check_items_array);
return $check_list->toArray();
} else {
return null;
}
}
public function save()
{
if (!$this->add && !$this->edit) {
$GLOBALS['phpgw']->redirect_link('/index.php', array('menuaction' => 'controller.uicontrol_item.index'));
}
$control_item_id = phpgw::get_var('id');
$title = phpgw::get_var('title');
$required = phpgw::get_var('required') == 'on' ? true : false;
$type = phpgw::get_var('control_item_type');
$control_group_id = phpgw::get_var('control_group');
$control_area_id = phpgw::get_var('control_area');
$what_to_do_txt = phpgw::get_var('what_to_do', 'html');
$what_to_do_txt = str_replace(" ", " ", $what_to_do_txt);
$how_to_do_txt = phpgw::get_var('how_to_do', 'html');
$how_to_do_txt = str_replace(" ", " ", $how_to_do_txt);
if ($control_item_id > 0) {
$control_item = $this->so->get_single($control_item_id);
} else {
$control_item = new controller_control_item();
}
$control_item->set_title($title);
$control_item->set_required($required);
$control_item->set_control_group_id($control_group_id);
$control_item->set_control_area_id($control_area_id);
$control_item->set_type($type);
$control_item->set_what_to_do($what_to_do_txt);
$control_item->set_how_to_do($how_to_do_txt);
if ($control_item->validate()) {
$transaction_status = true;
$db_control_item = $this->so->get_db();
$db_control_item->transaction_begin();
$saved_control_item_id = $this->so->store($control_item);
if ($saved_control_item_id == 0) {
$transaction_status = false;
}
// Delete item option values
$delete_status = $this->so->delete_option_values($saved_control_item_id);
if ($delete_status == 0) {
$transaction_status = false;
}
if ($transaction_status == true) {
$db_control_item->transaction_commit();
} else {
$db_control_item->transaction_abort();
}
$option_values = array();
$option_values = phpgw::get_var('option_values');
$option_values_array = array();
foreach ($option_values as $option_value) {
$control_item_option = new controller_control_item_option($option_value, $saved_control_item_id);
$option_values_array[] = $control_item_option;
}
$control_item->set_options_array($option_values_array);
// Add new control item option values
if ($transaction_status & $saved_control_item_id > 0 & ($control_item->get_type() == 'control_item_type_3' | $control_item->get_type() == 'control_item_type_4')) {
$control_item_options_array = $control_item->get_options_array();
foreach ($control_item_options_array as $control_item_option) {
$control_item_option_id = $this->so_control_item_option->store($control_item_option);
}
}
$GLOBALS['phpgw']->redirect_link('/index.php', array('menuaction' => 'controller.uicontrol_item.view', 'id' => $saved_control_item_id));
} else {
$this->edit($control_item);
}
}
/**
* Get check item objects from database including related control item and cases
*
* @param $message_ticket_id get check items and cases for this message
* @return check item objects
*/
public function get_check_items_with_cases_by_message($message_ticket_id)
{
$message_ticket_id = (int) $message_ticket_id;
$sql = "SELECT ci.id as ci_id, control_item_id, cic.component_location_id,";
$sql .= "check_list_id, cic.id as cic_id, cic.status as cic_status, cic.*, ";
$sql .= "coi.id as coi_id, coi.* ";
$sql .= "FROM controller_control_group JOIN controller_control_item ON controller_control_item.control_group_id=controller_control_group.id ";
$sql .= "JOIN controller_check_item ci ON ci.control_item_id = controller_control_item.id ";
$sql .= "LEFT JOIN controller_control_item as coi ON ci.control_item_id = coi.id ";
$sql .= "LEFT JOIN controller_check_item_case as cic ON ci.id = cic.check_item_id ";
$sql .= "WHERE cic.location_item_id = {$message_ticket_id}";
$this->db->query($sql);
$check_item_id = 0;
$check_item = null;
while ($this->db->next_record()) {
if ($this->db->f('ci_id') != $check_item_id) {
if ($check_item_id) {
$check_item->set_cases_array($cases_array);
$check_items_array[] = $check_item;
}
$check_item = new controller_check_item($this->unmarshal($this->db->f('ci_id'), 'int'));
$check_item->set_control_item_id($this->unmarshal($this->db->f('control_item_id'), 'int'));
$check_item->set_check_list_id($this->unmarshal($this->db->f('check_list_id'), 'int'));
$control_item = new controller_control_item($this->unmarshal($this->db->f('coi_id'), 'int'));
$control_item->set_title($this->db->f('title', true), 'string');
$control_item->set_required($this->db->f('required', true), 'string');
$control_item->set_what_to_do($this->db->f('what_to_do', true), 'string');
$control_item->set_how_to_do($this->db->f('how_to_do', true), 'string');
$control_item->set_control_group_id($this->db->f('control_group_id'), 'int');
$control_item->set_component_location_id($this->db->f('component_location_id'), 'int');
$control_item->set_type($this->db->f('type', true), 'string');
$check_item->set_control_item($control_item);
$cases_array = array();
}
if ($this->db->f('cic_id')) {
$case = new controller_check_item_case($this->unmarshal($this->db->f('cic_id'), 'int'));
$case->set_status($this->unmarshal($this->db->f('cic_status'), 'int'));
$case->set_check_item_id($this->unmarshal($this->db->f('check_item_id'), 'int'));
$case->set_location_id($this->unmarshal($this->db->f('location_id'), 'int'));
$case->set_location_item_id($this->unmarshal($this->db->f('location_item_id'), 'int'));
$case->set_descr($this->unmarshal($this->db->f('descr', true), 'string'));
$case->set_user_id($this->unmarshal($this->db->f('user_id'), 'int'));
$case->set_entry_date($this->unmarshal($this->db->f('entry_date'), 'int'));
$case->set_modified_date($this->unmarshal($this->db->f('modified_date'), 'int'));
$case->set_modified_by($this->unmarshal($this->db->f('modified_by'), 'int'));
$cases_array[] = $case;
}
$check_item_id = $check_item->get_id();
}
if ($check_item != null) {
$check_item->set_cases_array($cases_array);
$check_items_array[] = $check_item;
return $check_items_array;
} else {
return null;
}
}
/**
* Get control item objects with control item options from database as objects or as arrays
*
* @param $control_id control id
* @param $control_group_id control group id
* @param $return_type return data as objects or as arrays
* @return array with control items
*/
function get_control_items_and_options_by_control_and_group($control_id, $control_group_id, $return_type = "return_array")
{
$control_id = (int) $control_id;
$control_group_id = (int) $control_group_id;
$results = array();
$sql = "SELECT ci.id as ci_id, ci.*, cio.id as cio_id, cio.* ";
$sql .= "FROM controller_control_item ci ";
$sql .= "LEFT JOIN controller_control_item_list cl ON cl.control_item_id = ci.id ";
$sql .= "LEFT JOIN controller_control c ON c.id = cl.control_id ";
$sql .= "LEFT JOIN controller_control_item_option cio ON ci.id = cio.control_item_id ";
$sql .= "WHERE c.id={$control_id} ";
$sql .= "AND ci.control_group_id={$control_group_id} ";
$sql .= "ORDER BY cl.order_nr";
$this->db->query($sql, __LINE__, __FILE__);
$control_item_id = 0;
$control_item = null;
$control_item_array = array();
while ($this->db->next_record()) {
if ($this->db->f('ci_id') != $control_item_id) {
if ($control_item_id) {
$control_item->set_options_array($options_array);
if ($return_type == "return_array") {
$control_item_array[] = $control_item->toArray();
} else {
$control_item_array[] = $control_item;
}
}
$control_item = new controller_control_item($this->unmarshal($this->db->f('ci_id'), 'int'));
$control_item->set_title($this->unmarshal($this->db->f('title', true), 'string'));
$control_item->set_required($this->unmarshal($this->db->f('required'), 'boolean'));
$control_item->set_what_to_do($this->unmarshal($this->db->f('what_to_do', true), 'string'));
$control_item->set_how_to_do($this->unmarshal($this->db->f('how_to_do', true), 'string'));
$control_item->set_control_group_id($this->unmarshal($this->db->f('control_group_id'), 'int'));
$control_item->set_type($this->unmarshal($this->db->f('type', true), 'string'));
$options_array = array();
}
$control_item_option = new controller_control_item_option($this->db->f('option_value', true), $this->db->f('control_item_id'));
$control_item_option->set_id($this->db->f('cio_id'));
if ($return_type == "return_array") {
$options_array[] = $control_item_option->toArray();
} else {
$options_array[] = $control_item_option;
}
$control_item_id = $control_item->get_id();
}
if ($control_item != null) {
$control_item->set_options_array($options_array);
if ($return_type == "return_array") {
$control_item_array[] = $control_item->toArray();
} else {
$control_item_array[] = $control_item;
}
return $control_item_array;
} else {
return null;
}
}
/**
* Get control items for a control within control group
*
* @param $control_id control id
* @param $control_group_id control group id
* @return void
*/
function get_items_for_control_group($control_id, $control_group_id)
{
$control_id = (int) $control_id;
$control_group_id = (int) $control_group_id;
$results = array();
$sql = "select ci.* from controller_control_item ci, controller_control_item_list cil where ci.control_group_id = {$control_group_id} and cil.control_id = {$control_id} and ci.id = cil.control_item_id";
$this->db->query($sql, __LINE__, __FILE__);
while ($this->db->next_record()) {
//create check_item and add to return array
$control_item = new controller_control_item($this->unmarshal($this->db->f('id'), 'int'));
$control_item->set_title($this->unmarshal($this->db->f('title', true), 'string'));
$control_item->set_required($this->unmarshal($this->db->f('required', true), 'boolean'));
$control_item->set_type($this->unmarshal($this->db->f('type', true), 'string'));
$control_item->set_what_to_do($this->unmarshal($this->db->f('what_to_do', true), 'string'));
$control_item->set_how_to_do($this->unmarshal($this->db->f('how_to_do', true), 'string'));
$control_item->set_control_group_id($this->unmarshal($this->db->f('control_group_id'), 'int'));
$control_item->set_control_area_id($this->unmarshal($this->db->f('control_area_id'), 'int'));
$results[] = $control_item;
}
return $results;
}
