/**
* Return an array of databox (key=sbas_id) which are granted, with
* optionnal filter by rights
*
* @param Array $rights
* @return \databox[]
*/
public function get_granted_sbas($rights = [])
{
if (is_string($rights)) {
$rights = [$rights];
}
assert(is_array($rights));
$this->load_rights_sbas();
$ret = [];
foreach ($this->_rights_sbas as $sbas_id => $datas) {
$continue = false;
foreach ($rights as $right) {
if (!$this->has_right_on_sbas($sbas_id, $right)) {
$continue = true;
break;
}
}
if ($continue) {
continue;
}
try {
$ret[$sbas_id] = $this->app->findDataboxById((int) $sbas_id);
} catch (\Exception $e) {
}
}
return $ret;
}
public function __construct(Application $app, module_report $report)
{
$this->conn = $app->getApplicationBox()->get_connection();
$this->connbas = $app->findDataboxById($report->getSbasId())->get_connection();
$this->filter = new module_report_sqlfilter($app, $report);
$this->sql = '';
$this->params = [];
$this->total_row = 0;
$this->enable_limit = $report->getEnableLimit();
}
public function __construct(Application $app, module_report $report)
{
$this->app = $app;
$this->conn = $app->findDataboxById($report->getSbasId())->get_connection();
if (is_array($report->getTransQueryString())) {
$this->cor_query = $report->getTransQueryString();
}
$this->buildFilter($report);
$this->report = $report;
}
public function log_view($log_id, $referrer, $gv_sit)
{
$databox = $this->app->findDataboxById($this->get_sbas_id());
$connbas = $databox->get_connection();
$sql = 'INSERT INTO log_view (id, log_id, date, record_id, referrer, site_id)
VALUES
(null, :log_id, now(), :rec, :referrer, :site)';
$params = [':log_id' => $log_id, ':rec' => $this->get_record_id(), ':referrer' => $referrer, ':site' => $gv_sit];
$stmt = $connbas->prepare($sql);
$stmt->execute($params);
$stmt->closeCursor();
return $this;
}
/**
* {@inheritdoc}
*/
public function apply(base $appbox, Application $app)
{
$conn = $appbox->get_connection();
$sql = 'SELECT sbas_id, record_id, id FROM BasketElements';
$stmt = $conn->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt->closeCursor();
foreach ($result as $row) {
$sbas_id = (int) $row['sbas_id'];
try {
$connbas = $app->findDataboxById($sbas_id)->get_connection();
$connbas->connect();
} catch (\Exception $e) {
$conn->exec('DELETE FROM ValidationDatas WHERE basket_element_id = ' . $row['id']);
$conn->exec('DELETE FROM BasketElements WHERE id = ' . $row['id']);
continue;
}
$sql = 'SELECT record_id FROM record WHERE record_id = :record_id';
$stmt = $connbas->prepare($sql);
$stmt->execute([':record_id' => $row['record_id']]);
$rowCount = $stmt->rowCount();
$stmt->closeCursor();
if ($rowCount == 0) {
$conn->exec('DELETE FROM ValidationDatas WHERE basket_element_id = ' . $row['id']);
$conn->exec('DELETE FROM BasketElements WHERE id = ' . $row['id']);
}
}
$dql = "SELECT b FROM Phraseanet:Basket b WHERE b.description != ''";
$n = 0;
$perPage = 100;
$query = $app['orm.em']->createQuery($dql)->setFirstResult($n)->setMaxResults($perPage);
$paginator = new Paginator($query, true);
$count = count($paginator);
while ($n < $count) {
$query = $app['orm.em']->createQuery($dql)->setFirstResult($n)->setMaxResults($perPage);
$paginator = new Paginator($query, true);
foreach ($paginator as $basket) {
$htmlDesc = $basket->getDescription();
$description = trim(strip_tags(str_replace("<br />", "\n", $htmlDesc)));
if ($htmlDesc == $description) {
continue;
}
$basket->setDescription($description);
}
$n += $perPage;
$app['orm.em']->flush();
}
$app['orm.em']->flush();
return true;
}
public static function updateIcon(Application $app, $databox_id, $bit, $switch, UploadedFile $file)
{
$databox = $app->findDataboxById($databox_id);
$statusStructure = $app['factory.status-structure']->getStructure($databox);
if (!$statusStructure->hasStatus($bit)) {
throw new InvalidArgumentException(sprintf('bit %s does not exists', $bit));
}
$status = $statusStructure->getStatus($bit);
$switch = in_array($switch, ['on', 'off']) ? $switch : false;
if (!$switch) {
throw new Exception_InvalidArgument();
}
$url = $statusStructure->getUrl();
$path = $statusStructure->getPath();
if ($file->getSize() >= 65535) {
throw new Exception_Upload_FileTooBig();
}
if (!$file->isValid()) {
throw new Exception_Upload_Error();
}
self::deleteIcon($app, $databox_id, $bit, $switch);
$name = "-stat_" . $bit . "_" . ($switch == 'on' ? '1' : '0') . ".gif";
try {
$file = $file->move($app['root.path'] . "/config/status/", $path . $name);
} catch (FileException $e) {
throw new Exception_Upload_CannotWriteFile();
}
$custom_path = $app['root.path'] . '/www/custom/status/';
$app['filesystem']->mkdir($custom_path, 0750);
//resize status icon 16x16px
$imageSpec = new ImageSpecification();
$imageSpec->setResizeMode(ImageSpecification::RESIZE_MODE_OUTBOUND);
$imageSpec->setDimensions(16, 16);
$filePath = sprintf("%s%s", $path, $name);
$destPath = sprintf("%s%s", $custom_path, basename($path . $name));
try {
$app['media-alchemyst']->turninto($filePath, $destPath, $imageSpec);
} catch (\MediaAlchemyst\Exception $e) {
}
$status['img_' . $switch] = $url . $name;
$status['path_' . $switch] = $filePath;
return true;
}
public static function updateClientInfos(Application $app, $appId)
{
if (!$app->getAuthenticator()->isAuthenticated()) {
return;
}
$session = $app['repo.sessions']->find($app['session']->get('session_id'));
if (!$session) {
throw new SessionNotFound('No session found');
}
if (!$session->hasModuleId($appId)) {
$module = new SessionModule();
$module->setModuleId($appId);
$module->setSession($session);
$session->addModule($module);
$app['orm.em']->persist($module);
$app['orm.em']->persist($session);
$app['orm.em']->flush();
}
$appName = ['1' => 'Prod', '2' => 'Client', '3' => 'Admin', '4' => 'Report', '5' => 'Thesaurus', '6' => 'Compare', '7' => 'Validate', '8' => 'Upload', '9' => 'API'];
if (isset($appName[$appId])) {
$sbas_ids = array_keys($app->getAclForUser($app->getAuthenticatedUser())->get_granted_sbas());
foreach ($sbas_ids as $sbas_id) {
try {
$logger = $app['phraseanet.logger']($app->findDataboxById($sbas_id));
$databox = $app->findDataboxById($sbas_id);
$connbas = $databox->get_connection();
$sql = 'SELECT appli FROM log WHERE id = :log_id';
$stmt = $connbas->prepare($sql);
$stmt->execute([':log_id' => $logger->get_id()]);
$row3 = $stmt->fetch(PDO::FETCH_ASSOC);
$stmt->closeCursor();
if (!$row3) {
throw new Exception('no log');
}
$applis = unserialize($row3['appli']);
if (!in_array($appId, $applis)) {
$applis[] = $appId;
}
$sql = 'UPDATE log SET appli = :applis WHERE id = :log_id';
$params = [':applis' => serialize($applis), ':log_id' => $logger->get_id()];
$stmt = $connbas->prepare($sql);
$stmt->execute($params);
$stmt->closeCursor();
} catch (\Exception $e) {
}
}
}
return;
}
public static function activiteAddedTopTenUser(Application $app, $dmin, $dmax, $sbas_id, $list_coll_id)
{
$databox = $app->findDataboxById($sbas_id);
$conn = $databox->get_connection();
$result = [];
$datefilter = module_report_sqlfilter::constructDateFilter($dmin, $dmax, 'log_docs.date');
$params = [];
$params = array_merge($params, $datefilter['params']);
/*
$sql = "
SELECT tt.usrid, tt.user, sum( 1 ) AS nb
FROM (
SELECT DISTINCT(log.id), log.usrid, log.user
FROM (log_docs AS log_date)
INNER JOIN log FORCE INDEX (date_site) ON (log_date.log_id = log.id)
INNER JOIN log_colls FORCE INDEX (couple) ON (log.id = log_colls.log_id)
WHERE " . $datefilter['sql'] . " AND log_date.action = 'add'" .
(('' !== $collfilter['sql']) ? " AND (" . $collfilter['sql'] . ")" : '')
. ") AS tt
GROUP BY tt.usrid
ORDER BY nb ASC ";
*/
$sql = "" . " SELECT tt.usrid, tt.user, sum( 1 ) AS nb\n" . " FROM (\n" . " SELECT DISTINCT(log.id), log.usrid, log.user\n" . " FROM (log_docs)\n" . " INNER JOIN log FORCE INDEX (date_site) ON (log_docs.log_id = log.id)\n" . " WHERE " . $datefilter['sql'] . " AND log_docs.action = 'add'" . ") AS tt\n" . " GROUP BY tt.usrid\n" . " ORDER BY nb ASC ";
// no_file_put_contents("/tmp/report.txt", sprintf("%s (%s)\n%s\n\n", __FILE__, __LINE__, $sql), FILE_APPEND);
$stmt = $conn->prepare($sql);
$stmt->execute($params);
$rs = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt->closeCursor();
foreach ($rs as $row) {
$result[$row['usrid']]['lib'] = $row['user'];
$result[$row['usrid']]['nb'] = $row['nb'];
$result[$row['usrid']]['id'] = $row['usrid'];
}
return $result;
}
/**
* @param Application $app
* @param int $sbas_id
* @param string $type
* @param mixed content $value
*/
public static function update(Application $app, $sbas_id, $type, $value = '')
{
$databox = $app->findDataboxById($sbas_id);
$connbas = $databox->get_connection();
$sql = 'SELECT distinct site_id as site_id
FROM clients
WHERE site_id != :site_id';
$stmt = $connbas->prepare($sql);
$stmt->execute([':site_id' => $app['conf']->get('servername')]);
$rs = $stmt->fetchAll(\PDO::FETCH_ASSOC);
$stmt->closeCursor();
$sql = 'REPLACE INTO memcached (site_id, type, value)
VALUES (:site_id, :type, :value)';
$stmt = $connbas->prepare($sql);
foreach ($rs as $row) {
$stmt->execute([':site_id' => $row['site_id'], ':type' => $type, ':value' => $value]);
}
$stmt->closeCursor();
return;
}
private function calcWhere(Application $app, $sbas_id, &$sxtask)
{
$err = "";
/** @var databox $databox */
$databox = $app->findDataboxById($sbas_id);
/** @var Connection $connbas */
$connbas = $databox->get_connection();
$struct = $databox->get_meta_structure();
$tw = array();
$join = '';
$ijoin = 0;
// criteria <type type="XXX" />
if (($x = $sxtask->from->type['type']) !== null) {
switch (strtoupper($x)) {
case 'RECORD':
$tw[] = 'parent_record_id!=record_id';
break;
case 'STORY':
$tw[] = 'parent_record_id=record_id';
break;
}
}
// criteria <text field="XXX" compare="OP" value="ZZZ" />
foreach ($sxtask->from->text as $x) {
$field = $struct->get_element_by_name($x['field']);
if ($field != null) {
$ijoin++;
$comp = trim($x['compare']);
if (in_array($comp, array('<', '>', '<=', '>=', '=', '!='))) {
$s = 'p' . $ijoin . '.meta_struct_id=' . $connbas->quote($field->get_id()) . ' AND p' . $ijoin . '.value' . $comp . '' . $connbas->quote($x['value']) . '';
$tw[] = $s;
$join .= ' INNER JOIN metadatas AS p' . $ijoin . ' USING(record_id)';
} else {
// bad comparison operator
$err .= sprintf("bad comparison operator (%s)\n", $comp);
}
} else {
// unknown field ?
$err .= sprintf("unknown field (%s)\n", $x['field']);
}
}
// criteria <date direction ="XXX" field="YYY" delta="Z" />
foreach ($sxtask->from->date as $x) {
$field = $struct->get_element_by_name($x['field']);
if ($field != null) {
$ijoin++;
$s = 'p' . $ijoin . '.meta_struct_id=' . $connbas->quote($field->get_id()) . ' AND NOW()';
$dir = strtoupper($x['direction']);
if (in_array($dir, array('BEFORE', 'AFTER'))) {
// prevent malformed dates to act
$tw[] = '!ISNULL(CAST(p' . $ijoin . '.value AS DATETIME))';
$s .= $dir == 'BEFORE' ? '<' : '>=';
$delta = (int) $x['delta'];
if ($delta > 0) {
$s .= '(p' . $ijoin . '.value+INTERVAL ' . $delta . ' DAY)';
} elseif ($delta < 0) {
$s .= '(p' . $ijoin . '.value-INTERVAL ' . -$delta . ' DAY)';
} else {
$s .= 'CAST(p' . $ijoin . '.value AS DATETIME)';
}
$tw[] = $s;
$join .= ' INNER JOIN metadatas AS p' . $ijoin . ' USING(record_id)';
} else {
// bad direction
$err .= sprintf("bad direction (%s)\n", $x['direction']);
}
} else {
// unknown field ?
$err .= sprintf("unknown field (%s)\n", $x['field']);
}
}
// criteria <coll compare="OP" id="X,Y,Z" />
if ($x = $sxtask->from->coll) {
$tcoll = explode(',', $x['id']);
foreach ($tcoll as $i => $c) {
$tcoll[$i] = (int) $c;
}
if ($x['compare'] == '=') {
if (count($tcoll) == 1) {
$tw[] = 'coll_id = ' . $tcoll[0];
} else {
$tw[] = 'coll_id IN(' . implode(',', $tcoll) . ')';
}
} elseif ($x['compare'] == '!=') {
if (count($tcoll) == 1) {
$tw[] = 'coll_id != ' . $tcoll[0];
} else {
$tw[] = 'coll_id NOT IN(' . implode(',', $tcoll) . ')';
}
} else {
// bad operator
$err .= sprintf("bad comparison operator (%s)\n", $x['compare']);
}
}
// criteria <status mask="XXXXX" />
$x = trim($sxtask->from->status['mask']);
$x = preg_replace('/[^0-1]/', 'x', $x);
$mx = str_replace(' ', '0', ltrim(str_replace(array('0', 'x'), array(' ', ' '), $x)));
$ma = str_replace(' ', '0', ltrim(str_replace(array('x', '0'), array(' ', '1'), $x)));
if ($mx && $ma) {
$tw[] = '((status ^ 0b' . $mx . ') & 0b' . $ma . ')=0';
} elseif ($mx) {
$tw[] = '(status ^ 0b' . $mx . ')=0';
} elseif ($ma) {
$tw[] = '(status & 0b' . $ma . ")=0";
}
return array($tw, $join, $err);
}
public static function getPreff(Application $app, $sbasid)
{
$tab = [];
$databox = $app->findDataboxById((int) $sbasid);
foreach ($databox->get_meta_structure() as $databox_field) {
/* @var $databox_field \databox_field */
if ($databox_field->is_report()) {
$tab[] = $databox_field->get_name();
}
}
return $tab;
}
public function hydrate(Application $app)
{
$this->app = $app;
$this->set_databox($this->app->findDataboxById($this->sbas_id));
$this->loadVocabulary();
}
/**
*
* @param Application $app
* @param string $host
* @param int $port
* @param string $user
* @param string $password
* @param string $dbname
* @return databox
*/
public static function mount(Application $app, $host, $port, $user, $password, $dbname)
{
$conn = $app['db.provider'](['host' => $host, 'port' => $port, 'user' => $user, 'password' => $password, 'dbname' => $dbname]);
$conn->connect();
$conn = $app->getApplicationBox()->get_connection();
$sql = 'SELECT MAX(ord) as ord FROM sbas';
$stmt = $conn->prepare($sql);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$stmt->closeCursor();
if ($row) {
$ord = $row['ord'] + 1;
}
$sql = 'INSERT INTO sbas (sbas_id, ord, host, port, dbname, sqlengine, user, pwd)
VALUES (null, :ord, :host, :port, :dbname, "MYSQL", :user, :password)';
$stmt = $conn->prepare($sql);
$stmt->execute([':ord' => $ord, ':host' => $host, ':port' => $port, ':dbname' => $dbname, ':user' => $user, ':password' => $password]);
$stmt->closeCursor();
$sbas_id = (int) $conn->lastInsertId();
$app->getApplicationBox()->delete_data_from_cache(appbox::CACHE_LIST_BASES);
$databox = $app->findDataboxById($sbas_id);
$databox->delete_data_from_cache(databox::CACHE_COLLECTIONS);
phrasea::reset_sbasDatas($app['phraseanet.appbox']);
cache_databox::update($app, $databox->get_sbas_id(), 'structure');
$app['dispatcher']->dispatch(DataboxEvents::MOUNTED, new MountedEvent($databox));
return $databox;
}
/**
*
* @param Application $app
* @param string $serialized
*
* @return $this
*
* @throws \InvalidArgumentException
* @throws \RuntimeException
*/
public static function hydrate(Application $app, $serialized)
{
$serialized = json_decode($serialized, true);
if (!is_array($serialized)) {
throw new \InvalidArgumentException('SearchEngineOptions data are corrupted');
}
$options = new static();
$options->disallowBusinessFields();
$sort_by = $sort_ord = null;
foreach ($serialized as $key => $value) {
switch (true) {
case is_null($value):
$value = null;
break;
case in_array($key, ['date_min', 'date_max']):
$value = \DateTime::createFromFormat(DATE_ATOM, $value);
break;
case $value instanceof \stdClass:
$tmpvalue = (array) $value;
$value = [];
foreach ($tmpvalue as $k => $data) {
$k = ctype_digit($k) ? (int) $k : $k;
$value[$k] = $data;
}
break;
case in_array($key, ['date_fields', 'fields']):
$value = array_map(function ($serialized) use($app) {
$data = explode('_', $serialized);
return $app->findDataboxById($data[0])->get_meta_structure()->get_element($data[1]);
}, $value);
break;
case in_array($key, ['collections', 'business_fields']):
$value = array_map(function ($base_id) use($app) {
return \collection::get_from_base_id($app, $base_id);
}, $value);
break;
}
switch ($key) {
case 'record_type':
$options->setRecordType($value);
break;
case 'search_type':
$options->setSearchType($value);
break;
case 'status':
$options->setStatus($value);
break;
case 'date_min':
$options->setMinDate($value);
break;
case 'date_max':
$options->setMaxDate($value);
break;
case 'i18n':
if ($value) {
$options->setLocale($value);
}
break;
case 'stemming':
$options->setStemming($value);
break;
case 'sort_by':
$sort_by = $value;
break;
case 'sort_ord':
$sort_ord = $value;
break;
case 'date_fields':
$options->setDateFields($value);
break;
case 'fields':
$options->setFields($value);
break;
case 'collections':
$options->onCollections($value);
break;
case 'business_fields':
$options->allowBusinessFieldsOn($value);
break;
default:
throw new \RuntimeException(sprintf('Unable to handle key `%s`', $key));
break;
}
}
if ($sort_by) {
if ($sort_ord) {
$options->setSort($sort_by, $sort_ord);
} else {
$options->setSort($sort_by);
}
}
return $options;
}
public static function getNbConn(Application $app, $dmin, $dmax, $sbas_id, $list_coll_id)
{
$databox = $app->findDataboxById($sbas_id);
$conn = $databox->get_connection();
$datefilter = module_report_sqlfilter::constructDateFilter($dmin, $dmax);
$params = array_merge([':site_id' => $app['conf']->get(['main', 'key'])], $datefilter['params']);
$finalfilter = $datefilter['sql'] . ' AND ';
$finalfilter .= 'log_date.site = :site_id';
/*
$sql = "SELECT COUNT(DISTINCT(log_date.id)) as nb
FROM log as log_date FORCE INDEX (date_site)
INNER JOIN log_colls FORCE INDEX (couple) ON (log_date.id = log_colls.log_id)
WHERE " . $finalfilter;
*/
$sql = "SELECT COUNT(DISTINCT(log_date.id)) as nb\n" . " FROM log as log_date FORCE INDEX (date_site)\n" . " WHERE " . $finalfilter . "\n";
// no_file_put_contents("/tmp/report.txt", sprintf("%s (%s)\n%s\n\n", __FILE__, __LINE__, $sql), FILE_APPEND);
$stmt = $conn->prepare($sql);
$stmt->execute($params);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$stmt->closeCursor();
return (int) $row['nb'];
}
/**
* {@inheritdoc}
*
* @return MetaField
*/
public static function loadFromString(Application $app, $string)
{
if (!($datas = @unserialize($string))) {
throw new \InvalidArgumentException('Unable to load metadata from string');
}
try {
return new static($app->findDataboxById($datas['sbas_id'])->get_meta_structure()->get_element($datas['id']), $datas['value']);
} catch (NotFoundHttpException $e) {
throw new \InvalidArgumentException('Field does not exist anymore');
}
}
public static function getTopDl(Application $app, $dmin, $dmax, $sbas_id, $list_coll_id)
{
$databox = $app->findDataboxById((int) $sbas_id);
$conn = $databox->get_connection();
$params = [':site_id' => $app['conf']->get(['main', 'key'])];
$datefilter = module_report_sqlfilter::constructDateFilter($dmin, $dmax);
$params = array_merge($params, $datefilter['params']);
$finalfilter = "";
$array = ['preview' => [], 'document' => []];
$finalfilter .= $datefilter['sql'] . ' AND ';
$finalfilter .= 'log.site = :site_id';
/*
$sql = '
SELECT tt.id, tt.name, SUM(1) AS nb
FROM (
SELECT DISTINCT(log.id) AS log_id, log_date.record_id as id, subdef.name
FROM ( log )
INNER JOIN log_colls FORCE INDEX (couple) ON (log.id = log_colls.log_id)
INNER JOIN log_docs as log_date ON (log.id = log_date.log_id)
INNER JOIN subdef ON (log_date.record_id = subdef.record_id)
WHERE (
' . $finalfilter . '
)
AND ( log_date.action = \'download\'
OR log_date.action = \'mail\'
)
AND subdef.name = log_date.final
) AS tt
GROUP BY id, name
';
*/
$sql = "SELECT tt.id, tt.name, SUM(1) AS nb\n" . " FROM (\n" . " SELECT DISTINCT(log.id) AS log_id, log_docs.record_id as id, subdef.name\n" . " FROM ( log )\n" . " INNER JOIN log_docs ON (log.id = log_docs.log_id)\n" . " INNER JOIN subdef ON (log_docs.record_id = subdef.record_id)\n" . " WHERE (" . $finalfilter . ")\n" . " AND ( log_docs.action = 'download' OR log_docs.action = 'mail' )\n" . " AND subdef.name = log_docs.final\n" . " ) AS tt\n" . " GROUP BY id, name\n";
// no_file_put_contents("/tmp/report.txt", sprintf("%s (%s)\n%s\n\n", __FILE__, __LINE__, $sql), FILE_APPEND);
$stmt = $conn->prepare($sql);
$stmt->execute($params);
$rs = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt->closeCursor();
foreach ($rs as $row) {
$record = $databox->get_record($row['id']);
$k = $row['id'] . '_' . $sbas_id;
$orig_name = $record->get_original_name();
if ($row['name'] == 'document') {
$array[$row['name']][$k]['nb'] = (int) $row['nb'];
$array[$row['name']][$k]['lib'] = $orig_name;
$array[$row['name']][$k]['sbasid'] = $sbas_id;
$array[$row['name']][$k]['id'] = $row['id'];
} elseif ($row['name'] == "preview") {
$array[$row['name']][$k]['nb'] = (int) $row['nb'];
$array[$row['name']][$k]['lib'] = $orig_name;
$array[$row['name']][$k]['sbasid'] = $sbas_id;
$array[$row['name']][$k]['id'] = $row['id'];
}
}
return $array;
}
