BillingProcessDAS.java

/*
 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.process.db;

import java.util.Date;
import java.util.Iterator;

import com.sapienter.jbilling.server.util.Constants;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.ScrollableResults;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;

import com.sapienter.jbilling.server.user.UserDTOEx;
import com.sapienter.jbilling.server.user.db.CompanyDTO;
import com.sapienter.jbilling.server.user.db.UserDTO;
import com.sapienter.jbilling.server.util.db.AbstractDAS;

public class BillingProcessDAS extends AbstractDAS<BillingProcessDTO> {

    public BillingProcessDTO create(CompanyDTO entity, Date billingProcess,
            Integer periodId, Integer periodValue, Integer retries) {

        PeriodUnitDTO period = new PeriodUnitDAS().find(periodId);
        BillingProcessDTO dto = new BillingProcessDTO();
        dto.setEntity(entity);
        dto.setBillingDate(billingProcess);
        dto.setPeriodUnit(period);
        dto.setPeriodValue(periodValue);
        dto.setIsReview(0);
        dto.setRetriesToDo(retries);

        return save(dto);
    }

    public BillingProcessDTO findReview(Integer entityId) {
        Criteria criteria = getSession().createCriteria(BillingProcessDTO.class);
        criteria.createAlias("entity", "ent").add(Restrictions.eq("ent.id", entityId));
        criteria.add(Restrictions.eq("isReview", 1));

        return (BillingProcessDTO) criteria.uniqueResult();
    }

    public BillingProcessDTO isPresent(Integer entityId, Integer isReview, Date billingDate) {
        Criteria criteria = getSession().createCriteria(BillingProcessDTO.class);
        criteria.createAlias("entity", "ent").add(Restrictions.eq("ent.id", entityId));
        criteria.add(Restrictions.eq("isReview", isReview));
        criteria.add(Restrictions.eq("billingDate", billingDate));

        return (BillingProcessDTO) criteria.uniqueResult();
    }

    public ScrollableResults findUsersToProcess(int entityId) {
        Criteria criteria = getSession().createCriteria(UserDTO.class)
                .add(Restrictions.eq("deleted", 0))
                .createAlias("company", "c")
                    .add(Restrictions.eq("c.id", entityId))
                .createAlias("userStatus", "us")
                    .add(Restrictions.lt("us.id", UserDTOEx.STATUS_SUSPENDED))
                .setProjection(Projections.id())
                .setComment("BillingProcessDAS.findUsersToProcess " + entityId);
        return criteria.scroll();
    }

    public void reset() {
        getSession().flush();
        getSession().clear();
    }
    
    public Iterator getCountAndSum(Integer processId) {
        final String hql =
                "select count(id), sum(total), currency.id " +
                "  from InvoiceDTO " +
                " where billingProcess.id = :processId " +
                " group by currency.id";

        Query query = getSession().createQuery(hql);
        query.setParameter("processId", processId);
        return query.iterate();
    }

    /**
     * Search succesfull payments in Payment_Invoice map (with quantity > 0)
     * and returns result, groupped by currency
     *
     * @param processId
     * @return Iterator with currency, method and sum of amount fields of query
     */
    public Iterator getSuccessfulProcessCurrencyMethodAndSum(Integer processId) {
        final String hql =
                "select invoice.currency.id, method.id, sum(invoice.total) " +
                "  from InvoiceDTO invoice inner join invoice.paymentMap paymentMap " +
                " join paymentMap.payment payment join payment.paymentMethod method " +
                " where invoice.billingProcess.id = :processId and paymentMap.amount > 0" +
                " group by invoice.currency.id, method.id " +
                " having sum(invoice.total) > 0";

        Query query = getSession().createQuery(hql);
        query.setParameter("processId", processId);
        return query.iterate();
    }

    /**
     * Selection records from Invoice table without payment records or
     * with payments of 0 amount. Result groupped by currency
     * @param processId
     * @return Iterator with currency and amount value
     */
    public Iterator getFailedProcessCurrencyAndSum(Integer processId) {
        final String hql =
                "select invoice.currency.id, sum(invoice.total) " +
                "  from InvoiceDTO invoice left join invoice.paymentMap paymentMap" +
                " where invoice.billingProcess.id = :processId and (paymentMap is NULL or paymentMap.amount = 0) " +
                " group by invoice.currency.id";


        Query query = getSession().createQuery(hql);
        query.setParameter("processId", processId);
        return query.iterate();
    }

    private static final String BILLABLE_USERS_TO_PROCESS =
            "SELECT a.id "
            + " FROM UserDTO a, OrderDTO o"
            + " WHERE a.id = o.baseUserByUserId.id"
            + " AND ( "
            + "     o.nextBillableDay is null "
            + "     or cast(o.nextBillableDay as date) <= :dueDate "
            + " ) "
            + " AND o.deleted = 0 "
            + " AND a.company.id = :entity ";

    public ScrollableResults findBillableUsersToProcess(int entityId, Date processDate) {
        Query query = getSession().createQuery(BILLABLE_USERS_TO_PROCESS);
        query.setParameter("dueDate", processDate);
        query.setParameter("entity", entityId);

        return query.scroll();
    }

    private static final String BILLABLE_USERS_WITH_ORDER_HQL =
            "select user.id "
            + " from OrderDTO purchaseOrder "
            + "     join purchaseOrder.baseUserByUserId as user "
            + " where "
            + "     user.deleted = 0 "
            + "     and user.company.id = :entity_id "
            + "     and purchaseOrder.orderStatus.id = :active_status_id "
            + "     and ( "
            + "         purchaseOrder.nextBillableDay is null"
            + "         or cast(purchaseOrder.nextBillableDay as date) <= :process_date "
            + "     )";

    /**
     * Returns all billable users with an order to process. This can be either any open one-time
     * order or an active recurring order with a valid next billable date.
     *
     * @param entityId entity id to find orders for
     * @param processDate billing process run date
     * @return billable users
     */
    public ScrollableResults findBillableUsersWithOrdersToProcess(int entityId, Date processDate) {
        Query query = getSession().createQuery(BILLABLE_USERS_WITH_ORDER_HQL);
        query.setParameter("entity_id", entityId);
        query.setParameter("active_status_id", Constants.ORDER_STATUS_ACTIVE);
        query.setParameter("process_date", processDate);
        
        return query.scroll();
    }
}