tablePrefix);
define("SEITENANZAHL", 5);
define("DATACOUNT", 30);
define("IMPORTDATAPFAD", DATEIPFAD . "admin/data/import/");
define("EXPORTDATAPFAD", DATEIPFAD . "admin/data/export/");
define("TEMPDATAPFAD", DATEIPFAD . "admin/data/temp/");
define("CRLF", chr(13) . chr(10));
define("TAB", chr(9));
define("RELATIONTYPE_11", 1);
define("RELATIONTYPE_1N", 2);
define("RELATIONTYPE_NM", 3);
define("FIELDTYPE_NULL", 0);
define("FIELDTYPE_DATABASE", 1);
define("FIELDTYPE_DEFAULT", 2);
define("FIELDTYPE_SELECTION", 3);
define("FIELDTYPE_DATABASE_DEFAULT", 4);
define("FIELDTYPE_TABLEDEFAULT", 5);
define("OPTION_INSERT_TRUE", 1);
define("OPTION_INSERT_DEFAULT", 2);
define("OPTION_INSERT_MAPPING", 3);
define("IMPORT_STATUS_NOCHANGE", 1);
define("IMPORT_STATUS_UPDATE", 2);
define("IMPORT_STATUS_INSERT", 3);
define("IMPORT_STATUS_ERROR", 4);
define("IMPORT_STATUS_NOINSERT", 5);
define("IMPORT_STATUS_EXPORT", 7);
define("IMPORT_STATUS_NOEXPORTSELECTION", 8);
define("DATAEXCHANGE_TYPE_IMPORT", 1);
define("DATAEXCHANGE_TYPE_EXPORT", 2);
define("SPECIALFUNCTION_PRICECALCULATIONNET", 1);
define("SPECIALFUNCTION_PRICECALCULATIONGROSS", 2);
// $DataexchangeSettingArray[] = "expert_mode";
// $DataexchangeSettingArray[] = "history_delete_interval";
// $DataexchangeSettingArray[] = "show_db_details";
// $DataexchangeSettingArray[] = "show_import_history_details";
// $DataexchangeSettingArray[] = "show_import_history_status";
// $DataexchangeSettingArray[] = "show_export_history_details";
// $DataexchangeSettingArray[] = "show_export_history_status";
function GetTabledefaultFieldDataArray() {
$SQLString = 'SELECT ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.main_table, ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.main_field, ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.tabledefault_value ';
$SQLString .= 'FROM ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ' ';
$SQLString .= 'WHERE ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.field_type = \'' . FIELDTYPE_TABLEDEFAULT . '\' ';
$MySQLQueryReference = mysql_query($SQLString);
while ($TabledefaultFieldRow = mysql_fetch_array($MySQLQueryReference, MYSQL_ASSOC)) {
$TabledefaultFieldDataArray[TABLE_PREFIX . $TabledefaultFieldRow['main_table']][$TabledefaultFieldRow['main_field']] = $TabledefaultFieldRow['tabledefault_value'];
}
return $TabledefaultFieldDataArray;
}
function GetSpecialFunctionDataArray($DataExchangeType, $FilterID) {
$SpecialFunctionDataArray = array();
$SQLString = 'SELECT ';
$SQLString .= TABLE_DATAEXCHANGE_SPECIALFUNCTIONS . '.specialfunction_id, ';
$SQLString .= TABLE_DATAEXCHANGE_SPECIALFUNCTIONS . '.name, ';
$SQLString .= TABLE_DATAEXCHANGE_SPECIALFUNCTIONS . '.description, ';
$SQLString .= '!ISNULL(' . TABLE_DATAEXCHANGE_FILTERS_SPECIALFUNCTIONS . '.filter_id) AS specialfunction_selected ';
$SQLString .= 'FROM ';
$SQLString .= TABLE_DATAEXCHANGE_SPECIALFUNCTIONS . ' ';
$SQLString .= 'LEFT JOIN ' . TABLE_DATAEXCHANGE_FILTERS_SPECIALFUNCTIONS . ' ON ((' . TABLE_DATAEXCHANGE_SPECIALFUNCTIONS . '.specialfunction_id = ' . TABLE_DATAEXCHANGE_FILTERS_SPECIALFUNCTIONS . '.specialfunction_id) AND (' . TABLE_DATAEXCHANGE_FILTERS_SPECIALFUNCTIONS . '.filter_id = \'' . $FilterID . '\')) ';
$SQLString .= 'WHERE ';
$SQLString .= TABLE_DATAEXCHANGE_SPECIALFUNCTIONS . '.dataexchange_type = \'' . $DataExchangeType . '\' ';
// echo '$SQLString: ' . $SQLString . '
';
$MySQLQueryReference = mysql_query($SQLString);
while ($SpecialFunctionRow = mysql_fetch_array($MySQLQueryReference, MYSQL_ASSOC)) {
$SpecialFunctionDataArray[] = $SpecialFunctionRow;
}
return $SpecialFunctionDataArray;
}
function GetGroupedFieldAnzahl($ListArray) {
if ($ListArray["groupby"] == "filter") {
return GetFilterGroupedFieldAnzahl($ListArray);
} else {
return GetTablenameGroupedFieldAnzahl($ListArray);
}
}
function GetTablenameGroupedFieldAnzahl($ListArray) {
$SQLString = "SELECT ";
$SQLString .= "COUNT(temp_dataexchange_fields.field_id) AS field_anzahl ";
$SQLString .= "FROM ";
$SQLString .= "temp_dataexchange_fields ";
$SQLString .= "WHERE (";
// Suche
if ($ListArray["searchfield"] && $ListArray["searchstring"]) {
$SQLString .= "(";
$SQLString .= "(" . $ListArray["searchfield"] . " LIKE '%" . $ListArray["searchstring"] . "%') OR ";
$SQLString .= "(temp_dataexchange_fields.field_id = -1) ";
$SQLString .= ") AND ";
}
$SQLString .= " 1) ";
// echo '$SQLString: ' . $SQLString . '
';
$FieldAnzahlObject = mysql_fetch_object(mysql_query($SQLString));
return $FieldAnzahlObject->field_anzahl;
}
function GetTableSelect($TableDataArray, $SelectName, $SelectedTable, $TargetFieldsArray) {
global $a_de_field_please_select;
foreach ($TargetFieldsArray as $TargetFieldsElement) {
$tpl_onchange .= "SelectTable('" . $TargetFieldsElement[0] . "', this.value, '" . $TargetFieldsElement[1] . "');";
}
$tpl_table_select = "";
return $tpl_table_select;
}
function GetFieldSelect($TableFieldDataArray, $SelectName, $SelectedTable, $SelectedField) {
global $a_de_field_please_select;
if ($SelectedTable) {
$tpl_field_select = "";
} else {
$tpl_field_select = "";
}
return $tpl_field_select;
}
function DeleteMappingTable($FieldID) {
$SQLString = "DELETE FROM " . TABLE_DATAEXCHANGE_FIELDS_MAPPING . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_MAPPING . ".field_id = '" . $FieldID . "' ";
$MySQLQuery = mysql_query($SQLString);
$SQLString = "UPDATE " . TABLE_DATAEXCHANGE_FIELDS . " SET ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".option_insert = 0, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".mapping_default_value = '' ";
$SQLString = "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".field_id = '" . $FieldID . "' ";
$MySQLQuery = mysql_query($SQLString);
}
function GetGroupedFieldDataArray($ListArray) {
if ($ListArray["groupby"] == "filter") {
return GetFilterGroupedFieldDataArray($ListArray);
} else {
if (array_search(FIELDTYPE_DEFAULT, $ListArray["field_type_array"]) !== false) {
unset($ListArray["field_type_array"][array_search(FIELDTYPE_DEFAULT, $ListArray["field_type_array"])]);
}
return GetTablenameGroupedFieldDataArray($ListArray);
}
}
function GetTablenameGroupedFieldDataArray($ListArray) {
DeleteMappingTable(0);
global $a_de_field_all_fields;
global $TableFieldTranslationDataArray;
$TableFieldTranslationDataArray = GetTableFieldTranslationDataArray();
/* temporaere Tabelle anlegen
*
* Um Aktionen wie Sortierung oder Limitierung der Datensaetze besser ausfuehren zu
* koennen, wird hier mit einer temporaeren Tabelle gearbeitet, die im Ablauf dieser
* Funktion mit den gewuenschten Daten gefuellt wird.
*/
$SQLString = "CREATE TEMPORARY TABLE temp_dataexchange_fields (";
$SQLString .= "temp_dataexchange_fields.field_id INT NOT NULL, ";
$SQLString .= "temp_dataexchange_fields.rs_field_id INT NOT NULL, ";
$SQLString .= "temp_dataexchange_fields.field_name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, ";
$SQLString .= "temp_dataexchange_fields.table_name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, ";
$SQLString .= "temp_dataexchange_fields.field_table_path VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, ";
$SQLString .= "temp_dataexchange_fields.tabledefault_value VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, ";
$SQLString .= "temp_dataexchange_fields.field_type INT NOT NULL, ";
$SQLString .= "temp_dataexchange_fields.field_type_name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, ";
$SQLString .= "PRIMARY KEY (table_name, field_id) ";
$SQLString .= ")";
mysql_query($SQLString);
/* Filter ueber die Suche vorselektieren
*
* Hier werden bei einer Suchanfrage schon mal im Vorfeld die Filter durchsucht.
* Aus folgenden Gruenden ist dies notwendig:
* - Pruefung, ob es ueberhaupt ein Suchergbnis gibt
* - automatisches Aufklappen der Filter
*
*/
if (($ListArray["searchfield"] && $ListArray["searchstring"]) &&
($ListArray["searchfield"] == "temp_dataexchange_fields.field_name")) {
$SQLString = "SELECT DISTINCT ";
$SQLString .= "IF(((" . TABLE_DATAEXCHANGE_FIELDS . ".relation_type = " . RELATIONTYPE_11 . ") AND (" . TABLE_DATAEXCHANGE_FIELDS . ".option_language)), " . TABLE_DATAEXCHANGE_FIELDS . ".language_table, " . TABLE_DATAEXCHANGE_FIELDS . ".main_table) AS table_name ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".field_name LIKE '%" . $ListArray["searchstring"] . "%' ";
// echo '$SQLString: ' . $SQLString . '
';
$MySQLQueryReference = mysql_query($SQLString);
while ($TabelNameRow = mysql_fetch_array($MySQLQueryReference, MYSQL_ASSOC)) {
$SearchTableNameArray[] = $TabelNameRow["table_name"];
$ListArray["treeelement_id_array"][$TabelNameRow["table_name"]] = $TabelNameRow["table_name"];
$_SESSION[ACTLISTPREFIX . "_list"]["treeelement_id_array"][$TabelNameRow["table_name"]] = $TabelNameRow["table_name"];
}
if (!$SearchTableNameArray) {
return array();
}
}
/* Pruefung, ob ueberhaupt Daten da sind
*
* Hier wird geprueft, ob ueberhaupt Daten vorhanden sind. Dies ist notwendig, da im
* spaeteren Verlauf bei geschlossenen Filter keine Felder mehr explizit eingelesen
* werden.
*
*/
$SQLString = "SELECT ";
$SQLString .= "COUNT(" . TABLE_DATAEXCHANGE_FIELDS . ".field_id) ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . " ";
// Filtertype
if (is_array($ListArray['field_type_array'])) {
$SQLString .= "WHERE ";
if (count($ListArray['field_type_array']) == 0) {
$SQLString .= "(" . TABLE_DATAEXCHANGE_FIELDS . ".field_type IN (0)) ";
} else {
$SQLString .= "(" . TABLE_DATAEXCHANGE_FIELDS . ".field_type IN (" . implode(",", $ListArray['field_type_array']) . ")) ";
}
}
$SQLString .= "GROUP BY ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".dataexchange_type ";
$CountObject = mysql_fetch_object(mysql_query($SQLString));
if (!$CountObject) {
return array();
}
// alle Felder anzeigen
if (($ListArray["searchfield"] && $ListArray["searchstring"]) && ($ListArray["formaction"] == "search") && ($ListArray["searchfield"] == "temp_dataexchange_fields.field_name")) {
if ($ListArray["searchfield"] == "temp_dataexchange_fields.field_name") {
$FieldSearchfield = TABLE_DATAEXCHANGE_FIELDS . ".field_name";
}
$FieldDataArray = GetFieldDataArray($FieldSearchfield, $ListArray["searchstring"], TABLE_DATAEXCHANGE_FIELDS . ".field_name", "ASC", "", "", "", "", "", $ListArray["field_type_array"]);
foreach ($FieldDataArray as $FieldData) {
$SQLString = "INSERT INTO temp_dataexchange_fields SET ";
$SQLString .= "temp_dataexchange_fields.field_id = '" . $FieldData["field_id"] . "', ";
$SQLString .= "temp_dataexchange_fields.rs_field_id = '" . $FieldData["rs_field_id"] . "', ";
$SQLString .= "temp_dataexchange_fields.field_name = '" . $FieldData["field_name"] . "', ";
$SQLString .= "temp_dataexchange_fields.tabledefault_value = '" . $FieldData["tabledefault_value"] . "', ";
$SQLString .= "temp_dataexchange_fields.table_name = '" . $a_de_im_field_all_fields . "', ";
$SQLString .= "temp_dataexchange_fields.field_table_path = '" . $FieldData["field_table_path"] . "', ";
$SQLString .= "temp_dataexchange_fields.field_type = '" . $FieldData["field_type"] . "', ";
$SQLString .= "temp_dataexchange_fields.field_type_name = '" . $FieldData["field_type_name"] . "' ";
mysql_query($SQLString);
}
if ($FieldDataArray) {
$ListArray["treeelement_id_array"][-1] = -1;
$_SESSION[ACTLISTPREFIX . "_list"]["treeelement_id_array"][-1] = -1;
}
} elseif (in_array(-1, $ListArray["treeelement_id_array"])) {
$FieldDataArray = GetFieldDataArray("", "", TABLE_DATAEXCHANGE_FIELDS . ".field_name", "ASC", "", "", "", "", "", $ListArray["field_type_array"]);
foreach ($FieldDataArray as $FieldData) {
$SQLString = "INSERT INTO temp_dataexchange_fields SET ";
$SQLString .= "temp_dataexchange_fields.field_id = '" . $FieldData["field_id"] . "', ";
$SQLString .= "temp_dataexchange_fields.rs_field_id = '" . $FieldData["rs_field_id"] . "', ";
$SQLString .= "temp_dataexchange_fields.field_name = '" . $FieldData["field_name"] . "', ";
$SQLString .= "temp_dataexchange_fields.tabledefault_value = '" . $FieldData["tabledefault_value"] . "', ";
$SQLString .= "temp_dataexchange_fields.table_name = '" . $a_de_field_all_fields . "', ";
$SQLString .= "temp_dataexchange_fields.field_table_path = '" . $FieldData["field_table_path"] . "', ";
$SQLString .= "temp_dataexchange_fields.field_type = '" . $FieldData["field_type"] . "', ";
$SQLString .= "temp_dataexchange_fields.field_type_name = '" . $FieldData["field_type_name"] . "' ";
mysql_query($SQLString);
}
} else {
$SQLString = "INSERT INTO temp_dataexchange_fields SET ";
$SQLString .= "temp_dataexchange_fields.field_id = '-1', ";
$SQLString .= "temp_dataexchange_fields.rs_field_id = '-1', ";
$SQLString .= "temp_dataexchange_fields.field_name = '', ";
$SQLString .= "temp_dataexchange_fields.tabledefault_value = '', ";
$SQLString .= "temp_dataexchange_fields.table_name = '" . $a_de_field_all_fields . "', ";
$SQLString .= "temp_dataexchange_fields.field_table_path = '" . $FieldData["field_table_path"] . "', ";
$SQLString .= "temp_dataexchange_fields.field_type = '-1', ";
$SQLString .= "temp_dataexchange_fields.field_type_name = '' ";
mysql_query($SQLString);
}
// alle Felder einlesen
if ($ListArray["searchfield"] == "temp_dataexchange_fields.field_name") {
$FieldSearchfield = TABLE_DATAEXCHANGE_FIELDS . ".field_name";
}
$FieldDataArray = GetFieldDataArray($FieldSearchfield, $ListArray["searchstring"], TABLE_DATAEXCHANGE_FIELDS . ".field_name", "ASC", "", "", "", "", $SearchTableNameArray, $ListArray["field_type_array"]);
$InsertClosedTableNameArray = array();
foreach ($FieldDataArray as $FieldData) {
if ($FieldData["option_language"] && ($FieldData["relation_type"] == RELATIONTYPE_11)) {
$TableName = $FieldData["language_table"];
} else {
$TableName = $FieldData["main_table"];
}
if (in_array($TableName, $ListArray["treeelement_id_array"])) {
$SQLString = "INSERT INTO temp_dataexchange_fields SET ";
$SQLString .= "temp_dataexchange_fields.field_id = '" . $FieldData["field_id"] . "', ";
$SQLString .= "temp_dataexchange_fields.rs_field_id = '" . $FieldData["rs_field_id"] . "', ";
$SQLString .= "temp_dataexchange_fields.field_name = '" . $FieldData["field_name"] . "', ";
$SQLString .= "temp_dataexchange_fields.tabledefault_value = '" . $FieldData["tabledefault_value"] . "', ";
$SQLString .= "temp_dataexchange_fields.table_name = '" . $TableName . "', ";
$SQLString .= "temp_dataexchange_fields.field_table_path = '" . $FieldData["field_table_path"] . "', ";
$SQLString .= "temp_dataexchange_fields.field_type = '" . $FieldData["field_type"] . "', ";
$SQLString .= "temp_dataexchange_fields.field_type_name = '" . $FieldData["field_type_name"] . "' ";
mysql_query($SQLString);
} elseif (!in_array($TableName, $InsertClosedTableNameArray)) {
$SQLString = "INSERT INTO temp_dataexchange_fields SET ";
$SQLString .= "temp_dataexchange_fields.field_id = '-1', ";
$SQLString .= "temp_dataexchange_fields.rs_field_id = '-1', ";
$SQLString .= "temp_dataexchange_fields.field_name = '', ";
$SQLString .= "temp_dataexchange_fields.tabledefault_value = '', ";
$SQLString .= "temp_dataexchange_fields.table_name = '" . $TableName . "', ";
$SQLString .= "temp_dataexchange_fields.field_table_path = '', ";
$SQLString .= "temp_dataexchange_fields.field_type = '-1', ";
$SQLString .= "temp_dataexchange_fields.field_type_name = '' ";
mysql_query($SQLString);
$InsertClosedTableNameArray[] = $TableName;
}
}
$SQLString = "SELECT ";
$SQLString .= "temp_dataexchange_fields.field_id, ";
$SQLString .= "temp_dataexchange_fields.rs_field_id, ";
$SQLString .= "temp_dataexchange_fields.field_name, ";
$SQLString .= "temp_dataexchange_fields.table_name, ";
$SQLString .= "temp_dataexchange_fields.tabledefault_value, ";
$SQLString .= "temp_dataexchange_fields.field_table_path, ";
$SQLString .= "temp_dataexchange_fields.field_type, ";
$SQLString .= "temp_dataexchange_fields.field_type_name ";
$SQLString .= "FROM ";
$SQLString .= "temp_dataexchange_fields ";
$SQLString .= "WHERE (";
// Suche
if ($ListArray["searchfield"] && $ListArray["searchstring"]) {
$SQLString .= "(";
$SQLString .= "(" . $ListArray["searchfield"] . " LIKE '%" . $ListArray["searchstring"] . "%') OR ";
$SQLString .= "(temp_dataexchange_fields.field_id = -1) ";
$SQLString .= ") AND ";
}
$SQLString .= " 1) ";
// Sortierung
if ($ListArray["sortfield"] && $ListArray["sortorder"]) {
$SQLString .= "ORDER BY temp_dataexchange_fields.table_name ASC, " . $ListArray["sortfield"] . " " . $ListArray["sortorder"] . " ";
} else {
$SQLString .= "ORDER BY temp_dataexchange_fields.table_name ASC ";
}
// Limitierung
if (($ListArray["dataoffset"] !== '') && ($ListArray["datacount"] !== '')) {
$SQLString .= "LIMIT " . $ListArray["dataoffset"] . ", " . $ListArray["datacount"] . " ";
}
// echo '$SQLString: ' . $SQLString . '
';
$MySQLQueryReference = mysql_query($SQLString);
$FieldDataArray = array();
while ($FieldRow = mysql_fetch_array($MySQLQueryReference, MYSQL_ASSOC)) {
$FieldDataArray[] = $FieldRow;
}
// echo '';
// var_dump($FieldDataArray);
// echo '
';
return $FieldDataArray;
}
function UpdateDataexchangeInterval() {
// Einstellungen
$Einstellungen = GetEinstellungen('', 'dataexchange');
if (strtotime($Einstellungen->dataexchange->last_update) + ($Einstellungen->dataexchange->update_interval * 24 * 60 * 60) < time()) {
SaveKeyValueEinstellungen('dataexchange', 'last_update', date('Y-m-d H:i:s'));
}
}
function UpdateDataexchange() {
global $a_de_settings_update_message, $a_de_settings_update_message_table_field;
global $a_de_settings_update_message_field_update, $a_de_settings_update_message_field_insert, $a_de_settings_update_message_field_delete;
global $a_de_settings_update_message_filter_update, $a_de_settings_update_message_filter_insert, $a_de_settings_update_message_filter_delete;
global $a_de_settings_update_message_error;
// Einstellungen
$Einstellungen = GetEinstellungen('', 'dataexchange');
try {
// Parameter
$UpdateServerURL = $Einstellungen->dataexchange->update_server . '/index.php';
$PostArray['module'] = file_exists(DATEIPFAD . 'includes/functions.mod.dataexchange.inc.php') ? 1 : 0;
$PostArray['last_update'] = $Einstellungen->dataexchange->last_update;
$PostArray['api_version'] = 0.9;
$PostArray['api_action'] = 'update_dataexchange';
foreach ($PostArray as $PostKey => $PostValue) {
$GetString .= $PostKey . '=' . $PostValue . '&';
}
$GetString = substr($GetString, 0, strlen($GetString) - 1);
$CurlHandler = curl_init();
curl_setopt($CurlHandler, CURLOPT_URL, $UpdateServerURL . '?' . $GetString);
curl_setopt($CurlHandler, CURLOPT_USERAGENT, $_SERVER['HTTP_USER_AGENT']);
// curl_setopt($CurlHandler, CURLOPT_POST, 1);
// curl_setopt($CurlHandler, CURLOPT_POSTFIELDS, $PostArray);
curl_setopt($CurlHandler, CURLOPT_RETURNTRANSFER, true);
curl_setopt($CurlHandler, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($CurlHandler, CURLOPT_FOLLOWLOCATION, true);
$ReturnString = curl_exec($CurlHandler);
$UpdateDataexchangeDataArray = json_decode($ReturnString, true);
// echo '';
// var_dump($ReturnString);
// echo '
';
if (!$UpdateDataexchangeDataArray) {
throw new Exception();
}
$FieldCounterInsert = 0;
$FieldCounterUpdate = 0;
$FieldCounterDelete = 0;
// Tabellenbeschreibungen
$SQLString = 'TRUNCATE ' . TABLE_DATAEXCHANGE_TABLES . ' ';
$MySQLQueryReference = errorlogged_mysql_query($SQLString);
foreach ($UpdateDataexchangeDataArray['table_array'] as $TableData) {
$SQLString = 'INSERT INTO ' . TABLE_DATAEXCHANGE_TABLES . ' SET ';
$SQLString .= TABLE_DATAEXCHANGE_TABLES . '.table_name = \'' . $TableData['table_name'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_TABLES . '.table_caption = \'' . $TableData['table_caption'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_TABLES . '.table_idfield = \'' . $TableData['table_idfield'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_TABLES . '.default_idfield = \'' . $TableData['default_idfield'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_TABLES . '.default_tablelangu = \'' . $TableData['default_tablelangu'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_TABLES . '.default_languageidfield = \'' . $TableData['default_languageidfield'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_TABLES . '.default_tablerelation = \'' . $TableData['default_tablerelation'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_TABLES . '.default_relationidfield = \'' . $TableData['default_relationidfield'] . '\' ';
$MySQLQueryReference = errorlogged_mysql_query($SQLString);
}
// Feldbeschreibungen
$SQLString = 'TRUNCATE ' . TABLE_DATAEXCHANGE_TABLES_FIELDS . ' ';
$MySQLQueryReference = errorlogged_mysql_query($SQLString);
foreach ($UpdateDataexchangeDataArray['table_field_array'] as $TableFieldData) {
$SQLString = 'INSERT INTO ' . TABLE_DATAEXCHANGE_TABLES_FIELDS . ' SET ';
$SQLString .= TABLE_DATAEXCHANGE_TABLES_FIELDS . '.table_name = \'' . $TableFieldData['table_name'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_TABLES_FIELDS . '.field_name = \'' . $TableFieldData['field_name'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_TABLES_FIELDS . '.field_caption = \'' . $TableFieldData['field_caption'] . '\' ';
$MySQLQueryReference = errorlogged_mysql_query($SQLString);
}
// Felder
foreach ($UpdateDataexchangeDataArray['field_array'] as $FieldData) {
$FieldUpdate = false;
$FieldInsert = false;
$FieldDelete = false;
$SQLString = 'SELECT ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.field_id ';
$SQLString .= 'FROM ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ' ';
$SQLString .= 'WHERE ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.rs_field_id = \'' . $FieldData['rs_field_id'] . '\' ';
$FieldObject = mysql_fetch_object(errorlogged_mysql_query($SQLString));
if ($FieldObject) {
if (!$FieldData['deleted']) {
$SQLString = 'UPDATE ' . TABLE_DATAEXCHANGE_FIELDS . ' SET ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.field_type = \'' . $FieldData['field_type'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.field_name = \'' . $FieldData['field_name'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.field_csv_caption = \'' . $FieldData['field_csv_caption'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.field_alias = \'' . $FieldData['field_alias'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.dataexchange_type = \'' . $FieldData['dataexchange_type'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.relation_type = \'' . $FieldData['relation_type'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.option_mandatory = \'' . $FieldData['option_mandatory'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.option_identifier = \'' . $FieldData['option_identifier'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.option_insert = \'' . $FieldData['option_insert'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.option_insert_default_value = \'' . $FieldData['option_insert_default_value'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.option_language = \'' . $FieldData['option_language'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.option_striplinebreak = \'' . $FieldData['option_striplinebreak'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.option_striptags = \'' . $FieldData['option_striptags'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.option_trim = \'' . $FieldData['option_trim'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.regexp_search = \'' . addslashes($FieldData['regexp_search']) . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.regexp_insert = \'' . addslashes($FieldData['regexp_insert']) . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.mapping_default_value = \'' . $FieldData['mapping_default_value'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.main_table = \'' . $FieldData['main_table'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.main_field = \'' . $FieldData['main_field'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.main_idfield = \'' . $FieldData['main_idfield'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.relation_table = \'' . $FieldData['relation_table'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.relation_main_idfield = \'' . $FieldData['relation_main_idfield'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.relation_target_idfield = \'' . $FieldData['relation_target_idfield'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.target_table = \'' . $FieldData['target_table'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.target_field = \'' . $FieldData['target_field'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.target_idfield = \'' . $FieldData['target_idfield'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.language_table = \'' . $FieldData['language_table'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.language_field = \'' . $FieldData['language_field'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.language_idfield = \'' . $FieldData['language_idfield'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.language_languagefield = \'' . $FieldData['language_languagefield'] . '\' ';
$SQLString .= 'WHERE ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.rs_field_id = \'' . $FieldData['rs_field_id'] . '\' ';
$MySQLQueryReference = errorlogged_mysql_query($SQLString);
if (mysql_affected_rows() > 0) {
$FieldCounterUpdate++;
$FieldUpdate = true;
}
$FieldID = $FieldObject->field_id;
} else {
DeleteField($FieldObject->field_id);
$FieldCounterDelete++;
$FieldDelete = true;
$FieldID = $FieldObject->field_id;
}
} elseif (!$FieldData['deleted']) {
$SQLString = 'INSERT INTO ' . TABLE_DATAEXCHANGE_FIELDS . ' SET ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.rs_field_id = \'' . $FieldData['rs_field_id'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.field_type = \'' . $FieldData['field_type'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.field_name = \'' . $FieldData['field_name'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.field_csv_caption = \'' . $FieldData['field_csv_caption'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.field_alias = \'' . $FieldData['field_alias'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.dataexchange_type = \'' . $FieldData['dataexchange_type'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.relation_type = \'' . $FieldData['relation_type'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.option_mandatory = \'' . $FieldData['option_mandatory'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.option_identifier = \'' . $FieldData['option_identifier'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.option_insert = \'' . $FieldData['option_insert'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.option_insert_default_value = \'' . $FieldData['option_insert_default_value'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.option_language = \'' . $FieldData['option_language'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.option_striplinebreak = \'' . $FieldData['option_striplinebreak'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.option_striptags = \'' . $FieldData['option_striptags'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.regexp_search = \'' . addslashes($FieldData['regexp_search']) . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.regexp_insert = \'' . addslashes($FieldData['regexp_insert']) . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.mapping_default_value = \'' . $FieldData['mapping_default_value'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.main_table = \'' . $FieldData['main_table'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.main_field = \'' . $FieldData['main_field'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.main_idfield = \'' . $FieldData['main_idfield'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.relation_table = \'' . $FieldData['relation_table'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.relation_main_idfield = \'' . $FieldData['relation_main_idfield'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.relation_target_idfield = \'' . $FieldData['relation_target_idfield'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.target_table = \'' . $FieldData['target_table'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.target_field = \'' . $FieldData['target_field'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.target_idfield = \'' . $FieldData['target_idfield'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.language_table = \'' . $FieldData['language_table'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.language_field = \'' . $FieldData['language_field'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.language_idfield = \'' . $FieldData['language_idfield'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.language_languagefield = \'' . $FieldData['language_languagefield'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.tabledefault_value = \'' . $FieldData['tabledefault_value'] . '\' ';
// echo '$SQLString: ' . $SQLString . '
';
$MySQLQueryReference = errorlogged_mysql_query($SQLString);
$FieldCounterInsert++;
$FieldInsert = true;
$FieldID = mysql_insert_id();
}
// Selektionsbedingungen
if (is_array($FieldData['field_selection_array']) && !$FieldDelete) {
foreach ($FieldData['field_selection_array'] as $FieldSelectionData) {
$SQLString = 'SELECT ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_SELECTION . '.selection_id ';
$SQLString .= 'FROM ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_SELECTION . ' ';
$SQLString .= 'WHERE ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_SELECTION . '.rs_selection_id = \'' . $FieldSelectionData['rs_selection_id'] . '\' ';
$FieldSelectionObject = mysql_fetch_object(errorlogged_mysql_query($SQLString));
if ($FieldSelectionObject) {
if (!$FieldSelectionData['deleted']) {
$SQLString = 'UPDATE ' . TABLE_DATAEXCHANGE_FIELDS_SELECTION . ' SET ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_SELECTION . '.operation_comparison = \'' . $FieldSelectionData['operation_comparison'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_SELECTION . '.operation_logical = \'' . $FieldSelectionData['operation_logical'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_SELECTION . '.operation_value = \'' . $FieldSelectionData['operation_value'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_SELECTION . '.sort = \'' . $FieldSelectionData['sort'] . '\' ';
$SQLString .= 'WHERE ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_SELECTION . '.rs_selection_id = \'' . $FieldSelectionData['rs_selection_id'] . '\' ';
$MySQLQueryReference = errorlogged_mysql_query($SQLString);
if (mysql_affected_rows() > 0) {
if (!$FieldInsert && !$FieldUpdate) {
$FieldCounterUpdate++;
$FieldUpdate = true;
}
}
} else {
DeleteSelection($FieldSelectionObject->selection_id);
if (!$FieldInsert && !$FieldUpdate) {
$FieldCounterUpdate++;
$FieldUpdate = true;
}
}
} elseif (!$FieldSelectionData['deleted']) {
$SQLString = 'INSERT INTO ' . TABLE_DATAEXCHANGE_FIELDS_SELECTION . ' SET ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_SELECTION . '.rs_selection_id = \'' . $FieldSelectionData['rs_selection_id'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_SELECTION . '.field_id = \'' . $FieldID . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_SELECTION . '.operation_comparison = \'' . $FieldSelectionData['operation_comparison'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_SELECTION . '.operation_logical = \'' . $FieldSelectionData['operation_logical'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_SELECTION . '.operation_value = \'' . $FieldSelectionData['operation_value'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_SELECTION . '.sort = \'' . $FieldSelectionData['sort'] . '\' ';
$MySQLQueryReference = errorlogged_mysql_query($SQLString);
if (!$FieldInsert && !$FieldUpdate) {
$FieldCounterUpdate++;
$FieldUpdate = true;
}
}
}
}
// Mapping
if (is_array($FieldData['field_mapping_array']) && !$FieldDelete) {
foreach ($FieldData['field_mapping_array'] as $FieldMappingData) {
$SQLString = 'SELECT ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_MAPPING . '.mapping_id ';
$SQLString .= 'FROM ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_MAPPING . ' ';
$SQLString .= 'WHERE ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_MAPPING . '.rs_mapping_id = \'' . $FieldMappingData['rs_mapping_id'] . '\' ';
$FieldMappingObject = mysql_fetch_object(errorlogged_mysql_query($SQLString));
if ($FieldMappingObject) {
if (!$FieldMappingData['deleted']) {
$SQLString = 'UPDATE ' . TABLE_DATAEXCHANGE_FIELDS_MAPPING . ' SET ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_MAPPING . '.value_csv = \'' . $FieldMappingData['value_csv'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_MAPPING . '.value_mapping = \'' . $FieldMappingData['value_mapping'] . '\' ';
$SQLString .= 'WHERE ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_MAPPING . '.rs_mapping_id = \'' . $FieldMappingData['rs_mapping_id'] . '\' ';
$MySQLQueryReference = errorlogged_mysql_query($SQLString);
if (mysql_affected_rows() > 0) {
if (!$FieldInsert && !$FieldUpdate) {
$FieldCounterUpdate++;
$FieldUpdate = true;
}
}
} else {
DeleteMapping($FieldMappingObject->mapping_id);
if (!$FieldInsert && !$FieldUpdate) {
$FieldCounterUpdate++;
$FieldUpdate = true;
}
}
} elseif (!$FieldMappingData['deleted']) {
$SQLString = 'INSERT INTO ' . TABLE_DATAEXCHANGE_FIELDS_MAPPING . ' SET ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_MAPPING . '.rs_mapping_id = \'' . $FieldMappingData['rs_mapping_id'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_MAPPING . '.field_id = \'' . $FieldID . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_MAPPING . '.value_csv = \'' . $FieldMappingData['value_csv'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_MAPPING . '.value_mapping = \'' . $FieldMappingData['value_mapping'] . '\' ';
$MySQLQueryReference = errorlogged_mysql_query($SQLString);
if (!$FieldInsert && !$FieldUpdate) {
$FieldCounterUpdate++;
$FieldUpdate = true;
}
}
}
}
}
// Filter
foreach ($UpdateDataexchangeDataArray['filter_array'] as $FilterData) {
$FilterUpdate = false;
$FilterInsert = false;
$FilterDelete = false;
$SQLString = 'SELECT ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.filter_id ';
$SQLString .= 'FROM ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ' ';
$SQLString .= 'WHERE ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.rs_filter_id = \'' . $FilterData['rs_filter_id'] . '\' ';
$FilterObject = mysql_fetch_object(errorlogged_mysql_query($SQLString));
if ($FilterObject) {
if (!$FilterData['deleted']) {
$SQLString = 'UPDATE ' . TABLE_DATAEXCHANGE_FILTERS. ' SET ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.filter_name = \'' . $FilterData['filter_name'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.filter_description = \'' . $FilterData['filter_description'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.dataexchange_type = \'' . $FilterData['dataexchange_type'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.delimiter_ascii = \'' . $FilterData['delimiter_ascii'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.enclosure_ascii = \'' . $FilterData['enclosure_ascii'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.start_row = \'' . $FilterData['start_row'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.row_length = \'' . $FilterData['row_length'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.csv_headline = \'' . $FilterData['csv_headline'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.option_insert = \'' . $FilterData['option_insert'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.option_multi = \'' . $FilterData['option_multi'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.option_onetime = \'' . $FilterData['option_onetime'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.option_csv_headline = \'' . $FilterData['option_csv_headline'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.option_history = \'' . $FilterData['option_history'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.filter_image = \'' . $FilterData['filter_image'] . '\' ';
$SQLString .= 'WHERE ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.rs_filter_id = \'' . $FilterData['rs_filter_id'] . '\' ';
$MySQLQueryReference = errorlogged_mysql_query($SQLString);
if (mysql_affected_rows() > 0) {
$FilterCounterUpdate++;
$FilterUpdate = true;
}
$FilterID = $FilterObject->filter_id;
} else {
DeleteFilter($FilterObject->filter_id);
$FilterCounterDelete++;
$FilterDelete = true;
$FilterID = $FilterObject->filter_id;
}
} elseif (!$FilterData['deleted']) {
$SQLString = 'INSERT INTO ' . TABLE_DATAEXCHANGE_FILTERS. ' SET ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.rs_filter_id = \'' . $FilterData['rs_filter_id'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.filter_name = \'' . $FilterData['filter_name'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.filter_description = \'' . $FilterData['filter_description'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.dataexchange_type = \'' . $FilterData['dataexchange_type'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.delimiter_ascii = \'' . $FilterData['delimiter_ascii'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.enclosure_ascii = \'' . $FilterData['enclosure_ascii'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.start_row = \'' . $FilterData['start_row'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.row_length = \'' . $FilterData['row_length'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.csv_headline = \'' . $FilterData['csv_headline'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.option_insert = \'' . $FilterData['option_insert'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.option_multi = \'' . $FilterData['option_multi'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.option_onetime = \'' . $FilterData['option_onetime'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.option_csv_headline = \'' . $FilterData['option_csv_headline'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.option_history = \'' . $FilterData['option_history'] . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . '.filter_image = \'' . $FilterData['filter_image'] . '\' ';
$MySQLQueryReference = errorlogged_mysql_query($SQLString);
$FilterCounterInsert++;
$FilterInsert = true;
$FilterID = mysql_insert_id();
}
// Feldzuordnung
$SQLString = 'DELETE FROM ' . TABLE_DATAEXCHANGE_FILTERS_FIELDS . ' WHERE ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_FIELDS . '.filter_id = \'' . $FilterID . '\' ';
$MySQLQueryReference = errorlogged_mysql_query($SQLString);
if (is_array($FilterData['filter_field_array']) && !$FilterDelete) {
foreach ($FilterData['filter_field_array'] as $FieldFilterData) {
$SQLString = 'SELECT ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.field_id ';
$SQLString .= 'FROM ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ' ';
$SQLString .= 'WHERE ';
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . '.rs_field_id = \'' . $FieldFilterData['field_id'] . '\' ';
$FieldObject = mysql_fetch_object(errorlogged_mysql_query($SQLString));
$SQLString = 'INSERT INTO ' . TABLE_DATAEXCHANGE_FILTERS_FIELDS . ' SET ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_FIELDS . '.filter_id = \'' . $FilterID . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_FIELDS . '.field_id = \'' . $FieldObject->field_id . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_FIELDS . '.sort = \'' . $FieldFilterData['sort'] . '\' ';
$MySQLQueryReference = errorlogged_mysql_query($SQLString);
}
}
// Sonderfunktionen
$SQLString = 'DELETE FROM ' . TABLE_DATAEXCHANGE_FILTERS_SPECIALFUNCTIONS . ' WHERE ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_SPECIALFUNCTIONS . '.filter_id = \'' . $FilterID . '\' ';
$MySQLQueryReference = errorlogged_mysql_query($SQLString);
if (is_array($FilterData['filter_specialfunction_array']) && !$FilterDelete) {
foreach ($FilterData['filter_specialfunction_array'] as $FieldSpecialfunctionData) {
$SQLString = 'SELECT ';
$SQLString .= TABLE_DATAEXCHANGE_SPECIALFUNCTIONS . '.specialfunction_id ';
$SQLString .= 'FROM ';
$SQLString .= TABLE_DATAEXCHANGE_SPECIALFUNCTIONS . ' ';
$SQLString .= 'WHERE ';
$SQLString .= TABLE_DATAEXCHANGE_SPECIALFUNCTIONS . '.rs_specialfunction_id = \'' . $FieldSpecialfunctionData['specialfunction_id'] . '\' ';
$FieldObject = mysql_fetch_object(errorlogged_mysql_query($SQLString));
$SQLString = 'INSERT INTO ' . TABLE_DATAEXCHANGE_FILTERS_SPECIALFUNCTIONS . ' SET ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_SPECIALFUNCTIONS . '.filter_id = \'' . $FilterID . '\', ';
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_SPECIALFUNCTIONS . '.specialfunction_id = \'' . $FieldObject->specialfunction_id . '\' ';
$MySQLQueryReference = errorlogged_mysql_query($SQLString);
}
}
}
$UpdateDataexchangeResult['message'] = $a_de_settings_update_message;
$UpdateDataexchangeResult['message'] .= $a_de_settings_update_message_table_field;
if ($FieldCounterUpdate > 0) {
$UpdateDataexchangeResult['message'] .= sprintf($a_de_settings_update_message_field_update, $FieldCounterUpdate);
}
if ($FieldCounterInsert > 0) {
$UpdateDataexchangeResult['message'] .= sprintf($a_de_settings_update_message_field_insert, $FieldCounterInsert);
}
if ($FieldCounterDelete > 0) {
$UpdateDataexchangeResult['message'] .= sprintf($a_de_settings_update_message_field_delete, $FieldCounterDelete);
}
if ($FilterCounterUpdate > 0) {
$UpdateDataexchangeResult['message'] .= sprintf($a_de_settings_update_message_filter_update, $FilterCounterUpdate);
}
if ($FilterCounterInsert > 0) {
$UpdateDataexchangeResult['message'] .= sprintf($a_de_settings_update_message_filter_insert, $FilterCounterInsert);
}
if ($FilterCounterDelete > 0) {
$UpdateDataexchangeResult['message'] .= sprintf($a_de_settings_update_message_filter_delete, $FilterCounterDelete);
}
$UpdateDataexchangeResult['message'] = substr($UpdateDataexchangeResult['message'], 0, strlen($UpdateDataexchangeResult['message']) - 2);
// LastUpdate Datum setzen
// SaveKeyValueEinstellungen('dataexchange', 'last_update', date('Y.m.d H:i.s'));
} catch (Exception $e) {
$UpdateDataexchangeResult['message'] = $a_de_settings_update_message_error;
}
return $UpdateDataexchangeResult;
}
function GetFieldDefaultDataArray($FilterID, $DefaultValueArray = false) {
$SQLString = "SELECT ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_FIELDS . ".field_id, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".field_name, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".option_mandatory, ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_FIELDS_DEFAULT . ".option_default_field_value ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_FIELDS . " ";
$SQLString .= "LEFT JOIN " . TABLE_DATAEXCHANGE_FIELDS . " ON " . TABLE_DATAEXCHANGE_FILTERS_FIELDS . ".field_id = " .TABLE_DATAEXCHANGE_FIELDS . ".field_id ";
$SQLString .= "LEFT JOIN " . TABLE_DATAEXCHANGE_FILTERS_FIELDS_DEFAULT . " ON ((" . TABLE_DATAEXCHANGE_FILTERS_FIELDS_DEFAULT . ".filter_id = " . $FilterID . ") AND (" . TABLE_DATAEXCHANGE_FIELDS . ".field_id = " .TABLE_DATAEXCHANGE_FILTERS_FIELDS_DEFAULT . ".field_id)) ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_FIELDS . ".filter_id = '" . $FilterID . "' AND ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".field_type = '" . FIELDTYPE_DEFAULT . "' ";
$SQLString .= "ORDER BY " . TABLE_DATAEXCHANGE_FIELDS . ".field_name ";
// echo '$SQLString: ' . $SQLString . '
';
$MySQLQueryReference = mysql_query($SQLString);
while ($FieldDefaultRow = mysql_fetch_array($MySQLQueryReference, MYSQL_ASSOC)) {
if (isset($DefaultValueArray[$FieldDefaultRow["field_id"]])) {
$FieldDefaultRow["option_default_field_value"] = $DefaultValueArray[$FieldDefaultRow["field_id"]];
}
$FieldDefaultDataArray[$FieldDefaultRow["field_id"]] = $FieldDefaultRow;
}
return $FieldDefaultDataArray;
}
function DeleteIntervalHistory() {
$DataexchangeSettingsObject = GetDataexchangeSettings();
$SQLString = "SELECT ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . ".history_id ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . " ";
$SQLString .= "WHERE ";
$SQLString .= "DATE_ADD(" . TABLE_DATAEXCHANGE_HISTORY . ".date, INTERVAL " . $DataexchangeSettingsObject->history_delete_interval . " MONTH) < NOW() ";
$MySQLQueryReference = mysql_query($SQLString);
while ($HistoryRow = mysql_fetch_array($MySQLQueryReference)) {
DeleteHistory($HistoryRow["history_id"]);
}
}
function DeleteAllHistory() {
$SQLString = "TRUNCATE " . TABLE_DATAEXCHANGE_HISTORY . " ";
$MySQLQuery = mysql_query($SQLString);
$SQLString = "TRUNCATE " . TABLE_DATAEXCHANGE_HISTORY_STATUS . " ";
$MySQLQuery = mysql_query($SQLString);
$SQLString = "TRUNCATE " . TABLE_DATAEXCHANGE_HISTORY_ROWS . " ";
$MySQLQuery = mysql_query($SQLString);
$SQLString = "TRUNCATE " . TABLE_DATAEXCHANGE_HISTORY_TABLES . " ";
$MySQLQuery = mysql_query($SQLString);
$SQLString = "TRUNCATE " . TABLE_DATAEXCHANGE_HISTORY_FIELDS . " ";
$MySQLQuery = mysql_query($SQLString);
unlink_wc(IMPORTDATAPFAD, "*.csv");
unlink_wc(EXPORTDATAPFAD, "*.csv");
}
function GetFieldDataArray($SearchField = '', $SearchString = '', $SortField = '', $SortOrder = '', $DataOffset = '', $DataCount = '', $FilterDataExchangeType = '', $FilterID = '', $SearchTableNameArray = NULL, $FilterFieldTypeArray = NULL, $FilterWithoutSelectedFields = NULL) {
global $a_de_field_database, $a_de_field_default, $a_de_field_selection, $a_de_field_database_default;
DeleteMappingTable(0);
$SQLString = "SELECT ";
if ($FilterID) {
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ".filter_id, ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ".filter_name, ";
}
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".field_id, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".rs_field_id, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".field_name, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".field_type, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".relation_type, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".option_language, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".main_table, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".main_field, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".main_idfield, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".relation_table, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".relation_main_idfield, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".relation_target_idfield, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".target_table, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".target_field, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".target_idfield, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".language_table, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".language_field, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".language_idfield, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".language_languagefield, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".tabledefault_value ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . " ";
// Filter
if ($FilterID || $FilterWithoutSelectedFields) {
$SQLString .= "LEFT JOIN " . TABLE_DATAEXCHANGE_FILTERS_FIELDS . " ON ((" . TABLE_DATAEXCHANGE_FIELDS . ".field_id = " . TABLE_DATAEXCHANGE_FILTERS_FIELDS . ".field_id) AND (" . TABLE_DATAEXCHANGE_FILTERS_FIELDS . ".filter_id = '" . $FilterID . "')) ";
$SQLString .= "LEFT JOIN " . TABLE_DATAEXCHANGE_FILTERS . " ON " . TABLE_DATAEXCHANGE_FILTERS_FIELDS . ".filter_id = " . TABLE_DATAEXCHANGE_FILTERS . ".filter_id ";
}
$SQLString .= "WHERE (";
// Suche
if ($SearchField && $SearchString) {
$SQLString .= "(" . $SearchField . " LIKE '%" . $SearchString . "%') AND ";
}
// Filter
if ($FilterWithoutSelectedFields && $FilterID) {
$SQLString .= "(ISNULL(" . TABLE_DATAEXCHANGE_FILTERS_FIELDS . ".filter_id)) AND ";
} elseif ($FilterID) {
$SQLString .= "(" . TABLE_DATAEXCHANGE_FILTERS_FIELDS . ".filter_id = '" . $FilterID . "') AND ";
}
// Filtertype
if (is_array($FilterFieldTypeArray)) {
if (count($FilterFieldTypeArray) == 0) {
$SQLString .= "(" . TABLE_DATAEXCHANGE_FIELDS . ".field_type IN (0)) AND ";
} else {
$SQLString .= "(" . TABLE_DATAEXCHANGE_FIELDS . ".field_type IN (" . implode(",", $FilterFieldTypeArray) . ")) AND ";
}
}
// Import/Export
if ($FilterDataExchangeType) {
$SQLString .= "(" . TABLE_DATAEXCHANGE_FIELDS . ".dataexchange_type = '" . $FilterDataExchangeType . "') AND ";
}
//
if (is_array($SearchTableNameArray)) {
$SQLString .= "(";
foreach ($SearchTableNameArray as $SearchTableName) {
$SQLString .= "(IF((" . TABLE_DATAEXCHANGE_FIELDS . ".relation_type = " . RELATIONTYPE_11 . " AND " . TABLE_DATAEXCHANGE_FIELDS . ".option_language = 1), " . TABLE_DATAEXCHANGE_FIELDS . ".language_table, " . TABLE_DATAEXCHANGE_FIELDS . ".main_table) = '" . $SearchTableName . "') OR ";
}
$SQLString = substr($SQLString, 0, strlen($SQLString) - 4);
$SQLString .= ") AND ";
}
$SQLString .= " 1) ";
// Sortierung
if ($SortField && $SortOrder) {
$SQLString .= "ORDER BY " . $SortField . " " . $SortOrder . " ";
}
// Limitierung
if (($DataOffset !== '') && ($DataCount !== '')) {
$SQLString .= "LIMIT " . $DataOffset . ", " . $DataCount . " ";
}
// echo '$SQLString: ' . $SQLString . '
';
$MySQLQueryReference = mysql_query($SQLString);
$FieldDataArray = array();
while ($FieldRow = mysql_fetch_array($MySQLQueryReference, MYSQL_ASSOC)) {
unset($FieldTablePath);
// Feldtypen
switch ($FieldRow["field_type"]) {
case FIELDTYPE_DATABASE:
$FieldRow["field_type_name"] = $a_de_field_database;
break;
case FIELDTYPE_DEFAULT:
$FieldRow["field_type_name"] = $a_de_field_default;
break;
case FIELDTYPE_SELECTION:
$FieldRow["field_type_name"] = $a_de_field_selection;
break;
case FIELDTYPE_DATABASE_DEFAULT:
$FieldRow["field_type_name"] = $a_de_field_database_default;
break;
}
// Tabellenangaben fuer Uebersichten
if ($FieldRow["relation_type"] == RELATIONTYPE_11) {
if (!$FieldRow["option_language"]) {
$FieldTablePath = GetFieldName($FieldRow["main_table"], $FieldRow["main_field"]);
} else {
$FieldTablePath = GetFieldName($FieldRow["language_table"], $FieldRow["language_field"]);
}
} elseif ($FieldRow["relation_type"] == RELATIONTYPE_1N) {
$FieldTablePath = GetFieldName($FieldRow["main_table"], $FieldRow["main_field"]);
// if (!$FieldRow["option_language"]) {
// $FieldTablePath = GetFieldName($FieldRow["target_table"], $FieldRow["target_field"]);
// } else {
// $FieldTablePath = GetFieldName($FieldRow["language_table"], $FieldRow["language_field"]);
// }
} elseif ($FieldRow["relation_type"] == RELATIONTYPE_NM) {
if (!$FieldRow["option_language"]) {
$FieldTablePath = GetFieldName($FieldRow["target_table"], $FieldRow["target_field"]);
} else {
$FieldTablePath = GetFieldName($FieldRow["language_table"], $FieldRow["language_field"]);
}
} elseif ($FieldRow["field_type"] == FIELDTYPE_DEFAULT) {
$FieldTablePath = "" . $a_de_field_default . "";
}
$FieldRow["field_table_path"] = $FieldTablePath;
$FieldDataArray[] = $FieldRow;
}
return $FieldDataArray;
}
function GetFieldAnzahl($SearchField = '', $SearchString = '', $FilterDataExchangeType = '') {
$SQLString = "SELECT ";
$SQLString .= "COUNT(" . TABLE_DATAEXCHANGE_FIELDS . ".field_id) AS field_anzahl ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . " ";
$SQLString .= "WHERE (";
// Suche
if ($SearchField && $SearchString) {
$SQLString .= "(" . $SearchField . " LIKE '%" . $SearchString . "%') AND ";
}
// Import/Export
if ($FilterDataExchangeType) {
$SQLString .= "(" . TABLE_DATAEXCHANGE_FIELDS . ".dataexchange_type = '" . $FilterDataExchangeType . "') AND ";
}
$SQLString .= " 1)";
// echo '$SQLString: ' . $SQLString . '
';
$FieldObject = mysql_fetch_object(mysql_query($SQLString));
return $FieldObject->field_anzahl;
}
function GetFieldDetail($FieldID, $TablePrefix = false) {
$SQLString = "SELECT ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".field_id, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".field_name, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".field_csv_caption, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".field_alias, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".field_type, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".relation_type, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".main_table, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".main_field, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".main_idfield, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".target_table, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".target_field, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".target_idfield, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".relation_table, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".relation_main_idfield, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".relation_target_idfield, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".language_table, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".language_field, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".language_idfield, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".language_languagefield, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".option_mandatory, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".option_identifier, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".option_insert, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".option_insert_default_value, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".option_language, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".option_striplinebreak, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".option_striptags, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".option_trim, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".regexp_search, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".regexp_insert, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".mapping_default_value, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".regexp_search, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".regexp_insert, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".tabledefault_value ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".field_id = '" . $FieldID . "' ";
// echo '$SQLString: ' . $SQLString . '
';
$FieldObject = mysql_fetch_object(mysql_query($SQLString));
if ($TablePrefix == true) {
if ($FieldObject->main_table) {
$FieldObject->main_table = TABLE_PREFIX . $FieldObject->main_table;
}
if ($FieldObject->target_table) {
$FieldObject->target_table = TABLE_PREFIX . $FieldObject->target_table;
}
if ($FieldObject->relation_table) {
$FieldObject->relation_table = TABLE_PREFIX . $FieldObject->relation_table;
}
if ($FieldObject->language_table) {
$FieldObject->language_table = TABLE_PREFIX . $FieldObject->language_table;
}
}
$FieldObject->regexp_search = stripslashes($FieldObject->regexp_search);
$FieldObject->regexp_replace = stripslashes($FieldObject->regexp_replace);
return $FieldObject;
}
function CheckField($FieldID) {
$SQLString = "SELECT ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_FIELDS . ".filter_id, ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ".filter_name ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_FIELDS . " ";
$SQLString .= "LEFT JOIN " . TABLE_DATAEXCHANGE_FILTERS . " ON " . TABLE_DATAEXCHANGE_FILTERS_FIELDS . ".filter_id = " . TABLE_DATAEXCHANGE_FILTERS . ".filter_id ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_FIELDS . ".field_id = " . $FieldID . " ";
$MySQLQueryReference = mysql_query($SQLString);
if (!mysql_num_rows($MySQLQueryReference)) {
return true;
} else {
while ($CheckFieldRow = mysql_fetch_array($MySQLQueryReference)) {
$ReturnString .= "\"" . $CheckFieldRow["filter_name"] . "\", ";
}
return substr($ReturnString, 0, strlen($ReturnString) - 2);
}
}
function SaveTabledefaultValue($FieldID, $TabledefaultValue = '') {
// echo '$FieldID: ' . $FieldID . '
';
// echo '$TabledefaultValue: ' . $TabledefaultValue . '
';
if ($FieldID) {
$SQLString = "UPDATE " . TABLE_DATAEXCHANGE_FIELDS . " SET ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".tabledefault_value = '" . $TabledefaultValue . "' ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".field_id = '" . $FieldID . "' ";
$MySQLQueryReference = mysql_query($SQLString);
}
return $FieldID;
}
function DeleteField($FieldID) {
$SQLString = "DELETE FROM " . TABLE_DATAEXCHANGE_FIELDS . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".field_id = '" . $FieldID . "' ";
// echo '$SQLString: ' . $SQLString . '
';
$MySQLQuery = mysql_query($SQLString);
$SQLString = "DELETE FROM " . TABLE_DATAEXCHANGE_FILTERS_FIELDS . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_FIELDS . ".field_id = '" . $FieldID . "' ";
// echo '$SQLString: ' . $SQLString . '
';
$MySQLQuery = mysql_query($SQLString);
$SQLString = "DELETE FROM " . TABLE_DATAEXCHANGE_FIELDS_SELECTION . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_SELECTION . ".field_id = '" . $FieldID . "' ";
// echo '$SQLString: ' . $SQLString . '
';
$MySQLQuery = mysql_query($SQLString);
$SQLString = "DELETE FROM " . TABLE_DATAEXCHANGE_FIELDS_MAPPING . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_MAPPING . ".field_id = '" . $FieldID . "' ";
// echo '$SQLString: ' . $SQLString . '
';
$MySQLQuery = mysql_query($SQLString);
}
function GetSelectionDataArray($FieldID) {
global $a_de_field_selection_operation_comparison_equal_value, $a_de_field_selection_operation_comparison_equal_caption;
global $a_de_field_selection_operation_comparison_unequal_value, $a_de_field_selection_operation_comparison_unequal_caption;
global $a_de_field_selection_operation_comparison_less_value, $a_de_field_selection_operation_comparison_less_caption;
global $a_de_field_selection_operation_comparison_greater_value, $a_de_field_selection_operation_comparison_greater_caption;
global $a_de_field_selection_operation_logical_and_value, $a_de_field_selection_operation_logical_and_caption;
global $a_de_field_selection_operation_logical_or_value, $a_de_field_selection_operation_logical_or_caption;
$SelectionDataArray = array();
$SQLString = "SELECT ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_SELECTION . ".selection_id, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_SELECTION . ".operation_comparison, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_SELECTION . ".operation_logical, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_SELECTION . ".operation_value ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_SELECTION . " ";
$SQLString .= "WHERE ";
$SQLString .= "(";
$SQLString .= "(" . TABLE_DATAEXCHANGE_FIELDS_SELECTION . ".field_id = '" . $FieldID . "') AND ";
$SQLString .= " 1)";
$SQLString .= "ORDER BY " . TABLE_DATAEXCHANGE_FIELDS_SELECTION . ".sort ";
// echo '$SQLString: ' . $SQLString . '
';
$MySQLQueryReference = mysql_query($SQLString);
while ($SelectionRow = mysql_fetch_array($MySQLQueryReference, MYSQL_ASSOC)) {
if ($SelectionRow["operation_comparison"] == $a_de_field_selection_operation_comparison_equal_value) {
$SelectionRow["operation_comparison_caption"] = $a_de_field_selection_operation_comparison_equal_caption;
} elseif ($SelectionRow["operation_comparison"] == $a_de_field_selection_operation_comparison_unequal_value) {
$SelectionRow["operation_comparison_caption"] = $a_de_field_selection_operation_comparison_unequal_caption;
} elseif ($SelectionRow["operation_comparison"] == $a_de_field_selection_operation_comparison_less_value) {
$SelectionRow["operation_comparison_caption"] = $a_de_field_selection_operation_comparison_less_caption;
} elseif ($SelectionRow["operation_comparison"] == $a_de_field_selection_operation_comparison_greater_value) {
$SelectionRow["operation_comparison_caption"] = $a_de_field_selection_operation_comparison_greater_caption;
} else {
$SelectionRow["operation_comparison_caption"] = $SelectionRow["operation_comparison"];
}
if ($SelectionRow["operation_logical"] == $a_de_field_selection_operation_logical_and_value) {
$SelectionRow["operation_logical_caption"] = $a_de_field_selection_operation_logical_and_caption;
} elseif ($SelectionRow["operation_logical"] == $a_de_field_selection_operation_logical_or_value) {
$SelectionRow["operation_logical_caption"] = $a_de_field_selection_operation_logical_or_caption;
} else {
$SelectionRow["operation_logical_caption"] = $SelectionRow["operation_logical"];
}
$SelectionDataArray[] = $SelectionRow;
}
return $SelectionDataArray;
}
function GetMappingDataArray($FieldID) {
$MappingDataArray = array();
$SQLString = "SELECT ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_MAPPING . ".mapping_id, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_MAPPING . ".value_csv, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_MAPPING . ".value_mapping ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS_MAPPING . " ";
$SQLString .= "WHERE ";
$SQLString .= "(";
$SQLString .= "(" . TABLE_DATAEXCHANGE_FIELDS_MAPPING . ".field_id = '" . $FieldID . "') AND ";
$SQLString .= " 1)";
// echo '$SQLString: ' . $SQLString . '
';
$MySQLQueryReference = mysql_query($SQLString);
while ($MappingRow = mysql_fetch_array($MySQLQueryReference, MYSQL_ASSOC)) {
$MappingDataArray[] = $MappingRow;
}
return $MappingDataArray;
}
function GetFieldTableDataArray($TableName = '') {
$FieldTableDataArray = array();
$Einstellungen = GetEinstellungen('', 'dataexchange');
// vordefinierte Tabellen einlesen
$SQLString = "SELECT ";
$SQLString .= TABLE_DATAEXCHANGE_TABLES . ".table_name, ";
$SQLString .= TABLE_DATAEXCHANGE_TABLES . ".table_caption ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_TABLES . " ";
$SQLString .= "WHERE (";
if ($TableName) {
// $SQLString .= "(CONCAT('" . TABLE_PREFIX . "', " . TABLE_DATAEXCHANGE_TABLES . ".table_name) = '" . $TableName . "') AND ";
$SQLString .= "(" . TABLE_DATAEXCHANGE_TABLES . ".table_name = '" . $TableName . "') AND ";
}
$SQLString .= " 1)";
$SQLString .= "ORDER BY " . TABLE_DATAEXCHANGE_TABLES . ".table_caption ASC ";
// echo '$SQLString: ' . $SQLString . '
';
$MySQLQueryReference = mysql_query($SQLString);
$TableNameArray = array();
while ($DefinedTableRow = mysql_fetch_array($MySQLQueryReference, MYSQL_ASSOC)) {
$FieldTableDataArray[] = $DefinedTableRow;
$TableNameArray[] = $DefinedTableRow["table_name"];
}
$FieldTableCounter = count($FieldTableDataArray);
if ($Einstellungen->dataexchange->expert_mode) {
// alle Tabellen aus der Datenbank einlesen
$SQLString = "SHOW TABLES";
$MySQLQueryReference = mysql_query($SQLString);
$FieldTableCounter = count($FieldTableDataArray);
while ($TableRow = mysql_fetch_array($MySQLQueryReference, MYSQL_NUM)) {
if (substr($TableRow[0], 0, strlen(TABLE_PREFIX)) != TABLE_PREFIX) {
continue;
} else {
$TableRow[0] = substr($TableRow[0], strlen(TABLE_PREFIX));
}
if ($TableName) {
if (!in_array($TableRow[0], $TableNameArray) && ($TableRow[0] == $TableName)) {
$FieldTableDataArray[$FieldTableCounter]["table_name"] = $TableRow[0];
$FieldTableDataArray[$FieldTableCounter]["table_caption"] = $TableRow[0];
$FieldTableCounter++;
}
} else {
if (!in_array($TableRow[0], $TableNameArray)) {
$FieldTableDataArray[$FieldTableCounter]["table_name"] = $TableRow[0];
$FieldTableDataArray[$FieldTableCounter]["table_caption"] = $TableRow[0];
$FieldTableCounter++;
}
}
}
}
// echo '';
// var_dump($FieldTableDataArray);
// echo '
';
return $FieldTableDataArray;
}
function GetFieldTableFieldDataArray($TableName = '') {
$FieldTableFieldDataArray = array();
$Einstellungen = GetEinstellungen('', 'dataexchange');
$TableDataArray = GetFieldTableDataArray($TableName);
foreach ($TableDataArray as $TableData) {
if ($TableData["table_name"] == "0") {
continue;
}
// vordefinierte Felder einlesen
$SQLString = "SELECT ";
$SQLString .= TABLE_DATAEXCHANGE_TABLES_FIELDS . ".field_name, ";
$SQLString .= TABLE_DATAEXCHANGE_TABLES_FIELDS . ".field_caption ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_TABLES_FIELDS . " ";
$SQLString .= "WHERE (";
$SQLString .= TABLE_DATAEXCHANGE_TABLES_FIELDS . ".table_name = '" . $TableData["table_name"] . "' AND ";
$SQLString .= " 1)";
$SQLString .= "ORDER BY " . TABLE_DATAEXCHANGE_TABLES_FIELDS . ".field_caption ASC ";
// echo '$SQLString: ' . $SQLString . '
';
$MySQLQueryReference = mysql_query($SQLString);
$TableFieldNameArray = array();
while ($DefinedTableFieldRow = mysql_fetch_array($MySQLQueryReference, MYSQL_ASSOC)) {
$FieldTableFieldDataArray[$TableData["table_name"]][] = $DefinedTableFieldRow;
$TableFieldNameArray[] = $DefinedTableFieldRow["field_name"];
}
// echo '';
// var_dump($FieldTableFieldDataArray);
// echo '
';
$FieldTableFieldCounter = count($FieldTableFieldDataArray[$TableData["table_name"]]);
if ($Einstellungen->dataexchange->expert_mode) {
// alle Felder aus der Tabelle einlesen
if ($TableData["table_name"]) {
$SQLString = "SELECT ";
$SQLString .= TABLE_PREFIX . $TableData["table_name"] . ".* ";
$SQLString .= "FROM ";
$SQLString .= TABLE_PREFIX . $TableData["table_name"] . " ";
$MySQLQueryReference = mysql_query($SQLString);
$FieldCounter = 0;
$FieldTableFieldCounter = count($FieldTableFieldDataArray[$TableData["table_name"]]);
while ($FieldCounter < mysql_num_fields($MySQLQueryReference)) {
$FieldInformation = mysql_fetch_field($MySQLQueryReference, $FieldCounter);
if (!in_array($FieldInformation->name, $TableFieldNameArray)) {
$FieldTableFieldDataArray[$TableData["table_name"]][$FieldTableFieldCounter]["field_name"] = $FieldInformation->name;
$FieldTableFieldDataArray[$TableData["table_name"]][$FieldTableFieldCounter]["field_caption"] = $FieldInformation->name;
$FieldTableFieldCounter++;
}
$FieldCounter++;
}
}
}
}
return $FieldTableFieldDataArray;
}
function GetTableDetail($TableName) {
$SQLString = "SELECT ";
$SQLString .= TABLE_DATAEXCHANGE_TABLES . ".table_name, ";
$SQLString .= TABLE_DATAEXCHANGE_TABLES . ".table_caption, ";
$SQLString .= TABLE_DATAEXCHANGE_TABLES . ".default_idfield, ";
$SQLString .= TABLE_DATAEXCHANGE_TABLES . ".default_tablelangu, ";
$SQLString .= TABLE_DATAEXCHANGE_TABLES . ".default_languageidfield, ";
$SQLString .= TABLE_DATAEXCHANGE_TABLES . ".default_tablerelation, ";
$SQLString .= TABLE_DATAEXCHANGE_TABLES . ".default_relationidfield ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_TABLES . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_TABLES . ".table_name = '" . $TableName . "' ";
$TableObject = mysql_fetch_object(mysql_query($SQLString));
if (!$TableObject) {
$SQLString = "SELECT ";
$SQLString .= TABLE_PREFIX . $TableName . ".* ";
$SQLString .= "FROM ";
$SQLString .= TABLE_PREFIX . $TableName . " ";
$MySQLQueryReference = mysql_query($SQLString);
$PrimaryField = '';
$LanguageField = '';
$FieldCounter = 0;
$FieldTableFieldCounter = count($FieldTableFieldDataArray);
while ($FieldCounter < mysql_num_fields($MySQLQueryReference)) {
$FieldInformation = mysql_fetch_field($MySQLQueryReference, $FieldCounter);
if ($FieldInformation->primary_key && !$PrimaryField) {
$PrimaryField = $FieldInformation->name;
} elseif ($FieldInformation->primary_key && $PrimaryField) {
$PrimaryField = '';
}
if ($FieldInformation->name == 'language_id') {
$LanguageField = $FieldInformation->name;
}
$FieldCounter++;
}
$TableObject->table_name = $TableName;
$TableObject->table_caption = $TableName;
$TableObject->primary_field = $PrimaryField;
$TableObject->language_field = $LanguageField;
}
return $TableObject;
}
function GetFilterDetail($FilterID, $FilterDetail = false, $TablePrefix = false) {
$SQLString = "SELECT ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ".filter_id, ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ".filter_name, ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ".filter_description, ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ".dataexchange_type, ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ".delimiter_ascii, ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ".enclosure_ascii, ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ".start_row, ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ".row_length, ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ".option_csv_headline, ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ".option_insert, ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ".option_onetime, ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ".option_csv_headline, ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ".option_history ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ".filter_id = '" . $FilterID . "' ";
// echo '$SQLString: ' . $SQLString . '
';
$FilterObject = mysql_fetch_object(mysql_query($SQLString));
if ($FilterDetail) {
$FilterFieldDataArray = GetFilterFieldDataArray($FilterID);
foreach ($FilterFieldDataArray as $FilterFieldData) {
if ($FilterFieldData["field_id"]) {
$FieldObject = GetFieldDetail($FilterFieldData["field_id"], $TablePrefix);
if ($FieldObject->option_insert == OPTION_INSERT_MAPPING) {
$FieldObject->mapping_array = GetMappingDataArray($FilterFieldData["field_id"]);
}
if ($FieldObject->field_type == FIELDTYPE_SELECTION) {
$FieldObject->selection_array = GetSelectionDataArray($FilterFieldData["field_id"]);
}
$FilterObject->field_array[] = $FieldObject;
} else {
$FilterObject->field_array[]["field_name"] = "Leerfeld";
}
}
}
return $FilterObject;
}
function GetFilterFieldDataArray($FilterID, $FilterFieldTypeArray = NULL) {
$FieldDataArray = array();
$SQLString = "SELECT ";
$SQLString .= "IFNULL(" . TABLE_DATAEXCHANGE_FIELDS . ".field_id, '0') AS field_id, ";
$SQLString .= "IFNULL(" . TABLE_DATAEXCHANGE_FIELDS . ".field_name, 'Leerfeld') AS field_name, ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".option_identifier ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_FIELDS . " ";
$SQLString .= "LEFT JOIN " . TABLE_DATAEXCHANGE_FIELDS . " ON " . TABLE_DATAEXCHANGE_FILTERS_FIELDS . ".field_id = " . TABLE_DATAEXCHANGE_FIELDS . ".field_id ";
$SQLString .= "WHERE (";
$SQLString .= "(" . TABLE_DATAEXCHANGE_FILTERS_FIELDS . ".filter_id = '" . $FilterID . "') AND ";
// Filtertype
if (is_array($FilterFieldTypeArray)) {
if (count($FilterFieldTypeArray) == 0) {
$SQLString .= "(" . TABLE_DATAEXCHANGE_FIELDS . ".field_type IN (0)) AND ";
} else {
if (in_array(0, $FilterFieldTypeArray)) {
$SQLString .= "(" . TABLE_DATAEXCHANGE_FIELDS . ".field_type IN (" . implode(",", $FilterFieldTypeArray) . ") OR ";
$SQLString .= "(ISNULL(" . TABLE_DATAEXCHANGE_FIELDS . ".field_type))) AND ";
} else {
$SQLString .= "(" . TABLE_DATAEXCHANGE_FIELDS . ".field_type IN (" . implode(",", $FilterFieldTypeArray) . ")) AND ";
}
}
}
$SQLString .= " 1) ";
$SQLString .= "ORDER BY " . TABLE_DATAEXCHANGE_FILTERS_FIELDS . ".sort ";
$MySQLQueryReference = mysql_query($SQLString);
// echo '$SQLString: ' . $SQLString . '
';
$FieldCounter = 0;
while ($FieldRow = mysql_fetch_array($MySQLQueryReference, MYSQL_ASSOC)) {
$FieldDataArray[$FieldCounter] = $FieldRow;
if ($WithFieldTables) {
$FieldDataArray[$FieldCounter]["table_array"] = GetFieldTableArray($FieldRow["field_id"]);
}
$FieldCounter++;
}
return $FieldDataArray;
}
function DeleteFilter($FilterID) {
$SQLString = "DELETE FROM " . TABLE_DATAEXCHANGE_FILTERS . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ".filter_id = '" . $FilterID . "' ";
$MySQLQuery = mysql_query($SQLString);
$SQLString = "DELETE FROM " . TABLE_DATAEXCHANGE_FILTERS_FIELDS . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_FIELDS . ".filter_id = '" . $FilterID . "' ";
$MySQLQuery = mysql_query($SQLString);
$SQLString = "DELETE FROM " . TABLE_DATAEXCHANGE_FILTERS_SPECIALFUNCTIONS . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_SPECIALFUNCTIONS . ".filter_id = '" . $FilterID . "' ";
$MySQLQuery = mysql_query($SQLString);
$SQLString = "DELETE FROM " . TABLE_DATAEXCHANGE_FILTERS_FIELDS_DEFAULT . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_FIELDS_DEFAULT . ".filter_id = '" . $FilterID . "' ";
$MySQLQuery = mysql_query($SQLString);
}
function GetFilterDataArray($SearchField = '', $SearchString = '', $SortField = '', $SortOrder = '', $DataOffset = '', $DataCount = '', $FilterDataExchangeType = '', $SearchFilterArray = NULL) {
$SQLString = "SELECT ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ".filter_id, ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ".rs_filter_id, ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ".filter_name, ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ".filter_image, ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . ".dataexchange_type ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . " ";
$SQLString .= "WHERE (";
// Suche
if ($SearchField && $SearchString) {
$SQLString .= "(" . $SearchField . " LIKE '%" . $SearchString . "%') AND ";
}
// Import/Export
if ($FilterDataExchangeType) {
$SQLString .= "(" . TABLE_DATAEXCHANGE_FILTERS . ".dataexchange_type = '" . $FilterDataExchangeType . "') AND ";
}
// Suchfilter
if (is_array($SearchFilterArray)) {
$SQLString .= "(" . TABLE_DATAEXCHANGE_FILTERS . ".filter_id IN (" . implode(",", $SearchFilterArray) . ")) AND ";
}
$SQLString .= " 1)";
// Sortierung
if ($SortField && $SortOrder) {
$SQLString .= "ORDER BY " . $SortField . " " . $SortOrder . " ";
}
// Limitierung
if ((isset($DataOffset) && $DataOffset !== '') && (isset($DataCount) && $DataCount !== '')) {
$SQLString .= "LIMIT " . $DataOffset . ", " . $DataCount . " ";
}
// echo '$SQLString: ' . $SQLString . '
';
$MySQLQueryReference = mysql_query($SQLString);
$FilterDataArray = array();
while ($FilterRow = mysql_fetch_array($MySQLQueryReference, MYSQL_ASSOC)) {
if ($FilterRow['filter_image']) {
$FilterRow['filter_image_imagestring'] = "![](\"")
";
}
$FilterDataArray[] = $FilterRow;
}
return $FilterDataArray;
}
function GetFilterAnzahl($SearchField = '', $SearchString = '', $FilterDataExchangeType = '') {
$SQLString = "SELECT ";
$SQLString .= "COUNT(" . TABLE_DATAEXCHANGE_FILTERS . ".filter_id) AS filter_anzahl ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS . " ";
$SQLString .= "WHERE (";
// Suche
if ($SearchField && $SearchString) {
$SQLString .= "(" . $SearchField . " LIKE '%" . $SearchString . "%') AND ";
}
// Import/Export
if ($FilterDataExchangeType) {
$SQLString .= "(" . TABLE_DATAEXCHANGE_FILTERS . ".dataexchange_type = '" . $FilterDataExchangeType . "') AND ";
}
$SQLString .= " 1)";
// echo '$SQLString: ' . $SQLString . '
';
$FilterObject = mysql_fetch_object(mysql_query($SQLString));
return $FilterObject->filter_anzahl;
}
function CheckFilterFields($FilterFields) {
global $a_de_filter_msg_identifier;
global $a_de_filter_msg_main_table;
$SQLString = "SELECT ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".main_table, ";
$SQLString .= "SUM(" . TABLE_DATAEXCHANGE_FIELDS . ".option_identifier) AS number_identifier ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".field_id IN (" . implode(",", $FilterFields) . ") AND ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".main_table != '' ";
$SQLString .= "GROUP BY ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".main_table ";
// echo '$SQLString: ' . $SQLString . '
';
$MySQLQueryReference = mysql_query($SQLString);
if (mysql_num_rows($MySQLQueryReference) != 1) {
$CheckFilterFieldsReturnArray["error"] = true;
$CheckFilterFieldsReturnArray["error_message"] = $a_de_filter_msg_main_table;
} else {
$CheckFilterFieldsObject = mysql_fetch_object($MySQLQueryReference);
if ($CheckFilterFieldsObject->number_identifier < 1) {
$CheckFilterFieldsReturnArray["error"] = true;
$CheckFilterFieldsReturnArray["error_message"] = $a_de_filter_msg_identifier;
} else {
$CheckFilterFieldsReturnArray["error"] = false;
}
}
return $CheckFilterFieldsReturnArray;
}
function ImportCSV($ImportFileArray, $ImportFile, $FilterID, $Try = true, $LanguageID = 1) {
define("IMPORT_DEBUG", false);
define("IMPORT_ACITVE", true);
global $TableFieldTranslationDataArray;
global $a_de_history_record_singular;
global $a_de_history_message_update;
global $a_de_history_message_update_multi;
global $a_de_history_message_insert;
global $a_de_history_message_noinsert;
global $a_de_history_message_nochange;
global $a_de_history_message_update_extended;
global $a_de_history_message_update_multi_extended;
global $a_de_history_message_insert_extended;
global $a_de_history_message_noinsert_extended;
global $a_de_history_message_nochange_extended;
$ArtikelimportSettingsObject = GetDataexchangeSettings();
$TableFieldTranslationDataArray = GetTableFieldTranslationDataArray();
// ********************************************************************************
// ** Dateien pruefen und sichern
// ********************************************************************************
$ImportFileErrorArray = GetImportFile($ImportFileArray, $ImportFile);
if ($ImportFileErrorArray["error"] === true) {
return $ImportFileErrorArray;
} else {
$ImportFileName = $ImportFileErrorArray["import_file_name"];
$ImportOrgFileName = $ImportFileErrorArray["import_org_file_name"];
}
// ********************************************************************************
// ** Filter pruefen
// ********************************************************************************
$FilterErrorArray = CheckImportFilter($FilterID);
if ($FilterErrorArray["error"] === true) {
return $FilterErrorArray;
}
// ********************************************************************************
// ** Filterinformationen einlesen
// ********************************************************************************
$FilterObject = GetFilterDetail($FilterID, true, true);
// ********************************************************************************
// ** Datei einlesen
// ********************************************************************************
$FileHandler = fopen(IMPORTDATAPFAD . $ImportFileName, "r");
$RowCounter = 1;
while (($RowData = fgetcsv($FileHandler, $FilterObject->row_length, chr($FilterObject->delimiter_ascii), chr($FilterObject->enclosure_ascii))) !== false) {
if ($RowCounter >= $FilterObject->start_row) {
$CSVData[$RowCounter] = $RowData;
}
$RowCounter++;
}
fclose($FileHandler);
// ********************************************************************************
// ** Daten importieren
// ********************************************************************************
/**
* In der folgenden Schleife wird jede einzelne Zeile der CSV Datei druchgegangen
* und verarbeitet. In dieser Schleife ist eine weitere Schleife geschachtelt, die
* dann jedes einzelne Feld bearbeitet. Hierbei wir immer wieder auf die Information
* im $FilterObject bezug genommen.
*/
$DataexchangeStatus = GetDataexchangeStatusDataArray();
foreach ($CSVData as $RowNumber => $CSVRow) {
// ********************************************************************************
// ** Datensatz schon vorhanden
// ********************************************************************************
/**
* Hier wird über die Funktion CheckCSVRow() geprüft, ob der Datensatz anhand der
* Identifizierungsmerkmal in der Datenbank vorhanden ist. Über den Rückgabewert
* wird dann geprüft, ob der Datensatz eingefuegt oder aktualisiert werden soll.
* Wird mehr als ein Datensatz gefunden, wird ein Fehler für die zu importierende
* Zeile ein Fehler hinterlegt.
*/
$CheckCSVRowReturnArray = CheckCSVRow($CSVRow, $FilterObject, $LanguageID);
// extendet Message aufbereiten
foreach ($CheckCSVRowReturnArray["identifier_array"] as $Identifier) {
if ($ArtikelimportSettingsObject->show_db_details) {
$MessageExtendedIdentifier = $Identifier["table"] . "." . $Identifier["field"] . " = " . $Identifier["value"];
} else {
$MessageExtendedIdentifier = GetFieldName($Identifier["table"], $Identifier["field"]) . " = " . $Identifier["value"];
}
}
if ($CheckCSVRowReturnArray["error"]) {
$Report[$RowNumber]["message"] = $CheckCSVRowReturnArray["error_message"];
$Report[$RowNumber]["message_extended"] = sprintf($CheckCSVRowReturnArray["error_message_extended"], $MessageExtendedIdentifier);
$Report[$RowNumber]["import_status"] = $CheckCSVRowReturnArray["import_status"];
$Report[$RowNumber]["error"] = true;
$ReportStatus[$CheckCSVRowReturnArray["import_status"]]++;
continue;
} else {
$MainTableID = $CheckCSVRowReturnArray["main_table_id"];
$ImportType = $CheckCSVRowReturnArray["import_type"];
}
// ********************************************************************************
// ** Datensatz aktualisieren
// ********************************************************************************
/**
* Über die Funktion UpdateCSVRow() wird in Abhängigkeit des gewählten
* Relationstypen und der Spracheinstellungen die Satensätze eingefügt.
*/
if (($ImportType == "update") || ($ImportType == "insert")) {
$UpdateCSVRowReturnArray = UpdateCSVRow($CSVRow, $FilterObject, $LanguageID, $MainTableID, $Try, $KategorieArray);
if ($UpdateCSVRowReturnArray["error"]) {
$Report[$RowNumber]["message"] = $UpdateCSVRowReturnArray["message"];
$Report[$RowNumber]["import_status"] = $UpdateCSVRowReturnArray["import_status"];
$ReportStatus[$UpdateCSVRowReturnArray["import_status"]]++;
$RowNumber++;
} elseif ($UpdateCSVRowReturnArray["import_type"] == "update") {
$Report[$RowNumber]["message"] = $a_de_history_message_update;
$Report[$RowNumber]["message_extended"] = sprintf($a_de_history_message_update_extended, $MessageExtendedIdentifier);
$Report[$RowNumber]["field_array"] = $UpdateCSVRowReturnArray["field_array"];
$Report[$RowNumber]["import_status"] = $UpdateCSVRowReturnArray["import_status"];
$ReportStatus[$UpdateCSVRowReturnArray["import_status"]]++;
$RowNumber++;
} elseif ($UpdateCSVRowReturnArray["import_type"] == "insert") {
$Report[$RowNumber]["message"] = $a_de_history_message_insert;
$Report[$RowNumber]["message_extended"] = sprintf($a_de_history_message_insert_extended, $MessageExtendedIdentifier);
$Report[$RowNumber]["field_array"] = $UpdateCSVRowReturnArray["field_array"];
$Report[$RowNumber]["import_status"] = $UpdateCSVRowReturnArray["import_status"];
$ReportStatus[$UpdateCSVRowReturnArray["import_status"]]++;
$RowNumber++;
} elseif ($UpdateCSVRowReturnArray["import_type"] == "noinsert") {
$Report[$RowNumber]["message"] = $a_de_history_message_noinsert;
$Report[$RowNumber]["message_extended"] = sprintf($a_de_history_message_noinsert_extended, $MessageExtendedIdentifier);
$Report[$RowNumber]["import_status"] = $UpdateCSVRowReturnArray["import_status"];
$ReportStatus[$UpdateCSVRowReturnArray["import_status"]]++;
$RowNumber++;
} else {
$Report[$RowNumber]["message"] = $a_de_history_message_nochange;
$Report[$RowNumber]["message_extended"] = sprintf($a_de_history_message_nochange_extended, $MessageExtendedIdentifier);
$Report[$RowNumber]["import_status"] = $UpdateCSVRowReturnArray["import_status"];
$ReportStatus[$UpdateCSVRowReturnArray["import_status"]]++;
$RowNumber++;
}
}
if (IMPORT_DEBUG) { echo "
"; }
}
if ($Try) {
$ImportType = 1;
} else {
$ImportType = 2;
}
$HistoryID = SaveImportHistory($ImportOrgFileName, $ImportFileName, $ImportType, $Report, $ReportStatus, $FilterID, DATAEXCHANGE_TYPE_IMPORT, $FilterObject->option_history);
if (!$Try) {
CheckSpecialFunctions(DATAEXCHANGE_TYPE_IMPORT, $FilterID);
}
if (IMPORT_DEBUG) { exit; }
return $HistoryID;
}
function SaveImportHistory($ImportOrgFileName, $ImportFileName, $ImportType, $Report, $ReportStatus, $FilterID, $DataExchangeType, $OptionHistory) {
// Grunddaten sichern
$SQLString = "INSERT INTO " . TABLE_DATAEXCHANGE_HISTORY . " SET ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . ".dataexchange_type = '" . $DataExchangeType . "', ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . ".date = NOW(), ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . ".filename = '" . $ImportFileName . "', ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . ".filename_org = '" . $ImportOrgFileName . "', ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . ".type = '" . $ImportType . "', ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . ".filter_id = '" . $FilterID . "' ";
if (IMPORT_DEBUG) { echo '$SQLString: ' . $SQLString . '
'; }
$MySQLQueryReference = mysql_query($SQLString);
$HistoryID = mysql_insert_id();
// Status sichern
foreach ($ReportStatus as $StatusID => $StatusCounter) {
$SQLString = "INSERT INTO " . TABLE_DATAEXCHANGE_HISTORY_STATUS . " SET ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_STATUS . ".history_id = '" . $HistoryID . "', ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_STATUS . ".dataexchange_status_id = '" . $StatusID . "', ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_STATUS . ".count = '" . $StatusCounter . "' ";
if (IMPORT_DEBUG) { echo '$SQLString: ' . $SQLString . '
'; }
$MySQLQueryReference = mysql_query($SQLString);
}
if (!$OptionHistory) {
return $HistoryID;
}
// Zeilen sicher
foreach ($Report as $RowNumber => $Row) {
$SQLString = "INSERT INTO " . TABLE_DATAEXCHANGE_HISTORY_ROWS . " SET ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_ROWS . ".history_id = '" . $HistoryID . "', ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_ROWS . ".history_row_number = '" . $RowNumber . "', ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_ROWS . ".message = '" . addslashes($Row["message"]) . "', ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_ROWS . ".message_extend = '" . addslashes($Row["message_extended"]) . "', ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_ROWS . ".error = '" . $Row["error"] . "', ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_ROWS . ".status = '" . $Row["import_status"] . "' ";
if (IMPORT_DEBUG) { echo '$SQLString: ' . $SQLString . '
'; }
$MySQLQueryReference = mysql_query($SQLString);
if ($Row["field_array"]) {
foreach ($Row["field_array"] as $TableName => $TableData) {
if (!$TableName) {
continue;
}
$SQLString = "INSERT INTO " . TABLE_DATAEXCHANGE_HISTORY_TABLES . " SET ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_TABLES . ".history_id = '" . $HistoryID . "', ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_TABLES . ".history_row_number = '" . $RowNumber . "', ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_TABLES . ".table_name = '" . $TableName . "' ";
if (IMPORT_DEBUG) { echo '$SQLString: ' . $SQLString . '
'; }
$MySQLQueryReference = mysql_query($SQLString);
foreach ($TableData as $FieldName => $FieldData) {
$SQLString = "INSERT INTO " . TABLE_DATAEXCHANGE_HISTORY_FIELDS . " SET ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_FIELDS . ".history_id = '" . $HistoryID . "', ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_FIELDS . ".history_row_number = '" . $RowNumber . "', ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_FIELDS . ".table_name = '" . $TableName . "', ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_FIELDS . ".field_name = '" . $FieldName . "', ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_FIELDS . ".field_value_old = '" . $FieldData["value_old"] . "', ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_FIELDS . ".field_value_new = '" . $FieldData["value_new"] . "' ";
if (IMPORT_DEBUG) { echo '$SQLString: ' . $SQLString . '
'; }
$MySQLQueryReference = mysql_query($SQLString);
if ($FieldData["option_identifier"]) {
$SQLString = "INSERT INTO " . TABLE_DATAEXCHANGE_HISTORY_IDENTIFIER . " SET ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_IDENTIFIER . ".history_id = '" . $HistoryID . "', ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_IDENTIFIER . ".filter_id = '" . $FilterID . "', ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_IDENTIFIER . ".field_id = '" . $FieldData["field_id"] . "', ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_IDENTIFIER . ".value = '" . $FieldData["value_new"] . "' ";
if (IMPORT_DEBUG) { echo '$SQLString: ' . $SQLString . '
'; }
$MySQLQueryReference = mysql_query($SQLString);
}
}
}
}
}
return $HistoryID;
}
function GetTableFieldTranslationDataArray() {
$SQLString = "SELECT ";
$SQLString .= TABLE_DATAEXCHANGE_TABLES . ".table_name, ";
$SQLString .= TABLE_DATAEXCHANGE_TABLES . ".table_caption, ";
$SQLString .= TABLE_DATAEXCHANGE_TABLES_FIELDS . ".field_name, ";
$SQLString .= TABLE_DATAEXCHANGE_TABLES_FIELDS . ".field_caption ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_TABLES . " ";
$SQLString .= "LEFT JOIN " . TABLE_DATAEXCHANGE_TABLES_FIELDS . " ON " . TABLE_DATAEXCHANGE_TABLES . ".table_name = " . TABLE_DATAEXCHANGE_TABLES_FIELDS . ".table_name ";
$MySQLQueryReference = mysql_query($SQLString);
while ($DateRow = mysql_fetch_array($MySQLQueryReference)) {
$TableFieldTranslationDataArray[$DateRow["table_name"]]["table_name"] = $DateRow["table_name"];
$TableFieldTranslationDataArray[$DateRow["table_name"]]["table_caption"] = $DateRow["table_caption"];
$TableFieldTranslationDataArray[$DateRow["table_name"]]["field_array"][$DateRow["field_name"]]["field_name"] = $DateRow["field_name"];
$TableFieldTranslationDataArray[$DateRow["table_name"]]["field_array"][$DateRow["field_name"]]["field_caption"] = $DateRow["field_caption"];
}
return $TableFieldTranslationDataArray;
}
function GetImportFile($ImportFileArray, $ImportFile) {
if (!$ImportFile) {
// ********************************************************************************
// ** Datei pr�fen
// ********************************************************************************
$FileErrorArray = CheckImportFile($ImportFileArray);
if ($FileErrorArray["error"] === true) {
return $FileErrorArray;
}
// ********************************************************************************
// ** Datei sichern
// ********************************************************************************
if(!file_exists(DATEIPFAD . "admin/data/import/")) {
mkdir(DATEIPFAD . "admin/data/import/", 0777, TRUE);
}
$ImportFileName = date("Ymd_His") . ".csv";
$ImportOrgFileName = $ImportFileArray["name"];
move_uploaded_file($ImportFileArray["tmp_name"], IMPORTDATAPFAD . $ImportFileName);
chmod(IMPORTDATAPFAD . $ImportFileName, 0777);
$GetImportFileResultArray["import_file_name"] = $ImportFileName;
$GetImportFileResultArray["import_org_file_name"] = $ImportOrgFileName;
} else {
// automatisierter Import
if (substr($ImportFile, 0, strlen(TEMPDATAPFAD)) == TEMPDATAPFAD) {
$ImportFileName = date("Ymd_His") . ".csv";
$ImportOrgFileName = substr($ImportFile, strlen(TEMPDATAPFAD) + 1);
copy($ImportFile, IMPORTDATAPFAD . $ImportFileName);
$GetImportFileResultArray["import_file_name"] = $ImportFileName;
$GetImportFileResultArray["import_org_file_name"] = $ImportOrgFileName;
} else {
$SQLString = "SELECT ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . ".filename_org ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . ".filename = '" . $ImportFile . "' ";
$ImportFileObject = mysql_fetch_object(mysql_query($SQLString));
$ImportFileName = date("Ymd_His") . ".csv";
$ImportOrgFileName = $ImportFileObject->filename_org;
copy(IMPORTDATAPFAD . $ImportFile, IMPORTDATAPFAD . $ImportFileName);
$GetImportFileResultArray["import_file_name"] = $ImportFileName;
$GetImportFileResultArray["import_org_file_name"] = $ImportOrgFileName;
}
}
return $GetImportFileResultArray;
}
function CheckImportFile($ImportFileArray) {
global $a_de_import_error_nocsv, $a_de_import_error_nofile;
$FileErrorArray["error"] = false;
if ($ImportFileArray["tmp_name"]) {
$ImportFileNameArray = explode(".", $ImportFileArray["name"]);
if ((strtolower($ImportFileNameArray[count($ImportFileNameArray) - 1]) != 'csv') && (strtolower($ImportFileNameArray[count($ImportFileNameArray) - 1]) != 'txt')) {
$FileErrorArray["error"] = true;
$FileErrorArray["error_message"] = sprintf($a_de_import_error_nocsv, $ImportFileArray["name"]);
}
} else {
$FileErrorArray["error"] = true;
$FileErrorArray["error_message"] = $a_de_import_error_nofile;
}
return $FileErrorArray;
}
function CheckImportFilter($FilterID) {
global $a_de_import_error_nofields, $a_de_import_error_nofieldsidentifier;
// Filterinformationen einlesen
$FilterObject = GetFilterDetail($FilterID);
$FieldDataArray = GetFilterFieldDataArray($FilterID);
$FilterErrorArray["error"] = false;
$FilterFieldFound = false;
$FilterFieldIdentifierFound = false;
foreach ($FieldDataArray as $FieldData) {
$FilterFieldFound = true;
if ($FieldData["option_identifier"]) {
$FilterFieldIdentifierFound = true;
}
}
if (!$FilterFieldFound) {
$FilterErrorArray["error"] = true;
$FilterErrorArray["error_message"] = sprintf($a_de_import_error_nofields, $FilterObject->filter_name);
}
if (!$FilterFieldIdentifierFound && $FilterFieldFound) {
$FilterErrorArray["error"] = true;
$FilterErrorArray["error_message"] = sprintf($a_de_import_error_nofieldsidentifier, $FilterObject->filter_name);
}
return $FilterErrorArray;
}
function GetDataexchangeStatusDataArray($FilterDataExchangeType = '') {
$SQLString = "SELECT ";
$SQLString .= TABLE_DATAEXCHANGE_STATUS . ".dataexchange_status_id, ";
$SQLString .= TABLE_DATAEXCHANGE_STATUS . ".status_text, ";
$SQLString .= TABLE_DATAEXCHANGE_STATUS . ".dataexchange_type, ";
$SQLString .= TABLE_DATAEXCHANGE_STATUS . ".image ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_STATUS . " ";
$SQLString .= "WHERE (";
// Import/Export
if ($FilterDataExchangeType) {
$SQLString .= "(" . TABLE_DATAEXCHANGE_STATUS . ".dataexchange_type = '" . $FilterDataExchangeType . "') AND ";
}
$SQLString .= " 1)";
$SQLString .= "ORDER BY " . TABLE_DATAEXCHANGE_STATUS . ".sort ASC ";
// echo '$SQLString: ' . $SQLString . '
';
$MySQLQueryReference = mysql_query($SQLString);
while ($StatusRow = mysql_fetch_array($MySQLQueryReference)) {
$DataexchangeStatusDataArray[$StatusRow["dataexchange_status_id"]] = $StatusRow;
}
return $DataexchangeStatusDataArray;
}
/**
* Pruefung vor dem Import, ob die Daten schon vorhanden sind
*
* Hier wird anhand des Filters die Datenbank abgefragt, ob der Datensatz, so wie er in
* CSV Datei eingetragen ist schon vorhanden ist. Es wird ueber alle Relationstypen
* abgefragt. Als Ergbnis wird entweder ein Fehler zur�ckgegeben oder die Art des Imports.
* Also entweder wird der Datensatz aus der CSV Datei eingef�gt oder der Datensatz anhand
* des Eintrags aus der CSV Datei engepasst.
*
*/
function CheckCSVRow($CSVRow, $FilterObject, $LanguageID) {
global $a_de_history_message_error_tomanydata, $a_de_history_message_error_tomanydata_extended;
global $a_de_history_message_noimportmandatory, $a_de_history_message_noimportmandatory_extended;
$SQLIdentifierField = "";
$SQLIdentifierTable = "";
$SQLIdentifierJoinArray = array();
$SQLIdentifierWhereArray = array();
$IdentifierArray = array();
$IdentifierCounter = 0;
$OptionMandatoryCheck = true;
$OptionMandatoryCheckFieldArray = array();
foreach ($CSVRow as $ColumnNumber => $CSVValue) {
// Feldinformationen einlesen
$FieldObject = $FilterObject->field_array[$ColumnNumber];
// Pflichtfeld
if ($FieldObject->option_mandatory && ($CSVValue == '')) {
$OptionMandatoryCheck = false;
$OptionMandatoryCheckFieldArray[] = $FieldObject->field_name;
}
// Mapping aufbauen
if ($FieldObject->option_identifier && $FieldObject->option_insert == OPTION_INSERT_MAPPING) {
$FoundMapping = false;
// Eintrag in der Mappingtabelle vorhanden
foreach ($FieldObject->mapping_array as $MappingData) {
if ($MappingData["value_csv"] == $CSVValue) {
$CSVValue = $MappingData["value_mapping"];
$FoundMapping = true;
}
}
// Standardwert falls kein Eintrag gefunden
if (!$FoundMapping) {
$CSVValue = $FieldObject->mapping_default_value;
}
}
// Wenn es ein Feld zur Identifikation des Datensatzen ist, dieses fuer eine Abfrage
// in der Datenbank in Abhaengigkeit des Relationstypen und der Spracheinstellung
// ablegen
if ($FieldObject->option_identifier) {
switch ($FieldObject->relation_type) {
case RELATIONTYPE_11:
$SQLIdentifierField = $FieldObject->main_table . "." . $FieldObject->main_idfield;
$SQLIdentifierTable = $FieldObject->main_table;
if (!$FieldObject->option_language) {
$SQLIdentifierWhereArray[] = $FieldObject->main_table . "." . $FieldObject->main_field . " = '" . $CSVValue . "'";
$IdentifierArray[$IdentifierCounter]["table"] = $FieldObject->main_table;
$IdentifierArray[$IdentifierCounter]["field"] = $FieldObject->main_field;
$IdentifierArray[$IdentifierCounter]["value"] = $CSVValue;
} else {
$SQLIdentifierWhereArray[] = $FieldObject->language_table . "." . $FieldObject->language_field . " = '" . $CSVValue . "'";
$IdentifierArray[$IdentifierCounter]["table"] = $FieldObject->language_table;
$IdentifierArray[$IdentifierCounter]["field"] = $FieldObject->language_field;
$IdentifierArray[$IdentifierCounter]["value"] = $CSVValue;
}
// if ($FieldObject->option_language) {
// $SQLIdentifierJoinArray[] = "LEFT JOIN " . $FieldObject->language_table . " ON ((" . $FieldObject->main_table . "." . $FieldObject->main_idfield . " = " . $FieldObject->language_table . "." . $FieldObject->language_idfield . ") AND (" . $FieldObject->language_table . "." . $FieldObject->language_languagefield . " = " . $LanguageID . "))";
// }
break;
case RELATIONTYPE_1N:
if (!$FieldObject->option_language) {
$SQLIdentifierWhereArray[] = $FieldObject->target_table . "." . $FieldObject->target_field . " = '" . $CSVValue . "'";
$IdentifierArray[$IdentifierCounter]["table"] = $FieldObject->target_table;
$IdentifierArray[$IdentifierCounter]["field"] = $FieldObject->target_field;
$IdentifierArray[$IdentifierCounter]["value"] = $CSVValue;
} else {
$SQLIdentifierWhereArray[] = $FieldObject->language_table . "." . $FieldObject->language_field . " = '" . $CSVValue . "'";
$IdentifierArray[$IdentifierCounter]["table"] = $FieldObject->language_table;
$IdentifierArray[$IdentifierCounter]["field"] = $FieldObject->language_field;
$IdentifierArray[$IdentifierCounter]["value"] = $CSVValue;
}
$SQLIdentifierJoinArray[] = "LEFT JOIN " . $FieldObject->target_table . " ON " . $FieldObject->main_table . "." . $FieldObject->main_field . " = " . $FieldObject->target_table . "." . $FieldObject->target_idfield;
if ($FieldObject->option_language) {
$SQLIdentifierJoinArray[] = "LEFT JOIN " . $FieldObject->language_table . " ON (( " . $FieldObject->target_table . "." . $FieldObject->target_idfield . " = " . $FieldObject->language_table . "." . $FieldObject->language_idfield . ") AND (" . $FieldObject->language_table . "." . $FieldObject->language_languagefield . " = " . $LanguageID . "))";
}
break;
case RELATIONTYPE_NM:
if (!$FieldObject->option_language) {
$SQLIdentifierWhereArray[] = $FieldObject->target_table . "." . $FieldObject->target_field . " = '" . $CSVValue . "'";
$IdentifierArray[$IdentifierCounter]["table"] = $FieldObject->target_table;
$IdentifierArray[$IdentifierCounter]["field"] = $FieldObject->target_field;
$IdentifierArray[$IdentifierCounter]["value"] = $CSVValue;
} else {
$SQLIdentifierWhereArray[] = $FieldObject->language_table . "." . $FieldObject->language_field . " = '" . $CSVValue . "'";
$IdentifierArray[$IdentifierCounter]["table"] = $FieldObject->language_table;
$IdentifierArray[$IdentifierCounter]["field"] = $FieldObject->language_field;
$IdentifierArray[$IdentifierCounter]["value"] = $CSVValue;
}
$SQLIdentifierJoinArray[] = "LEFT JOIN " . $FieldObject->relation_table . " ON " . $FieldObject->main_table . "." . $FieldObject->main_idfield . " = " . $FieldObject->relation_table . "." . $FieldObject->relation_main_idfield;
$SQLIdentifierJoinArray[] = "LEFT JOIN " . $FieldObject->target_table . " ON " . $FieldObject->relation_table . "." . $FieldObject->relation_target_idfield . " = " . $FieldObject->target_table . "." . $FieldObject->target_idfield;
if ($FieldObject->option_language) {
$SQLIdentifierJoinArray[] = "LEFT JOIN " . $FieldObject->language_table . " ON ((" . $FieldObject->target_table . "." . $FieldObject->target_idfield . " = " . $FieldObject->language_table . "." . $FieldObject->language_idfield . ") AND (" . $FieldObject->language_table . "." . $FieldObject->language_languagefield . " = " . $LanguageID . "))";
}
break;
}
}
$IdentifierCounter++;
}
// doppelte Eintraege entfernen
$SQLIdentifierJoinArray = array_unique($SQLIdentifierJoinArray);
$SQLIdentifierWhereArray = array_unique($SQLIdentifierWhereArray);
// Datensatz abfragen
$SQLString = "SELECT ";
$SQLString .= $SQLIdentifierField . " ";
$SQLString .= "FROM ";
$SQLString .= $SQLIdentifierTable . " ";
$SQLString .= implode(" ", $SQLIdentifierJoinArray) . " ";
$SQLString .= "WHERE ";
$SQLString .= implode(" AND ", $SQLIdentifierWhereArray);
if ($SQLIdentifierTable == "shop_artikel") {
$SQLString .= "AND ";
$SQLString .= "((" . $SQLIdentifierTable . ".merkmalkombination = 0) OR (" . $SQLIdentifierTable . ".merkmalkombinationparentid != 0)) ";
}
if (IMPORT_DEBUG) { echo '$SQLString (001): ' . $SQLString . '
'; }
$MySQLQueryReference = mysql_query($SQLString);
// Pflichtfelder
if (!$OptionMandatoryCheck) {
$CheckCSVRowReturnArray["error"] = true;
$CheckCSVRowReturnArray["error_message"] = $a_de_history_message_noimportmandatory;
$CheckCSVRowReturnArray["error_message_extended"] = sprintf($a_de_history_message_noimportmandatory_extended, implode(',', $OptionMandatoryCheckFieldArray));
$CheckCSVRowReturnArray["import_status"] = IMPORT_STATUS_ERROR;
$CheckCSVRowReturnArray["identifier_array"] = $IdentifierArray;
// kein Datensatz vorhanden
} elseif (mysql_num_rows($MySQLQueryReference) == 0) {
$CheckCSVRowReturnArray["success"] = true;
$CheckCSVRowReturnArray["import_type"] = "insert";
$CheckCSVRowReturnArray["identifier_array"] = $IdentifierArray;
// genau ein Datensatz vorhanden
} elseif (mysql_num_rows($MySQLQueryReference) == 1) {
$MainTableArray = mysql_fetch_array($MySQLQueryReference, MYSQL_NUM);
$MainTableID = $MainTableArray[0];
$CheckCSVRowReturnArray["success"] = true;
$CheckCSVRowReturnArray["import_type"] = "update";
$CheckCSVRowReturnArray["main_table_id"] = $MainTableID;
$CheckCSVRowReturnArray["identifier_array"] = $IdentifierArray;
// mehr als ein Datensatz vorhanden
} elseif (mysql_num_rows($MySQLQueryReference) > 1) {
$CheckCSVRowReturnArray["error"] = true;
$CheckCSVRowReturnArray["error_message"] = $a_de_history_message_error_tomanydata;
$CheckCSVRowReturnArray["error_message_extended"] = $a_de_history_message_error_tomanydata_extended;
$CheckCSVRowReturnArray["import_status"] = IMPORT_STATUS_ERROR;
$CheckCSVRowReturnArray["identifier_array"] = $IdentifierArray;
}
return $CheckCSVRowReturnArray;
}
function GetFieldName($TableName, $FieldName) {
global $TableFieldTranslationDataArray;
global $a_de_field_default;
if ($TableName == $a_de_field_default) {
return $FieldName;
}
$Einstellungen = GetEinstellungen('', 'dataexchange');
// $TableName = substr($TableName, strlen(TABLE_PREFIX));
if (isset($TableFieldTranslationDataArray[$TableName]["field_array"][$FieldName])) {
if ($Einstellungen->dataexchange->show_db_details) {
return $TableFieldTranslationDataArray[$TableName]["field_array"][$FieldName]["field_caption"] . " (" . $TableName . "." . $FieldName . ")";
} else {
return $TableFieldTranslationDataArray[$TableName]["field_array"][$FieldName]["field_caption"];
}
} else {
return $TableName . "." . $FieldName;
}
}
function GetHistoryDetail($HistoryID) {
global $a_de_history_type_import_try, $a_de_history_type_import, $a_de_history_type_export;
$SQLString = "SELECT ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . ".date, ";
$SQLString .= "DATE_FORMAT(" . TABLE_DATAEXCHANGE_HISTORY . ".date, '%d.%m.%Y - %H:%i:%s') AS date_format, ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . ".filename, ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . ".filename_org, ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . ".type, ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . ".filter_id, ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . ".dataexchange_type ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . ".history_id = '" . $HistoryID . "' ";
$HistoryObject = mysql_fetch_object(mysql_query($SQLString));
if ($HistoryObject->dataexchange_type == DATAEXCHANGE_TYPE_IMPORT) {
if ($HistoryObject->type == 1) {
$HistoryObject->type_text = $a_de_history_type_import_try;
} else {
$HistoryObject->type_text = $a_de_history_type_import;
}
} else {
$HistoryObject->type_text = $a_de_history_type_export;
}
return $HistoryObject;
}
function GetHistoryStatusArray($HistoryID) {
global $a_de_history_record_singular, $a_de_history_record_plural;
$SQLString = "SELECT ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_STATUS . ".dataexchange_status_id, ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_STATUS . ".count, ";
$SQLString .= TABLE_DATAEXCHANGE_STATUS . ".status_text, ";
$SQLString .= TABLE_DATAEXCHANGE_STATUS . ".image ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_STATUS . " ";
$SQLString .= "LEFT JOIN " . TABLE_DATAEXCHANGE_STATUS . " ON " . TABLE_DATAEXCHANGE_HISTORY_STATUS . ".dataexchange_status_id = " . TABLE_DATAEXCHANGE_STATUS . ".dataexchange_status_id ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_STATUS . ".history_id = '" . $HistoryID . "' ";
$SQLString .= "ORDER BY " . TABLE_DATAEXCHANGE_STATUS . ".sort ASC ";
// echo '$SQLString: ' . $SQLString . '
';
$MySQLQueryReference = mysql_query($SQLString);
$HistoryStatusCounter = 0;
while ($HistoryStatusRow = mysql_fetch_array($MySQLQueryReference)) {
$HistoryStatusArray[$HistoryStatusCounter] = $HistoryStatusRow;
$HistoryStatusArray[$HistoryStatusCounter]["status_image"] = "![](\"../images/")
";
if ($HistoryStatusRow["count"] > 1) {
$HistoryStatusArray[$HistoryStatusCounter]["record_text"] = $a_de_history_record_plural;
} else {
$HistoryStatusArray[$HistoryStatusCounter]["record_text"] = $a_de_history_record_singular;
}
$HistoryStatusCounter++;
}
return $HistoryStatusArray;
}
function GetHistoryRowDataArray($HistoryID, $ShowDetails, $SortField, $SortOrder, $DataOffset, $DataCount, $FilterStatusArray) {
$HistoryRowDataArray = array();
$SQLString = "SELECT ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_ROWS . ".history_row_number, ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_ROWS . ".message, ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_ROWS . ".message_extend, ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_ROWS . ".error, ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_ROWS . ".status, ";
$SQLString .= TABLE_DATAEXCHANGE_STATUS . ".image ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_ROWS . " ";
$SQLString .= "LEFT JOIN " . TABLE_DATAEXCHANGE_STATUS . " ON " . TABLE_DATAEXCHANGE_HISTORY_ROWS . ".status = " . TABLE_DATAEXCHANGE_STATUS . ".dataexchange_status_id ";
$SQLString .= "WHERE ";
$SQLString .= "(";
$SQLString .= "(" . TABLE_DATAEXCHANGE_HISTORY_ROWS . ".history_id = '" . $HistoryID . "') AND ";
if (count($FilterStatusArray) > 0) {
$SQLString .= "(" . TABLE_DATAEXCHANGE_HISTORY_ROWS . ".status IN (" . implode(",", $FilterStatusArray) . ")) AND ";
} else {
$SQLString .= "(" . TABLE_DATAEXCHANGE_HISTORY_ROWS . ".status = -1) AND ";
}
$SQLString .= " 1)";
// Sortierung
if ($SortField && $SortOrder) {
$SQLString .= "ORDER BY " . $SortField . " " . $SortOrder . " ";
}
// Limitierung
if (($DataOffset !== '') && ($DataCount !== '')) {
$SQLString .= "LIMIT " . $DataOffset . ", " . $DataCount . " ";
}
// echo '$SQLString: ' . $SQLString . '
';
$MySQLQueryReference = mysql_query($SQLString);
while ($HistoryRowRow = mysql_fetch_array($MySQLQueryReference)) {
$RowNumber = $HistoryRowRow["history_row_number"];
$HistoryRowDataArray[$RowNumber]["message"] = $HistoryRowRow["message"];
$HistoryRowDataArray[$RowNumber]["message_extend"] = $HistoryRowRow["message_extend"];
$HistoryRowDataArray[$RowNumber]["error"] = $HistoryRowRow["error"];
$HistoryRowDataArray[$RowNumber]["status"] = $HistoryRowRow["status"];
$HistoryRowDataArray[$RowNumber]["status_image"] = "";
if ($ShowDetails) {
$SQLString = "SELECT ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_TABLES . ".table_name, ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_TABLES . ".table_type, ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_TABLES . ".message, ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_TABLES . ".status ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_TABLES . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_TABLES . ".history_id = '" . $HistoryID . "' AND ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_TABLES . ".history_row_number = '" . $RowNumber . "' ";
$MySQLQueryTableReference = mysql_query($SQLString);
while ($HistoryTableRow = mysql_fetch_array($MySQLQueryTableReference)) {
$TableName = $HistoryTableRow["table_name"];
$SQLString = "SELECT ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_FIELDS . ".field_name, ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_FIELDS . ".field_value_old, ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_FIELDS . ".field_value_new ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_FIELDS . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_FIELDS . ".history_id = '" . $HistoryID . "' AND ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_FIELDS . ".history_row_number = '" . $RowNumber . "' AND ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_FIELDS . ".table_name = '" . $TableName . "' ";
$MySQLQueryFieldReference = mysql_query($SQLString);
while ($HistoryFieldRow = mysql_fetch_array($MySQLQueryFieldReference)) {
if ($HistoryTableRow["table_type"] == 0) {
$HistoryRowDataArray[$RowNumber]["table_array"][$TableName][$HistoryFieldRow["field_name"]]["old_value"] = $HistoryFieldRow["field_value_old"];
$HistoryRowDataArray[$RowNumber]["table_array"][$TableName][$HistoryFieldRow["field_name"]]["new_value"] = $HistoryFieldRow["field_value_new"];
if (strlen($HistoryFieldRow["field_value_new"]) > 100) {
$HistoryRowDataArray[$RowNumber]["table_array"][$TableName][$HistoryFieldRow["field_name"]]["new_value_short"] = substr($HistoryFieldRow["field_value_new"], 0, 100) . " ...";
} else {
$HistoryRowDataArray[$RowNumber]["table_array"][$TableName][$HistoryFieldRow["field_name"]]["new_value_short"] = $HistoryFieldRow["field_value_new"];
}
} elseif ($HistoryTableRow["table_type"] == 2) {
$HistoryRowDataArray[$RowNumber]["relation_table_array"][$TableName][$HistoryFieldRow["field_name"]]["old_value"] = $HistoryFieldRow["field_value_old"];
$HistoryRowDataArray[$RowNumber]["relation_table_array"][$TableName][$HistoryFieldRow["field_name"]]["new_value"] = $HistoryFieldRow["field_value_new"];
if (strlen($HistoryFieldRow["field_value_new"]) > 20) {
$HistoryRowDataArray[$RowNumber]["relation_table_array"][$TableName][$HistoryFieldRow["field_name"]]["new_value_short"] = substr($HistoryFieldRow["field_value_new"], 0, 20) . " ...";
} else {
$HistoryRowDataArray[$RowNumber]["relation_table_array"][$TableName][$HistoryFieldRow["field_name"]]["new_value_short"] = $HistoryFieldRow["field_value_new"];
}
}
}
}
}
}
return $HistoryRowDataArray;
}
function GetHistoryRowAnzahl($HistoryID, $FilterStatusArray, $FilterAllStatus = false) {
$SQLString = "SELECT ";
$SQLString .= "COUNT(" . TABLE_DATAEXCHANGE_HISTORY_ROWS . ".history_id) AS history_row_anzahl ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_ROWS . " ";
$SQLString .= "WHERE ";
$SQLString .= "(";
$SQLString .= "(" . TABLE_DATAEXCHANGE_HISTORY_ROWS . ".history_id = '" . $HistoryID . "') AND ";
if (count($FilterStatusArray) > 0) {
$SQLString .= "(" . TABLE_DATAEXCHANGE_HISTORY_ROWS . ".status IN (" . implode(",", $FilterStatusArray) . ")) AND ";
} elseif ($FilterAllStatus) {
$SQLString .= "(" . TABLE_DATAEXCHANGE_HISTORY_ROWS . ".status > 0) AND ";
} else {
$SQLString .= "(" . TABLE_DATAEXCHANGE_HISTORY_ROWS . ".status = -1) AND ";
}
$SQLString .= " 1)";
// echo '$SQLString: ' . $SQLString . '
';
$HistoryRowObject = mysql_fetch_object(mysql_query($SQLString));
return $HistoryRowObject->history_row_anzahl;
}
function GetHistoryDataArray($SortField = '', $SortOrder = '', $DataOffset = '', $DataCount = '', $FilterDataExchangeType = '') {
global $a_de_history_type_import_try, $a_de_history_type_import, $a_de_history_type_export;
$SQLString = "SELECT ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . ".history_id, ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . ".date, ";
$SQLString .= "DATE_FORMAT(" . TABLE_DATAEXCHANGE_HISTORY . ".date, '%d.%m.%Y - %H:%i:%s') AS date_format, ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . ".filename, ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . ".dataexchange_type, ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . ".filename_org, ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . ".type ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . " ";
$SQLString .= "WHERE (";
// Import/Export
if ($FilterDataExchangeType) {
$SQLString .= "(" . TABLE_DATAEXCHANGE_HISTORY . ".dataexchange_type = '" . $FilterDataExchangeType . "') AND ";
}
$SQLString .= " 1)";
// Sortierung
if ($SortField && $SortOrder) {
$SQLString .= "ORDER BY " . $SortField . " " . $SortOrder . " ";
}
// Limitierung
if (($DataOffset !== '') && ($DataCount !== '')) {
$SQLString .= "LIMIT " . $DataOffset . ", " . $DataCount . " ";
}
// echo '$SQLString: ' . $SQLString . '
';
$MySQLQueryReference = mysql_query($SQLString);
$HistoryDataArray = array();
$HistoryCounter = 0;
while ($HistoryRow = mysql_fetch_array($MySQLQueryReference, MYSQL_ASSOC)) {
$HistoryDataArray[$HistoryCounter] = $HistoryRow;
if ($HistoryRow["dataexchange_type"] == DATAEXCHANGE_TYPE_IMPORT) {
if ($HistoryRow["type"] == 1) {
$HistoryDataArray[$HistoryCounter]["type_text"] = $a_de_history_type_import_try;
} else {
$HistoryDataArray[$HistoryCounter]["type_text"] = $a_de_history_type_import;
}
} else {
$HistoryDataArray[$HistoryCounter]["type_text"] = $a_de_history_type_export;
}
$HistoryCounter++;
}
return $HistoryDataArray;
}
function GetHistoryAnzahl($FilterDataExchangeType = '') {
$SQLString = "SELECT ";
$SQLString .= "COUNT(" . TABLE_DATAEXCHANGE_HISTORY . ".history_id) AS history_anzahl ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . " ";
$SQLString .= "WHERE (";
// Import/Export
if ($FilterDataExchangeType) {
$SQLString .= "(" . TABLE_DATAEXCHANGE_HISTORY . ".dataexchange_type = '" . $FilterDataExchangeType . "') AND ";
}
$SQLString .= " 1)";
// echo '$SQLString: ' . $SQLString . '
';
$HistoryObject = mysql_fetch_object(mysql_query($SQLString));
return $HistoryObject->history_anzahl;
}
function SaveFilterDefaultField($FilterID, $DefaultField, $CheckMandatory = true) {
if (!is_array($DefaultField)) {
$DefaultField = array();
}
global $a_de_ex_export_error_default_fields;
if ($CheckMandatory) {
// Pruefung, ob es Pflichtfelder sind
$SQLString = "SELECT ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".field_id ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_FIELDS . " ";
$SQLString .= "LEFT JOIN " . TABLE_DATAEXCHANGE_FIELDS . " ON " . TABLE_DATAEXCHANGE_FILTERS_FIELDS . ".field_id = " . TABLE_DATAEXCHANGE_FIELDS . ".field_id ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_FIELDS . ".filter_id = '" . $FilterID . "' AND ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".field_type = " . FIELDTYPE_DEFAULT . " AND ";
$SQLString .= TABLE_DATAEXCHANGE_FIELDS . ".option_mandatory = 1 ";
$MySQLQueryReference = mysql_query($SQLString);
while ($FieldRow = mysql_fetch_array($MySQLQueryReference, MYSQL_ASSOC)) {
if (trim($DefaultField[$FieldRow["field_id"]]) == "") {
$ExportResult["error"] = true;
$ExportResult["error_message"] = $a_de_ex_export_error_default_fields;
return $ExportResult;
}
}
}
// alte Daten loschen
$SQLString = "DELETE FROM " . TABLE_DATAEXCHANGE_FILTERS_FIELDS_DEFAULT . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_FIELDS_DEFAULT . ".filter_id = '" . $FilterID . "' ";
$MySQLQueryReference = mysql_query($SQLString);
// neuen Daten sichern
foreach ($DefaultField as $FieldID => $FieldValue) {
$SQLString = "INSERT INTO " . TABLE_DATAEXCHANGE_FILTERS_FIELDS_DEFAULT . " SET ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_FIELDS_DEFAULT . ".filter_id = '" . $FilterID . "', ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_FIELDS_DEFAULT . ".field_id = '" . $FieldID . "', ";
$SQLString .= TABLE_DATAEXCHANGE_FILTERS_FIELDS_DEFAULT . ".option_default_field_value = '" . $FieldValue . "' ";
// echo '$SQLString: ' . $SQLString . '
';
$MySQLQueryReference = mysql_query($SQLString);
}
}
function GetDataexchangeSettings() {
$Einstellungen = GetEinstellungen('', 'dataexchange');
return $Einstellungen->dataexchange;
}
function ExportCSV($FilterID, $LanguageID) {
define("IMPORT_DEBUG", false);
define("EXPORT_DEBUG", false);
define("EXPORT_ACITVE", true);
global $TableFieldTranslationDataArray;
global $a_de_history_message_export;
global $a_de_history_message_export_extended;
global $a_de_history_message_noexportselection;
global $a_de_history_message_noexportselection_extended;
global $a_de_history_message_noexportmandatory;
global $a_de_history_message_noexportmandatory_extended;
global $a_de_field_default;
$Einstellungen = GetEinstellungen('', 'dataexchange');
$DataexchangeSettingsObject = $Einstellungen->dataexchange;
$TableFieldTranslationDataArray = GetTableFieldTranslationDataArray();
// // ********************************************************************************
// // ** Dateien pr�fen und sichern
// // ********************************************************************************
//
// $ImportFileErrorArray = GetImportFile($ImportFileArray, $ImportFile);
//
// if ($ImportFileErrorArray["error"] === true) {
// return $ImportFileErrorArray;
// } else {
//
// $ImportFileName = $ImportFileErrorArray["import_file_name"];
// $ImportOrgFileName = $ImportFileErrorArray["import_org_file_name"];
//
// }
// ********************************************************************************
// ** Filter pr�fen
// ********************************************************************************
// $FilterErrorArray = CheckImportFilter($FilterID);
//
// if ($FilterErrorArray["error"] === true) {
// return $FilterErrorArray;
// }
// ********************************************************************************
// ** Filterinformationen einlesen
// ********************************************************************************
$FilterObject = GetFilterDetail($FilterID, true, true);
$FieldDefaultDataArray = GetFieldDefaultDataArray($FilterID);
$FilterDelimiter = chr($FilterObject->delimiter_ascii);
$FilterEnclosure = chr($FilterObject->enclosure_ascii);
$FilterCSVHeadline = $FilterObject->option_csv_headline;
// ********************************************************************************
// ** Datei anlegen/oeffnen
// ********************************************************************************
$Filename = preg_replace("/[^a-zA-Z0-9]/", "", $FilterObject->filter_name);
$Filename = strtolower($Filename);
$Filename .= "_" . date("Ymd_His");
$Filename .= ".csv";
if(!file_exists(DATEIPFAD . "admin/data/export/")) {
mkdir(DATEIPFAD . "admin/data/export/", 0777, TRUE);
}
$FileHandler = fopen(EXPORTDATAPFAD . $Filename, "w+");
// ********************************************************************************
// ** Filterinformationen aufbereiten
// ********************************************************************************
$SQLJoinArray = array();
$SQLLanguageWhereArray = array();
$SQLFieldArray = array();
$SQLFieldSelectionArray = array();
$FieldInformationArray = array();
$FieldCounter = 0;
$IdentifierArray = array();
$IdentifierFieldArray = array();
$FieldSelection = false;
// echo '';
// var_dump($FilterObject);
// echo '
';
//
// exit;
foreach ($FilterObject->field_array as $FieldInformation) {
$FoundSQLJoinElement = false;
// Selektionsfeld
if ($FieldInformation->field_type == FIELDTYPE_SELECTION) {
$FieldSelection = true;
}
// Vorgabfeld
if ($FieldInformation->field_type == FIELDTYPE_DEFAULT) {
$SQLFieldArray[] = "'" . $FieldDefaultDataArray[$FieldInformation->field_id]["option_default_field_value"] . "' AS '" . $FieldInformation->field_csv_caption . "'";
if ($FilterObject->option_history) {
$FieldInformationArray[$FieldCounter]["table_name"] = $a_de_field_default;
$FieldInformationArray[$FieldCounter]["field_name"] = $FieldInformation->field_name;
$FieldInformationArray[$FieldCounter]["option_identifier"] = 0;
$FieldInformationArray[$FieldCounter]["option_insert_default_value"] = 1;
$FieldInformationArray[$FieldCounter]["field_id"] = $FieldInformation->field_id;
}
}
// Leerfeld
if ($FieldInformation->field_type == FIELDTYPE_NULL) {
$SQLFieldArray[] = "'' AS ''";
$FieldInformationArray[$FieldCounter]["option_sppace"] = 1;
}
// Datenbankfeld
if (($FieldInformation->field_type == FIELDTYPE_DATABASE) || ($FieldInformation->field_type == FIELDTYPE_SELECTION)) {
if ($FieldInformation->relation_type == RELATIONTYPE_11) {
if (!$FieldInformation->language_table) {
if ($FieldInformation->field_alias) {
$SQLTable = $FieldInformation->main_table . " AS " . $FieldInformation->main_table . "_" . $FieldInformation->field_alias;
} else {
$SQLTable = $FieldInformation->main_table;
}
if (($FieldInformation->field_type == FIELDTYPE_DATABASE)) {
if ($FieldInformation->field_alias) {
$SQLFieldArray[] = $FieldInformation->main_table . "_" . $FieldInformation->field_alias . "." . $FieldInformation->main_field . " AS '" . $FieldInformation->field_csv_caption . "'";
} else {
$SQLFieldArray[] = $FieldInformation->main_table . "." . $FieldInformation->main_field . " AS '" . $FieldInformation->field_csv_caption . "'";
}
} else {
$SQLFieldSelectionName = "(IF(";
foreach ($FieldInformation->selection_array as $FieldSelectionKey => $FieldSelectionElement) {
$OperationLogical = $FieldInformation->selection_array[$FieldSelectionKey + 1]["operation_logical"] . " ";
if ($FieldInformation->field_alias) {
$SQLFieldSelectionName .= "(" . $FieldInformation->main_table . "_" . $FieldInformation->field_alias . "." . $FieldInformation->main_field . " " . $FieldSelectionElement["operation_comparison"] . " '" . $FieldSelectionElement["operation_value"] . "') " . $OperationLogical;
} else {
$SQLFieldSelectionName .= "(" . $FieldInformation->main_table . "." . $FieldInformation->main_field . " " . $FieldSelectionElement["operation_comparison"] . " '" . $FieldSelectionElement["operation_value"] . "') " . $OperationLogical;
}
}
$SQLFieldSelectionName = substr($SQLFieldSelectionName, 0, strlen($SQLFieldSelectionName) - strlen($OperationLogical));
$SQLFieldSelectionName .= ", true, false))";
$SQLFieldSelectionArray[] = $SQLFieldSelectionName;
}
if ($FilterObject->option_history) {
if ($FieldInformation->field_alias) {
$FieldInformationArray[$FieldCounter]["table_name"] = $FieldInformation->main_table . "_" . $FieldInformation->field_alias;
} else {
$FieldInformationArray[$FieldCounter]["table_name"] = $FieldInformation->main_table;
}
$FieldInformationArray[$FieldCounter]["field_name"] = $FieldInformation->main_field;
$FieldInformationArray[$FieldCounter]["option_identifier"] = $FieldInformation->option_identifier;
$FieldInformationArray[$FieldCounter]["option_mandatory"] = $FieldInformation->option_mandatory;
$FieldInformationArray[$FieldCounter]["option_insert"] = $FieldInformation->option_insert;
$FieldInformationArray[$FieldCounter]["field_id"] = $FieldInformation->field_id;
}
if ($FieldInformation->option_identifier && $FilterObject->option_onetime) {
$IdentifierCounter = count($IdentifierFieldArray);
$IdentifierFieldArray[$IdentifierCounter]["field_id"] = $FieldInformation->field_id;
if ($FieldInformation->field_alias) {
$IdentifierFieldArray[$IdentifierCounter]["field_name"] = $FieldInformation->field_alias . "_" . $FieldInformation->main_table . "." . $FieldInformation->main_field;
} else {
$IdentifierFieldArray[$IdentifierCounter]["field_name"] = $FieldInformation->main_table . "." . $FieldInformation->main_field;
}
}
} else {
// $SQLTable = $FieldInformation->main_table;
if (($FieldInformation->field_type == FIELDTYPE_DATABASE)) {
if ($FieldInformation->field_alias) {
$SQLFieldArray[] = $FieldInformation->language_table . "_" . $FieldInformation->field_alias . "." . $FieldInformation->language_field . " AS '" . $FieldInformation->field_csv_caption . "'";
} else {
$SQLFieldArray[] = $FieldInformation->language_table . "." . $FieldInformation->language_field . " AS '" . $FieldInformation->field_csv_caption . "'";
}
} else {
$SQLFieldSelectionName = "(IF(";
foreach ($FieldInformation->selection_array as $FieldSelectionKey => $FieldSelectionElement) {
$OperationLogical = $FieldInformation->selection_array[$FieldSelectionKey + 1]["operation_logical"] . " ";
if ($FieldInformation->field_alias) {
$SQLFieldSelectionName .= "(" . $FieldInformation->language_table . "_" . $FieldInformation->field_alias . "." . $FieldInformation->language_field . " " . $FieldSelectionElement["operation_comparison"] . " '" . $FieldSelectionElement["operation_value"] . "') " . $OperationLogical;
} else {
$SQLFieldSelectionName .= "(" . $FieldInformation->language_table . "." . $FieldInformation->language_field . " " . $FieldSelectionElement["operation_comparison"] . " '" . $FieldSelectionElement["operation_value"] . "') " . $OperationLogical;
}
}
$SQLFieldSelectionName = substr($SQLFieldSelectionName, 0, strlen($SQLFieldSelectionName) - strlen($OperationLogical));
$SQLFieldSelectionName .= ", true, false))";
$SQLFieldSelectionArray[] = $SQLFieldSelectionName;
}
if ($FilterObject->option_history) {
if ($FieldInformation->field_alias) {
$FieldInformationArray[$FieldCounter]["table_name"] = $FieldInformation->language_table . "_" . $FieldInformation->field_alias;
} else {
$FieldInformationArray[$FieldCounter]["table_name"] = $FieldInformation->language_table;
}
$FieldInformationArray[$FieldCounter]["field_name"] = $FieldInformation->language_field;
$FieldInformationArray[$FieldCounter]["option_identifier"] = $FieldInformation->option_identifier;
$FieldInformationArray[$FieldCounter]["option_mandatory"] = $FieldInformation->option_mandatory;
$FieldInformationArray[$FieldCounter]["option_insert"] = $FieldInformation->option_insert;
$FieldInformationArray[$FieldCounter]["field_id"] = $FieldInformation->field_id;
}
if ($FieldInformation->option_identifier && $FilterObject->option_onetime) {
$IdentifierCounter = count($IdentifierFieldArray);
$IdentifierFieldArray[$IdentifierCounter]["field_id"] = $FieldInformation->field_id;
if ($FieldInformation->field_alias) {
$IdentifierFieldArray[$IdentifierCounter]["field_name"] = $FieldInformation->main_table . "_" . $FieldInformation->field_alias . "." . $FieldInformation->main_field;
} else {
$IdentifierFieldArray[$IdentifierCounter]["field_name"] = $FieldInformation->main_table . "." . $FieldInformation->main_field;
}
}
unset($SQLJoinElement);
$SQLJoinElement["target_table"] = $FieldInformation->language_table;
$SQLJoinElement["field_alias"] = $FieldInformation->field_alias;
$SQLJoinElement["field_array"][0]["main_table"] = $FieldInformation->main_table;
$SQLJoinElement["field_array"][0]["main_field"] = $FieldInformation->main_idfield;
$SQLJoinElement["field_array"][0]["target_table"] = $FieldInformation->language_table;
$SQLJoinElement["field_array"][0]["target_field"] = $FieldInformation->language_idfield;
$SQLJoinElement["field_array"][1]["main_table"] = "";
$SQLJoinElement["field_array"][1]["main_field"] = $LanguageID;
$SQLJoinElement["field_array"][1]["target_table"] = $FieldInformation->language_table;
$SQLJoinElement["field_array"][1]["target_field"] = $FieldInformation->language_languagefield;
foreach ($SQLJoinArray as $SQLJoinArrayElement) {
if ($SQLJoinArrayElement == $SQLJoinElement) {
$FoundSQLJoinElement = true;
}
}
if (!$FoundSQLJoinElement) {
$SQLJoinArray[] = $SQLJoinElement;
}
$SQLLanguageWhereElement["language_table"] = $FieldInformation->language_table;
$SQLLanguageWhereElement["language_field"] = $FieldInformation->language_field;
$SQLLanguageWhereArray[] = $SQLLanguageWhereElement;
}
} elseif ($FieldInformation->relation_type == RELATIONTYPE_1N) {
if (!$FieldInformation->language_table) {
if (($FieldInformation->field_type == FIELDTYPE_DATABASE)) {
if ($FieldInformation->field_alias) {
$SQLFieldArray[] = $FieldInformation->target_table . "_" . $FieldInformation->field_alias . "." . $FieldInformation->target_field . " AS '" . $FieldInformation->field_csv_caption . "'";
} else {
$SQLFieldArray[] = $FieldInformation->target_table . "." . $FieldInformation->target_field . " AS '" . $FieldInformation->field_csv_caption . "'";
}
} else {
$SQLFieldSelectionName = "(IF(";
foreach ($FieldInformation->selection_array as $FieldSelectionKey => $FieldSelectionElement) {
$OperationLogical = $FieldInformation->selection_array[$FieldSelectionKey + 1]["operation_logical"] . " ";
if ($FieldInformation->field_alias) {
$SQLFieldSelectionName .= "(" . $FieldInformation->target_table . "_" . $FieldInformation->field_alias . "." . $FieldInformation->target_field . " " . $FieldSelectionElement["operation_comparison"] . " '" . $FieldSelectionElement["operation_value"] . "') " . $OperationLogical;
} else {
$SQLFieldSelectionName .= "(" . $FieldInformation->target_table . "." . $FieldInformation->target_field . " " . $FieldSelectionElement["operation_comparison"] . " '" . $FieldSelectionElement["operation_value"] . "') " . $OperationLogical;
}
}
$SQLFieldSelectionName = substr($SQLFieldSelectionName, 0, strlen($SQLFieldSelectionName) - strlen($OperationLogical));
$SQLFieldSelectionName .= ", true, false))";
$SQLFieldSelectionArray[] = $SQLFieldSelectionName;
}
if ($FilterObject->option_history) {
if ($FieldInformation->field_alias) {
$FieldInformationArray[$FieldCounter]["table_name"] = $FieldInformation->target_table . "_" . $FieldInformation->field_alias;
} else {
$FieldInformationArray[$FieldCounter]["table_name"] = $FieldInformation->target_table;
}
$FieldInformationArray[$FieldCounter]["field_name"] = $FieldInformation->target_field;
$FieldInformationArray[$FieldCounter]["option_identifier"] = $FieldInformation->option_identifier;
$FieldInformationArray[$FieldCounter]["option_mandatory"] = $FieldInformation->option_mandatory;
$FieldInformationArray[$FieldCounter]["option_insert"] = $FieldInformation->option_insert;
$FieldInformationArray[$FieldCounter]["field_id"] = $FieldInformation->field_id;
}
if ($FieldInformation->option_identifier && $FilterObject->option_onetime) {
$IdentifierCounter = count($IdentifierFieldArray);
$IdentifierFieldArray[$IdentifierCounter]["field_id"] = $FieldInformation->field_id;
if ($FieldInformation->field_alias) {
$IdentifierFieldArray[$IdentifierCounter]["field_name"] = $FieldInformation->target_table . "_" . $FieldInformation->field_alias . "." . $FieldInformation->target_field;
} else {
$IdentifierFieldArray[$IdentifierCounter]["field_name"] = $FieldInformation->target_table . "." . $FieldInformation->target_field;
}
}
unset($SQLJoinElement);
$SQLJoinElement["target_table"] = $FieldInformation->target_table;
$SQLJoinElement["field_alias"] = $FieldInformation->field_alias;
$SQLJoinElement["field_array"][0]["main_table"] = $FieldInformation->main_table;
$SQLJoinElement["field_array"][0]["main_field"] = $FieldInformation->main_field;
$SQLJoinElement["field_array"][0]["target_table"] = $FieldInformation->target_table;
$SQLJoinElement["field_array"][0]["target_field"] = $FieldInformation->target_idfield;
foreach ($SQLJoinArray as $SQLJoinArrayElement) {
if ($SQLJoinArrayElement == $SQLJoinElement) {
$FoundSQLJoinElement = true;
}
}
if (!$FoundSQLJoinElement) {
$SQLJoinArray[] = $SQLJoinElement;
}
} else {
// Felder
if (($FieldInformation->field_type == FIELDTYPE_DATABASE)) {
if ($FieldInformation->field_alias) {
$SQLFieldArray[] = $FieldInformation->language_table . "_" . $FieldInformation->field_alias . "." . $FieldInformation->language_field . " AS '" . $FieldInformation->field_csv_caption . "'";
} else {
$SQLFieldArray[] = $FieldInformation->language_table . "." . $FieldInformation->language_field . " AS '" . $FieldInformation->field_csv_caption . "'";
}
} else {
$SQLFieldSelectionName = "(IF(";
foreach ($FieldInformation->selection_array as $FieldSelectionKey => $FieldSelectionElement) {
$OperationLogical = $FieldInformation->selection_array[$FieldSelectionKey + 1]["operation_logical"] . " ";
if ($FieldInformation->field_alias) {
$SQLFieldSelectionName .= "(" . $FieldInformation->language_table . "_" . $FieldInformation->field_alias . "." . $FieldInformation->language_field . " " . $FieldSelectionElement["operation_comparison"] . " '" . $FieldSelectionElement["operation_value"] . "') " . $OperationLogical;
} else {
$SQLFieldSelectionName .= "(" . $FieldInformation->language_table . "." . $FieldInformation->language_field . " " . $FieldSelectionElement["operation_comparison"] . " '" . $FieldSelectionElement["operation_value"] . "') " . $OperationLogical;
}
}
$SQLFieldSelectionName = substr($SQLFieldSelectionName, 0, strlen($SQLFieldSelectionName) - strlen($OperationLogical));
$SQLFieldSelectionName .= ", true, false))";
$SQLFieldSelectionArray[] = $SQLFieldSelectionName;
}
if ($FilterObject->option_history) {
if ($FieldInformation->field_alias) {
$FieldInformationArray[$FieldCounter]["table_name"] = $FieldInformation->language_table . "_" . $FieldInformation->field_alias;
} else {
$FieldInformationArray[$FieldCounter]["table_name"] = $FieldInformation->language_table;
}
$FieldInformationArray[$FieldCounter]["field_name"] = $FieldInformation->language_field;
$FieldInformationArray[$FieldCounter]["option_identifier"] = $FieldInformation->option_identifier;
$FieldInformationArray[$FieldCounter]["option_mandatory"] = $FieldInformation->option_mandatory;
$FieldInformationArray[$FieldCounter]["option_insert"] = $FieldInformation->option_insert;
$FieldInformationArray[$FieldCounter]["field_id"] = $FieldInformation->field_id;
}
if ($FieldInformation->option_identifier && $FilterObject->option_onetime) {
$IdentifierCounter = count($IdentifierFieldArray);
$IdentifierFieldArray[$IdentifierCounter]["field_id"] = $FieldInformation->field_id;
if ($FieldInformation->field_alias) {
$IdentifierFieldArray[$IdentifierCounter]["field_name"] = $FieldInformation->target_table . "_" . $FieldInformation->field_alias. "." . $FieldInformation->target_field;
} else {
$IdentifierFieldArray[$IdentifierCounter]["field_name"] = $FieldInformation->target_table . "." . $FieldInformation->target_field;
}
}
unset($SQLJoinElement);
$SQLJoinElement["target_table"] = $FieldInformation->target_table;
$SQLJoinElement["field_alias"] = $FieldInformation->field_alias;
$SQLJoinElement["field_array"][0]["main_table"] = $FieldInformation->main_table;
$SQLJoinElement["field_array"][0]["main_field"] = $FieldInformation->main_field;
$SQLJoinElement["field_array"][0]["target_table"] = $FieldInformation->target_table;
$SQLJoinElement["field_array"][0]["target_field"] = $FieldInformation->target_idfield;
foreach ($SQLJoinArray as $SQLJoinArrayElement) {
if ($SQLJoinArrayElement == $SQLJoinElement) {
$FoundSQLJoinElement = true;
}
}
if (!$FoundSQLJoinElement) {
$SQLJoinArray[] = $SQLJoinElement;
}
unset($SQLJoinElement);
$SQLJoinElement["target_table"] = $FieldInformation->language_table;
$SQLJoinElement["field_alias"] = $FieldInformation->field_alias;
$SQLJoinElement["field_array"][0]["main_table"] = $FieldInformation->target_table;
$SQLJoinElement["field_array"][0]["main_field"] = $FieldInformation->target_idfield;
$SQLJoinElement["field_array"][0]["target_table"] = $FieldInformation->language_table;
$SQLJoinElement["field_array"][0]["target_field"] = $FieldInformation->language_idfield;
$SQLJoinElement["field_array"][1]["main_table"] = "";
$SQLJoinElement["field_array"][1]["main_field"] = $LanguageID;
$SQLJoinElement["field_array"][1]["target_table"] = $FieldInformation->language_table;
$SQLJoinElement["field_array"][1]["target_field"] = $FieldInformation->language_languagefield;
foreach ($SQLJoinArray as $SQLJoinArrayElement) {
if ($SQLJoinArrayElement == $SQLJoinElement) {
$FoundSQLJoinElement = true;
}
}
if (!$FoundSQLJoinElement) {
$SQLJoinArray[] = $SQLJoinElement;
}
$SQLLanguageWhereElement["language_table"] = $FieldInformation->language_table;
$SQLLanguageWhereElement["language_field"] = $FieldInformation->language_field;
$SQLLanguageWhereArray[] = $SQLLanguageWhereElement;
}
} elseif ($FieldInformation->relation_type == RELATIONTYPE_NM) {
if (!$FieldInformation->language_table) {
if (($FieldInformation->field_type == FIELDTYPE_DATABASE)) {
if ($FieldInformation->field_alias) {
$SQLFieldArray[] = $FieldInformation->target_table . "_" . $FieldInformation->field_alias . "." . $FieldInformation->target_field . " AS '" . $FieldInformation->field_csv_caption . "'";
} else {
$SQLFieldArray[] = $FieldInformation->target_table . "." . $FieldInformation->target_field . " AS '" . $FieldInformation->field_csv_caption . "'";
}
} else {
$SQLFieldSelectionName = "(IF(";
foreach ($FieldInformation->selection_array as $FieldSelectionKey => $FieldSelectionElement) {
$OperationLogical = $FieldInformation->selection_array[$FieldSelectionKey + 1]["operation_logical"] . " ";
if ($FieldInformation->field_alias) {
$SQLFieldSelectionName .= "(" . $FieldInformation->target_table . "_" . $FieldInformation->field_alias . "." . $FieldInformation->target_field . " " . $FieldSelectionElement["operation_comparison"] . " '" . $FieldSelectionElement["operation_value"] . "') " . $OperationLogical;
} else {
$SQLFieldSelectionName .= "(" . $FieldInformation->target_table . "." . $FieldInformation->target_field . " " . $FieldSelectionElement["operation_comparison"] . " '" . $FieldSelectionElement["operation_value"] . "') " . $OperationLogical;
}
}
$SQLFieldSelectionName = substr($SQLFieldSelectionName, 0, strlen($SQLFieldSelectionName) - strlen($OperationLogical));
$SQLFieldSelectionName .= ", true, false))";
$SQLFieldSelectionArray[] = $SQLFieldSelectionName;
}
if ($FilterObject->option_history) {
if ($FieldInformation->field_alias) {
$FieldInformationArray[$FieldCounter]["table_name"] = $FieldInformation->target_table . "_" . $FieldInformation->field_alias;
} else {
$FieldInformationArray[$FieldCounter]["table_name"] = $FieldInformation->target_table;
}
$FieldInformationArray[$FieldCounter]["field_name"] = $FieldInformation->target_field;
$FieldInformationArray[$FieldCounter]["option_identifier"] = $FieldInformation->option_identifier;
$FieldInformationArray[$FieldCounter]["option_mandatory"] = $FieldInformation->option_mandatory;
$FieldInformationArray[$FieldCounter]["option_insert"] = $FieldInformation->option_insert;
$FieldInformationArray[$FieldCounter]["field_id"] = $FieldInformation->field_id;
}
if ($FieldInformation->option_identifier && $FilterObject->option_onetime) {
$IdentifierCounter = count($IdentifierFieldArray);
$IdentifierFieldArray[$IdentifierCounter]["field_id"] = $FieldInformation->field_id;
if ($FieldInformation->field_alias) {
$IdentifierFieldArray[$IdentifierCounter]["field_name"] = $FieldInformation->target_table . "_" . $FieldInformation->field_alias . "." . $FieldInformation->target_field;
} else {
$IdentifierFieldArray[$IdentifierCounter]["field_name"] = $FieldInformation->target_table . "." . $FieldInformation->target_field;
}
}
unset($SQLJoinElement);
$SQLJoinElement["target_table"] = $FieldInformation->relation_table;
$SQLJoinElement["field_alias"] = $FieldInformation->field_alias;
$SQLJoinElement["field_array"][0]["main_table"] = $FieldInformation->main_table;
$SQLJoinElement["field_array"][0]["main_field"] = $FieldInformation->main_idfield;
$SQLJoinElement["field_array"][0]["target_table"] = $FieldInformation->relation_table;
$SQLJoinElement["field_array"][0]["target_field"] = $FieldInformation->relation_main_idfield;
foreach ($SQLJoinArray as $SQLJoinArrayElement) {
if ($SQLJoinArrayElement == $SQLJoinElement) {
$FoundSQLJoinElement = true;
}
}
if (!$FoundSQLJoinElement) {
$SQLJoinArray[] = $SQLJoinElement;
}
unset($SQLJoinElement);
$SQLJoinElement["target_table"] = $FieldInformation->target_table;
$SQLJoinElement["field_alias"] = $FieldInformation->field_alias;
$SQLJoinElement["field_array"][0]["main_table"] = $FieldInformation->relation_table;
$SQLJoinElement["field_array"][0]["main_field"] = $FieldInformation->relation_target_idfield;
$SQLJoinElement["field_array"][0]["target_table"] = $FieldInformation->target_table;
$SQLJoinElement["field_array"][0]["target_field"] = $FieldInformation->target_idfield;
foreach ($SQLJoinArray as $SQLJoinArrayElement) {
if ($SQLJoinArrayElement == $SQLJoinElement) {
$FoundSQLJoinElement = true;
}
}
if (!$FoundSQLJoinElement) {
$SQLJoinArray[] = $SQLJoinElement;
}
} else {
if (($FieldInformation->field_type == FIELDTYPE_DATABASE)) {
if ($FieldInformation->field_alias) {
$SQLFieldArray[] = $FieldInformation->language_table . "_" . $FieldInformation->field_alias . "." . $FieldInformation->language_field . " AS '" . $FieldInformation->field_csv_caption . "'";
} else {
$SQLFieldArray[] = $FieldInformation->language_table . "." . $FieldInformation->language_field . " AS '" . $FieldInformation->field_csv_caption . "'";
}
} else {
$SQLFieldSelectionName = "(IF(";
foreach ($FieldInformation->selection_array as $FieldSelectionKey => $FieldSelectionElement) {
$OperationLogical = $FieldInformation->selection_array[$FieldSelectionKey + 1]["operation_logical"] . " ";
if ($FieldInformation->field_alias) {
$SQLFieldSelectionName .= "(" . $FieldInformation->language_table . "_" . $FieldInformation->field_alias . "." . $FieldInformation->language_field . " " . $FieldSelectionElement["operation_comparison"] . " '" . $FieldSelectionElement["operation_value"] . "') " . $OperationLogical;
} else {
$SQLFieldSelectionName .= "(" . $FieldInformation->language_table . "." . $FieldInformation->language_field . " " . $FieldSelectionElement["operation_comparison"] . " '" . $FieldSelectionElement["operation_value"] . "') " . $OperationLogical;
}
}
$SQLFieldSelectionName = substr($SQLFieldSelectionName, 0, strlen($SQLFieldSelectionName) - strlen($OperationLogical));
$SQLFieldSelectionName .= ", true, false))";
$SQLFieldSelectionArray[] = $SQLFieldSelectionName;
}
if ($FilterObject->option_history) {
if ($FieldInformation->field_alias) {
$FieldInformationArray[$FieldCounter]["table_name"] = $FieldInformation->language_table . "_" . $FieldInformation->field_alias;
} else {
$FieldInformationArray[$FieldCounter]["table_name"] = $FieldInformation->language_table;
}
$FieldInformationArray[$FieldCounter]["field_name"] = $FieldInformation->language_field;
$FieldInformationArray[$FieldCounter]["option_identifier"] = $FieldInformation->option_identifier;
$FieldInformationArray[$FieldCounter]["option_mandatory"] = $FieldInformation->option_mandatory;
$FieldInformationArray[$FieldCounter]["option_insert"] = $FieldInformation->option_insert;
$FieldInformationArray[$FieldCounter]["field_id"] = $FieldInformation->field_id;
}
if ($FieldInformation->option_identifier && $FilterObject->option_onetime) {
$IdentifierCounter = count($IdentifierFieldArray);
$IdentifierFieldArray[$IdentifierCounter]["field_id"] = $FieldInformation->field_id;
if ($FieldInformation->field_alias) {
$IdentifierFieldArray[$IdentifierCounter]["field_name"] = $FieldInformation->target_table . "_" . $FieldInformation->field_alias . "." . $FieldInformation->target_field;
} else {
$IdentifierFieldArray[$IdentifierCounter]["field_name"] = $FieldInformation->target_table . "." . $FieldInformation->target_field;
}
}
unset($SQLJoinElement);
$SQLJoinElement["target_table"] = $FieldInformation->relation_table;
$SQLJoinElement["field_alias"] = $FieldInformation->field_alias;
$SQLJoinElement["field_array"][0]["main_table"] = $FieldInformation->main_table;
$SQLJoinElement["field_array"][0]["main_field"] = $FieldInformation->main_idfield;
$SQLJoinElement["field_array"][0]["target_table"] = $FieldInformation->relation_table;
$SQLJoinElement["field_array"][0]["target_field"] = $FieldInformation->relation_main_idfield;
foreach ($SQLJoinArray as $SQLJoinArrayElement) {
if ($SQLJoinArrayElement == $SQLJoinElement) {
$FoundSQLJoinElement = true;
}
}
if (!$FoundSQLJoinElement) {
$SQLJoinArray[] = $SQLJoinElement;
}
unset($SQLJoinElement);
$SQLJoinElement["target_table"] = $FieldInformation->target_table;
$SQLJoinElement["field_alias"] = $FieldInformation->field_alias;
$SQLJoinElement["field_array"][0]["main_table"] = $FieldInformation->relation_table;
$SQLJoinElement["field_array"][0]["main_field"] = $FieldInformation->relation_target_idfield;
$SQLJoinElement["field_array"][0]["target_table"] = $FieldInformation->target_table;
$SQLJoinElement["field_array"][0]["target_field"] = $FieldInformation->target_idfield;
foreach ($SQLJoinArray as $SQLJoinArrayElement) {
if ($SQLJoinArrayElement == $SQLJoinElement) {
$FoundSQLJoinElement = true;
}
}
if (!$FoundSQLJoinElement) {
$SQLJoinArray[] = $SQLJoinElement;
}
unset($SQLJoinElement);
$FoundSQLJoinElement = false;
$SQLJoinElement["target_table"] = $FieldInformation->language_table;
$SQLJoinElement["field_alias"] = $FieldInformation->field_alias;
$SQLJoinElement["field_array"][0]["main_table"] = $FieldInformation->target_table;
$SQLJoinElement["field_array"][0]["main_field"] = $FieldInformation->target_idfield;
$SQLJoinElement["field_array"][0]["target_table"] = $FieldInformation->language_table;
$SQLJoinElement["field_array"][0]["target_field"] = $FieldInformation->language_idfield;
$SQLJoinElement["field_array"][1]["main_table"] = "";
$SQLJoinElement["field_array"][1]["main_field"] = $LanguageID;
$SQLJoinElement["field_array"][1]["target_table"] = $FieldInformation->language_table;
$SQLJoinElement["field_array"][1]["target_field"] = $FieldInformation->language_languagefield;
foreach ($SQLJoinArray as $SQLJoinArrayElement) {
if ($SQLJoinArrayElement == $SQLJoinElement) {
$FoundSQLJoinElement = true;
}
}
if (!$FoundSQLJoinElement) {
$SQLJoinArray[] = $SQLJoinElement;
}
$SQLLanguageWhereElement["language_table"] = $FieldInformation->language_table;
$SQLLanguageWhereElement["language_field"] = $FieldInformation->language_field;
$SQLLanguageWhereArray[] = $SQLLanguageWhereElement;
}
}
}
$FieldCounter++;
}
// ********************************************************************************
// ** bereits exportierte Daten auschliessen
// ********************************************************************************
foreach ($IdentifierFieldArray as $IdentifierFieldKey => $IdentifierFieldElement) {
$SQLString = "SELECT ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_IDENTIFIER . ".value ";
$SQLString .= "FROM ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_IDENTIFIER . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_IDENTIFIER . ".filter_id = '" . $FilterID . "' AND ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY_IDENTIFIER . ".field_id = '" . $IdentifierFieldElement["field_id"] . "' ";
$MySQLQueryReference = mysql_query($SQLString);
unset($IdentifierValueArray);
$IdentifierValueArray = array();
while ($IdentifierRow = mysql_fetch_array($MySQLQueryReference)) {
$IdentifierValueArray[] = $IdentifierRow["value"];
}
$IdentifierValueArray = array_unique($IdentifierValueArray);
$IdentifierFieldArray[$IdentifierFieldKey]["field_value"] = implode(", ", $IdentifierValueArray);
}
// ********************************************************************************
// ** SQL Befehle aufbereiten
// ********************************************************************************
$ExportSQLString = "SELECT DISTINCT ";
$ExportSQLString .= implode(", ", $SQLFieldArray) . " ";
if (count($SQLFieldSelectionArray) > 0) {
$ExportSQLString .= ", ";
$ExportSQLString .= "IF(";
foreach ($SQLFieldSelectionArray as $SQLFieldSelectionElement) {
$ExportSQLString .= "(" . $SQLFieldSelectionElement . ") AND ";
}
$ExportSQLString = substr($ExportSQLString, 0, strlen($ExportSQLString) - 5);
$ExportSQLString .= ", true, false) AS selection_filter ";
}
$ExportSQLString .= "FROM ";
$ExportSQLString .= $SQLTable . " ";
// echo '';
// var_dump($SQLTable);
// echo '
';
//
// echo '';
// var_dump($SQLJoinArray);
// echo '
';
// echo '';
// var_dump($SQLLanguageWhereArray);
// echo '
';
foreach ($SQLJoinArray as $SQLJoinElement) {
if ($SQLJoinElement["field_alias"]) {
$ExportSQLString .= "LEFT JOIN " . $SQLJoinElement["target_table"] . " AS " . $SQLJoinElement["target_table"] . "_" . $SQLJoinElement["field_alias"] . " ON (";
} else {
$ExportSQLString .= "LEFT JOIN " . $SQLJoinElement["target_table"] . " ON (";
}
foreach ($SQLJoinElement["field_array"] as $FieldElement) {
$ExportSQLString .= "(";
// Hack für Merkmalkombinationen
if($FieldElement['main_table'] == TABLE_ARTIKEL && $FieldElement['main_field'] == 'id') {
$ExportSQLString .= 'IF(' . TABLE_ARTIKEL . '.merkmalkombinationparentid, ' . TABLE_ARTIKEL . '.merkmalkombinationparentid, ' . TABLE_ARTIKEL . '.id) = ';
$HackFiltermerkmalkombis = true;
} else {
if ($FieldElement["main_table"]) {
if ($SQLJoinElement["field_alias"] && ($FieldElement["main_table"] != $SQLTable)) {
$ExportSQLString .= $FieldElement["main_table"] . "_" . $SQLJoinElement["field_alias"] . "." . $FieldElement["main_field"] . " = ";
} else {
$ExportSQLString .= $FieldElement["main_table"] . "." . $FieldElement["main_field"] . " = ";
}
} else {
$ExportSQLString .= $FieldElement["main_field"] . " = ";
}
}
if ($FieldElement["target_table"]) {
if ($SQLJoinElement["field_alias"]) {
$ExportSQLString .= $FieldElement["target_table"] . "_" . $SQLJoinElement["field_alias"] . "." . $FieldElement["target_field"];
} else {
$ExportSQLString .= $FieldElement["target_table"] . "." . $FieldElement["target_field"];
}
} else {
$ExportSQLString .= $FieldElement["target_field"] . " = ";
}
$ExportSQLString .= ") AND ";
}
$ExportSQLString = substr($ExportSQLString, 0, strlen($ExportSQLString) - 5);
$ExportSQLString .= " ) ";
}
$ExportSQLString .= "WHERE ";
$ExportSQLString .= "(";
foreach ($IdentifierFieldArray as $IdentifierFieldElement) {
if ($IdentifierFieldElement["field_value"] != "") {
$TempIdentifierFieldArray = explode(',', $IdentifierFieldElement["field_value"]);
foreach ($TempIdentifierFieldArray as $TempIdentifierField) {
$ExportSQLString .= "(" . $IdentifierFieldElement["field_name"] . " != \"" . trim($TempIdentifierField) . "\") AND ";
}
// $ExportSQLString .= $IdentifierFieldElement["field_name"] . " NOT IN (" . $IdentifierFieldElement["field_value"] . ") AND ";
}
}
foreach ($SQLLanguageWhereArray as $SQLLanguageWhereElement) {
$ExportSQLString .= "(!ISNULL(" . $SQLLanguageWhereElement["language_table"] . "." . $SQLLanguageWhereElement["language_field"] . ")) AND ";
}
if($HackFiltermerkmalkombis) {
$ExportSQLString .= '(' . TABLE_ARTIKEL . '.merkmalkombination = \'0\') AND ';
}
$ExportSQLString .= " 1)";
// echo '$ExportSQLString: ' . $ExportSQLString . '
';
// exit;
// ********************************************************************************
// ** Daten auslesen, Wegschreiben und Report aufbereiten
// ********************************************************************************
$ReportStatus = array();
$Report = array();
unset($CSVRow);
unset($CSVFieldArray);
// Header
if ($FilterCSVHeadline) {
// echo '';
// var_dump($FilterObject->field_array);
// echo '
';
// exit;
foreach ($FilterObject->field_array as $FieldInformation) {
if (($FieldInformation->field_type == FIELDTYPE_DATABASE) || ($FieldInformation->field_type == FIELDTYPE_DEFAULT) || ($FieldInformation->field_type == FIELDTYPE_NULL)) {
if ($FilterEnclosure != " ") {
$CSVFieldArray[] = $FilterEnclosure . $FieldInformation->field_csv_caption . $FilterEnclosure;
} else {
$CSVFieldArray[] = $FieldInformation->field_csv_caption;
}
}
}
$CSVRow = implode($FilterDelimiter, $CSVFieldArray) . CRLF;
fwrite($FileHandler, $CSVRow);
}
// echo '$ExportSQLString: ' . $ExportSQLString . '
';
$MySQLQueryReference = mysql_query($ExportSQLString);
$RowNumber = 1;
while ($DataRow = mysql_fetch_array($MySQLQueryReference, MYSQL_ASSOC)) {
unset($CSVRow);
unset($CSVFieldArray);
unset($FieldArray);
$FieldCounter = 0;
$OptionMandatoryCheck = true;
$OptionMandatoryCheckFieldArray = array();
foreach ($DataRow as $DataRowKey => $DataRowValue) {
if ($DataRowKey == "selection_filter") {
continue;
}
if ($FilterObject->field_array[$FieldCounter]->option_mandatory && ($DataRowValue == '')) {
$OptionMandatoryCheck = false;
$OptionMandatoryCheckFieldArray[] = $DataRowKey;
}
// echo '$FilterObject->field_array[$FieldCounter]->regexp_search: ' . $FilterObject->field_array[$FieldCounter]->regexp_search . '
';
// echo '$FilterObject->field_array[$FieldCounter]->regexp_insert: ' . $FilterObject->field_array[$FieldCounter]->regexp_insert . '
';
if ($FilterObject->field_array[$FieldCounter]->regexp_search && $FilterObject->field_array[$FieldCounter]->regexp_insert) {
$FilterObject->field_array[$FieldCounter]->regexp_insert = str_replace('[SHOPURL]', URLPFAD, $FilterObject->field_array[$FieldCounter]->regexp_insert);
$DataRowValue = preg_replace("/" . $FilterObject->field_array[$FieldCounter]->regexp_search . "/", $FilterObject->field_array[$FieldCounter]->regexp_insert, $DataRowValue);
}
// echo '$DataRowValue: X' . $DataRowValue . 'X
';
if ($FilterObject->field_array[$FieldCounter]->option_striptags) {
$DataRowValue = strip_tags($DataRowValue);
}
if ($FilterObject->field_array[$FieldCounter]->option_trim) {
$DataRowValue = trim($DataRowValue);
}
if ($FilterObject->field_array[$FieldCounter]->option_striplinebreak) {
$DataRowValue = preg_replace("/\n\r/", "", $DataRowValue);
$DataRowValue = preg_replace("/\n/", "", $DataRowValue);
$DataRowValue = preg_replace("/\r/", "", $DataRowValue);
}
if ($FilterObject->field_array[$FieldCounter]->option_insert == OPTION_INSERT_MAPPING) {
$FoundMapping = false;
foreach ($FilterObject->field_array[$FieldCounter]->mapping_array as $MappingElement) {
if ($DataRowValue == $MappingElement['value_mapping']) {
$DataRowValue = $MappingElement['value_csv'];
$FoundMapping = true;
}
}
if (!$FoundMapping) {
$DataRowValue = $FilterObject->field_array[$FieldCounter]->mapping_default_value;
}
}
if ($FilterObject->field_array[$FieldCounter]->option_insert == OPTION_INSERT_DEFAULT) {
$DataRowValue = $FilterObject->field_array[$FieldCounter]->option_insert_default_value;
}
if ($FilterEnclosure != " ") {
$CSVFieldArray[] = $FilterEnclosure . $DataRowValue . $FilterEnclosure;
} else {
$CSVFieldArray[] = $DataRowValue;
}
if ($FilterObject->option_history) {
$FieldArray[$FieldInformationArray[$FieldCounter]["table_name"]][$FieldInformationArray[$FieldCounter]["field_name"]]["value_old"] = "";
$FieldArray[$FieldInformationArray[$FieldCounter]["table_name"]][$FieldInformationArray[$FieldCounter]["field_name"]]["value_new"] = $DataRowValue;
$FieldArray[$FieldInformationArray[$FieldCounter]["table_name"]][$FieldInformationArray[$FieldCounter]["field_name"]]["option_identifier"] = $FieldInformationArray[$FieldCounter]["option_identifier"];
$FieldArray[$FieldInformationArray[$FieldCounter]["table_name"]][$FieldInformationArray[$FieldCounter]["field_name"]]["option_insert_default_value"] = $FieldInformationArray[$FieldCounter]["option_insert_default_value"];
$FieldArray[$FieldInformationArray[$FieldCounter]["table_name"]][$FieldInformationArray[$FieldCounter]["field_name"]]["field_id"] = $FieldInformationArray[$FieldCounter]["field_id"];
}
$FieldCounter++;
}
$CSVRow = implode($FilterDelimiter, $CSVFieldArray) . CRLF;
if (($FieldSelection && ($DataRow["selection_filter"] == 0)) || !$OptionMandatoryCheck) {
} else {
fwrite($FileHandler, $CSVRow);
}
if ($FieldSelection && ($DataRow["selection_filter"] == 0)) {
$Report[$RowNumber]["message"] = $a_de_history_message_noexportselection;
$Report[$RowNumber]["message_extended"] = $a_de_history_message_noexportselection_extended;
$Report[$RowNumber]["field_array"] = $FieldArray;
$Report[$RowNumber]["import_status"] = IMPORT_STATUS_NOEXPORTSELECTION;
$ReportStatus[IMPORT_STATUS_NOEXPORTSELECTION]++;
} elseif (!$OptionMandatoryCheck) {
$Report[$RowNumber]["message"] = $a_de_history_message_noexportmandatory;
$Report[$RowNumber]["message_extended"] = sprintf($a_de_history_message_noexportmandatory_extended, implode(',', $OptionMandatoryCheckFieldArray));
$Report[$RowNumber]["field_array"] = $FieldArray;
$Report[$RowNumber]["import_status"] = IMPORT_STATUS_NOEXPORTSELECTION;
$ReportStatus[IMPORT_STATUS_NOEXPORTSELECTION]++;
} else {
$Report[$RowNumber]["message"] = $a_de_history_message_export;
$Report[$RowNumber]["message_extended"] = $a_de_history_message_export_extended;
$Report[$RowNumber]["field_array"] = $FieldArray;
$Report[$RowNumber]["import_status"] = IMPORT_STATUS_EXPORT;
$ReportStatus[IMPORT_STATUS_EXPORT]++;
}
$RowNumber++;
}
fclose($FileHandler);
// echo '';
// var_dump($FilterObject);
// echo '
';
$HistoryID = SaveImportHistory($Filename, $Filename, IMPORT_STATUS_EXPORT, $Report, $ReportStatus, $FilterID, DATAEXCHANGE_TYPE_EXPORT, $FilterObject->option_history);
if (IMPORT_DEBUG) { exit; }
// exit;
return $HistoryID;
}
function CheckSpecialFunctions($DataExchangeType, $FilterID) {
$SpecialFunctionDataArray = GetSpecialFunctionDataArray($DataExchangeType, $FilterID);
foreach ($SpecialFunctionDataArray as $SpecialFunctionData) {
if ($SpecialFunctionData['specialfunction_selected']) {
ExecuteSpecialFunctions($SpecialFunctionData['specialfunction_id']);
}
}
}
function ExecuteSpecialFunctions($SpecialfunctionID) {
switch ($SpecialfunctionID) {
case SPECIALFUNCTION_PRICECALCULATIONNET:
$SQLString = 'SELECT ';
$SQLString .= TABLE_MWST . '.id, ';
$SQLString .= TABLE_MWST . '.mwst ';
$SQLString .= 'FROM ';
$SQLString .= TABLE_MWST . ' ';
$MySQLQueryReference = mysql_query($SQLString);
while ($MwStRow = mysql_fetch_array($MySQLQueryReference, MYSQL_ASSOC)) {
$MwStDataArray[$MwStRow['id']] = $MwStRow['mwst'];
}
$SQLString = 'SELECT ';
$SQLString .= TABLE_ARTIKEL . '.id, ';
$SQLString .= TABLE_ARTIKEL . '.preis_netto, ';
$SQLString .= TABLE_ARTIKEL . '.mwst ';
$SQLString .= 'FROM ';
$SQLString .= TABLE_ARTIKEL . ' ';
$MySQLQueryReference = mysql_query($SQLString);
while ($ArtikelRow = mysql_fetch_array($MySQLQueryReference, MYSQL_ASSOC)) {
$SQLString = 'UPDATE ' . TABLE_ARTIKEL . ' SET ';
$SQLString .= TABLE_ARTIKEL . '.preis_brutto = \'' . round(($ArtikelRow['preis_netto'] / 100) * (100 + $MwStDataArray[$ArtikelRow['mwst']]), 2) . '\' ';
$SQLString .= 'WHERE ';
$SQLString .= TABLE_ARTIKEL . '.id = \'' . $ArtikelRow['id'] . '\' ';
$MySQLUpdateQueryReference = mysql_query($SQLString);
}
break;
case SPECIALFUNCTION_PRICECALCULATIONGROSS:
$SQLString = 'SELECT ';
$SQLString .= TABLE_MWST . '.id, ';
$SQLString .= TABLE_MWST . '.mwst ';
$SQLString .= 'FROM ';
$SQLString .= TABLE_MWST . ' ';
$MySQLQueryReference = mysql_query($SQLString);
while ($MwStRow = mysql_fetch_array($MySQLQueryReference, MYSQL_ASSOC)) {
$MwStDataArray[$MwStRow['id']] = $MwStRow['mwst'];
}
$SQLString = 'SELECT ';
$SQLString .= TABLE_ARTIKEL . '.id, ';
$SQLString .= TABLE_ARTIKEL . '.preis_brutto, ';
$SQLString .= TABLE_ARTIKEL . '.mwst ';
$SQLString .= 'FROM ';
$SQLString .= TABLE_ARTIKEL . ' ';
$MySQLQueryReference = mysql_query($SQLString);
while ($ArtikelRow = mysql_fetch_array($MySQLQueryReference, MYSQL_ASSOC)) {
$SQLString = 'UPDATE ' . TABLE_ARTIKEL . ' SET ';
$SQLString .= TABLE_ARTIKEL . '.preis_netto = \'' . round(($ArtikelRow['preis_brutto'] / (100 + $MwStDataArray[$ArtikelRow['mwst']])) * 100, 2) . '\' ';
$SQLString .= 'WHERE ';
$SQLString .= TABLE_ARTIKEL . '.id = \'' . $ArtikelRow['id'] . '\' ';
$MySQLUpdateQueryReference = mysql_query($SQLString);
}
break;
}
}
function GetFieldTableArray($FieldID) {
$FieldTableArray = array();
// Haupttabelle einlesen
$SQLString = "SELECT ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS_TABLES . ".field_table_id, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS_TABLES . ".field_table_type, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS_TABLES . ".table_name, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS_TABLES . ".field_name, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS_TABLES . ".keyfield_name, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS_TABLES . ".identifier, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS_TABLES . ".insert ";
$SQLString .= "FROM ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS_TABLES . " ";
$SQLString .= "WHERE ";
$SQLString .= "(";
$SQLString .= "(" . TABLE_ARTIKELIMPORT_FIELDS_TABLES . ".field_id = '" . $FieldID . "') AND ";
$SQLString .= "(" . TABLE_ARTIKELIMPORT_FIELDS_TABLES . ".field_table_parent = 0) AND ";
$SQLString .= " 1)";
// echo '$SQLString: ' . $SQLString . '
';
$FieldTableRow = mysql_fetch_array(mysql_query($SQLString), MYSQL_ASSOC);
if ($FieldTableRow) {
$FieldTableID = $FieldTableRow["field_table_id"];
$FieldTableLevel = 1;
$FieldTableArray[$FieldTableID]["field_table_id"] = $FieldTableRow["field_table_id"];
$FieldTableArray[$FieldTableID]["field_table_type"] = $FieldTableRow["field_table_type"];
$FieldTableArray[$FieldTableID]["table_name"] = $FieldTableRow["table_name"];
$FieldTableArray[$FieldTableID]["field_name"] = $FieldTableRow["field_name"];
$FieldTableArray[$FieldTableID]["keyfield_name"] = $FieldTableRow["keyfield_name"];
$FieldTableArray[$FieldTableID]["identifier"] = $FieldTableRow["identifier"];
$FieldTableArray[$FieldTableID]["insert"] = $FieldTableRow["insert"];
$FieldTableArray[$FieldTableID]["field_table_level"] = $FieldTableLevel;
// $FieldTableArray[$FieldTableID]["parent_table_name"] = "";
$FieldTableArray[$FieldTableID]["parent_table_id"] = 0;
// untergeordnete Tabellen einlesen
$FieldTableArray = GetFieldChildTableArray($FieldID, $FieldTableID, $FieldTableArray, $FieldTableLevel, $FieldTableRow["table_name"]);
}
return $FieldTableArray;
}
function GetFieldChildTableArray($FieldID, $ParentFieldTableID, $FieldTableArray, $FieldTableLevel, $ParentTableName) {
$FieldTableLevel++;
// untergeordnete Tabellen einlesen
$SQLString = "SELECT ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS_TABLES . ".field_table_id, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS_TABLES . ".field_table_type, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS_TABLES . ".table_name, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS_TABLES . ".field_name, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS_TABLES . ".keyfield_name, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS_TABLES . ".identifier, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS_TABLES . ".insert ";
$SQLString .= "FROM ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS_TABLES . " ";
$SQLString .= "WHERE ";
$SQLString .= "(";
$SQLString .= "(" . TABLE_ARTIKELIMPORT_FIELDS_TABLES . ".field_id = '" . $FieldID . "') AND ";
$SQLString .= "(" . TABLE_ARTIKELIMPORT_FIELDS_TABLES . ".field_table_parent ='" . $ParentFieldTableID . "') AND ";
$SQLString .= " 1)";
$MySQLQueryReference = mysql_query($SQLString);
// echo '$SQLString: ' . $SQLString . '
';
while ($FieldTableRow = mysql_fetch_array($MySQLQueryReference, MYSQL_ASSOC)) {
$FieldTableID = $FieldTableRow["field_table_id"];
$FieldTableArray[$FieldTableID]["field_table_id"] = $FieldTableRow["field_table_id"];
$FieldTableArray[$FieldTableID]["field_table_type"] = $FieldTableRow["field_table_type"];
$FieldTableArray[$FieldTableID]["table_name"] = $FieldTableRow["table_name"];
$FieldTableArray[$FieldTableID]["field_name"] = $FieldTableRow["field_name"];
$FieldTableArray[$FieldTableID]["keyfield_name"] = $FieldTableRow["keyfield_name"];
$FieldTableArray[$FieldTableID]["identifier"] = $FieldTableRow["identifier"];
$FieldTableArray[$FieldTableID]["insert"] = $FieldTableRow["insert"];
$FieldTableArray[$FieldTableID]["field_table_level"] = $FieldTableLevel;
// $FieldTableArray[$FieldTableID]["parent_table_name"] = $ParentTableName;
$FieldTableArray[$FieldTableID]["parent_table_id"] = $ParentFieldTableID;
// $FieldTableArray[$ParentFieldTableID]["child_table_name_array"][$FieldTableRow["field_table_type"]][] = $FieldTableRow["table_name"];
$FieldTableArray[$ParentFieldTableID]["child_table_id_array"][$FieldTableRow["field_table_type"]][] = $FieldTableRow["field_table_id"];
$FieldTableArray = GetFieldChildTableArray($FieldID, $FieldTableID, $FieldTableArray, $FieldTableLevel, $FieldTableRow["table_name"]);
}
return $FieldTableArray;
}
function DeleteHistory($HistoryID) {
$HistoryObject = GetHistoryDetail($HistoryID);
if (file_exists(IMPORTDATAPFAD . $HistoryObject->filename)) {
unlink(IMPORTDATAPFAD . $HistoryObject->filename);
}
$SQLString = "DELETE FROM " . TABLE_DATAEXCHANGE_HISTORY . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGE_HISTORY . ".history_id = '" . $HistoryID . "' ";
$MySQLQuery = mysql_query($SQLString);
$SQLString = "DELETE FROM " . TABLE_DATAEXCHANGET_HISTORY_STATUS . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGET_HISTORY_STATUS . ".history_id = '" . $HistoryID . "' ";
$MySQLQuery = mysql_query($SQLString);
$SQLString = "DELETE FROM " . TABLE_DATAEXCHANGET_HISTORY_ROWS . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGET_HISTORY_ROWS . ".history_id = '" . $HistoryID . "' ";
$MySQLQuery = mysql_query($SQLString);
$SQLString = "DELETE FROM " . TABLE_DATAEXCHANGET_HISTORY_TABLES . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGET_HISTORY_TABLES . ".history_id = '" . $HistoryID . "' ";
$MySQLQuery = mysql_query($SQLString);
$SQLString = "DELETE FROM " . TABLE_DATAEXCHANGET_HISTORY_FIELDS . " ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_DATAEXCHANGET_HISTORY_FIELDS . ".history_id = '" . $HistoryID . "' ";
$MySQLQuery = mysql_query($SQLString);
}
function GetTableName($TableName) {
global $TableFieldTranslationDataArray;
global $a_de_field_default;
if ($TableName == $a_de_field_default) {
return $TableName;
}
$ArtikelimportSettings = GetDataexchangeSettings();
$TableName = substr($TableName, strlen(TABLE_PREFIX));
if (isset($TableFieldTranslationDataArray[$TableName])) {
if ($ArtikelimportSettings->show_db_details) {
return $TableFieldTranslationDataArray[$TableName]["table_caption"] . " (" . $TableName . ")";
} else {
return $TableFieldTranslationDataArray[$TableName]["table_caption"];
}
} else {
return $TableName;
}
}
$TableInsertArray = array();
$TableInsertIDArray = array();
function CheckTableInsert($SQLTable, $SQLField, $SQLWhere, $SQLIDField, $Try) {
global $TableInsertArray;
$SQLString = PrepareSQLSelectString($SQLTable, $SQLField, $SQLWhere);
if ($Try) {
if ($TableInsertArray[$SQLString]) {
$CheckTableInsertResultArray["found_rows"] = 1;
$CheckTableInsertResultArray["insert_id"] = $TableInsertArray[$SQLString];
return $CheckTableInsertResultArray;
}
}
if (IMPORT_DEBUG) { echo '$SQLString (019): ' . $SQLString . '
'; }
$MySQLQueryReference = mysql_query($SQLString);
$CheckTableInsertResultArray["found_rows"] = mysql_num_rows($MySQLQueryReference);
if ($CheckTableInsertResultArray["found_rows"] > 1) {
$CheckTableInsertResultArray["error"] = true;
$CheckTableInsertResultArray["error_message"] = "Es wurden zu viele Einträge in der Zieltabelle gefunden.";
}
$DBValueArray = mysql_fetch_array(mysql_query($SQLString), MYSQL_ASSOC);
$CheckTableInsertResultArray["insert_id"] = $DBValueArray[$SQLIDField];
return $CheckTableInsertResultArray;
}
function TableInsert($SQLTable, $SQLField, $SQLWhere, $SQLIDField, $Try) {
global $TableInsertArray;
if ($Try) {
$SQLString = PrepareSQLSelectString($SQLTable, $SQLField, $SQLWhere);
if (!$TableInsertArray[$SQLString]) {
$SQLMaxString = "SELECT ";
$SQLMaxString .= "(MAX(" . $SQLTable . "." . $SQLIDField . ")) AS max_id ";
$SQLMaxString .= "FROM ";
$SQLMaxString .= $SQLTable . " ";
if (IMPORT_DEBUG) { echo '$SQLMaxString: ' . $SQLMaxString . '
'; }
$InsertObject = mysql_fetch_object(mysql_query($SQLMaxString));
$TableInsertArray[$SQLString] = ($InsertObject->max_id + 1);
$TableInsertResultArray["insert_id"] = ($InsertObject->max_id + 1);
$SQLString = "INSERT INTO " . $SQLTable . " SET ";
$SQLString .= implode(", ", $SQLWhere);
if (IMPORT_DEBUG) { echo '$SQLString: ' . $SQLString . '
'; }
} else {
$TableInsertResultArray["insert_id"] = $TableInsertArray[$SQLString];
}
} else {
$SQLString = "INSERT INTO " . $SQLTable . " SET ";
$SQLString .= implode(", ", $SQLWhere);
if (IMPORT_DEBUG) { echo '$SQLString: ' . $SQLString . '
'; }
if (IMPORT_ACTIVE) { $MySQLQueryReference = mysql_query($SQLString); }
$TableInsertResultArray["insert_id"] = mysql_insert_id();
}
return $TableInsertResultArray;
}
function PrepareSQLSelectString($SQLTable, $SQLField, $SQLWhere) {
if (is_array($SQLTable)) {
$SQLTable = array_unique($SQLTable);
}
if (is_array($SQLField)) {
$SQLField = array_unique($SQLField);
}
if (is_array($SQLWhere)) {
$SQLWhere = array_unique($SQLWhere);
}
$SQLString = "SELECT ";
if (is_array($SQLField)) {
$SQLString .= implode(", ", $SQLField) . " ";
} else {
$SQLString .= $SQLField . " ";
}
$SQLString .= "FROM ";
if (is_array($SQLTable)) {
$SQLString .= implode(", ", $SQLTable) . " ";
} else {
$SQLString .= $SQLTable . " ";
}
$SQLString .= "WHERE ";
if (is_array($SQLWhere)) {
$SQLString .= implode(" AND ", $SQLWhere) . " ";
} else {
$SQLString .= $SQLWhere . " ";
}
return $SQLString;
}
/**
* Aktualsierung eines Datensatzes in der Datenbank
*
*
*/
function UpdateCSVRow($CSVRow, $FilterObject, $LanguageID, $MainTableID, $Try, $KategorieArray) {
global $TableInsertArray;
global $TableInsertIDArray;
$MainTableType = "update";
$ImportStatus = IMPORT_STATUS_UPDATE;
// Datensatz nicht hinzufuegen
if (!$FilterObject->option_insert && (!$MainTableID && !$MainTableIDArray)) {
$UpdateCSVRowReturnArray["import_type"] = "noinsert";
$UpdateCSVRowReturnArray["import_status"] = IMPORT_STATUS_NOINSERT;
return $UpdateCSVRowReturnArray;
}
// Datensatz zuerst hinzufuegen
if (!$MainTableID) {
$MainTableType = "insert";
$ImportStatus = IMPORT_STATUS_INSERT;
$SQLMainInsertFieldArray = array();
$SQLMainInsertTabel = "";
$SQLMainInsertIDField = "";
foreach ($CSVRow as $ColumnNumber => $CSVValue) {
$FieldObject = $FilterObject->field_array[$ColumnNumber];
if ($FieldObject->option_identifier && ($FieldObject->relation_type == RELATIONTYPE_11)) {
$SQLMainInsertIDField = $FieldObject->main_idfield;
$SQLMainInsertTabel = $FieldObject->main_table;
$SQLMainInsertFieldArray[] = $FieldObject->main_table . "." . $FieldObject->main_field . " = '" . $CSVValue . "'";
if (!$Try) {
$ReportMainFieldArray[$FieldObject->main_table][$FieldObject->main_field]["import_type"] = 'import';
$ReportMainFieldArray[$FieldObject->main_table][$FieldObject->main_field]["value_old"] = '';
$ReportMainFieldArray[$FieldObject->main_table][$FieldObject->main_field]["value_new"] = $CSVValue;
}
}
}
$SQLString = "INSERT INTO " . $SQLMainInsertTabel . " SET ";
$SQLString .= implode(", ", $SQLMainInsertFieldArray);
if ($Try) {
if ($TableInsertArray[$SQLString]) {
$MainTableID = $TableInsertArray[$SQLString];
} else {
if ($TableInsertIDArray[$SQLMainInsertTabel]) {
$TableInsertIDArray[$SQLMainInsertTabel]++;
$MainTableID = $TableInsertIDArray[$SQLMainInsertTabel];
} else {
$SQLMaxString = "SELECT ";
$SQLMaxString .= "MAX(" . $SQLMainInsertTabel . "." . $SQLMainInsertIDField . ") AS max_id ";
$SQLMaxString .= "FROM ";
$SQLMaxString .= $SQLMainInsertTabel . " ";
if (IMPORT_DEBUG) { echo '$SQLMaxString (002): ' . $SQLMaxString . '
'; }
$InsertObject = mysql_fetch_object(mysql_query($SQLMaxString));
$TableInsertArray[$SQLString] = ($InsertObject->max_id + 1);
$TableInsertIDArray[$SQLMainInsertTabel] = ($InsertObject->max_id + 1);
$MainTableID = $TableInsertIDArray[$SQLMainInsertTabel];
if (IMPORT_DEBUG) { echo '$SQLString (003): ' . $SQLString . '
'; }
}
}
} else {
if (IMPORT_DEBUG) { echo '$SQLString (004): ' . $SQLString . '
'; }
if (IMPORT_ACTIVE) { $MySQLQueryReference = mysql_query($SQLString); }
$MainTableID = mysql_insert_id();
$NewMainTableID = $MainTableID;
}
}
foreach ($CSVRow as $ColumnNumber => $CSVValue) {
$FieldObject = $FilterObject->field_array[$ColumnNumber];
if ($FieldObject->option_identifier && ($FieldObject->relation_type == RELATIONTYPE_11)) {
continue;
}
if ($FieldObject->regexp_search) {
$CSVValue = preg_replace("/" . $FieldObject->regexp_search . "/", $FieldObject->regexp_insert, $CSVValue);
}
if ($FieldObject->option_striptags) {
$CSVValue = strip_tags($CSVValue);
}
if ($FieldObject->option_trim) {
$CSVValue = trim($CSVValue);
}
if ($FieldObject->option_striplinebreak) {
$CSVValue = preg_replace("/\n\r/", "", $CSVValue);
$CSVValue = preg_replace("/\n/", "", $CSVValue);
$CSVValue = preg_replace("/\r/", "", $CSVValue);
}
// Mapping aufbauen
if ($FieldObject->option_insert == OPTION_INSERT_MAPPING) {
$FoundMapping = false;
// Eintrag in der Mappingtabelle vorhanden
foreach ($FieldObject->mapping_array as $MappingData) {
if ($MappingData["value_csv"] == $CSVValue) {
$CSVValue = $MappingData["value_mapping"];
$FoundMapping = true;
}
}
// Standardwert falls kein Eintrag gefunden
if (!$FoundMapping) {
$CSVValue = $FieldObject->mapping_default_value;
}
}
// Standardwert
if ($FieldObject->option_insert == OPTION_INSERT_DEFAULT) {
if (!$FieldObject->option_language) {
$SQLString = PrepareSQLSelectString($FieldObject->target_table, $FieldObject->target_table . "." . $FieldObject->target_idfield, $FieldObject->target_table . "." . $FieldObject->target_field . " = '" . $CSVValue . "'");
if (IMPORT_DEBUG) { echo '$SQLString (022): ' . $SQLString . '
'; }
$MySQLQueryReference = mysql_query($SQLString);
if (!mysql_num_rows($MySQLQueryReference)) {
$CSVValue = $FieldObject->option_insert_default_value;
}
} else {
$SQLString = PrepareSQLSelectString($FieldObject->language_table, $FieldObject->language_table . "." . $FieldObject->language_idfield, $FieldObject->language_table . "." . $FieldObject->language_field . " = '" . $CSVValue . "'");
if (IMPORT_DEBUG) { echo '$SQLString (023): ' . $SQLString . '
'; }
$MySQLQueryReference = mysql_query($SQLString);
if (!mysql_num_rows($MySQLQueryReference)) {
$CSVValue = $FieldObject->option_insert_default_value;
}
}
}
// Daten fuer das Update aufbereiten
switch ($FieldObject->relation_type) {
case RELATIONTYPE_11:
if (!$FieldObject->option_language) {
$UpdateArray[$FieldObject->relation_type]["table_array"][$FieldObject->main_table]["main_idfield"] = $FieldObject->main_idfield;
$UpdateArray[$FieldObject->relation_type]["table_array"][$FieldObject->main_table]["main_field"][$FieldObject->main_field] = $CSVValue;
} else {
$UpdateArray[$FieldObject->relation_type]["language_table_array"][$FieldObject->language_table]["language_field"][$FieldObject->language_field] = $CSVValue;
$UpdateArray[$FieldObject->relation_type]["language_table_array"][$FieldObject->language_table]["language_idfield"][$FieldObject->language_idfield] = $MainTableID;
// $UpdateArray[$FieldObject->relation_type]["language_table_array"][$FieldObject->language_table]["language_languagefield"][$FieldObject->language_languagefield] = $LanguageID;
}
break;
case RELATIONTYPE_1N:
if (!$FieldObject->option_language) {
$UpdateArray[$FieldObject->relation_type]["target_table_array"][$FieldObject->target_table]["target_idfield"] = $FieldObject->target_idfield;
$UpdateArray[$FieldObject->relation_type]["target_table_array"][$FieldObject->target_table]["target_field"][$FieldObject->target_field] = $CSVValue;
$UpdateArray[$FieldObject->relation_type]["target_table_array"][$FieldObject->target_table]["main_table"] = $FieldObject->main_table;
$UpdateArray[$FieldObject->relation_type]["target_table_array"][$FieldObject->target_table]["main_field"] = $FieldObject->main_field;
} else {
$UpdateArray[$FieldObject->relation_type]["language_table_array"][$FieldObject->language_table]["language_field"][$FieldObject->language_field] = $CSVValue;
$UpdateArray[$FieldObject->relation_type]["language_table_array"][$FieldObject->language_table]["language_idfield"] = $FieldObject->language_idfield;
$UpdateArray[$FieldObject->relation_type]["language_table_array"][$FieldObject->language_table]["language_languagefield"][$FieldObject->language_languagefield] = $LanguageID;
$UpdateArray[$FieldObject->relation_type]["language_table_array"][$FieldObject->language_table]["target_table"] = $FieldObject->target_table;
$UpdateArray[$FieldObject->relation_type]["language_table_array"][$FieldObject->language_table]["target_idfield"] = $FieldObject->target_idfield;
$UpdateArray[$FieldObject->relation_type]["target_table_array"][$FieldObject->target_table]["target_idfield"] = $FieldObject->target_idfield;
$UpdateArray[$FieldObject->relation_type]["target_table_array"][$FieldObject->target_table]["main_table"] = $FieldObject->main_table;
$UpdateArray[$FieldObject->relation_type]["target_table_array"][$FieldObject->target_table]["main_field"] = $FieldObject->main_field;
}
break;
case RELATIONTYPE_NM:
if (!$FieldObject->option_language) {
$UpdateArray[$FieldObject->relation_type]["relation_table_array"][$FieldObject->relation_table]["relation_table"] = $FieldObject->relation_table;
$UpdateArray[$FieldObject->relation_type]["relation_table_array"][$FieldObject->relation_table]["relation_main_idfield"] = $FieldObject->relation_main_idfield;
$UpdateArray[$FieldObject->relation_type]["relation_table_array"][$FieldObject->relation_table]["relation_target_idfield"] = $FieldObject->relation_target_idfield;
$UpdateArray[$FieldObject->relation_type]["relation_table_array"][$FieldObject->relation_table]["target_table"] = $FieldObject->target_table;
$UpdateArray[$FieldObject->relation_type]["relation_table_array"][$FieldObject->relation_table]["target_idfield"] = $FieldObject->target_idfield;
$UpdateArray[$FieldObject->relation_type]["relation_table_array"][$FieldObject->relation_table]["main_table"] = $FieldObject->main_table;
$UpdateArray[$FieldObject->relation_type]["relation_table_array"][$FieldObject->relation_table]["main_field"] = $FieldObject->main_idfield;
$UpdateArray[$FieldObject->relation_type]["target_table_array"][$FieldObject->target_table]["target_idfield"] = $FieldObject->target_idfield;
$UpdateArray[$FieldObject->relation_type]["target_table_array"][$FieldObject->target_table]["target_field"][$FieldObject->target_field] = $CSVValue;
$UpdateArray[$FieldObject->relation_type]["target_table_array"][$FieldObject->target_table]["main_table"] = $FieldObject->main_table;
$UpdateArray[$FieldObject->relation_type]["target_table_array"][$FieldObject->target_table]["main_field"] = $FieldObject->main_field;
$UpdateArray[$FieldObject->relation_type]["target_table_array"][$FieldObject->target_table]["relation_table"] = $FieldObject->relation_table;
} else {
$UpdateArray[$FieldObject->relation_type]["relation_table_array"][$FieldObject->relation_table]["relation_table"] = $FieldObject->relation_table;
$UpdateArray[$FieldObject->relation_type]["relation_table_array"][$FieldObject->relation_table]["relation_main_idfield"] = $FieldObject->relation_main_idfield;
$UpdateArray[$FieldObject->relation_type]["relation_table_array"][$FieldObject->relation_table]["relation_target_idfield"] = $FieldObject->relation_target_idfield;
$UpdateArray[$FieldObject->relation_type]["relation_table_array"][$FieldObject->relation_table]["target_table"] = $FieldObject->target_table;
$UpdateArray[$FieldObject->relation_type]["relation_table_array"][$FieldObject->relation_table]["target_idfield"] = $FieldObject->target_idfield;
$UpdateArray[$FieldObject->relation_type]["relation_table_array"][$FieldObject->relation_table]["main_table"] = $FieldObject->main_table;
$UpdateArray[$FieldObject->relation_type]["relation_table_array"][$FieldObject->relation_table]["main_field"] = $FieldObject->main_idfield;
$UpdateArray[$FieldObject->relation_type]["language_table_array"][$FieldObject->language_table]["language_field"][$FieldObject->language_field] = $CSVValue;
$UpdateArray[$FieldObject->relation_type]["language_table_array"][$FieldObject->language_table]["language_idfield"] = $FieldObject->language_idfield;
// $UpdateArray[$FieldObject->relation_type]["language_table_array"][$FieldObject->language_table]["language_languagefield"][$FieldObject->language_languagefield] = $LanguageID;
$UpdateArray[$FieldObject->relation_type]["language_table_array"][$FieldObject->language_table]["target_table"] = $FieldObject->target_table;
$UpdateArray[$FieldObject->relation_type]["language_table_array"][$FieldObject->language_table]["target_idfield"] = $FieldObject->target_idfield;
$UpdateArray[$FieldObject->relation_type]["language_table_array"][$FieldObject->language_table]["option_insert_default_value"] = $FieldObject->option_insert_default_value;
$UpdateArray[$FieldObject->relation_type]["language_table_array"][$FieldObject->language_table]["option_insert"] = $FieldObject->option_insert;
$UpdateArray[$FieldObject->relation_type]["target_table_array"][$FieldObject->target_table]["target_idfield"] = $FieldObject->target_idfield;
$UpdateArray[$FieldObject->relation_type]["target_table_array"][$FieldObject->target_table]["main_table"] = $FieldObject->main_table;
$UpdateArray[$FieldObject->relation_type]["target_table_array"][$FieldObject->target_table]["main_field"] = $FieldObject->main_field;
$UpdateArray[$FieldObject->relation_type]["target_table_array"][$FieldObject->target_table]["relation_table"] = $FieldObject->relation_table;
}
break;
}
// ** Haupttabelle
// 1:1 normales Update
// 1:n Update mit Verweis auf sp�tere aufbereiten der Zieltabelle
// n:m kein Update
// ** Haupttabelle mit Sprache
// 1:1 Sprachtabelle - kein Update auf die Hauptabelle, nur auf die
// Sprachtabelle mit ID aus der Hauptabelle und der LanguageID
// 1:n Update mit Verweis auf sp�tere aufbereiten der Zieltabelle
// n:m kein Update
// ** Zieltabelle
// 1:n Daten pr�fen, ggf. Einf�gen (Standardwert, Uebersetzungstabelle), ID
// Ermitteln f�r das Upadte aus der Haupttabelle
// n:m Daten in der Sprachtabelle pr�fen, ggf. Einf�gen (Standardwert
// , Uebersetzungstabelle), ID ermitteln f�r das Upadte in die Relationstabelle
// ** Zieltabelle mit Sprache
// 1:n Daten pr�fen, ggf. Einf�gen (Standardwert, Uebersetzungstabelle), ID
// Ermitteln f�r das Upadte aus der Haupttabelle
// 1:n Daten pr�fen, ggf. Einf�gen (Standardwert, Uebersetzungstabelle), ID
// Ermitteln f�r das Upadte in die Relationstabelle
}
if ($MainTableType == "insert") {
$TabledefaultFieldDataArray = GetTabledefaultFieldDataArray();
for ($UpdateRelationType = 3; $UpdateRelationType > 0; $UpdateRelationType--) {
$UpdateData = $UpdateArray[$UpdateRelationType];
if ($UpdateData['target_table_array']) {
foreach ($UpdateData['target_table_array'] as $TableName => $TableElement) {
if ($TabledefaultFieldDataArray[$TableName]) {
foreach ($TabledefaultFieldDataArray[$TableName] as $TabledefaultFieldName => $TabledefaultFieldValue) {
$UpdateArray[$UpdateRelationType]['target_table_array'][$TableName]['target_field'][$TabledefaultFieldName] = $TabledefaultFieldValue;
}
}
}
}
if ($UpdateData['language_table_array']) {
foreach ($UpdateData['language_table_array'] as $TableName => $TableElement) {
if ($TabledefaultFieldDataArray[$TableName]) {
foreach ($TabledefaultFieldDataArray[$TableName] as $TabledefaultFieldName => $TabledefaultFieldValue) {
$UpdateArray[$UpdateRelationType]['language_table_array'][$TableName]['language_field'][$TabledefaultFieldName] = $TabledefaultFieldValue;
}
}
}
}
if ($UpdateData['table_array']) {
foreach ($UpdateData['table_array'] as $TableName => $TableElement) {
if ($TabledefaultFieldDataArray[$TableName]) {
foreach ($TabledefaultFieldDataArray[$TableName] as $TabledefaultFieldName => $TabledefaultFieldValue) {
$UpdateArray[$UpdateRelationType]['table_array'][$TableName]['main_field'][$TabledefaultFieldName] = $TabledefaultFieldValue;
}
}
}
}
}
}
// echo '';
// var_dump($TabledefaultFieldDataArray);
// echo '
';
//
// echo '';
// var_dump($UpdateArray);
// echo '
';
//
// exit;
for ($UpdateRelationType = 3; $UpdateRelationType > 0; $UpdateRelationType--) {
$UpdateData = $UpdateArray[$UpdateRelationType];
switch ($UpdateRelationType) {
// ********************************************************************************
// ** Aktualsierung eines Feldes des Relationstypen n:m
// ********************************************************************************
case RELATIONTYPE_NM:
// ********************************************************************************
// ** Sprachdatendaten aktualisiern
// ********************************************************************************
if ($UpdateData["language_table_array"]) {
$SQLTargetLanguageTable = "";
$SQLTargetLanguageFieldArray = array();
$SQLTargetLanguageWhereArray = array();
foreach ($UpdateData["language_table_array"] as $TargetLanguageTable => $TargetLanguageTableArray) {
// Daten einlesen, um zu pruefen, welche Felder aktualisiert werden muessen
$SQLTargetLanguageTable = $TargetLanguageTable;
foreach ($TargetLanguageTableArray["language_field"] as $TargetLanguageField => $TargetLanguageFieldValue) {
$SQLTargetLanguageWhereArray[] = $TargetLanguageTable . "." . $TargetLanguageField . " = '" . $TargetLanguageFieldValue . "'";
}
// foreach ($TargetLanguageTableArray["language_languagefield"] as $TargetLanguageLanguageField => $TargetLanguageLanguageIDFieldValue) {
// $SQLTargetLanguageWhereArray[] = $TargetLanguageTable . "." . $TargetLanguageLanguageField . " = " . $TargetLanguageLanguageIDFieldValue;
// }
$SQLTargetLanguageFieldArray[] = $TargetLanguageTable . "." . $TargetLanguageTableArray["language_idfield"];
$CheckTableInsertResultArray = CheckTableInsert($SQLTargetLanguageTable, $SQLTargetLanguageFieldArray, $SQLTargetLanguageWhereArray, $UpdateData["language_table_array"][$TargetLanguageTable]["language_idfield"], $Try);
// Daten ggf. aendern
if ($CheckTableInsertResultArray["found_rows"] == 1) {
// Updateverlauf in der Zieltabelle erweitern
$UpdateData["target_table_array"][$UpdateData["language_table_array"][$TargetLanguageTable]["target_table"]]["target_field"][$UpdateData["language_table_array"][$TargetLanguageTable]["target_idfield"]] = $CheckTableInsertResultArray["insert_id"];
// Daten einfuegen
} elseif ($CheckTableInsertResultArray["found_rows"] == 0) {
global $TableInsertArray;
if ($Try) {
$SQLString = PrepareSQLSelectString($SQLTargetLanguageTable, $SQLTargetLanguageFieldArray, $SQLTargetLanguageWhereArray);
if (!$TableInsertArray[$SQLString]) {
$SQLMaxString = "SELECT ";
$SQLMaxString .= "(MAX(" . $UpdateData["language_table_array"][$TargetLanguageTable]["target_table"] . "." . $UpdateData["language_table_array"][$TargetLanguageTable]["target_idfield"] . ") + 1) AS max_id ";
$SQLMaxString .= "FROM ";
$SQLMaxString .= $UpdateData["language_table_array"][$TargetLanguageTable]["target_table"] . " ";
if (IMPORT_DEBUG) { echo '$SQLMaxString (005): ' . $SQLMaxString . '
'; }
$InsertObject = mysql_fetch_object(mysql_query($SQLMaxString));
$TableInsertArray[$SQLString] = ($InsertObject->max_id + 1);
$TargetInsertID = ($InsertObject->max_id + 1);
$SQLString = "INSERT INTO " . $SQLTargetLanguageTable . " SET ";
$SQLString .= implode(", ", $SQLTargetLanguageWhereArray) . ", ";
$SQLString .= $TargetLanguageTable . "." . $TargetLanguageTableArray["language_idfield"] . " = " . $TargetInsertID;
if (IMPORT_DEBUG) { echo '$SQLString (006): ' . $SQLString . '
'; }
} else {
$TargetInsertID = $TableInsertArray[$SQLString];
}
} else {
$SQLMaxString = "SELECT ";
$SQLMaxString .= "(MAX(" . $UpdateData["language_table_array"][$TargetLanguageTable]["target_table"] . "." . $UpdateData["language_table_array"][$TargetLanguageTable]["target_idfield"] . ")) AS max_id ";
$SQLMaxString .= "FROM ";
$SQLMaxString .= $UpdateData["language_table_array"][$TargetLanguageTable]["target_table"] . " ";
if (IMPORT_DEBUG) { echo '$SQLMaxString (007): ' . $SQLMaxString . '
'; }
$InsertObject = mysql_fetch_object(mysql_query($SQLMaxString));
$TargetInsertID = ($InsertObject->max_id + 1);
$SQLString = "INSERT INTO " . $SQLTargetLanguageTable . " SET ";
$SQLString .= implode(", ", $SQLTargetLanguageWhereArray) . ", ";
$SQLString .= $TargetLanguageTable . "." . $TargetLanguageTableArray["language_idfield"] . " = " . $TargetInsertID;
if (IMPORT_DEBUG) { echo '$SQLString (008): ' . $SQLString . '
'; }
if (IMPORT_ACTIVE) { $MySQLQueryReference = mysql_query($SQLString); }
}
$UpdateData["target_table_array"][$UpdateData["language_table_array"][$TargetLanguageTable]["target_table"]]["target_field"][$UpdateData["language_table_array"][$TargetLanguageTable]["target_idfield"]] = $TargetInsertID;
foreach ($TargetLanguageTableArray["language_field"] as $TargetLanguageField => $TargetLanguageFieldValue) {
$ReportLanguageFieldArray[$TargetLanguageTable][$TargetLanguageField]["import_type"] = "insert";
$ReportLanguageFieldArray[$TargetLanguageTable][$TargetLanguageField]["value_new"] = $TargetLanguageFieldValue;
}
}
}
}
// ********************************************************************************
// ** Zieltabelle aktualisiern
// ********************************************************************************
if ($UpdateData["target_table_array"]) {
foreach ($UpdateData["target_table_array"] as $TargetTable => $TargetArray) {
$SQLTargetTable = "";
$SQLTargetFieldArray = array();
$SQLTargetWhereArray = "";
$TargetInsert = false;
$TargetExists = false;
// Daten einlesen
$SQLTargetTable = $TargetTable;
$SQLTargetFieldArray[] = $TargetTable . "." . $TargetArray["target_idfield"];
foreach ($TargetArray["target_field"] as $TargetField => $Value) {
$SQLTargetFieldArray[] = $TargetTable . "." . $TargetField;
}
foreach ($TargetArray["target_field"] as $TargetField => $Value) {
$SQLTargetWhereArray[] = $TargetTable . "." . $TargetField . " = '" . mysql_escape_string($Value) . "'";
}
$CheckTableInsertResultArray = CheckTableInsert($SQLTargetTable, $SQLTargetFieldArray, $SQLTargetWhereArray, $TargetArray["target_idfield"], $Try);
// Eintrag in der Zieltabelle nicht vorhanden
if ($CheckTableInsertResultArray["found_rows"] == 0) {
$TargetInsert = true;
// Eintrag in der Zieltabelle vorhanden
} elseif ($CheckTableInsertResultArray["found_rows"] == 1) {
$TargetExists = true;
$InsertID = $CheckTableInsertResultArray["insert_id"];
// mehr als Eintrag in der Zieltabelle gefunden: Fehler
} elseif ($CheckTableInsertResultArray["found_rows"] > 1) {
$UpdateCSVRowReturnArray["error"] = $CheckTableInsertResultArray["error"];
$UpdateCSVRowReturnArray["error_message"] = $CheckTableInsertResultArray["error_message"];
return $UpdateCSVRowReturnArray;
}
// Eintrag in die Zieltabelle einfuegen
if ($TargetInsert) {
$TableInsertResultArray = TableInsert($SQLTargetTable, $SQLTargetFieldArray, $SQLTargetWhereArray, $TargetArray["target_idfield"], $Try);
$TargetID = $TableInsertResultArray["insert_id"];
foreach ($TargetArray["target_field"] as $TargetField => $Value) {
$ReportTargetFieldArray[$SQLTargetTable][$TargetField]["import_type"] = "insert";
$ReportTargetFieldArray[$SQLTargetTable][$TargetField]["value_new"] = $Value;
}
// Updateverlauf in der Realtionstabelle erweitern
$UpdateData["relation_table_array"][$TargetArray["relation_table"]]["relation_target_field"] = $TargetID;
$UpdateData["relation_table_array"][$TargetArray["relation_table"]]["relation_main_field"] = $MainTableID;
} elseif ($TargetExists) {
// Updateverlauf in der Haupttabelle erweitern
$UpdateData["relation_table_array"][$TargetArray["relation_table"]]["relation_target_field"] = $InsertID;
$UpdateData["relation_table_array"][$TargetArray["relation_table"]]["relation_main_field"] = $MainTableID;
}
}
}
// ********************************************************************************
// ** Relationstabelle aktualisiern
// ********************************************************************************
if ($UpdateData["relation_table_array"]) {
foreach ($UpdateData["relation_table_array"] as $RelationTable => $RelationArray) {
$SQLCheckString = "SELECT ";
$SQLCheckString .= $RelationArray["relation_table"] . "." . $RelationArray["relation_main_idfield"] . " ";
$SQLCheckString .= "FROM ";
$SQLCheckString .= $RelationArray["relation_table"] . " ";
$SQLCheckString .= "WHERE ";
$SQLCheckString .= $RelationArray["relation_main_idfield"] . " = '" . $RelationArray["relation_main_field"] . "' AND ";
$SQLCheckString .= $RelationArray["relation_target_idfield"] . " = '" . $RelationArray["relation_target_field"] . "' ";
$RelationTableCheckObject = mysql_fetch_object(mysql_query($SQLCheckString));
if (!$Try) {
// alte Relation loeschen
$SQLDeleteString = "DELETE FROM ";
$SQLDeleteString .= $RelationArray["relation_table"] . " ";
$SQLDeleteString .= "WHERE ";
$SQLDeleteString .= $RelationArray["relation_main_idfield"] . " = '" . $MainTableID . "' ";
if (IMPORT_DEBUG) { echo '$SQLDeleteString (020): ' . $SQLDeleteString . '
'; }
$MySQLDeleteQueryReference = mysql_query($SQLDeleteString);
$SQLInsertString = "INSERT INTO " . $RelationArray["relation_table"] . " SET ";
$SQLInsertString .= $RelationArray["relation_main_idfield"] . " = '" . $RelationArray["relation_main_field"] . "', ";
$SQLInsertString .= $RelationArray["relation_target_idfield"] . " = '" . $RelationArray["relation_target_field"] . "' ";
if (IMPORT_DEBUG) { echo '$SQLInsertString (021): ' . $SQLInsertString . '
'; }
$MySQLInsertQueryReference = mysql_query($SQLInsertString);
}
if (!$RelationTableCheckObject) {
$ReportLanguageFieldArray[$RelationArray["relation_table"]][$RelationArray["relation_main_idfield"]]["import_type"] = "insert";
$ReportLanguageFieldArray[$RelationArray["relation_table"]][$RelationArray["relation_main_idfield"]]["value_new"] = $RelationArray["relation_main_field"];
$ReportLanguageFieldArray[$RelationArray["relation_table"]][$RelationArray["relation_main_idfield"]]["import_type"] = "insert";
$ReportLanguageFieldArray[$RelationArray["relation_table"]][$RelationArray["relation_target_idfield"]]["value_new"] = $RelationArray["relation_target_field"];
}
}
}
break;
// ********************************************************************************
// ** Aktualsierung eines Feldes des Relationstypen 1:n
// ********************************************************************************
case RELATIONTYPE_1N:
// ********************************************************************************
// ** Sprachdatendaten aktualisiern
// ********************************************************************************
if ($UpdateData["language_table_array"]) {
$SQLTargetLanguageTable = "";
$SQLTargetLanguageFieldArray = array();
$SQLTargetLanguageWhereArray = array();
foreach ($UpdateData["language_table_array"] as $TargetLanguageTable => $TargetLanguageTableArray) {
// Daten einlesen, um zu pruefen, welche Felder aktualisiert werden muessen
$SQLTargetLanguageTable = $TargetLanguageTable;
foreach ($TargetLanguageTableArray["language_field"] as $TargetLanguageField => $TargetLanguageFieldValue) {
$SQLTargetLanguageWhereArray[] = $TargetLanguageTable . "." . $TargetLanguageField . " = '" . $TargetLanguageFieldValue . "'";
}
// foreach ($TargetLanguageTableArray["language_languagefield"] as $TargetLanguageLanguageField => $TargetLanguageLanguageIDFieldValue) {
// $SQLTargetLanguageWhereArray[] = $TargetLanguageTable . "." . $TargetLanguageLanguageField . " = " . $TargetLanguageLanguageIDFieldValue;
// }
$SQLTargetLanguageFieldArray[] = $TargetLanguageTable . "." . $TargetLanguageTableArray["language_idfield"];
$CheckTableInsertResultArray = CheckTableInsert($SQLTargetLanguageTable, $SQLTargetLanguageFieldArray, $SQLTargetLanguageWhereArray, $UpdateData["language_table_array"][$TargetLanguageTable]["language_idfield"], $Try);
// Daten ggf. aendern
if ($CheckTableInsertResultArray["found_rows"] == 1) {
// Updateverlauf in der Zieltabelle erweitern
$UpdateData["target_table_array"][$UpdateData["language_table_array"][$TargetLanguageTable]["target_table"]]["target_field"][$UpdateData["language_table_array"][$TargetLanguageTable]["target_idfield"]] = $CheckTableInsertResultArray["insert_id"];
// Daten einfuegen
} elseif ($CheckTableInsertResultArray["found_rows"] == 0) {
global $TableInsertArray;
if ($Try) {
$SQLString = PrepareSQLSelectString($SQLTargetLanguageTable, $SQLTargetLanguageFieldArray, $SQLTargetLanguageWhereArray);
if (!$TableInsertArray[$SQLString]) {
$SQLMaxString = "SELECT ";
$SQLMaxString .= "(MAX(" . $UpdateData["language_table_array"][$TargetLanguageTable]["target_table"] . "." . $UpdateData["language_table_array"][$TargetLanguageTable]["target_idfield"] . ") + 1) AS max_id ";
$SQLMaxString .= "FROM ";
$SQLMaxString .= $UpdateData["language_table_array"][$TargetLanguageTable]["target_table"] . " ";
if (IMPORT_DEBUG) { echo '$SQLMaxString (005): ' . $SQLMaxString . '
'; }
$InsertObject = mysql_fetch_object(mysql_query($SQLMaxString));
$TableInsertArray[$SQLString] = ($InsertObject->max_id + 1);
$TargetInsertID = ($InsertObject->max_id + 1);
$SQLString = "INSERT INTO " . $SQLTargetLanguageTable . " SET ";
$SQLString .= implode(", ", $SQLTargetLanguageWhereArray) . " ";
// $SQLString .= $TargetLanguageTable . "." . $TargetLanguageTableArray["language_idfield"] . " = " . $TargetInsertID;
if (IMPORT_DEBUG) { echo '$SQLString (006): ' . $SQLString . '
'; }
} else {
$TargetInsertID = $TableInsertArray[$SQLString];
}
} else {
$SQLMaxString = "SELECT ";
$SQLMaxString .= "(MAX(" . $UpdateData["language_table_array"][$TargetLanguageTable]["target_table"] . "." . $UpdateData["language_table_array"][$TargetLanguageTable]["target_idfield"] . ")) AS max_id ";
$SQLMaxString .= "FROM ";
$SQLMaxString .= $UpdateData["language_table_array"][$TargetLanguageTable]["target_table"] . " ";
if (IMPORT_DEBUG) { echo '$SQLMaxString (007): ' . $SQLMaxString . '
'; }
$InsertObject = mysql_fetch_object(mysql_query($SQLMaxString));
$TargetInsertID = ($InsertObject->max_id + 1);
$SQLString = "INSERT INTO " . $SQLTargetLanguageTable . " SET ";
$SQLString .= implode(", ", $SQLTargetLanguageWhereArray) . ", ";
$SQLString .= $TargetLanguageTable . "." . $TargetLanguageTableArray["language_idfield"] . " = " . $TargetInsertID;
if (IMPORT_DEBUG) { echo '$SQLString (008): ' . $SQLString . '
'; }
if (IMPORT_ACTIVE) { $MySQLQueryReference = mysql_query($SQLString); }
}
$UpdateData["target_table_array"][$UpdateData["language_table_array"][$TargetLanguageTable]["target_table"]]["target_field"][$UpdateData["language_table_array"][$TargetLanguageTable]["target_idfield"]] = $TargetInsertID;
foreach ($TargetLanguageTableArray["language_languagefield"] as $TargetLanguageLanguageField => $TargetLanguageLanguageIDFieldValue) {
$ReportLanguageFieldArray[$TargetLanguageTable][$TargetLanguageLanguageField]["import_type"] = "insert";
$ReportLanguageFieldArray[$TargetLanguageTable][$TargetLanguageLanguageField]["value_new"] = $TargetLanguageLanguageIDFieldValue;
}
foreach ($TargetLanguageTableArray["language_field"] as $TargetLanguageField => $TargetLanguageFieldValue) {
$ReportLanguageFieldArray[$TargetLanguageTable][$TargetLanguageField]["import_type"] = "insert";
$ReportLanguageFieldArray[$TargetLanguageTable][$TargetLanguageField]["value_new"] = $TargetLanguageFieldValue;
}
}
}
}
// ********************************************************************************
// ** Zieltabelle aktualisiern
// ********************************************************************************
if ($UpdateData["target_table_array"]) {
foreach ($UpdateData["target_table_array"] as $TargetTable => $TargetArray) {
$SQLTargetTable = "";
$SQLTargetFieldArray = array();
$SQLTargetWhereArray = "";
$TargetInsert = false;
$TargetExists = false;
// Daten einlesen
$SQLTargetTable = $TargetTable;
$SQLTargetFieldArray[] = $TargetTable . "." . $TargetArray["target_idfield"];
foreach ($TargetArray["target_field"] as $TargetField => $Value) {
$SQLTargetFieldArray[] = $TargetTable . "." . $TargetField;
}
foreach ($TargetArray["target_field"] as $TargetField => $Value) {
$SQLTargetWhereArray[] = $TargetTable . "." . $TargetField . " = '" . mysql_escape_string($Value) . "'";
}
$CheckTableInsertResultArray = CheckTableInsert($SQLTargetTable, $SQLTargetFieldArray, $SQLTargetWhereArray, $TargetArray["target_idfield"], $Try);
// Eintrag in der Zieltabelle nicht vorhanden
if ($CheckTableInsertResultArray["found_rows"] == 0) {
$TargetInsert = true;
// Eintrag in der Zieltabelle vorhanden
} elseif ($CheckTableInsertResultArray["found_rows"] == 1) {
$TargetExists = true;
$InsertID = $CheckTableInsertResultArray["insert_id"];
// mehr als Eintrag in der Zieltabelle gefunden: Fehler
} elseif ($CheckTableInsertResultArray["found_rows"] > 1) {
$UpdateCSVRowReturnArray["error"] = $CheckTableInsertResultArray["error"];
$UpdateCSVRowReturnArray["error_message"] = $CheckTableInsertResultArray["error_message"];
return $UpdateCSVRowReturnArray;
}
// Eintrag in die Zieltabelle einf�gen
if ($TargetInsert) {
$TableInsertResultArray = TableInsert($SQLTargetTable, $SQLTargetFieldArray, $SQLTargetWhereArray, $TargetArray["target_idfield"], $Try);
$TargetID = $TableInsertResultArray["insert_id"];
foreach ($TargetArray["target_field"] as $TargetField => $Value) {
$ReportTargetFieldArray[$SQLTargetTable][$TargetField]["import_type"] = "insert";
$ReportTargetFieldArray[$SQLTargetTable][$TargetField]["value_new"] = $Value;
}
// Updateverlauf in der Haupttabelle erweitern
$UpdateArray[1]["table_array"][$UpdateData["target_table_array"][$TargetTable]["main_table"]]["main_field"][$UpdateData["target_table_array"][$TargetTable]["main_field"]] = $TargetID;
} elseif ($TargetExists) {
// Updateverlauf in der Haupttabelle erweitern
$UpdateArray[1]["table_array"][$UpdateData["target_table_array"][$TargetTable]["main_table"]]["main_field"][$UpdateData["target_table_array"][$TargetTable]["main_field"]] = $InsertID;
}
}
}
break;
// ********************************************************************************
// ** Aktualsierung eines Feldes des Relationstypen 1:1
// ********************************************************************************
case RELATIONTYPE_11:
// ********************************************************************************
// ** Sprachdatendaten aktualisiern
// ********************************************************************************
if ($UpdateData["language_table_array"]) {
$SQLMainLanguageTable = "";
$SQLMainLanguageFieldArray = array();
$SQLMainLanguageWhereArray = array();
foreach ($UpdateData["language_table_array"] as $LanguageTable => $TableArray) {
// Daten einlesen, um zu pr�fen, welche Felder aktualisiert werden m�ssen
$SQLMainLanguageTable = $LanguageTable;
foreach ($TableArray["language_field"] as $LanguageField => $LanguageFieldValue) {
$SQLMainLanguageFieldArray[] = $LanguageTable . "." . $LanguageField;
}
foreach ($TableArray["language_idfield"] as $LanguageIDField => $LanguageIDFieldValue) {
$SQLMainLanguageWhereArray[] = $LanguageTable . "." . $LanguageIDField . " = " . $LanguageIDFieldValue;
}
// foreach ($TableArray["language_languagefield"] as $LanguageLanguageField => $LanguageLanguageIDFieldValue) {
// $SQLMainLanguageWhereArray[] = $LanguageTable . "." . $LanguageLanguageField . " = " . $LanguageLanguageIDFieldValue;
// }
$SQLMainLanguageFieldArray = array_unique($SQLMainLanguageFieldArray);
$SQLMainLanguageWhereArray = array_unique($SQLMainLanguageWhereArray);
$SQLString = PrepareSQLSelectString($SQLMainLanguageTable, $SQLMainLanguageFieldArray, $SQLMainLanguageWhereArray);
if (IMPORT_DEBUG) { echo '$SQLString (009): ' . $SQLString . '
'; }
$DBMainLanguageValueArray = mysql_fetch_array(mysql_query($SQLString), MYSQL_ASSOC);
// Daten ggf. aendern
if ($DBMainLanguageValueArray) {
$SQLMainLanguageFieldArray = array();
$SQLMainLanguageWhereArray = array();
$SQLMainLanguageTable = $LanguageTable;
foreach ($TableArray["language_field"] as $LanguageField => $LanguageFieldValue) {
if ($LanguageFieldValue != $DBMainLanguageValueArray[$LanguageField]) {
$SQLMainLanguageFieldArray[] = $LanguageTable . "." . $LanguageField . " = '" . $LanguageFieldValue . "'";
$ReportLanguageFieldArray[$LanguageTable][$LanguageField]["import_type"] = "update";
$ReportLanguageFieldArray[$LanguageTable][$LanguageField]["value_old"] = $DBMainLanguageValueArray[$LanguageField];
$ReportLanguageFieldArray[$LanguageTable][$LanguageField]["value_new"] = $LanguageFieldValue;
}
}
foreach ($TableArray["language_idfield"] as $LanguageIDField => $LanguageIDFieldValue) {
$SQLMainLanguageWhereArray[] = $LanguageTable . "." . $LanguageIDField . " = " . $LanguageIDFieldValue;
}
// foreach ($TableArray["language_languagefield"] as $LanguageLanguageField => $LanguageLanguageIDFieldValue) {
// $SQLMainLanguageWhereArray[] = $LanguageTable . "." . $LanguageLanguageField . " = " . $LanguageLanguageIDFieldValue;
// }
$SQLMainLanguageFieldArray = array_unique($SQLMainLanguageFieldArray);
$SQLMainLanguageWhereArray = array_unique($SQLMainLanguageWhereArray);
if (count($SQLMainLanguageFieldArray) > 0) {
$SQLString = "UPDATE ";
$SQLString .= $SQLMainLanguageTable . " ";
$SQLString .= "SET ";
$SQLString .= implode(",", $SQLMainLanguageFieldArray) . " ";
$SQLString .= "WHERE ";
$SQLString .= implode(" AND ", $SQLMainLanguageWhereArray);
if (IMPORT_DEBUG) { echo '$SQLString (010): ' . $SQLString . '
'; }
if (!$Try) {
if (IMPORT_ACTIVE) { $MySQLQueryReference = mysql_query($SQLString); }
}
}
// Daten einf�gen
} else {
$SQLString = "INSERT INTO " . $SQLMainLanguageTable . " SET ";
$SQLString .= implode(", ", $SQLMainLanguageWhereArray) . ", ";
foreach ($TableArray["language_field"] as $LanguageField => $LanguageFieldValue) {
$SQLString .= $SQLMainLanguageTable . "." . $LanguageField . " = '" . $LanguageFieldValue . "', ";
}
$SQLString = substr($SQLString, 0, strlen($SQLString) - 2);
if (IMPORT_DEBUG) { echo '$SQLString (011): ' . $SQLString . '
'; }
if (!$Try) {
if (IMPORT_ACTIVE) { $MySQLQueryReference = mysql_query($SQLString); }
}
foreach ($TableArray["language_idfield"] as $LanguageIDField => $LanguageIDFieldValue) {
$ReportLanguageFieldArray[$LanguageTable][$LanguageIDField]["import_type"] = "insert";
$ReportLanguageFieldArray[$LanguageTable][$LanguageIDField]["value_new"] = $LanguageIDFieldValue;
}
foreach ($TableArray["language_field"] as $LanguageField => $LanguageFieldValue) {
$ReportLanguageFieldArray[$LanguageTable][$LanguageField]["import_type"] = "insert";
$ReportLanguageFieldArray[$LanguageTable][$LanguageField]["value_new"] = $LanguageFieldValue;
}
}
}
}
// ********************************************************************************
// ** Haupttabelle aktualisiern
// ********************************************************************************
$SQLMainTable = "";
$SQLMainFieldArray = array();
$SQLMainWhere = "";
foreach ($UpdateData["table_array"] as $MainTable => $TableArray) {
// Daten einlesen
$SQLMainTable = $MainTable;
foreach ($TableArray["main_field"] as $MainField => $Value) {
$SQLMainFieldArray[] = $MainTable . "." . $MainField;
}
// if ($MainTableType == "update") {
if (($MainTableType == "update") || ($MainTableID)) {
$SQLMainWhere = $MainTable . "." . $TableArray["main_idfield"] . " = " . $MainTableID;
} else {
$SQLMainWhere = $MainTable . "." . $TableArray["main_idfield"] . " IN (" . implode(",", $MainTableIDArray) . ")";
}
$SQLMainFieldArray = array_unique($SQLMainFieldArray);
$SQLString = "SELECT ";
$SQLString .= implode(", ", $SQLMainFieldArray) . " ";
$SQLString .= "FROM ";
$SQLString .= $SQLMainTable . " ";
$SQLString .= "WHERE ";
$SQLString .= $SQLMainWhere . " ";
if (IMPORT_DEBUG) { echo '$SQLString (012): ' . $SQLString . '
'; }
$DBMainValueArray = mysql_fetch_array(mysql_query($SQLString), MYSQL_ASSOC);
// Daten aendern
$SQLMainFieldArray = array();
$SQLMainTable = $MainTable;
foreach ($TableArray["main_field"] as $MainField => $Value) {
if ($Value != $DBMainValueArray[$MainField]) {
$SQLMainFieldArray[] = $MainTable . "." . $MainField . " = '" . mysql_escape_string($Value) . "'";
$ReportMainFieldArray[$MainTable][$MainField]["import_type"] = $MainTableType;
$ReportMainFieldArray[$MainTable][$MainField]["value_old"] = $DBMainValueArray[$MainField];
$ReportMainFieldArray[$MainTable][$MainField]["value_new"] = $Value;
}
}
// if ($MainTableType == "update") {
if (($MainTableType == "update") || ($MainTableID)) {
$SQLMainWhere = $MainTable . "." . $TableArray["main_idfield"] . " = " . $MainTableID;
} else {
$SQLMainWhere = $MainTable . "." . $TableArray["main_idfield"] . " IN (" . implode(",", $MainTableIDArray) . ")";
}
$SQLMainFieldArray = array_unique($SQLMainFieldArray);
if (count($SQLMainFieldArray) > 0) {
$SQLString = "UPDATE ";
$SQLString .= $SQLMainTable . " ";
$SQLString .= "SET ";
$SQLString .= implode(", ", $SQLMainFieldArray) . " ";
$SQLString .= "WHERE ";
$SQLString .= $SQLMainWhere;
if (IMPORT_DEBUG) { echo '$SQLString (013): ' . $SQLString . '
'; }
// if ($FilterObject->option_multi && )
if (!$Try) {
if (IMPORT_ACTIVE) {
$MySQLQueryReference = mysql_query($SQLString);
}
}
}
}
break;
}
}
// echo '';
// var_dump($UpdateData);
// echo '
';
//
// echo '';
// var_dump($ReportMainFieldArray);
// echo '
';
//
// echo '';
// var_dump($ReportLanguageFieldArray);
// echo '
';
//
// echo '';
// var_dump($ReportTargetFieldArray);
// echo '
';
//
// exit;
$ReportFieldArray = array();
if ($ReportMainFieldArray) {
$ReportFieldArray = array_merge($ReportFieldArray, $ReportMainFieldArray);
}
if ($ReportLanguageFieldArray) {
$ReportFieldArray = array_merge($ReportFieldArray, $ReportLanguageFieldArray);
}
if ($ReportTargetFieldArray) {
$ReportFieldArray = array_merge($ReportFieldArray, $ReportTargetFieldArray);
}
if ($ReportFieldArray) {
$UpdateCSVRowReturnArray["success"] = true;
$UpdateCSVRowReturnArray["import_type"] = $MainTableType;
$UpdateCSVRowReturnArray["field_array"] = $ReportFieldArray;
$UpdateCSVRowReturnArray["import_status"] = $ImportStatus;
} else {
$UpdateCSVRowReturnArray["success"] = true;
$UpdateCSVRowReturnArray["import_status"] = IMPORT_STATUS_NOCHANGE;
}
return $UpdateCSVRowReturnArray;
}
function GetFieldArray($FilterID) {
$ImportError = false;
// Felder ohne Relation einlesen
$SQLString = "SELECT ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".field_id, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".main_table, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".main_field, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".main_primary_field, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".main_language_field, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".relation_table, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".relation_field, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".relation_primary_field, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".relation_language_field, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".identifier, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".ignore, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".mandatory, ";
$SQLString .= TABLE_ARTIKELIMPORT_FILTERS_FIELDS . ".sort ";
$SQLString .= "FROM ";
$SQLString .= TABLE_ARTIKELIMPORT_FILTERS_FIELDS . " ";
$SQLString .= "LEFT JOIN " . TABLE_ARTIKELIMPORT_FIELDS . " ON " . TABLE_ARTIKELIMPORT_FILTERS_FIELDS . ".field_id = " . TABLE_ARTIKELIMPORT_FIELDS . ".field_id ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_ARTIKELIMPORT_FILTERS_FIELDS . ".filter_id = '" . $FilterID . "' AND ";
$SQLString .= "ISNULL(" . TABLE_ARTIKELIMPORT_FIELDS . ".relation_table) ";
$SQLString .= "ORDER BY ";
$SQLString .= TABLE_ARTIKELIMPORT_FILTERS_FIELDS . ".sort ASC ";
// echo '$SQLString: ' . $SQLString . '
';
$MySQLQueryreference = mysql_query($SQLString);
$MainTableArray = array();
while ($FieldRow = mysql_fetch_array($MySQLQueryreference, MYSQL_ASSOC)) {
$MainTableArray[$FieldRow["main_table"]]["table_name"] = $FieldRow["main_table"];
$MainTableArray[$FieldRow["main_table"]]["primary_field"] = $FieldRow["main_primary_field"];
$MainTableArray[$FieldRow["main_table"]]["field_array"][$FieldRow["sort"]]["field_name"] = $FieldRow["main_field"];
$MainTableArray[$FieldRow["main_table"]]["field_array"][$FieldRow["sort"]]["language_field"] = $FieldRow["main_language_field"];
$MainTableArray[$FieldRow["main_table"]]["field_array"][$FieldRow["sort"]]["identifier"] = $FieldRow["identifier"];
$MainTableArray[$FieldRow["main_table"]]["field_array"][$FieldRow["sort"]]["ignore"] = $FieldRow["ignore"];
$MainTableArray[$FieldRow["main_table"]]["field_array"][$FieldRow["sort"]]["mandatory"] = $FieldRow["mandatory"];
$MainTableArray[$FieldRow["main_table"]]["field_array"][$FieldRow["sort"]]["sort"] = $FieldRow["sort"];
if ($FieldRow["identifier"]) {
$MainTableArray[$FieldRow["main_table"]]["identifier_array"][$FieldRow["sort"]]["field_name"] = $FieldRow["main_field"];
$MainTableArray[$FieldRow["main_table"]]["identifier_array"][$FieldRow["sort"]]["sort"] = $FieldRow["sort"];
}
}
// Fehler pr�fen
if (count($MainTableArray) == 0) {
$ImportError = true;
$ImportErrorMessage .= "Fehler: keine Haupttabelle definiert!
";
}
foreach ($MainTableArray as $MainTable) {
if (count($MainTable["identifier_array"]) == 0) {
$ImportError = true;
$ImportErrorMessage .= "Fehler: kein Identifikations Feld f�r die Haupttabelle " . $MainTable["table_name"] . " definiert!
";
}
}
// Felder mit Relation einlesen
$SQLString = "SELECT ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".field_id, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".main_table, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".main_field, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".main_primary_field, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".main_language_field, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".relation_table, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".relation_field, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".relation_primary_field, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".relation_language_field, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".identifier, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".ignore, ";
$SQLString .= TABLE_ARTIKELIMPORT_FIELDS . ".mandatory, ";
$SQLString .= TABLE_ARTIKELIMPORT_FILTERS_FIELDS . ".sort ";
$SQLString .= "FROM ";
$SQLString .= TABLE_ARTIKELIMPORT_FILTERS_FIELDS . " ";
$SQLString .= "LEFT JOIN " . TABLE_ARTIKELIMPORT_FIELDS . " ON " . TABLE_ARTIKELIMPORT_FILTERS_FIELDS . ".field_id = " . TABLE_ARTIKELIMPORT_FIELDS . ".field_id ";
$SQLString .= "WHERE ";
$SQLString .= TABLE_ARTIKELIMPORT_FILTERS_FIELDS . ".filter_id = '" . $FilterID . "' AND ";
$SQLString .= "!ISNULL(" . TABLE_ARTIKELIMPORT_FIELDS . ".relation_table) ";
$SQLString .= "ORDER BY ";
$SQLString .= TABLE_ARTIKELIMPORT_FILTERS_FIELDS . ".sort ASC ";
// echo '$SQLString: ' . $SQLString . '
';
$MySQLQueryreference = mysql_query($SQLString);
$RelationFieldArray = array();
while ($FieldRow = mysql_fetch_array($MySQLQueryreference, MYSQL_ASSOC)) {
if (isset($MainTableArray["relation_table"])) {
$ImportError = true;
$ImportErrorMessage .= "Fehler: f�r das Feld " . $FieldRow["field_name"] . " der Tabelle " . $FieldRow["table_name"] . " ist keine Haupttabelle in dem Filter hinterlegt!";
} else {
$MainTableArray[$FieldRow["relation_table"]]["relation_table_array"][$FieldRow["main_table"]]["table_name"] = $FieldRow["main_table"];
$MainTableArray[$FieldRow["relation_table"]]["relation_table_array"][$FieldRow["main_table"]]["primary_field"] = $FieldRow["main_primary_field"];
$MainTableArray[$FieldRow["relation_table"]]["relation_table_array"][$FieldRow["main_table"]]["field_array"][$FieldRow["sort"]]["field_name"] = $FieldRow["main_field"];
$MainTableArray[$FieldRow["relation_table"]]["relation_table_array"][$FieldRow["main_table"]]["field_array"][$FieldRow["sort"]]["identifier"] = $FieldRow["identifier"];
$MainTableArray[$FieldRow["relation_table"]]["relation_table_array"][$FieldRow["main_table"]]["field_array"][$FieldRow["sort"]]["ignore"] = $FieldRow["ignore"];
$MainTableArray[$FieldRow["relation_table"]]["relation_table_array"][$FieldRow["main_table"]]["field_array"][$FieldRow["sort"]]["mandatory"] = $FieldRow["mandatory"];
$MainTableArray[$FieldRow["relation_table"]]["relation_table_array"][$FieldRow["main_table"]]["field_array"][$FieldRow["sort"]]["sort"] = $FieldRow["sort"];
if ($FieldRow["identifier"]) {
$MainTableArray[$FieldRow["relation_table"]]["relation_table_array"][$FieldRow["table_name"]]["identifier_array"][] = $FieldRow["identifier"];
}
}
}
if ($ImportError) {
echo $ImportErrorMessage;
exit;
}
// echo "";
// var_dump($MainTableArray);
// echo "
";
return $MainTableArray;
}