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.mycompany.CRMFly.hibernateAccess; import com.mycompany.CRMFly.ManagedBeans.Statistics; import com.mycompany.CRMFly.entities.Clients; import com.mycompany.CRMFly.entities.Contacts; import com.mycompany.CRMFly.entities.Contracts; import com.mycompany.CRMFly.entities.ContragentsInContract; import com.mycompany.CRMFly.entities.Documents; import com.mycompany.CRMFly.entities.Payments; import com.mycompany.CRMFly.entities.PositionsInContract; import com.mycompany.CRMFly.entities.Products; import com.mycompany.CRMFly.entities.Shipments; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.GregorianCalendar; import java.util.Iterator; import java.util.List; import org.hibernate.Query; import org.hibernate.SessionFactory; import org.hibernate.criterion.Disjunction; import org.hibernate.criterion.Restrictions; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Repository; /** * * @author ?? */ @Repository public class ContractsDAOImpl implements ContractsDAO { @Autowired private SessionFactory sessionFactory; @Autowired private ContactsDAO contactsDAO; @Autowired private DocumentsDAO documentsDAO; @Autowired private PaymentsDAO paymentsDAO; @Override public void addContract(Contracts contract) { List<Contacts> contTemp = contract.getAddContacts(); List<Documents> docTemp = contract.getDocsForContract(); List<Payments> payTemp = contract.getPaymentsForContract(); List<ContragentsInContract> contrTemp = contract.getContragents(); List<PositionsInContract> prodTemp = contract.getProductPositions(); sessionFactory.getCurrentSession().save(contract); if (contTemp != null && contTemp.size() != 0) { org.hibernate.Session sess = sessionFactory.getCurrentSession(); sess.enableFetchProfile("contracts-with-contacts"); contTemp = contactsDAO.getFromProxy(contTemp); for (Contacts contact : contTemp) { contact.getContractsConnectedWithContact().add(contract); contactsDAO.changeContact(contact); } } if (payTemp != null && payTemp.size() != 0) { org.hibernate.Session sess = sessionFactory.getCurrentSession(); sess.enableFetchProfile("contracts-with-payments"); payTemp = paymentsDAO.getFromProxy(payTemp); for (Payments payment : payTemp) { payment.setContractOnPayment(contract); paymentsDAO.changePayment(payment); } } if (docTemp != null && docTemp.size() != 0) { org.hibernate.Session sess = sessionFactory.getCurrentSession(); sess.enableFetchProfile("contracts-with-documents"); docTemp = documentsDAO.getFromProxy(docTemp); for (Documents document : docTemp) { document.setContract(contract); documentsDAO.changeDocument(document); } } if (contrTemp != null && contrTemp.size() != 0) { for (ContragentsInContract contragent : contrTemp) { contragent.setContract(contract); sessionFactory.getCurrentSession().save(contragent); } } if (prodTemp != null && prodTemp.size() != 0) { prodTemp = contract.getProductPositions(); for (PositionsInContract position : prodTemp) { position.setContract(contract); sessionFactory.getCurrentSession().save(position); } } } public void addContactConnection(Contracts contract, Contacts contact) { org.hibernate.Session sess = sessionFactory.getCurrentSession(); sess.enableFetchProfile("contracts-with-contacts"); contract.getAddContacts().add(contact); sess.update(contract); contact.getContractsConnectedWithContact().add(contract); sess.update(contact); } public void addDocumentConnection(Contracts contract, Documents document) { org.hibernate.Session sess = sessionFactory.getCurrentSession(); sess.enableFetchProfile("contracts-with-documents"); contract.getDocsForContract().add(document); sess.update(contract); document.setContract(contract); sess.update(contract); } public void addPaymentConnection(Contracts contract, Payments payment) { org.hibernate.Session sess = sessionFactory.getCurrentSession(); sess.enableFetchProfile("contracts-with-payments"); contract.getPaymentsForContract().add(payment); sess.update(contract); payment.setContractOnPayment(contract); sess.update(contract); } public void addProductConnection(Contracts contract, PositionsInContract position) { org.hibernate.Session sess = sessionFactory.getCurrentSession(); sess.enableFetchProfile("contracts-with-products"); sess.enableFetchProfile("products-with-contracts"); contract.getProductPositions().add(position); sess.update(contract); sess.save(position); Products product = position.getProduct(); product.getPositionsInContract().add(position); sess.update(product); } public void addContragentConnection(Contracts contract, ContragentsInContract agent) { org.hibernate.Session sess = sessionFactory.getCurrentSession(); sess.enableFetchProfile("clients-with-contracts"); sess.enableFetchProfile("contracts-with-clients"); contract.getContragents().add(agent); sess.update(contract); sess.save(agent); Clients client = agent.getClient(); client.getParticipantInContracts().add(agent); sess.update(client); } public void deleteContactConnection(Contracts contract, Contacts contact) { org.hibernate.Session sess = sessionFactory.getCurrentSession(); sess.enableFetchProfile("contracts-with-contacts"); contract.getAddContacts().remove(contact); sess.update(contract); contact.getContractsConnectedWithContact().remove(contract); sess.update(contact); } public void deleteDocumentConnection(Contracts contract, Documents document) { org.hibernate.Session sess = sessionFactory.getCurrentSession(); sess.enableFetchProfile("contracts-with-documents"); contract.getDocsForContract().remove(document); sess.update(contract); document.setContract(null); sess.update(contract); } public void deletePaymentConnection(Contracts contract, Payments payment) { org.hibernate.Session sess = sessionFactory.getCurrentSession(); sess.enableFetchProfile("contracts-with-payments"); contract.getPaymentsForContract().remove(payment); sess.update(contract); payment.setContractOnPayment(null); sess.update(contract); } public void deleteProductConnection(Contracts contract, PositionsInContract position) { org.hibernate.Session sess = sessionFactory.getCurrentSession(); sess.enableFetchProfile("contracts-with-products"); sess.enableFetchProfile("products-with-contracts"); contract.getProductPositions().remove(position); sess.update(contract); Products product = position.getProduct(); product.getPositionsInContract().remove(position); sess.update(product); sess.delete(position); } public void deleteContragentConnection(Contracts contract, ContragentsInContract agent) { org.hibernate.Session sess = sessionFactory.getCurrentSession(); sess.enableFetchProfile("clients-with-contracts"); sess.enableFetchProfile("contracts-with-clients"); contract.getContragents().remove(agent); sess.update(contract); Clients client = agent.getClient(); client.getParticipantInContracts().remove(agent); sess.update(client); sess.delete(agent); } @Override public List<Contracts> listContracts() { return sessionFactory.getCurrentSession().createQuery("from com.mycompany.CRMFly.entities.Contracts") .list(); } @Override public void removeContract(Contracts contract) { org.hibernate.Session sess = sessionFactory.getCurrentSession(); contract = (Contracts) sess.get(Contracts.class, contract.getId()); if (null != contract) { List<Contacts> contTemp = contract.getAddContacts(); List<Documents> docTemp = contract.getDocsForContract(); List<Payments> payTemp = contract.getPaymentsForContract(); List<ContragentsInContract> contrTemp = contract.getContragents(); List<PositionsInContract> prodTemp = contract.getProductPositions(); if (contTemp != null && contTemp.size() != 0) { sess.enableFetchProfile("contracts-with-contacts"); contTemp = contactsDAO.getFromProxy(contTemp); for (Contacts contact : contTemp) { contact.getContractsConnectedWithContact().remove(contract); sess.update(contact); } } if (payTemp != null && payTemp.size() != 0) { sess.enableFetchProfile("contracts-with-payments"); payTemp = paymentsDAO.getFromProxy(payTemp); for (Payments payment : payTemp) { payment.setContractOnPayment(null); sess.update(payment); } } if (docTemp != null && docTemp.size() != 0) { sess.enableFetchProfile("contracts-with-documents"); docTemp = documentsDAO.getFromProxy(docTemp); for (Documents document : docTemp) { document.setContract(null); sess.update(document); } } if (contrTemp != null && contrTemp.size() != 0) { for (ContragentsInContract contragent : contrTemp) { sess.delete(contragent); } } if (prodTemp != null && prodTemp.size() != 0) { prodTemp = contract.getProductPositions(); for (PositionsInContract position : prodTemp) { sess.delete(position); } } sess.update(contract); sessionFactory.getCurrentSession().delete(contract); } } @Override public void changeContract(Contracts contract) { sessionFactory.getCurrentSession().update(contract); } @Override public Contracts getContractForId(Long id) { // return (Contracts) sessionFactory.getCurrentSession(). // load(Contracts.class, id); return (Contracts) sessionFactory.getCurrentSession().get(Contracts.class, id); } @Override public List<Contracts> getAllContracts() { return sessionFactory.getCurrentSession().createCriteria(Contracts.class).list(); } @Override public List<Documents> getDocumentsforContract(Long id) { org.hibernate.Session sess = sessionFactory.getCurrentSession(); sess.enableFetchProfile("contracts-with-documents"); Contracts contract = (Contracts) sess.get(Contracts.class, id); return contract.getDocsForContract(); } @Override public List<Payments> getPaymentsforContract(Long id) { org.hibernate.Session sess = sessionFactory.getCurrentSession(); sess.enableFetchProfile("contracts-with-payments"); Contracts contract = (Contracts) sess.get(Contracts.class, id); return contract.getPaymentsForContract(); } @Override public List<Shipments> getShipmentsOnContract(Long id) { org.hibernate.Session sess = sessionFactory.getCurrentSession(); sess.enableFetchProfile("contracts-with-shipments"); Contracts contract = (Contracts) sess.get(Contracts.class, id); return contract.getShipmentsOnContract(); } @Override public List<Contacts> getAddContactsforContract(Long id) { org.hibernate.Session sess = sessionFactory.getCurrentSession(); sess.enableFetchProfile("contracts-with-contacts"); Contracts contract = (Contracts) sess.get(Contracts.class, id); return contract.getAddContacts(); } @Override public List<ContragentsInContract> getContragentsForContract(Long id) { org.hibernate.Session sess = sessionFactory.getCurrentSession(); sess.enableFetchProfile("contracts-with-contragents"); Contracts contract = (Contracts) sess.get(Contracts.class, id); return contract.getContragents(); } @Override public List<PositionsInContract> getPositionsForContract(Long id) { org.hibernate.Session sess = sessionFactory.getCurrentSession(); sess.enableFetchProfile("contracts-with-productPos"); Contracts contract = (Contracts) sess.get(Contracts.class, id); return contract.getProductPositions(); } @Override public Clients getClientsForPosition(Long id) { org.hibernate.Session sess = sessionFactory.getCurrentSession(); sess.enableFetchProfile("contracts-with-clients"); ContragentsInContract contragent = (ContragentsInContract) sess.get(ContragentsInContract.class, id); return contragent.getClient(); } @Override public Products getProductForPosition(Long id) { org.hibernate.Session sess = sessionFactory.getCurrentSession(); sess.enableFetchProfile("contracts-with-products"); PositionsInContract position = (PositionsInContract) sess.get(PositionsInContract.class, id); return position.getProduct(); } public PositionsInContract getPositionForId(Long id) { return (PositionsInContract) sessionFactory.getCurrentSession().get(PositionsInContract.class, id); } public ContragentsInContract getAgentForId(Long id) { return (ContragentsInContract) sessionFactory.getCurrentSession().get(ContragentsInContract.class, id); } public List<Contracts> getFromProxy(List<Contracts> proxy) { Disjunction or = Restrictions.disjunction(); for (Contracts pr : proxy) { or.add(Restrictions.eq("id", pr.getId())); } return sessionFactory.getCurrentSession().createCriteria(Contracts.class).add(or).list(); } public Calendar prepareDate(Date date) { Calendar calendar = Calendar.getInstance(); calendar.setTime(date); calendar.set(Calendar.HOUR, 0); calendar.set(Calendar.MINUTE, 0); calendar.set(Calendar.SECOND, 0); calendar.set(Calendar.MILLISECOND, 0); return calendar; } public List getStatisticsOnContractsForDates(Date after, Date before) { Calendar afterCalendar = prepareDate(after); Calendar beforeCalendar = prepareDate(before); // after = afterCalendar.getTime(); // before = beforeCalendar.getTime(); Integer yearDif = beforeCalendar.get(Calendar.YEAR) - afterCalendar.get(Calendar.YEAR); Integer monthDif = beforeCalendar.get(Calendar.MONTH) - afterCalendar.get(Calendar.MONTH) + yearDif * 12; List<Statistics> stat = new ArrayList<Statistics>(); org.hibernate.Session sess = sessionFactory.getCurrentSession(); sess.enableFetchProfile("contracts-with-clients"); List<Object[]> res = new ArrayList<Object[]>(); // afterCalendar = prepareDate(before); // afterCalendar.add(Calendar.MONTH, -1); for (int i = 0; i < monthDif + 1; i++) { if (monthDif == 0) { } else { if (i == 0) { afterCalendar.setTime(beforeCalendar.getTime()); afterCalendar.set(Calendar.DAY_OF_MONTH, 1); } else if (i == monthDif) { beforeCalendar.setTime(afterCalendar.getTime()); afterCalendar.setTime(after); } else { beforeCalendar.setTime(afterCalendar.getTime()); afterCalendar.add(Calendar.MONTH, -1); } } // beforeCalendar.add(Calendar.MONTH, -1); // afterCalendar.add(Calendar.MONTH, -1); Date afterNew = afterCalendar.getTime(); Date beforeNew = beforeCalendar.getTime(); String afterString = afterNew.toString().substring(4, 7); /* List results = sess.createCriteria(Contracts.class) .add( Restrictions.between("begin_date", after, before) ) .setProjection( Projections.projectionList() .add( Projections.sum("totalSum") ) .add( Projections.avg("totalSum") ) .add( Projections.rowCount() ) //.add( Projections.max("totalSum") ) ).list();*/ Query query = sess.createQuery("select '" + afterString + "', sum(contracts.totalSum), " + "avg(contracts.totalSum), count(contracts) " + "from com.mycompany.CRMFly.entities.Contracts contracts " + " where contracts.begin_date between :date1 and :date2"); query.setParameter("date1", afterNew); query.setParameter("date2", beforeNew); List list = query.list(); if (list != null && list.size() != 0) { for (Iterator it = list.iterator(); it.hasNext();) { Object[] row = (Object[]) it.next(); stat.add(new Statistics(row)); } } } return stat; } public List getStatisticsOnClientsForDates(Date after, Date before) { Calendar afterCalendar = prepareDate(after); Calendar beforeCalendar = prepareDate(before); List<Statistics> stat = new ArrayList<Statistics>(); org.hibernate.Session sess = sessionFactory.getCurrentSession(); sess.enableFetchProfile("contracts-with-clients"); Query query = sess.createQuery("select contr.name, sum(contracts.totalSum), " + "avg(contracts.totalSum), count(contracts) " + "from com.mycompany.CRMFly.entities.Contracts contracts " + "inner join contracts.contragents " + "contr where " + "contracts.begin_date between :date1 and :date2 " + "group by contr.name "); query.setParameter("date1", after); query.setParameter("date2", before); List list = query.list(); for (Iterator it = list.iterator(); it.hasNext();) { Object[] row = (Object[]) it.next(); stat.add(new Statistics(row)); } return stat; } public List getStatisticsOnProductsForDates(Date after, Date before) { Calendar afterCalendar = prepareDate(after); Calendar beforeCalendar = prepareDate(before); List<Statistics> stat = new ArrayList<Statistics>(); org.hibernate.Session sess = sessionFactory.getCurrentSession(); Query query = sess.createQuery("select product.productName, sum(contracts.totalSum), " + "avg(contracts.totalSum), count(contracts) " + "from com.mycompany.CRMFly.entities.Contracts contracts " + "inner join contracts.productPositions " + "product where " + "contracts.begin_date between :date1 and :date2 " + "group by product.productName "); query.setParameter("date1", after); query.setParameter("date2", before); List list = query.list(); for (Iterator it = list.iterator(); it.hasNext();) { Object[] row = (Object[]) it.next(); stat.add(new Statistics(row)); } return stat; } public List getStatisticsOnOrganisationsForDates(Date after, Date before) { Calendar afterCalendar = prepareDate(after); Calendar beforeCalendar = prepareDate(before); List<Statistics> stat = new ArrayList<Statistics>(); org.hibernate.Session sess = sessionFactory.getCurrentSession(); Query query = sess.createQuery("select contr.name, sum(contracts.totalSum), " + "avg(contracts.totalSum), count(contracts) " + "from com.mycompany.CRMFly.entities.Contracts contracts " + "inner join contracts.contragents " + "contr where " + "contracts.begin_date between :date1 and :date2 " + "and contr.type='organisation' " + "group by contr.name "); query.setParameter("date1", after); query.setParameter("date2", before); List list = query.list(); for (Iterator it = list.iterator(); it.hasNext();) { Object[] row = (Object[]) it.next(); stat.add(new Statistics(row)); } return stat; } public List getStatisticsOnCustomersForDates(Date after, Date before) { Calendar afterCalendar = prepareDate(after); Calendar beforeCalendar = prepareDate(before); List<Statistics> stat = new ArrayList<Statistics>(); org.hibernate.Session sess = sessionFactory.getCurrentSession(); Query query = sess.createQuery("select contr.name, sum(contracts.totalSum), " + "avg(contracts.totalSum), count(contracts) " + "from com.mycompany.CRMFly.entities.Contracts contracts " + "inner join contracts.contragents " + "contr where " + "contracts.begin_date between :date1 and :date2 " + "and contr.type='customer' " + "group by contr.name "); query.setParameter("date1", after); query.setParameter("date2", before); List list = query.list(); for (Iterator it = list.iterator(); it.hasNext();) { Object[] row = (Object[]) it.next(); stat.add(new Statistics(row)); } return stat; } public List getStatisticsForCategoriesOnDates(Date after, Date before) { Calendar afterCalendar = prepareDate(after); Calendar beforeCalendar = prepareDate(before); Integer yearDif = beforeCalendar.get(Calendar.YEAR) - afterCalendar.get(Calendar.YEAR); Integer monthDif = beforeCalendar.get(Calendar.MONTH) - afterCalendar.get(Calendar.MONTH) + yearDif * 12; List<Statistics> stat = new ArrayList<Statistics>(); org.hibernate.Session sess = sessionFactory.getCurrentSession(); List<Object[]> res = new ArrayList<Object[]>(); for (int i = 0; i < monthDif + 1; i++) { if (monthDif == 0) { } else { if (i == 0) { afterCalendar.setTime(beforeCalendar.getTime()); afterCalendar.set(Calendar.DAY_OF_MONTH, 1); } else if (i == monthDif) { beforeCalendar.setTime(afterCalendar.getTime()); afterCalendar.setTime(after); } else { beforeCalendar.setTime(afterCalendar.getTime()); afterCalendar.add(Calendar.MONTH, -1); } } Date afterNew = afterCalendar.getTime(); Date beforeNew = beforeCalendar.getTime(); String afterString = afterNew.toString().substring(4, 7); Query query = sess.createQuery("select contr.type||'s - '||'" + afterString + "', sum(contracts.totalSum), " + "avg(contracts.totalSum), count(contracts) " + "from com.mycompany.CRMFly.entities.Contracts contracts " + "inner join contracts.contragents " + "contr where " + "contracts.begin_date between :date1 and :date2 " + "group by contr.type"); query.setParameter("date1", afterNew); query.setParameter("date2", beforeNew); List list = query.list(); if (list != null && list.size() != 0) { for (Iterator it = list.iterator(); it.hasNext();) { Object[] row = (Object[]) it.next(); stat.add(new Statistics(row)); } } } return stat; } public List getStatisticsOnContragentsRolesForDates(Date after, Date before) { Calendar afterCalendar = prepareDate(after); Calendar beforeCalendar = prepareDate(before); List<Statistics> stat = new ArrayList<Statistics>(); org.hibernate.Session sess = sessionFactory.getCurrentSession(); Query query = sess.createQuery( "select conrole.dictValue, sum(contracts.totalSum), " + "avg(contracts.totalSum), count(contracts) " + "from com.mycompany.CRMFly.entities.Contracts contracts " + "inner join contracts.contragents " + "contr inner join contr.role conrole where " + "contracts.begin_date between :date1 and :date2 " + "group by conrole.dictValue "); query.setParameter("date1", after); query.setParameter("date2", before); List list = query.list(); for (Iterator it = list.iterator(); it.hasNext();) { Object[] row = (Object[]) it.next(); stat.add(new Statistics(row)); } return stat; } public List getStatisticsOnCustomersWithRolesForDates(Date after, Date before) { Calendar afterCalendar = prepareDate(after); Calendar beforeCalendar = prepareDate(before); List<Statistics> stat = new ArrayList<Statistics>(); org.hibernate.Session sess = sessionFactory.getCurrentSession(); Query query = sess.createQuery("select contr.name||' - '|| conrole.dictValue, sum(contracts.totalSum), " + "avg(contracts.totalSum), count(contracts) " + "from com.mycompany.CRMFly.entities.Contracts contracts " + "inner join contracts.contragents " + "contr inner join contr.role conrole where " + "contracts.begin_date between :date1 and :date2 " + "and contr.type='customer' " + "group by contr.name, conrole.dictValue"); query.setParameter("date1", after); query.setParameter("date2", before); List list = query.list(); for (Iterator it = list.iterator(); it.hasNext();) { Object[] row = (Object[]) it.next(); stat.add(new Statistics(row)); } return stat; } public List getStatisticsOnOrganisationsWithRolesForDates(Date after, Date before) { Calendar afterCalendar = prepareDate(after); Calendar beforeCalendar = prepareDate(before); List<Statistics> stat = new ArrayList<Statistics>(); org.hibernate.Session sess = sessionFactory.getCurrentSession(); Query query = sess.createQuery("select contr.name||' '|| conrole.dictValue, sum(contracts.totalSum), " + "avg(contracts.totalSum), count(contracts) " + "from com.mycompany.CRMFly.entities.Contracts contracts " + "inner join contracts.contragents " + "contr inner join contr.role conrole where " + "contracts.begin_date between :date1 and :date2 " + "and contr.type='organisation' " + "group by contr.name, conrole.dictValue"); query.setParameter("date1", after); query.setParameter("date2", before); List list = query.list(); for (Iterator it = list.iterator(); it.hasNext();) { Object[] row = (Object[]) it.next(); stat.add(new Statistics(row)); } return stat; } }