Java tutorial
/* jBilling - The Enterprise Open Source Billing System Copyright (C) 2003-2011 Enterprise jBilling Software Ltd. and Emiliano Conde This file is part of jbilling. jbilling is free software: you can redistribute it and/or modify it under the terms of the GNU Affero General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. jbilling is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more details. You should have received a copy of the GNU Affero General Public License along with jbilling. If not, see <http://www.gnu.org/licenses/>. */ package com.sapienter.jbilling.server.order.db; import com.sapienter.jbilling.server.user.UserBL; import com.sapienter.jbilling.server.user.db.UserDTO; import com.sapienter.jbilling.server.util.Constants; import com.sapienter.jbilling.server.util.db.AbstractDAS; import org.apache.log4j.Logger; import org.hibernate.Query; import org.joda.time.DateMidnight; import java.util.Collections; import java.util.List; public class OrderLineDAS extends AbstractDAS<OrderLineDTO> { private static final Logger LOG = Logger.getLogger(OrderLineDAS.class); public Long findLinesWithDecimals(Integer itemId) { final String hql = "select count(*)" + " from OrderLineDTO ol " + " where ol.deleted = 0 " + " and ol.item.id= :item and (ol.quantity - cast(ol.quantity as integer)) <> 0"; Query query = getSession().createQuery(hql); query.setParameter("item", itemId); return (Long) query.uniqueResult(); } @SuppressWarnings("unchecked") public List<OrderLineDTO> findByUserItem(Integer userId, Integer itemId) { final String hql = "select ol" + " from OrderLineDTO ol " + " where ol.deleted = 0 " + " and ol.item.id = :item " + " and ol.purchaseOrder.baseUserByUserId.id = :user"; Query query = getSession().createQuery(hql); query.setParameter("item", itemId); query.setParameter("user", userId); return query.list(); } @SuppressWarnings("unchecked") public List<OrderLineDTO> findRecurringByUser(Integer userId) { final String hql = "select line " + " from OrderLineDTO line " + "where line.deleted = 0 " + " and line.purchaseOrder.baseUserByUserId.id = :userId " + " and line.purchaseOrder.orderPeriod.id != :period " + " and line.purchaseOrder.orderStatus.id = :status " + " and line.purchaseOrder.deleted = 0 "; Query query = getSession().createQuery(hql); query.setParameter("userId", userId); query.setParameter("period", Constants.ORDER_PERIOD_ONCE); query.setParameter("status", Constants.ORDER_STATUS_ACTIVE); return query.list(); } /** * Returns the first recurring order line found for the given user and item ID. * * @param userId user id * @param itemId item id * @return first recurring order line found, null if none found */ @SuppressWarnings("unchecked") public OrderLineDTO findRecurringByUserItem(Integer userId, Integer itemId) { final String hql = "select line " + " from OrderLineDTO line " + "where line.deleted = 0 " + " and line.item.id = :itemId " + " and line.purchaseOrder.baseUserByUserId.id = :userId " + " and line.purchaseOrder.orderPeriod.id != :period " + " and line.purchaseOrder.orderStatus.id = :status " + " and line.purchaseOrder.deleted = 0 "; Query query = getSession().createQuery(hql); query.setParameter("itemId", itemId); query.setParameter("userId", userId); query.setParameter("period", Constants.ORDER_PERIOD_ONCE); query.setParameter("status", Constants.ORDER_STATUS_ACTIVE); List<OrderLineDTO> results = query.list(); return results.isEmpty() ? null : results.get(0); } @SuppressWarnings("unchecked") public List<OrderLineDTO> findOnetimeByUserItem(Integer userId, Integer itemId) { final String hql = "select line " + " from OrderLineDTO line " + "where line.deleted = 0 " + " and line.item.id = :itemId " + " and line.purchaseOrder.baseUserByUserId.id = :userId " + " and line.purchaseOrder.orderPeriod.id = :period " + " and line.purchaseOrder.orderStatus.id = :status " + " and line.purchaseOrder.deleted = 0 "; Query query = getSession().createQuery(hql); query.setParameter("itemId", itemId); query.setParameter("userId", userId); query.setParameter("period", Constants.ORDER_PERIOD_ONCE); query.setParameter("status", Constants.ORDER_STATUS_ACTIVE); return query.list(); } /** * Returns a list of all active and finished one time orders going back n number * of months, containing the given item id for the given user. * * @param userId user id of orders * @param itemId item id of order lines * @param months previous number of months to include (1 = this month plus the previous) * @return list of found one-time orders, empty list if none found */ @SuppressWarnings("unchecked") public List<OrderLineDTO> findOnetimeByUserItem(Integer userId, Integer itemId, Integer months) { final String hql = "select line " + " from OrderLineDTO line " + "where line.deleted = 0 " + " and line.item.id = :itemId " + " and line.purchaseOrder.baseUserByUserId.id = :userId " + " and line.purchaseOrder.orderPeriod.id = :period " + " and (line.purchaseOrder.orderStatus.id = :active_status" + " or line.purchaseOrder.orderStatus.id = :finished_status)" + " and line.purchaseOrder.deleted = 0 " + " and line.purchaseOrder.createDate > :startdate"; Query query = getSession().createQuery(hql); query.setParameter("itemId", itemId); query.setParameter("userId", userId); query.setParameter("period", Constants.ORDER_PERIOD_ONCE); query.setParameter("active_status", Constants.ORDER_STATUS_ACTIVE); query.setParameter("finished_status", Constants.ORDER_STATUS_FINISHED); DateMidnight startdate = new DateMidnight().minusMonths(months); query.setParameter("startdate", startdate.toDate()); return query.list(); } /** * Returns a list of all active and finished one-time orders going back n number * of months, for all direct immediate of the given parent user id. This is useful for * determining usage across all child users. * * @param parentUserId parent user id * @param itemId item id of order lines * @param months previous number of months to include (1 = 1 month period starting from today) * @return list of found one-time orders, empty list if none found */ @SuppressWarnings("unchecked") public List<OrderLineDTO> findOnetimeByParentUserItem(Integer parentUserId, Integer itemId, Integer months) { UserDTO parent = new UserBL(parentUserId).getEntity(); if (parent == null || parent.getCustomer() == null) { LOG.warn("Parent user " + parentUserId + " does not exist or is not a customer!"); return Collections.emptyList(); } final String hql = "select line " + " from OrderLineDTO line " + " where line.deleted = 0 " + " and line.item.id = :itemId " + " and line.purchaseOrder.baseUserByUserId.customer.parent.id = :parentId" + " and line.purchaseOrder.orderPeriod.id = :period " + " and (line.purchaseOrder.orderStatus.id = :active_status" + " or line.purchaseOrder.orderStatus.id = :finished_status)" + " and line.purchaseOrder.deleted = 0 " + " and line.purchaseOrder.createDate > :startdate "; Query query = getSession().createQuery(hql); query.setParameter("itemId", itemId); query.setParameter("parentId", parent.getCustomer().getId()); query.setParameter("period", Constants.ORDER_PERIOD_ONCE); query.setParameter("active_status", Constants.ORDER_STATUS_ACTIVE); query.setParameter("finished_status", Constants.ORDER_STATUS_FINISHED); DateMidnight startdate = new DateMidnight().minusMonths(months); query.setParameter("startdate", startdate.toDate()); return query.list(); } }