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.Arrays; import java.util.Calendar; import java.util.Date; import java.util.HashMap; import java.util.LinkedHashSet; import java.util.LinkedList; import java.util.List; import java.util.Set; import java.util.regex.Matcher; import java.util.regex.Pattern; import javax.validation.ConstraintViolation; import javax.validation.Validation; import javax.validation.Validator; import javax.validation.ValidatorFactory; import org.apache.commons.lang3.StringUtils; import org.hibernate.Query; import org.hibernate.Session; import org.hibernate.Transaction; import org.hibernate.transform.Transformers; import org.joda.time.LocalDateTime; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.context.MessageSource; import org.springframework.context.i18n.LocaleContextHolder; import org.springframework.core.env.Environment; import org.springframework.stereotype.Repository; import com.nkapps.billing.models.BankStatement; import com.nkapps.billing.models.BankStatementListPojo; import com.nkapps.billing.models.BankStatementMunis; import com.nkapps.billing.models.BankStatementMunisId; import com.nkapps.billing.models.BankStatementPayment; import com.nkapps.billing.models.BankStatementPaymentId; import com.nkapps.billing.models.DsApplication; import com.nkapps.billing.models.InvoiceNum; import com.nkapps.billing.models.Munis; import com.nkapps.billing.models.Payment; import com.nkapps.billing.models.PrintClaimPojo; import com.nkapps.billing.models.PrintClaimRegisterPojo; import com.nkapps.billing.models.PrintRegisterPojo; import com.nkapps.billing.models.ReportClick; import com.nkapps.billing.models.ReportClickBankStatement; import com.nkapps.billing.models.ReportClickBankStatementId; import com.nkapps.billing.models.ReportSmst; import com.nkapps.billing.models.ReportSmstBankStatement; import com.nkapps.billing.models.ReportSmstBankStatementId; /** * * @author nuraddin */ @Repository("bankStatementDao") public class BankStatementDaoImpl extends AbstractDao implements BankStatementDao { Logger logger = LoggerFactory.getLogger(BankStatementDaoImpl.class); @Autowired private Environment environment; @Autowired private MessageSource messageSource; @Autowired private PaymentDao paymentDao; @Override public int saveBankStatements(List<BankStatement> bsList) throws Exception { int errorCount = 0; ValidatorFactory factory = Validation.buildDefaultValidatorFactory(); Validator validator = factory.getValidator(); Session session = getSession(); Transaction transaction = session.beginTransaction(); int itr = 0; for (BankStatement bs : bsList) { itr++; Short transferable = 0; String transferableProps = null; Short transfered = 0; try { Set<String> fizTins = findFizTins(bs.getPaymentDetails()); String yurTin = findYurTin(bs.getPaymentDetails()); if (yurTin != null && !bs.getTin().equals(yurTin) && !isFizTin(bs.getTin()) && fizTins.isEmpty()) { transfered = -1; // 3 -litso throw new Exception("bank statement is payment detail yur tin not allowed"); } if (bs.getPaymentDetails().indexOf("$MUNIS$") > 0) { String tin = fizTins.iterator().next(); Payment payment = paymentDao.findPayment(tin, bs.getPaymentNum(), bs.getPaymentDate(), bs.getPaymentSum(), bs.getTin(), (short) 2); // munis payment to searching if (payment != null) { transfered = 2; // already transfered by munis BankStatementMunis bsm = new BankStatementMunis(); BankStatementMunisId bsmId = new BankStatementMunisId(); bsmId.setBankStatement(bs); bsmId.setMunis(payment.getMunisPayment().getId().getMunis()); bsm.setId(bsmId); session.save(bsm); throw new Exception("bank statement is already inserted by munis"); } } if (("fake tin").equals(bs.getTin()) && bs.getPaymentDetails().indexOf("CLICK") > 0) { transferable = 5; } else if (("fake tin").equals(bs.getTin()) && bs.getPaymentDetails().indexOf("CMC") > 0) { transferable = 6; } else { boolean isClaim = isClaim(session, bs); if (fizTins != null && !fizTins.isEmpty()) { if (isClaim) { // Trebovaniya String tin = fizTins.iterator().next(); // tolko dlya odin fiz inn transferable = 1; transferableProps = tin; } else { transferable = 2; transferableProps = StringUtils.join(fizTins, ","); } } else if (isClaim) { // Trebovaniya transferable = 3; transferableProps = yurTin; } else { transferable = 4; transferableProps = yurTin; } } } catch (Exception e) { // bank statement is not transferable } Set<ConstraintViolation<BankStatement>> constraints = validator.validate(bs); if (constraints.isEmpty()) { BankStatement bsPersisted = (BankStatement) session.get(BankStatement.class, bs.getId()); if (bsPersisted == null) { bs.setTransferable(transferable); bs.setTransferableProps(transferableProps); bs.setTransfered(transfered); // only when on saving bs.setReturnState((short) 0); // only when on saving session.save(bs); } else { bsPersisted.setTin(bs.getTin()); bsPersisted.setName(bs.getName()); bsPersisted.setMfo(bs.getMfo()); bsPersisted.setChet(bs.getChet()); bsPersisted.setPaymentNum(bs.getPaymentNum()); bsPersisted.setPaymentDate(bs.getPaymentDate()); bsPersisted.setPaymentSum(bs.getPaymentSum()); bsPersisted.setPaymentDetails(bs.getPaymentDetails()); bsPersisted.setTransferable(transferable); bsPersisted.setTransferableProps(transferableProps); bsPersisted.setIssuerSerialNumber(bs.getIssuerSerialNumber()); bsPersisted.setIssuerIp(bs.getIssuerIp()); bsPersisted.setDateUpdated(bs.getDateUpdated()); session.update(bsPersisted); } if (itr % 500 == 0) { session.flush(); session.clear(); } } else { errorCount++; logger.error("Error on saving, tin = " + bs.getTin()); } } transaction.commit(); session.close(); return errorCount; } @Override public void saveToPayment() throws Exception { List<BankStatement> bsList; Session sessionQuery = getSession(); String q = " SELECT " + " b " + " FROM BankStatement b" + " WHERE b.transferable IN (1,2,3,4) " + " AND b.transfered = 0 " + " AND b.bankStatementPayments IS EMPTY "; Query query = sessionQuery.createQuery(q); bsList = query.list(); sessionQuery.close(); sessionQuery = getSession(); ValidatorFactory factory = Validation.buildDefaultValidatorFactory(); Validator validator = factory.getValidator(); if (bsList != null) { LocalDateTime dateTime = LocalDateTime.now(); Session sessionTransaction = getSession(); Transaction transaction = sessionTransaction.beginTransaction(); int itr = 0; for (BankStatement bs : bsList) { itr++; try { transferBankStatementToPayment(sessionQuery, sessionTransaction, validator, bs, dateTime); if (itr % 500 == 0) { sessionTransaction.flush(); sessionTransaction.clear(); } } catch (Exception e) { logger.error("Error on payment saving: exception = " + e.getMessage() + ", bank_statement_id: " + bs.getId()); } } transaction.commit(); sessionTransaction.close(); } sessionQuery.close(); } @Override public void saveToReportClick() throws Exception { Session sessionQuery = getSession(); String q = " SELECT bs FROM BankStatement bs" + " WHERE bs.transferable = 5 AND (transfered = 0 OR bs.paymentDate > :paymentDate )"; Query query = sessionQuery.createQuery(q); Calendar cal = Calendar.getInstance(); cal.add(Calendar.DATE, -300); // update report click for last five days Date paymentDate = cal.getTime(); query.setParameter("paymentDate", paymentDate); List<BankStatement> bsList = query.list(); sessionQuery.close(); sessionQuery = getSession(); if (bsList != null) { LocalDateTime dateTime = LocalDateTime.now(); Session sessionTransaction = getSession(); Transaction transaction = sessionTransaction.beginTransaction(); for (BankStatement bs : bsList) { try { String operationDateStr = findOperationDate(bs.getPaymentDetails()); Date operationDate = new SimpleDateFormat("dd.MM.yyyy").parse(operationDateStr); query = sessionQuery.createQuery( "SELECT COALESCE(SUM(p.paymentSum),0) FROM Payment p WHERE p.paymentDate = :paymentDate AND p.sourceCode = 3"); query.setParameter("paymentDate", operationDate); BigDecimal clickPaymentSum = (BigDecimal) query.uniqueResult(); query = sessionTransaction .createQuery("SELECT rc FROM ReportClick rc WHERE rc.operationDate = :operationDate"); query.setParameter("operationDate", operationDate); ReportClick rc = (ReportClick) query.uniqueResult(); if (rc == null) { rc = new ReportClick(); rc.setOperationDate(operationDate); rc.setClickPaymentSum(clickPaymentSum); rc.setDateCreated(dateTime); rc.setDateUpdated(dateTime); sessionTransaction.save(rc); ReportClickBankStatement rcbs = new ReportClickBankStatement(); ReportClickBankStatementId rcbsId = new ReportClickBankStatementId(); rcbsId.setBankStatement(bs); rcbsId.setReportClick(rc); rcbs.setId(rcbsId); sessionTransaction.save(rcbs); } else { rc.setClickPaymentSum(clickPaymentSum); rc.setDateUpdated(dateTime); sessionTransaction.update(rc); query = sessionQuery.createQuery( "SELECT rcbs FROM ReportClickBankStatement rcbs JOIN rcbs.id.reportClick rc JOIN rcbs.id.bankStatement bs WHERE rc = :rc AND bs = :bs"); query.setParameter("rc", rc); query.setParameter("bs", bs); ReportClickBankStatement rcbs = (ReportClickBankStatement) query.uniqueResult(); if (rcbs == null) { rcbs = new ReportClickBankStatement(); ReportClickBankStatementId rcbsId = new ReportClickBankStatementId(); rcbsId.setBankStatement(bs); rcbsId.setReportClick(rc); rcbs.setId(rcbsId); sessionTransaction.save(rcbs); } } if (bs.getTransfered() == 0) { bs.setTransfered((short) 3); bs.setDateUpdated(dateTime); sessionTransaction.update(bs); } } catch (Exception e) { logger.error("Error on report click saving: bank_statement_id = " + bs.getId() + ", exception = " + e.getMessage()); } } transaction.commit(); sessionTransaction.close(); } sessionQuery.close(); } @Override public void saveToReportSmst() throws Exception { Session sessionQuery = getSession(); String q = " SELECT bs FROM BankStatement bs" + " WHERE bs.transferable = 6 AND (transfered = 0 OR bs.paymentDate > :paymentDate )"; Query query = sessionQuery.createQuery(q); Calendar cal = Calendar.getInstance(); cal.add(Calendar.DATE, -300); // update report click for last five days Date paymentDate = cal.getTime(); query.setParameter("paymentDate", paymentDate); List<BankStatement> bsList = query.list(); sessionQuery.close(); sessionQuery = getSession(); if (bsList != null) { LocalDateTime dateTime = LocalDateTime.now(); Session sessionTransaction = getSession(); Transaction transaction = sessionTransaction.beginTransaction(); for (BankStatement bs : bsList) { try { String operationDateStr = findOperationDate(bs.getPaymentDetails()); Date operationDate = new SimpleDateFormat("dd.MM.yyyy").parse(operationDateStr); query = sessionQuery.createQuery( "SELECT COALESCE(SUM(p.paymentSum),0) FROM Payment p WHERE p.paymentDate = :paymentDate AND p.sourceCode = 4"); query.setParameter("paymentDate", operationDate); BigDecimal smstPaymentSum = (BigDecimal) query.uniqueResult(); query = sessionTransaction .createQuery("SELECT rs FROM ReportSmst rs WHERE rs.operationDate = :operationDate"); query.setParameter("operationDate", operationDate); ReportSmst rs = (ReportSmst) query.uniqueResult(); if (rs == null) { rs = new ReportSmst(); rs.setOperationDate(operationDate); rs.setSmstPaymentSum(smstPaymentSum); rs.setDateCreated(dateTime); rs.setDateUpdated(dateTime); sessionTransaction.save(rs); ReportSmstBankStatement rsbs = new ReportSmstBankStatement(); ReportSmstBankStatementId rsbsId = new ReportSmstBankStatementId(); rsbsId.setBankStatement(bs); rsbsId.setReportSmst(rs); rsbs.setId(rsbsId); sessionTransaction.save(rsbs); } else { rs.setSmstPaymentSum(smstPaymentSum); rs.setDateUpdated(dateTime); sessionTransaction.update(rs); query = sessionQuery.createQuery( "SELECT rsbs FROM ReportSmstBankStatement rsbs JOIN rsbs.id.reportSmst rs JOIN rsbs.id.bankStatement bs WHERE rs = :rs AND bs = :bs"); query.setParameter("rs", rs); query.setParameter("bs", bs); ReportSmstBankStatement rsbs = (ReportSmstBankStatement) query.uniqueResult(); if (rsbs == null) { rsbs = new ReportSmstBankStatement(); ReportSmstBankStatementId rsbsId = new ReportSmstBankStatementId(); rsbsId.setBankStatement(bs); rsbsId.setReportSmst(rs); rsbs.setId(rsbsId); sessionTransaction.save(rsbs); } } if (bs.getTransfered() == 0) { bs.setTransfered((short) 3); bs.setDateUpdated(dateTime); sessionTransaction.update(bs); } } catch (Exception e) { logger.error("Error on report smst saving: bank_statement_id = " + bs.getId() + ", exception = " + e.getMessage()); } } transaction.commit(); sessionTransaction.close(); } sessionQuery.close(); } private void transferBankStatementToPayment(Session sessionQuery, Session sessionTransaction, Validator validator, BankStatement bs, LocalDateTime dateTime) throws Exception { switch (bs.getTransferable()) { case 1: { String tin = bs.getTransferableProps(); insertFizClaimPaymentByDebtor(sessionQuery, sessionTransaction, validator, bs, dateTime, tin); break; } case 2: { List<String> fizTins = Arrays.asList(bs.getTransferableProps().split(",")); insertFizPaymentByDebtor(sessionTransaction, validator, bs, dateTime, fizTins); break; } case 3: { String yurTin = bs.getTransferableProps(); insertClaimPayment(sessionQuery, sessionTransaction, validator, bs, yurTin, dateTime); break; } case 4: { String yurTin = bs.getTransferableProps(); insertPayment(sessionTransaction, validator, bs, yurTin, dateTime); break; } } } private void transferBankStatementToPaymentManually(Session sessionQuery, Session sessionTransaction, Validator validator, BankStatement bs, String tinManual, String tinDebtorManual, Long issuerSerialNumber, String issuerIp, LocalDateTime dateTime) throws Exception { if (bs.getTransferable() == -1) { boolean isClaim = isClaim(sessionQuery, bs); if (bs.getBankStatementPayments().isEmpty()) { if (isClaim) { insertClaimPaymentManually(sessionQuery, sessionTransaction, validator, bs, tinManual, tinDebtorManual, issuerSerialNumber, issuerIp, dateTime); } else { insertPaymentManually(sessionTransaction, validator, bs, tinManual, tinDebtorManual, issuerSerialNumber, issuerIp, dateTime); } } else { Payment payment = bs.getBankStatementPayments().iterator().next().getId().getPayment(); if (!payment.getKeyPayments().isEmpty()) { throw new Exception( messageSource.getMessage("bank_statement.editable.bs_payment_already_expensed_to_key", null, LocaleContextHolder.getLocale())); } // update payment payment.setTin(tinManual); payment.setTinDebtor(tinDebtorManual); payment.setIssuerSerialNumber(issuerSerialNumber); payment.setIssuerIp(issuerIp); payment.setDateUpdated(dateTime); sessionTransaction.update(payment); } // update bank statement bs.setIssuerSerialNumber(issuerSerialNumber); bs.setIssuerIp(issuerIp); bs.setDateUpdated(dateTime); sessionTransaction.update(bs); } else { if (bs.getTransfered() == 0) { switch (bs.getTransferable()) { case 1: { String tin = bs.getTransferableProps(); insertFizClaimPaymentByDebtor(sessionQuery, sessionTransaction, validator, bs, dateTime, tin); break; } case 2: { List<String> fizTins = Arrays.asList(bs.getTransferableProps().split(",")); insertFizPaymentByDebtor(sessionTransaction, validator, bs, dateTime, fizTins); break; } case 3: { String yurTin = bs.getTransferableProps(); insertClaimPayment(sessionQuery, sessionTransaction, validator, bs, yurTin, dateTime); break; } case 4: { String yurTin = bs.getTransferableProps(); insertPayment(sessionTransaction, validator, bs, yurTin, dateTime); break; } } } } } private void insertFizClaimPaymentByDebtor(Session sessionQuery, Session sessionTransaction, Validator validator, BankStatement bs, LocalDateTime dateTime, String tin) throws Exception { Query query = sessionQuery .createQuery("SELECT COALESCE(SUM(da.summa),0) FROM DsApplication da WHERE da.tin = :tin") .setParameter("tin", tin); BigDecimal allClaimSum = (BigDecimal) query.uniqueResult(); query = sessionQuery .createQuery("SELECT COALESCE(SUM(paymentSum),0) FROM Payment p WHERE p.tin = :tin AND p.claim = 1") .setParameter("tin", tin); BigDecimal paidClaimSum = (BigDecimal) query.uniqueResult(); BigDecimal realClaimSum, realPaymentSum; BigDecimal needClaimSum = allClaimSum.subtract(paidClaimSum); if (needClaimSum.compareTo(BigDecimal.ZERO) == 0) { realClaimSum = BigDecimal.ZERO; realPaymentSum = bs.getPaymentSum(); } else if (bs.getPaymentSum().compareTo(needClaimSum) <= 0) { realClaimSum = bs.getPaymentSum(); realPaymentSum = BigDecimal.ZERO; } else { realClaimSum = needClaimSum; realPaymentSum = bs.getPaymentSum().subtract(needClaimSum); } String tinDebtor = tin.equals(bs.getTin()) ? null : bs.getTin(); if (realClaimSum.compareTo(BigDecimal.ZERO) > 0) { Payment payment = new Payment(); payment.setTin(tin); payment.setPaymentNum(bs.getPaymentNum()); payment.setPaymentDate(bs.getPaymentDate()); payment.setPaymentSum(realClaimSum); // payment.setSourceCode((short) 1); payment.setState((short) 1); payment.setTinDebtor(tinDebtor); payment.setClaim((short) 1); // payment.setDateCreated(dateTime); payment.setDateUpdated(dateTime); Set<ConstraintViolation<Payment>> constraints = validator.validate(payment); if (constraints.isEmpty()) { sessionTransaction.save(payment); BankStatementPayment bsp = new BankStatementPayment(); BankStatementPaymentId bspId = new BankStatementPaymentId(); bspId.setBankStatement(bs); bspId.setPayment(payment); bsp.setId(bspId); sessionTransaction.save(bsp); } else { throw new Exception("payment isnot valid, tin = " + payment.getTin()); } } if (realPaymentSum.compareTo(BigDecimal.ZERO) > 0) { Payment payment = new Payment(); payment.setTin(tin); payment.setPaymentNum(bs.getPaymentNum()); payment.setPaymentDate(bs.getPaymentDate()); payment.setPaymentSum(realPaymentSum); // payment.setSourceCode((short) 1); payment.setState((short) 1); payment.setTinDebtor(tinDebtor); payment.setClaim((short) 0); // payment.setDateCreated(dateTime); payment.setDateUpdated(dateTime); Set<ConstraintViolation<Payment>> constraints = validator.validate(payment); if (constraints.isEmpty()) { sessionTransaction.save(payment); BankStatementPayment bsp = new BankStatementPayment(); BankStatementPaymentId bspId = new BankStatementPaymentId(); bspId.setBankStatement(bs); bspId.setPayment(payment); bsp.setId(bspId); sessionTransaction.save(bsp); } else { throw new Exception("payment isnot valid, tin = " + payment.getTin()); } } bs.setTransfered((short) 1); sessionTransaction.update(bs); } private void insertFizPaymentByDebtor(Session sessionTransaction, Validator validator, BankStatement bs, LocalDateTime dateTime, List<String> fizTins) throws Exception { BigDecimal fullSum = bs.getPaymentSum(); BigDecimal keyCost = new BigDecimal(getKeyCost()); int listSize = fizTins.size(); int currentIndex = 0; for (String fizTin : fizTins) { currentIndex++; BigDecimal currentSum; if (currentIndex == listSize || fullSum.compareTo(keyCost) < 0) { currentSum = fullSum; fullSum = BigDecimal.ZERO; } else { currentSum = keyCost; fullSum = fullSum.subtract(keyCost); } if (currentSum.compareTo(BigDecimal.ZERO) > 0) { String tinDebtor = fizTin.equals(bs.getTin()) ? null : bs.getTin(); Payment payment = new Payment(); payment.setTin(fizTin); payment.setPaymentNum(bs.getPaymentNum()); payment.setPaymentDate(bs.getPaymentDate()); payment.setPaymentSum(currentSum); payment.setSourceCode((short) 1); payment.setState((short) 1); payment.setTinDebtor(tinDebtor); payment.setClaim((short) 0); payment.setDateCreated(dateTime); payment.setDateUpdated(dateTime); Set<ConstraintViolation<Payment>> constraints = validator.validate(payment); if (constraints.isEmpty()) { sessionTransaction.save(payment); BankStatementPayment bsp = new BankStatementPayment(); BankStatementPaymentId bspId = new BankStatementPaymentId(); bspId.setBankStatement(bs); bspId.setPayment(payment); bsp.setId(bspId); sessionTransaction.save(bsp); } else { throw new Exception("payment isnot valid, tin = " + payment.getTin()); } } if (fullSum.compareTo(BigDecimal.ZERO) <= 0) { break; } } bs.setTransfered((short) 1); sessionTransaction.update(bs); } private void insertClaimPayment(Session sessionQuery, Session sessionTransaction, Validator validator, BankStatement bs, String yurTin, LocalDateTime dateTime) throws Exception { Query query = sessionQuery .createQuery("SELECT COALESCE(SUM(da.summa),0) FROM DsApplication da WHERE da.tin = :tin") .setParameter("tin", bs.getTin()); BigDecimal allClaimSum = (BigDecimal) query.uniqueResult(); query = sessionQuery .createQuery("SELECT COALESCE(SUM(paymentSum),0) FROM Payment p WHERE p.tin = :tin AND p.claim = 1") .setParameter("tin", bs.getTin()); BigDecimal paidClaimSum = (BigDecimal) query.uniqueResult(); BigDecimal realClaimSum, realPaymentSum; BigDecimal needClaimSum = allClaimSum.subtract(paidClaimSum); if (needClaimSum.compareTo(BigDecimal.ZERO) == 0) { realClaimSum = BigDecimal.ZERO; realPaymentSum = bs.getPaymentSum(); } else if (bs.getPaymentSum().compareTo(needClaimSum) <= 0) { realClaimSum = bs.getPaymentSum(); realPaymentSum = BigDecimal.ZERO; } else { realClaimSum = needClaimSum; realPaymentSum = bs.getPaymentSum().subtract(needClaimSum); } String tin, tinDebtor; if ("201122919".equals(bs.getTin())) { tin = yurTin; tinDebtor = bs.getTin(); } else { tin = bs.getTin(); tinDebtor = null; } if (realClaimSum.compareTo(BigDecimal.ZERO) > 0) { Payment payment = new Payment(); payment.setTin(tin); payment.setPaymentNum(bs.getPaymentNum()); payment.setPaymentDate(bs.getPaymentDate()); payment.setPaymentSum(realClaimSum); // payment.setSourceCode((short) 1); payment.setState((short) 1); payment.setTinDebtor(tinDebtor); payment.setClaim((short) 1); // payment.setDateCreated(dateTime); payment.setDateUpdated(dateTime); Set<ConstraintViolation<Payment>> constraints = validator.validate(payment); if (constraints.isEmpty()) { sessionTransaction.save(payment); BankStatementPayment bsp = new BankStatementPayment(); BankStatementPaymentId bspId = new BankStatementPaymentId(); bspId.setBankStatement(bs); bspId.setPayment(payment); bsp.setId(bspId); sessionTransaction.save(bsp); } else { throw new Exception("payment isnot valid, tin = " + payment.getTin()); } } if (realPaymentSum.compareTo(BigDecimal.ZERO) > 0) { Payment payment = new Payment(); payment.setTin(tin); payment.setPaymentNum(bs.getPaymentNum()); payment.setPaymentDate(bs.getPaymentDate()); payment.setPaymentSum(realPaymentSum); // payment.setSourceCode((short) 1); payment.setState((short) 1); payment.setTinDebtor(tinDebtor); payment.setClaim((short) 0); // payment.setDateCreated(dateTime); payment.setDateUpdated(dateTime); Set<ConstraintViolation<Payment>> constraints = validator.validate(payment); if (constraints.isEmpty()) { sessionTransaction.save(payment); BankStatementPayment bsp = new BankStatementPayment(); BankStatementPaymentId bspId = new BankStatementPaymentId(); bspId.setBankStatement(bs); bspId.setPayment(payment); bsp.setId(bspId); sessionTransaction.save(bsp); } else { throw new Exception("payment isnot valid, tin = " + payment.getTin()); } } bs.setTransfered((short) 1); sessionTransaction.update(bs); } private void insertPayment(Session sessionTransaction, Validator validator, BankStatement bs, String yurTin, LocalDateTime dateTime) throws Exception { Payment payment = new Payment(); if ("201122919".equals(bs.getTin())) { payment.setTin(yurTin); payment.setTinDebtor(bs.getTin()); } else { payment.setTin(bs.getTin()); } payment.setPaymentNum(bs.getPaymentNum()); payment.setPaymentDate(bs.getPaymentDate()); payment.setPaymentSum(bs.getPaymentSum()); payment.setSourceCode((short) 1); payment.setState((short) 1); payment.setClaim((short) 0); payment.setDateCreated(dateTime); payment.setDateUpdated(dateTime); Set<ConstraintViolation<Payment>> constraints = validator.validate(payment); if (constraints.isEmpty()) { sessionTransaction.save(payment); BankStatementPayment bsp = new BankStatementPayment(); BankStatementPaymentId bspId = new BankStatementPaymentId(); bspId.setBankStatement(bs); bspId.setPayment(payment); bsp.setId(bspId); sessionTransaction.save(bsp); } else { throw new Exception("payment isnot valid, tin = " + payment.getTin()); } bs.setTransfered((short) 1); sessionTransaction.update(bs); } private void insertClaimPaymentManually(Session sessionQuery, Session sessionTransaction, Validator validator, BankStatement bs, String tin, String tinDebtor, Long issuerSerialNumber, String issuerIp, LocalDateTime dateTime) throws Exception { Query query = sessionQuery .createQuery("SELECT COALESCE(SUM(da.summa),0) FROM DsApplication da WHERE da.tin = :tin") .setParameter("tin", tin); BigDecimal allClaimSum = (BigDecimal) query.uniqueResult(); query = sessionQuery .createQuery("SELECT COALESCE(SUM(paymentSum),0) FROM Payment p WHERE p.tin = :tin AND p.claim = 1") .setParameter("tin", tin); BigDecimal paidClaimSum = (BigDecimal) query.uniqueResult(); BigDecimal realClaimSum, realPaymentSum; BigDecimal needClaimSum = allClaimSum.subtract(paidClaimSum); if (needClaimSum.compareTo(BigDecimal.ZERO) == 0) { realClaimSum = BigDecimal.ZERO; realPaymentSum = bs.getPaymentSum(); } else if (bs.getPaymentSum().compareTo(needClaimSum) <= 0) { realClaimSum = bs.getPaymentSum(); realPaymentSum = BigDecimal.ZERO; } else { realClaimSum = needClaimSum; realPaymentSum = bs.getPaymentSum().subtract(needClaimSum); } if (realClaimSum.compareTo(BigDecimal.ZERO) > 0) { Payment payment = new Payment(); payment.setTin(tin); // payment.setPaymentNum(bs.getPaymentNum()); payment.setPaymentDate(bs.getPaymentDate()); payment.setPaymentSum(realClaimSum); // payment.setSourceCode((short) 1); payment.setState((short) 1); payment.setTinDebtor(tinDebtor); payment.setClaim((short) 1); // payment.setIssuerSerialNumber(issuerSerialNumber); payment.setIssuerIp(issuerIp); payment.setDateCreated(dateTime); payment.setDateUpdated(dateTime); Set<ConstraintViolation<Payment>> constraints = validator.validate(payment); if (constraints.isEmpty()) { sessionTransaction.save(payment); BankStatementPayment bsp = new BankStatementPayment(); BankStatementPaymentId bspId = new BankStatementPaymentId(); bspId.setBankStatement(bs); bspId.setPayment(payment); bsp.setId(bspId); sessionTransaction.save(bsp); } else { throw new Exception("payment isnot valid, tin = " + payment.getTin()); } } if (realPaymentSum.compareTo(BigDecimal.ZERO) > 0) { Payment payment = new Payment(); payment.setTin(tin); // payment.setPaymentNum(bs.getPaymentNum()); payment.setPaymentDate(bs.getPaymentDate()); payment.setPaymentSum(realPaymentSum); // payment.setSourceCode((short) 1); payment.setState((short) 1); payment.setTinDebtor(tinDebtor); payment.setClaim((short) 0); // payment.setIssuerSerialNumber(issuerSerialNumber); payment.setIssuerIp(issuerIp); payment.setDateCreated(dateTime); payment.setDateUpdated(dateTime); Set<ConstraintViolation<Payment>> constraints = validator.validate(payment); if (constraints.isEmpty()) { sessionTransaction.save(payment); BankStatementPayment bsp = new BankStatementPayment(); BankStatementPaymentId bspId = new BankStatementPaymentId(); bspId.setBankStatement(bs); bspId.setPayment(payment); bsp.setId(bspId); sessionTransaction.save(bsp); } else { throw new Exception("payment isnot valid, tin = " + payment.getTin()); } } bs.setTransfered((short) 1); sessionTransaction.update(bs); } private void insertPaymentManually(Session sessionTransaction, Validator validator, BankStatement bs, String tin, String tinDebtor, Long issuerSerialNumber, String issuerIp, LocalDateTime dateTime) throws Exception { Payment payment = new Payment(); payment.setTin(tin); // payment.setPaymentNum(bs.getPaymentNum()); payment.setPaymentDate(bs.getPaymentDate()); payment.setPaymentSum(bs.getPaymentSum()); payment.setSourceCode((short) 1); payment.setState((short) 1); payment.setTinDebtor(tinDebtor); payment.setClaim((short) 0); payment.setIssuerSerialNumber(issuerSerialNumber); payment.setIssuerIp(issuerIp); payment.setDateCreated(dateTime); payment.setDateUpdated(dateTime); Set<ConstraintViolation<Payment>> constraints = validator.validate(payment); if (constraints.isEmpty()) { sessionTransaction.save(payment); BankStatementPayment bsp = new BankStatementPayment(); BankStatementPaymentId bspId = new BankStatementPaymentId(); bspId.setBankStatement(bs); bspId.setPayment(payment); bsp.setId(bspId); sessionTransaction.save(bsp); } else { throw new Exception("payment isnot valid, tin = " + payment.getTin()); } bs.setTransfered((short) 1); sessionTransaction.update(bs); } @Override public String getKeyCost() { return environment.getRequiredProperty("key_cost"); } private String findOperationDate(String paymentDetails) { String result = null; String regex = "[0-9]{2}.[0-9]{2}.[0-9]{4}"; Matcher m = Pattern.compile(regex).matcher(paymentDetails); while (m.find()) { result = m.group(); } return result; } private String findYurTin(String paymentDetails) { String result = null; String regex = "[23]\\d{0}+[0-9]\\d{7}"; Matcher m = Pattern.compile(regex).matcher(paymentDetails); while (m.find()) { String tin = m.group(); if (validTin(tin)) { result = tin; } } return result; } private Set<String> findFizTins(String paymentDetails) { Set<String> set = new LinkedHashSet<>(); String regex = "[456]\\d{0}+[0-9]\\d{7}"; Matcher m = Pattern.compile(regex).matcher(paymentDetails); while (m.find()) { String tin = m.group(); if (validTin(tin)) { set.add(tin); } } return set; } private boolean isFizTin(String tin) { if (tin.startsWith("4") || tin.startsWith("5") || tin.startsWith("6")) { return true; } else { return false; } } private boolean validTin(String tin) { String tin9 = ""; double sum = 0; if (tin != null && tin.length() == 9) { tin9 = tin.substring(8, 9); sum = sum + Double.parseDouble(tin.substring(0, 1)) * 37; sum = sum + Double.parseDouble(tin.substring(1, 2)) * 29; sum = sum + Double.parseDouble(tin.substring(2, 3)) * 23; sum = sum + Double.parseDouble(tin.substring(3, 4)) * 19; sum = sum + Double.parseDouble(tin.substring(4, 5)) * 17; sum = sum + Double.parseDouble(tin.substring(5, 6)) * 13; sum = sum + Double.parseDouble(tin.substring(6, 7)) * 7; sum = sum + Double.parseDouble(tin.substring(7, 8)) * 3; sum = sum / 11; sum = Math.floor((1 - (sum - Math.floor(sum))) * 9); if (tin9.equals(String.valueOf(sum).substring(0, 1))) { return true; } } return false; } private boolean isClaim(Session session, BankStatement bs) { Calendar cal = Calendar.getInstance(); cal.set(2015, 0, 15); if (bs.getPaymentDate().compareTo(cal.getTime()) <= 0) { return false; } String[] matches = { "", "", "qarz", "", "", "", "?", "", "?", "", "2010", "2011", "2012", "2013", "2010 ", "2011 ", "2012 ", "2013 " }; String det = bs.getPaymentDetails().toLowerCase(); for (String match : matches) { if (det.contains(match)) { return true; } } String claimNum = regexFindClaimNum(det); if (claimNum == null) { return false; } return isFoundClaimNum(session, claimNum, bs.getTin()); } private String regexFindClaimNum(String paymentDetails) { String result = null; String regex = "[1][0-9]{6}"; Matcher m = Pattern.compile(regex).matcher(paymentDetails); while (m.find()) { String tin = m.group(); if (validTin(tin)) { result = tin; } } return result; } private boolean isFoundClaimNum(Session session, String claimNum, String tin) { DsApplication da = (DsApplication) session.get(DsApplication.class, Long.parseLong(claimNum)); if (da == null) { return false; } else { return tin.equals(da.getTin()); } } @Override public List<BankStatementListPojo> list(HashMap parameters) throws Exception { List<BankStatementListPojo> 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.mfo AS mfo,bs.chet AS chet," + " bs.paymentNum AS paymentNum, bs.paymentDate AS paymentDate, bs.paymentSum AS paymentSum," + " bs.paymentDetails AS paymentDetails," + " bs.transfered AS transfered," + " bs.dateUpdated AS dateUpdated" + " FROM BankStatement bs" + whereStr + " ORDER BY bs.paymentDate, bs.paymentSum "; Query query = session.createQuery(q); query.setResultTransformer(Transformers.aliasToBean(BankStatementListPojo.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 void editPaymentDate(String bankStatementId, Date paymentDate, 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())); } LocalDateTime dateTime = LocalDateTime.now(); bs.setPaymentDate(paymentDate); bs.setIssuerSerialNumber(issuerSerialNumber); bs.setIssuerIp(issuerIp); bs.setDateUpdated(dateTime); session.update(bs); transaction.commit(); session.close(); } @Override public void editActionKey(String bankStatementId, String tin, Long issuerSerialNumber, String issuerIp) throws Exception { Session sessionQuery = getSession(); Session sessionTransaction = getSession(); Transaction transaction = sessionTransaction.beginTransaction(); BankStatement bs = (BankStatement) sessionTransaction.get(BankStatement.class, bankStatementId); if (bs == null) { throw new Exception(messageSource.getMessage("bank_statement.editable.bs_not_found", null, LocaleContextHolder.getLocale())); } if (bs.getBankStatementPayments().size() > 1) { throw new Exception(messageSource.getMessage("bank_statement.editable.bs_contains_many_payments", null, LocaleContextHolder.getLocale())); } if (!validTin(tin)) { throw new Exception(messageSource.getMessage("bank_statement.editable.tin_not_valid", null, LocaleContextHolder.getLocale())); } if (bs.getTransferable() == 0) { throw new Exception(messageSource.getMessage("bank_statement.editable.bs_not_transferable", null, LocaleContextHolder.getLocale())); } ValidatorFactory factory = Validation.buildDefaultValidatorFactory(); Validator validator = factory.getValidator(); LocalDateTime dateTime = LocalDateTime.now(); String tinDebtor = tin.equals(bs.getTin()) ? null : bs.getTin(); transferBankStatementToPaymentManually(sessionQuery, sessionTransaction, validator, bs, tin, tinDebtor, issuerSerialNumber, issuerIp, dateTime); transaction.commit(); sessionTransaction.close(); sessionQuery.close(); } @Override public void checkAndRemoveBankStatement(String bankStatementId) 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())); } boolean canDelete = true; // check payment or munis if (!bs.getBankStatementPayments().isEmpty()) { for (BankStatementPayment bsp : bs.getBankStatementPayments()) { Payment payment = bsp.getId().getPayment(); if (!payment.getKeyPayments().isEmpty()) { // already payment buyed Key, that is why must be not deleted canDelete = false; break; } } } else if (bs.getBankStatementMunis() != null) { Munis m = bs.getBankStatementMunis().getId().getMunis(); Payment payment = m.getMunisPayment().getId().getPayment(); if (!payment.getKeyPayments().isEmpty()) { // already payment buyed Key, that is why must be not deleted canDelete = false; } } if (canDelete) { for (BankStatementPayment bsp : bs.getBankStatementPayments()) { session.delete(bsp); session.delete(bsp.getId().getPayment()); } if (bs.getBankStatementMunis() != null) { Munis m = bs.getBankStatementMunis().getId().getMunis(); Payment payment = m.getMunisPayment().getId().getPayment(); if (payment != null) { session.delete(m.getMunisPayment()); session.delete(payment); } session.delete(bs.getBankStatementMunis()); session.delete(m); } session.delete(bs); } else { throw new Exception( messageSource.getMessage("bank_statement.editable.bs_payment_already_expensed_to_key", null, LocaleContextHolder.getLocale())); } transaction.commit(); session.close(); } @Override public void updateInvoiceNums(Integer year) throws Exception { Session sessionQuery = getSession(); InvoiceNum in = (InvoiceNum) sessionQuery.get(InvoiceNum.class, year); if (in == null) { in = new InvoiceNum(year); in.setNum(0l); } Long num = in.getNum(); SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy H:m:s.S"); Date paymentDateBegin = sdf.parse("01.01." + year + " 00:00:00.000"); Date paymentDateEnd = sdf.parse("31.12." + year + " 23:59:59.999"); String q = " SELECT bs " + " FROM BankStatement bs LEFT JOIN FETCH bs.bankStatementPayments bsp LEFT JOIN FETCH bsp.id.payment p" + " WHERE bs.paymentDate BETWEEN :paymentDateBegin" + " AND :paymentDateEnd" + " AND bs.invoiceNum IS NULL" + " ORDER BY bs.paymentDate, bs.paymentSum "; Query query = sessionQuery.createQuery(q); query.setParameter("paymentDateBegin", paymentDateBegin); query.setParameter("paymentDateEnd", paymentDateEnd); List<BankStatement> bsList = query.list(); for (BankStatement bs : bsList) { if (bs.getBankStatementPayments().isEmpty()) { bs.setInvoiceNum(++num); } else { boolean isClaim = false; for (BankStatementPayment bsp : bs.getBankStatementPayments()) { Payment payment = bsp.getId().getPayment(); if (payment.getClaim() == 1) { isClaim = true; break; } } if (isClaim) { query = sessionQuery.createQuery("SELECT MAX(da.id) FROM DsApplication da WHERE tin = :tin") .setParameter("tin", bs.getTin()); Long dsApplicationId = (Long) query.uniqueResult(); if (dsApplicationId == null) { bs.setInvoiceNum(++num); } else { bs.setInvoiceNum(dsApplicationId); // ds_application id } } else { bs.setInvoiceNum(++num); } } } sessionQuery.close(); Session sessionTransaction = getSession(); Transaction transaction = sessionTransaction.beginTransaction(); in.setNum(num); sessionTransaction.saveOrUpdate(in); int itr = 0; for (BankStatement bs : bsList) { itr++; sessionTransaction.update(bs); if (itr % 500 == 0) { sessionTransaction.flush(); sessionTransaction.clear(); } } transaction.commit(); sessionTransaction.close(); } @Override public BankStatement getByBankStatementId(String bankStatementId) throws Exception { Session session = getSession(); BankStatement bs = (BankStatement) session.get(BankStatement.class, bankStatementId); session.close(); return bs; } @Override public List<BankStatement> getListByInvoice(Integer year, Long from, Long to) throws Exception { Session session = getSession(); SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy H:m:s.S"); Date paymentDateBegin = sdf.parse("01.01." + year + " 00:00:00.000"); Date paymentDateEnd = sdf.parse("31.12." + year + " 23:59:59.999"); String q = " SELECT bs" + " FROM BankStatement bs " + " WHERE bs.paymentDate BETWEEN :paymentDateBegin AND :paymentDateEnd" + " AND bs.invoiceNum BETWEEN :from AND :to " + " AND bs.tin <> '201589463' " + " ORDER BY bs.invoiceNum "; Query query = session.createQuery(q); query.setParameter("paymentDateBegin", paymentDateBegin); query.setParameter("paymentDateEnd", paymentDateEnd); query.setParameter("from", from); query.setParameter("to", to); List<BankStatement> list = query.list(); session.close(); return list; } @Override public List<PrintClaimPojo> getPrintClaimList(Date periodStart, Date periodEnd) throws Exception { Session session = getSession(); String q = " SELECT bs.tin as tin, bs.mfo as mfo," + " bs.chet as chet, bs.paymentNum as paymentNum," + " bs.paymentDate as paymentDate, p.paymentSum as paymentSum," + " bs.paymentDetails as paymentDetails " + " FROM BankStatement bs JOIN bs.bankStatementPayments bsp JOIN bsp.id.payment p" + " WHERE p.claim = 1 AND bs.paymentDate BETWEEN :periodStart AND :periodEnd " + " ORDER BY bs.paymentDate, bs.paymentSum"; Query query = session.createQuery(q); query.setParameter("periodStart", periodStart); query.setParameter("periodEnd", periodEnd); query.setResultTransformer(Transformers.aliasToBean(PrintClaimPojo.class)); List<PrintClaimPojo> listPojo = query.list(); session.close(); return listPojo; } @Override public List<PrintRegisterPojo> getPrintRegisterList(Date periodStart, Date periodEnd) throws Exception { Session session = getSession(); String q = " SELECT bs.invoiceNum as invoiceNum," + " bs.paymentNum as paymentNum, bs.paymentDate as paymentDate," + " bs.tin as tin, bs.name as name, bs.paymentSum as paymentSum " + " FROM BankStatement bs" + " WHERE bs.paymentDate BETWEEN :periodStart AND :periodEnd" + " AND bs.tin <> '201589463' AND bs.invoiceNum < 1000000" + " ORDER BY bs.invoiceNum"; Query query = session.createQuery(q); query.setParameter("periodStart", periodStart); query.setParameter("periodEnd", periodEnd); query.setResultTransformer(Transformers.aliasToBean(PrintRegisterPojo.class)); List<PrintRegisterPojo> listPojo = query.list(); session.close(); return listPojo; } @Override public List<PrintClaimRegisterPojo> getPrintClaimRegisterList(Date periodStart, Date periodEnd) throws Exception { Session session = getSession(); String q = " SELECT da.id as invoiceNum, p.paymentNum as paymentNum," + " p.paymentDate as paymentDate, p.tin as tin, da.name as name," + " p.paymentSum as paymentSum " + " FROM Payment p, DsApplication da" + " WHERE p.tin = da.tin AND p.claim = 1 AND p.sourceCode = 1" + " AND p.paymentDate BETWEEN :periodStart AND :periodEnd" + " ORDER BY p.paymentDate, p.paymentSum"; Query query = session.createQuery(q); query.setParameter("periodStart", periodStart); query.setParameter("periodEnd", periodEnd); query.setResultTransformer(Transformers.aliasToBean(PrintClaimRegisterPojo.class)); List<PrintClaimRegisterPojo> listPojo = query.list(); session.close(); return listPojo; } }