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.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.LocalDateTime; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.context.MessageSource; import org.springframework.context.i18n.LocaleContextHolder; import org.springframework.stereotype.Repository; import com.nkapps.billing.models.BankStatement; import com.nkapps.billing.models.BankStatementPayment; import com.nkapps.billing.models.BankStatementPaymentId; import com.nkapps.billing.models.ClaimListPojo; import com.nkapps.billing.models.Click; import com.nkapps.billing.models.ClickPayment; import com.nkapps.billing.models.ClickPojo; import com.nkapps.billing.models.KeyPayment; import com.nkapps.billing.models.KeyPaymentListPojo; import com.nkapps.billing.models.KeyTransaction; import com.nkapps.billing.models.MunisPayment; import com.nkapps.billing.models.Payment; import com.nkapps.billing.models.PaymentListPojo; import com.nkapps.billing.models.SimpleComboPojo; import com.nkapps.billing.models.Smst; import com.nkapps.billing.models.SmstPayment; /** * * @author nuraddin */ @Repository("paymentDao") public class PaymentDaoImpl extends AbstractDao implements PaymentDao { @Autowired private MessageSource messageSource; @Autowired private OverpaymentDao overpaymentDao; @Override public BigDecimal getOverpaymentSumByTin(String tin) throws Exception { Session session = getSession(); String q = " SELECT COALESCE(SUM(p.paymentSum),0) - COALESCE(SUM(kp.paidSum),0)" + " FROM Payment AS p LEFT OUTER JOIN p.keyPayments kp " + " WHERE p.tin = :tin " + " AND p.state IN (1,2)" + " AND p.claim = 0 "; Query query = session.createQuery(q); query.setParameter("tin", tin); BigDecimal overpaymentSum = (BigDecimal) query.uniqueResult(); session.close(); return overpaymentSum; } @Override public BigDecimal getTransactionId(String tin, Long serialNumber, BigDecimal keyCost) throws Exception { Session session = getSession(); Transaction transaction = session.beginTransaction(); KeyTransaction kt = new KeyTransaction(); kt.setTin(tin); kt.setSerialNumber(serialNumber); kt.setKeyCost(keyCost); kt.setCommitted((short) 0); LocalDateTime dateTime = LocalDateTime.now(); kt.setDateCreated(dateTime); kt.setDateUpdated(dateTime); session.save(kt); session.flush(); BigDecimal transactionId = kt.getId(); transaction.commit(); ; session.close(); return transactionId; } @Override public void saveKeyPaymentAndTransaction(BigDecimal keyTransactionId, BigDecimal keyCost) throws Exception { Session session = getSession(); Transaction transaction = session.beginTransaction(); LocalDateTime dateTime = LocalDateTime.now(); KeyTransaction ktr = (KeyTransaction) session.get(KeyTransaction.class, keyTransactionId); 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 " + " WHERE p.tin = :tin AND p.state IN (1,2) AND p.claim = 0" + " ORDER BY p.paymentDate, p.paymentNum "; Query query = session.createQuery(q); query.setParameter("tin", ktr.getTin()); query.setResultTransformer(Transformers.aliasToBean(Payment.class)); List<Payment> paymentList = query.list(); for (Payment payment : paymentList) { if (payment.getOverSum().compareTo(keyCost) <= 0) { KeyPayment kp = new KeyPayment(); kp.setSerialNumber(ktr.getSerialNumber()); kp.setPayment(payment); kp.setPaidSum(payment.getOverSum()); kp.setDateCreated(dateTime); kp.setDateUpdated(dateTime); session.save(kp); payment.setState((short) 3); payment.setDateUpdated(dateTime); session.update(payment); keyCost = keyCost.subtract(payment.getOverSum()); } else { KeyPayment kp = new KeyPayment(); kp.setSerialNumber(ktr.getSerialNumber()); kp.setPayment(payment); kp.setPaidSum(keyCost); kp.setDateCreated(dateTime); kp.setDateUpdated(dateTime); session.save(kp); payment.setState((short) 2); payment.setDateUpdated(dateTime); session.update(payment); keyCost = BigDecimal.ZERO; } if (keyCost.compareTo(BigDecimal.ZERO) <= 0) { break; } } ktr.setCommitted((short) 1); ktr.setDateUpdated(dateTime); session.update(ktr); transaction.commit(); session.close(); } @Override public Payment findPayment(String tin, String paymentNum, Date paymentDate, BigDecimal paymentSum, String tinDebtor, Short sourceCode) throws Exception { Session session = getSession(); // used for bank statement and munis, that is why fetched bank statement and munis String q = "SELECT " + " p " + " FROM Payment p LEFT JOIN FETCH p.bankStatement bs LEFT JOIN FETCH p.munis m" + " WHERE p.tin = :tin AND p.paymentNum = :paymentNum " + " AND p.paymentDate = :paymentDate " + " AND p.paymentSum = :paymentSum " + " AND p.tinDebtor = :tinDebtor " + " AND p.sourceCode = :sourceCode"; Query query = session.createQuery(q); query.setParameter("tin", tin); query.setParameter("paymentNum", paymentNum); query.setParameter("paymentDate", paymentDate); query.setParameter("paymentSum", paymentSum); query.setParameter("tinDebtor", tinDebtor); query.setParameter("sourceCode", sourceCode); Payment payment = (Payment) query.uniqueResult(); session.close(); return payment; } @Override public List<PaymentListPojo> getPaymentList(Map parameters) throws Exception { List<PaymentListPojo> listPojos; Session session = getSession(); String whereStr = ""; if (parameters.get("searchBy") != null && !"".equals(parameters.get("searchBy"))) { whereStr += " AND (p.tin LIKE :searchBy OR p.tinDebtor LIKE :searchBy OR p.paymentNum LIKE :searchBy) "; } if (parameters.get("searchWithinDate") != null && "true".equals(parameters.get("searchWithinDate"))) { whereStr += " AND p.paymentDate = :searchByDate"; } // if (!"".equals(whereStr)) { // whereStr = " WHERE " + whereStr.substring(whereStr.indexOf("AND") + 3); // } String q = " SELECT p.id as paymentId, p.tin as tin, p.paymentNum as paymentNum, " + " p.paymentDate as paymentDate, p.paymentSum as paymentSum, " + " CASE WHEN p.state = 1 THEN '" + messageSource.getMessage("payment.state_1", null, LocaleContextHolder.getLocale()) + "' " + " ELSE CASE WHEN p.state = 2 THEN '" + messageSource.getMessage("payment.state_2", null, LocaleContextHolder.getLocale()) + "' " + " ELSE '" + messageSource.getMessage("payment.state_3", null, LocaleContextHolder.getLocale()) + "' END END as state," // + " COALESCE(p.paymentSum,0) - (SELECT COALESCE(SUM(kp.paidSum),0) FROM p.keyPayments kp WHERE p.claim = 0) AS overSum," + " s.code as sourceCode,s.name as source, p.tinDebtor as tinDebtor," + " CASE WHEN p.claim = 0 THEN '" + messageSource.getMessage("payment.claim_0", null, LocaleContextHolder.getLocale()) + "'" + " ELSE '" + messageSource.getMessage("payment.claim_1", null, LocaleContextHolder.getLocale()) + "' END as claim " + " FROM Payment p, Source s" + " WHERE p.sourceCode = s.code " + whereStr + " ORDER BY p.paymentDate, p.paymentSum "; Query query = session.createQuery(q); query.setResultTransformer(Transformers.aliasToBean(PaymentListPojo.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 List<KeyPaymentListPojo> getKeyPaymentList(Map parameters) throws Exception { List<KeyPaymentListPojo> listPojos; Session session = getSession(); BigDecimal paymentId = new BigDecimal((String) parameters.get("paymentId")); String q = " SELECT kp.id as keyPaymentId, kp.serialNumber as serialNumber," + " kp.paidSum as paidSum, kp.dateUpdated as dateUpdated " + " FROM KeyPayment kp JOIN kp.payment p" + " WHERE p.id = :paymentId " + " ORDER BY kp.dateUpdated DESC "; Query query = session.createQuery(q); query.setParameter("paymentId", paymentId); query.setResultTransformer(Transformers.aliasToBean(KeyPaymentListPojo.class)); 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<SimpleComboPojo> getSources() throws Exception { List<SimpleComboPojo> listPojos; Session session = getSession(); Query query = session.createQuery( "SELECT new com.nkapps.billing.models.SimpleComboPojo(s.code, s.name) FROM Source s ORDER BY s.code"); listPojos = query.list(); session.close(); return listPojos; } @Override public void savePaymentManual(String bankStatementId, List<Payment> paymentList, Long issuerSerialNumber, String issuerIp) throws Exception { Session session = getSession(); Transaction transaction = session.beginTransaction(); BankStatement bs = (BankStatement) session.get(BankStatement.class, bankStatementId); if (bs == null) { throw new Exception(messageSource.getMessage("bank_statement.editable.bs_not_found", null, LocaleContextHolder.getLocale())); } 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(); if (overpaymentSum.compareTo(BigDecimal.ZERO) > 0) { LocalDateTime dateTime = LocalDateTime.now(); overpaymentDao.returnStateCommit(session, bs, overpaymentSum, issuerSerialNumber, issuerIp, dateTime); // if bankstatement already converted to payment, then them must revert for (Payment payment : paymentList) { BigDecimal paymentSum = payment.getPaymentSum(); if (paymentSum.compareTo(overpaymentSum) > 0) { paymentSum = overpaymentSum; overpaymentSum = BigDecimal.ZERO; } else { overpaymentSum = overpaymentSum.subtract(paymentSum); } payment.setPaymentSum(paymentSum); payment.setIssuerSerialNumber(issuerSerialNumber); payment.setIssuerIp(issuerIp); payment.setDateCreated(dateTime); payment.setDateUpdated(dateTime); session.save(payment); BankStatementPayment bsp = new BankStatementPayment(); BankStatementPaymentId bspId = new BankStatementPaymentId(); bspId.setBankStatement(bs); bspId.setPayment(payment); bsp.setId(bspId); session.save(bsp); if (overpaymentSum.compareTo(BigDecimal.ZERO) <= 0) { break; } } } transaction.commit(); session.close(); } @Override public void insertPaymentManual(String tin, String paymentNum, Date paymentDate, BigDecimal paymentSum, String tinDebtor, Long issuerSerialNumber, String issuerIp) throws Exception { Session session = getSession(); Transaction transaction = session.beginTransaction(); Payment payment = new Payment(); payment.setTin(tin); payment.setPaymentNum(paymentNum); payment.setPaymentDate(paymentDate); payment.setPaymentSum(paymentSum); payment.setSourceCode((short) 5); // manual (vruchnuyu tolko) payment.setState((short) 1); payment.setTinDebtor(tinDebtor); payment.setClaim((short) 0); payment.setIssuerSerialNumber(issuerSerialNumber); payment.setIssuerIp(issuerIp); LocalDateTime dateTime = LocalDateTime.now(); payment.setDateCreated(dateTime); payment.setDateUpdated(dateTime); session.save(payment); transaction.commit(); session.close(); } @Override public void updatePaymentManual(BigDecimal paymentId, String tin, String paymentNum, Date paymentDate, BigDecimal paymentSum, String tinDebtor, Long issuerSerialNumber, String issuerIp) throws Exception { Session session = getSession(); Transaction transaction = session.beginTransaction(); 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 " + " WHERE p.id = :paymentId "; Query query = session.createQuery(q); query.setParameter("paymentId", paymentId); query.setResultTransformer(Transformers.aliasToBean(Payment.class)); Payment payment = (Payment) query.uniqueResult(); // checking paymentsum >= key sum - (payment.payment_sum - payment.over_sum) short state; if (paymentSum.compareTo(payment.getPaymentSum().subtract(payment.getOverSum())) <= 0) { paymentSum = payment.getPaymentSum().subtract(payment.getOverSum()); state = 3; } else { if (payment.getPaymentSum().compareTo(payment.getOverSum()) == 0) { state = 1; } else { state = 2; } } payment.setTin(tin); payment.setPaymentNum(paymentNum); payment.setPaymentDate(paymentDate); payment.setPaymentSum(paymentSum); payment.setState(state); payment.setTinDebtor(tinDebtor); payment.setIssuerSerialNumber(issuerSerialNumber); payment.setIssuerIp(issuerIp); LocalDateTime dateTime = LocalDateTime.now(); payment.setDateUpdated(dateTime); session.update(payment); transaction.commit(); session.close(); } @Override public void removePaymentManual(BigDecimal paymentId) throws Exception { Session session = getSession(); Transaction transaction = session.beginTransaction(); Payment payment = (Payment) session.get(Payment.class, paymentId); if (!payment.getKeyPayments().isEmpty()) { // already payment buyed Key, that is why must be not deleted throw new Exception( messageSource.getMessage("bank_statement.editable.bs_payment_already_expensed_to_key", null, LocaleContextHolder.getLocale())); } if (payment.getSourceCode() == 1) { // bank statement (vipiska) BankStatementPayment bsp = payment.getBankStatementPayment(); BankStatement bs = bsp.getId().getBankStatement(); if (bs.getBankStatementPayments().size() == 1) { bs.setTransfered((short) 0); session.update(bs); } session.delete(bsp); } else if (payment.getSourceCode() == 2) { // munis MunisPayment mp = payment.getMunisPayment(); session.delete(mp); } else if (payment.getSourceCode() == 3) { // click ClickPayment cp = payment.getClickPayment(); session.delete(cp); } else if (payment.getSourceCode() == 4) { // smst SmstPayment sp = payment.getSmstPayment(); session.delete(sp); } session.delete(payment); transaction.commit(); session.close(); } @Override public List<ClaimListPojo> getClaimList(Map parameters) throws Exception { List<ClaimListPojo> listPojos; Session session = getSession(); String searchBy = (String) parameters.get("searchBy"); Query query = session.createQuery( "SELECT da.id, da.tin, da.name, da.sendDate, da.summa FROM DsApplication da WHERE da.tin LIKE :searchBy"); query.setParameter("searchBy", "%" + searchBy + "%"); query.setResultTransformer(Transformers.aliasToBean(ClaimListPojo.class)); listPojos = query.list(); session.close(); return listPojos; } }