function generate_site_plugin_sql($site_type_r)
{
$CRLF = get_user_browser_crlf();
$buffer = "#########################################################" . $CRLF . "# OpenDb " . get_opendb_version() . " " . $site_type_r['title'] . " (" . $site_type_r['site_type'] . ") Site Plugin" . $CRLF . "#########################################################";
$buffer .= $CRLF . $CRLF . "#" . $CRLF . "# Site Plugin." . $CRLF . "#" . $CRLF;
$buffer .= $CRLF . "INSERT INTO s_site_plugin ( site_type, classname, title, image, description, external_url, items_per_page, more_info_url ) " . "VALUES ( " . "'" . $site_type_r['site_type'] . "', " . "'" . $site_type_r['classname'] . "', " . "'" . addslashes($site_type_r['title']) . "', " . "'" . addslashes($site_type_r['image']) . "', " . "'" . addslashes($site_type_r['description']) . "', " . "'" . addslashes($site_type_r['external_url']) . "', " . "" . $site_type_r['items_per_page'] . ", " . "'" . addslashes($site_type_r['more_info_url']) . "' );";
$results = fetch_site_plugin_conf_rs($site_type_r['site_type']);
if ($results) {
$buffer .= $CRLF . $CRLF . "#" . $CRLF . "# Site Plugin Configuration" . $CRLF . "#" . $CRLF;
while ($site_plugin_conf_r = db_fetch_assoc($results)) {
$buffer .= $CRLF . "INSERT INTO s_site_plugin_conf ( site_type, name, keyid, description, value ) " . "VALUES ( " . "'" . $site_plugin_conf_r['site_type'] . "', " . "'" . addslashes($site_plugin_conf_r['name']) . "', " . "'" . addslashes($site_plugin_conf_r['keyid']) . "', " . "'" . addslashes($site_plugin_conf_r['description']) . "', " . "'" . addslashes($site_plugin_conf_r['value']) . "' );";
}
db_fetch_assoc($results);
}
$results = fetch_site_plugin_input_field_rs($site_type_r['site_type']);
if ($results) {
$buffer .= $CRLF . $CRLF . "#" . $CRLF . "# Site Plugin Input Fields" . $CRLF . "#" . $CRLF;
while ($site_plugin_input_field_r = db_fetch_assoc($results)) {
$buffer .= $CRLF . "INSERT INTO s_site_plugin_input_field ( site_type, field, order_no, description, prompt, field_type, default_value, refresh_mask ) " . "VALUES ( " . "'" . $site_plugin_input_field_r['site_type'] . "', " . "'" . $site_plugin_input_field_r['field'] . "', " . "" . $site_plugin_input_field_r['order_no'] . ", " . "'" . addslashes($site_plugin_input_field_r['description']) . "', " . "'" . addslashes($site_plugin_input_field_r['prompt']) . "', " . "'" . $site_plugin_input_field_r['field_type'] . "', " . "'" . addslashes($site_plugin_input_field_r['default_value']) . "', " . "'" . addslashes($site_plugin_input_field_r['refresh_mask']) . "' );";
}
db_fetch_assoc($results);
}
$results = fetch_site_plugin_link_rs($site_type_r['site_type']);
if ($results) {
$buffer .= $CRLF . $CRLF . "#" . $CRLF . "# Site Plugin Links" . $CRLF . "#" . $CRLF;
while ($fetch_site_plugin_link_r = db_fetch_assoc($results)) {
$buffer .= $CRLF . "INSERT INTO s_site_plugin_link ( site_type, s_item_type_group, s_item_type, order_no, description, url, title_url ) " . "VALUES ( " . "'" . $fetch_site_plugin_link_r['site_type'] . "', " . "'" . $fetch_site_plugin_link_r['s_item_type_group'] . "', " . "'" . $fetch_site_plugin_link_r['s_item_type'] . "', " . "" . $fetch_site_plugin_link_r['order_no'] . ", " . "'" . addslashes($fetch_site_plugin_link_r['description']) . "', " . "'" . addslashes($fetch_site_plugin_link_r['url']) . "', " . "'" . addslashes($fetch_site_plugin_link_r['title_url']) . "' );";
}
db_fetch_assoc($results);
}
$results = fetch_site_plugin_s_attribute_type_map_rs($site_type_r['site_type']);
if ($results) {
$buffer .= $CRLF . $CRLF . "#" . $CRLF . "# Site Plugin System Attribute Type Map" . $CRLF . "#" . $CRLF;
while ($site_plugin_s_attribute_type_map_r = db_fetch_assoc($results)) {
$buffer .= $CRLF . "INSERT INTO s_site_plugin_s_attribute_type_map ( site_type, s_item_type_group, s_item_type, variable, s_attribute_type, lookup_attribute_val_restrict_ind ) " . "VALUES ( " . "'" . $site_plugin_s_attribute_type_map_r['site_type'] . "', " . "'" . $site_plugin_s_attribute_type_map_r['s_item_type_group'] . "', " . "'" . $site_plugin_s_attribute_type_map_r['s_item_type'] . "', " . "'" . addslashes($site_plugin_s_attribute_type_map_r['variable']) . "', " . "'" . $site_plugin_s_attribute_type_map_r['s_attribute_type'] . "', " . "'" . $site_plugin_s_attribute_type_map_r['lookup_attribute_val_restrict_ind'] . "' );";
}
db_fetch_assoc($results);
}
$results = fetch_site_plugin_s_attribute_type_lookup_map_rs($site_type_r['site_type']);
if ($results) {
$buffer .= $CRLF . $CRLF . "#" . $CRLF . "# Site Plugin System Attribute Type Lookup Map" . $CRLF . "#" . $CRLF;
while ($site_plugin_s_attribute_type_lookup_map_r = db_fetch_assoc($results)) {
$buffer .= $CRLF . "INSERT INTO s_site_plugin_s_attribute_type_lookup_map ( site_type, s_attribute_type, value, lookup_attribute_val ) " . "VALUES ( " . "'" . $site_plugin_s_attribute_type_lookup_map_r['site_type'] . "', " . "'" . $site_plugin_s_attribute_type_lookup_map_r['s_attribute_type'] . "', " . "'" . addslashes($site_plugin_s_attribute_type_lookup_map_r['value']) . "', " . "'" . addslashes($site_plugin_s_attribute_type_lookup_map_r['lookup_attribute_val']) . "' ); ";
}
db_fetch_assoc($results);
}
$results = fetch_site_attribute_type_rs($site_type_r['site_type']);
if ($results) {
$buffer .= $CRLF . $CRLF . "####################################################################################################" . $CRLF . "# Item Type / Attribute Type relationships" . $CRLF . "####################################################################################################";
$attr_inserts = "";
$list_of_attribute_types = NULL;
while ($site_attribute_type_r = db_fetch_assoc($results)) {
$list_of_attribute_types[] = $site_attribute_type_r['s_attribute_type'];
$attr_inserts .= $CRLF . "INSERT INTO s_attribute_type ( s_attribute_type, description, prompt, input_type, display_type, s_field_type, site_type ) " . "VALUES ( " . "'" . $site_attribute_type_r['s_attribute_type'] . "', " . "'" . addslashes($site_attribute_type_r['description']) . "', " . "'" . addslashes($site_attribute_type_r['prompt']) . "', " . "'" . addslashes($site_attribute_type_r['input_type']) . "', " . "'" . addslashes($site_attribute_type_r['display_type']) . "', " . "'" . $site_attribute_type_r['s_field_type'] . "', " . "'" . $site_attribute_type_r['site_type'] . "' );";
}
db_fetch_assoc($results);
$buffer .= $CRLF . $CRLF . "#" . $CRLF . "# Site Plugin Attribute Type(s)" . $CRLF . "#" . $CRLF;
$buffer .= $attr_inserts;
$results = fetch_site_item_attribute_type_rs($site_type_r['site_type']);
if ($results) {
$buffer .= $CRLF . $CRLF . "#" . $CRLF . "# Site Plugin Item Attribute Type Relationship(s)" . $CRLF . "#" . $CRLF;
while ($site_item_attribute_type_r = db_fetch_assoc($results)) {
$buffer .= $CRLF . "INSERT INTO s_item_attribute_type ( s_item_type, s_attribute_type, order_no, prompt, compulsory_ind ) " . "VALUES ( " . "'" . $site_item_attribute_type_r['s_item_type'] . "', " . "'" . $site_item_attribute_type_r['s_attribute_type'] . "', " . " " . $site_item_attribute_type_r['order_no'] . ", " . "'" . addslashes($site_item_attribute_type_r['prompt']) . "', " . "'" . ifempty($site_item_attribute_type_r['compulsory_ind'], 'N') . "' );";
}
db_fetch_assoc($results);
}
}
$buffer .= $CRLF;
return $buffer;
}
/**
* The $site_plugin_attributes_r will consist of array entries, each identified by a
* alphakey. The value may in fact be another array and this has to be handled
* appropriately.
*/
function get_expanded_and_mapped_site_plugin_item_variables_r($site_type, $s_item_type, $site_item_attributes_r)
{
$query = "SELECT variable, s_attribute_type, lookup_attribute_val_restrict_ind " . "FROM s_site_plugin_s_attribute_type_map " . "WHERE site_type = '" . $site_type . "' ";
$query .= "AND (s_item_type = '*' OR s_item_type = '" . $s_item_type . "') AND ";
$query .= "(s_item_type_group = '*' ";
$item_type_group_arr = fetch_item_type_groups_for_item_type_r($s_item_type);
if (is_array($item_type_group_arr)) {
$query .= "OR s_item_type_group IN (" . format_sql_in_clause($item_type_group_arr) . ")) ";
} else {
$query .= ") ";
}
$query .= "ORDER BY variable, s_attribute_type";
$new_attributes_r = array();
// cache this, so we can check if lookup_attribute_val_restrict_ind = 'Y'
$lookup_attribute_val_restrict_ind = array();
$mapped_attributes_r = array();
$results = db_query($query);
if ($results && db_num_rows($results) > 0) {
$variable = NULL;
while ($attribute_type_map_r = db_fetch_assoc($results)) {
$value = NULL;
$variable = $attribute_type_map_r['variable'];
if (isset($site_item_attributes_r[$variable])) {
$value = $site_item_attributes_r[$variable];
// at least one direct mapping - title should not be flagged - as there is requirement for multiple mappings
if ($variable != 'title') {
$mapped_attributes_r[] = $variable;
}
}
$key = strtolower($attribute_type_map_r['s_attribute_type']);
if ($value !== NULL) {
if (isset($new_attributes_r[$key])) {
if (!is_array($new_attributes_r[$key])) {
$new_attributes_r[$key] = array($new_attributes_r[$key]);
}
if (is_array($value)) {
$new_attributes_r[$key] = array_merge($new_attributes_r[$key], $value);
} else {
$new_attributes_r[$key][] = $value;
}
} else {
$new_attributes_r[$key] = $value;
}
}
if ($attribute_type_map_r['lookup_attribute_val_restrict_ind'] == 'Y') {
$lookup_attribute_val_restrict_ind_r[$key] = 'Y';
}
}
db_free_result($results);
}
// now for any variables that do not have a mapping, add them to the $new_attributes_r
reset($site_item_attributes_r);
while (list($key, $value) = @each($site_item_attributes_r)) {
$key = strtolower($key);
if (isset($new_attributes_r[$key])) {
$oldValue = NULL;
// we want the direct mapping attributes first.
if (is_array($new_attributes_r[$key])) {
$oldValue = $new_attributes_r[$key];
} else {
$oldValue[] = $new_attributes_r[$key];
}
unset($new_attributes_r[$key]);
if (is_array($value)) {
$new_attributes_r[$key] = $value;
} else {
$new_attributes_r[$key][] = $value;
}
while (list(, $value) = each($oldValue)) {
if (!in_array($value, $new_attributes_r[$key])) {
$new_attributes_r[$key][] = $value;
}
}
} else {
if (!in_array($key, $mapped_attributes_r)) {
$new_attributes_r[$key] = $value;
}
}
}
$site_item_attributes_r = NULL;
// now we need to check to see if any lookup mappings exist for each
// of the attribute values, and update the $value's appropriately.
reset($new_attributes_r);
while (list($key, $value) = @each($new_attributes_r)) {
// temporary UPPER so we can work with actual s_attribute_type records in database
$s_attribute_type = strtoupper($key);
if (is_lookup_attribute_type($s_attribute_type)) {
$values_r = NULL;
// if a lookup attribute type, we want to make sure that the $value's
// are all arrays anyway, so lets do that check each time.
if (is_array($value)) {
$values_r = $value;
} else {
$values_r[] = $value;
}
$results = fetch_site_plugin_s_attribute_type_lookup_map_rs($site_type, $s_attribute_type);
if ($results) {
$found_entries_r = array();
$new_values_r = array();
while ($lookup_map_r = db_fetch_assoc($results)) {
for ($i = 0; $i < count($values_r); $i++) {
if (strcasecmp($values_r[$i], $lookup_map_r['value']) === 0) {
$found_entries_r[] = $values_r[$i];
if (!in_array($lookup_map_r['lookup_attribute_val'], $new_values_r)) {
$new_values_r[] = $lookup_map_r['lookup_attribute_val'];
}
}
}
}
db_free_result($results);
// now process all back into $values_r
for ($i = 0; $i < count($values_r); $i++) {
if (!in_array($values_r[$i], $found_entries_r) && !in_array($values_r[$i], $new_values_r)) {
$new_values_r[] = $values_r[$i];
}
}
$values_r = $new_values_r;
}
//if($results)
// now reassign back.
$site_item_attributes_r[strtolower($s_attribute_type)] = $values_r;
} else {
// the next process prefers arrays to deal with, even if single element
$site_item_attributes_r[strtolower($s_attribute_type)] = $value;
}
}
//
// now that we have expanded mappings, we need to map to s_item_attribute_type order number mappings
//
$new_attributes_r = $site_item_attributes_r;
$site_item_attributes_r = NULL;
// now we want to expand the $new_attributes_r, so we have a set of
// variables that include the order_no $fieldname type format.
$results = fetch_item_attribute_type_rs($s_item_type, NULL, 's_attribute_type');
if ($results) {
// this will be set if array encountered, but not lookup value.
$processing_s_attribute_type = FALSE;
while ($attribute_type_r = db_fetch_assoc($results)) {
$variable = strtolower($attribute_type_r['s_attribute_type']);
if (isset($new_attributes_r[$variable])) {
$fieldname = get_field_name($attribute_type_r['s_attribute_type'], $attribute_type_r['order_no']);
if (is_not_empty_array($new_attributes_r[$variable])) {
// TODO: Consider adding values not found in the lookup table to the s_attribute_type_lookup.
if (is_lookup_attribute_type($attribute_type_r['s_attribute_type'])) {
$lookup_attribute_val_restrict_ind = $lookup_attribute_val_restrict_ind_r[strtolower($attribute_type_r['s_attribute_type'])];
// here is where we want some sanity checking of the options
$value_r = $new_attributes_r[$variable];
$lookup_value_r = array();
for ($i = 0; $i < count($value_r); $i++) {
$raw_value = trim($value_r[$i]);
if (strlen($raw_value) > 0) {
$value = fetch_attribute_type_lookup_value($attribute_type_r['s_attribute_type'], $raw_value);
if ($value !== FALSE) {
$lookup_value_r[] = $value;
} else {
if ($lookup_attribute_val_restrict_ind != 'Y') {
// do not include if restricted to lookup values
$lookup_value_r[] = $raw_value;
}
}
}
}
$site_item_attributes_r[$fieldname] = array_unique($lookup_value_r);
} else {
// This indicates we have a repeated s_attribute_type, and so should act appropriately.
if ($processing_s_attribute_type != NULL && $attribute_type_r['s_attribute_type'] == $processing_s_attribute_type) {
$site_item_attributes_r[$fieldname] = $new_attributes_r[$variable][0];
// remove it
array_splice($new_attributes_r[$variable], 0, 1);
} else {
if (count($new_attributes_r[$variable]) > 1) {
// this is the first occurence of the s_attribute_type, so lets see if its repeated at least once.
if (is_numeric(fetch_s_item_attribute_type_next_order_no($s_item_type, $attribute_type_r['s_attribute_type'], $attribute_type_r['order_no']))) {
$site_item_attributes_r[$fieldname] = $new_attributes_r[$variable][0];
// remove it
array_splice($new_attributes_r[$variable], 0, 1);
$processing_s_attribute_type = $attribute_type_r['s_attribute_type'];
} else {
// otherwise just copy the whole thing.
$site_item_attributes_r[$fieldname] = $new_attributes_r[$variable];
unset($new_attributes_r[$variable]);
}
} else {
$site_item_attributes_r[$fieldname] = $new_attributes_r[$variable][0];
unset($new_attributes_r[$variable]);
}
}
}
} else {
if (!is_array($new_attributes_r[$variable])) {
$site_item_attributes_r[$fieldname] = $new_attributes_r[$variable];
unset($new_attributes_r[$variable]);
}
}
} else {
if ($attribute_type_r['s_field_type'] == 'TITLE' && isset($new_attributes_r['title'])) {
// in case developer forgot to setup title mapping.
$fieldname = get_field_name($attribute_type_r['s_attribute_type'], $attribute_type_r['order_no']);
$site_item_attributes_r[$fieldname] = $new_attributes_r['title'];
}
}
}
//while
db_free_result($results);
}
return $site_item_attributes_r;
}
