/** * Get single check item object from database including related control item * * @param $check_list_id check list id * @param $check_item_id control item id * @return check item object */ public function get_check_item_by_check_list_and_control_item($check_list_id, $control_item_id) { $check_list_id = (int) $check_list_id; $control_item_id = (int) $control_item_id; $sql = "SELECT ci.*, ci.id as c_id, coi.id as coi_id, coi.* "; $sql .= "FROM controller_check_item ci, controller_control_item coi "; $sql .= "WHERE ci.check_list_id = {$check_list_id} "; $sql .= "AND ci.control_item_id = coi.id "; $sql .= "AND ci.control_item_id = {$control_item_id}"; $this->db->query($sql, __LINE__, __FILE__); if ($this->db->next_record()) { $check_item = new controller_check_item($this->unmarshal($this->db->f('c_id'), 'int')); $check_item->set_check_list_id($this->unmarshal($this->db->f('check_list_id'), 'int')); $check_item->set_control_item_id($this->unmarshal($this->db->f('control_item_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'); $check_item->set_control_item($control_item->toArray()); return $check_item; } else { return null; } }
/** * 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; } }
/** * 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; } }