/** * Method loadFromSqlDataView * @access public * @param mixed $sql * @param mixed $properties [default value: array(] * @return boolean * @since 1.1.6 */ public function loadFromSqlDataView($sql, $properties = array(), $insert = false, $update = false, $delete = false) { if ($this->id == "") { throw new NewException(get_class($this) . "->loadFromSqlDataView() error: you must define an id to the Table (" . get_class($this) . "->setId())", 0, getDebugBacktrace(1)); } if (gettype($sql) != "object" || get_class($sql) != "SqlDataView") { throw new NewException(get_class($this) . "->loadFromSqlDataView() error: \$sql is not SqlDataView object", 0, getDebugBacktrace(1)); } if ($insert || $update || $delete) { if ($sql->isQueryWithJoin()) { throw new NewException(get_class($this) . "->loadFromSqlDataView() error: you need use SqlDataView object without JOIN if you want to use insert update or delete.", 0, getDebugBacktrace(1)); } $this->is_table_form_object = true; $this->table_form_object = new Form($this->getPage(), "Form_Table_" . $this->id); $this->table_form_object->setContent($this); $this->table_form_object->onSubmitJs("return false;"); } if (!is_array($properties)) { throw new NewException(get_class($this) . "->loadFromSqlDataView() error: \$properties need to be an array", 0, getDebugBacktrace(1)); } $this->from_sql_data_view_insert = $insert; $this->from_sql_data_view_update = $update; $this->from_sql_data_view_delete = $delete; $list_attribute = $sql->getListAttributeArray(); // Add properties to apply on all fields if (isset($properties[ModelViewMapper::PROPERTIES_ALL]) && is_array($properties[ModelViewMapper::PROPERTIES_ALL])) { $apply_all_array = $properties[ModelViewMapper::PROPERTIES_ALL]; foreach ($apply_all_array as $property_name => $property_value) { for ($i = 0; $i < sizeof($list_attribute); $i++) { $property[$property_name] = $property_value; if (isset($properties[$list_attribute[$i]])) { $properties[$list_attribute[$i]] = array_merge($properties[$list_attribute[$i]], $property); } else { $properties[$list_attribute[$i]] = $property; } } } } // check foreign keys $db_table_foreign_keys = $sql->getDbTableObject()->getDbTableForeignKeys(); foreach ($db_table_foreign_keys as $fk_attribute => $value) { if (isset($properties[$fk_attribute])) { $fk_property = $properties[$fk_attribute]; if (isset($fk_property["fk_attribute"])) { // create combobox $cmb = new ComboBox($this->table_form_object == null ? $this->getPage() : $this->table_form_object); // get foreign key data $query = "select distinct " . $value["column"] . " as id, " . $fk_property["fk_attribute"] . " as value from " . $value["table"]; if (isset($fk_property["fk_where"])) { $query .= " where " . $fk_property["fk_where"]; } if (isset($fk_property["fk_orderby"])) { $query .= " order by " . $fk_property["fk_orderby"]; } $stmt = DataBase::getInstance()->prepareStatement($query); $row = DataBase::getInstance()->stmtBindAssoc($stmt, $row); while ($stmt->fetch()) { $cmb->addItem(utf8encode($row['id']), utf8encode($row['value'])); } // add combo box in properties $value['cmb_obj'] = $cmb; $properties[$fk_attribute] = array_merge($properties[$fk_attribute], $value); } } } $this->from_sql_data_view_properties = $properties; // Define header $is_table_defined_style = false; $auto_header = sizeof($this->rows) > 0 ? false : true; if ($auto_header) { $row_table = new RowTable(); for ($i = 0; $i < sizeof($list_attribute); $i++) { // get property display if (isset($this->from_sql_data_view_properties[$list_attribute[$i]]["display"]) && $this->from_sql_data_view_properties[$list_attribute[$i]]["display"] == false) { continue; } $row_table->add($list_attribute[$i]); } if ($delete || $insert) { $row_table->add(); } $row_table = $this->addRow($row_table); if ($this->class == "") { $row_table->setHeaderClass(0); } else { if (is_numeric($this->class)) { $row_table->setHeaderClass($this->class); $is_table_defined_style = true; } } } else { if ($delete || $insert) { $row_table = $this->rows[0]; $row_table->add(); } } $key_attributes = $sql->getPrimaryKeysAttributes(); // check if all the fields of sql object are in the SQL attributes $list_attribute_change = false; $auto_hide_field_from = -1; $all_list_attributes = $sql->getDbTableObject()->getDbTableAttributes(); for ($i = 0; $i < sizeof($all_list_attributes); $i++) { if (!in_array($all_list_attributes[$i], $list_attribute)) { $tmp_list_attribute = $sql->getCustomFields(); $tmp_list_attribute .= ", `" . $sql->getDbTableObject()->getDbTableName() . "`.`" . $all_list_attributes[$i] . "`"; $sql->setCustomFields($tmp_list_attribute); if ($auto_hide_field_from == -1) { $auto_hide_field_from = sizeof($list_attribute); } } } if ($auto_hide_field_from != -1) { $list_attribute = $sql->getListAttributeArray(); for ($i = $auto_hide_field_from; $i < sizeof($list_attribute); $i++) { $properties[$list_attribute[$i]]["display"] = false; } } $list_attribute_type = $sql->getListAttributeTypeArray(); $auto_increment_id = $sql->getDbTableObject()->getDbTableAutoIncrement(); $this->from_sql_data_view_properties = $properties; // create empty row (ack to keep correct order of the table) $row_table = new RowTable(); $row_table->setId($this->id . "_emptyrow_"); for ($i = 0; $i < sizeof($list_attribute); $i++) { // get property display if (isset($this->from_sql_data_view_properties[$list_attribute[$i]]["display"]) && $this->from_sql_data_view_properties[$list_attribute[$i]]["display"] == false) { continue; } $row_table->add(); } if ($insert || $delete) { $row_table->add(); } $row_table->setStyle("display:none;"); $this->addRow($row_table); // Create insert row if ($insert) { $row_table = new RowTable(); $row_table->setId($this->id . "_row_"); for ($i = 0; $i < sizeof($list_attribute); $i++) { // get property display if (isset($this->from_sql_data_view_properties[$list_attribute[$i]]["display"]) && $this->from_sql_data_view_properties[$list_attribute[$i]]["display"] == false) { continue; } if ($list_attribute[$i] != $auto_increment_id) { $input_obj = $this->createDbAttributeObject(null, $list_attribute, $list_attribute_type, $i, "", $key_attributes); $row_table->add($input_obj); } else { $row_table->add(); } } $this->from_sql_data_view_add_button = new Button($this->table_form_object, $this->id . "_btnadd__ind_"); $this->from_sql_data_view_add_button->setPrimaryIcon("img/wsp-admin/button_ok_16.png"); $this->from_sql_data_view_add_button->onClick("onChangeTableFromSqlDataView")->setAjaxEvent()->disableAjaxWaitMessage(); $row_table->add($this->from_sql_data_view_add_button); if ($is_table_defined_style) { $row_table->setBorderPredefinedStyle($this->class); } $this->addRow($row_table); } else { if ($this->is_advance_table) { $row_table = clone $row_table; $this->addRow($row_table); } } // create reload button if ($insert || $update) { $this->from_sql_data_view_reload_pic = new Picture("wsp/img/reload_16x16.png", 16, 16, 0, Picture::ALIGN_ABSMIDDLE, "Please reload"); $this->from_sql_data_view_reload_pic->setId($this->id . "_btnreload__ind_"); $this->from_sql_data_view_reload_pic->onClick($this->getPage(), "onChangeTableFromSqlDataView")->setAjaxEvent()->disableAjaxWaitMessage(); } // Check if a delete button is clicked (if no primary key defined in table) $deleted_ind = -1; $is_delete_action = false; $it = $sql->retrieve(); if ($this->from_sql_data_view_delete && sizeof($key_attributes) == 0) { for ($i = 0; $i < $it->getRowsNum(); $i++) { $delete_pic = new Picture("img/wsp-admin/delete_16.png", 16, 16, 0, Picture::ALIGN_ABSMIDDLE); $delete_pic->setId($this->id . "_btndel__ind_" . $i); $delete_pic->onClickJs("if (!confirm('" . __(TABLE_CONFIME_DEL_ROW) . "')) { return false; }"); $delete_pic->onClick($this->getPage(), "onChangeTableFromSqlDataView")->setAjaxEvent()->disableAjaxWaitMessage(); if ($delete_pic->isClicked()) { $is_delete_action = true; $deleted_ind = $i; break; } } } // Generate table data $ind = 0; $this->sql_data_view_object = $sql; $this->data_row_iterator_object = $it; while ($it->hasNext()) { $row = $it->next(); $key_str = ""; if (sizeof($key_attributes) == 0) { $key_str = $ind; } else { try { for ($i = 0; $i < sizeof($key_attributes); $i++) { if ($i > 0) { $key_str .= "-"; } $key_str .= $row->getValue($key_attributes[$i]); } $key_str = strtolower(url_rewrite_format($key_str)); } catch (Exception $ex) { if ($insert || $update || $delete) { throw new NewException(get_class($this) . "->loadFromSqlDataView() error: \$properties need to include primary key of the table if you want to use insert, update or delete feature", 0, getDebugBacktrace(1)); } else { $key_str = $ind; } } if ($key_str == "") { throw new NewException(get_class($this) . "->loadFromSqlDataView() error: The system can't create empty key for row (key is created by the attribute(s): " . implode(", ", $key_attributes) . ")", 0, getDebugBacktrace(1)); } } $this->from_sql_data_view_data_row_array[$key_str] = $row; if ($deleted_ind == $ind) { // if no primary key defined in table $deleted_ind = -1; $ind--; } else { $this->addRowLoadFromSqlDataView($row, $list_attribute, $list_attribute_type, $key_attributes, $key_str, $is_delete_action, $ind); } $ind++; } }