}
require_once SQ_FUDGE_PATH . '/db_extras/db_extras.inc';
$GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db2');
$GLOBALS['SQ_SYSTEM']->doTransaction('BEGIN');
$db = $GLOBALS['SQ_SYSTEM']->db;
$sql = 'SELECT
a.assetid
FROM
(' . SQ_TABLE_RUNNING_PREFIX . 'ast a
JOIN ' . SQ_TABLE_RUNNING_PREFIX . 'ast_typ_inhd i ON a.type_code = i.type_code)
JOIN ' . SQ_TABLE_RUNNING_PREFIX . 'ast_lnk l
ON l.minorid = a.assetid';
$where = 'l.majorid IN (:assetid, :subfolder_assetid)
AND i.inhd_type_code = :inhd_type_code
AND a.created BETWEEN ' . db_extras_todate(MatrixDAL::getDbType(), ':created_from', FALSE) . '
AND ' . db_extras_todate(MatrixDAL::getDbType(), ':created_to', FALSE);
$where = $GLOBALS['SQ_SYSTEM']->constructRollbackWhereClause($where, 'a');
$where = $GLOBALS['SQ_SYSTEM']->constructRollbackWhereClause($where, 'l');
$sql = $sql . $where . ' ORDER BY a.created DESC';
$query = MatrixDAL::preparePdoQuery($sql);
MatrixDAL::bindValueToPdo($query, 'assetid', $asset->id);
MatrixDAL::bindValueToPdo($query, 'subfolder_assetid', $sub_folder->id);
MatrixDAL::bindValueToPdo($query, 'inhd_type_code', 'form_submission');
MatrixDAL::bindValueToPdo($query, 'created_from', $from_value);
MatrixDAL::bindValueToPdo($query, 'created_to', $to_value);
$assetids = MatrixDAL::executePdoAssoc($query, 0);
if (empty($assetids)) {
echo "No form submission found for '{$asset->name}' (#{$assetid}) within the specified date range\n";
exit;
}
echo 'Found ' . count($assetids) . " form submission(s) for '{$asset->name}' (#{$assetid})\n(Date range: {$from_value} to {$to_value})\n";
/**
* Update the sq_file_vers_history table in database. Set to_time for old record and add new record
*
* @param File_Versioning $file_versioning the File_Versioning object
* @param string $fileid the fileid of the file to be updated
* @param string $real_file the real file path of the file
* @param int $version the new version of the file
* @param string $extra_info
* @return boolean return TRUE if success; otherwise, return FALSE
* @see _updateFile() in file_versioning.inc
*/
function _updateFileVersionHistory($file_versioning, $fileid, $real_file, $version, $extra_info = '')
{
$GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db2');
$GLOBALS['SQ_SYSTEM']->doTransaction('BEGIN');
try {
//If this version is already in version history table, but the to_date is set, report it.
//This case is not likely to happen, just to make sure there is no duplicate version is set
$db_version_info = $file_versioning->_getFileInfoAtVersion($fileid, $version);
if (!empty($db_version_info)) {
echo "ERROR: THIS FILE (FILEID = {$fileid}, VERSION = {$version}) IS NO LONGER USED SINCE {$db_version_info['to_date']}\n";
return FALSE;
}
$now = time();
$date = ts_iso8601($now);
/*if (MatrixDAL::getDbType() == 'oci') {
$date = db_extras_todate(MatrixDAL::getDbType(), $date);
}*/
$sql = 'UPDATE sq_file_vers_history
SET to_date = :to_date
WHERE fileid = :fileid
AND to_date IS NULL';
try {
if (MatrixDAL::getDbType() == 'oci') {
$sql = str_replace(':to_date', db_extras_todate(MatrixDAL::getDbType(), ':to_date', FALSE), $sql);
}
$query = MatrixDAL::preparePdoQuery($sql);
MatrixDAL::bindValueToPdo($query, 'fileid', $fileid);
MatrixDAL::bindValueToPdo($query, 'to_date', $date);
MatrixDAL::execPdoQuery($query);
} catch (Exception $e) {
throw new Exception('Unable to update version history for file ID ' . $fileid . ' due to database error: ' . $e->getMessage());
}
if (file_exists($real_file)) {
$file_size = filesize($real_file);
$md5 = md5_file($real_file);
$sha1 = sha1_file($real_file);
$removal = '0';
} else {
$file_size = 0;
$md5 = '';
$sha1 = '';
$removal = '1';
}
$sql = 'INSERT INTO sq_file_vers_history
(fileid, version, from_date, to_date, file_size, md5, sha1, removal, extra_info)
VALUES
(:fileid, :version, :from_date, :to_date, :file_size, :md5, :sha1, :removal, :extra_info)';
try {
if (MatrixDAL::getDbType() == 'oci') {
$sql = str_replace(':from_date', db_extras_todate(MatrixDAL::getDbType(), ':from_date', FALSE), $sql);
}
$query = MatrixDAL::preparePdoQuery($sql);
MatrixDAL::bindValueToPdo($query, 'fileid', $fileid);
MatrixDAL::bindValueToPdo($query, 'version', $version);
MatrixDAL::bindValueToPdo($query, 'from_date', $date);
MatrixDAL::bindValueToPdo($query, 'to_date', NULL);
MatrixDAL::bindValueToPdo($query, 'file_size', $file_size);
MatrixDAL::bindValueToPdo($query, 'md5', $md5);
MatrixDAL::bindValueToPdo($query, 'sha1', $sha1);
MatrixDAL::bindValueToPdo($query, 'removal', $removal);
MatrixDAL::bindValueToPdo($query, 'extra_info', $extra_info);
MatrixDAL::execPdoQuery($query);
} catch (Exception $e) {
throw new Exception('Unable to insert version history for file ID ' . $fileid . ' due to database error: ' . $e->getMessage());
}
$GLOBALS['SQ_SYSTEM']->doTransaction('COMMIT');
$GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection();
} catch (Exception $e) {
echo "ERROR: " . $e->getMessage() . "\n";
$GLOBALS['SQ_SYSTEM']->doTransaction('ROLLBACK');
$GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection();
return FALSE;
}
return TRUE;
}
