/**
* Get possible converters
*
* @param $objRow
* @return array
*/
public function getConverters($objRow)
{
$return = array();
$objConverters = ConverterModel::findAll();
while ($objConverters->next()) {
$return[$objConverters->id] = $objConverters->title;
}
return $return;
}
/**
* Fill the tmp table in the way specified in the dada converter
*
* @param $objRun
* @return bool|mixed
*/
public static function fillTable($objRun)
{
foreach ($objRun->sources as $strSource) {
if (!in_array($strSource, $objRun->filled)) {
$intConverter = str_replace('_source', '', str_replace('cvx_', '', $strSource));
$objConverter = ConverterModel::findByPk($intConverter);
// get the glass of the converter
$strClass = ($GLOBALS['convertx']['classpath'][$objConverter->sourceType] ? $GLOBALS['convertx']['classpath'][$objConverter->sourceType] : 'Delahaye\\ConvertX\\Container') . '\\' . $objConverter->sourceType;
// fill temp table
if ($strClass::rawImport($intConverter, $objRun)) {
Tracking::log($objRun->id, $objRun->rootRun, sprintf($GLOBALS['TL_LANG']['tl_convertx_job']['temporarySourceFilled'], $intConverter), 'entry');
return $intConverter;
} else {
Tracking::log($objRun->id, $objRun->rootRun, sprintf($GLOBALS['TL_LANG']['tl_convertx_job']['temporarySourceNotFilled'], $intConverter), 'entry', 'error');
return false;
}
// fill exactly 1 of the temp source tables at a time
continue;
}
}
}
/**
* build a config for the target fields of the converter
*
* @param $intConverter
*/
public static function createTargetFields($intConverter)
{
$objConverter = Converter::findByPk($intConverter);
$objConverterfields = Converterfield::findBy('pid', $intConverter);
// -----------------------------------------
// check if we already have some field definitions, only add new
if ($objConverterfields) {
while ($objConverterfields->next()) {
$arrFieldsSource = $arrFieldsSource ? $arrFieldsSource : unserialize($objConverter->fieldsSource);
$arrFieldsTarget = $arrFieldsTarget ? $arrFieldsTarget : unserialize($objConverter->fieldsTarget);
$BlnAllowInsert = $BlnAllowInsert ? $BlnAllowInsert : $objConverter->allowInsert;
$BlnAllowUpdate = $BlnAllowUpdate ? $BlnAllowUpdate : $objConverter->allowUpdate;
foreach ($arrFieldsTarget as $k => $v) {
if ($v['name'] == $objConverterfields->fieldname) {
unset($arrFieldsTarget[$k]);
$sorting[] = $objConverterfields->sorting;
}
}
}
rsort($sorting);
$sorting = $sorting[0];
} else {
$arrFieldsSource = unserialize($objConverter->fieldsSource);
$arrFieldsTarget = unserialize($objConverter->fieldsTarget);
$BlnAllowInsert = $objConverter->allowInsert;
$BlnAllowUpdate = $objConverter->allowUpdate;
}
// source fields, collect fieldnames
foreach ($arrFieldsSource as $fieldSource) {
$arrFieldnamesSource[] = $fieldSource['name'];
}
// pre-define converterdefinitions for existing target fields, only 1:1
foreach ($arrFieldsTarget as $fieldTarget) {
$sorting = $sorting + 32;
$arrNew = array('pid' => $intConverter, 'sorting' => $sorting, 'tstamp' => time(), 'published' => '', 'fieldname' => $fieldTarget['name'], 'allowInsert' => $BlnAllowInsert ? $BlnAllowInsert : '', 'typeInsert' => 'Insertfromfield', 'fieldInsert' => in_array($fieldTarget['name'], $arrFieldnamesSource) ? $fieldTarget['name'] : '', 'modeInsert' => 'Addnew', 'allowUpdate' => $BlnAllowUpdate ? $BlnAllowUpdate : '', 'typeUpdate' => 'Updatefromfield', 'fieldUpdate' => in_array($fieldTarget['name'], $arrFieldnamesSource) ? $fieldTarget['name'] : '', 'modeUpdate' => 'Replace');
$objNew = new Converterfield();
$objNew->setRow($arrNew);
$objNew->save();
}
}
/**
* Get possible source fields
*
* @param $arrRow
* @return array
*/
public function getSourceFields($arrRow)
{
return ConverterModel::getSourceFieldnames($arrRow->activeRecord->pid);
}
/**
* Find the internal and external source tables (tmp versions)
*
* @param $intPid
* @return array
*/
public static function findTmpSourceTables($intPid)
{
$objSteps = StepModel::findBy(array('tl_convertx_step.pid=?', 'tl_convertx_step.published=?', 'tl_convertx_step.action=?'), array($intPid, 1, 'converter'));
if (!$objSteps) {
return array();
}
while ($objSteps->next()) {
$objConverter = ConverterModel::findByPk($objSteps->converter);
switch ($objConverter->sourceType) {
case 'InternalTable':
// for internal sources we don't need tmp tables
break;
default:
// tmp tables for external data get a name based on the converter id
$arrSources[] = 'cvx_' . $objConverter->id . '_source';
break;
}
}
return (array) $arrSources;
}
/**
* Prepare the tmp table for updates, use the deletion rules for it
*
* @param $objRun
* @return bool
*/
public static function clearTable($objRun)
{
$Database = Database::getInstance();
// perform the deletion rules defined in the converter for every internal target table
foreach ($objRun->targets as $strTarget) {
if (!in_array($strTarget, $objRun->cleared)) {
$objConverter = false;
foreach ($objRun->steps as $intStep) {
$objStep = StepModel::findByPk($intStep);
if ($objStep->action == 'converter' && !$objConverter) {
$objConverter = ConverterModel::findOneBy(array('id=?', 'targetTable=?'), array($objStep->converter, str_replace('cvx_', '', $strTarget)));
}
}
// there are tables not bound on a converter
if (!$objConverter) {
return $strTarget;
} else {
$objConverter->deleteRules = Helper::arrayOnly($objConverter->deleteRules);
$objConverter->fieldsTarget = Helper::arrayOnly($objConverter->fieldsTarget);
$objConverter->fieldsSource = Helper::arrayOnly($objConverter->fieldsSource);
// for updates we need all existent keys of the source and the target table
if ($objConverter->allowUpdate || $objConverter->deleteOnStart != '' && $objConverter->deleteOnStart != 'all') {
// source
$strSource = $objConverter->sourceType == 'InternalTable' ? $objConverter->sourceTable : 'cvx_' . $objConverter->id . '_source';
$objKeys = $Database->prepare("SELECT " . $objConverter->keySource . " FROM " . $strSource)->execute();
$arrSourceKeys = $objKeys->fetchEach($objConverter->keySource);
if ($objConverter->sourceHasFieldnames) {
// seems to be an artefact
// array_shift($arrSourceKeys);
}
// target
$objKeys = $Database->prepare("SELECT " . $objConverter->keyTarget . " FROM " . $strTarget)->execute();
$arrTargetKeys = $objKeys->fetchEach($objConverter->keyTarget);
$objConverter->targetKeys = serialize($arrTargetKeys);
}
$objConverter->save();
$blnOk = true;
// delete all data in target table
if ($objConverter->deleteOnStart == 'all') {
if (!($objDeletion = $Database->prepare("TRUNCATE TABLE " . $strTarget)->execute())) {
$blnOk = false;
}
} elseif ($objConverter->deleteOnStart == 'missing') {
// delete data missing in target, but existing in the source table
if (!($objDeletion = $Database->prepare("DELETE FROM " . $strTarget . " WHERE " . $objConverter->keyTarget . " IN ('" . implode("','", array_diff($arrTargetKeys, $arrSourceKeys)) . "')")->execute())) {
$blnOk = false;
}
} elseif ($objConverter->deleteOnStart == 'existent') {
// delete data already existing in target table
if (!($objDeletion = $Database->prepare("DELETE FROM " . $strTarget . " WHERE " . $objConverter->keyTarget . " IN ('" . implode("','", array_diff($arrTargetKeys, array_diff($arrTargetKeys, $arrSourceKeys))) . "')")->execute())) {
$blnOk = false;
}
}
// delete depending on rules
if ($objConverter->deleteOnRules) {
$strWhere = '';
foreach ($objConverter->deleteRules as $k => $v) {
$strWhere .= ($k > 0 ? ' ' . $v['type'] . ' ' : '') . $v['field'] . ' ';
switch ($v['operator']) {
case 'gteq':
$strWhere .= '>= \'' . addslashes($v['value']) . '\'';
break;
case 'gt':
$strWhere .= '> \'' . addslashes($v['value']) . '\'';
break;
case 'lt':
$strWhere .= '< \'' . addslashes($v['value']) . '\'';
break;
case 'lteq':
$strWhere .= '<= \'' . addslashes($v['value']) . '\'';
break;
case 'begins':
$strWhere .= 'LIKE \'' . addslashes($v['value']) . '%\'';
break;
case 'ends':
$strWhere .= 'LIKE \'%' . addslashes($v['value']) . '\'';
break;
default:
$strWhere .= '= \'' . addslashes($v['value']) . '\'';
break;
}
}
if (!($objDeletion = $Database->prepare("DELETE FROM " . $strTarget . " WHERE " . $strWhere)->execute())) {
$blnOk = false;
}
}
if ($blnOk) {
Tracking::log($objRun->id, $objRun->rootRun, sprintf($GLOBALS['TL_LANG']['tl_convertx_job']['temporaryTargetCleared'], $strTarget), 'entry');
return $strTarget;
} else {
Tracking::log($objRun->id, $objRun->rootRun, sprintf($GLOBALS['TL_LANG']['tl_convertx_job']['temporaryTargetNotCleared'], $strTarget), 'entry', 'error');
return false;
}
}
// fill exactly 1 of the temp source tables at a time
continue;
}
}
}
/**
* Do the raw data import
*
* @param $intConverter
* @param $objRun
* @return bool|mixed
*/
public static function rawImport($intConverter, $objRun)
{
// tmp table mit csv befüllen
$objConverter = ConverterModel::findByPk($intConverter);
$arrFields = unserialize($objConverter->fieldsSource);
foreach ($arrFields as $k => $v) {
$arrFieldNames[] = $v['name'];
}
// the csv file
$objCsv = FilesModel::findByUuid($objConverter->sourceFile);
//print_r($objCsv);die();
// set or determine char set
$strCharset = $objConverter->sourceCharSet ? $objConverter->sourceCharSet : false;
// set or determine line feed
$strLinefeed = $objConverter->sourceLineFeed ? $GLOBALS['convertx']['csv_linefeeds'][$objConverter->sourceLineFeed] : false;
// determine charset and/or line feed
if (!$strCharset || !$strLinefeed) {
$objFile = new File($objCsv->path);
$strCompleteFile = $objFile->getContent();
$strFirstLine = fgets($objFile->handle, 40960);
$strCharset = $strCharset ? $strCharset : Helper::getEncoding($strCompleteFile);
$strLinefeed = $strLinefeed ? $strLinefeed : Helper::getLineFeed($strFirstLine);
}
// set or determine enclosure
$strEnclosure = $objConverter->sourceEnclosure ? '\\' . $GLOBALS['convertx']['csv_enclosures'][$objConverter->sourceEnclosure] : '\\"';
// delete all data in the source table (maybe there after an error before)
Database::getInstance()->prepare("TRUNCATE cvx_" . $intConverter . "_source")->execute();
if (!$objConverter->sourceUseDataInfile) {
// handle csv file line by line
$objFile = new File($objCsv->path);
$arrCompleteFile = $objFile->getContentAsArray();
foreach ($arrCompleteFile as $k => $v) {
if ($k > 0 || !$objConverter->sourceHasFieldnames) {
$arrLine = String::splitCsv($v, $GLOBALS['convertx']['csv_delimiters'][$objConverter->sourceFieldDelimiter]);
$arrSet = array();
foreach ($arrFieldNames as $k2 => $v2) {
$arrSet[$v2] = $arrLine[$k2];
}
Database::getInstance()->prepare("insert into cvx_" . $intConverter . "_source %s")->set($arrSet)->execute();
}
}
} else {
// load it directly into the database
$strSql = sprintf("LOAD DATA LOCAL INFILE '%s' INTO TABLE `%s` CHARACTER SET %s FIELDS TERMINATED BY '%s' OPTIONALLY ENCLOSED BY '%s' LINES TERMINATED BY '%s' IGNORE %s LINES (`%s`)", addslashes(TL_ROOT . '/' . $objCsv->path), 'cvx_' . $intConverter . '_source', $strCharset, $GLOBALS['convertx']['csv_delimiters'][$objConverter->sourceFieldDelimiter], $strEnclosure, $strLinefeed, $objConverter->sourceHasFieldnames ? '1' : '0', implode('`, `', $arrFieldNames));
Database::getInstance()->prepare($strSql)->execute();
}
// try to eliminate lines that are completely empty
Database::getInstance()->prepare("DELETE FROM cvx_" . $intConverter . "_source WHERE `" . implode("`='' AND `", $arrFieldNames) . "`=''")->execute();
return true;
}
/**
* Perform the step
*
* @param $objRun
* @param $objStep
* @return bool|mixed|object
*/
public static function doStep($objRun, $objStep)
{
$objConverter = ConverterModel::findByPk($objStep->converter);
$objConverter->fieldsTarget = Helper::arrayOnly($objConverter->fieldsTarget);
$objConverter->fieldsSource = Helper::arrayOnly($objConverter->fieldsSource);
$objConverter->targetKeys = Helper::arrayOnly($objConverter->targetKeys);
// -----------------------------------------
// walk thru source data
// source
if ($objConverter->sourceType == 'InternalTable') {
$strSource = $objConverter->useTempSource ? 'cvx_' . $objConverter->sourceTable : $objConverter->sourceTable;
} else {
$strSource = 'cvx_' . $objConverter->id . '_source';
}
$objSource = Database::getInstance()->prepare("SELECT * FROM " . $strSource)->execute();
$strKeySource = $objConverter->keySource;
$intBegin = time();
$intLine = 0;
while ($objSource->next()) {
$intLine++;
// skip already processed lines
if ($objRun->line > 0 && $intLine <= $objRun->line) {
continue;
}
// perform an update if possible (allowed, key exists, target table not truncated)
if ($objConverter->allowUpdate && in_array($objSource->{$strKeySource}, $objConverter->targetKeys) && $objConverter->deleteOnStart != 'all') {
if ($strUpdated = self::insertOrUpdate('update', $objRun, $objConverter, $objSource)) {
$arrUpdated[] = $strUpdated;
} else {
$hasErrors = true;
// abort step if 1 line fails
if ($objConverter->abortOnError) {
break;
}
}
} elseif ($objConverter->allowInsert) {
// elseif ($objConverter->allowInsert && (!$objConverter->allowUpdate || ($objConverter->allowUpdate && !in_array($objSource->$strKeySource, $objConverter->targetKeys)))) {
if ($strInserted = self::insertOrUpdate('insert', $objRun, $objConverter, $objSource)) {
if ($strInserted != 'skip') {
$arrInserted[] = $strInserted;
}
} else {
$hasErrors = true;
// abort step if 1 line fails
if ($objConverter->abortOnError) {
break;
}
}
}
// check for performance of this step within the max execution time
$intNow = time();
if ($intNow > $intBegin + $GLOBALS['convertx']['maxExecutionTime']) {
$reload = true;
break;
}
}
// -----------------------------------------
// an error may not lead to a step abortion
if ($hasErrors && $objConverter->abortOnError) {
if ($objStep->abortOnError) {
$objReturn = (object) null;
$objReturn->title = $GLOBALS['TL_LANG']['tl_convertx_job']['abortion'];
$objReturn->error = sprintf($GLOBALS['TL_LANG']['tl_convertx_job']['abortionStep'], $objStep->title);
return $objReturn;
}
}
// -----------------------------------------
// note the amount of manipulated data sets
if (count($arrInserted) > 0) {
Tracking::log($objRun->id, $objRun->rootRun, sprintf($GLOBALS['TL_LANG']['tl_convertx_job']['setsInserted'], count($arrInserted)), 'entry', 'ok', print_r($arrInserted, true));
}
if (count($arrUpdated) > 0) {
Tracking::log($objRun->id, $objRun->rootRun, sprintf($GLOBALS['TL_LANG']['tl_convertx_job']['setsUpdated'], count($arrUpdated)), 'entry', 'ok', print_r($arrUpdated, true));
}
if ($reload) {
$objReturn = (object) null;
$objReturn->title = $GLOBALS['TL_LANG']['tl_convertx_job']['splitting'];
$objReturn->line = $intLine;
return $objReturn;
}
return true;
}
/**
* Save field lists for target and source
*
* @param DataContainer $dc
*/
public function setFieldLists(DataContainer $dc)
{
$objData = ConverterModel::findByPk(Input::get('id'));
$classTarget = $dc->activeRecord->targetType ? ($GLOBALS['convertx']['classpath'][$dc->activeRecord->targetType] ? $GLOBALS['convertx']['classpath'][$dc->activeRecord->targetType] : 'Delahaye\\ConvertX\\Container') . '\\' . $dc->activeRecord->targetType : false;
$classSource = $dc->activeRecord->sourceType ? ($GLOBALS['convertx']['classpath'][$dc->activeRecord->sourceType] ? $GLOBALS['convertx']['classpath'][$dc->activeRecord->sourceType] : 'Delahaye\\ConvertX\\Container') . '\\' . $dc->activeRecord->sourceType : false;
// check if target fields have to be cleared
if ($classTarget) {
if ($classTarget::checkTargetClear($dc, $objData)) {
ConverterfieldModel::clearTargetFields(Input::get('id'));
}
}
// use actual data
foreach ($GLOBALS['TL_DCA']['tl_convertx_converter']['fields'] as $k => $v) {
$objData->{$k} = $dc->activeRecord->{$k};
}
// get field lists for source and/or target
if ($classTarget) {
$objData->fieldsTarget = $dc->activeRecord->targetType ? serialize($classTarget::getFieldList($dc->activeRecord, 'target')) : false;
}
if ($classSource) {
$objData->fieldsSource = $dc->activeRecord->sourceType ? serialize($classSource::getFieldList($dc->activeRecord, 'source')) : false;
}
// save the model
$objData->save();
return;
}
