/**
* Get the local recordset associated to this transaction
* @return object resource Recordset resource
* @access protected
*/
function getLocalRecordset()
{
tNG_log::log('tNG_custom', 'getLocalRecordset');
$fakeArr = array();
$tmpArr = $this->columns;
foreach ($tmpArr as $colName => $colDetails) {
$tmpVal = KT_escapeForSql($colDetails['default'], $colDetails['type'], true);
$fakeArr[$colName] = $tmpVal;
}
return $this->getFakeRecordset($fakeArr);
}
function getCondition()
{
$all_string = "all";
$condition = '1=1';
// only records with link;
if ($this->linkRenderType == 1) {
$sql = 'SELECT DISTINCT a.' . $this->pk . ', a.' . $this->fieldName . ' FROM ' . $this->tableName . ' a INNER JOIN ' . $this->tableDetail . ' b ON a.' . $this->pk . '=b.' . $this->fk . ' ORDER BY a.' . $this->fieldName;
} else {
$sql = 'SELECT DISTINCT ' . $this->pk . ', ' . $this->fieldName . ' FROM ' . $this->tableName . ' ORDER BY ' . $this->fieldName;
}
$this->arrCategory = $this->getRecords($sql);
$sql = 'SELECT DISTINCT a.' . $this->pk . ', a.' . $this->fieldName . ' FROM ' . $this->tableName . ' a INNER JOIN ' . $this->tableDetail . ' b ON a.' . $this->pk . '=b.' . $this->fk . ' ORDER BY a.' . $this->fieldName;
$this->arrCategoryWithRec = $this->getRecords($sql);
if (count($this->arrCategory) > 0) {
if (isset($_GET[$this->getVarName])) {
$needle = KT_getRealValue("GET", $this->getVarName);
if ($needle !== $all_string && in_array($needle, $this->arrCategory)) {
$cond = array_search($needle, $this->arrCategory);
}
} else {
$arr = $this->arrCategory;
if (count($this->arrCategoryWithRec) > 0) {
$arr = $this->arrCategoryWithRec;
}
$needle = array_shift($arr);
$cond = array_search($needle, $this->arrCategory);
}
$this->arrCategory[] = $all_string;
if ($this->linkRenderType == 3) {
$this->arrCategoryWithRec[] = $all_string;
}
}
$this->selected = $needle;
$this->checkBoundries();
if (isset($cond)) {
if (!$this->isNumeric) {
$condition = ' ' . $this->fk . '=' . KT_escapeForSql($cond, "STRING_TYPE") . ' ';
} else {
$condition = ' ' . $this->fk . '=' . KT_escapeForSql($cond, "NUMERIC_TYPE") . ' ';
}
}
$condition = str_replace("%", "%%", $condition);
return $condition;
}
/**
* Prepares the delete SQL query to be executed
* @access protected
*/
function prepareSQL()
{
tNG_log::log('tNG_delete', 'prepareSQL', 'begin');
parent::prepareSQL();
// check if we have a valid primaryKey
if (!$this->primaryKey) {
$ret = new tNG_error('DEL_NO_PK_SET', array(), array());
}
// check the primary key value
if (!isset($this->primaryKeyColumn['value'])) {
$ret = new tNG_error('DEL_NO_PK_VAL', array(), array());
}
$ret = null;
$sql = 'DELETE FROM ' . $this->table . ' WHERE ' . KT_escapeFieldName($this->primaryKey) . ' = ';
$sql .= KT_escapeForSql($this->primaryKeyColumn['value'], $this->primaryKeyColumn['type']);
$this->setSQL($sql);
tNG_log::log('tNG_delete', 'prepareSQL', 'end');
return $ret;
}
/**
* transform the date value in a valid SQL condition; used for calculating the filter
* @param string column name;
* @param array column array information
* @param column value;
* @return string;
* @access public
*/
function prepareDateCondition($columnName, &$arr, $value)
{
$year = '';
$month = '';
$day = '';
$hour = '';
$min = '';
$sec = '';
$dateType = '';
$modifier = '';
$date1 = '';
$date2 = '';
$compareType1 = '';
$compareType2 = '';
$condJoin = '';
$cond = '';
$myDate = '';
$dateArr = array();
if (!isset($GLOBALS['KT_db_time_format_internal'])) {
KT_getInternalTimeFormat();
}
// extract modifier and date from value
if (preg_match('/^(<|>|=|<=|>=|=<|=>|<>|!=)\\s*\\d+.*$/', $value, $matches)) {
$modifier = trim($matches[1]);
$value = trim(substr($value, strlen($modifier)));
} elseif (preg_match('/^[^\\d]+/', $value)) {
$ret = '';
return $ret;
}
// prepare modifier for databases that do not support !=
if ($modifier == '!=') {
$modifier = '<>';
}
/* date pieces isolation */
// year only
if (preg_match('/^\\d+$/', $value)) {
$dateType = 'y';
$year = $value;
}
// year month
if (preg_match('/^\\d+[-\\/\\[\\]\\(\\)\\*\\|\\+\\.=,]{1}\\d+$/', $value)) {
$dateType = 'm';
$dateArr = preg_split('/([-\\/\\[\\]\\(\\)\\*\\|\\+\\.=,])/', $value, -1, PREG_SPLIT_NO_EMPTY);
$month = $dateArr[0];
$year = $dateArr[1];
if (strlen($month) > 2) {
$month = $dateArr[1];
$year = $dateArr[0];
}
}
// full date (year, month, day)
if (preg_match('/^\\d+[-\\/\\[\\]\\(\\)\\*\\|\\+\\.=,]{1}\\d+[-\\/\\[\\]\\(\\)\\*\\|\\+\\.=,]{1}\\d+$/', $value)) {
$dateType = 'd';
list($year, $month, $day) = $this->getDateParts($value);
}
// full date & hour
if (preg_match('/^\\d+[-\\/\\[\\]\\(\\)\\*\\|\\+\\.=,]{1}\\d+[-\\/\\[\\]\\(\\)\\*\\|\\+\\.=,]{1}\\d+\\s+\\d+[^\\d]*$/', $value)) {
$dateType = 'h';
$myParts = strpos($value, ' ');
$datePart = substr($value, 0, $myParts);
$timePart = substr($value, $myParts + 1);
list($year, $month, $day) = $this->getDateParts($datePart);
list($hour, $min, $sec) = $this->getTimeParts($timePart, 'HH');
}
// full date + hour, minutes
if (preg_match('/^\\d+[-\\/\\[\\]\\(\\)\\*\\|\\+\\.=,]{1}\\d+[-\\/\\[\\]\\(\\)\\*\\|\\+\\.=,]{1}\\d+\\s+\\d+:\\d+[^\\d]*$/', $value)) {
$dateType = 'i';
$myParts = strpos($value, ' ');
$datePart = substr($value, 0, $myParts);
$timePart = substr($value, $myParts + 1);
list($year, $month, $day) = $this->getDateParts($datePart);
list($hour, $min, $sec) = $this->getTimeParts($timePart, 'HH:ii');
}
// full date time
if (preg_match('/^\\d+[-\\/\\[\\]\\(\\)\\*\\|\\+\\.=,]{1}\\d+[-\\/\\[\\]\\(\\)\\*\\|\\+\\.=,]{1}\\d+\\s+\\d+:\\d+:\\d+[^\\d]*$/', $value)) {
$dateType = 's';
$myParts = strpos($value, ' ');
$datePart = substr($value, 0, $myParts);
$timePart = substr($value, $myParts + 1);
list($year, $month, $day) = $this->getDateParts($datePart);
list($hour, $min, $sec) = $this->getTimeParts($timePart, 'HH:ii:ss');
}
if ($dateType == '') {
$dateType = 't';
$value = KT_formatDate2DB($value);
}
/* prepare date parts */
// 1 or 2 digits year
if (preg_match('/^\\d{1,2}$/', $year)) {
if ($year < 70) {
$year = 2000 + $year;
} else {
$year = 1900 + $year;
}
}
if ($month < 1 || $month > 12) {
$month = '01';
}
if ($hour > 23) {
$hour = '00';
}
if ($min > 59) {
$min = '00';
}
if ($sec > 59) {
$sec = '00';
}
/* prepare condition operators based on modifiers */
switch ($modifier) {
case '>=':
$compareType1 = '>=';
$compareType2 = '';
$condJoin = '';
break;
case '<=':
$compareType1 = '';
$compareType2 = '<=';
$condJoin = '';
break;
case '<':
$compareType1 = '<';
$compareType2 = '';
$condJoin = '';
break;
case '>':
$compareType1 = '';
$compareType2 = '>';
$condJoin = '';
break;
case '<>':
$compareType1 = '<';
$compareType2 = '>';
$condJoin = 'OR';
break;
default:
$compareType1 = '>=';
$compareType2 = '<=';
$condJoin = 'AND';
break;
}
/* prepare dates for filtering */
switch ($dateType) {
case 'y':
$date1 = KT_convertDate($year . '-01-01', 'yyyy-mm-dd', $GLOBALS['KT_db_date_format']);
$date2 = KT_convertDate($year . '-12-31', 'yyyy-mm-dd', $GLOBALS['KT_db_date_format']);
break;
case 'm':
$date1 = KT_convertDate($year . '-' . $month . '-01', 'yyyy-mm-dd', $GLOBALS['KT_db_date_format']);
$maxday = KT_getDaysOfMonth($month, $year);
$date2 = KT_convertDate($year . '-' . $month . '-' . $maxday, 'yyyy-mm-dd', $GLOBALS['KT_db_date_format']);
break;
case 'd':
$date1 = KT_convertDate($year . '-' . $month . '-' . $day . ' 00:00:00', 'yyyy-mm-dd HH:ii:ss', $GLOBALS['KT_db_date_format'] . ' ' . $GLOBALS['KT_db_time_format_internal']);
$date2 = KT_convertDate($year . '-' . $month . '-' . $day . ' 23:59:59', 'yyyy-mm-dd HH:ii:ss', $GLOBALS['KT_db_date_format'] . ' ' . $GLOBALS['KT_db_time_format_internal']);
break;
case 'h':
$date1 = KT_convertDate($year . '-' . $month . '-' . $day . ' ' . $hour . ':00:00', 'yyyy-mm-dd HH:ii:ss', $GLOBALS['KT_db_date_format'] . ' ' . $GLOBALS['KT_db_time_format_internal']);
$date2 = KT_convertDate($year . '-' . $month . '-' . $day . ' ' . $hour . ':59:59', 'yyyy-mm-dd HH:ii:ss', $GLOBALS['KT_db_date_format'] . ' ' . $GLOBALS['KT_db_time_format_internal']);
break;
case 'i':
$date1 = KT_convertDate($year . '-' . $month . '-' . $day . ' ' . $hour . ':' . $min . ':00', 'yyyy-mm-dd HH:ii:ss', $GLOBALS['KT_db_date_format'] . ' ' . $GLOBALS['KT_db_time_format_internal']);
$date2 = KT_convertDate($year . '-' . $month . '-' . $day . ' ' . $hour . ':' . $min . ':59', 'yyyy-mm-dd HH:ii:ss', $GLOBALS['KT_db_date_format'] . ' ' . $GLOBALS['KT_db_time_format_internal']);
break;
case 's':
$date1 = KT_convertDate($year . '-' . $month . '-' . $day . ' ' . $hour . ':' . $min . ':' . $sec, 'yyyy-mm-dd HH:ii:ss', $GLOBALS['KT_db_date_format'] . ' ' . $GLOBALS['KT_db_time_format_internal']);
$date2 = KT_convertDate($year . '-' . $month . '-' . $day . ' ' . $hour . ':' . $min . ':' . $sec, 'yyyy-mm-dd HH:ii:ss', $GLOBALS['KT_db_date_format'] . ' ' . $GLOBALS['KT_db_time_format_internal']);
$compareType1 = '=';
$compareType2 = '';
$condJoin = '';
break;
case 't':
$date1 = $value;
$date2 = '';
$compareType1 = '=';
$compareType2 = '';
$condJoin = '';
break;
default:
$dateType = '';
$compareType1 = '';
$compareType2 = '';
$condJoin = '';
break;
}
if ($dateType != '') {
$cond = '(';
if ($compareType1 != '') {
$cond .= KT_escapeFieldName($columnName) . ' ' . $compareType1 . ' ' . KT_escapeForSql($date1, $arr['type']);
}
if ($compareType2 != '') {
if ($compareType1 != '') {
$cond .= ' ' . $condJoin . ' ';
}
$cond .= KT_escapeFieldName($columnName) . ' ' . $compareType2 . ' ' . KT_escapeForSql($date2, $arr['type']);
}
$cond .= ')';
}
return $cond;
}
/**
* Get the local recordset associated to this transaction
* @return object resource Recordset resource
* @access protected
*/
function getLocalRecordset()
{
tNG_log::log('tNG_multipleUpdate', 'getLocalRecordset');
$sql = '';
$tmpArr = $this->columns;
$tmpArr[$this->primaryKey]['type'] = $this->primaryKeyColumn['type'];
$tmpArr[$this->primaryKey]['method'] = $this->primaryKeyColumn['method'];
$tmpArr[$this->primaryKey]['reference'] = $this->primaryKeyColumn['reference'];
foreach ($tmpArr as $colName => $colDetails) {
if ($sql != '') {
$sql .= ',';
}
$sql .= KT_escapeFieldName($colName);
}
$sql .= ', ' . KT_escapeFieldName($this->primaryKey) . ' as ' . KT_escapeFieldName($this->pkName);
$sql = 'SELECT ' . $sql . ' FROM ' . $this->table;
$tmp_colValue = KT_getRealValue($this->primaryKeyColumn['method'], $this->primaryKeyColumn['reference'] . "_1");
$pkv = KT_getRealValue($this->primaryKeyColumn['method'], $this->primaryKeyColumn['reference']);
if (isset($tmp_colValue)) {
$sql = $sql . ' WHERE ' . KT_escapeFieldName($this->primaryKey) . ' IN (';
$sql = $sql . KT_escapeForSql($pkv, $this->primaryKeyColumn['type']);
$cnt = 1;
while (true) {
$tmp_colValue = KT_getRealValue($this->primaryKeyColumn['method'], $this->primaryKeyColumn['reference'] . "_" . $cnt++);
if (isset($tmp_colValue)) {
$sql = $sql . ", " . KT_escapeForSql($tmp_colValue, $this->primaryKeyColumn['type']);
} else {
break;
}
}
$sql = $sql . ')';
} else {
$sql = $sql . ' WHERE ' . KT_escapeFieldName($this->primaryKey) . '=';
$sql = $sql . KT_escapeForSql($pkv, $this->primaryKeyColumn['type']);
}
$rs = false;
if (isset($_SESSION['KT_lastUsedList']) && isset($_SESSION['sorter_tso_' . $_SESSION['KT_lastUsedList']])) {
$tmp_sql = $sql . ' ORDER BY ' . $_SESSION['sorter_tso_' . $_SESSION['KT_lastUsedList']];
$table_columns = array();
if (isset($this->connection->servermodel)) {
$res = $this->connection->Execute('SELECT * FROM ' . $this->table . ' LIMIT 1');
$table_columns = array_keys($res->fields);
} else {
$res = $this->connection->MetaColumns($this->table);
foreach ($res as $field => $col) {
$table_columns[] = $col->name;
}
}
$order_column = str_replace(' DESC', '', $_SESSION['sorter_tso_' . $_SESSION['KT_lastUsedList']]);
$order_column = explode('.', $order_column);
$order_column = $order_column[count($order_column) - 1];
if (in_array($order_column, $table_columns)) {
if (isset($this->connection->servermodel)) {
$rs = $this->connection->MySQL_Execute($tmp_sql);
} else {
$rs = $this->connection->Execute($tmp_sql);
}
}
}
if (!$rs) {
if (isset($this->connection->servermodel)) {
$rs = $this->connection->MySQL_Execute($sql);
} else {
$rs = $this->connection->Execute($sql);
}
}
if (!$rs) {
tNG_log::log('KT_ERROR');
$this->setError(new tNG_error('MUPD_RS', array(), array($this->connection->ErrorMsg(), $sql)));
echo $this->dispatcher->getErrorMsg();
exit;
}
return $rs;
}
/**
* Retrieve and store the saved values from database;
* @return string
* @access public
*/
function saveData()
{
tNG_log::log('tNG' . $this->transactionType, "saveData");
$keyName = $this->getPrimaryKey();
$keyValue = $this->getPrimaryKeyValue();
$keyType = $this->getColumnType($keyName);
$escapedKeyValue = KT_escapeForSql($keyValue, $keyType);
$sql = 'SELECT * FROM ' . $this->getTable() . ' WHERE ' . KT_escapeFieldName($keyName) . ' = ' . $escapedKeyValue;
$rs = $this->connection->Execute($sql);
if ($rs === false) {
return new tNG_error('FIELDS_SAVEDATA_ERROR', array(), array($sql, $this->connection->ErrorMsg()));
}
$this->savedData = $rs->fields;
return null;
}
/**
* Function KT_DynamicData replace all the dynamic data with their values;
* @param string $expression The expression to be evaluated
* @param object or null $tNG The tNG context in which the expression is evaluated
* @param string $escapeMethod The string escape method for the evaluated values (rawurlencode and SQL)
* @param booolean $useSavedData Weather to use the current tNG data or the saved values
* @param array $extraParams Extra expression parameters passed when for evaluation (of form $key => $value; any encounter of key will be replaced with its value)
* @return string the string with the dynamic data replaced with their values;
*/
function KT_DynamicData($expression, $tNG, $escapeMethod = '', $useSavedData = false, $extraParams = array(), $errorIfNotFound = true)
{
$PB = '{';
$PE = '}';
if (!is_string($expression)) {
return $expression;
}
// DynamicData functions - use this to define more functions
KT_getInternalTimeFormat();
$date_now = KT_convertDate(date('Y-m-d'), "yyyy-mm-dd", $GLOBALS['KT_screen_date_format']);
$date_dt_now = KT_convertDate(date('Y-m-d H:i:s'), "yyyy-mm-dd HH:ii:ss", $GLOBALS['KT_screen_date_format'] . ' ' . $GLOBALS['KT_screen_time_format_internal']);
$date_t_now = KT_convertDate(date('H:i:s'), "HH:ii:ss", $GLOBALS['KT_screen_time_format_internal']);
$dynamicDataFunctions = array('NOW()' => $date_now, 'now()' => $date_now, 'NOW' => $date_now, 'now' => $date_now, 'NOW_DT()' => $date_dt_now, 'now_dt()' => $date_dt_now, 'NOW_DT' => $date_dt_now, 'now_dt' => $date_dt_now, 'NOW_T()' => $date_t_now, 'now_t()' => $date_t_now, 'NOW_T' => $date_t_now, 'now_t' => $date_t_now, 'KT_REFERRER' => isset($_SERVER['HTTP_REFERER']) ? $_SERVER['HTTP_REFERER'] : '', 'kt_referrer' => isset($_SERVER['HTTP_REFERER']) ? $_SERVER['HTTP_REFERER'] : '', 'KT_CSV_LINE' => isset($GLOBALS['KT_CSV_LINE']) ? $GLOBALS['KT_CSV_LINE'] : '', 'KT_XML_LINE' => isset($GLOBALS['KT_XML_LINE']) ? $GLOBALS['KT_XML_LINE'] : '');
$placeholdersArr = KT_getReplacementsFromMessage($expression);
$replacementsArr = array();
switch ($escapeMethod) {
case 'rawurlencode':
break;
case 'expression':
break;
case 'SQL':
if (!isset($tNG)) {
$escapeMethod = false;
}
break;
default:
$escapeMethod = false;
break;
}
if ($useSavedData !== true) {
$useSavedData = false;
}
foreach ($placeholdersArr as $key => $placeholder) {
if (array_key_exists($placeholder, $extraParams)) {
// extra params have priority 1
$placeholderType = 'tng_ddextra';
$placeholderName = $placeholder;
} else {
// functions have priority 2
if (array_key_exists($placeholder, $dynamicDataFunctions)) {
$placeholderType = 'tNG_DDfunction';
$placeholderName = $placeholder;
} else {
$ptpos = strpos($placeholder, '.');
if (!$ptpos) {
// tng field
if (isset($tNG)) {
// attached to a tng, replace field with value
$placeholderType = 'tNG_tNGfield';
$placeholderName = $placeholder;
} else {
// no tng, leave as is
$placeholderType = 'tNG_tNGfieldLater';
$placeholderName = $placeholder;
}
} else {
$placeholderType = substr($placeholder, 0, $ptpos);
$placeholderName = substr($placeholder, $ptpos + 1);
}
}
}
$placeholder = $PB . $placeholder . $PE;
switch (strtolower($placeholderType)) {
case 'tng_ddfunction':
$replacementsArr[$placeholder] = $dynamicDataFunctions[$placeholderName];
break;
case 'tng_ddextra':
$replacementsArr[$placeholder] = $extraParams[$placeholderName];
break;
case 'tng_tngfield':
if ($useSavedData) {
$placeholderValue = $tNG->getSavedValue($placeholderName);
} else {
if (isset($tNG->columns[$placeholderName]) || $placeholderName == $tNG->getPrimaryKey()) {
$placeholderValue = $tNG->getColumnValue($placeholderName);
$placeholderType = $tNG->getColumnType($placeholderName);
} else {
if ($errorIfNotFound == true) {
die('KT_DynamicData:<br />Column ' . $placeholderName . ' is not part of the current transaction.');
} else {
$placeholderValue = $placeholder;
}
}
if ($escapeMethod == 'SQL') {
$placeholderValue = KT_escapeForSql($placeholderValue, $placeholderType);
}
}
$replacementsArr[$placeholder] = $placeholderValue;
break;
case 'tng_tngfieldlater':
break;
case 'get':
$myPlaceholderName = $placeholderName;
if (isset($tNG)) {
if (isset($tNG->multipleIdx)) {
$myPlaceholderName .= "_" . $tNG->multipleIdx;
}
}
$replacementsArr[$placeholder] = KT_getRealValue("GET", $myPlaceholderName);
if (!isset($replacementsArr[$placeholder])) {
$replacementsArr[$placeholder] = KT_getRealValue("GET", $placeholderName);
}
break;
case 'post':
$myPlaceholderName = $placeholderName;
if (isset($tNG)) {
if (isset($tNG->multipleIdx)) {
$myPlaceholderName .= "_" . $tNG->multipleIdx;
}
}
$replacementsArr[$placeholder] = KT_getRealValue("POST", $myPlaceholderName);
if (!isset($replacementsArr[$placeholder])) {
$replacementsArr[$placeholder] = KT_getRealValue("POST", $placeholderName);
}
break;
case 'cookie':
$replacementsArr[$placeholder] = KT_getRealValue("COOKIE", $placeholderName);
break;
case 'session':
KT_session_start();
$replacementsArr[$placeholder] = KT_getRealValue("SESSION", $placeholderName);
break;
case 'globals':
$replacementsArr[$placeholder] = KT_getRealValue("GLOBALS", $placeholderName);
break;
case 'request':
$replacementsArr[$placeholder] = KT_getRealValue("GLOBALS", $placeholderName);
break;
case 'server':
$replacementsArr[$placeholder] = KT_getRealValue("SERVER", $placeholderName);
break;
case 'application':
// CF only
break;
case 'csv':
$replacementsArr[$placeholder] = KT_getRealValue("CSV", $placeholderName);
break;
default:
// recordset
if (isset($GLOBALS[$placeholderType])) {
$rs = $GLOBALS[$placeholderType];
if (is_resource($rs)) {
$placeholderValue = $GLOBALS["row_" . $placeholderType][$placeholderName];
} elseif (is_object($rs)) {
$placeholderValue = $rs->Fields($placeholderName);
} else {
break;
}
} else {
$placeholderValue = $placeholder;
}
$replacementsArr[$placeholder] = $placeholderValue;
break;
}
}
reset($replacementsArr);
if ($escapeMethod == 'rawurlencode') {
if (!array_key_exists("{kt_login_redirect}", $replacementsArr) && !array_key_exists("{kt_referrer}", $replacementsArr) && !array_key_exists("{KT_REFERRER}", $replacementsArr)) {
$replacementsArr = array_map($escapeMethod, $replacementsArr);
}
} elseif ($escapeMethod == 'expression') {
$replacementsArr = array_map('KT_escapeExpression', $replacementsArr);
}
$newexpression = str_replace(array_keys($replacementsArr), array_values($replacementsArr), $expression);
/*if ($escapeMethod == 'expression') {
echo $newexpression."\n<br/>\n";
}*/
return $newexpression;
}
function getCondition()
{
$other_string = "other";
$number_string = "0_9";
$all_string = "all";
$allowed = range('A', 'Z');
$numbers_allowed = array('0', '1', '2', '3', '4', '5', '6', '7', '8', '9');
$condition = '1=1';
$arr = array();
$rs = $this->connection->Execute('SELECT DISTINCT ' . $this->fieldName . ' FROM ' . $this->tableName);
if ($rs) {
while (!$rs->EOF) {
$needle = strtoupper(substr($rs->Fields($this->fieldName), 0, 1));
if (in_array($needle, $allowed)) {
if (!in_array($needle, $arr)) {
$arr[] = $needle;
}
} elseif ($this->useNumbers === true && in_array($needle, $numbers_allowed)) {
$this->hasNumbers = true;
} else {
$this->hasOther = true;
}
$rs->MoveNext();
}
natsort($arr);
$this->arrLetters = $arr;
}
if ($this->hasNumbers) {
$this->arrLetters[] = $number_string;
}
if ($this->hasOther) {
$this->arrLetters[] = $other_string;
}
if (count($this->arrLetters) > 0) {
$this->arrLetters[] = $all_string;
}
$other_selected = false;
$numbers_selected = false;
$all_selected = false;
if (isset($_GET['KT_az'])) {
$cond = KT_getRealValue("GET", "KT_az");
if (strtolower($cond) == $other_string) {
$other_selected = true;
}
if (strtolower($cond) == $number_string) {
$numbers_selected = true;
}
if (strtolower($cond) == $all_string) {
$all_selected = true;
}
} else {
if (count($this->arrLetters) > 0) {
$cond = array_shift($arr);
}
}
$this->selected = $cond;
if ($numbers_selected) {
$this->selected = $number_string;
}
if ($other_selected) {
$this->selected = $other_string;
}
if ($all_selected) {
$this->selected = $all_string;
$cond = null;
}
$this->checkBoundries();
if (isset($cond)) {
if (!$other_selected && !$numbers_selected) {
$condition = ' (' . $this->fieldName . ' LIKE ' . strtoupper(KT_escapeForSql($cond . "%", "STRING_TYPE")) . ' OR ' . $this->fieldName . ' LIKE ' . strtolower(KT_escapeForSql($cond . "%", "STRING_TYPE")) . ') ';
} elseif ($numbers_selected) {
$condition = ' (';
for ($i = 0; $i < count($numbers_allowed); $i++) {
if ($i != 0) {
$condition .= ' OR ';
}
$condition .= $this->fieldName . ' LIKE \'' . $numbers_allowed[$i] . '%\'';
}
$condition .= ') ';
} else {
$condition = ' (';
$tmp_arr = $allowed;
if ($this->useNumbers) {
for ($i = 0; $i < count($numbers_allowed); $i++) {
$tmp_arr[] = $numbers_allowed[$i];
}
}
for ($i = 0; $i < count($tmp_arr); $i++) {
if ($i != 0) {
$condition .= ' AND ';
}
$condition .= $this->fieldName . ' NOT LIKE \'' . $tmp_arr[$i] . '%\'';
}
$condition .= ') ';
}
}
$condition = str_replace("%", "%%", $condition);
return $condition;
}
/**
* Get the local recordset associated to this transaction
* @return object resource Recordset resource
* @access protected
*/
function getLocalRecordset()
{
tNG_log::log('tNG_insert', 'getLocalRecordset');
$fakeArr = array();
$tmpArr = $this->columns;
if (!isset($tmpArr[$this->primaryKey])) {
$tmpArr[$this->primaryKey] = $this->primaryKeyColumn;
$tmpArr[$this->primaryKey]['default'] = NULL;
}
foreach ($tmpArr as $colName => $colDetails) {
$tmpVal = KT_escapeForSql($colDetails['default'], $colDetails['type'], true);
$fakeArr[$colName] = $tmpVal;
}
return $this->getFakeRecordset($fakeArr);
}
/**
* Executes all sub-transactions
* @access protected
*/
function prepareSQL()
{
tNG_log::log('tNG_import', 'prepareSQL', 'begin');
$ret = $this->prepareData();
if ($ret === null) {
$this->noSuccess = 0;
$this->noSkip = 0;
$failed = false;
$line = $this->lineStart;
$tNGindex = 1;
for ($k = 0; $k < count($this->data); $k++) {
$dataarr = $this->data[$k];
$skipped = false;
$line++;
/*
if ( !is_array($dataarr) || count($dataarr) < 1 || (count($dataarr) == 1 && reset($dataarr) == '') ) {
// skip empty lines
continue;
}
*/
// exports the values line to be available for KT_getRealValue and KT_DynamicData
unset($GLOBALS[$this->importReference]);
$GLOBALS[$this->importReference] = $dataarr;
unset($GLOBALS[$this->importReference . '_LINE']);
$GLOBALS[$this->importReference . '_LINE'] = $line;
$isInsert = true;
$uniqueColName = $this->uniqueKey;
if ($uniqueColName != '') {
$uniqueColDetails = $this->computeMultipleValues($this->columns[$uniqueColName], $tNGindex);
if ($uniqueColDetails['value'] != '') {
$sql = 'SELECT ' . KT_escapeFieldName($uniqueColName) . ' FROM ' . $this->getTable() . ' WHERE ' . KT_escapeFieldName($uniqueColName) . ' = ' . KT_escapeForSql($uniqueColDetails['value'], $uniqueColDetails['type']);
$rs = $this->connection->Execute($sql);
if ($rs === false) {
$failed = true;
$ret = new tNG_error('IMPORT_SQL_ERROR', array(), array($sql, $this->connection->ErrorMsg()));
tNG_log::log('KT_ERROR');
break;
}
if ($rs->recordCount() >= 1) {
// duplicates found
if ($this->handleDuplicates == "SKIP") {
// ignore case
$isInsert = false;
$this->noSkip++;
continue;
}
if ($this->handleDuplicates == "UPDATE") {
// update case
$isInsert = false;
$this->multTNGs[$tNGindex - 1] = new tNG_update($this->connection);
}
if ($this->handleDuplicates == "SKIPWITHERROR") {
// throw error case
$isInsert = false;
$skipped = true;
$this->noSkip++;
$this->multTNGs[$tNGindex - 1] = new tNG_insert($this->connection);
$this->multTNGs[$tNGindex - 1]->setError(new tNG_error($this->importType . '_IMPORT_DUPLICATE_ERROR', array($line, $uniqueColDetails['value'], $uniqueColName), array()));
}
}
}
}
if ($isInsert) {
$this->multTNGs[$tNGindex - 1] = new tNG_insert($this->connection);
}
$this->multTNGs[$tNGindex - 1]->setDispatcher($this->dispatcher);
$this->multTNGs[$tNGindex - 1]->multipleIdx = $tNGindex;
// register triggers
for ($j = 0; $j < sizeof($this->multTriggers); $j++) {
call_user_func_array(array(&$this->multTNGs[$tNGindex - 1], "registerConditionalTrigger"), $this->multTriggers[$j]);
}
$this->multTNGs[$tNGindex - 1]->setTable($this->table);
// add columns
foreach ($this->columns as $colName => $colDetails) {
$colDetails = $this->computeMultipleValues($colDetails, $tNGindex);
$this->columns[$colName]['value'] = $colDetails['value'];
if ($this->multTNGs[$tNGindex - 1]->transactionType == '_update') {
if ($colName != $uniqueColName) {
$this->multTNGs[$tNGindex - 1]->addColumn($colName, $colDetails['type'], $colDetails['method'], $colDetails['reference']);
}
} else {
$this->multTNGs[$tNGindex - 1]->addColumn($colName, $colDetails['type'], $colDetails['method'], $colDetails['reference'], $colDetails['default']);
}
}
if ($this->multTNGs[$tNGindex - 1]->transactionType == '_update') {
$this->multTNGs[$tNGindex - 1]->setPrimaryKey($uniqueColName, $uniqueColDetails['type'], 'VALUE', $uniqueColDetails['value']);
} else {
$this->multTNGs[$tNGindex - 1]->setPrimaryKey($this->primaryKey, $this->primaryKeyColumn['type']);
}
$this->multTNGs[$tNGindex - 1]->compileColumnsValues();
if ($this->getError()) {
$this->multTNGs[$tNGindex - 1]->setError($this->getError());
}
$this->multTNGs[$tNGindex - 1]->setStarted(true);
$this->multTNGs[$tNGindex - 1]->doTransaction();
if (!$skipped) {
if ($this->multTNGs[$tNGindex - 1]->getError()) {
$err = $this->multTNGs[$tNGindex - 1]->getError();
$tmp_all_errmsg = '';
$tmp_unique_details = '';
if ($uniqueColName != '') {
if ($uniqueColDetails['value'] != '') {
$tmp_unique_details = ' (' . $uniqueColName . ' = ' . $uniqueColDetails['value'] . ')';
}
}
foreach ($err->fieldErrors as $tmp_col => $tmp_errmsg) {
$tmp_all_errmsg .= "\n<br /> - " . $tmp_col . " : " . $tmp_errmsg;
}
if ($tmp_all_errmsg == '') {
$tmp_all_errmsg = $err->getDetails();
}
$lineErr = $line . $tmp_unique_details;
$newErr = new tNG_error($this->importType . '_IMPORT_LINE_ERROR', array($lineErr, $tmp_all_errmsg), array());
$this->multTNGs[$tNGindex - 1]->setError($newErr);
$failed = true;
} else {
$this->noSuccess++;
if ($this->getPrimaryKey() == $this->multTNGs[$tNGindex - 1]->getPrimaryKey()) {
$this->primaryKeyColumn['value'] = $this->multTNGs[$tNGindex - 1]->getPrimaryKeyValue();
}
}
}
$tNGindex++;
}
if (!$failed) {
for ($i = 0; $i < sizeof($this->multTNGs); $i++) {
if ($this->multTNGs[$i]->getError()) {
$failed = true;
$ret = new tNG_error('IMPORT_SKIPPED', array(), array());
tNG_log::log('KT_ERROR');
break;
}
}
}
if ($failed) {
if ($ret === null) {
$ret = new tNG_error('IMPORT_ERROR', array(), array());
tNG_log::log('KT_ERROR');
}
if ($this->executeSubSets === false) {
for ($i = 0; $i < sizeof($this->multTNGs); $i++) {
if (!$this->multTNGs[$i]->getError()) {
$this->multTNGs[$i]->setError($ret);
$this->multTNGs[$i]->executeTriggers('ERROR');
}
}
}
}
if ($this->executeSubSets === false) {
$this->noSuccess = 0;
}
} else {
tNG_log::log('KT_ERROR');
}
tNG_log::log('tNG_import', 'prepareSQL', 'end');
return $ret;
}
function UpdateOrder($id, $order)
{
$sql = 'UPDATE ' . $this->tableName . ' SET ' . KT_escapeFieldName($this->orderField) . ' = ' . KT_escapeForSql($order, "NUMERIC_TYPE") . ' WHERE ' . KT_escapeFieldName($this->pk) . ' = ' . KT_escapeForSql($id, $this->pkType);
$this->connection->Execute($sql) or die("Internal Error. Table Order:<br/>\n" . $this->connection->ErrorMsg());
}
/**
* contruct the SQL and execute it. it is using as value for the field the primarey key value from the transaction;
* return mix null or error object;
* @access public
*/
function Execute()
{
$pk_value = $this->tNG->getPrimaryKeyValue();
$pk_type = $this->tNG->getColumnType($this->tNG->getPrimaryKey());
$pk_value = KT_escapeForSql($pk_value, $pk_type);
if (count($this->fileRenameRule) > 0 || count($this->folderRenameRule) > 0) {
$sql = 'SELECT * FROM ' . $this->table . ' WHERE ' . KT_escapeFieldName($this->field) . " = " . $pk_value;
$rs = $this->tNG->connection->Execute($sql);
if ($rs === false) {
return new tNG_error('DEL_DR_SQL_ERROR', array(), array($this->tNG->connection->ErrorMsg(), $sql));
}
if ($rs->RecordCount() == 0) {
return null;
}
}
// prepare to delete files
if (count($this->fileRenameRule) > 0) {
$fullFileName = array();
$fullFileNameFolder = array();
for ($i = 0; $i < count($this->fileRenameRule); $i++) {
while (!$rs->EOF) {
$arr = array();
foreach ($rs->fields as $col => $value) {
$arr[$col] = $value;
}
$folder = $this->fileFolder[$i];
$fileName = KT_DynamicData($this->fileRenameRule[$i], $this->tNG, '', false, $arr);
// security
if (substr(KT_realpath($folder . $fileName), 0, strlen($folder)) != $folder) {
$baseFileName = dirname(KT_realpath($folder . $fileName, false));
$ret = new tNG_error("FOLDER_DEL_SECURITY_ERROR", array(), array($baseFileName, $folder));
return $ret;
}
$fullFileName[] = $fileName;
$fullFileNameFolder[] = $folder;
$rs->MoveNext();
}
$rs->MoveFirst();
}
}
// prepare to delete related folders
if (count($this->folderRenameRule) > 0) {
$relatedFolder = array();
for ($i = 0; $i < count($this->folderRenameRule); $i++) {
while (!$rs->EOF) {
$arr = array();
foreach ($rs->fields as $col => $value) {
$arr[$col] = $value;
}
$folder = $this->folder[$i];
$f = KT_DynamicData($this->folderRenameRule[$i], $this->tNG, '', false, $arr);
// security
if (substr(KT_realpath($folder . $f), 0, strlen($folder)) != $folder) {
$baseFileName = dirname(KT_realpath($folder . $f, false));
$ret = new tNG_error("FOLDER_DEL_SECURITY_ERROR", array(), array($baseFileName, $folder));
return $ret;
}
$relatedFolder[] = $folder . $f;
$rs->MoveNext();
}
$rs->MoveFirst();
}
}
// delete reocords
$sql = "DELETE FROM " . $this->table . " WHERE " . KT_escapeFieldName($this->field) . " = " . $pk_value;
$ret = $this->tNG->connection->Execute($sql);
if ($ret === false) {
return new tNG_error('DEL_DR_SQL_ERROR', array(), array($this->tNG->connection->ErrorMsg(), $sql));
}
// delete files
if (count($this->fileRenameRule) > 0) {
for ($i = 0; $i < count($fullFileName); $i++) {
if (file_exists($fullFileNameFolder[$i] . $fullFileName[$i])) {
$delRet = @unlink($fullFileNameFolder[$i] . $fullFileName[$i]);
$path_info = KT_pathinfo($fullFileNameFolder[$i] . $fullFileName[$i]);
$this->deleteThumbnails($path_info['dirname'] . '/thumbnails/', $path_info['basename']);
}
}
}
// delete related folder
if (count($this->folderRenameRule) > 0) {
for ($i = 0; $i < count($relatedFolder); $i++) {
$folder = new KT_Folder();
// delete thumbnails
$folder->deleteFolderNR($relatedFolder[$i]);
}
}
return null;
}
/**
* Return the values for extra columns to use in insert/update SQL;
* Only for PRO version
* @param string foreign key value
* @param array selected values
* @return array
* @access public
*/
function getExtraColumnsValues($fk, $insertValues)
{
$arr = array();
if (!in_array($fk, $insertValues)) {
return $arr;
}
if (count($this->columns) > 0) {
$arr['cols'] = array();
$arr['values'] = array();
$arr['update'] = array();
$fkReference = $this->fkReference;
$idxReference = "";
if (isset($this->tNG->multipleIdx)) {
$idxReference = '_' . $this->tNG->multipleIdx;
$idxReference = preg_quote($idxReference, '/');
}
$fkReference = preg_quote($fkReference, '/');
foreach ($this->columns as $colName => $arrTmp) {
$arr['cols'][] = KT_escapeFieldName($colName);
if ($arrTmp['method'] == 'VALUE') {
$arr['values'][] = KT_escapeForSql($arrTmp['value'], $arrTmp['type'], false);
$arr['update'][] = KT_escapeFieldName($colName) . '=' . $arr['values'][count($arr['values']) - 1];
} else {
$found = false;
foreach ($_POST as $key => $val) {
if (preg_match('/^' . $fkReference . '_' . $colName . '_' . $fk . $idxReference . '$/', $key)) {
if ($arrTmp['type'] == 'DATE_TYPE') {
$val = KT_formatDate2DB($val);
}
$arr['values'][] = KT_escapeForSql($val, $arrTmp['type'], false);
$arr['update'][] = KT_escapeFieldName($colName) . '=' . $arr['values'][count($arr['values']) - 1];
$found = true;
break;
}
}
if (!$found && $this->columns[$colName]['default'] != '') {
$val = KT_DynamicData($this->columns[$colName]['default'], null);
if ($this->columns[$colName]['type'] == 'DATE_TYPE') {
$val = KT_formatDate2DB($val);
}
$arr['values'][] = KT_escapeForSql($val, $arrTmp['type'], false);
$arr['update'][] = KT_escapeFieldName($colName) . '=' . $arr['values'][count($arr['values']) - 1];
}
}
}
}
return $arr;
}
/**
* Get the local recordset associated to this transaction
* @return object resource Recordset resource
* @access public
*/
function getLocalRecordset()
{
tNG_log::log('tNG_update', 'getLocalRecordset');
$sql = '';
$tmpArr = $this->columns;
$tmpArr[$this->primaryKey]['type'] = $this->primaryKeyColumn['type'];
$tmpArr[$this->primaryKey]['method'] = $this->primaryKeyColumn['method'];
$tmpArr[$this->primaryKey]['reference'] = $this->primaryKeyColumn['reference'];
foreach ($tmpArr as $colName => $colDetails) {
if ($sql != '') {
$sql .= ',';
}
$sql .= KT_escapeFieldName($colName);
}
$sql .= ', ' . KT_escapeFieldName($this->primaryKey) . ' as ' . KT_escapeFieldName($this->pkName);
$sql = 'SELECT ' . $sql . ' FROM ' . $this->table;
$sql = $sql . ' WHERE ' . KT_escapeFieldName($this->primaryKey) . ' =';
$pkValue = KT_getRealValue($this->primaryKeyColumn['method'], $this->primaryKeyColumn['reference']);
$sql = $sql . KT_escapeForSql($pkValue, $this->primaryKeyColumn['type']);
if (isset($this->connection->servermodel)) {
$rs = $this->connection->MySQL_Execute($sql);
} else {
$rs = $this->connection->Execute($sql);
}
if (!$rs) {
tNG_log::log('KT_ERROR');
$this->setError(new tNG_error('UPD_RS', array(), array($this->connection->ErrorMsg(), $sql)));
echo $this->dispatcher->getErrorMsg();
exit;
}
return $rs;
}
function sortList($primaryKeyValue, $foreignKeyValue, $over_primaryKeyValue, $insert_position)
{
if ($insert_position != "before" && $insert_position != "after") {
$insert_position = "before";
}
require_once realpath(dirname(__FILE__) . '/' . '/../../../../Connections/' . $this->connectionName . '.php');
$hostname = 'MM_' . $this->connectionName . '_HOSTNAME';
$connWrap = null;
if (empty($GLOBALS[$hostname])) {
// we are on mysql
// Make unified connection variable
$database = 'database_' . $this->connectionName;
$connWrap = new KT_Connection($GLOBALS[$this->connectionName], $GLOBALS[$database]);
} else {
$connWrap = $GLOBALS[$this->connectionName];
}
// GET CURRENT ORDER VALUE
$sql = 'SELECT ' . KT_escapeFieldName($this->orderFieldName) . ' FROM ' . $this->tableName . ' WHERE ' . KT_escapeFieldName($this->primaryKey) . ' = ' . KT_escapeForSql($primaryKeyValue, "NUMERIC_TYPE");
$rs = $connWrap->Execute($sql);
if ($rs === false) {
return array('error' => array('code' => 'SQL Error', 'message' => 'select current order failed: ' . $connWrap->ErrorMsg()));
}
// UPDATE ORDER VALUE IF CURRENT IS NULL
if (is_null($rs->Fields($this->orderFieldName))) {
// update order to the max + 1 value
$sql = 'SELECT MAX(' . KT_escapeFieldName($this->orderFieldName) . ')+1 as max_order' . ' FROM ' . $this->tableName;
$rs = $connWrap->Execute($sql);
if ($rs === false) {
return array('error' => array('code' => 'SQL Error', 'message' => 'select max order failed: ' . $connWrap->ErrorMsg()));
}
$max_order = (int) $rs->Fields("max_order");
$sql = 'UPDATE ' . $this->tableName . ' SET ' . KT_escapeFieldName($this->orderFieldName) . ' = ' . KT_escapeForSql($max_order, "NUMERIC_TYPE") . ' WHERE ' . KT_escapeFieldName($this->primaryKey) . ' = ' . KT_escapeForSql($primaryKeyValue, "NUMERIC_TYPE");
$rs = $connWrap->Execute($sql);
if ($rs === false) {
return array('error' => array('code' => 'SQL Error', 'message' => 'update order value failed: ' . $connWrap->ErrorMsg()));
}
return "OK";
}
$currentOrderValue = (int) $rs->Fields($this->orderFieldName);
// GET TARGET POSITION
$insert_as_min = false;
$insert_as_max = false;
if (!isset($over_primaryKeyValue) || $over_primaryKeyValue == "") {
if ($insert_position == "before") {
$sql = 'SELECT MIN(' . KT_escapeFieldName($this->orderFieldName) . ') as target_order';
$insert_as_min = true;
} else {
$sql = 'SELECT MAX(' . KT_escapeFieldName($this->orderFieldName) . ') as target_order';
$insert_as_max = true;
}
$sql .= ' FROM ' . $this->tableName . ' WHERE ' . KT_escapeFieldName($this->primaryKey) . ' != ' . KT_escapeForSql($primaryKeyValue, "NUMERIC_TYPE");
if (isset($this->foreignKey)) {
if (isset($foreignKeyValue) && $foreignKeyValue != "") {
$sql .= ' AND ' . KT_escapeFieldName($this->foreignKey) . ' = ' . KT_escapeForSql($foreignKeyValue, "NUMERIC_TYPE");
} else {
$sql .= ' AND ' . KT_escapeFieldName($this->foreignKey) . ' is null OR ' . KT_escapeFieldName($this->foreignKey) . '=0';
}
}
$rs = $connWrap->Execute($sql);
if ($rs === false) {
return array('error' => array('code' => 'SQL Error', 'message' => 'select target order failed: ' . $connWrap->ErrorMsg()));
}
if ($rs->EOF) {
// keep the current value for order, as there are no other items in the category
return 'OK';
}
$targetOrderValue = (int) $rs->Fields("target_order");
} else {
$sql = 'SELECT ' . KT_escapeFieldName($this->orderFieldName) . ' FROM ' . $this->tableName . ' WHERE ' . KT_escapeFieldName($this->primaryKey) . ' = ' . KT_escapeForSql($over_primaryKeyValue, "NUMERIC_TYPE");
$rs = $connWrap->Execute($sql);
if ($rs === false) {
return array('error' => array('code' => 'SQL Error', 'message' => 'select targeted order failed: ' . $connWrap->ErrorMsg()));
}
$targetOrderValue = (int) $rs->Fields($this->orderFieldName);
if ($insert_position == "after") {
if ($currentOrderValue > $targetOrderValue) {
$sql = 'SELECT ' . KT_escapeFieldName($this->orderFieldName) . ' FROM ' . $this->tableName . ' WHERE ' . KT_escapeFieldName($this->orderFieldName) . ' > ' . KT_escapeForSql($targetOrderValue, "NUMERIC_TYPE") . ' ORDER BY ' . KT_escapeFieldName($this->orderFieldName) . ' ASC';
$rs = $connWrap->Execute($sql);
if ($rs === false) {
return array('error' => array('code' => 'SQL Error', 'message' => 'select targeted order value failed: ' . $connWrap->ErrorMsg()));
}
if (!$rs->EOF) {
$targetOrderValue = (int) $rs->Fields($this->orderFieldName);
}
}
} else {
if ($currentOrderValue < $targetOrderValue) {
$sql = 'SELECT ' . KT_escapeFieldName($this->orderFieldName) . ' FROM ' . $this->tableName . ' WHERE ' . KT_escapeFieldName($this->orderFieldName) . ' < ' . KT_escapeForSql($targetOrderValue, "NUMERIC_TYPE") . ' ORDER BY ' . KT_escapeFieldName($this->orderFieldName) . ' DESC';
$rs = $connWrap->Execute($sql);
if ($rs === false) {
return array('error' => array('code' => 'SQL Error', 'message' => 'select targeted order value failed: ' . $connWrap->ErrorMsg()));
}
if (!$rs->EOF) {
$targetOrderValue = (int) $rs->Fields($this->orderFieldName);
}
}
}
}
if ($currentOrderValue < $targetOrderValue) {
if (!$insert_as_min) {
// if the order field has unique key set on it, must assure thare are no duplicates in order field
// get the max + 1 value
$sql = 'SELECT MAX(' . KT_escapeFieldName($this->orderFieldName) . ')+1 as max_order' . ' FROM ' . $this->tableName;
$rs = $connWrap->Execute($sql);
if ($rs === false) {
return array('error' => array('code' => 'SQL Error', 'message' => 'assure unique order: select max order failed: ' . $connWrap->ErrorMsg()));
}
$max_order = (int) $rs->Fields("max_order");
// add max+1 value to all the items that need to be shift
$sql = 'UPDATE ' . $this->tableName . ' SET ' . KT_escapeFieldName($this->orderFieldName) . '=' . KT_escapeFieldName($this->orderFieldName) . '+ ' . $max_order . ' WHERE ' . KT_escapeFieldName($this->orderFieldName) . ' <= ' . $targetOrderValue . ' AND ' . KT_escapeFieldName($this->orderFieldName) . ' > ' . $currentOrderValue;
$rs = $connWrap->Execute($sql);
if ($rs === false) {
return array('error' => array('code' => 'SQL Error', 'message' => 'shift order values: ' . $connWrap->ErrorMsg()));
}
// place current item to its final position
$sql = 'UPDATE ' . $this->tableName . ' SET ' . KT_escapeFieldName($this->orderFieldName) . '=' . $targetOrderValue . ' WHERE ' . KT_escapeFieldName($this->primaryKey) . ' = ' . KT_escapeForSql($primaryKeyValue, "NUMERIC_TYPE");
$rs = $connWrap->Execute($sql);
if ($rs === false) {
return array('error' => array('code' => 'SQL Error', 'message' => 'update item position: ' . $connWrap->ErrorMsg()));
}
// substract (max+2) from all the items that were previously shift
$sql = 'UPDATE ' . $this->tableName . ' SET ' . KT_escapeFieldName($this->orderFieldName) . '=' . KT_escapeFieldName($this->orderFieldName) . ' - ' . ($max_order + 1) . ' WHERE ' . KT_escapeFieldName($this->orderFieldName) . ' >= ' . $max_order;
$rs = $connWrap->Execute($sql);
if ($rs === false) {
return array('error' => array('code' => 'SQL Error', 'message' => 'shift back order values: ' . $connWrap->ErrorMsg()));
}
}
}
if ($currentOrderValue > $targetOrderValue) {
if (!$insert_as_max) {
// if the order field has unique key set on it, must assure thare are no duplicates in order field
// get the max + 1 value
$sql = 'SELECT MAX(' . KT_escapeFieldName($this->orderFieldName) . ')+1 as max_order' . ' FROM ' . $this->tableName;
$rs = $connWrap->Execute($sql);
if ($rs === false) {
return array('error' => array('code' => 'SQL Error', 'message' => 'assure unique order: select max order failed: ' . $connWrap->ErrorMsg()));
}
$max_order = (int) $rs->Fields("max_order");
// add max+1 value to all the items that need to be shift
$sql = 'UPDATE ' . $this->tableName . ' SET ' . KT_escapeFieldName($this->orderFieldName) . '=' . KT_escapeFieldName($this->orderFieldName) . '+ ' . $max_order . ' WHERE ' . KT_escapeFieldName($this->orderFieldName) . ' >= ' . $targetOrderValue . ' AND ' . KT_escapeFieldName($this->orderFieldName) . ' < ' . $currentOrderValue;
$rs = $connWrap->Execute($sql);
if ($rs === false) {
return array('error' => array('code' => 'SQL Error', 'message' => 'shift order values: ' . $connWrap->ErrorMsg()));
}
// place current item to its final position
$sql = 'UPDATE ' . $this->tableName . ' SET ' . KT_escapeFieldName($this->orderFieldName) . '=' . $targetOrderValue . ' WHERE ' . KT_escapeFieldName($this->primaryKey) . ' = ' . KT_escapeForSql($primaryKeyValue, "NUMERIC_TYPE");
$rs = $connWrap->Execute($sql);
if ($rs === false) {
return array('error' => array('code' => 'SQL Error', 'message' => 'update item position: ' . $connWrap->ErrorMsg()));
}
// substract (max+2) from all the items that were previously shift
$sql = 'UPDATE ' . $this->tableName . ' SET ' . KT_escapeFieldName($this->orderFieldName) . '=' . KT_escapeFieldName($this->orderFieldName) . ' - ' . ($max_order - 1) . ' WHERE ' . KT_escapeFieldName($this->orderFieldName) . ' >= ' . $max_order;
$rs = $connWrap->Execute($sql);
if ($rs === false) {
return array('error' => array('code' => 'SQL Error', 'message' => 'shift back order values: ' . $connWrap->ErrorMsg()));
}
}
}
return "OK";
}
function updateValue($pkvalue, $fieldvalue)
{
if (!$this->isEnabled) {
return array('error' => array('code' => 'Update Error', 'message' => 'You don\'t have permission to use the edit inplace!'));
}
require_once realpath(dirname(__FILE__) . '/' . '/../../../../Connections/' . $this->connectionName . '.php');
$hostname = 'MM_' . $this->connectionName . '_HOSTNAME';
$connWrap = null;
if (empty($GLOBALS[$hostname])) {
// we are on mysql
// Make unified connection variable
$database = 'database_' . $this->connectionName;
$connWrap = new KT_Connection($GLOBALS[$this->connectionName], $GLOBALS[$database]);
} else {
$connWrap = $GLOBALS[$this->connectionName];
}
$rs = $connWrap->Execute('UPDATE ' . $this->tableName . ' SET ' . KT_escapeFieldName($this->editField) . ' = ' . KT_escapeForSql($fieldvalue, $this->editFieldType) . ' WHERE ' . KT_escapeFieldName($this->primaryKey) . ' = ' . KT_escapeForSql($pkvalue, "NUMERIC_TYPE"));
if ($rs !== false) {
return "OK";
/*
$rs = $connWrap->Execute('SELECT '. KT_escapeFieldName($this->editField) .
' FROM '. $this->tableName .
' WHERE '. KT_escapeFieldName($this->primaryKey) .' = '. KT_escapeForSql($pkvalue, "NUMERIC_TYPE"));
if ($rs === false) {
return array('error' => array('code' => 'SQL Error', 'message' => 'Field selection error: '.$connWrap->ErrorMsg()));
}
if (!$rs->EOF) {
return $rs->Fields($this->editField);
}
return "";
*/
} else {
return array('error' => array('code' => 'SQL Error', 'message' => 'Update failed: ' . $connWrap->ErrorMsg()));
}
}
/**
* execute method of the class; check if record exists and return null or error;
* @param none
* @return mix null or error object if record exists
* @access public
*/
function Execute()
{
$where = array();
$i = 0;
foreach ($this->field as $field) {
if ($i++ == 0) {
$first = $field;
}
$type = $this->tNG->getColumnType($field);
$value = $this->tNG->getColumnValue($field);
$where[] = KT_escapeFieldName($field) . " = " . KT_escapeForSql($value, $type);
}
$sql = "SELECT * FROM " . $this->table . " WHERE " . implode(' AND ', $where);
if (in_array($this->tNG->transactionType, array('_update', '_multipleUpdate'))) {
$pk = $this->tNG->getPrimaryKey();
$pk_value = $this->tNG->getPrimaryKeyValue();
$pk_type = $this->tNG->getColumnType($this->tNG->getPrimaryKey());
$pk_value = KT_escapeForSql($pk_value, $pk_type);
$sql .= " AND " . $pk . " <> " . $pk_value;
}
$ret = $this->tNG->connection->Execute($sql);
if ($ret === false) {
return new tNG_error('CHECK_TF_SQL_ERROR', array(), array($this->tNG->connection->ErrorMsg(), $sql));
}
if (!$ret->EOF) {
$useSavedData = false;
if (in_array($this->tNG->transactionType, array('_delete', '_multipleDelete'))) {
$useSavedData = true;
}
$this->errorMsg = KT_DynamicData($this->errorMsg, $this->tNG, '', $useSavedData);
if ($GLOBALS['tNG_debug_mode'] == 'DEVELOPMENT') {
$err = new tNG_error('TRIGGER_MESSAGE__CHECK_UNIQUE', array(implode(', ', $this->field)), array());
} else {
$err = new tNG_error('%s', array($this->errorMsg), array());
}
if (count($this->field) == 1 && isset($this->tNG->columns[$this->field[$first]])) {
// set field error to $this->errorMsg
$err->setFieldError($this->field[$first], '%s', array($this->errorMsg));
if ($this->tNG->columns[$this->field[$first]]['method'] != 'POST') {
// set composed message as user error
$err->addDetails('%s', array($this->errorMsg), array(''));
}
} else {
// set composed message as user error
$err->addDetails('%s', array($this->errorMsg), array(''));
}
return $err;
}
return null;
}
function isLearnerInThisShare($userId, $shareId)
{
$query = "SELECT NULL\r\n\t\t FROM {$_SESSION['RealS_prefix']}share_cohort_members\r\n\t\t WHERE s_c_m_member = " . KT_escapeForSql($userId, "STRING_TYPE") . "\r\n\t\t AND s_c_m_share = " . KT_escapeForSql($shareId, "STRING_TYPE");
$mysql = new mysqlquery();
$rows = $mysql->runsql($query);
return count($rows);
}
/**
* Increment the counter
* @return nothing
* @access public
*/
function incrementCounter()
{
// increment in the same table
if ($this->table != '' && count($this->pk) > 0 && $this->counterField != '') {
$fileHash = $this->downloadHash;
$this->pk['value'] = $fileHash['pk'];
$sql = 'UPDATE ' . $this->table . ' SET ' . KT_escapeFieldName($this->counterField) . ' = ' . KT_escapeFieldName($this->counterField) . '+ 1 WHERE ' . KT_escapeFieldName($this->pk['field']) . ' = ' . KT_escapeForSql($this->pk['value'], $this->pk['type'], false);
$ret = $this->conn->Execute($sql);
if ($ret === false) {
$this->setError(new tNG_error('INCREMENTER_ERROR', array(), array($this->conn->ErrorMsg(), $sql)));
return;
}
}
// increment in the MTM table
if ($this->counterFieldMtm != '' && $this->tableMtm != '' && count($this->fkMtm) > 0 && count($this->pkMtm) > 0) {
$fileHash = $this->downloadHash;
if (!isset($fileHash['fkMtm']) || $fileHash['fkMtm'] == '') {
$this->setError(new tNG_error('INCREMENTER_ERROR_FK', array(), array($this->fkMtm['field'])));
return;
}
$this->fkMtm['value'] = $fileHash['fkMtm'];
if (!isset($fileHash['pkMtm']) || $fileHash['pkMtm'] == '') {
$this->setError(new tNG_error('INCREMENTER_ERROR_FK', array(), array($this->pkMtm['field'])));
return;
}
$this->pkMtm['value'] = $fileHash['pkMtm'];
$sql = 'UPDATE ' . $this->tableMtm . ' SET ' . KT_escapeFieldName($this->counterFieldMtm) . ' = ' . KT_escapeFieldName($this->counterFieldMtm) . '+ 1 WHERE ' . KT_escapeFieldName($this->pkMtm['field']) . ' = ' . KT_escapeForSql($this->pkMtm['value'], $this->pkMtm['type'], false) . ' AND ' . KT_escapeFieldName($this->fkMtm['field']) . ' = ' . KT_escapeForSql($this->fkMtm['value'], $this->fkMtm['type'], false);
$ret = $this->conn->Execute($sql);
if ($ret === false) {
$this->setError(new tNG_error('INCREMENTER_ERROR', array(), array($this->conn->ErrorMsg(), $sql)));
return;
}
}
return null;
}
function Trigger_UpdatePassword_CheckOldPassword(&$tNG)
{
$password_field = $GLOBALS['tNG_login_config']['password_field'];
$password_value = $tNG->getColumnValue($password_field);
$old_password_value = KT_DynamicData("{POST.old_" . $password_field . "}", $tNG);
if ($old_password_value != "" && $password_value == "") {
$errObj = new tNG_error("UPDATEPASS_NO_NEW_PASS", array(), array());
$errObj->setFieldError($password_field, "UPDATEPASS_NO_NEW_PASS_FIELDERR", array());
return $errObj;
}
if ($password_value != "") {
if ($GLOBALS['tNG_login_config']['password_encrypt'] == "true") {
if ($old_password_value != "") {
$old_password_value = tNG_encryptString($old_password_value);
}
}
$table = $GLOBALS['tNG_login_config']['table'];
$pk_field = $GLOBALS['tNG_login_config']['pk_field'];
$pk_value = KT_escapeForSql($tNG->getPrimaryKeyValue(), $GLOBALS['tNG_login_config']['pk_type']);
$sql = "SELECT " . KT_escapeFieldName($password_field) . " FROM " . $table . " WHERE " . KT_escapeFieldName($pk_field) . "=" . $pk_value;
$rs = $tNG->connection->Execute($sql);
if (!is_object($rs)) {
return new tNG_error("LOGIN_RECORDSET_ERR", array(), array());
}
if ($rs->RecordCount() == 0) {
return new tNG_error("UPDATEPASS_NO_RECORD", array(), array());
}
if ($rs->RecordCount() != 1) {
return new tNG_error("UPDATEPASS_TOMANY_RECORDS", array(), array());
}
$db_password_value = $rs->Fields($GLOBALS['tNG_login_config']['password_field']);
if ($db_password_value != $old_password_value) {
$tNG->addColumn("old_" . $password_field, "STRING_TYPE", "VALUE", "");
$errObj = new tNG_error("UPDATEPASS_WRONG_OLD_PASS", array(), array());
$errObj->setFieldError("old_" . $password_field, "UPDATEPASS_WRONG_OLD_PASS_FIELDERR", array());
return $errObj;
}
}
return null;
}
require_once dirname(realpath(__FILE__)) . '/../../Connections/' . $vars['conn'] . '.php';
$KT_conn = ${$vars['conn']};
$KT_conndb = ${'database_' . $vars['conn']};
// mysql adodb abstraction layer
if (is_resource($KT_conn)) {
$conn = new KT_Connection($KT_conn, $KT_conndb);
} else {
$conn =& $KT_conn;
}
KT_setDbType($conn);
$el = KT_getRealValue('GET', 'el');
$text = KT_getRealValue('GET', 'text');
$sql = 'INSERT INTO ' . $vars['table'] . ' (' . KT_escapeFieldName($vars['updatefield']) . ') VALUES (' . KT_escapeForSql($text, 'STRING_TYPE') . ')';
$conn->Execute($sql);
$ERROR = $conn->ErrorMsg();
$sql = 'SELECT ' . KT_escapeFieldName($vars['idfield']) . ' AS id FROM ' . $vars['table'] . ' WHERE ' . KT_escapeFieldName($vars['updatefield']) . ' = ' . KT_escapeForSql($text, 'STRING_TYPE');
$rsName = $vars['rsName'];
${$rsName} = $conn->Execute($sql);
${'row_' . $rsName} = ${$rsName}->fields;
$text = KT_escapeJS($text);
//JSRecordset($rsName);
?>
<html><body onLoad="parent.MXW_DynamicObject_reportDone('<?php
echo $el;
?>
', isError)">
<?php
if (${'row_' . $rsName}['id'] != '') {
?>
<script>
var isError = false;
/**
* execute method of the class;
* @param none
* @return mix null or error object if records exists and the value of the throwErrorIfExists;
* @access public
*/
function Execute()
{
$field_value = KT_escapeForSql($this->value, $this->type);
$sql = "SELECT " . KT_escapeFieldName($this->field) . " FROM " . $this->table . " WHERE " . KT_escapeFieldName($this->field) . " = " . $field_value;
$ret = $this->tNG->connection->Execute($sql);
if ($ret === false) {
return new tNG_error('CHECK_TF_SQL_ERROR', array(), array($this->tNG->connection->ErrorMsg(), $sql));
}
$useSavedData = false;
if (in_array($this->tNG->transactionType, array('_delete', '_multipleDelete'))) {
$useSavedData = true;
}
if ($this->throwErrorIfExists && !$ret->EOF) {
$err = new tNG_error('DEFAULT_TRIGGER_MESSAGE', array(), array());
return $err;
}
if (!$this->throwErrorIfExists && $ret->EOF) {
$err = new tNG_error('DEFAULT_TRIGGER_MESSAGE', array(), array());
return $err;
}
return null;
}
/**
* Get the local recordset associated to this transaction
* @return object resource Recordset resource
* @access protected
*/
function getLocalRecordset()
{
//Transaction was not started, use the default values
$fakeArr = array();
$tmpArr = $this->columns;
$fakeRs = array();
if (!isset($tmpArr[$this->primaryKey])) {
$tmpArr[$this->primaryKey] = $this->primaryKeyColumn;
$tmpArr[$this->primaryKey]['default'] = NULL;
}
foreach ($tmpArr as $colName => $colDetails) {
$tmpVal = KT_escapeForSql($colDetails['default'], $colDetails['type'], true);
$fakeArr[$colName] = $tmpVal;
}
for ($i = 0; $i < $this->insertElements; $i++) {
$fakeArr[$this->pkName] = "KT_NEW";
$fakeRs[$i] = $fakeArr;
}
return $this->getFakeRecordset($fakeRs);
}
