Example usage for org.hibernate.criterion Restrictions sqlRestriction

List of usage examples for org.hibernate.criterion Restrictions sqlRestriction

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.criterion.*
  5. Restrictions
  6. sqlRestriction

Introduction

In this page you can find the example usage for org.hibernate.criterion Restrictions sqlRestriction.

Prototype

public static Criterion sqlRestriction(String sql)

Source Link

Document

Apply a constraint expressed in SQL with no JDBC parameters.

Usage

From source file:fr.gael.dhus.olingo.v1.SQLVisitor.java

License:Open Source License

private Criterion getCriterionComparative(BinaryOperator operator, Object left, Object right) {
    Criterion criterion = null;/*from   ww w.  ja va2  s .  c o m*/
    if (left instanceof Member) {
        if (right instanceof Member) {
            // property <operator> property
            String lvalue = ((Member) left).getName();
            String rvalue = ((Member) right).getName();
            switch (operator) {
            case EQ: {
                criterion = Restrictions.eqProperty(lvalue, rvalue);
                break;
            }
            case NE: {
                criterion = Restrictions.neProperty(lvalue, rvalue);
                break;
            }
            case GT: {
                criterion = Restrictions.gtProperty(lvalue, rvalue);
                break;
            }
            case GE: {
                criterion = Restrictions.geProperty(lvalue, rvalue);
                break;
            }
            case LT: {
                criterion = Restrictions.ltProperty(lvalue, rvalue);
                break;
            }
            case LE: {
                criterion = Restrictions.leProperty(lvalue, rvalue);
                break;
            }
            default:
                throw new UnsupportedOperationException("Unsupported operation: " + operator.toUriLiteral());
            }
        } else {
            // property <operator> literal
            String property = ((Member) left).getName();
            criterion = internalCriterionComparative(operator, property, right);
        }
    } else if (right instanceof Member) {
        // literal <operator> property
        String property = ((Member) right).getName();
        criterion = internalCriterionComparative(operator, property, left);
    } else if (left instanceof Comparable) {
        // literal <operator> literal
        Comparable comparable = (Comparable) left;
        boolean bool;
        int result = comparable.compareTo(right);
        switch (operator) {
        case EQ: {
            bool = result == 0;
            break;
        }
        case NE: {
            bool = result != 0;
            break;
        }
        case GT: {
            bool = result > 0;
            break;
        }
        case GE: {
            bool = result >= 0;
            break;
        }
        case LT: {
            bool = result < 0;
            break;
        }
        case LE: {
            bool = result <= 0;
            break;
        }
        default:
            throw new UnsupportedOperationException("Unsupported operation: " + operator.toUriLiteral());
        }
        if (bool) {
            criterion = Restrictions.sqlRestriction("0=0");
        } else {
            criterion = Restrictions.sqlRestriction("0<>0");
        }
    }
    return criterion;
}

From source file:fr.mael.microrss.dao.impl.GenericDaoImpl.java

License:Open Source License

/**
 * @see fr.mael.jmusic.dao.GenericDao#selectRandom(int)
 *//*from  w  w  w.ja v  a 2  s  . c  o  m*/
@Override
public List<T> selectRandom(int nb) {
    Criteria criteria = sessionFactory.getCurrentSession().createCriteria(getPersistentClass());
    criteria.add(Restrictions.sqlRestriction("1=1 order by rand() limit " + nb));
    return (List<T>) criteria.list();
}

From source file:fsl.ta.toms.roms.dao.impl.ReportDAOImpl.java

@Override
public SummonsReportBO summonsReport(SummonsReportCriteriaBO reportCriteria, String userName, String userRegion,
        ReportDisplayInformationDAOImpl reportDisplayInformation) {
    Criteria criteria = this.hibernateTemplate.getSessionFactory().getCurrentSession()
            .createCriteria(SummonsDO.class, "s");
    criteria.createAlias("s.roadOperation", "ro", Criteria.LEFT_JOIN);
    criteria.createAlias("s.offender", "of", Criteria.LEFT_JOIN);
    criteria.createAlias("s.status", "st", Criteria.LEFT_JOIN);
    criteria.createAlias("ro.category", "c", Criteria.LEFT_JOIN);
    criteria.createAlias("s.taStaff", "ta", Criteria.LEFT_JOIN);
    criteria.createAlias("ta.person", "taPerson", Criteria.LEFT_JOIN);

    Criterion mainCriterion = Restrictions.sqlRestriction("1=1");

    if (reportCriteria.getOperationStartDate() != null && reportCriteria.getOperationEndDate() != null) {
        Date reportStartDate = DateUtils.searchDateFormater(reportCriteria.getOperationStartDate(),
                DateUtils.SEARCHDATETYPE.START);
        Date reportEndDate = DateUtils.searchDateFormater(reportCriteria.getOperationEndDate(),
                DateUtils.SEARCHDATETYPE.END);

        Criterion scheduledDate = Restrictions.or(
                Restrictions.between("ro.scheduledStartDtime", reportStartDate, reportEndDate),
                Restrictions.between("ro.scheduledEndDtime", reportStartDate, reportEndDate));

        Criterion actualDate = Restrictions.or(
                Restrictions.between("ro.actualStartDtime", reportStartDate, reportEndDate),
                Restrictions.between("ro.actualEndDtime", reportStartDate, reportEndDate));

        mainCriterion = Restrictions.and(mainCriterion, Restrictions.or(scheduledDate, actualDate));
    }//from   w w w. ja va  2 s  .  c  o m

    if (reportCriteria.getOffenceStartDate() != null && reportCriteria.getOffenceEndDate() != null) {
        mainCriterion = Restrictions.and(mainCriterion,
                Restrictions.between("s.offenceDtime",
                        DateUtils.searchDateFormater(reportCriteria.getOffenceStartDate(),
                                DateUtils.SEARCHDATETYPE.START),
                        DateUtils.searchDateFormater(reportCriteria.getOffenceEndDate(),
                                DateUtils.SEARCHDATETYPE.END)));
    }

    if (reportCriteria.getIssuedStartDate() != null && reportCriteria.getIssuedEndDate() != null) {
        mainCriterion = Restrictions.and(mainCriterion, Restrictions.between("s.issueDate",
                DateUtils.searchDateFormater(reportCriteria.getIssuedStartDate(),
                        DateUtils.SEARCHDATETYPE.START),
                DateUtils.searchDateFormater(reportCriteria.getIssuedEndDate(), DateUtils.SEARCHDATETYPE.END)));
    }

    if (reportCriteria.getPrintStartDate() != null && reportCriteria.getPrintEndDate() != null) {
        mainCriterion = Restrictions.and(mainCriterion, Restrictions.between("s.printDtime",
                DateUtils.searchDateFormater(reportCriteria.getPrintStartDate(),
                        DateUtils.SEARCHDATETYPE.START),
                DateUtils.searchDateFormater(reportCriteria.getPrintEndDate(), DateUtils.SEARCHDATETYPE.END)));

    }

    if (reportCriteria.getOffenderId() != null && reportCriteria.getOffenderId() > 0) {
        mainCriterion = Restrictions.and(mainCriterion,
                Restrictions.eq("of.personId", reportCriteria.getOffenderId()));
    }

    if (StringUtil.isSet(reportCriteria.getOffenderTRN())) {
        mainCriterion = Restrictions.and(mainCriterion,
                Restrictions.eq("of.trnNbr", reportCriteria.getOffenderTRN().trim()));
    }

    if (reportCriteria.getOperationCategory() != null && !reportCriteria.getOperationCategory().isEmpty()) {
        mainCriterion = Restrictions.and(mainCriterion,
                Restrictions.eq("c.categoryId", reportCriteria.getOperationCategory().trim()));

    }

    if (reportCriteria.getRoadOperationId() != null && reportCriteria.getRoadOperationId() > 0) {
        mainCriterion = Restrictions.and(mainCriterion,
                Restrictions.eq("ro.roadOperationId", reportCriteria.getRoadOperationId()));
    }

    if (reportCriteria.getTAOfficeRegion() != null && !reportCriteria.getTAOfficeRegion().isEmpty()) {
        mainCriterion = Restrictions.and(mainCriterion,
                Restrictions.eq("ro.officeLocCode", reportCriteria.getTAOfficeRegion().trim()));
    }

    if (reportCriteria.getTAStaffId() != null && !reportCriteria.getTAStaffId().isEmpty()) {
        mainCriterion = Restrictions.and(mainCriterion,
                Restrictions.eq("ta.staffId", reportCriteria.getTAStaffId().trim()));
    }

    if (StringUtil.isSet(reportCriteria.getTAStaffTRN())) {
        mainCriterion = Restrictions.and(mainCriterion,
                Restrictions.eq("taPerson.trnNbr", reportCriteria.getTAStaffTRN().trim()));
    }

    if (StringUtil.isSet(reportCriteria.getRoadOperationName())) {
        mainCriterion = Restrictions.and(mainCriterion, Restrictions
                .like("ro.operationName", reportCriteria.getRoadOperationName().trim(), MatchMode.ANYWHERE)
                .ignoreCase());
    }

    if (StringUtil.isSet(reportCriteria.getStatus())) {
        mainCriterion = Restrictions.and(mainCriterion,
                Restrictions.eq("s.status.statusId", reportCriteria.getStatus().trim()));
    }

    if (StringUtil.isSet(reportCriteria.getOffenderFirstName())) {
        mainCriterion = Restrictions.and(mainCriterion,
                Restrictions
                        .like("of.firstName", reportCriteria.getOffenderFirstName().trim(), MatchMode.ANYWHERE)
                        .ignoreCase());
    }

    if (StringUtil.isSet(reportCriteria.getOffenderLastName())) {
        mainCriterion = Restrictions.and(mainCriterion,
                Restrictions
                        .like("of.lastName", reportCriteria.getOffenderLastName().trim(), MatchMode.ANYWHERE)
                        .ignoreCase());
    }

    criteria.add(mainCriterion);

    String stringStartDate = "";
    String stringEndDate = "";
    String dateTypeUsed = "";
    Date startDate = null;
    Date endDate = null;
    try {
        if (reportCriteria.getOperationStartDate() != null && reportCriteria.getOperationEndDate() != null) {
            startDate = reportCriteria.getOperationStartDate();
            endDate = reportCriteria.getOperationEndDate();
            stringStartDate = DateUtils.getFormattedUtilDate(reportCriteria.getOperationStartDate());
            stringEndDate = DateUtils.getFormattedUtilDate(reportCriteria.getOperationEndDate());

            dateTypeUsed = "Operation";
        } else if (reportCriteria.getOffenceStartDate() != null && reportCriteria.getOffenceEndDate() != null) {
            startDate = reportCriteria.getOffenceStartDate();
            endDate = reportCriteria.getOffenceEndDate();
            stringStartDate = DateUtils.getFormattedUtilDate(reportCriteria.getOffenceStartDate());
            stringEndDate = DateUtils.getFormattedUtilDate(reportCriteria.getOffenceEndDate());

            dateTypeUsed = "Offence";
        } else if (reportCriteria.getPrintStartDate() != null && reportCriteria.getPrintEndDate() != null) {
            startDate = reportCriteria.getPrintStartDate();
            endDate = reportCriteria.getPrintEndDate();
            stringStartDate = DateUtils.getFormattedUtilDate(reportCriteria.getPrintStartDate());
            stringEndDate = DateUtils.getFormattedUtilDate(reportCriteria.getPrintEndDate());

            dateTypeUsed = "Print";
        } else if (reportCriteria.getIssuedStartDate() != reportCriteria.getIssuedEndDate()) {
            startDate = reportCriteria.getIssuedStartDate();
            endDate = reportCriteria.getIssuedEndDate();
            stringStartDate = DateUtils.getFormattedUtilDate(reportCriteria.getIssuedStartDate());
            stringEndDate = DateUtils.getFormattedUtilDate(reportCriteria.getIssuedEndDate());

            dateTypeUsed = "Issued";
        }
    } catch (Exception exe) {

    }

    /* Get report criteria names and descriptions */
    if (reportCriteria.getOffenderId() != null && reportCriteria.getOffenderId() > 0) {
        reportCriteria.setOffenderName(this.getPersonName(reportCriteria.getOffenderId()));
    } else if (StringUtil.isSet(reportCriteria.getOffenderTRN())) {
        reportCriteria.setOffenderName(this.getPersonName(reportCriteria.getOffenderTRN()));
    }

    reportCriteria
            .setOperationCategoryDesc(this.getOperationCategoryDesc(reportCriteria.getOperationCategory()));
    reportCriteria.setRoadOperationName(reportCriteria.getRoadOperationName());

    reportCriteria.setTAOfficeRegionDesc(this.getTAOfficeRegionDescription(reportCriteria.getTAOfficeRegion()));

    if (StringUtil.isSet(reportCriteria.getTAStaffId()))
        reportCriteria.setTAStaffName(this.getTAStaffName(reportCriteria.getTAStaffId()));
    else if (StringUtil.isSet(reportCriteria.getTAStaffTRN()))
        reportCriteria.setTAStaffName(this.getPersonName(reportCriteria.getTAStaffTRN()));
    /*
     * reportCriteria.setRoadOperationDesc(this.getRoadOperationName(
     * reportCriteria.getRoadOperationId()));
     */
    /* __________________________________________ */

    SummonsReportBO report = new SummonsReportBO(userName, userRegion, reportDisplayInformation.applicationName,
            reportDisplayInformation.summonsReportTitle + "(" + dateTypeUsed + ") " + stringStartDate + " TO "
                    + stringEndDate,
            startDate, endDate, reportCriteria.getSearchCriteriaString(),
            getTAOfficeRegionDescription(userRegion));

    List<SummonsReportResultsBO> summonsReportList = new ArrayList<SummonsReportResultsBO>();

    for (SummonsDO summons : (List<SummonsDO>) criteria.list()) {

        String offenderFullName = NameUtil.getName(
                (StringUtil.isSet(summons.getOffender().getFirstName())
                        ? summons.getOffender().getFirstName().trim()
                        : ""),
                (StringUtil.isSet(summons.getOffender().getLastName())
                        ? summons.getOffender().getLastName().trim()
                        : ""));

        String offenceDescription = summons.getRoadCheckOffenceOutcome().getRoadCheckOffence().getOffence()
                .getDescription();

        String tAStaffFullName = NameUtil.getName(
                (StringUtil.isSet(summons.getTaStaff().getPerson().getFirstName())
                        ? summons.getTaStaff().getPerson().getFirstName().trim()
                        : ""),
                (StringUtil.isSet(summons.getTaStaff().getPerson().getLastName())
                        ? summons.getTaStaff().getPerson().getLastName().trim()
                        : ""));

        VehicleDO vehicle = summons.getRoadCheckOffenceOutcome().getRoadCheckOffence().getRoadCheck()
                .getCompliance().getVehicle();
        String vehicleDetails = "";
        if (vehicle != null) {
            vehicleDetails = (StringUtil.isSet(vehicle.getModel()) ? vehicle.getModel().trim() : "") + "; "
                    + (StringUtil.isSet(vehicle.getMakeDescription()) ? vehicle.getMakeDescription().trim()
                            : "")
                    + "; Plate #: "
                    + (StringUtil.isSet(vehicle.getPlateRegNo()) ? vehicle.getPlateRegNo().trim() : "");
        }

        String locationOfOffence = summons.getRoadCheckOffenceOutcome().getRoadCheckOffence().getRoadCheck()
                .getCompliance().getCompliancyArtery().getDescription();

        String tAOfficeRegion = summons.getRoadCheckOffenceOutcome().getRoadCheckOffence().getRoadCheck()
                .getCompliance().getRoadOperation().getOfficeLocCode();

        String jPFullName = NameUtil.getName(
                (StringUtil.isSet(summons.getJusticeOfPeace().getPerson().getFirstName())
                        ? summons.getJusticeOfPeace().getPerson().getFirstName().trim()
                        : ""),
                (StringUtil.isSet(summons.getJusticeOfPeace().getPerson().getLastName())
                        ? summons.getJusticeOfPeace().getPerson().getLastName()
                        : ""));

        String roadOperationDetails = summons.getRoadCheckOffenceOutcome().getRoadCheckOffence().getRoadCheck()
                .getCompliance().getRoadOperation().getOperationName();

        /* Get latest trial for the summons */
        Criteria criteriaCourtAppearance = this.hibernateTemplate.getSessionFactory().getCurrentSession()
                .createCriteria(CourtAppearanceDO.class, "CApp");

        criteriaCourtAppearance.createAlias("CApp.courtCase", "CCase");
        criteriaCourtAppearance.createAlias("CCase.summons", "Summ");

        criteriaCourtAppearance.add(Restrictions.eq("Summ.summonsId", summons.getSummonsId()));

        criteriaCourtAppearance.addOrder(Order.asc("CApp.CourtDTime"));
        criteriaCourtAppearance.addOrder(Order.asc("CApp.courtAppearanceId"));

        Integer courtAppListSize = criteriaCourtAppearance.list().size();
        String courtDetails = "";

        if (courtAppListSize > 0) {
            CourtAppearanceDO courtApp = (CourtAppearanceDO) criteriaCourtAppearance.list()
                    .get(courtAppListSize - 1);

            String stringTrialDate = "";
            SimpleDateFormat dt = new SimpleDateFormat("yyyy-MMM-dd HH:mm:ss");
            try {
                stringTrialDate = dt.format(courtApp.getCourtDTime());

            } catch (Exception exe) {

            }

            courtDetails = String.format("Court Appearance Date is %s at %s.", stringTrialDate,
                    courtApp.getCourt().getDescription());
        }
        /* _______________________________ */

        SummonsReportResultsBO summonsRptResult = new SummonsReportResultsBO(offenderFullName,
                offenceDescription, tAStaffFullName, vehicleDetails, locationOfOffence, tAOfficeRegion,
                jPFullName, roadOperationDetails, courtDetails, getTAOfficeRegionDescription(tAOfficeRegion),
                summons.getOffenceDtime(), summons.getServedOnDate(), summons.getPrintDtime(),
                summons.getStatus().getStatusId(), summons.getStatus().getDescription(),
                summons.getManualSerialNumber(), summons.getComment(),
                summons.getReason() != null ? summons.getReason().getDescription() : null,
                summons.getReprintDtime());

        summonsReportList.add(summonsRptResult);
    }

    report.setResults(summonsReportList);
    return report;
}

From source file:fsl.ta.toms.roms.dao.impl.ReportDAOImpl.java

@Override
public WarningNoProsecutionReportBO warningNoProsecutionReport(WarningNoProReportCriteriaBO reportCriteria,
        String userName, String userRegion, ReportDisplayInformationDAOImpl reportDisplayInformation) {
    Criteria criteria = this.hibernateTemplate.getSessionFactory().getCurrentSession()
            .createCriteria(WarningNoProsecutionDO.class, "wnp");

    /* Create aliases */
    criteria.createAlias("wnp.roadOperation", "roadOp");
    criteria.createAlias("wnp.offender", "off");
    criteria.createAlias("wnp.taStaff", "staff");
    criteria.createAlias("wnp.status", "status");
    criteria.createAlias("roadOp.category", "cat");
    criteria.createAlias("staff.person", "taPerson");
    /* _____________ */

    Criterion mainCriterion = Restrictions.sqlRestriction("1=1");

    if (reportCriteria.getOperationStartDate() != null && reportCriteria.getOperationEndDate() != null) {
        Date reportStartDate = DateUtils.searchDateFormater(reportCriteria.getOperationStartDate(),
                DateUtils.SEARCHDATETYPE.START);
        Date reportEndDate = DateUtils.searchDateFormater(reportCriteria.getOperationEndDate(),
                DateUtils.SEARCHDATETYPE.END);

        Criterion scheduledDate = Restrictions.or(
                Restrictions.between("roadOp.scheduledStartDtime", reportStartDate, reportEndDate),
                Restrictions.between("roadOp.scheduledEndDtime", reportStartDate, reportEndDate));

        Criterion actualDate = Restrictions.or(
                Restrictions.between("roadOp.actualStartDtime", reportStartDate, reportEndDate),
                Restrictions.between("roadOp.actualEndDtime", reportStartDate, reportEndDate));

        mainCriterion = Restrictions.and(mainCriterion, Restrictions.or(scheduledDate, actualDate));
    }// w w  w  . ja v  a 2 s. co  m

    if (reportCriteria.getIssuedStartDate() != null && reportCriteria.getIssuedEndDate() != null) {
        Date reportStartDate = DateUtils.searchDateFormater(reportCriteria.getIssuedStartDate(),
                DateUtils.SEARCHDATETYPE.START);
        Date reportEndDate = DateUtils.searchDateFormater(reportCriteria.getIssuedEndDate(),
                DateUtils.SEARCHDATETYPE.END);

        mainCriterion = Restrictions.and(mainCriterion,
                Restrictions.between("wnp.issueDate", reportStartDate, reportEndDate));
    }

    if (reportCriteria.getPrintStartDate() != null && reportCriteria.getPrintEndDate() != null) {
        Date reportStartDate = DateUtils.searchDateFormater(reportCriteria.getPrintStartDate(),
                DateUtils.SEARCHDATETYPE.START);
        Date reportEndDate = DateUtils.searchDateFormater(reportCriteria.getPrintEndDate(),
                DateUtils.SEARCHDATETYPE.END);

        mainCriterion = Restrictions.and(mainCriterion,
                Restrictions.between("wnp.printDtime", reportStartDate, reportEndDate));
    }

    if (StringUtil.isSet(reportCriteria.getOffenderFirstName())) {
        mainCriterion = Restrictions.and(mainCriterion,
                Restrictions
                        .like("off.firstName", reportCriteria.getOffenderFirstName().trim(), MatchMode.ANYWHERE)
                        .ignoreCase());
    }

    if (reportCriteria.getOffenderId() != null && reportCriteria.getOffenderId() > 0) {
        mainCriterion = Restrictions.and(mainCriterion,
                Restrictions.eq("off.personId", reportCriteria.getOffenderId()));
    }

    if (StringUtil.isSet(reportCriteria.getOffenderLastName())) {
        mainCriterion = Restrictions.and(mainCriterion,
                Restrictions
                        .like("off.lastName", reportCriteria.getOffenderLastName().trim(), MatchMode.ANYWHERE)
                        .ignoreCase());
    }

    if (StringUtil.isSet(reportCriteria.getOffenderTRN())) {
        mainCriterion = Restrictions.and(mainCriterion,
                Restrictions.eq("off.trnNbr", reportCriteria.getOffenderTRN().trim()));
    }

    if (StringUtil.isSet(reportCriteria.getOperationCategory())) {
        mainCriterion = Restrictions.and(mainCriterion,
                Restrictions.eq("cat.categoryId", reportCriteria.getOperationCategory().trim()));
    }

    if (reportCriteria.getRoadOperationId() != null && reportCriteria.getRoadOperationId() > 0) {
        mainCriterion = Restrictions.and(mainCriterion,
                Restrictions.eq("roadOp.roadOperationId", reportCriteria.getRoadOperationId()));
    }

    if (StringUtil.isSet(reportCriteria.getRoadOperationName())) {
        mainCriterion = Restrictions.and(mainCriterion, Restrictions
                .like("roadOp.operationName", reportCriteria.getRoadOperationName().trim(), MatchMode.ANYWHERE)
                .ignoreCase());
    }

    if (StringUtil.isSet(reportCriteria.getStatus())) {
        mainCriterion = Restrictions.and(mainCriterion,
                Restrictions.eq("status.statusId", reportCriteria.getStatus().trim()));
    }

    if (StringUtil.isSet(reportCriteria.getTAOfficeRegion())) {
        mainCriterion = Restrictions.and(mainCriterion,
                Restrictions.eq("roadOp.officeLocCode", reportCriteria.getTAOfficeRegion().trim()));
    }

    if (StringUtil.isSet(reportCriteria.getTAStaffId())) {
        mainCriterion = Restrictions.and(mainCriterion,
                Restrictions.eq("staff.staffId", reportCriteria.getTAStaffId().trim()));
    }

    if (StringUtil.isSet(reportCriteria.getTAStaffTRN())) {
        mainCriterion = Restrictions.and(mainCriterion,
                Restrictions.eq("taPerson.trnNbr", reportCriteria.getTAStaffTRN().trim()));
    }

    criteria.add(mainCriterion);

    String stringStartDate = "";
    String stringEndDate = "";
    String dateTypeUsed = "";
    Date startDate = null;
    Date endDate = null;
    try {
        if (reportCriteria.getOperationStartDate() != null && reportCriteria.getOperationEndDate() != null) {
            startDate = reportCriteria.getOperationStartDate();
            endDate = reportCriteria.getOperationEndDate();
            stringStartDate = DateUtils.getFormattedUtilDate(reportCriteria.getOperationStartDate());
            stringEndDate = DateUtils.getFormattedUtilDate(reportCriteria.getOperationEndDate());

            dateTypeUsed = "Operation";
        } else if (reportCriteria.getPrintStartDate() != null && reportCriteria.getPrintEndDate() != null) {
            startDate = reportCriteria.getPrintStartDate();
            endDate = reportCriteria.getPrintEndDate();
            stringStartDate = DateUtils.getFormattedUtilDate(reportCriteria.getPrintStartDate());
            stringEndDate = DateUtils.getFormattedUtilDate(reportCriteria.getPrintEndDate());

            dateTypeUsed = "Print";
        } else if (reportCriteria.getIssuedStartDate() != reportCriteria.getIssuedEndDate()) {
            startDate = reportCriteria.getIssuedStartDate();
            endDate = reportCriteria.getIssuedEndDate();
            stringStartDate = DateUtils.getFormattedUtilDate(reportCriteria.getIssuedStartDate());
            stringEndDate = DateUtils.getFormattedUtilDate(reportCriteria.getIssuedEndDate());

            dateTypeUsed = "Issued";
        }
    } catch (Exception exe) {

    }
    /* Get report criteria names and descriptions */
    if (reportCriteria.getOffenderId() != null && reportCriteria.getOffenderId() > 0) {
        reportCriteria.setOffenderName(this.getPersonName(reportCriteria.getOffenderId()));
    } else if (StringUtil.isSet(reportCriteria.getOffenderTRN())) {
        reportCriteria.setOffenderName(this.getPersonName(reportCriteria.getOffenderTRN()));
    }

    reportCriteria
            .setOperationCategoryDesc(this.getOperationCategoryDesc(reportCriteria.getOperationCategory()));
    reportCriteria.setRoadOperationName(reportCriteria.getRoadOperationName());

    reportCriteria.setTAOfficeRegionDesc(this.getTAOfficeRegionDescription(reportCriteria.getTAOfficeRegion()));

    if (StringUtil.isSet(reportCriteria.getTAStaffId()))
        reportCriteria.setTAStaffName(this.getTAStaffName(reportCriteria.getTAStaffId()));
    else if (StringUtil.isSet(reportCriteria.getTAStaffTRN()))
        reportCriteria.setTAStaffName(this.getPersonName(reportCriteria.getTAStaffTRN()));

    criteria.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);

    Iterator iterator = criteria.list().iterator();

    List<WarningNoProsecutionDetailsBO> warningNoProDetailsList = new ArrayList<WarningNoProsecutionDetailsBO>();

    while (iterator.hasNext()) {
        Map result = (Map) iterator.next();

        RoadOperationDO roadOp = (RoadOperationDO) result.get("roadOp");
        PersonDO offender = (PersonDO) result.get("off");
        TAStaffDO taStaff = (TAStaffDO) result.get("staff");
        StatusDO status = (StatusDO) result.get("status");
        CDCategoryDO category = (CDCategoryDO) result.get("cat");
        WarningNoProsecutionDO warningNoPro = (WarningNoProsecutionDO) result.get("wnp");

        Integer roadCheckId = warningNoPro.getRoadCheckOffenceOutcome().getRoadCheckOffence().getRoadCheck()
                .getRoadCheckId();

        WarningNoProsecutionDetailsBO warningNoProItem = new WarningNoProsecutionDetailsBO(
                this.getPersonName(taStaff.getPerson().getPersonId())/* tAStaffFullName */,
                taStaff.getStaffId()/* tAStaffID */,
                this.getTeamLeadName(roadOp.getRoadOperationId(),
                        taStaff.getPerson().getPersonId())/* tATeamLeadFullName */,
                this.getPersonName(offender.getPersonId())/* offenderFullName */,
                this.getDLNumFromDLCheck(roadCheckId)/* offendersDriverLicence */,
                this.getVehicleDetailsFromMVCheck(roadCheckId)/* offenderVehicleDetials */,
                this.getVehicleOwenersFullNames(roadCheckId)/* vehicleOwnerFullNames */,
                roadOp.getOperationName()/* roadOperationName */,
                warningNoPro.getRoadCheckOffenceOutcome().getRoadCheckOffence().getRoadCheck().getCompliance()
                        .getCompliancyArtery().getShortDescription()/* locationOfOffence */,
                warningNoPro.getOffenceDtime()/* dateOfOffence */,
                warningNoPro.getManualSerialNumber()/* manualSerialNumber */,
                status.getDescription()/* status */,
                warningNoPro.getRoadCheckOffenceOutcome().getRoadCheckOffence().getOffence()
                        .getDescription()/* offenceDescription */,
                warningNoPro.getAllegation()/* allegation */, warningNoPro.getRoadCheckOffenceOutcome()
                        .getRoadCheckOffence().getOffence().getShortDescription()/* offenceShortDescription */);

        warningNoProDetailsList.add(warningNoProItem);
    }

    return new WarningNoProsecutionReportBO(userName, userRegion, reportDisplayInformation.applicationName,
            reportDisplayInformation.warningNoticeNoProsecutionReportTitle + " (" + dateTypeUsed + ") "
                    + stringStartDate + " TO " + stringEndDate,
            startDate, endDate, reportCriteria.getSearchCriteriaString(), userRegion, warningNoProDetailsList);
}

From source file:gov.nih.nci.cabio.GridIdTestBase.java

License:BSD License

protected void testGridId(Object obj) throws Exception {

    // retrieve the first object in the database
    DetachedCriteria criteria = DetachedCriteria.forClass(obj.getClass());
    criteria.add(Restrictions.sqlRestriction("rownum=1"));
    List results = appService.query(criteria);
    assertNotNull(results);/*from w w w  .ja  va  2  s.c  o m*/
    assertEquals("result size", 1, results.size());
    obj = results.get(0);
    assertNotNull(obj);

    // get the grid id
    String bigid = String.valueOf(ReflectionUtils.get(obj, "bigid"));

    // ensure the grid id exists
    assertNotNull(bigid);
    assertNotSame("", bigid);
    assertTrue(appService.exist(bigid));

    // ensure the grid id points to the correct object
    Object dataObj = appService.getDataObject(bigid);

    // get the internal id
    String id = String.valueOf(ReflectionUtils.get(obj, "id"));
    String did = String.valueOf(ReflectionUtils.get(dataObj, "id"));
    assertEquals("id", id, did);

}

From source file:grails.orm.HibernateCriteriaBuilder.java

License:Apache License

/**
 * Applies a sql restriction to the results to allow something like:
  <pre>/* w  w  w .j  a  va 2 s  . co  m*/
   def results = Person.withCriteria {
   sqlRestriction "char_length(first_name) <= 4"
   }
  </pre>
 *
 * @param sqlRestriction the sql restriction
 * @return a Criterion instance
 */
public org.grails.datastore.mapping.query.api.Criteria sqlRestriction(String sqlRestriction) {
    if (!validateSimpleExpression()) {
        throwRuntimeException(new IllegalArgumentException(
                "Call to [sqlRestriction] with value [" + sqlRestriction + "] not allowed here."));
    }
    addToCriteria(Restrictions.sqlRestriction(sqlRestriction));
    return this;
}

From source file:ispyb.server.common.services.sessions.Session3ServiceBean.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<Session3VO> findFiltered(Integer nbMax, String beamline, Date date1, Date date2, Date dateEnd,
        boolean usedFlag, String operatorSiteNumber) {
    Session session = (Session) this.entityManager.getDelegate();
    Criteria crit = session.createCriteria(Session3VO.class);

    crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); // DISTINCT RESULTS !

    if (beamline != null)
        crit.add(Restrictions.like("beamlineName", beamline));

    if (date1 != null)
        crit.add(Restrictions.ge("startDate", date1));
    if (date2 != null)
        crit.add(Restrictions.le("startDate", date2));

    if (dateEnd != null)
        crit.add(Restrictions.ge("endDate", dateEnd));

    // usedFlag =1 or endDate > yesterday
    if (usedFlag)
        crit.add(Restrictions
                .sqlRestriction("(usedFlag = 1 OR endDate >= " + Constants.MYSQL_ORACLE_YESTERDAY + " )"));

    if (nbMax != null)
        crit.setMaxResults(nbMax);/*from  ww w . ja v  a2  s.c o  m*/

    if (operatorSiteNumber != null) {
        crit.add(Restrictions.eq("operatorSiteNumber", operatorSiteNumber));
    }

    crit.addOrder(Order.desc("startDate"));

    return crit.list();
}

From source file:ispyb.server.common.services.sessions.Session3ServiceBean.java

License:Open Source License

@SuppressWarnings("unchecked")
@WebMethod//w ww. j a v a2 s. co m
private List<Session3VO> findFiltered(Integer proposalId, Integer nbMax, String beamline, Date date1,
        Date date2, Date dateEnd, boolean usedFlag, Integer nbShifts, String operatorSiteNumber) {

    Session session = (Session) this.entityManager.getDelegate();
    Criteria crit = session.createCriteria(Session3VO.class);

    crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); // DISTINCT RESULTS !

    if (proposalId != null) {
        Criteria subCrit = crit.createCriteria("proposalVO");
        subCrit.add(Restrictions.eq("proposalId", proposalId));
    }
    if (beamline != null)
        crit.add(Restrictions.like("beamlineName", beamline));

    if (date1 != null)
        crit.add(Restrictions.ge("startDate", date1));
    if (date2 != null)
        crit.add(Restrictions.le("startDate", date2));

    if (dateEnd != null)
        crit.add(Restrictions.ge("endDate", dateEnd));

    // usedFlag =1 or endDate >= yesterday

    if (usedFlag) {
        crit.add(Restrictions
                .sqlRestriction("(usedFlag = 1 OR endDate >= " + Constants.MYSQL_ORACLE_CURRENT_DATE + " )"));
    }

    if (nbMax != null)
        crit.setMaxResults(nbMax);

    if (nbShifts != null) {
        crit.add(Restrictions.eq("nbShifts", nbShifts));
    }

    if (operatorSiteNumber != null) {
        crit.add(Restrictions.eq("operatorSiteNumber", operatorSiteNumber));
    }

    crit.addOrder(Order.desc("startDate"));

    return crit.list();
}

From source file:ispyb.server.mx.daos.collections.DataCollection3DAOBean.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<DataCollection3VO> findByCustomQuery(Integer proposalId, String sampleName, String proteinAcronym,
        String beamlineName, Date experimentDateStart, Date experimentDateEnd, Integer minNumberOfImages,
        Integer maxNumberOfImages, String imagePrefix, Byte onlyPrintableForReport, Integer maxRecords) {

    Session session = (Session) this.entityManager.getDelegate();

    Criteria crit = session.createCriteria(DataCollection3VO.class);

    crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); // DISTINCT RESULTS !
    Criteria subCritgroup = crit.createCriteria("dataCollectionGroupVO");
    Criteria subCritSample = null;//from   w w w.  j a v  a 2 s.c om

    Criteria subCritSession = subCritgroup.createCriteria("sessionVO");

    if (proposalId != null) {
        Criteria subsubCritProposal = subCritSession.createCriteria("proposalVO");
        subsubCritProposal.add(Restrictions.eq("proposalId", proposalId));
    }

    if (sampleName != null) {
        if (!StringUtils.isEmpty(sampleName)) {
            subCritSample = subCritgroup.createCriteria("blSampleVO");
            sampleName = sampleName.replace('*', '%');
            subCritSample.add(Restrictions.like("name", sampleName));
        }
    }

    if (proteinAcronym != null) {
        if (!StringUtils.isEmpty(proteinAcronym)) {
            if (subCritSample == null)
                subCritSample = subCritgroup.createCriteria("blSampleVO");
            Criteria subsubCritCrystal = subCritSample.createCriteria("crystalVO");
            Criteria subsubsubCritProtein = subsubCritCrystal.createCriteria("proteinVO");
            proteinAcronym = proteinAcronym.replace('*', '%');
            subsubsubCritProtein.add(Restrictions.like("acronym", proteinAcronym));
        }
    }

    if (beamlineName != null) {
        if (!StringUtils.isEmpty(beamlineName)) {
            beamlineName = beamlineName.replace('*', '%');
            subCritSession.add(Restrictions.like("beamlineName", beamlineName));
        }
    }

    if (experimentDateStart != null) {
        if (Constants.DATABASE_IS_ORACLE()) {
            // Number of days between 01.01.1970 and creationDateStart = msecs divided by the number of msecs per
            // day
            String days = String.valueOf(experimentDateStart.getTime() / (24 * 60 * 60 * 1000));
            crit.add(Restrictions.sqlRestriction("startTime >= to_date('19700101', 'yyyymmdd') + " + days));
            // query += " AND o.startTime >= to_date('19700101', 'yyyymmdd') + " + days;
        } else if (Constants.DATABASE_IS_MYSQL())
            // query += " AND o.startTime >= '" + experimentDateStart + "'";
            crit.add(Restrictions.ge("startTime", experimentDateStart));
        else
            LOG.error("Database type not set.");
    }

    if (experimentDateEnd != null) {
        if (Constants.DATABASE_IS_ORACLE()) {
            // Number of days between 01.01.1970 and creationDateEnd = msecs divided by the number of msecs per day
            String days = String.valueOf(experimentDateEnd.getTime() / (24 * 60 * 60 * 1000));
            // query += " AND o.startTime <= to_date('19700101', 'yyyymmdd') + " + days;
            crit.add(Restrictions.sqlRestriction("startTime <= to_date('19700101', 'yyyymmdd') + " + days));
        } else if (Constants.DATABASE_IS_MYSQL())
            // query += " AND o.startTime <= '" + experimentDateEnd + "'";
            crit.add(Restrictions.le("startTime", experimentDateEnd));
        else
            LOG.error("Database type not set.");
    }

    if (minNumberOfImages != null) {
        crit.add(Restrictions.ge("numberOfImages", minNumberOfImages));
    }

    if (maxNumberOfImages != null) {
        crit.add(Restrictions.le("numberOfImages", maxNumberOfImages));
    }

    if (imagePrefix != null) {
        if (!StringUtils.isEmpty(imagePrefix)) {
            imagePrefix = imagePrefix.replace('*', '%');
            crit.add(Restrictions.ilike("imagePrefix", imagePrefix));
        }
    }

    if (onlyPrintableForReport != null) {
        crit.add(Restrictions.eq("printableForReport", onlyPrintableForReport));
    }

    if (maxRecords != null) {
        crit.setMaxResults(maxRecords);
    }

    crit.addOrder(Order.desc("startTime"));

    return crit.list();
}

From source file:ispyb.server.mx.services.collections.DataCollection3ServiceBean.java

License:Open Source License

/**
 * //from w ww  .j a v  a2  s .  co  m
 * @param proposalId
 * @param sampleName
 * @param proteinAcronym
 * @param beamlineName
 * @param experimentDateStart
 * @param experimentDateEnd
 * @param minNumberOfImages
 * @param maxNumberOfImages
 * @param onlyPrintableForReport
 * @param maxRecords
 * @return
 * @throws Exception
 */
@SuppressWarnings("unchecked")
public List<DataCollection3VO> findByCustomQuery(final Integer proposalId, final String sampleName,
        final String proteinAcronym, final String beamlineName, final Date experimentDateStart,
        final Date experimentDateEnd, final Integer minNumberOfImages, final Integer maxNumberOfImages,
        final String imagePrefix, final Byte onlyPrintableForReport, final Integer maxRecords)
        throws Exception {

    Session session = (Session) this.entityManager.getDelegate();

    Criteria crit = session.createCriteria(DataCollection3VO.class);

    crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); // DISTINCT
    // RESULTS !
    Criteria subCritgroup = crit.createCriteria("dataCollectionGroupVO");
    Criteria subCritSample = null;

    Criteria subCritSession = subCritgroup.createCriteria("sessionVO");

    if (proposalId != null) {
        Criteria subsubCritProposal = subCritSession.createCriteria("proposalVO");
        subsubCritProposal.add(Restrictions.eq("proposalId", proposalId));
    }

    if (sampleName != null) {
        if (!StringUtils.isEmpty(sampleName)) {
            subCritSample = subCritgroup.createCriteria("blSampleVO");
            String Na;
            Na = sampleName.replace('*', '%');
            subCritSample.add(Restrictions.like("name", Na));
        }
    }

    if (proteinAcronym != null) {
        if (!StringUtils.isEmpty(proteinAcronym)) {
            if (subCritSample == null)
                subCritSample = subCritgroup.createCriteria("blSampleVO");
            Criteria subsubCritCrystal = subCritSample.createCriteria("crystalVO");
            Criteria subsubsubCritProtein = subsubCritCrystal.createCriteria("proteinVO");
            String a;
            a = proteinAcronym.replace('*', '%');
            subsubsubCritProtein.add(Restrictions.like("acronym", a));
        }
    }

    if (beamlineName != null) {
        if (!StringUtils.isEmpty(beamlineName)) {
            String n;
            n = beamlineName.replace('*', '%');
            subCritSession.add(Restrictions.like("beamlineName", n));
        }
    }

    if (experimentDateStart != null) {
        if (Constants.DATABASE_IS_ORACLE()) {
            // Number of days between 01.01.1970 and creationDateStart =
            // msecs divided by the number of msecs per
            // day
            String days = String.valueOf(experimentDateStart.getTime() / (24 * 60 * 60 * 1000));
            crit.add(Restrictions.sqlRestriction("startTime >= to_date('19700101', 'yyyymmdd') + " + days));
            // query +=
            // " AND o.startTime >= to_date('19700101', 'yyyymmdd') + " +
            // days;
        } else if (Constants.DATABASE_IS_MYSQL())
            // query += " AND o.startTime >= '" + experimentDateStart + "'";
            crit.add(Restrictions.ge("startTime", experimentDateStart));
        else
            LOG.error("Database type not set.");
    }

    if (experimentDateEnd != null) {
        if (Constants.DATABASE_IS_ORACLE()) {
            // Number of days between 01.01.1970 and creationDateEnd = msecs
            // divided by the number of msecs per day
            String days = String.valueOf(experimentDateEnd.getTime() / (24 * 60 * 60 * 1000));
            // query +=
            // " AND o.startTime <= to_date('19700101', 'yyyymmdd') + " +
            // days;
            crit.add(Restrictions.sqlRestriction("startTime <= to_date('19700101', 'yyyymmdd') + " + days));
        } else if (Constants.DATABASE_IS_MYSQL())
            // query += " AND o.startTime <= '" + experimentDateEnd + "'";
            crit.add(Restrictions.le("startTime", experimentDateEnd));
        else
            LOG.error("Database type not set.");
    }

    if (minNumberOfImages != null) {
        crit.add(Restrictions.ge("numberOfImages", minNumberOfImages));
    }

    if (maxNumberOfImages != null) {
        crit.add(Restrictions.le("numberOfImages", maxNumberOfImages));
    }

    if (imagePrefix != null) {
        if (!StringUtils.isEmpty(imagePrefix)) {
            String p;
            p = imagePrefix.replace('*', '%');
            crit.add(Restrictions.ilike("imagePrefix", p));
        }
    }

    if (onlyPrintableForReport != null) {
        crit.add(Restrictions.eq("printableForReport", onlyPrintableForReport));
    }

    if (maxRecords != null) {
        crit.setMaxResults(maxRecords);
    }

    crit.addOrder(Order.desc("startTime"));

    List<DataCollection3VO> foundEntities = crit.list();
    return foundEntities;
}