Java tutorial
/* * 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.math.BigDecimal; import java.text.SimpleDateFormat; import java.util.Calendar; import java.util.Date; import java.util.List; import java.util.Map; import org.hibernate.Query; import org.hibernate.Session; import org.hibernate.Transaction; import org.hibernate.transform.Transformers; import org.joda.time.LocalDate; import org.joda.time.LocalDateTime; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Repository; import com.nkapps.billing.models.BankStatement; import com.nkapps.billing.models.InvoiceNum; import com.nkapps.billing.models.OverpaymentListPojo; import com.nkapps.billing.models.Payment; import com.nkapps.billing.models.ReturnNum; /** * * @author nuraddin */ @Repository("overpaymentDao") public class OverpaymentDaoImpl extends AbstractDao implements OverpaymentDao { @Autowired private BankStatementDao bankStatementDao; @Override public List<OverpaymentListPojo> getList(Map parameters) throws Exception { List<OverpaymentListPojo> listPojos; Session session = getSession(); String whereStr = ""; if (parameters.get("searchBy") != null && !"".equals(parameters.get("searchBy"))) { whereStr += " AND (bs.tin LIKE :searchBy OR bs.name LIKE :searchBy OR bs.mfo LIKE :searchBy" + " OR bs.chet LIKE :searchBy OR bs.paymentNum LIKE :searchBy OR bs.paymentDetails LIKE :searchBy ) "; } if (parameters.get("searchWithinDate") != null && "true".equals(parameters.get("searchWithinDate"))) { whereStr += " AND bs.paymentDate = :searchByDate"; } if (!"".equals(whereStr)) { whereStr = " WHERE " + whereStr.substring(whereStr.indexOf("AND") + 3); } String q = " SELECT bs.id AS id,bs.tin AS tin,bs.name as name," + " bs.paymentNum AS paymentNum, bs.paymentDate AS paymentDate, bs.paymentSum AS paymentSum," + " bs.paymentDetails AS paymentDetails," + " bs.transfered AS transfered," + " bs.dateUpdated AS dateUpdated," + " COALESCE(bs.paymentSum,0) - COALESCE(bs.returnSum,0) - (SELECT COALESCE(SUM(kp.paidSum),0) FROM bs.bankStatementPayments bsp JOIN bsp.id.payment p JOIN p.keyPayments kp WHERE p.claim = 0) AS overpaymentSum," + " bs.returnState as returnState, bs.returnSum as returnSum," + " bs.returnNum as returnNum, bs.returnDate as returnDate" + " FROM BankStatement bs" + whereStr + " ORDER BY bs.paymentDate, bs.paymentSum "; Query query = session.createQuery(q); query.setResultTransformer(Transformers.aliasToBean(OverpaymentListPojo.class)); if (parameters.get("searchBy") != null && !"".equals(parameters.get("searchBy"))) { query.setString("searchBy", ("%" + (String) parameters.get("searchBy") + "%").toUpperCase()); } 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 BankStatement saveReturnState(String bankStatementId, Short returnState, BigDecimal returnSum, Long issuerSerialNumber, String issuerIp) throws Exception { Session session = getSession(); Transaction transaction = session.beginTransaction(); BankStatement bs = (BankStatement) session.get(BankStatement.class, bankStatementId); String q = "SELECT COALESCE(bs.paymentSum,0) - COALESCE(bs.returnSum,0) - (SELECT COALESCE(SUM(kp.paidSum),0) FROM bs.bankStatementPayments bsp JOIN bsp.id.payment p JOIN p.keyPayments kp WHERE p.claim = 0) AS overpaymentSum" + " FROM BankStatement bs" + " WHERE bs = :bs"; Query query = session.createQuery(q); query.setParameter("bs", bs); BigDecimal overpaymentSum = (BigDecimal) query.uniqueResult(); LocalDateTime dateTime = LocalDateTime.now(); if (returnState == 0) { if (bs.getReturnSum() != null) { // get all overpayment sum overpaymentSum = overpaymentSum.add(bs.getReturnSum()); } bs.setReturnState(returnState); bs.setReturnSum(null); bs.setIssuerSerialNumber(issuerSerialNumber); bs.setIssuerIp(issuerIp); bs.setDateUpdated(dateTime); session.update(bs); returnStateRevert(session, bs, overpaymentSum, issuerSerialNumber, issuerIp, dateTime); } else { if (returnSum.compareTo(overpaymentSum) > 0) { // checking return sum must not be greater than overpayment sum returnSum = overpaymentSum; } bs.setReturnState(returnState); if (bs.getReturnSum() != null) { bs.setReturnSum(bs.getReturnSum().add(returnSum)); } else { bs.setReturnSum(returnSum); } bs.setIssuerSerialNumber(issuerSerialNumber); bs.setIssuerIp(issuerIp); bs.setDateUpdated(dateTime); session.update(bs); returnStateCommit(session, bs, returnSum, issuerSerialNumber, issuerIp, dateTime); } transaction.commit(); session.close(); return bs; } @Override public void returnStateCommit(Session session, BankStatement bs, BigDecimal returnSum, Long issuerSerialNumber, String issuerIp, LocalDateTime dateTime) throws Exception { if (!bs.getBankStatementPayments().isEmpty()) { // if bankstatement has payments String q = " SELECT p.id AS id, p.tin AS tin, p.paymentNum AS paymentNum, p.paymentDate AS paymentDate," + " p.paymentSum AS paymentSum, p.sourceCode AS sourceCode," + " p.state AS state, p.tinDebtor as tinDebtor,p.claim as claim, p.issuerSerialNumber as issuerSerialNumber," + " p.issuerIp as issuerIp,p.dateCreated AS dateCreated, p.dateUpdated as dateUpdated, " + " p.paymentSum - COALESCE((SELECT SUM(paidSum) FROM KeyPayment kp WHERE kp.payment = p),0) AS overSum " + " FROM Payment p JOIN p.bankStatementPayment bsp " + " WHERE p.state IN (1,2) AND p.claim = 0 " + " AND bsp.id.bankStatement = :bs" + " ORDER BY p.paymentDate, p.paymentNum "; Query query = session.createQuery(q); query.setParameter("bs", bs); query.setResultTransformer(Transformers.aliasToBean(Payment.class)); List<Payment> paymentList = query.list(); for (Payment payment : paymentList) { if (payment.getOverSum().compareTo(returnSum) <= 0) { payment.setPaymentSum(payment.getPaymentSum().subtract(payment.getOverSum())); payment.setState((short) 3); // payment.setIssuerSerialNumber(issuerSerialNumber); payment.setIssuerIp(issuerIp); payment.setDateUpdated(dateTime); session.update(payment); returnSum = returnSum.subtract(payment.getOverSum()); } else { payment.setPaymentSum(payment.getPaymentSum().subtract(returnSum)); payment.setIssuerSerialNumber(issuerSerialNumber); payment.setIssuerIp(issuerIp); payment.setDateUpdated(dateTime); session.update(payment); returnSum = BigDecimal.ZERO; } if (returnSum.compareTo(BigDecimal.ZERO) <= 0) { break; } } } } private void returnStateRevert(Session session, BankStatement bs, BigDecimal overpaymentSum, Long issuerSerialNumber, String issuerIp, LocalDateTime dateTime) throws Exception { if (!bs.getBankStatementPayments().isEmpty()) { // if bankstatement has payments String q = " SELECT p.id AS id, p.tin AS tin, p.paymentNum AS paymentNum, p.paymentDate AS paymentDate," + " p.paymentSum AS paymentSum, p.sourceCode AS sourceCode," + " p.state AS state, p.tinDebtor as tinDebtor,p.claim as claim, p.issuerSerialNumber as issuerSerialNumber," + " p.issuerIp as issuerIp,p.dateCreated AS dateCreated, p.dateUpdated as dateUpdated, " + " p.paymentSum - COALESCE((SELECT SUM(paidSum) FROM KeyPayment kp WHERE kp.payment = p),0) AS overSum " + " FROM Payment p JOIN p.bankStatementPayment bsp " + " WHERE p.claim = 0 " + " AND bsp.id.bankStatement = :bs" + " ORDER BY p.paymentDate, p.paymentNum "; Query query = session.createQuery(q); query.setParameter("bs", bs); query.setResultTransformer(Transformers.aliasToBean(Payment.class)); List<Payment> paymentList = query.list(); int listSize = paymentList.size(); int currentIndex = 0; BigDecimal keyCost = new BigDecimal(bankStatementDao.getKeyCost()); for (Payment payment : paymentList) { currentIndex++; overpaymentSum = overpaymentSum.subtract(payment.getOverSum()); if (currentIndex == listSize) { if (payment.getState() == 3) { if (payment.getPaymentSum().compareTo(BigDecimal.ZERO) > 0) { payment.setState((short) 2); } else { payment.setState((short) 1); } } payment.setPaymentSum(payment.getPaymentSum().add(overpaymentSum)); payment.setIssuerSerialNumber(issuerSerialNumber); payment.setIssuerIp(issuerIp); payment.setDateUpdated(dateTime); session.update(payment); overpaymentSum = BigDecimal.ZERO; } else { if (payment.getPaymentSum().compareTo(keyCost) < 0) { BigDecimal paymentSum = payment.getPaymentSum(); if (overpaymentSum.add(paymentSum).compareTo(keyCost) <= 0) { if (payment.getState() == 3) { if (payment.getPaymentSum().compareTo(BigDecimal.ZERO) > 0) { payment.setState((short) 2); } else { payment.setState((short) 1); } } payment.setPaymentSum(overpaymentSum.add(paymentSum)); payment.setIssuerSerialNumber(issuerSerialNumber); payment.setIssuerIp(issuerIp); payment.setDateUpdated(dateTime); session.update(payment); overpaymentSum = BigDecimal.ZERO; } else { if (payment.getState() == 3) { if (payment.getPaymentSum().compareTo(BigDecimal.ZERO) > 0) { payment.setState((short) 2); } else { payment.setState((short) 1); } } payment.setPaymentSum(keyCost); payment.setIssuerSerialNumber(issuerSerialNumber); payment.setIssuerIp(issuerIp); payment.setDateUpdated(dateTime); session.update(payment); overpaymentSum = overpaymentSum.add(paymentSum).subtract(keyCost); } } } if (overpaymentSum.compareTo(BigDecimal.ZERO) <= 0) { break; } } } } private String bankStatementReturnText(BankStatement bs) { String retDetail; if (bs.getTransferable() == -1) { retDetail = "00658 ? ? . .\r\n"; } else { retDetail = "00658 ? ? .\r\n"; } SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy"); String text = String.format("%s~%s~%s~%s~%s~%s~%s~%s~%s~%s~%s~%s~%s", bs.getReturnNum().toString(), dateFormat.format(bs.getReturnDate()), dateFormat.format(bs.getReturnDate()), "fake mfo", "fake account", "fake name", "fake tin", bs.getMfo(), bs.getChet(), bs.getName(), bs.getTin(), bs.getReturnSum().longValue() * 100, retDetail); return text; } @Override public String singleReturnBankStatementText(String bankStatementId, Long issuerSerialNumber, String issuerIp) throws Exception { Session session = getSession(); Transaction transaction = session.beginTransaction(); StringBuilder result = new StringBuilder(); int year = Calendar.getInstance().get(Calendar.YEAR); ReturnNum rn = (ReturnNum) session.get(ReturnNum.class, year); if (rn == null) { rn = new ReturnNum(year); rn.setNum(0l); } Long num = rn.getNum(); BankStatement bs = (BankStatement) session.get(BankStatement.class, bankStatementId); if (bs.getReturnState() == 1 || bs.getReturnState() == 2) { Date returnDate = Calendar.getInstance().getTime(); LocalDateTime dateTime = LocalDateTime.now(); bs.setReturnState((short) 2); if (bs.getReturnNum() == null) { bs.setReturnNum(++num); } bs.setReturnDate(returnDate); bs.setIssuerSerialNumber(issuerSerialNumber); bs.setIssuerIp(issuerIp); bs.setDateUpdated(dateTime); session.update(bs); result.append(bankStatementReturnText(bs)); } rn.setNum(num); session.saveOrUpdate(rn); transaction.commit(); session.close(); return result.toString(); } @Override public String allReturnBankStatementText(Date paymentDate, Long issuerSerialNumber, String issuerIp) throws Exception { Session session = getSession(); StringBuilder result = new StringBuilder(); int year = Calendar.getInstance().get(Calendar.YEAR); ReturnNum rn = (ReturnNum) session.get(ReturnNum.class, year); if (rn == null) { rn = new ReturnNum(year); rn.setNum(0l); } Long num = rn.getNum(); Query query = session.createQuery( "SELECT bs FROM BankStatement bs WHERE bs.paymentDate = :paymentDate AND bs.returnState IN (1,2)"); query.setParameter("paymentDate", paymentDate); List<BankStatement> bsList = query.list(); session.close(); session = getSession(); Transaction transaction = session.beginTransaction(); Date returnDate = Calendar.getInstance().getTime(); LocalDateTime dateTime = LocalDateTime.now(); for (BankStatement bs : bsList) { bs.setReturnState((short) 2); if (bs.getReturnNum() == null) { bs.setReturnNum(++num); } bs.setReturnDate(returnDate); bs.setIssuerSerialNumber(issuerSerialNumber); bs.setIssuerIp(issuerIp); bs.setDateUpdated(dateTime); session.update(bs); result.append(bankStatementReturnText(bs)); } rn.setNum(num); session.saveOrUpdate(rn); transaction.commit(); session.close(); return result.toString(); } }