com.nkapps.billing.dao.ReportDaoImpl.java Source code

Introduction

Here is the source code for com.nkapps.billing.dao.ReportDaoImpl.java

Source

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.nkapps.billing.dao;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;
import com.nkapps.billing.models.ReportClickListPojo;
import com.nkapps.billing.models.ReportSmstListPojo;

/**
 *
 * @author nuraddin
 */
@Repository("reportDao")
public class ReportDaoImpl extends AbstractDao implements ReportDao {

    @Override
    public List<ReportClickListPojo> getClickList(Map parameters) throws Exception {
        List<ReportClickListPojo> listPojos;
        Session session = getSession();

        String whereStr = "";
        if (parameters.get("searchWithinDate") != null && "true".equals(parameters.get("searchWithinDate"))) {
            whereStr += " AND  rc.operationDate = :searchByDate";
        }
        if (!"".equals(whereStr)) {
            whereStr = " WHERE " + whereStr.substring(whereStr.indexOf("AND") + 3);
        }

        String q = " SELECT rc.operationDate as operationDate, bs.paymentDate as bsPaymentDate,"
                + " bs.paymentNum as bsPaymentNum, bs.paymentSum as bsPaymentSum,rc.clickPaymentSum as clickPaymentSum,"
                + " COALESCE(bs.paymentSum,0) - COALESCE(rc.clickPaymentSum,0) as diffSum"
                + " FROM ReportClick rc JOIN rc.reportClickBankStatements rcbs JOIN rcbs.id.bankStatement bs"
                + whereStr + " ORDER BY rc.operationDate DESC ";
        Query query = session.createQuery(q);
        query.setResultTransformer(Transformers.aliasToBean(ReportClickListPojo.class));

        if (parameters.get("searchWithinDate") != null && "true".equals(parameters.get("searchWithinDate"))) {
            query.setParameter("searchByDate",
                    new SimpleDateFormat("dd.MM.yyyy").parse((String) parameters.get("searchByDate")));
        }

        Integer start = "".equals((String) parameters.get("start")) ? 0
                : Integer.parseInt((String) parameters.get("start"));
        Integer length = "".equals((String) parameters.get("length")) ? 0
                : Integer.parseInt((String) parameters.get("length"));
        query.setFirstResult(start).setMaxResults(length);

        listPojos = query.list();
        session.close();
        return listPojos;

    }

    @Override
    public List<ReportClickListPojo> getPrintClickList(Date periodStart, Date periodEnd) throws Exception {
        List<ReportClickListPojo> listPojos;
        Session session = getSession();

        String q = " SELECT rc.operationDate as operationDate, bs.paymentDate as bsPaymentDate,"
                + " bs.paymentNum as bsPaymentNum, bs.paymentSum as bsPaymentSum,rc.clickPaymentSum as clickPaymentSum,"
                + " COALESCE(bs.paymentSum,0) - COALESCE(rc.clickPaymentSum,0) as diffSum"
                + " FROM ReportClick rc JOIN rc.reportClickBankStatements rcbs JOIN rcbs.id.bankStatement bs"
                + " WHERE rc.operationDate BETWEEN :periodStart AND :periodEnd "
                + " ORDER BY rc.operationDate DESC ";
        Query query = session.createQuery(q);
        query.setResultTransformer(Transformers.aliasToBean(ReportClickListPojo.class));
        query.setParameter("periodStart", periodStart);
        query.setParameter("periodEnd", periodEnd);

        listPojos = query.list();
        session.close();
        return listPojos;
    }

    @Override
    public List<ReportSmstListPojo> getSmstList(Map parameters) throws Exception {
        List<ReportSmstListPojo> listPojos;
        Session session = getSession();

        String whereStr = "";
        if (parameters.get("searchWithinDate") != null && "true".equals(parameters.get("searchWithinDate"))) {
            whereStr += " AND  rs.operationDate = :searchByDate";
        }
        if (!"".equals(whereStr)) {
            whereStr = " WHERE " + whereStr.substring(whereStr.indexOf("AND") + 3);
        }

        String q = " SELECT rs.operationDate as operationDate, "
                + " (SELECT COALESCE(SUM(bs.paymentSum),0) FROM rs.reportSmstBankStatements rsbs JOIN rsbs.id.bankStatement bs) as bsPaymentSum,"
                + " rs.smstPaymentSum as smstPaymentSum,"
                + " (SELECT COALESCE(SUM(bs.paymentSum),0) FROM rs.reportSmstBankStatements rsbs JOIN rsbs.id.bankStatement bs) - COALESCE(rs.smstPaymentSum,0) as diffSum"
                + " FROM ReportSmst rs " + whereStr + " ORDER BY rs.operationDate DESC ";
        Query query = session.createQuery(q);
        query.setResultTransformer(Transformers.aliasToBean(ReportSmstListPojo.class));

        if (parameters.get("searchWithinDate") != null && "true".equals(parameters.get("searchWithinDate"))) {
            query.setParameter("searchByDate",
                    new SimpleDateFormat("dd.MM.yyyy").parse((String) parameters.get("searchByDate")));
        }

        Integer start = "".equals((String) parameters.get("start")) ? 0
                : Integer.parseInt((String) parameters.get("start"));
        Integer length = "".equals((String) parameters.get("length")) ? 0
                : Integer.parseInt((String) parameters.get("length"));
        query.setFirstResult(start).setMaxResults(length);

        listPojos = query.list();
        session.close();
        return listPojos;
    }

    @Override
    public List<ReportSmstListPojo> getPrintSmstList(Date periodStart, Date periodEnd) throws Exception {
        List<ReportSmstListPojo> listPojos;
        Session session = getSession();

        String q = " SELECT rs.operationDate as operationDate,"
                + " (SELECT COALESCE(SUM(bs.paymentSum),0) FROM rs.reportSmstBankStatements rsbs JOIN rsbs.id.bankStatement bs) as bsPaymentSum,"
                + " rs.smstPaymentSum as smstPaymentSum,"
                + " (SELECT COALESCE(SUM(bs.paymentSum),0) FROM rs.reportSmstBankStatements rsbs JOIN rsbs.id.bankStatement bs) - COALESCE(rs.smstPaymentSum,0) as diffSum"
                + " FROM ReportSmst rs " + " WHERE rs.operationDate BETWEEN :periodStart AND :periodEnd "
                + " ORDER BY rs.operationDate DESC ";
        Query query = session.createQuery(q);
        query.setResultTransformer(Transformers.aliasToBean(ReportSmstListPojo.class));
        query.setParameter("periodStart", periodStart);
        query.setParameter("periodEnd", periodEnd);

        listPojos = query.list();
        session.close();
        return listPojos;
    }

}