@Test public void testViewDateFilter() throws Exception { IDataSet db = database( support.loadFlatXmlDataStream(getClass(), "user.xml"), support.loadFlatXmlDataStream(getClass(), "wf_job.xml") ); support.cleanInsert(support.getConnection(tx), db); tx.commit(); JobFilter filter = new JobFilter(); filter.setCreated(Arrays.asList( ">", "2015-10-24T23:00:00", "<", "2015-10-26T23:00:00" )); List<JobView> jobs = dao.view(filter); assertEquals(1, jobs.size()); JobView job0 = jobs.get(0); assertEquals(new BigDecimal(2), job0.getId()); assertEquals("job.stt", job0.getProfileName()); }
@Override public List<JobView> view(JobFilter filter) { DBCommand cmd = db.createCommand(); cmd.select(tableJob.getColumns()); cmd.select(db.tableUser.username); final ProarcDatabase.WorkflowPhysicalDocTable tpd = db.tableWorkflowPhysicalDoc; cmd.select(tpd.barcode, tpd.detail, tpd.field001, tpd.issue, tpd.sigla, tpd.signature, tpd.volume, tpd.year); cmd.join(tableJob.ownerId, db.tableUser.id, DBJoinType.LEFT); DBCommand pmatCmd = db.createCommand(); final DBColumnExpr pmatMaterialId = db.tableWorkflowMaterial.id.min().as(db.tableWorkflowMaterial.id); pmatCmd.select(pmatMaterialId); pmatCmd.select(db.tableWorkflowTask.jobId); pmatCmd.join(db.tableWorkflowMaterial.id, db.tableWorkflowMaterialInTask.materialId); pmatCmd.join(db.tableWorkflowTask.id, db.tableWorkflowMaterialInTask.taskId); pmatCmd.where(db.tableWorkflowMaterial.type.is("PHYSICAL_DOCUMENT")); pmatCmd.groupBy(db.tableWorkflowTask.jobId); DBQuery pmatQuery = new DBQuery(pmatCmd); DBQuery.DBQueryColumn wmId = pmatQuery.findQueryColumn(pmatMaterialId); DBQuery.DBQueryColumn wjId = pmatQuery.findQueryColumn(db.tableWorkflowTask.jobId); cmd.join(tableJob.id, wjId, DBJoinType.LEFT); cmd.join(tpd.materialId, wmId, DBJoinType.RIGHT); EmpireUtils.addWhereIs(cmd, tableJob.id, () -> filter.getId()); if (filter.getLabel() != null) { String pattern = filter.getLabel().trim().replace("%", "\\%"); if (!pattern.isEmpty()) { cmd.where(tableJob.label.likeUpper('%' + pattern + '%')); } } EmpireUtils.addWhereLike(cmd, tpd.barcode, () -> filter.getMaterialBarcode()); EmpireUtils.addWhereLike(cmd, tpd.detail, () -> filter.getMaterialDetail()); EmpireUtils.addWhereLike(cmd, tpd.field001, () -> filter.getMaterialField001()); EmpireUtils.addWhereLike(cmd, tpd.issue, () -> filter.getMaterialIssue()); EmpireUtils.addWhereLike(cmd, tpd.sigla, () -> filter.getMaterialSigla()); EmpireUtils.addWhereLike(cmd, tpd.signature, () -> filter.getMaterialSignature()); EmpireUtils.addWhereLike(cmd, tpd.volume, () -> filter.getMaterialVolume()); EmpireUtils.addWhereLike(cmd, tpd.year, () -> filter.getMaterialYear()); EmpireUtils.addWhereIs(cmd, tableJob.parentId, () -> filter.getParentId()); EmpireUtils.addWhereIs(cmd, tableJob.profileName, () -> filter.getProfileName()); EmpireUtils.addWhereIs(cmd, tableJob.state, () -> filter.getState() == null ? null : filter.getState().name()); EmpireUtils.addWhereIs(cmd, tableJob.ownerId, () -> filter.getUserId()); EmpireUtils.addWhereIs(cmd, tableJob.priority, () -> filter.getPriority()); EmpireUtils.addWhereDate(cmd, tableJob.created, filter.getCreated()); EmpireUtils.addWhereDate(cmd, tableJob.timestamp, filter.getModified()); EmpireUtils.addOrderBy(cmd, filter.getSortBy(), tableJob.timestamp, true); DBReader reader = new DBReader(); try { reader.open(cmd, getConnection()); if (!reader.skipRows(filter.getOffset())) { return Collections.emptyList(); } ArrayList<JobView> viewItems = new ArrayList<JobView>(filter.getMaxCount()); for (Iterator<DBRecordData> it = reader.iterator(filter.getMaxCount()); it.hasNext();) { DBRecordData rec = it.next(); JobView view = new JobView(); rec.getBeanProperties(view); viewItems.add(view); } return viewItems; } finally { reader.close(); } }
EmpireWorkflowJobDao extends EmpireDao implements WorkflowJobDao { @Override public List<JobView> view(JobFilter filter) { DBCommand cmd = db.createCommand(); cmd.select(tableJob.getColumns()); cmd.select(db.tableUser.username); final ProarcDatabase.WorkflowPhysicalDocTable tpd = db.tableWorkflowPhysicalDoc; cmd.select(tpd.barcode, tpd.detail, tpd.field001, tpd.issue, tpd.sigla, tpd.signature, tpd.volume, tpd.year); cmd.join(tableJob.ownerId, db.tableUser.id, DBJoinType.LEFT); DBCommand pmatCmd = db.createCommand(); final DBColumnExpr pmatMaterialId = db.tableWorkflowMaterial.id.min().as(db.tableWorkflowMaterial.id); pmatCmd.select(pmatMaterialId); pmatCmd.select(db.tableWorkflowTask.jobId); pmatCmd.join(db.tableWorkflowMaterial.id, db.tableWorkflowMaterialInTask.materialId); pmatCmd.join(db.tableWorkflowTask.id, db.tableWorkflowMaterialInTask.taskId); pmatCmd.where(db.tableWorkflowMaterial.type.is("PHYSICAL_DOCUMENT")); pmatCmd.groupBy(db.tableWorkflowTask.jobId); DBQuery pmatQuery = new DBQuery(pmatCmd); DBQuery.DBQueryColumn wmId = pmatQuery.findQueryColumn(pmatMaterialId); DBQuery.DBQueryColumn wjId = pmatQuery.findQueryColumn(db.tableWorkflowTask.jobId); cmd.join(tableJob.id, wjId, DBJoinType.LEFT); cmd.join(tpd.materialId, wmId, DBJoinType.RIGHT); EmpireUtils.addWhereIs(cmd, tableJob.id, () -> filter.getId()); if (filter.getLabel() != null) { String pattern = filter.getLabel().trim().replace("%", "\\%"); if (!pattern.isEmpty()) { cmd.where(tableJob.label.likeUpper('%' + pattern + '%')); } } EmpireUtils.addWhereLike(cmd, tpd.barcode, () -> filter.getMaterialBarcode()); EmpireUtils.addWhereLike(cmd, tpd.detail, () -> filter.getMaterialDetail()); EmpireUtils.addWhereLike(cmd, tpd.field001, () -> filter.getMaterialField001()); EmpireUtils.addWhereLike(cmd, tpd.issue, () -> filter.getMaterialIssue()); EmpireUtils.addWhereLike(cmd, tpd.sigla, () -> filter.getMaterialSigla()); EmpireUtils.addWhereLike(cmd, tpd.signature, () -> filter.getMaterialSignature()); EmpireUtils.addWhereLike(cmd, tpd.volume, () -> filter.getMaterialVolume()); EmpireUtils.addWhereLike(cmd, tpd.year, () -> filter.getMaterialYear()); EmpireUtils.addWhereIs(cmd, tableJob.parentId, () -> filter.getParentId()); EmpireUtils.addWhereIs(cmd, tableJob.profileName, () -> filter.getProfileName()); EmpireUtils.addWhereIs(cmd, tableJob.state, () -> filter.getState() == null ? null : filter.getState().name()); EmpireUtils.addWhereIs(cmd, tableJob.ownerId, () -> filter.getUserId()); EmpireUtils.addWhereIs(cmd, tableJob.priority, () -> filter.getPriority()); EmpireUtils.addWhereDate(cmd, tableJob.created, filter.getCreated()); EmpireUtils.addWhereDate(cmd, tableJob.timestamp, filter.getModified()); EmpireUtils.addOrderBy(cmd, filter.getSortBy(), tableJob.timestamp, true); DBReader reader = new DBReader(); try { reader.open(cmd, getConnection()); if (!reader.skipRows(filter.getOffset())) { return Collections.emptyList(); } ArrayList<JobView> viewItems = new ArrayList<JobView>(filter.getMaxCount()); for (Iterator<DBRecordData> it = reader.iterator(filter.getMaxCount()); it.hasNext();) { DBRecordData rec = it.next(); JobView view = new JobView(); rec.getBeanProperties(view); viewItems.add(view); } return viewItems; } finally { reader.close(); } } }
EmpireWorkflowJobDao extends EmpireDao implements WorkflowJobDao { @Override public List<JobView> view(JobFilter filter) { DBCommand cmd = db.createCommand(); cmd.select(tableJob.getColumns()); cmd.select(db.tableUser.username); final ProarcDatabase.WorkflowPhysicalDocTable tpd = db.tableWorkflowPhysicalDoc; cmd.select(tpd.barcode, tpd.detail, tpd.field001, tpd.issue, tpd.sigla, tpd.signature, tpd.volume, tpd.year); cmd.join(tableJob.ownerId, db.tableUser.id, DBJoinType.LEFT); DBCommand pmatCmd = db.createCommand(); final DBColumnExpr pmatMaterialId = db.tableWorkflowMaterial.id.min().as(db.tableWorkflowMaterial.id); pmatCmd.select(pmatMaterialId); pmatCmd.select(db.tableWorkflowTask.jobId); pmatCmd.join(db.tableWorkflowMaterial.id, db.tableWorkflowMaterialInTask.materialId); pmatCmd.join(db.tableWorkflowTask.id, db.tableWorkflowMaterialInTask.taskId); pmatCmd.where(db.tableWorkflowMaterial.type.is("PHYSICAL_DOCUMENT")); pmatCmd.groupBy(db.tableWorkflowTask.jobId); DBQuery pmatQuery = new DBQuery(pmatCmd); DBQuery.DBQueryColumn wmId = pmatQuery.findQueryColumn(pmatMaterialId); DBQuery.DBQueryColumn wjId = pmatQuery.findQueryColumn(db.tableWorkflowTask.jobId); cmd.join(tableJob.id, wjId, DBJoinType.LEFT); cmd.join(tpd.materialId, wmId, DBJoinType.RIGHT); EmpireUtils.addWhereIs(cmd, tableJob.id, () -> filter.getId()); if (filter.getLabel() != null) { String pattern = filter.getLabel().trim().replace("%", "\\%"); if (!pattern.isEmpty()) { cmd.where(tableJob.label.likeUpper('%' + pattern + '%')); } } EmpireUtils.addWhereLike(cmd, tpd.barcode, () -> filter.getMaterialBarcode()); EmpireUtils.addWhereLike(cmd, tpd.detail, () -> filter.getMaterialDetail()); EmpireUtils.addWhereLike(cmd, tpd.field001, () -> filter.getMaterialField001()); EmpireUtils.addWhereLike(cmd, tpd.issue, () -> filter.getMaterialIssue()); EmpireUtils.addWhereLike(cmd, tpd.sigla, () -> filter.getMaterialSigla()); EmpireUtils.addWhereLike(cmd, tpd.signature, () -> filter.getMaterialSignature()); EmpireUtils.addWhereLike(cmd, tpd.volume, () -> filter.getMaterialVolume()); EmpireUtils.addWhereLike(cmd, tpd.year, () -> filter.getMaterialYear()); EmpireUtils.addWhereIs(cmd, tableJob.parentId, () -> filter.getParentId()); EmpireUtils.addWhereIs(cmd, tableJob.profileName, () -> filter.getProfileName()); EmpireUtils.addWhereIs(cmd, tableJob.state, () -> filter.getState() == null ? null : filter.getState().name()); EmpireUtils.addWhereIs(cmd, tableJob.ownerId, () -> filter.getUserId()); EmpireUtils.addWhereIs(cmd, tableJob.priority, () -> filter.getPriority()); EmpireUtils.addWhereDate(cmd, tableJob.created, filter.getCreated()); EmpireUtils.addWhereDate(cmd, tableJob.timestamp, filter.getModified()); EmpireUtils.addOrderBy(cmd, filter.getSortBy(), tableJob.timestamp, true); DBReader reader = new DBReader(); try { reader.open(cmd, getConnection()); if (!reader.skipRows(filter.getOffset())) { return Collections.emptyList(); } ArrayList<JobView> viewItems = new ArrayList<JobView>(filter.getMaxCount()); for (Iterator<DBRecordData> it = reader.iterator(filter.getMaxCount()); it.hasNext();) { DBRecordData rec = it.next(); JobView view = new JobView(); rec.getBeanProperties(view); viewItems.add(view); } return viewItems; } finally { reader.close(); } } EmpireWorkflowJobDao(ProarcDatabase db); }
EmpireWorkflowJobDao extends EmpireDao implements WorkflowJobDao { @Override public List<JobView> view(JobFilter filter) { DBCommand cmd = db.createCommand(); cmd.select(tableJob.getColumns()); cmd.select(db.tableUser.username); final ProarcDatabase.WorkflowPhysicalDocTable tpd = db.tableWorkflowPhysicalDoc; cmd.select(tpd.barcode, tpd.detail, tpd.field001, tpd.issue, tpd.sigla, tpd.signature, tpd.volume, tpd.year); cmd.join(tableJob.ownerId, db.tableUser.id, DBJoinType.LEFT); DBCommand pmatCmd = db.createCommand(); final DBColumnExpr pmatMaterialId = db.tableWorkflowMaterial.id.min().as(db.tableWorkflowMaterial.id); pmatCmd.select(pmatMaterialId); pmatCmd.select(db.tableWorkflowTask.jobId); pmatCmd.join(db.tableWorkflowMaterial.id, db.tableWorkflowMaterialInTask.materialId); pmatCmd.join(db.tableWorkflowTask.id, db.tableWorkflowMaterialInTask.taskId); pmatCmd.where(db.tableWorkflowMaterial.type.is("PHYSICAL_DOCUMENT")); pmatCmd.groupBy(db.tableWorkflowTask.jobId); DBQuery pmatQuery = new DBQuery(pmatCmd); DBQuery.DBQueryColumn wmId = pmatQuery.findQueryColumn(pmatMaterialId); DBQuery.DBQueryColumn wjId = pmatQuery.findQueryColumn(db.tableWorkflowTask.jobId); cmd.join(tableJob.id, wjId, DBJoinType.LEFT); cmd.join(tpd.materialId, wmId, DBJoinType.RIGHT); EmpireUtils.addWhereIs(cmd, tableJob.id, () -> filter.getId()); if (filter.getLabel() != null) { String pattern = filter.getLabel().trim().replace("%", "\\%"); if (!pattern.isEmpty()) { cmd.where(tableJob.label.likeUpper('%' + pattern + '%')); } } EmpireUtils.addWhereLike(cmd, tpd.barcode, () -> filter.getMaterialBarcode()); EmpireUtils.addWhereLike(cmd, tpd.detail, () -> filter.getMaterialDetail()); EmpireUtils.addWhereLike(cmd, tpd.field001, () -> filter.getMaterialField001()); EmpireUtils.addWhereLike(cmd, tpd.issue, () -> filter.getMaterialIssue()); EmpireUtils.addWhereLike(cmd, tpd.sigla, () -> filter.getMaterialSigla()); EmpireUtils.addWhereLike(cmd, tpd.signature, () -> filter.getMaterialSignature()); EmpireUtils.addWhereLike(cmd, tpd.volume, () -> filter.getMaterialVolume()); EmpireUtils.addWhereLike(cmd, tpd.year, () -> filter.getMaterialYear()); EmpireUtils.addWhereIs(cmd, tableJob.parentId, () -> filter.getParentId()); EmpireUtils.addWhereIs(cmd, tableJob.profileName, () -> filter.getProfileName()); EmpireUtils.addWhereIs(cmd, tableJob.state, () -> filter.getState() == null ? null : filter.getState().name()); EmpireUtils.addWhereIs(cmd, tableJob.ownerId, () -> filter.getUserId()); EmpireUtils.addWhereIs(cmd, tableJob.priority, () -> filter.getPriority()); EmpireUtils.addWhereDate(cmd, tableJob.created, filter.getCreated()); EmpireUtils.addWhereDate(cmd, tableJob.timestamp, filter.getModified()); EmpireUtils.addOrderBy(cmd, filter.getSortBy(), tableJob.timestamp, true); DBReader reader = new DBReader(); try { reader.open(cmd, getConnection()); if (!reader.skipRows(filter.getOffset())) { return Collections.emptyList(); } ArrayList<JobView> viewItems = new ArrayList<JobView>(filter.getMaxCount()); for (Iterator<DBRecordData> it = reader.iterator(filter.getMaxCount()); it.hasNext();) { DBRecordData rec = it.next(); JobView view = new JobView(); rec.getBeanProperties(view); viewItems.add(view); } return viewItems; } finally { reader.close(); } } EmpireWorkflowJobDao(ProarcDatabase db); @Override Job create(); @Override void update(Job job); @Override Job find(BigDecimal id); @Override List<JobView> view(JobFilter filter); }
EmpireWorkflowJobDao extends EmpireDao implements WorkflowJobDao { @Override public List<JobView> view(JobFilter filter) { DBCommand cmd = db.createCommand(); cmd.select(tableJob.getColumns()); cmd.select(db.tableUser.username); final ProarcDatabase.WorkflowPhysicalDocTable tpd = db.tableWorkflowPhysicalDoc; cmd.select(tpd.barcode, tpd.detail, tpd.field001, tpd.issue, tpd.sigla, tpd.signature, tpd.volume, tpd.year); cmd.join(tableJob.ownerId, db.tableUser.id, DBJoinType.LEFT); DBCommand pmatCmd = db.createCommand(); final DBColumnExpr pmatMaterialId = db.tableWorkflowMaterial.id.min().as(db.tableWorkflowMaterial.id); pmatCmd.select(pmatMaterialId); pmatCmd.select(db.tableWorkflowTask.jobId); pmatCmd.join(db.tableWorkflowMaterial.id, db.tableWorkflowMaterialInTask.materialId); pmatCmd.join(db.tableWorkflowTask.id, db.tableWorkflowMaterialInTask.taskId); pmatCmd.where(db.tableWorkflowMaterial.type.is("PHYSICAL_DOCUMENT")); pmatCmd.groupBy(db.tableWorkflowTask.jobId); DBQuery pmatQuery = new DBQuery(pmatCmd); DBQuery.DBQueryColumn wmId = pmatQuery.findQueryColumn(pmatMaterialId); DBQuery.DBQueryColumn wjId = pmatQuery.findQueryColumn(db.tableWorkflowTask.jobId); cmd.join(tableJob.id, wjId, DBJoinType.LEFT); cmd.join(tpd.materialId, wmId, DBJoinType.RIGHT); EmpireUtils.addWhereIs(cmd, tableJob.id, () -> filter.getId()); if (filter.getLabel() != null) { String pattern = filter.getLabel().trim().replace("%", "\\%"); if (!pattern.isEmpty()) { cmd.where(tableJob.label.likeUpper('%' + pattern + '%')); } } EmpireUtils.addWhereLike(cmd, tpd.barcode, () -> filter.getMaterialBarcode()); EmpireUtils.addWhereLike(cmd, tpd.detail, () -> filter.getMaterialDetail()); EmpireUtils.addWhereLike(cmd, tpd.field001, () -> filter.getMaterialField001()); EmpireUtils.addWhereLike(cmd, tpd.issue, () -> filter.getMaterialIssue()); EmpireUtils.addWhereLike(cmd, tpd.sigla, () -> filter.getMaterialSigla()); EmpireUtils.addWhereLike(cmd, tpd.signature, () -> filter.getMaterialSignature()); EmpireUtils.addWhereLike(cmd, tpd.volume, () -> filter.getMaterialVolume()); EmpireUtils.addWhereLike(cmd, tpd.year, () -> filter.getMaterialYear()); EmpireUtils.addWhereIs(cmd, tableJob.parentId, () -> filter.getParentId()); EmpireUtils.addWhereIs(cmd, tableJob.profileName, () -> filter.getProfileName()); EmpireUtils.addWhereIs(cmd, tableJob.state, () -> filter.getState() == null ? null : filter.getState().name()); EmpireUtils.addWhereIs(cmd, tableJob.ownerId, () -> filter.getUserId()); EmpireUtils.addWhereIs(cmd, tableJob.priority, () -> filter.getPriority()); EmpireUtils.addWhereDate(cmd, tableJob.created, filter.getCreated()); EmpireUtils.addWhereDate(cmd, tableJob.timestamp, filter.getModified()); EmpireUtils.addOrderBy(cmd, filter.getSortBy(), tableJob.timestamp, true); DBReader reader = new DBReader(); try { reader.open(cmd, getConnection()); if (!reader.skipRows(filter.getOffset())) { return Collections.emptyList(); } ArrayList<JobView> viewItems = new ArrayList<JobView>(filter.getMaxCount()); for (Iterator<DBRecordData> it = reader.iterator(filter.getMaxCount()); it.hasNext();) { DBRecordData rec = it.next(); JobView view = new JobView(); rec.getBeanProperties(view); viewItems.add(view); } return viewItems; } finally { reader.close(); } } EmpireWorkflowJobDao(ProarcDatabase db); @Override Job create(); @Override void update(Job job); @Override Job find(BigDecimal id); @Override List<JobView> view(JobFilter filter);  }