Example usage for org.hibernate.criterion Projections projectionList

List of usage examples for org.hibernate.criterion Projections projectionList

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.criterion.*
  5. Projections
  6. projectionList

Introduction

In this page you can find the example usage for org.hibernate.criterion Projections projectionList.

Prototype

public static ProjectionList projectionList()

Source Link

Document

Create a new projection list.

Usage

From source file:com.evolveum.midpoint.repo.sql.QueryInterpreterTest.java

License:Apache License

@Test
public void test370queryObjectypeByTypeUserAndLocality() throws Exception {
    Session session = open();/*from   w  w  w.ja  v a  2 s.c o m*/
    try {
        Criteria main = session.createCriteria(RObject.class, "o");
        ProjectionList projections = Projections.projectionList();
        addFullObjectProjectionList("o", projections, false);
        main.setProjection(projections);

        Conjunction c = Restrictions.conjunction();
        main.add(c);
        c.add(Restrictions.eq("o." + RObject.F_OBJECT_TYPE_CLASS, RObjectType.USER));
        c.add(Restrictions.and(Restrictions.eq("o.localityUser.orig", "Caribbean"),
                Restrictions.eq("o.localityUser.norm", "caribbean")));

        String expected = HibernateToSqlTranslator.toSql(main);

        EqualFilter eq = EqualFilter.createEqual(new ItemPath(UserType.F_LOCALITY), UserType.class,
                prismContext, new PolyString("Caribbean", "caribbean"));
        TypeFilter type = TypeFilter.createType(UserType.COMPLEX_TYPE, eq);

        String real = getInterpretedQuery(session, ObjectType.class, ObjectQuery.createObjectQuery(type));

        LOGGER.info("exp. query>\n{}\nreal query>\n{}", new Object[] { expected, real });
        AssertJUnit.assertEquals(expected, real);

        checkQueryTypeAlias(real, "m_user", "locality_orig", "locality_norm");
    } finally {
        close(session);
    }
}

From source file:com.evolveum.midpoint.repo.sql.QueryInterpreterTest.java

License:Apache License

@Test
public void test375queryObjectypeByTypeOrgAndLocality() throws Exception {
    Session session = open();//from   w w w .j  ava2 s .c  om
    try {
        Criteria main = session.createCriteria(RObject.class, "o");
        ProjectionList projections = Projections.projectionList();
        addFullObjectProjectionList("o", projections, false);
        main.setProjection(projections);

        Conjunction c = Restrictions.conjunction();
        main.add(c);
        c.add(Restrictions.eq("o." + RObject.F_OBJECT_TYPE_CLASS, RObjectType.ORG));
        c.add(Restrictions.and(Restrictions.eq("o.locality.orig", "Caribbean"),
                Restrictions.eq("o.locality.norm", "caribbean")));

        String expected = HibernateToSqlTranslator.toSql(main);

        EqualFilter eq = EqualFilter.createEqual(new ItemPath(OrgType.F_LOCALITY), OrgType.class, prismContext,
                new PolyString("Caribbean", "caribbean"));
        TypeFilter type = TypeFilter.createType(OrgType.COMPLEX_TYPE, eq);

        String real = getInterpretedQuery(session, ObjectType.class, ObjectQuery.createObjectQuery(type));

        LOGGER.info("exp. query>\n{}\nreal query>\n{}", new Object[] { expected, real });
        AssertJUnit.assertEquals(expected, real);

        checkQueryTypeAlias(real, "m_org", "locality_orig", "locality_norm");
    } finally {
        close(session);
    }
}

From source file:com.evolveum.midpoint.repo.sql.QueryInterpreterTest.java

License:Apache License

@Test
public void test380queryObjectypeByTypeAndExtensionAttribute() throws Exception {
    Session session = open();/*from  w w w  .  j  a v a 2s .  co m*/
    try {
        Criteria main = session.createCriteria(RObject.class, "o");
        ProjectionList projections = Projections.projectionList();
        addFullObjectProjectionList("o", projections, false);
        main.setProjection(projections);

        Conjunction c = Restrictions.conjunction();
        main.add(c);
        c.add(Restrictions.eq("o." + RObject.F_OBJECT_TYPE_CLASS, RObjectType.USER));

        Conjunction c2 = Restrictions.conjunction();
        main.createCriteria("strings", "s", JoinType.LEFT_OUTER_JOIN);
        c2.add(Restrictions.eq("s.ownerType", RObjectExtensionType.EXTENSION));
        c2.add(Restrictions.eq("s.name", new QName("http://example.com/p", "weapon")));
        c2.add(Restrictions.eq("s.value", "some weapon name"));
        c.add(c2);

        String expected = HibernateToSqlTranslator.toSql(main);

        EqualFilter eq = EqualFilter.createEqual(
                new ItemPath(ObjectType.F_EXTENSION, new QName("http://example.com/p", "weapon")),
                UserType.class, prismContext, "some weapon name");
        TypeFilter type = TypeFilter.createType(UserType.COMPLEX_TYPE, eq);

        String real = getInterpretedQuery(session, ObjectType.class, ObjectQuery.createObjectQuery(type));

        LOGGER.info("exp. query>\n{}\nreal query>\n{}", new Object[] { expected, real });
        AssertJUnit.assertEquals(expected, real);
    } finally {
        close(session);
    }
}

From source file:com.evolveum.midpoint.repo.sql.QueryInterpreterTest.java

License:Apache License

@Test
public void test390queryObjectypeByTypeAndReference() throws Exception {
    Session session = open();/*from  w w  w  .  j a v a2s  .  c  o  m*/
    try {
        Criteria main = session.createCriteria(RObject.class, "o");
        ProjectionList projections = Projections.projectionList();
        addFullObjectProjectionList("o", projections, false);
        main.setProjection(projections);

        Conjunction c = Restrictions.conjunction();
        main.add(c);
        c.add(Restrictions.eq("o." + RObject.F_OBJECT_TYPE_CLASS, RObjectType.USER));

        Criteria refs = main.createCriteria("linkRef", "l", JoinType.LEFT_OUTER_JOIN);
        c.add(Restrictions.and(Restrictions.eq("l.targetOid", "123")));

        String expected = HibernateToSqlTranslator.toSql(main);

        RefFilter ref = RefFilter.createReferenceEqual(UserType.F_LINK_REF, UserType.class, prismContext,
                "123");
        TypeFilter type = TypeFilter.createType(UserType.COMPLEX_TYPE, ref);

        String real = getInterpretedQuery(session, ObjectType.class, ObjectQuery.createObjectQuery(type));

        LOGGER.info("exp. query>\n{}\nreal query>\n{}", new Object[] { expected, real });
        AssertJUnit.assertEquals(expected, real);
    } finally {
        close(session);
    }
}

From source file:com.evolveum.midpoint.repo.sql.QueryInterpreterTest.java

License:Apache License

@Test
public void test400queryObjectypeByTypeComplex() throws Exception {
    Session session = open();/*ww  w. j ava 2 s.  co  m*/
    try {
        Criteria main = session.createCriteria(RObject.class, "o");
        ProjectionList projections = Projections.projectionList();
        addFullObjectProjectionList("o", projections, false);
        main.setProjection(projections);

        Conjunction c1 = Restrictions.conjunction();
        c1.add(Restrictions.eq("o." + RObject.F_OBJECT_TYPE_CLASS, RObjectType.USER));
        Criterion e1 = Restrictions.and(Restrictions.eq("o.localityUser.orig", "Caribbean"),
                Restrictions.eq("o.localityUser.norm", "caribbean"));
        Criterion e2 = Restrictions.and(Restrictions.eq("o.localityUser.orig", "Adriatic"),
                Restrictions.eq("o.localityUser.norm", "adriatic"));
        c1.add(Restrictions.or(e1, e2));

        Conjunction c2 = Restrictions.conjunction();
        c2.add(Restrictions.eq("o." + RObject.F_OBJECT_TYPE_CLASS, RObjectType.ORG));
        Criteria o1 = main.createCriteria("o.orgType", "o1", JoinType.LEFT_OUTER_JOIN);
        c2.add(Restrictions.eq("o1.elements", "functional"));

        Criterion c3 = Restrictions.eq("o." + RObject.F_OBJECT_TYPE_CLASS, RObjectType.REPORT);

        main.add(Restrictions.or(c1, c2, c3));
        String expected = HibernateToSqlTranslator.toSql(main);

        EqualFilter eq1 = EqualFilter.createEqual(UserType.F_LOCALITY, UserType.class, prismContext,
                new PolyString("Caribbean", "caribbean"));
        EqualFilter eq2 = EqualFilter.createEqual(UserType.F_LOCALITY, UserType.class, prismContext,
                new PolyString("Adriatic", "adriatic"));
        TypeFilter type1 = TypeFilter.createType(UserType.COMPLEX_TYPE, OrFilter.createOr(eq1, eq2));

        EqualFilter equal = EqualFilter.createEqual(OrgType.F_ORG_TYPE, OrgType.class, prismContext,
                "functional");
        TypeFilter type2 = TypeFilter.createType(OrgType.COMPLEX_TYPE, equal);

        TypeFilter type3 = TypeFilter.createType(ReportType.COMPLEX_TYPE, null);

        OrFilter or = OrFilter.createOr(type1, type2, type3);

        String real = getInterpretedQuery(session, ObjectType.class, ObjectQuery.createObjectQuery(or));

        LOGGER.info("exp. query>\n{}\nreal query>\n{}", new Object[] { expected, real });
        AssertJUnit.assertEquals(expected, real);
    } finally {
        close(session);
    }
}

From source file:com.floreantpos.model.dao.PosTransactionDAO.java

License:Open Source License

public TransactionSummary getTransactionSummary(Terminal terminal, Class transactionClass, Date from, Date to) {
    Session session = null;//from w  ww .  j a  v  a  2s .  co m
    TransactionSummary summary = new TransactionSummary();
    try {
        session = getSession();

        Criteria criteria = session.createCriteria(transactionClass);
        criteria.add(Restrictions.eq(PosTransaction.PROP_DRAWER_RESETTED, Boolean.FALSE));

        if (terminal != null) {
            criteria.add(Restrictions.eq(PosTransaction.PROP_TERMINAL, terminal));
        }

        if (from != null && to != null) {
            criteria.add(Restrictions.ge(PosTransaction.PROP_TRANSACTION_TIME, from));
            criteria.add(Restrictions.le(PosTransaction.PROP_TRANSACTION_TIME, to));
        }

        ProjectionList projectionList = Projections.projectionList();
        projectionList.add(Projections.count(PosTransaction.PROP_ID));
        projectionList.add(Projections.sum(PosTransaction.PROP_AMOUNT));
        projectionList.add(Projections.sum(PosTransaction.PROP_TIPS_AMOUNT));

        criteria.setProjection(projectionList);

        List list = criteria.list();

        if (list == null || list.size() == 0)
            return summary;

        Object[] o = (Object[]) list.get(0);
        int index = 0;

        summary.setCount(HibernateProjectionsUtil.getInt(o, index++));
        summary.setAmount(HibernateProjectionsUtil.getDouble(o, index++));
        summary.setTipsAmount(HibernateProjectionsUtil.getDouble(o, index++));

        return summary;
    } finally {
        closeSession(session);
    }
}

From source file:com.floreantpos.model.dao.RefundTransactionDAO.java

License:Open Source License

public RefundSummary getTotalRefundForTerminal(Terminal terminal) {
    Session session = null;//from  w w  w  .j  av a  2s  .  co  m
    RefundSummary refundSummary = new RefundSummary();

    try {
        session = getSession();

        Criteria criteria = session.createCriteria(getReferenceClass());

        ProjectionList projectionList = Projections.projectionList();
        projectionList.add(Projections.rowCount());
        projectionList.add(Projections.sum(RefundTransaction.PROP_AMOUNT));
        criteria.setProjection(projectionList);

        criteria.add(Restrictions.eq(RefundTransaction.PROP_TERMINAL, terminal));
        criteria.add(Restrictions.eq(RefundTransaction.PROP_DRAWER_RESETTED, Boolean.FALSE));

        List list = criteria.list();
        if (list.size() > 0) {
            Object[] objects = (Object[]) list.get(0);

            if (objects.length > 0 && objects[0] != null) {
                refundSummary.setCount(((Number) objects[0]).intValue());
            }

            if (objects.length > 1 && objects[1] != null) {
                refundSummary.setAmount(((Number) objects[1]).doubleValue());
            }
        }
        return refundSummary;
    } finally {
        closeSession(session);
    }

}

From source file:com.floreantpos.model.dao.SalesSummaryDAO.java

License:Open Source License

public List<SalesAnalysisData> findSalesAnalysis(Date start, Date end, UserType userType, Terminal terminal) {
    Session session = null;/*from www  . j a v a  2  s .c  o m*/

    try {
        ArrayList<SalesAnalysisData> list = new ArrayList<SalesAnalysisData>();

        session = getSession();

        Criteria criteria = session.createCriteria(Shift.class);
        List<Shift> shifts = criteria.list();

        criteria = session.createCriteria(MenuCategory.class);
        List<MenuCategory> categories = criteria.list();
        MenuCategory miscCategory = new MenuCategory();
        miscCategory.setName(Messages.getString("SalesSummaryDAO.0")); //$NON-NLS-1$
        categories.add(miscCategory);

        //find food sales
        criteria = session.createCriteria(TicketItem.class, "item"); //$NON-NLS-1$
        criteria.createCriteria("ticket", "t"); //$NON-NLS-1$ //$NON-NLS-2$
        criteria.createCriteria("t.owner", "u"); //$NON-NLS-1$ //$NON-NLS-2$
        ProjectionList projectionList = Projections.projectionList();
        projectionList.add(Projections.sum(TicketItem.PROP_ITEM_COUNT));
        projectionList.add(Projections.sum(TicketItem.PROP_SUBTOTAL_AMOUNT));
        projectionList.add(Projections.sum(TicketItem.PROP_DISCOUNT_AMOUNT));
        criteria.setProjection(projectionList);
        criteria.add(Restrictions.eq("item." + TicketItem.PROP_BEVERAGE, Boolean.FALSE)); //$NON-NLS-1$
        criteria.add(Restrictions.ge("t." + Ticket.PROP_ACTIVE_DATE, start)); //$NON-NLS-1$
        criteria.add(Restrictions.le("t." + Ticket.PROP_ACTIVE_DATE, end)); //$NON-NLS-1$

        if (userType != null) {
            criteria.add(Restrictions.eq("u." + User.PROP_TYPE, userType)); //$NON-NLS-1$
        }
        if (terminal != null) {
            criteria.add(Restrictions.eq("t." + Ticket.PROP_TERMINAL, terminal)); //$NON-NLS-1$
        }
        List datas = criteria.list();
        if (datas.size() > 0) {
            Object[] objects = (Object[]) datas.get(0);

            SalesAnalysisData data = new SalesAnalysisData();
            data.setShiftName(""); //$NON-NLS-1$
            data.setCategoryName(Messages.getString("SalesSummaryDAO.1")); //$NON-NLS-1$

            if (objects.length > 0 && objects[0] != null)
                data.setCount(((Number) objects[0]).intValue());

            if (objects.length > 1 && objects[1] != null)
                data.setGross(((Number) objects[1]).doubleValue());

            if (objects.length > 2 && objects[2] != null)
                data.setDiscount(((Number) objects[2]).doubleValue());

            data.calculate();
            list.add(data);
        }

        //find non food sales
        criteria = session.createCriteria(TicketItem.class, "item"); //$NON-NLS-1$
        criteria.createCriteria("ticket", "t"); //$NON-NLS-1$ //$NON-NLS-2$
        criteria.createCriteria("t.owner", "u"); //$NON-NLS-1$ //$NON-NLS-2$
        projectionList = Projections.projectionList();
        projectionList.add(Projections.sum(TicketItem.PROP_ITEM_COUNT));
        projectionList.add(Projections.sum(TicketItem.PROP_SUBTOTAL_AMOUNT));
        projectionList.add(Projections.sum(TicketItem.PROP_DISCOUNT_AMOUNT));
        criteria.setProjection(projectionList);
        criteria.add(Restrictions.eq("item." + TicketItem.PROP_BEVERAGE, Boolean.TRUE)); //$NON-NLS-1$
        criteria.add(Restrictions.ge("t." + Ticket.PROP_ACTIVE_DATE, start)); //$NON-NLS-1$
        criteria.add(Restrictions.le("t." + Ticket.PROP_ACTIVE_DATE, end)); //$NON-NLS-1$

        if (userType != null) {
            criteria.add(Restrictions.eq("u." + User.PROP_TYPE, userType)); //$NON-NLS-1$
        }
        if (terminal != null) {
            criteria.add(Restrictions.eq("t." + Ticket.PROP_TERMINAL, terminal)); //$NON-NLS-1$
        }
        datas = criteria.list();
        if (datas.size() > 0) {
            Object[] objects = (Object[]) datas.get(0);

            SalesAnalysisData data = new SalesAnalysisData();
            data.setShiftName(""); //$NON-NLS-1$
            data.setCategoryName(Messages.getString("SalesSummaryDAO.2")); //$NON-NLS-1$

            if (objects.length > 0 && objects[0] != null)
                data.setCount(((Number) objects[0]).intValue());

            if (objects.length > 1 && objects[1] != null)
                data.setGross(((Number) objects[1]).doubleValue());

            if (objects.length > 2 && objects[2] != null)
                data.setDiscount(((Number) objects[2]).doubleValue());

            data.calculate();
            list.add(data);
        }

        //find shift wise salse
        for (Shift shift : shifts) {

            for (MenuCategory category : categories) {

                criteria = session.createCriteria(TicketItem.class, "item"); //$NON-NLS-1$
                criteria.createCriteria("ticket", "t"); //$NON-NLS-1$ //$NON-NLS-2$
                criteria.createCriteria("t.owner", "u"); //$NON-NLS-1$ //$NON-NLS-2$
                projectionList = Projections.projectionList();
                projectionList.add(Projections.sum(TicketItem.PROP_ITEM_COUNT));
                projectionList.add(Projections.sum(TicketItem.PROP_SUBTOTAL_AMOUNT));
                projectionList.add(Projections.sum(TicketItem.PROP_DISCOUNT_AMOUNT));
                criteria.setProjection(projectionList);
                criteria.add(Restrictions.eq("item." + TicketItem.PROP_CATEGORY_NAME, category.getName())); //$NON-NLS-1$
                criteria.add(Restrictions.eq("t." + Ticket.PROP_SHIFT, shift)); //$NON-NLS-1$
                criteria.add(Restrictions.ge("t." + Ticket.PROP_ACTIVE_DATE, start)); //$NON-NLS-1$
                criteria.add(Restrictions.le("t." + Ticket.PROP_ACTIVE_DATE, end)); //$NON-NLS-1$

                if (userType != null) {
                    criteria.add(Restrictions.eq("u." + User.PROP_TYPE, userType)); //$NON-NLS-1$
                }
                if (terminal != null) {
                    criteria.add(Restrictions.eq("t." + Ticket.PROP_TERMINAL, terminal)); //$NON-NLS-1$
                }
                datas = criteria.list();
                if (datas.size() > 0) {
                    Object[] objects = (Object[]) datas.get(0);

                    SalesAnalysisData data = new SalesAnalysisData();
                    data.setShiftName(shift.getName());
                    data.setCategoryName(category.getName());

                    if (objects.length > 0 && objects[0] != null)
                        data.setCount(((Number) objects[0]).intValue());

                    if (objects.length > 1 && objects[1] != null)
                        data.setGross(((Number) objects[1]).doubleValue());

                    if (objects.length > 2 && objects[2] != null)
                        data.setDiscount(((Number) objects[2]).doubleValue());

                    data.calculate();
                    list.add(data);
                }
            }
        }

        //find all sales
        for (MenuCategory category : categories) {

            criteria = session.createCriteria(TicketItem.class, "item"); //$NON-NLS-1$
            criteria.createCriteria("ticket", "t"); //$NON-NLS-1$ //$NON-NLS-2$
            criteria.createCriteria("t.owner", "u"); //$NON-NLS-1$ //$NON-NLS-2$
            projectionList = Projections.projectionList();
            projectionList.add(Projections.sum(TicketItem.PROP_ITEM_COUNT));
            projectionList.add(Projections.sum(TicketItem.PROP_SUBTOTAL_AMOUNT));
            projectionList.add(Projections.sum(TicketItem.PROP_DISCOUNT_AMOUNT));
            criteria.setProjection(projectionList);
            criteria.add(Restrictions.eq("item." + TicketItem.PROP_CATEGORY_NAME, category.getName())); //$NON-NLS-1$
            criteria.add(Restrictions.ge("t." + Ticket.PROP_ACTIVE_DATE, start)); //$NON-NLS-1$
            criteria.add(Restrictions.le("t." + Ticket.PROP_ACTIVE_DATE, end)); //$NON-NLS-1$

            if (userType != null) {
                criteria.add(Restrictions.eq("u." + User.PROP_TYPE, userType)); //$NON-NLS-1$
            }
            if (terminal != null) {
                criteria.add(Restrictions.eq("t." + Ticket.PROP_TERMINAL, terminal)); //$NON-NLS-1$
            }
            datas = criteria.list();
            if (datas.size() > 0) {
                Object[] objects = (Object[]) datas.get(0);

                SalesAnalysisData data = new SalesAnalysisData();
                data.setShiftName("ALL DAY"); //$NON-NLS-1$
                data.setCategoryName(category.getName());

                if (objects.length > 0 && objects[0] != null)
                    data.setCount(((Number) objects[0]).intValue());

                if (objects.length > 1 && objects[1] != null)
                    data.setGross(((Number) objects[1]).doubleValue());

                if (objects.length > 2 && objects[2] != null)
                    data.setDiscount(((Number) objects[2]).doubleValue());

                data.calculate();
                list.add(data);
            }
        }
        return list;
    } finally {
        if (session != null) {
            closeSession(session);
        }
    }
}

From source file:com.floreantpos.model.dao.SalesSummaryDAO.java

License:Open Source License

public SalesStatistics findKeyStatistics(Date start, Date end, UserType userType, Terminal terminal) {
    Session session = null;/*from  w  w  w  .ja  v  a 2  s  . co m*/

    try {
        SalesStatistics salesSummary = new SalesStatistics();

        session = getSession();

        //retrieve restaurant information and set it to sales summary
        Restaurant restaurant = (Restaurant) get(Restaurant.class, new Integer(1), session);
        if (restaurant != null) {
            salesSummary
                    .setCapacity(restaurant.getCapacity() != null ? restaurant.getCapacity().intValue() : 0);
            salesSummary.setTables(restaurant.getTables() != null ? restaurant.getTables().intValue() : 0);
        }

        {
            //find gross sale, discount and tax. this excludes void tickets
            //net sale = gross_sale - discount
            Criteria criteria = session.createCriteria(Ticket.class, "ticket"); //$NON-NLS-1$
            criteria.createCriteria(Ticket.PROP_OWNER, "u"); //$NON-NLS-1$

            ProjectionList projectionList = Projections.projectionList();
            projectionList.add(Projections.rowCount());
            projectionList.add(Projections.sum(Ticket.PROP_SUBTOTAL_AMOUNT));
            projectionList.add(Projections.sum(Ticket.PROP_DISCOUNT_AMOUNT));
            projectionList.add(Projections.sum(Ticket.PROP_TAX_AMOUNT));
            criteria.setProjection(projectionList);
            criteria.add(Restrictions.ge(Ticket.PROP_CREATE_DATE, start));
            criteria.add(Restrictions.le(Ticket.PROP_CREATE_DATE, end));

            //do not take into account void tickets
            criteria.add(Restrictions.eq(Ticket.PROP_VOIDED, Boolean.FALSE));
            criteria.add(Restrictions.eq(Ticket.PROP_REFUNDED, Boolean.FALSE));

            if (userType != null) {
                criteria.add(Restrictions.eq("u." + User.PROP_TYPE, userType)); //$NON-NLS-1$
            }

            if (terminal != null) {
                criteria.add(Restrictions.eq(Ticket.PROP_TERMINAL, terminal));
            }
            List list = criteria.list();
            if (list.size() > 0) {
                Object[] objects = (Object[]) list.get(0);

                if (objects.length > 1 && objects[1] != null) {
                    salesSummary.setGrossSale(((Number) objects[1]).doubleValue());
                }
                if (objects.length > 2 && objects[2] != null) {
                    salesSummary.setDiscount(((Number) objects[2]).intValue());
                }
                if (objects.length > 3 && objects[3] != null) {
                    salesSummary.setTax(((Number) objects[3]).intValue());
                }
            }
        }

        {
            //determine number of guests within the specified time. this includes void tickets, 
            Criteria criteria = session.createCriteria(Ticket.class, "ticket"); //$NON-NLS-1$
            criteria.createCriteria(Ticket.PROP_OWNER, "u"); //$NON-NLS-1$

            ProjectionList projectionList = Projections.projectionList();
            projectionList.add(Projections.rowCount());
            projectionList.add(Projections.sum(Ticket.PROP_NUMBER_OF_GUESTS));
            criteria.setProjection(projectionList);
            criteria.add(Restrictions.ge(Ticket.PROP_CREATE_DATE, start));
            criteria.add(Restrictions.le(Ticket.PROP_CREATE_DATE, end));

            if (userType != null) {
                criteria.add(Restrictions.eq("u." + User.PROP_TYPE, userType)); //$NON-NLS-1$
            }

            if (terminal != null) {
                criteria.add(Restrictions.eq(Ticket.PROP_TERMINAL, terminal));
            }
            List list = criteria.list();
            if (list.size() > 0) {
                Object[] objects = (Object[]) list.get(0);
                salesSummary.setCheckCount(((Number) objects[0]).intValue());

                if (objects.length > 1 && objects[1] != null) {
                    salesSummary.setGuestCount(((Number) objects[1]).intValue());
                }
            }
        }

        {
            //find number of open tickets and its total amount
            Criteria criteria = session.createCriteria(Ticket.class, "ticket"); //$NON-NLS-1$
            criteria.createCriteria(Ticket.PROP_OWNER, "u"); //$NON-NLS-1$

            ProjectionList projectionList = Projections.projectionList();
            projectionList.add(Projections.rowCount());
            projectionList.add(Projections.sum(Ticket.PROP_TOTAL_AMOUNT));
            criteria.setProjection(projectionList);
            criteria.add(Restrictions.ge(Ticket.PROP_CREATE_DATE, start));
            criteria.add(Restrictions.le(Ticket.PROP_CREATE_DATE, end));
            criteria.add(Restrictions.eq(Ticket.PROP_CLOSED, Boolean.FALSE));

            if (userType != null) {
                criteria.add(Restrictions.eq("u." + User.PROP_TYPE, userType)); //$NON-NLS-1$
            }
            if (terminal != null) {
                criteria.add(Restrictions.eq(Ticket.PROP_TERMINAL, terminal));
            }
            List list = criteria.list();
            if (list.size() > 0) {
                Object[] objects = (Object[]) list.get(0);
                salesSummary.setOpenChecks(((Number) objects[0]).intValue());

                if (objects.length > 1 && objects[1] != null) {
                    salesSummary.setOpenAmount(((Number) objects[1]).doubleValue());
                }
            }
        }

        {
            //find number of void tickets and its total amount
            Criteria criteria = session.createCriteria(Ticket.class, "ticket"); //$NON-NLS-1$
            criteria.createCriteria(Ticket.PROP_OWNER, "u"); //$NON-NLS-1$
            ProjectionList projectionList = Projections.projectionList();
            projectionList.add(Projections.rowCount());
            projectionList.add(Projections.sum(Ticket.PROP_TOTAL_AMOUNT));
            criteria.setProjection(projectionList);
            criteria.add(Restrictions.ge(Ticket.PROP_CREATE_DATE, start));
            criteria.add(Restrictions.le(Ticket.PROP_CREATE_DATE, end));
            criteria.add(Restrictions.eq(Ticket.PROP_CLOSED, Boolean.TRUE));
            criteria.add(Restrictions.eq(Ticket.PROP_VOIDED, Boolean.TRUE));

            if (userType != null) {
                criteria.add(Restrictions.eq("u." + User.PROP_TYPE, userType)); //$NON-NLS-1$
            }
            if (terminal != null) {
                criteria.add(Restrictions.eq(Ticket.PROP_TERMINAL, terminal));
            }
            List list = criteria.list();
            if (list.size() > 0) {
                Object[] objects = (Object[]) list.get(0);
                salesSummary.setVoidChecks(((Number) objects[0]).intValue());

                if (objects.length > 1 && objects[1] != null) {
                    salesSummary.setVoidAmount(((Number) objects[1]).doubleValue());
                }
            }
        }

        {
            //find non taxable sales
            Criteria criteria = session.createCriteria(Ticket.class, "ticket"); //$NON-NLS-1$
            criteria.createCriteria(Ticket.PROP_OWNER, "u"); //$NON-NLS-1$
            ProjectionList projectionList = Projections.projectionList();
            projectionList.add(Projections.rowCount());
            projectionList.add(Projections.sum(Ticket.PROP_TOTAL_AMOUNT));
            criteria.setProjection(projectionList);
            criteria.add(Restrictions.ge(Ticket.PROP_CREATE_DATE, start));
            criteria.add(Restrictions.le(Ticket.PROP_CREATE_DATE, end));
            criteria.add(Restrictions.eq(Ticket.PROP_VOIDED, Boolean.FALSE));
            criteria.add(Restrictions.eq(Ticket.PROP_REFUNDED, Boolean.FALSE));
            criteria.add(Restrictions.eq(Ticket.PROP_TAX_EXEMPT, Boolean.TRUE));

            if (userType != null) {
                criteria.add(Restrictions.eq("u." + User.PROP_TYPE, userType)); //$NON-NLS-1$
            }
            if (terminal != null) {
                criteria.add(Restrictions.eq(Ticket.PROP_TERMINAL, terminal));
            }
            List list = criteria.list();
            if (list.size() > 0) {
                Object[] objects = (Object[]) list.get(0);
                salesSummary.setNtaxChecks(((Number) objects[0]).intValue());

                if (objects.length > 1 && objects[1] != null) {
                    salesSummary.setNtaxAmount(((Number) objects[1]).doubleValue());
                }
            }
        }
        {
            //find reopen statistics
            Criteria criteria = session.createCriteria(Ticket.class, "ticket"); //$NON-NLS-1$
            criteria.createCriteria(Ticket.PROP_OWNER, "u"); //$NON-NLS-1$
            ProjectionList projectionList = Projections.projectionList();
            projectionList.add(Projections.rowCount());
            projectionList.add(Projections.sum(Ticket.PROP_TOTAL_AMOUNT));
            criteria.setProjection(projectionList);
            criteria.add(Restrictions.ge(Ticket.PROP_CREATE_DATE, start));
            criteria.add(Restrictions.le(Ticket.PROP_CREATE_DATE, end));
            criteria.add(Restrictions.eq(Ticket.PROP_RE_OPENED, Boolean.TRUE));

            if (userType != null) {
                criteria.add(Restrictions.eq("u." + User.PROP_TYPE, userType)); //$NON-NLS-1$
            }
            if (terminal != null) {
                criteria.add(Restrictions.eq(Ticket.PROP_TERMINAL, terminal));
            }
            List list = criteria.list();
            if (list.size() > 0) {
                Object[] objects = (Object[]) list.get(0);
                salesSummary.setRopnChecks(((Number) objects[0]).intValue());

                if (objects.length > 1 && objects[1] != null) {
                    salesSummary.setRopnAmount(((Number) objects[1]).doubleValue());
                }
            }
        }

        {
            //calculate totalLaborHours in the specified period
            Criteria criteria = session.createCriteria(AttendenceHistory.class, "history"); //$NON-NLS-1$
            criteria.createCriteria(AttendenceHistory.PROP_USER, "u"); //$NON-NLS-1$
            criteria.add(Restrictions.ge(AttendenceHistory.PROP_CLOCK_IN_TIME, start));
            criteria.add(Restrictions.le(AttendenceHistory.PROP_CLOCK_IN_TIME, end));

            if (userType != null) {
                criteria.add(Restrictions.eq("u." + User.PROP_TYPE, userType)); //$NON-NLS-1$
            }
            if (terminal != null) {
                criteria.add(Restrictions.eq(AttendenceHistory.PROP_TERMINAL, terminal));
            }
            List list = criteria.list();

            double laborHours = 0;
            double laborCost = 0;
            for (Object object : list) {
                AttendenceHistory attendenceHistory = (AttendenceHistory) object;
                double laborHourInMillisecond = 0;
                if (!attendenceHistory.isClockedOut() || attendenceHistory.getClockOutTime() == null) {
                    Shift attendenceShift = attendenceHistory.getShift();
                    laborHourInMillisecond = Math
                            .abs(end.getTime() - attendenceHistory.getClockInTime().getTime());
                    if (laborHourInMillisecond > attendenceShift.getShiftLength()) {
                        laborHourInMillisecond = attendenceShift.getShiftLength();
                    }
                } else {
                    laborHourInMillisecond = Math.abs(attendenceHistory.getClockInTime().getTime()
                            - attendenceHistory.getClockInTime().getTime());
                }
                double hour = (laborHourInMillisecond * (2.77777778 * Math.pow(10, -7)));
                laborHours += hour;
                laborCost += hour * (attendenceHistory.getUser().getCostPerHour() == null ? 0
                        : attendenceHistory.getUser().getCostPerHour());
            }
            salesSummary.setLaborHour(laborHours);
            salesSummary.setLaborCost(laborCost);
        }

        {
            //find summary by shift
            Criteria criteria = session.createCriteria(Shift.class);
            List shifts = criteria.list();
            for (Object object : shifts) {
                Shift shift = (Shift) object;

                List<OrderType> values = Application.getInstance().getOrderTypes(); //change enum
                for (OrderType ticketType : values) {
                    findRecordByProfitCenter(start, end, userType, terminal, session, salesSummary, shift,
                            ticketType);
                }

            }
        }

        salesSummary.calculateOthers();
        return salesSummary;
    } finally {
        if (session != null) {
            closeSession(session);
        }
    }
}

From source file:com.floreantpos.model.dao.SalesSummaryDAO.java

License:Open Source License

private void findRecordByProfitCenter(Date start, Date end, UserType userType, Terminal terminal,
        Session session, SalesStatistics salesSummary, Shift shift, OrderType ticketType) {
    Criteria criteria;/* w w  w.  jav a  2  s  .  c  o m*/
    criteria = session.createCriteria(Ticket.class, "ticket"); //$NON-NLS-1$
    criteria.createCriteria(Ticket.PROP_OWNER, "u"); //$NON-NLS-1$
    ProjectionList projectionList = Projections.projectionList();
    projectionList.add(Projections.rowCount());
    projectionList.add(Projections.sum(Ticket.PROP_NUMBER_OF_GUESTS));
    projectionList.add(Projections.sum(Ticket.PROP_SUBTOTAL_AMOUNT));
    criteria.setProjection(projectionList);
    criteria.add(Restrictions.ge(Ticket.PROP_CREATE_DATE, start));
    criteria.add(Restrictions.le(Ticket.PROP_CREATE_DATE, end));
    criteria.add(Restrictions.eq(Ticket.PROP_SHIFT, shift));
    criteria.add(Restrictions.eq(Ticket.PROP_TICKET_TYPE, ticketType.name()));

    if (userType != null) {
        criteria.add(Restrictions.eq("u." + User.PROP_TYPE, userType)); //$NON-NLS-1$
    }
    if (terminal != null) {
        criteria.add(Restrictions.eq(Ticket.PROP_TERMINAL, terminal));
    }
    List list = criteria.list();
    if (list.size() > 0) {
        ShiftwiseSalesTableData data = new ShiftwiseSalesTableData();
        data.setProfitCenter(ticketType.toString());
        Object[] objects = (Object[]) list.get(0);

        data.setShiftName(shift.getName());
        data.setCheckCount(((Number) objects[0]).intValue());

        if (objects.length > 1 && objects[1] != null) {
            data.setGuestCount(((Number) objects[1]).intValue());
        }
        if (objects.length > 2 && objects[2] != null) {
            data.setTotalSales(((Number) objects[2]).doubleValue());
        }
        data.setPercentage(data.getTotalSales() * 100 / salesSummary.getGrossSale());
        data.calculateOthers();
        salesSummary.addSalesTableData(data);
    }
}