From 09341adc8ac40f357b53d24b3f88034bae2ebe41 Mon Sep 17 00:00:00 2001 From: =?utf8?q?V=C3=A1s=C3=A1ry=20D=C3=A1niel?= Date: Wed, 18 Jul 2018 12:14:24 +0000 Subject: [PATCH] git-tfs-id: [http://tfs.userrendszerhaz.hu:8080/tfs/DefaultCollection]$/MediaCube;C31191 --- server/-configuration/scheduledjobs.json | 5 +- .../jobtemplates/retrieve-ondemand.xml | 4 + .../steps/OutputPathAndNameSelectorStep.java | 4 +- .../server/steps/TSMRestoreStep.java | 9 +- .../src/user/jobengine/db/ItemDAOEx.java | 11 +- .../src/user/jobengine/db/MediaFinder.java | 569 ++++++++++++++++++ .../pages/searchitems.zul | 4 - .../resources/i3-label_hu.properties | 2 +- 8 files changed, 592 insertions(+), 16 deletions(-) create mode 100644 server/user.jobengine.osgi.db/src/user/jobengine/db/MediaFinder.java diff --git a/server/-configuration/scheduledjobs.json b/server/-configuration/scheduledjobs.json index 9c0dfcc3..8368e843 100644 --- a/server/-configuration/scheduledjobs.json +++ b/server/-configuration/scheduledjobs.json @@ -19,9 +19,12 @@ "parameters": [ {"name": "globalRetrievePath", "value": "file://isilon.intra.echotv.hu", "type": "java.lang.String"}, {"name": "localRetrievePath", "value": "/mnt/ISILON", "type": "java.lang.String"}, - {"name": "trafficOutputFolder", "value": "PLAYOUT_NLE", "type": "java.lang.String"}, + {"name": "materialOutputFolder", "value": "PLAYOUT_NLE", "type": "java.lang.String"}, + {"name": "promoOutputFolder", "value": "PROMO_NLE", "type": "java.lang.String"}, + {"name": "advertisementOutputFolder", "value": "REKLAM_NLE", "type": "java.lang.String"}, {"name": "octopusOutputFolder", "value": "OCTOPUS", "type": "java.lang.String"}, {"name": "genericOutputFolder", "value": "ARCHIVE_RESTORE", "type": "java.lang.String"}, + {"name": "onlineOutputFolder", "value": "ONLINE", "type": "java.lang.String"}, {"name": "killDateDays", "value": 7, "type": "java.lang.Integer"} ] }, diff --git a/server/user.jobengine.executors/jobtemplates/retrieve-ondemand.xml b/server/user.jobengine.executors/jobtemplates/retrieve-ondemand.xml index e9234abb..be4d957a 100644 --- a/server/user.jobengine.executors/jobtemplates/retrieve-ondemand.xml +++ b/server/user.jobengine.executors/jobtemplates/retrieve-ondemand.xml @@ -9,6 +9,7 @@ + @@ -42,6 +43,9 @@ + + + diff --git a/server/user.jobengine.executors/src/user/jobengine/server/steps/OutputPathAndNameSelectorStep.java b/server/user.jobengine.executors/src/user/jobengine/server/steps/OutputPathAndNameSelectorStep.java index deac1f1d..4a8cb20d 100644 --- a/server/user.jobengine.executors/src/user/jobengine/server/steps/OutputPathAndNameSelectorStep.java +++ b/server/user.jobengine.executors/src/user/jobengine/server/steps/OutputPathAndNameSelectorStep.java @@ -64,8 +64,8 @@ public class OutputPathAndNameSelectorStep extends JobStep { @StepEntry public Object[] execute(String localRetrievePath, String materialOutputFolder, String promoOutputFolder, String advertisementOutputFolder, - String octopusOutputFolder, String genericOutputFolder, String onlineOutputFolder, boolean retrieveToNexio, String houseId, String targetPathType, - IJobEngine jobEngine, IJobRuntime jobRuntime) throws IOException { + String octopusOutputFolder, String genericOutputFolder, String onlineOutputFolder, String houseId, String targetPathType, IJobEngine jobEngine, + IJobRuntime jobRuntime) throws IOException { marker = jobRuntime.getMarker(); check(localRetrievePath, materialOutputFolder, promoOutputFolder, advertisementOutputFolder, octopusOutputFolder, genericOutputFolder, houseId, targetPathType); diff --git a/server/user.jobengine.executors/src/user/jobengine/server/steps/TSMRestoreStep.java b/server/user.jobengine.executors/src/user/jobengine/server/steps/TSMRestoreStep.java index 02190245..98a956ac 100644 --- a/server/user.jobengine.executors/src/user/jobengine/server/steps/TSMRestoreStep.java +++ b/server/user.jobengine.executors/src/user/jobengine/server/steps/TSMRestoreStep.java @@ -1,5 +1,6 @@ package user.jobengine.server.steps; +import java.io.IOException; import java.nio.file.Paths; import java.util.List; @@ -33,6 +34,11 @@ public class TSMRestoreStep extends JobStep { private String sourceFileName; private Marker marker; + protected void afterRestore(String targetPath, int killDateDays, String targetFileName) throws IOException { + if (killDateDays > 0) + EscortFiles.createUNCKillDate(targetPath, targetFileName, killDateDays, marker); + } + protected StoreUri createTargetUri(IItemManager manager, String targetPath) { return manager.createStoreUri(RemoteStoreProtocol.LOCAL, targetPath); } @@ -71,8 +77,7 @@ public class TSMRestoreStep extends JobStep { logger.info(new MediaCubeMarker(successRecipient), msg); logger.info(marker, msg); - if (killDateDays > 0) - EscortFiles.createUNCKillDate(targetPath, targetFileName, killDateDays, marker); + afterRestore(targetPath, killDateDays, targetFileName); } catch (Exception e) { Message msg = LogUtils.format("Az '{}' állomány visszatöltése sikertelen. A rendszer hibaüzenete: {}", sourceFileName, e.getMessage()); diff --git a/server/user.jobengine.osgi.db/src/user/jobengine/db/ItemDAOEx.java b/server/user.jobengine.osgi.db/src/user/jobengine/db/ItemDAOEx.java index a7ce69cd..8f1dd3ac 100644 --- a/server/user.jobengine.osgi.db/src/user/jobengine/db/ItemDAOEx.java +++ b/server/user.jobengine.osgi.db/src/user/jobengine/db/ItemDAOEx.java @@ -71,13 +71,11 @@ public class ItemDAOEx { typeCriteria.append(")"); } - String columNames = getColumnNames(); - StringBuilder sql = new StringBuilder(); sql.append("SELECT "); - sql.append(columNames); + sql.append(getColumnNames()); sql.append( - " FROM ITEM i, MEDIA m, MEDIAFILE mf, MEDIADESCRIPTION md, VW_MEDIAFILES vw_mf WHERE i.id = md.itemid AND m.id = md.mediaid AND mf.id = md.mediafileid AND m.id=vw_mf.mediaid"); + " FROM ITEM i, MEDIA m, MEDIAFILE mf, MEDIADESCRIPTION md, VW_MEDIAFILES vw_mf, ITEMTYPE it WHERE i.id = md.itemid AND m.id = md.mediaid AND mf.id = md.mediafileid AND m.id=vw_mf.mediaid AND m.itemTypeId = it.id"); if (typeIDs != null) { sql.append(" AND m.itemtypeid IN "); sql.append(typeCriteria.toString()); @@ -154,14 +152,15 @@ public class ItemDAOEx { private String getColumnNames() { StringBuilder columns = new StringBuilder(); - columns.append("i.houseid itemhouseid,"); + columns.append("i.id itemid,"); columns.append("i.title itemtitle,"); columns.append("m.id mediaid,"); + columns.append("m.title mediatitle,"); columns.append("m.archived,"); columns.append("m.length,"); columns.append("m.itemtypeid mediaitemtypeid,"); + columns.append("it.name mediaitemtypename,"); columns.append("m.houseid mediahouseid,"); - columns.append("m.title mediatitle,"); columns.append("mf.houseid mediafilehouseid,"); columns.append("vw_mf.mediafilecount"); return columns.toString(); diff --git a/server/user.jobengine.osgi.db/src/user/jobengine/db/MediaFinder.java b/server/user.jobengine.osgi.db/src/user/jobengine/db/MediaFinder.java new file mode 100644 index 00000000..7d91c1c0 --- /dev/null +++ b/server/user.jobengine.osgi.db/src/user/jobengine/db/MediaFinder.java @@ -0,0 +1,569 @@ +package user.jobengine.db; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.Formatter; +import java.util.List; +import java.util.Locale; + +import org.apache.logging.log4j.LogManager; +import org.apache.logging.log4j.Logger; + +import sqlj.runtime.ref.DefaultContext; + +public class MediaFinder { + private static final Logger logger = LogManager.getLogger(); + private ItemManager manager = null; + + public MediaFinder(ItemManager manager) { + this.manager = manager; + } + + private String buildQueryCore(QueryObject parameter) { + QueryBuilder queryBuilder = new QueryBuilder(); + queryBuilder.getParameters(parameter.getFilters()); + return queryBuilder.getQuery(); + } + + private String buildResultQuery(QueryObject queryObject, String innerQuery, SearchResult csr) { + StringBuilder resultQuery = new StringBuilder(); + Formatter qf = new Formatter(resultQuery, Locale.getDefault()); + RenderParameter rp = queryObject.getParameters(); + if (rp != null) { + long bottom = rp.getPage() * rp.getRowPerPage() + 1; + long top = bottom + rp.getRowPerPage() - 1; + resultQuery.append("WITH NUMBEREDITEM AS ("); + Metadata orderMetadata = rp.getOrderBy(); + if (queryObject.hasOrderInfo()) + qf.format("SELECT ROWNUMBER() OVER (ORDER BY %1$s %2$s) AS RN, ", orderMetadata.getOrderName("i", "md"), rp.getOrderDirection().name()); + else + resultQuery.append("SELECT ROWNUMBER() OVER () AS RN, "); + resultQuery.append("i.ID, i.TITLE, i.DESCRIPTION, i.ITEMTYPEID, i.HOUSEID, i.CREATED, i.ISFOLDER FROM ITEM i "); + String orderTableName = orderMetadata.getTableName(); + if (orderTableName != null) + qf.format("LEFT OUTER JOIN %1$s md ON (md.ID = i.ID) ", orderTableName); + resultQuery.append("WHERE i.ID IN "); + qf.format("(SELECT ii.ID %1$s AND ii.ITEMTYPEID = %2$d))", innerQuery, csr.getItemType().getId()); + qf.format(" SELECT * FROM NUMBEREDITEM WHERE RN BETWEEN %1$d AND %2$d", bottom, top); + } else { + resultQuery.append("SELECT i.ID, i.TITLE, i.DESCRIPTION, i.ITEMTYPEID, i.HOUSEID, i.CREATED, i.ISFOLDER FROM ITEM i "); + resultQuery.append("WHERE i.ID IN "); + qf.format("(SELECT ii.ID %1$s) ORDER BY i.TITLE", innerQuery); + } + + logger.trace(resultQuery.toString()); + return resultQuery.toString(); + } + + private PreparedStatement createArchiveMediaSearchStatement(Connection connection, String criteria, String orderBy, boolean orderAscending, + List typeIDs) throws SQLException { + StringBuilder typeCriteria = new StringBuilder(); + if (typeIDs != null) { + typeCriteria.append("("); + for (int i = 0; i < typeIDs.size(); i++) { + typeCriteria.append(typeIDs.get(i)); + if (i < typeIDs.size() - 1) + typeCriteria.append(","); + } + typeCriteria.append(")"); + } + + StringBuilder sql = new StringBuilder(); + sql.append("SELECT "); + sql.append(getColumnNames()); + sql.append( + " FROM ITEM i, MEDIA m, MEDIAFILE mf, MEDIADESCRIPTION md, VW_MEDIAFILES vw_mf, ITEMTYPE it WHERE i.id = md.itemid AND m.id = md.mediaid AND mf.id = md.mediafileid AND m.id=vw_mf.mediaid AND m.itemTypeId = it.id"); + if (typeIDs != null) { + sql.append(" AND m.itemtypeid IN "); + sql.append(typeCriteria.toString()); + } + + sql.append(" AND CONTAINS(md.description, ?) >= 1"); + sql.append(" ORDER BY "); + + if (orderBy == null) { + sql.append("itemtitle desc, mediaitemtypeid"); + } else { + sql.append(orderBy); + sql.append(orderAscending ? " ASC" : " DESC"); + } + + String query = sql.toString(); + logger.info(query); + PreparedStatement st = connection.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); + st.setString(1, criteria); + return st; + } + + private PreparedStatement createArchiveMediaSearchStatement1(Connection connection, String criteria, String orderBy, boolean orderAscending, + List typeIDs) throws SQLException { + StringBuilder typeCriteria = new StringBuilder(); + if (typeIDs != null) { + typeCriteria.append("("); + for (int i = 0; i < typeIDs.size(); i++) { + typeCriteria.append(typeIDs.get(i)); + if (i < typeIDs.size() - 1) + typeCriteria.append(","); + } + typeCriteria.append(")"); + } + + String columNames = getColumnNames(); + StringBuilder innerSql = new StringBuilder(); + innerSql.append("SELECT DISTINCT "); + innerSql.append(columNames); + innerSql.append(" FROM ITEM i, MEDIA m, MEDIAFILE mf WHERE m.itemid = i.id AND mf.mediaid = m.id AND mf.storeid = 1"); + if (typeIDs != null) { + innerSql.append(" AND m.itemtypeid IN "); + innerSql.append(typeCriteria.toString()); + } + + StringBuilder sql = new StringBuilder(); + sql.append("SELECT * FROM (("); + sql.append(innerSql); + sql.append(" AND ((CONTAINS(i.title, ?) >= 1) OR (CONTAINS(i.description, ?) >= 1))"); + sql.append(") UNION ("); + sql.append(innerSql); + sql.append(" AND ((CONTAINS(m.title, ?) >= 1) OR (CONTAINS(m.description, ?) >= 1))"); + sql.append(") UNION ("); + sql.append(innerSql); + sql.append(" AND (CONTAINS(mf.houseId, ?) >= 1)"); + sql.append(")) ORDER BY "); + + if (orderBy == null) { + sql.append("itemtitle desc, mediaitemtypeid"); + } else { + sql.append(orderBy); + sql.append(orderAscending ? " ASC" : " DESC"); + } + + String query = sql.toString(); + logger.info(query); + PreparedStatement st = connection.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); + + for (int s = 1; s <= 5; s++) { + st.setString(s, criteria); + } + return st; + } + + private String getColumnNames() { + StringBuilder columns = new StringBuilder(); + columns.append("i.id itemid,"); + columns.append("i.title itemtitle,"); + columns.append("m.id mediaid,"); + columns.append("m.title mediatitle,"); + columns.append("m.archived,"); + columns.append("m.length,"); + columns.append("m.itemtypeid mediaitemtypeid,"); + columns.append("it.name mediaitemtypename,"); + columns.append("m.houseid mediahouseid,"); + columns.append("mf.houseid mediafilehouseid,"); + columns.append("vw_mf.mediafilecount"); + return columns.toString(); + } + + private List getResultHeaders(Connection connection, QueryObject queryObject, String innerQuery) { + List results = new ArrayList(); + String typeQuery; + if (queryObject.getParameters() == null) + typeQuery = String.format("SELECT CAST(COUNT(DISTINCT(ii.ID)) AS BIGINT) %s", innerQuery); + else + typeQuery = String.format("SELECT ii.ITEMTYPEID, CAST(COUNT(DISTINCT(ii.ID)) AS BIGINT) %s GROUP BY ii.ITEMTYPEID", innerQuery); + + logger.trace(typeQuery.toString()); + PreparedStatement st = null; + ResultSet rs = null; + try { + st = connection.prepareStatement(typeQuery); + rs = st.executeQuery(); + while (rs.next()) { + SearchResult sr = new SearchResult(); + if (queryObject.getParameters() == null) { + sr.setItemCount(rs.getLong(1)); + } else { + ItemType itemType = (ItemType) manager.get(ItemType.class, rs.getLong(1)); + sr.setItemType(itemType); + sr.setItemCount(rs.getLong(2)); + } + + sr.setParameters(queryObject.getParameters()); + results.add(sr); + } + } catch (Exception e) { + logger.catching(e); + } finally { + try { + if (rs != null) + rs.close(); + } catch (Exception e1) { + e1.printStackTrace(); + } + try { + if (st != null) + st.close(); + } catch (Exception e1) { + e1.printStackTrace(); + } + } + return results; + } + + private List getTypeIDs(List searchFilters) { + List result = null; + if (searchFilters != null) { + for (String typeName : searchFilters) { + ItemType itemType = manager.getItemType(typeName); + if (itemType == null) + continue; + if (result == null) + result = new ArrayList<>(); + result.add(String.valueOf(itemType.getId())); + } + } + return result; + } + + public List getUntranscodedMedias() { + List result = null; + + ResultSet rs = null; + PreparedStatement st = null; + DefaultContext context = manager.getDbContext(); + Connection connection = context.getConnection(); + try { + + String query = "select m.ID, m.TITLE, m.DESCRIPTION, m.ITEMID, m.ITEMTYPEID, m.HOUSEID, m.LENGTH, m.CREATED, m.ARCHIVED, m.POSTER from MEDIA m right outer join VW_MEDIAFILES f on (f.MEDIAID = m.ID) where m.CREATED > '2017-12-14' and f.MEDIAFILECOUNT = 1"; + st = connection.prepareStatement(query); + rs = st.executeQuery(); + + while (rs.next()) { + Media m = new Media(); + m.setPersister(manager); + m.setId(rs.getLong("id")); + m.setTitle(rs.getString("title")); + m.setDescription(rs.getString("description")); + m.setItemId(rs.getLong("itemid")); + m.setItemTypeId(rs.getLong("itemtypeid")); + m.setHouseId(rs.getString("houseid")); + m.setLength(rs.getLong("length")); + m.setCreated(rs.getTimestamp("created")); + m.setArchived(rs.getTimestamp("archived")); + m.setPoster(rs.getBytes("poster")); + if (result == null) + result = new ArrayList<>(); + result.add(m); + } + + connection.commit(); + } catch (Exception e) { + try { + connection.rollback(); + } catch (Exception e1) { + } + manager.throwError(e); + } finally { + try { + if (rs != null) + rs.close(); + } catch (Exception e1) { + } + try { + if (st != null) + st.close(); + } catch (Exception e1) { + } + manager.putDbContext(context); + } + + return result; + } + + @SuppressWarnings("unchecked") + public List search(QueryObject queryObject) { + manager.traceIn(); + if (queryObject == null || queryObject.getFilters() == null || queryObject.getFilters().size() == 0) + throw new IllegalArgumentException("QueryObject not specified."); + + List results = new ArrayList(); + + ResultSet rs = null; + PreparedStatement st = null; + DefaultContext context = manager.getDbContext(); + Connection connection = context.getConnection(); + + try { + String innerQuery = buildQueryCore(queryObject); + + if (queryObject.getActiveItemTypeId() == 0) { + // keres�s az �sszes t�pusban + // Ha nincs RenderParameter, akkor az eredm�ny egy + // SearchResult-ba ker�l, egy�bk�nt + // ItemType-onk�nt t�bbe + results = getResultHeaders(connection, queryObject, innerQuery); + } else { + SearchResult sr = new SearchResult(); + ItemType itemType = (ItemType) manager.get(ItemType.class, queryObject.getActiveItemTypeId()); + sr.setItemType(itemType); + // TODO + // sr.setItemCount(rs.getLong(2)); + sr.setParameters(queryObject.getParameters()); + results.add(sr); + } + + if (results.size() != 0) { + SearchResult csr = results.get(0); + String resultQuery = buildResultQuery(queryObject, innerQuery, csr); + st = connection.prepareStatement(resultQuery); + + // int rpp = (int)parameter.getParameters().getRowPerPage(); + // st.setFetchSize(rpp); + // st.setMaxRows(rpp); + + rs = st.executeQuery(); + List items = new ArrayList(); + ItemDAO dao = (ItemDAO) manager.getBaseDAO(Item.class); + items = (List) (List) dao.entities(context, rs); + csr.setItems(items); + rs.close(); + } + + connection.commit(); + } catch (Exception e) { + try { + connection.rollback(); + } catch (Exception e1) { + e1.printStackTrace(); + } + manager.throwError(e); + } finally { + try { + if (rs != null) + rs.close(); + } catch (Exception e1) { + } + try { + if (st != null) + st.close(); + } catch (Exception e1) { + } + manager.putDbContext(context); + } + manager.traceOut(); + return results; + } + + public SearchResult search(String criteria, int fromResult, int toResult) { + SearchResult result = new SearchResult(); + + ResultSet rs = null; + PreparedStatement st = null; + DefaultContext context = manager.getDbContext(); + Connection connection = context.getConnection(); + + try { + StringBuffer sql = new StringBuffer(); + sql.append("select * from (("); + sql.append("select distinct i.id,i.itemtypeid,i.houseid,i.title,i.description,i.isfolder,i.created,i.modified from item i, media m, mediafile mf"); + sql.append( + " where ((CONTAINS(i.title, ?) >= 1) OR (CONTAINS(i.description, ?) >= 1) OR (CONTAINS(i.houseId, ?) >= 1)) and m.itemid = i.id and mf.MEDIAID = m.id"); + sql.append(")UNION("); + sql.append( + "select distinct i2.id,i2.itemtypeid,i2.houseid,i2.title,i2.description,i2.isfolder,i2.created,i2.modified from item i2, media m2, mediafile mf2"); + sql.append( + " where ((CONTAINS(m2.title, ?) >= 1) OR (CONTAINS(m2.description, ?) >= 1) OR (CONTAINS(m2.houseId, ?) >= 1)) and m2.itemid = i2.id and mf2.MEDIAID = m2.id"); + sql.append(")UNION("); + sql.append( + "select distinct i3.id,i3.itemtypeid,i3.houseid,i3.title,i3.description,i3.isfolder,i3.created,i3.modified from item i3, media m3, mediafile mf3"); + sql.append(" where (CONTAINS(mf3.houseId, ?) >= 1) and m3.itemid = i3.id and mf3.MEDIAID = m3.id"); + sql.append(")) order by title"); + String query = sql.toString(); + /* + query = "SELECT i.* from item i, media m, mediafile mf WHERE m.ITEMID = i.id AND mf.MEDIAID = m.ID" + + " AND (" + "(CONTAINS(i.TITLE, '" + criteria + "') >= 1) OR (CONTAINS(i.DESCRIPTION, '" + criteria + + "') >= 1)" + " OR (CONTAINS(m.TITLE, '" + criteria + "') >= 1) OR (CONTAINS(m.DESCRIPTION, '" + + criteria + "') >= 1)" + " OR (CONTAINS(mf.HOUSEID, '" + criteria + + "') >= 1)) ORDER BY i.title"; + */ + // query = "SELECT * FROM ITEM ORDER BY title"; + + st = connection.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); + for (int s = 1; s <= 7; s++) { + st.setString(s, criteria); + } + rs = st.executeQuery(); + rs.last(); + result.setItemCount(rs.getRow()); + + // set begining position + if (fromResult > -1) { + if (fromResult <= result.getItemCount()) { + if (fromResult > 1) { + rs.absolute(fromResult - 1); + } else { + rs.beforeFirst(); + } + } else { + if (result.getItemCount() > 0) { + throw new Exception("Invalid searchResult 'from' position: " + fromResult + " total records: " + result.getItemCount() + " " + query); + } + } + } else { + rs.beforeFirst(); + } + + int maxRecords = toResult - fromResult; + + ItemDAO dao = (ItemDAO) manager.getBaseDAO(Item.class); + result.setItems((List) (List) dao.entities(context, rs, maxRecords)); + + connection.commit(); + } catch (Exception e) { + try { + connection.rollback(); + } catch (Exception e1) { + e1.printStackTrace(); + } + manager.throwError(e); + } finally { + try { + if (rs != null) + rs.close(); + } catch (Exception e1) { + } + try { + if (st != null) + st.close(); + } catch (Exception e1) { + } + manager.putDbContext(context); + } + manager.traceOut(); + + return result; + } + + public SearchResult searchArchivedMedia(String criteria, List searchFilters, String orderBy, boolean orderAscending, int fromResult, + int toResult) { + SearchResult result = new SearchResult<>(); + + List typeIDs = getTypeIDs(searchFilters); + + ResultSet rs = null; + PreparedStatement st = null; + DefaultContext context = manager.getDbContext(); + Connection connection = context.getConnection(); + try { + + st = createArchiveMediaSearchStatement(connection, criteria, orderBy, orderAscending, typeIDs); + rs = st.executeQuery(); + rs.last(); + result.setItemCount(rs.getRow()); + + // set begining position + if (fromResult > -1) { + if (fromResult <= result.getItemCount()) { + if (fromResult > 1) { + rs.absolute(fromResult - 1); + } else { + rs.beforeFirst(); + } + } else { + if (result.getItemCount() > 0) { + throw new Exception("Invalid searchResult 'from' position: " + fromResult + " total records: " + result.getItemCount()); + } + } + } else { + rs.beforeFirst(); + } + + int maxRecords = toResult - fromResult; + + String[] criterias = criteria.replace("*", "").replace("+", " ").split(" "); + if (criteria.startsWith("\"") && criteria.endsWith("\"")) + criterias = new String[] { criteria.replace("\"", "") }; + List resultList = null; + logger.info("Processing query result, maxRecords {}", maxRecords); + + while (rs.next() && maxRecords > 0) { + ArchivedMedia am = new ArchivedMedia(); + Media media = manager.getMedia(rs.getLong("mediaid")); + am.setMedia(media); + Item item = manager.getItem(media.getItemId()); + am.setItem(item); + + if (resultList == null) + resultList = new ArrayList<>(); + resultList.add(am); + + setRelevant(criterias, am, media.getDescription()); + if (am.getRelevant() == null) + setRelevant(criterias, am, item.getDescription()); + + // media.setTitle(media.getTitle().replace("hirado", "")); + maxRecords--; + } + logger.info("Query result processed"); + result.setItems(resultList); + connection.commit(); + } catch (Exception e) { + try { + connection.rollback(); + } catch (Exception e1) { + e1.printStackTrace(); + } + manager.throwError(e); + } finally { + try { + if (rs != null) + rs.close(); + } catch (Exception e1) { + } + try { + if (st != null) + st.close(); + } catch (Exception e1) { + } + manager.putDbContext(context); + } + manager.traceOut(); + + return result; + } + + private void setRelevant(String[] criterias, ArchivedMedia am, String content) { + if (content == null) + return; + for (String c : criterias) { + if (c != null && c.length() > 2) { + + int pos = content.toLowerCase().indexOf(c.toLowerCase()); + if (pos > -1) { + int start = 0; + if (pos > 100) + start = pos - 100; + else + start = 0; + int end = pos + 200; + if (end > content.length() - 1) + end = content.length() - 1; + + String relevant = content.substring(start, pos) + "" + content.substring(pos, pos + c.length()) + "" + + content.substring(pos + c.length(), end); + + if (start > 0) + relevant = "..." + relevant; + if (end < content.length() - 1) + relevant = relevant + "..."; + + am.setRelevant(relevant); + break; + } + + } + } + } +} diff --git a/server/user.jobengine.osgi.server/pages/searchitems.zul b/server/user.jobengine.osgi.server/pages/searchitems.zul index 59d2e955..736a85a7 100644 --- a/server/user.jobengine.osgi.server/pages/searchitems.zul +++ b/server/user.jobengine.osgi.server/pages/searchitems.zul @@ -151,11 +151,7 @@