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